--[dbo].[sp_getAllDocumentsByFilters] @utente=N'F006803',@codiceFiscale=N'BRDMRZ48E28D612K',@cognome=N'BARDAZZI',@nome=N'MAURIZIO',@dataDa=NULL,@dataA=NULL,@flagArchiviato=null,@tpReportHD_US=null
CREATE PROCEDURE [dbo].[sp_getAllDocumentsByFilters_TEST]
	@tpReport as int = null,
	@tpReportHD_US as int = null,
	@utente as varchar(13) = null,
	@codiceFiscale as varchar(16) = null,
	@dataDa as datetime = null,
	@dataA as datetime = null,
	@flagArchiviato as bit = null,
	@cognome as varchar(50) = null,
	@nome as varchar(50) = null,
	@codiceContratto as varchar(11) = null
AS
DECLARE @Sql nvarchar(4000)
DECLARE @SqlCondition nvarchar(4000)
set @cognome = replace(@cognome, '''','''''')
set @nome = replace(@nome, '''','''''')
--DECLARE @esisteContratto as smallint 
--set @esisteContratto = 0
--
--select @esisteContratto = count(*) 
--from c6reportfisico
--where (codicecontratto is null or codicecontratto = @codiceContratto)
--and @utente is null or (rete + codicepb = @utente)
--and @tpReport is null 
--		or ( @tpReport = 1 and TipoReport = 'Diagnosi')
--		or ( @tpReport = 2 and TipoReport = 'Pianificazione')
--		or ( @tpReport = 3 and TipoReport = 'Proposta')
--		or ( @tpReport = 4 and TipoReport = 'Monitoraggio')
--and @cognome is null or (CognomeCliente like @cognome + '%')
--and @nome is null or (NomeCliente like @nome + '%')
SET @Sql = N''
if isnull(@tpReportHD_US,0) = 0
BEGIN
	SET @Sql = N'
		SELECT   
			Cliente.IdCliente, 
			ReportperClienteDati.Identificativo, 
			substring(Cliente.Utente, 2,7) as CodicePB, 
			ReportperClienteDati.FlgArchiviato AS stato, 
			Cliente.Nominativo, 
			ReportperClienteDati.DtCreazione, 
			ReportperClienteDati.Note, 
			TipologiaReport.Descrizione AS TipologiaReport, 
			Cliente.Cognome, 
			Cliente.Nome, 
			Cliente.CodFiscale, 
			0 as pdfC6
		FROM Cliente 
		INNER JOIN ReportPerCliente 
			ON Cliente.IdCliente = ReportPerCliente.IdCliente 
		INNER JOIN ReportperClienteDati 
			ON ReportPerCliente.IdReport = ReportperClienteDati.IdReport 
			AND ReportPerCliente.IdCliente = ReportperClienteDati.IdCliente
		INNER JOIN Report 
			ON ReportPerCliente.IdReport = Report.IdReport 
		INNER JOIN TipologiaReport 
			ON Report.TpReport = TipologiaReport.TpReport 
		WHERE 1=1 '
	--print @Sql
	SET @SqlCondition = ''
	if @codiceContratto is not null SET @SqlCondition = @SqlCondition + ' AND Cliente.CodiceContratto = '''  +  @codiceContratto +  ''''
	if @codiceFiscale  is not null SET @SqlCondition = @SqlCondition + ' AND Cliente.CodFiscale = '''  +  @codiceFiscale +  ''''
	--V cambiato report per cliente in report per cliente dati
	if @utente         is not null SET @SqlCondition = @SqlCondition + ' AND reportPerClienteDati.Utente = '''  +  @utente +  ''''
	if @cognome        is not null SET @SqlCondition = @SqlCondition + ' AND Cliente.Cognome LIKE '''  +  @cognome +  '%'''
	if @nome           is not null SET @SqlCondition = @SqlCondition + ' AND Cliente.Nome LIKE '''  +  @nome +  '%'''
	if @tpReport       is not null SET @SqlCondition = @SqlCondition + ' AND TipologiaReport.TpReport = ' +  CAST(@tpReport as nvarchar(10))
	--if (@tpReport is null) AND (@tpReportHD_US is not null) 
	if (@tpReport is null) AND (isnull(@tpReportHD_US,0) = 1) -- modifica per il caso in cui arrivi @tpReportHD_US pari a zero
				SET @SqlCondition = @SqlCondition + ' AND TipologiaReport.TpReport IN (1,4)'
	if @flagArchiviato is not null SET @SqlCondition = @SqlCondition + ' AND ReportperClienteDati.FlgArchiviato = ' +  CAST(@flagArchiviato as nvarchar(10))
	if (@dataDa is not null) AND (@dataA is not null) 
				SET @SqlCondition = @SqlCondition + ' AND ReportperClienteDati.DtCreazione BETWEEN ''' + CAST(@dataDa as nvarchar(50)) + ''' AND ''' +  CAST(@dataA as nvarchar(50))  +  ''''
	else if @dataDa is null AND @dataA is not null 
				SET @SqlCondition = @SqlCondition + ' AND ReportperClienteDati.DtCreazione <= ''' +  CAST(@dataA as nvarchar(50)) +  ''''
	else if @dataDa is not null AND @dataA is null
				SET @SqlCondition = @SqlCondition + ' AND ReportperClienteDati.DtCreazione >= ''' +  CAST(@dataDa as nvarchar(50)) +  ''''
	--PRINT @SqlCondition
	SET @Sql = @Sql + @SqlCondition + ' UNION ALL '
END
print @Sql
return
--Per ContrattoSEI decommentare
SET @Sql = @Sql  +  '
	SELECT 
		319 as IdCliente, 
		Identificativo, 
		CodicePB, 
		''true'' as Stato, 
		(NomeCliente + '' '' + CognomeCliente) as Nominativo, 
		Data as DtCreazione, 
		''Report centralizzato'' as Note, 
		tipoReport as TipologiaReport, 
		CognomeCliente as Cognome, 
		NomeCliente as Nome, 
		Cliente as CodFiscale, 
		1 as pdfC6 
	FROM C6ReportFisico 
	WHERE 1 = 1 AND tipoReport <> ''LR'''
SET @SqlCondition = ''
if @codiceContratto is not null SET @SqlCondition = @SqlCondition + ' AND CodiceContratto = '''  +  @codiceContratto +  ''''
if @flagArchiviato is not null SET @SqlCondition = @SqlCondition + ' AND StatoLock = ''' + cast(@flagArchiviato as varchar(1)) + '''' 
if @codiceFiscale  is not null SET @SqlCondition = @SqlCondition + ' AND Cliente = '''  +  @codiceFiscale +  ''''
if @utente         is not null SET @SqlCondition = @SqlCondition + ' AND CodicePB = '''  +  substring(@utente,2,len(@utente)-1) +  ''''
if @cognome        is not null SET @SqlCondition = @SqlCondition + ' AND CognomeCliente LIKE '''  +  @cognome +  '%'''
if @nome           is not null SET @SqlCondition = @SqlCondition + ' AND NomeCliente LIKE '''  +  @nome +  '%'''
if @tpReport       is not null SET @SqlCondition = @SqlCondition + 
	case @tpReport 
		when 1 then ' AND TipoReport = ''Diagnosi'''
		when 2 then ' AND TipoReport = ''Pianificazione'''
		when 3 then ' AND TipoReport = ''Proposta'''
		when 4 then ' AND TipoReport = ''Monitoraggio'''
		else ' AND TipoReport IS NOT NULL'
	end 
--if (@tpReport is null) AND (@tpReportHD_US is not null) 
if (@tpReport is null) AND (isnull(@tpReportHD_US,0) = 1)
			SET @SqlCondition = @SqlCondition + ' AND TipoReport IN (''Diagnosi'', ''Monitoraggio'')'
if (@dataDa is not null) AND (@dataA is not null) 
			SET @SqlCondition = @SqlCondition + ' AND Data BETWEEN ''' + CAST(@dataDa as nvarchar(50)) + ''' AND ''' +  CAST(@dataA as nvarchar(50))  +  ''''
else if @dataDa is null AND @dataA is not null 
            SET @SqlCondition = @SqlCondition + ' AND Data <= ''' +  CAST(@dataA as nvarchar(50)) +  ''''
else if @dataDa is not null AND @dataA is null
			SET @SqlCondition = @SqlCondition + ' AND Data >= ''' +  CAST(@dataDa as nvarchar(50)) +  ''''
--print @Sql
SET @Sql = @Sql + @SqlCondition
SET @Sql = 'SELECT IdCliente, 
			Identificativo, 
			CodicePB, 
			Stato, 
			Nominativo, 
			DtCreazione, 
			Note, 
			TipologiaReport, 
			Cognome, 
			Nome, 
			CodFiscale, 
			pdfC6 
			FROM (' + @Sql+ ') A
			ORDER BY 
				Nominativo, 
				TipologiaReport, 
				DtCreazione DESC' 
--SET @Sql = @Sql  +  ' 
--	ORDER BY 
--		Nominativo, 
--		Descrizione, 
--		DtCreazione DESC'
print @Sql
--print @Sql
EXECUTE sp_executesql @Sql