unit foBafSqlOpen;

interface

uses
  System.SysUtils, System.Types, System.UITypes, System.Classes, System.Variants,
  FMX.Types, FMX.Controls, FMX.Forms, FMX.Graphics, FMX.Dialogs,
  FMX.Controls.Presentation, FMX.StdCtrls, FMX.Layouts, FMX.ListBox, FMX.Edit,
  FMX.ComboEdit, FMX.Memo.Types, FMX.TabControl, FMX.ScrollBox, FMX.Memo,
  Winapi.Windows, DB, System.Rtti, FMX.Grid.Style, FMX.Grid;

type
  THistItem = class
    FSql: string;
    FCon: string;
  end;


  TfrmSqlOpen = class(TForm)
    pnlDesk: TPanel;
    pnlRight: TPanel;
    pnlLeft: TPanel;
    Splitter1: TSplitter;
    pnlTop: TPanel;
    pnlBottom: TPanel;
    Splitter2: TSplitter;
    lbSelect: TListBox;
    edtSelect: TEdit;
    btnTablesRefresh: TButton;
    cmbDatabase: TComboBox;
    lblSelectOpen2: TLabel;
    btnOpen: TButton;
    cmbSelect: TComboEdit;
    memSql: TMemo;
    TabControl1: TTabControl;
    tabSelect: TTabItem;
    tabHistory: TTabItem;
    tabData: TTabItem;
    tabFields: TTabItem;
    memFields: TMemo;
    sgSelect: TStringGrid;
    sgData: TStringGrid;
    btnFields: TButton;
    edtFields: TEdit;
    lblFields: TLabel;
    cbFields: TCheckBox;
    memHist: TMemo;
    lbHist: TListBox;
    Splitter3: TSplitter;
    btnSqlHist: TButton;
    btnSqlSql: TButton;
    btnSqlLog: TButton;
    tabResultFields: TTabItem;
    btnResultFields: TButton;
    cbResultFields: TCheckBox;
    edtResultFields: TEdit;
    memResultFields: TMemo;
    procedure btnTablesRefreshClick(Sender: TObject);
    procedure cmbDatabaseChange(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure edtSelectChange(Sender: TObject);
    procedure btnOpenClick(Sender: TObject);
    procedure lbSelectDblClick(Sender: TObject);
    procedure lbSelectChange(Sender: TObject);
    procedure btnFieldsClick(Sender: TObject);
    procedure lbHistChange(Sender: TObject);
    procedure btnSqlHistClick(Sender: TObject);
    procedure memSqlKeyDown(Sender: TObject; var Key: Word; var KeyChar: Char;
      Shift: TShiftState);
    procedure btnSqlSqlClick(Sender: TObject);
    procedure btnResultFieldsClick(Sender: TObject);
    procedure btnSqlLogClick(Sender: TObject);
  private
    FConName: string;
    FTables: TStringList;
    procedure AddHist(ASql: string);
  public
    procedure Init;
    procedure SetFontSize(ASize: single);
  end;

var
  frmSqlOpen: TfrmSqlOpen;

implementation

{$R *.fmx}

uses dmMain, uBafCodeUtils, uBafTypes, uOsStuff, uBafFmxUtils;

procedure TfrmSqlOpen.AddHist(ASql: string);
var
  i: integer;
  LItem: THistItem;
begin
  for i := 0 to lbHist.Items.Count - 1 do begin
    LItem := (lbHist.Items.Objects[i] as THistItem);
    if LItem.FSql = ASql then begin
      lbHist.Items.Move(i, 0);
      lbHist.Items[i] := FormatDateTime('hh:mm:ss', now) + ' - ' + LItem.FCon;
      exit;
    end;
  end;
  LItem := THistItem.Create;
  LItem.FSql := ASql;
  LItem.FCon := FConName;
  lbHist.Items.InsertObject(0,
      FormatDateTime('hh:mm:ss', now) + ' - ' + LItem.FCon, LItem);
end;

procedure TfrmSqlOpen.btnFieldsClick(Sender: TObject);
var
  i: integer;
  s, LText, LPre: string;
begin
  LPre := Trim(edtFields.Text);
  if (LPre <> '') and (LPre[Length(LPre)] <> '.') then
    LPre := LPre + '.';
  for i := 0 to memFields.Lines.Count - 1 do begin
    s := Trim(memFields.Lines[i]);
    if s <> '' then
      LText := LText + ', ' + LPre + s;
  end;
  s := copy(LText, 3, MaxInt);
  if cbFields.IsChecked then
    Clipboard.AsText := LText
  else
    memFields.Lines.Text := LText;
end;

procedure TfrmSqlOpen.btnOpenClick(Sender: TObject);
var
  LData: TDataset;
  LSql: string;
  LRow: integer;
  c, t1, t2: int64;
begin
  QueryPerformanceFrequency(c);
  QueryPerformanceCounter(t1);
  LSql := memSql.Lines.Text;
  LSql := dataMain.ReplaceSqlFunction(LSql);
  dataMain.QueryPrepare(FConName, 'Select', LSql);
  LData := dataMain.QueryOpen(FConName, 'Select');
  TBafCodeUtils.SqlOpen(sgSelect, LData, StrToIntDef(cmbSelect.Text, 42));
  dataMain.QueryClose(FConName, 'Select');
  QueryPerformanceCounter(t2);
  lblSelectOpen2.Text := FormatFloat('0.000', (t2 - t1) / c);
  Application.ProcessMessages;
  AddHist(Trim(LSql));
  TabControl1.ActiveTab := tabSelect;
end;

procedure TfrmSqlOpen.btnResultFieldsClick(Sender: TObject);
var
  i: integer;
  s, LText, LPre: string;
begin
  LPre := Trim(edtResultFields.Text);
  if (LPre <> '') and (LPre[Length(LPre)] <> '.') then
    LPre := LPre + '.';
  for i := 0 to memResultFields.Lines.Count - 1 do begin
    s := Trim(memResultFields.Lines[i]);
    if s <> '' then
      LText := LText + ', ' + LPre + s;
  end;
  s := copy(LText, 3, MaxInt);
  if cbResultFields.IsChecked then
    Clipboard.AsText := LText
  else
    memResultFields.Lines.Text := LText;
end;

procedure TfrmSqlOpen.btnSqlHistClick(Sender: TObject);
var
  LItem: THistItem;
  ix: integer;
begin
  if lbHist.ItemIndex > -1 then begin
    LItem := (lbHist.Items.Objects[lbHist.ItemIndex] as THistItem);
    memSql.Lines.Text := LItem.FSql;
    ix := cmbDatabase.Items.IndexOf(LItem.FCon);
    if ix >= 0 then
       cmbDatabase.ItemIndex := ix;
  end;
end;

procedure TfrmSqlOpen.btnSqlLogClick(Sender: TObject);
var
  sl: TStringList;
  i: integer;
  s, t: string;
begin
  sl := TStringList.Create;
  try
    for i := 0 to memSql.Lines.Count - 1 do begin
      s := memSql.Lines[i];
      if copy(s, 1, 4) = '#sql' then
        s := copy(s, 6, MaxInt)
      else
       s := '';
      if i < (memSql.Lines.Count - 1) then
        t := memSql.Lines[i + 1]
      else
        t := '';
      if (Pos(s, t) > 0) and (Pos('-----', t) > 0) then
        s := copy(t, Pos('-----', t) + 5, MaxInt);
      if s <> '' then
        sl.Add(s);
    end; // for i := 0
    memSql.Lines.Assign(sl);
    memSql.SetFocus;
  finally
    sl.Free;
  end;
// procedure TfrmSqlOpen.btnSqlLogClick
end;

procedure TfrmSqlOpen.btnSqlSqlClick(Sender: TObject);
begin
  AddRemovePrefix((Sender as TButton).Text + ' ', memSql);
end;

procedure TfrmSqlOpen.btnTablesRefreshClick(Sender: TObject);
begin
  cmbDatabaseChange(nil);
end;

procedure TfrmSqlOpen.cmbDatabaseChange(Sender: TObject);
begin
  if cmbDatabase.ItemIndex >= 0 then begin
    FConName := cmbDatabase.Items[cmbDatabase.ItemIndex];
    FTables.Clear;
    dataMain.GetTables(FConName, FTables);
    edtSelectChange(nil);
  end;
end;

procedure TfrmSqlOpen.edtSelectChange(Sender: TObject);
var
  i, LLength: integer;
  s: string;
begin
  s := edtSelect.Text;
  LLength := Length(s);
  if LLength = 0 then
    lbSelect.Items.Assign(FTables)
  else begin
    lbSelect.Items.Clear;
    for i := 0 to FTables.Count - 1 do begin
      if AnsiCompareText(copy(FTables[i], 1, LLength), s) = 0 then
        lbSelect.Items.Add(FTables[i]);
    end;
  end;
end;

procedure TfrmSqlOpen.FormCreate(Sender: TObject);
begin
  FTables := TStringList.Create;
end;

procedure TfrmSqlOpen.FormDestroy(Sender: TObject);
begin
  FreeAndNil(FTables);
end;

procedure TfrmSqlOpen.Init;
begin
  btnTablesRefresh.Text := #$f01e;
  btnTablesRefresh.BringToFront;
  cmbDatabase.Items.Assign(dataMain.GetDbConnectionList);
  cmbDatabase.ItemIndex := 0;
  TabControl1.ActiveTab := tabSelect;
end;

procedure TfrmSqlOpen.lbHistChange(Sender: TObject);
begin
  if lbHist.ItemIndex > -1 then
    memHist.Lines.Text := (lbHist.Items.Objects[lbHist.ItemIndex] as THistItem).FSql;
end;

procedure TfrmSqlOpen.lbSelectChange(Sender: TObject);
var
  LSql: string;
  LData: TDataset;
  i: integer;
begin
  if TabControl1.ActiveTab = tabData then begin
    LSql :=  dataMain.GetLimitedSelect(FConName, lbSelect.Items[lbSelect.ItemIndex], 42);
    LSql := dataMain.ReplaceSqlFunction(LSql);
    dataMain.QueryPrepare(FConName, 'Select', LSql);
    LData := dataMain.QueryOpen(FConName, 'Select');
    TBafCodeUtils.SqlOpen(sgData, LData, 42);
    dataMain.QueryClose(FConName, 'Select');
  end
  else if TabControl1.ActiveTab = tabFields then begin
    LSql :=  dataMain.GetLimitedSelect(FConName, lbSelect.Items[lbSelect.ItemIndex], 42);
    LSql := dataMain.ReplaceSqlFunction(LSql);
    dataMain.QueryPrepare(FConName, 'Select', LSql);
    LData := dataMain.QueryOpen(FConName, 'Select');
    memFields.Lines.Clear;
    for i := 0 to LData.FieldCount - 1 do
      memFields.Lines.Add(LData.Fields[i].FieldName);
    dataMain.QueryClose(FConName, 'Select');
  end
  else if TabControl1.ActiveTab = tabResultFields then begin
    memResultFields.Lines.Clear;
    for i := 1 to sgSelect.ColumnCount - 1 do
      memResultFields.Lines.Add(sgSelect.Columns[i].Header);
  end;
end;

procedure TfrmSqlOpen.lbSelectDblClick(Sender: TObject);
begin
  if lbSelect.ItemIndex >= 0 then begin
    memSql.Lines.Text := dataMain.GetLimitedSelect(FConName,
        lbSelect.Items[lbSelect.ItemIndex], StrToIntDef(cmbSelect.Text, 42));
    Application.ProcessMessages;
    btnOpenClick(nil);
  end;
end;

procedure TfrmSqlOpen.memSqlKeyDown(Sender: TObject; var Key: Word;
  var KeyChar: Char; Shift: TShiftState);
begin
  if (Key in [vkF5, vkF9]) or ((key = vkReturn) and (ssCtrl in Shift)) then
    btnOpenClick(nil);
end;

procedure TfrmSqlOpen.SetFontSize(ASize: single);
begin
  memSql.TextSettings.Font.Size := ASize;
  memFields.TextSettings.Font.Size := ASize;
  memHist.TextSettings.Font.Size := ASize;
end;

end.
