/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/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/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__; |
/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; |