PHP + Delphi + FireBird

Question

I have a problem to retrieve large volume of data with PHP + Delphi 9 + Firebird.

Application screen shot I have a button in Delphi Application called "checks" , when I click the button it generate PHP Excel File by retrieving data from Firebird according to the condition specified in the function arguments.

Delphi Procedure:

procedure TfrmTransactions.dxBarButton2Click(Sender: TObject); //action click 
var ids: string;
  DateFrom, DateTo: string;
begin
  ids:='1';
  datefrom  := FormatDateTime('yyyy"/"mm"/"dd', dateStart.EditValue);
  dateto    := FormatDateTime('yyyy"/"mm"/"dd', dateEnd.EditValue);
  DBModule.SendRequest('checkFlow=' + Ids + '&startdate=' + datefrom + '&enddate=' + dateto, 'CheckFlow.xls');

end;

Note :* The above code execute by calling php method checkFlow in the excelexport.php file and generate 'CheckFlow.xls' * checkFlow is a function in the excelexport.php refer below for the file.

Problem:

I executed the function in PHP and tested ,it is work well for small and large volume of data retrieval, but when I integrate in Delphi button click, it is not working for large volume of data but if I retrieve small volume of data through Delphi the excel file is generated.

PHP File that get data from firebird and insert into sql server table:

<?php
require_once('class.pdo.firebird.php');
require_once('config.php');
require_once('xml.php');


function insertCheckFlowProduBanco($startdate,$enddate){
try{
$x=0;
$dateInArray = array();
$db = new PDOFirebird();
$datesql="SELECT DISTINCT  EXTRACT(YEAR FROM MV.FEC_VENCIMIENTO) ||'/'||
                substring(100+EXTRACT(MONTH from MV.FEC_VENCIMIENTO) from 2 for 2) ||'/'||
                substring(100+EXTRACT(DAY from MV.FEC_VENCIMIENTO) from 2 for 2)   AS FECHA
                FROM MOV_BANCOS  MV
                WHERE MV.CONCILIADO not in('A') AND  MV.MONTO>0 AND MV.KEY_BANCO=1
                AND MV.KEY_MOV_BANCO=1
                UNION  ALL
                SELECT DISTINCT  EXTRACT(YEAR FROM MV.FEC_VENCIMIENTO) ||'/'||
                substring(100+EXTRACT(MONTH from MV.FEC_VENCIMIENTO) from 2 for 2) ||'/'||
                substring(100+EXTRACT(DAY from MV.FEC_VENCIMIENTO) from 2 for 2)   AS FECHA
                FROM MOV_BANCOS  MV
                WHERE MV.CONCILIADO not in('A') AND MV.FEC_VENCIMIENTO >= '".$startdate."'
                AND MV.FEC_VENCIMIENTO <='".$enddate."' AND MV.MONTO>0 AND MV.KEY_BANCO=1"; 

    $dates = $db->query($datesql);
    while ( $row = $dates->fetch( PDO::FETCH_ASSOC ) ) {
    if($row['FECHA']!=null){
    $fecha= $row['FECHA'];  
    }
    $dateInArray[$x] = $fecha;
    $x++;   
}
$farmsql="SELECT DISTINCT MV.OBSERVACION AS NAME
FROM MOV_BANCOS MV
 WHERE MV.CONCILIADO not in('A') AND MV.KEY_BANCO=1 AND MV.KEY_MOV_BANCO=1
 UNION ALL
 SELECT DISTINCT MV.OBSERVACION AS NAME
FROM MOV_BANCOS MV 
WHERE MV.CONCILIADO not in('A') AND MV.FEC_VENCIMIENTO >= '".$startdate."'
AND MV.FEC_VENCIMIENTO <='".$enddate."' AND MV.MONTO>0 AND MV.KEY_BANCO=1";
$farms= $db->query($farmsql);
while ( $row = $farms->fetch( PDO::FETCH_ASSOC ) ) {
if($row['NAME']!=null){
$name=$row['NAME'];
}
foreach($dateInArray as $dateInArrayValue=>$valuedate){
$valuesql="SELECT  MV.OBSERVACION AS NAME,SUM(CAST(MV.MONTO as FLOAT)) as MONTO
                FROM MOV_BANCOS MV 
                WHERE MV.CONCILIADO not in('A') AND MV.FEC_VENCIMIENTO = '".$valuedate."'
                AND MV.OBSERVACION ='".$name."'
                AND MV.MONTO>0 AND MV.KEY_BANCO=1
                GROUP BY MV.OBSERVACION
                UNION ALL
                SELECT  MV.OBSERVACION AS NAME,SUM(CAST(MV.MONTO as FLOAT)) as MONTO
                FROM MOV_BANCOS MV 
                WHERE MV.CONCILIADO not in('A') AND MV.FEC_VENCIMIENTO = '".$valuedate."'
                AND MV.OBSERVACION ='".$name."'
                AND MV.MONTO>0 AND MV.KEY_BANCO=1
                GROUP BY MV.OBSERVACION";
$result= $db->query($valuesql);

$chequesql="SELECT  MV.NO_DOC_BANCO AS DOC
                FROM MOV_BANCOS MV 
                WHERE MV.CONCILIADO not in('A') AND MV.FEC_VENCIMIENTO = '".$valuedate."'
                AND MV.OBSERVACION ='".$name."'
                AND MV.MONTO>0 AND MV.KEY_BANCO=1
                UNION ALL
                SELECT  MV.NO_DOC_BANCO
                FROM MOV_BANCOS MV 
                WHERE MV.CONCILIADO not in('A') AND MV.FEC_VENCIMIENTO = '".$valuedate."'
                AND MV.OBSERVACION ='".$name."'
                AND MV.MONTO>0 AND MV.KEY_BANCO=1";

$cheque=$db->query($chequesql);
$y=0;
$chequearray=array();
while ( $row = $cheque->fetch( PDO::FETCH_ASSOC ) ) {
if($row['DOC']!=null){
$chequearray[]=$row['DOC'];
}
$y++;
}
$doc = implode(",", $chequearray);
$amount=array();
$i=0;
while ( $row = $result->fetch( PDO::FETCH_ASSOC ) ) {
$amount[$i]=$row['MONTO'];
}
if(($valuedate!=null) && ($name!=null) &&($amount[$i]!=0)){
$sql="INSERT INTO FLOWCHECK(SUPPLIER,BANK,DATE,AMOUNT,DOC)
VALUES('".$name."','PRODUBANCO','".$valuedate."','".$amount[$i]."','".$doc."')";
alexGetValues($sql);
}
$i++;
}
}
}catch(PDOException $e){
        return $e->getMessage();
}
}
?>

