From 0012acbbcd0735812e941a6c2f6b7a55253facb9 Mon Sep 17 00:00:00 2001
From: Marc Balmer <marc@msys.ch>
Date: Mon, 15 Jul 2013 12:28:43 +0200
Subject: [PATCH] Bug 7802: Remove CUR_DATE() and CURRENT_DATE() MySQLisms, replace by CAST(now() AS date).

Signed-off-by: Jonathan Druart <jonathan.druart@biblibre.com>
---
 C4/Acquisition.pm                                  |    4 ++--
 C4/NewsChannels.pm                                 |    4 ++--
 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 +-
 8 files changed, 19 insertions(+), 19 deletions(-)

diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm
index 1a210b5..a705505 100644
--- a/C4/Acquisition.pm
+++ b/C4/Acquisition.pm
@@ -2116,7 +2116,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 );
@@ -2172,7 +2172,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 0201729..7698802 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/Reserves.pm b/C4/Reserves.pm
index 1f89127..6429575 100644
--- a/C4/Reserves.pm
+++ b/C4/Reserves.pm
@@ -296,7 +296,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);
@@ -362,7 +362,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);
@@ -920,7 +920,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
     " );
@@ -1733,7 +1733,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);
@@ -1764,7 +1764,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);
@@ -1796,7 +1796,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 4343592..d73f7b1 100755
--- a/circ/overdue.pl
+++ b/circ/overdue.pl
@@ -297,7 +297,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..5b09238 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