[dbo].[st_MES_MonitorMachine]

--------------------------------------------

USE [ChiefMESNew]

GO

/****** Object: StoredProcedure [dbo].[st_MES_MonitorMachine] Script Date: 05/15/2015 17:18:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*************

creator: Wuchun

create date: 2012.12.17

Module Code: 100001

Remark: 按车间显示机器监视

//增加试模工单显示 by Wuchun on 2014/03/17

//增加停机刷卡表，提高性能 on 2014/04/10

//试模不显示未刷卡停机，显示试模中断

//良品数=生产数加上调机时的生产数再减去巡机数 2014.7.22 hz

//添加返回判断维修结果RepairStatus 2014.8.5 hz

//机台根据工作中心查询,把left join 修改为inner join 2015-5-12 hz

exec st_MES_MonitorMachine '','','','', 'zh_cn',100,1

**************/

ALTER proc [dbo].[st_MES_MonitorMachine] (

@in_StaCode varchar(20) = '',

@in_WsCode varchar(20) = '', --车间代码

@in_MachineState varchar(10) = '', --机器状态

@in_MachineNo varchar(20) = '', --机器编号

@in_Lan varchar(10) = 'zh_cn', --多语言

@in_pagesize smallint = -1,

@in_page smallint = 1,

@out_total_rows int = 1 output

)

as

begin

if object_ID('tempdb..#tempTable') is not null drop table #tempTable

set nocount on

CREATE TABLE #tempTable

(

RowSeq int identity(1,1),

MachineNo varchar(50),

DeviceStatus varchar(20),

WSCode varchar(20),

MO varchar(20),

DispatchNO varchar(50),

DispatchPrior float,

LiveCycle decimal(18,2),

GoodQty int,

BadQty int,

ClientIP varchar(20),

BeginCycle datetime,

StopReason varchar(100),

StopTime varchar(1000),

MouldNO varchar(50),

ItemNO varchar(50),

MES_COMQuality int default(0),

CardEmployee varchar(50),

StopBegin datetime,

RepairStatus int

)

DECLARE @MachineNO varchar(50),@WsCode varchar(20),@IPAddr varchar(20),@EndCycle char(22),@DispatchNO varchar(50),

@MO varchar(50), @DispatchPrior float, @MouldNO varchar(50),

@Cycle decimal(18,2), @StandCycle decimal(18,2), @MinInjectionCycle decimal(18,2),

@MaxInjectionCycle decimal(18,2),

@BeginCycle char(19), @ItemNO varchar(50), @ReasonName varchar(50), @Unknown varchar(2000),

@AbNormalProduct varchar(2000),@NotStarted varchar(2000), @RegularProduct varchar(2000),

@StopTime int, @StopTime1 int,@CardEmployee varchar(50), @StopBegin datetime

declare @txt_NOOrder varchar(200), @txt_BrokenNetWork varchar(200), @txt_SM varchar(200),

@MOType int,@proQty int, @DispatchStatus int

if (@in_Lan = 'zh_cn') set @in_Lan = 'zh_cn'

select @AbNormalProduct=case @in_Lan

when 'zh_cn' then Lan_CN

when 'en' then Lan_EN

else ''

end

from Sys_Language

where keyname='txt_AbNormalProduct'

select @NotStarted = case @in_Lan

when 'zh_cn' then Lan_Cn

when 'en' then Lan_En

else ''

end

from Sys_Language

where keyname='txt_NotStarted'

select @RegularProduct=case @in_Lan

when 'zh_cn' then Lan_Cn

when 'en' then Lan_En

else ''

end

from Sys_Language

where keyname='txtRegularProduct'

select @Unknown=case @in_Lan

when 'zh_cn' then Lan_Cn

when 'en' then Lan_En

else ''

end

from Sys_Language

where keyname='txt_Unknown' --未刷卡停机

select @txt_NOOrder=case @in_Lan

when 'zh_cn' then Lan_Cn

when 'en' then Lan_En

else ''

end

from Sys_Language

where keyname='txt_NOOrder'

select @txt_BrokenNetWork=case @in_Lan when 'zh_cn' then Lan_Cn when 'en' then Lan_En else '' end

from Sys_Language

where keyname='txt_BrokenNetWork'

