From f8617bf0c55a2cd242037fcace18783216d503e9 Mon Sep 17 00:00:00 2001
From: Emmi Takkinen <emmi.takkinen@outlook.com>
Date: Tue, 7 Jul 2020 15:45:17 +0300
Subject: [PATCH 2/3] Bug 16371: Rewrite get_daily_quote

Sponsored-by: Koha-Suomi Oy
---
 Koha/Quote.pm | 65 +++++++++++++++++++++------------------------------
 1 file changed, 26 insertions(+), 39 deletions(-)

diff --git a/Koha/Quote.pm b/Koha/Quote.pm
index 244aea0673..e74aceac65 100644
--- a/Koha/Quote.pm
+++ b/Koha/Quote.pm
@@ -23,6 +23,7 @@ use DBI qw(:sql_types);
 use Koha::Database;
 use Koha::DateUtils qw(dt_from_string);
 use Koha::Exceptions::UnknownProgramState;
+use Koha::Quotes;
 
 use base qw(Koha::Object);
 
@@ -46,15 +47,6 @@ Currently supported options are:
 'random'    Select a random quote
 noop        When no option is passed in, this sub will return the quote timestamped for the current day
 
-The function returns an anonymous hash following this format:
-
-        {
-          'source' => 'source-of-quote',
-          'timestamp' => 'timestamp-value',
-          'text' => 'text-of-quote',
-          'id' => 'quote-id'
-        };
-
 =cut
 
 # This is definitely a candidate for some sort of caching once we finally settle caching/persistence issues...
@@ -62,47 +54,42 @@ The function returns an anonymous hash following this format:
 
 sub get_daily_quote {
     my ($self, %opts) = @_;
-    my $dbh = C4::Context->dbh;
-    my $query = '';
-    my $sth = undef;
+
     my $quote = undef;
+    my $parser = Koha::Database->new->schema->storage->datetime_parser;
+    my $dt = $parser->format_datetime(dt_from_string);
+
     if ($opts{'id'}) {
-        $query = 'SELECT * FROM quotes WHERE id = ?';
-        $sth = $dbh->prepare($query);
-        $sth->execute($opts{'id'});
-        $quote = $sth->fetchrow_hashref();
+        $quote = $self->find({ id => $opts{'id'} });
     }
     elsif ($opts{'random'}) {
         # Fall through... we also return a random quote as a catch-all if all else fails
     }
     else {
-        $query = 'SELECT * FROM quotes WHERE timestamp LIKE CONCAT(CURRENT_DATE,\'%\') ORDER BY timestamp DESC LIMIT 0,1';
-        $sth = $dbh->prepare($query);
-        $sth->execute();
-        $quote = $sth->fetchrow_hashref();
+        $quote = Koha::Quotes->search(
+            {
+                timestamp => { -like => "$dt%" },
+            },
+            {
+                order_by => { -desc => 'timestamp' },
+                rows => 1,
+            }
+        )->single;
     }
     unless ($quote) {        # if there are not matches, choose a random quote
-        # get a list of all available quote ids
-        $sth = C4::Context->dbh->prepare('SELECT count(*) FROM quotes;');
-        $sth->execute;
-        my $range = ($sth->fetchrow_array)[0];
-        # chose a random id within that range if there is more than one quote
+        my $range = Koha::Quotes->search->count;
         my $offset = int(rand($range));
-        # grab it
-        $query = 'SELECT * FROM quotes ORDER BY id LIMIT 1 OFFSET ?';
-        $sth = C4::Context->dbh->prepare($query);
-        # 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();
-        $quote = $sth->fetchrow_hashref();
+        $quote = Koha::Quotes->search(
+            {},
+            {
+                order_by => 'id',
+                rows => 1,
+                offset => $offset,
+            }
+        )->single;
+
         # update the timestamp for that quote
-        $query = 'UPDATE quotes SET timestamp = ? WHERE id = ?';
-        $sth = C4::Context->dbh->prepare($query);
-        $sth->execute(
-            DateTime::Format::MySQL->format_datetime( dt_from_string() ),
-            $quote->{'id'}
-        );
+        $quote->update({ timestamp => $dt });
     }
     return $quote;
 }
-- 
2.17.1