@@ -, +, @@ --------- -- This will confirm that the modified GetNewsToDisplay function has not broken. --- C4/NewsChannels.pm | 5 ++--- installer/data/mysql/updatedatabase.pl | 7 +++++++ t/db_dependent/NewsChannels.t | 25 +++++++++++++++++++++++-- 3 files changed, 32 insertions(+), 5 deletions(-) --- a/C4/NewsChannels.pm +++ a/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); --- a/installer/data/mysql/updatedatabase.pl +++ a/installer/data/mysql/updatedatabase.pl @@ -10267,6 +10267,13 @@ if ( CheckVersion($DBversion) ) { SetVersion($DBversion); } +$DBversion = "3.19.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); +} + # DEVELOPER PROCESS, search for anything to execute in the db_update directory # SEE bug 13068 # if there is anything in the atomicupdate, read and execute it. --- a/t/db_dependent/NewsChannels.t +++ a/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', '

We have some exciting news!

', 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', '

We make news exciting!

', 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; --