sqlbuilder.pas

D6+SQL2000 通用自定义查询.

          for i:=0 to FDataSet.Owner.ComponentCount-1 do
            if ((FDataSet.Owner.Components[i] is TDataSource)) and
               ((FDataSet.Owner.Components[i] as TDataSource).DataSet=FField.LookupDataSet) then
                  ListSource:=TDataSource(FDataSet.Owner.Components[i]);
          ListField:=FField.LookupResultField;
          KeyField:=FField.LookupKeyFields;
        end;
      end ;
  end;
  if FValueCtrl<>nil then
  with FValueCtrl do
  begin
    Parent:=Self;
    Left:=Label3.Left+Label3.Width+(cboOpt.Left-Label2.Left-label2.Width);
    Top:=cboOpt.Top;
    Width:=145;
  end;
end;

procedure TSQLBuilderForm.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  if FValueCtrl<>nil then FValueCtrl:=nil;
end;

procedure TSQLBuilderForm.btnEmptyClick(Sender: TObject);
begin
  while FilterList.RecordCount>0 do
  begin
    FilterList.Delete;
  end;
  SetLogicState;
end;

procedure TSQLBuilderForm.AddFilter;
begin
  if FValueCtrl=nil then Exit;
  if cboDisplayName.Text='' then
  begin
    showmessage('请选择项目');
    cboDisplayName.SetFocus;
    Exit;
  end;
  if cboOpt.Text='' then
  begin
    showmessage('请选择比较符');
    cboOpt.SetFocus;
    Exit;
  end;
  if TCustomDBEditEh(FValueCtrl).Text='' then
  begin
    showmessage('请输入/选择值');
    FValueCtrl.SetFocus;
    Exit;
  end;
  FilterList.AppendRecord([ifthen(cboLogic.Enabled,cboLogic.Text,''),
    cboDisplayName.Text,cboOpt.Text,TCustomDBEditEh(FValueCtrl).Text]);
end;


procedure TSQLBuilderForm.btnAddClick(Sender: TObject);
begin
  AddFilter;
  SetLogicState;
end;

procedure TSQLBuilderForm.SetLogicState;
begin
  with FilterList do
  begin
    cboLogic.Enabled:=(RecordCount>0);
    if RecordCount>0 then
    begin
      First;
      if FieldByName('cLogic').AsString<>'' then
      begin
        Edit;
        FieldByName('cLogic').AsString:='';
        Post;
      end;
    end;
  end;
end;

procedure TSQLBuilderForm.btnDelClick(Sender: TObject);
begin
  if FilterList.RecordCount>0 then
    FilterList.Delete;
  SetLogicState;
end;

procedure TSQLBuilderForm.btnReplaceClick(Sender: TObject);
begin
if FValueCtrl=nil then Exit;
  if cboDisplayName.Text='' then
  begin
    showmessage('请选择项目');
    cboDisplayName.SetFocus;
    Exit;
  end;
  if cboOpt.Text='' then
  begin
    showmessage('请选择比较符');
    cboOpt.SetFocus;
    Exit;
  end;
  if TCustomDBEditEh(FValueCtrl).Text='' then
  begin
    showmessage('请输入/选择值');
    FValueCtrl.SetFocus;
    Exit;
  end;
  with FilterList do
  if RecordCount=0 then
    AppendRecord([ifthen(cboLogic.Enabled,cboLogic.Text,''),
      cboDisplayName.Text,cboOpt.Text,TCustomDBEditEh(FValueCtrl).Text])
  else begin
    Edit;
    FieldByName('cLogic').AsString:=ifthen(cboLogic.Enabled,cboLogic.Text,'');
    FieldByName('cDisplayName').AsString:=cboDisplayName.Text;
    FieldByName('cOpt').AsString:=cboOpt.Text;
    FieldByName('cValue').AsString:=TCustomDBEditEh(FValueCtrl).Text;
    Post;
  end;
  SetLogicState;
end;

function TSQLBuilderForm.ReverseFieldName(cDisplayName: string;bFullPath:Boolean): string;
var
  i:integer;
begin
  with FDataSet do
  for i:=0 to FieldCount-1 do
  begin
    if Fields[i].DisplayName=cDisplayName then
    begin
      case Fields[i].FieldKind of
      fkData:Result:=IfThen(bFullPath,Fields[i].DataSet.Name+'.','')
        +Fields[i].FieldName;
      fkLookup:Result:=IfThen(bFullPath,Fields[i].LookupDataSet.Name+'.','')
        +Fields[i].LookupResultField;
      end;
      Break;
    end;
  end;
end;

