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
    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,
    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 where /*Maria*/ /*Maria*/
    --Maria
    B.Room = R.RoomNum and
    --Maria
    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 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='',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
	
GO

Delete from Relations where lcase(RelationTable) = lcase('Сметка за стая - Справка')

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('//v_SmetkaRoom','__OPTIONS__','Сметка за стая - Справка','__OPTIONS__','Сметка за стая - Справка','__OPTIONS__',1,GetFreeNInRelations('Сметка за стая - Справка', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','',1,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('//v_SmetkaRoom','Room','Сметка за стая - Справка','Стая','Сметка за стая - Справка','Стая',2,GetFreeNInRelations('Сметка за стая - Справка', Null, Null, 10),'Room','','',3,0,'','','select RoomName from Rooms where Status <> 1 and Status <> 3','',40,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('//v_SmetkaRoom','GuestName','Сметка за стая - Справка','Гост','Сметка за стая - Справка','Гост',2,GetFreeNInRelations('Сметка за стая - Справка', Null, Null, 10),'GuestName','','',3,0,'','','','',200,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('//v_SmetkaRoom','ServiceName','Сметка за стая - Справка','Услуга','Сметка за стая - Справка','Услуга',2,GetFreeNInRelations('Сметка за стая - Справка', Null, Null, 10),'ServiceName','','',3,1,'','','','',300,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('//v_SmetkaRoom','ServicePrice','Сметка за стая - Справка','Цена','Сметка за стая - Справка','Цена',2,GetFreeNInRelations('Сметка за стая - Справка', Null, Null, 10),'ServicePrice','','# ### ##0.00',1,0,'','','','',50,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('//v_SmetkaRoom','Qty','Сметка за стая - Справка','Кол.','Сметка за стая - Справка','Кол.',2,GetFreeNInRelations('Сметка за стая - Справка', Null, Null, 10),'Sum(Qty)','Sum(qrData.Кол.)','',1,0,'','','','',32,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('//v_SmetkaRoom','TotalSum','Сметка за стая - Справка','Сума','Сметка за стая - Справка','Сума',2,GetFreeNInRelations('Сметка за стая - Справка', Null, Null, 10),'Sum(TotalSum)','Sum(qrData.Сума)','# ### ##0.00',1,0,'','','','',50,'',0,0,1,today())

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='v_SmetkaRoom'and ColName='__OPTIONS__')

Go

f1: for f1 as curs scroll cursor for select grNo=N from AdminGroups do
  if grNo <> 0 then
    insert into InformPermission(N,RelationN,AdminGroupNo,Stoinost)
    values((select MAX(N+1) from InformPermission),(select N from Relations where TableName='//v_SmetkaRoom'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;

GO

Delete from Relations where lcase(RelationTable) = lcase('Free and occupied beds for period')

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#FreeBedsForPeriod','__OPTIONS__','Свободни и заети стаи за период','__OPTIONS__','Free and occupied beds for period','__OPTIONS',1,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','call FillFreeBedsForPeriod()',1,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#FreeBedsForPeriod','bvrData2','Свободни и заети стаи за период','От дата','Free and occupied beds for period','FromDate',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select today(*)',100,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#FreeBedsForPeriod','bvrData1','Свободни и заети стаи за период','До дата','Free and occupied beds for period','ToDate',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'bvrData1','','',0,0,1,1,'','select today(*)',100,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#FreeBedsForPeriod','CurrentDate','Свободни и заети стаи за период','Дата','Free and occupied beds for period','CurrentDate',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'CurrentDate','','',1,0,'','','','',100,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#FreeBedsForPeriod','HotelName','Свободни и заети стаи за период','Общежитие','Free and occupied beds for period','HotelName',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'HotelName','','',0,0,'','','','',100,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#FreeBedsForPeriod','TotalBeds','Свободни и заети стаи за период','Легла','Free and occupied beds for period','TotalBeds',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'sum(TotalBeds)','Sum(qrData.Легла)','',0,0,'','','','',100,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#FreeBedsForPeriod','Students','Свободни и заети стаи за период','Студенти','Free and occupied beds for period','Students',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'sum(Students)','Sum(qrData.Студенти)','',0,0,'','','','',100,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#FreeBedsForPeriod','FreeBeds','Свободни и заети стаи за период','Свободни легла','Free and occupied beds for period','FreeBeds',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'sum(FreeBeds)','Sum(qrData.Свободни легла)','',0,0,'','','','',100,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#FreeBedsForPeriod','Unusable','Свободни и заети стаи за период','Неизползваеми','Free and occupied beds for period','Unusable',2,GetFreeNInRelations('Free and occupied beds for period', Null, Null, 10),'Unusable','Sum(qrData.Неизползваеми)','',0,0,'','','','',80,'',0,0,1,today())

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='#FreeBedsForPeriod'and ColName='__OPTIONS__')

Go

f1: for f1 as curs scroll cursor for select grNo=N from AdminGroups do
  if grNo <> 0 then
    insert into InformPermission(N,RelationN,AdminGroupNo,Stoinost)
    values((select MAX(N+1) from InformPermission),(select N from Relations where TableName='#FreeBedsForPeriod'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;

GO