findselect.pas

一个很好用的万能查询控件。部分代码经过更改。

{*******************************************************}
{       查询基类     Unit                               }
{                                                       }
{       Copyright (c) 2001-2001, 广东亿康公司           }
{       实现Form中通用查询功能                          }
{       Athor:乔有钢                                    }
{*******************************************************}
unit FindSelect;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, comctrls, DBGridEh, DBGrids, AdoDB,DBTables;

type
  TfrmFindSelect = class(TForm)
    GroupBox1: TGroupBox;
    Label1: TLabel;
    btnOk: TButton;
    Button2: TButton;
    Label2: TLabel;
    Label3: TLabel;
    dtpTime: TDateTimePicker;
    edtFind: TComboBox;
    cmbIndex: TComboBox;
    procedure btnOkClick(Sender: TObject);
    procedure edtFindKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure Button2Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure cmbIndexChange(Sender: TObject);
    procedure cmbIndexKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure dtpTimeKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);

  private

  public
    slList: TStringList;

    function Load: boolean; virtual; //要改变查询的条件只要该此方法即可
    { Public declarations }
  end;
procedure FindLoad(sSQLText: string);
procedure Find(sIndexStr: string; sFindStr: string);
procedure OpenFindSelectForm(slStringList: TStringList);
var
  frmFindSelect: TfrmFindSelect;

implementation

uses FindClass;

var
  sIndex, sSQL, sGrid, sFind, sSqlText: string;
  bIsOk, bIsTime: boolean;
  FDBGridEh: TDBGridEh;
  FDBGrid: TDBGrid;
  FForm: TForm;
  FAdoQuery: TAdoQuery;
  FQuery: TQuery;
  AdoDataSet: TAdoDataSet;
{$R *.DFM}
  //要使用此模块只要调用FindLoad方法即可

procedure FindLoad(sSQLText: string);
var
  slStrList: TstringList;
  i, j: integer;
begin
  slStrList := TStringList.Create;
  //FForm := TForm(Sender);
  FForm := gFForm;
  sSQL := sSQLText;
 // sGrid := sGridName;
 // FAdoQuery := adqAdoQuery;
  sGrid := gsGridName;
  FAdoQuery := gFAdoQuery;
  FQuery := gFQuery;
  AdoDataSet := gAdoDataSet;
  if gbIsDBGridEh = True then
    with FForm do begin
      for j := 0 to ComponentCount - 1 do
        if Components[j] is TDBGridEh then begin
          if (Components[j] as TDBGridEh).Name = sGrid then begin
            FDBGridEh := TDBGridEh(Components[j]);
            for i := 0 to FDBGridEh.Columns.Count - 1 do
            begin
              slStrList.Add((Components[j] as TDBGridEh).Columns[i].Title.Caption);
            end;
          end;
        end;
    end;
  if gbIsDBGrid = True then
    with FForm do begin
      for j := 0 to ComponentCount - 1 do
        if Components[j] is TDBGrid then begin
          if (Components[j] as TDBGrid).Name = sGrid then begin
            FDBGrid := TDBGrid(Components[j]);
            for i := 0 to FDBGrid.Columns.Count - 1 do
            begin
              slStrList.Add((Components[j] as TDBGrid).Columns[i].Title.Caption);
            end;
          end;
        end;
    end;
  OpenFindSelectForm(slStrList);
  if bIsOk then
    Find(sIndex, sFind);
end;

procedure Find(sIndexStr: string; sFindStr: string);
  procedure OpenData;
  begin
    if FAdoQuery<>nil then
    with FAdoQuery do
    begin
      Close;
      Sql.Clear;
      Sql.Text := sSqlText;
      Open;
      gsCount := IntToStr(FAdoQuery.RecordCount);
    end;
    if AdoDataSet<>nil then
    with AdoDataSet do
    begin
      Close;
      CommandText := sSqlText;
      Open;
      gsCount := IntToStr(AdoDataSet.RecordCount);
    end;
    if FQuery<>nil then
    with FQuery do
    begin
      Close;
      Sql.Clear;
      Sql.Text := sSqlText;
      Open;
      gsCount := IntToStr(FQuery.RecordCount);
    end;
  end;
var
  i: integer;
  sIndex: string;
