[PATCH] Bug 7802: Remove CUR_DATE() and CURRENT_DATE() MySQLisms, replace by CAST(now() AS date).
Marc Balmer
marc at msys.ch
Thu Mar 22 00:00:32 CET 2012
---
C4/Acquisition.pm | 4 ++--
C4/NewsChannels.pm | 4 ++--
C4/Overdues.pm | 8 ++++----
C4/Reserves.pm | 12 ++++++------
circ/overdue.pl | 2 +-
misc/cronjobs/longoverdue.pl | 6 +++---
.../stats/monthly_circulation_statistics.pl | 6 +++---
.../cronjobs/stats/monthly_new_items_statistics.pl | 2 +-
.../stats/monthly_new_patron_statistics.pl | 2 +-
9 files changed, 23 insertions(+), 23 deletions(-)
diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm
index bebc3a8..77b6dae 100644
--- a/C4/Acquisition.pm
+++ b/C4/Acquisition.pm
@@ -1797,7 +1797,7 @@ sub GetContracts {
$query = "SELECT *
FROM aqcontract
WHERE booksellerid=?
- AND contractenddate >= CURDATE( )";
+ AND contractenddate >= CAST(now() AS date)";
}
my $sth = $dbh->prepare($query);
$sth->execute( $booksellerid );
@@ -1853,7 +1853,7 @@ sub AddClaim {
my $query = "
UPDATE aqorders SET
claims_count = claims_count + 1,
- claimed_date = CURDATE()
+ claimed_date = CAST(now() AS date)
WHERE ordernumber = ?
";
my $sth = $dbh->prepare($query);
diff --git a/C4/NewsChannels.pm b/C4/NewsChannels.pm
index e68d6ff..6a3f1a5 100644
--- a/C4/NewsChannels.pm
+++ b/C4/NewsChannels.pm
@@ -141,11 +141,11 @@ sub GetNewsToDisplay {
SELECT *,timestamp AS newdate
FROM opac_news
WHERE (
- expirationdate >= CURRENT_DATE()
+ expirationdate >= CAST(now() AS date)
OR expirationdate IS NULL
OR expirationdate = '00-00-0000'
)
- AND `timestamp` <= CURRENT_DATE()
+ AND `timestamp` <= CAST(now() AS date)
AND lang = ?
ORDER BY number
"; # expirationdate field is NOT in ISO format?
diff --git a/C4/Overdues.pm b/C4/Overdues.pm
index 23e75f5..b82175e 100644
--- a/C4/Overdues.pm
+++ b/C4/Overdues.pm
@@ -124,7 +124,7 @@ sub Getoverdues {
SELECT issues.*, items.itype as itemtype, items.homebranch, items.barcode
FROM issues
LEFT JOIN items USING (itemnumber)
- WHERE date_due < NOW()
+ WHERE date_due < CAST(now() AS date)
";
} else {
$statement = "
@@ -132,7 +132,7 @@ LEFT JOIN items USING (itemnumber)
FROM issues
LEFT JOIN items USING (itemnumber)
LEFT JOIN biblioitems USING (biblioitemnumber)
- WHERE date_due < NOW()
+ WHERE date_due < CAST(now() AS date)
";
}
@@ -199,7 +199,7 @@ sub checkoverdues {
LEFT JOIN biblio ON items.biblionumber = biblio.biblionumber
LEFT JOIN biblioitems ON items.biblioitemnumber = biblioitems.biblioitemnumber
WHERE issues.borrowernumber = ?
- AND issues.date_due < NOW()"
+ AND issues.date_due < CAST(now() AS date)"
);
# FIXME: SELECT * across 4 tables? do we really need the marc AND marcxml blobs??
$sth->execute($borrowernumber);
@@ -1227,7 +1227,7 @@ sub GetOverduesForBranch {
WHERE (accountlines.amountoutstanding != '0.000000')
AND (accountlines.accounttype = 'FU' )
AND (issues.branchcode = ? )
- AND (issues.date_due < NOW())
+ AND (issues.date_due < CAST(now() AS date))
";
my @getoverdues;
my $i = 0;
diff --git a/C4/Reserves.pm b/C4/Reserves.pm
index 930d524..3358601 100644
--- a/C4/Reserves.pm
+++ b/C4/Reserves.pm
@@ -274,7 +274,7 @@ sub GetReservesFromBiblionumber {
FROM reserves
WHERE biblionumber = ? ";
unless ( $all_dates ) {
- $query .= "AND reservedate <= CURRENT_DATE()";
+ $query .= "AND reservedate <= CAST(now() AS date)";
}
$query .= "ORDER BY priority";
my $sth = $dbh->prepare($query);
@@ -340,7 +340,7 @@ sub GetReservesFromItemnumber {
WHERE itemnumber=?
";
unless ( $all_dates ) {
- $query .= " AND reservedate <= CURRENT_DATE()";
+ $query .= " AND reservedate <= CAST(now() AS date)";
}
my $sth_res = $dbh->prepare($query);
$sth_res->execute($itemnumber);
@@ -870,7 +870,7 @@ sub CancelExpiredReserves {
# Cancel reserves that have passed their expiration date.
my $dbh = C4::Context->dbh;
my $sth = $dbh->prepare( "
- SELECT * FROM reserves WHERE DATE(expirationdate) < DATE( CURDATE() )
+ SELECT * FROM reserves WHERE DATE(expirationdate) < CAST(now() AS date)
AND expirationdate IS NOT NULL
AND found IS NULL
" );
@@ -1734,7 +1734,7 @@ sub _Findgroupreserve {
AND priority > 0
AND item_level_request = 1
AND itemnumber = ?
- AND reservedate <= CURRENT_DATE()
+ AND reservedate <= CAST(now() AS date)
AND suspend = 0
/;
my $sth = $dbh->prepare($item_level_target_query);
@@ -1765,7 +1765,7 @@ sub _Findgroupreserve {
AND priority > 0
AND item_level_request = 0
AND hold_fill_targets.itemnumber = ?
- AND reservedate <= CURRENT_DATE()
+ AND reservedate <= CAST(now() as date)
AND suspend = 0
/;
$sth = $dbh->prepare($title_level_target_query);
@@ -1797,7 +1797,7 @@ sub _Findgroupreserve {
AND reserves.reservedate = reserveconstraints.reservedate )
OR reserves.constrainttype='a' )
AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
- AND reserves.reservedate <= CURRENT_DATE()
+ AND reserves.reservedate <= CAST(now() AS date)
AND suspend = 0
/;
$sth = $dbh->prepare($query);
diff --git a/circ/overdue.pl b/circ/overdue.pl
index a4cfa7a..6768829 100755
--- a/circ/overdue.pl
+++ b/circ/overdue.pl
@@ -290,7 +290,7 @@ if ($noreport) {
$strsth .= " AND borrowers.gonenoaddress <> 0";
}
elsif ( $borflagsfilter eq 'debarred' ) {
- $strsth .= " AND borrowers.debarred >= CURDATE()" ;
+ $strsth .= " AND borrowers.debarred >= CAST(now() AS date)" ;
}
elsif ( $borflagsfilter eq 'lost') {
$strsth .= " AND borrowers.lost <> 0";
diff --git a/misc/cronjobs/longoverdue.pl b/misc/cronjobs/longoverdue.pl
index 159db51..de1f0a6 100755
--- a/misc/cronjobs/longoverdue.pl
+++ b/misc/cronjobs/longoverdue.pl
@@ -117,7 +117,7 @@ unless ($confirm) {
}
# In my opinion, this line is safe SQL to have outside the API. --atz
-our $bounds_sth = C4::Context->dbh->prepare("SELECT DATE_SUB(CURDATE(), INTERVAL ? DAY)");
+our $bounds_sth = C4::Context->dbh->prepare("SELECT DATE_SUB(CAST(now() AS date), INTERVAL ? DAY)");
sub bounds ($) {
$bounds_sth->execute(shift);
@@ -130,8 +130,8 @@ sub longoverdue_sth {
SELECT items.itemnumber, borrowernumber, date_due
FROM issues, items
WHERE items.itemnumber = issues.itemnumber
- AND DATE_SUB(CURDATE(), INTERVAL ? DAY) > date_due
- AND DATE_SUB(CURDATE(), INTERVAL ? DAY) <= date_due
+ AND DATE_SUB(CAST(now() AS date), INTERVAL ? DAY) > date_due
+ AND DATE_SUB(CAST(now() AS date), INTERVAL ? DAY) <= date_due
AND itemlost <> ?
ORDER BY date_due
";
diff --git a/misc/cronjobs/stats/monthly_circulation_statistics.pl b/misc/cronjobs/stats/monthly_circulation_statistics.pl
index 1d8ab9f..bd4a69d 100755
--- a/misc/cronjobs/stats/monthly_circulation_statistics.pl
+++ b/misc/cronjobs/stats/monthly_circulation_statistics.pl
@@ -64,12 +64,12 @@ my $dbh = C4::Context->dbh;
my $sth2 = $dbh->prepare ("SELECT branchcode, branchname FROM branches ORDER BY branchcode");
# number of checkouts for this library
-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");
+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");
# number of renewals for this library
my $sth4 = $dbh->prepare ("SELECT COUNT(statistics.itemnumber) FROM statistics,items,biblioitems
- WHERE YEAR(statistics.datetime)=YEAR(SUBDATE(CURDATE(),INTERVAL 1 MONTH))
- AND MONTH(statistics.datetime)=MONTH(SUBDATE(CURDATE(),INTERVAL 1 MONTH))
+ WHERE 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.itemnumber=items.itemnumber
AND biblioitems.ccode=?
AND homebranch=?
diff --git a/misc/cronjobs/stats/monthly_new_items_statistics.pl b/misc/cronjobs/stats/monthly_new_items_statistics.pl
index d5df701..d5cc7fd 100755
--- a/misc/cronjobs/stats/monthly_new_items_statistics.pl
+++ b/misc/cronjobs/stats/monthly_new_items_statistics.pl
@@ -59,7 +59,7 @@ unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , 'localhost';
my $dbh = C4::Context->dbh;
-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");
+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");
my ($row,$itemtype,$count);
diff --git a/misc/cronjobs/stats/monthly_new_patron_statistics.pl b/misc/cronjobs/stats/monthly_new_patron_statistics.pl
index 2ef2a14..fc87c52 100755
--- a/misc/cronjobs/stats/monthly_new_patron_statistics.pl
+++ b/misc/cronjobs/stats/monthly_new_patron_statistics.pl
@@ -60,7 +60,7 @@ unshift @{$Mail::Sendmail::mailcfg{'smtp'}} , 'localhost';
my $dbh = C4::Context->dbh;
my $sth1 = $dbh->prepare ("SELECT categorycode FROM categories ORDER BY categorycode");
-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");
+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");
my ($rowc,$rowb,$categorycode,$branchcode,$count,$line);
--
1.7.2.5
--------------010707050005070907040005--
More information about the Koha-patches
mailing list