Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 722 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 722 Rev 775
Line 1... Line 1...
1 
<?php
 1 
<?php
- 
 
 2 
/** Corriger les codes langues pays genre et nombre à la main
2 
/** Exemple lancement:
 3 
 * Exemple lancement:
3 
 * /opt/lampp/bin/php -d memory_limit=3500M ~/web/eflore-projets/scripts/cli.php nva -a chargerTous
 4 
 * /opt/lampp/bin/php -d memory_limit=3500M ~/web/eflore-projets/scripts/cli.php nva -a chargerTous
4 
*/
 5 
*/
5 
class Nva extends EfloreScript {
 6 
class Nva extends EfloreScript {
Line 6... Line 7...
6
7
Line 11... Line 12...
11 
 
 12 
 
12 
			$cmd = $this->getParametre('a');
 13 
			$cmd = $this->getParametre('a');
13 
		    switch ($cmd) {
 14 
		    switch ($cmd) {
14 
		    	case 'chargerTous' :
 15 
		    	case 'chargerTous' :
15 
		    		$this->chargerStructureSql();
 16 
		    		$this->chargerStructureSql();
16 
		    		$this->chargerDonnees();
 17 
		    		$this->chargerDonnees(Config::get('chemins.nva'), Config::get('tables.nva'));
17 
		    		$this->genererChampCodeLangue();
 18 
		    		$this->chargerDonnees(Config::get('chemins.index'), Config::get('tables.index'));
- 
 
 19 
		    		$this->ajouterChampNomVernaIndex();
18 
		    		$this->genererChampNumTax();
 20 
		    		$this->chargerDonnees(Config::get('chemins.ontologies'), Config::get('tables.ontologies'));
19 
		    		break;
 21 
		    		break;
20 
	    		case 'chargerStructureSql' :
 22 
	    		case 'chargerStructureSql' :
21 
	    			$this->chargerStructureSql();
 23 
	    			$this->chargerStructureSql();
22 
	    			break;
 24 
	    			break;
23 
				case 'chargerDonnees' :
 25 
				case 'chargerDonnees' :
24 
					$this->chargerDonnees();
 26 
					$this->chargerDonnees();
25 
					break;
 - 
 
26 
				case 'genererChampNumTax' :
 - 
 
27 
					$this->genererChampNumTax();
 - 
 
28 
					break;
 27 
					break;
29 
				case 'genererChampCodeLangue' :
 28 
				case 'ajouterChampNomVernaIndex' :
30 
					$this->genererChampCodeLangue();
 29 
					$this->ajouterChampNomVernaIndex();
31 
					break;
 30 
					break;
32 
				case 'supprimerTous' :
 31 
				case 'supprimerTous' :
33 
					$this->supprimerTous();
 32 
					$this->supprimerTous();
34 
					break;
 33 
					break;
Line 38... Line 37...
38 
		} catch (Exception $e) {
 37 
		} catch (Exception $e) {
39 
			$this->traiterErreur($e->getMessage());
 38 
			$this->traiterErreur($e->getMessage());
40 
		}
 39 
		}
41 
    }
 40 
    }
Line 42... Line 41...
42 
 
 41 
 
43 
	private function chargerDonnees() {
 - 
 
44 
		$chemin = Config::get('chemins.nva');
 - 
 
45 
		$table = Config::get('tables.nva');
 42 
	private function chargerDonnees($chemin, $table) {
46 
		$requete = "LOAD DATA INFILE '$chemin' ".
 43 
		$requete = "LOAD DATA INFILE '$chemin' ".
47 
			"REPLACE INTO TABLE $table ".
 44 
			"REPLACE INTO TABLE $table ".
48 
			'CHARACTER SET utf8 '.
 45 
			'CHARACTER SET utf8 '.
49 
			'FIELDS '.
 46 
			'FIELDS '.
Line 52... Line 49...
52 
			"	ESCAPED BY '\\\' ".
 49 
			"	ESCAPED BY '\\\' ".
53 
			'IGNORE 1 LINES';
 50 
			'IGNORE 1 LINES';
54 
		$this->getBdd()->requeter($requete);
 51 
		$this->getBdd()->requeter($requete);
55 
	}
 52 
	}
