-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE procedure [C6MartPeriodico].[temp_RP_DM_PATRIMONIO_BF]
	-- Add the parameters for the stored procedure here
	--select * from C6MartPeriodico.patrimonio_bf
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
SET NOCOUNT ON;
	DECLARE @ID_ELAB INT
	DECLARE @TIPO_ELAB VARCHAR(1)
	SET @ID_ELAB = C6MartPeriodico.GETIDELAB()
	SET @TIPO_ELAB = C6MartPeriodico.GETTIPOELAB()
	-- Log esecuzione
	INSERT INTO LOG_ESECUZIONE
		(Nome, Inizio, Nota)
	VALUES (
		'RP_DM_PATRIMONIO_BF',
		GETDATE(),
		'Elaborazione trimestrale'
	)
	TRUNCATE TABLE C6MartPeriodico.PATRIMONIO_BF
 -- *** INCROCIO TRA SPB E CONSULENZA ************************
 SELECT -- PER DIVERSI DA CC ************************
		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  SPB.TIPPROD IN ('DT', 'FI') AND 
--              SPB.DTSOTTOS IS NOT NULL THEN null
--			ELSE SPB.DTSOTTOS
         CASE WHEN ISDATE(SPB.DTSOTTOS) = 0 THEN NULL
 				ELSE SPB.DTSOTTOS
 		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 LTRIM(RTRIM(CON.nomeProgetto))
		END AS NOME_PROGETTO,
		CON.area AS ID_AREA,
		CASE
			WHEN ISDATE(SPB.DTRIFE) = 0 THEN NULL
			ELSE SPB.DTRIFE --CAST(CAST(SPB.DTRIFE AS VARCHAR(8)) AS DATETIME)
 		END AS DATA_OPERAZIONE,
		NULL AS DATA_VALUTA, -- 105 PER FORMAT DD/MM/YYYY
		NULL AS DATA_REGOLAMENTO,
		SPB.QUOTE AS QUANTITA,
		NULL AS PREZZO,
		SPB.CTV AS CTV,
		SPB.VALORE AS CTV_NOMINALE,		
        NULL AS VERSATO_NETTO,
		SPB.PARTVIA AS PARTVIA_DISINV,
		SPB.PARTVIAAL AS DATA_PARTVIA_DISINV,			
		NULL AS PARTVIA_INV,	 
 		CASE 
 			WHEN (SPB.TIPPROD = 'GE' 
 				OR SPB.TIPPROD = 'GES')
 				AND SPB.DTSOTTOS < 20070101
 				THEN NULL 
 			WHEN SPB.TIPPROD IN ( 'ASVI','ASU1')
 				THEN NULL
 			WHEN SPB.TIPPROD = 'ASUL' AND SPB.CODINT NOT IN ('18','19','35','PR','PX')
 				THEN NULL
 			ELSE SPB.RENDINIZ
 		END AS REND_SOTTOSCRIZIONE,
 		CASE 
 			WHEN (SPB.TIPPROD = 'GE' 
 				OR SPB.TIPPROD = 'GES')
 				AND SPB.DTSOTTOS < 20070101
 				THEN NULL 
 			WHEN SPB.TIPPROD IN ( 'ASVI','ASU1')
 				THEN NULL
 			WHEN SPB.TIPPROD = 'ASUL' AND SPB.CODINT NOT IN ('18','19','35','PR','PX')
 				THEN NULL
 			ELSE SPB.RENDINIZ
 		END AS REND_TRIMESTRE,	
 		CASE 
 			WHEN (SPB.TIPPROD = 'GE' 
 				OR SPB.TIPPROD = 'GES')
 				AND SPB.DTSOTTOS < 20070101
 				THEN NULL 
 			WHEN SPB.TIPPROD IN ( 'ASVI','ASU1')
 				THEN NULL
 			WHEN SPB.TIPPROD = 'ASUL' AND SPB.CODINT NOT IN ('18','19','35','PR','PX')
 				THEN NULL
 			ELSE SPB.RENDANNO
 		END AS REND_ANNO,
		CON.StrType,
        CON.Complessita AS COMPLEX,
        CON.DescrizioneEmittente AS EMITTENTE,
		SPB.ISIN,
	 	@ID_ELAB AS ID_ELAB,  
	 	@TIPO_ELAB AS TIPO_ELAB,   
		CON.chiaveClientePB	
	INTO #PATRIMONIO
	FROM C6StagingPeriodico.SPB_CONTR_SINTESI SPB
	INNER JOIN C6StagingPeriodico.RP_Consulenza CON
		ON CON.rete = SPB.RETE
		AND CON.cod_fiscale = SPB.CODFIS
		AND CON.Position_id = SPB.positionid	 
	WHERE SPB.POSITIONID IS NOT NULL
          and CON.area <> 'CC' and SPB.TIPPROD <> 'CC'
    UNION ALL
    SELECT -- *** PER UGUALI DA CC ************************      
		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 SPB.TIPPROD = 'DT' /* OR SPB.TIPOPRODOTTO = 'FI' */ THEN SPB.DATAINIZIORENDIMENTO 
