-- =============================================
-- Author:		Paolo Giovanetti  / TRINGALI PER FIDUCIARIE
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE procedure [C6MartPeriodico].[RP_DM_ANAG_CLIENTI_PER_GENERAZIONE_MAIL_20190612]
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_ANAG_CLIENTI_PER_GENERAZIONE_MAIL',
		GETDATE(),
		'Elaborazione trimestrale'
	)
	TRUNCATE TABLE C6MartPeriodico.ANAG_CLIENTI 
	INSERT INTO C6MartPeriodico.[ANAG_CLIENTI]
	(
		[RETE]
		,[COD_FISCALE]
		,[NOME]
		,[COGNOME]
		,[DATA_NASCITA]
		,[STATO_CIVILE]
		,[NUM_COMPONENTI]
		,[NUM_FIGLI]
		,[ETA]
		,[PARTITA_IVA]
		,[CITTADINANZA]
		,[PERSONA_FIS_GIUR]
		,[INDIRIZZO]
		,[LOCALITA]
		,[PROVINCIA]
		,[CAP]
		,[NAZIONE]
		,[PRESSO]
		,[TEL_RECAPITO]
		,[TEL_CELL]
		,[E_MAIL]
		,[DATA_INIZIO]
		,[DATA_FINE]
		,[STATO]
		,[RAG_SOC]
		,[SESSO]
		,[ID_PROMOTORE]
		,[ID_ELAB]
		,[TIPO_ELAB])
	SELECT
		distinct
