fix biblionumber

[google-map-tiles.git] / koha.pl

diff --git a/koha.pl b/koha.pl
index 1394cea..7e34821 100755 (executable)
--- a/koha.pl
+++ b/koha.pl
@@ -12,22 +12,9 @@ my $dbh      = DBI->connect ( "dbi:Pg:dbname=koha" , "dpavlin" , "" , { AutoCommit =
 my $point      = param('POINT') ;
 my $zoom       = param('ZOOM') ;
 
-my $lat                = 0 ;
-my $lng                = 0 ;
-my $latpix     = 0 ;
-my $lngpix     = 0 ;
-
-my $volpnt     = '' ;
-my $vlat       = 0 ;
-my $vlng       = 0 ;
-my $vlatpix    = 0 ;
-my $vlngpix    = 0 ;
-
-my $value      = '' ;
 my $maxpix     = 15 ;                                  # Maximum pixels between click and point
-my $descript   = '' ;
-my $x          = '' ;
-my $i          = 0 ;
+
+my $limit_books = 100; # max. for one click
 
 print qq{Content-type: text/xml\r\n\r\n};
 print qq{<?xml version="1.0" encoding="UTF-8"?>\n} ;
@@ -43,22 +30,32 @@ if ( !$point )
 
 if ( $point =~ /(.*),(.*)/ )
 {
- $lat  = $1 ;
- $lng  = $2 ;
+ my $lat       = $1 ;
+ my $lng       = $2 ;
 
- $value = &Google_Tile_Factors($zoom) ;                                        # Calculate Tile Factors
+ my $value = &Google_Tile_Factors($zoom) ;                                     # Calculate Tile Factors
 
- ($latpix,$lngpix) = &Google_Coord_to_Pix( $value, $lat, $lng ) ;      # Convert coordinate to pixel location
+ my ($latpix,$lngpix) = &Google_Coord_to_Pix( $value, $lat, $lng ) ;   # Convert coordinate to pixel location
  
- $x = "select city,country,count,point,point'($lat,$lng)' <-> point as distance from geo_count order by distance limit 1" ;
-
- if ( my ($city,$country,$count,$volpnt,$i) = $dbh->selectrow_array($x) )      # Got one
+ my $sql = qq{
+select
+       city_koha
+       ,country
+       ,count
+       ,point
+       ,point'($lat,$lng)' <-> point as distance
+from geo_count
+order by distance
+limit 1
+ };
+
+ if ( my ($city,$country,$count,$volpnt,$i) = $dbh->selectrow_array($sql) )    # Got one
  {
   $volpnt =~ /\((.*),(.*)\)/ ; 
-  $vlat = $1 ;
-  $vlng = $2 ;
+  my $vlat = $1 ;
+  my $vlng = $2 ;
 
-  ($vlatpix,$vlngpix) = &Google_Coord_to_Pix( $value, $vlat, $vlng ) ; # Convert coordinate to pixel location
+  my ($vlatpix,$vlngpix) = &Google_Coord_to_Pix( $value, $vlat, $vlng ) ;      # Convert coordinate to pixel location
 
   if ( sqrt(($vlatpix -        $latpix)**2  + ($vlngpix - $lngpix)**2) > $maxpix )
   {
@@ -69,19 +66,26 @@ if ( $point =~ /(.*),(.*)/ )
 
        my $sth = $dbh->prepare(qq{
 select
-       author, title, bi.biblionumber
+       author, title, max(bi.biblionumber) as biblionumber, count(title)
 from geo_city c
 join geo_biblioitems bi on bi.city = c.city_koha
 join biblio b on b.biblionumber = bi.biblionumber
-where c.city = ? and country = ?
-order by timestamp
-limit 100
+where c.city_koha = ? and country = ?
+group by author, title
+order by min(timestamp)
+limit $limit_books
        });
        $sth->execute( $city, $country );
 
-       $count = $sth->rows if $sth->rows > $count;
+       my $rows = $sth->rows;
+       if ( $rows == $limit_books ) {
+               $rows = "more than $rows";
+               $rows = $count if $count > $rows;
+       };
+       my $books = 'books';
+       $books = 'book' if $rows == 1;
 
-       my $descript = "<b>$city</b> <em>$country</em> $count items\n<ol>";
+       my $descript = "<b>$city</b> <em>$country</em> <small>$rows $books</small>\n<ol>";
 
        while ( my $row = $sth->fetchrow_hashref ) {
                $descript .= sprintf qq|<li><a target="koha" href="http://koha.ffzg.hr/cgi-bin/koha/opac-detail.pl?biblionumber=%d">%s</a> %s\n|,