unit uMain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ExtCtrls, ComCtrls, ComObj, DB, ADODB, xmldom, XMLIntf,
  msxmldom, XMLDoc, DBCtrls, Buttons, DBGridEh, Menus, ToolCtrlsEh,
  DBGridEhToolCtrls, GridsEh, DBAxisGridsEh, DateUtils, ImgList, DBCtrlsEh,
  IniFiles, DBGridEhGrouping, DynVarsEh, EhLibVCL, System.UITypes,
  System.AnsiStrings, PrnDbgeh, ClipBrd;

type
  TfrmMain = class(TForm)
    FirstPicker: TDateTimePicker;
    SecondPicker: TDateTimePicker;
    PredprCB: TComboBox;
    MainStatusBar: TStatusBar;
    InvoicesDBEhGridPopup: TPopupMenu;
    ChangeInvNumberBtn: TMenuItem;
    ShowInvDocBtn: TMenuItem;
    BlankCodeXMLDocument: TXMLDocument;
    AddVendoInvoiceBtn: TMenuItem;
    UpdateYearInvNumbersBtn: TMenuItem;
    PickPopup: TPopupMenu;
    SelInvWithErrorsBtn: TMenuItem;
    SelNotFoundInvBtn: TMenuItem;
    SelUslInvBtn: TMenuItem;
    MainMenu: TMainMenu;
    MenuSettBtn: TMenuItem;
    MenuStatBtn: TMenuItem;
    MenuStatusBtn: TMenuItem;
    AllStatBtn: TMenuItem;
    MenuUpdateStatXMLBtn: TMenuItem;
    MenuHelpBtn: TMenuItem;
    XMLOpenDialog: TOpenDialog;
    StatusXMLDoc: TXMLDocument;
    XMLSaveDialog: TSaveDialog;
    RecipientCB: TComboBox;
    FindFormBtn: TBitBtn;
    NotAllStatBtn: TMenuItem;
    MenuCrStatFileBtn: TMenuItem;
    MainPageControl: TPageControl;
    TovSheet: TTabSheet;
    TovDBGridEh: TDBGridEh;
    TovSelAllBtn: TButton;
    TovSelAllCB: TCheckBox;
    OnlyRepBtn: TButton;
    OnlyRepCB: TCheckBox;
    BuhSheet: TTabSheet;
    BuhSelAllBtn: TButton;
    BuhDBGridEh: TDBGridEh;
    BuhSelAllCB: TCheckBox;
    InvoicesSheet: TTabSheet;
    InvoicesDBGridEh: TDBGridEh;
    RosterDBGridEh: TDBGridEh;
    SearchPanel: TPanel;
    SearchNumDocLbl: TLabel;
    SearchEdit: TEdit;
    SearchDBNav: TDBNavigator;
    InvSelAllBtn: TButton;
    InvSelAllCB: TCheckBox;
    MainProgressBar: TProgressBar;
    StatusUpdatePanel: TPanel;
    StatusMemo: TMemo;
    InvoiceXMLDocument: TXMLDocument;
    SearchPanelBtn: TButton;
    CreateXMLBtn: TButton;
    UpdateStatusBtn: TButton;
    DeleteInvoiceBtn: TButton;
    RefreshInvoicesBtn: TButton;
    StartUpdateStatusBtn: TButton;
    SearchBtn: TButton;
    RefreshTovBtn: TButton;
    PrepareTovBtn: TButton;
    RefreshBuhBtn: TButton;
    PrepareBuhBtn: TButton;
    DocsPrintDBGridEh: TPrintDBGridEh;
    PrintTovBtn: TButton;
    PrintBuhBtn: TButton;
    TopPanel: TPanel;
    AddDescriptionBtn: TMenuItem;
    CopyInvNumberBtn: TMenuItem;
    AddForeignCountyInfoBtn: TMenuItem;
    procedure FormCreate(Sender: TObject);
    procedure PredprCBKeyUp(Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure PrepareTovBtnClick(Sender: TObject);
    procedure RefreshInvoicesBtnClick(Sender: TObject);
    procedure MainPageControlChange(Sender: TObject);
    procedure TovSelAllCBClick(Sender: TObject);
    procedure InvSelAllCBClick(Sender: TObject);
    procedure ChangeInvNumberBtnClick(Sender: TObject);
    procedure ShowInvDocBtnClick(Sender: TObject);
    procedure InvoicesDBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
      AFont: TFont; var Background: TColor; State: TGridDrawState);
    procedure TovDBGridEhGetCellParams(Sender: TObject; Column: TColumnEh; AFont:
      TFont; var Background: TColor; State: TGridDrawState);
    procedure AddVendoInvoiceBtnClick(Sender: TObject);
    procedure InvoicesDBEhGridPopupPopup(Sender: TObject);
    procedure TovDBGridEhCellClick(Column: TColumnEh);
    procedure InvoicesDBGridEhRowDetailPanelShow(Sender: TCustomDBGridEh; var CanShow: Boolean);
    procedure TovSelAllBtnClick(Sender: TObject);
    procedure InvSelAllBtnClick(Sender: TObject);
    procedure OnlyRepBtnClick(Sender: TObject);
    procedure UpdateYearInvNumbersBtnClick(Sender: TObject);
    procedure SelInvWithErrorsBtnClick(Sender: TObject);
    procedure SelNotFoundInvBtnClick(Sender: TObject);
    procedure InvoicesDBGridEhSortMarkingChanged(Sender: TObject);
    procedure TovDBGridEhSortMarkingChanged(Sender: TObject);
    procedure SearchBtnClick(Sender: TObject);
    procedure SelUslInvBtnClick(Sender: TObject);
    procedure RefreshBuhBtnClick(Sender: TObject);
    procedure BuhDBGridEhCellClick(Column: TColumnEh);
    procedure BuhDBGridEhGetCellParams(Sender: TObject; Column: TColumnEh; AFont:
      TFont; var Background: TColor; State: TGridDrawState);
    procedure BuhDBGridEhSortMarkingChanged(Sender: TObject);
    procedure PrepareBuhBtnClick(Sender: TObject);
    procedure AlterCheckBox(CheckBox: TCheckBox);
    procedure BuhSelAllCBClick(Sender: TObject);
    procedure BuhSelAllBtnClick(Sender: TObject);
    procedure MenuSettBtnClick(Sender: TObject);
    procedure MenuStatBtnClick(Sender: TObject);
    procedure MenuHelpBtnClick(Sender: TObject);
    procedure MenuUpdateStatXMLBtnClick(Sender: TObject);
    procedure MainPageControlChanging(Sender: TObject; var AllowChange: Boolean);
    procedure FindFormBtnClick(Sender: TObject);
    procedure AllStatBtnClick(Sender: TObject);
    procedure NotAllStatBtnClick(Sender: TObject);
    procedure StartUpdateStatusBtnClick(Sender: TObject);
    procedure RefreshTovBtnClick(Sender: TObject);
    procedure SearchPanelBtnClick(Sender: TObject);
    procedure CreateXMLBtnClick(Sender: TObject);
    procedure UpdateStatusBtnClick(Sender: TObject);
    procedure DeleteInvoiceBtnClick(Sender: TObject);
    procedure PrintTovBtnClick(Sender: TObject);
    procedure PrintBuhBtnClick(Sender: TObject);
    procedure AddDescriptionBtnClick(Sender: TObject);
    procedure CopyInvNumberBtnClick(Sender: TObject);
    procedure AddForeignCountyInfoBtnClick(Sender: TObject);
  private
    { Private declarations }
    procedure InsertToBase(cSelectDocs, cSpecQ, cDataQ, cFreeInvNumber: TADOQuery; idPlat: Integer);
    procedure ChangeCheckBoxValue(CheckBox: TCheckBox; Query: TADOQuery; Table: string; DBGridEh: TDBGridEh);
    function Protection(UNP: string): Boolean;
    procedure CreateXMLStatusFile(All: Boolean);
    procedure FillComBox(Query: TADOQuery; FieldName, AllText: string; WorkComBox: TComboBox);
    procedure RefreshInvoicesWithBookmark;
  public
    { Public declarations }
    RemoteStatus: Boolean;
  end;

var
  frmMain: TfrmMain;
  CBS: string;
  CBII: Smallint;
  prevPageIndex, loadIndex: Byte;
  BlankCodeArr: array[0..8] of string = ('402861', '402862', '402860', '401850',
    '402961', '402962', '402960', '401950', '401951');

implementation

uses
  uDM, uSettings, uStatistic, uAddVendorInvoice, uSearch, UtilUnit,
  StatusUpdater, uPrepInvDataThread, uInvDocs, uForeignInfo;

{$R *.dfm}

procedure TfrmMain.RefreshInvoicesWithBookmark;
var
  bm: TBookmark;
begin
  with DM.invoicesSelQ do
  begin
    bm := GetBookmark;
    Close;
    Open;
    GotoBookmark(bm);
    FreeBookmark(bm);
  end;
end;

function TfrmMain.Protection(UNP: string): Boolean;
begin
  Result := False;

  case StrToInt(Copy(UNP, 1, 1)) of
    3:
      if IndexStr(UNP, ['300327017', '391028477', '300329632', '300344912',
        '300080216', '300064325', '300065546', '300012790', '300327203',
        '300414037', '300004153', '390288788', '391004784', '300337593',
        '390173204', '300361573', '300433246', '300354572', '300026267',
        '391538330', '300726372', '300291284', '300007762', '300327430',
        '300032909', '300029000', '391716759', '391535890', '300019805',
        '391536220', '300036879', '391855888']) >= 0 then
        Result := True;
    4:
      if IndexStr(UNP, ['400001426', '400043837']) >= 0 then
        Result := True;
    6:
      if IndexStr(UNP, ['600030813', '600085602', '600327881', '600093961',
        '690792589', '600037767', '691522554', '600026835', '690000553',
        '600034081', '691926393', '600014948', '691181730', '600078148',
        '600095816', '690024460', '600057638', '600165936', '600123699',
        '600123711', '600123737', '600018599', '600311098', '690293057',
        '600020916', '600358416', '600213608', '600057482', '600115965',
        '691986220', '600043628', '600071128', '600026822', '600379485', '600004706']) >= 0 then
        Result := True;
    7:
      if IndexStr(UNP, ['700267438', '700015555', '790210205', '700031339',
        '700100651', '700101382', '700003882', '700108902', '700102626',
        '700263736', '700004105', '700107018', '700087456', '700018794',
        '700262569', '700028834', '700030651', '700018674', '700160969',
        '700008404', '700161171', '700254702', '700032455']) >= 0 then
        Result := True;
  end;
end;

procedure TfrmMain.FillComBox(Query: TADOQuery; FieldName, AllText: string; WorkComBox: TComboBox);
begin
  with Query do
  begin
    Open;
    First;
    WorkComBox.Items.Clear;
    WorkComBox.AddItem(AllText, TObject(0));

    while not eof do
    begin
      WorkComBox.AddItem(FieldByName(FieldName).AsString, TObject(FieldByName('idPredpr').AsInteger));
      Next;
    end;
  end;

  WorkComBox.ItemIndex := 0;
