-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,ljf>
-- Create date: <Create Date,,2019-08-15>
-- Description:	<Description,,使用量分析>
-- =============================================
ALTER PROCEDURE pro_usage_analysis
@level			int,					--传入的值
@nodeName		varchar(50),			--节点的值
@time			varchar(20),			--传入时间
@page			int,					--页数
@limit			int,					--条数
@result_count	int	out					--输出的记录值
AS
declare			@nodeCount		int				--区域、楼盘、楼栋数目
declare			@name			varchar(50)		--区域、楼盘、楼栋
declare			@total_used		numeric(24,2)	--用量
declare			@table_name		varchar(20)		--获取表的时间
declare			@quarter		varchar(10)		--获取到第几季度
declare			@sqlStr			nvarchar(max)	--sql语句

declare	@result_table table
(
	id				bigint identity(1,1)		not null,
	times			varchar(50)					null,
	name			varchar(50)					null,
	total_used		numeric(24,2)				null,
	ratio			varchar(20)					null
)
BEGIN
	if @level is null
	begin
		set @level = 1
	end
	if @nodeName is null
	begin
		select top 1 @nodeName = house_num from user_manage where parent_id = 0
	end
	if @time is null
	begin
		set @time = convert(varchar(10),datepart(yyyy,getdate()))+'年第1季度'
	end
	print @time
	--查询当前级别下有多少个子节点
	--select * from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')
	
	if len(@time) = 10	--按日查询
	begin
		--获取表的后缀时间
		set @table_name = replace(substring(@time,1,7),'-','')
		if @level = 3		--查询具体到楼栋
		begin
			--声明游标
			declare cs_name cursor for
				select house_num from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')	
			--打开游标
			open cs_name
			fetch next from cs_name into @name
			while @@fetch_status = 0
			begin
				--判断是否存在值
				set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_day'+@table_name+' where building_name = '''+@name+'''';
				--执行SQL语句，把结果赋值给变量
				exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out

				if @nodeCount <> 0
				begin  
					set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_day'+@table_name
								+' where building_name = '''+@name+''' and convert(varchar(10),cur_date,23) = '''+@time+'''';
					--执行SQL语句，把结果赋值给变量
					exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
				end
				else 
				begin
					set @total_used = 0
				end
				insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
				fetch next from cs_name into @name
			end
			--关闭游标
			close cs_name
			--释放游标
			deallocate cs_name	
			select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
			update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'   
		end
		else 
		if @level = 2		--查询具体到楼盘
		begin
			--声明游标
			declare cs_name cursor for
				select house_num from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')	
			--打开游标
			open cs_name
			fetch next from cs_name into @name
			while @@fetch_status = 0
			begin
				--判断是否存在值
				set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_day'+@table_name+' where areas_name = '''+@name+'''';
				--执行SQL语句，把结果赋值给变量
				exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
				if @nodeCount <> 0
				begin  
					set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_day'+@table_name
								+' where areas_name = '''+@name+''' and convert(varchar(10),cur_date,23) = '''+@time+'''';
					--执行SQL语句，把结果赋值给变量
					exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
				end
				else 
				begin
					set @total_used = 0
				end
				insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
				fetch next from cs_name into @name
			end
			--关闭游标
			close cs_name
			--释放游标
			deallocate cs_name	
			select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
			if @total_used = 0
			begin
				update @result_table set ratio = '0.00%'  
			end else
			begin
				update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'  
			end 	
		end
		else 
		if @level = 1	--查询到某个片区
		begin
			--声明游标
			declare cs_name cursor for
				(select house_num from user_manage where parent_id in 
					(select id from user_manage where parent_id =
						(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')))	
			--打开游标
			open cs_name
			fetch next from cs_name into @name
			while @@fetch_status = 0
			begin
				--判断是否存在值
				set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_day'+@table_name+' where areas_name = '''+@name+'''';
				--执行SQL语句，把结果赋值给变量
				exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
				if @nodeCount <> 0
				begin  
					set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_day'+@table_name
								+' where areas_name = '''+@name+''' and convert(varchar(10),cur_date,23) = '''+@time+'''';
					--执行SQL语句，把结果赋值给变量
					exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
				end
				else 
				begin
					set @total_used = 0
				end
				insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
				fetch next from cs_name into @name
			end
			--关闭游标
			close cs_name
			--释放游标
			deallocate cs_name	
			select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
			if @total_used = 0
			begin
				update @result_table set ratio = '0.00%'  
			end else
			begin
				update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'  
			end 
		end		
	end
	else
	if len(@time) = 7	--按月查询
	begin
	if @level = 3		--查询具体到楼栋
		begin
			--声明游标
			declare cs_name cursor for
				select house_num from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')	
			--打开游标
			open cs_name
			fetch next from cs_name into @name
			while @@fetch_status = 0
			begin
				--判断是否存在值
				set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_day'+@table_name+' where building_name = '''+@name+'''';
				--执行SQL语句，把结果赋值给变量
				exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out

				if @nodeCount <> 0
				begin  
					--select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month2019 where building_name = ''+@name+''
					set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
								+' where building_name = '''+@name+''' and cur_date = '''+@time+'''';
					--执行SQL语句，把结果赋值给变量
					exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
				end
				else 
				begin
					set @total_used = 0
				end
				insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
				fetch next from cs_name into @name
			end
			--关闭游标
			close cs_name
			--释放游标
			deallocate cs_name	
			select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
			update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'   
		end
		else 
		if @level = 2		--查询具体到楼盘
		begin
			--声明游标
			declare cs_name cursor for
				select house_num from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')	
			--打开游标
			open cs_name
			fetch next from cs_name into @name
			while @@fetch_status = 0
			begin
				--判断是否存在值
				set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_day'+@table_name+' where areas_name = '''+@name+'''';
				--执行SQL语句，把结果赋值给变量
				exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
				if @nodeCount <> 0
				begin  
					--select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month2019 where areas_name = ''+@name+''
					set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
								+' where areas_name = '''+@name+''' and cur_date = '''+@time+'''';
					--执行SQL语句，把结果赋值给变量
					exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
				end
				else 
				begin
					set @total_used = 0
				end
				insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
				fetch next from cs_name into @name
			end
			--关闭游标
			close cs_name
			--释放游标
			deallocate cs_name	
			select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
			if @total_used = 0
			begin
				update @result_table set ratio = '0.00%'  
			end else
			begin
				update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'  
			end 	
		end
		else 
		if @level = 1	--查询到某个片区
		begin
			--声明游标
			declare cs_name cursor for
				(select house_num from user_manage where parent_id in 
					(select id from user_manage where parent_id =
						(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')))	
			--打开游标
			open cs_name
			fetch next from cs_name into @name
			while @@fetch_status = 0
			begin
				--判断是否存在值
				--select top 1 @nodeCount = count(*) from wt_building_month2019 where areas_name = ''+@name+''
				--判断是否存在值
				set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_day'+@table_name+' where areas_name = '''+@name+'''';
				--执行SQL语句，把结果赋值给变量
				exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out

				if @nodeCount <> 0
				begin  
					--select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month2019 where areas_name = ''+@name+''
					set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
								+' where areas_name = '''+@name+''' and cur_date = '''+@time+'''';
					--执行SQL语句，把结果赋值给变量
					exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
				end
				else 
				begin
					set @total_used = 0
				end
				insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
				fetch next from cs_name into @name
			end
			--关闭游标
			close cs_name
			--释放游标
			deallocate cs_name	
			select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
			if @total_used = 0
			begin
				update @result_table set ratio = '0.00%'  
			end else
			begin
				update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'  
			end 
		end
	end
	if len(@time) = 4	--按年查询
	begin
	if @level = 3		--查询具体到楼栋
		begin
			--声明游标
			declare cs_name cursor for
				select house_num from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')	
			--打开游标
			open cs_name
			fetch next from cs_name into @name
			while @@fetch_status = 0
			begin
				--判断是否存在值
				--select top 1 @nodeCount = count(*) from wt_building_year2019 where building_name = ''+@name+''
				--判断是否存在值
				set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_year'+@table_name+' where building_name = '''+@name+'''';
				--执行SQL语句，把结果赋值给变量
				exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
				if @nodeCount <> 0
				begin  
					--select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_year2019 where building_name = ''+@name+''
					set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_year'+@table_name
								+' where building_name = '''+@name+''' and cur_date = '''+@time+'''';
					--执行SQL语句，把结果赋值给变量
					exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
				end
				else 
				begin
					set @total_used = 0
				end
				insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
				fetch next from cs_name into @name
			end
			--关闭游标
			close cs_name
			--释放游标
			deallocate cs_name	
			select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
			update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'   
		end
		else 
		if @level = 2		--查询具体到楼盘
		begin
			--声明游标
			declare cs_name cursor for
				select house_num from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')	
			--打开游标
			open cs_name
			fetch next from cs_name into @name
			while @@fetch_status = 0
			begin
				--判断是否存在值
				--select top 1 @nodeCount = count(*) from wt_building_year2019 where areas_name = ''+@name+''
				--判断是否存在值
				set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_year'+@table_name+' where areas_name = '''+@name+'''';
				--执行SQL语句，把结果赋值给变量
				exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out

				if @nodeCount <> 0
				begin  
					--select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_year2019 where areas_name = ''+@name+''
					set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_year'+@table_name
								+' where areas_name = '''+@name+''' and cur_date = '''+@time+'''';
					--执行SQL语句，把结果赋值给变量
					exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
				end
				else 
				begin
					set @total_used = 0
				end
				insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
				fetch next from cs_name into @name
			end
			--关闭游标
			close cs_name
			--释放游标
			deallocate cs_name	
			select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
			if @total_used = 0
			begin
				update @result_table set ratio = '0.00%'  
			end else
			begin
				update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'  
			end 	
		end
		else 
		if @level = 1	--查询到某个片区
		begin
			--声明游标
			declare cs_name cursor for
				(select house_num from user_manage where parent_id in 
					(select id from user_manage where parent_id =
						(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')))	
			--打开游标
			open cs_name
			fetch next from cs_name into @name
			while @@fetch_status = 0
			begin
				--判断是否存在值
				--select top 1 @nodeCount = count(*) from wt_building_year2019 where areas_name = ''+@name+''
				--判断是否存在值
				set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_year'+@table_name+' where areas_name = '''+@name+'''';
				--执行SQL语句，把结果赋值给变量
				exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out
				if @nodeCount <> 0
				begin  
					--select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_year2019 where areas_name = ''+@name+''
					set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_year'+@table_name
								+' where areas_name = '''+@name+''' and cur_date = '''+@time+'''';
					--执行SQL语句，把结果赋值给变量
					exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
				end
				else 
				begin
					set @total_used = 0
				end
				insert into @result_table(times,name,total_used)values(@time,@name,@total_used)
				fetch next from cs_name into @name
			end
			--关闭游标
			close cs_name
			--释放游标
			deallocate cs_name	
			select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
			if @total_used = 0
			begin
				update @result_table set ratio = '0.00%'  
			end else
			begin
				update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'  
			end 
		end			
	end
	else 
	begin				--按季度查询
		--获取哪一年
		set @table_name = substring(@time,1,4) 
		--获取哪一个季度
		set @quarter = substring(@time,6,5)	
		--判断是属于哪一个季度
		--if charindex('1',@quarter) > 0			--第一季度
		begin
			if @level = 3		--查询具体到楼栋
			begin
				--声明游标
				declare cs_name cursor for
					select house_num from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')	
				--打开游标
				open cs_name
				fetch next from cs_name into @name
				while @@fetch_status = 0
				begin
					--判断是否存在值
					--select top 1 @nodeCount = count(*) from wt_building_month2019 where building_name = ''+@name+''
					--判断是否存在值
					set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_month'+@table_name+' where building_name = '''+@name+'''';
					--执行SQL语句，把结果赋值给变量
					exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out

					if @nodeCount <> 0
					begin  
						--select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month2019 where building_name = ''+@name+''
						if charindex('1',@quarter) > 0			--第一季度
						begin
							set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
									+' where building_name = '''+@name+''' and cur_date = '''+@table_name+'-01'+''' or cur_date = '''+@table_name+'-02'+''' or cur_date = '''+@table_name+'-03'+'''';
							--执行SQL语句，把结果赋值给变量
							exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
						end 
						else
						if charindex('2',@quarter) > 0			--第二季度
						begin
							set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
									+' where building_name = '''+@name+''' and cur_date = '''+@table_name+'-04'+''' or cur_date = '''+@table_name+'-05'+''' or cur_date = '''+@table_name+'-06'+'''';
							--执行SQL语句，把结果赋值给变量
							exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
						end
						else 
						if charindex('3',@quarter) > 0			--第三季度
						begin
							set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
									+' where building_name = '''+@name+''' and cur_date = '''+@table_name+'-07'+''' or cur_date = '''+@table_name+'-08'+''' or cur_date = '''+@table_name+'-09'+'''';
							--执行SQL语句，把结果赋值给变量
							exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
						end
						else 
						if charindex('4',@quarter) > 0			--第四季度
						begin
							set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
									+' where building_name = '''+@name+''' and cur_date = '''+@table_name+'-10'+''' or cur_date = '''+@table_name+'-11'+''' or cur_date = '''+@table_name+'-12'+'''';
							--执行SQL语句，把结果赋值给变量
							exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
						end
					end
					else 
					begin
						set @total_used = 0
					end
					insert into @result_table(times,name,total_used)values(@time,@name,isnull(@total_used,0.00))
					fetch next from cs_name into @name
				end
				--关闭游标
				close cs_name
				--释放游标
				deallocate cs_name	
				select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
				update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'   
			end
			else 
			if @level = 2		--查询具体到楼盘
			begin
				--声明游标
				declare cs_name cursor for
					select house_num from user_manage where parent_id =(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')	
				--打开游标
				open cs_name
				fetch next from cs_name into @name
				while @@fetch_status = 0
				begin
					--判断是否存在值
					--select top 1 @nodeCount = count(*) from wt_building_month2019 where areas_name = ''+@name+''
					--判断是否存在值
					set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_month'+@table_name+' where areas_name = '''+@name+'''';
					--执行SQL语句，把结果赋值给变量
					exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out

					if @nodeCount <> 0
					begin  
						--select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month2019 where areas_name = ''+@name+''
						--set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
						--		+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-01'+''' or cur_date = '''+@table_name+'-02'+''' or cur_date = '''+@table_name+'-02'+'''';
						----执行SQL语句，把结果赋值给变量
						--exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
						if charindex('1',@quarter) > 0			--第一季度
						begin
							set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
									+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-01'+''' or cur_date = '''+@table_name+'-02'+''' or cur_date = '''+@table_name+'-03'+'''';
							--执行SQL语句，把结果赋值给变量
							exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
						end 
						else
						if charindex('2',@quarter) > 0			--第二季度
						begin
							set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
									+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-04'+''' or cur_date = '''+@table_name+'-05'+''' or cur_date = '''+@table_name+'-06'+'''';
							--执行SQL语句，把结果赋值给变量
							exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
						end
						else 
						if charindex('3',@quarter) > 0			--第三季度
						begin
							set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
									+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-07'+''' or cur_date = '''+@table_name+'-08'+''' or cur_date = '''+@table_name+'-09'+'''';
							--执行SQL语句，把结果赋值给变量
							exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
						end
						else 
						if charindex('4',@quarter) > 0			--第四季度
						begin
							set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
									+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-10'+''' or cur_date = '''+@table_name+'-11'+''' or cur_date = '''+@table_name+'-12'+'''';
							--执行SQL语句，把结果赋值给变量
							exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
						end
					end
					else 
					begin
						set @total_used = 0
					end
					insert into @result_table(times,name,total_used)values(@time,@name,isnull(@total_used,0.00))
					fetch next from cs_name into @name
				end
				--关闭游标
				close cs_name
				--释放游标
				deallocate cs_name	
				select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
				if @total_used = 0
				begin
					update @result_table set ratio = '0.00%'  
				end else
				begin
					update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'  
				end 	
			end
			else 
			if @level = 1	--查询到某个片区
			begin
				print '查询到某个片区' 
				--声明游标
				declare cs_name cursor for
					(select house_num from user_manage where parent_id in 
						(select id from user_manage where parent_id =
							(select id from user_manage where house_num = ''+@nodeName+'' and node_type = ''+@level+'')))	
				--打开游标
				open cs_name
				fetch next from cs_name into @name
				while @@fetch_status = 0
				begin
					--判断是否存在值
					--select top 1 @nodeCount = count(*) from wt_building_month2019 where areas_name = ''+@name+''
					--判断是否存在值
					set @sqlStr ='select top 1 @nodeCount = count(*) from wt_building_month'+@table_name+' where areas_name = '''+@name+'''';
					--执行SQL语句，把结果赋值给变量
					exec sp_executesql @sqlStr,N'@nodeCount int out',@nodeCount out

					if @nodeCount <> 0
					begin  
						--select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month2019 where areas_name = ''+@name+''
						--set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
						--		+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-01'+''' or cur_date = '''+@table_name+'-02'+''' or cur_date = '''+@table_name+'-02'+'''';
						----执行SQL语句，把结果赋值给变量
						--exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
						if charindex('1',@quarter) > 0			--第一季度
						begin
							set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
									+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-01'+''' or cur_date = '''+@table_name+'-02'+''' or cur_date = '''+@table_name+'-03'+'''';
							--执行SQL语句，把结果赋值给变量
							--print @sqlStr
							exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
						end 
						else
						if charindex('2',@quarter) > 0			--第二季度
						begin
							set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
									+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-04'+''' or cur_date = '''+@table_name+'-05'+''' or cur_date = '''+@table_name+'-06'+'''';
							--执行SQL语句，把结果赋值给变量
							exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
						end
						else 
						if charindex('3',@quarter) > 0			--第三季度
						begin
							set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
									+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-07'+''' or cur_date = '''+@table_name+'-08'+''' or cur_date = '''+@table_name+'-09'+'''';
							--执行SQL语句，把结果赋值给变量
							exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
						end
						else 
						if charindex('4',@quarter) > 0			--第四季度
						begin
							set @sqlStr = 'select top 1 @total_used  = isnull(calc_flow,0.00) from wt_building_month'+@table_name
									+' where areas_name = '''+@name+''' and cur_date = '''+@table_name+'-10'+''' or cur_date = '''+@table_name+'-11'+''' or cur_date = '''+@table_name+'-12'+'''';
							--执行SQL语句，把结果赋值给变量
							exec sp_executesql @sqlStr,N'@total_used int out',@total_used out
						end
					end
					else 
					begin
						set @total_used = 0
					end
					insert into @result_table(times,name,total_used)values(@time,@name,isnull(@total_used,0.00))
					fetch next from cs_name into @name
				end
				--关闭游标
				close cs_name
				--释放游标
				deallocate cs_name	
				select top 1 @total_used = isnull(convert(numeric(24,2),sum(total_used)),0.00) from @result_table
				if @total_used = 0
				begin
					update @result_table set ratio = '0.00%'  
				end else
				begin
					update @result_table set ratio = convert(varchar(20),convert(numeric(24,2),total_used/@total_used)*100)+'%'  
				end 
			end	
		end
	end
	select * from @result_table order by id OFFSET (@limit * (@page-1)) ROW FETCH NEXT @limit rows only 
	select top 1 @result_count = count(*) from @result_table
END
GO