/** * 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.'); } }
/** * Creates data provider instance with search query applied * @return ActiveDataProvider */ public function search($params) { $query = City::find()->joinWith(['country']); $dataProvider = new ActiveDataProvider(['query' => $query]); if (!($this->load($params) && $this->validate())) { return $dataProvider; } $query->andFilterWhere(['id' => $this->id, 'country_id' => $this->country_id]); $query->andFilterWhere(['like', City::tableName() . '.title', $this->title])->andFilterWhere(['like', Country::tableName() . '.title', $this->country]); return $dataProvider; }
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(); } }