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

(-)a/C4/Acquisition.pm (-2 / +2 lines)
Lines 1797-1803 sub GetContracts { Link Here
1797
        $query = "SELECT *
1797
        $query = "SELECT *
1798
            FROM aqcontract
1798
            FROM aqcontract
1799
            WHERE booksellerid=?
1799
            WHERE booksellerid=?
1800
                AND contractenddate >= CURDATE( )";
1800
                AND contractenddate >= CAST(now() AS date)";
1801
    }
1801
    }
1802
    my $sth = $dbh->prepare($query);
1802
    my $sth = $dbh->prepare($query);
1803
    $sth->execute( $booksellerid );
1803
    $sth->execute( $booksellerid );
Lines 1853-1859 sub AddClaim { Link Here
1853
    my $query        = "
1853
    my $query        = "
1854
        UPDATE aqorders SET
1854
        UPDATE aqorders SET
1855
            claims_count = claims_count + 1,
1855
            claims_count = claims_count + 1,
1856
            claimed_date = CURDATE()
1856
            claimed_date = CAST(now() AS date)
1857
        WHERE ordernumber = ?
1857
        WHERE ordernumber = ?
1858
        ";
1858
        ";
1859
    my $sth = $dbh->prepare($query);
1859
    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/Overdues.pm (-4 / +4 lines)
Lines 124-130 sub Getoverdues { Link Here
124
   SELECT issues.*, items.itype as itemtype, items.homebranch, items.barcode
124
   SELECT issues.*, items.itype as itemtype, items.homebranch, items.barcode
125
     FROM issues 
125
     FROM issues 
126
LEFT JOIN items       USING (itemnumber)
126
LEFT JOIN items       USING (itemnumber)
127
    WHERE date_due < NOW()
127
    WHERE date_due < CAST(now() AS date)
128
";
128
";
129
    } else {
129
    } else {
130
        $statement = "
130
        $statement = "
Lines 132-138 LEFT JOIN items USING (itemnumber) Link Here
132
     FROM issues 
132
     FROM issues 
133
LEFT JOIN items       USING (itemnumber)
133
LEFT JOIN items       USING (itemnumber)
134
LEFT JOIN biblioitems USING (biblioitemnumber)
134
LEFT JOIN biblioitems USING (biblioitemnumber)
135
    WHERE date_due < NOW()
135
    WHERE date_due < CAST(now() AS date)
136
";
136
";
137
    }
137
    }
138
138
Lines 199-205 sub checkoverdues { Link Here
199
         LEFT JOIN biblio      ON items.biblionumber     = biblio.biblionumber
199
         LEFT JOIN biblio      ON items.biblionumber     = biblio.biblionumber
200
         LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
200
         LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
201
            WHERE issues.borrowernumber  = ?
201
            WHERE issues.borrowernumber  = ?
202
            AND   issues.date_due < NOW()"
202
            AND   issues.date_due < CAST(now() AS date)"
203
    );
203
    );
204
    # FIXME: SELECT * across 4 tables?  do we really need the marc AND marcxml blobs??
204
    # FIXME: SELECT * across 4 tables?  do we really need the marc AND marcxml blobs??
205
    $sth->execute($borrowernumber);
205
    $sth->execute($borrowernumber);
