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
30 use vars qw($VERSION @ISA @EXPORT);
32 # set the version for version checking
33 $VERSION = do { my @v = '$Revision$' =~ /\d+/g; shift(@v) . "." . join( "_", map { sprintf "%03d", $_ } @v ); };
35 # used in reciveorder subroutine
36 # to provide library specific handling
37 my $library_name = C4::Context->preference("LibraryName");
41 C4::Acquisition - Koha functions for dealing with orders and acquisitions
49 The functions in this module deal with acquisitions, managing book
50 orders, converting money to different currencies, and so forth.
60 &getbasket &getbasketcontent &newbasket &closebasket
62 &getorders &getallorders &getrecorders
63 &getorder &neworder &delorder
64 &ordersearch &histsearch
65 &modorder &getsingleorder &invoice &receiveorder
66 &updaterecorder &newordernum
67 &getsupplierlistwithlateorders
69 &getparcels &getparcelinformation
70 &bookfunds &curconvert &getcurrencies &bookfundbreakdown
71 &updatecurrencies &getcurrency
73 &bookseller &breakdown
86 $aqbasket = &getbasket($basketnumber);
88 get all basket informations in aqbasket for a given basket
93 my $dbh = C4::Context->dbh;
96 "select aqbasket.*,borrowers.firstname+' '+borrowers.surname as authorisedbyname, borrowers.branchcode as branch from aqbasket left join borrowers on aqbasket.authorisedby=borrowers.borrowernumber where basketno=?"
98 $sth->execute($basketno);
99 return ( $sth->fetchrow_hashref );
103 =item getbasketcontent
105 ($count, @orders) = &getbasketcontent($basketnumber, $booksellerID);
107 Looks up the pending (non-cancelled) orders with the given basket
108 number. If C<$booksellerID> is non-empty, only orders from that seller
111 C<&basket> returns a two-element array. C<@orders> is an array of
112 references-to-hash, whose keys are the fields from the aqorders,
113 biblio, and biblioitems tables in the Koha database. C<$count> is the
114 number of elements in C<@orders>.
119 sub getbasketcontent {
120 my ( $basketno, $supplier, $orderby ) = @_;
121 my $dbh = C4::Context->dbh;
123 "SELECT aqorderbreakdown.*,biblio.*,biblioitems.*,aqorders.*,biblio.title FROM aqorders,biblio,biblioitems
124 LEFT JOIN aqorderbreakdown ON aqorderbreakdown.ordernumber=aqorders.ordernumber
126 AND biblio.biblionumber=aqorders.biblionumber AND biblioitems.biblioitemnumber
127 =aqorders.biblioitemnumber
128 AND (datecancellationprinted IS NULL OR datecancellationprinted =
130 if ( $supplier ne '' ) {
131 $query .= " AND aqorders.booksellerid=?";
134 $orderby = "biblioitems.publishercode" unless $orderby;
135 $query .= " ORDER BY $orderby";
136 my $sth = $dbh->prepare($query);
137 if ( $supplier ne '' ) {
138 $sth->execute( $basketno, $supplier );
141 $sth->execute($basketno);
147 while ( my $data = $sth->fetchrow_hashref ) {
148 $results[$i] = $data;
152 return ( $i, @results );
157 $basket = &newbasket();
159 Create a new basket in aqbasket table
163 my ( $booksellerid, $authorisedby ) = @_;
164 my $dbh = C4::Context->dbh;
167 "insert into aqbasket (creationdate,booksellerid,authorisedby) values(now(),'$booksellerid','$authorisedby')"
170 #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
171 my $basket = $dbh->{'mysql_insertid'};
177 &newbasket($basketno);
179 close a basket (becomes unmodifiable,except for recieves
184 my $dbh = C4::Context->dbh;
186 $dbh->prepare("update aqbasket set closedate=now() where basketno=?");
187 $sth->execute($basketno);
192 &neworder($basket, $biblionumber, $title, $quantity, $listprice,
193 $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp,
194 $ecost, $gst, $budget, $unitprice, $subscription,
195 $booksellerinvoicenumber);
197 Adds a new order to the database. Any argument that isn't described
198 below is the new value of the field with the same name in the aqorders
199 table of the Koha database.
201 C<$ordnum> is a "minimum order number." After adding the new entry to
202 the aqorders table, C<&neworder> finds the first entry in aqorders
203 with order number greater than or equal to C<$ordnum>, and adds an
204 entry to the aqorderbreakdown table, with the order number just found,
205 and the book fund ID of the newly-added order.
207 C<$budget> is effectively ignored.
209 C<$subscription> may be either "yes", or anything else for "no".
216 $basketno, $bibnum, $title, $quantity,
217 $listprice, $booksellerid, $authorisedby, $notes,
218 $bookfund, $bibitemnum, $rrp, $ecost,
219 $gst, $budget, $cost, $sub,
220 $invoice, $sort1, $sort2
224 my $year = localtime->year() + 1900;
225 my $month = localtime->mon() + 1; # months starts at 0, add 1
227 if ( !$budget || $budget eq 'now' ) {
231 # if month is july or more, budget start is 1 jul, next year.
232 elsif ( $month >= '7' ) {
233 ++$year; # add 1 to year , coz its next year
234 $budget = "'$year-07-01'";
238 # START OF NEW BUDGET, 1ST OF JULY, THIS YEAR
239 $budget = "'$year-07-01'";
242 if ( $sub eq 'yes' ) {
249 # if $basket empty, it's also a new basket, create it
251 $basketno = newbasket( $booksellerid, $authorisedby );
254 my $dbh = C4::Context->dbh;
255 my $sth = $dbh->prepare(
256 "insert into aqorders
257 ( biblionumber,title,basketno,quantity,listprice,notes,
258 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,sort1,sort2,budgetdate,entrydate)
259 values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,$budget,now() )"
263 $bibnum, $title, $basketno, $quantity, $listprice,
264 $notes, $bibitemnum, $rrp, $ecost, $gst,
265 $cost, $sub, $sort1, $sort2
269 #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
270 my $ordnum = $dbh->{'mysql_insertid'};
271 $sth = $dbh->prepare(
272 "insert into aqorderbreakdown (ordernumber,bookfundid) values
275 $sth->execute( $ordnum, $bookfund );
277 return ( $basketno, $ordnum );
282 &delorder($biblionumber, $ordernumber);
284 Cancel the order with the given order and biblio numbers. It does not
285 delete any entries in the aqorders table, it merely marks them as
292 my ( $bibnum, $ordnum ) = @_;
293 my $dbh = C4::Context->dbh;
294 my $sth = $dbh->prepare(
295 "update aqorders set datecancellationprinted=now()
296 where biblionumber=? and ordernumber=?"
298 $sth->execute( $bibnum, $ordnum );
304 &modorder($title, $ordernumber, $quantity, $listprice,
305 $biblionumber, $basketno, $supplier, $who, $notes,
306 $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget,
307 $unitprice, $booksellerinvoicenumber);
309 Modifies an existing order. Updates the order with order number
310 C<$ordernumber> and biblionumber C<$biblionumber>. All other arguments
311 update the fields with the same name in the aqorders table of the Koha
314 Entries with order number C<$ordernumber> in the aqorderbreakdown
315 table are also updated to the new book fund ID.
322 $title, $ordnum, $quantity, $listprice, $bibnum,
323 $basketno, $supplier, $who, $notes, $bookfund,
324 $bibitemnum, $rrp, $ecost, $gst, $budget,
325 $cost, $invoice, $sort1, $sort2
328 my $dbh = C4::Context->dbh;
329 my $sth = $dbh->prepare(
330 "update aqorders set title=?,
331 quantity=?,listprice=?,basketno=?,
332 rrp=?,ecost=?,unitprice=?,booksellerinvoicenumber=?,
333 notes=?,sort1=?, sort2=?
335 ordernumber=? and biblionumber=?"
338 $title, $quantity, $listprice, $basketno, $rrp,
339 $ecost, $cost, $invoice, $notes, $sort1,
340 $sort2, $ordnum, $bibnum
343 $sth = $dbh->prepare(
344 "update aqorderbreakdown set bookfundid=? where
348 unless ( $sth->execute( $bookfund, $ordnum ) )
349 { # zero rows affected [Bug 734]
351 "insert into aqorderbreakdown (ordernumber,bookfundid) values (?,?)";
352 $sth = $dbh->prepare($query);
353 $sth->execute( $ordnum, $bookfund );
360 $order = &newordernum();
362 Finds the next unused order number in the aqorders table of the Koha
363 database, and returns it.
368 # FIXME - Race condition
370 my $dbh = C4::Context->dbh;
371 my $sth = $dbh->prepare("Select max(ordernumber) from aqorders");
373 my $data = $sth->fetchrow_arrayref;
374 my $ordnum = $$data[0];
382 &receiveorder($biblionumber, $ordernumber, $quantityreceived, $user,
383 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
384 $freight, $bookfund, $rrp);
386 Updates an order, to reflect the fact that it was received, at least
387 in part. All arguments not mentioned below update the fields with the
388 same name in the aqorders table of the Koha database.
390 Updates the order with bibilionumber C<$biblionumber> and ordernumber
393 Also updates the book fund ID in the aqorderbreakdown table.
400 $biblio, $ordnum, $quantrec, $user, $cost,
401 $invoiceno, $freight, $rrp, $bookfund
404 my $dbh = C4::Context->dbh;
405 my $sth = $dbh->prepare(
406 "update aqorders set quantityreceived=?,datereceived=now(),booksellerinvoicenumber=?,
407 unitprice=?,freight=?,rrp=?
408 where biblionumber=? and ordernumber=?"
410 my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblio );
412 ModStatus( $suggestionid, 'AVAILABLE', '', $biblio );
414 $sth->execute( $quantrec, $invoiceno, $cost, $freight, $rrp, $biblio,
418 # Allows libraries to change their bookfund during receiving orders
419 # allows them to adjust budgets
420 if ( C4::Context->preferene("LooseBudgets") ) {
421 my $sth = $dbh->prepare(
422 "UPDATE aqorderbreakdown SET bookfundid=?
425 $sth->execute( $bookfund, $ordnum );
432 &updaterecorder($biblionumber, $ordernumber, $user, $unitprice,
435 Updates the order with biblionumber C<$biblionumber> and order number
436 C<$ordernumber>. C<$bookfundid> is the new value for the book fund ID
437 in the aqorderbreakdown table of the Koha database. All other
438 arguments update the fields with the same name in the aqorders table.
446 my ( $biblio, $ordnum, $user, $cost, $bookfund, $rrp ) = @_;
447 my $dbh = C4::Context->dbh;
448 my $sth = $dbh->prepare(
451 where biblionumber=? and ordernumber=?
454 $sth->execute( $cost, $rrp, $biblio, $ordnum );
458 "update aqorderbreakdown set bookfundid=? where ordernumber=?");
459 $sth->execute( $bookfund, $ordnum );
471 ($count, $orders) = &getorders($booksellerid);
473 Finds pending orders from the bookseller with the given ID. Ignores
474 completed and cancelled orders.
476 C<$count> is the number of elements in C<@{$orders}>.
478 C<$orders> is a reference-to-array; each element is a
479 reference-to-hash with the following fields:
485 Gives the number of orders in with this basket number.
487 =item C<authorizedby>
493 These give the value of the corresponding field in the aqorders table
494 of the Koha database.
498 Results are ordered from most to least recent.
504 my ($supplierid) = @_;
505 my $dbh = C4::Context->dbh;
506 my $strsth = "Select count(*),authorisedby,creationdate,aqbasket.basketno,
507 closedate,surname,firstname,aqorders.title
509 left join aqbasket on aqbasket.basketno=aqorders.basketno
510 left join borrowers on aqbasket.authorisedby=borrowers.borrowernumber
511 where booksellerid=? and (quantity > quantityreceived or
512 quantityreceived is NULL) and datecancellationprinted is NULL and (to_days(now())-to_days(closedate) < 180 or closedate is null)";
513 if ( C4::Context->preference("IndependantBranches") ) {
514 my $userenv = C4::Context->userenv;
515 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
517 " and (borrowers.branchcode = '"
519 . "' or borrowers.branchcode ='')";
522 $strsth .= " group by basketno order by aqbasket.basketno";
523 my $sth = $dbh->prepare($strsth);
524 $sth->execute($supplierid);
526 while ( my $data = $sth->fetchrow_hashref ) {
527 push( @results, $data );
530 return ( scalar(@results), \@results );
535 ($order, $ordernumber) = &getorder($biblioitemnumber, $biblionumber);
537 Looks up the order with the given biblionumber and biblioitemnumber.
539 Returns a two-element array. C<$ordernumber> is the order number.
540 C<$order> is a reference-to-hash describing the order; its keys are
541 fields from the biblio, biblioitems, aqorders, and aqorderbreakdown
542 tables of the Koha database.
547 my ( $bi, $bib ) = @_;
548 my $dbh = C4::Context->dbh;
551 "Select ordernumber from aqorders where biblionumber=? and biblioitemnumber=?"
553 $sth->execute( $bib, $bi );
555 # FIXME - Use fetchrow_array(), since we're only interested in the one
557 my $ordnum = $sth->fetchrow_hashref;
559 my $order = getsingleorder( $ordnum->{'ordernumber'} );
560 return ( $order, $ordnum->{'ordernumber'} );
565 $order = &getsingleorder($ordernumber);
567 Looks up an order by order number.
569 Returns a reference-to-hash describing the order. The keys of
570 C<$order> are fields from the biblio, biblioitems, aqorders, and
571 aqorderbreakdown tables of the Koha database.
577 my $dbh = C4::Context->dbh;
578 my $sth = $dbh->prepare(
579 "Select * from biblio,biblioitems,aqorders left join aqorderbreakdown
580 on aqorders.ordernumber=aqorderbreakdown.ordernumber
581 where aqorders.ordernumber=?
582 and biblio.biblionumber=aqorders.biblionumber and
583 biblioitems.biblioitemnumber=aqorders.biblioitemnumber"
585 $sth->execute($ordnum);
586 my $data = $sth->fetchrow_hashref;
593 ($count, @results) = &getallorders($booksellerid);
595 Looks up all of the pending orders from the supplier with the given
596 bookseller ID. Ignores cancelled and completed orders.
598 C<$count> is the number of elements in C<@results>. C<@results> is an
599 array of references-to-hash. The keys of each element are fields from
600 the aqorders, biblio, and biblioitems tables of the Koha database.
602 C<@results> is sorted alphabetically by book title.
609 #gets all orders from a certain supplier, orders them alphabetically
610 my ($supplierid) = @_;
611 my $dbh = C4::Context->dbh;
613 my $strsth = "Select count(*),authorisedby,creationdate,aqbasket.basketno,
614 closedate,surname,firstname,aqorders.biblionumber,aqorders.title, aqorders.ordernumber
616 left join aqbasket on aqbasket.basketno=aqorders.basketno
617 left join borrowers on aqbasket.authorisedby=borrowers.borrowernumber
618 where booksellerid=? and (quantity > quantityreceived or
619 quantityreceived is NULL) and datecancellationprinted is NULL ";
621 if ( C4::Context->preference("IndependantBranches") ) {
622 my $userenv = C4::Context->userenv;
623 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
625 " and (borrowers.branchcode = '"
627 . "' or borrowers.branchcode ='')";
630 $strsth .= " group by basketno order by aqbasket.basketno";
631 my $sth = $dbh->prepare($strsth);
632 $sth->execute($supplierid);
633 while ( my $data = $sth->fetchrow_hashref ) {
634 push( @results, $data );
637 return ( scalar(@results), @results );
640 =item getparcelinformation
642 ($count, @results) = &getparcelinformation($booksellerid, $code, $date);
644 Looks up all of the received items from the supplier with the given
645 bookseller ID at the given date, for the given code. Ignores cancelled and completed orders.
647 C<$count> is the number of elements in C<@results>. C<@results> is an
648 array of references-to-hash. The keys of each element are fields from
649 the aqorders, biblio, and biblioitems tables of the Koha database.
651 C<@results> is sorted alphabetically by book title.
656 sub getparcelinformation {
658 #gets all orders from a certain supplier, orders them alphabetically
659 my ( $supplierid, $code, $datereceived ) = @_;
660 my $dbh = C4::Context->dbh;
663 if $code; # add % if we search on a given code (otherwise, let him empty)
665 "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\'";
667 if ( C4::Context->preference("IndependantBranches") ) {
668 my $userenv = C4::Context->userenv;
669 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
671 " and (borrowers.branchcode = '"
673 . "' or borrowers.branchcode ='')";
676 $strsth .= " order by aqbasket.basketno";
677 ### parcelinformation : $strsth
678 my $sth = $dbh->prepare($strsth);
679 $sth->execute($supplierid);
680 while ( my $data = $sth->fetchrow_hashref ) {
681 push( @results, $data );
683 my $count = scalar(@results);
684 ### countparcelbiblio: $count
687 return ( scalar(@results), @results );
690 =item getparcelinformation
692 ($count, @results) = &getparcelinformation($booksellerid, $code, $date);
694 Looks up all of the received items from the supplier with the given
695 bookseller ID at the given date, for the given code. Ignores cancelled and completed orders.
697 C<$count> is the number of elements in C<@results>. C<@results> is an
698 array of references-to-hash. The keys of each element are fields from
699 the aqorders, biblio, and biblioitems tables of the Koha database.
701 C<@results> is sorted alphabetically by book title.
706 sub getparcelinformation {
708 #gets all orders from a certain supplier, orders them alphabetically
709 my ( $supplierid, $code, $datereceived ) = @_;
710 my $dbh = C4::Context->dbh;
713 if $code; # add % if we search on a given code (otherwise, let him empty)
715 "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\'";
717 if ( C4::Context->preference("IndependantBranches") ) {
718 my $userenv = C4::Context->userenv;
719 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
721 " and (borrowers.branchcode = '"
723 . "' or borrowers.branchcode ='')";
726 $strsth .= " order by aqbasket.basketno";
727 ### parcelinformation : $strsth
728 my $sth = $dbh->prepare($strsth);
729 $sth->execute($supplierid);
730 while ( my $data = $sth->fetchrow_hashref ) {
731 push( @results, $data );
733 my $count = scalar(@results);
734 ### countparcelbiblio: $count
737 return ( scalar(@results), @results );
740 =item getsupplierlistwithlateorders
742 %results = &getsupplierlistwithlateorders;
744 Searches for suppliers with late orders.
749 sub getsupplierlistwithlateorders {
751 my $dbh = C4::Context->dbh;
753 #FIXME NOT quite sure that this operation is valid for DBMs different from Mysql, HOPING so
754 #should be tested with other DBMs
757 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
758 if ( $dbdriver eq "mysql" ) {
759 $strsth = "SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
760 FROM aqorders, aqbasket
761 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
762 WHERE aqorders.basketno = aqbasket.basketno AND
763 (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY) AND (datereceived = '' or datereceived is null))
767 $strsth = "SELECT DISTINCT aqbasket.booksellerid, aqbooksellers.name
768 FROM aqorders, aqbasket
769 LEFT JOIN aqbooksellers ON aqbasket.aqbooksellerid = aqbooksellers.id
770 WHERE aqorders.basketno = aqbasket.basketno AND
771 (closedate < (CURDATE( )-(INTERVAL $delay DAY))) AND (datereceived = '' or datereceived is null))
775 # warn "C4::Acquisition getsupplierlistwithlateorders : ".$strsth;
776 my $sth = $dbh->prepare($strsth);
779 while ( my ( $id, $name ) = $sth->fetchrow ) {
780 $supplierlist{$id} = $name;
782 return %supplierlist;
787 %results = &getlateorders;
789 Searches for suppliers with late orders.
796 my $supplierid = shift;
799 my $dbh = C4::Context->dbh;
801 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
803 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
806 if ( $dbdriver eq "mysql" ) {
807 $strsth = "SELECT aqbasket.basketno,
808 DATE(aqbasket.closedate) as orderdate, aqorders.quantity - IFNULL(aqorders.quantityreceived,0) as quantity, aqorders.rrp as unitpricesupplier,aqorders.ecost as unitpricelib,
809 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp as subtotal, aqbookfund.bookfundname as budget, borrowers.branchcode as branch,
810 aqbooksellers.name as supplier,
811 aqorders.title, biblio.author, biblioitems.publishercode as publisher, biblioitems.publicationyear,
812 DATEDIFF(CURDATE( ),closedate) AS latesince
815 (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber) LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber
816 ) LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber
817 ) LEFT JOIN aqbookfund on aqorderbreakdown.bookfundid = aqbookfund.bookfundid
818 ),(aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
819 WHERE aqorders.basketno = aqbasket.basketno AND (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY))
820 AND ((datereceived = '' OR datereceived is null) OR (aqorders.quantityreceived < aqorders.quantity) ) ";
821 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
822 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'"
825 " AND borrowers.branchcode like \'"
826 . C4::Context->userenv->{branch} . "\'"
827 if ( C4::Context->preference("IndependantBranches")
828 && C4::Context->userenv
829 && C4::Context->userenv->{flags} != 1 );
831 " HAVING quantity<>0 AND unitpricesupplier<>0 AND unitpricelib<>0 ORDER BY latesince,basketno,borrowers.branchcode, supplier ";
834 $strsth = "SELECT aqbasket.basketno,
835 DATE(aqbasket.closedate) as orderdate,
836 aqorders.quantity, aqorders.rrp as unitpricesupplier,aqorders.ecost as unitpricelib, aqorders.quantity * aqorders.rrp as subtotal
837 aqbookfund.bookfundname as budget, borrowers.branchcode as branch,
838 aqbooksellers.name as supplier,
839 biblio.title, biblio.author, biblioitems.publishercode as publisher, biblioitems.publicationyear,
840 (CURDATE - closedate) AS latesince
843 (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber) LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber
844 ) LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber
845 ) LEFT JOIN aqbookfund on aqorderbreakdown.bookfundid = aqbookfund.bookfundid
846 ),(aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
847 WHERE aqorders.basketno = aqbasket.basketno AND (closedate < (CURDATE -(INTERVAL $delay DAY))
848 AND ((datereceived = '' OR datereceived is null) OR (aqorders.quantityreceived < aqorders.quantity) ) ";
849 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
850 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'"
853 " AND borrowers.branchcode like \'"
854 . C4::Context->userenv->{branch} . "\'"
855 if ( C4::Context->preference("IndependantBranches")
856 && C4::Context->userenv->{flags} != 1 );
858 " ORDER BY latesince,basketno,borrowers.branchcode, supplier";
860 warn "C4::Acquisition : getlateorders SQL:" . $strsth;
861 my $sth = $dbh->prepare($strsth);
865 while ( my $data = $sth->fetchrow_hashref ) {
866 $data->{hilighted} = $hilighted if ( $hilighted > 0 );
867 $data->{orderdate} = format_date( $data->{orderdate} );
868 push @results, $data;
869 $hilighted = -$hilighted;
872 return ( scalar(@results), @results );
878 #gets all orders from a certain supplier, orders them alphabetically
880 my $dbh = C4::Context->dbh;
882 my $sth = $dbh->prepare(
883 "Select * from aqorders,biblio,biblioitems where booksellerid=?
884 and (cancelledby is NULL or cancelledby = '')
885 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
886 aqorders.biblioitemnumber and
887 aqorders.quantityreceived>0
888 and aqorders.datereceived >=now()
889 group by aqorders.biblioitemnumber
893 $sth->execute($supid);
894 while ( my $data = $sth->fetchrow_hashref ) {
895 push( @results, $data );
898 return ( scalar(@results), @results );
903 ($count, @results) = &ordersearch($search, $biblionumber, $complete);
907 C<$search> may take one of several forms: if it is an ISBN,
908 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
909 order number, C<&ordersearch> returns orders with that order number
910 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
911 to be a space-separated list of search terms; in this case, all of the
912 terms must appear in the title (matching the beginning of title
915 If C<$complete> is C<yes>, the results will include only completed
916 orders. In any case, C<&ordersearch> ignores cancelled orders.
918 C<&ordersearch> returns an array. C<$count> is the number of elements
919 in C<@results>. C<@results> is an array of references-to-hash with the
938 my ( $search, $id, $biblio, $catview ) = @_;
939 my $dbh = C4::Context->dbh;
940 my @data = split( ' ', $search );
943 @searchterms = ($id);
945 map { push( @searchterms, "$_%", "% $_%" ) } @data;
946 push( @searchterms, $search, $search, $biblio );
950 "SELECT *,biblio.title FROM aqorders,biblioitems,biblio,aqbasket
951 WHERE aqorders.biblioitemnumber = biblioitems.biblioitemnumber AND
952 aqorders.basketno = aqbasket.basketno
953 AND aqbasket.booksellerid = ?
954 AND biblio.biblionumber=aqorders.biblionumber
955 AND ((datecancellationprinted is NULL)
956 OR (datecancellationprinted = '0000-00-00'))
960 map { "(biblio.title like ? or biblio.title like ?)" } @data )
962 . ") OR biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
967 "SELECT *,biblio.title FROM aqorders,biblioitems,biblio,aqbasket
968 WHERE aqorders.biblioitemnumber = biblioitems.biblioitemnumber AND
969 aqorders.basketno = aqbasket.basketno
970 AND biblio.biblionumber=aqorders.biblionumber
971 AND ((datecancellationprinted is NULL)
972 OR (datecancellationprinted = '0000-00-00'))
973 AND (aqorders.quantityreceived < aqorders.quantity OR aqorders.quantityreceived is NULL)
977 map { "(biblio.title like ? OR biblio.title like ?)" } @data )
979 . ") or biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
981 $query .= " GROUP BY aqorders.ordernumber";
982 my $sth = $dbh->prepare($query);
983 $sth->execute(@searchterms);
985 my $sth2 = $dbh->prepare("SELECT * FROM biblio WHERE biblionumber=?");
987 $dbh->prepare("SELECT * FROM aqorderbreakdown WHERE ordernumber=?");
988 while ( my $data = $sth->fetchrow_hashref ) {
989 $sth2->execute( $data->{'biblionumber'} );
990 my $data2 = $sth2->fetchrow_hashref;
991 $data->{'author'} = $data2->{'author'};
992 $data->{'seriestitle'} = $data2->{'seriestitle'};
993 $sth3->execute( $data->{'ordernumber'} );
994 my $data3 = $sth3->fetchrow_hashref;
995 $data->{'branchcode'} = $data3->{'branchcode'};
996 $data->{'bookfundid'} = $data3->{'bookfundid'};
997 push( @results, $data );
1002 return ( scalar(@results), @results );
1006 my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_;
1009 my $total_qtyreceived = 0;
1010 my $total_price = 0;
1012 # don't run the query if there are no parameters (list would be too long for sure !
1013 if ( $title || $author || $name || $from_placed_on || $to_placed_on ) {
1014 my $dbh = C4::Context->dbh;
1016 "select biblio.title,biblio.author,aqorders.basketno,name,aqbasket.creationdate,aqorders.datereceived, aqorders.quantity, aqorders.quantityreceived, aqorders.ecost, aqorders.ordernumber from aqorders,aqbasket,aqbooksellers,biblio";
1017 $query .= ",borrowers "
1018 if ( C4::Context->preference("IndependantBranches") );
1020 " where aqorders.basketno=aqbasket.basketno and aqbasket.booksellerid=aqbooksellers.id and biblio.biblionumber=aqorders.biblionumber ";
1021 $query .= " and aqbasket.authorisedby=borrowers.borrowernumber"
1022 if ( C4::Context->preference("IndependantBranches") );
1023 $query .= " and biblio.title like " . $dbh->quote( "%" . $title . "%" )
1026 " and biblio.author like " . $dbh->quote( "%" . $author . "%" )
1028 $query .= " and name like " . $dbh->quote( "%" . $name . "%" ) if $name;
1029 $query .= " and creationdate >" . $dbh->quote($from_placed_on)
1031 $query .= " and creationdate<" . $dbh->quote($to_placed_on)
1034 if ( C4::Context->preference("IndependantBranches") ) {
1035 my $userenv = C4::Context->userenv;
1036 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1038 " and (borrowers.branchcode = '"
1039 . $userenv->{branch}
1040 . "' or borrowers.branchcode ='')";
1043 $query .= " order by booksellerid";
1044 warn "query histearch: " . $query;
1045 my $sth = $dbh->prepare($query);
1048 while ( my $line = $sth->fetchrow_hashref ) {
1049 $line->{count} = $cnt++;
1050 $line->{toggle} = 1 if $cnt % 2;
1051 push @order_loop, $line;
1052 $line->{creationdate} = format_date( $line->{creationdate} );
1053 $line->{datereceived} = format_date( $line->{datereceived} );
1054 $total_qty += $line->{'quantity'};
1055 $total_qtyreceived += $line->{'quantityreceived'};
1056 $total_price += $line->{'quantity'} * $line->{'ecost'};
1059 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
1070 ($count, @results) = &invoice($booksellerinvoicenumber);
1072 Looks up orders by invoice number.
1074 Returns an array. C<$count> is the number of elements in C<@results>.
1075 C<@results> is an array of references-to-hash; the keys of each
1076 elements are fields from the aqorders, biblio, and biblioitems tables
1077 of the Koha database.
1084 my $dbh = C4::Context->dbh;
1086 my $sth = $dbh->prepare(
1087 "Select * from aqorders,biblio,biblioitems where
1088 booksellerinvoicenumber=?
1089 and biblio.biblionumber=aqorders.biblionumber and biblioitems.biblioitemnumber=
1090 aqorders.biblioitemnumber group by aqorders.ordernumber,aqorders.biblioitemnumber"
1092 $sth->execute($invoice);
1093 while ( my $data = $sth->fetchrow_hashref ) {
1094 push( @results, $data );
1097 return ( scalar(@results), @results );
1102 ($count, @results) = &bookfunds();
1104 Returns a list of all book funds.
1106 C<$count> is the number of elements in C<@results>. C<@results> is an
1107 array of references-to-hash, whose keys are fields from the aqbookfund
1108 and aqbudget tables of the Koha database. Results are ordered
1109 alphabetically by book fund name.
1116 my $dbh = C4::Context->dbh;
1117 my $userenv = C4::Context->userenv;
1118 my $branch = $userenv->{branch};
1121 if ( $branch ne '' ) {
1122 $strsth = "SELECT * FROM aqbookfund,aqbudget WHERE aqbookfund.bookfundid
1123 =aqbudget.bookfundid AND startdate<now() AND enddate>now() AND (aqbookfund.branchcode is null or aqbookfund.branchcode='' or aqbookfund.branchcode= ? )
1124 GROUP BY aqbookfund.bookfundid ORDER BY bookfundname";
1127 $strsth = "SELECT * FROM aqbookfund,aqbudget WHERE aqbookfund.bookfundid
1128 =aqbudget.bookfundid AND startdate<now() AND enddate>now()
1129 GROUP BY aqbookfund.bookfundid ORDER BY bookfundname";
1131 my $sth = $dbh->prepare($strsth);
1132 if ( $branch ne '' ) {
1133 $sth->execute($branch);
1139 while ( my $data = $sth->fetchrow_hashref ) {
1140 push( @results, $data );
1143 return ( scalar(@results), @results );
1146 =item bookfundbreakdown
1148 returns the total comtd & spent for a given bookfund, and a given year
1149 used in acqui-home.pl
1154 sub bookfundbreakdown {
1155 my ( $id, $year, $start, $end ) = @_;
1156 my $dbh = C4::Context->dbh;
1158 # if no start/end dates given defaut to everything
1160 $start = '0000-00-00';
1164 # do a query for spent totals.
1165 my $sth = $dbh->prepare(
1166 "Select quantity,datereceived,freight,unitprice,listprice,ecost,
1167 quantityreceived,subscription
1168 from aqorders left join aqorderbreakdown on
1169 aqorders.ordernumber=aqorderbreakdown.ordernumber
1170 where bookfundid=? and (datecancellationprinted is NULL or
1171 datecancellationprinted='0000-00-00') and
1172 ((datereceived >= ? and datereceived < ?) or
1173 (budgetdate >= ? and budgetdate < ?))"
1175 $sth->execute( $id, $start, $end, $start, $end );
1178 while ( my $data = $sth->fetchrow_hashref ) {
1179 if ( $data->{'subscription'} == 1 ) {
1180 $spent += $data->{'quantity'} * $data->{'unitprice'};
1184 my $leftover = $data->{'quantity'} - $data->{'quantityreceived'};
1185 $spent += ( $data->{'unitprice'} ) * $data->{'quantityreceived'};
1190 # then do a seperate query for commited totals, (pervious single query was
1191 # returning incorrect comitted results.
1193 my $query = "Select quantity,datereceived,freight,unitprice,
1194 listprice,ecost,quantityreceived as qrev,
1195 subscription,title,itemtype,aqorders.biblionumber,
1196 aqorders.booksellerinvoicenumber,
1197 quantity-quantityreceived as tleft,
1198 aqorders.ordernumber as ordnum,entrydate,budgetdate,
1199 booksellerid,aqbasket.basketno
1200 from aqorderbreakdown,aqbasket,aqorders
1201 left join biblioitems on
1202 biblioitems.biblioitemnumber=aqorders.biblioitemnumber
1203 where bookfundid=? and aqorders.ordernumber=aqorderbreakdown.ordernumber and
1204 aqorders.basketno=aqbasket.basketno and
1205 (budgetdate >= ? and budgetdate < ?) and
1206 (datecancellationprinted is NULL or datecancellationprinted='0000-00-00')";
1208 my $sth = $dbh->prepare($query);
1209 $sth->execute( $id, $start, $end );
1214 while ( my $data = $sth->fetchrow_hashref ) {
1215 my $left = $data->{'tleft'};
1216 if ( !$left || $left eq '' ) {
1217 $left = $data->{'quantity'};
1219 if ( $left && $left > 0 ) {
1220 my $subtotal = $left * $data->{'ecost'};
1221 $data->{subtotal} = $subtotal;
1222 $data->{'left'} = $left;
1223 $comtd += $subtotal;
1227 #warn " spent=$spent, comtd=$comtd\n";
1229 return ( $spent, $comtd );
1235 $foreignprice = &curconvert($currency, $localprice);
1237 Converts the price C<$localprice> to foreign currency C<$currency> by
1238 dividing by the exchange rate, and returns the result.
1240 If no exchange rate is found, C<&curconvert> assumes the rate is one
1247 my ( $currency, $price ) = @_;
1248 my $dbh = C4::Context->dbh;
1249 my $sth = $dbh->prepare("Select rate from currency where currency=?");
1250 $sth->execute($currency);
1251 my $cur = ( $sth->fetchrow_array() )[0];
1256 return ( $price / $cur );
1261 ($count, $currencies) = &getcurrencies();
1263 Returns the list of all known currencies.
1265 C<$count> is the number of elements in C<$currencies>. C<$currencies>
1266 is a reference-to-array; its elements are references-to-hash, whose
1267 keys are the fields from the currency table in the Koha database.
1273 my $dbh = C4::Context->dbh;
1274 my $sth = $dbh->prepare("Select * from currency");
1277 while ( my $data = $sth->fetchrow_hashref ) {
1278 push( @results, $data );
1281 return ( scalar(@results), \@results );
1284 =item updatecurrencies
1286 &updatecurrencies($currency, $newrate);
1288 Sets the exchange rate for C<$currency> to be C<$newrate>.
1293 sub updatecurrencies {
1294 my ( $currency, $rate ) = @_;
1295 my $dbh = C4::Context->dbh;
1296 my $sth = $dbh->prepare("update currency set rate=? where currency=?");
1297 $sth->execute( $rate, $currency );
1309 ($count, @results) = &bookseller($searchstring);
1311 Looks up a book seller. C<$searchstring> may be either a book seller
1312 ID, or a string to look for in the book seller's name.
1314 C<$count> is the number of elements in C<@results>. C<@results> is an
1315 array of references-to-hash, whose keys are the fields of of the
1316 aqbooksellers table in the Koha database.
1322 my ($searchstring) = @_;
1323 my $dbh = C4::Context->dbh;
1325 $dbh->prepare("Select * from aqbooksellers where name like ? or id = ?");
1326 $sth->execute( "$searchstring%", $searchstring );
1328 while ( my $data = $sth->fetchrow_hashref ) {
1329 push( @results, $data );
1332 return ( scalar(@results), @results );
1337 ($count, $results) = &breakdown($ordernumber);
1339 Looks up an order by order ID, and returns its breakdown.
1341 C<$count> is the number of elements in C<$results>. C<$results> is a
1342 reference-to-array; its elements are references-to-hash, whose keys
1343 are the fields of the aqorderbreakdown table in the Koha database.
1350 my $dbh = C4::Context->dbh;
1352 $dbh->prepare("Select * from aqorderbreakdown where ordernumber=?");
1355 while ( my $data = $sth->fetchrow_hashref ) {
1356 push( @results, $data );
1359 return ( scalar(@results), \@results );
1364 ($count, @results) = &branches();
1366 Returns a list of all library branches.
1368 C<$count> is the number of elements in C<@results>. C<@results> is an
1369 array of references-to-hash, whose keys are the fields of the branches
1370 table of the Koha database.
1376 my $dbh = C4::Context->dbh;
1378 if ( C4::Context->preference("IndependantBranches")
1379 && ( C4::Context->userenv )
1380 && ( C4::Context->userenv->{flags} != 1 ) )
1382 my $strsth = "Select * from branches ";
1384 " WHERE branchcode = "
1385 . $dbh->quote( C4::Context->userenv->{branch} );
1386 $strsth .= " order by branchname";
1387 warn "C4::Acquisition->branches : " . $strsth;
1388 $sth = $dbh->prepare($strsth);
1391 $sth = $dbh->prepare("Select * from branches order by branchname");
1396 while ( my $data = $sth->fetchrow_hashref ) {
1397 push( @results, $data );
1401 return ( scalar(@results), @results );
1406 &updatesup($bookseller);
1408 Updates the information for a given bookseller. C<$bookseller> is a
1409 reference-to-hash whose keys are the fields of the aqbooksellers table
1410 in the Koha database. It must contain entries for all of the fields.
1411 The entry to modify is determined by C<$bookseller-E<gt>{id}>.
1413 The easiest way to get all of the necessary fields is to look up a
1414 book seller with C<&booksellers>, modify what's necessary, then call
1415 C<&updatesup> with the result.
1422 my $dbh = C4::Context->dbh;
1423 my $sth = $dbh->prepare(
1424 "Update aqbooksellers set
1425 name=?,address1=?,address2=?,address3=?,address4=?,postal=?,
1426 phone=?,fax=?,url=?,contact=?,contpos=?,contphone=?,contfax=?,contaltphone=?,
1427 contemail=?,contnotes=?,active=?,
1428 listprice=?, invoiceprice=?,gstreg=?, listincgst=?,
1429 invoiceincgst=?, specialty=?,discount=?,invoicedisc=?,
1434 $data->{'name'}, $data->{'address1'},
1435 $data->{'address2'}, $data->{'address3'},
1436 $data->{'address4'}, $data->{'postal'},
1437 $data->{'phone'}, $data->{'fax'},
1438 $data->{'url'}, $data->{'contact'},
1439 $data->{'contpos'}, $data->{'contphone'},
1440 $data->{'contfax'}, $data->{'contaltphone'},
1441 $data->{'contemail'}, $data->{'contnotes'},
1442 $data->{'active'}, $data->{'listprice'},
1443 $data->{'invoiceprice'}, $data->{'gstreg'},
1444 $data->{'listincgst'}, $data->{'invoiceincgst'},
1445 $data->{'specialty'}, $data->{'discount'},
1446 $data->{'invoicedisc'}, $data->{'nocalc'},
1447 $data->{'notes'}, $data->{'id'}
1454 $id = &insertsup($bookseller);
1456 Creates a new bookseller. C<$bookseller> is a reference-to-hash whose
1457 keys are the fields of the aqbooksellers table in the Koha database.
1458 All fields must be present.
1460 Returns the ID of the newly-created bookseller.
1467 my $dbh = C4::Context->dbh;
1468 my $sth = $dbh->prepare("Select max(id) from aqbooksellers");
1470 my $data2 = $sth->fetchrow_hashref;
1472 $data2->{'max(id)'}++;
1473 $sth = $dbh->prepare("Insert into aqbooksellers (id) values (?)");
1474 $sth->execute( $data2->{'max(id)'} );
1476 $data->{'id'} = $data2->{'max(id)'};
1478 return ( $data->{'id'} );
1483 ($count, $results) = &getparcels($dbh, $bookseller, $order, $limit);
1485 get a lists of parcels
1486 Returns the count of parcels returned and a pointer on a hash list containing parcel informations as such :
1497 my ( $bookseller, $order, $code, $datefrom, $dateto, $limit ) = @_;
1498 my $dbh = C4::Context->dbh;
1500 "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 ";
1501 $strsth .= "and aqorders.booksellerinvoicenumber like \"$code%\" "
1503 $strsth .= "and datereceived >=" . $dbh->quote($datefrom) . " "
1505 $strsth .= "and datereceived <=" . $dbh->quote($dateto) . " " if ($dateto);
1506 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
1507 $strsth .= "order by $order " if ($order);
1508 $strsth .= " LIMIT 0,$limit" if ($limit);
1509 my $sth = $dbh->prepare($strsth);
1510 ### getparcels: $strsth
1514 while ( my $data2 = $sth->fetchrow_hashref ) {
1515 push @results, $data2;
1519 return ( scalar(@results), @results );
1524 ($count, $results) = &getparcels($dbh, $bookseller, $order, $limit);
1526 get a lists of parcels
1527 Returns the count of parcels returned and a pointer on a hash list containing parcel informations as such :
1538 my ( $bookseller, $order, $code, $datefrom, $dateto, $limit ) = @_;
1539 my $dbh = C4::Context->dbh;
1541 "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 ";
1542 $strsth .= "and aqorders.booksellerinvoicenumber like \"$code%\" "
1544 $strsth .= "and datereceived >=" . $dbh->quote($datefrom) . " "
1546 $strsth .= "and datereceived <=" . $dbh->quote($dateto) . " " if ($dateto);
1547 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
1548 $strsth .= "order by $order " if ($order);
1549 $strsth .= " LIMIT 0,$limit" if ($limit);
1550 my $sth = $dbh->prepare($strsth);
1551 ### getparcels: $strsth
1555 while ( my $data2 = $sth->fetchrow_hashref ) {
1556 push @results, $data2;
1560 return ( scalar(@results), @results );
1563 END { } # module clean-up code here (global destructor)
1572 Koha Developement team <info@koha.org>