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

GO

create view
  v_ServicesSaif
  as select Account=g.GuestNum,
    ServiceDate=DATEFORMAT(sc.DateCreditDt,'DD.MM.YYYY'),
    Service=s.ServiceName,
    GuestName=g."Name",
    Country=Countries.Description,
    Agent=Contracts.ShortName,
    PayedSum=sc.PayedServices*sc.SumCredit,
    Balance=(sc.PayedServices-sc.NumberServices)*sc.SumCredit,
    AdminName=Admin.ShortName,
    Room=ASL.LastRoom,
    Currency=cur.Symbol
    from "dba".ServiceCredits as sc
    ,Services as s
    ,Contracts LEFT OUTER JOIN Guests as g on Contracts.ContractNo=g.Contract
    ,Countries
    ,Admin
    ,Currencies as cur
    ,v_ArriveStayLeaveGuestBook as ASL
    where sc.Service=s.ServiceNo
    and sc.GuestNo=g.GuestNum
    and Contracts.GroupNo=any(select distinct(showtype) from "dba".GrContracts where GrNo=-1)
    and g.CountryArrive=Countries.CountryKod
    and sc.KodAdmin=Admin.AdminNo
    and ASL.GuestNum=g.GuestNum
    and sc.KodCurrency=cur.KodCurrency
    and ((s.ServiceName like '%сейф%') or (s.ServiceName like '%Сейф%'))
    and ((sc.SPaReservationFk=-1) or (sc.SPaReservationFk<>-1 and sc.SpaTransferFlag=1))


GO

Delete from Relations where lcase(RelationTable) = lcase('ServicesSafe')

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_ServicesSaif','ServiceDate','Сейф услуги','Дата',
 'ServicesSafe','Date',2,
 GetFreeNInRelations('ServicesSafe', Null, Null, 10),
 'ServiceDate','','',0,1,'','','','',80,'',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_ServicesSaif','Room','Сейф услуги','Стая No',
 'ServicesSafe','Room #',2,
 GetFreeNInRelations('ServicesSafe', Null, Null, 10),
 'Room','','',0,1,'','','','',48,'',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_ServicesSaif','Account','Сейф услуги','Кл.номер',
 'ServicesSafe','Client Number',2,
 GetFreeNInRelations('ServicesSafe', Null, Null, 10),
 'Account','','',0,0,'','','','',80,'',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_ServicesSaif','Agent','Сейф услуги','Агент',
 'ServicesSafe','Agent',2,
 GetFreeNInRelations('ServicesSafe', Null, Null, 10),
 'Agent','','',0,0,'','','','',80,'',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_ServicesSaif','GuestName','Сейф услуги','Име гост',
 'ServicesSafe','Guest Name',2,
 GetFreeNInRelations('ServicesSafe', Null, Null, 10),
 'GuestName','','',0,0,'','','"SELECT ""Name"" FROM Guests GROUP BY ""Name"""','',150,'',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_ServicesSaif','Country','Сейф услуги','Държава',
 'ServicesSafe','Country',2,
 GetFreeNInRelations('ServicesSafe', Null, Null, 10),
 'Country','','',0,0,'','','select Description from Countries','',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('v_ServicesSaif','Service','Сейф услуги','Услуга',
 'ServicesSafe','Service',2,
 GetFreeNInRelations('ServicesSafe', Null, Null, 10),
 'Service','','',0,0,'','','select ServiceName from Services where ServiceName like ''%сейф%'' or ServiceName like ''%Сейф%''','',120,'',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_ServicesSaif','PayedSum','Сейф услуги','Платени',
 'ServicesSafe','Paid',2,
 GetFreeNInRelations('ServicesSafe', Null, Null, 10),
 'Sum(PayedSum)','Sum(qrData.Платени)','# ##0.00',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('v_ServicesSaif','Balance','Сейф услуги','Баланс',
 'ServicesSafe','Balance',2,
 GetFreeNInRelations('ServicesSafe', Null, Null, 10),
 'Balance','Sum(qrData.Баланс)','# ##0.00',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('v_ServicesSaif','Currency','Сейф услуги','Код валута',
 'ServicesSafe','Currency',2,
 GetFreeNInRelations('ServicesSafe', Null, Null, 10),
 'Currency','','',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('v_ServicesSaif','AdminName','Сейф услуги','Админ',
 'ServicesSafe','Admin',2,
 GetFreeNInRelations('ServicesSafe', Null, Null, 10),
 'AdminName','','',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('v_ServicesSaif','__OPTIONS__','Сейф услуги','__OPTIONS__','ServicesSafe','__OPTIONS__',1,
GetFreeNInRelations('ServicesSafe', Null, Null, 10),
 '__OPTIONS__','','',0,0,'','','','',NULL,'',0,0,1,today())


GO

if (select count(*) from InformPermission where RelationN=(
      select N from Relations where TableName='v_ServicesSaif' and ColName='__OPTIONS__'))=0 then
      
       insert into InformPermission(N,RelationN,AdminGroupNo,Stoinost)
         values((select MAX(N+1) from InformPermission),(select N from Relations where TableName='v_ServicesSaif' and ColName='__OPTIONS__'),1,1);
end if;