IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_DateForecast')))
THEN Drop view v_DateForecast
END IF

GO

create view
  DBA.v_DateForecast
  as select DateForecast.FDate,
    --Maria +, Status na staia za period
    RoomCount=DateForecast.RoomCount-(select isnull(count(distinct NRoom),0) from DBA.RoomStatusForPeriod where StartDate <= DateForecast.FDate and EndDate >= DateForecast.FDate and NStatus <> 0),
    --Maria -
    DateForecast.BusyRooms,
    DateForecast.LeaveTodayRooms,
    DateForecast.StayingTodayRooms,
    DateForecast.ArrivingTodayRooms,
    DateForecast.BusyRoomsFIT,
    --Maria +, Status na staia za period; proveriava se dali datata, za koiato pravim prognozata e v niakoi ot periodite ot RoomStatusForPeriod
    CrashedRooms=DateForecast.CrashedRooms+(select isnull(count(distinct NRoom),0) from DBA.RoomStatusForPeriod where StartDate <= DateForecast.FDate and EndDate >= DateForecast.FDate and NStatus = 1),
    ServiceRooms=DateForecast.ServiceRooms+(select isnull(count(distinct NRoom),0) from DBA.RoomStatusForPeriod where StartDate <= DateForecast.FDate and EndDate >= DateForecast.FDate and NStatus = 3),
    OwnedRooms=DateForecast.OwnedRooms+(select isnull(count(distinct NRoom),0) from DBA.RoomStatusForPeriod where StartDate <= DateForecast.FDate and EndDate >= DateForecast.FDate and NStatus = 10),
    --Maria -
    DateForecast.Elderly,
    DateForecast.Children,
    ArrivingElderly=isnull((if(convert(integer,now(*)) > convert(integer,DateForecast.FDate)) then 0 else(select count(*) from DBA.reserve join DBA.guests on reserve.ReserveNo = guests.Reservation where guests.AdultType = 0 and reserve.status <> 2 and guests.status=0 and convert(integer,reserve.DateReserveDt) >= convert(integer,now(*)) and convert(integer,reserve.DateReserveDt)+reserve.days-1 >= convert(integer,DateForecast.FDate) and convert(integer,reserve.DateReserveDt) <= convert(integer,DateForecast.FDate)) endif),0),
    ArrivingChildren=isnull((if(convert(integer,now(*)) > convert(integer,DateForecast.FDate)) then 0 else(select count(*) from DBA.reserve join DBA.guests on reserve.ReserveNo = guests.Reservation where guests.AdultType = 1 and reserve.status <> 2 and guests.status=0 and convert(integer,reserve.DateReserveDt) >= convert(integer,now(*)) and convert(integer,reserve.DateReserveDt)+reserve.days-1 >= convert(integer,DateForecast.FDate) and convert(integer,reserve.DateReserveDt) <= convert(integer,DateForecast.FDate)) endif),0),
    DateForecast.Breakfasts,
    DateForecast.Lunches,DateForecast.Dinners,
    DateForecast.HalfBoards,
    BusyTonightRooms=DateForecast.BusyTodayRooms,
    ExpFreeRooms=DateForecast.RoomCount-DateForecast.BusyTodayRooms-(select isnull(count(distinct NRoom),0) from RoomStatusForPeriod where StartDate <= DateForecast.FDate and EndDate >= DateForecast.FDate and NStatus in (1,3,10)),
    PercBusyRooms=100*BusyTonightRooms/DateForecast.RoomCount,
    TotElderly=DateForecast.Elderly+ArrivingElderly,
    TotChildren=DateForecast.Children+ArrivingChildren from
    DBA.DateForecast where
    DateForecast."Session" = bvrConnectID
 -- Kozludjov - Added ArrivingElderly and ArrivingChildren
 -- Maria - changes about room status for period