函数与复杂的存储过程-白红宇

函数与复杂的存储过程

阅读量：5022 次

发布时间：2019-06-12

本文共 47397 字，大约阅读时间需要 157 分钟。

USE [ChiefmesNew]

/****** Object: UserDefinedFunction [dbo].[FN_GetIsolationQty] Script Date: 10/19/2015 13:48:18 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

-- creator: hz

-- create date: 2015.8.5

-- Module: Public

-- Description: 返回派工单隔离总数

select dbo.FN_GetIsolationQty('2014051602','20140516020001','text01','P01',0,'2014-05-11 08:00','2014-05-19 20:00')

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

ALTER FUNCTION [dbo].[FN_GetIsolationQty](

@MO varchar(20),

@DispatchNO varchar(50),

@ItemNO varchar(20),

@ProcCode varchar(20) = '',

@DispatchPrior float,

@BeginDate char(19),

@EndDate char(19)

)

RETURNS int

BEGIN

declare @BadQty int

if(@BeginDate = '' and @EndDate = '')

begin

set @BeginDate='1900-01-01'

set @EndDate = convert(varchar(19),GETDATE(),121)

end

select @BadQty = sum(d.BadQty)

from MES_QC q

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

inner join Sys_PubCode sp on d.badreasonid=sp.FieldValue

where DispatchNO = @DispatchNO

and (@DispatchPrior = 0 or DispatchPrior = @DispatchPrior)

and ItemNO = @ItemNO

and QCDate between @BeginDate and @EndDate

and (@ProcCode = '' or ProcCode = @ProcCode)

and fieldname='ddl_BadReason'

and sp.IsBad=1

and q.BillType=1

return isnull(@BadQty,0)

END

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

USE [ChiefmesNew]

/****** Object: StoredProcedure [dbo].[st_MES_RptProductForDay] Script Date: 10/19/2015 09:51:52 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

--AUTHOR: hz

--CREATE DATE: 2014/5/15

--DESCRIPTION: 生产日报

--update by fsq 2014/07/17

--Remark:优化班次显示问题

--update by 2014.9.1 hz 添加开始读数，结束读数，度数，电表倍率字段

--update by 2015-5-20绑定原料,批号等数据

--update by hz 2015-08-05

--Remark:添加隔离数显示和良品数-生产数-次品数-隔离数 IsolationNum

--exec st_MES_RptProductForDay '','','','91','2015-10-16','2015-10-16',200,1

--exec st_MES_RptProductForDay '810611229','','','1000T4#','2015-10-16','2015-10-16',200,1

-- =============================================

ALTER PROCEDURE [dbo].[st_MES_RptProductForDay]

(

@ProductNo varchar(30),

@ProductName varchar(500),

@StaCode varchar(20),

@MachineNo varchar(50),

@BeginDate varchar(20),

@EndDate varchar(20),

@in_pagesize int = -1,

@in_page int = 1,

@out_total_rows int = 1 output

)

--第一部分，基础数据来源。

if object_id('tempdb..#tmpDispatchOrder') is not null drop table #tmpDispatchOrder

create table #tmpDispatchOrder

(

--RowSeq int identity(1,1),

MO varchar(50),

DispatchNo varchar(50),

DispatchPrior float,

ProductNo varchar(50),

MachineNo varchar(50),

ProcCode varchar(20),

StaCode varchar(10),

currDate varchar(10),

Bc varchar(10),

BcCode varchar(10),

BCBeginTime varchar(19),

BCEndTime varchar(19),

ProductName varchar(2000),

MaterialNo varchar(50),

BatchNo varchar(100),

DispatchNum int,

FactStartDate varchar(20),

FactStopDate varchar(20),

StandCycle decimal(18,2),

StandEmployee int,

SocketNum int,

MoType int,

ClassNum int, --班次计划数

ProductNum int ,

GoodNum int,--实际合格数

BadNum int,

IsolationNum int, --隔离数

WorkTime decimal(18,4),

NoCardTime decimal(18,4),

TestModeTime decimal(18,4),

StopTime decimal(18,4),

PatrolMachineNum int,

AdjustMacNum int,

PolishNum int,

BadRate varchar(100),

BadReason varchar(8000),

StartReading varchar(20),

EndReading varchar(20),

Degreeses int,

MeterRate int,

Flag int,

ReProductNum int

)

declare @r_BeginDate varchar(19), @r_EndDate varchar(19)

select @r_BeginDate = CONVERT(varchar(10),@BeginDate,121)+ ' 07:30:00'

select @r_EndDate = CONVERT(varchar(10),dateadd(day,1,@EndDate),121)+ ' 07:29:59'

--获取有生产的派工单

insert into #tmpDispatchOrder(MO, DispatchNo, DispatchPrior, ProductNo,MachineNo,

ProcCode,StaCode, ProductName, BatchNo, DispatchNum, BadNum,IsolationNum, FactStartDate, FactStopDate

, StandCycle, StandEmployee, SocketNum,MoType,flag,MaterialNo)

select distinct a.MO, a.DispatchNo, a.DispatchPrior, a.ItemNo,a.MachineNO,a.ProcCode,b.StaCode,c.ItemName,m.BatchNO,

a.DispatchQty, 0,0,convert(char(19),a.ActualStartDate,121), convert(char(19),a.ActualEndDate,121)

, a.StandCycle, c.StandEmployee, case a.SocketNum when 0 then 1 else a.SocketNum end SocketNum ,a.MOType,0,m.MaterialNO

from V_DispatchOrder a

inner join mes_machine b on a.MachineNo=b.MachineNo

left join mes_Item c on a.ItemNo=c.ItemNo

left join mes_MouldDetail d on a.MouldNo=d.MouldNo

left join (select DispatchNo,MaterialNO,BatchNO from MES_Material b where b.ID=(select ID=MAX(ID) from MES_Material

where DispatchNo=b.DispatchNO group by DispatchNO) ) m on a.DispatchNo=m.DispatchNO

where dispatchStatus>0

and ((ActualStartDate between @r_BeginDate and @r_EndDate )

OR (ActualEndDate between @r_BeginDate and @r_EndDate )

OR (ActualStartDate <= @r_BeginDate and ActualEndDate >= @r_EndDate))

and (@StaCode = '' or a.StaCode in(select StaCode from dbo.FN_GetStaCode(@StaCode)))

and (@MachineNo = '' or a.MachineNO like '%'+@MachineNo+'%')

and (@ProductNo = '' or a.ItemNO like '%'+@ProductNo+'%')

--中间表有生成但派工单表已经删除的单

insert into #tmpDispatchOrder(MO, DispatchNo, DispatchPrior, ProductNo,MachineNo, StaCode, ProductName,Flag)

select j.MO, j.DispatchNo, j.DispatchPrior, j.ItemNo,j.MachineNo, j.StaCode,j.itemName,0

from JM_Interface j

left join #tmpDispatchOrder d on j.DispatchNO=d.DispatchNo and d.DispatchPrior=j.DispatchPrior

where d.DispatchNO is null

and ((BeginDate between @r_BeginDate and @r_EndDate )

OR (EndDate between @r_BeginDate and @r_EndDate )

OR (BeginDate <= @r_BeginDate and EndDate >= @r_EndDate))

and (@StaCode = '' or j.StaCode in(select StaCode from dbo.FN_GetStaCode(@StaCode)))

and (@MachineNo = '' or j.MachineNO like '%'+@MachineNo+'%')

and (@ProductNo = '' or j.ItemNO like '%'+@ProductNo+'%')

----获取没有生产，但刷了卡的派工单

-- insert into #tmpDispatchOrder(MO, DispatchNo, DispatchPrior, ProductNo,MachineNo, ProcCode,StaCode, ProductName, BatchNo, DispatchNum, BadNum, FactStartDate, FactStopDate

-- , StandCycle, StandEmployee, SocketNum,MoType,flag)

--select distinct a.MO, a.DispatchNo, a.DispatchPrior, a.ItemNo,a.MachineNO,a.ProcCode,b.StaCode,c.ItemName, a.LotNO, a.DispatchQty, 0, convert(char(19),a.ActualStartDate,121), convert(char(19),a.ActualEndDate,121)

-- , a.StandCycle, c.StandEmployee, case a.SocketNum when 0 then 1 else a.SocketNum end SocketNum ,a.MOType,-1

-- from V_DispatchOrder a

-- join V_CardDetail q on a.dispatchno=q.dispatchno and a.dispatchprior=q.dispatchprior

-- inner join mes_machine b on a.MachineNo=b.MachineNo

-- left join mes_Item c on a.ItemNo=c.ItemNo

-- left join #tmpDispatchOrder d on a.dispatchno=d.dispatchno and a.dispatchprior=d.dispatchprior

--where d.dispatchno is null and ((q.StartDate between @r_BeginDate and @r_EndDate )

-- OR (q.EndDate between @r_BeginDate and @r_EndDate )

-- OR (q.StartDate <= @r_BeginDate and q.EndDate >= @r_EndDate))

--and (@StaCode = '' or a.StaCode in(select StaCode from dbo.FN_GetStaCode(@StaCode)))

-- and (@MachineNo = '' or a.MachineNO like '%'+@MachineNo+'%')

-- and (@ProductNo = '' or a.ItemNO like '%'+@ProductNo+'%')

if object_id('tempdb..#tmpDispatchBc') is not null drop table #tmpDispatchBc

create table #tmpDispatchBc

(

MO varchar(50),

DispatchNo varchar(50),

DispatchPrior float,

ProductNo varchar(50),

ProcCode varchar(20),

currDate varchar(10),

Bc varchar(10),

BcCode varchar(10),

BCBeginTime varchar(20),

BCEndTime varchar(20),

ProdBeginTime datetime,

ProdEndTime datetime,

CycleTime decimal(10,2),

Flag int

)

if object_id('tempdb..#NoCardTime') is not null drop table #NoCardTime

create table #NoCardTime

(

mo varchar(20),

dispatchno varchar(20),

machineno varchar(520),

currDate varchar(10),

BCCode varchar(10),

Bc varchar(10),

BCBeginTime varchar(20),

BCEndTime varchar(20),

TotalTime decimal(18,4),

NoCardTime decimal(18,4),

StopTime decimal(18,4),

TestModeTime decimal(18,4)

)

declare @t_CurDate varchar(10)

select @t_CurDate = CONVERT(varchar(10),convert(datetime,@BeginDate),121)

while(@t_CurDate<=convert(varchar(10),convert(datetime,@EndDate),121))

begin

--未刷卡停机

insert into #tmpDispatchBc(MO, DispatchNo,DispatchPrior,ProductNo,ProcCode, currDate,Bc, BcCode,BCBeginTime,BCEndTime, Flag )

select MO, DispatchNo,DispatchPrior,ProductNo,ProcCode,@t_CurDate,'白班','AC',@t_CurDate+' 07:30:00',@t_CurDate+' 19:59:59',flag

from #tmpDispatchOrder

insert into #tmpDispatchBc(MO, DispatchNo,DispatchPrior,ProductNo,ProcCode,currDate,Bc, BcCode,BCBeginTime,BCEndTime,Flag)

select MO, DispatchNo,DispatchPrior,ProductNo,ProcCode,@t_CurDate,'晚班','BC',@t_CurDate+' 20:00:00',CONVERT(varchar(10),dateadd(day,1,convert(datetime,@t_CurDate)),121)+' 07:29:59',flag

from #tmpDispatchOrder

insert into #NoCardTime(MachineNO, BCCode,Bc,CurrDate,BCBeginTime,BCEndTime, MO, DispatchNO)

select MachineNo, 'BC','晚班', @t_CurDate ,@t_CurDate+' 20:00:00',CONVERT(varchar(10),dateadd(day,1,convert(datetime,@t_CurDate)),121)+' 07:29:59', '', ''

from V_Machine where (@MachineNo = '' or MachineNO like '%'+@MachineNo+'%')

union

select MachineNo, 'AC','白班', @t_CurDate ,@t_CurDate+' 07:30:00',@t_CurDate+' 19:59:59', '', ''

from V_Machine where (@MachineNo = '' or MachineNO like '%'+@MachineNo+'%')

set @t_CurDate = CONVERT(varchar(10),dateadd(day,1,convert(datetime,@t_CurDate)),121)

end

--如果当前时间是白班，删除晚班的记录，因为还没到晚班

delete from #NoCardTime where BCBeginTime >GETDATE()

delete from #tmpDispatchBc where BCBeginTime >GETDATE()

insert into #tmpDispatchOrder(Mo,MoType,DispatchNo,DispatchPrior,ProductNo,ProductName,MachineNo,ProcCode,StaCode,

currDate,Bc,BcCode,StandCycle,SocketNum,MaterialNo,BatchNo,BCBeginTime,BCEndTime,flag,DispatchNum)

select Mo,MoType,DispatchNo,DispatchPrior,ProductNo,ProductName,MachineNo,ProcCode,a.StaCode,currDate,Bc,BcCode,

StandCycle,SocketNum, a.MaterialNO,a.BatchNO,BCBeginTime,BCEndTime, case when flag=0 then 1 else -2 end flag,DispatchNum

from (

select distinct a.Mo,MoType,a.DispatchNo,a.DispatchPrior,a.ProductNo,b.ProductName,

b.MachineNo,b.ProcCode,b.StaCode,a.currDate,a.Bc,c.BcCode,b.StandCycle,SocketNum=b.SocketNum,

convert(varchar(19),a.BCBeginTime,121) BCBeginTime,convert(varchar(19),a.BCEndTime,121) BCEndTime

,b.MaterialNO

,b.BatchNO,a.flag,b.DispatchNum

from #tmpDispatchBc a join MES_BCDetail c on a.BcCode=c.BcCode

inner join #tmpDispatchOrder b on a.DispatchNo=b.DispatchNo and a.DispatchPrior=b.DispatchPrior and a.ProductNo=b.ProductNo) a

order by MachineNo, currDate,bc,DispatchNo desc,ProductNo desc

--更新没有生产的派工单

update d set ProductNum = dbo.FN_GetProdQty(Mo,DispatchNo,ProductNo,ProcCode,DispatchPrior, BCBeginTime,BCEndTime)+dbo.FN_GetAdjustMachineQty(Mo,DispatchNo,ProductNo,DispatchPrior, BCBeginTime, BCEndTime,'',ProcCode),

BadNum = dbo.FN_GetBadQty(Mo,DispatchNo,ProductNo,ProcCode,DispatchPrior, BCBeginTime, BCEndTime),

IsolationNum=dbo.FN_GetIsolationQty(Mo,DispatchNo,ProductNo,ProcCode,DispatchPrior, BCBeginTime, BCEndTime),--隔离数

PatrolMachineNum=dbo.FN_GetPatrolMachineOrPolishQty(MO,DispatchNo,ProductNo,ProcCode,DispatchPrior,BCBeginTime, BCEndTime,2),

ReProductNum=ISNULL(DispatchNum,0)-(dbo.FN_GetProdQty(Mo,DispatchNo,ProductNo,ProcCode,DispatchPrior, '','')+dbo.FN_GetAdjustMachineQty(Mo,DispatchNo,ProductNo,DispatchPrior, '', '','',ProcCode)),

flag=2

from #tmpDispatchOrder d

where d.flag=-2

--20151019

--select 'ATest1'

--select IsolationNum,flag,* from #tmpDispatchOrder

--20151019

delete from #tmpDispatchOrder where ProductNum=0 and BadNum=0 and flag=2

--更新中间表已经生成的数据

update d set ProductNum = prodqty,

BadNum = badqty,

--IsolationNum=j.GoodProdQty,

--20151019

IsolationNum=dbo.FN_GetIsolationQty(d.MO,d.DispatchNo,d.ProductNo,d.ProcCode,d.DispatchPrior,d.BCBeginTime,d.BCEndTime),--隔离数

WorkTime = prodtime,

TestModeTime=TestMouldTime,

d.StopTime = j.StopTime,

PatrolMachineNum=xunjiqty

,polishNum=damoqty,flag=2

from #tmpDispatchOrder d join JM_Interface j

on d.dispatchno=j.dispatchno and d.dispatchprior=j.dispatchprior and d.currDate=j.currDate and d.BcCode=j.BcCode

where d.flag=1

--20151019

--select 'ATest2'

--select IsolationNum,flag,* from #tmpDispatchOrder

--20151019

BadNum = dbo.FN_GetBadQty(Mo,DispatchNo,ProductNo,ProcCode,DispatchPrior, BCBeginTime, BCEndTime),

IsolationNum=dbo.FN_GetIsolationQty(Mo,DispatchNo,ProductNo,ProcCode,DispatchPrior, BCBeginTime, BCEndTime),--隔离数

PatrolMachineNum=dbo.FN_GetPatrolMachineOrPolishQty(MO,DispatchNo,ProductNo,ProcCode,DispatchPrior,BCBeginTime, BCEndTime,2),

ReProductNum=DispatchNum-(dbo.FN_GetProdQty(Mo,DispatchNo,ProductNo,ProcCode,DispatchPrior, '','')+dbo.FN_GetAdjustMachineQty(Mo,DispatchNo,ProductNo,DispatchPrior, '', '','',ProcCode)),

flag=2

from #tmpDispatchOrder d

where d.flag=1 and ProductNum is null

--20151019

--select 'ATest3'

--select IsolationNum,flag,* from #tmpDispatchOrder

--20151019

--计算班次所有派工单的总时间,停机时间就计算空单的停机时间

update t1 set NoCardTime = isnull(i.NoCardTime,0.0000),totaltime=ISNULL(i.TotalTime,0)

from #NoCardTime t1

left join (select t.MachineNO,CurrDate,BCCode,BCBeginTime,BCEndTime, NoCardTime =(DATEDIFF(ss,BCBeginTime,BCEndTime)+1)/3600.0-Sum(TotalTime),Sum(TotalTime) TotalTime

from (select distinct d.MachineNO, d.BCCode,d.CurrDate,d.dispatchno,d.BCBeginTime,d.BCEndTime,TotalTime=d.WorkTime+isnull(d.StopTime,0)+d.TestModeTime

from #tmpDispatchOrder d

where d.DispatchNO <> '' and d.Flag=2) t

group by t.MachineNO,CurrDate,BCCode,BCBeginTime,BCEndTime

)i on t1.MachineNO = i.MachineNO and t1.CurrDate=i.CurrDate and t1.BCCode=i.BCCode

--更新空单的试模时间

update d set TestModeTime=TestMouldTime,d.StopTime=j.stoptime

from #NoCardTime d join JM_Interface j on d.currDate=j.currDate and d.MachineNO = j.MachineNO and d.BcCode=j.BcCode where d.DispatchNO='' and j.DispatchNO=''

--更新中间表还没有生成的派工单的数据

BadNum = dbo.FN_GetBadQty(Mo,DispatchNo,ProductNo,ProcCode,DispatchPrior, BCBeginTime, BCEndTime),

IsolationNum=dbo.FN_GetIsolationQty(Mo,DispatchNo,ProductNo,ProcCode,DispatchPrior, BCBeginTime, BCEndTime),--隔离数

AdjustMacNum = dbo.FN_GetAdjustMachineQty(Mo,DispatchNo,ProductNo,DispatchPrior, BCBeginTime, BCEndTime,'',ProcCode),

WorkTime = dbo.FN_GetProductTime(mo,DispatchNo,ProcCode,DispatchPrior,BCBeginTime, BCEndTime),

--TestModeTime=case when MoType=2 then dbo.FN_GetProductTime(mo,DispatchNo,ProcCode,DispatchPrior,BCBeginTime, BCEndTime) else 0 end,

TestModeTime=dbo.FN_GetTestModeTime(Mo,DispatchNo,ProcCode,DispatchPrior,BCBeginTime, BCEndTime),

StopTime = dbo.FN_GetStopTime(Mo,DispatchNo,ProcCode,DispatchPrior, BCBeginTime, BCEndTime),

PatrolMachineNum=dbo.FN_GetPatrolMachineOrPolishQty(MO,DispatchNo,ProductNo,ProcCode,DispatchPrior,BCBeginTime, BCEndTime,2)

,polishNum=dbo.FN_GetPolishQty(MO,DispatchNo,ProductNo,ProcCode,DispatchPrior,BCBeginTime, BCEndTime),

ReProductNum=DispatchNum-(dbo.FN_GetProdQty(Mo,DispatchNo,ProductNo,ProcCode,DispatchPrior, '','')+dbo.FN_GetAdjustMachineQty(Mo,DispatchNo,ProductNo,DispatchPrior, '', '','',ProcCode)),

flag=2

from #tmpDispatchOrder d

where d.flag=1 and GETDATE() between BCBeginTime and BCEndTime

update t1 set totaltime=ISNULL(i.TotalTime,0),TestModeTime=dbo.FN_GetStopReasonTimeByMach2(t1.MachineNO,44, t1.BCBeginTime, t1.BCEndTime), StopTime=dbo.FN_GetStopTimeByMachine2(t1.MachineNO, t1.BCBeginTime, t1.BCEndTime)

from #NoCardTime t1

left join (select t.MachineNO,CurrDate,BCCode,BCBeginTime,BCEndTime,Sum(TotalTime) TotalTime

from (select distinct MachineNO, BCCode,CurrDate,dispatchno,BCBeginTime,BCEndTime, TotalTime=WorkTime+StopTime+TestModeTime

from #tmpDispatchOrder

where isnull(DispatchNO,'') <> '' and flag=2 and GETDATE() between BCBeginTime and BCEndTime) t

group by t.MachineNO,CurrDate,BCCode,BCBeginTime,BCEndTime

)i on t1.MachineNO = i.MachineNO and t1.CurrDate=i.CurrDate and t1.BCCode=i.BCCode where GETDATE() between t1.BCBeginTime and t1.BCEndTime

update #NoCardTime set StopTime=dbo.FN_GetStopTimeByMachine3(MachineNO, BCBeginTime, BCEndTime),TestModeTime=dbo.FN_GetStopReasonTimeByMach(MachineNO,44, BCBeginTime, BCEndTime) where totaltime=0.0000 and GETDATE() between BCBeginTime and BCEndTime

insert into #tmpDispatchOrder(MachineNO, BCCode,Bc, CurrDate, MO, DispatchNO,NoCardTime,TestModeTime,StopTime,BCBeginTime,BCEndTime,flag) select MachineNO, BCCode,Bc, CurrDate, MO, DispatchNO,12.0000-TotalTime-StopTime-TestModeTime,TestModeTime,StopTime,BCBeginTime,BCEndTime,2 from #NoCardTime

if object_id('tempdb..#tmpDispatchOrder1') is not null drop table #tmpDispatchOrder1

create table #tmpDispatchOrder1

(

RowSeq int identity(1,1),

MO varchar(50),

DispatchNo varchar(50),

DispatchPrior float,

ProductNo varchar(50),

MachineNo varchar(50),

ProcCode varchar(20),

StaCode varchar(10),

currDate varchar(10),

Bc varchar(10),

BcCode varchar(10),

BCBeginTime varchar(19),

BCEndTime varchar(19),

ProductName varchar(2000),

MaterialNo varchar(50),

BatchNo varchar(100),

DispatchNum int,

FactStartDate varchar(20),

FactStopDate varchar(20),

StandCycle decimal(18,2),

StandEmployee int,

SocketNum int,

MoType int,

ClassNum int, --班次计划数

ProductNum int ,

GoodNum int,--实际合格数

BadNum int,

IsolationNum int, --隔离数

WorkTime decimal(18,4),

NoCardTime decimal(18,4),

TestModeTime decimal(18,4),

StopTime decimal(18,4),

PatrolMachineNum int,

AdjustMacNum int,

PolishNum int,

BadRate varchar(100),

BadReason varchar(8000),

StartReading varchar(20),

EndReading varchar(20),

Degreeses int,

MeterRate int,

ReProductNum int

)

insert into #tmpDispatchOrder1(MO,DispatchNo,DispatchPrior,ProductNo,MachineNo,ProcCode,StaCode,currDate,Bc,BcCode,BCBeginTime,BCEndTime, ProductName,

MaterialNo,BatchNo,DispatchNum,FactStartDate,FactStopDate,StandCycle,StandEmployee,SocketNum,MoType,ClassNum,ProductNum,

GoodNum,BadNum,IsolationNum, WorkTime,NoCardTime,TestModeTime,StopTime,PatrolMachineNum,AdjustMacNum,PolishNum,BadRate,BadReason,StartReading,EndReading,Degreeses,MeterRate,ReProductNum)

select MO,DispatchNo,DispatchPrior,ProductNo,MachineNo,ProcCode,StaCode,currDate,Bc,BcCode,BCBeginTime,BCEndTime, ProductName,

MaterialNo,BatchNo,DispatchNum,FactStartDate,FactStopDate,StandCycle,StandEmployee,SocketNum,MoType,ClassNum,ProductNum,

GoodNum,BadNum,IsolationNum, WorkTime,NoCardTime,TestModeTime,StopTime,PatrolMachineNum,AdjustMacNum,PolishNum,BadRate,BadReason,StartReading,EndReading,Degreeses,MeterRate,ReProductNum

from

#tmpDispatchOrder where Flag=2 order by MachineNo, currDate,bc,DispatchNo desc,ProductNo desc

declare @row_from int, @row_to int

select @out_total_rows = count(*) from #tmpDispatchOrder1 --where stoptime is not null

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

update #tmpDispatchOrder1 set ClassNum=CASE WHEN StandCycle = 0 THEN 0 ELSE Ceiling(CAST((WorkTime+StopTime)*3600/

CONVERT(decimal(18,2), StandCycle) *isnull(SocketNum,1) AS decimal(18,4)))END,

ProductNum=ProductNum,GoodNum=ProductNum-BadNum-IsolationNum-PatrolMachineNum

--,PatrolMachineNum=case when ProductNum>PatrolMachineNum then PatrolMachineNum else 0 end

where RowSeq between @row_from and @row_to

update #tmpDispatchOrder1 set BadRate=CASE WHEN ProductNum = 0 THEN '0.00%' ELSE rtrim(ltrim(cast(CAST(BadNum/

CONVERT(decimal(18,2),ProductNum)*100 AS decimal(10,2)) as char)))+'%' END

where RowSeq between @row_from and @row_to

declare @tMo varchar(20),@tDispatchNo varchar(50),@tDispatchPrior float,@tProductNo varchar(50),@tProcCode varchar(20),

@tcurrDate varchar(50),@tBc varchar(20),@BCBeginTime varchar(20),

@BCEndTime varchar(20),@tBeginDate varchar(20), @tEndDate varchar(20),@tBadNum int ,@tBadText varchar(8000),

@tProductNum int,@QCBillNoList varchar(8000),

@tEmpID varchar(8000),@tEmpName varchar(8000),@iEmpID varchar(10),@iBadNum int,

@iBadText varchar(8000),@iEmpName varchar(50),@iQCBillNO varchar(50),@Machine varchar(50)

declare tmpCur cursor for

select Mo,DispatchNo,DispatchPrior,ProductNo,ProcCode,currDate,BCBeginTime,BCEndTime,bc,MachineNo from #tmpDispatchOrder1 where RowSeq between @row_from and @row_to

OPEN tmpCur

FETCH next FROM tmpCur INTO @tMo, @tDispatchNo,@tDispatchPrior,@tProductNo,@tProcCode,@tcurrDate,@BCBeginTime,@BCEndTime,@tBc,@Machine

while @@fetch_status=0

begin

select @tBadNum=0,@tProductNum=0,@tBadText='',@tBeginDate='',@tEndDate='',@tEmpID='',@tEmpName='',@QCBillNoList=''

select @tBeginDate=@BCBeginTime,

@tEndDate=@BCEndTime

if object_id('tempdb..#tmpQC') is not null drop table #tmpQC

select a.QCBillNO into #tmpQC

from mes_QC a

join mes_qcdetail q on q.qcbillno=a.qcbillno

left join sys_pubcode p on p.fieldvalue=badreasonid and fieldname='ddl_BadReason'

where a.QCDate BETWEEN @tBeginDate AND @tEndDate

and a.DispatchNo=@tDispatchNo and a.ProcCode=@tProcCode and a.ItemNO=@tProductNo and a.BillType=0

declare tmpCur2 cursor for

select distinct QCBillNO from #tmpQC

open tmpCur2

fetch next from tmpCur2 into @iQCBillNO

while @@fetch_status=0

begin

set @QCBillNoList=case when @QCBillNoList='' then @QCBillNoList+@iQCBillNO else @QCBillNoList+','+@iQCBillNO end

fetch next from tmpCur2 into @iQCBillNO

end

set @tBadText=(select dbo.FN_GetQCQueryClassType(@QCBillNoList))--Add by zhuss

deallocate tmpCur2

update #tmpDispatchOrder1 set BadReason=@tBadText where DispatchNo=@tDispatchNo and ProductNo=@tProductNo and ProcCode=@tProcCode and currDate=@tcurrDate and Bc=@tBc

/*2014.9.1 hz 添加开始读数，结束读数，度数，电表倍率字段*/

