querycustomer.pas

可对供应商,顾客,商品,信息进行添加修改查询删除.可随时统计进货量和销售量,同时统计销售额和利润等.

unit QueryCustomer;

interface

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

type
  TQueryCustomerFrm = class(TForm)
    QueryResult_GroupBox: TGroupBox;
    QueryCustomer_StringGrid: TStringGrid;
    QueryCustomer_Query: TQuery;
    QueryCondition_GroupBox: TGroupBox;
    QueryCustomerReturn_BitBtn: TBitBtn;
    QueryCustomerOk_BitBtn: TBitBtn;
    ConpanyName_CheckBox: TCheckBox;
    LinkMan_CheckBox: TCheckBox;
    City_CheckBox: TCheckBox;
    City_ComboBox: TComboBox;
    QueryCount_Edit: TEdit;
    Label1: TLabel;
    ConpanyName_ComboBox: TComboBox;
    LinkMan_ComboBox: TComboBox;
   procedure QueryCustomerReturn_BitBtnClick(Sender: TObject);
    procedure QueryCustomerOk_BitBtnClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  QueryCustomerFrm: TQueryCustomerFrm;

implementation

{$R *.dfm}
procedure TQueryCustomerFrm.QueryCustomerReturn_BitBtnClick(Sender: TObject);
begin
   QueryCustomerFrm.hide;
end;

procedure TQueryCustomerFrm.QueryCustomerOk_BitBtnClick(Sender: TObject);
var querystring:string;
    i:integer;
    tag:integer;
begin
   tag:=0;
   for i:=1 to 100 do
     with QueryCustomer_StringGrid do
           begin
            cells[0,i]:='';
            cells[1,i]:='';
            cells[2,i]:='';
            cells[3,i]:='';
            cells[4,i]:='';
            cells[5,i]:='';
            cells[6,i]:='';
            cells[7,i]:='';
            cells[8,i]:='';
            cells[9,i]:='';
            cells[10,i]:='';
           end;

   //-------查询条件组合的选择------
   if ConpanyName_CheckBox.Checked=true then
      if LinkMan_CheckBox.Checked=true then
         if City_CheckBox.Checked=true  then
            tag:=1 //1，2，3三个条件
         else
            tag:=2 //1，2二个条件
      else
        if City_CheckBox.Checked=true  then
            tag:=7  //1,3 二个条件
        else
           tag:=3  //1 一个条件
    else
      if LinkMan_CheckBox.Checked=true then
           if City_CheckBox.Checked=true  then
             tag:=4  //2，3 二个条件
           else
             tag:=5   //2 一个条件
      else
         if City_CheckBox.Checked=true  then
            tag:=6  //3 一个条件
         else messagebox(QueryCustomerFrm.handle,'请选择查询条件','警告',mb_ok+mb_iconwarning);

  //-------查询条件设定------
  querystring:='select * from customer where';
  case tag of
      1:querystring:=querystring+' CompanyName='+''''+ConpanyName_ComboBox.text+''''+' and LinkMan='+''''+LinkMan_ComboBox.text+''''+' and City='+''''+City_ComboBox.text+'''';
      2:querystring:=querystring+' CompanyName='+''''+ConpanyName_ComboBox.text+''''+' and LinkMan='+''''+LinkMan_ComboBox.text+'''';
      3:querystring:=querystring+' CompanyName='+''''+ConpanyName_ComboBox.text+'''';
      4:querystring:=querystring+' LinkMan='+''''+LinkMan_ComboBox.text+''''+' and City='+''''+City_ComboBox.text+'''';
      5:querystring:=querystring+' LinkMan='+''''+LinkMan_ComboBox.text+'''';
      6:querystring:=querystring+' City='+''''+City_ComboBox.text+'''';
      7:querystring:=querystring+' CompanyName='+''''+ConpanyName_ComboBox.text+''''+' and City='+''''+City_ComboBox.text+'''';
    end;

   with QueryCustomer_Query do
      begin
       close;
       sql.clear;
       sql.Add(querystring);
       open;
       first;
       for i:=1 to recordcount do
        begin
          //-------顾客信息查询------
          with QueryCustomer_StringGrid do
           begin
            cells[0,i]:=fieldbyname('customerID').asstring;
            cells[1,i]:=fieldbyname('CompanyName').asstring;
            cells[2,i]:=fieldbyname('LinkMan').asstring;
            cells[3,i]:=fieldbyname('Address').asstring;
            cells[4,i]:=fieldbyname('City').asstring;
            cells[5,i]:=fieldbyname('Region').asstring;
            cells[6,i]:=fieldbyname('Zipcode').asstring;
            cells[7,i]:=fieldbyname('Tel').asstring;
            cells[8,i]:=fieldbyname('Fax').asstring;
            cells[9,i]:=fieldbyname('Homepage').asstring;
            cells[10,i]:=fieldbyname('customerRemark').asstring;
           end;
         next;
         end;
        QueryCount_Edit.Text:=inttostr(recordcount);
      end;   
