unit MainUnit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, ExtCtrls, StdCtrls, Grids, ComObj, ActiveX;

type
  TMainForm = class(TForm)
    MainStatusBar: TStatusBar;
    MainPageControl: TPageControl;
    TabSheetKontragent: TTabSheet;
    TabSheetSaldo: TTabSheet;
    TabSheetRezult: TTabSheet;
    Panel1: TPanel;
    GroupBoxVzamKontr: TGroupBox;
    GroupBox2: TGroupBox;
    StringGridVzamKontr: TStringGrid;
    StringGridVzamKontrDogovor: TStringGrid;
    Panel2: TPanel;
    GroupBoxSaldoKontr: TGroupBox;
    GroupBoxSaldokontrDogovor: TGroupBox;
    StringGridSaldoKontr: TStringGrid;
    StringGridSaldoDogovor: TStringGrid;
    Panel3: TPanel;
    EditVzamKontr: TEdit;
    ButtonSelectVzamKontr: TButton;
    ProgressBarImportVzamKontr: TProgressBar;
    ButtonImportVzamKontr: TButton;
    EditFRVzamKontr: TEdit;
    Label1: TLabel;
    EditPathSaldo: TEdit;
    ButtonSelectSaldo: TButton;
    EditFRSaldo: TEdit;
    ButtonImportSaldo: TButton;
    ProgressBarImportSaldo: TProgressBar;
    Label2: TLabel;
    ButtonSelect: TButton;
    ButtonCalcRaznica: TButton;
    ButtonExport: TButton;
    ProgressBarExportRezult: TProgressBar;
    MainOpenDialog: TOpenDialog;
    PageControlR: TPageControl;
    TabSheetRUspex: TTabSheet;
    TabSheetError: TTabSheet;
    GroupBoxRUspex: TGroupBox;
    GroupBoxRUDogovor: TGroupBox;
    StringGridRUKontragent: TStringGrid;
    StringGridRUDogovor: TStringGrid;
    GroupBoxErrorVzaimSKontragentami: TGroupBox;
    GroupBoxErrorDogovor: TGroupBox;
    StringGridErrorVzamSKontragent: TStringGrid;
    StringGridErrorSaldo: TStringGrid;
    CBSelectTKontr: TComboBox;
    CBColSelectKontr: TComboBox;
    EditSearchKontr: TEdit;
    ButtonSearhKontr: TButton;
    CBSelectTableSaldo: TComboBox;
    CBSelectColSaldo: TComboBox;
    EditSearchSaldo: TEdit;
    ButtonSearchSaldo: TButton;
    CBSelectTableRU: TComboBox;
    CBSelectColRU: TComboBox;
    EditSearchRU: TEdit;
    ButtonSearchRU: TButton;
    procedure ButtonSelectVzamKontrClick(Sender: TObject);
    procedure ButtonSelectSaldoClick(Sender: TObject);
    procedure ButtonImportVzamKontrClick(Sender: TObject);
    procedure ButtonImportSaldoClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure ButtonSelectClick(Sender: TObject);
    procedure ButtonCalcRaznicaClick(Sender: TObject);
    procedure ButtonExportClick(Sender: TObject);
    procedure ButtonSearhKontrClick(Sender: TObject);
    procedure ButtonSearchSaldoClick(Sender: TObject);
    procedure ButtonSearchRUClick(Sender: TObject);
  private
    { Private declarations }

  public
    { Public declarations }
  end;

var
  MainForm: TMainForm;

implementation

{$R *.dfm}
function nom_dog(xstr:string):string;
  var
  xp:integer;
  tr,st1,st2:string;
   begin
   nom_dog:='';
   st1:='№';
   st2:='от';
   xp:=AnsiPos(st1,xstr);
   if(xp>0) then tr:=Copy(xstr,xp+1,Length(xstr)-xp) else tr:=xstr;
   xp:=AnsiPos(st2,tr);
   if(xp>0) then tr:=Copy(tr,1,xp-2) else tr:=tr;
   nom_dog:=Trim(tr);
   end;
function kontragent_detect(str:string):integer;
  begin
  kontragent_detect:=0;
    if(Length(str)=10) then  kontragent_detect:=1; //Фирма
    if(Length(str)=12) then  kontragent_detect:=2; // ИП
  end;
