From e122410f9b426aeb3141ecd7da7782be5672159d Mon Sep 17 00:00:00 2001
From: rangi <rangi>
Date: Sat, 6 Mar 2004 07:45:33 +0000
Subject: [PATCH] First cut of a script to scrap the average customer ratings
 for books from Amazon and store them in a ratings table in the Koha db
 ratings.sql is the table definition

My next task is to extend it to work for cd's and dvds as well.
---
 misc/amazonratings/get_ratings.pl | 63 +++++++++++++++++++++++++++++++
 misc/amazonratings/ratings.sql    | 18 +++++++++
 2 files changed, 81 insertions(+)
 create mode 100755 misc/amazonratings/get_ratings.pl
 create mode 100644 misc/amazonratings/ratings.sql

diff --git a/misc/amazonratings/get_ratings.pl b/misc/amazonratings/get_ratings.pl
new file mode 100755
index 0000000000..d1ab9b2cc3
--- /dev/null
+++ b/misc/amazonratings/get_ratings.pl
@@ -0,0 +1,63 @@
+#!/usr/bin/perl
+# get_ratings.pl
+#
+# A script to fetch the ratings of a given title, using the isbn number
+# Initially just books, but ill expand to handle dvd's and cd's as well
+
+# uses a new table, ratings, pipe the ratings.sql script into mysql to create the table.
+
+use warnings;
+use strict;
+use HTTP::Cookies;
+use LWP::UserAgent;
+use C4::Context;
+
+my $url="http://www.amazon.com/exec/obidos/search-handle-url/index%3Dbooks%26field-isbn%3D";
+
+my $dbh=C4::Context->dbh();
+
+my $query="SELECT isbn,biblioitemnumber,biblionumber FROM biblioitems";
+my $sth=$dbh->prepare($query);
+$sth->execute();
+while (my $data=$sth->fetchrow_hashref()){
+  $data->{'isbn'}=~ s/\-//g;
+    $data->{'isbn'}=~ s/ +//g;
+    
+# append isbn 
+# isbn must appear without spaces or -
+
+$url.=$data->{'isbn'};
+my $ua = LWP::UserAgent->new;
+my $content = $ua->get($url)->content;
+#print $content;
+
+
+my $rating;
+
+if ($content=~ /alt="(.*?) out of 5 stars"/){
+    $rating=$1;
+    
+	         }
+if ($rating){
+    # first check we dont already have a rating, if so, and its different update it
+    # otherwise insert a new rating
+    my $query2="SELECT * FROM ratings WHERE biblioitemnumber=?";
+    my $sth2=$dbh->prepare($query2);
+    $sth2->execute($data->{'biblioitemnumber'});
+    if (my $ratings=$sth2->fetchrow_hashref()){
+	if ($rating ne $ratings->{'rating'}){
+	    my $query3="UPDATE ratings SET rating=? WHERE biblioitemnumber=?";
+	    my $sth3=$dbh->prepare($query3);
+	    $sth3->execute($rating,$data->{'biblioitemnumber'});
+	    $sth3->finish();
+	    }
+	}
+    else {
+	my $query3="INSERT INTO ratings (rating,biblioitemnumber,biblionumber) VALUES (?,?,?)";
+	my $sth3=$dbh->prepare($query3);
+	$sth3->execute($rating,$data->{'biblioitemnumber'},$data->{'biblionumber'});
+	$sth3->finish();
+	}
+    $sth2->finish();
+    }
+    }
diff --git a/misc/amazonratings/ratings.sql b/misc/amazonratings/ratings.sql
new file mode 100644
index 0000000000..d287dee526
--- /dev/null
+++ b/misc/amazonratings/ratings.sql
@@ -0,0 +1,18 @@
+-- MySQL dump 8.21
+--
+-- Host: localhost    Database: Koha2
+---------------------------------------------------------
+-- Server version	3.23.49-log
+
+--
+-- Table structure for table 'ratings'
+--
+
+CREATE TABLE ratings (
+  biblioitemnumber int(11) NOT NULL default '0',
+  biblionumber int(11) default NULL,
+  rating varchar(10) default NULL,
+  modified timestamp(14) NOT NULL,
+  PRIMARY KEY  (biblioitemnumber)
+) TYPE=MyISAM;
+
-- 
2.39.2