
/****** Object:  StoredProcedure [dbo].[DSS_Ingresos_Hospedaje_Fechas]    Script Date: 05/20/2013 13:57:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

DROP PROCEDURE [dbo].[DSS_Ingresos_Hospedaje_Fechas]
GO

CREATE PROCEDURE [dbo].[DSS_Ingresos_Hospedaje_Fechas]    
					@desde     DATETIME,
 					@hasta     DATETIME,
					@tasa      NUMERIC(13,8),
					@id_oper   INT,
 					@result    NUMERIC(19,4) OUTPUT
AS

BEGIN
   SET NOCOUNT ON
    
   DECLARE @result_reservas NUMERIC(19,4),
	       @result_rooming  NUMERIC(19,4)

   SELECT @result  = 0
   
   IF @id_oper = 0
   BEGIN
 
   SELECT @result = SUM(coalesce(hospedaje, 0)) 
   FROM(	  
   
	   SELECT hospedaje = SUM(COALESCE(ingxaloj * @tasa, 0)) 
	   
	   FROM       ESTAXING ing
	   INNER JOIN ESTADIAS dias  ON ing.id_dias  = dias.id_dias
	   INNER JOIN ESTACOMA coma  ON ing.id_coma  = coma.id_coma
	   INNER JOIN HOTEUHAB uhab  ON coma.id_uhab = uhab.id_uhab
	   INNER JOIN HOTETHAB thab  ON uhab.id_thab = thab.id_thab
	   INNER JOIN HOTECONT cont  ON coma.id_cont = cont.id_cont
	   INNER JOIN SICLAMON mone  ON ing.id_mone  = mone.id_mone
	   WHERE  DATEDIFF(DAY, @desde, dias.fecha) >= 0 	       
		  AND DATEDIFF(DAY, @hasta, dias.fecha) <= 0 	     
	   UNION ALL
	   SELECT hospedaje  = SUM(CASE WHEN ing.tipo = 1 
									THEN importe * @tasa 
									ELSE 0 
									END) 
	   FROM       ESTAAING ing
	   INNER JOIN ESTADIAS dias  ON ing.id_dias  = dias.id_dias
	   INNER JOIN ESTACOMA coma  ON ing.id_coma  = coma.id_coma
	   INNER JOIN HOTEUHAB uhab  ON coma.id_uhab = uhab.id_uhab
	   INNER JOIN HOTETHAB thab  ON uhab.id_thab = thab.id_thab
	   INNER JOIN HOTECONT cont  ON coma.id_cont = cont.id_cont
	   INNER JOIN SICLAMON mone  ON ing.id_mone  = mone.id_mone
	   WHERE  DATEDIFF(DAY, @desde, dias.fecha) >= 0 
		  AND DATEDIFF(DAY, @hasta, dias.fecha) <= 0) as total  
		  
   END
   ELSE
   BEGIN
   
	   CREATE TABLE #TempFactCerradas
	   (
			empresa         VARCHAR(100),
			id_hfac         INT,
			cuenta          VARCHAR(100),
			nro_cheque      INT,
			nro_documento   VARCHAR(100),
			id_origen       VARCHAR(100),
			referencia      VARCHAR(100),
			refer_thab      VARCHAR(100),
			cliente         VARCHAR(100),
			contrato        VARCHAR(100),
			agencia         VARCHAR(100),
			importe_moncob  NUMERIC(18, 2),
			mon_cob         VARCHAR(100),
			importe_monfact NUMERIC(18, 2),
			mon_fact		VARCHAR(100),
			fecha_cierre    DATETIME,
			id_operador		INT,
			cod_tcob		VARCHAR(100),
			descripcion		VARCHAR(100),
			factura			INT,
			nro_factura		VARCHAR(100),
			fecha_ent		DATETIME,
			fecha_sal		DATETIME,
			paxs			INT,
			Hospedaje		NUMERIC(18, 2),
			ayb				NUMERIC(18, 2),
			otros			NUMERIC(18, 2),
			Depositos		NUMERIC(18, 2),
			impuestos		NUMERIC(18, 2),
			descuentos		NUMERIC(18, 2),
			origen			CHAR(1),
			operador		VARCHAR(100),
			terminal		VARCHAR(100)
	   )
	   
	   INSERT #TempFactCerradas
	   EXEC Facturas_Cerradas @desde, @hasta, '', 4, '000'
	   
	   DECLARE @res_face NUMERIC(19,4),
	           @res_tarj NUMERIC(19,4),
	           @res_faca NUMERIC(19,4)
	   
	   -- De Habitaciones
	   SELECT @res_face = SUM(Hospedaje)
	   FROM   #TempFactCerradas fact
	   INNER JOIN HOTEHFAC hfac ON fact.id_hfac   = hfac.id_hfac
	   Inner Join Hotehfex hfex on hfac.id_hfac   = hfex.id_hfac
       Inner Join Hotehhab hhab on hfex.id_origen = hhab.id_face
       LEFT  JOIN HOTEHROM hrom ON hhab.id_room   = hrom.id_room
       LEFT  JOIN HOTEROOM room ON hhab.id_room   = room.id_room
       LEFT  JOIN HOTEHRES hres ON hhab.id_rese   = hres.id_rese
       LEFT  JOIN HOTERESE rese ON hhab.id_rese   = rese.id_rese
       WHERE ISNULL(hrom.id_oper, ISNULL(room.id_oper, ISNULL(hres.id_oper, ISNULL(rese.id_oper, hhab.id_oper)))) = @id_oper
       
       -- De Folios Externos
       SELECT @res_tarj = SUM(Hospedaje)
	   FROM   #TempFactCerradas fact
	   INNER JOIN HOTEHFAC hfac ON fact.id_hfac   = hfac.id_hfac
	   Inner Join Hotehfex hfex on hfac.id_hfac   = hfex.id_hfac
       Inner Join Hcretarj tarj on hfex.id_origen = tarj.id_tarj
       WHERE hfac.id_operador = @id_oper
       
       -- De Facturas de Agencia
       SELECT @res_faca = SUM(Hospedaje)
	   FROM   #TempFactCerradas fact
	   INNER JOIN HOTEHFAC hfac ON fact.id_hfac   = hfac.id_hfac
	   Inner Join Hotehfag hfag on hfac.id_hfac   = hfag.id_hfac
	   Inner Join Hotehhab hhab on hhab.id_faca   = hfag.id_faca
	   LEFT  JOIN HOTEHROM hrom ON hhab.id_room   = hrom.id_room
       LEFT  JOIN HOTEROOM room ON hhab.id_room   = room.id_room
       LEFT  JOIN HOTEHRES hres ON hhab.id_rese   = hres.id_rese
       LEFT  JOIN HOTERESE rese ON hhab.id_rese   = rese.id_rese
       WHERE ISNULL(hrom.id_oper, ISNULL(room.id_oper, ISNULL(hres.id_oper, ISNULL(rese.id_oper, hhab.id_oper)))) = @id_oper
       
       SET @result = ISNULL(@res_face, 0) + ISNULL(@res_tarj, 0) + ISNULL(@res_faca, 0)
       
       DROP TABLE #TempFactCerradas
	   
   END

   EXECUTE DSS_Ingresos_Hospedaje_Fechas_Reservas 
           @desde, @hasta, @tasa, @id_oper,
           @result_reservas OUTPUT
           

   EXECUTE DSS_Ingresos_Hospedaje_Fechas_Rooming 
           @desde, @hasta, @tasa, @id_oper,
           @result_rooming  OUTPUT
           

   SELECT @result = @result + ISNULL(@result_reservas, 0) + ISNULL(@result_rooming, 0)	

   RETURN @result

END





