Subversion Repositories eFlore/Applications.del

Rev

Rev 1523 | Rev 1584 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
1390 raphael 1
<?php
2
/**
3
 * @author		Raphaël Droz <raphael@tela-botanica.org>
4
 * @copyright	Copyright (c) 2013, Tela Botanica (accueil@tela-botanica.org)
5
 * @license	http://www.cecill.info/licences/Licence_CeCILL_V2-fr.txt Licence CECILL
6
 * @license	http://www.gnu.org/licenses/gpl.html Licence GNU-GPL
7
 * @see http://www.tela-botanica.org/wikini/eflore/wakka.php?wiki=ApiIdentiplante01Images
1495 raphael 8
 * @see http://www.tela-botanica.org/wikini/identiplante/wakka.php?wiki=IdentiPlante_PictoFlora_MoteurRecherche
1422 raphael 9
 *
10
 * Backend pour PictoFlora (del.html#page_recherche_images)
11
 *
12
 *
13
 * == Notes ==
14
 *
15
 * tri=votes et tri=tags: affectent le choix des images affichées (donc getIdImages())
16
 * Cependant ce total ne nous intéresse même pas (MoyenneVotePresenteur.java s'en occupe).
17
 * Seul tri=date_transmission nous évite l'AVG() + GROUP BY
18
 *
19
 * protocole: il affecte l'affichage des information, mais le JSON contient déjà
20
 * l'intégralité (chercher les données de vote pour 1 ou plusieurs protocoles) est quasi-identique.
21
 * Par contre, le tri par moyenne des votes, sous-entend "pour un protocole donné".
22
 * Dès lors le choix d'un protocole doit avoir été fait afin de régler le JOIN et ainsi l'ORDER BY.
23
 * (cf requestFilterParams())
24
 *
25
 * Histoire: auparavant (pré-r142x) un AVG + GROUP BY étaient utilisés pour générer on-the-fly les valeurs
1490 raphael 26
 * utilisées ensuite pour l'ORDER BY. La situation à base de del_image_stat
27
 * est déjà bien meilleure sans être pour autant optimale. cf commentaire de sqlAddConstraint()
1422 raphael 28
 *
29
 *
30
 * Tags:
31
 * Le comportement habituel dans le masque *général*: les mots sont séparés par des espaces,
32
 * implod()ed par des AND (tous les mots doivent matcher).
33
 * Et le test effectué doit matcher sur:
34
 * %(les tags d'observations)% *OU* %(les tags d'images)% *OU* %(les tags publics)%
35
 *
36
 * Le comportement habituel dans le masque *tag*: les mots ne sont *pas* splittés (1 seule expression),
37
 * Et le test effectué doit matcher sur:
38
 * ^(expression)% *OU* %(expression)% [cf getConditionsImages()]
39
 *
40
 * Par défaut les tags sont comma-separated (OU logique).
41
 * Cependant pour conserver le comportement du masque général qui sous-entend un ET logique sur
42
 * des tags séparés par des espaces recherche
43
 *
44
 * TODO:
45
 * -affiner la gestion de passage de mots-clefs dans le masque général.
46
 * - subqueries dans le FROM pour les critère WHERE portant directement sur v_del_image
47
 * plutôt que dans WHERE (qui nécessite dès lors un FULL-JOIN)
48
 * (http://www.mysqlperformanceblog.com/2007/04/06/using-delayed-join-to-optimize-count-and-limit-queries/)
49
 * - éviter de dépendre d'une jointure systématique sur `cel_obs`, uniquement pour `(date_)transmission
50
 * (cf VIEW del_image)
1492 raphael 51
 * - poursuivre la réorganisation des méthodes statiques parmis Observation, ListeObservations et ListeImages2
1422 raphael 52
 * - *peut-être*: passer requestFilterParams() en méthode de classe
53
 *
1438 raphael 54
 *
55
 * MySQL sux:
56
 * EXPLAIN SELECT  id_image FROM v_del_image vdi WHERE vdi.id_image IN (SELECT ce_image FROM del_image_tag WHERE actif = 1 LIMIT 1);
1486 raphael 57
 *	MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery
1438 raphael 58
 * EXPLAIN SELECT * FROM del_image WHERE id_image IN (SELECT 3);
1486 raphael 59
 *	PRIMARY
1438 raphael 60
 * EXPLAIN SELECT * FROM del_image WHERE id_image IN (SELECT MIN(3));
1486 raphael 61
 *	DEPENDENT SUBQUERY ... ... ... mwarf !
1438 raphael 62
 * EXPLAIN SELECT  id_image FROM v_del_image vdi WHERE vdi.id_image IN (SELECT ce_image FROM del_image_tag WHERE actif = 1);
1486 raphael 63
 *	5.5: MATERIALIZED		del_image_tag	ALL				ce_image NULL NULL NULL 38276 Using where
64
 *	5.1: DEPENDENT SUBQUERY	del_image_tag	index_subquery	ce_image ce_image 8 func 1 Using where
1438 raphael 65
 * FORCE INDEX/IGNORE INDEX semble incapable de résoudre le problème de l'optimiseur MySQL
66
 *
1390 raphael 67
 */
68
 
1490 raphael 69
require_once(dirname(__FILE__) . '/../DelTk.php');
1390 raphael 70
require_once(dirname(__FILE__) . '/../observations/Observation.php');
71
restore_error_handler();
72
restore_exception_handler();
73
error_reporting(E_ALL);
74
 
