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

(-)a/C4/Acquisition.pm (-2 / +2 lines)
Lines 2147-2153 sub GetContracts { Link Here
2147
        $query = "SELECT *
2147
        $query = "SELECT *
2148
            FROM aqcontract
2148
            FROM aqcontract
2149
            WHERE booksellerid=?
2149
            WHERE booksellerid=?
2150
                AND contractenddate >= CURDATE( )";
2150
                AND contractenddate >= CAST(now() AS date)";
2151
    }
2151
    }
2152
    my $sth = $dbh->prepare($query);
2152
    my $sth = $dbh->prepare($query);
2153
    $sth->execute( $booksellerid );
2153
    $sth->execute( $booksellerid );
Lines 2203-2209 sub AddClaim { Link Here
2203
    my $query        = "
2203
    my $query        = "
2204
        UPDATE aqorders SET
2204
        UPDATE aqorders SET
2205
            claims_count = claims_count + 1,
2205
            claims_count = claims_count + 1,
2206
            claimed_date = CURDATE()
2206
            claimed_date = CAST(now() AS date)
2207
        WHERE ordernumber = ?
2207
        WHERE ordernumber = ?
2208
        ";
2208
        ";
2209
    my $sth = $dbh->prepare($query);
2209
    my $sth = $dbh->prepare($query);
(-)a/C4/NewsChannels.pm (-2 / +2 lines)
Lines 141-151 sub GetNewsToDisplay { Link Here
141
     SELECT *,timestamp AS newdate
141
     SELECT *,timestamp AS newdate
142
     FROM   opac_news
142
     FROM   opac_news
143
     WHERE   (
143
     WHERE   (
144
        expirationdate >= CURRENT_DATE()
144
        expirationdate >= CAST(now() AS date)
145
        OR    expirationdate IS NULL
145
        OR    expirationdate IS NULL
146
        OR    expirationdate = '00-00-0000'
146
        OR    expirationdate = '00-00-0000'
147
      )
147
      )
148
      AND   `timestamp` <= CURRENT_DATE()
148
      AND   `timestamp` <= CAST(now() AS date)
149
      AND   lang = ?
149
      AND   lang = ?
150
      ORDER BY number
150
      ORDER BY number
151
    ";				# expirationdate field is NOT in ISO format?
151
    ";				# expirationdate field is NOT in ISO format?
(-)a/C4/Reserves.pm (-6 / +6 lines)
Lines 277-283 sub GetReservesFromBiblionumber { Link Here
277
        FROM     reserves
277
        FROM     reserves
278
        WHERE biblionumber = ? ";
278
        WHERE biblionumber = ? ";
279
    unless ( $all_dates ) {
279
    unless ( $all_dates ) {
280
        $query .= "AND reservedate <= CURRENT_DATE()";
280
        $query .= "AND reservedate <= CAST(now() AS date)";
281
    }
281
    }
282
    $query .= "ORDER BY priority";
282
    $query .= "ORDER BY priority";
283
    my $sth = $dbh->prepare($query);
283
    my $sth = $dbh->prepare($query);
Lines 343-349 sub GetReservesFromItemnumber { Link Here
343
    WHERE  itemnumber=?
343
    WHERE  itemnumber=?
344
    ";
344
    ";
345
    unless ( $all_dates ) {
345
    unless ( $all_dates ) {
346
	$query .= " AND reservedate <= CURRENT_DATE()";
346
        $query .= " AND reservedate <= CAST(now() AS date)";
347
    }
347
    }
348
    my $sth_res = $dbh->prepare($query);
348
    my $sth_res = $dbh->prepare($query);
349
    $sth_res->execute($itemnumber);
349
    $sth_res->execute($itemnumber);
Lines 897-903 sub CancelExpiredReserves { Link Here
897
    # Cancel reserves that have passed their expiration date.
897
    # Cancel reserves that have passed their expiration date.
898
    my $dbh = C4::Context->dbh;
898
    my $dbh = C4::Context->dbh;
899
    my $sth = $dbh->prepare( "
899
    my $sth = $dbh->prepare( "
900
        SELECT * FROM reserves WHERE DATE(expirationdate) < DATE( CURDATE() ) 
900
        SELECT * FROM reserves WHERE DATE(expirationdate) < CAST(now() AS date)
901
        AND expirationdate IS NOT NULL
901
        AND expirationdate IS NOT NULL
902
        AND found IS NULL
902
        AND found IS NULL
903
    " );
903
    " );
Lines 1768-1774 sub _Findgroupreserve { Link Here
1768
        AND priority > 0
1768
        AND priority > 0
1769
        AND item_level_request = 1
1769
        AND item_level_request = 1
1770
        AND itemnumber = ?
1770
        AND itemnumber = ?
1771
        AND reservedate <= CURRENT_DATE()
1771
        AND reservedate <= CAST(now() AS date)
1772
        AND suspend = 0
1772
        AND suspend = 0
1773
    /;
1773
    /;
1774
    my $sth = $dbh->prepare($item_level_target_query);
1774
    my $sth = $dbh->prepare($item_level_target_query);
Lines 1799-1805 sub _Findgroupreserve { Link Here
1799
        AND priority > 0
1799
        AND priority > 0
1800
        AND item_level_request = 0
1800
        AND item_level_request = 0
1801
        AND hold_fill_targets.itemnumber = ?
1801
        AND hold_fill_targets.itemnumber = ?
1802
        AND reservedate <= CURRENT_DATE()
1802
        AND reservedate <= CAST(now() as date)
1803
        AND suspend = 0
1803
        AND suspend = 0
1804
    /;
1804
    /;
1805
    $sth = $dbh->prepare($title_level_target_query);
1805
    $sth = $dbh->prepare($title_level_target_query);
Lines 1831-1837 sub _Findgroupreserve { Link Here
1831
          AND reserves.reservedate    = reserveconstraints.reservedate )
1831
          AND reserves.reservedate    = reserveconstraints.reservedate )
1832
          OR  reserves.constrainttype='a' )
1832
          OR  reserves.constrainttype='a' )
1833
          AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1833
          AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1834
          AND reserves.reservedate <= CURRENT_DATE()
1834
          AND reserves.reservedate <= CAST(now() AS date)
1835
          AND suspend = 0
1835
          AND suspend = 0
1836
    /;
1836
    /;
1837
    $sth = $dbh->prepare($query);
1837
    $sth = $dbh->prepare($query);
(-)a/circ/overdue.pl (-1 / +1 lines)
Lines 297-303 if ($noreport) { Link Here
297
        $strsth .= " AND borrowers.gonenoaddress <> 0";
297
        $strsth .= " AND borrowers.gonenoaddress <> 0";
298
    }
298
    }
299
    elsif ( $borflagsfilter eq 'debarred' ) {
299
    elsif ( $borflagsfilter eq 'debarred' ) {
300
        $strsth .= " AND borrowers.debarred >=  CURDATE()" ;
300
        $strsth .= " AND borrowers.debarred >=  CAST(now() AS date)" ;
301
    }
301
    }
302
    elsif ( $borflagsfilter eq 'lost') {
302
    elsif ( $borflagsfilter eq 'lost') {
303
        $strsth .= " AND borrowers.lost <> 0";
303
        $strsth .= " AND borrowers.lost <> 0";
(-)a/misc/cronjobs/longoverdue.pl (-3 / +3 lines)
Lines 117-123 unless ($confirm) { Link Here
117
}
117
}
118
118
119
# In my opinion, this line is safe SQL to have outside the API. --atz
119
# In my opinion, this line is safe SQL to have outside the API. --atz
120
our $bounds_sth = C4::Context->dbh->prepare("SELECT DATE_SUB(CURDATE(), INTERVAL ? DAY)");
120
our $bounds_sth = C4::Context->dbh->prepare("SELECT DATE_SUB(CAST(now() AS date), INTERVAL ? DAY)");
121
121
122
sub bounds ($) {
122
sub bounds ($) {
123
    $bounds_sth->execute(shift);
123
    $bounds_sth->execute(shift);
Lines 130-137 sub longoverdue_sth { Link Here
130
    SELECT items.itemnumber, borrowernumber, date_due
130
    SELECT items.itemnumber, borrowernumber, date_due
131
      FROM issues, items
131
      FROM issues, items
132
     WHERE items.itemnumber = issues.itemnumber
132
     WHERE items.itemnumber = issues.itemnumber
133
      AND  DATE_SUB(CURDATE(), INTERVAL ? DAY)  > date_due
133
      AND  DATE_SUB(CAST(now() AS date), INTERVAL ? DAY)  > date_due
134
      AND  DATE_SUB(CURDATE(), INTERVAL ? DAY) <= date_due
134
      AND  DATE_SUB(CAST(now() AS date), INTERVAL ? DAY) <= date_due
135
      AND  itemlost <> ?
135
      AND  itemlost <> ?
136
     ORDER BY date_due
136
     ORDER BY date_due
137
    ";
137
    ";
(-)a/misc/cronjobs/stats/monthly_circulation_statistics.pl (-3 / +3 lines)
Lines 64-75 my $dbh = C4::Context->dbh; Link Here
64
my $sth2 = $dbh->prepare ("SELECT branchcode, branchname FROM branches ORDER BY branchcode");
64
my $sth2 = $dbh->prepare ("SELECT branchcode, branchname FROM branches ORDER BY branchcode");
65
65
66
# number of checkouts for this library
66
# number of checkouts for this library
67
my $sth3 = $dbh->prepare ("SELECT COUNT(*) FROM biblioitems,items,statistics WHERE biblioitems.biblioitemnumber=items.biblioitemnumber AND statistics.itemnumber=items.itemnumber AND items.ccode=? AND YEAR(statistics.datetime)=YEAR(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) AND MONTH(statistics.datetime)=MONTH(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) AND statistics.branch=? AND statistics.type='issue' GROUP BY ccode");
67
my $sth3 = $dbh->prepare ("SELECT COUNT(*) FROM biblioitems,items,statistics WHERE biblioitems.biblioitemnumber=items.biblioitemnumber AND statistics.itemnumber=items.itemnumber AND items.ccode=? AND YEAR(statistics.datetime)=YEAR(SUBDATE(CAST(now() AS date),INTERVAL 1 MONTH)) AND MONTH(statistics.datetime)=MONTH(SUBDATE(CAST(now() AS date),INTERVAL 1 MONTH)) AND statistics.branch=? AND statistics.type='issue' GROUP BY ccode");
68
68
69
# number of renewals for this library
69
# number of renewals for this library
70
my $sth4 = $dbh->prepare ("SELECT COUNT(statistics.itemnumber) FROM statistics,items,biblioitems
70
my $sth4 = $dbh->prepare ("SELECT COUNT(statistics.itemnumber) FROM statistics,items,biblioitems
71
	WHERE YEAR(statistics.datetime)=YEAR(SUBDATE(CURDATE(),INTERVAL 1 MONTH))
71
	WHERE YEAR(statistics.datetime)=YEAR(SUBDATE(CAST(now() AS date),INTERVAL 1 MONTH))
72
	AND MONTH(statistics.datetime)=MONTH(SUBDATE(CURDATE(),INTERVAL 1 MONTH))
72
	AND MONTH(statistics.datetime)=MONTH(SUBDATE(CAST(now() AS date),INTERVAL 1 MONTH))
73
	AND statistics.itemnumber=items.itemnumber
73
	AND statistics.itemnumber=items.itemnumber
74
	AND biblioitems.ccode=?
74
	AND biblioitems.ccode=?
75
        AND homebranch=?
75
        AND homebranch=?
(-)a/misc/cronjobs/stats/monthly_new_items_statistics.pl (-1 / +1 lines)
Lines 59-65 unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , 'localhost'; Link Here
59
59
60
my $dbh = C4::Context->dbh;
60
my $dbh = C4::Context->dbh;
61
61
62
my $sth = $dbh->prepare ("SELECT biblioitems.ccode,COUNT(biblioitems.ccode) FROM items,biblioitems WHERE YEAR(items.dateaccessioned)=YEAR(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) AND MONTH(items.dateaccessioned)=MONTH(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) AND biblioitems.biblioitemnumber=items.biblioitemnumber GROUP BY biblioitems.ccode");
62
my $sth = $dbh->prepare ("SELECT biblioitems.ccode,COUNT(biblioitems.ccode) FROM items,biblioitems WHERE YEAR(items.dateaccessioned)=YEAR(SUBDATE(CAST(now() AS date),INTERVAL 1 MONTH)) AND MONTH(items.dateaccessioned)=MONTH(SUBDATE(CAST(now() AS date),INTERVAL 1 MONTH)) AND biblioitems.biblioitemnumber=items.biblioitemnumber GROUP BY biblioitems.ccode");
63
63
64
my ($row,$itemtype,$count);
64
my ($row,$itemtype,$count);
65
65
(-)a/misc/cronjobs/stats/monthly_new_patron_statistics.pl (-2 / +1 lines)
Lines 60-66 unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , 'localhost'; Link Here
60
60
61
my $dbh = C4::Context->dbh;
61
my $dbh = C4::Context->dbh;
62
my $sth1 = $dbh->prepare ("SELECT categorycode FROM categories ORDER BY categorycode");
62
my $sth1 = $dbh->prepare ("SELECT categorycode FROM categories ORDER BY categorycode");
63
my $sth2 = $dbh->prepare ("SELECT branchcode,COUNT(branchcode) FROM borrowers WHERE categorycode=? AND YEAR(dateenrolled)=YEAR(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) AND MONTH(dateenrolled)=MONTH(SUBDATE(CURDATE(),INTERVAL 1 MONTH)) GROUP BY branchcode");
63
my $sth2 = $dbh->prepare ("SELECT branchcode,COUNT(branchcode) FROM borrowers WHERE categorycode=? AND YEAR(dateenrolled)=YEAR(SUBDATE(CAST(now() AS date),INTERVAL 1 MONTH)) AND MONTH(dateenrolled)=MONTH(SUBDATE(CAST(now() AS date),INTERVAL 1 MONTH)) GROUP BY branchcode");
64
64
65
my ($rowc,$rowb,$categorycode,$branchcode,$count,$line);
65
my ($rowc,$rowb,$categorycode,$branchcode,$count,$line);
66
66
67
- 

Return to bug 7802