end;

procedure TQueryCustomerFrm.FormShow(Sender: TObject);
var City,CompanyName,LinkMan:array[1..100] of string;
    CountFlag,i,j,flag:integer;
    CityTemp,LinkMantemp,CompanyNametemp:string;
begin
   QueryCustomer_Query.Active:=False;
   QueryCustomer_Query.Active:=True;
   with  QueryCustomer_StringGrid do
     begin
       colwidths[1]:=200;
       colwidths[3]:=200;
       colwidths[7]:=150;
       colwidths[8]:=150;
       colwidths[9]:=200;
       colwidths[10]:=300;
       cells[0,0]:='顾客编号';
       cells[1,0]:='公司名称';
       cells[2,0]:='联系人姓名';
       cells[3,0]:='联系地址';
       cells[4,0]:='所在城市';
       cells[5,0]:='所在地区';
       cells[6,0]:='邮政编码';
       cells[7,0]:='联系电话';
       cells[8,0]:='传真号码';
       cells[9,0]:='公司主页';
       cells[10,0]:='备注信息';
     end;

   //-------获取数据库中的顾客所在城市的数据------
   CountFlag:=1;
   flag:=0;
   with QueryCustomer_Query do
      begin
         close;
         sql.clear;
         sql.Add('select city from customer');
         open;
         first;
         City[CountFlag]:=fieldbyname('City').asstring;
         for i:=1 to recordcount do
           begin
             CityTemp:=fieldbyname('City').asstring;
             for j:=1 to  CountFlag do
                if  City[j]=CityTemp then
                     begin
                      flag:=1;
                      break;
                     end;
             if flag<>1 then
                begin
                  CountFlag:=CountFlag+1;
                  City[CountFlag]:=CityTemp;
                end;
             next;
             flag:=0;
            end;
      end;
   City_ComboBox.Clear;
   for i:=1 to  CountFlag do
      City_ComboBox.items.insert(-1,City[i]);

   //-------获取数据库中的公司名称的数据------
   CountFlag:=1;
   flag:=0;
   with QueryCustomer_Query do
      begin
         close;
         sql.clear;
         sql.Add('select CompanyName from customer');
         open;
         first;
         CompanyName[CountFlag]:=fieldbyname('CompanyName').asstring;
         for i:=1 to recordcount do
           begin
             CompanyNameTemp:=fieldbyname('CompanyName').asstring;
             for j:=1 to  CountFlag do
                if  CompanyName[j]=CompanyNameTemp then
                     begin
                      flag:=1;
                      break;
                     end;
             if flag<>1 then
                begin
                  CountFlag:=CountFlag+1;
                  CompanyName[CountFlag]:=CompanyNameTemp;
                end;
             next;
             flag:=0;
            end;
      end;
   ConpanyName_ComboBox.Clear;
   for i:=1 to  CountFlag do
      ConpanyName_ComboBox.items.insert(-1,CompanyName[i]);

   //-------获取数据库中的联系人的数据------
   CountFlag:=1;
   flag:=0;
   with QueryCustomer_Query do
      begin
         close;
         sql.clear;
         sql.Add('select LinkMan from customer');
         open;
         first;
         LinkMan[CountFlag]:=fieldbyname('LinkMan').asstring;
         for i:=1 to recordcount do
           begin
             LinkManTemp:=fieldbyname('LinkMan').asstring;
             for j:=1 to  CountFlag do
                if  LinkMan[j]=LinkManTemp then
                     begin
                      flag:=1;
                      break;
                     end;
             if flag<>1 then
                begin
                  CountFlag:=CountFlag+1;
                  LinkMan[CountFlag]:=LinkManTemp;
                end;
             next;
             flag:=0;
            end;
      end;
   LinkMan_ComboBox.Clear;
   for i:=1 to  CountFlag do
      LinkMan_ComboBox.items.insert(-1,LinkMan[i]);
end;


end.