WebSVN – eFlore/Projets.eflore-projets – Diff – /trunk/services/bibliotheque/nom/NomDAO.php


<?php
class NomDAO {
	private $bdd = null;
	private $versions = null;
	private $requeteNbreNomsTotal = null;
 
	public function __construct(Ressources $ressources, Parametres $parametres, Bdd $bdd, Versions $versions) {
		$this->ressources = $ressources;
		$this->parametres = $parametres;
		$this->bdd = $bdd;
		$this->versions = $versions;
	}
 
	public function rechercherInfosNom() {
		$table = $this->getTable();
		$detailsId = $this->ressources->getDetailsId();
		$detailsId = $this->bdd->proteger($detailsId);
		$requete =
				'SELECT ns.*,  '.
				'	nr.nom_sci AS nr_nom_sci, nb.nom_sci AS nb_nom_sci '.
				"FROM $table AS ns ".
				"	LEFT JOIN $table AS nr ON (ns.num_nom_retenu = nr.num_nom) ".
				"	LEFT JOIN $table AS nb ON (ns.basionyme = nb.num_nom) ".
				"WHERE ns.num_nom = $detailsId ";
		$resultats = $this->bdd->recuperer($requete);
		$nom = new NomDO($resultats);
		return $nom;
	}
 
	public function rechercher() {
		$clause = $this->getClauseSelectSpeciale();
		$table = $this->getTable();
		$conditions = $this->getConditions();
		$where = $this->getWhere($conditions);
		$navigation = $this->getNavigation();
 
		$requete = "SELECT $clause ns.*, ".
			'	nr.nom_sci AS nr_nom_sci, nb.nom_sci AS nb_nom_sci '.
			"FROM $table AS ns ".
			"	LEFT JOIN $table AS nr ON (ns.num_nom_retenu = nr.num_nom) ".
			"	LEFT JOIN $table AS nb ON (ns.basionyme = nb.num_nom) ".
			$where.' '.$conditions.' '.
			'ORDER BY ns.nom_sci ASC '.
			"LIMIT $navigation ";
 
		$this->requeteNbreNomsTotal = $this->transformerRequetePourNbreNomsTotal($requete);
		$resultats = $this->bdd->recupererTous($requete);
 
		return $resultats;
	}
 
	public function rechercherFloue() {
		$clause = $this->getClauseSelectSpeciale();
		$table = $this->getTable();
		$masque = $this->parametres->getMasquePourBdd();
		$where = $this->getWhere();
		$navigation = $this->getNavigation();
 
		$requete = "SELECT $clause ns.*, ".
			'	nr.nom_sci AS nr_nom_sci, nb.nom_sci AS nb_nom_sci '.
			"FROM $table AS ns ".
			"	LEFT JOIN $table AS nr ON (ns.num_nom_retenu = nr.num_nom) ".
			"	LEFT JOIN $table AS nb ON (ns.basionyme = nb.num_nom) ".
			$where .
			($masque ? ($where ? ' AND ' : ' WHERE ').
				"	(SOUNDEX(ns.nom_sci) = SOUNDEX($masque)) ".
				"	OR (SOUNDEX(REVERSE(ns.nom_sci)) = SOUNDEX(REVERSE($masque))) " : '').
			'ORDER BY ns.nom_sci ASC '.
			"LIMIT $navigation ";
		$this->requeteNbreNomsTotal = $this->transformerRequetePourNbreNomsTotal($requete);
		$resultats = $this->bdd->recupererTous($requete);
		return $resultats;
	}
 
	private function getClauseSelectSpeciale() {
		$clause = (Config::get('bdd_protocole') == 'mysql') ? 'SQL_CALC_FOUND_ROWS' : '';
		return $clause;
	}
 
	private function getTable() {
		$versions = $this->versions->getVersions();
		$derniereVersion = end($versions);
		$projetNom = strtolower($this->ressources->getProjetNom());
		return $projetNom.'_v'.$derniereVersion;
	}
 
