yh_sel.pas

学员管理的软件

unit yh_sel;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ComCtrls, StdCtrls, Buttons, Grids, DBGrids, ExtCtrls,
  Excel2000, OleServer,Printers;

type
  Tzh_cx = class(TForm)
    Panel1: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    myname: TEdit;
    addr: TEdit;
    age: TEdit;
    email: TEdit;
    qx: TComboBox;
    card_cat: TComboBox;
    sex: TComboBox;
    card_num: TEdit;
    GroupBox1: TGroupBox;
    GroupBox2: TGroupBox;
    BitBtn1: TBitBtn;
    BitBtn2: TBitBtn;
    BitBtn3: TBitBtn;
    ProgressBar1: TProgressBar;
    BitBtn4: TBitBtn;
    BitBtn5: TBitBtn;
    ExcelApplication1: TExcelApplication;
    ExcelWorksheet1: TExcelWorksheet;
    ExcelWorkbook1: TExcelWorkbook;
    SaveDialog1: TSaveDialog;
    if1: TComboBox;
    if3: TComboBox;
    if2: TComboBox;
    if4: TComboBox;
    if5: TComboBox;
    if6: TComboBox;
    ListView1: TListView;
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn5Click(Sender: TObject);
    procedure ageKeyPress(Sender: TObject; var Key: Char);
    procedure ListView1ColumnClick(Sender: TObject; Column: TListColumn);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  zh_cx: Tzh_cx;
  colvalue:string;
  stringsql:string;
  isasc:boolean=true;
implementation

{$R *.dfm}
uses main,yh_propertity;
procedure Tzh_cx.BitBtn1Click(Sender: TObject);
var
name:string;
mysex:string;
cardcat:integer;
cardnum:string;
myage:integer;
myqx:string;
dz:string;
myemail:string;
mysql:string;
begin
if age.Text = '' then
begin
messagedlg('年龄必须填写',mtError,[mbOK],0);
exit;
end;
listview1.Clear;
name:=myname.Text;
mysex:=sex.Text;
cardcat:=card_cat.ItemIndex+1;
cardnum:=card_num.Text;
myage:=strtoint(age.Text);
myqx:=qx.Text;
dz:=addr.Text;
myemail:=email.text;
mysql:='';
mysql:='select ID,姓名,性别,年龄,证件类别,证件号,类别';
mysql:=mysql+' from 人员信息表 where 姓名 like ''%'+name+'%'' '+trim(if5.text)+' ';
mysql:=mysql+'性别='''+trim(mysex)+''' '+trim(if2.Text)+' 年龄='+inttostr(myage);
mysql:=mysql+' '+trim(if6.Text)+' 地址 like ''%'+dz+'%'' '+trim(if1.Text)+' 证件类别='+inttostr(cardcat);
mysql:=mysql+' and 证件号 like ''%'+cardnum+'%'' '+trim(if3.Text)+' 类别 like ''%';
mysql:=mysql+myqx+'%'' '+trim(if4.Text)+' 电子邮件 like ''%'+myemail+'%''';
stringsql:=mysql;
with main.DataModule2 do
begin
aq1.Close;
aq1.SQL.Clear;
aq1.SQL.Add(mysql);
aq1.Open;
while not aq1.Eof do
begin
  with listview1.Items.Add do
  begin
    caption:=aq1.FieldValues['ID'];
    subitems.Add(aq1.FieldValues['姓名']);
    subitems.Add(aq1.FieldValues['性别']);
    subitems.Add(inttostr(aq1.FieldValues['证件类别']));
    subitems.Add(aq1.FieldValues['证件号']);
    subitems.Add(aq1.FieldValues['类别']);
    subitems.Add(inttostr(aq1.FieldValues['年龄']));
  end;
  aq1.Next;
end;
end;
end;

procedure Tzh_cx.BitBtn3Click(Sender: TObject);
begin
close;
end;

procedure Tzh_cx.BitBtn4Click(Sender: TObject);
var
ss:string;
begin
ss:='';
ss:='select ID,姓名,性别,年龄,证件类别,证件号,类别 from 人员信息表';
stringsql:=ss;
with main.DataModule2 do
begin
aq1.Close;
aq1.SQL.Clear;
aq1.SQL.Add(ss);
aq1.Open;
listview1.Clear;
while not aq1.Eof do
begin
  with listview1.Items.Add do
  begin
    caption:=aq1.FieldValues['ID'];
    subitems.Add(aq1.FieldValues['姓名']);
    subitems.Add(aq1.FieldValues['性别']);
    subitems.Add(inttostr(aq1.FieldValues['证件类别']));
    subitems.Add(aq1.FieldValues['证件号']);
    subitems.Add(aq1.FieldValues['类别']);
    subitems.Add(inttostr(aq1.FieldValues['年龄']));
  end;
  aq1.Next;
end;
end;

end;

procedure Tzh_cx.BitBtn2Click(Sender: TObject);
begin
try
colVALUE:=listview1.Selected.Caption;
except
exit;
end;
if trim(colvalue)='' then
exit;
yh_propertity.hy_pro.Show;
end;


procedure Tzh_cx.BitBtn5Click(Sender: TObject);
var
  bexcelvisible:boolean;
  i,j:Integer;
  titlestr,fvalue,dispstr:string;
  ch:char;
  count:integer;
  mysql:string;
  mynum:integer;
