通用查询组件设计
前段时间由于工作较忙，无暇整理本组件的相关文档，请大家谅解！以后我会陆续整理公布该组件的所有相关文档及源码！
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;
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;