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'
序号 | '+ ---- | 表示标题列将在单元格中居中并以粗体显示，-- | 色号 | '+ 缸号 | ' + 留位重量 | ' + 留位时间 | '+ 订单号 | '+ 留位操作人 | ' +
|---|
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'
序号 | '+ ---- | 表示标题列将在单元格中居中并以粗体显示，-- | 排单号 | '+ 品名 | ' + 纱类 | ' + 纱支 | ' + 色号 | ' + 缸号 | ' +留位重量 | ' +留位操作人 | ' + 留位时间 | ' +
|---|
SET @strpcHTML=replace(replace(@strpcHTML,'<','<'),'>','>')
PRINT @strpcHTML
PRINT CONVERT(NVARCHAR(10),GETDATE(),120) + N' 完成汇总 ODM 到期的色纱... ' + @strCRLF + @strCRLF
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