X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FAcquisition.pm;h=3e274fec24b4dbe78c8fcafa0814eece9cba3530;hb=d32a8c21b48d2799db84b782447e99767395adb5;hp=827062703595362224b70407a30b51633842ecd2;hpb=31ddb16fa2d142706b7f17c924b05a46e9774edb;p=koha.git diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 8270627035..3e274fec24 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -19,8 +19,8 @@ package C4::Acquisition; use strict; -require Exporter; use C4::Context; +use C4::Debug; use C4::Dates qw(format_date); use MARC::Record; use C4::Suggestions; @@ -28,8 +28,20 @@ use Time::localtime; use vars qw($VERSION @ISA @EXPORT); -# set the version for version checking -$VERSION = 3.01; +BEGIN { + # set the version for version checking + $VERSION = 3.01; + require Exporter; + @ISA = qw(Exporter); + @EXPORT = qw( + &GetBasket &NewBasket &CloseBasket + &GetPendingOrders &GetOrder &GetOrders + &GetOrderNumber &GetLateOrders &NewOrder &DelOrder + &SearchOrder &GetHistory &GetRecentAcqui + &ModOrder &ModReceiveOrder &ModOrderBiblioNumber + &GetParcels &GetParcel + ); +} # used in receiveorder subroutine # to provide library specific handling @@ -50,28 +62,8 @@ orders, basket and parcels. =head1 FUNCTIONS -=over 2 - -=cut - -@ISA = qw(Exporter); -@EXPORT = qw( - &GetBasket &NewBasket &CloseBasket - &GetPendingOrders &GetOrder &GetOrders - &GetOrderNumber &GetLateOrders &NewOrder &DelOrder - &SearchOrder &GetHistory &GetRecentAcqui - &ModOrder &ModReceiveOrder &ModOrderBiblioNumber - &GetParcels &GetParcel -); - =head2 FUNCTIONS ABOUT BASKETS -=over 2 - -=cut - -#------------------------------------------------------------# - =head3 GetBasket =over 4 @@ -85,8 +77,6 @@ informations for a given basket returned as a hashref. =back -=back - =cut sub GetBasket { @@ -166,12 +156,8 @@ sub CloseBasket { #------------------------------------------------------------# -=back - =head2 FUNCTIONS ABOUT ORDERS -=over 2 - =cut #------------------------------------------------------------# @@ -210,7 +196,7 @@ Results are ordered from most to least recent. =cut sub GetPendingOrders { - my ($supplierid,$grouped) = @_; + my ($supplierid,$grouped, $closed) = @_; my $dbh = C4::Context->dbh; my $strsth = " SELECT ".($grouped?"count(*),":"")."aqbasket.basketno, @@ -220,25 +206,30 @@ sub GetPendingOrders { LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber WHERE booksellerid=? - AND (quantity > quantityreceived OR quantityreceived is NULL) AND datecancellationprinted IS NULL - AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL) "; + if($closed){ + $strsth .= " + AND (quantity > quantityreceived OR quantityreceived is NULL) + AND closedate IS NOT NULL "; + }else{ + $strsth .= "AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL)"; + } ## FIXME Why 180 days ??? + my @query_params = ( $supplierid ); if ( C4::Context->preference("IndependantBranches") ) { my $userenv = C4::Context->userenv; if ( ($userenv) && ( $userenv->{flags} != 1 ) ) { - $strsth .= - " and (borrowers.branchcode = '" - . $userenv->{branch} - . "' or borrowers.branchcode ='')"; + $strsth .= " and (borrowers.branchcode = ? + or borrowers.branchcode = '')"; + push @query_params, $userenv->{branch}; } } $strsth .= " group by aqbasket.basketno" if $grouped; $strsth .= " order by aqbasket.basketno"; my $sth = $dbh->prepare($strsth); - $sth->execute($supplierid); + $sth->execute( @query_params ); my $results = $sth->fetchall_arrayref({}); $sth->finish; return $results; @@ -270,7 +261,7 @@ sub GetOrders { my $dbh = C4::Context->dbh; my $query =" SELECT aqorderbreakdown.*, - biblio.*,biblioitems.publishercode, + biblio.*,biblioitems.*, aqorders.*, aqbookfund.bookfundname, biblio.title @@ -304,10 +295,14 @@ sub GetOrders { $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber); +=back + Looks up the ordernumber with the given biblionumber and biblioitemnumber. Returns the number of this order. +=over 4 + =item C<$ordernumber> is the order number. =back @@ -350,7 +345,7 @@ sub GetOrder { my ($ordnum) = @_; my $dbh = C4::Context->dbh; my $query = " - SELECT * + SELECT biblioitems.*, biblio.*, aqorderbreakdown.*, aqorders.* FROM aqorders LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber @@ -374,7 +369,7 @@ sub GetOrder { &NewOrder($basket, $biblionumber, $title, $quantity, $listprice, $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp, $ecost, $gst, $budget, $unitprice, $subscription, - $booksellerinvoicenumber, $purchaseorder); + $booksellerinvoicenumber, $purchaseorder, $branchcode); Adds a new order to the database. Any argument that isn't described below is the new value of the field with the same name in the aqorders @@ -387,6 +382,8 @@ entry to the aqorderbreakdown table, with the order number just found, and the book fund ID of the newly-added order. C<$budget> is effectively ignored. + If it's undef (anything false) or the string 'now', the current day is used. + Else, the upcoming July 1st is used. C<$subscription> may be either "yes", or anything else for "no". @@ -400,7 +397,8 @@ sub NewOrder { $listprice, $booksellerid, $authorisedby, $notes, $bookfund, $bibitemnum, $rrp, $ecost, $gst, $budget, $cost, $sub, - $invoice, $sort1, $sort2, $purchaseorder + $invoice, $sort1, $sort2, $purchaseorder, + $branchcode ) = @_; @@ -408,18 +406,18 @@ sub NewOrder { my $month = localtime->mon() + 1; # months starts at 0, add 1 if ( !$budget || $budget eq 'now' ) { - $budget = "now()"; + $budget = undef; } # if month is july or more, budget start is 1 jul, next year. elsif ( $month >= '7' ) { ++$year; # add 1 to year , coz its next year - $budget = "'$year-07-01'"; + $budget = "$year-07-01"; } else { # START OF NEW BUDGET, 1ST OF JULY, THIS YEAR - $budget = "'$year-07-01'"; + $budget = "$year-07-01"; } if ( $sub eq 'yes' ) { @@ -437,27 +435,30 @@ sub NewOrder { my $dbh = C4::Context->dbh; my $query = " INSERT INTO aqorders - ( biblionumber,title,basketno,quantity,listprice,notes, - biblioitemnumber,rrp,ecost,gst,unitprice,subscription,sort1,sort2,budgetdate,entrydate,purchaseordernumber) - VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,$budget,now(),? ) + ( biblionumber, title, basketno, quantity, listprice, + notes, biblioitemnumber, rrp, ecost, gst, + unitprice, subscription, sort1, sort2, budgetdate, + entrydate, purchaseordernumber) + VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,COALESCE(?,NOW()),NOW(),? ) "; my $sth = $dbh->prepare($query); $sth->execute( $bibnum, $title, $basketno, $quantity, $listprice, $notes, $bibitemnum, $rrp, $ecost, $gst, - $cost, $sub, $sort1, $sort2, $purchaseorder + $cost, $sub, $sort1, $sort2, $budget, + $purchaseorder ); $sth->finish; #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null my $ordnum = $dbh->{'mysql_insertid'}; $query = " - INSERT INTO aqorderbreakdown (ordernumber,bookfundid) - VALUES (?,?) + INSERT INTO aqorderbreakdown (ordernumber,bookfundid, branchcode) + VALUES (?,?,?) "; $sth = $dbh->prepare($query); - $sth->execute( $ordnum, $bookfund ); + $sth->execute( $ordnum, $bookfund, $branchcode ); $sth->finish; return ( $basketno, $ordnum ); } @@ -471,7 +472,7 @@ sub NewOrder { &ModOrder($title, $ordernumber, $quantity, $listprice, $biblionumber, $basketno, $supplier, $who, $notes, $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget, - $unitprice, $booksellerinvoicenumber); + $unitprice, $booksellerinvoicenumber, $branchcode); Modifies an existing order. Updates the order with order number C<$ordernumber> and biblionumber C<$biblionumber>. All other arguments @@ -479,7 +480,7 @@ update the fields with the same name in the aqorders table of the Koha database. Entries with order number C<$ordernumber> in the aqorderbreakdown -table are also updated to the new book fund ID. +table are also updated to the new book fund ID or branchcode. =back @@ -490,9 +491,10 @@ sub ModOrder { $title, $ordnum, $quantity, $listprice, $bibnum, $basketno, $supplier, $who, $notes, $bookfund, $bibitemnum, $rrp, $ecost, $gst, $budget, - $cost, $invoice, $sort1, $sort2, $purchaseorder + $cost, $invoice, $sort1, $sort2, $purchaseorder, $branchcode ) = @_; + # FIXME : Refactor to pass a hashref instead of fifty params. my $dbh = C4::Context->dbh; my $query = " UPDATE aqorders @@ -512,20 +514,20 @@ sub ModOrder { $sth->finish; $query = " UPDATE aqorderbreakdown - SET bookfundid=? + SET bookfundid=?,branchcode=? WHERE ordernumber=? "; $sth = $dbh->prepare($query); - unless ( $sth->execute( $bookfund, $ordnum ) ) - { # zero rows affected [Bug 734] + my $rv = $sth->execute( $bookfund,$branchcode, $ordnum ); + unless($rv && ( $rv ne '0E0' )) { # zero rows affected [Bug 734] my $query =" INSERT INTO aqorderbreakdown - (ordernumber,bookfundid) - VALUES (?,?) + (ordernumber,branchcode,bookfundid) + VALUES (?,?,?) "; $sth = $dbh->prepare($query); - $sth->execute( $ordnum, $bookfund ); + $sth->execute( $ordnum,$branchcode, $bookfund ); } $sth->finish; } @@ -630,10 +632,7 @@ sub ModReceiveOrder { $order->{'listprice'},$order->{'booksellerid'},$order->{'authorisedby'},$order->{'notes'}, $order->{'bookfundid'},$order->{'biblioitemnumber'},$order->{'rrp'},$order->{'ecost'},$order->{'gst'}, $order->{'budget'},$order->{'unitcost'},$order->{'sub'},'',$order->{'sort1'},$order->{'sort2'},$order->{'purchaseordernumber'}); - - $sth=$dbh->prepare(" insert into aqorderbreakdown (ordernumber, branchcode, bookfundid) values (?,?,?)"); - $sth->execute($newOrder,$order->{branch},$order->{bookfundid}); - } else { + } else { $sth=$dbh->prepare("update aqorders set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?, unitprice=?,freight=?,rrp=? @@ -680,7 +679,7 @@ C<@results> is an array of references-to-hash with the following keys: =cut sub SearchOrder { - my ( $search, $id, $biblionumber, $catview ) = @_; + my ( $search, $id, $biblionumber ) = @_; my $dbh = C4::Context->dbh; my @data = split( ' ', $search ); my @searchterms; @@ -691,7 +690,20 @@ sub SearchOrder { push( @searchterms, $search, $search, $biblionumber ); my $query; ### FIXME THIS CAN raise a problem if more THAN ONE biblioitem is linked to one biblio - if ($id) { + if(not $id and $biblionumber and $search){ + $query = "SELECT *,biblio.title + FROM aqorders + LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber + LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber + LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno + WHERE ((datecancellationprinted is NULL) + OR (datecancellationprinted = '0000-00-00')) + AND aqorders.biblionumber = ? + AND aqorders.ordernumber = ? + "; + @searchterms = ($biblionumber, $search); + } + elsif($id) { $query = "SELECT *,biblio.title FROM aqorders @@ -707,7 +719,6 @@ sub SearchOrder { map { "(biblio.title like ? or biblio.title like ?)" } @data ) ) . ") OR biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) "; - } else { $query = @@ -726,6 +737,7 @@ sub SearchOrder { ) . ") or biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) "; } + $query .= " GROUP BY aqorders.ordernumber"; ### $query my $sth = $dbh->prepare($query); @@ -791,13 +803,8 @@ sub DelOrder { $sth->finish; } - -=back - =head2 FUNCTIONS ABOUT PARCELS -=over 2 - =cut #------------------------------------------------------------# @@ -846,24 +853,23 @@ sub GetParcel { LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber WHERE - aqbasket.booksellerid=? - AND aqorders.booksellerinvoicenumber LIKE \"$code\" - AND aqorders.datereceived= \'$datereceived\'"; + aqbasket.booksellerid = ? + AND aqorders.booksellerinvoicenumber LIKE ? + AND aqorders.datereceived = ? "; + my @query_params = ( $supplierid, $code, $datereceived ); if ( C4::Context->preference("IndependantBranches") ) { my $userenv = C4::Context->userenv; if ( ($userenv) && ( $userenv->{flags} != 1 ) ) { - $strsth .= - " AND (borrowers.branchcode = '" - . $userenv->{branch} - . "' OR borrowers.branchcode ='')"; + $strsth .= " and (borrowers.branchcode = ? + or borrowers.branchcode = '')"; + push @query_params, $userenv->{branch}; } } $strsth .= " ORDER BY aqbasket.basketno"; ### parcelinformation : $strsth - # warn "STH : $strsth"; my $sth = $dbh->prepare($strsth); - $sth->execute($supplierid); + $sth->execute( @query_params ); while ( my $data = $sth->fetchrow_hashref ) { push( @results, $data ); } @@ -882,8 +888,12 @@ sub GetParcel { $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto); get a lists of parcels. +=back + * Input arg : +=over 4 + =item $bookseller is the bookseller this function has to get parcels. @@ -914,6 +924,7 @@ a pointer on a hash list containing parcel informations as such : sub GetParcels { my ($bookseller,$order, $code, $datefrom, $dateto) = @_; my $dbh = C4::Context->dbh; + my @query_params = (); my $strsth =" SELECT aqorders.booksellerinvoicenumber, datereceived,purchaseordernumber, @@ -924,18 +935,31 @@ sub GetParcels { WHERE aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL "; - $strsth .= "and aqorders.booksellerinvoicenumber like \"$code%\" " if ($code); - - $strsth .= "and datereceived >=" . $dbh->quote($datefrom) . " " if ($datefrom); + if ( defined $code ) { + $strsth .= ' and aqorders.booksellerinvoicenumber like ? '; + # add a % to the end of the code to allow stemming. + push @query_params, "$code%"; + } + + if ( defined $datefrom ) { + $strsth .= ' and datereceived >= ? '; + push @query_params, $datefrom; + } - $strsth .= "and datereceived <=" . $dbh->quote($dateto) . " " if ($dateto); + if ( defined $dateto ) { + $strsth .= 'and datereceived <= ? '; + push @query_params, $dateto; + } $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived "; + + # can't use a placeholder to place this column name. + # but, we could probably be checking to make sure it is a column that will be fetched. $strsth .= "order by $order " if ($order); -### $strsth + my $sth = $dbh->prepare($strsth); - $sth->execute; + $sth->execute( @query_params ); my $results = $sth->fetchall_arrayref({}); $sth->finish; return @$results; @@ -966,96 +990,81 @@ sub GetLateOrders { my $dbh = C4::Context->dbh; #BEWARE, order of parenthesis and LEFT JOIN is important for speed - my $strsth; my $dbdriver = C4::Context->config("db_scheme") || "mysql"; - # warn " $dbdriver"; - if ( $dbdriver eq "mysql" ) { - $strsth = " - SELECT aqbasket.basketno,aqorders.ordernumber, - DATE(aqbasket.closedate) AS orderdate, - aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity, - aqorders.rrp AS unitpricesupplier, - aqorders.ecost AS unitpricelib, - (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal, - aqbookfund.bookfundname AS budget, - borrowers.branchcode AS branch, - aqbooksellers.name AS supplier, - aqorders.title, - biblio.author, - biblioitems.publishercode AS publisher, - biblioitems.publicationyear, - DATEDIFF(CURDATE( ),closedate) AS latesince - FROM ((( - (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber) - LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber) - LEFT JOIN aqorderbreakdown ON aqorders.ordernumber = aqorderbreakdown.ordernumber) - LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid), - (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber) - LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id - WHERE aqorders.basketno = aqbasket.basketno - AND (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY)) - AND ((datereceived = '' OR datereceived is null) - OR (aqorders.quantityreceived < aqorders.quantity) ) - "; - $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid); - $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'" - if ($branch); - $strsth .= - " AND borrowers.branchcode like \'" - . C4::Context->userenv->{branch} . "\'" - if ( C4::Context->preference("IndependantBranches") - && C4::Context->userenv - && C4::Context->userenv->{flags} != 1 ); - $strsth .=" HAVING quantity<>0 - AND unitpricesupplier<>0 - AND unitpricelib<>0 - ORDER BY latesince,basketno,borrowers.branchcode, supplier - "; + my @query_params = ($delay); # delay is the first argument regardless + my $select = " + SELECT aqbasket.basketno, + aqorders.ordernumber, + DATE(aqbasket.closedate) AS orderdate, + aqorders.rrp AS unitpricesupplier, + aqorders.ecost AS unitpricelib, + aqbookfund.bookfundname AS budget, + borrowers.branchcode AS branch, + aqbooksellers.name AS supplier, + aqorders.title, + biblio.author, + biblioitems.publishercode AS publisher, + biblioitems.publicationyear, + "; + my $from = " + FROM ((( + (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber) + LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber) + LEFT JOIN aqorderbreakdown ON aqorders.ordernumber = aqorderbreakdown.ordernumber) + LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid), + (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber) + LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id + WHERE aqorders.basketno = aqbasket.basketno + AND ( (datereceived = '' OR datereceived IS NULL) + OR (aqorders.quantityreceived < aqorders.quantity) + ) + "; + my $having = ""; + if ($dbdriver eq "mysql") { + $select .= " + aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity, + (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal, + DATEDIFF(CURDATE( ),closedate) AS latesince + "; + $from .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) "; + $having = " + HAVING quantity <> 0 + AND unitpricesupplier <> 0 + AND unitpricelib <> 0 + "; + } else { + # FIXME: account for IFNULL as above + $select .= " + aqorders.quantity AS quantity, + aqorders.quantity * aqorders.rrp AS subtotal, + (CURDATE - closedate) AS latesince + "; + $from .= " AND (closedate <= (CURDATE -(INTERVAL ? DAY)) "; } - else { - $strsth = " - SELECT aqbasket.basketno, - DATE(aqbasket.closedate) AS orderdate, - aqorders.quantity, aqorders.rrp AS unitpricesupplier, - aqorders.ecost as unitpricelib, - aqorders.quantity * aqorders.rrp AS subtotal - aqbookfund.bookfundname AS budget, - borrowers.branchcode AS branch, - aqbooksellers.name AS supplier, - biblio.title, - biblio.author, - biblioitems.publishercode AS publisher, - biblioitems.publicationyear, - (CURDATE - closedate) AS latesince - FROM(( ( - (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber) - LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber) - LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber) - LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid), - (aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id - WHERE aqorders.basketno = aqbasket.basketno - AND (closedate < (CURDATE -(INTERVAL $delay DAY)) - AND ((datereceived = '' OR datereceived is null) - OR (aqorders.quantityreceived < aqorders.quantity) ) "; - $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid); - - $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'" if ($branch); - $strsth .=" AND borrowers.branchcode like \'". C4::Context->userenv->{branch} . "\'" - if (C4::Context->preference("IndependantBranches") && C4::Context->userenv->{flags} != 1 ); - $strsth .=" ORDER BY latesince,basketno,borrowers.branchcode, supplier"; + if (defined $supplierid) { + $from .= ' AND aqbasket.booksellerid = ? '; + push @query_params, $supplierid; } - my $sth = $dbh->prepare($strsth); - $sth->execute; + if (defined $branch) { + $from .= ' AND borrowers.branchcode LIKE ? '; + push @query_params, $branch; + } + if (C4::Context->preference("IndependantBranches") + && C4::Context->userenv + && C4::Context->userenv->{flags} != 1 ) { + $from .= ' AND borrowers.branchcode LIKE ? '; + push @query_params, C4::Context->userenv->{branch}; + } + my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier"; + $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params); + my $sth = $dbh->prepare($query); + $sth->execute(@query_params); my @results; - my $hilighted = 1; - while ( my $data = $sth->fetchrow_hashref ) { - $data->{hilighted} = $hilighted if ( $hilighted > 0 ); - $data->{orderdate} = format_date( $data->{orderdate} ); + while (my $data = $sth->fetchrow_hashref) { + $data->{orderdate} = format_date($data->{orderdate}); push @results, $data; - $hilighted = -$hilighted; } - $sth->finish; return @results; } @@ -1065,9 +1074,31 @@ sub GetLateOrders { =over 4 -(\@order_loop, $total_qty, $total_price, $total_qtyreceived)=&GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on ) - -this function get the search history. +(\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on ); + + Retreives some acquisition history information + + returns: + $order_loop is a list of hashrefs that each look like this: + { + 'author' => 'Twain, Mark', + 'basketno' => '1', + 'biblionumber' => '215', + 'count' => 1, + 'creationdate' => 'MM/DD/YYYY', + 'datereceived' => undef, + 'ecost' => '1.00', + 'id' => '1', + 'invoicenumber' => undef, + 'name' => '', + 'ordernumber' => '1', + 'quantity' => 1, + 'quantityreceived' => undef, + 'title' => 'The Adventures of Huckleberry Finn' + } + $total_qty is the sum of all of the quantities in $order_loop + $total_price is the cost of each in $order_loop times the quantity + $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop =back @@ -1105,35 +1136,45 @@ sub GetHistory { $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber" if ( C4::Context->preference("IndependantBranches") ); - $query .= " WHERE 1 "; - $query .= " AND biblio.title LIKE " . $dbh->quote( "%" . $title . "%" ) - if $title; + $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') "; + + my @query_params = (); + + if ( defined $title ) { + $query .= " AND biblio.title LIKE ? "; + push @query_params, "%$title%"; + } - $query .= - " AND biblio.author LIKE " . $dbh->quote( "%" . $author . "%" ) - if $author; + if ( defined $author ) { + $query .= " AND biblio.author LIKE ? "; + push @query_params, "%$author%"; + } - $query .= " AND name LIKE " . $dbh->quote( "%" . $name . "%" ) if $name; + if ( defined $name ) { + $query .= " AND name LIKE ? "; + push @query_params, "%$name%"; + } - $query .= " AND creationdate >" . $dbh->quote($from_placed_on) - if $from_placed_on; + if ( defined $from_placed_on ) { + $query .= " AND creationdate >= ? "; + push @query_params, $from_placed_on; + } - $query .= " AND creationdate<" . $dbh->quote($to_placed_on) - if $to_placed_on; - $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00')"; + if ( defined $to_placed_on ) { + $query .= " AND creationdate <= ? "; + push @query_params, $to_placed_on; + } if ( C4::Context->preference("IndependantBranches") ) { my $userenv = C4::Context->userenv; if ( ($userenv) && ( $userenv->{flags} != 1 ) ) { - $query .= - " AND (borrowers.branchcode = '" - . $userenv->{branch} - . "' OR borrowers.branchcode ='')"; + $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) "; + push @query_params, $userenv->{branch}; } } $query .= " ORDER BY booksellerid"; my $sth = $dbh->prepare($query); - $sth->execute; + $sth->execute( @query_params ); my $cnt = 1; while ( my $line = $sth->fetchrow_hashref ) { $line->{count} = $cnt++; @@ -1175,14 +1216,9 @@ sub GetRecentAcqui { return \@results; } -END { } # module clean-up code here (global destructor) - 1; - __END__ -=back - =head1 AUTHOR Koha Developement team