--		tabNucleo.chiaveCliente,
--		tabNucleo.clienteConParentela AS chiaveClientePB,
		VCONTRATTI.RETE AS RETE,
		VCONTRATTI.COD_FISCALE AS COD_FISCALE,
		ISNULL(SPB_CLIENTI.NOME,CLI.nome) AS NOME,
		ISNULL(SPB_CLIENTI.COGNOME,CLI.cognome) AS COGNOME,
		ISNULL(tabNucleo.dtNascita,SPB_CLIENTI.DATADINASCITA) AS DATA_NASCITA,
		--CASE
 	--		WHEN SUBSTRING(ALL_CF_PB.CODICE_FISCALE,10,2) < '40' 
		--		AND ALL_CF_PB.MARITALSTATUS = 'Married' THEN 'Coniugato'
 	--		WHEN SUBSTRING(ALL_CF_PB.CODICE_FISCALE,10,2) < '40' 
		--		AND ALL_CF_PB.MARITALSTATUS = 'Divorced' THEN 'Divorziato'
 	--		WHEN SUBSTRING(ALL_CF_PB.CODICE_FISCALE,10,2) < '40' 
		--		AND ALL_CF_PB.MARITALSTATUS = 'Single' THEN 'Libero'
 	--		WHEN SUBSTRING(ALL_CF_PB.CODICE_FISCALE,10,2) > '40' 
		--		AND ALL_CF_PB.MARITALSTATUS = 'Married' THEN 'Coniugata'
 	--		WHEN SUBSTRING(ALL_CF_PB.CODICE_FISCALE,10,2) > '40' 
		--		AND ALL_CF_PB.MARITALSTATUS = 'Divorced' THEN 'Divorziata'
 	--		WHEN SUBSTRING(ALL_CF_PB.CODICE_FISCALE,10,2) > '40' 
		--		AND ALL_CF_PB.MARITALSTATUS = 'Single' THEN 'Libera'
 	--		ELSE NULL
		--END AS STATO_CIVILE,
		NULL  AS STATO_CIVILE,
		tabNucleo.componentiNucleo AS NUM_COMPONENTI,
		tabFigli.numFigli AS NUM_FIGLI,
		FLOOR(DateDiff(Day, ISNULL(tabNucleo.dtNascita,SPB_CLIENTI.DATADINASCITA), GetDate()) / 365.25) AS ETA,
		NULL AS PARTITA_IVA,
		SPB_CLIENTI.CITTADINANZA AS CITTADINANZA,
		NULL AS PERSONA_FIS_GIUR,
		ISNULL(SPB_INDIRIZZI.INDIRIZZO, '') AS INDIRIZZO,
		ISNULL(SPB_INDIRIZZI.CITTA, '') AS LOCALITA,
		ISNULL(SPB_INDIRIZZI.PROVINCIA, '') AS PROVINCIA,
		ISNULL(SPB_INDIRIZZI.CAP, '') AS CAP,
		ISNULL(SPB_INDIRIZZI.NAZIONE, '') AS NAZIONE,
		'' AS PRESSO,
		NULL AS TEL_RECAPITO,
		NULL AS TEL_CELL,
		VCONTRATTI.EMAIL AS E_MAIL,
		SPB_CLIENTI.DATAATTIVAZIONECLIENTE AS DATA_INIZIO,
		NULL AS DATA_FINE,
		NULL AS STATO,
		NULL AS RAG_SOC,
		CASE
 			WHEN SUBSTRING(VCONTRATTI.COD_FISCALE,10,2) < '40' THEN 'M'
			ELSE 'F'
		END AS SESSO,
		VCONTRATTI.COD_AGENTE AS ID_PROMOTORE,
		@ID_ELAB AS ID_ELAB,
		@TIPO_ELAB AS TIPO_ELAB
	--INTO [C6MartPeriodico].[RP_ANAG_CLIENTI]
	FROM 
		C6Martperiodico.rp_VCONTRATTIpergenerazionereport VCONTRATTI
	LEFT JOIN 
		(select 
		COUNT(*) as componentiNucleo,
		chiaveClientePB1 as clienteConParentela,
		cl.chiaveCliente,
		cl.nome,
		cl.cognome,
		cl.dtNascita
		from 
		C6staging.RP_Parentela p
		inner join C6staging.RP_GradoParentela g
		on p.idparentela = g.idparentela
		inner join C6Staging.RP_ClientePB c
		on c.chiaveClientePB = p.chiaveClientePB1
		inner join C6Staging.RP_Cliente cl
		on c.chiaveCliente = cl.chiaveCliente
		group by 
		chiaveClientePB1, cl.chiaveCliente, cl.nome, cl.cognome, cl.dtNascita) tabNucleo
	ON
		VCONTRATTI.chiaveClientePB = tabNucleo.clienteConParentela
	LEFT JOIN
		(select 
		COUNT(*) as numFigli,
		chiaveClientePB1 as clienteConParentela,
		cl.chiaveCliente,
		cl.nome,
		cl.cognome,
		cl.dtNascita
		from 
		C6staging.RP_Parentela p
		inner join C6staging.RP_GradoParentela g
		on p.idparentela = g.idparentela
		inner join C6Staging.RP_ClientePB c
		on c.chiaveClientePB = p.chiaveClientePB1
		inner join C6Staging.RP_Cliente cl
		on c.chiaveCliente = cl.chiaveCliente
		where
		g.descParentela in ('Figlio','Figlia','Figlio del partner','figlia del partner')
		group by 
		chiaveClientePB1, cl.chiaveCliente, cl.nome, cl.cognome, cl.dtNascita) tabFigli
	ON 
		VCONTRATTI.chiaveClientePB = tabFigli.clienteConParentela
	AND
		tabFigli.clienteConParentela = tabNucleo.clienteConParentela
	LEFT OUTER JOIN 
		C6STAGING.SPB_CLIENTI 
	ON 
			C6STAGING.SPB_CLIENTI.CODICERETE = VCONTRATTI.RETE
			AND C6STAGING.SPB_CLIENTI.CODICEFISCALE = VCONTRATTI.COD_FISCALE 
	LEFT OUTER JOIN 
		C6STAGING.SPB_INDIRIZZI 
		ON 
			C6STAGING.SPB_INDIRIZZI.RETE = VCONTRATTI.RETE
			AND C6STAGING.SPB_INDIRIZZI.CODICEFISCALE = VCONTRATTI.COD_FISCALE
	JOIN 
		C6MART.CONTRATTOSEI_METADATI METADATI
		ON
			VCONTRATTI.CHIAVECLIENTEPB = METADATI.CHIAVECLIENTEPB
	LEFT JOIN c6staging.RP_Cliente CLI
		ON
			VCONTRATTI.COD_FISCALE = CASE 
										WHEN (LEN(CLI.CODFIS) = 16) THEN CLI.CODFIS
										WHEN (LEN(CLI.CODMAN) = 0 AND LEN(CLI.PIVA) > 0) THEN CLI.PIVA
										ELSE 'FF@'+CODMAN
									  END
