format("Y-m-d"); //$a_date = date ("Y-m-d", strtotime($a_row["Date"])); if ($a_date=="1970-01-01") { echo "date impossible à convertir ! (".$val.") "; die(); } return $a_date; } function to_BOOLEAN($val) { if (isset($val)) { if (is_null($val)) return("NULL"); $val = trim($val); if ($val=="1" || $val=="true" or $val=="TRUE") return "true"; if ($val=="0" || $val=="-1" || $val=="false" || $val=="FALSE") return "false"; } return ("NULL"); } function to_FLOAT($val) { //echo "-".$val."
"; if (isset($val)) { if (strlen(trim($val))==0 || is_null($val)) return("NULL"); return (floatval(trim($val))); } return ("NULL"); } function to_STRING($val) { if (isset($val)) { if (is_null($val)) return("NULL"); return ("\"".addslashes($val)."\""); } return ("NULL"); } function SELECT_FROM_TEMOINS($title) { global $db; //page_id, page_title $sql = "SELECT page.*, `text`.* FROM page, `text`, revision WHERE page.page_latest=revision.rev_id AND revision.rev_text_id=`text`.old_id ".($title!=""?" AND page_title=\"".$title."\"":"")." ORDER BY page_title"; $row = NULL; //echo ""; //echo ""; if($res = mysql_query($sql)) { while($text = mysql_fetch_array($res)) { if (stristr($text["old_text"], "UFO:Event") === FALSE || stristr($text["page_title"], "UFO:Cases") === FALSE) { // This is not an UFO sight } else { // ATTENTION : il peut y avoir plusieurs témoignages dans une page de cas $case = stristr($text["old_text"], "UFO:Event"); $case = substr($case, 9, strpos($case,"}}")-9); $case = explode("|", str_replace("\t", "", $case)); if (sizeof($case>20)) { $a_row = NULL; $a_row["title"] = $text["page_title"]; $a_row["page_id"] = $text["page_id"]; // Updating information ... foreach ($case as $keyvalue) { $keyvalue = explode ("=", $keyvalue); $a_row[trim($keyvalue[0])] = trim($keyvalue[1]); } } //echo $text["old_flags"]."
"; //echo ""; //echo ""; $row[] = $a_row; } } } //echo "
page_namespacepage_titlepage_restrictionspage_counterpage_is_redirectpage_is_newpage_randompage_touchedpage_latestpage_len
".$text["rev_page"]."".$text["rev_comment"]."".$text["rev_user"]."".$text["rev_user_text"]."".$text["rev_timestamp"]."".$text["rev_minor_edit"]."".$text["rev_deleted"]."
".$text["page_namespace"]."".$text["page_title"]."".$text["page_restrictions"]."".$text["page_counter"]."".$text["page_is_redirect"]."".$text["page_is_new"]."".$text["page_random"]."".$text["page_touched"]."".$text["page_latest"]."".$text["page_len"]."
"; return $row; } function WIKI_TO_SQL_CASES($row, $renew) { global $db; $chars = array("[","]"); foreach ($row as $a_row) { //echo $a_row["Description"]; $SET = "SET "; while (list($key, $value)=each($a_row)) { if ($key!="title" && $key!="ID_event") $SET .= $key." = \"".addslashes($value)."\""; } // Association des sources de données // 0 météorite du 25/01/2008 (#777700) // 1 NUFORC (#ffffff) // 2 GEIPAN (#ffffff) // 3 Base OVNI France (#ff00ff) // 4 Larry Hatch (#ffffff) // 5 Dominique Weinstein (#ff99ff) // 6 Affaire Ummo // 7 Crops Circles NULL #ffffff switch(trim($a_row["DataBaseSource"])) { case "NUFORC": $ID_SourceType = 1; break; case "GEIPAN": $ID_SourceType = 2; break; case "Base OVNI France": $ID_SourceType = 3; break; case "Larry Hatch": $ID_SourceType = 4; break; case "Dominique Weinstein": $ID_SourceType = 5; break; case "Affaire Ummo": $ID_SourceType = 6;break; case "Crops Circles": $ID_SourceType = 7;break; case NULL: $ID_SourceType=NULL;break; } // IL S'AGIT DE CREER DES CLES UNIQUES (INTERNES) UTILISEES POUR CROISER LES DONNEES // IL EST IMPORTANT DE CONSERVER LE TYPE DANS LE TITRE // Clé d'un CAS: Position et Date du début du point de démarrage du premier témoignage du cas // 1981/01/08_17:00:00_@_FR_31_Toulouse_43.50E_6.48N N'indiquer formellement Latitude / Longitude que si // "%+06.2f#%+07.2f#" //1. Si plusieurs témoignages : PGCM géographique des témoignages //2. (?) La clé doit-il être le niveau le plus précis (peu parlant), ou l'arborescence complète jusqu'au + précis diponible. // il serait bien de distinguer une coordonnée (latitude/longitude) associée à une ville de celle d'une localisation précise du témoin, voire mieux trajectoire $ID_wCase = to_DATE($a_row["Date"])."_".date ("H:i:s", strtotime($a_row["Time"]))."_@_". $a_row["Country"]."_".(isset($a_row["Department"])?$a_row["Department"]:$a_row["State/Region"])."_".(strlen($a_row["Town/Place"])>0?$a_row["Town/Place"]."_":""). sprintf("%.2f%s_%.2f%s", abs($a_row["Latitude"]), ($a_row["Latitude"]>0?"N":"S"), abs($a_row["Longitude"]), ($a_row["Longitude"]>0?"W":"E")); // Clé d'un TEMOIGNAGE : Position et Date du début du témoignage + Prénom / Nom // 43.50E_6.48N_1981/01/08_17:00:00_Maurisse_Masse $ID_wTestimony = str_replace($chars,"",$a_row["Witness"]); $ID_wTestimony = $ID_wCase."_".(strlen($ID_wTestimony)>0?$ID_wTestimony:"T1"); $Testimony = ""; # 1. Vérifie l'existence du cas dans la table map_events // opt. renew : forcer la recréation de tous les enregistrements liés à cette clé et et sinon, mettre à jour ceux qui existent déjà if (!$renew) { } else { // la clé est construite à partir de la plus petite granularité d'information disponible (// attention dans le cas d'un CAS, agréger l'information) $ID_wCase = ""; $ID_wCase .= to_DATE($a_row["Date"])."_".date ("H:i", strtotime($a_row["Time"]))."_@"; if (isset($a_row["Latitude"]) && isset($a_row["Longitude"])) $ID_wCase .= "_".sprintf("%.2f_%.2f", $a_row["Latitude"], $a_row["Longitude"]); else $ID_wCase .= (strlen($a_row["Country"])>0?"_".$a_row["Country"]:""). (isset($a_row["Department"])?"_".$a_row["Department"]:(strlen($a_row["State/Region"])>0?"_".$a_row["State/Region"]:"")). (strlen($a_row["Town/Place"])>0?"_".$a_row["Town/Place"]:""); $Testimony = str_replace($chars, "", $a_row["Witness"]); $ID_wTestimony = $ID_wCase."_#_".(strlen($Testimony)>0?$Testimony:"T1"); } // ID Cas & Témoin N° $rech1 = "SELECT * FROM map_events WHERE ID_page=".$a_row["page_id"]; //." AND Witness=".(strlen($Testimony)>0?"\"".$Testimony."\"":"NULL"); echo $rech1."
"; $rech1 = mysql_query($rech1); if (mysql_num_rows($rech1)) { echo "Mise à jour"; // un enregistrement à mettre à jour //(!isset($a_row["Latitude"]) || is_null($a_row["Latitude"]) || strlen($a_row["Latitude"])<1?"NULL":$a_row["Latitude"]) //(!isset($a_row["Longitude"]) || is_null($a_row["Longitude"]) || strlen($a_row["Latitude"])<1?"NULL":$a_row["Longitude"]) $sqli = "UPDATE map_events SET ID_SourceType = ".$ID_SourceType.", ID_Testimony = \"".addslashes($ID_wTestimony)."\", ID_page = ".$a_row["page_id"].", page_title= ".(is_null($a_row["title"])?"NULL":"\"".addslashes($a_row["title"])."\"").", Witness = ".(is_null($a_row["Witness"])?"\"T1\"":"\"".addslashes($a_row["Witness"])."\"").", Latitude = ".to_FLOAT($a_row["Latitude"]).", Longitude = ".to_FLOAT($a_row["Longitude"]).", Country = ".(is_null($a_row["Country"])?"NULL":"\"".addslashes($a_row["Country"])."\"").", State = ".(is_null($a_row["State/Region"])?"NULL":"\"".addslashes($a_row["State/Region"])."\"").", Town = ".(is_null($a_row["Town/Place"])?"NULL":"\"".addslashes($a_row["Town/Place"])."\"").", date_s = \"".to_DATE($a_row["Date"])."\", hour_s = \"".$a_row["Time"]."\", Weather = ".(is_null($a_row["Weather"])?"NULL":"\"".addslashes($a_row["Weather"])."\"").", Summary = ".(is_null($a_row["Description"])?"NULL":"\"".addslashes($a_row["Description"])."\"").", nb_objects = ".(is_null($a_row["Objects_Nb"])?"NULL":($a_row["Objects_Nb"]=="Inconnu"?-1:$a_row["Objects_Nb"])).", shape = ".(is_null($a_row["Object_Type"])?"NULL":"\"".addslashes($a_row["Object_Type"])."\"").", surface_type = ".(is_null($a_row["Surface_Type"])?"NULL":"\"".addslashes($a_row["Surface_Type"])."\"").", surface_color = ".(is_null($a_row["Main_Color"])?"NULL":"\"".addslashes($a_row["Main_Color"])."\"").", lights = ".to_FLOAT($a_row["Lights"]).", l_red = ".to_FLOAT($a_row["L_Red"]).", l_white = ".to_FLOAT($a_row["L_White"]).", l_yellow = ".to_FLOAT($a_row["L_Yellow"]).", l_blue = ".to_FLOAT($a_row["L_Blue"]).", l_green = ".to_FLOAT($a_row["L_Green"]).", l_orange = ".to_FLOAT($a_row["L_Orange"]).", l_multi = ".to_BOOLEAN($a_row["L_Multi"]).", flashing = ".to_BOOLEAN($a_row["Flashing"]).", duration_min = ".(is_null($a_row["Duration_Min"])?"NULL":"\"".addslashes($a_row["Duration_Min"])."\"").", duration_max = ".(is_null($a_row["Duration_Max"])?"NULL":"\"".addslashes($a_row["Duration_Max"])."\"").", speed = ".(is_null($a_row["Speed"])?"NULL":"\"".addslashes($a_row["Speed"])."\"").", wp1_dir = ".to_FLOAT($a_row["wp1_dir"]).", wp1_az = ".to_FLOAT($a_row["wp1_az"]).", DIRs = ".(is_null($a_row["DIRs"])?"NULL":"\"".addslashes($a_row["DIRs"])."\"").", dis_appeared = ".(is_null($a_row["instant_disparition"])?"NULL":$a_row["instant_disparition"])." WHERE ID_page=".$a_row["page_id"]; //." AND Witness=".(strlen($Testimony)>0?"\"".$Testimony."\"":"NULL"); //echo $sqli."
"; mysql_query($sqli) or die('La mise à jour de la page ['.$a_row["page_id"].'] a échoué.

