I have a piece of code that generates some tables:
bp_rows
: where I take a snapshot of some "headers" for each row of the table.bp_flussi
: where I store a date and an amount only if the amount is not equal to 0.
Flussi can be positive or negative. When this is done, I use different code to populate a table with one row for each line in bp_rows
. Then each row will split in five sub rows with details coming from bp_flussi
and bp_rows
(a column for each month) and the fifth row is for a total. In the end of the table I have a grand total.
This code is taking too much time to load. Actually, the output is loaded into a div
on the page using jQuery's .load()
and only when the browser notifies me that the script is taking too long, it will output the table (if I hit to continue running the script) and this will happen immediately.
My question: Will there be a difference in terms of performance if I use .ajax()
method instead of .load()
? How can I debug the PHP page that will build the table to see what is taking so much time?
The table is loaded with this code:
$('.container').load('plan/visualizza.php?id_bp='+id_bp+'&dett='+dett+'&veicolo='+veicolo);
The PHP code:
<?php
require '../../session_handler.inc.php';
session_start();
$actusr = $_SESSION['id'];
$id_bp = $_GET['id_bp'];
$dett = $_GET['dett'];
$veicolo = $_GET['veicolo'];
if($veicolo != '0'){
$veicolo_clause = " AND titolare='$veicolo'";
}else{
$veicolo_clause = "";
}
require_once '../../global_functions.php';
require '../Classes/financial_class.php';
$f = new Financial;
$irr_mensile = array();
//$annoMin = $pdo->query("SELECT MIN(anno) AS anno FROM bp_flussi WHERE id_bp = '$id_bp'")->fetchColumn();
//$annoMax = $pdo->query("SELECT MAX(anno) AS anno FROM bp_flussi WHERE id_bp = '$id_bp'")->fetchColumn();
$annoMin = '2008';
$annoMax = '2016';
$vuoto = 0.00;
$creds = $pdo->query("SELECT * FROM bp_rows WHERE id_bp = '$id_bp'$veicolo_clause")->fetchAll();
$data_rif = $pdo->query("SELECT data_rifer FROM bp_head WHERE id = '$id_bp'")->fetchColumn();
?>
<script type="text/javascript">
$(document).ready(function(){
resizeDiv();
$("#spnmsg").fadeTo(200,0.1,function(){
$(this).removeClass().addClass('spn_normal').html("Visualizzazione del business plan '<b><?php echo get_name('bp_head',$id_bp,'nome',$pdo);?></b>'.").fadeTo(900,1);
});
//$('#businessplan').DataTable();
});
</script>
<div class="titolo" id="title">
<h1>JENIUS</h1>
<h2>Business Plan '<?php echo get_name('bp_head',$id_bp,'nome',$pdo);?>' con data di riferimento <?php echo mysql2table($data_rif); ?> </h2>
</div>
<div class="pagina "id="contenuto">
<table class="report" id="businessplan">
<caption class="report">Crediti inseriti nella sofferenza</caption>
<thead>
<tr>
<th class="report">ID</th>
<th class="report">Lotto</th>
<th class="report">Sofferenza</th>
<th class="report">Data acquisto</th>
<th class="report">Cedente</th>
<th class="report">Valore ammesso</th>
<th class="report">Prezzo acquisto</th>
<th class="report">Originator</th>
<th class="report">Stato</th>
<th class="report">Data chiusura</th>
<th class="report">Motivo chiusura</th>
<th class="report">Data ultimo incasso</th>
<th class="report destra">% inc. prev.</th>
<th class="report">IRR Storico</th>
<th class="report">IRR Mensile</th>
<th class="report">IRR Annuale</th>
<th class="report destra">NPV</th>
<th class="report">Tipo flusso</th>
<?php
for($anno = $annoMin; $anno<=$annoMax; $anno++){
for($mese = 1; $mese <= 12; $mese++){
$mese = sprintf("%02s", $mese);
echo '<th class="report">'.$mese.'/'.$anno.'</th>';
}
}
?>
<th class="report">Totale</th>
</tr>
</thead>
<tbody>
<?php
//inizializzo l'array che contiene i valori dei totali di riga per mese per fare il totale di tabella
$flusso_complessivo = array();
for($anno = $annoMin; $anno<=$annoMax; $anno++){
for($mese = 1; $mese <= 12; $mese++){
$flusso_complessivo[] = $vuoto;
}
}
$k = 0;
foreach($creds as $cred){
?>
<!-- dati generali dei singoli crediti -->
<tr>
<td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo $cred['id_cre']; ?></td>
<td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo $cred['serie']; ?></td>
<td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo soff2name($cred['cod_soff'],$pdo); ?></td>
<td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo mysql2table($cred['data_acq']); ?></td>
<td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo prot2name($cred['cedente'],$pdo); ?></td>
<td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo number_format($cred['ammesso'],"2",",","."); ?></td>
<td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo number_format($cred['prezzo_acq'],"2",",","."); ?></td>
<td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo prot2name($cred['originator'],$pdo); ?></td>
<td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo $cred['stato']; ?></td>
<td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo ($cred['data_chius'] <> '0000-00-00') ? mysql2table($cred['data_chius']) : ''; ?></td>
<td class="report quarta" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>><?php echo chius2name($cred['caus_chius'],$pdo); ?></td>
<?php
$error = '';
$credito = $cred['id_cre'];
$last_inc = $pdo->query("SELECT max(data_inc) FROM incassi_row WHERE id_cre = '$credito'")->fetchColumn();
echo '<td class="report quarta" ';
echo ($dett == 1) ? 'rowspan="5"' : '';
echo '>';
echo ($last_inc <> '') ? mysql2table($last_inc).'</td>' : '</td>';
$perc_worst = $pdo->query("SELECT perc_worst FROM azioni_row JOIN azioni_head ON azioni_row.id_az = azioni_head.id_az WHERE id_cred = '$credito'")->fetchColumn();
echo '<td class="report quarta destra" ';
echo ($dett == 1) ? 'rowspan="5"' : '';
echo '>'.$perc_worst.'%</td>';
?>
<td class="report quarta" id="" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>>IRR Storico</td>
<td class="report quarta" id="irr_mese_<?php echo $k; ?>" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>>IRR Mensile</td>
<td class="report quarta" id="irr_anno_<?php echo $k; ?>" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>>IRR Annuale</td>
<td class="report quarta destra" id="" <?php echo ($dett == 1) ? 'rowspan="5"' : '' ?>>NPV</td>
<!-- dettagli acquisto -->
<?php
$k = $k + 1;
echo ($dett == 1) ? '<td class="report">Acquisto</td>' : '';
$acquisti = array();
$acquisto_tot = 0;
for($anno = $annoMin; $anno<=$annoMax; $anno++){
for($mese = 1; $mese <= 12; $mese++){
$mese = sprintf("%02s", $mese);
$inizio = $anno.'-'.$mese.'-01';
$fine = $anno.'-'.$mese.'-31';
$prezzo_acq = $pdo->query("SELECT prezzo_acq FROM crediti WHERE id_cre='$credito' AND data_acq BETWEEN '$inizio' AND '$fine'")->fetchColumn();
echo($dett == 1) ? '<td class="report">-'.num2cur($prezzo_acq).'</td>' : '';
array_push($acquisti,$prezzo_acq);
$acquisto_tot = $acquisto_tot + $prezzo_acq;
}
}
echo ($dett == 1) ? '<td class="report"><b>-'.num2cur($acquisto_tot).'</b></td></tr>' : '';
?>
<!-- dettagli incassi -->
<?php
echo ($dett == 1) ? '<tr><td class="report">Incasso</td>' : '';
$incasso_tot = 0;
$incassi = array();
for($anno = $annoMin; $anno<=$annoMax; $anno++){
for($mese = 1; $mese <= 12; $mese++){
$mese = sprintf("%02s", $mese);
$periodo = $mese.'/'.$anno;
try{
$sql = "SELECT incasso FROM bp_flussi WHERE id_cre='$credito' AND id_bp='$id_bp' AND periodo = '$periodo'";
$z = $pdo->prepare($sql);
$z->execute();
}
catch(PDOException $e){
$importo = $e->getMessage();
}
$incasso = $z->fetchColumn();
echo ($dett == 1) ? '<td class="report incasso _'.$credito.'">'.num2cur($incasso).'</td>' : '';
$incasso_tot = $incasso_tot + $incasso;
array_push($incassi,$incasso);
}
}
echo ($dett == 1) ? '<td class="report"><b>'.num2cur($incasso_tot).'</b></td></tr>' : '';
?>
<!-- dettagli spese -->
<?php
echo ($dett == 1) ? '<tr><td class="report">Spesa</td>' : '';
$spesa_tot = 0;
$spese = array();
for($anno = $annoMin; $anno<=$annoMax; $anno++){
for($mese = 1; $mese <= 12; $mese++){
$mese = sprintf("%02s", $mese);
$periodo = $mese.'/'.$anno;
try{
$sql = "SELECT spesa FROM bp_flussi WHERE id_cre='$credito' AND id_bp='$id_bp' AND periodo = '$periodo'";
$z = $pdo->prepare($sql);
$z->execute();
}
catch(PDOException $e){
$importo = $e->getMessage();
}
$spesa = $z->fetchColumn();
echo ($dett == 1) ? '<td class="report spesa_'.$credito.'">-'.num2cur($spesa).'</td>' : '';
$spesa_tot = $spesa_tot + $spesa;
$spese[] = $spesa;
}
}
echo ($dett == 1) ? '<td class="report"><b>-'.num2cur($spesa_tot).'</b></td></tr>' : '';
?>
<!-- dettagli vendita -->
<?php
echo ($dett == 1) ? '<tr><td class="report">Vendita</td>' : '';
$vendite = array();
$vendita_tot = 0;
for($anno = $annoMin; $anno<=$annoMax; $anno++){
for($mese = 1; $mese <= 12; $mese++){
$mese = sprintf("%02s", $mese);
$inizio = $anno.'-'.$mese.'-01';
$fine = $anno.'-'.$mese.'-31';
try{
$sql = "SELECT prezzo_chius FROM crediti WHERE id_cre='$credito' AND data_chius BETWEEN '$inizio' AND '$fine'";
$z = $pdo->prepare($sql);
$z->execute();
}
catch(PDOException $e){
$prezzo_ven = $e->getMessage();
}
$prezzo_ven = $z->fetchColumn();
echo ($dett == 1) ? '<td class="report">'.num2cur($prezzo_ven).'</td>' : '';
array_push($vendite, $prezzo_ven);
$vendita_tot = $vendita_tot + $prezzo_ven;
}
}
echo ($dett == 1) ? '<td class="report"><b>'.num2cur($vendita_tot).'</b></td></tr><tr class="quarta">' : '';
?>
<!-- righe totale -->
<td class="report quarta">Totale</td>
<?php
$i = 0;
$importo_tot = 0;
$irr_array = array();
for($anno = $annoMin; $anno<=$annoMax; $anno++){
for($mese = 1; $mese <= 12; $mese++){
$mese = sprintf("%02s", $mese);
$importo = $incassi[$i] + $vendite[$i] - $spese[$i] - $acquisti[$i];
echo ($importo >= 0) ? '<td class="report quarta success">'.num2cur($importo).'</td>' :
'<td class="report quarta err">'.num2cur($importo).'</td>';
$flusso_complessivo[$i] = $flusso_complessivo[$i] + $importo;
array_push($irr_array, $importo);
$i = $i + 1;
$importo_tot = $importo_tot + $importo;
}
}
echo '<td class="report quarta"><b>'.num2cur($importo_tot).'</b></td>';
?>
</tr>
<?php
foreach($irr_array as $value){
if($value == 0){
array_shift($irr_array);
}else{
break;
}
}
for($i = count($irr_array)-1; $i>=0; $i-- ){
if($irr_array[$i] == '0'){
array_pop($irr_array);
}else{
break;
}
}
if(count($irr_array)>0){
$irr_singolo = $f->IRR($irr_array);
if(is_null($irr_singolo)){$irr_singolo = '0.00';}
}else{
$irr_singolo = '0.00';
}
array_push($irr_mensile, $irr_singolo);
} //fine del foreach credito
?>
<tr>
<td class="report" colspan="18">Totale</td>
<?php
$i = 0;
$totalissimo = 0;
for($anno = $annoMin; $anno<=$annoMax; $anno++){
for($mese = 1; $mese <= 12; $mese++){
$valore = $flusso_complessivo[$i];
echo ($valore >= 0) ? '<td class="report success"><b>'.num2cur($valore).'</b></td>' : '<td class="report err"><b>'.num2cur($valore).'</b></td>';
$totalissimo += $valore;
$i = $i + 1;
}
}
?>
<td class="report"><b><?php echo num2cur($totalissimo); ?></b></td>
</tr>
</tbody>
</table>
<script type="text/javascript">
var irrs = <?php echo json_encode($irr_mensile); ?>;
var num_creds = irrs.length;
for(i=0; i<=num_creds; i++){
sing_irr_mese = irrs[i];
if(sing_irr_mese != '0.00'){ sing_irr_anno = 1 + sing_irr_mese; }else{ sing_irr_anno = '0.00';}
sing_irr_anno = Math.pow(sing_irr_anno,12);
if(sing_irr_anno != '0.00'){ sing_irr_anno = sing_irr_anno -1; }else{ sing_irr_anno = '0.00';}
sing_irr_mese= sing_irr_mese * 100;
sing_irr_anno = sing_irr_anno * 100;
$('#irr_mese_'+i).number(sing_irr_mese, 2,',','.').append('%');
$('#irr_anno_'+i).number(sing_irr_anno, 2,',','.').append('%');
}
</script>
</div>
.load()
and.ajax()
is not actually noticeable as time taken for server request and response will be simply be much more competitively. \$\endgroup\$