Attachment #31139 for bug #12167

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




sub GetNewsToDisplay {
    my ($lang,$branch) = @_;
    my $dbh = C4::Context->dbh;
    # SELECT *,DATE_FORMAT(timestamp, '%d/%m/%Y') AS newdate
    my $query = q{
     SELECT *,opac_news.timestamp AS newdate
     FROM   opac_news
     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);
    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;
    }

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

(-)a/installer/data/mysql/updatedatabase.pl (+8 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
		8623
8616	=head1 FUNCTIONS	8624	=head1 FUNCTIONS
8617		8625
8618	=head2 TableExists($table)	8626	=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 =

ok( $opac_news_count >= 2, 'Successfully tested get_opac_news for LIB1!' );

# Test GetNewsToDisplay
( $arrayref_opac_news ) = GetNewsToDisplay( q{}, 'LIB1' );
ok( $arrayref_opac_news, '$arrayref_opac_news is ' .
                          ( defined($arrayref_opac_news) ?
                              ('Defined') : 'UNDEFINED')
  );
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;
- 

Return to bug 12167

Lines 188-214 sub get_opac_news { Link Here

(-)a/C4/NewsChannels.pm (-6 / +5 lines)
188	sub GetNewsToDisplay {	188	sub GetNewsToDisplay {
189	my ($lang,$branch) = @_;	189	my ($lang,$branch) = @_;
190	my $dbh = C4::Context->dbh;	190	my $dbh = C4::Context->dbh;
191	# SELECT *,DATE_FORMAT(timestamp, '%d/%m/%Y') AS newdate
192	my $query = q{	191	my $query = q{
193	SELECT *,timestamp AS newdate	192	SELECT *,opac_news.timestamp AS newdate
194	FROM opac_news	193	FROM opac_news
195	WHERE (	194	WHERE (
196	expirationdate >= CURRENT_DATE()	195	expirationdate >= CURRENT_DATE()
197	OR expirationdate IS NULL	196	OR expirationdate IS NULL
198	OR expirationdate = '00-00-0000'
199	)	197	)
200	AND DATE(timestamp) < DATE_ADD(CURDATE(), INTERVAL 1 DAY)	198	AND CAST( opac_news.timestamp AS DATE ) <= CURRENT_DATE
201	AND (lang = '' OR lang = ?)	199	AND (lang = '' OR lang = ?)
202	AND (branchcode IS NULL OR branchcode = ?)	200	AND (branchcode IS NULL OR branchcode = ?)
203	ORDER BY number	201	ORDER BY number
204	}; # expirationdate field is NOT in ISO format?	202	}; # expirationdate field is NOT in ISO format?
205	# timestamp has HH:mm:ss, CURRENT_DATE generates 00:00:00	203	# casting timestamp to date truncates HH:mm:ss
206	# by adding 1, that captures today correctly.	204	# changing to <= CURRENT_DATE, today is captured.
207	my $sth = $dbh->prepare($query);	205	my $sth = $dbh->prepare($query);
208	$lang = $lang // q{};	206	$lang = $lang // q{};
209	$sth->execute($lang,$branch);	207	$sth->execute($lang,$branch);
210	my @results;	208	my @results;
211	while ( my $row = $sth->fetchrow_hashref ){	209	while ( my $row = $sth->fetchrow_hashref ){
		210	# this will type cast and respect system preferences.
212	$row->{newdate} = format_date($row->{newdate});	211	$row->{newdate} = format_date($row->{newdate});
213	push @results, $row;	212	push @results, $row;
214	}	213	}

Lines 3-9 Link Here

(-)a/t/db_dependent/NewsChannels.t (-5 / +31 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 => 10;	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 132-138 my ( $opac_news_count, $arrayref_opac_news ) = get_opac_news( 0, q{}, 'LIB1' ); Link Here
132	ok( $opac_news_count >= 2, 'Successfully tested get_opac_news for LIB1!' );	146	ok( $opac_news_count >= 2, 'Successfully tested get_opac_news for LIB1!' );
133		147
134	# Test GetNewsToDisplay	148	# Test GetNewsToDisplay
135	( $opac_news_count, $arrayref_opac_news ) = GetNewsToDisplay( q{}, 'LIB1' );	149	( $arrayref_opac_news ) = GetNewsToDisplay( q{}, 'LIB1' );
136	ok( $opac_news_count >= 2, 'Successfully tested GetNewsToDisplay for LIB1!' );	150	ok( $arrayref_opac_news, '$arrayref_opac_news is ' .
		151	( defined($arrayref_opac_news) ?
		152	('Defined') : 'UNDEFINED')
		153	);
		154	ok( ref $arrayref_opac_news eq 'ARRAY',
		155	'$arrayref_opac_news is an array reference.' );
		156	ok( (scalar @$arrayref_opac_news) >= 2, 'Successfully tested GetNewsToDisplay for LIB1!' );
		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.');
137		164
138	$dbh->rollback;	165	$dbh->rollback;
139	-