
/****** Object:  StoredProcedure [dbo].[sp_hourly_production_num2]    Script Date: 2019-06-21 15:08:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





--每小时计划数量-实际数量-效率- 设备日志
CREATE procedure [dbo].[sp_hourly_production_num2] 
(
	@depid int,
	@centerid int
)
as 
begin
	set nocount on  
	if @depid=null
	begin 
		set @depid=0
	end

		create table   #temp(
		id int primary key  IDENTITY(1,1) ,
		hour_s datetime,
		hour_e datetime,
		--计划数
		plannum float,
		--实际数量
		realnum float,
		--缺陷数
		--defectnum float,
		--废品数
		--scrapnum float,
		--效率
		eff float
	)
	
	--s:时程表
	create table #schedule(
		id int primary key  IDENTITY(1,1) ,
		st datetime,
		et datetime
	)

	declare @st datetime,@et datetime
	declare @today datetime
	set @today=getdate()
	declare cur cursor for select cast(convert(nvarchar(100),@today,112)+' '+convert(nvarchar(100),[AttStartTime],108) as datetime), cast(convert(nvarchar(100),@today,112)+' '+convert(nvarchar(100),[AttEndTime],108) as datetime) from TimeSchedule
	open cur 
	fetch next from cur into @st,@et
	while @@FETCH_STATUS=0
	begin
		if @st>@et
		begin 
			insert into #schedule(st,et) values(dateadd(day,-1,@st),@et)
			insert into #schedule(st,et) values(@st,dateadd(day,+1,@et))
		end
		else
		begin
			insert into #schedule(st,et) values(@st,@et)
		end
		fetch next from cur into @st,@et		 
	end
	close cur
	deallocate cur
	--e:时程表

	--获取当前小时
	declare @curtime datetime,@curhours int
	declare @t varchar(100)
	set @t=convert(varchar(100),@today,112)
	set @curhours=datediff(hour,@t,@today)
	set @curtime=dateadd(hour,@curhours,@t)
	declare @hcount int;
	set @hcount=-7;

	declare @hour_s datetime,@hour_e datetime,@thour_s datetime,@thour_e datetime
	declare @plannum float,@planworktime float,@realnum float,@realworktime float,@eff float,@intime int,@tplannum float

	while @hcount<=0
	begin		
		set @hour_s=dateadd(hour,@hcount,@curtime)
		set @thour_s=dateadd(hour,@hcount,@curtime)
		set @hour_e=dateadd(hour,@hcount+1,@curtime)
		set @thour_e=dateadd(hour,@hcount+1,@curtime)
		set @plannum=0
		set @realnum=0
		set @tplannum=0
		set @realworktime=0
		set @planworktime=0
		set @eff=0
		set @intime=0


		exec [dbo].[sp_hourly_plan_num2] @depid,@centerid,@hour_s,@hour_e,@realnum output,@plannum out,@eff out


		insert into #temp (hour_s,hour_e,plannum,realnum,eff) values(@hour_s,@hour_e,@plannum,@realnum,@eff)
		set @hcount = @hcount+1
	end

	select * from #temp

	return 1
end
GO


