bsidyPutOut.CustomerID AND
dbo.T_Subsidymonthplan.[Month] = dbo.T_SubsidyPutOut.[Month]
if (exists(select * FROM T_Subsidymonthplan
WHERE (CustomerID=@id and [Month] = @month)))
begin
DELETE FROM T_Subsidymonthplan
WHERE (CustomerID=@id and [Month] = @month)
end
else
begin
print ''''没有相关客户的本月补助冲突！或本月补助冲突已经清理完毕！''''
break
end
end /*清除冲突补助月份的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] |
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 