

/****** Object:  StoredProcedure [dbo].[sp_hourly_plan_num2]    Script Date: 2019-06-21 15:05:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--每小时计划数量计算2-设备日志
CREATE PROCEDURE  [dbo].[sp_hourly_plan_num2] 
	@depid int,
	@centerid int,
	@hour_s datetime,
	@hour_e datetime,
	@realnum float output,
	@plannum float output,
	@eff float output
	--@realnum float output,
	--@realworktime float output
AS
BEGIN
	SET NOCOUNT ON;

	declare @realworktime float,@planworktime float
	set @realnum=0
	set @plannum=0
	set @realworktime=0
	set @planworktime=0
	declare @unithour float,@t1 datetime,@t2 datetime,@last_et datetime,@num float
	set @unithour=0
	set @eff=0
	--时间分区
		if @depid=0
		begin
			declare cur2 cursor for  select [ElecProWorkNum].[RecordNum], [ProProcess].[UnitHour],[ElecProWorkNum].[CreateTime],[ElecProWorkNum].[ReportTime]  from [ElecProWorkNum] INNER JOIN [ProcessWork] ON  [ElecProWorkNum].[ProWorkId]=[ProcessWork].[ProWorkId] INNER JOIN [ProProcess] ON [ProcessWork].[ProProcessId]=[ProProcess].[ProProcessId] where [ElecProWorkNum].[ReportTime]>=@hour_s and [ElecProWorkNum].[ReportTime]<@hour_e and [ProcessWork].[CenterId]=@centerid
		end
		else
		begin
		--select  [ElecProWorkNum].[RecordNum], [ProProcess].[UnitHour],[ElecProWorkNum].[CreateTime],[ElecProWorkNum].[ReportTime]  from [ElecProWorkNum] INNER JOIN [ProcessWork] ON  [ElecProWorkNum].[ProWorkId]=[ProcessWork].[ProWorkId] INNER JOIN [ProProcess] ON [ProcessWork].[ProProcessId]=[ProProcess].[ProProcessId] where [ElecProWorkNum].[ReportTime]>=@hour_s and [ElecProWorkNum].[ReportTime]<@hour_e and [ProcessWork].[DepId]=@depid and [ProcessWork].[CenterId]=@centerid
			declare cur2 cursor for  select  [ElecProWorkNum].[RecordNum], [ProProcess].[UnitHour],[ElecProWorkNum].[CreateTime],[ElecProWorkNum].[ReportTime]  from [ElecProWorkNum] INNER JOIN [ProcessWork] ON  [ElecProWorkNum].[ProWorkId]=[ProcessWork].[ProWorkId] INNER JOIN [ProProcess] ON [ProcessWork].[ProProcessId]=[ProProcess].[ProProcessId] where [ElecProWorkNum].[ReportTime]>=@hour_s and [ElecProWorkNum].[ReportTime]<@hour_e and [ProcessWork].[DepId]=@depid and [ProcessWork].[CenterId]=@centerid
		end

		open cur2
		fetch next from cur2 into @num,@unithour,@t1,@t2
		while @@FETCH_STATUS=0
		begin	
			set @realnum=@realnum+@num
			set @realworktime=@realworktime+@num*@unithour	
			--if @num!=0
			--begin
				--select @t1,@t2,DATEDIFF(second,@t1,@t2),@unithour
				set @planworktime=@planworktime+DATEDIFF(second,@t1,@t2)
				if(@unithour>0 )
				begin
					--select @t1,@t2,DATEDIFF(second,@t1,@t2),@unithour

					set @plannum=@plannum+DATEDIFF(second,@t1,@t2)/@unithour
				end
			--end
		
			
			fetch next from cur2 into @num,@unithour,@t1,@t2

		end
		set @plannum=round(@plannum,0)
		if(@planworktime!=0)
		begin
			set @eff=@realworktime/@planworktime
			set @eff=round(@eff,4)
		end
		close cur2
		deallocate cur2

END
GO


