通用查询组件设计（续三）

通用查询组件设计

作者：nxyc_twz@163.com

　　前段时间由于工作较忙，无暇整理本组件的相关文档，请大家谅解！以后我会陆续整理公布该组件的所有相关文档及源码！

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;

字段定义类

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

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;