--[C6MartPeriodico].[PL_S172ProtezioneCapitaleAreeBisogno] 'f','BCCRRT43T09H449J'
CREATE procedure [C6MartPeriodico].[PL_S172ProtezioneCapitaleAreeBisogno_20171201_ori]
	-- Add the parameters for the stored procedure here
	@Rete char(1), 
	@CodiceFiscale varchar(16)
AS
BEGIN
-------------- recupero i totali dei prodotti a capitale protetto raggruppati per area di bisogno
SELECT -- PER DIVERSI DA CC ************************
		case 
			when (SPB.Isin = 'LU1106988527' and codprod in ('L191', 'RS', 'RM', 'RB', 'RV')) then 'INTERFUND 80' -- INTERFUND 80
			when (spb.tipprod = 'fi' and spb.subprod = 'f43' and spb.codint = '01') then 'FONDITALIA 80' 	-- FONDITALIA 80
			when (spb.subprod = 'cs80') then 'My Blue Protection 80'
			when (spb.subprod = 'ct80') then 'My White Protection 80'
			else ''
		end as nomeprod,
		SPB.RETE AS RETE,
		SPB.CODFIS AS COD_FISCALE,
		SPB.POSITIONID AS POSITIONID,
		SUBSTRING(SPB.POSITIONID,1,57) AS COD_PRODOTTO, 
		SPB.CODCONF AS ID_CONTRATTO,
        CASE WHEN ISDATE(SPB.DTSOTTOS) = 0 THEN NULL
				ELSE CAST(CAST(SPB.DTSOTTOS AS VARCHAR(8)) AS DATETIME)
		END AS DATA_SOTTOSCRIZIONE,
        null AS COD_STATO,
		SPB.NUMPOL AS NUM_POLIZZA,
		SPB.MAF AS COD_MAF,
		SPB.CODINT AS COD_INTERNO,
		SPB.SUBPROD AS COD_SOTTOPRODOTTO,
		SPB.TIPPROD AS TIPO_PRODOTTO,
		CASE
			WHEN CON.area = 'Inv' AND CON.tipoProgetto = 0 THEN 'Prodotti non associati a progetti'
			ELSE replace (LTRIM(RTRIM(CON.nomeProgetto)), ';', '')
		END AS NOME_PROGETTO,
        case when isnull(CON.tipoProgetto, '') = '' then 'Default' 
             when CON.tipoProgetto = 0 then 'Default' 
             else 'ProgettoInvestimento' end as TIPO_PROGETTO,
		CON.area AS ID_AREA,
		SPB.CTV AS CAPPROT,
		SPB.ISIN,
		CON.chiaveClientePB,
        0 AS REND_NON_RAPPR,
        CON.ChiaveProgetto AS ORDINAMENTO_PROGETTO,
        CASE WHEN CON.orizzonteTemporale = 0 THEN NULL ELSE CON.orizzonteTemporale END AS ORIZZ_TEMP_NUM,
        CASE WHEN ISNULL(ORIZZONTETEMPORALE, 0) = 0 THEN NULL ELSE  LTRIM(RTRIM(ORIZZONTETEMPORALE)) + ' anni' END AS ORIZZONTETEMPORALE,
        CON.CHIAVEPROGETTO AS CHIAVEPROGETTO 	
	INTO #PATR_BF
	FROM C6StagingPeriodico.SPB_CONTR_SINTESI_v SPB
	INNER JOIN C6StagingPeriodico.RP_Consulenza CON
		ON CON.rete = SPB.RETE
		AND CON.cod_fiscale = SPB.CODFIS
		AND CON.Position_id = SPB.positionid	 
	LEFT JOIN C6StagingPeriodico.RP_ChiaviMonitoraggio CM
		ON CON.chiaveprogetto = CM.chiaveProgetto
       AND CM.stato = 1
	WHERE
	spb.CODFIS = @CodiceFiscale and 
	 SPB.POSITIONID IS NOT NULL
          and CON.area <> 'CC' and SPB.TIPPROD <> 'CC'
          and SPB.Isin <> 'EURO00000009'
          and SPB.Isin <> 'EURO10000007'
          and CON.area <> 'SELF'
		  and 
		  ((SPB.Isin = 'LU1106988527' and codprod in ('L191', 'RS', 'RM', 'RB', 'RV')) -- INTERFUND 80/CAPPROT
				or
		   (spb.tipprod = 'fi'		-- FONDITALIA 80/FOI80
		    and spb.subprod = 'f43'
		    and spb.codint = '01')
			or
				spb.subprod in ('cs80', 'ct80') -- myblue, mywhite 
			)
		  SELECT
			TSCA.RETE,
			TSCA.CODFIS,
			C6StagingPeriodico.RP_Info_DaProdottoCSaCatalogo(
				TSCA.TIPPROD,
				CONS.CODINTERNO,
				CONS.CODMAF,
				CONS.CodIsin,
				TSCA.SUBPROD
			)
			+
			dbo.Crea_positionID(
				TSCA.TIPPROD,
				CONS.CODINTERNO,
				CONS.CODMAF,
				'',
				TSCA.SUBPROD,
				TSCA.CODCONF,
				CONS.NUMPOLIZZA,
				TSCA.CONTO,
				TSCA.RUBR,
				TSCA.CUSTGAR,
				TSCA.TERMID,
				TSCA.ANNO,
				TSCA.PROG
			) AS POSITION_ID
        INTO #REND_NON_RAPPR 
		FROM
			C6StagingPeriodico.TSCABP AS TSCA
            inner join   
            C6StagingPeriodico.tcptab as TCP
            ON TCP.CODFIS=TSCA.CODFIS
            AND TCP.RETE=TSCA.RETE
            AND TCP.CODCONF=TSCA.CODCONF
            AND TCP.CODPROG=TSCA.CODPROG
            AND TCP.CODPROD=TSCA.CODPROD
            AND TCP.SUBPROD=TSCA.SUBPROD
            AND TCP.ISIN=TSCA.ISIN
            AND TCP.dtrif=TSCA.dtrif
            AND TCP.dtingresso=TSCA.dtingresso
            AND TCP.CONTO=TSCA.CONTO
            AND TCP.CUSTGAR=TSCA.CUSTGAR
            AND TCP.CODABI=TSCA.CODABI
            AND ISNULL(TCP.PROG,'XXX')=ISNULL(TSCA.PROG,'XXX')
            AND ISNULL(TCP.TERMID,'XXX')=ISNULL(TSCA.TERMID,'XXX')
            AND ISNULL(TCP.ANNO,'XXX')=ISNULL(TSCA.ANNO,'XXX')
            inner join 
		C6StagingPeriodico.RP_CONSULENZA AS CONS 
        ON
				 TSCA.CODCONF = CONS.CODCONF
				AND (TSCA.CODPROD = CONS.CodInterno			
				 OR  TSCA.CODABI  = CONS.CodInterno)						 
				AND ISNULL(TSCA.SUBPROD, '') = ISNULL(CONS.CODSOTTOPRODOTTO, '') 
				AND ISNULL(TSCA.ISIN, '') = ISNULL(CONS.CodIsin, '') 
		   INNER JOIN
			C6StagingPeriodico.RP_CHIAVIMONITORAGGIO AS CM
			 ON CONS.chiaveprogetto = CM.chiaveProgetto
             AND CM.stato = 1
			 and TSCA.CODPROG = CM.IDMONITORAGGIO
		WHERE
		   TSCA.CODFIS =@CodiceFiscale
		   and TCP.dtuscita = '99991231'
		   UPDATE PATR_BF
				SET PATR_BF.REND_NON_RAPPR = 1
			FROM
				#REND_NON_RAPPR AS REND_NON_RAPPR
					INNER JOIN #PATR_BF AS PATR_BF
						ON PATR_BF.RETE = REND_NON_RAPPR.RETE
						AND PATR_BF.COD_FISCALE = 
								CASE WHEN(SUBSTRING(PATR_BF.COD_FISCALE,1,3) = 'FF@') THEN 'FF@'+REND_NON_RAPPR.CODFIS
									 ELSE  REND_NON_RAPPR.CODFIS
								END 
						AND PATR_BF.POSITIONID = REND_NON_RAPPR.POSITION_ID
		  --------- Individuati i prodotti, devo prendere il capitale protetto sulle tabelle relative
		  ---Interfund Crescita Protetta 80 (tipoprodotto ASU1) e presente sulla tabella [C6MartPeriodico].[CAPPROT]
		  ---Fonditalia Crescita protetta 80 --> FOI80
		  UPDATE patrimonio
		  set patrimonio.capprot = capprot.controvalore_prot
		  from  #PATR_BF as patrimonio
		  inner join 
		  C6MartPeriodico.CAPPROT as capprot
		  on patrimonio.rete = capprot.rete and patrimonio.cod_fiscale=capprot.codfis
		  where patrimonio.id_contratto = capprot.cod_conf
		  UPDATE patrimonio
		  set patrimonio.capprot = foi.ctrvimpprot
		  from  #PATR_BF as patrimonio
		  inner join 
		  C6MartPeriodico.FOI80 as foi
		  on patrimonio.rete = foi.rete and patrimonio.cod_fiscale=foi.codfis
		  where patrimonio.id_contratto = foi.codconf
		   ----------------------------------------raggruppo per area
		  select id_area,sum(capprot) as totCapProt
		  into #capitaleProtetto
		  from #PATR_BF
		  where REND_NON_RAPPR = 0
		  group by id_area
