-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
--select count(*) from c6stagingperiodico.spb_contr_sintesi
CREATE procedure [C6StagingPeriodico].[RP_ST_SPB_CONTR_SINTESI_BK_eligoTitoli]
AS
BEGIN
	SET NOCOUNT ON;
	-- Log esecuzione
	INSERT INTO LOG_ESECUZIONE
		(Nome, Inizio, Nota)
	VALUES (
		'ST_SPB_CONTR_SINTESI',
		GETDATE(),
		'Elaborazione trimestrale'
	)
-- FC 18/03/2014 Ottimizzazione tempi di esecuzione
-- Sostituzione in tutte le where condition  	
-- condizione DTTRIM = c6martperiodico.gettrimestre2(getdate()
-- con set @dtTrime=convert(decimal,c6martperiodico.gettrimestre2(getdate(),0))
-- condizione DTTRIM = @dtTrime (set @dtTrime=convert(decimal,c6martperiodico.gettrimestre2(getdate(),0))  )
	Declare @dtTrime as decimal;
	Declare @dtTrimePrec as decimal;
	set @dtTrime=convert(decimal,c6martperiodico.gettrimestre2(getdate(),0))
	set @dtTrimePrec=convert(decimal,c6martperiodico.getprevioustrimestre(getdate(),0))
	TRUNCATE TABLE [C6StagingPeriodico].[SPB_CONTR_SINTESI]
	INSERT INTO [C6StagingPeriodico].[SPB_CONTR_SINTESI]
			   ([POSITIONID]
			  ,[DTTRIM]
			  ,[DTRIFE]
			  ,[RETE]
			  ,[CODFIS]
			  ,[CODCONF]
			  ,[NUMPOL]
			  ,[CONTO]
			  ,[RUBR]
			  ,[CUSTGAR]
			  ,[TIPPROD]
			  ,[CODINT]
			  ,[CODPROD]
			  ,[SUBPROD]
			  ,[MAF]
			  ,[CODABI]
			  ,[PCTKEY]
			  ,[DESTIT]
			  ,[CTV]
			  ,[PARTVIA]
			  ,[PARTVIAAL]
			  ,[DTSOTTOS]
			  ,[RENDINIZ]
			  ,[RENDANNO]
			  ,[VALORE]
			  ,[SUBSET]
			  ,[CVN]
			  ,[SALRISC]
			  ,[ISIN])
	SELECT 
		   NULL AS [POSITIONID]
	      ,[DTTRIM]
		  ,[DTRIFE]
           ,[CS].[RETE]
		   ,CASE
		WHEN CODMAN ='' THEN CODFIS
		ELSE 'FF@'+CODMAN
		    END AS CODFIS
		  ,[CODCONF]
		  ,[NUMPOL]
		  ,[CONTO]
		  ,case when (tipprod = 'CC' and rubr =' ') then '000'
			else RUBR end as RUBR
		  ,case when (tipprod = 'CC' and rubr =' ') then '0'
			else CUSTGAR end as custgar
		  ,[TIPPROD]
		  ,[CODINT]
		  ,[CODPROD]
		  ,[SUBPROD]
		  ,[MAF]
		  ,[CODABI]
		  ,case when (tipprod = 'CC' and rubr =' ') then '0'
			else PCTKEY end as pctkey
		  ,[DESTIT]
		  ,[CTV]
		  ,[PARTVIA]
		  ,[PARTVIAAL]
		  ,[DTSOTTOS]
		  ,[RENDINIZ]
		  ,[RENDANNO]
		  ,[VALORE]
		  ,[SUBSET]
		  ,[CVN]
		  ,[SALRISC]
		  ,'NA' as ISIN 
	  FROM (select * from [C6StagingPeriodico].[WSEICS] 
where [DTTRIM] = @dtTrime) AS CS
join
(SELECT
		Rete, Cod_Fiscale, Data_FineAvanzato, ROW_NUMBER() OVER(PARTITION BY Rete, Cod_Fiscale ORDER BY Data_FineAvanzato) AS UltimoAperto
	FROM 
        C6MartPeriodico.RP_vContrattiPerGenerazioneReport) C6
--INNER JOIN
--			[C6Mart].[CONTRATTOSEI] AS C6
ON
			CS.RETE = C6.RETE AND
-- TRINGALI 19022010
	(case when cs.codman <> '' then 'FF@'+ ltrim(rtrim(cs.codman)) else cs.codfis end) = C6.COd_fiscale
--      LEFT JOIN C6StagingPeriodico.WMANDATI AS MANDATI
--      ON cs.codman = ltrim(rtrim(MANDATI.CODMAND))
	  WHERE [DTTRIM] =@dtTrime
      AND ISNULL(C6.DATA_FINEAVANZATO,'99991231') >convert(varchar(10),@dtTrimePrec,112) --c6martperiodico.getprevioustrimestre(getdate(),0)--c6martperiodico.gettrimestre2(c6martperiodico.gettrimestre2(getdate(),1),0)
       -- Atringali C6MARTPERIODICO.GETTRIMESTRE(GETDATE(),0)
       --     AND ISNULL(C6.DATA_FINEAVANZATO,'99991231') > C6MARTPERIODICO.GETTRIMESTRE(GETDATE(),0)
	  --V I KE, KS LI RECUPERO DALLA WSEIUL
	  --AND SUBPROD NOT IN ('KE','KS','KW','K1','K2','K3','K4','K5','K6','K7')
		--modifica Emanuele 20131112
		AND SUBPROD NOT IN ('KE','KS','KW','K1','K2','K3','K4','K5','K6','K7', 'L191')
AND C6.UltimoAperto = 1
--trimestrale Aprile 2013
--and c6.rete+c6.COD_FIScale+c6.codicecontratto <> 'FRMNCRD39R25F943Z007608CO205'
UNION ALL
SELECT 
		   NULL AS [POSITIONID]
	      ,CS.[DTTRIM]
		  ,CS.[DTRIFE]
           ,[CS].[RETE]
		   ,CASE
		WHEN CS.CODMAN ='' THEN CS.CODFIS
		ELSE 'FF@'+CS.CODMAN
		    END AS CODFIS
		  ,CS.[CODCONF]
		  ,[NUMPOL]
		  ,[CONTO]
		  ,case when (tipprod = 'CC' and rubr =' ') then '000'
			else RUBR end as RUBR
		  ,case when (tipprod = 'CC' and rubr =' ') then '0'
			else CUSTGAR end as custgar
		  ,--CASE WHEN UL.SUBPROD IN ('VAR10','VAR5','VAR15') THEN 'ASU1' ELSE 'ASU2'
		  CASE WHEN UL.ISIN IS NULL OR UL.SUBPROD = 'CL05' THEN 'ASU1' ELSE 'ASU2'
			end as TIPPROD
		  ,UL.[CODINT]
		  ,[CODPROD]
		  ,UL.[SUBPROD]
		  ,UL.[MAF]
		  ,[CODABI]
		  ,case when (tipprod = 'CC' and rubr =' ') then '0'
			else PCTKEY end as pctkey
		  ,[DESTIT]
		  ,UL.[CTV]
		  ,[PARTVIA]
		  ,[PARTVIAAL]
		  ,[DTSOTTOS]
		  ,[RENDINIZ]
		  ,[RENDANNO]
		  ,UL.[CTV] AS VALORE --V Anomalia trimestrale ottobre 2011, il valore diventa il ctv nominale e deve essere uguale al ctv per il calcolo corretto del var
		  ,UL.[SUBSET]
		  ,[CVN]
		  ,[SALRISC]
		  ,case when(UL.ISIN is null) then 'NA' else UL.ISIN end as ISIN
	  FROM (select * from [C6StagingPeriodico].[WSEICS] 
where [DTTRIM] = @dtTrime) AS CS 
join
(SELECT
		Rete, Cod_Fiscale, Data_FineAvanzato, ROW_NUMBER() OVER(PARTITION BY Rete, Cod_Fiscale ORDER BY Data_FineAvanzato) AS UltimoAperto
	FROM 
        C6MartPeriodico.RP_vContrattiPerGenerazioneReport) C6
--	  JOIN [C6Mart].[CONTRATTOSEI] AS C6 
		ON CS.RETE = C6.RETE 
		AND (case when cs.codman <> '' then 'FF@'+ ltrim(rtrim(cs.codman)) else cs.codfis end) = C6.COd_fiscale
	  JOIN C6STAGINGPERIODICO.WSEIUL AS UL
		ON	CS.CODCONF = UL.CODCONF
		AND	CS.DTTRIM = UL.DTTRIM
	  WHERE CS.[DTTRIM] =@dtTrime
      AND ISNULL(C6.DATA_FINEAVANZATO,'99991231') >convert(varchar(10),@dtTrimePrec,112)--c6martperiodico.getprevioustrimestre(getdate(),0)--c6martperiodico.gettrimestre2(c6martperiodico.gettrimestre2(getdate(),1),0)
       -- Atringali C6MARTPERIODICO.GETTRIMESTRE(GETDATE(),0)
       --     AND ISNULL(C6.DATA_FINEAVANZATO,'99991231') > C6MARTPERIODICO.GETTRIMESTRE(GETDATE(),0)
	  --V I KE, KS,KW LI RECUPERO DALLA WSEIUL
	  AND CS.SUBPROD  IN ('KE','KS','KW','K1') and ul.subprod not in('VAR5','VAR10','VAR15','CL05','LIQUI')
AND C6.UltimoAperto = 1
	 UNION ALL
	  --INSERIRE VAR5..15
      --V Modifica fatta in quanto sulle tabelle di replica i prodotti erano rappresentati con i sottostanti, effettuo il raggruppamento per
	  -- codconf butto il MAF che non è significativo e sommo il ctv. I var5, var10, e var15 sono gestiti per linea quindi devono avere una riga
	  -- unica e devono essere classificati come ASU1
	  SELECT 
		DISTINCT
		   NULL AS [POSITIONID]
	      ,CS.[DTTRIM]
		  ,CS.[DTRIFE]
           ,[CS].[RETE]
		   ,CASE
		WHEN CS.CODMAN ='' THEN CS.CODFIS
		ELSE 'FF@'+CS.CODMAN
		    END AS CODFIS
		  ,CS.[CODCONF]
		  ,[NUMPOL]
		  ,[CONTO]
		  ,case when (tipprod = 'CC' and rubr =' ') then '000'
			else RUBR end as RUBR
		  ,case when (tipprod = 'CC' and rubr =' ') then '0'
			else CUSTGAR end as custgar
		  ,--CASE WHEN UL.SUBPROD IN ('VAR10','VAR5','VAR15') THEN 'ASU1' ELSE 'ASU2'
		  'ASU1' as TIPPROD
		  ,UL.[CODINT]
		  ,[CODPROD]
		  ,UL.[SUBPROD]
		  ,'NA' as MAF--UL.[MAF]
		  ,[CODABI]
		  ,case when (tipprod = 'CC' and rubr =' ') then '0'
			else PCTKEY end as pctkey
		  ,[DESTIT]
		  ,SUM(UL.[CTV]) OVER (partition by UL.codConf) as CTV
		  ,[PARTVIA]
		  ,[PARTVIAAL]
		  ,[DTSOTTOS]
		  ,[RENDINIZ]
		  ,[RENDANNO]
		  ,[VALORE]
		  ,UL.[SUBSET]
		  ,[CVN]
		  ,[SALRISC]
		  ,'NA' as ISIN
	  FROM (select * from [C6StagingPeriodico].[WSEICS] 
where [DTTRIM] = @dtTrime ) AS CS 
--	  JOIN [C6Mart].[CONTRATTOSEI] AS C6 
join
(SELECT
		Rete, Cod_Fiscale, Data_FineAvanzato, ROW_NUMBER() OVER(PARTITION BY Rete, Cod_Fiscale ORDER BY Data_FineAvanzato) AS UltimoAperto
	FROM
        C6MartPeriodico.RP_vContrattiPerGenerazioneReport) C6
		ON CS.RETE = C6.RETE 
		AND (case when cs.codman <> '' then 'FF@'+ ltrim(rtrim(cs.codman)) else cs.codfis end) = C6.COd_fiscale
	  JOIN C6STAGINGPERIODICO.WSEIUL AS UL
		ON	CS.CODCONF = UL.CODCONF
		AND	CS.DTTRIM = UL.DTTRIM
	  WHERE CS.[DTTRIM] = @dtTrime
      --AND ISNULL(C6.DATA_FINEAVANZATO,'99991231') > c6martperiodico.gettrimestre2(c6martperiodico.gettrimestre2(getdate(),1),0)
       -- Atringali C6MARTPERIODICO.GETTRIMESTRE(GETDATE(),0)
       --     AND ISNULL(C6.DATA_FINEAVANZATO,'99991231') > C6MARTPERIODICO.GETTRIMESTRE(GETDATE(),0)
	  --V I KE, KS,KW LI RECUPERO DALLA WSEIUL
	  AND	(
				CS.SUBPROD  IN ('K1','KE','KS','KW') and ul.subprod in('VAR5','VAR10','VAR15','CL05','LIQUI') 
				OR 
				(CS.SUBPROD  IN ('K2','K3','K4','K5','K6','K7')) 
				--OR
				--(CS.SUBPROD IN ('K1') AND UL.SUBPROD IN ('CL05'))
			)
AND C6.UltimoAperto = 1		   
--inizio intervento per Omnia linea Self
UNION ALL
SELECT
	POSITIONID
	,DTTRIM
	,DTRIFE
	,RETE
	,CODFIS
	,CODCONF
	,NUMPOL
	,CONTO
	,RUBR
	,custgar
	,TIPPROD
	,CODINT
	,CODPROD
	,SUBPROD
	,MAF
	,CODABI
	,pctkey
	,DESTIT
	,sum(CTV) as CTV
	,PARTVIA
	,PARTVIAAL
	,DTSOTTOS
	,RENDINIZ
	,RENDANNO
	,sum(VALORE) as VALORE
	,SUBSET
	,CVN
	,SALRISC
	,ISIN
FROM
	(
	SELECT
		NULL AS POSITIONID
		,CS.DTTRIM
		,CS.DTRIFE
		,CS.RETE
		,CASE
		WHEN CS.CODMAN ='' THEN CS.CODFIS
		ELSE 'FF@'+CS.CODMAN
		END AS CODFIS
		,CS.CODCONF
		,NUMPOL
		,CONTO
		,case when (GP.ISIN = 'EURO00000009' and rubr =' ') then '000'
		else RUBR end as RUBR
		,case when (GP.ISIN = 'EURO00000009' and rubr =' ') then '0'
		else CUSTGAR end as custgar
		,'GP' as TIPPROD
		,CODINT
		,CODPROD
		,SUBPROD
		,'NA' as MAF --UL.MAF
		,CODABI
		,case when (GP.ISIN = 'EURO00000009' and rubr =' ') then '0'
		else PCTKEY end as pctkey
		,DESTIT
		,GP.CTV as CTV
--INIZIO LAST INTERVENTO OMNIA		
--		,PARTVIA
--		,PARTVIAAL
		,0.00 AS PARTVIA
		,0 AS PARTVIAAL
--FINE LAST INTERVENTO OMNIA		
		,DTSOTTOS
		,RENDINIZ
		,RENDANNO
		,GP.CTV AS VALORE
		,'' AS SUBSET --?
		,CVN
		,SALRISC
		,GP.ISIN
	FROM (select * from C6StagingPeriodico.WSEICS 
	WHERE DTTRIM = @dtTrime
	and   subprod = 'L191') AS CS
	join
	(SELECT
			Rete, Cod_Fiscale, Data_FineAvanzato, ROW_NUMBER() OVER(PARTITION BY Rete, Cod_Fiscale ORDER BY Data_FineAvanzato) AS UltimoAperto
		FROM
			C6MartPeriodico.RP_vContrattiPerGenerazioneReport) C6
			ON CS.RETE = C6.RETE 
			AND (case when cs.codman <> '' then 'FF@'+ ltrim(rtrim(cs.codman)) else cs.codfis end) = C6.COd_fiscale
		  JOIN C6STAGINGPERIODICO.WSEIGP AS GP
			ON	CS.CODCONF = GP.CODCONF
			AND	CS.DTTRIM = GP.DTTRIM
		  WHERE CS.DTTRIM =@dtTrime
	AND C6.UltimoAperto = 1
--inizio esclusione isin a null emanuele
	and GP.ISIN is not null
--fine esclusione isin a null emanuele
	) a
GROUP BY
	POSITIONID
	,DTTRIM
	,DTRIFE
	,RETE
	,CODFIS
	,CODCONF
	,NUMPOL
	,CONTO
	,RUBR
	,custgar
	,TIPPROD
	,CODINT
	,CODPROD
	,SUBPROD
	,MAF
	,CODABI
	,pctkey
	,DESTIT
	,PARTVIA
	,PARTVIAAL
	,DTSOTTOS
	,RENDINIZ
	,RENDANNO
	,SUBSET
	,CVN
	,SALRISC
	,ISIN
--INIZIO LAST INTERVENTO OMNIA		
UNION ALL
SELECT
	POSITIONID
	,DTTRIM
	,DTRIFE
	,RETE
	,CODFIS
	,CODCONF
	,NUMPOL
	,CONTO
	,RUBR
	,custgar
	,TIPPROD
	,CODINT
	,CODPROD
	,SUBPROD
	,MAF
	,CODABI
	,pctkey
	,DESTIT
	,CTV
	,PARTVIA
	,PARTVIAAL
	,DTSOTTOS
	,RENDINIZ
	,RENDANNO
	,VALORE
	,SUBSET
	,CVN
	,SALRISC
	,ISIN
FROM
	(
	SELECT distinct
		NULL AS POSITIONID
		,CS.DTTRIM
		,CS.DTRIFE
		,CS.RETE
		,CASE
		WHEN CS.CODMAN ='' THEN CS.CODFIS
		ELSE 'FF@'+CS.CODMAN
		END AS CODFIS
		,CS.CODCONF
		,NUMPOL
		,CONTO
		,RUBR
		,CUSTGAR
		,'GE' as TIPPROD
		,CODINT
		,CODPROD
		,SUBPROD
		,'NA' as MAF 
		,CODABI
		,PCTKEY
		,DESTIT
		,0.00 AS CTV
		,PARTVIA
		,PARTVIAAL
		,DTSOTTOS
		,RENDINIZ
		,RENDANNO
		,0.00 AS VALORE
		,'' AS SUBSET 
		,0.00 as CVN
		,SALRISC
		,'NA' as ISIN
	FROM (select * from C6StagingPeriodico.WSEICS 
	WHERE DTTRIM = @dtTrime
	and   subprod = 'L191'
	and   partvia <> 0.00) AS CS 
	join
	(SELECT
			Rete, Cod_Fiscale, Data_FineAvanzato, ROW_NUMBER() OVER(PARTITION BY Rete, Cod_Fiscale ORDER BY Data_FineAvanzato) AS UltimoAperto
		FROM
			C6MartPeriodico.RP_vContrattiPerGenerazioneReport) C6
			ON CS.RETE = C6.RETE 
			AND (case when cs.codman <> '' then 'FF@'+ ltrim(rtrim(cs.codman)) else cs.codfis end) = C6.COd_fiscale
	AND C6.UltimoAperto = 1) a
--FINE LAST INTERVENTO OMNIA		
--fine intervento per Omnia linea Self
--inizio inserimento su testquality emanuele
--insert into c6martperiodico.test_quality
--select distinct rete, codfis, 'LINEA SELF ISIN A NULL SU TABELLA WSEIGP' from C6StagingPeriodico.WSEIGP where isin is null
--fine inserimento su testquality emanuele
	-- Log esecuzione
	UPDATE
		LOG_ESECUZIONE
	SET
		Fine = GETDATE(),
		Tipo = 'INSERT',
		Righe = @@ROWCOUNT
	WHERE
		Nome = 'ST_SPB_CONTR_SINTESI' AND
		Inizio = (
			SELECT
				MAX(Inizio)
			FROM
				LOG_ESECUZIONE
			WHERE
				Nome = 'ST_SPB_CONTR_SINTESI'
		)
END