Subversion Repositories eFlore/Applications.del

<?php

/**

 * DEL (Détermination en ligne [Pictoflora/Identiplante]) Toolkit

 * Quelques fonctions utiles, utilisées et/ou utilisables aussi bien par images/*, observations/*

 * et probablement d'autres, comme determination/*.

 *

 * Les domaines des fonctions tournent autour de 4 aspects:

 * - gestions des paramètres d'entrée utilisateurs, valeurs par défaut et sanitization

 * - génération de SQL

 * - processing de tableau de pattern d'utilisation SQL assez commun

 * - formattage basique de sortie (JSON)

 * + quelques helpers basiques

 *

 * @category	php 5.2

 * @package		del

 * @author		Raphaël Droz <raphael@tela-botanica.org>

 * @copyright	Copyright (c) 2013 Tela Botanica (accueil@tela-botanica.org)

 * @license	http://www.cecill.info/licences/Licence_CeCILL_V2-fr.txt Licence CECILL

 * @license	http://www.gnu.org/licenses/gpl.html Licence GNU-GPL

 */

define('_LISTE_OBS_MAX_RESULT_LIMIT', 1000);

define('_LISTE_OBS_MAX_ID_OBS', 10e7);

// SELECT MAX(num_taxonomique) FROM bdtfx_v2_00;

define('_LISTE_OBS_MAX_BDTFX_NT', 1000000); // 44378 + 1000

// SELECT MAX(num_nom) FROM bdtfx_v2_00;

define('_LISTE_OBS_MAX_BDTFX_NN', 1000000); // 120816 + 10000

class DelTk {

	static $parametres_autorises = array(

        'masque', 'masque.famille', 'masque.nn', 'masque.referentiel', // taxon

        'masque.genre', 'masque.espece', 'masque.ns', // nom_sel

        'masque.commune', 'masque.departement', 'masque.id_zone_geo', // loc

        'masque.auteur', 'masque.date', 'masque.tag', 'masque.type', // autres

        // tri, offset

        'navigation.depart', 'navigation.limite',

        'tri', 'ordre', // TODO: 'total=[yes]', 'fields=[x,y,...]'

        // TODO: masque.annee, masque.insee (!= departement)

	);

	static $default_params = array(

        'navigation.depart' => 0, 'navigation.limite' => 10,

        'tri' => 'date_transmission', 'ordre' => 'desc');

    // input filtering

	/* Construit un (vulgaire) abstract syntax tree:

	   "AND" => [ "tag1", "tag2" ]

	   Idéalement (avec un parser simple comme proposé par http://hoa-project.net/Literature/Hack/Compiler.html#Langage_PP)

	   nous aurions:

	   "AND" => [ "tag1", "tag2", "OR" => [ "tag3", "tag4" ] ]

	   Ici nous devons traiter les cas suivants:

	   tags séparés par des "ET/AND OU/OR", séparés par des espaces ou des virgules.

	   Mais la chaîne peut aussi avoir été issue du "masque général" (la barre de recherche générique).

	   ce qui implique des comportement par défaut différents afin de préserver la compatibilité.

	   Théorie:

	   1) tags passés par "champ tag":

	   - support du ET/OU, et explode par virgule.

	   - si pas d'opérande détectée: "OU"

	   2) tags passés par "recherche générale":

	   - support du ET/OU, et explode par whitespace.

	   - si pas d'opérande détectée: "ET"

	   La présence de $additional_sep s'explique car ET/OU sous-entendent une séparation par des espaces.

	   Mais ce n'est pas toujours pertinent car: 1) la compatibilité suggère de considérer parfois

	   la virgule comme séparateur et 2) les tags *peuvent* contenir des espaces. Par conséquent:

	   * a,b,c => "a" $default_op "b" $default_op "c"

	   * a,b AND c => "a" AND "b" AND "c"

	   * a OR b AND c,d => "a" AND "b" AND "c" AND "d"

	   C'est à dire par ordre décroissant de priorité:

	   1) opérande contenu dans la chaîne

	   2) opérande par défaut

	   3) les séparateurs présents sont substitués par l'opérande déterminée par 1) ou 2)

	   // TODO: support des parenthèses, imbrications & co: "(", ")"

	   // http://codehackit.blogspot.fr/2011/08/expression-parser-in-php.html

	   // http://blog.angeloff.name/post/2012/08/05/php-recursive-patterns/

	   @param $str: la chaîne à "parser"

	   @param $default_op: "AND" ou "OR"

	   @param $additional_sep: séparateur de mots:

	*/

