-- Stored procedure
-- =============================================
-- Author:		Paolo Giovanetti
-- Create date: 17 febbraio 2009
-- Description: 
-- Aggiunta GESTIONE EligoTitoli L192 (FCianfa, 26/11/2014)
-- =============================================
--select * from c6martperiodico.patrimonio_bf where cod_fiscale = 'BNDCRN60H52A181P'
-- [C6MartPeriodico].[PL_S43DettaglioProdottiBF] 'F','BNDCRN60H52A181P'
CREATE   procedure [C6MartPeriodico].[PL_S43DettaglioProdottiBF] --'S','MNSLNZ44A58F351Y'
 -- Add the parameters for the stored procedure here
 @Rete char(1), 
 @CodiceFiscale varchar(16)
AS
BEGIN
-------------------------------------------------------------------------------------
--INIZIO INTERVENTI OMNIA QUIII
	DECLARE @TOTSELFLIQ AS INT
	SELECT @TOTSELFLIQ = COUNT(*)
	FROM C6MARTPERIODICO.PATRIMONIO_BF
	WHERE PATRIMONIO_BF.RETE = @Rete
		AND PATRIMONIO_BF.COD_FISCALE = @CodiceFiscale
		AND ISIN in('EURO00000009', 'EURO10000007')
		AND ID_AREA = 'LIQ'
	DECLARE @TOTLIQ AS INT
	SELECT @TOTLIQ = COUNT(*)
	FROM C6MARTPERIODICO.PATRIMONIO_BF
	WHERE PATRIMONIO_BF.RETE = @Rete
		AND PATRIMONIO_BF.COD_FISCALE = @CodiceFiscale
		AND ID_AREA = 'LIQ'
--FINE INTERVENTI OMNIA QUIII
--------------------------------------------------------------------------------------
SELECT
--distinct
CASE 
	WHEN AREA.ID_AREA = 'Pre1' OR AREA.ID_AREA = 'Pre2' THEN 'Pre' 
	ELSE AREA.ID_AREA 
END AS needarea,
CASE 
	WHEN AREA.ID_AREA = 'Pre1' OR AREA.ID_AREA = 'Pre2' THEN 'Previdenza'
	WHEN AREA.ID_AREA = 'Na'  THEN 'Non allocate'
	ELSE AREA.NOME_AREA 
END AS needareades,
CASE 
	WHEN AREA.ID_AREA = 'Pre1' OR AREA.ID_AREA = 'Pre2' THEN  3 
	ELSE AREA.ORDINAMENTO 
END AS areasortorder,
CASE 
--WHEN AREA.ID_AREA = 'Pre' THEN 'Default'
	WHEN AREA.ID_AREA = 'Inv' AND PATRBF.NOME_PROGETTO IS NULL THEN 'Prodotti non associati a progetti'
	-- 20161219 - mod per far uscire il nome progetto sulle risorse na
	WHEN AREA.ID_AREA = 'Na'  THEN 'Default'
	-- fine 20161219 - mod per far uscire il nome progetto sulle risorse na
	ELSE ltrim(rtrim(PATRBF.NOME_PROGETTO))
END AS nome_progetto,
PATRBF.CHIAVE_PROGETTO AS ORDINAMENTO_PROGETTO,
SUM(PATRBF.CTV) OVER (PARTITION BY AREA.ID_AREA) AS somma_controval_needarea,
SUM(PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) OVER (PARTITION BY AREA.ID_AREA) AS versato_needarea,
CASE 
	WHEN COUNT(CASE WHEN (PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY AREA.ID_AREA) > 0 THEN 'n.d.' 
	ELSE NULL 
END as versato_needareaString,
SUM(MONIT.MINUS_PLUSVALENZA) OVER (PARTITION BY AREA.ID_AREA) AS MinusPlusValenza_needarea,
CASE 
	WHEN COUNT( CASE 
					WHEN MONIT.MINUS_PLUSVALENZA IS NULL THEN 1 
					ELSE NULL 
				END ) OVER (PARTITION BY AREA.ID_AREA) > 0 THEN 'n.d.' 
	ELSE NULL 
END AS MinusPlusValenza_needareaString,
CASE 
	WHEN AREA.ID_AREA = 'Inv' THEN SUM(PATRBF.CTV) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.CHIAVE_PROGETTO) 
	ELSE NULL	
