IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetInsAccount_Calculate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetInsAccount_Calculate]
GO

-- =============================================
--exec [GetInsAccount_Calculate] 1,'2011','4','110104','','','0','0','','1','1','1',''
-- exec [GetInsAccount_Calculate] 2,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetInsAccount_Calculate] 4,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetInsAccount_Calculate] 6,'2011','1','','','','0','0','','1','1','1',''
-- Author:		<Author,huabo,>
-- Create date: <Create Date,2011-10-25,>
-- Description:	<Description,废气监测点达标率计算>
-- =============================================
CREATE proc [dbo].[GetInsAccount_Calculate]
(
	@type int ,   --按照XX类型统计,例如：行业，地区
	@year varchar(4),  --监测年
	@quarter varchar(20), --季度
	@citycode varchar(max),  ---行政区编码
	@tradecode varchar(max), ---行业代码
	@whatercode varchar(max), ---
	@controltype varchar(50),  ---废水控制级别
	@polutioncode varchar(max), ---监测项目编号
	@controltypegas varchar(50), ---废气控制级别
	@enterprisetype varchar(10),---企业类型
	@joinType varchar(10),   ----1.未测不参与评价。2.未测按不达标计算
	@pjType varchar(10),   ---1.实测浓度。2.按单位基准排水量计算
	@timeWhere varchar(100)  ---时间段
)
as
--变量设置开始
declare @tempCity varchar(max)
declare @tempTrade varchar(max)
declare @tempControlType varchar(50)
declare @tempControlTypeGas varchar(50)
declare @temppolutionCode varchar(max)
declare @tempenterprisetype varchar(100)
declare @tempjointype varchar(100)
declare @temppjtype varchar(100)
declare @temptimewhere varchar(100)

set @tempCity=''
set @tempTrade=''
set @temppolutionCode=''
set @tempControlType=''
set @tempControlTypeGas=''
set @tempenterprisetype='1'
set @tempjointype=''
set @temppjtype='isnull(污染物浓度是否达标,0)'
set @temptimewhere=''

--行政区代码
if @citycode!=''
begin
	set @tempCity=@citycode
end
--行业代码
if @tradecode!=''
begin
	set @tempTrade=' and 行业代码 in('+@tradecode+') '
end
--控制级别
if @controltype!='0'
begin
	set @tempControlType=' and 污染源控制级别代码 in ('+@controltype+') '
end
--气控制级别
if @controltypegas!='0'
begin
	set @tempControlTypeGas=' and 污染源控制级别代码气 in ('+@controltypegas+') '
end
--监测项目
if @polutioncode!=''
begin
	set @temppolutionCode=' and 监测项目编号 in ('+@polutioncode+')'
end
--企业类型
if @enterprisetype<>''
begin
	set @tempenterprisetype=@enterprisetype
end
--如果评价类型为“实测浓度”
if @pjType='1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'--不参加评价
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--如果评价类型为“基准浓度”
else if @pjType='2'
begin
	set @temppjtype='基准浓度是否达标'
	if @joinType='1'
	begin
		set @tempjointype=' and 是否参与基准浓度=1 '
	end
	else
    begin
        set @tempjointype=' and 是否参与基准浓度 is not null ' --是否参与基准浓度is null的数据是无效的，需要过滤掉，类似于没有标准值的监测项目对于达标计算而言是无意义的。
    end
end
--如果企业类型不是污染源
if @enterprisetype<>'1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'   --不参加评价
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--时间条件
if @timeWhere is not null and @timeWhere<>''
begin
	set @temptimewhere=@timeWhere
end
else
begin
	set @temptimewhere=' and 监测年='+@year+' and 季度 in ('+@quarter+') '
end

-----主体部分开始
declare @sSQL nvarchar (MAX)
BEGIN
if @type=1  --按行业查询
begin		
	set @sSQL='
	/*计算某季度、某次，监测点是否达标*/
	select 监测年,季度,次数,行业,行业编码,详细名称,市,监测点代码,
	case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
	into #temp from 
	(
		select a.企业类型,a.监测年,a.季度,a.次数,c.行业,b.行业代码,c.行业代码 as 行业编码,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,
		a.浓度是否达标 as 污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,
		b.行政区代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,市,a.排放上限,
		convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期  
		from 业务_废气监测数据 a join V_工业和固废企业_简单 b
		on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
		inner join (select rtrim(代码编码) as 行业代码,代码名称 as 行业 from 系统_代码 where 代码类型编码=15) c
		on substring(b.行业代码,1,2)=c.行业代码
		inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) d
		on substring(b.行政区代码,1,4)+''00''=d.市代码
	)a 
	where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@temppolutionCode+@tempjointype+'
	group by 监测年,季度,次数,行业,行业编码,详细名称,市,监测点代码
	
	select 代码名称,代码编码,count(详细名称) as 企业数量,cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率,sum(监测点数量) as 监测点数量
	from (
		/*计算某企业，监测点数量，及监测点达标率*/
		select 监测年 as 年度,行业 as 代码名称,行业编码 as 代码编码,详细名称,市,avg(监测点达标率) as 监测点达标率,count(监测点代码) as 监测点数量
		from 
		(	/*计算某企业某监测点达标率*/
			select 监测年,行业,行业编码,详细名称,市,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率 
			from #temp group by 监测年,行业,行业编码,详细名称,市,监测点代码
		 ) a group by 监测年,行业,行业编码,详细名称,市 
	) a group by 代码名称,代码编码
	union
	select ''全部''代码名称,''000000'' as 代码编码,count(详细名称) as 企业数量,cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率,sum(监测点数量) as 监测点数量
	from (
		/*计算某企业，监测点数量，及监测点达标率*/
		select 监测年 as 年度,行业 as 代码名称,行业编码 as 代码编码,详细名称,市,avg(监测点达标率) as 监测点达标率,count(监测点代码) as 监测点数量
		from 
		(	/*计算某季度某次监测点达标率*/
			select 监测年,行业,行业编码,详细名称,市,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率 
			from #temp group by 监测年,行业,行业编码,详细名称,市,监测点代码
		 ) a group by 监测年,行业,行业编码,详细名称,市 
	) a
	order by 代码编码,代码名称'
end
if(@type=2)--按地区查询
begin
set @sSQL='
	/*计算某季度、某次，监测点是否达标*/
	select 监测年,季度,次数,省,市,省代码,市代码,详细名称,监测点代码,
	case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
	into #temp from 
	(
		select a.企业类型,a.监测年,a.季度,a.次数,c.省,d.市,c.省代码,d.市代码,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,
		a.浓度是否达标 as 污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,
		b.行政区代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,b.行业代码,a.排放上限,
		convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期  
		from 业务_废气监测数据 a join V_工业和固废企业_简单 b
		on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
		inner join (select rtrim(代码编码) as 省代码,代码名称 as 省 from 系统_代码 where 代码类型编码=14) c
		on substring(b.行政区代码,1,2)+''0000''=c.省代码
		inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) d
		on substring(b.行政区代码,1,4)+''00''=d.市代码
	)a 
	where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@temppolutionCode+@tempjointype+'
	group by 监测年,季度,次数,省,市,省代码,市代码,详细名称,监测点代码
	
	select 省+''/''+市 as 代码名称,省代码+''/''+市代码 as 代码编码,count(详细名称) as 企业数量,sum(监测点数量) as 监测点数量,
	cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率
	from 
	(	/*计算某企业，监测点数量，及监测点达标率*/
		select 监测年,省,市,省代码,市代码,详细名称,avg(监测点达标率) as 监测点达标率,count(监测点代码) as 监测点数量
		from 
		(	/*计算某季度某次监测点达标率*/
			select 监测年,省,市,省代码,市代码,详细名称,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率
			from #temp group by 监测年,省,市,省代码,市代码,详细名称,监测点代码
		) a group by 监测年,省,市,省代码,市代码,详细名称
	) a group by 监测年,省,市,省代码,市代码
	union
	select ''全部'' as 代码名称,''000000'' as 代码编码,count(详细名称) as 企业数量,sum(监测点数量) as 监测点数量,
	cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率
	from 
	(	/*计算某企业，监测点数量，及监测点达标率*/
		select 监测年,省,市,省代码,市代码,详细名称,avg(监测点达标率) as 监测点达标率,count(监测点代码) as 监测点数量
		from 
		(	/*计算某季度某次监测点达标率*/
			select 监测年,省,市,省代码,市代码,详细名称,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率
			from #temp group by 监测年,省,市,省代码,市代码,详细名称,监测点代码
		) a group by 监测年,省,市,省代码,市代码,详细名称
	) a
	order by 代码编码'
end
if(@type=4)--按地区查询
begin
	set @sSQL=
		'/*计算某季度、某次，监测点是否达标*/
		select 监测年,季度,次数,省,省代码,详细名称,市,监测点代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
		into #temp from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,c.省,c.省代码,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,
			a.浓度是否达标 as 污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,
			b.行政区代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,b.行业代码,市,a.排放上限,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期  
			from 业务_废气监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
			inner join (select rtrim(代码编码) as 省代码,代码名称 as 省 from 系统_代码 where 代码类型编码=14) c
			on substring(b.行政区代码,1,2)+''0000''=c.省代码
			inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) d
			on substring(b.行政区代码,1,4)+''00''=d.市代码
		)a 
		where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@temppolutionCode+@tempjointype+'
		group by 监测年,季度,次数,省,省代码,详细名称,市,监测点代码
		
		select 省 as 代码名称,省代码 as 代码编码,count(详细名称) as 企业数量,cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率,sum(监测点数量) as 监测点数量
		from 
		(	/*计算某企业，监测点数量，及监测点达标率*/
			select 监测年,省,省代码,详细名称,市,avg(监测点达标率) as 监测点达标率,count(监测点代码) as 监测点数量
			from 
			(	/*计算某季度某次监测点达标率*/
				select 监测年,省,省代码,详细名称,市,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率
				from #temp group by 监测年,省,省代码,详细名称,市,监测点代码
			) a group by 监测年,省,省代码,详细名称,市
		) a group by 省,省代码 
		union
		select ''全部'' as 代码名称,''000000'' as 代码编码,count(详细名称) as 企业数量,cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率,sum(监测点数量) as 监测点数量
		from 
		(	/*计算某企业，监测点数量，及监测点达标率*/
			select 监测年,省,省代码,详细名称,市,avg(监测点达标率) as 监测点达标率,count(监测点代码) as 监测点数量
			from 
			(	/*计算某季度监测点达标率*/
				select 监测年,省,省代码,详细名称,市,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率
				from #temp group by 监测年,省,省代码,详细名称,市,监测点代码
			) a group by 监测年,省,省代码,详细名称,市
		) a
		order by 代码编码'
end

exec(@sSQL)
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCropGasAccount_Calculate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCropGasAccount_Calculate]
GO

-- =============================================
-- exec [GetCropGasAccount_Calculate] 1,'2011','4','','','','0','0','','1','1','1','14        ',''
-- exec [GetCropGasAccount_Calculate] 2,'2011','1','','','','0','0','','1','1','1','110100    ',''
-- exec [GetCropGasAccount_Calculate] 4,'2011','1','','','','0','0','','1','1','1','120000    ',''
-- exec [GetCropGasAccount_Calculate] 6,'2011','1','','','','0','0','','1','1','1','72954',''
-- Author:		<Author,huabo,>
-- Create date: <Create Date,2011-10-25,>
-- Description:	<Description,废气监测点企业达标率计算>
-- =============================================
CREATE proc [dbo].[GetCropGasAccount_Calculate]
(
	@type int,
	@year varchar(4),
	@quarter varchar(20),
	@citycode varchar(max),
	@tradecode varchar(max),
	@whatercode varchar(max),
	@controltype varchar(50),
	@priparm varchar(50),
	@polutioncode varchar(max),
	@controltypegas varchar(50),
	@enterprisetype varchar(10),
	@joinType varchar(10),
	@pjType varchar(10),
	@timeWhere varchar(100)
)
as
--变量设置开始
declare @tempCity varchar(max)
declare @tempTrade varchar(max)
declare @tempControlType varchar(50)
declare @tempControlTypeGas varchar(50)
declare @temppolutionCode varchar(max)
declare @tempenterprisetype varchar(100)
declare @tempjointype varchar(100)
declare @temppjtype varchar(100)
declare @temppriparm varchar(100)
declare @temptimewhere varchar(100)

set @tempCity=''
set @tempTrade=''
set @temppolutionCode=''
set @tempControlType=''
set @tempControlTypeGas=''
set @tempenterprisetype='1'
set @tempjointype='   '
set @temppjtype='isnull(污染物浓度是否达标,0)'
set @temppriparm=''

--行政区代码
if @citycode!=''
begin
	set @tempCity=@citycode
end
--行业代码
if @tradecode!=''
begin
	set @tempTrade=' and 行业编码 in('+@tradecode+') '
end
--控制级别
if @controltype!='0'
begin
	set @tempControlType=' and 污染源控制级别代码 in ('+@controltype+') '
end
--气控制级别
if @controltypegas!='0'
begin
	set @tempControlTypeGas=' and 污染源控制级别代码气 in ('+@controltypegas+') '
end
--监测项目
if @polutioncode!=''
begin
	set @temppolutionCode=' and 监测项目编号 in ('+@polutioncode+')'
end
--企业类型
if @enterprisetype<>''
begin
	set @tempenterprisetype=@enterprisetype
end
--如果评价类型为“实测浓度”
if @pjType='1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--如果评价类型为“基准浓度”
else if @pjType='2'
begin
	set @temppjtype='基准浓度是否达标'
	if @joinType='1'
	begin
		set @tempjointype=' and 是否参与基准浓度=1 '
	end
	else
    begin
        set @tempjointype=' and 是否参与基准浓度 is not null ' --是否参与基准浓度is null的数据是无效的，需要过滤掉，类似于没有标准值的监测项目对于达标计算而言是无意义的。
    end
end
--如果企业类型不是污染源
if @enterprisetype<>'1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end

--过滤主键条件
if @type=1 and @priparm<>'000000'
begin
	set @temppriparm=' and 行业编码='''+@priparm+''' '
end
if @type=2 and @priparm<>'000000'
begin
	set @temppriparm=' and 市级行政区代码='''+@priparm+''' '
end
if @type=4 and @priparm<>'000000'
begin
	set @temppriparm=' and 省级行政区代码='''+@priparm+''' '
end
if @type=6 and @priparm<>'000000'
begin
	set @temppriparm=' and 区域代码='''+@priparm+''' '
end
--时间条件
if @timeWhere is not null and @timeWhere<>''
begin
	set @temptimewhere=@timeWhere
end
else
begin
	set @temptimewhere=' and 监测年='+@year+' and 季度 in ('+@quarter+') '
end
                   
-----主体部分开始
declare @sSQL nvarchar (MAX)
BEGIN

if (@type=1 or @type=2 or @type=4) 
begin		
	set @sSQL=
	'with cte as
	(
		/*计算某季度、某次，监测点是否达标*/
		select 监测年,季度,次数,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,监测点代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
		from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,c.行业,c.行业代码 as 行业编码,b.行政区代码,c1.省级行政区名称,c2.市级行政区名称,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,
			a.浓度是否达标 as 污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,c1.省级行政区代码,c2.市级行政区代码,
			b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,a.排放上限,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期 
			from 业务_废气监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
			inner join (select 代码编码 as 行业代码,代码名称 as 行业 from 系统_代码 where 代码类型编码=15) c
			on substring(b.行业代码,1,2)=c.行业代码
			inner join (select 代码编码 as 省级行政区代码,代码名称 as 省级行政区名称 from 系统_代码 where 代码类型编码=14) c1
			on substring(b.行政区代码,1,2)+''0000''=c1.省级行政区代码
			inner join (select 代码编码 as 市级行政区代码,代码名称 as 市级行政区名称 from 系统_代码 where 代码类型编码=14) c2
			on substring(b.行政区代码,1,4)+''00''=c2.市级行政区代码
			inner join 业务_废气监测点信息 as e
			on a.监测年 = e.监测年 and a.企业代码 = e.企业代码 and a.监测点代码 = e.监测点代码 and a.企业类型=e.企业类型
		)a 
		where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@temppolutionCode+@tempjointype+@temppriparm+'
		group by 监测年,季度,次数,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,监测点代码
	)
	select 行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,sum(监测点数量) as 监测点数量,cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率
	from (
			select 行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,count(监测点代码) as 监测点数量,avg(监测点达标率) as 监测点达标率
			from 
			(	/*计算某企业某监测点达标率*/
				select 监测年,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率
				from cte group by 监测年,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,监测点代码
			) a group by 行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称 
	) a group by 行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称'
end
exec(@sSQL)
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetletAccount_Calculate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetletAccount_Calculate]
GO

-- =============================================
-- exec [GetletAccount] 1,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetletAccount] 2,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetletAccount] 3,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetletAccount] 4,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetletAccount] 5,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetletAccount] 6,'2011','1','','','','0','0','','1','1','1',''
-- Author:		<Author,huabo,>
-- Create date: <Create Date,2011-10-25,>
-- Description:	<Description,废水排放口达标率计算>
-- =============================================
CREATE proc [dbo].[GetletAccount_Calculate]
(
	@type int ,
	@year varchar(4),
	@quarter varchar(20),
	@citycode varchar(max),
	@tradecode varchar(max),
	@whatercode varchar(max),
	@controltype varchar(50),
	@polutioncode varchar(max),
	@controltypegas varchar(50),
	@enterprisetype varchar(5),
	@joinType varchar(10),
	@pjType varchar(10),
	@timeWhere varchar(100)
)
as
--变量设置开始
declare @tempCity varchar(max)
declare @tempTrade varchar(max)
declare @tempWhater varchar(max)
declare @tempControlType varchar(50)
declare @tempControlTypeGas varchar(50)
declare @temppolutionCode varchar(max)
declare @tempenterprisetype varchar(100)
declare @tempjointype varchar(100)
declare @temppjtype varchar(100)
declare @temptimewhere varchar(100)

set @tempCity=''
set @tempTrade=''
set @tempWhater=''
set @tempControlType=''
set @temppolutionCode=''
set @tempControlTypeGas=''
set @tempenterprisetype='1'
set @tempjointype=''
set @temppjtype='isnull(污染物浓度是否达标,0)'

--受纳水体
if @whatercode!=''
begin
	set @whatercode=''''+replace(@whatercode,',',''',''')+''''
	set @tempWhater=' and (substring(受纳水体代码,1,2) in ('+@whatercode+') or 受纳水体代码 in('+@whatercode+')) '
