unit fClientForm;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, StdCtrls,
  uROClient, uROClientIntf, uRORemoteService, uROBinMessage, Grids, DBGrids,
  ExtCtrls, uROPoweredByRemObjectsButton, uDAPoweredByDataAbstractButton,
  ComCtrls;

type
  TClientForm = class(TForm)
    Panel1: TPanel;
    DAPoweredByDataAbstractButton1: TDAPoweredByDataAbstractButton;
    btExecute: TButton;
    btClear: TButton;
    Panel2: TPanel;
    Splitter1: TSplitter;
    dgResults: TDBGrid;
    tvTemplates: TTreeView;
    Splitter2: TSplitter;
    Panel3: TPanel;
    edQuery: TMemo;
    edDescription: TMemo;
    procedure FormCreate(Sender: TObject);
    procedure tvTemplatesChange(Sender: TObject; Node: TTreeNode);
    procedure btClearClick(Sender: TObject);
    procedure btExecuteClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  ClientForm: TClientForm;

implementation

uses
  StrUtils, fClientDataModule, uStatement, DataAbstract4_Intf;
  
{$R *.dfm}

procedure TClientForm.btClearClick(Sender: TObject);
begin
  edQuery.Clear;
  if (ClientDataModule.tblQueryResults.Active) then
    ClientDataModule.tblQueryResults.Close;
end;

procedure TClientForm.btExecuteClick(Sender: TObject);
var
  tri: TableRequestInfoV6;
  triArr: array of TableRequestInfo;
begin
  if Trim(edQuery.Text) = '' then begin
    ShowMessage('The query is empty. Please select one of the query templates or write your own.');
    exit;
  end;

  tri := TableRequestInfoV6.Create();
  with tri do begin
    Sql := edQuery.Text;
    IncludeSchema := true;
    MaxRecords := 1000;
  end;

  try
    with ClientDataModule do begin
      tblQueryResults.ClearRows(true);
      dgResults.Columns.Clear;
      SetLength(triArr, 1);
      triArr[0] := tri;
      RemoteDataAdapter.Fill([tblQueryResults], triArr);
    end;
  except
    on E: Exception do ShowMessage('Error executing query: ' + E.Message);
  end;
end;

procedure TClientForm.FormCreate(Sender: TObject);
var
  r, n: TTreeNode;
  s: TStatement;
