X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FAcquisition.pm;h=67590067ec13d96199ce4114899457e900de233f;hb=288b74cf3a5accace7e85b59ac54324242bf7bf0;hp=0530d27a02ac9259c36c36d96a8280e36ec06b62;hpb=2eda32b247480f270d332d893b4079abe91ccb52;p=koha.git diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 0530d27a02..67590067ec 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -17,16 +17,34 @@ package C4::Acquisition; # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place, # Suite 330, Boston, MA 02111-1307 USA + use strict; -require Exporter; use C4::Context; +use C4::Dates qw(format_date); use MARC::Record; -# use C4::Biblio; +use C4::Suggestions; +use Time::localtime; use vars qw($VERSION @ISA @EXPORT); -# set the version for version checking -$VERSION = 0.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 +my $library_name = C4::Context->preference("LibraryName"); =head1 NAME @@ -34,495 +52,600 @@ C4::Acquisition - Koha functions for dealing with orders and acquisitions =head1 SYNOPSIS - use C4::Acquisition; +use C4::Acquisition; =head1 DESCRIPTION The functions in this module deal with acquisitions, managing book -orders, converting money to different currencies, and so forth. +orders, basket and parcels. =head1 FUNCTIONS =over 2 -=cut +=head2 FUNCTIONS ABOUT BASKETS -@ISA = qw(Exporter); -@EXPORT = qw( - &getbasket &getbasketcontent &newbasket &closebasket +=over 2 - &getorders &getallorders &getrecorders - &getorder &neworder &delorder - &ordersearch &histsearch - &modorder &getsingleorder &invoice &receiveorder - &updaterecorder &newordernum +=head3 GetBasket - &bookfunds &curconvert &getcurrencies &bookfundbreakdown - &updatecurrencies &getcurrency +=over 4 - &branches &updatesup &insertsup - &bookseller &breakdown -); +$aqbasket = &GetBasket($basketnumber); -# -# -# -# BASKETS -# -# -# -=item getbasket +get all basket informations in aqbasket for a given basket - $aqbasket = &getbasket($basketnumber); +return : +informations for a given basket returned as a hashref. + +=back + +=back -get all basket informations in aqbasket for a given basket =cut -sub getbasket { - my ($basketno)=@_; - my $dbh=C4::Context->dbh; - my $sth=$dbh->prepare("select aqbasket.*,borrowers.firstname+' '+borrowers.surname as authorisedbyname from aqbasket left join borrowers on aqbasket.authorisedby=borrowers.borrowernumber where basketno=?"); - $sth->execute($basketno); - return($sth->fetchrow_hashref); +sub GetBasket { + my ($basketno) = @_; + my $dbh = C4::Context->dbh; + my $query = " + SELECT aqbasket.*, + concat( b.firstname,' ',b.surname) AS authorisedbyname, + b.branchcode AS branch + FROM aqbasket + LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber + WHERE basketno=? + "; + my $sth=$dbh->prepare($query); + $sth->execute($basketno); + my $basket = $sth->fetchrow_hashref; + return ( $basket ); } -=item getbasketcontent +#------------------------------------------------------------# - ($count, @orders) = &getbasketcontent($basketnumber, $booksellerID); +=head3 NewBasket -Looks up the pending (non-cancelled) orders with the given basket -number. If C<$booksellerID> is non-empty, only orders from that seller -are returned. +=over 4 -C<&basket> returns a two-element array. C<@orders> is an array of -references-to-hash, whose keys are the fields from the aqorders, -biblio, and biblioitems tables in the Koha database. C<$count> is the -number of elements in C<@orders>. +$basket = &NewBasket(); + +Create a new basket in aqbasket table + +=back =cut -#' -sub getbasketcontent { - my ($basketno,$supplier,$orderby)=@_; - my $dbh = C4::Context->dbh; - my $query="Select *,biblio.title from aqorders,biblio,biblioitems - left join aqorderbreakdown on aqorderbreakdown.ordernumber=aqorders.ordernumber - where basketno='$basketno' - and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber - =aqorders.biblioitemnumber - and (datecancellationprinted is NULL or datecancellationprinted = - '0000-00-00')"; - if ($supplier ne ''){ - $query.=" and aqorders.booksellerid='$supplier'"; - } - - $orderby="biblioitems.publishercode" unless $orderby; - $query.=" order by $orderby"; - my $sth=$dbh->prepare($query); - $sth->execute; - my @results; - # print $query; - my $i=0; - while (my $data=$sth->fetchrow_hashref){ - $results[$i]=$data; - $i++; - } - $sth->finish; - return($i,@results); + +# FIXME : this function seems to be unused. + +sub NewBasket { + my ( $booksellerid, $authorisedby ) = @_; + my $dbh = C4::Context->dbh; + my $query = " + INSERT INTO aqbasket + (creationdate,booksellerid,authorisedby) + VALUES (now(),'$booksellerid','$authorisedby') + "; + my $sth = + $dbh->do($query); + +#find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-( + my $basket = $dbh->{'mysql_insertid'}; + return $basket; } -=item newbasket +#------------------------------------------------------------# - $basket = &newbasket(); +=head3 CloseBasket -Create a new basket in aqbasket table -=cut +=over 4 -sub newbasket { - my ($booksellerid,$authorisedby) = @_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->do("insert into aqbasket (creationdate,booksellerid,authorisedby) values(now(),'$booksellerid','$authorisedby')"); - #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-( - my $basket = $dbh->{'mysql_insertid'}; - return($basket); -} +&CloseBasket($basketno); -=item closebasket +close a basket (becomes unmodifiable,except for recieves) - &newbasket($basketno); +=back -close a basket (becomes unmodifiable,except for recieves =cut -sub closebasket { - my ($basketno) = @_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("update aqbasket set closedate=now() where basketno=?"); - $sth->execute($basketno); +sub CloseBasket { + my ($basketno) = @_; + my $dbh = C4::Context->dbh; + my $query = " + UPDATE aqbasket + SET closedate=now() + WHERE basketno=? + "; + my $sth = $dbh->prepare($query); + $sth->execute($basketno); } -=item neworder +#------------------------------------------------------------# - &neworder($basket, $biblionumber, $title, $quantity, $listprice, - $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp, - $ecost, $gst, $budget, $unitprice, $subscription, - $booksellerinvoicenumber); +=back -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 -table of the Koha database. +=head2 FUNCTIONS ABOUT ORDERS -C<$ordnum> is a "minimum order number." After adding the new entry to -the aqorders table, C<&neworder> finds the first entry in aqorders -with order number greater than or equal to C<$ordnum>, and adds an -entry to the aqorderbreakdown table, with the order number just found, -and the book fund ID of the newly-added order. +=over 2 -C<$budget> is effectively ignored. +=cut -C<$subscription> may be either "yes", or anything else for "no". +#------------------------------------------------------------# -=cut -#' -sub neworder { - my ($basketno,$bibnum,$title,$quantity,$listprice,$booksellerid,$authorisedby,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$sub,$invoice,$sort1,$sort2)=@_; - if ($budget eq 'now'){ - $budget="now()"; - } else { - $budget="'2001-07-01'"; - } - if ($sub eq 'yes'){ - $sub=1; - } else { - $sub=0; - } - # if $basket empty, it's also a new basket, create it - unless ($basketno) { - $basketno=newbasket($booksellerid,$authorisedby); - } - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("insert into aqorders - (biblionumber,title,basketno,quantity,listprice,notes, - biblioitemnumber,rrp,ecost,gst,unitprice,subscription,sort1,sort2) - values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); - $sth->execute($bibnum,$title,$basketno,$quantity,$listprice,$notes, - $bibitemnum,$rrp,$ecost,$gst,$cost,$sub,$sort1,$sort2); - $sth->finish; - #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null - my $ordnum = $dbh->{'mysql_insertid'}; - $sth=$dbh->prepare("insert into aqorderbreakdown (ordernumber,bookfundid) values - (?,?)"); - $sth->execute($ordnum,$bookfund); - $sth->finish; - return $basketno; -} +=head3 GetPendingOrders -=item delorder +=over 4 - &delorder($biblionumber, $ordernumber); +$orders = &GetPendingOrders($booksellerid, $grouped); -Cancel the order with the given order and biblio numbers. It does not -delete any entries in the aqorders table, it merely marks them as -cancelled. +Finds pending orders from the bookseller with the given ID. Ignores +completed and cancelled orders. -=cut -#' -sub delorder { - my ($bibnum,$ordnum)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("update aqorders set datecancellationprinted=now() - where biblionumber=? and ordernumber=?"); - $sth->execute($bibnum,$ordnum); - $sth->finish; -} +C<$orders> is a reference-to-array; each element is a +reference-to-hash with the following fields: +C<$grouped> is a boolean that, if set to 1 will group all order lines of the same basket +in a single result line -=item modorder +=over 2 - &modorder($title, $ordernumber, $quantity, $listprice, - $biblionumber, $basketno, $supplier, $who, $notes, - $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget, - $unitprice, $booksellerinvoicenumber); +=item C -Modifies an existing order. Updates the order with order number -C<$ordernumber> and biblionumber C<$biblionumber>. All other arguments -update the fields with the same name in the aqorders table of the Koha -database. +=item C -Entries with order number C<$ordernumber> in the aqorderbreakdown -table are also updated to the new book fund ID. +=item C + +These give the value of the corresponding field in the aqorders table +of the Koha database. + +=back + +=back + +Results are ordered from most to least recent. =cut -#' -sub modorder { - my ($title,$ordnum,$quantity,$listprice,$bibnum,$basketno,$supplier,$who,$notes,$bookfund,$bibitemnum,$rrp,$ecost,$gst,$budget,$cost,$invoice,$sort1,$sort2)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("update aqorders set title=?, - quantity=?,listprice=?,basketno=?, - rrp=?,ecost=?,unitprice=?,booksellerinvoicenumber=?, - notes=?,sort1=?, sort2=? - where - ordernumber=? and biblionumber=?"); - $sth->execute($title,$quantity,$listprice,$basketno,$rrp,$ecost,$cost,$invoice,$notes,$sort1,$sort2,$ordnum,$bibnum); - $sth->finish; - $sth=$dbh->prepare("update aqorderbreakdown set bookfundid=? where - ordernumber=?"); - if ($sth->execute($bookfund,$ordnum) == 0) { # zero rows affected [Bug 734] - my $query="insert into aqorderbreakdown (ordernumber,bookfundid) values (?,?)"; - $sth=$dbh->prepare($query); - $sth->execute($ordnum,$bookfund); - } - $sth->finish; + +sub GetPendingOrders { + my ($supplierid,$grouped) = @_; + my $dbh = C4::Context->dbh; + my $strsth = " + SELECT ".($grouped?"count(*),":"")."aqbasket.basketno, + surname,firstname,aqorders.*, + aqbasket.closedate, aqbasket.creationdate + FROM aqorders + 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) + "; + ## 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 = ? + 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( @query_params ); + my $results = $sth->fetchall_arrayref({}); + $sth->finish; + return $results; } -=item newordernum +#------------------------------------------------------------# - $order = &newordernum(); +=head3 GetOrders -Finds the next unused order number in the aqorders table of the Koha -database, and returns it. +=over 4 + +@orders = &GetOrders($basketnumber, $orderby); + +Looks up the pending (non-cancelled) orders with the given basket +number. If C<$booksellerID> is non-empty, only orders from that seller +are returned. + +return : +C<&basket> returns a two-element array. C<@orders> is an array of +references-to-hash, whose keys are the fields from the aqorders, +biblio, and biblioitems tables in the Koha database. + +=back =cut -#' -# FIXME - Race condition -sub newordernum { - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select max(ordernumber) from aqorders"); - $sth->execute; - my $data=$sth->fetchrow_arrayref; - my $ordnum=$$data[0]; - $ordnum++; - $sth->finish; - return($ordnum); + +sub GetOrders { + my ( $basketno, $orderby ) = @_; + my $dbh = C4::Context->dbh; + my $query =" + SELECT aqorderbreakdown.*, + biblio.*,biblioitems.publishercode, + aqorders.*, + aqbookfund.bookfundname, + biblio.title + FROM aqorders + LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber + LEFT JOIN aqbookfund ON aqbookfund.bookfundid=aqorderbreakdown.bookfundid + LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber + LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber + WHERE basketno=? + AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') + "; + + $orderby = "biblioitems.publishercode,biblio.title" unless $orderby; + $query .= " ORDER BY $orderby"; + my $sth = $dbh->prepare($query); + $sth->execute($basketno); + my @results; + + while ( my $data = $sth->fetchrow_hashref ) { + push @results, $data; + } + $sth->finish; + return @results; } -=item receiveorder +#------------------------------------------------------------# - &receiveorder($biblionumber, $ordernumber, $quantityreceived, $user, - $unitprice, $booksellerinvoicenumber, $biblioitemnumber, - $freight, $bookfund, $rrp); +=head3 GetOrderNumber -Updates an order, to reflect the fact that it was received, at least -in part. All arguments not mentioned below update the fields with the -same name in the aqorders table of the Koha database. +=over 4 -Updates the order with bibilionumber C<$biblionumber> and ordernumber -C<$ordernumber>. +$ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber); -Also updates the book fund ID in the aqorderbreakdown table. +Looks up the ordernumber with the given biblionumber and biblioitemnumber. + +Returns the number of this order. + +=item C<$ordernumber> is the order number. + +=back =cut -#' -sub receiveorder { - my ($biblio,$ordnum,$quantrec,$user,$cost,$invoiceno,$freight,$rrp)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("update aqorders set quantityreceived=?,datereceived=now(),booksellerinvoicenumber=?, - unitprice=?,freight=?,rrp=? - where biblionumber=? and ordernumber=?"); - $sth->execute($quantrec,$invoiceno,$cost,$freight,$rrp,$biblio,$ordnum); - $sth->finish; +sub GetOrderNumber { + my ( $biblionumber,$biblioitemnumber ) = @_; + my $dbh = C4::Context->dbh; + my $query = " + SELECT ordernumber + FROM aqorders + WHERE biblionumber=? + AND biblioitemnumber=? + "; + my $sth = $dbh->prepare($query); + $sth->execute( $biblionumber, $biblioitemnumber ); + + return $sth->fetchrow; } -=item updaterecorder +#------------------------------------------------------------# + +=head3 GetOrder - &updaterecorder($biblionumber, $ordernumber, $user, $unitprice, - $bookfundid, $rrp); +=over 4 + +$order = &GetOrder($ordernumber); + +Looks up an order by order number. -Updates the order with biblionumber C<$biblionumber> and order number -C<$ordernumber>. C<$bookfundid> is the new value for the book fund ID -in the aqorderbreakdown table of the Koha database. All other -arguments update the fields with the same name in the aqorders table. +Returns a reference-to-hash describing the order. The keys of +C<$order> are fields from the biblio, biblioitems, aqorders, and +aqorderbreakdown tables of the Koha database. -C<$user> is ignored. +=back =cut -#' -sub updaterecorder{ - my($biblio,$ordnum,$user,$cost,$bookfund,$rrp)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("update aqorders set - unitprice=?, rrp=? - where biblionumber=? and ordernumber=? - "); - $sth->execute($cost,$rrp,$biblio,$ordnum); - $sth->finish; - $sth=$dbh->prepare("update aqorderbreakdown set bookfundid=? where ordernumber=?"); - $sth->execute($bookfund,$ordnum); - $sth->finish; + +sub GetOrder { + my ($ordnum) = @_; + my $dbh = C4::Context->dbh; + my $query = " + SELECT biblioitems.*, biblio.*, aqorderbreakdown.*, aqorders.* + FROM aqorders + LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber + LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber + LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber + WHERE aqorders.ordernumber=? + + "; + my $sth= $dbh->prepare($query); + $sth->execute($ordnum); + my $data = $sth->fetchrow_hashref; + $sth->finish; + return $data; } -# -# -# ORDERS -# -# +#------------------------------------------------------------# -=item getorders +=head3 NewOrder - ($count, $orders) = &getorders($booksellerid); +=over 4 -Finds pending orders from the bookseller with the given ID. Ignores -completed and cancelled orders. + &NewOrder($basket, $biblionumber, $title, $quantity, $listprice, + $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp, + $ecost, $gst, $budget, $unitprice, $subscription, + $booksellerinvoicenumber, $purchaseorder); -C<$count> is the number of elements in C<@{$orders}>. +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 +table of the Koha database. -C<$orders> is a reference-to-array; each element is a -reference-to-hash with the following fields: +C<$ordnum> is a "minimum order number." After adding the new entry to +the aqorders table, C<&neworder> finds the first entry in aqorders +with order number greater than or equal to C<$ordnum>, and adds an +entry to the aqorderbreakdown table, with the order number just found, +and the book fund ID of the newly-added order. -=over 4 +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". -=item C +=back -Gives the number of orders in with this basket number. +=cut -=item C +sub NewOrder { + my ( + $basketno, $bibnum, $title, $quantity, + $listprice, $booksellerid, $authorisedby, $notes, + $bookfund, $bibitemnum, $rrp, $ecost, + $gst, $budget, $cost, $sub, + $invoice, $sort1, $sort2, $purchaseorder + ) + = @_; + + my $year = localtime->year() + 1900; + my $month = localtime->mon() + 1; # months starts at 0, add 1 + + if ( !$budget || $budget eq 'now' ) { + $budget = undef; + } -=item C + # 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"; + } + else { -=item C + # START OF NEW BUDGET, 1ST OF JULY, THIS YEAR + $budget = "$year-07-01"; + } -These give the value of the corresponding field in the aqorders table -of the Koha database. + if ( $sub eq 'yes' ) { + $sub = 1; + } + else { + $sub = 0; + } -=back + # if $basket empty, it's also a new basket, create it + unless ($basketno) { + $basketno = NewBasket( $booksellerid, $authorisedby ); + } -Results are ordered from most to least recent. + 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 ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,COALESCE(?,NOW()),NOW(),? ) + "; + my $sth = $dbh->prepare($query); + + $sth->execute( + $bibnum, $title, $basketno, $quantity, $listprice, + $notes, $bibitemnum, $rrp, $ecost, $gst, + $cost, $sub, $sort1, $sort2, $budget, + $purchaseorder + ); + $sth->finish; -=cut -#' -sub getorders { - my ($supplierid)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select count(*),authorisedby,creationdate,aqbasket.basketno, - closedate,surname,firstname - from aqorders - 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) - group by basketno order by aqbasket.basketno"); - $sth->execute($supplierid); - my @results = (); - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - $sth->finish; - return (scalar(@results),\@results); + #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null + my $ordnum = $dbh->{'mysql_insertid'}; + $query = " + INSERT INTO aqorderbreakdown (ordernumber,bookfundid) + VALUES (?,?) + "; + $sth = $dbh->prepare($query); + $sth->execute( $ordnum, $bookfund ); + $sth->finish; + return ( $basketno, $ordnum ); } -=item getorder +#------------------------------------------------------------# - ($order, $ordernumber) = &getorder($biblioitemnumber, $biblionumber); +=head3 ModOrder -Looks up the order with the given biblionumber and biblioitemnumber. +=over 4 -Returns a two-element array. C<$ordernumber> is the order number. -C<$order> is a reference-to-hash describing the order; its keys are -fields from the biblio, biblioitems, aqorders, and aqorderbreakdown -tables of the Koha database. +&ModOrder($title, $ordernumber, $quantity, $listprice, + $biblionumber, $basketno, $supplier, $who, $notes, + $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget, + $unitprice, $booksellerinvoicenumber); + +Modifies an existing order. Updates the order with order number +C<$ordernumber> and biblionumber C<$biblionumber>. All other arguments +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. + +=back =cut -sub getorder{ - my ($bi,$bib)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select ordernumber from aqorders where biblionumber=? and biblioitemnumber=?"); - $sth->execute($bib,$bi); - # FIXME - Use fetchrow_array(), since we're only interested in the one - # value. - my $ordnum=$sth->fetchrow_hashref; - $sth->finish; - my $order=getsingleorder($ordnum->{'ordernumber'}); - return ($order,$ordnum->{'ordernumber'}); +sub ModOrder { + my ( + $title, $ordnum, $quantity, $listprice, $bibnum, + $basketno, $supplier, $who, $notes, $bookfund, + $bibitemnum, $rrp, $ecost, $gst, $budget, + $cost, $invoice, $sort1, $sort2, $purchaseorder + ) + = @_; + my $dbh = C4::Context->dbh; + my $query = " + UPDATE aqorders + SET title=?, + quantity=?,listprice=?,basketno=?, + rrp=?,ecost=?,unitprice=?,booksellerinvoicenumber=?, + notes=?,sort1=?, sort2=?, purchaseordernumber=? + WHERE ordernumber=? AND biblionumber=? + "; + my $sth = $dbh->prepare($query); + $sth->execute( + $title, $quantity, $listprice, $basketno, $rrp, + $ecost, $cost, $invoice, $notes, $sort1, + $sort2, $purchaseorder, + $ordnum, $bibnum + ); + $sth->finish; + my $branchcode; + $query = " + UPDATE aqorderbreakdown + SET bookfundid=?,branchcode=? + WHERE ordernumber=? + "; + $sth = $dbh->prepare($query); + + unless ( $sth->execute( $bookfund,$branchcode, $ordnum ) ) + { # zero rows affected [Bug 734] + my $query =" + INSERT INTO aqorderbreakdown + (ordernumber,branchcode,bookfundid) + VALUES (?,?,?) + "; + $sth = $dbh->prepare($query); + $sth->execute( $ordnum,$branchcode, $bookfund ); + } + $sth->finish; } -=item getsingleorder +#------------------------------------------------------------# - $order = &getsingleorder($ordernumber); +=head3 ModOrderBiblioNumber -Looks up an order by order number. +=over 4 -Returns a reference-to-hash describing the order. The keys of -C<$order> are fields from the biblio, biblioitems, aqorders, and -aqorderbreakdown tables of the Koha database. +&ModOrderBiblioNumber($biblioitemnumber,$ordnum, $biblionumber); + +Modifies the biblioitemnumber for an existing order. +Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>. + +=back =cut -sub getsingleorder { - my ($ordnum)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select * from biblio,biblioitems,aqorders left join aqorderbreakdown - on aqorders.ordernumber=aqorderbreakdown.ordernumber - where aqorders.ordernumber=? - and biblio.biblionumber=aqorders.biblionumber and - biblioitems.biblioitemnumber=aqorders.biblioitemnumber"); - $sth->execute($ordnum); - my $data=$sth->fetchrow_hashref; - $sth->finish; - return($data); +sub ModOrderBiblioNumber { + my ($biblioitemnumber,$ordnum, $biblionumber) = @_; + my $dbh = C4::Context->dbh; + my $query = " + UPDATE aqorders + SET biblioitemnumber = ? + WHERE ordernumber = ? + AND biblionumber = ?"; + my $sth = $dbh->prepare($query); + $sth->execute( $biblioitemnumber, $ordnum, $biblionumber ); } -=item getallorders +#------------------------------------------------------------# - ($count, @results) = &getallorders($booksellerid); +=head3 ModReceiveOrder -Looks up all of the pending orders from the supplier with the given -bookseller ID. Ignores cancelled and completed orders. +=over 4 -C<$count> is the number of elements in C<@results>. C<@results> is an -array of references-to-hash. The keys of each element are fields from -the aqorders, biblio, and biblioitems tables of the Koha database. +&ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user, + $unitprice, $booksellerinvoicenumber, $biblioitemnumber, + $freight, $bookfund, $rrp); -C<@results> is sorted alphabetically by book title. +Updates an order, to reflect the fact that it was received, at least +in part. All arguments not mentioned below update the fields with the +same name in the aqorders table of the Koha database. + +If a partial order is received, splits the order into two. The received +portion must have a booksellerinvoicenumber. + +Updates the order with bibilionumber C<$biblionumber> and ordernumber +C<$ordernumber>. + +Also updates the book fund ID in the aqorderbreakdown table. + +=back =cut -#' -sub getallorders { - #gets all orders from a certain supplier, orders them alphabetically - my ($supid)=@_; - my $dbh = C4::Context->dbh; - my @results = (); - my $sth=$dbh->prepare("Select * from aqorders,biblio,biblioitems,aqbasket where aqbasket.basketno=aqorders.basketno - and booksellerid=? - and (cancelledby is NULL or cancelledby = '') - and (quantityreceived < quantity or quantityreceived is NULL) - and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber= - aqorders.biblioitemnumber - group by aqorders.biblioitemnumber - order by - biblio.title"); - $sth->execute($supid); - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - $sth->finish; - return(scalar(@results),@results); -} -# FIXME - Never used -sub getrecorders { - #gets all orders from a certain supplier, orders them alphabetically - my ($supid)=@_; - my $dbh = C4::Context->dbh; - my @results= (); - my $sth=$dbh->prepare("Select * from aqorders,biblio,biblioitems where booksellerid=? - and (cancelledby is NULL or cancelledby = '') - and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber= - aqorders.biblioitemnumber and - aqorders.quantityreceived>0 - and aqorders.datereceived >=now() - group by aqorders.biblioitemnumber - order by - biblio.title"); - $sth->execute($supid); - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - $sth->finish; - return(scalar(@results),@results); + +sub ModReceiveOrder { + my ( + $biblionumber, $ordnum, $quantrec, $user, $cost, + $invoiceno, $freight, $rrp, $bookfund, $datereceived + ) + = @_; + my $dbh = C4::Context->dbh; +# warn "DATE BEFORE : $daterecieved"; +# $daterecieved=POSIX::strftime("%Y-%m-%d",CORE::localtime) unless $daterecieved; +# warn "DATE REC : $daterecieved"; + $datereceived = C4::Dates->output('iso') unless $datereceived; + my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber ); + if ($suggestionid) { + ModStatus( $suggestionid, 'AVAILABLE', '', $biblionumber ); + } + # Allows libraries to change their bookfund during receiving orders + # allows them to adjust budgets + if ( C4::Context->preference("LooseBudgets") && $bookfund ) { + my $query = " + UPDATE aqorderbreakdown + SET bookfundid=? + WHERE ordernumber=? + "; + my $sth = $dbh->prepare($query); + $sth->execute( $bookfund, $ordnum ); + $sth->finish; + } + + my $sth=$dbh->prepare("SELECT * FROM aqorders LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber + WHERE biblionumber=? AND aqorders.ordernumber=?"); + $sth->execute($biblionumber,$ordnum); + my $order = $sth->fetchrow_hashref(); + $sth->finish(); + + if ( $order->{quantity} > $quantrec ) { + $sth=$dbh->prepare("update aqorders + set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?, + unitprice=?,freight=?,rrp=?,quantity=? + where biblionumber=? and ordernumber=?"); + $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$quantrec,$biblionumber,$ordnum); + $sth->finish; + # create a new order for the remaining items, and set its bookfund. + my $newOrder = NewOrder($order->{'basketno'},$order->{'biblionumber'},$order->{'title'}, $order->{'quantity'} - $quantrec, + $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'}); + } else { + $sth=$dbh->prepare("update aqorders + set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?, + unitprice=?,freight=?,rrp=? + where biblionumber=? and ordernumber=?"); + $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$biblionumber,$ordnum); + $sth->finish; + } + return $datereceived; } +#------------------------------------------------------------# -=item ordersearch +=head3 SearchOrder - ($count, @results) = &ordersearch($search, $biblionumber, $complete); +@results = &SearchOrder($search, $biblionumber, $complete); Searches for orders. @@ -537,9 +660,8 @@ words). If C<$complete> is C, the results will include only completed orders. In any case, C<&ordersearch> ignores cancelled orders. -C<&ordersearch> returns an array. C<$count> is the number of elements -in C<@results>. C<@results> is an array of references-to-hash with the -following keys: +C<&ordersearch> returns an array. +C<@results> is an array of references-to-hash with the following keys: =over 4 @@ -554,377 +676,526 @@ following keys: =back =cut -#' -sub ordersearch { - my ($search,$id,$biblio,$catview) = @_; - my $dbh = C4::Context->dbh; - my @data = split(' ',$search); - my @searchterms = ($id); - map { push(@searchterms,"$_%","% $_%") } @data; - push(@searchterms,$search,$search,$biblio); - my $sth=$dbh->prepare("Select *,biblio.title from aqorders,biblioitems,biblio,aqbasket - where aqorders.biblioitemnumber = biblioitems.biblioitemnumber and - aqorders.basketno = aqbasket.basketno - and aqbasket.booksellerid = ? - and biblio.biblionumber=aqorders.biblionumber - and ((datecancellationprinted is NULL) - or (datecancellationprinted = '0000-00-00')) - and ((" - .(join(" and ",map { "(biblio.title like ? or biblio.title like ?)" } @data)) - .") or biblioitems.isbn=? or (aqorders.ordernumber=? and aqorders.biblionumber=?)) " - .(($catview ne 'yes')?" and (quantityreceived < quantity or quantityreceived is NULL)":"") - ." group by aqorders.ordernumber"); - $sth->execute(@searchterms); - my @results = (); - my $sth2=$dbh->prepare("Select * from biblio where biblionumber=?"); - my $sth3=$dbh->prepare("Select * from aqorderbreakdown where ordernumber=?"); - while (my $data=$sth->fetchrow_hashref){ - $sth2->execute($data->{'biblionumber'}); - my $data2=$sth2->fetchrow_hashref; - $data->{'author'}=$data2->{'author'}; - $data->{'seriestitle'}=$data2->{'seriestitle'}; - $sth3->execute($data->{'ordernumber'}); - my $data3=$sth3->fetchrow_hashref; - $data->{'branchcode'}=$data3->{'branchcode'}; - $data->{'bookfundid'}=$data3->{'bookfundid'}; - push(@results,$data); - } - $sth->finish; - $sth2->finish; - $sth3->finish; - return(scalar(@results),@results); -} +sub SearchOrder { + my ( $search, $id, $biblionumber, $catview ) = @_; + my $dbh = C4::Context->dbh; + my @data = split( ' ', $search ); + my @searchterms; + if ($id) { + @searchterms = ($id); + } + map { push( @searchterms, "$_%", "%$_%" ) } @data; + 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) { + $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 aqbasket.booksellerid = ? + AND ((datecancellationprinted is NULL) + OR (datecancellationprinted = '0000-00-00')) + AND ((" + . ( + join( " AND ", + map { "(biblio.title like ? or biblio.title like ?)" } @data ) + ) + . ") OR biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) "; -sub histsearch { - my ($title,$author,$name)=@_; - my $dbh= C4::Context->dbh; - my $query = "select biblio.title,aqorders.basketno,name,aqbasket.creationdate,aqorders.datereceived, aqorders.quantity - from aqorders,aqbasket,aqbooksellers,biblio - where aqorders.basketno=aqbasket.basketno and aqbasket.booksellerid=aqbooksellers.id and - biblio.biblionumber=aqorders.biblionumber"; - $query .= " and biblio.title like ".$dbh->quote("%".$title."%") if $title; - $query .= " and biblio.author like ".$dbh->quote("%".$author."%") if $author; - $query .= " and name like ".$dbh->quote("%".$name."%") if $name; - warn "Q : $query"; - my $sth = $dbh->prepare($query); - $sth->execute; - my @order_loop; - while (my $line = $sth->fetchrow_hashref) { - push @order_loop, $line; - } - return \@order_loop; + } + else { + $query = + " SELECT *,biblio.title + FROM aqorders + LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber + LEFT JOIN aqbasket on aqorders.basketno=aqbasket.basketno + LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber + WHERE ((datecancellationprinted is NULL) + OR (datecancellationprinted = '0000-00-00')) + AND (aqorders.quantityreceived < aqorders.quantity OR aqorders.quantityreceived is NULL) + AND ((" + . ( + join( " AND ", + map { "(biblio.title like ? OR biblio.title like ?)" } @data ) + ) + . ") or biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) "; + } + $query .= " GROUP BY aqorders.ordernumber"; + ### $query + my $sth = $dbh->prepare($query); + $sth->execute(@searchterms); + my @results = (); + my $query2 = " + SELECT * + FROM biblio + WHERE biblionumber=? + "; + my $sth2 = $dbh->prepare($query2); + my $query3 = " + SELECT * + FROM aqorderbreakdown + WHERE ordernumber=? + "; + my $sth3 = $dbh->prepare($query3); + + while ( my $data = $sth->fetchrow_hashref ) { + $sth2->execute( $data->{'biblionumber'} ); + my $data2 = $sth2->fetchrow_hashref; + $data->{'author'} = $data2->{'author'}; + $data->{'seriestitle'} = $data2->{'seriestitle'}; + $sth3->execute( $data->{'ordernumber'} ); + my $data3 = $sth3->fetchrow_hashref; + $data->{'branchcode'} = $data3->{'branchcode'}; + $data->{'bookfundid'} = $data3->{'bookfundid'}; + push( @results, $data ); + } + ### @results + $sth->finish; + $sth2->finish; + $sth3->finish; + return @results; } -# -# -# MONEY -# -# -=item invoice +#------------------------------------------------------------# - ($count, @results) = &invoice($booksellerinvoicenumber); +=head3 DelOrder -Looks up orders by invoice number. +=over 4 -Returns an array. C<$count> is the number of elements in C<@results>. -C<@results> is an array of references-to-hash; the keys of each -elements are fields from the aqorders, biblio, and biblioitems tables -of the Koha database. +&DelOrder($biblionumber, $ordernumber); + +Cancel the order with the given order and biblio numbers. It does not +delete any entries in the aqorders table, it merely marks them as +cancelled. + +=back =cut -#' -sub invoice { - my ($invoice)=@_; - my $dbh = C4::Context->dbh; - my @results = (); - my $sth=$dbh->prepare("Select * from aqorders,biblio,biblioitems where - booksellerinvoicenumber=? - and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber= - aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber"); - $sth->execute($invoice); - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - $sth->finish; - return(scalar(@results),@results); + +sub DelOrder { + my ( $bibnum, $ordnum ) = @_; + my $dbh = C4::Context->dbh; + my $query = " + UPDATE aqorders + SET datecancellationprinted=now() + WHERE biblionumber=? AND ordernumber=? + "; + my $sth = $dbh->prepare($query); + $sth->execute( $bibnum, $ordnum ); + $sth->finish; } -=item bookfunds - ($count, @results) = &bookfunds(); +=back -Returns a list of all book funds. +=head2 FUNCTIONS ABOUT PARCELS -C<$count> is the number of elements in C<@results>. C<@results> is an -array of references-to-hash, whose keys are fields from the aqbookfund -and aqbudget tables of the Koha database. Results are ordered -alphabetically by book fund name. +=over 2 =cut -#' -sub bookfunds { - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select * from aqbookfund,aqbudget where aqbookfund.bookfundid - =aqbudget.bookfundid - group by aqbookfund.bookfundid order by bookfundname"); - $sth->execute; - my @results = (); - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - $sth->finish; - return(scalar(@results),@results); -} -=item bookfundbreakdown +#------------------------------------------------------------# - returns the total comtd & spent for a given bookfund - used in acqui-home.pl -=cut -#' - -sub bookfundbreakdown { - my ($id)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select quantity,datereceived,freight,unitprice,listprice,ecost,quantityreceived,subscription - from aqorders,aqorderbreakdown where bookfundid=? and - aqorders.ordernumber=aqorderbreakdown.ordernumber - and (datecancellationprinted is NULL or - datecancellationprinted='0000-00-00')"); - $sth->execute($id); - my $comtd=0; - my $spent=0; - while (my $data=$sth->fetchrow_hashref){ - if ($data->{'subscription'} == 1){ - $spent+=$data->{'quantity'}*$data->{'unitprice'}; - } else { - my $leftover=$data->{'quantity'}-$data->{'quantityreceived'}; - $comtd+=($data->{'ecost'})*$leftover; - $spent+=($data->{'unitprice'})*$data->{'quantityreceived'}; - } - } - $sth->finish; - return($spent,$comtd); -} +=head3 GetParcel +=over 4 +@results = &GetParcel($booksellerid, $code, $date); -=item curconvert +Looks up all of the received items from the supplier with the given +bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders. - $foreignprice = &curconvert($currency, $localprice); +C<@results> is an array of references-to-hash. The keys of each element are fields from +the aqorders, biblio, and biblioitems tables of the Koha database. -Converts the price C<$localprice> to foreign currency C<$currency> by -dividing by the exchange rate, and returns the result. +C<@results> is sorted alphabetically by book title. -If no exchange rate is found, C<&curconvert> assumes the rate is one -to one. +=back =cut -#' -sub curconvert { - my ($currency,$price)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select rate from currency where currency=?"); - $sth->execute($currency); - my $cur=($sth->fetchrow_array())[0]; - $sth->finish; - if ($cur==0){ - $cur=1; - } - return($price / $cur); + +sub GetParcel { + #gets all orders from a certain supplier, orders them alphabetically + my ( $supplierid, $code, $datereceived ) = @_; + my $dbh = C4::Context->dbh; + my @results = (); + $code .= '%' + if $code; # add % if we search on a given code (otherwise, let him empty) + my $strsth =" + SELECT authorisedby, + creationdate, + aqbasket.basketno, + closedate,surname, + firstname, + aqorders.biblionumber, + aqorders.title, + aqorders.ordernumber, + aqorders.quantity, + aqorders.quantityreceived, + aqorders.unitprice, + aqorders.listprice, + aqorders.rrp, + aqorders.ecost + FROM aqorders + LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno + LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber + WHERE + 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 = ? + or borrowers.branchcode = '')"; + push @query_params, $userenv->{branch}; + } + } + $strsth .= " ORDER BY aqbasket.basketno"; + ### parcelinformation : $strsth + my $sth = $dbh->prepare($strsth); + $sth->execute( @query_params ); + while ( my $data = $sth->fetchrow_hashref ) { + push( @results, $data ); + } + ### countparcelbiblio: scalar(@results) + $sth->finish; + + return @results; } -=item getcurrencies +#------------------------------------------------------------# - ($count, $currencies) = &getcurrencies(); +=head3 GetParcels -Returns the list of all known currencies. +=over 4 -C<$count> is the number of elements in C<$currencies>. C<$currencies> -is a reference-to-array; its elements are references-to-hash, whose -keys are the fields from the currency table in the Koha database. +$results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto); +get a lists of parcels. -=cut -#' -sub getcurrencies { - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select * from currency"); - $sth->execute; - my @results = (); - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - $sth->finish; - return(scalar(@results),\@results); -} +* Input arg : -=item updatecurrencies +=item $bookseller +is the bookseller this function has to get parcels. - &updatecurrencies($currency, $newrate); +=item $order +To know on what criteria the results list has to be ordered. -Sets the exchange rate for C<$currency> to be C<$newrate>. +=item $code +is the booksellerinvoicenumber. -=cut -#' -sub updatecurrencies { - my ($currency,$rate)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("update currency set rate=? where currency=?"); - $sth->execute($rate,$currency); - $sth->finish; -} +=item $datefrom & $dateto +to know on what date this function has to filter its search. -# -# -# OTHERS -# -# +* return: +a pointer on a hash list containing parcel informations as such : -=item bookseller +=item Creation date - ($count, @results) = &bookseller($searchstring); +=item Last operation -Looks up a book seller. C<$searchstring> may be either a book seller -ID, or a string to look for in the book seller's name. +=item Number of biblio -C<$count> is the number of elements in C<@results>. C<@results> is an -array of references-to-hash, whose keys are the fields of of the -aqbooksellers table in the Koha database. +=item Number of items + +=back =cut -#' -sub bookseller { - my ($searchstring)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select * from aqbooksellers where name like ? or id = ?"); - $sth->execute("$searchstring%",$searchstring); - my @results; - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - $sth->finish; - return(scalar(@results),@results); -} -=item breakdown +sub GetParcels { + my ($bookseller,$order, $code, $datefrom, $dateto) = @_; + my $dbh = C4::Context->dbh; + my @query_params = (); + my $strsth =" + SELECT aqorders.booksellerinvoicenumber, + datereceived,purchaseordernumber, + count(DISTINCT biblionumber) AS biblio, + sum(quantity) AS itemsexpected, + sum(quantityreceived) AS itemsreceived + FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno + WHERE aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL + "; + + 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; + } + + if ( defined $dateto ) { + $strsth .= 'and datereceived <= ? '; + push @query_params, $dateto; + } - ($count, $results) = &breakdown($ordernumber); + $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived "; -Looks up an order by order ID, and returns its breakdown. + # 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); -C<$count> is the number of elements in C<$results>. C<$results> is a -reference-to-array; its elements are references-to-hash, whose keys -are the fields of the aqorderbreakdown table in the Koha database. + my $sth = $dbh->prepare($strsth); -=cut -#' -sub breakdown { - my ($id)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select * from aqorderbreakdown where ordernumber=?"); - $sth->execute($id); - my @results = (); - while (my $data=$sth->fetchrow_hashref){ - push(@results,$data); - } - $sth->finish; - return(scalar(@results),\@results); + $sth->execute( @query_params ); + my $results = $sth->fetchall_arrayref({}); + $sth->finish; + return @$results; } -=item branches +#------------------------------------------------------------# - ($count, @results) = &branches(); +=head3 GetLateOrders -Returns a list of all library branches. +=over 4 -C<$count> is the number of elements in C<@results>. C<@results> is an -array of references-to-hash, whose keys are the fields of the branches -table of the Koha database. +@results = &GetLateOrders; -=cut -#' -sub branches { - my $dbh = C4::Context->dbh; - my $sth = $dbh->prepare("Select * from branches order by branchname"); - my @results = (); +Searches for bookseller with late orders. - $sth->execute(); - while (my $data = $sth->fetchrow_hashref) { - push(@results,$data); - } # while +return: +the table of supplier with late issues. This table is full of hashref. +=back + +=cut + +sub GetLateOrders { + my $delay = shift; + my $supplierid = shift; + my $branch = shift; + + 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"; + + my @query_params = (); + + # 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 ? DAY)) + AND ((datereceived = '' OR datereceived is null) + OR (aqorders.quantityreceived < aqorders.quantity) ) + "; + + push @query_params, $delay; + + if ( defined $supplierid ) { + $strsth .= ' AND aqbasket.booksellerid = ? '; + push @query_params, $supplierid; + } + + if ( defined $branch ) { + $strsth .= ' AND borrowers.branchcode like ? '; + push @query_params, $branch; + } + + if ( C4::Context->preference("IndependantBranches") + && C4::Context->userenv + && C4::Context->userenv->{flags} != 1 ) { + $strsth .= ' AND borrowers.branchcode like ? '; + push @query_params, C4::Context->userenv->{branch}; + } + + $strsth .= " HAVING quantity <> 0 + AND unitpricesupplier <> 0 + AND unitpricelib <> 0 + ORDER BY latesince, basketno, borrowers.branchcode, supplier "; + } 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"; + } + my $sth = $dbh->prepare($strsth); + $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} ); + push @results, $data; + $hilighted = -$hilighted; + } $sth->finish; - return(scalar(@results), @results); -} # sub branches + return @results; +} -=item updatesup +#------------------------------------------------------------# - &updatesup($bookseller); +=head3 GetHistory + +=over 4 -Updates the information for a given bookseller. C<$bookseller> is a -reference-to-hash whose keys are the fields of the aqbooksellers table -in the Koha database. It must contain entries for all of the fields. -The entry to modify is determined by C<$bookseller-E{id}>. +(\@order_loop, $total_qty, $total_price, $total_qtyreceived)=&GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on ) -The easiest way to get all of the necessary fields is to look up a -book seller with C<&booksellers>, modify what's necessary, then call -C<&updatesup> with the result. +this function get the search history. + +=back =cut -#' -sub updatesup { - my ($data)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Update aqbooksellers set - name=?,address1=?,address2=?,address3=?,address4=?,postal=?, - phone=?,fax=?,url=?,contact=?,contpos=?,contphone=?,contfax=?,contaltphone=?, - contemail=?,contnotes=?,active=?, - listprice=?, invoiceprice=?,gstreg=?, listincgst=?, - invoiceincgst=?, specialty=?,discount=?,invoicedisc=?, - nocalc=? - where id=?"); - $sth->execute($data->{'name'},$data->{'address1'},$data->{'address2'}, - $data->{'address3'},$data->{'address4'},$data->{'postal'},$data->{'phone'}, - $data->{'fax'},$data->{'url'},$data->{'contact'},$data->{'contpos'}, - $data->{'contphone'},$data->{'contfax'},$data->{'contaltphone'}, - $data->{'contemail'}, - $data->{'contnote'},$data->{'active'},$data->{'listprice'}, - $data->{'invoiceprice'},$data->{'gstreg'},$data->{'listincgst'}, - $data->{'invoiceincgst'},$data->{'specialty'},$data->{'discount'}, - $data->{'invoicedisc'},$data->{'nocalc'},$data->{'id'}); - $sth->finish; -} -=item insertsup +sub GetHistory { + my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_; + my @order_loop; + my $total_qty = 0; + my $total_qtyreceived = 0; + my $total_price = 0; + +# don't run the query if there are no parameters (list would be too long for sure !) + if ( $title || $author || $name || $from_placed_on || $to_placed_on ) { + my $dbh = C4::Context->dbh; + my $query =" + SELECT + biblio.title, + biblio.author, + aqorders.basketno, + name,aqbasket.creationdate, + aqorders.datereceived, + aqorders.quantity, + aqorders.quantityreceived, + aqorders.ecost, + aqorders.ordernumber, + aqorders.booksellerinvoicenumber as invoicenumber, + aqbooksellers.id as id, + aqorders.biblionumber + FROM aqorders + LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno + LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id + LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber"; + + $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 .= + " AND biblio.author LIKE " . $dbh->quote( "%" . $author . "%" ) + if $author; + + $query .= " AND name LIKE " . $dbh->quote( "%" . $name . "%" ) if $name; + + $query .= " AND creationdate >" . $dbh->quote($from_placed_on) + if $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 ( C4::Context->preference("IndependantBranches") ) { + my $userenv = C4::Context->userenv; + if ( ($userenv) && ( $userenv->{flags} != 1 ) ) { + $query .= + " AND (borrowers.branchcode = '" + . $userenv->{branch} + . "' OR borrowers.branchcode ='')"; + } + } + $query .= " ORDER BY booksellerid"; + my $sth = $dbh->prepare($query); + $sth->execute; + my $cnt = 1; + while ( my $line = $sth->fetchrow_hashref ) { + $line->{count} = $cnt++; + $line->{toggle} = 1 if $cnt % 2; + push @order_loop, $line; + $line->{creationdate} = format_date( $line->{creationdate} ); + $line->{datereceived} = format_date( $line->{datereceived} ); + $total_qty += $line->{'quantity'}; + $total_qtyreceived += $line->{'quantityreceived'}; + $total_price += $line->{'quantity'} * $line->{'ecost'}; + } + } + return \@order_loop, $total_qty, $total_price, $total_qtyreceived; +} - $id = &insertsup($bookseller); +=head2 GetRecentAcqui -Creates a new bookseller. C<$bookseller> is a reference-to-hash whose -keys are the fields of the aqbooksellers table in the Koha database. -All fields must be present. + $results = GetRecentAcqui($days); -Returns the ID of the newly-created bookseller. + C<$results> is a ref to a table which containts hashref =cut -#' -sub insertsup { - my ($data)=@_; - my $dbh = C4::Context->dbh; - my $sth=$dbh->prepare("Select max(id) from aqbooksellers"); - $sth->execute; - my $data2=$sth->fetchrow_hashref; - $sth->finish; - $data2->{'max(id)'}++; - $sth=$dbh->prepare("Insert into aqbooksellers (id) values (?)"); - $sth->execute($data2->{'max(id)'}); - $sth->finish; - $data->{'id'}=$data2->{'max(id)'}; - updatesup($data); - return($data->{'id'}); -} -END { } # module clean-up code here (global destructor) +sub GetRecentAcqui { + my $limit = shift; + my $dbh = C4::Context->dbh; + my $query = " + SELECT * + FROM biblio + ORDER BY timestamp DESC + LIMIT 0,".$limit; + + my $sth = $dbh->prepare($query); + $sth->execute; + my @results; + while(my $data = $sth->fetchrow_hashref){ + push @results,$data; + } + return \@results; +} 1; __END__