END	AS somma_controval_need_prog,
CASE 
	WHEN AREA.ID_AREA = 'Inv' THEN SUM(PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.CHIAVE_PROGETTO) 
	ELSE NULL
END	AS versato_need_prog,
CASE 
	WHEN AREA.ID_AREA = 'Inv' AND COUNT(CASE WHEN (PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.CHIAVE_PROGETTO) > 0 
		THEN 'n.d.'
	ELSE NULL
END	AS versato_need_progString,
CASE 
	WHEN AREA.ID_AREA = 'Inv' THEN SUM(MONIT.MINUS_PLUSVALENZA) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.CHIAVE_PROGETTO) 
	ELSE NULL
END	AS MinusPlusValenza_need_prog,
CASE 
	WHEN AREA.ID_AREA = 'Inv' AND COUNT(CASE WHEN MONIT.MINUS_PLUSVALENZA IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY AREA.ID_AREA, PATRBF.NOME_PROGETTO, PATRBF.CHIAVE_PROGETTO) > 0	
		THEN 'n.d.'
	ELSE NULL
END	AS MinusPlusValenza_need_progString,
CASE 
	WHEN AREA.ID_AREA = 'Inv' THEN RISCHIO_AGGR_PROG.VAR_PERC_PTF
	ELSE NULL
END	AS somma_var_need_prog,
CASE 
	WHEN AREA.ID_AREA = 'Inv' THEN RISCHIO_AGGR_PROG.COPERTURA
	ELSE NULL
END	AS somma_copertura_need_prog,
RISCHIO_AGGR_AREA.VAR_PERC_PTF AS var_needarea,
CASE 
	WHEN ISNULL(SUM(RISCHIO_AGGR_AREA.COPERTURA) OVER (PARTITION BY AREA.ID_AREA),0.00) = 0.00 
		THEN 'n.c.' 
--------------------------------------------------------------
--INIZIO INTERVENTI OMNIA QUIII				
	WHEN @TOTSELFLIQ = @TOTLIQ and AREA.ID_AREA = 'LIQ' 
		THEN 'n.c.' 
--FINE INTERVENTI OMNIA QUIII
--------------------------------------------------------------			
	ELSE NULL 
END AS var_needareaString,
CAST(ISNULL(RISCHIO_AGGR_AREA.COPERTURA,100) AS DECIMAL (5,2)) AS copertura_needarea,
	CASE 
	WHEN (ISNULL(RISCHIO_AGGR_AREA.copertura,100) < 100) 
		--THEN '(*) Grado di copertura: ' + REPLACE(CAST(CAST(RISCHIO_AGGR_AREA.COPERTURA AS DECIMAL (5,2)) AS VARCHAR),'.',',') + '%'  
		THEN 'Grado di copertura: ' + REPLACE(CAST(CAST(RISCHIO_AGGR_AREA.COPERTURA AS DECIMAL (5,2)) AS VARCHAR),'.',',') + '%'  
	ELSE '' 
END AS copertura_needareaString,
RISCHIO_AGGR_PROG.VAR_PERC_PTF AS var_need_prog,
CASE 
	WHEN ISNULL(RISCHIO_AGGR_PROG.COPERTURA,0.00) = 0.00 THEN 'n.c.' 
	ELSE NULL 
END AS var_need_progString,
RISCHIO_AGGR_PROG.COPERTURA AS copertura_need_prog,
dbo.TOSHORTDATESTRING(PATRBF.DATA_SOTTOSCRIZIONE) AS dataSottoscrizione, 
PATRBF.ID_CONTRATTO AS ContrattoDossier, 
--V
CASE WHEN PATRBF.TIPO_PRODOTTO <> 'CC' THEN ANAGPROD.DESCR_PRODOTTO
	 ELSE ANAGPROD.DESCR_PRODOTTO+' '+SUBSTRING(COD_CC,4,2) + '/' + SUBSTRING(COD_CC,6,LEN(ANAG_CC.COD_CC))
