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

GO

create view
  DBA.v_Kamerierki
  as select RoomType=Max(Classes.ShortName),
    RoomNum=Rooms.RoomNum,
    GuestNo=Min(Book.Guest),
    -- Maria +, Status na sraia za period
    RoomStatus=(if (select count(*) from RoomStatusForPeriod where NRoom=RoomNum and StartDate<=bvrData1 and EndDate>=bvrData1) > 0 then 
       (select RS.Description from RoomStatusForPeriod as RSFP, RoomStatus as RS where RSFP.NRoom=RoomNum and RSFP.StartDate<=bvrData1 and RSFP.EndDate>=bvrData1 and RSFP.NStatus=RS.RoomStatusNo)
            else Max(RoomStatus.Description) endif),
    --Maria -
    Elderly=Sum(if(Book.DateRegDt <= bvrData1) and
    (Days(Book.DateRegDt,Book.NumLodgings) >= bvrData1) and
    (Guests.AdultType = 0) and
    (Guests.Status = 1) then 1 else 0 endif),
    Children=Sum(if(Book.DateRegDt <= bvrData1) and
    (Days(Book.DateRegDt,Book.NumLodgings) >= bvrData1) and
    (Guests.AdultType > 0) and
    (Guests.Status = 1) then 1 else 0 endif),
    RegDate=DBA.GetLeaveArriveDate(1,GuestNo),
    DaysClean=max(Registers.IntStojnost),
    Clean=(if Mod("date"(bvrData1)-RegDate,DaysClean) <> 0 then ' ' else 'П,' endif),
    ChangeSheets=(if Mod("date"(bvrData1)-RegDate,(select Max(r.IntStojnost) from
      DBA.Registers as r where
      r.RegisterName = 'CleanPerDays')) <> 0 then '' else 'СБ' endif),
    ToDo=Clean+LeaveStat,
    LeaveDate=DBA.GetLeaveArriveDate(2,GuestNo),
    GuestName=Max(if(Book.DateRegDt <= bvrData1) and
    (Days(Book.DateRegDt,Book.NumLodgings) >= bvrData1) then
      Guests.Name
    else null
    endif),VIP=Max(if(Book.DateRegDt <= bvrData1) and
    (Days(Book.DateRegDt,Book.NumLodgings) >= bvrData1) then
      VIP.VipName else null endif),
    KamerierkaName=(select FullName from DBA.Admin where AdminNo = Rooms.Kamerierka),
    LeaveStat=if LeaveDate = bvrData1 then 'З' else ' ' endif,
    IsReserve=Max(if RP.ReserveNo <> 0 then
      if(select Lr.Status from DBA.Reserve as Lr where Lr.ReserveNo = RP.ReserveNo) = 2 then null
      else 'Резервирана' endif else null endif),Note1='',Note2='' from DBA.Book left outer join DBA.Guests on Book.Guest = Guests.GuestNum,
    DBA.Rooms left outer join DBA.ReservePlan as RP on RP.FromDateDt = bvrData1 and Rooms.RoomNum = RP.RoomNo,
    DBA.Rooms left outer join DBA.Book on Rooms.RoomNum = Book.Room and
    Book.DateRegDt < bvrData1 and
    Days(Book.DateRegDt,Book.NumLodgings) >= bvrData1 and
    Book.NumLodgings > 0,
    DBA.Guests left outer join DBA.VIP on Guests.TipVip = VIP.N,
    DBA.Guests,DBA.Classes,DBA.RoomStatus,DBA.VIP,DBA.Registers where
    Rooms.RoomCat = Classes.Class and
    Rooms.Status = RoomStatus.RoomStatusNo and
    Registers.RegisterName = 'Kamerierki'
    group by RoomNum,KamerierkaName