
/****** Object:  StoredProcedure [dbo].[Pron_IngR_Reserva]    Script Date: 04/03/2013 09:16:19 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

DROP PROCEDURE dbo.Pron_IngR_Reserva
GO

CREATE PROCEDURE dbo.Pron_IngR_Reserva
	   @dia_inic   DATETIME, 
	   @dia_fin    DATETIME, 
	   @dia_reserv DATETIME, 
	   @mone       INT, 
	   @tasa_ext   NUMERIC(13, 8),
	   @id_oper    INT
AS

/*   Calcula ingreso de Hospedaje en RESERVAS, dentro un rango de fechas a base de la fecha de reservacion hacia el pasado */

BEGIN

	DECLARE @Ing_LocalI NUMERIC(15, 2), 
			@Ing_LocalII NUMERIC(15, 2), 
			@Ing_LocalIII NUMERIC(15, 2), 
			@mese VARCHAR(10), 
			@ciclo DATETIME, 
			@ing_reserva_reg NUMERIC(15, 2)
			
	DECLARE @Tab_PREH TABLE
	(
		id_rese INT, 
		id_cont INT, 
		id_estado INT, 
		id_mone INT, 
		tasa NUMERIC(13, 8), 
		fecha_ent DATETIME, 
		fecha_sal DATETIME, 
		fecha_reserv DATETIME, 
		cant_habi INT, 
		precio_hab MONEY, 
		precio NUMERIC(18, 2), 
		cantidad TINYINT, 
		Impuesto NUMERIC(8, 2), 
		nombre VARCHAR(40), 
		nomb_reserv VARCHAR(40), 
		nro_reserva INT, 
		fecha_anulada DATETIME, 
		id_lres INT, 
		id_ralo INT, 
		id_agen INT, 
		tasa_fija NUMERIC(13, 8), 
		cod_mone CHAR(3)
	)

	DECLARE @Tab_PREHII TABLE
	(
		id_rese INT, 
		id_cont INT, 
		id_estado INT, 
		id_mone INT, 
		tasa NUMERIC(13, 8), 
		fecha_ent DATETIME, 
		fecha_sal DATETIME, 
		fecha_reserv DATETIME, 
		cant_habi INT, 
		precio_hab MONEY, 
		precio NUMERIC(18, 2), 
		cantidad TINYINT, 
		Impuesto NUMERIC(8, 2), 
		nombre VARCHAR(40), 
		nomb_reserv VARCHAR(40), 
		nro_reserva INT, 
		fecha_anulada DATETIME, 
		id_lres INT, 
		id_ralo INT, 
		id_agen INT, 
		tasa_fija NUMERIC(13, 8), 
		cod_mone CHAR(3)
	)							 


     
	/* CREA TABLA DE POSIBLES DATOS Pasado--Futuro */

	INSERT INTO @Tab_PREH
	SELECT *
	FROM(
		SELECT rese.id_rese, 
			   rese.id_cont, 
			   id_estado, 
			   cont.id_mone, 
			   sicla.tasa, 
			   reau.fecha_desde fecha_ent, 
			   reau.fecha_hasta fecha_sal, 
			   fecha_reserv, 
			   cant_habi, 
			   precio_hab, 
			   reau.precio, 
			   reau.cantidad, 
			   SUM(COALESCE(D.porc_impu, 0))AS "Impuesto", 
			   nombre, 
			   nomb_reserv, 
			   nro_reserva, 
			   fecha_anulada, 
			   lres.id_lres, 
			   lres.id_ralo, 
			   id_agen, 
			   tasa_fija, 
			   sicla.cod_mone
		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 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)
		GROUP BY rese.id_rese, rese.id_cont, id_estado, 
				 cont.id_mone, sicla.tasa, 
				 fecha_desde, fecha_hasta, 
				 fecha_reserv, cant_habi, 
				 precio_hab, reau.precio, 
				 reau.cantidad, nombre, nomb_reserv, 
				 nro_reserva, fecha_anulada, lres.id_lres, 
				 lres.id_ralo, id_agen, tasa_fija, 
				 sicla.cod_mone
	)AS TotalI

	INSERT INTO @Tab_PREHII
	SELECT *
	FROM(
		SELECT rese.id_rese, 
			   rese.id_cont, 
			   id_estado, 
			   cont.id_mone, 
			   sicla.tasa, 
			   reau.fecha_desde fecha_ent, 
			   reau.fecha_hasta fecha_sal, 
			   fecha_reserv, 
			   cant_habi, 
			   precio_hab, 
			   reau.precio, 
			   reau.cantidad, 
			   SUM(COALESCE(D.porc_impu, 0))AS "Impuesto", 
			   nombre, 
			   nomb_reserv, 
			   nro_reserva, 
			   fecha_anulada, 
			   lres.id_lres, 
			   lres.id_ralo, 
			   id_agen, 
			   tasa_fija, 
			   sicla.cod_mone
		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 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)
		GROUP BY rese.id_rese, rese.id_cont, id_estado, 
				 cont.id_mone, sicla.tasa, 
				 fecha_desde, fecha_hasta, 
				 fecha_reserv, cant_habi, 
				 precio_hab, reau.precio, reau.cantidad, 
				 nombre, nomb_reserv, nro_reserva, 
				 fecha_anulada, lres.id_lres, lres.id_ralo, 
				 id_agen, tasa_fija, 
				 sicla.cod_mone
	)AS TotalII   
	
	DELETE FROM @Tab_PREH
	WHERE id_estado = 2 
	  AND id_rese  IN  (SELECT ISNULL(id_rese, 0) 
	                    FROM   Hoteface
	                    WHERE  CAST(fecha_ent AS DATE) <= @dia_reserv)
						  
	DELETE FROM @Tab_PREHII
	WHERE id_estado = 2 
	  AND id_rese  IN  (SELECT ISNULL(id_rese, 0) 
	                    FROM   Hoteface
	                    WHERE  CAST(fecha_ent AS DATE) <= @dia_reserv)            
				              
	/* CREA CONSULTA DATOS DE INGRESO DE HOSPEDAJE POR MES */

	SET @mese = LEFT(YEAR(@dia_inic), 4) + RIGHT('0' + LEFT(MONTH(@dia_inic), 2), 2)

	WHILE @mese <= LEFT(YEAR(@dia_fin), 4) + RIGHT('0' + LEFT(MONTH(@dia_fin), 2), 2)
	BEGIN 
		
		SELECT @ciclo = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dia_inic)+1,0)) -- Obtener último día del mes en base a la fecha dada.

		SET @ing_reserva_reg = 0
		
		WHILE CAST(@dia_inic AS DATE) <= CAST(@ciclo AS DATE)
		BEGIN
		
			SELECT @Ing_LocalI = COALESCE(SUM(Precio / ((impuesto/100) + 1) * cantidad * CASE WHEN id_mone != @mone THEN @tasa_ext / COALESCE(tasa_fija, tasa)
																							  ELSE 1
																							  END * cant_habi), 0.00) 
			FROM @Tab_PREH
			WHERE DATEDIFF(DAY, @dia_reserv, fecha_reserv) <= 0
			  AND DATEDIFF(day, fecha_ent, @dia_inic) >= 0
			  AND DATEDIFF(day, @dia_inic, fecha_sal) > 0
              
			SELECT @Ing_LocalII = COALESCE(SUM(Precio / ((impuesto/100) + 1) * cantidad * CASE WHEN id_mone != @mone THEN @tasa_ext / COALESCE(tasa_fija, tasa)
																							   ELSE 1
																						       END * cant_habi), 0.00) 
			FROM @Tab_PREHII
			WHERE DATEDIFF(DAY, @dia_reserv, fecha_reserv) <= 0
			  AND DATEDIFF(day, fecha_ent, @dia_inic) >= 0
			  AND DATEDIFF(day, @dia_inic, fecha_sal) > 0
			  
			/*******************************************************************************************
			 *******************************************************************************************
			 *** Pronóstico de HOTELINC por concepto de RÉGIMEN - Original: dbo.Calc_Ing_RegExtra     **
			 *******************************************************************************************
			 *******************************************************************************************/
			
			SELECT @Ing_LocalIII = SUM(cantidad_linc * CONVERT(NUMERIC(15, 2), precio_linc) * CASE WHEN Reg1.id_mone != @mone THEN @tasa_ext / sicla.tasa
																					               ELSE 1
																				                   END
																				                   
								   / ((ISNULL(Reg1.porc_impu, 0) / 100) + 1))
			FROM(
				SELECT  fact.id_fact, 
						pre0.id_pre0, 
						face.id_mone, 
						face.fecha_ent,
						SUM(ISNULL(im.porc_impu, 0)) porc_impu
				FROM       Hoteface face
				LEFT  JOIN HOTEHROM hrom  ON face.id_room = hrom.id_room
				LEFT  JOIN HOTEROOM room  ON face.id_room = room.id_room
				LEFT  JOIN HOTEHRES hres  ON face.id_rese = hres.id_rese
				LEFT  JOIN HOTERESE rese  ON face.id_rese = rese.id_rese
				INNER JOIN Hotefact fact  ON face.id_face = fact.id_face
				INNER JOIN Hotehabi habi  ON face.id_habi = habi.id_habi
				INNER JOIN HoteRali rali  ON face.id_ralo = rali.id_ralo
				INNER JOIN Hotepre0 pre0  ON face.id_mone = pre0.id_mone
								         AND rali.id_con0 = pre0.id_con0 -- Sea de Rég.
				LEFT  JOIN HOTEIMPU si    ON pre0.id_pre0 = si.id_pre0
			    LEFT  JOIN SICLAIMP im    ON si.id_impu   = im.id_impu
				WHERE (@id_oper = 0 OR ISNULL(hrom.id_oper, ISNULL(room.id_oper, ISNULL(hres.id_oper, ISNULL(rese.id_oper, face.id_oper)))) = @id_oper)
				  --AND CAST(face.fecha_ent AS DATE) <= @dia_reserv
			    GROUP BY fact.id_fact, pre0.id_pre0, face.id_mone, face.fecha_ent
			) Reg1
			INNER JOIN Hotelinc linc  ON reg1.id_fact = linc.id_fact
						             AND reg1.id_pre0 = linc.id_pre0
			INNER JOIN Siclamon sicla ON reg1.id_mone = sicla.id_mone
			WHERE CAST(Reg1.fecha_ent AS DATE) <= @dia_reserv
			  AND (DATEDIFF(day, linc.fecha_desde, @dia_inic) >= 0
			  AND DATEDIFF(day, @dia_inic, linc.fecha_hasta) > 0
			   OR DATEDIFF(day, linc.fecha_desde, @dia_inic) >= 0
			  AND DATEDIFF(day, @dia_inic, linc.fecha_hasta) > 0)  
			
			
			--SELECT @dia_inic Fecha, @ing_reserva_reg IngresoAct, COALESCE(@Ing_LocalI, 0) Vivo, COALESCE(@Ing_LocalII, 0) Hist
			
			UPDATE DEBGDAIPG
			SET ing_Reserva_Reg = COALESCE(@Ing_LocalI, 0) + COALESCE(@Ing_LocalII, 0) + COALESCE(@Ing_LocalIII, 0)
			WHERE fecha = CAST(@dia_inic AS DATE)

			SET @dia_inic = @dia_inic + 1
			SET @ing_reserva_reg = @ing_reserva_reg + COALESCE(@Ing_LocalI, 0) + COALESCE(@Ing_LocalII, 0) + COALESCE(@Ing_LocalIII, 0)
			
			SET @Ing_LocalI = 0
			SET @Ing_LocalII = 0
		END
		
		/* CREA TABLA CON DATOS DE INGRESO DE HOSPEDAJE POR MES */

		UPDATE HOTEDAIPG
		SET ing_Reserva_Reg = @ing_reserva_reg
		WHERE mes = MONTH(@ciclo)
		  AND fyear = YEAR(@ciclo)

		SET @mese = LEFT(YEAR(@dia_inic), 4) + RIGHT('0' + LEFT(MONTH(@dia_inic), 2), 2)

	END 
  
END



