IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_VtdPariPoSmetki'))) THEN
Drop View v_VtdPariPoSmetki
END IF

GO

CREATE VIEW "DBA"."v_VtdPariPoSmetki"  as
select
 SmetkaNo=VaucherInvoiceNo,
    VBroi=round(SUM(if Payment=1 then Sum*ExchangeRate else 0 endif),4),
    PoSmetka=round(SUM(if Payment<>1 then Sum*ExchangeRate else 0 endif),4),
    Suma=round(SUM(Sum*ExchangeRate),4)
    from "DBA".ResMoney as RM,"DBA".DayRates as DR
    where RM.Currency=DR.KodCurrency and RM.DateMoneyDt=DR.DateDt
    group by SmetkaNo


GO


IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_IzdadeniSmetki'))) THEN
Drop View v_IzdadeniSmetki
END IF

GO

CREATE VIEW "DBA"."v_IzdadeniSmetki"
as 
select SmetkaNo=INV.InvoiceNo,
FakturaNo=if((INV.TrueInvoiceNo <> 0) and(INV.TrueInvoiceNo < 1000000000)) then INV.MaskaFaktura || substr('0000000000',1,9-length(INV.TrueInvoiceNo)) || INV.TrueInvoiceNo
else convert(varchar,INV.TrueInvoiceNo)
endif,
DokData=INV.InvoiceDateDt,
Klient=INV.Whom,
DanNo=INV.Bulstat,
Operator=ADMIN.ShortName,
SumaBDDS=round(TotalNoVAT,4),
DDS=round(VAT,4),
Suma=round(GrandTotal,4),
PlatVBroi=round(VBroi,4),
PlatPoSmetka=round(PoSmetka,4),
Plateni=round(VBroi+PoSmetka,4),
DDSTypeSt=(if INV.DDSType=1 then '20%' else '7%' endif)
from
DBA.Invoices as INV,DBA.v_PariPoSmetki as PARI,DBA.Admin where
INV.InvoiceNo = PARI.SmetkaNo and INV.KodAdmin = ADMIN.AdminNo


Union all


select 
SmetkaNo=INV.VaucherInvoiceNo,
FakturaNo=if((INV.TrueInvoiceNo <> 0) and(INV.TrueInvoiceNo < 1000000000)) then INV.MaskaFaktura || substr('0000000000',1,9-length(INV.TrueInvoiceNo)) || INV.TrueInvoiceNo
else convert(varchar,INV.TrueInvoiceNo)
endif,
DokData=INV.InvoiceDateDt,
Klient=INV.Whom,
DanNo=INV.Bulstat,
Operator=ADMIN.ShortName,
SumaBDDS=round(GrandTotal,4),
DDS=round(VAT,4),
Suma=round(TotalNoVAT,4),
PlatVBroi=round(VBroi,4),
PlatPoSmetka=round(PoSmetka,4),
Plateni=round(VBroi+PoSmetka,4),
DDSTypeSt='7%'
from
DBA.VaucherInvoices as INV,DBA.v_VtdPariPoSmetki as PARI,DBA.Admin where
INV.VaucherInvoiceNo = PARI.SmetkaNo and INV.KodAdmin = ADMIN.AdminNo


GO


if (Select count(*) from Relations where TableName='v_IzdadeniSmetki' and ColName='DDSTypeSt' and RelationTable='Issued Accounts')=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_IzdadeniSmetki','DDSTypeSt','Издадени сметки','ДДС Тип',
 'Issued Accounts','DDSTypeSt',2,
 GetFreeNInRelations('Issued Accounts', Null, Null, 10),
 'DDSTypeSt','','',0,0,'0','0','select ''20%'' union all select ''7%'' ','',60,'',0,0,1,TODAY())

end if;