-- =============================================
-- Description:	<La sp legge i dati, rispettivamente, dalle seguenti tb (scaricate da DB2): 
-- C6StagingPeriodico.WSEIFP  e con tali dati popola la tb dbo.C6MartPeriodico.FOI80
--N.B.: Le tb sono in join con la tb c6stagingperiodico.rp_catalogoprodotti, ciò è dettato dalla necessità di reperire il nome del fondo>
-- =============================================
CREATE procedure [C6MartPeriodico].[DM_FOI80_pre_selective]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Par_percentuale INT
SET @Par_percentuale = 80
DECLARE @DataFineTrim VARCHAR(8)
    SET @DataFineTrim = c6martperiodico.getTrimestre2(getDate(),0)
	-- Log esecuzione
	INSERT INTO LOG_ESECUZIONE
		(Nome, Inizio, Nota)
	VALUES (
		'DM_FOI80_pre_selective',
		GETDATE(),
		'Elaborazione trimestrale'
	)
	TRUNCATE TABLE C6MartPeriodico.FOI80
	INSERT INTO C6MartPeriodico.FOI80
	([DTTRIM]
      ,[DTRIFE]
      ,[RETE]
      ,[CODFIS]
      ,[DESPROD]
      ,[CODMAN]
      ,[CODCONF]
      ,[CODPROD]
      ,[SUBPROD]
      ,[ISIN]
      ,[CTV]
      ,[PREZZO]
      ,[QUOTE]
      ,[PREZZOMAX]
      ,[DTPREZZOMAX]
      ,[DESMAX]
      ,[PREZZOPROT]
      ,[CTRVIMPPROT]
      ,[DESPROT]
      ,[DESPRODTIT]
      ,[QUOTA_MAX_PERC]
	)
	select --top 10
	@DataFineTrim AS DTTRIM,
	DTRIFE AS DTRIFE,
	RETE AS RETE,
    CODFIS AS CODFIS,
    --ISNULL(CAT.NOMEPRODOTTO,'Da SP - Fonditalia Crescita Protetta 80 Cap EUR') as DESPROD, 
    CAT.NOMEPRODOTTO as DESPROD,
    ISNULL(CODMAN,'') AS CODMAN,
    CODCONF AS COD_CONF,
    CODPROD AS CODPROD,
    SUBPROD AS SUBPROD,
    ISIN AS ISIN,
    CTV AS CTV,
	PREZZO AS PREZZO,
	QUOTE AS QUOTE, 
	PREZZOMAX AS PREZZOMAX,
	DTPREZZOMAX AS DTPREZZOMAX,
	'Quota massima' AS DESMAX,
	PREZZOPROT AS PREZZOPROT,
	CTRVIMPPROT AS CTRVIMPPROT,
	'Quota e controvalore protetti *'  AS DESPROT,
	'Fonditalia Crescita Protetta 80' AS [DESPRODTIT],
	@Par_percentuale AS QUOTA_MAX_PERC 
	FROM C6StagingPeriodico.WSEIFP FP
	LEFT OUTER join c6stagingperiodico.rp_catalogoprodotti CAT
    on  --UL.codint=CAT.codinterno and
    FP.subprod=CAT.codsottoprodotto
    --and FP.isin=ISNULL(CAT.codisin,'')
    and CAT.visibilita = 1
	WHERE FP.DTTRIM = @DataFineTrim
	and PREZZOMAX <> 0.000 
	--AND FP.isin <> ''
