以维护日期的不同而改变显示内容 . 如今天是 21 号 . 维护了七天的数据即 21-- 28 . 当在 22 号打开维护界面的时候 , 维护内容显示为 22 -- 29 号的数据以及日期 .
创建维护表 :..
----------------- 创建数据记录表 -------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pdl_WeekProcedure]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Pdl_WeekProcedure
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pdl_WeekProcedure]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
Create Table Pdl_WeekProcedure (
[Id] int Identity(1,1) Not Null,
[DateId] datetime Not Null ,
[Partnum] varchar(20) Null ,
[Revision] varchar(5) Null DEFAULT('0'),
[Todate] datetime Null,
[Qnty1] int Null Default(0),
[Qnty2] int Null Default(0),
[Qnty3] int Null Default(0),
[Qnty4] int Null Default(0),
[Qnty5] int Null Default(0),
[Qnty6] int Null Default(0),
[Qnty7] int Null Default(0)--,
-- [WeekDate] datetime
CONSTRAINT [PK_WeekProcedure] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
End
----------------- 创建日期表 --------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pdl_WeekProcedureDate]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Pdl_WeekProcedureDate
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pdl_WeekProcedureDate]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
Create Table Pdl_WeekProcedureDate (
[Id] int Identity(1,1) Not Null,
[DateId] datetime Not Null ,
[Date1] datetime Null ,
[Date2] datetime Null ,
[Date3] datetime Null ,
[Date4] datetime Null ,
[Date5] datetime Null ,
[Date6] datetime Null ,
[Date7] datetime Null
CONSTRAINT [PK_WeekProcedureDate] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
End
----------- 创建历一周交货排程史表 ----------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pdl_WeekProcHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table Pdl_WeekProcHistory
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Pdl_WeekProcHistory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
Create Table Pdl_WeekProcHistory (
[Id] int Identity(1,1) Not Null,
[DateId] datetime Not Null ,
[Partnum] varchar(20) Null ,
[Revision] varchar(5) Null DEFAULT('0'),
[Qnty1] int Null Default(0),
[Qnty2] int Null Default(0),
[Qnty3] int Null Default(0),
[Qnty4] int Null Default(0),
[Qnty5] int Null Default(0),
[Qnty6] int Null Default(0),
[Qnty7] int Null Default(0),
[Date1] datetime Null ,
[Date2] datetime Null ,
[Date3] datetime Null ,
[Date4] datetime Null ,
[Date5] datetime Null ,
[Date6] datetime Null ,
[Date7] datetime Null ,
[IOTime] datetime Not Null Default(Getdate()),
[OutPutDate] datetime Null
CONSTRAINT [PK_WeekProcHistory] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
End
更新维护表日期数据 :
/*
Procedure Name : 一周交货排程数据更新
Author Name : lyf
Date : 01/13/2005
*/
Alter Procedure Pdl_WeekProcDateUpdate As
Declare @num int , @num1 int, @num2 int, @Count int
Declare @Str varchar(4000)
Declare @DateId datetime ,@Date1 datetime , @Date2 datetime ,@Date3 datetime, @Date4 datetime,
@Date5 datetime, @Date6 datetime, @Date7 datetime
Select @DateId = Convert(datetime, Convert(varchar,Getdate(),101))
Select @Date1= Convert(datetime, Convert(varchar,Getdate(),101))
Select @Date2= @Date1+1 , @Date3 = @Date1+2, @Date4= @Date1+3,
@Date5 = @Date1+4, @Date6 = @Date1+5 , @Date7 = @Date1+6
--------------- 修正更新为当天日期后向后推 7 天 --------
IF Not Exists(Select * From Pdl_WeekProcedureDate
Where Convert(datetime, Convert(varchar, DateId, 101)) = Convert(datetime, Convert(varchar,@DateId,101)))
Insert Into Pdl_WeekProcedureDate (DateId,Date1,Date2,Date3,Date4,Date5,Date6,Date7)
Values(@DateId, @Date1, @Date2, @Date3, @Date4, @Date5, @Date6,@Date7)
---------- 将表 Pdl_WeekProcedure 里的数据换算成当天最新数据 " 对应程序维护接口 "—
Select @num = Isnull(DatedIFF(day, (Select Min(DateId) From Pdl_WeekProcedure ),
(Select Max(DateId) From Pdl_WeekProcedureDate )),0)
IF @num >0
Begin
Set @Count = 1
Set @num1 = @num
Set @num2 = @num
Set @num = 7 - @num
Set @Str=''
While @Count <= @num
Begin
Select @Str = @Str + ' Qnty'+Convert(varchar,@Count) + ' = Qnty'+Convert(varchar, @num1+1)+','
Set @Count = @Count +1
Set @num1 = @num1+1
End
While @num2 >0
Begin
Select @Str = @Str + ' Qnty'+Convert(varchar, (7-@num2+1)) + ' = 0,'
Set @num2 = @num2-1
End
Select @str = Left(@str , Len(@str)-1)
-------------- 插入一周交货排程维护历史 ------------
Insert Into Pdl_WeekProcHistory( DateId, Partnum, Revision, Qnty1, Qnty2, Qnty3, Qnty4, Qnty5, Qnty6, Qnty7,
Date1, Date2, Date3, Date4, Date5, Date6, Date7, IOTime, OutPutDate )
Select t1.DateId, t1.Partnum, t1.Revision, t1.Qnty1, t1.Qnty2, t1.Qnty3, t1.Qnty4, t1.Qnty5, t1.Qnty6, t1.Qnty7,
t2.Date1, t2.Date2, t2.Date3, t2.Date4, t2.Date5, t2.Date6, t2.Date7 , Getdate(), t1.Todate
From Pdl_WeekProcedure t1(nolock) , Pdl_WeekProcedureDate t2 (nolock)
Where t1.Dateid = t2.DateId
------------------------ 更新 Pdl_WeekProcedure 数据 ----------------------------
Exec('Update Pdl_WeekProcedure Set ' + @Str )
Update Pdl_WeekProcedure
Set DateId = (Select Max(DateId) From Pdl_WeekProcedureDate )
------------------------- 更新当天出货时间字段 --------------------------
Update Pdl_WeekProcedure Set Todate = Null
Where Convert(datetime, Convert(varchar,Todate,101)) <> Convert(datetime, Convert(varchar,Getdate(),101))
End
--delete Pdl_WeekProcedureDate where dateid='2005-01-15 00:00:00.000'
-- Update Pdl_WeekProcedure set dateid = convert(datetime, convert(varchar, getdate(),101))-1
生成最后数据集合 :
/*
Procedure Name : 一周交货排程报表
Author Name : lyf
Date : 01/18/2005
*/
-- exec Pdl_WeekDeliveryProcedure '','','','','',''
Alter Procedure Pdl_WeekDeliveryProcedure
@InBPartnum varchar(20) ='', --'DH024-030', -- '', MH075-013
@InEPartnum varchar(20) ='', --'DH024-030', -- '', MH075-013
@InBRevision varchar(5)='',
@InERevision varchar(5)='',
@BDate varchar(100), --datetime, --='01/18/2005' ,
@EDate varchar(100), --datetime -- ='01/21/2005'
@Flage int
As
Declare @BDateTime datetime , @EDateTime datetime
--Set @BDate = isnull(@BDate, getdate())
--Set @EDate = isnull(@EDate, getdate()+6)
------------- 调用更新维护纪录表数据存储过程 -------------------
Exec Pdl_WeekProcDateUpdate
Select @BDateTime = convert(datetime,Convert(varchar , Getdate() , 101))
Select @EDateTime = convert(datetime,Convert(varchar , @BDateTime +7 , 101))
/* 取周出货状态数据表结构 */
Select top 0 PartNum , Revision , Qnty= 0 , WeekDate=DateId , Com= 'A9' -- = Convert(varchar(5), WeekDate , 101)
into #WeekConfig
From Pdl_WeekProcedure(nolock)
/* 在制品数据 */
select t1.Partnum, t1.Revision ,
Qnty = sum(case t1.POP
when 0
then ceiling(1 * t1.QNTY /(power(ISNULL(t3.LPiece,1),case t1.POP when 0 then 0 else 1 end)*
power(ISNULL(t3.LLPiece,1) , case t1.POP when 2 then 1 else 0 end)))
when 1
then ceiling(1 * t1.QNTY * power(ISNULL(t3.LPiece,1),case t1.POP when 0 then 1 else 0 end) /
power(ISNULL(t3.LLPiece,1) , case t1.POP when 2 then 1 else 0 end))
when 2
then (1 * t1.QNTY * power(ISNULL(t3.LPiece,1),case t1.POP when 0 then 1 else 0 end)*
power(ISNULL(t3.LLPiece,1) , case t1.POP when 2 then 0 else 1 end))
end), DelDate = min(t4.ExpStkDate) , UPP = convert(varchar,t3.LPiece) +'*'+ convert(varchar, t3.LLPiece) ,
t5.Id , t5.name
Into #tmpStknwip
from Stknwip t1(nolock),
LotInfo t2(nolock),
ProdBasic t3(nolock) ,
OrderDetail t4(nolock) ,
PDL_ProcGroup t5 (nolock) ,
PDL_ProcGroupDetail t6 (nolock)
where t1.LotNum=t2.LotNum
and t1.Layer=t2.Layer
and t2.IsInSC <> 1
and t1.Qnty>0
and t1.ProcCode not in('990','000')
and t1.Partnum = t3.Partnum
and t1.Revision = t3.Revision
--and t1.Layer = t3.Layer </S