-- =============================================
-- Author:		Paolo Giovanetti
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE procedure [C6MartPeriodico].[DM_ELABORAZIONI_EMAIL_RNA]
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @ID_ELAB INT
	DECLARE @TIPO_ELAB VARCHAR(1)
	SET @ID_ELAB = C6MartPeriodico.GETIDELAB()
	SET @TIPO_ELAB = C6MartPeriodico.GETTIPOELAB()
	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,
		ISNULL(AL.DATA_NASCITA, CL.DTNASCI) 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, CL.DTNASCI), GETDATE()) AS ETA,
		NULL 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,
		AL.CODICE_PB AS ID_PROMOTORE,
		@ID_ELAB AS ID_ELAB,
		@TIPO_ELAB AS TIPO_ELAB
	FROM C6STAGINGPERIODICO.ALL_CF_PB AL
	LEFT JOIN C6STAGINGPERIODICO.WSEIAN2 CL
		ON CL.RETE = AL.RETE_PB
		AND CL.CODFIS = AL.CODICEFISCALE
END