queryarchievementunit.~pas

一个很不错的系统! 查询模块方面是我编的最好的一次.几乎完美!!!!.

unit queryarchievementunit;

interface

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

type
  Tfrm_queryarchievement = class(TForm)
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    employeeId: TComboBox;
    GroupBox2: TGroupBox;
    Label3: TLabel;
    lowTime: TDateTimePicker;
    highTime: TDateTimePicker;
    GroupBox3: TGroupBox;
    Label4: TLabel;
    lowMoney: TEdit;
    highMoney: TEdit;
    b_query: TButton;
    b_clear: TButton;
    employeeName: TEdit;
    GroupBox6: TGroupBox;
    asc: TRadioButton;
    desc: TRadioButton;
    GroupBox4: TGroupBox;
    DBNavigator1: TDBNavigator;
    GroupBox7: TGroupBox;
    DBGrid1: TDBGrid;
    q_archievement: TADOQuery;
    ds_archievement: TDataSource;
    t_employee: TADOTable;
    procedure FormCreate(Sender: TObject);
    procedure employeeIdChange(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure b_queryClick(Sender: TObject);
    procedure b_clearClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure ascClick(Sender: TObject);
  private
    { Private declarations }
    str:string;
    function ismoney(s:string):boolean;

  public
    { Public declarations }
 
  end;

var
  frm_queryarchievement: Tfrm_queryarchievement;

implementation
  uses datamoduleunit;
{$R *.dfm}

function  Tfrm_queryarchievement.ismoney(s:string):boolean;
 var i:integer;
begin
  i:=1;
   while i<length(s) do
    begin
         if (s[i]<>'.') and((ord(s[i])<48) or (ord(s[i])>57)) then
         begin
         result:=false;
         exit;
        end;
     i:=i+1;
    end;
   result:=true;
end;

procedure Tfrm_queryarchievement.FormCreate(Sender: TObject);
var i:integer;
begin
i:=1;
employeeid.Items.Add('');
t_employee.open;
 while i<t_employee.RecordCount do
  begin
   employeeid.Items.Add(t_employee.FieldValues['员工编号']);
   t_employee.Next;
   i:=i+1;
  end;
   
end;

procedure Tfrm_queryarchievement.employeeIdChange(Sender: TObject);
begin
 if employeeid.ItemIndex=0 then
  begin
    employeename.Text:='';
    exit;
  end;
  with t_employee do
   begin
    close;
    filter:='员工编号='''+employeeid.Text+'''';
    filtered:=true;
    open;
    if recordcount=1 then
      employeename.Text:=FieldValues['员工姓名'];
    end;
end;
procedure Tfrm_queryarchievement.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
 t_employee.Close;
 q_archievement.Close;
end;
procedure Tfrm_queryarchievement.b_queryClick(Sender: TObject);
var low,high,s:string;
begin
s:='select 员工编号,sum(金额) as 消售总金额';
 s:=s+' from mytransaction ';
 if employeeid.Text<>''
   then  s:=s+' where 员工编号='+employeeid.Text;
 if employeeid.Text=''
  then   s:=s+' where'
   else  s:=s+' and';
   low:=formatdatetime('mm''/''dd''/''yyyy',lowtime.Date);
   high:=formatdatetime('mm''/''dd''/''yyyy',hightime.Date);
  s:=s+'  日期>='''+low+'''';
  s:=s+' and 日期<='''+high+'''';
  s:=s+' group by 员工编号 having sum(金额)>=';
  if  lowmoney.Text=''
     then s:=s+'0'
     else if ismoney(lowmoney.Text)=true
             then s:=s+lowmoney.Text
              else
              begin
               showmessage('输入金额错误,请重新输入');
               activecontrol:=lowmoney;
               exit;
              end;

  if highmoney.Text=''
     then s:=s+ ' and sum(金额)<=999999'
     else if ismoney(highmoney.Text)=true
           then s:=s+' and sum(金额)<='+highmoney.Text
           else
              begin
               showmessage('输入金额错误,请重新输入');
               activecontrol:=highmoney;
               exit;
              end;
q_archievement.Close;
 q_archievement.SQL.Clear;
 q_archievement.SQL.Add(s);
 try
   q_archievement.Open;
 except
   q_archievement.ExecSQL;
 end;
 str:=s;
 end;

procedure Tfrm_queryarchievement.b_clearClick(Sender: TObject);
begin
 employeeid.ItemIndex:=0;
 employeename.Text:='';
 lowtime.Date:=strtodate('1983-6-27');
 hightime.Date:=now;
 lowmoney.Text:='';
 highmoney.Text:='';
 if asc.Checked then asc.Checked:=false;
 if desc.Checked then desc.Checked:=false;
 
end;

procedure Tfrm_queryarchievement.FormShow(Sender: TObject);
begin
frm_queryarchievement.b_clearClick(nil);
end;

procedure Tfrm_queryarchievement.ascClick(Sender: TObject);
 var s:string;
begin
   q_archievement.Close;
   q_archievement.SQL.Clear;
   s:=str;
 if sender=asc
  then  q_archievement.SQL.Add(s+' order by 员工编号 asc')
  else  q_archievement.SQL.Add(s+' order by 员工编号 desc');
  q_archievement.SQL.Add(s);
   try
    q_archievement.Open;
   except
    q_archievement.ExecSQL;
   end;
end;

end.