From 423b949bb72ac3ed7afa936a849752a400147382 Mon Sep 17 00:00:00 2001
From: Katrin Fischer <Katrin.Fischer.83@web.de>
Date: Thu, 12 Dec 2013 00:54:08 +0100
Subject: [PATCH] Bug 11279: Improve how a new quote of the day is selected

If there is a gap in the id sequence for the quotes table, it
is possible that no new quote will be selected. This will happen
particularly when a lot of the older quotes with low ids have been
deleted.

This patch improves the selection of a new quote.

To test:
- Load sample quotes
- Delete the first half of the quotes.
  Note: With 34 quotes, delete the quotes with ids from 1-17
- Activate the QuoteOfTheDay system preference
- Check if a quote is displayed in OPAC
- Reload the page a few times, no quote should be displayed
  Note: make sure you don't have a quote with the current
  date in your quotes table before running those tests
- Run 'perl t/db_dependent/Koha.t'
  Note: requires sample quotes!
- Apply patch
- Reload the OPAC start page
- Verify a quote was now picked
- Run 'perl t/db/dependent/Koha.t' again - all tests should still pass

Signed-off-by: Galen Charlton <gmc@esilibrary.com>

Patch modified to use "LIMIT 1 OFFSET ?" rather than "LIMIT ?, 1"; the
latter construction does not work in PostgreSQL.

Signed-off-by: Jonathan Druart <jonathan.druart@biblibre.com>
---
 C4/Koha.pm |   10 +++++++---
 1 file changed, 7 insertions(+), 3 deletions(-)

diff --git a/C4/Koha.pm b/C4/Koha.pm
index 937ac5f..f3ffcc3 100644
--- a/C4/Koha.pm
+++ b/C4/Koha.pm
@@ -29,6 +29,7 @@ use Koha::DateUtils qw(dt_from_string);
 use Memoize;
 use DateTime::Format::MySQL;
 use autouse 'Data::Dumper' => qw(Dumper);
+use DBI qw(:sql_types);
 
 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK $DEBUG);
 
@@ -1443,11 +1444,14 @@ sub GetDailyQuote {
         my $range = ($sth->fetchrow_array)[0];
         if ($range > 1) {
             # chose a random id within that range if there is more than one quote
-            my $id = int(rand($range));
+            my $offset = int(rand($range));
             # grab it
-            $query = 'SELECT * FROM quotes WHERE id = ?;';
+            $query = 'SELECT * FROM quotes ORDER BY id LIMIT 1 OFFSET ?';
             $sth = C4::Context->dbh->prepare($query);
-            $sth->execute($id);
+            # see http://www.perlmonks.org/?node_id=837422 for why
+            # we're being verbose and using bind_param
+            $sth->bind_param(1, $offset, SQL_INTEGER);
+            $sth->execute();
         }
         else {
             $query = 'SELECT * FROM quotes;';
-- 
1.7.10.4