SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

alter PROCEDURE GetCalculo_Pronostico_Contratos
                 @Contrato nvarchar(20),
 			     @thab int,
	 		     @f_ent datetime,
			     @f_sal datetime,
			     @respetar_release bit,
			     @respetar_resumen bit
AS

/* Procedimiento que  genera un cursor con la disponibilidad contra cupos ,  
    para cada dia del periodo de fechas pasado como parametro de un 
    contrato (parametro) para un tipo de habit dado y respetando el release del contrato.
    Se devuelve ademas las columnas ; cupos contratados para cada dia,las habit ocup reserv y 
    las que salen ese dia  
 Ojo: A partir del 21/11/2005, si la agencia comparte cupos, 
sumo los cupos,reservadas y ocupadas de todos los contratos de la agencia
*/

Begin
  declare @dia_inic datetime, @release int, 
          @disp int,@cupos int,
          @dia_sal datetime,@fecha_hotelera datetime,@ocup int,
          @reserv int,@hab_salen int, 
          @ncupos int, @room int

Declare @cont int

Select @cont = id_cont from Hotecont where Contrato = @Contrato

  Create Table #t_dispon ( disp int, cupos int, ocup int, reserv int, release int)
  Select @dia_sal = @f_sal
  Select @dia_inic = @f_ent

  Select @fecha_hotelera = fecha_hotelera From Hotetabl
  While DATEDIFF(day,@dia_inic,@dia_sal) >= 0 
    begin 
       Execute Calc_Release_Contrato     @cont, @dia_inic, @release output 
       Execute Calc_Cupos_Cont_Dia_Tipo  @cont, @dia_inic, @thab, @respetar_release, @cupos output

      /* Calculo habitaciones ocupadas para la noche 
       (no incluyo las habitaciones que salen el dia que estoy analizando) */
     Select @ocup = Count(*) 
        From Hoteface face 
           Inner Join Hoteuhab uhab on face.id_uhab = uhab.id_uhab
           Inner Join Hotethab thab on uhab.id_thab = thab.id_thab
 	   Inner Join Hotecont cont  on face.id_cont = cont.id_cont
	   Inner Join Hoteagen agen  on cont.id_agen = agen.id_agen
         Where
	      DateDiff(day, @dia_inic, face.fecha_sal) > 0 and
              thab.id_thab  = @thab               and
	     ( face.id_cont = @cont or 
	       face.id_cont in ( Select id_cont 
					From hotecont
					where id_agen in(Select cont.id_agen 
							   from hotecont cont 
							     Inner Join Hoteagen agen on cont.id_agen = agen.id_agen 
							    where cont.id_cont = @cont and agen.comparte_cupos = 1
							 ) )	
	     ) 
     SET @ocup = COALESCE(@ocup,0)

     /* Calculo habitaciones reservadas  por reservas para la noche  */
     /* ===========================================================  */
    Select @reserv = Sum (lres.cant_habi) 
        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 Hoteagen agen  on cont.id_agen = agen.id_agen
           Where  rese.id_estado in (3,5) and
	          DateDiff(day, rese.fecha_ent, @dia_inic) >= 0 and
	          DateDiff(day, @dia_inic, rese.fecha_sal) > 0  and
                  lres.id_uhab in 
                      (Select id_uhab 
                          From Hoteuhab 
                          Where id_thab = @thab)  and
	          ( rese.id_cont = @cont or 
		    rese.id_cont in ( Select id_cont 
					             From hotecont
					             where id_agen in 
					             (Select cont.id_agen 
							         from hotecont cont 
							           Inner Join Hoteagen agen on cont.id_agen = agen.id_agen 
							          where cont.id_cont = @cont and agen.comparte_cupos = 1
							 ) )	
	           ) 
     SET @reserv = COALESCE(@reserv,0)

     /* Calculo habitaciones reservadas por rooming  para la noche  */
      /* ===========================================================  */
    Select @room = Count(*) 
       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 Hoteagen agen  on cont.id_agen = agen.id_agen
        Where 
             rool.id_estado in (3,5) 	    		   and 
	     DateDiff(day, rool.fecha_ent, @dia_inic) >= 0 and
	     DateDiff(day, @dia_inic, rool.fecha_sal) > 0  and
             rool.id_uhab in (Select id_uhab From Hoteuhab Where id_thab = @thab)  and
	     ( room.id_cont = @cont or 
	       room.id_cont in ( Select id_cont 
					From hotecont
					where id_agen in(Select cont.id_agen 
							   from hotecont cont 
							     Inner Join Hoteagen agen on cont.id_agen = agen.id_agen 
							    where cont.id_cont = @cont and agen.comparte_cupos = 1
							 ) )	
	      )

    SET @room = COALESCE(@room,0)

    if @respetar_release = 1
      if  DATEDIFF(day,@fecha_hotelera,@dia_inic) < @release 
          Select  @ncupos = 0 
      else
         Select  @ncupos = @cupos 
    else
        Select  @ncupos = @cupos           
  
     Select  @disp  = @ncupos - @ocup - @reserv - @room  

     SET @reserv=  @reserv + @room 

     Insert #t_dispon Values(@disp,@cupos,@ocup,@reserv,@release)
     Select @dia_inic = DATEADD(day,1, @dia_inic) 
  end

  Select * From #t_dispon     
end




