end
--行政区
if @citycode!=''
begin
	set @tempCity=@citycode
end
--行业
if @tradecode!=''
begin
set @tempTrade=' and 行业代码 in('+@tradecode+')  '
end
--控制级别
if @controltype!='0'
begin
set @tempControlType=' and 污染源控制级别代码 in ('+@controltype+')'
end
--控制级别气
if @controltypegas!='0'
begin
set @tempControlTypeGas=' and 污染源控制级别代码气 in ('+@controltypegas+')'
end
--监测项目
if @polutioncode!=''
begin
set @temppolutionCode=' and 监测项目编号 in ('+@polutioncode+')'
end
--企业类型
if @enterprisetype<>''
begin
	set @tempenterprisetype=@enterprisetype
end
--如果评价类型为“实测浓度”
if @pjType='1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--如果评价类型为“基准浓度”
else if @pjType='2'
begin
	set @temppjtype='基准浓度是否达标'
	if @joinType='1'
	begin
		set @tempjointype=' and 是否参与基准浓度=1 '
	end
	else
    begin
        set @tempjointype=' and 是否参与基准浓度 is not null ' --是否参与基准浓度is null的数据是无效的，需要过滤掉，类似于没有标准值的监测项目对于达标计算而言是无意义的。
    end
end
--如果企业类型不是污染源
if @enterprisetype<>'1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--时间条件
if @timeWhere is not null and @timeWhere<>''
begin
	set @temptimewhere=@timeWhere
end
else
begin
	set @temptimewhere=' and 监测年='+@year+' and 季度 in ('+@quarter+') '
end
-----主体部分开始

declare @sSQL nvarchar (MAX)
BEGIN

if @type=1  --按行业查询
begin		
	set @sSQL='
	with cte as
	(
		/*计算某季度、某次，监测点是否达标*/
		select 监测年,季度,次数,行业,行业编码,详细名称,市,监测点代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
		from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,c.行业,c.行业代码 as 行业编码,b.行业代码,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,
			a.污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,d.受纳水体代码,市,a.排放上限,
			b.行政区代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期  
			from 业务_废水监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
			inner join (select rtrim(代码编码) as 行业代码,代码名称 as 行业 from 系统_代码 where 代码类型编码=15) c
			on substring(b.行业代码,1,2)=c.行业代码
			inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) c1
			on substring(b.行政区代码,1,4)+''00''=c1.市代码
			inner join 业务_废水排口信息 d on a.监测年=d.监测年 and a.企业代码=d.企业代码 
			and a.监测点代码=d.监测点代码 and a.企业类型=d.企业类型
			where d.是否参与评价=1
		)a 
		where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@tempWhater+@temppolutionCode+@tempjointype+'
		group by 监测年,季度,次数,行业,行业编码,详细名称,市,监测点代码
	)
	
	select 行业 as 代码名称,行业编码 as 代码编码,count(详细名称) as 企业数量,sum(监测点数量) as 监测点数量,
	cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率
	from 
	(	/*计算某企业，监测点数量，及监测点达标率*/
		select 监测年,行业,行业编码,详细名称,市,count(监测点代码) as 监测点数量,avg(监测点达标率) as 监测点达标率
		from 
		(	/*计算某企业某监测点达标率*/
			select 监测年,行业,行业编码,详细名称,市,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率
			from cte group by 监测年,行业,行业编码,详细名称,市,监测点代码
		) a group by 监测年,行业,行业编码,详细名称,市
	) a group by 行业,行业编码
	union
	select ''全部'' 代码名称,''000000'' 代码编码,count(详细名称) as 企业数量,sum(监测点数量) as 监测点数量,
	cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率
	from 
	(	/*计算某企业，监测点数量，及监测点达标率*/
		select 监测年,行业,行业编码,详细名称,市,count(监测点代码) as 监测点数量,avg(监测点达标率) as 监测点达标率
		from 
		(	/*计算某企业某监测点达标率*/
			select 监测年,行业,行业编码,详细名称,市,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率
			from cte group by 监测年,行业,行业编码,详细名称,市,监测点代码
		) a group by 监测年,行业,行业编码,详细名称,市
	) a
	order by 代码编码'
end
if(@type=2)--按地区查询
begin
set @sSQL='
	with cte as
	(
		/*计算某季度、某次，监测点是否达标*/
		select 监测年,季度,次数,省,市,省代码,市代码,详细名称,监测点代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
		from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,c.省,d.市,c.省代码,d.市代码,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,
			a.污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,e.受纳水体代码,
			b.行政区代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,b.行业代码,a.排放上限,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期  
			from 业务_废水监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
			inner join (select rtrim(代码编码) as 省代码,代码名称 as 省 from 系统_代码 where 代码类型编码=14) c
			on substring(b.行政区代码,1,2)+''0000''=c.省代码
			inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) d
			on substring(b.行政区代码,1,4)+''00''=d.市代码
			inner join 业务_废水排口信息 e on a.监测年=e.监测年 and a.企业代码=e.企业代码 
			and a.监测点代码=e.监测点代码 and a.企业类型=e.企业类型
			where e.是否参与评价=1
		)a 
		where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@tempWhater+@temppolutionCode+@tempjointype+'
		group by 监测年,季度,次数,省,市,省代码,市代码,详细名称,监测点代码
	)

	select 省+''/''+市 as 代码名称,省代码+''/''+市代码 as 代码编码,count(详细名称) as 企业数量,sum(监测点数量) as 监测点数量,
	cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率
	from 
	(	/*计算某企业，监测点数量，及监测点达标率*/
		select 监测年,省,市,省代码,市代码,详细名称,count(监测点代码) as 监测点数量,avg(监测点达标率) as 监测点达标率
		from 
		(	/*计算某季度监测点达标率*/
			select 监测年,省,市,省代码,市代码,详细名称,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率
			from cte group by 监测年,省,市,省代码,市代码,详细名称,监测点代码
		) a group by 监测年,省,市,省代码,市代码,详细名称
	) a group by 省,市,省代码,市代码
	union
	select ''全部'' as 代码名称,''000000'' as 代码编码,count(详细名称) as 企业数量,sum(监测点数量) as 监测点数量,
	cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率
	from 
	(	/*计算某企业，监测点数量，及监测点达标率*/
		select 监测年,省,市,省代码,市代码,详细名称,count(监测点代码) as 监测点数量,avg(监测点达标率) as 监测点达标率
		from 
		(	/*计算某季度监测点达标率*/
			select 监测年,省,市,省代码,市代码,详细名称,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率
			from cte group by 监测年,省,市,省代码,市代码,详细名称,监测点代码
		) a group by 监测年,省,市,省代码,市代码,详细名称
	) a
	order by 代码编码'
end
if(@type=4)--按地区查询
begin
	set @sSQL=
	'with cte as 
	(
		/*计算某季度、某次，监测点是否达标*/
		select 监测年,季度,次数,省,省代码,详细名称,市,监测点代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
		from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,c.省,c.省代码,a.企业代码,a.监测点代码,a.监测项目编号,
			a.污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,d.受纳水体代码,
			b.行政区代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,b.行业代码,市,b.详细名称,a.排放上限,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期  
			from 业务_废水监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
			inner join (select rtrim(代码编码) as 省代码,代码名称 as 省 from 系统_代码 where 代码类型编码=14) c
			on substring(b.行政区代码,1,2)+''0000''=c.省代码
			inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) c1
			on substring(b.行政区代码,1,4)+''00''=c1.市代码
			inner join 业务_废水排口信息 d on a.监测年=d.监测年 and a.企业代码=d.企业代码 
			and a.监测点代码=d.监测点代码 and a.企业类型=d.企业类型
			where d.是否参与评价=1
		)a 
		where 排放上限 is not null and 企业类型 ='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@tempWhater+@temppolutionCode+@tempjointype+'
		group by 监测年,季度,次数,省,省代码,详细名称,市,监测点代码
	)
	select 省 as 代码名称,省代码 as 代码编码,count(详细名称) as 企业数量,sum(监测点数量) as 监测点数量,
	cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率
	from 
	(	/*计算某季度，某企业，监测点数量，及监测点达标率*/
		select 监测年,省,省代码,详细名称,市,count(监测点代码) as 监测点数量,avg(监测点达标率) as 监测点达标率
		from 
		(	/*计算某企业某监测点达标率*/
			select 监测年,省,省代码,详细名称,市,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率 
			from cte group by 监测年,省,省代码,详细名称,市,监测点代码
		) a group by 监测年,省,省代码,详细名称,市
	) a group by 省,省代码
	union
	select ''全部'' as 代码名称,''000000'' as 代码编码,count(详细名称) as 企业数量,sum(监测点数量) as 监测点数量,
	cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率
	from 
	(	/*计算某季度，某企业，监测点数量，及监测点达标率*/
		select 监测年,省,省代码,详细名称,市,count(监测点代码) as 监测点数量,avg(监测点达标率) as 监测点达标率
		from 
		(	/*计算某企业某监测点达标率*/
			select 监测年,省,省代码,详细名称,市,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率 
			from cte group by 监测年,省,省代码,详细名称,市,监测点代码
		) a group by 监测年,省,省代码,详细名称,市
	) a
	order by 代码编码'
End
if(@type=3)--按水系查询
begin
	set @sSQL='
	with cte as
	(
		/*计算某季度、某次，监测点是否达标*/
		select 监测年,季度,次数,水系,水系代码,详细名称,市,监测点代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
		from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,d.代码名称 as 水系,d.代码编码 as 水系代码,c.受纳水体代码,
			a.企业代码,a.监测点代码,a.监测项目编号,a.污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,
			b.行政区代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,b.行业代码,b.详细名称,市,a.排放上限,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期  
			from 业务_废水监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码
			inner join 业务_废水排口信息 c on a.监测年=c.监测年 and a.企业代码=c.企业代码 
			and a.监测点代码=c.监测点代码 and a.企业类型=c.企业类型   
			inner join (select 代码编码,代码名称 from 系统_代码 where 代码类型编码=16 
			and (substring(代码编码,3,8)=''00000000'' or len(代码编码)=1)) d
			on substring(c.受纳水体代码,1,2)=substring(d.代码编码,1,2) or c.受纳水体代码=d.代码编码
			inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) c1
			on substring(b.行政区代码,1,4)+''00''=c1.市代码
			where c.是否参与评价=1
		)a 
		where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@tempWhater+@temppolutionCode+@tempjointype+'
		group by 监测年,季度,次数,水系,水系代码,详细名称,市,监测点代码
	)

	select 水系 as 代码名称,水系代码 as 代码编码,count(详细名称) as 企业数量,sum(监测点数量) as 监测点数量,
	cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率
	from 
	(	/*计算某企业，监测点数量，及监测点达标率*/
		select 监测年,水系,水系代码,详细名称,市,count(监测点代码) as 监测点数量,avg(监测点达标率) as 监测点达标率
		from 
		(	/*计算某企业某监测点达标率*/
			select 监测年,水系,水系代码,详细名称,市,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率
			from cte group by 监测年,水系,水系代码,详细名称,市,监测点代码
		) a group by 监测年,水系,水系代码,详细名称,市
	) a group by 水系,水系代码
	union 
	select ''全部'' as 代码名称,''000000'' as 代码编码,count(详细名称) as 企业数量,sum(监测点数量) as 监测点数量,
	cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率
	from 
	(	/*计算某企业，监测点数量，及监测点达标率*/
		select 监测年,水系,水系代码,详细名称,市,count(监测点代码) as 监测点数量,avg(监测点达标率) as 监测点达标率
		from 
		(	/*计算某企业某监测点达标率*/
			select 监测年,水系,水系代码,详细名称,市,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率
			from cte group by 监测年,水系,水系代码,详细名称,市,监测点代码
		) a group by 监测年,水系,水系代码,详细名称,市
	) a
	order by  代码编码'	
end
if(@type=5)--按流域查询
begin
	set @sSQL='
	with cte as 
	(
		/*计算某季度、某次，监测点是否达标*/
		select 监测年,季度,次数,流域,流域代码,详细名称,市,监测点代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
		from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,d.代码名称 as 流域,d.代码编码 as 流域代码,c.受纳水体代码,
			a.企业代码,a.监测点代码,a.监测项目编号,a.污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,
			b.行政区代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,b.行业代码,b.详细名称,市,a.排放上限,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期  
			from 业务_废水监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码
			inner join 业务_废水排口信息 c on a.监测年=c.监测年 and a.企业代码=c.企业代码 
			and a.监测点代码=c.监测点代码 and a.企业类型=c.企业类型   
			inner join (select 代码编码,代码名称 from 系统_代码 where 代码类型编码=20 
			and (substring(代码编码,3,8)=''00000000'' or len(代码编码)=1)) d
			on substring(c.受纳水体代码,1,1)=substring(d.代码编码,1,1) or c.受纳水体代码=d.代码编码
			inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) c1
			on substring(b.行政区代码,1,4)+''00''=c1.市代码
			where c.是否参与评价=1
		)a 
		where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@tempWhater+@temppolutionCode+@tempjointype+'
		group by 监测年,季度,次数,流域,流域代码,详细名称,市,监测点代码
	)
	select 流域 as 代码名称,流域代码 as 代码编码,count(详细名称) as 企业数量,sum(监测点数量) as 监测点数量,
	cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率 
	from 
	(	/*计算某企业，监测点数量，及监测点达标率*/
		select 监测年,流域,流域代码,详细名称,市,count(监测点代码) as 监测点数量,avg(监测点达标率) as 监测点达标率
		from 
		(	/*计算某企业某监测点达标率*/
			select 监测年,流域,流域代码,详细名称,市,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率 
			from cte group by 监测年,流域,流域代码,详细名称,市,监测点代码
		) a group by 监测年,流域,流域代码,详细名称,市
	) a group by 流域,流域代码
	union
	select ''全部'' as 代码名称,''000000'' as 代码编码,count(详细名称) as 企业数量,sum(监测点数量) as 监测点数量,
	cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率 
	from 
	(	/*计算某企业，监测点数量，及监测点达标率*/
		select 监测年,流域,流域代码,详细名称,市,count(监测点代码) as 监测点数量,avg(监测点达标率) as 监测点达标率
		from 
		(	/*计算某企业某监测点达标率*/
			select 监测年,流域,流域代码,详细名称,市,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率 
			from cte group by 监测年,流域,流域代码,详细名称,市,监测点代码
		) a group by 监测年,流域,流域代码,详细名称,市
	) a
	order by 代码编码'
end

exec(@sSQL)

END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCropletAccount_Calculate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCropletAccount_Calculate]
GO

