IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('CalcRoomNum')))
THEN Drop procedure CalcRoomNum
END IF

GO

create function
DBA.CalcRoomNum(in @RoomNo integer,in @Buzy integer)
returns integer
begin
  declare @NumDays integer;
  declare @ToDate date;
  set @NumDays=0;
  if @Buzy = 0 then
    select max(days(today(*),R.FromDateDt)) into @NumDays
      from ReservePlan as R,Reserve as RR where
      R.RoomNo > 0 and
      R.FromdateDt > today(*) and
      R.RoomNo = @RoomNo and
      RR.ReserveNo = R.ReserveNo and
      RR.Status = 1 and 
      R.FromdateDt = (select min(ReservePlan.FromDateDt) from
        ReservePlan,Reserve where ReservePlan.ReserveNo=Reserve.ReserveNo and Reserve.Status<>2 and
        ReservePlan.RoomNo > 0 and
        ReservePlan.FromdateDt > today(*) and
        ReservePlan.RoomNo = @RoomNo);
    //    message '@NumDays0='+cast(@RoomNo as varchar)+'   '+cast(@NumDays as varchar);
    return @NumDays
  else
    select max(DateregDt+NumLodgings) into @ToDate
      from Book where Room = @RoomNo and Staying < 3;
    select days(today(*),@ToDate) into @NumDays;
    if((@NumDays is null) or(@NumDays <= 0)) then
      set @NumDays=0
    end if;
    //    message '@NumDays='+cast(@RoomNo as varchar)+'   '+cast(@Buzy as varchar)+'   '+cast(@NumDays as varchar);
    return @NumDays
  end if
end
//[v 25.03.2003 ] Jorko
//[v 17.04.2003] Jorko     and Staying<3
//[v 22.08.2003] Jorko  ne pokazva veche anulirani rezervacii
//[v 14.05.2008] Maria, uslovieto v select za R.FromDate e promenemo da vzima nai blizkata data, za koiato ima rezervacia i tia ne e otmenena