select @txt_SM=case @in_Lan when 'zh_cn' then Lan_CN when 'en' then Lan_EN else '' end

from Sys_Language

where keyname='txt_MonitorSM'

----------------------------------------------------------------------------------------

--获取停机卡

select *

into #t_StopCardDetail

from V_CardDetail a

WHERE a.Flag = 1 and a.EndDate is null

declare @Normal_Count int , @abNormal_Count int, @Stop_Count int, @NoOrder_count int, @NONetWork_Count int

declare @MaxUploadDate datetime , @MES_COMQuality int, @txt_Unknow varchar(200)

declare @TotalNum int, @UploadTime datetime, @lastMociEndCycle datetime, @LastMociTotalnum int

--Black 未开机

--Normal 正常

insert into #tempTable(MachineNO, DeviceStatus, WSCode, MO, DispatchNO

, DispatchPrior, LiveCycle, GoodQty, BadQty, ClientIP,

BeginCycle, StopReason, MES_COMQuality,StopTime,StopBegin,RepairStatus)

select mm.MachineNO,

case when isnull(datediff(minute, mm.COMTime, getdate()),2)>1 then 'NONetWork' else 'Stop' end,

mm.WsCode, '', '', 0.00, 0.00, 0, 0, ci.IPAddr, null,

case when isnull(datediff(minute, mm.COMTime, getdate()),2)>1 then @txt_BrokenNetWork else @Unknown end,

case when isnull(datediff(minute, mm.COMTime, getdate()),2)>1 then 0 else isnull(mm.MES_COMQuality,0) end,

'',

case when isnull(datediff(minute, mm.COMTime, getdate()),2)>1 then mm.COMTime else null end,

mm.RepairStatus

FROM MES_Machine mm

inner join dbo.FN_GetStaCode(@In_StaCode) ws on mm.StaCode = ws.StaCode

left join MES_Communication ci on ci.MachineNo=mm.MachineNo

where --(@in_StaCode = '' or ws.StaCode = @In_StaCode) and

(@in_MachineNO = '' or mm.MachineNO = @in_MachineNO)

order by ci.IPAddr desc --mm.MachineNO asc

DECLARE @Today char(10)

SET @Today=convert(char(10),getdate(),120)

IF EXISTS(select CURSOR_NAME from MASTER.dbo.syscursors where cursor_name='cursor_outer')

BEGIN

DEALLOCATE cursor_outer

END

DECLARE cursor_outer CURSOR FOR

select distinct m.clientIP, MES_COMQuality from #tempTable m

--where MES_COMQuality > 0

--where convert(char(10), m.endcycle,120)=@Today

OPEN cursor_outer

FETCH next FROM cursor_outer INTO @IPAddr,@MES_COMQuality --@DispatchOrder,@IPAddr,@EndCycle

while @@fetch_status=0

BEGIN

select @ReasonName='',@MO='', @DispatchNO='',@CardEmployee = '', @StopBegin = null

select top 1 @MO = MO,

@DispatchNO = DispatchNO,

@DispatchPrior = DispatchPrior,

@EndCycle=EndCycle, @Cycle=isnull(CycleTime,0.00)

, @BeginCycle = convert(char(19),EndCycle,121) , @Totalnum = Totalnum

from MES_DataMain

where clientip = @IPAddr

and convert(char(10),endcycle,120)<=@Today

order by EndCycle desc --最后结束时间 ,生产周期

--select top 1 @StandCycle=isnull(StandCycle,0.00),@proQty=prodqty, @ItemNO=ItemNO,

--良品数=生产数加上调机时的生产数再减去巡机数 2014.7.22 hz

select top 1 @StandCycle=isnull(StandCycle,0.00),

@proQty=prodqty+dbo.FN_GetAdjustMachineQty(Mo,DispatchNo,ItemNO,DispatchPrior, '', '','',ProcCode)-

dbo.FN_GetPatrolMachineOrPolishQty(MO,DispatchNo,ItemNO,ProcCode,DispatchPrior,'', '',1),

@ItemNO=ItemNO,

@MinInjectionCycle=ISNULL(MinCycle,0.00),

@MaxInjectionCycle=ISNULL(MaxCycle,0.00), @MouldNO = MouldNO, @MOType = isnull(MOType,1),

