canfind.pas

pasa人力资源考勤管理系统

unit canfind;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  ExtCtrls, Grids, DBGrids, StdCtrls, Buttons, Db, ADODB, ImgList, Menus,
  DBCtrls, ComCtrls, ToolWin;

type
  Tformcanfind = class(TForm)
    Panel1: TPanel;
    DBGrid1: TDBGrid;
    Splitter1: TSplitter;
    Panel2: TPanel;
    ScrollBox1: TScrollBox;
    ScrollBox2: TScrollBox;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    SpnNewshow: TSpeedButton;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    ComboBox3: TComboBox;
    ComboBox4: TComboBox;
    ComboBox5: TComboBox;
    ComboBox6: TComboBox;
    ComboBox7: TComboBox;
    ComboBox8: TComboBox;
    ComboBox9: TComboBox;
    ComboBox10: TComboBox;
    ComboBox11: TComboBox;
    SpnPrint: TSpeedButton;
    SpnToExcel: TSpeedButton;
    SpnExit: TSpeedButton;
    Splitter2: TSplitter;
    DataSource1: TDataSource;
    ADOQuery1: TADOQuery;
    ImageList1: TImageList;
    SpeedButton1: TSpeedButton;
    SpeedButton2: TSpeedButton;
    SpeedButton3: TSpeedButton;
    SpeedButton4: TSpeedButton;
    ComboBox13: TComboBox;
    ComboBox14: TComboBox;
    ComboBox15: TComboBox;
    ComboBox16: TComboBox;
    ComboBox17: TComboBox;
    PopupMenu1: TPopupMenu;
    N1: TMenuItem;
    N2: TMenuItem;
    N4: TMenuItem;
    N3: TMenuItem;
    copycheck: TCheckBox;
    ToolBar1: TToolBar;
    ToolButton1: TToolButton;
    ToolButton5: TToolButton;
    ToolButton6: TToolButton;
    tbnsort: TToolButton;
    tbnfind: TToolButton;
    ToolButton7: TToolButton;
    ToolButton4: TToolButton;
    DBNavigator1: TDBNavigator;
    Panel3: TPanel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    DBText1: TDBText;
    DBText2: TDBText;
    DBText3: TDBText;
    DBText4: TDBText;
    DBText5: TDBText;
    Label12: TLabel;
    DBText7: TDBText;
    Label11: TLabel;
    DBText6: TDBText;
    DBText8: TDBText;
    DBCheckBox2: TDBCheckBox;
    DBCheckBox3: TDBCheckBox;
    procedure SpnExitClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure SpnNewshowClick(Sender: TObject);
    procedure FormKeyPress(Sender: TObject; var Key: Char);
    procedure SpeedButton1Click(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure SpeedButton3Click(Sender: TObject);
    procedure SpeedButton4Click(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure ComboBox4Change(Sender: TObject);
    procedure ComboBox7Change(Sender: TObject);
    procedure ComboBox10Change(Sender: TObject);
    procedure N1Click(Sender: TObject);
    procedure N2Click(Sender: TObject);
    procedure N4Click(Sender: TObject);
    procedure N3Click(Sender: TObject);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure copycheckClick(Sender: TObject);
    procedure ToolButton4Click(Sender: TObject);
    procedure ToolButton1Click(Sender: TObject);
    procedure SpnToExcelClick(Sender: TObject);
    procedure ToolButton5Click(Sender: TObject);
    procedure SpnPrintClick(Sender: TObject);
  private
    { Private declarations }
    findex:array[1..5] of integer;   //存放选取了哪些column
    fsort:array[1..5] of string;     //选取的column的排列顺序是ASC RO DESC
    fcount1:0..5;                    //最多允许选取5个以内的column
    f:string;                       //设定单字段排序
    procedure iniadodataset;
    procedure iniadoquery;
    function  changetrue():boolean;
    procedure itemadd(cs,f:string);  //当输入字段时，在值中添加出来
  public
    { Public declarations }
    adt:Tadodataset;
    procedure createfield(Cfield:integer);  //产生cfield个checkbox
    procedure showcolumnname(n:integer);         //使checkbox把field显示出来
    procedure gettablename(tm,tab:string);
    procedure showcomboxitem(y:integer);
  end;

var
  formcanfind: Tformcanfind;
  ary:Tadoquery;
  aryB,adtB:boolean;
  atb:Tadotable;
  tname:string;   //在datamod中的组件名
  tabname:string; //在datamod中的组件对应的sql table
  fcount:integer; //查询的table有多少个field

implementation

uses datamol,main,cvcode, printsel;
{$R *.DFM}

procedure Tformcanfind.createfield(Cfield:integer);
var i,x,y:integer;
    check:Tcheckbox;
begin
  x:=8;
  y:=8;
  for i:=0 to cfield-1 do
  begin
    check:=tcheckbox.Create(self);
    check.parent:= Scrollbox1;
    if (i mod 8)=0 then
     begin
      if i<>0 then x:=x+80;
      y:=8;
      check.Left :=x;
      check.top :=y;
     end
    else begin
      check.Left :=x;
      y:=y+24;
      check.Top:=y;

     end;
    check.Name :='check'+inttostr(i);
    check.OnClick :=copycheckclick;
  end;
end;

//****变换dbgrid1的title.caption
procedure Tformcanfind.showcolumnname(n:integer);
var i,j:integer;
    s:string;
    ckx:Tcheckbox;
begin
  s:='';
  j:=0;
  fcount:=n;
  for i:=0 to n-1 do
  begin
    ckx:=Tcheckbox(findcomponent('check'+inttostr(i)));
    with ckx do
    begin
      if ckx.Checked=true then
       begin
         dbgrid1.Columns[j].Title.Caption:=ckx.Caption;
         j:=j+1;
       end;
    end;
  end;
end;
procedure Tformcanfind.gettablename(tm,tab:string);
begin
  tname:=tm;
  tabname:=tab;
  if copy(tname,1,3)='Qry' then
    iniadoquery
  else if copy(tname,1,3)='Ado' then
    iniadodataset
  else if copy(tname,1,3)='pie' then
    iniadodataset
  else
    Application.Messagebox(pchar(tname+'   名字不合规定,不能继续作业'),'Error',mb_ok+mb_iconerror);
end;

procedure Tformcanfind.iniadodataset;  //如果来源表名是adodataset,就如下
begin
 { if  Tadodataset(datamod.FindComponent(tname))<>nil then
    adt:=Tadodataset(datamod.FindComponent(tname));    }
  if adt<>nil then adtB:=true;
end;

procedure Tformcanfind.iniadoquery;
begin
  {if Tadoquery(datamod.FindComponent(tname))<>nil then
     ary:=Tadoquery(datamod.FindComponent(tname));}
  if ary<>nil then aryB:=true;
end;

procedure Tformcanfind.SpnExitClick(Sender: TObject);
begin
  close;
end;

procedure Tformcanfind.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  action:=cafree;
  formcanfind:=nil;
end;

procedure Tformcanfind.FormCreate(Sender: TObject);
begin
  adtB:=false;
  aryB:=false;
  fcount1:=0;
end;

//当重新选取字段时，刷新dbgrid
procedure Tformcanfind.SpnNewshowClick(Sender: TObject);
var fname,sl:String;
    i,j:integer;
    ckx:Tcheckbox;
    cb:boolean;
begin
  sl:='';
  j:=0;
try
  if adtB then
   begin
       for i:=0 to fcount-1 do
       begin
         ckx:=Tcheckbox(findcomponent('check'+inttostr(i)));
         with ckx do
         begin
           if ckx.Checked then
             begin
               j:=j+1;
               if j=1 then
                fname:=adt.Fields[i].fieldName
               else
                fname:=fname+','+adt.Fields[i].fieldName;
             end;
         end;
       end;
       with adoquery1 do
       begin
         close;
         sql.clear;
         sql.add('select '+fname+' from '+tabname+' ');
         open;
       end;
    end
  else if aryB then
   begin
     with ary do
     begin

     end;
   end;
   cb:=changetrue();    //判断条件式是否合格
   if not cb then
     begin
       if trim(combobox1.text)<>'' then
         sl:=sl+adt.Fields[strtoint(copy(combobox1.Text,1,2))-1].fieldName +' '+combobox2.Text +''''+combobox13.Text+'''' ;
       if trim(combobox4.Text)<>'' then
       begin
         if combobox3.Text ='并且' then
           sl:=sl+' and '
         else
           sl:=sl+' or ';
           sl:=sl+adt.Fields[ strtoint(copy(combobox4.Text,1,2))-1].fieldName+' '+combobox5.Text+'''' +combobox14.Text +'''';
           //sl:=sl+adoquery1.Fields[ strtoint(copy(combobox4.Text,1,2))-1].fieldName+' '+combobox5.Text+'''' +combobox14.Text +'''';
       end;
       if trim(combobox7.text)<>'' then
       begin
        if combobox6.Text='并且' then
          sl:=sl+' and '
        else
          sl:=sl+' or ';
        sl:=sl+adt.Fields[strtoint(copy(combobox7.Text,1,2))-1].fieldName +' '+combobox8.Text +''''+combobox15.Text +'''';
       end;
       if trim(combobox11.text)<>'' then
       begin
        if combobox9.Text ='并且' then
          sl:=sl+' and '
        else
          sl:=sl+' or ';
        sl:=sl+adt.Fields[ strtoint(copy(combobox10.Text,1,2))-1].fieldName+' '+combobox11.Text+''''+combobox16.Text+'''' ;
       end;
       with adoquery1 do
       begin
        close;
        sql.Clear;
        sql.add('select '+fname+' from '+tabname+' where '+sl+' ');
        open;
       end;
     end;
  if not adoquery1.eof then
    dbgrid1.Hint :='共有　'+inttostr(adoquery1.RecordCount)+'  条记录';
 if fcount>0 then
   showcolumnname(fcount);
 except
  if adoquery1.Active then
    adoquery1.Close;
 end;
end;

//判断选取的值是否符合条件
function Tformcanfind.changetrue():boolean;