declare @StartElecNum varchar(20),@EndElecNum varchar(20),@bcCount int,@nocardCount int

set @StartElecNum='F'

set @EndElecNum='F'

if not exists(select * from MES_ElecDetail a where a.MachineNo=@Machine and InputDate between @BCBeginTime and @BCEndTime and CardID='00000001')

begin

select @nocardCount= COUNT(*) from MES_ElecDetail a where a.MachineNo=@Machine and InputDate between @BCBeginTime and @BCEndTime

if(@nocardCount >1)

begin

select top 1 @StartElecNum=ElecNum from MES_ElecDetail a where a.MachineNo=@Machine and InputDate between @BCBeginTime and @BCEndTime order by InputDate

select top 1 @EndElecNum=ElecNum from MES_ElecDetail a where a.MachineNo=@Machine and InputDate between @BCBeginTime and @BCEndTime order by InputDate desc

end

else

begin

select top 1 @StartElecNum=ElecNum from MES_ElecDetail a where a.MachineNo=@Machine and InputDate between @BCBeginTime and @BCEndTime order by InputDate

end

else

begin

select top 1 @StartElecNum=ElecNum from MES_ElecDetail a where a.MachineNo=@Machine and InputDate between @BCBeginTime and @BCEndTime and CardID='00000001' order by InputDate desc

