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

(-)a/C4/Acquisition.pm (-4 / +4 lines)
Lines 1515-1523 sub GetLateOrders { Link Here
1515
        $select .= "
1515
        $select .= "
1516
        aqorders.quantity - IFNULL(aqorders.quantityreceived,0)                 AS quantity,
1516
        aqorders.quantity - IFNULL(aqorders.quantityreceived,0)                 AS quantity,
1517
        (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1517
        (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1518
        DATEDIFF(CURDATE( ),closedate) AS latesince
1518
        (CAST(now() AS date) - closedate) AS latesince
1519
        ";
1519
        ";
1520
        $from .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) ";
1520
        $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) ";
1521
        $having = "
1521
        $having = "
1522
        HAVING quantity          <> 0
1522
        HAVING quantity          <> 0
1523
            AND unitpricesupplier <> 0
1523
            AND unitpricesupplier <> 0
Lines 1775-1781 sub GetContracts { Link Here
1775
        $query = "SELECT *
1775
        $query = "SELECT *
1776
            FROM aqcontract
1776
            FROM aqcontract
1777
            WHERE booksellerid=?
1777
            WHERE booksellerid=?
1778
                AND contractenddate >= CURDATE( )";
1778
                AND contractenddate >= CAST(now() AS date)";
1779
    }
1779
    }
1780
    my $sth = $dbh->prepare($query);
1780
    my $sth = $dbh->prepare($query);
1781
    $sth->execute( $booksellerid );
1781
    $sth->execute( $booksellerid );
