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 outputprint @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: 不能重复获取asbegin-- 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=@repetitionsfrequency) --实际周期大于周期次数beginset @statuscode = 2;return 2;enddeclare @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_idselect @count = count(1) from @temp_table;--判断并更新总积分（0:添加 其他:修改）if(@count=0)begininsert into gm_jf(account_id,jf_amount,ttl_jf_amount)values(@account_id,@hq_jf_amount,@hq_jf_amount)endelsebegindeclare @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_idendcommit tran;set @statuscode = 1;if(@@error0)beginset @statuscode = 0;rollback tran;endend
-- ===============测试======================================================= /* declare @statuscode int = 1; exec sp_gm_jf_cutscore 'admin','gs_jf_0004',2,1,'',@statuscode output print @statuscode*/-- =============================华丽的分割线===================================set ansi_nulls ongoset quoted_identifier ongoalter 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: 不能重复获取asbegin 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(@@error0) begin set @statuscode = 2; rollback tran; endend