
/****** Object:  StoredProcedure [dbo].[Calc_Ing_Reserva]    Script Date: 08/17/2014 11:12:16 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO




DROP PROCEDURE dbo.DSS_Calc_Ing_Reserva
GO

CREATE PROCEDURE dbo.DSS_Calc_Ing_Reserva
	   @dia_inic    DATETIME, 
	   @dia_fin     DATETIME, 
	   @mone        INT, 
	   @tasa_ext    NUMERIC(13, 8), 
	   @id_oper     INT,
	   @ing_reserva NUMERIC(15, 2) OUTPUT
AS

/*   Calcula el pronostico de ingresos de extras por reservas en la moneda base del Front , para el dia pasado como parametro */

BEGIN

	DECLARE @tasa_mb NUMERIC(13, 8), 
			@fecha_hotel DATETIME
			
	SELECT @tasa_mb = tasa, 
		   @fecha_hotel = fecha_hotelera
	FROM       Hotetabl tabl
	INNER JOIN Siclamon sicla ON tabl.id_mone = sicla.id_mone 
           
	-- Recordar que Front siempre guarda en HOTELRES el precio con impuesto incluido. 
	DECLARE @ImpuHospedaje NUMERIC(18, 2) = ISNULL((SELECT TOP 1 SUM(im.porc_impu) impu_hosp
													FROM       HOTETHAB th
													INNER JOIN HOTECON0 c0 ON th.id_con0 = c0.id_con0
													INNER JOIN HOTEPRE0 p0 ON c0.id_con0 = p0.id_con0
													INNER JOIN HOTEIMPU ip ON p0.id_pre0 = ip.id_pre0
													INNER JOIN SICLAIMP im ON ip.id_impu = im.id_impu
													INNER JOIN SICLAMON mo ON p0.id_mone = mo.id_mone
													WHERE mo.tasa = 1 --AND ip.incluido = 1
													GROUP BY th.id_thab), 0) / 100 + 1

	SELECT @ing_reserva = SUM(CONVERT(NUMERIC(15, 2), precio_hab / @ImpuHospedaje) * cant_habi * CASE WHEN cont.id_mone != @mone THEN @tasa_ext / sicla.tasa
																									  ELSE 1
																								      END)
	FROM       Hoterese rese
	INNER JOIN Hotelres lres  ON rese.id_rese = lres.id_rese
	INNER JOIN Hotecont cont  ON rese.id_cont = cont.id_cont
	INNER JOIN Siclamon sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR rese.id_oper = @id_oper)
	  AND (DATEDIFF(day, rese.fecha_ent, @dia_inic) >= 0
	  AND  DATEDIFF(day, @dia_inic, rese.fecha_sal) > 0
	   OR  DATEDIFF(day, rese.fecha_ent, @dia_fin) >= 0
	  AND  DATEDIFF(day, @dia_fin, rese.fecha_sal) > 0)
	  AND rese.id_estado IN (2, 3, 5)
	  AND rese.id_rese NOT IN (SELECT ISNULL(id_rese, 0) FROM Hoteface)
              
	-- Vamos a incluir el histórico para que no hayan diferencias con DSS
	SELECT @ing_reserva = COALESCE(@ing_reserva, 0) 
		+ COALESCE(SUM(CONVERT(NUMERIC(15, 2), precio_hab / @ImpuHospedaje) * cant_habi * CASE WHEN cont.id_mone != @mone THEN @tasa_ext / sicla.tasa
																							   ELSE 1
																							   END), 0.00)
	FROM       Hotehres rese
	INNER JOIN Hotehlre lres  ON rese.id_rese = lres.id_rese
	INNER JOIN Hotecont cont  ON rese.id_cont = cont.id_cont
	INNER JOIN Siclamon sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR rese.id_oper = @id_oper)
	  AND (DATEDIFF(day, rese.fecha_ent, @dia_inic) >= 0
	  AND  DATEDIFF(day, @dia_inic, rese.fecha_sal) > 0
	   OR  DATEDIFF(day, rese.fecha_ent, @dia_fin) >= 0
	  AND  DATEDIFF(day, @dia_fin, rese.fecha_sal) > 0)
	  AND rese.id_estado IN (2, 3, 5)
	  AND rese.id_rese NOT IN (SELECT ISNULL(id_rese, 0) FROM Hoteface)

	SELECT @ing_reserva = COALESCE(@ing_reserva, 0) + COALESCE(SUM(reau.precio 
		* reau.cantidad * CASE WHEN cont.id_mone != @mone THEN @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
							   ELSE 1
							   END), 0.00)
	FROM       Hoterese rese
	INNER JOIN Hotelres lres  ON rese.id_rese = lres.id_rese
	INNER JOIN Hotecont cont  ON rese.id_cont = cont.id_cont
	INNER JOIN Hotereau reau  ON lres.id_lres = reau.id_lres
	INNER JOIN Siclamon sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR rese.id_oper = @id_oper)
	  AND (DATEDIFF(day, reau.fecha_desde, @dia_inic) >= 0
	  AND  DATEDIFF(day, @dia_inic, reau.fecha_hasta) > 0
	   OR  DATEDIFF(day, reau.fecha_desde, @dia_fin) >= 0
	  AND  DATEDIFF(day, @dia_fin, reau.fecha_hasta) > 0)
	  AND rese.id_estado IN (2, 3, 5)
	  AND reau.id_con0 IN (SELECT thab.id_con0 FROM hotethab thab)
	  AND rese.id_rese NOT IN (SELECT ISNULL(id_rese, 0) FROM Hoteface)
              
	-- Vamos a incluir el histórico para que no hayan diferencias con DSS
	SELECT @ing_reserva = COALESCE(@ing_reserva, 0) + COALESCE(SUM(reau.precio 
		* reau.cantidad * CASE WHEN cont.id_mone != @mone THEN @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
							   ELSE 1
							   END), 0.00)
	FROM       Hotehres  rese
	INNER JOIN Hotehlre  lres  ON rese.id_rese = lres.id_rese
	INNER JOIN Hotecont  cont  ON rese.id_cont = cont.id_cont
	INNER JOIN Hotehreau reau  ON lres.id_lres = reau.id_lres
	INNER JOIN Siclamon  sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR rese.id_oper = @id_oper)
	  AND (DATEDIFF(day, reau.fecha_desde, @dia_inic) >= 0
	  AND  DATEDIFF(day, @dia_inic, reau.fecha_hasta) > 0
	  OR   DATEDIFF(day, reau.fecha_desde, @dia_fin) >= 0
	  AND  DATEDIFF(day, @dia_fin, reau.fecha_hasta) > 0)
	  AND rese.id_estado IN (2, 3, 5)
	  AND reau.id_con0 IN (SELECT thab.id_con0 FROM hotethab thab)
	  AND rese.id_rese NOT IN (SELECT ISNULL(id_rese, 0) FROM Hoteface)

	SELECT @ing_reserva = COALESCE(@ing_reserva, 0)
	
END







