USE [AutoMonitorDB]
GO

/****** Object:  StoredProcedure [dbo].[ProcV2GetPCHourDataStrength]    Script Date: 01/09/2019 17:28:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

            
-- =============================================              
-- Author:  秦春娟              
-- Create date: 2010-10-20              
-- Description: ProcV2GetPCHourDataStrength获取行转列的排口小时浓度数据      
--Update user and data:liuwen(2014-11-21)            
-- test: ProcV2GetPCHourDataStrength 12,1,610100000001,'2012-11-01 00:00:00.000','2012-12-14 23:59:59.997',3,2        
-- test: ProcV2GetPCHourDataStrength 12,1,110000001022,'2014-09-01 00:00:00.000','2014-11-21 23:59:59.997',2,2              
-- =============================================           
 CREATE PROCEDURE [dbo].[ProcV2GetPCHourDataStrength]              
 @PageSize   int , --每页多少条                  
 @PageCurr   int , --第几页                  
 @PSCode bigint,             
 @StartDate varchar(30)  ,               
 @EndDate varchar(30),            
 @OutputCode bigint,             
 @OutputType int   --1水排口，2气排口，3水进口            
as              
declare @rowCount int              
declare @startNumber int                
declare @endNumber int              
declare @StartMonitorTime datetime               
declare @EndMonitorTime datetime               
            
            
            
--分页计算            
set @startNumber = @PageCurr*@PageSize - @PageSize + 1                
set @endNumber = @startNumber + @PageSize - 1                
            
if @OutputType = 1            
begin            
select @rowCount = count( MonitorTime)  from            
(            
	 select distinct  MonitorTime from MonitorOperationData.WaterFacHourData   with (nolock)       
	 where pscode = @PSCode and MonitorTime between @StartDate and @EndDate            
	 and outputCode = @OutputCode            
	 union             
	 select  distinct MonitorTime from MonitorOperationData.WaterOutputHourData   with (nolock)           
	 where pscode = @PSCode and MonitorTime between @StartDate and @EndDate            
	 and outputCode = @OutputCode            
 ) a            
---------------------------------------------------------              
---------------------------------------------------------            
  --计算开始结束时间            
select @StartMonitorTime = min(MonitorTime),@EndMonitorTime = max(MonitorTime) from            
(      
	 select MonitorTime,row_number() over(order by MonitorTime desc) rowid            
	 from       
	 (             
		  select distinct   MonitorTime from             
		  MonitorOperationData.WaterFacHourData    with (nolock)              
		  where pscode = @PSCode   and outputCode = @OutputCode             
		  and MonitorTime between @StartDate and @EndDate             
		  union             
		  select  distinct  MonitorTime from             
		  MonitorOperationData.WaterOutputHourData    with (nolock)     
		  where pscode = @PSCode and outputCode = @OutputCode            
		  and MonitorTime between @StartDate and @EndDate            
	 ) t              
)s            
where rowid between @startNumber  and @endNumber            
--返回非主键列列数            
print convert(varchar,@StartMonitorTime,120)            
print @EndMonitorTime            
select 2*count('1') as PIVORowNO from            
(             
	 select PollutantCode AS PollutantCode from  MonitorOperationData.WaterFacHourData   with (nolock)               
	 where pscode = @PSCode and outputCode = @OutputCode            
	 and MonitorTime between @StartMonitorTime and @EndMonitorTime             
	 group by PollutantCode              
)t                   
---------------------------------------------------------            
---------------------------------------------------------            
--返回数据结果            
select t.PSCode as PScode,convert(varchar,t.MonitorTime,120) as MonitorTime      
,case when a.RevisedFlow is null then '' else convert(varchar,a.RevisedFlow) end as WateroutputFlow       
,PollutantCode+'w' as PollutantCode       
,case when t.RevisedStrength is null then '' else     
(    
	 case when PollutantCode='001' then convert(varchar, dbo.clearzero(ROUND(t.RevisedStrength,1)))     
	 when PollutantCode='011' then convert(varchar,dbo.clearzero(ROUND(t.RevisedStrength,1)))    
	 when PollutantCode='060' then convert(varchar,dbo.clearzero(ROUND(t.RevisedStrength,2)))    
	 else     
	 convert(varchar,dbo.clearzero(t.RevisedStrength))    
	 end    
)    
 end as PollutantValue            
from        
(                       
    select PSCode,MonitorTime,outputcode,PollutantCode as PollutantCode,dbo.ClearZero(RevisedStrength) as RevisedStrength 
    from		MonitorOperationData.WaterFacHourData   with (nolock)                
	where pscode = @PSCode and outputCode = @OutputCode and MonitorTime between @StartDate and @EndDate 
	and outputCode = @OutputCode               
)t                
left join MonitorOperationData.WaterOutputHourData a  with (nolock)   on t.pscode = a.pscode             
		and t.MonitorTime= a.MonitorTime and a.outputCode = t.OutputCode            
where  t.MonitorTime between @StartMonitorTime and @EndMonitorTime              
union all            
select t.PSCode as PScode,convert(varchar,t.MonitorTime,120) as MonitorTime      
,case when a.RevisedFlow is null then '' else convert(varchar,a.RevisedFlow) end as WateroutputRevisedFlow       
,PollutantCode+'wColor' as PollutantCode,            
(case when (t.IsException = 0) then  'green'     
  when (t.IsException = 3)then 'red'     
  when (t.IsException = 1) then 'yellow'     
  else  'gray'            
  end) as PollutantValue            
from        
(                     
    select PSCode,MonitorTime,outputcode,PollutantCode as PollutantCode,RevisedStrength,IsException 
    from  MonitorOperationData.WaterFacHourData  with (nolock)                 
	where pscode = @PSCode and outputCode = @OutputCode and MonitorTime between @StartDate and @EndDate 
	and outputCode = @OutputCode               
)t                
left join MonitorOperationData.WaterOutputHourData a  with (nolock)   on t.pscode = a.pscode             
and t.MonitorTime= a.MonitorTime and a.outputCode = t.OutputCode            
where  t.MonitorTime between @StartMonitorTime and @EndMonitorTime              
order by   MonitorTime desc            
---------------------------------------------------------            
---------------------------------------------------------            
--返回总行数            
select @rowCount as Rows              
---------------------------------------------------------            
end            
else if @OutputType =2            
begin            
select @rowCount = count(MonitorTime)  from            
(            
 select MonitorTime from  MonitorOperationData.GasFacHourZsData   with (nolock)              
 where pscode = @PSCode and outputCode = @OutputCode and MonitorTime between @StartDate and @EndDate             
 union             
 select MonitorTime from MonitorOperationData.GasOutputHourData   with (nolock)             
 where pscode = @PSCode and outputCode = @OutputCode and MonitorTime between @StartDate and @EndDate            
               
) a            
---------------------------------------------------------              
---------------------------------------------------------            
  --计算开始结束时间            
select @StartMonitorTime = min(MonitorTime),@EndMonitorTime = max(MonitorTime) from            
(      
 select MonitorTime,row_number() over(order by MonitorTime desc) rowid            
 from       
 (             
	  select distinct MonitorTime from             
	  MonitorOperationData.GasFacHourZsData   with (nolock)             
	  where pscode = @PSCode  and outputCode = @OutputCode and MonitorTime between @StartDate and @EndDate            
	      
	  union            
	  select distinct  MonitorTime from             
	  MonitorOperationData.GasOutputHourData     with (nolock)            
	  where pscode = @PSCode and outputCode = @OutputCode  and MonitorTime between @StartDate and @EndDate             
 ) t              
)s            
 where rowid between @startNumber  and @endNumber            
--返回非主键列列数         
     
 select 3*count('1') as PIVORowNO 
 from            
(     
	select distinct * from (        
	select PollutantCode as PollutantCode  from  MonitorOperationData.GasFacHourZsData   with (nolock)            
	where pscode = @PSCode  and outputCode = @OutputCode  and MonitorTime between @StartMonitorTime and @EndMonitorTime              
	union            
	select distinct PollutantCode as PollutantCode   from             
	MonitorOperationData.GasFacHourData    with (nolock)           
	where pscode = @PSCode and outputCode = @OutputCode  and MonitorTime between @StartMonitorTime and @EndMonitorTime      
  ) d            
)t              
    
---------------------------------------------------------            
---------------------------------------------------------            
--返回数据结果            
select t.PSCode as PScode,convert(varchar,t.MonitorTime,120) as MonitorTime      
,case when c.RevisedFlow is null then '' else convert(varchar,c.RevisedFlow) end as GasOutputFlow       
,PollutantCode+'g' as PollutantCode      
,case when t.RevisedStrength is null then '' else convert(varchar,t.RevisedStrength) end as PollutantValue            
from        
(             
	 select distinct PSCode,outputcode,MonitorTime,PollutantCode+'z' as PollutantCode,    
	 case when ( pollutantcode = '001' or pollutantcode = '002' or pollutantcode = '003') then     
		convert(varchar,dbo.clearzero(ROUND(RevisedStrength,1)))    
		else convert(varchar,dbo.clearzero(RevisedStrength)) end as RevisedStrength            
	 from  MonitorOperationData.GasFacHourZsData   with (nolock)             
	 where pscode = @PSCode  and outputCode = @OutputCode   and MonitorTime between @StartDate and @EndDate       
	 union       
	 select distinct PSCode,outputcode,MonitorTime,PollutantCode as PollutantCode,    
	 case when ( pollutantcode = '001' or pollutantcode = '002' or pollutantcode = '003') then     
		convert(varchar,dbo.clearzero(ROUND(RevisedStrength,1)))    
		else convert(varchar,dbo.clearzero(RevisedStrength)) end as RevisedStrength            
	 from  MonitorOperationData.GasFacHourData   with (nolock)             
	 where pscode = @PSCode  and outputCode = @OutputCode   and MonitorTime between @StartDate and @EndDate             
)t                
left join MonitorOperationData.GasOutputHourData c on t.pscode = c.pscode and t.MonitorTime= c.MonitorTime and t.outputcode = c.outputcode            
where  t.MonitorTime between @StartMonitorTime and @EndMonitorTime            
union            
select distinct t.PSCode as PScode,convert(varchar,t.MonitorTime,120) as MonitorTime      
,case when c.RevisedFlow is null then '' else convert(varchar,c.RevisedFlow) end as GasOutputRevisedFlow       
,PollutantCode+'zgColor' as PollutantCode,            
(	case when (t.IsException = 0) then  'green'     
	when (t.IsException = 3)then 'red'     
	when (t.IsException = 1) then 'yellow'     
	else  'gray'            
	end
) as PollutantValue            
from        
(             
    select PSCode,outputcode,MonitorTime,PollutantCode as PollutantCode,RevisedStrength,IsException  
    from  MonitorOperationData.GasFacHourZsData  with (nolock)             
	where pscode = @PSCode  and outputCode = @OutputCode   and MonitorTime between @StartDate and @EndDate              
)t                
left join MonitorOperationData.GasOutputHourData c on t.pscode = c.pscode and t.MonitorTime= c.MonitorTime 
	and t.outputcode = c.outputcode            
where  t.MonitorTime between @StartMonitorTime and @EndMonitorTime              
order by   MonitorTime desc            
---------------------------------------------------------            
---------------------------------------------------------            
--返回总行数            
select @rowCount as Rows              
---------------------------------------------------------            
end            
else if @OutputType=3            
begin            
select @rowCount = count(MonitorTime)  from            
(                  
    select distinct MonitorTime from MonitorOperationData.WaterInputHourData    with (nolock)            
    where pscode = @PSCode and inputcode=@OutputCode  and MonitorTime between @StartDate and @EndDate            
    union             
    select distinct MonitorTime from MonitorOperationData.WaterinputFacHourData   with (nolock)             
    where pscode = @PSCode and Inputcode=@OutputCode and MonitorTime between @StartDate and @EndDate            
) a            
---------------------------------------------------------              
---------------------------------------------------------            
  --计算开始结束时间            
select @StartMonitorTime = min(MonitorTime),@EndMonitorTime = max(MonitorTime) from            
(      
 select MonitorTime,row_number() over(order by MonitorTime desc) rowid            
 from       
 (             
	  select distinct MonitorTime from             
	  MonitorOperationData.WaterInputHourData   with (nolock)         
	  where pscode = @PSCode  and Inputcode=@OutputCode and MonitorTime between @StartDate and @EndDate             
	  union            
	  select distinct MonitorTime from          
	  MonitorOperationData.WaterinputFacHourData     with (nolock)          
	  where pscode = @PSCode and Inputcode=@OutputCode  and MonitorTime between @StartDate and @EndDate             
 ) t              
)s            
where rowid between @startNumber  and @endNumber            
--返回非主键列列数            
select 2*count('1') as PIVORowNO from            
(             
	select PollutantCode as PollutantCode  from  MonitorOperationData.WaterinputFacHourData   with (nolock)              
	where pscode = @PSCode and Inputcode=@OutputCode  and MonitorTime between @StartMonitorTime and @EndMonitorTime            
	group by PollutantCode               
)t              
               
            
---------------------------------------------------------            
---------------------------------------------------------            
--返回数据结果            
select t.PSCode as PScode,convert(varchar,t.MonitorTime,120) as MonitorTime      
,case when b.RevisedFlow is null then '' else convert(varchar,b.RevisedFlow) end as WaterInputFlow      
,PollutantCode+'i' as PollutantCode      
,case when t.RevisedStrength is null then '' else     
(    
	 case when PollutantCode='001' then convert(varchar, dbo.clearzero(ROUND(t.RevisedStrength,1)))     
	 when PollutantCode='011' then convert(varchar,dbo.clearzero(ROUND(t.RevisedStrength,1)))    
	 when PollutantCode='060' then convert(varchar,dbo.clearzero(ROUND(t.RevisedStrength,2)))    
	 else     
	 convert(varchar,dbo.clearzero(t.RevisedStrength))    
	 end    
)    
 end as PollutantValue            
from        
(             
    select PSCode,inputcode as outputcode,MonitorTime,PollutantCode as PollutantCode,
    dbo.ClearZero(RevisedStrength) as  RevisedStrength 
    from MonitorOperationData.WaterinputFacHourData   with (nolock)            
	where pscode = @PSCode  and Inputcode=@OutputCode  and MonitorTime between @StartDate and @EndDate              
)t                
left join MonitorOperationData.WaterinputHourData b  with (nolock) on t.pscode = b.pscode and t.MonitorTime= b.MonitorTime 
and t.outputcode = b.inputcode                 
where  t.MonitorTime between @StartMonitorTime and @EndMonitorTime              
--union all            
--select t.PSCode as PScode,convert(varchar,t.MonitorTime,120) as MonitorTime,b.RevisedFlow as WaterInputRevisedFlow,PollutantCode+'iColor' as PollutantCode,            
--        (case when (t.IsException = 0) then  'green' when (t.IsException = 3)then 'red' when (t.IsException = 1) then 'yellow' else  'black'            
--       end)  as PollutantValue            
--from  (             
--    select PSCode,inputcode as outputcode,MonitorTime,PollutantCode as PollutantCode,RevisedStrength,IsException  from MonitorOperationData.WaterinputFacHourData             
--  where pscode = @PSCode  and Inputcode=@OutputCode  and MonitorTime between @StartDate and @EndDate              
--              
--      )t                
--      left join MonitorOperationData.WaterinputHourData b on t.pscode = b.pscode and t.MonitorTime= b.MonitorTime and t.outputcode = b.inputcode            
--            
--where  t.MonitorTime between @StartMonitorTime and @EndMonitorTime            
order by   MonitorTime desc            
---------------------------------------------------------            
---------------------------------------------------------            
--返回总行数            
select @rowCount as Rows              
---------------------------------------------------------            
     
end            
            
            
            
            
            
            
            
            
            
            
            
GO