select top 1 @EndElecNum=ElecNum from MES_ElecDetail a where a.MachineNo=@Machine and InputDate between dateadd(HH,12,@BCBeginTime) and dateadd(HH,12,@BCEndTime) and CardID='00000001' order by InputDate desc

end

update d set StartReading=@StartElecNum,EndReading=@EndElecNum,Degreeses=case when @StartElecNum<>'F' and @EndElecNum<>'F'

then case when (CONVERT(float,@EndElecNum)-CONVERT(float,@StartElecNum))<0

then 0 else (CONVERT(float,@EndElecNum)-CONVERT(float,@StartElecNum)) end

end,MeterRate=80

from #tmpDispatchOrder1 d where MachineNo=@Machine and d.Bc=@tBc and d.currDate=@tcurrDate and DispatchNo=''

update #tmpDispatchOrder1 set MeterRate=null where MachineNo =@Machine and Bc=@tBc and currDate=@tcurrDate and RowSeq<(select top 1 RowSeq from #tmpDispatchOrder1 where MachineNo =@Machine and Bc=@tBc and currDate=@tcurrDate order by RowSeq desc)

/*2014.9.1 end*/

FETCH next FROM tmpCur INTO @tMo, @tDispatchNo,@tDispatchPrior,@tProductNo,@tProcCode,@tcurrDate,@BCBeginTime,@BCEndTime,@tBc,@Machine

