unit query;

interface

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

type
  TfrmQuery = class(TForm)
    GroupBox1: TGroupBox;
    Label1: TLabel;
    no: TEdit;
    d1: TDateTimePicker;
    Label2: TLabel;
    d2: TDateTimePicker;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    edit1: TEdit;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    Label6: TLabel;
    edit2: TEdit;
    Label7: TLabel;
    edit3: TEdit;
    Label8: TLabel;
    edit4: TEdit;
    Label9: TLabel;
    Query1: TQuery;
    procedure noKeyPress(Sender: TObject; var Key: Char);
    procedure FormCreate(Sender: TObject);
    procedure edit1KeyPress(Sender: TObject; var Key: Char);
    procedure edit2KeyPress(Sender: TObject; var Key: Char);
    procedure edit3KeyPress(Sender: TObject; var Key: Char);
    procedure edit4KeyPress(Sender: TObject; var Key: Char);
    procedure BitBtn1Click(Sender: TObject);
  private
    { Private declarations }
    {定義文本框的字符計數}
    count,l1,l2,h1,h2:integer;
  public
    { Public declarations }
  end;

var
  frmQuery: TfrmQuery;

implementation

{$R *.dfm}

procedure TfrmQuery.noKeyPress(Sender: TObject; var Key: Char);
begin
   if count<4 then
   begin
      if (ord(key)>=48)and (ord(key)<=57) then
      begin
         count:=count+1;
      end
      else if (ord(key)=8) and (count>0) then
      begin
         count:=count-1;
      end
      else
         key:=chr(27);
   end
   else if (count<=4) and (count>0) and (ord(key)=8) then
   begin
      count:=count-1;
   end
   else
      key:=chr(27);
end;

procedure TfrmQuery.FormCreate(Sender: TObject);
begin
   count:=0;
   l1:=0;
   l2:=0;
   h1:=0;
   h2:=0;
   d1.Date:=strToDateTime('1980-1-1');
   d2.Date:=now;
end;

procedure TfrmQuery.edit1KeyPress(Sender: TObject; var Key: Char);
begin
   if l1<2 then
   begin
      if (ord(key)>=48)and (ord(key)<=57) then
      begin
         l1:=l1+1;
      end
      else if (ord(key)=8) and (l1>0) then
      begin
         l1:=l1-1;
      end
      else
         key:=chr(27);
   end
   else if (l1<=4) and (l1>0) and (ord(key)=8) then
   begin
      l1:=l1-1;
   end
   else
      key:=chr(27);
end;

procedure TfrmQuery.edit2KeyPress(Sender: TObject; var Key: Char);
begin
   if l2<2 then
   begin
      if (ord(key)>=48)and (ord(key)<=57) then
      begin
         l2:=l2+1;
      end
      else if (ord(key)=8) and (l2>0) then
      begin
         l2:=l2-1;
      end
      else
         key:=chr(27);
   end
   else if (l2<=4) and (l2>0) and (ord(key)=8) then
   begin
      l2:=l2-1;
   end
   else
      key:=chr(27);
end;

procedure TfrmQuery.edit3KeyPress(Sender: TObject; var Key: Char);
begin
   if h1<2 then
   begin
      if (ord(key)>=48)and (ord(key)<=57) then
      begin
         h1:=h1+1;
      end
      else if (ord(key)=8) and (h1>0) then
      begin
         h1:=h1-1;
      end
      else
         key:=chr(27);
   end
   else if (l1<=4) and (h1>0) and (ord(key)=8) then
   begin
      h1:=h1-1;
   end
   else
      key:=chr(27);
end;

procedure TfrmQuery.edit4KeyPress(Sender: TObject; var Key: Char);
begin
   if h2<2 then
   begin
      if (ord(key)>=48) and (ord(key)<=57) then
      begin
         h2:=h2+1;
      end
      else if (ord(key)=8) and (h2>0) then
      begin
         h2:=h2-1;
      end
      else
         key:=chr(27);
   end
   else if (l2<=4) and (h2>0) and (ord(key)=8) then
   begin
      h2:=h2-1;
   end
   else
      key:=chr(27);
end;

procedure TfrmQuery.BitBtn1Click(Sender: TObject);
var
   lowTime,highTime:string;
   lowPeriod,highPeriod:string;
   s:string;
begin
   {處理"查詢時間"}
   lowTime:=formatDateTime('mm''/''dd''/''yyyy',d1.Date);
   lowTime:=lowTime+' 00:00:00';
   highTime:=formatDateTime('mm''/''dd''/''yyyy',d2.Date);
   highTime:=highTime+' 23:59:59';
   {處理"通話時長"}
   if edit1.Text<>'' then
      lowPeriod:=edit1.Text+':'
   else
      lowPeriod:='00:';
   if edit2.Text<>'' then
      lowPeriod:=lowPeriod+edit2.Text+':'
   else
      lowPeriod:=lowPeriod+'00:';
   lowPeriod:=lowPeriod+'00';
   if edit3.Text<>'' then
      highPeriod:=edit3.Text+':'
   else
      highPeriod:='23:';
   if edit4.Text<>'' then
      highPeriod:=highPeriod+edit4.Text+':'
   else
      highPeriod:=highPeriod+'59:';
   highPeriod:=highPeriod+'59';
   {生成查詢語句}
   s:='select * from detail ';
   s:=s+'where 開始時間>='''+lowTime+''' '+
      'and 開始時間<='''+highTime+''' '+
      'and 通話時長>='''+lowPeriod+''' '+
      'and 通話時長<='''+highPeriod+''' ';
   if no.Text<>'' then
      s:=s+'and 分機號='''+no.Text+'''';
   {執行查詢}
showMessage(s);
   with query1 do
   begin
      close;
      sql.Clear;
      sql.Add(s);
      try
         open;
      except
         execsql;
      end;
   end;
end;

end.