	private function getConditions() {
		$masquesStrictes = array('nn', 'rg');
		$paramsMasque = array(
			'' => 'nom_sci',
			'nn' => 'num_nom',
			'rg' => 'rang',
			'sg' => 'nom_supra_generique',
			'gen' => 'genre',
			'sp' => 'epithete_sp',
			'ssp' => 'epithete_infra_sp',
			'au' => 'auteur',
			'an' => 'annee');
 
		$operateurParDefaut = $this->getOperateurCondition();
		$conditionsSql = array();
		foreach ($paramsMasque as $typeMasque => $champ) {
			$operateur = in_array($typeMasque, $masquesStrictes) ? '=' : $operateurParDefaut;
			if ($valeurMasque = $this->parametres->getMasquePourBdd($typeMasque)) {
				$conditionsSql[] = "ns.$champ $operateur $valeurMasque";
			}
		}
		return implode(' AND ', $conditionsSql);
	}
 
	private function getOperateurCondition() {
		$operateur = '';
		$recherche = $this->parametres->get('recherche');
		if ($recherche == 'stricte') {
			$operateur = '=';
		} else if ($recherche == 'etendue') {
			$operateur = 'LIKE';
		}
		return $operateur;
	}
 
	private function getWhere($conditions = '') {
		$where = '';
		if ($this->ressources->getServiceNom() == 'taxons') {
			$where = 'WHERE ns.num_nom = ns.num_nom_retenu ';
		} else if ($conditions != '') {
			$where = 'WHERE ';
		}
		return $where;
	}
 
	private function getNavigation() {
		$debut = (int) $this->parametres->get('navigation.depart');
		$nbre = $this->parametres->get('navigation.limite');
		$navigation = "$debut,$nbre";
		return $navigation;
	}
 
	private function transformerRequetePourNbreNomsTotal($requete) {
		$requete = preg_replace('/SELECT .* FROM/', 'SELECT COUNT(*) AS nbre FROM', $requete);
		$requete = preg_replace('/LIMIT [0-9]+,[0-9]+/', '', $requete);
		return $requete;
	}
 
	public function recupererNombreNomsTotal() {
		if (Config::get('bdd_protocole') == 'mysql') {
			$requete = 'SELECT FOUND_ROWS() AS nbre';
		} else {
			$requete = $this->requeteNbreNomsTotal;
		}
 
		$nombre = $this->bdd->recuperer($requete);
		return (int) $nombre['nbre'];
	}
}
?>