1422 raphael 75
// del/services/0.1/images?navigation.depart=0&navigation.limite=12&tri=votes&ordre=desc
76
// del/services/0.1/images?navigation.depart=0&navigation.limite=12&tri=votes&ordre=desc&masque=plop
77
// del/services/0.1/images?navigation.depart=0&navigation.limite=12&tri=votes&ordre=desc&protocole=3
78
// del/services/0.1/images?navigation.depart=0&navigation.limite=12&tri=votes&ordre=desc&protocole=3&masque=plop
79
 
1514 aurelien 80
class ListeImages {
1390 raphael 81
 
1495 raphael 82
    // TODO: PHP-x.y, ces variables devrait être des "const"
83
    static $format_image_possible = array('O','CRX2S','CRS','CXS','CS','XS','S','M','L','XL','X2L','X3L');
1390 raphael 84
 
1564 mathias 85
    static $tri_possible = array('date_transmission', 'date_observation', 'votes', 'tags', 'points');
1390 raphael 86
 
1495 raphael 87
    // en plus de ceux dans DelTk
88
    static $parametres_autorises = array('protocole', 'masque.tag_cel', 'masque.tag_pictoflora', 'masque.milieu');
1422 raphael 89
 
1495 raphael 90
    static $default_params = array('navigation.depart' => 0, 'navigation.limite' => 10,
91
				   'tri' => 'date_transmission', 'ordre' => 'desc',
92
				   // spécifiques à PictoFlora:
93
				   'format' => 'XL');
1422 raphael 94
 
1564 mathias 95
    static $default_proto = 3; // proto par défaut: capitalisation d'img (utilisé uniquement pour tri=(tags|votes|points))
1422 raphael 96
 
1495 raphael 97
    static $mappings = array(
98
	'observations' => array( // v_del_image
99
	    "id_observation" => 1,
100
	    "date_observation" => 1,
101
	    "date_transmission" => 1,
102
	    "famille" => "determination.famille",
103
	    "nom_sel" => "determination.ns",
104
	    "nom_sel_nn" => "determination.nn",
105
	    "nom_referentiel" => "determination.referentiel",
106
	    "nt" => "determination.nt",
107
	    "ce_zone_geo" => "id_zone_geo",
108
	    "zone_geo" => 1,
109
	    "lieudit" => 1,
110
	    "station" => 1,
111
	    "milieu" => 1,
112
	    "mots_cles_texte" => "mots_cles_texte",
113
	    "commentaire" => 1,
114
	    "ce_utilisateur" => "auteur.id",
115
	    "nom_utilisateur" => "auteur.nom",
116
	    "prenom_utilisateur" => "auteur.prenom",
117
	),
118
	'images' => array( // v_del_image
119
	    'id_image' => 1,
120
	    // l'alias suivant est particulier: in-fine il doit s'appeler mots_cles_texte
121
	    // mais nous afin d'éviter un conflit d'alias nous le renommons plus tard (reformateImagesDoubleIndex)
122
	    'i_mots_cles_texte' => 1
123
	));
1422 raphael 124
 
125
 
1495 raphael 126
    public function __construct(Conteneur $conteneur = null) {
1564 mathias 127
		$this->conteneur = $conteneur == null ? new Conteneur() : $conteneur;
128
		$this->conteneur->chargerConfiguration('config_images.ini');
129
		$this->gestionBdd = $conteneur->getGestionBdd();
130
		$this->bdd = $this->gestionBdd->getBdd();
1495 raphael 131
    }
1390 raphael 132
 
1495 raphael 133
    public function consulter($ressources, $parametres) {
134
	/* Certes nous sélectionnons ici (nom|prenom|courriel)_utilisateur de cel_obs, mais il ne nous intéressent pas
135
	   Par contre, ci-dessous nous prenons i_(nom|prenom|courriel)_utilisateur.
136
	   Notons cependant qu'aucun moyen ne devrait permettre que i_*_utilisateur != *_utilisateur
137
	   Le propriétaire d'une obs et de l'image associée est *toujours* le même. */
138
	array_walk(self::$mappings['observations'], create_function('&$val, $k', 'if($val==1) $val = $k;'));
139
	array_walk(self::$mappings['images'], create_function('&$val, $k', 'if($val==1) $val = $k;'));
140
	// pour les votes, les mappings de "Observation" nous suffisent
141
	array_walk(Observation::$mappings['votes'], create_function('&$val, $k', 'if($val==1) $val = $k;'));
1390 raphael 142
 
1495 raphael 143
	// la nécessité du 'groupby' dépend des 'join's utilisés (LEFT ou INNER) ainsi que de la cardinalité
144
	// de `ce_image` dans ces tables jointes.
145
	// Contrairement à IdentiPlantes, nous n'avons de HAVING pour PictoFlora, mais par contre un ORDER BY
146
	$req = array('select' => array(), 'join' => array(), 'where' => array(), 'groupby' => array(), 'orderby' => array());
1422 raphael 147
 
148
 
1495 raphael 149
	$db = $this->bdd;
1390 raphael 150
 
1495 raphael 151
	// filtrage de l'INPUT général, on réutilise 90% de identiplante en terme de paramètres autorisés
152
	// ($parametres_autorises) sauf... masque.type qui fait des modif' de WHERE sur les mots-clefs.
153
	// Évitons ce genre de chose pour PictoFlora et les risques de conflits avec masque.tag
154
	// même si ceux-ci sont improbables (pas d'<input> pour cela).
155
	$params_ip = DelTk::requestFilterParams($parametres,
156
						array_diff(DelTk::$parametres_autorises,
157
							   array('masque.type')),
158
						$this->conteneur);
1422 raphael 159
 
1495 raphael 160
	// notre propre filtrage sur l'INPUT
161
	$params_pf = self::requestFilterParams($parametres,
162
					       array_merge(DelTk::$parametres_autorises,
163
							   self::$parametres_autorises));
1390 raphael 164
 
1495 raphael 165
	/* filtrage des tags + sémantique des valeurs multiples:
166
	   Lorsqu'on utilise masque.tag* pour chercher des tags, ils sont
167
	   postulés comme séparés par des virgule, et l'un au moins des tags doit matcher. */
168
	$params_pf['masque.tag_cel'] = DelTk::buildTagsAST(@$parametres['masque.tag_cel'], 'OR', ',');
1514 aurelien 169
 
170
 
171
	if(!isset($parametres['masque.tag_pictoflora']) && isset($parametres['masque.tag'])) {
172
		$parametres['masque.tag_pictoflora'] = $parametres['masque.tag'];
173
	}
1495 raphael 174
	$params_pf['masque.tag_pictoflora'] = DelTk::buildTagsAST(@$parametres['masque.tag_pictoflora'], 'OR', ',');
1390 raphael 175
 
1495 raphael 176
	$params = array_merge(
1490 raphael 177
            DelTk::$default_params, // paramètre par défaut Identiplante
178
            self::$default_params, // paramètres par défaut PictoFlora
179
            $params_ip, // les paramètres passés, traités par Identiplante
180
            $params_pf); // les paramètres passés, traités par PictoFlora
1390 raphael 181
 
1514 aurelien 182
	if(isset($parametres['format'])) {
183
		$params['format'] = $parametres['format'];
184
	}
1422 raphael 185
 
1495 raphael 186
	// création des contraintes (génériques de DelTk)
187
	DelTk::sqlAddConstraint($params, $db, $req);
188
	// création des contraintes spécifiques (sur les tags essentiellement)
189
	self::sqlAddConstraint($params, $db, $req, $this->conteneur);
190
	// création des contraintes spécifiques impliquées par le masque général
191
	self::sqlAddMasqueConstraint($params, $db, $req, $this->conteneur);
192
	// l'ORDER BY s'avére complexe
193
	self::sqlOrderBy($params, $db, $req);
1390 raphael 194
 
1495 raphael 195
	// 1) grunt-work: *la* requête de récupération des id valides (+ SQL_CALC_FOUND_ROWS)
196
	// $idobs_tab = ListeObservations::getIdObs($params, $req, $db);
197
	$idobs_tab = self::getIdImages($params, $req, $db);
1390 raphael 198
 
1495 raphael 199
	// Ce n'est pas la peine de continuer s'il n'y a pas eu de résultats dans la table del_obs_images
200
	if(!$idobs_tab) {
201
	    $resultat = new ResultatService();
202
	    $resultat->corps = array('entete' => DelTk::makeJSONHeader(0, $params, Config::get('url_service')),
203
				     'resultats' => array());
204
	    return $resultat;
205
	    /*
1486 raphael 206
              header('HTTP/1.0 404 Not Found');
207
              // don't die (phpunit)
208
              throw(new Exception()); */
1495 raphael 209
	}
1390 raphael 210
 
211
 
1495 raphael 212
	// idobs est une liste (toujours ordonnée) des id d'observations recherchées
213
	$idobs = array_values(array_map(create_function('$a', 'return $a["id_image"];'), $idobs_tab));
214
	$total = $db->recuperer('SELECT FOUND_ROWS() AS c'); $total = intval($total['c']);
1390 raphael 215
 
1495 raphael 216
	$liaisons = self::chargerImages($db, $idobs);
1564 mathias 217
 
218
	// debug: infos de score
219
	/*$infosScore = array();
220
	foreach ($idobs_tab as $iot) {
221
		$infosScore[$iot['id_image']] = array(
222
			'nb_votes' => $iot['nb_votes'],
223
			'nb_points' => $iot['nb_points'],
224
			'moyenne' => $iot['moyenne']
225
		);
226
	}*/
1495 raphael 227
	/*
1486 raphael 228
        // Q&D
229
        $images = array();
230
        $o = new Observation($this->conteneur);
231
        foreach($idobs as $i) {
232
        $images[$i] = $o->consulter(array($i), array('justthrow' => 1));
233
        }
1495 raphael 234
	*/
235
	list($images, $images_keyed_by_id_image) = self::reformateImagesDoubleIndex(
236
	    $liaisons,
1497 raphael 237
	    $this->conteneur->getParametre('images.url_images'),
1495 raphael 238
	    $params['format']);
1422 raphael 239
 
1495 raphael 240
	// on charge les votes pour ces images et pour *tous* les protocoles
241
	$votes = Observation::chargerVotesImage($db, $liaisons, NULL);
1422 raphael 242
 
1495 raphael 243
	// subtilité, nous passons ici le tableau d'images indexé par id_image qui est bien plus pratique pour
244
	// associer les vote à un tableau, puisque nous ne connaissons pas les id d'observation.
245
	// Mais magiquement (par référence), cela va remplir notre tableau indexé par couple d'id (id_image, id_observation)
246
	// cf reformateImagesDoubleIndex() à qui revient la tâche de créer ces deux versions simultanément lorsque
247
	// c'est encore possible.
248
	if($votes) Observation::mapVotesToImages($votes, $images_keyed_by_id_image);
1422 raphael 249
 
1495 raphael 250
	// les deux masques de tags sont transformés en AST dans le processus de construction de la requête.
251
	// Reprenous les paramètres originaux non-nettoyés (ils sont valables car le nettoyage est déterministe)
252
	$params_header = array_merge($params, array_filter(array('masque.tag_cel' => @$parametres['masque.tag_cel'],
253
								 'masque.tag_pictoflora' => @$parametres['masque.tag_pictoflora'])));
254
	$resultat = new ResultatService();
255
	$resultat->corps = array('entete' => DelTk::makeJSONHeader($total, $params_header, Config::get('url_service')),
256
				 'resultats' => $images);
257
	return $resultat;
258
    }
259
 
260
    /**
261
     * TODO: partie spécifique liées à la complexité de PictoFlora:
262
     * génération de la clause ORDER BY (génère la valeur de la clef orderby' de $req)
263
     * nécessaire ? tableau sprintf(key (tri) => value (ordre), key => value ...).
264
     * Cependant il est impensable de joindre sur un AVG() des valeurs des votes pour
265
     * *chaque* couple (id_image, protocole) de la base afin de trouver les images
266
     * les "mieux notées", ou bien les images ayant le "plus de tags" (COUNT())
267
     */
268
    static function sqlOrderBy($p, $db, &$req) {
1564 mathias 269
		// parmi self::$tri_possible
270
		if($p['tri'] == 'votes') { // LEFT JOIN sur "dis" ci-dessous
271
		    $req['orderby'] = 'dis.moyenne ' . $p['ordre'] . ', dis.nb_votes ' . $p['ordre'];
272
		    return;
273
		}
274
 
275
		if($p['tri'] == 'points') { // LEFT JOIN sur "dis" ci-dessous
276
		    $req['orderby'] = 'dis.nb_points ' . $p['ordre'] . ', dis.moyenne ' . $p['ordre'] . ', dis.nb_votes ' . $p['ordre'];
277
		    return;
278
		}
279
 
280
		if($p['tri'] == 'tags') { // LEFT JOIN sur "dis" ci-dessous
281
		    $req['orderby'] = 'dis.nb_tags ' . $p['ordre'];
282
		    return;
283
		}
284
 
285
		if($p['tri'] == 'date_observation') {
286
		    $req['orderby'] = 'date_observation ' . $p['ordre'] . ', id_observation ' . $p['ordre'];
287
		    return;
288
		}
289
 
290
		// tri == 'date_transmission'
291
		// avant cel:r1860, date_transmission pouvait être NULL
292
		// or nous voulons de la cohérence (notamment pour phpunit)
293
		$req['orderby'] = 'date_transmission ' . $p['ordre'] . ', id_observation ' . $p['ordre'];
1495 raphael 294
    }
295
 
296
    /*
297
     * in $p: un tableau de paramètres, dont:
298
     * - 'masque.tag_cel': *tableau* de mots-clefs à chercher parmi cel_image.mots_clefs_texte
299
     * - 'masque.tag_pictoflora': *tableau* de mots-clefs à chercher parmi del_image_tag.tag_normalise
300
     * - 'tag_explode_semantic': défini si les éléments sont tous recherchés ou NON
301
     *
302
     * in/ou: $req: un tableau de structure de requête MySQL
303
     *
304
     * Attention, le fait que nous cherchions masque.tag_cel OU/ET masque.tag_cel
305
     * ne dépend pas de nous, mais du niveau supérieur de construction de la requête:
306
     * Soit directement $this->consulter() si des masque.tag* sont passés
307
     * (split sur ",", "AND" entre chaque condition, "OR" pour chaque valeur de tag)
308
     * Soit via sqlAddMasqueConstraint():
309
     * (pas de split, "OR" entre chaque condition) [ comportement historique ]
310
     * équivalent à:
311
     * (split sur " ", "OR" entre chaque condition, "AND" pour chaque valeur de tag)
312
     *
313
     */
314
    static function sqlAddConstraint($p, $db, &$req, Conteneur $c = NULL) {
315
	// TODO implement dans DelTk ?
316
	if(!empty($p['masque.milieu'])) {
317
	    $req['where'][] = 'vdi.milieu LIKE '.$db->proteger('%' . $p['masque.milieu'].'%');
1390 raphael 318
	}
319
 
1422 raphael 320
 
1495 raphael 321
	/* Pour le tri par AVG() des votes nous avons toujours un protocole donné,
322
	   celui-ci indique sur quels votes porte l'AVG.
323
	   (c'est un *vote* qui porte sur un protocole et non l'image elle-même) */
324
	/* TODO: perf problème:
325
	   1) SQL_CALC_FOUND_ROWS: fixable en:
1486 raphael 326
           - dissociant le comptage de la récup d'id + javascript async
327
           - ou ne rafraîchir le total *que* pour les requête impliquant un changement de pagination
328
           (paramètre booléen "with-total" par exemple)
1495 raphael 329
	   2) jointure forcées: en utilisant `del_imagè`, nous forçons les 2 premiers
1486 raphael 330
           JOIN sur cel_obs_images et cel_obs pour filtrer sur "transmission".
331
           Dénormaliser cette valeur et l'intégrer à `cel_images` ferait économiser cette couteuse
332
           jointure, ... lorsqu'aucun masque portant sur `cel_obs` n'est utilisé
1495 raphael 333
	   3) non-problème: l'ordre des joins est forcé par l'usage de la vue:
1486 raphael 334
           (cel_images/cel_obs_images/cel_obs/del_image_stat)
335
           Cependant c'est à l'optimiseur de définir son ordre préféré. */
1564 mathias 336
	if($p['tri'] == 'votes' || $p['tri'] == 'points') {
1495 raphael 337
	    // $p['protocole'] *est* défini (cf requestFilterParams())
338
	    // petite optimisation: INNER JOIN si ordre DESC car les 0 à la fin
339
	    if($p['ordre'] == 'desc') {
340
		// pas de group by nécessaire pour cette jointure
341
		// PRIMARY KEY (`ce_image`, `ce_protocole`)
342
		$req['join'][] = sprintf('INNER JOIN del_image_stat dis'.
343
					 ' ON vdi.id_image = dis.ce_image'.
344
					 ' AND dis.ce_protocole = %d',
345
					 $p['protocole']);
346
	    } else {
347
		$req['join'][] = sprintf('LEFT JOIN del_image_stat dis'.
348
					 ' ON vdi.id_image = dis.ce_image'.
349
					 ' AND dis.ce_protocole = %d',
350
					 $p['protocole']);
351
		// nécessaire (dup ce_image dans del_image_stat)
352
		$req['groupby'][] = 'vdi.id_observation';
353
	    }
354
	}
1422 raphael 355
 
1495 raphael 356
	if($p['tri'] == 'tags') {
357
	    $req['join'][] = sprintf('%s JOIN del_image_stat dis ON vdi.id_image = dis.ce_image',
358
				     ($p['ordre'] == 'desc') ? 'INNER' : 'LEFT');
359
	    // nécessaire (dup ce_image dans del_image_stat)
360
	    $req['groupby'][] = 'vdi.id_observation';
361
	}
1422 raphael 362
 
1495 raphael 363
	// car il ne sont pas traités par la générique requestFilterParams() les clefs "masque.tag_*"
364
	// sont toujours présentes; bien que parfois NULL.
365
	if($p['masque.tag_cel']) {
366
	    if(isset($p['masque.tag_cel']['AND'])) {
367
		// TODO: utiliser les tables de mots clefs normaliées dans tb_cel ?
368
		// et auquel cas laisser au client le choix du couteux "%" ?
369
		$tags = $p['masque.tag_cel']['AND'];
370
		array_walk($tags, create_function('&$val, $k, $db',
371
						  '$val = sprintf("CONCAT(vdi.mots_cles_texte,vdi.i_mots_cles_texte) LIKE %s",
1486 raphael 372
																  $db->proteger("%".$val."%"));'),
1495 raphael 373
			   $db);
374
		$req['where'][] = '(' . implode(' AND ', $tags) . ')';
375
	    }
376
	    else {
377
		$req['where'][] = sprintf("CONCAT(vdi.mots_cles_texte,vdi.i_mots_cles_texte) REGEXP %s",
378
					  $db->proteger(implode('|', $p['masque.tag_cel']['OR'])));
379
	    }
380
	}
1422 raphael 381
 
1495 raphael 382
	if($p['masque.tag_pictoflora']) {
383
	    // inutilisable pour l'instant
384
	    // self::sqlAddPictoFloraTagConstraint1($p, $db, $req);
1422 raphael 385
 
1495 raphael 386
	    // intéressante, mais problème d'optimiseur MySQL 5.5 (dependant subquery)
387
	    // self::sqlAddPictoFloraTagConstraint2($p, $db, $req);
1422 raphael 388
 
1495 raphael 389
	    // approche fiable mais sous-optimale
390
	    self::sqlAddPictoFloraTagConstraint3($p, $db, $req);
391
	}
392
    }
1486 raphael 393
 
1495 raphael 394
    /* approche intéressante si les deux problèmes suivants peuvent être résolu:
395
       - LEFT JOIN => dup => *gestion de multiples GROUP BY* (car in-fine un LIMIT est utilisé)
396
       - dans le cas d'un ET logique, comment chercher les observations correspondantes ? */
397
    static function sqlAddPictoFloraTagConstraint1($p, $db, &$req) {
398
	// XXX: utiliser tag plutôt que tag_normalise ?
399
	$req['join'][] = 'LEFT JOIN del_image_tag dit ON dit.ce_image = vdi.id_image';
400
	$req['where'][] = 'dit.actif = 1';
401
	$req['groupby'][] = 'vdi.id_image'; // TODO: nécessaire (car dup') mais risque de conflict en cas de tri (multiple GROUP BY)
402
	// XXX: en cas de ET, possibilité du GROUP_CONCAT(), mais probablement sans grand intérêt, d'où une boucle
403
	if(isset($p['masque.tag_pictoflora']['AND'])) {
404
	    // TODO/XXX : comment matcher les observations ayant tous les mots-clef passés ?
405
	    // ... le LEFT-JOIN n'y semble pas adapté
406
	}
407
	else {
408
	    $protected_tags = array();
409
	    foreach($p['masque.tag_pictoflora']['OR'] as $tag) $protected_tags[] = $db->proteger(strtolower($tag));
410
	    $req['where'][] = sprintf('tag_normalise IN (%s)', implode(',', $protected_tags));
411
	}
412
    }
1486 raphael 413
 
1495 raphael 414
    // inutilisé pour l'instant pour cause de soucis d'optimiseur MySQL (cf commentaire en intro)
415
    static function sqlAddPictoFloraTagConstraint2($p, $db, &$req) {
416
	// Note à propos des 4 "@ instruction" ci-dessous (notamment sur recupererTous())
417
	// REGEXP permet un puissant mécanisme de sélection des obs/image à qui sait
418
	// l'utiliser, mais peut sortir une erreur en cas de REGEXP invalide
419
	// ex: REGEX "^(".
420
	// Pour l'heure nous ignorons ce type d'erreur car aucun de nos champ de recherche
421
	// ne peuvent (ou ne devrait) comporter des meta-caractères
422
	// ([])?*+\\
423
	if(isset($p['masque.tag_pictoflora']['AND'])) {
424
	    // optimsation: en cas de "AND" on sort() l'input et le GROUP_CONCAT()
425
	    // donc nous utilisons des ".*" plutôt que de multiples conditions et "|"
426
	    sort($p['masque.tag_pictoflora']['AND']);
427
	    $req['where'][] = sprintf("vdi.id_image IN (SELECT ce_image FROM del_image_tag WHERE actif = 1".
428
				      " GROUP BY ce_image".
429
				      " HAVING GROUP_CONCAT(tag_normalise ORDER BY tag_normalise) REGEXP %s)",
430
				      $db->proteger(implode('.*', $p['masque.tag_pictoflora']['AND'])));
431
	}
432
	else {
433
	    $req['where'][] = sprintf("vdi.id_image IN (SELECT ce_image FROM del_image_tag WHERE actif = 1".
434
				      " GROUP BY ce_image".
435
				      " HAVING GROUP_CONCAT(tag_normalise) REGEXP %s)",
436
				      $db->proteger(implode('|', $p['masque.tag_pictoflora']['OR'])));
437
	}
438
    }
1491 raphael 439
 
1495 raphael 440
    // si l'on est bassiné par les "DEPENDENT SUBQUERY", nous la faisons donc indépendemment via cette fonction
441
    static function sqlAddPictoFloraTagConstraint3($p, $db, &$req) {
442
	if(isset($p['masque.tag_pictoflora']['AND'])) {
443
	    // optimsation: en cas de "AND" on sort() l'input et le GROUP_CONCAT()
444
	    // donc nous utilisons des ".*" plutôt que de multiples conditions et "|"
445
	    sort($p['masque.tag_pictoflora']['AND']);
1486 raphael 446
 
1495 raphael 447
	    // plutôt que db->connexion->query->fetchColumn(), une API pourrie nous oblige à ...
448
	    $ids = @$db->recupererTous(sprintf(
449
		"SELECT ce_image FROM del_image_tag WHERE actif = 1".
450
		" GROUP BY ce_image".
451
		" HAVING GROUP_CONCAT(tag_normalise ORDER BY tag_normalise) REGEXP %s",
452
		$db->proteger(implode('.*', $p['masque.tag_pictoflora']['AND']))));
1486 raphael 453
 
1495 raphael 454
	    // puis:
455
	    $ids = @array_map(create_function('$e', 'return $e["ce_image"];'), $ids);
1514 aurelien 456
	    $ids = !empty($ids) ? implode(',', $ids) : 'SELECT ce_image FROM del_image_tag  WHERE false';
457
	    $req['where'][] = sprintf("vdi.id_image IN (%s)", $ids);
1491 raphael 458
 
1495 raphael 459
	}
460
	else {
461
	    $ids = @$db->recupererTous(sprintf(
462
		"SELECT ce_image FROM del_image_tag WHERE actif = 1".
463
		" GROUP BY ce_image".
464
		" HAVING GROUP_CONCAT(tag_normalise) REGEXP %s",
465
		$db->proteger(implode('|', $p['masque.tag_pictoflora']['OR']))));
1486 raphael 466
 
1495 raphael 467
	    $ids = @array_map(create_function('$e', 'return $e["ce_image"];'), $ids);
1514 aurelien 468
	    $ids = !empty($ids) ? implode(',', $ids) : 'SELECT ce_image FROM del_image_tag  WHERE false';
469
	    $req['where'][] = sprintf("vdi.id_image IN (%s)", $ids);
1422 raphael 470
	}
1495 raphael 471
    }
1422 raphael 472
 
1495 raphael 473
    static function getIdImages($p, $req, $db) {
474
	return $db->recupererTous(sprintf(
475
	    'SELECT SQL_CALC_FOUND_ROWS id_image' .
1564 mathias 476
		//', dis.moyenne, dis.nb_points, dis.nb_votes' . // debug
1495 raphael 477
	    ' FROM v_del_image vdi'.
478
	    ' %s' . // LEFT JOIN if any
479
	    ' WHERE %s'. // where-clause ou TRUE
480
	    ' %s'. // group-by
481
	    ' ORDER BY %s'.
482
	    ' LIMIT %d, %d -- %s',
1422 raphael 483
 
1495 raphael 484
	    $req['join'] ? implode(' ', array_unique($req['join'])) : '',
485
	    $req['where'] ? implode(' AND ', $req['where']) : 'TRUE',
1422 raphael 486
 
1495 raphael 487
	    $req['groupby'] ? ('GROUP BY ' . implode(', ', array_unique($req['groupby']))) : '',
1422 raphael 488
 
1495 raphael 489
	    $req['orderby'],
1422 raphael 490
 
1495 raphael 491
	    $p['navigation.depart'], $p['navigation.limite'], __FILE__ . ':' . __LINE__));
492
    }
1422 raphael 493
 
1495 raphael 494
    static function chargerImages($db, $idImg) {
1564 mathias 495
		$obs_fields = DelTk::sqlFieldsToAlias(self::$mappings['observations'], NULL);
496
		$image_fields = DelTk::sqlFieldsToAlias(self::$mappings['images'], NULL);
497
 
498
		return $db->recupererTous(sprintf('SELECT '.
1495 raphael 499
					  ' CONCAT(id_image, "-", id_observation) AS jsonindex,'.
500
					  ' %1$s, %2$s FROM v_del_image '.
501
					  ' WHERE %3$s'.
1564 mathias 502
					  ' ORDER BY %4$s'. // important car MySQL ne conserve par l'ordre du IN()
503
					  ' -- %5$s',
1495 raphael 504
					  $obs_fields, $image_fields,
505
					  sprintf('id_image IN (%s)', implode(',', $idImg)),
1564 mathias 506
					  sprintf('FIELD(id_image, %s)', implode(',', $idImg)),
1495 raphael 507
					  __FILE__ . ':' . __LINE__));
508
    }
1422 raphael 509
 
1495 raphael 510
    /* "masque" ne fait jamais que faire une requête sur la plupart des champs, (presque) tous traités
511
       de manière identique à la seule différence que:
512
       1) ils sont combinés par des "OU" logiques plutôt que des "ET".
513
       2) les tags sont traités différemment pour conserver la compatibilité avec l'utilisation historique:
1486 raphael 514
       Tous les mots-clefs doivent matcher et sont séparés par des espaces
515
       (dit autrement, str_replace(" ", ".*", $mots-clefs-requête) =~ $mots-clefs-mysql)
1495 raphael 516
       Pour plus d'information: (ListeObservations|DelTk)::sqlAddMasqueConstraint() */
517
    static function sqlAddMasqueConstraint($p, $db, &$req, Conteneur $c = NULL) {
518
	if(!empty($p['masque'])) {
519
	    $or_params = array('masque.auteur' => $p['masque'],
520
			       'masque.departement' => $p['masque'],
521
			       'masque.commune' => $p['masque'], // TODO/XXX ?
522
			       'masque.id_zone_geo' => $p['masque'],
1422 raphael 523
 
1495 raphael 524
			       /* tous-deux remplacent masque.tag
525
				  mais sont traité séparément des requestFilterParams() */
526
			       // 'masque.tag_cel' => $p['masque'],
527
			       // 'masque.tag_pictoflora' => $p['masque'],
1422 raphael 528
 
1495 raphael 529
			       'masque.ns' => $p['masque'],
530
			       'masque.famille' => $p['masque'],
531
			       'masque.date' => $p['masque'],
532
			       'masque.genre' => $p['masque'],
533
			       'masque.milieu' => $p['masque'],
1523 aurelien 534
	    		   'masque.tag_cel' => $p['masque'],
1564 mathias 535
	    	     'masque.tag_pictoflora' => $p['masque'],
1422 raphael 536
 
1495 raphael 537
			       // tri est aussi nécessaire car affecte les contraintes de JOIN
538
			       'tri' => $p['tri'],
539
			       'ordre' => $p['ordre']);
1422 raphael 540
 
1498 raphael 541
	    /* Cependant les champs spécifiques ont priorité sur le masque général.
542
	       Pour cette raison nous supprimons la génération de SQL du masque général sur les
543
	       champ spécifiques qui feront l'objet d'un traitement avec une valeur propre. */
544
	    if(isset($p['masque.auteur'])) unset($or_params['masque.auteur']);
545
	    if(isset($p['masque.departement'])) unset($or_params['masque.departement']);
546
	    if(isset($p['masque.commune'])) unset($or_params['masque.commune']);
547
	    if(isset($p['masque.id_zone_geo'])) unset($or_params['masque.id_zone_geo']);
548
	    if(isset($p['masque.ns'])) unset($or_params['masque.ns']);
549
	    if(isset($p['masque.famille'])) unset($or_params['masque.famille']);
550
	    if(isset($p['masque.date'])) unset($or_params['masque.date']);
551
	    if(isset($p['masque.genre'])) unset($or_params['masque.genre']);
552
	    if(isset($p['masque.milieu'])) unset($or_params['masque.milieu']);
553
	    if(isset($p['masque.tag_cel'])) unset($or_params['masque.tag_cel']);
554
	    if(isset($p['masque.tag_pictoflora'])) unset($or_params['masque.tag_pictoflora']);
555
 
1495 raphael 556
	    $or_masque = array_merge(
557
		DelTk::requestFilterParams($or_params, NULL, $c /* pour masque.departement */),
558
		self::requestFilterParams($or_params));
1422 raphael 559
 
1495 raphael 560
	    /* Lorsqu'on utilise le masque général pour chercher des tags, ils sont
561
	       postulés comme séparés par des espaces, et doivent être tous matchés. */
1498 raphael 562
	    if(isset($or_params['masque.tag_cel']))
563
		$or_masque['masque.tag_cel'] = DelTk::buildTagsAST($p['masque'], 'AND', ' ');
564
	    if(isset($or_params['masque.tag_pictoflora']))
565
		$or_masque['masque.tag_pictoflora'] = DelTk::buildTagsAST($p['masque'], 'AND', ' ');
1430 raphael 566
 
567
 
1495 raphael 568
	    // pas de select, groupby & co ici: uniquement 'join' et 'where'
569
	    $or_req = array('join' => array(), 'where' => array());
570
	    DelTk::sqlAddConstraint($or_masque, $db, $or_req);
571
	    self::sqlAddConstraint($or_masque, $db, $or_req);
1422 raphael 572
 
1495 raphael 573
	    if($or_req['where']) {
574
		$req['where'][] = '(' . implode(' OR ', $or_req['where']) . ')';
575
		// utile au cas ou des jointures seraient rajoutées
576
		$req['join'] = array_unique(array_merge($req['join'], $or_req['join']));
577
	    }
1422 raphael 578
	}
1495 raphael 579
    }
1422 raphael 580
 
1490 raphael 581
 
1495 raphael 582
    // cf Observation::reformateObservationSimpleIndex() et ListeObservations::reformateObservation()
1490 raphael 583
    // (trop de variétés de formatage, à unifier côté client pour unifier côté backend ...)
1495 raphael 584
    static function reformateImagesDoubleIndex($obs, $url_pattern = '', $image_format = 'XL') {
1564 mathias 585
		// XXX: cf Observation.php::consulter(), nous pourriouns ici
586
		// conserver les valeurs vides (pour les phptests notamment, ou non)
587
		// $obs = array_map('array_filter', $obs);
588
		$obs_merged = $obs_keyed_by_id_image = array();
589
		foreach($obs as $o) {
590
		    // ceci nous complique la tâche pour le reste du processing...
591
		    $id = $o['jsonindex'];
592
		    // ainsi nous utilisons deux tableaux: le final, indexé par couple d'id(image-obs)
593
		    // et celui indexé par simple id_image qui est fort utile pour mapVotesToImages()
594
		    // mais tout deux partage leur référence à "protocole"
595
		    $image = array(
596
				'id_image' => $o['id_image'],
597
				'binaire.href' => sprintf($url_pattern, $o['id_image'], $image_format),
598
				'mots_cles_texte' => @$o['i_mots_cles_texte'], // @, peut avoir été filtré par array_map() ci-dessus
599
		    );
600
 
601
		    unset($o['id_image'], $o['i_mots_cles_texte'], $o['jsonindex']);
602
		    if(!isset($obs_merged[$id])) $obs_merged[$id] = $image;
603
		    $obs_merged[$id]['observation'] = $o;
604
		    $obs_merged[$id]['protocoles_votes'] = array();
605
 
606
		    $obs_keyed_by_id_image[$image['id_image']]['protocoles_votes'] = &$obs_merged[$id]['protocoles_votes'];
607
		}
608
 
609
		return array($obs_merged,$obs_keyed_by_id_image);
1495 raphael 610
    }
1490 raphael 611
 
612
 
1422 raphael 613
 
1495 raphael 614
    // complete & override DelTk::requestFilterParams() (même usage)
615
    static function requestFilterParams(Array $params, $parametres_autorises = NULL) {
616
	if($parametres_autorises) { // filtrage de toute clef inconnue
617
	    $params = array_intersect_key($params, array_flip($parametres_autorises));
618
	}
1422 raphael 619
 
1495 raphael 620
	$p = array();
621
	$p['tri'] = DelTk::unsetIfInvalid($params, 'tri', self::$tri_possible);
622
	$p['format'] = DelTk::unsetIfInvalid($params, 'format', self::$format_image_possible);
1422 raphael 623
 
1495 raphael 624
	// "milieu" inutile pour IdentiPlantes ?
625
	if(isset($params['masque.milieu'])) $p['masque.milieu'] = trim($params['masque.milieu']);
1422 raphael 626
 
1495 raphael 627
	// compatibilité
628
	if(isset($params['masque.tag'])) {
629
	    $params['masque.tag_cel'] = $params['masque.tag_pictoflora'] = $params['masque.tag'];
630
	}
1422 raphael 631
 
1564 mathias 632
	if($p['tri'] == 'votes' || $p['tri'] == 'tags' || $p['tri'] == 'points') {
1495 raphael 633
	    // ces critère de tri des image à privilégier ne s'applique qu'à un protocole donné
634
	    if(!isset($params['protocole']) || !is_numeric($params['protocole']))
635
		$p['protocole'] = self::$default_proto;
636
	    else
637
		$p['protocole'] = intval($params['protocole']);
1390 raphael 638
	}
1422 raphael 639
 
1495 raphael 640
	return array_filter($p, create_function('$a','return !in_array($a, array("",false,null),true);'));
641
    }
1422 raphael 642
 
643
 
1495 raphael 644
 
645
    // met à jour *toutes* les stats de nombre de tags et de moyenne des votes
646
    static function _update_statistics($db) {
647
	$db->requeter("TRUNCATE TABLE del_image_stat");
648
	$db->requeter(<<<EOF
1422 raphael 649
INSERT INTO `del_image_stat` (
1430 raphael 650
	SELECT id_image, divo.ce_protocole, divo.moyenne, divo.nb_votes, dit.ctags
651
	FROM `tb_cel`.`cel_images` ci
652
	LEFT JOIN
653
	( SELECT ce_image, ce_protocole, AVG(valeur) AS moyenne, COUNT(valeur) AS nb_votes FROM del_image_vote
654
	  GROUP BY ce_image, ce_protocole ) AS divo
655
	ON ci.id_image = divo.ce_image
656
	LEFT JOIN
657
	( SELECT ce_image, COUNT(id_tag) as ctags FROM del_image_tag
658
	  GROUP BY ce_image ) AS dit
659
	ON ci.id_image = dit.ce_image )
1422 raphael 660
EOF
1495 raphael 661
	);
662
    }
1430 raphael 663
 
1495 raphael 664
    static function revOrderBy($orderby) {
1564 mathias 665
    	// @TODO plutôt 'desc' ? '' : 'desc', non ?
666
		  return $orderby == 'asc' ? 'desc' : 'asc';
1495 raphael 667
    }
1486 raphael 668
}