1 package C4::Acquisition;
3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along with
17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
18 # Suite 330, Boston, MA 02111-1307 USA
29 use vars qw($VERSION @ISA @EXPORT);
31 # set the version for version checking
32 $VERSION = do { my @v = '$Revision$' =~ /\d+/g; shift(@v) . "." . join( "_", map { sprintf "%03d", $_ } @v ); };
34 # used in reciveorder subroutine
35 # to provide library specific handling
36 my $library_name = C4::Context->preference("LibraryName");
40 C4::Acquisition - Koha functions for dealing with orders and acquisitions
48 The functions in this module deal with acquisitions, managing book
49 orders, converting money to different currencies, and so forth.
59 &getbasket &getbasketcontent &newbasket &closebasket
61 &getorders &getallorders &getrecorders
62 &getorder &neworder &delorder
63 &ordersearch &histsearch
64 &modorder &getsingleorder &invoice &receiveorder
65 &updaterecorder &newordernum
66 &getsupplierlistwithlateorders
68 &getparcels &getparcelinformation
69 &bookfunds &curconvert &getcurrencies &bookfundbreakdown
70 &updatecurrencies &getcurrency
72 &bookseller &breakdown
85 $aqbasket = &getbasket($basketnumber);
87 get all basket informations in aqbasket for a given basket
92 my $dbh = C4::Context->dbh;
95 "select aqbasket.*,borrowers.firstname+' '+borrowers.surname as authorisedbyname, borrowers.branchcode as branch from aqbasket left join borrowers on aqbasket.authorisedby=borrowers.borrowernumber where basketno=?"
97 $sth->execute($basketno);
98 return ( $sth->fetchrow_hashref );
102 =item getbasketcontent
104 ($count, @orders) = &getbasketcontent($basketnumber, $booksellerID);
106 Looks up the pending (non-cancelled) orders with the given basket
107 number. If C<$booksellerID> is non-empty, only orders from that seller
110 C<&basket> returns a two-element array. C<@orders> is an array of
111 references-to-hash, whose keys are the fields from the aqorders,
112 biblio, and biblioitems tables in the Koha database. C<$count> is the
113 number of elements in C<@orders>.
118 sub getbasketcontent {
119 my ( $basketno, $supplier, $orderby ) = @_;
120 my $dbh = C4::Context->dbh;
122 "SELECT aqorderbreakdown.*,biblio.*,biblioitems.*,aqorders.*,biblio.title FROM aqorders,biblio,biblioitems
123 LEFT JOIN aqorderbreakdown ON aqorderbreakdown.ordernumber=aqorders.ordernumber
125 AND biblio.biblionumber=aqorders.biblionumber AND biblioitems.biblioitemnumber
126 =aqorders.biblioitemnumber
127 AND (datecancellationprinted IS NULL OR datecancellationprinted =
129 if ( $supplier ne '' ) {
130 $query .= " AND aqorders.booksellerid=?";
133 $orderby = "biblioitems.publishercode" unless $orderby;
134 $query .= " ORDER BY $orderby";
135 my $sth = $dbh->prepare($query);
136 if ( $supplier ne '' ) {
137 $sth->execute( $basketno, $supplier );
140 $sth->execute($basketno);
146 while ( my $data = $sth->fetchrow_hashref ) {
147 $results[$i] = $data;
151 return ( $i, @results );
156 $basket = &newbasket();
158 Create a new basket in aqbasket table
162 my ( $booksellerid, $authorisedby ) = @_;
163 my $dbh = C4::Context->dbh;
166 "insert into aqbasket (creationdate,booksellerid,authorisedby) values(now(),'$booksellerid','$authorisedby')"
169 #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
170 my $basket = $dbh->{'mysql_insertid'};
176 &newbasket($basketno);
178 close a basket (becomes unmodifiable,except for recieves
183 my $dbh = C4::Context->dbh;
185 $dbh->prepare("update aqbasket set closedate=now() where basketno=?");
186 $sth->execute($basketno);
191 &neworder($basket, $biblionumber, $title, $quantity, $listprice,
192 $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp,
193 $ecost, $gst, $budget, $unitprice, $subscription,
194 $booksellerinvoicenumber);
196 Adds a new order to the database. Any argument that isn't described
197 below is the new value of the field with the same name in the aqorders
198 table of the Koha database.
200 C<$ordnum> is a "minimum order number." After adding the new entry to
201 the aqorders table, C<&neworder> finds the first entry in aqorders
202 with order number greater than or equal to C<$ordnum>, and adds an
203 entry to the aqorderbreakdown table, with the order number just found,
204 and the book fund ID of the newly-added order.
206 C<$budget> is effectively ignored.
208 C<$subscription> may be either "yes", or anything else for "no".
215 $basketno, $bibnum, $title, $quantity,
216 $listprice, $booksellerid, $authorisedby, $notes,
217 $bookfund, $bibitemnum, $rrp, $ecost,
218 $gst, $budget, $cost, $sub,
219 $invoice, $sort1, $sort2
223 if ( !$budget || $budget eq 'now' ) {
224 $sth = $dbh->prepare(
225 "INSERT INTO aqorders
226 (biblionumber,title,basketno,quantity,listprice,notes,
227 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,sort1,sort2,budgetdate,entrydate)
228 VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,now(),now() )"
231 $bibnum, $title, $basketno, $quantity, $listprice,
232 $notes, $bibitemnum, $rrp, $ecost, $gst,
233 $cost, $sub, $sort1, $sort2
238 ##FIXME HARDCODED DATE.
239 $budget = "'2006-07-01'";
240 $sth = $dbh->prepare(
241 "INSERT INTO aqorders
242 (biblionumber,title,basketno,quantity,listprice,notes,
243 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,sort1,sort2,budgetdate,entrydate)
244 VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,now() )"
247 $bibnum, $title, $basketno, $quantity, $listprice,
248 $notes, $bibitemnum, $rrp, $ecost, $gst,
249 $cost, $sub, $sort1, $sort2, $budget
255 #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
256 my $ordnum = $dbh->{'mysql_insertid'};
257 $sth = $dbh->prepare(
258 "INSERT INTO aqorderbreakdown (ordernumber,bookfundid) VALUES
261 $sth->execute( $ordnum, $bookfund );
268 &delorder($biblionumber, $ordernumber);
270 Cancel the order with the given order and biblio numbers. It does not
271 delete any entries in the aqorders table, it merely marks them as
278 my ( $bibnum, $ordnum ) = @_;
279 my $dbh = C4::Context->dbh;
280 my $sth = $dbh->prepare(
281 "update aqorders set datecancellationprinted=now()
282 where biblionumber=? and ordernumber=?"
284 $sth->execute( $bibnum, $ordnum );
290 &modorder($title, $ordernumber, $quantity, $listprice,
291 $biblionumber, $basketno, $supplier, $who, $notes,
292 $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget,
293 $unitprice, $booksellerinvoicenumber);
295 Modifies an existing order. Updates the order with order number
296 C<$ordernumber> and biblionumber C<$biblionumber>. All other arguments
297 update the fields with the same name in the aqorders table of the Koha
300 Entries with order number C<$ordernumber> in the aqorderbreakdown
301 table are also updated to the new book fund ID.
308 $title, $ordnum, $quantity, $listprice, $bibnum,
309 $basketno, $supplier, $who, $notes, $bookfund,
310 $bibitemnum, $rrp, $ecost, $gst, $budget,
311 $cost, $invoice, $sort1, $sort2
314 my $dbh = C4::Context->dbh;
315 my $sth = $dbh->prepare(
316 "update aqorders set title=?,
317 quantity=?,listprice=?,basketno=?,
318 rrp=?,ecost=?,unitprice=?,booksellerinvoicenumber=?,
319 notes=?,sort1=?, sort2=?
321 ordernumber=? and biblionumber=?"
324 $title, $quantity, $listprice, $basketno, $rrp,
325 $ecost, $cost, $invoice, $notes, $sort1,
326 $sort2, $ordnum, $bibnum
329 $sth = $dbh->prepare(
330 "update aqorderbreakdown set bookfundid=? where
334 unless ( $sth->execute( $bookfund, $ordnum ) )
335 { # zero rows affected [Bug 734]
337 "insert into aqorderbreakdown (ordernumber,bookfundid) values (?,?)";
338 $sth = $dbh->prepare($query);
339 $sth->execute( $ordnum, $bookfund );
346 $order = &newordernum();
348 Finds the next unused order number in the aqorders table of the Koha
349 database, and returns it.
354 # FIXME - Race condition
356 my $dbh = C4::Context->dbh;
357 my $sth = $dbh->prepare("Select max(ordernumber) from aqorders");
359 my $data = $sth->fetchrow_arrayref;
360 my $ordnum = $$data[0];
368 &receiveorder($biblionumber, $ordernumber, $quantityreceived, $user,
369 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
370 $freight, $bookfund, $rrp);
372 Updates an order, to reflect the fact that it was received, at least
373 in part. All arguments not mentioned below update the fields with the
374 same name in the aqorders table of the Koha database.
376 Updates the order with bibilionumber C<$biblionumber> and ordernumber
379 Also updates the book fund ID in the aqorderbreakdown table.
385 my ( $biblio, $ordnum, $quantrec, $user, $cost, $invoiceno, $freight, $rrp )
387 my $dbh = C4::Context->dbh;
388 my $sth = $dbh->prepare(
389 "update aqorders set quantityreceived=?,datereceived=now(),booksellerinvoicenumber=?,
390 unitprice=?,freight=?,rrp=?
391 where biblionumber=? and ordernumber=?"
393 my $suggestionid = findsuggestion_from_biblionumber( $dbh, $biblio );
395 changestatus( $suggestionid, 'AVAILABLE', '', $biblio );
397 $sth->execute( $quantrec, $invoiceno, $cost, $freight, $rrp, $biblio,
404 &updaterecorder($biblionumber, $ordernumber, $user, $unitprice,
407 Updates the order with biblionumber C<$biblionumber> and order number
408 C<$ordernumber>. C<$bookfundid> is the new value for the book fund ID
409 in the aqorderbreakdown table of the Koha database. All other
410 arguments update the fields with the same name in the aqorders table.
418 my ( $biblio, $ordnum, $user, $cost, $bookfund, $rrp ) = @_;
419 my $dbh = C4::Context->dbh;
420 my $sth = $dbh->prepare(
423 where biblionumber=? and ordernumber=?
426 $sth->execute( $cost, $rrp, $biblio, $ordnum );
430 "update aqorderbreakdown set bookfundid=? where ordernumber=?");
431 $sth->execute( $bookfund, $ordnum );
443 ($count, $orders) = &getorders($booksellerid);
445 Finds pending orders from the bookseller with the given ID. Ignores
446 completed and cancelled orders.
448 C<$count> is the number of elements in C<@{$orders}>.
450 C<$orders> is a reference-to-array; each element is a
451 reference-to-hash with the following fields:
457 Gives the number of orders in with this basket number.
459 =item C<authorizedby>
465 These give the value of the corresponding field in the aqorders table
466 of the Koha database.
470 Results are ordered from most to least recent.
476 my ($supplierid) = @_;
477 my $dbh = C4::Context->dbh;
478 my $strsth = "Select count(*),authorisedby,creationdate,aqbasket.basketno,
479 closedate,surname,firstname,aqorders.title
481 left join aqbasket on aqbasket.basketno=aqorders.basketno
482 left join borrowers on aqbasket.authorisedby=borrowers.borrowernumber
483 where booksellerid=? and (quantity > quantityreceived or
484 quantityreceived is NULL) and datecancellationprinted is NULL and (to_days(now())-to_days(closedate) < 180 or closedate is null)";
485 if ( C4::Context->preference("IndependantBranches") ) {
486 my $userenv = C4::Context->userenv;
487 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
489 " and (borrowers.branchcode = '"
491 . "' or borrowers.branchcode ='')";
494 $strsth .= " group by basketno order by aqbasket.basketno";
495 my $sth = $dbh->prepare($strsth);
496 $sth->execute($supplierid);
498 while ( my $data = $sth->fetchrow_hashref ) {
499 push( @results, $data );
502 return ( scalar(@results), \@results );
507 ($order, $ordernumber) = &getorder($biblioitemnumber, $biblionumber);
509 Looks up the order with the given biblionumber and biblioitemnumber.
511 Returns a two-element array. C<$ordernumber> is the order number.
512 C<$order> is a reference-to-hash describing the order; its keys are
513 fields from the biblio, biblioitems, aqorders, and aqorderbreakdown
514 tables of the Koha database.
519 my ( $bi, $bib ) = @_;
520 my $dbh = C4::Context->dbh;
523 "Select ordernumber from aqorders where biblionumber=? and biblioitemnumber=?"
525 $sth->execute( $bib, $bi );
527 # FIXME - Use fetchrow_array(), since we're only interested in the one
529 my $ordnum = $sth->fetchrow_hashref;
531 my $order = getsingleorder( $ordnum->{'ordernumber'} );
532 return ( $order, $ordnum->{'ordernumber'} );
537 $order = &getsingleorder($ordernumber);
539 Looks up an order by order number.
541 Returns a reference-to-hash describing the order. The keys of
542 C<$order> are fields from the biblio, biblioitems, aqorders, and
543 aqorderbreakdown tables of the Koha database.
549 my $dbh = C4::Context->dbh;
550 my $sth = $dbh->prepare(
551 "Select * from biblio,biblioitems,aqorders left join aqorderbreakdown
552 on aqorders.ordernumber=aqorderbreakdown.ordernumber
553 where aqorders.ordernumber=?
554 and biblio.biblionumber=aqorders.biblionumber and
555 biblioitems.biblioitemnumber=aqorders.biblioitemnumber"
557 $sth->execute($ordnum);
558 my $data = $sth->fetchrow_hashref;
565 ($count, @results) = &getallorders($booksellerid);
567 Looks up all of the pending orders from the supplier with the given
568 bookseller ID. Ignores cancelled and completed orders.
570 C<$count> is the number of elements in C<@results>. C<@results> is an
571 array of references-to-hash. The keys of each element are fields from
572 the aqorders, biblio, and biblioitems tables of the Koha database.
574 C<@results> is sorted alphabetically by book title.
581 #gets all orders from a certain supplier, orders them alphabetically
582 my ($supplierid) = @_;
583 my $dbh = C4::Context->dbh;
585 my $strsth = "Select count(*),authorisedby,creationdate,aqbasket.basketno,
586 closedate,surname,firstname,aqorders.biblionumber,aqorders.title, aqorders.ordernumber
588 left join aqbasket on aqbasket.basketno=aqorders.basketno
589 left join borrowers on aqbasket.authorisedby=borrowers.borrowernumber
590 where booksellerid=? and (quantity > quantityreceived or
591 quantityreceived is NULL) and datecancellationprinted is NULL ";
593 if ( C4::Context->preference("IndependantBranches") ) {
594 my $userenv = C4::Context->userenv;
595 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
597 " and (borrowers.branchcode = '"
599 . "' or borrowers.branchcode ='')";
602 $strsth .= " group by basketno order by aqbasket.basketno";
603 my $sth = $dbh->prepare($strsth);
604 $sth->execute($supplierid);
605 while ( my $data = $sth->fetchrow_hashref ) {
606 push( @results, $data );
609 return ( scalar(@results), @results );
612 =item getparcelinformation
614 ($count, @results) = &getparcelinformation($booksellerid, $code, $date);
616 Looks up all of the received items from the supplier with the given
617 bookseller ID at the given date, for the given code. Ignores cancelled and completed orders.
619 C<$count> is the number of elements in C<@results>. C<@results> is an
620 array of references-to-hash. The keys of each element are fields from
621 the aqorders, biblio, and biblioitems tables of the Koha database.
623 C<@results> is sorted alphabetically by book title.
628 sub getparcelinformation {
630 #gets all orders from a certain supplier, orders them alphabetically
631 my ( $supplierid, $code, $datereceived ) = @_;
632 my $dbh = C4::Context->dbh;
635 if $code; # add % if we search on a given code (otherwise, let him empty)
637 "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\'";
639 if ( C4::Context->preference("IndependantBranches") ) {
640 my $userenv = C4::Context->userenv;
641 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
643 " and (borrowers.branchcode = '"
645 . "' or borrowers.branchcode ='')";
648 $strsth .= " order by aqbasket.basketno";
649 ### parcelinformation : $strsth
650 my $sth = $dbh->prepare($strsth);
651 $sth->execute($supplierid);
652 while ( my $data = $sth->fetchrow_hashref ) {
653 push( @results, $data );
655 my $count = scalar(@results);
656 ### countparcelbiblio: $count
659 return ( scalar(@results), @results );
662 =item getsupplierlistwithlateorders
664 %results = &getsupplierlistwithlateorders;
666 Searches for suppliers with late orders.
671 sub getsupplierlistwithlateorders {
673 my $dbh = C4::Context->dbh;
675 #FIXME NOT quite sure that this operation is valid for DBMs different from Mysql, HOPING so
676 #should be tested with other DBMs
679 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
680 if ( $dbdriver eq "mysql" ) {
681 $strsth = "SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
682 FROM aqorders, aqbasket
683 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
684 WHERE aqorders.basketno = aqbasket.basketno AND
685 (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY) AND (datereceived = '' or datereceived is null))
689 $strsth = "SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
690 FROM aqorders, aqbasket
691 LEFT JOIN aqbooksellers ON aqbasket.aqbooksellerid = aqbooksellers.id
692 WHERE aqorders.basketno = aqbasket.basketno AND
693 (closedate < (CURDATE( )-(INTERVAL $delay DAY))) AND (datereceived = '' or datereceived is null))
697 # warn "C4::Acquisition getsupplierlistwithlateorders : ".$strsth;
698 my $sth = $dbh->prepare($strsth);
701 while ( my ( $id, $name ) = $sth->fetchrow ) {
702 $supplierlist{$id} = $name;
704 return %supplierlist;
709 %results = &getlateorders;
711 Searches for suppliers with late orders.
718 my $supplierid = shift;
721 my $dbh = C4::Context->dbh;
723 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
725 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
728 if ( $dbdriver eq "mysql" ) {
729 $strsth = "SELECT aqbasket.basketno,
730 DATE(aqbasket.closedate) as orderdate, aqorders.quantity - IFNULL(aqorders.quantityreceived,0) as quantity, aqorders.rrp as unitpricesupplier,aqorders.ecost as unitpricelib,
731 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp as subtotal, aqbookfund.bookfundname as budget, borrowers.branchcode as branch,
732 aqbooksellers.name as supplier,
733 aqorders.title, biblio.author, biblioitems.publishercode as publisher, biblioitems.publicationyear,
734 DATEDIFF(CURDATE( ),closedate) AS latesince
737 (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber) LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber
738 ) LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber
739 ) LEFT JOIN aqbookfund on aqorderbreakdown.bookfundid = aqbookfund.bookfundid
740 ),(aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
741 WHERE aqorders.basketno = aqbasket.basketno AND (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY))
742 AND ((datereceived = '' OR datereceived is null) OR (aqorders.quantityreceived < aqorders.quantity) ) ";
743 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
744 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'"
747 " AND borrowers.branchcode like \'"
748 . C4::Context->userenv->{branch} . "\'"
749 if ( C4::Context->preference("IndependantBranches")
750 && C4::Context->userenv
751 && C4::Context->userenv->{flags} != 1 );
753 " HAVING quantity<>0 AND unitpricesupplier<>0 AND unitpricelib<>0 ORDER BY latesince,basketno,borrowers.branchcode, supplier ";
756 $strsth = "SELECT aqbasket.basketno,
757 DATE(aqbasket.closedate) as orderdate,
758 aqorders.quantity, aqorders.rrp as unitpricesupplier,aqorders.ecost as unitpricelib, aqorders.quantity * aqorders.rrp as subtotal
759 aqbookfund.bookfundname as budget, borrowers.branchcode as branch,
760 aqbooksellers.name as supplier,
761 biblio.title, biblio.author, biblioitems.publishercode as publisher, biblioitems.publicationyear,
762 (CURDATE - closedate) AS latesince
765 (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber) LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber
766 ) LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber
767 ) LEFT JOIN aqbookfund on aqorderbreakdown.bookfundid = aqbookfund.bookfundid
768 ),(aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
769 WHERE aqorders.basketno = aqbasket.basketno AND (closedate < (CURDATE -(INTERVAL $delay DAY))
770 AND ((datereceived = '' OR datereceived is null) OR (aqorders.quantityreceived < aqorders.quantity) ) ";
771 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
772 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'"
775 " AND borrowers.branchcode like \'"
776 . C4::Context->userenv->{branch} . "\'"
777 if ( C4::Context->preference("IndependantBranches")
778 && C4::Context->userenv->{flags} != 1 );
780 " ORDER BY latesince,basketno,borrowers.branchcode, supplier";
782 warn "C4::Acquisition : getlateorders SQL:" . $strsth;
783 my $sth = $dbh->prepare($strsth);
787 while ( my $data = $sth->fetchrow_hashref ) {
788 $data->{hilighted} = $hilighted if ( $hilighted > 0 );
789 $data->{orderdate} = format_date( $data->{orderdate} );
790 push @results, $data;
791 $hilighted = -$hilighted;
794 return ( scalar(@results), @results );
800 #gets all orders from a certain supplier, orders them alphabetically
802 my $dbh = C4::Context->dbh;
804 my $sth = $dbh->prepare(
805 "Select * from aqorders,biblio,biblioitems where booksellerid=?
806 and (cancelledby is NULL or cancelledby = '')
807 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
808 aqorders.biblioitemnumber and
809 aqorders.quantityreceived>0
810 and aqorders.datereceived >=now()
811 group by aqorders.biblioitemnumber
815 $sth->execute($supid);
816 while ( my $data = $sth->fetchrow_hashref ) {
817 push( @results, $data );
820 return ( scalar(@results), @results );
825 ($count, @results) = &ordersearch($search, $biblionumber, $complete);
829 C<$search> may take one of several forms: if it is an ISBN,
830 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
831 order number, C<&ordersearch> returns orders with that order number
832 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
833 to be a space-separated list of search terms; in this case, all of the
834 terms must appear in the title (matching the beginning of title
837 If C<$complete> is C<yes>, the results will include only completed
838 orders. In any case, C<&ordersearch> ignores cancelled orders.
840 C<&ordersearch> returns an array. C<$count> is the number of elements
841 in C<@results>. C<@results> is an array of references-to-hash with the
860 my ( $search, $id, $biblio, $catview ) = @_;
861 my $dbh = C4::Context->dbh;
862 my @data = split( ' ', $search );
863 my @searchterms = ($id);
864 map { push( @searchterms, "$_%", "% $_%" ) } @data;
865 push( @searchterms, $search, $search, $biblio );
866 my $sth = $dbh->prepare(
867 "Select biblio.*,biblioitems.*,aqorders.*,aqbasket.*,biblio.title from aqorders,biblioitems,biblio,aqbasket
868 where aqorders.biblioitemnumber = biblioitems.biblioitemnumber and
869 aqorders.basketno = aqbasket.basketno
870 and aqbasket.booksellerid = ?
871 and biblio.biblionumber=aqorders.biblionumber
872 and ((datecancellationprinted is NULL)
873 or (datecancellationprinted = '0000-00-00'))
877 map { "(biblio.title like ? or biblio.title like ?)" } @data )
879 . ") or biblioitems.isbn=? or (aqorders.ordernumber=? and aqorders.biblionumber=?)) "
881 ( $catview ne 'yes' )
882 ? " and (quantityreceived < quantity or quantityreceived is NULL)"
885 . " group by aqorders.ordernumber"
887 $sth->execute(@searchterms);
889 my $sth2 = $dbh->prepare("Select * from biblio where biblionumber=?");
891 $dbh->prepare("Select * from aqorderbreakdown where ordernumber=?");
892 while ( my $data = $sth->fetchrow_hashref ) {
893 $sth2->execute( $data->{'biblionumber'} );
894 my $data2 = $sth2->fetchrow_hashref;
895 $data->{'author'} = $data2->{'author'};
896 $data->{'seriestitle'} = $data2->{'seriestitle'};
897 $sth3->execute( $data->{'ordernumber'} );
898 my $data3 = $sth3->fetchrow_hashref;
899 $data->{'branchcode'} = $data3->{'branchcode'};
900 $data->{'bookfundid'} = $data3->{'bookfundid'};
901 push( @results, $data );
906 return ( scalar(@results), @results );
910 my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_;
913 my $total_qtyreceived = 0;
916 # don't run the query if there are no parameters (list would be too long for sure !
917 if ( $title || $author || $name || $from_placed_on || $to_placed_on ) {
918 my $dbh = C4::Context->dbh;
920 "select biblio.title,biblio.author,aqorders.basketno,name,aqbasket.creationdate,aqorders.datereceived, aqorders.quantity, aqorders.quantityreceived, aqorders.ecost from aqorders,aqbasket,aqbooksellers,biblio";
921 $query .= ",borrowers "
922 if ( C4::Context->preference("IndependantBranches") );
924 " where aqorders.basketno=aqbasket.basketno and aqbasket.booksellerid=aqbooksellers.id and biblio.biblionumber=aqorders.biblionumber ";
925 $query .= " and aqbasket.authorisedby=borrowers.borrowernumber"
926 if ( C4::Context->preference("IndependantBranches") );
927 $query .= " and biblio.title like " . $dbh->quote( "%" . $title . "%" )
930 " and biblio.author like " . $dbh->quote( "%" . $author . "%" )
932 $query .= " and name like " . $dbh->quote( "%" . $name . "%" ) if $name;
933 $query .= " and creationdate >" . $dbh->quote($from_placed_on)
935 $query .= " and creationdate<" . $dbh->quote($to_placed_on)
938 if ( C4::Context->preference("IndependantBranches") ) {
939 my $userenv = C4::Context->userenv;
940 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
942 " and (borrowers.branchcode = '"
944 . "' or borrowers.branchcode ='')";
947 $query .= " order by booksellerid";
948 warn "query histearch: " . $query;
949 my $sth = $dbh->prepare($query);
952 while ( my $line = $sth->fetchrow_hashref ) {
953 $line->{count} = $cnt++;
954 $line->{toggle} = 1 if $cnt % 2;
955 push @order_loop, $line;
956 $line->{creationdate} = format_date( $line->{creationdate} );
957 $line->{datereceived} = format_date( $line->{datereceived} );
958 $total_qty += $line->{'quantity'};
959 $total_qtyreceived += $line->{'quantityreceived'};
960 $total_price += $line->{'quantity'} * $line->{'ecost'};
963 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
974 ($count, @results) = &invoice($booksellerinvoicenumber);
976 Looks up orders by invoice number.
978 Returns an array. C<$count> is the number of elements in C<@results>.
979 C<@results> is an array of references-to-hash; the keys of each
980 elements are fields from the aqorders, biblio, and biblioitems tables
981 of the Koha database.
988 my $dbh = C4::Context->dbh;
990 my $sth = $dbh->prepare(
991 "Select * from aqorders,biblio,biblioitems where
992 booksellerinvoicenumber=?
993 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
994 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber"
996 $sth->execute($invoice);
997 while ( my $data = $sth->fetchrow_hashref ) {
998 push( @results, $data );
1001 return ( scalar(@results), @results );
1006 ($count, @results) = &bookfunds();
1008 Returns a list of all book funds.
1010 C<$count> is the number of elements in C<@results>. C<@results> is an
1011 array of references-to-hash, whose keys are fields from the aqbookfund
1012 and aqbudget tables of the Koha database. Results are ordered
1013 alphabetically by book fund name.
1020 my $dbh = C4::Context->dbh;
1021 my $userenv = C4::Context->userenv;
1022 my $branch = $userenv->{branch};
1025 if ( !( $branch eq '' ) ) {
1026 $strsth = "Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
1027 =aqbudget.bookfundid and startdate<now() and enddate>now() and (aqbookfund.branchcode is null or aqbookfund.branchcode='' or aqbookfund.branchcode= ? )
1028 group by aqbookfund.bookfundid order by bookfundname";
1031 $strsth = "Select * from aqbookfund,aqbudget where aqbookfund.bookfundid
1032 =aqbudget.bookfundid and startdate<now() and enddate>now()
1033 group by aqbookfund.bookfundid order by bookfundname";
1035 my $sth = $dbh->prepare($strsth);
1036 if ( !( $branch eq '' ) ) {
1037 $sth->execute($branch);
1043 while ( my $data = $sth->fetchrow_hashref ) {
1044 push( @results, $data );
1047 return ( scalar(@results), @results );
1050 =item bookfundbreakdown
1052 returns the total comtd & spent for a given bookfund, and a given year
1053 used in acqui-home.pl
1058 sub bookfundbreakdown {
1059 my ( $id, $year ) = @_;
1060 my $dbh = C4::Context->dbh;
1061 my $sth = $dbh->prepare(
1062 "SELECT startdate, enddate, quantity, datereceived, freight, unitprice, listprice, ecost, quantityreceived, subscription
1063 FROM aqorders, aqorderbreakdown, aqbudget, aqbasket
1064 WHERE aqorderbreakdown.bookfundid = ?
1065 AND aqorders.ordernumber = aqorderbreakdown.ordernumber
1067 datecancellationprinted IS NULL
1068 OR datecancellationprinted = '0000-00-00'
1070 AND aqbudget.bookfundid = aqorderbreakdown.bookfundid
1071 AND aqbasket.basketno = aqorders.basketno
1072 AND aqbasket.creationdate >= startdate
1073 AND enddate >= aqbasket.creationdate
1074 and startdate<=now() and enddate>=now()"
1079 while ( my $data = $sth->fetchrow_hashref ) {
1081 if ( $data->{'subscription'} == 1 ) {
1082 $spent += $data->{'quantity'} * $data->{'unitprice'};
1085 my $leftover = $data->{'quantity'} - $data->{'quantityreceived'};
1086 $comtd += ( $data->{'ecost'} ) * $leftover;
1087 $spent += ( $data->{'unitprice'} ) * $data->{'quantityreceived'};
1091 return ( $spent, $comtd );
1096 $foreignprice = &curconvert($currency, $localprice);
1098 Converts the price C<$localprice> to foreign currency C<$currency> by
1099 dividing by the exchange rate, and returns the result.
1101 If no exchange rate is found, C<&curconvert> assumes the rate is one
1108 my ( $currency, $price ) = @_;
1109 my $dbh = C4::Context->dbh;
1110 my $sth = $dbh->prepare("Select rate from currency where currency=?");
1111 $sth->execute($currency);
1112 my $cur = ( $sth->fetchrow_array() )[0];
1117 return ( $price / $cur );
1122 ($count, $currencies) = &getcurrencies();
1124 Returns the list of all known currencies.
1126 C<$count> is the number of elements in C<$currencies>. C<$currencies>
1127 is a reference-to-array; its elements are references-to-hash, whose
1128 keys are the fields from the currency table in the Koha database.
1134 my $dbh = C4::Context->dbh;
1135 my $sth = $dbh->prepare("Select * from currency");
1138 while ( my $data = $sth->fetchrow_hashref ) {
1139 push( @results, $data );
1142 return ( scalar(@results), \@results );
1145 =item updatecurrencies
1147 &updatecurrencies($currency, $newrate);
1149 Sets the exchange rate for C<$currency> to be C<$newrate>.
1154 sub updatecurrencies {
1155 my ( $currency, $rate ) = @_;
1156 my $dbh = C4::Context->dbh;
1157 my $sth = $dbh->prepare("update currency set rate=? where currency=?");
1158 $sth->execute( $rate, $currency );
1170 ($count, @results) = &bookseller($searchstring);
1172 Looks up a book seller. C<$searchstring> may be either a book seller
1173 ID, or a string to look for in the book seller's name.
1175 C<$count> is the number of elements in C<@results>. C<@results> is an
1176 array of references-to-hash, whose keys are the fields of of the
1177 aqbooksellers table in the Koha database.
1183 my ($searchstring) = @_;
1184 my $dbh = C4::Context->dbh;
1186 $dbh->prepare("Select * from aqbooksellers where name like ? or id = ?");
1187 $sth->execute( "$searchstring%", $searchstring );
1189 while ( my $data = $sth->fetchrow_hashref ) {
1190 push( @results, $data );
1193 return ( scalar(@results), @results );
1198 ($count, $results) = &breakdown($ordernumber);
1200 Looks up an order by order ID, and returns its breakdown.
1202 C<$count> is the number of elements in C<$results>. C<$results> is a
1203 reference-to-array; its elements are references-to-hash, whose keys
1204 are the fields of the aqorderbreakdown table in the Koha database.
1211 my $dbh = C4::Context->dbh;
1213 $dbh->prepare("Select * from aqorderbreakdown where ordernumber=?");
1216 while ( my $data = $sth->fetchrow_hashref ) {
1217 push( @results, $data );
1220 return ( scalar(@results), \@results );
1225 ($count, @results) = &branches();
1227 Returns a list of all library branches.
1229 C<$count> is the number of elements in C<@results>. C<@results> is an
1230 array of references-to-hash, whose keys are the fields of the branches
1231 table of the Koha database.
1237 my $dbh = C4::Context->dbh;
1239 if ( C4::Context->preference("IndependantBranches")
1240 && ( C4::Context->userenv )
1241 && ( C4::Context->userenv->{flags} != 1 ) )
1243 my $strsth = "Select * from branches ";
1245 " WHERE branchcode = "
1246 . $dbh->quote( C4::Context->userenv->{branch} );
1247 $strsth .= " order by branchname";
1248 warn "C4::Acquisition->branches : " . $strsth;
1249 $sth = $dbh->prepare($strsth);
1252 $sth = $dbh->prepare("Select * from branches order by branchname");
1257 while ( my $data = $sth->fetchrow_hashref ) {
1258 push( @results, $data );
1262 return ( scalar(@results), @results );
1267 &updatesup($bookseller);
1269 Updates the information for a given bookseller. C<$bookseller> is a
1270 reference-to-hash whose keys are the fields of the aqbooksellers table
1271 in the Koha database. It must contain entries for all of the fields.
1272 The entry to modify is determined by C<$bookseller-E<gt>{id}>.
1274 The easiest way to get all of the necessary fields is to look up a
1275 book seller with C<&booksellers>, modify what's necessary, then call
1276 C<&updatesup> with the result.
1283 my $dbh = C4::Context->dbh;
1284 my $sth = $dbh->prepare(
1285 "Update aqbooksellers set
1286 name=?,address1=?,address2=?,address3=?,address4=?,postal=?,
1287 phone=?,fax=?,url=?,contact=?,contpos=?,contphone=?,contfax=?,contaltphone=?,
1288 contemail=?,contnotes=?,active=?,
1289 listprice=?, invoiceprice=?,gstreg=?, listincgst=?,
1290 invoiceincgst=?, specialty=?,discount=?,invoicedisc=?,
1295 $data->{'name'}, $data->{'address1'},
1296 $data->{'address2'}, $data->{'address3'},
1297 $data->{'address4'}, $data->{'postal'},
1298 $data->{'phone'}, $data->{'fax'},
1299 $data->{'url'}, $data->{'contact'},
1300 $data->{'contpos'}, $data->{'contphone'},
1301 $data->{'contfax'}, $data->{'contaltphone'},
1302 $data->{'contemail'}, $data->{'contnote'},
1303 $data->{'active'}, $data->{'listprice'},
1304 $data->{'invoiceprice'}, $data->{'gstreg'},
1305 $data->{'listincgst'}, $data->{'invoiceincgst'},
1306 $data->{'specialty'}, $data->{'discount'},
1307 $data->{'invoicedisc'}, $data->{'nocalc'},
1315 $id = &insertsup($bookseller);
1317 Creates a new bookseller. C<$bookseller> is a reference-to-hash whose
1318 keys are the fields of the aqbooksellers table in the Koha database.
1319 All fields must be present.
1321 Returns the ID of the newly-created bookseller.
1328 my $dbh = C4::Context->dbh;
1329 my $sth = $dbh->prepare("Select max(id) from aqbooksellers");
1331 my $data2 = $sth->fetchrow_hashref;
1333 $data2->{'max(id)'}++;
1334 $sth = $dbh->prepare("Insert into aqbooksellers (id) values (?)");
1335 $sth->execute( $data2->{'max(id)'} );
1337 $data->{'id'} = $data2->{'max(id)'};
1339 return ( $data->{'id'} );
1344 ($count, $results) = &getparcels($dbh, $bookseller, $order, $limit);
1346 get a lists of parcels
1347 Returns the count of parcels returned and a pointer on a hash list containing parcel informations as such :
1358 my ( $bookseller, $order, $code, $datefrom, $dateto, $limit ) = @_;
1359 my $dbh = C4::Context->dbh;
1361 "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 ";
1362 $strsth .= "and aqorders.booksellerinvoicenumber like \"$code%\" "
1364 $strsth .= "and datereceived >=" . $dbh->quote($datefrom) . " "
1366 $strsth .= "and datereceived <=" . $dbh->quote($dateto) . " " if ($dateto);
1367 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
1368 $strsth .= "order by $order " if ($order);
1369 $strsth .= " LIMIT 0,$limit" if ($limit);
1370 my $sth = $dbh->prepare($strsth);
1371 ### getparcels: $strsth
1375 while ( my $data2 = $sth->fetchrow_hashref ) {
1376 push @results, $data2;
1380 return ( scalar(@results), @results );
1383 END { } # module clean-up code here (global destructor)
1392 Koha Developement team <info@koha.org>