	static function buildTagsAST($str = NULL, $default_op, $additional_sep = ',') {

		if(!$str) return;

		$words = preg_split('/ (OR|AND|ET|OU) /', $str, -1, PREG_SPLIT_NO_EMPTY);

		if(preg_match('/\b(ET|AND)\b/', $str)) $op = 'AND';

		elseif(preg_match('/\b(OU|OR)\b/', $str)) $op = 'OR';

		else $op = $default_op;

		if($additional_sep) {

			array_walk($words,

            create_function('&$v, $k, $sep', '$v = preg_split("/".$sep."/", $v, -1, PREG_SPLIT_NO_EMPTY);'),

            $additional_sep);

		}

		$words = DelTk::array_flatten($words);

		$words = array_map('trim', $words);

		return array($op => array_filter($words));

	}

	static function array_flatten($arr) {

		$arr = array_values($arr);

		while (list($k,$v)=each($arr)) {

			if (is_array($v)) {

				array_splice($arr,$k,1,$v);

				next($arr);

			}

		}

		return $arr;

	}

	// supprime l'index du tableau des paramètres si sa valeur ne correspond pas

	// au spectre passé par $values.

	static function unsetIfInvalid(&$var, $index, $values) {

		if(array_key_exists($index, $var)) {

			if(!in_array($var[$index], $values)) unset($var[$index]);

			else return $var[$index];

		}

		return NULL;

	}

	/* Filtre et valide les paramètres reconnus. Effectue *toute* la sanitization *sauf* l'escape-string

	   Cette fonction est appelée:

	   - une fois sur les champs de recherche avancées

	   - une fois sur le masque général si celui-ci à été spécifié. Dans ce cas,

	   la chaîne générale saisie est utilisée comme valeur pour chacun des champs particuliers

	   avec les traitements particuliers qui s'imposent

	   Par exemple: si l'on cherche "Languedoc", cela impliquera:

	   WHERE (nom_sel like "Languedoc" OR nom_ret ... OR ...) mais pas masque.date ou masque.departement

	   qui s'assure d'un pattern particulier */

