CREATE procedure [C6Mart].[RP_DM_POPOLACONTRATTI_20170727_VarAdeguatezza] AS
BEGIN
INSERT INTO LOG_ESECUZIONE
		(Nome, Inizio, Nota)
	VALUES (
		'RP_DM_POPOLACONTRATTI',
		GETDATE(),
		'Elaborazione giornaliera'
	)	
-- LOG CAMBIO COD_FISCALE RETE  ***************************************************************************************
 INSERT INTO c6mart.test_logmd
         SELECT 'VARZ CF RETE',GETDATE(),VC.COD_FISCALE,VC.RETE,			  
				NULL AS RIATTIVAZIONE_1,NULL AS TIPOCONTRATTO_1,
				NULL AS RIATTIVAZIONE_2,NULL AS TIPOCONTRATTO_2,
				NULL AS RIATTIVAZIONE_3,NULL AS TIPOCONTRATTO_3,NULL AS INVIO_3, 
				'da ' + VC.cod_fiscale +'-'+ VC.RETE + ' a ' + C6.cod_fiscale +'-'+ C6.RETE	 
	     FROM C6MART.VCONTRATTI VC 
	     JOIN [C6Mart].[vContrattiC6]  C6 
	     ON VC.chiaveClientePB = C6.CHIAVECLIENTEPB
	     WHERE VC.cod_fiscale <> C6.cod_fiscale OR VC.RETE <> C6.RETE 	     
--********************************************************************************************************************	     
TRUNCATE TABLE C6MART.VCONTRATTI
--DELETE C6MART.VCONTRATTI 
--	WHERE chiaveClientePB in (SELECT chiaveClientePB  FROM [C6Mart].[vContrattiC6])
INSERT INTO C6MART.VCONTRATTI
SELECT *  FROM [C6Mart].[vContrattiC6] 
INSERT INTO [C6Mart].[CONTRATTOSEI_METADATI]
           ([CHIAVECLIENTEPB]
           ,[ClienteMIGRATO]
           ,[RISORSENA]
           ,[PROFILOATTIVO]
           ,[NumSolleciti]
           ,[NumRevoche]
           ,[Data_ultimoSollecito]
           ,[Data_ultimaRevoca]
           ,[InvioBenvenuto]
           ,[Tip_riattivazione]
           ,[Tip_contratto])           
     SELECT    
			A.CHIAVECLIENTEPB,
			NULL,
			NULL,
			NULL,
			0,
			0,
			NULL,
			NULL,
			'N',
			A.Riattivazione,
			A.Tip_Contratto
	 FROM C6MART.VCONTRATTI A
	 LEFT JOIN [C6Mart].[CONTRATTOSEI_METADATI] B
		ON A.chiaveClientePB = B.CHIAVECLIENTEPB
	 WHERE B.CHIAVECLIENTEPB IS NULL 
---- FCianfa: INIZIO FORZATURA per errore giornaliero del 31/07/2015, il cliente non è censito in C6Mart.ANAG_CLIENTI
--DELETE  from C6MART.VCONTRATTI
--where rete+cod_fiscale = 'FFF@9700'
---- EN20160315: INIZIO FORZATURA per errore giornaliero del 15/03/2016, il cliente è in C6Mart.ANAG_CLIENTI incompleto e non presente su SIMPB
---- where rete+cod_fiscale = 'FBRBMRA51H53G089M'
----FINE FORZATURA
---- LL: INIZIO FORZATURA per errore giornaliero del 25/03/2016 ----------------------------------------------
   --DELETE  from C6MART.VCONTRATTI
   --where rete+cod_fiscale = 'SFROFMN88T49H501H'
---- LL: INIZIO FORZATURA per Ticket T-1410414-M4B3 (08/04/2016) 
--       Il contratto ha subito un cambio di cf e per errore è stata generata una nuova email di perfez.; 
--       per evitare anche l'eventuale generazione della lettera di benvenuto lo escludiamo
   --DELETE  from C6MART.VCONTRATTI
   --where rete+cod_fiscale = 'FGRMLGU42L14A662R'