-- =============================================
-- exec [GetCropletAccount_Calculate] 1,'2011','1,2,3,4','','','','0','000000','','0','1','1','1',''
-- exec [GetCropletAccount] 2,'2011','1','','','','0','0','','1','1','1','110100    ',''
-- exec [GetCropletAccount] 3,'2011','1','','','','0','0','','1','1','1','AA00000000',''
-- exec [GetCropletAccount] 4,'2011','1','','','','0','0','','1','1','1','110000    ',''
-- exec [GetCropletAccount] 5,'2011','1','','','','0','0','','1','1','1','A         ',''
-- exec [GetCropletAccount] 6,'2011','1','','','','0','0','','1','1','1','72954',''
-- Author:		<Author,,huabo>
-- Create date: <Create Date,2011-1--25,>
-- Description:	<Description,废水排放口企业达标率计算>
-- =============================================
CREATE proc [dbo].[GetCropletAccount_Calculate]
(
	@type int,
	@year varchar(4),
	@quarter varchar(20),
	@citycode varchar(max),
	@tradecode varchar(max),
	@whatercode varchar(max),
	@controltype varchar(50),
	@priparm varchar(50),
	@polutioncode varchar(max),
	@controltypegas varchar(50),
	@enterprisetype varchar(10),
	@joinType varchar(10),
	@pjType varchar(10),
	
	@timeWhere varchar(100)
)
as
--变量设置开始
declare @tempCity varchar(max)
declare @tempTrade varchar(max)
declare @tempWhater varchar(max)
declare @tempControlType varchar(50)
declare @tempControlTypeGas varchar(50)
declare @temppolutionCode varchar(max)
declare @tempenterprisetype varchar(100)
declare @tempjointype varchar(100)
declare @temppjtype varchar(100)
declare @temppriparm varchar(100)
declare @temptimewhere varchar(100)

set @tempCity=''
set @tempTrade=''
set @tempWhater=''
set @tempControlType=''
set @temppolutionCode=''
set @tempControlTypeGas=''
set @tempenterprisetype='1'
set @tempjointype=''
set @temppjtype='isnull(污染物浓度是否达标,0)'
set @temppriparm=''

--受纳水体
if @whatercode!=''
begin
	set @whatercode=''''+replace(@whatercode,',',''',''')+''''
	set @tempWhater=' and (substring(受纳水体代码,1,2) in ('+@whatercode+') or 受纳水体代码 in('+@whatercode+')) '
end
--行政区
if @citycode!=''
begin
	set @tempCity=@citycode
end
--行业
if @tradecode!=''
begin
set @tempTrade=' and 行业编码 in('+@tradecode+')  '
end
--控制级别
if @controltype!='0'
begin
set @tempControlType=' and 污染源控制级别代码 in ('+@controltype+')'
end
--控制级别气
if @controltypegas!='0'
begin
set @tempControlTypeGas=' and 污染源控制级别代码气 in ('+@controltypegas+')'
end
--监测项目
if @polutioncode!=''
begin
set @temppolutionCode=' and 监测项目编号 in ('+@polutioncode+')'
end
--企业类型
if @enterprisetype<>''
begin
	set @tempenterprisetype=@enterprisetype
end
--如果评价类型为“实测浓度”
if @pjType='1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--如果评价类型为“基准浓度”
else if @pjType='2'
begin
	set @temppjtype='基准浓度是否达标'
	if @joinType='1'
	begin
		set @tempjointype=' and 是否参与基准浓度=1 '
	end
	else
    begin
        set @tempjointype=' and 是否参与基准浓度 is not null ' --是否参与基准浓度is null的数据是无效的，需要过滤掉，类似于没有标准值的监测项目对于达标计算而言是无意义的。
    end
