Subversion Repositories eFlore/Applications.cel

Compare Revisions

Ignore whitespace Rev 2448 → Rev 2449

/trunk/jrest/services/CelStatistiqueTxt.php
384,7 → 384,6
* @return array
*/
private function getGrandsContributeurs() {
 
$nombre = 10; // les $nombre plus importants contributeurs
$jours = 7; // depuis $jours jours
$critere = null; // "obs", "img" ou null (les deux)
443,36 → 442,41
}
 
private function construireRequeteGrandsContributeurs($nombre = 10, $jours = 7, $critere = null) {
$req = "";
$requete = '';
switch ($critere) {
case "obs":
$req = "SELECT ce_utilisateur , prenom_utilisateur , nom_utilisateur , courriel_utilisateur , count(*) as nombreObs" .
" FROM cel_obs" .
" WHERE transmission = 1" .
" AND TO_DAYS(NOW()) - TO_DAYS(date_transmission) <= $jours" .
" GROUP BY ce_utilisateur ORDER BY nombreObs DESC LIMIT $nombre;";
case 'obs':
$requete = "SELECT ce_utilisateur , prenom_utilisateur , nom_utilisateur , courriel_utilisateur , ".
" COUNT(*) AS nombreObs ".
"FROM cel_obs ".
"WHERE transmission = 1 ".
"AND TO_DAYS(NOW()) - TO_DAYS(date_transmission) <= $jours ".
"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 ".
case 'img':
$requete = "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 ".
" 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; ";
"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, ".
$requete = "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 ".
" 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 ; ";
"ORDER BY somme DESC ".
"LIMIT $nombre ";
}
 
return $req;
return $requete;
}
}
?>
}
/trunk/jrest/services/CelWidgetMap.php
63,8 → 63,8
$json = null;
$requete = 'SELECT utm_secteur, utm_x, utm_y, wgs84_latitude AS latitude, wgs84_longitude AS longitude '.
'FROM cel_obs AS co '.
' LEFT JOIN cel_zones_geo AS l '.
' ON (l.id_zone_geo = co.ce_zone_geo) '.
' LEFT JOIN cel_zones_geo AS zg '.
' ON (zg.id_zone_geo = co.ce_zone_geo) '.
"WHERE transmission = '1' ".
$this->construireWhereDept().
$this->construireWhereCommune().
137,8 → 137,8
' lieudit, zone_geo, date_observation, milieu, commentaire, '.
' utm_secteur, utm_x, utm_y, id_zone_geo, date_transmission, nom_referentiel '.
'FROM cel_obs AS co '.
' LEFT JOIN cel_zones_geo AS l '.
" ON (l.id_zone_geo = co.ce_zone_geo) ".
' LEFT JOIN cel_zones_geo AS zg '.
" ON (zg.id_zone_geo = co.ce_zone_geo) ".
"WHERE transmission = '1' ".
$this->construireWhereCoordonnees().
$this->construireWherePhotosSeulement().
276,8 → 276,8
 
