delete from Relations where RelationTable='Issued Accounts'

GO

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', '__OPTIONS__', 'Издадени сметки', '__OPTIONS__', 'Issued Accounts', '__OPTIONS__', 1, GetFreeNInRelations('Issued Accounts',null,null,5), '__OPTIONS__', '', '', 0, 0, '', '', '', '', 1, '', 0, 0, 1, '23.09.2009');

GO


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', 'SmetkaNo', 'Издадени сметки', 'С-ка No', 'Issued Accounts', 'Account #', 2, GetFreeNInRelations('Issued Accounts',null,null,5), 'SmetkaNo', '', '', 1, 0, '', '', '', '', 80, '', 0, 0, 1, '23.09.2009');

GO


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', 'FakturaNo', 'Издадени сметки', 'Ф-ра No', 'Issued Accounts', 'Invoice Number', 0, GetFreeNInRelations('Issued Accounts',null,null,5), 'FakturaNo', '', '', 1, 0, '', '', '', '', 56, '', 0, 0, 1, '23.09.2009');

GO


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', 'DokData', 'Издадени сметки', 'Дата', 'Issued Accounts', 'Date', 2, GetFreeNInRelations('Issued Accounts',null,null,5), 'DokData', '', '', 0, 0, '', '', '', '', 72, '', 0, 0, 1, '23.09.2009');

GO


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', 'DokTime', 'Издадени сметки', 'Час', 'Issued Accounts', 'Time', 2, GetFreeNInRelations('Issued Accounts',null,null,5), 'DokTime', '', '', 0, 0, '', '', '', '', 72, '', 0, 0, 1, '23.09.2009');

GO


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', 'SumaBDDS', 'Издадени сметки', 'Стн. б.ДДС', 'Issued Accounts', 'Value (no VAT)', 0, GetFreeNInRelations('Issued Accounts',null,null,5), 'SUM(SumaBDDS)', 'SUM(qrData.Стн. б.ДДС)', '# ### ##0.00', 1, 0, '', '', '', '', 80, '', 0, 0, 1, '23.09.2009');

GO


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', 'DDS', 'Издадени сметки', 'ДДС', 'Issued Accounts', 'VAT', 2, GetFreeNInRelations('Issued Accounts',null,null,5), 'SUM(DDS)', 'SUM(qrData.ДДС)', '# ### ##0.00', 1, 0, '', '', '', '', 80, '', 0, 0, 1, '23.09.2009');

GO


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', 'Suma', 'Издадени сметки', 'Стойност', 'Issued Accounts', 'Value', 2, GetFreeNInRelations('Issued Accounts',null,null,5), 'SUM(Suma)', 'SUM(qrData.Стойност)', '# ### ##0.00', 1, 0, '', '', '', '', 80, '', 0, 0, 1, '23.09.2009');

GO


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', 'PlatVBroi', 'Издадени сметки', 'В брой', 'Issued Accounts', 'In Cash', 2, GetFreeNInRelations('Issued Accounts',null,null,5), 'SUM(PlatVBroi)', 'SUM(qrData.В брой)', '# ### ##0.00', 1, 0, '', '', '', '', 80, '', 0, 0, 1, '23.09.2009');

GO


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', 'PlatPoSmetka', 'Издадени сметки', 'По сметка', 'Issued Accounts', 'On Account', 2, GetFreeNInRelations('Issued Accounts',null,null,5), 'SUM(PlatPoSmetka)', 'SUM(qrData.По сметка)', '# ### ##0.00', 1, 0, '', '', '', '', 80, '', 0, 0, 1, '23.09.2009');

GO


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', 'Plateni', 'Издадени сметки', 'Общо пл.', 'Issued Accounts', 'Total Paid', 2, GetFreeNInRelations('Issued Accounts',null,null,5), 'SUM(Plateni)', 'SUM(qrData.Общо пл.)', '# ### ##0.00', 1, 0, '', '', '', '', 80, '', 0, 0, 1, '23.09.2009');

GO


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', 'Klient', 'Издадени сметки', 'Клиент', 'Issued Accounts', 'Client', 2, GetFreeNInRelations('Issued Accounts',null,null,5), 'Klient', '', '', 0, 0, '', '', 'SELECT Whom FROM Invoices GROUP BY Whom', '', 160, '', 0, 0, 1, '23.09.2009');

GO


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', 'DanNo', 'Издадени сметки', 'ЕИН', 'Issued Accounts', 'Tax #', 0, GetFreeNInRelations('Issued Accounts',null,null,5), 'DanNo', '', '', 0, 0, '', '', 'SELECT Whom FROM Invoices GROUP BY Whom', '', 88, '', 0, 0, 1, '23.09.2009');