end
--如果企业类型不是污染源
if @enterprisetype<>'1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--过滤主键条件
if @type=1 and @priparm<>'000000'
begin
	set @temppriparm=' and 行业编码='''+@priparm+''' '
end
if @type=2 and @priparm<>'000000'
begin
	set @temppriparm=' and 市级行政区代码='''+@priparm+''' '
end
if @type=4 and @priparm<>'000000'
begin
	set @temppriparm=' and 省级行政区代码='''+@priparm+''' '
end
if @type=3 and @priparm<>'000000'
begin
	set @temppriparm=' and 水系代码='''+@priparm+''' '
end
if @type=5 and @priparm<>'000000'
begin
	set @temppriparm=' and 流域代码='''+@priparm+''' '
end
if @type=6 and @priparm<>'000000'
begin
	set @temppriparm=' and 区域代码='''+@priparm+''' '
end
--时间条件
if @timeWhere is not null and @timeWhere<>''
begin
	set @temptimewhere=@timeWhere
end
else
begin
	set @temptimewhere=' and 监测年='+@year+' and 季度 in ('+@quarter+') '
end

-----主体部分开始
declare @sSQL nvarchar (MAX)
BEGIN
if (@type=1 or @type=2 or @type=3 or @type=4 or @type=5)  --按行业查询
begin		
	set @sSQL='
	with cte as
	(
		/*计算某季度、某次，监测点是否达标*/
		select 监测年,季度,次数,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,监测点代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
		from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,c.行业,c.行业代码 as 行业编码,b.行政区代码,c1.省级行政区名称,c2.市级行政区名称,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,
			a.污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,d.受纳水体代码,水系代码,流域代码,
			b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,c1.省级行政区代码,c2.市级行政区代码,a.排放上限,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期 
			from 业务_废水监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
			inner join (select 代码编码 as 行业代码,代码名称 as 行业 from 系统_代码 where 代码类型编码=15) c
			on substring(b.行业代码,1,2)=c.行业代码
			inner join (select 代码编码 as 省级行政区代码,代码名称 as 省级行政区名称 from 系统_代码 where 代码类型编码=14) c1
			on substring(b.行政区代码,1,2)+''0000''=c1.省级行政区代码
			inner join (select 代码编码 as 市级行政区代码,代码名称 as 市级行政区名称 from 系统_代码 where 代码类型编码=14) c2
			on substring(b.行政区代码,1,4)+''00''=c2.市级行政区代码
			inner join 业务_废水排口信息 d on a.监测年=d.监测年 and a.企业代码=d.企业代码 
			and a.监测点代码=d.监测点代码 and a.企业类型=d.企业类型
			inner join (select 代码编码 as 水系代码,代码名称 from 系统_代码 where 代码类型编码=16 and (substring(代码编码,3,8)=''00000000'' or len(代码编码)=1)) c3
			on substring(d.受纳水体代码,1,2)=substring(c3.水系代码,1,2) or d.受纳水体代码=c3.水系代码
			inner join (select 代码编码 as 流域代码,代码名称 from 系统_代码 where 代码类型编码=20 and (substring(代码编码,3,8)=''00000000'' or len(代码编码)=1)) c4
			on substring(d.受纳水体代码,1,1)=substring(c4.流域代码,1,1) or d.受纳水体代码=c4.流域代码
			where d.是否参与评价=1
		)a 
		where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@tempWhater+@temppolutionCode+@tempjointype+@temppriparm+'
		group by 监测年,季度,次数,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,监测点代码
	)	
	select 行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,sum(监测点数量) as 监测点数量,cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率
	from (
			select 行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,count(监测点代码) as 监测点数量,avg(监测点达标率) as 监测点达标率
			from 
			(	/*计算某企业某监测点达标率*/
				select 监测年,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率
				from cte group by 监测年,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,监测点代码
			) a group by 行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称 
	) a group by 行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称'
end
exec(@sSQL)

END

GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetEquipAccount_Calculate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetEquipAccount_Calculate]
GO

------------------------------------
-- =============================================
-- exec [GetEquipAccount] 1,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetEquipAccount] 2,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetEquipAccount] 4,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetEquipAccount] 6,'2011','1','','','','0','0','','1','1','1',''
-- Author:		<Author,huabo,>
-- Create date: <Create Date,2011-10-25,>
-- Description:	<Description,废气排放设备达标率计算>
-- =============================================
CREATE proc [dbo].[GetEquipAccount_Calculate]
(
	@type int ,
	@year varchar(4),
	@quarter varchar(20),
	@citycode varchar(max),
	@tradecode varchar(max),
	@whatercode varchar(max),
	@controltype varchar(50),
	@controltypegas varchar(50),
	@polutioncode varchar(max),
	@enterprisetype varchar(10),
	@joinType varchar(10),
	@pjType varchar(10),
	@timeWhere varchar(100)
)
as
   --变量设置开始
declare @tempCity varchar(max)
declare @tempTrade varchar(max)
declare @tempControlType varchar(50)
declare @tempControlTypeGas varchar(50)
declare @temppolutionCode varchar(max)
declare @tempenterprisetype varchar(100)
declare @tempjointype varchar(100)
declare @temppjtype varchar(100)		
declare @temptimewhere varchar(100)

set @tempCity=''
set @tempTrade=''
set @tempControlType=''
set @tempControlTypeGas=''
set @temppolutionCode=''
set @tempenterprisetype='1'
set @tempjointype=''
set @temppjtype='isnull(污染物浓度是否达标,0)'

--行政区代码
if @citycode!=''
begin
	set @tempCity=@citycode
end
--行业代码
if @tradecode!=''
begin
	set @tempTrade=' and 行业代码 in('+@tradecode+') '
end
--控制级别
if @controltype!='0'
begin
	set @tempControlType=' and 污染源控制级别代码 in ('+@controltype+') '
end
--气控制级别
if @controltypegas!='0'
begin
	set @tempControlTypeGas=' and 污染源控制级别代码气 in ('+@controltypegas+') '
end
--监测项目
if @polutioncode!=''
begin
	set @temppolutionCode=' and 监测项目编号 in ('+@polutioncode+')'
end
--企业类型
if @enterprisetype<>''
begin
	set @tempenterprisetype=@enterprisetype
end
--如果评价类型为“实测浓度”
if @pjType='1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--如果评价类型为“基准浓度”
else if @pjType='2'
begin
	set @temppjtype='基准浓度是否达标'
	if @joinType='1'
	begin
		set @tempjointype=' and 是否参与基准浓度=1 '
	end
	else
    begin
        set @tempjointype=' and 是否参与基准浓度 is not null ' --是否参与基准浓度is null的数据是无效的，需要过滤掉，类似于没有标准值的监测项目对于达标计算而言是无意义的。
    end
end
--如果企业类型不是污染源
if @enterprisetype<>'1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--时间条件
if @timeWhere is not null and @timeWhere<>''
begin
	set @temptimewhere=@timeWhere
end
else
begin
	set @temptimewhere=' and 监测年='+@year+' and 季度 in ('+@quarter+') '
end

-----主体部分开始
declare @sSQL nvarchar (MAX)
BEGIN
if @type=1  --按行业查询
begin	
	set @sSQL='
	with cte as 
	(
		/*计算某季度、某次，排污设备是否达标*/
		select 监测年,季度,次数,行业,行业编码,详细名称,市,排污设备代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 排污设备是否达标
		from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,c.行业,c.行业代码 as 行业编码,b.行业代码,a.企业代码,d.排污设备代码,a.监测项目编号,
			a.浓度是否达标 as 污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,b.详细名称,市,
			b.行政区代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,a.排放上限,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期   
			from 业务_废气监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
			join (select rtrim(代码编码) as 行业代码,代码名称 as 行业 from 系统_代码 where 代码类型编码=15) c
			on substring(b.行业代码,1,2)=c.行业代码 
			inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) c1
			on substring(b.行政区代码,1,4)+''00''=c1.市代码
			inner join 业务_废气监测点信息 d on a.监测年=d.监测年 and a.企业代码=d.企业代码 
			and a.监测点代码=d.监测点代码  and a.企业类型=d.企业类型
		)a 
		where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@temppolutionCode+@tempjointype+'
		group by 监测年,季度,次数,行业,行业编码,详细名称,市,排污设备代码
	)
	
	select 行业 as 代码名称,行业编码 as 代码编码,count(详细名称) as 企业数量,sum(排污设备数量) as 排污设备数量,cast(avg(排污设备达标率) as decimal(18,3)) as 排污设备达标率
	from 
	(	/*计算某企业排污设备达标率*/
		select 行业,行业编码,详细名称,市,count(排污设备代码) as 排污设备数量,avg(排污设备达标率) as 排污设备达标率
		from
		(
			/*计算某企业某排污设备达标率*/
			select 监测年,行业,行业编码,详细名称,市,排污设备代码,count(排污设备是否达标) as 监测次数,sum(排污设备是否达标) as 达标次数,
			cast(sum(排污设备是否达标) as decimal(18,2))/count(排污设备是否达标) as 排污设备达标率
			from cte group by 监测年,行业,行业编码,详细名称,市,排污设备代码
		) as a group by 行业,行业编码,详细名称,市
	) a group by 行业,行业编码
	union
	select ''全部'' as 代码名称,''000000'' as 代码编码,count(详细名称) as 企业数量,sum(排污设备数量) as 排污设备数量,cast(avg(排污设备达标率) as decimal(18,3)) as 排污设备达标率
	from 
	(	/*计算某企业排污设备达标率*/
		select 行业,行业编码,详细名称,市,count(排污设备代码) as 排污设备数量,avg(排污设备达标率) as 排污设备达标率
		from
		(
			/*计算某企业某排污设备达标率*/
			select 监测年,行业,行业编码,详细名称,市,排污设备代码,count(排污设备是否达标) as 监测次数,sum(排污设备是否达标) as 达标次数,
			cast(sum(排污设备是否达标) as decimal(18,2))/count(排污设备是否达标) as 排污设备达标率
			from cte group by 监测年,行业,行业编码,详细名称,市,排污设备代码
		) as a group by 行业,行业编码,详细名称,市
	) a
order by 代码编码'
end
if(@type=2)--按地区查询
begin
set @sSQL='
	with cte as
	(
		/*计算某季度、某次，排污设备是否达标*/
		select 监测年,季度,次数,省,市,省代码,市代码,详细名称,排污设备代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 排污设备是否达标
		from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,c.省,d.市,c.省代码,d.市代码,a.企业代码,e.排污设备代码,a.监测项目编号,
			a.浓度是否达标 as 污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,
			b.行政区代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,b.行业代码,b.详细名称,a.排放上限,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期   
			from 业务_废气监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
			inner join (select rtrim(代码编码) as 省代码,代码名称 as 省 from 系统_代码 where 代码类型编码=14) c
			on substring(b.行政区代码,1,2)+''0000''=c.省代码
			inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) d
			on substring(b.行政区代码,1,4)+''00''=d.市代码
			join 业务_废气监测点信息 e on a.监测年=e.监测年 and a.企业代码=e.企业代码 
			and a.监测点代码=e.监测点代码  and a.企业类型=e.企业类型
		)a 
		where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@temppolutionCode+@tempjointype+'
		group by 监测年,季度,次数,省,市,省代码,市代码,详细名称,市,排污设备代码
	)

	select 省+''/''+市 as 代码名称,省代码+''/''+市代码 as 代码编码,count(详细名称) as 企业数量,sum(排污设备数量) as 排污设备数量,
	cast(avg(排污设备达标率) as decimal(18,3)) as 排污设备达标率
	from 
	(	/*计算某企业排污设备数量，及排污设备平均达标率*/
		select 监测年,省,市,省代码,市代码,详细名称,count(排污设备代码) as 排污设备数量,avg(排污设备达标率) as 排污设备达标率
		from 
		(	/*计算某企业某排污设备达标率*/
			select 监测年,省,市,省代码,市代码,详细名称,排污设备代码,cast(sum(排污设备是否达标) as decimal(18,2))/count(排污设备是否达标) as 排污设备达标率
			from cte group by 监测年,省,市,省代码,市代码,详细名称,排污设备代码
		) a group by 监测年,省,市,省代码,市代码,详细名称,市
	) a group by 省,市,省代码,市代码
	union
	select ''全部'' as 代码名称,''000000'' as 代码编码,count(详细名称) as 企业数量,sum(排污设备数量) as 排污设备数量,
	cast(avg(排污设备达标率) as decimal(18,3)) as 排污设备达标率
	from 
	(	/*计算某企业排污设备数量，及排污设备平均达标率*/
		select 监测年,省,市,省代码,市代码,详细名称,count(排污设备代码) as 排污设备数量,avg(排污设备达标率) as 排污设备达标率
		from 
		(	/*计算某企业某排污设备达标率*/
			select 监测年,省,市,省代码,市代码,详细名称,排污设备代码,cast(sum(排污设备是否达标) as decimal(18,2))/count(排污设备是否达标) as 排污设备达标率
			from cte group by 监测年,省,市,省代码,市代码,详细名称,排污设备代码
		) a group by 监测年,省,市,省代码,市代码,详细名称
	) a
	order by 代码编码'
end
if(@type=4)--按地区查询
begin
set @sSQL='
	with cte as
	(
		/*计算某季度、某次，排污设备是否达标*/
		select 监测年,季度,次数,省,省代码,详细名称,市,排污设备代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 排污设备是否达标
		from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,c.省,c.省代码,a.企业代码,d.排污设备代码,a.监测项目编号,
			a.浓度是否达标 as 污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,
			b.行政区代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,b.行业代码,b.详细名称,市,a.排放上限,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期   
			from 业务_废气监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
			inner join (select rtrim(代码编码) as 省代码,代码名称 as 省 from 系统_代码 where 代码类型编码=14) c
			on substring(b.行政区代码,1,2)+''0000''=c.省代码
			inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) c1
			on substring(b.行政区代码,1,4)+''00''=c1.市代码
			inner join 业务_废气监测点信息 d on a.监测年=d.监测年 and a.企业代码=d.企业代码 
			and a.监测点代码=d.监测点代码  and a.企业类型=d.企业类型
		)a 
		where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@temppolutionCode+@tempjointype+'
		group by 监测年,季度,次数,省,省代码,详细名称,市,排污设备代码
	)

	select 省 as 代码名称,省代码 as 代码编码,count(详细名称) as 企业数量,sum(排污设备数量) as 排污设备数量,
	cast(avg(排污设备达标率) as decimal(18,3)) as 排污设备达标率
	from 
	(	/*计算某企业排污设备数量，及排污设备平均达标率*/
		select 监测年,省,省代码,详细名称,市,avg(排污设备达标率) as 排污设备达标率,count(排污设备代码) as 排污设备数量
		from 
		(	/*计算某企业某排污设备达标率*/
			select 监测年,省,省代码,详细名称,市,排污设备代码,cast(sum(排污设备是否达标) as decimal(18,2))/count(排污设备是否达标) as 排污设备达标率
			from cte group by 监测年,省,省代码,详细名称,市,排污设备代码
		) a group by 监测年,省,省代码,详细名称,市
	) a group by 省,省代码
	union
	select ''全部'' as 代码名称,''000000'' as 代码编码,count(详细名称) as 企业数量,sum(排污设备数量) as 排污设备数量,
	cast(avg(排污设备达标率) as decimal(18,3)) as 排污设备达标率
	from 
	(	/*计算某企业排污设备数量，及排污设备平均达标率*/
		select 监测年,省,省代码,详细名称,市,avg(排污设备达标率) as 排污设备达标率,count(排污设备代码) as 排污设备数量
		from 
		(	/*计算某企业某排污设备达标率*/
			select 监测年,省,省代码,详细名称,市,排污设备代码,cast(sum(排污设备是否达标) as decimal(18,2))/count(排污设备是否达标) as 排污设备达标率
			from cte group by 监测年,省,省代码,详细名称,市,排污设备代码
		) a group by 监测年,省,省代码,详细名称,市
	) a
	order by 代码编码'
end

exec(@sSQL)
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCorpEquipAccount_Calculate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCorpEquipAccount_Calculate]
GO

-- =============================================
-- exec [GetCorpEquipAccount] 1,'2011','1','','','','0','0','','1','1','1','06        ',''
-- exec [GetCorpEquipAccount] 2,'2011','1','','','','0','0','','1','1','1','110100    ',''
-- exec [GetCorpEquipAccount] 4,'2011','1','','','','0','0','','1','1','1','120000    ',''
-- exec [GetCorpEquipAccount] 6,'2011','1','','','','0','0','','1','1','1','72954',''
-- Author:		<Author,huabo,>
-- Create date: <Create Date,2011-10-25,>
-- Description:	<Description,废气排放设备达标率计算>
-- =============================================
Create proc [dbo].[GetCorpEquipAccount_Calculate]
(
	@type int,
	@year varchar(4),
	@quarter varchar(20),
	@citycode varchar(max),
	@tradecode varchar(max),
	@whatercode varchar(max),
	@controltype varchar(50),
	@controltypegas varchar(50),
	@polutioncode varchar(max),
	@enterprisetype varchar(10),
	@joinType varchar(10),
	@pjType varchar(10),
	@priparm varchar(50),
	@timeWhere varchar(100)
)
as
--变量设置开始
declare @tempCity varchar(max)
declare @tempTrade varchar(max)
declare @tempControlType varchar(50)
declare @tempControlTypeGas varchar(50)
declare @temppolutionCode varchar(max)
declare @tempenterprisetype varchar(100)
declare @tempjointype varchar(100)
declare @temppjtype varchar(100)
declare @temppriparm varchar(100)
declare @temptimewhere varchar(100)

set @tempCity=''
set @tempTrade=''
set @temppolutionCode=''
set @tempControlType=''
set @tempControlTypeGas=''
set @tempenterprisetype='1'
set @tempjointype=''
set @temppjtype='isnull(污染物浓度是否达标,0)'
set @temppriparm=''

--行政区代码
if @citycode!=''
begin
	set @tempCity=@citycode
end
--行业代码
if @tradecode!=''
begin
	set @tempTrade=' and 行业编码 in('+@tradecode+') '
end
--控制级别
if @controltype!='0'
begin
	set @tempControlType=' and 污染源控制级别代码 in ('+@controltype+') '
end
--气控制级别
if @controltypegas!='0'
begin
	set @tempControlTypeGas=' and 污染源控制级别代码气 in ('+@controltypegas+') '
end
--监测项目
if @polutioncode!=''
begin
	set @temppolutionCode=' and 监测项目编号 in ('+@polutioncode+')'
end
--企业类型
if @enterprisetype<>''
begin
	set @tempenterprisetype=@enterprisetype
end
--如果评价类型为“实测浓度”
if @pjType='1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--如果评价类型为“基准浓度”
else if @pjType='2'
begin
	set @temppjtype='基准浓度是否达标'
	if @joinType='1'
	begin
		set @tempjointype=' and 是否参与基准浓度=1 '
	end
	else
    begin
        set @tempjointype=' and 是否参与基准浓度 is not null ' --是否参与基准浓度is null的数据是无效的，需要过滤掉，类似于没有标准值的监测项目对于达标计算而言是无意义的。
    end
end
--如果企业类型不是污染源
if @enterprisetype<>'1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end

--过滤主键条件
if @type=1 and @priparm<>'000000'
begin
	set @temppriparm=' and 行业编码='''+@priparm+''' '
end
if @type=2 and @priparm<>'000000'
begin
	set @temppriparm=' and 市级行政区代码='''+@priparm+''' '
end
if @type=4 and @priparm<>'000000'
begin
	set @temppriparm=' and 省级行政区代码='''+@priparm+''' '
end
if @type=6 and @priparm<>'000000'
begin
	set @temppriparm=' and 区域代码='''+@priparm+''' '
end
--时间条件
if @timeWhere is not null and @timeWhere<>''
begin
	set @temptimewhere=@timeWhere
end
else
begin
	set @temptimewhere=' and 监测年='+@year+' and 季度 in ('+@quarter+') '
end
                   
-----主体部分开始
declare @sSQL nvarchar (MAX)
BEGIN

if (@type=1 or @type=2 or @type=4)  --按行业查询
begin		
	set @sSQL='
	with cte as
	(
		/*计算某季度、某次，排污设备是否达标*/
		select 监测年,季度,次数,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,排污设备代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 排污设备是否达标
		from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,c.行业,c.行业代码 as 行业编码,b.行政区代码,c1.省级行政区名称,c2.市级行政区名称,b.详细名称,a.企业代码,e.排污设备代码,a.监测项目编号,
			a.浓度是否达标 as 污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,c1.省级行政区代码,c2.市级行政区代码,
			b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,a.排放上限,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期 
			from 业务_废气监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
			inner join (select 代码编码 as 行业代码,代码名称 as 行业 from 系统_代码 where 代码类型编码=15) c
			on substring(b.行业代码,1,2)=c.行业代码
			inner join (select 代码编码 as 省级行政区代码,代码名称 as 省级行政区名称 from 系统_代码 where 代码类型编码=14) c1
			on substring(b.行政区代码,1,2)+''0000''=c1.省级行政区代码
			inner join (select 代码编码 as 市级行政区代码,代码名称 as 市级行政区名称 from 系统_代码 where 代码类型编码=14) c2
			on substring(b.行政区代码,1,4)+''00''=c2.市级行政区代码
			inner join 业务_废气监测点信息 as e
			on a.监测年 = e.监测年 and a.企业代码 = e.企业代码 and a.监测点代码 = e.监测点代码 and a.企业类型=e.企业类型
		)a 
		where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@temppolutionCode+@tempjointype+@temppriparm+'
		group by 监测年,季度,次数,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,排污设备代码
	)
	select 监测年 as 年度,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,count(排污设备代码) as 排污设备数量,
	cast(avg(排污设备达标率) as decimal(18,3)) as 排污设备达标率
	from 
	(	/*计算某企业某排污设备达标率*/
		select 监测年,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,排污设备代码,cast(sum(排污设备是否达标) as decimal(18,2))/count(排污设备是否达标) as 排污设备达标率
		from cte group by 监测年,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,排污设备代码
	) a group by 监测年,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称 
	order by 年度,a.详细名称'
end
exec(@sSQL)
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetNoOrgAccount_Calculate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetNoOrgAccount_Calculate]
GO

--exec [GetNoOrgAccount_Calculate] 1,'2011','4','','','','0','0','','1','1','1',''
--废气无组织达标率计算
CREATE proc [dbo].[GetNoOrgAccount_Calculate]
(
	@type int,
	@year varchar(4),
	@quarter varchar(20),
	@citycode varchar(max),
	@tradecode varchar(max),
	@whatercode varchar(max),
	@controltype varchar(50),
	@controltypegas varchar(50),
	@polutioncode varchar(max),
	@enterprisetype varchar(10),
	@joinType varchar(10),
	@pjType varchar(10),
	@timeWhere varchar(100)
)
as
--变量设置开始
declare @tempCity varchar(max)
declare @tempTrade varchar(max)
declare @tempControlType varchar(50)
declare @tempControlTypeGas varchar(50)
declare @temppolutionCode varchar(max)
declare @tempenterprisetype varchar(100)
declare @tempjointype varchar(100)
declare @temppjtype varchar(100)
declare @temptimewhere varchar(100)

set @tempCity=''
set @tempTrade=''
set @temppolutionCode=''
set @tempControlType=''
set @tempControlTypeGas=''
set @tempenterprisetype='1'
set @tempjointype=''
set @temppjtype='isnull(污染物浓度是否达标,0)'

--行政区代码
if @citycode!=''
begin
	set @tempCity=@citycode
end
--行业代码
if @tradecode!=''
begin
	set @tempTrade=' and 行业代码 in('+@tradecode+') '
end
--控制级别
if @controltype!='0'
begin
	set @tempControlType=' and 污染源控制级别代码 in ('+@controltype+') '
end
--气控制级别
if @controltypegas!='0'
begin
	set @tempControlTypeGas=' and 污染源控制级别代码气 in ('+@controltypegas+') '
end
--监测项目
if @polutioncode!=''
begin
	set @temppolutionCode=' and 监测项目编号 in ('+@polutioncode+')'
end
--企业类型
if @enterprisetype<>''
begin
	set @tempenterprisetype=@enterprisetype
end
--如果评价类型为“实测浓度”
if @pjType='1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--如果评价类型为“基准浓度”
else if @pjType='2'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--如果企业类型不是污染源
if @enterprisetype<>'1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--时间条件
if @timeWhere is not null and @timeWhere<>''
begin
	set @temptimewhere=@timeWhere
end
else
begin
	set @temptimewhere=' and 监测年='+@year+' and 季度 in ('+@quarter+') '
end
                      
-----主体部分开始
declare @sSQL nvarchar (MAX)
BEGIN
if @type=1  --按行业查询
begin		
	set @sSQL='
	with cte as 
	(
		/*计算某季度、某次，监测点是否达标*/
		select 监测年,季度,次数,行业,行业编码,详细名称,市,监测点代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
		from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,c.行业,c.行业代码 as 行业编码,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,
			a.污染物浓度是否达标,a.污染物浓度,b.行政区代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,b.行业代码,市,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期,排放上限  
			from 业务_废气无组织排放监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
			inner join (select rtrim(代码编码) as 行业代码,代码名称 as 行业 from 系统_代码 where 代码类型编码=15) c
			on substring(b.行业代码,1,2)=c.行业代码
			inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) d
			on substring(b.行政区代码,1,4)+''00''=d.市代码
			inner join 业务_无组织排放信息 as e
			on a.监测年 = e.监测年 and a.企业代码 = e.企业代码 and a.监测点代码 = e.监测点代码 and a.企业类型=e.企业类型
		)a 
		where a.排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@temppolutionCode+@tempjointype+'
		group by 监测年,季度,次数,行业,行业编码,详细名称,市,监测点代码
	)
	select 代码名称,代码编码,count(详细名称) as 企业数量,cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率,sum(监测点数量) as 监测点数量
	from (
		/*计算某企业，监测点数量，及监测点达标率*/
		select 监测年 as 年度,行业 as 代码名称,行业编码 as 代码编码,详细名称,市,avg(监测点达标率) as 监测点达标率,count(distinct 监测点代码) as 监测点数量
		from 
		(	/*计算某季度某次监测点达标率*/
			select 监测年,季度,次数,行业,行业编码,详细名称,市,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率 
			from cte group by 监测年,季度,次数,行业,行业编码,详细名称,市,监测点代码
		 ) a group by 监测年,行业,行业编码,详细名称,市 
	) a group by 代码名称,代码编码
	union
	select ''全部''代码名称,''000000'' as 代码编码,count(详细名称) as 企业数量,cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率,sum(监测点数量) as 监测点数量
	from (
		/*计算某企业，监测点数量，及监测点达标率*/
		select 监测年 as 年度,行业 as 代码名称,行业编码 as 代码编码,详细名称,市,avg(监测点达标率) as 监测点达标率,count(distinct 监测点代码) as 监测点数量
		from 
		(	/*计算某季度某次监测点达标率*/
			select 监测年,季度,次数,行业,行业编码,详细名称,市,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率 
			from cte group by 监测年,季度,次数,行业,行业编码,详细名称,市,监测点代码
		 ) a group by 监测年,行业,行业编码,详细名称,市 
	) a
	order by 代码编码,代码名称'
end
if(@type=2)--按地区查询
begin
set @sSQL='
	with cte as
	(
		/*计算某季度、某次，监测点是否达标*/
		select 监测年,季度,次数,省,市,省代码,市代码,详细名称,监测点代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
		from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,c.省,d.市,c.省代码,d.市代码,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,
			a.污染物浓度是否达标,a.污染物浓度,b.行政区代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,b.行业代码,排放上限,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期  
			from 业务_废气无组织排放监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
			inner join (select rtrim(代码编码) as 省代码,代码名称 as 省 from 系统_代码 where 代码类型编码=14) c
			on substring(b.行政区代码,1,2)+''0000''=c.省代码
			inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) d
			on substring(b.行政区代码,1,4)+''00''=d.市代码
			inner join 业务_无组织排放信息 as e
			on a.监测年 = e.监测年 and a.企业代码 = e.企业代码 and a.监测点代码 = e.监测点代码 and a.企业类型=e.企业类型
		)a 
		where a.排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@temppolutionCode+@tempjointype+'
		group by 监测年,季度,次数,省,市,省代码,市代码,详细名称,监测点代码
	)
	select 省+''/''+市 as 代码名称,省代码+''/''+市代码 as 代码编码,count(详细名称) as 企业数量,sum(监测点数量) as 监测点数量,
	cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率
	from 
	(	/*计算某企业，监测点数量，及监测点达标率*/
		select 监测年,省,市,省代码,市代码,详细名称,avg(监测点达标率) as 监测点达标率,count(distinct 监测点代码) as 监测点数量
		from 
		(	/*计算某季度某次监测点达标率*/
			select 监测年,季度,次数,省,市,省代码,市代码,详细名称,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率
			from cte group by 监测年,季度,次数,省,市,省代码,市代码,详细名称,监测点代码
		) a group by 监测年,省,市,省代码,市代码,详细名称
	) a group by 监测年,省,市,省代码,市代码
	union
	select ''全部'' as 代码名称,''000000'' as 代码编码,count(详细名称) as 企业数量,sum(监测点数量) as 监测点数量,
	cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率
	from 
	(	/*计算某企业，监测点数量，及监测点达标率*/
		select 监测年,省,市,省代码,市代码,详细名称,avg(监测点达标率) as 监测点达标率,count(distinct 监测点代码) as 监测点数量
		from 
		(	/*计算某季度某次监测点达标率*/
			select 监测年,季度,次数,省,市,省代码,市代码,详细名称,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率
			from cte group by 监测年,季度,次数,省,市,省代码,市代码,详细名称,监测点代码
		) a group by 监测年,省,市,省代码,市代码,详细名称
	) a
	order by 代码编码'
end
if(@type=4)--按地区查询
begin
set @sSQL='
	with cte as
		(
			/*计算某季度、某次，监测点是否达标*/
			select 监测年,季度,次数,省,省代码,详细名称,市,监测点代码,
			case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
			from 
			(
				select a.企业类型,a.监测年,a.季度,a.次数,c.省,c.省代码,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,市,
				a.污染物浓度是否达标,a.污染物浓度,b.行政区代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,b.行业代码,
				convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期,排放上限  
				from 业务_废气无组织排放监测数据 a join V_工业和固废企业_简单 b
				on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
				inner join (select rtrim(代码编码) as 省代码,代码名称 as 省 from 系统_代码 where 代码类型编码=14) c
				on substring(b.行政区代码,1,2)+''0000''=c.省代码
				inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) d
				on substring(b.行政区代码,1,4)+''00''=d.市代码
				inner join 业务_无组织排放信息 as e
				on a.监测年 = e.监测年 and a.企业代码 = e.企业代码 and a.监测点代码 = e.监测点代码 and a.企业类型=e.企业类型
			)a 
			where a.排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@temppolutionCode+@tempjointype+'
			group by 监测年,季度,次数,省,省代码,详细名称,市,监测点代码
		)
		select 省 as 代码名称,省代码 as 代码编码,count(详细名称) as 企业数量,cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率,sum(监测点数量) as 监测点数量
		from 
		(	/*计算某企业，监测点数量，及监测点达标率*/
			select 监测年,省,省代码,详细名称,市,avg(监测点达标率) as 监测点达标率,count(distinct 监测点代码) as 监测点数量
			from 
			(	/*计算某季度某次监测点达标率*/
				select 监测年,季度,次数,省,省代码,详细名称,市,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率
				from cte group by 监测年,季度,次数,省,省代码,详细名称,市,监测点代码
			) a group by 监测年,省,省代码,详细名称,市
		) a group by 省,省代码 
		union
		select ''全部'' as 代码名称,''000000'' as 代码编码,count(详细名称) as 企业数量,cast(avg(监测点达标率) as decimal(18,3)) as 监测点达标率,sum(监测点数量) as 监测点数量
		from 
		(	/*计算某企业，监测点数量，及监测点达标率*/
			select 监测年,省,省代码,详细名称,市,avg(监测点达标率) as 监测点达标率,count(distinct 监测点代码) as 监测点数量
			from 
			(	/*计算某季度监测点达标率*/
				select 监测年,季度,次数,省,省代码,详细名称,市,监测点代码,cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as 监测点达标率
				from cte group by 监测年,季度,次数,省,省代码,详细名称,市,监测点代码
			) a group by 监测年,省,省代码,详细名称,市
		) a
		order by 代码编码'
end

exec(@sSQL)
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCropNoOrgAccount_Calculate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCropNoOrgAccount_Calculate]
GO
--废气无组织企业达标率计算
CREATE proc [dbo].[GetCropNoOrgAccount_Calculate]
(
	@type int,
	@year varchar(4),
	@quarter varchar(20),
	@citycode varchar(max),
	@tradecode varchar(max),
	@whatercode varchar(max),
	@controltype varchar(50),
	@controltypegas varchar(50),
	@polutioncode varchar(max),
	@enterprisetype varchar(10),
	@joinType varchar(10),
	@pjType varchar(10),
	@priparm varchar(50),
	@timeWhere varchar(100)
)
as
--变量设置开始
declare @tempCity varchar(max)
declare @tempTrade varchar(max)
declare @tempControlType varchar(50)
declare @tempControlTypeGas varchar(50)
declare @temppolutionCode varchar(max)
declare @tempenterprisetype varchar(100)
declare @tempjointype varchar(100)
declare @temppjtype varchar(100)
declare @temppriparm varchar(100)
declare @temptimewhere varchar(100)

set @tempCity=''
set @tempTrade=''
set @temppolutionCode=''
set @tempControlType=''
set @tempControlTypeGas=''
set @tempenterprisetype='1'
set @tempjointype=''
set @temppjtype='isnull(污染物浓度是否达标,0)'
set @temppriparm=''

--行政区代码
if @citycode!=''
begin
	set @tempCity=@citycode
end
--行业代码
if @tradecode!=''
begin
	set @tempTrade=' and 行业编码 in('+@tradecode+') '
end
--控制级别
if @controltype!='0'
begin
	set @tempControlType=' and 污染源控制级别代码 in ('+@controltype+') '
end
--气控制级别
if @controltypegas!='0'
begin
	set @tempControlTypeGas=' and 污染源控制级别代码气 in ('+@controltypegas+') '
end
--监测项目
if @polutioncode!=''
begin
	set @temppolutionCode=' and 监测项目编号 in ('+@polutioncode+')'
end
--企业类型
if @enterprisetype<>''
begin
	set @tempenterprisetype=@enterprisetype
end
--如果评价类型为“实测浓度”
if @pjType='1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--如果评价类型为“基准浓度”
else if @pjType='2'
begin
	set @temppjtype='基准浓度是否达标'
	if @joinType='1'
	begin
		set @tempjointype=' and 是否参与基准浓度=1 '
	end
	else
    begin
        set @tempjointype=' and 是否参与基准浓度 is not null ' --是否参与基准浓度is null的数据是无效的，需要过滤掉，类似于没有标准值的监测项目对于达标计算而言是无意义的。
    end
end
--如果企业类型不是污染源
if @enterprisetype<>'1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end

--过滤主键条件
if @type=1 and @priparm<>'000000'
begin
	set @temppriparm=' and 行业编码='''+@priparm+''' '
end
if @type=2 and @priparm<>'000000'
begin
	set @temppriparm=' and 市级行政区代码='''+@priparm+''' '
end
if @type=4 and @priparm<>'000000'
begin
	set @temppriparm=' and 省级行政区代码='''+@priparm+''' '
end
if @type=6 and @priparm<>'000000'
begin
	set @temppriparm=' and 区域代码='''+@priparm+''' '
end
--时间条件
if @timeWhere is not null and @timeWhere<>''
begin
	set @temptimewhere=@timeWhere
end
else
begin
	set @temptimewhere=' and 监测年='+@year+' and 季度 in ('+@quarter+') '
end
                   
-----主体部分开始
declare @sSQL nvarchar (MAX)
BEGIN

if (@type=1 or @type=2 or @type=4) 
begin		
	set @sSQL=
	'with cte as
	(
		/*计算某季度、某次，监测点是否达标*/
		select 监测年,季度,次数,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,监测点代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
		from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,c.行业,c.行业代码 as 行业编码,b.行政区代码,c1.省级行政区名称,c2.市级行政区名称,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,
			a.污染物浓度是否达标,a.污染物浓度,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,b.行业代码,c1.省级行政区代码,c2.市级行政区代码,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期,a.排放上限
			from 业务_废气无组织排放监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
			inner join (select 代码编码 as 行业代码,代码名称 as 行业 from 系统_代码 where 代码类型编码=15) c
			on substring(b.行业代码,1,2)=c.行业代码
			inner join (select 代码编码 as 省级行政区代码,代码名称 as 省级行政区名称 from 系统_代码 where 代码类型编码=14) c1
			on substring(b.行政区代码,1,2)+''0000''=c1.省级行政区代码
			inner join (select 代码编码 as 市级行政区代码,代码名称 as 市级行政区名称 from 系统_代码 where 代码类型编码=14) c2
			on substring(b.行政区代码,1,4)+''00''=c2.市级行政区代码
			inner join 业务_无组织排放信息 as e
			on a.监测年 = e.监测年 and a.企业代码 = e.企业代码 and a.监测点代码 = e.监测点代码 and a.企业类型=e.企业类型
		)a 
		where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@temppolutionCode+@tempjointype+@temppriparm+'
		group by 监测年,季度,次数,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,监测点代码
	)
	select 监测年,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,count(监测点代码) as 监测点数量,count(监测点是否达标) as 监测次数,
	sum(监测点是否达标) as 达标次数,cast(cast(sum(监测点是否达标) as decimal(18,2))/count(监测点是否达标) as decimal(18,3)) as 监测点达标率 
	from cte group by 监测年,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称
	order by 监测年,详细名称'
end
exec(@sSQL)
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetWhaterSourceAccount_Calculate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetWhaterSourceAccount_Calculate]
GO

-- =============================================
-- exec [GetWhaterSourceAccount] 1,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetWhaterSourceAccount] 2,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetWhaterSourceAccount] 3,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetWhaterSourceAccount] 4,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetWhaterSourceAccount] 5,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetWhaterSourceAccount] 6,'2011','1','','','','0','0','','1','1','1',''
-- Author:		<Author,huabo,>
-- Create date: <Create Date,2011-10-25,>
-- Description:	<Description,废水污染源达标率计算>
-- =============================================
CREATE proc [dbo].[GetWhaterSourceAccount_Calculate]
(
	@type int,
	@year varchar(4),
	@quarter varchar(20),
	@citycode varchar(max),
	@tradecode varchar(max),
	@whatercode varchar(max),
	@controltype varchar(50),
	@controltypegas varchar(50),
	@polutioncode varchar(max),
	@enterprisetype varchar(5),
	@joinType varchar(10),
	@pjType varchar(10),
	@timeWhere varchar(100)
)
as
--变量设置开始
declare @tempCity varchar(max)
declare @tempTrade varchar(max)
declare @tempWhater varchar(max)
declare @tempControlType varchar(50)
declare @tempControlTypeGas varchar(50)
declare @temppolutionCode varchar(max)
declare @tempenterprisetype varchar(100)
declare @tempjointype varchar(100)
declare @temppjtype varchar(100)
declare @temptimewhere varchar(100)

set @tempCity=''
set @tempTrade=''
set @tempWhater=''
set @tempControlType=''
set @temppolutionCode=''
set @tempControlTypeGas=''
set @tempenterprisetype='1'
set @tempjointype=''
set @temppjtype='isnull(污染物浓度是否达标,0)'

--受纳水体
if @whatercode!=''
begin
	set @whatercode=''''+replace(@whatercode,',',''',''')+''''
	set @tempWhater=' and (substring(受纳水体代码,1,2) in ('+@whatercode+') or 受纳水体代码 in('+@whatercode+')) '
