Rev 1638 | Rev 1642 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
<?php/*** @category PHP* @package jrest* @author Raphaël Droz <raphael@tela-botania.org>* @copyright 2013 Tela-Botanica* @license http://www.cecill.info/licences/Licence_CeCILL_V2-fr.txt Licence CECILL* @license GPL v3 <http://www.gnu.org/licenses/gpl.txt>*//*** Service d'import de données d'observation du CEL au format XLS*/// sont define()'d commme n° de colonne tous les abbrevs retournés par ExportXLS::nom_d_ensemble_vers_liste_de_colonnes()// préfixés par C_ cf: detectionEntete()set_include_path(get_include_path() . PATH_SEPARATOR . dirname(dirname(realpath(__FILE__))) . '/lib');// TERMerror_reporting(-1);ini_set('html_errors', 0);ini_set('xdebug.cli_color', 2);require_once('lib/PHPExcel/Classes/PHPExcel.php');require_once('ExportXLS.php');date_default_timezone_set("Europe/Paris");// nombre d'INSERT à cumuler par requête SQL// (= nombre de lignes XLS à bufferiser)define('NB_LIRE_LIGNE_SIMUL', 30);// Numbers of days between January 1, 1900 and 1970 (including 19 leap years)// see traiterDateObs()define("MIN_DATES_DIFF", 25569);class MyReadFilter implements PHPExcel_Reader_IReadFilter {// exclusion de colonnespublic $exclues = array();// lecture par morceauxpublic $ligne_debut = 0;public $ligne_fin = 0;public function __construct() {}public function def_interval($debut, $nb) {$this->ligne_debut = $debut;$this->ligne_fin = $debut + $nb;}public function readCell($colonne, $ligne, $worksheetName = '') {if(@$this->exclues[$colonne]) return false;// si des n° de morceaux ont été initialisés, on filtre...if($this->ligne_debut && ($ligne < $this->ligne_debut || $ligne >= $this->ligne_fin)) return false;return true;}}class ImportXLS extends Cel {static $ordre_BDD = Array("ce_utilisateur","prenom_utilisateur","nom_utilisateur","courriel_utilisateur","ordre","nom_sel","nom_sel_nn","nom_ret","nom_ret_nn","nt","famille","nom_referentiel","zone_geo","ce_zone_geo","date_observation","lieudit","station","milieu","commentaire","transmission","date_creation","date_modification","latitude","longitude");/*Ces colonnes:- sont propre à tous les enregistrements uploadés- sont indépendantes du numéro de lignes- n'ont pas de valeur par défaut dans la structure de la table- nécessitent une initialisation dans le cadre de l'upload*/public $colonnes_statiques = Array("ce_utilisateur" => NULL,"prenom_utilisateur" => NULL,"nom_utilisateur" => NULL,"courriel_utilisateur" => NULL,// XXX: fixes (mais pourraient varier dans le futur si la mise-à-jour// d'observation est implémentée"date_creation" => NULL, // ne peut initialiser d'une date ici"date_modification" => NULL, // idem, cf initialiser_colonnes_statiques());function ExportXLS($config) {parent::__construct($config);}function createElement($pairs) {if(!isset($pairs['utilisateur']) || trim($pairs['utilisateur']) == '') {echo '0'; exit;}$id_utilisateur = intval($pairs['utilisateur']);if(!isset($_SESSION)) session_start();$this->controleUtilisateur($id_utilisateur);$this->utilisateur = $this->getInfosComplementairesUtilisateur($id_utilisateur);$this->initialiser_colonnes_statiques($id_utilisateur);$infos_fichier = array_pop($_FILES);/*$objPHPExcel = PHPExcel_IOFactory::load($infos_fichier['tmp_name']);$donnees = $objPHPExcel->getActiveSheet()->toArray(NULL,FALSE,FALSE,TRUE);*//*$objReader = PHPExcel_IOFactory::createReader("Excel5");$objReader->setReadDataOnly(true);$objPHPExcel = $objReader->load($infos_fichier['tmp_name']);*///var_dump($donnees);$objReader = PHPExcel_IOFactory::createReader("Excel5");$objReader->setReadDataOnly(true);// on ne conserve que l'en-tête$filtre = new MyReadFilter();$filtre->def_interval(1, 2);$objReader->setReadFilter($filtre);$objPHPExcel = $objReader->load($infos_fichier['tmp_name']);$obj_infos = $objReader->listWorksheetInfo($infos_fichier['tmp_name']);// XXX: indépendant du readFilter ?$nb_lignes = $obj_infos[0]['totalRows'];$donnees = $objPHPExcel->getActiveSheet()->toArray(NULL, FALSE, FALSE, TRUE);$filtre->exclues = self::detectionEntete($donnees[1]);$obs_ajouts = 0;$obs_maj = 0;$dernier_ordre = $this->requeter("SELECT MAX(ordre) AS ordre FROM cel_obs WHERE ce_utilisateur = $id_utilisateur");$dernier_ordre = intval($dernier_ordre[0]['ordre']) + 1;if(! $dernier_ordre) $dernier_ordre = 0;// lecture par morceaux (chunks), NB_LIRE_LIGNE_SIMUL lignes à fois// pour aboutir des requêtes SQL d'insert groupés.for($ligne = 2; $ligne < $nb_lignes + NB_LIRE_LIGNE_SIMUL; $ligne += NB_LIRE_LIGNE_SIMUL) {$filtre->def_interval($ligne, NB_LIRE_LIGNE_SIMUL);$objReader->setReadFilter($filtre);/* recharge avec $filtre actif (filtre sur lignes colonnes):- exclue les colonnes inutiles/inutilisables)- ne selectionne que les lignes dans le range [$ligne - $ligne + NB_LIRE_LIGNE_SIMUL] */$objPHPExcel = $objReader->load($infos_fichier['tmp_name']);$donnees = $objPHPExcel->getActiveSheet()->toArray(NULL, FALSE, FALSE, TRUE);// ici on appel la fonction qui fera effectivement l'insertion multiple// à partir des (au plus) NB_LIRE_LIGNE_SIMUL lignes// TODO: passer $this, ne sert que pour appeler des méthodes public qui pourraient être statiques// notamment dans RechercheInfosTaxonBeta.phpself::chargerLignes($donnees, $this->colonnes_statiques, $dernier_ordre, $this, $obs_ajouts, $obs_maj);}die('end');}static function detectionEntete($entete) {$colonnes_reconnues = Array();$cols = ExportXLS::nom_d_ensemble_vers_liste_de_colonnes('standard');foreach($entete as $k => $v) {$entete_simple = iconv('UTF-8', 'ASCII//TRANSLIT', strtolower(trim($v)));foreach($cols as $col) {$entete_officiel_simple = iconv('UTF-8', 'ASCII//TRANSLIT', strtolower(trim($col['nom'])));$entete_officiel_abbrev = $col['abbrev'];if($entete_simple == $entete_officiel_simple || $entete_simple == $entete_officiel_abbrev) {// debug echo "define C_" . strtoupper($entete_officiel_abbrev) . ", $k\n";define("C_" . strtoupper($entete_officiel_abbrev), $k);$colonnes_reconnues[$k] = 1;break;}}}// prépare le filtre de PHPExcel qui évitera le traitement de toutes les colonnes superflues// eg: diff ( Array( H => Commune, I => rien ) , Array( H => 1, K => 1 )// ==> Array( I => rien )$colonnesID_non_reconnues = array_diff_key($entete, $colonnes_reconnues);// des colonnes de ExportXLS::nom_d_ensemble_vers_liste_de_colonnes()// ne retient que celles marquées "importables"$colonnes_automatiques = array_filter($cols, function($v) { return !$v['importable']; });// ne conserve que le nom long pour matcher avec la ligne XLS d'entêtearray_walk($colonnes_automatiques, function(&$v) { $v = $v['nom']; });// intersect ( Array ( N => Milieu, S => Ordre ), Array ( ordre => Ordre, phenologie => Phénologie ) )// ==> Array ( S => Ordre, AA => Phénologie )$colonnesID_a_exclure = array_intersect($entete, $colonnes_automatiques);// TODO: pourquoi ne pas comparer avec les abbrevs aussi ?// merge ( Array( I => rien ) , Array ( S => Ordre, AA => Phénologie ) )// ==> Array ( I => rien, AA => Phénologie )return array_merge($colonnesID_non_reconnues, $colonnesID_a_exclure);}/** charge un groupe de lignes*/static function chargerLignes($lignes, $colonnes_statiques, &$dernier_ordre, $cel, &$inserted, &$updated) {$enregistrement = NULL;$enregistrements = Array();$images = Array();foreach($lignes as $ligne) {$ligne = array_filter($ligne, function($cell) { return !is_null($cell); });if(!$ligne) continue;if( ($enregistrement = self::chargerLigne($ligne, $colonnes_statiques, $dernier_ordre, $cel)) ) {$enregistrements[] = $enregistrement;if(isset($enregistrement['_images'])) {$pos = count($enregistrements) - 1;// ne dépend pas de cel_obs, et seront insérées *après* les enregistrements// mais nous ne voulons pas nous priver de faire des INSERT multiples pour autant$images[] = Array("text" => $enregistrements[$pos]['_images'],"obs_pos" => $pos);// ce champ n'a pas a faire partie de l'insertion dans cel_obs,// mais est utile pour cel_obs_imagesunset($enregistrements[$pos]['_images']);}$dernier_ordre++;}}if(!$enregistrements) die('AIE // XXX');$req = '';foreach($enregistrements as $enregistrement)$req .= implode(', ', self::sortArrayByArray($enregistrement, self::$ordre_BDD));print_r($req);// $cel->executer($req);// transactionnel + auto-inc$lastid = $cel->bdd->lastInsertId();foreach($images as $image) {$obs = $enregistrements[$image["obs_pos"]];$id_obs = $lastid // dernier autoinc inséré- count($enregistrements) - 1 // correspondrait au premier autoinc+ $image["obs_pos"]; // ordre d'insertion = ordre dans le tableau $enregistrements// TODO: INSERT}}static function chargerLigne($ligne, $colonnes_statiques, $dernier_ordre, $cel) {// en premier car le résultat est utile pour// traiter longitude et latitude (traiterLonLat())$referentiel = self::identReferentiel($ligne[C_NOM_REFERENTIEL]);// $espece est rempli de plusieurs informations$espece = Array();self::traiterEspece($ligne, $espece, $cel);return array_merge($colonnes_statiques,// Dans ce tableau, seules devraient apparaître les données variable pour chaque ligne.// Dans ce tableau, l'ordre des clefs n'importe pas (cf: self::sortArrayByArray())Array("ordre" => $dernier_ordre,"nom_sel" => $espece[C_NOM_SEL],"nom_sel_nn" => $espece[C_NOM_SEL_NN],"nom_ret" => $espece[C_NOM_RET],"nom_ret_nn" => $espece[C_NOM_RET_NN],"nt" => $espece[C_NT],"famille" => $espece[C_FAMILLE],"nom_referentiel" => $referentiel,"zone_geo" => TODO,"ce_zone_geo" => self::traiterDepartement(trim($ligne[C_CE_ZONE_GEO])),"date_observation" => self::traiterDateObs($ligne[C_DATE_OBSERVATION]),"lieudit" => trim($ligne[C_LIEUDIT]),"station" => trim($ligne[C_STATION]),"milieu" => trim($ligne[C_MILIEU]),"commentaire" => trim($ligne[C_COMMENTAIRE]), // TODO: foreign-key"transmission" => in_array(strtolower(trim($ligne[C_TRANSMISSION])), array(1, 'oui')) ? 1 : 0,"latitude" => self::traiterLonLat(NULL, $ligne[C_LATITUDE], $referentiel),"longitude" => self::traiterLonLat($ligne[C_LONGITUDE], NULL, $referentiel),));}/* FONCTIONS de TRANSFORMATION de VALEUR DE CELLULE */// TODO: PHP 5.3, utiliser date_parse_from_format()// TODO: parser les heures (cf product-owner)// TODO: passer par le timestamp pour s'assurer de la validitéstatic function traiterDateObs($date) {// TODO: see https://github.com/PHPOffice/PHPExcel/issues/208if(is_double($date)) {if($date > 0)return PHPExcel_Style_NumberFormat::toFormattedString($date, PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2) . " 00:00:00";throw new Exception("erreur: date antérieure à 1970 et format de cellule \"DATE\" utilisés ensemble");// attention, UNIX timestamp, car Excel les décompte depuis 1900// cf http://fczaja.blogspot.fr/2011/06/convert-excel-date-into-timestamp.html// $timestamp = ($date - MIN_DATES_DIFF) * 60 * 60 * 24 - time(); // NON// $timestamp = PHPExcel_Calculation::getInstance()->calculateFormula("=" . $date . "-DATE(1970,1,1)*60*60*24"); // NON// echo strftime("%Y/%m/%d 00:00:00", $timestamp); // NON}else {$timestamp = strtotime($date);if(!$timestamp) return NULL; // TODO: throw errorreturn strftime("%Y-%m-%d 00:00:00", strtotime($date));}}static function identReferentiel($referentiel) {// SELECT DISTINCT nom_referentiel, COUNT(id_observation) AS count FROM cel_obs GROUP BY nom_referentiel ORDER BY count DESC;if(strpos(strtolower($referentiel), 'bdtfx') !== FALSE) return 'bdtfx:v1.01';if(strpos(strtolower($referentiel), 'bdtxa') !== FALSE) return 'bdtxa:v1.00';if(strpos(strtolower($referentiel), 'bdnff') !== FALSE) return 'bdnff:4.02';if(strpos(strtolower($referentiel), 'isfan') !== FALSE) return 'isfan:v1.00';return NULL;/* TODO: cf story,En cas de NULL faire une seconde passe de détection à partir du nom saisie */}/* NON!Un taxon d'un référentiel donné peut être théoriquement observé n'importe où sur le globe.Il n'y a pas lieu d'effectuer des restriction ici.Cependant des erreurs fréquentes (0,0 ou lon/lat inversées) peuvent être détectés ici.TODO */static function traiterLonLat($lon = NULL, $lat = NULL, $referentiel = 'bdtfx:v1.01') {// verifier format decimal +// + limite france si bdtfx ou bdtxa$bbox = self::getReferentielBBox($referentiel);if(!$bbox) return NULL;if($lon) {if($lon < $bbox['EST'] && $lon > $bbox['OUEST']) return is_numeric($lon) ? $lon : NULL;else return NULL;}if($lat) {if($lat < $bbox['NORD'] && $lat > $bbox['SUD']) return is_numeric($lat) ? $lat : NULL;return NULL;}}static function traiterEspece($ligne, Array &$espece, $cel) {$taxon_info_webservice = new RechercheInfosTaxonBeta($cel->config);$ascii = iconv('UTF-8', 'ASCII//TRANSLIT', $ligne[C_NOM_SEL]);$resultat_recherche_espece = $taxon_info_webservice->rechercherInfosSurTexteCodeOuNumTax($ligne[C_NOM_SEL]);// on supprime les noms retenus et renvoi tel quel// on réutilise les define pour les noms d'indexes, tant qu'à faireif (empty($resultat_recherche_espece['en_id_nom'])) {$espece[C_NOM_SEL] = $ligne[C_NOM_SEL];$espece[C_NOM_SEL_NN] = $ligne[C_NOM_SEL_NN];// TODO: si empty(C_NOM_SEL) et !empty(C_NOM_SEL_NN) : recherche info à partir de C_NOM_SEL_NN$espece[C_NOM_RET] = $ligne[C_NOM_RET];$espece[C_NOM_RET_NN] = $ligne[C_NOM_RET_NN];$espece[C_NT] = $ligne[C_NT];$espece[C_FAMILLE] = $ligne[C_FAMILLE];return;}// succès de la détection, récupération des infos$espece[C_NOM_SEL] = $resultat_recherche_espece['nom_sel'];$espece[C_NOM_SEL_NN] = $resultat_recherche_espece['en_id_nom'];$complement = $taxon_info_webservice->rechercherInformationsComplementairesSurNumNom($resultat_recherche_espece['en_id_nom']);$espece[C_NOM_RET] = $complement['Nom_Retenu'];$espece[C_NOM_RET_NN] = $complement['Num_Nom_Retenu'];$espece[C_NT] = $complement['Num_Taxon'];$espece[C_FAMILLE] = $complement['Famille'];}static function traiterDepartement($departement) {if(strpos($departement, "INSEE-C:", 0) === 0) return $departement;if(!is_numeric($departement)) return NULL; // TODO ?if(strlen($departement) == 4) return "INSEE-C:0" . $departement;if(strlen($departement) == 5) return "INSEE-C:" . $departement;// if(strlen($departement) <= 9) return "INSEE-C:0" . $departement; // ? ... TODOreturn trim($departement); // TODO}/* HELPERS */// http://stackoverflow.com/questions/348410/sort-an-array-based-on-another-arraystatic function sortArrayByArray($array, $orderArray) {$ordered = array();foreach($orderArray as $key) {if(array_key_exists($key, $array)) {$ordered[$key] = $array[$key];unset($array[$key]);}}return $ordered + $array;}// retourne une BBox [N,S,E,O) pour un référentiel donnéstatic function getReferentielBBox($referentiel) {if($referentiel == 'bdtfx:v1.01') return Array('NORD' => 51.2, // Dunkerque'SUD' => 41.3, // Bonifacio'EST' => 9.7, // Corse'OUEST' => -5.2); // Ouessanreturn FALSE;}public function initialiser_colonnes_statiques($id_utisateur) {$this->colonnes_statiques = array_merge($this->colonnes_statiques,Array("ce_utilisateur" => $id_utisateur,"prenom_utilisateur" => $this->utilisateur['prenom'],"nom_utilisateur" => $this->utilisateur['nom'],"courriel_utilisateur" => $this->utilisateur['courriel'],"date_creation" => date("Y-m-d H:i:s"),"date_modification" => date("Y-m-d H:i:s"),));}}