if (Exists (Select Table_name from SysTable where Table_name='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
         ,SpaReservations SpaRes
    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 ((SpaRes.N=SC.SPaReservationFk) and(SpaRes.IfPayed=2))))

    union all

    
  select G.GuestNum,
    GuestName=G."Name",
    Qty=(B.NumLodgings-B.PayedLodgings),
    Price=string(B.Price,' ',CrS.Symbol),
    PriceLv=B.Price*CrS.ExchangeRate,
    Total=PriceLv*Qty,
    RoomNo=Rooms.RoomName,ServiceName='Нощувка',
    G.Note,
    DateService=B.DateRegDt
    from "dba".Guests as G,"dba".Book as B,"dba".Currencies as Crs,"dba".Rooms
    where B.WhoPays=G.GuestNum
    and B.Room=Rooms.RoomNum
    and B.KodCurrency=CrS.KodCurrency
    and B.NumLodgings<>B.PayedLodgings
    and G.ServiceSum<>0
    and G.Status=2
    and Balance<>0 union all
  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
//[01.03.04] dobaven e nov kriterii za selektirane na neuredeni smetki t.e saldoto da e <> 0
//////////////////////////////////////////////////////////////////////////////////////////////////////////////