function detect_inn(str:string):bool;
   var
   x:array[1..24] of integer;
   rezult,i,n:integer;
  begin
    x[1]:=AnsiPos(' ', str);
    x[2]:=AnsiPos('/', str);
    x[3]:=AnsiPos('-', str);
    x[4]:=AnsiPos('.', str);
    x[5]:=AnsiPos('А', str);
    x[6]:=AnsiPos('а', str);
    x[7]:=AnsiPos('О', str);
    x[8]:=AnsiPos('о', str);
    x[9]:=AnsiPos('И', str);
    x[10]:=AnsiPos('и', str);
    x[11]:=AnsiPos('У', str);
    x[12]:=AnsiPos('у', str);
    x[13]:=AnsiPos('Й', str);
    x[14]:=AnsiPos('й', str);
    x[15]:=AnsiPos('Е', str);
    x[16]:=AnsiPos('е', str);
    x[17]:=AnsiPos('Э', str);
    x[18]:=AnsiPos('э', str);
    x[19]:=AnsiPos('Ю', str);
    x[20]:=AnsiPos('ю', str);
    x[21]:=AnsiPos('Ё', str);
    x[22]:=AnsiPos('ё', str);
    x[23]:=AnsiPos('Я', str);
    x[24]:=AnsiPos('я', str);
    rezult:=0;
    n:=24;
    for i := 1 to n do
      begin
        rezult:=rezult+x[i];
      end;

if (rezult=0) then detect_inn:=true  else detect_inn:=false;
end;
function n_dogovor_detect(str:string):boolean;
  var
  x1,x2,x3,x4,x5,x6,x7,r,r2:integer;
  begin
   n_dogovor_detect:=false;
   x1:=AnsiPos('/',str);
   x2:=AnsiPos('Д',str);
   x3:=AnsiPos('+',str);
   x4:=AnsiPos('А',str);
   x5:=AnsiPos('З',str);
   x6:=AnsiPos('С',str);
   x7:=AnsiPos('№',str);
   r:=x4+x5+x6+x3;
   r2:=x1+x2+x7;
   if((r2>0)and(x7>0)and(r=0)) then n_dogovor_detect:=true;
  end;
  function n_dogovor_detect2(str:string):boolean;
  var
  x1,x2,x3,x4,x5,x6,r:integer;
  begin
   n_dogovor_detect2:=false;
   x1:=AnsiPos('/',str);
   x2:=AnsiPos('Д',str);
   x3:=AnsiPos('+',str);
   x4:=AnsiPos('А',str);
   x5:=AnsiPos('З',str);
   x6:=AnsiPos('С',str);

   r:=x4+x5+x6+x3;

   if((x1>0)and(x2>0)and(r=0)) then n_dogovor_detect2:=true;
  end;
  //
  function search_row_inn(Grid:TStringGrid;xstr:string; ic:integer):integer;
    var
  i,n,r:integer;
  st:string;
    begin
    search_row_inn:=0;
   n:=Grid.RowCount;
   i:=1;
   while  (i<n) do
    begin
    //AnsiPos(Form1.Edit2.Text,Form1.Edit1.Text);
    st:=Grid.Cells[ic,i];
    r:=AnsiCompareStr(st,xstr );
    if (r=0) then
      begin
      search_row_inn:=i;
      break;
      end
    else
      begin
      i:=i+1;
      end;
    end;
    end;
 //
 function search_row_inn2(Grid:TStringGrid;xstr:string; xstr2:string;ic:integer;ic2:integer):integer;
    var
  i,n,r,r2:integer;
  st,st2:string;
    begin
    search_row_inn2:=0;
   n:=Grid.RowCount;
   i:=1;
   while  (i<n) do
    begin
    //AnsiPos(Form1.Edit2.Text,Form1.Edit1.Text);
    st:=Grid.Cells[ic,i];
    r:=AnsiCompareStr(st,xstr );
    st2:=Grid.Cells[ic2,i];
    r2:=AnsiCompareStr(st2,xstr2 );
    if ((r=0)and(r2=0)) then
      begin
      search_row_inn2:=i;
      break;
      end
    else
      begin
      i:=i+1;
      end;
    end;
    end;
 //
procedure Xls_Open(XLSFile:string; Grid:TStringGrid;Grid2:TStringGrid;Progres:TProgressBar; firstrow:integer);
 const
  xlCellTypeLastCell = $0000000B;
var
  ExlApp, Sheet: OLEVariant;
 r,x,y,rowindex,max:integer;
 st,st2,summa,op:string;
