/**
* Finds the City model based on its primary key value.
* If the model is not found, a 404 HTTP exception will be thrown.
* @param integer $id
* @return City the loaded model
* @throws NotFoundHttpException if the model cannot be found
*/
protected function findModel($id)
{
if (($model = City::findOne($id)) !== null) {
return $model;
} else {
throw new NotFoundHttpException('The requested page does not exist.');
}
}
public function actionIntegration()
{
$dsn_array_attribute = explode('=', Yii::$app->db->dsn);
$db_name = $dsn_array_attribute[2];
$table_prefix = Yii::$app->db->tablePrefix;
BaseFileHelper::createDirectory($this->tempDir, 0777);
foreach ($this->files as $table_name => $filename) {
$zip_name = $this->tempDir . $filename;
if (!file_exists($zip_name)) {
if (!($tmp_file = fopen($zip_name, 'w'))) {
return;
}
$curl = curl_init();
$url = $this->baseUrl . $filename;
curl_setopt($curl, CURLOPT_URL, $url);
curl_setopt($curl, CURLOPT_FILE, $tmp_file);
curl_setopt($curl, CURLOPT_HEADER, 0);
curl_exec($curl);
curl_close($curl);
fclose($tmp_file);
}
if (preg_match('/\\.zip$/', $zip_name)) {
$zip = new \ZipArchive();
$zip->open($zip_name);
$zip->extractTo($this->tempDir);
}
$unziped_name = str_replace('.zip', '.txt', $zip_name);
if ($table_name == 'geoname_alternative_name') {
$new_unzipped_name = $this->tempDir . 'ru_names.txt';
`grep -P '^\\d+\t\\d+\tru\t' {$unziped_name} > {$new_unzipped_name}`;
$unziped_name = $new_unzipped_name;
} else {
if ($table_name == 'geoname_country') {
$new_unzipped_name = $this->tempDir . 'countryInfo_fixed.txt';
`grep -Pv '^#.*\$' {$unziped_name} > {$new_unzipped_name}`;
$unziped_name = $new_unzipped_name;
}
}
$fields = ArrayHelper::getColumn(Yii::$app->db->createCommand("SHOW COLUMNS FROM {$table_prefix}{$table_name}")->query(), 'Field');
Yii::$app->db->createCommand("TRUNCATE {$table_prefix}{$table_name}")->query();
$sql = "LOAD DATA LOCAL INFILE '" . $unziped_name . "' INTO TABLE " . $table_prefix . $table_name . ' CHARACTER SET utf8 FIELDS TERMINATED BY \\"\\t\\" LINES TERMINATED BY \\"\\n\\" (' . implode(', ', $fields) . ')';
$db = Yii::$app->db;
$cmd = "mysql --local-infile=1 -u{$db->username} -p{$db->password} {$db_name} -e \"{$sql}\" \n";
`{$cmd}`;
}
BaseFileHelper::removeDirectory($this->tempDir);
Yii::$app->db->createCommand("DELETE FROM {$table_prefix}geoname_country WHERE geoname_id = 0")->query();
$sql = "SELECT {$table_prefix}geoname_country.*,\n (SELECT alternate_name FROM {$table_prefix}geoname_alternative_name WHERE {$table_prefix}geoname_alternative_name.geoname_id = {$table_prefix}geoname_country.geoname_id LIMIT 0,1) as ru_name\n FROM {$table_prefix}geoname_country ";
$geoname_countries = Yii::$app->db->createCommand($sql)->query();
foreach ($geoname_countries as $geoname_country) {
if (!($country = Country::findOne(['iso' => $geoname_country['iso']]))) {
$country = new Country();
}
$attributes = ['iso' => $geoname_country['iso'], 'title' => $geoname_country['ru_name'], 'worldpart' => $geoname_country['continent'], 'geoname_id' => $geoname_country['geoname_id'], 'is_published' => 1];
if (!$country->getIsNewRecord()) {
$attributes = ['geoname_id' => $geoname_country['geoname_id']];
}
$capital_id = $this->getCapitalIdByName($geoname_country['capital'], $geoname_country['iso'], $table_prefix);
if ($capital_id && $country->capital_id != $capital_id) {
$attributes['capital_id'] = $capital_id;
}
$country->setAttributes($attributes);
$country->save();
}
$country_ids = [];
foreach (Yii::$app->db->createCommand("SELECT id, iso FROM {$table_prefix}country")->query() as $item) {
$country_ids[$item['iso']] = $item['id'];
}
$sql = "SELECT\n {$table_prefix}geoname_city.geoname_id as geoname_id,\n {$table_prefix}geoname_city.alternate_names as alternate_names,\n {$table_prefix}geoname_city.latitude as latitude,\n {$table_prefix}geoname_city.longitude as longitude,\n {$table_prefix}geoname_city.country_code as country_code,\n {$table_prefix}geoname_alternative_name.alternate_name as ru_name\n FROM {$table_prefix}geoname_city\n JOIN {$table_prefix}geoname_alternative_name ON {$table_prefix}geoname_alternative_name.geoname_id = {$table_prefix}geoname_city.geoname_id\n GROUP BY geoname_id ";
$geoname_cities = Yii::$app->db->createCommand($sql)->query();
foreach ($geoname_cities as $geoname_city) {
if (!$geoname_city['country_code']) {
continue;
}
if (!isset($country_ids[$geoname_city['country_code']])) {
continue;
}
if (!$geoname_city['geoname_id']) {
continue;
}
$russian_city_title = trim($geoname_city['ru_name']);
$russian_city_title = mb_convert_case($russian_city_title, MB_CASE_TITLE, 'UTF-8');
if (!preg_match('/[а-яА-я]/', $russian_city_title)) {
continue;
}
if (!($city = City::findOne(['geoname_id' => $geoname_city['geoname_id']]))) {
$city = new City();
}
$identifier = strtolower($this->translitRussian($russian_city_title));
$identifier = str_replace(' ', ' ', $identifier);
$identifier = str_replace(' ', '-', $identifier);
$attributes = ['title' => $russian_city_title, 'latitude' => $geoname_city['latitude'], 'longitude' => $geoname_city['longitude'], 'identifier' => $identifier, 'geoname_id' => $geoname_city['geoname_id'], 'country_id' => $country_ids[$geoname_city['country_code']], 'is_published' => 1];
if (!$city->getIsNewRecord()) {
unset($attributes['title']);
unset($attributes['identifier']);
unset($attributes['country_id']);
unset($attributes['is_published']);
}
$city->setAttributes($attributes);
$city->save();
}
}