View | Details | Raw Unified | Return to bug 16371
Collapse All | Expand All

(-)a/Koha/Quote.pm (-40 / +26 lines)
Lines 23-28 use DBI qw(:sql_types); Link Here
23
use Koha::Database;
23
use Koha::Database;
24
use Koha::DateUtils qw(dt_from_string);
24
use Koha::DateUtils qw(dt_from_string);
25
use Koha::Exceptions::UnknownProgramState;
25
use Koha::Exceptions::UnknownProgramState;
26
use Koha::Quotes;
26
27
27
use base qw(Koha::Object);
28
use base qw(Koha::Object);
28
29
Lines 46-60 Currently supported options are: Link Here
46
'random'    Select a random quote
47
'random'    Select a random quote
47
noop        When no option is passed in, this sub will return the quote timestamped for the current day
48
noop        When no option is passed in, this sub will return the quote timestamped for the current day
48
49
49
The function returns an anonymous hash following this format:
50
51
        {
52
          'source' => 'source-of-quote',
53
          'timestamp' => 'timestamp-value',
54
          'text' => 'text-of-quote',
55
          'id' => 'quote-id'
56
        };
57
58
=cut
50
=cut
59
51
60
# This is definitely a candidate for some sort of caching once we finally settle caching/persistence issues...
52
# This is definitely a candidate for some sort of caching once we finally settle caching/persistence issues...
Lines 62-108 The function returns an anonymous hash following this format: Link Here
62
54
63
sub get_daily_quote {
55
sub get_daily_quote {
64
    my ($self, %opts) = @_;
56
    my ($self, %opts) = @_;
65
    my $dbh = C4::Context->dbh;
57
66
    my $query = '';
67
    my $sth = undef;
68
    my $quote = undef;
58
    my $quote = undef;
59
    my $parser = Koha::Database->new->schema->storage->datetime_parser;
60
    my $dt = $parser->format_datetime(dt_from_string);
61
69
    if ($opts{'id'}) {
62
    if ($opts{'id'}) {
70
        $query = 'SELECT * FROM quotes WHERE id = ?';
63
        $quote = $self->find({ id => $opts{'id'} });
71
        $sth = $dbh->prepare($query);
72
        $sth->execute($opts{'id'});
73
        $quote = $sth->fetchrow_hashref();
74
    }
64
    }
75
    elsif ($opts{'random'}) {
65
    elsif ($opts{'random'}) {
76
        # Fall through... we also return a random quote as a catch-all if all else fails
66
        # Fall through... we also return a random quote as a catch-all if all else fails
77
    }
67
    }
78
    else {
68
    else {
79
        $query = 'SELECT * FROM quotes WHERE timestamp LIKE CONCAT(CURRENT_DATE,\'%\') ORDER BY timestamp DESC LIMIT 0,1';
69
        $quote = Koha::Quotes->search(
80
        $sth = $dbh->prepare($query);
70
            {
81
        $sth->execute();
71
                timestamp => { -like => "$dt%" },
82
        $quote = $sth->fetchrow_hashref();
72
            },
73
            {
74
                order_by => { -desc => 'timestamp' },
75
                rows => 1,
76
            }
77
        )->single;
83
    }
78
    }
84
    unless ($quote) {        # if there are not matches, choose a random quote
79
    unless ($quote) {        # if there are not matches, choose a random quote
85
        # get a list of all available quote ids
80
        my $range = Koha::Quotes->search->count;
86
        $sth = C4::Context->dbh->prepare('SELECT count(*) FROM quotes;');
87
        $sth->execute;
88
        my $range = ($sth->fetchrow_array)[0];
89
        # chose a random id within that range if there is more than one quote
90
        my $offset = int(rand($range));
81
        my $offset = int(rand($range));
91
        # grab it
82
        $quote = Koha::Quotes->search(
92
        $query = 'SELECT * FROM quotes ORDER BY id LIMIT 1 OFFSET ?';
83
            {},
93
        $sth = C4::Context->dbh->prepare($query);
84
            {
94
        # see http://www.perlmonks.org/?node_id=837422 for why
85
                order_by => 'id',
95
        # we're being verbose and using bind_param
86
                rows => 1,
96
        $sth->bind_param(1, $offset, SQL_INTEGER);
87
                offset => $offset,
97
        $sth->execute();
88
            }
98
        $quote = $sth->fetchrow_hashref();
89
        )->single;
90
99
        # update the timestamp for that quote
91
        # update the timestamp for that quote
100
        $query = 'UPDATE quotes SET timestamp = ? WHERE id = ?';
92
        $quote->update({ timestamp => $dt });
101
        $sth = C4::Context->dbh->prepare($query);
102
        $sth->execute(
103
            DateTime::Format::MySQL->format_datetime( dt_from_string() ),
104
            $quote->{'id'}
105
        );
106
    }
93
    }
107
    return $quote;
94
    return $quote;
108
}
95
}
109
- 

Return to bug 16371