通用查詢組件設計
前段時間由于工作較忙，無暇整理本組件的相關文檔，請大家諒解！以后我會陸續整理公布該組件的所有相關文檔及源碼！
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;
新聞熱點
疑難解答
