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_ReportServices', '__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_ReportServices', '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_ReportServices', '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_ReportServices', '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_ReportServices', '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_ReportServices', 'InvoiceNo', 'Отчет услуги', 'Сметка', 'Отчет услуги', 'Сметка', 2, GetFreeNInRelations('Отчет услуги',null,null,5), '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', 'Отчет услуги', 'Фискализирана', 'Отчет услуги', 'Фискализирана', 0, 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_ReportServices', 'Service', 'Отчет услуги', 'Услуга', 'Отчет услуги', 'Услуга', 2, GetFreeNInRelations('Отчет услуги',null,null,5), '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,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_ReportServices', '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_ReportServices', '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_ReportServices', 'FullName', 'Отчет услуги', 'Администратор', 'Отчет услуги', 'Администратор', 2, GetFreeNInRelations('Отчет услуги',null,null,5), 'FullName', '', '', 1, 0, '', '', '', '', 200, '', 0, 0, 1, today())
 
GO

Call InsertPravaSpravki('Отчет услуги');

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 Inv1.ExternalNo from DBA.Invoices as 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 'Да' --Maria -
    endif),
	GuestName = Guests.Name from 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 Inv1.ExternalNo from DBA.Invoices as 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 'Да' --Maria -
    endif),
	GuestName = Guests.Name from 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 Inv1.ExternalNo from DBA.Invoices as 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 'Да' --Maria -
    endif),
	GuestName = Guests.Name from 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,Guests.Name union all
  select InvoiceNo=(select first Inv1.ExternalNo from DBA.Invoices as 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
    else( --Maria +, 1982
    --Maria -
    -- gnikolov - добавено съобразяване с регистър VTDCommonCounter поръчка 2008
    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),
	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)