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

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
@DelayDateINT= 40,
@priorDateINT= 5,
@bIsSendEmailBIT= 0,
@bCleanPPCDyReserveBIT= 1,
@bCleanPCDyReserveBIT= 0
ASBEGIN/*
DECLARE @DelayDateINT= 40
DECLARE @priorDateINT= 5
DECLARE @bIsSendEmailBIT= 1
DECLARE @bCleanPPCDyReserveBIT= 0
DECLARE @bCleanPCDyReserveBIT= 0
*/

/* 测试模式 */
DECLARE @bIsTestModeBIT= 1DECLARE @pSubjectTextNVARCHAR(255) = ”
DECLARE @strProfile_nameNVARCHAR(255) = ”DECLARE @pBodyTextNVARCHAR(max) = ”
DECLARE@pRecipientsNVARCHAR(max) = ”DECLARE @strHeadHTMLNVARCHAR(MAX) = ”
DECLARE @strpcHTMLNVARCHAR(MAX) = ”
DECLARE @strppcHTMLNVARCHAR(MAX) = ”DECLARE@MailSuffixNVARCHAR(20)
DECLARE @strEmailNVARCHAR(2000) = ”DECLARE @strCRLFNVARCHAR(10)
SET @strCRLF = NCHAR(13) + NCHAR(10)SET @MailSuffix = ‘@esquel.com’

SET NOCOUNT ON;/* 汇总资料 –> e-mail */
/* 原则上一个库存对应一个缸号, 故以缸号分组 */
IF @bIsSendEmail=1
BEGINPRINT CONVERT(NVARCHAR(10),GETDATE(),120) + N’ 开始汇总到期的色纱留位并发送 E-Mail ‘ + @strCRLF + @strCRLF
/* 网页 跟单色纱留位 汇总 */
SELECTTOP 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
FROMDB..ppcDyReservea
INNER JOIN DB..yarntotalstoreb ON a.Batch_no=b.Batch_No
WHEREa.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 DESCSET @strppcHTML =
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’

‘ ;SET @strppcHTML=replace(replace(@strppcHTML,'<‘,”)
PRINT @strppcHTMLPRINT CONVERT(NVARCHAR(10),GETDATE(),120) + N’ 完成汇总网页到期的色纱… ‘ + @strCRLF + @strCRLF
/* ODM 色纱留位 汇总 */
SELECTTOP 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
FROMpcDYReservea
INNER JOIN DB..yarntotalstoreb ON a.Batch_no=b.Batch_No
WHEREb.Batch_No’N/A’ AND (b.Stock_Type=’寄存’ OR b.Stock_Type=’留用’) AND
a.Status’C’ AND a.Taken_Weight=0AND 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 DESCSET @strpcHTML =
N’

[KMIS-ODM] 留位即将到期数据：

‘ +
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’

‘ ;SET @strpcHTML=replace(replace(@strpcHTML,'<‘,”)
PRINT @strpcHTML
PRINT CONVERT(NVARCHAR(10),GETDATE(),120) + N’ 完成汇总 ODM 到期的色纱… ‘ + @strCRLF + @strCRLFSET @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= @pSubjectTextPRINT 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

/* 以缸号 统计 网页留位数量 */
SELECTTOP 1000
a.Batch_no,
SUM(a.Reserve_Qty)AS Reserve_Qty
INTO#CET_Temp_ppcDyReserve_Total
FROMYarnStoreDB..ppcDyReservea
INNER JOIN YarnStoreDB..yarntotalstoreb ON a.Batch_no=b.Batch_No
WHEREa.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 */
UPDATEYarnStoreDB.dbo.yarntotalstore
SETReserve_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
FROMYarnStoreDB.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网页留位 数据 */
DELETEFROM YarnStoreDB..ppcDyReserve
FROMYarnStoreDB..ppcDyReservea
INNER JOIN YarnStoreDB..yarntotalstoreb ON a.Batch_no=b.Batch_No
WHEREa.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 + @strCRLFEND
/* 处理 ODM 中的留位数据 */
IF @bCleanPCDyReserve=1
BEGINPRINT CONVERT(NVARCHAR(10),GETDATE(),120) + N’ 开始处理 ODM 中的留位数据….’ + @strCRLF + @strCRLF
/* 以缸号 统计 ODM 留位数量 */
SELECTTOP 1000
a.Batch_No,
SUM(a.Reserve_Weight) AS Reserve_Weight_Count
INTO#CET_Temp_pcDYReserve_Total
FROMpcDYReservea
INNER JOIN YarnStoreDB..yarntotalstoreb ON a.Batch_no=b.Batch_No
WHEREb.Batch_No’N/A’ AND (b.Stock_Type=’寄存’ OR b.Stock_Type=’留用’) AND
a.Status’C’ AND a.Taken_Weight=0AND
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
–SETDY_Reserved_Qty = DY_Reserved_Qty-c.Reserve_Weight
SETDY_Reserved_Qty = 0
FROMpcArrangeMain 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
WHEREb.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 */
UPDATEYarnStoreDB.dbo.yarntotalstore
SETReserve_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
FROMYarnStoreDB.dbo.yarntotalstore a
INNER JOIN #CET_Temp_pcDYReserve_Total b ON a.batch_NO=b.Batch_No
WHEREa.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’

/* 更新取消标识 */
UPDATEpcDyReserve SET Status=’C’
FROMpcDYReservea
INNER JOIN YarnStoreDB..yarntotalstoreb ON a.Batch_no=b.Batch_No
WHEREb.Batch_No’N/A’ AND (b.Stock_Type=’寄存’ OR b.Stock_Type=’留用’) AND
a.Status’C’ AND a.Taken_Weight=0AND
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
ENDIF @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;ENDGO

相关推荐: xp开机如何进入dos

本篇内容主要讲解“xp开机如何进入dos”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“xp开机如何进入dos”吧!xp开机怎么进入dos1.开启电脑，在出现屏幕第一屏的时候，按键盘上的“F8”键，进入Windows…