{*******************************************************}
{                                                       }
{  Responsive Software     http://www.responsive.co.nz  }
{                                                       }
{  Copyright (c) 2003-2006 Responsive Software Limited  }
{                                                       }
{*******************************************************}

unit ProxyDatabaseObjectCollectionUnit;

interface

uses
  Classes, DBTables, DB, IBDatabase,
  DatabaseObjects;

type
  TProxyDatabaseObject = class;

  // this is used to provide generic access to any collection of database objects
  // without requiring the objects to be loaded until they are required
  TProxyDatabaseObjectCollection = class
  private
    FDatabaseObjectClass : TDatabaseObjectClass;
    FDatabaseObjectCollection : TDatabaseObjectCollection;
    FProxyDatabaseObjects : TList;
    FDataset : TDataset;
    FCount : integer;
    function GetCount : integer;
    function ObjectLoaded
      (i : integer) : boolean;
    function GetObject
      (i : integer) : TDatabaseObject;
    function OpenLocalDataset
      (SelectionString : string) : integer;
    procedure CloseLocalDataset;
    function GetDatasetRecNoOffset
      (RecNo : integer) : integer;
  public
    constructor Create
      (DatabaseObjectClass : TDatabaseObjectClass;
       DatabaseObjectCollection : TDatabaseObjectCollection;
       SelectionString : string); overload;
    destructor Destroy; override;

    procedure InsertObject
      (Index : integer;
       DatabaseObject : TDatabaseObject);
    procedure DeleteObject
      (Index : integer); 

    procedure SaveToCSVFile;

    property Count : integer read GetCount;
    property Objects[i : integer] : TDatabaseObject read GetObject; default;
  end;

  TProxyDatabaseObject = class
  private
    FDatabaseObject : TDatabaseObject;
    FDatasetRecNo : integer;
    FRecNo : integer;
  public
    constructor Create
      (DatabaseObject : TDatabaseObject;
       DatasetRecNo : integer;
       RecNo : integer);
    destructor Destroy; override;
    function DatabaseObject : TDatabaseObject;
    function DatasetRecNo : integer;
    function RecNo : integer;
    function DatasetRecNoOffset : integer;
    procedure IncrementRecNo;
    procedure DecrementRecNo;
  end;

implementation

uses
  Progress, GeneralUtilities, Utilities, Globals, IBQuery,
  SysUtils, Dialogs, Controls, Forms;

{***** TProxyDatabaseObjectCollection methods *********************************}

constructor TProxyDatabaseObjectCollection.Create
  (DatabaseObjectClass : TDatabaseObjectClass;
   DatabaseObjectCollection : TDatabaseObjectCollection;
   SelectionString : string);
begin
  inherited Create;
  FDatabaseObjectClass := DatabaseObjectClass;
  // if collection supplied then use this and ignore the selection string
  if DatabaseObjectCollection <> nil then
    FDatabaseObjectCollection := DatabaseObjectCollection
  // otherwise create a proxy object collection
  else begin
    FProxyDatabaseObjects := TList.Create;
    if ClientMode then
      FCount := ClientCommunicator.OpenRemoteDataset
        (DatabaseObjectClass,SelectionString)
    else
      FCount := OpenLocalDataset(SelectionString);
  end;
end;

destructor TProxyDatabaseObjectCollection.Destroy;
begin
  // destroy proxy object collection
  DestroyList(FProxyDatabaseObjects);
  if FDatabaseObjectCollection = nil then
  begin
    if ClientMode then
      ClientCommunicator.CloseRemoteDataset
    else
      CloseLocalDataset;
  end;
end;

function TProxyDatabaseObjectCollection.GetCount : integer;
begin
  if FDatabaseObjectCollection <> nil then
    Result := FDatabaseObjectCollection.Count
  else
    Result := FCount;
end;

function TProxyDatabaseObjectCollection.ObjectLoaded
  (i : integer) : boolean;
var
  j : integer;
begin
  for j := 0 to FProxyDatabaseObjects.Count - 1 do
    if TProxyDatabaseObject(FProxyDatabaseObjects[j]).RecNo = i + 1 then
    begin
      Result := true;
      Exit;
    end;
  Result := false;
end;

function TProxyDatabaseObjectCollection.GetObject
  (i : integer) : TDatabaseObject;
var
  j : integer;
  ProxyDatabaseObject : TProxyDatabaseObject;
  DatabaseObject : TDatabaseObject;
  RecNo : integer;
  DatabaseObjectCollection : TDatabaseObjectCollection;
  DatasetRecNoOffset : integer;
begin
  Result := nil;
  // if we are using a supplied collection then
  // just return the corresponding object in this
  if FDatabaseObjectCollection <> nil then
  begin
    Result := FDatabaseObjectCollection[i];
    Exit;
  end else begin
    if (i<0) or (i>=FCount) then
      Exit;
    // look to see if this object is already loaded
    for j := 0 to FProxyDatabaseObjects.Count - 1 do
    begin
      ProxyDatabaseObject := TProxyDatabaseObject(FProxyDatabaseObjects[j]);
      if ProxyDatabaseObject.RecNo = i + 1 then
      begin
        Result := ProxyDatabaseObject.DatabaseObject;
        Exit;
      end;
    end;
    // determine the offset for this record number between
    // the proxy collection and the dataset record number
    DatasetRecNoOffset := GetDatasetRecNoOffset(i+1);
    // load and add the next twenty records to the collection
    // in anticipation that they may be needed
    if ClientMode then
    begin
      DatabaseObjectCollection := TDatabaseObjectCollection.Create;
      ClientCommunicator.LoadDatabaseObjectsFromRemoteDataset
        (DatabaseObjectCollection,i+1+DatasetRecNoOffset,i+20+DatasetRecNoOffset);
      // transfer ownership of objects to proxy collection
      DatabaseObjectCollection.Owned := false;
      for j := 0 to DatabaseObjectCollection.Count - 1 do
      begin
        RecNo := i + 1 + j;
        DatabaseObject := DatabaseObjectCollection[j];
        if not ObjectLoaded(i+j) then
        begin
          ProxyDatabaseObject := TProxyDatabaseObject.Create
            (DatabaseObject,RecNo+DatasetRecNoOffset,RecNo);
          FProxyDatabaseObjects.Add(ProxyDatabaseObject);
        end else
          DatabaseObject.Free;
        if RecNo = i + 1 then
          Result := DatabaseObject;
      end;
      DatabaseObjectCollection.Free;
    end else begin
      for j := 0 to 19 do
      begin
        if not ObjectLoaded(i+j) then
        begin
          RecNo := i + 1 + j;
          if RecNo+DatasetRecNoOffset <= FDataset.RecordCount then
          begin
            FDataset.RecNo := RecNo+DatasetRecNoOffset;
            DatabaseObject := FDatabaseObjectClass.Create;
            DatabaseObject.LoadFromTable(FDataset);
            ProxyDatabaseObject := TProxyDatabaseObject.Create
              (DatabaseObject,RecNo+DatasetRecNoOffset,RecNo);
            FProxyDatabaseObjects.Add(ProxyDatabaseObject);
            if RecNo = i + 1 then
              Result := DatabaseObject;
          end;
        end;
      end;
    end;
  end;
end;

function TProxyDatabaseObjectCollection.OpenLocalDataset
  (SelectionString : string) : integer;
begin
  if Firebird then
    FDataset := FDatabaseObjectClass.OpenIBQuery(SelectionString)
  else
    FDataset := FDatabaseObjectClass.OpenQuery(SelectionString);
  Result := FDataset.RecordCount;
end;

procedure TProxyDatabaseObjectCollection.CloseLocalDataset;
begin
  if FDataset <> nil then
  begin
    FDataset.Active := false;
    if Firebird then
    begin
      if TIBQuery(FDataset).Transaction.InTransaction then
        TIBQuery(FDataset).Transaction.Commit;
      TIBQuery(FDataset).Transaction.Free;
    end;
  end;
  FDataset.Free;
end;

function TProxyDatabaseObjectCollection.GetDatasetRecNoOffset
  (RecNo : integer) : integer;
var
  i : integer;
  ProxyDatabaseObject : TProxyDatabaseObject;
  SelectedProxyDatabaseObject : TProxyDatabaseObject;
  Offset : integer;
begin
  SelectedProxyDatabaseObject := nil;
  // find the proxy object with the record number immediately
  // preceding the one we are about to load 
  for i := 0 to FProxyDatabaseObjects.Count - 1 do
  begin
    ProxyDatabaseObject := TProxyDatabaseObject(FProxyDatabaseObjects[i]);
    // ignore any inserted records which do not have corresponding entries
    // in the dataset
    if ProxyDatabaseObject.DatasetRecNo = 0 then
      continue;
    // find the one with the maximum record number not exceeding
    // the one specified
    if (ProxyDatabaseObject.RecNo <= RecNo) and
       ( (SelectedProxyDatabaseObject = nil) or
         (ProxyDatabaseObject.RecNo > SelectedProxyDatabaseObject.RecNo) ) then
      SelectedProxyDatabaseObject := ProxyDatabaseObject;
  end;
  // return the offset after adjusting for inserted records
  if SelectedProxyDatabaseObject <> nil then
  begin
    Offset := SelectedProxyDatabaseObject.DatasetRecNoOffset;
    // adjust for any inserted records
    for i := 0 to FProxyDatabaseObjects.Count - 1 do
    begin
      ProxyDatabaseObject := TProxyDatabaseObject(FProxyDatabaseObjects[i]);
      if (ProxyDatabaseObject.DatasetRecNo = 0) and
        (ProxyDatabaseObject.RecNo > SelectedProxyDatabaseObject.RecNo) and
        (ProxyDatabaseObject.RecNo < RecNo) then
        Inc(Offset);
    end;
    Result := Offset;
  end else
    Result := 0;
end;

procedure TProxyDatabaseObjectCollection.InsertObject
  (Index : integer;
   DatabaseObject : TDatabaseObject);
var
  RecNo : integer;
  i : integer;
  ProxyDatabaseObject : TProxyDatabaseObject;
begin
  if FDatabaseObjectCollection <> nil then
    FDatabaseObjectCollection.Insert(Index,DatabaseObject)
  else begin
    RecNo := Index + 1;
    // increment all larger record numbers to make room
    for i := 0 to FProxyDatabaseObjects.Count - 1 do
    begin
      ProxyDatabaseObject := TProxyDatabaseObject(FProxyDatabaseObjects[i]);
      if ProxyDatabaseObject.RecNo >= RecNo then
        ProxyDatabaseObject.IncrementRecNo;
    end;
    // add the new object via a proxy object
    ProxyDatabaseObject := TProxyDatabaseObject.Create
      (DatabaseObject,0,RecNo);
    FProxyDatabaseObjects.Add(ProxyDatabaseObject);
    Inc(FCount);
  end;
end;

procedure TProxyDatabaseObjectCollection.DeleteObject
  (Index : integer);
var
  RecNo : integer;
  i : integer;
  ProxyDatabaseObject : TProxyDatabaseObject;
begin
  if FDatabaseObjectCollection <> nil then
    FDatabaseObjectCollection.Delete(Index)
  else begin
    RecNo := Index + 1;
    // remove matching record number and decrement all larger record numbers
    for i := FProxyDatabaseObjects.Count - 1 downto 0 do
    begin
      ProxyDatabaseObject := TProxyDatabaseObject(FProxyDatabaseObjects[i]);
      if ProxyDatabaseObject.RecNo = RecNo then
      begin
        FProxyDatabaseObjects.Delete(i);
        ProxyDatabaseObject.Free;
      end else if ProxyDatabaseObject.RecNo > RecNo then
        ProxyDatabaseObject.DecrementRecNo;
    end;
    Dec(FCount);
  end;
end;

procedure TProxyDatabaseObjectCollection.SaveToCSVFile;
var
  i : integer;
  FileName : string;
  FileStream : TFileStream;
  Str : string;
begin
  FileName := GetRegistryString('CSVFileLocation');
  FileName := FileName + FDatabaseObjectClass.TableName + '.CSV';
  if ChooseFileSave(FileName,'Save File As','CSV files (*.CSV)|*.CSV'
    + '|Any file (*.*)|*.*') then
  begin
    if (not FileExists(FileName)) or (MessageDlg('Overwrite existing file?',
        mtConfirmation, [mbYes, mbNo], 0) = mrYes) then
    begin
      // delete existing file
      SysUtils.DeleteFile(FileName);
      // open file
      FileStream := OpenFileStream(FileName,false);
      // output heading string to file
      if MessageDlg('Show field headings in CSV file?',
        mtConfirmation, [mbYes, mbNo], 0) = mrYes then
      begin
        Str := FDatabaseObjectClass.CSVHeadingsString + CRLF;
        FileStream.Write(PChar(Str)^,Length(Str));
      end;
      // let the user save the contents of the proxy collection to a file
      // in comma separated value format (CSV)
      ProgressForm.SetStep(0);
      ProgressForm.SetCaption('Extracting data to a CSV file.  Please wait...');
      ProgressForm.Show;
      if ClientCommunicator <> nil then
        ClientCommunicator.SetServerThreadPriority(tpLowest);
      try
        for i := 0 to Count - 1 do
        begin
          ProgressForm.SetPosition(i * 100 div Count);
          // check to see if escape has been pressed
          Application.ProcessMessages;
          if ProgressForm.EscapePressed then
          begin
            if MessageDlg('CANCEL CSV EXTRACT?',
              mtConfirmation, [mbYes,mbNo], 0) = mrYes then
            begin
              FileStream.Free;
              FileStream := nil;
              SysUtils.DeleteFile(FileName);
              break;
            end else
              ProgressForm.EscapePressed := false;
          end;
          // output detail to file
          Str := TDatabaseObject(Objects[i]).CSVDetailsString + CRLF;
          FileStream.Write(PChar(Str)^,Length(Str));
        end;
      finally
        SaveRegistryString('CSVFileLocation',ExtractFilePath(FileName));
        ProgressForm.Hide;
        FileStream.Free;
        if ClientCommunicator <> nil then
          ClientCommunicator.SetServerThreadPriority(tpNormal);
      end;
    end;
  end;
end;

{***** TProxyDatabaseObject methods *******************************************}

constructor TProxyDatabaseObject.Create
  (DatabaseObject : TDatabaseObject;
   DatasetRecNo : integer;
   RecNo : integer);
begin
  FDatabaseObject := DatabaseObject;
  FDatasetRecNo := DatasetRecNo;
  FRecNo := RecNo;
end;

destructor TProxyDatabaseObject.Destroy;
begin
  FDatabaseObject.Free;
end;

function TProxyDatabaseObject.DatabaseObject : TDatabaseObject;
begin
  Result := FDatabaseObject;
end;

function TProxyDatabaseObject.DatasetRecNo : integer;
begin
  Result := FDatasetRecNo;
end;

function TProxyDatabaseObject.RecNo : integer;
begin
  Result := FRecNo;
end;

function TProxyDatabaseObject.DatasetRecNoOffset : integer;
begin
  if FDatasetRecNo <> 0 then
    Result := FDatasetRecNo - FRecNo
  else
    Result := 0;
end;

procedure TProxyDatabaseObject.IncrementRecNo;
begin
  Inc(FRecNo);
end;

procedure TProxyDatabaseObject.DecrementRecNo;
begin
  Dec(FRecNo);
end;

end.