-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
--[C6MartPeriodico].[TEMP_RP_DM_DETTAGLIO_ASUL]
CREATE PROCEDURE [C6MartPeriodico].[RP_DM_DETTAGLIO_ASUL]
AS
BEGIN
	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_PERIODICO
		(Nome, Inizio, Nota)
	VALUES (
		'RP_DM_DETTAGLIO_ASUL_1',
		GETDATE(),
		'Elaborazione trimestrale'
	)
    DECLARE @MaxDTTrim AS decimal(9,0)
    SELECT @MaxDTTrim = MAX(DTTRIM) FROM C6STAGINGPERIODICO.WSEIUL
	TRUNCATE TABLE C6MartPeriodico.DETTAGLIO_ASUL
	SELECT --********* ATTENZIONE ALLA GESTIONE DEI PRODOTTI ASSICURATIVI SKANDIA
		   --********* DA AGGIUNGERE
		   --********* SI TROVANO CON CONDIZIONE COD_ISIN_SOTTOSTANTE IS NOT NULL
		   --********* ENTRO SUL CATALOGO CON COD_ISIN_SOTTOSTANTE, COD_MAF_SOTTOSTANTE  
		SPB_CONTR_SINTESI.RETE AS RETE,
		SPB_CONTR_SINTESI.CODFIS AS COD_FISCALE,
		--ISNULL(
		--	C6StagingPeriodico.RP_Info_DaProdottoCSaCatalogo(SPB_CONTR_SINTESI.TIPPROD, SPB_ASS_UNIT.CODINT, SPB_ASS_UNIT.MAF, '', SPB_ASS_UNIT.SUBPROD), 
		--	SUBSTRING(SPB_CONTR_SINTESI.POSITIONID, 1, 57)) AS COD_PRODOTTO,
		CAST('' AS VARCHAR(255)) AS COD_PRODOTTO,
		--ISNULL(SUBSTRING(SPB_CONTR_SINTESI.POSITIONID, 1, 57),
  --          C6StagingPeriodico.RP_Info_DaProdottoCSaCatalogo(SPB_CONTR_SINTESI.TIPPROD, SPB_ASS_UNIT.CODINT, SPB_ASS_UNIT.MAF, '', SPB_ASS_UNIT.SUBPROD)
  --            ) AS COD_PRODOTTO,
		SPB_ASS_UNIT.CODCONF AS ID_CONTRATTO,
		SPB_ASS_UNIT.SUBPROD AS COD_SOTTOPRODOTTO,
		SPB_ASS_UNIT.CODINT AS COD_INTERNO,
		SPB_ASS_UNIT.CTV AS CTV,
        CASE
			WHEN ISDATE(SPB_ASS_UNIT.DTRIFE) = 0 THEN NULL
			ELSE CAST(CAST(SPB_ASS_UNIT.DTRIFE AS VARCHAR(8)) AS DATETIME)
		END AS DATA_RIF,
		SPB_ASS_UNIT.ISINFIT AS COD_ISIN_FITTIZIO,
		SPB_ASS_UNIT.ISIN AS COD_ISIN_SOTT,
		SPB_ASS_UNIT.MAF AS COD_MAF_SOTT,
		@ID_ELAB AS ID_ELAB,
		@TIPO_ELAB AS TIPO_ELAB,
		SPB_CONTR_SINTESI.POSITIONID AS SPB_POSITIONID
    INTO #TEMP_DETTAGLIO_ASUL		
	FROM 
		C6STAGINGPERIODICO.SPB_CONTR_SINTESI 
	INNER JOIN
		C6STAGINGPERIODICO.WSEIUL SPB_ASS_UNIT 
		ON SPB_CONTR_SINTESI.CODCONF = SPB_ASS_UNIT.CODCONF
	LEFT OUTER JOIN
		C6STAGINGPERIODICO.RP_CatalogoProdotti CATITEMREF 
		ON SPB_ASS_UNIT.CODINT = CATITEMREF.CodInterno
		AND SPB_ASS_UNIT.SUBPROD = CATITEMREF.CodSottoprodotto
		-------------------> Mod del 23/1/2017 - ripristinata and sulla visibilità precedentemente commentata
        AND CATITEMREF.Visibilita = 1 
		-------------------> Fine Mod del 23/1/2017
		AND (SPB_ASS_UNIT.ISIN = '' --and CATITEMREF.CodIsin is null --= '$'
              OR (
			SPB_ASS_UNIT.ISIN = CATITEMREF.CodAdeguatezza
			AND 
			SPB_ASS_UNIT.MAF = CATITEMREF.CodMaf)
		)