--			ELSE SPB.DTSOTTOS
        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,
		CON.NOMEPROGETTO AS NOME_PROGETTO,
		CASE WHEN SPB.CTV>0 THEN 'Liq' ELSE 'Cc' END AS ID_AREA, --PRCONSUL.NEEDAREAID AS ID_AREA,--POSITION.C_NEEDAREASE_3D485 AS ID_AREA,
		SPB.DTRIFE AS DATA_OPERAZIONE,
		null AS DATA_VALUTA, -- 105 PER FORMAT DD/MM/YYYY
		NULL AS DATA_REGOLAMENTO,
		SPB.QUOTE AS QUANTITA,
		null AS PREZZO,
		SPB.CTV AS CTV,
		SPB.VALORE AS CTV_NOMINALE,
		NULL AS VERSATO_NETTO,
		SPB.PARTVIA AS PARTVIA_DISINV,
		SPB.PARTVIAAL AS DATA_PARTVIA_DISINV,
		NULL AS PARTVIA_INV,  
		SPB.RENDINIZ AS REND_SOTTOSCRIZIONE,
		null AS REND_TRIMESTRE,
		SPB.RENDANNO AS REND_ANNO,
        CON.StrType,
        CON.Complessita AS COMPLEX,
        CON.DescrizioneEmittente as EMITTENTE,
		SPB.ISIN,
		@ID_ELAB AS ID_ELAB,  
		@TIPO_ELAB AS TIPO_ELAB,  
		CON.chiaveClientePB	
	FROM C6StagingPeriodico.SPB_CONTR_SINTESI SPB
	INNER JOIN C6StagingPeriodico.RP_Consulenza CON
		ON CON.rete = SPB.RETE
		AND CON.cod_fiscale = SPB.CODFIS
		AND CON.Position_id = SPB.positionid		 
    INNER JOIN C6StagingPeriodico.RP_CATALOGOPRODOTTI KAT
		ON SUBSTRING(SPB.POSITIONID,1,57)=KAT.CATALOGUEID
    LEFT OUTER JOIN C6StagingPeriodico.RP_AnEmittente MIT
        ON  MIT.Emittente =KAT.EMITTENTE
	WHERE SPB.TIPPROD = 'CC' 
	/*
	truncate table C6MartPeriodico.patrimonio_bf
	select top 1 * from #patrimonio
 */
SELECT [RETE]
      ,[COD_FISCALE]
      ,[POSITIONID]
      ,[COD_PRODOTTO]
      ,[ID_CONTRATTO]
      ,[DATA_SOTTOSCRIZIONE]
      ,[COD_STATO]
      ,[NUM_POLIZZA]
      ,[COD_MAF]
      ,[COD_INTERNO]
      ,[COD_SOTTOPRODOTTO]
      ,[TIPO_PRODOTTO]
      ,[NOME_PROGETTO]
      ,[ID_AREA]
      ,[DATA_OPERAZIONE]
      ,[DATA_VALUTA]
      ,[DATA_REGOLAMENTO]
      ,[QUANTITA]
      ,[PREZZO]
      ,[CTV]
      ,[CTV_NOMINALE]
      ,[VERSATO_NETTO]
      ,[PARTVIA_DISINV]
      ,[DATA_PARTVIA_DISINV]
      ,[PARTVIA_INV]
      ,[REND_SOTTOSCRIZIONE]
      ,[REND_TRIMESTRE]
      ,[REND_ANNO]
      ,[ID_ELAB]
      ,[TIPO_ELAB]
      ,[STRTYPE]
      ,[COMPLEX]
      ,[EMITTENTE]
      ,[ISIN]
      ,[ChiaveClientePb]
  INTO #PATRIMONIO2
  FROM #PATRIMONIO
	UNION ALL 
	SELECT 
		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 SPB.TIPPROD IN ('DT', 'FI') AND SPB.dtsottos IS NOT NULL THEN SPB.DATAINIZIORENDIMENTO 
