/*清除冲突补助月份的T_SQL代码结束*/

set @month=''''2004-9-1'''' /*补助的月份,根据实际定*/
set @cardtype=4       /*卡的类别，根据实际定*/  
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 ''''更改当月的前期补助信息完成!''''
-------------------------------------------------------------------------------------------------
           /*清除管理费的触发器及生成t_mngfarelog表的脚本*/
create trigger tr_mngfarelog on t_incomerec for insert,update
as
if exists(select * from t_incomerec where mngfare>0)
   begin
     declare @statid tinyint,@mealid tinyint,@port tinyint,@term tinyint,
        @sumdt datetime,@incomefare money,@mngfare money,@avginc money,
        @incomecount int
     select @statid=statid,@mealid=mealid,@port=port,@term=term,
     @sumdt=sumdt, @incomefare=incomefare,@incomecount=incomecount,
     @mngfare=mngfare from t_incomerec where mngfare>0
     update t_incomerec set mngfare=0 where statid=@statid and mealid=@mealid and
            port=@port and term=@term and sumdt=@sumdt
     set @avginc=@incomefare/@incomecount
     if @avginc>5/*平均消费值，根据实际定*/
       begin
         update t_incomerec set incomefare=4.5*incomecount where
         statid=@statid and mealid=@mealid and port=@port and term=@term and sumdt=@sumdt
       end  
     insert into t_mngfarelog values(@statid,@mealid,@port,@term,
                 @sumdt,@incomefare,@incomecount,@mngfare)  
   end
go

if exists (select * from dbo.sysobjects where id = object_id(N''''[dbo].[t_mngfarelog]'''') and
OBJECTPROPERTY(id, N''''IsUserTable'''') = 1) /*生成t_mngfarelog表的脚本*/
drop table [dbo].[t_mngfarelog]
GO

CREATE TABLE [dbo].[t_mngfarelog] (
    [statid] [tinyint] NOT NULL ,
    [mealid] [tinyint] NOT NULL ,
    [port] [tinyint] NOT NULL ,
    [term] [tinyint] NOT NULL ,
    [sumdt] [datetime] NOT NULL ,
    [incomefare] [money] NOT NULL ,
    [incomecount] [int] NOT NULL ,
    [mngfare] [money] NULL
) ON [PRIMARY]
GO 

上一页  [1] [2] [3] [4]