@DispatchStatus = DispatchStatus

from MES_Dispatchorder

where MO = @MO and DispatchNO = @DispatchNO and DispatchPrior = @DispatchPrior

order by ID

set @txt_Unknow = @Unknown

--最后一个单子不是主单则重新获取主单信息

if(@DispatchStatus <> 1)

begin

select @MachineNO = machineno from MES_Communication where IPAddr = @IPAddr

--select top 1 @StandCycle=isnull(StandCycle,0.00),@proQty=prodqty, @ItemNO=ItemNO,

--良品数=生产数加上调机时的生产数再减去巡机数 2014.7.22 hz

select top 1 @StandCycle=isnull(StandCycle,0.00),

@proQty=prodqty+dbo.FN_GetAdjustMachineQty(Mo,DispatchNo,ItemNO,DispatchPrior, '', '','',ProcCode)-

dbo.FN_GetPatrolMachineOrPolishQty(MO,DispatchNo,ItemNO,ProcCode,DispatchPrior,'', '',1), @ItemNO=ItemNO,

@MinInjectionCycle=ISNULL(MinCycle,0.00),

@MaxInjectionCycle=ISNULL(MaxCycle,0.00), @MouldNO = MouldNO, @MOType = isnull(MOType,1),

@DispatchStatus = DispatchStatus

from MES_Dispatchorder

where MachineNO = @MachineNO and DispatchStatus = 1

order by ID

end

if (@MOType = 2) set @txt_Unknow = @txt_SM

---连接中断也显示派工单信息

if @MES_COMQuality = 0

begin

update #tempTable set MO = @MO,DispatchNo = @DispatchNO, DispatchPrior = @DispatchPrior

where ClientIP=@IPAddr

FETCH NEXT FROM cursor_outer INTO @IPAddr, @MES_COMQuality

continue

end

if @DispatchNO not like 'TMP%' and @DispatchNO <> ''

begin --------派工单号为??????号时 ---注塑车间改为固定的100秒

if exists(select top 1 clientIP from MES_DataMain a

join MES_Machine b on a.MachineNo = b.MachineNO

left join MES_Process p on a.ProcCode = p.ProcCode

where datediff(second,case when abs(datediff(year,a.EndCycle,GETDATE()))>1 then GETDATE() else a.EndCycle end,getdate())>

case when isnull(@StandCycle,1)*3<90 then 90 else isnull(@StandCycle,1)*3 end

and a.MO = @MO and a.DispatchNO =@DispatchNO and a.DispatchPrior = @DispatchPrior and a.clientIP=@IPAddr

order by a.EndCycle desc) --系统时间大于最后生产时间+(3模*周期或周期小于90秒，则取90秒) 则停机

begin

select @MaxUploadDate = max(updatetime) from MES_DataMain where ClientIP = @IPAddr

if datediff(minute, isnull(@MaxUploadDate,getdate()), getdate())<2 and @MaxUploadDate is not null

and datediff(minute, @EndCycle, @MaxUploadDate)>2

begin

update #tempTable set DeviceStatus='Normal', MES_COMQuality = 5 , ItemNO = @ItemNO

, StopReason = '补数', MO = @MO, DispatchNo = @DispatchNO, DispatchPrior = @DispatchPrior ,MouldNO = @MouldNO

, GoodQty = @proQty--dbo.FN_GetGoodQty(@MO, @DispatchNO, @ItemNO, '', @DispatchPrior,'','')

where ClientIP=@IPAddr

end

else

begin

select top 1 @ReasonName=case @in_Lan when 'zh_cn' then c.Lan_CN when 'en' then c.Lan_EN else '' end ,

@StopBegin= a.StartDate, @CardEmployee = dbo.FN_GetEmpNameByID(case when isnull(a.StartEmpID,'')<>'' then a.StartEmpID else a.StartCardID end )

from #t_StopCardDetail a

join Sys_PubCode b on b.ModuleCode = 'All' and b.FieldName = 'FuncID' and a.CardType = b.FieldValue

left join Sys_Language c on c.KeyName = b.KeyName

WHERE a.ClientIP = @IPAddr

order by a.ID desc

set @StopTime1 = -1