function TSQLBuilderForm.ReverseLogic(cLogic: string): string;
begin
  if cLogic='并且' then Result:=' And ';
  if cLogic='或者' then Result:=' Or '
end;

function TSQLBuilderForm.ReverseOpt(cOpt: string): string;
begin
  if cOpt='等于' then Result:='=';
  if cOpt='不等于' then Result:='<>';
  if cOpt='大于' then Result:='>';
  if cOpt='大于等于' then Result:='>=';
  if cOpt='小于' then Result:='<';
  if cOpt='小于等于' then Result:='<=';
  if cOpt='始于' then Result:=cOpt;
  if cOpt='并非起始于' then Result:=cOpt;
  if cOpt='止于' then Result:=cOpt;
  if cOpt='并非结束于' then Result:=cOpt;
  if cOpt='包含' then Result:=cOpt;
  if cOpt='不包含' then Result:=cOpt;
end;

function TSQLBuilderForm.GetSQLStr: String;
var
  SQLStr,JoinStr,WhereStr:string;
  cOptDisplay,cLogic,cFieldName,cOpt,cValue:string;
  FilterStr:string;
  ft:TFieldType;
  Sept:String;
begin
  SQLStr:='Select * From '+FDataSet.Name+' ';
  JoinStr:='';
  WhereStr:='';
  //生成连接串
  with FilterList do
  begin
    First;
    while not Eof do
    begin
      with FDataSet.FieldByName(ReverseFieldName(FieldByName('cDisplayName').AsString,False)) do
      if FieldKind=fkLookup then
        JoinStr:=JoinStr+' Inner Join '+LookupDataSet.Name+' On '
          +DataSet.Name+'.'+KeyFields+'='+LookupDataSet.Name
          +'.'+LookupKeyFields;
      Next;
    end;
  end;
  //生成实际的条件表达式
  with FilterList do
  begin
    First;
    while not Eof do
    begin
      cOptDisplay:=FieldByName('cOpt').AsString;
      cOpt:=ReverseOpt(FieldByName('cOpt').AsString);
      cLogic:=ReverseLogic(FieldByName('cLogic').AsString);
      cFieldName:=ReverseFieldName(FieldByName('cDisplayName').AsString,True);
      cValue:=FieldByName('cValue').AsString;
      ft:=FDataSet.FieldByName(ReverseFieldName(FieldByName('cDisplayName').AsString,False)).DataType;
      case ft of
      ftString,ftFixedChar, ftWideString,ftMemo,ftDate, ftTime, ftDateTime:Sept:='''';
      ftBoolean,ftLargeint, ftFMTBcd,ftSmallint, ftInteger,
        ftWord,ftBCD,ftFloat,ftCurrency,ftAutoInc:Sept:='';
      end;
      if ft=ftBoolean then cValue:=IfThen(cValue='是','True','False');
      if cOpt<>cOptDisplay then
        FilterStr:=cLogic+cFieldName+cOpt+Sept+cValue+Sept
      else begin
        if cOpt='始于' then
          FilterStr:=cLogic+cFieldName+' Like '''+cValue+'%''';
        if cOpt='并非起始于' then
          FilterStr:=cLogic+' not '+cFieldName+' Like '''+cValue+'%''';
        if cOpt='止于' then
          FilterStr:=cLogic+cFieldName+' Like ''%'+cValue+'''';
        if cOpt='并非结束于' then
          FilterStr:=cLogic+' not '+cFieldName+' Like ''%'+cValue+'''';
        if cOpt='包含' then
          FilterStr:=cLogic+cFieldName+' Like ''%'+cValue+'%''';
        if cOpt='不包含' then
          FilterStr:=cLogic+' not '+cFieldName+' Like ''%'+cValue+'%''';
      end;
      WhereStr:=WhereStr+FilterStr;
      Next;
    end;
    if WhereStr<>'' then WhereStr:=' Where '+WhereStr;
  end;
  Result:=SQLStr+IfThen(JoinStr='',' ',JoinStr)
    +IfThen(WhereStr='',' ',WhereStr);
end;

procedure TSQLBuilderForm.btnOKClick(Sender: TObject);
begin
  FDataSet.SQL.Text:=GetSQLStr;
end;

procedure TSQLBuilderForm.btn1Click(Sender: TObject);
begin
  FDataSet.Active:=False;
  FDataSet.SQL.Text:=GetSQLStr;
  FDataSet.Active:=True;
end;

constructor TSQLBuilderForm.Create(DataSet: TADOQuery);
begin
  FDataSet:=DataSet;
  inherited Create(Application);
end;

end.