fssearch.~pas

一个快速查询控件

unit fsSearch;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Buttons, DB, ADODB, ComCtrls;

const
  ConditonCount=11;

type
  TCondition=record
    Display:string;
    Condition:string;
    visible:boolean;
  end;
  TDispCondition=record
    Logic:string;
    CompItem:string;
    Condition:string;
    Value:string;
  end;
  pQueryItem=^TQueryItem;
  TQueryItem=record
    QueryStr:string;
    DispCondition:TDispCondition;
  end;

  TfSearchDlg = class(TForm)
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    cbCompItem: TComboBox;
    cbCondition: TComboBox;
    dtCompare: TEdit;
    dtRange1: TEdit;
    dtRange2: TEdit;
    bbOk: TBitBtn;
    bbCancel: TBitBtn;
    SpeedButton1: TSpeedButton;
    SpeedButton2: TSpeedButton;
    SpeedButton3: TSpeedButton;
    cbMultiCondition: TCheckBox;
    ListView1: TListView;
    procedure FormCreate(Sender: TObject);
    procedure bbOkClick(Sender: TObject);
    procedure cbConditionCloseUp(Sender: TObject);
    procedure bbCancelClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure cbCompItemChange(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure cbMultiConditionClick(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure SpeedButton3Click(Sender: TObject);
  private
    { Private declarations }
    Conditions:array [0..ConditonCount-1] of TCondition;
    procedure SetCompareEdit(Simple:boolean);
    function GetCompareState():boolean; //当前是单输入框时为true
    function IsRange():boolean;   //条件是否为范围
    function GetCondition(var left,right:string):string;
    procedure IniWindow();
    function IsDateTime(dt:string):boolean;
    function IsInteger(s:string):boolean;
    function IsReal(s:string):boolean;
    function GetConditionIndex():integer;
    procedure SetCondition();
    function GetFieldType(DataType:TDataType):integer;  //1:字符串类型2:整型3:浮点型（含货币型）4:日期时间型
    procedure SetWindow(Simple:boolean);
    procedure LoadStatus();
    procedure SaveStatus();
    function DoQuery(IsAnd:boolean):boolean; //处理单个查询
    procedure lvDisp();
  public
    { Public declarations }
    QueryStr:string;
    Fields:TFields;
    SearchSuccess:boolean;
    DateSeparator:string;  //日期分格符
    CompItemIndex,ConditionIndex:integer;
    CompareValue,CompareRange1,CompareRange2:string;
    MultiCondition:boolean;
    SaveCondition:boolean;
    QueryList:TList;
  end;

function SearchExec(IniField:TFields;var ResponseStr:string):boolean;
function Search(IniField:TFields;var sql:string):boolean; //不成熟

implementation


{$R *.dfm}

function Search(IniField:TFields;var sql:string):boolean;
var
  where,sOrderBy,s,sSql:string;
  i:integer;
begin
  result:=false;
  if not SearchExec(IniField,where) then exit;
  sSql:=sql;
  if where<>'' then
  begin
    i:=pos('order by',sSql);
    if i<>0 then
    begin
      sOrderBy:=copy(sSql,i,MaxInt);
      s:=copy(sSql,1,i-1);
    end
    else begin
      s:=sSql;
      sOrderBy:='';
    end;
    if pos('where',s)=0 then
      s:=s+' where ('+where+') '+sOrderBy
    else
      s:=s+' and ('+where+') '+sOrderBy;
    sql:=s;
    result:=true;
  end;
end;

function SearchExec(IniField:TFields;var ResponseStr:string):boolean;
var
  fm:TfSearchDlg;
begin
  fm:=TfSearchDlg.Create(application);
  try
    fm.Fields:=IniField;
    fm.Caption:=TForm(iniField.DataSet.Owner).Caption+'查询';
    fm.ShowModal;
    if fm.SearchSuccess then
    begin
      ResponseStr:=fm.QueryStr;
      result:=true;
    end
    else
      result:=false;
  finally
    fm.Free;
  end;
end;

procedure TfSearchDlg.IniWindow();
begin
  conditions[0].Display:='包含';
  Conditions[0].Condition:=' like ';
  conditions[1].Display:='等于';
  Conditions[1].Condition:=' = ';
  conditions[2].Display:='范围';
  Conditions[2].Condition:='';
  conditions[3].Display:='不等于';
  Conditions[3].Condition:=' <> ';
  conditions[4].Display:='不包含';
  Conditions[4].Condition:=' not like ';
  conditions[5].Display:='大于';
  Conditions[5].Condition:=' > ';
  conditions[6].Display:='大于等于';
  Conditions[6].Condition:=' >= ';
  conditions[7].Display:='小于';
  Conditions[7].Condition:=' < ';
  conditions[8].Display:='小于等于';
  Conditions[8].Condition:=' <= ';
  conditions[9].Display:='左边包含';    //'B%'
  Conditions[9].Condition:=' like ';
  conditions[10].Display:='右边包含';
  Conditions[10].Condition:=' like ';
end;

procedure TfSearchDlg.SetCondition();
var
  i,DataType:integer;
begin
  cbCondition.Clear;
  if cbCompItem.Items.Count>0 then
    DataType:=GetFieldType(Fields[cbCompItem.ItemIndex].DataType)
  else begin
    exit;
  end;
  case DataType of
    -1:begin
      Conditions[0].visible:=true;
      Conditions[1].visible:=true;
      Conditions[2].visible:=true;
      Conditions[3].visible:=true;
      Conditions[4].visible:=true;
      Conditions[5].visible:=true;
      Conditions[6].visible:=true;
      Conditions[7].visible:=true;
      Conditions[8].visible:=true;
      Conditions[9].visible:=true;
      Conditions[10].visible:=true;
    end;
    1:begin
      Conditions[0].visible:=true;
      Conditions[1].visible:=true;
      Conditions[2].visible:=true;
      Conditions[3].visible:=true;
      Conditions[4].visible:=true;
      Conditions[5].visible:=true;
      Conditions[6].visible:=true;
      Conditions[7].visible:=true;
      Conditions[8].visible:=true;
      Conditions[9].visible:=true;
      Conditions[10].visible:=true;
    end;
    2:begin
      Conditions[0].visible:=false;
      Conditions[1].visible:=true;
      Conditions[2].visible:=true;
      Conditions[3].visible:=true;
      Conditions[4].visible:=false;
      Conditions[5].visible:=true;
      Conditions[6].visible:=true;
      Conditions[7].visible:=true;
      Conditions[8].visible:=true;
      Conditions[9].visible:=false;
      Conditions[10].visible:=false;
    end ;
    3:begin
      Conditions[0].visible:=false;
      Conditions[1].visible:=true;
      Conditions[2].visible:=true;
      Conditions[3].visible:=true;
      Conditions[4].visible:=false;
      Conditions[5].visible:=true;
      Conditions[6].visible:=true;
      Conditions[7].visible:=true;
      Conditions[8].visible:=true;
      Conditions[9].visible:=false;
      Conditions[10].visible:=false;
    end;
    4:begin
      Conditions[0].visible:=false;
      Conditions[1].visible:=true;
      Conditions[2].visible:=true;
      Conditions[3].visible:=true;
      Conditions[4].visible:=false;
      Conditions[5].visible:=true;
      Conditions[6].visible:=true;
      Conditions[7].visible:=true;
      Conditions[8].visible:=true;
      Conditions[9].visible:=false;
      Conditions[10].visible:=false;
    end;
  else
    exit;
  end; //end case
  for i:=low(conditions) to high(Conditions) do
  begin
    if Conditions[i].visible then
      cbCondition.Items.Add(Conditions[i].Display);
  end; //end for
  cbCondition.ItemIndex:=0;
end;

function TfSearchDlg.GetFieldType(DataType:TDataType):integer;
begin
  result:=0;
  if DataType in [ftString, ftMemo, ftFmtMemo, ftFixedChar, ftWideString] then
  begin
    //1字符串类型 //整型 //浮点型（含货币型）//日期时间型
    result:=1;
    exit;
  end;
  if DataType in [ftSmallint, ftInteger, ftWord,ftAutoInc, ftLargeInt, ftArray] then
  begin
    //整型
    result:=2;
    exit;
  end;
  if DataType in [ftFloat, ftCurrency, ftBCD] then
  begin
    //浮点型（含货币型）
    result:=3;
    exit;
  end;
  if DataType in [ftDate, ftDateTime] then
  begin
    //日期时间型
    result:=4;
  end;
end;

procedure TfSearchDlg.SetWindow(Simple:boolean);
begin
  if Simple then
  begin
    self.Height:=100;
  end
  else begin
    self.Height:=214;
  end;
  {
  if (cbOr.Checked) or (cbAnd.Checked) then
  begin
    self.Height:=146;
    bbOk.Top:=64;
    bbCancel.Top:=64;
  end
  else begin
    self.Height:=120;
    bbOk.Top:=48;
    bbCancel.Top:=48;
  end;
  }
end;

function TfSearchDlg.GetConditionIndex():integer;
var
  i:integer;
  s:string;
begin
  result:=-1;
  s:=cbCondition.Text;
  for i:=low(Conditions) to high(conditions) do
  begin
    if Conditions[i].Display=s then
    begin
      result:=i;
      break;
    end;
  end; //end for
end;

function TfSearchDlg.GetCondition(var left,right:string):string;
var
  index:integer;
begin
  left:='';
  right:='';
  index:=GetConditionIndex();
  result:=Conditions[Index].Condition;
  if Index in [0,4,10] then
    left:='%';
  if Index in [0,4,9] then
    right:='%';
end;

function TfSearchDlg.IsRange():boolean;
begin
  if GetConditionIndex()=2 then
    result:=true
  else
    result:=false;
end;

function TfSearchDlg.IsDateTime(dt:string):boolean;
begin
  try
    StrToDateTime(dt);
    result:=true;
  except
    result:=false;
  end; //end try
end;

function TfSearchDlg.IsInteger(s:string):boolean;
var
  i,Error:integer;
begin
  val(s,i,Error);
  result:=(Error=0);
end;

function TfSearchDlg.IsReal(s:string):boolean;