Note: * I use Union ALL because firebird returns null for the first column when retrieving data. * I get the data from the Firebird and insert in sql server. * The data that is stored in the sql server table is then display in Excel * Below is the checkFlow code.

excelexport.php:(checkFlow)

<?php
require_once('Spreadsheet/Excel/Writer.php');
require_once('flowCheck.php');
ini_set('max_execution_time', 300);


function checkFlow($startdate,$enddate,$externalfile=''){

  $xls = new Spreadsheet_Excel_Writer();
  $xls->send('CheckFlow.xls');
  $formats = array(
 'space' => array('align' => 'center'),
 'title'    => array('size' => 14, 'bold' => 1, 'align' => 'center', 'color' => 'black', 'fgcolor' => array(224, 224, 224)),
 'left'     => array('size' => 10, 'align' => 'left','left' => 1, 'right' => 1, 'top' => 1, 'bottom' => 1),
 'center'   => array('size' => 10, 'align' => 'center','left' => 1, 'right' => 1, 'top' => 1, 'bottom' => 1),
 'right'   => array('size' => 10, 'align' => 'right','left' => 1,'right' => 1, 'top' => 1, 'bottom' => 1,'width' => 100),
 'header' =>   array('size' => 10, 'bold' => 1, 'align' => 'center','fgcolor' => array(255, 205, 160),'bottom' => 1,'left' => 1, 'right' => 1, 'top' => 1),
 );
   $colFormats = createFormats(&$xls, &$formats);

 insertCheckFlowProduBanco($startdate,$enddate);
$banksql="SELECT DISTINCT BANK FROM FLOWCHECK
            WHERE  CONVERT(VARCHAR,DATE,111)>='".$startdate."' 
            AND CONVERT(VARCHAR,DATE,111)<='".$enddate."'";

$banks=new AlexMSSQLDataset($banksql);
$bankarray=array();

while($banks->moveNext()){
 $bankarray[]=$banks->getFieldValue('BANK');
}

 foreach($bankarray as $val){
  $rowHeader=7;$colHeader=0;
  $col=0;
 $row=9;
 $sheet =&$xls->addWorksheet($val);
 $sheet->writeRow(1,1,array('Flujo de Cheques  ','','',''),&$colFormats['title']);
 $sheet->mergeCells(1,1,1,7);
 $datesql=" SELECT DISTINCT Convert(varchar,Date,111) AS Date,DATENAME(dw,Date) as Day
    FROM FLOWCHECK
    where convert(varchar,date,111)>='".$startdate."'
    and convert(varchar,date,111)<='".$enddate."'
    and bank='".$val."'";

 $dates=new AlexMSSQLDataset($datesql);

 $sheet->writeRow($rowHeader++, $colHeader, array('Banco','Farm'), &$colFormats['header']);
 $sheet->setColumn(0,0,30);
 $sheet->setColumn(1,1,60);
 $col1=2;
 $x=0;
  $dateInArray = array();
 while ($dates->moveNext()){
 $day=$dates->getFieldValue('Day');

 if( $day == 'Monday'){
    $day='Lunes';
 }else if( $day == 'Tuesday'){
    $day='Martes ';
 }else if( $day == 'Wednesday'){
    $day='Miércoles  ';
 }else if( $day == 'Thursday'){
    $day='Jueves  ';
 }else if( $day == 'Friday'){
    $day='Viernes';
 }else if( $day == 'Saturday'){
    $day='Sábado  ';
 }else if( $day == 'Sunday'){
    $day='Domingo ';
    }
 $sheet->write($rowHeader-1,$col1++,$day.' '.$dates->getFieldValue('Date'),&$colFormats['header']); 
 $dateInArray[$x] = $dates->getFieldValue('Date');
 $col1=2;
 foreach($dateInArray as $dateInArrayValue=>$valuedate){
  $sheet->mergeCells($rowHeader-1,$col1,$rowHeader-1,$col1+1);
  $sheet->write($rowHeader,$col1++,'Cheque No',&$colFormats['header']);
  $sheet->write($rowHeader,$col1++,'Value',&$colFormats['header']);
  }
 $x++; 
 $sheet->setColumn(1,$col1,25);
 }

  $farmsql="SELECT DISTINCT supplier,bank FROM flowCheck
    where convert(varchar,date,111)>='".$startdate."'
    and convert(varchar,date,111)<='".$enddate."' 
    and bank='".$val."'
    order by bank ";
 $farms=new AlexMSSQLDataset($farmsql); 
 while($farms->movenext()){
  $bank=$farms->getFieldValue('bank');
  $name=$farms->getFieldValue('supplier');
  $sheet->write($row,$col++,$bank,$colFormats['left']);
  $sheet->write($row,$col++,$name,$colFormats['left']);
  foreach($dateInArray as $dateInArrayValue=>$valuedate){
   $valuesql="SELECT supplier,amount,doc FROM flowCheck
    where convert(varchar,date,111)='".$valuedate."'
     and supplier='".$name."'
    and bank='".$val."'  ";

 $values=new AlexMSSQLDataset($valuesql);
 while($values->moveNext()){
 $farmvalues=$values->getFieldValue('amount');
 $chequenumber=$values->getFieldValue('doc');
 }
  if($values->getFieldValue('amount')!= null){
  $sheet->write($row,$col++,$chequenumber,$colFormats['left']);
 $sheet->write($row,$col++,round($farmvalues,2),$colFormats['left']);

 }else{
 $sheet->write($row,$col++,0,$colFormats['left']);
  $sheet->write($row,$col++,0,$colFormats['left']);
 }
}

 $col=0;
 $row++;
 $auxrow=$rowHeader+3;
 $auxcol=$col;
 }

  $sheet->writeRow($row, $auxcol, array('Total'), &$colFormats['header']);
 foreach($dateInArray as $dateInArrayval){
  $sheet->write($row, $auxcol+3,'=SUM('.$xls->rowcolToCell($auxrow-2,$auxcol+3).':'.$xls->rowcolToCell($row-1,$auxcol+3).')', &$colFormats['left']);
  $auxcol=$auxcol+2;

 $sheet->setMargins(0.40);
 $sheet->fitToPages(1, 0);
 $sheet->hideGridLines();
 $sheet->insertBitmap(0, 0, SHAREDPATH.'\\logo-reports.bmp', 3, 3);
 }
 } 
$xls->close();

}


