-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
--[C6MartPeriodico].[GESTIONE_SELECT_S80_EVOLUZIONE_CTV] '31/12/2015'
CREATE procedure [C6MartPeriodico].[GESTIONE_SELECT_S80_EVOLUZIONE_CTV]
	@datafase varchar(10)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    SELECT
	a.rete,
	a.cod_fiscale,
	@datafase as DataFase,
	EXTRARENDIMENTO,
	INVESTIMENTO,
	PREVIDENZA,
	RISERVA,
	LIQUIDITA,
	RisorseNonAllocate,
	contocorrente,
	PARTVIA_DISINV + isnull(b.importo,0) AS Partita_viaggiante,
	--sum(case Upper(a.id_area) when 'CC' then 0 else a.ctv end ) as risorseFinanziarie,
	a.CTV + isnull(b.importo,0)+ PARTVIA_DISINV AS Totale,
	--V
	CASE WHEN RA.VAR_PERC_PTF IS NULL THEN 0.00 ELSE ROUND(RA.VAR_PERC_PTF,2) END AS VarComplessivo,
	isnull(RA.COPERTURA,0) AS Copertura,
	CR.MAX_VAR AS VarMassimoProfiloFinanziario,
	-- TRINGALI MODIFICARE
	ra.creditrisk as RISKCLASS,
	mifid.riskclass as RISKCLASSMAX,
	case when ra.creditrisk is null then 'n.a.' else null end as RiskClassString,
	case when mifid.riskclass is null then 'n.a.' else null end as RiskClassMaxString
FROM
	(SELECT
		rete,
		cod_fiscale,
		sum(case Upper(id_area) when 'EXT' then ctv else 0 end ) AS EXTRARENDIMENTO,
		sum(case Upper(id_area) when 'INV' then ctv else 0 end ) AS INVESTIMENTO,
		sum(case when Upper(id_area) IN ('PRE1', 'PRE2') then ctv else 0 end ) AS PREVIDENZA,
		sum(case Upper(id_area) when 'RIS' then ctv else 0 end ) AS RISERVA,
		sum(case Upper(id_area) when 'LIQ' then ctv else 0 end ) AS LIQUIDITA,
		sum(case Upper(id_area) when 'NA' then ctv else 0 end ) AS RisorseNonAllocate,
		sum(case Upper(id_area) when 'CC' then ctv else 0 end ) as contocorrente,
		sum((isnull(PARTVIA_DISINV,0))) as PARTVIA_DISINV,
		sum(CTV) as CTV
	FROM 
		[C6MartPeriodico].PATRIMONIO_BF
	GROUP BY rete, cod_fiscale) a
LEFT JOIN c6martperiodico.partite_viaggianti b
	on a.rete=b.rete and a.cod_fiscale=b.cod_fiscale
INNER JOIN C6MartPeriodico.MIFID AS MIFID
	ON 	a.RETE = MIFID.RETE
	AND a.COD_FISCALE = MIFID.COD_FISCALE
INNER JOIN C6MartPeriodico.CODIFICA_RISCHIO AS CR
	ON MIFID.PROFILO_ASS = CR.PROFILO
LEFT OUTER JOIN C6MartPeriodico.RISCHIO_AGGREGATO AS RA
	ON 	RA.RETE = a.RETE
	AND RA.COD_FISCALE = a.COD_FISCALE
	AND RA.COD_AGGREG = 'COMPLESSIVO|BF'
END