begin
  ExlApp := CreateOleObject('Excel.Application');
  ExlApp.Visible := false;
  ExlApp.Workbooks.Open(XLSFile);
  Sheet := ExlApp.Workbooks[ExtractFileName(XLSFile)].WorkSheets[1];
  Sheet.Cells.SpecialCells(xlCellTypeLastCell, EmptyParam).Activate;
    r := ExlApp.ActiveCell.Row-firstrow;
    max:=r;
    Progres.Max:=max;
    //:=(r-1)-StrToInt(MainForm.LEFirstRowKontragent.Text);
    Grid.RowCount:=max;
    //r-StrToInt(MainForm.LEFirstRowKontragent.Text);
    rowindex:=firstrow;
    x:=1;
    y:=1;
     while rowindex<r do
      begin
      st:=trim(sheet.cells[rowindex,1]);
      st2:=trim(sheet.cells[rowindex,2]);
      Grid.Cells[4,x]:='0';
        if(n_dogovor_detect(st)) then
          begin
          Grid2.RowCount:=Grid2.RowCount+1;
          Grid2.Cells[0,y]:= IntToStr(y);
          Grid2.Cells[1,y]:=op;
          //nom_dog(xstr:string)
          Grid2.Cells[2,y]:= nom_dog(trim(sheet.cells[rowindex,1]));
          if((Length(op)=10) or (Length(op)=9)) then
           begin
             summa:=trim(sheet.cells[rowindex,7]);
             Grid2.Cells[3,y]:='0';
             if(Length(summa)=0)then Grid2.Cells[3,y]:=trim(sheet.cells[rowindex,8]) else  Grid2.Cells[3,y]:=summa;
           end;
          if((Length(op)=11) or (Length(op)=12)) then
            begin
            summa:=trim(sheet.cells[rowindex,8]);
            Grid2.Cells[3,y]:='0';
            if(Length(summa)=0)then Grid2.Cells[3,y]:=trim(sheet.cells[rowindex,7]) else  Grid2.Cells[3,y]:=summa;
            end;
          y:=y+1;
          end
          else
          begin
          Grid.RowCount:=Grid.RowCount+1;
          Grid.Cells[0,x]:= IntToStr(x);
          Grid.Cells[1,x]:= trim(sheet.cells[rowindex,1]);
          Grid.Cells[2,x]:= trim(sheet.cells[rowindex,2]);
          op:=trim(sheet.cells[rowindex,2]);
          Grid.Cells[4,x]:='0';
           if(kontragent_detect(st2)=1) then
            begin
            if((Length(op)=9) or (Length(op)=10)) then
           begin
             summa:=trim(sheet.cells[rowindex,7]);
             Grid.Cells[4,x]:='0';
             if(Length(summa)=0)then  Grid.Cells[4,x]:=trim(sheet.cells[rowindex,8]) else  Grid.Cells[4,x]:=summa;
           end;
            Grid.Cells[3,x]:='Контрагент.Фирма';
            end;
           if(kontragent_detect(st2)=2) then
            begin
            if((Length(op)=11) or (Length(op)=12)) then
           begin
             summa:=trim(sheet.cells[rowindex,8]);
             Grid.Cells[4,x]:='0';
             if(Length(summa)=0)then  Grid.Cells[4,x]:=trim(sheet.cells[rowindex,7]) else  Grid.Cells[4,x]:=summa;
           end;
            Grid.Cells[3,x]:='Контрагент.ИП';
            end;
           x:=x+1;
          end;
     rowindex:=rowindex+1;
     Progres.Position:=Progres.Position+1;
      end;
 ExlApp.Quit;
 ExlApp := Unassigned;
 Sheet := Unassigned;
 Progres.Position:=0;
end;
// Функции распознавания мусора
function neaz(xstr:string):boolean;
var
 z:integer;
 str:string;
  begin
  neaz:=true;
  str:='АЗ';
  z:=AnsiPos(str,xstr);
  if(z>0)then neaz:=false;
  end;
      function netipdogovora(zstr:string):boolean;
  var
s:array[1..15] of string;
i,n,r:integer;
st:string;
rb:boolean;
  begin
  s[1]:='Оптовая реализация нефтепродуктов и СУГ';
  s[2]:='Реализация услуг по аренде и лизингу';
  s[3]:='Прочие расчеты с покупателями';
  s[4]:='Реализация услуг по транспортировке нефтепродуктов';
  s[5]:='Реализация иных товаров';
  s[6]:='Реализация услуг по перевалке и хранению нефтепродуктов';
  s[7]:='Реализация комиссионных/агентских услуг';
  s[8]:='Расчеты с агентом по реализации товаров, работ, услуг';
  s[9]:='Услуги по программам лояльности';
  s[10]:='Реализация услуг по аренде и лизингу';
  s[11]:='Оптовая реализация нефтепродуктов и СУГ по гос.заказам федеральн.программам';
  s[12]:='Реализация других услуг';
  s[13]:='Управление';
  s[14]:='Итого';
  s[15]:='Розничная реализация нефтепродуктов и СУГ';
  n:=15;
  rb:=true;
   for i:=1 to n do
    begin
  st:=s[i];
  r:=AnsiCompareStr( zstr,st );
   if (r=0) then
      begin
        rb:=false;      
    end;
    end;
   netipdogovora:=rb;
  end;

  procedure Xls_Open2(XLSFile:string; Grid:TStringGrid;Grid2:TStringGrid;Progres:TProgressBar; firstrow:integer);
 const
  xlCellTypeLastCell = $0000000B;
var
  ExlApp, Sheet: OLEVariant;
 r,x,y,rowindex,max,colinn:integer;
 st,dogovor,summa1,summa2:string;