if (!isset($_GET['noresponse']))
{
    $successauth = true;

        if (($_GET['client']) and ($_GET['auth']))
        {
        $client = intval($_GET['client']);
        $auth = intval($_GET['auth']);

        $userdata = new AlexMSSQLDataset('select AuthNumber from WebAuth where ID='.$client);
        $userdata->moveNext();
        alexGetValues('delete from WebAuth where ID='.$client);

        if ($userdata->getFieldValue(0) == $auth)
        {
            $successauth = true;
        }
    }

        if ($successauth)
        {

        if($_GET['checkFlow']){
            $startdate = $_GET['startdate'];
            $enddate = $_GET['enddate'];
            checkFlow($startdate,$enddate);
        }
        }
        else
        {
                $xls = new Spreadsheet_Excel_Writer();
                $xls->send('alert.xls');
                $sheet =& $xls->addWorksheet('Alert');

                $sheet->mergeCells(0, 0, 2, 0);

                if (file_exists(SHAREDPATH.'\\authalert.bmp'))
                {
            $sheet->insertBitmap(0, 0, SHAREDPATH.'\\authalert.bmp', 3, 3);
                }

                $sheet->write(0, 1, 'Authentication failed');
                $sheet->write(1, 1, 'You are not granted to view this report');

                $sheet->hideGridLines();

                $xls->close();
                exit();
        }
}




