From 18a824eb0d4dfc6f0254983617e2dc9b20aece9f Mon Sep 17 00:00:00 2001 From: Marc Balmer Date: Thu, 22 Mar 2012 00:00:32 +0100 Subject: [PATCH] Bug 7802: Remove CUR_DATE() and CURRENT_DATE() MySQLisms, replace by CAST(now() AS date). --- C4/Acquisition.pm | 8 ++++---- C4/Bookseller.pm | 2 +- 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 +- 10 files changed, 26 insertions(+), 26 deletions(-) diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 4fd5cc2..2938e35 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -1515,9 +1515,9 @@ sub GetLateOrders { $select .= " aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity, (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal, - DATEDIFF(CURDATE( ),closedate) AS latesince + (CAST(now() AS date) - closedate) AS latesince "; - $from .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) "; + $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) "; $having = " HAVING quantity <> 0 AND unitpricesupplier <> 0 @@ -1775,7 +1775,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 ); @@ -1831,7 +1831,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/Bookseller.pm b/C4/Bookseller.pm index f14bd02..1c29539 100644 --- a/C4/Bookseller.pm +++ b/C4/Bookseller.pm @@ -110,7 +110,7 @@ sub GetBooksellersWithLateOrders { "SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id - WHERE (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY) + WHERE (closedate < DATE_SUB(CAST(now() AS date),INTERVAL $delay DAY) AND (datereceived = '' OR datereceived IS NULL))"; my $sth = $dbh->prepare($query_string); 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 660e10b..68ac3b3 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 < CURDATE() + 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 < CURDATE() + 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 < CURDATE()" + 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); @@ -1218,7 +1218,7 @@ sub GetOverduesForBranch { WHERE (accountlines.amountoutstanding != '0.000000') AND (accountlines.accounttype = 'FU' ) AND (issues.branchcode = ? ) - AND (issues.date_due < CURDATE()) + AND (issues.date_due < CAST(now() AS date)) "; my @getoverdues; my $i = 0; diff --git a/C4/Reserves.pm b/C4/Reserves.pm index 276658c..f7f6070 100644 --- a/C4/Reserves.pm +++ b/C4/Reserves.pm @@ -268,7 +268,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); @@ -334,7 +334,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); @@ -864,7 +864,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 " ); @@ -1615,7 +1615,7 @@ sub _Findgroupreserve { AND priority > 0 AND item_level_request = 1 AND itemnumber = ? - AND reservedate <= CURRENT_DATE() + AND reservedate <= CAST(now() AS date) /; my $sth = $dbh->prepare($item_level_target_query); $sth->execute($itemnumber); @@ -1645,7 +1645,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) /; $sth = $dbh->prepare($title_level_target_query); $sth->execute($itemnumber); @@ -1676,7 +1676,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) /; $sth = $dbh->prepare($query); $sth->execute( $biblio, $bibitem, $itemnumber ); diff --git a/circ/overdue.pl b/circ/overdue.pl index c25a1d6..7a046b3 100755 --- a/circ/overdue.pl +++ b/circ/overdue.pl @@ -279,7 +279,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