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,
    DateDanEvent=Invoices.DateDanEvent,
    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
	and VR.Active <> 0 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,
    DateDanEvent=Invoices.DateDanEvent,
    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 
	and VR.Active <> 0 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),
    DateDanEvent=max(Invoices.DateDanEvent),
    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
	and VR.Active <> 0 
    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,
    DateDanEvent=VI.DateDanEvent,
    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)