----FINE FORZATURA---------------------------------------------------------------------------------------------
---- EN20161230: INIZIO FORZATURA per errore giornaliero del 30/12/2016 ----------------------------------------------
   ----DELETE  from C6MART.VCONTRATTI
   ----where rete+cod_fiscale = 'FMRTRND45C07F930Z'
   ----DELETE  from C6MART.VCONTRATTI
   ----where rete+cod_fiscale = 'FVNARRT65S64Z326I'
   ------per rimediare errore nel giornaliero il 21/03/2017
   DELETE  from C6MART.VCONTRATTI
   where rete+cod_fiscale = 'F04904530633'
   ------per rimediare errore nel giornaliero il 24/05/2017
   DELETE  from C6MART.VCONTRATTI
   where rete+cod_fiscale = 'F02588590402'
   ------per errore del giornaliero il 06/06/2017
  -- DELETE  from C6MART.VCONTRATTI
  -- where rete+cod_fiscale in (
		--'FSCNLRT54T04E613J',
		--'FBRRMNL69S45C957K',
		--'FTBRDAA34M63B455T',
		--'FSNTRNO50C55I342U',
		--'FLMLGNN75D58F918C',
		--'SSTCRRT86H30E058Z'
  -- )
   ------Clienti professionali esclusi dal giornaliero 13/06/2017
   DELETE  from C6MART.VCONTRATTI
   where rete+cod_fiscale in (
   'FMGNGPP55B25G224T',
   'FFF@5844'
   )
----FINE FORZATURA---------------------------------------------------------------------------------------------
-- CASO NUOVO CONTRATTO ***************************************************************************************
INSERT INTO c6mart.test_logmd 
SELECT 'NUOVO CONTRATTO',GETDATE(),VC.COD_FISCALE,VC.RETE,
VC.riattivazione     AS RIATTIVAZIONE_1,VC.tip_contratto AS TIPOCONTRATTO_1,
MD.tip_riattivazione AS RIATTIVAZIONE_2,MD.tip_Contratto AS TIPOCONTRATTO_2,
VC.Riattivazione     AS RIATTIVAZIONE_3,VC.Tip_Contratto AS TIPOCONTRATTO_3,'N' AS INVIO_3,''
FROM [C6Mart].[CONTRATTOSEI_METADATI] MD inner join
         (select VC1.cod_fiscale,VC1.rete,VC1.chiaveClientePB,VC1.Riattivazione,VC1.Tip_Contratto
	     FROM C6MART.VCONTRATTI VC1 
	     JOIN [C6Mart].[CONTRATTOSEI_METADATI]  MC1 
	     ON VC1.chiaveClientePB = MC1.CHIAVECLIENTEPB
	     LEFT JOIN c6mart.gestione_pdf_ftp  PDF1
	     ON SUBSTRING(PDF1.NomeFile, 1, 11) = VC1.CODICECONTRATTO
	     WHERE MC1.INVIOBENVENUTO = 'S' AND PDF1.CODICEFISCALE IS NULL) VC
 ON VC.chiaveClientePB = MD.chiaveClientePB	       
UPDATE [C6Mart].[CONTRATTOSEI_METADATI] SET 
--SELECT     
TIP_RIATTIVAZIONE =VC.Riattivazione, TIP_CONTRATTO=VC.Tip_Contratto
,NumSolleciti=0,NumRevoche=0,Data_ultimoSollecito=null,Data_ultimaRevoca=null,InvioBenvenuto='N' 
FROM [C6Mart].[CONTRATTOSEI_METADATI] MD inner join
         (select VC1.chiaveClientePB,VC1.Riattivazione,VC1.Tip_Contratto
	     FROM C6MART.VCONTRATTI VC1 
	     JOIN [C6Mart].[CONTRATTOSEI_METADATI]  MC1 
	     ON VC1.chiaveClientePB = MC1.CHIAVECLIENTEPB
	     LEFT JOIN c6mart.gestione_pdf_ftp  PDF1
	     ON SUBSTRING(PDF1.NomeFile, 1, 11) = VC1.CODICECONTRATTO
	     WHERE MC1.INVIOBENVENUTO = 'S' AND PDF1.CODICEFISCALE IS NULL) VC
 ON VC.chiaveClientePB = MD.chiaveClientePB
--  CASO RIATTIVAZIONE   ***************************************************************************************
INSERT INTO c6mart.test_logmd 
SELECT 'RIATTIVAZIONE',GETDATE(),VC.COD_FISCALE,VC.RETE,
VC.riattivazione AS RIATTIVAZIONE_1,VC.tip_contratto AS TIPOCONTRATTO_1,
MD.Tip_riattivazione AS RIATTIVAZIONE_2,MD.tip_Contratto AS TIPOCONTRATTO_2,
1 AS RIATTIVAZIONE_3,VC.TIP_CONTRATTO AS TIPOCONTRATTO_3,'N' AS INVIO_3,''
FROM C6MART.VCONTRATTI VC JOIN [C6Mart].[CONTRATTOSEI_METADATI]  MD 
ON VC.chiaveClientePB = MD.CHIAVECLIENTEPB
AND VC.riattivazione = 1 AND VC.DATA_FINEAVANZATO > GETDATE() AND MD.Tip_riattivazione = 0
UPDATE [C6Mart].[CONTRATTOSEI_METADATI] SET
--SELECT  
 TIP_RIATTIVAZIONE = 1, TIP_CONTRATTO=VC.TIP_CONTRATTO
