IF (EXISTS (select Table_name 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
      else
// Yanko +
// dobaveno, za da ne se pokazva "-" pred kolichestvoto, ako kol.>0, a cenata e 0
    // ((-1)*ART.Qty)
        if (ART.Totalsum=0)
          then ART.QTY
          else ((-1)*ART.Qty)
        endif
// Yanko -
      endif
    endif,
    EdCena=round(abs(ART.Price)*((if ART.DDSType=1 then (1+bvrDDSPart) else 1.07 endif )*(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)
    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
    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 

    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)*( (if ART.DDSType=1 then  (1+bvrDDSPart) else 1.07 endif)*(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)
    /*    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
    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 
    
    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)*((if ART.DDSType=1 then  (1+bvrDDSPart) else 1.07 endif)*(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)
    /*    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
    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)
    group by ART.InvoiceNo,ART.ArticleNo,INV.CancelInvoiceNo,Discount,ART.DDSType
    
    
GO

if (Select count(*) from Relations where TableName='v_ProdajbiPoUslugi' and ColName='DDSTypeSt' and RelationTable='Issued Accounts by Service')=0 then

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,
Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,
Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
 VALUES('v_ProdajbiPoUslugi','DDSTypeSt','Издадени сметки по услуги','ДДС Тип',
 'Issued Accounts by Service','DDSTypeSt',2,
 GetFreeNInRelations('Issued Accounts by Service', Null, Null, 10),
 'DDSTypeSt','','',0,0,'0','0','select ''20%'' union all select ''7%'' ','',60,'',0,0,1,TODAY())

end if;