SQLServer发送HTML格式邮件（事务）

USE MyDB
GO

创新互联公司-专业网站定制、快速模板网站建设、高性价比靖西网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式靖西网站制作公司更省心,省钱,快速模板网站建设找我们，业务覆盖靖西地区。费用合理售后完善，10余年实体公司更值得信赖。

/**********************************************************************************
*
* 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 = 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.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'' +
   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) ) +
   N'

序号	--	--	色号	缸号	留位重量	留位时间	订单号	留位操作人	E-mail

' ;

SET @strppcHTML=replace(replace(@strppcHTML,'<','<'),'>','>')
PRINT @strppcHTML

PRINT CONVERT(NVARCHAR(10),GETDATE(),120) + N' 完成汇总网页到期的色纱... ' + @strCRLF + @strCRLF

  /* ODM 色纱留位汇总 */
  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.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'' +
   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) ) +
   N'

序号	--	--	排单号	品名	纱类	纱支	色号	缸号	留位重量	留位操作人	留位时间	E-mail

' ;

  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

网站题目：SQLServer发送HTML格式邮件（事务）
网页路径：http://scyanting.com/article/piceph.html