2005-06-23个人日志

今天去了fjjrzyjsxy做系统维护工作，主要解决了两大问题:一个是补助月报的修正，由于老出现改完后当场可以过后又不行的情况，后面直接把那个补助月报的T-SQL修正代码形成一个快捷方式放到桌面上，叫黄老师以后如果报表有错，直接执行那个快捷方式，这样就省了我们不少麻烦，因为那T-SQL代码都是自动执行的，不要输入任何参数，只要有错，就可执行，但只针对当月报表。二是解决了开户发卡时，学（工）号没办法自动形成问题。主要是在T_customers表中做了一个触发器问题解决！

附:

/*补助月报表信息纠正T-SQL代码*/

declare @k int, @k1 int,@k2 int,@month datetime,@subsidyFare money,@cardtype tinyint,@subsidyFare1 money,@subsidyFare2 money

select top 1 @month=month from t_subsidymonth order by month desc /*获取当月补助的月份*/

deallocate k

declare k cursor for select cardtype from t_subsidymonth where month=@month

open k

fetch next from k into @cardtype

while (@@fetch_status=0)

begin /*以下为修正T_subsidymonth表信息代码，以未发算已发为原则*/

select @k=count(*),@subsidyFare=isnull(sum(subsidy),0) 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 str(@cardtype)+'类卡更改当月的补助信息完成!'

end

else

begin

print str(@cardtype)+'类卡计划总数不一致!'

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 str(@cardtype)+'类卡更改当月的前期补助信息完成!'

fetch next from k into @cardtype

end

close k

deallocate k

/*自动形成学(工)号的触发器*/

CREATE TRIGGER tr_outid ON T_Customers
FOR INSERT
AS
update T_customers set T_customers.outid=i.customerid from inserted as I where T_customers.customerid= i.customerid