From 1db954bdea968aa62fd3c0edcc2473638c983c16 Mon Sep 17 00:00:00 2001 From: Martin Persson Date: Fri, 18 Dec 2015 11:16:08 +0100 Subject: [PATCH] Bug 14272: Show single news item (backend) Updates: * Date formatting added to get_opac_new() * Proper filtering by way of GetNewsToDisplay Sponsored-by: Halland County Library Signed-off-by: Aleisha --- C4/NewsChannels.pm | 46 ++++++++++++++++++++++++++++++++++------------ 1 file changed, 34 insertions(+), 12 deletions(-) diff --git a/C4/NewsChannels.pm b/C4/NewsChannels.pm index cbcc702..f624f1f 100644 --- a/C4/NewsChannels.pm +++ b/C4/NewsChannels.pm @@ -125,15 +125,21 @@ sub get_opac_new { my ($idnew) = @_; my $dbh = C4::Context->dbh; my $query = q{ - SELECT opac_news.*,branches.branchname + SELECT opac_news.*,branches.branchname, + timestamp AS newdate, + borrowers.title AS author_title, + borrowers.firstname AS author_firstname, + borrowers.surname AS author_surname FROM opac_news LEFT JOIN branches ON opac_news.branchcode=branches.branchcode + LEFT JOIN borrowers on borrowers.borrowernumber = opac_news.borrowernumber WHERE opac_news.idnew = ?; }; my $sth = $dbh->prepare($query); $sth->execute($idnew); my $data = $sth->fetchrow_hashref; $data->{$data->{'lang'}} = 1 if defined $data->{lang}; + $data->{newdate} = output_pref({ dt => dt_from_string( $data->{newdate} ), dateonly => 1 }); $data->{expirationdate} = output_pref({ dt => dt_from_string( $data->{expirationdate} ), dateonly => 1 }); $data->{timestamp} = output_pref({ dt => dt_from_string( $data->{timestamp} ), dateonly => 1 }) ; return $data; @@ -189,21 +195,35 @@ sub get_opac_news { =cut sub GetNewsToDisplay { - my ($lang,$branch) = @_; + my ($lang,$branch,$time,$id) = @_; my $dbh = C4::Context->dbh; # SELECT *,DATE_FORMAT(timestamp, '%d/%m/%Y') AS newdate + my @values; my $query = q{ SELECT opac_news.*,timestamp AS newdate, - borrowers.title AS author_title, + borrowers.title AS author_title, borrowers.firstname AS author_firstname, - borrowers.surname AS author_surname - FROM opac_news - LEFT JOIN borrowers on borrowers.borrowernumber = opac_news.borrowernumber - WHERE ( - expirationdate >= CURRENT_DATE() - OR expirationdate IS NULL - OR expirationdate = '00-00-0000' - ) + borrowers.surname AS author_surname + FROM opac_news + LEFT JOIN borrowers on borrowers.borrowernumber = + opac_news.borrowernumber + WHERE (}; + + if ( defined $id ) { + $query .= q{ opac_news.idnew = ? } if defined $id; + push( @values, $id ); + } + + unless ( defined $time ) { + $query .= q{ AND } if defined $id; + $query .= q{ + expirationdate >= CURRENT_DATE() + OR expirationdate IS NULL + OR expirationdate = '00-00-0000' + } unless( defined $time and $time eq 'all' ); + } + + $query .= q{ ) AND DATE(timestamp) < DATE_ADD(CURDATE(), INTERVAL 1 DAY) AND (lang = '' OR lang = ?) AND (opac_news.branchcode IS NULL OR opac_news.branchcode = ?) @@ -213,7 +233,9 @@ sub GetNewsToDisplay { # by adding 1, that captures today correctly. my $sth = $dbh->prepare($query); $lang = $lang // q{}; - $sth->execute($lang,$branch); + push( @values, $lang ); + push( @values, $branch ); + $sth->execute(@values); my @results; while ( my $row = $sth->fetchrow_hashref ){ $row->{newdate} = output_pref({ dt => dt_from_string( $row->{newdate} ), dateonly => 1 }); -- 2.1.4