end;

procedure TfrmMain.FormCreate(Sender: TObject);
var
  F: TextFile;
  Ini: Tinifile;
  S1, S2, S3: string;
begin
  FillComBox(DM.PredprQ, 'NamePredpr', '<Все подразделения>', PredprCB);
  FillComBox(DM.RecipientQ, 'NamePredpr', '<Все контрагенты>', RecipientCB);
  loadIndex := 0;

  FirstPicker.DateTime := Now;
  SecondPicker.DateTime := FirstPicker.DateTime;

  CBII := -1;

  FormatSettings.DecimalSeparator := '.';

  //Пересоздание файла Gear.ini с новой структурой
  AssignFile(F, 'Gear.ini');
  Reset(F);
  Readln(F, S1);
  Readln(F, S2);
  Read(F, S3);
  CloseFile(F);

  if S1 <> '[Path]' then
  begin
    DeleteFile('Gear.ini');
    Ini := TiniFile.Create(extractfilepath(paramstr(0)) + 'Gear.ini');
    Ini.WriteString('Path', 'Path', S1);
    Ini.WriteString('Proxy', 'Ip', S2);
    Ini.WriteString('Proxy', 'Socket', S3);
    Ini.WriteString('DopUNN', 'Value', '0');
    Ini.WriteInteger('Visible', 'Value', 0);
    Ini.WriteString('Number', 'StartNumber', '1000000000');
    Ini.WriteString('Status', 'Remote', '0');
    Ini.Free;
  end;

  Ini := TiniFile.Create(extractfilepath(paramstr(0)) + 'Gear.ini');
  Self.RemoteStatus := StrToBool(Ini.ReadString('Status', 'Remote', '0'));
  Ini.Free;

  MainPageControl.ActivePageIndex := 0;

  DM.MainFormHWND := Self.Handle;
end;

procedure TfrmMain.InsertToBase(cSelectDocs, cSpecQ, cDataQ, cFreeInvNumber: TADOQuery; idPlat: Integer);
var
  count, i, docCode: Smallint;
  divisionList: IXMLNodeList;
  blank, datedog, contractNumber, docNumber: string;
  totalCost, totalCostSpec, totalVat, totalExcise, CostSpec, RosterCostVat: Currency;
  Ini: Tinifile;
  StartNumber: string;
  documentType, rateType: Byte;
  contractDate: TDateTime;
  idRoster: Integer;
begin
  if not FileExists(ExtractFilePath(Application.ExeName) + 'blankCode.xml') then
  begin
    MessageDlg('Нет файла blankCode.xml.', mtError, [mbOK], 0);
    Exit;
  end;

  Ini := TiniFile.Create(extractfilepath(paramstr(0)) + 'Gear.ini');
  StartNumber := Ini.ReadString('Number', 'StartNumber', '1000000000');
  Ini.Free;

  BlankCodeXMLDocument.FileName := ExtractFilePath(Application.ExeName) + 'blankCode.xml';
  BlankCodeXMLDocument.Active := True;

  cSelectDocs.DisableControls;
  cSelectDocs.First;

  count := 0;

  while not cSelectDocs.Eof do
  begin
    MainProgressBar.Position := 100 * cSelectDocs.RecNo div cSelectDocs.RecordCount;

    with DM do
      if cSelectDocs.FieldByName('pick').AsBoolean then
      try
        // Выбор последнего свободного номера ЭСЧФ
        with cFreeInvNumber do
        begin
          Close;
          Parameters.ParamByName('StartNum').Value := StrToInt64(StartNumber);
          Parameters.ParamByName('idPlat').Value := idPlat;
          Parameters.ParamByName('idDoc').Value := cSelectDocs.FieldByName('IdDoc').AsInteger;
          Open;
        end;

        // ----------------- Вставка информации в таблицу invoices -------------
        // Определение типа документа
        if cSelectDocs.FieldByName('doppick').AsBoolean then
          documentType := 4
        else
          case cSelectDocs.FieldByName('VidDoc').AsInteger of
            1, 9:
              documentType := 2;
            2, 10:
              documentType := 1;
          else
            documentType := 1;
          end;

        // Проверка договора
        try
          contractNumber := Copy(cSelectDocs.FieldByName('dogovor').AsString, 1,
            LastDelimiter(' ', cSelectDocs.FieldByName('dogovor').AsString) - 1);
          datedog := Copy(cSelectDocs.FieldByName('dogovor').AsString,
            LastDelimiter(' ', cSelectDocs.FieldByName('dogovor').AsString) + 1, 10);

          if Length(datedog) = 9 then
            raise EConvertError.Create('');

          contractDate := StrToDate(datedog);
        except
          on EConvertError do
          begin
            MessageDlg(cSelectDocs.FieldByName('NamePredpr').AsString +
              '. Поле "Договор" не соответствуют шаблону "номер дд.мм.гггг".' +
              'Данная счет-фактура не сформирована!', mtError, [mbOK], 0);
            cSelectDocs.Next;
            Continue;
          end;
        end;

        //Перенос указазателя в DataQ т.к цены при создании ЭСЧФ по накладным формируются по данным из DataQ, а не из SelectDocs
        //26.02.2017
        if (TComponent(cDataQ).Name = 'DataQ') then
          cDataQ.Locate('numDoc;VidDoc', VarArrayOf([cSelectDocs.FieldByName('NumDoc').AsInteger,
            cSelectDocs.FieldByName('VidDoc').AsInteger]), [loCaseInsensitive])
        else
          cDataQ.Locate('numDoc;VidDoc', VarArrayOf([cSelectDocs.FieldByName('NumDoc').AsString,
            cSelectDocs.FieldByName('VidDoc').AsInteger]), [loCaseInsensitive]);

        SpecSum := False;

        // Вычисление сумм
        with cSpecQ do
        begin
          Close;
          Parameters.ParamByName('Pr_SrCen').Value := cSelectDocs.FieldByName('Pr_SrCen').Value;
          Parameters.ParamByName('IdDoc').Value := cSelectDocs.FieldByName('IdDoc').AsInteger;

          if (TComponent(cSpecQ).Name = 'SpecQ') then
            SQL[SQL.Count - 1] := 'AND NOT (SprTovar.KodGr in (99,93) AND DocString.NDS=0)'
          else
            SQL[SQL.Count - 1] := 'AND NOT (SprTovar.KodGr in (99,93) AND tbDocTovOt.NDS=0)';
          Open;

          First;
          totalCost := 0;
          totalVat := 0;
          totalExcise := 0;

          while not Eof do
          begin
            totalCost := totalCost + FieldByName('cost').AsCurrency;
            totalVat := totalVat + FieldByName('SumNDS').AsCurrency;
            totalExcise := totalExcise + FieldByName('SumExcise').AsCurrency;
            Next;
          end;
        end;

        if SpecSum then
          totalCostSpec := totalCost + totalVat
        else
          totalCostSpec := totalCost;

        // Вставка информации в таблицу invoices
        InsertInvoice(idPlat, cFreeInvNumber.FieldByName('compilingYear').AsInteger,
          cFreeInvNumber.FieldByName('number').AsVariant, documentType,
          cSelectDocs.FieldByName('DateDoc').AsVariant, idPlat, 1, False, Null,
          Null, Null, Null, Null, Null, Null, cSelectDocs.FieldByName('idPlat').AsInteger,
          1, Null, Null, Null, Null, contractNumber, contractDate, Null,
          totalCost + totalVat, totalExcise, totalVat, totalCostSpec);
        // ---------------------------------------------------------------------
        // ------------------ Вставка товаров в таблицу roster -----------------
        cSpecQ.First;

        while not cSpecQ.Eof do
        begin
          RosterCostVat := cSpecQ.FieldByName('cost').AsCurrency + cSpecQ.FieldByName('SumNDS').AsCurrency;

          if SpecSum then
            CostSpec := RosterCostVat
          else
            CostSpec := cSpecQ.FieldByName('cost').AsCurrency;

          if cSpecQ.FieldByName('NDS').AsCurrency = 0 then
            rateType := 3
          else if cSpecQ.FieldByName('idPrizLog').AsInteger = 41 then
            rateType := 4
          else
            rateType := 1;

          idRoster := InsertRoster(idPlat, cFreeInvNumber.FieldByName('compilingYear').AsVariant,
            cFreeInvNumber.FieldByName('number').AsVariant, cSpecQ.FieldByName('PNT').AsVariant,
            cSpecQ.RecNo, cSpecQ.FieldByName('NameVidTov').AsVariant, cSpecQ.FieldByName
            ('KodVED').AsVariant, Null, Null, cSpecQ.FieldByName('KolF').AsVariant,
            cSpecQ.FieldByName('price').AsVariant, CostSpec, cSpecQ.FieldByName('SumExcise').AsVariant,
            cSpecQ.FieldByName('NDS').AsVariant, rateType, cSpecQ.FieldByName('SumNDS').AsVariant,
            RosterCostVat);

          // Вставка описания что ЭСЧФ с нулевым НДС
          if rateType = 3 then
            InsertDescription(idRoster, 2);

          cSpecQ.Next;
        end;
        // ---------------------------------------------------------------------
        // Вставка грузоотправителей в таблицу consignors
        InsertConsignor(idPlat, cFreeInvNumber.FieldByName('compilingYear').AsInteger,
          cFreeInvNumber.FieldByName('number').AsVariant, cSelectDocs.FieldByName('KodSkl').AsInteger);

        // Вставка грузополучателей в таблицу consignees
        InsertConsignee(idPlat, cFreeInvNumber.FieldByName('compilingYear').AsInteger,
          cFreeInvNumber.FieldByName('number').AsVariant, cSelectDocs.FieldByName('IdPredpr').AsInteger);

        // 23.09.2021
        if Length(Trim(cSelectDocs.FieldByName('blankCode').AsString)) = 0 then
          //Выбор кода бланка из файла
          with BlankCodeXMLDocument.DocumentElement do
          begin
            divisionList := ChildNodes['divisionList'].ChildNodes;
            blank := BlankCodeArr[0]; //'402861'

            for i := 0 to divisionList.Count - 1 do
              if divisionList[i].ChildNodes['kodSkl'].Text = cSelectDocs.FieldByName('KodSkl').AsString then
              begin
                blank := BlankCodeArr[StrToInt(divisionList[i].ChildNodes['item'].Text)];
                break;
              end;
          end
        else
          blank := cSelectDocs.FieldByName('blankCode').AsString;

        // 12.05.2020
        case StrToInt(blank) of
          402860..402862, 402960..402962:
            docCode := 603;
          401850, 401950, 401951:
            docCode := 602;
        else
          docCode := 602;
        end;

        //26.02.2018
        if MainPageControl.ActivePageIndex = 0 then
          docNumber := IntToStrWithZeros(cSelectDocs.FieldByName('NumDoc').AsInteger, 7)
        else
          docNumber := cSelectDocs.FieldByName('NumDocWithZeros').Value;

        // Если документа не существует, то добавляем новый
        // Иначе обновляем существующий
        if not CheckDocumentExist(idPlat, cFreeInvNumber.FieldByName('compilingYear').AsInteger,
          cFreeInvNumber.FieldByName('number').AsVariant, docNumber, cSelectDocs.FieldByName
          ('VidDoc').AsInteger, cSelectDocs.FieldByName('KodSkl').AsInteger,
          cSelectDocs.FieldByName('IdPredpr').AsInteger) then
          InsertDocument(idPlat, cFreeInvNumber.FieldByName('compilingYear').AsInteger,
            cFreeInvNumber.FieldByName('number').AsVariant, docCode, cSelectDocs.FieldByName
            ('DateDoc').AsString, blank, cSelectDocs.FieldByName('Seria').AsVariant,
            docNumber, cSelectDocs.FieldByName('VidDoc').AsInteger, cSelectDocs.FieldByName
            ('KodSkl').AsInteger, cSelectDocs.FieldByName('IdPredpr').AsInteger, True)
        else
          UpdateDocument(idPlat, cFreeInvNumber.FieldByName('compilingYear').AsInteger,
            cFreeInvNumber.FieldByName('number').AsVariant, docCode, cSelectDocs.FieldByName
            ('DateDoc').AsString, blank, cSelectDocs.FieldByName('Seria').AsVariant,
            docNumber, cSelectDocs.FieldByName('VidDoc').AsInteger, cSelectDocs.FieldByName
            ('KodSkl').AsInteger, cSelectDocs.FieldByName('IdPredpr').AsInteger);

        // Обновление признака сформированности ЭСЧФ
        with completedUpdQ do
        begin
          Parameters.ParamByName('idSender').Value := idPlat;
          Parameters.ParamByName('compilingYear').Value := cFreeInvNumber.FieldByName
            ('compilingYear').AsInteger;
          Parameters.ParamByName('number').Value := cFreeInvNumber.FieldByName('number').AsVariant;
          ExecSQL;
        end;

        inc(count);
      except
        on EInv: EInvoiceException do
          MessageDlg(EInv.Message, mtError, [mbOK], 0);
        on E: Exception do
          MessageDlg('Ошибка создания ЭСЧФ с текстом: ' + e.Message, mtError, [mbOK], 0);
      end;

    cSelectDocs.Next;
  end;

  cSpecQ.Close;
  cSpecQ.SQL[cSpecQ.SQL.Count - 1] := '--НЕ СТИРАТЬ';

  DM.invoicesDelQ.ExecSQL;

  MessageDlg('Операция завершена успешно. ЭСЧФ подготовлено: ' + IntToStr(count), mtInformation, [mbOk], 0);
  MainStatusBar.Panels[0].Text := 'Накладных: 0';

  cSelectDocs.EnableControls;
  cSelectDocs.Close;

  BlankCodeXMLDocument.Active := False;

  if MainPageControl.ActivePageIndex = 0 then
  begin
    PrepareTovBtn.Enabled := False;
    TovSelAllCB.Enabled := False;
    TovSelAllCB.Checked := True;
    TovSelAllBtn.Enabled := False;
  end
  else
  begin
    PrepareBuhBtn.Enabled := False;
    BuhSelAllCB.Enabled := False;
    BuhSelAllCB.Checked := True;
    BuhSelAllBtn.Enabled := False;
  end;