,NumSolleciti=0,NumRevoche=0,Data_ultimoSollecito=null,Data_ultimaRevoca=null,InvioBenvenuto='N'
FROM C6MART.VCONTRATTI VC JOIN [C6Mart].[CONTRATTOSEI_METADATI]  MC ON VC.chiaveClientePB = MC.CHIAVECLIENTEPB
AND VC.riattivazione = 1 AND VC.DATA_FINEAVANZATO  > GETDATE() AND MC.Tip_riattivazione = 0
--  CASO FINE RIATTIVAZIONE   ***************************************************************************************
INSERT INTO c6mart.test_logmd 
SELECT 'FINE RIATTIVAZIONE',GETDATE(),VC.COD_FISCALE,VC.RETE,
VC.riattivazione AS RIATTIVAZIONE_1,VC.tip_contratto AS TIPOCONTRATTO_1,
MD.Tip_riattivazione AS RIATTIVAZIONE_2,MD.tip_Contratto AS TIPOCONTRATTO_2,
0 AS RIATTIVAZIONE_3,null AS TIPOCONTRATTO_3,null AS INVIO_3,''
FROM C6MART.VCONTRATTI VC JOIN [C6Mart].[CONTRATTOSEI_METADATI]  MD 
ON VC.chiaveClientePB = MD.CHIAVECLIENTEPB
AND VC.riattivazione = 0 AND VC.DATA_FINEAVANZATO <= GETDATE() AND MD.Tip_riattivazione <> 0
UPDATE [C6Mart].[CONTRATTOSEI_METADATI] SET
-- SELECT  
 TIP_RIATTIVAZIONE = 0
FROM C6MART.VCONTRATTI VC JOIN [C6Mart].[CONTRATTOSEI_METADATI]  MD ON VC.chiaveClientePB = MD.CHIAVECLIENTEPB
AND VC.riattivazione = 0 AND VC.DATA_FINEAVANZATO <= GETDATE() AND MD.Tip_riattivazione <> 0
--  CASO UPGRADE   ***************************************************************************************
INSERT INTO c6mart.test_logmd 
SELECT 'UPGRADE',GETDATE(),COD_FISCALE,RETE,
VC.riattivazione     AS RIATTIVAZIONE_1,VC.tip_contratto AS TIPOCONTRATTO_1,
MD.Tip_riattivazione AS RIATTIVAZIONE_2,MD.tip_Contratto AS TIPOCONTRATTO_2,
2					 AS RIATTIVAZIONE_3,2				 AS TIPOCONTRATTO_3,'N' AS INVIO_3,''
FROM C6MART.VCONTRATTI VC JOIN [C6Mart].[CONTRATTOSEI_METADATI]  MD 
ON VC.chiaveClientePB = MD.CHIAVECLIENTEPB
AND VC.tip_contratto = 2 AND VC.DATA_FINEAVANZATO   > GETDATE() AND MD.tip_Contratto = 1  
INSERT INTO C6Mart.GESTIONE_EMAIL (Rete,Cod_Fiscale,Tipo,Data_Generazione,ID_ELAB)
SELECT rete,cod_fiscale,'PERF' as TipoEmail,GETDATE() AS DataGenerazione ,999 
FROM C6MART.VCONTRATTI VC JOIN [C6Mart].[CONTRATTOSEI_METADATI]  MD 
ON VC.chiaveClientePB = MD.CHIAVECLIENTEPB
AND VC.tip_contratto = 2 AND VC.DATA_FINEAVANZATO  > GETDATE() AND MD.tip_Contratto = 1         
UPDATE [C6Mart].[CONTRATTOSEI_METADATI] SET
--SELECT 
 TIP_RIATTIVAZIONE =2, TIP_CONTRATTO=2
