From a5d716bbf47bd0dd53865dc637a52a69539bc823 Mon Sep 17 00:00:00 2001 From: Mark Tompsett Date: Sun, 27 Jul 2014 21:39:53 -0400 Subject: [PATCH] [SIGNED-OFF] Bug 12167: Clean up GetNewsToDisplay This bug started out as a result of busting while in transitions of MySQL were happening in Ubuntu. However, I figured this fix should remove backtick MySQLisms as per http://wiki.koha-community.org/wiki/Coding_Guidelines#SQL6:_Backticks And clean up GetNewsToDisplay a bit. TEST PLAN --------- 1) News should function fine before applying any patches. 2) Apply all patches. 3) prove -v t/db_dependent/NewsChannels.t -- expecting all happy results. -- note the removal of backticks and the assumed typecasting of a timestamp to date. -- specified opac_news.timestamp to make sure that it would more likely parse as a fieldname and not as a keyword. Signed-off-by: Bernardo Gonzalez Kriegel All patches applied, test pass, cleaner code, no regressions found, no koha-qa errors --- C4/NewsChannels.pm | 10 +++++----- 1 file changed, 5 insertions(+), 5 deletions(-) diff --git a/C4/NewsChannels.pm b/C4/NewsChannels.pm index 47772ef..fc820d9 100644 --- a/C4/NewsChannels.pm +++ b/C4/NewsChannels.pm @@ -188,27 +188,27 @@ sub get_opac_news { sub GetNewsToDisplay { my ($lang,$branch) = @_; my $dbh = C4::Context->dbh; - # SELECT *,DATE_FORMAT(timestamp, '%d/%m/%Y') AS newdate my $query = q{ - SELECT *,timestamp AS newdate + SELECT *,opac_news.timestamp AS newdate FROM opac_news WHERE ( expirationdate >= CURRENT_DATE() OR expirationdate IS NULL OR expirationdate = '00-00-0000' ) - AND `timestamp` < CURRENT_DATE()+1 + AND CAST( opac_news.timestamp AS DATE ) <= CURRENT_DATE() AND (lang = '' OR lang = ?) AND (branchcode IS NULL OR branchcode = ?) ORDER BY number }; # expirationdate field is NOT in ISO format? - # timestamp has HH:mm:ss, CURRENT_DATE generates 00:00:00 - # by adding 1, that captures today correctly. + # casting timestamp to date truncates HH:mm:ss + # changing to <= CURRENT_DATE() today is captured. my $sth = $dbh->prepare($query); $lang = $lang // q{}; $sth->execute($lang,$branch); my @results; while ( my $row = $sth->fetchrow_hashref ){ + # this will type cast and respect system preferences. $row->{newdate} = format_date($row->{newdate}); push @results, $row; } -- 1.7.9.5