-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
--select count(*) from c6stagingperiodico.spb_contr_sintesi
CREATE procedure [C6StagingPeriodico].[ST_SPB_CONTR_SINTESI_pre_selective]
AS
BEGIN
	SET NOCOUNT ON;
	-- Log esecuzione
	INSERT INTO LOG_ESECUZIONE
		(Nome, Inizio, Nota)
	VALUES (
		'ST_SPB_CONTR_SINTESI_pre_selective',
		GETDATE(),
		'Elaborazione trimestrale'
	)
	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 [C6StagingPeriodico].[WSEICS] AS CS
join
(SELECT
		Rete, Cod_Fiscale, Data_FineAvanzato, ROW_NUMBER() OVER(PARTITION BY Rete, Cod_Fiscale ORDER BY Data_FineAvanzato) AS UltimoAperto
	FROM
		C6Mart.CONTRATTOSEI) 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] = c6martperiodico.gettrimestre2(getdate(),0)
      AND ISNULL(C6.DATA_FINEAVANZATO,'99991231') > 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')
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 [C6StagingPeriodico].[WSEICS] AS CS 
join
(SELECT
		Rete, Cod_Fiscale, Data_FineAvanzato, ROW_NUMBER() OVER(PARTITION BY Rete, Cod_Fiscale ORDER BY Data_FineAvanzato) AS UltimoAperto
	FROM
		C6Mart.CONTRATTOSEI) 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] = c6martperiodico.gettrimestre2(getdate(),0)
      AND ISNULL(C6.DATA_FINEAVANZATO,'99991231') > 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 [C6StagingPeriodico].[WSEICS] 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
		C6Mart.CONTRATTOSEI) 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] = c6martperiodico.gettrimestre2(getdate(),0)
      --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		   
	-- Log esecuzione
	UPDATE
		LOG_ESECUZIONE
	SET
		Fine = GETDATE(),
		Tipo = 'INSERT',
		Righe = @@ROWCOUNT
	WHERE
		Nome = 'ST_SPB_CONTR_SINTESI_pre_selective' AND
		Inizio = (
			SELECT
				MAX(Inizio)
			FROM
				LOG_ESECUZIONE
			WHERE
				Nome = 'ST_SPB_CONTR_SINTESI_pre_selective'
		)
END