unit QueryUser;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DBTables, DB, StdCtrls, ComCtrls, Grids, DBGrids, ExtCtrls,
  DBCtrls, IdGlobal;

type
  TfrmQueryUser = class(TForm)
    DBGrid1: TDBGrid;
    GroupBox1: TGroupBox;
    Label14: TLabel;
    Label15: TLabel;
    Label2: TLabel;
    Label4: TLabel;
    Label6: TLabel;
    UserID: TEdit;
    UserName: TEdit;
    gender: TComboBox;
    btnQuery: TButton;
    btnClear: TButton;
    tel: TEdit;
    Query1: TQuery;
    DataSource1: TDataSource;
    level: TComboBox;
    GroupBox3: TGroupBox;
    lowJoin: TDateTimePicker;
    highJoin: TDateTimePicker;
    GroupBox2: TGroupBox;
    lowBirthday: TDateTimePicker;
    highBirthday: TDateTimePicker;
    Label1: TLabel;
    Label3: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    GroupBox6: TGroupBox;
    DBNavigator1: TDBNavigator;
    GroupBox4: TGroupBox;
    asc: TRadioButton;
    desc: TRadioButton;
    GroupBox5: TGroupBox;
    id: TRadioButton;
    name: TRadioButton;
    edu: TRadioButton;
    join: TRadioButton;
    procedure btnQueryClick(Sender: TObject);
    procedure btnClearClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure ascClick(Sender: TObject);
    procedure descClick(Sender: TObject);
    procedure idClick(Sender: TObject);
    procedure nameClick(Sender: TObject);
    procedure eduClick(Sender: TObject);
    procedure joinClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);

  private
    { Private declarations }
    sql: string;
  public
    { Public declarations }
    function isInteger(s:string):boolean;
  end;

var
  frmQueryUser: TfrmQueryUser;

implementation

{$R *.dfm}
function TfrmQueryUser.isInteger(s: string): boolean;
var
   i:integer;
begin
   i:=1;
   if length(s)=0 then
   begin
      result:=false;
      exit;
   end;
   while i<=length(s) do
   begin
      if (isNumeric(s[i])=false) then
      begin
         result:=false;
         exit;
      end;
      i:=i+1;
   end;
   result:=true;
end;

procedure TfrmQueryUser.FormCreate(Sender: TObject);
begin
    //顯示所有紀錄
    Query1.Close;
    Query1.SQL.Clear;
    Query1.SQL.Add('select * from UserInfo');
    try
        Query1.Open;
    except

    end;

    //設置輸入焦點
    activeControl := UserId;

    //清空所有輸入
    UserId.Text := '';
    UserName.Text := '';
    gender.ItemIndex := 0;
    lowBirthday.Date := strToDate('1800-1-1');
    highBirthday.Date := now;
    lowJoin.Date := strToDate('1800-1-1');
    highJoin.date := now;
    level.ItemIndex := 0;
    tel.Text := '';
end;

procedure TfrmQueryUser.btnQueryClick(Sender: TObject);
var
    flag: boolean;
    low, high: string;
