query.pas

delphi开发的中国移动大客户管理系统,后台数据库为oracle

//------------------------------------------------------------
//      作者:曾庆顺
//      模块:通用查询窗口
//      时间：2001.07.15
//      功能介绍：P_Query(Dbgrid:TDbGrid);
//                DbGRid对应的数据控件---标准控件
//-----------------------------------------------------------
unit query;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls,DBGrids,DB;

type
  TQueryFrm = class(TForm)
    GroupBox1: TGroupBox;
    CB_Field: TComboBox;
    RB_and: TRadioButton;
    RB_or: TRadioButton;
    CB_Logical: TComboBox;
    CB_Values: TComboBox;
    Label1: TLabel;
    Btn_Vert: TButton;
    Btn_Add: TButton;
    Btn_Delete: TButton;
    Btn_Query: TButton;
    ListBox1: TListBox;
    Btn_Close: TButton;
    procedure FormShow(Sender: TObject);
    procedure CB_FieldChange(Sender: TObject);
    procedure Btn_VertClick(Sender: TObject);
    procedure Btn_CloseClick(Sender: TObject);
    procedure CB_LogicalChange(Sender: TObject);
    procedure ListBox1Click(Sender: TObject);
    procedure CB_ValuesChange(Sender: TObject);
    procedure Btn_AddClick(Sender: TObject);
    procedure Btn_DeleteClick(Sender: TObject);
    procedure Btn_QueryClick(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    { Private declarations }
    FFilter:string;
    FFiltered:Boolean;
    FList,FListV:Array of TStringList;
    //得到字段的名
    Function GetFieldName(FindName:string):string;
    //得到查找字段的KeyFields名
    Function GetKeyFieldName(FindName:string):string;
    //的到查找字段的索引位置
    Function GetKeyFieldPos(FindName:string):integer;
  public
    { Public declarations }
    FDbgrid:TDbGrid;
  end;
  procedure P_Query(Dbgrid:TDbGrid);
var
  QueryFrm: TQueryFrm;

implementation

{$R *.DFM}
procedure P_Query(Dbgrid:TDbGrid);
var dlg:TQueryfrm;
begin
    if not Dbgrid.DataSource.DataSet.Active then exit;
    dlg:=tqueryfrm.Create(nil);
    dlg.FDbgrid:=TDbGrid(DBGrid);
    dlg.ShowModal;
end;
procedure TQueryFrm.FormDestroy(Sender: TObject);
var i:integer;
begin
    for i:=0 to FDbGrid.Columns.Count -1 do
    begin
       if FList[i] <> nil then FList[i].Free;
       if FListV[i] <> nil then FListV[i].Free;
    end;
    FList:=nil;
    FListV:=nil;
end;

procedure TQueryFrm.FormShow(Sender: TObject);
var i:integer;
    str:string;
begin
   Setlength(FList,FDbGrid.Columns.Count);
   Setlength(FListV,FDbGrid.Columns.Count);
   for i:=0 to FDbGrid.Columns.Count -1 do
   begin
      FList[i]:=nil;
      FListV[i]:=nil;
   end;

   //保存旧过滤条件
   FFilter:=FDbGrid.DataSource.DataSet.Filter;
   FFiltered:=FDbGrid.DataSource.DataSet.Filtered;
   //取字段
   CB_Field.Clear;
   for i:=0 to FDbGrid.Columns.Count -1 do
   begin
     //如果为数据字段和计算字段则增加
     //如果SIZE>255 则是MEMO大字段不可过滤
     //和奇怪的事情，如果传入的grid为继成的窗体的FDbGrid.Fields[0]出错
     try
       str:=FDbGrid.Fields[i].KeyFields;
     except
       continue;
     end;
       if FDbGrid.Fields[i].Size > 255 then continue;
       if (FDbGrid.Fields[i].FieldKind = fkData) or (FDbGrid.Fields[i].FieldKind = fkLookup) then
       begin
          //判断是否还是查找字段
          if (FDbGrid.Fields[i].FieldKind = fkLookup) then
          begin
             str:=FDbGrid.Fields[i].KeyFields;
             if FDbGrid.DataSource.DataSet.FieldByName(str).FieldKind = fkLookup then continue;
          end;

          CB_Field.Items.Add(trim(FDbGrid.Columns[i].Title.Caption));
          if FDbGrid.Fields[i].FieldKind = fkLookup then
          begin
            FList[i]:=TStringList.Create;
            FListV[i]:=TStringList.Create;
          end;
       end;
   end;
end;
Function TQueryFrm.GetFieldName(FindName:string):string;
var i:integer;
begin
   result:='';
   for i:=0 to FDbGrid.Columns.Count -1 do
   begin
     if trim(FDbGrid.Columns[i].Title.Caption) = FindName then
     begin
        result:= FDbGrid.Columns[i].FieldName;
        break;
     end;
   end;
end;
Function TQueryFrm.GetKeyFieldName(FindName:string):string;
var i:integer;
begin
   result:='';
   for i:=0 to FDbGrid.Columns.Count -1 do
   begin
     if trim(FDbGrid.Columns[i].Title.Caption) = FindName then
     begin
        result:= FDbGrid.Columns[i].Field.KeyFields;
        break;
     end;
   end;
end;

Function TQueryFrm.GetKeyFieldPos(FindName:string):integer;
var i:integer;
begin
   result:=-1;
   for i:=0 to FDbGrid.Columns.Count -1 do
   begin
     if trim(FDbGrid.Columns[i].Title.Caption) = FindName then
     begin
        result:= i;
        break;
     end;
   end;
end;
procedure TQueryFrm.CB_FieldChange(Sender: TObject);
var 
    Bookmark: TBookmark;
    lItem:string;
    FieldName,KeyFieldName:string;
    pos:integer;
begin
    CB_Values.Clear;
    FDbGrid.DataSource.DataSet.DisableControls;
    Bookmark:=FDbGrid.DataSource.DataSet.GetBookmark;
    FDbGrid.DataSource.DataSet.First;
    //得到字段名
    FieldName:=GetFieldName(CB_Field.text);
    //判断是否是查找字段
    if FDbGrid.DataSource.DataSet.FieldByName(FieldName).FieldKind = fkLookup then
    begin
      pos:=GetKeyFieldPos(CB_Field.text);
      FList[pos].Clear;
      FListV[pos].Clear;
      KeyFieldName:=GetKeyFieldName(CB_Field.text);
    end;
    while not FDbGrid.DataSource.DataSet.Eof do
    begin
      lItem:=FDbGrid.DataSource.DataSet.FieldByName(FieldName).asstring; //FDbGrid.Columns[CB_Field.ItemIndex].FieldName
      if (CB_Values.Items.IndexOf(litem) <> -1) or (lItem = '') then
      begin
        FDbGrid.DataSource.DataSet.Next;
        continue;
      end;
      CB_Values.Items.Add(lItem);
      //如果是查找字段
      if FDbGrid.DataSource.DataSet.FieldByName(FieldName).FieldKind = fkLookup then
      begin
         FList[pos].Add(FDbGrid.DataSource.DataSet.FieldByName(KeyFieldName).asstring);
         FListV[pos].Add(lItem);
      end;
      FDbGrid.DataSource.DataSet.Next;
    end;
    FDbGrid.DataSource.DataSet.GotoBookmark(Bookmark);
    FDbGrid.DataSource.DataSet.FreeBookmark(Bookmark);
    FDbGrid.DataSource.DataSet.EnableControls;
end;

procedure TQueryFrm.Btn_VertClick(Sender: TObject);
begin
   FDbGrid.DataSource.DataSet.Filter:='';
   FDbGrid.DataSource.DataSet.Filtered:=False;
end;

procedure TQueryFrm.Btn_CloseClick(Sender: TObject);
begin
  ModalResult:=mrCancel;
end;

procedure TQueryFrm.CB_LogicalChange(Sender: TObject);
begin
    if CB_Logical.Text ='为空值' then
    begin
      CB_Values.Enabled:=false;
      Btn_Add.Enabled :=true;
    end
    else CB_Values.Enabled:=true;
end;

procedure TQueryFrm.ListBox1Click(Sender: TObject);
begin
   if ListBox1.SelCount > 0 then Btn_delete.Enabled:=true
   else
     Btn_delete.Enabled:=false;
end;

procedure TQueryFrm.CB_ValuesChange(Sender: TObject);
begin
   Btn_Add.Enabled :=true;
end;

procedure TQueryFrm.Btn_AddClick(Sender: TObject);
   Function space(ch:char;n:integer):string;
   var str:string;
       i:integer;
   begin
      str:='';
      for i:=1 to n do
      begin
         str:=str+ch;
      end;
      result:=str;
   end;
   var tem:string;
begin
   if CB_Field.Text = '' then
   begin
      Application.MessageBox('字段不能为空','错误',MB_OKCANCEL+MB_ICONEXCLAMATION);
      exit;
   end;
   if CB_logical.Text = '' then
   begin
      Application.MessageBox('逻辑符不能为空','错误',MB_OKCANCEL+MB_ICONEXCLAMATION);
      exit;
   end;
   if CB_Values.Text = '' then
   begin
      if CB_Logical.Text<> '为空值' then
      begin
          Application.MessageBox('数据值不能为空','错误',MB_OKCANCEL+MB_ICONEXCLAMATION);
          exit;
      end;
   end;

   if RB_and.Checked then tem:='并且  '
   else if RB_or.Checked then tem:='或者  '
   else tem:='      ';
   tem:=tem + CB_Field.Text + space(' ',20 - length(CB_Field.text)) + '  ';
   tem:=tem + CB_logical.Text + space(' ',8 - length(CB_logical.text)) + '  ';
   tem:=tem + CB_Values.Text;

   ListBox1.Items.Add(tem);
   CB_Values.Text := '';

   if not RB_and.Enabled then
   begin
      Rb_and.Enabled:=true;
      RB_and.Checked := true;
   end;
   if not RB_or.Enabled then RB_or.Enabled := true;

   Btn_add.Enabled:=false;
   Btn_Query.Enabled:=true;
end;

procedure TQueryFrm.Btn_DeleteClick(Sender: TObject);
var i,k:integer;
begin
   k:=0;
   for i:= 0 to ListBox1.Items.Count - 1 do
   begin
      if ListBox1.Selected[i - k ] then
      begin
         ListBox1.Items.Delete(i - k);
         inc(k);
      end;
   end;
   if Listbox1.Items.Count < 1 then
   begin
     Btn_query.Enabled:=false;

   end;
   Btn_Delete.Enabled:=false;
end;

procedure TQueryFrm.Btn_QueryClick(Sender: TObject);
var i:integer;
    ls_log,ls_field,ls_oper,ls_values:string;
    ls_cond:string;
    FieldName:string;
    pos:integer;
    index:integer;
begin
    ls_cond:='';
    for i:=0 to ListBox1.Items.Count - 1 do
    begin
      ls_log:=   copy(ListBox1.Items.Strings[i],1,4);
      if (i = 0) and (ls_log<>'    ') then ls_log:='    ';
      ls_field:= trim(copy(ListBox1.Items.Strings[i],7,20));
      ls_oper:=  trim(copy(ListBox1.Items.Strings[i],29,8));
      ls_values:=copy(ListBox1.Items.Strings[i],39,length(ListBox1.Items.Strings[i]) - 38);

      //判断是否是查找字段 取原值
      FieldName:=GetFieldName(ls_field);
      if FDbGrid.DataSource.DataSet.FieldByName(FieldName).FieldKind = fkLookup then
      begin
        pos:=GetKeyFieldPos(ls_field);
        index:=FListV[pos].IndexOf(ls_values);
        if (pos = -1 ) or (index = -1) then exit;
        ls_values:=FList[pos].Strings[index];
      end;

      if ls_log = '    ' then ls_cond:=ls_cond + ''
      else if ls_log = '并且' then ls_cond:=ls_cond + ' and '
      else if ls_log = '或者' then ls_cond:=ls_cond + ' or ';
      //判断是否是查找字段
      if FDbGrid.DataSource.DataSet.FieldByName(FieldName).FieldKind = fkLookup then
         ls_cond:=ls_cond + GetKeyFieldName(ls_field)
      else
         ls_cond:=ls_cond + FieldName;

      if ls_oper = '等于' then ls_cond:=ls_cond + ' = '
      else if ls_oper = '小于'   then ls_cond:=ls_cond + ' < '
      else if ls_oper = '大于'   then ls_cond:=ls_cond + ' > '
      else if ls_oper = '小于等于' then ls_cond:=ls_cond + ' <= '
      else if ls_oper = '大于等于' then ls_cond:=ls_cond + ' >= '
      else if ls_oper = '不等于' then  ls_cond:=ls_cond + ' <> '
      else if ls_oper = '包含'   then  ls_cond:=ls_cond + ' = ';
      if ls_oper = '包含' then ls_cond:=ls_cond + ''''+ls_values+'*'''
      else  ls_cond:=ls_cond + ''''+ls_values+'''';
    end;
    FDbGrid.DataSource.DataSet.Filter:=ls_cond ;
    try
      FDbGrid.DataSource.DataSet.Filtered:=true;
    except
      MessageDlg('查询条件组合有错！',mtConfirmation, [mbYes], 0);
      exit;
    end;

    ModalResult:=mrok;
end;



end.