delete from Relations where RelationTable='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,5), '__OPTIONS__', '', '', 0, 0, '', '', '', '', 1, '', 0, 0, 1, '23.01.2020'); 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,5), 'bvrData1', '', '', 1, 0, '1', '1', '', 'select bvrData1', 56, '', 0, 0, 1, '23.01.2020'); 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,5), 'bvrData2', '', '', 1, 0, '1', '1', '', 'select bvrData2', 56, '', 0, 0, 1, '23.01.2020'); 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', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'OtchetNumber', '', '', 1, 0, '', '', '', '', 60, '', 0, 0, 1, '23.01.2020'); 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', 'HotelName', 'Издадени сметки по услуги', 'Общежитие', 'Issued Accounts by Service', 'HotelName', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'HotelName', '', '', 0, 0, '', '', 'select HotelName from Hotels as H, Registers as R where H.Hotel_Id = R.IntStojnost and R.RegisterName = ''HotelId''', '', 150, '', 0, 0, 1, '23.01.2020'); 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', 'Vid', 'Издадени сметки по услуги', 'Операция', 'Issued Accounts by Service', 'Vid', 2, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'Vid', '', '', 1, 1, '', '', '', '', 100, '', 0, 0, 1, '23.01.2020'); 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,5), 'SmetkaNo', '', '', 1, 1, '', '', '', '', 80, '', 0, 0, 1, '23.01.2020'); 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', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'FakturaNo', '', '', 1, 0, '', '', '', '', 56, '', 0, 0, 1, '23.01.2020'); 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,5), 'DokData', '', '', 0, 0, '', '', '', '', 72, '', 0, 0, 1, '23.01.2020'); 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', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'DokTime', '', '', 0, 0, '', '', '', '', 72, '', 0, 0, 1, '23.01.2020'); 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', 'DateDanEvent', 'Издадени сметки по услуги', 'Дата дан.събитие', 'Issued Accounts by Service', 'DateDanEvent', 2, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'DateDanEvent', '', '', 0, 0, '', '', '', '', 72, '', 0, 0, 1, '29.11.2021'); 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,5), 'Service', '', '', 0, 0, '', '', 'select ServiceName from Services union (select ''Нощувка'') union (select ''Застраховка'') ', '', 150, '', 0, 0, 1, '23.01.2020'); 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,5), 'SUM(Kol)', 'SUM(qrData.Кол.)', '0.##', 1, 0, '', '', '', '', 56, '', 0, 0, 1, '23.01.2020'); 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', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'EdCena', '', '# ### ##0.00', 1, 0, '', '', '', '', 72, '', 0, 0, 1, '23.01.2020'); 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,5), 'SUM(Stoinost)', 'SUM(qrData.Стойност)', '# ### ##0.00', 1, 0, '', '', '', '', 80, '', 0, 0, 1, '23.01.2020'); 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)', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'SUM(StoinostBDDS)', 'SUM(qrData.Стн. б.ДДС)', '# ### ##0.00', 1, 0, '', '', '', '', 80, '', 0, 0, 1, '23.01.2020'); 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', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'SUM(DDS)', 'SUM(qrData.ДДС)', '# ### ##0.00', 0, 0, '0', '0', '', '', 60, '', 0, 0, 1, '23.01.2020'); 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', 'PayTypeStr', 'Издадени сметки по услуги', 'Начин на плащане', 'Issued Accounts by Service', 'PayTypeStr', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'PayTypeStr', '', '', 0, 0, '', '', 'select PayTypeName from PayTypes', '', 80, '', 0, 0, 1, '23.01.2020'); 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', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'Klient', '', '', 0, 0, '', '', 'SELECT Whom FROM Invoices GROUP BY Whom', '', 160, '', 0, 0, 1, '23.01.2020'); 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', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'GuestNum', '', '', 1, 0, '', '', '', '', 56, '', 0, 0, 1, '23.01.2020'); 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,5), 'GuestName', '', '', 0, 0, '', '', 'SELECT "Name" FROM Guests GROUP BY "Name"', '', 150, '', 0, 0, 1, '23.01.2020'); 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', 'FakNo', 'Издадени сметки по услуги', 'Фак. №', 'Issued Accounts by Service', 'FakNo', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'FakNo', '', '', 0, 0, '', '', 'SELECT "Name" FROM Guests GROUP BY "Name"', '', 150, '', 0, 0, 1, '23.01.2020'); 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', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'ContractName', '', '', 1, 0, '', '', 'select ShortName from Contracts', '', 150, '', 0, 0, 1, '23.01.2020'); 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', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'Country', '', '', 1, 0, '', '', 'select Description from Countries where Active=1', '', 150, '', 0, 0, 1, '23.01.2020'); 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', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'Komentar', '', '', 0, 0, '', '', '', '', 170, '', 0, 0, 1, '23.01.2020'); 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', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'Operator', '', '', 0, 0, '', '', 'SELECT ShortName FROM ADMIN', '', 110, '', 0, 0, 1, '23.01.2020'); 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', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'Nationality', '', '', 0, 0, '', '', 'SELECT Description FROM HumanKinds', '', 150, '', 0, 0, 1, '23.01.2020'); 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,5), 'RoomNo', '', '', 1, 0, '', '', '', '', 50, '', 0, 0, 1, '23.01.2020'); 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', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'Discount', '', '', 1, 0, '', '', '', '', 55, '', 0, 0, 1, '23.01.2020'); 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', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'EdCenaWithDisc', '', '# ### ##0.00', 1, 0, '', '', '', '', 70, '', 0, 0, 1, '23.01.2020'); 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', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'StoinostWithDisc', '', '# ### ##0.00', 1, 0, '', '', '', '', 70, '', 0, 0, 1, '23.01.2020'); 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', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'DDSTypeSt', '', '', 0, 0, '0', '0', 'select ''20%'' union all select ''7%'' ', '', 60, '', 0, 0, 1, '23.01.2020'); 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,5), 'Fiscal', '', '', 0, 0, '0', '0', 'select ''Да'' union all select ''Не'' ', '', 60, '', 0, 0, 1, '23.01.2020'); 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,5), 'ServiceName', '', '', 0, 0, '', '', 'select ServiceName from Services where Active = 1', '', 100, '', 0, 0, 1, '23.01.2020'); 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', 'PriceKind', 'Издадени сметки по услуги', 'Ценоразпис', 'Issued Accounts by Service', 'PriceKind', 0, GetFreeNInRelations('Issued Accounts by Service',null,null,5), 'PriceKind', '', '', 0, 0, '', '', 'select Description from PriceKinds where Valid = 1', '', 160, '', 0, 0, 1, '23.01.2020'); GO Call InsertPravaSpravki('Issued Accounts by Service'); 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), Vid=(if Inv.CancelInvoiceNo > 0 then 'Кредитиране' else 'Плащане' endif), SmetkaNo=(select first Inv1.ExternalNo from DBA.Invoices as 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, DateDanEvent=Inv.DateDanEvent, --Maria +, 1975 DokTime=DateFormat(INV.TrueInvoiceHour,'hh:mm'), --Maria - Service=Art.ArticleName, --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 else -- Yanko + -- dobaveno, za da ne se pokazva "-" pred kolichestvoto, ako kol.>0, a cenata e 0 -- ((-1)*ART.Qty) if(ART.Totalsum = 0) then ART.QTY else( -- 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 -- [07.10.2009] - Milko Dobavena nova kolona HotelName (Ime na obstejitie) -- [26.11.2009] - Milko - Dobavena nova kolona FakNo -- [02.12.2009] - Milko - Dobavena nova kolona Cenorazpis i _ ako e usluga (-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(BK.DateRegDt,' - ',"Date"(Days(BK.DateRegDt,BK.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),ServiceName=Services.ServiceName, HotelName=(select H.HotelName from DBA.Hotels as H,DBA.Registers as R where H.Hotel_Id = R.IntStojnost and R.RegisterName = 'HotelId'), FakNo=G.FN, PriceKind=PK.Description, PayTypeStr=PT.PayTypeName 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 ,DBA.PayTypes as PT where BK.PriceKind = PK.PriceKind and BK.Room = R.RoomNum and ART.InvoiceNo = INV.InvoiceNo and Abs(ART.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 and Inv.PayHow = PT.PayTypeN union all select OtchetNumber=DBA.GetOtchetNumber(ART.InvoiceNo), Vid=(if Inv.CancelInvoiceNo > 0 then 'Кредитиране' else 'Плащане' endif), SmetkaNo=(select first Inv1.ExternalNo from DBA.Invoices as 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, DateDanEvent=Inv.DateDanEvent, DokTime=DateFormat(INV.TrueInvoiceHour,'hh:mm'), Service=Art.ArticleName, 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 v_ArriveStayLeaveGuestBook.LastRoomName from dba.v_ArriveStayLeaveGuestBook where v_ArriveStayLeaveGuestBook.GuestNum = G.GuestNum) else null endif), DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%', Fiscal=(if INV.Fiscal = 0 then 'Не' else 'Да' endif),ServiceName=Services.ServiceName, HotelName=(select H.HotelName from DBA.Hotels as H,DBA.Registers as R where H.Hotel_Id = R.IntStojnost and R.RegisterName = 'HotelId'), FakNo=G.FN,PriceKind='Услуга', PayTypeStr=PT.PayTypeName 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 ,DBA.PayTypes as PT 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 and Inv.PayHow = PT.PayTypeN union all select OtchetNumber=DBA.GetOtchetNumber(ART.InvoiceNo), Vid=(if Inv.CancelInvoiceNo > 0 then 'Кредитиране' else 'Плащане' endif), SmetkaNo=(select first Inv1.ExternalNo from DBA.Invoices as 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), DateDanEvent=Max(Inv.DateDanEvent), 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 v_ArriveStayLeaveGuestBook.LastRoomName from dba.v_ArriveStayLeaveGuestBook where v_ArriveStayLeaveGuestBook.GuestNum = G.GuestNum) else null endif), DDSTypeSt=convert(varchar,DBA.FormatNum(Max(VR.Rate)*100,'0.##'))+'%', Fiscal=(if INV.Fiscal = 0 then 'Не' else 'Да' endif),ServiceName=Services.ServiceName, HotelName=(select H.HotelName from DBA.Hotels as H,DBA.Registers as R where H.Hotel_Id = R.IntStojnost and R.RegisterName = 'HotelId'), FakNo=G.FN,PriceKind='Услуга', PayTypeStr=PT.PayTypeName 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 ,DBA.PayTypes as PT 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 and Inv.PayHow = PT.PayTypeN group by ART.InvoiceNo, ART.ArticleNo, INV.CancelInvoiceNo, Discount, ART.DDSType,INV.Fiscal,ServiceName,FakNo,PT.PayTypeName union all select OtchetNumber=DBA.GetOtchetNumber(VA.InvoiceNo), Vid=(if VI.CancelInvoiceNo > 0 then 'Кредитиране' else 'Плащане' endif), SmetkaNo=(select first Inv1.ExternalNo from DBA.Invoices as 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, DateDanEvent=VI.DateDanEvent, 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 = G.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 = G.GuestNum) else '-' endif),RoomNo=(if(GuestNum > 0) then(select distinct vv.LastRoomName from dba.v_ArriveStayLeaveGuestBook as vv where vv.GuestNum = vv.GuestNum) else null endif), DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%', Fiscal=(if VI.Fiscal = 0 then 'Не' else 'Да' endif),ServiceName=S.ServiceName, HotelName=(select H.HotelName from DBA.Hotels as H,DBA.Registers as R where H.Hotel_Id = R.IntStojnost and R.RegisterName = 'HotelId'),FakNo='',PriceKind='Услуга', PayTypeStr=PT.PayTypeName 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 ,DBA.PayTypes as PT 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 VI.PayHow = PT.PayTypeN and( (select Registers.IntStojnost from DBA.Registers where Registers.RegisterName = 'VTDCommonCounter') = 0)