IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_WaitingBills'))) THEN
Drop View v_WaitingBills
END IF

GO

create view "DBA".v_WaitingBills
  as select G.GuestNum,
    GuestName=G."Name",
    Qty=(SC.NumberServices-SC.PayedServices),
    Price=string(SC.SumCredit,' ',CrS.Symbol),
    PriceLv=SC.SumCredit*CrS.ExchangeRate,
    Total=PriceLv*Qty,
    RoomNo=ASL.LastRoom,
    S.ServiceName,
    G.Note,
    DateService=SC.DateCreditDt
    from "dba".Guests as G,"dba".ServiceCredits as SC,"dba".Currencies as CrS
    ,"dba".v_ArriveStayLeaveGuestBook as ASL,"dba".Services as S
    where SC.WhoPays=G.GuestNum
    and SC.WhoPays=ASL.GuestNum
    and SC.KodCurrency=CrS.KodCurrency
    and SC.NumberServices<>SC.PayedServices
    and SC.Service=S.ServiceNo
    and G.ServiceSum<>0
    and G.Status=2
    and Balance<>0
    and((SC.SPaReservationFk=-1) or(SC.SPaReservationFk<>-1 and SC.SpaTransferFlag=1))
    

union all

  /* Yanko +*/
  select GuestNum=B.Guest,
    GuestName=G."Name",
    Qty=(B.NumLodgings-B.PayedLodgings),
    Price=string(B.Price,' ',Crs.Symbol),
    PriceLv=B.Price*Crs.ExchangeRate,
    Total=PriceLv*Qty,
    RoomNo=B.Room,
    ServiceName='Нощувка',
    G.Note,
    DateService=B.DateRegDt
    from "dba".Book as B,"dba".Guests as G,"dba".Currencies as CrS
    where B.WhoPays=G.GuestNum
    and B.KodCurrency=CrS.KodCurrency
    and B.PayedLodgings<>B.NumLodgings
    and B.Price<>0
    and G.Status=2
    and B.WhoPays<>0

union all

  /* Yanko -*/
  select GuestNum=RM.GuestNo,
    GuestName=G."Name",
    Qty=(-1),
    Price=string(RM.Sum,' ',C.Symbol),
    PriceLv=RM.Sum*C.ExchangeRate,
    Total=Qty*PriceLv,RoomNo='0',ServiceName='Депозит',
    G.Note,
    DateService=RM.DateMoneyDt
    from "dba".ResMoney as RM,"dba".Guests as G,"dba".Currencies as C
    where RM.Status=2
    and RM.InvoiceNo=any(select InvoiceNo from "dba".Invoices where CancelInvoiceNo=0)
    and RM.GuestNo<>0
    and RM.DepositNo>0
    and G.GuestNum=RM.GuestNo
    and G.Balance<>0
    and G.Status=2
    and C.KodCurrency=RM.Currency