begin
  ExlApp := CreateOleObject('Excel.Application');
  ExlApp.Visible := false;
  ExlApp.Workbooks.Open(XLSFile);
  Sheet := ExlApp.Workbooks[ExtractFileName(XLSFile)].WorkSheets[1];
  Sheet.Cells.SpecialCells(xlCellTypeLastCell, EmptyParam).Activate;
  colinn:=0;
    r := ExlApp.ActiveCell.Row-firstrow;
    max:=r;
    Progres.Max:=max;
    //:=(r-1)-StrToInt(MainForm.LEFirstRowKontragent.Text);
    //r-StrToInt(MainForm.LEFirstRowKontragent.Text);
    rowindex:=firstrow;
    x:=1;
    y:=1;
     while rowindex<max do
      begin
      st:=trim(sheet.cells[rowindex,1]);
      if(n_dogovor_detect2(st)) then
        begin
        //Grid2.Cells[2,y]:= nom_dog(trim(sheet.cells[rowindex,1]));
        dogovor:=nom_dog(trim(sheet.cells[rowindex,1]));
        summa1:=trim(sheet.cells[rowindex,7]);
        summa2:=trim(sheet.cells[rowindex,8]);
        Grid2.RowCount:=Grid2.RowCount+1;
        end
       else if(detect_inn(st)) then
         begin
         colinn:=colinn+1;
         Grid2.Cells[0,y]:=IntToStr(y);
         Grid2.Cells[1,y]:=st;
         Grid2.Cells[2,y]:=dogovor;
         Grid2.Cells[3,y]:=summa1;
         Grid2.Cells[4,y]:=summa2;
         if(colinn=1) then
           begin
            Grid.Cells[2,x]:=st;
            x:=x+1;
           end;
         y:=y+1;
         end
         else
          begin
          if (neaz(st)) then
            begin
            if(netipdogovora(st)) then
              begin
              colinn:=0;
            Grid.RowCount:=Grid.RowCount+1;
            Grid.Cells[0,x]:=IntToStr(x);
            Grid.Cells[1,x]:=st;
            Grid.Cells[3,x]:=trim(sheet.cells[rowindex,7]);
            Grid.Cells[4,x]:=trim(sheet.cells[rowindex,8]);

            end;
            end;
          end;
     rowindex:=rowindex+1;
     Progres.Position:=Progres.Position+1;
      end;
 ExlApp.Quit;
 ExlApp := Unassigned;
 Sheet := Unassigned;
 Progres.Position:=0;
end;

procedure AutoSizeGridColumn(Grid: TStringGrid; column: integer);
var
 i: integer;
 temp: integer;
 max: integer;
begin
 max := 0;
 for i := 0 to (Grid.RowCount - 1) do
  begin
  temp := Grid.Canvas.TextWidth(grid.cells[column, i]);
  if temp > max then max := temp;
  end;
 Grid.ColWidths[column] := Max + Grid.GridLineWidth + 3;
end;

procedure TMainForm.ButtonSelectVzamKontrClick(Sender: TObject);
begin
if(MainForm.MainOpenDialog.Execute) then MainForm.EditVzamKontr.Text:=MainForm.MainOpenDialog.FileName;
end;

procedure TMainForm.ButtonSelectSaldoClick(Sender: TObject);
begin
if(MainForm.MainOpenDialog.Execute) then MainForm.EditPathSaldo.Text:=MainForm.MainOpenDialog.FileName;
end;

procedure TMainForm.ButtonImportVzamKontrClick(Sender: TObject);
var
fn:string;
fr:integer;
i:integer;
begin
fn:=MainForm.EditVzamKontr.Text;
fr:=StrToInt(MainForm.EditFRVzamKontr.Text);
Xls_Open(fn, MainForm.StringGridVzamKontr,MainForm.StringGridVzamKontrDogovor,MainForm.ProgressBarImportVzamKontr,fr);
for i:=0 to 4 do AutoSizeGridColumn(MainForm.StringGridVzamKontr, i);
for i:=0 to 3 do AutoSizeGridColumn(MainForm.StringGridVzamKontrDogovor, i);
end;

procedure TMainForm.ButtonImportSaldoClick(Sender: TObject);
var
fn:string;
fr:integer;
i:integer;
begin
fn:=MainForm.EditPathSaldo.Text;
fr:=StrToInt(MainForm.EditFRSaldo.Text);
Xls_Open2(fn, MainForm.StringGridSaldoKontr,MainForm.StringGridSaldoDogovor,MainForm.ProgressBarImportSaldo,fr);
for i:=0 to 4 do AutoSizeGridColumn(MainForm.StringGridSaldoKontr,i);
for i:=0 to 4 do AutoSizeGridColumn(MainForm.StringGridSaldoDogovor, i);
end;

