-- Stored procedure
-- =============================================
-- Author:		Simone D'Elia
-- Create date: Aprile 2022
-- Description: 
-- =============================================
-- [C6MartPeriodico].[PL_S182DettaglioProdottiESG] 'F', 'GLLGZN62T06H501I'
CREATE   procedure [C6MartPeriodico].[PL_S182DettaglioProdottiESG_bkp_20250228_Pre_ESG2024] --'F','GLLGZN62T06H501I'
 -- 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
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 = 'Inv' AND PATRBF.NOME_PROGETTO IS NULL THEN 'Prodotti non associati a progetti'
	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,
	case when ESG_E = 'SI' then 'Sì' else 'No' end as ESG_E,
    case when ESG_S = 'SI' then 'Sì' else 'No' end as ESG_S,
    case when ESG_G = 'SI' then 'Sì' else 'No' end as ESG_G,
    case when CatSFDR <> '' then esg.CatSFDR else '-' end as CatSFDR,
    case when ESGRating <> '' then esg.esgrating else '-' end as ESGRating,
    case when TrendESGRating <> '' then esg.TrendESGRating else '-' end as TrendESGRating,
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, 
--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,
--case when (patrbf.tipo_prodotto ='CC') or (patrbf.isin in('EURO00000009', 'EURO10000007')) then 100.00
--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 
--	WHEN ISNULL(RISCHIO_prod.COPERTURA,0.00) = 0.00 and patrbf.tipo_prodotto <> 'CC' THEN 'n.c.' 
--	WHEN RISCHIO_PROD.CREDITRISK IS NULL THEN 'n.a.'
--		ELSE NULL 
--	END as creditriskstring,
	--RISCHIO_PROD.CREDITRISKEMIT as creditriskemit,
	-- savingmap - Novembre 2019
	patrbf.tipo_prodotto as Tipo_Prodotto
		,CASE
           WHEN CAST(NEWID() AS BINARY(1)) % 2 = 0 THEN 'S'
           ELSE 'N' end as ESG_E_PROD_CLI,----decommenta per ESG 2024
	CASE
           WHEN CAST(NEWID() AS BINARY(1)) % 2 = 0 THEN 'S'
           ELSE 'N' end as ESG_S_PROD_CLI,----decommenta per ESG 2024
	CASE
           WHEN CAST(NEWID() AS BINARY(1)) % 2 = 0 THEN 'S'
           ELSE 'N' end as ESG_G_PROD_CLI	----decommenta per ESG 2024
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 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
--				
--		+ 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
--		
left join [C6StagingPeriodico].[ESG_ANAG_METRICHE] esg
on esg.cod_prodotto = ANAGPROD.COD_PRODOTTO
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)
WHERE 1 = 1
	AND PATRBF.RETE = @Rete
	AND PATRBF.COD_FISCALE = @CodiceFiscale
     AND PATRBF.CTV <>0
	 and AREA.ORDINAMENTO in (1,2,3,4,5,8,9) --aggiunto 10/10/2022 per non far uscire le risorse non allocate e c/c negativi
ORDER BY 
	CASE
	when AREA.ORDINAMENTO = 10 then 10
	when AREA.ORDINAMENTO = 5 then 5
	when AREA.ORDINAMENTO = 4 then 4
	WHEN AREA.ORDINAMENTO = 8 or area.ORDINAMENTO = 9 then 3 
	when AREA.ORDINAMENTO = 3 then 3
	when AREA.ORDINAMENTO = 2 then 2
	when AREA.ORDINAMENTO = 1 then 1
	ELSE  3
END 
	,CHIAVE_progetto,nome_progetto, PATRBF.CTV DESC
END