unit foBafSqlEditor;

interface

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

type
  TfrmSqlEditor = class(TForm)
    pnlDesk: TPanel;
    Panel1: TPanel;
    Splitter1: TSplitter;
    Panel2: TPanel;
    Panel3: TPanel;
    Splitter2: TSplitter;
    Panel4: TPanel;
    memSql: TMemo;
    sgSelect: TStringGrid;
    cmbDatabase: TComboBox;
    Panel5: TPanel;
    Splitter3: TSplitter;
    Panel6: TPanel;
    lbTables: TListBox;
    btnSelectOpen2: TButton;
    cmbSelect2: TComboEdit;
    lblSelectOpen2: TLabel;
    sgFields: TStringGrid;
    CheckColumn1: TCheckColumn;
    StringColumn1: TStringColumn;
    StringColumn2: TStringColumn;
    StringColumn3: TStringColumn;
    StringColumn4: TStringColumn;
    btnSelect: TButton;
    btnAdd: TButton;
    btnAll: TButton;
    btnNone: TButton;
    edtAlias: TEdit;
    Label1: TLabel;
    btnInner: TButton;
    btnLeft: TButton;
    btnTablesRefresh: TButton;
    procedure cmbDatabaseChange(Sender: TObject);
    procedure lbTablesChange(Sender: TObject);
    procedure btnNoneClick(Sender: TObject);
    procedure btnSelectClick(Sender: TObject);
    procedure btnAddClick(Sender: TObject);
    procedure btnSelectOpen2Click(Sender: TObject);
    procedure btnInnerClick(Sender: TObject);
    procedure sgFieldsCellClick(const Column: TColumn; const Row: Integer);
    procedure btnTablesRefreshClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    FConName: string;
    FTableName: string;
    FHasInit: boolean;
    lbTablesChangeLock: boolean;
    function GetColumnNames: string;
  public
    procedure Init;
  end;

var
  frmSqlEditor: TfrmSqlEditor;

implementation

{$R *.fmx}

uses dmMain, uBafCodeUtils, uBafTypes;

{ TForm1 }

procedure TfrmSqlEditor.btnAddClick(Sender: TObject);
var
  sl: TStringList;
  s: string;
begin
  sl := TStringList.Create;
  try
    sl.Assign(memSql.Lines);
    s := sl[memSql.CaretPosition.Line];
    Insert(GetColumnNames, s, memSql.CaretPosition.Pos + 1);
    sl[memSql.CaretPosition.Line] := s;
    memSql.Lines.Assign(sl);
  finally
    sl.Free;
  end;
end;

procedure TfrmSqlEditor.btnNoneClick(Sender: TObject);
var
  i: integer;
begin
  for i := 0 to sgFields.RowCount - 1 do
    sgFields.Cells[0,i] := IfThen(Sender = btnNone, 'false', 'true');
end;

procedure TfrmSqlEditor.btnSelectClick(Sender: TObject);
var
  LSql: string;
begin
  LSql := dataMain.GetLimitedSelect(FConName,
        FTableName + ' ' + edtAlias.Text, StrToIntDef(cmbSelect2.Text, 42));
  memSql.Lines.Text := LSql;
end;

procedure TfrmSqlEditor.btnSelectOpen2Click(Sender: TObject);
var
  LData: TDataset;
  LSql: string;
  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(cmbSelect2.Text, 42));
  dataMain.QueryClose(FConName, 'Select');
  QueryPerformanceCounter(t2);
  lblSelectOpen2.Text := FormatFloat('0.000', (t2 - t1) / c);
end;

procedure TfrmSqlEditor.btnTablesRefreshClick(Sender: TObject);
begin
  dataMain.GetTables(FConName, lbTables.Items);
end;

procedure TfrmSqlEditor.btnInnerClick(Sender: TObject);
var
  sl: TStringList;
  i: integer;
  s: string;
