-- Stored procedure  
-- Stored procedure  
-- =============================================  
-- Author:  <Author,,Name>  
-- Create date: <Create Date,,>  
-- Description: <Description,,>  
-- =============================================  
-- [C6MartPeriodico].[Popola_S80_S129_EvoluzioneCTVPatrimonio]'F','GSTGPL59P03B586G'  
CREATE procedure [C6MartPeriodico].[Popola_S80_S129_EvoluzioneCTVPatrimonio_20170724_preCR_fasi1e2]  
AS  
BEGIN  
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.  
 SET NOCOUNT ON;  
 -- Recupero data congelamento SEI  
 DECLARE @DataCongelamento DATETIME  
 DECLARE @DettMovPrec money  
 DECLARE @DettMovCorr money  
 DECLARE @AppoDettMovPrec varchar(50)  
 DECLARE @AppoDettMovCorr varchar(50)  
 DECLARE @NumAsterischiPrec int  
 DECLARE @NumAsterischiCorr int  
 DECLARE @DATA_FOTO datetime  
 DECLARE @UltimoInvio int  
 SET @NumAsterischiPrec = 0  
 SET @NumAsterischiCorr = 0  
 SET @DataCongelamento = C6MartPeriodico.getDataCongelamento()  
   -- INSERIMENTO BLOCCO PER MONITORAGGIO CHIUSO O NON ESISTENTE  
 --   -- Inserimento Dati Scirocco  
 --   SELECT @DettMovPrec = DETT_MOV_PREC, @DettMovCorr = DETT_MOV_CORR  
 --FROM C6MartPeriodico.DettaglioMovimenti  
 --WHERE  
 -- RETE = @Rete  
 --AND  
 -- COD_FIS = @CodiceFiscale  
 --IF @DettMovPrec is null  
 -- SET @AppoDettMovPrec = 'n.d.'  
 --ELSE  
 -- SET @AppoDettMovPrec = REPLACE(CAST(@DettMovPrec as varchar(50)), '.', ',')  
 --IF @DettMovCorr is null  
 -- SET @AppoDettMovCorr = 'n.d.'  
 --ELSE  
 -- SET @AppoDettMovCorr = REPLACE(CAST(@DettMovCorr as varchar(50)), '.', ',')  
 ----query per asterischi situazione precedente  
 --IF EXISTS(SELECT * FROM [C6StagingPeriodico].[WSEICS]  
 -- WHERE CODPROD IN ('37', 'GP') AND DTTRIM = c6martperiodico.getprevioustrimestre(getdate(),0)  
 -- AND RETE = @Rete AND CODFIS = @CodiceFiscale)  
 -- SET @NumAsterischiPrec = 1  
 --IF EXISTS(SELECT * FROM C6StagingPeriodico.ST_MON  
 -- WHERE RETE = @Rete AND CODFIS = @CodiceFiscale)  
 -- SET @NumAsterischiPrec = 1  
 -------------------------------------------  
 ----query per asterischi situazione corrente  
 --IF EXISTS(SELECT * FROM C6MartPeriodico.PATRIMONIO_BF  
 -- WHERE COD_INTERNO IN ('37', 'GP')  
 -- AND RETE = @Rete AND COD_FISCALE = @CodiceFiscale)  
 -- SET @NumAsterischiCorr = 1  
 -------------------------------------------  
 --SELECT @DATA_FOTO = DATA_FOTO FROM C6MartPeriodico.MONITORAGGIO_INIZIALE a  
 --WHERE a.Rete = @Rete  
 --AND a.COD_FISCALE = @CodiceFiscale  
 --SELECT @UltimoInvio = Count(*) FROM C6MartPeriodico.MONITORAGGIO_REPORT a  
 --WHERE a.Rete = @Rete  
 --AND a.COD_FISCALE = @CodiceFiscale  
truncate table C6MartPeriodico.TB_S80_S129_EvoluzioneCTVPatrimonio  
 -- Attivazione monitoraggio  
 insert into C6MartPeriodico.TB_S80_S129_EvoluzioneCTVPatrimonio  
 SELECT  
  INI.RETE,  
  INI.Cod_Fiscale,  
  1 AS Fase,  
  isnull(INI.DATA_FOTO,0) AS DataFase,  
  ISNULL(INI.PARTITA_VIAGGIANTE,0) AS Partita_viaggiante,  
  isnull(INI.CTV_EXT,0) as CTV_EXT,  
  isnull(INI.CTV_INV,0) as CTV_INV,  
  isnull(INI.CTV_PRE,0) as CTV_PRE,  
  isnull(INI.CTV_RIS,0) as CTV_RIS,  
  isnull(INI.CTV_LIQ,0) as CTV_LIQ,  
  isnull(INI.CTV_EXT,0) + isnull(INI.CTV_INV,0) + isnull(INI.CTV_PRE,0) + isnull(INI.CTV_RIS,0) + isnull(INI.CTV_LIQ,0) AS RisorseAllocate,  
  isnull(INI.CTV_NA,0) AS RisorseNonAllocate,  
  isnull(INI.CTV_EXT,0) + isnull(INI.CTV_INV,0) + isnull(INI.CTV_PRE,0) + isnull(INI.CTV_RIS,0) + isnull(INI.CTV_LIQ,0) + isnull(INI.CTV_NA,0) AS RisorseFinanziarie,  
  isnull(INI.CTV_CC,0) AS Contocorrente,  