'.$sqli.'
'); } elseif (mysql_num_rows($rech1)>=1) { // plusieurs possibilités = BUG } else { // 1981/01/08_17:00_@_43.50_6.48_Maurisse_Masse $rech2 = mysql_query("SELECT * FROM map_events WHERE ID_Testimony='".$ID_wTestimony."'"); if (mysql_num_rows($rech2)==1) { // un enregistrement à mettre à jour } elseif (mysql_num_rows($rech2) == false) { echo "Insertion
"; //echo "[".($a_row["instant_disparition"]==NULL)."]
[".is_null($a_row["instant_disparition"])."]
"; //(!isset($a_row["Latitude"]) || is_null($a_row["Latitude"]) || strlen($a_row["Latitude"])<1?"NULL":$a_row["Latitude"]) //(!isset($a_row["Longitude"]) || is_null($a_row["Longitude"]) || strlen($a_row["Latitude"])<1?"NULL":$a_row["Longitude"]) $sqli = "INSERT INTO map_events(ID_SourceType, ID_Testimony, ID_page, page_title, Witness, Latitude, Longitude, Country, State, Town, date_s, hour_s, weather, Summary, nb_objects, shape, surface_type, surface_color, lights, l_red, l_white, l_yellow, l_blue, l_green, l_orange, l_multi, flashing, duration_min, duration_max, speed, wp1_dir, wp1_az, DIRs, dis_appeared) VALUES ( ".$ID_SourceType.", \"".addslashes($ID_wTestimony)."\", ".$a_row["page_id"].", ".(is_null($a_row["title"])?"NULL":"\"".addslashes($a_row["title"])."\"").", ".(is_null($a_row["Witness"])?"\"T1\"":"\"".addslashes($a_row["Witness"])."\"").", ".to_FLOAT($a_row["Latitude"]).", ".to_FLOAT($a_row["Longitude"]).", ".(is_null($a_row["Country"])?"NULL":"\"".addslashes($a_row["Country"])."\"").", ".(is_null($a_row["State/Region"])?"NULL":"\"".addslashes($a_row["State/Region"])."\"").", ".(is_null($a_row["Town/Place"])?"NULL":"\"".addslashes($a_row["Town/Place"])."\"").", \"".to_DATE($a_row["Date"])."\", \"".$a_row["Time"]."\", ".(is_null($a_row["Weather"])?"NULL":"\"".addslashes($a_row["Weather"])."\"").", ".(is_null($a_row["Description"])?"NULL":"\"".addslashes($a_row["Description"])."\"").", ".(is_null($a_row["Objects_Nb"])?"NULL":($a_row["Objects_Nb"]=="Inconnu"?-1:$a_row["Objects_Nb"])).", ".(is_null($a_row["Object_Type"])?"NULL":"\"".addslashes($a_row["Object_Type"])."\"").", ".(is_null($a_row["Surface_Type"])?"NULL":"\"".addslashes($a_row["Surface_Type"])."\"").", ".(is_null($a_row["Main_Color"])?"NULL":"\"".addslashes($a_row["Main_Color"])."\"").", ".to_FLOAT($a_row["Lights"]).", ".to_FLOAT($a_row["L_Red"]).", ".to_FLOAT($a_row["L_White"]).", ".to_FLOAT($a_row["L_Yellow"]).", ".to_FLOAT($a_row["L_Blue"]).", ".to_FLOAT($a_row["L_Green"]).", ".to_FLOAT($a_row["L_Orange"]).", ".to_BOOLEAN($a_row["L_Multi"]).", ".to_BOOLEAN($a_row["Flashing"]).", ".(is_null($a_row["Duration_Min"])?"NULL":"\"".addslashes($a_row["Duration_Min"])."\"").", ".(is_null($a_row["Duration_Max"])?"NULL":"\"".addslashes($a_row["Duration_Max"])."\"").", ".(is_null($a_row["Speed"])?"NULL":"\"".addslashes($a_row["Speed"])."\"").", ".to_FLOAT($a_row["wp1_dir"]).", ".to_FLOAT($a_row["wp1_az"]).", ".(is_null($a_row["DIRs"])?"NULL":"\"".addslashes($a_row["DIRs"])."\"").", ".(is_null($a_row["instant_disparition"])?"NULL":$a_row["instant_disparition"]).")"; //echo $sqli."
"; mysql_query($sqli) or die('La création de l enregistrement ['.$a_row["page_id"].'] a échoué.
'); } } // INSERER dans la table map_events en se basant non pas sur l'ID mais sur les éléments précisés précédemment pour faire pivot //mysql_query("UPDATE map_events ".$SET." WHERE Age = '36' WHERE FirstName = 'Peter' AND LastName = 'Griffin'"); } } // OPERATION SET QUI APPLIQUE UNE FONCTION DE REMPLISSAGE ? # Export des cas. Affichage du nom normalisé de chaque témoignage. //WIKI_TO_SQL_CASES($row, true); //SQL_TO_WIKI_CASES(); #Après avoir ajouté un cas dans le wikipedia: $row = SELECT_FROM_TEMOINS($title); //print_r($row); WIKI_TO_SQL_CASES($row, true); # Depuis le Wikipedia, renommer les champs: ?>