{*********************************************************}
{                                                         }
{                     MightyQuery                         }
{            SQL Database Management Software             }
{                                                         }
{   Original developer: Zlatko Matić, 2009-2015           }
{             e-mail:                                     }
{                    matic.zlatko@gmail.com               }
{                    zmatasoft@gmail.com                  }
{                    zlatko.matic@matasoft.hr             }
{  Milkovićeva 6, Mala Ostrna, 10370 Dugo Selo,  Croatia. }
{                                                         }
{*********************************************************}
{
    This file is copyright (c) 2011 by Zlatko Matić

    This source code is distributed under
    the Library GNU General Public License (see the file
    COPYING) with the following modification:

    As a special exception, the copyright holders of this library give you
    permission to link this library with independent modules to produce an
    executable, regardless of the license terms of these independent modules,
    and to copy and distribute the resulting executable under terms of your choice,
    provided that you also meet, for each linked independent module, the terms
    and conditions of the license of that module. An independent module is a module
    which is not derived from or based on this library. If you modify this
    library, you may extend this exception to your version of the library, but you are
    not obligated to do so. If you do not wish to do so, delete this exception
    statement from your version.

    If you didn't receive a copy of the file COPYING, contact:
          Free Software Foundation
          675 Mass Ave
          Cambridge, MA  02139
          USA

    Modifications are made by Zlatko Matić and contributors for purpose of greater glory of Lazarus/FreePascal/CodeTyphon!

 **********************************************************************}
{-----------------------------------------------------------------------------

The Initial Developer of the Original Code is Zlatko Matić
(matalab@gmail.com, matic.zlatko@gmail.com, zlatko.matic@matasoft.hr).

Contributor(s):

Last Modified: 21.02.2023

Known Issues:

-Extremely slow query execution when more than one table joined in query when there is
additional where clause in query. It can be overcomed with "ASSIGN TO variable" non-standard expression
-->first execute query on a table with where clasue, assign resultset to a variable and then use
the variable in second query (instead of the table).
History (Change log):

version 1.0.4, date 04.06.2011:
        MightyQuery released as free software
version 1.0.5, date 25.07.2011 :
        - SQL code colouring and highlighting (based on TSynMemo and TSynSQLSyn).
        - Auto completion added for SQL code.
version 1.0.6, date 26.07.2011:
        - Code rewritten so that it is based on zmsql package. For now on,
        MightyQuery will be based on zmsql package and two projects are going to
        be synchronized.
version 1.0.7, date 04.08.2011:
        - Bugfixes release. Compiled with zmsql 0.1.3
version 1.0.8, date 09.08.2011:
        - Bugfixes release. A bug that disabled import from external database sources is solved.
        Compiled with zmsql 0.1.5
version 1.0.9, date 29.02.2012:
        - Compiled with zmsql 0.1.12 and CodeTyphon 2.50
        - Due to bug in current TDBF version, DBF packing on closing does not work...
version 1.1.0, date 06.05.2015:
        - Compiled with zmsql 0.1.19 and CodeTyphon 5.30
        - Visual query builder integrated.
        - MightyQuery can now open and edit spreadsheets (fpspreadsheet integrated).
version 1.2.0, date 14.02.2023:
        - Refactoring with CodeTyphon ver. 8.00
version 1.2.1, date 15.02.2023:
        - bugfixes related to spreadsheet exporting to csv files
veraion 1.2.2, date 21.02.2023
        - refactoring with heavily modified pl_ZMSQL package, version 7.6.2
-----------------------------------------------------------------------------}

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, LResources, Forms, Controls, Graphics, Dialogs,
  dbf, db, csvdataset, csvdocument,
  LConvEncoding, lazutf8, StrUtils,
  {
   dbf_common,
  }
  Unit5, ComCtrls, PairSplitter, ExtCtrls, DBGrids, DbCtrls, StdCtrls,
  EditBtn, ZMConnection, ZMQueryDataSet, ZMQueryBuilder, vsb_Zmsql, Grids,
  fpcsvexport, sqldb, ActnList, XMLPropStorage, Spin, Buttons, SynEdit, Types,
  lazfileutils, SynHighlighterSQL, SynMemo, SynCompletion, fpspreadsheetctrls,
  fpspreadsheetgrid, fpsexport, fpsDataset, fpsReaderWriter, fpstypes,
  fpspreadsheet, fpsAllFormats, ZConnection, ZDataset,  TypInfo,
  RxDBGrid, RxDBGridFooterTools, rxdbverticalgrid,
  RxDBGridExportSpreadSheet, RxSortZeos, RxSortSqlDB, RxSortFBDataSet;

