Shulou(Shulou.com)06/02 Report--

USE MyDB

GO

/ *

*

* Author: Kinwar

* Create Date: 2015-4Mux

* Description: 1) summarize the retention data of the color yarn web page & KMIS-ODM and send E-mail notifications

* 2) automatically clear the color yarn of the web page & the expiration retention data of KMIS-ODM

*

* Parameters: 1) @ DelayDate removes the reservation for more than 40 days by default

* 2) how many days ahead of time does @ priorDate send mail? default is 5 days.

* 3) whether @ bIsSendEmail needs to send email by default is

* 4) whether @ bCleanPPCDyReserve clears the order retention data of the web page by default

* 5) whether @ bCleanPCDyReserve clears KMIS-ODM retention data by default

*

* * /

CREATE PROCEDURE USP_CheckDyReserveTimeout

@ DelayDate INT = 40

@ priorDate INT = 5

@ bIsSendEmail BIT = 0

@ bCleanPPCDyReserve BIT = 1

@ bCleanPCDyReserve BIT = 0

AS

BEGIN

/ *

DECLARE @ DelayDate INT = 40

DECLARE @ priorDate INT = 5

DECLARE @ bIsSendEmail BIT = 1

DECLARE @ bCleanPPCDyReserve BIT = 0

DECLARE @ bCleanPCDyReserve BIT = 0

, /

/ * Test mode * /

DECLARE @ bIsTestMode BIT = 1

DECLARE @ pSubjectText NVARCHAR

DECLARE @ strProfile_name NVARCHAR

DECLARE @ pBodyText NVARCHAR (max) =''

DECLARE @ pRecipients NVARCHAR (max) =''

DECLARE @ strHeadHTML NVARCHAR (MAX) =''

DECLARE @ strpcHTML NVARCHAR (MAX) =''

DECLARE @ strppcHTML NVARCHAR (MAX) =''

DECLARE @ MailSuffix NVARCHAR (20)

DECLARE @ strEmail NVARCHAR (2000) =''

DECLARE @ strCRLF NVARCHAR (10)

SET @ strCRLF = NCHAR (13) + NCHAR (10)

SET @ MailSuffix ='@ esquel.com'

SET NOCOUNT ON

/ * Summary data-- > e-mail * /

/ * in principle, one inventory corresponds to one cylinder number, so it is grouped by cylinder number * /

IF @ bIsSendEmail=1

BEGIN

PRINT CONVERT (NVARCHAR (10), GETDATE (), 120) + N' starts summarizing expired yarn reservation and sends E-Mail'+ @ strCRLF + @ strCRLF

/ * webpage with monochrome yarn retention summary * /

SELECT TOP 1000

Id = IDENTITY (INT,1,1)

MAX (a.Color_code) AS Color_code

A.Batch_no

SUM (a.Reserve_Qty) AS Reserve_Qty

MAX (a.Reserve_Time) AS Reserve_Time

A.PPO_NO

A.Operator

MAX (a.Operator) + @ MailSuffix AS OperatorMail

INTO # Temp_ppcDYReserve_Mail

FROM DB..ppcDyReserve a

INNER JOIN DB..yarntotalstore b ON a.Batch_no=b.Batch_No

WHERE a. Flagman stores K' AND a.Statusstores 1 'AND a. Batchbacks noisy NAccord A' AND (b.stockholders typewriter 'deposit' OR b.stockstocks typewriters' reserved') AND

B.Weight > 0 AND b.warehousewarecodecodes DY' AND b.yarnreservation sortsand DY' AND-- b.Reserve_Weight > 0 AND / * this condition cannot be added because the previous reservation is not synchronized.

DATEDIFF (DD, Reserve_Time, GETDATE ()) > @ DelayDate-@priorDate

GROUP BY a.PPO_NO, a.Batch_No, a.Operator

ORDER BY a.Operator, Reserve_Time DESC

SET @ strppcHTML =

N' [web tracking] retention data is about to expire:

'+

Nasty'+-indicates the size of the table frame, 0 means invisible, and 1 minus 2 pyrrine 3 in turn.

Nissan +

N 'serial number'-indicates that the header column will be centered in the cell and displayed in bold,--

N 'color number' +

N 'cylinder number' +

N 'retention weight' +

N 'retention time' +

N 'order number' +

N 'reservation operator' +

Noble Emurmail' +

CAST (

(SELECT

Td =''+ CONVERT (NVARCHAR (10), id) +',''

Td =''+ Color_code+'',''

Td =''+ Batch_no+'',''

Td =''+ CONVERT (NVARCHAR (20), Reserve_Qty) +',''

Td =''+ CONVERT (NVARCHAR (16), Reserve_Time,120) +',''

Td =''+ PPO_NO+'',''

Td =''+ Operator+'',''

Td =''+ OperatorMail+'',''

FROM # Temp_ppcDYReserve_Mail

-- ORDER BY Operator, Reserve_Time DESC

FOR XML PATH ('tr'), TYPE)

AS NVARCHAR (MAX)) +

