Currently, I am facing an issue with date filters on a line chart using Chart JS. After making some modifications, including updating the values of the date ranges, the chart seems to be malfunctioning. Strangely, there are no errors being logged in the console.
The setup involves the use of 3 PHP files: one for handling and managing a connection with SAP, another for managing the ajax call, and finally, the homepage containing the problematic chart.
The structure is as follows:
Here is the script snippet inside index.php responsible for the faulty AJAX call:
$(document).ready(function(){
$('#selector-ceco').on('change', function(){
// -------------variables actualizacion graficas -----------
//console.log($('#fecha_i').val(), typeof $('#fecha_i').val());
var ceco = String($('#selector-ceco').val());
var fecha_i = $('#fecha_i').val().replace(/-/g, '');
var fecha_f = $('#fecha_f').val().replace(/-/g, '');
//if (ceco==0) {ceco="";};
console.log(typeof fecha_f, fecha_f);
var montos = []; var fechas = []; var gasto_total = 0; var dates =[];
$.ajax({
url: "inc/cargar_hist_cecos.php",
type: 'POST',
data: {'ceco_electo':ceco, 'fecha_i': fecha_i, 'fecha_f': fecha_f},
dataType: 'json',
success: function(retorno, status){
console.log("llegue");
for (let i = 0 ; i<retorno.length ; i++){
montos.push(parseFloat(retorno[i]['TOTAL']));
fechas.push(retorno[i]['FECHA'].replace(/(\d\d\d\d)(\d\d)/,'$1-$2'));
//dates.push(Date(retorno[i]['FECHA']));
gasto_total += parseFloat(retorno[i]['TOTAL']);
};
//console.log(typeof dates[0], Date(fechas[0]));
gasto_ceco.data.datasets[0].data = montos;
gasto_ceco.data.labels = fechas;
gasto_ceco.update();
}
});
Next is the code snippet for the AJAX call handler:
<?php
include_once 'db.inc.php';
session_start();
if(isset($_POST['ceco_electo'])){
$CONN_data = new DbS;
$valores = $CONN_data->gasto_ceco( $_POST['ceco_electo'] , $_SESSION['grupo'], $_POST['fecha_i'], $_POST['fecha_f']);
$hist = json_encode($valores);
echo $hist;
}else{
echo "error";
};
?>
And here's the snippet from the connection file:
public function gasto_ceco ($CECO, $tipo, $fecha_i, $fecha_f){
$pdo = $this->connect_sap();
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = <<<SQL
SELECT T2."OcrCode" as "CECO", sum(T2."Quantity"*T2."Price") as "TOTAL", YEAR(T4."DocDate")*100+MONTH(T4."DocDate") as FECHA
FROM PCH1 T2 INNER JOIN OPCH T4 on T4."DocEntry"=T2."DocEntry"
WHERE T2."AcctCode" like '55%' AND T4."DocDate" > '20220101' AND T4."CANCELED" <> 'N ' AND T2."OcrCode" = {$CECO} AND T4."DocDate" > cast({$fecha_i} as VARCHAR) AND T4."DocDate" < CAST({$fecha_f} as VARCHAR)
GROUP BY T2."OcrCode", YEAR(T4."DocDate")*100+MONTH(T4."DocDate")
ORDER BY YEAR(T4."DocDate")*100+MONTH(T4."DocDate") ASC
SQL;
echo $sql;
$stmt = $pdo->prepare($sql);
$res = $stmt->execute();
echo $res;
try {
return $gasto_ceco = $stmt->fetchAll(PDO::FETCH_ASSOC);
}catch(PDOException $e){
echo "error: ".$e->getMessage();
};
}
};
I have attempted creating a separate file that calls the functions and the query successfully executes. Despite trying to capture any AJAX error messages, I haven't made much progress.
Prior to this problem, I had a similar function without fecha_i and fecha_f, along with corresponding modifications, which worked perfectly fine.
**EDIT**: In case it provides some insight, here is the HTML related to the chart:
<div class="card-body">
<div class="chart-area">
<input type="date" id="fecha_i" value="2022-01-01" name="fecha_i">
<input type="date" id="fecha_f" value="2023-12-31" name="fecha_f">
<canvas id="grafico-gasto-ceco"></canvas>
<script>
var labels= []; var data_gasto_raw = [];
var ctx_gasto_ceco = document.getElementById("grafico-gasto-ceco").getContext('2d');
var config = {
...
};
var gasto_ceco = new Chart(ctx_gasto_ceco, config);
</script>
</div>
</div>