public function getLocation($ip, $ipNumber)
{
$conn = DB::get_conn();
$addressType = IpToLocation::addr_type($ip);
$sql = "SELECT\n\t\t\t\t\t\t`ip_start` AS IPFrom,\n\t\t\t\t\t\t`ip_end` AS IPTo,\n\t\t\t\t\t\t`country` AS Country,\n\t\t\t\t\t\t`stateprov` AS Region,\n\t\t\t\t\t\t`city` AS City\n\t\t\t\t \tFROM\n\t\t\t\t\t\t`dbip_lookup`\n\t\t\t\t\tWHERE\n\t\t\t\t\t\taddr_type = '{$addressType}'\n\t\t\t\t\t\tAND ip_start <= '" . $conn->escapeString($ipNumber) . "'\n\t\t\t\t\tORDER BY\n\t\t\t\t\t\tip_start DESC\n\t\t\t\t\tLIMIT 1";
$res = DB::query($sql);
while ($row = $res->nextRecord()) {
$location = new IpToLocation($row);
$this->debugLocation($location);
return $location;
}
}
public static function GetLocation()
{
if ($strIP = self::IPAddress()) {
$iNumber = self::IPAddressToIPNumber($strIP);
if (self::GetProvider() == 'IPDBCOM') {
return IpToLocation::get()->filter(array('IPFrom:LessThanOrEqual' => $iNumber))->sort('IPFrom DESC')->first();
} else {
return IpToLocation::get()->filter(array('IPFrom:LessThanOrEqual' => $iNumber, 'IPTo:GreaterThanOrEqual' => $iNumber))->first();
}
}
return null;
}
public function run($request)
{
$strCSVPath = CONTINENTAL_CONTENT_PATH . '/code/ThirdParty/dbip-city.csv';
if (!file_exists($strCSVPath)) {
echo "<p>I cant find the dbip-city.csv file to import any data.<br>\n\t\t\t\tPlease download any database from <a href='https://db-ip.com/db/'>https://db-ip.com/db/</a>.<br>\n\t\t\t\tNOTE: It's adviced to edit the DB to only include the countries you want to handle, it contains 2 million records!!!<br>\n\t\t\t\tOr make a CSV contain these columns<br>\n\t\t\t\t`IPFrom`,`IPTo`,`Country`,`Region`,`City`\n\t\t\t\t</p>";
//"0.0.0.0","0.255.255.255","US","California","Los Angeles"
} else {
if (!isset($_REQUEST['confirm'])) {
$strLink = Director::baseURL() . 'dev/tasks/ImportDBIPcom?confirm=1';
echo "<p>CAUTION!!!<br>\n\t\t\t\t\tPlease confirm your action<br>\n\t\t\t\t\t<a href='{$strLink}'>I confirm the action</a><br>\n\t\t\t\t\t<a href='{$strLink}&emptydb=1'>I confirm the action, please empty the DB before you import</a>\n\t\t\t\t\t</p>";
} else {
increase_time_limit_to();
//
// this needs varbinary fields so create the table here
//
$arr = DB::table_list();
if (!in_array('dbip_lookup', $arr)) {
$strSQL = "CREATE TABLE `dbip_lookup` (\n\t\t\t\t\t `addr_type` enum('ipv4','ipv6') NOT NULL,\n\t\t\t\t\t `ip_start` varbinary(16) NOT NULL,\n\t\t\t\t\t `ip_end` varbinary(16) NOT NULL,\n\t\t\t\t\t `country` char(2) NOT NULL,\n\t\t\t\t\t `stateprov` varchar(80) NOT NULL,\n\t\t\t\t\t `city` varchar(80) NOT NULL,\n\t\t\t\t\t PRIMARY KEY (`ip_start`)\n\t\t\t\t\t);";
DB::query($strSQL);
}
if (isset($_REQUEST['emptydb'])) {
DB::query('TRUNCATE `dbip_lookup`;');
}
$conn = DB::get_conn();
if ($conn->supportsTransactions()) {
$conn->transactionStart();
}
$handle = fopen($strCSVPath, "r");
if ($handle) {
while (($line = fgets($handle)) !== false) {
$line = str_replace('","', '___', $line);
$line = str_replace('"', '', $line);
$arrParts = Convert::raw2sql(explode("___", $line));
$vals = array('addr_type' => "'" . IpToLocation::addr_type($arrParts[0]) . "'", 'ip_start' => "'" . $conn->escapeString(ContinentalContentUtils::IPAddressToIPNumber($arrParts[0])) . "'", 'ip_end' => "'" . $conn->escapeString(ContinentalContentUtils::IPAddressToIPNumber($arrParts[1])) . "'", 'country' => "'" . $arrParts[2] . "'", 'stateprov' => "'" . $arrParts[3] . "'", 'city' => "'" . $arrParts[4] . "'");
$fields = array_keys($vals);
DB::query('INSERT INTO `dbip_lookup` (`' . implode('`,`', $fields) . '`) VALUES (' . implode(',', $vals) . ')');
}
fclose($handle);
} else {
echo 'Error opening file';
}
if ($conn->supportsTransactions()) {
$conn->transactionEnd();
}
}
}
}
public function getLocation($ip, $ipNumber)
{
$location = IpToLocation::get()->filter(array('IPFrom:LessThanOrEqual' => $ipNumber, 'IPTo:GreaterThanOrEqual' => $ipNumber, 'Type' => ContinentalContentUtils::IPType($ip) == 'ipv4' ? 'IpV4' : 'IpV6'))->first();
$this->debugLocation($location);
return $location;
}
