ufrmfiltersql.pas

一个会议签到系统

{ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
//**                 数据过滤，对表格现有数据的过滤。                         **
//**            是数字的转换为数字，其他都认为是文本比较                      **
*ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
//              20030322  过滤用形成SQL语句再到数据库中去查询
*ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
*               REdtFilterSQL 当第一次获得焦点时 自身文本消失
*               可能时 RichEdit 自身的毛病
*ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
*               查询表格式  关键在于  And Or  之间的关系
*               易先录                          20030420       
*ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
}
unit uFrmFilterSQL;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Buttons, ExtCtrls, OleCtrls, ComCtrls,Db, uFramGrid;

type
  TFrmFilterSQL = class(TForm)
    BtnSure: TBitBtn;
    BtnCancel: TBitBtn;
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    MemoFilterSQL: TMemo;
    MemoFilter: TMemo;
    SpeedButton1: TSpeedButton;
    PnlGrid: TPanel;
    FM: TFramGrid;
    Timer1: TTimer;
    Panel1: TPanel;
    procedure BtnCancelClick(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormShow(Sender: TObject);
    procedure EdtFilterValueBKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure BtnSureClick(Sender: TObject);
    procedure FormShortCut(var Msg: TWMKey; var Handled: Boolean);
    procedure MemoFilterSQLChange(Sender: TObject);
    procedure SpeedButton1Click(Sender: TObject);
    procedure FMCmbIntKeyPress(Sender: TObject; var Key: Char);
    procedure FMEdtBtnAKeyPress(Sender: TObject; var Key: Char);
    procedure FMCmbStrAChange(Sender: TObject);
    procedure FMEdtBtnAEnter(Sender: TObject);
    procedure FMCmbStrBEnter(Sender: TObject);
    procedure FMEdtBtnAKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure FMSBtnEdtAClick(Sender: TObject);
  private
    saFieldName:array of String;
    saFieldNameE:array of String;
    saFieldType:array  of TFieldType;
    iFieldType:Integer; // 1 数子 2 时间  3  字符
    { Private declarations }
  public
    sTempSQL:String;
    Procedure PGetGridData();
    Procedure PSetFilterChar(sCmbTxT:String);
    Procedure PAddFilterToREdt();
    Procedure PAddFilterSQLToREdt();
    Procedure PGetFieldType(sCmbTxt:String);
    Procedure PInitFilterGrid();
    Procedure PSetFilterToGrid();   
    { Public declarations }
  end;

var
  FrmFilterSQL: TFrmFilterSQL;

implementation

uses uFuntionSys, uFrmQueryTop, uFrmFilterSQLQ;

{$R *.DFM}

procedure TFrmFilterSQL.BtnCancelClick(Sender: TObject);
begin
  Close;
end;

procedure TFrmFilterSQL.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  SaveINIForm(Sender as TForm);
  FM.PConfigIO(False);
  Action:=caFree;
  FrmFilterSQL:=nil; 
end;

procedure TFrmFilterSQL.FormShow(Sender: TObject);
begin
  ReadINIForm(Sender as TForm);
  PGetGridData();
  if  FrmQueryTop.iSureORCancel = 50 then  begin
      MemoFilter.text:=FrmQueryTop.MemoFilter.Text;
      MemoFilterSQL.Text:=FrmQueryTop.MemoFilterSQL.Text;
  end;  
  PInitFilterGrid();
  FM.PConfigIO(True);
  PSetFilterToGrid(); 
end;
{*******************************************************************************
*                  表格的列名
*                  不可见的列名不能出现在条件过滤中
*******************************************************************************}
procedure TFrmFilterSQL.PGetGridData();
Var
  I,K,iLen:integer;
begin
  iLen:=0;
  SetLength(saFieldName,iLen);
  SetLength(saFieldNameE,iLen);
  SetLength(saFieldType,iLen);
  K:=FrmQueryTop.dxDBGrid1.DataSource.DataSet.FieldCount;

  FM.CmbStrA.Items.Clear;
  for I:=0 to  K-1 do begin
      if FrmQueryTop.dxDBGrid1.Columns[I].Visible then begin
         iLen:=iLen+1;
         SetLength(saFieldName,iLen);
         SetLength(saFieldNameE,iLen);
         SetLength(saFieldType,iLen);
         {** 加 T.  是 相关联的算法 一改动 网格序号也要改动**}
         saFieldName[iLen-1]:='T.'+FrmQueryTop.dxDBGrid1.Columns[I].FieldName;
         saFieldNameE[iLen-1]:=FrmQueryTop.dxDBGrid1.Columns[I].Caption;
         saFieldType[iLen-1]:=FrmQueryTop.dxDBGrid1.Columns[I].Field.DataType;
         
         FM.CmbStrA.Items.Append(saFieldNameE[iLen-1]);            
      end;    
  end;

end;

procedure TFrmFilterSQL.FormShortCut(var Msg: TWMKey;
  var Handled: Boolean);
begin
  if Msg.CharCode = 222 then  begin//单引号问题
     Handled:=True;
     Exit;
  end;
end;
procedure TFrmFilterSQL.EdtFilterValueBKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin


end;
{*******************************************************************************
*                     字段类型不同  过滤条件 符号不同
*******************************************************************************}
procedure TFrmFilterSQL.BtnSureClick(Sender: TObject);
begin

  PAddFilterToREdt();
  PAddFilterSQLToREdt();

  if Trim(MemoFilterSQL.Text) = '' then begin
     DlgWarningA('没有设置过滤条件!');
     Exit;
  end;

  FrmQueryTop.MemoFilter.Text:=MemoFilter.Text;
  FrmQueryTop.MemoFilterSQL.Text:=MemoFilterSQL.Text;
  if FrmQueryTop.iSureORCancel = 50 then
     FrmQueryTop.PFindarrayToEdit();
  FrmQueryTop.iSureORCancel:=100;   
  Close;     
end;
{(ftUnknown, ftString, ftSmallint, ftInteger, ftWord, ftBoolean, ftFloat,
  ftCurrency, ftBCD, ftDate, ftTime, ftDateTime, ftBytes, ftVarBytes, ftAutoInc,
  ftBlob, ftMemo, ftGraphic, ftFmtMemo, ftParadoxOle, ftDBaseOle, ftTypedBinary,
  ftCursor, ftFixedChar, ftWideString, ftLargeint, ftADT, ftArray, ftReference,
   ftDataSet, ftOraBlob, ftOraClob, ftVariant, ftInterface, ftIDispatch, ftGuid);
}
{*******************************************************************************
*              设置过滤条件   的符号  数字日期不能有 Like
*               iFieldType   // 1 数子 2 时间  3  字符
*******************************************************************************}
procedure TFrmFilterSQL.PSetFilterChar(sCmbTxT:String);
var
  K:integer;
begin

  FM.CmbStrB.Items.Clear;
  FM.CmbStrB.Items.Add('无');
  FM.CmbStrB.Items.Add('>');
  FM.CmbStrB.Items.Add('>=');
  FM.CmbStrB.Items.Add('<');
  FM.CmbStrB.Items.Add('<=');
  FM.CmbStrB.Items.Add('=');
  FM.CmbStrB.Items.Add('<>');

  K:=FM.CmbStrA.Items.IndexOf(sCmbTxT);

  if (saFieldType[K] = ftMemo) or (saFieldType[K] = ftWideString) or
     (saFieldType[K] = ftString) or (saFieldType[K] = ftFmtMemo)  then
     begin
       FM.CmbStrB.Items.Add('包含');
       iFieldType:=3;    //字符
     end else if
     (saFieldType[K] = ftInteger) or (saFieldType[K] = ftWord) or
     (saFieldType[K] = ftSmallint) or (saFieldType[K] = ftFloat) or
     (saFieldType[K] = ftCurrency) or (saFieldType[K] = ftFloat)  then
     begin
       iFieldType:=1;   //数字
     end else    iFieldType:=2;  //时间
end;
{*******************************************************************************
*           空时 sZIduan = ''  and sZiduan is NULL
*******************************************************************************}
{*******************************************************************************
*                        把过滤的条件添加到 左边
*******************************************************************************}
procedure TFrmFilterSQL.PAddFilterToREdt;
var
  Str:String;
  I,K:integer;
  bCount:Boolean;
begin
  {***第一步 清楚 没有设置条件 的行*****}
  for I:=FM.FxGrid.Rows-2 downto 1 do begin
    Str:=FM.FxGrid.TextMatrix[I,0];
    if Str = ''         then FM.FxGrid.RemoveItem(I);
    if Str = '网格序号' then FM.FxGrid.RemoveItem(I);//假列 不能设置
    Str:=FM.FxGrid.TextMatrix[I,1];