#!/usr/bin/env perl use strict; use DBI; use Geo::IP; # Database connection. my ($dsn) = "DBI:mysql:database=eprintstats;host=localhost"; my ($user_name) = "eprintstatspriv"; my ($password) = "AuldGrizzel"; my ( $connect, $query, %types, $stat, $row, $num_rows, $vtype ); # Geolocation database. my ($gi); my ($gidb) = '/usr/local/share/GeoIP/GeoLiteCity.dat'; # Miscellaneous variable. my (%cities); my ($num_IPs) = -1; my ($num_hits) = 0; my ( $ip, $count, $location ); my ( $lat, $long, $city ) = ( 0, 0, '' ); $gi = Geo::IP->open( $gidb, GEOIP_STANDARD ) or die "Unable to open GeoIP database $gidb\n"; $connect = DBI->connect( $dsn, $user_name, $password, { RaiseError => 1 } ); $types{'download'} = $types{'abstract'} = 0; $query = "SELECT ip, view_type, COUNT(*) AS count FROM view GROUP BY ip, view_type ORDER BY count DESC" . ( ( $num_IPs > 0 ) ? " LIMIT $num_IPs" : '' ); $stat = $connect->prepare($query); $stat->execute(); $num_rows = $stat->rows; if ( $num_rows > 0 ) { $num_IPs = $num_rows if ( $num_IPs < 1 ); while ( $row = $stat->fetchrow_hashref() ) { $ip = $row->{'ip'}; $count = $row->{'count'}; $vtype = $row->{'view_type'}; $location = $gi->record_by_addr($ip); if ( defined($location) ) { $lat = $location->latitude; $long = $location->longitude; $city = ( ( $location->city eq '' ) ? "Unknown ($lat, $long)" : $location->city ); if ( !defined( $cities{$city} ) ) { $cities{$city}{'lat'} = $lat; $cities{$city}{'long'} = $long; $cities{$city}{'abstract'} = 0; $cities{$city}{'download'} = 0; } $cities{$city}{$vtype} += $count; } } # Need to wait until we have all the counts before writing the data out. print '<?xml version="1.0"?>' . "\n"; print "<markers>\n"; foreach $city ( keys %cities ) { print '<marker city="' . $city . '" lat="' . $cities{$city}{'lat'} . '" lng="' . $cities{$city}{'long'} . '" abs="' . $cities{$city}{'abstract'} . '" dl="' . $cities{$city}{'download'} . '" />' . "\n"; } print "</markers>\n"; } $stat->finish(); $connect->disconnect(); sub round { my ($n) = shift; return int( $n + 0.5 * ( $n <=> 0 ) ); }