begin
  sl := TStringList.Create;
  try
    sl.Assign(memSql.Lines);
    i := TBafCodeUtils.FindFirstLine(sl, 'from', true);
    if i > -1 then
      sl.Insert(i, '    , ' + GetColumnNames);
    s := IfThen(Sender = btnInner, '  inner join ', '  left outer join ')
        + FTableName + ' ' + edtAlias.Text + ' on';
    i := TBafCodeUtils.FindFirstLine(sl, 'where', true);
    if i > -1 then
      sl.Insert(i, s)
    else begin
      i := TBafCodeUtils.FindFirstLine(sl, 'order by', true);
      if i > -1 then
        sl.Insert(i, s)
      else begin
        i := TBafCodeUtils.FindFirstLine(sl, 'group by', true);
        if i > -1 then
          sl.Insert(i, s)
        else begin
          i := TBafCodeUtils.FindFirstLine(sl, 'limit', true);
          if i > -1 then
            sl.Insert(i, s)
          else
            sl.Add(s);
        end;
      end;
    end;
    memSql.Lines.Assign(sl);
  finally
    sl.Free;
  end;
// procedure TfrmSqlEditor.btnInnerClick
end;

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

procedure TfrmSqlEditor.FormCreate(Sender: TObject);
begin
  btnTablesRefresh.TextSettings.Font.Family := 'Font Awesome 5 Free Regular';
  btnTablesRefresh.Text := #$f01e;
  btnTablesRefresh.BringToFront;
end;

function TfrmSqlEditor.GetColumnNames: string;
var
  i: integer;
  LSelect: boolean;
  s, LPre: string;
begin
  LSelect := false;
  for i := 0 to sgFields.RowCount - 1 do begin
    if AnsiLowerCase(sgFields.Cells[0, i]) = 'true' then begin
      LSelect := true;
      Break;
    end;
  end;
  if LSelect then begin
    LPre := IfThen(Trim(edtAlias.Text) = '', '', edtAlias.Text+ '.');
    for i := 0 to sgFields.RowCount - 1 do begin
      if AnsiLowerCase(sgFields.Cells[0, i]) = 'true' then
        s := s + ', ' + LPre + sgFields.Cells[1, i];
    end;
    result := copy(s, 3, MaxInt);
  end
  else
    result := '*';
// function TfrmSqlEditor.GetColumnNames
end;

procedure TfrmSqlEditor.Init;
begin
  cmbDatabase.Items.Assign(dataMain.GetDbConnectionList);
  cmbDatabase.ItemIndex := 0;
  FHasInit := true;
end;

procedure TfrmSqlEditor.lbTablesChange(Sender: TObject);
var
  LSql: string;
  LDataSet: TDataSet;
  i, LSize: integer;
begin
  if lbTablesChangeLock then
    exit;
  lbTablesChangeLock := true;
  try
    FTableName := lbTables.Items[lbTables.ItemIndex];
    LSql := dataMain.GetLimitedSelect(FConName,
          FTableName, StrToIntDef(cmbSelect2.Text, 42));
    dataMain.QueryPrepare(FConName, 'Tables', LSql);
    LDataSet := dataMain.QueryOpen(FConName, 'Tables');
    sgFields.RowCount := LDataSet.FieldCount;
    for i := 0 to LDataSet.FieldCount - 1 do begin
      sgFields.Cells[0, i] := '';
      sgFields.Cells[1, i] := AnsiLowerCase(LDataSet.Fields[i].FieldName);
      sgFields.Cells[2, i] := BafGetFieldtypeName(LDataSet.Fields[i].DataType);
      LSize := LDataSet.Fields[i].Size;
      sgFields.Cells[3, i] := IfThen(LSize = 0, '', IntToStr(LSize));
      if not LDataSet.Eof then
        sgFields.Cells[4, i] := LDataSet.Fields[i].AsString;
    end;
  finally
    lbTablesChangeLock := false;
  end;
end;

procedure TfrmSqlEditor.sgFieldsCellClick(const Column: TColumn;
  const Row: Integer);
begin
  if Column.Index = 0 then begin
    if sgFields.Cells[0, Row] = 'true' then
      sgFields.Cells[0, Row] := 'false'
    else
      sgFields.Cells[0, Row] := 'true';
  end;
end;

end.
