CREATE PROCEDURE [dbo].[usp_ConsultarVerificaciones_Dynamic2]
(@fechaBegin datetime, @fechaEnd datetime, @nombreTercero nvarchar(255),@idUVF int, @idEdo int, @idMpio int, @idTVF int, @idTMVF int, @idTratamiento int, @idEstablecimiento int ,@idTipoMercancia int,@idMercancia int, @idTipoNormaLegal int, @idNormaLegal int, @idTT int, @result bit, @pause bit, @nivel int, @idUsr int)
AS
	-- variables de trabajo
	DECLARE @strSelect nvarchar(MAX), @strEnd nvarchar(MAX), @strFiltro nvarchar (MAX), @strConsulta nvarchar(MAX);

	-- asignando valores de inicio a variables base
	SET @strSelect = 'select distinct
	   Verif.idVerificacion, Verif.IdUVF, CatUVF.RazonSocial as UVF,
	  Verif.IdUsuario,CatUser.Nombre as Usuario,
	  dbo.ufn_FormatearFecha(Fecha) as Fecha, Verif.HoraDeSolicitud, Verif.HoraDeInicioVerificacion,
	  Verif.Georeferencia.STAsText() AS Georeferencia,
	  Verif.Estado as IdEstado,
	  CatEdo.Nombre as Estado,
	  Verif.Municipio AS IdMunicipio,
	  CatMuni.NombreMunicipio as Municipio,Localidad,
	  TipoDeVerificacion AS IdTipoVerificacion,
	  CatTipoVerif.TipoVerificacion as  TipoVerificacion,
	  IdTipoDeTransporte AS IdTipoTransporte, CatTipoTransport.TipoDeTransporte AS TipoTransporte,
	  	Resultado, Observaciones, FechaDeFinVerificacion, Pausa, TiempoAcumulado,
			ISNULL(STUFF((SELECT '+CHAR(39)+','+CHAR(39)+ '+ CatPrd.Producto as[text()]
		                from dbo.ProductosVerificados b2
						    left outer join dbo.Productos CatPrd
							   on b2.IdProducto = CatPrd.IdProducto
						where b2.IdVerificacion = Verif.IdVerificacion
						for xml path('+CHAR(39)+CHAR(39)+')),1,1,'+CHAR(39)+CHAR(39)+'),'+CHAR(39)+CHAR(39)+') as Productos,
	        ISNULL(STUFF((SELECT '+CHAR(39)+','+CHAR(39)+ '+ CatNorm.Norma as[text()]
		                from dbo.NormasAplicadas n2
						    left outer join dbo.NormasOficiales CatNorm
							   on n2.IdNorma = CatNorm.IdNorma
						where n2.IdVerificacion = Verif.IdVerificacion
						for xml path('+CHAR(39)+CHAR(39)+')),1,1,'+CHAR(39)+CHAR(39)+'),'+CHAR(39)+CHAR(39)+') as Normas,
			ISNULL(STUFF((SELECT '+CHAR(39)+','+CHAR(39)+ '+ i2.RutaImagen as[text()]
		                from dbo.ImagenesVerificacion i2
						where i2.IdVerificacion = Verif.IdVerificacion
						for xml path('+CHAR(39)+CHAR(39)+')),1,1,'+CHAR(39)+CHAR(39)+'),'+CHAR(39)+CHAR(39)+') as ListadoImgsSistema,
				ISNULL(STUFF((SELECT '+CHAR(39)+','+CHAR(39)+ '+ CatTImg.TipoImagen as[text()]
		                from dbo.ImagenesVerificacion i3
						  left outer join dbo.TiposImagen CatTImg
							   on i3.IdTipoImagen = CatTImg.IdTipoImagen
						where i3.IdVerificacion = Verif.IdVerificacion
						for xml path('+CHAR(39)+CHAR(39)+')),1,1,'+CHAR(39)+CHAR(39)+'),'+CHAR(39)+CHAR(39)+') as TipoImagenes,
	Verif.IdMateriaVerificacion, CatMatVerif.MateriaVerificacion AS MateriaVerificacion,
	Verif.IdTipoEstablecimiento, CatTipoEstab.TipoEstablecimiento AS TipoEstablecimiento,
	Verif.IdTipoTratamiento, CatTipoTrat.TipoTratamiento AS TipoTratamiento, Verif.NoImagenes

	from Verificaciones Verif WITH(NOLOCK)
	   left outer join dbo.UnidadesDeVerificacion CatUVF WITH(NOLOCK)
	      on Verif.IdUVF = CatUVF.Id_uvf
		left outer join dbo.Usuarios CatUser WITH(NOLOCK)
		  on Verif.IdUsuario= CatUser.IdUsuario
	    left outer join dbo.Estados CatEdo WITH(NOLOCK)
		  on Verif.Estado = CatEdo.IdEstado
	    left outer join dbo.Municipios CatMuni WITH(NOLOCK)
		  on Verif.Municipio = CatMuni.IdMunicipio
		left outer join dbo.TiposDeVerificacion CatTipoVerif WITH(NOLOCK)
		  on Verif.TipoDeVerificacion = CatTipoVerif.IdTipoVerificacion
	    left outer join dbo.TiposDeTransporte CatTipoTransport WITH(NOLOCK)
		  on Verif.IdTipoDeTransporte = CatTipoTransport.IdTipoTransporte
	    left outer join dbo.MateriaDeVerificacion CatMatVerif WITH(NOLOCK)
		  on Verif.IdMateriaVerificacion = CatMatVerif.IdMateriaVerificacion
		left outer join dbo.TipoDeEstablecimiento CatTipoEstab WITH(NOLOCK)
		  on Verif.IdTipoEstablecimiento = CatTipoEstab.IdTipoEstablecimiento
		left outer join dbo.TipoDeTratamiento CatTipoTrat WITH(NOLOCK)
		  on Verif.IdTipoTratamiento = CatTipoTrat.IdTipoTratamiento
		     left outer join dbo.ProductosVerificados b4 WITH(NOLOCK)
                          on Verif.IdVerificacion = b4.IdVerificacion
                    left outer join dbo.Productos CatPrd WITH(NOLOCK)
                          on b4.IdProducto = CatPrd.IdProducto
             left outer join dbo.NormasAplicadas b5 WITH(NOLOCK)
                          on Verif.IdVerificacion = b5.IdVerificacion
                    left outer join dbo.NormasOficiales CatNormas WITH(NOLOCK)
                          on b5.IdNorma = CatNormas.IdNorma';
	-- segmento final
	SET @strEnd = ' ORDER BY Fecha ASC;';

	-- armando filtro
	SET @strFiltro = ' WHERE Fecha >= ''' + CAST(@fechaBegin AS varchar) + ''' AND ( FechaDeFinVerificacion <= ''' + CAST(@fechaEnd AS varchar) +''' OR FechaDeFinVerificacion IS NULL) ';
	IF ((@idUVF IS NOT NULL) OR (@nivel = 2)) -- UVF (nivel 2)
		SET @strFiltro = @strFiltro + ' AND Verif.IdUVF = ' + CAST(@idUVF AS varchar);
	IF (@idEdo IS NOT NULL) -- estado
		SET @strFiltro = @strFiltro + ' AND Verif.Estado = ' + CAST(@idEdo AS varchar);
	IF (@idMpio IS NOT NULL) -- municipio
		SET @strFiltro = @strFiltro + ' AND Verif.Municipio = ' + CAST(@idMpio AS varchar);
	IF (@idTVF IS NOT NULL) -- TVF
		SET @strFiltro = @strFiltro + ' AND Verif.TipoDeVerificacion = ' + CAST(@idTVF AS varchar);


	IF (@idTMVF IS NOT NULL) -- TMVF  IGM DTI 12/27/2017
		SET @strFiltro = @strFiltro + '  AND Verif.IdMateriaVerificacion = ' + CAST(@idTMVF AS varchar);
	IF (@idTratamiento IS NOT NULL) -- TTratamiento  IGM DTI 12/28/2017
		SET @strFiltro = @strFiltro + '  AND Verif.IdTipoTratamiento = ' + CAST(@idTratamiento AS varchar);
	IF (@idEstablecimiento IS NOT NULL) -- TEstablecimiento  IGM DTI 12/28/2017
		SET @strFiltro = @strFiltro + '  AND Verif.IdTipoEstablecimiento = ' + CAST(@idEstablecimiento AS varchar);

	IF (@idTipoMercancia IS NOT NULL) -- Tipo de Producto (Tipo de Mercancia)  IGM DTI 01/02/2018
		SET @strFiltro = @strFiltro + '  AND CatPrd.TipoProducto = ' + CAST(@idTipoMercancia AS varchar);
	IF (@idMercancia IS NOT NULL) -- Producto (Mercancia)  IGM DTI 01/02/2018
		SET @strFiltro = @strFiltro + '  AND CatPrd.idProducto = ' + CAST(@idMercancia AS varchar);

	IF (@idTipoNormaLegal IS NOT NULL) -- Tipo de Normas  IGM DTI 01/02/2018 (MM/DD/YYYY)
		SET @strFiltro = @strFiltro + '  AND CatNormas.idTipoNorma = ' + CAST(@idTipoNormaLegal AS varchar);
	IF (@idNormaLegal IS NOT NULL) -- Normas  IGM DTI 01/02/2018
		SET @strFiltro = @strFiltro + '  AND CatNormas.idNorma = ' + CAST(@idNormaLegal AS varchar);


    IF ((@nombreTercero IS NOT NULL) ) -- Nombre del Tercero Fitosanitario IGM DTI 01/08/2018 (MM/DD/YYYY)
		SET @strFiltro = @strFiltro + ' AND CatUser.Nombre like  '+CHAR(39) +'%' + @nombreTercero+'%' +CHAR(39);
	IF (@idTT IS NOT NULL) -- TT
		SET @strFiltro = @strFiltro + ' AND IdTipoDeTransporte = ' + CAST(@idTT AS varchar);
	IF (@result IS NOT NULL) -- resultado
		SET @strFiltro = @strFiltro + ' AND Resultado = ' + CAST(@result AS VARCHAR(1));
	IF (@pause IS NOT NULL) -- pausa
		SET @strFiltro = @strFiltro + ' AND Pausa = ' + CAST(@pause AS VARCHAR(1));
	IF(@nivel = 3) -- usuario (verificador) nivel 3
		SET @strFiltro = @strFiltro + 'AND Verif.IdUsuario = ' + CAST(@idUsr AS varchar);


	-- armando consulta dinamica
		SET @strConsulta = @strSelect + @strFiltro + @strEnd;
		-- ejecutando consulta
		EXEC sp_executesql @strConsulta, N'@P_fechaB datetime, @P_fechaE datetime, @P_IdUVF int, @P_IdEdo int, @P_IdMpio int, @P_IdTVF int, @P_IdTT int, @P_Result bit, @P_Pause bit, @P_nivel int, @P_IdUsr int',
		@fechaBegin, @fechaEnd, @idUVF, @idEdo, @idMpio, @idTVF, @idTT, @result, @pause, @nivel, @idUsr;