176 lines
7.3 KiB
PHP
176 lines
7.3 KiB
PHP
|
<?php
|
||
|
|
include('include/entete.php');
|
||
|
|
include('include/entete_adm.php');
|
||
|
|
include('config/config.inc.php');
|
||
|
|
include('config/lib.inc.php');
|
||
|
|
include('config/lib_personal_PhPExcel.php');
|
||
|
|
|
||
|
|
function downloadFile($file) {
|
||
|
|
$ar_ext = explode('.', $file);
|
||
|
|
$ext = strtolower(end($ar_ext));
|
||
|
|
$extensions = array(
|
||
|
|
'bmp' => 'image/bmp',
|
||
|
|
'csv' => 'text/csv',
|
||
|
|
'doc' => 'application/msword',
|
||
|
|
'docx' => 'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
|
||
|
|
'exe' => 'application/octet-stream',
|
||
|
|
'gif' => 'image/gif',
|
||
|
|
'htm' => 'text/html',
|
||
|
|
'html' => 'text/html',
|
||
|
|
'ico' => 'image/vnd.microsoft.icon',
|
||
|
|
'jpeg' => 'image/jpg',
|
||
|
|
'jpe' => 'image/jpg',
|
||
|
|
'jpg' => 'image/jpg',
|
||
|
|
'pdf' => 'application/pdf',
|
||
|
|
'png' => 'image/png',
|
||
|
|
'ppt' => 'application/vnd.ms-powerpoint',
|
||
|
|
'psd' => 'image/psd',
|
||
|
|
'swf' => 'application/x-shockwave-flash',
|
||
|
|
'tif' => 'image/tiff',
|
||
|
|
'tiff' => 'image/tiff',
|
||
|
|
'xhtml' => 'application/xhtml+xml',
|
||
|
|
'xml' => 'application/xml',
|
||
|
|
'xls' => 'application/vnd.ms-excel',
|
||
|
|
'xlsx' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
|
||
|
|
'zip' => 'application/zip'
|
||
|
|
);
|
||
|
|
$ctype = isset($extensions[$ext]) ? $extensions[$ext] : 'application/force-download';
|
||
|
|
|
||
|
|
if (file_exists($file) && is_readable($file)) {
|
||
|
|
// required for IE, otherwise Content-disposition is ignored
|
||
|
|
if(ini_get('zlib.output_compression')) ini_set('zlib.output_compression', 'Off');
|
||
|
|
|
||
|
|
|
||
|
|
header('Pragma: public'); // required
|
||
|
|
header('Expires: 0');
|
||
|
|
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
|
||
|
|
header('Cache-Control: private',false); // required for certain browsers
|
||
|
|
header('Content-Type: '. $ctype);
|
||
|
|
header('Content-Disposition: attachment; filename='. $file .';' );
|
||
|
|
header('Content-Transfer-Encoding: binary');
|
||
|
|
header('Content-Length: '. filesize($file));
|
||
|
|
readfile($file);
|
||
|
|
}
|
||
|
|
else {
|
||
|
|
header('HTTP/1.0 404 Not Found');
|
||
|
|
echo "<h1>Error 404: File Not Found: <br /><em>$file</em></h1>";
|
||
|
|
}
|
||
|
|
}
|
||
|
|
$maCleDeCryptage = getCleDeCryptage();
|
||
|
|
|
||
|
|
$pathFic = 'export/';
|
||
|
|
$filename=date("Ymd")."_suivi_cfa.xls";
|
||
|
|
$finalData=array();
|
||
|
|
$cpt=1;
|
||
|
|
|
||
|
|
//libelles
|
||
|
|
$finalData[0]['A']=iconv("UTF-8", "ISO-8859-1//TRANSLIT","Etablissement");
|
||
|
|
$finalData[0]['B']=iconv("UTF-8", "ISO-8859-1//TRANSLIT","Adresse 1");
|
||
|
|
$finalData[0]['C']=iconv("UTF-8", "ISO-8859-1//TRANSLIT","Adresse 2");
|
||
|
|
$finalData[0]['D']=iconv("UTF-8", "ISO-8859-1//TRANSLIT","Code postal");
|
||
|
|
$finalData[0]['E']=iconv("UTF-8", "ISO-8859-1//TRANSLIT","Ville");
|
||
|
|
$finalData[0]['F']=iconv("UTF-8", "ISO-8859-1//TRANSLIT","Référent");
|
||
|
|
$finalData[0]['G']=iconv("UTF-8", "ISO-8859-1//TRANSLIT","Tél référent");
|
||
|
|
$finalData[0]['H']=iconv("UTF-8", "ISO-8859-1//TRANSLIT","Email référent");
|
||
|
|
$finalData[0]['I']=iconv("UTF-8", "ISO-8859-1//TRANSLIT","Inscriptions");
|
||
|
|
$finalData[0]['J']=iconv("UTF-8", "ISO-8859-1//TRANSLIT","Sollicitations");
|
||
|
|
$finalData[0]['K']=iconv("UTF-8", "ISO-8859-1//TRANSLIT","Rendez-vous");
|
||
|
|
$finalData[0]['L']=iconv("UTF-8", "ISO-8859-1//TRANSLIT","Classes");
|
||
|
|
$finalData[0]['M']=iconv("UTF-8", "ISO-8859-1//TRANSLIT","Elèves");
|
||
|
|
$finalData[0]['N']=iconv("UTF-8", "ISO-8859-1//TRANSLIT","Date dernière connexion");
|
||
|
|
$query='select * from eta';
|
||
|
|
$db = connect_base();
|
||
|
|
$queryRequest = $db->prepare( $query );
|
||
|
|
$queryRequest->execute();
|
||
|
|
$data=$queryRequest->fetchAll();
|
||
|
|
foreach($data as $cle=>$val){
|
||
|
|
$eta=$add1=$add2=$cp=$ville=$nom_ref=$tel_ref=$email_ref="";
|
||
|
|
$eta=decrypter($maCleDeCryptage,$val['nom_eta']);
|
||
|
|
$add1=decrypter($maCleDeCryptage,$val['adresse1']);
|
||
|
|
$add2=decrypter($maCleDeCryptage,$val['adresse2']);
|
||
|
|
$cp=decrypter($maCleDeCryptage,$val['cp']);
|
||
|
|
$ville=decrypter($maCleDeCryptage,$val['ville']);
|
||
|
|
$nom_ref=decrypter($maCleDeCryptage,$val['nom_ref']);
|
||
|
|
$tel_ref=decrypter($maCleDeCryptage,$val['tel_ref']);
|
||
|
|
$email_ref=decrypter($maCleDeCryptage,$val['email_ref']);
|
||
|
|
$derniereCo = MysqlToDateFr($val['date_co']);
|
||
|
|
if($derniereCo == "01/01/1970") $derniereCo = "";
|
||
|
|
|
||
|
|
|
||
|
|
//NB Trinômes Total
|
||
|
|
$query="select * from capitaines,equipes where id_cap=num_capitaine and capitaines.num_eta=:id_eta";
|
||
|
|
$db2 = connect_base();
|
||
|
|
$queryRequest2 = $db2->prepare( $query );
|
||
|
|
$queryRequest2->bindParam( ':id_eta', $val['id_eta'], PDO::PARAM_INT );
|
||
|
|
$queryRequest2->execute();
|
||
|
|
$trinomes=$queryRequest2->rowCount();
|
||
|
|
|
||
|
|
if($trinomes == 0 ){
|
||
|
|
$trinomes = "Non inscrit";
|
||
|
|
} else {
|
||
|
|
if ($trinomes == 1 ) $trinomes = $trinomes.' trinôme';
|
||
|
|
else $trinomes = $trinomes.' trinômes';
|
||
|
|
}
|
||
|
|
|
||
|
|
//NB de RDV, de classes et d'élèves
|
||
|
|
$query3="SELECT count(*) as rdvs, sum(nbClasses) as classes, sum(nbEleves) as eleves
|
||
|
|
FROM agenda
|
||
|
|
INNER JOIN collegelycee ON idCoLy=numCoLy
|
||
|
|
WHERE statut= 'reserve' AND numEta=:numEta";
|
||
|
|
$db3 = connect_base();
|
||
|
|
$queryRequest3 = $db3->prepare( $query3 );
|
||
|
|
$queryRequest3->bindParam( ':numEta', $val['id_eta'], PDO::PARAM_INT );
|
||
|
|
$queryRequest3->execute();
|
||
|
|
$data3=$queryRequest3->fetchAll();
|
||
|
|
if(isset($data3[0]['rdvs'])) $countRDV = $data3[0]['rdvs']; else $countRDV = 0;
|
||
|
|
if(isset($data3[0]['classes'])) $classes = $data3[0]['classes']; else $classes = 0;
|
||
|
|
if(isset($data3[0]['eleves'])) $eleves = $data3[0]['eleves']; else $eleves = 0;
|
||
|
|
|
||
|
|
|
||
|
|
//NB total de sollicitations, nb total de classes et nb total d'élèves
|
||
|
|
$query4="SELECT count(*) as sollicitations, sum(nbClasses) as classes, sum(nbEleves) as eleves
|
||
|
|
FROM sollicitations
|
||
|
|
WHERE traitement<>1 AND chxNumEta=:chxNumEta";
|
||
|
|
$db4 = connect_base();
|
||
|
|
$queryRequest4 = $db4->prepare( $query4 );
|
||
|
|
$queryRequest4->bindParam( ':chxNumEta', $val['id_eta'], PDO::PARAM_INT );
|
||
|
|
$queryRequest4->execute();
|
||
|
|
$data4=$queryRequest4->fetchAll();
|
||
|
|
if(isset($data4[0]['sollicitations'])) $countSol = $data4[0]['sollicitations']; else $countSol = 0;
|
||
|
|
if(isset($data4[0]['classes'])) $classes = $classes + $data4[0]['classes'];
|
||
|
|
if(isset($data4[0]['eleves'])) $eleves = $eleves + $data4[0]['eleves'];
|
||
|
|
|
||
|
|
$finalData[$cpt]['A']=iconv("UTF-8", "ISO-8859-1//TRANSLIT",$eta);
|
||
|
|
$finalData[$cpt]['B']=iconv("UTF-8", "ISO-8859-1//TRANSLIT",$add1);
|
||
|
|
$finalData[$cpt]['C']=iconv("UTF-8", "ISO-8859-1//TRANSLIT",$add2);
|
||
|
|
$finalData[$cpt]['D']=iconv("UTF-8", "ISO-8859-1//TRANSLIT",$cp);
|
||
|
|
$finalData[$cpt]['E']=iconv("UTF-8", "ISO-8859-1//TRANSLIT",$ville);
|
||
|
|
$finalData[$cpt]['F']=iconv("UTF-8", "ISO-8859-1//TRANSLIT",$nom_ref);
|
||
|
|
$finalData[$cpt]['G']=iconv("UTF-8", "ISO-8859-1//TRANSLIT",$tel_ref);
|
||
|
|
$finalData[$cpt]['H']=iconv("UTF-8", "ISO-8859-1//TRANSLIT",$email_ref);
|
||
|
|
$finalData[$cpt]['I']=iconv("UTF-8", "ISO-8859-1//TRANSLIT",$trinomes);
|
||
|
|
$finalData[$cpt]['J']=iconv("UTF-8", "ISO-8859-1//TRANSLIT",$countSol);
|
||
|
|
$finalData[$cpt]['K']=iconv("UTF-8", "ISO-8859-1//TRANSLIT",$countRDV);
|
||
|
|
$finalData[$cpt]['L']=iconv("UTF-8", "ISO-8859-1//TRANSLIT",$classes);
|
||
|
|
$finalData[$cpt]['M']=iconv("UTF-8", "ISO-8859-1//TRANSLIT",$eleves);
|
||
|
|
$finalData[$cpt]['N']=iconv("UTF-8", "ISO-8859-1//TRANSLIT",$derniereCo);
|
||
|
|
$cpt++;
|
||
|
|
}
|
||
|
|
|
||
|
|
$attributsFichier = array();
|
||
|
|
$attributsFichier[0] = "Export Deciday";
|
||
|
|
$attributsFichier[1] = "Export Deciday";
|
||
|
|
$attributsFichier[2] = "Export Deciday";
|
||
|
|
$attributsFichier[3] = "Export Deciday";
|
||
|
|
$attributsFichier[4] = "Export Deciday";
|
||
|
|
$attributsFichier[5] = "Export Deciday";
|
||
|
|
$attributsFichier[6] = "Export Deciday";
|
||
|
|
$attributsFichier[7] = "Export Deciday";
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
writingArrayWithLetterToExcel5WithType($pathFic.$filename, 0, "DECIDAY", $finalData, $attributsFichier, PHPExcel_Cell_DataType::TYPE_STRING);
|
||
|
|
|
||
|
|
|
||
|
|
downloadFile($pathFic.$filename);
|