WHERE SPB_CONTR_SINTESI.tipprod ='ASUL'
		AND SPB_ASS_UNIT.DTTRIM = @MaxDTTrim
	-------------------> Mod del 23/1/2017 -Commentata and sulla visibilità
	--AND CATITEMREF.Visibilita = 1
	-------------------> Fine Mod del 23/1/2017
    and SPB_CONTR_SINTESI.CTV <> 0
	-- Log esecuzione
	UPDATE
		LOG_ESECUZIONE_PERIODICO
	SET
		Fine = GETDATE(),
		Tipo = 'INSERT',
		Righe = @@ROWCOUNT
	WHERE
		Nome = 'RP_DM_DETTAGLIO_ASUL_1' AND
		Inizio = (
			SELECT
				MAX(Inizio)
			FROM
				LOG_ESECUZIONE_PERIODICO
			WHERE
				Nome = 'RP_DM_DETTAGLIO_ASUL_1'
		)
	-- Log esecuzione
	INSERT INTO LOG_ESECUZIONE_PERIODICO
		(Nome, Inizio, Nota)
	VALUES (
		'RP_DM_DETTAGLIO_ASUL_2',
		GETDATE(),
		'Elaborazione trimestrale'
	)
	declare @visible int 
	-- FerAcu 20250226 Variante dell'update progressiva più rigorosa che  entra sul catalogo anche per ISIN, aggiunta per gestire cattile valorizzazioni sui SottoProdotti LSEL
	-- FerAcu 20250226 NEW
	set @visible = 1
	while @visible >= 0
		begin
			UPDATE #TEMP_DETTAGLIO_ASUL
			SET COD_PRODOTTO = CAT.CATALOGUEID
			FROM 
					#TEMP_DETTAGLIO_ASUL
			--********************************************************************************
					INNER JOIN  
					(
						select * from c6stagingperiodico.rp_catalogoprodotti where visibilita = 1
					)CAT
						on  1=1
					   -- FerAcu 20250225 aggiunto criterio su ISIN con capisco perche l'Update non finiva in errore, riportavas un valore a caso ...
					   and cat.CodIsin = case when COD_ISIN_SOTT != '' Then COD_ISIN_SOTT Else COD_ISIN_FITTIZIO End 
					   and (
							--caso 1
							(COD_SOTTOPRODOTTO not in ('LIQUI', 'VAR5', 'VAR10', 'VAR15')
							and isnull(cat.CODINTERNO,'') = isnull(COD_INTERNO,'')
							and isnull(cat.CODSOTTOPRODOTTO,'') = isnull(COD_SOTTOPRODOTTO,''))
							or 
							--else caso 1 
							(COD_SOTTOPRODOTTO in ('LIQUI', 'VAR5', 'VAR10', 'VAR15')
							and isnull(cat.CODINTERNO,'') = isnull(COD_INTERNO,'')
							and isnull(cat.CODSOTTOPRODOTTO,'') = isnull(COD_SOTTOPRODOTTO,'')
							and cat.TIPOPRODOTTO = 'ASUL')
						  )
			--***********************************************************************************		
			WHERE COD_PRODOTTO = ''
			set @visible = @visible - 1
			end
	-- FerAcu 20250226 OLD con verifica sul singlolo match
	set @visible = 1
	while @visible >= 0
		begin
			UPDATE #TEMP_DETTAGLIO_ASUL
			SET COD_PRODOTTO = CAT.CATALOGUEID
			FROM 
					#TEMP_DETTAGLIO_ASUL
			--********************************************************************************
					CROSS APPLY  
					(
						select *
						     , COUNT(1) OVER(PARTITION BY cp.CODINTERNO, cp.CODSOTTOPRODOTTO, cp.TIPOPRODOTTO ) AS count_match
						from c6stagingperiodico.rp_catalogoprodotti cp
						where visibilita = 1
						   and (
								--caso 1
								(COD_SOTTOPRODOTTO not in ('LIQUI', 'VAR5', 'VAR10', 'VAR15')
								and isnull(cp.CODINTERNO,'') = isnull(COD_INTERNO,'')
								and isnull(cp.CODSOTTOPRODOTTO,'') = isnull(COD_SOTTOPRODOTTO,''))
								or 
								--else caso 1 
								(COD_SOTTOPRODOTTO in ('LIQUI', 'VAR5', 'VAR10', 'VAR15')
								and isnull(cp.CODINTERNO,'') = isnull(COD_INTERNO,'')
								and isnull(cp.CODSOTTOPRODOTTO,'') = isnull(COD_SOTTOPRODOTTO,'')
								and cp.TIPOPRODOTTO = 'ASUL')
							  )
					) CAT
			--***********************************************************************************		
			WHERE COD_PRODOTTO = ''
			  -- FerAcu 20250226
			  and count_match = 1
			set @visible = @visible - 1
			end
	-------------------> Mod del 23/1/2017 
		--aggiorno tutti quelli che non hanno almeno un prodotto in catalogo con visibilità 1
		-- FerAcu 20250226 NEW
		set @visible = 1
		while @visible >= 0
			begin
				UPDATE #TEMP_DETTAGLIO_ASUL
				SET COD_PRODOTTO = CAT.CATALOGUEID
				FROM 
					   #TEMP_DETTAGLIO_ASUL
				--********************************************************************************
					   INNER JOIN  c6stagingperiodico.rp_catalogoprodotti cat
							on  1=1
					   -- FerAcu 20250225 aggiunto ctriterio su ISIN con capisco perche l'Update non finiva in errore, riportavas un valore a caso ...
					   and cat.CodIsin = case when COD_ISIN_SOTT != '' Then COD_ISIN_SOTT Else COD_ISIN_FITTIZIO End 
					   and (
							 isnull(cat.CODINTERNO,'') = isnull(COD_INTERNO,'')
							and isnull(cat.CODSOTTOPRODOTTO,'') = isnull(COD_SOTTOPRODOTTO,'')
							and cat.TIPOPRODOTTO = 'ASUL'
						   )
				--***********************************************************************************		
				WHERE COD_PRODOTTO = ''
				set @visible = @visible - 1
			end
		-- FerAcu 20250226 OLD con verifica sul singlolo match
		set @visible = 1
		while @visible >= 0
			begin
				UPDATE #TEMP_DETTAGLIO_ASUL
				SET COD_PRODOTTO = CAT.CATALOGUEID
				FROM #TEMP_DETTAGLIO_ASUL
				--********************************************************************************
					CROSS APPLY  
					(
						select *
						     , COUNT(1) OVER(PARTITION BY cp.CODINTERNO, cp.CODSOTTOPRODOTTO, cp.TIPOPRODOTTO ) AS count_match
						from c6stagingperiodico.rp_catalogoprodotti cp
						where 1=1
					      and (
   								 isnull(cp.CODINTERNO,'') = isnull(COD_INTERNO,'')
								and isnull(cp.CODSOTTOPRODOTTO,'') = isnull(COD_SOTTOPRODOTTO,'')
								and cp.TIPOPRODOTTO = 'ASUL'
						      )
					) CAT
				--***********************************************************************************		
				WHERE COD_PRODOTTO = ''
			      -- FerAcu 20250226
				  and count_match = 1
				set @visible = @visible - 1
			end
	-------------------> Fine Mod del 23/1/2017 
	-- Log esecuzione
	UPDATE
		LOG_ESECUZIONE_PERIODICO
	SET
		Fine = GETDATE(),
		Tipo = 'INSERT',
		Righe = @@ROWCOUNT
	WHERE
		Nome = 'RP_DM_DETTAGLIO_ASUL_2' AND
		Inizio = (
			SELECT
				MAX(Inizio)
			FROM
				LOG_ESECUZIONE_PERIODICO
			WHERE
				Nome = 'RP_DM_DETTAGLIO_ASUL_2'
		)
	-- Log esecuzione
	INSERT INTO LOG_ESECUZIONE_PERIODICO
		(Nome, Inizio, Nota)
	VALUES (
		'RP_DM_DETTAGLIO_ASUL_3',
		GETDATE(),
		'Elaborazione trimestrale'
	)
	-------------> modifica del 23/01/2017 - questa update non è corretta - sostituiamo con scarto su data quality
		--UPDATE #TEMP_DETTAGLIO_ASUL
		--SET COD_PRODOTTO = SPB_POSITIONID
		--WHERE COD_PRODOTTO = ''
	select 
		14 as idTipoCtr,
		rete as rete,
		cod_fiscale as codfis,
		(select DescSegnalazione  from   c6martperiodico.tb_tipocontrollo where idtipocontrollo = 14) + temp.cod_interno + '/' + temp.cod_sottoprodotto + '/'  +temp.id_contratto
		 as descrizione,
		@ID_ELAB as idelab,
		'' as richiesta,
		0 as idfase,
		getdate() as dataelab
	into #TbErrori
	from  #TEMP_DETTAGLIO_ASUL temp
	WHERE temp.COD_PRODOTTO = ''
	Insert into C6MartPeriodico.TB_SegnalazioniDataQuality
    select * from #TbErrori
	-------------> fine modifica del 23/01/2017 
	-- Log esecuzione
	UPDATE
		LOG_ESECUZIONE_PERIODICO
	SET
		Fine = GETDATE(),
		Tipo = 'INSERT',
		Righe = @@ROWCOUNT
	WHERE
		Nome = 'RP_DM_DETTAGLIO_ASUL_3' AND
		Inizio = (
			SELECT
				MAX(Inizio)
			FROM
				LOG_ESECUZIONE_PERIODICO
			WHERE
				Nome = 'RP_DM_DETTAGLIO_ASUL_3'
		)
	-- Log esecuzione
	INSERT INTO LOG_ESECUZIONE_PERIODICO
		(Nome, Inizio, Nota)
	VALUES (
		'RP_DM_DETTAGLIO_ASUL_4',
		GETDATE(),
		'Elaborazione trimestrale'
	)
	INSERT INTO [C6MartPeriodico].[DETTAGLIO_ASUL]
		   ([RETE]
		   ,[COD_FISCALE]
		   ,[COD_PRODOTTO]
		   ,[ID_CONTRATTO]
		   ,[COD_SOTTOPRODOTTO]
		   ,[COD_INTERNO]
		   ,[CTV]
		   ,[DATA_RIF]
		   ,[COD_ISIN_FITTIZIO]
		   ,[COD_ISIN_SOTT]
		   ,[COD_MAF_SOTT]
		   ,[ID_ELAB]
		   ,[TIPO_ELAB]
		   ,[POSITION_ID]
		--   ,[CHIAVEPROGETTO]
		   )
	SELECT
		    d.[RETE]
		   ,d.[COD_FISCALE]
		   ,d.[COD_PRODOTTO]
		   ,d.[ID_CONTRATTO]
		   ,d.[COD_SOTTOPRODOTTO]
		   ,d.[COD_INTERNO]
		   ,d.[CTV]
		   ,d.[DATA_RIF]
		   ,d.[COD_ISIN_FITTIZIO]
		   ,d.[COD_ISIN_SOTT]
		   ,d.[COD_MAF_SOTT]
		   ,d.[ID_ELAB]
		   ,d.[TIPO_ELAB]
		   ,D.[SPB_POSITIONID]
		 --  ,p.[Chiaveprogetto]
     FROM #TEMP_DETTAGLIO_ASUL d
	-- left join C6MartPeriodico.PATRIMONIO_BF patr on
	-- patr.POSITION_ID=d.SPB_POSITIONID
		update d
	set d.ChiaveProgetto=patr.CHIAVE_PROGETTO
	from C6MartPeriodico.DETTAGLIO_ASUL d left join C6MartPeriodico.PATRIMONIO_BF patr on
	 patr.POSITION_ID=d.POSITION_ID
    DROP TABLE #TEMP_DETTAGLIO_ASUL
	-- Log esecuzione
	UPDATE
		LOG_ESECUZIONE_PERIODICO
	SET
		Fine = GETDATE(),
		Tipo = 'INSERT',
		Righe = @@ROWCOUNT
	WHERE
		Nome = 'RP_DM_DETTAGLIO_ASUL_4' AND
		Inizio = (
			SELECT
				MAX(Inizio)
			FROM
				LOG_ESECUZIONE_PERIODICO
			WHERE
				Nome = 'RP_DM_DETTAGLIO_ASUL_4'
		)
END