SQL Server发送HTML格式邮件（事务）

jinying8869

　　USE MyDB
　　GO
　　/**********************************************************************************
　　*
　　* Author:  Kinwar
　　* Create Date: 2015-4-X
　　* Description: 1) 汇总色纱网页 & KMIS-ODM 的留位数据 并派送 E-mail 通知
　　*                     2) 自动清除网页色纱 & KMIS-ODM 的到期留位数据
　　*
　　* Parameters: 1) @DelayDate   清除超过多少天的留位  默认 40 天
　　*    2) @priorDate   提前多少天发送邮件   默认 5 天
　　*    3) @bIsSendEmail  是否需要发送邮件   默认 是
　　*    4) @bCleanPPCDyReserve 是否清除网页跟单留位数据 默认 是
　　*    5) @bCleanPCDyReserve 是否清除 KMIS-ODM 留位数据 默认 是
　　*
　　**********************************************************************************/
　　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
　　*/
　　/* 测试模式 */
　　DECLARE @bIsTestMode  BIT = 1
　　DECLARE @pSubjectText  NVARCHAR(255) = ''
　　DECLARE @strProfile_name NVARCHAR(255) = ''
　　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;
　　/* 汇总资料 --> e-mail */
　　/* 原则上一个库存对应一个缸号, 故以缸号分组 */
　　IF @bIsSendEmail=1
　　BEGIN
　　PRINT CONVERT(NVARCHAR(10),GETDATE(),120) + N' 开始汇总到期的色纱留位并发送 E-Mail ' + @strCRLF + @strCRLF
　　/* 网页 跟单色纱留位 汇总 */
　　SELECT TOP 1000

