真实的国产乱ⅩXXX66竹夫人,五月香六月婷婷激情综合,亚洲日本VA一区二区三区,亚洲精品一区二区三区麻豆

成都創(chuàng)新互聯(lián)網(wǎng)站制作重慶分公司

SQLServer發(fā)送HTML格式郵件（事務(wù)）

USE MyDB
GO

創(chuàng)新互聯(lián)公司-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價比靖西網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式靖西網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋靖西地區(qū)。費用合理售后完善，10余年實體公司更值得信賴。

/**********************************************************************************
 * 
 * Author:  Kinwar
 * Create Date: 2015-4-X
 * Description: 1) 匯總色紗網(wǎng)頁 & KMIS-ODM 的留位數(shù)據(jù) 并派送 E-mail 通知
 *                     2) 自動清除網(wǎng)頁色紗 & KMIS-ODM 的到期留位數(shù)據(jù)
 *
 * Parameters: 1) @DelayDate   清除超過多少天的留位  默認(rèn) 40 天
 *    2) @priorDate   提前多少天發(fā)送郵件   默認(rèn) 5 天
 *    3) @bIsSendEmail  是否需要發(fā)送郵件   默認(rèn) 是
 *    4) @bCleanPPCDyReserve 是否清除網(wǎng)頁跟單留位數(shù)據(jù) 默認(rèn) 是
 *    5) @bCleanPCDyReserve 是否清除 KMIS-ODM 留位數(shù)據(jù) 默認(rèn) 是
 *
**********************************************************************************/

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 */
 /* 原則上一個庫存對應(yīng)一個缸號, 故以缸號分組 */
 IF @bIsSendEmail=1
 BEGIN  

  PRINT CONVERT(NVARCHAR(10),GETDATE(),120) + N' 開始匯總到期的色紗留位并發(fā)送 E-Mail ' + @strCRLF + @strCRLF


  /* 網(wǎng)頁 跟單色紗留位 匯總 */
  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 /* 由于之前網(wǎng)頁留位沒有同步過來,所以不能加這個條件 */
    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'

[網(wǎng)頁跟單] 留位即將到期數(shù)據(jù)：
' +
             
    N'' +               --表示表邊框大細(xì)，0表示不可見,1,2,3依次小到大                               
    N''+
    N''+              --表示標(biāo)題列將在單元格中居中并以粗體顯示，
    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' 完成匯總網(wǎng)頁到期的色紗... ' + @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 /* 由于之前網(wǎng)頁留位沒有同步過來,所以不能加這個條件 */
    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] 留位即將到期數(shù)據(jù)：
' +        
    N'' +             --表示表邊框大細(xì)，0表示不可見,1,2,3依次小到大                               
    N''+
    N''+            --表示標(biāo)題列將在單元格中居中并以粗體顯示，
    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'

本信件由 <<色紗網(wǎng)頁留位>> 監(jiān)控系統(tǒng)自動發(fā)送。
'
  SET @strHeadHTML += N'

詳細(xì)色紗留位統(tǒng)計數(shù)據(jù)，可連進(jìn)本公司網(wǎng)址查詢：
'    
  
  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'
本次統(tǒng)計即將留位到期數(shù)據(jù)如下：
'

  SET @pBodyText = @strHeadHTML + @strppcHTML + @strpcHTML
   

  /* 統(tǒng)計郵件列表 & 設(shè)置 SQL Profile_name */
  IF @bIsTestMode=1
  BEGIN
    SET @strEmail   = 'XX@esquel.com'
    SET @strProfile_name  = 'MSSQLProfile'
    SET @pSubjectText = N'<<<溢達(dá) [色紗留位] 監(jiān)控系統(tǒng)警示通知>>> **測試狀態(tài)** ' + 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'<<<溢達(dá) [色紗留位] 監(jiān)控系統(tǒng)警示通知>>> ' + CONVERT(NVARCHAR(10), GETDATE(), 120)
  END
  
  PRINT CONVERT(NVARCHAR(10),GETDATE(),120) + ' 獲取郵件列表... ' + @strCRLF + @strCRLF + @strEmail

  /* 發(fā)送郵件 */
  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' 完成匯總到期的色紗留位并成功發(fā)送 E-Mail... ' + @strCRLF + @strCRLF
  
 END

 /* 下面開始處理到期的色紗留位，系統(tǒng)自動清除 */

 PRINT CONVERT(NVARCHAR(10),GETDATE(),120) + N' 下面開始處理到期的色紗留位，系統(tǒng)將自動清除留位... ' + @strCRLF + @strCRLF


 /* 處理 網(wǎng)頁跟單 中的留位數(shù)據(jù) */
 IF @bCleanPPCDyReserve=1
 BEGIN
    
  PRINT CONVERT(NVARCHAR(10),GETDATE(),120) + N' 開始處理 網(wǎng)頁跟單 中的留位數(shù)據(jù)...' + @strCRLF + @strCRLF
  
    
  /* 以缸號 統(tǒng)計 網(wǎng)頁留位數(shù)量 */
  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網(wǎng)頁留位 數(shù)據(jù) */
   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' 開始處理 網(wǎng)頁跟單 中的留位數(shù)據(jù)完成....' + @strCRLF + @strCRLF

 END


 /* 處理 ODM 中的留位數(shù)據(jù) */
 IF @bCleanPCDyReserve=1
 BEGIN

  PRINT CONVERT(NVARCHAR(10),GETDATE(),120) + N' 開始處理 ODM 中的留位數(shù)據(jù)....' + @strCRLF + @strCRLF
 

  /* 以缸號 統(tǒng)計 ODM 留位數(shù)量 */
  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

   /* 更新排單明細(xì)的留位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'
     
   /* 更新取消標(biāo)識 */
   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


當(dāng)前題目：SQLServer發(fā)送HTML格式郵件（事務(wù)）
標(biāo)題來源：http://weahome.cn/article/piceph.html

在線咨詢

微信咨詢

電話咨詢

028-86922220（工作日）

18980820575（7×24）

提交需求

返回頂部