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

(-)a/C4/NewsChannels.pm (-6 / +5 lines)
Lines 188-214 sub get_opac_news { Link Here
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
    }
(-)a/installer/data/mysql/updatedatabase.pl (+8 lines)
Lines 8613-8618 if ( CheckVersion($DBversion) ) { Link Here
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)
(-)a/t/db_dependent/NewsChannels.t (-5 / +31 lines)
Lines 3-9 Link Here
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
- 

Return to bug 12167