1.GM_JF客戶賬戶積分表

2. GM_JF_DETAIL客戶賬戶積分消費(fèi)記錄

3. GM_JF_ACTION _RULES積分動作規(guī)則表

4.GM_JF_GOODS _RULES積分商品規(guī)則表

代碼如下:

-- ===============測試=======================================================

/*

declare @StatusCode int = 1;

exec sp_GM_JF_AddScore 'admin','AN_JF_001_001',1,5,0,'',@StatusCode output

print @StatusCode

*/

-- ===========================================================================

/*

* 判斷是否重復(fù)獲取積分(首次完善個人資料,首次修改密碼等等不能重復(fù)獲取積分)

* 判斷是根據(jù) 從GM_JF_DETAIL(詳情表)查詢周期內(nèi)的數(shù)據(jù)條數(shù)與GM_JF_ACTION_RULES(動作規(guī)則表)內(nèi)的周期重復(fù)次數(shù)對比

* 如果大于等于周期重復(fù)次數(shù),則為重復(fù)獲取積分

* 接下來

* 1.詳情表的數(shù)據(jù)入庫

* 2.判斷總積分表是否存在對應(yīng)客戶的總積分 沒有則插入一條新的,有 則讀取其數(shù)據(jù),并更新

*

*/

ALTER PROCEDURE [dbo].[sp_GM_JF_AddScore]

@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 -- 狀態(tài)碼: 0:失敗 1:成功 2: 不能重復(fù)獲取

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

declare

@repetitionsCycle float=0, --周期（天）

@repetitionsCycle_second int=0,--周期（秒）

@repetitionsFrequency int=0, --一個周期內(nèi)允許最大次數(shù)

@realFrequency int=0, --實際周期

@USE_DATE datetime = GETDATE();

--是否重復(fù)獲取積分

select top(1) @repetitionsCycle=RepetitionsCycle,@repetitionsFrequency=RepetitionsFrequency from GM_JF_ACTION_RULES where AN_CategoryNumber=@JF_CategoryNumber;

if(@repetitionsCycle<1)

BEGIN

set @repetitionsCycle_second = (@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)

END

ELSE

BEGIN

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(10),DATEADD(DAY,-(@repetitionsCycle-1),@USE_DATE),120)

END

if(@realFrequency>=@repetitionsFrequency) --實際周期大于周期次數(shù)

begin

set @StatusCode = 2;

return 2;

end

declare @count int = 0; --數(shù)據(jù)條數(shù)

declare @temp_table table --表變量

(

ACCOUNT_ID varchar(30),

JF_AMOUNT decimal(16,2),

TTL_JF_AMOUNT decimal(16,2),

Last_Update_Time datetime,

[Version] int

);

begin tran;

--插入詳情

insert into GM_JF_DETAIL

(ACCOUNT_ID,JF_CategoryNumber,CARD_NUM,HQ_JF_AMOUNT,[State],USE_DESC)

values

(@ACCOUNT_ID,@JF_CategoryNumber,@CARD_NUM,@HQ_JF_AMOUNT,@State,@USE_DESC)

--填充表變量

insert into @temp_table select ACCOUNT_ID,JF_AMOUNT,TTL_JF_AMOUNT,Last_Update_Time,[Version] from GM_JF where ACCOUNT_ID=@ACCOUNT_ID

select @count = count(1) from @temp_table;

--判斷并更新總積分（0:添加 其他:修改）

IF(@count=0)

begin

insert into GM_JF(ACCOUNT_ID,JF_AMOUNT,TTL_JF_AMOUNT)

values

(@ACCOUNT_ID,@HQ_JF_AMOUNT,@HQ_JF_AMOUNT)

end

else

begin

declare @JF_AMOUNT int, --總積分

@TTL_JF_AMOUNT int, --可用積分

@Version int; --版本號

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+@HQ_JF_AMOUNT),TTL_JF_AMOUNT=(@TTL_JF_AMOUNT+@HQ_JF_AMOUNT),Last_Update_Time=GETDATE(),[Version]=(@Version+1) where ACCOUNT_ID=@ACCOUNT_ID

end

Commit tran;

set @StatusCode = 1;

IF(@@ERROR<>0)

BEGIN

set @StatusCode = 0;

ROLLBACK tran;

END

END

更多信息請查看IT技術(shù)專欄