procedure TMyFieldInfo.SetVariables(d: TDataset);
var
value : String;
begin
//设置变量值
if AnsiUpperCase(FilterValue) = ''''NULL'''' then //如果FilterValue为空，则退出
exit;
if FieldType = ftString then //如果字段类型为字符串型，则
begin
if CaseSensitive then //如果大小写敏感
case MatchType of //匹配类型
fdMatchStart, fdMatchAny : //起始部分匹配或任意位置匹配
value := FilterValue;
fdMatchEnd : //结束部分匹配
value := ''''%'''' + FilterValue;
fdMatchExact : //非匹配记录
value := FilterValue;
end
else //大小写不敏感
case MatchType of
fdMatchStart, fdMatchAny : //起始部分匹配或任意位置匹配
value := AnsiUpperCase(FilterValue);
fdMatchEnd : //结束部分匹配
value := ''''%'''' + AnsiUpperCase(FilterValue); {do not localize}
fdMatchExact : //非匹配记录
value := AnsiUpperCase(FilterValue);
end;
end
else//字段类型为非字符串型
value := FilterValue;
if MatchType <> fdMatchRange then//如果匹配类型不为按范围
TQuery(d).ParamByName(FieldName + ''''Filter'''').Value := value
else //否则
begin
if CaseSensitive then //如果大小写敏感
begin
if StartingValue <> '''''''' then //如果起始范围值不为空
TQuery(d).ParamByName(FieldName + ''''Start'''').Value := StartingValue;
if EndingValue <> '''''''' then //如果结束范围不为空
TQuery(d).ParamByName(FieldName + ''''End'''').Value := EndingValue;
end
else //大小写敏感
begin
if StartingValue <> '''''''' then //如果起始范围值不为空
TQuery(d).ParamByName(FieldName + ''''Start'''').Value := AnsiUpperCase(StartingValue);
if EndingValue <> '''''''' then //如果结束范围值不为空
TQuery(d).ParamByName(FieldName + ''''End'''').Value := AnsiUpperCase(EndingValue);
end;
end;
end
end;
字段定义类
TMyFieldInfo = class //字段类
public
FieldName : String; //字段名
FieldOrigin : String;
FieldType : TFieldType; //字段类型
DisplayLabel : String; //显示的名称
MatchType : TDBFilterMatchType; //匹配类型
FilterValue : String; //过滤值
StartingValue : String; //开始值
EndingValue : String; //结束值
CaseSensitive : boolean; //是否大小写敏感
NonMatching : boolean; //不匹配
procedure Assign(o : TMyFieldInfo); //指定字段定义
function CreateSQL : String; //创建SQL语句
procedure SetVariables( d : TDataset); //设置字段变量
end;
指定字段定义
procedure TMyFieldInfo.Assign(o : TMyFieldInfo);
begin
//指定字段信息
FieldName := o.FieldName;
FieldOrigin := o.FieldOrigin;
FieldType := o.FieldType;
DisplayLabel := o.DisplayLabel;
MatchType := o.MatchType;
FilterValue := o.FilterValue;
StartingValue := o.StartingValue;
EndingValue := o.EndingValue;
CaseSensitive := o.CaseSensitive;
NonMatching := o.NonMatching;
end;
创建SQL语句
function TMyFieldInfo.CreateSQL: String;
var
Field : String;
begin
//创建SQL语句
if FieldOrigin <> '''''''' then
Field := FieldOrigin
else
Field := FieldName;
if NonMatching then
Result := '''' not ( ''''
else
Result := '''' ( '''';
if AnsiUpperCase(FilterValue) = ''''NULL'''' then
begin
Result := Result + Format(''''%s is NULL) '''', [Field]);
exit;
end;
if FieldType = ftString then
begin
if CaseSensitive then
case MatchType of
fdMatchStart:
Result := Result + Format(''''%0:s starting with :%1:sFilter ) '''', [Field, FieldName]);
fdMatchAny:
Result := Result + Format(''''%0:s containing :%1:sFilter ) '''', [Field, FieldName]);
fdMatchEnd :
Result := Result + Format(''''%0:s = :%1:sFilter ) '''', [Field, FieldName]);
fdMatchExact :
Result := Result + Format(''''%0:s = :%1:sFilter ) '''', [Field, FieldName]);
fdMatchRange :
begin
if StartingValue <> '''''''' then
Result := Result + Format(''''%0:s >= :%1:sStart)'''', [Field, FieldName]);
if (StartingValue <> '''''''') and (EndingValue <> '''''''') then
Result := Result + '''' and ('''';
if EndingValue <> '''''''' then
Result := Result + Format(''''%0:s <= :%1:sEnd)'''', [Field, FieldName]);
end;
end
else
case MatchType of
fdMatchStart:
Result := Result + Format(''''UPPER(%0:s) starting with :%1:sFilter ) '''', [Field, FieldName]); {do not localize}
fdMatchAny:
Result := Result + Format(''''UPPER(%0:s) containing :%1:sFilter ) '''', [Field, FieldName]); {do not localize}
fdMatchEnd :
Result := Result + Format(''''UPPER(%0:s) like :%1:sFilter ) '''', [Field, FieldName]); {do not localize}
fdMatchExact :
Result := Result + Format(''''UPPER(%0:s) = :%1:sFilter ) '''', [Field, FieldName]); {do not localize}
fdMatchRange :
begin
if FieldType = ftString then
begin
if StartingValue <> '''''''' then
Result := Result + Format(''''UPPER(%0:s) >= :%1:sStart)'''', [Field, FieldName]); {do not localize}
if (StartingValue <> '''''''') and (EndingValue <> '''''''') then
Result := Result + '''' and (''''; {do not localize}
if EndingValue <> '''''''' then
Result := Result + Format(''''UPPER(%0:s) <= :%1:sEnd)'''', [Field, FieldName]); {do not localize}
end
else
begin
if StartingValue <> '''''''' then
Result := Result + Format(''''%0:s >= :%1:sStart)'''', [Field, FieldName]); {do not localize}
if (StartingValue <> '''''''') and (EndingValue <> '''''''') then
Result := Result + '''' and (''''; {do not localize}
if EndingValue <> '''''''' then
Result := Result + Format(''''%0:s <= :%1:sEnd)'''', [Field, FieldName]); {do not localize}
end
end;
end;
end
else
case MatchType of
fdMatchRange :
begin
if StartingValue <> '''''''' then
Result := Result + Format(''''%0:s >= :%1:sStart)'''', [Field, FieldName]); {do not localize}
if (StartingValue <> '''''''') and (EndingValue <> '''''''') then
Result := Result + '''' and (''''; {do not localize}
if EndingValue <> '''''''' then
Result := Result + Format(''''%0:s <= :%1:sEnd)'''', [Field, FieldName]); {do not localize}
end;
else
Result := Result + Format(''''%0:s = :%1:sFilter ) '''', [Field, FieldName]); {do not localize}
end;
end;