Line 56... Line 53...
56
53
57 
	private function genererChampNumTax() {
 54 
	private function ajouterChampNomVernaIndex() {
58 
		$this->preparerTablePrChpNumTax();
 - 
 
59 
		$nns_taxons = $this->recupererTuplesPrNumTax();
 55 
		$this->preparerTablePrChpNomVerna();
60 
		$this->remplirChpNumTax($nns_taxons);
 56 
		$this->remplirChpNomVerna();
Line 61... Line 57...
61 
	}
 57 
	}
62
58
63 
	private function preparerTablePrChpNumTax() {
 59 
	private function preparerTablePrChpNomVerna() {
64 
		$table = Config::get('tables.nva');
 60 
		$table = Config::get('tables.index');
65 
		$requete = "SHOW COLUMNS FROM $table LIKE 'num_taxon' ";
 61 
		$requete = "SHOW COLUMNS FROM $table LIKE 'nom_vernaculaire' ";
66 
		$resultat = $this->getBdd()->recuperer($requete);
 62 
		$resultat = $this->getBdd()->recuperer($requete);
- 
 
 63 
		if ($resultat === false) {
67 
		if ($resultat === false) {
 64 
			$requete = 	"ALTER TABLE $table ".
- 
 
 65 
					'ADD `nom_vernaculaire` VARCHAR( 40 ) NOT NULL ,
68 
			$requete = 	"ALTER TABLE $table ".
 66 
					ADD `code_langue` VARCHAR( 10 ) NOT NULL ,
69 
					'ADD num_taxon INT(10) ';
 67 
					ADD `num_genre` INT( 1 ), ADD `num_nombre` INT( 1 ) ';
70 
			$this->getBdd()->requeter($requete);
 68 
			$this->getBdd()->requeter($requete);
- 
 
 69 
		}
Line 71... Line 70...
71 
		}
 70 
	}
72 
	}
 71 
 
73
72
74 
	private function recupererTuplesPrNumTax() {
 - 
 
75 
		$table_ref = Config::get('tables.bdtxa');
 - 
 
76 
		$requete = 'SELECT  num_tax, num_nom '.
 - 
 
77 
					"FROM ".$table_ref." ";
 - 
 
78 
		$resultat = $this->getBdd()->recupererTous($requete);
 - 
 
79 
		return $resultat;
 - 
 
80 
	}
 - 
 
81
- 
 
