unit QueryLeader;

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;
    Label6: TLabel;
    UserID: TEdit;
    UserName: TEdit;
    gender: TComboBox;
    btnQuery: TButton;
    btnClear: TButton;
    tel: TEdit;
    GroupBox3: TGroupBox;
    lowJoin: TDateTimePicker;
    highJoin: TDateTimePicker;
    GroupBox2: TGroupBox;
    lowBirthday: TDateTimePicker;
    highBirthday: TDateTimePicker;
    Label1: TLabel;
    Label3: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    GroupBox7: TGroupBox;
    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);
    procedure FormShow(Sender: TObject);

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

var
  frmQueryUser: TfrmQueryUser;

implementation

uses dataModule;

{$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
    //顯示所有紀(jì)錄
    dm.Query1.Close;
    dm.Query1.SQL.Clear;
    dm.Query1.SQL.Add('select * from leader');
    try
        dm.Query1.Open;
    except

    end;

    //設(shè)置輸入焦點(diǎn)
    activeControl := UserId;

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

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

    flag := false;
    if isInteger(UserID.Text)  then
    begin
        flag := true;
        sql := sql + 'where 編號(hào)='+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+'''';

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

   //

   if tel.Text <> '' then
    begin
        if flag then
            sql := sql + ' and 電話=''' + tel.Text+''''
        else begin;
            sql := sql + ' where 電話=''' + tel.Text+'''';
            flag := true;
        end;
    end;

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

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

    tel.Text := '';
end;



procedure TfrmQueryUser.ascClick(Sender: TObject);
var
    context : string;
begin
    if( id.Checked = true )
        then context := '編號(hào)'
    else if ( name.Checked = true )
        then context := '姓名'
    else if ( join.Checked )
        then context := '任職時(shí)間'
    else if ( edu.checked )
        then context := '級(jí)別';

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

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

end;

procedure TfrmQueryUser.descClick(Sender: TObject);
var
    context : string;
begin
    if( id.Checked = true )
        then context := '編號(hào)'
    else if ( name.Checked = true )
        then context := '姓名'
    else if ( join.Checked )
        then context := '任職時(shí)間'
    else if ( edu.checked )
        then context := '級(jí)別';

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

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

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

    //更新表格

    //改變查詢語(yǔ)句
    dm.Query1.close;
    dm.Query1.SQL.Clear;
    dm.Query1.SQL.Add('select * from leader order by 編號(hào) ' + sorttype);

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

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

    //更新表格

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

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

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

    //更新表格

    //改變查詢語(yǔ)句
    dm.Query1.close;
    dm.Query1.SQL.Clear;
    dm.Query1.SQL.Add('select * from leader order by 級(jí)別 ' + sorttype);

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

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

    //更新表格

    //改變查詢語(yǔ)句
    dm.Query1.close;
    dm.Query1.SQL.Clear;
    dm.Query1.SQL.Add('select * from leader order by 任職時(shí)間 ' + sorttype);

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

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

procedure TfrmQueryUser.FormShow(Sender: TObject);
begin
  //改變查詢語(yǔ)句
    dm.Query1.close;
    dm.Query1.SQL.Clear;
    dm.Query1.SQL.Add('select * from leader order by 編號(hào) asc');

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

end.