end;

procedure TfrmMain.PrepareTovBtnClick(Sender: TObject);
begin
  with DM do
  begin
    InsertToBase(SelectDocs, SpecQ, DataQ, FreeInvNumber, NastrPredprQidPlat.AsInteger);
    SelectDocsBuh.Close;
  end;
end;

procedure TfrmMain.PrintBuhBtnClick(Sender: TObject);
begin
  DocsPrintDBGridEh.DBGridEh := BuhDBGridEh;
  BuhDBGridEh.Columns[0].Visible := False;
  BuhDBGridEh.Columns[1].Visible := False;
  DocsPrintDBGridEh.Preview;
  BuhDBGridEh.Columns[0].Visible := True;
  BuhDBGridEh.Columns[1].Visible := True;
end;

procedure TfrmMain.PrintTovBtnClick(Sender: TObject);
begin
  DocsPrintDBGridEh.DBGridEh := TovDBGridEh;
  TovDBGridEh.Columns[0].Visible := False;
  TovDBGridEh.Columns[1].Visible := False;
  DocsPrintDBGridEh.Preview;
  TovDBGridEh.Columns[0].Visible := True;
  TovDBGridEh.Columns[1].Visible := True;
end;

procedure TfrmMain.PredprCBKeyUp(Sender: TObject; var Key: Word; Shift: TShiftState);
var
  SS: Byte;
  WorkQuery: TADOQuery;
begin
  if loadIndex = 0 then
    WorkQuery := DM.PredprQ
  else
    WorkQuery := DM.PredprQBuh;

  if (PredprCB.Text <> CBS) and (PredprCB.ItemIndex = CBII) then
  begin
    with WorkQuery do
    begin
      Filtered := False;
      if not (PredprCB.Text = '') then
      begin
        Filter := 'NamePredpr LIKE '#39'%' + PredprCB.Text + '%'#39;
        Filtered := True;
      end;
    end;

    CBS := PredprCB.Text;
    SS := PredprCB.SelStart;
    CBII := PredprCB.ItemIndex;

    FillComBox(WorkQuery, 'namePredpr', '<Все подразделения>', PredprCB);

    PredprCB.DroppedDown := True;
    PredprCB.Text := CBS;
    PredprCB.SelStart := SS;
  end;
end;

procedure TfrmMain.RefreshTovBtnClick(Sender: TObject);
begin
  RefreshTovBtn.Enabled := False;
  InvoicesSheet.Enabled := False;
  BuhSheet.Enabled := False;

  with DM do
  begin
    SelectDocs.Close;
    MainStatusBar.Panels[0].Text := 'Получение данных....';

    with DataQ do
    begin
      Close;
      Parameters.ParamByName('First').Value := DateToStr(FirstPicker.Date);
      Parameters.ParamByName('Second').Value := DateToStr(SecondPicker.Date);
      Parameters.ParamByName('idPlat').Value := NastrPredprQidPlat.AsInteger;

      if PredprCB.ItemIndex > 0 then
      begin
//        PredprQ.RecNo := PredprCB.ItemIndex;
        SQL[SQL.Count - 3] := 'AND DC.KodSkl = ' + inttostr(integer(PredprCB.Items.Objects
          [PredprCB.ItemIndex])); //PredprQ.FieldByName('IdPredpr').AsString;
      end
      else
        SQL[SQL.Count - 3] := '--НЕ СТИРАТЬ1';

      if RecipientCB.ItemIndex > 0 then
      begin
//        RecipientQ.RecNo := RecipientCB.ItemIndex;
        SQL[SQL.Count - 2] := 'AND tbPlat.IdPredpr = ' + inttostr(integer(RecipientCB.Items.Objects
          [RecipientCB.ItemIndex])); //RecipientQ.FieldByName('IdPredpr').AsString;
      end
      else
        SQL[SQL.Count - 2] := '--НЕ СТИРАТЬ2';

      if OnlyRepCB.Checked then
        SQL[SQL.Count - 1] := 'AND DC.NumOtch IS NOT NULL '
      else
        SQL[SQL.Count - 1] := '--НЕ СТИРАТЬ3';
    end;

    //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    //Поток в котором получаются данные для формирования ЭСЧФ
    PrepInvDataThread := TPrepInvDataThread.Create(true, CreateQ, DataQ, InsertQ,
      SelectDocs, TovSelAllCB, TovSelAllBtn, PrepareTovBtn, RefreshTovBtn,
      PrintTovBtn, BuhSheet, InvoicesSheet, MainStatusBar, MainProgressBar);
    PrepInvDataThread.FreeOnTerminate := true;
    PrepInvDataThread.Priority := tpNormal;
    PrepInvDataThread.Start;
  end;
end;

procedure TfrmMain.RefreshInvoicesBtnClick(Sender: TObject);
var
  i, TypeVisible: Byte;
  CountStatus: array[0..9] of Integer;
  SumCompleted: array[0..3] of Currency;
  Ini: TIniFile;
  idPredpr: integer;
begin
  SearchPanel.Visible := False;
  StatusUpdatePanel.Visible := False;
  MainProgressBar.Position := 0;

  //Обнуление массивов
  for i := 0 to 9 do
  begin
    CountStatus[i] := 0;
    if i < 4 then
      SumCompleted[i] := 0;
  end;

  Ini := TiniFile.Create(extractfilepath(paramstr(0)) + 'Gear.ini');
  TypeVisible := Ini.ReadInteger('Visible', 'Value', 0);
  Ini.Free;

  with DM do
  begin
    with Data2Q do
    begin
      SQL[6] := 'SET @idPlat = ' + NastrPredprQidPlat.AsString;
      SQL[7] := 'SET @First = CAST(' + #39 + FormatDateTime('dd.mm.yyyy',
        FirstPicker.Date) + #39 + ' as datetime)';
      SQL[8] := 'SET @Second = CAST(' + #39 + FormatDateTime('dd.mm.yyyy',
        SecondPicker.Date) + #39 + ' as datetime)';

      if PredprCB.ItemIndex > 0 then
        SQL[SQL.Count - 3] := 'AND ' + inttostr(integer(PredprCB.Items.Objects[PredprCB.ItemIndex]))
          + ' in (SELECT idPredpr FROM consignors WHERE idSender = inv.idSender AND compilingYear = inv.compilingYear AND number = inv.number)'
      else
        SQL[SQL.Count - 3] := '--НЕ СТИРАТЬ1';

      if RecipientCB.ItemIndex > 0 then
      begin
        RecipientQ.RecNo := RecipientCB.ItemIndex;
        SQL[SQL.Count - 2] := 'AND recipient.idPredpr = ' + inttostr(integer(RecipientCB.Items.Objects
          [RecipientCB.ItemIndex]));
      end
      else
        SQL[SQL.Count - 2] := '--НЕ СТИРАТЬ2';

      case TypeVisible of
        1:
          SQL[SQL.Count - 1] := 'AND doc.VidDoc in (1,2) ';
        2:
          SQL[SQL.Count - 1] := 'AND doc.VidDoc in (6,9,10) ';
      else
        SQL[SQL.Count - 1] := '--НЕ СТИРАТЬ3';
      end;

      ExecSQL;
    end;

    invoicesSelQ.Close;
    invoicesSelQ.Open;

     //...........................................................
     //**********************************************************

    InvSelAllCB.Checked := True;
    SearchByDocNumber.Close;
    SearchEdit.Clear;

    if not Self.RemoteStatus then
      UpdateStatusBtn.Enabled := not invoicesSelQ.IsEmpty
    else
      UpdateStatusBtn.Enabled := False;

    CreateXMLBtn.Enabled := not invoicesSelQ.IsEmpty;
    DeleteInvoiceBtn.Enabled := not invoicesSelQ.IsEmpty;
    SearchPanelBtn.Enabled := not invoicesSelQ.IsEmpty;
    InvSelAllCB.Enabled := not invoicesSelQ.IsEmpty;
    InvSelAllBtn.Enabled := not invoicesSelQ.IsEmpty;

    for i in [0, 1, 2, 3, 4] do
      InvoicesDBEhGridPopup.Items[i].Enabled := not invoicesSelQ.IsEmpty;

    for i in [0, 1, 2] do
      PickPopup.Items[i].Enabled := not invoicesSelQ.IsEmpty;

    if MonthOf(Now()) < 3 then
      UpdateYearInvNumbersBtn.Enabled := not invoicesSelQ.IsEmpty;

    if not invoicesSelQ.IsEmpty then
    begin
      invoicesSelQ.DisableControls;
      //Накопление количества ЭСЧФ по статусам и подсчёт суммы по ВЫСТАВЛЕННЫМ! ЭСЧФ
      invoicesSelQ.First;

      while not invoicesSelQ.Eof do
      begin
        inc(CountStatus[invoicesSelQinvStatus.AsInteger]);

        if (invoicesSelQinvStatus.AsInteger in [3, 5]) then
        begin
          SumCompleted[0] := SumCompleted[0] + invoicesSelQtotalCost.AsCurrency;
          SumCompleted[1] := SumCompleted[1] + invoicesSelQtotalExcise.AsCurrency;
          SumCompleted[2] := SumCompleted[2] + invoicesSelQtotalVat.AsCurrency;
          SumCompleted[3] := SumCompleted[3] + invoicesSelQtotalCostVat.AsCurrency;
        end;

        invoicesSelQ.Next;
      end;

      with MainStatusBar do
      begin
        Panels[0].Text := 'Счёт-фактур: ' + IntToStr(invoicesSelQ.RecordCount);
        Panels[1].Text := 'В разработке: ' + inttostr(CountStatus[1]);
        Panels[2].Text := 'Выставленных: ' + inttostr(CountStatus[3]);
        Panels[3].Text := 'Выставленных.Подписанных: ' + inttostr(CountStatus[5]);
        Panels[4].Text := 'Ошибка: ' + inttostr(CountStatus[9]);
        Panels[5].Text := 'Не найдено: ' + inttostr(CountStatus[8]);
        Panels[6].Text := 'Нет данных: ' + inttostr(CountStatus[0]);
      end;

      InvoicesDBGridEh.Columns[1].Footer.Value := 'Итого по выставленным:';
      InvoicesDBGridEh.Columns[7].Footer.Value := CurrToStr(SumCompleted[0]);
      InvoicesDBGridEh.Columns[8].Footer.Value := CurrToStr(SumCompleted[1]);
      InvoicesDBGridEh.Columns[9].Footer.Value := CurrToStr(SumCompleted[2]);
      InvoicesDBGridEh.Columns[10].Footer.Value := CurrToStr(SumCompleted[3]);

      invoicesSelQ.First;
      invoicesSelQ.EnableControls;
    end
    else
    begin
      for i := 0 to MainStatusBar.Panels.Count - 1 do
        MainStatusBar.Panels[i].Text := '';

      for i in [1, 7, 8, 9, 10] do
        InvoicesDBGridEh.Columns[i].Footer.Value := '';
    end;
  end;
