SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'WMAN_Bitacora_GetInfo') )
DROP PROCEDURE WMAN_Bitacora_GetInfo
GO

CREATE PROCEDURE WMAN_Bitacora_GetInfo
		@id_oper INT, 
		@fec_ini DATETIME, 
		@fec_fin DATETIME, 
		@filtro VARCHAR(255), 
		@id_modulo INT, 
		@id_app INT,
		@id_datos INT, 
		@id_opc INT
AS
BEGIN
--s=30/3/2016&f=31/3/2016&u=52&m=undefined&a=104&d=null&o=104022&t=

DECLARE @Sql nVARCHAR(MAX)
SET @Sql = ''

DECLARE @oper VARCHAR(100),
		@fil VARCHAR(100), 
		@modulo VARCHAR(100), 
		@app VARCHAR(100),
		@datos VARCHAR(100), 
		@opc VARCHAR(100)

--Filtro para Encabezado--

	CREATE TABLE #TempEncabezado (
	fil_oper varchar(100),
	fil_modulo varchar(100),
	fil_app	varchar(100),
	fil_datos varchar(100),
	fil_opc varchar(100)
	)

	IF(@id_oper != '')
	BEGIN 
		SET @oper = (SELECT descripcion FROM Operador WHERE id_oper = @id_oper) 
	END
	ELSE 
	BEGIN 
		SET @oper = 'LblGENAll' 
	END

	IF(@id_modulo != '')
	BEGIN 
		SET @modulo = (SELECT desc_modulo FROM Modulo WHERE id_modulo = @id_modulo) 
	END
	ELSE 
	BEGIN 
		SET @modulo =  'LblGENAll' 
	END

	IF(@id_app != '')
	BEGIN 
		SET @app = (SELECT desc_app FROM Aplicaciones WHERE id_app = @id_app) 
	END
	ELSE 
	BEGIN 
		SET @app = 'LblGENAll' 
	END

	IF(@id_datos != '')
	BEGIN 
		SET @datos = (SELECT descripcion FROM Datos WHERE id_datos = @id_datos) 
	END
	ELSE 
	BEGIN 
		SET @datos = 'LblGENAll' 
	END

	IF(@id_opc != '')
	BEGIN 
		SET @opc = (SELECT desc_opc FROM Opciones WHERE id_opc = @id_opc) 
	END
	ELSE 
	BEGIN 
		SET @opc = 'LblGENAll' 
	END


	--SET @fil =

	INSERT INTO #TempEncabezado(fil_oper, fil_modulo, fil_app, fil_datos, fil_opc) 
	VALUES (@oper, @modulo, @app, @datos, @opc)

	SELECT * FROM #TempEncabezado

	--Informacion Solicitada--

	SET @fec_ini = DATEADD(hour,0,@fec_ini)
	SET @fec_ini = DATEADD(minute,0,@fec_ini)
	SET @fec_ini = DATEADD(second,0,@fec_ini)

	SET @fec_fin = DATEADD(hour,23,@fec_fin)
	SET @fec_fin = DATEADD(minute,59,@fec_fin)
	SET @fec_fin = DATEADD(second,59,@fec_fin)


	SET @Sql = @Sql +'SELECT id_log_sheet, MO.id_modulo, MO.desc_modulo, APL.id_app, APL.desc_app, OPC.id_opc, OPC.desc_opc,
	DA.id_datos, DA.base_datos, DA.descripcion desc_datos, DA.servidor, OPE.id_oper, OPE.nombre, OPE.descripcion, Marca, observaciones
	FROM Log_Sheet LO
	INNER JOIN Datos DA ON LO.id_datos = DA.id_datos
	INNER JOIN  Terminales TER ON LO.id_terminal = TER.id_terminal
	INNER JOIN  Operador OPE ON LO.id_oper = OPE.id_oper
	INNER JOIN  Opciones OPC ON LO.id_opc = OPC.id_opc
	INNER JOIN Aplicaciones APL ON APL.id_app = OPC.id_app
	INNER JOIN Modulo MO ON MO.id_modulo = APL.id_modulo 
	WHERE Marca BETWEEN  '''+LTRIM(RTRIM(CONVERT(varchar(100),@fec_ini)))+''' AND '''+LTRIM(RTRIM(CONVERT(varchar(100),@fec_fin))) +''''
	
	IF(@id_oper != 0)
	BEGIN
		SET @Sql = @Sql +  ' AND OPE.id_oper = '+LTRIM(RTRIM(CONVERT(varchar(100),@id_oper)))
	END
	IF(@id_modulo != '')
	BEGIN
		SET @Sql = @Sql +  ' AND MO.id_modulo = '+LTRIM(RTRIM(CONVERT(varchar(100),@id_modulo)))
	END
	IF(@id_app != 0)
	BEGIN
		SET @Sql = @Sql +  ' AND APL.id_app = '+LTRIM(RTRIM(CONVERT(varchar(100),@id_app)))
	END
	IF(@id_opc != 0)
	BEGIN
		SET @Sql = @Sql +  ' AND OPC.id_opc = '+LTRIM(RTRIM(CONVERT(varchar(100),@id_opc)))
	END
	IF(@id_datos != 0)
	BEGIN
		SET @Sql = @Sql +  ' AND DA.id_datos = '+LTRIM(RTRIM(CONVERT(varchar(100),@id_datos)))
	END
	IF(@filtro != '')
	BEGIN
		SET @Sql = @Sql +  ' AND observaciones LIKE '''+'%'+LTRIM(RTRIM(CONVERT(varchar(100),@filtro)))+'%'''
	END
	
	SET @Sql = @Sql + ' ORDER BY Marca '
	
	EXEC sp_executesql @Sql
	--SELECT @Sql

END
GO
