IF (Exists(select Table_name from SysTable where LCase(Table_name)=LCase('v_CrossServices'))) THEN
  Drop view v_CrossServices
END IF
Go

CREATE VIEW v_CrossServices
  (
    FromGuestName,
    FromAccount,
    Service,
    ServiceNum,
    /*    ServiceSngPrice=DayRates.ExchangeRate*ServiceCredits.SumCredit,*/
    ServiceSngPrice,
    AllSum,
    Currency,
    RegDate,
    ToGuestName,
    ToAccount,
    AdminName
  )
AS SELECT
    FromGuest."Name" as FromGuestName,
    FromGuest.GuestNum as FromAccount,
    Services.ServiceName as Service,
    ServiceCredits.NumberServices as ServiceNum,
    /*    ServiceSngPrice=DayRates.ExchangeRate*ServiceCredits.SumCredit,*/
    ServiceCredits.SumCredit as ServiceSngPrice,
    ServiceCredits.NumberServices*ServiceSngPrice as AllSum,
    Currencies.Symbol as Currency,
    ServiceCredits.DateCreditDt as RegDate,
    ToGuest."Name" as ToGuestName,
    ToGuest.GuestNum as ToAccount,
    Admin.ShortName as AdminName
FROM "dba".ServiceCredits,"dba".Services,"dba".Guests as FromGuest,"dba".Guests as ToGuest,"DBA".admin,"DBA".DayRates,"DBA".Currencies
WHERE DayRates.DateDt=ServiceCredits.DateCreditDt
    and DayRates.KodCurrency=ServiceCredits.KodCurrency
    and ServiceCredits.Service=Services.ServiceNo
    and ServiceCredits.DateCreditDt>=bvrData1 and ServiceCredits.DateCreditDt<=bvrData2
    and ServiceCredits.GuestNo=FromGuest.GuestNum
    and ServiceCredits.GuestNo<>ServiceCredits.WhoPays
    and ServiceCredits.WhoPays<>0
    and ServiceCredits.KodCurrency=Currencies.KodCurrency
    and ServiceCredits.WhoPays=ToGuest.GuestNum
    and ServiceCredits.KodAdmin=Admin.AdminNo

Go

