Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 415 | Rev 433 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
370 mathilde 1
<?php
2
/** Exemple lancement:
3
 * /opt/lampp/bin/php -d memory_limit=3500M ~/web/eflore-projets/scripts/cli.php baseflor -a chargerTous
4
*/
5
class Baseflor extends EfloreScript {
6
 
7
	private $table = null;
8
	private $nb_erreurs;
9
	private $erreurs_ligne;
10
	private $ligne_num;
11
	private $colonne_valeur;
12
	private $colonne_num;
13
	private $type_bio = array(
14
		"A","a","B","b","C","c","Cfru","cfru","Csuf","csuf","Ccou","ccou","H","h","Heri",
15
		"heri","Hsto","hsto","Hces","hces","Hros","hros","Hrub","hrub","Hbis","hbis","G",
16
		"g","Gbul","gbul","Gtub","gtub","Grhi","grhi","T","t","Tver","tver","Test","test");
17
	private $ss_type_bio = array("aqua", "lia", "épi", "hpar", "par", "suc", "semp", "cad", "car");
18
	//les deux suivants basés sur valeurs trouvées (--> pas de légende !)
19
	private $signes_seuls = array("?", "x", "x~", "x=","xb","xB","-");
20
	private $signes_nn_seuls = array("~", "=","b","B");
21
	//paramètres pour lancement de fonctions sur colonnes
22
	private $verifierIntervalle = array(23 => array(1,9), 24 => array(1,9), 25 => array(1,9),
23
		26 => array(1,9), 27 => array(1,12), 28 => array(1,9), 29 => array(1,9), 30 => array(0,9),
24
		31 => array(1,9), 32 => array(1,9), 33 => array(1,9), 34 => array(1,9), 35 => array(1,9),
25
		36 => array(1,12), 37 => array(1,9), 38 => array(1,9), 39 => array(0,9),);
26
	private $verifierColonnesBasiques = array(1 =>"/(^[0-9]*\/)|(inconnu)/", 2 => "/(^[AB]?[0-9]+$)|(^nc$)/",
27
		3 => "/(^[AB]?[0-9]+$)|(^nc$)/", 4 => "/[^0-9]+/", 5 => "/[^0-9]+/", 6 => "/[^0-9]+/", 7 => "/[^0-9]+/",
28
		8 => "/[^0-9]+/", 9 => "/[^0-9]+/", 10 => "/[^0-9]+/", 11 => "/[^0-9]+/", 12 => "/[^0-9]+/",
29
		13 => "/[^0-9]+/", 14 => "/^([1-9]|1[0-2])(\-([1-9]|1[0-2]))*$/", 16 => "/[^0-9]+/", 19 => "/[^0-9]+/",
30
		20 => "/[^0-9]+/", 21 => "/[^0-9]+/", 22 => "/[^0-9]+/",  40 => "/[^0-9]+/",
31
		41 => "/[^0-9]+/", 42 => "/[^0-9]+/", 43 => "/[^0-9]+/", 44 => "/[^0-9]+/", 45 => "/[^0-9]+/",
32
		46 => "/[^0-9]+/", 47 => "/[^0-9]+/", 48 => "/[^0-9]+/", 49 => "/[^0-9]+/", 50 => "/[^0-9]+/",
33
		51 => "/[^0-9]+/", 52 => "/[^0-9]+/", 53 => "/[^0-9]+/", 54 => "/[^0-9]+/", 55 => "/[^0-9]+/"
34
		);
35
 
36
 
37
	public function executer() {
38
		// Lancement de l'action demandée
39
		try {
40
			$this->initialiserProjet('baseflor');
41
			$cmd = $this->getParametre('a');
42
		    switch ($cmd) {
415 mathilde 43
		    	case 'chargerStructureSql' :
370 mathilde 44
	    			$this->chargerStructureSql();
45
	    			break;
46
	    		case 'chargerDonnees' :
47
	    			$this->verifFichier();
383 mathilde 48
	    			if($this->nb_erreurs == 0){
370 mathilde 49
	    				$this->chargerDonnees();
415 mathilde 50
	    				} else {
372 mathilde 51
	    				echo "je ne peux pas charger les données car le fichier comporte des erreurs.
52
	    				Voir le fichier baseflor_verif.txt\n";
370 mathilde 53
	    			}
54
	    			break;
55
				case 'supprimerTous' :
56
					$this->supprimerTous();
57
					break;
58
				case 'chargerOntologies' :
59
					$this->chargerOntologies();
60
					break;
61
				case 'verifierDonnees' :
62
					 $this->verifFichier();
63
					 break;
64
				case 'genererChamps' :
65
					 $this->genererChpsBDNT();
66
					 $this->genererChpsNumTaxon();
67
					 $this->genererChpsNumNomen();
68
					 break;
416 mathilde 69
				case 'chargerMetadonnees':
70
					$this->chargerMetadonnees();
71
					break;
370 mathilde 72
				default :
73
					throw new Exception("Erreur : la commande '$cmd' n'existe pas!");
74
			}
75
		} catch (Exception $e) {
76
			$this->traiterErreur($e->getMessage());
77
		}
78
    }
79
 
416 mathilde 80
 
81
 
370 mathilde 82
   //-- génération de champs --//
415 mathilde 83
    private function genererChamps(){
370 mathilde 84
    	$this->genererChpsBDNT();
85
    	$this->genererChpsNumTaxon();
86
    	$this->genererChpsNumNomen();
87
    }
88
 
89
    private function genererChpsBDNT(){
90
    	$this->initialiserGenerationChamps();
91
    	$this->preparerTablePrChpsBDNT();
92
    	$resultats = $this->recupererTuplesNumsOriginels();
93
    	$bdnt = array();
94
    	foreach ($resultats as $chps => $res) {
95
    		$cle = $res['cle'];
96
    		$nno = $res['num_nomen_originel'];
97
    		$nto = $res['num_taxon_originel'];
98
    		if ( (preg_match("/^([AB])[0-9]+$/", $nno, $retour)) || (preg_match("/^([AB])[0-9]+$/", $nto, $retour)) ){
99
    			if($retour[1]=='A'){
100
    				$bdnt[$cle]= "BDAFX";
101
    			}else{
102
    				$bdnt[$cle]= "BDBFX";
103
    			}
104
 
105
    		}elseif(($nno == 'nc') && ($nto == 'nc')){
106
    			$bdnt[$cle]= "nc";
107
    		}else{
108
    			$bdnt[$cle]= "BDTFX";
109
    		}
110
 
111
    	}
112
    	$this->remplirChamps($bdnt, "BDNT");
113
    }
114
 
115
    private function genererChpsNumTaxon(){
116
    	$this->initialiserGenerationChamps();
117
    	$this->preparerTablePrChpsNumTaxon();
118
    	$resultats = $this->recupererTuplesNumsOriginels();
119
    	$num_taxon= array();
120
    	foreach ($resultats as $chps => $res) {
121
    		$cle = $res['cle'];
122
    	  	$nto = $res['num_taxon_originel'];
123
    		if  (preg_match("/^[AB]([0-9]+)$/", $nto, $retour)){
124
    			$num_taxon[$cle]= intval($retour[1]);
125
    		}elseif($nto == 'nc'){
126
    			$num_taxon[$cle]= 0;
127
    		}else{
128
    			$num_taxon[$cle]= intval($nto);
129
    		}
130
 
131
    	}
132
    	$this->remplirChamps($num_taxon, "num_taxon");
133
    }
134
 
135
    private function genererChpsNumNomen(){
136
    	$this->initialiserGenerationChamps();
137
    	$this->preparerTablePrChpsNumNomen();
138
    	$resultats = $this->recupererTuplesNumsOriginels();
139
    	$num_nomen= array();
140
    	foreach ($resultats as $chps => $res) {
141
    		$cle = $res['cle'];
142
    		$nno = $res['num_nomen_originel'];
143
    		if  (preg_match("/^[AB]([0-9]+)$/", $nno, $retour)){
144
				$num_nomen[$cle]= intval($retour[1]);
145
    		}elseif($nno == 'nc'){
146
    			$num_nomen[$cle]= 0;
147
    		}else{
148
    			$num_nomen[$cle]=intval($nno);
149
    		}
150
    	}
151
    	$this->remplirChamps($num_nomen, "num_nomen");
152
    }
153
 
154
     private function remplirChamps($champs, $nom_colonne) {
155
    	foreach ($champs as $cle => $val) {
156
    		$val = $this->getBdd()->proteger($val);
157
    		$requete = "UPDATE {$this->table} SET $nom_colonne = $val WHERE cle = $cle ";
158
    		$resultat = $this->getBdd()->requeter($requete);
159
    		if ($resultat === false) {
160
    			throw new Exception("Erreur d'insertion pour le tuple clé = ".$cle);
161
    		}
162
    		$this->afficherAvancement("Insertion des valeurs pour ".$nom_colonne." dans la base en cours");
163
    	}
164
    	echo "\n";
165
    }
166
 
167
 
168
 
169
 
170
    private function initialiserGenerationChamps() {
171
    	$this->table = Config::get('tables.donnees');
172
    }
173
 
174
    private function preparerTablePrChpsBDNT() {
175
    	$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'BDNT' ";
176
    	$resultat = $this->getBdd()->recuperer($requete);
177
    	if ($resultat === false) {
178
    		$requete = 	"ALTER TABLE {$this->table} ".
179
    				'ADD BDNT VARCHAR( 6 ) '.
180
    				'CHARACTER SET utf8 COLLATE utf8_general_ci'.
181
    				' NOT NULL AFTER catminat_code ';
182
    		$this->getBdd()->requeter($requete);
183
    	}
184
    }
185
 
186
    private function preparerTablePrChpsNumTaxon() {
187
    	$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'num_taxon' ";
188
    	$resultat = $this->getBdd()->recuperer($requete);
189
    	if ($resultat === false) {
190
    		$requete = 	"ALTER TABLE {$this->table} ".
191
        				'ADD num_taxon INT( 10 ) NOT NULL'.
383 mathilde 192
        				' AFTER catminat_code';
370 mathilde 193
    		$this->getBdd()->requeter($requete);
194
    	}
195
    }
196
 
197
    private function preparerTablePrChpsNumNomen() {
198
    	$requete = "SHOW COLUMNS FROM {$this->table} LIKE 'num_nomen' ";
199
    	$resultat = $this->getBdd()->recuperer($requete);
200
    	if ($resultat === false) {
201
    		$requete = 	"ALTER TABLE {$this->table} ".
202
            				'ADD num_nomen INT( 10 ) NOT NULL'.
383 mathilde 203
            				' AFTER catminat_code';
370 mathilde 204
    		$this->getBdd()->requeter($requete);
205
    	}
206
    }
207
    private function recupererTuplesNumsOriginels(){
208
    	$requete = 'SELECT cle, num_taxon_originel, num_nomen_originel '.
209
    					"FROM {$this->table} ";
210
    	$resultat = $this->getBdd()->recupererTous($requete);
211
    	return $resultat;
212
    }
213
 
214
    //-- chargements, suppression, exécution  --//
215
 
416 mathilde 216
    protected function chargerMetadonnees() {
217
    	$contenuSql = $this->recupererContenu(Config::get('chemins.metadonnees'));
218
    	$this->executerScripSql($contenuSql);
219
    }
220
 
370 mathilde 221
    private function chargerOntologies() {
222
    	$chemin = Config::get('chemins.ontologies');
223
    	$table = Config::get('tables.ontologies');
224
    	$requete = "LOAD DATA INFILE '$chemin' ".
225
    			"REPLACE INTO TABLE $table ".
226
    			'CHARACTER SET utf8 '.
227
    			'FIELDS '.
228
    			"	TERMINATED BY '\t' ".
229
    			"	ENCLOSED BY '' ".
230
    			"	ESCAPED BY '\\\' "
231
    			;
232
    	$this->getBdd()->requeter($requete);
233
    }
234
 
235
    protected function chargerStructureSql() {
236
    	$contenuSql = $this->recupererContenu(Config::get('chemins.structureSql'));
415 mathilde 237
     	$this->executerScripSql($contenuSql);
238
 
370 mathilde 239
    }
240
 
241
 
242
    protected function executerScripSql($sql) {
243
    	$requetes = Outils::extraireRequetes($sql);
244
    	foreach ($requetes as $requete) {
245
    		$this->getBdd()->requeter($requete);
246
    	}
247
    }
248
 
249
    private function chargerDonnees() {
250
    	$table = Config::get('tables.donnees');
251
    	$requete = "LOAD DATA INFILE '".Config::get('chemins.donnees')."' ".
252
        				"REPLACE INTO TABLE $table ".
253
        				'CHARACTER SET utf8 '.
254
        				'FIELDS '.
255
        				"	TERMINATED BY '\t' ".
256
        				"	ENCLOSED BY '' ".
257
        				"	ESCAPED BY '\\\'";
258
    	$this->getBdd()->requeter($requete);
259
    }
260
 
261
    private function supprimerTous() {
415 mathilde 262
    	$requete = "DROP TABLE IF EXISTS baseflor_meta, baseflor_ontologies, baseflor_v2012_03_19";
370 mathilde 263
    	$this->getBdd()->requeter($requete);
264
    }
265
 
266
 
267
    //-- vérifications de données  --//
268
 
269
    private function verifierSiVide(){
270
    	$bool=false;
271
    	if ( $this->colonne_valeur  == "" ){
272
    		$bool=true;
273
    	}
274
    	return $bool;
275
    }
276
 
277
 
278
    private function verifierIntervallesTous($valeur){
279
    	$intervalle=$this->verifierIntervalle[$this->colonne_num];
280
      	$this->verifierIntervalleChiffres($intervalle[0], $intervalle[1],$valeur);
281
    }
282
 
283
 
284
    private function verifierIntervalleChiffres($mini, $max, $valeur){
285
    	 if (( ($valeur < $mini) || ($valeur > $max) ) && ($valeur != "")){
286
    		$this->erreurs_ligne[$this->colonne_num] = $this->colonne_valeur;
287
   			$this->nb_erreurs++;
288
    	}
289
 
290
    }
291
 
292
 
293
    private function verifierValeursIndic(){
294
    	if (( preg_match("/^([^0-9])*([0-9]+)([^0-9])*$/", $this->colonne_valeur,$retour) == 1  )){
295
    		$this->verifierIntervallesTous($retour[2]);
296
    		if((isset($retour[3]))&&(!(in_array($retour[3], $this->signes_nn_seuls))) ){
297
    			$this->erreurs_ligne[$this->colonne_num] =$this->colonne_valeur;
298
    			$this->nb_erreurs++;
299
    		}
300
    		if(($retour[1] != "-")&&($retour[1]!="") ){
301
    			$this->erreurs_ligne[$this->colonne_num] =$this->colonne_valeur;
302
    			$this->nb_erreurs++;
303
    			if($this->ligne_num==3){
304
    			}
305
    		}
306
    	}elseif(!(in_array( $this->colonne_valeur, $this->signes_seuls))&& !($this->verifierSiVide())) {
307
    			$this->erreurs_ligne[$this->colonne_num] = $this->colonne_valeur;
308
    			$this->nb_erreurs++;
309
    		}
310
    }
311
 
312
 private function verifierTypeBio(){
313
 	if (( preg_match("/(.+)\((.+)\)$/", $this->colonne_valeur,$retour) == 1  )){
314
 		$this->verifType_SsType($retour[1]);
315
 		$this->verifType_SsType($retour[2]);
316
 	}else{
317
 		$this->verifType_SsType($this->colonne_valeur);
318
 	}
319
 }
320
 
321
    private function verifType_SsType($chaine_a_verif){
322
    	//verifie type
323
    	if(( preg_match("/^([a-zA-Zé]+)\-(.+)$|^([a-zA-Zé]+[^\-])$/", $chaine_a_verif, $retour) == 1  )){
324
    		$type=$retour[1];
325
    		if(isset($retour[3])){
326
    			$type=$retour[3];
327
    		}
328
    		 if(!(in_array($type, $this->type_bio))) {
329
    			$this->erreurs_ligne[$this->colonne_num] = $this->colonne_valeur;
330
    			$this->nb_erreurs++;
331
    		}
332
    		//verifie sous_type(s)
333
    		if ($retour[2]!=""){
334
    			$ss_type[0]=$retour[2];
335
    			if (( preg_match("/\-/", $ss_type[0])) == 1 ){
336
    				$ss_type = preg_split("/\-/", $retour[2]);
337
    			}
338
    			foreach($ss_type as $sst){
339
    				if(!(in_array($sst, $this->ss_type_bio))) {
340
    					$this->erreurs_ligne[$this->colonne_num] = $this->colonne_valeur;
341
    					$this->nb_erreurs++;
342
    				}
343
    			}
344
    		}
345
    	}
346
    }
347
 
348
    private function verifierColonne($chaine){
349
    	if ( ( preg_match($chaine, $this->colonne_valeur) == 0 ) && !($this->verifierSiVide()) ){
350
    		$this->erreurs_ligne[$this->colonne_num]=$this->colonne_valeur;
351
    		$this->nb_erreurs++;
352
    	}
353
    }
354
 
355
    //vérifie par colonnes les erreurs d'une ligne
356
   private function verifErreursLignes($colonne_array){
357
   	$this->erreurs_ligne=array();
358
     for ($this->colonne_num = 0; $this->colonne_num < count($colonne_array); $this->colonne_num++){
359
     	 if ( isset($colonne_array) ){
360
     	 	$this->colonne_valeur = $colonne_array[$this->colonne_num];
361
   			if ( (( ($this->colonne_num > 0) && ($this->colonne_num < 15) ) || ( $this->colonne_num == 16 )
362
   				|| (($this->colonne_num > 18 ) && ( $this->colonne_num < 23 ) )
363
   				||  ( $this->colonne_num > 39 ))  ){
364
   				$motif = $this->verifierColonnesBasiques[$this->colonne_num];
365
   			  	$this->verifierColonne($motif);
366
   			}elseif ( $this->colonne_num == 15 ){
367
   				$this->verifierTypeBio();
368
   			} elseif (($this->colonne_num > 22 ) && ( $this->colonne_num < 33 )){
369
   				$this->verifierIntervallesTous($this->colonne_valeur);
370
   			}elseif (( $this->colonne_num > 32) && ( $this->colonne_num < 41)){
371
   				  $this->verifierValeursIndic();
372
   			}
373
   		} else {
374
   			echo "ligne ".$this->ligne_num.": pas de tabulation  \n ";
375
   		}
376
   }
377
 
378
}
379
 
380
    //verifie la cohérence des valeurs des colonnes
381
    private function verifFichier(){
383 mathilde 382
    	$dernier_nb_erreur = 0;
373 mathilde 383
    	if (file_exists(Config::get('chemins.donnees_verif'))) {
384
    		unlink(Config::get('chemins.donnees_verif'));
385
    	}
370 mathilde 386
    	$fichier = fopen(Config::get('chemins.donnees'), "r");
387
    	$fichier2 = fopen(Config::get('chemins.donnees_verif'), "w");
388
    	$this->valeurs=array();
389
    	$this->nb_erreurs=0;
390
    	if ( ($fichier != false) && ($fichier2 != false) ){
383 mathilde 391
    		$this->ligne_num=0;
370 mathilde 392
    		while ( !feof($fichier) ) {
383 mathilde 393
    			if ($this->ligne_num == 0){
370 mathilde 394
    				fputs($fichier2," !!! REGARDEZ LES COLONNES DANS NUMERO_COLONNES_IMPORTANT.TXT.\n");
395
    			}
396
    			$this->ligne_num++;
397
    			$ligne = fgets($fichier);
398
    			$col = preg_split("/	/", $ligne);
399
    			$this->verifErreursLignes($col);
400
    			//rédaction du fichier de vérif
401
    			if(count($this->nb_erreurs)!= 0){
402
    				if($dernier_nb_erreur != $this->nb_erreurs){
403
    					$dernier_nb_erreur = $this->nb_erreurs;
404
    					fputs($fichier2," \n erreurs sur la ligne".$this->ligne_num."\n");
405
    					foreach ($this->erreurs_ligne as $cle => $v){
406
    						fputs($fichier2,"colonne ".$cle." : ".$v." - ");
407
    					}
408
    				}
409
    			}
410
    		}
411
    	}
412
    	if ($this->nb_erreurs == 0){
413
    		fputs($fichier2,"/n il n'y a pas d'erreurs./n");
414
    	}
415
    	echo $this->nb_erreurs."erreurs";
416
    	fclose($fichier);
417
    	fclose($fichier2);
418
    }
419
 
420
 
421
 
422
}
423
 
424
?>