var
  Tat_FK_PF_TblTemplateFileName: String;
  Tat_FK_PF_TblFileName: String;
  Tat_FK_PF_OutFileName: String;
  Tat_FK_PF_param_DirOut: String;
  Tat_FK_PF_LstPayroll: TStringList;
  Tat_FK_PF_Tbl: TTable;
  Tat_FK_PF_DirDefaultOut: string;    // путь по умолчанию
  Tat_FK_PF_DirDefaultTmp: string;    // путь по умолчанию
  LstErrors: TStringList;
  i, n: Integer;

function CheckPathOut( xPath: string): string;
begin
  if (TRIM(xPath) = '')
    then Result := Tat_FK_PF_DirDefaultOut
    else Result := xPath;
end;

function CheckPathTmp( xPath: string): string;
begin
  if (TRIM(xPath) = '')
    then Result := Tat_FK_PF_DirDefaultTmp
    else Result := xPath;
end;

procedure MakeReport1(Report: variant);
var
  ComReportCnfg: Variant;
  ComReportCnfg_FileName: String;
  i: Integer;
begin
  ComReportCnfg := CreateOleObject('Microsoft.XmlDom');
  ComReportCnfg.Load(Report.documentElement.getAttribute('CONFIG'));
  for i := 0 to ComReportCnfg.documentElement.childNodes.Length - 1 do
  begin
    if (ComReportCnfg.documentElement.childNodes.item[i].getAttribute('value') <> '')
      and (ComReportCnfg.documentElement.childNodes.item[i].getAttribute('value') <> 'Tatarstan.cfg') then
    begin
      ComReportCnfg_FileName := my_getFullPath(report.documentElement.getAttribute('CONFIG'),
      ComReportCnfg.documentElement.childNodes.item[i].getAttribute('value'));
      if not FileExists(ComReportCnfg_FileName) then
        ShowMessage(Format('Отчет "%s" не сформирован: не найден файл %s',
        [ComReportCnfg.documentElement.childNodes.item[i].getAttribute('name'),
          ComReportCnfg_FileName]))
      else
        ObjectReport(ComReportCnfg_FileName, Report);
    end;
  end;
end;

procedure ObjectReport(ConfigFileName: string; Report: variant);
var
 ObjReportCnfg: Variant;
 param_ObjName,
 param_TmplFileName,
 param_OutDir,
 param_SessionReport,
 ReportSaveAs: String;
 i: integer;
 
begin
  ObjReportCnfg := CreateOleObject('Microsoft.XmlDom');
  ObjReportCnfg.Load(ConfigFileName);
  for i := 0 to ObjReportCnfg.documentElement.getElementsbyTagName('NODE').Length - 1 do
    with ObjReportCnfg.documentElement.getElementsbyTagName('NODE').item[i] do
      case UpperCase(getAttribute('name')) of
        UpperCase('Объект'): param_ObjName := getAttribute('value');
        UpperCase('Шаблон отчета'): param_TmplFileName :=
          my_getFullPath(ConfigFileName, getAttribute('value'));
        UpperCase('Исходящий каталог'): param_OutDir:= my_getFullPath(ConfigFileName, getAttribute('value'));
        UpperCase('Отчет по сессии'): param_SessionReport:= getAttribute('value');
      end;
  ForceDirectories(param_OutDir);
  //Спец поля
  case UpperCase(param_ObjName) of
    UpperCase('Ростов'): SpecialFields_Rostov(Report);
  end;
  if param_SessionReport = 'Да' then
    for i := 0 to report.getElementsByTagName('SESSION').length - 1 do
    begin
    //Имя файла
      with Report.getElementsByTagName('SESSION').item[i] do
        case UpperCase(param_ObjName) of
          UpperCase('Ростов'), UpperCase('Челябинская область'):
            ReportSaveAs:= param_OutDir + '\' +
              FileName_Rostov(getElementsByTagName('PP').item[0].getAttribute('KOD_TOFK_PAY'),
              getAttribute('DATE'),
              getAttribute('EXCH_PACKET'));
        end;
      WordReport(param_TmplFileName, ReportSaveAs, Report.getElementsByTagName('SESSION').item[i]);
    end
    else
    begin
      with report.getElementsByTagName('SESSION').item[0] do
      //Имя файла
        case UpperCase(param_ObjName) of
          UpperCase('Ростов'), UpperCase('Челябинская область'):
            ReportSaveAs:= param_OutDir + '\' +
              FileName_Rostov(getElementsByTagName('PP').item[0].getAttribute('KOD_TOFK_PAY'),
              getAttribute('DATE'),
              getAttribute('EXCH_PACKET'));
        end;
      WordReport(param_TmplFileName, ReportSaveAs, Report);
    end;
    with GetActiveOleObject('Word.Application') do
    begin
      Visible := True;
      ActiveDocument.Activate;
    end;
end;

procedure WordReport(ReportTemplate, ReportSaveAs: String; Xml: Variant);
var
  Word: Variant;
begin
  try
    Word := GetActiveOleObject('Word.Application');
  except
    Word := CreateOleObject('Word.Application');
  end;
  Word.Documents.Add(ReportTemplate);
  Word.Application.Run('Module.Start', Xml.xml);
  Word.ActiveDocument.SaveAs(ReportSaveAs, 6);
end;

//==============================================================================
//                                     Rostov
//==============================================================================

procedure SpecialFields_Rostov(Report: Variant);
var
  i: integer;
begin
  //Наименование ОФК
  for i := 0 to report.getElementsByTagName('PP').Length - 1 do
    Report.getElementsByTagName('PP').item[i].setAttribute('SPEC_OFK_DESCRIPTION',
      QueryField('Select * From Org Where Name = ?',
        [Report.getElementsByTagName('PP').item[i].getAttribute('KOD_TOFK_PAY')],
        'CAPTION'));
end;

function FileName_Rostov(Kod_OFK, ReportDate, PacketNumber: String): String;
begin
  Result := 'Reestr' + Copy(Kod_OFK, 3, 2) +
    FormatDateTime('yymmdd', StrToDate(ReportDate)) +
    StringReplace(Format('%3d',[StrToInt(PacketNumber)]), ' ', '0') +
    '.RTF';
end;

//==============================================================================
//==============================================================================

//Преобразует относительный путь в полный
function my_getFullPath(RelativePath, Path: string): string;
begin
 if (Pos(':', Path) > 0)
  or (Copy(Path, 1, 2) = '\\') then
  Result := Path
 else
  Result := ExtractFilePath(RelativePath) + Path;
end;

