Subversion Repositories eFlore/Applications.cel

Rev

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

Rev 3667 Rev 3751
Line 74... Line 74...
74
		return $liste;
74
		return $liste;
75
	}
75
	}
Line 76... Line 76...
76
 
76
 
77
	private function construireRequeteListeUtilisateurNbrePhoto() {
77
	private function construireRequeteListeUtilisateurNbrePhoto() {
78
		$select = 	'SELECT co.courriel_utilisateur, COUNT(DISTINCT ci.id_image) AS nbre ';
78
		$select = 	'SELECT co.courriel_utilisateur, COUNT(DISTINCT ci.id_image) AS nbre ';
79
		$from = 	'FROM cel_obs AS co '.
79
		$from = 	'FROM cel_export_total AS co '.
80
			'	LEFT JOIN cel_images AS ci ON (co.id_observation = ci.ce_observation) ';
80
			'	LEFT JOIN cel_images_export AS ci ON (co.id_observation = ci.ce_observation) ';
81
		$where = 	'WHERE co.transmission = 1 ';
81
		$where = 	'WHERE co.transmission = 1 ';
82
		$groupBy = 	'GROUP BY co.courriel_utilisateur ';
82
		$groupBy = 	'GROUP BY co.courriel_utilisateur ';
83
		$orderBy = 	'ORDER BY nbre DESC ';
83
		$orderBy = 	'ORDER BY nbre DESC ';
Line 128... Line 128...
128
		return $liste;
128
		return $liste;
129
	}
129
	}
Line 130... Line 130...
130
 
130
 
131
	private function construireRequeteListeTaxonNbrePhoto() {
131
	private function construireRequeteListeTaxonNbrePhoto() {
132
		$select = 	'SELECT nom_ret, COUNT(DISTINCT ci.id_image) AS nbre ';
132
		$select = 	'SELECT nom_ret, COUNT(DISTINCT ci.id_image) AS nbre ';
133
		$from = 	'FROM cel_obs co '.
133
		$from = 	'FROM cel_export_total co '.
134
			'	LEFT JOIN cel_images ci ON (co.id_observation = ci.ce_observation) ';
134
			'	LEFT JOIN cel_images_export ci ON (co.id_observation = ci.ce_observation) ';
135
		$where = 	'WHERE co.transmission = 1 '.
135
		$where = 	'WHERE co.transmission = 1 '.
136
			"	AND nom_ret != '' ";
136
			"	AND nom_ret != '' ";
137
		$groupBy = 	'GROUP BY nom_ret ';
137
		$groupBy = 	'GROUP BY nom_ret ';
138
		$orderBy = 	'ORDER BY nbre DESC ';
138
		$orderBy = 	'ORDER BY nbre DESC ';
Line 220... Line 220...
220
		$info = (int) Cel::db()->requeterValeurUnique($requete);
220
		$info = (int) Cel::db()->requeterValeurUnique($requete);
221
		return $info;
221
		return $info;
222
	}
222
	}
Line 223... Line 223...
223
 
223
 
224
	private function construireRequeteNbreObs() {
224
	private function construireRequeteNbreObs() {
225
		$requete = 	'SELECT COUNT(id) AS nbre '.
225
		$requete = 	'SELECT COUNT(*) AS nbre '.
Line 226... Line 226...
226
					'FROM cel_obs ';
226
					'FROM cel_export_total ';
227
 
227
 
228
		if (count($this->parametres) != 0) {
228
		if (count($this->parametres) != 0) {
Line 229... Line 229...
229
			$filtres = array();
229
			$filtres = array();
230
			extract($this->parametres);
230
			extract($this->parametres);
231
 
231
 
232
			if (isset($utilisateur)) {
232
			if (isset($utilisateur)) {
233
				$filtres[] = "user_email = $utilisateur ";
233
				$filtres[] = "courriel_utilisateur = $utilisateur ";
234
			}
234
			}
235
			if (isset($num_taxon)) {
235
			if (isset($num_taxon)) {
236
				$filtres[] = "accepted_sci_name_id = $num_taxon ";
236
				$filtres[] = "nom_ret_nn = $num_taxon ";
237
			}
237
			}
Line 238... Line 238...
238
			if (isset($taxon)) {
238
			if (isset($taxon)) {
239
				$filtres[] = "accepted_sci_name LIKE $taxon ";
239
				$filtres[] = "nom_ret LIKE $taxon ";
Line 240... Line 240...
240
			}
240
			}
241
 
241
 
Line 242... Line 242...
242
			$requete .= ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
242
			$requete .= ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
243
		}
243
		}
244
 
244
 
245
		return $requete;
245
		return $requete;
Line 246... Line 246...
246
	}
246
	}
247
 
247
 
