
/****** Object:  StoredProcedure [dbo].[Pron_IngO_Reserva]    Script Date: 04/03/2013 10:07:16 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

DROP PROCEDURE dbo.Pron_IngO_Reserva
GO

CREATE PROCEDURE dbo.Pron_IngO_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_Local NUMERIC(15, 2), 
			@mese VARCHAR(10), 
			@ciclo DATETIME, 
			@ing_reserva_Otr NUMERIC(15, 2), 
			@ing_LocalII NUMERIC(15, 2),
			@ing_LocalIII NUMERIC(15, 2)

	CREATE TABLE #Tab_PREH(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, 
						   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), 
						   Por_Imp NUMERIC(5, 2))

	CREATE TABLE #Tab_PREHA(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, 
							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), 
							Por_Imp NUMERIC(5, 2), 
							incluido INT)

	CREATE TABLE #Tab_PREHAII(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, 
							  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), 
							  Por_Imp NUMERIC(5, 2), 
							  incluido INT)						 

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

	INSERT INTO #Tab_PREH
	SELECT rese.id_rese, 
		   rese.id_cont, 
		   id_estado, 
		   cont.id_mone, 
		   t.tasa, 
		   reau.fecha_desde fecha_ent, 
		   reau.fecha_hasta fecha_sal, 
		   fecha_reserv, 
		   cant_habi, 
		   precio_hab, 
		   reau.precio, 
		   cantidad, 
		   nombre, 
		   nomb_reserv, 
		   nro_reserva, 
		   fecha_anulada, 
		   lres.id_lres, 
		   lres.id_ralo, 
		   id_agen, 
		   tasa_fija, 
		   t.cod_mone, 
		   D.porc_impu
	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
	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
	WHERE (@id_oper = 0 OR rese.id_oper = @id_oper)
	  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)
	  
	--UNION
	INSERT INTO #Tab_PREH
	SELECT rese.id_rese, 
		   rese.id_cont, 
		   id_estado, 
		   cont.id_mone, 
		   t.tasa, 
		   reau.fecha_desde fecha_ent, 
		   reau.fecha_hasta fecha_sal, 
		   fecha_reserv, 
		   cant_habi, 
		   precio_hab, 
		   reau.precio, 
		   cantidad, 
		   nombre, 
		   nomb_reserv, 
		   nro_reserva, 
		   fecha_anulada, 
		   lres.id_lres, 
		   lres.id_ralo, 
		   id_agen, 
		   tasa_fija, 
		   t.cod_mone, 
		   D.porc_impu
	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
	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
	WHERE (@id_oper = 0 OR rese.id_oper = @id_oper)
	  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)
	ORDER BY fecha_reserv


	INSERT INTO #Tab_PREHA
	SELECT rese.id_rese, 
		   rese.id_cont, 
		   id_estado, 
		   cont.id_mone, 
		   sicla.tasa, 
		   reag.fecha_desde fecha_ent, 
		   reag.fecha_hasta fecha_sal, 
		   fecha_reserv, 
		   cant_habi, 
		   precio_hab = lres.precio_hab, 
		   reag.precio, 
		   cantidad, 
		   nombre, 
		   nomb_reserv, 
		   nro_reserva, 
		   fecha_anulada, 
		   lres.id_lres, 
		   lres.id_ralo, 
		   id_agen, 
		   tasa_fija, 
		   sicla.cod_mone, 
		   Por_Imp = COALESCE(D.porc_impu, 0), 
		   incluido
	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 reag  ON lres.id_lres = reag.id_lres
	INNER JOIN HOTECFAG B     ON B.id_cfag = REAg.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 rese.id_estado IN (2, 3, 5)
	  AND ISNULL(B.id_uhab, 0) = 0
	  AND ISNULL(B.id_ralo, 0) = 0
	  
	--UNION -- provoca errores el union con esta, los calculos dan mal o.O
	INSERT INTO #Tab_PREHA
	SELECT rese.id_rese, 
		   rese.id_cont, 
		   id_estado, 
		   cont.id_mone, 
		   sicla.tasa, 
		   reag.fecha_desde fecha_ent, 
		   reag.fecha_hasta fecha_sal, 
		   fecha_reserv, 
		   cant_habi, 
		   precio_hab = lres.precio_hab, 
		   reag.precio, 
		   cantidad, 
		   nombre, 
		   nomb_reserv, 
		   nro_reserva, 
		   fecha_anulada, 
		   lres.id_lres, 
		   lres.id_ralo, 
		   id_agen, 
		   tasa_fija, 
		   sicla.cod_mone, 
		   Por_Imp = COALESCE(D.porc_impu, 0), 
		   incluido
	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 reag  ON lres.id_lres = reag.id_lres
	INNER JOIN HOTECFAG  B     ON B.id_cfag = REAg.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 rese.id_estado IN (2, 3, 5)
	  AND ISNULL(B.id_uhab, 0) = 0
	  AND ISNULL(B.id_ralo, 0) = 0
	ORDER BY fecha_reserv


	INSERT INTO #Tab_PREHAII
	SELECT *
	FROM #Tab_PREHA
	WHERE incluido = 1

	UPDATE #Tab_PREHA
	SET incluido = 1
	FROM       #Tab_PREHA   T1
	INNER JOIN #Tab_PREHAII T2 ON T1.id_rese = T2.id_rese
	WHERE t1.incluido = 0

	UPDATE #Tab_PREHA
	SET   Por_Imp = 0
	WHERE incluido = 0
	
	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_PREHA
	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_Otr = 0
			WHILE @dia_inic <= @ciclo
				BEGIN

					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(
						SELECT id_rese,
						       precio, 
							   cantidad, 
							   id_mone, 
							   tasa, 
							   tasa_fija, 
							   SUM(COALESCE(Por_Imp, 0))AS "Impuesto",
							   cant_habi
						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
						GROUP BY id_rese, precio, cantidad, id_mone, tasa, tasa_fija, cant_habi) AS totalX
		
					--set @ing_LocalII= Coalesce(@ing_localII ,0) 

					SELECT @ing_Local = 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(
						SELECT id_rese,
						       precio, 
							   cantidad, 
							   id_mone, 
							   tasa, 
							   tasa_fija, 
							   incluido,
							   SUM(COALESCE(Por_Imp, 0))AS "Impuesto",
							   cant_habi
						FROM #Tab_PREHA
						WHERE DATEDIFF(DAY, @dia_reserv, fecha_reserv) <= 0
						  AND DATEDIFF(day, fecha_ent, @dia_inic) >= 0
						  AND DATEDIFF(day, @dia_inic, fecha_sal) > 0
						GROUP BY id_rese, precio, cantidad, id_mone, tasa, tasa_fija, incluido, cant_habi) AS totalXY
						
						
					/*******************************************************************************************
					 *******************************************************************************************
					 ***     Pronóstico de HOTELINC por concepto de EXTRAS - Original: dbo.Calc_Ing_OExtra    **
					 *******************************************************************************************
					 *******************************************************************************************/
					 
					SELECT cantidad_linc, precio_linc, fact.id_mone, sicla.tasa, SUM(ISNULL(im.porc_impu, 0)) porc_impu
					INTO   #DSSCIngOExtr
					FROM       Hoteface face
					INNER JOIN Hotefact fact  ON face.id_face = fact.id_face
					INNER JOIN Hotelinc linc  ON fact.id_fact = linc.id_fact
					INNER JOIN Siclamon sicla ON fact.id_mone = sicla.id_mone
					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
					LEFT  JOIN HOTEIMPU si    ON linc.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 (DATEDIFF(day, linc.fecha_desde, @dia_inic) >= 0
					  AND DATEDIFF(day, @dia_inic, linc.fecha_hasta) > 0
					   OR DATEDIFF(day, linc.fecha_desde, @dia_fin) >= 0
					  AND DATEDIFF(day, @dia_fin, linc.fecha_hasta) > 0)
					 AND linc.id_pre0 NOT IN(
						SELECT pre0.id_pre0
						FROM Hoteface face
						INNER JOIN Hotefact fact ON face.id_face = fact.id_face
						INNER JOIN Hotehabi habi ON face.id_habi = habi.id_habi
						INNER JOIN Hotethab thab ON habi.id_thab = thab.id_thab
						INNER JOIN Hotepre0 pre0 ON face.id_mone = pre0.id_mone AND thab.id_con0 = pre0.id_con0) -- No sea Hosp.
					 AND linc.id_pre0 NOT IN(
						SELECT pre0.id_pre0
						FROM       HoteRali rali
						INNER JOIN Hotepre0 pre0 ON rali.id_con0 = pre0.id_con0) -- No sea Rég.
					 AND CAST(face.fecha_ent AS DATE) <= CAST(@dia_reserv AS DATE)
					GROUP BY cantidad_linc, precio_linc, fact.id_mone, sicla.tasa
						
						
					SELECT @ing_LocalIII = SUM(cantidad_linc * CONVERT(NUMERIC(15, 2), precio_linc) * CASE WHEN id_mone != @mone THEN @tasa_ext / tasa
																									       ELSE 1
																									       END
										   / ((ISNULL(porc_impu, 0) / 100) + 1))
					FROM   #DSSCIngOExtr
					
					DROP TABLE #DSSCIngOExtr
						
					UPDATE DEBGDAIPG
					SET ing_reserva_Otr = COALESCE(@Ing_Local, 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_Otr = @ing_reserva_Otr + COALESCE(@Ing_Local, 0) + COALESCE(@ing_localII, 0) + COALESCE(@ing_localIII, 0)
					SET @Ing_Local = 0
					SET @ing_LocalII = 0
				END   	
		
			/* CREA TABLA CON DATOS DE INGRESO DE HOSPEDAJE POR MES */

			UPDATE HOTEDAIPG
			SET ing_reserva_Otr = @ing_reserva_Otr
			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