end
--行政区
if @citycode!=''
begin
	set @tempCity=@citycode
end
--行业
if @tradecode!=''
begin
set @tempTrade=' and 行业代码 in('+@tradecode+')  '
end
--控制级别
if @controltype!='0'
begin
set @tempControlType=' and 污染源控制级别代码 in ('+@controltype+')'
end
--控制级别气
if @controltypegas!='0'
begin
set @tempControlTypeGas=' and 污染源控制级别代码气 in ('+@controltypegas+')'
end
--监测项目
if @polutioncode!=''
begin
set @temppolutionCode=' and 监测项目编号 in ('+@polutioncode+')'
end
--企业类型
if @enterprisetype<>''
begin
	set @tempenterprisetype=@enterprisetype
end
--如果评价类型为“实测浓度”
if @pjType='1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--如果评价类型为“基准浓度”
else if @pjType='2'
begin
	set @temppjtype='基准浓度是否达标'
	if @joinType='1'
	begin
		set @tempjointype=' and 是否参与基准浓度=1 '
	end
	else
    begin
        set @tempjointype=' and 是否参与基准浓度 is not null ' --是否参与基准浓度is null的数据是无效的，需要过滤掉，类似于没有标准值的监测项目对于达标计算而言是无意义的。
    end
end
--如果企业类型不是污染源
if @enterprisetype<>'1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--时间条件
if @timeWhere is not null and @timeWhere<>''
begin
	set @temptimewhere=@timeWhere
end
else
begin
	set @temptimewhere=' and 监测年='+@year+' and 季度 in ('+@quarter+') '
end

-----主体部分开始
declare @sSQL nvarchar (MAX)
BEGIN
if object_id('tempdb..##temp') is not null begin drop table ##temp end
set @sSQL=
	'select 监测年,季度,次数,行业,行业编码,详细名称,监测点代码,省,市,省代码,市代码,水系代码,水系,流域代码,流域,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
		into ##temp from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,c.行业,行业编码,b.行业代码,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,
			a.污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,d.受纳水体代码,水系代码,水系,流域代码,流域,
			b.行政区代码,e.省,省代码,f.市,市代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,a.排放上限,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期 
			from 业务_废水监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
			inner join (select rtrim(代码编码) as 行业编码,代码名称 as 行业 from 系统_代码 where 代码类型编码=15) c
			on substring(b.行业代码,1,2)=c.行业编码
			inner join 业务_废水排口信息 d on a.监测年=d.监测年 and a.企业代码=d.企业代码 
			and a.监测点代码=d.监测点代码 and a.企业类型=d.企业类型
			inner join (select rtrim(代码编码) as 省代码,代码名称 as 省 from 系统_代码 where 代码类型编码=14) e
			on substring(b.行政区代码,1,2)+''0000''=e.省代码
			inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) f
			on substring(b.行政区代码,1,4)+''00''=f.市代码
			inner join (select 代码编码 as 水系代码,代码名称 as 水系 from 系统_代码 where 代码类型编码=16 
			and (substring(代码编码,3,8)=''00000000'' or len(代码编码)=1)) c1
			on substring(d.受纳水体代码,1,2)=substring(c1.水系代码,1,2) or d.受纳水体代码=c1.水系代码
			inner join (select 代码编码 as 流域代码,代码名称 as 流域 from 系统_代码 where 代码类型编码=20 
			and (substring(代码编码,3,8)=''00000000'' or len(代码编码)=1)) c2
			on substring(d.受纳水体代码,1,1)=substring(c2.流域代码,1,1) or d.受纳水体代码=c2.流域代码
			where d.是否参与评价=1
		) a 
		where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@tempWhater+@temppolutionCode+@tempjointype+'
		group by 监测年,季度,次数,行业,行业编码,省,市,省代码,市代码,详细名称,监测点代码,水系代码,水系,流域代码,流域'
		exec(@sSQL)
if @type=1  --按行业查询
begin	
	select 代码名称,代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 from
	(
		/*计算某企业达标率*/
		select 监测年,行业 as 代码名称,行业编码 as 代码编码,详细名称,市,sum(污染源是否达标) as 达标次数,count(污染源是否达标) as 监测次数,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from 
		(	/*计算某季度、某次，某企业是否达标*/
			select 监测年,季度,次数,行业,行业编码,详细名称,市,
			case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
			from ##temp group by 监测年,季度,次数,行业,行业编码,详细名称,市
		) a group by 监测年,行业,行业编码,详细名称,市
	) a group by 代码名称,代码编码
	union
	select '全部' as 代码名称,'000000' as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 from
	(
		/*计算某企业达标率*/
		select 监测年,行业 as 代码名称,行业编码 as 代码编码,详细名称,市,sum(污染源是否达标) as 达标次数,count(污染源是否达标) as 监测次数,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from 
		(	/*计算某季度、某次，某企业是否达标*/
			select 监测年,季度,次数,行业,行业编码,详细名称,市,
			case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
			from ##temp group by 监测年,季度,次数,行业,行业编码,详细名称,市
		) a group by 监测年,行业,行业编码,详细名称,市
	) a 
	order by 代码编码
end
if(@type=2)--按地区查询
begin
select 代码名称,代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率
	from( 
		select 省+'/'+市 as 代码名称,省代码+'/'+市代码 as 代码编码,详细名称,达标次数,监测次数,污染源达标率
		from 
		(	/*计算某企业达标率*/
			select 监测年,省,市,省代码,市代码,详细名称,sum(污染源是否达标) as 达标次数,count(污染源是否达标) as 监测次数,
			cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
			from 
			(	/*计算某季度,次数企业是否达标*/
				select 监测年,季度,次数,省,市,省代码,市代码,详细名称,
				case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
				from ##temp group by 监测年,季度,次数,省,市,省代码,市代码,详细名称
			) a group by 监测年,省,市,省代码,市代码,详细名称
		) a
	) a group by 代码名称,代码编码
	union
	select '全部' as 代码名称,'000000' as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率
	from( 
		select 省+'/'+市 as 代码名称,省代码+'/'+市代码 as 代码编码,详细名称,达标次数,监测次数,污染源达标率
		from 
		(	/*计算某企业达标率*/
			select 监测年,省,市,省代码,市代码,详细名称,sum(污染源是否达标) as 达标次数,count(污染源是否达标) as 监测次数,
			cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
			from 
			(	/*计算某季度,次数企业是否达标*/
				select 监测年,季度,次数,省,市,省代码,市代码,详细名称,
				case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
				from ##temp group by 监测年,季度,次数,省,市,省代码,市代码,详细名称
			) a group by 监测年,省,市,省代码,市代码,详细名称
		) a
	) a
	order by 代码编码