248
	private function construireRequeteNbreObsPubliques() {
248
	private function construireRequeteNbreObsPubliques() {
Line 249... Line 249...
249
		$requete = 	'SELECT COUNT(id) AS nbre '.
249
		$requete = 	'SELECT COUNT(id) AS nbre '.
250
					'FROM cel_obs '.
250
					'FROM cel_export_total '.
251
					"WHERE is_public = 1 ";
251
					"WHERE transmission = 1 ";
252
 
252
 
253
		if (count($this->parametres) != 0) {
253
		if (count($this->parametres) != 0) {
254
			$filtres = array();
254
			$filtres = array();
255
			extract($this->parametres);
255
			extract($this->parametres);
256
 
256
 
257
			if (isset($utilisateur)) {
257
			if (isset($utilisateur)) {
Line 258... Line 258...
258
				$filtres[] = "user_email = $utilisateur ";
258
				$filtres[] = "courriel_utilisateur = $utilisateur ";
259
			}
259
			}
Line 260... Line 260...
260
			if (isset($num_taxon)) {
260
			if (isset($num_taxon)) {
261
				$filtres[] = "accepted_sci_name_id = $num_taxon ";
261
				$filtres[] = "nom_ret_nn = $num_taxon ";
Line 262... Line 262...
262
			}
262
			}
263
			if (isset($taxon)) {
263
			if (isset($taxon)) {
264
				$filtres[] = "accepted_sci_name LIKE $taxon ";
264
				$filtres[] = "nom_ret LIKE $taxon ";
265
			}
265
			}
266
 
266
 
267
			$requete .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');
267
			$requete .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');
Line 268... Line 268...
268
		}
268
		}
Line 298... Line 298...
298
		return $requete;
298
		return $requete;
299
	}
299
	}
Line 300... Line 300...
300
 
300
 
301
	private function construireRequeteNbreImg() {
301
	private function construireRequeteNbreImg() {
302
		$select = 	'SELECT COUNT(DISTINCT ci.id_image) AS nbre ';
302
		$select = 	'SELECT COUNT(DISTINCT ci.id_image) AS nbre ';
Line 303... Line 303...
303
		$from = 'FROM cel_images ci ';
303
		$from = 'FROM cel_images_export ci ';
304
 
304
 
Line 305... Line 305...
305
		if (count($this->parametres) != 0) {
305
		if (count($this->parametres) != 0) {
Line 316... Line 316...
316
			}
316
			}
317
			if (isset($taxon)) {
317
			if (isset($taxon)) {
318
				$filtres[] = "nom_ret LIKE $taxon ";
318
				$filtres[] = "nom_ret LIKE $taxon ";
319
			}
319
			}
320
			if (isset($num_taxon) || isset($taxon)) {
320
			if (isset($num_taxon) || isset($taxon)) {
321
				$from .= 'LEFT JOIN cel_obs co ON (ci.ce_observation = co.id_observation) ';
321
				$from .= 'LEFT JOIN cel_export_total co ON (ci.ce_observation = co.id_observation) ';
322
			}
322
			}
Line 323... Line 323...
323
 
323
 
324
			$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
324
			$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
325
		}
325
		}
Line 328... Line 328...
328
		return $requete;
328
		return $requete;
329
	}
329
	}
Line 330... Line 330...
330
 
330
 