Lines 1227-1233 sub GetOverduesForBranch { Link Here
1227
    WHERE (accountlines.amountoutstanding  != '0.000000')
1227
    WHERE (accountlines.amountoutstanding  != '0.000000')
1228
      AND (accountlines.accounttype         = 'FU'      )
1228
      AND (accountlines.accounttype         = 'FU'      )
1229
      AND (issues.branchcode =  ?   )
1229
      AND (issues.branchcode =  ?   )
1230
      AND (issues.date_due  < NOW())
1230
      AND (issues.date_due  < CAST(now() AS date))
1231
    ";
1231
    ";
1232
    my @getoverdues;
1232
    my @getoverdues;
1233
    my $i = 0;
1233
    my $i = 0;
(-)a/C4/Reserves.pm (-6 / +6 lines)
Lines 274-280 sub GetReservesFromBiblionumber { Link Here
274
        FROM     reserves
274
        FROM     reserves
275
        WHERE biblionumber = ? ";
275
        WHERE biblionumber = ? ";
276
    unless ( $all_dates ) {
276
    unless ( $all_dates ) {
277
        $query .= "AND reservedate <= CURRENT_DATE()";
277
        $query .= "AND reservedate <= CAST(now() AS date)";
278
    }
278
    }
279
    $query .= "ORDER BY priority";
279
    $query .= "ORDER BY priority";
280
    my $sth = $dbh->prepare($query);
280
    my $sth = $dbh->prepare($query);
Lines 340-346 sub GetReservesFromItemnumber { Link Here
340
    WHERE  itemnumber=?
340
    WHERE  itemnumber=?
341
    ";
341
    ";
342
    unless ( $all_dates ) {
342
    unless ( $all_dates ) {
343
	$query .= " AND reservedate <= CURRENT_DATE()";
343
	$query .= " AND reservedate <= CAST(now() AS date)";
344
    }
344
    }
345
    my $sth_res = $dbh->prepare($query);
345
    my $sth_res = $dbh->prepare($query);
346
    $sth_res->execute($itemnumber);
346
    $sth_res->execute($itemnumber);
Lines 870-876 sub CancelExpiredReserves { Link Here
870
    # Cancel reserves that have passed their expiration date.
870
    # Cancel reserves that have passed their expiration date.
871
    my $dbh = C4::Context->dbh;
871
    my $dbh = C4::Context->dbh;
872
    my $sth = $dbh->prepare( "
872
    my $sth = $dbh->prepare( "
873
        SELECT * FROM reserves WHERE DATE(expirationdate) < DATE( CURDATE() ) 
873
        SELECT * FROM reserves WHERE DATE(expirationdate) < CAST(now() AS date) 
874
        AND expirationdate IS NOT NULL
874
        AND expirationdate IS NOT NULL
875
        AND found IS NULL
875
        AND found IS NULL
876
    " );
876
    " );
Lines 1734-1740 sub _Findgroupreserve { Link Here
1734
        AND priority > 0
1734
        AND priority > 0
1735
        AND item_level_request = 1
1735
        AND item_level_request = 1
1736
        AND itemnumber = ?
1736
        AND itemnumber = ?
1737
        AND reservedate <= CURRENT_DATE()
1737
        AND reservedate <= CAST(now() AS date)
1738
        AND suspend = 0
1738
        AND suspend = 0
1739
    /;
1739
    /;
1740
    my $sth = $dbh->prepare($item_level_target_query);
1740
    my $sth = $dbh->prepare($item_level_target_query);
Lines 1765-1771 sub _Findgroupreserve { Link Here
1765
        AND priority > 0
1765
        AND priority > 0
1766
        AND item_level_request = 0
1766
        AND item_level_request = 0
1767
        AND hold_fill_targets.itemnumber = ?
1767
        AND hold_fill_targets.itemnumber = ?
1768
        AND reservedate <= CURRENT_DATE()
1768
        AND reservedate <= CAST(now() as date)
1769
        AND suspend = 0
1769
        AND suspend = 0
1770
    /;
1770
    /;
1771
    $sth = $dbh->prepare($title_level_target_query);
1771
    $sth = $dbh->prepare($title_level_target_query);
Lines 1797-1803 sub _Findgroupreserve { Link Here
1797
          AND reserves.reservedate    = reserveconstraints.reservedate )
1797
          AND reserves.reservedate    = reserveconstraints.reservedate )
1798
          OR  reserves.constrainttype='a' )
1798
          OR  reserves.constrainttype='a' )
1799
          AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1799
          AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1800
          AND reserves.reservedate <= CURRENT_DATE()
1800
          AND reserves.reservedate <= CAST(now() AS date)
1801
          AND suspend = 0
1801
          AND suspend = 0
1802
    /;
1802
    /;
1803
    $sth = $dbh->prepare($query);
1803
    $sth = $dbh->prepare($query);
(-)a/circ/overdue.pl (-1 / +1 lines)
Lines 290-296 if ($noreport) { Link Here
290
        $strsth .= " AND borrowers.gonenoaddress <> 0";
290
        $strsth .= " AND borrowers.gonenoaddress <> 0";
291
    }
291
    }
292
    elsif ( $borflagsfilter eq 'debarred' ) {
292
    elsif ( $borflagsfilter eq 'debarred' ) {
293
        $strsth .= " AND borrowers.debarred >=  CURDATE()" ;
293
        $strsth .= " AND borrowers.debarred >=  CAST(now() AS date)" ;
294
    }
294
    }
295
    elsif ( $borflagsfilter eq 'lost') {
295
    elsif ( $borflagsfilter eq 'lost') {
296
        $strsth .= " AND borrowers.lost <> 0";
296
        $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