Rev 216	Rev 220
1	`<?php`	1	`<?php`
2	`class NomDAO {`	2	`class NomDAO {`
3	`private $bdd = null;`	3	`private $bdd = null;`
4	`private $versions = null;`	4	`private $versions = null;`
-		5	`private $requeteNbreNomsTotal = null;`
5		6
6	`public function __construct(Ressources $ressources, Parametres $parametres, Bdd $bdd, Versions $versions) {`	7	`public function __construct(Ressources $ressources, Parametres $parametres, Bdd $bdd, Versions $versions) {`
7	`$this->ressources = $ressources;`	8	`$this->ressources = $ressources;`
8	`$this->parametres = $parametres;`	9	`$this->parametres = $parametres;`
9	`$this->bdd = $bdd;`	10	`$this->bdd = $bdd;`
10	`$this->versions = $versions;`	11	`$this->versions = $versions;`
11	`}`	12	`}`
12		13
13	`public function rechercherInfosNom() {`	14	`public function rechercherInfosNom() {`
14	`$table = $this->getTable();`	15	`$table = $this->getTable();`
15	`$detailsId = $this->ressources->getDetailsId();`	16	`$detailsId = $this->ressources->getDetailsId();`
16	`$detailsId = $this->bdd->proteger($detailsId);`	17	`$detailsId = $this->bdd->proteger($detailsId);`
17	`$requete =`	18	`$requete =`
18	`'SELECT ns.*, '.`	19	`'SELECT ns.*, '.`
19	`' nr.nom_sci AS nr_nom_sci, nb.nom_sci AS nb_nom_sci '.`	20	`' nr.nom_sci AS nr_nom_sci, nb.nom_sci AS nb_nom_sci '.`
20	`"FROM $table AS ns ".`	21	`"FROM $table AS ns ".`
21	`" LEFT JOIN $table AS nr ON (ns.num_nom_retenu = nr.num_nom) ".`	22	`" LEFT JOIN $table AS nr ON (ns.num_nom_retenu = nr.num_nom) ".`
22	`" LEFT JOIN $table AS nb ON (ns.basionyme = nb.num_nom) ".`	23	`" LEFT JOIN $table AS nb ON (ns.basionyme = nb.num_nom) ".`
23	`"WHERE ns.num_nom = $detailsId ";`	24	`"WHERE ns.num_nom = $detailsId ";`
24	`$resultats = $this->bdd->recuperer($requete);`	25	`$resultats = $this->bdd->recuperer($requete);`
25	`$nom = new NomDO($resultats);`	26	`$nom = new NomDO($resultats);`
26	`return $nom;`	27	`return $nom;`
27	`}`	28	`}`
28		29
29	`public function rechercher() {`	30	`public function rechercher() {`
-		31	`$clause = $this->getClauseSelectSpeciale();`
30	`$table = $this->getTable();`	32	`$table = $this->getTable();`
31	`$conditions = $this->getConditions();`	33	`$conditions = $this->getConditions();`
32	`$where = $this->getWhere($conditions);`	34	`$where = $this->getWhere($conditions);`
33	`$navigation = $this->getNavigation();`	35	`$navigation = $this->getNavigation();`
34		36
35	`$requete = 'SELECT SQL_CALC_FOUND_ROWS ns.*, '.`	37	`$requete = "SELECT $clause ns.*, ".`
36	`' nr.nom_sci AS nr_nom_sci, nb.nom_sci AS nb_nom_sci '.`	38	`' nr.nom_sci AS nr_nom_sci, nb.nom_sci AS nb_nom_sci '.`
37	`"FROM $table AS ns ".`	39	`"FROM $table AS ns ".`
38	`" LEFT JOIN $table AS nr ON (ns.num_nom_retenu = nr.num_nom) ".`	40	`" LEFT JOIN $table AS nr ON (ns.num_nom_retenu = nr.num_nom) ".`
39	`" LEFT JOIN $table AS nb ON (ns.basionyme = nb.num_nom) ".`	41	`" LEFT JOIN $table AS nb ON (ns.basionyme = nb.num_nom) ".`
40	`$where.$conditions.`	42	`$where.' '.$conditions.' '.`
41	`'ORDER BY ns.nom_sci ASC '.`	43	`'ORDER BY ns.nom_sci ASC '.`
42	`"LIMIT $navigation ";`	44	`"LIMIT $navigation ";`
43		-
-		45	`$this->requeteNbreNomsTotal = $this->transformerRequetePourNbreNomsTotal($requete);`
44	`$resultats = $this->bdd->recupererTous($requete);`	46	`$resultats = $this->bdd->recupererTous($requete);`
45		47
46	`return $resultats;`	48	`return $resultats;`
47	`}`	49	`}`
48		50
49	`public function rechercherFloue() {`	51	`public function rechercherFloue() {`
-		52	`$clause = $this->getClauseSelectSpeciale();`
50	`$table = $this->getTable();`	53	`$table = $this->getTable();`
51	`$masque = $this->parametres->getMasquePourBdd();`	54	`$masque = $this->parametres->getMasquePourBdd();`
52	`$where = $this->getWhere();`	55	`$where = $this->getWhere();`
53	`$navigation = $this->getNavigation();`	56	`$navigation = $this->getNavigation();`
-		57
54	`$requete = 'SELECT SQL_CALC_FOUND_ROWS ns.*, '.`	58	`$requete = "SELECT $clause ns.*, ".`
55	`' nr.nom_sci AS nr_nom_sci, nb.nom_sci AS nb_nom_sci '.`	59	`' nr.nom_sci AS nr_nom_sci, nb.nom_sci AS nb_nom_sci '.`
56	`"FROM $table AS ns ".`	60	`"FROM $table AS ns ".`
57	`" LEFT JOIN $table AS nr ON (ns.num_nom_retenu = nr.num_nom) ".`	61	`" LEFT JOIN $table AS nr ON (ns.num_nom_retenu = nr.num_nom) ".`
58	`" LEFT JOIN $table AS nb ON (ns.basionyme = nb.num_nom) ".`	62	`" LEFT JOIN $table AS nb ON (ns.basionyme = nb.num_nom) ".`
59	`$where .`	63	`$where .`
60	`($masque ? ($where ? ' AND ' : ' WHERE ').`	64	`($masque ? ($where ? ' AND ' : ' WHERE ').`
61	`" (SOUNDEX(ns.nom_sci) = SOUNDEX($masque)) ".`	65	`" (SOUNDEX(ns.nom_sci) = SOUNDEX($masque)) ".`
62	`" OR (SOUNDEX(REVERSE(ns.nom_sci)) = SOUNDEX(REVERSE($masque))) " : '').`	66	`" OR (SOUNDEX(REVERSE(ns.nom_sci)) = SOUNDEX(REVERSE($masque))) " : '').`
63	`'ORDER BY ns.nom_sci ASC '.`	67	`'ORDER BY ns.nom_sci ASC '.`
64	`"LIMIT $navigation ";`	68	`"LIMIT $navigation ";`
-		69	`$this->requeteNbreNomsTotal = $this->transformerRequetePourNbreNomsTotal($requete);`
65	`$resultats = $this->bdd->recupererTous($requete);`	70	`$resultats = $this->bdd->recupererTous($requete);`
66	`return $resultats;`	71	`return $resultats;`
67	`}`	72	`}`
-		73
-		74	`private function getClauseSelectSpeciale() {`
-		75	`$clause = (Config::get('bdd_protocole') == 'mysql') ? 'SQL_CALC_FOUND_ROWS' : '';`
-		76	`return $clause;`
-		77	`}`
68		78
69	`private function getTable() {`	79	`private function getTable() {`
70	`$versions = $this->versions->getVersions();`	80	`$versions = $this->versions->getVersions();`
71	`$derniereVersion = end($versions);`	81	`$derniereVersion = end($versions);`
72	`$projetNom = strtolower($this->ressources->getProjetNom());`	82	`$projetNom = strtolower($this->ressources->getProjetNom());`
73	`return $projetNom.'_v'.$derniereVersion;`	83	`return $projetNom.'_v'.$derniereVersion;`
74	`}`	84	`}`
75		85
76	`private function getConditions() {`	86	`private function getConditions() {`
77	`$masquesStrictes = array('nn', 'rg');`	87	`$masquesStrictes = array('nn', 'rg');`
78	`$paramsMasque = array(`	88	`$paramsMasque = array(`
79	`'' => 'nom_sci',`	89	`'' => 'nom_sci',`
80	`'nn' => 'num_nom',`	90	`'nn' => 'num_nom',`
81	`'rg' => 'rang',`	91	`'rg' => 'rang',`
82	`'sg' => 'nom_supra_generique',`	92	`'sg' => 'nom_supra_generique',`
83	`'gen' => 'genre',`	93	`'gen' => 'genre',`
84	`'sp' => 'epithete_sp',`	94	`'sp' => 'epithete_sp',`
85	`'ssp' => 'epithete_infra_sp',`	95	`'ssp' => 'epithete_infra_sp',`
86	`'au' => 'auteur',`	96	`'au' => 'auteur',`
87	`'an' => 'annee');`	97	`'an' => 'annee');`
88		98
89	`$operateurParDefaut = $this->getOperateurCondition();`	99	`$operateurParDefaut = $this->getOperateurCondition();`
90	`$conditionsSql = array();`	100	`$conditionsSql = array();`
91	`foreach ($paramsMasque as $typeMasque => $champ) {`	101	`foreach ($paramsMasque as $typeMasque => $champ) {`
92	`$operateur = in_array($typeMasque, $masquesStrictes) ? '=' : $operateurParDefaut;`	102	`$operateur = in_array($typeMasque, $masquesStrictes) ? '=' : $operateurParDefaut;`
93	`if ($valeurMasque = $this->parametres->getMasquePourBdd($typeMasque)) {`	103	`if ($valeurMasque = $this->parametres->getMasquePourBdd($typeMasque)) {`
94	`$conditionsSql[] = "ns.$champ $operateur $valeurMasque";`	104	`$conditionsSql[] = "ns.$champ $operateur $valeurMasque";`
95	`}`	105	`}`
96	`}`	106	`}`
97	`return implode(' AND ', $conditionsSql);`	107	`return implode(' AND ', $conditionsSql);`
98	`}`	108	`}`
99		109
100	`private function getOperateurCondition() {`	110	`private function getOperateurCondition() {`
101	`$operateur = '';`	111	`$operateur = '';`
102	`$recherche = $this->parametres->get('recherche');`	112	`$recherche = $this->parametres->get('recherche');`
103	`if ($recherche == 'stricte') {`	113	`if ($recherche == 'stricte') {`
104	`$operateur = '=';`	114	`$operateur = '=';`
105	`} else if ($recherche == 'etendue') {`	115	`} else if ($recherche == 'etendue') {`
106	`$operateur = 'LIKE';`	116	`$operateur = 'LIKE';`
107	`}`	117	`}`
108	`return $operateur;`	118	`return $operateur;`
109	`}`	119	`}`
110		120
111	`private function getWhere($conditions = '') {`	121	`private function getWhere($conditions = '') {`
112	`$where = '';`	122	`$where = '';`
113	`if ($this->ressources->getServiceNom() == 'taxons') {`	123	`if ($this->ressources->getServiceNom() == 'taxons') {`
114	`$where = 'WHERE ns.num_nom = ns.num_nom_retenu ';`	124	`$where = 'WHERE ns.num_nom = ns.num_nom_retenu ';`
115	`} else if ($conditions != '') {`	125	`} else if ($conditions != '') {`
116	`$where = 'WHERE ';`	126	`$where = 'WHERE ';`
117	`}`	127	`}`
118	`return $where;`	128	`return $where;`
119	`}`	129	`}`
120		130
121	`private function getNavigation() {`	131	`private function getNavigation() {`
122	`$debut = (int) $this->parametres->get('navigation.depart');`	132	`$debut = (int) $this->parametres->get('navigation.depart');`
123	`$nbre = $this->parametres->get('navigation.limite');`	133	`$nbre = $this->parametres->get('navigation.limite');`
124	`$navigation = "$debut,$nbre";`	134	`$navigation = "$debut,$nbre";`
125	`return $navigation;`	135	`return $navigation;`
126	`}`	136	`}`
-		137
-		138	`private function transformerRequetePourNbreNomsTotal($requete) {`
-		139	`$requete = preg_replace('/SELECT .* FROM/', 'SELECT COUNT(*) AS nbre FROM', $requete);`
-		140	`$requete = preg_replace('/LIMIT [0-9]+,[0-9]+/', '', $requete);`
-		141	`return $requete;`
-		142	`}`
127		143
-		144	`public function recupererNombreNomsTotal() {`
128	`public function recupererNombreNomsTotal() {`	145	`if (Config::get('bdd_protocole') == 'mysql') {`
-		146	`$requete = 'SELECT FOUND_ROWS() AS nbre';`
-		147	`} else {`
-		148	`$requete = $this->requeteNbreNomsTotal;`
-		149	`}`
129	`$requete = 'SELECT FOUND_ROWS() AS nbre';`	150
130	`$nombre = $this->bdd->recuperer($requete);`	151	`$nombre = $this->bdd->recuperer($requete);`
131	`return (int) $nombre['nbre'];`	152	`return (int) $nombre['nbre'];`
132	`}`	153	`}`
133	`}`	154	`}`
134	`?>`	155	`?>`

Subversion Repositories eFlore/Projets.eflore-projets

(root)/trunk/services/bibliotheque/nom/NomDAO.php @ 220 – Rev 216 → 220