Nasty'

SET @ strppcHTML=replace (replace (@ strppcHTML,''))

PRINT @ strppcHTML

PRINT CONVERT (NVARCHAR (10), GETDATE (), 120) + N' complete the summary of the expired color yarn on the web page.'+ @ strCRLF + @ strCRLF

/ * ODM color yarn retention summary * /

SELECT TOP 1000

Id = IDENTITY (INT,1,1)

A.Job_No

MAX (a.Gk_No) AS Gk_No

MAX (a.Yarn_Type) AS Yarn_Type

MAX (a.Yarn_Count) AS Yarn_Count

MAX (a.Color_Code) AS Color_Code

A.Batch_No

SUM (a.Reserve_Weight) AS Reserve_Weight_Count

A.Operator

MAX (a.Operator_Time) AS Operator_Time

MAX (a.Operator) + @ MailSuffix AS OperatorMail

INTO # Temp_pcDYReserve_Mail

FROM pcDYReserve a

INNER JOIN DB..yarntotalstore b ON a.Batch_no=b.Batch_No

WHERE b.Batchbacks Notification Notification A'A'AND (b.Stockstocks typewriters' hosting'OR b.Stockstocks typewriters' reserved') AND

A.Statusproof C'AND a.Taken_Weight=0 AND b.warehousepacking codewords DY 'AND b.yarnflowers sortsand DY' AND

B.Weight > 0 AND-- b.Reserve_Weight > 0 AND / * this condition cannot be added because the reservation of the web page has not been synchronized before.

DATEDIFF (DD, a.Update_Time, GETDATE ()) > @ DelayDate-@priorDate

GROUP BY a.Job_No, a.Batch_No, a.Operator

ORDER BY a.Operator, Operator_Time DESC

SET @ strpcHTML =

N' [KMIS-ODM] retention data is about to expire:

'+

Nasty'+-indicates the size of the table frame, 0 means invisible, and 1 minus 2 pyrrine 3 in turn.

Nissan +

N 'serial number'-indicates that the header column will be centered in the cell and displayed in bold,--

N 'order number' +

N 'product name' +

N 'yarn' +

N 'yarn count' +

N 'color number' +

N 'cylinder number' +

N 'retention weight' +

N 'reservation operator' +

N 'retention time' +

Noble Emurmail' +

CAST (

(SELECT

Td =''+ CONVERT (NVARCHAR (10), id) +',''

Td =''+ Job_No+'',''

Td =''+ Gk_No+'',''

Td =''+ Yarn_Type+'',''

Td =''+ Yarn_Count+'',''

Td =''+ Color_Code+'',''

Td =''+ Batch_No+'',''

Td =''+ CONVERT (NVARCHAR (20), Reserve_Weight_Count) +',''

Td =''+ Operator+'',''

Td =''+ CONVERT (NVARCHAR (16), Operator_Time,120) +',''

Td =''+ OperatorMail+'',''

FROM # Temp_pcDYReserve_Mail

-- ORDER BY Operator, Operator_Time

FOR XML PATH ('tr'), TYPE)

AS NVARCHAR (MAX)) +

Nasty'

SET @ strpcHTML=replace (replace (@ strpcHTML,''))

PRINT @ strpcHTML

PRINT CONVERT (NVARCHAR (10), GETDATE (), 120) + N' complete the summary of ODM expired yarn.'+ @ strCRLF + @ strCRLF

SET @ strHeadHTML = N'Dear All

'

SET @ strHeadHTML + = N'

This letter is sent automatically by the monitoring system.

'

SET @ strHeadHTML + = N'

For detailed statistics of color yarn retention, please visit our website for more information:

'

IF @ bIsTestMode=1

SET @ strHeadHTML + = Noble-> http://192.168.7.X/newweb/gkMIS/DyReserve/index.asp

'

ELSE

SET @ strHeadHTML + = Noble-> http://192.168.7.X/newweb/gkmis/DyReserve/index.asp

'

SET @ strHeadHTML + = N' the data that will be retained for expiration this time are as follows:

'

SET @ pBodyText = @ strHeadHTML + @ strppcHTML + @ strpcHTML

/ * Statistics mailing list & set SQL Profile_name * /

IF @ bIsTestMode=1

BEGIN

SET @ strEmail = 'XX@esquel.com'

SET @ strProfile_name = 'MSSQLProfile'

SET @ pSubjectText = N' * * Test status * * + CONVERT (NVARCHAR (10), GETDATE (), 120)

