Home  >  Article  >  Database  >  积分获取和消费的存储过程

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

WBOY
WBOYOriginal
2016-06-07 14:57:321270browse

1.GM_JF客户账户积分表 2.GM_JF_DETAIL客户账户积分消费记录 3.GM_JF_ACTION_RULES积分动作规则表 4.GM_JF_GOODS_RULES积分商品规则表 无 -- ===============测试=======================================================/*declare @StatusCode int = 1;exec

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
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn