queryobj.pas

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

  if (Index < 0) or (Index > FAndClause.Count - 1) then
    Result := ''
  else
    Result := FAndClause[Index];
End;

Function  TSQLQryObj.Get_CriteriaDesp(Index: Integer): String;
Begin
  if (Index < 0) or (Index > FAndClauseDesp.Count - 1) then
    Result := ''
  else
    Result := FAndClauseDesp[Index];
End;

Function  TSQLQryObj.Get_Join(Index: Integer): String;
Begin
  if (Index < 0) or (Index > Joins.Count - 1) then
    Result := ''
  else
    Result := Joins[Index];
End;

Function  TSQLQryObj.Get_JoinDesp(Index: Integer): String;
Begin
  if (Index < 0) or (Index > JoinsDesp.Count - 1) then
    Result := ''
  else
    Result := JoinsDesp[Index];
End;

function TSQLQryObj.GetSQL: String;
var
  nCnt: Integer;
  Tbls:String;
  SelClause: String;
begin
  SelClause:='';
  For nCnt:=0 TO FDispFields.Count-1 do
    SelClause := SelClause + ' ' +
                 FDispFields[nCnt] + ' ,';

  Tbls:='';
  For nCnt:=0 To FQryTables.Count-1 Do Begin
    Tbls:=Tbls+TTable(FQryTables.Objects[nCnt]).TableName+', ';
  End;

  SelClause := Copy(SelClause, 0, Length(SelClause) - 1);
  Tbls := Copy(Tbls, 1, Length(Tbls) - 2);

  Result := Format('SELECT %s FROM %s WHERE %s',
                   [SelClause,
                   Tbls,
                   WhereClause]);
end;

procedure TSQLQryObj.AddTable(DesName: String; ATable: TTable);
begin
  FQryTables.AddObject(DesName, ATable);
  self.DatabaseName := ATable.DatabaseName;
end;

function TSQLQryObj.GetTableName(Index: Integer): String;
var
  tmp: String;
begin
  if (Index < 0) or (Index > FQryTables.Count - 1) then
    Result := ''
  else begin
    tmp := TTable(FQryTables.Objects[Index]).TableName;
    Result := UpperCase(GetPart(tmp, 0, '.'));
  end;
end;

function TSQLQryObj.GetTableDesc(Index: Integer): String;
begin
  if (Index < 0) or (Index > FQryTables.Count - 1) then
    Result := ''
  else
    Result := FQryTables[Index];
end;

procedure TSQLQryObj.SetTableDesc(Index: Integer; Desc: String);
begin
  if (Index < 0) or (Index > FQryTables.Count) then
    raise ERangeError.Create('Out of bound (' + IntToStr(Index) + ').');
  FQryTables[Index] := Desc;
end;

Function  TSQLQryObj.Get_DispFldName(Index: Integer):String;
Begin
  if (Index < 0) or (Index > FDispFields.Count - 1) then
    Result := ''
  else
    Result := FDispFields[Index];
End;

Function  TSQLQryObj.Get_DispFldCap (Index: Integer):String;
Begin
  if (Index < 0) or (Index > FDispFieldsCap.Count - 1) then
    Result := ''
  else
    Result := FDispFieldsCap[Index];
End;

function TSQLQryObj.GetTablesName: TStrings;
begin
  if FQryTables = nil then
    Result := nil
  else
    Result := TStrings(FQryTables);
end;

procedure TSQLQryObj.AddDisplayField(FieldName,FieldCap: String);
begin
  FDispFields.Add(FieldName);
  FDispFieldsCap.Add(FieldCap);
end;

function TSQLQryObj.FieldInTable(Index: Integer; AFieldName: String): Boolean;
begin
  Result := False;
  if (Index < 0) or (FQryTables.Objects[Index] = nil) then Exit;

  with TTable(FQryTables.Objects[Index]) do
  begin
    if not Active then Open;

    Result := (FieldDefs.IndexOf(AFieldName) > -1);
  end;
end;


function TSQLQryObj.FieldInTable(ATableName, AFieldName: String): Boolean;
//ATableName is the real table name in database
var
  nCnt, Index: Integer;
begin
  Result := False;
  Index := -1;
  with FQryTables do
  begin
    for nCnt := 0 to Count - 1 do
    //Find table whitch indicates the database table in query table list
      if AnsiCompareText(TTable(Objects[nCnt]).TableName, ATableName) = 0 then
      begin
        Index := nCnt;
        Break;
      end;

    if (Index < 0) or (Objects[Index] = nil) then Exit;

    with TTable(Objects[Index]) do
    begin
      if not Active then Open;
      //If FieldDefs.IndexOf(AFieldName)<>-1 meanse the field IS in the table
      Result := (FieldDefs.IndexOf(AFieldName) > -1);
    end;
  end;