　　id =>　　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.Flag='K' AND a.Status='1' AND a.Batch_no'N/A' AND (b.Stock_Type='寄存' OR b.Stock_Type='留用') AND
　　b.Weight>0 AND b.warehouse_code='DY' AND b.yarn_sort='DY' AND --b.Reserve_Weight>0 AND /* 由于之前网页留位没有同步过来,所以不能加这个条件 */
　　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'[网页跟单] 留位即将到期数据：' +
　　N'' +               --表示表边框大细，0表示不可见,1,2,3依次小到大
　　N''+
　　N'序号'+              ----表示标题列将在单元格中居中并以粗体显示，--
　　N'色号'+
　　N'缸号' +
　　N'留位重量' +
　　N'留位时间'+
　　N'订单号'+
　　N'留位操作人' +
　　N'E-mail' +
　　CAST (
　　(SELECT

　　td = '
　　td = '
　　td = '
　　td = '
　　td = '
　　td = '
　　td = '
　　td = '　　FROM #Temp_ppcDYReserve_Mail
　　--ORDER BY Operator, Reserve_Time DESC
　　FOR XML PATH('tr'), TYPE )
　　AS NVARCHAR(MAX) ) +
　　N'' ;
　　SET @strppcHTML=replace(replace(@strppcHTML,'<','')
　　PRINT @strppcHTML
　　PRINT CONVERT(NVARCHAR(10),GETDATE(),120) + N' 完成汇总网页到期的色纱... ' + @strCRLF + @strCRLF
　　/* ODM 色纱留位 汇总 */
　　SELECT TOP 1000

　　id =>　　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.Batch_No'N/A' AND (b.Stock_Type='寄存' OR b.Stock_Type='留用') AND
　　a.Status'C' AND a.Taken_Weight=0 AND b.warehouse_code='DY' AND b.yarn_sort='DY' AND
　　b.Weight>0 AND  --b.Reserve_Weight>0 AND /* 由于之前网页留位没有同步过来,所以不能加这个条件 */
　　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] 留位即将到期数据：' +
　　N'' +             --表示表边框大细，0表示不可见,1,2,3依次小到大
　　N''+
　　N'序号'+            ----表示标题列将在单元格中居中并以粗体显示，--
　　N'排单号'+
　　N'品名' +
　　N'纱类' +
　　N'纱支' +
　　N'色号' +
　　N'缸号' +
　　N'留位重量' +
　　N'留位操作人' +
　　N'留位时间' +
　　N'E-mail' +
　　CAST (
　　(SELECT

　　td = '
　　td = '
　　td = '
　　td = '
　　td = '
　　td = '
　　td = '
　　td = '
　　td = '
　　td = '
　　td = '　　FROM #Temp_pcDYReserve_Mail
　　--ORDER BY Operator, Operator_Time
　　FOR XML PATH('tr'), TYPE )
　　AS NVARCHAR(MAX) ) +
　　N'' ;
　　SET @strpcHTML=replace(replace(@strpcHTML,'<','')
　　PRINT @strpcHTML
　　PRINT CONVERT(NVARCHAR(10),GETDATE(),120) + N' 完成汇总 ODM 到期的色纱... ' + @strCRLF + @strCRLF
　　SET @strHeadHTML  = N'Dear All, '
　　SET @strHeadHTML += N'本信件由  监控系统自动发送。'
　　SET @strHeadHTML += N'详细色纱留位统计数据，可连进本公司网址查询：'
　　IF @bIsTestMode=1
　　SET @strHeadHTML += N'--> http://192.168.7.X/newweb/gkMIS/DyReserve/index.asp '
　　ELSE
　　SET @strHeadHTML += N'--> http://192.168.7.X/newweb/gkmis/DyReserve/index.asp '
　　SET @strHeadHTML += N'本次统计即将留位到期数据如下： '
　　SET @pBodyText = @strHeadHTML + @strppcHTML + @strpcHTML
　　/* 统计邮件列表 & 设置 SQL Profile_name */
　　IF @bIsTestMode=1
　　BEGIN
　　SET @strEmail   = 'XX@esquel.com'
　　SET @strProfile_name  = 'MSSQLProfile'
　　SET @pSubjectText = N' **测试状态** ' + 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) + ' 获取邮件列表... ' + @strCRLF + @strCRLF + @strEmail
　　/* 发送邮件 */
　　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' 完成汇总到期的色纱留位并成功发送 E-Mail... ' + @strCRLF + @strCRLF
　　END
　　/* 下面开始处理到期的色纱留位，系统自动清除 */
　　PRINT CONVERT(NVARCHAR(10),GETDATE(),120) + N' 下面开始处理到期的色纱留位，系统将自动清除留位... ' + @strCRLF + @strCRLF
　　/* 处理 网页跟单 中的留位数据 */
　　IF @bCleanPPCDyReserve=1
　　BEGIN
　　PRINT CONVERT(NVARCHAR(10),GETDATE(),120) + N' 开始处理 网页跟单 中的留位数据...' + @strCRLF + @strCRLF
　　/* 以缸号 统计 网页留位数量 */
　　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.Flag='K' AND a.Status='1' AND a.Batch_no'N/A' AND (b.Stock_Type='寄存' OR b.Stock_Type='留用') AND
　　b.Weight>0 AND b.Reserve_Weight>0 AND b.warehouse_code='DY' AND b.yarn_sort='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
　　/* 更新公共库存表 yarntotalstore */
　　UPDATE YarnStoreDB.dbo.yarntotalstore
　　SET  Reserve_Weight = CASE WHEN ISNULL(a.Reserve_Weight,0) - ISNULL(b.Reserve_Qty,0) > 0 THEN
　　ISNULL(a.Reserve_Weight,0) - ISNULL(b.Reserve_Qty,0)
　　ELSE 0 END
　　FROM YarnStoreDB.dbo.yarntotalstore    a
　　INNER JOIN #CET_Temp_ppcDyReserve_Total  b ON a.batch_NO=b.Batch_No
　　WHERE  a.Batch_no'N/A' AND (a.Stock_Type='寄存' OR a.Stock_Type='留用') AND
　　a.Weight>0 AND a.Reserve_Weight>0 AND a.warehouse_code='DY' AND a.yarn_sort='DY'
　　/* 清除到期的 PPC网页留位 数据 */
　　DELETE FROM YarnStoreDB..ppcDyReserve
　　FROM YarnStoreDB..ppcDyReserve    a
　　INNER JOIN YarnStoreDB..yarntotalstore b ON a.Batch_no=b.Batch_No
　　WHERE a.Flag='K' AND a.Status='1' AND a.Batch_no'N/A' AND (b.Stock_Type='寄存' OR b.Stock_Type='留用') AND
　　b.Weight>0 AND b.Reserve_Weight>0 AND b.warehouse_code='DY' AND b.yarn_sort='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' 开始处理 网页跟单 中的留位数据完成....' + @strCRLF + @strCRLF
　　END
　　/* 处理 ODM 中的留位数据 */
　　IF @bCleanPCDyReserve=1
　　BEGIN
　　PRINT CONVERT(NVARCHAR(10),GETDATE(),120) + N' 开始处理 ODM 中的留位数据....' + @strCRLF + @strCRLF
　　/* 以缸号 统计 ODM 留位数量 */
　　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.Batch_No'N/A' AND (b.Stock_Type='寄存' OR b.Stock_Type='留用') AND
　　a.Status'C' AND a.Taken_Weight=0 AND
　　b.Weight>0 AND b.Reserve_Weight>0 AND b.warehouse_code='DY' AND b.yarn_sort='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
　　/* 更新排单明细的留位pcArrangeDetail */
　　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.Closed'Y' AND
　　ISNULL(a.Confirmed,'')'C' AND c.Reserve_Weight>0 AND
　　b.Color_Code'GREY' AND c.Status'C' AND c.Taken_Weight=0 AND c.Batch_No'N/A' AND
　　DATEDIFF(DD, c.Update_Time, GETDATE())>@DelayDate
　　/* 更新公共库存表 yarntotalstore */
　　UPDATE YarnStoreDB.dbo.yarntotalstore
　　SET  Reserve_Weight = CASE WHEN ISNULL(a.Reserve_Weight,0) - ISNULL(b.Reserve_Weight_Count,0) > 0 THEN
　　ISNULL(a.Reserve_Weight,0) - ISNULL(b.Reserve_Weight_Count,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.Batch_No'N/A' AND (a.Stock_Type='寄存' OR a.Stock_Type='留用') AND
　　a.Weight>0 AND a.Reserve_Weight>0 AND a.warehouse_code='DY' AND a.yarn_sort='DY'
　　/* 更新取消标识 */
　　UPDATE pcDyReserve SET Status='C'
　　FROM pcDYReserve        a
　　INNER JOIN YarnStoreDB..yarntotalstore b ON a.Batch_no=b.Batch_No
　　WHERE b.Batch_No'N/A' AND (b.Stock_Type='寄存' OR b.Stock_Type='留用') AND
　　a.Status'C' AND a.Taken_Weight=0 AND
　　b.Weight>0 AND b.Reserve_Weight>0 AND b.warehouse_code='DY' AND b.yarn_sort='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
　　/* 清空临时表 */
　　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' 处理完成...' + @strCRLF + @strCRLF
　　SET NOCOUNT OFF;
　　END
　　GO