I'm trying to export the data from the database to excel with laravel 5.0. To achieve this I use a stored procedure to get data.
BEGIN
SET SESSION group_concat_max_len = (3*1024);
SET @SQL = NULL;
SELECT GROUP_CONCAT(DISTINCT
CONCAT('MAX(CASE WHEN se.date_seance = ', CONCAT('\'',se.date_seance,'\'') ,
' AND v.faux_gout = ',CONCAT('\'',v.faux_gout,'\''),
' THEN uv.point END) '
, CONCAT('\'',DATE_FORMAT(se.date_seance,'%d-%M-%Y'),'\''), CONCAT('\'faux_gout:',v.faux_gout,'\'') ))
INTO @SQL
FROM user_verre uv
LEFT JOIN users u ON uv.user_id = u.id
LEFT JOIN verres v ON uv.verre_id = v.id
LEFT JOIN series s ON v.serie_id = s.id
LEFT JOIN seances se ON se.id = s.seance_id
WHERE v.est_faux = 1 AND se.statut = 1 AND se.etablissement_id = _labo AND (se.date_seance BETWEEN _debut AND _fin);
#SELECT @SQL 'jour-2';
SET @SQL = CONCAT('SELECT u.name,', IFNULL(@SQL,'u.name ') ,
'FROM user_verre uv
LEFT JOIN users u ON uv.user_id = u.id
LEFT JOIN verres v ON uv.verre_id = v.id
LEFT JOIN series s ON v.serie_id = s.id
LEFT JOIN seances se ON se.id = s.seance_id
WHERE v.est_faux = 1 AND se.statut = 1 AND se.etablissement_id = ',_labo,' AND (se.date_seance BETWEEN ', '\'',_debut,'\'' , ' AND ' ,'\'',_fin,'\'' ,')
GROUP BY u.name');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
and the php code belows
$alphabet = range('A', 'Z');
return Excel::create('performance '.join('-',$date), function($excel) use ($usines,$date , $alphabet){
$excel->setTitle('performance');
$pas = 0;
foreach ($usines as $usine_id) {
//Call the stored process
$result = DB::select("CALL performance_month(?, ?, ?)",[$date[0],$date[1],$usine_id]);
$labo = Etablissement::find($usine_id);
$data = array();
if(count($result)!=0){
//build the table to display in the excel file
foreach ($result as $key => $value) {
$ligne = get_object_vars($value);
$entete = array();
foreach($ligne as $k => $l){
array_push($entete , $k);
}
$pas = count($entete);
array_push($entete , '% DETECTION');
$data [] = $entete;
break;
}
$line = 4;
$lettre = $alphabet[$pas-1];
foreach ($result as $y => $val) {
$val = (array) $val;
array_push($val,'=SUM(B'.$line.':'.$lettre.$line.')/COUNTA(B'.$line.':'.$lettre.$line.')*100');
$data [] = $val;
$line++;
}
}
$excel->sheet($labo->nom, function($sheet) use($data) {
$sheet->fromArray($data,null,'A3',true,false);
});
}
})->download('xlsx');
the screenshoot belows show the good result in dev environment
but when i deploy on server i get this result
in dev environment i use mysql 5.7.21
library for excel export "maatwebsite/excel": "~2.1.0",
in prod environment i use mysql 5.6.17
but when i call the procedure in phpMyAdmin (prod and dev) i get the same result
someone has an idea of ββthe cause of malfunctioning??