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),
    SmetkaNo=(select first 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,
    --Maria +, 1975
    DokTime=DateFormat(INV.TrueInvoiceHour,'hh:mm'),
    --Maria -
    Service=Art.ArticleName, --Dobrin 2195 - vmesto ot Services da vzima imeto na Cenorazpisa
    /*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
        -- Yanko -
        --Maria
        --RoomNo=convert(varchar,BK.Room),
        --Maria
        --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif)
        --Maria +,1982
        --Maria -
        --Maria
        --Maria
        --Maria
        --Maria
        --Maria +, 1975
        --Maria -
        --Maria
        --RoomNo=(if(G.GuestNum <> 0) then convert(varchar,(select distinct LastRoom from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum)) else null endif),
        --Maria
        --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif)
        --Maria +,1982
        --Maria -
        -- 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)
        --and ART.ServiceNo not in(1004,1005)
        --Maria +, 1975
        --Maria -
        --Maria
        --RoomNo=max(if(G.GuestNum <> 0) then convert(varchar,(select distinct LastRoom from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum)) else null endif),
        --Maria
        --DDSTypeSt=(if ART.DDSType = 1 then '20%' else '7%' endif)
        --Maria +,1982
        --Maria -
        --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))
        --Maria +, 1975
        --Maria -
        -- [v.1 --]
        -- [v.2 23.Mar.2007] - Yanko, dobaveni smetki ot VTD, promeneno DDS da se vzima ot VATRates
        -- [v.3 26.May.2008] - gnikolov, aiaaaaia eieiia AAN, p1902
        --Maria
        --RoomNo=(if(GuestNum > 0) then convert(varchar,(select distinct vv.LastRoom from dba.v_ArriveStayLeaveGuestBook as vv where vv.GuestNum = GuestNum)) else null endif),
        --Maria
        --Maria +,1982
        --Maria -
        -- gnikolov - aiaaaaii nuia?acyaaia n ?aaenou? VTDCommonCounter ii?u?ea 2008
        --Dobrin 2195
        --Dobrin 2195
        --EdCena=round(abs(ART.Price)*((1+VR.Rate)*(if TipDDS = 2 then 1 else 0 endif)),4), Dobrin - MON
        --VR.DDSType = INV.DDSType Dobrin - MON
        --EdCena=round(abs(ART.Price)*((1+VR.Rate)*(if ART.TipDDS = 2 then 1 else 0 endif)),4), Dobrin - MON
        --VR.DDSType = INV.DDSType Dobrin - MON
        --EdCena=round(Max(abs(ART.Price)*((1+VR.Rate)*(if ART.TipDDS = 2 then 1 else 0 endif))),4), Dobrin - MON
        else --VR.DDSType = INV.DDSType Dobrin - MON
          ((-1)*ART.Qty)
        endif
      endif
    endif,
    EdCena=round(abs(ART.Price)*(1+VR.Rate),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(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=R.RoomName,
    DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%',
    Fiscal=(if INV.Fiscal = 0 then 'Не' else 'Да' endif),
    ServiceName=Services.ServiceName
	 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.VATRates as VR where
    BK.PriceKind = PK.PriceKind and
    BK.Room = R.RoomNum and
    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.ID = ART.TipDDS
    
    
    union all
    

    select OtchetNumber=DBA.GetOtchetNumber(ART.InvoiceNo),
    SmetkaNo=(select first 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,
    DokTime=DateFormat(INV.TrueInvoiceHour,'hh:mm'),
    Service=Art.ArticleName,
    Kol=if INV.CancelInvoiceNo > 0 then(-1)*ART.Qty else ART.Qty endif,
    EdCena=round(abs(ART.Price)*(1+VR.Rate),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 LastRoomName from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum) else null endif),
    DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%',
    Fiscal=(if INV.Fiscal = 0 then 'Не' else 'Да' endif),
    ServiceName=Services.ServiceName


	 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 = Services.ServiceNo and
    G.Contract = Contracts.ContractNo and
    G.NativeCountry = Countries.CountryKod and
    G.HumanKind = HK.HumanKindNo and
    VR.ID = ART.TipDDS


        union all
        
        select OtchetNumber=DBA.GetOtchetNumber(ART.InvoiceNo),
    SmetkaNo=(select first 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),
    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,
    EdCena=round(Max(abs(ART.Price)*(1+VR.Rate)),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 LastRoomName from dba.v_ArriveStayLeaveGuestBook where GuestNum = G.GuestNum) else null endif),
    DDSTypeSt=convert(varchar,DBA.FormatNum(Max(VR.Rate)*100,'0.##'))+'%',
    Fiscal=(if INV.Fiscal = 0 then 'Не' else 'Да' endif),
    ServiceName=Services.ServiceName
	 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.ID = ART.TipDDS 

    group by ART.InvoiceNo,
    ART.ArticleNo,
    INV.CancelInvoiceNo,
    Discount,
    ART.DDSType,INV.Fiscal, ServiceName
    
    union all
    
    select OtchetNumber=DBA.GetOtchetNumber(VA.InvoiceNo),
    SmetkaNo=(select first 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,
    DokTime=DateFormat(VI.TrueInvoiceHour,'hh:mm'),
    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*Cr.ExchangeRate) else(VA.GrandTotal*Cr.ExchangeRate)/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*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 = 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 = GuestNum)
    else '-' endif),RoomNo=(if(GuestNum > 0) then(select distinct vv.LastRoomName from dba.v_ArriveStayLeaveGuestBook as vv where vv.GuestNum = GuestNum) else null endif),
    DDSTypeSt=convert(varchar,DBA.FormatNum(VR.Rate*100,'0.##'))+'%',
    Fiscal=(if VI.Fiscal = 0 then 'Не' else 'Да' endif),
    ServiceName=S.ServiceName

	 from
    DBA.VaucherArticles as VA,
    DBA.VaucherInvoices as VI,
    DBA.Services as S,
    DBA.VATRates as VR,
    DBA.Admin,
    DBA.Contracts as C,
    DBA.Currencies as Cr
	 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 and
    Cr.KodCurrency = VI.KodCurrency and
    ((select IntStojnost from DBA.Registers where RegisterName = 'VTDCommonCounter') = 0)

    go

    update Relations
set ColName = 'Service'
where  TableName = 'v_ProdajbiPoUslugi'
and ColName = 'ServiceName'
and BGColName = 'Услуга'

go

    update Relations
set ColName = 'ServiceName'
where  TableName = 'v_ProdajbiPoUslugi'
and BGColName = 'Име услуга'