IF (select count(sc.table_id) from sys.syscolumn as sc == sys.systable as st where LCase(st.table_name)= LCase('Invoices') and LCase(sc.column_name)= LCase('ExternalNo') ) = 0 then alter table Invoices add "ExternalNo" varchar(20); END IF; GO for A as @ACurs scroll cursor for select InvoiceNo as @InvoiceNo from Invoices do update Invoices set ExternalNo=@InvoiceNo where InvoiceNo=@InvoiceNo; end for; GO Delete from Relations where lcase(RelationTable) = lcase('Issued Accounts by Service') 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_ProdajbiPoUslugi','__OPTIONS__','Издадени сметки по услуги','__OPTIONS__','Issued Accounts by Service','__OPTIONS__',1,GetFreeNInRelations('Issued Accounts by Service', 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_ProdajbiPoUslugi','bvrData1','Издадени сметки по услуги','От дата','Issued Accounts by Service','bvrData1',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'bvrData1','','',1,0,1,1,'','select bvrData1',56,'',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_ProdajbiPoUslugi','bvrData2','Издадени сметки по услуги','До дата','Issued Accounts by Service','bvrData2',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'bvrData2','','',1,0,1,1,'','select bvrData2',56,'',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_ProdajbiPoUslugi','OtchetNumber','Издадени сметки по услуги','Отчет N','Issued Accounts by Service','OtchetNumber',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'OtchetNumber','','',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_ProdajbiPoUslugi','SmetkaNo','Издадени сметки по услуги','С-ка No','Issued Accounts by Service','Account #',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'SmetkaNo','','',1,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_ProdajbiPoUslugi','FakturaNo','Издадени сметки по услуги','Ф-ра No','Issued Accounts by Service','Invoice Number',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'FakturaNo','','',1,0,'','','','',56,'',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_ProdajbiPoUslugi','DokData','Издадени сметки по услуги','Дата','Issued Accounts by Service','Date',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'DokData','','',0,0,'','','','',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_ProdajbiPoUslugi','DokTime','Издадени сметки по услуги','Час','Issued Accounts by Service','Time',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'DokTime','','',0,0,'','','','',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_ProdajbiPoUslugi','Service','Издадени сметки по услуги','Услуга','Issued Accounts by Service','Service',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Service','','',0,0,'','','select ServiceName from Services union (select ''Нощувка'') union (select ''Застраховка'') ','',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_ProdajbiPoUslugi','Kol','Издадени сметки по услуги','Кол.','Issued Accounts by Service','Quant.',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'SUM(Kol)','SUM(qrData.Кол.)','0.##',1,0,'','','','',56,'',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_ProdajbiPoUslugi','EdCena','Издадени сметки по услуги','Ед.цена','Issued Accounts by Service','Sng. Price',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'EdCena','','# ### ##0.00',1,0,'','','','',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_ProdajbiPoUslugi','Stoinost','Издадени сметки по услуги','Стойност','Issued Accounts by Service','Value',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'SUM(Stoinost)','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_ProdajbiPoUslugi','StoinostBDDS','Издадени сметки по услуги','Стн. б.ДДС','Issued Accounts by Service','Value (no VAT)',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'SUM(StoinostBDDS)','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_ProdajbiPoUslugi','DDS','Издадени сметки по услуги','ДДС','Issued Accounts by Service','DDS',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'SUM(DDS)','SUM(qrData.ДДС)','# ### ##0.00',0,0,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_ProdajbiPoUslugi','Klient','Издадени сметки по услуги','Клиент','Issued Accounts by Service','Client',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Klient','','',0,0,'','','SELECT Whom FROM Invoices GROUP BY Whom','',160,'',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_ProdajbiPoUslugi','GuestNum','Издадени сметки по услуги','Кл.номер','Issued Accounts by Service','Client Number',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'GuestNum','','',1,0,'','','','',56,'',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_ProdajbiPoUslugi','GuestName','Издадени сметки по услуги','Име гост','Issued Accounts by Service','Guest Name',2,GetFreeNInRelations('Issued Accounts by Service', 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_ProdajbiPoUslugi','ContractName','Издадени сметки по услуги','Фирма','Issued Accounts by Service','ContractName',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'ContractName','','',1,0,'','','select ShortName from Contracts','',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_ProdajbiPoUslugi','Country','Издадени сметки по услуги','Държава','Issued Accounts by Service','Country',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Country','','',1,0,'','','select Description from Countries where Active=1','',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_ProdajbiPoUslugi','Komentar','Издадени сметки по услуги','Коментар','Issued Accounts by Service','Comments',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Komentar','','',0,0,'','','','',170,'',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_ProdajbiPoUslugi','Operator','Издадени сметки по услуги','Съставил','Issued Accounts by Service','Signed By',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Operator','','',0,0,'','','SELECT ShortName FROM ADMIN','',110,'',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_ProdajbiPoUslugi','Nationality','Издадени сметки по услуги','Националност','Issued Accounts by Service','Nationality',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Nationality','','',0,0,'','','SELECT Description FROM HumanKinds','',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_ProdajbiPoUslugi','RoomNo','Издадени сметки по услуги','Стая No','Issued Accounts by Service','RoomNo',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'RoomNo','','',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_ProdajbiPoUslugi','Discount','Издадени сметки по услуги','Отстъпка','Issued Accounts by Service','Discount',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Discount','','',1,0,'','','','',55,'',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_ProdajbiPoUslugi','EdCenaWithDisc','Издадени сметки по услуги','Цена с отстъпка','Issued Accounts by Service','EdCenaWithDisc',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'EdCenaWithDisc','','# ### ##0.00',1,0,'','','','',70,'',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_ProdajbiPoUslugi','StoinostWithDisc','Издадени сметки по услуги','Сума с отстъпка','Issued Accounts by Service','StoinostWithDisc',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'StoinostWithDisc','','# ### ##0.00',1,0,'','','','',70,'',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_ProdajbiPoUslugi','DDSTypeSt','Издадени сметки по услуги','ДДС Тип','Issued Accounts by Service','DDSTypeSt',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'DDSTypeSt','','',0,0,0,0,'select ''20%'' union all select ''7%'' ','',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_ProdajbiPoUslugi','Fiscal','Издадени сметки по услуги','Фискализирана','Issued Accounts by Service','Fiscal',2,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'Fiscal','','',0,0,0,0,'select ''Да'' union all select ''Не'' ','',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_ProdajbiPoUslugi','ServiceName','Издадени сметки по услуги','Име услуга','Issued Accounts by Service','ServiceName',0,GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),'ServiceName','','',0,0,'','','select ServiceName from Services','',100,'',0,0,1,today()) Go delete from InformPermission where RelationN=( select N from Relations where TableName='v_ProdajbiPoUslugi'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_ProdajbiPoUslugi'and ColName='__OPTIONS__' ),GrNo,1); end if; end for; GO Delete from Relations where lcase(RelationTable) = lcase('Issued Accounts') 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_IzdadeniSmetki','__OPTIONS__','Издадени сметки','__OPTIONS__','Issued Accounts','__OPTIONS__',1,GetFreeNInRelations('Issued Accounts', 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_IzdadeniSmetki','SmetkaNo','Издадени сметки','С-ка No','Issued Accounts','Account #',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'SmetkaNo','','',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_IzdadeniSmetki','FakturaNo','Издадени сметки','Ф-ра No','Issued Accounts','Invoice Number',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'FakturaNo','','',1,0,'','','','',56,'',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_IzdadeniSmetki','DokData','Издадени сметки','Дата','Issued Accounts','Date',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'DokData','','',0,0,'','','','',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_IzdadeniSmetki','DokTime','Издадени сметки','Час','Issued Accounts','Time',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'DokTime','','',0,0,'','','','',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_IzdadeniSmetki','SumaBDDS','Издадени сметки','Стн. б.ДДС','Issued Accounts','Value (no VAT)',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'SUM(SumaBDDS)','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_IzdadeniSmetki','DDS','Издадени сметки','ДДС','Issued Accounts','VAT',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'SUM(DDS)','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_IzdadeniSmetki','Suma','Издадени сметки','Стойност','Issued Accounts','Value',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'SUM(Suma)','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_IzdadeniSmetki','PlatVBroi','Издадени сметки','В брой','Issued Accounts','In Cash',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'SUM(PlatVBroi)','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_IzdadeniSmetki','PlatPoSmetka','Издадени сметки','По сметка','Issued Accounts','On Account',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'SUM(PlatPoSmetka)','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_IzdadeniSmetki','Plateni','Издадени сметки','Общо пл.','Issued Accounts','Total Paid',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'SUM(Plateni)','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_IzdadeniSmetki','Klient','Издадени сметки','Клиент','Issued Accounts','Client',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'Klient','','',0,0,'','','SELECT Whom FROM Invoices GROUP BY Whom','',160,'',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_IzdadeniSmetki','DanNo','Издадени сметки','ЕИН','Issued Accounts','Tax #',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'DanNo','','',0,0,'','','SELECT Whom FROM Invoices GROUP BY Whom','',88,'',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_IzdadeniSmetki','Operator','Издадени сметки','Съставил','Issued Accounts','Signed By',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'Operator','','',0,0,'','','SELECT ShortName FROM ADMIN','',110,'',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_IzdadeniSmetki','DDSTypeSt','Издадени сметки','ДДС Тип','Issued Accounts','DDSTypeSt',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'DDSTypeSt','','',0,0,0,0,'select ''20%'' union all select ''7%'' ','',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_IzdadeniSmetki','Fiscal','Издадени сметки','Фискализирана','Issued Accounts','Fiscal',2,GetFreeNInRelations('Issued Accounts', Null, Null, 10),'Fiscal','','',0,0,0,0,'select ''Да'' union all select ''Не''','',60,'',0,0,1,today()) Go delete from InformPermission where RelationN=( select N from Relations where TableName='v_IzdadeniSmetki'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_IzdadeniSmetki'and ColName='__OPTIONS__' ),GrNo,1); end if; end for; GO Delete from Relations where lcase(RelationTable) = lcase('Payment Report') 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_PayTypes','__OPTIONS__','Отчет плащания','__OPTIONS__','Payment Report','__OPTIONS__',1,GetFreeNInRelations('Payment Report', 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_PayTypes','bvrData1','Отчет плащания','От дата','Payment Report','From Date',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'bvrData1','','',1,0,1,1,'','select Date(Now())-1',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_PayTypes','bvrData2','Отчет плащания','До дата','Payment Report','To Date',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'bvrData2','','',1,0,1,1,'','select Date(Now())',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_PayTypes','DataPay','Отчет плащания','Дата','Payment Report','Date',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'DataPay','','',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_PayTypes','PayType','Отчет плащания','Начин пл.','Payment Report','Type Of Payment',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'PayType','','',0,0,'','','select PayTypeName from PayTypes','',104,'',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_PayTypes','DocNomer','Отчет плащания','Номер на документ','Payment Report','Document Number',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'DocNomer','','',1,0,'','','','',110,'',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_PayTypes','Smetka','Отчет плащания','Сметка N','Payment Report','Invoice Number',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'Smetka','','',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_PayTypes','Fiscal','Отчет плащания','Фискализирана','Payment Report','Fiscal',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'Fiscal','','',1,2,'','',' select ''Не'' union all select ''Да'' ','',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_PayTypes','PTVidName','Отчет плащания','Вид','Payment Report','Вид',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'PTVidName','','',1,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_PayTypes','Valuta','Отчет плащания','Валута','Payment Report','Currency',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'Valuta','','',1,0,'','','SELECT Symbol FROM Currencies','',64,'',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_PayTypes','AllSumVlt','Отчет плащания','Сума влт','Payment Report','Total Currency',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'Sum(AllSumVlt)','','# ### ##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_PayTypes','ExchangeRate','Отчет плащания','Курс','Payment Report','Курс',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'ExchangeRate','','# ##0.0000',1,0,'','','','',64,'',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_PayTypes','AllSum','Отчет плащания','Сума лв','Payment Report','Total LC',2,GetFreeNInRelations('Payment Report', Null, Null, 10),'Sum(AllSum)','SUM(qrData.Сума лв)','# ### ##0.00',1,0,'','','','',80,'',0,0,1,today()) Go delete from InformPermission where RelationN=( select N from Relations where TableName='v_PayTypes'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_PayTypes'and ColName='__OPTIONS__' ),GrNo,1); end if; end for; 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_ReportServices','__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_ReportServices','bvrData1','Отчет услуги','От дата','Отчет услуги','От дата',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'bvrData1','','',0,0,1,1,'','select bvrData1',64,'',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_ReportServices','bvrData2','Отчет услуги','До дата','Отчет услуги','До дата',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select bvrData2',64,'',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_ReportServices','InvoiceDateDt','Отчет услуги','Дата','Отчет услуги','Дата',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'InvoiceDateDt','','',0,0,'','','','',64,'',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_ReportServices','InvoiceNo','Отчет услуги','Сметка','Отчет услуги','Сметка',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'InvoiceNo','','',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_ReportServices','Fiscal','Отчет услуги','Фискализирана','Отчет услуги','Фискализирана',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'Fiscal','','',0,0,'','',' select ''Не'' union all select ''Да'' ','',64,'',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_ReportServices','Service','Отчет услуги','Услуга','Отчет услуги','Услуга',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'Service','','',0,101,'','','select distinct Service from v_ReportServices','',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_ReportServices','Qty','Отчет услуги','Бр.услуги','Отчет услуги','Qty',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'Sum(Qty)','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_ReportServices','Suma','Отчет услуги','Сума','Отчет услуги','Сума',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'Sum(Suma)','SUM(qrData.Сума)','# ### ##0.00',1,0,'','','','',64,'',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_ReportServices','PayTypeName','Отчет услуги','Начин на плащане','Отчет услуги','PayTypeName',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'PayTypeName','','',0,0,'','','select PayTypeName from PayTypes','',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_ReportServices','FullName','Отчет услуги','Администратор','Отчет услуги','Администратор',2,GetFreeNInRelations('Отчет услуги', Null, Null, 10),'FullName','','',1,0,'','','','',200,'',0,0,1,today()) Go delete from InformPermission where RelationN=( select N from Relations where TableName='v_ReportServices'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_ReportServices'and ColName='__OPTIONS__' ),GrNo,1); end if; end for; GO IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('PrepareAddrCard'))) THEN Drop procedure PrepareAddrCard END IF GO create procedure DBA.PrepareAddrCard(in @Gno integer) begin declare @MonthsGreek varchar(50); declare i integer; declare @BroiPayments integer; declare j integer; declare sqlsttm varchar(200); declare @InvNo varchar(20); set @BroiPayments=(select count(*) from Book as B,Articles as A,Invoices as Inv,Currencies as C where B.WhoPays = @Gno and B.KodCurrency = C.KodCurrency and B.RegNum = A.RegNo and A.InvoiceNo = Inv.InvoiceNo and Inv.CancelInvoiceNo = 0); set i=1; set @MonthsGreek=' I II III IV V VI VIIVIII IX X XI XII'; delete from #AddrCardPayments; while i <= 48 loop insert into #AddrCardPayments( Mes,MesReal) values( Trim(SUBSTR(@MonthsGreek,i,4)),i/4+1) ; set i=i+4 end loop; set j=1; for f1 as curs1 scroll cursor for select if TipDDS = 2 then SUM(A.Price*(1+bvrDDSPart)) else SUM(A.Price) endif as Price,cast(SRS.Stoinost as integer) as Mnth,cast(SRS1.Stoinost as integer) as Yr,GetInvoiceNumbers(@GNo,Mnth,Yr) as INo from Book as B,Articles as A,Invoices as Inv,Currencies as C,SvoRedoveStn as SRS,SvoRedoveStn as SRS1 where B.WhoPays = @Gno and B.KodCurrency = C.KodCurrency and B.RegNum = A.RegNo and A.InvoiceNo = Inv.InvoiceNo and Inv.CancelInvoiceNo = 0 and SRS.ArticleNo = A.ArticleNo and SRS1.ArticleNo = A.ArticleNo and SRS.InvoiceNo = A.InvoiceNo and SRS1.InvoiceNo = A.InvoiceNo and SRS.Svoistvo = 1 and SRS1.Svoistvo = 2 group by TipDDS,INo,Mnth,Yr order by Yr asc,Mnth asc do set i=Mnth; select first ExternalNo into @InvNo from Invoices as Inv1 where Inv1.InvoiceNo = INo; set sqlsttm='update #AddrCardPayments set lvUG' || j || '=' || Round(Price,2) || ',kvUG' || j || '=''' || @InvNo || ''' where MesReal=' || i; execute immediate sqlsttm; if i = 12 then set j=j+1 end if end for end GO Delete from Relations where lcase(RelationTable) = lcase('Arrive guests per date') 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_ArrivingForDate','__OPTIONS__','Настанени студенти за дата','__OPTIONS__','Arrive guests per date','__OPTIONS__',1,GetFreeNInRelations('Arrive guests per date', 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_ArrivingForDate','bvrData1','Настанени студенти за дата','Дата','Arrive guests per date','Date',2,GetFreeNInRelations('Arrive guests per date', Null, Null, 10),'bvrData1','','',1,0,1,1,'','select date(now())',80,'',0,0,'',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_ArrivingForDate','GuestName','Настанени студенти за дата','Гост','Arrive guests per date','Guest',2,GetFreeNInRelations('Arrive guests per date', Null, Null, 10),'GuestName','','',1,0,'','','select "Name" from Guests','',80,'',0,0,'',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_ArrivingForDate','Country','Настанени студенти за дата','Държава','Arrive guests per date','Country',2,GetFreeNInRelations('Arrive guests per date', Null, Null, 10),'Country','','',1,0,'','','','',100,'',0,0,'',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_ArrivingForDate','RoomNum','Настанени студенти за дата','Стая No','Arrive guests per date','Room #',2,GetFreeNInRelations('Arrive guests per date', Null, Null, 10),'RoomNum','','',1,1,'','','select RoomName from Rooms where Status <> 1 and Status <> 3','',80,'',0,0,'',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_ArrivingForDate','NumNight','Настанени студенти за дата','Бр.нощ.','Arrive guests per date','Sleep Nmb',2,GetFreeNInRelations('Arrive guests per date', Null, Null, 10),'Max(NumNight)','','',1,0,'','','','',80,'',0,0,'',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_ArrivingForDate','PriceNight','Настанени студенти за дата','Ед.цена.','Arrive guests per date','Price',2,GetFreeNInRelations('Arrive guests per date', Null, Null, 10),'PriceNight','','',1,0,'','','','',80,'',0,0,'',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_ArrivingForDate','SumLodgings','Настанени студенти за дата','Общо нощ.','Arrive guests per date','SumLodgings',2,GetFreeNInRelations('Arrive guests per date', Null, Null, 10),'SumLodgings','','# ### ##0.00',1,0,'','','','',80,'',0,0,'',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_ArrivingForDate','SumNoPayed','Настанени студенти за дата','Баланс','Arrive guests per date','SumNoPayed',2,GetFreeNInRelations('Arrive guests per date', Null, Null, 10),'SumNoPayed','','# ### ##0.00',1,0,'','','','',80,'',0,0,'',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_ArrivingForDate','SumPayedLodgings','Настанени студенти за дата','Платени','Arrive guests per date','Paid',2,GetFreeNInRelations('Arrive guests per date', Null, Null, 10),'Sum(SumPayedLodgings)','','# ### ##0.00',1,0,'','','','',80,'',0,0,'',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_ArrivingForDate','InvNo','Настанени студенти за дата','Сметка No','Arrive guests per date','InvNo',2,GetFreeNInRelations('Arrive guests per date', Null, Null, 10),'InvNo','','',1,0,'','','','',80,'',0,0,'',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_ArrivingForDate','VaucherNo','Настанени студенти за дата','Ваучер No','Arrive guests per date','VaucherNo',0,GetFreeNInRelations('Arrive guests per date', Null, Null, 10),'VaucherNo','','',1,0,'','','','',60,'',0,0,0,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_ArrivingForDate','Agent','Настанени студенти за дата','Договор','Arrive guests per date','Agent',2,GetFreeNInRelations('Arrive guests per date', Null, Null, 10),'Agent','','',1,0,'','','','',80,'',0,0,'',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_ArrivingForDate','Admin','Настанени студенти за дата','Админ','Arrive guests per date','Admin',2,GetFreeNInRelations('Arrive guests per date', Null, Null, 10),'Admin','','',1,0,'','','','',150,'',0,0,'',today()) Go delete from InformPermission where RelationN=( select N from Relations where TableName='v_ArrivingForDate'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_ArrivingForDate'and ColName='__OPTIONS__' ),GrNo,1); end if; end for; GO IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_ArrivingForDate'))) THEN Drop view v_ArrivingForDate END IF GO create view dba.v_ArrivingForDate as select GuestNum=Guests.GuestNum, GuestName=Guests.Name, --Maria --RoomNum=Bk1.Room, RoomNum=R.RoomName, --Maria ArriveDate=BK2.ArriveDate, LeaveDate=BK2.LeaveDate, NumNight=BK2.NumNights, PriceNight=string(BK1.Price,' ',Currencies.Symbol), SumLodgings=BK2.NumNights*BK1.Price*DayRates.ExchangeRate, SumPayedLodgings=0, SumNoPayed=(SumLodgings-SumPayedLodgings), Country=Countries.Description, Admin=a.FullName, Agent=c.ShortName, InvNo=null, VaucherNo=Guests.VaulcherNo from DBA.Book as BK1,dba.v_ArriveStayLeaveGuestBook as BK2,DBA.Guests,dba.DayRates, --Maria dba.Rooms as R, --Maria dba.Currencies,dba.Countries,dba.Admin as a,dba.Contracts as c where --Maria BK1.Room = R.RoomNum and --Maria BK1.Guest = Guests.GuestNum and BK2.GuestNum = Guests.GuestNum and BK1.KodCurrency = Currencies.KodCurrency and BK1.Staying = 1 and bvrData1 >= BK1.DateRegDt and bvrData1 < Days(BK1.DateRegDt,BK1.NumLodgings) and DayRates.DateDt = bvrData1 and DayRates.KodCurrency = BK1.KodCurrency and Guests.NativeCountry = Countries.CountryKod and BK1.KodAdmin = a.AdminNo and BK2.ArriveDate = bvrData1 and Guests.Contract = c.ContractNo and //(select Count(a.QTY) from dba.Articles as a,dba.Invoices as I where a.InvoiceNo = I.InvoiceNo and I.whom = Guests.Name) = 0 and BK1.NumLodgings <> BK1.PayedLodgings union all select GuestNum=Guests.GuestNum, GuestName=Guests.Name, --Maria --RoomNum=Bk1.Room, RoomNum=R.RoomName, --Maria ArriveDate=BK2.ArriveDate, LeaveDate=BK2.LeaveDate, NumNight=BK2.NumNights, PriceNight=string(BK1.Price,' ',Currencies.Symbol), SumLodgings=BK2.NumNights*BK1.Price*DayRates.ExchangeRate, SumPayedLodgings=DBA.GetPayedLodgingsWithInvoice(I.InvoiceNo,BK1.Price,DayRates.ExchangeRate,BK1.RegNum), SumNoPayed=DBA.GetUnpayedLodgings(I.InvoiceNo,BK1.RegNum,BK1.NumLodgings,BK1.Price,DayRates.ExchangeRate), /* ((select Qty from "dba".Articles where InvoiceNo=I.InvoiceNo and ServiceNo in(-1,-2)))*BK1.Price*DayRates.ExchangeRate as SumPayedLodgings,*/ /* (NumLodgings-(select Sum(a.Qty) from "dba".Articles as a,"dba".Invoices as Inv where a.InvoiceNo=Inv.InvoiceNo and a.ServiceNo in(-1,-2) and Inv.InvoiceNo<=I.InvoiceNo and Inv.whom=Guests."Name"))*BK1.Price*DayRates.ExchangeRate as SumNoPayed,*/ Country=Countries.Description, Admin=a.FullName, Agent=c.ShortName, InvNo=(select first ExternalNo from Invoices Inv1 where Inv1.InvoiceNo=I.InvoiceNo), VaucherNo=Guests.VaulcherNo from DBA.Book as BK1,dba.v_ArriveStayLeaveGuestBook as BK2,DBA.Guests,dba.DayRates, --Maria dba.Rooms as R, --Maria dba.Currencies,dba.Countries,dba.Admin as a,dba.Contracts as c,dba.Invoices as I,dba.articles as ar where --Maria BK1.Room = R.RoomNum and --Maria BK1.Guest = Guests.GuestNum and BK2.GuestNum = Guests.GuestNum and BK1.KodCurrency = Currencies.KodCurrency and BK1.Staying = 1 and bvrData1 >= BK1.DateRegDt and bvrData1 < Days(BK1.DateRegDt,BK1.NumLodgings) and DayRates.DateDt = bvrData1 and DayRates.KodCurrency = BK1.KodCurrency and Guests.NativeCountry = Countries.CountryKod and BK1.KodAdmin = a.AdminNo and BK2.ArriveDate = bvrData1 and Guests.Contract = c.ContractNo and ar.InvoiceNo = I.InvoiceNo and ar.RegNo = BK1.RegNum // and BK1.NumLodgings<>BK1.PayedLodgings GO IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_IzdadeniSmetki'))) THEN Drop view v_IzdadeniSmetki END IF GO create view DBA.v_IzdadeniSmetki as select SmetkaNo=(select first ExternalNo from Invoices Inv1 where Inv1.InvoiceNo=Inv.InvoiceNo), RealSmetkaNo=Inv.InvoiceNo, FakturaNo=if((Inv.TrueInvoiceNo <> 0) and(Inv.TrueInvoiceNo < 1000000000)) then Inv.MaskaFaktura || substr('0000000000',1,9-length(Inv.TrueInvoiceNo)) || Inv.TrueInvoiceNo else convert(varchar,Inv.TrueInvoiceNo) endif, DokData=Inv.InvoiceDateDt, --Maria +, 1975 DokTime=DateFormat(Inv.TrueInvoiceHour,'hh:mm'), --Maria - Klient=Inv.Whom, DanNo=Inv.Bulstat, Operator=Adm.ShortName, SumaBDDS=round(Inv.TotalNoVAT+Inv.WithoutVAT,4), DDS=round(Inv.VAT,4), Suma=round(Inv.GrandTotal,4), PlatVBroi=round(Pari.VBroi,4), PlatPoSmetka=round(Pari.PoSmetka,4), Plateni=round(Pari.VBroi+Pari.PoSmetka,4), DDSTypeSt=(if Inv.DDSType = 1 then '20%' else '7%' endif), --Maria +, 1982 Fiscal=(if Inv.Fiscal = 0 then 'Не' else 'Да' endif) from --Maria - DBA.Invoices as Inv,DBA.v_PariPoSmetki as Pari,DBA.Admin as Adm where Inv.InvoiceNo = Pari.SmetkaNo and Inv.KodAdmin = Adm.AdminNo union all select SmetkaNo=(select first ExternalNo from Invoices Inv1 where Inv1.InvoiceNo=Inv.VaucherInvoiceNo), RealSmetkaNo=Inv.VaucherInvoiceNo, FakturaNo=if((Inv.TrueInvoiceNo <> 0) and(Inv.TrueInvoiceNo < 1000000000)) then Inv.MaskaFaktura || substr('0000000000',1,9-length(Inv.TrueInvoiceNo)) || Inv.TrueInvoiceNo else convert(varchar,Inv.TrueInvoiceNo) endif, DokData=Inv.InvoiceDateDt, --Maria +, 1975 DokTime=DateFormat(Inv.TrueInvoiceHour,'hh:mm'), --Maria - Klient=Inv.Whom, DanNo=Inv.Bulstat, Operator=Adm.ShortName, SumaBDDS=round(Inv.TotalNoVAT+Inv.WithoutVAT,4), DDS=round(Inv.VAT,4), Suma=round(Inv.GrandTotal,4), PlatVBroi=round(Pari.VBroi,4), PlatPoSmetka=round(Pari.PoSmetka,4), Plateni=round(Pari.VBroi+Pari.PoSmetka,4),DDSTypeSt='7%', --Maria +, 1982 Fiscal=(if Inv.Fiscal = 0 then 'Не' else 'Да' endif) from --Maria - DBA.VaucherInvoices as Inv,DBA.v_VtdPariPoSmetki as Pari,DBA.Admin as Adm where Inv.VaucherInvoiceNo = Pari.SmetkaNo and Inv.KodAdmin = Adm.AdminNo and( (select IntStojnost from DBA.Registers where RegisterName = 'VTDCommonCounter') = 0) order by RealSmetkaNo -- gnikolov - добавено съобразяване с регистър VTDCommonCounter поръчка 2008 --[30.07.2009] - Yoan - Izchislqva pravilno SumaBDDS GO IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_ProdajbiPoUslugi'))) THEN Drop view v_ProdajbiPoUslugi END IF GO create view DBA.v_ProdajbiPoUslugi as select OtchetNumber=DBA.GetOtchetNumber(ART.InvoiceNo), SmetkaNo=(select first ExternalNo from Invoices Inv1 where Inv1.InvoiceNo=ART.InvoiceNo), FakturaNo=if((INV.TrueInvoiceNo <> 0) and(INV.TrueInvoiceNo < 1000000000)) then INV.MaskaFaktura || substr('0000000000',1,9-length(INV.TrueInvoiceNo)) || INV.TrueInvoiceNo endif,DokData=INV.InvoiceDateDt, --Maria +, 1975 DokTime=DateFormat(INV.TrueInvoiceHour,'hh:mm'), --Maria - Service=PK.Description, --Dobrin 2195 - vmesto ot Services da vzima imeto na Cenorazpisa /*Kol=if INV.CancelInvoiceNo>0 then(-1)*ART.Qty else ART.Qty endif,*/ /*Kol=if (ART.totalsum < 0 ) then ((-1)*ART.Qty) else ART.Qty endif,*/ Kol=if(ART.totalsum > 0) then ART.Qty else if(ART.Qty < 0) then ART.Qty -- Yanko + -- dobaveno, za da ne se pokazva "-" pred kolichestvoto, ako kol.>0, a cenata e 0 else -- ((-1)*ART.Qty) if(ART.Totalsum = 0) then ART.QTY -- Yanko - --Maria --RoomNo=convert(varchar,BK.Room), --Maria --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif) --Maria +,1982 --Maria - --Maria --Maria --Maria --Maria --Maria +, 1975 --Maria - --Maria --RoomNo=(if(G.GuestNum <> 0) then convert(varchar,(select distinct LastRoom from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum)) else null endif), --Maria --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif) --Maria +,1982 --Maria - -- RoomNo=(if(G.GuestNum<>0) then convert(varchar,(select distinct Max(B.Room) from "dba".Book as B,"dba".Guests as G where B.WhoPays=G.GuestNum and INV.Whom=G."Name" and G.GuestNum<>0)) else null endif) --and ART.ServiceNo not in(1004,1005) --Maria +, 1975 --Maria - --Maria --RoomNo=max(if(G.GuestNum <> 0) then convert(varchar,(select distinct LastRoom from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum)) else null endif), --Maria --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif) --Maria +,1982 --Maria - --RoomNo=Max((if(G.GuestNum<>0) then convert(varchar,(select distinct Max(B.Room) from "dba".Book as B,"dba".Guests as G where B.WhoPays=G.GuestNum and INV.Whom=G."Name" and G.GuestNum<>0)) else null endif)) --Maria +, 1975 --Maria - -- [v.1 --] -- [v.2 23.Mar.2007] - Yanko, dobaveni smetki ot VTD, promeneno DDS da se vzima ot VATRates -- [v.3 26.May.2008] - gnikolov, aiaaaaia eieiia AAN, p1902 --Maria --RoomNo=(if(GuestNum > 0) then convert(varchar,(select distinct vv.LastRoom from dba.v_ArriveStayLeaveGuestBook as vv where vv.GuestNum = GuestNum)) else null endif), --Maria --Maria +,1982 --Maria - -- gnikolov - aiaaaaii nuia?acyaaia n ?aaenou? VTDCommonCounter ii?u?ea 2008 --Dobrin 2195 --Dobrin 2195 --EdCena=round(abs(ART.Price)*((1+VR.Rate)*(if TipDDS = 2 then 1 else 0 endif)),4), Dobrin - MON --VR.DDSType = INV.DDSType Dobrin - MON --EdCena=round(abs(ART.Price)*((1+VR.Rate)*(if ART.TipDDS = 2 then 1 else 0 endif)),4), Dobrin - MON --VR.DDSType = INV.DDSType Dobrin - MON --EdCena=round(Max(abs(ART.Price)*((1+VR.Rate)*(if ART.TipDDS = 2 then 1 else 0 endif))),4), Dobrin - MON --VR.DDSType = INV.DDSType Dobrin - MON else((-1)*ART.Qty) endif endif endif, EdCena=round(abs(ART.Price)*(1+VR.Rate),4), Stoinost=round(Kol*EdCena,4), StoinostBDDS=round(Kol*abs(ART.Price),4), DDS=Stoinost-StoinostBDDS, Discount=ART.Discount, EdCenaWithDisc=round(DBA.CalcPriceWithDiscount(Discount,EdCena),4), StoinostWithDisc=round(Kol*EdCenaWithDisc,4), Klient=INV.Whom, DanNo=INV.WhomDanNum, GuestNum=G.GuestNum, GuestName=G.Name, Komentar=String(DateRegDt,' - ',"Date"(Days(DateRegDt,NumLodgings))), Operator=ADMIN.ShortName, ContractName=(if(Inv.ContractNo <> 0) then (select CC.ShortName from dba.Contracts as CC where CC.ContractNo = Inv.ContractNo) else Contracts.ShortName endif), Country=Countries.Description, Nationality=HK.Description, RoomNo=R.RoomName, DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%', Fiscal=(if INV.Fiscal = 0 then 'Не' else 'Да' endif) from DBA.PriceKinds as PK, DBA.Articles as ART, DBA.Services, DBA.Invoices as INV, DBA.Book as BK, DBA.Rooms as R, DBA.Guests as G, DBA.Admin as ADMIN, DBA.Contracts, DBA.Countries, DBA.HumanKinds as HK, DBA.VATRates as VR where BK.PriceKind = PK.PriceKind and BK.Room = R.RoomNum and ART.InvoiceNo = INV.InvoiceNo and Abs(RegNo) = BK.RegNum and BK.RegNum <> 0 and INV.InvoiceDateDt >= bvrData1 and INV.InvoiceDateDt <= bvrData2 and BK.Guest = G.GuestNum and INV.KodAdmin = ADMIN.AdminNo and ART.ServiceNo = Services.ServiceNo and G.Contract = Contracts.ContractNo and G.HumanKind = HK.HumanKindNo and G.NativeCountry = Countries.CountryKod and VR.ID = ART.TipDDS union all select OtchetNumber=DBA.GetOtchetNumber(ART.InvoiceNo), SmetkaNo=(select first ExternalNo from Invoices Inv1 where Inv1.InvoiceNo=ART.InvoiceNo), FakturaNo=if((INV.TrueInvoiceNo <> 0) and(INV.TrueInvoiceNo < 1000000000)) then INV.MaskaFaktura || substr('0000000000',1,9-length(INV.TrueInvoiceNo)) || INV.TrueInvoiceNo endif,DokData=INV.InvoiceDateDt, DokTime=DateFormat(INV.TrueInvoiceHour,'hh:mm'), Service=Services.ServiceName, Kol=if INV.CancelInvoiceNo > 0 then(-1)*ART.Qty else ART.Qty endif, EdCena=round(abs(ART.Price)*(1+VR.Rate),4), Stoinost=round(Kol*EdCena,4), StoinostBDDS=round(Kol*abs(ART.Price),4), DDS=Stoinost-StoinostBDDS, Discount=ART.Discount, EdCenaWithDisc=round(DBA.CalcPriceWithDiscount(Discount,EdCena),4), StoinostWithDisc=round(Kol*EdCenaWithDisc,4), Klient=INV.Whom, DanNo=INV.WhomDanNum, GuestNum=G.GuestNum, GuestName=G.Name,Komentar='', Operator=ADMIN.ShortName, ContractName=(if(Inv.ContractNo <> 0) then (select CC.ShortName from dba.Contracts as CC where CC.ContractNo = Inv.ContractNo) else Contracts.ShortName endif), Country=Countries.Description, Nationality=HK.Description, RoomNo=(if(G.GuestNum <> 0) then(select distinct LastRoomName from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum) else null endif), DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%', Fiscal=(if INV.Fiscal = 0 then 'Не' else 'Да' endif) from DBA.Articles as ART, DBA.Services, DBA.Invoices as INV, DBA.ServiceCredits as SC, DBA.Guests as G, DBA.Admin as ADMIN, DBA.Contracts,dba.Countries, DBA.HumanKinds as HK, DBA.VATRates as VR where ART.InvoiceNo = INV.InvoiceNo and ART.CreditNo = SC.CreditNo and ART.CreditNo <> 0 and SC.GuestNo = G.GuestNum and INV.KodAdmin = ADMIN.AdminNo and INV.InvoiceDateDt >= bvrData1 and INV.InvoiceDateDt <= bvrData2 and ART.ServiceNo = Services.ServiceNo and G.Contract = Contracts.ContractNo and G.NativeCountry = Countries.CountryKod and G.HumanKind = HK.HumanKindNo and VR.ID = ART.TipDDS union all select OtchetNumber=DBA.GetOtchetNumber(ART.InvoiceNo), SmetkaNo=(select first ExternalNo from Invoices Inv1 where Inv1.InvoiceNo=ART.InvoiceNo), FakturaNo=if((max(INV.TrueInvoiceNo) <> 0) and(max(INV.TrueInvoiceNo) < 1000000000)) then max(INV.MaskaFaktura) || substr('0000000000',1,9-length(max(INV.TrueInvoiceNo))) || max(INV.TrueInvoiceNo) endif,DokData=Max(INV.InvoiceDateDt), DokTime=DateFormat(Max(INV.TrueInvoiceHour),'hh:mm'), Service=Max(Services.ServiceName), Kol=if INV.CancelInvoiceNo > 0 then(-1)*max(ART.Qty) else max(ART.Qty) endif, EdCena=round(Max(abs(ART.Price)*(1+VR.Rate)),4), Stoinost=round(Kol*EdCena,4), StoinostBDDS=round(Kol*Max(abs(ART.Price)),4), DDS=Stoinost-StoinostBDDS, Discount=ART.Discount, EdCenaWithDisc=round(DBA.CalcPriceWithDiscount(Discount,EdCena),4), StoinostWithDisc=round(Kol*EdCenaWithDisc,4), Klient=Max(INV.Whom), DanNo=Max(INV.WhomDanNum), GuestNum=Max(RM.GuestNo), GuestName=Max(G.Name),Komentar='', Operator=Max(ADMIN.ShortName), ContractName=Max(if(Inv.ContractNo <> 0) then (select CC.ShortName from dba.Contracts as CC where CC.ContractNo = Inv.ContractNo) else Contracts.ShortName endif), Country=Max(Countries.Description), Nationality=Max(HK.Description), RoomNo=max(if(G.GuestNum <> 0) then(select distinct LastRoomName from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum) else null endif), DDSTypeSt=convert(varchar,DBA.FormatNum(Max(VR.Rate)*100,'0.##'))+'%', Fiscal=(if INV.Fiscal = 0 then 'Не' else 'Да' endif) from DBA.Articles as ART, DBA.Services, DBA.Invoices as INV, DBA.Guests as G, DBA.Admin as ADMIN, DBA.Contracts, DBA.Countries, DBA.ResMoney as RM, DBA.HumanKinds as HK, DBA.VATRates as VR where ART.InvoiceNo = INV.InvoiceNo and ART.CreditNo = 0 and ART.RegNo = 0 and RM.InvoiceNo = INV.InvoiceNo and G.GuestNum = RM.GuestNo and INV.InvoiceDateDt >= bvrData1 and INV.InvoiceDateDt <= bvrData2 and G.Contract = Contracts.ContractNo and G.NativeCountry = Countries.CountryKod and INV.KodAdmin = ADMIN.AdminNo and ART.ServiceNo = Services.ServiceNo and G.HumanKind = HK.HumanKindNo and ART.ServiceNo in( 1004,1005) and VR.ID = ART.TipDDS group by ART.InvoiceNo, ART.ArticleNo, INV.CancelInvoiceNo, Discount, ART.DDSType,INV.Fiscal union all select OtchetNumber=DBA.GetOtchetNumber(VA.InvoiceNo), SmetkaNo=(select first ExternalNo from Invoices Inv1 where Inv1.InvoiceNo=VA.InvoiceNo), FakturaNo=if((VI.TrueInvoiceNo <> 0) and(VI.TrueInvoiceNo < 1000000000)) then VI.MaskaFaktura || substr('0000000000',1,9-length(VI.TrueInvoiceNo)) || VI.TrueInvoiceNo endif,DokData=VI.InvoiceDateDt, DokTime=DateFormat(VI.TrueInvoiceHour,'hh:mm'), Service=S.ServiceName, Kol=(if VI.CancelInvoiceNo > 0 then-Abs(VA.TotalNights) else Abs(VA.TotalNights) endif),EdCena=Round(Abs(if VA.SinglePrice <> 0 then(VA.SinglePrice*Cr.ExchangeRate) else(VA.GrandTotal*Cr.ExchangeRate)/VA.TotalNights endif)*(if VI.VAT = 0 then 1 else(1+VR.Rate) endif),4),Stoinost=Round(Kol*EdCena,4), StoinostBDDS=Round(Kol*Abs(if VA.SinglePrice <> 0 then(VA.SinglePrice*Cr.ExchangeRate) else(VA.GrandTotal*Cr.ExchangeRate)/VA.TotalNights endif),4),DDS=Stoinost-StoinostBDDS, Discount=(if VA.SinglePrice <> 0 then VA.Discount else 0 endif), EdCenaWithDisc=Round(DBA.CalcPriceWithDiscount(Discount,EdCena),4), StoinostWithDisc=Round(Kol*EdCenaWithDisc,4), Klient=VI.Whom, DanNo=VI.WhomDanNum, GuestNum=(if VA.CreditNo > 0 then(select SC.GuestNo from DBA.ServiceCredits as SC where SC.CreditNo = VA.CreditNo) else-1 endif),GuestName=(if VA.CreditNo > 0 then(select G.Name from DBA.Guests as G,DBA.ServiceCredits as SC where G.GuestNum = SC.GuestNo and SC.CreditNo = VA.CreditNo) else '-' endif),Komentar='',Operator=ADMIN.ShortName, ContractName=C.ShortName, Country=(if GuestNum > 0 then(select CC.Description from DBA.Countries as CC,DBA.Guests as G where CC.CountryKod = G.NativeCountry and G.GuestNum = GuestNum) else '-' endif),Nationality=(if GuestNum > 0 then(select HK.Description from DBA.HumanKinds as HK,DBA.Guests as G where HK.HumanKindNo = G.HumanKind and G.GuestNum = GuestNum) else '-' endif),RoomNo=(if(GuestNum > 0) then(select distinct vv.LastRoomName from dba.v_ArriveStayLeaveGuestBook as vv where vv.GuestNum = GuestNum) else null endif), DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%', Fiscal=(if VI.Fiscal = 0 then 'Не' else 'Да' endif) from DBA.VaucherArticles as VA, DBA.VaucherInvoices as VI, DBA.Services as S, DBA.VATRates as VR, DBA.Admin, DBA.Contracts as C, DBA.Currencies as Cr where VA.InvoiceNo = VI.VaucherInvoiceNo and VA.ServiceNo = S.ServiceNo and VR.ID = 4 and VI.KodAdmin = Admin.AdminNo and C.ContractNo = VI.ContractNo and VI.InvoiceDateDt >= bvrData1 and VI.InvoiceDateDt <= bvrData2 and Cr.KodCurrency = VI.KodCurrency and( (select IntStojnost from DBA.Registers where RegisterName = 'VTDCommonCounter') = 0) GO IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_PayTypes'))) THEN Drop view v_PayTypes END IF GO create view DBA.v_PayTypes as select DataPay=ResMoney.DateMoneyDt, PayType=PayTypes.PayTypeName, AllSum=ResMoney.Sum*DayRates.ExchangeRate, AllSumVlt=ResMoney.Sum, Valuta=Currencies.Symbol, DayRates.ExchangeRate, PTVidName=PTV.Description, DocNomer=ResMoney.AccountNumber, Smetka=(if (ResMoney.VaucherInvoiceNo is null) and (ResMoney.InvoiceNo is not null) then (select first ExternalNo from Invoices Inv1 where Inv1.InvoiceNo=ResMoney.InvoiceNo) else if (ResMoney.InvoiceNo is null) and (ResMoney.VaucherInvoiceNo is not null) then ''||ResMoney.VaucherInvoiceNo else null endif endif), --Maria +, 1982 Fiscal=(if ResMoney.VaucherInvoiceNo is null then( if(select Fiscal from DBA.Invoices where InvoiceNo = ResMoney.InvoiceNo) = 0 then 'Не' else 'Да' endif) else if ResMoney.InvoiceNo is null then( if(select Fiscal from DBA.VaucherInvoices where VaucherInvoiceNo = ResMoney.VaucherInvoiceNo) = 0 then 'Не' else 'Да' endif) endif endif) from --Maria - dba.PayTypes,dba.ResMoney,dba.DayRates,DBA.Currencies,dba.PayTypesVids as PTV where ResMoney.Payment = PayTypes.PayTypeN and ResMoney.Currency = DayRates.KodCurrency and ResMoney.DateMoneyDt = DayRates.DateDt and ResMoney.Currency = Currencies.KodCurrency and DateMoneyDt >= bvrData1 and DateMoneyDt <= bvrData2 and ResMoney.PTVid = PTV.N and( ((ResMoney.VaucherInvoiceNo is null) and(select IntStojnost from DBA.Registers where RegisterName = 'VTDCommonCounter') = 1) or( (select IntStojnost from DBA.Registers where RegisterName = 'VTDCommonCounter') = 0)) -- gnikolov - добавено съобразяване с регистър VTDCommonCounter поръчка 2008 GO Delete from Relations where lcase(RelationTable) = lcase('VIP') 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_VIP','__OPTIONS__','VIP','__OPTIONS__','VIP','__OPTIONS__',1,GetFreeNInRelations('VIP', 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_VIP','GuestName','VIP','Име гост','VIP','Guest Name',2,GetFreeNInRelations('VIP', 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_VIP','RoomNo','VIP','Стая No','VIP','Room #',2,GetFreeNInRelations('VIP', Null, Null, 10),'RoomNo','','',0,0,'','','select RoomName from Rooms where Status <> 1 and Status <> 3','',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_VIP','ArriveDate','VIP','Пристигнал','VIP','Arrived',2,GetFreeNInRelations('VIP', 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_VIP','DaysToStay','VIP','Дни','VIP','Days',2,GetFreeNInRelations('VIP', Null, Null, 10),'DaysToStay','','',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_VIP','LeaveDate','VIP','Очак.дата.зам.','VIP','Exp.Check.Out.Date',2,GetFreeNInRelations('VIP', 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_VIP','VIP','VIP','VIP','VIP','VIP',2,GetFreeNInRelations('VIP', Null, Null, 10),'VIP','','',0,0,'','','select vipname from vip','',80,'',0,0,1,today()) Go delete from InformPermission where RelationN=( select N from Relations where TableName='//v_VIP'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_VIP'and ColName='__OPTIONS__' ),GrNo,1); end if; end for; GO IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_ReportServices'))) THEN Drop view v_ReportServices END IF GO create view DBA.v_ReportServices as select InvoiceNo=(select first ExternalNo from Invoices Inv1 where Inv1.InvoiceNo=Articles.InvoiceNo), Service=Services.ServiceName, Suma=round(if Articles.TipDDS = 3 then Articles.TotalSum else Articles.TotalSum*(1+VR.Rate) endif,4), Invoices.InvoiceDateDt, PayTypes.PayTypeName, Admin.FullName, ContractName=Contracts.FullName, Qty=(if Invoices.CancelInvoiceNo > 0 then(-1)*Articles.Qty else Articles.Qty endif), --Maria +, 1982 Fiscal=(if Invoices.Fiscal = 0 then 'Не' else 'Да' endif) from --Maria - DBA.Articles, DBA.PayTypes, DBA.Invoices, DBA.Services, DBA.Admin, DBA.Book, DBA.Guests, DBA.Contracts, DBA.VATRates as VR where Articles.InvoiceNo = Invoices.InvoiceNo and Invoices.InvoiceDateDt >= bvrData1 and Invoices.InvoiceDateDt <= bvrData2 and Invoices.PayHow = PayTypes.PayTypeN and Articles.ServiceNo = Services.ServiceNo and Invoices.KodAdmin = Admin.AdminNo and abs(Articles.RegNo) = Book.RegNum and Articles.RegNo <> 0 and Book.Guest = Guests.GuestNum and Guests.Contract *= Contracts.ContractNo and VR.DDSType = Articles.DDSType union all select InvoiceNo=(select first ExternalNo from Invoices Inv1 where Inv1.InvoiceNo=Articles.InvoiceNo), Service=Services.ServiceName, Suma=round(if Articles.TipDDS = 3 then DBA.CalcPriceWithDiscount(Articles.Discount,Articles.Price*Articles.Qty) else DBA.CalcPriceWithDiscount(Articles.Discount,Articles.Price*Articles.Qty)*(1+VR.Rate) endif,4), Invoices.InvoiceDateDt, PayTypes.PayTypeName, Admin.FullName, ContractName=Contracts.FullName, Qty=(if Invoices.CancelInvoiceNo > 0 then(-1)*Articles.Qty else Articles.Qty endif), --Maria +, 1982 Fiscal=(if Invoices.Fiscal = 0 then 'Не' else 'Да' endif) from --Maria - DBA.Articles, DBA.PayTypes, DBA.Invoices, DBA.Services, DBA.Admin, DBA.ServiceCredits, DBA.Guests, DBA.Contracts, DBA.VATRates as VR where Articles.InvoiceNo = Invoices.InvoiceNo and Invoices.InvoiceDateDt >= bvrData1 and Invoices.InvoiceDateDt <= bvrData2 and Invoices.PayHow = PayTypes.PayTypeN and Articles.ServiceNo = Services.ServiceNo and Invoices.KodAdmin = Admin.AdminNo and Articles.CreditNo = ServiceCredits.CreditNo and Articles.CreditNo <> 0 and ServiceCredits.GuestNo = Guests.GuestNum and Guests.Contract *= Contracts.ContractNo and VR.DDSType = Articles.DDSType union all select InvoiceNo=(select first ExternalNo from Invoices Inv1 where Inv1.InvoiceNo=max(Articles.InvoiceNo)), Service=max(Services.ServiceName), Suma=round(max(if Articles.TipDDS = 3 then Articles.TotalSum else Articles.TotalSum*(1+VR.Rate) endif),4), max(Invoices.InvoiceDateDt), max(PayTypes.PayTypeName), max(Admin.FullName), ContractName=max(Contracts.FullName), Qty=(if Invoices.CancelInvoiceNo > 0 then(-1)*Articles.Qty else Articles.Qty endif), --Maria +, 1982 Fiscal=(if Invoices.Fiscal = 0 then 'Не' else 'Да' endif) from --Maria - DBA.Articles, DBA.PayTypes, DBA.Invoices, DBA.Services, DBA.Admin, DBA.ResMoney, DBA.Guests, DBA.Contracts, DBA.VATRates as VR where Articles.InvoiceNo = Invoices.InvoiceNo and Invoices.InvoiceDateDt >= bvrData1 and Invoices.InvoiceDateDt <= bvrData2 and Invoices.PayHow = PayTypes.PayTypeN and Articles.ServiceNo = Services.ServiceNo and Invoices.KodAdmin = Admin.AdminNo and Articles.RegNo = 0 and Articles.CreditNo = 0 and ResMoney.InvoiceNo = Invoices.InvoiceNo and Guests.GuestNum = ResMoney.GuestNo and Contracts.ContractNo = Guests.Contract and Articles.ServiceNo in( 1004,1005) and VR.DDSType = Articles.DDSType group by Articles.InvoiceNo, Articles.ArticleNo, Articles.Qty, Invoices.CancelInvoiceNo,Invoices.Fiscal union all select InvoiceNo=(select first ExternalNo from Invoices Inv1 where Inv1.InvoiceNo=VA.InvoiceNo), Service=S.ServiceName, Suma=Round((VA.GrandTotal*Cr.ExchangeRate)*(if(VI.VAT = 0 and VI.WithoutVAT <> 0) then 1 --Maria +, 1982 --Maria - -- gnikolov - добавено съобразяване с регистър VTDCommonCounter поръчка 2008 else(1+VR.Rate) endif),4),VI.InvoiceDateDt, PT.PayTypeName, Admin.FullName, ContractName=Contracts.FullName, Qty=(if VI.CancelInvoiceNo > 0 then-Abs(VA.TotalNights) else Abs(VA.TotalNights) endif), Fiscal=(if VI.Fiscal = 0 then 'Не' else 'Да' endif) from DBA.VaucherArticles as VA, DBA.VaucherInvoices as VI, DBA.PayTypes as PT, DBA.VATRates as VR, DBA.Services as S, DBA.Admin, DBA.Contracts, DBA.Currencies as Cr where VA.InvoiceNo = VI.VaucherInvoiceNo and VI.PayHow = PT.PayTypeN and VI.KodAdmin = Admin.AdminNo and Contracts.ContractNo = VI.ContractNo and VI.InvoiceDateDt >= bvrData1 and VI.InvoiceDateDt <= bvrData2 and VR.ID = 4 and VA.ServiceNo = S.ServiceNo and Cr.KodCurrency = VI.KodCurrency and( (select IntStojnost from DBA.Registers where RegisterName = 'VTDCommonCounter') = 0) 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_Reservations','__Options__','Заети стаи по резервации','__Options__','Заети стаи по резервации','__Options__',1,GetFreeNInRelations('Заети стаи по резервации', Null, Null, 10),'__Options__','','',0,0,'','','Printer.Orientation=1','',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_Reservations','bvrData1','Заети стаи по резервации','От дата','Заети стаи по резервации','bvrData1',2,GetFreeNInRelations('Заети стаи по резервации', Null, Null, 10),'bvrData1','','',0,0,1,1,'','select Date(Now())',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_Reservations','bvrData2','Заети стаи по резервации','До дата','Заети стаи по резервации','bvrData2',2,GetFreeNInRelations('Заети стаи по резервации', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select Date(Now())',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_Reservations','ReserveNo','Заети стаи по резервации','Номер','Заети стаи по резервации','Номер',2,GetFreeNInRelations('Заети стаи по резервации', Null, Null, 10),'','','',2,2,'','','','',43,'',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_Reservations','DateReserveDt','Заети стаи по резервации','Дата','Заети стаи по резервации','Дата',2,GetFreeNInRelations('Заети стаи по резервации', Null, Null, 10),'','','',0,1,'','','','',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_Reservations','ReserveName','Заети стаи по резервации','Име','Заети стаи по резервации','Име',2,GetFreeNInRelations('Заети стаи по резервации', Null, Null, 10),'','','',0,0,'','','','',160,'',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_Reservations','Hora','Заети стаи по резервации','Души','Заети стаи по резервации','Души',2,GetFreeNInRelations('Заети стаи по резервации', Null, Null, 10),'','SUM(qrData.Души)','',2,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_Reservations','Days','Заети стаи по резервации','Дни','Заети стаи по резервации','Дни',2,GetFreeNInRelations('Заети стаи по резервации', Null, Null, 10),'','','',2,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_Reservations','BoardPlanName','Заети стаи по резервации','Борд','Заети стаи по резервации','Борд',2,GetFreeNInRelations('Заети стаи по резервации', Null, Null, 10),'','','',2,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_Reservations','BusyCount','Заети стаи по резервации','Типове стаи','Заети стаи по резервации','Типове стаи',2,GetFreeNInRelations('Заети стаи по резервации', Null, Null, 10),'','','',0,0,'','','','',100,'',0,1,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_Reservations','BusyRooms','Заети стаи по резервации','Номера стаи','Заети стаи по резервации','Номера стаи',2,GetFreeNInRelations('Заети стаи по резервации', Null, Null, 10),'','','',0,0,'','','','',140,'',0,1,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_Reservations','ReserveNote','Заети стаи по резервации','Бележка','Заети стаи по резервации','Бележка',2,GetFreeNInRelations('Заети стаи по резервации', Null, Null, 10),'','','',0,0,'','','','',200,'',0,1,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_Reservations','Phone','Заети стаи по резервации','Телефон','Заети стаи по резервации','Телефон',2,GetFreeNInRelations('Заети стаи по резервации', Null, Null, 10),'','','',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_Reservations','ContractName','Заети стаи по резервации','Договор','Заети стаи по резервации','ContractName',2,GetFreeNInRelations('Заети стаи по резервации', Null, Null, 10),'ContractName','','',0,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_Reservations','StatusName','Заети стаи по резервации','Статус резервация','Заети стаи по резервации','Статус резервация',2,GetFreeNInRelations('Заети стаи по резервации', Null, Null, 10),'','','',0,0,'','','','',100,'',0,0,1,today()) Go delete from InformPermission where RelationN=( select N from Relations where TableName='//v_Reservations'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_Reservations'and ColName='__OPTIONS__' ),GrNo,1); end if; end for; GO