sqlserer存储过程笔记零碎记录

/*    ==脚本参数==

    源服务器版本 : SQL Server 2012 (11.0.5058)
    源数据库引擎版本 : Microsoft SQL Server Enterprise Edition
    源数据库引擎类型 : 独立的 SQL Server

    目标服务器版本 : SQL Server 2017
    目标数据库引擎版本 : Microsoft SQL Server Standard Edition
    目标数据库引擎类型 : 独立的 SQL Server
*/

USE [CRM_CN2]
GO
/****** Object:  StoredProcedure [dbo].[SP_AAAAAAAAAA_TEST]    Script Date: 2019/7/4 15:34:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--/*********************************************************************************************************
--建立者: --------zrf
-- Create date:2019-06-28
--调用的程序﹕
--说明﹕会籍将要降级的前一个月，会员过期前的一个月 发送短信
--*********************************************************************************************************/
--EXEC [SP_CRM_AUTO_OPERATE_RENEW]
ALTER PROCEDURE [dbo].[SP_AAAAAAAAAA_001]
AS
BEGIN
    DECLARE @NOW_TIME DATETIME;
    DECLARE @TRAN_DATE DATETIME;
    DECLARE @FOLLOW_BY VARCHAR(20);

    SELECT @NOW_TIME = GETDATE();
    SELECT @FOLLOW_BY = 'SYSTEM';
    SELECT @TRAN_DATE = CONVERT(VARCHAR, DATEADD(DAY, -1, @NOW_TIME), 111) + ' 23:59:59';
    DECLARE @WxMsgIDForWillDown UNIQUEIDENTIFIER = '4421D674-038A-4BE4-9159-768687ADD842'; --对会籍即将降级的会员新增微信提醒
    DECLARE @SMSMsgIDForWillDown UNIQUEIDENTIFIER = 'A5EBA7C5-8DF9-4CB7-8C14-BF8475E75E97'; --短信-会籍到期前一个月提醒,及时将要降级

    IF OBJECT_ID('tempdb..#tbCRM_Operate_Match') IS NOT NULL
        DROP TABLE #tbCRM_Operate_Match;

    SELECT *
    INTO #tbCRM_Operate_Match
    FROM
    (
        SELECT 1 Card_Level_To,
               0 Money_From
        UNION
        SELECT DISTINCT
               Card_Level_To,
               Money_From
        FROM [dbo].[tbCRM_Operate_Match]
        WHERE Tran_Code = 3
              AND Card_Level_To IN ( 2, 3, 4, 5 ) --平级，降级
    ) T;

    IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL
        DROP TABLE #TEMP;
    CREATE TABLE #TEMP
    (
        CRM_Domain SMALLINT,
        Customer_ID INT,
        Membership_Seq SMALLINT,
        Card_No BIGINT,
        Card_Level SMALLINT,
        Join_Date DATETIME,
        [Expiry_Date] DATETIME,
        Status_ID SMALLINT,
        Credit_Available INT,
        ReConsume_Credit INT,
        Consume_Total MONEY,
        ReConsume_Total MONEY
    );

    IF OBJECT_ID('tempdb..#TEMP_CARD') IS NOT NULL
        DROP TABLE #TEMP_CARD;
    CREATE TABLE #TEMP_CARD
    (
        CRM_Domain SMALLINT,
        Customer_ID INT,
        Membership_Seq SMALLINT,
        Membership_Seq_New SMALLINT,
        Card_No BIGINT,
        Card_Level_Old SMALLINT,
        Card_Level_New SMALLINT,
        Join_Date DATETIME,
        [Expiry_Date] DATETIME,
        Status_ID SMALLINT,
        Credit_Available INT,
        Consume_Total MONEY,
        ReConsume_Total MONEY
    );

    --找出当前将要在一个月后过期的会员
    INSERT INTO #TEMP
    (
        CRM_Domain,
        Customer_ID,
        Membership_Seq,
        Card_No,
        Card_Level,
        Join_Date,
        Expiry_Date,
        Status_ID,
        Credit_Available,
        ReConsume_Credit,
        Consume_Total,
        ReConsume_Total
    )
    SELECT CRM_Domain,
           Customer_ID,
           Membership_Seq,
           Card_No,
           Card_Level,
           Join_Date,
           [Expiry_Date],
           Status_ID,
           Credit_Available,
           0 ReConsume_Credit,
           Consume_Total,
           ReConsume_Total
    FROM dbo.tbCRM_Card_Master
    WHERE Status_ID = 0
    AND Expiry_Date > GETDATE()
    AND DATEDIFF(month, DATEADD(MONTH,1, GETDATE()), Expiry_Date) =0;

    --找出当前将要降级的会员
    INSERT INTO #TEMP_CARD
    (
        CRM_Domain,
        Customer_ID,
        Membership_Seq,
        Membership_Seq_New,
        Card_No,
        Card_Level_Old,
        Card_Level_New,
        Join_Date,
        [Expiry_Date],
        Status_ID,
        Credit_Available,
        Consume_Total,
        ReConsume_Total
    )
    SELECT CAM.CRM_Domain,
           CAM.Customer_ID,
           CAM.Membership_Seq,
           CAM.Membership_Seq + 1 AS Membership_Seq_New,
           CAM.Card_No,
           CAM.Card_Level AS Card_Level_Old,
           0 Card_Level_New,
           @TRAN_DATE Join_Date,
           CAM.[Expiry_Date],
           CAM.Status_ID,
           CAM.Credit_Available,
           CAM.Consume_Total,
           CAM.ReConsume_Total
    FROM #TEMP AS CAM
        INNER JOIN #tbCRM_Operate_Match T1
            ON CAM.Card_Level = T1.Card_Level_To
               AND CAM.ReConsume_Total < T1.Money_From
    WHERE CAM.Status_ID = 0
          AND CAM.Card_Level IN (2, 3, 4, 5 );