end
if(@type=4)--按地区查询
begin
	select 省 as 代码名称,省代码 as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 
	from 
	(	/*计算某企业达标率*/
		select 监测年,省,省代码,详细名称,市,sum(污染源是否达标) as 达标次数,count(污染源是否达标) as 监测次数,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from 
		(	/*计算某季度、某次，企业是否达标*/
			select 监测年,季度,次数,省,省代码,详细名称,市,
			case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
			from ##temp group by 监测年,季度,次数,省,省代码,详细名称,市
		) a group by 监测年,省,省代码,详细名称,市
	) a group by 省,省代码 
	union
	select '全部' as 代码名称,'000000' as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 
	from 
	(	/*计算某企业达标率*/
		select 监测年,省,省代码,详细名称,市,sum(污染源是否达标) as 达标次数,count(污染源是否达标) as 监测次数,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from 
		(	/*计算某季度、某次，企业是否达标*/
			select 监测年,季度,次数,省,省代码,详细名称,市,
			case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
			from ##temp group by 监测年,季度,次数,省,省代码,详细名称,市
		) a group by 监测年,省,省代码,详细名称,市
	) a
	order by 代码编码
end
if(@type=3)--按水系查询
begin
	select 水系 as 代码名称,水系代码 as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 
	from 
	(	/*计算某企业达标率*/
		select 监测年,水系,水系代码,详细名称,市,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from 
		(	/*计算某季度、某次，企业是否达标*/
			select 监测年,季度,次数,水系,水系代码,详细名称,市,
			case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
			from ##temp group by 监测年,季度,次数,水系,水系代码,详细名称,市
		) a group by 监测年,水系,水系代码,详细名称,市
	) a group by 水系,水系代码
	union
	select '全部' as 代码名称,'000000' as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 
	from 
	(	/*计算某企业达标率*/
		select 监测年,水系,水系代码,详细名称,市,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from 
		(	/*计算某季度、某次，企业是否达标*/
			select 监测年,季度,次数,水系,水系代码,详细名称,市,
			case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
			from ##temp group by 监测年,季度,次数,水系,水系代码,详细名称,市
		) a group by 监测年,水系,水系代码,详细名称,市
	) a
	order by 代码编码
end
if(@type=5)--按流域查询
begin
	select 流域 as 代码名称,流域代码 as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 
	from 
	(	/*计算某某企业达标率*/
		select 监测年,流域,流域代码,详细名称,市,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from 
		(	/*计算某季度、某次，企业是否达标*/
			select 监测年,季度,次数,流域,流域代码,详细名称,市,
			case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
			from ##temp group by 监测年,季度,次数,流域,流域代码,详细名称,市
		) a group by 监测年,流域,流域代码,详细名称,市
	) a group by 流域,流域代码
	union
	select '全部' as 代码名称,'000000' as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 
	from 
	(	/*计算某某企业达标率*/
		select 监测年,流域,流域代码,详细名称,市,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from 
		(	/*计算某季度、某次，企业是否达标*/
			select 监测年,季度,次数,流域,流域代码,详细名称,市,
			case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
			from ##temp group by 监测年,季度,次数,流域,流域代码,详细名称,市
		) a group by 监测年,流域,流域代码,详细名称,市
	) a
	order by 代码编码
end

if object_id('tempdb..##temp') is not null begin drop table ##temp end
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCorpWhaterSourceAccount_Calculate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCorpWhaterSourceAccount_Calculate]
GO

-- =============================================
-- exec [GetCorpWhaterSourceAccount] 1,'2011','1','','','','0','0','','1','1','1','03        ',''
-- exec [GetCorpWhaterSourceAccount] 2,'2011','1','','','','0','0','','1','1','1','110100    ',''
-- exec [GetCorpWhaterSourceAccount] 3,'2011','1','','','','0','0','','1','1','1','AA00000000',''
-- exec [GetCorpWhaterSourceAccount] 4,'2011','1','','','','0','0','','1','1','1','110000    ',''
-- exec [GetCorpWhaterSourceAccount] 5,'2011','1','','','','0','0','','1','1','1','A         ',''
-- exec [GetCorpWhaterSourceAccount] 6,'2011','1','','','','0','0','','1','1','1','72954',''
-- Author:		<Author,,huabo>
-- Create date: <Create Date,2011-1--25,>
-- Description:	<Description,废水污染源企业达标率计算>
-- =============================================
CREATE proc [dbo].[GetCorpWhaterSourceAccount_Calculate]
(
	@type int,
	@year varchar(4),
	@quarter varchar(20),
	@citycode varchar(max),
	@tradecode varchar(max),
	@whatercode varchar(max),
	@controltype varchar(50),
	@controltypegas varchar(50),
	@polutioncode varchar(max),
	@enterprisetype varchar(5),
	@joinType varchar(10),
	@pjType varchar(10),
	@priparm varchar(50),
	@timeWhere varchar(100)
)
as
--变量设置开始
declare @tempCity varchar(max)
declare @tempTrade varchar(max)
declare @tempWhater varchar(max)
declare @tempControlType varchar(50)
declare @tempControlTypeGas varchar(50)
declare @temppolutionCode varchar(max)
declare @tempenterprisetype varchar(100)
declare @tempjointype varchar(100)
declare @temppjtype varchar(100)
declare @temppriparm varchar(100)
declare @temptimewhere varchar(100)

set @tempCity=''
set @tempTrade=''
set @tempWhater=''
set @tempControlType=''
set @temppolutionCode=''
set @tempControlTypeGas=''
set @tempenterprisetype='1'
set @tempjointype=''
set @temppjtype='isnull(污染物浓度是否达标,0)'
set @temppriparm=''
--受纳水体
if @whatercode!=''
begin
	set @whatercode=''''+replace(@whatercode,',',''',''')+''''
	set @tempWhater=' and (substring(受纳水体代码,1,2) in ('+@whatercode+') or 受纳水体代码 in('+@whatercode+')) '
end
--行政区
if @citycode!=''
begin
	set @tempCity=@citycode
end
--行业
if @tradecode!=''
begin
set @tempTrade=' and 行业编码 in('+@tradecode+')  '
end
--控制级别
if @controltype!='0'
begin
set @tempControlType=' and 污染源控制级别代码 in ('+@controltype+')'
end
--控制级别气
if @controltypegas!='0'
begin
set @tempControlTypeGas=' and 污染源控制级别代码气 in ('+@controltypegas+')'
end
--监测项目
if @polutioncode!=''
begin
set @temppolutionCode=' and 监测项目编号 in ('+@polutioncode+')'
end
--企业类型
if @enterprisetype<>''
begin
	set @tempenterprisetype=@enterprisetype
end
--如果评价类型为“实测浓度”
if @pjType='1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--如果评价类型为“基准浓度”
else if @pjType='2'
begin
	set @temppjtype='基准浓度是否达标'
	if @joinType='1'
	begin
		set @tempjointype=' and 是否参与基准浓度=1 '
	end
	else
    begin
        set @tempjointype=' and 是否参与基准浓度 is not null ' --是否参与基准浓度is null的数据是无效的，需要过滤掉，类似于没有标准值的监测项目对于达标计算而言是无意义的。
    end
