Subversion Repositories eFlore/Applications.cel

Compare Revisions

Ignore whitespace Rev 2445 → Rev 2446

/trunk/jrest/services/CelStatistiqueTxt.php
70,9 → 70,8
private function construireRequeteListeUtilisateurNbrePhoto() {
$select = 'SELECT co.courriel_utilisateur, COUNT(DISTINCT ci.id_image) AS nbre ';
$from = 'FROM cel_obs AS co '.
' LEFT JOIN cel_obs_images AS coi ON (coi.id_observation = co.id_observation) '.
' LEFT JOIN cel_images AS ci ON (coi.id_image = ci.id_image) ';
$where = 'WHERE transmission = 1 ';
' LEFT JOIN cel_images AS ci ON (co.id_observation = ci.ce_observation) ';
$where = 'WHERE co.transmission = 1 ';
$groupBy = 'GROUP BY co.courriel_utilisateur ';
$orderBy = 'ORDER BY nbre DESC ';
$limitSql = 'LIMIT 0,150 ';
125,10 → 124,9
private function construireRequeteListeTaxonNbrePhoto() {
$select = 'SELECT nom_ret, COUNT(DISTINCT ci.id_image) AS nbre ';
$from = 'FROM cel_obs co '.
' LEFT JOIN cel_obs_images coi ON (coi.id_observation = co.id_observation) '.
' LEFT JOIN cel_images ci ON (coi.id_image = ci.id_image) ';
$where = 'WHERE transmission = 1 '.
" AND nom_ret != '' ";
' LEFT JOIN cel_images ci ON (co.id_observation = ci.ce_observation) ';
$where = 'WHERE co.transmission = 1 '.
" AND nom_ret != '' ";
$groupBy = 'GROUP BY nom_ret ';
$orderBy = 'ORDER BY nbre DESC ';
$limitSql = 'LIMIT 0,150 ';
310,8 → 308,7
$filtres[] = "nom_ret LIKE $taxon ";
}
if (isset($num_taxon) || isset($taxon)) {
$from .= 'LEFT JOIN cel_obs_images coi ON (coi.id_image = ci.id_image) '.
'LEFT JOIN cel_obs co ON (coi.id_observation = co.id_observation) ';
$from .= 'LEFT JOIN cel_obs co ON (ci.ce_observation = co.id_observation) ';
}
 
$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
323,8 → 320,7
 
private function construireRequeteNbreImgLiees() {
$select = 'SELECT COUNT(DISTINCT ci.id_image) AS nbre ';
$from = 'FROM cel_obs_images coi '.
' LEFT JOIN cel_images ci ON (coi.id_image = ci.id_image) ';
$from = 'FROM cel_images ci ';
 
if (count($this->parametres) != 0) {
$filtres = array();
342,7 → 338,7
}
 
if (isset($num_taxon) || isset($taxon)) {
$from .= 'LEFT JOIN cel_obs ON (coi.id_observation = co.id_observation) ';
$from .= 'LEFT JOIN cel_obs ON (ci.ce_observation = co.id_observation) ';
}
 
$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
353,9 → 349,9
}
 
private function construireRequeteNbreObsLiees() {
$select = 'SELECT COUNT(DISTINCT coi.id_observation) AS nbre ';
$from = 'FROM cel_obs_images coi '.
' LEFT JOIN cel_obs co ON (coi.id_observation = co.id_observation) ';
$select = 'SELECT COUNT(DISTINCT id_observation) AS nbre ';
$from = 'FROM cel_images ci '.
' LEFT JOIN cel_obs co ON (ci.ce_observation = co.id_observation) ';
 
if (count($this->parametres) != 0) {
$filtres = array();
457,25 → 453,23
" GROUP BY ce_utilisateur ORDER BY nombreObs DESC LIMIT $nombre;";
break;
case "img":
$req = "SELECT co.ce_utilisateur , co.prenom_utilisateur , co.nom_utilisateur , co.courriel_utilisateur , count(DISTINCT ci.id_image) as nombreImg" .
" FROM cel_images ci" .
" RIGHT JOIN cel_obs_images coi ON coi.id_image = ci.id_image" .
" LEFT JOIN cel_obs co ON coi.id_observation = co.id_observation" .
" WHERE co.transmission = 1" .
" AND TO_DAYS(NOW()) - TO_DAYS(co.date_transmission) <= $jours" .
" GROUP BY co.ce_utilisateur ORDER BY nombreImg DESC LIMIT $nombre;";
$req = "SELECT co.ce_utilisateur , co.prenom_utilisateur , co.nom_utilisateur , co.courriel_utilisateur , count(DISTINCT ci.id_image) as nombreImg ".
"FROM cel_images ci ".
"RIGHT JOIN cel_obs co ON ci.ce_observation = co.id_observation ".
"WHERE co.transmission = 1 ".
"AND TO_DAYS(NOW()) - TO_DAYS(co.date_transmission) <= $jours ".
"GROUP BY co.ce_utilisateur ORDER BY nombreImg DESC LIMIT $nombre; ";
break;
default:
$req = "SELECT co.ce_utilisateur , co.prenom_utilisateur , co.nom_utilisateur , co.courriel_utilisateur ," .
" count(DISTINCT ci.id_image) as nombreImg, count(DISTINCT co.id_observation) as nombreObs," .
" count(DISTINCT ci.id_image) + count(DISTINCT co.id_observation) as somme" .
" FROM cel_images ci" .
" RIGHT JOIN cel_obs_images coi ON coi.id_image = ci.id_image" .
" LEFT JOIN cel_obs co ON coi.id_observation = co.id_observation" .
" WHERE co.transmission = 1" .
" AND TO_DAYS(NOW()) - TO_DAYS(co.date_transmission) <= $jours" .
" GROUP BY co.ce_utilisateur" .
" ORDER BY somme DESC LIMIT $nombre;";
$req = "SELECT co.ce_utilisateur, co.prenom_utilisateur, co.nom_utilisateur, co.courriel_utilisateur, ".
"COUNT(DISTINCT ci.id_image) AS nombreImg, COUNT(DISTINCT co.id_observation) AS nombreObs, ".
"COUNT(DISTINCT ci.id_image) + COUNT(DISTINCT co.id_observation) AS somme ".
"FROM cel_images ci ".
"RIGHT JOIN cel_obs co ON ci.ce_observation = co.id_observation ".
"WHERE co.transmission = 1 ".
"AND TO_DAYS(NOW()) - TO_DAYS(co.date_transmission) <= $jours ".
"GROUP BY co.ce_utilisateur ".
"ORDER BY somme DESC LIMIT $nombre ; ";
}
 
return $req;