会员积分清零

wbv123

if not exists(select * from c_bill where billcode=’CAS_ql’)
begin
INSERT c_bill(billcode,billname,serialtype,serialno,serialdate)
values(‘CAS_ql’,’兑奖单(积分清零)’,’yyyymmddssssssssssssssss’,0,’2015-01-06 17:14:26.950′)
end
go
if object_id(‘p_memcard_qingling’) is not null drop proc p_memcard_qingling

go
create proc p_memcard_qingling @enddate datetime as

declare @orgtype varchar(30)
select @orgtype=orgtype from c_org_busi where busno=dbo.f_sys_ini(‘1001’)
–会员积分清零在总部进行处理
if @orgtype =’10’
begin
–统计大于给定日期之后的会员销售和兑奖记录
select b.memcardno,b.integral into #ql from u_sale_m a (nolock) ,u_memcard_sale b (nolock) where a.saleno=b.saleno
and a.accdate>@enddate
union all
select b.memcardno,-b.integral from u_memcard_cash b (nolock) where b.status=1
and convert(char(10),b.execdate,121)>convert(char(10),@enddate,121)

–统计#ql查询出来的会员卡的积分合计
select memcardno,sum(integral) as integral into #ql1 from #ql
group by memcardno

–统计所有需要兑奖的卡号
–定义:给定日期之后统计的销售和兑奖积分合计定义为A，当前会员卡的积分定义为B，本次清的积分定义为C
–1、如果A<=0 ，那么C=B –2、如果A>0 and A>B,那么C=0
–3、如果A>0 and A0 and A=B,那么C=B

select a.*,case when isnull(b.integral,0)<=0 then a.integral when isnull(b.integral,0)>0 and isnull(b.integral,0)>a.integral then 0
when isnull(b.integral,0)>0 and isnull(b.integral,0)0 and isnull(b.integral,0)=a.integral then a.integral end as cashjf, –需兑奖积分
a.integral-case when isnull(b.integral,0)<=0 then a.integral when isnull(b.integral,0)>0 and isnull(b.integral,0)>a.integral then 0
when isnull(b.integral,0)>0 and isnull(b.integral,0)0 and isnull(b.integral,0)=a.integral then a.integral end
as jfye –兑奖后积分
into #ql2
from u_memcard_Reg a (nolock) left join #ql1 b on a.memcardno=b.memcardno

–删除兑奖积分为零的卡号
delete from #ql2 where cashjf=0

declare @cashno varchar(40)
declare @memcardno varchar(40)
declare @jf numeric(16,6)

begin tran

create table #disable_u_memcard_cash (id int)
create table #disable_u_memcard_reg (id int)

declare memcard_cash cursor local for select memcardno from #ql2 where cashjf <>0

open memcard_cash
fetch memcard_cash into @memcardno
while @@fetch_status = 0
begin
EXEC p_skip ‘CAS_ql’

SELECT @cashno = serialno FROM dbo.f_get_serialno(‘CAS_ql’)
select @jf=cashjf from #ql2 b where memcardno=@memcardno

insert into u_memcard_cash(cashno,memcardno,busno,cardlevel,integrala,integral,status,execdate,createuser,
createtime,notes)
select @cashno,a.memcardno,dbo.f_sys_ini(‘1001’),a.cardlevel,a.integral,
case when cashjf<0 then a.integral else cashjf end ,1,getdate(),’168′,getdate(),’积分清零’
from #ql2 a where a.memcardno=@memcardno

update u_memcard_reg set integral=integral-@jf where memcardno=@memcardno
fetch memcard_cash into @memcardno
end
close memcard_cash
deallocate memcard_cash

drop table #disable_u_memcard_cash,#disable_u_memcard_reg

commit
drop table #ql,#ql1,#ql2
end