
/****** Object:  StoredProcedure [dbo].[Pronostico_Reservas]    Script Date: 10/29/2012 14:29:32 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

DROP PROCEDURE dbo.Pronostico_Reservas
GO

CREATE PROCEDURE dbo.Pronostico_Reservas
	   @Dia_Inicial DATETIME, 
	   @Dia_Salida  DATETIME, 
	   @Opcion      TINYINT, 
	   @TipoMoneda  CHAR(3), 
	   @id_oper     INT,
	   @TTotal      NUMERIC(12, 2) OUTPUT
AS

/*  Procedimiento que genera un cursor con
    el pron¢stico de ingresos para cada dia del  
    periodo pasado como parametro  */

BEGIN
	SET NOCOUNT ON
	
	DECLARE @Tasa_ext NUMERIC(13, 8) = 1, 
			@RTotal   NUMERIC(12, 2), 
			@Moneda   INT

	SELECT @Moneda = id_mone
	FROM   SICLAMON
	WHERE  cod_mone = @TipoMoneda

	DECLARE @ing_reserva      NUMERIC(15, 2), 
			@ing_reserva_Reg  NUMERIC(15, 2), 
			@ing_reserva_Otr  NUMERIC(15, 2), 
			@ing_Ro_Al        NUMERIC(15, 2), 
			@ing_Ro_Pen       NUMERIC(15, 2), 
			@ing_ROAgen       NUMERIC(15, 2), 
			@Ping_reserva     NUMERIC(15, 2), 
			@Ping_reserva_Reg NUMERIC(15, 2), 
			@Ping_reserva_Otr NUMERIC(15, 2), 
			@Ping_Ro_Al       NUMERIC(15, 2), 
			@Ping_Ro_Pen      NUMERIC(15, 2), 
			@Ping_ROAgen      NUMERIC(15, 2), 
			@ndw              INT, 
			@cdw              CHAR(2), 
			@fechaH           DATETIME,
			@ing_Pro_Al       NUMERIC(15, 2),
		    @ing_Pro_Pen      NUMERIC(15, 2),
		    @ing_OAgen_Pro    NUMERIC(15, 2),
		    @ing_ocup         NUMERIC(15, 2),
		    @result           NUMERIC(15, 2)

	CREATE TABLE #t_booking
	(
		cdia      CHAR(2), 
		Dia       DATETIME, 
		Reserv    NUMERIC(15, 2), 
		Ro_Al     NUMERIC(15, 2), 
		Re_Pen    NUMERIC(15, 2), 
		Ro_Pen    NUMERIC(15, 2), 
		Otr_Re    NUMERIC(15, 2), 
		Otr_Ro_Ag NUMERIC(15, 2)
	)

	SELECT @fechaH = fecha_hotelera FROM Hotetabl
	
	SET @Ping_reserva = 0
	SET @Ping_reserva_Reg = 0
	SET @Ping_reserva_Otr = 0
	SET @Ping_Ro_Al = 0
	SET @Ping_Ro_Pen = 0
	SET @Ping_ROAgen = 0
  
	WHILE DATEDIFF(day, @Dia_Inicial, @Dia_Salida) >= 0
	BEGIN 
     
		/* Ingresos de Extras por Reservas  */

		/* ================================ */

		EXECUTE DSS_Calc_Ing_Reserva @Dia_Inicial, @Dia_Inicial, @Moneda, @tasa_ext, @id_oper, @ing_reserva OUTPUT
		SET @ing_reserva = @ing_reserva + @Ping_reserva
		
		/* Ingresos de Extras por Huéspedes  */

		/* ================================ */
		
		EXECUTE DSS_Calc_Ing_Ocupacion @Dia_Inicial, @Dia_Inicial, @Moneda, @tasa_ext, @id_oper, @ing_ocup OUTPUT
		SET @ing_reserva = @ing_reserva + ISNULL(@ing_ocup, 0)
 
		/* Ingresos de Extras por Reservas Regimen de Pension */

		/* ================================================== */

		EXECUTE DSS_Calc_Ing_Reserva_Reg @Dia_Inicial, @Dia_Inicial, @Moneda, @tasa_ext, @id_oper, @ing_reserva_Reg OUTPUT
		SET @ing_reserva_Reg = @ing_reserva_Reg + @Ping_reserva_Reg

		/* Ingresos de Extras por Reservas Otros cargos Autos */

		/* ================================================== */

		EXECUTE DSS_Calc_Ing_Reserva_Otr @Dia_Inicial, @Dia_Inicial, @Moneda, @tasa_ext, @id_oper, @ing_reserva_Otr OUTPUT
		SET @ing_reserva_Otr = @ing_reserva_Otr + @Ping_reserva_Otr
		
		/* Ingresos de Extras por Huéspedes Otros cargos Autos */

		/* ================================================== */
		
		EXECUTE DSS_Calc_Ing_OExtra @Dia_Inicial, @Dia_Inicial, @Moneda, @tasa_ext, @id_oper, @result OUTPUT
		SET @ing_reserva_Otr = @ing_reserva_Otr + ISNULL(@result, 0)
   
		/* Ingresos de Agencias  por Rooming List de Alojamiento , pensi¢n y otros  */

		/* ======================================================================== */

		EXECUTE DSS_Calc_Ing_Rooming @Dia_Inicial, @Dia_Inicial, @Moneda, @tasa_ext, @id_oper, @ing_Ro_Al OUTPUT, @ing_Ro_Pen OUTPUT, @ing_ROAgen OUTPUT
		SET @ing_Ro_Al = @ing_Ro_Al + @Ping_Ro_Al
		SET @ing_Ro_Pen = @ing_Ro_Pen + @Ping_Ro_Pen
		SET @ing_ROAgen = @ing_ROAgen + @Ping_ROAgen 
		
		/* Ingresos de Agencias por Facturas de Agencias  */

		/* ======================================================================== */
		  
		EXEC dbo.DSS_Calc_Ing_Proforma @Dia_Inicial, @Dia_Inicial, @Dia_Inicial, @Moneda, @tasa_ext, @id_oper
			, @ing_Pro_Al OUTPUT, @ing_Pro_Pen OUTPUT, @ing_OAgen_Pro OUTPUT 
			
		SET @ing_Ro_Al = @ing_Ro_Al + ISNULL(@ing_Pro_Al, 0)
		SET @ing_Ro_Pen = @ing_Ro_Pen + ISNULL(@ing_Pro_Pen, 0)
		SET @ing_ROAgen = @ing_ROAgen + ISNULL(@ing_OAgen_Pro, 0)

		continuar:
		SELECT @ndw = DATEPART(dw, @Dia_Inicial)
		SELECT @cdw = CASE WHEN @ndw = 1 THEN 'Do'
						   WHEN @ndw = 2 THEN 'Lu'
						   WHEN @ndw = 3 THEN 'Ma'
						   WHEN @ndw = 4 THEN 'Mi'
						   WHEN @ndw = 5 THEN 'Ju'
						   WHEN @ndw = 6 THEN 'Vi'
						   ELSE 'Sa'
					       END

		INSERT INTO #t_booking VALUES
		(
			@cdw, 
			@Dia_Inicial, 
			@ing_reserva, 
			COALESCE(@ing_Ro_Al, 0.00), 
			@ing_Reserva_Reg, 
			@ing_Ro_Pen, 
			@Ing_Reserva_Otr, 
			@ing_RoAgen
		)

		SELECT @Dia_Inicial = DATEADD(day, 1, @Dia_Inicial)
		
	END

	IF @Opcion = 97 -- Extras
	BEGIN
		SELECT @TTotal = Otr_Re + Otr_Ro_Ag
		FROM #t_booking
	END

	IF @Opcion = 98 -- Hospedaje
	BEGIN
		SELECT @TTotal = Reserv + Ro_Al + Re_Pen + Ro_Pen
		FROM #t_booking
	END

	IF @Opcion = 99 -- Total General
	BEGIN
		SELECT @TTotal = Reserv + Ro_Al + Re_Pen + Ro_Pen + Otr_Re + Otr_Ro_Ag
		FROM #t_booking
	END

	IF @Opcion = 100 -- Tabla
	BEGIN
		SELECT * FROM #t_booking
	END

	IF @Opcion = 101 -- Tabla
	BEGIN
		SELECT *, 
			   Reserv + Ro_Al + Re_Pen + Ro_Pen AS Hospedaje
		FROM #t_booking
	END

	RETURN @TTotal
	
END

