USE [ChiefmesNew]
GO
/****** Object: StoredProcedure [dbo].[st_Alarm_GenAlarmDealTime] Script Date: 04/05/2016 09:42:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/********************
Create By: Wuchun
Create Date: 2013-08-20
Module: Alarm
Remark: 产生报警派工单的台帐处理时间
update by WUchun on 2013-11-15 增加按班次计算时段逻辑
exec [st_Alarm_GenAlarmDealTime]
truncate table MES_AlarmBillDealTime
select * from MES_AlarmBillDealTime
********************/
ALTER proc [dbo].[st_Alarm_GenAlarmDealTime]
as
declare @rBCCode varchar(10)
select @rBCCode = dbo.[Fn_GetBCCode]()
select distinct Seq = identity(int), AlarmBillNO, AlarmCode, AlarmSetNO, MO, DispatchNO, DispatchPrior, MachineNO, BeginTime,
UnitTime, CalTimesByBC , BCCode
into #AlarmBillTime
from MES_AlarmBill
where DealFlag = 0 and AlarmCode in(select AlarmCode from MES_AlarmTypeSet where TimesShow =1)
and (BCCode = 'All' or charindex(BCCode, @rBCCode)>0)
and UnitTime>0 and datediff(minute,getdate(),NextDealTime)<0
declare @icount int, @i int , @UnitTime float , @MO varchar(50), @DispatchPrior float, @CalTimesByBC bit
declare @AlarmBillNO varchar(100), @AlarmCode varchar(100), @AlarmSetNo varchar(100), @NextDealTime datetime
declare @DispatchNO varchar(100), @MachineNO varchar(100), @BeginTime datetime , @LastDealTime datetime, @BCCode varchar(50)
declare @BCBeginTime datetime , @BCEndTime datetime
select @i= 1
select @icount = COUNT(*) from #AlarmBillTime
while(@i<=@icount)
begin
select @AlarmBillNO = AlarmBillNO, @AlarmCode = AlarmCode, @MO = MO,
@AlarmSetNo = AlarmSetNo, @DispatchNO = DispatchNO, @DispatchPrior = DispatchPrior, @MachineNO = MachineNO
, @BeginTime = BeginTime, @LastDealTime = null, @UnitTime = UnitTime, @CalTimesByBC = CalTimesByBC, @BCCode = BCCode
from #AlarmBillTime
where Seq = @i
--存在符合要求的触发条件则忽略
if(exists(select top 1 * from MES_AlarmBillDealTime
where AlarmbillNO =@AlarmBillNO and AlarmSetNo= @AlarmSetNo
and GETDATE() between DealBegin and DealEnd)
)
begin
set @i = @i+1
continue
end
else
begin
select @BCBeginTime = null, @BCEndTime = null
if(@CalTimesByBC = 1)
begin
if(@BCCode = 'All')
set @BCCode =''
--以班次开始时间计算时段时间
set @BCBeginTime = convert(datetime,dbo.Fn_GetBCBeginTime(@BCCode))
set @BCEndTime = convert(datetime, dbo.Fn_GetBCEndTime(@BCCode))
select @LastDealTime = max(DealEnd)
from MES_AlarmBillDealTime
where AlarmbillNO =@AlarmBillNO and AlarmSetNo= @AlarmSetNo
--大于班次时间则跳过
if(@LastDealTime>@BCEndTime)
begin
set @i = @i+1
continue
end
--小于班次时间则取班次开始时间
if(@LastDealTime<@BCBeginTime or @LastDealTime is null)
set @LastDealTime = @BCBeginTime
end
else
begin
--按派工单开始时间计算时段
select @LastDealTime = max(DealEnd)
from MES_AlarmBillDealTime
where AlarmbillNO =@AlarmBillNO and AlarmSetNo= @AlarmSetNo
if(@LastDealTime is null)
set @LastDealTime = @BeginTime
end
set @NextDealTime = DATEADD(minute, @UnitTime*60, @LastDealTime)
while(getdate()>@NextDealTime)
begin
if(DATEDIFF(HOUR, @NextDealTime, GETDATE())<2*@UnitTime)
begin
insert into MES_AlarmBillDealTime(AlarmBillNo, AlarmCode, AlarmSetNo, MO, DispatchNo, DispatchPrior, MachineNO,
DealBegin, DealEnd, DealFlag, BeginTime, UnitTime, CreateTime)
select @AlarmBillNO, @AlarmCode, @AlarmSetNo, @MO, @DispatchNo, @DispatchPrior, @MachineNO,
dateadd(second,1,@LastDealTime), @NextDealTime, 0, @BeginTime, @UnitTime, GETDATE()
end
set @LastDealTime = @NextDealTime
set @NextDealTime = DATEADD(minute, @UnitTime*60, @LastDealTime)
end
insert into MES_AlarmBillDealTime(AlarmBillNo, AlarmCode, AlarmSetNo, MO, DispatchNo, DispatchPrior, MachineNO,
DealBegin, DealEnd, DealFlag, BeginTime, UnitTime, CreateTime)
select @AlarmBillNO, @AlarmCode, @AlarmSetNo, @MO, @DispatchNo, @DispatchPrior, @MachineNO,
dateadd(second,1,@LastDealTime), @NextDealTime, 0, @BeginTime, @UnitTime, GETDATE()
update MES_AlarmBill set NextDealTime = @NextDealTime where AlarmBillNO = @AlarmBillNO and BCCode = @BCCode
end
set @i = @i+1
end
delete from MES_AlarmBillDealTime where DealEnd is null
delete from MES_AlarmBillDealTime where CreateTime<dateadd(day,-1,getdate())
drop table #AlarmBillTime
/****** Object: StoredProcedure [dbo].[st_MES_GenStatStopCard] Script Date: 10/17/2013 09:54:34 ******/
SET ANSI_NULLS ON
---------------------------------------------------------------------------------------------------------
select top 100 * from MES_AlarmBill order by CreateDate desc
select top 100 * from MES_AlarmBillDealTime