begin
mysql:=stringsql;
mynum:=0;
with main.DataModule2 do
begin
aq1.Close;
aq1.SQL.Clear;
aq1.SQL.Add(mysql);
aq1.Open;
while not aq1.Eof do
begin
inc(mynum);
aq1.Next;
end;
end;
ProgressBar1.Max:=mynum-1;
progressbar1.Visible:=true;
if application.MessageBox('启动excel么？','咨询信息',MB_YESNO+MB_DEFBUTTON2)<>IDNO then
bexcelvisible:=true
else
bexcelvisible:=false;
try
screen.Cursor:=crhourglass;
excelapplication1.Connect;
except
messagedlg('excel没安装!',MTError,[mbOK],0);
abort;
end;
excelapplication1.Caption:='符合记录人员信息';
excelapplication1.Workbooks.Add(null,0);
excelworkbook1.ConnectTo(excelapplication1.Workbooks[1]);
try
excelworkbook1.Worksheets.Add(null,excelworkbook1.Worksheets[excelworkbook1.Worksheets.Count],null,null,0);
except
application.MessageBox('创建excel新页时失败！','信息',0);
exit;
end;
excelworksheet1.ConnectTo(excelworkbook1.Worksheets[1] as _worksheet);
excelworksheet1.Range['A1','E1'].Merge(true);
excelworksheet1.Range['A1','D2'].HorizontalAlignment:=$ffffeff4;
excelworksheet1.Cells.Item[1,1].value:='符合条件的人员信息';
i:=2;
count:=main.DataModule2.AQ1.FieldDefs.Count;
for j:=1 to count do
begin
excelworksheet1.Cells.Item[i,j].value:=main.DataModule2.AQ1.Fields[j-1].DisplayLabel;
end;
excelworksheet1.Range['A1','D1'].Font.Name:='隶书';
excelworksheet1.Range['A1','D1'].Font.Size:=18;
main.DataModule2.AQ1.First;
i:=0;
while not main.DataModule2.AQ1.Eof do
begin
for J:=0 to main.DataModule2.AQ1.fielddefs.count-1 do
begin
dispstr:=main.DataModule2.AQ1.Fields[j].AsString;
excelworksheet1.Cells.Item[i+3,j+1].value:=dispstr;
end;
ProgressBar1.Position:=i;
main.DataModule2.AQ1.Next;
i:=i+1;
end;
main.DataModule2.AQ1.close;
screen.Cursor:=crdefault;
if bexcelvisible then
excelapplication1.Visible[0]:=true
else
begin
if savedialog1.Execute then
begin
        try
        excelworksheet1.SaveAs(savedialog1.filename);
        application.MessageBox('保存成功','恭喜',MB_OK);
        except
        application.MessageBox('保存出错','错误',MB_OK);
        end;
end;
excelapplication1.Disconnect;
excelapplication1.Quit;
progressbar1.Position:=0;
end;
end;

procedure Tzh_cx.ageKeyPress(Sender: TObject; var Key: Char);
begin
if key in ['0','1','2','3','4','5','6','7','8','9'] then
else
begin
key:=#0;
messagebox(handle,'不合法字符','错误信息',MB_OK);
end;
end;

procedure Tzh_cx.ListView1ColumnClick(Sender: TObject;
  Column: TListColumn);
var
s:string;
label gothere;
begin
if trim(column.Caption)='编号' then
begin
if isasc then
begin
s:='order by ID';
goto gothere;
end
else
begin
s:='order by ID desc';
goto gothere;
end;
end;
if trim(column.Caption)='帐户名' then
begin
if isasc then
begin
s:='order by 姓名';
goto gothere;
end
else
begin
s:='order by 姓名 desc';
goto gothere;
end;
end;
if trim(column.Caption)='性别' then
begin
if isasc then
begin
s:='order by 性别';
goto gothere;
end
else
begin
s:='order by 性别 desc';
goto gothere;
end;
end;
if trim(column.Caption)='证件类别' then
begin
if isasc then
begin
s:='order by 证件类别';
goto gothere;
end
else
begin
s:='order by 证件类别 desc';
goto gothere;
end;
end;
if trim(column.Caption)='证件号' then
begin
if isasc then
begin
s:='order by 证件号';
goto gothere;
end
else
begin
s:='order by 证件号 desc';
goto gothere;
end;
end;
if trim(column.Caption)='权限' then
begin
if isasc then
begin
s:='order by 类别';
goto gothere;
end
else
begin
s:='order by 类别 desc';
goto gothere;
end;
end;
if trim(column.Caption)='年龄' then
begin
if isasc then
begin
s:='order by 年龄';
goto gothere;
end
else
begin
s:='order by 年龄 desc';
goto gothere;
end;
end;
gothere:
isasc:=not isasc;
if stringsql='' then exit;
s:=stringsql+' '+s;
listview1.Clear;
with main.DataModule2 do
begin
   aq1.Close;
   aq1.SQL.Clear;
   aq1.SQL.Add(s);
   aq1.Open;
   while not aq1.Eof do
   begin
    with listview1.Items.Add do
    begin
    caption:=aq1.FieldValues['ID'];
    subitems.Add(aq1.FieldValues['姓名']);
    subitems.Add(aq1.FieldValues['性别']);
    subitems.Add(inttostr(aq1.FieldValues['证件类别']));
    subitems.Add(aq1.FieldValues['证件号']);
    subitems.Add(aq1.FieldValues['类别']);
    subitems.Add(inttostr(aq1.FieldValues['年龄']));
    end;
   aq1.Next;
  end;
end;



end;

end.