Delete from Relations where RelationTable='Подробен отчет услуги' 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_ElaborateReportServices', '__OPTIONS__', 'Подробен отчет услуги', '__OPTIONS__', 'Подробен отчет услуги', '__OPTIONS__', 1, GetFreeNInRelations('Подробен отчет услуги',null,null,5), '__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_ElaborateReportServices', 'bvrData1', 'Подробен отчет услуги', 'От дата', 'Подробен отчет услуги', 'От дата', 2, GetFreeNInRelations('Подробен отчет услуги',null,null,5), '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_ElaborateReportServices', 'bvrData2', 'Подробен отчет услуги', 'До дата', 'Подробен отчет услуги', 'До дата', 2, GetFreeNInRelations('Подробен отчет услуги',null,null,5), '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_ElaborateReportServices', 'GuestName', 'Подробен отчет услуги', 'Име на студент', 'Подробен отчет услуги', 'GuestName', 2, GetFreeNInRelations('Подробен отчет услуги',null,null,5), 'GuestName', '', '', 0, 0, '', '', '', '', 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_ElaborateReportServices', 'InvoiceDateDt', 'Подробен отчет услуги', 'Дата', 'Подробен отчет услуги', 'Дата', 2, GetFreeNInRelations('Подробен отчет услуги',null,null,5), '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_ElaborateReportServices', 'InvoiceNo', 'Подробен отчет услуги', 'Сметка', 'Подробен отчет услуги', 'Сметка', 2, GetFreeNInRelations('Подробен отчет услуги',null,null,5), 'InvoiceNo', '', '', 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_ElaborateReportServices', 'Fiscal', 'Подробен отчет услуги', 'Фискализирана', 'Подробен отчет услуги', 'Фискализирана', 2, GetFreeNInRelations('Подробен отчет услуги',null,null,5), '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_ElaborateReportServices', 'Service', 'Подробен отчет услуги', 'Услуга', 'Подробен отчет услуги', 'Услуга', 2, GetFreeNInRelations('Подробен отчет услуги',null,null,5), 'Service', '', '', 0, 101, '', '', 'select distinct Service from v_ElaborateReportServices', '', 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_ElaborateReportServices', 'Qty', 'Подробен отчет услуги', 'Бр.услуги', 'Подробен отчет услуги', 'Qty', 0, GetFreeNInRelations('Подробен отчет услуги',null,null,5), '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_ElaborateReportServices', 'Suma', 'Подробен отчет услуги', 'Сума', 'Подробен отчет услуги', 'Сума', 2, GetFreeNInRelations('Подробен отчет услуги',null,null,5), '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_ElaborateReportServices', 'PayTypeName', 'Подробен отчет услуги', 'Начин на плащане', 'Подробен отчет услуги', 'PayTypeName', 2, GetFreeNInRelations('Подробен отчет услуги',null,null,5), '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_ElaborateReportServices', 'FullName', 'Подробен отчет услуги', 'Администратор', 'Подробен отчет услуги', 'Администратор', 0, GetFreeNInRelations('Подробен отчет услуги',null,null,5), 'FullName', '', '', 1, 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_ElaborateReportServices', 'OtchetDate', 'Подробен отчет услуги', 'Дата отчет', 'Подробен отчет услуги', 'Дата отчет', 2, GetFreeNInRelations('Подробен отчет услуги',null,null,5), 'OtchetDate', '', '', 1, 101, '', '', 'select OtchetDateDt from Otcheti', '', 90, '', 0, 0, 1, today()) GO INSERT INTO Relations(TableName, ColName, BGTableName, BGColName, RelationTable, RelationCol, SelectCol, N, Function, Suma, Mask, Align, Sortirane, Zadaljitelna, Promenliva, FilterList, DftFilter, Shirina, Opisanie, DftLike, AutoStretch, Spravka, PoslData) VALUES('v_ElaborateReportServices', 'OtchetNo', 'Подробен отчет услуги', 'Номер отчет', 'Подробен отчет услуги', 'Номер отчет', 2, GetFreeNInRelations('Подробен отчет услуги',null,null,5), 'OtchetNo', '', '', 1, 101, '', '', 'select OtchetNo from Otcheti', '', 90, '', 0, 0, 1, today()) GO Call InsertPravaSpravki('Подробен отчет услуги'); GO IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_ElaborateReportServices'))) THEN Drop view v_ElaborateReportServices END IF GO create view DBA.v_ElaborateReportServices as select Articles.InvoiceNo, Service="left"(Articles.ArticleName,if charindex('в стая',Articles.ArticleName) = 0 then if charindex('за стая',Articles.ArticleName) = 0 then LENGTH(Articles.ArticleName) else --Maria +, 1982 --Maria - --Maria +, 1982 --Maria - --Maria +, 1982 --Maria - --Maria +, 1982 --Maria - -- gnikolov - добавено съобразяване с регистър VTDCommonCounter поръчка 2008 charindex('за стая',Articles.ArticleName)-2 endif else charindex('в стая',Articles.ArticleName)-2 endif),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),OtchetDate=Otcheti.OtchetDateDt, OtchetNo=Otcheti.OtchetNo, Fiscal=(if Invoices.Fiscal = 0 then 'Не' else 'Да' endif), GuestName = Guests.Name from DBA.Articles ,DBA.Otcheti ,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.InvoiceNo >= Otcheti.FromInvoiceNo and Invoices.InvoiceNo <= Otcheti.ToInvoiceNo 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 Articles.InvoiceNo, Service="left"(Articles.ArticleName,if charindex('в стая',Articles.ArticleName) = 0 then if charindex('за стая',Articles.ArticleName) = 0 then LENGTH(Articles.ArticleName) else charindex('за стая',Articles.ArticleName)-2 endif else charindex('в стая',Articles.ArticleName)-2 endif),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),OtchetDate=Otcheti.OtchetDateDt, OtchetNo=Otcheti.OtchetNo, Fiscal=(if Invoices.Fiscal = 0 then 'Не' else 'Да' endif), GuestName = Guests.Name from DBA.Articles ,DBA.Otcheti ,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.InvoiceNo >= Otcheti.FromInvoiceNo and Invoices.InvoiceNo <= Otcheti.ToInvoiceNo 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=max(Articles.InvoiceNo), Service=max("left"(Articles.ArticleName,if charindex('в стая',Articles.ArticleName) = 0 then if charindex('за стая',Articles.ArticleName) = 0 then LENGTH(Articles.ArticleName) else charindex('за стая',Articles.ArticleName)-2 endif else charindex('в стая',Articles.ArticleName)-2 endif)),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),OtchetDate=Otcheti.OtchetDateDt, OtchetNo=Otcheti.OtchetNo, Fiscal=(if Invoices.Fiscal = 0 then 'Не' else 'Да' endif), GuestName = Guests.Name from DBA.Articles ,DBA.Otcheti ,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.InvoiceNo >= Otcheti.FromInvoiceNo and Invoices.InvoiceNo <= Otcheti.ToInvoiceNo 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,OtchetDate,OtchetNo,Guests.Name union all select VA.InvoiceNo, Service=S.ServiceName, Suma=Round((VA.GrandTotal*Cr.ExchangeRate)*(if(VI.VAT = 0 and VI.WithoutVAT <> 0) then 1 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),OtchetDate='01.01.01',OtchetNo=-1, Fiscal=(if VI.Fiscal = 0 then 'Не' else 'Да' endif), GuestName = VA.GuestName 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 Registers.IntStojnost from DBA.Registers where Registers.RegisterName = 'VTDCommonCounter') = 0)