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