<?php

/**
 * Class Geo :: Geo table lookups
 */
class Geo {

	/**
	 * @return array Result array of all country fields (id, country_code, name)
	 */
	public static function getCountries() {

		$DB = new \DB\SQL('sqlite:' . dirname(__FILE__) . DIRECTORY_SEPARATOR . '../sql/mysa_geo_countries.sqlite');
		return ($DB->exec('SELECT country_code, name FROM mysa_geo_countries ORDER BY name ASC', null, 900));

	}

	/**
	 * @param $cc string Country Code
	 * @return array Result array of all filtered region fields (id, country_code, region_code, name)
	 */
	public static function getRegions($cc) {

		$DB = new \DB\SQL('sqlite:' . dirname(__FILE__) . DIRECTORY_SEPARATOR . '../sql/mysa_geo_regions.sqlite');
		return ($DB->exec('SELECT country_code, region_code, name FROM mysa_geo_regions WHERE country_code=? ORDER BY name ASC', $cc, 900));

	}

	/**
	 * @param $cc string Country Code
	 * @param $rc string Region Code
	 * @return array Result array of all filtered city fields (id, country_code, region_code, name) and name_encoded with encoded name (for utf8 chars)
	 */
	public static function getCities($cc, $rc) {

		$DB = new \DB\SQL('sqlite:' . dirname(__FILE__) . DIRECTORY_SEPARATOR . '../sql/mysa_geo_cities.sqlite');
		$result = $DB->exec('SELECT country_code, region_code, name FROM mysa_geo_cities WHERE country_code=:cc AND region_code=:rc ORDER BY name ASC',
				array(':cc' => $cc, ':rc' => $rc));
		$result_encoded = array();
		foreach ($result as $key => $value) {
			$result_encoded[$key] = $value;
			$result_encoded[$key]['name_encoded'] = strtoupper(urlencode($value['name']));
		}

		return ($result_encoded);

	}


	/**
	 * @param $cc string Country Code
	 * @return string Country name
	 */
	public static function getCountryName($cc) {
		$DB = new \DB\SQL('sqlite:' . dirname(__FILE__) . DIRECTORY_SEPARATOR . '../sql/mysa_geo_countries.sqlite');
		$result = $DB->exec('SELECT name FROM mysa_geo_countries WHERE country_code=? LIMIT 1', $cc, 900);
		if (empty($result)) return '';
		return ($result[0]['name']);
	}


	/**
	 * @param $cc string Country Code
	 * @param $rc string Region Code
	 * @return string Region name
	 */
	public static function getRegionName($cc, $rc) {
		$DB = new \DB\SQL('sqlite:' . dirname(__FILE__) . DIRECTORY_SEPARATOR . '../sql/mysa_geo_regions.sqlite');
		$result = $DB->exec('SELECT name FROM mysa_geo_regions WHERE country_code=:cc AND region_code=:rc LIMIT 1', array(':cc' => $cc, ':rc' => $rc), 900);
		if (empty($result)) return '';
		return ($result[0]['name']);
	}

}