Subversion Repositories eFlore/Applications.cel

Rev

Rev 970 | Rev 1765 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 970 Rev 1359
Line 55... Line 55...
55 
	private function getListeUtilisateursNbrePhotos() {
 55 
	private function getListeUtilisateursNbrePhotos() {
56 
		$liste = array();
 56 
		$liste = array();
Line 57... Line 57...
57
57
58 
		$requete = $this->construireRequeteListeUtilisateurNbrePhoto();
 58 
		$requete = $this->construireRequeteListeUtilisateurNbrePhoto();
59 
		if ($requete != null) {
 59 
		if ($requete != null) {
60 
			$resultats = $this->executerRequete($requete);
 - 
 
61
60 
			$resultats = $this->executerRequete($requete);
62 
			if ($resultats != false) {
 61 
			if ($resultats != false) {
63 
				foreach ($resultats as $resultat) {
 62 
				foreach ($resultats as $resultat) {
64 
					$liste[$resultat['identifiant']] = $resultat['nbre'];
 63 
					$liste[$resultat['courriel_utilisateur']] = $resultat['nbre'];
65 
				}
 64 
				}
66 
			}
 65 
			}
67 
		}
 66 
		}
68 
		return $liste;
 67 
		return $liste;
Line 69... Line 68...
69 
	}
 68 
	}