type

  { TFormMain }

  TFormMain = class(TForm)
    BtnOpen: TButton;
    BtnSave: TButton;
    BtnNextSheet: TButton;
    BtnPrevSheet: TButton;
    ButtonApplyFieldsRenaming: TButton;
    ButtonCombineTablesToTemplate: TButton;
    ButtonCombineTables: TButton;
    ButtonApplySelectedFieldDelimiter: TButton;
    ButtonActivateCSVTablePreview1: TButton;
    ButtonCurrentSheetToCSV: TButton;
    ButtonAllSpreadsheetsToCSVFiles: TButton;
    ButtonCloseSpreadsheet: TButton;
    ButtonDeleteAllRowsCSVTableData: TButton;
    ButtonDetermineCurrentFieldDelimiter: TButton;
    ButtonSpreadsheetToCSV: TButton;
    ButtonReformatTableToZMSQLStandard: TButton;
    ButtonReformatAllTablesToZMSQLStandard: TButton;
    ButtonRegisterAllTables: TButton;
    ButtonDeleteAllQueries: TButton;
    ButtonDeleteAllQueryParameters: TButton;
    ButtonPackAll: TButton;
    ButtonRegisterFolderPath: TButton;
    ButtonInitiateFreshTables: TButton;
    ButtonEmptyPackAllTables: TButton;
    ButtonDeleteAllProjects: TButton;
    ButtonSelectAllExternalTables: TButton;
    ButtonUnselectAllConnectors: TButton;
    ButtonSelectAllConnectors: TButton;
    ButtonDeleteAllExternalTables: TButton;
    ButtonActivateCSVTablePreview: TButton;
    ButtonRunParameterizedQuery: TButton;
    ButtonDeleteAllConnectors: TButton;
    ButtonDeleteAllFields: TButton;
    ButtonDeleteAllTables: TButton;
    ButtonPopulateSpreadsheetsList: TButton;
    ButtonDeactivateCSVTablePreview: TButton;
    ButtonExportCSVTableData: TButton;
    ButtonExportQueryResult: TButton;
    ButtonExportSQLTableData: TButton;
    ButtonFilterCSVTableData: TButton;
    ButtonFilterQueryResult: TButton;
    ButtonFilterSQLTableData: TButton;
    ButtonFindCSVTableData: TButton;
    ButtonFindQueryResult: TButton;
    ButtonFindUnloadQueryResult: TButton;
    ButtonFindSQLTableData: TButton;
    ButtonGetFieldsSchema: TButton;
    ButtonGetSQLTableFieldsSchema: TButton;
    ButtonOpenSpreadsheet: TButton;
    ButtonRunQueryBuilder: TButton;
    ButtonSaveQueryResult: TButton;
    ButtonRegisterAllTablesAndFields: TButton;
    ButtonSetFieldsSchema: TButton;
    ButtonSQLTablesSchema: TButton;
    ButtonTestConnection: TButton;
    ButtonImport: TButton;
    ButtonActivateSQLTablePreview: TButton;
    ButtonDeactivateSQLTablePreview: TButton;
    ButtonApplySqlFunctionToQueryText: TButton;
    ButtonApplySqlExpressionToQueryText: TButton;
    ButtonApplyFieldNameToQueryText: TButton;
    ButtonApplyTableNameToQueryText: TButton;
    ButtonLoadQueryFromFile: TButton;
    ButtonRunQuery: TButton;
    ButtonSaveQueryToFile: TButton;
    ButtonUnselectAllExternalTables: TButton;
    ButtonUpdateCSVTable: TButton;
    CbOverrideZoomFactor: TCheckBox;
    CbWriteZoomFactor: TCheckBox;
    ComboBoxFieldDelimiter: TComboBox;
    CSVExporterCSVTableData: TCSVExporter;
    CSVExporterQueryResult: TCSVExporter;
    CSVExporterExternalTable: TCSVExporter;
    DBGridCSVTableData: TRxDBGrid;
    DBGridFields: TRxDBGrid;
    DBGridAutoincs: TRxDBGrid;
    DBGridQueryResult: TRxDBGrid;
    DBGridSQLConnectors: TRxDBGrid;
    DBGridSQLTables: TRxDBGrid;
    DBGridSQLTableData: TRxDBGrid;
    DBNavigatorCSVTable: TDBNavigator;
    DBNavigatorFields: TDBNavigator;
    DBNavigatorAutoincs: TDBNavigator;
    DBNavigatorQueryResult: TDBNavigator;
    DBNavigatorSQLConnectors: TDBNavigator;
    DBNavigatorSQLTables: TDBNavigator;
    DBNavigatorSQLTable: TDBNavigator;
    edZoom: TSpinEdit;
    FileNameEditSaveQueryResult: TFileNameEdit;
    DBGridFieldsForQueries: TRxDBGrid;
    DBGridTablesForQueries: TRxDBGrid;
    DBGridQueriesParameters: TRxDBGrid;
    DBGridQueries: TRxDBGrid;
    DBNavigatorFieldsForQueries: TDBNavigator;
    DBNavigatorTablesForQueries: TDBNavigator;
    DBNavigatorQueriesParameters: TDBNavigator;
    DBNavigatorQueries: TDBNavigator;
    FileNameEditSaveQuerySQL: TFileNameEdit;
    FPSExport1: TFPSExport;
    Label1: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    Label12: TLabel;
    Label13: TLabel;
    Label14: TLabel;
    Label15: TLabel;
    Label16: TLabel;
    Label17: TLabel;
    Label18: TLabel;
    Label19: TLabel;
    Label2: TLabel;
    Label20: TLabel;
    Label21: TLabel;
    Label22: TLabel;
    Label23: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    ListBoxSpreadsheets: TListBox;
    ListBoxSQLTables: TListBox;
    ListBoxSQLTableFields: TListBox;
    ListBoxSqlFunctions: TListBox;
    ListBoxSqlExpressions: TListBox;
    Memo1: TMemo;
    Memo2: TMemo;
    Memo3: TMemo;
    Memo4: TMemo;
    OpenDialog: TOpenDialog;
    PageControlTables: TPageControl;
    PageControlQueries: TPageControl;
    PairSplitter16: TPairSplitter;
    PairSplitter17: TPairSplitter;
    PairSplitter18: TPairSplitter;
    PairSplitter2: TPairSplitter;
    PairSplitter3: TPairSplitter;
    PairSplitter4: TPairSplitter;
    PairSplitter5: TPairSplitter;
    PairSplitter6: TPairSplitter;
    PairSplitter7: TPairSplitter;
    PairSplitter8: TPairSplitter;
    PairSplitter9: TPairSplitter;
    PairSplitterSide10: TPairSplitterSide;
    PairSplitterSide11: TPairSplitterSide;
    PairSplitterSide12: TPairSplitterSide;
    PairSplitterSide13: TPairSplitterSide;
    PairSplitterSide14: TPairSplitterSide;
    PairSplitterSide15: TPairSplitterSide;
    PairSplitterSide16: TPairSplitterSide;
    PairSplitterSide17: TPairSplitterSide;
    PairSplitterSide18: TPairSplitterSide;
    PairSplitterSide19: TPairSplitterSide;
    PairSplitterSide3: TPairSplitterSide;
    PairSplitterSide31: TPairSplitterSide;
    PairSplitterSide32: TPairSplitterSide;
    PairSplitterSide33: TPairSplitterSide;
    PairSplitterSide34: TPairSplitterSide;
    PairSplitterSide35: TPairSplitterSide;
    PairSplitterSide36: TPairSplitterSide;
    PairSplitterSide4: TPairSplitterSide;
    PairSplitterSide5: TPairSplitterSide;
    PairSplitterSide6: TPairSplitterSide;
    PairSplitterSide7: TPairSplitterSide;
    PairSplitterSide8: TPairSplitterSide;
    PairSplitterSide9: TPairSplitterSide;
    Panel11: TPanel;
    Panel13: TPanel;
    Panel14: TPanel;
    Panel15: TPanel;
    Panel16: TPanel;
    Panel17: TPanel;
    Panel18: TPanel;
    Panel19: TPanel;
    Panel20: TPanel;
    Panel21: TPanel;
    Panel22: TPanel;
    Panel23: TPanel;
    Panel24: TPanel;
    Panel25: TPanel;
    Panel26: TPanel;
    Panel27: TPanel;
    Panel28: TPanel;
    Panel29: TPanel;
    Panel3: TPanel;
    Panel30: TPanel;
    Panel31: TPanel;
    Panel32: TPanel;
    Panel33: TPanel;
    Panel34: TPanel;
    Panel35: TPanel;
    Panel36: TPanel;
    Panel37: TPanel;
    Panel38: TPanel;
    Panel39: TPanel;
    Panel40: TPanel;
    Panel41: TPanel;
    Panel42: TPanel;
    Panel43: TPanel;
    Panel44: TPanel;
    Panel45: TPanel;
    Panel46: TPanel;
    Panel47: TPanel;
    Panel48: TPanel;
    Panel49: TPanel;
    Panel50: TPanel;
    Panel51: TPanel;
    Panel52: TPanel;
    Panel53: TPanel;
    Panel54: TPanel;
    Panel55: TPanel;
    Panel56: TPanel;
    Panel57: TPanel;
    Panel58: TPanel;
    Panel59: TPanel;
    Panel6: TPanel;
    Panel60: TPanel;
    Panel61: TPanel;
    Panel65: TPanel;
    Panel66: TPanel;
    Panel67: TPanel;
    Panel68: TPanel;
    Panel69: TPanel;
    Panel7: TPanel;
    Panel70: TPanel;
    Panel71: TPanel;
    Panel72: TPanel;
    Panel73: TPanel;
    Panel74: TPanel;
    Panel75: TPanel;
    Panel78: TPanel;
    Panel79: TPanel;
    Panel8: TPanel;
    Panel80: TPanel;
    Panel9: TPanel;
    DBGridTables: TRxDBGrid;
    DBGridProjects: TRxDBGrid;
    DBNavigatorTables: TDBNavigator;
    DBNavigatorProjects: TDBNavigator;
    DirectoryEditProjectFolder: TDirectoryEdit;
    FileNameEditTableFile: TFileNameEdit;
    PageControl1: TPageControl;
    Panel10: TPanel;
    Panel12: TPanel;
    RxDBGridExportSpreadSheet1: TRxDBGridExportSpreadSheet;
    RxDBGridExportSpreadSheetProjects: TRxDBGridExportSpreadSheet;
    RxDBGridExportSpreadSheetAutoinc: TRxDBGridExportSpreadSheet;
    RxDBGridExportSpreadSheetCSVTable: TRxDBGridExportSpreadSheet;
    RxDBGridExportSpreadSheetFields: TRxDBGridExportSpreadSheet;
    RxDBGridExportSpreadSheetFieldsForQueries: TRxDBGridExportSpreadSheet;
    RxDBGridExportSpreadSheetQueries: TRxDBGridExportSpreadSheet;
    RxDBGridExportSpreadSheetQueryParameters: TRxDBGridExportSpreadSheet;
    RxDBGridExportSpreadSheetQueryResult: TRxDBGridExportSpreadSheet;
    RxDBGridExportSpreadSheetSQLConnectors: TRxDBGridExportSpreadSheet;
    RxDBGridExportSpreadSheetSQLTableData: TRxDBGridExportSpreadSheet;
    RxDBGridExportSpreadSheetSQLTables: TRxDBGridExportSpreadSheet;
    RxDBGridExportSpreadSheetTables: TRxDBGridExportSpreadSheet;
    RxDBGridExportSpreadSheetTablesForQueries: TRxDBGridExportSpreadSheet;
    RxDBGridFooterTools1: TRxDBGridFooterTools;
    RxSortFBDataSet1: TRxSortFBDataSet;
    RxSortSqlDB1: TRxSortSqlDB;
    RxSortZeos1: TRxSortZeos;
    SaveDialog: TSaveDialog;
    sCellEdit1: TsCellEdit;
    sCellIndicator1: TsCellIndicator;
    SpeedButton1: TSpeedButton;
    SpeedButtonMoveFieldDown: TSpeedButton;
    SpeedButtonMoveFieldUp: TSpeedButton;
    sSpreadsheetInspector1: TsSpreadsheetInspector;
    sWorkbookSource1: TsWorkbookSource;
    sWorkbookTabControl1: TsWorkbookTabControl;
    sWorksheetDataset1: TsWorksheetDataset;
    sWorksheetGrid1: TsWorksheetGrid;
    SynEditSQLEditor: TSynMemo;
    SynSQLSyn1: TSynSQLSyn;
    PairSplitter1: TPairSplitter;
    PairSplitterSide1: TPairSplitterSide;
    PairSplitterSide2: TPairSplitterSide;
    Panel1: TPanel;
    Panel2: TPanel;
    Panel4: TPanel;
    Panel5: TPanel;
    Donate: TTabSheet;
    Contact: TTabSheet;
    TabSheetSpreadsheets: TTabSheet;
    TabSheetTableData: TTabSheet;
    TabSheetTableDesign: TTabSheet;
    TabSheetQueryDesign: TTabSheet;
    TabSheetQueryResult: TTabSheet;
    UsersManual: TTabSheet;
    About: TTabSheet;
    TabSheetExternalSources: TTabSheet;
    TabSheetProjects: TTabSheet;
    TabSheetTables: TTabSheet;
    TabSheetQueries: TTabSheet;
    VSBBuilderZmsql1: TVSBBuilderZmsql;
    VSBEngineZmsql1: TVSBEngineZmsql;
    XMLPropStorage1: TXMLPropStorage;
    ZMQueryBuilder1: TZMQueryBuilder;
    procedure BtnNextSheetClick(Sender: TObject);
    procedure BtnOpenClick(Sender: TObject);
    procedure BtnPrevSheetClick(Sender: TObject);
    procedure BtnSaveClick(Sender: TObject);
    procedure ButtonActivateCSVTablePreviewClick(Sender: TObject);
    procedure ButtonActivateSQLTablePreviewClick(Sender: TObject);
    procedure ButtonAllSpreadsheetsToCSVFilesClick(Sender: TObject);
    procedure ButtonApplyFieldsRenamingClick(Sender: TObject);
    procedure ButtonApplySelectedFieldDelimiterClick(Sender: TObject);
    procedure ButtonCloseSpreadsheetClick(Sender: TObject);
    procedure ButtonCombineTablesToTemplateClick(Sender: TObject);
    procedure ButtonCurrentSheetToCSVClick(Sender: TObject);
    procedure ButtonDeleteAllRowsCSVTableDataClick(Sender: TObject);
    procedure ButtonDetermineCurrentFieldDelimiterClick(Sender: TObject);
    procedure ButtonCombineTablesClick(Sender: TObject);
    procedure ButtonReformatAllTablesToZMSQLStandardClick(Sender: TObject);
    procedure ButtonReformatTableToZMSQLStandardClick(Sender: TObject);
    procedure ButtonRegisterFolderPathClick(Sender: TObject);
    procedure ButtonRunParameterizedQueryClick(Sender: TObject);
    procedure ButtonRegisterAllTablesAndFieldsClick(Sender: TObject);
    procedure ButtonDeactivateCSVTablePreviewClick(Sender: TObject);
    procedure ButtonDeactivateSQLTablePreviewClick(Sender: TObject);
    procedure ButtonDeleteAllConnectorsClick(Sender: TObject);
    procedure ButtonDeleteAllExternalTablesClick(Sender: TObject);
    procedure ButtonDeleteAllFieldsClick(Sender: TObject);
    procedure ButtonDeleteAllProjectsClick(Sender: TObject);
    procedure ButtonDeleteAllQueriesClick(Sender: TObject);
    procedure ButtonDeleteAllQueryParametersClick(Sender: TObject);
    procedure ButtonDeleteAllTablesClick(Sender: TObject);
    procedure ButtonEmptyConnectorsClick(Sender: TObject);
    procedure ButtonEmptyPackAllTablesClick(Sender: TObject);
    procedure ButtonExportCSVTableDataClick(Sender: TObject);
    procedure ButtonExportQueryResultClick(Sender: TObject);
    procedure ButtonExportSQLTableDataClick(Sender: TObject);
    procedure ButtonFilterTableClick(Sender: TObject);
    procedure ButtonFilterCSVTableDataClick(Sender: TObject);
    procedure ButtonFilterQueryResultClick(Sender: TObject);
    procedure ButtonFilterSQLTableDataClick(Sender: TObject);
    procedure ButtonFindCSVTableDataClick(Sender: TObject);
    procedure ButtonFindQueryResultClick(Sender: TObject);
    procedure ButtonFindSQLTableDataClick(Sender: TObject);
    procedure ButtonFindUnloadQueryResultClick(Sender: TObject);
    procedure ButtonGetSQLTableFieldsSchemaClick(Sender: TObject);
    procedure ButtonApplyFieldNameToQueryTextClick(Sender: TObject);
    procedure ButtonApplySqlExpressionToQueryTextClick(Sender: TObject);
    procedure ButtonApplySqlFunctionToQueryTextClick(Sender: TObject);
    procedure ButtonApplyTableNameToQueryTextClick(Sender: TObject);
    procedure ButtonGetFieldsSchemaClick(Sender: TObject);
    procedure ButtonImportClick(Sender: TObject);
    procedure ButtonInitiateFreshTablesClick(Sender: TObject);
    procedure ButtonLoadQueryFromFileClick(Sender: TObject);
    procedure ButtonOpenSpreadsheetClick(Sender: TObject);
    procedure ButtonPackAllClick(Sender: TObject);
    procedure ButtonPopulateSpreadsheetsListClick(Sender: TObject);
    procedure ButtonRegisterAllTablesClick(Sender: TObject);
    procedure ButtonRunQueryBuilderClick(Sender: TObject);
    procedure ButtonSaveQueryToFileClick(Sender: TObject);
    procedure ButtonSelectAllConnectorsClick(Sender: TObject);
    procedure ButtonSelectAllExternalTablesClick(Sender: TObject);
    procedure ButtonSpreadsheetToCSVClick(Sender: TObject);
    procedure ButtonSQLTablesSchemaClick(Sender: TObject);
    procedure ButtonRemoveFilterQueryResultClick(Sender: TObject);
    procedure ButtonRemoveFilterTableClick(Sender: TObject);
    procedure ButtonRunQueryClick(Sender: TObject);
    procedure ButtonSaveQueryResultClick(Sender: TObject);
    procedure ButtonSetFieldsSchemaClick(Sender: TObject);
    procedure ButtonTestConnectionClick(Sender: TObject);
    procedure ButtonUnselectAllConnectorsClick(Sender: TObject);
    procedure ButtonUnselectAllExternalTablesClick(Sender: TObject);
    procedure ButtonUpdateCSVTableClick(Sender: TObject);
    procedure DirectoryEditProjectFolderAcceptDirectory(Sender: TObject;
      var Value: String);
    procedure edZoomEditingDone(Sender: TObject);
    procedure FileNameEditTableFileAcceptFileName(Sender: TObject;
      var Value: String);
    procedure FileNameEditTableFileClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure RxDBGridExportSpreadSheetProjectsBeforeExecute(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure SpeedButtonMoveFieldDownClick(Sender: TObject);
    procedure SpeedButtonMoveFieldUpClick(Sender: TObject);
    procedure SynEditSQLEditorChange(Sender: TObject);
  private
    { private declarations }
    FWorkbook: TsWorkbook;
    FOpenFormats: TsSpreadFormatIDArray;
    FSaveFormats: TsSpreadFormatIDArray;
    procedure DoExecute(Sender: TObject);
    procedure DoSearchPosition(var APosition: integer);
    procedure RunQuery;
    function LoadFile(const FileName: TFileName): string;
    procedure SaveFile(const FileName: TFileName; const content: string);
    procedure LoadFile(const AFileName: String; AFormatID: TsSpreadFormatID);
    procedure SaveFile(const AFileName: String; AFormatID: TsSpreadFormatID);
  public
    { public declarations }
    //Global variables.
    vApplicationPath:String;
    AutocompleteList: TStrings;
    vEditedTable:TBookmark;
    SynCompletion1:TSynCompletion;
    //Global procedures and functions.
    function StringToCaseSelect(Selector : string;CaseList: array of string): Integer;
    procedure AdjusTRxDBGridColumnsToTitles(var pDBGrid:TRxDBGrid);
    procedure InsertColumnsToCSVTable;
    procedure DropColumnsFromCSVTable;
    procedure RearrangeColumnsInCSVTable;
    procedure CombineTables(pCombinedTableName: string; pAlphabetically: boolean=False);
    procedure CombineTablesToTemplate(pTemplateTableName: string);
    procedure RenameCSVColumns;
    procedure CreateCSVTable;
    procedure ActivateCSVTablePreview;
    procedure UpdateCSVTable;
    procedure OpenFormFilters(var pDataset: TZMQueryDataset);
    procedure OpenFormFind(var pDataset: TZMQueryDataset);
    procedure OpenFormExportToCSV(var pDataset:TZMQueryDataset);
    procedure SetZMSQLProject;
    procedure UpdateTablesList;
    procedure UpdateAutocompleteList;
    procedure GetSpreadsheetsNames(FileList: TStrings);
    procedure GetTablesNames;
    procedure RegisterAllTablesAndFields;
    procedure RegisterAllTables;
    procedure GetFieldsNames;
    procedure GetFieldsListFromCSVFile(pFileName:string; List: TStrings);
    function DetermineSeparator(AFileName: String; var HasFieldNames: Boolean): Char;
    procedure ReformatTableToZMSQLStandard;
    procedure ExportCurrentSheetToCSVFile;
    procedure ExportSpreadsheetToCSVFiles;
  end; 

var
  FormMain: TFormMain;


implementation

uses Unit2,Unit3,Unit4;

{ TFormMain }

procedure TFormMain.SaveFile(const FileName: TFileName;const content: string);
begin
  with TFileStream.Create(FileName, fmCreate) do
    try
      Write(Pointer(content)^, Length(content));
    finally
      Free;
    end;
end;

procedure TFormMain.LoadFile(const AFileName: String;
  AFormatID: TsSpreadFormatID);
var
  crs: TCursor;
  book: TsWorkbook;
  i: Integer;
  sheet: TsWorksheet;
begin
  crs := Screen.Cursor;
  try
    if CbOverrideZoomFactor.Checked then begin
      book := TsWorkbook.Create;
      try
        Screen.Cursor := crHourglass;
        // Read the file
        book.ReadFromFile(AFilename, AFormatID);
        // If you want to override the zoom factor of the file set it before
        // assigning the worksheet to the grid.
        for i:=0 to book.GetWorksheetCount-1 do
        begin
          sheet := book.GetWorksheetByIndex(i);
          sheet.ZoomFactor := edZoom.Value / 100;
        end;
//        book.GetFirstWorksheet.ZoomFactor := edZoom.Value / 100;
        // Load the worksheet into the grid.
        sWorksheetGrid1.LoadFromWorkbook(book, 0);
      except
        on E:Exception do begin
          MessageDlg(Format('File "%s" cannot be loaded.'#13 + E.Message, [AFilename]),
            mtError, [mbOK], 0);
          book.Free;
        end;
      end;
    end else begin
      sWorksheetGrid1.LoadSheetFromSpreadsheetFile(AFilename, 0);
      edZoom.Value := sWorksheetGrid1.ZoomFactor * 100;
    end;
  finally
    Screen.Cursor := crs;
  end;
end;

procedure TFormMain.SaveFile(const AFileName: String;
  AFormatID: TsSpreadFormatID);
begin
  Screen.Cursor := crHourglass;
  try
    sWorksheetGrid1.SaveToSpreadsheetFile(AFileName, AFormatID);
  finally
    Screen.Cursor := crDefault;
  end;
end;

function TFormMain.LoadFile(const FileName: TFileName): string;
begin
  with TStringList.Create do
    try
      LoadFromFile(FileName);
      Result := Text;
    finally
      Free;
    end;
end;

function TFormMain.StringToCaseSelect(Selector:string;CaseList:array of string):Integer;
{
Usage:

case StringToCaseSelect('Delphi',
      ['About','Borland','Delphi']) of
   0:ShowMessage('You''ve picked About') ;
   1:ShowMessage('You''ve picked Borland') ;
   2:ShowMessage('You''ve picked Delphi') ;
end;
}
var cnt: integer;
begin
   Result:=-1;
   for cnt:=0 to Length(CaseList)-1 do
begin
     if CompareText(Selector, CaseList[cnt]) = 0 then
     begin
       Result:=cnt;
       Break;
     end;
   end;
end;

procedure TFormMain.GetTablesNames;
var
  i: Integer;
  vTableList: TStringList;
begin
  try
    vTableList := TStringList.Create;
    Datamodule1.ZMConnection1.GetTableNames(vTableList);
    //Delete previous records
    with Datamodule1.Tables do begin
      FindFirst;
      while not EOF do begin
        Delete;
      end;
    end;
    for i:=0 to vTableList.Count-1 do begin
      with Datamodule1.Tables do begin
        Append;
        FieldByName('TableName').AsString:=vTableList.ValueFromIndex[i];
        Post;
      end;
    end;
    Datamodule1.Tables.Refresh;
    Datamodule1.Tables.FindFirst;
  finally
    vTableList.Free;
  end
end;

procedure TFormMain.RegisterAllTablesAndFields;
begin
  GetTablesNames;
  with Datamodule1 do begin
    Tables.FindFirst;
    while not Tables.EOF do begin
      GetFieldsNames;
      Tables.Next;
    end;
    Datamodule1.Tables.Refresh;
    Tables.FindFirst;
  end;
end;

procedure TFormMain.RegisterAllTables;
begin
  GetTablesNames;
  Datamodule1.Tables.Refresh;
end;

procedure TFormMain.AdjusTRxDBGridColumnsToTitles(var pDBGrid:TRxDBGrid);
var
  vTitleWidth:Integer;
  vTextWidth:Integer;
  I:Integer;
begin
  with pDBGrid do begin
    //Adjust columns to titles width.
    for I:=0 to Columns.Count-1 do begin
      vTitleWidth:=Canvas.TextWidth(Columns[i].Title.Caption);
      vTextWidth:=Columns[I].Width;
      if(vTitleWidth>vTextWidth) then
        begin
          Columns[I].Width:=vTitleWidth+10;
        end;
    end;
  end;
end;

procedure TFormMain.FormCreate(Sender: TObject);
var
  priorityFormats: Array[0..7] of TsSpreadFormatID;
begin
  //Extraxt executable path.
  vApplicationPath:=extractfiledir(application.exename);

  //Set temporary autocomplete list
  AutocompleteList := TStringList.Create;

  //Enforce creation of DataModule1
  Application.CreateForm(TDataModule1, DataModule1);

  //Set Autocompletion for SQLEditor
  SynCompletion1 := TSynCompletion.Create(self);
  SynCompletion1.Editor := SynEditSQLEditor;
  SynCompletion1.CaseSensitive := False;
  SynCompletion1.ShowSizeDrag := True;
  SynCompletion1.DoubleClickSelects := True;
  SynCompletion1.OnExecute := @DoExecute;
  SynCompletion1.OnSearchPosition := @DoSearchPosition;

  //Populate list of available protocols for ZConnection
  Datamodule1.ZConnection1.GetProtocolNames(DBGridSQLConnectors.Columns.ColumnByFieldname('PROTOCOL').PickList);

  //Settings related to spreadsheets
  priorityFormats[0] := ord(sfOOXML);
  priorityFormats[1] := ord(sfExcel8);
  priorityFormats[2] := ord(sfExcel5);
  priorityFormats[3] := ord(sfExcel2);
  priorityFormats[4] := ord(sfExcelXML);
  priorityFormats[5] := ord(sfOpenDocument);
  priorityFormats[6] := ord(sfCSV);
  priorityFormats[7] := ord(sfHTML);

  OpenDialog.Filter := GetFileFormatFilter('|', ';', faRead, priorityFormats, true, true);
    // true, true --> add "All spreadsheet formats" and "All Excel formats" at top
  SaveDialog.Filter := GetFileFormatFilter('|', ';', faWrite, priorityFormats);

  FOpenFormats := GetSpreadFormats(faRead, priorityFormats);
  FSaveFormats := GetSpreadFormats(faWrite, priorityFormats);

end;

procedure TFormMain.FormDestroy(Sender: TObject);
begin
  AutocompleteList.Free;
  //Free global bookmark
  Datamodule1.Tables.FreeBookmark(vEditedTable);
end;

procedure TFormMain.RxDBGridExportSpreadSheetProjectsBeforeExecute(
  Sender: TObject);
begin
  TRxDBGridExportSpreadSheet(Sender).FileName:=DataModule1.ZMConnection1.DatabasePath + TRxDBGridExportSpreadSheet(Sender).FileName;
end;

procedure TFormMain.SpeedButton1Click(Sender: TObject);
begin
  sWorksheetGrid1.ZoomFactor := edZoom.Value / 100;
end;

procedure TFormMain.SpeedButtonMoveFieldDownClick(Sender: TObject);
var
  vField1, vField2, vFieldComment1, vFieldComment2: string;
  A,B : TBookmark;
begin
  with DataModule1.Fields do begin
    try
      DisableControls;
      A:=GetBookmark;
      vField1:=FieldByName('FIELDNAME').AsString;
      vFieldComment1:=FieldByName('FIELDCOMMENT').AsString;
      Next;
      B:=GetBookmark;
      vField2:=FieldByName('FIELDNAME').AsString;
      vFieldComment2:=FieldByName('FIELDCOMMENT').AsString;
      Edit;
      FieldByName('FIELDNAME').AsString:=vField1;
      FieldByName('FIELDCOMMENT').AsString:=vFieldComment1;
      Post;
      GotoBookmark(A);
      Edit;
      FieldByName('FIELDNAME').AsString:=vField2;
      FieldByName('FIELDCOMMENT').AsString:=vFieldComment2;
      Post;
      GotoBookmark(B);
    finally
      FreeBookMark(A);
      FreeBookmark(B);
      EnableControls;
    end;
  end;
end;

procedure TFormMain.SpeedButtonMoveFieldUpClick(Sender: TObject);
var
  vField1, vField2, vFieldComment1, vFieldComment2: string;
  A, B: TBookmark;
begin
  with DataModule1.Fields do begin
    try
      DisableControls;
      A:=GetBookmark;
      vField1:=FieldByName('FIELDNAME').AsString;
      vFieldComment1:=FieldByName('FIELDCOMMENT').AsString;
      Prior;
      B:=GetBookmark;
      vField2:=FieldByName('FIELDNAME').AsString;
      vFieldComment2:=FieldByName('FIELDCOMMENT').AsString;
      Edit;
      FieldByName('FIELDNAME').AsString:=vField1;
      FieldByName('FIELDCOMMENT').AsString:=vFieldComment1;
      Post;
      GotoBookmark(A);
      Edit;
      FieldByName('FIELDNAME').AsString:=vField2;
      FieldByName('FIELDCOMMENT').AsString:=vFieldComment2;
      Post;
      GotoBookmark(B);
    finally
      FreeBookMark(A);
      FreeBookmark(B);
      EnableControls;
    end;
  end;
end;

procedure TFormMain.SynEditSQLEditorChange(Sender: TObject);
begin
  with Datamodule1 do begin
    if Queries.Active=True then Queries.Edit;
  end;
end;

procedure TFormMain.ActivateCSVTablePreview;
var
  vTableFileName:String;
  vHasFieldNames:Boolean;
  vDelimiter: Char;
  vZMSQLDelimiter: TFieldDelimiter;
  i: integer;
begin
  with Datamodule1 do begin
    try
      ShowMessage('MightyQuery will try to load data now. Please, be patient.');

      DatasourceCSVTableData.Enabled:=False;
      DatasourceCSVTableData.DataSet:=nil;

      vTableFileName:=Projects.FieldByName('PROJECTPATH').AsString + Tables.FieldByName('TableName').AsString + '.csv';
      vDelimiter:=DetermineSeparator(vTableFileName, vHasFieldNames);
      if (vHasFieldNames=False) then begin
        ShowMessage('One or more columns have wrong incorrect field names!' + LineEnding
        + 'Column names must not contain spaces, neither be enquoted. Please correct the column names, i.e. field names!');
      end;

      {$IFDEF EnforceZMSQL}
      try
        //Option A) Load CSV table with ZMQueryDataSet
        ZMQueryDatasetCSVTableData.DisableControls;
        ZMQueryDatasetCSVTableData.TableName:=Tables.FieldByName('TableName').AsString;

        Case vDelimiter of
          ';':  vZMSQLDelimiter:=fdSemicolon;
          #9 :  vZMSQLDelimiter:=fdTab;
          ',':  vZMSQLDelimiter:=fdComma;
          '|':  vZMSQLDelimiter:=fdBar;
          ':':  vZMSQLDelimiter:=fdColon;
          '-':  vZMSQLDelimiter:=fdDash;
          '/':  vZMSQLDelimiter:=fdSlash;
          '\':  vZMSQLDelimiter:=fdBackSlash;
        else
         vZMSQLDelimiter:=fdSemicolon;
        end;

        ZMQueryDatasetCSVTableData.FieldDelimiter:=vZMSQLDelimiter;
        DatasourceCSVTableData.DataSet:=ZMQueryDatasetCSVTableData;

        ZMQueryDatasetCSVTableData.Active:=False;
        ZMQueryDatasetCSVTableData.FieldDefs.Clear;
        ZMQueryDatasetCSVTableData.Fields.Clear;

        ZMQueryDatasetCSVTableData.LoadFromTable;
        ZMQueryDatasetCSVTableData.EnableControls;
      except
        on E:Exception do begin
          ShowMessage('Error while trying to open table '+Tables.FieldByName('TABLENAME').AsString+'. ' + LineEnding + E.Message);
        end;
      end;

      {$ELSE}

      //Option B) Load CSV table with TCSVDataSet
      try
        CSVTableData.Active:=False;
        CSVTableData.FieldDefs.Clear;
        CSVTableData.Fields.Clear;

        CSVTableData.IndexDefs.Update;

        CSVTableData.IndexDefs.Clear;
        CSVTableData.ClearIndexes;

        //Delete existing indexes
        for i := 0 to Pred(CSVTableData.IndexDefs.Count) do begin
            CSVTableData.IndexDefs.Delete(i);
        end;

        CSVTableData.IndexFieldNames:='';
        CSVTableData.IndexName:='';

        CSVTableData.IndexDefs.Update;

        DatasourceCSVTableData.DataSet:=CSVTableData;

        CSVTableData.CSVOptions.FirstLineAsFieldNames:=vHasFieldNames;
        CSVTableData.CSVOptions.Delimiter:=vDelimiter;

        CSVTableData.LoadFromCSVFile(vTableFileName);

        CSVTableData.EnableControls;
      except
         on E:Exception do begin
          ShowMessage('Error while trying to open table '+Tables.FieldByName('TABLENAME').AsString+'. ' + LineEnding + E.Message);
        end;
      end;
      {$ENDIF}

      DatasourceCSVTableData.Enabled:=True;

      //Autofill columns then let columns be adjustable.
      DBGridCSVTableData.AutoFillColumns:=True;
      DBGridCSVTableData.AutoFillColumns:=False;

      //Adjust columns to titles width.
      //AdjusTRxDBGridColumnsToTitles(DBGridCSVTableData);
      //DBGridCSVTableData.AutoAdjustColumns;
      DBGridCSVTableData.OptimizeColumnsWidthAll;
    finally
      DatasourceCSVTableData.Enabled:=True;
      ShowMessage('Dataset is loaded!');
    end;
  end;
end;

procedure TFormMain.UpdateCSVTable;
var
  vFileName: String;
begin
  with Datamodule1 do begin
    {$IFDEF EnforceZMSQL}
    try
      //Goto the CSV Table that has to be edited.
      Tables.GotoBookmark(vEditedTable);
      ZMQueryDatasetCSVTableData.DisableControls;
      //Get bookmark
      vBookmark:=ZMQueryDatasetCSVTableData.GetBookmark;
      if ((Tables.Active=True)
          and (Tables.FieldByName('TableName').IsNull=False)
          and (Tables.FieldByName('TableName').AsString<>'')
          and (Tables.IsEmpty=False)) then begin
        //Synchronize CSVTable with BufDataset.
       try
         CSVExporterCSVTableData.Dataset:=ZMQueryDatasetCSVTableData;
         CSVExporterCSVTableData.FileName:=ZMConnection1.DatabasePath+Tables.FieldByName('TABLENAME').AsString+'.csv';
         CSVExporterCSVTableData.Execute;
         ShowMessage('Table '+Tables.FieldByName('TABLENAME').AsString+' updated and saved to file: '+CSVExporterCSVTableData.FileName);
       except
         on E:Exception do begin
           ShowMessage('Error while trying to save updated table '+Tables.FieldByName('TABLENAME').AsString+'. ' + LineEnding + E.Message);
         end;
       end;
      end;
    finally
      ZMQueryDatasetCSVTableData.GotoBookmark(vBookmark);
      ZMQueryDatasetCSVTableData.FreeBookmark(vBookmark);
      ZMQueryDatasetCSVTableData.EnableControls;
    end;
    {$ELSE}
    try
      if ((Tables.Active=True)
          and (Tables.FieldByName('TableName').IsNull=False)
          and (Tables.FieldByName('TableName').AsString<>'')
          and (Tables.IsEmpty=False)) then begin
           try
             CSVTableData.Active:=True;
             vFileName:= DataModule1.Projects.FieldByName('PROJECTPATH').AsString + DataModule1.Tables.FieldByName('TABLENAME').AsString + '.csv';
             CSVTableData.SaveToCSVFile(vFileName);
             ShowMessage('Table '+Tables.FieldByName('TABLENAME').AsString+' updated and saved to file: '+ vFileName);
           except
             on E:Exception do begin
               ShowMessage('Error while trying to save updated table '+Tables.FieldByName('TABLENAME').AsString+'. ' + LineEnding + E.Message);
             end;
           end;
          end;
    except
      on E:Exception do begin
        ShowMessage('Error while trying to save updated table '+Tables.FieldByName('TABLENAME').AsString+'. ' + LineEnding + E.Message);
      end;
    end;
    {$ENDIF}
  end;
end;

procedure TFormMain.DoExecute(Sender: TObject);
  procedure Add(s: String);
  begin
    if pos(lowercase(SynCompletion1.CurrentString), lowercase(s)) = 1 then
      SynCompletion1.ItemList.Add(s);
  end;
var
  i:Integer;
begin
  SynCompletion1.ItemList.Clear;
  for i:=0 to AutocompleteList.Count-1 do begin
    Add(AutocompleteList.Strings[i]);
  end;
end;

procedure TFormMain.DoSearchPosition(var APosition: integer);
  procedure Add(s: String);
  begin
    if pos(lowercase(SynCompletion1.CurrentString), lowercase(s)) = 1 then
      SynCompletion1.ItemList.Add(s);
  end;
var
  i:Integer;
begin
  SynCompletion1.ItemList.Clear;
  for i:=0 to AutocompleteList.Count-1 do begin
    Add(AutocompleteList.Strings[i]);
  end;
  if SynCompletion1.ItemList.Count > 0 then
    APosition := 0
  else
    APosition := -1;
end;

procedure TFormMain.DirectoryEditProjectFolderAcceptDirectory(Sender: TObject;
  var Value: String);
var
   vFolderDelimiterPosition: Integer;
   vFolderDelimiter: string;
begin
  vFolderDelimiterPosition:=LastDelimiter('/\', Value);
  vFolderDelimiter:=UTF8Copy(Value, vFolderDelimiterPosition, 1);
  if MessageDlg('Do you want to append browsed folder as a new project folder?',
    mtConfirmation,[mbYes,mbNo],0)=mrYes then
    begin
      with Datamodule1 do begin
        Projects.Append;
        Projects.FieldByName('PROJECTPATH').AsString:=Value + vFolderDelimiter;
        Projects.Post;
        Projects.Refresh;
      end;
    end
    else begin
      if MessageDlg('Do you want to apply browsed folder as a project folder of the currently selected project?',
      mtConfirmation,[mbYes,mbNo],0)=mrYes then
        begin
          with Datamodule1 do begin
            Projects.Edit;
            Projects.FieldByName('PROJECTPATH').AsString:=Value + vFolderDelimiter;
            Projects.Post;
            Projects.Refresh;
          end;
        end
    end;
end;

procedure TFormMain.edZoomEditingDone(Sender: TObject);
begin
  sWorksheetGrid1.ZoomFactor := edZoom.Value / 100;
end;

procedure TFormMain.FileNameEditTableFileAcceptFileName(Sender: TObject;
  var Value: String);
var
  vTableName:String;
  vTableFileName:String;
  vDelimiterPosition:Integer;
  vExtensionDelimiterPosition:Integer;
begin
  with Datamodule1 do begin
    if MessageDlg('Do you want to append browsed file as a a new registered table of the current project?'
      ,mtConfirmation,[mbYes,mbNo],0)=mrYes then
        begin
          vDelimiterPosition:=LastDelimiter('/\',Value);
          vTableFileName:=Copy(Value,(vDelimiterPosition+1),(Length(Value)-vDelimiterPosition));
          vExtensionDelimiterPosition:=LastDelimiter('.',vTableFileName);
          vTableName:=Copy(vTableFileName,0,(vExtensionDelimiterPosition-1));
          try
            Tables.Append;
            Tables.FieldByName('TableName').AsString:=vTableName;
            Tables.Post;
          except
            ShowMessage('The '+vTableFileName+' file could not be registered as table: '
              +vTableName+' in the project: '+Projects.FieldByName('ProjectName').AsString);
          end;
          Tables.Refresh;
          //Refresh Schema information.
          ButtonGetFieldsSchemaClick(Self);
        end
        else begin
          if MessageDlg('Do you want to register selected file as the currently selected table of the current project?'
            ,mtConfirmation,[mbYes,mbNo],0)=mrYes then
              begin
                vDelimiterPosition:=LastDelimiter('/\',Value);
                vTableFileName:=Copy(Value,(vDelimiterPosition+1),(Length(Value)-vDelimiterPosition));
                vExtensionDelimiterPosition:=LastDelimiter('.',vTableFileName);
                vTableName:=Copy(vTableFileName,0,(vExtensionDelimiterPosition-1));
                try
                  Tables.Edit;
                  Tables.FieldByName('TableName').AsString:=vTableName;
                  Tables.Post;
                except
                  ShowMessage('The '+vTableFileName+' file could not be registered as table: '
                    +vTableName+' in the project: '+Projects.FieldByName('ProjectName').AsString);
                end;
                Tables.Refresh;
                //Refresh Schema information.
                ButtonGetFieldsSchemaClick(Self);
              end;
        end;
  end;
end;

procedure TFormMain.FileNameEditTableFileClick(Sender: TObject);
begin
  FileNameEditTableFile.InitialDir:=Datamodule1.Projects.FieldByName('ProjectPath').AsString;
end;

procedure TFormMain.FormClose(Sender: TObject; var CloseAction: TCloseAction);
begin
   DataModule1.PackDbfTables;
end;

procedure TFormMain.ButtonGetFieldsSchemaClick(Sender: TObject);
begin
  GetFieldsNames;
  ShowMessage('Fields (columns) registered!');
end;

procedure TFormMain.ButtonImportClick(Sender: TObject);
begin
  with Datamodule1 do begin
    try
      SQLConnectors.DisableControls;
      SQLTables.DisableControls;
      //Iterate through SQLConnectors
      SQLConnectors.First;
      while not SQLConnectors.EOF do begin
        if SQLConnectors.FieldByName('TOBEIMPORTED').AsBoolean=True then begin
          try
            //Establish connection
            with ZConnection1 do begin
              SQLConnectors.Edit;
              SQLConnectors.Post;
              //Set properties
              Protocol:=SQLConnectors.FieldByName('PROTOCOL').AsString;
              Database:=SQLConnectors.FieldByName('DATABASE').AsString;
              HostName:=SQLConnectors.FieldByName('HOSTNAME').AsString;
              Port:=SQLConnectors.FieldByName('PORT').AsInteger;
              User:=SQLConnectors.FieldByName('USER').AsString;
              Password:=SQLConnectors.FieldByName('PASSWORD').AsString;
              Catalog:=SQLConnectors.FieldByName('CATALOG').AsString;
              //Try to connect
              try
                Connected:=True;
                //Iterate through SQLTables
                SQLTables.First;
                while not SQLTables.EOF do begin
                  if SQLTables.FieldByName('TOBEIMPORTED').AsBoolean=True then begin
                    try
                      //Get data
                      try
                        ZTable1.Active:=False;
                        try
                          ZTable1.TableName:=SQLTables.FieldByName('SQLTABLENAME').AsString;
                          ZTable1.Active:=True;
                        except
                          ZTable1.TableName:=UTF8QuotedStr(SQLTables.FieldByName('SQLTABLENAME').AsString, '"');
                          ZTable1.Active:=True;
                        end;
                        ZTable1.First;
                        //Export data
                        CSVExporterExternalTable.Dataset:=ZTable1;
                        try
                          if ((SQLTables.FieldByName('IMPORTEDNAME').AsString<>'')
                              and (SQLTables.FieldByName('IMPORTEDNAME').IsNull=False)) then begin
                                 try
                                   CSVExporterExternalTable.FileName:=ZMConnection1.DatabasePath+SQLTables.FieldByName('IMPORTEDNAME').AsString+'.csv';
                                   CSVExporterExternalTable.Execute;
                                   ShowMessage('External table '+SQLTables.FieldByName('SQLTABLENAME').AsString
                                        +' succesfully imported to '+ZMConnection1.DatabasePath
                                        +SQLTables.FieldByName('IMPORTEDNAME').AsString+'.csv');
                                 except
                                   on E:Exception do begin
                                     ShowMessage('External table '+SQLTables.FieldByName('SQLTABLENAME').AsString+' could not be imported. '+E.Message);
                                   end;
                                 end;
                              end
                              else begin
                                 try
                                   CSVExporterExternalTable.FileName:=ZMConnection1.DatabasePath+SQLTables.FieldByName('SQLTABLENAME').AsString+'.csv';
                                   CSVExporterExternalTable.Execute;
                                   ShowMessage('External table '+SQLTables.FieldByName('SQLTABLENAME').AsString
                                        +' succesfully imported to '+ZMConnection1.DatabasePath
                                        +SQLTables.FieldByName('IMPORTEDNAME').AsString+'.csv');
                                 except
                                   on E:Exception do begin
                                     ShowMessage('External table '+SQLTables.FieldByName('SQLTABLENAME').AsString+' could not be imported. '+E.Message);
                                   end;
                                 end;
                              end;
                        finally
                        end;
                      except
                        on E:Exception do begin
                          ShowMessage('I could not activate table. '+E.Message);
                        end;
                      end;
                    finally
                      ZTable1.Close;
                    end;
                  end;
                  SQLTables.Next;
                end;
              except
                on E:Exception do begin
                  ShowMessage('I could not establish connection to the Connector '+SQLConnectors.FieldByName('SQLCONNECTORNAME').AsString+'. '+E.Message);
                end;
              end;
            end;
          finally
            ZConnection1.Connected:=False;
          end;
        end;
        SQLConnectors.Next;
      end;
      ShowMessage('External database(s) tables(s) imported!');
    finally
      SQLConnectors.EnableControls;
      SQLTables.EnableControls;
    end;
  end;
end;

procedure TFormMain.ButtonInitiateFreshTablesClick(Sender: TObject);
begin
  DataModule1.InitiateFreshDbfTables;
end;

procedure TFormMain.ButtonLoadQueryFromFileClick(Sender: TObject);
begin
  SynEditSQLEditor.Clear;
  SynEditSQLEditor.Text:=LoadFile(FileNameEditSaveQuerySQL.FileName);
end;

procedure TFormMain.ButtonOpenSpreadsheetClick(Sender: TObject);
var
  vSpreadsheetFileName:String;
  vSpreadsheetFileNameFull:String;
  vListBoxSelectedIndex:Integer;

begin
  try
    vListBoxSelectedIndex:=ListBoxSpreadsheets.ItemIndex;
    if vListBoxSelectedIndex>=0 then begin
      vSpreadsheetFileName:=ListBoxSpreadsheets.Items.Strings[vListBoxSelectedIndex];
      vSpreadsheetFileNameFull:=DataModule1.ZMConnection1.DatabasePath+vSpreadsheetFileName;
      sWorkBookSource1.LoadFromSpreadsheetFile(vSpreadsheetFileNameFull);
      //ShowMessage(vSpreadsheetFileNameFull);
      //sWorkBookSource1.FileName:=vSpreadsheetFileNameFull;
    end;
  except
    on E:Exception do begin
      ShowMessage('Error while trying to open the spreadsheet:'+vSpreadsheetFileNameFull+'. '+E.Message);
    end;
  end;
end;

procedure TFormMain.ButtonPackAllClick(Sender: TObject);
begin
  DataModule1.PackDbfTables;
end;

procedure TFormMain.ButtonPopulateSpreadsheetsListClick(Sender: TObject);
var
  vSpreadsheetsList: TStringList;
begin
  try
    //Delete previous records
    ListBoxSpreadsheets.Clear;
    //Populate listbox
    try
      vSpreadSheetsList:=TStringList.Create;
      GetSpreadsheetsNames(vSpreadSheetsList);
      ListBoxSpreadsheets.Items.Assign(vSpreadsheetsList);
    finally
      vSpreadSheetsList.Free;
    end;
  except
    on E:Exception do begin
      ShowMessage('Error while trying to populate listbox. '+E.Message);
    end;
  end
end;

procedure TFormMain.ButtonRegisterAllTablesClick(Sender: TObject);
var
  crs: TCursor;
begin
  if MessageDlg('This action will delete all registered tables and fields for the'
  +' currently selected project and automatically recreate list of registered tables only, from csv files in the project folder..'
  +LineEnding+'Are you sure you want to recreate the list of tables?',
  mtWarning, [mbOK, mbCancel],0) = mrOk then begin
    try
      Screen.Cursor := crHourglass;
      RegisterAllTables;
      ShowMessage('Registration finished!');
    finally
      Screen.Cursor := crs;
    end;
  end;
end;

procedure TFormMain.ButtonRunQueryBuilderClick(Sender: TObject);
begin


     ZMQueryBuilder1.ACtive:=True;

   {
    with DataModule1 do begin
      VSBEngineZmsql1.DatabaseName:=ZMConnection1.DatabasePath;
      VSBEngineZmsql1.ShowSystemTables := False;

      if VSBBuilderZmsql1.Execute then
      begin
        ZMQueryDatasetQueryResult.SQL.Text:=VSBBuilderZmsql1.SQL.Text;
        ZMQueryDatasetQueryResult.Active:=False;
        ZMQueryDatasetQueryResult.Active:=true;
      end;
    end;

   }
end;

procedure TFormMain.ButtonSaveQueryToFileClick(Sender: TObject);
begin
  try
    SaveFile(FileNameEditSaveQuerySQL.FileName,Datamodule1.Queries.FieldByName('QUERYTEXT').AsString);
    ShowMessage('Query SQL saved to file: '+FileNameEditSaveQuerySQL.FileName);
  except
    on E:Exception do begin
      ShowMessage('Error while trying to save query sql to file. '+E.Message);
    end;
  end;
end;

procedure TFormMain.ButtonSelectAllConnectorsClick(Sender: TObject);
begin
  with DataModule1.SQLConnectors do begin
    First;
    while not EOF do begin
      Edit;
      FieldByName('TOBEIMPORTED').AsBoolean:=True;
      Post;
      Next;
    end;
  end;
end;

procedure TFormMain.ButtonSelectAllExternalTablesClick(Sender: TObject);
begin
  with DataModule1.SQLTables do begin
    First;
    while not EOF do begin
      Edit;
      FieldByName('TOBEIMPORTED').AsBoolean:=True;
      Post;
      Next;
    end;
  end;
end;

procedure TFormMain.ButtonSpreadsheetToCSVClick(Sender: TObject);
var
   crs: TCursor;
begin
   try
      crs:=Screen.Cursor;
      Screen.Cursor:=crHourGlass;

      ExportSpreadsheetToCSVFiles;

      ShowMessage('Sheet(s) of the currently selected spreadsheet are exported as CSV files, into the project folder!'
                            + LineEnding + 'Check out the project directory and register new csv files into the project.');
   finally
     Screen.Cursor:=crs;
   end;
end;

procedure TFormMain.ExportCurrentSheetToCSVFile;
var
  vSpreadsheetFileName:String;
  vSpreadsheetFileNameFull:String;
  vSpreadsheetName: string;
  vListBoxSelectedIndex:Integer;
  vCSVFileName: string;
begin
  //Open current spreadsheet from list
  try
    vListBoxSelectedIndex:=ListBoxSpreadsheets.ItemIndex;
    if vListBoxSelectedIndex>=0 then begin
      vSpreadsheetFileName:=ListBoxSpreadsheets.Items.Strings[vListBoxSelectedIndex];
      vSpreadsheetFileNameFull:=DataModule1.ZMConnection1.DatabasePath + vSpreadsheetFileName;
      vSpreadsheetName:=ExtractFileNameWithoutExt(vSpreadsheetFileName);

      sWorkBookSource1.LoadFromSpreadsheetFile(vSpreadsheetFileNameFull);
      //ShowMessage(vSpreadsheetFileNameFull);
      //sWorkBookSource1.FileName:=vSpreadsheetFileNameFull;

      vCSVFileName:=vSpreadsheetName;
      vCSVFileName:=UTF8StringReplace(vCSVFileName, ' ', '_', [rfReplaceAll, rfIgnoreCase]);
      vCSVFileName:=DataModule1.ZMConnection1.DatabasePath + vCSVFileName + '.csv';

      CSVParams.Delimiter:=';';
      sWorkbookSource1.SaveToSpreadsheetFile(vCSVFileName, sfCSV);

       ShowMessage('Sheet:' +  sWorksheetGrid1.Workbook.ActiveWorksheet.Name + LineEnding
                     + ' of the spreadsheet ' + vSpreadsheetFileNameFull
                     + LineEnding + 'is exported to following csv file: '
                     + LineEnding + vCSVFileName);
    end;
  except
    on E:Exception do begin
      ShowMessage('Error while trying to export sheet:' +  sWorksheetGrid1.Workbook.ActiveWorksheet.Name
      + ' of the spreadsheet ' + vSpreadsheetFileNameFull +'. ' + LineEnding + E.Message);
    end;
  end;
end;

procedure TFormMain.ExportSpreadsheetToCSVFiles;
var
    i, n: Integer;
    vSpreadsheetFileName:String;
    vSpreadsheetFileNameFull:String;
    vSpreadsheetName: string;
    vListBoxSelectedIndex:Integer;
    vCSVFileName: string;
begin
    //Open current spreadsheet from list
    try
      vListBoxSelectedIndex:=ListBoxSpreadsheets.ItemIndex;
      if vListBoxSelectedIndex>=0 then begin
        vSpreadsheetFileName:=ListBoxSpreadsheets.Items.Strings[vListBoxSelectedIndex];
        vSpreadsheetFileNameFull:=DataModule1.ZMConnection1.DatabasePath + vSpreadsheetFileName;
        vSpreadsheetName:=ExtractFileNameWithoutExt(vSpreadsheetFileName);

        sWorkBookSource1.LoadFromSpreadsheetFile(vSpreadsheetFileNameFull);
        //ShowMessage(vSpreadsheetFileNameFull);
        //sWorkBookSource1.FileName:=vSpreadsheetFileNameFull;

      end;
    except
      on E:Exception do begin
        ShowMessage('Error while trying to open the spreadsheet:'+vSpreadsheetFileNameFull+'. '+E.Message);
      end;
    end;
    //Navigating through sheets
    n := sWorksheetGrid1.Workbook.GetWorksheetCount;
    i := 0;
    while (i <= n-1) do begin
      begin
        try
          sWorksheetGrid1.LoadFromWorkbook(sWorksheetGrid1.Workbook, i);

          vCSVFileName:=vSpreadsheetName + '_' + sWorksheetGrid1.Workbook.ActiveWorksheet.Name;
          vCSVFileName:=UTF8StringReplace(vCSVFileName, ' ', '_', [rfReplaceAll, rfIgnoreCase]);
          vCSVFileName:=DataModule1.ZMConnection1.DatabasePath + vCSVFileName + '.csv';

          CSVParams.Delimiter:=';';
          sWorksheetGrid1.Workbook.WriteToFile(vCSVFileName, sfCSV);


           ShowMessage('Sheet:' +  sWorksheetGrid1.Workbook.ActiveWorksheet.Name + LineEnding
                               + ' of the spreadsheet ' + vSpreadsheetFileNameFull
                               + LineEnding + 'is exported to following csv file: '
                               + LineEnding + vCSVFileName);


        except
          on E:Exception do begin
            ShowMessage('Error while trying to export sheet:' +  sWorksheetGrid1.Workbook.ActiveWorksheet.Name
            + ' of the spreadsheet ' + vSpreadsheetFileNameFull +'. ' + LineEnding + E.Message);
          end;
        end;
      end;
      inc(i);
    end;
end;


procedure TFormMain.ButtonSQLTablesSchemaClick(Sender: TObject);
var I:Integer;
begin
  with Datamodule1 do begin
    try
      //Establish connection
      with ZConnection1 do begin
        SQLConnectors.Edit;
        SQLConnectors.Post;
        //Set properties
        Protocol:=SQLConnectors.FieldByName('PROTOCOL').AsString;
        Database:=SQLConnectors.FieldByName('DATABASE').AsString;
        HostName:=SQLConnectors.FieldByName('HOSTNAME').AsString;
        Port:=SQLConnectors.FieldByName('PORT').AsInteger;
        User:=SQLConnectors.FieldByName('USER').AsString;
        Password:=SQLConnectors.FieldByName('PASSWORD').AsString;
        Catalog:=SQLConnectors.FieldByName('CATALOG').AsString;
        //Try to connect
        try
          Connected:=True;
          //Populate table SQLTables
          try
            //Delete SQLTables for current SQLConnector
            SQLTables.First;
            while not SQLTables.EOF do begin
              SQLTables.Delete;
            end;
            //Get information about tables in the database.
            GetTableNames('',ListBoxSQLTables.Items);
            //Iterate through ListBoxSQLTables and insert into SQLTables
            for I:=0 to ListBoxSQLTables.Items.Count-1 do begin
                With SQLTables do begin
                  Append;
                  FieldByName('SQLTABLENAME').AsString:=ListBoxSQLTables.Items[I];
                  FieldByName('IMPORTEDNAME').AsString:=UTF8StringReplace(ListBoxSQLTables.Items[I], ' ', '_',[rfReplaceAll, rfIgnoreCase]);
                  Post;
                end;
            end;
            SQLTables.Refresh;
          except
            on E:Exception do begin
              ShowMessage('I could not get tables schema. '+E.Message);
            end;
          end;
        except
          on E:Exception do begin
            ShowMessage('I could not establish connection. '+E.Message);
          end;
        end;
      end;
      ShowMessage('External database table schema registered!');
    finally
      ZConnection1.Connected:=False;
    end;
  end;
end;

procedure TFormMain.ButtonRemoveFilterQueryResultClick(Sender: TObject);
begin
  Datamodule1.DatasourceQueryResult.DataSet.Filtered:=False;
end;

procedure TFormMain.ButtonRemoveFilterTableClick(Sender: TObject);
begin
  Datamodule1.DatasourceCSVTableData.DataSet.Filtered:=False;
end;

procedure TFormMain.ButtonApplySqlExpressionToQueryTextClick(Sender: TObject);
var
  vSelectedItem:Integer;
  vCurrentLine, vCurrentPosition:Integer;
  vCurrentLineText:string;
begin
  vSelectedItem:=ListBoxSqlExpressions.ItemIndex;
  if vSelectedItem>=0 then
    with ListBoxSqlExpressions do begin
      vCurrentLine:=SynEditSQLEditor.CaretY;
      vCurrentPosition:=SynEditSQLEditor.CaretX;
      vCurrentLineText:=SynEditSQLEditor.Lines.Strings[vCurrentLine];
      SynEditSQLEditor.Lines.Strings[vCurrentLine]:=Copy(vCurrentLineText,0,vCurrentPosition)
        +' '+Items[vSelectedItem]
        +Copy(vCurrentLineText,vCurrentPosition+1,Length(vCurrentLineText)-vCurrentPosition)
        +' ';  //This inserts into the place where cursor is positioned.
    end
end;

procedure TFormMain.ButtonApplyFieldNameToQueryTextClick(Sender: TObject);
var
  vCurrentLine, vCurrentPosition:Integer;
  vCurrentLineText:string;
begin
  with DataModule1 do begin
    with Fields do begin
      vCurrentLine:=SynEditSQLEditor.CaretY;
      vCurrentPosition:=SynEditSQLEditor.CaretX;
      vCurrentLineText:=SynEditSQLEditor.Lines.Strings[vCurrentLine];
      SynEditSQLEditor.Lines.Strings[vCurrentLine]:=Copy(vCurrentLineText,0,vCurrentPosition)
        +' '+Tables.FieldByName('TableName').AsString+'.'+FieldByName('FieldName').AsString
        +Copy(vCurrentLineText,vCurrentPosition+1,Length(vCurrentLineText)-vCurrentPosition)
        +' ';  //This inserts into the place where cursor is positioned.
    end;
  end;
end;

procedure TFormMain.ButtonGetSQLTableFieldsSchemaClick(Sender: TObject);
var vBookmark:TBookmark;
begin
  with Datamodule1 do begin
    try
      //Get bookmark
      vBookmark:=SQLTables.GetBookmark;
      //Establish connection
      with ZConnection1 do begin
        SQLConnectors.Edit;
        SQLConnectors.Post;
        //Set properties
        Protocol:=SQLConnectors.FieldByName('PROTOCOL').AsString;
        Database:=SQLConnectors.FieldByName('DATABASE').AsString;
        HostName:=SQLConnectors.FieldByName('HOSTNAME').AsString;
        Port:=SQLConnectors.FieldByName('PORT').AsInteger;
        User:=SQLConnectors.FieldByName('USER').AsString;
        Password:=SQLConnectors.FieldByName('PASSWORD').AsString;
        Catalog:=SQLConnectors.FieldByName('CATALOG').AsString;
        //Try to connect
        try
          Connected:=True;
          //Goto bookmark
          SQLTables.GotoBookmark(vBookmark);
          //Get information about fields of the current SQLTable.
          GetColumnNames(SQLTables.FieldByName('SQLTABLENAME').AsString,'',ListBoxSQLTableFields.Items);
        except
          on E:Exception do begin
            ShowMessage('I could not establish connection. '+E.Message);
          end;
        end;
      end;
    finally
      SQLTables.FreeBookmark(vBookmark);
      ZConnection1.Connected:=False;
    end;
  end;
end;

procedure TFormMain.ButtonActivateSQLTablePreviewClick(Sender: TObject);
begin
  with DataModule1 do begin
    try
      ShowMessage('MightyQuery will try to load data now. Please, be patient.');
      //Establish connection
      with ZConnection1 do begin
        //Set properties
        Protocol:=SQLConnectors.FieldByName('PROTOCOL').AsString;
        Database:=SQLConnectors.FieldByName('DATABASE').AsString;
        HostName:=SQLConnectors.FieldByName('HOSTNAME').AsString;
        Port:=SQLConnectors.FieldByName('PORT').AsInteger;
        User:=SQLConnectors.FieldByName('USER').AsString;
        Password:=SQLConnectors.FieldByName('PASSWORD').AsString;
        Catalog:=SQLConnectors.FieldByName('CATALOG').AsString;
        //Try to connect
        try
          Connected:=True;
          //Show Data
          with ZTable1 do begin
            try
              Active:=False;
              try
                ZTable1.TableName:=SQLTables.FieldByName('SQLTABLENAME').AsString;
                Active:=True;
              except
                ZTable1.TableName:=UTF8QuotedStr(SQLTables.FieldByName('SQLTABLENAME').AsString, '"');
                Active:=True;
              end;
              First;
              ZMQueryDatasetSQLTableData.CopyFromADataset(ZTable1);
              Active:=False;
            except
              on E:Exception do begin
                ShowMessage('I could not activate table. '+E.Message);
              end;
            end;
          end;
        except
          on E:Exception do begin
            ShowMessage('I could not establish connection. '+E.Message);
          end;
        end;
      end;
      ShowMessage('External database table loaded!');
    finally
      ZConnection1.Connected:=False;
      ShowMessage('Finished!');
    end;
  end;
end;

procedure TFormMain.ButtonAllSpreadsheetsToCSVFilesClick(Sender: TObject);
var
   crs: TCursor;
   I: Integer;
begin
   try
      crs:=Screen.Cursor;
      Screen.Cursor:=crHourGlass;

      for I:=0 to ListBoxSpreadsheets.Items.Count-1 do begin
          ListBoxSpreadsheets.ItemIndex:=I;
          ExportCurrentSheetToCSVFile;
      end;

      ShowMessage('Sheet(s) of the call listed spreadsheeta are exported as corresponding CSV files, into the project folder!'
                            + LineEnding + 'Check out the project directory and register new csv files into the project.');
   finally
     Screen.Cursor:=crs;
   end;
end;

procedure TFormMain.ButtonApplyFieldsRenamingClick(Sender: TObject);
var
 vFileName:String;
 vFileExists: boolean;
 vPreviewDataSet: TDataset;
begin
  with Datamodule1 do begin
    {$IFDEF EnforceZMSQL}
    vPreviewDataSet:=ZMQueryDatasetCSVTableData;
    {$ELSE}
    vPreviewDataSet:=CSVTableData;
    {$ENDIF}
    try
      //Disable controls.
      Fields.DisableControls;
      vFileName:=ZMConnection1.DatabasePath+Tables.FieldByName('TableName').AsString+'.csv';
      try
        //First, check whether the table exists.
        vFileExists:=FileExistsUTF8(vFileName);
        if vFileExists then begin
          //The table does exist.
          //Call procedure for columns renaming
          RenameCSVColumns;
        end
        else begin
          //The table does not exist.
          CreateCSVTable;
        end;
      except
        on E:Exception do begin
          ShowMessage('Error in procedure for renaming columns. '+E.Message);
        end;
      end;
      ShowMessage('Fields (columns) renamed!');
    finally
      //Refresh CSVTable preview.
      if vPreviewDataSet.Active then ActivateCSVTablePreview;
      //Refresh Schema information.(This must be executed only when both insert and delete statements are finished.
      ButtonGetFieldsSchemaClick(Self);
      //Enable controls.
      Fields.EnableControls;
    end;
  end;
end;

procedure TFormMain.ButtonApplySelectedFieldDelimiterClick(Sender: TObject);
begin
  ShowMessage('Field delimiter will be set to: ' + ComboBoxFieldDelimiter.Text);
  case ComboBoxFieldDelimiter.Text of
    'Semicolon':  DataModule1.ZMQueryDatasetQueryResult.FieldDelimiter:=fdSemicolon;
    'Tab':        DataModule1.ZMQueryDatasetQueryResult.FieldDelimiter:=fdTab;
    'Comma':      DataModule1.ZMQueryDatasetQueryResult.FieldDelimiter:=fdComma;
    'Bar':        DataModule1.ZMQueryDatasetQueryResult.FieldDelimiter:=fdBar;
    'Colon':      DataModule1.ZMQueryDatasetQueryResult.FieldDelimiter:=fdColon;
    'Dash':       DataModule1.ZMQueryDatasetQueryResult.FieldDelimiter:=fdDash;
    'Slash':      DataModule1.ZMQueryDatasetQueryResult.FieldDelimiter:=fdSlash;
    'BackSlash':  DataModule1.ZMQueryDatasetQueryResult.FieldDelimiter:=fdBackSlash;
  end;
end;

procedure TFormMain.ButtonCloseSpreadsheetClick(Sender: TObject);
begin
  sWorksheetGrid1.Clear;
  sWorksheetGrid1.Refresh;
end;

procedure TFormMain.ButtonCombineTablesToTemplateClick(Sender: TObject);
var
  crs: TCursor;
  vTemplateTableName: string;
  vBookmarkTables: TBookmark;
  vBookmarkFields: TBookmark;
  vPreviewDataSet: TDataset;
begin
  {$IFDEF EnforceZMSQL}
  vPreviewDataSet:=DataModule1.ZMQueryDatasetCSVTableData;
  {$ELSE}
  vPreviewDataSet:=DataModule1.CSVTableData;
  {$ENDIF}
  if MessageDlg('This action will merge/append records from other tables into the selected table, which is considered to be the template.'
  +LineEnding + 'During this process, columns having exactly same name as columns in the template table, will be combined together,'
  +LineEnding + 'while rows will be merge/appended.'
  +LineEnding + 'Depending on number and complexity of tables, this might take a while to execute.'
  +LineEnding + 'Are you sure you want to merge/append other tables into the selected table?',
  mtWarning, [mbOK, mbCancel],0) = mrOk then begin
    try
      vBookMarkTables:=DataModule1.Tables.GetBookmark;
      vBookMarkFields:=DataModule1.Fields.GetBookmark;
      vTemplateTableName:=DataModule1.Tables.FieldByName('TABLENAME').AsString;
      Screen.Cursor := crHourglass;
      CombineTablesToTemplate(vTemplateTableName);
      {GetFieldsNames;}
      DataModule1.Tables.GotoBookmark(vBookmarkTables);
      DataModule1.Fields.GotoBookmark(vBookmarkFields);
      ShowMessage('Rows are merge/appended into the template table: ' +  vTemplateTableName + '!');
      //Refresh CSVTable preview.
      if vPreviewDataSet.Active then ActivateCSVTablePreview;
    finally
      DataModule1.Tables.FreeBookmark(vBookmarkTables);
      DataModule1.Fields.FreeBookmark(vBookmarkFields);
      Screen.Cursor := crs;
    end;
  end;
end;

procedure TFormMain.ButtonCurrentSheetToCSVClick(Sender: TObject);
var
  crs: TCursor;
begin
  try
     crs:=Screen.Cursor;
     Screen.Cursor:=crHourGlass;

     ExportCurrentSheetToCSVFile;

     ShowMessage('Sheet(s) of the currently selected spreadsheet are exported as CSV files, into the project folder!'
                           + LineEnding + 'Check out the project directory and register new csv files into the project.');
  finally
    Screen.Cursor:=crs;
  end;
end;

procedure TFormMain.ButtonDeleteAllRowsCSVTableDataClick(Sender: TObject);
begin
  if MessageDlg('This action will delete all rows in the underlying CSV file.'
  +LineEnding+'Are you sure that you want to delete all rows?', mtWarning, [mbOK, mbCancel],0) = mrOk then
    begin
      {$IFDEF EnforceZMSQL}
      with DataModule1.ZMQueryDatasetCSVTableData do begin
        First;
        while not EOF do begin
          Delete;
        end;
      end;
      {$ELSE}
      with DataModule1.CSVTableData do begin
        First;
        while not EOF do begin
          Delete;
        end;
      end;
      {$ENDIF}
      UpdateCSVTable;
    end;
end;

procedure TFormMain.ButtonDetermineCurrentFieldDelimiterClick(Sender: TObject);
var TI: PTypeInfo;
begin
  TI:=GetPropInfo(DataModule1.ZMQueryDatasetQueryResult, 'FieldDelimiter')^.PropType;
  ShowMessage('Field delimiter is currently set to: ' + GetEnumName(TI, Ord(DataModule1.ZMQueryDatasetQueryResult.FieldDelimiter)));
  case DataModule1.ZMQueryDatasetQueryResult.FieldDelimiter of
    fdSemicolon: ComboBoxFieldDelimiter.Text:='Semicolon';
    fdTab:       ComboBoxFieldDelimiter.Text:='Tab';
    fdComma:     ComboBoxFieldDelimiter.Text:='Comma';
    fdBar:       ComboBoxFieldDelimiter.Text:='Bar';
    fdColon:     ComboBoxFieldDelimiter.Text:='Colon';
    fdDash:      ComboBoxFieldDelimiter.Text:='Dash';
    fdSlash:     ComboBoxFieldDelimiter.Text:='Slash';
    fdBackSlash: ComboBoxFieldDelimiter.Text:='BackSlash';
  end;
end;

procedure TFormMain.ButtonCombineTablesClick(Sender: TObject);
var
  crs: TCursor;
  vCombinedTableName: string;
  vAlphabetically: boolean;
begin
  if MessageDlg('This action will create new table with combined fields (columns) from all tables registered in the project.'
  +LineEnding + 'During this process, columns having exactly same name will be combined together,'
  +LineEnding + 'while rows will be merge/appended.'
  +LineEnding + 'Depending on number and complexity of tables, this might take a while to execute.'
  +LineEnding + 'Are you sure you want to merge/append all tables in the project, into a new combined table?',
  mtWarning, [mbOK, mbCancel],0) = mrOk then begin
    try
      vCombinedTableName := InputBox('Combined table name', 'Please enter name for the new combined table:', 'combined_'+DataModule1.Projects.FieldByName('PROJECT_ID').AsString);
      if MessageDlg('Column names sorting', 'Do you wish to sort column names alphabetically?', mtConfirmation, [mbYes, mbNo],1) = mrYes then vAlphabetically:=True else vAlphabetically:=False;
      CombineTables(vCombinedTableName, vAlphabetically);
      Screen.Cursor := crHourglass;
      with DataModule1.Tables do begin
        Append;
        FieldByName('TABLENAME').AsString:=vCombinedTableName;
        Post;
      end;
      GetFieldsNames;
      ShowMessage('New combined table created and registered!');
    finally
      Screen.Cursor := crs;
    end;
  end;
end;

procedure TFormMain.ButtonReformatAllTablesToZMSQLStandardClick(Sender: TObject
  );
var
  crs: TCursor;
begin
  if MessageDlg('This action will inspect all tables and if a table does not comply to standard CSV format used by ZMSQL package, it will be reformatted.'
  +LineEnding + 'If there are many datasets or datasets are large, this procedure might take a while...'
  +LineEnding +'Are you sure you want to reformat all tables?',
  mtWarning, [mbOK, mbCancel],0) = mrOk then begin
    try
      crs:=Screen.Cursor;
      Screen.Cursor := crHourglass;
      DataModule1.ZMQueryDatasetCSVTableData.DisableControls;
      with Datamodule1 do begin
        Tables.First;
        while not Tables.EOF do begin
              ReformatTableToZMSQLStandard;
              Tables.Next;
        end;
      end;
    finally
      DataModule1.Tables.Refresh;
      DataModule1.ZMQueryDatasetCSVTableData.EnableControls;
      Screen.Cursor := crs;
      ShowMessage('Updating finished!');
    end;
  end;
end;

procedure TFormMain.ButtonReformatTableToZMSQLStandardClick(Sender: TObject);
var
  crs: TCursor;
begin
  if MessageDlg('This action will inspect currently selected table and if the table does not comply to standard CSV format used by ZMSQL package, it will be reformatted.'
  +LineEnding + 'If there are many datasets or datasets are large, this procedure might take a while...'
  +LineEnding +'Are you sure you want to reformat the table?',
  mtWarning, [mbOK, mbCancel],0) = mrOk then begin
    try
      crs:=Screen.Cursor;
      Screen.Cursor := crHourglass;
      DataModule1.ZMQueryDatasetCSVTableData.DisableControls;
      with Datamodule1 do begin
           ReformatTableToZMSQLStandard;
      end;
    finally
      DataModule1.Tables.Refresh;
      DataModule1.ZMQueryDatasetCSVTableData.EnableControls;
      Screen.Cursor := crs;
      ShowMessage('Updating finished!');
    end;
  end;
end;

procedure TFormMain.ReformatTableToZMSQLStandard;
var
  vTableFileName:String;
  vHasFieldNames:Boolean;
  vDelimiter: Char;
  vZMSQLDelimiter: TFieldDelimiter;
begin
    with Datamodule1 do begin

        DatasourceCSVTableData.Enabled:=False;
        DatasourceCSVTableData.DataSet:=nil;


        vTableFileName:=Projects.FieldByName('PROJECTPATH').AsString + Tables.FieldByName('TableName').AsString + '.csv';
        vDelimiter:=DetermineSeparator(vTableFileName, vHasFieldNames);
        if (vHasFieldNames=False) then begin
          ShowMessage('One or more columns have wrong incorrect field names!' + LineEnding
          + 'Column names must not contain spaces, neither be enquoted. Please correct the column names, i.e. field names!');
        end;

        {$IFDEF EnforceZMSQL}
        if ((Tables.Active=True)
            and (Tables.FieldByName('TableName').IsNull=False)
            and (Tables.FieldByName('TableName').AsString<>'')
            and (Tables.IsEmpty=False)) then begin

              Case vDelimiter of
                ';':  vZMSQLDelimiter:=fdSemicolon;
                #9 :  vZMSQLDelimiter:=fdTab;
                ',':  vZMSQLDelimiter:=fdComma;
                '|':  vZMSQLDelimiter:=fdBar;
                ':':  vZMSQLDelimiter:=fdColon;
                '-':  vZMSQLDelimiter:=fdDash;
                '/':  vZMSQLDelimiter:=fdSlash;
                '\':  vZMSQLDelimiter:=fdBackSlash;
              else
               vZMSQLDelimiter:=fdSemicolon;
              end;

              if  (vZMSQLDelimiter<>fdSemicolon) then begin
                //Synchronize CSVTable with BufDataset.
               try
                 ZMQueryDatasetCSVTableData.Active:=False;
                 ZMQueryDatasetCSVTableData.FieldDefs.Clear;
                 ZMQueryDatasetCSVTableData.Fields.Clear;
                 ZMQueryDatasetCSVTableData.FieldDelimiter:=vZMSQLDelimiter;
                 ZMQueryDatasetCSVTableData.TableName:=Tables.FieldByName('TableName').AsString;
                 ZMQueryDatasetCSVTableData.LoadFromTable;
                 DatasourceCSVTableData.DataSet:=ZMQueryDatasetCSVTableData;
                 DatasourceCSVTableData.Enabled:=True;
                 //Export to csv file
                 CSVExporterCSVTableData.Dataset:=ZMQueryDatasetCSVTableData;
                 CSVExporterCSVTableData.FileName:=ZMConnection1.DatabasePath+Tables.FieldByName('TABLENAME').AsString+'.csv';
                 CSVExporterCSVTableData.Execute;
                 ShowMessage('Table '+Tables.FieldByName('TABLENAME').AsString+' updated and saved to file: '+CSVExporterCSVTableData.FileName);
               except
                 on E:Exception do begin
                   ShowMessage('Error while trying to save updated table '+Tables.FieldByName('TABLENAME').AsString+'. ' + LineEnding + E.Message);
                 end;
               end;
              end
              else begin
                   ShowMessage('Skipping table '+Tables.FieldByName('TABLENAME').AsString +' because its is already in proper format.');
              end;
            end
            else begin
                 ShowMessage('Skipping table '+Tables.FieldByName('TABLENAME').AsString +' because its is already in proper format.');
            end;

        {$ELSE}
        if ((Tables.Active=True)
            and (Tables.FieldByName('TableName').IsNull=False)
            and (Tables.FieldByName('TableName').AsString<>'')
            and (Tables.IsEmpty=False)) then begin
          try
            if (vDelimiter <> ';') then begin
              vTableFileName:= DataModule1.Projects.FieldByName('PROJECTPATH').AsString + DataModule1.Tables.FieldByName('TABLENAME').AsString + '.csv';
              CSVTableData.Active:=True;
              CSVTableData.SaveToCSVFile(vTableFileName);
              ShowMessage('Table '+Tables.FieldByName('TABLENAME').AsString+' updated and saved to file: '+ vTableFileName);
            end
            else begin
                 ShowMessage('Skipping table '+Tables.FieldByName('TABLENAME').AsString +' because its is already in proper format.');
            end;
          except
            on E:Exception do begin
              ShowMessage('Error while trying to save updated table '+Tables.FieldByName('TABLENAME').AsString+'. ' + LineEnding + E.Message);
            end;
          end;
        end
        else begin
             ShowMessage('Skipping table '+Tables.FieldByName('TABLENAME').AsString +' because its is already in proper format.');
        end;
        {$ENDIF}

    end;

end;


procedure TFormMain.ButtonRegisterFolderPathClick(Sender: TObject);
var
   vFolderDelimiterPosition: Integer;
   vFolderDelimiter: string;
begin
  vFolderDelimiterPosition:=LastDelimiter('/\', DirectoryEditProjectFolder.Text);
  vFolderDelimiter:=UTF8Copy(DirectoryEditProjectFolder.Text, vFolderDelimiterPosition, 1);
  With DataModule1.Projects do begin
    Edit;
    FieldByName('PROJECTPATH').AsString:=DirectoryEditProjectFolder.Text + vFolderDelimiter;
    Post;
    Refresh;
  end;
end;

procedure TFormMain.ButtonActivateCSVTablePreviewClick(Sender: TObject);
var
  crs: TCursor;

begin
  try
    crs:=Screen.Cursor;
    Screen.Cursor:=crHourGlass;
    PageControlTables.ActivePage:=TabSheetTableData;
    ActivateCSVTablePreview;
  finally
    Screen.Cursor:=crs;
  end;
end;

procedure TFormMain.BtnOpenClick(Sender: TObject);
var
  fmt: TsSpreadFormatID;
begin
  //if OpenDialog.FileName='' then OpenDialog.InitialDir:=SetDirSeparators(pathMedia);

  if sWorkbookSource1 <> nil then
    OpenDialog.InitialDir := ExtractFileDir(sWorkbookSource1.FileName);
  if OpenDialog.Execute then begin
    // 3 because FilterIndex is 1-based and there are 2 add'l items at the top.
    if OpenDialog.FilterIndex < 3 then
      fmt := sfidUnknown
    else
      fmt := FOpenFormats[OpenDialog.FilterIndex - 3];
    LoadFile(OpenDialog.FileName, fmt);
  end;
end;

procedure TFormMain.BtnNextSheetClick(Sender: TObject);
var
  i, n: Integer;
begin
  n := sWorksheetGrid1.Workbook.GetWorksheetCount;
  i := 0;
  while (i < n-1) do begin
    if (sWorksheetGrid1.Workbook.GetWorksheetByIndex(i) = sWorksheetGrid1.Worksheet) then
    begin
      sWorksheetGrid1.LoadFromWorkbook(sWorksheetGrid1.Workbook, i+1);
      exit;
    end;
    inc(i);
  end;
end;

procedure TFormMain.BtnPrevSheetClick(Sender: TObject);
var
  i, n: Integer;
begin
  n := sWorksheetGrid1.Workbook.GetWorksheetCount;
  i := 1;
  while (i < n) do begin
    if (sWorksheetGrid1.Workbook.GetWorksheetByIndex(i) = sWorksheetGrid1.Worksheet) then
    begin
      sWorksheetGrid1.LoadFromWorkbook(sWorksheetGrid1.Workbook, i-1);
      exit;
    end;
    inc(i);
  end;
end;

procedure TFormMain.BtnSaveClick(Sender: TObject);
var
  fmt: TsSpreadFormatID;
  fmts: TsSpreadFormatIDArray;
  i: Integer;
begin
  { Set up the save dialog such that it shows the loaded file name and type }
  SaveDialog.InitialDir := ExtractFileDir(sWorkbookSource1.FileName);
  SaveDialog.FileName := ExtractFileName(sWorkbookSource1.FileName);
  fmts := GetSpreadFormatsFromFileName(faWrite, SaveDialog.FileName);
  for i:=0 to High(FSaveFormats) do
    if FSaveFormats[i] = fmts[0] then begin
      SaveDialog.FilterIndex := i + 1;
      break;
    end;
  if SaveDialog.Execute then begin
    fmt := FSaveFormats[SaveDialog.FilterIndex - 1];
    //SaveFile(SaveDialog.FileName, fmt);
    sWorkbookSource1.SaveToSpreadsheetFile(SaveDialog.FileName, fmt);
  end;
end;

procedure TFormMain.ButtonRunParameterizedQueryClick(Sender: TObject);
var
  I:Integer;
  vOriginalSQL:String;
  vPreparedSQL:String;
  vSQL:TStrings;
  crs: TCursor;
begin
  try
    crs:= Screen.Cursor;
    Screen.Cursor := crHourglass;
    with Datamodule1 do begin
      ZMQueryDatasetQueryResult.Close;
      ZMQueryDatasetQueryResult.ClearDataSet;
      //Get SQL string
      vSQL:=TStringList.Create;
      vSQL.Clear;
      vOriginalSQL:='';
      vPreparedSQL:='';
      for I:=0 to SynEditSQLEditor.Lines.Count-1 do begin
        vOriginalSQL:=SynEditSQLEditor.Lines[I];
        vPreparedSQL:=vOriginalSQL;
        //Prepare SQL
        QueriesParameters.First;
        while not QueriesParameters.EOF do begin
          vPreparedSQL:=AnsiReplaceText(vPreparedSQL,
          QueriesParameters.FieldByName('QUERYPARAMETERNAME').AsString,
          QueriesParameters.FieldByName('QUERYPARAMETERVALUE').AsString);
          QueriesParameters.Next;
        end;
        vSQL.Append(vPreparedSQL);
      end;
      //Set prepared SQL
      ZMQueryDatasetQueryResult.SQL.Clear;
      ZMQueryDatasetQueryResult.SQL:=vSQL;
      //Execute SQL.
      RunQuery;
    end;
  finally
    Screen.Cursor := crs;
  end;
end;

procedure TFormMain.ButtonRegisterAllTablesAndFieldsClick(Sender: TObject);
var
  crs: TCursor;
begin
  if MessageDlg('This action will delete all registered tables and fields for the'
  +' currently selected project and automatically recreate list of registered tables and fields, from csv files in the project folder..'
  +LineEnding+'Are you sure you want to recreate the list of tables and fields?',
  mtWarning, [mbOK, mbCancel],0) = mrOk then begin
    try
      Screen.Cursor := crHourglass;
      RegisterAllTablesAndFields;
      ShowMessage('Registration finished!');
    finally
      Screen.Cursor := crs;
    end;
  end;
end;

procedure TFormMain.ButtonDeactivateCSVTablePreviewClick(Sender: TObject);
begin
  with Datamodule1 do begin
    {$IFDEF EnforceZMSQL}
    ZMQueryDatasetCSVTableData.Close;
    //Free global bookmark
    Tables.FreeBookmark(vEditedTable);
    {$ELSE}
    CSVTableData.Close;
    {$ENDIF}
  end;
end;

procedure TFormMain.ButtonDeactivateSQLTablePreviewClick(Sender: TObject);
begin
  Datamodule1.ZMQueryDatasetSQLTableData.Close;
end;

procedure TFormMain.ButtonDeleteAllConnectorsClick(Sender: TObject);
begin
  if MessageDlg('This action will delete definition of all connectors for the currently selected project.'
  +LineEnding+'Are you sure that you want to delete all connectors definition?', mtWarning, [mbOK, mbCancel],0) = mrOk then
    begin
      with DataModule1.SQLConnectors do begin
        First;
        while not EOF do begin
          Delete;
        end;
      end;
    end;
end;

procedure TFormMain.ButtonDeleteAllExternalTablesClick(Sender: TObject);
begin
  if MessageDlg('This action will delete definition of all external tables for the currently selected connector.'
  +LineEnding+'Are you sure that you want to delete all external tables definition?', mtWarning, [mbOK, mbCancel],0) = mrOk then
    begin
      with DataModule1.SQLTables do begin
        First;
        while not EOF do begin
          Delete;
        end;
      end;
    end;
end;

procedure TFormMain.ButtonDeleteAllFieldsClick(Sender: TObject);
begin
  if MessageDlg('This action will delete definition of all fields for the currently selected dataset.'
  +LineEnding+'Are you sure that you want to delete all dataset fields definition?', mtWarning, [mbOK, mbCancel],0) = mrOk then
    begin
      with DataModule1.Fields do begin
        First;
        while not EOF do begin
          Delete;
        end;
      end;
    end;
end;

procedure TFormMain.ButtonDeleteAllProjectsClick(Sender: TObject);
begin
  if MessageDlg('This action will delete definition of all projects.'
  +LineEnding+'Are you sure that you want to delete all projects?', mtWarning, [mbOK, mbCancel],0) = mrOk then
    begin
      with DataModule1.Projects do begin
        First;
        while not EOF do begin
          Delete;
        end;
      end;
    end;
end;

procedure TFormMain.ButtonDeleteAllQueriesClick(Sender: TObject);
begin
  if MessageDlg('This action will delete definition of all queries for the currently selected project.'
  +LineEnding+'Are you sure that you want to delete all queries definition?', mtWarning, [mbOK, mbCancel],0) = mrOk then
    begin
      with DataModule1.Queries do begin
        First;
        while not EOF do begin
          Delete;
        end;
      end;
    end;
end;

procedure TFormMain.ButtonDeleteAllQueryParametersClick(Sender: TObject);
begin
  if MessageDlg('This action will delete definition of all query parameters for the currently selected query.'
  +LineEnding+'Are you sure that you want to delete all query parameters?', mtWarning, [mbOK, mbCancel],0) = mrOk then
    begin
      with DataModule1.QueriesParameters do begin
        First;
        while not EOF do begin
          Delete;
        end;
      end;
    end;
end;

procedure TFormMain.ButtonDeleteAllTablesClick(Sender: TObject);
begin
  if MessageDlg('This action will delete definition of all tables for the currently selected project.'
  +LineEnding+'Are you sure that you want to delete all tables definition?', mtWarning, [mbOK, mbCancel],0) = mrOk then
    begin
      with DataModule1.Tables do begin
        First;
        while not EOF do begin
          Delete;
        end;
      end;
    end;
end;

procedure TFormMain.ButtonEmptyConnectorsClick(Sender: TObject);
begin

end;

procedure TFormMain.ButtonEmptyPackAllTablesClick(Sender: TObject);
begin
  if MessageDlg('This action will delete definition of all projects and compact the database.'
  +LineEnding+'Are you sure that you want to delete all projects?', mtWarning, [mbOK, mbCancel],0) = mrOk then
    begin
         DataModule1.EmptyPackDbfTables;
    end;
end;

procedure TFormMain.ButtonExportCSVTableDataClick(Sender: TObject);
begin
  OpenFormExportToCSV(Datamodule1.ZMQueryDatasetCSVTableData);
end;

procedure TFormMain.ButtonExportQueryResultClick(Sender: TObject);
begin
  OpenFormExportToCSV(Datamodule1.ZMQueryDatasetQueryResult);
end;

procedure TFormMain.ButtonExportSQLTableDataClick(Sender: TObject);
begin
  OpenFormExportToCSV(Datamodule1.ZMQueryDatasetSQLTableData);
end;

procedure TFormMain.ButtonFilterCSVTableDataClick(Sender: TObject);
begin
  OpenFormFilters(Datamodule1.ZMQueryDatasetCSVTableData);
end;

procedure TFormMain.ButtonFilterSQLTableDataClick(Sender: TObject);
begin
  OpenFormFilters(Datamodule1.ZMQueryDatasetSQLTableData);
end;

procedure TFormMain.ButtonFindCSVTableDataClick(Sender: TObject);
begin
  OpenFormFind(Datamodule1.ZMQueryDatasetCSVTableData);
end;

procedure TFormMain.ButtonFindSQLTableDataClick(Sender: TObject);
begin
  OpenFormFind(Datamodule1.ZMQueryDatasetSQLTableData);
end;

procedure TFormMain.ButtonFindUnloadQueryResultClick(Sender: TObject);
begin
  Datamodule1.ZMQueryDatasetQueryResult.Close;
end;

procedure TFormMain.ButtonApplySqlFunctionToQueryTextClick(Sender: TObject);
var
  vSelectedItem:Integer;
  vCurrentLine, vCurrentPosition:Integer;
  vCurrentLineText:string;
begin
  vSelectedItem:=ListBoxSqlFunctions.ItemIndex;
  if vSelectedItem>=0 then
    with ListBoxSqlFunctions do begin
      vCurrentLine:=SynEditSQLEditor.CaretY;
      vCurrentPosition:=SynEditSQLEditor.CaretX;
      vCurrentLineText:=SynEditSQLEditor.Lines.Strings[vCurrentLine];
      SynEditSQLEditor.Lines.Strings[vCurrentLine]:=Copy(vCurrentLineText,0,vCurrentPosition)
        +' '+Items[vSelectedItem]
        +Copy(vCurrentLineText,vCurrentPosition+1,Length(vCurrentLineText)-vCurrentPosition)
        +' ';  //This inserts into the place where cursor is positioned.
    end
end;

procedure TFormMain.ButtonApplyTableNameToQueryTextClick(Sender: TObject);
var
  vCurrentLine, vCurrentPosition:Integer;
  vCurrentLineText:string;
begin
  with Datamodule1.Tables do begin
    vCurrentLine:=SynEditSQLEditor.CaretY;
    vCurrentPosition:=SynEditSQLEditor.CaretX;
    vCurrentLineText:=SynEditSQLEditor.Lines.Strings[vCurrentLine];
    SynEditSQLEditor.Lines.Strings[vCurrentLine]:=Copy(vCurrentLineText,0,vCurrentPosition)
      +' '+FieldByName('TableName').AsString
      +Copy(vCurrentLineText,vCurrentPosition+1,Length(vCurrentLineText)-vCurrentPosition)
      +' ';  //This inserts into the place where cursor is positioned.
  end;
end;

procedure TFormMain.ButtonFilterQueryResultClick(Sender: TObject);
begin
  OpenFormFilters(Datamodule1.ZMQueryDatasetQueryResult);
end;

procedure TFormMain.ButtonFilterTableClick(Sender: TObject);
begin
  with Datamodule1 do begin
    try
      Filters.Edit;
      Filters.Post;
      DatasourceCSVTableData.DataSet.DisableControls;
      try
        DatasourceCSVTableData.DataSet.Filtered:=False;
        DatasourceCSVTableData.DataSet.Filter:=Filters.FieldByName('FILTERTEXT').AsString;
        DatasourceCSVTableData.DataSet.Filtered:=True;
      except
        on E:Exception do begin
          ShowMessage('Error: '+E.Message);
          DatasourceCSVTableData.DataSet.Filtered:=False;
        end;
      end;
    finally
      DatasourceCSVTableData.DataSet.EnableControls;
    end;
  end;
end;

procedure TFormMain.ButtonFindQueryResultClick(Sender: TObject);
begin
  OpenFormFind(Datamodule1.ZMQueryDatasetQueryResult);
end;

procedure TFormMain.ButtonRunQueryClick(Sender: TObject);
var
  I:Integer;
  crs: TCursor;
begin
  try
    crs:= Screen.Cursor;
    Screen.Cursor := crHourglass;
    with Datamodule1 do begin
      ZMQueryDatasetQueryResult.Close;
      ZMQueryDatasetQueryResult.ClearDataSet;
      //Set SQL
      ZMQueryDatasetQueryResult.SQL.Clear;
      for I:=0 to SynEditSQLEditor.Lines.Count-1 do begin
        ZMQueryDatasetQueryResult.SQL.Append(SynEditSQLEditor.Lines[I]);
      end;
      RunQuery;
    end;
  finally
    Screen.Cursor := crs;
  end;
end;

procedure TFormMain.RunQuery;
begin
  ShowMessage('MightyQuery will try to execute the query now. Please, be patient.');
  //Execute SQL.
  with Datamodule1 do begin

    ZMQueryDatasetQueryResult.Active:=False;
    ZMQueryDatasetQueryResult.FieldDefs.Clear;
    ZMQueryDatasetQueryResult.Fields.Clear;

    DataSourceQueryResult.Enabled:=False;
    DataSourceQueryResult.DataSet:=nil;
    ZMQueryDatasetQueryResult.QueryExecute;
    DataSourceQueryResult.DataSet:=ZMQueryDatasetQueryResult;
    DataSourceQueryResult.Enabled:=True;
  end;
  //Autofill columns then let columns be adjustable.
  DBGridQueryResult.AutoFillColumns:=True;
  DBGridQueryResult.AutoFillColumns:=False;
  //Adjust columns to titles width.
  //AdjusTRxDBGridColumnsToTitles(DBGridQueryResult);
  //DBGridQueryResult.AutoAdjustColumns;
  DBGridQueryResult.OptimizeColumnsWidthAll;
  //Bring TabSheetQueryResult to front plan.
  PageControlQueries.ActivePage:=TabSheetQueryResult;
  ShowMessage('Finished!');
end;

procedure TFormMain.ButtonSaveQueryResultClick(Sender: TObject);
begin
   try
     CSVExporterQueryResult.DataSet:=Datamodule1.ZMQueryDatasetQueryResult;
     CSVExporterQueryResult.FileName:=FileNameEditSaveQueryResult.FileName;
     CSVExporterQueryResult.Execute;
     ShowMessage('Query result saved to file: '+FileNameEditSaveQueryResult.FileName);
   except
     on E:Exception do begin
       ShowMessage('Error while trying to save query result. '+E.Message);
     end;
   end;
end;

procedure TFormMain.ButtonSetFieldsSchemaClick(Sender: TObject);
var
 vFileName:String;
 vFileExists: boolean;
 vPreviewDataSet: TDataset;
begin
  with Datamodule1 do begin
    {$IFDEF EnforceZMSQL}
    vPreviewDataSet:=ZMQueryDatasetCSVTableData;
    {$ELSE}
    vPreviewDataSet:=CSVTableData;
    {$ENDIF}
    try
      //Disable controls.
      Fields.DisableControls;
      vFileName:=ZMConnection1.DatabasePath+Tables.FieldByName('TableName').AsString+'.csv';
      try
        //First, check whether the table exists.
        vFileExists:=FileExistsUTF8(vFileName);
        if vFileExists then begin
          //The table does exist.
          //Call procedures for inserting and droping columns.
          InsertColumnsToCSVTable;
          DropColumnsFromCSVTable;
          //Call procedure for rearranging columns order.
          RearrangeColumnsInCSVTable;
        end
        else begin
          //The table does not exist.
          CreateCSVTable;
        end;
      except
        on E:Exception do begin
          ShowMessage('Error in SetFieldsSchema procedure. '+E.Message);
        end;
      end;
      ShowMessage('Fields (columns) restructured!');
    finally
      //Refresh CSVTable preview.
      if vPreviewDataSet.Active then ActivateCSVTablePreview;
      //Refresh Schema information.(This must be executed only when both insert and delete statements are finished.
      ButtonGetFieldsSchemaClick(Self);
      //Enable controls.
      Fields.EnableControls;
    end;
  end;
end;

procedure TFormMain.ButtonTestConnectionClick(Sender: TObject);
begin
  with Datamodule1 do begin
    try
      with ZConnection1 do begin
        SQLConnectors.Edit;
        SQLConnectors.Post;
        //Set properties
        Protocol:=SQLConnectors.FieldByName('PROTOCOL').AsString;
        Database:=SQLConnectors.FieldByName('DATABASE').AsString;
        HostName:=SQLConnectors.FieldByName('HOSTNAME').AsString;
        Port:=SQLConnectors.FieldByName('PORT').AsInteger;
        User:=SQLConnectors.FieldByName('USER').AsString;
        Password:=SQLConnectors.FieldByName('PASSWORD').AsString;
        Catalog:=SQLConnectors.FieldByName('CATALOG').AsString;
        //Try to connect
        try
          Connected:=True;
          ShowMessage ('Connection O.K.');
        except
          on E:Exception do begin
            ShowMessage('I could not establish connection. '+E.Message);
          end;
        end;
      end;
    finally
      ZConnection1.Connected:=False;
    end;
  end;
end;

procedure TFormMain.ButtonUnselectAllConnectorsClick(Sender: TObject);
begin
  with DataModule1.SQLConnectors do begin
    First;
    while not EOF do begin
      Edit;
      FieldByName('TOBEIMPORTED').AsBoolean:=False;
      Post;
      Next;
    end;
  end;
end;

procedure TFormMain.ButtonUnselectAllExternalTablesClick(Sender: TObject);
begin
  with DataModule1.SQLTables do begin
    First;
    while not EOF do begin
      Edit;
      FieldByName('TOBEIMPORTED').AsBoolean:=False;
      Post;
      Next;
    end;
  end;
end;

procedure TFormMain.ButtonUpdateCSVTableClick(Sender: TObject);
begin
  UpdateCSVTable;
end;

procedure TFormMain.InsertColumnsToCSVTable;
var
  I:Integer;
  vFieldExists:Boolean;
  vSQL:String;
  vOldFieldList: TStringList;
  vFileName:String;
begin
  with Datamodule1 do begin
    try
      vOldFieldList:=TStringList.Create;
      //Examine CSV file
      vFileName:=ZMConnection1.DatabasePath+Tables.FieldByName('TableName').AsString+'.csv';
      GetFieldsListFromCSVFile(vFileName, vOldFieldList);
      //Disable controls.
      Fields.DisableControls;
      vSQL:='';
      //Iterate through Fields Schema information and insert if neccessary.
      Fields.First;
      while not Fields.EOF do begin
        //Iterate through  old fields schema to see whether the field already exists or not.
        vFieldExists:=False;
        for I:=0 to vOldFieldList.Count-1 do begin
          if Fields.FieldByName('FieldName').AsString=vOldFieldList[I] then vFieldExists:=True;
            end;
        //If a field does not exist, create it.
        if vFieldExists=False then begin
            vSQL:= vSQL+'ALTER TABLE '+Tables.FieldByName('TableName').AsString;
            vSQL:= vSQL + ' ADD COLUMN ' + Fields.FieldByName('FieldName').AsString
              + ';' + LineEnding;
        end;
        Fields.Next;
      end;
      vSQL:=vSQL + 'COMMIT;';
      //Execute INSERT statements.
      try
        ZMQueryDataSetActionQuery.SQL.Text:=vSQL;
        ZMQueryDataSetActionQuery.QueryExecute;
      except
        on E:Exception do begin
          ShowMessage('I could not execute following query: '+ ZMQueryDataSetActionQuery.SQL.Text
          + LineEnding +E.Message);
        end;
      end;
    finally
      vOldFieldList.Free;
      //Enable controls.
      Fields.EnableControls;
    end;
  end;
end;

procedure TFormMain.DropColumnsFromCSVTable;
var
  I:Integer;
  vFieldExists:Boolean;
  vSQL:String;
  vOldFieldList: TStringList;
  vFileName:String;
begin
  with Datamodule1 do begin
    try
      vOldFieldList:=TStringList.Create;
      //Examine CSV file
      vFileName:=ZMConnection1.DatabasePath+Tables.FieldByName('TableName').AsString+'.csv';
      GetFieldsListFromCSVFile(vFileName, vOldFieldList);
      //Disable controls.
      Fields.DisableControls;
      //--DROP COLUMNS--
      //Iterate through CSVTable Schema to see whether there are some fields
      //that are not present in the table Fields.
      //If so, delete it.
      vSQL:='';
      for I:=0 to vOldFieldList.Count-1 do begin
        vFieldExists:=False;
        Fields.First;
        while not Fields.EOF do begin
          if Fields.FieldByName('FieldName').AsString=vOldFieldList[I] then vFieldExists:=True;
          Fields.Next;
        end;
        //If a field does not exist in the table Fields and exists in the CSV file, delete it.
        if vFieldExists=False then
          begin
            vSQL:=vSQL+'ALTER TABLE ' + Tables.FieldByName('TableName').AsString
              + ' DROP COLUMN ' + vOldFieldList[I]
              + ';' + LineEnding;
          end;
      end;
      vSQL:=vSQL + 'COMMIT';
      //Execute DELETE statements.
      try
        ZMQueryDatasetActionQuery.SQL.Text:=vSQL;
        ZMQueryDatasetActionQuery.QueryExecute;
      except
        on E:Exception do begin
          ShowMessage('I could not execute following query: '+ ZMQueryDataSetActionQuery.SQL.Text
          + LineEnding +E.Message);
        end;
      end;
    finally
      vOldFieldList.Free;
      //Enable controls.
      Fields.EnableControls;
    end;
  end;
end;

procedure TFormMain.RearrangeColumnsInCSVTable;
var
  vSQL:String;
  vFieldList: TStringList;
begin
  with Datamodule1 do begin
    try
      vFieldList:=TStringList.Create;
      VFieldList.Delimiter:=char(',');
      //Disable controls.
      Fields.DisableControls;
      vSQL:='';
      vSQL:='ASSIGN TO ' + Tables.FieldByName('TableName').AsString + '_new' + LineEnding;
      vSQL:= vSQL+'SELECT ' + LineEnding;
      //Iterate through Fields Schema information
      Fields.First;
      while not Fields.EOF do begin
        VFieldList.Add (Fields.FieldByName('FieldName').AsString);
        Fields.Next;
      end;
      vSQL:= vSQL + vFieldList.DelimitedText + LineEnding;
      vSQL:= vSQL+'FROM '+Tables.FieldByName('TableName').AsString + ';' + LineEnding;
      vSQL:=vSQL + 'DROP TABLE ' + Tables.FieldByName('TableName').AsString + ';' + LineEnding;
      vSQL:=vSQL + 'ASSIGN TO ' + Tables.FieldByName('TableName').AsString + ' SELECT * FROM ' +  Tables.FieldByName('TableName').AsString + '_new'  + ';' + LineEnding;
      vSQL:=vSQL + 'SAVE TABLE ' + Tables.FieldByName('TableName').AsString + ';' + LineEnding;
      vSQL:=vSQL + 'RELEASE TABLE ' + Tables.FieldByName('TableName').AsString + '_new' + ';' + LineEnding;
      vSQL:=vSQL + 'COMMIT ' + ';';
      //Execute SQL.
      try
        ZMQueryDataSetActionQuery.SQL.Text:=vSQL;
        ZMQueryDataSetActionQuery.QueryExecute;
      except
        on E:Exception do begin
          ShowMessage('I could not execute following query: '+ ZMQueryDataSetActionQuery.SQL.Text
          + LineEnding +E.Message);
        end;
      end;
    finally
      vFieldList.Free;
      //Enable controls.
      Fields.EnableControls;
    end;
  end;
end;

procedure TFormMain.CombineTables(pCombinedTableName: string; pAlphabetically: boolean=False);
var
  vSQL, vInsertSQL:String;
  vCombinedFieldList, vFieldList: TStringList;
  vFileName: string;
begin
  with Datamodule1 do begin
    try

      vFileName:=ZMConnection1.DatabasePath + pCombinedTableName + '.csv';

      vCombinedFieldList:=TStringList.Create;
      vCombinedFieldList.Delimiter:=char(',');
      if pAlphabetically then begin
        vCombinedFieldList.Duplicates:=dupIgnore;
        vCombinedFieldList.SortStyle:=sslAuto;
        vCombinedFieldList.Sorted:=True;
      end;

      vFieldList:=TStringList.Create;
      VFieldList.Delimiter:=char(',');

      //Disable controls.
      Fields.DisableControls;

      vSQL:='';
      vInsertSQL:='';

      //Iterate through Tables and Fields Schema information
      Tables.First;
      while not Tables.EOF do begin
        vFieldList.Clear;
        Fields.First;
        while not Fields.EOF do begin
          if (vCombinedFieldList.IndexOf(Fields.FieldByName('FieldName').AsString)=-1) then vCombinedFieldList.Add(Fields.FieldByName('FieldName').AsString);
          vFieldList.Add (Fields.FieldByName('FieldName').AsString);
          Fields.Next;
        end;
        vInsertSQL:=vInsertSQL + 'INSERT INTO ' + pCombinedTableName + ' SELECT * FROM ' + Tables.FieldByName('TABLENAME').AsString + ';' + LineEnding;
        Tables.Next;
      end;

      vSQL:='';
      vSQL:='CREATE TABLE ' + pCombinedTableName + '('
                    + vCombinedFieldList.DelimitedText + ');' + LineEnding;
      vSQL:=vSQL + vInsertSQL;
      vSQL:=vSQL + 'COMMIT ' + ';';

      if FileExistsUTF8(vFileName) then DeleteFileUTF8(vFileName);

      //Execute SQL.
      try
        ZMQueryDataSetActionQuery.SQL.Text:=vSQL;
        ZMQueryDataSetActionQuery.QueryExecute;
      except
        on E:Exception do begin
          ShowMessage('I could not execute following query: '+ ZMQueryDataSetActionQuery.SQL.Text
          + LineEnding +E.Message);
        end;
      end;
    finally
      vFieldList.Free;
      vCombinedFieldList.Free;
      //Enable controls.
      Fields.EnableControls;
    end;
  end;
end;

procedure TFormMain.CombineTablesToTemplate(pTemplateTableName: string);
var
  vSQL, vInsertSQL:String;
  vTemplateFieldList, vFieldList: TStringList;
  vFileName: string;
  vAFieldIsInTemplate: boolean;
  i: integer;
begin
  with Datamodule1 do begin
    try

      vFileName:=ZMConnection1.DatabasePath + pTemplateTableName + '.csv';

      vTemplateFieldList:=TStringList.Create;
      vTemplateFieldList.Delimiter:=char(',');

      vFieldList:=TStringList.Create;
      VFieldList.Delimiter:=char(',');

      //Disable controls.
      Fields.DisableControls;

      vSQL:='';
      vInsertSQL:='';

      //Inspect template table
      Fields.First;
      while not Fields.EOF do begin
        if (Tables.FieldByName('TABLENAME').AsString=pTemplateTableName) then begin
          if (vTemplateFieldList.IndexOf(Fields.FieldByName('FieldName').AsString)=-1) then vTemplateFieldList.Add(Fields.FieldByName('FieldName').AsString);
        end;
        Fields.Next;
      end;

      //Iterate through Tables and Fields Schema information
      Tables.First;
      while not Tables.EOF do begin
        vFieldList.Clear;
        Fields.First;
        while not Fields.EOF do begin
          if (Tables.FieldByName('TABLENAME').AsString<>pTemplateTableName) then begin
            vFieldList.Add (Fields.FieldByName('FieldName').AsString);
          end;
          Fields.Next;
        end;

        //Inspect whether this table contains template fields
        vAFieldIsInTemplate:=False;
        for i:=0 to vFieldList.Count-1 do begin
          if (vTemplateFieldList.IndexOf(vFieldList[i])>0) then vAFieldIsInTemplate:=True;
        end;

        if ((Tables.FieldByName('TABLENAME').AsString<>pTemplateTableName) and (vAFieldIsInTemplate=True)) then begin
          vInsertSQL:=vInsertSQL + 'INSERT INTO ' + pTemplateTableName + ' SELECT * FROM ' + Tables.FieldByName('TABLENAME').AsString + ';' + LineEnding;
        end;

        Tables.Next;
      end;

      vSQL:='';
      if (FileExistsUTF8(vFileName)=False) then begin
        vSQL:='CREATE TABLE ' + pTemplateTableName + '('
                      + vTemplateFieldList.DelimitedText + ');' + LineEnding;
      end;
      vSQL:=vSQL + vInsertSQL;
      vSQL:=vSQL + 'COMMIT ' + ';';

      //Execute SQL.
      try
        ZMQueryDataSetActionQuery.SQL.Text:=vSQL;
        ZMQueryDataSetActionQuery.QueryExecute;
      except
        on E:Exception do begin
          ShowMessage('I could not execute following query: '+ ZMQueryDataSetActionQuery.SQL.Text
          + LineEnding +E.Message);
        end;
      end;
    finally
      vFieldList.Free;
      vTemplateFieldList.Free;
      //Enable controls.
      Fields.EnableControls;
    end;
  end;
end;

procedure TFormMain.RenameCSVColumns;
var
  I:Integer;
  vSQL:String;
  vOldFieldList: TStringList;
  vNewFieldList: TStringList;
  vFileName:String;
  vFieldExpression: string;
begin
  with Datamodule1 do begin
    try
      vOldFieldList:=TStringList.Create;
      vNewFieldList:=TStringList.Create;
      vNewFieldList.Delimiter:=char(',');
      //Examine CSV file
      vFileName:=ZMConnection1.DatabasePath+Tables.FieldByName('TableName').AsString+'.csv';
      GetFieldsListFromCSVFile(vFileName, vOldFieldList);
      //Disable controls.
      Fields.DisableControls;
      //Iterate through Fields Schema information
      Fields.First;
      i:=0;
      while not Fields.EOF do begin
            vFieldExpression:=vOldFieldList[i] + ' AS ' + Fields.FieldByName('FieldName').AsString;
            vNewFieldList.Add(vFieldExpression);
        Fields.Next;
        inc(I);
      end;
      //Construct SQL
      vSQL:='ASSIGN TO ' + Tables.FieldByName('TableName').AsString + '_new' + LineEnding;
      vSQL:=vSQL + 'SELECT ' + UTF8StringReplace(vNewFieldList.DelimitedText, '"', '', [rfReplaceAll, rfIgnoreCase])  + LineEnding;
      vSQL:=vSQL + 'FROM ' +  Tables.FieldByName('TableName').AsString + ';' + LineEnding;
      vSQL:=vSQL + 'DROP TABLE ' + Tables.FieldByName('TableName').AsString + ';' + LineEnding;
      vSQL:=vSQL + 'ASSIGN TO ' + Tables.FieldByName('TableName').AsString + ' SELECT * FROM ' +  Tables.FieldByName('TableName').AsString + '_new'  + ';' + LineEnding;
      vSQL:=vSQL + 'SAVE TABLE ' + Tables.FieldByName('TableName').AsString + ';' + LineEnding;
      vSQL:=vSQL + 'RELEASE TABLE ' + Tables.FieldByName('TableName').AsString + '_new' + ';' + LineEnding;
      vSQL:=vSQL + 'COMMIT;';
      //Execute INSERT statements.
      try
        ZMQueryDataSetActionQuery.SQL.Text:=vSQL;
        ZMQueryDataSetActionQuery.QueryExecute;
      except
        on E:Exception do begin
          ShowMessage('I could not execute following query: '+ ZMQueryDataSetActionQuery.SQL.Text
          + LineEnding +E.Message);
        end;
      end;
    finally
      vOldFieldList.Free;
      vNewFieldList.Free;
      //Enable controls.
      Fields.EnableControls;
    end;
  end;
end;

procedure TFormMain.CreateCSVTable;
var
  vSQL, vInsertColumns:String;
begin
  with Datamodule1 do begin
    try
      //Disable controls.
      Fields.DisableControls;
      //Set initial variables values.
      vInsertColumns:='''';
      vSQL:='';
      vSQL:='CONNECT TO ' + ZMConnection1.DatabasePath + ';' + LineEnding;
      //Iterate through Fields Schema information.
      Fields.First;
      while not Fields.EOF do begin
        vInsertColumns:=vInsertColumns+Fields.FieldByName('FieldName').AsString+''''+','+'''';
        Fields.Next;
      end;
      //Trim the last "," from the vInsertColumns.
      vInsertColumns:=Copy(vInsertColumns,0,(Length(vInsertColumns)-1));

      vSQL:= vSQL+'CREATE TABLE '+''''+Tables.FieldByName('TableName').AsString+''''
        +'('+vInsertColumns+')'+';';
      //Execute CREATE TABLE statement.
      ZMQueryDatasetActionQuery.SQL.Text:=vSQL;
      ZMQueryDatasetActionQuery.QueryExecute;
    finally
      //Enable controls.
      Fields.EnableControls;
    end;
  end;
end;

procedure TFormMain.SetZMSQLProject;
begin
  with Datamodule1 do begin
    //Connect to "database"
    ZMConnection1.Connected:=False;
    ZMConnection1.DatabasePath:=UTF8Copy(Projects.FieldByName('ProjectPath').AsString, 1, UTF8Length(Projects.FieldByName('ProjectPath').AsString)-1);
    ZMConnection1.Connected:=True;

    //Set FileNameEdit controls
    FileNameEditTableFile.InitialDir:=ZMConnection1.DatabasePath;
    FileNameEditTableFile.FileName:=FileNameEditTableFile.InitialDir;
    FileNameEditTableFile.Text:=FileNameEditTableFile.InitialDir;

    FileNameEditSaveQueryResult.InitialDir:=ZMConnection1.DatabasePath;
    FileNameEditSaveQueryResult.FileName:=FileNameEditTableFile.InitialDir;
    FileNameEditSaveQueryResult.Text:=FileNameEditTableFile.InitialDir;


    FileNameEditSaveQuerySQL.InitialDir:=ZMConnection1.DatabasePath;
    FileNameEditSaveQuerySQL.FileName:=FileNameEditTableFile.InitialDir;
    FileNameEditSaveQuerySQL.Text:=FileNameEditTableFile.InitialDir;
  end;
end;

procedure TFormMain.UpdateTablesList;
var
 i:Integer;
 vBookMark:TBookmark;
begin
  with Datamodule1 do begin
    vBookMark:=Tables.GetBookmark;
    try
      Tables.DisableControls;
      Fields.DisableControls;
      SynSQLSyn1.TableNames.Text:='';
      Tables.First;
      for i:=0 to Tables.RecordCount-1 do begin
        SynSQLSyn1.TableNames.Append(Tables.FieldByName('TABLENAME').AsString);
        Tables.Next;
      end;
    finally
      Tables.GotoBookmark(vBookmark);
      Tables.EnableControls;
      Fields.EnableControls;
    end;
  end;
end;

procedure TFormMain.UpdateAutocompleteList;
var
  vBookmark1:TBookmark;
  vBookmark2:TBookmark;
begin
  with Datamodule1 do begin
    vBookmark1:=Tables.GetBookmark;
    vBookmark2:=Fields.GetBookmark;
    try
      Tables.DisableControls;
      Fields.DisableControls;
      AutocompleteList.Clear;
      AutocompleteList.Append('SELECT');
      AutocompleteList.Append('FROM');
      AutocompleteList.Append('SELECT * FROM');
      AutocompleteList.Append('WHERE');
      AutocompleteList.Append('HAVING');
      AutocompleteList.Append('GROUP BY');
      AutocompleteList.Append('ORDER BY');
      AutocompleteList.Append('AS');
      AutocompleteList.Append('IN ()');
      AutocompleteList.Append('LIKE ()');
      AutocompleteList.Append('CREATE TABLE');
      AutocompleteList.Append('ALTER TABLE');
      AutocompleteList.Append('DROP TABLE');
      AutocompleteList.Append('ADD COLUMN');
      AutocompleteList.Append('DROP COLUMN');
      AutocompleteList.Append('ASSIGN TO');
      AutocompleteList.Append('SAVE TABLE');
      AutocompleteList.Append('RELEASE TABLE');
      AutocompleteList.Append('COMMIT');
      AutocompleteList.Append('INSERT INTO');
      AutocompleteList.Append('VALUES ()');
      AutocompleteList.Append('UPDATE');
      AutocompleteList.Append('SET');
      AutocompleteList.Append('DELETE FROM');
      AutocompleteList.Append('FIX(expression, precision)');
      AutocompleteList.Append('TRUNC(expression, precision)');
      AutocompleteList.Append('ASNUMBER(expression)');
      AutocompleteList.Append('FORMAT(value,formatstring');
      AutocompleteList.Append('FORMAT(value,''[literalstring]%[width][.prec]type'')' );
      AutocompleteList.Append('YEAR()');
      AutocompleteList.Append('MONTH()');
      AutocompleteList.Append('DAY()');
      AutocompleteList.Append('WEEKNUMBERS()');
      AutocompleteList.Append('DATEADD (interval, number, datestring)');
      AutocompleteList.Append('SOUNDEX(expression)');
      AutocompleteList.Append('LOWER(expression)');
      AutocompleteList.Append('UPPER(expression)');
      AutocompleteList.Append('TRIM(expression)');
      AutocompleteList.Append('LEFT(expression, count)');
      AutocompleteList.Append('RIGHT(expression, count)');
      AutocompleteList.Append('MID(expression, from, count)');
      AutocompleteList.Append('LENGTH(expression)');
      AutocompleteList.Append('REPLACE(source, oldpattern, newpattern)');
      AutocompleteList.Append('SUBSTR_AFTER(source, substring)');
      AutocompleteList.Append('SUBSTR_BEFORE(source, substring)');
      AutocompleteList.Append('SQR(expression)');
      AutocompleteList.Append('SQRT(expression)');
      AutocompleteList.Append('SIN(expression)');
      AutocompleteList.Append('COS(expression)');
      AutocompleteList.Append('CEIL(expression)');
      AutocompleteList.Append('FLOOR(expression)');
      Tables.First;
      AutocompleteList.Append(Tables.FieldByName('TABLENAME').AsString);
      while not Tables.EOF do begin
        Fields.First;
        while not Fields.EOF do begin
          AutocompleteList.Append(Fields.FieldByName('FIELDNAME').AsString);
          AutocompleteList.Append(Tables.FieldByName('TABLENAME').AsString+'.'+Fields.FieldByName('FIELDNAME').AsString);
          Fields.Next;
        end;
        Tables.Next;
      end;
      Tables.GotoBookmark(vBookmark1);
      Fields.GotoBookmark(vBookmark2);
    finally
      Tables.FreeBookmark(vBookmark1);
      Fields.FreeBookmark(vBookmark2);
      Tables.EnableControls;
      Fields.EnableControls;
    end;
  end;
end;

procedure TFormMain.GetSpreadsheetsNames(FileList: TStrings);
var
  SR: TSearchRec;
  Path: string;
  vSpreadsheetType:array[1..3] of String;
  i:Integer;
begin
  Path:=Datamodule1.ZMConnection1.DatabasePath;
  vSpreadsheetType[1]:='*.xls';
  vSpreadsheetType[2]:='*.xlsx';
  vSpreadsheetType[3]:='*.ods';
  for i:=Low(vSpreadsheetType) to High(vSpreadsheetType)  do begin
    if FindFirst(Path + vSpreadsheetType[i], faAnyFile, SR) = 0 then
    begin
      repeat
        if (SR.Attr <> faDirectory) then
        begin
          //FileList.Add(ChangeFileExt(SR.Name, ''));
          FileList.Add(SR.Name);
        end;
      until FindNext(SR) <> 0;
      FindClose(SR);
    end;
  end;
end;

procedure TFormMain.GetFieldsNames;
var
  I:integer;
  vFileName:String;
  vFieldList: TStringList;
begin
  with Datamodule1 do begin
    try
      vFieldList:=TStringList.Create;
      Fields.DisableControls;
      //Examine CSV file
      vFileName:=ZMConnection1.DatabasePath+Tables.FieldByName('TableName').AsString+'.csv';
      GetFieldsListFromCSVFile(vFileName, vFieldList);
      //Register Fields
      try
        with Fields do begin
          //Delete all corresponding records from the table Fields.
           First;
           while not EOF do begin
             Delete;
           end;
           //Refresh;
          //Register fields
          for I:=0 to vFieldList.Count-1 do begin
            try
              Append;
              FieldByName('FieldName').AsString:=vFieldList[I];
              Post;
            except
               on E:Exception do begin
                  ShowMessage('Error for the field:' + vFieldList[I] +LineEnding + 'I could not retrieve fields schema information. '+E.Message);
               end;
            end;
          end;
          Refresh;
        end;
      except
        on E:Exception do begin
          ShowMessage('I could not retrieve fields schema information for the following csv dataset: ' + Tables.FieldByName('TableName').AsString + LineEnding + E.Message);
        end;
      end;
    finally
      vFieldList.Free;
      Fields.EnableControls;
    end;
  end;
end;

procedure TFormMain.GetFieldsListFromCSVFile(pFileName:string; List: TStrings);
var
  FileStream: TFileStream;
  Parser: TCSVParser;
  vFieldName: string;
  vHasFieldNames: boolean;
begin
  try
    Parser:=TCSVParser.Create;
    FileStream:= TFileStream.Create(pFileName, fmOpenRead +fmShareDenyWrite);
    Parser.Delimiter:=DetermineSeparator(pFileName, vHasFieldNames);
    if (vHasFieldNames=False) then begin
      ShowMessage('In the csv file: ' + pFileName + ' '
      + 'one or more columns have wrong incorrect field names!' + LineEnding
      + 'Column names must not contain spaces, neither be enquoted. Please correct the column names, i.e. field names!');
    end;
    Parser.SetSource(FileStream);
    while Parser.ParseNextCell do begin
          //Set header data
          if (Parser.CurrentRow=0) then begin
             vFieldName:=Parser.CurrentCellText;
             vFieldName:=ConvertEncoding(vFieldName, GuessEncoding(vFieldName), EncodingUTF8);
             List.Add(vFieldName);
          end
          else begin
            break;
          end;
    end;
  finally
    Parser.Free;
    FileStream.Free;
  end;
end;

function TFormMain.DetermineSeparator(AFileName: String; var HasFieldNames: Boolean): Char;
const
  Seps: array[1..8] of char = (';', #9, ',', '|', ':', '-', '/', '\');
    //fdSemicolon, fdTab, fdComma, fdBar, fdColon, fdDash, fdSlash, fdBackSlash);
var F:TextFile;
   S{,S2,T}:String;
   I:Integer;
begin
  AssignFile(F,AFileName);
  Reset(F);
  try
    Readln(F,S);
  finally
    CloseFile(F);
  end;
  Result:=#0;
  //Scan the line for the separator character:
  I:=0;
  while (Result=#0) and (I<8) do
  begin
    Inc(I);
    if UTF8Pos(Seps[i],S)<>0 then Result:=Seps[i];
  end;
  //Try and detect the presence of fieldnames:
  //no spaces or double separator:
  if Result<>#0 then
    HasFieldNames:=(UTF8Pos('',S)=0) and
                   (UTF8Pos(Result+Result,S)=0);
end;

procedure TFormMain.OpenFormFilters(var pDataset:TZMQueryDataset);
begin
  with FormFilters do begin
    //Close the form FormFilters, if it is already opened.
    try
      if FormFilters.Showing=True then
        Close;
    except
      ShowMessage('Error while trying to close form FormFilters.');
    end;
    //Create the form FormFilters.
    Create(pDataset);
    //Open the form FormFilters.
    try
      Visible:=True;
    except
      on E:Exception do begin
        ShowMessage('Error while trying to open form "FormFilters".'+E.Message);
      end;
    end;
  end;
end;

procedure TFormMain.OpenFormFind(var pDataset:TZMQueryDataset);
begin
  with FormFind do begin
    //Close the form FormFind, if it is already opened.
    try
      if FormFind.Showing=True then
        Close;
    except
      ShowMessage('Error while trying to close form FormFind.');
    end;
    //Create the form FormFind.
    try
      Create(pDataset);
    except
      on E:Exception do begin
        ShowMessage(E.Message);
      end;
    end;
    //Open the form FormFind.
    try
      Visible:=True;
    except
      on E:Exception do begin
        ShowMessage('Error while trying to open form "FormFind".'+E.Message);
      end;
    end;
  end;
end;

procedure TFormMain.OpenFormExportToCSV(var pDataset:TZMQueryDataset);
begin
  with FormExportToCSV do begin
    //Close the form FormExportToCSV, if it is already opened.
    try
      if FormExportToCSV.Showing=True then
        Close;
    except
      ShowMessage('Error while trying to close form FormExportToCsv.');
    end;
    //Create the form FormExportToCSV.
    Create(pDataset);
    FormExportToCSV.FileNameEdit1.FileName:=DataModule1.ZMConnection1.DatabasePath + pDataset.Name + '.csv';
    //Open the form FormExportToCSV.
    try
      Visible:=True;
    except
      on E:Exception do begin
        ShowMessage('Error while trying to open form "FormExportToCSV". '+E.Message);
      end;
    end;
  end;
end;


initialization
  {$I unit1.ctrs}

end.

