
/****** Object:  StoredProcedure [dbo].[Pronostico_General_Futuro]    Script Date: 01/21/2015 16:07:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

DROP PROCEDURE [dbo].[DSS_Pronostico_General_Futuro]
GO

CREATE PROCEDURE [dbo].[DSS_Pronostico_General_Futuro]
                  @Dia_Inicial     datetime, 
			--	  @Dia_Final     datetime, 
				  @Opcion tinyint,
				  @Resultado int Output
      --		  @respetar_cupos  int,
				  --@row             int,
   			--	  @release         int

/*   parametro @row si es = 1 se devuelven todas las columnas 
     calculadas sino se devuelven las columnas de habit  libres,
     ocupadas,salidas,reservadas y fuera_orden en ese orden */

AS

Begin
    SET NOCOUNT ON
  
   declare  @libres int,@ocup int,@fechaH datetime,@fecha_i datetime,
            @salen int,@reserv int,@ford int,@entran int,@ocup_noche int,
            @clientes int, @room int,@total_habit int,@cdw char(2),@ndw int,
            @disp int,@tot_reserv int, @porc numeric(6,2),
            @prom_libres int, @prom_ocup int, @prom_salen int,@prom_Reserv int, @prom_Ford int, 
            @prom_Entran int,  @prom_Ocup_Noche numeric(8,2),
            @prom_Client_noche int, @cant_dias int

 Declare @respetar_cupos  int,@row int,@release int, @Dia_Final     datetime
 Select @respetar_cupos=0,@row=1,@release=1
 Set @Dia_Final = @Dia_Inicial

  if @row = 1
      CREATE TABLE #booking (
                   Dw char(2),dia datetime,Libres int,
                   Ocup int,Salen int,Reserv int,
	               Ford int,Entran int,Ocup_Noche     int,
	               Client_Noche int ,Tot_habit      int  ,
                   Porc numeric(8,2)    ) 
  else
    CREATE TABLE #libres  
                  (Libres int,Ocup   int,
		           Salen  int,Reserv int,
		           Ford   int,) 

  Select @fechaH = fecha_hotelera From Hotetabl
  SET @fecha_i = @Dia_Inicial
  Select @disp = 0
  
  Execute Calc_Habitaciones_Hotel @total_habit output
  While DATEDIFF(day,@Dia_Inicial,@Dia_Final) >= 0 
   begin 
      if @Dia_Inicial  < @fechaH
        begin
             Select @ocup = 0, @salen = 0, @reserv = 0, @room = 0, 
                    @ford = 0, @entran = 0, @clientes = 0, 
                    @disp = 0
             Goto continuar
        end
      Execute Calc_Habit_Ocup_Hotel      @Dia_Inicial, @ocup   output
      Execute Calc_Habit_Salen_Dia_Hotel @Dia_Inicial, @salen  output
      Execute Calc_Habit_Res_Hotel_Dia   @Dia_Inicial, @reserv output
      Execute Calc_Habit_Room_Hotel_Dia  @Dia_Inicial, @room   output   
      Execute Calc_Habit_Ford_Hotel      @Dia_Inicial, @ford   output

      if @row = 1 
          begin
              Execute Calc_Habit_Entran_Dia_Hotel @Dia_Inicial, @entran   output    
              Execute Calc_Clientes_Noche_Hotel   @Dia_Inicial, @clientes output
          end  
      if @respetar_cupos = 1
         Execute Calc_Cupos_Disp_Dia  @Dia_Inicial, @release, @disp output
      else
         Select @disp = 0

 continuar:
       Select @tot_reserv  = @reserv + @room + @disp
       Select @libres      = @total_habit + @salen - @ocup - @tot_reserv - @ford 

        /* tambien se puede calcular como el total - libres - ford */
       Select @ocup_noche = @ocup - @salen + @tot_reserv
    
       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

       /* Calculo porciento de Ocupacion  para la noche */
       if @total_habit >  0
            Select @porc   = Convert(numeric(6,2),@ocup_noche) / Convert(numeric(6,2),@total_habit)  * 100
       else
           Select @porc   = 0
  
       if @row = 1 
           Insert #booking Values(@cdw,@Dia_Inicial,@libres,@ocup,
                                  @salen,@tot_reserv,@ford,@entran,
                                  @ocup_noche,@clientes, @total_habit,@porc)
       else
           Insert #libres Values(@libres,@ocup,@salen,
                                 @tot_reserv,@ford)         

        Select @Dia_Inicial = DATEADD(day,1, @Dia_Inicial) 
    end
   
    if @row = 1
      begin
          Select 
            @prom_libres     = Sum (libres),
            @prom_ocup     = Sum (Ocup),
            @prom_salen     = Sum (Salen),
            @prom_Reserv  = Sum (Reserv), 
            @prom_Ford      = Sum (Ford), 
            @prom_Entran   =  Sum (Entran),
            @prom_Ocup_Noche = Sum (Ocup_Noche), 
            @prom_Client_noche      = Sum (Client_noche) 
         From #booking 
        Select @cant_dias = DATEDIFF(day,@fecha_i,@Dia_Final) + 1 
        Insert Into #booking   Values (
                             'Pr',@Dia_Final,
                            @prom_libres/@cant_dias    ,
    		 @prom_ocup/@cant_dias   ,
    		 @prom_salen/@cant_dias  ,
    		 @prom_reserv/@cant_dias ,
    		 @prom_ford/@cant_dias  ,
    		 @prom_entran/@cant_dias ,    		     		     		     		     		 
    		 @prom_Ocup_Noche/@cant_dias,
    		 @prom_Client_noche/@cant_dias,
                            0,    		 
    		 @prom_Ocup_Noche / (@total_habit * @cant_dias)  * 100  )
       --Select * From #booking       
      
     end
   -- else
    
   -- Select * From #booking
    
   -- Select * From #libres
   -- If @Opcion = 1 Select SUM(Libres) From #booking

 If @Opcion = 1 Select @Resultado=  SUM(Libres)       From #booking Where Dw='PR'    
 If @Opcion = 2 Select @Resultado=  SUM(Ocup)         From #booking Where Dw='PR'    
 If @Opcion = 3 Select @Resultado=  SUM(Salen)        From #booking Where Dw='PR'    
 If @Opcion = 4 Select @Resultado=  SUM(Reserv)       From #booking Where Dw='PR'    
 If @Opcion = 5 Select @Resultado=  SUM(Ford)         From #booking Where Dw='PR'    
 If @Opcion = 6 Select @Resultado=  SUM(Entran)       From #booking Where Dw='PR'    
 If @Opcion = 7 Select @Resultado=  SUM(Ocup_Noche)   From #booking Where Dw='PR'   
 If @Opcion = 8 Select @Resultado=  SUM(Client_Noche) From #booking Where Dw='PR'   
 If @Opcion = 9 Select @Resultado=  SUM(Tot_habit)    From #booking Where Dw='PR'  
 
 
 
 
    Return @Resultado
end





















