SP 存储过程随笔记录（1）

USE [CRM_CN2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--/*********************************************************************************************************
--建立者: HERMAN  日期﹕2020-06-28
--調用的程序﹕
--說明﹕Enzo star权益
--更新記錄﹕
--		日期			    更改人				   更新說明
------------------	     -------------			--------------------------------------------
--  2020-06-28             HERMAN                   新增
----*********************************************************************************************************/
--EXEC [SP_CRM_AUTO_ENZO_STAR_POWER]
CREATE PROCEDURE [dbo].[SP_CRM_AUTO_ENZO_STAR_POWER]
AS
BEGIN
	DECLARE @Tran_Date DATETIME = CONVERT(DATE, DATEADD(DAY, -1, GETDATE()));
	DECLARE @Error_No INT = 0;
	DECLARE @Error_Msg NVARCHAR(500) = N'';

	--Enzo貨品
	IF OBJECT_ID('tempdb..#TempIncludeStockType') IS NOT NULL
		DROP TABLE #TempIncludeStockType;   

	--購買ENZO貨品消費換積分及生日月送1倍積分
	IF OBJECT_ID('tempdb..#tbCRM_EnzoStar_Credit_History') IS NOT NULL
		DROP TABLE #tbCRM_EnzoStar_Credit_History;

	SELECT DISTINCT stock_type,
					stock_group
	INTO #TempIncludeStockType
	FROM   dbo.[stock nature]
	WHERE  product_line = 10;

	--消費獎分(售價1:1積分 壹年有效期)
	SELECT  CCH.*,
			CM.Birthday,
			CAST(CCH.Actual_Selling_Price AS INT) AS EnzoStar_Credit, 
			DATEADD(SECOND,86399,DATEADD(DAY,365,CONVERT(VARCHAR(10),CCH.Tran_Date,23)))  AS Credit_Expiry_Date
	INTO    #tbCRM_EnzoStar_Credit_History
	FROM    tbCRM_Credit_History AS CCH WITH ( NOLOCK )    
			LEFT JOIN tbCRM_Credit_History AS CCH10 WITH ( NOLOCK ) 
			ON CCH10.CRM_Domain = CCH.CRM_Domain
				AND CCH10.Customer_ID = CCH.Customer_ID
				AND CCH10.Stock_Group = CCH.Stock_Group
				AND CCH10.Stock_Type = CCH.Stock_Type
				AND CCH10.Stock_Create_Domain = CCH.Stock_Create_Domain
				AND CCH10.Stock_No = CCH.Stock_No
				AND CCH10.Stock_Sub_No = CCH.Stock_Sub_No
				AND CCH10.Tran_Date > CCH.Tran_Date
				AND CCH10.Tran_Code = 10
			INNER JOIN tbCRM_Customer_Tag AS Ta WITH ( NOLOCK )
			ON Ta.CRM_Domain = CCH.CRM_Domain
				AND Ta.Customer_ID = CCH.Customer_ID
				AND Ta.Tag_ID = 23 --Enzo Star
				AND Ta.Status_Flag=0
			INNER JOIN #TempIncludeStockType S 
			ON S.stock_group = CCH.Stock_Group
				AND S.stock_type = CCH.Stock_Type 
			INNER JOIN dbo.tbCRM_Customer_Master AS CM WITH (NOLOCK)
			ON CM.CRM_Domain = CCH.CRM_Domain 
				AND CM.Customer_ID = CCH.Customer_ID
	WHERE   CCH.Tran_Code = 1
			AND CCH.Tran_Date >= @Tran_Date
			AND CCH.Tran_Date < CONVERT(DATE, GETDATE(),120)
			AND CCH10.Seq IS NULL;

	--生日獎分(額外贈送的1倍銷售積分3個月有效期)
	INSERT  #tbCRM_EnzoStar_Credit_History
			( Seq ,
			  Tran_Date ,
			  Tran_Code ,
			  CRM_Domain ,
			  Customer_ID ,
			  Membership_Seq ,
			  Card_No ,
			  IC_Card_No ,
			  Serial_No ,
			  Credit ,
			  Sales_ICS_Domain ,
			  Sales_Branch_Code ,
			  Sales_Order_No ,
			  Sales_Item ,
			  Stock_Group ,
			  Stock_Type ,
			  Stock_Create_Domain ,
			  Stock_No ,
			  Stock_Sub_No ,
			  Stock_Out_Date ,
			  Mould_ICS_Domain ,
			  Mould_Type ,
			  Mould_No ,
			  Mould_Stock_Type ,
			  Original_Selling_Price ,
			  Actual_Selling_Price ,
			  Selling_Price ,
			  Consume_Price ,
			  Coupon_Price ,
			  Actual_Consume_Price ,
			  VIP_Approved_By ,
			  Discount ,
			  Gift_Code ,
			  Handler ,
			  Remark ,
			  Bar_Code ,
			  Birthday ,
			  EnzoStar_Credit ,
			  Credit_Expiry_Date
			)
			SELECT  Seq ,
					Tran_Date ,
					Tran_Code ,
					CRM_Domain ,
					Customer_ID ,
					Membership_Seq ,
					Card_No ,
					IC_Card_No ,
					Serial_No ,
					Credit ,
					Sales_ICS_Domain ,
					Sales_Branch_Code ,
					Sales_Order_No ,
					Sales_Item ,
					Stock_Group ,
					Stock_Type ,
					Stock_Create_Domain ,
					Stock_No ,
					Stock_Sub_No ,
					Stock_Out_Date ,
					Mould_ICS_Domain ,
					Mould_Type ,
					Mould_No ,
					Mould_Stock_Type ,
					0 ,
					0 ,
					0 ,
					0 ,
					0 ,
					0 ,
					0 ,
					Discount ,
					Gift_Code ,
					Handler ,
					Remark ,
					Bar_Code ,
					Birthday ,
					Credit AS EnzoStar_Credit ,
					DATEADD(SECOND, 86399,DATEADD(MONTH, 3, CONVERT(VARCHAR(10), Tran_Date, 23))) AS Credit_Expiry_Date
			FROM    #tbCRM_EnzoStar_Credit_History
			WHERE   MONTH(Birthday) = MONTH(@Tran_Date)
					AND Credit <> 0

	--插入積分歷史中間表
	IF EXISTS (SELECT * FROM #tbCRM_EnzoStar_Credit_History)
	BEGIN
		BEGIN TRY
			BEGIN TRANSACTION MyTransaction;

			DECLARE @Handler VARCHAR(20) = 'SYSTEM';
			INSERT INTO CRM_CN2_MD.dbo.tbCRM_Credit_History_Log (
				Seq_New,
				Seq,
				Create_Date,
				Status_ID,
				Tran_Date,
				Tran_Code,
				CRM_Domain,
				Customer_ID,
				Membership_Seq,
				Card_No,
				IC_Card_No,
				Serial_No,
				Credit,
				Sales_ICS_Domain,
				Sales_Branch_Code,
				Sales_Order_No,
				Sales_Item,
				Stock_Group,
				Stock_Type,
				Stock_Create_Domain,
				Stock_No,
				Stock_Sub_No,
				Stock_Out_Date,
				Mould_ICS_Domain,
				Mould_Type,
				Mould_No,
				Mould_Stock_Type,
				Original_Selling_Price,
				Actual_Selling_Price,
				Selling_Price,
				Consume_Price,
				Coupon_Price,
				Discount,
				Gift_Code,
				Handler,
				Remark,
				Credit_Expiry_Date
			)
			SELECT NEWID() Seq_New,
					0 Seq,
					@Tran_Date Create_Date,
					0 Status_ID,
					@Tran_Date Tran_Date,
					47 Tran_Code,
					CH.CRM_Domain,
					CH.Customer_ID,
					CH.Membership_Seq,
					CH.Card_No,
					CH.IC_Card_No,
					CH.Serial_No,
					CH.EnzoStar_Credit,
					CH.Sales_ICS_Domain,
					CH.Sales_Branch_Code,
					CH.Sales_Order_No,
					CH.Sales_Item,
					CH.Stock_Group,
					CH.Stock_Type,
					CH.Stock_Create_Domain,
					CH.Stock_No,
					CH.Stock_Sub_No,
					CH.Stock_Out_Date,
					CH.Mould_ICS_Domain,
					CH.Mould_Type,
					CH.Mould_No,
					CH.Mould_Stock_Type,
					CH.Original_Selling_Price,
					CH.Actual_Selling_Price,
					CH.Selling_Price,
					CH.Consume_Price,
					CH.Coupon_Price,
					CH.Discount,
					CH.Gift_Code,
					@Handler Handler,
					CH.Remark,
					CH.Credit_Expiry_Date
			FROM   #tbCRM_EnzoStar_Credit_History CH
			WHERE  CH.EnzoStar_Credit > 0;

			COMMIT TRANSACTION MyTransaction;
		END TRY
		BEGIN CATCH
			ROLLBACK TRANSACTION MyTransaction;
			SELECT @Error_No = @@ERROR,
				   @Error_Msg = ERROR_MESSAGE();
			RAISERROR(N'Enzo消費獎積分報錯,%s', 16, 1, @Error_Msg);
		END CATCH;
	END;

END;