SQL Server sends messages in HTML format (transaction)
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
