
/****** Object:  StoredProcedure [dbo].[Calc_Ing_Reserva_Otr]    Script Date: 08/17/2014 11:13:54 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO







DROP PROCEDURE dbo.DSS_Calc_Ing_Reserva_Otr
GO


CREATE PROCEDURE dbo.DSS_Calc_Ing_Reserva_Otr
	   @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, 
			@ing_reservaII NUMERIC(15, 2)
			
	DECLARE @Tab_IngOAgenI TABLE
	(
		id_room   INT, 
		id_cfag   INT, 
		precio    NUMERIC(15, 2), 
		cantidad  INT, 
		id_mone   INT, 
		tasa_fija NUMERIC(13, 4), 
		tasa      NUMERIC(13, 4), 
		impuesto  NUMERIC(8, 2), 
		incluido  INT, 
		cant_habi INT
	)

	DECLARE @Tab_IngOAgenII TABLE
	(
		id_room   INT, 
		id_cfag   INT, 
		precio    NUMERIC(15, 2), 
		cantidad  INT, 
		id_mone   INT, 
		tasa_fija NUMERIC(13, 4), 
		tasa      NUMERIC(13, 4), 
		impuesto  NUMERIC(8, 2), 
		incluido  INT, 
		cant_habi INT
	)

	SELECT @tasa_mb = tasa, 
		   @fecha_hotel = fecha_hotelera
	FROM       Hotetabl tabl
	INNER JOIN Siclamon sicla ON tabl.id_mone = sicla.id_mone

	SELECT @ing_reservaII = SUM(Precio / (impuesto / 100 + 1) * cantidad * cant_habi)
	FROM(
		SELECT reau.precio, 
			   reau.cantidad, 
			   SUM(COALESCE(D.porc_impu, 0))AS "Impuesto", 
			   lres.cant_habi
		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, @fecha_hotel, fecha_ent) >= 0
		  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 NOT IN (SELECT rali.id_con0 FROM hoterali rali)
		  AND reau.id_con0 NOT IN (SELECT thab.id_con0 FROM hotethab thab)
		  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

	--SELECT @ing_reservaII = COALESCE(@ing_reservaII, 0)
	
	SELECT @ing_reservaII = COALESCE(@ing_reservaII, 0) + COALESCE((SELECT SUM(Precio / (impuesto / 100 + 1) * cantidad * cant_habi)
	FROM(
		SELECT reau.precio, 
			   reau.cantidad, 
			   SUM(COALESCE(D.porc_impu, 0))AS "Impuesto", 
			   lres.cant_habi
		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, @fecha_hotel, fecha_ent) >= 0
		  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 NOT IN (SELECT rali.id_con0 FROM hoterali rali)
		  AND reau.id_con0 NOT IN (SELECT thab.id_con0 FROM hotethab thab)
		  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_reservaII = COALESCE(@ing_reservaII, 0)

	INSERT INTO @Tab_IngOAgenI
	SELECT rese.id_rese, 
		   reau.id_cfag, 
		   reau.precio, 
		   reau.cantidad, 
		   cont.id_mone, 
		   cont.tasa_fija, 
		   sicla.tasa, 
		   Impuesto = COALESCE(D.porc_impu, 0), 
		   incluido, 
		   lres.cant_habi
	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 HOTEREAG reau  ON lres.id_lres = reau.id_lres
	INNER JOIN HOTECFAG B     ON B.id_cfag    = REAU.id_cfag
	INNER JOIN SICLAMON T     ON t.id_mone    = cont.id_mone AND t.tasa = 1
	LEFT  JOIN HOTEIMAG C     ON C.id_cfag    = B.id_cfag
	INNER JOIN HOTEIMCO imco  ON cont.id_cont = imco.id_cont AND C.id_impu = imco.id_impu
	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, @fecha_hotel, fecha_ent) >= 0
	  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 rese.id_rese NOT IN (SELECT ISNULL(id_rese, 0) FROM Hoteface)
	  AND ISNULL(B.id_uhab, 0) = 0
	  AND ISNULL(B.id_ralo, 0) = 0
	  
	INSERT INTO @Tab_IngOAgenI
	SELECT rese.id_rese, 
		   reau.id_cfag, 
		   reau.precio, 
		   reau.cantidad, 
		   cont.id_mone, 
		   cont.tasa_fija, 
		   sicla.tasa, 
		   Impuesto = COALESCE(D.porc_impu, 0), 
		   incluido, 
		   lres.cant_habi
	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 HOTEHREAG reau  ON lres.id_lres = reau.id_lres
	INNER JOIN HOTECFAG  B     ON B.id_cfag    = REAU.id_cfag
	INNER JOIN SICLAMON  T     ON t.id_mone    = cont.id_mone AND t.tasa = 1
	LEFT  JOIN HOTEIMAG  C     ON C.id_cfag    = B.id_cfag
	INNER JOIN HOTEIMCO  imco  ON cont.id_cont = imco.id_cont AND C.id_impu = imco.id_impu
	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, @fecha_hotel, fecha_ent) >= 0
	  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 rese.id_rese NOT IN (SELECT ISNULL(id_rese, 0) FROM Hoteface)
	  AND ISNULL(B.id_uhab, 0) = 0
	  AND ISNULL(B.id_ralo, 0) = 0

	INSERT INTO @Tab_IngOAgenII
	SELECT *
	FROM   @Tab_IngOAgenI
	WHERE  incluido = 1

	UPDATE @Tab_IngOAgenI
	SET    incluido = 1
	FROM       @Tab_IngOAgenI  T1
	INNER JOIN @Tab_IngOAgenII T2 ON T1.id_room = T2.id_room
	WHERE  t1.incluido = 0

	UPDATE @Tab_IngOAgenI
	SET    impuesto = 0
	WHERE  incluido = 0

	SELECT @ing_reserva = SUM(precio / (Impuesto / 100 + 1) * cantidad 
		* cant_habi * CASE WHEN id_mone != 3 THEN 1 / COALESCE(tasa_fija, tasa)
						   ELSE 1
						   END)
	FROM(
		SELECT id_room, 
			   precio, 
			   cantidad, 
			   id_mone, 
			   tasa_fija, 
			   tasa, 
			   incluido, 
			   SUM(COALESCE(Impuesto, 0)) AS "Impuesto", 
			   cant_habi
		FROM @Tab_IngOAgenI
		GROUP BY id_room, 
				 precio, 
				 cantidad, 
				 id_mone, 
				 tasa_fija, 
				 tasa, 
				 incluido, 
				 cant_habi) AS Total


	SELECT @ing_reserva = COALESCE(@ing_reservaII, 0) + COALESCE(@ing_reserva, 0)

END







     