end
--如果企业类型不是污染源
if @enterprisetype<>'1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--过滤主键条件
if @type=1 and @priparm<>'000000'
begin
	set @temppriparm=' and 行业编码='''+@priparm+''' '
end
if @type=2 and @priparm<>'000000'
begin
	set @temppriparm=' and 市级行政区代码='''+@priparm+''' '
end
if @type=4 and @priparm<>'000000'
begin
	set @temppriparm=' and 省级行政区代码='''+@priparm+''' '
end
if @type=3 and @priparm<>'000000'
begin
	set @temppriparm=' and 水系代码='''+@priparm+''' '
end
if @type=5 and @priparm<>'000000'
begin
	set @temppriparm=' and 流域代码='''+@priparm+''' '
end
if @type=6 and @priparm<>'000000'
begin
	set @temppriparm=' and 区域代码='''+@priparm+''' '
end
--时间条件
if @timeWhere is not null and @timeWhere<>''
begin
	set @temptimewhere=@timeWhere
end
else
begin
	set @temptimewhere=' and 监测年='+@year+' and 季度 in ('+@quarter+') '
end

-----主体部分开始
declare @sSQL nvarchar (MAX)
BEGIN
if (@type=1 or @type=2 or @type=3 or @type=4 or @type=5)  --按行业查询
begin		
	set @sSQL=
	   'select 监测年 as 年度,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,sum(污染源是否达标) 达标次数,
		count(污染源是否达标) as 监测次数,cast(cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as decimal(18,3)) as 达标率
		from 
		(	/*计算某季度、某次，企业是否达标*/
			select 监测年,季度,次数,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,
			case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
			from 
			(	/*计算某季度、某次，监测点是否达标*/
				select 监测年,季度,次数,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,监测点代码,
						case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
						from 
						(
							select a.企业类型,a.监测年,a.季度,a.次数,c.行业,c.行业代码 as 行业编码,b.行政区代码,c1.省级行政区名称,c2.市级行政区名称,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,
							a.污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,d.受纳水体代码,水系代码,流域代码,
							b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,c1.省级行政区代码,c2.市级行政区代码,a.排放上限,
							convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期  
							from 业务_废水监测数据 a join V_工业和固废企业_简单 b
							on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
							inner join (select 代码编码 as 行业代码,代码名称 as 行业 from 系统_代码 where 代码类型编码=15) c
							on substring(b.行业代码,1,2)=c.行业代码
							inner join (select 代码编码 as 省级行政区代码,代码名称 as 省级行政区名称 from 系统_代码 where 代码类型编码=14) c1
							on substring(b.行政区代码,1,2)+''0000''=c1.省级行政区代码
							inner join (select 代码编码 as 市级行政区代码,代码名称 as 市级行政区名称 from 系统_代码 where 代码类型编码=14) c2
							on substring(b.行政区代码,1,4)+''00''=c2.市级行政区代码
							join 业务_废水排口信息 d on a.监测年=d.监测年 and a.企业代码=d.企业代码 
							and a.监测点代码=d.监测点代码 and a.企业类型=d.企业类型
							inner join (select 代码编码 as 水系代码,代码名称 from 系统_代码 where 代码类型编码=16 and (substring(代码编码,3,8)=''00000000'' or len(代码编码)=1)) c3
							on substring(d.受纳水体代码,1,2)=substring(c3.水系代码,1,2) or d.受纳水体代码=c3.水系代码
							inner join (select 代码编码 as 流域代码,代码名称 from 系统_代码 where 代码类型编码=20 and (substring(代码编码,3,8)=''00000000'' or len(代码编码)=1)) c4
							on substring(d.受纳水体代码,1,1)=substring(c4.流域代码,1,1) or d.受纳水体代码=c4.流域代码
							where d.是否参与评价=1
						)a 
						where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@tempWhater+@temppolutionCode+@tempjointype+@temppriparm+'
						group by 监测年,季度,次数,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,监测点代码
				) a group by 监测年,季度,次数,行业,行政区代码,省级行政区名称,市级行政区名称,行业编码,详细名称
		) a group by 监测年,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称 
		order by 年度,详细名称'
end
if(@type=6)--按专题区域
begin
set @sSQL='select 年度,代码名称,代码编码,详细名称,企业代码,sum(监测次数) as 监测次数,
sum(达标次数) as 达标次数,avg(达标率) as 达标率
from (
			select 监测年 as 年度,季度,专题区域 as 代码名称,区域代码 as 代码编码,详细名称,企业代码,sum(污染源是否达标) 达标次数,
				count(污染源是否达标) as 监测次数,
			cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as  达标率
			from 
			(	/*计算某季度、某次，企业是否达标*/
				select 监测年,季度,次数,专题区域,区域代码,详细名称,企业代码,
				case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
				from 
				(	/*计算某季度、某次，监测点是否达标*/
					select 监测年,季度,次数,专题区域,区域代码,详细名称,企业代码,监测点代码,
					case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
					from 
					(
						select a.企业类型,a.监测年,a.季度,a.次数,c.代码名称 as 专题区域,c.ID as 区域代码,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,
						a.污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,d.受纳水体代码,
						b.行政区代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,b.行业代码,a.排放上限,
						convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期  
						from 业务_废水监测数据 a join V_工业和固废企业_简单 b
						on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
						join (select a.代码名称,a.ID,b.行政区名称,b.行政区编码 from 系统_代码 a join 系统_专题区域代码 b
						on a.ID=b.区域类型编码 where a.代码类型编码=33) c on b.行政区代码=c.行政区编码
						join 业务_废水排口信息 d on a.监测年=d.监测年 and a.企业代码=d.企业代码 
						and a.监测点代码=d.监测点代码 and a.企业类型=d.企业类型
						where d.是否参与评价=1
					)a 
					where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@tempWhater+@temppolutionCode+@tempjointype+@temppriparm+'
					group by 监测年,季度,次数,专题区域,区域代码,详细名称,企业代码,监测点代码
				) a group by 监测年,季度,次数,专题区域,区域代码,详细名称,企业代码
			) a group by 监测年,季度,专题区域,区域代码,详细名称,企业代码 
	) a group by 年度,代码名称,代码编码,详细名称,企业代码
order by 年度,企业代码'
end
exec(@sSQL)

END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetGasSourceAccount_Calculate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetGasSourceAccount_Calculate]
GO

-- =============================================
-- exec [GetGasSourceAccount_Calculate] 1,'2011','4','','','','0','0','','1','1','1',''
-- exec [GetGasSourceAccount] 2,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetGasSourceAccount] 4,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetGasSourceAccount] 6,'2011','1','','','','0','0','','1','1','1',''
-- Author:		<Author,huabo,>
-- Create date: <Create Date,2011-10-25,>
-- Description:	<Description,废气污染源达标率计算>
-- =============================================
CREATE proc [dbo].[GetGasSourceAccount_Calculate]
(
	@type int ,
	@year varchar(4),
	@quarter varchar(20),
	@citycode varchar(max),
	@tradecode varchar(max),
	@whatercode varchar(max),
	@controltype varchar(50),
	@controltypegas varchar(50),
	@polutioncode varchar(max),
	@enterprisetype varchar(10),
	@joinType varchar(10),
	@pjType varchar(10),
	@timeWhere varchar(100)
)
as
--变量设置开始
declare @tempCity varchar(max)
declare @tempTrade varchar(max)
declare @tempControlType varchar(50)
declare @tempControlTypeGas varchar(50)
declare @temppolutionCode varchar(max)
declare @tempenterprisetype varchar(100)
declare @tempjointype varchar(100)
declare @temppjtype varchar(100)
declare @temptimewhere varchar(100)

set @tempCity=''
set @tempTrade=''
set @temppolutionCode=''
set @tempControlType=''
set @tempControlTypeGas=''
set @tempenterprisetype='1'
set @tempjointype=''
set @temppjtype='isnull(污染物浓度是否达标,0)'

--行政区代码
if @citycode!=''
begin
	set @tempCity=@citycode
end
--行业代码
if @tradecode!=''
begin
	set @tempTrade=' and 行业代码 in('+@tradecode+') '
end
--控制级别
if @controltype!='0'
begin
	set @tempControlType=' and 污染源控制级别代码 in ('+@controltype+') '
end
--气控制级别
if @controltypegas!='0'
begin
	set @tempControlTypeGas=' and 污染源控制级别代码气 in ('+@controltypegas+') '
end
--监测项目
if @polutioncode!=''
begin
	set @temppolutionCode=' and 监测项目编号 in ('+@polutioncode+')'
end
--企业类型
if @enterprisetype<>''
begin
	set @tempenterprisetype=@enterprisetype
end
--如果评价类型为“实测浓度”
if @pjType='1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--如果评价类型为“基准浓度”
else if @pjType='2'
begin
	set @temppjtype='基准浓度是否达标'
	if @joinType='1'
	begin
		set @tempjointype=' and 是否参与基准浓度=1 '
	end
	else
    begin
        set @tempjointype=' and 是否参与基准浓度 is not null ' --是否参与基准浓度is null的数据是无效的，需要过滤掉，类似于没有标准值的监测项目对于达标计算而言是无意义的。
    end
end
--如果企业类型不是污染源
if @enterprisetype<>'1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--时间条件
if @timeWhere is not null and @timeWhere<>''
begin
	set @temptimewhere=@timeWhere
end
else
begin
	set @temptimewhere=' and 监测年='+@year+' and 季度 in ('+@quarter+') '
end
                   
-----主体部分开始
declare @sSQL nvarchar (MAX)
BEGIN
if object_id('tempdb..##temp') is not null begin drop table ##temp end
set @sSQL=
	/*计算某季度、某次，监测点是否达标*/
	'select 监测年,季度,次数,行业,行业编码,详细名称,监测点代码,省,市,省代码,市代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
		into ##temp from 
		(
			select a.企业类型,a.监测年,a.季度,a.次数,d.行业,b.行业代码,行业编码,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,
			a.浓度是否达标 as 污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,a.排放上限,
			b.行政区代码,e.省,省代码,f.市,市代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期  
			from 业务_废气监测数据 a join V_工业和固废企业_简单 b
			on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
			inner join 业务_废气监测点信息 as c
			on a.监测年 = c.监测年 and a.企业代码 = c.企业代码 and a.监测点代码 = c.监测点代码 and a.企业类型=c.企业类型
			inner join (select 代码编码 as 行业编码,代码名称 as 行业 from 系统_代码 where 代码类型编码=15) d
			on substring(b.行业代码,1,2)=d.行业编码
			inner join (select rtrim(代码编码) as 省代码,代码名称 as 省 from 系统_代码 where 代码类型编码=14) e
			on substring(b.行政区代码,1,2)+''0000''=e.省代码
			inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) f
			on substring(b.行政区代码,1,4)+''00''=f.市代码
		) a 
		where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@temppolutionCode+@tempjointype+'
		group by 监测年,季度,次数,行业,行业编码,省,市,省代码,市代码,详细名称,监测点代码'
exec(@sSQL)
if @type=1  --按行业查询
begin	
	select 代码名称,代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 from
	(
		/*计算某企业达标率*/
		select 监测年,行业 as 代码名称,行业编码 as 代码编码,详细名称,市,sum(污染源是否达标) as 达标次数,count(污染源是否达标) as 监测次数,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from 
		(	/*计算某季度、某次，某企业是否达标*/
			select 监测年,季度,次数,行业,行业编码,详细名称,市,
			case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
			from ##temp group by 监测年,季度,次数,行业,行业编码,详细名称,市
		) a group by 监测年,行业,行业编码,详细名称,市
	) a group by 代码名称,代码编码
	union
	select '全部' as 代码名称,'000000' as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 from
	(
		/*计算某企业达标率*/
		select 监测年,行业 as 代码名称,行业编码 as 代码编码,详细名称,市,sum(污染源是否达标) as 达标次数,count(污染源是否达标) as 监测次数,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from 
		(	/*计算某季度、某次，某企业是否达标*/
			select 监测年,季度,次数,行业,行业编码,详细名称,市,
			case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
			from ##temp group by 监测年,季度,次数,行业,行业编码,详细名称,市
		) a group by 监测年,行业,行业编码,详细名称,市
	) a 
	order by 代码编码
end
if(@type=2)--按地区查询
begin
	select 代码名称,代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率
	from( 
		select 省+'/'+市 as 代码名称,省代码+'/'+市代码 as 代码编码,详细名称,达标次数,监测次数,污染源达标率
		from 
		(	/*计算某企业达标率*/
			select 监测年,省,市,省代码,市代码,详细名称,sum(污染源是否达标) as 达标次数,count(污染源是否达标) as 监测次数,
			cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
			from 
			(	/*计算某季度,次数企业是否达标*/
				select 监测年,季度,次数,省,市,省代码,市代码,详细名称,
				case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
				from ##temp group by 监测年,季度,次数,省,市,省代码,市代码,详细名称
			) a group by 监测年,省,市,省代码,市代码,详细名称
		) a
	) a group by 代码名称,代码编码
	union
	select '全部' as 代码名称,'000000' as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率
	from( 
		select 省+'/'+市 as 代码名称,省代码+'/'+市代码 as 代码编码,详细名称,达标次数,监测次数,污染源达标率
		from 
		(	/*计算某企业达标率*/
			select 监测年,省,市,省代码,市代码,详细名称,sum(污染源是否达标) as 达标次数,count(污染源是否达标) as 监测次数,
			cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
			from 
			(	/*计算某季度,次数企业是否达标*/
				select 监测年,季度,次数,省,市,省代码,市代码,详细名称,
				case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
				from ##temp group by 监测年,季度,次数,省,市,省代码,市代码,详细名称
			) a group by 监测年,省,市,省代码,市代码,详细名称
		) a
	) a
	order by 代码编码
end
if(@type=4)--按地区查询
begin
	select 省 as 代码名称,省代码 as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 
	from 
	(	/*计算某企业达标率*/
		select 监测年,省,省代码,详细名称,市,sum(污染源是否达标) as 达标次数,count(污染源是否达标) as 监测次数,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from 
		(	/*计算某季度、某次，企业是否达标*/
			select 监测年,季度,次数,省,省代码,详细名称,市,
			case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
			from ##temp group by 监测年,季度,次数,省,省代码,详细名称,市
		) a group by 监测年,省,省代码,详细名称,市
	) a group by 省,省代码 
	union
	select '全部' as 代码名称,'000000' as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 
	from 
	(	/*计算某企业达标率*/
		select 监测年,省,省代码,详细名称,市,sum(污染源是否达标) as 达标次数,count(污染源是否达标) as 监测次数,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from 
		(	/*计算某季度、某次，企业是否达标*/
			select 监测年,季度,次数,省,省代码,详细名称,市,
			case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
			from ##temp group by 监测年,季度,次数,省,省代码,详细名称,市
		) a group by 监测年,省,省代码,详细名称,市
	) a
	order by 代码编码
end

if object_id('tempdb..##temp') is not null begin drop table ##temp end
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCorpGasSourceAccount_Calculate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCorpGasSourceAccount_Calculate]
GO

-- =============================================
-- exec [GetCorpGasSourceAccount] 1,'2011','1','','','','0','0','','1','1','1','06        ',''
-- exec [GetCorpGasSourceAccount] 2,'2011','1','','','','0','0','','1','1','1','110100    ',''
-- exec [GetCorpGasSourceAccount] 4,'2011','1','','','','0','0','','1','1','1','120000    ',''
-- exec [GetCorpGasSourceAccount] 6,'2011','1','','','','0','0','','1','1','1','72954',''
-- Author:		<Author,huabo,>
-- Create date: <Create Date,2011-10-25,>
-- Description:	<Description,废气污染源企业达标率计算>
-- =============================================
CREATE proc [dbo].[GetCorpGasSourceAccount_Calculate]
(
	@type int ,
	@year varchar(4),
	@quarter varchar(20),
	@citycode varchar(max),
	@tradecode varchar(max),
	@whatercode varchar(max),
	@controltype varchar(50),
	@controltypegas varchar(50),
	@polutioncode varchar(max),
	@enterprisetype varchar(10),
	@joinType varchar(10),
	@pjType varchar(10),
	@priparm varchar(50),
	@timeWhere varchar(100)
)
as
--变量设置开始
declare @tempCity varchar(max)
declare @tempTrade varchar(max)
declare @tempControlType varchar(50)
declare @tempControlTypeGas varchar(50)
declare @temppolutionCode varchar(max)
declare @tempenterprisetype varchar(100)
declare @tempjointype varchar(100)
declare @temppjtype varchar(100)
declare @temppriparm varchar(100)
declare @temptimewhere varchar(100)

set @tempCity=''
set @tempTrade=''
set @temppolutionCode=''
set @tempControlType=''
set @tempControlTypeGas=''
set @tempenterprisetype='1'
set @tempjointype=''
set @temppjtype='isnull(污染物浓度是否达标,0)'
set @temppriparm=''
--行政区代码
if @citycode!=''
begin
	set @tempCity=@citycode
end
--行业代码
if @tradecode!=''
begin
	set @tempTrade=' and 行业编码 in('+@tradecode+') '
end
--控制级别
if @controltype!='0'
begin
	set @tempControlType=' and 污染源控制级别代码 in ('+@controltype+') '
end
--气控制级别
if @controltypegas!='0'
begin
	set @tempControlTypeGas=' and 污染源控制级别代码气 in ('+@controltypegas+') '
end
--监测项目
if @polutioncode!=''
begin
	set @temppolutionCode=' and 监测项目编号 in ('+@polutioncode+')'
end
--企业类型
if @enterprisetype<>''
begin
	set @tempenterprisetype=@enterprisetype
end
--如果评价类型为“实测浓度”
if @pjType='1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--如果评价类型为“基准浓度”
else if @pjType='2'
begin
	set @temppjtype='基准浓度是否达标'
	if @joinType='1'
	begin
		set @tempjointype=' and 是否参与基准浓度=1 '
	end
	else
    begin
        set @tempjointype=' and 是否参与基准浓度 is not null ' --是否参与基准浓度is null的数据是无效的，需要过滤掉，类似于没有标准值的监测项目对于达标计算而言是无意义的。
    end
end
--如果企业类型不是污染源
if @enterprisetype<>'1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end

--过滤主键条件
if @type=1 and @priparm<>'000000'
begin
	set @temppriparm=' and 行业编码='''+@priparm+''' '
end
if @type=2 and @priparm<>'000000'
begin
	set @temppriparm=' and 市级行政区代码='''+@priparm+''' '
end
if @type=4 and @priparm<>'000000'
begin
	set @temppriparm=' and 省级行政区代码='''+@priparm+''' '
end
if @type=6 and @priparm<>'000000'
begin
	set @temppriparm=' and 区域代码='''+@priparm+''' '
end
--时间条件
if @timeWhere is not null and @timeWhere<>''
begin
	set @temptimewhere=@timeWhere
end
else
begin
	set @temptimewhere=' and 监测年='+@year+' and 季度 in ('+@quarter+') '
end
                   
-----主体部分开始
declare @sSQL nvarchar (MAX)
BEGIN
if (@type=1 or @type=2 or @type=4)  --按行业、地区查询
begin		
	set @sSQL=
	   'select 监测年 as 年度,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,sum(污染源是否达标) 达标次数,
		count(污染源是否达标) as 监测次数,cast(cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as decimal(18,3)) as 达标率
		from 
		(	/*计算某季度、某次，某企业是否达标*/
			select 监测年,季度,次数,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,
			case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
			from 
			(	/*计算某季度、某次，监测点是否达标*/
				select 监测年,季度,次数,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,监测点代码,
					case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
					from 
					(
						select a.企业类型,a.监测年,a.季度,a.次数,c.行业,c.行业代码 as 行业编码,b.行政区代码,c1.省级行政区名称,c2.市级行政区名称,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,
						a.浓度是否达标 as 污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,
						b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,c1.省级行政区代码,c2.市级行政区代码,a.排放上限,
						convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期 
						from 业务_废气监测数据 a join V_工业和固废企业_简单 b
						on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
						inner join (select 代码编码 as 行业代码,代码名称 as 行业 from 系统_代码 where 代码类型编码=15) c
						on substring(b.行业代码,1,2)=c.行业代码
						inner join (select 代码编码 as 省级行政区代码,代码名称 as 省级行政区名称 from 系统_代码 where 代码类型编码=14) c1
						on substring(b.行政区代码,1,2)+''0000''=c1.省级行政区代码
						inner join (select 代码编码 as 市级行政区代码,代码名称 as 市级行政区名称 from 系统_代码 where 代码类型编码=14) c2
						on substring(b.行政区代码,1,4)+''00''=c2.市级行政区代码
						inner join 业务_废气监测点信息 as e
						on a.监测年 = e.监测年 and a.企业代码 = e.企业代码 and a.监测点代码 = e.监测点代码 and a.企业类型=e.企业类型
					)a 
					where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@temppolutionCode+@tempjointype+@temppriparm+'
					group by 监测年,季度,次数,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称,监测点代码
			) a group by 监测年,季度,次数,行业,行政区代码,省级行政区名称,市级行政区名称,行业编码,详细名称
		) a group by 监测年,行业,行业编码,行政区代码,省级行政区名称,市级行政区名称,详细名称 
		order by 年度,详细名称'
end
if(@type=6)--按专题区域查询
begin
set @sSQL='select 年度,代码名称,代码编码,详细名称,企业代码,sum(监测次数) as 监测次数,
sum(达标次数) as 达标次数,avg(达标率) as 达标率
from (
			select 监测年 as 年度,季度,专题区域 as 代码名称,区域代码 as 代码编码,详细名称,企业代码,sum(污染源是否达标) 达标次数,
				count(污染源是否达标) as 监测次数,
			cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as  达标率
			from 
			(	/*计算某季度、某次，企业是否达标*/
				select 监测年,季度,次数,专题区域,区域代码,详细名称,企业代码,
				case (sum(cast(监测点是否达标 as int))-count(监测点是否达标)) when 0 then 1 else 0 end as 污染源是否达标
				from 
				(	/*计算某季度、某次，监测点是否达标*/
					select 监测年,季度,次数,专题区域,区域代码,详细名称,企业代码,监测点代码,
					case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 监测点是否达标
					from 
					(
						select a.企业类型,a.监测年,a.季度,a.次数,c.代码名称 as 专题区域,c.ID as 区域代码,b.详细名称,a.企业代码,a.监测点代码,a.监测项目编号,
						a.浓度是否达标 as 污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.污染物浓度,
						b.行政区代码,b.污染源控制级别代码,b.污染源控制级别代码气,b.自定义属性,b.行业代码,a.排放上限,
						convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期 
						from 业务_废气监测数据 a join V_工业和固废企业_简单 b
						on a.监测年=b.监测年 and a.企业类型=b.企业类型 and a.企业代码=b.企业代码 
						join (select a.代码名称,a.ID,b.行政区名称,b.行政区编码 from 系统_代码 a join 系统_专题区域代码 b
						on a.ID=b.区域类型编码 where a.代码类型编码=33) c on b.行政区代码=c.行政区编码
					)a 
					where 排放上限 is not null and 企业类型='+@tempenterprisetype+@temptimewhere+@tempCity+@tempTrade++@tempControlType+@tempControlTypeGas+@temppolutionCode+@tempjointype+@temppriparm+'
					group by 监测年,季度,次数,专题区域,区域代码,详细名称,企业代码,监测点代码
				) a group by 监测年,季度,次数,专题区域,区域代码,详细名称,企业代码
			) a group by 监测年,季度,专题区域,区域代码,详细名称,企业代码 
) a group by 年度,代码名称,代码编码,详细名称,企业代码
order by 年度,企业代码'
end
exec(@sSQL)
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetWhaterPolutionAccount_Calculate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetWhaterPolutionAccount_Calculate]
GO

-- =============================================
-- exec [GetWhaterPolutionAccount_Calculate] 2,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetWhaterPolutionAccount] 3,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetWhaterPolutionAccount] 4,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetWhaterPolutionAccount] 5,'2011','1','','','','0','0','','1','1','1',''
-- exec [GetWhaterPolutionAccount] 6,'2011','1','','','','0','0','','1','1','1',''
-- Author:		<Author,huabo,>
-- Create date: <Create Date,2011-10-25,>
-- Description:	<Description,污水处理厂达标率计算>
-- =============================================
CREATE proc [dbo].[GetWhaterPolutionAccount_Calculate]
(
	@type int ,
	@year varchar(4),
	@quarter varchar(20),
	@citycode varchar(max),
	@tradecode varchar(max),
	@whatercode varchar(max),
	@controltype varchar(50),
	@controltypegas varchar(50),
	@polutioncode varchar(max),
	@enterprisetype varchar(5),
	@joinType varchar(10),
	@pjType varchar(10),
	@timeWhere varchar(100)
)
as
--变量设置开始
declare @tempCity varchar(max)
declare @tempTrade varchar(max)
declare @tempWhater varchar(max)
declare @tempControlType varchar(50)
declare @tempControlTypeGas varchar(50)
declare @temppolutionCode varchar(max)
declare @tempenterprisetype varchar(100)
declare @tempjointype varchar(100)
declare @temppjtype varchar(100)
declare @temptimewhere varchar(100)

set @tempCity=''
set @tempTrade=''
set @tempWhater=''
set @tempControlType=''
set @temppolutionCode=''
set @tempControlTypeGas=''
set @tempenterprisetype='1'
set @tempjointype=''
set @temppjtype='isnull(污染物浓度是否达标,0)'

--受纳水体
if @whatercode!=''
begin
	set @whatercode=''''+replace(@whatercode,',',''',''')+''''
	set @tempWhater=' and (substring(受纳水体代码,1,2) in ('+@whatercode+') or 受纳水体代码 in('+@whatercode+')) '
end
--行政区
if @citycode!=''
begin
	set @tempCity=@citycode
end
--行业
if @tradecode!=''
begin
set @tempTrade=''
end
--控制级别
if @controltype!='0'
begin
set @tempControlType=' and 污染源控制级别代码 in ('+@controltype+')'
end
--控制级别气
if @controltypegas!='0'
begin
set @tempControlType=''
end
--监测项目
if @polutioncode!=''
begin
set @temppolutionCode=' and 监测项目编号 in ('+@polutioncode+')'
end
--企业类型
if @enterprisetype<>''
begin
	set @tempenterprisetype=@enterprisetype