70
69
71 
	private function construireRequeteListeUtilisateurNbrePhoto() {
 70 
	private function construireRequeteListeUtilisateurNbrePhoto() {
72 
		$select = 	'SELECT identifiant, COUNT(DISTINCT ci_id_image) AS nbre ';
 71 
		$select = 	'SELECT co.courriel_utilisateur, COUNT(DISTINCT ci.id_image) AS nbre ';
73 
		$from = 	'FROM cel_inventory '.
 72 
		$from = 	'FROM cel_obs co '.
74 
					'	LEFT JOIN cel_obs_images ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) '.
 73 
					'	LEFT JOIN cel_obs_images coi ON (coi.id_observation = co.id_observation) '.
75 
					'	LEFT JOIN cel_images ON (coi_ce_image = ci_id_image) ';
 74 
					'	LEFT JOIN cel_images ci ON (coi.id_image = ci.id_image) ';
76 
		$where = 	'WHERE transmission = 1 ';
 75 
		$where = 	'WHERE transmission = 1 ';
77 
		$groupBy = 	'GROUP BY identifiant ';
 76 
		$groupBy = 	'GROUP BY co.courriel_utilisateur ';
Line 78... Line 77...
78 
		$orderBy = 	'ORDER BY nbre DESC ';
 77 
		$orderBy = 	'ORDER BY nbre DESC ';
79 
		$limitSql =	'LIMIT 0,150 ';
 - 
 
80
78 
		$limitSql =	'LIMIT 0,150 ';
81 
		$zero_images = false;
 79
Line 82... Line 80...
82
80 
		$zero_images = false;
Line 83... Line 81...
83 
		if (count($this->parametres) != 0) {
 81 
		if (count($this->parametres) != 0) {
84 
			extract($this->parametres);
 82 
			extract($this->parametres);
85
83
86 
			$filtres = array();
 84 
			$filtres = array();
87
85
88 
			if (isset($utilisateur)) {
 86 
			if (isset($utilisateur)) {
89 
				$filtres[] = "identifiant = $utilisateur ";
 87 
				$filtres[] = "co.courriel_utilisateur = $utilisateur ";
90 
			}
 88 
			}
91 
			if (isset($num_taxon)) {
 89 
			if (isset($num_taxon)) {
92 
				$filtres[] = "num_taxon = $num_taxon ";
 90 
				$filtres[] = "nt = $num_taxon ";
Line 147... Line 145...
147 
		return $where;
 145 
		return $where;
148 
	}
 146 
	}
Line 149... Line 147...
149
147
150 
	private function getListeTaxonsNbrePhotos() {
 148 
	private function getListeTaxonsNbrePhotos() {
- 
 
 149 
		$requete = $this->construireRequeteListeTaxonNbrePhoto();
151 
		$requete = $this->construireRequeteListeTaxonNbrePhoto();
 150 
 
152 
		$resultats = $this->executerRequete($requete);
 151 
		$resultats = $this->executerRequete($requete);
153 
		$liste = array();
 152 
		$liste = array();
154 
		if ($resultats != false) {
 153 
		if ($resultats != false) {
155 
			foreach ($resultats as $resultat) {
 154 
			foreach ($resultats as $resultat) {
Line 158... Line 157...
158 
		}
 157 
		}
159 
		return $liste;
 158 
		return $liste;
160 
	}
 159 
	}
Line 161... Line 160...
161
160
162 
	private function construireRequeteListeTaxonNbrePhoto() {
 161 
	private function construireRequeteListeTaxonNbrePhoto() {
163 
		$select = 	'SELECT nom_ret, COUNT(DISTINCT ci_id_image) AS nbre ';
 162 
		$select = 	'SELECT nom_ret, COUNT(DISTINCT ci.id_image) AS nbre ';
164 
		$from = 	'FROM cel_inventory '.
 163 
		$from = 	'FROM cel_obs co '.
165 
					'	LEFT JOIN cel_obs_images ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) '.
 164 
					'	LEFT JOIN cel_obs_images coi ON (coi.id_observation = co.id_observation) '.
166 
					'	LEFT JOIN cel_images ON (coi_ce_image = ci_id_image) ';
 165 
					'	LEFT JOIN cel_images ci ON (coi.id_image = ci.id_image) ';
167 
		$where = 	'WHERE transmission = 1 '.
 166 
		$where = 	'WHERE transmission = 1 '.
168 
					"	AND nom_ret != '' ";
 167 
					"	AND nom_ret != '' ";
169 
		$groupBy = 	'GROUP BY nom_ret ';
 168 
		$groupBy = 	'GROUP BY nom_ret ';
170 
		$orderBy = 	'ORDER BY nbre DESC ';
 169 
		$orderBy = 	'ORDER BY nbre DESC ';
Line 175... Line 174...
175 
			extract($this->parametres);
 174 
			extract($this->parametres);
Line 176... Line 175...
176
175
Line 177... Line 176...
177 
			$filtres = array();
 176 
			$filtres = array();
178
177
179 
			if (isset($utilisateur)) {
 178 
			if (isset($utilisateur)) {
180 
				$filtres[] = "identifiant = $utilisateur ";
 179 
				$filtres[] = "co.courriel_utilisateur = $utilisateur ";
181 
			}
 180 
			}
182 
			if (isset($num_taxon)) {
 181 
			if (isset($num_taxon)) {
183 
				$filtres[] = "num_taxon = $num_taxon ";
 182 
				$filtres[] = "nt = $num_taxon ";
184 
			}
 183 
			}
185 
			if (isset($taxon)) {
 184 
			if (isset($taxon)) {
186 
				$filtres[] = "nom_ret LIKE $taxon ";
 185 
				$filtres[] = "nom_ret LIKE $taxon ";
Line 194... Line 193...
194 
				$limitSql = str_replace('150', $limit, $limitSql);
 193 
				$limitSql = str_replace('150', $limit, $limitSql);
195 
			}
 194 
			}
196 
		}
 195 
		}
Line 197... Line 196...
197
196
- 
 
 197 
		$requete = $select.$from.$where.$groupBy.$orderBy.$limitSql;
198 
		$requete = $select.$from.$where.$groupBy.$orderBy.$limitSql;
 198 
 
199 
		return $requete;
 199 
		return $requete;
Line 200... Line 200...
200 
	}
 200 
	}
Line 236... Line 236...
236
236
237 
		return $info;
237 
		return $info;
Line 238... Line 238...
238 
	}
 238 
	}
239
239
240 
	private function construireRequeteNbreObs() {
 240 
	private function construireRequeteNbreObs() {
Line 241... Line 241...
241 
		$requete = 	'SELECT COUNT(id) AS nbre '.
 241 
		$requete = 	'SELECT COUNT(id_observation) AS nbre '.
242 
					'FROM cel_inventory ';
 242 
					'FROM cel_obs ';
243 
 
 243 
 
Line 244... Line 244...
244 
		if (count($this->parametres) != 0) {
 244 
		if (count($this->parametres) != 0) {
245 
			$filtres = array();
 245 
			$filtres = array();
246 
			extract($this->parametres);
 246 
			extract($this->parametres);
247
247
248 
			if (isset($utilisateur)) {
 248 
			if (isset($utilisateur)) {
249 
				$filtres[] = "identifiant = $utilisateur ";
 249 
				$filtres[] = "courriel_utilisateur = $utilisateur ";
250 
			}
 250 
			}
251 
			if (isset($num_taxon)) {
 251 
			if (isset($num_taxon)) {
252 
				$filtres[] = "num_taxon = $num_taxon ";
 252 
				$filtres[] = "num_taxon = $num_taxon ";
Line 253... Line 253...
253 
			}
 253 
			}
254 
			if (isset($taxon)) {
 254 
			if (isset($taxon)) {
255 
				$filtres[] = "nom_ret LIKE $taxon ";
 255 
				$filtres[] = "nom_ret LIKE $taxon ";
256 
			}
 256 
			}
257
257
Line 258... Line 258...
258 
			$requete .= ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
 258 
			$requete .= ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
259 
		}
 259 
		}
260
260 
 
261 
		return $requete;
 261 
		return $requete;
Line 262... Line 262...
262 
	}
 262 
	}
263
263
264 
	private function construireRequeteNbreObsPubliques() {
 264 
	private function construireRequeteNbreObsPubliques() {
Line 265... Line 265...
265 
		$requete = 	'SELECT COUNT(id) AS nbre '.
 265 
		$requete = 	'SELECT COUNT(id_observation) AS nbre '.
266 
					'FROM cel_inventory '.
 266 
					'FROM cel_obs '.
267 
					"WHERE transmission = 1 ";
 267 
					"WHERE transmission = 1 ";
268 
 
 268 
 
269 
		if (count($this->parametres) != 0) {
 269 
		if (count($this->parametres) != 0) {
270 
			$filtres = array();
 270 
			$filtres = array();
271 
			extract($this->parametres);
 271 
			extract($this->parametres);
Line 285... Line 285...
285
285
286 
		return $requete;
 286 
		return $requete;
Line 287... Line 287...
287 
	}
 287 
	}
288
288
289 
	private function construireRequeteNbreObsParCommune() {
 289 
	private function construireRequeteNbreObsParCommune() {
290 
		$requete = 	'SELECT COUNT(id) AS nbre '.
 290 
		$requete = 	'SELECT COUNT(id_observation) AS nbre '.
291 
					'FROM cel_inventory '.
 291 
					'FROM cel_obs '.
292 
					"WHERE location != '000null' ".
 292 
					"WHERE zone_geo IS NOT NULL ".
Line 293... Line 293...
293 
					"	AND id_location != '000null' ";
 293 
					"	AND ce_zone_geo IS NOT NULL ";
294 
		$groupBy = 'GROUP BY location, id_location';
 294 
		$groupBy = 'GROUP BY zone_geo, ce_zone_geo';
295 
 
 295 
 
Line 296... Line 296...
296 
		if (count($this->parametres) != 0) {
 296 
		if (count($this->parametres) != 0) {
297 
			$filtres = array();
 297 
			$filtres = array();
298 
			extract($this->parametres);
 298 
			extract($this->parametres);
299
299
300 
			if (isset($utilisateur)) {
 300 
			if (isset($utilisateur)) {
301 
				$filtres[] = "identifiant = $utilisateur ";
 301 
				$filtres[] = "courriel_utilisateur = $utilisateur ";
302 
			}
 302 
			}
303 
			if (isset($num_taxon)) {
 303 
			if (isset($num_taxon)) {
304 
				$filtres[] = "num_taxon = $num_taxon ";
 304 
				$filtres[] = "nt = $num_taxon ";
Line 313... Line 313...
313
313
314 
		return $requete;
 314 
		return $requete;
Line 315... Line 315...
315 
	}
 315 
	}
316
316
317 
	private function construireRequeteNbreImg() {
 317 
	private function construireRequeteNbreImg() {
Line 318... Line 318...
318 
		$select = 	'SELECT COUNT(DISTINCT ci_id_image) AS nbre ';
 318 
		$select = 	'SELECT COUNT(DISTINCT ci.id_image) AS nbre ';
319 
		$from = 'FROM cel_images ';
 319 
		$from = 'FROM cel_images ci ';
Line 320... Line 320...
320
320
Line 321... Line 321...
321 
		if (count($this->parametres) != 0) {
 321 
		if (count($this->parametres) != 0) {
322 
			$filtres = array();
 322 
			$filtres = array();
323
323
324 
			extract($this->parametres);
 324 
			extract($this->parametres);
Line 325... Line 325...
325
325
326 
			if (isset($utilisateur)) {
 326 
			if (isset($utilisateur)) {
327 
				$filtres[] = "ci_ce_utilisateur = $utilisateur ";
 327 
				$filtres[] = "courriel_utilisateur = $utilisateur ";
328 
			}
 328 
			}
329 
			if (isset($num_taxon)) {
 329 
			if (isset($num_taxon)) {
330
330
331 
				$filtres[] = "num_taxon = $num_taxon ";
 331 
				$filtres[] = "nt = $num_taxon ";
332 
			}
 332 
			}
333 
			if (isset($taxon)) {
 333 
			if (isset($taxon)) {
Line 334... Line 334...
334 
				$filtres[] = "nom_ret LIKE $taxon ";
 334 
				$filtres[] = "nom_ret LIKE $taxon ";
335 
			}
 335 
			}
336 
			if (isset($num_taxon) || isset($taxon)) {
 - 
 
337 
				$from .= 'LEFT JOIN cel_obs_images ON (coi_ce_image = ci_id_image) '.
 336 
			if (isset($num_taxon) || isset($taxon)) {
- 
 
 337 
				$from .= 'LEFT JOIN cel_obs_images coi ON (coi.id_image = ci.id_image) '.
338 
						'LEFT JOIN cel_inventory ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) ';
 338 
						'LEFT JOIN cel_obs co ON (coi.id_observation = co.id_observation) ';
339 
			}
 339 
			}
Line 340... Line 340...
340
340
341 
			$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
 341 
			$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
342 
		}
 342 
		}
343
343 
		$requete = $select.$from.$where;
Line 344... Line 344...
344 
		$requete = $select.$from.$where;
 344 
 
345 
		return $requete;
 345 
		return $requete;
Line 346... Line 346...
346 
	}
 346 
	}
Line 347... Line 347...
347
347
348 
	private function construireRequeteNbreImgLiees() {
 348 
	private function construireRequeteNbreImgLiees() {
349 
		$select = 'SELECT COUNT(DISTINCT ci_id_image) AS nbre ';
 349 
		$select = 'SELECT COUNT(DISTINCT ci.id_image) AS nbre ';
350 
		$from = 'FROM cel_obs_images '.
 350 
		$from = 'FROM cel_obs_images coi '.
351 
				'	LEFT JOIN cel_images ON (coi_ce_image = ci_id_image) ';
 351 
				'	LEFT JOIN cel_images ci ON (coi.id_image = ci.id_image) ';
352
352
353 
		if (count($this->parametres) != 0) {
 353 
		if (count($this->parametres) != 0) {
354 
			$filtres = array();
 354 
			$filtres = array();
355
355
Line 356... Line 356...
356 
			extract($this->parametres);
 356 
			extract($this->parametres);
357
357
358 
			if (isset($utilisateur)) {
 358 
			if (isset($utilisateur)) {
Line 359... Line 359...
359 
				$filtres[] = "coi_ce_utilisateur = $utilisateur ";
 359 
				$filtres[] = "ci.courriel_utilisateur = $utilisateur ";
360 
			}
 360 
			}
Line 361... Line 361...
361 
			if (isset($num_taxon)) {
 361 
			if (isset($num_taxon)) {
362 
				$filtres[] = "num_taxon = $num_taxon ";
 362 
				$filtres[] = "nt = $num_taxon ";
363 
			}
 363 
			}
Line 364... Line 364...
364 
			if (isset($taxon)) {
 364 
			if (isset($taxon)) {
365 
				$filtres[] = "nom_ret LIKE $taxon ";
 365 
				$filtres[] = "nom_ret LIKE $taxon ";
366 
			}
 366 
			}
367
367
Line 368... Line 368...
368 
			if (isset($num_taxon) || isset($taxon)) {
 368 
			if (isset($num_taxon) || isset($taxon)) {
369 
				$from .= 'LEFT JOIN cel_inventory ON (coi_ce_observation = ordre AND coi_ce_utilisateur = identifiant) ';
 369 
				$from .= 'LEFT JOIN cel_obs ON (coi.id_observation = co.id_observation) ';
Line 370... Line 370...
370 
			}
 370 
			}
Line 371... Line 371...
371
371
372 
			$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
 372 
			$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
373 
		}
 373 
		}
374
374
375 
		$requete = $select.$from.$where;
 375 
		$requete = $select.$from.$where;
376 
		return $requete;
 376 
		return $requete;
377 
	}
 377 
	}
378
378
379 
	private function construireRequeteNbreObsLiees() {
 379 
	private function construireRequeteNbreObsLiees() {