-- Stored procedure
--[C6MartPeriodico].[GESTIONE_SELECT_FTP_WHITELIST]343
CREATE procedure [C6MartPeriodico].[GESTIONE_SELECT_FTP_WHITELIST]
@IdZip as INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    SELECT DISTINCT 
	contrattosei.cod_fiscale,contrattosei.rete,
	[C6MartPeriodico].[CONCATENA_WHITELIST](C6MartPeriodico.GESTIONE_PDF_FTP.Rete, C6MartPeriodico.GESTIONE_PDF_FTP.CodiceFiscale, @IdZip) AS riga
	FROM [C6MartPeriodico].GESTIONE_PDF_FTP
	INNER JOIN C6MARTPERIODICO.CONTRATTOSEI
		ON CONTRATTOSEI.Rete = GESTIONE_PDF_FTP.Rete
		AND CONTRATTOSEI.COD_FISCALE = GESTIONE_PDF_FTP.CodiceFiscale
	INNER JOIN [C6MartPeriodico].ANAG_CLIENTI 
		ON ANAG_CLIENTI.Rete = GESTIONE_PDF_FTP.Rete
		AND ANAG_CLIENTI.COD_FISCALE = GESTIONE_PDF_FTP.CodiceFiscale
	WHERE 
    GESTIONE_PDF_FTP.id_fk_zip  = @IdZip
	AND 
	contrattosei.rete+CONTRATTOSEI.COD_FISCALE NOT IN (SELECT rete+CodiceFiscale FROM C6MartPeriodico.ExcludedList where bloccato = 1)
	UPDATE C6MARTPERIODICO.CONTROLLO_TRIMESTRALE SET
	       STATO_REPORT = 8,
		   DATA_TRANSIZIONE=GETDATE()	
	WHERE  STATO_REPORT=6 
		   AND
		   cod_fiscale+rete in 
			(select codicefiscale+rete 
			 from c6martperiodico.gestione_pdf_ftp
			 where id_fk_zip=@idZIP
			)		
		   AND
		   RETE+cod_fiscale NOT IN (SELECT rete+CodiceFiscale FROM C6MartPeriodico.ExcludedList where bloccato = 1)
	UPDATE C6MARTPERIODICO.CONTROLLO_TRIMESTRALE SET
		   STATO_REPORT = 7,
		   DATA_TRANSIZIONE=GETDATE()	
	WHERE  STATO_REPORT=9
		   AND
		   cod_fiscale+rete in 
			(select codicefiscale+rete 
			 from c6martperiodico.gestione_pdf_ftp
			 where id_fk_zip=@idZIP
			)		
		   AND
		   RETE+cod_fiscale IN (SELECT rete+CodiceFiscale FROM C6MartPeriodico.ExcludedList where bloccato = 1)		
END