unitquery.pas

自己设计的一个简易的考勤管理系统

unit UnitQuery;

interface

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

type
  TfrmQuery = class(TForm)
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    Label1: TLabel;
    Label2: TLabel;
    Label5: TLabel;
    Button1: TButton;
    btnPartialQuery: TButton;
    editQueryCondition: TEdit;
    btnExactQuery: TButton;
    ComboStuInfoFields: TComboBox;
    TabSheet2: TTabSheet;
    Label3: TLabel;
    Label6: TLabel;
    Button2: TButton;
    ComboIOFields: TComboBox;
    pID: TPanel;
    Label4: TLabel;
    editIDValue: TEdit;
    pTime: TPanel;
    Label7: TLabel;
    Label8: TLabel;
    DateTimePicker1: TDateTimePicker;
    DateTimePicker2: TDateTimePicker;
    btnIOQuery: TButton;
    DBGrid1: TDBGrid;
    DBGrid3: TDBGrid;
    Button3: TButton;
    procedure query(condition:integer);
    procedure btnExactQueryClick(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure btnPartialQueryClick(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure ComboIOFieldsChange(Sender: TObject);
    procedure btnIOQueryClick(Sender: TObject);
    function getUFieldName():string;
    function getRFieldName():string;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure ComboStuInfoFieldsChange(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure Button3Click(Sender: TObject);
  private
    { Private declarations }
  public
  end;

var
  frmQuery: TfrmQuery;
 // uFieldName:string;
implementation
 uses unitDataModule;
{$R *.dfm}
procedure TfrmQuery.query(condition:integer);
var
fieldName,fieldValue,sqlStr:string;
begin
   fieldName := getUFieldName();
   fieldValue := trim(editQueryCondition.Text);
   with dModule.ADOQU do
   begin
      close;
      sql.Clear;
      if fieldvalue = '' then
        sqlStr := 'select * from users'
      else
            case condition of
            1 :
                sqlStr := 'select * from users where ' + fieldName + '=''' + fieldValue + '''';
            2:
                sqlStr := 'select * from users where ' + fieldName + ' like ''%' + fieldValue + '%''';
            end;
      SQL.add(sqlStr);
      open;
      if recordcount < 0 then
        messagedlg('没有找到满足条件的记录!',mtInformation,mbokcancel,0)
      else
        messagedlg('找到' + inttostr(recordcount) + '条相关记录!',mtInformation,mbokcancel,0);
   end;
end;
function TfrmQuery.getUFieldName():string;
var
index:integer;
begin
   index := comboStuInfoFields.ItemIndex;
   case index of
     0: result := 'id';
     1: result := 'name';
     2: result := 'major';
   end;
end;
function TfrmQuery.getRFieldName():string;
var
index:integer;
begin
   index := comboIOFields.ItemIndex;
   case index of
     0: result := 'timeIn';
     1: result := 'timeOut';
     2: result := 'userid';
   end;
end;

procedure TfrmQuery.btnExactQueryClick(Sender: TObject);
begin
    query(1);
end;

procedure TfrmQuery.Button1Click(Sender: TObject);
begin
    close;
end;

procedure TfrmQuery.btnPartialQueryClick(Sender: TObject);
begin
  query(2);
end;

procedure TfrmQuery.Button2Click(Sender: TObject);
begin
    close;
end;

procedure TfrmQuery.ComboIOFieldsChange(Sender: TObject);
begin
    if (ComboIOFields.ItemIndex=0) or (ComboIOFields.ItemIndex=1) then
        begin
            pTime.Visible := true;
            pID.Visible := false;
        end
    else
        begin
            pID.Visible := true;
            pTime.Visible := false;
        end;
end;

procedure TfrmQuery.btnIOQueryClick(Sender: TObject);
var
rfieldValue,sqlstr : string;
begin
   rfieldValue := trim(editIDValue.Text);
   case comboIOFields.ItemIndex of
       0:
           sqlstr := 'select * from users,records where id = userid and timeIn between #' + datetostr(datetimepicker1.DateTime) + '# and #' +datetostr(datetimepicker2.DateTime)+ '#';

       1:
           sqlstr := 'select * from users,records where id = userid and timeOut between #' + datetostr(datetimepicker1.DateTime) + '# and #' +datetostr(datetimepicker2.DateTime)+ '#';
       2:
          sqlstr := 'select * from users,records where id = userid and id =''' + rfieldvalue + '''';
       else
          sqlstr := 'select * from users,records where id = userid';
       end;
   with  dModule.ADOQUserRecord do
   begin
        close;
        sql.Clear;
        sql.Add(sqlstr); 
        open;
        if recordcount < 0 then
            messagedlg('没有找到满足条件的记录!',mtInformation,mbokcancel,0)
        else
            messagedlg('找到' + inttostr(recordcount) + '条相关记录!',mtInformation,mbokcancel,0);
   end; 
end;

procedure TfrmQuery.FormClose(Sender: TObject; var Action: TCloseAction);
begin
    action := cafree;
end;

procedure TfrmQuery.ComboStuInfoFieldsChange(Sender: TObject);
begin
    editQueryCondition.Clear;
end;

procedure TfrmQuery.FormShow(Sender: TObject);
begin
    with dModule.ADOQUserRecord do
    begin
        close;
        sql.Clear;
        sql.Add('select * from users,records where id = userid');
        open;
    end;
    with dModule.ADOQU do
    begin
        close;
        sql.Clear;
        sql.Add('select * from users');
        open;
    end;
end;

procedure TfrmQuery.Button3Click(Sender: TObject);
begin
    with dModule.ADOQUserRecord do
    begin
        if messagedlg('你确定要清空登陆历史记录吗？'+ char(13)+char(10) + '建议您备份数据库后再清空!',mtconfirmation,[mbok,mbcancel],0)=mrok then
        begin
            close;
            sql.Clear;
            sql.Add('delete from records');
            execsql;
            messagedlg('删除成功！',mtinformation,[mbok],0);
        end;
    end;
end;
end.