-----------------------------------------------------------------  
SELECT distinct [NeedArea]
      ,[AreaName]
      ,[PercentualeModello]
      ,[PercentualeAttuale]
      ,[ControvaloreAttuale]
      ,[ControvaloreModello]
      ,[Totale_Percentuale]
      ,[DifferenzaEuro]
      ,[DifferenzaPercentuale]
      ,[var_needarea]
      ,[var_needareaString]
      ,[copertura_needarea]
      ,[copertura_needareaString]
      ,[Nota]
      ,[somma_controvalAttuale]
      ,[somma_controvalModello]
      ,[somma_percentualeAttuale]
      ,[somma_percentualeModello]
      ,[var_tot]
      ,[var_totString]
      ,[var_tot_pir]
      ,[var_tot_pirString]
      ,[copertura_tot]
      ,[copertura_totString]
      ,[copertura_tot_pir]
      ,[copertura_tot_pirString]
      ,[VAR_RISFIN]
      ,[VAR_RISFINSTRING]
      ,[copertura_risfin]
      ,[copertura_risfinString]
      ,[ordinamento]
	  ,case  
			when ISNULL(cap.totCapProt,0) = 0 then 0
			else cap.totCapProt
			end as [capprot]
  FROM [C6MartPeriodico].[TB_S133_S157DatiPiramide] pir
  left join #capitaleProtetto cap
  on pir.needarea = cap.id_area
  WHERE	[Rete] 		= @Rete 
  AND	[CodiceFiscale] = @CodiceFiscale
  order by ordinamento
END