From b68a77895a5dc280f5b3c9ec130fbb3ff82bf75f Mon Sep 17 00:00:00 2001
From: Mark Tompsett <mtompset@hotmail.com>
Date: Sun, 27 Jul 2014 21:39:53 -0400
Subject: [PATCH] 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.
---
 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