END

CLOSE tmpCur

DEALLOCATE tmpCur

select ROW_NUMBER() OVER(ORDER BY MachineNo asc ) ID,Bc,StaCode,MachineNo,MO,DispatchNo,ProductNo,ProductName,

currDate,MaterialNo,BatchNo,ProductNum,GoodNum,BadNum,IsolationNum,

PolishNum,PatrolMachineNum,WorkTime,NoCardTime,TestModeTime,StopTime,BadRate,BadReason,StartReading,

EndReading,Degreeses,MeterRate,DispatchNum,ReProductNum

from #tmpDispatchOrder1 where RowSeq between @row_from and @row_to

--and stoptime is not null

select TotalRows = @out_total_rows

select SUM(ProductNum) from #tmpDispatchOrder1

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

USE [ChiefmesNew]

/****** Object: StoredProcedure [dbo].[ImportProductForDay] Script Date: 10/19/2015 09:44:30 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

--AUTHOR: hz

--CREATE DATE: 2014/5/15

--DESCRIPTION: 生产日报

--update by hz 2015-08-05

--Remark:添加隔离数显示和良品数-生产数-次品数-隔离数

--exec ImportProductForDay

-- =============================================

ALTER PROCEDURE [dbo].[ImportProductForDay]

declare @BeginDate varchar(20),@EndDate varchar(20),@BC varchar(10),@BeginTime varchar(20),@BcTime varchar(20)

set @BeginTime=CONVERT(varchar(19),getdate(),120)

if(@BeginTime>=CONVERT(varchar(11),@BeginTime,120)+'20:00:00' and @BeginTime<=CONVERT(varchar(11),@BeginTime,120)+'20:00:59')

begin

set @BeginTime=convert(varchar(19),dateadd(MI,1,cast(@BeginTime as datetime)),120)

end

else if(@BeginTime>=CONVERT(varchar(11),@BeginTime,120)+'07:30:00' and @BeginTime<=CONVERT(varchar(11),@BeginTime,120)+'07:30:59')

begin

set @BeginTime=convert(varchar(19),dateadd(MI,1,cast(@BeginTime as datetime)),120)

end

select @BC=dbo.Fn_GetBCCodeByTime(@BeginTime)

select @BeginDate= convert(varchar(19),convert(varchar(11),@BeginTime,120)+BeginTime,120),

@EndDate=convert(varchar(11),@BeginTime,120)+EndTime from MES_BCDetail where BcCode=@BC

if(@BeginTime>@EndDate)

begin

set @EndDate=convert(varchar(19),dateadd(day,1,cast(@EndDate as datetime)),120)

end

else if(@BC='BC')

begin

set @BeginDate=convert(varchar(19),dateadd(day,-1,cast(@BeginDate as datetime)),120)

end

--select @BeginDate

--select @EndDate

--第一部分，基础数据来源。

if object_id('tempdb..#tmpDispatchOrder') is not null drop table #tmpDispatchOrder

create table #tmpDispatchOrder

(

--RowSeq int identity(1,1),

MO varchar(50),

DispatchNo varchar(50),

DispatchPrior float,

ProductNo varchar(50),

MachineNo varchar(50),

ProcCode varchar(20),

StaCode varchar(10),

currDate varchar(10),

Bc varchar(10),

BcCode varchar(10),

BCBeginTime varchar(19),

BCEndTime varchar(19),

ProductName varchar(2000),

MaterialNo varchar(50),

BatchNo varchar(100),

DispatchNum int,

FactStartDate varchar(20),

FactStopDate varchar(20),

StandCycle decimal(18,2),

StandEmployee int,

SocketNum int,

MoType int,

ClassNum int, --班次计划数

ProductNum int ,

ProductQty int,

GoodNum int,--实际合格数

BadNum int,

IsolationNum int, --隔离数

WorkTime decimal(18,4),

NoCardTime decimal(18,4),

TestModeTime decimal(18,4),

StopTime decimal(18,4),

PatrolMachineNum int,

AdjustMacNum int,

PolishNum int,

BadRate varchar(100),

BadReason varchar(8000),

StartReading varchar(20),

EndReading varchar(20),

Degreeses int,

MeterRate int,

StrEmpName varchar(8000),

DispatchQty int,

Flag int

)

declare @r_EndDate varchar(19)

--select @r_EndDate = CONVERT(varchar(10),dateadd(day,1,@EndDate),121)+ ' 07:59:59'

insert into #tmpDispatchOrder(MO, DispatchNo, DispatchPrior, ProductNo,MachineNo, ProcCode,StaCode, ProductName, BatchNo, DispatchNum, BadNum,IsolationNum, FactStartDate, FactStopDate

, StandCycle, StandEmployee, SocketNum,MoType,DispatchQty,flag)

select distinct a.MO, a.DispatchNo, a.DispatchPrior, a.ItemNo,a.MachineNO,a.ProcCode,b.StaCode,c.ItemName, a.LotNO, a.DispatchQty, 0,0, convert(char(19),a.ActualStartDate,121), convert(char(19),a.ActualEndDate,121)

, a.StandCycle, c.StandEmployee, case a.SocketNum when 0 then 1 else a.SocketNum end SocketNum ,a.MOType,a.DispatchQty,0

from V_DispatchOrder a

inner join mes_machine b on a.MachineNo=b.MachineNo

left join mes_Item c on a.ItemNo=c.ItemNo

left join mes_MouldDetail d on a.MouldNo=d.MouldNo

left join (select DispatchNo,MaterialNO,BatchNO from MES_Material b where b.ID=(select ID=MAX(ID) from MES_Material

where DispatchNo=b.DispatchNO group by DispatchNO) ) m on a.DispatchNo=m.DispatchNO

where dispatchStatus>0

and ((ActualStartDate between @BeginDate and @EndDate )

OR (ActualEndDate between @BeginDate and @EndDate )

OR (ActualStartDate <= @BeginDate and ActualEndDate >= @EndDate))

--获取没有生产，但刷了次品的派工单

insert into #tmpDispatchOrder(MO, DispatchNo, DispatchPrior, ProductNo,MachineNo, ProcCode,StaCode, ProductName, BatchNo, DispatchNum, BadNum,IsolationNum, FactStartDate, FactStopDate

, StandCycle, StandEmployee, SocketNum,MoType,DispatchQty,flag)

, a.StandCycle, c.StandEmployee, case a.SocketNum when 0 then 1 else a.SocketNum end SocketNum ,a.MOType,a.DispatchQty,-1

from V_DispatchOrder a join mes_qc q on a.dispatchno=q.dispatchno and a.dispatchprior=q.dispatchprior and a.itemno=q.itemno

inner join mes_machine b on a.MachineNo=b.MachineNo

left join mes_Item c on a.ItemNo=c.ItemNo left join #tmpDispatchOrder d on a.dispatchno=d.dispatchno and a.dispatchprior=d.dispatchprior

where d.dispatchno is null and qcDate between @BeginDate and @EndDate

if object_id('tempdb..#tmpDispatchBc') is not null drop table #tmpDispatchBc

create table #tmpDispatchBc

(

MO varchar(50),

DispatchNo varchar(50),

DispatchPrior float,

ProductNo varchar(50),

ProcCode varchar(20),

currDate varchar(10),

Bc varchar(10),

BcCode varchar(10),

BCBeginTime varchar(20),

BCEndTime varchar(20),

ProdBeginTime datetime,

ProdEndTime datetime,

CycleTime decimal(10,2),

Flag int

)

if object_id('tempdb..#NoCardTime') is not null drop table #NoCardTime

create table #NoCardTime

(

mo varchar(20),

dispatchno varchar(20),

machineno varchar(520),

currDate varchar(10),

BCCode varchar(10),

Bc varchar(10),

BCBeginTime varchar(20),

BCEndTime varchar(20),

TotalTime decimal(18,4),

NoCardTime decimal(18,4),

StopTime decimal(18,4),

TestModeTime decimal(18,4)

)

declare @t_CurDate varchar(10)

select @t_CurDate = CONVERT(varchar(10),convert(datetime,@BeginDate),121)

--while(@t_CurDate<=convert(varchar(10),convert(datetime,@EndDate),121))

--begin

--未刷卡停机

insert into #tmpDispatchBc(MO, DispatchNo,DispatchPrior,ProductNo,ProcCode, currDate,Bc, BcCode,BCBeginTime,BCEndTime, Flag )

select MO, DispatchNo,DispatchPrior,ProductNo,ProcCode,@t_CurDate,'白班','AC',@t_CurDate+' 07:30:00',@t_CurDate+' 19:59:59',Flag

from #tmpDispatchOrder

insert into #NoCardTime(MachineNO, BCCode,Bc,CurrDate,BCBeginTime,BCEndTime, MO, DispatchNO)

select MachineNo, 'BC','晚班', @t_CurDate ,@t_CurDate+' 20:00:00',CONVERT(varchar(10),dateadd(day,1,convert(datetime,@t_CurDate)),121)+' 07:29:59', '', ''

from V_Machine --where (@MachineNo = '' or MachineNO like '%'+@MachineNo+'%')

union

select MachineNo, 'AC','白班', @t_CurDate ,@t_CurDate+' 07:30:00',@t_CurDate+' 19:59:59', '', ''

from V_Machine --where (@MachineNo = '' or MachineNO like '%'+@MachineNo+'%')

insert into #tmpDispatchBc(MO, DispatchNo,DispatchPrior,ProductNo,ProcCode,currDate,Bc, BcCode,BCBeginTime,BCEndTime,Flag)

select MO, DispatchNo,DispatchPrior,ProductNo,ProcCode,@t_CurDate,'晚班','BC',@t_CurDate+' 20:00:00',CONVERT(varchar(10),dateadd(day,1,convert(datetime,@t_CurDate)),121)+' 07:29:59',Flag

from #tmpDispatchOrder

set @t_CurDate = CONVERT(varchar(10),dateadd(day,1,convert(datetime,@t_CurDate)),121)

--end

--如果当前时间是白班，删除晚班的记录，因为还没到晚班

delete from #NoCardTime where BCBeginTime >GETDATE()

delete from #tmpDispatchBc where BCBeginTime >GETDATE()

insert into #tmpDispatchOrder(Mo,MoType,DispatchNo,DispatchPrior,ProductNo,ProductName,MachineNo,ProcCode,StaCode,currDate,Bc,BcCode,StandCycle,SocketNum,MaterialNo,BatchNo,BCBeginTime,BCEndTime,DispatchQty,flag)

select Mo,MoType,DispatchNo,DispatchPrior,ProductNo,ProductName,MachineNo,ProcCode,a.StaCode,currDate,Bc,BcCode,

StandCycle,SocketNum, a.MaterialNO,a.BatchNO,BCBeginTime,BCEndTime,DispatchQty,case when flag=0 then 1 else -2 end flag

from (

select distinct a.Mo,MoType,a.DispatchNo,a.DispatchPrior,a.ProductNo,b.ProductName,b.MachineNo,b.ProcCode,b.StaCode,a.currDate,a.Bc,c.BcCode,b.StandCycle,SocketNum=b.SocketNum,convert(varchar(19),a.BCBeginTime,121) BCBeginTime,convert(varchar(19),a.BCEndTime,121) BCEndTime

,b.MaterialNO

,b.BatchNO,b.DispatchQty ,a.flag

from #tmpDispatchBc a join MES_BCDetail c on a.BcCode=c.BcCode

inner join #tmpDispatchOrder b on a.DispatchNo=b.DispatchNo and a.DispatchPrior=b.DispatchPrior and a.ProductNo=b.ProductNo) a

order by MachineNo, currDate,bc,DispatchNo desc,ProductNo desc

--更新没有生产的派工单

ProductQty=(dbo.FN_GetProdQty(Mo,DispatchNo,ProductNo,ProcCode,DispatchPrior, '','')+dbo.FN_GetAdjustMachineQty(Mo,DispatchNo,ProductNo,DispatchPrior, '', '','',ProcCode))-dbo.FN_GetBadQty(Mo,DispatchNo,ProductNo,ProcCode,DispatchPrior, '', '')-dbo.FN_GetPatrolMachineOrPolishQty(MO,DispatchNo,ProductNo,ProcCode,DispatchPrior,'', '',2),

BadNum = dbo.FN_GetBadQty(Mo,DispatchNo,ProductNo,ProcCode,DispatchPrior, BCBeginTime, BCEndTime),

IsolationNum=dbo.FN_GetIsolationQty(Mo,DispatchNo,ProductNo,ProcCode,DispatchPrior, BCBeginTime, BCEndTime),--隔离数

PatrolMachineNum=dbo.FN_GetPatrolMachineOrPolishQty(MO,DispatchNo,ProductNo,ProcCode,DispatchPrior,BCBeginTime, BCEndTime,2),

flag=2

from #tmpDispatchOrder d

where d.flag=-2

--更新中间表已经生成的数据

update d set ProductNum = prodqty,

ProductQty=prodqty,

BadNum = badqty,

IsolationNum=dbo.FN_GetIsolationQty(d.Mo,d.DispatchNo,ProductNo,ProcCode,d.DispatchPrior, BCBeginTime, BCEndTime),--隔离数

WorkTime = prodtime,

TestModeTime=TestMouldTime,

d.StopTime = j.StopTime,

PatrolMachineNum=xunjiqty

,polishNum=damoqty,flag=2

from #tmpDispatchOrder d join JM_Interface j on d.dispatchno=j.dispatchno and d.dispatchprior=j.dispatchprior and d.currDate=j.currDate and d.BcCode=j.BcCode

where d.flag=1 -- RowSeq between @row_from and @row_to

--delete from #tmpDispatchOrder_Reach where ProductNum=0 and BadNum=0 and TestModeTime=0.00 and WorkTime=0.00 and StopTime=0.00 and PatrolMachineNum=0 and PolishNum=0

--delete from #tmpDispatchOrder_Reach where StopTime<0.00