-- =============================================
-- Author:  Gentilezza Marco
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE procedure [C6Mart].[PL_D2_S162DettaglioProdotti]
 @Rete char(1), 
 @CodiceFiscale varchar(16)
AS
BEGIN
	SELECT  
		PATRTERZI.DESCR_GRUPPO_PRODOTTO  AS TipoProdotto,
		PATRTERZI.INTERMEDIARIO AS Intermediario,
		dbo.TOSHORTDATESTRING(PATRTERZI.DATA_SOTTOSCRIZIONE) AS DataDiSottoscrizioni, 
		ANAGPRODTERZI.NOME_PRODOTTO AS Descrizione,
		ROUND(PATRTERZI.CTV,2) AS Controvalore, 
		ROUND(PATRTERZI.VERSATO_NETTO,2) AS VersatoNetto, 
		ROUND(PATRTERZI.CTV,2) - ROUND(PATRTERZI.VERSATO_NETTO,2) AS MinusPlusValenza, 
		RISCHIOPROD.VAR_PERC_PTF AS VarPerc,
		CASE 
			WHEN ISNULL(PATRTERZI.VERSATO_NETTO, 0) = 0 THEN 'n.d.' 
			ELSE NULL 
		END AS VersatoNettoString,
		CASE 
			WHEN PATRTERZI.CTV - PATRTERZI.VERSATO_NETTO IS NULL THEN 'n.d.' 
			WHEN ISNULL(PATRTERZI.VERSATO_NETTO, 0) = 0 THEN 'n.d.' 
			ELSE NULL 
		END AS MinusPlusValenzaString,
		CASE
			WHEN ISNULL(RISCHIOPROD.copertura,0.00) = 0.00 THEN 'n.c.' 
			ELSE NULL 
		END AS VarPercString,
		SUM(ROUND(PATRTERZI.CTV,2)) OVER (PARTITION BY PATRTERZI.DESCR_GRUPPO_PRODOTTO) AS TotaleCTV,
		SUM(ROUND(PATRTERZI.VERSATO_NETTO,2)) OVER (PARTITION BY PATRTERZI.DESCR_GRUPPO_PRODOTTO) AS TotaleVersatoNetto,
		CASE 
			WHEN COUNT(CASE WHEN PATRTERZI.VERSATO_NETTO IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY PATRTERZI.DESCR_GRUPPO_PRODOTTO) > 0 THEN 'n.d.' 
			ELSE NULL 
		END AS TotaleVersatoNettoString,
		SUM(ROUND(PATRTERZI.CTV,2) - ROUND(PATRTERZI.VERSATO_NETTO,2)) OVER (PARTITION BY PATRTERZI.DESCR_GRUPPO_PRODOTTO) AS TotaleMinus,
		CASE 
			WHEN COUNT(CASE WHEN (PATRTERZI.CTV - PATRTERZI.VERSATO_NETTO) IS NULL THEN 1 ELSE NULL END) OVER (PARTITION BY PATRTERZI.DESCR_GRUPPO_PRODOTTO) > 0 THEN 'n.d.' 
			ELSE NULL 
		END AS TotaleMinusString,
		RISCHIOAGGREG.VAR_PERC_PTF AS TotaleVar,
		CASE WHEN ISNULL(RISCHIOAGGREG.COPERTURA,0.00) = 0.00 THEN 'n.c.' ELSE NULL END AS TotaleVarString,
		RISCHIOAGGREG.COPERTURA AS Copertura_Tot,
		NULL AS Aggiornamento, 
		CASE 
			WHEN RISCHIOAGGREG.COPERTURA > 0 AND RISCHIOAGGREG.COPERTURA < 100 
				THEN 'Grado di copertura: ' + REPLACE(CAST(CAST(RISCHIOAGGREG.COPERTURA AS DECIMAL (5,2)) AS VARCHAR),'.',',') + '%'
			ELSE '' -- CASI 0, 100, NULL
		END AS Copertura_Totale,
		PATRTERZI.RETE AS Rete, 
		PATRTERZI.COD_FISCALE AS Cod_Fiscale,
		'TABELLA PRODOTTI FINANZIARI ALTRI' AS Tipo,
		CASE WHEN (ISNULL(RISCHIOAGGREG.copertura,100) < 100) THEN '*' ELSE '' END AS Note,
		CASE WHEN PATRTERZI.COD_PRODOTTO = 'PRODOTTO NON IN CATALOGO' AND ISNULL(RISCHIOPROD.copertura,0.00) <> 0.00 THEN 1 ELSE 0	END AS ProdNonInCatalogo,
		CASE UPPER(PATRTERZI.DESCR_GRUPPO_PRODOTTO) 
			WHEN 'FONDI' THEN 0
			WHEN 'GESTIONI PATRIMONIALI' THEN 1
			WHEN 'PRODOTTI DI TIPO AMMINISTRATO (ESCLUSO C/C)' THEN 2
			WHEN 'ASSICURATIVI UNITI/INDEX LINKED' THEN 3
			WHEN 'PORTAFOGLIO GENERICO' THEN 4
			ELSE 10000
		END AS OrdinamentoProdotti,
 RISCHIOPROD.CREDITRISK as creditrisk,
    CASE 
		WHEN ISNULL(RISCHIOpROD.COPERTURA,0.00) = 0.00 THEN 'n.c.' 
		WHEN RISCHIOPROD.CREDITRISK IS NULL THEN 'n.a.'
		ELSE NULL 
	END as creditriskstring,
	RISCHIOPROD.CREDITRISKEMIT as creditriskemit
	FROM 
		C6MART.PATRIMONIO_TERZI PATRTERZI
	INNER JOIN 
		C6MART.ANAG_PRODOTTI_TERZI ANAGPRODTERZI
		ON 
			PATRTERZI.COD_PRODOTTO_TERZI = ANAGPRODTERZI.COD_PRODOTTO_TERZI
	LEFT OUTER JOIN 
		C6MART.RISCHIO_AGGREGATO RISCHIOPROD
		ON 
			PATRTERZI.RETE = RISCHIOPROD.RETE
			AND PATRTERZI.COD_FISCALE = RISCHIOPROD.COD_FISCALE
			AND RISCHIOPROD.COD_AGGREG ='TIPOPRODTERZI|TERZI|' + PATRTERZI.DESCR_GRUPPO_PRODOTTO + '|'+ PATRTERZI.COD_PRODOTTO_TERZI
	LEFT OUTER JOIN 
		C6MART.RISCHIO_AGGREGATO RISCHIOAGGREG
		ON 
			PATRTERZI.RETE = RISCHIOAGGREG.RETE
			AND PATRTERZI.COD_FISCALE = RISCHIOAGGREG.COD_FISCALE
			AND RISCHIOAGGREG.COD_AGGREG ='TIPOPRODTERZI|TERZI|' + PATRTERZI.DESCR_GRUPPO_PRODOTTO
	WHERE  1 = 1 
		AND PATRTERZI.RETE = @Rete
		AND PATRTERZI.COD_FISCALE = @CodiceFiscale
		AND PATRTERZI.DESCR_GRUPPO_PRODOTTO IS NOT NULL
	ORDER BY 
		OrdinamentoProdotti, 
		Controvalore DESC
END