	static function requestFilterParams(Array $params, $parametres_autorises = NULL, Conteneur $c = NULL /* pour la récup des départements */ ) {

		if($parametres_autorises) { // filtrage de toute clef inconnue

			$params = array_intersect_key($params, array_flip($parametres_autorises));

		}

		$p['tri'] = DelTK::unsetIfInvalid($params, 'tri', array('date_observation'));

		$p['ordre'] = DelTK::unsetIfInvalid($params, 'ordre', array('asc','desc'));

		$p['masque.referentiel'] = DelTK::unsetIfInvalid($params, 'masque.referentiel', array('bdtfx','bdtxa','isfan'));

		// TODO: use filter_input(INPUT_GET);

		// renvoie FALSE ou NULL si absent ou invalide

		$p['navigation.limite'] = filter_var(@$params['navigation.limite'],

		FILTER_VALIDATE_INT,

		array('options' => array('default' => NULL,

		'min_range' => 1,

		'max_range' => _LISTE_OBS_MAX_RESULT_LIMIT)));

		$p['navigation.depart'] = filter_var(@$params['navigation.depart'],

		FILTER_VALIDATE_INT,

		array('options' => array('default' => NULL,

		'min_range' => 0,

		'max_range' => _LISTE_OBS_MAX_ID_OBS)));

		if(isset($params['masque.departement'])) {

			// STRING: 0 -> 95, 971 -> 976, 2A + 2B (./services/configurations/config_departements_bruts.ini)

			// accept leading 0 ?

			// TODO; filter patterns like 555.

			if(preg_match(';^(\d{2}|\d{3}|2a|2b)$;i', $params['masque.departement'])) {

				$p['masque.departement'] = $params['masque.departement'];

			}

			// cf configurations/config_departements_bruts.ini

			elseif( !is_null($c) && ( $x = $c->getParametre(

				strtolower(str_replace(' ','-',iconv("UTF-8", "ASCII//TRANSLIT", $params['masque.departement'])))

			))) {

				$p['masque.departement'] = sprintf("INSEE-C:%02d___", $x);

			}

		}

		if(isset($params['masque.date'])) {

			// une année, TODO: masque.annee

			if(is_numeric($params['masque.date'])) {

				$p['masque.date'] = $params['masque.date'];

			}

			elseif(strpos($params['masque.date'], '/' !== false) &&

			($x = strtotime(str_replace('/','-',$params['masque.date'])))) {

				$p['masque.date'] = $x;

			}

			elseif(strpos($params['masque.date'], '-' !== false) &&

			($x = strtotime($params['masque.date'])) ) {

				$p['masque.date'] = $x;

			}

		}

		$p['masque.nn'] = filter_var(@$params['masque.nn'],

		FILTER_VALIDATE_INT,

		array('options' => array('default' => NULL,

		'min_range' => 0,

		'max_range' => _LISTE_OBS_MAX_BDTFX_NN)));

		$p['masque.nt'] = filter_var(@$params['masque.nt'],

		FILTER_VALIDATE_INT,

		array('options' => array('default' => NULL,

		'min_range' => 0,

		'max_range' => _LISTE_OBS_MAX_BDTFX_NT)));

		// TODO: should we really trim() ?

		if(isset($params['masque.ns'])) $p['masque.ns'] = trim($params['masque.ns']);

		// if(isset($params['masque.texte'])) $p['masque.texte'] = trim($params['masque.texte']);

		if(isset($params['masque.famille'])) {

			// mysql -N<<<"SELECT DISTINCT famille FROM bdtfx_v1_02;"|sed -r "s/(.)/\1\n/g"|sort -u|tr -d "\n"

			$p['masque.famille'] = preg_replace('/[^a-zA-Z %_]/', '', iconv("UTF-8",

			"ASCII//TRANSLIT",

			$params['masque.famille']));

		}

		// masque.genre est un alias pour masque.ns (nom_sel), mais permet de rajouter une clause supplémentaire

		// sur nom_sel. Précédemment: WHERE nom_sel LIKE '%<masque.genre>% %'.

		// Désormais masque.genre doit être intégralement spécifié, les caractères '%' et '_' seront interprétés.

		// Attention toutefois car la table del_observation intègre des nom_sel contenant '_'

		if(isset($params['masque.genre'])) $p['masque.genre'] = trim($params['masque.genre']);

		if(isset($params['masque.ns'])) $p['masque.ns'] = trim($params['masque.ns']);

		// masque.espece n'était pas déclaré dans la "where" mais utilisé via config + switch//default

		if(isset($params['masque.espece'])) $p['masque.espece'] = trim($params['masque.espece']);

		// idem pour id_zone_geo qui mappait à ce_zone_geo:

		if(isset($params['masque.id_zone_geo']) && preg_match(';^(INSEE-C:\d{5}|\d{2})$;', $params['masque.id_zone_geo'])) {

			$p['masque.id_zone_geo'] = $params['masque.id_zone_geo'];

		}

		// masque.commune (zone_geo)

		// TODO: que faire avec des '%' en INPUT ?

		// Le masque doit *permettre* une regexp et non l'imposer. Charge au client de faire son travail

		if(isset($params['masque.commune'])) $p['masque.commune'] = str_replace(array('-',' '), '_', $params['masque.commune']);

		// masque.auteur: peut-être un id, un courriel, ou un nom ou prénom, ...

		if(isset($params['masque.auteur'])) $p['masque.auteur'] = trim($params['masque.auteur']);

		// sera trimmé plus tard, cf sqlAddConstraint

		if(isset($params['masque'])) $p['masque'] = trim($params['masque']);

		// masque.tag, idem que pour masque.genre et masque.commune

		if(isset($params['masque.tag'])) {

			$x = explode(',',$params['masque.tag']);

			$x = array_map('trim', $x);

			$p['masque.tag'] = implode('|', array_filter($x));

		}

		// masque.type: ['adeterminer', 'aconfirmer', 'endiscussion', 'validees']

		if(isset($params['masque.type'])) {

			$p['masque.type'] = array_flip(array_intersect(array_filter(explode(';', $params['masque.type'])),

			array('adeterminer', 'aconfirmer', 'endiscussion', 'validees')));

		}

		// TODO: masque (général)

		// on filtre les NULL, FALSE et '', mais pas les 0, d'où le callback()

		// TODO: PHP-5.3

		return array_filter($p, create_function('$a','return !in_array($a, array("",false,null),true);'));

	}