end;

procedure TfrmMain.MainPageControlChange(Sender: TObject);
begin
  MainPageControl.Pages[0].Highlighted := False;
  MainPageControl.Pages[1].Highlighted := False;
  MainPageControl.Pages[2].Highlighted := False;
  MainPageControl.ActivePage.Highlighted := True;

  //09.09.2019
  case MainPageControl.ActivePageIndex of
    0:
      if (prevPageIndex <> 2) or ((prevPageIndex = 2) and (loadIndex = 1)) then
      begin
        FillComBox(DM.PredprQ, 'NamePredpr', '<Все подразделения>', PredprCB);
        loadIndex := 0;
      end;
    1:
      if (prevPageIndex <> 2) or ((prevPageIndex = 2) and (loadIndex = 0)) then
      begin
        FillComBox(DM.PredprQBuh, 'NamePredpr', '<Все подразделения>', PredprCB);
        loadIndex := 1;
      end;
  end;
end;

procedure TfrmMain.UpdateStatusBtnClick(Sender: TObject);
begin
  StatusUpdatePanel.Visible := not StatusUpdatePanel.Visible;
end;

procedure TfrmMain.ChangeCheckBoxValue(CheckBox: TCheckBox; Query: TADOQuery;
  Table: string; DBGridEh: TDBGridEh);
var
  Point: Pointer;
begin
  if Query.IsEmpty then
    Exit;

  Point := Query.GetBookmark;

  with DM do
  begin
    PickChange.SQL[1] := Table;
    PickChange.Parameters.ParamByName('pick').Value := CheckBox.Checked;
    PickChange.ExecSQL;
  end;

  Query.Close;
  Query.Open;
  DBGridEh.SetFocus;
  Query.GotoBookmark(Point);
end;

procedure TfrmMain.TovSelAllCBClick(Sender: TObject);
begin
  ChangeCheckBoxValue(TovSelAllCB, DM.SelectDocs, '#invoices', TovDBGridEh);
end;

procedure TfrmMain.InvSelAllCBClick(Sender: TObject);
begin
  ChangeCheckBoxValue(InvSelAllCB, DM.invoicesSelQ, '#invoicesout', InvoicesDBGridEh);
end;

procedure TfrmMain.ChangeInvNumberBtnClick(Sender: TObject);
var
  number: string;
begin
  if (DM.invoicesSelQinvStatus.AsInteger in [8, 9]) then
  begin
    try
      with DM do
      begin
        with FreeInvNumberLight do
        begin
          Close;
          Parameters.ParamByName('idPlat').Value := NastrPredprQidPlat.AsInteger;
          Parameters.ParamByName('Year').Value := invoicesSelQcompilingYear.AsString;
          Open;

          number := FieldByName('Number').Text;
        end;

        if InputQuery('Изменение номера', 'Введите новый номер. Последний свободный номер: ', number) then
        begin
          if Length(number) > 10 then
          begin
            MessageDlg('Номер ЭСЧФ не может содержать больше 10 символов', mtError, [mbOK], 0);
            Exit;
          end;

          if StrToInt64(number) > 9999990000 then
          begin
            MessageDlg('Введённый номер слишком велик', mtError, [mbOK], 0);
            Exit;
          end;

          // Изменяем номера в реальной и временной таблице
          UpdateHistoryNumber('history', invoicesSelQidSender.AsInteger,
            invoicesSelQcompilingYear.AsInteger, invoicesSelQnumber.AsLargeInt, StrToInt64(number));
          UpdateHistoryNumber('#invoicesout', invoicesSelQidSender.AsInteger,
            invoicesSelQcompilingYear.AsInteger, invoicesSelQnumber.AsLargeInt, StrToInt64(number));

          invoicesSelQ.Close;
          invoicesSelQ.Open;
        end;
      end;
    except
      on EConvertError do
        MessageDlg('Введено неверное значение', mtError, [mbOK], 0);
      on EOleException do
        MessageDlg('Номер ' + number + ' уже существует', mtError, [mbOK], 0);
    end;
  end
  else
    MessageDlg('ЭСЧФ имеет конечный статус или о ней нет информации', mtError, [mbOK], 0);
end;

procedure TfrmMain.CopyInvNumberBtnClick(Sender: TObject);
begin
  Clipboard.AsText := DM.invoicesSelQfullNumber.AsString;
end;

procedure TfrmMain.ShowInvDocBtnClick(Sender: TObject);
begin
  with DM do
    frmInvDocs := TfrmInvDocs.Create(Self, invoicesSelQidSender.AsInteger,
      invoicesSelQcompilingYear.AsInteger, invoicesSelQnumber.AsLargeInt, invoicesSelQdocumentType.AsInteger);

  frmInvDocs.ShowModal;
  frmInvDocs.Free;
end;

procedure TfrmMain.InvoicesDBGridEhGetCellParams(Sender: TObject; Column:
  TColumnEh; AFont: TFont; var Background: TColor; State: TGridDrawState);
begin
  //$00ECEAD5
  case DM.invoicesSelQdocumentType.AsInteger of
    2:
      Background := $00FFAEAE;
    4:
      Background := $00FFA4D8;
  end;

  if DM.invoicesSelQBadR10Count.AsInteger > 0 then
    Background := $0098CAF8;

  if DM.invoicesSelQinvStatus.AsInteger = 9 then
  begin
    Background := $003E42F0;
    AFont.Color := clWhite;
  end;
end;

procedure TfrmMain.TovDBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
  AFont: TFont; var Background: TColor; State: TGridDrawState);
begin
  if DM.SelectDocsVidDoc.Value = 1 then
    Background := $00FFAEAE;
end;

procedure TfrmMain.AddDescriptionBtnClick(Sender: TObject);
var
  desc: string;
begin
  try
    with DM do
    begin
      desc := invoicesSelQdescription.AsString;

      if InputQuery('Изменение дополнительных сведений', 'Введите доп. сведения (П. 31): ', desc) then
      begin
          // Изменяем номера в реальной и временной таблице
        UpdateIvoiceDescription('invoices', invoicesSelQidSender.AsInteger,
          invoicesSelQcompilingYear.AsInteger, invoicesSelQnumber.AsLargeInt, desc);
        UpdateIvoiceDescription('#invoicesout', invoicesSelQidSender.AsInteger,
          invoicesSelQcompilingYear.AsInteger, invoicesSelQnumber.AsLargeInt, desc);

        RefreshInvoicesWithBookmark;
      end;
    end;
  except
    on E: Exception do
      MessageDlg('Ошибка при обновление доп. сведений с текстом: ' + e.Message, mtError, [mbOK], 0);
  end;
end;

procedure TfrmMain.AddForeignCountyInfoBtnClick(Sender: TObject);
begin
  with DM do
    frmForeignInfo := TfrmForeignInfo.Create(Self, invoicesSelQidSender.AsInteger,
      invoicesSelQcompilingYear.AsInteger, invoicesSelQnumber.AsLargeInt,
      invoicesSelQpTaxesNumber.AsString, invoicesSelQpTaxesDate.AsDateTime,
      invoicesSelQpDeclaration.AsString, invoicesSelQdateRelease.AsDateTime,
      invoicesSelQdateActualExport.AsDateTime);

  if frmForeignInfo.ShowModal = mrOk then
    RefreshInvoicesWithBookmark;

  frmForeignInfo.Free;
end;

procedure TfrmMain.AddVendoInvoiceBtnClick(Sender: TObject);
begin
  with DM do
    frmAddVendorInvoice := TfrmAddVendorInvoice.Create(Application,
      invoicesSelQidSender.AsInteger, invoicesSelQcompilingYear.AsInteger,
      invoicesSelQidRecipient.AsInteger, invoicesSelQnumber.AsLargeInt,
      invoicesSelQfullvendor.AsString, invoicesSelQinvoice.AsString,
      invoicesSelQvendorDate.AsDateTime, invoicesSelQdocumentType.AsInteger);

  if frmAddVendorInvoice.ShowModal <> mrCancel then
    RefreshInvoicesWithBookmark;

  frmAddVendorInvoice.Free;
end;

