if (select count(*) from Registers where RegisterName='HideOldDebts')=0 then
	insert into Registers(RegisterName,IntStojnost,Komentar)
    values('HideOldDebts',0,'Скрива стари задължения (по-стари от 5 години)')
end if

GO

IF (EXISTS(Select * from sys.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,
    Season=0,
    GuestName=G.Name,
    Qty=(SC.NumberServices-SC.PayedServices),
    Prc=0.0,
    Price=string(SC.SumCredit,' ',CrS.Symbol),
    PriceLv=SC.SumCredit*CrS.ExchangeRate,
    Total=PriceLv*Qty,
    --Maria
    --RoomNo = ASL.LastRoom,
    RoomNo=ASL.LastRoomName,
    --Maria
    ServiceName=if exists(select * from DBA.SvoServiceCreditsStn where SvoServiceCreditsStn.glava = SC.CreditNo and SvoServiceCreditsStn.Svoistvo = 1) then
      (select SvoServiceCreditsStn.Stoinost from DBA.SvoServiceCreditsStn where SvoServiceCreditsStn.glava = SC.CreditNo and SvoServiceCreditsStn.Svoistvo = 1)
    else S.ServiceName
    endif,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 G.Balance <> 0 and (
    (SC.SPaReservationFk = -1) or(SC.SPaReservationFk <> -1 and SC.SpaTransferFlag = 1)) 
	and (((SC.DateCreditDt>DATEADD(YEAR, -5, GETDATE())) 
          and ((select intstojnost from dba.registers where registername = 'HideOldDebts') = 1) and (SC.NumberServices<>SC.PayedServices))
	    or (((select intstojnost from dba.registers where registername = 'HideOldDebts') = 0))) union all
  /* Yanko +*/
  select distinct GuestNum=B.Guest,
    Season=Ps.SeasonNo,
    GuestName=G.Name,
    Qty=Round(DBA.GetMonthKol(B.RegNum,Season),3),
    Prc=DBA.GetPriceForBook(B.RegNum,B.PriceKind,Season),
    Price=string(Prc,' ',Crs.Symbol),
    PriceLv=Prc*Crs.ExchangeRate,
    Total=PriceLv*Qty,
    --Maria
    --RoomNo=B.Room,
    RoomNo=R.RoomName,ServiceName='Наем (' || Lower(Ps.Description) || ' сезон)',
    --Maria
    G.Note,
    DateService=B.DateRegDt from DBA.PriceSeasons as Ps
      ,dba.Book as B,dba.Guests as G,dba.Currencies as CrS,DBA.Rooms as R /*Maria*/ /*Maria*/
    --Maria
    where B.Room = R.RoomNum
    --Maria
    and 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
    and Qty <> 0.0 
	and (((dateadd(day,B.NumLodgings,B.DateRegDt)>DATEADD(YEAR, -5, GETDATE())) 
          and ((select intstojnost from dba.registers where registername = 'HideOldDebts') = 1))
         or (((select intstojnost from dba.registers where registername = 'HideOldDebts') = 0))) union all
  /* Yanko -*/
  select GuestNum=RM.GuestNo,
    Season=0,
    GuestName=G.Name,
    Qty=(-1),
    Prc=0.0,
    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 Invoices.InvoiceNo from dba.Invoices where Invoices.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
	
GO

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

GO

create view DBA.v_WaitingBills2
  as SELECT distinct
G.Name GuestName,
G.Balance Total
FROM
Guests G
join Book B on G.GuestNum=B.Guest
WHERE (((select intstojnost from dba.registers where registername = 'FlagMinusSaldo') = 1 and Total > 0) 
	or ((select intstojnost from dba.registers where registername = 'FlagMinusSaldo') = 0))
AND G.Status = 2
and (((dateadd(day,B.NumLodgings,B.DateRegDt)>DATEADD(YEAR, -5, GETDATE())) 
          and ((select intstojnost from dba.registers where registername = 'HideOldDebts') = 1))
         or (((select intstojnost from dba.registers where registername = 'HideOldDebts') = 0)))

union all

SELECT 
MAX(G.GroupName) GuestName,  
sum((B.NumLodgings - B.PayedLodgings)*B.Price*C.ExchangeRate) Total
FROM
Groups G, Book B, Currencies C
WHERE B.GroupPays <> 0 
AND B.GroupPays = G.GroupNum 
AND C.KodCurrency = B.KodCurrency 
AND B.NumLodgings > B.Payedlodgings
and (((select intstojnost from dba.registers where registername = 'FlagMinusSaldo') = 1 and (B.Price > 0 and (B.NumLodgings - B.PayedLodgings)<>0)) 
	or ((select intstojnost from dba.registers where registername = 'FlagMinusSaldo') = 0))
and (((dateadd(day,B.NumLodgings,B.DateRegDt)>DATEADD(YEAR, -5, GETDATE())) 
          and ((select intstojnost from dba.registers where registername = 'HideOldDebts') = 1))
	    or (((select intstojnost from dba.registers where registername = 'HideOldDebts') = 0)))
HAVING SUM((B.NumLodgings - B.PayedLodgings) * B.Price * C.ExchangeRate) > 0

union all

SELECT
MAX(G.GroupName) GuestName,
sum((SC.NumberServices - SC.PayedServices)*SC.SumCredit*CrS.ExchangeRate) Total
FROM
DBA.Groups G, DBA.ServiceCredits SC, DBA.Currencies CrS,
WHERE SC.GroupPays <> 0 
AND SC.GroupPays = G.GroupNum 
AND SC.KodCurrency = CrS.KodCurrency 
AND SC.NumberServices > SC.PayedServices
AND ((SC.SPaReservationFk=-1) or (SC.SPaReservationFk<>-1 and SC.SpaTransferFlag=1))
and (((select intstojnost from dba.registers where registername = 'FlagMinusSaldo') = 1 and (SC.SumCredit > 0 and (SC.NumberServices - SC.PayedServices)<>0)) 
	or ((select intstojnost from dba.registers where registername = 'FlagMinusSaldo') = 0))
and (((SC.DateCreditDt>DATEADD(YEAR, -5, GETDATE())) 
          and ((select intstojnost from dba.registers where registername = 'HideOldDebts') = 1) and (SC.NumberServices<>SC.PayedServices))
	    or (((select intstojnost from dba.registers where registername = 'HideOldDebts') = 0)))
HAVING SUM((SC.NumberServices - SC.PayedServices) * SC.SumCredit * CrS.ExchangeRate) > 0
	
GO