if (Exists (Select Table_name from SysTable where Table_name='v_OfficialRegGuests')) then
  Drop View v_OfficialRegGuests
end if

GO

create VIEW "DBA"."v_OfficialRegGuests"  as
select G.GuestNum
from "dba".ResMoney as R,"dba".Invoices as I,"dba".Guests as G
where R.GuestNo>0
  and R.InvoiceNo=I.InvoiceNo
  and I.VidSmetka in (2,6)
  and I.CancelInvoiceNo=0
  and G.GuestNum=R.GuestNo
  and R.DateMoneyDt between bvrData1 and bvrData2
 union
select Guest from Book
where RegNum in (select RegNo
                 from Articles
                 where InvoiceNo in (select R.InvoiceNo
                                     from ResMoney R,Invoices I
                                     where I.InvoiceNo=R.InvoiceNo
                                       and I.VidSmetka in (2,6)
                                       and I.CancelInvoiceNo=0
                                       and R.DateMoneyDt between bvrdata1
                                       and bvrdata2)
                                       and RegNo>0
                )
 union
select GuestNum
from Guests
where GuestNum in (select GuestNo
                   from ServiceCredits
                   where CreditNo in (select CreditNo
                                      from Articles
                                      where InvoiceNo in (select R.InvoiceNo
                                                          from ResMoney R,Invoices I
                                                          where I.InvoiceNo=R.InvoiceNo
                                                            and I.VidSmetka in (2,6)
                                                            and I.CancelInvoiceNo=0
                                                            and R.DateMoneyDt between bvrdata1 and bvrdata2)
                                                            and CreditNo>0)
                  )
//[05.08.2005] Jorko, Modify
GO

if (Exists (Select Table_name from SysTable where Table_name='v_OfficialRegisterDanFak')) then
  Drop View v_OfficialRegisterDanFak
end if

GO

create VIEW "DBA"."v_OfficialRegisterDanFak"  as
select G.GuestNum,
G.OfficialNum,
ArriveDate,Signature='',
LeaveDate=convert(date,Days(BK.ArriveDate,BedNights)),
G."Name",
BirthCountry=CO.Description,
BirthData=String(BirthDateDt,', ',G.City),
PassportData=String(G.PassportSeria,G.PassportNumber),
Age=Years(BirthDateDt,current date),
ImaTaksa=IsResortTaxed(BirthCountry,BirthDateDt,G.Sex),
AddressData=G."Address",
RoomNo=LastRoom,
BNights=Days(BK.ArriveDate,BK.LeaveDate),
TNights=ImaTaksa*BNights,
BedNights=Restr(BNights),
TaxNights=Restr(TNights),
MPSData=String(G.CarNumber,' ',G.CarModel),
G.Reservation,
G.Balance
from "DBA".v_ArriveStayLeaveGuestBook as BK,"DBA".Guests as G,"DBA".Countries as CO,"DBA".Contracts as Ctr
where BK.GuestNum=G.GuestNum
  and G.NativeCountry=CO.CountryKod
  and Mod(RoomNo,bvrOfficDivider)<bvrOfficReminder
  and ArriveDate>=bvrData1
  and ArriveDate<=bvrData2
  and G.OfficialNum>=0
  and G.Contract=Ctr.ContractNo
  and G.GuestNum in (select GuestNum from v_OfficialRegGuests)
and(Ctr.ContractNo=0 or Ctr.ShortName like bvrOfficContrIncl)
and G.HasData=1
//[05.08.2005] Jorko, Modify
GO

if (Exists (Select Table_name from SysTable where Table_name='v_FreeRooms')) then
  Drop View v_FreeRooms
end if

GO

create view "DBA".v_FreeRooms as
select Room=Rooms.RoomNum,
    Class=Max(CL.ShortName),
    Status=(if count(Book.RegNum)=0 then '  Свободна' else
      if min(Book.DateRegDt+Book.NumLodgings)=bvrData2 then 'Напуска' else ' Заета' endif
    endif),
    ReserveName=max(Reserve."Name")
    from "DBA".Rooms left outer join "dba".Book on Book.NumLodgings<>0
            and Rooms.RoomNum=Book.Room 
            and Days(Book.DateRegDt,Book.NumLodgings)>=bvrData2 
            and Book.DateRegDt<=bvrData2
		left outer join ReservePlan on Rooms.RoomNum=ReservePlan.RoomNo 	
                  and ReservePlan.FromDateDt=bvrData2
		left outer join Reserve on ReservePlan.ReserveNo=Reserve.ReserveNo 	
    ,"DBA".Classes as CL,
    "DBA".Reserve
    where Rooms.RoomCat=CL.Class
	and Rooms.Status<>1
	and Rooms.Status<>3
    group by Rooms.RoomNum
//[v. 01.06.29]
//[v. 05.08.2005] Jorko H5-->H9
GO

if (Exists (Select Table_name from SysTable where Table_name='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),
    RoomStatus=Max(RoomStatus.Description),
    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=1)
    and(Guests.Status=1) then 1 else 0 endif),
    RegDate=Max(if(Book.DateRegDt<=bvrData1)
    and(Days(Book.DateRegDt,Book.NumLodgings)>=bvrData1) then
      Book.DateRegDt else null endif),
    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=Max(Days(Book.DateRegDt,(select bb.NumLodgings
                                       from "dba".book as bb
                                       where bb.room=RoomNum
                                         and bb.DateRegDt<=bvrData1
                                         and Days(bb.DateRegDt,bb.NumLodgings)>=bvrData1
                                         and bb.Guest=Guests.GuestNum))),
    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),
    LeaveStat=if LeaveDate=bvrData1 then 'З' else ' ' endif,
    IsReserve=Max(if RP.ReserveNo<>0 then 'Резервирана' else null
    endif)
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
    ,Rooms left outer join Book on Rooms.RoomNum=Book.Room
                   and Book.DateRegDt<bvrData1
                   and Days(Book.DateRegDt,Book.NumLodgings)>=bvrData1
                   and Book.NumLodgings>0
    ,Guests left outer join 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
//[05.08.2005] Jorko, Update na view-to "where clause"