procedure TfrmMain.InvoicesDBEhGridPopupPopup(Sender: TObject);
begin
  with DM do
    if not invoicesSelQ.IsEmpty then
    begin
      case invoicesSelQdocumentType.AsInteger of
        1:
          if Length(Trim(invoicesSelQfullvendor.AsString)) > 0 then
            AddVendoInvoiceBtn.Caption := 'Изменить/Удалить продавца'
          else
            AddVendoInvoiceBtn.Caption := 'Добавить продавца';
        2:
          if Length(Trim(invoicesSelQinvoice.AsString)) > 0 then
            AddVendoInvoiceBtn.Caption := 'Изменить ссылку на ЭСЧФ'
          else
            AddVendoInvoiceBtn.Caption := 'Добавить ссылку на ЭСЧФ';
      end;

      AddForeignCountyInfoBtn.Enabled := invoicesSelQrCountryCode.AsString <>
        invoicesSelQpCountryCode.AsString;
    end;
end;

procedure TfrmMain.StartUpdateStatusBtnClick(Sender: TObject);
var
  ThreadStatus: TThreadStatus;
  Ini: Tinifile;
  Config: TConfig;
begin
  StatusMemo.Clear;

  Ini := TiniFile.Create(extractfilepath(paramstr(0)) + 'Gear.ini');
  Config.ProxyIp := Ini.ReadString('Proxy', 'Ip', '');
  Config.ProxySocket := Ini.ReadString('Proxy', 'Socket', '');
  Config.ProxyLogin := Ini.ReadString('Proxy', 'Login', '');
  Config.ProxyPassword := Ini.ReadString('Proxy', 'Password', '');
  Ini.Free;

  //Обновление статустов ЭСЧФ в отдельном потоке
  ThreadStatus := TThreadStatus.Create(true, DM.invoicesSelQ, MainProgressBar,
    InvoicesSheet, StatusMemo, Config);
  ThreadStatus.FreeOnTerminate := true;
  ThreadStatus.Priority := tpNormal;
  ThreadStatus.Start;
end;

procedure TfrmMain.TovDBGridEhCellClick(Column: TColumnEh);
begin
  DM.SelectDocsdoppick.ReadOnly := (Column.Index = 1) and (DM.SelectDocsVidDoc.AsInteger = 2);
end;

procedure TfrmMain.InvoicesDBGridEhRowDetailPanelShow(Sender: TCustomDBGridEh; var CanShow: Boolean);
begin
  with DM do
  begin
    if invoicesSelQ.IsEmpty then
      Exit;

    with rosterStrings do
    begin
      Close;
      Parameters.ParamByName('sender').Value := invoicesSelQidSender.AsInteger;
      Parameters.ParamByName('year').Value := invoicesSelQcompilingYear.AsInteger;
      Parameters.ParamByName('number').Value := invoicesSelQnumber.AsLargeInt;
      Open;
    end;
  end;
end;

procedure TfrmMain.AlterCheckBox(CheckBox: TCheckBox);
begin
  if CheckBox.Enabled then
    CheckBox.Checked := not CheckBox.Checked;
end;

procedure TfrmMain.TovSelAllBtnClick(Sender: TObject);
begin
  AlterCheckBox(TovSelAllCB);
end;

procedure TfrmMain.InvSelAllBtnClick(Sender: TObject);
begin
  AlterCheckBox(InvSelAllCB);
end;

procedure TfrmMain.OnlyRepBtnClick(Sender: TObject);
begin
  OnlyRepCB.Checked := not OnlyRepCB.Checked;
end;

procedure TfrmMain.UpdateYearInvNumbersBtnClick(Sender: TObject);
begin
  DM.NewYearUpdate.ExecSQL;
  RefreshInvoicesBtn.Click;
end;

procedure TfrmMain.SelInvWithErrorsBtnClick(Sender: TObject);
begin
  with DM do
  begin
    PickChange.SQL[1] := '#invoicesout';
    PickChange.Parameters.ParamByName('pick').Value := False;
    PickChange.ExecSQL;

    PickQuery.Parameters.ParamByName('Stat').Value := 9;
    PickQuery.ExecSQL;
    invoicesSelQ.Close;
    invoicesSelQ.Open;
  end;

  InvoicesDBGridEh.SetFocus;
end;

procedure TfrmMain.SelNotFoundInvBtnClick(Sender: TObject);
begin
  with DM do
  begin
    PickChange.SQL[1] := '#invoicesout';
    PickChange.Parameters.ParamByName('pick').Value := False;
    PickChange.ExecSQL;

    PickQuery.Parameters.ParamByName('Stat').Value := 8;
    PickQuery.ExecSQL;
    invoicesSelQ.Close;
    invoicesSelQ.Open;
  end;

  InvoicesDBGridEh.SetFocus;
end;

procedure MultiSorting(DBGridEh: TDBGridEh);
var
  i: Integer;
  s: WideString;
begin
  //Алгоритм сортировки по нескольким полям
  with DBGridEh.SortMarkedColumns do
  begin
    if count = 0 then
      Exit;

    s := DBGridEh.Columns[Items[0].Index].FieldName;

    if Items[0].Title.SortMarker = smUpEh then
      s := s + ' ASC'
    else
      s := s + ' DESC';

    try
      for i := 1 to count - 1 do
      begin
        s := s + ', ' + DBGridEh.Columns[Items[i].Index].FieldName;

        if Items[i].Title.SortMarker = smUpEh then
          s := s + ' ASC'
        else
          s := s + ' DESC';
      end;
    except
    end;
  end;

  s := StringReplace(s, 'fullNumber', '[compilingYear], [number]', [rfReplaceAll]);
  s := StringReplace(s, 'status', '[invStatus]', [rfReplaceAll]);
  s := StringReplace(s, 'fullvendor', '[vendorId], [vendorYear], [vendorNumber]', [rfReplaceAll]);
  s := StringReplace(s, 'invoicesType', '[documentType]', [rfReplaceAll]);
  s := StringReplace(s, 'ForeingStatus', '[pTaxesNumber], [pDeclaration]', [rfReplaceAll]);
  TADOQuery(DBGridEh.DataSource.DataSet).Sort := s;
end;

procedure TfrmMain.TovDBGridEhSortMarkingChanged(Sender: TObject);
var
  bm: TBookmark;
begin
  with DM do
  begin
    if SelectDocs.IsEmpty then
      Exit;

    bm := SelectDocs.GetBookmark;
    MultiSorting(TDBGridEh(Sender));
    SelectDocs.GotoBookmark(bm);
    SelectDocs.FreeBookmark(bm);
  end;
end;

procedure TfrmMain.InvoicesDBGridEhSortMarkingChanged(Sender: TObject);
var
  bm: TBookmark;
begin
  with DM do
  begin
    if invoicesSelQ.IsEmpty then
      Exit;

    bm := invoicesSelQ.GetBookmark;
    MultiSorting(TDBGridEh(Sender));
    invoicesSelQ.GotoBookmark(bm);
    invoicesSelQ.FreeBookmark(bm);
  end;
end;

procedure TfrmMain.SearchBtnClick(Sender: TObject);
begin
  with DM do
  begin
    with SearchByDocNumber do
    begin
      Close;
      Parameters.ParamByName('DNumber').Value := '%' + SearchEdit.Text + '%';
      Parameters.ParamByName('idPlat').Value := NastrPredprQidPlat.AsInteger;
      Open;
      First;
    end;

    if not invoicesSelQ.Locate('number', SearchByDocNumber.FieldByName('number').AsVariant,
      [loCaseInsensitive]) then
      MessageDlg('Не найдено ЭСЧФ по данному документу', mtWarning, [mbOK], 0);
  end;
end;

procedure TfrmMain.SelUslInvBtnClick(Sender: TObject);
begin
  with DM do
  begin
    PickChange.SQL[1] := '#invoicesout';
    PickChange.Parameters.ParamByName('pick').Value := False;
    PickChange.ExecSQL;

    PickUslugi.ExecSQL;
    invoicesSelQ.Close;
    invoicesSelQ.Open;
    InvoicesDBGridEh.SetFocus;
  end;
end;

procedure TfrmMain.RefreshBuhBtnClick(Sender: TObject);
begin
  RefreshBuhBtn.Enabled := False;
  TovSheet.Enabled := False;
  InvoicesSheet.Enabled := False;
  DM.SelectDocsBuh.Close;
  MainStatusBar.Panels[0].Text := 'Получение данных....';

  with DM do
  begin
    with DataQBuh do
    begin
      Close;
      Parameters.ParamByName('First').Value := DateToStr(FirstPicker.Date);
      Parameters.ParamByName('Second').Value := DateToStr(SecondPicker.Date);
      Parameters.ParamByName('idPlat').Value := NastrPredprQidPlat.AsInteger;

      if PredprCB.ItemIndex > 0 then
      begin
//        PredprQBuh.RecNo := PredprCB.ItemIndex;
        SQL[SQL.Count - 2] := 'AND Ot.IdPredprOb = ' + inttostr(integer(PredprCB.Items.Objects
          [PredprCB.ItemIndex]))//PredprQBuh.FieldByName('IdPredpr').AsString;
      end
      else
        SQL[SQL.Count - 2] := '--НЕ СТИРАТЬ1';

      if RecipientCB.ItemIndex > 0 then
      begin
//        RecipientQ.RecNo := RecipientCB.ItemIndex;
        SQL[SQL.Count - 2] := 'AND tbPlat.IdPredpr = ' + inttostr(integer(RecipientCB.Items.Objects
          [RecipientCB.ItemIndex])); //RecipientQ.FieldByName('IdPredpr').AsString;
      end
      else
        SQL[SQL.Count - 1] := '--НЕ СТИРАТЬ2';
    end;
    //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    //Поток получения данных для формирования ЭСЧФ
    PrepInvDataThread := TPrepInvDataThread.Create(true, CreateQBuh, DataQBuh,
      InsertQBuh, SelectDocsBuh, BuhSelAllCB, BuhSelAllBtn, PrepareBuhBtn,
      RefreshBuhBtn, PrintBuhBtn, TovSheet, InvoicesSheet, MainStatusBar, MainProgressBar);
    PrepInvDataThread.FreeOnTerminate := true;
    PrepInvDataThread.Priority := tpNormal;
    PrepInvDataThread.Start;
  end;
end;

procedure TfrmMain.BuhDBGridEhCellClick(Column: TColumnEh);
begin
  with DM do
    if (Column.Index = 1) and (SelectDocsBuhVidDoc.Value = 9) then
      SelectDocsBuhdoppick.ReadOnly := False
    else
      SelectDocsBuhdoppick.ReadOnly := True;
end;

procedure TfrmMain.BuhDBGridEhGetCellParams(Sender: TObject; Column: TColumnEh;
  AFont: TFont; var Background: TColor; State: TGridDrawState);
begin
  if DM.SelectDocsBuhVidDoc.Value = 9 then
    Background := $00FFAEAE;
end;

procedure TfrmMain.BuhDBGridEhSortMarkingChanged(Sender: TObject);
var
  bm: TBookmark;
