-- =============================================
-- Author:		Marian Zaki
-- Create date: 29/11/2010
-- Description:	Checks for data validation 
-- =============================================
CREATE procedure [C6MartPeriodico].[Popola_S80_S129_EvoluzioneCTVPatrimonio_percentuale_Fase3]
-- Add the parameters for the stored procedure here
AS
begin
DECLARE @DataCongelamento DATETIME  
 --DECLARE @DettMovPrec money  
 --DECLARE @DettMovCorr money  
 --DECLARE @AppoDettMovPrec varchar(50)  
 --DECLARE @AppoDettMovCorr varchar(50)  
 --DECLARE @NumAsterischiPrec int  
 --DECLARE @NumAsterischiCorr int  
 --SET @NumAsterischiPrec = 0  
 --SET @NumAsterischiCorr = 0  
 SET @DataCongelamento = C6MartPeriodico.getDataCongelamento()  
drop table if exists c6martperiodico.TB_Popola_S80_S129_EvoluzioneCTVPatrimonio_percentuale_Fase3
SELECT   
 rete,  
 cod_fiscale,  
   3 AS Fase,  
   CASE  
    WHEN @DataCongelamento IS NULL THEN GETDATE()  
    ELSE @DataCongelamento  
   END AS DataFase,  
   --Get the sum Operazioni in corso   
   --sum((isnull(IMPORTO,0))) as Partita_viaggiante,  
   0 as Partita_viaggiante,  
   sum(case Upper(id_area) when 'EXT' then ctv else 0 end ) as CTV_EXT,  
   sum(case Upper(id_area) when 'INV' then ctv else 0 end ) as CTV_INV,  
   sum(case when Upper(id_area) like 'PRE%' then ctv else 0 end ) as CTV_PRE,  
   --sum(case Upper(id_area) when 'PRE' then ctv else 0 end ) as CTV_PRE,  
   sum(case Upper(id_area) when 'RIS' then ctv else 0 end ) as CTV_RIS,     
   sum(case Upper(id_area) when 'LIQ' then ctv else 0 end ) as CTV_LIQ, 
   -- aggiunta del 19-10-2020 per il calcolo delle percentuali delle varie aree della piramide
 --  	case
	--	when (sum(case Upper(id_area) when 'EXT' then ctv else 0 end )+ sum(case Upper(id_area) when 'INV' then ctv else 0 end )+ sum(case when Upper(id_area) like 'PRE%' then ctv else 0 end ) +  
	--		sum(case Upper(id_area) when 'RIS' then ctv else 0 end )+ sum(case Upper(id_area) when 'LIQ' then ctv else 0 end )) = 0
	--		then 0.0 
	--		else 
	--			sum(case Upper(id_area) when 'EXT' then ctv else 0 end ) / (sum(case Upper(id_area) when 'EXT' then ctv else 0 end )+ sum(case Upper(id_area) when 'INV' then ctv else 0 end )+ sum(case when Upper(id_area) like 'PRE%' then ctv else 0 end ) +  
	--			sum(case Upper(id_area) when 'RIS' then ctv else 0 end )+ sum(case Upper(id_area) when 'LIQ' then ctv else 0 end )) * 100 
	--end as PERC_EXT,
	--case 
	--	when (sum(case Upper(id_area) when 'EXT' then ctv else 0 end )+ sum(case Upper(id_area) when 'INV' then ctv else 0 end )+ sum(case when Upper(id_area) like 'PRE%' then ctv else 0 end ) +  
	--		sum(case Upper(id_area) when 'RIS' then ctv else 0 end )+ sum(case Upper(id_area) when 'LIQ' then ctv else 0 end )) = 0
	--	then 0.0
	--	else
	--		sum(case Upper(id_area) when 'INV' then ctv else 0 end ) / (sum(case Upper(id_area) when 'EXT' then ctv else 0 end )+ sum(case Upper(id_area) when 'INV' then ctv else 0 end )+ sum(case when Upper(id_area) like 'PRE%' then ctv else 0 end ) +  
	--		sum(case Upper(id_area) when 'RIS' then ctv else 0 end )+ sum(case Upper(id_area) when 'LIQ' then ctv else 0 end )) * 100
	--end as PERC_INV,
	--case
	--	when (sum(case Upper(id_area) when 'EXT' then ctv else 0 end )+ sum(case Upper(id_area) when 'INV' then ctv else 0 end )+ sum(case when Upper(id_area) like 'PRE%' then ctv else 0 end ) +  
	--		  sum(case Upper(id_area) when 'RIS' then ctv else 0 end )+ sum(case Upper(id_area) when 'LIQ' then ctv else 0 end )) = 0
	--	then
	--		0.0
	--	else
	--		sum(case when Upper(id_area) like 'PRE%' then ctv else 0 end ) / (sum(case Upper(id_area) when 'EXT' then ctv else 0 end )+ sum(case Upper(id_area) when 'INV' then ctv else 0 end )+ sum(case when Upper(id_area) like 'PRE%' then ctv else 0 end ) +  
	--		sum(case Upper(id_area) when 'RIS' then ctv else 0 end )+ sum(case Upper(id_area) when 'LIQ' then ctv else 0 end )) * 100
	--end as PERC_PRE,
	--case
	--	when (sum(case Upper(id_area) when 'EXT' then ctv else 0 end )+ sum(case Upper(id_area) when 'INV' then ctv else 0 end )+ sum(case when Upper(id_area) like 'PRE%' then ctv else 0 end ) +  
	--			sum(case Upper(id_area) when 'RIS' then ctv else 0 end )+ sum(case Upper(id_area) when 'LIQ' then ctv else 0 end )) = 0
	--	then
	--		0.0
	--	else
	--	   sum(case Upper(id_area) when 'RIS' then ctv else 0 end ) / (sum(case Upper(id_area) when 'EXT' then ctv else 0 end )+ sum(case Upper(id_area) when 'INV' then ctv else 0 end )+ sum(case when Upper(id_area) like 'PRE%' then ctv else 0 end ) +  
	--	   sum(case Upper(id_area) when 'RIS' then ctv else 0 end )+ sum(case Upper(id_area) when 'LIQ' then ctv else 0 end )) * 100
	--end as PERC_RIS,
	--case
	--	when (sum(case Upper(id_area) when 'EXT' then ctv else 0 end )+ sum(case Upper(id_area) when 'INV' then ctv else 0 end )+ sum(case when Upper(id_area) like 'PRE%' then ctv else 0 end ) +  
	--		sum(case Upper(id_area) when 'RIS' then ctv else 0 end )+ sum(case Upper(id_area) when 'LIQ' then ctv else 0 end )) = 0
	--	then
	--		0.0
	--	else
	--	   sum(case Upper(id_area) when 'LIQ' then ctv else 0 end ) / (sum(case Upper(id_area) when 'EXT' then ctv else 0 end )+ sum(case Upper(id_area) when 'INV' then ctv else 0 end )+ sum(case when Upper(id_area) like 'PRE%' then ctv else 0 end ) +  
	--	   sum(case Upper(id_area) when 'RIS' then ctv else 0 end )+ sum(case Upper(id_area) when 'LIQ' then ctv else 0 end )) * 100 
	--end as PERC_LIQ,
  --isnull([C6MartPeriodico].[GetPercentualePiramideMod](cod_fiscale ,RETE ,'Ext'),0.00) as PERC_EXT,
  --isnull([C6MartPeriodico].[GetPercentualePiramideMod](cod_fiscale ,RETE ,'Inv'),0.00) as PERC_INV,
  --isnull([C6MartPeriodico].[GetPercentualePiramideMod](cod_fiscale ,RETE ,'Pre'),0.00) as PERC_PRE,
  --isnull([C6MartPeriodico].[GetPercentualePiramideMod](cod_fiscale ,RETE ,'Ris'),0.00) as PERC_RIS,
  --isnull([C6MartPeriodico].[GetPercentualePiramideMod](cod_fiscale ,RETE ,'Liq'),0.00) as PERC_LIQ,
	    -- fine aggiunta del 19-10-2020
   sum(case Upper(id_area) when 'EXT' then ctv else 0 end )+   
   sum(case Upper(id_area) when 'INV' then ctv else 0 end )+   
   sum(case when Upper(id_area) like 'PRE%' then ctv else 0 end ) +  
   sum(case Upper(id_area) when 'RIS' then ctv else 0 end )+  
   sum(case Upper(id_area) when 'LIQ' then ctv else 0 end ) AS CTV_RA,  
   sum(case Upper(id_area) when 'NA' then ctv else 0 end ) as CTV_NA,  
   sum(case Upper(id_area) when 'EXT' then ctv else 0 end )+   
   sum(case Upper(id_area) when 'INV' then ctv else 0 end )+   
   --sum(case Upper(id_area) when 'PRE' then ctv else 0 end )+   
   sum(case when Upper(id_area) like 'PRE%' then ctv else 0 end ) +  
   sum(case Upper(id_area) when 'RIS' then ctv else 0 end )+  
   sum(case Upper(id_area) when 'LIQ' then ctv else 0 end )+   
   sum(case Upper(id_area) when 'NA' then ctv else 0 end ) AS CTV_RF,      
   --CTV_CC = (Case WHEN sum(case Upper(id_area) when 'CC' then ctv  else 0 end ) < 0 THEN sum(case Upper(id_area) when 'CC' then ctv  else 0 end) ELSE 0 END),  
   --sum(Case When (Upper(id_area) = 'CC' and cast(ctv As Numeric)<0) Then ctv else 0 end) AS CTV_CC,  
   sum(Case Upper(id_area) when 'CC' then ctv else 0 end) AS CTV_CC, 
