USE [EASYCON_LJ]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_pk_eff1]    Script Date: 2019-04-03 10:15:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE function [dbo].[fn_pk_eff1](
	@pkworkid int,
	@dataSource int

)
returns float
as 
	begin
	declare @eff float
	set @eff=0
	--设备日志
	if @dataSource=1  
		begin
			declare cur_eff cursor for select case when sum(datediff(SECOND,[dbo].[ElecProWorkNum].[CreateTime],[dbo].[ElecProWorkNum].[ReportTime]))<>0 then  cast(sum([dbo].[ElecProWorkNum].[RecordNum]*[dbo].[ProProcess].[UnitHour]) as float)/cast(sum(datediff(SECOND,[dbo].[ElecProWorkNum].[CreateTime],[dbo].[ElecProWorkNum].[ReportTime])) as float) else NULL end from [dbo].[ProcessWork] INNER JOIN [dbo].[ElecProWorkNum] ON [dbo].[ProcessWork].[ProWorkId]=[dbo].[ElecProWorkNum].[ProWorkId] INNER JOIN [dbo].[ProProcess] ON [dbo].[ProProcess].[PFormMId]=[dbo].[ProcessWork].[PFormMId] AND [dbo].[ProcessWork].[ProProcessId]=[dbo].[ProProcess].[ProProcessId] where [dbo].[ProcessWork].[ProWorkId]=@pkworkid 
			open cur_eff
			fetch next from cur_eff into @eff
			close cur_eff
			deallocate cur_eff
		end
	else 
		begin
			--scada
			if @dataSource=2 
				begin
					declare cur_eff cursor for  select case when (sum(AutoWork.dRuntime+AutoWork.dStopTime+AutoWork.dUndoTime)>0) then cast(sum(AutoWork.dEquNum*ProProcess.UnitHour)  as float ) /cast (sum(AutoWork.dRuntime+AutoWork.dStopTime+AutoWork.dUndoTime) as float ) else NULL end from AutoWork inner join ProcessWork on AutoWork.ProWorkId<>'' and AutoWork.ProWorkId is not null and AutoWork.ProWorkId=ProcessWork.ProWorkId inner join ProProcess on ProcessWork.ProProcessId=ProProcess.ProProcessId where AutoWork.ProWorkId=@pkworkid
					open cur_eff
					fetch next from cur_eff into @eff
					close cur_eff
					deallocate cur_eff
				end
			else
				begin
				--报工单
					declare cur_eff cursor for select case when sum(datediff(SECOND,[dbo].[ElecProWorkNum].[CreateTime],[dbo].[ElecProWorkNum].[ReportTime]))<>0 then  cast(sum([dbo].[ElecProWorkNum].[RecordNum]*[dbo].[ProProcess].[UnitHour]) as float)/cast(sum(datediff(SECOND,[dbo].[ElecProWorkNum].[CreateTime],[dbo].[ElecProWorkNum].[ReportTime])) as float) else NULL end from [dbo].[ProcessWork] INNER JOIN [dbo].[ElecProWorkNum] ON [dbo].[ProcessWork].[ProWorkId]=[dbo].[ElecProWorkNum].[ProWorkId] INNER JOIN [dbo].[ProProcess] ON [dbo].[ProProcess].[PFormMId]=[dbo].[ProcessWork].[PFormMId] AND [dbo].[ProcessWork].[ProProcessId]=[dbo].[ProProcess].[ProProcessId] where [dbo].[ProcessWork].[ProWorkId]=@pkworkid 
					open cur_eff
					fetch next from cur_eff into @eff
					close cur_eff
					deallocate cur_eff
				end
		end 


	return @eff

	end
GO