$requete = 'SELECT SQL_CALC_FOUND_ROWS DISTINCT nom_ret, nom_ret_nn, nt, famille '.
'FROM cel_obs AS co '.
' LEFT JOIN cel_zones_geo AS l '.
' ON (l.id_zone_geo = co.ce_zone_geo) '.
' LEFT JOIN cel_zones_geo AS zg '.
' ON (zg.id_zone_geo = co.ce_zone_geo) '.
"WHERE transmission = '1' ".
" AND nom_ret != '' ".
$this->construireWhereDept().
506,11 → 506,11
private function obtenirObsLieesImg($type, $param) {
// Construction de la requête
$requete = 'SELECT DISTINCT id_observation AS id_obs, co.ce_utilisateur AS utilisateur '.
'FROM cel_images ci'.
'FROM cel_images AS ci'.
' INNER JOIN cel_obs AS co '.
' ON ci.ce_observation = co.id_observation '.
' LEFT JOIN locations AS l '.
' ON (l.id_zone_geo = co.ce_zone_geo) '.
' LEFT JOIN cel_zones_geo AS zg '.
' ON (zg.id_zone_geo = co.ce_zone_geo) '.
"WHERE co.transmission = '1' ".
($type == 'date.photo' ? " AND (date_prise_de_vue LIKE ".str_replace('-', ':', $param).") " : '').
($type == 'date.ajout' ? " AND ci.date_creation LIKE $param " : '').
666,11 → 666,11
$tag_sql = $this->getSqlWhereMotsCles($tag);
// Construction de la requête
$requete = 'SELECT DISTINCT co.id_observation AS id_obs, ci.ce_utilisateur AS utilisateur '.
'FROM cel_images ci'.
'FROM cel_images AS ci '.
' INNER JOIN cel_obs AS co '.
' ON (ci.ce_observation = co.id_observation) '.
' LEFT JOIN cel_zones_geo AS l '.
" ON (l.id_zone_geo = co.ce_zone_geo) ".
' LEFT JOIN cel_zones_geo AS zg '.
" ON (zg.id_zone_geo = co.ce_zone_geo) ".
"WHERE co.transmission = '1' ".
$this->construireWhereCoordonnees().
$this->construireWhereUtilisateur().
/trunk/jrest/services/CelWidgetMapPoint.php
107,8 → 107,8
"wgs84_longitude, ".
$concatenation_id." as id_coord ".
'FROM cel_obs AS co '.
' LEFT JOIN cel_zones_geo AS l '.
' ON (l.nom = co.zone_geo AND l.id_zone_geo = co.ce_zone_geo) '.
' LEFT JOIN cel_zones_geo AS zg '.
' ON (zg.nom = co.zone_geo AND zg.id_zone_geo = co.ce_zone_geo) '.
"WHERE transmission = '1' ".
" AND (".
$this->construireWhereRectangleStationOR()." OR ".
145,8 → 145,8
private function compterObservations($params) {
$requete = 'SELECT COUNT(*) as nb '.
'FROM cel_obs AS co '.
' LEFT JOIN cel_zones_geo AS l '.
' ON (l.nom = co.zone_geo AND l.id_zone_geo = co.ce_zone_geo) '.
' LEFT JOIN cel_zones_geo AS zg '.
' ON (zg.nom = co.zone_geo AND zg.id_zone_geo = co.ce_zone_geo) '.
"WHERE transmission = '1' ".
" AND (".
$this->construireWhereRectangleStationOR()." OR ".
333,8 → 333,8
' lieudit, zone_geo, date_observation, milieu, commentaire, '.
' utm_secteur, utm_x, utm_y, code, date_transmission, nom_referentiel '.
'FROM cel_obs AS co '.
' LEFT JOIN cel_zones_geo AS l '.
" ON (l.nom = co.zone_geo AND l.id_zone_geo = co.ce_zone_geo) ".
' LEFT JOIN cel_zones_geo AS zg '.
" ON (zg.nom = co.zone_geo AND zg.id_zone_geo = co.ce_zone_geo) ".
"WHERE transmission = '1' ".
(($this->communeEstDemandee()) ? $this->construireWhereCommuneSansCoordonneesAvecSensibles() : $this->construireWhereCoordonneesSansSensibles()).
$this->construireWhereDept().
479,8 → 479,8
 
$requete = 'SELECT SQL_CALC_FOUND_ROWS DISTINCT nom_ret, nom_ret_nn, nt, famille '.
'FROM cel_obs AS co '.
' LEFT JOIN cel_zones_geo AS l '.
' ON (l.nom = co.zone_geo AND l.id_zone_geo = co.ce_zone_geo) '.
' LEFT JOIN cel_zones_geo AS zg '.
' ON (zg.nom = co.zone_geo AND zg.id_zone_geo = co.ce_zone_geo) '.
"WHERE transmission = '1' ".
" AND nom_ret != '' ".
$this->construireWhereDept().
737,11 → 737,11
private function obtenirObsLieesImg($type, $param) {
// Construction de la requête
$requete = 'SELECT DISTINCT co.id_obs, ci.ce_utilisateur AS utilisateur '.
'FROM cel_images '.
'FROM cel_images AS ci '.
' LEFT JOIN cel_obs AS co '.
' ON (ci.ce_observation = co.id_observation) '.
' LEFT JOIN cel_zones_geo AS l '.
' ON (l.nom = co.zone_geo AND l.id_zone_geo = co.ce_zone_geo) '.
' LEFT JOIN cel_zones_geo AS zg '.
' ON (zg.nom = co.zone_geo AND zg.id_zone_geo = co.ce_zone_geo) '.
"WHERE co.transmission = '1' ".
($type == 'date.photo' ? " AND (ci.date_prise_de_vue LIKE ".str_replace('-', ':', $param).' ' : '').
($type == 'date.creation' ? " AND ci.date_creation LIKE $param " : '').
1098,8 → 1098,8
'FROM cel_images ci'.
' INNER JOIN cel_obs AS co '.
' ON (ci.ce_observation = co.id_observation) '.
' LEFT JOIN cel_zones_geo AS l '.
" ON (l.nom = co.zone_geo AND l.id_zone_geo = co.ce_zone_geo) ".
' LEFT JOIN cel_zones_geo AS zg '.
" ON (zg.nom = co.zone_geo AND zg.id_zone_geo = co.ce_zone_geo) ".
"WHERE co.transmission = '1' ".
$this->construireWhereCoordonnees().
$this->construireWhereUtilisateur().
/trunk/jrest/services/CelSyndicationImage.php
331,13 → 331,13
}
 
// Construction de la requête
$requete = (isset($this->distinct) ? 'SELECT DISTINCT' : 'SELECT').' ci.*, '.
' cim.id_image, cim.ce_utilisateur, nom_original, cim.date_creation, cim.mots_cles_texte AS mots_cles_texte_images, '.
' ci.mots_cles_texte AS mots_cles_texte_images_obs, cim.commentaire, note_qualite, nom_referentiel '.
'FROM cel_obs AS ci LEFT JOIN cel_images AS cim ON (ci.id_observation = cim.ce_observation) '.
'WHERE ci.transmission = 1 '.
' AND ci.ce_utilisateur = cim.ce_utilisateur '.
'ORDER BY '.(isset($this->orderby) && (!is_null($this->orderby)) ? $this->orderby : 'cim.date_creation DESC').' '.
$requete = (isset($this->distinct) ? 'SELECT DISTINCT' : 'SELECT').' co.*, '.
' ci.id_image, ci.ce_utilisateur, nom_original, ci.date_creation, ci.mots_cles_texte AS mots_cles_texte_images, '.
' co.mots_cles_texte AS mots_cles_texte_images_obs, ci.commentaire, note_qualite, nom_referentiel '.
'FROM cel_obs AS co LEFT JOIN cel_images AS ci ON (co.id_observation = ci.ce_observation) '.
'WHERE co.transmission = 1 '.
' AND co.ce_utilisateur = ci.ce_utilisateur '.
'ORDER BY '.(isset($this->orderby) && (!is_null($this->orderby)) ? $this->orderby : 'ci.date_creation DESC').' '.
"LIMIT $this->start, $this->limit ";
 
$elements = $this->executerRequete($requete);
366,15 → 366,15
private function getServiceComplet() {
 
// Construction de la requête
$requete = (isset($this->distinct) ? 'SELECT DISTINCT' : 'SELECT').' ci.*, '.
' cim.id_image, ci.ce_utilisateur, nom_original, cim.date_creation, cim.mots_cles_texte AS mots_cles_texte_images, '.
' ci.mots_cles_texte AS mots_cles_texte_obs, cim.commentaire AS commentaire_img, note_qualite, nom_referentiel, '.
' ci.commentaire AS commentaire_obs '.
'FROM cel_images AS cim '.
' LEFT JOIN cel_obs AS ci '.
' ON (cim.ce_observation = ci.id_observation) '.
(($this->etreFluxAdmin()) ? '' : 'WHERE ci.transmission = 1 ').
'ORDER BY '.(isset($this->orderby) && (!is_null($this->orderby)) ? $this->orderby : 'cim.date_creation DESC').' '.
$requete = (isset($this->distinct) ? 'SELECT DISTINCT' : 'SELECT').' co.*, '.
' ci.id_image, co.ce_utilisateur, nom_original, ci.date_creation, ci.mots_cles_texte AS mots_cles_texte_images, '.
' co.mots_cles_texte AS mots_cles_texte_obs, ci.commentaire AS commentaire_img, note_qualite, nom_referentiel, '.
' co.commentaire AS commentaire_obs '.
'FROM cel_images AS ci '.
' LEFT JOIN cel_obs AS co '.
' ON (ci.ce_observation = co.id_observation) '.
(($this->etreFluxAdmin()) ? '' : 'WHERE co.transmission = 1 ').
'ORDER BY '.(isset($this->orderby) && (!is_null($this->orderby)) ? $this->orderby : 'ci.date_creation DESC').' '.
"LIMIT $this->start, $this->limit ";
//echo $requete;
$elements = $this->executerRequete($requete);
/trunk/jrest/services/ImageDateList.php
28,71 → 28,62
 
private $correspondance_fonction = array(1 => 'year', 2 => 'month', 3 => 'day');
 
public function getElement($uid){
// Controle detournement utilisateur
$this->controleUtilisateur($uid[0]);
 
function getElement($uid){
if (!is_numeric($uid[0])) {
return;
}
 
// Controle detournement utilisateur
$this->controleUtilisateur($uid[0]);
$conditions = $this->traiterParametresEtConstruireRequete($uid);
 
if(!is_numeric($uid[0])) {
return;
}
$requete_liste_dates = 'SELECT DISTINCT '.
'date_prise_de_vue AS id '.
'FROM cel_images '.
"WHERE $conditions ".
'ORDER BY date_prise_de_vue';
 
$condition_requete = $this->traiterParametresEtConstruireRequete($uid);
 
$requete_liste_dates = 'SELECT DISTINCT '.
'date_prise_de_vue AS id '.
'FROM cel_images WHERE '.$condition_requete.' '.
'ORDER BY date_prise_de_vue';
 
$liste_dates = Cel::db()->requeter($requete_liste_dates);
 
$liste_dates = $this->formaterListeResultats($liste_dates);
 
$this->envoyerJson($liste_dates);
return true;
$liste_dates = Cel::db()->requeter($requete_liste_dates);
$liste_dates = $this->formaterListeResultats($liste_dates);
$this->envoyerJson($liste_dates);
return true;
}
 
private function formaterListeResultats($liste_dates) {
 
if (!$liste_dates) {
$liste_dates = array();
}
$liste_dates = array();
}
 
foreach($liste_dates as &$date) {
 
if($date['id'] == null || trim($date['id']) == '' || $date['id'] == 'null') {
$date = '0000-00-00';
} else {
$date_heures = explode(' ',$date['id']);
if(count($date_heures) > 1) {
$date = $date_heures[0];
} else {
$date = $date['id'];
}
}
}
 
return $liste_dates;
foreach($liste_dates as &$date) {
if ($date['id'] == null || trim($date['id']) == '' || $date['id'] == 'null') {
$date = '0000-00-00';
} else {
$date_heures = explode(' ',$date['id']);
if (count($date_heures) > 1) {
$date = $date_heures[0];
} else {
$date = $date['id'];
}
}
}
return $liste_dates;
}
 
private function traiterParametresEtConstruireRequete($params) {
$conditions = ' ce_utilisateur = '.Cel::db()->proteger($params[0]);
 
$requete_condition = ' ce_utilisateur = '.Cel::db()->proteger($params[0]);
 
$taille_tableau_parametres = count($params);
for($i=1; $i < $taille_tableau_parametres; $i++) {
if($this->estUnParametreDate($params[$i])) {
for ($i = 1; $i < $taille_tableau_parametres; $i++) {
if ($this->estUnParametreDate($params[$i])) {
$fonction_date = $this->correspondance_fonction[$i];
$requete_condition .= ' AND '.$fonction_date.'(date_prise_de_vue) = '.Cel::db()->proteger($params[$i]);
$conditions .= ' AND '.$fonction_date.'(date_prise_de_vue) = '.Cel::db()->proteger($params[$i]);
}
}
 
return $requete_condition;
return $conditions;
}
 
private function estUnParametreDate($valeur) {
return is_numeric($valeur) && $valeur != "all";
return is_numeric($valeur) && $valeur != 'all';
}
}
?>
}
/trunk/jrest/lib/RechercheImage.php
37,7 → 37,6
}
 
public function rechercherImages($id_utilisateur = null, $criteres = array(), $debut = 0 , $limite = 50) {
 
$ordre = (isset($criteres['tri']) && $criteres['tri']) ? $criteres['tri'] : 'ci.ordre';
unset($criteres['tri']);
$direction = (isset($criteres['dir']) && $criteres['dir']) ? $criteres['dir'] : 'ASC';
67,23 → 66,20
}
 
public function compterImages($id_utilisateur = null, $criteres = array()) {
 
$ordre = (isset($criteres['tri']) && $criteres['tri']) ? $criteres['tri'] : 'ci.ordre';
unset($criteres['tri']);
$direction = (isset($criteres['dir']) && $criteres['dir']) ? $criteres['dir'] : 'ASC';
unset($criteres['dir']);
 
$requete_recherche_images = 'SELECT COUNT(*) as nb_images ';
$requete_recherche_images = 'SELECT COUNT(*) AS nb_images ';
 
if ($this->doitJoindreTableObs($criteres)) {
$requete_recherche_images .= $this->fabriquerRequeteJointureObs();
$requete_recherche_images .= ($id_utilisateur != null) ? 'AND ci.ce_utilisateur = '.Cel::db()->proteger($id_utilisateur) : '';
 
} else {
$requete_recherche_images .= 'FROM cel_images ci ';
$requete_recherche_images .= ($id_utilisateur != null) ? 'WHERE ci.ce_utilisateur = '.Cel::db()->proteger($id_utilisateur) : '';
}
 
$sous_requete_recherche = $this->fabriquerSousRequeteRecherche($id_utilisateur, $criteres);
 
$requete_recherche_images .= $sous_requete_recherche;
/trunk/jrest/lib/GestionImage.php
203,8 → 203,8
$idUtilisateurP = Cel::db()->proteger($id_utilisateur);
 
$requete = 'SELECT id_image '.
'FROM cel_images WHERE '.
"ce_utilisateur = $idUtilisateurP ".
'FROM cel_images '.
"WHERE ce_utilisateur = $idUtilisateurP ".
"AND ordre IN ($idsImagesP) ".
' -- '.__FILE__.' : '.__LINE__;