--select *
--from c6martperiodico.patrimonio_bf
--where cod_fiscale = 'GNGFRZ63H17D810C'
--and id_area = 'Liq'
--
--begin tran commit
--update 
--c6martperiodico.patrimonio_bf
--set ordinamento_progetto = 527950
--where cod_fiscale = 'GNGFRZ63H17D810C'
--and id_area = 'Liq'
--and ordinamento_progetto is null
-- Stored procedure
-- =============================================
-- Author:		Paolo Giovanetti -- Alessandro Tringali
-- Create date: 15 gennaio 2009  -- 19/09/2009
-- Description:	riporta il valore complessivo dei prodotti attualmente destinati all'area 
--				di bisogno progetto (controvalore attuale) ed il relativo valore di rischio (VaR attuale)
-- =============================================
-- [C6MartPeriodico].[PL_S86PatrimonioFinanziarioMAssetClass] 'S','GNGFRZ63H17D810C'
CREATE procedure [C6MartPeriodico].[PL_S86PatrimonioFinanziarioMAssetClass]
	-- Add the parameters for the stored procedure here
	@Rete char(1), 
	@CodiceFiscale varchar(16)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
SELECT
		VALORIASSET.ID_AREA AS Need_Area,
		VALORIASSET.NOME_PROGETTO,
		VALORIASSET.ord_progetto,
		VALORIASSET.ID_ASSETCLASS AS AssetClassId,
		ASSETCLASS.DESCRIZIONE AS AssetClassName,
		--ROUND(VALORIASSET.CONTROVALORE, 2) AS Controvalore,
		CAST(VALORIASSET.CONTROVALORE AS DECIMAL(15,2)) AS Controvalore,
		ROUND(VALORIASSET.CONTROVALORE / SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY VALORIASSET.ID_AREA, VALORIASSET.NOME_PROGETTO) * 100, 2) AS Percentuale
	FROM (
          SELECT 
          VAL.RETE,
          VAL.COD_FISCALE,
          VAL.ID_AREA,
          VAL.NOME_PROGETTO,
		  val.ord_progetto,
          SUM(CONTROVALORE) AS CONTROVALORE,
          ID_ASSETCLASS
          FROM
          (--V Escludo gli ASU2 che essendo skandia vanno gestiti in modo particolare
					SELECT
						PATR_BF.RETE,
						PATR_BF.COD_FISCALE,
						CASE
							WHEN PATR_BF.ID_AREA IN ('Pre1', 'Pre2') THEN 'Pre'
							ELSE PATR_BF.ID_AREA
						END AS ID_AREA,
						ltrim(rtrim(PATR_BF.NOME_PROGETTO)) NOME_PROGETTO ,
						patr_bf.ordinamento_progetto ord_progetto,
						(PATR_BF.CTV * ISNULL(ASSETPERC.PERC, 1)) AS CONTROVALORE,
						ISNULL(ASSETPERC.ID_ASSETCLASS, 'AL') AS ID_ASSETCLASS
					FROM C6MartPeriodico.PATRIMONIO_BF AS PATR_BF
						INNER JOIN C6MartPeriodico.ASSET_PERC AS ASSETPERC
							ON PATR_BF.COD_PRODOTTO = ASSETPERC.COD_PRODOTTO
					WHERE
						PATR_BF.ID_AREA NOT IN ('Cc', 'Na','Pre1','Pre2','Pre')
						AND ASSETPERC.LIVELLO = 1
						--AND PATR_BF.TIPO_PRODOTTO NOT IN ('ASUL','CC')
						AND PATR_BF.TIPO_PRODOTTO NOT IN ('ASUL','ASU2')
						AND PATR_BF.COD_FISCALE=@CodiceFiscale
                        AND PATR_BF.RETE= @Rete
--			  
				UNION ALL
				---DETTAGLIO ASUL SENZA  GLI SKANDIA
				SELECT      PATR_BF.RETE,
							PATR_BF.COD_FISCALE,
						CASE
							WHEN PATR_BF.ID_AREA IN ('Pre1', 'Pre2') THEN 'Pre'
							ELSE PATR_BF.ID_AREA
						END AS ID_AREA,
							PATR_BF.NOME_PROGETTO,
							patr_bf.ordinamento_progetto ord_progetto,
                            ASUL.CTV*ASSETPERC.PERC as Controvalore,
							ASSETPERC.ID_ASSETCLASS
					FROM C6MartPeriodico.DETTAGLIO_ASUL ASUL
					INNER JOIN C6MartPeriodico.ASSET_PERC ASSETPERC
						ON ASSETPERC.COD_PRODOTTO = rtrim(ASUL.COD_PRODOTTO)
						AND ASUL.COD_ISIN_SOTT = ''
						AND ASSETPERC.LIVELLO = 1
					INNER JOIN C6MartPeriodico.PATRIMONIO_BF PATR_BF
						ON ASUL.COD_FISCALE=PATR_BF.COD_fISCALE
						AND ASUL.RETE=PATR_BF.RETE
						AND ASUL.id_contratto=PATR_BF.ID_CONTRATTO
					WHERE
					ASUL.RETE = @Rete
					AND ASUL.COD_FISCALE = @CodiceFiscale
					AND PATR_BF.ID_AREA NOT IN ('Pre1','Pre2','Pre')
				 UNION ALL
				 --DETTAGLIO ASUL  SKANDIA
				 SELECT     ASUL.RETE,
							ASUL.COD_FISCALE,
						CASE
							WHEN ASUL.ID_AREA IN ('Pre1', 'Pre2') THEN 'Pre'
							ELSE ASUL.ID_AREA
						END AS ID_AREA,
							ASUL.NOME_PROGETTO,
							asul.ordinamento_progetto ord_progetto,
							ASUL.CTV*ASSETPERC.PERC as Controvalore,
							ASSETPERC.ID_ASSETCLASS
					FROM C6MartPeriodico.PATRIMONIO_BF ASUL
					INNER JOIN C6MartPeriodico.ASSET_PERC ASSETPERC
						ON ASSETPERC.COD_ISIN = rtrim(ASUL.ISIN)
						AND ASSETPERC.COD_MAF = rtrim(ASUL.COD_MAF)
						AND ASSETPERC.COD_INTERNO = rtrim(ASUL.COD_INTERNO)
						AND rtrim(ASSETPERC.COD_SOTTOPRODOTTO) = ASUL.COD_SOTTOPRODOTTO
						AND rtrim(ASSETPERC.COD_PRODOTTO) = ASUL.COD_PRODOTTO
						AND ASSETPERC.LIVELLO = 1
--					 INNER JOIN C6MartPeriodico.PATRIMONIO_BF PATR_BF
--						ON ASUL.COD_FISCALE=PATR_BF.COD_fISCALE
--						AND ASUL.RETE=PATR_BF.RETE
--						AND ASUL.ID_CONTRATTO=PATR_BF.ID_CONTRATTO
					WHERE
						ASUL.RETE =@Rete
						AND ASUL.COD_FISCALE = @CodiceFiscale
						--V gli SKANDIA sono a livello di patrimonio_bf con tipo prod ASU2
						AND ASUL.tipo_Prodotto = 'ASU2'
						AND ASUL.ID_AREA NOT IN ('Pre1','Pre2','Pre')
		  ) val
           where Controvalore >0
			GROUP BY 
			VAL.RETE,
			val.COD_FISCALE,
			VAL.ID_AREA,
			VAL.NOME_PROGETTO,
			val.ord_progetto,
			VAL.ID_ASSETCLASS
	UNION ALL
	/* nuovo */
		SELECT 
          VAL.RETE,
          VAL.COD_FISCALE,
          VAL.ID_AREA,
          VAL.NOME_PROGETTO,
		  MAX(val.ord_progetto),
          SUM(CONTROVALORE) AS CONTROVALORE,
          ID_ASSETCLASS
          FROM
		(--V Escludo gli ASU2 che essendo skandia vanno gestiti in modo particolare
					SELECT
						PATR_BF.RETE,
						PATR_BF.COD_FISCALE,
						CASE
							WHEN PATR_BF.ID_AREA IN ('Pre1', 'Pre2') THEN 'Pre'
							ELSE PATR_BF.ID_AREA
						END AS ID_AREA,
						ltrim(rtrim(PATR_BF.NOME_PROGETTO)) NOME_PROGETTO ,
						patr_bf.ordinamento_progetto ord_progetto,
						(PATR_BF.CTV * ISNULL(ASSETPERC.PERC, 1)) AS CONTROVALORE,
						ISNULL(ASSETPERC.ID_ASSETCLASS, 'AL') AS ID_ASSETCLASS
					FROM C6MartPeriodico.PATRIMONIO_BF AS PATR_BF
						INNER JOIN C6MartPeriodico.ASSET_PERC AS ASSETPERC
							ON PATR_BF.COD_PRODOTTO = ASSETPERC.COD_PRODOTTO
					WHERE
						PATR_BF.ID_AREA  IN ('Pre1','Pre2','Pre')
						AND ASSETPERC.LIVELLO = 1
						--AND PATR_BF.TIPO_PRODOTTO NOT IN ('ASUL','CC')
						AND PATR_BF.TIPO_PRODOTTO NOT IN ('ASUL','ASU2')
						AND PATR_BF.COD_FISCALE=@CodiceFiscale
                        AND PATR_BF.RETE= @Rete
--			  
				UNION ALL
				---DETTAGLIO ASUL SENZA  GLI SKANDIA
				SELECT      PATR_BF.RETE,
							PATR_BF.COD_FISCALE,
						CASE
							WHEN PATR_BF.ID_AREA IN ('Pre1', 'Pre2') THEN 'Pre'
							ELSE PATR_BF.ID_AREA
						END AS ID_AREA,
							PATR_BF.NOME_PROGETTO,
							patr_bf.ordinamento_progetto ord_progetto,
                            ASUL.CTV*ASSETPERC.PERC as Controvalore,
							ASSETPERC.ID_ASSETCLASS
					FROM C6MartPeriodico.DETTAGLIO_ASUL ASUL
					INNER JOIN C6MartPeriodico.ASSET_PERC ASSETPERC
						ON ASSETPERC.COD_PRODOTTO = rtrim(ASUL.COD_PRODOTTO)
						AND ASUL.COD_ISIN_SOTT = ''
						AND ASSETPERC.LIVELLO = 1
					INNER JOIN C6MartPeriodico.PATRIMONIO_BF PATR_BF
						ON ASUL.COD_FISCALE=PATR_BF.COD_fISCALE
						AND ASUL.RETE=PATR_BF.RETE
						AND ASUL.id_contratto=PATR_BF.ID_CONTRATTO
					WHERE
					ASUL.RETE = @Rete
					AND ASUL.COD_FISCALE = @CodiceFiscale
					AND PATR_BF.ID_AREA IN ('Pre1','Pre2','Pre')
				 UNION ALL
				 --DETTAGLIO ASUL  SKANDIA
				 SELECT     ASUL.RETE,
							ASUL.COD_FISCALE,
						CASE
							WHEN ASUL.ID_AREA IN ('Pre1', 'Pre2') THEN 'Pre'
							ELSE ASUL.ID_AREA
						END AS ID_AREA,
							ASUL.NOME_PROGETTO,
							asul.ordinamento_progetto ord_progetto,
							ASUL.CTV*ASSETPERC.PERC as Controvalore,
							ASSETPERC.ID_ASSETCLASS
					FROM C6MartPeriodico.PATRIMONIO_BF ASUL
					INNER JOIN C6MartPeriodico.ASSET_PERC ASSETPERC
						ON ASSETPERC.COD_ISIN = rtrim(ASUL.ISIN)
						AND ASSETPERC.COD_MAF = rtrim(ASUL.COD_MAF)
						AND ASSETPERC.COD_INTERNO = rtrim(ASUL.COD_INTERNO)
						AND rtrim(ASSETPERC.COD_SOTTOPRODOTTO) = ASUL.COD_SOTTOPRODOTTO
						AND rtrim(ASSETPERC.COD_PRODOTTO) = ASUL.COD_PRODOTTO
						AND ASSETPERC.LIVELLO = 1
--					 INNER JOIN C6MartPeriodico.PATRIMONIO_BF PATR_BF
--						ON ASUL.COD_FISCALE=PATR_BF.COD_fISCALE
--						AND ASUL.RETE=PATR_BF.RETE
--						AND ASUL.ID_CONTRATTO=PATR_BF.ID_CONTRATTO
					WHERE
						ASUL.RETE =@Rete
						AND ASUL.COD_FISCALE = @CodiceFiscale
						--V gli SKANDIA sono a livello di patrimonio_bf con tipo prod ASU2
						AND ASUL.tipo_Prodotto = 'ASU2'
						AND ASUL.ID_AREA  IN ('Pre1','Pre2','Pre')
		  ) val
           where Controvalore >0
			GROUP BY 
			VAL.RETE,
			val.COD_FISCALE,
			VAL.ID_AREA,
			VAL.NOME_PROGETTO,
			VAL.ID_ASSETCLASS
	/**/
    )
    VALORIASSET
	INNER JOIN C6MartPeriodico.ASSETCLASS AS ASSETCLASS
		ON ASSETCLASS.ID_ASSETCLASS = VALORIASSET.ID_ASSETCLASS
	WHERE
		VALORIASSET.RETE =@Rete
		AND VALORIASSET.COD_FISCALE = @CodiceFiscale
	ORDER BY
		ASSETCLASS.ORDINAMENTO
