欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

积分获取和消费的存储过程学习示例

程序员文章站 2023-10-20 11:04:01
1.gm_jf客户账户积分表 2. gm_jf_detail客户账户积分消费记录 3. gm_jf_action _rules积分动作规则表 4.gm_jf_good...

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