procedure TMainForm.FormCreate(Sender: TObject);
begin
//
 MainForm.StringGridVzamKontr.Cells[0,0]:='ID';
 MainForm.StringGridVzamKontr.Cells[1,0]:='Контрагент';
 MainForm.StringGridVzamKontr.Cells[2,0]:='ИНН';
 MainForm.StringGridVzamKontr.Cells[3,0]:='Тип контрагента';
 MainForm.StringGridVzamKontr.Cells[4,0]:='Долг фирмы/клиента';
 //
 MainForm.StringGridVzamKontrDogovor.Cells[0,0]:='№';
 MainForm.StringGridVzamKontrDogovor.Cells[1,0]:='ИНН';
 MainForm.StringGridVzamKontrDogovor.Cells[2,0]:='Договор';
 MainForm.StringGridVzamKontrDogovor.Cells[3,0]:='Долг фирмы/клиента';
 //
 MainForm.StringGridSaldoKontr.Cells[0,0]:='№';
 MainForm.StringGridSaldoKontr.Cells[1,0]:='Контрагент';
 MainForm.StringGridSaldoKontr.Cells[2,0]:='ИНН';
 MainForm.StringGridSaldoKontr.Cells[3,0]:='Дебет';
 MainForm.StringGridSaldoKontr.Cells[4,0]:='Кредит';
 //
 MainForm.StringGridSaldoDogovor.Cells[0,0]:='№';
 MainForm.StringGridSaldoDogovor.Cells[1,0]:='ИНН';
 MainForm.StringGridSaldoDogovor.Cells[2,0]:='Договор';
 MainForm.StringGridSaldoDogovor.Cells[3,0]:='Дебет';
 MainForm.StringGridSaldoDogovor.Cells[4,0]:='Кредит';
 //
 MainForm.StringGridRUKontragent.Cells[0,0]:='№';
 MainForm.StringGridRUKontragent.Cells[1,0]:='Контрагент';
 MainForm.StringGridRUKontragent.Cells[2,0]:='ИНН';
 MainForm.StringGridRUKontragent.Cells[3,0]:='Данные АИС ТПС';
 MainForm.StringGridRUKontragent.Cells[4,0]:='Данные ТИС';
 MainForm.StringGridRUKontragent.Cells[5,0]:='Разница';
//
 MainForm.StringGridRUDogovor.Cells[0,0]:='№';
 MainForm.StringGridRUDogovor.Cells[1,0]:='ИНН';
 MainForm.StringGridRUDogovor.Cells[2,0]:='Договор';
 MainForm.StringGridRUDogovor.Cells[3,0]:='Данные АИС ТПС';
 MainForm.StringGridRUDogovor.Cells[4,0]:='Данные ТИС';
 MainForm.StringGridRUDogovor.Cells[5,0]:='Разница';
 //
 MainForm.StringGridErrorVzamSKontragent.Cells[0,0]:='№';
 MainForm.StringGridErrorVzamSKontragent.Cells[1,0]:='Контрагент/Договор';
 MainForm.StringGridErrorVzamSKontragent.Cells[2,0]:='ИНН';
 MainForm.StringGridErrorVzamSKontragent.Cells[3,0]:='Тип контрагента';
 MainForm.StringGridErrorVzamSKontragent.Cells[4,0]:='Долг фирмы/клиента';
 //
 MainForm.StringGridErrorSaldo.Cells[0,0]:='№';
 MainForm.StringGridErrorSaldo.Cells[1,0]:='Контрагент/Договор';
 MainForm.StringGridErrorSaldo.Cells[2,0]:='ИНН';
 MainForm.StringGridErrorSaldo.Cells[3,0]:='Дебет';
 MainForm.StringGridErrorSaldo.Cells[4,0]:='Кредит';
end;

procedure TMainForm.ButtonSelectClick(Sender: TObject);
  var
  i,n,x,icol,icol2,r,m,j,z:integer;
  st,st2,summa1,summa2:string;
begin
//Выборка контрагентов
i:=1;
n:=MainForm.StringGridVzamKontr.RowCount;
icol:=2;
x:=1;
st:='';
 while(i<n) do
  begin
   st:=MainForm.StringGridVzamKontr.Cells[2,i]; //
     r:=search_row_inn(MainForm.StringGridSaldoKontr,st,icol);
     if((r>0)and(Length(st)>0)) then
      begin
      MainForm.StringGridRUKontragent.RowCount:=MainForm.StringGridRUKontragent.RowCount+1;
      MainForm.StringGridRUKontragent.Cells[0,x]:=MainForm.StringGridVzamKontr.Cells[0,i];
      MainForm.StringGridRUKontragent.Cells[1,x]:=MainForm.StringGridVzamKontr.Cells[1,i];
      MainForm.StringGridRUKontragent.Cells[2,x]:=MainForm.StringGridVzamKontr.Cells[2,i];
      MainForm.StringGridRUKontragent.Cells[3,x]:=MainForm.StringGridVzamKontr.Cells[4,i];
      summa1:=MainForm.StringGridSaldoKontr.Cells[3,r];
      summa2:=MainForm.StringGridSaldoKontr.Cells[4,r];
      if(Length(summa1)=0) then MainForm.StringGridRUKontragent.Cells[4,x]:=summa2;
      if(Length(summa2)=0) then MainForm.StringGridRUKontragent.Cells[4,x]:=summa1;
      if((Length(summa1)=0) and (Length(summa2)=0)) then MainForm.StringGridRUKontragent.Cells[4,x]:='0';
      x:=x+1;
      end;
   i:=i+1;
  end;
  // Корректировка ширины