GO


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', 'Operator', 'Издадени сметки', 'Съставил', 'Issued Accounts', 'Signed By', 0, GetFreeNInRelations('Issued Accounts',null,null,5), 'Operator', '', '', 0, 0, '', '', 'SELECT ShortName FROM ADMIN', '', 110, '', 0, 0, 1, '23.09.2009');

GO


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,5), 'DDSTypeSt', '', '', 0, 0, '0', '0', 'select ''20%'' union all select ''7%'' ', '', 60, '', 0, 0, 1, '23.09.2009');

GO


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', 'Fiscal', 'Издадени сметки', 'Фискализирана', 'Issued Accounts', 'Fiscal', 2, GetFreeNInRelations('Issued Accounts',null,null,5), 'Fiscal', '', '', 0, 0, '0', '0', 'select ''Да'' union all select ''Не''', '', 60, '', 0, 0, 1, '23.09.2009');

GO


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', 'DateDanEvent', 'Издадени сметки', 'Дата дан.събитие', 'Issued Accounts', 'DateDanEvent', 2, GetFreeNInRelations('Issued Accounts',null,null,5), 'DateDanEvent', '', '', 0, 0, '', '', '', '', 72, '', 0, 0, 1, '23.09.2009');

GO

Call InsertPravaSpravki('Issued Accounts');

GO

IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_IzdadeniSmetki')))
THEN Drop view v_IzdadeniSmetki
END IF

GO

create view DBA.v_IzdadeniSmetki as
  select SmetkaNo=(select first Inv1.ExternalNo from DBA.Invoices as Inv1 where Inv1.InvoiceNo = Inv.InvoiceNo),
    RealSmetkaNo=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,
	DateDanEvent=Inv.DateDanEvent,
    --Maria +, 1975
    DokTime=DateFormat(Inv.TrueInvoiceHour,'hh:mm'),
    --Maria -
    Klient=Inv.Whom,
    DanNo=Inv.Bulstat,
    Operator=Adm.ShortName,
    SumaBDDS=round(Inv.TotalNoVAT+Inv.WithoutVAT,4),
    DDS=round(Inv.VAT,4),
    Suma=round(Inv.GrandTotal,4),
    PlatVBroi=round(Pari.VBroi,4),
    PlatPoSmetka=round(Pari.PoSmetka,4),
    Plateni=round(Pari.VBroi+Pari.PoSmetka,4),
    DDSTypeSt=(if Inv.DDSType = 1 then '20%'
    else '7%' --Maria +, 1982
    endif),Fiscal=(if Inv.Fiscal = 0 then 'Не'
    else 'Да' --Maria -
    endif) from DBA.Invoices as Inv,DBA.v_PariPoSmetki as Pari,DBA.Admin as Adm
    where Inv.InvoiceNo = Pari.SmetkaNo and Inv.KodAdmin = Adm.AdminNo union all
  select SmetkaNo=(select first Inv1.ExternalNo from DBA.Invoices as Inv1 where Inv1.InvoiceNo = Inv.VaucherInvoiceNo),
    RealSmetkaNo=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,
	DateDanEvent=Inv.DateDanEvent,
    --Maria +, 1975
    DokTime=DateFormat(Inv.TrueInvoiceHour,'hh:mm'),
    --Maria -
    Klient=Inv.Whom,
    DanNo=Inv.Bulstat,
    Operator=Adm.ShortName,
    SumaBDDS=round(Inv.TotalNoVAT+Inv.WithoutVAT,4),
    DDS=round(Inv.VAT,4),
    Suma=round(Inv.GrandTotal,4),
    PlatVBroi=round(Pari.VBroi,4),
    PlatPoSmetka=round(Pari.PoSmetka,4),
    Plateni=round(Pari.VBroi+Pari.PoSmetka,4),DDSTypeSt='7%',
    --Maria +, 1982
    Fiscal=(if Inv.Fiscal = 0 then 'Не'
    else 'Да' --Maria -
    endif) from DBA.VaucherInvoices as Inv,DBA.v_VtdPariPoSmetki as Pari,DBA.Admin as Adm
    where Inv.VaucherInvoiceNo = Pari.SmetkaNo and Inv.KodAdmin = Adm.AdminNo and(
    (select Registers.IntStojnost from DBA.Registers where Registers.RegisterName = 'VTDCommonCounter') = 0)
    order by RealSmetkaNo asc
-- gnikolov - добавено съобразяване с регистър VTDCommonCounter поръчка 2008
--[30.07.2009] - Yoan - Izchislqva pravilno SumaBDDS