unit searchcustbsns;interfaceuses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
StdCtrls, ComCtrls, ListViewcombysjy, Db, DBTables;type
Tsearchcustbsnsform = class(TForm)
GroupBox1: TGroupBox;
GroupBox2: TGroupBox;
Button1: TButton;
Button2: TButton;
ListView11: TListView1;
Query0: TQuery;
Query1: TQuery;
CheckBox1: TCheckBox;
CheckBox2: TCheckBox;
CheckBox3: TCheckBox;
CheckBox4: TCheckBox;
CheckBox5: TCheckBox;
CheckBox6: TCheckBox;
CheckBox7: TCheckBox;
CheckBox8: TCheckBox;
CheckBox9: TCheckBox;
CheckBox10: TCheckBox;
Edit1: TEdit;
ComboBox1: TComboBox;
ComboBox2: TComboBox;
DateTimePicker1: TDateTimePicker;
DateTimePicker2: TDateTimePicker;
DateTimePicker3: TDateTimePicker;
DateTimePicker4: TDateTimePicker;
DateTimePicker5: TDateTimePicker;
DateTimePicker6: TDateTimePicker;
ComboBox3: TComboBox;
ComboBox4: TComboBox;
Edit2: TEdit;
DateTimePicker7: TDateTimePicker;
DateTimePicker8: TDateTimePicker;
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Label4: TLabel;
Query1cust_account: TIntegerField;
Query1business_name: TStringField;
Query1account_type: TSmallintField;
Query1business_no: TSmallintField;
Query1open_date: TDateTimeField;
Query1requeststop_date: TDateTimeField;
Query1requestopen_date: TDateTimeField;
Query1use_state: TSmallintField;
Query1business_point: TSmallintField;
Query1opreator: TSmallintField;
Query1stop_date: TDateTimeField;
Query1Ingress_Policy_Name: TStringField;
Query1Egress_Policy_Name: TStringField;
Query1entry_name: TStringField;
Query1account_typename: TStringField;
Query1use_statename: TStringField;
Query1business_pointname: TStringField;
Query1operator_name: TStringField;
Label5: TLabel;
procedure Button2Click(Sender: TObject);
procedure CheckBox1Click(Sender: TObject);
procedure CheckBox2Click(Sender: TObject);
procedure CheckBox3Click(Sender: TObject);
procedure CheckBox4Click(Sender: TObject);
procedure CheckBox5Click(Sender: TObject);
procedure CheckBox6Click(Sender: TObject);
procedure CheckBox7Click(Sender: TObject);
procedure CheckBox8Click(Sender: TObject);
procedure CheckBox9Click(Sender: TObject);
procedure CheckBox10Click(Sender: TObject);
procedure FormActivate(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Query1CalcFields(DataSet: TDataSet);
private
{ Private declarations }
public
{ Public declarations }
end;var
searchcustbsnsform: Tsearchcustbsnsform;implementation{$R *.DFM}procedure Tsearchcustbsnsform.Button2Click(Sender: TObject);
begin
close;
end;procedure Tsearchcustbsnsform.CheckBox1Click(Sender: TObject);
begin
if checkbox1.Checked = true then
begin
edit1.Enabled := true;
edit1.Color := clwindow;
edit1.SetFocus;
end
else
begin
edit1.Enabled := false;
edit1.Color := clactiveborder;
end;
end;procedure Tsearchcustbsnsform.CheckBox2Click(Sender: TObject);
begin
if checkbox2.Checked = true then
begin
combobox1.Enabled := true;
combobox1.Color := clwindow;
combobox1.SetFocus;
end
else
begin
combobox1.Enabled := false;
combobox1.Color := clactiveborder;
end;
end;procedure Tsearchcustbsnsform.CheckBox3Click(Sender: TObject);
begin
if checkbox3.Checked = true then
begin
combobox2.Color := clwindow;
combobox2.Enabled := true;
combobox2.SetFocus;
end
else
begin
combobox2.Color := clactiveborder;
combobox2.Enabled := false;
end;
end;procedure Tsearchcustbsnsform.CheckBox4Click(Sender: TObject);
begin
if checkbox4.Checked = true then
begin
datetimepicker1.Color := clwindow;
datetimepicker1.Enabled := true;
datetimepicker2.Color := clwindow;
datetimepicker2.Enabled := true;
datetimepicker1.SetFocus;
end
else
begin
datetimepicker1.Color := clactiveborder;
datetimepicker1.Enabled := false;
datetimepicker2.Color := clactiveborder;
datetimepicker2.Enabled := false;
end;
end;procedure Tsearchcustbsnsform.CheckBox5Click(Sender: TObject);
begin
if checkbox5.Checked = true then
begin
datetimepicker3.Color := clwindow;
datetimepicker3.Enabled := true;
datetimepicker4.Color := clwindow;
datetimepicker4.Enabled := true;
datetimepicker3.SetFocus;
end
else
begin
datetimepicker3.Color := clactiveborder;
datetimepicker3.Enabled := false;
datetimepicker4.Color := clactiveborder;
datetimepicker4.Enabled := false;
end;
end;procedure Tsearchcustbsnsform.CheckBox6Click(Sender: TObject);
begin
if checkbox6.Checked = true then
begin
datetimepicker5.Color := clwindow;
datetimepicker5.Enabled := true;
datetimepicker6.Color := clwindow;
datetimepicker6.Enabled := true;
datetimepicker5.SetFocus;
end
else
begin
datetimepicker5.Color := clactiveborder;
datetimepicker5.Enabled := false;
datetimepicker6.Color := clactiveborder;
datetimepicker6.Enabled := false;
end;
end;procedure Tsearchcustbsnsform.CheckBox7Click(Sender: TObject);
begin
if checkbox7.Checked = true then
begin
combobox3.Color := clwindow;
combobox3.Enabled := true;
combobox3.SetFocus;
end
else
begin
combobox3.Color := clactiveborder;
combobox3.Enabled := false;
end;
end;procedure Tsearchcustbsnsform.CheckBox8Click(Sender: TObject);
begin
if checkbox8.Checked = true then
begin
combobox4.Color := clwindow;
combobox4.Enabled := true;
combobox4.SetFocus;
end
else
begin
combobox4.Color := clactiveborder;
combobox4.Enabled := false;
end;
end;procedure Tsearchcustbsnsform.CheckBox9Click(Sender: TObject);
begin
if checkbox9.Checked = true then
begin
edit2.Enabled := true;
edit2.Color := clwindow;
edit2.SetFocus;
end
else
begin
edit2.Enabled := false;
edit2.Color := clactiveborder;
end;
end;procedure Tsearchcustbsnsform.CheckBox10Click(Sender: TObject);
begin
if checkbox10.Checked = true then
begin
datetimepicker7.Color := clwindow;
datetimepicker7.Enabled := true;
datetimepicker8.Color := clwindow;
datetimepicker8.Enabled := true;
datetimepicker7.SetFocus;
end
else
begin
datetimepicker7.Color := clactiveborder;
datetimepicker7.Enabled := false;
datetimepicker8.Color := clactiveborder;
datetimepicker8.Enabled := false;
end;
end;procedure Tsearchcustbsnsform.FormActivate(Sender: TObject);
var
cur_date : tdatetime;
i : integer;
begin
with query0 do
begin
close;
sql.clear;
sql.add('select getdate()');
prepare;
open;
cur_date := fields[0].asdatetime;
close;
end;
datetimepicker1.DateTime := cur_date - 7;
datetimepicker2.DateTime := cur_date;
datetimepicker3.DateTime := cur_date - 7;
datetimepicker4.DateTime := cur_date;
datetimepicker5.DateTime := cur_date - 7;
datetimepicker6.DateTime := cur_date;
datetimepicker7.DateTime := cur_date - 7;
datetimepicker8.DateTime := cur_date; with query0 do
begin
sql.clear;
sql.add('select distinct business_name from t03_bsnsinfo');
prepare;
open;
if recordcount > 0 then
begin
first;
for i := 0 to recordcount - 1 do
begin
combobox1.Items.Add(fieldbyname('business_name').asstring);
next;
end;
end;
close;
sql.clear;
sql.add('select distinct account_typename from t03_accounttype');
prepare;
open;
if recordcount > 0 then
begin
first;
for i := 0 to recordcount - 1 do
begin
combobox2.items.add(fieldbyname('account_typename').asstring);
next;
end;
end;
close;
sql.clear;
sql.add('select distinct loc_name from t00_location');
prepare;
open;
if recordcount > 0 then
begin
first;
for i := 0 to recordcount - 1 do
begin
combobox4.Items.add(fieldbyname('loc_name').asstring);
next;
end;
end;
close;
end;
end;procedure Tsearchcustbsnsform.Button1Click(Sender: TObject);
var
bc{业务帐号} , bb{业务名称} , ba{计费类型} , bo{开通时间} ,
bre{申请停止时间} , brb{申请开始时间} , bs{业务状态} , bp{营业点} ,
bd{操作员} , be{停止时间} : boolean;
cust_account , business_no , account_type , business_state , point , operator_no : integer;
begin
bc := false;
bb := false;
ba := false;
bo := false;
bre:= false;
brb:= false;
bs := false;
bp := false;
bd := false;
be := false; if checkbox1.Checked = true then//得到客户流水号
begin
bc := true;
with query0 do
begin
close;
sql.clear;
sql.add('select * from t02_custinfo where entry_name =:e_n ');
parambyname('e_n').asstring := edit1.Text;
prepare;
open;
if recordcount < 1 then
begin
showmessage('没有这个客户！');
close;
edit1.SetFocus;
exit;
end;
cust_account := fieldbyname('cust_account').asinteger;
close;
end;
end; if checkbox2.Checked = true then//取得业务号
begin
bb := true;
if combobox1.Text = '' then
begin
showmessage('请选择业务名称！');
combobox1.SetFocus;
exit;
end;
with query0 do
begin
close;
sql.clear;
sql.add('select * from t03_bsnsinfo where business_name = :b_n');
parambyname('b_n').asstring := combobox1.Text;
prepare;
open;
if recordcount = 0 then
begin
showmessage('这个业务可能已经被删除！');
close;
combobox1.SetFocus;
exit;
end;
business_no := fieldbyname('business_no').asinteger;
close;
end;
end; if checkbox3.Checked = true then//取得计费类型
begin
ba := true;
if combobox2.Text = '' then
begin
showmessage('请选择计费类型！');
combobox2.SetFocus;
exit;
with query0 do
begin
close;
sql.clear;
sql.add('select * from t03_accounttype where account_typename =:a_n ' );
parambyname('a_n').asstring := combobox2.Text;
prepare;
open;
if recordcount = 0 then
begin
showmessage('这个计费类型可能被删除！');
combobox2.SetFocus;
close;
exit;
end;
account_type := fieldbyname('account_type').asinteger;
close;
end;
end;
end; if checkbox4.Checked = true then//取得开通时间
begin
bo := true;
end; if checkbox5.Checked = true then//取得申请停止时间
begin
bre := true;
end; if checkbox6.Checked = true then//取得申请开始时间
begin
brb := true;
end; if checkbox7.Checked = true then//取得业务状态
begin
bs := true;
if combobox3.Text = '' then
begin
showmessage('请选择业务状态！');
combobox3.SetFocus;
exit;
end;
business_state := combobox3.ItemIndex + 1;
end;
if checkbox8.Checked = true then//取得营业点
begin
bp := true;
if combobox4.Text = '' then
begin
showmessage('请选择营业点！');
combobox4.SetFocus;
exit;
end;
with query0 do
begin
close;
sql.clear;
sql.add('select * from t00_location where loc_name = :l_n');
parambyname('l_n').asstring := combobox4.Text;
prepare;
open;
if recordcount = 0 then
begin
showmessage('这个地点可能已经被删除！');
combobox4.SetFocus;
close;
exit;
end;
point := fieldbyname('loc_num').asinteger;
close;
end;
end; if checkbox9.Checked = true then//取得操作员
begin
bd := true;
if trim(edit2.Text) = '' then
begin
showmessage('请输入操作员名称！');
edit2.SetFocus;
exit;
end;
with query0 do
begin
close;
sql.Clear;
sql.add('select * from t00_sysuser where user_account = :u_a');
parambyname('u_a').asstring := edit2.Text;
prepare;
open;
if recordcount = 0 then
begin
showmessage('不存在这个操作员！');
edit2.SetFocus;
close;
exit;
end;
operator_no := fieldbyname('user_series').asinteger;
close;
end;
end; if checkbox10.Checked = true then//取得停止时间
begin
be := true;
end; with query1 do
begin
close;
sql.Clear;
sql.add('select * from t02_custbsns where (((((((((((1 = 1)'); if bc = true then//客户帐号条件
begin
sql.add(' and (cust_account = ' + inttostr(cust_account) + '))');
end
else
begin
sql.add(')');
end; if bb = true then//业务条件
begin
sql.add(' and (business_no =' + inttostr(business_no) + ' ))');
end
else
begin
sql.add(')');
end; if ba = true then//计费类型条件
begin
sql.add(' and (account_type =' + inttostr(account_type) + ' ))')
end
else
begin
sql.add(')');
end; if bo = true then//开通时间条件
begin
sql.add(' and ( (open_date >= :o_d1) and (open_date <= :o_d2) ))');
parambyname('o_d1').asdatetime := datetimepicker1.DateTime;
parambyname('o_d2').asdatetime := datetimepicker2.DateTime;
end
else
begin
sql.add(')');
end; if bre = true then//申请停止时间条件
begin
sql.add(' and ( (requeststop_date >= :r_d1) and (requeststop_date <= :r_d2) ))');
parambyname('r_d1').asdatetime := datetimepicker3.DateTime;
parambyname('r_d2').asdatetime := datetimepicker4.DateTime;
end
else
begin
sql.add(')');
end; if brb = true then//申请开通时间条件
begin
sql.add(' and ((requestopen_date >= :r_d3) and (requestopen_date <= :r_d4)))');
parambyname('r_d3').asdatetime := datetimepicker5.DateTime;
parambyname('r_d4').asdatetime := datetimepicker6.DateTime;
end
else
begin
sql.add(')');
end; if bs = true then//业务状态条件
begin
sql.add(' and (use_state = ' + inttostr(business_state) + '))');
end
else
begin
sql.add(')');
end; if bp = true then//营业点条件
begin
sql.add(' and (business_point = ' + inttostr(point) + '))');
end
else
begin
sql.add(')');
end; if bd = true then//操作员条件
begin
sql.add(' and (opreator = '+ inttostr(operator_no) +'))');
end
else
begin
sql.add(')');
end; if be = true then//停止时间条件
begin
sql.add(' and ((stop_date >= :st_d1) and (stop_date <= :st_d2)))');
parambyname('st_d1').asdatetime := datetimepicker7.DateTime;
parambyname('st_d2').asdatetime := datetimepicker8.DateTime;
end
else
begin
sql.add(')');
end; end;
label5.Caption := '查询中...';
label5.Refresh;
listview11.getthedata;
label5.Caption := '';
end;procedure Tsearchcustbsnsform.Query1CalcFields(DataSet: TDataSet);
begin
with query0 do
begin
close;
sql.clear;
sql.add('select * from t02_custinfo where cust_account = ' + query1.fieldbyname('cust_account').asstring);
prepare;
open;
if recordcount > 0 then
query1.FieldByName('entry_name').asstring := fieldbyname('entry_name').asstring;
close;
sql.clear;
sql.add('select * from t03_accounttype where account_type = ' + query1.fieldbyname('use_state').asstring);
prepare;
open;
if recordcount > 0 then
query1.fieldbyname('account_typename').asstring := fieldbyname('account_typename').asstring;
close;
query1.FieldByName('use_statename').asstring := combobox3.Items[query1.fieldbyname('use_state').asinteger - 1];
sql.clear;
sql.add('select * from t00_location where loc_num = ' + query1.fieldbyname('business_point').asstring);
prepare;
open;
if recordcount > 0 then
query1.FieldByName('business_pointname').asstring := fieldbyname('loc_name').asstring;
close;
sql.clear;
sql.add('select * from t00_sysuser where user_series = ' + query1.fieldbyname('opreator').asstring);
prepare;
open;
if recordcount > 0 then
query1.FieldByName('operator_name').asstring := fieldbyname('user_account').asstring;
close;
end;
end;end.
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
StdCtrls, ComCtrls, ListViewcombysjy, Db, DBTables;type
Tsearchcustbsnsform = class(TForm)
GroupBox1: TGroupBox;
GroupBox2: TGroupBox;
Button1: TButton;
Button2: TButton;
ListView11: TListView1;
Query0: TQuery;
Query1: TQuery;
CheckBox1: TCheckBox;
CheckBox2: TCheckBox;
CheckBox3: TCheckBox;
CheckBox4: TCheckBox;
CheckBox5: TCheckBox;
CheckBox6: TCheckBox;
CheckBox7: TCheckBox;
CheckBox8: TCheckBox;
CheckBox9: TCheckBox;
CheckBox10: TCheckBox;
Edit1: TEdit;
ComboBox1: TComboBox;
ComboBox2: TComboBox;
DateTimePicker1: TDateTimePicker;
DateTimePicker2: TDateTimePicker;
DateTimePicker3: TDateTimePicker;
DateTimePicker4: TDateTimePicker;
DateTimePicker5: TDateTimePicker;
DateTimePicker6: TDateTimePicker;
ComboBox3: TComboBox;
ComboBox4: TComboBox;
Edit2: TEdit;
DateTimePicker7: TDateTimePicker;
DateTimePicker8: TDateTimePicker;
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Label4: TLabel;
Query1cust_account: TIntegerField;
Query1business_name: TStringField;
Query1account_type: TSmallintField;
Query1business_no: TSmallintField;
Query1open_date: TDateTimeField;
Query1requeststop_date: TDateTimeField;
Query1requestopen_date: TDateTimeField;
Query1use_state: TSmallintField;
Query1business_point: TSmallintField;
Query1opreator: TSmallintField;
Query1stop_date: TDateTimeField;
Query1Ingress_Policy_Name: TStringField;
Query1Egress_Policy_Name: TStringField;
Query1entry_name: TStringField;
Query1account_typename: TStringField;
Query1use_statename: TStringField;
Query1business_pointname: TStringField;
Query1operator_name: TStringField;
Label5: TLabel;
procedure Button2Click(Sender: TObject);
procedure CheckBox1Click(Sender: TObject);
procedure CheckBox2Click(Sender: TObject);
procedure CheckBox3Click(Sender: TObject);
procedure CheckBox4Click(Sender: TObject);
procedure CheckBox5Click(Sender: TObject);
procedure CheckBox6Click(Sender: TObject);
procedure CheckBox7Click(Sender: TObject);
procedure CheckBox8Click(Sender: TObject);
procedure CheckBox9Click(Sender: TObject);
procedure CheckBox10Click(Sender: TObject);
procedure FormActivate(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Query1CalcFields(DataSet: TDataSet);
private
{ Private declarations }
public
{ Public declarations }
end;var
searchcustbsnsform: Tsearchcustbsnsform;implementation{$R *.DFM}procedure Tsearchcustbsnsform.Button2Click(Sender: TObject);
begin
close;
end;procedure Tsearchcustbsnsform.CheckBox1Click(Sender: TObject);
begin
if checkbox1.Checked = true then
begin
edit1.Enabled := true;
edit1.Color := clwindow;
edit1.SetFocus;
end
else
begin
edit1.Enabled := false;
edit1.Color := clactiveborder;
end;
end;procedure Tsearchcustbsnsform.CheckBox2Click(Sender: TObject);
begin
if checkbox2.Checked = true then
begin
combobox1.Enabled := true;
combobox1.Color := clwindow;
combobox1.SetFocus;
end
else
begin
combobox1.Enabled := false;
combobox1.Color := clactiveborder;
end;
end;procedure Tsearchcustbsnsform.CheckBox3Click(Sender: TObject);
begin
if checkbox3.Checked = true then
begin
combobox2.Color := clwindow;
combobox2.Enabled := true;
combobox2.SetFocus;
end
else
begin
combobox2.Color := clactiveborder;
combobox2.Enabled := false;
end;
end;procedure Tsearchcustbsnsform.CheckBox4Click(Sender: TObject);
begin
if checkbox4.Checked = true then
begin
datetimepicker1.Color := clwindow;
datetimepicker1.Enabled := true;
datetimepicker2.Color := clwindow;
datetimepicker2.Enabled := true;
datetimepicker1.SetFocus;
end
else
begin
datetimepicker1.Color := clactiveborder;
datetimepicker1.Enabled := false;
datetimepicker2.Color := clactiveborder;
datetimepicker2.Enabled := false;
end;
end;procedure Tsearchcustbsnsform.CheckBox5Click(Sender: TObject);
begin
if checkbox5.Checked = true then
begin
datetimepicker3.Color := clwindow;
datetimepicker3.Enabled := true;
datetimepicker4.Color := clwindow;
datetimepicker4.Enabled := true;
datetimepicker3.SetFocus;
end
else
begin
datetimepicker3.Color := clactiveborder;
datetimepicker3.Enabled := false;
datetimepicker4.Color := clactiveborder;
datetimepicker4.Enabled := false;
end;
end;procedure Tsearchcustbsnsform.CheckBox6Click(Sender: TObject);
begin
if checkbox6.Checked = true then
begin
datetimepicker5.Color := clwindow;
datetimepicker5.Enabled := true;
datetimepicker6.Color := clwindow;
datetimepicker6.Enabled := true;
datetimepicker5.SetFocus;
end
else
begin
datetimepicker5.Color := clactiveborder;
datetimepicker5.Enabled := false;
datetimepicker6.Color := clactiveborder;
datetimepicker6.Enabled := false;
end;
end;procedure Tsearchcustbsnsform.CheckBox7Click(Sender: TObject);
begin
if checkbox7.Checked = true then
begin
combobox3.Color := clwindow;
combobox3.Enabled := true;
combobox3.SetFocus;
end
else
begin
combobox3.Color := clactiveborder;
combobox3.Enabled := false;
end;
end;procedure Tsearchcustbsnsform.CheckBox8Click(Sender: TObject);
begin
if checkbox8.Checked = true then
begin
combobox4.Color := clwindow;
combobox4.Enabled := true;
combobox4.SetFocus;
end
else
begin
combobox4.Color := clactiveborder;
combobox4.Enabled := false;
end;
end;procedure Tsearchcustbsnsform.CheckBox9Click(Sender: TObject);
begin
if checkbox9.Checked = true then
begin
edit2.Enabled := true;
edit2.Color := clwindow;
edit2.SetFocus;
end
else
begin
edit2.Enabled := false;
edit2.Color := clactiveborder;
end;
end;procedure Tsearchcustbsnsform.CheckBox10Click(Sender: TObject);
begin
if checkbox10.Checked = true then
begin
datetimepicker7.Color := clwindow;
datetimepicker7.Enabled := true;
datetimepicker8.Color := clwindow;
datetimepicker8.Enabled := true;
datetimepicker7.SetFocus;
end
else
begin
datetimepicker7.Color := clactiveborder;
datetimepicker7.Enabled := false;
datetimepicker8.Color := clactiveborder;
datetimepicker8.Enabled := false;
end;
end;procedure Tsearchcustbsnsform.FormActivate(Sender: TObject);
var
cur_date : tdatetime;
i : integer;
begin
with query0 do
begin
close;
sql.clear;
sql.add('select getdate()');
prepare;
open;
cur_date := fields[0].asdatetime;
close;
end;
datetimepicker1.DateTime := cur_date - 7;
datetimepicker2.DateTime := cur_date;
datetimepicker3.DateTime := cur_date - 7;
datetimepicker4.DateTime := cur_date;
datetimepicker5.DateTime := cur_date - 7;
datetimepicker6.DateTime := cur_date;
datetimepicker7.DateTime := cur_date - 7;
datetimepicker8.DateTime := cur_date; with query0 do
begin
sql.clear;
sql.add('select distinct business_name from t03_bsnsinfo');
prepare;
open;
if recordcount > 0 then
begin
first;
for i := 0 to recordcount - 1 do
begin
combobox1.Items.Add(fieldbyname('business_name').asstring);
next;
end;
end;
close;
sql.clear;
sql.add('select distinct account_typename from t03_accounttype');
prepare;
open;
if recordcount > 0 then
begin
first;
for i := 0 to recordcount - 1 do
begin
combobox2.items.add(fieldbyname('account_typename').asstring);
next;
end;
end;
close;
sql.clear;
sql.add('select distinct loc_name from t00_location');
prepare;
open;
if recordcount > 0 then
begin
first;
for i := 0 to recordcount - 1 do
begin
combobox4.Items.add(fieldbyname('loc_name').asstring);
next;
end;
end;
close;
end;
end;procedure Tsearchcustbsnsform.Button1Click(Sender: TObject);
var
bc{业务帐号} , bb{业务名称} , ba{计费类型} , bo{开通时间} ,
bre{申请停止时间} , brb{申请开始时间} , bs{业务状态} , bp{营业点} ,
bd{操作员} , be{停止时间} : boolean;
cust_account , business_no , account_type , business_state , point , operator_no : integer;
begin
bc := false;
bb := false;
ba := false;
bo := false;
bre:= false;
brb:= false;
bs := false;
bp := false;
bd := false;
be := false; if checkbox1.Checked = true then//得到客户流水号
begin
bc := true;
with query0 do
begin
close;
sql.clear;
sql.add('select * from t02_custinfo where entry_name =:e_n ');
parambyname('e_n').asstring := edit1.Text;
prepare;
open;
if recordcount < 1 then
begin
showmessage('没有这个客户！');
close;
edit1.SetFocus;
exit;
end;
cust_account := fieldbyname('cust_account').asinteger;
close;
end;
end; if checkbox2.Checked = true then//取得业务号
begin
bb := true;
if combobox1.Text = '' then
begin
showmessage('请选择业务名称！');
combobox1.SetFocus;
exit;
end;
with query0 do
begin
close;
sql.clear;
sql.add('select * from t03_bsnsinfo where business_name = :b_n');
parambyname('b_n').asstring := combobox1.Text;
prepare;
open;
if recordcount = 0 then
begin
showmessage('这个业务可能已经被删除！');
close;
combobox1.SetFocus;
exit;
end;
business_no := fieldbyname('business_no').asinteger;
close;
end;
end; if checkbox3.Checked = true then//取得计费类型
begin
ba := true;
if combobox2.Text = '' then
begin
showmessage('请选择计费类型！');
combobox2.SetFocus;
exit;
with query0 do
begin
close;
sql.clear;
sql.add('select * from t03_accounttype where account_typename =:a_n ' );
parambyname('a_n').asstring := combobox2.Text;
prepare;
open;
if recordcount = 0 then
begin
showmessage('这个计费类型可能被删除！');
combobox2.SetFocus;
close;
exit;
end;
account_type := fieldbyname('account_type').asinteger;
close;
end;
end;
end; if checkbox4.Checked = true then//取得开通时间
begin
bo := true;
end; if checkbox5.Checked = true then//取得申请停止时间
begin
bre := true;
end; if checkbox6.Checked = true then//取得申请开始时间
begin
brb := true;
end; if checkbox7.Checked = true then//取得业务状态
begin
bs := true;
if combobox3.Text = '' then
begin
showmessage('请选择业务状态！');
combobox3.SetFocus;
exit;
end;
business_state := combobox3.ItemIndex + 1;
end;
if checkbox8.Checked = true then//取得营业点
begin
bp := true;
if combobox4.Text = '' then
begin
showmessage('请选择营业点！');
combobox4.SetFocus;
exit;
end;
with query0 do
begin
close;
sql.clear;
sql.add('select * from t00_location where loc_name = :l_n');
parambyname('l_n').asstring := combobox4.Text;
prepare;
open;
if recordcount = 0 then
begin
showmessage('这个地点可能已经被删除！');
combobox4.SetFocus;
close;
exit;
end;
point := fieldbyname('loc_num').asinteger;
close;
end;
end; if checkbox9.Checked = true then//取得操作员
begin
bd := true;
if trim(edit2.Text) = '' then
begin
showmessage('请输入操作员名称！');
edit2.SetFocus;
exit;
end;
with query0 do
begin
close;
sql.Clear;
sql.add('select * from t00_sysuser where user_account = :u_a');
parambyname('u_a').asstring := edit2.Text;
prepare;
open;
if recordcount = 0 then
begin
showmessage('不存在这个操作员！');
edit2.SetFocus;
close;
exit;
end;
operator_no := fieldbyname('user_series').asinteger;
close;
end;
end; if checkbox10.Checked = true then//取得停止时间
begin
be := true;
end; with query1 do
begin
close;
sql.Clear;
sql.add('select * from t02_custbsns where (((((((((((1 = 1)'); if bc = true then//客户帐号条件
begin
sql.add(' and (cust_account = ' + inttostr(cust_account) + '))');
end
else
begin
sql.add(')');
end; if bb = true then//业务条件
begin
sql.add(' and (business_no =' + inttostr(business_no) + ' ))');
end
else
begin
sql.add(')');
end; if ba = true then//计费类型条件
begin
sql.add(' and (account_type =' + inttostr(account_type) + ' ))')
end
else
begin
sql.add(')');
end; if bo = true then//开通时间条件
begin
sql.add(' and ( (open_date >= :o_d1) and (open_date <= :o_d2) ))');
parambyname('o_d1').asdatetime := datetimepicker1.DateTime;
parambyname('o_d2').asdatetime := datetimepicker2.DateTime;
end
else
begin
sql.add(')');
end; if bre = true then//申请停止时间条件
begin
sql.add(' and ( (requeststop_date >= :r_d1) and (requeststop_date <= :r_d2) ))');
parambyname('r_d1').asdatetime := datetimepicker3.DateTime;
parambyname('r_d2').asdatetime := datetimepicker4.DateTime;
end
else
begin
sql.add(')');
end; if brb = true then//申请开通时间条件
begin
sql.add(' and ((requestopen_date >= :r_d3) and (requestopen_date <= :r_d4)))');
parambyname('r_d3').asdatetime := datetimepicker5.DateTime;
parambyname('r_d4').asdatetime := datetimepicker6.DateTime;
end
else
begin
sql.add(')');
end; if bs = true then//业务状态条件
begin
sql.add(' and (use_state = ' + inttostr(business_state) + '))');
end
else
begin
sql.add(')');
end; if bp = true then//营业点条件
begin
sql.add(' and (business_point = ' + inttostr(point) + '))');
end
else
begin
sql.add(')');
end; if bd = true then//操作员条件
begin
sql.add(' and (opreator = '+ inttostr(operator_no) +'))');
end
else
begin
sql.add(')');
end; if be = true then//停止时间条件
begin
sql.add(' and ((stop_date >= :st_d1) and (stop_date <= :st_d2)))');
parambyname('st_d1').asdatetime := datetimepicker7.DateTime;
parambyname('st_d2').asdatetime := datetimepicker8.DateTime;
end
else
begin
sql.add(')');
end; end;
label5.Caption := '查询中...';
label5.Refresh;
listview11.getthedata;
label5.Caption := '';
end;procedure Tsearchcustbsnsform.Query1CalcFields(DataSet: TDataSet);
begin
with query0 do
begin
close;
sql.clear;
sql.add('select * from t02_custinfo where cust_account = ' + query1.fieldbyname('cust_account').asstring);
prepare;
open;
if recordcount > 0 then
query1.FieldByName('entry_name').asstring := fieldbyname('entry_name').asstring;
close;
sql.clear;
sql.add('select * from t03_accounttype where account_type = ' + query1.fieldbyname('use_state').asstring);
prepare;
open;
if recordcount > 0 then
query1.fieldbyname('account_typename').asstring := fieldbyname('account_typename').asstring;
close;
query1.FieldByName('use_statename').asstring := combobox3.Items[query1.fieldbyname('use_state').asinteger - 1];
sql.clear;
sql.add('select * from t00_location where loc_num = ' + query1.fieldbyname('business_point').asstring);
prepare;
open;
if recordcount > 0 then
query1.FieldByName('business_pointname').asstring := fieldbyname('loc_name').asstring;
close;
sql.clear;
sql.add('select * from t00_sysuser where user_series = ' + query1.fieldbyname('opreator').asstring);
prepare;
open;
if recordcount > 0 then
query1.FieldByName('operator_name').asstring := fieldbyname('user_account').asstring;
close;
end;
end;end.
@name vchar(40) = null,
@age int = null,
as
if @name = null and age =null
select * from Customer;
if @name = null and age !=null
select * from Customer where age = @age;
....
在实际数据库管理系统中，用户对表中数据的操作，最频繁的莫过于浏览查询了，而查询中若能提供为某字段建立的排序功能，则非常有利于用户对“关键数据”的了解。
Windows的用户都知道，在“我的电脑”或“资源管理器”中打开任一文件夹，若以“详细资料”方式查看，系统会显示出该文件夹下的子文件夹和文件相关信息，如：名称、类型 、大小、修改时间，用户只需要单击标题栏中的相应项，则系统自动按该项进行“升序”（或“降序”）的排列显示，这样用户便能轻松查看相应的文件夹或文件对象的内容。
受此启发，考虑能不能在显示数据的Grid表格中完成如此功能呢？答案是肯定的。下面以在Delphi中的实现方法为例，通过具体内容，介绍该功能的实现。
步骤如下：
一、先建立一数据表
该表以Delphi 中最常用的Paradox为类型，取名为Student，反映（在职）学生的基本情况。该表各字段定义如下：
--------------------------------------------
字段名 类型 大小
序号 Short型 / (Key*)
学号 Alpha型 6
出生日期 Date型 /
性别 Alpha型 2
婚否 Logical型 /
英语 Number型 /
高数 Number型 /
PASCAL Number型 /
备注 Memo型 20
-------------------------------------------
保存后，随意往表中输入3至5条记录内容。
注：①表中必须建立关键索引（为首字段建立）。此处为“序号”字段；
②该表中使用了Paradox常用的几种字段类型，但尚未全部包含。
二、建立项目，实现功能
1．新建一项目，并为表单添加相关控件，各控件主要属性如下表：
2．建立各Click的事件代码
Button1(打开表)的Click事件代码如下：
procedure TForm1.Button1Click(Sender: TObject);
begin
Table1.Open; // 打开Table1关联的表Student
end;
Button2(关闭表单)的Click事件代码如下：
procedure TForm1.Button2Click(Sender: TObject);
begin
Application.Terminate;
end;
DBGrid1的TitleClick事件代码如下：
procedure TForm1.DBGrid1TitleClick(Column: TColumn);
//注：本过程参数Column包含的信息量非常多
begin
MySort(DBGrid1,Column);
end; //调用字段排序
其中，MySort(DBGrid1,Column)为自定义的排序过程，具体代码见下述。
3．建立通用处理模块
为使该功能具有“通用性”，将其定义为一过程。
首先，预声明过程及建立两个全局私有变量：
...
Type
...
procedure MySort(DBGrid0:TDBGrid; Column: TColumn);//预声明过程
private
{ Private declarations }
psIndexName:string; //记录当前索引名称
plAscend:boolean; //记录当前索引名称的索引状态
public
{ Public declarations }
end;
...
其次，该过程完整代码如下：
procedure TForm1.MySort(DBGrid0:TDBGrid; Column: TColumn);
var
//本模块使用到的psIndexName, plAscend两个变量见上定义
mode:char; //记录是“升序”还是“降序”
ColName:string; //记录当前字段名
iCol:Integer; //记录当前列号
begin
with DBGrid0.DataSource.DataSet as TTable do //Table0
begin
//检测当前工作表是否已打开
if not Active
then begin
MessageBeep(0);
Application.MessageBox('工作表尚未打开！','停止',MB_OK+MB_ICONSTOP);
Abort
end;
//检测当前字段是否“能排序”。以下字段类型不能排序
case Column.Field.DataType of
ftBoolean,
ftBytes,
ftBlob, //Binary
ftMemo,
ftGraphic,
ftFmtMemo, //Formatted memo
ftParadoxOle: //OLE
begin
MessageBeep(0);
Application.MessageBox(Pchar('项目"'+Column.FieldName+'"'+'不能排序！'),'停止
',MB_OK+MB_ICONSTOP);
Abort
end;
end; //case
mode:='0';
iCol:=Column.Field.FieldNo-1;
try
ColName:=Column.fieldname;
if psIndexName=Column.fieldname
then begin //与原来同列
if plAscend //升序
then begin
mode:='2';
IndexName:=ColName+'2'; //应“降序”
end
else begin
mode:='1';
IndexName:=ColName+'1'; //应“升序”
end;
plAscend:=not plAscend;
end
else begin //新列
IndexName:=ColName+'2';
plAscend:=false;
psIndexName:=ColName;
end;
except
on EDatabaseError do //若未有索引，则重新建立
begin
Messagebeep(0);
//以下新建索引
IndexName:='';
Close;
Exclusive:=true;
if mode='1'
then AddIndex(ColName+'1',ColName,[ixCaseInsensitive],'')//
else //包括'0'
AddIndex(ColName+'2',ColName,[ixDescending,ixCaseInsensitive],'');
Exclusive:=false;
Open;
try //try 1
if mode<>'1'
then begin
mode:='2';//转换
plAscend:=false;
end
else plAscend:=true;
IndexName:=ColName+mode;
psIndexName:=ColName;
except
on EDBEngineError do
IndexName:='';
end //try 2
end
end;
First;
end; //with
DBGrid0.SelectedIndex:=iCol;
end;//End of MySort
如图所示，当对“日期型”的“出生日期”单击产生的排序结果。
本过程已对所有可能的错误进行了相应的检测及处理，代码是比较完整的。因此，把该过程放入你相应的单元中，对每一个DBGrid，只要传递不同的DBGrid及Column参数，就能实现对应数据表的自动排序处理，而事先只为某字段建立一关键索引即可，其它Secondery Indexes的建立均在程序中自动完成，但会为每一个建立了索引的字段生成了一些附加文件（如*.XG?,*YG?等）。当然若有必要，可以在表单关闭前将所有的附加文件删除。
利用这种方法来实现数据表的自动排序，非常灵活实用。该思想当然也适用于其它编程语言。
软件环境：中文Win98/中文Delphi5.0。