WebSVN – eFlore/Applications.cel – Diff – /trunk/jrest/services/CelStatistiqueTxt.php



		return $liste;
	}

 
	private function construireRequeteListeUtilisateurNbrePhoto() {
		$select = 	'SELECT co.courriel_utilisateur, COUNT(DISTINCT ci.id_image) AS nbre ';
		$from = 	'FROM cel_export_total AS co '.
			'	LEFT JOIN cel_images_export 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 ';

		return $liste;
	}

 
	private function construireRequeteListeTaxonNbrePhoto() {
		$select = 	'SELECT nom_ret, COUNT(DISTINCT ci.id_image) AS nbre ';
		$from = 	'FROM cel_export_total co '.
			'	LEFT JOIN cel_images_export 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 ';

		$info = (int) Cel::db()->requeterValeurUnique($requete);
		return $info;
	}

 
	private function construireRequeteNbreObs() {
		$requete = 	'SELECT COUNT(*) AS nbre '.

					'FROM cel_export_total ';
 
		if (count($this->parametres) != 0) {

			$filtres = array();
			extract($this->parametres);
 
			if (isset($utilisateur)) {
				$filtres[] = "courriel_utilisateur = $utilisateur ";
			}
			if (isset($num_taxon)) {
				$filtres[] = "nom_ret_nn = $num_taxon ";
			}

			if (isset($taxon)) {
				$filtres[] = "nom_ret LIKE $taxon ";

			}
 

			$requete .= ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
		}
 
		return $requete;

	}
 
	private function construireRequeteNbreObsPubliques() {

		$requete = 	'SELECT COUNT(*) AS nbre '.
					'FROM cel_export_total '.
					"WHERE transmission = 1 ";
 
		if (count($this->parametres) != 0) {
			$filtres = array();
			extract($this->parametres);
 
			if (isset($utilisateur)) {

				$filtres[] = "courriel_utilisateur = $utilisateur ";
			}

			if (isset($num_taxon)) {
				$filtres[] = "nom_ret_nn = $num_taxon ";

			}
			if (isset($taxon)) {
				$filtres[] = "nom_ret LIKE $taxon ";
			}
 
			$requete .= ((count($filtres) > 0) ? 'AND '.implode(' AND ', $filtres) : '');

		}

		return $requete;
	}

 
	private function construireRequeteNbreImg() {
		$select = 	'SELECT COUNT(DISTINCT ci.id_image) AS nbre ';

		$from = 'FROM cel_images_export ci ';
 

		if (count($this->parametres) != 0) {

			}
			if (isset($taxon)) {
				$filtres[] = "nom_ret LIKE $taxon ";
			}
			if (isset($num_taxon) || isset($taxon)) {
				$from .= 'LEFT JOIN cel_export_total co ON (ci.ce_observation = co.id_observation) ';
			}

 
			$where = ((count($filtres) > 0) ? 'WHERE '.implode(' AND ', $filtres) : '');
		}

		return $requete;
	}

 
	private function construireRequeteNbreImgLiees($publiquesSeulement=false) {
		$select = 'SELECT COUNT(DISTINCT ci.id_image) AS nbre ';

		$from = 'FROM cel_images_export ci ';
 

		if (count($this->parametres) != 0) {

			if ($publiquesSeulement === true) {
				$filtres[] = "ci.transmission = 1";
			}

 
			if (isset($num_taxon) || isset($taxon)) {
				$from .= 'LEFT JOIN cel_export_total ON (ci.ce_observation = co.id_observation) ';

			}
 

		return $requete;
	}

 
	private function construireRequeteNbreObsLiees() {
		$select = 'SELECT COUNT(DISTINCT id_observation) AS nbre ';
		$from = 'FROM cel_images_export ci '.

				'	LEFT JOIN cel_export_total co ON (ci.ce_observation = co.id_observation) ';
 

		if (count($this->parametres) != 0) {

		$requete = '';
		switch ($critere) {
			case 'obs':
				$requete = "SELECT ce_utilisateur , prenom_utilisateur , nom_utilisateur , courriel_utilisateur , ".
					"	COUNT(*) AS nombreObs ".
					"FROM cel_export_total ".
					"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':
				$requete = "SELECT co.ce_utilisateur , co.prenom_utilisateur , co.nom_utilisateur , ".
					"	co.courriel_utilisateur , COUNT(DISTINCT ci.id_image) AS nombreImg ".
					"FROM cel_images_export ci ".
					"	RIGHT JOIN cel_export_total 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:
				$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_export ci ".
					"	RIGHT JOIN cel_export_total 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 ";

Rev 3666	Rev 3834
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(*) 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 ";`

Subversion Repositories eFlore/Applications.cel

(root)/trunk/jrest/services/CelStatistiqueTxt.php – Rev 3666 → 3834