{################################################################################################}
{################################################################################################}
{#########################                                               ########################}
{#######################                                                  #######################}
{######################     Функции, формирующие отчет для Татарстана     #######################}
{#######################                                                  #######################}
{#########################                                               ########################}
{################################################################################################}
{################################################################################################}

procedure MakeReportTat(XmlIn: Variant);
begin
  Tat_FK_PF_Main(XmlIn, XmlIn.documentElement.getAttribute('F_OUTFILENAME'));
end;

function GetFinOrgan(taxcode,kpp,account: String): Variant;
var XML:Variant;
    sql: String;
begin
  XML:=CreateOleObject('Microsoft.XmlDom');
  if ((Length(Trim(account)) + Length(Trim(taxcode)) + Length(Trim(kpp))) > 0) then
  begin
    try
      sql := ' select distinct fo.caption as caption ' +
      '       ,fo.taxcode as taxcode ' +
      '       ,fo.caption as id ' +
      '   from org fo ' +
      '        join orgaccount oacc on (oacc.keeper_id = fo.id) ' +
      //'        join orgaccount oacc on (oacc.org_id = fo.id) ' +
      '        join org o on (o.id = oacc.org_id) ' +
      '  where oacc.orgacctype_id = 2 ';
      if (Length(Trim(account)) > 0) then sql := sql + ' and oacc.account_number like ' + '''' + '%' + Trim(account) + '%' + '''';
      if (Length(Trim(taxcode)) > 0) then sql := sql + ' and o.taxcode like ' + '''' + '%' + Trim(taxcode) + '%' + '''';
      if (Length(Trim(kpp)) > 0) then sql := sql + ' and o.kpp like ' + '''' + '%' + Trim(kpp) + '%' + '''';
        XML.LoadXML(RunQuery(sql));
    except
      Raise('Ошибка при выполнении запроса для поиска финоргана по ИНН, КПП и счету: ' +  LastException.Message);
    end;
  end;
  Result := XML;
end;

Function AddFirstZero(S:string; i:integer):string;
begin
 Result:=S;
 while length(Result)<i do
  Result:='0'+Result;
end;

// ----- Функция возвращает строку, добавляющую необходимое количество нулей в начало строки -------//
// Первый параметр (original) - исходная строка, к которой надо добавить нули
// Второй парамет (totalCharCount) - необходимая длина возвращаемой строки
function addZeroFirst(original: String; totalCharCount: Integer): String;
var
  curCountDeficit, i: Integer;
  curStr: String;
begin
  curStr := '';
  curCountDeficit := 0;
  if (totalCharCount > length(original)) then
  begin
    curCountDeficit := totalCharCount - length(original);
    for i := 1 to  curCountDeficit do
      curStr := curStr + '0';
  end;
  curStr := curStr + original;
  Result := curStr;
end;

//-------- Функция строит строит ClientDataSet для выбора организации из входящего XML, заносит его значения в грид. Возвращает последний параметр.
Function ChooseRec(XMLDoc:Variant; tag:String; withTaxcode:boolean; acaption:String): String;
var
  Form : TForm;
  cdset : TClientDataSet;
  i : Integer;
begin
  Result := '';
  cdset := TClientDataSet.Create(nil);
  cdset.Filtered := True;
  cdset.FilterOptions := foCaseInsensitive;
  with cdset do
  begin
    if (withTaxcode) then
    begin
      with FieldDefs.AddFieldDef do
      begin
        DataType := ftString;
        Size := 12;
        Name := 'taxcode';
      end;
    end;
    with FieldDefs.AddFieldDef do
    begin
      DataType := ftString;
      Size := 255;
      Name := 'orgname';
    end;
    with FieldDefs.AddFieldDef do
    begin
      DataType := ftString;
      Size := 255;
      Name := 'orgid';
    end;
    CreateDataSet;
  end;
  with cdset do
  begin
    for i := 0 to (XMLDoc.getElementsByTagName(tag).Length - 1) do
    begin
      Append;
      if (withTaxcode) then FieldByName('taxcode').Value := XMLDoc.getElementsByTagName(tag).Item[i].getAttribute('TAXCODE');
      FieldByName('orgname').Value := XMLDoc.getElementsByTagName(tag).Item[i].getAttribute('CAPTION');
      FieldByName('orgid').Value := XMLDoc.getElementsByTagName(tag).Item[i].getAttribute('ID');
      Post;
    end;
  end;
  Form := FRec(aCaption, cdset, withTaxcode);
  If Form.ShowModal=1 then
  begin
    Result := Trim(cdset.FieldByName('orgid').Value);
    cdset.close;
  end;
end;

function FRec(aCaption:string; cdset:TClientDataSet; withTaxcode:boolean):TForm;
var
  FRec:TForm;
  bok,bcancel : TButton;
  grid : TDBgrid;
  ds : TDataSource;
begin
  FRec := TForm.create(nil);
  with FRec do
  begin
    Left := 318;
    Top := 329;
    BorderStyle := bsDialog;
    Caption := aCaption;
    ClientHeight := 298;
    if (withTaxcode) then
      ClientWidth := 626
    else
      ClientWidth := 505;
    Position := poScreenCenter;
    Color := clBtnFace;
    Font.Color := clWindowText;
    Font.Height := -11;
    Font.Name := 'MS Sans Serif';
    PixelsPerInch := 96;
    BorderIcons := nil;
  end;
  bOk:=TButton.Create(FRec);
  with bOk do
  begin
    Parent := FRec;
    Left := 56;
    Top := 256;
    Width := 89;
    Height := 33;
    Caption := 'Выбрать';
    TabOrder := 1;
    ModalResult := 1;
    TabOrder := 0;
  end;
  bCancel:=TButton.Create(FRec);
  with bCancel do
  begin
    Parent:=FRec;
    if (withTaxcode) then
      Left := 496
    else
      Left := 394;
    Top := 256;
    Width := 91;
    Height := 33;
    Caption := 'Отказаться';
    ModalResult := 2;
    TabOrder := 1;
  end;
  ds := TDataSource.Create(FRec);
  with ds do
  begin
    DataSet := cdset;
  end;
   grid := TDBGrid.Create(FRec);
  with grid do
  begin
    Parent := FRec;
    Left := 0;
    Top := 0;
    Width := 625;
    Height := 241;
    TabOrder := 2;
    DataSource := ds;
    TitleFont.Color := clWindowText;
    TitleFont.Height := -11;
    TitleFont.Name := 'MS Sans Serif';
    if (withTaxcode) then
    begin
      Columns.Add;
      Columns.items[0].Expanded := False;
      Columns.items[0].FieldName := 'taxcode';
      Columns.items[0].Title.Alignment := taCenter;
      Columns.items[0].Title.Caption := 'ИНН';
      Columns.items[0].Width := 102;
      Columns.items[0].Visible := True;
      Columns.Add;
      Columns.items[1].Expanded := False;
      Columns.items[1].FieldName := 'orgName';
      Columns.items[1].Title.Alignment := taCenter;
      Columns.items[1].Title.Caption := 'Наимнование';
      Columns.items[1].Width := 490;
      Columns.items[1].Visible := True;
      Columns.Add;
      Columns.items[2].Expanded := False;
      Columns.items[2].FieldName := 'orgid';
      Columns.items[2].Width := 0;
      Columns.items[2].Visible := False;
    end
    else
    begin
      Columns.Add;
      Columns.items[0].Expanded := False;
      Columns.items[0].FieldName := 'orgName';
      Columns.items[0].Title.Alignment := taCenter;
      Columns.items[0].Title.Caption := 'Наимнование';
      Columns.items[0].Width := 490;
      Columns.items[0].Visible := True;
      Columns.Add;
      Columns.items[1].Expanded := False;
      Columns.items[1].FieldName := 'orgid';
      Columns.items[1].Width := 0;
      Columns.items[1].Visible := False;
    end;
  end;

  Result:= FRec;
end;

function convertDateStr(original: String):String;
var
  curStr : String;
begin
  curStr:='';
  curStr := curStr + copy(original,9,2) + '.' + copy(original,6,2) + '.' + copy(original,1,4);
  Result := curStr;
end;

//------ Функция возвращает XML документ с айдишником платежки, серией и номером чека
        // Первый параметр - айдишник платежки.
function GetZajavkaData(payorderId: String):Variant;
var XML:Variant;
    sql: String;
begin
  XML:=CreateOleObject('Microsoft.XmlDom');
  if (payorderId<>'') then
  begin
    try
      sql := ' select bo.cheque_ser as cheque_ser, ' +
      '        bo.cheque_num as cheque_num ' +
      '   from payorder p ' +
      '        left join document dp on (p.document_id = dp.id) ' +
      '        left join document dz on (dz.id = dp.parent_id) ' +
      '        left join budgorder bo on (bo.document_id = dz.id) ' +
      '  where ((bo.cheque_ser is not null) or (bo.cheque_num is not null)) ' +
      '        and p.id = ' + payorderId;
      XML.LoadXML(RunQuery(sql));

      if XML.GetElementsByTagName('ROW').Length = 0 then
      begin
        sql := ' select bo.cheque_ser as cheque_ser, ' +
        '        bo.cheque_num as cheque_num ' +
        '   from payorder p ' +
        '        left join document dp on (p.document_id = dp.id) ' +
        '        left join document dz on (dz.id = dp.parent_id) ' +
        '        left join chequeorder bo on (bo.document_id = dz.id) ' +
        '  where ((bo.cheque_ser is not null) or (bo.cheque_num is not null)) ' +
        '        and p.id = ' + payorderId;
        XML.LoadXML(RunQuery(sql));
      end;
    except
      Raise('Ошибка при выполнении запроса для поиска серии и номера чека из заявки на оплату расходов: ' +  LastException.Message);
    end;
  end;
  Result := XML;
end;

function XmlStrToDateStr(Str: String): String;
var
  dt: TDateTime;
begin
  dt := EncodeDate(StrToInt(Copy(Str, 1, 4)),
    StrToInt(Copy(Str, 6, 2)),
    StrToInt(Copy(Str, 9, 2)));
  Result := FormatDateTime('dd.mm.yyyy', dt);
end;

function ChangeFileExt(FileName: String; NewExt: String): String;
var
  n: Integer;
begin
  n := Length(FileName);
  while n > 0 do
  begin
    if FileName[n] = '.' then Break;
    n := n - 1;
  end;
  if n = 0 then Result := FileName
  else Result := Copy(FileName, 1, n) + NewExt;
end;

//Возвращает признак галок "счет иного получателя" или "счет для выдачи наличных"
Function AccNalPriznak(Acc_ID: string): boolean;
var xml: variant;
    sql: string;
begin
 Result:= false;
 if Acc_ID = '' then Acc_ID := '0';
 xml:= CreateOleObject('Microsoft.XmlDom');
 sql:= 'Select Cash_Flag, Other_Receiver_Flag '+
       ' from OrgAccount '+
       ' where id = ' + Acc_ID;
 xml.LoadXML(RunQuery(SQL));
 if xml.documentElement.childNodes.Length = 0 then Exit;
 with xml.documentElement.childNodes.item[0] do
  if (getAttribute('CASH_FLAG') = '1') or (getAttribute('OTHER_RECEIVER_FLAG') = '1') then
   Result:= true;
end;

function Tat_AccNalPriznakBudget(Acc_ID: String; BudgetId: String): Boolean;
var
  sql: String;
  Xml: Variant;
begin
  Result := False;
  if Acc_ID = '' then Exit;
  sql := ' select oa.id ' +
         ' from orgaccount oa ' +
         ' inner join budget b on (b.org_id = oa.org_id) ' +
         ' where b.id = ' + BudgetId +
         ' and oa.id = ' + Acc_Id +
         ' and oa.cash_flag = 1 ';
  Xml := CreateOleObject('Microsoft.XmlDom');
  try
    Xml.LoadXML(RunQuery(sql));
    if Xml.DocumentElement.ChildNodes.Length <> 0 then Result := True;
  finally
    Xml := nil;
  end;
end;

// Убирает конечный символ '\' в строке (ликвидирует многовариантность задания
// имён каталогов).
function RemoveTerminalSlash(str: String): String;
var
  n: Integer;
begin
  n := Length(str);
  if (n = 0) or (str[n] <> '\') then Result := str
  else Result := Copy(str, 1, n - 1);
end;

function YYYY_MM_DD_ToDate(Str: String): TDateTime;
begin
  try
    Result := EncodeDate(StrToInt(Copy(Str, 1, 4)),
      StrToInt(Copy(Str, 6, 2)),
      StrToInt(Copy(Str, 9, 2)));
  except
    Raise('YYYY_MM_DD: Не удается преобразовать строку ' + Str +
      ' в дату');
  end;
end;

//------ Функция возвращает XML документ с найденными по запросу организациями и их счетами
     // Первый параметр - номер счета.
     // второй параметр - айди типа счета организации.
     // третий параметр - ИНН организации
     // четрвертый параметр - КПП организации
     // пятый параметр - айди организации
     // шестой параметр - айди роли организации
  function GetOrgAndOrgaccount(account_number,orgacctype_id,taxcode,kpp,id,orgrole_id: String):Variant;
  var XML:Variant;
      sql: String;
  begin
    XML:=CreateOleObject('Microsoft.XmlDom');
    try
      sql := ' select o.id org_id ' +
             '      ,o.caption ' +
             '      ,o.description ' +
             '      ,o.taxcode ' +
             '      ,o.kpp ' +
             '      ,o.name ' +
             '      ,a.id acc_id ' +
             '      ,a.account_number ' +
             '      ,a.orgacctype_id ' +
             '      ,b.bic ' +
             '      ,b.coraccount ' +
             '      ,b.bank_type ' +
             '      ,b.city_type ' +
             '      ,b.city_name ' +
             '      ,b.description bank_name ' +
             ' from org o ' +
             '      left join orgaccount a on (a.org_id = o.id) ' +
             '      left join bank b On (b.id=a.bank_id) ' +
             '      left join orgroles ors on (ors.org_id = o.id) ' +
             ' where 0 = 0 ' +
             '       and o.id > 0 ' +
             '       and o.close_date is null ' +
             '       and a.close_date is null ';
             if length(Trim(id)) > 0 then
               sql := sql + ' and o.id = ' + trim(id);
             if length(trim(taxcode)) > 0 then
               sql := sql + ' and o.taxcode = ' + '''' +  trim(taxcode) + '''';
             if length(trim(kpp)) > 0 then
               sql := sql + ' and o.kpp = ' + '''' + trim(kpp) + '''';
             if length(trim(account_number)) > 0 then
               sql := sql + ' and a.account_number = ' + '''' + trim(account_number) + '''';
             if length(trim(orgacctype_id)) > 0 then
               sql := sql + ' and a.orgacctype_id = ' + trim(orgacctype_id);
             if length(trim(orgrole_id)) > 0 then
               sql := sql + ' and ors.orgrole_id = ' + trim(orgrole_id);
      XML.LoadXML(RunQuery(sql));
    except
      Raise('Ошибка при выполнении запроса для поиска организации и ее счета: ' +  LastException.Message);
    end;
    Result := XML;
  end;

//----- функция возвращает строку, в которой символ, переданный в качестве первого параметра заменен на символ, переденный в качестве второго параметра
       // Первый параметр - исходная строка
  function ReplaceCharAtString(S: String; fromChar: Char; toChar : Char): String;
  var
    i, n: Integer;
    str: String;
  begin
    str := '';
    n := Length(S);
    for i := 1 to n do
      if S[i] = fromChar then
        str := str + toChar
      else
        str := str + S[i];
    Result := str;
  end;

//------ Функция возвращает XML документ со сведениями для построения отчета по выгрузке в акбарс
        // Первый параметр - строка айдишников платежных поручений.
  function GetReportData(payorderIds: String):Variant;
  var XML:Variant;
      sql: String;
  begin
    XML:=CreateOleObject('Microsoft.XmlDom');
    if (payorderIds<>'') then
    begin
      try
        sql := ' select po.pay_account as pay_account, ' +
        '        pr.doc_date as rasp_date, ' +
        '        pr.doc_number as rasp_number, ' +
        '        count(po3.id) as rasp_count, ' +
        '        sum(po3.amount) as rasp_amount, ' +
        '        count(po1.id) as pp_count, ' +
        '        sum(po1.amount) as pp_amount, ' +
        '        count(po2.id) as kass_count, ' +
        '        sum(po2.amount) as kass_amount ' +
        '   from document doc1 ' +
        '        left join payorder po on (po.document_id = doc1.id) ' +
        '        left join payorder po1 on (po1.document_id = doc1.id and po1.bankoper_id<>3) ' +
        '        left join payorder po2 on (po2.document_id = doc1.id and po2.bankoper_id = 3) ' +
        '        left join payrolldoc prd on ((po1.id = prd.doc_id or po2.id = prd.doc_id) and (prd.payroll_id in ' +
        '                                     (select pr1.id from payroll pr1 ' +
        '                                       where prd.payroll_id = pr1.id ' +
        '                                             and pr1.dispstatus_id = 10))) ' +
        '        left join payroll pr on (prd.payroll_id = pr.id) ' +
        '        left join payorder po3 on (po3.id = prd.doc_id) ' +
        '  where doc1.id in (' + payorderIds + ') ' +
        '        group by pr.doc_date, ' +
        '                 pr.doc_number, ' +
        '                 po.pay_account ' +
        '        order by po.pay_account ';
          XML.LoadXML(RunQuery(sql));
      except
        Raise('Ошибка при выполнении запроса для построения отчета: ' +  LastException.Message);
      end;
    end;
    Result := XML;
  end;

function Tat_GetCounter(Dir: String): String;
var
  n: Integer;
  str: String;
  FileName: String;
  strs: TStringList;
begin
  FileName := Dir + '\TatCnt.txt';
  strs := TStringList.Create;
  try
    try
      strs.LoadFromFile(FileName);
      n := StrToInt(strs[0]);
    except
      n := 0;
    end;
    if n >= 9999 then n := 0 else n := n + 1;
    strs.Clear;
    str := IntToStr(n);
    strs.Add(str);
    strs.SaveToFile(FileName);
  finally
    strs.Free;
  end;
  Result := AddZeroFirst(str, 4);
end;

function Tat_GetDateCounter(Dir: String; dt: TDateTime): String;
var
  i, n: Integer;
  Index: Integer;
  Value: Integer;
  bFound: Boolean;
  str, curstr: String;
  FileName: String;
  strs: TStrings;
begin
  bFound := False;
  FileName := Dir + '\TatDateCnt.txt';
  str := FormatDateTime('ddmmyy', dt);
  strs := TStringList.Create;
  try
    try strs.LoadFromFile(FileName) except end;
    n := strs.Count;
    i := 0;
    while i < 50 do
    begin
      if i >= n then Break;
      curstr := strs[i];
      if Copy(curstr, 1, 6) = str then
      begin
        bFound := True;
        Index := i;
        try
          Value := StrToInt(Copy(curstr, 8, Length(curstr)));
        except
          Value := 1;
        end;
      end;
      i := i + 1;
    end;
    if not bFound then
    begin
      if i >= 50 then begin Index := 0; Value := 1; end
      else
      begin
        Index := strs.Add(str + ' 1');
        Value := 1;
      end;
    end;
    n := Value + 1;
    if n > 90 then n := 1;
    strs[Index] := str + ' ' + IntToStr(n);
    strs.SaveToFile(FileName);
  finally
    strs.Free;
  end;
  Result := AddZeroFirst(IntToStr(Value * 10), 3);
end;

//------ Функция возвращает XML документ со сведениями о бюджетной строке
        // Первый параметр - ID организации-владельца сметы - обязательный
        // Второй параметр - КФСР
        // Третий параметр - КЦСР
        // Четвертый параметр - КВР
        // Пятый параметр - КЕСР
        // Шестой параметр - КВСР (КАДМР)
        // Седьмой параметр - КДФ
        // Восьмой параметр - КДЕ
        // Девятый параметр - КДР
        // Десятый параметр - тип сметы
  function getExpBudgetLine(estOwnerId,kfsr,kcsr,kvr,kesr,kadmr,kdf,kde,kdr,estkindId: String):Variant;
  var XML:Variant;
      sql: String;
  begin
    XML:=CreateOleObject('Microsoft.XmlDom');
    if (Length(Trim(estOwnerId)) > 0) then
    begin
      try
        sql := ' select  exp.* ' +
        '         ,est.caption as est_caption ' +
        '         ,org_rec.caption as rec_caption ' +
        '   from  estimate est, org o,expbudget exp ' +
        '         left join org org_rec on (org_rec.id = exp.recipient_id) ' +
        '   where 0 = 0 ' +
        '         and exp.estimate_id = est.id ' +
        '         and est.org_id = o.id ' +
        '         and o.id = ' + estOwnerId;
        if (Length(Trim(kfsr)) > 0) then sql := sql + ' and exp.kfsr_code = ' + kfsr;
        if (Length(Trim(kcsr)) > 0) then sql := sql + ' and exp.kcsr_code = ' + kcsr;
        if (Length(Trim(kvr)) > 0) then sql := sql + ' and exp.kvr_code = ' + kvr;
        if (Length(Trim(kesr)) > 0) then sql := sql + ' and exp.kesr_code = ' + kesr;
        if (Length(Trim(kadmr)) > 0) then sql := sql + ' and exp.kadmr_code = ' + kadmr;
        if (Length(Trim(kdf)) > 0) then sql := sql + ' and exp.kdf_code = ' + kdf;
        if (Length(Trim(kde)) > 0) then sql := sql + ' and exp.kde_code = ' + kde;
        if (Length(Trim(kdr)) > 0) then sql := sql + ' and exp.kdr_code = ' + kdr;
        if (Length(Trim(estkindId)) > 0) then sql := sql + ' and est.estkind_id = ' + estkindId;
          XML.LoadXML(RunQuery(sql));
      except
        Raise('Ошибка при выполнении запроса для поиска бюджетной строки: ' +  LastException.Message);
      end;
    end;
    Result := XML;
  end;

//------ Функция возвращает XML документ со сведениями о бюджетной строке
        // Первый параметр - ID организации-бюджетополучателя - обязательный
        // Второй параметр - КФСР
        // Третий параметр - КЦСР
        // Четвертый параметр - КВР
        // Пятый параметр - КЕСР
        // Шестой параметр - КВСР (КАДМР)
        // Седьмой параметр - КДФ
        // Восьмой параметр - КДЕ
        // Девятый параметр - КДР
        // Десятый параметр - тип сметы
  function GetExpBudgetLineByRec(RecipientId,kfsr,kcsr,kvr,kesr,kadmr,kdf,kde,kdr,estkindId: String):Variant;
  var XML:Variant;
      sql: String;
  begin
    XML:=CreateOleObject('Microsoft.XmlDom');
    if (Length(Trim(RecipientId)) > 0) then
    begin
      try
        sql := ' select  exp.* ' +
        '         ,est.caption as est_caption ' +
        '         ,org_rec.caption as rec_caption ' +
        '   from  estimate est, org o,expbudget exp ' +
        '         left join org org_rec on (org_rec.id = exp.recipient_id) ' +
        '   where 0 = 0 ' +
        '         and exp.estimate_id = est.id ' +
        '         and est.org_id = o.id ' +
        '         and est.recipient_id = ' + RecipientId;
        if (Length(Trim(kfsr)) > 0) then sql := sql + ' and exp.kfsr_code = ' + kfsr;
        if (Length(Trim(kcsr)) > 0) then sql := sql + ' and exp.kcsr_code = ' + kcsr;
        if (Length(Trim(kvr)) > 0) then sql := sql + ' and exp.kvr_code = ' + kvr;
        if (Length(Trim(kesr)) > 0) then sql := sql + ' and exp.kesr_code = ' + kesr;
        if (Length(Trim(kadmr)) > 0) then sql := sql + ' and exp.kadmr_code = ' + kadmr;
        if (Length(Trim(kdf)) > 0) then sql := sql + ' and exp.kdf_code = ' + kdf;
        if (Length(Trim(kde)) > 0) then sql := sql + ' and exp.kde_code = ' + kde;
        if (Length(Trim(kdr)) > 0) then sql := sql + ' and exp.kdr_code = ' + kdr;
        if (Length(Trim(estkindId)) > 0) then sql := sql + ' and est.estkind_id = ' + estkindId;
          XML.LoadXML(RunQuery(sql));
      except
        Raise('Ошибка при выполнении запроса для поиска бюджетной строки: ' +  LastException.Message);
      end;
    end;
    Result := XML;
  end;

function RemoveSpaces(str: String): String;
var
  i, n: Integer;
begin
  Result := '';
  n := Length(str);
  for i := 1 to n do
    if str[i] <> ' ' then Result := Result + str[i];
end;

function FExpBudgetRows(aCaption:string; cdset:TClientDataSet):TForm;
  var
    FormExpBudgetRows:TForm;
    bok,bcancel : TButton;
    grid : TDBgrid;
    ds : TDataSource;
  begin
    FormExpBudgetRows := TForm.create(nil);
    with FormExpBudgetRows do
    begin
      Left := 318;
      Top := 329;
      BorderStyle := bsDialog;
      Caption := aCaption;
      ClientWidth := 759;
      ClientHeight := 298;
      Position := poScreenCenter;
      Color := clBtnFace;
      Font.Color := clWindowText;
      Font.Height := -11;
      Font.Name := 'MS Sans Serif';
      PixelsPerInch := 96;
      BorderIcons := nil;
    end;

    bOk:=TButton.Create(FormExpBudgetRows);
    with bOk do
    begin
      Parent := FormExpBudgetRows;
      Left := 56;
      Top := 256;
      Width := 89;
      Height := 33;
      Caption := 'Выбрать';
      TabOrder := 1;
      ModalResult := 1;
      TabOrder := 0;
    end;

    bCancel:=TButton.Create(FormExpBudgetRows);
    with bCancel do
    begin
      Parent:=FormExpBudgetRows;
      Left := 615;
      Top := 256;
      Width := 91;
      Height := 33;
      Caption := 'Отказаться';
      ModalResult := 2;
      TabOrder := 1;
    end;

    ds := TDataSource.Create(FormExpBudgetRows);
    with ds do
    begin
      DataSet := cdset;
    end;

    grid := TDBGrid.Create(FormExpBudgetRows);
    with grid do
    begin
      Parent := FormExpBudgetRows;
      Left := 0;
      Top := 0;
      Width := 758;
      Height := 241;
      TabOrder := 2;
      DataSource := ds;
      TitleFont.Color := clWindowText;
      TitleFont.Height := -11;
      TitleFont.Name := 'MS Sans Serif';
      Columns.Add;
      Columns.items[0].Expanded := False;
      Columns.items[0].FieldName := 'est_caption';
      Columns.items[0].Title.Alignment := taCenter;
      Columns.items[0].Title.Caption := 'Смета';
      Columns.items[0].Width := 200;
      Columns.items[0].Visible := True;
      Columns.Add;
      Columns.items[1].Expanded := False;
      Columns.items[1].FieldName := 'rec_caption';
      Columns.items[1].Title.Alignment := taCenter;
      Columns.items[1].Title.Caption := 'Бюджетополучатель';
      Columns.items[1].Width := 200;
      Columns.items[1].Visible := True;
      Columns.Add;
      Columns.items[2].Expanded := False;
      Columns.items[2].FieldName := 'kfsr';
      Columns.items[2].Title.Alignment := taCenter;
      Columns.items[2].Title.Caption := 'КФСР';
      Columns.items[2].Width := 37;
      Columns.items[2].Visible := True;
      Columns.Add;
      Columns.items[3].Expanded := False;
      Columns.items[3].FieldName := 'kcsr';
      Columns.items[3].Title.Alignment := taCenter;
      Columns.items[3].Title.Caption := 'КЦСР';
      Columns.items[3].Width := 47;
      Columns.items[3].Visible := True;
      Columns.Add;
      Columns.items[4].Expanded := False;
      Columns.items[4].FieldName := 'kvr';
      Columns.items[4].Title.Alignment := taCenter;
      Columns.items[4].Title.Caption := 'КВР';
      Columns.items[4].Width := 30;
      Columns.items[4].Visible := True;
      Columns.Add;
      Columns.items[5].Expanded := False;
      Columns.items[5].FieldName := 'kesr';
      Columns.items[5].Title.Alignment := taCenter;
      Columns.items[5].Title.Caption := 'КЭС';
      Columns.items[5].Width := 30;
      Columns.items[5].Visible := True;
      Columns.Add;
      Columns.items[6].Expanded := False;
      Columns.items[6].FieldName := 'kadmr';
      Columns.items[6].Title.Alignment := taCenter;
      Columns.items[6].Title.Caption := 'КАДМ';
      Columns.items[6].Width := 37;
      Columns.items[6].Visible := True;
      Columns.Add;
      Columns.items[7].Expanded := False;
      Columns.items[7].FieldName := 'kdf';
      Columns.items[7].Title.Alignment := taCenter;
      Columns.items[7].Title.Caption := 'Доп.ФК';
      Columns.items[7].Width := 45;
      Columns.items[7].Visible := True;
      Columns.Add;
      Columns.items[8].Expanded := False;
      Columns.items[8].FieldName := 'kde';
      Columns.items[8].Title.Alignment := taCenter;
      Columns.items[8].Title.Caption := 'Доп.ЭК';
      Columns.items[8].Width := 45;
      Columns.items[8].Visible := True;
      Columns.Add;
      Columns.items[9].Expanded := False;
      Columns.items[9].FieldName := 'kdr';
      Columns.items[9].Title.Alignment := taCenter;
      Columns.items[9].Title.Caption := 'Доп.КР';
      Columns.items[9].Width := 45;
      Columns.items[9].Visible := True;
      Columns.Add;
      Columns.items[10].Expanded := False;
      Columns.items[10].FieldName := 'est_id';
      Columns.items[10].Width := 0;
      Columns.items[10].Visible := False;
      Columns.items[10].Expanded := False;
      Columns.items[10].FieldName := 'rec_id';
      Columns.items[10].Width := 0;
      Columns.items[10].Visible := False;
    end;
    Result:= FormExpBudgetRows;
  end;

//------ Функция возвращает XML документ с найденными по запросу постановлениями
     // Первый параметр - дата постановления.
     // второй параметр - номер постановления.
  function GetDirectionByNumberAndDate(dir_doc_number,dir_doc_date: String):Variant;
  var XML:Variant;
      sql: String;
  begin
    XML:=CreateOleObject('Microsoft.XmlDom');
    if ((dir_doc_number <> '') or (dir_doc_date <> '')) then
    begin
      try
        if (dir_doc_date <> '') then
          sql := ' select ';
        sql := sql + ' id as id ' +
                     ',doc_number as doc_number ' +
                     ',doc_date as doc_date ' +
                     ',purpose as purpose ' +
                     ',amount as amount ' +
                     ' from direction dir ' +
                     ' where 0 = 0 ';
        if (dir_doc_number<>'') then
          sql := sql + '       and dir.doc_number = ' + '''' + Trim(dir_doc_number) + '''';
        if (dir_doc_date<>'') then
          sql := sql + '       and dir.doc_date = ?';
        if (dir_doc_date <> '') then
          XML.LoadXML(RunQueryParams(sql,[FormatDateTime('yyyy-mm-dd', StrToDate(dir_doc_date))]))
        else
          XML.LoadXML(RunQuery(sql));
      except
        Raise('Ошибка при выполнении запроса для поиска постановления по дате и номеру: ' +  LastException.Message);
      end;
    end;
    Result := XML;
  end;

function GetOrgRoleName(OrgRole: String): String;
var
  sql: String;
  Xml: Variant;
begin
  sql := ' select caption from orgrole where id = ' + OrgRole;
  Xml := CreateOleObject('Microsoft.XmlDom');
  try
    Xml.LoadXml(RunQuery(sql));
    if Xml.GetElementsByTagName('ROW').Length = 0 then Result := ''
    else
      Result := Xml.GetElementsByTagName('ROW').Item[0].GetAttribute('CAPTION');
  finally
    Xml := nil;
  end;
end;

//----- Функция возвращает тип boolean, характеризующий наличие кодов бюджетной классификации в справочнике (true - код найден, false - код не найден)
      // Первый параметр - имя таблицы справочника
      // Второй параметр - строка с кодом, который нужно найти в справочнике
  function GetBkCode(spr_name, code:string):boolean;
  var
    XML:Variant;
    sql:String;
  begin
    if ((spr_name<>'') and (code<>'')) then
    begin
      XML:=CreateOleObject('Microsoft.XmlDom');
      try
        try
          sql :=' select code as code ' +
            ' from ' + spr_name + ' spr ' +
            ' where spr.code = ' + code;
          XML.LoadXML(RunQuery(sql));
          Result := XML.GetElementsByTagName('ROW').Length <> 0;
        except
          Raise('Поиск КБК: ' + LastException.Message);
        end;
      finally
        XML := nil;
      end;
    end
    else
      Result := false;
  end;

function Create_Report(
  XMLDoc : Variant;
  FileName: String;
  TemplateFileName: String;
  DbfFileName: String
) : boolean;

  var
    ExcelTemp, Excel, Book, BookTemp, Sheet, SheetTemp: Variant;
    XMLElem, XMLRepQuery, XMLRepElem: Variant;
    saveDialog : TSaveDialog;
    reportName, payorderIds, currentPayAccount, raspDate : String;
    currentRow, i : Integer;
    raspCount, payCount, kassCount : Integer;
    raspTotalAmount, payTotalAmount, kassTotalAmount : Currency;
  begin
    //StatusBar.SimpleText:= 'Выполняется построение отчета ... ';
    Result := false;
    payorderIds := '';

    //------ Выцепляем айдишники платежек в таблице DOCUMENT
    for i := 0 to  XMLDoc.DocumentElement.ChildNodes.Length - 1 do
    begin
      if payorderIds='' then
        payorderIds :=
          XMLDoc.DocumentElement.ChildNodes.Item[i].getAttribute('DOCUMENT_ID')
      else
        payorderIds := payorderIds + ',' +
          XMLDoc.DocumentElement.ChildNodes.Item[i].getAttribute('DOCUMENT_ID');
    end;

    XMLRepQuery := GetReportData(payorderIds);
    if XMLRepQuery.getElementsByTagName('ROW').Length = 0 then exit;

    currentRow := 1;
    currentPayAccount := '';
    XMLElem := XMLDoc.documentElement;
    saveDialog := TSaveDialog.Create(nil);
    saveDialog.InitialDir := ExtractFileDir(FileName);
    saveDialog.Title := 'Сохранение отчета по выгруженным платежкам';
    saveDialog.Filter := 'Файлы вормата Excel (*.xls)|*.xls';
    saveDialog.DefaultExt := 'xls';
    reportName := FileName;
    try
      if not FileExists(reportName) then
      begin
        CopyFile(TemplateFileName, FileName,false); // копируем исходный файл (заготовку) в реальный файл, где будем строить отчет;
      end
      else
      begin
        showMessage('Внимание! Имя файла отчета уже встречается в папке отчетов.' + #13 + #10 +
                                'Пожалуйста выберите дургое имя или файл для замены новым файлом.');
        saveDialog.FileName := ExtractFileName(reportName);
        if saveDialog.Execute then
        begin
          CopyFile(TemplateFileName, saveDialog.FileName, false); // перезатираем, если файл уже существует
          reportName := saveDialog.FileName;
          if not FileExists(reportName) then
            Raise('Ошибка копирования в каталог отчетов');
        end
        else
          showMessage('Отчет не сохранен!');
      end;
    except
      Raise('Копирование шаблона отчета в каталог отчетов: ' + LastException.Message);
    end;
    if not FileExists(reportName) then
      Raise('Ошибка копирования в каталог отчетов');
    try
        Excel := CreateOLEObject('Excel.Application');
        ExcelTemp := CreateOLEObject('Excel.Application');
        Book := Excel.WorkBooks.Open(reportName);
        BookTemp := ExcelTemp.WorkBooks.Open(TemplateFileName);
        Sheet := Book.ActiveSheet;
        Excel.DisplayAlerts := false;
        SheetTemp := BookTemp.ActiveSheet;
        Excel.Cells.Select;
        Excel.Selection.Clear;

        Excel.visible := true; // чтобы вывод можно было визуально ощутить. Если надо только готовый вариант увидеть - поместить эту строку в конец.

 ///////////// непосредственно вывод /////////////////////
        SheetTemp.Range(SheetTemp.Cells(1,1),SheetTemp.Cells(3,8)).Copy;
        Sheet.Paste;
        Sheet.Cells((currentRow + 1),1).Value := SheetTemp.Cells(2,1).Value +
          ' ' + ExtractFileName(DbfFileName);
        currentRow := currentRow + (3 - 1) + 1;
        Sheet.Cells(currentRow,1).Select;
        for i := 0 to  XMLRepQuery.getElementsByTagName('ROW').Length - 1 do
        begin
          XMLRepElem := XMLRepQuery.getElementsByTagName('ROW').Item[i];
          if currentPayAccount<>XMLRepElem.getAttribute('PAY_ACCOUNT') then
          begin
            raspCount := 0;
            payCount := 0;
            kassCount := 0;
            raspTotalAmount := 0;
            payTotalAmount := 0;
            kassTotalAmount := 0;
            if (currentPayAccount<>'') then
            begin
              SheetTemp.Range(SheetTemp.Cells(11,1),SheetTemp.Cells(11,8)).Copy;
              Sheet.Paste;
              currentRow := currentRow + (1 - 1) + 1;
              Sheet.Cells(currentRow,1).Select;
            end;
            currentPayAccount := XMLRepElem.getAttribute('PAY_ACCOUNT');
            SheetTemp.Range(SheetTemp.Cells(4,1),SheetTemp.Cells(7,8)).Copy;
            Sheet.Paste;
            Sheet.Cells((currentRow + 1),1).Value := SheetTemp.Cells(5,1).Value + ' ' + currentPayAccount;
            currentRow := currentRow + (7 - 4) + 1;
            Sheet.Cells(currentRow,1).Select;
          end;
          SheetTemp.Range(SheetTemp.Cells(9,1),SheetTemp.Cells(9,8)).Copy;
          Sheet.Paste;

          if XMLRepElem.getAttribute('RASP_DATE')<>'' then
          begin
              raspDate := '';
              raspDate := raspDate + Copy(XMLRepElem.getAttribute('RASP_DATE'),9,2);
              raspDate := raspDate + '.' + Copy(XMLRepElem.getAttribute('RASP_DATE'),6,2);
              raspDate := raspDate + '.' + Copy(XMLRepElem.getAttribute('RASP_DATE'),1,4);
              Sheet.Cells(currentRow,1).Value := raspDate;
          end;

          Sheet.Cells(currentRow,2).Value := Trim(XMLRepElem.getAttribute('RASP_NUMBER'));

          if XMLRepElem.getAttribute('RASP_COUNT')<>'0' then
          begin
            Sheet.Cells(currentRow,3).Value := XMLRepElem.getAttribute('RASP_COUNT');
            Sheet.Cells(currentRow,4).Value := XMLRepElem.getAttribute('RASP_AMOUNT');
            raspCount := raspCount + XMLRepElem.getAttribute('RASP_COUNT');
            raspTotalAmount := raspTotalAmount + XMLRepElem.getAttribute('RASP_AMOUNT');
          end;

          Sheet.Cells(currentRow,5).Value := XMLRepElem.getAttribute('PP_COUNT');
          payCount := payCount + XMLRepElem.getAttribute('PP_COUNT');

          if (XMLRepElem.getAttribute('PP_AMOUNT')='') then
            Sheet.Cells(currentRow,6).Value := '0'
          else
          begin
            Sheet.Cells(currentRow,6).Value := XMLRepElem.getAttribute('PP_AMOUNT');
            payTotalAmount := payTotalAmount + XMLRepElem.getAttribute('PP_AMOUNT');
          end;

          Sheet.Cells(currentRow,7).Value := XMLRepElem.getAttribute('KASS_COUNT');
          kassCount := kassCount + XMLRepElem.getAttribute('KASS_COUNT');

          if (XMLRepElem.getAttribute('KASS_AMOUNT')='') then
            Sheet.Cells(currentRow,8).Value := '0'
          else
          begin
            Sheet.Cells(currentRow,8).Value := XMLRepElem.getAttribute('KASS_AMOUNT');
            kassTotalAmount := kassTotalAmount + XMLRepElem.getAttribute('KASS_AMOUNT');
          end;

          currentRow := currentRow + 1;
          Sheet.Cells(currentRow,1).Select;
        end;
        SheetTemp.Range(SheetTemp.Cells(11,1),SheetTemp.Cells(11,8)).Copy;
        Sheet.Paste;
        Sheet.Cells(currentRow,3).Value := raspCount;
        Sheet.Cells(currentRow,4).Value := raspTotalAmount;
        Sheet.Cells(currentRow,5).Value := payCount;
        Sheet.Cells(currentRow,6).Value := payTotalAmount;
        Sheet.Cells(currentRow,7).Value := kassCount;
        Sheet.Cells(currentRow,8).Value := kassTotalAmount;
////////////// Конец вывода.

        ExcelTemp.CutCopyMode := False;
        Excel.Cells(1,1).Select;
        Book.Save;
        Result := true;
        //StatusBar.SimpleText:= 'Построение отчета завершено';
    finally
      Sheet := nil;
      SheetTemp := nil;
//      Book.Close;  // чтобы книга оставалась открытой после составления отчета
      BookTemp.close;
      Book := nil;
      BookTemp := nil;
//      Excel.Quit;  // чтобы книга оставалась открытой после составления отчета
      ExcelTemp.Quit;
      Excel := nil;
      ExcelTemp := nil;
      saveDialog.free;
      if not Result then DeleteFile(reportName);
    end;
  end;

{procedure SetProgressBarPos(ProgressBar: TProgressBar; Pos: Integer);
begin
  try
    ProgressBar.Position := Pos;
  except

  end;
end;}

//-------Функция возвращает объект TTable оптимирзированный для обработки выписки (проставляются дебет и кредит в одинаковых записях)
      // Первый параметр - исходная таблица, полученная из банка
  function prepareAbbTable(table: TTable):TTable;
  var
  i,j: Integer;
  str : String;
  begin
    table.first;
    //ProgressBar.Min := 0;
    //ProgressBar.Max:= Table.RecordCount;
    while not table.EOF do
    begin
      table.edit;
      //SetProgressBarPos(ProgressBar, table.RecNo);
      table.fieldByName('MFO_PAYER').AsString :=  ReplaceCharAtString(table.fieldByName('MFO_PAYER').AsString,'''','"');
      table.fieldByName('BANK_PAY').AsString := ReplaceCharAtString(table.fieldByName('BANK_PAY').AsString,'''','"');
      table.fieldByName('SITY_PAY').AsString := ReplaceCharAtString(table.fieldByName('SITY_PAY').AsString,'''','"');
      table.fieldByName('ACC_PAYER').AsString := ReplaceCharAtString(table.fieldByName('ACC_PAYER').AsString,'''','"');
      table.fieldByName('REAL_DEBET').AsString := ReplaceCharAtString(table.fieldByName('REAL_DEBET').AsString,'''','"');
      table.fieldByName('CORACC_PAC').AsString := ReplaceCharAtString(table.fieldByName('CORACC_PAC').AsString,'''','"');
      table.fieldByName('MFO_REC').AsString := ReplaceCharAtString(table.fieldByName('MFO_REC').AsString,'''','"');
      table.fieldByName('BANK_REC').AsString := ReplaceCharAtString(table.fieldByName('BANK_REC').AsString,'''','"');
      table.fieldByName('SITY_REC').AsString := ReplaceCharAtString(table.fieldByName('SITY_REC').AsString,'''','"');
      table.fieldByName('ACC_REC').AsString := ReplaceCharAtString(table.fieldByName('ACC_REC').AsString,'''','"');
      table.fieldByName('REAL_KRED').AsString := ReplaceCharAtString(table.fieldByName('REAL_KRED').AsString,'''','"');
      table.fieldByName('CORACC_REC').AsString := ReplaceCharAtString(table.fieldByName('CORACC_REC').AsString,'''','"');
      table.fieldByName('CURDEB').AsString := ReplaceCharAtString(table.fieldByName('CURDEB').AsString,'''','"');
      table.fieldByName('CURKRED').AsString := ReplaceCharAtString(table.fieldByName('CURKRED').AsString,'''','"');
      table.fieldByName('NUMB_DOCUM').AsString := ReplaceCharAtString(table.fieldByName('NUMB_DOCUM').AsString,'''','"');
      table.fieldByName('KIND_OPER').AsString := ReplaceCharAtString(table.fieldByName('KIND_OPER').AsString,'''','"');
      table.fieldByName('PAYER').AsString := ReplaceCharAtString(table.fieldByName('PAYER').AsString,'''','"');
      table.fieldByName('RECEIVER').AsString := ReplaceCharAtString(table.fieldByName('RECEIVER').AsString,'''','"');
      table.fieldByName('GROUND').AsString := ReplaceCharAtString(table.fieldByName('GROUND').AsString,'''','"');
      table.fieldByName('INN_PAYER').AsString := ReplaceCharAtString(table.fieldByName('INN_PAYER').AsString,'''','"');
      table.fieldByName('INN_REC').AsString := ReplaceCharAtString(table.fieldByName('INN_REC').AsString,'''','"');
      table.fieldByName('SYMBOL_CAC').AsString := ReplaceCharAtString(table.fieldByName('SYMBOL_CAC').AsString,'''','"');
      table.fieldByName('VID').AsString := ReplaceCharAtString(table.fieldByName('VID').AsString,'''','"');
      table.fieldByName('UNIQ_NUM').AsString := ReplaceCharAtString(table.fieldByName('UNIQ_NUM').AsString,'''','"');
      table.fieldByName('NUM_PACK').AsString := ReplaceCharAtString(table.fieldByName('NUM_PACK').AsString,'''','"');
      table.fieldByName('TPRINT').AsString := ReplaceCharAtString(table.fieldByName('TPRINT').AsString,'''','"');
      table.fieldByName('MAIL').AsString := ReplaceCharAtString(table.fieldByName('MAIL').AsString,'''','"');
      table.fieldByName('CONPAY').AsString := ReplaceCharAtString(table.fieldByName('CONPAY').AsString,'''','"');
      table.fieldByName('ACTION').AsString := ReplaceCharAtString(table.fieldByName('ACTION').AsString,'''','"');
      table.fieldByName('STAT_NAL').AsString := ReplaceCharAtString(table.fieldByName('STAT_NAL').AsString,'''','"');
      table.fieldByName('KPP_PAY').AsString := ReplaceCharAtString(table.fieldByName('KPP_PAY').AsString,'''','"');
      table.fieldByName('KPP_REC').AsString := ReplaceCharAtString(table.fieldByName('KPP_REC').AsString,'''','"');
      table.fieldByName('CLASS_NAL').AsString := ReplaceCharAtString(table.fieldByName('CLASS_NAL').AsString,'''','"');
      table.fieldByName('OKATO_NAL').AsString := ReplaceCharAtString(table.fieldByName('OKATO_NAL').AsString,'''','"');
      table.fieldByName('OSN_NAL').AsString := ReplaceCharAtString(table.fieldByName('OSN_NAL').AsString,'''','"');
      table.fieldByName('PER_NAL').AsString := ReplaceCharAtString(table.fieldByName('PER_NAL').AsString,'''','"');
      table.fieldByName('NUM_NAL').AsString := ReplaceCharAtString(table.fieldByName('NUM_NAL').AsString,'''','"');
      table.fieldByName('DAT_NAL').AsString := ReplaceCharAtString(table.fieldByName('DAT_NAL').AsString,'''','"');
      table.fieldByName('TYPE_NAL').AsString := ReplaceCharAtString(table.fieldByName('TYPE_NAL').AsString,'''','"');
      table.fieldByName('NUM_CHAST').AsString := ReplaceCharAtString(table.fieldByName('NUM_CHAST').AsString,'''','"');
      table.fieldByName('SHIFR_PL').AsString := ReplaceCharAtString(table.fieldByName('SHIFR_PL').AsString,'''','"');
      table.fieldByName('NUM_PL').AsString := ReplaceCharAtString(table.fieldByName('NUM_PL').AsString,'''','"');
      table.fieldByName('DAT_PL').AsString := ReplaceCharAtString(table.fieldByName('DAT_PL').AsString,'''','"');
      table.fieldByName('NUM_POR').AsString := ReplaceCharAtString(table.fieldByName('NUM_POR').AsString,'''','"');
      table.next;
    end;

    //StatusBar.SimpleText:= 'Подготовка файла к обработке. Этап 2. (определение: дебет/кредит)';
    table.first;
    //SetProgressBarPos(ProgressBar, 0);
    //if Table.RecordCount > 0 then ProgressBar.Max:= Table.RecordCount - 1
    //else ProgressBar.Max := 0;
    for i := 1 to (table.RecordCount - 1) do
    begin
      //SetProgressBarPos(ProgressBar, i + 1);
      str := '';
      table.edit;
      table.FieldByName('SUM_OST').AsInteger := i;
      if ((Trim(table.FieldByName('NUM_CHAST').AsString) = 'd') or (Trim(table.FieldByName('NUM_CHAST').AsString) = 'k')) then
      begin
        table.next;
        continue;
      end;
      if (table.fieldByName('MFO_PAYER').AsString<>'') then
        str := (str + 'MFO_PAYER=' + '''' + table.fieldByName('MFO_PAYER').AsString + '''' + ' and ');
      if (table.fieldByName('BANK_PAY').AsString<>'') then
        str := (str + 'BANK_PAY=' + '''' + table.fieldByName('BANK_PAY').AsString + '''' + ' and ');
      if (table.fieldByName('SITY_PAY').AsString<>'') then
        str := (str + 'SITY_PAY=' + '''' + table.fieldByName('SITY_PAY').AsString + '''' + ' and ');
      if (table.fieldByName('ACC_PAYER').AsString<>'') then
        str := (str + 'ACC_PAYER=' + '''' + table.fieldByName('ACC_PAYER').AsString + '''' + ' and ');
      if (table.fieldByName('REAL_DEBET').AsString<>'') then
        str := (str + 'REAL_DEBET=' + '''' + table.fieldByName('REAL_DEBET').AsString + '''' + ' and ');
      if (table.fieldByName('CORACC_PAC').AsString<>'') then
        str := (str + 'CORACC_PAC=' + '''' + table.fieldByName('CORACC_PAC').AsString + '''' + ' and ');
      if (table.fieldByName('MFO_REC').AsString<>'') then
        str := (str + 'MFO_REC=' + '''' + table.fieldByName('MFO_REC').AsString + '''' + ' and ');
      if (table.fieldByName('BANK_REC').AsString<>'') then
        str := (str + 'BANK_REC=' + '''' + table.fieldByName('BANK_REC').AsString + '''' + ' and ');
      if (table.fieldByName('SITY_REC').AsString<>'') then
        str := (str + 'SITY_REC=' + '''' + table.fieldByName('SITY_REC').AsString + '''' + ' and ');
      if (table.fieldByName('ACC_REC').AsString<>'') then
        str := (str + 'ACC_REC=' + '''' + table.fieldByName('ACC_REC').AsString + '''' + ' and ');
      if (table.fieldByName('REAL_KRED').AsString<>'') then
        str := (str + 'REAL_KRED=' + '''' + table.fieldByName('REAL_KRED').AsString + '''' + ' and ');
      if (table.fieldByName('CORACC_REC').AsString<>'') then
        str := (str + 'CORACC_REC=' + '''' + table.fieldByName('CORACC_REC').AsString + '''' + ' and ');
      if (table.fieldByName('SUM').AsString<>'') then
        str := (str + 'SUM=' + table.fieldByName('SUM').AsString + ' and ');
      if (table.fieldByName('SROK').AsString<>'') then
        str := (str + 'SROK=' + '''' + table.fieldByName('SROK').AsString + '''' + ' and ');
      if (table.fieldByName('DATE_CARRY').AsString<>'') then
        str := (str + 'DATE_CARRY=' + '''' + table.fieldByName('DATE_CARRY').AsString + '''' + ' and ');
      if (table.fieldByName('NUMB_DOCUM').AsString<>'') then
        str := (str + 'NUMB_DOCUM=' + '''' + table.fieldByName('NUMB_DOCUM').AsString + '''' + ' and ');
      if (table.fieldByName('DATE_DOCUM').AsString<>'') then
        str := (str + 'DATE_DOCUM=' + '''' + table.fieldByName('DATE_DOCUM').AsString + '''' + ' and ');
      if (table.fieldByName('KIND_OPER').AsString<>'') then
        str := (str + 'KIND_OPER=' + '''' + table.fieldByName('KIND_OPER').AsString + '''' + ' and ');
      if (table.fieldByName('PAYER').AsString<>'') then
        str := (str + 'PAYER=' + '''' + table.fieldByName('PAYER').AsString + '''' + ' and ');
      if (table.fieldByName('RECEIVER').AsString<>'') then
        str := (str + 'RECEIVER=' + '''' + table.fieldByName('RECEIVER').AsString + '''' + ' and ');
      if (table.fieldByName('GROUND').AsString<>'') then
        str := (str + 'GROUND=' + '''' + table.fieldByName('GROUND').AsString + '''' + ' and ');
      if (table.fieldByName('INN_PAYER').AsString<>'') then
        str := (str + 'INN_PAYER=' + '''' + table.fieldByName('INN_PAYER').AsString + '''' + ' and ');
      if (table.fieldByName('INN_REC').AsString<>'') then
        str := (str + 'INN_REC=' + '''' + table.fieldByName('INN_REC').AsString + '''' + ' and ');
      if (table.fieldByName('SYMBOL_CAC').AsString<>'') then
        str := (str + 'SYMBOL_CAC=' + '''' + table.fieldByName('SYMBOL_CAC').AsString + '''' + ' and ');
      if (table.fieldByName('VID').AsString<>'') then
        str := (str + 'VID=' + '''' + table.fieldByName('VID').AsString + '''' + ' and ');
      if (table.fieldByName('PAYMENT').AsString<>'') then
        str := (str + 'PAYMENT=' + table.fieldByName('PAYMENT').AsString + ' and ');
      if (table.fieldByName('CONPAY').AsString<>'') then
        str := (str + 'CONPAY=' + '''' + table.fieldByName('CONPAY').AsString + '''' + ' and ');
      if (table.fieldByName('STAT_NAL').AsString<>'') then
        str := (str + 'STAT_NAL=' + '''' + table.fieldByName('STAT_NAL').AsString + '''' + ' and ');
      if (table.fieldByName('KPP_PAY').AsString<>'') then
        str := (str + 'KPP_PAY=' + '''' + table.fieldByName('KPP_PAY').AsString + '''' + ' and ');
      if (table.fieldByName('KPP_REC').AsString<>'') then
        str := (str + 'KPP_REC=' + '''' + table.fieldByName('KPP_REC').AsString + '''' + ' and ');
      if (table.fieldByName('CLASS_NAL').AsString<>'') then
        str := (str + 'CLASS_NAL=' + '''' + table.fieldByName('CLASS_NAL').AsString + '''' + ' and ');
      if (table.fieldByName('OKATO_NAL').AsString<>'') then
        str := (str + 'OKATO_NAL=' + '''' + table.fieldByName('OKATO_NAL').AsString + '''' + ' and ');
      if (table.fieldByName('OSN_NAL').AsString<>'') then
        str := (str + 'OSN_NAL=' + '''' + table.fieldByName('OSN_NAL').AsString + '''' + ' and ');
      if (table.fieldByName('PER_NAL').AsString<>'') then
        str := (str + 'PER_NAL=' + '''' + table.fieldByName('PER_NAL').AsString + '''' + ' and ');
      if (table.fieldByName('NUM_NAL').AsString<>'') then
        str := (str + 'NUM_NAL=' + '''' + table.fieldByName('NUM_NAL').AsString + '''' + ' and ');
      if (table.fieldByName('DAT_NAL').AsString<>'') then
        str := (str + 'DAT_NAL=' + '''' + table.fieldByName('DAT_NAL').AsString + '''' + ' and ');
      if (table.fieldByName('TYPE_NAL').AsString<>'') then
        str := (str + 'TYPE_NAL=' + '''' + table.fieldByName('TYPE_NAL').AsString + '''' + ' and ');
      if (table.fieldByName('NUM_CHAST').AsString<>'') then
        str := (str + 'NUM_CHAST=' + '''' + table.fieldByName('NUM_CHAST').AsString + '''' + ' and ');

      if (str<>'') then
        str := copy(str,1,(Length(str)-5));
      table.filter := str;
      table.filtered := true;
      if ((table.recordCount mod 2) = 0) then
      begin
        for j := 1 to table.recordCount do
        begin
          table.edit;
          if ((j mod 2) = 0) then
            table.FieldByName('NUM_CHAST').AsString := 'd'
          else
            table.FieldByName('NUM_CHAST').AsString := 'k';
          table.next;
        end;
      end;
      table.filtered := false;
      table.locate('SUM_OST',i,loPartialKey);
      table.next;
    end;

   table.first;
  result := table;
  end;
procedure Tat_FK_PF_Main(Xml: Variant; OutFileName: String);
var
  i, n: Integer;
  IsNal: Boolean;
  XmlElem: Variant;
  xFileSource, xFileDestin: string;
begin
  LstErrors := TStringList.Create;
  SetParams(Xml, OutFileName);
  try
    n := Xml.DocumentElement.ChildNodes.Length - 1;
    for i := 0 to n do
    begin
      XmlElem := Xml.DocumentElement.ChildNodes.Item[i];
      IsNal := False;
      if Tat_FK_PF_IsNal(XmlElem.GetAttribute('REC_ACC_ID')) then
      begin
        try
          IsNal := True;
          InsertIntoDbfFile(XML, XmlElem);
        except
          LstErrors.Add('Ошибка при выгрузке платёжного поручения в DBF: ' +
            Tat_FK_PF_GetElementDescription(XmlElem) + ': ' +
            LastException.Message);
          Raise(LastException.Message);
        end;
       end;
       try
         AddPayrollToReport(XmlElem, IsNal);
       except
         LstErrors.Add('Ошибка выгрузки в MS Excel: ' +
           Tat_FK_PF_GetElementDescription(XmlElem) + ': ' +
           LastException.Message);
      end;
    end;

    if Tat_FK_PF_Tbl <> nil then
    begin
      
      Tat_FK_PF_Tbl.Close;
    end;

    ForceDirectories(RemoveTerminalSlash(
      CheckPathOut( XML.DocumentElement.GetAttribute('PATH_OUTPUT'))) + '\ARC\XLS');
    ForceDirectories(RemoveTerminalSlash(
      CheckPathOut( XML.DocumentElement.GetAttribute('PATH_OUTPUT'))) + '\ARC\DBF');
    // Вывод отчёта в Excel.
    ShowPayrollReport(RemoveTerminalSlash(
      CheckPathOut( XML.DocumentElement.GetAttribute('PATH_OUTPUT'))) + '\ARC\XLS\' +
         ChangeFileExt(Tat_FK_PF_OutFileName, 'xls'));
         
    xFileSource := RemoveTerminalSlash( CheckPathTmp( XML.DocumentElement.GetAttribute('PATH_TEMP'))) +
       '\a' + Copy(ChangeFileExt(Tat_FK_PF_OutFileName, 'dbf'), 2, 100);
    xFileDestin := RemoveTerminalSlash( CheckPathOut( XML.DocumentElement.GetAttribute('PATH_OUTPUT'))) +
       '\a' + Copy(ChangeFileExt(Tat_FK_PF_OutFileName, 'dbf'), 2, 100);
    
    CopyFile(xFileSource, xFileDestin);
    
    xFileDestin := RemoveTerminalSlash(CheckPathOut( XML.DocumentElement.GetAttribute('PATH_OUTPUT'))) +
       '\ARC\DBF\a' + Copy(ChangeFileExt(Tat_FK_PF_OutFileName, 'dbf'), 2, 100);
    
    CopyFile(xFileSource, xFileDestin);

    
    DeleteFile(xFileSource);
    
    if LstErrors.Count > 0 then
    begin
      LstErrors.SaveToFile(RemoveTerminalSlash(
        CheckPathTmp( XML.DocumentElement.GetAttribute('PATH_TEMP'))) +
        '\errors.txt');
      ShellExecute('notepad.exe',
        RemoveTerminalSlash(CheckPathTmp( XML.DocumentElement.GetAttribute('PATH_TEMP'))) +
        '\errors.txt', False);
    end;

  finally
    FreeTat_FK_PF;
    LstErrors.Free;
  end;
end;

procedure SetParams(Xml: Variant; OutFileName: String);
var
  param_CFG, param_Date, param_Packet: String;
  param_OutFileNameMask: String;
  OrgPay_Code: String;
  cfgList: TStringList;
  Xmle: Variant;
begin
  param_CFG := Xml.DocumentElement.GetAttribute('CONFIG');
  param_Date:= Xml.DocumentElement.GetAttribute('DATE'); //dd.mm.yyyy
  param_Packet:= Xml.DocumentElement.GetAttribute('EXCH_PACKET'); //Номер порции
  cfgList := TStringList.Create;
  try
    cfgList.CommaText := param_CFG;
    param_OutFileNameMask:= cfgList.Values('Маска формирования имени файла');
    Tat_FK_PF_param_DirOut:= cfgList.Values('Исходящий каталог');
  finally
    cfgList.Free;
  end;
  Xmle := Xml.DocumentElement.ChildNodes.Item[0];
  if Xmle.GetAttribute('DOCUMENTCLASS_ID') = '13' then
    OrgPay_Code:= Xmle.GetAttribute('PAY_CODE')
  else if Xmle.GetAttribute('DOCUMENTCLASS_ID') = '48' then
    OrgPay_Code:= Xmle.getAttribute('ORG_CODE');

  // путь по умолчанию
  Tat_FK_PF_DirDefaultOut :=
    ExtractFileDir( XML.DocumentElement.GetAttribute('INI_FILE')) + '\Out';
  Tat_FK_PF_DirDefaultTmp :=
    ExtractFileDir( XML.DocumentElement.GetAttribute('INI_FILE')) + '\Temp';

  InitTat_FK_PF(ExtractFileDir(XML.DocumentElement.GetAttribute('INI_FILE')) +
      '\akbar_out.dbf',
  RemoveTerminalSlash(CheckPathTmp( XML.DocumentElement.GetAttribute('PATH_TEMP')))
     + '\a' + Copy(ChangeFileExt(OutFileName, 'dbf'), 2, 100));
  Tat_FK_PF_OutFileName := OutFileName;
end;

procedure InitTat_FK_PF(TblTemplateFileName, TblFileName: String);
begin
  //ShowMessage(TblTemplateFileName + '    ;    ' + TblFileName);
  Tat_FK_PF_TblTemplateFileName := TblTemplateFileName;
  Tat_FK_PF_TblFileName := TblFileName;
  Tat_FK_PF_Tbl := nil;
  Tat_FK_PF_LstPayroll := TStringList.Create;
  
end;

procedure FreeTat_FK_PF;
begin
  Tat_FK_PF_FreeLstPayroll;
  Tat_FK_PF_LstPayroll.Free;
  if Tat_FK_PF_Tbl <> nil then Tat_FK_PF_Tbl.Free;
end;

procedure Tat_FK_PF_FreeLstPayroll;
var
  i, n: Integer;
begin
  n := Tat_FK_PF_LstPayroll.Count - 1;
  for i := 0 to n do
  begin
    Tat_FK_PF_LstPayroll.Objects[i].Free;
  end;
end;

// Заполняем таблицу DBF.
procedure InsertIntoDbfFile(XmlDoc, XmlElem: Variant);
var
  str: String;
  XmlDocTemp: Variant;
  Tbl: TTable;
begin
  Tbl := Tat_FK_PF_Tbl;
  if Tbl = nil then
  begin
    Tat_FK_PF_CreateDbfFile;
    Tbl := Tat_FK_PF_Tbl;
  end;
  Tbl.Insert;
  try
    Tbl.FieldByName('MFO_PAYER').AsString :=
      XMLElem.getAttribute('REC_BIC');
    Tbl.FieldByName('BANK_PAY').AsString :=
      AnsiToOem(XMLElem.getAttribute('REC_B_NAME'));
    Tbl.FieldByName('SITY_PAY').AsString :=
      AnsiToOem(XMLElem.getAttribute('REC_B_CITY'));
    Tbl.FieldByName('ACC_PAYER').AsString :=
      XMLElem.getAttribute('REC_ACCOUNT');
    Tbl.FieldByName('REAL_DEBET').AsString :=
      XMLElem.getAttribute('REC_ACCOUNT');
    Tbl.FieldByName('CORACC_PAC').AsString :=
      XMLElem.getAttribute('REC_CORACCOUNT');

    if Trim(XmlElem.getAttribute('REC_UFK_ACCOUNT')) <> '' then
    begin
      //Tbl.FieldByName('ACC_REC').AsString :=
      //  XMLElem.getAttribute('REC_UFK_ACCOUNT');
      Tbl.FieldByName('PAYER').AsString :=
        AnsiToOem( Format('%-160s',[XMLElem.getAttribute('REC_UFK_NAME') +
        ' (' + XMLElem.getAttribute('REC_NAME') + ', ' +
        'ЛС ' + XMLElem.getAttribute('REC_ACCOUNT') + ')']));
    end
    else
    begin
      //Tbl.FieldByName('ACC_REC').AsString := XMLElem.getAttribute('REC_ACCOUNT');
      Tbl.FieldByName('PAYER').AsString :=
        AnsiToOem(XMLElem.getAttribute('REC_NAME'));
    end;

    if XmlElem.getAttribute('REC_FILIAL_NAME') <> '' then
      Tbl.FieldByName('PAYER').AsString :=
        Format('%-160s',[Tbl.FieldByName('PAYER').AsString + ' ' +
        AnsiToOem(XMLElem.getAttribute('REC_FILIAL_NAME'))]);

    Tbl.FieldByName('ACC_REC').AsString := '20202810500550000001';
    Tbl.FieldByName('REAL_KRED').AsString :=
      Tbl.FieldByName('ACC_REC').AsString;
    Tbl.FieldByName('MFO_REC').AsString := '049205805';
    Tbl.FieldByName('BANK_REC').AsString := AnsiToOem('ОАО "АК БАРС" БАНК');
    Tbl.FieldByName('SITY_REC').AsString := AnsiToOem('КAЗАНЬ');
    Tbl.FieldByName('RECEIVER').AsString :=
      AnsiToOem('Министерство финансов Республики Татарстан');
    Tbl.FieldByName('CORACC_REC').AsString := '30101810000000000805';
    Tbl.FieldByName('INN_REC').AsString := '1654019570';
    Tbl.FieldByName('KPP_REC').AsString := '165501001';

    XmlDocTemp := GetFinOrgan(XMLElem.getAttribute('REC_TAXCODE'),
      XMLElem.getAttribute('REC_KPP'),XMLElem.getAttribute('REC_ACCOUNT'));

    if (XMLDocTemp.getElementsByTagName('ROW').Length <> 0) then
    begin
      if (XMLDocTemp.getElementsByTagName('ROW').Length = 1) then
      begin
        Tbl.FieldByName('PAYER').AsString :=
        Format('%-160s',
        [AnsiToOem(
        XMLDocTemp.getElementsByTagName('ROW').Item[0].getAttribute('CAPTION'))
        + ' (' + Tbl.FieldByName('PAYER').AsString + ')']);
      end
      else
      begin
        str := ChooseRec(XMLDocTemp, 'ROW', true,
          'Выбор организации-финоргана (счет: '
           + XMLElem.getAttribute('REC_ACCOUNT') + ', '
           + 'ИНН: ' + XMLElem.getAttribute('REC_TAXCODE') + ', '
           + 'КПП: ' + XMLElem.getAttribute('REC_KPP') + ')');
        if str <> '' then
          Tbl.FieldByName('PAYER').AsString :=
            Format('%-160s',[AnsiToOem(str) + ' (' +
            Tbl.FieldByName('PAYER').AsString + ')'])
        else
          Raise(
   'Отказ от выбора организации-финоргана. Продолжение выгрузки невозможно!');
      end;
    end;

    Tbl.FieldByName('SUM').AsString :=
      XMLElem.getAttribute('AMOUNT');
    //Tatarstan
    Tbl.FieldByName('DATE_CARRY').AsString :='';
    // XMLDoc.documentElement.getAttribute('DATE');
    //!Tatarstan
    Tbl.FieldByName('NUMB_DOCUM').AsString :=
      XMLElem.getAttribute('DOC_NUMBER');

    Tbl.FieldByName('DATE_DOCUM').AsString :=
      convertDateStr(XMLElem.getAttribute('DOC_DATE'));
    //Tatarstan
    //if (length(XMLElem.getAttribute('BANKOPER_ID'))>0) then
        Tbl.FieldByName('KIND_OPER').AsString :='03';
          //addZeroFirst(XMLElem.getAttribute('BANKOPER_ID'),2);
        Tbl.FieldByName('SYMBOL_CAC').AsString :='40';
   //!Tatarstan
    Tbl.FieldByName('GROUND').AsString := Tat_FK_PF_PayerForGround(
      AnsiToOem(XMLElem.getAttribute('PAY_NAME'))) + ' ' +
      AnsiToOem(XMLElem.getAttribute('DESCRIPTION'));
    Tbl.FieldByName('INN_PAYER').AsString :=
      XMLElem.getAttribute('REC_TAXCODE');
    Tbl.FieldByName('VID').AsString := AnsiToOem('электронно');
    Tbl.FieldByName('PAYMENT').AsString :=
      XMLElem.getAttribute('QUEUE_KIND');

    if (Length(XMLElem.getAttribute('AUTHOR_ID')) <> 0) then
      Tbl.FieldByName('STAT_NAL').AsString :=
        addZeroFirst(XMLElem.getAttribute('AUTHOR_ID'),2);

    // если указан хотя бы один код бюджетной классификации
    {if ((length(XMLElem.getAttribute('KADMD_CODE')) <> 0) or  // если указан
     (length(XMLElem.getAttribute('KD_CODE')) <> 0) or     // хотя бы один код
     (length(XMLElem.getAttribute('KESD_CODE')) <> 0)) then // бюджетной классификации}

    // Если указан код бюджетной классфикации
    if length(XMLElem.getAttribute('PI_BUDGET_CODE')) <> 0 then
    begin
      // Код бюджетной  (Администратор поступлений и выбытий (3))
      {str := Format('%-3s',
        [addZeroFirst(XMLElem.getAttribute('KADMD_CODE'),3)]);
      // классификации   (Классификатор видов доходов (14))
      str := str + Format('%-14s',
        [addZeroFirst(XMLElem.getAttribute('KD_CODE'),14)]);
      // три атрибута составляют один КБК длиной в 20 симв
      // (Экономический классификатор (3))
      str := str + Format('%-3s',
        [addZeroFirst(XMLElem.getAttribute('KESD_CODE'),3)]);
      Tbl.FieldByName('CLASS_NAL').AsString := str;}
      Tbl.FieldByName('CLASS_NAL').AsString :=
         XMLElem.getAttribute('PI_BUDGET_CODE');
    end;

    Tbl.FieldByName('OKATO_NAL').AsString := XMLElem.getAttribute('PAY_OKATO');
    Tbl.FieldByName('OSN_NAL').AsString :=
      AnsiToOem(XMLElem.getAttribute('GROUND_ID'));
    Tbl.FieldByName('PER_NAL').AsString :=
      AnsiToOem(XMLElem.getAttribute('TAXPERIOD'));
    Tbl.FieldByName('NUM_NAL').AsString :=
      AnsiToOem(XMLElem.getAttribute('GRND_DOC_NUMBER'));
    Tbl.FieldByName('DAT_NAL').AsString :=
      XMLElem.getAttribute('GRND_DOC_DATE');
    Tbl.FieldByName('TYPE_NAL').AsString :=
      AnsiToOem(XMLElem.getAttribute('PAYTYPE_ID'));
    Tbl.FieldByName('KPP_PAY').AsString := XMLElem.getAttribute('REC_KPP');

    // ищем серию и номер чека из заявки на оплату расходов и вставляем в формируемый файл
    XMLDocTemp := GetZajavkaData(XMLElem.getAttribute('ID'));
    if (XMLDocTemp.getElementsByTagName('ROW').Length <> 0) then
    begin
      str := '';
      if XMLDocTemp.getElementsByTagName('ROW').Item[0]
        .getAttribute('CHEQUE_SER')<>'' then
        str := str + AnsiToOem(XMLDocTemp.getElementsByTagName('ROW').
          Item[0].getAttribute('CHEQUE_SER')) + ' ';
      if XMLDocTemp.getElementsByTagName('ROW').Item[0]
        .getAttribute('CHEQUE_NUM')<>'' then
        str := str + AnsiToOem(XMLDocTemp.getElementsByTagName('ROW').Item[0]
        .getAttribute('CHEQUE_NUM'));
      Tbl.FieldByName('CONPAY').AsString := str;
      str :=
        AnsiToOem(XMLDocTemp.getElementsByTagName('ROW').Item[0]
          .getAttribute('CHEQUE_NUM'));
      Tbl.FieldByName('NUMB_DOCUM').AsString := Copy(str, Length(str) - 2, 3);
    end;

    Tbl.FieldByName('CONPAY').AsString := Tbl.FieldByName('CONPAY').AsString +
      ' <' + XMLElem.GetAttribute('BUDGET_ID') + '>';

  except
    Tbl.Cancel;
    Raise(LastException.Message);
  end;
  Tbl.Post;
end;

procedure AddPayrollToReport(XMLElem: Variant; IsNal: Boolean);
var
  XmlTemp: Variant;
  Item: Variant;
begin
  XmlTemp := Tat_FK_PF_GetPayroll(XmlElem.getAttribute('ID'));
  //XmlTemp.Save('C:\payroll.xml');
  if XmlTemp.documentElement.childNodes.Length = 0 then Exit;
  Item := XmlTemp.documentElement.childNodes.Item[0];
  Tat_FK_PF_AddPayroll(XmlElem.getAttribute('PAY_ACCOUNT'), IsNal,
    Item.getAttribute('PAYROLL_ID'),
    Trim(Item.getAttribute('DOC_NUMBER')),
    StrToFloat(Item.getAttribute('AMOUNT')),
    StrToFloat(XMLElem.getAttribute('AMOUNT')),
    Item.getAttribute('DOC_DATE'),
    Item.getAttribute('CNT'),
    {Item.getAttribute('CHEQUE_CNT'),
    StrToFloat(Item.getAttribute('CHEQUE_AMOUNT'))}
    0, 0.0
    );
end;

function Tat_FK_PF_AddPayrollAccount(Account: String; IsNal: Boolean): TClientDataset;
var
  n : Integer;
  strAccount: String;
begin
  //if (Account[1] = '+') or (Account[1] = '-') then ShowMessage(Account);
  if IsNal then strAccount := '+' + Account else strAccount := '-' + Account;
  n := Tat_FK_PF_LstPayroll.IndexOf(strAccount);
  if n = -1 then
  begin
    n := Tat_FK_PF_LstPayroll.Add(strAccount);
    Result := Tat_FK_PF_CreatePayrollCDS;
    Tat_FK_PF_LstPayroll.Objects[n] := Result;
  end
  else Result := TClientDataSet(Tat_FK_PF_LstPayroll.Objects[n]);
end;

function Tat_FK_PF_CreatePayrollCDS: TClientDataSet;
var
  FieldDefs: TFieldDefs;
begin
  Result := TClientDataSet.Create(nil);
  Result.FilterOptions := foCaseInsensitive;
  FieldDefs := Result.FieldDefs;
  with FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Size := 32;
    Name := 'PAYROLL_ID';
  end;
  with FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Size := 32;
    Name := 'DATE';
  end;
  with FieldDefs.AddFieldDef do
  begin
    DataType := ftString;
    Size := 64;
    Name := 'DOC_NUMBER';
  end;
  with FieldDefs.AddFieldDef do
  begin
    DataType := ftInteger;
    Name := 'PP_COUNT';
  end;
  with FieldDefs.AddFieldDef do
  begin
    DataType := ftInteger;
    Name := 'CNT';
  end;
  with FieldDefs.AddFieldDef do
  begin
    DataType := ftCurrency;
    Name := 'AMOUNT';
  end;
  with FieldDefs.AddFieldDef do
  begin
    DataType := ftCurrency;
    Name := 'PP_AMOUNT';
  end;
  with FieldDefs.AddFieldDef do
  begin
    DataType := ftInteger;
    Name := 'CHEQUE_COUNT';
  end;
  with FieldDefs.AddFieldDef do
  begin
    DataType := ftCurrency;
    Name := 'CHEQUE_AMOUNT';
  end;
  Result.CreateDataSet;
  Result.Open;
end;

procedure ShowPayrollReport(SaveToFileName: String);
var
  i, n, pos, start, j: Integer;
  str, str2: String;
  Excel, Book, Sheet, Range: Variant;
  CDS: TClientDataSet;
  CntFull, PPCountFull, ChequeCountFull: Integer;
  AmountFull, PPAmountFull, ChequeAmountFull: Currency;
  CntAll, PPCountAll, ChequeCountAll: Integer;
  AmountAll, PPAmountAll, ChequeAmountAll: Currency;
  CntNal, PPCountNal, ChequeCountNal: Integer;
  AmountNal, PPAmountNal, ChequeAmountNal: Currency;
  V: Variant;
begin
  Excel := CreateOleObject('Excel.Application');
  try
  Excel.Application.Visible := True;
  Book := Excel.Workbooks.Add;
  Sheet := Book.ActiveSheet;
  CntAll := 0; PPCountAll := 0; ChequeCountAll := 0;
  AmountAll := 0.00; PPAmountAll := 0.00; ChequeAmountAll := 0.00;
  CntNal := 0; PPCountNal := 0; ChequeCountNal := 0;
  AmountNal := 0.00; PPAmountNal := 0.00; ChequeAmountNal := 0.00;
  Sheet.Rows[1].Font.Bold := True;
  Sheet.Cells[1, 3] := 'ОПИСЬ РАСПОРЯЖЕНИЙ НА ПЕРЕЧИСЛЕНИЕ СРЕДСТВ';
  Sheet.Cells[2, 3] := 'включенных в файл выгрузки ' +
    ChangeFileExt(ExtractFileName(SaveToFileName), 'rpc');
  Sheet.Application.ActiveWindow.DisplayGridlines := False;
  Sheet.Range['C1:H1'].MergeCells := True;
  Sheet.Range['C1:H1'].HorizontalAlignment := 3;
  Sheet.Range['C2:H2'].MergeCells := True;
  Sheet.Range['C2:H2'].HorizontalAlignment := 3;
  Sheet.Columns[1].ColumnWidth := 10;
  Sheet.Columns[2].ColumnWidth := 10;
  Sheet.Columns[3].ColumnWidth := 8;
  Sheet.Columns[4].ColumnWidth := 16;
  Sheet.Columns[5].ColumnWidth := 8;
  Sheet.Columns[6].ColumnWidth := 16;
  Sheet.Columns[7].ColumnWidth := 8;
  Sheet.Columns[8].ColumnWidth := 16;
  pos := 5;
  n := Tat_FK_PF_LstPayroll.Count - 1;
  for i := 0 to n do
  begin
    Sheet.Rows[pos].Font.Bold := True;
    str := Tat_FK_PF_LstPayroll[i];
    if Length(str) > 1 then
    begin
      str2 := 'Счет: ' + Copy(str, 2, Length(str) - 1);
      if str[1] = '+' then str2 := str2 + ' (наличка)';
      Sheet.Cells[pos, 1] := str2;
    end;
    pos := pos + 1;
    start := pos;
    Sheet.Rows[pos].Font.Bold := True;
    str := IntToStr(pos);
    Sheet.Range['A' + str + ':D' + str].MergeCells := True;
    Sheet.Range['E' + str + ':F' + str].MergeCells := True;
    Sheet.Range['G' + str + ':H' + str].MergeCells := True;
    Sheet.Range['A' + str + ':H' + str].HorizontalAlignment := 3;
    Sheet.Cells[pos, 1] := 'Распоряжение';
    Sheet.Cells[pos, 5] := 'Платежные поручения';
    Sheet.Cells[pos, 7] := 'Кассовые документы';
    pos := pos + 1;
    str := IntToStr(pos);
    Sheet.Rows[pos].Font.Bold := True;
    Sheet.Range['A' + str + ':H' + str].HorizontalAlignment := 3;
    Sheet.Cells[pos, 1] := 'Дата';
    Sheet.Cells[pos, 2] := 'Номер';
    Sheet.Cells[pos, 3] := 'Кол-во';
    Sheet.Cells[pos, 4] := 'Сумма';
    Sheet.Cells[pos, 5] := 'Кол-во';
    Sheet.Cells[pos, 6] := 'Сумма';
    Sheet.Cells[pos, 7] := 'Кол-во';
    Sheet.Cells[pos, 8] := 'Сумма';
    CDS := TClientDataSet(Tat_FK_PF_LstPayroll.Objects[i]);
    CntFull := 0; PPCountFull := 0; ChequeCountFull := 0;
    AmountFull := 0.00; PPAmountFull := 0.00; ChequeAmountFull := 0.00;
    while not CDS.Eof do
    begin
      pos := pos + 1;
      str := IntToStr(pos);
      Sheet.Range['A' + str + ':C' + str].HorizontalAlignment := 3;
      Sheet.Range['E' + str + ':E' + str].HorizontalAlignment := 3;
      Sheet.Range['G' + str + ':G' + str].HorizontalAlignment := 3;
      Sheet.Cells[pos, 1] := CDS.FieldByName('DATE').AsString;
      Sheet.Cells[pos, 2] := CDS.FieldByName('DOC_NUMBER').AsString;
      Sheet.Cells[pos, 3] := CDS.FieldByName('CNT').AsInteger;
      Sheet.Cells[pos, 4] := CDS.FieldByName('AMOUNT').AsCurrency;
      Sheet.Cells[pos, 5] := CDS.FieldByName('PP_COUNT').AsInteger;
      Sheet.Cells[pos, 6] := CDS.FieldByName('PP_AMOUNT').AsCurrency;
      Sheet.Cells[pos, 7] := CDS.FieldByName('CHEQUE_COUNT').AsInteger;
      Sheet.Cells[pos, 8] := CDS.FieldByName('CHEQUE_AMOUNT').AsCurrency;
      CntFull := CntFull + CDS.FieldByName('CNT').AsInteger;
      PPCountFull := PPCountFull + CDS.FieldByName('PP_COUNT').AsInteger;
      ChequeCountFull := ChequeCountFull +
        CDS.FieldByName('CHEQUE_COUNT').AsInteger;
      AmountFull := AmountFull + CDS.FieldByName('AMOUNT').AsCurrency;
      PPAmountFull := PPAmountFull + CDS.FieldByName('PP_AMOUNT').AsCurrency;
      ChequeAmountFull := ChequeAmountFull +
        CDS.FieldByName('CHEQUE_AMOUNT').AsCurrency;
      CDS.Next;
    end;
    pos := pos + 1;
    str := IntToStr(pos);
    Sheet.Rows[pos].Font.Bold := True;
    Sheet.Range['A' + str + ':B' + str].MergeCells := True;
    Sheet.Range['C' + str + ':C' + str].HorizontalAlignment := 3;
    Sheet.Range['E' + str + ':E' + str].HorizontalAlignment := 3;
    Sheet.Range['G' + str + ':G' + str].HorizontalAlignment := 3;
    Sheet.Cells[pos, 1] := 'Итого:';
    Sheet.Cells[pos, 3] := CntFull;
    Sheet.Cells[pos, 4] := AmountFull;
    Sheet.Cells[pos, 5] := PPCountFull;
    Sheet.Cells[pos, 6] := PPAmountFull;
    Sheet.Cells[pos, 7] := ChequeCountFull;
    Sheet.Cells[pos, 8] := ChequeAmountFull;
    CntAll := CntAll + CntFull;
    AmountAll := AmountAll + AmountFull;
    PPCountAll := PPCountAll + PPCountFull;
    PPAmountAll := PPAmountAll + PPAmountFull;
    ChequeCountAll := ChequeCountAll + ChequeCountFull;
    ChequeAmountAll := ChequeAmountAll + ChequeAmountFull;
    str2 := Tat_FK_PF_LstPayroll[i];
    if (Length(str2) > 0) and (str2[1] = '+') then
    begin
      CntNal := CntNal + CntFull;
      AmountNal := AmountNal + AmountFull;
      PPCountNal := PPCountNal + PPCountFull;
      PPAmountNal := PPAmountNal + PPAmountFull;
      ChequeCountNal := ChequeCountNal + ChequeCountFull;
      ChequeAmountNal := ChequeAmountNal + ChequeAmountFull;
    end;
    //Sheet.Range['D' + str + ':D' + str].NumberFormat := '0.00';
    //Sheet.Range['F' + str + ':F' + str].NumberFormat := '0.00';
    //Sheet.Range['H' + str + ':H' + str].NumberFormat := '0.00';
    str := IntToStr(start); str2 := IntToStr(pos);
    Range := Sheet.Range['A' + str + ':H' + str2];
    for j := 7 to 12 do
    begin
      Range.Borders[j].LineStyle := 1;
      Range.Borders[j].Weight := 2;
    end;
    pos := pos + 2;
  end;
  pos := pos + 1;
  Sheet.Rows[pos].Font.Bold := True;
  Sheet.Cells[pos, 1] := 'Итого по наличке:';
  Sheet.Cells[pos, 3] := CntNal;
  Sheet.Cells[pos, 4] := AmountNal;
  Sheet.Cells[pos, 5] := PPCountNal;
  Sheet.Cells[pos, 6] := PPAmountNal;
  Sheet.Cells[pos, 7] := ChequeCountNal;
  Sheet.Cells[pos, 8] := ChequeAmountNal;
  str := IntToStr(pos);
  //Sheet.Range['D' + str + ':D' + str].NumberFormat := '0.00';
  //Sheet.Range['F' + str + ':F' + str].NumberFormat := '0.00';
  //Sheet.Range['H' + str + ':H' + str].NumberFormat := '0.00';
  pos := pos + 1;
  Sheet.Rows[pos].Font.Bold := True;
  Sheet.Cells[pos, 1] := 'Итого:';
  //Sheet.Cells[pos, 3] := CntAll;
  //Sheet.Cells[pos, 4] := AmountAll;
  Sheet.Cells[pos, 5] := PPCountAll;
  Sheet.Cells[pos, 6] := PPAmountAll;
  Sheet.Cells[pos, 7] := ChequeCountAll;
  Sheet.Cells[pos, 8] := ChequeAmountAll;
  str := IntToStr(pos);
  //Sheet.Range['D' + str + ':D' + str].NumberFormat := '0.00';
  //Sheet.Range['F' + str + ':F' + str].NumberFormat := '0.00';
  //Sheet.Range['H' + str + ':H' + str].NumberFormat := '0.00';
  Range := Sheet.Range['A' + IntToStr(pos - 1) + ':H' + str];
  for j := 7 to 12 do
  begin
    Range.Borders[j].LineStyle := 1;
    Range.Borders[j].Weight := 2;
  end;
  str := ExtractFileDir(SaveToFileName);
  if not DirectoryExists(str) then
    ShowMessage('Папка ' + str + ' не существует')
  else
  begin
    try
      Book.SaveAs(SaveToFileName);
    except
      ShowMessage('Не удаётся сохранить ' + SaveToFileName +
        ': ' + LastException.Message);
    end;
  end;
  finally
    Excel := nil;
  end;
end;

function Tat_FK_PF_GetPayroll(PayorderId: String): Variant;
var
  sql: String;
begin
  Result := CreateOleObject('Microsoft.XmlDom');
  sql := ' select payroll.id as payroll_id ' +
         '       ,payroll.doc_number ' +
         '       ,payroll.doc_date ' +
         '       ,payroll.amount ' +
         '       ,(select count(*) from payrolldoc p where p.payroll_id = ' +
         '         payroll.id) as cnt ' +
         {'       ,(select count(*) from payrolldoc p ' +
         '         join payorder po on (po.id = p.doc_id) ' +
         '         join document d on (d.id = po.document_id) ' +
         '         join budgorder bo on (bo.document_id = d.parent_id) ' +
         '         where p.payroll_id = payroll.id) as cheque_cnt ' +
         '       ,(select sum(bo.amount) from payrolldoc p ' +
         '         join payorder po on (po.id = p.doc_id) ' +
         '         join document d on (d.id = po.document_id) ' +
         '         join budgorder bo on (bo.document_id = d.parent_id) ' +
         '         where p.payroll_id = payroll.id) as cheque_amount ' +}
         ' from payroll ' +
         ' join payrolldoc on (payrolldoc.payroll_id = payroll.id) ' +
         ' where payroll.dispstatus_id <> -1 ' +
         ' and payrolldoc.doc_id = ' + PayorderId;
  Result.LoadXml(RunQuery(sql));
end;

procedure Tat_FK_PF_AddPayroll(Account: String; IsNal: Boolean; PayrollId: String; DocNumber: String;
  Amount: Currency; PPAmount: Currency; PayrollDate: String; CntPO: Integer;
    CntCheque: Integer; ChequeAmount: Currency);
var
  CDS: TClientDataSet;
  Cnt: Integer;
  Amt: Currency;
begin
  CDS := Tat_FK_PF_AddPayrollAccount(Account, IsNal);
  CDS.Filter := 'PAYROLL_ID=''' + PayrollId + '''';
  CDS.Filtered := True;
  try
    if CDS.RecordCount > 0 then
    begin
      Cnt := CDS.FieldByName('PP_COUNT').AsInteger;
      Amt := CDS.FieldByName('PP_AMOUNT').AsCurrency;
      CDS.Edit;
      CDS.FieldByName('PP_COUNT').AsInteger := Cnt + 1;
      CDS.FieldByName('PP_AMOUNT').AsCurrency := Amt + PPAmount;
      CDS.Post;
    end
    else
    begin
      CDS.Insert;
      CDS.FieldByName('PAYROLL_ID').AsString := PayrollId;
      CDS.FieldByName('DOC_NUMBER').AsString := DocNumber;
      CDS.FieldByName('CNT').AsInteger := CntPO;
      CDS.FieldByName('PP_COUNT').AsInteger := 1;
      CDS.FieldByName('PP_AMOUNT').AsCurrency := PPAmount;
      CDS.FieldByName('AMOUNT').AsCurrency := Amount;
      CDS.FieldByName('DATE').AsString := XmlStrToDateStr(PayrollDate);
      CDS.FieldByName('CHEQUE_COUNT').AsInteger := CntCheque;
      CDS.FieldByName('CHEQUE_AMOUNT').AsCurrency := ChequeAmount;
      CDS.Post;
    end;
  finally
    CDS.Filtered := False;
  end;
end;

procedure Tat_FK_PF_CreateDbfFile;
begin
  CopyFile(Tat_FK_PF_TblTemplateFileName, Tat_FK_PF_TblFileName);
  Tat_FK_PF_Tbl := TTable.Create(nil);
  Tat_FK_PF_Tbl.TableType := ttFoxPro;
  Tat_FK_PF_Tbl.TableName := Tat_FK_PF_TblFileName;
  Tat_FK_PF_Tbl.EmptyTable;
  Tat_FK_PF_Tbl.Open;
end;

function Tat_FK_PF_GetElementDescription(XmlElem: Variant): String;
begin
  Result := 'ПП ID: ' + XmlElem.GetAttribute('ID') +
    ' №' + XmlElem.GetAttribute('DOC_NUMBER') +
    ' от ' + XmlElem.GetAttribute('DOC_DATE');
end;

function Tat_FK_PF_IsNal(AccId: String): Boolean;
begin
  Result := Tat_AccNalPriznakBudget(AccId, Budget_Id);
  //Result := True;
end;

function Tat_FK_PF_PayerForGround(Payer: String): String;
var
  i, L, M: Integer;
begin
  Result := '';
  L := Length(Payer);
  while (L > 2) do
  begin
    if Payer[L] = ')' then Break;
    L := L - 1;
  end;
  if L <= 2 then Exit;
  M := L - 1;
  while M > 0 do
  begin
    if Payer[M] = ',' then Break;
    M := M - 1;
  end;
  if (M <= 0) or (L - M < 2) then Exit;
  Result := Trim(Copy(Payer, M + 1, L - M - 1));
end;




//==============================================================================
//==============================================================================

begin
end.
