Credit acquisition and consumption

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.