Change the display content in maintenance date. For example, today is No. 21. Maintaining the data of seven days, 21-28. When opening the maintenance interface on the 22nd, the maintenance content is displayed as 22-9 data and the date. Create maintenance table: ..
----------------- Create a data record table -------------------------
if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [PDL_WeekProcedure]') And ObjectProperty (ID, n'susertable ') = 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'uSERTABLE ') = 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
----------------- Create a date --------------------
If Exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [PDL_WeekProcedate]') And ObjectProperty (ID, n'uSERTABLE ') = 1)
DROP TABLE PDL_WEEKPROCEDATEDATE
Go
IF not exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [PDL_WeekProcedate]') And ObjectProperty (ID, n'susertable ') = 1)
Begin
CREATE TABLE PDL_WEEKPROCEDATE (
[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 nullconstraint [pk_weekproceduredate] Primary Key Clustered
(
[ID]
) On [primary]
) On [primary]
End
----------- Create a weekly delivery schedule ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -
IF exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [PDL_WeekProchistory]) and ObjectProperty (id, n'uSERTABLE') = 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'susertable') = 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
Update Maintenance Table Date Data: / *
Procedure Name: Delivery Scheduling Data Update
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, @ 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
-------------- Correct Update to push 7 days after the day --------
IF not exists (SELECT * FROM PDL_WEEKPROCEDATEDATE
WHERE 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)
---------- Transport the data in the table PDL_WEEKPROCEDURE to the latest data "corresponding program maintenance interface" -
SELECT @num = isnull (datediff (select min (dateid) from pop pdl_weekprocedure,
(Select Max (dateid) from PDL_WeekProcedate)), 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 a week delivery schedule Maintenance history ------------
INSERT INTO PDL_WEEKPROCHISTORY (dateid, partnum, revision, qnty1, qnty2, qnty3, qnty4, qnty5, qnty6, qnty7, date1, date2, date3, date4, date5, date6, date7, otime, outputdate
Select T1.DateID, T1.Partnum, T1.Revision, T1.QNTY1, T1.QNTY2, T1.QNTY3, T1.QNTY4, T1.QNTY5, T1.QNTY6, T1.QNTY7,
T2.DATE11, T2.DATE2, T2.DATE3, T2.DATE4, T2.DATE5, T2.DATE6, T2.DATE7, GETDATE (), T1.TODATE
From PDL_WeekProcedure T1 (NOLOCK), PDL_WEEKPROCEDATETE T2 (NOLOCK)
Where t1.dateid = t2.dateID
------------------------ Update PDL_WEEKPROCEDURE Data ----------------------- -----
EXEC ('update PDL_WeekProcedure Set' @str)
Update PDL_WeekProcedure
Set dateid = (Select Max (dateid) from PDL_WeekProcedate)
------------------------- Update the day shipping time field ----------------- -------
Update PDL_WeekProcedure Set Todate = NULL
WHERE Convert (Varchar, Todate, 101)) <> Convert (DateTime, Convert (VARCHAR, GETDATE (), 101))
End
--delete pdl_weekprocedatedate where dateid = '2005-01-15 00: 00:00.000'
- Update PDL_WeekProcedure Set Dateid = Convert (Datetime, Convert (Varchar, Getdate (), 101) - 1
Generate the last data collection:
/ *
Procedure Name: A week delivery schedule
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 DATIME
--Set @Bdate = isnull (@BDATE, GETDATE ())
--Set @EDATE = isnull (@EDATE, GETDATE () 6)
------------- Call Update Maintenance Record Table Data Store -------------------
EXEC PDL_WEEKPROCDATEUPDATE
SELECT @BDateTime = Convert (DateTime, Convert (varchar, getdate (), 101))
SELECT @EDATETIME = Convert (DateTime, Convert (varchar, @BDatetime 7, 101))
/ * Take a weekly shipping status data table structure * /
Select Top 0 Partnum, Revision, Qnty = 0, Weekdate = DATEID, COM = 'A9' - = Convert (VARCHAR (5), Weekdate, 101)
INTO #Weekconfig
From PDL_WeekProcedure (NOLOCK)
/ * In Product Data * /
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 @ 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 integrity generation * /
Declare @checkpartnum varchar (20), @CheckRevision varchar (5)
Declare @wipproccocode 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 @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 @wipproccode, @wipid
End
End
Close Checkwip
Deallocate Checkwip
/ * Total number of products * /
Select Partnum, Revision, Qnty = SUM (QNTY),
Deldate, UPP, Proccode = Name, Idinto #stknwip
From #tmpstknwip
GROUP BY PartNum, Revision, Deldate, UPP, ID, NAME
ORDER BY ID
/ * Judgment integrity * /
IF (NOT EXISTS (SELECT * ") and (select * from #stknwip)))
Begin
INSERT INTO #stknwip (Partnum, Revision, Qnty, Proccode, ID)
SELECT 'NOT DATA', 'NO', 0, Procode = 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, Procode = 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 @ inepartnum = '')))
AND ((t1.revision = @inbrevision or @INBREVISION = ')
AND (t1.revision = @inerevision or @inerevision = ')))))
End
/ * Generate delivery status time format and integrity generation * /
Declare @Partnum varchar (20), @Revision var, @datecount Int
Declare @Weekdate DateTime, @com varchar (200)
SELECT @com = 'a'
Declare AddDatelist Insensitive Cursorfor
Select Partnum, Revision from #weekconfig
GROUP BY PartNum, Revision
Open adddatelist
Begin
Fetch next from addddatelist @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 addddatelist @Partnum, @Revision
End
End
Close AddDDateList
Deallocate AddDatelist
/ * Biometric cross data set * /
INSERT INTO #stknwip (Partnum, Revision, Qnty, Proccode, ID)
Select Partnum, Revision, Qnty, COM, '1A'
From #weekconfig
Where weekdate> = @Bdatetime and weekdate <= @EDatetimedeclare @stkpartnum varchar (20), @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 @StkpartNum, @stkrevision
While @@ fetch_status = 0
Begin
Select @stkdeldate = min (delivery), @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 @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) ' '' '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'uSERTABLE ') = 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_TMPWEEKPROCSELECT 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
------------------------------------ Add the position of the position ----------- -------------------
Exec ('alter Table #stknwiptotal add [inWarehouse] int not null default (0)')
--------- Modify the number of warents ----------
Update T1 set t1.inwarehouse = isnull (t2.pqnty, 0) from #stknwiptotal t1, #inwarehouse t2
Where t1.partnum = t2.partnum
------------ Modify maintenance data, convenient time limit range -------------------------
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 co_partdate
Begin
Fetch next from cu_partdate @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_WeekProcedate 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 @PartNumdate, @RevisionDate
End
End
Close Cu_PartDate
Deallocate Cu_PartDate
--------------- Query the time range of user settings --------------------------- -
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
------------------------------ Conversion Data Display Format ------------------------------------------------------------------------------------------------------------ -------------
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) ' '' Ten 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.QNNTY6, 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
