IF (EXISTS(Select * from systable where LCase(table_name) = LCase('v_ProdajbiPoUslugi')))
  THEN Drop View v_ProdajbiPoUslugi
END IF

Go

create view
  DBA.v_ProdajbiPoUslugi
as 
select 
  OtchetNumber=GetOtchetNumber(ART.InvoiceNo),
  SmetkaNo=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,
  Service=Services.ServiceName,
  /*Kol=if INV.CancelInvoiceNo>0 then(-1)*ART.Qty else ART.Qty endif,*/
  /*Kol=if (ART.totalsum < 0 ) then ((-1)*ART.Qty) else ART.Qty endif,*/
  Kol=if(ART.totalsum > 0) then
    ART.Qty
  else
    if(ART.Qty < 0) then
      ART.Qty
    -- Yanko +
    -- dobaveno, za da ne se pokazva "-" pred kolichestvoto, ako kol.>0, a cenata e 0
    else -- ((-1)*ART.Qty)
      if(ART.Totalsum = 0) then
        ART.QTY
      else((-1)*ART.Qty)
      endif
    endif -- Yanko -
  endif,
  EdCena=round(abs(ART.Price)*((1+VR.Rate)*(if TipDDS = 2 then 1 else 0 endif)),4),
  Stoinost=round(Kol*EdCena,4),
  StoinostBDDS=round(Kol*abs(ART.Price),4),
  Discount=ART.Discount,
  EdCenaWithDisc=round(CalcPriceWithDiscount(Discount,EdCena),4),
  StoinostWithDisc=round(Kol*EdCenaWithDisc,4),
  Klient=INV.Whom,
  DanNo=INV.WhomDanNum,
  GuestNum=G.GuestNum,
  GuestName=G.Name,
  Komentar=String(DateRegDt,' - ',"Date"(Days(DateRegDt,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=convert(varchar,BK.Room),
  --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif)
  DDSTypeSt=Convert(Varchar, FormatNum(VR.Rate * 100,'0.##'))+'%'
from
  DBA.Articles as ART,
  DBA.Services,
  DBA.Invoices as INV,
  DBA.Book as BK,
  DBA.Guests as G,
  DBA.Admin as ADMIN,
  DBA.Contracts,
  DBA.Countries,
  DBA.HumanKinds as HK,
  DBA.VATRates as VR
where
  ART.InvoiceNo = INV.InvoiceNo
  and Abs(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 VR.DDSType = INV.DDSType
  

union all


select
  OtchetNumber=GetOtchetNumber(ART.InvoiceNo),
  SmetkaNo=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,
  Service=Services.ServiceName,
  Kol=if INV.CancelInvoiceNo > 0 then(-1)*ART.Qty else ART.Qty endif,
  EdCena=round(abs(ART.Price)*((1+VR.Rate)*(if ART.TipDDS = 2 then 1 else 0 endif)),4),
  Stoinost=round(Kol*EdCena,4),
  StoinostBDDS=round(Kol*abs(ART.Price),4),
  Discount=ART.Discount,
  EdCenaWithDisc=round(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 convert(varchar,(select distinct LastRoom from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum)) else null endif),
  --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif)
  DDSTypeSt=Convert(Varchar, FormatNum(VR.Rate * 100,'0.##'))+'%'
  -- RoomNo=(if(G.GuestNum<>0) then convert(varchar,(select distinct Max(B.Room) from "dba".Book as B,"dba".Guests as G where B.WhoPays=G.GuestNum and INV.Whom=G."Name" and G.GuestNum<>0)) else null endif)
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.VATRates as VR
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 not in(1004,1005)
  and ART.ServiceNo = Services.ServiceNo
  and G.Contract = Contracts.ContractNo
  and G.NativeCountry = Countries.CountryKod
  and G.HumanKind = HK.HumanKindNo
  and VR.DDSType = INV.DDSType
  

union all


select
  OtchetNumber=GetOtchetNumber(ART.InvoiceNo),
  SmetkaNo=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),
  Service=Max(Services.ServiceName),
  Kol=if INV.CancelInvoiceNo > 0 then(-1)*max(ART.Qty) else max(ART.Qty) endif,
  EdCena=round(Max(abs(ART.Price)*((1+VR.Rate)*(if ART.TipDDS = 2 then 1 else 0 endif))),4),
  Stoinost=round(Kol*EdCena,4),
  StoinostBDDS=round(Kol*Max(abs(ART.Price)),4),
  Discount=ART.Discount,
  EdCenaWithDisc=round(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 convert(varchar,(select distinct LastRoom from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum)) else null endif),
  --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif)
  DDSTypeSt=Convert(Varchar, FormatNum(Max(VR.Rate) * 100,'0.##'))+'%'
  --RoomNo=Max((if(G.GuestNum<>0) then convert(varchar,(select distinct Max(B.Room) from "dba".Book as B,"dba".Guests as G where B.WhoPays=G.GuestNum and INV.Whom=G."Name" and G.GuestNum<>0)) else null endif))
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.VATRates as VR
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 VR.DDSType = INV.DDSType
group by
  ART.InvoiceNo,
  ART.ArticleNo,
  INV.CancelInvoiceNo,
  Discount,
  ART.DDSType
  

union all


select
  OtchetNumber=GetOtchetNumber(VA.InvoiceNo),
  SmetkaNo=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,
  Service=S.ServiceName,
  Kol=(if VI.CancelInvoiceNo > 0 then -Abs(VA.TotalNights) else Abs(VA.TotalNights) endif),
  EdCena=Round(Abs(if VA.SinglePrice<>0 then VA.SinglePrice else VA.GrandTotal/VA.TotalNights endif)*(if VI.VAT=0 then 1 else (1 + VR.Rate) endif)    ,4),
  Stoinost=Round(Kol*EdCena,4),
  StoinostBDDS=Round(Kol*Abs(if VA.SinglePrice<>0 then VA.SinglePrice else VA.GrandTotal/VA.TotalNights endif),4),
  Discount=(if VA.SinglePrice <> 0 then VA.Discount else 0 endif),
  EdCenaWithDisc=Round(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 ServiceCredits as SC where SC.CreditNo=VA.CreditNo) else -1 endif),
  GuestName=(if VA.CreditNo>0 then (Select G.Name from Guests as G, 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 Countries as CC, Guests as G where CC.CountryKod=G.NativeCountry and G.GuestNum=GuestNum) else '-' endif),
  Nationality=(if GuestNum > 0 then (Select HK.Description from HumanKinds as HK, Guests as G where HK.HumanKindNo=G.HumanKind and G.GuestNum=GuestNum) else '-' endif),
  RoomNo=(if(GuestNum > 0) then Convert(Varchar,(Select distinct vv.LastRoom from dba.v_ArriveStayLeaveGuestBook as vv where vv.GuestNum = GuestNum)) else null endif),
  DDSTypeSt=Convert(Varchar, FormatNum(VR.Rate * 100,'0.##'))+'%'
from
  VaucherArticles as VA,
  VaucherInvoices as VI,
  Services as S,
  VATRates as VR,
  Admin,
  Contracts as C
where
  VA.InvoiceNo = VI.VaucherInvoiceNo
  and VA.ServiceNo = S.ServiceNo
  and VR.ID=4
  and VI.KodAdmin = Admin.AdminNo
  and C.ContractNo = VI.ContractNo
  and VI.InvoiceDateDt >= bvrData1
  and VI.InvoiceDateDt <= bvrData2
-- [v.1 --]
-- [v.2 23.Mar.2007] - Yanko, dobaveni smetki ot VTD, promeneno DDS da se vzima ot VATRates