
/****** Object:  StoredProcedure [dbo].[Pron_IngAG_Reserva]    Script Date: 04/03/2013 11:03:40 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

DROP PROCEDURE dbo.Pron_IngAG_Reserva
GO

CREATE PROCEDURE dbo.Pron_IngAG_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_loc_Ro_Pen NUMERIC(15, 2), 
			@ing_loc_Ro_Al NUMERIC(15, 2), 
			@ing_loc_OAgen NUMERIC(15, 2), 
			@mese VARCHAR(10), 
			@ciclo DATETIME, 
			@ing_Ro_Pen NUMERIC(15, 2), 
			@ing_Ro_Al NUMERIC(15, 2), 
			@ing_OAgen NUMERIC(15, 2)
	DECLARE @id_rool INT, 
			@id_uhab INT, 
			@id_ralo TINYINT, 
			@precio NUMERIC(15, 2), 
			@tasa NUMERIC(13, 8), 
			@id_cont INT, 
			@id_cfag INT, 
			@tipo_cfag TINYINT, 
			@uhab INT, 
			@ralo INT, 
			@tipo CHAR(1), 
			@aloj_precio NUMERIC(15, 2), 
			@aloj_porc NUMERIC(4, 2), 
			@pen_precio NUMERIC(15, 2), 
			@pen_porc NUMERIC(4, 2), 
			@otr_precio NUMERIC(15, 2), 
			@otr_porc NUMERIC(4, 2), 
			@aloj NUMERIC(15, 2), 
			@pen NUMERIC(15, 2), 
			@otr NUMERIC(15, 2), 
			@factor_div TINYINT, 
			@cant_pax INT, 
			@factor_div_ralo TINYINT, 
			@precio_ralo NUMERIC(15, 2), 
			@id_cfag_ralo INT, 
			@ing_loc_OAgenII NUMERIC(15, 2), 
			@Impuestos NUMERIC(15, 2), 
			@incluido INT
			
	DECLARE @ing_Pro_Al NUMERIC(15, 2) = 0, 
	        @ing_Pro_Pen NUMERIC(15, 2) = 0, 
	        @ing_OAgen_Pro NUMERIC(15, 2) = 0 
			
	CREATE TABLE #Tab_PRAIH(id_room INT, 
							id_cont INT, 
							id_estado INT, 
							id_mone INT, 
							id_origen INT, 
							tasa NUMERIC(13, 8), 
							fecha_ent DATETIME, 
							fecha_sal DATETIME, 
							fecha_rooming DATETIME, 
							precio_rool MONEY, 
							precio NUMERIC(18, 2), 
							cantidad TINYINT, 
							nro_rooming INT, 
							id_habi INT, 
							id_ralo INT, 
							id_rool INT, 
							id_agen INT, 
							contrato CHAR(11), 
							tasa_fija NUMERIC(13, 8), 
							cod_mone CHAR(3))

	CREATE TABLE #Tab_PRAHH(id_room INT, 
							id_cont INT, 
							id_estado INT, 
							id_mone INT, 
							id_origen INT, 
							tasa NUMERIC(13, 8), 
							fecha_ent DATETIME, 
							fecha_sal DATETIME, 
							fecha_rooming DATETIME, 
							precio_rool MONEY, 
							precio NUMERIC(18, 2), 
							cantidad TINYINT, 
							nro_rooming INT, 
							id_habi INT, 
							id_ralo INT, 
							id_rool INT, 
							id_agen INT, 
							contrato CHAR(11), 
							tasa_fija NUMERIC(13, 8), 
							cod_mone CHAR(3))

	CREATE TABLE #Tab_PRAOH(id_room INT, 
							id_cont INT, 
							id_estado INT, 
							id_mone INT, 
							id_origen INT, 
							tasa NUMERIC(13, 8), 
							fecha_ent DATETIME, 
							fecha_sal DATETIME, 
							fecha_rooming DATETIME, 
							precio_rool MONEY, 
							precio NUMERIC(18, 2), 
							cantidad TINYINT, 
							nro_rooming INT, 
							id_habi INT, 
							id_ralo INT, 
							id_rool INT, 
							id_agen INT, 
							contrato CHAR(11), 
							tasa_fija NUMERIC(13, 8), 
							cod_mone CHAR(3), 
							Por_Imp NUMERIC(5, 2))

	CREATE TABLE #Tab_PRAX(id_room INT, 
						   id_cont INT, 
						   id_estado INT, 
						   id_mone INT, 
						   id_origen INT, 
						   tasa NUMERIC(13, 8), 
						   fecha_ent DATETIME, 
						   fecha_sal DATETIME, 
						   fecha_rooming DATETIME, 
						   precio_rool MONEY, 
						   precio NUMERIC(18, 2), 
						   cantidad TINYINT, 
						   nro_rooming INT, 
						   id_habi INT, 
						   id_ralo INT, 
						   id_rool INT, 
						   id_agen INT, 
						   contrato CHAR(11), 
						   tasa_fija NUMERIC(13, 8), 
						   cod_mone CHAR(3), 
						   Por_Imp NUMERIC(5, 2), 
						   incluido INT)

	CREATE TABLE #Tab_PRAXII(id_room INT, 
							 id_cont INT, 
							 id_estado INT, 
							 id_mone INT, 
							 id_origen INT, 
							 tasa NUMERIC(13, 8), 
							 fecha_ent DATETIME, 
							 fecha_sal DATETIME, 
							 fecha_rooming DATETIME, 
							 precio_rool MONEY, 
							 precio NUMERIC(18, 2), 
							 cantidad TINYINT, 
							 nro_rooming INT, 
							 id_habi INT, 
							 id_ralo INT, 
							 id_rool INT, 
							 id_agen INT, 
							 contrato CHAR(11), 
							 tasa_fija NUMERIC(13, 8), 
							 cod_mone CHAR(3), 
							 Por_Imp NUMERIC(5, 2), 
							 incluido INT)

	DECLARE @Tab_IngHAgenI TABLE(id_rool INT, 
								 id_uhab INT, 
								 id_ralo INT, 
								 precio NUMERIC(15, 2), 
								 tasa NUMERIC(13, 4), 
								 impuesto NUMERIC(5, 2), 
								 incluido INT, 
								 id_cont INT)

	DECLARE @Tab_IngHAgenII TABLE(id_rool INT, 
								  id_uhab INT, 
								  id_ralo INT, 
								  precio NUMERIC(15, 2), 
								  tasa NUMERIC(13, 4), 
								  impuesto NUMERIC(5, 2), 
								  incluido INT, 
								  id_cont INT)							 							 

     
	/* CREA TABLA DE POSIBLES DATOS TAB_PRAIH */
	
	/*

	INSERT INTO #Tab_PRAIH -- Carg. Régimen
	SELECT room.id_room, 
		   room.id_cont, 
		   id_estado, 
		   cont.id_mone, 
		   id_origen, 
		   tasa, 
		   fecha_ent, 
		   fecha_sal, 
		   fecha_rooming, 
		   precio_rool = rool.precio, 
		   rlau.precio, 
		   cantidad, 
		   nro_rooming, 
		   id_habi, 
		   rool.id_ralo, 
		   rool.id_rool, 
		   id_agen, 
		   contrato, 
		   tasa_fija, 
		   cod_mone
	FROM       Hoteroom room
	INNER JOIN Hoterool rool ON room.id_room = rool.id_room
	INNER JOIN Hotecont cont ON room.id_cont = cont.id_cont
	--INNER JOIN Hoterlau rlau ON rool.id_rool = rlau.id_rool
	INNER JOIN Hoterlag rlau ON rool.id_rool = rlau.id_rool
	INNER JOIN Siclamon sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
	 AND (rool.id_estado IN(3, 5) 
		OR (rool.id_estado = 2 
				AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
			)
		)
	 --AND rlau.id_con0 IN(
		--SELECT rali.id_con0
		--FROM   hoterali rali
		--WHERE  rool.id_ralo = rali.id_ralo)
	 AND rlau.id_cfag IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_ralo, 0) = rool.id_ralo)
	
	--UNION
	INSERT INTO #Tab_PRAIH
	SELECT room.id_room, 
		   room.id_cont, 
		   id_estado, 
		   cont.id_mone, 
		   id_origen, 
		   tasa, 
		   fecha_ent, 
		   fecha_sal, 
		   fecha_rooming, 
		   precio_rool = rool.precio, 
		   rlau.precio, 
		   cantidad, 
		   nro_rooming, 
		   id_habi, 
		   rool.id_ralo, 
		   rool.id_rool, 
		   id_agen, 
		   contrato, 
		   tasa_fija, 
		   cod_mone
	FROM       HOTEHROM room
	INNER JOIN HOTEHROL rool ON room.id_room = rool.id_room
	INNER JOIN Hotecont cont ON room.id_cont = cont.id_cont
	--INNER JOIN Hotehrlau rlau ON rool.id_rool = rlau.id_rool
	INNER JOIN Hotehrlag rlau ON rool.id_rool = rlau.id_rool
	INNER JOIN Siclamon sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
	 AND (rool.id_estado IN(3, 5) 
		OR (rool.id_estado = 2 
				AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
			)
		)
	 --AND rlau.id_con0 IN(
		--SELECT rali.id_con0
		--FROM   hoterali rali
		--WHERE  rool.id_ralo = rali.id_ralo)
	 AND rlau.id_cfag IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_ralo, 0) = rool.id_ralo)
	ORDER BY fecha_rooming
	
	*/

	/* CREA TABLA DE POSIBLES DATOS TAB_PRAHH */

	INSERT INTO #Tab_PRAHH -- Carg. Hospedaje
	SELECT room.id_room, 
		   room.id_cont, 
		   id_estado, 
		   cont.id_mone, 
		   id_origen, 
		   tasa, 
		   fecha_ent, 
		   fecha_sal, 
		   fecha_rooming, 
		   precio_rool = rool.precio, 
		   rlau.precio, 
		   cantidad, 
		   nro_rooming, 
		   id_habi, 
		   rool.id_ralo, 
		   rool.id_rool, 
		   id_agen, 
		   contrato, 
		   tasa_fija, 
		   cod_mone
	FROM       Hoteroom room
	INNER JOIN Hoterool rool ON room.id_room = rool.id_room
	INNER JOIN Hotecont cont ON room.id_cont = cont.id_cont
	--INNER JOIN Hoterlau rlau ON rool.id_rool = rlau.id_rool
	INNER JOIN Hoterlag rlau ON rool.id_rool = rlau.id_rool
	INNER JOIN Siclamon sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
	 AND (rool.id_estado IN(3, 5) 
		OR (rool.id_estado = 2 
				AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
			)
		)
	 --AND rlau.id_con0 IN(
		--SELECT thab.id_con0
		--FROM hotethab thab)
	 AND rlau.id_cfag IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_uhab, 0) <> 0)
	 
	--UNION
	INSERT INTO #Tab_PRAHH
	SELECT room.id_room, 
		   room.id_cont, 
		   id_estado, 
		   cont.id_mone, 
		   id_origen, 
		   tasa, 
		   fecha_ent, 
		   fecha_sal, 
		   fecha_rooming, 
		   precio_rool = rool.precio, 
		   rlau.precio, 
		   cantidad, 
		   nro_rooming, 
		   id_habi, 
		   rool.id_ralo, 
		   rool.id_rool, 
		   id_agen, 
		   contrato, 
		   tasa_fija, 
		   cod_mone
	FROM       HOTEHROM room
	INNER JOIN HOTEHROL rool ON room.id_room = rool.id_room
	INNER JOIN Hotecont cont ON room.id_cont = cont.id_cont
	--INNER JOIN Hotehrlau rlau ON rool.id_rool = rlau.id_rool
	INNER JOIN Hotehrlag rlau ON rool.id_rool = rlau.id_rool
	INNER JOIN Siclamon sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
	 AND (rool.id_estado IN(3, 5) 
		OR (rool.id_estado = 2 
				AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
			)
		)
	 --AND rlau.id_con0 IN(
		--SELECT thab.id_con0
		--FROM hotethab thab)
	 AND rlau.id_cfag IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_uhab, 0) <> 0)
	ORDER BY fecha_rooming


	/* SE AGREGARON LAS LINEAS 166..172-175  Y  183..189-192 */

	/******************************* CREA TABLA CARGOS OTROS DIRECTOS ************************************/

	INSERT INTO #Tab_PRAOH
	SELECT room.id_room, 
		   room.id_cont, 
		   id_estado, 
		   cont.id_mone, 
		   id_origen, 
		   tasa, 
		   fecha_ent = rlau.fecha_desde, 
		   fecha_sal = rlau.fecha_hasta, 
		   fecha_rooming, 
		   precio_rool = rool.precio, 
		   rlau.precio, 
		   rlau.cantidad, 
		   nro_rooming, 
		   id_habi, 
		   rool.id_ralo, 
		   rool.id_rool, 
		   id_agen, 
		   contrato, 
		   tasa_fija, 
		   cod_mone, 
		   D.porc_impu
	FROM       Hoteroom room
	INNER JOIN Hoterool rool ON room.id_room = rool.id_room
	INNER JOIN Hotecont cont ON room.id_cont = cont.id_cont
	INNER JOIN HOTERLAU rlau ON rool.id_rool = rlau.id_rool --Inner Join Siclamon  sicla on  cont.id_mone  = sicla.id_mone
	INNER JOIN HOTEPRE0 B ON B.id_con0 = rlau.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 room.id_oper = @id_oper)
	 AND (rool.id_estado IN(3, 5) 
		OR (rool.id_estado = 2 
				AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
			)
		)
	 AND rlau.id_con0 NOT IN(
		SELECT thab.id_con0
		FROM hotethab thab)
	 AND rlau.id_con0 NOT IN(
		SELECT rali.id_con0
		FROM hoterali rali)
		
	--UNION
	INSERT INTO #Tab_PRAOH
	SELECT room.id_room, 
		   room.id_cont, 
		   id_estado, 
		   cont.id_mone, 
		   id_origen, 
		   tasa, 
		   fecha_ent = rlau.fecha_desde, 
		   fecha_sal = rlau.fecha_hasta, 
		   fecha_rooming, 
		   precio_rool = rool.precio, 
		   rlau.precio, 
		   rlau.cantidad, 
		   nro_rooming, 
		   id_habi, 
		   rool.id_ralo, 
		   rool.id_rool, 
		   id_agen, 
		   contrato, 
		   tasa_fija, 
		   cod_mone, 
		   D.porc_impu
	FROM       HOTEHROM room
	INNER JOIN HOTEHROL rool ON room.id_room = rool.id_room
	INNER JOIN Hotecont cont ON room.id_cont = cont.id_cont
	INNER JOIN HOTEHRLAU rlau ON rool.id_rool = rlau.id_rool --Inner Join Siclamon  sicla on  cont.id_mone  = sicla.id_mone
	INNER JOIN HOTEPRE0 B ON B.id_con0 = rlau.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 room.id_oper = @id_oper)
	 AND (rool.id_estado IN(3, 5) 
		OR (rool.id_estado = 2 
				AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
			)
		)
	 AND rlau.id_con0 NOT IN(
		SELECT thab.id_con0
		FROM hotethab thab)
	 AND rlau.id_con0 NOT IN(
		SELECT rali.id_con0
		FROM hoterali rali)
	ORDER BY fecha_rooming

	/* SE AGREGARON LAS LINEAS 205..210-212  Y  221..226-228 */

	/******************************* CREA TABLA CARGOS OTROS AGENCIAS ************************************/

	INSERT INTO #Tab_PRAX
	SELECT room.id_room, 
		   room.id_cont, 
		   id_estado, 
		   cont.id_mone, 
		   id_origen, 
		   tasa, 
		   fecha_ent = rlag.fecha_desde, 
		   fecha_sal = rlag.fecha_hasta, 
		   fecha_rooming, 
		   precio_rool = rool.precio, 
		   rlag.precio, 
		   cantidad, 
		   nro_rooming, 
		   id_habi, 
		   rool.id_ralo, 
		   rool.id_rool, 
		   id_agen, 
		   contrato, 
		   tasa_fija, 
		   cod_mone, 
		   Por_Imp = COALESCE(aimp.porc_impu, 0), 
		   incluido
	FROM       Hoteroom room
	INNER JOIN Hoterool rool  ON room.id_room = rool.id_room
	INNER JOIN Hotecont cont  ON room.id_cont = cont.id_cont
	INNER JOIN HOTERLAG rlag  ON rool.id_rool = rlag.id_rool
	INNER JOIN HOTEIMAG imag  ON rlag.id_cfag = imag.id_cfag
	INNER JOIN HOTEIMCO imco  ON cont.id_cont = imco.id_cont
						     AND imag.id_impu = imco.id_impu
	INNER JOIN SICLAIMP aimp  ON imag.id_impu = aimp.id_impu
	INNER JOIN Siclamon sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
	 AND (rool.id_estado IN(3, 5) 
		OR (rool.id_estado = 2 
				AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
			)
		)
	 --AND rlag.id_cfag NOT IN(SELECT thab.id_con0 FROM hotethab thab)
	 --AND rlag.id_cfag NOT IN(SELECT rali.id_con0 FROM hoterali rali)
	 AND rlag.id_cfag NOT IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_uhab, 0) <> 0)
	 AND rlag.id_cfag NOT IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_ralo, 0) <> 0)
	 AND rlag.id_cfag NOT IN(
		SELECT cfag.id_cfag
		FROM HOTECFAG cfag
		WHERE cfag.id_ralo >= 1)
		
	--UNION
	INSERT INTO #Tab_PRAX
	SELECT room.id_room, 
		   room.id_cont, 
		   id_estado, 
		   cont.id_mone, 
		   id_origen, 
		   tasa, 
		   fecha_ent = rlag.fecha_desde, 
		   fecha_sal = rlag.fecha_hasta, 
		   fecha_rooming, 
		   precio_rool = rool.precio, 
		   rlag.precio, 
		   cantidad, 
		   nro_rooming, 
		   id_habi, 
		   rool.id_ralo, 
		   rool.id_rool, 
		   id_agen, 
		   contrato, 
		   tasa_fija, 
		   cod_mone, 
		   Por_Imp = COALESCE(aimp.porc_impu, 0), 
		   incluido
	FROM       HOTEHROM  room
	INNER JOIN HOTEHROL  rool  ON room.id_room = rool.id_room
	INNER JOIN Hotecont  cont  ON room.id_cont = cont.id_cont
	INNER JOIN HOTEHRLAG rlag  ON rool.id_rool = rlag.id_rool
	INNER JOIN HOTEIMAG  imag  ON rlag.id_cfag = imag.id_cfag
	INNER JOIN HOTEIMCO  imco  ON cont.id_cont = imco.id_cont
						      AND imag.id_impu = imco.id_impu
	INNER JOIN SICLAIMP  aimp  ON imag.id_impu = aimp.id_impu
	INNER JOIN Siclamon  sicla ON cont.id_mone = sicla.id_mone
	WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
	 AND (rool.id_estado IN(3, 5) 
		OR (rool.id_estado = 2 
				AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
			)
		)
	 --AND rlag.id_cfag NOT IN(SELECT thab.id_con0 FROM hotethab thab)
	 --AND rlag.id_cfag NOT IN(SELECT rali.id_con0 FROM hoterali rali)
	 AND rlag.id_cfag NOT IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_uhab, 0) <> 0)
	 AND rlag.id_cfag NOT IN (SELECT sa.id_cfag FROM HOTECFAG sa WHERE ISNULL(sa.id_ralo, 0) <> 0)
	 AND rlag.id_cfag NOT IN(
		SELECT cfag.id_cfag
		FROM HOTECFAG cfag
		WHERE cfag.id_ralo >= 1)
	ORDER BY fecha_rooming

	INSERT INTO #Tab_PRAXII
	SELECT *
	FROM #Tab_PRAX
	WHERE incluido = 1

	UPDATE #Tab_PRAX
	SET incluido = 1
	FROM #Tab_PRAX T1
		 INNER JOIN #Tab_PRAXII T2 ON T1.id_room = T2.id_room
	WHERE t1.incluido = 0

	UPDATE #Tab_PRAX
	SET Por_Imp = 0
	WHERE incluido = 0		

	/***************************************************************************************************************************************************/
    
    /*
	DELETE FROM #Tab_PRAIH
	WHERE id_room IN (SELECT ISNULL(id_room, 0) 
                      FROM   Hoteface
                      WHERE  CAST(fecha_ent AS DATE) <= @dia_reserv)
	
	DELETE FROM #Tab_PRAHH
	WHERE id_room IN (SELECT ISNULL(id_room, 0) 
                      FROM   Hoteface
                      WHERE  CAST(fecha_ent AS DATE) <= @dia_reserv)
	
	DELETE FROM #Tab_PRAOH
	WHERE id_room IN (SELECT ISNULL(id_room, 0) 
                      FROM   Hoteface
                      WHERE  CAST(fecha_ent AS DATE) <= @dia_reserv)
	
	DELETE FROM #Tab_PRAX
	WHERE id_room IN (SELECT ISNULL(id_room, 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(d, -1, DATEADD(m, 1, CAST(LEFT(YEAR(LEFT(YEAR(@dia_inic), 4) + RIGHT('0' + LEFT(MONTH(@dia_inic), 2), 2) + RIGHT('0' + LEFT(DAY(@dia_inic), 2), 2)), 4) + RIGHT('0' + LEFT(MONTH(LEFT(YEAR(@dia_inic), 4) + RIGHT('0' + LEFT(MONTH(@dia_inic), 2), 2) + RIGHT('0' + LEFT(DAY(@dia_inic), 2), 2)), 2), 2) + '01' AS SMALLDATETIME)))

		SET @ing_Ro_Pen = 0
		SET @ing_Ro_Al = 0
		SET @ing_OAgen = 0
		WHILE @dia_inic <= @ciclo
		BEGIN

			/*SELECT @ing_loc_Ro_Pen = SUM(precio * cantidad * CASE
																  WHEN id_mone != @mone THEN @tasa_ext / COALESCE(tasa_fija, tasa)
																  ELSE 1
															 END)
			FROM #Tab_PRAIH
			WHERE DATEDIFF(DAY, @dia_reserv, fecha_rooming) <= 0
			  AND DATEDIFF(day, fecha_ent, @dia_inic) >= 0
			  AND DATEDIFF(day, @dia_inic, fecha_sal) > 0*/

			SELECT @ing_loc_Ro_Pen = 0 --COALESCE(@ing_loc_Ro_Pen, 0)

			SELECT @ing_loc_Ro_Al = SUM(precio * cantidad * CASE
																 WHEN id_mone != @mone THEN @tasa_ext / COALESCE(tasa_fija, tasa)
																 ELSE 1
															END)
			FROM #Tab_PRAHH
			WHERE DATEDIFF(DAY, @dia_reserv, fecha_rooming) <= 0
			  AND DATEDIFF(day, fecha_ent, @dia_inic) >= 0
			  AND DATEDIFF(day, @dia_inic, fecha_sal) > 0

			SELECT @ing_loc_Ro_Al = COALESCE(@ing_loc_Ro_Al, 0)	
					                                                                                      
			/*********************************************  OTROS INGRESOS POR CARGOS AUTOMATICOS DIRECTOS ***********************************************************/

			SELECT @ing_loc_OAgenII = SUM(precio / (Impuesto / 100 + 1) * cantidad * CASE
																						  WHEN id_mone != @mone THEN @tasa_ext / COALESCE(tasa_fija, tasa)
																						  ELSE 1
																					 END)
			FROM(
				SELECT precio, 
					   cantidad, 
					   id_mone, 
					   tasa, 
					   tasa_fija, 
					   SUM(COALESCE(Por_Imp, 0))AS "Impuesto"
				FROM #Tab_PRAOH
				WHERE DATEDIFF(DAY, @dia_reserv, fecha_rooming) <= 0
				  AND DATEDIFF(day, fecha_ent, @dia_inic) >= 0
				  AND DATEDIFF(day, @dia_inic, fecha_sal) > 0
				GROUP BY id_room,
				         precio, 
						 cantidad, 
						 id_mone, 
						 tasa, 
						 tasa_fija)AS totalII


			SELECT @ing_loc_OAgenII = COALESCE(@ing_loc_OAgenII, 0)
						                                                                                      
			/*********************************************  OTROS INGRESOS POR CARGOS AUTOMATICOS AGENCIAS *******************************************************/

			SELECT @ing_loc_OAgen = SUM(precio / (Impuesto / 100 + 1) * cantidad * CASE
																						WHEN id_mone != @mone THEN @tasa_ext / COALESCE(tasa_fija, tasa)
																						ELSE 1
																				   END)
			FROM(
				SELECT precio, 
					   cantidad, 
					   id_mone, 
					   tasa, 
					   tasa_fija, 
					   SUM(COALESCE(Por_Imp, 0))AS "Impuesto"
				FROM #Tab_PRAX
				WHERE DATEDIFF(DAY, @dia_reserv, fecha_rooming) <= 0
				  AND DATEDIFF(day, fecha_ent, @dia_inic) >= 0
				  AND DATEDIFF(day, @dia_inic, fecha_sal) > 0
				GROUP BY id_room,
				         precio, 
						 cantidad, 
						 id_mone, 
						 tasa, 
						 tasa_fija) AS total


			SELECT @ing_loc_OAgen = COALESCE(@ing_loc_OAgen, 0) + COALESCE(@ing_loc_OAgenII, 0)				                                                                                      

			/******************************************************** TABLAS PARA UNIFICAR "INCLUIDO" **********************************************************************/

			INSERT INTO @Tab_IngHAgenI
			SELECT rool.id_rool, 
				   rool.id_uhab, 
				   rool.id_ralo, 
				   precio = CONVERT(NUMERIC(15, 2), precio), 
				   tasa = CASE WHEN cont.id_mone != @mone THEN @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
							   ELSE 1
							   END, 
				   porc_impu = COALESCE(porc_impu, 0), 
				   --incluido = COALESCE(incluido, 2), 
				   1 incluido, -- Esto debido a que en HOTEROOL los precios siempre incluyen el impuesto.  
				   room.id_cont
			FROM       Hoterool rool
			INNER JOIN Hoteroom room ON rool.id_room = room.id_room
			INNER JOIN Hotecont cont ON room.id_cont = cont.id_cont
			INNER JOIN HOTECFAG cfag ON rool.id_uhab = cfag.id_uhab AND cfag.eliminado = 0
			INNER JOIN HOTEIMAG imag ON cfag.id_cfag = imag.id_cfag
			INNER JOIN HOTEIMCO imco ON cont.id_cont = imco.id_cont AND imag.id_impu = imco.id_impu
			INNER JOIN SICLAIMP aimp ON imag.id_impu = aimp.id_impu
			INNER JOIN Siclamon sicla ON cont.id_mone = sicla.id_mone
			WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
			  AND DATEDIFF(day, @dia_reserv, fecha_rooming) <= 0
			  AND (DATEDIFF(day, rool.fecha_ent, @dia_inic) >= 0
			   AND DATEDIFF(day, @dia_inic, rool.fecha_sal) > 0
				OR DATEDIFF(day, rool.fecha_ent, @dia_fin) >= 0
			   AND DATEDIFF(day, @dia_fin, rool.fecha_sal) > 0)
			  AND (rool.id_estado IN(3, 5) 
				OR (rool.id_estado = 2 
						AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
					)
				)
			
			--UNION
			INSERT INTO @Tab_IngHAgenI
			SELECT rool.id_rool, 
				   rool.id_uhab, 
				   rool.id_ralo, 
				   precio = CONVERT(NUMERIC(15, 2), precio), 
				   tasa = CASE WHEN cont.id_mone != @mone THEN @tasa_ext / COALESCE(cont.tasa_fija, sicla.tasa)
							   ELSE 1
							   END, 
				   porc_impu = COALESCE(porc_impu, 0), 
				   --incluido = COALESCE(incluido, 2), 
				   1 incluido, -- Esto debido a que en HOTEROOL los precios siempre incluyen el impuesto.  
				   room.id_cont
			FROM       HOTEHROL rool
			INNER JOIN HOTEHROM room ON rool.id_room = room.id_room
			INNER JOIN Hotecont cont ON room.id_cont = cont.id_cont
			INNER JOIN HOTECFAG cfag ON rool.id_uhab = cfag.id_uhab AND cfag.eliminado = 0
			INNER JOIN HOTEIMAG imag ON cfag.id_cfag = imag.id_cfag
			INNER JOIN HOTEIMCO imco ON cont.id_cont = imco.id_cont AND imag.id_impu = imco.id_impu
			INNER JOIN SICLAIMP aimp ON imag.id_impu = aimp.id_impu
			INNER JOIN Siclamon sicla ON cont.id_mone = sicla.id_mone
			WHERE (@id_oper = 0 OR room.id_oper = @id_oper)
			  AND DATEDIFF(DAY, @dia_reserv, fecha_rooming) <= 0
			  AND DATEDIFF(day, fecha_ent, @dia_inic) >= 0
			  AND DATEDIFF(day, @dia_inic, fecha_sal) > 0
			  AND (rool.id_estado IN(3, 5) 
				OR (rool.id_estado = 2 
						AND room.id_room NOT IN (SELECT ISNULL(id_room, 0) FROM HOTEFACE)
					)
				)

			INSERT INTO @Tab_IngHAgenII
			SELECT *
			FROM   @Tab_IngHAgenI
			WHERE  incluido = 1

			UPDATE @Tab_IngHAgenI
			SET    incluido = 1
			FROM       @Tab_IngHAgenI  T1
			INNER JOIN @Tab_IngHAgenII T2 ON T1.id_rool = T2.id_rool
			WHERE  t1.incluido = 0

			UPDATE @Tab_IngHAgenI
			SET    impuesto = 0
			WHERE  incluido = 0


			/************************************************** FIN TABLAS PARA UNIFICAR "INCLUIDO" **********************************************************************/

			/******************************************************** CURSOR DE INGRESOS **********************************************************************/

			DECLARE c_pron_rool SCROLL CURSOR
				FOR SELECT *
					FROM(
						SELECT id_rool, 
							   id_uhab, 
							   id_ralo, 
							   precio, 
							   tasa, 
							   SUM(COALESCE(impuesto, 0))AS "Impuestos", 
							   incluido, 
							   id_cont
						FROM @Tab_IngHAgenI
						GROUP BY id_rool, 
								 id_uhab, 
								 id_ralo, 
								 precio, 
								 tasa, 
								 incluido, 
								 id_cont)AS Total
				FOR READ ONLY

			OPEN c_pron_rool
			FETCH FIRST FROM c_pron_rool INTO @id_rool, @id_uhab, @id_ralo, @precio, @tasa, @Impuestos, @incluido, @id_cont

			/************************************************************** INICIO CURSOR *******************************************************************************/

			WHILE @@fetch_status = 0
			BEGIN
				SELECT @Impuestos = CASE WHEN @incluido = 2 THEN 1
										 ELSE @Impuestos / 100 + 1
										 END

				/* Busca si esta incluido en el contrato */

				SELECT @id_cfag = NULL

				/*  Busco el id_cfag  */

				SELECT @id_cfag = id_cfag, 
					   @tipo_cfag = tipo_cfag, 
					   @uhab = cfag.id_uhab, 
					   @ralo = cfag.id_ralo
				FROM Hotecfag cfag
				WHERE id_uhab IS NOT NULL
				  AND id_ralo IS NOT NULL
				  AND id_uhab = @id_uhab
				  AND id_ralo = @id_ralo
				  AND eliminado = 0

				/************************************************************** INICIO FACTOR DIV *******************************************************************************/

				SELECT @factor_div = factor_div
				FROM Hoteccon
				WHERE id_cfag = @id_cfag
				  AND id_cont = @id_cont
				  
				IF @id_cfag IS NULL
				BEGIN
					SELECT @id_cfag = MAX(hotecfag.id_cfag), 
						   @tipo_cfag = MIN(tipo_cfag), 
						   @uhab = @id_uhab, 
						   @cant_pax = MAX(max_adultos)
					FROM       Hotecfag
					INNER JOIN hoteccon ON hotecfag.id_cfag = hoteccon.id_cfag
					INNER JOIN hoteuhab ON hoteuhab.id_uhab = hotecfag.id_uhab
					WHERE hotecfag.id_uhab = @id_uhab
					  AND hoteccon.id_cont = @id_cont
					  AND hotecfag.eliminado = 0

					SELECT @factor_div = CASE WHEN factor_div <= 0 THEN 1
											  ELSE factor_div
											  END
					FROM Hoteccon
					WHERE id_cfag = @id_cfag
					  AND id_cont = @id_cont

					SELECT @id_cfag_ralo = MAX(hotecfag.id_cfag), 
						   @ralo = @id_ralo, 
						   @precio_ralo = CONVERT(NUMERIC(15, 2), MAX(precio))
					FROM       Hotecfag
					INNER JOIN hoteccon ON hotecfag.id_cfag = hoteccon.id_cfag
					INNER JOIN hotecpre ON hoteccon.id_ccon = hotecpre.id_ccon
					INNER JOIN hoteperi ON hoteperi.id_peri = hotecpre.id_peri
					WHERE id_ralo = @id_ralo
					  AND hoteccon.id_cont = @id_cont
					  AND hotecfag.eliminado = 0
					  AND DATEDIFF(day, hoteperi.fecha_desde, @dia_inic) >= 0
					  AND DATEDIFF(day, @dia_inic, hoteperi.fecha_hasta) > 0

					SELECT @factor_div_ralo = factor_div
					FROM Hoteccon
					WHERE id_cfag = @id_cfag_ralo
					  AND id_cont = @id_cont
					SELECT @precio_ralo = COALESCE(@precio_ralo * @cant_pax / @factor_div_ralo * @tasa, 0)
				END

				/************************************************************** FIN FACTOR DIV *******************************************************************************/

				/********************************************************** INICIO BUSQUEDA PRECIO ********************************************************************************/

				SELECT @precio = @precio * @cant_pax / @factor_div * @tasa  

				/* Convierto el precio a moneda final */

				IF @tipo_cfag IN(1, 2, 3)    

				/* No compuesto  */

				BEGIN
					IF @uhab IS NOT NULL AND @ralo IS NOT NULL
					BEGIN
						SELECT @ing_loc_Ro_Al = COALESCE(@ing_loc_Ro_Al, 0) + COALESCE(@precio, 0) / @Impuestos
						--- Coalesce(@precio_ralo,0) /* Ingreso de alojamiento */
						SELECT @ing_loc_Ro_Pen = COALESCE(@ing_loc_Ro_Pen, 0) + COALESCE(@precio_ralo, 0)    

					/* Ingreso de pension */

					END
					ELSE
					BEGIN
						IF @uhab IS NOT NULL AND @ralo IS NULL
						BEGIN
							SELECT @ing_loc_Ro_Al = COALESCE(@ing_loc_Ro_Al, 0) + COALESCE(@precio, 0) / @Impuestos     

						/* Ingreso de Alojamiento */

						END
						ELSE
						BEGIN
							SELECT @ing_loc_OAgen = COALESCE(@ing_loc_OAgen, 0) + COALESCE(@precio, 0)
						END
					END   

				/* Ingreso por otros   */

				END
				ELSE
				BEGIN
				
					/* tipo del concepto compuesto  */
				
					SELECT @tipo = csim.tipo_csim   
					FROM       Hoteccom ccom
					INNER JOIN Hotecsim csim ON ccom.id_csim = csim.id_csim
					WHERE ccom.id_cfag = @id_cfag EXEC Calc_Ing_Desg @id_cfag, @aloj_precio OUTPUT, @aloj_porc OUTPUT, @pen_precio OUTPUT, @pen_porc OUTPUT, @otr_precio OUTPUT, @otr_porc OUTPUT

					SELECT @aloj = @aloj_precio * @factor_div + (@precio * @aloj_porc / 100), 
						   @pen = @pen_precio * @factor_div + (@precio * @pen_porc / 100), 
						   @otr = @otr_precio * @factor_div + (@precio * @otr_porc / 100)

					IF @precio > 0 AND @aloj <= @precio
					BEGIN
						SELECT @ing_loc_Ro_Al = @ing_loc_Ro_Al + @aloj, 
							   @precio = @precio - @aloj
					END
					ELSE
					BEGIN
						SELECT @ing_loc_Ro_Al = @ing_loc_Ro_Al + @precio, 
							   @precio = 0
					END

					IF @precio > 0 AND @pen <= @precio
					BEGIN
						SELECT @ing_loc_Ro_Pen = @ing_loc_Ro_Pen + @pen, 
							   @precio = @precio - @pen
					END
					ELSE
					BEGIN
						SELECT @ing_loc_Ro_Pen = @ing_loc_Ro_Pen + @precio, 
							   @precio = 0
					END

					IF @precio > 0 AND @otr <= @precio
					BEGIN
						SELECT @ing_loc_OAgen = @ing_loc_OAgen + @otr, 
							   @precio = @precio - @otr
					END
					ELSE
					BEGIN
						SELECT @ing_loc_OAgen = @ing_loc_OAgen + @precio, 
							   @precio = 0
					END

					IF @precio > 0   

						/* Asigno el resto del desglose del concepto */

					BEGIN
						IF @tipo = 1
						BEGIN
							SELECT @ing_loc_Ro_Al = @ing_loc_Ro_Al + @precio
						END
						ELSE
						BEGIN
							IF @tipo = 2
							BEGIN
								SELECT @ing_loc_Ro_Pen = @ing_loc_Ro_Pen + @precio
							END
							ELSE
							BEGIN
								IF @tipo = 3
								BEGIN
									SELECT @ing_loc_OAgen = @ing_loc_OAgen + @precio
								END
							END
						END
					END
				END  

				/******************************************************* FIN BUSQUEDAD PRECIO *******************************************************************************/

				FETCH NEXT FROM c_pron_rool INTO @id_rool, @id_uhab, @id_ralo, @precio, @tasa, @Impuestos, @incluido, @id_cont
				DELETE @Tab_IngHAgenI
				DELETE @Tab_IngHAgenII
			END

			DEALLOCATE c_pron_rool
	
			/****************************************************** FIN CALCULO POR CURSOR **********************************************************************************/
			
			/* Vamos a incluir acá el pronóstico de factura de agencia, llamando al proceso nativo de Front */
			
			/*******************************************************************************************
			 *******************************************************************************************
			 ***                     Pronóstico de HOTEFACA - Todo Ingreso                            **
			 *******************************************************************************************
			 *******************************************************************************************/
			
			SET @ing_Pro_Al = 0
			SET @ing_Pro_Pen = 0
			SET @ing_OAgen_Pro = 0 
			  
			EXEC dbo.DSS_Calc_Ing_Proforma @dia_reserv, @dia_inic, @dia_inic, @mone, @tasa_ext, @id_oper
				, @ing_Pro_Al OUTPUT, @ing_Pro_Pen OUTPUT, @ing_OAgen_Pro OUTPUT 
			
			UPDATE DEBGDAIPG
			SET ing_Ro_Al = /*COALESCE(@ing_Ro_Al, 0) +*/ COALESCE(@ing_loc_Ro_Al, 0) + COALESCE(@ing_Pro_Al, 0), 
				ing_Ro_Pen = /*COALESCE(@ing_Ro_Pen, 0) +*/ COALESCE(@ing_loc_Ro_Pen, 0) + COALESCE(@ing_Pro_Pen, 0), 
				ing_ROAgen = /*COALESCE(@ing_OAgen, 0) +*/ COALESCE(@ing_loc_OAgen, 0) + COALESCE(@ing_OAgen_Pro, 0)
			WHERE fecha = CAST(@dia_inic AS DATE)
			
			
			SET @dia_inic = @dia_inic + 1

			SET @ing_Ro_Pen = COALESCE(@ing_Ro_Pen, 0) + COALESCE(@ing_loc_Ro_Pen, 0) + COALESCE(@ing_Pro_Pen, 0)
			SET @ing_loc_Ro_Pen = 0
			SET @ing_Ro_Al = COALESCE(@ing_Ro_Al, 0) + COALESCE(@ing_loc_Ro_Al, 0) + COALESCE(@ing_Pro_Al, 0)
			SET @ing_loc_Ro_Al = 0
			SET @ing_OAgen = COALESCE(@ing_OAgen, 0) + COALESCE(@ing_loc_OAgen, 0) + COALESCE(@ing_OAgen_Pro, 0)
			SET @ing_loc_OAgen = 0

		END   	
	
		/* CREA TABLA CON DATOS DE INGRESO DE HOSPEDAJE POR MES */

		UPDATE HOTEDAIPG
		SET ing_Ro_Al = @ing_Ro_Al, 
			ing_Ro_Pen = @ing_Ro_Pen, 
			ing_ROAgen = @ing_OAgen
		WHERE mes = MONTH(@ciclo)
		  AND fyear = YEAR(@ciclo)

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

	END

END