begin
  with DM.SelectDocsBuh do
  begin
    if IsEmpty then
      Exit;

    bm := GetBookmark;
    MultiSorting(TDBGridEh(Sender));
    GotoBookmark(bm);
    FreeBookmark(bm);
  end;
end;

procedure TfrmMain.PrepareBuhBtnClick(Sender: TObject);
begin
  with DM do
  begin
    InsertToBase(SelectDocsBuh, SpecQBuh, DataQBuh, FreeInvNumberBuh, NastrPredprQidPlat.AsInteger);
    SelectDocs.Close;
  end;
end;

procedure TfrmMain.BuhSelAllCBClick(Sender: TObject);
begin
  ChangeCheckBoxValue(BuhSelAllCB, DM.SelectDocsBuh, '#invoicesbuh', BuhDBGridEh);
end;

procedure TfrmMain.CreateXMLBtnClick(Sender: TObject);
var
  SavePath, DopUnnFlag, InvName: string;
  Ini: Tinifile;
  count: Integer;
  flnull, protect: Boolean;
begin
  Ini := TiniFile.Create(extractfilepath(paramstr(0)) + 'Gear.ini');
  SavePath := Ini.ReadString('Path', 'Path', '');
  DopUnnFlag := Ini.ReadString('DopUNN', 'Value', '0');
  Ini.Free;

  FormatSettings.DecimalSeparator := '.';

  if not DirectoryExists(SavePath) then
  begin
    MessageDlg('Путь не существует! Задайте другой путь.', mtError, [mbOK], 0);
    Exit;
  end;

  with DM do
  begin
    protect := Protection(NastrPredprQUNN.AsString);

    invoicesSelQ.DisableControls;
    invoicesSelQ.First;
    count := 0;

    //Алгоритм создания XML файла ЭСЧФ
    while not invoicesSelQ.Eof do
    begin
      flnull := False;
      MainProgressBar.Position := 100 * invoicesSelQ.RecNo div invoicesSelQ.RecordCount;

      // and (invoicesSelQBadR10Count.AsInteger = 0)
      if (invoicesSelQpick.AsBoolean) then
      begin
        InvName := invoicesSelQsenderUNP.AsString + '-' +
          invoicesSelQcompilingYear.AsString + '-' + IntToStrWithZeros(invoicesSelQnumber.AsVariant, 10);

        if (invoicesSelQdocumentType.AsInteger in [2, 4]) and (not CheckRefund(invoicesSelQidSender.Value,
          invoicesSelQcompilingYear.Value, invoicesSelQnumber.Value)) and (Length
          (Trim(invoicesSelQdescription.AsString)) = 0) then
        begin
          MessageDlg(InvName + #10#13 +
            'Для дополнительной ЭСЧФ должно быть заполнено поле "Доп. сведения" или проставлен признак возврата у документа.',
            mtError, [mbOK], 0);
          invoicesSelQ.Next;
          Continue;
        end;

        try
          with InvoiceXMLDocument do
          begin
            XML.Clear;
            Active := True;
            Version := '1.0';

            with AddChild('issuance', 'http://www.w3schools.com') do
            begin
              Attributes['sender'] := invoicesSelQsenderUNP.AsString;

              if invoicesSelQdocumentType.AsInteger = 2 then
              begin
                //Если тип счёт фактуры "Дополнительная с ссылкой"
                with AddChild('general') do
                begin
                  ChildValues['number'] := InvName;
                  ChildValues['dateIssuance'] := FormatDateTime('yyyy-mm-dd', Now);
                  ChildValues['dateTransaction'] := FormatDateTime('yyyy-mm-dd',
                    invoicesSelQdateTransaction.AsDateTime);
                  ChildValues['documentType'] := 'ADDITIONAL';

                  if Length(Trim(invoicesSelQinvoice.AsString)) <> 0 then
                    ChildValues['invoice'] := invoicesSelQinvoice.Value
                  else
                  begin
                    MessageDlg(InvName + #10#13 + 'Ссылка на исходную ЭСЧФ не заполнена', mtError, [mbOK], 0);
                    Break;
                  end;

                  ChildValues['sendToRecipient'] := 'true';
                end;

                with AddChild('deliveryCondition') do
                begin
                  with AddChild('contract') do
                  begin
                    ChildValues['number'] := invoicesSelQcontractNumber.AsString;
                    ChildValues['date'] := FormatDateTime('yyyy-mm-dd', invoicesSelQcontractDate.AsDateTime);

                    with AddChild('documents') do
                    begin
                      documentsSelQ.Close;
                      documentsSelQ.Parameters.ParamByName('idSender').Value :=
                        invoicesSelQidSender.AsInteger;
                      documentsSelQ.Parameters.ParamByName('compilingYear').Value
                        := invoicesSelQcompilingYear.AsInteger;
                      documentsSelQ.Parameters.ParamByName('number').Value := invoicesSelQnumber.AsVariant;
                      documentsSelQ.Open;
                      documentsSelQ.First;

                      while not documentsSelQ.Eof do
                      begin
                        with AddChild('document') do
                        begin
                          with AddChild('docType') do
                          begin
                            ChildValues['code'] := documentsSelQ.FieldByName('code').AsString;

                            if (documentsSelQ.FieldByName('code').AsInteger =
                              601) and (Length(Trim(documentsSelQ.FieldByName('additionalValue').AsString))
                              > 0) then
                              ChildValues['value'] := documentsSelQ.FieldByName('additionalValue').AsString
                            else
                              ChildValues['value'] := documentsSelQ.FieldByName('value').AsString;
                          end;

                          ChildValues['date'] := FormatDateTime('yyyy-mm-dd',
                            documentsSelQ.FieldByName('docDate').AsDateTime);

                          if IndexStr(documentsSelQ.FieldByName('code').AsString, ['602', '603']) <> -1 then
                          begin
                            ChildValues['blankCode'] := documentsSelQ.FieldByName('blankCode').AsString;

                            if Length(documentsSelQ.FieldByName('seria').AsString) = 0 then
                              ChildValues['seria'] := 'АА'
                            else
                              ChildValues['seria'] := documentsSelQ.FieldByName('seria').AsString;
                          end;

                          ChildValues['number'] := documentsSelQ.FieldByName('docNumber').AsString;
                          ChildValues['refund'] := documentsSelQ.FieldByName('refund').AsString.ToLower;
                        end;

                        documentsSelQ.Next;
                      end;
                    end;
                  end;

                  if (not invoicesSelQdescription.IsNull) and (Length(Trim(invoicesSelQdescription.AsString))
                    > 0) then
                    ChildValues['description'] := invoicesSelQdescription.AsString;
                end;

                with AddChild('roster') do
                begin
                  if documentsSelQ.FieldByName('refund').AsBoolean then
                  begin
                    Attributes['totalCostVat'] := CurrToStr(invoicesSelQtotalCostVat.AsCurrency * (-1));
                    Attributes['totalExcise'] := CurrToStr(invoicesSelQtotalExcise.AsCurrency * (-1));
                    Attributes['totalVat'] := CurrToStr(invoicesSelQtotalVat.AsCurrency * (-1));
                    Attributes['totalCost'] := CurrToStr(invoicesSelQtotalCost.AsCurrency * (-1));
                  end
                  else
                  begin
                    Attributes['totalCostVat'] := invoicesSelQtotalCostVat.AsString;
                    Attributes['totalExcise'] := invoicesSelQtotalExcise.AsString;
                    Attributes['totalVat'] := invoicesSelQtotalVat.AsString;
                    Attributes['totalCost'] := invoicesSelQtotalCost.AsString;
                  end;

                  rosterSelQ.Parameters.ParamByName('idSender').Value := invoicesSelQidSender.AsInteger;
                  rosterSelQ.Parameters.ParamByName('compilingYear').Value :=
                    invoicesSelQcompilingYear.AsInteger;
                  rosterSelQ.Parameters.ParamByName('number').Value := invoicesSelQnumber.AsVariant;
                  rosterSelQ.Open;
                  rosterSelQ.First;

                  while not rosterSelQ.Eof do
                  begin
                    with AddChild('rosterItem') do
                    begin
                      ChildValues['number'] := rosterSelQ.FieldByName('itemNumber').AsInteger;
                      ChildValues['name'] := rosterSelQ.FieldByName('name').AsString;

                      if rosterSelQ.FieldByName('value').AsString <> 'CALCULATED' then
                        ChildValues['code'] := rosterSelQ.FieldByName('code').AsString;

                    //ChildValues['code_oced'] := 12345;
                    //ChildValues['units'] := 'штук';
                      ChildValues['count'] := CurrToStr(rosterSelQ.FieldByName('count').AsCurrency);
                      ChildValues['price'] := CurrToStr(rosterSelQ.FieldByName('price').AsCurrency);

                      if documentsSelQ.FieldByName('refund').AsBoolean then
                      begin
                        ChildValues['cost'] := CurrToStr(rosterSelQ.FieldByName('cost').AsCurrency * (-1));
                        ChildValues['summaExcise'] := CurrToStr(rosterSelQ.FieldByName
                          ('summaExcise').AsCurrency * (-1));
                      end
                      else
                      begin
                        ChildValues['cost'] := rosterSelQ.FieldByName('cost').AsString;
                        ChildValues['summaExcise'] := rosterSelQ.FieldByName('summaExcise').AsString;
                      end;

                      with AddChild('vat') do
                      begin
                        ChildValues['rate'] := CurrToStr(rosterSelQ.FieldByName('rate').AsCurrency);
                        ChildValues['rateType'] := rosterSelQ.FieldByName('value').AsString;
                        if documentsSelQ.FieldByName('refund').AsBoolean then
                          ChildValues['summaVat'] := CurrToStr(rosterSelQ.FieldByName
                            ('summaVat').AsCurrency * (-1))
                        else
                          ChildValues['summaVat'] := rosterSelQ.FieldByName('summaVat').AsString;
                      end;

                      if documentsSelQ.FieldByName('refund').AsBoolean then
                        ChildValues['costVat'] := CurrToStr(rosterSelQ.FieldByName
                          ('costVat').AsCurrency * (-1))
                      else
                        ChildValues['costVat'] := rosterSelQ.FieldByName('costVat').AsString;

                      descriptionsSelQ.Close;
                      descriptionsSelQ.Parameters.ParamByName('idStr').Value :=
                        rosterSelQ.FieldByName('idStr').Value;
                      descriptionsSelQ.Open;

                      if not descriptionsSelQ.IsEmpty then
                        with AddChild('descriptions') do
                          ChildValues['description'] := descriptionsSelQ.FieldByName
                            ('descriptionValue').AsString;
                    end;
                    rosterSelQ.Next;
                  end;
                  rosterSelQ.Close;
                end;
              end
              else if (invoicesSelQdocumentType.AsInteger in [1, 4]) then
              begin
                //Если тип счёт фактуры "Оригинальная" и "Дополнительная без ссылки"
                with AddChild('general') do
                begin
                  ChildValues['number'] := InvName;
                  ChildValues['dateIssuance'] := FormatDateTime('yyyy-mm-dd', Now);
                  ChildValues['dateTransaction'] := FormatDateTime('yyyy-mm-dd',
                    invoicesSelQdateTransaction.AsDateTime);
                  if invoicesSelQdocumentType.Value = 1 then
                    ChildValues['documentType'] := 'ORIGINAL'
                  else
                    ChildValues['documentType'] := 'ADD_NO_REFERENCE';
                end;

                with AddChild('provider') do
                begin
                  ChildValues['providerStatus'] := 'SELLER';
                  ChildValues['dependentPerson'] := invoicesSelQpDependentPerson.AsString;
                  ChildValues['residentsOfOffshore'] := invoicesSelQpResidentsOfOffshore.AsString;
                  ChildValues['specialDealGoods'] := invoicesSelQspecialDealGoods.AsString;
                  ChildValues['bigCompany'] := invoicesSelQpBigCompany.AsString;
                  ChildValues['countryCode'] := invoicesSelQpCountryCode.AsString;
                  ChildValues['unp'] := invoicesSelQpUNP.AsString;

                  if (invoicesSelQpBranchCode.AsInteger <> 0) and (invoicesSelQpBranchCode.AsInteger
                    <> 8000) then
                    ChildValues['branchCode'] := IntToStrWithZeros(invoicesSelQpBranchCode.AsInteger, 4);

                  ChildValues['name'] := invoicesSelQpName.AsString;
                  ChildValues['address'] := invoicesSelQpAddress.AsString;

                  if not invoicesSelQvendorId.IsNull then
                    with AddChild('vendor') do
                    begin
                      ChildValues['number'] := invoicesSelQfullvendor.AsString;
                      ChildValues['date'] := FormatDateTime('yyyy-mm-dd', invoicesSelQvendorDate.AsDateTime);
                    end;

                  if not invoicesSelQpTaxesNumber.IsNull then
                    with AddChild('taxes') do
                    begin
                      ChildValues['number'] := invoicesSelQpTaxesNumber.AsString;
                      ChildValues['date'] := FormatDateTime('yyyy-mm-dd', invoicesSelQpTaxesDate.AsDateTime);
                    end;

                  if not invoicesSelQpDeclaration.IsNull then
                  begin
                    ChildValues['declaration'] := invoicesSelQpDeclaration.AsString;
                    ChildValues['dateRelease'] := FormatDateTime('yyyy-mm-dd',
                      invoicesSelQdateRelease.AsDateTime);
                    ChildValues['dateActualExport'] := FormatDateTime('yyyy-mm-dd',
                      invoicesSelQdateActualExport.AsDateTime);
                  end;
                end;

                with AddChild('recipient') do
                begin
                  ChildValues['recipientStatus'] := 'CUSTOMER';
                  ChildValues['dependentPerson'] := invoicesSelQrDependentPerson.AsString;
                  ChildValues['residentsOfOffshore'] := invoicesSelQrResidentsOfOffshore.AsString;
                  ChildValues['specialDealGoods'] := Null; //invoicesSelQrSpecialDealGoods.AsString;
                  ChildValues['bigCompany'] := invoicesSelQrBigCompany.AsString;

                  if not invoicesSelQidRecipient.IsNull then
                  begin
                    ChildValues['countryCode'] := invoicesSelQrCountryCode.AsString;
                    ChildValues['unp'] := invoicesSelQrUNP.AsString;

                    //Добавление кода филиала если в настройках включена опция
                    if (DopUnnFlag <> '0') and (not (invoicesSelQrBranchCode.AsInteger in [0, 1])) then
                      ChildValues['branchCode'] := IntToStrWithZeros(invoicesSelQrBranchCode.AsInteger, 4);

                    ChildValues['name'] := invoicesSelQrName.AsString;
                    ChildValues['address'] := invoicesSelQrAddress.AsString;
                  end;
                end;

                with AddChild('senderReceiver') do
                begin
                  with AddChild('consignors') do
                  begin
                    consignorsSelQ.Parameters.ParamByName('idSender').Value := invoicesSelQidSender.AsInteger;
                    consignorsSelQ.Parameters.ParamByName('compilingYear').Value
                      := invoicesSelQcompilingYear.AsInteger;
                    consignorsSelQ.Parameters.ParamByName('number').Value := invoicesSelQnumber.AsVariant;
                    consignorsSelQ.Open;
                    consignorsSelQ.First;

                    while not consignorsSelQ.Eof do
                    begin
                      with AddChild('consignor') do
                      begin
                        ChildValues['countryCode'] := consignorsSelQ.FieldByName('KodCountry').AsString;
                        ChildValues['unp'] := consignorsSelQ.FieldByName('UNN').AsString;
                        ChildValues['name'] := consignorsSelQ.FieldByName('namePredpr').AsString;
                        ChildValues['address'] := consignorsSelQ.FieldByName('address').AsString;
                      end;

                      consignorsSelQ.Next;
                    end;

                    consignorsSelQ.Close;
                  end;

                  with AddChild('consignees') do
                  begin
                    consigneesSelQ.Parameters.ParamByName('idSender').Value := invoicesSelQidSender.AsInteger;
                    consigneesSelQ.Parameters.ParamByName('compilingYear').Value
                      := invoicesSelQcompilingYear.AsInteger;
                    consigneesSelQ.Parameters.ParamByName('number').Value := invoicesSelQnumber.AsVariant;
                    consigneesSelQ.Open;
                    consigneesSelQ.First;

                    while not consigneesSelQ.Eof do
                    begin
                      with AddChild('consignee') do
                      begin
                        ChildValues['countryCode'] := consigneesSelQ.FieldByName('KodCountry').AsString;
                        ChildValues['unp'] := consigneesSelQ.FieldByName('UNN').AsString;
                        ChildValues['name'] := consigneesSelQ.FieldByName('namePredpr').AsString;
                        ChildValues['address'] := consigneesSelQ.FieldByName('address').AsString;
                      end;

                      consigneesSelQ.Next;
                    end;

                    consigneesSelQ.Close;
                  end;
                end;

                with AddChild('deliveryCondition') do
                begin
                  with AddChild('contract') do
                  begin
                    ChildValues['number'] := invoicesSelQcontractNumber.AsString;
                    ChildValues['date'] := FormatDateTime('yyyy-mm-dd', invoicesSelQcontractDate.AsDateTime);

                    with AddChild('documents') do
                    begin
                      documentsSelQ.Close;
                      documentsSelQ.Parameters.ParamByName('idSender').Value :=
                        invoicesSelQidSender.AsInteger;
                      documentsSelQ.Parameters.ParamByName('compilingYear').Value
                        := invoicesSelQcompilingYear.AsInteger;
                      documentsSelQ.Parameters.ParamByName('number').Value := invoicesSelQnumber.AsVariant;
                      documentsSelQ.Open;
                      documentsSelQ.First;

                      while not documentsSelQ.Eof do
                      begin
                        with AddChild('document') do
                        begin
                          with AddChild('docType') do
                          begin
                            ChildValues['code'] := documentsSelQ.FieldByName('code').AsString;

                            if (documentsSelQ.FieldByName('code').AsInteger =
                              601) and (Length(Trim(documentsSelQ.FieldByName('additionalValue').AsString))
                              > 0) then
                              ChildValues['value'] := documentsSelQ.FieldByName('additionalValue').AsString
                            else
                              ChildValues['value'] := documentsSelQ.FieldByName('value').AsString;
                          end;

                          ChildValues['date'] := FormatDateTime('yyyy-mm-dd',
                            documentsSelQ.FieldByName('docDate').AsDateTime);

                          if IndexStr(documentsSelQ.FieldByName('code').AsString, ['602', '603']) <> -1 then
                          begin
                            ChildValues['blankCode'] := documentsSelQ.FieldByName('blankCode').AsString;

                            if Length(documentsSelQ.FieldByName('seria').AsString) = 0 then
                              ChildValues['seria'] := 'АА'
                            else
                              ChildValues['seria'] := documentsSelQ.FieldByName('seria').AsString;
                          end;

                          ChildValues['number'] := documentsSelQ.FieldByName('docNumber').AsString;

                          if invoicesSelQdocumentType.AsInteger = 4 then
                            ChildValues['refund'] := documentsSelQ.FieldByName('refund').AsString.ToLower;
                        end;

                        documentsSelQ.Next;
                      end;
                    end;
                  end;

                  if (not invoicesSelQdescription.IsNull) and (Length(Trim(invoicesSelQdescription.AsString))
                    > 0) then
                    ChildValues['description'] := invoicesSelQdescription.AsString;
                end;

                with AddChild('roster') do
                begin
                  if (invoicesSelQdocumentType.AsInteger <> 1) and (documentsSelQ.FieldByName
                    ('refund').AsBoolean) then
                  begin
                    Attributes['totalCostVat'] := CurrToStr(invoicesSelQtotalCostVat.AsCurrency * (-1));
                    Attributes['totalExcise'] := CurrToStr(invoicesSelQtotalExcise.AsCurrency * (-1));
                    Attributes['totalVat'] := CurrToStr(invoicesSelQtotalVat.AsCurrency * (-1));
                    Attributes['totalCost'] := CurrToStr(invoicesSelQtotalCost.AsCurrency * (-1));
                  end
                  else
                  begin
                    Attributes['totalCostVat'] := invoicesSelQtotalCostVat.AsString;
                    Attributes['totalExcise'] := invoicesSelQtotalExcise.AsString;
                    Attributes['totalVat'] := invoicesSelQtotalVat.AsString;
                    Attributes['totalCost'] := invoicesSelQtotalCost.AsString;

                  end;

                  rosterSelQ.Parameters.ParamByName('idSender').Value := invoicesSelQidSender.AsInteger;
                  rosterSelQ.Parameters.ParamByName('compilingYear').Value :=
                    invoicesSelQcompilingYear.AsInteger;
                  rosterSelQ.Parameters.ParamByName('number').Value := invoicesSelQnumber.AsVariant;
                  rosterSelQ.Open;
                  rosterSelQ.First;

                  while not rosterSelQ.Eof do
                  begin
                    with AddChild('rosterItem') do
                    begin
                      ChildValues['number'] := rosterSelQ.FieldByName('itemNumber').AsInteger;
                      ChildValues['name'] := rosterSelQ.FieldByName('name').AsString;

                      if rosterSelQ.FieldByName('value').AsString <> 'CALCULATED' then
                        ChildValues['code'] := rosterSelQ.FieldByName('code').AsString;

                      //ChildValues['code_oced'] := 12345;
                      //ChildValues['units'] := 'штук';
                      ChildValues['count'] := CurrToStr(rosterSelQ.FieldByName('count').AsCurrency);
                      ChildValues['price'] := CurrToStr(rosterSelQ.FieldByName('price').AsCurrency);

                      if (invoicesSelQdocumentType.AsInteger <> 1) and (documentsSelQ.FieldByName
                        ('refund').AsBoolean) then
                      begin
                        ChildValues['cost'] := CurrToStr(rosterSelQ.FieldByName('cost').AsCurrency * (-1));
                        ChildValues['summaExcise'] := CurrToStr(rosterSelQ.FieldByName
                          ('summaExcise').AsCurrency * (-1));
                      end
                      else
                      begin
                        ChildValues['cost'] := rosterSelQ.FieldByName('cost').AsString;
                        ChildValues['summaExcise'] := rosterSelQ.FieldByName('summaExcise').AsString;
                      end;

                      with AddChild('vat') do
                      begin
                        ChildValues['rate'] := CurrToStr(rosterSelQ.FieldByName('rate').AsCurrency);
                        ChildValues['rateType'] := rosterSelQ.FieldByName('value').AsString;

                        if (invoicesSelQdocumentType.AsInteger <> 1) and (documentsSelQ.FieldByName
                          ('refund').AsBoolean) then
                          ChildValues['summaVat'] := CurrToStr(rosterSelQ.FieldByName
                            ('summaVat').AsCurrency * (-1))
                        else
                          ChildValues['summaVat'] := rosterSelQ.FieldByName('summaVat').AsString;
                      end;

                      if (invoicesSelQdocumentType.AsInteger <> 1) and (documentsSelQ.FieldByName
                        ('refund').AsBoolean) then
                        ChildValues['costVat'] := CurrToStr(rosterSelQ.FieldByName
                          ('costVat').AsCurrency * (-1))
                      else
                        ChildValues['costVat'] := rosterSelQ.FieldByName('costVat').AsString;

                      descriptionsSelQ.Close;
                      descriptionsSelQ.Parameters.ParamByName('idStr').Value :=
                        rosterSelQ.FieldByName('idStr').Value;
                      descriptionsSelQ.Open;

                      if not descriptionsSelQ.IsEmpty then
                        with AddChild('descriptions') do
                        begin
                          ChildValues['description'] := descriptionsSelQ.FieldByName
                            ('descriptionValue').AsString;
                        end;
                    end;
                    rosterSelQ.Next;
                  end;

                  rosterSelQ.Close;
                end;
              end;

              if not protect then
                Sleep(600000);
            end;

            if not flnull then
            begin
              SaveToFile(SavePath + '\' + InvName + '.xml');
              Inc(count);
            end;
          end;
        except
          on e: Exception do
            MessageDlg('Ошибка при создании ЭСЧФ ' + InvName + ', с текстом: ' +
              e.Message, mtError, [mbOK], 0);
        end;
      end;

      invoicesSelQ.Next;
    end;

    invoicesSelQ.EnableControls;
  end;

  MessageDlg('Операция завершена успешно. Счёт-фактур выгружено: ' + IntToStr(count),
    mtInformation, [mbOK], 0);
end;

procedure TfrmMain.SearchPanelBtnClick(Sender: TObject);
begin
  SearchPanel.Visible := not SearchPanel.Visible;

  if SearchPanel.Visible then
    SearchEdit.SetFocus;
end;

procedure TfrmMain.BuhSelAllBtnClick(Sender: TObject);
begin
  AlterCheckBox(BuhSelAllCB);
end;

procedure TfrmMain.MenuSettBtnClick(Sender: TObject);
begin
  if not FileExists(ExtractFilePath(Application.ExeName) + 'blankCode.xml') then
  begin
    MessageDlg('Нет файла blankCode.xml.', mtError, [mbOK], 0);
    Exit;
  end;

  frmSettings := TfrmSettings.Create(Application);
  if frmSettings.ShowModal = mrOk then
    Self.RemoteStatus := frmSettings.RemoteStatusCB.Checked;
  frmSettings.Free;
end;

procedure TfrmMain.MenuStatBtnClick(Sender: TObject);
begin
  frmStatistic := TfrmStatistic.Create(Self, Self.RemoteStatus);
  frmStatistic.ShowModal;
  frmStatistic.Free;
end;

procedure TfrmMain.MenuHelpBtnClick(Sender: TObject);
begin
  MessageDlg('Фиолетовый - накладная по вычетам. (Дополнительная ЭСЧФ)' + #10#13
    + 'Сиреневый - дополнительная ЭСЧФ без ссылки.' + #10#13 +
    'Оранжевый - нет кода ТНВЕД у товара со ставкой НДС 10%.' + #10#13 +
    'Красный - ошибка загрузки на портал.', mtInformation, [mbOK], 0);
  frmMain.SetFocus;
end;

procedure TfrmMain.CreateXMLStatusFile(All: Boolean);
begin
  if not XMLSaveDialog.Execute then
  begin
    MessageDlg('Сохранение файла было отменено.', mtWarning, [mbOK], 0);
    Exit;
  end;

  with DM do
  begin
    SelectSenderList.Close;
    SelectSenderList.Parameters.ParamByName('First').Value := DateToStr(FirstPicker.Date);
    SelectSenderList.Parameters.ParamByName('Second').Value := DateToStr(SecondPicker.Date);
    SelectSenderList.Open;

    if SelectSenderList.IsEmpty then
    begin
      MessageDlg('За указанный период нет ЭСЧФ', mtWarning, [mbOK], 0);
      Exit;
    end;

    with StatusXMLDoc do
    begin
      XML.Clear;
      Active := True;
      Version := '1.0';
      Options := StatusXMLDoc.Options + [doNodeAutoIndent];

      with AddChild('root') do
      begin
        with AddChild('invoices') do
        begin
          SelectSenderList.First;

          while not SelectSenderList.Eof do
          begin
            MainProgressBar.Position := 100 * SelectSenderList.RecNo div SelectSenderList.RecordCount;

            with AddChild('sender') do
            begin
              Attributes['senderunn'] := SelectSenderListUNN.AsString;
              Attributes['senderdopunn'] := SelectSenderListdopUNN.AsString;

              SelectInvoicesList.Close;
              SelectInvoicesList.Parameters.ParamByName('First').Value := DateToStr(FirstPicker.Date);
              SelectInvoicesList.Parameters.ParamByName('Second').Value := DateToStr(SecondPicker.Date);
              SelectInvoicesList.Parameters.ParamByName('idSender').Value :=
                SelectSenderListidPredpr.AsInteger;

              if All then
                SelectInvoicesList.SQL[SelectInvoicesList.SQL.Count - 2] := 'AND (ISNULL(h.invStatus,0) not in (6,7))'
              else
                SelectInvoicesList.SQL[SelectInvoicesList.SQL.Count - 2] :=
                  'AND (ISNULL(h.invStatus,0) not in (5,6,7))';

              SelectInvoicesList.Open;

              while not SelectInvoicesList.Eof do
              begin
                with AddChild('invoice') do
                begin
                  Attributes['compilingYear'] := SelectInvoicesListcompilingYear.AsString;
                  Attributes['number'] := IntToStrWithZeros(SelectInvoicesListnumber.AsLargeInt, 10);
                  Attributes['invstatus'] := '';
                  Attributes['statusdate'] := '';
                end;

                SelectInvoicesList.Next
              end;
            end;

            SelectSenderList.Next;
          end;
        end;
      end;

      SaveToFile(XMLSaveDialog.FileName);
      XML.Clear;
    end;
  end;

  MessageDlg('Формирование файла завершено', mtInformation, [mbOK], 0);
end;

procedure TfrmMain.DeleteInvoiceBtnClick(Sender: TObject);
begin
   // из history и documents не удаляется.
  if Application.MessageBox('Вы действительно хотите удалить выбранные записи?',
    'Подтверждение удаления', MB_YESNO + MB_ICONQUESTION) = IDNO then
    Exit;

  with DM do
  begin
    invoicesSelQ.DisableControls;
    invoicesSelQ.First;

    while not invoicesSelQ.Eof do
    begin
      if invoicesSelQpick.AsBoolean then
        DeleteInvoice(invoicesSelQidSender.AsInteger, invoicesSelQcompilingYear.AsInteger,
          invoicesSelQnumber.AsLargeInt);

      invoicesSelQ.Next;
    end;

    invoicesSelQ.EnableControls;
  end;

  RefreshInvoicesBtn.Click;
end;

procedure TfrmMain.MenuUpdateStatXMLBtnClick(Sender: TObject);
var
  IListSenders, IListInvoices: IXMLNodeList;
  INodeSenders, INodeInvoices: IXMLNode;
  i, j: Integer;
begin
  if not XMLOpenDialog.Execute then
  begin
    MessageDlg('Выбор файла отменён', mtWarning, [mbOK], 0);
    Exit;
  end;

  with DM do
  begin
    StatusXMLDoc.XML.Clear;
    StatusXMLDoc.FileName := XMLOpenDialog.FileName;
    StatusXMLDoc.Active := True;

    with StatusXMLDoc.DocumentElement do
    begin
      IListSenders := ChildNodes['invoices'].ChildNodes;

      for i := 0 to IListSenders.Count - 1 do
      begin
        MainProgressBar.Position := 100 * (i + 1) div IListSenders.Count;

        if IListSenders[i].NodeName = 'sender' then
        begin
          INodeSenders := IListSenders[i];

          SelectXMLSender.Close;
          SelectXMLSender.Parameters.ParamByName('UNN').Value := INodeSenders.Attributes['senderunn'];

          if INodeSenders.Attributes['senderdopunn'] = '' then
            SelectXMLSender.Parameters.ParamByName('dopUNN').Value := 1
          else
            SelectXMLSender.Parameters.ParamByName('dopUNN').Value := INodeSenders.Attributes['senderdopunn'];

          SelectXMLSender.Open;

          if SelectXMLSender.IsEmpty then
            Continue;

          IListInvoices := INodeSenders.ChildNodes;
          for j := 0 to IListInvoices.Count - 1 do
            if IListInvoices[j].NodeName = 'invoice' then
            begin
              INodeInvoices := IListInvoices[j];

              with UpdateInvoiceStatus do
              begin
                if INodeInvoices.Attributes['invstatus'] = '' then
                begin
                  Parameters.ParamByName('invstat').Value := null;
                  Parameters.ParamByName('invstatdate').Value := null;
                end
                else
                begin
                  Parameters.ParamByName('invstat').Value := INodeInvoices.Attributes['invstatus'];
                  Parameters.ParamByName('invstatdate').Value := INodeInvoices.Attributes['statusdate'];
                end;

                Parameters.ParamByName('invsend').Value := SelectXMLSenderidPredpr.AsInteger;
                Parameters.ParamByName('invyear').Value := INodeInvoices.Attributes['compilingYear'];
                Parameters.ParamByName('invnumb').Value := StrToInt64(VarToStr(INodeInvoices.Attributes
                  ['number']));
                ExecSQL;
              end;
            end;
        end;
      end;
    end;
  end;

  MessageDlg('Обновление статусов завершено', mtInformation, [mbOK], 0);
end;

procedure TfrmMain.MainPageControlChanging(Sender: TObject; var AllowChange: Boolean);
begin
  prevPageIndex := MainPageControl.ActivePageIndex;
end;

procedure TfrmMain.FindFormBtnClick(Sender: TObject);
begin
  frmSearch := TfrmSearch.Create(Self, RecipientCB);
  frmSearch.ShowModal;
  frmSearch.Free;
end;

procedure TfrmMain.AllStatBtnClick(Sender: TObject);
begin
  CreateXMLStatusFile(True);
end;

procedure TfrmMain.NotAllStatBtnClick(Sender: TObject);
begin
  CreateXMLStatusFile(False);
end;

end.