331
	private function construireRequeteNbreImgLiees($publiquesSeulement=false) {
331
	private function construireRequeteNbreImgLiees($publiquesSeulement=false) {
332
		$select = 'SELECT COUNT(DISTINCT ci.id_image) AS nbre ';
332
		$select = 'SELECT COUNT(DISTINCT ci.id_image) AS nbre ';
Line 333... Line 333...
333
		$from = 'FROM cel_images ci ';
333
		$from = 'FROM cel_images_export ci ';
334
 
334
 
Line 335... Line 335...
335
		if (count($this->parametres) != 0) {
335
		if (count($this->parametres) != 0) {
Line 349... Line 349...
349
			if ($publiquesSeulement === true) {
349
			if ($publiquesSeulement === true) {
350
				$filtres[] = "ci.transmission = 1";
350
				$filtres[] = "ci.transmission = 1";
351
			}
351
			}
Line 352... Line 352...
352
 
352
 
353
			if (isset($num_taxon) || isset($taxon)) {
353
			if (isset($num_taxon) || isset($taxon)) {
354
				$from .= 'LEFT JOIN cel_obs ON (ci.ce_observation = co.id_observation) ';
354
				$from .= 'LEFT JOIN cel_export_total ON (ci.ce_observation = co.id_observation) ';
Line 355... Line 355...
355
			}
355
			}
356
 
356
 
Line 361... Line 361...
361
		return $requete;
361
		return $requete;
362
	}
362
	}
Line 363... Line 363...
363
 
363
 
364
	private function construireRequeteNbreObsLiees() {
364
	private function construireRequeteNbreObsLiees() {
365
		$select = 'SELECT COUNT(DISTINCT id_observation) AS nbre ';
365
		$select = 'SELECT COUNT(DISTINCT id_observation) AS nbre ';
366
		$from = 'FROM cel_images ci '.
366
		$from = 'FROM cel_images_export ci '.
Line 367... Line 367...
367
				'	LEFT JOIN cel_obs co ON (ci.ce_observation = co.id_observation) ';
367
				'	LEFT JOIN cel_export_total co ON (ci.ce_observation = co.id_observation) ';
368
 
368
 
Line 369... Line 369...
369
		if (count($this->parametres) != 0) {
369
		if (count($this->parametres) != 0) {
Line 458... Line 458...
458
		$requete = '';
458
		$requete = '';
459
		switch ($critere) {
459
		switch ($critere) {
460
			case 'obs':
460
			case 'obs':
461
				$requete = "SELECT ce_utilisateur , prenom_utilisateur , nom_utilisateur , courriel_utilisateur , ".
461
				$requete = "SELECT ce_utilisateur , prenom_utilisateur , nom_utilisateur , courriel_utilisateur , ".
462
					"	COUNT(*) AS nombreObs ".
462
					"	COUNT(*) AS nombreObs ".
463
					"FROM cel_obs ".
463
					"FROM cel_export_total ".
464
					"WHERE transmission = 1 ".
464
					"WHERE transmission = 1 ".
465
					"AND TO_DAYS(NOW()) - TO_DAYS(date_transmission) <= $jours ".
465
					"AND TO_DAYS(NOW()) - TO_DAYS(date_transmission) <= $jours ".
466
					"GROUP BY ce_utilisateur ".
466
					"GROUP BY ce_utilisateur ".
467
					"ORDER BY nombreObs DESC ".
467
					"ORDER BY nombreObs DESC ".
468
					"LIMIT $nombre ";
468
					"LIMIT $nombre ";
469
				break;
469
				break;
470
			case 'img':
470
			case 'img':
471
				$requete = "SELECT co.ce_utilisateur , co.prenom_utilisateur , co.nom_utilisateur , ".
471
				$requete = "SELECT co.ce_utilisateur , co.prenom_utilisateur , co.nom_utilisateur , ".
472
					"	co.courriel_utilisateur , COUNT(DISTINCT ci.id_image) AS nombreImg ".
472
					"	co.courriel_utilisateur , COUNT(DISTINCT ci.id_image) AS nombreImg ".
473
					"FROM cel_images ci ".
473
					"FROM cel_images_export ci ".
474
					"	RIGHT JOIN cel_obs co ON ci.ce_observation = co.id_observation ".
474
					"	RIGHT JOIN cel_export_total co ON ci.ce_observation = co.id_observation ".
475
					"WHERE co.transmission = 1 ".
475
					"WHERE co.transmission = 1 ".
476
					"AND TO_DAYS(NOW()) - TO_DAYS(co.date_transmission) <= $jours ".
476
					"AND TO_DAYS(NOW()) - TO_DAYS(co.date_transmission) <= $jours ".
477
					"GROUP BY co.ce_utilisateur ".
477
					"GROUP BY co.ce_utilisateur ".
478
					"ORDER BY nombreImg DESC ".
478
					"ORDER BY nombreImg DESC ".
479
					"LIMIT $nombre ";
479
					"LIMIT $nombre ";
480
				break;
480
				break;
481
			default:
481
			default:
482
				$requete = "SELECT co.ce_utilisateur, co.prenom_utilisateur, co.nom_utilisateur, co.courriel_utilisateur, ".
482
				$requete = "SELECT co.ce_utilisateur, co.prenom_utilisateur, co.nom_utilisateur, co.courriel_utilisateur, ".
483
					"COUNT(DISTINCT ci.id_image) AS nombreImg, COUNT(DISTINCT co.id_observation) AS nombreObs, ".
483
					"COUNT(DISTINCT ci.id_image) AS nombreImg, COUNT(DISTINCT co.id_observation) AS nombreObs, ".
484
					"COUNT(DISTINCT ci.id_image) + COUNT(DISTINCT co.id_observation) AS somme ".
484
					"COUNT(DISTINCT ci.id_image) + COUNT(DISTINCT co.id_observation) AS somme ".
485
					"FROM cel_images ci ".
485
					"FROM cel_images_export ci ".
486
					"	RIGHT JOIN cel_obs co ON ci.ce_observation = co.id_observation ".
486
					"	RIGHT JOIN cel_export_total co ON ci.ce_observation = co.id_observation ".
487
					"WHERE co.transmission = 1 ".
487
					"WHERE co.transmission = 1 ".
488
					"AND TO_DAYS(NOW()) - TO_DAYS(co.date_transmission) <= $jours ".
488
					"AND TO_DAYS(NOW()) - TO_DAYS(co.date_transmission) <= $jours ".
489
					"GROUP BY co.ce_utilisateur ".
489
					"GROUP BY co.ce_utilisateur ".
490
					"ORDER BY somme DESC ".
490
					"ORDER BY somme DESC ".
491
					"LIMIT $nombre ";
491
					"LIMIT $nombre ";