82 
	private function remplirChpNumTax($nns_taxons) {
 73 
	private function remplirChpNomVerna() {
83 
		$table = Config::get('tables.nva');
 74 
		$table = Config::get('tables.index');
84 
		foreach ($nns_taxons as $nn) {
 - 
 
85 
			if (!empty($nn['num_tax']) && is_numeric($nn['num_tax'])) {
 - 
 
86 
				$requete = "UPDATE $table SET num_taxon = ".$nn['num_tax']." WHERE num_nom = ".$nn['num_nom']." ";
 - 
 
87 
				$resultat = $this->getBdd()->requeter($requete);
 75 
		$requete = "UPDATE `nva_index_v2_03`
88 
			}
 - 
 
89 
			if ($resultat === false) {
 76 
			SET nom_vernaculaire=(select n.nom_vernaculaire from nva_v2_03 n where n.num_nom_vernaculaire=nva_index_v2_03.num_nom_vernaculaire),
90 
				throw new Exception("Erreur d'insertion pour le tuple $id");
 - 
 
91 
			}
 - 
 
92 
			$this->afficherAvancement("Insertion des nums taxonomiques dans la base en cours");
 - 
 
93 
		}
 - 
 
94 
		echo "\n";
 - 
 
95 
	}
 - 
 
96
- 
 
97 
	private function genererChampCodeLangue() {
 - 
 
98 
		$this->preparerTablePrChpCodeLangue();
 - 
 
99 
		$codes_langues = $this->recupererTuplesPrCodeLangue();
 - 
 
100 
		$this->remplirChpCodeLangue($codes_langues);
 - 
 
101 
	}
 - 
 
102
- 
 
103 
	private function preparerTablePrChpCodeLangue() {
 77 
			code_langue=(select n.code_langue from nva_v2_03 n where n.num_nom_vernaculaire=nva_index_v2_03.num_nom_vernaculaire),
104 
		$table = Config::get('tables.nva');
 78 
			num_genre=(select n.num_genre from nva_v2_03 n where n.num_nom_vernaculaire=nva_index_v2_03.num_nom_vernaculaire),
105 
		$requete = "SHOW COLUMNS FROM $table LIKE 'code_langue' ";
 - 
 
106 
		$resultat = $this->getBdd()->recuperer($requete);
 - 
 
107 
		if ($resultat === false) {
 - 
 
108 
			$requete = 	"ALTER TABLE $table ".
 - 
 
109 
						'ADD code_langue VARCHAR(3) ';
 - 
 
110 
			$this->getBdd()->requeter($requete);
 - 
 
111 
		}
 - 
 
112 
	}
 - 
 
113
- 
 
114 
	private function recupererTuplesPrCodeLangue() {
 - 
 
115 
		$codes_langues = array();
 - 
 
116 
		if (($handle = fopen(Config::get("dossierTsv")."codes_langues.csv", "r")) !== false) {
 - 
 
117 
		    while (($data = fgetcsv($handle, 1000, ",")) !== false) {
 - 
 
118 
		    	$codes_langues[] = array('code_iso_639_3' => $data[0],
 - 
 
119 
		    						'nom' => $data[1]);
 - 
 
120 
		    }
 - 
 
121 
		    fclose($handle);
 - 
 
122 
		}
 - 
 
123
- 
 
124 
		return $codes_langues;
 - 
 
125 
	}
 - 
 
126
- 
 
127 
	private function remplirChpCodeLangue($codes_langues) {
 - 
 
128 
		$table = Config::get('tables.nva');
 - 
 
129 
		foreach ($codes_langues as $code) {
 - 
 
130 
			if (!empty($code['nom'])) {
 - 
 
131 
				$requete = "UPDATE $table SET code_langue = ".$this->getBdd()->proteger($code['code_iso_639_3'])." ".
 - 
 
132 
						   "WHERE TRIM(langue) = ".$this->getBdd()->proteger(trim($code['nom']))." ";
 - 
 
133 
				$resultat = $this->getBdd()->requeter($requete);
 79 
			num_nombre=(select n.num_nombre from nva_v2_03 n where n.num_nom_vernaculaire=nva_index_v2_03.num_nom_vernaculaire)";
134 
			}
 - 
 
135 
			if ($resultat === false) {
 - 
 
136 
				throw new Exception("Erreur d'insertion pour le tuple $id");
 80 
		$resultat = $this->getBdd()->requeter($requete);
- 
 
 81 
		if ($resultat === false) {
137 
			}
 82 
			throw new Exception("Erreur d'update d'ajouts des noms");
138 
			$this->afficherAvancement("Insertion des codes de langues dans la base en cours");
 83 
		}
- 
 
 84 
		$this->afficherAvancement("Ajout des noms vernaculaires à l'index");
- 
 
 85 
		echo "\n";
- 
 
 86 
	}
Line 139... Line 87...
139 
		}
 87
140 
		echo "\n";
 88 
 
- 
 
 89 
 
141 
	}
 90 
 
142 
 
 91 
	private function supprimerTous() {
143 
	private function supprimerTous() {
 92 
		$requete = "DROP TABLE IF EXISTS ".Config::get('tables.nva').", ".Config::get('tables.index').
144 
		$requete = "DROP TABLE IF EXISTS ".Config::get('tables.nva').", nva_meta ";
 93 
					", ".Config::get('tables.ontologies').", nva_meta ";
145 
		$this->getBdd()->requeter($requete);
 94 
		$this->getBdd()->requeter($requete);