CREATE procedure [C6StagingPeriodico].[RP_ST_APPO_CONTRATTI_20171120]
AS
-- Log esecuzione
	INSERT INTO LOG_ESECUZIONE
		(Nome, Inizio, Nota)
	VALUES (
		'RP_ST_APPO_CONTRATTI',
		GETDATE(),
		'Elaborazione trimestrale'
	)
 declare @dtold datetime
 declare @dtpre datetime
 declare @dttrim  datetime
 set @dttrim = cast(c6martperiodico.getTrimestre2(getDate(), 1) as datetime)
 --INIZIO INTERVENTO MAU
 --set @dtpre = DATEADD(month, -1,@dttrim)
 --set @dtold = DATEADD(month, -3,@dttrim)
    set @dtold = DATEADD(month, -2,@dttrim)
 --FINE INTERVENTO MAU
 print @dttrim 
 print @dtpre 
 print @dtold
TRUNCATE TABLE ScartiQuadratura
 SELECT DISTINCT    
    CASE WHEN CF IS NULL THEN PIVA      
     WHEN CF IS NOT NULL AND PIVA IS NOT NULL THEN 'FF@'+CF
     ELSE CF
    END COD_FISCALE,
    RETE,
    CODICECONTRATTO,
    DATACHIUSURAAVANZATO AS DATA_FINEAVANZATO 
   INTO #DFA    
      FROM     
         [CONSUNI].consulenzaUnica.dbo.VCONTRATTIFINEAVANZATO
 --ESTRAIAMO TUTTI I CF CHE HANNO RICEVUTO IL REPORT DI BENVENUTO ENTRO L'ULTIMO GIORNO DEL MESE PRECEDENTE
 --AL MESE DI FINE TRIMESTRE (ES: PER IL 4° TRIMESTRE 01/10 - 31/12 REPORT DI BENVENUTO RICEVUTO ENTRO IL 30/11)  
 SELECT   
   PDF1.CODICEFISCALE AS CF,PDF1.RETE AS R,SUBSTRING(PDF1.NOMEFILE,0,12) AS C,PDF1.DATAGENERAZIONE AS D
   INTO #pdf1 
 FROM c6mart.gestione_PDF_ftp PDF1 
  WHERE 1=1
  AND DATAGENERAZIONE < @dttrim
  select a.* 
  into #DFA1 
  from #DFA a
  inner join (
  select c.*,max(data_fineavanzato) over (partition by rete,cod_fiscale) as data_max  
  from #DFA c
  inner join #pdf1 d
  on c.rete=d.R and c.cod_fiscale=d.CF and c.codicecontratto=d.C
  ) b
  on a.rete=b.rete and a.cod_fiscale=b.cod_fiscale and a.data_fineavanzato=b.data_fineavanzato 
  and a.data_fineavanzato=data_max
 --ESTRAIAMO TUTTI I CONTRATTI NON REVOCATI (DATA REVOCA IMPOSTATA CON DEFAULT 99990131) E QUELLI CHE 
 --SONO STATI REVOCATI DURANTE IL TRIMESTRE OGGETTO DI TRIMESTRALE
 --(ES: PER IL 4° TRIMESTRE 01/10 - 31/12 TUTTI I CONTRATTI CHE RISULTANO NON REVOCATI + QUELLI CHE SONO STATI 
 --REVOCATO DOPO IL 30/09)  
 SELECT  DISTINCT P.* 
  INTO #PDF 
  FROM #PDF1 P 
  INNER JOIN #DFA1  VC
    ON  P.CF = VC.COD_FISCALE AND P.R=VC.RETE AND P.C=VC.CODICECONTRATTO      
    WHERE DATA_FINEAVANZATO >= @dtold 
select C.* 
INTO #CONGELATI
from 
#PDF P
INNER JOIN
   C6StagingPeriodico.ContrattiPreTrimestrale c 
ON P.R =  C.RETE
AND P.CF = C.COD_FISCALE
AND P.C = C.CODICECONTRATTO
select C.* 
INTO #CONGELATICHIUSI
from 
#PDF P
INNER JOIN
   [C6StagingPeriodico].[GetContrattiChiusiFromSEI] (
   c6MartPeriodico.getPreviousTrimestre(getDate(), 0), 
   c6martperiodico.getTrimestre(getDAte(), 0)) c 
ON
   P.R = c.RETE
AND 
   p.CF = case when len(c.CF) < 16 then 'FF@' + c.CF 
               when c.CF is null then c.PIVA
			   else c.CF END
AND P.C = C.COD_CONF
LEFT JOIN #CONGELATI CONG
ON P.R =  CONG.RETE
AND P.CF = CONG.COD_FISCALE
AND P.C = CONG.CODICECONTRATTO
WHERE CONG.RETE IS NULL
TRUNCATE TABLE C6StagingPERIODICO.APPO_CONTRATTI
INSERT INTO C6StagingPERIODICO.APPO_CONTRATTI
SELECT 
    *
FROM 
   #CONGELATI
-- modifica del 1/12/2016 - contratto non più esistente - query inutile
-- modifica del 5/12/2016 - segnalata squadratura con capgemini: quindi sembra un agente fittizio; ripristino
WHERE codicecontratto <> '390026CO101' and cod_agente <> '015312' --MRRRRT78S30L219K cliente di prova
-- fine modifica del 5/12/2016
-- fine modifica del 1/12/2016
and codicecontratto not in
(
select codicecontratto from EsclusioneContrattiPerQuadratura
)
UNION
SELECT 
chiaveClientePB,	
--case when len(CF) < 16 then 'FF@' + CF else CF END as COD_FISCALE,
case when len(CF) < 16 then 'FF@' + CF 
     when CF is null then PIVA
	 else CF END as COD_FISCALE,
