
GO
/****** Object:  StoredProcedure [dbo].[sp_hourly_plan_num1]    Script Date: 2019-06-21 15:04:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--每小时计划数量计算1
Create PROCEDURE  [dbo].[sp_hourly_plan_num1] 
	@depid int,
	@centerid int,
	@hour_s datetime,
	@hour_e datetime,
	@plannum float output
	--@realnum float output,
	--@realworktime float output
AS
BEGIN
	SET NOCOUNT ON;

	declare @realnum float,@realworktime float
	set @realnum=0
	set @plannum=0
	set @realworktime=0
	declare @unithour float,@t1 datetime,@t2 datetime,@last_et datetime,@num float,@hasRow int
	--select @hour_s,@hour_e
	set @unithour=0
	set @t1=@hour_s
	if @depid=0
	begin 
	select @hasRow=count(*) from [WorkRecord] INNER JOIN [ProProcess] ON [WorkRecord].[ProProcessId]=[ProProcess].[ProProcessId] where [WorkRecord].[ReportTime]>=@hour_s and [WorkRecord].[ReportTime]<@hour_e and [WorkRecord].[CenterId]=@centerid and [WorkRecord].[RecordState]=2
	end
	else
	begin 
		select @hasRow=count(*) from [WorkRecord] INNER JOIN [ProProcess] ON [WorkRecord].[ProProcessId]=[ProProcess].[ProProcessId] where [WorkRecord].[ReportTime]>=@hour_s and [WorkRecord].[ReportTime]<@hour_e and [WorkRecord].[DepId]=@depid and [WorkRecord].[CenterId]=@centerid  and [WorkRecord].[RecordState]=2
	end
	 --select [WorkRecord].[GenuineNum], [ProProcess].[UnitHour],[WorkRecord].[ReportTime] from [WorkRecord] INNER JOIN [ProProcess] ON [WorkRecord].[ProProcessId]=[ProProcess].[ProProcessId] where [WorkRecord].[ReportTime]>=@hour_s and [WorkRecord].[ReportTime]<@hour_e and [WorkRecord].[DepId]=@depid and [WorkRecord].[CenterId]=@centerid
	--时间分区
		if @depid=0
		begin
			declare cur2 cursor for  select [WorkRecord].[GenuineNum], [ProProcess].[UnitHour],[WorkRecord].[ReportTime]  from [WorkRecord] INNER JOIN [ProProcess] ON [WorkRecord].[ProProcessId]=[ProProcess].[ProProcessId] where [WorkRecord].[ReportTime]>=@hour_s and [WorkRecord].[ReportTime]<@hour_e and [WorkRecord].[CenterId]=@centerid and [WorkRecord].[RecordState]=2
		end
		else
		begin
			declare cur2 cursor for  select [WorkRecord].[GenuineNum], [ProProcess].[UnitHour],[WorkRecord].[ReportTime] from [WorkRecord] INNER JOIN [ProProcess] ON [WorkRecord].[ProProcessId]=[ProProcess].[ProProcessId] where [WorkRecord].[ReportTime]>=@hour_s and [WorkRecord].[ReportTime]<@hour_e and [WorkRecord].[DepId]=@depid and [WorkRecord].[CenterId]=@centerid  and [WorkRecord].[RecordState]=2
		end

		open cur2
		fetch next from cur2 into @num,@unithour,@t2
		while @@FETCH_STATUS=0
		begin	
			set @realnum=@realnum+@num
			set @realworktime=@realworktime+@num*@unithour	
			if(@unithour>0)
			begin
				--select @t1,@t2
				set @plannum=@plannum+DATEDIFF(second,@t1,@t2)/@unithour
			end
			
			set @t1=@t2
			fetch next from cur2 into @num,@unithour,@t2
			if(@@FETCH_STATUS!=0)
			begin
				if(@unithour>0)
				begin			
					set @plannum=@plannum+DATEDIFF(second,@t2,@hour_e)/@unithour
				end
			end
		end
		
		if(@hasRow=0)
		begin
			--确定区间
		declare @t datetime,@curhours int ,@endtime datetime
		set @t=cast(convert(varchar(100),GETDATE(),112) as datetime)
		set @curhours=datediff(hour,@t,@hour_s)
		set @endtime=dateadd(hour,@curhours+1,@t)
			if(@depid=0)
			begin
				select top 1 @unithour= [ProProcess].[UnitHour]  from [WorkRecord] INNER JOIN [ProProcess] ON [WorkRecord].[ProProcessId]=[ProProcess].[ProProcessId] where [WorkRecord].[ReportTime]>=@hour_e and [WorkRecord].[ReportTime]<@endtime  and [WorkRecord].[CenterId]=@centerid and [WorkRecord].[RecordState]=2
			end
			else
			begin
				select top 1 @unithour= [ProProcess].[UnitHour]  from [WorkRecord] INNER JOIN [ProProcess] ON [WorkRecord].[ProProcessId]=[ProProcess].[ProProcessId] where [WorkRecord].[ReportTime]>=@hour_e and [WorkRecord].[ReportTime]<@endtime and [WorkRecord].[DepId]=@depid and [WorkRecord].[CenterId]=@centerid and [WorkRecord].[RecordState]=2
			end

			if(@unithour!=0)
			begin
				set @plannum=DATEDIFF(second,@hour_s,@hour_e)/@unithour
			end
		end

		set @plannum=round(@plannum,0)
		close cur2
		deallocate cur2

END