begin
    //根據輸入的內容查詢
    sql := 'select * from UserInfo ';

    flag := false;
    if isInteger(UserID.Text)  then
    begin
        flag := true;
        sql := sql + 'where 用戶編號='+UserID.Text;
    end;

    //
    if UserName.Text <> '' then
    begin
        if flag then
            sql := sql + ' and 姓名=''' + UserName.Text+''''
        else begin;
            sql := sql + ' where 姓名=''' + UserName.Text+'''';
            flag := true;
        end;
    end;

    //
    if Gender.Text <> '' then
    begin
        if flag then
            sql := sql + ' and 性別=''' + Gender.Text+''''
        else begin;
            sql := sql + ' where 性別=''' + Gender.Text+'''';
            flag := true;
        end;
    end;

    //
   {處理生日}
   low:=formatDateTime('mm''/''dd''/''yyyy',lowbirthday.Date);
   high:=formatDateTime('mm''/''dd''/''yyyy',highbirthday.Date);
   if flag then
      sql:=sql+'and '
   else
      sql:=sql+' where ';
   sql:=sql+'生日<='''+high+''' and 生日>='''+low+'''';

   {處理注冊時間}
   low:=formatDateTime('mm''/''dd''/''yyyy',lowjoin.Date);
   high:=formatDateTime('mm''/''dd''/''yyyy',highjoin.Date);
   sql:=sql+' and '+'注冊時間<='''+high+''' and 注冊時間>='''+low+'''';

   //
    if level.Text <> '' then
        sql := sql + ' and 級別 = ''' + Level.Text+'''';
    if tel.Text<> '' then
        sql := sql + ' and 電話 = ''' + Level.Text+'''';

    //根據字符串sql進行查詢
    Query1.Close;
    Query1.SQL.Clear;
    Query1.SQL.Add(sql);
    try
        Query1.Open;
    except
        Query1.ExecSQL();
    end;
end;

procedure TfrmQueryUser.btnClearClick(Sender: TObject);
begin
    UserId.Text := '';
    UserName.Text := '';
    gender.ItemIndex := 0;
    lowBirthday.Date := strToDate('1800-1-1');
    highBirthday.Date := now;
    lowJoin.Date := strToDate('1800-1-1');
    highJoin.date := now;
    level.ItemIndex := 0;
    tel.Text := '';
end;



procedure TfrmQueryUser.ascClick(Sender: TObject);
var
    context : string;
begin
    if( id.Checked = true )
        then context := '用戶編號'
    else if ( name.Checked = true )
        then context := '姓名'
    else if ( join.Checked )
        then context := '注冊時間'
    else if ( edu.checked )
        then context := '級別';

    //改變查詢語句
    Query1.close;
    Query1.SQL.Clear;
    Query1.SQL.Add('select * from UserInfo order by '+context+' asc');

    //重新查詢
    try
        Query1.Open;
    except
        Query1.ExecSQL;
    end;

end;

procedure TfrmQueryUser.descClick(Sender: TObject);
var
    context : string;
begin
    if( id.Checked = true )
        then context := '用戶編號'
    else if ( name.Checked = true )
        then context := '姓名'
    else if ( join.Checked )
        then context := '注冊時間'
    else if ( edu.checked )
        then context := '級別';

    //改變查詢語句
    Query1.close;
    Query1.SQL.Clear;
    Query1.SQL.Add('select * from UserInfo order by '+context+' desc');

    //重新查詢
    try
        Query1.Open;
    except
        Query1.ExecSQL;
    end;
end;

procedure TfrmQueryUser.idClick(Sender: TObject);
var
    sorttype : string;
begin
    //確定排序方式
    if ( asc.Checked )
        then
        sorttype := 'asc'
    else
        sorttype := 'desc';

    //更新表格

    //改變查詢語句
    Query1.close;
    Query1.SQL.Clear;
    Query1.SQL.Add('select * from UserInfo order by 用戶編號 ' + sorttype);

    //重新查詢
    try
        Query1.Open;
    except
        Query1.ExecSQL;
    end;
end;

procedure TfrmQueryUser.nameClick(Sender: TObject);
var
    sorttype : string;
begin
    //確定排序方式
    if ( asc.Checked )
        then
        sorttype := 'asc'
    else
        sorttype := 'desc';

    //更新表格

    //改變查詢語句
    Query1.close;
    Query1.SQL.Clear;
    Query1.SQL.Add('select * from UserInfo order by 姓名 ' + sorttype);

    //重新查詢
    try
        Query1.Open;
    except
        Query1.ExecSQL;
    end;
end;

procedure TfrmQueryUser.eduClick(Sender: TObject);
var
    sorttype : string;
begin
    //確定排序方式
    if ( asc.Checked )
        then
        sorttype := 'asc'
    else
        sorttype := 'desc';

    //更新表格

    //改變查詢語句
    Query1.close;
    Query1.SQL.Clear;
    Query1.SQL.Add('select * from UserInfo order by 級別 ' + sorttype);

    //重新查詢
    try
        Query1.Open;
    except
        Query1.ExecSQL;
    end;
end;

procedure TfrmQueryUser.joinClick(Sender: TObject);
var
    sorttype : string;
begin
    //確定排序方式
    if ( asc.Checked )
        then
        sorttype := 'asc'
    else
        sorttype := 'desc';

    //更新表格

    //改變查詢語句
    Query1.close;
    Query1.SQL.Clear;
    Query1.SQL.Add('select * from UserInfo order by 注冊時間 ' + sorttype);

    //重新查詢
    try
        Query1.Open;
    except
        Query1.ExecSQL;
    end;
end;

procedure TfrmQueryUser.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
    Query1.Close();
    self.Free;
end;

end.