NULL as COD_MANDATO,	 
PIVA as PIVA_FIDUCIARIA,	
PB	as COD_AGENTE,
RETE,
COD_CONF as codiceContratto,
NULL as flagRol,
INDIRIZZO,	
CAP,	
CITTA,
PROV as PROVINCIA,	
NAZIONE,	
null as PRESSO,
EMAIL,	
FREQUENZA_DIAGNOSI	as FREQ_DIAGNOSI,
FREQUENZA_MONITORAGGIO as FREQ_MONITORAGGIO,	
FLAG_STAMPA_REPORT	as GENERAZIONEREPORT,
DATA_SOTTOSCRIZIONE_CB as DATA_INIZIOBASE,	
DATA_CHIUSURA_CB as DATA_FINEBASE,
DATA_PERFEZIONAMENTO_CB	as DATA_PERF_BASE,
DATA_PERFEZIONAMENTO_C6	 as DATA_PERF,	
DATA_SOTTOSCRIZIONE_C6	as DATA_INZIOAVANZATO,
DATA_CHIUSURA_C6 as DATA_FINEAVANZATO,
null as RIATTIVAZIONE,	
--null as TIP_CONTRATTO,
case when TIPCONTRATTO is null then 0 
else 
(case when TIPCONTRATTO = 'A' then 1 
 else 2 end) end as TIP_CONTRATTO,		
null as TIP_RIATTIVAZIONE,	
'S' as ClienteMIGRATO,	
'N' as RISORSENA,	
1 as PROFILOATTIVO,	
0 as NumSolleciti,	
0 as NumRevoche,	
null as Data_ultimoSollecito,	
null as Data_ultimaRevoca,	
'S' as InvioBenvenuto
FROM 
#CONGELATICHIUSI
-- modifica del 1/12/2016 - contratto non più esistente - query inutile
-- modifica del 5/12/2016 - segnalata squadratura con capgemini: quindi sembra un agente fittizio; ripristino
WHERE COD_CONF <> '390026CO101' and PB <> '015312' --MRRRRT78S30L219K cliente di prova
-- fine modifica del 5/12/2016
-- fine modifica del 1/12/2016
and COD_CONF not in    -- FC 11122013 aggiornato campo codicecontratto INESISTENTE con COD_CONF
(
select codicecontratto from EsclusioneContrattiPerQuadratura
)
UPDATE C6StagingPERIODICO.APPO_CONTRATTI
	set  
		 indirizzo	       = a.indirizzo
		,cap		       = a.cap	
		,citta		       = a.citta
		,provincia	       = a.Provincia
		,nazione	       = a.Nazione
		,presso		       = a.Presso
		,Cod_Agente        = a.Cod_Agente
		,Freq_Diagnosi	   = a.Freq_Diagnosi
	    ,FREQ_MONITORAGGIO = a.FREQ_MONITORAGGIO
from c6mart.vcontratti a
inner join C6StagingPERIODICO.APPO_CONTRATTI b
on a.codicecontratto = b.codicecontratto
INSERT INTO ScartiQuadratura
SELECT
	P.R
	,P.CF
	,P.C
	,1
	,0
FROM
	#PDF P
LEFT JOIN
 C6StagingPERIODICO.APPO_CONTRATTI AC
ON P.R =  AC.rete
AND P.CF = AC.COD_FISCALE
AND P.C = AC.codiceContratto
WHERE AC.RETE IS NULL
and p.c not in ( select codiceContratto from EsclusioneContrattiPerQuadratura )
	-- Log esecuzione
	UPDATE
		LOG_ESECUZIONE
	SET
		Fine = GETDATE(),
		Tipo = 'INSERT',
		Righe = @@ROWCOUNT
	WHERE
		Nome = 'RP_ST_APPO_CONTRATTI' AND
		Inizio = (
			SELECT
				MAX(Inizio)
			FROM
				LOG_ESECUZIONE
			WHERE
				Nome = 'RP_ST_APPO_CONTRATTI'
		)
DROP TABLE #DFA		
DROP TABLE #DFA1		
DROP TABLE #pdf1
DROP TABLE #PDF
DROP TABLE #CONGELATI
DROP TABLE #CONGELATICHIUSI
	declare @iScarti INT
	SELECT @iscarti = count(*) FROM ScartiQuadratura 
	if @iScarti > 0
		RAISERROR ('Scarti Quadratura presenti (Verificare su tabella "ScartiQuadratura").', -- Message text.
               16, -- Severity.
               1 -- State.
               );
--select count(*) from C6StagingPERIODICO.APPO_CONTRATTI_TEST
--select count(*) from C6StagingPERIODICO.APPO_CONTRATTI
--select count(*) from vcontrcong0913
--select v.* from #CONTRATTI_DA_CONSIDERARE t
--right join vcontrcong0913 v
--on t.rete= v.rete
--and t.cod_fiscale = v.cod_fiscale
--where t.rete is null
--select top 1 * from 
--[consevo].consulenzaUnicafl.dbo.cbs_AvanzatiChiusi c 
--where cf = '4200'