for i:=0 to 5 do AutoSizeGridColumn(MainForm.StringGridRUKontragent, i);
//Выборка договоров
//search_row_inn2(Grid:TStringGrid;xstr:string; xstr2:string;ic:integer;ic2:integer):integer;
i:=1;
n:=MainForm.StringGridVzamKontrDogovor.RowCount;
icol:=1;
icol2:=2;
x:=1;
st:='';
st2:='';
 while(i<n) do
  begin
   st:=MainForm.StringGridVzamKontrDogovor.Cells[1,i]; //
   st2:=MainForm.StringGridVzamKontrDogovor.Cells[2,i];
   r:=search_row_inn2(MainForm.StringGridSaldoDogovor,st,st2,icol,icol2);
     if((r>0)and(Length(st)>0)) then
      begin
      MainForm.StringGridRUDogovor.RowCount:=MainForm.StringGridRUDogovor.RowCount+1;
      MainForm.StringGridRUDogovor.Cells[0,x]:=MainForm.StringGridVzamKontrDogovor.Cells[0,i];
      MainForm.StringGridRUDogovor.Cells[1,x]:=MainForm.StringGridVzamKontrDogovor.Cells[1,i];
      MainForm.StringGridRUDogovor.Cells[2,x]:=MainForm.StringGridVzamKontrDogovor.Cells[2,i];
      MainForm.StringGridRUDogovor.Cells[3,x]:=MainForm.StringGridVzamKontrDogovor.Cells[3,i];
      summa1:=MainForm.StringGridSaldoDogovor.Cells[3,r];
      summa2:=MainForm.StringGridSaldoDogovor.Cells[4,r];
      if(Length(summa1)=0) then MainForm.StringGridRUDogovor.Cells[4,x]:=summa2;
      if(Length(summa2)=0) then MainForm.StringGridRUDogovor.Cells[4,x]:=summa1;
      if((Length(summa1)=0)and(Length(summa2)=0)) then MainForm.StringGridRUDogovor.Cells[4,x]:='0';
      MainForm.StringGridRUDogovor.Cells[6,x]:=IntToStr(r);
      MainForm.StringGridRUDogovor.Cells[7,x]:=MainForm.StringGridSaldoDogovor.Cells[1,r];
      x:=x+1;
      end;
    i:=i+1;
  end;
 // Корректировка ширины
for i:=0 to 7 do AutoSizeGridColumn(MainForm.StringGridRUDogovor, i);
//
n:=MainForm.StringGridVzamKontr.RowCount-1;
m:=MainForm.StringGridSaldoKontr.RowCount-1;
z:=1;
    for i:=1 to n do
      begin
      st:=MainForm.StringGridVzamKontr.Cells[2,i];
      if((Length(st)=9)or(Length(st)=11)or(Length(st)=0)) then
      begin
      MainForm.StringGridErrorVzamSKontragent.RowCount:=MainForm.StringGridErrorVzamSKontragent.RowCount+1;
      MainForm.StringGridErrorVzamSKontragent.Cells[0,z]:=IntToStr(z);
      MainForm.StringGridErrorVzamSKontragent.Cells[1,z]:=MainForm.StringGridVzamKontr.Cells[1,i];
      MainForm.StringGridErrorVzamSKontragent.Cells[2,z]:=MainForm.StringGridVzamKontr.Cells[2,i];
      MainForm.StringGridErrorVzamSKontragent.Cells[3,z]:=MainForm.StringGridVzamKontr.Cells[3,i];
      MainForm.StringGridErrorVzamSKontragent.Cells[4,z]:=MainForm.StringGridVzamKontr.Cells[4,i];
      z:=z+1;
      end;
      end;
 z:=1;
     for j:=1 to m do
      begin
      st:=MainForm.StringGridSaldoKontr.Cells[2,j];
      if((Length(st)=9)or(Length(st)=11)or(Length(st)=0)) then
      begin
      MainForm.StringGridErrorSaldo.RowCount:=MainForm.StringGridErrorSaldo.RowCount+1;
      MainForm.StringGridErrorSaldo.Cells[0,z]:=IntToStr(z);
      MainForm.StringGridErrorSaldo.Cells[1,z]:=MainForm.StringGridSaldoKontr.Cells[1,j];
      MainForm.StringGridErrorSaldo.Cells[2,z]:=MainForm.StringGridSaldoKontr.Cells[2,j];
      MainForm.StringGridErrorSaldo.Cells[3,z]:=MainForm.StringGridSaldoKontr.Cells[3,j];
      MainForm.StringGridErrorSaldo.Cells[4,z]:=MainForm.StringGridSaldoKontr.Cells[4,j];
      z:=z+1;
      end;
      end;
 // Корректировка ширины
