queryachievement.pas

餐饮管理源代码 使用BDE Administrator在本地机器上添加一个Paradox数据库别名命名为WorkData

unit queryAchievement;

interface

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

type
  TfrmQueryArchievement = class(TForm)
    GroupBox1: TGroupBox;
    GroupBox4: TGroupBox;
    DBNavigator1: TDBNavigator;
    GroupBox6: TGroupBox;
    asc: TRadioButton;
    desc: TRadioButton;
    GroupBox7: TGroupBox;
    DBGrid1: TDBGrid;
    employeeId: TComboBox;
    Label1: TLabel;
    Label2: TLabel;
    GroupBox2: TGroupBox;
    lowTime: TDateTimePicker;
    highTime: TDateTimePicker;
    Label3: TLabel;
    GroupBox3: TGroupBox;
    lowMoney: TEdit;
    highMoney: TEdit;
    Label4: TLabel;
    Button1: TButton;
    Button2: TButton;
    sqlArchievement: TQuery;
    DataSource1: TDataSource;
    employeeName: TEdit;
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button2Click(Sender: TObject);
    procedure employeeIdChange(Sender: TObject);
    procedure ascClick(Sender: TObject);
    procedure descClick(Sender: TObject);
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
    s:string;
    function isMoney(s:string):boolean;
  public
    { Public declarations }
  end;

var
  frmQueryArchievement: TfrmQueryArchievement;

implementation

uses dataModule;

{$R *.dfm}

procedure TfrmQueryArchievement.FormCreate(Sender: TObject);
begin
   {处理初始查询并显示dbgrid1}
   s:='select 员工编号,sum(金额) as 销售额 from mytransaction '+
      'group by 员工编号 ';
   with sqlArchievement do
   begin
      close;
      sql.Clear;
      sql.Add(s);
      open;
   end;
   {处理初始时间和金额}
   lowTime.Date:=strToDate('1800-1-1');
   highTime.Date:=now;
   lowMoney.Text:='0';
   highMoney.Text:='99999999';
   {处理"员工编号"组合框}
   employeeId.Items.Add('');
   dm:=Tdm.create(self);
   with dm.tblEmployee do
   begin
      open;
      while not eof do
      begin
         employeeId.Items.Add(fieldByName('员工编号').AsString);
         next;
      end;
      close;
   end;
   {初始化输入焦点}
   activeControl:=employeeId;
end;

procedure TfrmQueryArchievement.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
   dm.Free;
end;

procedure TfrmQueryArchievement.Button2Click(Sender: TObject);
begin
   employeeId.ItemIndex:=0;
   employeeName.Text:='';
   lowTime.Date:=strToDate('1800-1-1');
   highTime.Date:=now;
   lowMoney.Text:='0';
   highMoney.Text:='99999999';

end;

procedure TfrmQueryArchievement.employeeIdChange(Sender: TObject);
begin
   if employeeId.ItemIndex>0 then//选择了一个员工编号
   begin
      with dm.tblEmployee do
      begin
         close;
         open;
         while not eof do
         begin
            if fieldByName('员工编号').AsString=employeeId.Text then
            begin
               employeeName.Text:=fieldByName('员工姓名').AsString;
               close;
               exit;
            end;
            next;
         end;
         close;
      end;
    end
    else
      employeeName.Text:='';
end;

procedure TfrmQueryArchievement.ascClick(Sender: TObject);
begin
   with sqlArchievement do
   begin
   Close;
   SQL.Clear;
   SQL.Add(s+' order by 员工编号 asc');
   try
      Open;
   except
      ExecSQL;
   end;
   end;
end;

procedure TfrmQueryArchievement.descClick(Sender: TObject);

begin

   with sqlArchievement do
   begin
   Close;
   SQL.Clear;
   SQL.Add(s+' order by 员工编号 desc');
  try
      Open;
   except
      ExecSQL;
   end;
   end;
end;

procedure TfrmQueryArchievement.Button1Click(Sender: TObject);
begin
   s:='select 员工编号,sum(金额) '+
      'from mytransaction ';
   {处理"员工编号"}
   if employeeId.Items[employeeId.itemIndex]<>'' then
      s:=s+'where 员工编号='+employeeId.Items[employeeId.itemIndex];
   {处理"查询时期"}
   if employeeId.Items[employeeId.itemIndex]='' then
      s:=s+' where '
   else s:=s+' and ';
   s:=s+'日期>='''+formatDateTime('mm''/''dd''/''yyyy',lowTime.Date)+'''';
   s:=s+' and 日期<='''+formatDateTime('mm''/''dd''/''yyyy',highTime.Date)+'''';
   {处理"查询金额"}
   s:=s+' group by 员工编号 having sum(金额)>=';
   if isMoney(lowMoney.Text) then
      s:=s+lowMoney.Text
   else if lowMoney.Text='' then
      s:=s+'0'
   else
   begin
      showMessage('输入金额错误！');
      activecontrol:=lowMoney;
      exit;
   end;
   s:=s+' and sum(金额)<=';
   if isMoney(highMoney.Text) then
      s:=s+highMoney.Text
   else if highMoney.Text='' then
      s:=s+'99999999'
   else
   begin
      showMessage('输入金额错误！');
      activecontrol:=highMoney;
      exit;
   end;
   {查询"员工业绩"}
  with sqlArchievement do
   begin
      close;
      sql.Clear;
      sql.Add(s);
      open;
   end;
end;

function TfrmQueryArchievement.isMoney(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) and (s[i]<>'.')then
      begin
         result:=false;
         exit;
      end;
      i:=i+1;
   end;
   result:=true;
end;

end.