--INIZIO INTERVENTI OMNIA  
  isnull(INI.CTVSELF,0) AS ctvself,  
  isnull(INI.CTV_EXT,0) + isnull(INI.CTV_INV,0) + isnull(INI.CTV_PRE,0) + isnull(INI.CTV_RIS,0) + isnull(INI.CTV_LIQ,0) + isnull(INI.CTV_NA,0) + isnull(INI.CTV_CC,0) + isnull(INI.CTVSELF,0) + ISNULL(INI.PARTITA_VIAGGIANTE,0) AS TotalePatrimonio    
--FINE INTERVENTI OMNIA  
  ,null as DettMovS80  
  ,null as DettMovS129  
  ,0 as NumeroAsterischiNota  
  ,0 as sommaproventi  
 FROM  
  C6MartPeriodico.MONITORAGGIO_INIZIALE INI  
        left outer join  
        C6MartPeriodico.MONITORAGGIO_REPORT rep  
        on   
        REP.RETE=INI.RETE  
        AND   
        REP.COD_FISCALE=INI.COD_FISCALE  
 WHERE  
     INI.DATA_FOTO < ISNULL(REP.DATA_INVIO,'2999-12-31')  
 -- INI.RETE = @Rete AND  
 -- INI.COD_FISCALE = @CodiceFiscale  
    UNION ALL  
 -- Precedente invio report  
  SELECT  
   INI.RETE,  
  INI.Cod_Fiscale,  
  2 AS Fase,  
  rep.DATA_INVIO AS DataFase,  
  ISNULL(rep.PARTITA_VIAGGIANTE,0) AS Partita_viaggiante,  
  rep.CTV_EXT,  
  rep.CTV_INV,  
  rep.CTV_PRE,  
  rep.CTV_RIS,  
  rep.CTV_LIQ,  
  rep.CTV_EXT + rep.CTV_INV + rep.CTV_PRE + rep.CTV_RIS + rep.CTV_LIQ AS RisorseAllocate,    
  rep.CTV_NA AS RisorseNonAllocate,  
  rep.CTV_EXT + rep.CTV_INV + rep.CTV_PRE + rep.CTV_RIS + rep.CTV_LIQ + rep.CTV_NA AS RisorseFinanziarie,    
  rep.CTV_CC AS Contocorrente,  
  --Contocorrente = CASE WHEN CAST(CTV_CC AS NUMERIC(20,4)) < 0 THEN CTV_CC ELSE 0 END,  
--INIZIO INTERVENTI OMNIA  
  isnull(rep.CTV_SELF,0) AS CTV_SELF,  
  rep.CTV_EXT + rep.CTV_INV + rep.CTV_PRE + rep.CTV_RIS + rep.CTV_LIQ + rep.CTV_NA + rep.CTV_CC + ISNULL(rep.CTV_SELF,0) + ISNULL(rep.PARTITA_VIAGGIANTE,0) AS TotalePatrimonio      
--FINE INTERVENTI OMNIA  
--ToDo Fc    
--  ,(CASE WHEN @DATA_FOTO < '20111001' THEN 'n.d.' ELSE @AppoDettMovPrec END) as DettMov  
--  ,(CASE WHEN @DATA_FOTO < '20111001' THEN 0 ELSE @NumAsterischiPrec END) as NumeroAsterischiNota  
  ,null as DettMovS80  
  ,null as DettMovS129  
  ,0 as NumeroAsterischiNota  
      ,0 as sommaproventi  
 FROM  
  C6MartPeriodico.MONITORAGGIO_INIZIALE INI  
        left outer join  
        C6MartPeriodico.MONITORAGGIO_REPORT rep  
        on   
        REP.RETE=INI.RETE  
        AND   
        REP.COD_FISCALE=INI.COD_FISCALE  
 WHERE  
        INI.DATA_FOTO < ISNULL(REP.DATA_INVIO,cast('29991231' as datetime))  
  and ISNULL(DATA_INVIO,cast('29991231' as datetime))<@datacongelamento  
 UNION ALL  
 -- Situazione corrente  
 --All values will be read from DB and passed to code, no value will be calculated on code  
 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,  
   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  
   sum(Case Upper(id_area) when 'self' then ctv else 0 end) AS CTV_SELF,     
