积分获取和消费的存储过程

最近梳理积分系统，数据库中的表主要是四张关系表 

1.GM_JF客户账户积分表

2. GM_JF_DETAIL客户账户积分消费记录

3. GM_JF_ACTION _RULES积分动作规则表

4.GM_JF_GOODS _RULES积分商品规则表

里面的字段就不一一叙述了 具体的看图片

各个表之间的逻辑关系就和容易理解了，下面是处理各种积分动作带来的积分的添加，存储过程如下

-- ===============测试=======================================================
/*
declare @StatusCode int = 1;
exec sp_GM_JF_AddScore 'admin','AN_JF_001_001',1,5,0,'',@StatusCode output
print @StatusCode
*/
-- ===========================================================================
/*
* 判断是否重复获取积分(首次完善个人资料,首次修改密码等等不能重复获取积分)
* 判断是根据 从GM_JF_DETAIL(详情表)查询周期内的数据条数与GM_JF_ACTION_RULES(动作规则表)内的周期重复次数对比
* 如果大于等于周期重复次数,则为重复获取积分
* 接下来
*     1.详情表的数据入库
*     2.判断总积分表是否存在对应客户的总积分 没有则插入一条新的,有 则读取其数据,并更新
*
*/
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     -- 状态码: 0:失败 1:成功 2: 不能重复获取
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,    --一个周期内允许最大次数
@realFrequency     int=0,     --实际周期

@USE_DATE     datetime = GETDATE();
--是否重复获取积分
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)    --实际周期大于周期次数
begin
set @StatusCode = 2;
return 2;
end

declare @count int = 0;     --数据条数
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

 

下面是积分消费，本想把两个写在一起的，奈何

-- ===============测试=======================================================

  /* declare @StatusCode int = 1;
     exec sp_GM_JF_CutScore 'admin','GS_JF_0004',2,1,'',@StatusCode output
     print @StatusCode*/
-- =============================华丽的分割线===================================

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_GM_JF_CutScore]
    @ACCOUNT_ID varchar(30), --兑换ID 如admin
    @GS_CategoryNumber varchar(12), --兑换商品类型
    @CARD_NUM int,  --兑换数量
    --@HQ_JF_AMOUNT int, --兑换的积分总值 每个类型对应积分量*兑换数量
    @State varchar(16),--兑换状态
    @USE_DESC varchar(400), --备注
    --输出参数
    @StatusCode int output            -- 状态码: 0:失败 1:成功 2: 不能重复获取
as
begin    
    SET NOCOUNT ON;--不返回计数（表示受 Transact-SQL 语句影响的行数）   
    declare  --判断处理 自定义变量            
            @score int ,                --要兑换商品类型的积分值
            @scoreSum int,                --要兑换商品类型的积分总值    
            @JF_AMOUNT int,                --可用积分
            @Version int;        --版本号
            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)) --判断可用余额是否大于 兑换所需要的积分
            begin    
                begin tran; 
                --插入详情 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)
                
                --修改总表字段 可用余额 版本号 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; --可用积分小于要兑换物品的积分总值 兑换失败
            end
    
    IF(@@ERROR<>0)
    BEGIN
        set @StatusCode = 2;
        ROLLBACK tran;
    end
END
    
    

存储过程的代码欢迎各位高手指正