    // SQL helpers

	/* Lorsque l'on concatène des champs, un seul NULL prend le dessus,

	   Il faut donc utiliser la syntaxe IFNULL(%s, "").

	   (Cette fonction effectue aussi l'implode() "final" */

	static function sqlAddIfNullPourConcat($tab) {

		// XXX: PHP-5.3

		return implode(',',array_map(create_function('$a', 'return "IFNULL($a, \"\")";'), $tab));

	}

	/* Converti un tableau associatif et un préfix optionnel en une chaîne de champs adéquate

	   à un SELECT MySQL.

	   $select (optionnel) restreint les champs mappés aux valeurs de $select.

	   Si $select n'est pas fourni, toutes les clefs présentes dans $map seront présentes dans

	   le SELECT en sortie */

	static function sqlFieldsToAlias($map, $select = NULL, $prefix = NULL) {

		if($select) $arr = array_intersect_key($map, array_flip($select));

		else $arr = $map;

		$keys = array_keys($arr);

		if($prefix) array_walk($keys, create_function('&$val, $k, $prefix', '$val = sprintf("%s.`%s`", $prefix, $val);'), $prefix);

		else array_walk($keys, create_function('&$val, $k', '$val = sprintf("`%s`", $val);'));

		return implode(', ', array_map(create_function('$v, $k', 'return sprintf("%s AS `%s`", $k, $v);'), $arr, $keys));

	}

	/*

	  Retourne une clause where du style:

	  CONCAT(IF(du.prenom IS NULL, "", du.prenom), [...] vdi.i_nomutilisateur) REGEXP 'xxx'

	  Note; i_(nom|prenom_utilisateur), alias pour cel_images.(nom|prenom), n'est pas traité

	  car cette information est redondante dans cel_image et devrait être supprimée.

	*/

	static function addAuteursConstraint($val, $db, &$where) {

		@list($a, $b) = explode(' ', $val, 2);

		// un seul terme

		$champs_n = array('du.prenom', // info user authentifié de l'obs depuis l'annuaire

		'vdi.prenom_utilisateur', // info user anonyme de l'obs

		/* 'vdi.i_prenom_utilisateur' */ ); // info user anonyme de l'image

		$champs_p = array('du.nom', // idem pour le nom

		'vdi.nom_utilisateur',

		/* 'vdi.i_nom_utilisateur' */ );

		/*

		  Note: pour l'heure, étant donnés:

		  - les CONVERT() de la VIEW del_utilisateur

		  - DEFAULT CHARSET=latin1 pour tela_prod_v4.annuaire_tela

		  - DEFAULT CHARSET=utf8 pour tb_cel.cel_obs

		  et l'âge du capitaine...

		  - REGEXP est case-sensitive, et collate les caractères accentués

		  - LIKE est case-insensitive, et collate les caractères accentués

		*/

		if(! $b) {

			$where[] = sprintf('CONCAT(%s,%s) LIKE %s',

			DelTk::sqlAddIfNullPourConcat($champs_n),

			DelTk::sqlAddIfNullPourConcat($champs_p),

			$db->proteger("%".$val."%"));

		}

		else {

			$where[] = sprintf('(CONCAT(%1$s,%2$s) LIKE %3$s AND CONCAT(%1$s,%2$s) LIKE %4$s)',

			DelTk::sqlAddIfNullPourConcat($champs_n),

			DelTk::sqlAddIfNullPourConcat($champs_p),

			$db->proteger("%" . $a . "%"), $db->proteger("%" . $b . "%"));

		}

	}