END AS Descrizione,
--Bido to get Code_CC field
--NOT AT ALL ANAGPROD.DESCR_PRODOTTO + ' ' + (STUFF(substring(ANAG_CC.COD_CC  , 4 , len(ANAG_CC.COD_CC) - 3), 3 , 0, '/') ) AS Descrizione,
--End Bido
--ANAGPROD.DESCR_PRODOTTO AS Descrizione,
PATRBF.CTV AS Controvalore, 
PATRBF.CTV - MONIT.MINUS_PLUSVALENZA AS VersatoNetto, 
CASE WHEN (PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) IS NULL THEN 'n.d.' ELSE NULL END as VersatoNettoString,
MONIT.MINUS_PLUSVALENZA AS MinusPlusValenza, 
CASE WHEN (PATRBF.CTV - MONIT.MINUS_PLUSVALENZA) IS NULL THEN 'n.d.' ELSE NULL END as MinusPlusValenzaString,
--V nuova gestione cc
case when (patrbf.tipo_prodotto ='CC') then 0.00 
else RISCHIO_PROD.VAR_PERC_PTF
end AS VaRprodotto, 
--INIZIO INTERVENTI OMNIA
--CASE WHEN ISNULL(RISCHIO_PROD.Copertura,0.00) = 0.00 and patrbf.tipo_prodotto <>'CC' THEN 'n.c.' ELSE NULL END as varProdottoString,
CASE WHEN ISNULL(RISCHIO_PROD.Copertura,0.00) = 0.00 and patrbf.tipo_prodotto <>'CC' THEN 'n.c.' 
     WHEN patrbf.isin in('EURO00000009', 'EURO10000007') THEN 'n.c.'
ELSE NULL END as varProdottoString,
--FINE INTERVENTI OMNIA
--INIZIO INTERVENTI OMNIA
--case when (patrbf.tipo_prodotto ='CC') then 100.00
case when (patrbf.tipo_prodotto ='CC') or (patrbf.isin in('EURO00000009', 'EURO10000007')) then 100.00
--FINE INTERVENTI OMNIA
else RISCHIO_PROD.Copertura 
end AS Coperturaprodotto, 
PATRBF.REND_ANNO AS PerformanceYTD, 
PATRBF.REND_SOTTOSCRIZIONE AS performanceDS,
PARTVIA_DISINV AS partitaViaggiante,
CASE WHEN (ISNULL(RISCHIO_AGGR_AREA.copertura,100) < 100) THEN '*' ELSE '' END AS nota_needarea,
RISCHIO_PROD.CREDITRISK as creditrisk,
CASE
--INIZIO INTERVENTI OMNIA
--	WHEN ISNULL(RISCHIO_prod.COPERTURA,0.00) = 0.00 and patrbf.tipo_prodotto <> 'CC' THEN 'n.c.' 
	WHEN (ISNULL(RISCHIO_prod.COPERTURA,0.00) = 0.00 
	        and  patrbf.tipo_prodotto <> 'CC') 
	        or  (patrbf.isin in('EURO00000009', 'EURO10000007'))
	        THEN 'n.c.' 
--FINE INTERVENTI OMNIA	
	WHEN RISCHIO_PROD.CREDITRISK IS NULL THEN 'n.a.'
		ELSE NULL 
	END as creditriskstring,
--CASE 
--	WHEN RISCHIO_PROD.CREDITRISKEMIT IS NULL THEN 'n.a.'
--		ELSE cast(RISCHIO_PROD.CREDITRISKEMIT as varchar(20))
--	END as creditriskemit
	RISCHIO_PROD.CREDITRISKEMIT as creditriskemit,
	-- savingmap - Novembre 2019
	patrbf.tipo_prodotto as Tipo_Prodotto,
	--APC dicembre 2022
	apc.is_APC as isAPC
FROM 
C6MartPeriodico.PATRIMONIO_BF PATRBF
INNER JOIN 
C6MartPeriodico.ANAG_PRODOTTI ANAGPROD 
ON 
	ANAGPROD.COD_PRODOTTO = PATRBF.COD_PRODOTTO
INNER JOIN 
C6MartPeriodico.AREA_BISOGNO AREA 
ON 
	--LEFT(PATRBF.ID_AREA,3) = AREA.ID_AREA 
	(LEFT(PATRBF.ID_AREA,3) = AREA.ID_AREA and AREA.ID_AREA <>'self') or (PATRBF.ID_AREA='self' and  AREA.ID_AREA ='self')
