Credit acquisition and consumption

1. GM_JF customer account points Table 2. GM_JF_DETAIL customer account points consumption record 3. GM_JF_ACTION_RULES Points Action rule table 4. GM_JF_GOODS_RULES points item rule table none -- test * declare @ StatusCodeint1 exec;

1. GM_JF Customer Account integral table 2. GM_JF_DETAIL customer account credit consumption record 3. GM_JF_ACTION_RULES Integral Action rule table 4. GM_JF_GOODS_RULES points commodity rule table none -- ====================== test ====== ===================================================================== /* declare @ StatusCode int = 1; exec

1. GM_JF Customer Account integral table

2. GM_JF_DETAIL credit consumption record of the Customer Account

3. GM_JF_ACTION _ RULES Integral Action rule table

4. GM_JF_GOODS _ RULES points commodity rule table <无>

-- ====================== Test ============================= =====================================/* declare @ StatusCode int = 1; exec sp_GM_JF_AddScore 'admin', 'an _ jf_00000000', 0 ,'', @ StatusCode outputprint @ StatusCode */-- ================================ ========================================================== ===/ ** determine whether or not to repeatedly obtain points (improving personal data for the first time, points cannot be obtained again when you change the password for the first time. * The result is determined based on the number of data entries in the query cycle from GM_JF_DETAIL (Details table) and GM_JF_ACTION_RULES (action rule table) comparison of the number of recurrence within * if it is greater than or equal to the number of recurrence, the credits are obtained again * Next * 1. details table data warehouse * 2. if no customer's total points exist in the total points table, insert a new one. If yes, read the data and update **/alter procedure [dbo]. [declare] @ ACCOUNT_ID varchar (30), @ JF_CategoryNumber varchar (15), @ CARD_NUM int, @ HQ_JF_AMOUNT int, @ State varchar (16), @ USE_DESC varchar (400 ), @ StatusCode int output -- Status Code: 0: Failed 1: Successful 2: ASBEGIN -- set nocount on added to prevent extra result sets from -- interfering with SELECT statements. set nocount on; declare @ repetitionsCycle float = 0, -- cycle (day) @ repetitionsCycle_second int = 0, -- cycle (second) @ repetitionsFrequency int = 0, -- maximum number of times allowed in a cycle @ realFrequency int = 0, -- actual cycle @ USE_DATE datetime = GETDATE (); -- whether to repeatedly obtain points select top (1) @ repetitionsCycle = RepetitionsCycle, @ repetitionsFrequency = RepetitionsFrequency from GM_JF_ACTION_RULES where AN_CategoryNumber = @ JF_CategoryNumber; if (@ repetitionsCycle <1) BEGINset @ percent = (@ repetitionsCycle-1) * 24*60*60; select @ realFrequency = COUNT (1) from GM_JF_DETAIL where ACCOUNT_ID = @ ACCOUNT_ID and JF_CategoryNumber = @ JF_CategoryNumber and USE_DATE <= @ USE_DATE and USE_DATE> = CONVERT (varchar (19 ), DATEADD (SECOND,-@ repetitionsCycle_second, @ USE_DATE), 120) ENDELSEBEGINselect @ realFrequency = COUNT (1) from GM_JF_DETAIL where ACCOUNT_ID = @ ACCOUNT_ID and JF_CategoryNumber = @ JF_CategoryNumber and USE_DATE <= @ USE_DATE and USE_DATE> = CONVERT (varchar (10), DATEADD (DAY,-(@ repetitionsCycle-1 ), @ USE_DATE), 120) END if (@ realFrequency >=@ repetitionsFrequency) -- the actual cycle is greater than the number of cycles beginset @ StatusCode = 2; return 2; enddeclare @ count int = 0; -- data entries declare @ temp_table table -- table variables (ACCOUNT_ID varchar (30), JF_AMOUNT decimal (), TTL_JF_AMOUNT decimal (), Last_Update_Time datetime, [Version] int ); begin tran; -- insert details insert into GM_JF_DETAIL (ACCOUNT_ID, JF_CategoryNumber, CARD_NUM, region, [State], USE_DESC) values (@ ACCOUNT_ID, @ JF_CategoryNumber, @ CARD_NUM, @ region, @ State, @ USE_DESC) -- fill the table variable insert into @ temp_table select ACCOUNT_ID, JF_AMOUNT, TTL_JF_AMOUNT, Last_Update_Time, [Version] from GM_JF where ACCOUNT_ID = @ ACCOUNT_IDselect @ count (1) from @ temp_table; -- judge and update the total points (0: add others: Modify) IF (@ count = 0) begininsert into GM_JF (ACCOUNT_ID, JF_AMOUNT, TTL_JF_AMOUNT) values (@ ACCOUNT_ID, @ override, @ override) aggregate @ JF_AMOUNT int, -- total points @ TTL_JF_AMOUNT int, -- available points @ Version int; -- Version number select @ JF_AMOUNT = JF_AMOUNT, @ TTL_JF_AMOUNT = TTL_JF_AMOUNT, @ Version = [Version] from @ temp_table where ACCOUNT_ID = @ ACCOUNT_ID; update GM_JF set JF_AMOUNT = (@ JF_AMOUNT + @ amount), TTL_JF_AMOUNT = (@ TTL_JF_AMOUNT + @ amount ), last_Update_Time = GETDATE (), [Version] = (@ Version + 1) where ACCOUNT_ID = @ ACCOUNT_IDendCommit tran; set @ StatusCode = 1; IF (@ ERROR <> 0) BEGINset @ StatusCode = 0; ROLLBACK tran; ENDEND

-- ====================== Test ============================= =====================================/* declare @ StatusCode int = 1; exec sp_GM_JF_CutScore 'admin', 'gs _ JF_0004 ', 2, 1 ,'', @ StatusCode output print @ StatusCode */-- ========================== gorgeous split line ======================================== SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo]. [sp_GM_JF_CutScore] @ ACCOUNT_ID varchar (30), -- redeem ID for example admin @ GS_CategoryNumber varchar (12), -- redeem product type @ CARD_NUM int, -- redeem quantity -- @ HQ_JF_AMOUNT int, -- the total value of redeem points. Each type corresponds to the number of points * redeem @ State varchar (16), -- redeem status @ USE_DESC varchar (400 ), -- remarks -- output parameter @ StatusCode int output -- Status Code: 0: Failed 1: Successful 2: asbegin set nocount on cannot be obtained repeatedly; -- no count is returned (indicating the number of rows affected by the Transact-SQL statement) declare -- judge and process the custom variable @ score int, -- the point value of the product type to be exchanged @ scoreSum int, -- the total points of the product type to be redeemed @ JF_AMOUNT int, -- available points @ Version int; -- version select @ score = GS_SCORE from GM_JF_GOOD_RULES where GS_CategoryNumber = @ GS_CategoryNumber -- 'gs _ JF_0003 'if (@ CARD_NUM> 0) begin set @ scoreSum = @ score * @ CARD_NUM * (-1) end select @ JF_AMOUNT = JF_AMOUNT, @ Version = [Version] from GM_JF where ACCOUNT_ID = @ ACCOUNT_ID; if (@ JF_AMOUNT> @ scoreSum * (-1) -- determine whether the available balance is greater than the points required for the redemption; -- insert details GM_JF_DETAIL insert into GM_JF_DETAIL (ACCOUNT_ID, JF_CategoryNumber, CARD_NUM, HQ_JF_AMOUNT, [State], USE_DESC) values (@ ACCOUNT_ID, @ GS_CategoryNumber, @ CARD_NUM, @ scoreSum, @ State, @ USE_DESC) -- modify the available balance Version number of the table field GM_JF update GM_JF set JF_AMOUNT = (@ JF_AMOUNT + @ scoreSum), Last_Update_Time = GETDATE (), [Version] = (@ Version + 1) where ACCOUNT_ID = @ ACCOUNT_ID set @ StatusCode = 1; Commit tran; end else begin set @ StatusCode = 2; -- the value of the available points is less than the total value of the points to be redeemed. end IF (@ ERROR <> 0) BEGIN set @ StatusCode = 2; ROLLBACK tran; endEND