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
G.Name GuestName,
G.Balance Total
FROM
Guests G
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

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))
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))
HAVING SUM((SC.NumberServices - SC.PayedServices) * SC.SumCredit * CrS.ExchangeRate) > 0
	
GO

Delete from Relations where lcase(TableName) = lcase('v_WaitingBills2')

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_WaitingBills2','__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_WaitingBills2','GuestName','Неуредени сметки - Обобщена','Име','Неуредени сметки - Обобщена','GuestName',2,GetFreeNInRelations('Неуредени сметки - Обобщена', Null, Null, 10),'GuestName','','',0,0,'','','','',180,'',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_WaitingBills2','Total','Неуредени сметки - Обобщена','Общо','Неуредени сметки - Обобщена','Total',2,GetFreeNInRelations('Неуредени сметки - Обобщена', Null, Null, 10),'Total','','',0,0,'','','','',90,'',0,0,1,today(*))

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='v_WaitingBills2'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_WaitingBills2'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;

GO