queryobj.pas

仓库管理系统 仓库管理系统

unit QueryObj;

interface

uses Classes, SysUtils, DB, DBTables;

type
  TSQLQryObj = class(TObject)
  private
    TheDataBaseName: String;
    FQryTables: TStringList;      //Query injured tables' name
    FDispFields: TStringList;     //the name of fields in select clause
    FDispFieldsCap:TStringList;   //the caption of fields in select clause
    FAndClause: TStringList;      //the and clause
    FAndClauseDesp:TStringList;   //the description of and clause;
    FJoins: TStringList;          //the junction of tables
    FJoinsDesp:TStringList;       //The Junction description

    Procedure Set_DBName(ADataBaseName:String);
    function GetTableCount: Integer;
    Function Get_DispfldCount:Integer;
    Function Get_CriteriaCount:Integer;
    Function Get_JoinCount:Integer;
    function GetQueryTable(Index: Integer): TTable;
    procedure SetQueryTable(Index: Integer; Value: TTable);
    function GetTableName(Index: Integer): String;
    function GetTablesName: TStrings;
    function GetTableDesc(Index: Integer): String;
    procedure SetTableDesc(Index: Integer; Desc: String);
    Function  Get_DispFldName(Index: Integer):String;
    Function  Get_DispFldCap (Index: Integer):String;
    function GetWhereClause: String;
    Function  Get_Criteria(Index: Integer): String;
    Function  Get_CriteriaDesp(Index: Integer): String;
    Function  Get_Join(Index: Integer): String;
    Function  Get_JoinDesp(Index: Integer): String;
    function GetSQL: String;
    function OrPosWrong(CurIndex, Dir: Integer): Boolean;
  public
    constructor Create;overload;
    //constructor Create(QryFileName:String);overload;
    destructor Destroy; override;
    function FieldInTable(ATableName, AFieldName: String): Boolean; overload;
    function FieldInTable(Index: Integer; AFieldName: String): Boolean; overload;

    Function FieldDataType(ATBCaption,AFldName:String):TFieldType;

    procedure AddTable(DesName: String; ATable: TTable);
    procedure AddDisplayField(FieldName,FieldCap: String);
    procedure AddCriteria(Value,Description: String);
    //procedure MoveCriteria(CurIndex, Dir: Integer);
    //procedure DeleteCriteria(Index: Integer);
    procedure AddJoin(Value,Description: String);

    Procedure RemoveAll;
    Procedure RemoveTables;
    Procedure ReomveJoins;
    Procedure RemoveDispFlds;
    Procedure RemoveAndClause;

    Property DatabaseName: String read TheDataBaseName write Set_DBName;
    property TableCount: Integer read GetTableCount;
    Property DispfldCount:Integer read Get_DispfldCount;
    Property CriteriaCount:integer read Get_CriteriaCount;
    Property JoinCount    :integer read Get_JoinCount;

    property Tables[Index: Integer]: TTable read GetQueryTable write SetQueryTable;
    property TableDesc[Index: Integer]: String read GetTableDesc write SetTableDesc;
    property TableDescList: TStrings read GetTablesName;
    property TableName[Index: Integer]: String read GetTableName;
    Property DispField[Index: Integer]: String read Get_DispFldName;
    Property DispFieldCap[Index: Integer]: String read Get_DispFldCap;
    property WhereClause: String read GetWhereClause;
    Property AndClause:TStringList Read FAndClause;
    Property Criteria[Index: Integer]: String read Get_Criteria;
    Property AndClauseDesp:TStringList Read FAndClauseDesp;
    Property CriteriaDesp[Index: Integer]: String read Get_CriteriaDesp;
    property Joins: TStringList read FJoins;
    Property Join[Index: Integer] :String read Get_Join;
    Property JoinsDesp:TStringList Read FJoinsDesp;
    Property JoinDesp[Index: Integer]:String read Get_JoinDesp;
    property SQL: String read GetSQL;
  end;

implementation

Procedure TSQLQryObj.RemoveALL;
Begin
  //Added by Edward Lane , March 21st , 2000
  // 删除以前的查询条件, 以防止条件重复
  FQryTables.Clear;
  //FQryTablesCap.Clear;

  FDispFields.Clear;
  FDispFieldsCap.Clear;
  FAndClause.Clear;
  FJoins.Clear;
  // 结束增加
End;
Procedure TSQLQryObj.RemoveTables;
Begin
  FQryTables.Clear;
End;

Procedure TSQLQryObj.ReomveJoins;
Begin
  FJoins.Clear;
End;

Procedure TSQLQryObj.RemoveDispFlds;
Begin
  FDispFields.Clear;
  FDispFieldsCap.Clear;
End;

Procedure TSQLQryObj.RemoveAndClause;
Begin
  FAndClause.Clear;
End;

function GetPart(Src: String; Part: Integer; Sep: Char) : String;
var
  I, iCnt: Integer;
  bCopied: Boolean;
