querysale.pas

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

unit querySale;

interface

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

type
  TfrmQuerySale = class(TForm)
    GroupBox1: TGroupBox;
    Label1: TLabel;
    Label2: TLabel;
    goodsId: TComboBox;
    GroupBox2: TGroupBox;
    Label3: TLabel;
    lowTime: TDateTimePicker;
    highTime: TDateTimePicker;
    GroupBox3: TGroupBox;
    Label4: TLabel;
    lowMoney: TEdit;
    highMoney: TEdit;
    Button1: TButton;
    Button2: TButton;
    goodsName: TEdit;
    GroupBox4: TGroupBox;
    DBNavigator1: TDBNavigator;
    GroupBox6: TGroupBox;
    asc: TRadioButton;
    desc: TRadioButton;
    GroupBox7: TGroupBox;
    DBGrid1: TDBGrid;
    sqlSale: TQuery;
    DataSource1: TDataSource;
    procedure FormCreate(Sender: TObject);
    procedure goodsIdChange(Sender: TObject);
    procedure Button2Click(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
  frmQuerySale: TfrmQuerySale;

implementation

uses dataModule;

{$R *.dfm}

procedure TfrmQuerySale.FormCreate(Sender: TObject);
begin
   {处理初始查询并显示dbgrid1}
   s:='select 商品编号,sum(金额) as 销售额 from mytransaction '+
      'group by 商品编号 ';
   with sqlSale 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';
   {处理"商品编号"组合框}
   goodsId.Items.Add('');
   dm:=Tdm.create(self);
   with dm.tblGoods do
   begin
      open;
      while not eof do
      begin
         goodsId.Items.Add(fieldByName('商品编号').AsString);
         next;
      end;
      close;
   end;
   {初始化输入焦点}
   activeControl:=goodsId;
end;


procedure TfrmQuerySale.goodsIdChange(Sender: TObject);
begin
   if goodsId.ItemIndex>0 then//选择了一个商品编号
   begin
      with dm.tblgoods do
      begin
         close;
         open;
         while not eof do
         begin
            if fieldByName('商品编号').AsString=goodsId.Text then
            begin
               goodsName.Text:=fieldByName('商品名称').AsString;
               close;
               exit;
            end;
            next;
         end;
         close;
      end;
    end
    else
      goodsName.Text:='';
end;

procedure TfrmQuerySale.Button2Click(Sender: TObject);
begin
   goodsId.ItemIndex:=0;
   goodsName.Text:='';
   lowTime.Date:=strToDate('1800-1-1');
   highTime.Date:=now;
   lowMoney.Text:='0';
   highMoney.Text:='99999999';
end;

procedure TfrmQuerySale.ascClick(Sender: TObject);
begin
   with sqlsale do
   begin
   Close;
   SQL.Clear;
   SQL.Add(s+' order by 商品编号 asc');
   try
      Open;
   except
      ExecSQL;
   end;
   end;
end;
procedure TfrmQuerySale.descClick(Sender: TObject);
begin

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

procedure TfrmQuerySale.Button1Click(Sender: TObject);
begin
   s:='select 商品编号,sum(金额) '+
      'from mytransaction ';
   {处理"商品编号"}
   if goodsId.Items[goodsId.itemIndex]<>'' then
      s:=s+'where 商品编号='+goodsId.Items[goodsId.itemIndex];
   {处理"查询日期"}
   if goodsId.Items[goodsId.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 sqlsale do
   begin
      close;
      sql.Clear;
      sql.Add(s);
      open;
   end;
end;
function TfrmQuerySale.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.