USE [ibcs_baotou_true]
GO
/****** Object: StoredProcedure [dbo].[P_RP_BaoTouRemissionCountMonthBB] Script Date: 04/15/2011 10:43:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*《减免汇总月报表》中“本月减免”中的历史数据及“本年累计减免”中的数据应该分为本年数据和本年前数据。
《减免明细表》中“减免历史欠费”也应分为本年数据和本年前数据，目前的报表无法进行和财务的对账，
分开的目的就是为了和财务进行数据的核对*/
-- =============================================
-- Author: zhengqiao@shanghai3h.com
-- Create date: <2009-12-3>
-- Description: <包头市供水总公司XXXX年XX月减免汇总月报表(T26)>
--------Sample:EXEC P_RP_BaoTouRemissionCountMonthBB '201002','','','',1
ALTER procedure [dbo].[P_RP_BaoTouRemissionCountMonthBB](
@countDate varchar(20),--查询年月
@waterType varchar(20)='',--用水类别
@S_ST varchar(20)='',--站点，可复选用逗号隔开
@S_CID char(16)='',--用户代码
@flag int=1--分质用水标志位；0：分质；1：非分质
)
as
declare
@sql varchar(3000)--临时sql变量
,@NowMonth varchar(20)--当前账务年月
,@JianMianTableName varchar(50)--ZW_JianMiann表
,@JianMianZBTableName varchar(50)--ZW_JianMian_ZB表
,@BiaoKaxxTableName VARCHAR(50) -- 表卡信息表名
,@s_BeginDate varchar(100)--减免日期开始日期
,@s_EndDate varchar(100)--减免日期结束日期
,@condition varchar(1000)--拼接查询条件
,@countYear varchar(50)
,@countMonth varchar(50)
,@exChange int--标志位
set @exChange=0
------------------------------------------------------------
set @countYear=substring(convert(char(8),@countDate,112),1,4) --2011
set @countMonth=substring(convert(char(8),@countDate,112),5,2) --04
-------得到账务年月范围---------
select @s_BeginDate=convert(varchar(10),D_KaiShiRQ,112),
@s_EndDate=convert(varchar(10),D_JieShuRQ,112)
from SYS_JieZhuanRZ where I_ZhangWuNY=@countDate
--
print @s_BeginDate
if @s_BeginDate is null
set @exChange=1--return 0
--获取表名
print @countDate
select @JianMianTableName='ZW_JianMian'
select @JianMianZBTableName='ZW_JianMian_ZB'
select @BiaoKaxxTableName=dbo.F_GetTableName(@countDate,'KG_BiaoKaXX')
print @JianMianTableName
------判断要用到的表中是否有不存在的
IF(@JianMianTableName IS NULL)
BEGIN
set @exChange=1--RETURN 0
END
-----查询居民类别的简号
create table #TMPJMJH (I_JH INT ,I_TJH int)
insert into #TMPJMJH
select * from F_BaotouGetFenLeiJH(0)
-----查询企业类别的简号
create table #TMPQYJH (I_JH INT,I_TJH int )
insert into #TMPQYJH
select * from F_BaotouGetFenLeiJH(1)
CREATE TABLE #TMPJMJH1 (I_JH INT,S_FeiLeiMC varchar(50))
insert into #TMPJMJH1 select I_JH,S_FeiLeiMC from (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia )) JG_YongShuiFL
declare @S_JH varchar(1000)--存放简号，用逗号隔开
declare @JH varchar(20)--存放游标轮循的简号值
set @S_JH=''
if @waterType<>''
begin
delete from #TMPJMJH1--清空表
set @sql='SELECT distinct I_JH,S_FeiLeiMC
FROM (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia )) [JG_YongShuiFL]
where i_ancestor IN (SELECT ID FROM (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia )) JG_YongShuiFL WHERE I_ANCESTOR =0)
AND S_FEILEIMC LIKE ''%'+@waterType+'%'' '
print @sql
INSERT INTO #TMPJMJH1
EXEC(@sql)
---使用游标读取#TMPJMJH表中字段值
declare JHCursor cursor for --声明游标
select I_JH from #TMPJMJH1
open JHCursor --打开游标
fetch next from JHCursor into @JH--使游标指向第一行
-- print @JH
while @@FETCH_STATUS=0
begin
set @S_JH=@S_JH+@JH+','
fetch next from JHCursor into @JH
end
close JHCursor--关闭游标
deallocate JHCursor--释放游标占用的空间
-- print @S_JH
if @S_JH<>''
set @S_JH=substring(@S_JH,1,len(@S_JH)-1)
end
----拼接查询条件
print '11111'
set @condition=' and a.S_CaoZuoY=''100233'''
if @S_ST<>'' and @S_ST<>'00'
set @condition=@condition+' and a.S_ST in('+@S_ST+')'
if @S_CID<>''
set @condition=@condition+' and a.S_CID='''+@S_CID+''''
if @waterType<>'' and @S_JH<>''
set @condition=@condition+' and a.I_JH in('+@S_JH+')'
select * into #biaoka from kg_biaokaxx where 1=2
set @sql='
select * from '+@BiaokaxxTableName+' where I_JiHuiYSJJFS>=0 '
insert into #biaoka exec(@sql)
--定义报表临时表1 本月临时报表
create table #NowMonth(I_JH int,
WaterType varchar(50),--用水分类
CountDate varchar(50),
BasicFee numeric(12, 2),--基本水费
SecondFee numeric(12,2),--二级水费
OverstepPlanFee numeric(12,2),--超计划水费
LateFee numeric(12,2),--滞纳金
SewageDisposeFee numeric(12,2),--污水处理费
TotalAmount numeric(12,2),--合计本月总额
------------------------
BasicFee1 numeric(12, 2),--历史基本水费
SecondFee1 numeric(12,2),--历史二级水费
OverstepPlanFee1 numeric(12,2),--历史超计划水费
LateFee1 numeric(12,2),--滞纳金
SewageDisposeFee1 numeric(12,2),--污水处理费
TotalAmount1 numeric(12,2),--合计历史总额
TotalCount numeric(12,2),--合计总额
)
--定义报表临时表2 本年临时报表
create table #ThisYear(I_JH int,
WaterType varchar(50),--用水分类
TotalBasicFee numeric(12, 2),--基本水费
TotalSecondFee numeric(12,2),--二级水费
TotalOverstepPlanFee numeric(12,2),--超计划水费
TotalLateFee numeric(12,2),--滞纳金
TotalSewageDisposeFee numeric(12,2),--污水处理费
YearTotalAmount numeric(12,2),--合计总额
CountDate varchar(50),
)
print '33333'
------------分质、非分质
declare @con varchar(100)
if @flag=0
set @con=' and c.I_BiaoZhongL=3'
else
set @con=' and c.I_BiaoZhongL<>3'
select * into #tmpJianMian from zw_jianmian where 1=2
set @sql='
select a.*
from '+@JianMianTableName+' as a
left join '+@BiaoKaxxTableName+' as c on a.s_cid=c.s_cid
where (a.DL_CaoZuoRQ>='+@s_BeginDate+' and a.DL_CaoZuoRQ<='+@s_EndDate+') and n_je>0
and a.I_ChuLi in(4,16) '+@con+@condition
print @sql
insert into #tmpJianMian exec(@sql)
if @exChange=0
begin
----------------本月统计临时表--------------------
--基本水费
create table #tmpBasicFee(I_JH int,N_ZongJinE numeric(18,2))
set @sql='select b.I_JH,sum(b.N_ZongJinE) ''N_ZongJinE'' from
#tmpJianMian as a
left join '+@JianMianZBTableName+' as b on a.i_jianmianbh=b.i_jianmianbh
where a.I_JLZT=0 and b.I_JLZT=0 and b.I_JieTiJB=0 and b.i_leixing=1
and b.I_FeiYongDLBH=580 and (a.i_y*100+a.i_m)='+@countDate+'
group by b.I_JH'
insert into #tmpBasicFee exec(@sql)
--二级水费
create table #tmpSecondFee(I_JH int,N_ZongJinE numeric(18,2))
set @sql='select b.I_JH,sum(b.N_ZongJinE) ''N_ZongJinE'' from
#tmpJianMian as a
left join '+@JianMianZBTableName+' as b on a.i_jianmianbh=b.i_jianmianbh
where a.I_JLZT=0 and b.I_JLZT=0 and b.I_JieTiJB>0 and a.s_cid not in (select s_cid from #biaoka)
and b.I_FeiYongDLBH=580 and b.i_leixing=1 and (a.i_y*100+a.i_m)='+@countDate+'
group by b.I_JH'
--print @sql
insert into #tmpSecondFee exec(@sql)
--超计划水费
create table #tmpOverstepPlanFee(I_JH int,N_ZongJinE numeric(18,2))
set @sql='select b.I_JH,sum(b.N_ZongJinE) ''N_ZongJinE'' from
#tmpJianMian as a
left join '+@JianMianZBTableName+' as b on a.i_jianmianbh=b.i_jianmianbh
where a.I_JLZT=0 and b.I_JLZT=0 and b.I_JieTiJB>0 and a.s_cid in (select s_cid from #biaoka)
and b.I_FeiYongDLBH=580 and b.i_leixing=1 and (a.i_y*100+a.i_m)='+@countDate+'
group by b.I_JH'
--print @sql
insert into #tmpOverstepPlanFee exec(@sql)
-----滞纳金
--set @sql='select a.I_JH,sum(b.N_ZongJinE) ''N_ZongJinE'' into ##tmpLateFee from
--#tmpJianMian as a
--left join '+@JianMianZBTableName+' as b on
--a.i_jianmianbh=b.i_jianmianbh
--where a.I_JLZT=0
--and b.I_JLZT=0'+@condition+'
--and (a.DL_CaoZuoRQ>='+@s_BeginDate+' and a.DL_CaoZuoRQ<='+@s_EndDate+')
--and a.I_ChuLi in(4,16)
--group by a.I_JH'
----print @sql
--exec(@sql)
--污水处理费
create table #tmpSewageDisposeFee(I_JH int,N_ZongJinE numeric(18,2))
set @sql='select b.I_JH,sum(isnull(N_ZongJinE,0)) as ''N_ZongJinE'' from
#tmpJianMian as a
left join '+@JianMianZBTableName+' as b on a.i_jianmianbh=b.i_jianmianbh
where a.I_JLZT=0 and b.I_JLZT=0 and b.I_JieTiJB=0 and b.i_leixing=1
and b.I_FeiYongDLBH=581 and (a.i_y*100+a.i_m)='+@countDate+' group by b.I_JH'
--print @sql
insert into #tmpSewageDisposeFee exec(@sql)
------------------------------------
select I_JH into #tmp3 from #tmpBasicFee --本月基本水费
select I_JH into #tmp4 from #tmpSecondFee --二级水费
select I_JH into #tmp5 from #tmpOverstepPlanFee --超计划水费
select I_JH into #tmp6 from #tmpSewageDisposeFee --污水处理费
--------------本月水费所有表--------------------------------
create table #first(i_jh int,
s_feileimc varchar(64)
,BasicCost numeric(18,2),
SecondLevelCost numeric(18,2),
OverPlanCost numeric(18,2),
LateFeeCost numeric(18,2),
SlopsCost numeric(18,2),
AllCost numeric(18,2),
YM varchar(16))
--------------合并本月水费所有表-----------------
insert into #first
select T.I_JH,F.S_FeiLeiMC,
isnull(A.N_ZongJinE,0) as 'BasicFee',
isnull(B.N_ZongJinE,0) as 'SecondFee',
isnull(C.N_ZongJinE,0) as 'OverstepPlanFee',
0 as 'LateFee',
isnull(D.N_ZongJinE,0) as 'SewageDisposeFee',
isnull(A.N_ZongJinE,0)+isnull(B.N_ZongJinE,0)+isnull(C.N_ZongJinE,0)+isnull(D.N_ZongJinE,0)
as 'TotalAmount',
@countYear+'年'+@countMonth+'月' as CountDate
from
(select I_JH from #tmp3 union select I_JH from #tmp4 union
select I_JH from #tmp5 union select I_JH from #tmp6) T
left join #tmpBasicFee A on T.I_JH=A.I_JH
left join #tmpSecondFee B on T.I_JH=B.I_JH
left join #tmpOverstepPlanFee C on T.I_JH=C.I_JH
left join #tmpSewageDisposeFee D on T.I_JH=D.I_JH
--left join ##tmpLateFee E on T.I_JH=E.I_JH
left join #TMPJMJH1 F on T.I_JH=F.I_JH
end
-------------------------------------------------------------------------------------------------------------------------------------------
----------减免历史基本水费、减免污水处理费---------
create table #lsBasicSlops(I_JH int,BasicCost numeric(18,2))
SET @SQL='SELECT t.*
FROM(
SELECT b.I_JH,SUM(b.N_ZongJinE) AS BasicCost
FROM #tmpJianMian AS a
LEFT JOIN '+@JianMianZBTableName+' AS b ON a.i_jianmianbh=b.i_jianmianbh
WHERE a.I_JLZT=0 and b.I_JLZT=0 AND b.I_FeiYongDLBH=580 AND b.I_JIETIJB=0 and b.i_leixing=1
and (a.i_y*100+a.i_m)<'+@countDate+' GROUP BY b.I_JH) t'
print @sql
insert into #lsBasicSlops EXEC(@SQL)
--污水费
create table #lsSlops(I_JH int,SlopsCost numeric(18,2))
SET @SQL='SELECT t1.*
FROM (
SELECT b.I_JH,SUM(b.N_ZongJinE) AS SlopsCost
FROM #tmpJianMian AS a
LEFT JOIN '+@JianMianZBTableName+' AS b ON a.i_jianmianbh=b.i_jianmianbh
WHERE a.I_JLZT=0 and b.I_JLZT=0 AND b.I_FeiYongDLBH=581 AND b.I_JIETIJB=0 and b.i_leixing=1
and (a.i_y*100+a.i_m)<'+@countDate+' GROUP BY b.I_JH
) t1'
print @sql
insert into #lsSlops EXEC(@SQL)
----------减免历史二级水费---------------------
create table #lsSecondLevel(I_JH int,SecondLevelCost numeric(18,2))
SET @SQL='SELECT b.I_JH,SUM(b.N_ZongJinE) AS SecondLevelCost
FROM #tmpJianMian AS a
LEFT JOIN '+@JianMianZBTableName+' AS b ON a.i_jianmianbh=b.i_jianmianbh
WHERE a.I_JLZT=0 and b.I_JLZT=0 AND b.I_FeiYongDLBH=580 AND b.I_JIETIJB>0 and b.i_leixing=1
and (a.i_y*100+a.i_m)<'+@countDate+' and a.s_cid not in (select s_cid from #biaoka)
GROUP BY b.I_JH'
insert into #lsSecondLevel EXEC(@SQL)
----------减免历史超计划水费-------------------
create table #lsOverPlan(I_JH int,OverPlanCost numeric(18,2))
SET @SQL='SELECT b.I_JH,SUM(b.N_ZongJinE) AS OverPlanCost
FROM #tmpJianMian AS a
LEFT JOIN '+@JianMianZBTableName+' AS b ON a.i_jianmianbh=b.i_jianmianbh
WHERE a.I_JLZT=0 and b.I_JLZT=0 AND b.I_FeiYongDLBH=580 AND b.I_JIETIJB>0 and b.i_leixing=1
and (a.i_y*100+a.i_m)<'+@countDate+' and a.s_cid in (select s_cid from #biaoka)
GROUP BY b.I_JH'
insert into #lsOverPlan EXEC(@SQL)
--------------------创建历史水费所有表
create table #second(
i_jh int,
s_feileimc varchar(64),
BasicCost numeric(18,2),
SecondLevelCost numeric(18,2),
OverPlanCost numeric(18,2),
LateFeeCost numeric(18,2),
SlopsCost numeric(18,2),
AllCost numeric(18,2),
YM varchar(16))
-----------------合并历史所有表-------------------
select distinct i_jh into #lsjh from #lsBasicSlops
union select i_jh from #lsSecondLevel
union select i_jh from #lsOverPlan
union select i_jh from #lsSlops
insert into #second SELECT DISTINCT t.i_jh,S_FeiLeiMC
,isnull(a.BasicCost,0) as BasicCost
,isnull(b.SecondLevelCost,0) as SecondLevelCost
,isnull(c.OverPlanCost,0) as OverPlanCost
,0 AS LateFeeCost
,isnull(e.SlopsCost,0) as SlopsCost
,ISNULL(a.BasicCost,0)+ISNULL(e.SlopsCost,0)+ISNULL(b.SecondLevelCost,0)+ISNULL(c.OverPlanCost,0) AS AllCost
, @countYear+'年'+@countMonth+'月' as CountDate
FROM #lsjh AS t
LEFT JOIN #lsBasicSlops AS a ON t.I_JH=a.I_JH
LEFT JOIN #lsSecondLevel AS b ON t.I_JH=b.I_JH
LEFT JOIN #lsOverPlan AS c ON t.I_JH=c.I_JH
LEFT JOIN #lsSlops AS e ON t.I_JH=e.I_JH
LEFT JOIN (select * from JG_YongShuiFL where
i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia )) JG_YongShuiFL ON t.I_JH=JG_YongShuiFL.I_JH
--#secon 历史水费所有表 #first本月水费所有表 #NowMonth 本月临时表（包含历史和非历史所有） #ThisYear 本年临时表 不含历史
-------------------------------------------------------------------------------------------------------------------------------------------
---本月临时统计
select distinct i_jh,S_FeiLeiMC,YM into #he from #first
union select i_jh,S_FeiLeiMC,YM from #second
insert into #NowMonth
select a.i_jh,a.S_FeiLeiMC,a.YM,
isnull(b.BasicCost,0) as BasicCost ,
isnull(b.SecondLevelCost,0) as SecondLevelCost,
isnull(b.OverPlanCost,0) as OverPlanCost,
isnull(b.LateFeeCost,0) as LateFeeCost ,
isnull(b.SlopsCost,0) as SlopsCost,
isnull(b.AllCost,0) as AllCost, --本月合计
isnull(c.BasicCost,0) as BasicCost1,
isnull(c.SecondLevelCost,0) as SecondLevelCost1,--历史二级水费
isnull(c.OverPlanCost,0) as OverPlanCost1,--历史超计划水费
isnull(c.LateFeeCost,0) as LateFeeCost1,
isnull(c.SlopsCost,0) as SlopsCost1,
isnull(c.AllCost,0) as AllCost1, --本月历史减免合计
isnull(b.AllCost,0)+isnull(c.AllCost,0) as Allcount
from #he as a
left join #first as b on a.i_jh=b.i_jh
left join #second as c on a.i_jh=c.i_jh
---------------------本年统计临时表------------------
declare @year varchar(10),--当前时间的年份
@m int,--月份循环变量
@month int,--当前时间的月份
@tmpYearM varchar(50),--循环使用账务年月
@s_StartDate varchar(50),
@I_EndDate varchar(50)
set @year=convert(char(4),@countDate,112)
set @month=cast(substring(convert(char(8),@countDate,112),5,2) as int) --获取当前输入日期的月份
set @m=1
--print @month
-------------------------------------------------
---创建基本水费临时表
create table #tmpBasicFee1(I_JH int,N_ZongJinE numeric(12,2))
---创建二级水费临时表
create table #tmpSecondFee1(I_JH int,N_ZongJinE numeric(12,2))
---创建超计划水费临时表
create table #tmpOverstepPlanFee1(I_JH int,N_ZongJinE numeric(12,2))
---创建污水处理费临时表
create table #tmpSewageDisposeFee1(I_JH int,N_ZongJinE numeric(12,2))
---创建滞纳金临时表
--create table #tmpLateFee1(I_JH int,N_ZongJinE numeric(12,2))
----------减免历史污水费
create table #ylsSlops(I_JH int,SlopsCost numeric(18,2))
----------减免历史基本水费
create table #ylsBasicSlops(I_JH int,BasicCost numeric(18,2))
----------减免历史二级水费
create table #ylsSecondLevel(I_JH int,SecondLevelCost numeric(18,2))
----------减免历史超计划水费
create table #ylsOverPlan(I_JH int,OverPlanCost numeric(18,2))
---while循环遍历从1月到当前月的各种费用
while @m<=@month --Condition here must be int or some type inherits int
begin
if len(@m)=1
set @tmpYearM=@year+'0'+cast(@m as varchar(5))
else
set @tmpYearM=@year+cast(@m as varchar(5))
print @tmpYearM
-------得到账务年月范围---------
select @s_StartDate=convert(varchar(8),D_KaiShiRQ,112),
@I_EndDate=convert(varchar(8),D_JieShuRQ,112)
from SYS_JieZhuanRZ where I_ZhangWuNY=@tmpYearM
print @s_StartDate
--
if @s_StartDate is null
begin
set @m=@m+1
print @m
continue--重新开始循环
end
--得到当前账务年月
select @NowMonth=max(I_ZhangWuNY) from SYS_JieZhuanRZ
set @JianMianTableName='ZW_JianMian'
set @JianMianZBTableName='ZW_JianMian_ZB'
select @BiaoKaxxTableName=dbo.F_GetTableName(@tmpYearM,'KG_BiaoKaXX')
delete from #tmpJianMian
set @sql='
select a.*
from '+@JianMianTableName+' as a
left join '+@BiaoKaxxTableName+' as c on a.s_cid=c.s_cid
where (a.DL_CaoZuoRQ>='+@s_StartDate+' and a.DL_CaoZuoRQ<='+@I_EndDate+') and n_je>0
and a.I_ChuLi in(4,16) and (a.i_y*100+a.i_m)<='+@tmpYearM+@con+@condition
insert into #tmpJianMian exec(@sql)
--
delete from #biaoka
set @sql='
select * from '+@BiaokaxxTableName+' where I_JiHuiYSJJFS>=0 '
insert into #biaoka exec(@sql)
---基本水费
set @sql='insert into #tmpBasicFee1
select b.I_JH,sum(b.N_ZongJinE) ''N_ZongJinE'' from
#tmpJianMian as a
left join '+@JianMianZBTableName+' as b on a.i_jianmianbh=b.i_jianmianbh
where a.I_JLZT=0 and b.I_JLZT=0 and b.I_JieTiJB=0 and b.i_leixing=1
and b.I_FeiYongDLBH=580 group by b.I_JH'
print @sql
exec(@sql)
print @s_BeginDate
--合计本年二级水费
set @sql='insert into #tmpSecondFee1
select b.I_JH,sum(b.N_ZongJinE) ''N_ZongJinE'' from
#tmpJianMian as a
left join '+@JianMianZBTableName+' as b on a.i_jianmianbh=b.i_jianmianbh
where a.I_JLZT=0 and b.I_JLZT=0 and b.I_JieTiJB>0 and b.i_leixing=1 and a.s_cid not in (select s_cid from #biaoka)
and b.I_FeiYongDLBH=580 group by b.I_JH'
-- print @sql
exec(@sql)
--合计超计划水费
set @sql='insert into #tmpOverstepPlanFee1
select b.I_JH,sum(b.N_ZongJinE) ''N_ZongJinE'' from
#tmpJianMian as a
left join '+@JianMianZBTableName+' as b on a.i_jianmianbh=b.i_jianmianbh
where a.I_JLZT=0 and b.I_JLZT=0 and b.I_JieTiJB>0 and b.i_leixing=1 and a.s_cid in (select s_cid from #biaoka)
and b.I_FeiYongDLBH=580 group by b.I_JH'
-- print @sql
exec(@sql)
-- ---滞纳金
-- set @sql='insert into #tmpLateFee1
-- select a.I_JH,sum(b.N_ZongJinE) ''N_ZongJinE'' from
-- #tmpJianMian as a
-- left join '+@JianMianZBTableName+' as b on
-- a.i_jianmianbh=b.i_jianmianbh
-- where a.I_JLZT=0
-- and b.I_JLZT=0'+@condition+'
-- and (a.DL_CaoZuoRQ>='+@s_BeginDate+' and a.DL_CaoZuoRQ<='+@s_EndDate+')
-- and a.I_ChuLi in(4,16) group by a.I_JH'
---- print @sql
-- exec(@sql)
--合计污水处理费
set @sql='insert into #tmpSewageDisposeFee1
select b.I_JH,sum(isnull(b.N_ZongJinE,0)) as ''N_ZongJinE'' from
#tmpJianMian as a
left join '+@JianMianZBTableName+' as b on
a.i_jianmianbh=b.i_jianmianbh
where a.I_JLZT=0 and b.I_JLZT=0 and b.I_JieTiJB=0 and b.i_leixing=1
and b.I_FeiYongDLBH=581 group by b.I_JH'
-- print @sql
exec(@sql)
----------------------------------------------------------历史减免--------------------------------------------------------
SET @SQL='insert into #ylsBasicSlops SELECT t.*
FROM(
SELECT b.I_JH,SUM(b.N_ZongJinE) AS BasicCost
FROM #tmpJianMian AS a
LEFT JOIN '+@JianMianZBTableName+' AS b ON a.i_jianmianbh=b.i_jianmianbh
WHERE a.I_JLZT=0 and b.I_JLZT=0 AND b.I_FeiYongDLBH=580 AND b.I_JIETIJB=0 and b.i_leixing=1 GROUP BY b.I_JH) t'
EXEC(@SQL) print @sql
SET @SQL='insert into #ylsSlops SELECT t1.*
FROM (
SELECT b.I_JH,SUM(b.N_ZongJinE) AS SlopsCost
FROM #tmpJianMian AS a
LEFT JOIN '+@JianMianZBTableName+' AS b ON a.i_jianmianbh=b.i_jianmianbh
WHERE a.I_JLZT=0 and b.I_JLZT=0 AND b.I_FeiYongDLBH=581 AND b.I_JIETIJB=0 and b.i_leixing=1
GROUP BY b.I_JH
) t1'
EXEC(@SQL) print @sql
SET @SQL='insert into #ylsSecondLevel SELECT b.I_JH,SUM(b.N_ZongJinE) AS SecondLevelCost
FROM #tmpJianMian AS a
LEFT JOIN '+@JianMianZBTableName+' AS b ON a.i_jianmianbh=b.i_jianmianbh
WHERE a.I_JLZT=0 and b.I_JLZT=0 AND b.I_FeiYongDLBH=580 AND b.I_JIETIJB>0 and b.i_leixing=1
and a.s_cid not in (select s_cid from #biaoka)GROUP BY b.I_JH'
EXEC(@SQL) print @sql
SET @SQL='insert into #ylsOverPlan SELECT b.I_JH,SUM(b.N_ZongJinE) AS OverPlanCost
FROM #tmpJianMian AS a
LEFT JOIN '+@JianMianZBTableName+' AS b ON a.i_jianmianbh=b.i_jianmianbh
WHERE a.I_JLZT=0 and b.I_JLZT=0 AND b.I_FeiYongDLBH=580 AND b.I_JIETIJB>0 and b.i_leixing=1
and a.s_cid in (select s_cid from #biaoka) GROUP BY b.I_JH'
EXEC(@SQL) print @sql
--------------------------------------------------------结束历史减免统计------------------------------------------------------
----------------改变循环控制变量-----------
set @m=@m+1
end
--------------------创建历史水费所有表
create table #ysecond(
i_jh int,
--s_feileimc varchar(64),
BasicCost numeric(18,2),
SecondLevelCost numeric(18,2),
OverPlanCost numeric(18,2),
LateFeeCost numeric(18,2),
SlopsCost numeric(18,2),
AllCost numeric(18,2),
YM varchar(16))
-----------------合并历史所有表-------------------
select distinct i_jh into #ylsjh from #ylsBasicSlops
union select i_jh from #ylsSecondLevel
union select i_jh from #ylsOverPlan
union select i_jh from #ylsSlops
insert into #ysecond SELECT DISTINCT t.i_jh
--,S_FeiLeiMC
,isnull(a.BasicCost,0) as BasicCost
,isnull(b.SecondLevelCost,0) as SecondLevelCost
,isnull(c.OverPlanCost,0) as OverPlanCost
,0 AS LateFeeCost
,isnull(e.SlopsCost,0) as SlopsCost
,ISNULL(a.BasicCost,0)+ISNULL(e.SlopsCost,0)+ISNULL(b.SecondLevelCost,0)+ISNULL(c.OverPlanCost,0) AS AllCost
, @countYear+'年'+@countMonth+'月' as CountDate
FROM #ylsjh AS t
LEFT JOIN #ylsBasicSlops AS a ON t.I_JH=a.I_JH
LEFT JOIN #ylsSecondLevel AS b ON t.I_JH=b.I_JH
LEFT JOIN #ylsOverPlan AS c ON t.I_JH=c.I_JH
LEFT JOIN #ylsSlops AS e ON t.I_JH=e.I_JH
LEFT JOIN (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia )) JG_YongShuiFL ON t.I_JH=JG_YongShuiFL.I_JH
--创建临时表 保存 #ysecond中部分内容
create table #tmpsec(
i_jh int,
yBasicCost numeric(18,2),
ySecondLevelCost numeric(18,2),
yOverPlanCost numeric(18,2),
yLateFeeCost numeric(18,2),
ySlopsCost numeric(18,2))
insert into #tmpsec
select i_jh, sum(BasicCost),sum(SecondLevelCost),sum(OverPlanCost),sum(LateFeeCost),sum(SlopsCost) from #ysecond group by i_jh
print 'ssssssss'
------------------------------------------------结束历史所有表----------------------------------------------------------------
select I_JH,sum(N_ZongJinE) 'N_ZongJinE' into #tmpBasic
from #tmpBasicFee1 group by I_JH
select I_JH,sum(N_ZongJinE) 'N_ZongJinE' into #tmpSecond
from #tmpSecondFee1 group by I_JH
select I_JH,sum(N_ZongJinE) 'N_ZongJinE' into #tmpOverstepPlan
from #tmpOverstepPlanFee1 group by I_JH
select I_JH,sum(N_ZongJinE) 'N_ZongJinE' into #tmpSewageDispose
from #tmpSewageDisposeFee1 group by I_JH
-- select I_JH,sum(N_ZongJinE) 'N_ZongJinE' into #tmpLate
-- from #tmpLateFee1 group by I_JH
select I_JH into #tmp31 from #tmpBasic
select I_JH into #tmp41 from #tmpSecond
select I_JH into #tmp51 from #tmpOverstepPlan
select I_JH into #tmp61 from #tmpSewageDispose
-- select I_JH into #tmp71 from #tmpLate
-----------------------年统计---------------------------
insert into #ThisYear
select T.I_JH,F.S_FeiLeiMC,--用水分类
isnull(A.N_ZongJinE,0) as 'TotalBasicFee', --基本水费
isnull(B.N_ZongJinE,0) as 'TotalSecondFee', --二级水费
isnull(C.N_ZongJinE,0) as 'TotalOverstepPlanFee',--超计划水费
0 as 'TotalLateFee', ---滞纳金
isnull(D.N_ZongJinE,0) as 'TotalSewageDisposeFee',--污水处理费
isnull(A.N_ZongJinE,0)+isnull(B.N_ZongJinE,0)+isnull(C.N_ZongJinE,0)+isnull(D.N_ZongJinE,0)
as 'YearTotalAmount', --本年合计减免
@countYear+'年'+@countMonth+'月' as CountDate
from (
select I_JH from #tmp31
union
select I_JH from #tmp41
union
select I_JH from #tmp51
union
select I_JH from #tmp61
)T
left join #tmpBasic A on T.I_JH=A.I_JH
left join #tmpSecond B on T.I_JH=B.I_JH
left join #tmpOverstepPlan C on T.I_JH=C.I_JH
left join #tmpSewageDispose D on T.I_JH=D.I_JH
left join #TMPJMJH1 F on T.I_JH=F.I_JH
left join #ysecond G on T.I_JH=G.I_JH
-- left join #tmpLate E on T.I_JH=E.I_JH
----------------------------------------------
select distinct i_jh,CountDate,WaterType into #jh from #NowMonth
union
select i_jh,CountDate,WaterType from #ThisYear
---------------------------------------------------------------------------------------
if @exChange=0
begin
select distinct
f.s_feileimc as s_daleimc, --用户类别
t. CountDate, --统计年月
t.WaterType, --用水类别
isnull(a.BasicFee,0) 'BasicFee', --本月基本水费
isnull(a.SecondFee,0) 'SecondFee', --本月二级水费
isnull(a.OverstepPlanFee,0) 'OverstepPlanFee',--本月超计划水费
isnull(a.LateFee,0) 'LateFee', --本月滞纳金
isnull(a.SewageDisposeFee,0) 'SewageDisposeFee', --本月污水处理费
isnull(a.TotalAmount,0) 'TotalAmount', --本月合计
isnull(a.BasicFee1,0) 'BasicFee1', --历史基本水费
isnull(a.SecondFee1,0) 'SecondFee1', --历史二级水费
isnull(a.OverstepPlanFee1,0) 'OverstepPlanFee1',--历史超计划水费
isnull(a.LateFee1,0) 'LateFee1', --历史滞纳金
isnull(a.SewageDisposeFee1,0) 'SewageDisposeFee1', --历史污水处理费
isnull(a.TotalAmount1,0) 'TotalAmount1', --历史合计
isnull(a.TotalCount,0) as TotalCount, --减免合计
b.TotalBasicFee, --基本水费
b.TotalSecondFee, --二级水费
b.TotalOverstepPlanFee, --超计划水费
b.TotalLateFee, --滞纳金
b.TotalSewageDisposeFee, --污水处理费
------------
c.yBasicCost,
c.ySecondLevelCost,
c.yOverPlanCost,
c.yLateFeeCost,
isnull(c.ySlopsCost,0) 'ySlopsCost',
--0 as SlopsCost,
--c.AllCost ,
ISNULL(c.yBasicCost,0)+ISNULL(c.ySecondLevelCost,0)+ISNULL(c.yLateFeeCost,0)+ISNULL(c.yLateFeeCost,0)+ISNULL(c.ySlopsCost,0) AS AllCost,
----------------------
b.YearTotalAmount --合计
from #jh as t
left join #NowMonth as a on t.i_jh=a.i_jh
left join #ThisYear as b on t.I_JH=b.I_JH
left join #tmpsec as c on t.I_JH=c.I_JH
left JOIN (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia )) JG_YongShuiFL ON t.I_JH= JG_YongShuiFL.I_JH
left join (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia )) f on JG_YongShuiFL.I_ANCESTOR=f.ID
end
else
begin
select distinct f.s_feileimc as s_daleimc ,
b.WaterType,
a.*,
b.CountDate,
b.TotalBasicFee,
b.TotalSecondFee,
b.TotalOverstepPlanFee,
b.TotalLateFee,
b.TotalSewageDisposeFee,
----------------
c.yBasicCost as 'yBasicCost',
c.ySecondLevelCost as'ySecondLevelCost',
c.yOverPlanCost as'yOverPlanCost',
c.yLateFeeCost as 'yLateFeeCost',
c.ySlopsCost as 'ySlopsCost',
--0 as SlopsCost,
--c.AllCost,
ISNULL(c.yBasicCost,0)+ISNULL(c.ySecondLevelCost,0)+ISNULL(c.yLateFeeCost,0)+ISNULL(c.yLateFeeCost,0)+ISNULL(c.ySlopsCost,0) AS AllCost,
b.YearTotalAmount
from #NowMonth as a right join #ThisYear as b on a.I_JH=b.I_JH right join #tmpsec as c on b.I_JH=c.I_JH
LEFT JOIN (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia ))JG_YongShuiFL ON b.I_JH=JG_YongShuiFL.I_JH
left join (select * from JG_YongShuiFL where i_tiaojiah=(select max(i_tiaojiah) from jg_tiaojia )) f on JG_YongShuiFL.I_ANCESTOR=f.ID
end
drop table #NowMonth
drop table #ThisYear
drop table #tmpJianMian
转载于:https://www.cnblogs.com/zq281660880/archive/2011/04/15/2017272.html
963
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