if @ReasonName <> ''

begin

select top 1 @StopTime=datediff(MINUTE,@StopBegin, GETDATE())

end

else

begin

select top 1 @StopTime=datediff(ss,case when abs(datediff(year,EndCycle,GETDATE()))>1 then GETDATE()

else EndCycle end,getdate())/60.0 , @StopBegin = EndCycle

from MES_DataMain

where Clientip=@IPAddr

order by EndCycle desc

---精确"未刷卡停机"停机时间扣减中间正常停机时间 add by wuchun 0505

--begin

select @StopTime1 = datediff(ss,MAX(EndDate),GETDATE())/60.0

from V_CardDetail a

WHERE a.Flag = 1 and ClientIP = @IPAddr

if(@StopTime>@StopTime1 and @StopTime1>0)

set @StopTime = @StopTime1

--end

end

update #tempTable set DeviceStatus='Stop' ,LiveCycle='0.00',GoodQty='0',BeginCycle=''

, StopReason=case when @ReasonName = '' and StopReason = '' then @txt_Unknow

when @ReasonName <> '' then @ReasonName else @txt_Unknow end

, CardEmployee = @CardEmployee, StopBegin = @StopBegin

, StopTime = dbo.Fn_GetMinuteToDH(isnull(@StopTime,0))

, MO = @MO, DispatchNo = @DispatchNO, DispatchPrior = @DispatchPrior

,MouldNO = @MouldNO

where ClientIP=@IPAddr

end

end

else

begin

if(((@Cycle>@MaxInjectionCycle) or (@Cycle<@MinInjectionCycle)) and @MOType = 1) -----在异常生产的

begin

update #tempTable set DeviceStatus='AbNormal', MO = @MO, DispatchNo=@DispatchNO, DispatchPrior = @DispatchPrior

, LiveCycle=@Cycle, GoodQty = @proQty --dbo.FN_GetGoodQty(@MO, @DispatchNO, @ItemNO, '', @DispatchPrior,'','')

, BadQty = 0--dbo.FN_GetBadQty(@MO, @DispatchNO, @ItemNO,'', @DispatchPrior, '','')

, BeginCycle = @BeginCycle, StopReason=@AbNormalProduct,MES_COMQuality = 5

, MouldNO = @MouldNO , ItemNO = @ItemNO

where ClientIP=@IPAddr

end

else ----在正常生产的

begin

update #tempTable set DeviceStatus='Normal', MO = @MO, DispatchNo = @DispatchNO, DispatchPrior = @DispatchPrior

, LiveCycle=@Cycle, GoodQty = @proQty--dbo.FN_GetGoodQty(@MO, @DispatchNO, @ItemNO, '', @DispatchPrior,'','')

, BadQty = 0--dbo.FN_GetBadQty(@MO, @DispatchNO, @ItemNO,'', @DispatchPrior, '','')

, BeginCycle = @BeginCycle, StopReason= case when @MOType = 2 then @txt_SM else @RegularProduct end, MES_COMQuality = 5

, MouldNO = @MouldNO, ItemNO = @ItemNO

where ClientIP=@IPAddr

end

end

end

else

begin

--print @IPAddr