?>

Kindly suggest me with an solution.

Thanks in advance!

Answer 1

You can use LIMIT :

Limit is used to limit your MySQL query results to those that fall within a specified range. You can use it to show the first X number of results, or to show a range from X - Y results.

It is phrased as Limit X, Y and included at the end of your query. X is the starting point (remember the first record is 0) and Y is the duration (how many records to display). Also Known As: Range Results Examples:

SELECT * FROM your_table LIMIT 0, 250

This will display the first 250 results from the database.

SELECT * FROM your_table LIMIT 250, 5

This will show records 250, 251, 252, 253, and 254

Delphi
First you must get the record count of your table.
in your .php file with a function and a very simple select get the row count.

php

<?php
require_once('class.pdo.firebird.php');
require_once('config.php');
require_once('xml.php');

if (isset($_REQUEST['action']) {
    if ($_REQUEST['action']=='getRCount') {
      getrowcount($_REQUEST['startdate'],$_REQUEST['enddate']);
      exit;
    }
 }

function getrowcount($startdate,$enddate) { 
// PSEUDO
connect to PDO
//do a simple select not select *
$sql="SELECT .... WHERE  CONVERT(VARCHAR,DATE,111)>='".$startdate."' 
        AND CONVERT(VARCHAR,DATE,111)<='".$enddate."'";
$res=new AlexMSSQLDataset($sql);
echo $res->num_rows;
 }

function insertCheckFlowProduBanco($startdate,$enddate){
[...]

delphi

function TfrmTransactions.getrowcount(datefrom,dateto:integer): integer;
// PSEUDO
var
conStr : ansistring;

begin // here with a request to your .php file on the server
      // get the max rowcount 
conStr := 'getDataFromFirebird.php?action=getRCount'+
          '&startdate=' + datefrom + '&enddate=' + dateto;
connect to server 'http://myserver/myApp/'+conStr; 
result := response;
end;

procedure TfrmTransactions.dxBarButton2Click(Sender: TObject);
var Ids: string;
DateFrom, DateTo: ansistring;
maxcount : integer;
Limitfrom,LimitCount,conStr : ansistring;
begin
Ids:='1';
DateFrom  := FormatDateTime('yyyy"/"mm"/"dd', dateStart.EditValue);
DateTo    := FormatDateTime('yyyy"/"mm"/"dd', dateEnd.EditValue);    

  maxcount:=getrowcount(DateFrom, DateTo); 
  LimitFrom  := 0;
  LimitCount := 250;

  while LimitFrom < maxcount do begin

  if (LimitFrom + LimitCount) > maxcount then 
         LimitCount := LimitFrom + LimitCount - maxcount;

  conStr := 'checkFlow=' + Ids + 
            '&startdate=' + datefrom + 
            '&enddate=' + dateto +
            '&LimitFrom=' + intToStr(LimitFrom)+
            '&LimitCount=' + intToStr(LimitCount);

  DBModule.SendRequest(conStr,'CheckFlow.xls');
  LimitFrom := LimitFrom + LimitCount;
  end;
 end;

Since I do not know which database table is the parent, I can only show a suggestion here.
php

[...]
$xysql="SELECT  ....
                ....
                FROM MOV_BANCOS  MV
                WHERE MV.CONCILIADO not in('A') AND MV.FEC_VENCIMIENTO >= '".$startdate."'
                AND MV.FEC_VENCIMIENTO <='".$enddate."' AND MV.MONTO>0 AND MV.KEY_BANCO=1 
                LIMIT ".$LimitFrom.", ".$LimitCount;
[...]

Something like that

Of course you have to build your .xls file data by data (append not overwrite)