sqlbuilder.pas

D6+SQL2000 通用自定义查询.

unit SQLBuilder;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Controls, Forms, DB, ADODB, StdCtrls, Mask, DBCtrlsEh, DBGridEh, DBLookupEh,
  ToolCtrlsEh, StrUtils, DBClient, ExtCtrls, Grids, Dialogs;

type
  TSQLBuilderForm = class(TForm)
    pnl1: TPanel;
    pnl2: TPanel;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    cboDisplayName: TDBComboBoxEh;
    cboOpt: TDBComboBoxEh;
    btnAdd: TButton;
    btnDel: TButton;
    btnReplace: TButton;
    btnEmpty: TButton;
    cboLogic: TDBComboBoxEh;
    dbgFilter: TDBGridEh;
    FilterList: TClientDataSet;
    FilterListcLogic: TStringField;
    FilterListcFieldName: TStringField;
    FilterListcOpt: TStringField;
    FilterListcValue: TStringField;
    dsFilterList: TDataSource;
    pnl3: TPanel;
    btnOK: TButton;
    btnCancel: TButton;
    procedure FormCreate(Sender: TObject);
    procedure cboDisplayNameChange(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure btnEmptyClick(Sender: TObject);
    procedure btnAddClick(Sender: TObject);
    procedure btnDelClick(Sender: TObject);
    procedure btnReplaceClick(Sender: TObject);
    procedure btnOKClick(Sender: TObject);
    procedure btn1Click(Sender: TObject);
  private
    { Private declarations }
    FValueCtrl:TWinControl;
    FDataSet:TADOQuery;
  {-------------------------------------------------------------------------------
    过程名:    SetFieldList
    功能:      设置要显示的项目（字段）列表
    作者:      tuchaoqian
    日期:      2005.05.16
    参数:      无
    返回值:    无
  -------------------------------------------------------------------------------}
  procedure SetFieldList;

    {-------------------------------------------------------------------------------
    过程名:    SetOptList
    功能:      设置不同字段类型的操作符
    作者:      tuchaoqian
    日期:      2005.05.16
    参数:      FField:TField
    返回值:    无
  -------------------------------------------------------------------------------}
    procedure SetOptList(FField:TField);
    
    {-------------------------------------------------------------------------------
    过程名:    SetValueCtrl
    功能:      设置不同字段类型显示值的控件
    作者:      tuchaoqian
    日期:      2005.05.16
    参数:      FField:TField
    返回值:    无
    -------------------------------------------------------------------------------}
    procedure SetValueCtrl(FField:TField);
    
    {-------------------------------------------------------------------------------
      过程名:    AddFilter
      功能:      添加条件
      作者:      tuchaoqian
      日期:      2005.05.16
      参数:      无
      返回值:    无
    -------------------------------------------------------------------------------}
    procedure AddFilter;
    
    {-------------------------------------------------------------------------------
      过程名:    SetLogicState
      功能:      设置逻辑关系控件可用
      作者:      tuchaoqian
      日期:      2005.05.16
      参数:      无
      返回值:    无
    -------------------------------------------------------------------------------}
    procedure SetLogicState;
    //返回实际的逻辑操作符
    function ReverseLogic(cLogic:string):string;
    //返回实际字段名，bFullPath是否包含表的全名
    function ReverseFieldName(cDisplayName:string;bFullPath:Boolean):string;
    //返回实际的操作符
    function ReverseOpt(cOpt:string):string;
    //得到最终的SQL语句
    function GetSQLStr:String;
  public
    constructor Create(DataSet:TADOQuery);
    { Public declarations }
  end;

var
  SQLBuilderForm: TSQLBuilderForm;

implementation


{$R *.dfm}

procedure TSQLBuilderForm.FormCreate(Sender: TObject);
begin
  SetFieldList;
  cboLogic.ItemIndex :=0;
  cboOpt.ItemIndex:=0;
end;

procedure TSQLBuilderForm.SetFieldList;
var
  i:integer;
begin
  cboDisplayName.Clear;
  for i:=0 to FDataSet.FieldCount-1 do
    cboDisplayName.Items.Add(FDataSet.Fields[i].DisplayName);
  cboDisplayName.ItemIndex:=0;
end;

procedure TSQLBuilderForm.cboDisplayNameChange(Sender: TObject);
var
  i:integer;
begin
  for i:=0 to FDataSet.FieldCount-1 do
  begin
    if FDataSet.Fields[i].DisplayName=cboDisplayName.Text then
    begin
      SetOptList(FDataSet.Fields[i]);
      SetValueCtrl(FDataSet.Fields[i]);
    end;
  end;
end;

procedure TSQLBuilderForm.SetOptList(FField:TField);
begin
  cboOpt.Items.Clear;
  case  FField.DataType of
    ftString,ftFixedChar, ftWideString,ftMemo:
      begin
        cboOpt.Items.Add('等于');
        cboOpt.Items.Add('不等于');
        cboOpt.Items.Add('大于');
        cboOpt.Items.Add('大于等于');
        cboOpt.Items.Add('小于');
        cboOpt.Items.Add('小于等于');
        cboOpt.Items.Add('始于');
        cboOpt.Items.Add('并非起始于');
        cboOpt.Items.Add('止于');
        cboOpt.Items.Add('并非结束于');
        cboOpt.Items.Add('包含');
        cboOpt.Items.Add('不包含');
      end;
    ftBoolean:
      begin
        cboOpt.Items.Add('等于');
        cboOpt.Items.Add('不等于');
      end;
    ftDate, ftTime, ftDateTime:
      begin
        cboOpt.Items.Add('等于');
        cboOpt.Items.Add('不等于');
        cboOpt.Items.Add('大于');
        cboOpt.Items.Add('大于等于');
        cboOpt.Items.Add('小于');
        cboOpt.Items.Add('小于等于');
      end;
    ftLargeint, ftFMTBcd,ftSmallint, ftInteger, ftWord,ftBCD,ftFloat,ftCurrency,ftAutoInc:
      begin
        cboOpt.Items.Add('等于');
        cboOpt.Items.Add('不等于');
        cboOpt.Items.Add('大于');
        cboOpt.Items.Add('大于等于');
        cboOpt.Items.Add('小于');
        cboOpt.Items.Add('小于等于');
      end;
    else
      showmessage('未知错误,请联系软件供应商');
  end;
end;

procedure TSQLBuilderForm.SetValueCtrl(FField: TField);
var
  i:integer;
begin
  if FValueCtrl<>nil then FValueCtrl:=nil;
  case  FField.FieldKind of
    fkData,fkCalculated:
      begin
        case  FField.DataType of
          ftString,ftFixedChar, ftWideString,ftMemo:
            begin
              FValueCtrl:=TDBEditEh.Create(Self);
              With TDBEditEh(FValueCtrl) do
              begin
                FField.DataSize;
              end;
            end;
          ftBoolean:
            begin
              FValueCtrl:=TDBComboBoxEh.Create(Self);
              with TDBComboBoxEh(FValueCtrl) do
              begin
                Items.Add('是');
                Items.Add('否');
                DropDownBox.Sizable:=True;
              end;
            end;
          ftDate, ftTime, ftDateTime:
            begin
              FValueCtrl:=TDBDateTimeEditEh.Create(Self);
              with TDBDateTimeEditEh(FValueCtrl) do
              begin
                Kind:=dtkDateTimeEh;
              end;
            end;
          ftLargeint, ftFMTBcd,ftSmallint, ftInteger, ftWord,ftBCD,ftAutoInc,ftFloat,ftCurrency:
            begin
              FValueCtrl:=TDBNumberEditEh.Create(Self);
              with TDBNumberEditEh(FValueCtrl) do
              begin
                EditButton.Style:=ebsPlusEh;
                EditButton.Visible:=True;
                if FField is TIntegerField then
                  DisplayFormat:=TIntegerField(FField).DisplayFormat;
                if FField is TFloatField then
                  DisplayFormat:=TFloatField(FField).DisplayFormat;
                  DecimalPlaces:=3;
                if FField is TCurrencyField then
                begin
                  DisplayFormat:=TCurrencyField(FField).DisplayFormat;
                  currency:=True;
                end;
              end;
            end;
        end;
      end;
    fkLookup:
      begin
        FValueCtrl:=TDBLookupComboboxEh.Create(Self);
        With TDBLookupComboboxEh(FValueCtrl) do
        begin
          DropDownBox.Sizable:=True;
          if not FField.LookupDataSet.Active then FField.LookupDataSet.Active:=True;