-- =============================================
-- Author:		Paolo Giovanetti
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE procedure [C6MartPeriodico].[DM_ANAG_CLIENTI_PER_GENERAZIONE_MAIL]
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
		(Nome, Inizio, Nota)
	VALUES (
		'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
		AL.RETE_PB AS RETE,
		AL.CODICEFISCALE AS COD_FISCALE,
		AL.NOME AS NOME,
		AL.COGNOME AS COGNOME,
		AL.DATA_NASCITA AS DATA_NASCITA, 
		CASE
     		WHEN SUBSTRING(AL.CODICE_FISCALE,10,2) < '40' 
				AND AL.MARITALSTATUS = 'Married' THEN 'Coniugato'
     		WHEN SUBSTRING(AL.CODICE_FISCALE,10,2) < '40' 
				AND AL.MARITALSTATUS = 'Divorced' THEN 'Divorziato'
     		WHEN SUBSTRING(AL.CODICE_FISCALE,10,2) < '40' 
				AND AL.MARITALSTATUS = 'Single' THEN 'Libero'
     		WHEN SUBSTRING(AL.CODICE_FISCALE,10,2) > '40' 
				AND AL.MARITALSTATUS = 'Married' THEN 'Coniugata'
     		WHEN SUBSTRING(AL.CODICE_FISCALE,10,2) > '40' 
				AND AL.MARITALSTATUS = 'Divorced' THEN 'Divorziata'
     		WHEN SUBSTRING(AL.CODICE_FISCALE,10,2) > '40' 
				AND AL.MARITALSTATUS = 'Single' THEN 'Libera'
     		ELSE NULL
		END 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(AL.DATA_NASCITA) AS ETA,
		NULL AS PARTITA_IVA,
		NULL AS CITTADINANZA,
		NULL AS PERSONA_FIS_GIUR,
		ISNULL(C6.INDIRIZZO, '') AS INDIRIZZO,
		ISNULL(C6.CITTA, '') AS LOCALITA,
		ISNULL(C6.PROV, '') AS PROVINCIA,
		ISNULL(C6.CAP, '') AS CAP,
		ISNULL(C6.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,
		AL.CODICE_PB AS ID_PROMOTORE,
		@ID_ELAB AS ID_ELAB,
		@TIPO_ELAB AS TIPO_ELAB
	FROM C6STAGINGPERIODICO.ALL_CF_PB AL
	LEFT JOIN
	(SELECT
		Rete, Cod_Fiscale, Data_FineAvanzato, INDIRIZZO, CITTA, PROV, CAP, NAZIONE, ROW_NUMBER() OVER(PARTITION BY Rete, Cod_Fiscale ORDER BY Data_FineAvanzato) AS UltimoAperto
	FROM
		C6Mart.CONTRATTOSEI) C6
		ON AL.RETE_PB = C6.RETE
		AND AL.CODICEFISCALE = C6.COD_FISCALE
	WHERE
		--C6.Data_FINEAvanzato IS NULL
        ISNULL(C6.Data_FineAvanzato, CAST('99991231' AS datetime)) > cast(c6martperiodico.getPreviousTrimestre(getDate(),0) as datetime)--CAST('20100930' AS datetime))
	AND C6.UltimoAperto = 1
/*	
LEFT JOIN C6STAGINGPERIODICO.WSEIAN CL
		ON CL.RETE = AL.RETE_PB
		AND CL.CODFIS = AL.CODICEFISCALE
	WHERE CL.DTTRIM = (
		SELECT MAX(DTTRIM)
		FROM C6StagingPeriodico.WSEIAN
	)
*/
	-- Log esecuzione
	UPDATE
		LOG_ESECUZIONE
	SET
		Fine = GETDATE(),
		Tipo = 'INSERT',
		Righe = @@ROWCOUNT
	WHERE
		Nome = 'DM_ANAG_CLIENTI_PER_GENERAZIONE_MAIL' AND
		Inizio = (
			SELECT
				MAX(Inizio)
			FROM
				LOG_ESECUZIONE
			WHERE
				Nome = 'DM_ANAG_CLIENTI_PER_GENERAZIONE_MAIL'
		)
END