Delete from Relations where lcase(RelationTable) = lcase('Transfer of Service to Account')

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_CrossServices','bvrData1','Прехвърлени услуги към сметка','От дата','Transfer of Service to Account','From Date',0,
GetFreeNInRelations('Transfer of Service to Account', Null, Null, 10),'bvrData1','','',0,0,1,1,'','select bvrData1',72,'',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_CrossServices','bvrData2','Прехвърлени услуги към сметка','До дата','Transfer of Service to Account','To Date',0,
GetFreeNInRelations('Transfer of Service to Account', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select bvrData2',72,'',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_CrossServices','FromGuestName','Прехвърлени услуги към сметка','От гост','Transfer of Service to Account','From Guest',2,
GetFreeNInRelations('Transfer of Service to Account', Null, Null, 10),'FromGuestName','','',0,0,'','','select "Name" from Guests','',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_CrossServices','FromAccount','Прехвърлени услуги към сметка','От сметка','Transfer of Service to Account','From Account',2,
GetFreeNInRelations('Transfer of Service to Account', Null, Null, 10),'FromAccount','','',0,0,'','','','',60,'',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_CrossServices','Service','Прехвърлени услуги към сметка','Услуга','Transfer of Service to Account','Service',2,
GetFreeNInRelations('Transfer of Service to Account', Null, Null, 10),'Service','','',0,0,'','','select servicename from services','',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_CrossServices','ServiceNum','Прехвърлени услуги към сметка','Брой','Transfer of Service to Account','Number',2,
GetFreeNInRelations('Transfer of Service to Account', Null, Null, 10),'ServiceNum','','',0,0,'','','','',60,'',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_CrossServices','ServiceSngPrice','Прехвърлени услуги към сметка','Ед.цена','Transfer of Service to Account','Sng. Price',2,
GetFreeNInRelations('Transfer of Service to Account', Null, Null, 10),'ServiceSngPrice','','# ##0.00',0,0,'','','','',60,'',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_CrossServices','AllSum','Прехвърлени услуги към сметка','Сум','Transfer of Service to Account','Total',2,
GetFreeNInRelations('Transfer of Service to Account', Null, Null, 10),'Sum(AllSum)','Sum(qrData.Сума)','# ##0.00',1,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_CrossServices','Currency','Прехвърлени услуги към сметка','Валута','Transfer of Service to Account','Currency',2,
GetFreeNInRelations('Transfer of Service to Account', Null, Null, 10),'Currency','','',1,0,'','','','',60,'',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_CrossServices','RegDate','Прехвърлени услуги към сметка','Дата начисляване','Transfer of Service to Account','RegDate',2,
GetFreeNInRelations('Transfer of Service to Account', Null, Null, 10),'','','',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_CrossServices','ToGuestName','Прехвърлени услуги към сметка','На гост','Transfer of Service to Account','To Guest',2,
GetFreeNInRelations('Transfer of Service to Account', Null, Null, 10),'ToGuestName','','',0,0,'','','','',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_CrossServices','ToAccount','Прехвърлени услуги към сметка','Към сметка','Transfer of Service to Account','ToAccount',2,
GetFreeNInRelations('Transfer of Service to Account', Null, Null, 10),'ToAccount','','',1,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_CrossServices','AdminName','Прехвърлени услуги към сметка','Админ.','Transfer of Service to Account','Admin.',2,
GetFreeNInRelations('Transfer of Service to Account', Null, Null, 10),'AdminName','','',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_CrossServices','__OPTIONS__','Прехвърлени услуги към сметка','__OPTIONS__','Transfer of Service to Account','__OPTIONS__',1,
GetFreeNInRelations('Transfer of Service to Account', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','',1,'',0,0,1,today())

GO

if (select count(*) from InformPermission where RelationN=(
  select N from Relations where TableName='v_CrossServices'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_CrossServices'and ColName='__OPTIONS__'  ),1,1);
end if;

//[07.August.2006] - Yanko - dobavena kolona Data na nachisliavane

Go
IF (Exists(select Table_name from SysTable where LCase(Table_name)=LCase('v_CrossBaseServices'))) THEN
  Drop view v_CrossBaseServices
END IF
Go

CREATE VIEW v_CrossBaseServices
  (
    FromGuestName,
    FromAccount,
    Service,
    ServiceCount,
    Price,
    AllSum,
    Currency,
    RegDate,
    ToGuestName,
    ToAccount,
    AdminName
  )
AS SELECT
    FromGuest."Name" as FromGuestName,
    FromGuest.GuestNum as FromAccount,
    Service='Нощувка',
    Book.NumLodgings as ServiceCount,
    Book.Price as Price,
    Book.NumLodgings*(Book.Price+InsurancePrices.PriceIns) as AllSum,
    Currencies.Symbol as Currency,
    Book.DateRegDt as RegDate,
    ToGuest."Name" as ToGuestName,
    ToGuest.GuestNum as ToAccount,
    Admin.ShortName as AdminName
FROM "dba".Guests as FromGuest,"dba".Guests as ToGuest,"DBA".InsurancePrices,"DBA".Book,"DBA".admin,"DBA".Currencies
WHERE Book.DateRegDt>=bvrData1 and Book.DateRegDt<=bvrData2 and Book.Guest=FromGuest.GuestNum
    and Book.Guest<>Book.WhoPays
    and Book.WhoPays<>0
    and Book.KodCurrency=Currencies.KodCurrency
    and Book.WhoPays=ToGuest.GuestNum
    and Book.KodAdmin=Admin.AdminNo
    and InsurancePrices.HumanKind=FromGuest.HumanKind
Go

Delete from Relations where lcase(RelationTable) = lcase('Transfer of Initial Services to Account')

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_CrossBaseServices','__OPTIONS__','Прехвърлени основни услуги към сметка','__OPTIONS__','Transfer of Initial Services to Account','__OPTIONS__',1,GetFreeNInRelations('Transfer of Initial Services to Account', 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_CrossBaseServices','bvrData1','Прехвърлени основни услуги към сметка','От дата','Transfer of Initial Services to Account','From Date',2,GetFreeNInRelations('Transfer of Initial Services to Account', Null, Null, 10),'bvrData1','','',0,0,1,1,'','select bvrData1',72,'',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_CrossBaseServices','bvrData2','Прехвърлени основни услуги към сметка','До дата','Transfer of Initial Services to Account','To Date',2,GetFreeNInRelations('Transfer of Initial Services to Account', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select bvrData2',72,'',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_CrossBaseServices','FromGuestName','Прехвърлени основни услуги към сметка','От гост','Transfer of Initial Services to Account','From Guest',2,GetFreeNInRelations('Transfer of Initial Services to Account', Null, Null, 10),'FromGuestName','','',0,0,'','','select "Name" from Guests','',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_CrossBaseServices','FromAccount','Прехвърлени основни услуги към сметка','От сметка','Transfer of Initial Services to Account','From Account',2,GetFreeNInRelations('Transfer of Initial Services to Account', Null, Null, 10),'FromAccount','','',0,0,'','','select GuestNum from Guests','',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_CrossBaseServices','ServiceCount','Прехвърлени основни услуги към сметка','Брой','Transfer of Initial Services to Account','Number',0,GetFreeNInRelations('Transfer of Initial Services to Account', Null, Null, 10),'ServiceCount','','',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_CrossBaseServices','Price','Прехвърлени основни услуги към сметка','Ед. цена','Transfer of Initial Services to Account','Price',0,GetFreeNInRelations('Transfer of Initial Services to Account', Null, Null, 10),'Price','','',1,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_CrossBaseServices','AllSum','Прехвърлени основни услуги към сметка','Стойност','Transfer of Initial Services to Account','AllSum',0,GetFreeNInRelations('Transfer of Initial Services to Account', Null, Null, 10),'AllSum','','',1,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_CrossBaseServices','Currency','Прехвърлени основни услуги към сметка','Валута','Transfer of Initial Services to Account','Currency',0,GetFreeNInRelations('Transfer of Initial Services to Account', Null, Null, 10),'Currency','','',1,0,'','','','',60,'',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_CrossBaseServices','RegDate','Прехвърлени основни услуги към сметка','Дата начисляване','Transfer of Initial Services to Account','RegDate',2,GetFreeNInRelations('Transfer of Initial Services to Account', Null, Null, 10),'RegDate','','',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_CrossBaseServices','ToGuestName','Прехвърлени основни услуги към сметка','Към гост','Transfer of Initial Services to Account','To Guest',2,GetFreeNInRelations('Transfer of Initial Services to Account', Null, Null, 10),'ToGuestName','','',0,0,'','','select "Name" from Guests','',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_CrossBaseServices','ToAccount','Прехвърлени основни услуги към сметка','Към сметка','Transfer of Initial Services to Account','To Account',2,GetFreeNInRelations('Transfer of Initial Services to Account', Null, Null, 10),'ToAccount','','',0,0,'','','select GuestNum from Guests','',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_CrossBaseServices','AdminName','Прехвърлени основни услуги към сметка','Админ.','Transfer of Initial Services to Account','Admin.',2,GetFreeNInRelations('Transfer of Initial Services to Account', Null, Null, 10),'AdminName','','',0,0,'','','','',100,'',0,0,1,today())

GO

if (select count(*) from InformPermission where RelationN=(
  select N from Relations where TableName='v_CrossBaseServices'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_CrossBaseServices'and ColName='__OPTIONS__'  ),1,1);
end if;

//[07.August.2006] - Yanko - dobavena kolona Data na nachisliavane

Go

IF (Exists(select Table_name from SysTable where LCase(Table_name)=LCase('v_CrossServicesContracts'))) THEN
  Drop view v_CrossServicesContracts
END IF
Go


CREATE VIEW "DBA"."v_CrossServicesContracts"  as select FromGuestName=FromGuest."Name",
    FromAccount=FromGuest.GuestNum,
    Service=Services.ServiceName,
    ServiceNum=ServiceCredits.NumberServices,
    ServiceSngPrice=ServiceCredits.SumCredit,
    /*    ServiceSngPrice=DayRates.ExchangeRate*ServiceCredits.SumCredit,*/
    AllSum=ServiceCredits.NumberServices*ServiceSngPrice,
    Currency=Currencies.Symbol,
    RegDate=ServiceCredits.DateCreditDt,
    ToContract=Contracts.ShortName,
    AdminName=Admin.ShortName
    from "dba".ServiceCredits,"dba".Services,"dba".Guests as FromGuest,"DBA".admin,"DBA".DayRates,"DBA".Contracts,"DBA".Currencies
    where DayRates.DateDt=ServiceCredits.DateCreditDt
    and DayRates.KodCurrency=ServiceCredits.KodCurrency
    and ServiceCredits.Service=Services.ServiceNo
    and ServiceCredits.DateCreditDt>=bvrData1 and ServiceCredits.DateCreditDt<=bvrData2
    and ServiceCredits.GuestNo=FromGuest.GuestNum
    and ServiceCredits.ContractPays<>0
    and Contracts.GroupNo=any(select ShowType from "dba".GrContracts where GrNo=-1)
    and ServiceCredits.KodCurrency=Currencies.KodCurrency
    and ServiceCredits.ContractPays=Contracts.ContractNo
    and ServiceCredits.KodAdmin=Admin.AdminNo
    and FromGuest.GuestNum >0

Go

Delete from Relations where lcase(RelationTable) = lcase('Transfer of Services to Contract')

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_CrossServicesContracts','__OPTIONS__','Прехвърлени услуги към договор','__OPTIONS__','Transfer of Services to Contract','__OPTIONS__',1,GetFreeNInRelations('Transfer of Services to Contract', 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_CrossServicesContracts','bvrData1','Прехвърлени услуги към договор','От дата','Transfer of Services to Contract','From Date',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'bvrData1','','',0,0,1,1,'','select bvrData1',72,'',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_CrossServicesContracts','bvrData2','Прехвърлени услуги към договор','До дата','Transfer of Services to Contract','To Date',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select bvrData2',72,'',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_CrossServicesContracts','FromGuestName','Прехвърлени услуги към договор','От гост','Transfer of Services to Contract','From Guest',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'FromGuestName','','',0,0,'','','','',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_CrossServicesContracts','FromAccount','Прехвърлени услуги към договор','От сметка','Transfer of Services to Contract','From Account',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'FromAccount','','',0,0,'','','select GuestNum from Guests','',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_CrossServicesContracts','Service','Прехвърлени услуги към договор','Услуга','Transfer of Services to Contract','Service',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'Service','','',0,0,'','','select servicename from services','',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_CrossServicesContracts','ServiceNum','Прехвърлени услуги към договор','Брой','Transfer of Services to Contract','Number',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'ServiceNum','','',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_CrossServicesContracts','ServiceSngPrice','Прехвърлени услуги към договор','Ед.цена','Transfer of Services to Contract','Sng. Price',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'ServiceSngPrice','','',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_CrossServicesContracts','AllSum','Прехвърлени услуги към договор','Сума','Transfer of Services to Contract','Total',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'AllSum','','',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_CrossServicesContracts','Currency','Прехвърлени услуги към договор','Валута','Transfer of Services to Contract','Currency',0,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'Currency','','',1,0,'','','','',60,'',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_CrossServicesContracts','RegDate','Прехвърлени услуги към договор','Дата начисляване','Transfer of Services to Contract','RegDate',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'RegDate','','',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_CrossServicesContracts','ToContract','Прехвърлени услуги към договор','Към договор','Transfer of Services to Contract','To Conract',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'ToContract','','',0,0,'','','select ShortName from Contracts','',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_CrossServicesContracts','AdminName','Прехвърлени услуги към договор','Админ.','Transfer of Services to Contract','Admin.',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'AdminName','','',0,0,'','','','',80,'',0,0,1,today())

GO

if (select count(*) from InformPermission where RelationN=(
  select N from Relations where TableName='v_CrossServicesContracts'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_CrossServicesContracts'and ColName='__OPTIONS__'  ),1,1);
end if;

//[v. 31.10.02] - niky dobaven e filtyr za gr dogovori
//[07.August.2006] - Yanko - dobavena kolona Data na nachisliavane

Go

IF (Exists(select Table_name from SysTable where LCase(Table_name)=LCase('v_CrossBaseServicesContracts'))) THEN
  Drop view v_CrossBaseServicesContracts
END IF
Go

CREATE VIEW "DBA"."v_CrossBaseServicesContracts"  
AS SELECT 
    FromGuestName=FromGuest."Name",
    FromAccount=FromGuest.GuestNum,
    Service='Нощувка',
    ServiceCount=Book.NumLodgings,
    Price=Book.Price,
    AllSum=Book.NumLodgings*Book.Price,
    Currency=Currencies.Symbol,
    RegDate=Book.DateRegDt,
    ToContract=Contracts.ShortName,
    AdminName=Admin.ShortName
    from "dba".Guests as FromGuest,"DBA".Book,"DBA".admin,"DBA".Contracts,"DBA".Currencies
    where Book.DateRegDt>=bvrData1 and Book.DateRegDt<=bvrData2
    and Book.Guest=FromGuest.GuestNum
    and Book.ContractPays=Contracts.ContractNo
    and Contracts.GroupNo=any(select ShowType from "dba".GrContracts where GrNo=-1)
    and Book.ContractPays<>0
    and Book.KodCurrency=Currencies.KodCurrency
    and Book.KodAdmin=Admin.AdminNo

Delete from Relations where lcase(RelationTable) = lcase('Transfer of Services to Contract')

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_CrossServicesContracts','__OPTIONS__','Прехвърлени услуги към договор','__OPTIONS__','Transfer of Services to Contract','__OPTIONS__',1,GetFreeNInRelations('Transfer of Services to Contract', 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_CrossServicesContracts','bvrData1','Прехвърлени услуги към договор','От дата','Transfer of Services to Contract','From Date',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'bvrData1','','',0,0,1,1,'','select bvrData1',72,'',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_CrossServicesContracts','bvrData2','Прехвърлени услуги към договор','До дата','Transfer of Services to Contract','To Date',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select bvrData2',72,'',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_CrossServicesContracts','FromGuestName','Прехвърлени услуги към договор','От гост','Transfer of Services to Contract','From Guest',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'FromGuestName','','',0,0,'','','','',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_CrossServicesContracts','FromAccount','Прехвърлени услуги към договор','От сметка','Transfer of Services to Contract','From Account',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'FromAccount','','',0,0,'','','select GuestNum from Guests','',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_CrossServicesContracts','Service','Прехвърлени услуги към договор','Услуга','Transfer of Services to Contract','Service',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'Service','','',0,0,'','','select servicename from services','',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_CrossServicesContracts','ServiceNum','Прехвърлени услуги към договор','Брой','Transfer of Services to Contract','Number',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'ServiceNum','','',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_CrossServicesContracts','ServiceSngPrice','Прехвърлени услуги към договор','Ед.цена','Transfer of Services to Contract','Sng. Price',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'ServiceSngPrice','','',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_CrossServicesContracts','AllSum','Прехвърлени услуги към договор','Сума','Transfer of Services to Contract','Total',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'AllSum','','',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_CrossServicesContracts','Currency','Прехвърлени услуги към договор','Валута','Transfer of Services to Contract','Currency',0,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'Currency','','',1,0,'','','','',60,'',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_CrossServicesContracts','ToContract','Прехвърлени услуги към договор','Към договор','Transfer of Services to Contract','To Conract',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'ToContract','','',0,0,'','','select ShortName from Contracts','',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_CrossServicesContracts','AdminName','Прехвърлени услуги към договор','Админ.','Transfer of Services to Contract','Admin.',2,GetFreeNInRelations('Transfer of Services to Contract', Null, Null, 10),'AdminName','','',0,0,'','','','',80,'',0,0,1,today())

GO

if (select count(*) from InformPermission where RelationN=(
  select N from Relations where TableName='v_CrossServicesContracts'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_CrossServicesContracts'and ColName='__OPTIONS__'  ),1,1);
end if;
Delete from Relations where lcase(RelationTable) = lcase('Transfer of Initial Services to Contract')

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_CrossBaseServicesContracts','__OPTIONS__','Прехвърлени основни услуги към договор','__OPTIONS__','Transfer of Initial Services to Contract','__OPTIONS__',1,GetFreeNInRelations('Transfer of Initial Services to Contract', 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_CrossBaseServicesContracts','bvrData1','Прехвърлени основни услуги към договор','От дата','Transfer of Initial Services to Contract','From Date',2,GetFreeNInRelations('Transfer of Initial Services to Contract', Null, Null, 10),'bvrData1','','',0,0,1,1,'','select bvrData1',72,'',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_CrossBaseServicesContracts','bvrData2','Прехвърлени основни услуги към договор','До дата','Transfer of Initial Services to Contract','To Date',2,GetFreeNInRelations('Transfer of Initial Services to Contract', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select bvrData2',72,'',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_CrossBaseServicesContracts','FromGuestName','Прехвърлени основни услуги към договор','От гост','Transfer of Initial Services to Contract','From Guest',2,GetFreeNInRelations('Transfer of Initial Services to Contract', Null, Null, 10),'FromGuestName','','',0,0,'','','select "Name" from Guests','',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_CrossBaseServicesContracts','FromAccount','Прехвърлени основни услуги към договор','От сметка','Transfer of Initial Services to Contract','From Account',0,GetFreeNInRelations('Transfer of Initial Services to Contract', Null, Null, 10),'FromAccount','','',0,0,'','','select GuestNum from Guests','',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_CrossBaseServicesContracts','ServiceCount','Прехвърлени основни услуги към договор','Брой','Transfer of Initial Services to Contract','Number',0,GetFreeNInRelations('Transfer of Initial Services to Contract', Null, Null, 10),'ServiceCount','','',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_CrossBaseServicesContracts','Price','Прехвърлени основни услуги към договор','Ед. цена','Transfer of Initial Services to Contract','Price',2,GetFreeNInRelations('Transfer of Initial Services to Contract', Null, Null, 10),'Price','','',1,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_CrossBaseServicesContracts','AllSum','Прехвърлени основни услуги към договор','Стойност','Transfer of Initial Services to Contract','AllSum',2,GetFreeNInRelations('Transfer of Initial Services to Contract', Null, Null, 10),'AllSum','','',1,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_CrossBaseServicesContracts','Currency','Прехвърлени основни услуги към договор','Валута','Transfer of Initial Services to Contract','Currency',0,GetFreeNInRelations('Transfer of Initial Services to Contract', Null, Null, 10),'Currency','','',1,0,'','','','',60,'',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_CrossBaseServicesContracts','RegDate','Прехвърлени основни услуги към договор','Дата начисляване','Transfer of Initial Services to Contract','RegDate',2,GetFreeNInRelations('Transfer of Initial Services to Contract', Null, Null, 10),'RegDate','','',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_CrossBaseServicesContracts','ToContract','Прехвърлени основни услуги към договор','Към договор','Transfer of Initial Services to Contract','To Contract',2,GetFreeNInRelations('Transfer of Initial Services to Contract', Null, Null, 10),'ToContract','','',0,0,'','','select ShortName from Contracts','',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_CrossBaseServicesContracts','AdminName','Прехвърлени основни услуги към договор','Админ.','Transfer of Initial Services to Contract','Admin.',2,GetFreeNInRelations('Transfer of Initial Services to Contract', Null, Null, 10),'AdminName','','',0,0,'','','','',100,'',0,0,1,today())

GO

if (select count(*) from InformPermission where RelationN=(
  select N from Relations where TableName='v_CrossBaseServicesContracts'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_CrossBaseServicesContracts'and ColName='__OPTIONS__'  ),1,1);
end if;

//[v. 01.03.02]
//[v. 31.10.02] niky - dobaven e filtyr za grupi dogovori
//[07.August.2006] - Yanko - dobavena kolona Data na nachisliavane

Go

IF (Exists(select Table_name from SysTable where LCase(Table_name)=LCase('v_TouristsWithTur'))) THEN
  Drop view v_TouristsWithTur
END IF
Go

CREATE VIEW "DBA"."v_TouristsWithTur"
AS SELECT
    RoomTypeNo=Cl.Class,
    RoomType=Cl.ShortName,
    RoomNo=Rooms.RoomNum,
    Account=Guests.GuestNum,
    Agent=Contracts.ShortName,
    CarNumber=Guests.CarNumber,
    Safe=Guests.SafeNo,
    Ime=Guests."Name",
    BirthDate=Guests.BirthDateDt,
    ReserveNo=Rsv.ReserveNo,
    Elderly=if Guests.AdultType=0 then 1 else 0 endif,
    Children=if Guests.AdultType>0 then 1 else 0 endif,
    PriceKind=Book.PriceKind,
    ArriveDate=ASL.ArriveDate,
    LeaveDate=ASL.LeaveDate,
    NightsToStay=ASL.NumNights,
    PriceNight=Book.Price,
    AllSuma=NightsToStay*PriceNight,
    Balance=Guests.Balance,
    VaucherNo=Guests.VaulcherNo
    from "DBA".Reserve as Rsv,"DBA".Classes as Cl,"dba".Contracts
    ,"dba".Guests,"DBA".Book,"DBA".Rooms,"DBA".v_ArriveStayLeaveGuestBook as ASL
    where Book.DateRegDt<=bvrData2 and Book.DateRegDt+Book.NumLodgings>bvrData2
    and Book.Room=Rooms.RoomNum and Rooms.RoomCat=Cl.Class and Book.Guest=Guests.GuestNum
    and Guests.Reservation*=Rsv.ReserveNo
    and Guests.Contract is not null and Guests.Contract=Contracts.ContractNo
    and Contracts.GroupNo=any(select ShowType from "dba".GrContracts where GrNo=-1)
    and Book.Guest=ASL.GuestNum

Delete from Relations where lcase(RelationTable) = lcase('Tourists by Tour-Operators')

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_TouristsWithTur','__OPTIONS__','Туристи чрез тур-оператори','__OPTIONS__','Tourists by Tour-Operators','__OPTIONS__',1,GetFreeNInRelations('Tourists by Tour-Operators', 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_TouristsWithTur','bvrData2','Туристи чрез тур-оператори','Дата','Tourists by Tour-Operators','Date',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'RoomType','','',0,0,1,1,'','select current date',72,'',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_TouristsWithTur','RoomType','Туристи чрез тур-оператори','Тип стая','Tourists by Tour-Operators','Room Type',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'RoomType','','',0,0,'','','select shortname from Classes','',60,'',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_TouristsWithTur','RoomNo','Туристи чрез тур-оператори','Стая No','Tourists by Tour-Operators','Room #',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'RoomNo','','',0,0,'','','select RoomNum from Rooms','',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_TouristsWithTur','Account','Туристи чрез тур-оператори','Кл.номер','Tourists by Tour-Operators','Client Number',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'Account','','',0,0,'','','','',60,'',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_TouristsWithTur','Agent','Туристи чрез тур-оператори','Агент','Tourists by Tour-Operators','Agent',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'Agent','','',0,0,'','','select shortname from contracts','',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_TouristsWithTur','CarNumber','Туристи чрез тур-оператори','Кола No','Tourists by Tour-Operators','Car Number',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'CarNumber','','',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_TouristsWithTur','Safe','Туристи чрез тур-оператори','Сейф','Tourists by Tour-Operators','Safe',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'Safe','','',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_TouristsWithTur','Ime','Туристи чрез тур-оператори','Име','Tourists by Tour-Operators','Name',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'Ime','','',0,0,'','','select Name from guests','',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_TouristsWithTur','BirthDate','Туристи чрез тур-оператори','Дата раждане','Tourists by Tour-Operators','BirthDate',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'BirthDate','','',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('v_TouristsWithTur','ReserveNo','Туристи чрез тур-оператори','Резер.No','Tourists by Tour-Operators','Reserv. #',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'ReserveNo','','',0,0,'','','','',60,'',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_TouristsWithTur','Elderly','Туристи чрез тур-оператори','В','Tourists by Tour-Operators','A',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'Sum(Elderly)','Sum(qrData.В)','',1,0,'','','','',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_TouristsWithTur','Children','Туристи чрез тур-оператори','Д','Tourists by Tour-Operators','K',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'Sum(Children)','Sum(qrData.Д)','',1,0,'','','','',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_TouristsWithTur','ArriveDate','Туристи чрез тур-оператори','Пристига','Tourists by Tour-Operators','Arrive',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'ArriveDate','','',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_TouristsWithTur','LeaveDate','Туристи чрез тур-оператори','Заминава','Tourists by Tour-Operators','Check Out',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'LeaveDate','','',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_TouristsWithTur','NightsToStay','Туристи чрез тур-оператори','Нощи','Tourists by Tour-Operators','Nights',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'Sum(NightsToStay)','Sum(qrData.Нощи)','',1,0,'','','','',60,'',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_TouristsWithTur','PriceNight','Туристи чрез тур-оператори','Ед.Цена','Tourists by Tour-Operators','PriceNight',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'PriceNight','','',0,0,'','','','',90,'',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_TouristsWithTur','AllSuma','Туристи чрез тур-оператори','ОБЩА СУМА','Tourists by Tour-Operators','AllSuma',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'AllSuma','','',0,0,'','','','',90,'',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_TouristsWithTur','Balance','Туристи чрез тур-оператори','Дължима сума','Tourists by Tour-Operators','Balance',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'Balance','','',0,0,'','','','',90,'',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_TouristsWithTur','VaucherNo','Туристи чрез тур-оператори','Ваучер No','Tourists by Tour-Operators','VaucherNo',2,GetFreeNInRelations('Tourists by Tour-Operators', Null, Null, 10),'VaucherNo','','',0,0,0,0,'','',70,'',0,0,1,today())

GO

if (select count(*) from InformPermission where RelationN=(
  select N from Relations where TableName='v_TouristsWithTur'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_TouristsWithTur'and ColName='__OPTIONS__'  ),1,1);
end if;

//[v 01.06.04]
//[v 31.10.02] niky - dobaven e filtyr za grupa dogovori
//[v 14.07.2003 ] Jorko dobaveni 3 koloni Price, AllSuma, i Balance
//[v 28.01.2004] Jorko opraveni vuzrastite na decata
//[v 07.August.2006] - Yanko - dobavena kolona za data na rajdane

Go

ALTER 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
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))

// Yanko +
UNION ALL

SELECT
  GuestNum=B.Guest,
  GuestName=G."Name",
  Qty=(B.NumLodgings-B.PayedLodgings),
  Price=string(B.Price,' ',Crs.Symbol),
  PriceLv=B.Price*Crs.ExchangeRate,
  Total=PriceLv*Qty,
  RoomNo=B.Room,
  ServiceName='Нощувка',
  G.Note,
  DateService=B.DateRegDt
FROM "dba".Book as B, "dba".Guests as G, "dba".Currencies as CrS
WHERE B.WhoPays=G.GuestNum
  and B.KodCurrency=CrS.KodCurrency
  and B.PayedLodgings <> B.NumLodgings
  and B.Price<>0
  and G.Status=2
// Yanko -

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
//[07.August.2006] - Yanko - dobaveni noshtuvki