--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  
 FROM   
  C6MartPeriodico.PATRIMONIO_BF   
 group by rete,cod_fiscale   
select count(*) from C6MartPeriodico.TB_S80_S129_EvoluzioneCTVPatrimonio  
update s80  
set DettMovS80 = case when dettmov.DETT_MOV_PREC is null   
  then 'n.d.'  
   else REPLACE(CAST(dettmov.DETT_MOV_PREC as varchar(50)), '.', ',')   
end ,  
DettMovS129 = case when dettmov.DETT_MOV_PREC is null   
  then 'n.d.'  
   else REPLACE(CAST(dettmov.DETT_MOV_PREC as varchar(50)), '.', ',')   
end   
from C6MartPeriodico.TB_S80_S129_EvoluzioneCTVPatrimonio s80  
inner join C6MartPeriodico.DettaglioMovimenti DettMov  
on  s80.rete = dettmov.rete  and dettmov.COD_FIS = s80.COD_FIScale  
where s80.fase=2  
update s80  
set DettMovS80 = case when dettmov.DETT_MOV_CORR is null   
  then 'n.d.'  
   else REPLACE(CAST(dettmov.DETT_MOV_CORR as varchar(50)), '.', ',')   
end ,  
DettMovS129 = case when dettmov.DETT_MOV_TRIMESTRE_PREC is null   
  then 'n.d.'  
   else REPLACE(CAST(dettmov.DETT_MOV_TRIMESTRE_PREC as varchar(50)), '.', ',')   
end   
from C6MartPeriodico.TB_S80_S129_EvoluzioneCTVPatrimonio s80  
inner join C6MartPeriodico.DettaglioMovimenti DettMov  
on  s80.rete = dettmov.rete  and dettmov.COD_FIS = s80.COD_FIScale  
where s80.fase=3  
--update S129  
--set DettMovS129 = case when dettmov.DETT_MOV_TRIMESTRE_PREC is null   
--  then 'n.d.'  
--   else REPLACE(CAST(dettmov.DETT_MOV_TRIMESTRE_PREC as varchar(50)), '.', ',')   
--end   
--from C6MartPeriodico.TB_S80_S129_EvoluzioneCTVPatrimonio S129  
--inner join C6MartPeriodico.DettaglioMovimenti DettMov  
--on  S129.rete = dettmov.rete  and dettmov.COD_FIS = S129.COD_FIScale  
--where S129.fase=3  
declare @TimecPRec  varchar(8)  
set @TimecPRec =  c6martperiodico.getprevioustrimestre(getdate(),0)  
update s80x  
set NumeroAsterischiNota =  
case   
when ws.CODFIS is null  
  then   0  
else 1   
end   
FROM C6MartPeriodico.TB_S80_S129_EvoluzioneCTVPatrimonio s80x  
left join [C6StagingPeriodico].[WSEICS] ws  
on s80x.rete = ws.rete  and ws.CODFIS = s80x.COD_FIScale  
WHERE CODPROD IN ('37', 'GP') AND DTTRIM = @TimecPRec AND s80x.fase = 2  
update s80x  
set NumeroAsterischiNota =  
case   
when ws.COD_FISCALE is null  
  then   0  
else 1   
end   
FROM C6MartPeriodico.TB_S80_S129_EvoluzioneCTVPatrimonio s80x  
left join C6MartPeriodico.PATRIMONIO_BF ws  
on s80x.rete = ws.rete  and ws.COD_FISCALE = s80x.COD_FIScale  
WHERE  COD_INTERNO IN ('37', 'GP') AND s80x.fase = 3  
SELECT * FROM C6MartPeriodico.PATRIMONIO_BF  
 -- WHERE COD_INTERNO IN ('37', 'GP')  
 -- AND RETE = @Rete AND COD_FISCALE = @CodiceFiscale  
SELECT rete,Cod_Fiscale,sum(IMPORTO) sommaProventi  
into #SommaProventi  
FROM [C6MartPeriodico].proventi   
--where rete='F' and Cod_Fiscale='DLLLCU62T60G408A'  
group by  rete,Cod_Fiscale   
update s80x  
set s80x.sommaproventi = sp.sommaproventi  
FROM C6MartPeriodico.TB_S80_S129_EvoluzioneCTVPatrimonio s80x  
left join  #SommaProventi sp  
on s80x.rete = sp.rete  and sp.Cod_Fiscale = s80x.COD_FIScale  
END