#!/usr/bin/perl
######################################################################
#
#  Cycle through all requests and retrieve the ip numbers
#  then get the country name and code and re-insert into record.
#  This is supplemental to the eprints-usage.php script as the 
#  php Geo module doesn't convert as many ip nos to country data.
#  But maybe that was just the version used. If the php script turns out
#  to retrieve the info as well as this perl script then uncomment the
#  appropriate section from that script.
#
######################################################################

#
# Edit the dsn and add username password
# Run it after eprints-usage.php runs
#

use DBI;
use strict;
use Geo::IPfree;

my ($dsn) = "DBI:mysql:eprintstats:localhost";
my ($user_name) = "eprintstatspriv";
my ($password) = "AuldGrizzel";
my ($dbh,$sth);
my ($hashref);
my ($country,$country_name);

$dbh = DBI->connect($dsn, $user_name, $password, { RaiseError => 1 });
#$sth = $dbh->prepare("select id,ip from view limit 50");
$sth = $dbh->prepare("select id,ip from view where country_code = ''");
$sth->execute();
my ($query);
while ($hashref = $sth->fetchrow_hashref()) {
	
	($country,$country_name) = Geo::IPfree::LookUp($hashref->{"ip"});
	if($country_name ne '') {
		# Create sql to update table.
		$query = "UPDATE view set country_code = '" . $country . "', country_name = " . $dbh->quote($country_name) . " where id = " . $hashref->{"id"};
		$dbh->do($query);
		$query = '';
		$country_name = '';
		$country = '';
	}
}