--	union
--	select --top 10
--	RETE AS RETE,
--    CODFIS AS CODFIS,
--    CAT.NOMEPRODOTTO as DES_PROD,
--	CODCONF AS COD_CONF,
--	PREZZO AS QUOTA,
----	CTV/ PREZZO AS NUMEROQUOTE, 
--	QUOTE AS NUMEROQUOTE, 
--	CTV AS CONTROVALORE,
--	@DataFineTrim AS DATARIF, 
--	'Quota massima' AS DES_MAX,
--	PREZZOMAX AS QUOTA_MAX,
--	@Par_percentuale AS QUOTA_MAX_PERC, 
--	CONVERT(datetime, CONVERT(varchar,CONVERT(int,DTPREZZOMAX)), 0)  AS DATARIF_MAX,
--	'Quota e controvalore protetti'  AS DES_PROT,
--	PREZZOPROT AS QUOTA_PROT,
----	CTRVIMPPROT/ PREZZOPROT AS NUMEROQUOTE_PROT, 
--	QUOTE AS NUMEROQUOTE_PROT, 
--	CTRVIMPPROT AS CONTROVALORE_PROT
--	FROM C6StagingPeriodico.WSEIUL UL
--    LEFT OUTER join c6stagingperiodico.rp_catalogoprodotti CAT
--    on  UL.codint=CAT.codinterno
--    and UL.subprod=CAT.codsottoprodotto
--    and UL.isinFIT=ISNULL(CAT.codisin,'')
--    and CAT.visibilita = 1
--	WHERE UL.DTTRIM = @DataFineTrim
--	and PREZZOMAX <> 0.000 
--	AND UL.isinFIT <> ''
--	union
--	select --top 10
--	RETE AS RETE,
--    CODFIS AS CODFIS,
--    CAT.NOMEPRODOTTO as DES_PROD,
--	CODCONF AS COD_CONF,
--	PREZZO AS QUOTA,
----	CTV/ PREZZO AS NUMEROQUOTE, 
--	QUOTE AS NUMEROQUOTE, 
--	CTV AS CONTROVALORE,
--	@DataFineTrim AS DATARIF, 
--	'Quota massima' AS DES_MAX,
--	PREZZOMAX AS QUOTA_MAX,
--	@Par_percentuale AS QUOTA_MAX_PERC, 
--	CONVERT(datetime, CONVERT(varchar,CONVERT(int,DTPREZZOMAX)), 0)  AS DATARIF_MAX,
--	'Quota e controvalore protetti'  AS DES_PROT,
--	PREZZOPROT AS QUOTA_PROT,
----	CTRVIMPPROT/ PREZZOPROT AS NUMEROQUOTE_PROT,
--	QUOTE AS NUMEROQUOTE_PROT, 	 
--	CTRVIMPPROT AS CONTROVALORE_PROT
--	FROM C6StagingPeriodico.WSEIUL UL
--    LEFT OUTER join c6stagingperiodico.rp_catalogoprodotti CAT
--    on  UL.codint=CAT.codinterno
--    and UL.subprod=CAT.codsottoprodotto
--    and CAT.visibilita = 1
--	WHERE UL.DTTRIM = @DataFineTrim
--	and PREZZOMAX <> 0.000 
--	AND UL.isin = ''
--	AND UL.isinFIT = ''
--	union
--	select --top 10
--	RETE AS RETE,
--    CODFIS AS CODFIS,
--    CAT.NOMEPRODOTTO as DES_PROD,
--	CODCONF AS COD_CONF,
--	PREZZO AS QUOTA,
----	CTV/ PREZZO AS NUMEROQUOTE, 
--	QUOTE AS NUMEROQUOTE, 
--	CTV AS CONTROVALORE,
--	@DataFineTrim AS DATARIF, 
--	'Quota massima' AS DES_MAX,
--	PREZZOMAX AS QUOTA_MAX,
--	@Par_percentuale AS QUOTA_MAX_PERC, 
--	CONVERT(datetime, CONVERT(varchar,CONVERT(int,DTPREZZOMAX)), 0)  AS DATARIF_MAX,
--	'Quota e controvalore protetti'  AS DES_PROT,
--	PREZZOPROT AS QUOTA_PROT,
----	CTRVIMPPROT/ PREZZOPROT AS NUMEROQUOTE_PROT, 
--	QUOTE AS NUMEROQUOTE_PROT, 
--	CTRVIMPPROT AS CONTROVALORE_PROT
--	FROM C6StagingPeriodico.WSEIGP GP
--    LEFT OUTER join c6stagingperiodico.rp_catalogoprodotti CAT
--    on  GP.isin=ISNULL(CAT.codisin,'')
--    and CAT.tipoprodotto = 'GP'
--    and CAT.visibilita = 1
-- 	WHERE GP.DTTRIM = @DataFineTrim
--	and PREZZOMAX <> 0.000 
	-- Log esecuzione
	UPDATE
		LOG_ESECUZIONE
	SET
		Fine = GETDATE(),
		Tipo = 'INSERT',
		Righe = @@ROWCOUNT
	WHERE
		Nome = 'DM_FOI80_pre_selective' AND
		Inizio = (
			SELECT
				MAX(Inizio)
			FROM
				LOG_ESECUZIONE
			WHERE
				Nome = 'DM_FOI80_pre_selective'
		)
END