/** * @Created By : Mahipal Singh Adhikari * @Created On : 2-Dec-2010 * @Description : Used to parse Lonely Planet Country XML and save data to database table */ public function lonelyXmlAction() { //disable layout and view $this->view->layout()->disableLayout(); $this->_helper->viewRenderer->setNoRender(true); //create Db and Sanitize class objects $db = Zend_Registry::get("db"); $sanitizeM = new Base_Sanitize(); //Truncate or Empty table first $db->query("TRUNCATE TABLE lonely_planet_country"); //$directory="data/1615/xml/20110119/"; $directory = "data/1615/xml/20110304/"; //$directory="data/1615/xml/test/"; if (!($dp = opendir($directory))) { die("Cannot open {$directory}."); } $countFiles = 1; while ($filename = readdir($dp)) { $file_path = ""; if (is_dir($filename)) { continue; } else { if ($filename != '.' && $filename != '..') { $fileExtension = explode(".", $filename); $fileExtension = strtolower($fileExtension[count($fileExtension) - 1]); if ($fileExtension == "xml") { $file_path = $directory . $filename; //open and replace HTMl tags from XML file and rewrite $fileContent = ""; $fh = ""; $fileContent = file_get_contents($file_path); $fileContent = $this->replaceText($fileContent); $fh = fopen($file_path, 'w+') or die("can't open file"); fwrite($fh, $fileContent); fclose($fh); //now load XML files and parse XMLs $data = array(); echo "<br><br>Strart processing file==><b>" . $file_path . "</b> counter=>" . $countFiles; $destination = simplexml_load_file($file_path); $data["node_id"] = (string) $destination["node_id"]; $data["destination_name"] = (string) $sanitizeM->clearInputs($destination->destination_name); $data["full_destination_name"] = (string) $destination->general->full_destination_name; $data["intro_mini"] = (string) $this->replaceText($destination->general->intro_mini, true); $data["intro_short"] = (string) $this->replaceText($destination->general->intro_short, true); $data["warning_title"] = (string) $destination->general->warning_title; $data["warning_text"] = (string) $this->replaceText($destination->general->warning_text, true); $data["warning_position"] = (string) $destination->general->warning_position; $data["warning_severity"] = (string) $destination->general->warning_severity; //timezones if ($destination->general->timezones) { $tz = 0; $arrayTimezone = array(); foreach ($destination->general->timezones->children() as $timezone) { $arrayTimezone[$tz]['gmt_utc'] = (string) $timezone->gmt_utc; $tz++; } $data["timezones"] = serialize($arrayTimezone); } $data["weights_measures_system"] = (string) $destination->general->weights_measures_system; $data["capital_city"] = (string) $destination->general->capital_city; //government leaders if ($destination->government->leaders) { $ld = 0; $arrayLeaders = array(); foreach ($destination->government->leaders->children() as $leader) { $arrayLeaders[$ld]['leader_name'] = (string) $leader->leader_name; $arrayLeaders[$ld]['leader_type'] = (string) $leader->leader_type; $arrayLeaders[$ld]['leader_title'] = (string) $leader->leader_title; $ld++; } $data["leaders"] = serialize($arrayLeaders); } $data["government_type"] = (string) $destination->government->government_type; $data["area_sqkm"] = (string) $destination->environment->area_sqkm; $data["population"] = (string) $destination->environment->population; //languages $i = 0; if ($destination->society->language_spokens) { $arrayLanguage = array(); foreach ($destination->society->language_spokens->children() as $language_spoken) { $arrayLanguage[$i]['language_spoken_type'] = (string) $language_spoken->language_spoken_type; $arrayLanguage[$i]['language_spoken_name'] = (string) $language_spoken->language_spoken_name; if ($language_spoken->language_spoken_description) { $arrayLanguage[$i]['language_spoken_description'] = (string) $this->replaceText($language_spoken->language_spoken_description, true); } $i++; } $data["language_spokens"] = serialize($arrayLanguage); } $data["religion"] = (string) $this->replaceText($destination->society->religion, true); $data["currency_code"] = (string) $destination->economy->currency_code; $data["currency_name"] = (string) $destination->economy->currency_name; $data["currency_symbol"] = (string) $destination->economy->currency_symbol; $data["currency_unit"] = (string) $destination->economy->currency_unit; //$data["relative_cost_rooms"]=serialize($destination->money->relative_cost_rooms); //$data["relative_cost_meals"]=serialize($destination->money->relative_cost_meals); $pre = 'pre-departure'; $when_to_go = $destination->{$pre}->when_to_go; $data["when_to_go"] = (string) $this->replaceText($destination->{$pre}->when_to_go, true); $data["visas_overview"] = (string) $this->replaceText($destination->{$pre}->visas_overview, true); //electrical plugs if ($destination->{$pre}->electrical_plugs) { $ep = 0; $arrayElectricalPlugs = array(); foreach ($destination->{$pre}->electrical_plugs->children() as $electrical_plugs) { $arrayElectricalPlugs[$ep]['image_filename'] = (string) $electrical_plugs->image_filename; $arrayElectricalPlugs[$ep]['electrical_plug_description'] = (string) $this->replaceText($electrical_plugs->electrical_plug_description, true); $ep++; } $data["electrical_plugs"] = serialize($arrayElectricalPlugs); } $data["electricity_voltage"] = (string) $destination->{$pre}->electricity_voltage; $data["electricity_hz"] = (string) $destination->{$pre}->electricity_hz; $data["dangers_and_annoyances"] = (string) $this->replaceText($destination->health->dangers_and_annoyances, true); //health conditions if ($destination->health->health_conditions) { $arrHealthCondition = array(); foreach ($destination->health->health_conditions->children() as $healthCondition) { $type = ""; $type = (string) $healthCondition->health_condition_type; $arrHealthCondition[$type] = (string) $this->replaceText($healthCondition->health_condition_description, true); } $data["health_conditions"] = serialize($arrHealthCondition); } $data["weather_overview"] = (string) $this->replaceText($destination->weather->weather_overview, true); $data["country_dialling_code"] = (string) $destination->communication->country_dialling_code; //transports if ($destination->transport) { $arrTransport = array(); foreach ($destination->transport->destination_transport_topics->children() as $transport) { $transport_type = ""; $transport_type = (string) $transport->transport_type; $arrTransport[$transport_type] = (string) $this->replaceText($transport->transport_description, true); } $data["transport"] = serialize($arrTransport); } $data["history_pre_20c"] = (string) $this->replaceText($destination->culture->history_pre_20c, true); $data["history_modern"] = (string) $this->replaceText($destination->culture->history_modern, true); $data["history_recent"] = (string) $this->replaceText($destination->culture->history_recent, true); //$data["product_info"]=serialize($destination->product_info); //get images if ($destination->images) { $img = 0; $arrayImages = array(); foreach ($destination->images->children() as $image) { $arrayImages[$img]['image_caption'] = (string) $image->image_caption; $arrayImages[$img]['image_photographer'] = (string) $image->image_photographer; $arrayImages[$img]['image_filename'] = (string) $image->image_filename; $arrayImages[$img]['image_thumbnail_filename'] = (string) $image->image_thumbnail_filename; $img++; } $data["images"] = serialize($arrayImages); } //get maps if ($destination->map) { $arrayMaps = array(); $arrayMaps['map_name'] = (string) $destination->map->map_name; $arrayMaps['map_filename'] = (string) $destination->map->map_filename; $arrayMaps['map_thumbnail_filename'] = (string) $destination->map->map_thumbnail_filename; $data["map"] = serialize($arrayMaps); } $i = 0; foreach ($destination->pois->children() as $poi) { $arrayPoi[$i]['poi_name'] = (string) $poi->poi_name; $address_parts = (array) $poi->address_parts; $addressPart = array(); if (isset($address_parts['address_part']) && count($address_parts['address_part']) > 0) { foreach ($address_parts['address_part'] as $address_part) { $addressPart[] = (array) $address_part; } $arrayPoi[$i]['address_parts'] = $addressPart; } $arrayPoi[$i]['address_postcode'] = (string) $poi->address_postcode; $arrayPoi[$i]['poi_web'] = (string) $poi->poi_web; $arrayPoi[$i]['review_summary'] = (string) $this->replaceText($poi->review_summary, true); $arrayPoi[$i]['review_full'] = (string) $this->replaceText($poi->review_full, true); $arrayPoi[$i]['keywords'] = (array) $poi->keywords; $i++; } $data["pois"] = serialize($arrayPoi); //attrations if ($destination->attractions) { $attr = 0; $arrayAttr = array(); foreach ($destination->attractions->children() as $attraction) { $arrayAttr[$attr]['destination_name'] = (string) $attraction->destination_name; $arrayAttr[$attr]['full_destination_name'] = (string) $attraction->general->full_destination_name; $arrayAttr[$attr]['intro_short'] = (string) $this->replaceText($attraction->general->intro_short, true); $arrayAttr[$attr]['intro_medium'] = (string) $this->replaceText($attraction->general->intro_medium, true); $attr++; } $data["attractions"] = serialize($arrayAttr); } $data["copyright"] = (string) $destination->copyright; $data["addedon"] = time(); /*----Country Info----*/ $countryM = new Application_Model_Country(); $countryName = $sanitizeM->clearInputs($data["destination_name"]); if (!get_magic_quotes_gpc()) { $countryName = addslashes($countryName); } //$countryName=@mb_convert_encoding($countryName, 'UTssssF8', 'auto'); $country = $countryM->fetchRow("name like '{$countryName}'"); if (false === $country) { //get country continent $continent_id = ""; $continent_id = $countryM->countryContinent($countryName); $countryName = $sanitizeM->clearInputs($data["destination_name"]); //if doesn't exists if (!$this->duplicateCountries($countryName)) { $countryM->setName($countryName); $countryM->setContinentId($continent_id); $country_id = $countryM->save(); } } else { $country_id = $country->getId(); } $data['country_id'] = $country_id; /*---------------*/ //check existing country record in lonely_planet_country table $lonelyM = new Application_Model_LonelyPlanetCountry(); $lonelyM = $lonelyM->fetchRow("country_id={$country_id}"); if (false !== $lonelyM) { $res = $db->update("lonely_planet_country", $data, "country_id={$country_id}"); } else { $res = $db->insert("lonely_planet_country", $data); } echo "<br>END File==><b>" . $file_path . "</b> has been processed, counter=>" . $countFiles; $countFiles++; } //end of if } } //end of else if } //end of while closedir($dp); //rename countries name $db->query("UPDATE country SET name='Bosnia and Herzogovina' WHERE name='Bosnia-Hercegovina'"); $db->query("UPDATE country SET name='Vatican City' WHERE name='Holy See'"); $db->query("UPDATE country SET name='Ivory Coast' WHERE name='Cote dIvoire'"); //delete countries for which no planet feeds data available //$sSQlQuery = "DELETE FROM country WHERE id IN(select t.id FROM (SELECT id FROM `country` WHERE id NOT IN (SELECT country_id FROM lonely_planet_country WHERE country_id IS NOT NULL)) AS t)"; //$db->query($sSQlQuery); }