--SELECT * FROM #TEMP_CARD


    UPDATE #TEMP_CARD
    SET Card_Level_New = T2.Card_Level_New
    FROM #TEMP_CARD T
        INNER JOIN
        (
            SELECT T.Card_No,
                   MAX(T2.Card_Level_To) Card_Level_New
            FROM #TEMP_CARD T
                INNER JOIN #tbCRM_Operate_Match T2
                    ON T2.Money_From <= CASE
                                            WHEN T.Consume_Total < 0 THEN
                                                0
                                            ELSE
                                                T.Consume_Total
                                        END
            GROUP BY T.Card_No
        ) T2
            ON T.Card_No = T2.Card_No;

    BEGIN TRY
        BEGIN TRAN MyTransaction;

        --插入短信记录表，将降级的会员发送短信
        INSERT INTO [tbCRM_Information_History]
        (
            [CRM_Domain],
            [Customer_ID],
            [Channel_ID],
            [Send_Theme],
            [Send_Content],
            [Contact_Way],
            [Create_By],
            [Create_Date],
            [Send_Date],
            [Sent_Date],
            [Reply_Content],
            [Reply_Date],
            Is_Vip,
            [Status_ID]
        )
        SELECT #TEMP_CARD.[CRM_Domain],
               #TEMP_CARD.[Customer_ID],
               4,
                          --  N'短信-将要降级通知，提前一个月' ,
               N'短信-预降级通知',
               dbo.[F_PREPARE_INFORMATION_TEMPLATE](
                                                       @SMSMsgIDForWillDown,
                                                       N'<params><item name="@CurrentCardLevel">'
                                                       + CCL_Old.Card_Level_Desc
                                                       + N'</item><item name="@DownCardLevel">'
                                                       + CCL_New.Card_Level_Desc + N'</item><item name="@ExpiryDate">'
                                                       + CONVERT(VARCHAR, DATEADD(DAY, 30, GETDATE()), 23)
                                                       + N'</item><item name="@Money">'
                                                       + CONVERT(VARCHAR, OPM.Money_From - #TEMP_CARD.ReConsume_Total)
                                                       + N'</item></params>'
                                                   ),
               ISNULL(CUM.Mobile_Phone1, 0),
               @FOLLOW_BY,
               @NOW_TIME, --十点之后才发送升级降级续约短信
               (CASE
                    WHEN DATEPART(hh, @NOW_TIME) < 10 THEN
                        CONVERT(VARCHAR, @NOW_TIME, 23) + ' 10:00:00'
                    ELSE
                        DATEADD(MINUTE, -5, @NOW_TIME)
                END
               ),
               '1900-1-1',
               '0',
               '1900-1-1',
               0,
               1
        FROM #TEMP_CARD
            INNER JOIN tbCRM_Customer_Master CUM
                ON CUM.CRM_Domain = #TEMP_CARD.CRM_Domain
                   AND CUM.Customer_ID = #TEMP_CARD.Customer_ID
            INNER JOIN dbo.tbCRM_Card_Level CCL_New
                ON #TEMP_CARD.Card_Level_New = CCL_New.Card_Level
            INNER JOIN dbo.tbCRM_Card_Level CCL_Old
                ON #TEMP_CARD.Card_Level_Old = CCL_Old.Card_Level
            INNER JOIN dbo.tbCRM_Information_Template CIT
                ON CIT.Template_Id = @SMSMsgIDForWillDown
            INNER JOIN #tbCRM_Operate_Match OPM
                ON #TEMP_CARD.Card_Level_Old = OPM.Card_Level_To
        WHERE CIT.Status_Flag = 0
              AND GETDATE()
              BETWEEN CIT.Valid_From AND CIT.Valid_Until
        UNION
        SELECT CCW.CRM_Domain,
               CCW.Customer_ID,
               CCW.Channel_ID,
               N'WEIXIN_TEMPLATE_MSG（微信-会籍降级提前提醒）',
               dbo.[F_PREPARE_INFORMATION_TEMPLATE](
                                                       @WxMsgIDForWillDown,
                                                       N'<params>
                            <item name="@card_no">' + CONVERT(VARCHAR, #TEMP_CARD.Card_No)
                                                       + N'</item>
                            <item name="@ContactWay">' + CCW.Contact_Way
                                                       + N'</item>
                            <item name="@CardLevelDesc">' + CCLOld.Card_Level_Desc
                                                       + N'</item>
                            <item name="@Money">' + CONVERT(VARCHAR, OPM.Money_From - #TEMP_CARD.ReConsume_Total)
                                                       + N'</item>
                            <item name="@NewCardLevelDesc">' + CCL.Card_Level_Desc
                                                       + N'</item>
                            <item name="@TranDate">' + CONVERT(VARCHAR, DATEADD(DAY, 30, GETDATE()), 23)
                                                       + N'</item>
                            </params>'
                                                   ),
               CCW.Contact_Way,
               @FOLLOW_BY,
               @NOW_TIME Create_Date,
               (CASE
                    WHEN DATEPART(hh, @NOW_TIME) < 10 THEN
                        CONVERT(VARCHAR, @NOW_TIME, 23) + ' 10:00:00'
                    ELSE
                        DATEADD(MINUTE, -30, @NOW_TIME)
                END
               ),
               '1900-1-1',
               N'',
               N'1900-1-1',
               0,
               1
        FROM #TEMP_CARD
            INNER JOIN dbo.tbCRM_Contact_Way AS CCW
                ON CCW.CRM_Domain = #TEMP_CARD.CRM_Domain
                   AND CCW.Customer_ID = #TEMP_CARD.Customer_ID
                   AND CCW.Channel_ID = 5
            INNER JOIN dbo.tbCRM_Card_Level CCL
                ON #TEMP_CARD.Card_Level_New = CCL.Card_Level
            INNER JOIN dbo.tbCRM_Card_Level CCLOld
                ON #TEMP_CARD.Card_Level_Old = CCLOld.Card_Level
            INNER JOIN dbo.tbCRM_Information_Template CIT
                ON CIT.Template_Id = @WxMsgIDForWillDown
            INNER JOIN dbo.#tbCRM_Operate_Match OPM
                ON OPM.Card_Level_To = #TEMP_CARD.Card_Level_Old
        WHERE CIT.Status_Flag = 0
              AND GETDATE()
              BETWEEN CIT.Valid_From AND CIT.Valid_Until;


        PRINT ('okokokoko');
        COMMIT TRAN MyTransaction;
    END TRY
    --捕获异常并处理异常
    BEGIN CATCH
        ROLLBACK TRAN MyTransaction;
        PRINT ERROR_MESSAGE();
    END CATCH;

END;