LEFT JOIN 
C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_PROD 
ON 
	PATRBF.RETE= RISCHIO_PROD.RETE
	AND PATRBF.COD_FISCALE= RISCHIO_PROD.COD_FISCALE
	AND RISCHIO_PROD.COD_AGGREG = 
				CASE 
					WHEN (PATRBF.NOME_PROGETTO IS NOT NULL)
						THEN 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO+ 
						 '|' + isnull(cast(patrbf.CHIAVE_PROGETTO as varchar),'') + '|'
					when LEFT(PATRBF.ID_AREA,3)='Na' then 'RISFIN|NA|'
					ELSE 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' 
				END
--		END + PATRBF.POSITION_ID 
		+ PATRBF.POSITION_ID 
LEFT JOIN C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_AGGR_PROG
	ON PATRBF.RETE= RISCHIO_AGGR_PROG.RETE
	AND PATRBF.COD_FISCALE= RISCHIO_AGGR_PROG.COD_FISCALE
	AND RISCHIO_AGGR_PROG.COD_AGGREG = 'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) + '|' + PATRBF.NOME_PROGETTO
		+ '|' +  isnull(cast(patrbf.CHIAVE_PROGETTO as varchar),'')
LEFT JOIN C6MartPeriodico.RISCHIO_AGGREGATO RISCHIO_AGGR_AREA
	ON PATRBF.RETE= RISCHIO_AGGR_AREA.RETE
	AND PATRBF.COD_FISCALE= RISCHIO_AGGR_AREA.COD_FISCALE
	AND RISCHIO_AGGR_AREA.COD_AGGREG =   case 
		when LEFT(PATRBF.ID_AREA,3) = 'Na' then 'RISFIN|NA'
         else  'RISFIN|PIRAMIDE|' + LEFT(PATRBF.ID_AREA,3) 
		end
--	AND RISCHIO_AGGR_AREA.COD_AGGREG = 
--		CASE AREA.ID_AREA 
--			WHEN 'Na' THEN 'RISORSENONASSOCIATE' 
--			ELSE 'COMPLESSIVO|BF|' + LEFT(PATRBF.ID_AREA,3) 
--		END
LEFT JOIN
C6MartPeriodico.MONITORAGGIO_ATTUALE MONIT
ON
	PATRBF.RETE = MONIT.RETE
	AND PATRBF.COD_FISCALE = MONIT.COD_FISCALE
	AND LEFT(PATRBF.ID_AREA, 3) = MONIT.ID_AREA
	AND CASE WHEN PATRBF.NOME_PROGETTO IS NULL THEN 'XXX' ELSE PATRBF.NOME_PROGETTO END = MONIT.NOME_PROGETTO
	And patrbf.ordinamento_progetto=monit.ordinamento_progetto
--Bido to get Code_CC field
LEFT OUTER JOIN C6MartPeriodico.ANAG_CC
ON 
	ANAG_CC.COD_FISCALE = PATRBF.COD_FISCALE
	AND ANAG_CC.RETE = PATRBF.RETE
	AND cast(ANAG_CC.cod_cc as integer ) = substring(PATRBF.position_id,72,9)
		--aggiunta 18/12/2022 per APC103
left join C6MartPeriodico.WSEICC apc
on 
	apc.RETE=PATRBF.RETE
	and	apc.cod_fiscale=patrbf.cod_fiscale
	and PATRBF.ID_CONTRATTO = --apc.CODCONFPR
	(case when PATRBF.tipo_prodotto='CC' then apc.CODCONFC else apc.CODCONFPR end)
	--fine aggiunta 18/12/2022 per APC103
WHERE 1 = 1
	AND PATRBF.RETE = @Rete
	AND PATRBF.COD_FISCALE = @CodiceFiscale
    AND PATRBF.CTV <>0
ORDER BY 
	AREASORTORDER,CHIAVE_PROGETTO,nome_progetto, PATRBF.CTV DESC
END
--select top 1 * from c6martperiodico.patrimonio_bf where tipo_prodotto = 'CC'
--select SUBSTRING(COD_CC,4,2) + '/' + SUBSTRING(COD_CC,6,LEN(ANAG_CC.COD_CC)),ANAG_CC.COD_CC 
--from c6martperiodico.anag_cc