unit uDBPagination;

{< DB pagination for mySQL - adding LIMIT M,N phrase to Select Request}

interface

uses
  System.SysUtils, System.Classes, FireDAC.Stan.Intf, FireDAC.Stan.Option, FireDAC.Stan.Param, FireDAC.Stan.Error,
  FireDAC.DatS, FireDAC.Phys.Intf, FireDAC.DApt.Intf, FireDAC.Stan.Async, FireDAC.DApt, Data.DB, FireDAC.Comp.DataSet,
  FireDAC.Comp.Client,Math;

type
  TDBPagination = class(TDataModule)
  private

   FFDQuery:TFDQuery;
   FSQLRequest:string;

   FCountOfRecords:Int64;

   FCurrentRow:Int64;
   FCurrentPage:Integer;
   FCountOfPages:Integer;


   FRecordsOnPage:Int64;

   FInitialSQLText:string;



  public
    { Public declarations }

    constructor Create(AOwner: TComponent); override;

    procedure LoadSomeLimitedNumberOfRows(AStartRow:Integer; ACountOfRows:integer);
    procedure CountNumberOfPages();

    procedure LoadFirstPage();

    procedure LoadNextPage();
    procedure LoadPreviousPage();

    property FDQuery:TFDQuery read FFDQuery write FFDQuery;
    property SQLRequest:string read FSQLRequest write FSQLRequest;


    property CurrentPage:Integer read FCurrentPage;
    property CountOfPages:Integer read FCountOfPages;



    property  RecordsOnPage:Int64 read FRecordsOnPage write FRecordsOnPage;



    property InitialSQLText:string read FInitialSQLText write FInitialSQLText;

    property CountOfRecords:Int64 read FCountOfRecords write FCountOfRecords;



  end;


//var
//  DBPagination: TDBPagination;

implementation

{%CLASSGROUP 'Vcl.Controls.TControl'}

{$R *.dfm}

{ TDBPagination }

procedure TDBPagination.CountNumberOfPages;
begin

Assert(FInitialSQLText<>'','FInitialSQLText is empty. Fill it First');
Assert(FRecordsOnPage<>0,'FRecordsOnPage is 0. Cannot be so Fill it First');

  if Assigned(FDQuery) then begin

      with FDQuery do begin

      Disconnect();
      SQL.Text:=FInitialSQLText;
      Open();

      FCountOfPages:=Ceil(FCountOfRecords/FRecordsOnPage);

      Disconnect();
      end;

  end;



end;





constructor TDBPagination.Create(AOwner: TComponent);
begin
  inherited;

  FCountOfPages:=0;
  FCurrentPage:=0;
  FInitialSQLText:='';

end;

procedure TDBPagination.LoadFirstPage;
begin

LoadSomeLimitedNumberOfRows(0,FRecordsOnPage);

end;

procedure TDBPagination.LoadSomeLimitedNumberOfRows(AStartRow:Integer; ACountOfRows:integer);


begin

    if Assigned(FDQuery) then

     begin

         FDQuery.Disconnect();

        // Attention here ! Original statement FDQuery.SQL.Text
        // should be without semicolon
        FDQuery.SQL.Text:=FInitialSQLText;
        FDQuery.SQL.Text:=FDQuery.SQL.Text+' LIMIT :StartRow,:CountOfRows';


        with FDQuery do begin

        Params.ParamValues['StartRow']:=AStartRow;
        Params.ParamValues['CountOfRows']:=ACountOfRows;


        end;



       FDQuery.Open();


     end;


 end;





procedure TDBPagination.LoadNextPage();
begin

  if FCurrentPage<FCountOfPages then
  begin
  FCurrentPage:=FCurrentPage+1;
  FCurrentRow:=FCurrentRow+FRecordsOnPage;
  LoadSomeLimitedNumberOfRows(FCurrentRow,FRecordsOnPage);

  end;

end;

procedure TDBPagination.LoadPreviousPage();
begin

  if FCurrentPage>0 then
  begin
  FCurrentPage:=FCurrentPage-1;
  FCurrentRow:=FCurrentRow-FRecordsOnPage;
  LoadSomeLimitedNumberOfRows(FCurrentRow,FRecordsOnPage);

  end;

end;


end.