/*
	SELECT
		AL.RETE AS RETE,
        [dbo].[FFCODICEFISCALE] (AL.CODFIS,AL.CODMAN,AL.PIVA)AS COD_FISCALE,
		RTRIM(ISNULL(CL.NOME,AL.NOMECLIENTE)) AS NOME,
		RTRIM(ISNULL(CL.COGNOME,AL.COGNOMECLIENTE)) AS COGNOME,
		ISNULL(
			AL.DTNASCITA,
			CASE
				WHEN ISDATE(CL.DTNASCI) = 0 THEN NULL
				ELSE CAST(CAST(CL.DTNASCI AS VARCHAR(8)) AS DATETIME)
			END) AS DATA_NASCITA, 
          NULL AS STATO_CIVILE,
		NULL AS NUM_COMPONENTI,
		NULL AS NUM_FIGLI,
		/*DATEDIFF(YY,
			ISNULL(
				AL.DATA_NASCITA,
				CASE
					WHEN ISDATE(CL.DTNASCI) = 0 THEN NULL
					ELSE CAST(CAST(CL.DTNASCI AS VARCHAR(8)) AS DATETIME)
				END), 
			GETDATE()) AS ETA,*/
		dbo.Eta(ISNULL(
			AL.DTNASCITA,
			CASE
				WHEN ISDATE(CL.DTNASCI) = 0 THEN NULL
				ELSE CAST(CAST(CL.DTNASCI AS VARCHAR(8)) AS DATETIME)
			END)
		) AS ETA,
		[dbo].[FFPIVA] (AL.CODMAN, AL.PIVA) AS PARTITA_IVA,
		NULL AS CITTADINANZA,
		NULL AS PERSONA_FIS_GIUR,
		ISNULL(CL.INDIRIZZO, '') AS INDIRIZZO,
		ISNULL(CL.CITTA, '') AS LOCALITA,
		ISNULL(CL.PROVINCIA, '') AS PROVINCIA,
		ISNULL(CL.CAP, '') AS CAP,
		ISNULL(CL.NAZIONE, '') AS NAZIONE,
		'' AS PRESSO,
		NULL AS TEL_RECAPITO,
		NULL AS TEL_CELL,
		NULL AS E_MAIL,
		NULL AS DATA_INIZIO,
		NULL AS DATA_FINE,
		NULL AS STATO,
		NULL AS RAG_SOC,
		--NULL AS SESSO,
		CASE
 			WHEN SUBSTRING(AL.CODFIS,10,2) < '40' THEN 'M'
			ELSE 'F'
		END AS SESSO,
		AL.AGENTE AS ID_PROMOTORE,
		@ID_ELAB AS ID_ELAB,
		@TIPO_ELAB AS TIPO_ELAB
	FROM C6STAGINGPERIODICO.RP_InfoCliente AL
--	LEFT JOIN C6STAGINGPERIODICO.WSEIAN CL
--		ON CL.RETE = AL.RETE
--		AND ( ltrim(rtrim(CL.CODFIS)) = CASE WHEN ISNULL(AL.CODFIS, '') = '' THEN AL.PIVA ELSE AL.CODFIS END )
--        AND (ISNULL(CL.CODMAN, '') = ISNULL(AL.CODMAN, ''))
--	WHERE CL.DTTRIM = (
--		SELECT MAX(DTTRIM)
--		FROM C6StagingPeriodico.WSEIAN
--	) 
*/
	-- Log esecuzione
	UPDATE
		LOG_ESECUZIONE_PERIODICO
	SET
		Fine = GETDATE(),
		Tipo = 'INSERT',
		Righe = @@ROWCOUNT
	WHERE
		Nome = 'RP_DM_ANAG_CLIENTI_PER_GENERAZIONE_MAIL' AND
		Inizio = (
			SELECT
				MAX(Inizio)
			FROM
				LOG_ESECUZIONE_PERIODICO
			WHERE
				Nome = 'RP_DM_ANAG_CLIENTI_PER_GENERAZIONE_MAIL'
		)
END