for i:=0 to 4 do AutoSizeGridColumn(MainForm.StringGridErrorVzamSKontragent, i);
for i:=0 to 4 do AutoSizeGridColumn(MainForm.StringGridErrorSaldo, i);
//
end;

procedure TMainForm.ButtonCalcRaznicaClick(Sender: TObject);
 var
 i,n:integer;
 x,y,r:double;
 st1,st2,sr:string;
begin
 n:=MainForm.StringGridRUKontragent.RowCount-1;
 for i:=1 to n do
  begin
  st1:=MainForm.StringGridRUKontragent.Cells[3,i];
  st2:=MainForm.StringGridRUKontragent.Cells[4,i];
  if(Length(st1)=0) then st1:='0';
  if(Length(st2)=0) then st2:='0';
  x:=StrToFloat(st1);
  y:=StrToFloat(st2);
  r:=y-x;
  sr:=FloatToStr(r);
  MainForm.StringGridRUKontragent.Cells[5,i]:=sr;
  end;
// Корректировка ширины
for i:=0 to 5 do AutoSizeGridColumn(MainForm.StringGridRUKontragent, i);
//
n:=MainForm.StringGridRUDogovor.RowCount-1;
 for i:=1 to n do
  begin
  st1:=MainForm.StringGridRUDogovor.Cells[3,i];
  st2:=MainForm.StringGridRUDogovor.Cells[4,i];
  if(Length(st1)=0) then st1:='0';
  if(Length(st2)=0) then st2:='0';
  x:=StrToFloat(st1);
  y:=StrToFloat(st2);
  r:=y-x;
  sr:=FloatToStr(r);
  MainForm.StringGridRUDogovor.Cells[5,i]:=sr;
  end;
//
for i:=0 to 5 do AutoSizeGridColumn(MainForm.StringGridRUDogovor, i);
end;

procedure TMainForm.ButtonExportClick(Sender: TObject);
  var
  ExlApp, Sheet: OLEVariant;
  i,n,j,m,z:integer;
  st,st2:string;
begin
//
n:=MainForm.StringGridRUKontragent.RowCount-1;
m:=MainForm.StringGridRUDogovor.RowCount-1;
MainForm.ProgressBarExportRezult.Max:=n;
  ExlApp := CreateOleObject('Excel.Application');
  ExlApp.Visible := false;
  ExlApp.Workbooks.Add();
  Sheet:=ExlApp.Workbooks[1].WorkSheets[1];
  Sheet.Cells[1,1]:='Контрагент/Договор';
  Sheet.Cells[1,2]:='ИНН';
  Sheet.Cells[1,3]:='Данные АИС ТПС';
  Sheet.Cells[1,4]:='Данные ТИС';
  Sheet.Cells[1,5]:='Разница';
  z:=2;
    for i:=1 to n do
      begin
      Sheet.Cells[z,1]:=MainForm.StringGridRUKontragent.Cells[1,i];
      Sheet.Cells[z,2]:=MainForm.StringGridRUKontragent.Cells[2,i];
      Sheet.Cells[z,3]:=MainForm.StringGridRUKontragent.Cells[3,i];
      Sheet.Cells[z,4]:=MainForm.StringGridRUKontragent.Cells[4,i];
      Sheet.Cells[z,5]:=MainForm.StringGridRUKontragent.Cells[5,i];
      st:=MainForm.StringGridRUKontragent.Cells[2,i];
      z:=z+1;
      for j:=1 to m do
        begin
        st2:=MainForm.StringGridRUDogovor.Cells[1,j];
        if(st=st2) then
          begin
          Sheet.Cells[z,1]:=MainForm.StringGridRUDogovor.Cells[2,j];
          Sheet.Cells[z,2]:=MainForm.StringGridRUDogovor.Cells[1,j];
          Sheet.Cells[z,3]:=MainForm.StringGridRUDogovor.Cells[3,j];
          Sheet.Cells[z,4]:=MainForm.StringGridRUDogovor.Cells[4,j];
          Sheet.Cells[z,5]:=MainForm.StringGridRUDogovor.Cells[5,j];
          z:=z+1;
          end;

        end;
      MainForm.ProgressBarExportRezult.Position:=i;
      end;
  MainForm.ProgressBarExportRezult.Position:=0;
  n:=MainForm.StringGridErrorVzamSKontragent.RowCount-1;
  MainForm.ProgressBarExportRezult.Max:=n;
  Sheet:=ExlApp.Workbooks[1].WorkSheets[2];
  Sheet.Cells[1,1]:='Контрагент';
  Sheet.Cells[1,2]:='ИНН';
  Sheet.Cells[1,3]:='Тип контрагента';
  Sheet.Cells[1,4]:='Долг фирмы/клиента';
  for i:=1 to n do
    begin
      Sheet.Cells[i+1,1]:=MainForm.StringGridErrorVzamSKontragent.Cells[1,i];
      Sheet.Cells[i+1,2]:=MainForm.StringGridErrorVzamSKontragent.Cells[2,i];
      Sheet.Cells[i+1,3]:=MainForm.StringGridErrorVzamSKontragent.Cells[3,i];
      Sheet.Cells[i+1,4]:=MainForm.StringGridErrorVzamSKontragent.Cells[4,i];
      MainForm.ProgressBarExportRezult.Position:=i;
    end;
  MainForm.ProgressBarExportRezult.Position:=0;
   n:=MainForm.StringGridErrorSaldo.RowCount-1;
  MainForm.ProgressBarExportRezult.Max:=n;
  Sheet:=ExlApp.Workbooks[1].WorkSheets[3];
  Sheet.Cells[1,1]:='Контрагент';
  Sheet.Cells[1,2]:='ИНН';
  Sheet.Cells[1,3]:='Дебет';
  Sheet.Cells[1,4]:='Кредит';
  for i:=1 to n do
    begin
      Sheet.Cells[i+1,1]:=MainForm.StringGridErrorSaldo.Cells[1,i];
      Sheet.Cells[i+1,2]:=MainForm.StringGridErrorSaldo.Cells[2,i];
      Sheet.Cells[i+1,3]:=MainForm.StringGridErrorSaldo.Cells[3,i];
      Sheet.Cells[i+1,4]:=MainForm.StringGridErrorSaldo.Cells[4,i];
      MainForm.ProgressBarExportRezult.Position:=i;
    end;
    MainForm.ProgressBarExportRezult.Position:=0;
  ExlApp.Visible :=true;