--
--	SELECT
--		VALORIASSET.ID_AREA AS Need_Area,
--		VALORIASSET.NOME_PROGETTO,
--		VALORIASSET.ID_ASSETCLASS AS AssetClassId,
--		ASSETCLASS.DESCRIZIONE AS AssetClassName,
--		--ROUND(VALORIASSET.CONTROVALORE, 2) AS Controvalore,
--		CAST(VALORIASSET.CONTROVALORE AS DECIMAL(15,2)) AS Controvalore,
--		ROUND(VALORIASSET.CONTROVALORE / SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY VALORIASSET.ID_AREA, VALORIASSET.NOME_PROGETTO) * 100, 2) AS Percentuale
--	FROM (
--		SELECT
--			PATR_BF.RETE,
--			PATR_BF.COD_FISCALE,
--			CASE
--				WHEN PATR_BF.ID_AREA IN ('Pre1', 'Pre2') THEN 'Pre'
--				ELSE PATR_BF.ID_AREA
--			END AS ID_AREA,
--			PATR_BF.NOME_PROGETTO,
--			SUM(PATR_BF.CTV * ISNULL(ASSETPERC.PERC, 1)) AS CONTROVALORE,
--			ISNULL(ASSETPERC.ID_ASSETCLASS, 'AL') AS ID_ASSETCLASS
--		FROM C6MartPeriodico.PATRIMONIO_BF AS PATR_BF
--			INNER JOIN C6MartPeriodico.ASSET_PERC AS ASSETPERC
--				ON PATR_BF.COD_PRODOTTO = ASSETPERC.COD_PRODOTTO
--		WHERE
--			PATR_BF.ID_AREA NOT IN ('Cc', 'Na')
--			AND ASSETPERC.LIVELLO = 1
--		GROUP BY
--			PATR_BF.RETE,
--			PATR_BF.COD_FISCALE,
--			PATR_BF.ID_AREA,
--			PATR_BF.NOME_PROGETTO,
--			ASSETPERC.ID_ASSETCLASS
--	) VALORIASSET
--	INNER JOIN C6MartPeriodico.ASSETCLASS AS ASSETCLASS
--		ON ASSETCLASS.ID_ASSETCLASS = VALORIASSET.ID_ASSETCLASS
--	WHERE
--		VALORIASSET.RETE = @Rete
--		AND VALORIASSET.COD_FISCALE = @CodiceFiscale
--	ORDER BY
--		ASSETCLASS.ORDINAMENTO
	/*SELECT
		ID_AREA AS NEED_AREA,
		NOME_PROGETTO,
		VALORIASSET.ID_ASSETCLASS AS AssetClassId, 
		ASSETCLASS.DESCRIZIONE AS AssetClassName, 
		SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY VALORIASSET.ID_ASSETCLASS, ID_AREA, NOME_PROGETTO) AS Controvalore,
		/*(
			SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY VALORIASSET.ID_ASSETCLASS, ID_AREA, NOME_PROGETTO) /
			SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY 'TOTALE')
		) * 100 AS Percentuale,*/
		VALORIASSET.CONTROVALORE / SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY ID_AREA, NOME_PROGETTO) * 100 AS Percentuale,
		--SUM(VALORIASSET.CONTROVALORE) OVER (PARTITION BY 'TOTALE') AS PatrimonioFinanziarioCTV,
		ASSETCLASS.ORDINAMENTO AS Ordinamento
	FROM (
		--PRODOTTI TERZI A CATALOGO
		SELECT
			LEFT(PATRBF.ID_AREA,3) AS ID_AREA,
			PATRBF.NOME_PROGETTO,
			ISNULL(ASSETPERC.ID_ASSETCLASS,'AL') AS ID_ASSETCLASS,
			PATRBF.CTV * ISNULL(ASSETPERC.PERC,1) AS Controvalore
		FROM 
			--C6MartPeriodico.PATRIMONIO_TERZI PATRTERZI
			C6MartPeriodico.PATRIMONIO_BF PATRBF
		LEFT JOIN 
			C6MartPeriodico.ASSET_PERC ASSETPERC 
			ON 
				PATRBF.COD_PRODOTTO = ASSETPERC.COD_PRODOTTO
				AND ASSETPERC.LIVELLO = 1
		WHERE 1 = 1
			--AND PATRTERZI.COD_PRODOTTO <> 'PRODOTTO NON IN CATALOGO'
			AND PATRBF.RETE = @Rete
			AND PATRBF.COD_FISCALE = @CodiceFiscale
			AND PATRBF.ID_AREA NOT IN ('Cc', 'Na')
		/*UNION ALL
		--PRODOTTI TERZI NON A CATALOGO
		SELECT 
			ISNULL(ASSETPERC.ID_ASSETCLASS,'AL') AS ID_ASSETCLASS,
			PATRTERZI.CTV*ISNULL(ASSETPERC.PERC,1) AS Controvalore
		FROM 
			C6MartPeriodico.PATRIMONIO_TERZI PATRTERZI
		LEFT JOIN 
			C6MartPeriodico.ASSET_PERC_TERZI ASSETPERC 
			ON 
				PATRTERZI.COD_PRODOTTO_TERZI = ASSETPERC.COD_PRODOTTO_TERZI
				AND ASSETPERC.LIVELLO = 1
		WHERE 1 = 1
			AND PATRTERZI.COD_PRODOTTO = 'PRODOTTO NON IN CATALOGO'
			AND PATRTERZI.RETE = @Rete
			AND PATRTERZI.COD_FISCALE = @CodiceFiscale*/
	) VALORIASSET
	INNER JOIN 
		C6MartPeriodico.ASSETCLASS ASSETCLASS
		ON 
			ASSETCLASS.ID_ASSETCLASS = VALORIASSET.ID_ASSETCLASS
	ORDER BY 
		ASSETCLASS.ORDINAMENTO*/
END