begin
  bCopied := (Part = 0);		// 若是拷贝第0部分则开始标志即为真.
  iCnt := 0;
  Result := '';

  for I := 1 to Length(Src) do
  begin
	   if Src[I] = Sep then
   	begin
	     if bCopied then	 //如果已开始拷贝则遇到分隔符退出
   	    Exit
	     else if (I < Length(Src)) and (Src[I+1] <> Sep) then
  	    Inc(iCnt);
	     bCopied := (iCnt = Part);
	   end else if bCopied then
      Result := Result + Src[I];
  end;
end;

constructor TSQLQryObj.Create;
begin
  inherited;

  FQryTables := TStringList.Create;
  //FQryTablesCap:=TStringList.Create;

  FDispFields := TStringList.Create;
  FDispFieldsCap:=TStringList.Create;
  FAndClause := TStringList.Create;
  FAndClauseDesp:=TStringList.Create;
  FJoins := TStringList.Create;
  FJoinsDesp:=TStringList.Create;
end;

{constructor TSQLQryObj.Create(QryFileName:String);
Begin
  Create;
  //Load from QryFile:qryfilename
End;   }

destructor TSQLQryObj.Destroy;
begin
  if FQryTables <> nil then
    FQryTables.Free;
  //if FQryTablesCap <> nil then
  //  FQryTablesCap.Free;
 
  if FDispFields <> nil then
    FDispFields.Free;
  if FDispFieldsCap <> nil then
    FDispFieldsCap.Free;
  if FAndClause <> nil then
    FAndClause.Free;
  if FAndClauseDesp <> nil then
    FAndClauseDesp.Free;
  if FJoins <> nil then
    FJoins.Free;
  if FJoinsDesp <> nil then
    FJoinsDesp.Free;

  inherited;
end;

function TSQLQryObj.GetQueryTable(Index: Integer): TTable;
begin
  if (Index < 0) or (Index > FQryTables.Count - 1) then
    Result := nil
  else
    Result := TTable(FQryTables.Objects[Index]);
end;

procedure TSQLQryObj.SetQueryTable(Index: Integer; Value: TTable);
begin
  if (Index < 0) or (Index > FQryTables.Count - 1) then
    raise ERangeError.Create('Out of band (' + IntToStr(Index) + ').');
  FQryTables.Objects[Index] := Value;
end;

Procedure TSQLQryObj.Set_DBName(ADataBaseName:String);
Begin
  TheDataBaseName:=ADataBaseName;
End;

function TSQLQryObj.GetTableCount: Integer;
var
  nCnt: Integer;
begin
  if FQryTables <> nil then
    Result := FQryTables.Count
  else begin
    Result := 0;
    for nCnt := 0 to FQryTables.Count - 1 do
      if (FQryTables.Objects[nCnt] <> nil) and
         (FQryTables.Objects[nCnt] is TTable) then
        Inc(Result);
  end;
end;

Function TSQLQryObj.Get_DispfldCount:Integer;
Begin
  If FDispFields<>nil Then
    Result:=FDispFields.Count
  Else
    Result:=-1;
End;
Function TSQLQryObj.Get_CriteriaCount:Integer;
Begin
  If FAndClause<>nil Then
    Result:=FAndClause.Count
  Else
    Result:=-1;
End;
Function TSQLQryObj.Get_JoinCount:Integer;
Begin
  If FJoins<>nil Then
    Result:=FJoins.Count
  Else
    Result:=-1;
End;

function TSQLQryObj.GetWhereClause: String;
var
  nCnt: Integer;
  JoinCriteria: String;
begin
  // 多表连接条件
  If (FJoins = nil ) Or(FJoins.Count <1 ) Then
      JoinCriteria := ''
  Else Begin//*build Join Criterias
    JoinCriteria :=' ('+ FJoins[0]+') ';
    for nCnt := 1 to FJoins.Count - 1 do
      JoinCriteria := JoinCriteria + ' AND '
                    +' ('+FJoins[nCnt]+') ';
    //JoinCriteria:='('+JoinCriteria+')';
  End;
  // 选择条件
  if (FAndClause = nil) or (FAndClause.Count < 1) then
    Result:=''
  Else Begin//*Built And/Or Criterias
    with FAndClause do  begin
      nCnt := 0;
      // 忽略前导的'或'
      while (nCnt < Count) and
            (AnsiCompareText(FAndClause[nCnt], 'OR') = 0) do
        Inc(nCnt);

      // 第一个有效的查询条件
      Result := '(' + FAndClause[nCnt] + ')';
      Inc(nCnt);
      while nCnt < Count do  begin
        if AnsiCompareText(FAndClause[nCnt], ' OR ') <> 0 then
          Result := Result + ' AND (' + FAndClause[nCnt] + ')'
        else begin
          Inc(nCnt);
          if nCnt < Count then
            Result := Result + ' OR ' + '(' + FAndClause[nCnt] + ')';
        end;
        Inc(nCnt);
      end;
    End;
  End;
  //*建立完整约束条件
  If Length(JoinCriteria)>0 Then
    If Length(Result)>0 Then
      Result:=Result+' and '+JoinCriteria
    Else
      Result:=JoinCriteria;
end;

Function  TSQLQryObj.Get_Criteria(Index: Integer): String;
Begin