函数与复杂的存储过程

USE [ChiefmesNew]
GO
/****** Object: UserDefinedFunction [dbo].[FN_GetIsolationQty] Script Date: 10/19/2015 13:48:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***********************************************
-- 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
AS
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]
GO
/****** Object: StoredProcedure [dbo].[st_MES_RptProductForDay] Script Date: 10/19/2015 09:51:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--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
)
as

--第一部分，基础数据来源。
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()

--更新没有生产的派工单
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

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=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 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),--隔离数
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
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]
GO
/****** Object: StoredProcedure [dbo].[ImportProductForDay] Script Date: 10/19/2015 09:44:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--AUTHOR: hz
--CREATE DATE: 2014/5/15
--DESCRIPTION: 生产日报
--update by hz 2015-08-05
--Remark:添加隔离数显示和良品数-生产数-次品数-隔离数
--exec ImportProductForDay
-- =============================================
ALTER PROCEDURE [dbo].[ImportProductForDay]

as
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)
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,-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

--更新没有生产的派工单
update d set ProductNum = dbo.FN_GetProdQty(Mo,DispatchNo,ProductNo,ProcCode,DispatchPrior, BCBeginTime,BCEndTime)+dbo.FN_GetAdjustMachineQty(Mo,DispatchNo,ProductNo,DispatchPrior, BCBeginTime, BCEndTime,'',ProcCode),
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

--计算班次所有派工单的总时间,停机时间就计算空单的停机时间
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=''

--更新中间表还没有生成的派工单的数据
update d set ProductNum = dbo.FN_GetProdQty(Mo,DispatchNo,ProductNo,ProcCode,DispatchPrior, BCBeginTime,BCEndTime)+dbo.FN_GetAdjustMachineQty(Mo,DispatchNo,ProductNo,DispatchPrior, BCBeginTime, BCEndTime,'',ProcCode),
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),--隔离数
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),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 ,
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
)

declare @row_from int, @row_to int
--select @out_total_rows = count(*) from #tmpDispatchOrder1
--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, ProductQty=ProductQty,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

--select @Tmpcount=COUNT(*) from MES_ElecDetail a inner join #tmpDispatchBc b on a.MO=b.MO and a.DispatchNo=b.DispatchNo and a.DispatchPrior=b.DispatchPrior
-- and a.ProcCode=b.ProcCode where a.MachineNo=@Machine and InputDate between @BCBeginTime and @BCEndTime
-- if(@Tmpcount >1)
-- begin
-- select top 1 @StartElecNum=ElecNum from MES_ElecDetail a inner join #tmpDispatchBc b on a.MO=b.MO and a.DispatchNo=b.DispatchNo and a.DispatchPrior=b.DispatchPrior
-- and a.ProcCode=b.ProcCode where a.MachineNo=@Machine and InputDate between @BCBeginTime and @BCEndTime order by InputDate
-- select top 1 @EndElecNum=ElecNum from MES_ElecDetail a inner join #tmpDispatchBc b on a.MO=b.MO and a.DispatchNo=b.DispatchNo and a.DispatchPrior=b.DispatchPrior
-- and a.ProcCode=b.ProcCode 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 inner join #tmpDispatchBc b on a.MO=b.MO and a.DispatchNo=b.DispatchNo and a.DispatchPrior=b.DispatchPrior
-- and a.ProcCode=b.ProcCode where a.MachineNo=@Machine and InputDate between @BCBeginTime and @BCEndTime order by InputDate
-- end

--update d set StartReading=@StartElecNum,EndReading=@EndElecNum,Degreeses=case when @StartElecNum<>'F' and @EndElecNum<>'F'
-- then case when (CONVERT(int,@EndElecNum)-CONVERT(int,@StartElecNum))<0
-- then 0 else (CONVERT(int,@EndElecNum)-CONVERT(int,@StartElecNum)) end
-- end,MeterRate=80
-- from #tmpDispatchOrder1 d where MachineNo=@Machine and d.Bc=@tBc and currDate=@tcurrDate

--select @NoCardcount=COUNT(*) from MES_ElecDetail a inner join #NoCardTime b on a.MachineNo=b.machineno where a.MachineNo=@Machine and InputDate between @BCBeginTime and @BCEndTime
--if(@NoCardcount >1)
-- begin
-- select top 1 @StartElecNum=ElecNum from MES_ElecDetail a inner join #NoCardTime b on a.MachineNo=b.machineno where a.MachineNo=@Machine and InputDate between @BCBeginTime and @BCEndTime order by InputDate
-- select top 1 @EndElecNum=ElecNum from MES_ElecDetail a inner join #NoCardTime b on a.MachineNo=b.machineno where a.MachineNo=@Machine and InputDate between @BCBeginTime and @BCEndTime order by InputDate desc
-- end
--else
-- begin
-- print 1
-- select top 1 @StartElecNum=ElecNum from MES_ElecDetail a inner join #NoCardTime b on a.MachineNo=b.machineno where a.MachineNo=@Machine and InputDate between @BCBeginTime and @BCEndTime order by InputDate
-- end

--update d set StartReading=@StartElecNum,EndReading=@EndElecNum,Degreeses=case when @StartElecNum<>'F' and @EndElecNum<>'F'
-- then case when (CONVERT(int,@EndElecNum)-CONVERT(int,@StartElecNum))<0
-- then 0 else (CONVERT(int,@EndElecNum)-CONVERT(int,@StartElecNum)) end
-- end,MeterRate=80
-- from #tmpDispatchOrder1 d where MachineNo=@Machine and d.Bc=@tBc and currDate=@tcurrDate

--update #tmpDispatchOrder1 set StartReading=null,EndReading=null,Degreeses=null,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,
--PolishNum,PatrolMachineNum,WorkTime,NoCardTime,TestModeTime,StopTime,BadRate,BadReason,StartReading,EndReading,Degreeses,MeterRate,StrEmpName
-- from #tmpDispatchOrder1 where RowSeq between @row_from and @row_to
select ROW_NUMBER() OVER(ORDER BY MachineNo asc ) ID,Bc,MachineNo,StrEmpName,ProductNo,ProductName,currDate,DispatchQty,ProductQty,GoodNum,BadNum,BadRate,BadReason,BCBeginTime,BCEndTime
from #tmpDispatchOrder1 where (case when Bc='白班' then 'AC' else 'BC' end)=@BC --where RowSeq between @row_from and @row_to

--select TotalRows = @out_total_rows

posted on 2015-10-20 16:41 chengjunde 阅读(391) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

chengjunde

函数与复杂的存储过程

导航

公告