SP 存储过程随笔记录（1）
2020-10-10 本文已影响0人
YANG_LIVE
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;
