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,
    DokTime=DateFormat(INV.TrueInvoiceHour,'hh:mm'),
    Service=Art.ArticleName, 
    Kol=if(ART.totalsum > 0) then
      ART.Qty
    else if(ART.Qty < 0) then
        ART.Qty
      else 
        if(ART.Totalsum = 0) then
          ART.QTY
        else((-1)*ART.Qty)
        endif
      endif
    endif,
	VATRate=ART.VatRate,
    EdCena=round(abs(ART.Price)*(1+VATRate),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(VatRate*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.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 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,
    VATRate=ART.VatRate,
	EdCena=round(abs(ART.Price)*(1+VATRate),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(VatRate*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.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 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,
    VATRate=ART.VatRate,
	EdCena=round(Max(abs(ART.Price)*(1+VatRate)),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(VatRate)*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.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 Inv.PayHow = PT.PayTypeN
    group by ART.InvoiceNo,
    ART.ArticleNo,
    INV.CancelInvoiceNo,
    Discount,
    ART.DDSType,INV.Fiscal,ServiceName,VatRate,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),
	VATRate=VA.VatRate,
	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+VATRate)
    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(VATRate*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.Admin
      ,DBA.Contracts as C
      ,DBA.Currencies as Cr
      ,DBA.PayTypes as PT
    where VA.InvoiceNo = VI.VaucherInvoiceNo
    and VA.ServiceNo = S.ServiceNo
    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)