|
|
|
/*公安专几个月或一年只有一次补助的卡的处理代码,当月补助形成后发放前处理的T_SQL代码(除该类卡形成补助当月外) */
declare @month/*月份*/ datetime,@k /*卡类别*/ int,@customerid int /*客户号*/, @subsidyfare/*补助额*/ money
set @k=6 /*卡类别，根据实际定*/
select @month= month from t_subsidymonthplan/*获取补助当月份值*/
declare custid_dist cursor for select distinct customerid from t_subsidynotputout where cardtype=@k
open custid_dist
fetch next from custid_dist into @customerid
while (@@fetch_status=0)
begin
insert into t_subsidymonthplan(month,customerid,cardtype,subsidy) values(@month,@customerid,@k,0)
fetch next from custid_dist into @customerid
end
close custid_dist
deallocate custid_dist
declare custid_subsidyfare cursor for select customerid,sum(subsidy) as sum_subsidy from t_subsidynotputout where cardtype=@k group by customerid
open custid_subsidyfare
fetch next from custid_subsidyfare into @customerid,@subsidyfare
while (@@fetch_status=0)
begin
update t_customers set subsidydt=@month ,cursubsidyfare=@subsidyfare,subsidyout='F' where customerid =@customerid
fetch next from custid_subsidyfare into @customerid,@subsidyfare
end
close custid_subsidyfare
deallocate custid_subsidyfare
/*公安专补助月报表信息纠正T_SQL代码(当月补助发放完后)*/
declare @k int, @k1 int,@k2 int,@month datetime,@subsidyFare money,@cardtype tinyint,@subsidyFare1 money,@subsidyFare2 money
select @month=month from t_subsidymonthplan /*获取当月补助的月份*/
set @cardtype=4 /*卡的类别(除5、6类卡)，根据实际定*/
delete from T_subsidymonthplan where cardtype in (5,6)/*注意卡类*/
delete from t_subsidynotputout where customerid in (select customerid from t_customers where subsidyout='T' and cardtype in (5,6))/*注意卡类*/
select @k=count(*),@subsidyFare=sum(subsidy) from t_subsidypre where cardtype=@cardtype and month=@month /*统计当月补助计划人数及金额，以t_subsidypre帐本为准*/
if exists(select * from t_subsidymonth where plancount=@k and plansubsidy=@subsidyFare and month=@month and cardtype=@cardtype) /*判断当月补助计划数及金额是否正确*/
begin
select @k1=count(*),@subsidyFare1=sum(subsidy) from t_subsidymonthplan where cardtype=@cardtype and month=@month /*统计当月补助未发人数及金额*/
if @subsidyFare1 is null
begin
set @subsidyFare1=0
end
set @k2=@k-@k1
set @subsidyFare2=@subsidyFare-@subsidyFare1
update t_subsidymonth set putoutcount=@k2,putoutsubsidy=@subsidyFare2 where cardtype=@cardtype and month=@month /*当月已发人数及金额=当月补助计划人数及金额-当月补助未发人数及金额*/
print '更改当月的补助信息完成!'
end
else
begin
print '计划总数不一致!'
end
select @k=count(*),@subsidyFare=sum(subsidy) from t_subsidynotputout where cardtype=@cardtype and month<@month /*统计前期补助未发人数及金额*/
select @k1=planprecount,@subsidyFare1=planpre from t_subsidymonth where cardtype=@cardtype and month=@month /*统计当月前期补助计划人数及金额*/
set @k2=@k1-@k
set @subsidyFare2=@subsidyFare1-@subsidyFare
if @subsidyFare2 is null
begin
set @subsidyFare2=0
end
update t_subsidymonth set putoutprecount=@k2,putoutpre=@subsidyFare2 where cardtype=@cardtype and month=@month /*当月前期已发人数及金额=当月前期补助计划人数及金额-前期补助未发人数及金额*/
print '更改当月的前期补助信息完成!'
|
|
