unit_superquery.pas

万能查询主要代码, 可按配置无限选择查询条件,自动生成SQL语句.

unit Unit_SuperQuery;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Buttons, ComCtrls, dxdbtrel, unit_func, ExtCtrls, jpeg;

const
   CompOperatorMax = 8;
   RelaOperatorMax = 4;
   ControlNamesMax = 4;

type
  TForm_SuperQuery = class(TForm)
    Panel1: TPanel;
    Image1: TImage;
    SpeedButton1: TSpeedButton;
    SpeedButton2: TSpeedButton;
    Panel2: TPanel;
    Label3: TLabel;
    Label2: TLabel;
    Label1: TLabel;
    Label4: TLabel;
    Image2: TImage;
    procedure FormShow(Sender: TObject);
    procedure SpeedButton2Click(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
  private
    { Private declarations }
    g_row_Index :Integer;
    procedure FieldBoxChange(Sender: TObject); //比较字段  OnChange
    procedure CompBoxChange(Sender: TObject); //比较运算符 OnChange
    procedure RelaBoxChange(Sender: TObject); //关系运算符 OnChange
    procedure DeleteControls(rowIndex :Integer); //删除控件
    procedure CreateControl(ControlName :String; cType :TSupperQueryType; left :Integer; top :Integer; width :Integer);//
    procedure CreateControls(rowIndex :Integer);//生成控件
    procedure NumberCompValueKeyPress(Sender: TObject; var Key: Char);//数字比较值按键事件
    procedure NumberCompValueExit(Sender: TObject);//数字比较值焦点离开事件
    function createSQL(rowIndex :Integer) :String;//生成SQL
  protected
    procedure WndProc(var m:TMessage); override;//
  public
    { Public declarations }
    resultSQL :String;
    ConfigSQLData :array of TSupperQuerySQLData;
  end;

var
  Form_SuperQuery: TForm_SuperQuery;

const
   CompOperator :array[0..1, 0..CompOperatorMax - 1] of String = //比较运算符
   (
     ('=', '>', '<', '>=', '<=', '<>', 'like', 'havechild'),
     ('等于', '大于', '小于', '大于等于', '小于等于', '不等于', '包含', '包含子项')
   );
   RelaOperator :array[0..1, 0..RelaOperatorMax - 1] of String = //关系运算符
   (
     (' ', 'and', 'or', 'not'),
     (' ', '与', '或', '非')
   );
   ControlNames :array[0..ControlNamesMax - 1] of String = //控件名称
   (
     'CompField', 'CompOperator', 'CompValue', 'RelaOperator'
   );
   xHeight :Integer = 25;//高

implementation
   uses unit_main;

{$R *.dfm}

function getNumberIndex(str :String) :Integer;
var
   i :Integer;
begin
   result := 0;
   for i := 1 to Length(str) do
   begin
      if str[i] in['0'..'9'] then
      begin
         result := i;
         Break;
      end;
   end;
end;

function getTDateTimePickerText(DateTimePicker :TDateTimePicker) :String;
var
   l :Integer;
   c :array of char;
begin
   l := sendMessage(DateTimePicker.Handle, WM_GETTEXTLENGTH, 0, 0);
   setLength(c, l + 1);
   sendMessage(DateTimePicker.Handle, WM_GETTEXT, l + 1, longInt(c));

   result := '';
   for l := low(c) to high(c) - 1 do result := result + c[l]; 
end;

procedure TForm_SuperQuery.WndProc(var m :TMessage);
begin
   if m.Msg = WM_SIZING then
   begin
      Abort;
   end else
   begin
      inherited;
   end;
end;

function TForm_SuperQuery.createSQL(rowIndex :Integer) :String;
var
   i :Integer;
   cC :TComponent;
   aC,bC,dC :TComboBox;
   aV,bV,cV,dV :String;
   function isNumber(value :String) :Boolean;
   begin
      result := true;
      try
        StrToInt(value);
      except
         result := false;
      end;
   end;
begin
   result := ' ';
   for i := 1 to rowIndex do
   begin
      aC := (FindComponent(ControlNames[0] + IntToStr(i)) as TComboBox);
      bC := (FindComponent(ControlNames[1] + IntToStr(i)) as TComboBox);
      dC := (FindComponent(ControlNames[3] + IntToStr(i)) as TComboBox);
      aV := String(aC.Items.Objects[aC.ItemIndex]);
      bV := String(bC.Items.Objects[bC.ItemIndex]);
      //比较类型[cText cDate cNumber cTree cList]
      cC := FindComponent(ControlNames[2] + IntToStr(i));
      case ConfigSQLData[aC.ItemIndex].cType of
         cNumber :
         begin
            cV := (cC as TEdit).Text;
         end;
         cText :
         begin
            if UpperCase(bV) = 'LIKE' then
               cV := '''%'+ (cC as TEdit).Text +'%'''
            else
               cV := ''''+ (cC as TEdit).Text +'''';
         end;
         cDate :
         begin
            cV := ''''+ getTDateTimePickerText(cC as TDateTimePicker) +'''';
         end;
         cTree :
         begin
            cV := IntToStr(Integer((cC as TdxTreeViewEdit).Selected.Data));
         end;
         cList :
         begin
            cV := String((cC as TComboBox).Items.Objects[(cC as TComboBox).ItemIndex]);
            if not isNumber(cV) then cV := ''''+ cV +'''';
         end;
      end;
      //关系符
      dV := String(dC.Items.Objects[dC.ItemIndex]);

      if trim(dV) <> '' then
      begin
         if trim(dV) = 'not' then
            dV := ' and '+ dV +' '
         else
            dV := ' '+ dV +' ';
      end;
      //
      if ConfigSQLData[aC.ItemIndex].HaveChild then 
         result := result + StringReplace(aV, '%s', cV, [rfReplaceAll, rfIgnoreCase]) + dV
      else
         result := result + Format(aV, [bV, cV]) + dV;
   end;
end;

procedure TForm_SuperQuery.NumberCompValueKeyPress(Sender: TObject; var Key: Char);
begin
   if (not (Key in['.', '0'..'9'])) and (Ord(Key) <> 8) then Abort;
end;

procedure TForm_SuperQuery.NumberCompValueExit(Sender: TObject);
begin
   if trim((Sender as TEdit).Text) = ''then
   begin
      (Sender as TEdit).Text := '0';
   end else if Copy(trim((Sender as TEdit).Text), 1, 1) = '.'then
   begin
      (Sender as TEdit).Text := '0'+ (Sender as TEdit).Text;
   end;
end;

procedure TForm_SuperQuery.FieldBoxChange(Sender: TObject);
var
   t,i :Integer;
   tI,cName :String;
   CompType :TSupperQueryType;
   NewNode,ParentNode :TTreeNode;
   haveChild :Boolean;
   //添加下拉值
   procedure addComboBoxValue(ComboBox :TComboBox);
   var
      ii :Integer;
      b :array[0..CompOperatorMax -1] of Boolean;
   begin
      //[cText cDate cNumber cTree cList]
      //['=','>','<','>=','<=','<>','like']
      for ii := 0 to CompOperatorMax - 1 do b[ii] := true;
      case CompType of
         cDate,cNumber :
         begin
            b[6] := false;
            b[7] := false;            
         end;
         cText :
         begin
            b[1] := false;
            b[2] := false;
            b[3] := false;
            b[4] := false;
            b[7] := false; 
         end;
         cTree,cList :
         begin
            b[1] := false;
            b[2] := false;
            b[3] := false;
            b[4] := false;
            b[6] := false;
            b[7] := false;
            if haveChild then
            begin
               b[0] := false;
               b[5] := false;
               b[7] := true;
            end;
         end;
      end;

      ComboBox.Items.Clear;
      for ii := 0 to CompOperatorMax - 1 do
      begin
         if not b[ii] then Continue;
         ComboBox.AddItem(CompOperator[1][ii], TObject(CompOperator[0][ii]));
      end;
      ComboBox.ItemIndex := 0;
      ComboBox.OnChange := CompBoxChange;
   end;
   //
   function getParentNode(Items :TTreeNodes; id :Integer) :TTreeNode;
   var
      ii :Integer;
   begin
      result := nil;
      for ii := 0 to Items.Count - 1 do
      begin
         if Integer(Items[ii].Data) = id then
         begin
            result := Items[ii];
            Break;
         end;
      end;
   end;
begin
   cName := (Sender as TComboBox).Name;
   tI := Copy(cName, getNumberIndex(cName), Length(cName));
   CompType := ConfigSQLData[(Sender as TComboBox).ItemIndex].cType;
   haveChild := ConfigSQLData[(Sender as TComboBox).ItemIndex].HaveChild;
   //
   cName := ControlNames[1] + tI;
   addComboBoxValue((FindComponent(cName) as TComboBox));
   //
   cName := ControlNames[2] + tI;
   if StrToInt(tI) < 2 then
   begin
      t := 0;