X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=koha-import.pl;h=b2ba4d1a91b9ea158e41a4d0c3ac8b0e64bc8232;hb=8e1b6c231b7ff790d7d166f34494c1f161487b75;hp=8bb4acf2305b2a834a3d9429ddc2caad123d1c0d;hpb=38ff7a6edf4f69525bd094908b8a715b7514dcef;p=google-map-tiles.git

diff --git a/koha-import.pl b/koha-import.pl
index 8bb4acf..b2ba4d1 100755
--- a/koha-import.pl
+++ b/koha-import.pl
@@ -3,6 +3,8 @@ use warnings;
 use strict;
 
 use DBI;
+use XML::Simple;
+use Data::Dump qw(dump);
 
 our $koha_dsn    = 'dbi:oursql:dbname=koha';
 our $koha_user   = 'kohaadmin';
@@ -54,5 +56,131 @@ sub fetch_table {
 	} while $offset;
 }
 
-fetch_table 'biblio' => 'biblionumber' ;
-fetch_table 'biblioitems' => 'biblioitemnumber' ;
+#fetch_table 'biblio' => 'biblionumber' ;
+#fetch_table 'biblioitems' => 'biblioitemnumber' ;
+
+sub geo_biblioitems {
+
+warn "# drop geo_biblioitems";
+eval { $g_dbh->do(qq{ drop table geo_biblioitems }) };
+
+warn "# create geo_biblioitems";
+$g_dbh->do(qq{
+create table geo_biblioitems (
+	biblioitemnumber integer not null references biblioitems(biblioitemnumber),
+	biblionumber integer not null references biblio(biblionumber),
+	city text
+)
+});
+my $sth_insert = $g_dbh->prepare(qq{
+	insert into geo_biblioitems values (?,?,?)
+});
+
+warn "# select bibiloitems";
+my $sth = $g_dbh->prepare(qq{
+SELECT
+	biblioitemnumber, biblionumber, isbn, issn, marcxml
+from biblioitems
+});
+$sth->execute;
+
+warn $sth->rows, " rows\n";
+
+sub warn_dump {
+	warn dump @_,$/ if $ENV{DEBUG};
+}
+
+my $i = 0;
+
+while ( my $row = $sth->fetchrow_hashref ) {
+	my $xml = XMLin( delete $row->{marcxml}, ForceArray => [ 'datafield', 'subfield' ] );
+
+	warn_dump($row, $xml);
+
+	my @tag_260 = grep { $_->{tag} eq '260' } @{ $xml->{datafield} };
+
+	next unless @tag_260;
+
+	warn_dump @tag_260 if $ENV{DEBUG};
+
+	foreach my $sf ( @{ $tag_260[0]->{subfield} } ) {
+		$row->{ 'tag_260_' . $sf->{code} } = $sf->{content};
+	}
+
+	$row->{city} = $row->{tag_260_a};
+	$row->{city} =~ s/['"]+//g;
+	$row->{city} =~ s/\s*\[etc.*\].*$//;
+	$row->{city} =~ s/\s*[:]\s*$//;
+	$row->{city} =~ s/[\[\]]+//g;
+
+	warn_dump $row;
+
+	warn "# $i ", $row->{city}, $/ if $i++ % 100 == 0;
+
+	$sth_insert->execute( $row->{biblioitemnumber}, $row->{biblionumber}, $row->{city} );
+}
+
+} # geo_biblioitems;
+
+#geo_biblioitems;
+
+eval {
+$g_dbh->do(qq{
+create table geo_city (
+	city_koha text not null primary key,
+	city text,
+	country text,
+	county text,
+	lat float,
+	lng float,
+	radius int,
+	dump text
+)
+});
+};
+
+my $sth_insert = $g_dbh->prepare(qq{insert into geo_city values (?,?,?,?,?,?,?,?)});
+
+my $sth = $g_dbh->prepare(qq{
+select count(*),city from geo_biblioitems where city not in (select city_koha from geo_city) group by city order by count(city) desc
+});
+$sth->execute;
+
+warn $sth->rows, " cities to geolocate";
+
+use Geo::Coder::PlaceFinder;
+
+my $geocoder = Geo::Coder::PlaceFinder->new( appid => 'eQWEGC58' );
+
+while( my $row = $sth->fetchrow_hashref ) {
+	my $location  = $geocoder->geocode(location => $row->{city});
+	warn dump($location);
+	$sth_insert->execute(
+		$row->{city}
+		, $location->{city},
+		, $location->{country}
+		, $location->{county}
+		, $location->{latitude}
+		, $location->{longitude}
+		, $location->{radius}
+		, dump($location)
+	);
+}
+
+eval { $g_dbh->do(qq{ drop table geo_count }) };
+$g_dbh->do(qq{
+
+select
+	count(biblionumber)
+	,point(lat,lng)
+	,geo_city.city
+	,geo_city.city_koha
+	,country
+into geo_count
+from geo_biblioitems
+join geo_city on city_koha = geo_biblioitems.city
+where length(geo_city.city) > 1
+group by geo_city.city, country, lat, lng, city_koha
+order by count(biblionumber) desc
+
+});