WebSVN – eFlore/Applications.cel – Diff – /trunk/scripts/modules/nettoyage/Nettoyage.php



	}

	
	private function supprimerAntislash() {
		foreach ($this->colonnes as $id=>$colonne) {
			echo "Suppression des antislash dans la colonne ".$colonne['column_name']." ...";
			$requete = "UPDATE ".$this->bd_cel.".cel_obs SET ".$colonne['column_name']." = REPLACE(".$colonne['column_name'].', "\\", "")';
			$this->bdd->requeter($requete);
			echo "effectuée \n";
		}

	
	private function unifierCertitude() {
		echo "Uniformisation du champ certitude/identification";
		$certitude = array("A déterminer" => "à déterminer",
		    "À déterminer, voir photo" => "à déterminer",
		    "A déterminer" => "à déterminer",
		    "aDeterminer" => "à déterminer",
		    "déterminer" => "à déterminer",
		    "Inconnue" => "à déterminer",
		    "Ceraine" => "certain",

		    "subsp à vérifier" => "douteux",
		    "subsp. microphyllum ?" => "douteux",
		    "très douteuse" => "douteux",
		    "Très probable" => "douteux");
		foreach ($certitude as $valeur=>$saisie) {
			$requete = 'UPDATE '.$this->bd_cel.'.cel_obs SET commentaire = concat(commentaire, " Certitude : ", certitude), certitude = "'.$saisie.'" WHERE certitude ="'.$valeur.'"';
			$this->bdd->requeter($requete);
			echo "effectuée \n";
		}
		$requete_verif = "SELECT certitude FROM ".$this->bd_cel.".cel_obs WHERE certitude NOT IN ('douteux', 'certain', 'à déterminer', '') and certitude is not NULL";
		$verif = $this->bdd->recupererTous($requete_verif);

	    $requete = "UPDATE ".$this->bd_cel.".cel_obs 
	        SET commentaire = case isnull(commentaire) when 1 THEN certitude ELSE concat(commentaire, ' Certitude : ', certitude) END, 
	        certitude = '' WHERE `certitude` NOT IN ('douteux', 'certain', 'à déterminer', '') and certitude is not NULL";
	    $this->bdd->requeter($requete);
	        echo "effectuée \n";
	    $requete_verif = "SELECT certitude FROM ".$this->bd_cel.".cel_obs WHERE certitude NOT IN ('douteux', 'certain', 'à déterminer', '') and certitude is not NULL";
	    $verif = $this->bdd->recupererTous($requete_verif);
	    if ($verif != array()) print_r($verif);
	}

	

		echo "Uniformisation du nom de référentiel";
		$referentiels = array("BDTFX", "BDTRE", "BDTXA", "APD", "LBF", "ISFAN");
		foreach ($referentiels as $referentiel) {
			$requete = "UPDATE ".$this->bd_cel.".cel_obs SET nom_referentiel = '".$referentiel."' WHERE nom_referentiel like '".$referentiel."%'";
			$this->bdd->requeter($requete);
			$requete = "UPDATE ".$this->bd_cel.".cel_obs SET nom_referentiel = 'autre' WHERE nom_sel_nn IS NULL or nom_sel_nn = '0'";
			$this->bdd->requeter($requete);
			$requete = "UPDATE ".$this->bd_cel.".cel_obs SET nom_referentiel = 'APD' WHERE nom_referentiel like 'bdtao%'";
			$this->bdd->requeter($requete);
			$requete = "UPDATE ".$this->bd_cel.".cel_obs SET nom_referentiel = 'BDTFX' WHERE nom_referentiel like 'bdnff%'";
			$this->bdd->requeter($requete);

	    $requete = "UPDATE ".$this->bd_cel.".`cel_obs` c left join 
	        (SELECT distinct `ce_observation`, `nom_sel_nn` 
	        FROM ".$this->bd_del.".`del_commentaire` WHERE `proposition_retenue` = 1) d on 
	        `ce_observation` = `id_observation` SET `certitude` = 'certain' 
	        WHERE (c.`mots_cles_texte` LIKE '%adeterminer%' 
	        or c.`certitude` = 'à déterminer') and c.`nom_sel_nn` = d.`nom_sel_nn`";
	    $this->bdd->requeter($requete);

	    
	    echo "Certitude à douteux quand obs validée sur ip mais num_nom diff entre ip et cel \n";
	    $requete = "UPDATE ".$this->bd_cel.".`cel_obs` c left join
	        (SELECT distinct `ce_observation`, `nom_sel_nn`
	        FROM ".$this->bd_del.".`del_commentaire`  WHERE `proposition_retenue` = 1 ) d on
	        `ce_observation` = `id_observation` SET `certitude` = 'douteux'
	        WHERE (c.`mots_cles_texte` LIKE '%adeterminer%'
	        or c.`certitude` = 'à déterminer') and c.`nom_sel_nn` != d.`nom_sel_nn`";

	    $this->bdd->requeter($requete);
	    
	    echo "Certitude à aDeterminer quand obs non validée sur ip \n";
	    $requete = "UPDATE ".$this->bd_cel.".`cel_obs` c SET `certitude` = 'à déterminer'
	        WHERE c.`mots_cles_texte` LIKE '%adeterminer%' AND 

	        (c.`certitude` = '' or c.certitude IS NULL)";

Subversion Repositories eFlore/Applications.cel

(root)/trunk/scripts/modules/nettoyage/Nettoyage.php – Rev 3445 → 3446

Rev 3445		Rev 3446
Line 127...		Line 127...
127	`}`	127	`}`
Line 128...		Line 128...
128		128
129	`private function supprimerAntislash() {`	129	`private function supprimerAntislash() {`
130	`foreach ($this->colonnes as $id=>$colonne) {`	130	`foreach ($this->colonnes as $id=>$colonne) {`
131	`echo "Suppression des antislash dans la colonne ".$colonne['column_name']." ...";`	131	`echo "Suppression des antislash dans la colonne ".$colonne['column_name']." ...";`
132	`$requete = "UPDATE ".$this->bd_cel.".cel_obs SET ".$colonne['column_name']." = REPLACE(".$colonne['column_name'].', "\", "")';`	132	`$requete = "UPDATE ".$this->bd_cel.".cel_obs SET ".$colonne['column_name']." = REPLACE(".$colonne['column_name'].', "\\", "")';`
133	`$this->bdd->requeter($requete);`	133	`$this->bdd->requeter($requete);`
134	`echo "effectuée \n";`	134	`echo "effectuée \n";`
135	`}`	135	`}`
Line 162...		Line 162...
162		162
163	`private function unifierCertitude() {`	163	`private function unifierCertitude() {`
164	`echo "Uniformisation du champ certitude/identification";`	164	`echo "Uniformisation du champ certitude/identification";`
165	`$certitude = array("A déterminer" => "à déterminer",`	165	`$certitude = array("A déterminer" => "à déterminer",`
-		166	`"À déterminer, voir photo" => "à déterminer",`
166	`"À déterminer, voir photo" => "à déterminer",`	167	`"A déterminer" => "à déterminer",`
167	`"aDeterminer" => "à déterminer",`	168	`"aDeterminer" => "à déterminer",`
168	`"déterminer" => "à déterminer",`	169	`"déterminer" => "à déterminer",`
169	`"Inconnue" => "à déterminer",`	170	`"Inconnue" => "à déterminer",`
170	`"Ceraine" => "certain",`	171	`"Ceraine" => "certain",`
Line 219...		Line 220...
219	`"subsp à vérifier" => "douteux",`	220	`"subsp à vérifier" => "douteux",`
220	`"subsp. microphyllum ?" => "douteux",`	221	`"subsp. microphyllum ?" => "douteux",`
221	`"très douteuse" => "douteux",`	222	`"très douteuse" => "douteux",`
222	`"Très probable" => "douteux");`	223	`"Très probable" => "douteux");`
223	`foreach ($certitude as $valeur=>$saisie) {`	224	`foreach ($certitude as $valeur=>$saisie) {`
224	`$requete = 'UPDATE ".$this->bd_cel.".cel_obs SET commentaire = concat(commentaire, " Certitude : ", certitude), certitude = "'.$saisie.'" WHERE certitude ="'.$valeur.'"';`	225	`$requete = 'UPDATE '.$this->bd_cel.'.cel_obs SET commentaire = concat(commentaire, " Certitude : ", certitude), certitude = "'.$saisie.'" WHERE certitude ="'.$valeur.'"';`
225	`$this->bdd->requeter($requete);`	226	`$this->bdd->requeter($requete);`
226	`echo "effectuée \n";`	227	`echo "effectuée \n";`
227	`}`	228	`}`
228	`$requete_verif = "SELECT certitude FROM ".$this->bd_cel.".cel_obs WHERE certitude NOT IN ('douteux', 'certain', 'à déterminer', '') and certitude is not NULL";`	229	`$requete_verif = "SELECT certitude FROM ".$this->bd_cel.".cel_obs WHERE certitude NOT IN ('douteux', 'certain', 'à déterminer', '') and certitude is not NULL";`
229	`$verif = $this->bdd->recupererTous($requete_verif);`	230	`$verif = $this->bdd->recupererTous($requete_verif);`
Line 235...		Line 236...
235	`$requete = "UPDATE ".$this->bd_cel.".cel_obs`	236	`$requete = "UPDATE ".$this->bd_cel.".cel_obs`
236	`SET commentaire = case isnull(commentaire) when 1 THEN certitude ELSE concat(commentaire, ' Certitude : ', certitude) END,`	237	`SET commentaire = case isnull(commentaire) when 1 THEN certitude ELSE concat(commentaire, ' Certitude : ', certitude) END,`
237	certitude = '' WHERE `certitude` NOT IN ('douteux', 'certain', 'à déterminer', '') and certitude is not NULL";	238	certitude = '' WHERE `certitude` NOT IN ('douteux', 'certain', 'à déterminer', '') and certitude is not NULL";
238	`$this->bdd->requeter($requete);`	239	`$this->bdd->requeter($requete);`
239	`echo "effectuée \n";`	240	`echo "effectuée \n";`
240	`$requete_verif = "SELECT certitude FROM ".$this->bd_cel.".cel_obs WHERE certitude NOT IN ('douteux', 'certain', 'aDeterminer', '') and certitude is not NULL";`	241	`$requete_verif = "SELECT certitude FROM ".$this->bd_cel.".cel_obs WHERE certitude NOT IN ('douteux', 'certain', 'à déterminer', '') and certitude is not NULL";`
241	`$verif = $this->bdd->recupererTous($requete_verif);`	242	`$verif = $this->bdd->recupererTous($requete_verif);`
242	`if ($verif != array()) print_r($verif);`	243	`if ($verif != array()) print_r($verif);`
243	`}`	244	`}`
Line 244...		Line 245...
244		245
Line 325...		Line 326...
325	`echo "Uniformisation du nom de référentiel";`	326	`echo "Uniformisation du nom de référentiel";`
326	`$referentiels = array("BDTFX", "BDTRE", "BDTXA", "APD", "LBF", "ISFAN");`	327	`$referentiels = array("BDTFX", "BDTRE", "BDTXA", "APD", "LBF", "ISFAN");`
327	`foreach ($referentiels as $referentiel) {`	328	`foreach ($referentiels as $referentiel) {`
328	`$requete = "UPDATE ".$this->bd_cel.".cel_obs SET nom_referentiel = '".$referentiel."' WHERE nom_referentiel like '".$referentiel."%'";`	329	`$requete = "UPDATE ".$this->bd_cel.".cel_obs SET nom_referentiel = '".$referentiel."' WHERE nom_referentiel like '".$referentiel."%'";`
329	`$this->bdd->requeter($requete);`	330	`$this->bdd->requeter($requete);`
330	`$requete = "UPDATE ".$this->bd_cel.".cel_obs SET nom_referentiel = 'autre' WHERE nom_sel_nn = '' or nom_sel_nn = '0'";`	331	`$requete = "UPDATE ".$this->bd_cel.".cel_obs SET nom_referentiel = 'autre' WHERE nom_sel_nn IS NULL or nom_sel_nn = '0'";`
331	`$this->bdd->requeter($requete);`	332	`$this->bdd->requeter($requete);`
332	`$requete = "UPDATE ".$this->bd_cel.".cel_obs SET nom_referentiel = 'APD' WHERE nom_referentiel like 'bdtao%'";`	333	`$requete = "UPDATE ".$this->bd_cel.".cel_obs SET nom_referentiel = 'APD' WHERE nom_referentiel like 'bdtao%'";`
333	`$this->bdd->requeter($requete);`	334	`$this->bdd->requeter($requete);`
334	`$requete = "UPDATE ".$this->bd_cel.".cel_obs SET nom_referentiel = 'BDTFX' WHERE nom_referentiel like 'bdnff%'";`	335	`$requete = "UPDATE ".$this->bd_cel.".cel_obs SET nom_referentiel = 'BDTFX' WHERE nom_referentiel like 'bdnff%'";`
335	`$this->bdd->requeter($requete);`	336	`$this->bdd->requeter($requete);`
Line 420...		Line 421...
420	$requete = "UPDATE ".$this->bd_cel.".`cel_obs` c left join	421	$requete = "UPDATE ".$this->bd_cel.".`cel_obs` c left join
421	(SELECT distinct `ce_observation`, `nom_sel_nn`	422	(SELECT distinct `ce_observation`, `nom_sel_nn`
422	FROM ".$this->bd_del.".`del_commentaire` WHERE `proposition_retenue` = 1) d on	423	FROM ".$this->bd_del.".`del_commentaire` WHERE `proposition_retenue` = 1) d on
423	`ce_observation` = `id_observation` SET `certitude` = 'certain'	424	`ce_observation` = `id_observation` SET `certitude` = 'certain'
424	WHERE (c.`mots_cles_texte` LIKE '%adeterminer%'	425	WHERE (c.`mots_cles_texte` LIKE '%adeterminer%'
425	or c.`certitude` = 'aDeterminer') and c.`nom_sel_nn` = d.`nom_sel_nn`";	426	or c.`certitude` = 'à déterminer') and c.`nom_sel_nn` = d.`nom_sel_nn`";
426	`$this->bdd->requeter($requete);`	427	`$this->bdd->requeter($requete);`
Line 427...		Line 428...
427		428
428	`echo "Certitude à douteux quand obs validée sur ip mais num_nom diff entre ip et cel \n";`	429	`echo "Certitude à douteux quand obs validée sur ip mais num_nom diff entre ip et cel \n";`
429	$requete = "UPDATE ".$this->bd_cel.".`cel_obs` c left join	430	$requete = "UPDATE ".$this->bd_cel.".`cel_obs` c left join
430	(SELECT distinct `ce_observation`, `nom_sel_nn`	431	(SELECT distinct `ce_observation`, `nom_sel_nn`
431	FROM ".$this->bd_del.".`del_commentaire` WHERE `proposition_retenue` = 1 ) d on	432	FROM ".$this->bd_del.".`del_commentaire` WHERE `proposition_retenue` = 1 ) d on
432	`ce_observation` = `id_observation` SET `certitude` = 'douteux'	433	`ce_observation` = `id_observation` SET `certitude` = 'douteux'
433	WHERE (c.`mots_cles_texte` LIKE '%adeterminer%'	434	WHERE (c.`mots_cles_texte` LIKE '%adeterminer%'
434	or c.`certitude` = 'aDeterminer') and c.`nom_sel_nn` != d.`nom_sel_nn`";	435	or c.`certitude` = 'à déterminer') and c.`nom_sel_nn` != d.`nom_sel_nn`";
Line 435...		Line 436...
435	`$this->bdd->requeter($requete);`	436	`$this->bdd->requeter($requete);`
436		437
437	`echo "Certitude à aDeterminer quand obs non validée sur ip \n";`	438	`echo "Certitude à aDeterminer quand obs non validée sur ip \n";`
438	$requete = "UPDATE ".$this->bd_cel.".`cel_obs` c SET `certitude` = 'aDeterminer'	439	$requete = "UPDATE ".$this->bd_cel.".`cel_obs` c SET `certitude` = 'à déterminer'
439	WHERE c.`mots_cles_texte` LIKE '%adeterminer%' AND	440	WHERE c.`mots_cles_texte` LIKE '%adeterminer%' AND
Line 440...		Line 441...
440	(c.`certitude` = '' or c.certitude IS NULL)";	441	(c.`certitude` = '' or c.certitude IS NULL)";