,NumSolleciti=0,NumRevoche=0,Data_ultimoSollecito=null,Data_ultimaRevoca=null,InvioBenvenuto='N'
FROM C6MART.VCONTRATTI VC JOIN [C6Mart].[CONTRATTOSEI_METADATI]  MD 
ON VC.chiaveClientePB = MD.CHIAVECLIENTEPB
AND VC.tip_contratto = 2 AND VC.DATA_FINEAVANZATO   > GETDATE() AND MD.tip_Contratto = 1         
--  CASO DOWNGRADE   ***************************************************************************************
INSERT INTO c6mart.test_logmd 
SELECT 'DOWNGRADE',GETDATE(),COD_FISCALE,RETE,
VC.riattivazione     AS RIATTIVAZIONE_1,VC.tip_contratto AS TIPOCONTRATTO_1,
MD.Tip_riattivazione AS RIATTIVAZIONE_2,MD.tip_Contratto AS TIPOCONTRATTO_2,
VC.riattivazione	 AS RIATTIVAZIONE_3,1				 AS TIPOCONTRATTO_3,null AS INVIO_3,''
FROM C6MART.VCONTRATTI VC JOIN [C6Mart].[CONTRATTOSEI_METADATI]  MD 
ON VC.chiaveClientePB = MD.CHIAVECLIENTEPB
AND VC.tip_contratto = 1 AND VC.DATA_FINEAVANZATO   > GETDATE() AND MD.tip_Contratto = 2  
UPDATE [C6Mart].[CONTRATTOSEI_METADATI] SET
--SELECT
    TIP_RIATTIVAZIONE =VC.riattivazione, TIP_CONTRATTO=1
FROM C6MART.VCONTRATTI VC JOIN [C6Mart].[CONTRATTOSEI_METADATI]  MD 
ON VC.chiaveClientePB = MD.CHIAVECLIENTEPB
AND VC.tip_contratto = 1 AND VC.DATA_FINEAVANZATO   > GETDATE() AND MD.tip_Contratto = 2   
--CASO 4
INSERT INTO c6mart.test_logmd 
SELECT 'OLD >> AFF',GETDATE(),VC.COD_FISCALE,VC.RETE,
VC.riattivazione     AS RIATTIVAZIONE_1,VC.tip_contratto AS TIPOCONTRATTO_1,
MD.Tip_riattivazione AS RIATTIVAZIONE_2,MD.tip_Contratto AS TIPOCONTRATTO_2,
NULL				 AS RIATTIVAZIONE_3,NULL			 AS TIPOCONTRATTO_3,NULL AS INVIO_3,''
FROM C6MART.VCONTRATTI VC JOIN [C6Mart].[CONTRATTOSEI_METADATI]  MD 
ON VC.chiaveClientePB = MD.CHIAVECLIENTEPB
AND VC.tip_contratto = 1 AND VC.DATA_FINEAVANZATO > GETDATE() AND MD.tip_Contratto = 0
--CASO 5
INSERT INTO c6mart.test_logmd 
SELECT 'OLD >> PRI',GETDATE(),VC.COD_FISCALE,VC.RETE,
VC.riattivazione     AS RIATTIVAZIONE_1,VC.tip_contratto AS TIPOCONTRATTO_1,
MD.Tip_riattivazione AS RIATTIVAZIONE_2,MD.tip_Contratto AS TIPOCONTRATTO_2,
NULL				 AS RIATTIVAZIONE_3,NULL			 AS TIPOCONTRATTO_3,NULL AS INVIO_3,''
FROM C6MART.VCONTRATTI VC JOIN [C6Mart].[CONTRATTOSEI_METADATI]  MD 
ON VC.chiaveClientePB = MD.CHIAVECLIENTEPB
AND VC.tip_contratto = 2 AND VC.DATA_FINEAVANZATO > GETDATE() AND MD.tip_Contratto = 0
--------------------------------------------------------------------------------------------------------------------
 INSERT INTO C6MART.TEST_QUALITY
 SELECT DISTINCT VC.RETE,VC.COD_FISCALE,'CONTRATTOSEI_METADATI NON AGG.1' FROM [C6Mart].[vContrattiSei] VC
				LEFT JOIN c6mart.gestione_pdf_ftp  PDF
				ON SUBSTRING(PDF.NomeFile, 1, 11) = VC.CODICECONTRATTO
			WHERE    VC.INVIOBENVENUTO = 'S' AND PDF.CODICEFISCALE IS NULL
-- Log esecuzione
	UPDATE
		LOG_ESECUZIONE
	SET
		Fine = GETDATE(),
		Tipo = 'INSERT',
		Righe = @@ROWCOUNT
	WHERE
		Nome = 'RP_DM_POPOLACONTRATTI' AND
		Inizio = (
			SELECT
				MAX(Inizio)
			FROM
				LOG_ESECUZIONE
			WHERE
				Nome = 'RP_DM_POPOLACONTRATTI'
		)
END