	/**

	 * - Rempli le tableau des contraintes "where" et "join" nécessaire

	 * à la *recherche* des observations demandées ($req) utilisées par self::getIdObs()

	 *

	 * Attention, cela signifie que toutes les tables ne sont pas *forcément*

	 * join'ées, par exemple si aucune contrainte ne le nécessite.

	 * $req tel qu'il est rempli ici est utile pour récupéré la seule liste des

	 * id d'observation qui match.

	 * Pour la récupération effective de "toutes" les données correspondante, il faut

	 * réinitialiser $req["join"] afin d'y ajouter toutes les autres tables.

	 *

	 * Note: toujours rajouter les préfixes de table (vdi,du,doi ou di), en fonction de ce que défini

	 * les JOIN qui sont utilisés.

	 * le préfix de v_del_image est "vdi" (cf: "FROM" de self::getIdObs())

	 * le préfix de del_utilisateur sur id_utilisateur = vdi.ce_utilisateur est "du"

	 *

	 * @param $p les paramètres (notamment de masque) passés par l'URL et déjà traités/filtrés (sauf quotes)

	 * @param $req le tableau, passé par référence représentant les composants de la requête à bâtir

	 */

	static function sqlAddConstraint($p, $db, &$req) {

		if(!empty($p['masque.auteur'])) {

			// id du poster de l'obs

			$req['join'][] = 'LEFT JOIN del_utilisateur AS du ON du.id_utilisateur = vdi.ce_utilisateur';

			// id du poster de l'image... NON, c'est le même que le posteur de l'obs

			// Cette jointure de table est ignoré ci-dessous pour les recherches d'auteurs

			// $req['join'][] = 'LEFT JOIN del_utilisateur AS dui ON dui.id_utilisateur = vdi.i_ce_utilisateur';

			if(is_numeric($p['masque.auteur'])) {

				$req['where'][] = sprintf('(du.id_utilisateur = %1$d OR vdi.id_utilisateur = %1$d)', $p['masque.auteur']);

			}

			elseif(preg_match(';^.{5,}@[a-z0-9-.]{5,}$;i', $p['masque.auteur'])) {

				$req['where'][] = sprintf('(du.courriel LIKE %1$s OR vdi.courriel LIKE %1$s )',

				$db->proteger($p['masque.auteur'] . '%'));

			}

			else {

				DelTk::addAuteursConstraint($p['masque.auteur'], $db, $req['where']);

			}

		}

		if(!empty($p['masque.date'])) {

			if(is_integer($p['masque.date']) && $p['masque.date'] < 2030 && $p['masque.date'] > 1600) {

				$req['where'][] = sprintf("YEAR(vdi.date_observation) = %d", $p['masque.date']);

			}

			else {

				$req['where'][] = sprintf("DATE_FORMAT(vdi.date_observation, '%%Y-%%m-%%d') = %s",

				$db->proteger(strftime('%Y-%m-%d', $p['masque.date'])));

			}

		}

		// TODO: avoir des champs d'entrée distinct

		if(!empty($p['masque.departement'])) {

			$req['where'][] = sprintf("vdi.ce_zone_geo = %s", $db->proteger('INSEE-C:'.$p['masque.departement']));

		}

		if(!empty($p['masque.id_zone_geo'])) {

			$req['where'][] = sprintf("vdi.ce_zone_geo = %s", $db->proteger($p['masque.id_zone_geo']));

		}

		if(!empty($p['masque.genre'])) {

			$req['where'][] = 'vdi.nom_sel LIKE '.$db->proteger('%' . $p['masque.genre'].'% %');

		}

		if(!empty($p['masque.famille'])) {

			$req['where'][] = 'vdi.famille = '.$db->proteger($p['masque.famille']);

		}

		if(!empty($p['masque.ns'])) {

			$req['where'][] = 'vdi.nom_sel LIKE '.$db->proteger($p['masque.ns'].'%');

		}

		if(!empty($p['masque.nn'])) {

			$req['where'][] = sprintf('vdi.nom_sel_nn = %1$d OR vdi.nom_ret_nn = %1$d', $p['masque.nn']);

		}

		if(!empty($p['masque.referentiel'])) {

			$req['where'][] = sprintf('vdi.nom_referentiel LIKE %s', $db->proteger($p['masque.referentiel'].'%'));

		}

		if(!empty($p['masque.commune'])) {

			$req['where'][] = 'vdi.zone_geo LIKE '.$db->proteger($p['masque.commune'].'%');

		}

		if(!empty($p['masque.tag'])) {

			// TODO: remove LOWER() lorsqu'on est sur que les tags sont uniformés en minuscule

			// i_mots_cles_texte provient de la VIEW v_del_image

			if(isset($p['masque.tag']['AND'])) {

                /* Lorsque nous interprêtons la chaîne provenant du masque général (cf: buildTagsAST($p['masque'], 'OR', ' ') dans sqlAddMasqueConstraint()),

                   nous sommes splittés par espace. Cependant, assurons que si une virgule à été saisie, nous n'aurons pas le motif

                   " AND CONCAT(mots_cles_texte, i_mots_cles_texte) REGEXP ',' " dans notre requête.

                   XXX: Au 12/11/2013, une recherche sur tag depuis le masque général implique un OU, donc le problème ne se pose pas ici */

				$subwhere = array();

				foreach($p['masque.tag']['AND'] as $tag) {

                    if(trim($tag) == ',') continue;

					$subwhere[] = sprintf(

						'LOWER(CONCAT(%s)) REGEXP %s',

						DelTk::sqlAddIfNullPourConcat(array('vdi.mots_cles_texte', 'vdi.i_mots_cles_texte')),

						$db->proteger(strtolower($tag)));

				}

				$req['where'][] = '(' . implode(' AND ', $subwhere) . ')';

			}

			else {

				$req['where'][] = sprintf(

					'LOWER(CONCAT(%s)) REGEXP %s',

					DelTk::sqlAddIfNullPourConcat(array('vdi.mots_cles_texte', 'vdi.i_mots_cles_texte')),

					$db->proteger(strtolower(implode('|', $p['masque.tag']['OR']))));

			}

		}

    }

