CREATE PROCEDURE [C6MartPeriodico].[GESTIONE_SELECT_FTP_DA_INVIARE]
 @isLotto1 int = 0
AS
BEGIN
	SELECT 
		GESTIONE_PDF_FTP.Rete,
		GESTIONE_PDF_FTP.CodiceFiscale ,
		--GESTIONE_PDF_FTP.IdReport
		GESTIONE_PDF_FTP.identityreport as IdReport
--		,(NomeFile + '_' + (Case TipoReport when 'D' then 'DP' WHEN 'M' THEN 'MP' else TipoReport end)) as NomeFile
		,(
		  NomeFile + '_' 
		           + (
		              CASE GESTIONE_PDF_FTP.TipoReport WHEN 'D' THEN 'DP' 
		                                               WHEN 'M' THEN (
																	  CASE CONTRATTOSEI.TIP_CONTRATTO 
																	  WHEN 2 THEN 'PP' 
		                                                              ELSE 'MP' 
		                                                               END 
		                                                             ) 
		                                               ELSE GESTIONE_PDF_FTP.TipoReport 
		                                                END
		             )
		 ) as NomeFile		
		,TipoReport
		--,convert(varchar,GESTIONE_PDF_FTP.IdReport)+ '_' +  NomeFile   NomeFilelocale
		,convert(varchar,GESTIONE_PDF_FTP.identityreport)+ '_' +  NomeFile   NomeFilelocale
	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.controllo_trimestrale 
    on  controllo_trimestrale.Rete = GESTIONE_PDF_FTP.Rete
		and controllo_trimestrale.Cod_Fiscale = GESTIONE_PDF_FTP.CodiceFiscale
		LEFT JOIN (SELECT [Cod_Fiscale],[Rete] FROM [C6MartPeriodico].[WHITELIST_ZIP]
		WHERE [Pacchetto_Zip] iS null) WHITELIST_ZIP
		on WHITELIST_ZIP.COD_FISCALE = CONTRATTOSEI.COD_FISCALE
		and WHITELIST_ZIP.Rete = CONTRATTOSEI.RETE
			left join [C6MartPeriodico].[ExcludedList] on GESTIONE_PDF_FTP.Rete = [ExcludedList].Rete and GESTIONE_PDF_FTP.codicefiscale = [ExcludedList].codicefiscale
	WHERE
		FlagInvio = 'N'
	--	and GESTIONE_PDF_FTP.Rete = null and GESTIONE_PDF_FTP.codicefiscale = null
		and  ExcludedList.Rete is null
		---- AND Definitivo = 0 Il FLag non è piu gestito per la generazione Zip
		and dataGenerazione > c6martperiodico.gettrimestre(getdate(),0)
		--and stato_report=4
		and (WHITELIST_ZIP.rete is not null or @isLotto1 = 0 or 0 = ( SELECT count(*) 
		FROM [C6MartPeriodico].[WHITELIST_ZIP]
		WHERE [Pacchetto_Zip] iS null
		))
	--EN20170706 - output ordered by CAP demand from SELECTA	
	--order by rete,codiceFiscale,identityreport --idreport
	order by rete,CAP, codiceFiscale,identityreport --idreport
END