From 4b66ac3af6062b1900825d14e6aba6810b0e944b Mon Sep 17 00:00:00 2001
From: Dobrica Pavlinusic <dpavlin@rot13.org>
Date: Sun, 3 Apr 2011 19:09:53 +0200
Subject: [PATCH] extract data directly from database for huge speedup

---
 koha.pl | 39 +++++++++++++++++++++------------------
 1 file changed, 21 insertions(+), 18 deletions(-)

diff --git a/koha.pl b/koha.pl
index 72f7681..3970c5e 100755
--- a/koha.pl
+++ b/koha.pl
@@ -7,9 +7,8 @@ use CGI qw/:standard *table/;
 use strict ;
 use DBI ;
 use USNaviguide_Google_Tiles ;
-use XML::FeedPP;
 
-my $dbh 	= DBI->connect ( "dbi:Pg:dbname=koha" , "" , "" , { AutoCommit => 1 } ) ;
+my $dbh 	= DBI->connect ( "dbi:Pg:dbname=koha" , "dpavlin" , "" , { AutoCommit => 1 } ) ;
 my $point	= param('POINT') ;
 my $zoom	= param('ZOOM') ;
 
@@ -52,7 +51,7 @@ if ( $point =~ /(.*),(.*)/ )
 
  ($latpix,$lngpix) = &Google_Coord_to_Pix( $value, $lat, $lng ) ;	# Convert coordinate to pixel location
  
- $x = "select name,descript,volpnt,point'($lat,$lng)' <-> volpnt as distance from gvp_world order by distance limit 1" ;
+ $x = "select city,count,point,point'($lat,$lng)' <-> point as distance from geo_count order by distance limit 1" ;
 
  if ( ($name,$descript,$volpnt,$i) = $dbh->selectrow_array($x) )	# Got one
  {
@@ -69,23 +68,27 @@ if ( $point =~ /(.*),(.*)/ )
   } else								# Good point found
   {
 
-	my $hash = eval $descript;
-
-	my $feed = XML::FeedPP->new( "http://koha.ffzg.hr/cgi-bin/koha/opac-search.pl?idx=pl&format=rss2&q=$name" );
-
-	my @links;
-	foreach my $item ( $feed->get_item ) {
-		push @links, sprintf qq|<li><a target="koha" href="%s">%s</a> %s|,
-			$item->link, $item->title, $item->description
+	my $sth = $dbh->prepare(qq{
+select
+	author, title, bi.biblionumber
+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 = ?
+order by timestamp
+limit 100
+	});
+	$sth->execute( $name );
+
+	$descript = "<b>$name</b> $descript items\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|,
+			$row->{biblionumber}, $row->{title}, $row->{author}
 		;
 	}
-	$descript = join("\n"
-		, "<b>$name</b>"
-		, " " . @links
-		, "<ol>"
-		, @links
-		, "</ol>"
-	);
+
+	$descript .= "\n</ol>\n";
 
    print qq!<info error = "" name = "$name" lat="$vlat" lng="$vlng">\n! ;
    print qq! <description><\![CDATA[$descript]]></description>\n! ;
-- 
2.20.1