begin
  Application.Title := ClientForm.Caption;
  tvTemplates.Items.BeginUpdate;
  r := tvTemplates.Items.Add(nil, '1.SELECT');
    s := TStatement.Create(
      'Simple select all fields (using *)',
      'SELECT * FROM clients',
      'Select all fields for all clients'
    );
    n := tvTemplates.Items.AddChild(r, s.Name);
    n.Data := s;

    s := TStatement.Create(
      'Simple select with specified fields',
      'SELECT clientid, clientname, contactaddress '#13'FROM clients',
      'Select several fields for clients'
    );
    n := tvTemplates.Items.AddChild(r, s.Name);
    n.Data := s;

  r := tvTemplates.Items.Add(r, '2.WHERE');
    s := TStatement.Create(
      'IN (1, 2, .., n)',
      'SELECT productid, productname FROM products '#13'WHERE productcode in (''ASUS.M2NE'', ''ASUS.M3A'', ''ASUS.M2NMXSEP'')',
      'Select all product codes in the specified set'
    );
    n := tvTemplates.Items.AddChild(r, s.Name);
    n.Data := s;

    s := TStatement.Create(
      'Unary Condition',
      'SELECT * '#13'FROM clients '#13'WHERE contactaddress IS NOT NULL',
      'Select all clients that have address info'
    );
    n := tvTemplates.Items.AddChild(r, s.Name);
    n.Data := s;

    s := TStatement.Create(
      'Like Condition',
      'SELECT * '#13'FROM clients '#13'WHERE clientname LIKE ''A%''',
      'Like Condition'
    );
    n := tvTemplates.Items.AddChild(r, s.Name);
    n.Data := s;

  r := tvTemplates.Items.Add(r, '3.JOINS');
    s := TStatement.Create(
      'With join condition in WHERE clause',
      'SELECT p.productId, p.productname, p.productgroup, s.sellername '#13'FROM products p, sellers s '#13'WHERE s.sellerid = p.productseller '#13'AND p.productgroup = 46',
      'Inner join sample'
    );
    n := tvTemplates.Items.AddChild(r, s.Name);
    n.Data := s;

    s := TStatement.Create(
      'Inner',
      'SELECT p.productId, p.productname, p.productgroup, s.sellername '#13'FROM products p '#13'INNER JOIN sellers s on s.sellerid = p.productseller '#13'WHERE p.productgroup = 46',
      'Inner join sample'
    );
    n := tvTemplates.Items.AddChild(r, s.Name);
    n.Data := s;

    s := TStatement.Create(
      'Left Outer',
      'SELECT p.productId, p.productname, p.productgroup, s.sellername '#13'FROM products p '#13'LEFT OUTER JOIN sellers s on s.sellerid = p.productseller '#13'WHERE p.productgroup = 46',
      'Left outer join sample'
    );
    n := tvTemplates.Items.AddChild(r, s.Name);
    n.Data := s;

    s := TStatement.Create(
      'Right Outer',
      'SELECT p.productId, p.productname, p.productgroup, s.sellername '#13'FROM products p '#13'RIGHT OUTER JOIN sellers s on s.sellerid = p.productseller '#13'WHERE s.sellername in (''IBM'', ''MSI'', ''ASUS'')',
      'Right outer join sample'
    );
    n := tvTemplates.Items.AddChild(r, s.Name);
    n.Data := s;

    s := TStatement.Create(
      'Inner on 3 Tables',
      'SELECT c.clientid, c.clientname, o.orderdate, d.total '#13'FROM clients c '#13'INNER JOIN orders o on c.clientid = o.client '#13'INNER JOIN orderdetails d on o.orderid = d.[order] '#13'WHERE d.total > 5000',
      'Inner Join on 3 tables'
    );
    n := tvTemplates.Items.AddChild(r, s.Name);
    n.Data := s;

  r := tvTemplates.Items.Add(r, '4.ORDER BY');
    s := TStatement.Create(
      'Descending',
      'SELECT * FROM clients '#13'ORDER BY clientname DESC',
      'Select all clients order by their names descending'
    );
    n := tvTemplates.Items.AddChild(r, s.Name);
    n.Data := s;

    s := TStatement.Create(
      'Ascending',
      'SELECT * FROM clients '#13'ORDER BY clientname ASC',
      'Select all clients order by their names ascending'
    );
    n := tvTemplates.Items.AddChild(r, s.Name);
    n.Data := s;

    s := TStatement.Create(
      'By field position',
      'SELECT clientid, clientname FROM clients '#13'ORDER BY 2 DESC',
      'Select all clients order by their names ascending'
    );
    n := tvTemplates.Items.AddChild(r, s.Name);
    n.Data := s;

    s := TStatement.Create(
      'Order by several fields',
      'SELECT product, price, quantity, total '#13'FROM orderdetails '#13'ORDER BY price, provider DESC',
      'Select all orders order by unit price and provider descending'
    );
    n := tvTemplates.Items.AddChild(r, s.Name);
    n.Data := s;

    s := TStatement.Create(
      'Order on joined tables',
      'SELECT p.productid, p.productname, p.productgroup, s.sellername '#13'FROM products p '#13'INNER JOIN sellers s ON s.sellerid = p.productseller '#13'WHERE p.productgroup = 46 '#13'ORDER BY p.productname, s.sellername DESC',
      'Select all orders order by id and unit price descending'
    );
    n := tvTemplates.Items.AddChild(r, s.Name);
    n.Data := s;
    tvTemplates.Items.EndUpdate;
    // TODO: until bug in DynamicRequest fixed
    //ClientDataModule.Login;
end;

procedure TClientForm.tvTemplatesChange(Sender: TObject; Node: TTreeNode);
var
  s: TStatement;
  temp: string;
  p: integer;
begin
  if Assigned(Node.Data) then begin
    s := TStatement(Node.Data);
    edDescription.Text := s.Description;
    edQuery.Clear;
    temp := s.SQL;
    repeat
      p := Pos(#13, temp);
      if p > 0 then begin
        edQuery.Lines.Append(LeftStr(temp, p - 1));
        temp := RightStr(temp, Length(temp) - p);
      end
      else begin
        edQuery.Lines.Append(temp);
        temp := '';
      end;
    until temp = '';
  end;
end;

end.