Lines 1831-1837 sub AddClaim { Link Here
1831
    my $query        = "
1831
    my $query        = "
1832
        UPDATE aqorders SET
1832
        UPDATE aqorders SET
1833
            claims_count = claims_count + 1,
1833
            claims_count = claims_count + 1,
1834
            claimed_date = CURDATE()
1834
            claimed_date = CAST(now() AS date)
1835
        WHERE ordernumber = ?
1835
        WHERE ordernumber = ?
1836
        ";
1836
        ";
1837
    my $sth = $dbh->prepare($query);
1837
    my $sth = $dbh->prepare($query);
(-)a/C4/Bookseller.pm (-1 / +1 lines)
Lines 110-116 sub GetBooksellersWithLateOrders { Link Here
110
      "SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
110
      "SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
111
    FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
111
    FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
112
    LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
112
    LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
113
    WHERE (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY)
113
    WHERE (closedate < DATE_SUB(CAST(now() AS date),INTERVAL $delay DAY)
114
    AND (datereceived = '' OR datereceived IS NULL))";
114
    AND (datereceived = '' OR datereceived IS NULL))";
115
115
116
    my $sth = $dbh->prepare($query_string);
116
    my $sth = $dbh->prepare($query_string);
(-)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 1202-1208 sub GetOverduesForBranch { Link Here
1202
    WHERE (accountlines.amountoutstanding  != '0.000000')
1202
    WHERE (accountlines.amountoutstanding  != '0.000000')
1203
      AND (accountlines.accounttype         = 'FU'      )
1203
      AND (accountlines.accounttype         = 'FU'      )
1204
      AND (issues.branchcode =  ?   )
1204
      AND (issues.branchcode =  ?   )
1205
      AND (issues.date_due  < NOW())
1205
      AND (issues.date_due  < CAST(now() AS date))
1206
    ";
1206
    ";
1207
    my @getoverdues;
1207
    my @getoverdues;
1208
    my $i = 0;
1208
    my $i = 0;
(-)a/C4/Reserves.pm (-6 / +6 lines)
Lines 268-274 sub GetReservesFromBiblionumber { Link Here
268
        FROM     reserves
268
        FROM     reserves
269
        WHERE biblionumber = ? ";
269
        WHERE biblionumber = ? ";
270
    unless ( $all_dates ) {
270
    unless ( $all_dates ) {
271
        $query .= "AND reservedate <= CURRENT_DATE()";
271
        $query .= "AND reservedate <= CAST(now() AS date)";
272
    }
272
    }
273
    $query .= "ORDER BY priority";
273
    $query .= "ORDER BY priority";
274
    my $sth = $dbh->prepare($query);
274
    my $sth = $dbh->prepare($query);
Lines 334-340 sub GetReservesFromItemnumber { Link Here
334
    WHERE  itemnumber=?
334
    WHERE  itemnumber=?
335
    ";
335
    ";
336
    unless ( $all_dates ) {
336
    unless ( $all_dates ) {
337
	$query .= " AND reservedate <= CURRENT_DATE()";
337
	$query .= " AND reservedate <= CAST(now() AS date)";
338
    }
338
    }
339
    my $sth_res = $dbh->prepare($query);
339
    my $sth_res = $dbh->prepare($query);
340
    $sth_res->execute($itemnumber);
340
    $sth_res->execute($itemnumber);
Lines 864-870 sub CancelExpiredReserves { Link Here
864
    # Cancel reserves that have passed their expiration date.
864
    # Cancel reserves that have passed their expiration date.
865
    my $dbh = C4::Context->dbh;
865
    my $dbh = C4::Context->dbh;
866
    my $sth = $dbh->prepare( "
866
    my $sth = $dbh->prepare( "
867
        SELECT * FROM reserves WHERE DATE(expirationdate) < DATE( CURDATE() ) 
867
        SELECT * FROM reserves WHERE DATE(expirationdate) < CAST(now() AS date) 
868
        AND expirationdate IS NOT NULL
868
        AND expirationdate IS NOT NULL
869
        AND found IS NULL
869
        AND found IS NULL
870
    " );
870
    " );
Lines 1615-1621 sub _Findgroupreserve { Link Here
1615
        AND priority > 0
1615
        AND priority > 0
1616
        AND item_level_request = 1
1616
        AND item_level_request = 1
1617
        AND itemnumber = ?
1617
        AND itemnumber = ?
1618
        AND reservedate <= CURRENT_DATE()
1618
        AND reservedate <= CAST(now() AS date)
1619
    /;
1619
    /;
1620
    my $sth = $dbh->prepare($item_level_target_query);
1620
    my $sth = $dbh->prepare($item_level_target_query);
1621
    $sth->execute($itemnumber);
1621
    $sth->execute($itemnumber);
Lines 1645-1651 sub _Findgroupreserve { Link Here
1645
        AND priority > 0
1645
        AND priority > 0
1646
        AND item_level_request = 0
1646
        AND item_level_request = 0
1647
        AND hold_fill_targets.itemnumber = ?
1647
        AND hold_fill_targets.itemnumber = ?
1648
        AND reservedate <= CURRENT_DATE()
1648
        AND reservedate <= CAST(now() AS date)
1649
    /;
1649
    /;
1650
    $sth = $dbh->prepare($title_level_target_query);
1650
    $sth = $dbh->prepare($title_level_target_query);
1651
    $sth->execute($itemnumber);
1651
    $sth->execute($itemnumber);
Lines 1676-1682 sub _Findgroupreserve { Link Here
1676
          AND reserves.reservedate    = reserveconstraints.reservedate )
1676
          AND reserves.reservedate    = reserveconstraints.reservedate )
1677
          OR  reserves.constrainttype='a' )
1677
          OR  reserves.constrainttype='a' )
1678
          AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1678
          AND (reserves.itemnumber IS NULL OR reserves.itemnumber = ?)
1679
          AND reserves.reservedate <= CURRENT_DATE()
1679
          AND reserves.reservedate <= CAST(now() AS date)
1680
    /;
1680
    /;
1681
    $sth = $dbh->prepare($query);
1681
    $sth = $dbh->prepare($query);
1682
    $sth->execute( $biblio, $bibitem, $itemnumber );
1682
    $sth->execute( $biblio, $bibitem, $itemnumber );
(-)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