| Line 68... |
Line 68... |
| 68 |
}
|
68 |
}
|
| Line 69... |
Line 69... |
| 69 |
|
69 |
|
| 70 |
private function construireRequeteListeUtilisateurNbrePhoto() {
|
70 |
private function construireRequeteListeUtilisateurNbrePhoto() {
|
| 71 |
$select = 'SELECT co.courriel_utilisateur, COUNT(DISTINCT ci.id_image) AS nbre ';
|
71 |
$select = 'SELECT co.courriel_utilisateur, COUNT(DISTINCT ci.id_image) AS nbre ';
|
| 72 |
$from = 'FROM cel_obs AS co '.
|
72 |
$from = 'FROM cel_obs AS co '.
|
| 73 |
' LEFT JOIN cel_obs_images AS coi ON (coi.id_observation = co.id_observation) '.
|
- |
|
| 74 |
' LEFT JOIN cel_images AS ci ON (coi.id_image = ci.id_image) ';
|
73 |
' LEFT JOIN cel_images AS ci ON (co.id_observation = ci.ce_observation) ';
|
| 75 |
$where = 'WHERE transmission = 1 ';
|
74 |
$where = 'WHERE co.transmission = 1 ';
|
| 76 |
$groupBy = 'GROUP BY co.courriel_utilisateur ';
|
75 |
$groupBy = 'GROUP BY co.courriel_utilisateur ';
|
| 77 |
$orderBy = 'ORDER BY nbre DESC ';
|
76 |
$orderBy = 'ORDER BY nbre DESC ';
|
| Line 78... |
Line 77... |
| 78 |
$limitSql = 'LIMIT 0,150 ';
|
77 |
$limitSql = 'LIMIT 0,150 ';
|
| Line 123... |
Line 122... |
| 123 |
}
|
122 |
}
|
| Line 124... |
Line 123... |
| 124 |
|
123 |
|
| 125 |
private function construireRequeteListeTaxonNbrePhoto() {
|
124 |
private function construireRequeteListeTaxonNbrePhoto() {
|
| 126 |
$select = 'SELECT nom_ret, COUNT(DISTINCT ci.id_image) AS nbre ';
|
125 |
$select = 'SELECT nom_ret, COUNT(DISTINCT ci.id_image) AS nbre ';
|
| 127 |
$from = 'FROM cel_obs co '.
|
126 |
$from = 'FROM cel_obs co '.
|
| 128 |
' LEFT JOIN cel_obs_images coi ON (coi.id_observation = co.id_observation) '.
|
- |
|
| 129 |
' LEFT JOIN cel_images ci ON (coi.id_image = ci.id_image) ';
|
127 |
' LEFT JOIN cel_images ci ON (co.id_observation = ci.ce_observation) ';
|
| 130 |
$where = 'WHERE transmission = 1 '.
|
128 |
$where = 'WHERE co.transmission = 1 '.
|
| 131 |
" AND nom_ret != '' ";
|
129 |
" AND nom_ret != '' ";
|
| 132 |
$groupBy = 'GROUP BY nom_ret ';
|
130 |
$groupBy = 'GROUP BY nom_ret ';
|
| 133 |
$orderBy = 'ORDER BY nbre DESC ';
|
131 |
$orderBy = 'ORDER BY nbre DESC ';
|
| Line 308... |
Line 306... |
| 308 |
}
|
306 |
}
|
| 309 |
if (isset($taxon)) {
|
307 |
if (isset($taxon)) {
|
| 310 |
$filtres[] = "nom_ret LIKE $taxon ";
|
308 |
$filtres[] = "nom_ret LIKE $taxon ";
|
| 311 |
}
|
309 |
}
|
| 312 |
if (isset($num_taxon) || isset($taxon)) {
|
310 |
if (isset($num_taxon) || isset($taxon)) {
|
| 313 |
$from .= 'LEFT JOIN cel_obs_images coi ON (coi.id_image = ci.id_image) '.
|
- |
|
| 314 |
'LEFT JOIN cel_obs co ON (coi.id_observation = co.id_observation) ';
|
311 |
$from .= 'LEFT JOIN cel_obs co ON (ci.ce_observation = co.id_observation) ';
|
| 315 |
}
|
312 |
}
|
| Line 316... |
Line 313... |
| 316 |
|
313 |
|
| 317 |
$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
|
314 |
$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
|
| 318 |
}
|
315 |
}
|
| Line 321... |
Line 318... |
| 321 |
return $requete;
|
318 |
return $requete;
|
| 322 |
}
|
319 |
}
|
| Line 323... |
Line 320... |
| 323 |
|
320 |
|
| 324 |
private function construireRequeteNbreImgLiees() {
|
321 |
private function construireRequeteNbreImgLiees() {
|
| 325 |
$select = 'SELECT COUNT(DISTINCT ci.id_image) AS nbre ';
|
322 |
$select = 'SELECT COUNT(DISTINCT ci.id_image) AS nbre ';
|
| 326 |
$from = 'FROM cel_obs_images coi '.
|
- |
|
| Line 327... |
Line 323... |
| 327 |
' LEFT JOIN cel_images ci ON (coi.id_image = ci.id_image) ';
|
323 |
$from = 'FROM cel_images ci ';
|
| 328 |
|
324 |
|
| Line 329... |
Line 325... |
| 329 |
if (count($this->parametres) != 0) {
|
325 |
if (count($this->parametres) != 0) {
|
| Line 340... |
Line 336... |
| 340 |
if (isset($taxon)) {
|
336 |
if (isset($taxon)) {
|
| 341 |
$filtres[] = "nom_ret LIKE $taxon ";
|
337 |
$filtres[] = "nom_ret LIKE $taxon ";
|
| 342 |
}
|
338 |
}
|
| Line 343... |
Line 339... |
| 343 |
|
339 |
|
| 344 |
if (isset($num_taxon) || isset($taxon)) {
|
340 |
if (isset($num_taxon) || isset($taxon)) {
|
| 345 |
$from .= 'LEFT JOIN cel_obs ON (coi.id_observation = co.id_observation) ';
|
341 |
$from .= 'LEFT JOIN cel_obs ON (ci.ce_observation = co.id_observation) ';
|
| Line 346... |
Line 342... |
| 346 |
}
|
342 |
}
|
| 347 |
|
343 |
|
| Line 348... |
Line 344... |
| 348 |
$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
|
344 |
$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
|
| 349 |
}
|
345 |
}
|
| 350 |
|
346 |
|
| Line 351... |
Line 347... |
| 351 |
$requete = $select.$from.$where;
|
347 |
$requete = $select.$from.$where;
|
| 352 |
return $requete;
|
348 |
return $requete;
|
| 353 |
}
|
349 |
}
|
| 354 |
|
350 |
|
| Line 355... |
Line 351... |
| 355 |
private function construireRequeteNbreObsLiees() {
|
351 |
private function construireRequeteNbreObsLiees() {
|
| 356 |
$select = 'SELECT COUNT(DISTINCT coi.id_observation) AS nbre ';
|
352 |
$select = 'SELECT COUNT(DISTINCT id_observation) AS nbre ';
|
| Line 357... |
Line 353... |
| 357 |
$from = 'FROM cel_obs_images coi '.
|
353 |
$from = 'FROM cel_images ci '.
|
| Line 455... |
Line 451... |
| 455 |
" WHERE transmission = 1" .
|
451 |
" WHERE transmission = 1" .
|
| 456 |
" AND TO_DAYS(NOW()) - TO_DAYS(date_transmission) <= $jours" .
|
452 |
" AND TO_DAYS(NOW()) - TO_DAYS(date_transmission) <= $jours" .
|
| 457 |
" GROUP BY ce_utilisateur ORDER BY nombreObs DESC LIMIT $nombre;";
|
453 |
" GROUP BY ce_utilisateur ORDER BY nombreObs DESC LIMIT $nombre;";
|
| 458 |
break;
|
454 |
break;
|
| 459 |
case "img":
|
455 |
case "img":
|
| 460 |
$req = "SELECT co.ce_utilisateur , co.prenom_utilisateur , co.nom_utilisateur , co.courriel_utilisateur , count(DISTINCT ci.id_image) as nombreImg" .
|
456 |
$req = "SELECT co.ce_utilisateur , co.prenom_utilisateur , co.nom_utilisateur , co.courriel_utilisateur , count(DISTINCT ci.id_image) as nombreImg ".
|
| 461 |
" FROM cel_images ci" .
|
457 |
"FROM cel_images ci ".
|
| 462 |
" RIGHT JOIN cel_obs_images coi ON coi.id_image = ci.id_image" .
|
- |
|
| 463 |
" LEFT JOIN cel_obs co ON coi.id_observation = co.id_observation" .
|
458 |
"RIGHT JOIN cel_obs co ON ci.ce_observation = co.id_observation ".
|
| 464 |
" WHERE co.transmission = 1" .
|
459 |
"WHERE co.transmission = 1 ".
|
| 465 |
" AND TO_DAYS(NOW()) - TO_DAYS(co.date_transmission) <= $jours" .
|
460 |
"AND TO_DAYS(NOW()) - TO_DAYS(co.date_transmission) <= $jours ".
|
| 466 |
" GROUP BY co.ce_utilisateur ORDER BY nombreImg DESC LIMIT $nombre;";
|
461 |
"GROUP BY co.ce_utilisateur ORDER BY nombreImg DESC LIMIT $nombre; ";
|
| 467 |
break;
|
462 |
break;
|
| 468 |
default:
|
463 |
default:
|
| 469 |
$req = "SELECT co.ce_utilisateur , co.prenom_utilisateur , co.nom_utilisateur , co.courriel_utilisateur ," .
|
464 |
$req = "SELECT co.ce_utilisateur, co.prenom_utilisateur, co.nom_utilisateur, co.courriel_utilisateur, ".
|
| 470 |
" count(DISTINCT ci.id_image) as nombreImg, count(DISTINCT co.id_observation) as nombreObs," .
|
465 |
"COUNT(DISTINCT ci.id_image) AS nombreImg, COUNT(DISTINCT co.id_observation) AS nombreObs, ".
|
| 471 |
" count(DISTINCT ci.id_image) + count(DISTINCT co.id_observation) as somme" .
|
466 |
"COUNT(DISTINCT ci.id_image) + COUNT(DISTINCT co.id_observation) AS somme ".
|
| 472 |
" FROM cel_images ci" .
|
467 |
"FROM cel_images ci ".
|
| 473 |
" RIGHT JOIN cel_obs_images coi ON coi.id_image = ci.id_image" .
|
- |
|
| 474 |
" LEFT JOIN cel_obs co ON coi.id_observation = co.id_observation" .
|
468 |
"RIGHT JOIN cel_obs co ON ci.ce_observation = co.id_observation ".
|
| 475 |
" WHERE co.transmission = 1" .
|
469 |
"WHERE co.transmission = 1 ".
|
| 476 |
" AND TO_DAYS(NOW()) - TO_DAYS(co.date_transmission) <= $jours" .
|
470 |
"AND TO_DAYS(NOW()) - TO_DAYS(co.date_transmission) <= $jours ".
|
| 477 |
" GROUP BY co.ce_utilisateur" .
|
471 |
"GROUP BY co.ce_utilisateur ".
|
| 478 |
" ORDER BY somme DESC LIMIT $nombre;";
|
472 |
"ORDER BY somme DESC LIMIT $nombre ; ";
|
| 479 |
}
|
473 |
}
|
| Line 480... |
Line 474... |
| 480 |
|
474 |
|
| 481 |
return $req;
|
475 |
return $req;
|
| 482 |
}
|
476 |
}
|