consumptionsum.pas

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

unit consumptionSum;

interface

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

type
  TfrmConsumptionSum = class(TForm)
    GroupBox1: TGroupBox;
    Label1: TLabel;
    memberId: TEdit;
    GroupBox2: TGroupBox;
    lowMoney: TEdit;
    highMoney: TEdit;
    Label2: TLabel;
    Button2: TButton;
    Button3: TButton;
    GroupBox3: TGroupBox;
    DBGrid1: TDBGrid;
    GroupBox4: TGroupBox;
    DBGrid2: TDBGrid;
    sqlConsumptionInfo: TQuery;
    DataSource1: TDataSource;
    sqlConsumptionSum: TQuery;
    DataSource2: TDataSource;
    procedure FormCreate(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
    s1,s2:string;
    function isMoney(s:string):boolean;
    function isInteger(s:string):boolean;
  public
    { Public declarations }
  end;

var
  frmConsumptionSum: TfrmConsumptionSum;

implementation

uses dataModule,IdGlobal;
{$R *.dfm}

procedure TfrmConsumptionSum.FormCreate(Sender: TObject);
begin
   lowMoney.Text:='0';
   highMoney.Text:='99999999';
   s1:='select 会员编号,sum(金额) as 金额 '+
      'from mytransaction '+
      'group by 会员编号 ';
   with sqlConsumptionSum do
   begin
      close;
      sql.Clear;
      sql.Add(s1);
      open;
   end;
   s2:='select 商品编号,商品名称,单价 AS 原价,单位,数量 AS 购买数量,金额 AS 交易金额,日期 AS 消费日期 '+
      'from mytransaction AS a, goods As b, member AS c '+
      'where a.商品编号=b.商品编号 and a.会员编号=c.会员编号';
   with sqlConsumptionInfo do
   begin
      close;
      sql.Clear;
      sql.Add(s2);
      open;
   end;
   
end;

procedure TfrmConsumptionSum.Button3Click(Sender: TObject);
begin
   memberId.Text:='';
   lowMoney.Text:='0';
   highMoney.Text:='99999999';
end;

procedure TfrmConsumptionSum.Button2Click(Sender: TObject);
begin
   s2:='select 商品编号,商品名称,单价 AS 原价,单位,数量 AS 购买数量,金额 AS 交易金额,日期 AS 消费日期 '+
      'from mytransaction AS a, goods As b, member AS c '+
      'where a.商品编号=b.商品编号 and a.会员编号=c.会员编号';
   {处理会员编号}
   if isInteger(memberId.Text) then
   begin
      s1:='select 会员编号,sum(金额) as 金额 '+
      'from mytransaction '+
      'where 会员编号='+memberId.Text+
      ' group by 会员编号 '+
      'having ';
      s2:=s2+' and a.会员编号='+memberId.Text;
   end
   else if memberId.Text='' then
      s1:='select 会员编号,sum(金额) as 金额 '+
      'from mytransaction '+
      ' group by 会员编号 '+
      'having '
   else
   begin
      showMessage('会员编号输入错误');
      activeControl:=memberId;
      exit;
   end;
   {处理消费金额}
   if isMoney(lowMoney.Text) then
      s1:=s1+'sum(金额)>='+lowMoney.Text
   else
      s1:=s1+'sum(金额)>=0';
   if isMoney(highMoney.Text) then
      s1:=s1+' and sum(金额)<='+highMoney.Text
   else
      s1:=s1+' and sum(金额)>=99999999';
   {执行查询}
   with sqlConsumptionInfo do
   begin
      close;
      sql.Clear;
      sql.Add(s2);
      open;
   end;
   with sqlConsumptionSum do
   begin
      close;
      sql.Clear;
      sql.Add(s1);
      open;
   end; 
end;

function TfrmConsumptionSum.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;

function TfrmConsumptionSum.isInteger(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) then
      begin
         result:=false;
         exit;
      end;
      i:=i+1;
   end;
   result:=true;
end;

end.