--			ELSE SPB.DATADISOTTOSCRIZIONE 
		 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,
		--LTRIM(RTRIM(NEWPOSITION.NOMEPROGETTOPROP)) AS NOME_PROGETTO,
		null AS NOME_PROGETTO,
		--ISNULL(NEWPOSITION.AREAPROP,'Na') AS ID_AREA,
		'Na' AS ID_AREA,
		SPB.DTRIFE AS DATA_OPERAZIONE,
		NULL AS DATA_VALUTA, -- 105 PER FORMAT DD/MM/YYYY
		NULL AS DATA_REGOLAMENTO,
		convert(decimal(15,3),SPB.QUOTE) AS QUANTITA,
		NULL AS PREZZO,
		convert(decimal(15,3),SPB.CTV) AS CTV,
		convert(decimal(15,3),SPB.VALORE) AS CTV_NOMINALE,
        NULL AS VERSATO_NETTO,
		convert(decimal(15,3),SPB.PARTVIA) AS PARTVIA_DISINV,
		SPB.PARTVIAAL AS DATA_PARTVIA_DISINV,
		NULL AS PARTVIA_INV,
		convert(decimal(8,5),SPB.RENDINIZ) AS REND_SOTTOSCRIZIONE,
		NULL AS REND_TRIMESTRE,
		convert(decimal(8,5),SPB.RENDANNO) AS REND_ANNO,
        @ID_ELAB AS ID_ELAB,
		@TIPO_ELAB AS TIPO_ELAB,
        STRTYPE,
        null as COMPLEX,
        null as EMITTENTE,
		SPB.ISIN,
		VCON.CHIAVECLIENTEPB
		FROM C6StagingPeriodico.SPB_CONTR_SINTESI SPB 		
		LEFT JOIN #PATRIMONIO PATR   
			ON	SPB.RETE = PATR.RETE
			AND SPB.CODFIS = PATR.COD_FISCALE
			AND SPB.Positionid = PATR.positionid	
		--LEFT JOIN C6STAGING.RP_vNewPositionProposta NEWPOSITION
		--	ON	SPB.POSITIONID = NEWPOSITION.positionIDProposta
		JOIN C6MartPeriodico.VCONTRATTI VCON   
			ON SPB.RETE = VCON.RETE
			AND SPB.CODFIS = VCON.COD_FISCALE
			--AND VCON.chiaveClientePB  = NEWPOSITION.chiaveClientePB			 
	WHERE SPB.POSITIONID IS NOT NULL
		  AND PATR.POSITIONID IS NULL
    SELECT RETE, COD_FISCALE, POSITIONID, GETDATE()  as dtstamp
    INTO #PATRIMONIO_DUPLICATI
    FROM #PATRIMONIO2
    GROUP BY RETE, COD_FISCALE, POSITIONID
    HAVING COUNT(*) > 1
    INSERT INTO C6MartPeriodico.TEST_QUALITY
    SELECT RETE, COD_FISCALE, 'POSIZIONI DUPLICATE - PATRIMONIO_BF' 
    FROM #PATRIMONIO_DUPLICATI
    GROUP BY RETE, COD_FISCALE
	INSERT INTO C6MartPeriodico.PATRIMONIO_BF 
	SELECT P.* FROM 
	#PATRIMONIO2 P LEFT JOIN #PATRIMONIO_DUPLICATI T
	ON P.RETE = T.RETE
	AND P.COD_FISCALE = T.COD_FISCALE
	--AND P.POSITIONID = T.POSITIONID
	WHERE T.RETE IS NULL
	UPDATE
		LOG_ESECUZIONE
	SET
		Fine = GETDATE(),
		Tipo = 'INSERT',
		Righe = @@ROWCOUNT
	WHERE
		Nome = 'RP_DM_PATRIMONIO_BF' AND
		Inizio = (
			SELECT
				MAX(Inizio)
			FROM
				LOG_ESECUZIONE
			WHERE
				Nome = 'RP_DM_PATRIMONIO_BF'
		)
--
--DROP TABLE #TempPianif
--
DROP table #patrimonio
DROP table #PATRIMONIO2
drop table #PATRIMONIO_DUPLICATI
END