以维护日期的不同而改变显示内容. 如今天是 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
and t2.PoNum*=t4.PoNum
and t2.Poitem *=t4.SerialNum
and t1.ProcCode = t6.ProcCode
and t5.Id = t6.Id
and (( t1.PartNum >= @InBPartnum or @InBPartnum='')
and (t1.PartNum <= @InEPartnum or @InEPartnum=''))
and ((t1.Revision = @InBRevision or @InBRevision ='')
and (t1.Revision = @InERevision or @InERevision =''))
Group By t1.Partnum, t1.Revision,
t3.LPiece , t3.LLPiece , t5.Id ,t5.name
Order By t5.Id , t1.partnum
/*wip完整性生成*/
Declare @CheckPartNum varchar(20) , @CheckRevision varchar(5)
Declare @WipProcCode varchar(20) , @WipId varchar(5)
Select Top 1 @CheckPartNum= Partnum , @CheckRevision = Revision From #tmpStknwip
DECLARE CheckWip INSENSITIVE CURSOR
FOR
Select ProcName = RTrim(LTrim(t1.Name)) , Id = RTrim(LTrim(t1.Id))
From PDL_ProcGroup t1(nolock) , PDL_ProcGroupDetail t2 (nolock)
Where t1.Id = t2.Id
Group By t1.Id, t1.Name
OPEN CheckWip
BEGIN
FETCH NEXT FROM CheckWip INTO @WipProcCode , @WipId
WHILE @@FETCH_STATUS = 0
BEGIN
IF Not Exists(Select * From #tmpStknwip Where Name = @WipProcCode and Id = @WipId )
Insert Into #tmpStknwip( Partnum, Revision , Qnty, Name , Id)
Select @CheckPartNum ,@CheckRevision , 0 ,@WipProcCode ,@WipId
FETCH NEXT FROM CheckWip INTO @WipProcCode , @WipId
END
END
CLOSE CheckWip
DEALLOCATE CheckWip
/*合计在制品数量*/
Select Partnum, Revision , Qnty = sum(Qnty) ,
DelDate , UPP , ProcCode = Name , Id
Into #Stknwip
From #tmpStknwip
Group By Partnum, Revision , DelDate , UPP , Id , Name
Order By ID
/*判断完整性*/
IF (( Not Exists( Select * from #WeekConfig )) and (Not Exists( Select * from #Stknwip )) )
Begin
Insert Into #Stknwip( Partnum, Revision , Qnty, ProcCode , Id)
Select 'not data' , 'no' , 0 , ProcCode = Name , Id
From PDL_ProcGroup
Insert Into #WeekConfig(PartNum , Revision , Qnty ,WeekDate , Com)
Select 'not data' , 'no' , 0 , @BDateTime, 'A9'
End
Else
Begin
IF Not Exists( Select * from #Stknwip )
Insert Into #Stknwip( Partnum, Revision , Qnty, ProcCode , Id)
Select 'not data' , 'no' , 0 , ProcCode = Name , Id
From PDL_ProcGroup
IF Not Exists ( Select * From #WeekConfig )
Insert Into #WeekConfig(PartNum , Revision , Qnty ,WeekDate, Com)
Select Top 1 t1.Partnum, t1.Revision , Qnty = 0 , @BDateTime , 'A9'
From Stknwip t1(nolock), LotInfo t2(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 >= @InBPartnum or @InBPartnum='')
and (t1.PartNum <= @InEPartnum or @InEPartnum=''))
and ((t1.Revision = @InBRevision or @InBRevision ='')
and (t1.Revision = @InERevision or @InERevision =''))
End
/*生成出货状态时间格式以及完整性生成*/
Declare @Partnum varchar(20) , @Revision varchar(5) , @Count int , @DateCount int
Declare @WeekDate datetime , @Com varchar(200)
Select @Com ='A'
DECLARE AddDateList INSENSITIVE CURSOR
FOR
Select Partnum, Revision From #WeekConfig
Group by Partnum, Revision
OPEN AddDateList
BEGIN
FETCH NEXT FROM AddDateList INTO @Partnum , @Revision
WHILE @@FETCH_STATUS = 0
BEGIN
Select @Count = Count(WeekDate) From #WeekConfig
Where Partnum = @Partnum and Revision = @Revision and WeekDate >= @BDateTime and WeekDate <= @EDateTime
Select @DateCount = 0
IF @Count < 7
Begin
While @DateCount < 7
Begin
Select @WeekDate = convert(datetime, Convert(varchar , (@BDateTime + @DateCount), 101))
IF Not Exists( Select * From #WeekConfig
Where Partnum = @Partnum and Revision = @Revision
and convert(datetime, Convert(varchar ,WeekDate , 101)) = @WeekDate
)
Begin
Insert Into #WeekConfig(partnum, revision, qnty , weekdate , Com)
Values(@Partnum , @Revision , 0 , convert(datetime, Convert(varchar , (@BDateTime + @DateCount), 101)),
@Com+ Convert(varchar, @DateCount))
Select @DateCount = @DateCount + 1
End
Else
Begin
Update #WeekConfig Set Com = @Com+ Convert(varchar, @DateCount)
Where Partnum = @Partnum and Revision = @Revision
and convert(datetime, Convert(varchar ,WeekDate , 101)) = @WeekDate
Select @DateCount = @DateCount + 1
End
End
End
FETCH NEXT FROM AddDateList INTO @Partnum , @Revision
END
END
CLOSE AddDateList
DEALLOCATE AddDateList
/*生成交叉数据集*/
Insert Into #Stknwip( Partnum, Revision , Qnty, ProcCode , Id)
Select PartNum , Revision , Qnty , Com , '1A'
From #WeekConfig
Where WeekDate >= @BDateTime and WeekDate <= @EDateTime
Declare @StkPartnum varchar(20) , @StkRevision varchar(5) , @StkDelDate datetime , @StkUPP varchar(10)
DECLARE CU_StknWip INSENSITIVE CURSOR
FOR
Select Partnum , Revision From #Stknwip
Group By Partnum , Revision
OPEN CU_StknWip
BEGIN
FETCH NEXT FROM CU_StknWip INTO @StkPartnum , @StkRevision
WHILE @@FETCH_STATUS = 0
BEGIN
select @StkDelDate = min(deldate), @StkUPP =min(upp)
from #Stknwip
Where Partnum = @StkPartnum and Revision = @StkRevision
Update t1 Set t1.DelDate = @StkDelDate , t1.UPP = @StkUPP From #Stknwip t1
Where t1.Partnum = @StkPartnum and t1.Revision = @StkRevision
and (isnull(t1.DelDate,'01/01/1900')= '01/01/1900' or isnull(t1.UPP,'-') = '-')
FETCH NEXT FROM CU_StknWip INTO @StkPartnum , @StkRevision
END
END
CLOSE CU_StknWip
DEALLOCATE CU_StknWip
Declare @s varchar(8000)
Set @s=''
Select @s=@s+',['+rtrim(proccode)+'] = max(case proccode when '''+rtrim(proccode)+''' then Qnty else '''' end)'
From #Stknwip Group By proccode , Id Order By Id
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[X_tmpWeekProc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[X_tmpWeekProc]
Exec('select Partnum, Revision , DelDate=min(DelDate) ,UPP '+@s+' Into X_tmpWeekProc from #Stknwip group by Partnum , Revision, UPP ')
Select * Into #StknWipTotal From X_tmpWeekProc
Drop Table X_tmpWeekProc
Select t2.PartNum,t2.Revision, PQnty = Sum(t2.PQnty)
Into #InWarehouse
From PDL_PassBas t1(nolock),
PDL_PassDtl t2(nolock),
LotInfo t3(nolock)
Where t1.PaperNo = t2.PaperNo
and (t1.PaperDate >= @BDateTime)
and (t1.PaperDate <= @EDateTime)
and t2.AftProc >= '800'
and t2.LotNum = t3.LotNum
and t1.finished = 1
and t1.IsCancel = 0
Group By t2.PartNum,t2.Revision
------------------------------------增加仓位字段------------------------------
Exec('ALTER TABLE #StknWipTotal add [InWarehouse] int not NULL DEFAULT(0)')
---------修改入库数----------
Update t1 Set t1.InWarehouse = isnull(t2.PQnty,0) From #StknWipTotal t1 , #InWarehouse t2
Where t1.Partnum = t2.Partnum
------------修改维护数据, 方便时间限定范围-------------------------
Select top 0 Dateid,PartNum, Revision , TodayTime = DateId , Qnty = 0 , DateStr='Qnty10' ,Todate
Into #ToDate
From Pdl_WeekProcedure
Declare @DayNum int , @CheckDate datetime , @StrInt varchar(8000)
Declare @PartnumDate varchar(20) , @RevisionDate varchar(5)
Set @CheckDate = Convert(datetime , Convert(varchar, getdate(), 101))
Set @StrInt = ''
DECLARE Cu_PartDate INSENSITIVE CURSOR
FOR
Select Distinct PartNum, Revision From Pdl_WeekProcedure
OPEN Cu_PartDate
BEGIN
FETCH NEXT FROM Cu_PartDate INTO @PartnumDate , @RevisionDate
WHILE @@FETCH_STATUS = 0
BEGIN
Set @DayNum = 1
While @DayNum <= 7
Begin
Set @StrInt = Convert(varchar, @Daynum)
Exec ( ' Insert Into #ToDate(Dateid, PartNum, Revision , TodayTime, Qnty ,DateStr, Todate)
Select t2.Dateid, Partnum='''+@PartnumDate+''', Revision='''+@RevisionDate +''',
t1.[Date' + @StrInt +'] , Qnty = sum(Isnull(t2.[Qnty' + @StrInt +'],0)),
DateStr =' +'''Qnty' + @StrInt +''', t2.Todate
From Pdl_WeekProcedureDate t1(nolock) ,
Pdl_WeekProcedure t2(nolock)
Where t1.DateId = t2.DateId and t1.DateId='' ' + @CheckDate +'''
and t2.Partnum = ''' +@PartnumDate +''' and t2.Revision = ''' +@RevisionDate+'''
Group By t1.[Date' + @StrInt +'], t2.Dateid, t2.Todate ' )
Set @DayNum = @DayNum + 1
End
FETCH NEXT FROM Cu_PartDate INTO @PartnumDate , @RevisionDate
END
END
CLOSE Cu_PartDate
DEALLOCATE Cu_PartDate
---------------查询用户设置的时间范围 ------------------------------
select t1.* into #tmpTodate From #ToDate t1 ,
(Select distinct PartnumRevision = Partnum+Revision
From #ToDate
Where Qnty>0 and ((TodayTime>=@BDate or @BDate='')
and (TodayTime <=@EDate or @EDate =''))) t2
Where (t1.Partnum+t1.Revision) = t2.PartnumRevision
------------------------------转换数据显示格式-----------------------------
Select top 0 Dateid, Partnum, Revision, Qnty1,Qnty2,Qnty3,Qnty4,Qnty5,Qnty6,Qnty7, Todate
Into #WeekProcedure
From Pdl_WeekProcedure
declare @str varchar(8000)
set @str =''
select @str =@str+',['+rtrim(DateStr)+']=max(case TodayTime when '''+rtrim(TodayTime)+''' then Qnty else '''' end)'
from #tmpTodate group by TodayTime,DateStr Order by DateStr
Select @str = Isnull(@str,'0,0,0,0,0,0,0')
If (Exists( select * from #tmpTodate)) and (@Flage = 0)
Begin
Insert Into #WeekProcedure(Dateid, Partnum, Revision, Qnty1,Qnty2,Qnty3,Qnty4,Qnty5,Qnty6,Qnty7, Todate)
exec('Select Dateid, Partnum, Revision '+@str+' ,Todate From #tmpTodate Group By Dateid, Partnum, Revision, Todate')
Update t1 Set t1.[A0]=t2.Qnty1, t1.[A1]=t2.Qnty2, t1.[A2]=t2.Qnty3, t1.DelDate =t2.Todate,
t1.[A3]=t2.Qnty4, t1.[A4]=t2.Qnty5, t1.[A5]=t2.Qnty6, t1.[A6]=t2.Qnty7
From #StknWipTotal t1, #WeekProcedure t2(nolock)
Where Ltrim(Rtrim(t1.PartNum)) = Ltrim(Rtrim(t2.PartNum))
and Ltrim(Rtrim(t1.Revision)) = Ltrim(Rtrim(t2.Revision))
and convert(datetime, @BDateTime) = t2.DateId
Select t1.*, BDateTime = convert(datetime, @BDateTime) , EDateTime =convert(datetime, @EDateTime)
From #StknWipTotal t1 , #WeekProcedure t2
Where t1.Partnum = t2.Partnum and t1.Revision = t2.Revision
End
Else
If @Flage = 1
Begin
Update t1 Set A0=Qnty1, A1=Qnty2, A2=Qnty3, A3=Qnty4, A4=Qnty5, A5=Qnty6, A6=Qnty7, t1.DelDate =t2.Todate
From #StknWipTotal t1, Pdl_WeekProcedure t2
Where Ltrim(Rtrim(t1.PartNum)) = Ltrim(Rtrim(t2.PartNum))
and Ltrim(Rtrim(t1.Revision)) = Ltrim(Rtrim(t2.Revision))
and convert(datetime, @BDateTime) = t2.DateId
Select t1.*, BDateTime = convert(datetime, @BDateTime) , EDateTime =convert(datetime, @EDateTime)
From #StknWipTotal t1
End
