From 514f2b8efb5a2ae9a15a420579a0894b5cd54edc Mon Sep 17 00:00:00 2001
From: Mark Tompsett <mtompset@hotmail.com>
Date: Fri, 8 Aug 2014 19:55:53 -0400
Subject: [PATCH] [PASSED QA] Bug 12167: Made SQL even more ANSI SQL.

The comparison against '00-00-0000' is not possible under
PostgreSQL. By providing a database upgrade which replaces all
'0000-00-00' dates with NULL, this portion of the SQL query can
be removed.

Additionally, MySQL can handle CURRENT_DATE(), but PostgreSQL
barfs horribly. By removing the ()'s, it functions in both.

The NewsChannels.t specifically added an explanationdate IS NULL
case to catch what was not tested before.

TEST PLAN
---------
1) Apply patch.
2) ./installer/data/mysql/updatedatabase.pl
3) prove -v t/db_dependent/NewsChannels.t
   -- This will confirm that the modified GetNewsToDisplay
      function has not broken.

Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>

Update database runs without problem, test pass, no koha-qa errors.

Signed-off-by: Katrin Fischer <Katrin.Fischer.83@web.de>
---
 C4/NewsChannels.pm                     |  5 ++---
 installer/data/mysql/updatedatabase.pl |  7 +++++++
 t/db_dependent/NewsChannels.t          | 25 +++++++++++++++++++++++--
 3 files changed, 32 insertions(+), 5 deletions(-)

diff --git a/C4/NewsChannels.pm b/C4/NewsChannels.pm
index fc820d9..b6c7f00 100644
--- a/C4/NewsChannels.pm
+++ b/C4/NewsChannels.pm
@@ -194,15 +194,14 @@ sub GetNewsToDisplay {
      WHERE   (
         expirationdate >= CURRENT_DATE()
         OR    expirationdate IS NULL
-        OR    expirationdate = '00-00-0000'
      )
-     AND   CAST( opac_news.timestamp AS DATE ) <= CURRENT_DATE()
+     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?
        # casting timestamp to date truncates HH:mm:ss
-       # changing to <= CURRENT_DATE() today is captured.
+       # changing to <= CURRENT_DATE, today is captured.
     my $sth = $dbh->prepare($query);
     $lang = $lang // q{};
     $sth->execute($lang,$branch);
diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl
index de77bf3..9656181 100755
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -8613,6 +8613,13 @@ if ( CheckVersion($DBversion) ) {
     SetVersion($DBversion);
 }
 
+$DBversion = "3.17.00.XXX";
+if ( CheckVersion($DBversion) ) {
+    $dbh->do("UPDATE opac_news SET expirationdate=NULL WHERE expirationdate='0000-00-00';");
+    print "Upgrade to $DBversion done (Bug 12167: Clean up date and time type casting issues)\n";
+    SetVersion ($DBversion);
+}
+
 =head1 FUNCTIONS
 
 =head2 TableExists($table)
diff --git a/t/db_dependent/NewsChannels.t b/t/db_dependent/NewsChannels.t
index 332b396..6a817e0 100644
--- a/t/db_dependent/NewsChannels.t
+++ b/t/db_dependent/NewsChannels.t
@@ -3,7 +3,7 @@
 use Modern::Perl;
 use C4::Dates qw(format_date);
 use C4::Branch qw(GetBranchName);
-use Test::More tests => 12;
+use Test::More tests => 14;
 
 BEGIN {
     use_ok('C4::NewsChannels');
@@ -27,7 +27,7 @@ my $rv = add_opac_new();    # intentionally bad
 ok( $rv == 0, 'Correctly failed on no parameter!' );
 
 my $timestamp = '2000-01-01';
-my ( $timestamp1, $timestamp2 ) = ( $timestamp, $timestamp );
+my ( $timestamp1, $timestamp2, $timestamp3 ) = ( $timestamp, $timestamp, $timestamp );
 my ( $title1, $new1, $lang1, $expirationdate1, $number1 ) =
   ( 'News Title', '<p>We have some exciting news!</p>', q{}, '2999-12-30', 1 );
 my $href_entry1 = {
@@ -57,6 +57,20 @@ my $href_entry2 = {
 $rv = add_opac_new($href_entry2);
 ok( $rv == 1, 'Successfully added the second dummy news item!' );
 
+my ( $title3, $new3, $lang3, $expirationdate3, $number3 ) =
+  ( 'News Title3', '<p>We make news exciting!</p>', q{}, undef, 1 );
+my $href_entry3 = {
+    title          => $title3,
+    new            => $new3,
+    lang           => $lang3,
+    expirationdate => $expirationdate3,
+    timestamp      => $timestamp3,
+    number         => $number3,
+    branchcode     => 'LIB1',
+};
+$rv = add_opac_new($href_entry3);
+ok( $rv == 1, 'Successfully added the third dummy news item!' );
+
 # We need to determine the idnew in a non-MySQLism way.
 # This should be good enough.
 my $query =
@@ -141,4 +155,11 @@ ok( ref $arrayref_opac_news eq 'ARRAY',
     '$arrayref_opac_news is an array reference.' );
 ok( (scalar @$arrayref_opac_news) >= 2, 'Successfully tested GetNewsToDisplay for LIB1!' );
 
+$query =
+q{ SELECT idnew from opac_news WHERE timestamp='2000-01-01' AND expirationdate IS NULL; };
+$sth = $dbh->prepare($query);
+$sth->execute();
+my $nullcheck = $sth->fetchrow();
+ok ($nullcheck && $nullcheck>=0, 'Successfully found third dummy newsitem.');
+
 $dbh->rollback;
-- 
1.9.1