# 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::Date;
+use C4::Debug;
+use C4::Dates qw(format_date);
use MARC::Record;
use C4::Suggestions;
-# use C4::Biblio;
+use Time::localtime;
use vars qw($VERSION @ISA @EXPORT);
-# set the version for version checking
-$VERSION = do { my @v = '$Revision$' =~ /\d+/g;shift(@v) . "." . join("_", map {sprintf "%03d", $_ } @v); };
+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
=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
-
- &getorders &getallorders &getrecorders
- &getorder &neworder &delorder
- &ordersearch &histsearch
- &modorder &getsingleorder &invoice &receiveorder
- &updaterecorder &newordernum
- &getsupplierlistwithlateorders
- &getlateorders
- &getparcels &getparcelinformation
- &bookfunds &curconvert &getcurrencies &bookfundbreakdown
- &updatecurrencies &getcurrency
- &updatesup &insertsup
- &bookseller &breakdown
-);
+=head3 GetBasket
-#
-#
-#
-# BASKETS
-#
-#
-#
-=item getbasket
+=over 4
- $aqbasket = &getbasket($basketnumber);
+$aqbasket = &GetBasket($basketnumber);
get all basket informations in aqbasket for a given basket
+
+return :
+informations for a given basket returned as a hashref.
+
+=back
+
=cut
-sub getbasket {
- my ($basketno)=@_;
- my $dbh=C4::Context->dbh;
- my $sth=$dbh->prepare("select aqbasket.*,borrowers.firstname+' '+borrowers.surname as authorisedbyname, borrowers.branchcode as branch 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 aqorderbreakdown.*,biblio.*,biblioitems.*,aqorders.*,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
+
+=over 4
+
+&CloseBasket($basketno);
+
+close a basket (becomes unmodifiable,except for recieves)
+
+=back
-Create a new basket in aqbasket table
=cut
-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);
+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 closebasket
+#------------------------------------------------------------#
- &newbasket($basketno);
+=head2 FUNCTIONS ABOUT ORDERS
-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);
-}
+#------------------------------------------------------------#
-=item neworder
+=head3 GetPendingOrders
- &neworder($basket, $biblionumber, $title, $quantity, $listprice,
- $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp,
- $ecost, $gst, $budget, $unitprice, $subscription,
- $booksellerinvoicenumber);
+=over 4
-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.
+$orders = &GetPendingOrders($booksellerid, $grouped);
-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.
+Finds pending orders from the bookseller with the given ID. Ignores
+completed and cancelled orders.
-C<$budget> is effectively ignored.
+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
-C<$subscription> may be either "yes", or anything else for "no".
+=over 2
-=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;
-}
+=item C<authorizedby>
-=item delorder
+=item C<entrydate>
- &delorder($biblionumber, $ordernumber);
+=item C<basketno>
-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.
+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 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;
+
+sub GetPendingOrders {
+ my ($supplierid,$grouped, $closed) = @_;
+ 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 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 ";
+ }
+ ## 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 modorder
+#------------------------------------------------------------#
- &modorder($title, $ordernumber, $quantity, $listprice,
- $biblionumber, $basketno, $supplier, $who, $notes,
- $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget,
- $unitprice, $booksellerinvoicenumber);
+=head3 GetOrders
-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.
+=over 4
-Entries with order number C<$ordernumber> in the aqorderbreakdown
-table are also updated to the new book fund ID.
+@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
-#'
-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=?");
- unless ($sth->execute($bookfund,$ordnum)) { # zero rows affected [Bug 734]
- my $query="insert into aqorderbreakdown (ordernumber,bookfundid) values (?,?)";
- $sth=$dbh->prepare($query);
- $sth->execute($ordnum,$bookfund);
- }
- $sth->finish;
+
+sub GetOrders {
+ my ( $basketno, $orderby ) = @_;
+ my $dbh = C4::Context->dbh;
+ my $query ="
+ SELECT aqorderbreakdown.*,
+ biblio.*,biblioitems.*,
+ 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 newordernum
+#------------------------------------------------------------#
- $order = &newordernum();
+=head3 GetOrderNumber
-Finds the next unused order number in the aqorders table of the Koha
-database, and returns it.
+=over 4
-=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);
-}
+$ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
-=item receiveorder
+=back
- &receiveorder($biblionumber, $ordernumber, $quantityreceived, $user,
- $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
- $freight, $bookfund, $rrp);
+Looks up the ordernumber with the given biblionumber and biblioitemnumber.
-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.
+Returns the number of this order.
-Updates the order with bibilionumber C<$biblionumber> and ordernumber
-C<$ordernumber>.
+=over 4
-Also updates the book fund ID in the aqorderbreakdown table.
+=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=?");
- my $suggestionid = findsuggestion_from_biblionumber($dbh,$biblio);
- if ($suggestionid) {
- changestatus($suggestionid,'AVAILABLE','',$biblio);
- }
- $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
+#------------------------------------------------------------#
- &updaterecorder($biblionumber, $ordernumber, $user, $unitprice,
- $bookfundid, $rrp);
+=head3 GetOrder
-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.
+=over 4
-C<$user> is ignored.
+$order = &GetOrder($ordernumber);
-=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;
-}
+Looks up an order by order number.
-#
-#
-# ORDERS
-#
-#
+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.
-=item getorders
+=back
- ($count, $orders) = &getorders($booksellerid);
+=cut
-Finds pending orders from the bookseller with the given ID. Ignores
-completed and cancelled orders.
+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;
+}
-C<$count> is the number of elements in C<@{$orders}>.
+#------------------------------------------------------------#
-C<$orders> is a reference-to-array; each element is a
-reference-to-hash with the following fields:
+=head3 NewOrder
=over 4
-=item C<count(*)>
-
-Gives the number of orders in with this basket number.
+ &NewOrder($basket, $biblionumber, $title, $quantity, $listprice,
+ $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp,
+ $ecost, $gst, $budget, $unitprice, $subscription,
+ $booksellerinvoicenumber, $purchaseorder, $branchcode);
-=item C<authorizedby>
+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.
-=item C<entrydate>
+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.
-=item C<basketno>
+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.
-These give the value of the corresponding field in the aqorders table
-of the Koha database.
+C<$subscription> may be either "yes", or anything else for "no".
=back
-Results are ordered from most to least recent.
-
=cut
-#'
-sub getorders {
- my ($supplierid)=@_;
- my $dbh = C4::Context->dbh;
- my $strsth ="Select count(*),authorisedby,creationdate,aqbasket.basketno,
-closedate,surname,firstname,aqorders.title
-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)";
- 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.=" group by basketno order by aqbasket.basketno";
- my $sth=$dbh->prepare($strsth);
- $sth->execute($supplierid);
- my @results = ();
- while (my $data=$sth->fetchrow_hashref){
- push(@results,$data);
- }
- $sth->finish;
- return (scalar(@results),\@results);
-}
-=item getorder
+sub NewOrder {
+ my (
+ $basketno, $bibnum, $title, $quantity,
+ $listprice, $booksellerid, $authorisedby, $notes,
+ $bookfund, $bibitemnum, $rrp, $ecost,
+ $gst, $budget, $cost, $sub,
+ $invoice, $sort1, $sort2, $purchaseorder,
+ $branchcode
+ )
+ = @_;
+
+ my $year = localtime->year() + 1900;
+ my $month = localtime->mon() + 1; # months starts at 0, add 1
+
+ if ( !$budget || $budget eq 'now' ) {
+ $budget = undef;
+ }
- ($order, $ordernumber) = &getorder($biblioitemnumber, $biblionumber);
+ # 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 {
-Looks up the order with the given biblionumber and biblioitemnumber.
+ # START OF NEW BUDGET, 1ST OF JULY, THIS YEAR
+ $budget = "$year-07-01";
+ }
-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.
+ if ( $sub eq 'yes' ) {
+ $sub = 1;
+ }
+ else {
+ $sub = 0;
+ }
-=cut
+ # if $basket empty, it's also a new basket, create it
+ unless ($basketno) {
+ $basketno = NewBasket( $booksellerid, $authorisedby );
+ }
-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'});
+ 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;
+
+ #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
+ my $ordnum = $dbh->{'mysql_insertid'};
+ $query = "
+ INSERT INTO aqorderbreakdown (ordernumber,bookfundid, branchcode)
+ VALUES (?,?,?)
+ ";
+ $sth = $dbh->prepare($query);
+ $sth->execute( $ordnum, $bookfund, $branchcode );
+ $sth->finish;
+ return ( $basketno, $ordnum );
}
-=item getsingleorder
+#------------------------------------------------------------#
- $order = &getsingleorder($ordernumber);
+=head3 ModOrder
-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.
+&ModOrder($title, $ordernumber, $quantity, $listprice,
+ $biblionumber, $basketno, $supplier, $who, $notes,
+ $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget,
+ $unitprice, $booksellerinvoicenumber, $branchcode);
+
+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 or branchcode.
+
+=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 ModOrder {
+ my (
+ $title, $ordnum, $quantity, $listprice, $bibnum,
+ $basketno, $supplier, $who, $notes, $bookfund,
+ $bibitemnum, $rrp, $ecost, $gst, $budget,
+ $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
+ 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;
+ $query = "
+ UPDATE aqorderbreakdown
+ SET bookfundid=?,branchcode=?
+ WHERE ordernumber=?
+ ";
+ $sth = $dbh->prepare($query);
+
+ my $rv = $sth->execute( $bookfund,$branchcode, $ordnum );
+ unless($rv && ( $rv ne '0E0' )) { # 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 getallorders
+#------------------------------------------------------------#
- ($count, @results) = &getallorders($booksellerid);
+=head3 ModOrderBiblioNumber
-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.
+&ModOrderBiblioNumber($biblioitemnumber,$ordnum, $biblionumber);
-C<@results> is sorted alphabetically by book title.
+Modifies the biblioitemnumber for an existing order.
+Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
+
+=back
=cut
-#'
-sub getallorders {
- #gets all orders from a certain supplier, orders them alphabetically
- my ($supplierid)=@_;
- my $dbh = C4::Context->dbh;
- my @results = ();
- my $strsth ="Select count(*),authorisedby,creationdate,aqbasket.basketno,
-closedate,surname,firstname,aqorders.biblionumber,aqorders.title, aqorders.ordernumber
-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 ";
-
- 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.=" group by basketno order by aqbasket.basketno";
- my $sth=$dbh->prepare($strsth);
- $sth->execute($supplierid);
- while (my $data=$sth->fetchrow_hashref){
- push(@results,$data);
- }
- $sth->finish;
- return(scalar(@results),@results);
+
+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 getparcelinformation
- ($count, @results) = &getparcelinformation($booksellerid, $code, $date);
+#------------------------------------------------------------#
-Looks up all of the received items from the supplier with the given
-bookseller ID at the given date, for the given code. Ignores cancelled and completed orders.
+=head3 ModReceiveOrder
-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.
+=over 4
-C<@results> is sorted alphabetically by book title.
+&ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
+ $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
+ $freight, $bookfund, $rrp);
-=cut
-#'
-sub getparcelinformation {
- #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,aqbasket left join borrowers on aqbasket.authorisedby=borrowers.borrowernumber where aqbasket.basketno=aqorders.basketno and aqbasket.booksellerid=? and aqorders.booksellerinvoicenumber like \"$code\" and aqorders.datereceived= \'$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.=" order by aqbasket.basketno";
- ### parcelinformation : $strsth
- my $sth=$dbh->prepare($strsth);
- $sth->execute($supplierid);
- while (my $data=$sth->fetchrow_hashref){
- push(@results,$data);
- }
- my $count =scalar(@results);
- ### countparcelbiblio: $count
- $sth->finish;
-
- return(scalar(@results),@results);
-}
-=item getsupplierlistwithlateorders
+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.
- %results = &getsupplierlistwithlateorders;
+If a partial order is received, splits the order into two. The received
+portion must have a booksellerinvoicenumber.
-Searches for suppliers with late orders.
+Updates the order with bibilionumber C<$biblionumber> and ordernumber
+C<$ordernumber>.
-=cut
-#'
-sub getsupplierlistwithlateorders {
- my $delay=shift;
- my $dbh = C4::Context->dbh;
-#FIXME NOT quite sure that this operation is valid for DBMs different from Mysql, HOPING so
-#should be tested with other DBMs
-
- my $strsth;
- my $dbdriver = C4::Context->config("db_scheme")||"mysql";
- if ($dbdriver eq "mysql"){
- $strsth="SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
- FROM aqorders, aqbasket
- 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))
- ";
- }else {
- $strsth="SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
- FROM aqorders, aqbasket
- LEFT JOIN aqbooksellers ON aqbasket.aqbooksellerid = aqbooksellers.id
- WHERE aqorders.basketno = aqbasket.basketno AND
- (closedate < (CURDATE( )-(INTERVAL $delay DAY))) AND (datereceived = '' or datereceived is null))
- ";
- }
-# warn "C4::Acquisition getsupplierlistwithlateorders : ".$strsth;
- my $sth = $dbh->prepare($strsth);
- $sth->execute;
- my %supplierlist;
- while (my ($id,$name) = $sth->fetchrow) {
- $supplierlist{$id} = $name;
- }
- return %supplierlist;
-}
+Also updates the book fund ID in the aqorderbreakdown table.
-=item getlateorders
+=back
- %results = &getlateorders;
+=cut
-Searches for suppliers with late orders.
-=cut
-#'
-sub getlateorders {
- my $delay=shift;
- my $supplierid = shift;
- my $branch = shift;
+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();
- 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,
- 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 ";
- } 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";
- }
- warn "C4::Acquisition : getlateorders SQL:".$strsth;
- my $sth = $dbh->prepare($strsth);
- $sth->execute;
- 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);
-}
-
-# 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);
+ 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.
If C<$complete> is C<yes>, 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
=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.*,biblioitems.*,aqorders.*,aqbasket.*,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 histsearch {
- 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 from aqorders,aqbasket,aqbooksellers,biblio";
- $query .= ",borrowers " if (C4::Context->preference("IndependantBranches"));
- $query .=" where aqorders.basketno=aqbasket.basketno and aqbasket.booksellerid=aqbooksellers.id and biblio.biblionumber=aqorders.biblionumber ";
- $query .= " and aqbasket.authorisedby=borrowers.borrowernumber" if (C4::Context->preference("IndependantBranches"));
- $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;
- 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";
- warn "query histearch: ".$query;
- 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;
+sub SearchOrder {
+ my ( $search, $id, $biblionumber ) = @_;
+ 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(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
+ 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=?)) ";
+ }
+ 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);
-=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);
-}
+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.
-=item bookfunds
+=back
- ($count, @results) = &bookfunds();
+=cut
-Returns a list of all book funds.
+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;
+}
-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.
+=head2 FUNCTIONS ABOUT PARCELS
=cut
-#'
-sub bookfunds {
- my ($branch)=@_;
- my $dbh = C4::Context->dbh;
- my $userenv = C4::Context->userenv;
- my $branch = $userenv->{branch};
- my $strsth;
-
- if (!($branch eq '')) {
- $strsth="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
- =aqbudget.bookfundid and startdate<now() and enddate>now() and (aqbookfund.branchcode is null or aqbookfund.branchcode='' or aqbookfund.branchcode= ? )
- group by aqbookfund.bookfundid order by bookfundname";
- } else {
- $strsth="Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
- =aqbudget.bookfundid and startdate<now() and enddate>now()
- group by aqbookfund.bookfundid order by bookfundname";
- }
- my $sth=$dbh->prepare($strsth);
- if (!($branch eq '')){
- $sth->execute($branch);
- } else {
- $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, and a given year
- used in acqui-home.pl
-=cut
-#'
-
-sub bookfundbreakdown {
- my ($id, $year)=@_;
- my $dbh = C4::Context->dbh;
- my $sth=$dbh->prepare("SELECT startdate, enddate, quantity, datereceived, freight, unitprice, listprice, ecost, quantityreceived, subscription
-FROM aqorders, aqorderbreakdown, aqbudget, aqbasket
-WHERE aqorderbreakdown.bookfundid = ?
-AND aqorders.ordernumber = aqorderbreakdown.ordernumber
-AND (
-datecancellationprinted IS NULL
-OR datecancellationprinted = '0000-00-00'
-)
-AND aqbudget.bookfundid = aqorderbreakdown.bookfundid
-AND aqbasket.basketno = aqorders.basketno
-AND aqbasket.creationdate >= startdate
-AND enddate >= aqbasket.creationdate
-and startdate<=now() and enddate>=now()");
- $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);
-}
-=item getcurrencies
+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;
- ($count, $currencies) = &getcurrencies();
+ return @results;
+}
-Returns the list of all known currencies.
+#------------------------------------------------------------#
-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.
+=head3 GetParcels
-=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);
-}
+=over 4
-=item updatecurrencies
+$results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
+get a lists of parcels.
- &updatecurrencies($currency, $newrate);
+=back
-Sets the exchange rate for C<$currency> to be C<$newrate>.
+* Input arg :
-=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;
-}
+=over 4
-#
-#
-# OTHERS
-#
-#
+=item $bookseller
+is the bookseller this function has to get parcels.
-=item bookseller
+=item $order
+To know on what criteria the results list has to be ordered.
- ($count, @results) = &bookseller($searchstring);
+=item $code
+is the booksellerinvoicenumber.
-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 $datefrom & $dateto
+to know on what date this function has to filter its search.
-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.
+* return:
+a pointer on a hash list containing parcel informations as such :
-=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 Creation date
-=item breakdown
+=item Last operation
- ($count, $results) = &breakdown($ordernumber);
+=item Number of biblio
-Looks up an order by order ID, and returns its breakdown.
+=item Number of items
-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.
+=back
=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);
-}
+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;
+ }
-=item branches
+ if ( defined $dateto ) {
+ $strsth .= 'and datereceived <= ? ';
+ push @query_params, $dateto;
+ }
- ($count, @results) = &branches();
+ $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
-Returns a list of all library branches.
+ # 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 an
-array of references-to-hash, whose keys are the fields of the branches
-table of the Koha database.
+ my $sth = $dbh->prepare($strsth);
-=cut
-#'
-sub branches {
- my $dbh = C4::Context->dbh;
- my $sth;
- if (C4::Context->preference("IndependantBranches") && (C4::Context->userenv) && (C4::Context->userenv->{flags} != 1)){
- my $strsth ="Select * from branches ";
- $strsth.= " WHERE branchcode = ".$dbh->quote(C4::Context->userenv->{branch});
- $strsth.= " order by branchname";
- warn "C4::Acquisition->branches : ".$strsth;
- $sth=$dbh->prepare($strsth);
- } else {
- $sth = $dbh->prepare("Select * from branches order by branchname");
- }
- my @results = ();
+ $sth->execute( @query_params );
+ my $results = $sth->fetchall_arrayref({});
+ $sth->finish;
+ return @$results;
+}
- $sth->execute();
- while (my $data = $sth->fetchrow_hashref) {
- push(@results,$data);
- } # while
+#------------------------------------------------------------#
- $sth->finish;
- return(scalar(@results), @results);
-} # sub branches
+=head3 GetLateOrders
-=item updatesup
+=over 4
+
+@results = &GetLateOrders;
- &updatesup($bookseller);
+Searches for bookseller with late orders.
-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<gt>{id}>.
+return:
+the table of supplier with late issues. This table is full of hashref.
-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.
+=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;
+
+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 $dbdriver = C4::Context->config("db_scheme") || "mysql";
+
+ 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)) ";
+ }
+ if (defined $supplierid) {
+ $from .= ' AND aqbasket.booksellerid = ? ';
+ push @query_params, $supplierid;
+ }
+ 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;
+ while (my $data = $sth->fetchrow_hashref) {
+ $data->{orderdate} = format_date($data->{orderdate});
+ push @results, $data;
+ }
+ return @results;
}
-=item insertsup
+#------------------------------------------------------------#
- $id = &insertsup($bookseller);
+=head3 GetHistory
+
+=over 4
-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.
+(\@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
-Returns the ID of the newly-created bookseller.
+=back
=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'});
+
+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 (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
+
+ my @query_params = ();
+
+ if ( defined $title ) {
+ $query .= " AND biblio.title LIKE ? ";
+ push @query_params, "%$title%";
+ }
+
+ if ( defined $author ) {
+ $query .= " AND biblio.author LIKE ? ";
+ push @query_params, "%$author%";
+ }
+
+ if ( defined $name ) {
+ $query .= " AND name LIKE ? ";
+ push @query_params, "%$name%";
+ }
+
+ if ( defined $from_placed_on ) {
+ $query .= " AND creationdate >= ? ";
+ push @query_params, $from_placed_on;
+ }
+
+ 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 = ? OR borrowers.branchcode ='' ) ";
+ push @query_params, $userenv->{branch};
+ }
+ }
+ $query .= " ORDER BY booksellerid";
+ my $sth = $dbh->prepare($query);
+ $sth->execute( @query_params );
+ 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;
}
-=item getparcels
+=head2 GetRecentAcqui
- ($count, $results) = &getparcels($dbh, $bookseller, $order, $limit);
+ $results = GetRecentAcqui($days);
-get a lists of parcels
-Returns the count of parcels returned and a pointer on a hash list containing parcel informations as such :
- Creation date
- Last operation
- Number of biblio
- Number of items
-
+ C<$results> is a ref to a table which containts hashref
=cut
-#'
-sub getparcels {
- my ($bookseller, $order, $code,$datefrom,$dateto, $limit)=@_;
- my $dbh = C4::Context->dbh;
- my $strsth = "SELECT aqorders.booksellerinvoicenumber, datereceived, count(DISTINCT biblionumber) as biblio, sum(quantity) as itemsexpected, sum(quantityreceived) as itemsreceived from aqorders, aqbasket where aqbasket.basketno = aqorders.basketno and aqbasket.booksellerid = $bookseller and datereceived is not null ";
- $strsth .= "and aqorders.booksellerinvoicenumber like \"$code%\" " if ($code);
- $strsth .= "and datereceived >=".$dbh->quote($datefrom)." " if ($datefrom);
- $strsth .= "and datereceived <=".$dbh->quote($dateto)." " if ($dateto);
- $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
- $strsth .= "order by $order " if ($order);
- $strsth .= " LIMIT 0,$limit" if ($limit);
- my $sth=$dbh->prepare($strsth);
-### getparcels: $strsth
- $sth->execute;
- my @results;
- while (my $data2=$sth->fetchrow_hashref) {
- push @results, $data2;
- }
-
- $sth->finish;
- return(scalar(@results), @results);
-}
-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__
-=back
-
=head1 AUTHOR
Koha Developement team <info@koha.org>