    // formatage de réponse HTTP

	static function makeJSONHeader($total, $params, $url_service) {

		$prev_url = $next_url = NULL;

		$url_service_sans_slash = substr($url_service, 0, -1);

		// aplatissons les params! - une seule couche cela dit, après débrouillez-vous

		$params_a_plat = $params;

		foreach ($params_a_plat as $cle_plate => $pap) {

			if (is_array($pap)) {

				$params_a_plat[$cle_plate] = implode(array_keys($pap), ',');

			}

		}

		$next_offset = $params['navigation.depart'] + $params['navigation.limite'];

		if($next_offset < $total) {

			$next_url = $url_service_sans_slash . '?' . http_build_query(array_merge($params_a_plat, array('navigation.depart' => $next_offset)));

		}

		$prev_offset = $params['navigation.depart'] - $params['navigation.limite'];

		if($prev_offset >= 0) {

			$prev_url = $url_service_sans_slash . '?' . http_build_query(array_merge($params_a_plat, array('navigation.depart' => $prev_offset)));

		}

		return array(

			'masque' => http_build_query(array_diff_key($params, array_flip(array('navigation.depart', 'navigation.limite')))),

			'total' => $total,

			'depart' => $params['navigation.depart'],

			'limite' => $params['navigation.limite'],

			'href.precedent' => $prev_url,

			'href.suivant' => $next_url

		);

	}

}