SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

IF EXISTS (SELECT * FROM sys.objects 
   WHERE object_id = OBJECT_ID(N'Calculo_Ingreso_Futuro') )
DROP Procedure Calculo_Ingreso_Futuro
GO

Create PROCEDURE Calculo_Ingreso_Futuro
                @Dia_Inicial Datetime,
                @Dia_Salida  Datetime,
                @Opcion tinyint,
                @TipoMoneda char(3), 
                @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
  --@Dia_Salida Date,
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_ocup         numeric(15,2),
          @ing_RegExtra     numeric(15,2),@ing_OExtra       numeric(15,2), 
	      @ing_Ro_Al        numeric(15,2),@ing_Pro_Al       numeric(15,2),
          @ing_Ro_Pen       numeric(15,2),@ing_Pro_Pen      numeric(15,2),
          @ing_OAgen        numeric(15,2),@ing_ROAgen       numeric(15,2),
          @ndw              int,@cdw              char(2),
          @fechaH           datetime

   Create Table #t_booking  
               (cdia char(2),Dia Datetime,Reserv numeric(15,2),
			    Ocup numeric(15,2),Reg_Extra numeric(15,2),
			    Otr_Extra numeric(15,2), Ro_Al numeric(15,2),
			    Pro_Al numeric(15,2),Re_Pen numeric(15,2),
			    Ro_Pen numeric(15,2),Pro_Pen numeric(15,2),
			    Otr_Re numeric(15,2),Otr_Ro_Ag    numeric(15,2),
			    Otr_Pro_Ag   numeric(15,2)  )

  Select @fechaH = fecha_hotelera From Hotetabl
  
  --Set @Dia_Salida = @Dia_Inicial
  While DATEDIFF(day, @Dia_Inicial, @Dia_Salida)  >=  0 
    Begin 
        if @Dia_Inicial  < @fechaH
           begin
               Select @ing_reserva = 0, @ing_ocup = 0, 
                      @ing_OExtra = 0, @ing_Ro_Al = 0, 
                      @ing_Ro_Pen = 0, @ing_ROAgen  = 0, @ing_Pro_Al = 0,
                      @ing_Pro_Pen = 0, @ing_OAgen = 0
               Goto continuar
          end  
      
         /* Ingresos de Extras por Reservas  */
         /* ================================ */
         Execute Calc_Ing_Reserva 
                 @Dia_Inicial, @Dia_Inicial, 
                 @Moneda, @tasa_ext, 
                 @ing_reserva output
     
         /* Ingresos de Extras por Reservas Regimen de Pension */
         /* ================================================== */
         Execute Calc_Ing_Reserva_Reg  
                 @Dia_Inicial, @Dia_Inicial, 
                 @Moneda, @tasa_ext, 
                 @ing_reserva_Reg output

         /* Ingresos de Extras por Reservas Otros cargos Autos */
         /* ================================================== */
         Execute Calc_Ing_Reserva_Otr    
                 @Dia_Inicial, @Dia_Inicial, 
                 @Moneda, @tasa_ext, 
                 @ing_reserva_Otr output

         /* Ingresos de Extras por Ocupaci¢n */
         /* ================================ */
         Execute Calc_Ing_Ocupacion 
                 @Dia_Inicial, @Dia_Inicial, 
                 @Moneda, @tasa_ext, 
                 @ing_ocup output

           /* Ingresos de Extras por Otros (cargos automaticos que son de pension)  */
           /* ===================================================================== */
         Execute Calc_Ing_RegExtra 
                 @Dia_Inicial, @Dia_Inicial, 
                 @Moneda, @tasa_ext, 
                 @ing_RegExtra output
     
           /* Ingresos de Extras por Otros (cargos automaticos que no son de alojamiento ni pension)  */
           /* ======================================================================================= */
         Execute Calc_Ing_OExtra  
                 @Dia_Inicial, @Dia_Inicial, 
                 @Moneda, @tasa_ext, 
                 @ing_OExtra output

          /* Ingresos de Agencias  por Rooming List de Alojamiento , pensi¢n y otros  */  
          /* ======================================================================== */   
         Execute Calc_Ing_Rooming   
                 @Dia_Inicial, @Dia_Inicial, 
                 @Moneda, @tasa_ext, 
                 @ing_Ro_Al output, @ing_Ro_Pen output, 
                 @ing_ROAgen  output 
      
           /* Ingresos de Agencias  por facturas (Proforma)  de Alojamiento,  pensi¢n y otros */ 
           /* =============================================================================== */    
         Execute Calc_Ing_Proforma  
                 @Dia_Inicial, @Dia_Inicial, @Moneda, 
                 @tasa_ext, 
                 @ing_Pro_Al output, 
                 @ing_Pro_Pen output, 
                 @ing_OAgen output

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 #t_booking 
             Values(@cdw, @Dia_Inicial, @ing_reserva, 
                    @ing_ocup, @ing_RegExtra, @ing_OExtra, 
                    Coalesce(@ing_Ro_Al,0.00), 
                    Coalesce(@ing_Pro_Al,0.00), 
                    @ing_Reserva_Reg, @ing_Ro_Pen, 
                    @ing_Pro_Pen, @Ing_Reserva_Otr,
                    @ing_RoAgen, @ing_oAgen)

        Select @Dia_Inicial = DATEADD(day,1, @Dia_Inicial) 
    end
    
    If @Opcion = 97 -- Total General Solo Extras
    Begin
		Select @TTotal = (Otr_Extra+Otr_Re+Otr_Pro_Ag+Otr_Ro_Ag) 
				From #t_booking    
    End  
    
    If @Opcion = 98 -- Total General Sin Extras
    Begin
		Select @TTotal = (Reserv+Ocup+Reg_Extra+Ro_Al+
			   Pro_Al+Re_Pen+Ro_Pen+Pro_Pen) 
				From #t_booking    
    End  
    
    If @Opcion = 99 -- Total General
    Begin
		Select @TTotal = (Reserv+Ocup+Reg_Extra+Otr_Extra+Ro_Al+
			   Pro_Al+Re_Pen+Ro_Pen+Pro_Pen+
				Otr_Re+Otr_Pro_Ag+Otr_Ro_Ag) 
				From #t_booking    
    End        
            
Return @TTotal
end