if exists(select top 1 clientIP from MES_DataMain a

left join MES_Machine b on a.MachineNo = b.MachineNO

left join MES_Process p on a.ProcCode = p.ProcCode

where datediff(second,case when abs(datediff(year,a.EndCycle,GETDATE()))>1 then GETDATE() else a.EndCycle end,getdate())

<=case when isnull(@StandCycle,1)*3<90 then 90 else isnull(@StandCycle,1)*3 end

and a.MO = @MO and a.DispatchNO =@DispatchNO and a.DispatchPrior = @DispatchPrior and a.clientIP=@IPAddr

order by a.EndCycle desc) --(系统时间-最好生产时间）小于(3模*周期或90秒)

begin

update #tempTable set DeviceStatus='NOOrder', DispatchNO = @DispatchNO, LiveCycle=@Cycle,GoodQty='0', BeginCycle=@BeginCycle, MES_COMQuality = 5

, StopReason = @txt_NOOrder

where ClientIP=@IPAddr

end

else

begin

--print @IPAddr

select @MaxUploadDate = max(updatetime) from MES_DataMain where ClientIP = @IPAddr

if datediff(minute, isnull(@MaxUploadDate,getdate()), getdate())<2 and @MaxUploadDate is not null

and datediff(minute, @EndCycle, @MaxUploadDate)>2

begin

update #tempTable set DeviceStatus='Normal', MES_COMQuality = 5 ,MouldNO = @MouldNO, ItemNO = @ItemNO

, StopReason = '补数', MO = @MO, DispatchNo = @DispatchNO, DispatchPrior = @DispatchPrior

, GoodQty = @proQty--dbo.FN_GetGoodQty(@MO, @DispatchNO, @ItemNO, '', @DispatchPrior,'','')

where ClientIP=@IPAddr

end

else

begin

select top 1 @ReasonName=case @in_Lan when 'zh_cn' then c.Lan_CN when 'en' then c.Lan_EN else '' end ,

@StopBegin= a.StartDate,@CardEmployee = isnull((select top 1 EmpNameCn from MES_Employee where ICCardID = a.StartCardID and state = 1),a.StartCardID)

from #t_StopCardDetail a

join Sys_PubCode b on b.ModuleCode = 'All' and b.FieldName = 'FuncID' and a.CardType = b.FieldValue

left join Sys_Language c on c.KeyName = b.KeyName

WHERE a.ClientIP = @IPAddr

order by a.ID desc

set @StopTime =-1

if @ReasonName <> ''

begin

select top 1 @StopTime=datediff(MINUTE,@StopBegin, GETDATE())

end

else

begin

select top 1 @StopTime=datediff(ss,case when abs(datediff(year,EndCycle,GETDATE()))>1 then GETDATE() else EndCycle end,getdate())/60.0 , @StopBegin = EndCycle from MES_DataMain

where Clientip=@IPAddr

order by EndCycle desc

---精确"未刷卡停机"停机时间扣减中间正常停机时间 add by wuchun 0505

--begin

select @StopTime1 = datediff(ss,MAX(EndDate),GETDATE())/60.0

from V_CardDetail a

WHERE a.Flag = 1 and ClientIP = @IPAddr

if(@StopTime>@StopTime1 and @StopTime1>0)

set @StopTime = @StopTime1

--end

end

update #tempTable set DeviceStatus='Stop', DispatchNO = @DispatchNO,

LiveCycle=@Cycle,GoodQty='0', BeginCycle=@BeginCycle, MES_COMQuality = 5

, StopReason = case when @ReasonName = '' and StopReason = '' then @txt_Unknow

when @ReasonName <> '' then @ReasonName

else @txt_Unknow

end

, StopBegin = @StopBegin

, StopTime = dbo.Fn_GetMinuteToDH(isnull(@StopTime,0)), CardEmployee = @CardEmployee

, MO = @MO, DispatchPrior = @DispatchPrior

where ClientIP=@IPAddr

end

end

end

FETCH NEXT FROM cursor_outer INTO @IPAddr, @MES_COMQuality -- @DispatchOrder,@IPAddr,@EndCycle

END

CLOSE cursor_outer

DEALLOCATE cursor_outer

declare @row_from int, @row_to int

 

--输入停机原因需重新分页

declare @MachineTotal int

if(@in_MachineState <> '' and @in_MachineState <> 'All')

begin

update #tempTable set BadQty =b.BadQty from (select isnull(sum(d.BadQty),0) BadQty,t.MachineNo from #tempTable t

left join MES_QC q on t.DispatchNO=q.DispatchNO and t.DispatchPrior=q.DispatchPrior and q.ItemNO=t.ItemNO

left join mes_qcdetail d on q.qcbillno=d.qcbillno

left join Sys_PubCode sp on d.badreasonid=sp.FieldValue and fieldname='ddl_BadReason'

where IsBad=1 and

(@in_MachineState = '' or @in_MachineState = 'All' or DeviceStatus =@in_MachineState)

and RowSeq between @row_from and @row_to

group by RowSeq,t.MachineNo,t.DeviceStatus,t.WSCode,t.MO,t.DispatchNO,t.DispatchPrior,t.LiveCycle,t.GoodQty,t.ClientIP

,t.BeginCycle,StopReason,StopTime,t.MouldNO,t.ItemNO,MES_COMQuality,CardEmployee,StopBegin) b join #tempTable t on t.machineno=b.machineno