--INIZIO INTERVENTI OMNIA
	-- 20181206 MIOFOGLIO
	sum(case when upper(id_area) = 'SELF' and cod_interno = 'XY' then ctv else 0 end) as CTV_SELF,
	--sum(Case Upper(id_area) when 'self' then ctv else 0 end) AS CTV_SELF,
	-- fine 20181206 MIOFOGLIO
	  -- aggiunta del 19-10-2020 per il calcolo delle percentuali delle varie aree della piramide
--FINE INTERVENTI OMNIA  
   sum(case Upper(id_area) when 'EXT' then ctv else 0 end )+   
   sum(case Upper(id_area) when 'INV' then ctv else 0 end )+   
   sum(case when Upper(id_area) like 'PRE%' then ctv else 0 end )+  
   sum(case Upper(id_area) when 'RIS' then ctv else 0 end )+  
   sum(case Upper(id_area) when 'LIQ' then ctv else 0 end )+   
   sum(case Upper(id_area) when 'NA' then ctv else 0 end ) +  
--INIZIO INTERVENTI OMNIA  
     sum(Case Upper(id_area) when 'CC' then ctv else 0 end) + sum(Case Upper(id_area) when 'self' then ctv else 0 end) AS TotalePatrimonio  
--FINE INTERVENTI OMNIA  
 --ToDo Fc    
   --,(CASE WHEN @UltimoInvio = 0 AND @DATA_FOTO < '20111001' THEN 'n.d.' ELSE @AppoDettMovCorr END) as DettMov  
   --,(CASE WHEN @UltimoInvio = 0 AND @DATA_FOTO < '20111001' THEN 0 ELSE @NumAsterischiCorr END) as NumeroAsterischiNota  
  ,null as DettMovS80  
  ,null as DettMovS129  
  ,0 NumeroAsterischiNota  
  ,0 as sommaproventi
  -- 20181206 MIOFOGLIO
	,sum(case when upper(id_area) = 'SELF' and cod_interno = 'FO' then ctv else 0 end) as CTV_SELF_FOGLIO
  -- fine 20181206 MIOFOGLIO
  into #temp
 FROM   
  C6MartPeriodico.PATRIMONIO_BF   
 group by rete,cod_fiscale   
 select t.rete,t.cod_fiscale,t.Fase,t.DataFase,t.Partita_viaggiante,t.CTV_EXT,t.CTV_INV,
 t.CTV_PRE,t.CTV_RIS,t.CTV_LIQ,
 p.PERC_EXT,p.PERC_INV,p.PERC_PRE,p.PERC_RIS,p.PERC_LIQ,t.CTV_RA,t.CTV_NA,
 t.CTV_RF,t.CTV_CC,t.CTV_SELF,t.TotalePatrimonio,t.DettMovS80,t.DettMovS129,t.NumeroAsterischiNota,t.sommaproventi,t.CTV_SELF_FOGLIO
 into c6martperiodico.TB_Popola_S80_S129_EvoluzioneCTVPatrimonio_percentuale_Fase3
 from #temp t inner join C6MartPeriodico.tb_patrimonio_bf_percentuale_piramide p on t.RETE+t.COD_FISCALE=p.rete+p.cod_fiscale
-- select * from C6MartPeriodico.TB_Popola_S80_S129_EvoluzioneCTVPatrimonio_percentuale_Fase_tre
--select * from C6MartPeriodico.tb_patrimonio_bf_percentuale_piramide
END