begin
  if FDBGridEh <> nil then
    with FForm do begin
      for i := 0 to FDBGridEh.Columns.Count - 1 do begin
        if FDBGridEh.Columns[i].Title.Caption = sIndexStr then begin
          sIndex := FDBGridEh.Columns[i].FieldName;
          if pos('where',sSQL)>0 then
          sSQL:= sSQL+ ' and '
          else
          sSQL:= sSQL+ ' where ';
          sSqlText :=  sSQL +  'convert(char(16),' + sIndex + ')' + ' like ''' + sFindStr + '' + '%''  ';
          if bIsTime = True then
            sSqlText := sSQL + 'convert(char(10),' + sIndex + ',121)'+ '=' + QuotedStr(sFindStr);
          OpenData; 
          Exit;
        end;
      end;

    end;
  if FDBGrid <> nil then
    with FForm do begin
      for i := 0 to FDBGrid.Columns.Count - 1 do begin
        if FDBGrid.Columns[i].Title.Caption = sIndexStr then begin
          sIndex := FDBGrid.Columns[i].FieldName;
          if pos('where',sSQL)>0 then
          sSQL:= sSQL+ ' and '
          else
          sSQL:= sSQL+ ' where ';          
          sSqlText :=  sSQL +  'convert(char(16),' + sIndex + ')' + ' like ''' + sFindStr + '' + '%''  ';
          if bIsTime = True then
            sSqlText :=  sSQL  + 'convert(char(10),' + sIndex + ',121)' + '=' + QuotedStr(sFindStr);
          OpenData;
                //如果查询结果为空，可以根据实际情况修改下面的代码   
          Exit;
        end;
      end;

    end;
end;

procedure TfrmFindSelect.btnOkClick(Sender: TObject);
var
  i: integer;
begin
  sFind := Trim(edtFind.Text);
  sIndex := Trim(cmbIndex.Text);
  if gbShowDateTimePicker then
  if (Pos('时间', cmbIndex.Text) > 0) or (Pos('日期', cmbIndex.Text) > 0) then begin
    sFind := FormatDateTime('YYYY-MM-DD', dtpTime.Date);
    bIsTime := True
  end else
    bIsTime := False;
  Close;
  for i := 0 to edtFind.Items.Count - 1 do begin
    if Trim(edtFind.Items[i]) = sFind then
      Exit;
  end;
  edtFind.Items.Add(sFind); //存入以前查过的内容

end;

procedure TfrmFindSelect.edtFindKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if key = VK_RETURN then
    btnOk.Click;

end;

procedure TfrmFindSelect.Button2Click(Sender: TObject);
begin
  bIsOk := False;
  Close;
end;

procedure TfrmFindSelect.FormShow(Sender: TObject);
begin
  load;
  bIsOk := True;
  if cmbIndex.Items.Count > 0 then
    cmbIndex.ItemIndex := 0;
  frmFindSelect.Caption := gsFindFormCaption;
  if (Pos('时间', cmbIndex.Text) > 0) or (Pos('日期', cmbIndex.Text) > 0) then
    dtpTime.Visible := True
  else
    dtpTime.Visible := False;  
end;

procedure TfrmFindSelect.FormCreate(Sender: TObject);
begin
  slList := TStringList.Create;

end;

procedure OpenFindSelectForm(slStringList: TStringList);
begin
  if frmFindSelect = nil then
    frmFindSelect := TfrmFindSelect.Create(nil);
  frmFindSelect.slList := slStringList;
  frmFindSelect.ShowModal;
end;

procedure TfrmFindSelect.cmbIndexChange(Sender: TObject);
begin
  if gbShowDateTimePicker then
  if (Pos('时间', cmbIndex.Text) > 0) or (Pos('日期', cmbIndex.Text) > 0) then
    dtpTime.Visible := True
  else
    dtpTime.Visible := False;
end;

procedure TfrmFindSelect.cmbIndexKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if key = VK_RETURN then begin
    if (Pos('时间', cmbIndex.Text) > 0) or (Pos('日期', cmbIndex.Text) > 0) then
      dtpTime.SetFocus
    else
      edtFind.SetFocus;
  end;
end;

procedure TfrmFindSelect.dtpTimeKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if key = VK_RETURN then
    btnOk.Click;
end;

function TfrmFindSelect.Load: boolean;
var
  i: integer;
begin
  cmbIndex.Items.Clear;
  for i := 0 to slList.Count - 1 do
    cmbIndex.Items.Add(slList[i]);
  if i > 0 then Result := True;
   //改变窗体的字体和默-认输入法
  EdtFind.ImeName := '';
 if gbIsParentFont then begin
  edtFind.Font := gFForm.Font;
  GroupBox1.Font := gFForm.Font;
  label1.Font := gFForm.Font;
  label2.Font := gFForm.Font;
  BtnOk.Font := gFForm.Font;
  Button2.Font := gFForm.Font;
  end;
  if gbShowFindHint then begin
    edtFind.ShowHint := True;
    GroupBox1.ShowHint := True;
    frmFindSelect.ShowHint := True;
    edtFind.Hint := gsFindHint;
    GroupBox1.Hint := gsFindHint;
    frmFindSelect.Hint := gsFindHint;
  end;
  frmFindSelect.Update;
end;


end.