通过TCustomAttribute和反射实现动态查询(二)
再实现一个基类，通过反射来解析TCustomAttribute类，组合成SQL语句。
unit uBISearchBaseClass; interface uses System.Classes, System.TypInfo, System.Rtti, System.SysUtils, uBISearchAttributeIni, cxGrid, uBISearchAttributes, cxGridDBTableView; type /// <summary> /// 基本信息模糊查询的种类。 /// </summary> /// <remarks> /// 商品、单位、职员、部门、仓库、地区。 /// </remarks> TBISearchType = (bistGood, bistUnit, bistEmployee, bistDepartment, bistStore, bistArea); /// <summary> /// 基本信息模糊查询控制基类，实现字段配置、SQL的构造等功能。 /// </summary> TBISearchBaseClass = class strict private FBISearchAttributeIniFile : TBISearchAttributeIniFile; FSelectSQL : string; FBISelectMatchType : TBISelectMatchType; FWheres: TStrings; FSelects: TStrings; procedure SetBISelectMatchType(const Value: TBISelectMatchType); protected procedure IniSelectSQL; virtual; public constructor Create(ABISearchType : TBISearchType); virtual; destructor Destroy; override; /// <summary> /// 保存查询列配置Where字段到ini文件。 /// </summary> procedure SaveWhereFieldConfig(AFieldAlias : string; ASelected : Boolean); virtual; /// <summary> /// 保存查询列配置Select字段到ini文件。 /// </summary> procedure SaveSelectFieldConfig(AFieldAlias : string; ASelected : Boolean); virtual; /// <summary> /// 获取查询语句。 /// </summary> function SelectSQL(AFilter : string) : string; /// <summary> /// 获取指定Where字段是否被勾选，即字段是否参与where的生成。 /// </summary> function WhereChecked(AFieldAlias : string) : Boolean; /// <summary> /// 获取指定Select字段是否被勾选，即字段是否参与select的生成。 /// </summary> function SelectChecked(AFieldAlias : string) : Boolean; procedure InitDBTableView(AGrid: TcxGridDBTableView); virtual; property BISelectMatchType : TBISelectMatchType read FBISelectMatchType write SetBISelectMatchType; property Wheres : TStrings read FWheres; property Selects : TStrings read FSelects; end; implementation { TBISearchBaseClass } constructor TBISearchBaseClass.Create(ABISearchType: TBISearchType); var sType : string; begin FWheres := TStringList.Create; FSelects := TStringList.Create; FBISearchAttributeIniFile := TBISearchAttributeIniFile.Create(nil); sType := GetEnumName(TypeInfo(TBISearchType), Ord(ABISearchType)); FBISearchAttributeIniFile.SetBISearchType(sType); FBISelectMatchType := smtRight; IniSelectSQL; end; destructor TBISearchBaseClass.Destroy; begin FBISearchAttributeIniFile.Free; FWheres.Free; FSelects.Free; inherited; end; procedure TBISearchBaseClass.IniSelectSQL; var ctx : TRttiContext; t : TRttiType; p : TRttiProperty; a : TCustomAttribute; AValid : Boolean; szSQL, szSelect, szWhere, szFrom, szWhereDefault : string; szWhereItem, szSelectItem, szSonCount : string; begin szSQL := ''; szSelect := ''; szWhere := ''; szWhereDefault := ''; szSonCount := ''; ctx := TRttiContext.Create; try t := ctx.GetType(ClassType); for a in t.GetAttributes do begin if a is TBaseInfoQueryMainTable then begin szFrom := TBaseInfoQueryMainTable(a).TableName; FBISelectMatchType := TBISelectMatchType(FBISearchAttributeIniFile.MatchType); end; end; for p in t.GetProperties do for a in p.GetAttributes do begin if a is TBaseInfoQueryWhereFieldsAttribute then begin szWhereItem := TBaseInfoQueryWhereFieldsAttribute(a).FieldCaption; if not FBISearchAttributeIniFile.WhereExists(TBaseInfoQueryWhereFieldsAttribute(a).FieldAlias) then AValid := TBaseInfoQueryWhereFieldsAttribute(a).Checked else AValid := FBISearchAttributeIniFile.WhereChecked(TBaseInfoQueryWhereFieldsAttribute(a).FieldAlias); (* 如果该字段是勾选有效的，则加入where语句。*) if AValid then begin szWhereItem := szWhereItem + '=1;'+TBaseInfoQueryWhereFieldsAttribute(a).FieldAlias; szWhere := szWhere + TBaseInfoQueryWhereFieldsAttribute(a).TableName+'.'+TBaseInfoQueryWhereFieldsAttribute(a).FieldName + ' like %% or '; end else begin szWhereItem := szWhereItem + '=0;'+TBaseInfoQueryWhereFieldsAttribute(a).FieldAlias; szWhereDefault := szWhereDefault + TBaseInfoQueryWhereFieldsAttribute(a).TableName+'.'+TBaseInfoQueryWhereFieldsAttribute(a).FieldName + ' like %% or '; end; FWheres.Add(szWhereItem); end; if a is TBaseInfoQuerySelectFieldsAttribute then begin szSelectItem := TBaseInfoQuerySelectFieldsAttribute(a).FieldCaption; if not FBISearchAttributeIniFile.SelectExists(TBaseInfoQuerySelectFieldsAttribute(a).FieldAlias) then AValid := TBaseInfoQuerySelectFieldsAttribute(a).Visible else AValid := FBISearchAttributeIniFile.SelectChecked(TBaseInfoQuerySelectFieldsAttribute(a).FieldAlias); if AValid or TBaseInfoQuerySelectFieldsAttribute(a).Fixed then begin if TBaseInfoQuerySelectFieldsAttribute(a).FieldAlias <> TBaseInfoQuerySelectFieldsAttribute(a).FieldName then begin szSelect := szSelect + TBaseInfoQuerySelectFieldsAttribute(a).FieldName +' as '+TBaseInfoQuerySelectFieldsAttribute(a).FieldAlias+',' end else begin szSelect := szSelect + TBaseInfoQuerySelectFieldsAttribute(a).FieldName+',' end; if szSonCount = '' then begin if TBaseInfoQuerySelectFieldsAttribute(a).FieldCaption='儿子数' then szSonCount := ' and '+ TBaseInfoQuerySelectFieldsAttribute(a).FieldName+'=0'; end; if AValid then begin szSelectItem := szSelectItem + '=1;'+TBaseInfoQuerySelectFieldsAttribute(a).FieldAlias end else begin (* 隐藏的固定Select字段不支持自定义。*) Continue; end; end else begin szSelectItem := szSelectItem + '=0;'+TBaseInfoQuerySelectFieldsAttribute(a).FieldAlias; end; FSelects.Add(szSelectItem); end; end; if szWhere = '' then begin szWhere := szWhereDefault; end; szWhere := szWhere.Substring(0, szWhere.Length-3); szSelect := szSelect.TrimRight([',']); szSQL := 'select ' + szSelect +' from ' +szFrom + ' where ' +'('+ szWhere+')'+szSonCount; FSelectSQL := szSQL; finally ctx.Free; end; end; procedure TBISearchBaseClass.InitDBTableView(AGrid: TcxGridDBTableView); var ctx : TRttiContext; t : TRttiType; p : TRttiProperty; a : TCustomAttribute; AValid : Boolean; AcxGridDBColumn : TcxGridDBColumn; begin ctx := TRttiContext.Create; AGrid.ClearItems; try t := ctx.GetType(ClassType); for p in t.GetProperties do for a in p.GetAttributes do begin if a is TBaseInfoQuerySelectFieldsAttribute then begin if not FBISearchAttributeIniFile.SelectExists(TBaseInfoQuerySelectFieldsAttribute(a).FieldAlias) then AValid := TBaseInfoQuerySelectFieldsAttribute(a).Visible else AValid := FBISearchAttributeIniFile.SelectChecked(TBaseInfoQuerySelectFieldsAttribute(a).FieldAlias); AValid := AValid or TBaseInfoQuerySelectFieldsAttribute(a).Fixed; if AValid then begin AcxGridDBColumn := AGrid.CreateColumn; AcxGridDBColumn.DataBinding.FieldName := TBaseInfoQuerySelectFieldsAttribute(a).FieldAlias; AcxGridDBColumn.Caption := TBaseInfoQuerySelectFieldsAttribute(a).FieldCaption; if TBaseInfoQuerySelectFieldsAttribute(a).Visible then begin AcxGridDBColumn.VisibleForCustomization := True; AcxGridDBColumn.Visible := True; end else begin AcxGridDBColumn.VisibleForCustomization := False; AcxGridDBColumn.Visible := False; end; AcxGridDBColumn.Tag := 0; if TBaseInfoQuerySelectFieldsAttribute(a).FieldCaption='ID' then AcxGridDBColumn.Tag := 100 else if TBaseInfoQuerySelectFieldsAttribute(a).FieldCaption='fullid' then AcxGridDBColumn.Tag := 101 else if TBaseInfoQuerySelectFieldsAttribute(a).FieldCaption='儿子数' then AcxGridDBColumn.Tag := 102; end; end; end; finally end; end; procedure TBISearchBaseClass.SaveSelectFieldConfig(AFieldAlias: string; ASelected: Boolean); begin if ASelected then FBISearchAttributeIniFile.CheckSelect(AFieldAlias) else begin FBISearchAttributeIniFile.UnCheckSelect(AFieldAlias); end; end; procedure TBISearchBaseClass.SaveWhereFieldConfig(AFieldAlias : string; ASelected : Boolean); begin if ASelected then FBISearchAttributeIniFile.CheckWhere(AFieldAlias) else begin FBISearchAttributeIniFile.UnCheckWhere(AFieldAlias); end; end; function TBISearchBaseClass.WhereChecked(AFieldAlias: string): Boolean; begin Result := FBISearchAttributeIniFile.WhereChecked(AFieldAlias); end; function TBISearchBaseClass.SelectChecked(AFieldAlias: string): Boolean; begin Result := FBISearchAttributeIniFile.SelectChecked(AFieldAlias); end; function TBISearchBaseClass.SelectSQL(AFilter : string): string; var szMatch : string; AMatchType : TBISelectMatchType; begin IniSelectSQL; if Wheres.Count = 0 then begin Exit(''); end; AMatchType := FBISelectMatchType; case ord(AMatchType) of 0 : szMatch := QuotedStr('%'+Trim(AFilter)); 1 : szMatch := QuotedStr(Trim(AFilter)+'%'); 2 : szMatch := QuotedStr('%'+Trim(AFilter)+'%'); end; Result := StringReplace(FSelectSQL, '%%', szMatch, [rfReplaceAll]); end; procedure TBISearchBaseClass.SetBISelectMatchType( const Value: TBISelectMatchType); begin FBISelectMatchType := Value; FBISearchAttributeIniFile.MatchType := Ord(Value); end; end.
