From 8f49a4bd90152f17be763b7f1d89b4a1914931db Mon Sep 17 00:00:00 2001 From: Marc Balmer Date: Thu, 22 Mar 2012 00:00:32 +0100 Subject: [PATCH 1/2] Bug 7802: Remove CUR_DATE() and CURRENT_DATE() MySQLisms, replace by CAST(now() AS date). signed-off-by Jonathan Druart --- 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 56e0941..960e790 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.7.3