-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
--exec C6_S_QueryRicerca @CodicePB=NULL,@NomePB=NULL,@CognomePB=NULL,@CFCliente=NULL,@NomeCliente=NULL,@CognomeCliente=NULL,@Rete=N'F'
create PROCEDURE [dbo].[C6_S_QueryRicerca_bck_campagne]
	-- Add the parameters for the stored procedure here
	@CodicePB as varchar(6),
	@NomePB as varchar(50),
	@CognomePB as varchar(50),
	@CFCliente as varchar(16),
	@NomeCliente as varchar(50),
	@CognomeCliente as varchar(50),
	@Rete as varchar(1)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	declare @tiporeport as varchar(255)	
	set @tiporeport = ''	
	select distinct @tiporeport = @tiporeport + ',' +c.tiporeport
	FROM C6ReportFisico c
		WHERE
			((@rete is null) or (c.rete = @rete)) 
			and
			((@CodicePB is null) or (c.CodicePB like @codicePB + '%')) 
			and
			((@cfCliente is null) or (c.Cliente like @cfCliente + '%')) 
			and
			((@nomeCliente is null) or (c.nomeCliente like @nomeCliente + '%'))
			and
			((@cognomeCliente is null) or (c.cognomeCliente like @cognomeCliente + '%'))
			and
			((@cognomePB is null) or (c.cognomePB like @cognomePB + '%'))
			and
			((@nomePB is null) or (c.nomePB like @nomePB + '%'))
	if(len(@tiporeport)>0)
	set @tiporeport = right(@tiporeport,len(@tiporeport)-1)
    -- Insert statements for procedure here
	SELECT DISTINCT
		Rete, 
		c.Cliente as CodiceFiscale,
		c.NomeCliente as NomeCliente,
		c.CognomeCliente as CognomeCliente,
		c.CodicePB as CodicePB,
		isnull(c.NomePB,'') as NomePB,
		isnull(c.cognomePB,'') as CognomePB,
		case upper(@tiporeport) when 'DIAGNOSI' then '1' when 'MONITORAGGIO' then '4' else '0' end as tiporeport
	FROM
		C6ReportFisico c
	WHERE
		((@rete is null) or (c.rete = @rete)) 
		and
		((@CodicePB is null) or (c.CodicePB like @codicePB + '%')) 
		and
		((@cfCliente is null) or (c.Cliente like @cfCliente + '%')) 
		and
		((@nomeCliente is null) or (c.nomeCliente like @nomeCliente + '%'))
		and
		((@cognomeCliente is null) or (c.cognomeCliente like @cognomeCliente + '%'))
		and
		((@cognomePB is null) or (c.cognomePB like @cognomePB + '%'))
		and
		((@nomePB is null) or (c.nomePB like @nomePB + '%'))
--		(@nomePB = null) or
--		(u.utente = @codicePB) or
--		(@cognomePB = null) or
--		(u.utente = @codicePB) or
	Order by 
		Rete,CodicePB,CognomePB,NomePB,CodiceFiscale,CognomeCliente,NomeCliente
END