end;

procedure TMainForm.ButtonSearhKontrClick(Sender: TObject);
var
st:string;
icol,scol,table:integer;
begin
 table:=MainForm.CBSelectTKontr.ItemIndex;
 scol:=MainForm.CBColSelectKontr.ItemIndex;
 st:=MainForm.EditSearchKontr.Text;
 case table of
 0:
 begin
 case scol of
 0:icol:=0;
 1:icol:=1;
 2:icol:=2;
 3:icol:=4;
 end;
 MainForm.StringGridVzamKontr.Row:=search_row_inn(MainForm.StringGridVzamKontr,st,icol);
 end;
 1:
 begin
 case scol of
 0:icol:=0;
 1:icol:=2;
 2:icol:=1;
 3:icol:=3;
 end;
 MainForm.StringGridVzamKontrDogovor.Row:=search_row_inn(MainForm.StringGridVzamKontrDogovor,st,icol);
 end;
 end;

end;

procedure TMainForm.ButtonSearchSaldoClick(Sender: TObject);
var
st:string;
icol,scol,table:integer;
begin
 table:=MainForm.CBSelectTableSaldo.ItemIndex;
 scol:=MainForm.CBSelectColSaldo.ItemIndex;
 st:=MainForm.EditSearchSaldo.Text;
 case table of
 0:
 begin
 case scol of
 0:icol:=0;
 1:icol:=1;
 2:icol:=2;
 3:icol:=4;
 end;
if(search_row_inn(MainForm.StringGridSaldoKontr,st,icol)>0) then MainForm.StringGridSaldoKontr.Row:=search_row_inn(MainForm.StringGridSaldoKontr,st,icol);
 end;
 1:
 begin
 case scol of
 0:icol:=0;
 1:icol:=2;
 2:icol:=1;
 3:icol:=3;
 4:icol:=4;
 end;
if(search_row_inn(MainForm.StringGridSaldoDogovor,st,icol)>0) then MainForm.StringGridSaldoDogovor.Row:=search_row_inn(MainForm.StringGridSaldoDogovor,st,icol);
 end;
end;
end;
procedure TMainForm.ButtonSearchRUClick(Sender: TObject);
var
st:string;
icol,scol,table:integer;
begin
table:=MainForm.CBSelectTableRU.ItemIndex;
 scol:=MainForm.CBSelectColRU.ItemIndex;
 st:=MainForm.EditSearchRU.Text;
 case table of
 0:
 begin
 case scol of
 0:icol:=0;
 1:icol:=1;
 2:icol:=2;
 3:icol:=3;
 4:icol:=4;
 5:icol:=5;
 end;
if(search_row_inn(MainForm.StringGridRUKontragent,st,icol)>0) then MainForm.StringGridRUKontragent.Row:=search_row_inn(MainForm.StringGridRUKontragent,st,icol);
 end;
 1:
 begin
 case scol of
 0:icol:=0;
 1:icol:=2;
 2:icol:=1;
 3:icol:=3;
 4:icol:=4;
 end;
if(search_row_inn(MainForm.StringGridRUDogovor,st,icol)>0) then MainForm.StringGridRUDogovor.Row:=search_row_inn(MainForm.StringGridRUDogovor,st,icol);
 end;
end;
end;

end.