Subversion Repositories eFlore/Applications.cel

<?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');
// TERM
error_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 colonnes
        public $exclues = array();

        // lecture par morceaux
    public $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.php
                        self::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ête
                array_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_images
                                        unset($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/208
                if(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 error
                        return 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'à faire
                if (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; // ? ... TODO
                return trim($departement); // TODO
        }


        /* HELPERS */

        // http://stackoverflow.com/questions/348410/sort-an-array-based-on-another-array
        static 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); // Ouessan
                return 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"),
                                                                                                ));

        }
}