-- =============================================
-- Author:		Paolo Giovanetti
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
--select count(*) from c6mart.anag_clienti
CREATE procedure [C6Mart].[RP_DM_ANAG_CLIENTI]
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @ID_ELAB INT
	DECLARE @TIPO_ELAB VARCHAR(1)
	SET @ID_ELAB = C6MART.GETIDELAB()
	SET @TIPO_ELAB = C6MART.GETTIPOELAB()
	-- Log esecuzione
	INSERT INTO LOG_ESECUZIONE
		(Nome, Inizio, Nota)
	VALUES (
		'RP_DM_ANAG_CLIENTI',
		GETDATE(),
		'Elaborazione giornaliera'
	)
--	DELETE C6MART.ANAG_CLIENTI 
--	FROM C6MART.ANAG_CLIENTI
--	INNER JOIN c6staging.RP_Cliente
--		ON ANAG_CLIENTI.chiaveCliente = RP_Cliente.chiaveCliente
--		--ON RP_ANAG_CLIENTI.RETE = RP_Cliente.RETE_PB
--		--AND RP_ANAG_CLIENTI.COD_FISCALE = RP_Cliente.codFis
	truncate table C6MART.ANAG_CLIENTI
	-- Log esecuzione
	UPDATE
		LOG_ESECUZIONE
	SET
		Fine = GETDATE(),
		Tipo = 'DELETE',
		Righe = @@ROWCOUNT
	WHERE
		Nome = 'RP_DM_ANAG_CLIENTI' AND
		Inizio = (
			SELECT
				MAX(Inizio)
			FROM
				LOG_ESECUZIONE
			WHERE
				Nome = 'RP_DM_ANAG_CLIENTI'
		)
	INSERT INTO LOG_ESECUZIONE
		(Nome, Inizio, Nota)
	VALUES (
		'RP_DM_ANAG_CLIENTI',
		GETDATE(),
		'Elaborazione giornaliera'
	)
	INSERT INTO [C6Mart].[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
		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,
		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 [C6Mart].[RP_ANAG_CLIENTI]
	FROM 
		C6Mart.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
		and p.dtfine>getdate()
		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')
		and p.dtfine>getdate()
		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
	WHERE
		VCONTRATTI.DATA_FINEAVANZATO > getDate()
		--AND ISNULL(INVIOBENVENUTO,'N') = 'N'
	-- Log esecuzione
	UPDATE
		LOG_ESECUZIONE
	SET
		Fine = GETDATE(),
		Tipo = 'INSERT',
		Righe = @@ROWCOUNT
	WHERE
		Nome = 'RP_DM_ANAG_CLIENTI' AND
		Inizio = (
			SELECT
				MAX(Inizio)
			FROM
				LOG_ESECUZIONE
			WHERE
				Nome = 'RP_DM_ANAG_CLIENTI'
		)
END