consumptionsumunit.~pas

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

unit consumptionsumunit;

interface

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

type
  Tfrm_consumptionsum = class(TForm)
    GroupBox1: TGroupBox;
    Label1: TLabel;
    memberId: TEdit;
    GroupBox2: TGroupBox;
    Label2: TLabel;
    lowMoney: TEdit;
    highMoney: TEdit;
    b_query: TButton;
    b_clear: TButton;
    GroupBox3: TGroupBox;
    DBGrid1: TDBGrid;
    GroupBox4: TGroupBox;
    DBGrid2: TDBGrid;
    ADOConnection1: TADOConnection;
    q_sum: TADOQuery;
    q_info: TADOQuery;
    ds_sum: TDataSource;
    ds_info: TDataSource;
    midname: TPanel;
    DBGrid3: TDBGrid;
    t_member: TADOTable;
    ds_member: TDataSource;
    procedure memberIdDblClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure DBGrid3DblClick(Sender: TObject);
    procedure b_queryClick(Sender: TObject);
    procedure b_clearClick(Sender: TObject);
    procedure memberIdChange(Sender: TObject);
    procedure lowMoneyChange(Sender: TObject);
    procedure highMoneyChange(Sender: TObject);
    procedure memberIdKeyPress(Sender: TObject; var Key: Char);
    procedure lowMoneyKeyPress(Sender: TObject; var Key: Char);
    procedure highMoneyKeyPress(Sender: TObject; var Key: Char);
  private
    { Private declarations }
    function isint(s:string):boolean;
    function ismoney(s:string):boolean;
  public
    { Public declarations }



  end;

var
  frm_consumptionsum: Tfrm_consumptionsum;

implementation

{$R *.dfm}
function tfrm_consumptionsum.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;
function tfrm_consumptionsum.isint(s:string):boolean;
var i:integer;
begin
 i:=1;
  while i<length(s) do
   begin
    if (ord(s[i])<48) or (ord(s[i])>57) then
     begin
      result:=false;
      exit;
     end;
     i:=i+1;
   end;
     result:=true;


end;

procedure Tfrm_consumptionsum.memberIdDblClick(Sender: TObject);
begin
midname.Top:=17;
midname.Left:=103;
t_member.Open;
dbgrid3.Align:=alclient;
midname.Visible:=true;
end;

procedure Tfrm_consumptionsum.FormShow(Sender: TObject);
begin
midname.Visible:=false;

end;

procedure Tfrm_consumptionsum.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
t_member.Close;
end;

procedure Tfrm_consumptionsum.DBGrid3DblClick(Sender: TObject);
begin
memberid.Text:=inttostr(t_member.FieldValues['会员编号']);
midname.Visible:=false;
end;

procedure Tfrm_consumptionsum.b_queryClick(Sender: TObject);
var s1,s2:string;
begin
 s1:='select 会员编号,金额,sum(金额) as 消费总金额'+' from mytransaction group by 会员编号 having ';
 s2:='select f.商品编号,商品姓名 as 商品名称,单价 as 原价,单位,数量 as 购买数量,金额 as 交易金额,my.日期 as 消费日期 ';
 s2:=s2+' from mytransaction as my,foods as f,member as m ';
 s2:=s2+' where my.商品编号=f.商品编号 and my.会员编号=m.会员编号';
 if (isint(memberid.Text)=true) and (length(memberid.Text)=5) then
  begin
   s1:='select 会员编号,sum(金额) as 消费总金额'+'  from mytransaction where 会员编号='+memberid.Text+
    ' group by 会员编号  having';
    s2:=s2+' and  my.会员编号='+memberid.Text;
  end
  else
    if  memberid.Text=''
     then s1:='select 会员编号,sum(金额) as 消费总金额 from mytransaction group by 会员编号  having'
      else
       begin
       showmessage('你输入的会员编号不正确');
       memberid.Text:='';
       activecontrol:=memberid;
       exit;
       end;
    if (ismoney(lowmoney.Text)) and (lowmoney.Text<>'')
      then begin
            s1:=s1+' sum(金额)>='+lowmoney.Text;
            s2:=s2+' and 金额>='+lowmoney.Text;
           end
         else
          begin
            s1:=s1+' sum(金额)>=0';
           // s2:=s2+' and 金额>=0';
          end;
    if (ismoney(highmoney.Text)) and (highmoney.Text<>'')
      then
         begin
            s1:=s1+' and sum(金额)<='+highmoney.Text;
            s2:=s2+' and 金额<='+highmoney.Text;
         end
          else
           begin
            s1:=s1+' and sum(金额)<=999999';
            //s2:=s2+' and 金额<=999999';
           end;

 q_sum.Close;
 q_sum.SQL.Clear;
 q_sum.SQL.Add(s1);
 try
   q_sum.Open;
 except
   q_sum.ExecSQL;
  end;
   q_info.Close;
 q_info.SQL.Clear;
 q_info.SQL.Add(s2);
 try
   q_info.Open;
 except
   q_info.ExecSQL;
  end;
 memberid.Enabled:=true;
 lowmoney.Enabled:=true;
highmoney.Enabled:=true;

end;

procedure Tfrm_consumptionsum.b_clearClick(Sender: TObject);
begin
memberid.Text:='';
lowmoney.Text:='0';
 highmoney.Text:='999999';
end;

procedure Tfrm_consumptionsum.memberIdChange(Sender: TObject);
begin
//lowmoney.Enabled:=false;
//highmoney.Enabled:=false;
end;

procedure Tfrm_consumptionsum.lowMoneyChange(Sender: TObject);
begin
//memberid.Clear;
//memberid.Enabled:=false;
end;

procedure Tfrm_consumptionsum.highMoneyChange(Sender: TObject);
begin
//memberid.Enabled:=false;
end;

procedure Tfrm_consumptionsum.memberIdKeyPress(Sender: TObject;
  var Key: Char);
begin
if key=chr(vk_return) then frm_consumptionsum.b_queryClick(nil);
end;

procedure Tfrm_consumptionsum.lowMoneyKeyPress(Sender: TObject;
  var Key: Char);
begin
if key=chr(vk_return) then frm_consumptionsum.b_queryClick(nil);
end;

procedure Tfrm_consumptionsum.highMoneyKeyPress(Sender: TObject;
  var Key: Char);
begin
if key=chr(vk_return) then frm_consumptionsum.b_queryClick(nil);
end;

end.