delete from Relations where RelationTable='Payment Report'

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_PayTypes', '__OPTIONS__', 'Отчет плащания', '__OPTIONS__', 'Payment Report', '__OPTIONS__', 1, GetFreeNInRelations('Payment Report',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_PayTypes', 'bvrData1', 'Отчет плащания', 'От дата', 'Payment Report', 'From Date', 2, GetFreeNInRelations('Payment Report',null,null,5), 'bvrData1', '', '', 1, 0, '1', '1', '', 'select Date(Now())-1', 32, '', 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_PayTypes', 'bvrData2', 'Отчет плащания', 'До дата', 'Payment Report', 'To Date', 2, GetFreeNInRelations('Payment Report',null,null,5), 'bvrData2', '', '', 1, 0, '1', '1', '', 'select Date(Now())', 32, '', 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_PayTypes', 'DataPay', 'Отчет плащания', 'Дата', 'Payment Report', 'Date', 2, GetFreeNInRelations('Payment Report',null,null,5), 'DataPay', '', '', 0, 1, '', '', '', '', 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_PayTypes', 'PayType', 'Отчет плащания', 'Начин пл.', 'Payment Report', 'Type Of Payment', 2, GetFreeNInRelations('Payment Report',null,null,5), 'PayType', '', '', 0, 0, '', '', 'select PayTypeName from PayTypes', '', 104, '', 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_PayTypes', 'DocNomer', 'Отчет плащания', 'Номер на документ', 'Payment Report', 'Document Number', 0, GetFreeNInRelations('Payment Report',null,null,5), 'DocNomer', '', '', 1, 0, '', '', '', '', 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_PayTypes', 'Smetka', 'Отчет плащания', 'Сметка N', 'Payment Report', 'Invoice Number', 2, GetFreeNInRelations('Payment Report',null,null,5), 'Smetka', '', '', 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_PayTypes', 'Fiscal', 'Отчет плащания', 'Фискализирана', 'Payment Report', 'Fiscal', 2, GetFreeNInRelations('Payment Report',null,null,5), 'Fiscal', '', '', 1, 2, '', '', ' select ''Не'' union all select ''Да'' ', '', 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_PayTypes', 'PTVidName', 'Отчет плащания', 'Вид', 'Payment Report', 'Вид', 2, GetFreeNInRelations('Payment Report',null,null,5), 'PTVidName', '', '', 1, 0, '', '', '', '', 120, '', 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_PayTypes', 'Valuta', 'Отчет плащания', 'Валута', 'Payment Report', 'Currency', 0, GetFreeNInRelations('Payment Report',null,null,5), 'Valuta', '', '', 1, 0, '', '', 'SELECT Symbol FROM Currencies', '', 64, '', 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_PayTypes', 'AllSumVlt', 'Отчет плащания', 'Сума влт', 'Payment Report', 'Total Currency', 0, GetFreeNInRelations('Payment Report',null,null,5), 'Sum(AllSumVlt)', '', '# ### ##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_PayTypes', 'ExchangeRate', 'Отчет плащания', 'Курс', 'Payment Report', 'Курс', 0, GetFreeNInRelations('Payment Report',null,null,5), 'ExchangeRate', '', '# ##0.0000', 1, 0, '', '', '', '', 64, '', 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_PayTypes', 'AllSum', 'Отчет плащания', 'Сума лв', 'Payment Report', 'Total LC', 2, GetFreeNInRelations('Payment Report',null,null,5), 'Sum(AllSum)', '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_PayTypes', 'DateDanEvent', 'Отчет плащания', 'Дата дан.събитие', 'Payment Report', 'DateDanEvent', 2, GetFreeNInRelations('Payment Report',null,null,5), 'DateDanEvent', '', '', 0, 1, '', '', '', '', 80, '', 0, 0, 1, '23.09.2009');
GO

Call InsertPravaSpravki('Payment Report');

GO

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

GO

create view DBA.v_PayTypes
  as select DataPay=ResMoney.DateMoneyDt,
    PayType=PayTypes.PayTypeName,
    AllSum=ResMoney.Sum*DayRates.ExchangeRate,
    AllSumVlt=ResMoney.Sum,
    Valuta=Currencies.Symbol,
    DayRates.ExchangeRate,
    PTVidName=PTV.Description,
    DocNomer=ResMoney.AccountNumber,
    Smetka=(if(ResMoney.VaucherInvoiceNo is null) and(ResMoney.InvoiceNo is not null) then(select first Inv1.ExternalNo from DBA.Invoices as Inv1 where Inv1.InvoiceNo = ResMoney.InvoiceNo)
    else if(ResMoney.InvoiceNo is null) and(ResMoney.VaucherInvoiceNo is not null) then '' || ResMoney.VaucherInvoiceNo else null endif
    endif), --Maria +, 1982
	DateDanEvent = (if(ResMoney.VaucherInvoiceNo is null) and(ResMoney.InvoiceNo is not null) then(select first DateDanEvent from DBA.Invoices as Inv1 where Inv1.InvoiceNo = ResMoney.InvoiceNo)
    else if(ResMoney.InvoiceNo is null) and(ResMoney.VaucherInvoiceNo is not null) then (select first DateDanEvent from VaucherInvoices where VaucherInvoiceNo = ResMoney.VaucherInvoiceNo) else null endif
    endif),
    Fiscal=(if ResMoney.VaucherInvoiceNo is null then(
      if(select Invoices.Fiscal from DBA.Invoices where Invoices.InvoiceNo = ResMoney.InvoiceNo) = 0 then 'Не'
      else 'Да'
      endif)
    else if ResMoney.InvoiceNo is null then(
        if(select VaucherInvoices.Fiscal from DBA.VaucherInvoices where VaucherInvoices.VaucherInvoiceNo = ResMoney.VaucherInvoiceNo) = 0 then 'Не'
        else 'Да'
        endif) endif --Maria -
    endif) from dba.PayTypes,dba.ResMoney,dba.DayRates,DBA.Currencies,dba.PayTypesVids as PTV
    where ResMoney.Payment = PayTypes.PayTypeN
    and ResMoney.Currency = DayRates.KodCurrency and ResMoney.DateMoneyDt = DayRates.DateDt
    and ResMoney.Currency = Currencies.KodCurrency
    and ResMoney.DateMoneyDt >= bvrData1 and ResMoney.DateMoneyDt <= bvrData2
    and ResMoney.PTVid = PTV.N and(
    ((ResMoney.VaucherInvoiceNo is null) and(select Registers.IntStojnost from DBA.Registers where Registers.RegisterName = 'VTDCommonCounter') = 1) or(
    (select Registers.IntStojnost from DBA.Registers where Registers.RegisterName = 'VTDCommonCounter') = 0))
-- gnikolov - добавено съобразяване с регистър VTDCommonCounter поръчка 2008