END

ELSE

BEGIN

SELECT @ strEmail + = OperatorMail +'; 'FROM (

SELECT DISTINCT OperatorMail FROM # Temp_ppcDYReserve_Mail

UNION ALL

SELECT DISTINCT OperatorMail FROM # Temp_pcDYReserve_Mail) a

SET @ strProfile_name = 'kmisdatabasemail'

SET @ pSubjectText = N''+ CONVERT (NVARCHAR (10), GETDATE (), 120)

END

PRINT CONVERT (NVARCHAR (10), GETDATE (), 120) + 'get mailing list.' + @ strCRLF + @ strCRLF + @ strEmail

/ * send email * /

EXEC msdb.dbo.sp_send_dbmail

@ profile_name = @ strProfile_name

@ recipients = @ strEmail

@ body = @ pBodyText

@ body_format = 'HTML'

@ subject = @ pSubjectText

PRINT CONVERT (NVARCHAR (10), GETDATE (), 120) + N' completes the summary of expired color yarn reservation and successfully sends Emael. '+ @ strCRLF + @ strCRLF

END

/ * the expired color yarn retention will be processed below, and the system will automatically clear it * /

The expired color yarn retention will be processed under PRINT CONVERT (NVARCHAR (10), GETDATE (), 120) + N'. The system will automatically clear the reservation.'+ @ strCRLF + @ strCRLF

/ * deal with the retention data in the web page order * /

IF @ bCleanPPCDyReserve=1

BEGIN

PRINT CONVERT (NVARCHAR (10), GETDATE (), 120) + N' begins to process the retention data in the web order.'+ @ strCRLF + @ strCRLF

/ * count the number of reservations on the web page by cylinder number * /

SELECT TOP 1000

A.Batch_no

SUM (a.Reserve_Qty) AS Reserve_Qty

INTO # CET_Temp_ppcDyReserve_Total

FROM YarnStoreDB..ppcDyReserve a

INNER JOIN YarnStoreDB..yarntotalstore b ON a.Batch_no=b.Batch_No

WHERE a. Flagman stores K' AND a.Statusstores 1 'AND a. Batchbacks noisy NAccord A' AND (b.stockholders typewriter 'deposit' OR b.stockstocks typewriters' reserved') AND

B.Weight > 0 AND b.Reserve_Weight > 0 AND b.warehousepacking codewords DY 'AND b.yarnflowers sortsand collecting DY' AND

DATEDIFF (DD, Reserve_Time, GETDATE ()) > @ DelayDate-@ DelayDate

GROUP BY a.Batch_No

ORDER BY a.Batch_No

BEGIN TRANSACTION Tran_ppcDyReserve

BEGIN TRY

/ * Update the public inventory table yarntotalstore * /

UPDATE YarnStoreDB.dbo.yarntotalstore

SET Reserve_Weight = CASE WHEN ISNULL (a.Reserveweight WeightMagne0)-ISNULL (b.Reserveworthy QtyMagin0) > 0 THEN

ISNULL (a.Reserveweight WeightMagne0)-ISNULL (b.Reserveworthy QtyPhone0)

ELSE 0 END

FROM YarnStoreDB.dbo.yarntotalstore a

INNER JOIN # CET_Temp_ppcDyReserve_Total b ON a.batch_NO=b.Batch_No

WHERE a. Batchstocks noisy Nash A'AND (a. Stockholders typewriter 'hosting' OR a.stockstocks typewriters' reserved') AND

A.Weight > 0 AND a.Reserve_Weight > 0 AND a. Warehousepacking codewords DY 'AND a.yarn sortstones

/ * clear expired PPC page retention data * /

DELETE FROM YarnStoreDB..ppcDyReserve

FROM YarnStoreDB..ppcDyReserve a

INNER JOIN YarnStoreDB..yarntotalstore b ON a.Batch_no=b.Batch_No

WHERE a. Flagman stores K' AND a.Statusstores 1 'AND a. Batchbacks noisy NAccord A' AND (b.stockholders typewriter 'deposit' OR b.stockstocks typewriters' reserved') AND

B.Weight > 0 AND b.Reserve_Weight > 0 AND b.warehousepacking codewords DY 'AND b.yarnflowers sortsand collecting DY' AND

DATEDIFF (DD, Reserve_Time, GETDATE ()) > @ DelayDate-@ DelayDate

COMMIT TRANSACTION Tran_ppcDyReserve

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER () AS ErrorNumber

ROLLBACK TRANSACTION Tran_ppcDyReserve

END CATCH

PRINT CONVERT (NVARCHAR (10), GETDATE (), 120) + N' begins to process the retention data in the web page.'+ @ strCRLF + @ strCRLF

END

/ * processing retention data in ODM * /

IF @ bCleanPCDyReserve=1

BEGIN

PRINT CONVERT (NVARCHAR (10), GETDATE (), 120) + N' starts processing retention data in ODM.'+ @ strCRLF + @ strCRLF

/ * count ODM reservation by cylinder number * /

SELECT TOP 1000

A.Batch_No

SUM (a.Reserve_Weight) AS Reserve_Weight_Count

INTO # CET_Temp_pcDYReserve_Total

FROM pcDYReserve a

INNER JOIN YarnStoreDB..yarntotalstore b ON a.Batch_no=b.Batch_No

WHERE b.Batchbacks Notification Notification A'A'AND (b.Stockstocks typewriters' hosting'OR b.Stockstocks typewriters' reserved') AND

A.Statusproof C' AND a.Taken_Weight=0 AND

B.Weight > 0 AND b.Reserve_Weight > 0 AND b.warehousepacking codewords DY 'AND b.yarnflowers sortsand collecting DY' AND

DATEDIFF (DD, a.Update_Time, GETDATE ()) > 40

GROUP BY a.Batch_No

ORDER BY a.Batch_No

BEGIN TRANSACTION Tran_pcDyReserve

BEGIN TRY

/ * update the reservation pcArrangeDetail of the scheduling details * /

UPDATE pcArrangeDetail

-- SET DY_Reserved_Qty = DY_Reserved_Qty-c.Reserve_Weight

SET DY_Reserved_Qty = 0

FROM pcArrangeMain a

INNER JOIN pcArrangeDetail b ON a.Job_ID=b.Job_ID

INNER JOIN dbo.pcDYReserve c ON b.Job_Item_Id=c.Job_Item_Id

INNER JOIN # CET_Temp_pcDYReserve_Total d ON c.Batch_No=d.Batch_No

WHERE b.Dy_Plan_Qty > 0 AND b.Closedflowers Y'AND

ISNULL (a. Confirmed dint')'C 'AND c.Reserve_Weight > 0 AND

B. Coloured Codebooks GREY 'AND c. Statusbands C' AND c.Taken_Weight=0 AND c. Batchbacks Notification Ndeband A'AND

DATEDIFF (DD, c.Update_Time, GETDATE ()) > @ DelayDate

/ * Update the public inventory table yarntotalstore * /

UPDATE YarnStoreDB.dbo.yarntotalstore

SET Reserve_Weight = CASE WHEN ISNULL (a.Reserveweight WeightGrain0)-ISNULL (b.ReservewaterWeightweight Count0) > 0 THEN

ISNULL (a.Reserveweight WeightMagne0)-ISNULL (b.ReservewaterWeightWeightCountDifferent 0)

ELSE 0 END

FROM YarnStoreDB.dbo.yarntotalstore a

INNER JOIN # CET_Temp_pcDYReserve_Total b ON a.batch_NO=b.Batch_No

WHERE a. Batchstocks Notification Nash A'AND (a. Stockholders typewriter 'hosting' OR a.stockstocks typewriters' reserved') AND

A.Weight > 0 AND a.Reserve_Weight > 0 AND a. Warehousepacking codewords DY 'AND a.yarn sortstones

/ * Update cancellation identity * /

UPDATE pcDyReserve SET Status='C'

FROM pcDYReserve a

INNER JOIN YarnStoreDB..yarntotalstore b ON a.Batch_no=b.Batch_No

WHERE b.Batchbacks Notification Notification A'A'AND (b.Stockstocks typewriters' hosting'OR b.Stockstocks typewriters' reserved') AND

A.Statusproof C' AND a.Taken_Weight=0 AND

B.Weight > 0 AND b.Reserve_Weight > 0 AND b.warehousepacking codewords DY 'AND b.yarnflowers sortsand collecting DY' AND

DATEDIFF (DD, a.Update_Time, GETDATE ()) > 40

COMMIT TRANSACTION Tran_pcDyReserve

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER () AS ErrorNumber

ROLLBACK TRANSACTION Tran_pcDyReserve

END CATCH

END

/ * clear the temporary table * /

IF @ bIsSendEmail=1

BEGIN

DROP TABLE # Temp_ppcDYReserve_Mail

DROP TABLE # Temp_pcDYReserve_Mail

END

IF @ bCleanPPCDyReserve=1 DROP TABLE # CET_Temp_ppcDyReserve_Total

IF @ bCleanPCDyReserve=1 DROP TABLE # CET_Temp_pcDYReserve_Total

PRINT CONVERT (NVARCHAR (10), GETDATE (), 120) + N' processing complete.'+ @ strCRLF + @ strCRLF

SET NOCOUNT OFF

END

GO

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.