Attachment #30885 for bug #12167

View | Details | Raw Unified | Return to bug 12167
Collapse All | Expand All




     WHERE   (
        expirationdate >= CURRENT_DATE()
        OR    expirationdate IS NULL
        OR    expirationdate = '00-00-0000'
     )
     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.
    my $sth = $dbh->prepare($query);
    $lang = $lang // q{};
    $sth->execute($lang,$branch);

Lines 8613-8618 if ( CheckVersion($DBversion) ) { Link Here

(-)a/installer/data/mysql/updatedatabase.pl (+7 lines)
8613	SetVersion($DBversion);	8613	SetVersion($DBversion);
8614	}	8614	}
8615		8615
		8616	$DBversion = "3.17.00.XXX";
		8617	if ( CheckVersion($DBversion) ) {
		8618	$dbh->do("UPDATE opac_news SET expirationdate=NULL WHERE expirationdate='0000-00-00';");
		8619	print "Upgrade to $DBversion done (Bug 12167: Clean up date and time type casting issues)\n";
		8620	SetVersion ($DBversion);
		8621	}
		8622
8616	=head1 FUNCTIONS	8623	=head1 FUNCTIONS
8617		8624
8618	=head2 TableExists($table)	8625	=head2 TableExists($table)




use Modern::Perl;
use C4::Dates qw(format_date);
use C4::Branch qw(GetBranchName);
use Test::More tests => 14;

BEGIN {
    use_ok('C4::NewsChannels');

ok( $rv == 0, 'Correctly failed on no parameter!' );

my $timestamp = '2000-01-01';
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 = {

$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 =

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

Return to bug 12167

Lines 194-208 sub GetNewsToDisplay { Link Here

(-)a/C4/NewsChannels.pm (-3 / +2 lines)
194	WHERE (	194	WHERE (
195	expirationdate >= CURRENT_DATE()	195	expirationdate >= CURRENT_DATE()
196	OR expirationdate IS NULL	196	OR expirationdate IS NULL
197	OR expirationdate = '00-00-0000'
198	)	197	)
199	AND CAST( opac_news.timestamp AS DATE ) <= CURRENT_DATE()	198	AND CAST( opac_news.timestamp AS DATE ) <= CURRENT_DATE
200	AND (lang = '' OR lang = ?)	199	AND (lang = '' OR lang = ?)
201	AND (branchcode IS NULL OR branchcode = ?)	200	AND (branchcode IS NULL OR branchcode = ?)
202	ORDER BY number	201	ORDER BY number
203	}; # expirationdate field is NOT in ISO format?	202	}; # expirationdate field is NOT in ISO format?
204	# casting timestamp to date truncates HH:mm:ss	203	# casting timestamp to date truncates HH:mm:ss
205	# changing to <= CURRENT_DATE() today is captured.	204	# changing to <= CURRENT_DATE, today is captured.
206	my $sth = $dbh->prepare($query);	205	my $sth = $dbh->prepare($query);
207	$lang = $lang // q{};	206	$lang = $lang // q{};
208	$sth->execute($lang,$branch);	207	$sth->execute($lang,$branch);

Lines 3-9 Link Here

(-)a/t/db_dependent/NewsChannels.t (-3 / +23 lines)
3	use Modern::Perl;	3	use Modern::Perl;
4	use C4::Dates qw(format_date);	4	use C4::Dates qw(format_date);
5	use C4::Branch qw(GetBranchName);	5	use C4::Branch qw(GetBranchName);
6	use Test::More tests => 12;	6	use Test::More tests => 14;
7		7
8	BEGIN {	8	BEGIN {
9	use_ok('C4::NewsChannels');	9	use_ok('C4::NewsChannels');
Lines 27-33 my $rv = add_opac_new(); # intentionally bad Link Here
27	ok( $rv == 0, 'Correctly failed on no parameter!' );	27	ok( $rv == 0, 'Correctly failed on no parameter!' );
28		28
29	my $timestamp = '2000-01-01';	29	my $timestamp = '2000-01-01';
30	my ( $timestamp1, $timestamp2 ) = ( $timestamp, $timestamp );	30	my ( $timestamp1, $timestamp2, $timestamp3 ) = ( $timestamp, $timestamp, $timestamp );
31	my ( $title1, $new1, $lang1, $expirationdate1, $number1 ) =	31	my ( $title1, $new1, $lang1, $expirationdate1, $number1 ) =
32	( 'News Title', '<p>We have some exciting news!</p>', q{}, '2999-12-30', 1 );	32	( 'News Title', '<p>We have some exciting news!</p>', q{}, '2999-12-30', 1 );
33	my $href_entry1 = {	33	my $href_entry1 = {
Lines 57-62 my $href_entry2 = { Link Here
57	$rv = add_opac_new($href_entry2);	57	$rv = add_opac_new($href_entry2);
58	ok( $rv == 1, 'Successfully added the second dummy news item!' );	58	ok( $rv == 1, 'Successfully added the second dummy news item!' );
59		59
		60	my ( $title3, $new3, $lang3, $expirationdate3, $number3 ) =
		61	( 'News Title3', '<p>We make news exciting!</p>', q{}, undef, 1 );
		62	my $href_entry3 = {
		63	title => $title3,
		64	new => $new3,
		65	lang => $lang3,
		66	expirationdate => $expirationdate3,
		67	timestamp => $timestamp3,
		68	number => $number3,
		69	branchcode => 'LIB1',
		70	};
		71	$rv = add_opac_new($href_entry3);
		72	ok( $rv == 1, 'Successfully added the third dummy news item!' );
		73
60	# We need to determine the idnew in a non-MySQLism way.	74	# We need to determine the idnew in a non-MySQLism way.
61	# This should be good enough.	75	# This should be good enough.
62	my $query =	76	my $query =
Lines 141-144 ok( ref $arrayref_opac_news eq 'ARRAY', Link Here
141	'$arrayref_opac_news is an array reference.' );	155	'$arrayref_opac_news is an array reference.' );
142	ok( (scalar @$arrayref_opac_news) >= 2, 'Successfully tested GetNewsToDisplay for LIB1!' );	156	ok( (scalar @$arrayref_opac_news) >= 2, 'Successfully tested GetNewsToDisplay for LIB1!' );
143		157
		158	$query =
		159	q{ SELECT idnew from opac_news WHERE timestamp='2000-01-01' AND expirationdate IS NULL; };
		160	$sth = $dbh->prepare($query);
		161	$sth->execute();
		162	my $nullcheck = $sth->fetchrow();
		163	ok ($nullcheck && $nullcheck>=0, 'Successfully found third dummy newsitem.');
		164
144	$dbh->rollback;	165	$dbh->rollback;
145	-