貼一個我寫的關於生產排程的存儲過程
来源：互联网 发布：linux显示文件大小 编辑：程序博客网 时间：2024/04/29 18:23
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--根據工作中心的生產能力安排每天的生產任務
--附注:界面FrmFProductOrderAnalyse(生產訂單分析)中調用
--思路:前工序必須生產夠後工序以後每天的產量才後工序才開始生產(異步)
--先求每工序較後工序需提前天數，然後累加算總天數(提前天數以最後工序開山日期為準)
--再求最後工序的開始日期，就可以求出各工序開始日期，最後算出各工序需要天數，就可以
--求出各工序結束日期
--工序日最大產量的計算中每天都不考慮較機時間(設置時間)
--15-不用較機[順推] （15為分析方法代碼，不能改）
--注意：由訂單開始日期作為第一道工序的生產開始日期（由前往後推）
--Wrriten By hanjoe 2009-02-13
ALTER proc [dbo].[Pro_A_POErpAndVirtualAnalyseNotAdjustBackwardFive]
@BAANServer varchar(50),--ERP服务器IP
@CompanyNo varchar(25),--公司编号（ERP表名以公司编号为后缀）
@BAANDB varchar(50),--ERP数据库名
@BAANUid varchar(50),--ERP数据库用户
@BAANPwd varchar(50),--ERP数据库密码
@AccountNO varchar(25),--帐套编号，也是本地数据库名称
@dl float,--工作效率
@SqlCriteriaErp nvarchar(1000)='',--ERP訂單查询条件(获取原始数据的过滤条件)，前台得加Where(原因:后台根据@SqlCriteria空否给@Sql赋值不方便)
@SqlCriteriaVir nvarchar(1000)='',--虛擬訂單查詢條件
@IsCover bit=0 --是否覆蓋以前的分析記錄(默認為0，不覆蓋)
as
set nocount on
begin
--Step 0:創建ERP服務器連接
if not exists (select 1 from master.dbo.sysservers where srvname=@BAANServer)
begin
exec sp_addlinkedserver @BAANServer,'SQLSERVER','SQLOLEDB',@BAANServer
exec sp_addlinkedsrvlogin @BAANServer,'false',null,@BAANUid,@BAANPwd
end
--Step 1:创建临时表，保存ERP訂單、虛擬訂單
create table #T --虛擬表1:#T
(
t_pdno int, --生产订单
t_osta int, --訂單狀態(ERP為Binary,這裡將轉換為int)
t_ostn nvarchar(10),--訂單狀態
t_sors nvarchar(25),--數據來源
t_mitm char(16), --物料
t_dsca nvarchar(60),--物料说明
t_qrdr int, --订货数量(ERP:float)
t_cuni char(3), --单位
t_opro char(6), --工艺流程
t_opno smallint, --工序
t_seqn smallint, --顺序
t_tano smallint, --任务
t_dscr nvarchar(60),--任務說明(ERP:char(60))
t_cwoc char(3), --工作中心
t_dscg nvarchar(60),--工作中心說明(ERP:char(60))
t_wcru real, --周基本能力(工作中心)
t_dcru real, --日基本能力(工作中心)
t_mcno char(6), --设备
t_dscs nvarchar(60),--設備說明(ERP:char(60))
t_jbno char(16), --生产批号
t_prdt datetime, --开始日期
t_dldt datetime, --结束日期
t_mccp real, --周基本能力(設備)
t_mdcp real, --日基本能力(設備)
t_sutm int, --设置时间
t_rutm float, --运行时间
t_mcoc real, --设备占用量
t_mnoc real, --人員佔用量
t_efic float, --工作效率
t_daya int --日生产量
)
--Step 2:獲取ERP訂單计算日生产量并存入临时表
--注：日产量是要计算的第一个数据，由它后续产生的天数、最长天数、最晚开始日期、具体结束日期由下面的步骤产生
--注意：子查詢已經將訂單狀態從Binary轉int,因此前台不用轉
--注意：工作中心分析需要字段t_sors值來區分，不能隨便改
declare @Sql nvarchar(4000)
set @Sql=N'insert into #T
(
t_pdno,--生产订单
t_osta,--訂單狀態
t_ostn,--訂單狀態
t_sors,--數據來源
t_mitm,--物料
t_dsca,--物料说明
t_qrdr,--订货数量
t_cuni,--单位
t_opro,--工艺流程
t_opno,--工序
t_seqn,--顺序
t_tano,--任务
t_dscr,--任務說明
t_cwoc,--工作中心
t_dscg,--工作中心說明
t_wcru,--周能力(中心)
t_dcru,--日能力(中心)
t_mcno,--设备
t_dscs,--設備說明
t_jbno,--生产批号
t_prdt,--开始日期
t_dldt,--结束日期
t_mccp,--周能力(設備)
t_mdcp,--日能力(設備)
t_sutm,--设置时间
t_rutm,--运行时间
t_mcoc,--设备占用量
t_mnoc,--人員佔用量
t_efic,--工作效率
t_daya--日生产量
)
select t_pdno,--生产订单
t_osta,--訂單狀態
t_ostn,
''ERP'' as t_sors,
t_mitm,--物料
t_dsca,--物料说明
t_qrdr,--订货数量
t_cuni,--单位
t_opro,--工艺流程
t_opno,--工序
t_seqn,--顺序
t_tano,--任务
t_dscr,--任務說明
t_cwoc,--工作中心
t_dscg,--工作中心說明
t_wcru,--周能力(中心)
t_dcru,--日能力(中心)
t_mcno,--设备
t_dscs,--設備說明
t_jbno,--生产批号
t_prdt,--开始日期
t_dldt,--结束日期
t_mccp,--周能力(設備)
t_mdcp,--日能力(設備)
t_sutm,--设置时间
t_rutm,--运行时间
t_mcoc,--设备占用量
t_mnoc,--人員佔用量
t_efic,--工作效率
t_daya--日生产量
from
(select sfc.t_pdno,--生产订单
cast(sfc.t_osta as int) as t_osta,
case when cast(sfc.t_osta as int)=2 then ''計劃''
when cast(sfc.t_osta as int)=3 then ''已打印''
when cast(sfc.t_osta as int)=4 then ''已下達''
when cast(sfc.t_osta as int)=5 then ''活動''
when cast(sfc.t_osta as int)=6 then ''完成''
when cast(sfc.t_osta as int)=7 then ''已結''
when cast(sfc.t_osta as int)=8 then ''存檔'' end as t_ostn,
sfc.t_mitm,--物料
itm.t_dsca,--物料说明
sfc.t_qrdr,--订货数量
itm.t_cuni,--单位
sfc.t_opro,--工艺流程
rou.t_opno,--工序
rou.t_seqn,--顺序
rou.t_tano,--任务
rour.t_dsca as t_dscr,--任務說明
rou.t_cwoc,--工作中心
roug.t_dsca as t_dscg,--中心說明
roug.t_wcru,--周能力(中心)
roug.t_dcru,--日能力(中心)
rou.t_mcno,--设备
rous.t_dsca as t_dscs,--設備說明
sfc.t_jbno,--生产批号
sfc.t_prdt,--开始日期
sfc.t_dldt,--结束日期
rous.t_mccp,--周能力(設備)
rous.t_mdcp,--日能力
rou.t_sutm,--设置时间
rou.t_rutm,--运行时间
rou.t_mcoc, --设备占用量
rou.t_mnoc,'--人員佔用量
+convert(varchar(25),@dl) +N' as t_efic,'--工作效率
+N't_daya=(ceiling(case rou.t_rutm when 0 '--工作中心日最大生产量(向上取整)
+N' then 999999999 '--运行时间t_rutm为表示生产一件产品几乎不用时间，改用产量则表示每天生产无限多，用表示
+N' else (rous.t_mdcp * 60) * 1.0 / rou.t_rutm * rou.t_mcoc * '+ cast(@dl as varchar)+' end))'
--日产量的计算公式为：工作效率 *(日基本能力 * 60 ) / 运行时间 * 设备占用量
+N' from ['+@BAANServer+'].'+@BAANDB+N'.dbo.ttisfc001'+ @CompanyNo + N' sfc '--生产订单表(以生產訂單做左連接，都是從ERP取數據，所以連接條件不用取空格)
+N' left join ['+@BAANServer+'].'+@BAANDB+N'.dbo.ttiitm001'+@CompanyNo+N' itm on sfc.t_mitm=itm.t_item'--物料(获取物料说明；连接条件：物料)
+N' left join ['+@BAANServer+'].'+@BAANDB+N'.dbo.ttirou102'+@CompanyNo+N' rou on sfc.t_mitm = rou.t_mitm and sfc.t_opro=rou.t_opro'--工艺流程(连接条件：物料、工艺流程)
+N' left join ['+@BAANServer+'].'+@BAANDB+N'.dbo.ttirou002'+@CompanyNo+N' rous on rou.t_mcno=rous.t_mcno'--设备(獲取設備說明、周能力、日能力，连接条件:设备)
+N' left join ['+@BAANServer+'].'+@BAANDB+N'.dbo.ttirou003'+@CompanyNo+N' rour on rou.t_tano=rour.t_tano'--任務(獲取任務說明，連接條件:任務)
+N' left join ['+@BAANServer+'].'+@BAANDB+N'.dbo.ttirou001'+@CompanyNo+N' roug on rou.t_cwoc=roug.t_cwoc) Temp '--工作中心(獲取工作中心說明、周能力、日能力，連接條件:工作中心)
+@SqlCriteriaErp --子查詢，是方便前台寫查詢條件，否則要用表別名
print @Sql
EXEC sp_executesql @Sql
--step 3:獲取虛擬訂單並计算日生产量并存入临时表
--(注意:工作中心分析需要字段t_sors值來區分，不能隨便改)
declare @Sqlv nvarchar(4000)
set @Sqlv=N'insert into #T
(
t_pdno,--生产订单
t_osta,--訂單狀態
t_ostn,
t_sors,--數據來源
t_mitm,--物料
t_dsca,--物料说明
t_qrdr,--订货数量
t_cuni,--单位
t_opro,--工艺流程
t_opno,--工序
t_seqn,--顺序
t_tano,--任务
t_dscr,--任務說明
t_cwoc,--工作中心
t_dscg,--中心說明
t_wcru,--周能力(中心)
t_dcru,--日能力(中心)
t_mcno,--设备
t_dscs,--設備說明
t_jbno,--生产批号
t_prdt,--开始日期
t_dldt,--结束日期
t_mccp,--周能力(設備)
t_mdcp,--日能力
t_sutm,--设置时间
t_rutm,--运行时间
t_mcoc,--设备占用量
t_mnoc,--人員佔用量
t_efic,--工作效率
t_daya --日产量
)
select t_pdno,--生产订单
t_osta,--訂單狀態
t_ostn,
''Virtual'' as t_sors,
t_mitm,--物料
t_dsca,--物料说明
t_qrdr,--订货数量
t_cuni,--单位
t_opro,--工艺流程
t_opno,--工序
t_seqn,--顺序
t_tano,--任务
t_dscr,--任務說明
t_cwoc,--工作中心
t_dscg,--中心說明
t_wcru,--周能力(中心)
t_dcru,--日能力(中心)
t_mcno,--设备
t_dscs,--設備說明
t_jbno,--生产批号
t_prdt,--开始日期
t_dldt,--结束日期
t_mccp,--周能力(設備)
t_mdcp,--日能力
t_sutm,--设置时间
t_rutm,--运行时间
t_mcoc,--设备占用量
t_mnoc,--人員佔用量
t_efic,--工作效率
t_daya --日产量
from
(select sfc.t_pdno,--生产订单
sfc.t_osta,--訂單狀態
case sfc.t_osta when 1 then ''計劃'' when 0 then ''取消'' when 3 then ''確定'' end as t_ostn,--訂單狀態
sfc.t_mitm,--物料
sfc.t_dsca,--物料说明
sfc.t_qrdr,--订货数量
sfc.t_cuni,--单位
sfc.t_opro,--工艺流程
rou.t_opno,--工序
rou.t_seqn,--顺序
rou.t_tano,--任务
rour.t_dsca as t_dscr,--任務說明
rou.t_cwoc,--工作中心
roug.t_dsca as t_dscg,--中心說明
roug.t_wcru,--周能力(中心)
roug.t_dcru,--日能力(中心)
rou.t_mcno,--设备
rous.t_dsca as t_dscs,--設備說明
sfc.t_jbno,--生产批号
sfc.t_prdt,--开始日期
sfc.t_dldt,--结束日期
rous.t_mccp,--周能力(設備)
rous.t_mdcp,--日能力(設備)
rou.t_sutm,--设置时间
rou.t_rutm,--运行时间
rou.t_mcoc, --设备占用量
rou.t_mnoc,'--人員佔用量real
+convert(varchar(25),@dl) +N' as t_efic,'--工作效率
+N't_daya=(ceiling(case rou.t_rutm when 0 '--工作中心日最大生产量(向上取整)
+N' then 999999999 '--运行时间t_rutm为表示生产一件产品几乎不用时间，改用产量则表示每天生产无限多，用表示
+N' else (rous.t_mdcp * 60) * 1.0 / rou.t_rutm * rou.t_mcoc * '+ cast(@dl as varchar)+' end))'
--日产量的计算公式为：工作效率 *(日基本能力 * 60) / 运行时间 * 设备占用量
+N' from ['+@AccountNO+N'].dbo.TA_ProductOrderVirtual sfc '--生产订单表(以生產訂單做左連接，都是從ERP取數據，所以連接條件不用取空格)
+N' left join ['+@BAANServer+'].'+@BAANDB+N'.dbo.ttirou102'+@CompanyNo+N' rou on Rtrim(Ltrim(sfc.t_mitm)) = Rtrim(Ltrim(rou.t_mitm)) and Rtrim(Ltrim(sfc.t_opro))=Rtrim(Ltrim(rou.t_opro))'--工艺流程(连接条件：物料、工艺流程)
+N' left join ['+@BAANServer+'].'+@BAANDB+N'.dbo.ttirou002'+@CompanyNo+N' rous on rou.t_mcno=rous.t_mcno'--设备(獲取設備說明、周能力、日能力，连接条件:设备)
+N' left join ['+@BAANServer+'].'+@BAANDB+N'.dbo.ttirou003'+@CompanyNo+N' rour on rou.t_tano=rour.t_tano'--任務(獲取任務說明，連接條件:任務)
+N' left join ['+@BAANServer+'].'+@BAANDB+N'.dbo.ttirou001'+@CompanyNo+N' roug on rou.t_cwoc=roug.t_cwoc'--工作中心(獲取工作中心說明、周能力、日能力，連接條件:工作中心)
+N' where sfc.t_efdt >= getdate()' --過濾已過期的虛擬訂單(前台不用，有的存儲過程需在前台過濾)
+N') Temp '
+@SqlCriteriaVir --子查詢，是方便前台寫查詢條件，否則要用表別名
print @Sqlv
EXEC sp_executesql @Sqlv --警告: 聚合或其它 SET 操作消除了空值。
--step 4:計算各工序需要天數、較後工序開始日期提前天、最后一天的产量
--注意:求提前期t_prdy是最重要的步驟，如果這裡出問題，後面的都將出錯
--未完成:有個策略問題:如果前工序比後工序大很多，就不用提前1天了。
select t_pdno, --生产订单
t_qrdr, --訂貨數量
t_opno, --工序
t_wdsd=ceiling(t_qrdr*1.0/t_daya),--各工序需要天數(訂貨數量/最大产量)
t_lqty=(case t_qrdr%t_daya --注意: cast(T2.t_qrdr as int) as t_qrdr,--数量（转int供取模）
when t_qrdr then t_qrdr
when 0 then t_daya
else t_qrdr%t_daya
end), --各工序最后一天的产量(取模）
t_prdy=case when t_qrdr <=t_daya then 1 --1：訂貨數量<=最大產能，提前一天(不管後工序)
when t_daya >=t_dayn then 1 --2：工序產量>=後工序產量(不管訂貨數量)，提前一天
when t_qrdr > t_daya and t_qrdr< t_dayn--3：訂貨數量>工序產量;訂貨數量<後工序產量
then ceiling(t_qrdr*1.0/t_daya)
else (case when ceiling((t_dayn-t_daya)*(t_qrdr*1.0/t_dayn)/t_daya+1) is null
or ceiling((t_dayn-t_daya)*(t_qrdr*1.0/t_dayn)/t_daya+1) <=0
then 1 --空是最後工序，比結束日期提前一天；小於0，前工序比後工序產能大
else ceiling((t_dayn-t_daya)*(t_qrdr*1.0/t_dayn)/t_daya+1)
end) --4：較後工序開始日期提前天
end
--提前天公式:(後工序產量-當前工序產量)*(訂單數量/後工序產量)/當前工序產量+1 (是否加1要判斷)
into #PRDY --虛擬表2:#PRDY
from (
select t_pdno, --生产订单
t_qrdr, --訂貨數量(用於外查詢求需要天數、最後一天產量)
t_opno, --工序
t_daya, --最大產量(用於外查詢求需要天數、最後一天產量)
t_opnt=(select top 1 t_opno
from #T
where t_pdno=t.t_pdno
and t_opno>t.t_opno
order by t_opno), --後工序
t_dayn=(select top 1 t_daya
from #T
where t_pdno=t.t_pdno
and t_opno>t.t_opno
order by t_opno) --後工序最大產量
from #T t
) t --子查詢:求出當前工序、後工序及其最大產量
----step 5:求各工序總提前天數(以最後工序開始那天為基準)
--select prdy.t_pdno, --生产订单
-- prdy.t_opno, --工序
-- t_prds=sum(prdy2.t_prdy) --總提前天(以最後工序開始那天為基準)
-- --以後求出最後工序開始日期，然後根據總提前天就可以求出各工序的開始日期
--into #PRDS --虛擬表3:#PRDS
--from #PRDY prdy
--left join #PRDY prdy2
--on prdy.t_pdno=prdy2.t_pdno --生产订单
-- and prdy.t_opno<=prdy2.t_opno --工序
--group by prdy.t_pdno,--生产订单
-- prdy.t_opno --工序
--step 5:求各工序總推後天數
select prdy.t_pdno, --生产订单
prdy.t_opno, --工序
t_prds=sum(prdy2.t_prdy) --總提前天(以最後工序開始那天為基準)
--以後求出最後工序開始日期，然後根據總提前天就可以求出各工序的開始日期
into #PRDS --虛擬表3:#PRDS
from #PRDY prdy
left join #PRDY prdy2
on prdy.t_pdno=prdy2.t_pdno --生产订单
and prdy.t_opno>prdy2.t_opno --工序
group by prdy.t_pdno,--生产订单
prdy.t_opno --工序
----step 6:求各工序開始時間、結束時間
--select prds.t_pdno, --生产订单
-- prds.t_opno, --工序
-- t_stdt=dbo.Fun_GetStartDateByEndDateWorkdays(lsdy2.t_stdt,
-- prds.t_prds), --各工序开始日期
-- t_oped=dbo.Fun_GetEndDateByStartDateWorkDays(
-- dbo.Fun_GetStartDateByEndDateWorkdays(lsdy2.t_stdt,
-- prds.t_prds),
-- prdy.t_wdsd ), --各工序結束時間
-- prdy.t_wdsd, --各工序需要天數
-- prdy.t_lqty --各工序最后一天的产量
--into #BGED --虛擬表4:#BGED
--from #PRDS prds
--left join
-- ( --因為前面的總提前期是以最後工序开始日期為基準的，所以隻有先求出最後工序开始日期，才能求開始日期
-- select t_pdno, --生产订单
-- t_stdt=dbo.Fun_GetStartDateByEndDateWorkdays(dateadd(day,-1,t_dldt),t_lsdy)--最後工序开始日期
-- --注意:dateadd(day,-1,t_dldt)，減1的原因:交貨日期前一天完工,所以把交貨日期前一天當作結束日期
-- from
-- (
-- select t.t_pdno, --生产订单
-- t.t_dldt, --結束日期(用於外查詢求開始日期)
-- t_lsdy=ceiling(t.t_qrdr*1.0/t.t_daya) --最後工序所需天數
-- from #T t
-- where t_opno=(select top 1 t_opno
-- from #T
-- where t_pdno=t.t_pdno
-- order by t_opno desc)--最後工序
-- ) lsdy ---子查詢2:最後工序所需天數
-- ) lsdy2--子查詢3:計算最後工序的開始日期(OK)
--on prds.t_pdno=lsdy2.t_pdno
--left join #PRDY prdy
--on prds.t_pdno=prdy.t_pdno --生产订单
-- and prds.t_opno=prdy.t_opno --工序
--step 6:求各工序開始時間、結束時間
select prds.t_pdno, --生产订单
prds.t_opno, --工序
t_stdt=dbo.Fun_GetEndDateByStartDateWorkDays(lsdy2.t_stdt,
prds.t_prds), --各工序开始日期
t_oped=dbo.Fun_GetEndDateByStartDateWorkDays(
dbo.Fun_GetEndDateByStartDateWorkDays(lsdy2.t_stdt,
prds.t_prds),
prdy.t_wdsd ), --各工序結束時間
prdy.t_wdsd, --各工序需要天數
prdy.t_lqty --各工序最后一天的产量
into #BGED --虛擬表4:#BGED
from #PRDS prds
left join
( --因為前面的總提前期是以最後工序开始日期為基準的，所以隻有先求出最後工序开始日期，才能求開始日期
-- select t_pdno, --生产订单
-- t_stdt=dbo.Fun_GetStartDateByEndDateWorkdays(dateadd(day,-1,t_dldt),t_lsdy)--最後工序开始日期
-- --注意:dateadd(day,-1,t_dldt)，減1的原因:交貨日期前一天完工,所以把交貨日期前一天當作結束日期
-- from
-- (
select distinct t.t_pdno, --生产订单
dateadd(day,1,t.t_prdt) as t_stdt --開始日期(用於外查詢求開始日期)
--t_lsdy=ceiling(t.t_qrdr*1.0/t.t_daya) --最後工序所需天數
from #T t
-- where t_opno=(select top 1 t_opno
-- from #T
-- where t_pdno=t.t_pdno
-- order by t_opno desc)--最後工序
-- ) lsdy ---子查詢2:最後工序所需天數
) lsdy2--子查詢3:計算最後工序的開始日期(OK)
on prds.t_pdno=lsdy2.t_pdno
left join #PRDY prdy
on prds.t_pdno=prdy.t_pdno --生产订单
and prds.t_opno=prdy.t_opno --工序
--Step 7:把待分析的数据保存進臨時表
select t.t_pdno,--生产订单int
t.t_osta,--訂單狀態
t.t_ostn,
t.t_sors,--數據來源
t.t_mitm,--物料char(16)
t.t_dsca,--物料说明nvarchar(60)
t.t_qrdr,--数量float 数据库保存为int
t.t_cuni,--单位char(3)
t.t_opro,--工艺流程char(6)
t.t_opno,--工序smallint
t.t_seqn,--顺序smallint
convert(varchar(10),t.t_opno)+'/ '+convert(varchar(10),t.t_seqn) as t_pose,--工序/顺序
t.t_tano,--任务smallint
t.t_dscr,--任務說明char(60)數據庫保存為nvarchar(60)
t.t_cwoc,--工作中心char(3)
t.t_dscg,--工作中心說明char(60)數據庫保存為nvarchar(60)
t.t_wcru,--周基本能力(工作中心)
t.t_dcru,--日基本能力(工作中心)
t.t_mcno,--设备char(6)
t.t_dscs,--設備說明char(60)數據庫保存為nvarchar(60)
t.t_jbno,--生产批号char(16)
t.t_prdt,--开始日期datetime
t.t_dldt,--结束日期datetime
t.t_mccp,--周基本能力(設備)real
t.t_mdcp,--日基本能力real
t.t_sutm,--设置时间int
t.t_rutm,--运行时间float
t.t_mcoc,--设备占用量real
t.t_mnoc,--人員佔用量real
t.t_efic,--工作效率float
t.t_daya,--日生产量int
--c.t_maxq,--每个工序实际安排最大产量int(同步生產中用到)
c.t_wdsd,--所需天数int
--c.t_maxd,--最长天数int(同步生產中用到)
c.t_stdt,--开始日期datetime
c.t_oped,--每个工序具体的结束日期datetime
c.t_wdsm,--总天数(周末加工作日)int
c.t_moth,--跨月份数量int
c.t_lqty--每个工序最后一天的生产量（取模）int
into #Temp --虛擬表5:#Temp
from #T t --#T原始數據臨時表
left join
(
select t_pdno,--生产订单
t_opno,--工序
t_stdt,--各工序开始日期
t_oped,--各工序结束日期
t_wdsd,--各工序需要天數
t_wdsm=(datediff(day,t_stdt,t_oped)+1),--总天数(周末加工作日,datediff(day,各工序开始日期,各工序结束日期)+1)
t_moth=(datediff(month,t_stdt,t_oped)+1),--跨月份数量(datediff(month,各工序开始日期，各工序结束日期)+1)
t_lqty --最后一天的产量
from #BGED
) c --子查詢:求总天数(周末加工作日)、跨月份数量
on t.t_pdno =c.t_pdno
and t.t_opno=c.t_opno --连接条件：生产订单、工序、顺序
order by t.t_pdno,t.t_seqn,t.t_opno
--Step 8:如果覆蓋，將本地數據庫中分析过的訂單删除
if @IsCover=1 --覆蓋
begin
declare @Sql2 nvarchar(1000)
set @Sql2=N'delete from '+N'['+ @AccountNO+N']'+ N'.dbo.TA_ProductOrderAnalyse'
+N' where exists (select 1 from #Temp where t_pdno='+N'['+ @AccountNO+N']'+ N'.dbo.TA_ProductOrderAnalyse.t_pdno)'
print @Sql2
EXEC sp_executesql @Sql2--完成將本地數據庫中分析过的訂單删除
end
--Step 9:分析获取到的数据，显示每天的生产任务
--周末用-17表示，-1表示当前月份没有那一天
select
top 60
t_aaam=identity(int,1,1) into #M --用于拆分月份
from syscolumns a
select *,convert(char(7),t_aymd,21) as t_aaym,
case when datepart(dw,dateadd(day, 0,t_aymd)) in (1,7) then '-17' when dateadd(day, 0,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day, 0,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [1],
-- datepart(dw,dateadd(day, 0,ym)) in (1,7)周末 dateadd(day, 0,ym)=t_oped 每个工序的结束结束日期就分配最后一天的生产量权 dateadd(day, 0,ym) between bd and t_oped开始日期到结束日期就安排最大生产量
case when datepart(dw,dateadd(day, 1,t_aymd)) in (1,7) then '-17' when dateadd(day, 1,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day, 1,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [2],
case when datepart(dw,dateadd(day, 2,t_aymd)) in (1,7) then '-17' when dateadd(day, 2,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day, 2,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [3],
case when datepart(dw,dateadd(day, 3,t_aymd)) in (1,7) then '-17' when dateadd(day, 3,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day, 3,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [4],
case when datepart(dw,dateadd(day, 4,t_aymd)) in (1,7) then '-17' when dateadd(day, 4,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day, 4,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [5],
case when datepart(dw,dateadd(day, 5,t_aymd)) in (1,7) then '-17' when dateadd(day, 5,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day, 5,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [6],
case when datepart(dw,dateadd(day, 6,t_aymd)) in (1,7) then '-17' when dateadd(day, 6,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day, 6,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [7],
case when datepart(dw,dateadd(day, 7,t_aymd)) in (1,7) then '-17' when dateadd(day, 7,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day, 7,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [8],
case when datepart(dw,dateadd(day, 8,t_aymd)) in (1,7) then '-17' when dateadd(day, 8,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day, 8,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [9],
case when datepart(dw,dateadd(day, 9,t_aymd)) in (1,7) then '-17' when dateadd(day, 9,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day, 9,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [10],
case when datepart(dw,dateadd(day,10,t_aymd)) in (1,7) then '-17' when dateadd(day,10,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,10,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [11],
case when datepart(dw,dateadd(day,11,t_aymd)) in (1,7) then '-17' when dateadd(day,11,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,11,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [12],
case when datepart(dw,dateadd(day,12,t_aymd)) in (1,7) then '-17' when dateadd(day,12,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,12,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [13],
case when datepart(dw,dateadd(day,13,t_aymd)) in (1,7) then '-17' when dateadd(day,13,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,13,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [14],
case when datepart(dw,dateadd(day,14,t_aymd)) in (1,7) then '-17' when dateadd(day,14,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,14,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [15],
case when datepart(dw,dateadd(day,15,t_aymd)) in (1,7) then '-17' when dateadd(day,15,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,15,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [16],
case when datepart(dw,dateadd(day,16,t_aymd)) in (1,7) then '-17' when dateadd(day,16,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,16,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [17],
case when datepart(dw,dateadd(day,17,t_aymd)) in (1,7) then '-17' when dateadd(day,17,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,17,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [18],
case when datepart(dw,dateadd(day,18,t_aymd)) in (1,7) then '-17' when dateadd(day,18,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,18,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [19],
case when datepart(dw,dateadd(day,19,t_aymd)) in (1,7) then '-17' when dateadd(day,19,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,19,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [20],
case when datepart(dw,dateadd(day,20,t_aymd)) in (1,7) then '-17' when dateadd(day,20,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,20,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [21],
case when datepart(dw,dateadd(day,21,t_aymd)) in (1,7) then '-17' when dateadd(day,21,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,21,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [22],
case when datepart(dw,dateadd(day,22,t_aymd)) in (1,7) then '-17' when dateadd(day,22,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,22,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [23],
case when datepart(dw,dateadd(day,23,t_aymd)) in (1,7) then '-17' when dateadd(day,23,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,23,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [24],
case when datepart(dw,dateadd(day,24,t_aymd)) in (1,7) then '-17' when dateadd(day,24,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,24,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [25],
case when datepart(dw,dateadd(day,25,t_aymd)) in (1,7) then '-17' when dateadd(day,25,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,25,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [26],
case when datepart(dw,dateadd(day,26,t_aymd)) in (1,7) then '-17' when dateadd(day,26,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,26,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [27],
case when datepart(dw,dateadd(day,27,t_aymd)) in (1,7) then '-17' when dateadd(day,27,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,27,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [28],
case when isdate(convert(char(8),t_aymd,21)+'29')=0 then '-1' when datepart(dw,dateadd(day,28,t_aymd)) in (1,7) then '-17' when dateadd(day,28,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,28,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [29],
case when isdate(convert(char(8),t_aymd,21)+'30')=0 then '-1' when datepart(dw,dateadd(day,29,t_aymd)) in (1,7) then '-17' when dateadd(day,29,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,29,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [30],
case when isdate(convert(char(8),t_aymd,21)+'31')=0 then '-1' when datepart(dw,dateadd(day,30,t_aymd)) in (1,7) then '-17' when dateadd(day,30,t_aymd)=t_oped then cast(t_lqty as nvarchar(9)) when dateadd(day,30,t_aymd) between t_stdt and t_oped then cast(t_daya as nvarchar(9)) else null end as [31]
into #S --虛擬表6:#S
from (
select *,
dateadd(mm,#M.t_aaam-1,t_stdt-datepart(day,t_stdt)+1) as t_aymd--年月
from #Temp T
left join #M
on T.t_moth >=#M.t_aaam---拆分月份
) A
--Step 10:如果不覆蓋，刪除虛擬表中以前分析过的訂單
if @IsCover=0 --覆蓋
begin
declare @Sql3 nvarchar(1000)
set @Sql3=N'delete from #S'
+N' where exists (select 1 from '
+N' ['+@AccountNO+N']'+N'.dbo.TA_ProductOrderAnalyse'
+N' where t_pdno=#S.t_pdno)'
print @Sql3
EXEC(@Sql3)--完成刪除虛擬表中以前分析过的訂單
end
--Step 11:将分析结果插入各数据库中，以便对工作中心进行汇总分析
--附注1:每次分析都保存下来，分析过了则将以前的记录删除(Step 5)
--扶助2:不保存訂單狀態，因為它是實時取出來的
set nocount off --注意，不能刪除，前台用Cmd.ExecuteNonQuery返回最後一次影響的條數
declare @Sql4 nvarchar(4000)
set @Sql4=N'insert into '
+ N'[' + @AccountNO + N']' + N'.dbo.TA_ProductOrderAnalyse '
+N'(t_pdno,
t_sors,
t_mitm,
t_dsca,
t_qrdr,
t_cuni,
t_opro,
t_opno,
t_seqn,
t_pose,
t_tano,
t_dscr,
t_cwoc,
t_dscg,
t_wcru,
t_dcru,
t_mcno,
t_dscs,
t_jbno,
t_prdt,
t_dldt,
t_mccp,
t_mdcp,
t_sutm,
t_rutm,
t_mcoc,
t_mnoc,
t_efic,
t_daya,
t_wdsd,
t_stdt,
t_wdsm,
t_oped,
t_moth,
t_lqty,
t_aaam,
t_aymd,
t_aaym,
t_anno,
t_annm,
[1],
[2],
[3],
[4],
[5],
[6],
[7],
[8],
[9],
[10],
[11],
[12],
[13],
[14],
[15],
[16],
[17],
[18],
[19],
[20],
[21],
[22],
[23],
[24],
[25],
[26],
[27],
[28],
[29],
[30],
[31])
select t_pdno,
t_sors,
t_mitm,
t_dsca,
t_qrdr,
t_cuni,
t_opro,
t_opno,
t_seqn,
t_pose,
t_tano,
t_dscr,
t_cwoc,
t_dscg,
t_wcru,
t_dcru,
t_mcno,
t_dscs,
t_jbno,
t_prdt,
t_dldt,
t_mccp,
t_mdcp,
t_sutm,
t_rutm,
t_mcoc,
t_mnoc,
t_efic,
t_daya,
t_wdsd,
t_stdt,
t_wdsm,
t_oped,
t_moth,
t_lqty,
t_aaam,
t_aymd,
t_aaym,
15,
''不用較機[順推]'',
[1],
[2],
[3],
[4],
[5],
[6],
[7],
[8],
[9],
[10],
[11],
[12],
[13],
[14],
[15],
[16],
[17],
[18],
[19],
[20],
[21],
[22],
[23],
[24],
[25],
[26],
[27],
[28],
[29],
[30],
[31]
from #S '
print @Sql4
exec sp_executesql @Sql4
end
- 貼一個我寫的關於生產排程的存儲過程
- 我写我的
- 我认识的我
- 我的``
- 我的
- 我的
- 我的
- 我的
- 我的
- 我的
- 我的
- 我的
- 我的
- 我的
- 我的
- 我的
- 我的
- 我的
- JavaFX
- ListView 动态增加列标题
- MySQL 字符串函数：字符串截取
- WAP介绍
- 有意网址
- 貼一個我寫的關於生產排程的存儲過程
- 一时兴起
- 用link文件安装Eclipse插件
- SAF 中缓存服务的实现
- 数据库-备份Sybase DataBase
- MD5
- 怎样获得下载频道的资源分？
- 解决方案：CS0016: 未能写入输出文件“WINDOWS/Microsoft.NET/Framework/v2.0.50727/Temporary ASP.NET Files/*/*.dll' 拒绝访问
- Java IO操作浅析