end
--如果评价类型为“实测浓度”
if @pjType='1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--如果评价类型为“基准浓度”
else if @pjType='2'
begin
	set @temppjtype='基准浓度是否达标'
	if @joinType='1'
	begin
		set @tempjointype=' and 是否参与基准浓度=1 '
	end
	else
    begin
        set @tempjointype=' and 是否参与基准浓度 is not null ' --是否参与基准浓度is null的数据是无效的，需要过滤掉，类似于没有标准值的监测项目对于达标计算而言是无意义的。
    end
end
--如果企业类型不是污染源
if @enterprisetype<>'1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--时间条件
if @timeWhere is not null and @timeWhere<>''
begin
	set @temptimewhere=@timeWhere
end
else
begin
	set @temptimewhere=' and 监测年='+@year+' and 季度 in ('+@quarter+') '
end

-----主体部分开始
declare @sSQL nvarchar (MAX)
BEGIN
if object_id('tempdb..##regionCode') is not null begin drop table ##regionCode end
if object_id('tempdb..##temp') is not null begin drop table ##temp end
exec('select * into ##regionCode from (select 代码编码 as 行政区代码 from 系统_代码 where 代码类型编码=14) as t where 1=1 '+@citycode)
set @sSQL =
	'select 监测年,季度,次数,省,市,省代码,市代码,详细名称,水系代码,水系,流域代码,流域,
		case (sum(cast(污染物浓度是否达标 as int))-count(污染物浓度是否达标)) when 0 then 1 else 0 end as 污染源是否达标
		into ##temp from 
		(
			select a.监测年,a.季度,a.次数,c.省,d.市,c.省代码,d.市代码,b.详细名称,a.企业代码,a.监测项目编号,b.受纳水体代码,
			a.浓度是否达标 as 污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.出口污染物浓度 as 污染物浓度,
			b.行政区代码,b.控制级别代码 as 污染源控制级别代码,水系代码,水系,流域代码,流域,a.排放上限,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期
			from 业务_污水处理厂监测数据 a join 业务_污水处理厂信息 b
			on a.监测年=b.监测年 and a.企业代码=b.企业代码 
			inner join (select rtrim(代码编码) as 省代码,代码名称 as 省 from 系统_代码 where 代码类型编码=14) c
			on substring(b.行政区代码,1,2)+''0000''=c.省代码
			inner join (select rtrim(代码编码) as 市代码,代码名称 as 市 from 系统_代码 where 代码类型编码=14) d
			on substring(b.行政区代码,1,4)+''00''=d.市代码
			inner join (select 代码编码 as 水系代码,代码名称 as 水系 from 系统_代码 where 代码类型编码=16 
			and (substring(代码编码,3,8)=''00000000'' or len(代码编码)=1)) c1
			on substring(b.受纳水体代码,1,2)=substring(c1.水系代码,1,2) or b.受纳水体代码=c1.水系代码
			inner join (select 代码编码 as 流域代码,代码名称 as 流域 from 系统_代码 where 代码类型编码=20 
			and (substring(代码编码,3,8)=''00000000'' or len(代码编码)=1)) c2
			on substring(b.受纳水体代码,1,1)=substring(c2.流域代码,1,1) or b.受纳水体代码=c2.流域代码
		)a 
		where 排放上限 is not null and 1=1 and 行政区代码 in (select 行政区代码 from ##regionCode) '+@temptimewhere+@tempTrade+@tempControlType+@tempControlTypeGas+@tempWhater+@temppolutionCode+@tempjointype+'
		group by 监测年,季度,次数,省,市,省代码,市代码,详细名称,水系代码,水系,流域代码,流域'
		exec(@sSQL)
if(@type=2)--按地区查询
begin
	select 省+'/'+市 as 代码名称,省代码+'/'+市代码 as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 
	from 
	(	/*计算某季度，某企业达标率*/
		select 监测年,省,市,省代码,市代码,详细名称,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from ##temp group by 监测年,省,市,省代码,市代码,详细名称
	) a group by 省,市,省代码,市代码
	union 
	select '全部' as 代码名称,'000000' as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 
	from 
	(	/*计算某季度，某企业达标率*/
		select 监测年,省,市,省代码,市代码,详细名称,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from ##temp group by 监测年,省,市,省代码,市代码,详细名称
	) a
	order by 代码编码
end
if(@type=4)--按地区查询
begin
	select 省 as 代码名称,省代码 as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 
	from 
	(	/*计算某季度，某企业达标率*/
		select 监测年,省,省代码,详细名称,市,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from ##temp group by 监测年,省,省代码,详细名称,市
	) a group by 省,省代码 
	union
	select '全部' as 代码名称,'000000' as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 
	from 
	(	/*计算某季度，某企业达标率*/
		select 监测年,省,省代码,详细名称,市,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from ##temp group by 监测年,省,省代码,详细名称,市
	) a
	order by 代码编码
end
if(@type=3)--按水系查询
begin
	select 水系 as 代码名称,水系代码 as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 
	from 
	(	/*计算某季度，某企业达标率*/
		select 监测年,水系,水系代码,详细名称,市,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from ##temp group by 监测年,水系,水系代码,详细名称,市
	) a group by 水系,水系代码
	union
	select '全部' as 代码名称,'000000' as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 
	from 
	(	/*计算某季度，某企业达标率*/
		select 监测年,水系,水系代码,详细名称,市,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from ##temp group by 监测年,水系,水系代码,详细名称,市
	) a
	order by 代码编码
end
if(@type=5)--按流域查询
begin
	select 流域 as 代码名称,流域代码 as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 
		from 
		(	/*计算某季度，某企业达标率*/
			select 监测年,流域,流域代码,详细名称,市,
			cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
			from ##temp group by 监测年,流域,流域代码,详细名称,市
		) a group by 流域,流域代码
	union
	select '全部' as 代码名称,'000000' as 代码编码,count(详细名称) as 企业数量,cast(avg(污染源达标率) as decimal(18,3)) as 达标率 
	from 
	(	/*计算某季度，某企业达标率*/
		select 监测年,流域,流域代码,详细名称,市,
		cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 污染源达标率
		from ##temp group by 监测年,流域,流域代码,详细名称,市
	) a
	order by 代码编码
end

if object_id('tempdb..##regionCode') is not null begin drop table ##regionCode end
if object_id('tempdb..##temp') is not null begin drop table ##temp end
END

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCorpWhaterPolutionAccount_Calculate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCorpWhaterPolutionAccount_Calculate]
GO

-- =============================================
-- exec [GetCorpWhaterPolutionAccount] 2,'2011','1','','','','0','0','','1','1','1','110100    ',''
-- exec [GetCorpWhaterPolutionAccount] 3,'2011','1','','','','0','0','','1','1','1','AA00000000',''
-- exec [GetCorpWhaterPolutionAccount] 4,'2011','1','','','','0','0','','1','1','1','110000    ',''
-- exec [GetCorpWhaterPolutionAccount] 5,'2011','1','','','','0','0','','1','1','1','A         ',''
-- exec [GetCorpWhaterPolutionAccount] 6,'2011','1','','','','0','0','','1','1','1','72954',''
-- Author:		<Author,,huabo>
-- Create date: <Create Date,2011-1--25,>
-- Description:	<Description,污水处理厂企业达标率计算>
-- =============================================
CREATE proc [dbo].[GetCorpWhaterPolutionAccount_Calculate]
(
	@type int ,
	@year varchar(4),
	@quarter varchar(20),
	@citycode varchar(max),
	@tradecode varchar(max),
	@whatercode varchar(max),
	@controltype varchar(50),
	@controltypegas varchar(50),
	@polutioncode varchar(max),
	@enterprisetype varchar(5),
	@joinType varchar(10),
	@pjType varchar(10),
	@priparm varchar(50),
	@timeWhere varchar(100)
)
as
--变量设置开始
declare @tempCity varchar(max)
declare @tempTrade varchar(max)
declare @tempWhater varchar(max)
declare @tempControlType varchar(50)
declare @tempControlTypeGas varchar(50)
declare @temppolutionCode varchar(max)
declare @tempenterprisetype varchar(100)
declare @tempjointype varchar(100)
declare @temppjtype varchar(100)
declare @temppriparm varchar(100)
declare @temptimewhere varchar(100)

set @tempCity=''
set @tempTrade=''
set @tempWhater=''
set @tempControlType=''
set @temppolutionCode=''
set @tempControlTypeGas=''
set @tempenterprisetype='1'
set @tempjointype=''
set @temppjtype='isnull(污染物浓度是否达标,0)'
set @temppriparm=''

--受纳水体
if @whatercode!=''
begin
	set @whatercode=''''+replace(@whatercode,',',''',''')+''''
	set @tempWhater=' and (substring(受纳水体代码,1,2) in ('+@whatercode+') or 受纳水体代码 in('+@whatercode+')) '
end
--行政区
if @citycode!=''
begin
	set @tempCity=@citycode
end
--行业
if @tradecode!=''
begin
set @tempTrade=''
end
--控制级别
if @controltype!='0'
begin
set @tempControlType=' and 污染源控制级别代码 in ('+@controltype+')'
end
--控制级别气
if @controltypegas!='0'
begin
set @tempControlType=''
end
--监测项目
if @polutioncode!=''
begin
set @temppolutionCode=' and 监测项目编号 in ('+@polutioncode+')'
end
--企业类型
if @enterprisetype<>''
begin
	set @tempenterprisetype=@enterprisetype
end
--如果评价类型为“实测浓度”
if @pjType='1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--如果评价类型为“基准浓度”
else if @pjType='2'
begin
	set @temppjtype='基准浓度是否达标'
	if @joinType='1'
	begin
		set @tempjointype=' and 是否参与基准浓度=1 '
	end
	else
    begin
        set @tempjointype=' and 是否参与基准浓度 is not null ' --是否参与基准浓度is null的数据是无效的，需要过滤掉，类似于没有标准值的监测项目对于达标计算而言是无意义的。
    end
end
--如果企业类型不是污染源
if @enterprisetype<>'1'
begin
	set @temppjtype='isnull(污染物浓度是否达标,0)'
	if @joinType='1'
	begin
		set @tempjointype=' and 污染物浓度 is not null '
	end
end
--过滤主键条件
if @type=1 and @priparm<>'000000'
begin
	set @temppriparm=' and 行业编码='''+@priparm+''' '
end
if @type=2 and @priparm<>'000000'
begin
	set @temppriparm=' and 市级行政区代码='''+@priparm+''' '
end
if @type=4 and @priparm<>'000000'
begin
	set @temppriparm=' and 省级行政区代码='''+@priparm+''' '
end
if @type=3 and @priparm<>'000000'
begin
	set @temppriparm=' and 水系代码='''+@priparm+''' '
end
if @type=5 and @priparm<>'000000'
begin
	set @temppriparm=' and 流域代码='''+@priparm+''' '
end
if @type=6 and @priparm<>'000000'
begin
	set @temppriparm=' and 区域代码='''+@priparm+''' '
end
--时间条件
if @timeWhere is not null and @timeWhere<>''
begin
	set @temptimewhere=@timeWhere
end
else
begin
	set @temptimewhere=' and 监测年='+@year+' and 季度 in ('+@quarter+') '
end

-----主体部分开始
declare @sSQL nvarchar (MAX)
BEGIN
if object_id('tempdb..##regionCode') is not null begin drop table ##regionCode end
exec('select * into ##regionCode from (select 代码编码 as 行政区代码 from 系统_代码 where 代码类型编码=14) as t where 1=1 '+@citycode)
if(@type=2 or @type=3 or @type=4 or @type=5)--按地区查询
begin
set @sSQL=
	   'select 监测年 as 年度,行政区代码,省级行政区名称,市级行政区名称,详细名称,sum(污染源是否达标) 达标次数,
		count(污染源是否达标) as 监测次数,cast(cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as decimal(18,3)) as 达标率
		from 
		(	/*计算某季度、某次，企业是否达标*/
			select 监测年,季度,次数,行政区代码,省级行政区名称,市级行政区名称,详细名称,
			case (sum(cast(污染物浓度是否达标 as int))-count(污染物浓度是否达标)) when 0 then 1 else 0 end as 污染源是否达标
			from 
			(
				select a.监测年,a.季度,a.次数,b.行政区代码,c.省级行政区名称,d.市级行政区名称,b.详细名称,a.企业代码,a.监测项目编号,b.受纳水体代码,
				a.浓度是否达标 as 污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.出口污染物浓度 as 污染物浓度,水系代码,流域代码,
				b.控制级别代码 as 污染源控制级别代码,c.省级行政区代码,d.市级行政区代码,a.排放上限,
				convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期
				from 业务_污水处理厂监测数据 a join 业务_污水处理厂信息 b
				on a.监测年=b.监测年 and a.企业代码=b.企业代码 
				inner join (select rtrim(代码编码) as 省级行政区代码,代码名称 as 省级行政区名称 from 系统_代码 where 代码类型编码=14) c
				on substring(b.行政区代码,1,2)+''0000''=c.省级行政区代码
				inner join (select rtrim(代码编码) as 市级行政区代码,代码名称 as 市级行政区名称 from 系统_代码 where 代码类型编码=14) d
				on substring(b.行政区代码,1,4)+''00''=d.市级行政区代码
				inner join (select 代码编码 as 水系代码,代码名称 from 系统_代码 where 代码类型编码=16 and (substring(代码编码,3,8)=''00000000'' or len(代码编码)=1)) c3
				on substring(b.受纳水体代码,1,2)=substring(c3.水系代码,1,2) or b.受纳水体代码=c3.水系代码
				inner join (select 代码编码 as 流域代码,代码名称 from 系统_代码 where 代码类型编码=20 and (substring(代码编码,3,8)=''00000000'' or len(代码编码)=1)) c4
				on substring(b.受纳水体代码,1,1)=substring(c4.流域代码,1,1) or b.受纳水体代码=c4.流域代码
			)a 
			where 排放上限 is not null and 1=1 and 行政区代码 in (select 行政区代码 from ##regionCode) '+@temptimewhere+@tempTrade++@tempControlType+@tempControlTypeGas+@tempWhater+@temppolutionCode+@tempjointype+@temppriparm+'
			group by 监测年,季度,次数,行政区代码,省级行政区名称,市级行政区名称,详细名称
		) a group by 监测年,行政区代码,省级行政区名称,市级行政区名称,详细名称 
		order by 年度,详细名称'
end
if(@type=6)--专题区域
begin
set @sSQL='select 年度,代码名称,代码编码,详细名称,企业代码,sum(监测次数) as 监测次数,
sum(达标次数) as 达标次数,avg(达标率) as 达标率
from (
	select 监测年 as 年度,季度,专题区域 as 代码名称,区域代码 as 代码编码,详细名称,企业代码,sum(污染源是否达标) 达标次数,
	count(污染源是否达标) as 监测次数,cast(sum(污染源是否达标) as decimal(18,2))/count(污染源是否达标) as 达标率
	from 
	(	/*计算某季度、某次，企业是否达标*/
		select 监测年,季度,次数,专题区域,区域代码,详细名称,企业代码,
		case (sum(cast('+@temppjtype+' as int))-count('+@temppjtype+')) when 0 then 1 else 0 end as 污染源是否达标
		from 
		(
			select a.监测年,a.季度,a.次数,c.代码名称 as 专题区域,c.ID as 区域代码,b.详细名称,a.企业代码,a.监测项目编号,b.受纳水体代码,
			a.浓度是否达标 as 污染物浓度是否达标,a.基准浓度是否达标,a.是否参与基准浓度,a.出口污染物浓度 as 污染物浓度,
			b.行政区代码,b.控制级别代码 as 污染源控制级别代码,a.排放上限,
			convert(datetime,convert(varchar,a.监测年)+''-''+convert(varchar,a.监测月)+''-''+convert(varchar,a.监测日)) as 监测日期
			from 业务_污水处理厂监测数据 a join 业务_污水处理厂信息 b
			on a.监测年=b.监测年 and a.企业代码=b.企业代码 
			join (select a.代码名称,a.ID,b.行政区名称,b.行政区编码 from 系统_代码 a join 系统_专题区域代码 b
			on a.ID=b.区域类型编码 where a.代码类型编码=33) c on b.行政区代码=c.行政区编码
		)a 
		where 排放上限 is not null and 1=1 and 行政区代码 in (select 行政区代码 from ##regionCode) '+@temptimewhere+@tempTrade++@tempControlType+@tempControlTypeGas+@tempWhater+@temppolutionCode+@tempjointype+@temppriparm+'
		group by 监测年,季度,次数,专题区域,区域代码,详细名称,企业代码
	) a group by 监测年,季度,专题区域,区域代码,详细名称,企业代码 
) a group by 年度,代码名称,代码编码,详细名称,企业代码
order by 年度,企业代码'
end

exec(@sSQL)

if object_id('tempdb..##regionCode') is not null begin drop table ##regionCode end

END

GO
