SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

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


CREATE PROCEDURE DSS_Pronostico_Complementario_Futuro
                 @Dia		 datetime, 
				 @Resultado int Output

AS
Begin
 
  declare  @ocup int, @reserv int, @room int,
	       @Socup int, @Sreserv int, @Sroom int,
		   @fecha_hotel datetime , @Prueba int
            

            
  Select @fecha_hotel = fecha_hotelera from hotetabl

/******************************  Calculo de Ocupacion Estan ***************************/
  Select @ocup = Count(face.id_face) 
	     From Hoteface face
		  	  inner join HOTECONT cont on face.id_cont= cont.id_cont
		 Where DateDiff(day, fecha_ent, @dia) >= 0 
		   and DateDiff(day, @dia, fecha_sal) >= 0 
		   and cont.TipoContrato=1 
				   
				   
  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
		 Where DateDiff(day, rese.fecha_ent, @dia) >= 0 
		   and DateDiff(day, @dia, rese.fecha_sal) >= 0 
		   and rese.id_estado in (3,5) 
		   and cont.TipoContrato=1 


  Select @room = Count(rool.id_rool) 
         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
         Where DATEDIFF(day,@fecha_hotel, rool.fecha_ent) >= 0 
           and DATEDIFF(day,rool.fecha_ent, @dia) >= 0 
           and DATEDIFF(day,@dia, rool.fecha_sal) >= 0 
           and cont.TipoContrato=1 
           and rool.id_estado in (3,5) 
	      
/******************************  Calculo de Ocupacion Salen ***************************/

  Select @Socup = Count(face.id_face) 
         From Hoteface face
              Inner Join HOTECONT cont on face.id_cont = cont.id_cont
         Where DATEDIFF(day,fecha_sal, @dia) = 0 
           and cont.TipoContrato=1


  Select @Sreserv = Count(rool.id_rool)  
     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
     Where DATEDIFF(day,@fecha_hotel, rool.fecha_ent) >= 0 
       and DATEDIFF(day, @dia, rool.fecha_sal) = 0 
       and cont.TipoContrato=1 
       and rool.id_estado in (3,5)           


  Select @Sroom = 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
		 Where DATEDIFF(day,rese.fecha_sal,@dia) = 0 
		   and cont.TipoContrato=1 
		   and rese.id_estado in (3,5)   
	      
    set @Resultado=(@ocup+Coalesce(@reserv,0)+@room)-(@Socup+@Sreserv+Coalesce(@Sroom,0))		             

    Return @Resultado
end	      