
/****** Object:  StoredProcedure [dbo].[Calc_Ing_Reserva_Reg]    Script Date: 08/17/2014 11:15:25 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






DROP PROCEDURE dbo.DSS_Calc_Ing_Reserva_Reg
GO


CREATE PROCEDURE dbo.DSS_Calc_Ing_Reserva_Reg
	   @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
	SET NOCOUNT ON

	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

	SELECT @ing_reserva = SUM(Precio / (impuesto / 100 + 1) * cantidad * cant_habi)
	FROM(
		SELECT lres.cant_habi, 
			   reau.precio, 
			   reau.cantidad, 
			   SUM(COALESCE(D.porc_impu, 0))AS "Impuesto"
		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 HOTEPRE0 B     ON B.id_con0    = REAU.id_con0
		INNER JOIN SICLAMON T     ON t.id_mone    = B.id_mone AND t.tasa = 1
		LEFT  JOIN HOTEIMPU C     ON C.id_pre0    = B.id_pre0 OR COALESCE(C.id_pre0, 0) = 0
		LEFT  JOIN SICLAIMP D     ON D.id_impu    = C.id_impu
		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 rali.id_con0 FROM hoterali rali WHERE rali.id_ralo = lres.id_ralo)
		  AND rese.id_rese NOT IN (SELECT ISNULL(id_rese, 0) FROM Hoteface)
		GROUP BY reau.precio, 
				 reau.cantidad, 
				 lres.id_lres, 
				 lres.cant_habi) AS Total
     
	-- Vamos a incluir el histórico para que no hayan diferencias con DSS
	SET @ing_reserva = COALESCE(@ing_reserva, 0) 
		+ COALESCE((SELECT SUM(Precio / (impuesto / 100 + 1) * cantidad * cant_habi)
			FROM(
				SELECT lres.cant_habi, 
					   reau.precio, 
					   reau.cantidad, 
					   SUM(COALESCE(D.porc_impu, 0)) AS "Impuesto"
				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 HOTEPRE0  B    ON B.id_con0    = REAU.id_con0
				INNER JOIN SICLAMON  T    ON t.id_mone    = B.id_mone AND t.tasa = 1
				LEFT  JOIN HOTEIMPU  C    ON C.id_pre0    = B.id_pre0 OR COALESCE(C.id_pre0, 0) = 0
				LEFT  JOIN SICLAIMP  D    ON D.id_impu    = C.id_impu
				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 rali.id_con0 FROM hoterali rali WHERE rali.id_ralo = lres.id_ralo)
				  AND rese.id_rese NOT IN (SELECT ISNULL(id_rese, 0) FROM Hoteface)
				GROUP BY reau.precio, 
					     reau.cantidad, 
					     lres.id_lres, 
					     lres.cant_habi) AS Total), 0)

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