end;


{function TSQLQryObj.FieldDataType(TblIndex: Integer; FieldDesc: String): TFieldType;
var
  FieldName: String;
begin
  Result := ftUnknown;

  if (TblIndex < 0) or (FQryTables.Objects[TblIndex] = nil) then Exit;

  with TTable(FQryTables.Objects[TblIndex]) do
    try
      if not Active then Open;
      //Get the field
      FieldName := FQryFields[FQryFieldsCap.IndexOf(FieldDesc)];
      //Return field type
      Result := FieldDefs.Find(FieldName).DataType;
    except end;
end;  }

Function TSQLQryObj.FieldDataType(ATBCaption,AFldName:String):TFieldType;
Var intTBIndex:Integer;
Begin
  Result:=ftUnknown;
  //*Find the index of ATBCaption pointed table in FQryTables
  intTBIndex:=FQryTables.IndexOf(ATBCaption);
  If intTBIndex<>-1 Then
    //*Varify field
    with TTable(FQryTables.Objects[intTBIndex]) do
    try
      if not Active then Open;
      //Return field type
      Result := FieldDefs.Find(AFldName).DataType;
    except end;

End;

{function TSQLQryObj.MaskByDesc(DescName: String): String;
var
  nIdx: Integer;
begin
  nIdx := FQryFieldsCap.IndexOf(DescName);
  if (nIdx < -1) then
    raise Exception.Create('Field not exists.')
  else
    Result := FQryFieldsMask[nIdx];
end;}

procedure TSQLQryObj.AddCriteria(Value,Description: String);
begin
  if FAndClause = nil then
    FAndClause := TStringList.Create;
  If FAndClauseDesp=nil Then
    FAndClauseDesp:=TStringList.Create;

  if (AnsiCompareText(Value, 'or') = 0) and
     (FAndClause.Count < 1) then
    raise Exception.Create('''or'' 必须连接两组条件.');

  FAndClause.Add(Value);
  FAndClauseDesp.Add(Description);
end;

function TSQLQryObj.OrPosWrong(CurIndex, Dir: Integer): Boolean;
begin
  Result :=// 下移第一个条件,而第二个条件为'或'
           ((CurIndex = 0) and
            (AnsiCompareText(FAndClause[CurIndex + Dir], 'or') = 0))
           or
           // 上移最后一个条件,而倒数第二个条件为'或'
           ((CurIndex = FAndClause.Count - 1) and
            (AnsiCompareText(FAndClause[CurIndex + Dir], 'or') = 0))
           or
           // 移动中间的条件,造成两个'或'连在一起
           ((CurIndex > 0) and (CurIndex < FAndClause.Count - 1) and
            (AnsiCompareText(FAndClause[CurIndex - Dir], 'or') = 0) and
            (AnsiCompareText(FAndClause[CurIndex + Dir], 'or') = 0));
end;

//--------------------------------------//
//  Direction:  -1 up; 1 down
//--------------------------------------//
{procedure TSQLQryObj.MoveCriteria(CurIndex, Dir: Integer);
begin
  if OrPosWrong(CurIndex, Dir) then
    raise Exception.Create('');

  FAndClause.Move(CurIndex, CurIndex + Dir);
  FAndClause.Move(CurIndex, CurIndex + Dir);
end;   }

{procedure TSQLQryObj.DeleteCriteria(Index: Integer);
var
  dir: Integer;
begin
  if (Index > FAndClause.Count - 1) or (Index < 0) then Exit;

  if Index = 0 then dir := 1
  else dir := -1;
  if OrPosWrong(Index, dir) then
    raise Exception.Create('');

  FAndClause.Delete(Index);
  FAndClauseDesp.Delete(Index);
end; }

procedure TSQLQryObj.AddJoin(Value,Description: String);
var
  Jlf, Jrf,         //join left field,join right field
  Jlt, Jrt: String; //join left table,join right table
begin
  if Pos('=', Value) < 0 then
    raise Exception.Create('Join expression error.');

  Jlt := Trim(GetPart(Value, 0, '='));
  Jlf := GetPart(Jlt, 1, '.');
  Jlt := GetPart(Jlt, 0, '.');

  Jrt := Trim(GetPart(Value, 1, '='));
  Jrf := GetPart(Jrt, 1, '.');
  Jrt := GetPart(Jrt, 0, '.');

  if not (FieldInTable(Jlt, Jlf) and FieldInTable(Jrt, jrf)) then
    raise Exception.Create('Join expression error.');

  FJoins.Add(Value);
  FJoinsDesp.Add(Description);
end;

end.