select RowSeq = identity(int), MachineNo, DeviceStatus, WSCode, MO, DispatchNO, MouldNO

,DispatchPrior, LiveCycle

--只更新当前页良品数 ：优化性能add by wuchun on 2014/01/02

, GoodQty = case when DeviceStatus = 'Normal' or deviceStatus = 'abNormal' then GoodQty-BadQty else 0 end

, BadQty, ClientIP, BeginCycle, StopReason , StopTime,ItemNO, CardEmployee, StopBegin

, MES_COMQuality,RepairStatus

into #Result

from #tempTable

where DeviceStatus =@in_MachineState

order by RowSeq

execute st_MES_RS_Pages @out_total_rows, @in_pagesize, @in_page, @row_from output, @row_to output

select *

from #Result

where RowSeq between @row_from and @row_to

order by RowSeq

select @out_total_rows = count(*) from #Result

select @MachineTotal = count(*) from #tempTable

end

else

begin

/* pages */

select @out_total_rows = count(*) from #tempTable

set @MachineTotal = @out_total_rows

if(@in_MachineState = '' or @in_MachineState = 'All')

execute st_MES_RS_Pages @out_total_rows, @in_pagesize, @in_page, @row_from output, @row_to output

else

select @row_from = 0, @row_to = @out_total_rows

update #tempTable set BadQty =b.BadQty from (select isnull(sum(d.BadQty),0) BadQty,t.MachineNo from #tempTable t

left join MES_QC q on t.DispatchNO=q.DispatchNO and t.DispatchPrior=q.DispatchPrior and q.ItemNO=t.ItemNO

left join mes_qcdetail d on q.qcbillno=d.qcbillno

left join Sys_PubCode sp on d.badreasonid=sp.FieldValue and fieldname='ddl_BadReason'

where IsBad=1 and

(@in_MachineState = '' or @in_MachineState = 'All' or DeviceStatus =@in_MachineState)

and RowSeq between @row_from and @row_to

group by RowSeq,t.MachineNo,t.DeviceStatus,t.WSCode,t.MO,t.DispatchNO,t.DispatchPrior,

t.LiveCycle,t.GoodQty,t.ClientIP

,t.BeginCycle,StopReason,StopTime,t.MouldNO,t.ItemNO,MES_COMQuality,CardEmployee,StopBegin) b join #tempTable t on t.machineno=b.machineno

--只更新当前页良品数 ：优化性能add by wuchun on 2014/01/02

update #tempTable set GoodQty = case when DeviceStatus = 'Normal' or deviceStatus = 'abNormal' then GoodQty-BadQty else 0 end

where (@in_MachineState = '' or @in_MachineState = 'All' or DeviceStatus =@in_MachineState)

and RowSeq between @row_from and @row_to

select * from #tempTable

where (@in_MachineState = '' or @in_MachineState = 'All' or DeviceStatus =@in_MachineState)

and RowSeq between @row_from and @row_to

order by RowSeq

end

select @Normal_Count =0, @abNormal_Count = 0, @Stop_Count = 0, @NONetWork_Count = 0, @NoOrder_count = 0

select @Normal_Count = case when DeviceStatus = 'Normal' then @Normal_Count + 1 else @Normal_Count end

, @abNormal_Count = case when DeviceStatus = 'abNormal' then @abNormal_Count + 1 else @abNormal_Count end

, @Stop_Count = case when DeviceStatus = 'Stop' then @Stop_Count + 1 else @Stop_Count end

, @NoOrder_count = case when DeviceStatus = 'NoOrder' then @NoOrder_count + 1 else @NoOrder_count end

, @NONetWork_Count = case when DeviceStatus = 'NONetWork' then @NONetWork_Count + 1 else @NONetWork_Count end

from #tempTable

select TotalCount = isnull(@out_total_rows,0), NormalCount = @Normal_Count, abNormal_Count = @abNormal_Count,

Stop_Count = @Stop_Count, NoOrder_count = @NoOrder_count, NONetWork_Count = @NONetWork_Count, MachineTotal = @MachineTotal

drop table #tempTable

set nocount off

end

GO