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 receiveorder 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, basket and parcels.
60 &GetBasket &NewBasket &CloseBasket
61 &GetPendingOrders &GetOrder &GetOrders
62 &GetOrderNumber &GetLateOrders &NewOrder &DelOrder
64 &ModOrder &ModReceiveOrder
70 =head2 FUNCTIONS ABOUT BASKETS
76 #------------------------------------------------------------#
82 $aqbasket = &GetBasket($basketnumber);
84 get all basket informations in aqbasket for a given basket
87 informations for a given basket returned as a hashref.
96 my ($basketno) = shift;
97 my $dbh = C4::Context->dbh;
100 concat(borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
101 borrowers.branchcode AS branch
103 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
106 my $sth=$dbh->prepare($query);
107 $sth->execute($basketno);
108 return ( $sth->fetchrow_hashref );
111 #------------------------------------------------------------#
117 $basket = &NewBasket();
119 Create a new basket in aqbasket table
125 # FIXME : this function seems to be unused.
128 my ( $booksellerid, $authorisedby ) = @_;
129 my $dbh = C4::Context->dbh;
132 (creationdate,booksellerid,authorisedby)
133 VALUES (now(),'$booksellerid','$authorisedby')
138 #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
139 my $basket = $dbh->{'mysql_insertid'};
143 #------------------------------------------------------------#
149 &CloseBasket($basketno);
151 close a basket (becomes unmodifiable,except for recieves)
159 my $dbh = C4::Context->dbh;
165 my $sth = $dbh->prepare($query);
166 $sth->execute($basketno);
169 #------------------------------------------------------------#
173 =head2 FUNCTIONS ABOUT ORDERS
179 #------------------------------------------------------------#
181 =head3 GetPendingOrders
185 $orders = &GetPendingOrders($booksellerid);
187 Finds pending orders from the bookseller with the given ID. Ignores
188 completed and cancelled orders.
190 C<$orders> is a reference-to-array; each element is a
191 reference-to-hash with the following fields:
195 =item C<authorizedby>
201 These give the value of the corresponding field in the aqorders table
202 of the Koha database.
208 Results are ordered from most to least recent.
212 sub GetPendingOrders {
213 my $supplierid = shift;
214 my $dbh = C4::Context->dbh;
215 my $strsth = "SELECT aqorders.*,aqbasket.*,borrowers.firstname,borrowers.surname
217 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
218 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
220 AND (quantity > quantityreceived OR quantityreceived is NULL)
221 AND datecancellationprinted IS NULL
222 AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL) ";
224 if ( C4::Context->preference("IndependantBranches") ) {
225 my $userenv = C4::Context->userenv;
226 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
228 " and (borrowers.branchcode = '"
230 . "' or borrowers.branchcode ='')";
233 $strsth .= " group by aqbasket.basketno order by aqbasket.basketno";
234 my $sth = $dbh->prepare($strsth);
235 $sth->execute($supplierid);
237 while (my $data = $sth->fetchrow_hashref ) {
238 push @results, $data ;
244 #------------------------------------------------------------#
250 @orders = &GetOrders($basketnumber, $orderby);
252 Looks up the non-cancelled orders (whether received or not) with the given basket
253 number. If C<$booksellerID> is non-empty, only orders from that seller
257 C<&basket> returns a two-element array. C<@orders> is an array of
258 references-to-hash, whose keys are the fields from the aqorders,
259 biblio, and biblioitems tables in the Koha database.
266 my ( $basketno, $orderby ) = @_;
267 my $dbh = C4::Context->dbh;
269 SELECT aqorderbreakdown.*,
273 LEFT JOIN aqorderbreakdown ON
274 aqorders.ordernumber=aqorderbreakdown.ordernumber
276 AND biblio.biblionumber=aqorders.biblionumber
277 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
280 $orderby = "biblio.title" unless $orderby;
281 $query .= " ORDER BY $orderby";
282 my $sth = $dbh->prepare($query);
283 $sth->execute($basketno);
287 while ( my $data = $sth->fetchrow_hashref ) {
288 push @results, $data;
296 my $dbh = C4::Context->dbh;
297 my $sth=$dbh->prepare("Select * from biblio,aqorders left join aqorderbreakdown
298 on aqorders.ordernumber=aqorderbreakdown.ordernumber
299 where aqorders.ordernumber=?
300 and biblio.biblionumber=aqorders.biblionumber");
301 $sth->execute($ordnum);
302 my $data=$sth->fetchrow_hashref;
307 #------------------------------------------------------------#
309 =head3 GetOrderNumber
313 $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
315 Looks up the ordernumber with the given biblionumber
317 Returns the number of this order.
319 =item C<$ordernumber> is the order number.
325 my ( $biblionumber ) = @_;
326 my $dbh = C4::Context->dbh;
333 my $sth = $dbh->prepare($query);
334 $sth->execute( $biblionumber );
336 return $sth->fetchrow;
339 #------------------------------------------------------------#
345 $order = &GetOrder($ordernumber);
347 Looks up an order by order number.
349 Returns a reference-to-hash describing the order. The keys of
350 C<$order> are fields from the biblio, , aqorders, and
351 aqorderbreakdown tables of the Koha database.
359 my $dbh = C4::Context->dbh;
363 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
364 WHERE aqorders.ordernumber=?
365 AND biblio.biblionumber=aqorders.biblionumber
368 my $sth= $dbh->prepare($query);
369 $sth->execute($ordnum);
370 my $data = $sth->fetchrow_hashref;
375 #------------------------------------------------------------#
381 &NewOrder($basket, $biblionumber, $title, $quantity, $listprice,
382 $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp,
383 $ecost, $gst, $budget, $unitprice, $subscription,
384 $booksellerinvoicenumber);
386 Adds a new order to the database. Any argument that isn't described
387 below is the new value of the field with the same name in the aqorders
388 table of the Koha database.
390 C<$ordnum> is a "minimum order number." After adding the new entry to
391 the aqorders table, C<&neworder> finds the first entry in aqorders
392 with order number greater than or equal to C<$ordnum>, and adds an
393 entry to the aqorderbreakdown table, with the order number just found,
394 and the book fund ID of the newly-added order.
396 C<$budget> is effectively ignored.
398 C<$subscription> may be either "yes", or anything else for "no".
406 $basketno, $biblionumber, $title, $quantity,
407 $listprice, $booksellerid, $authorisedby, $notes,
408 $bookfund, $rrp, $ecost,
409 $gst, $budget, $cost, $sub,
410 $purchaseorderno, $sort1, $sort2,$discount,$branch
414 my $year = localtime->year() + 1900;
415 my $month = localtime->mon() + 1; # months starts at 0, add 1
417 if ( !$budget || $budget eq 'now' ) {
421 if ( $sub eq 'yes' ) {
428 # if $basket empty, it's also a new basket, create it
430 $basketno = NewBasket( $booksellerid, $authorisedby );
433 my $dbh = C4::Context->dbh;
436 ( biblionumber,title,basketno,quantity,listprice,notes,
437 rrp,ecost,gst,unitprice,subscription,sort1,sort2,purchaseordernumber,discount,budgetdate,entrydate)
438 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,$budget,now() )
440 my $sth = $dbh->prepare($query);
443 $biblionumber, $title, $basketno, $quantity, $listprice,
444 $notes, $rrp, $ecost, $gst,
445 $cost, $sub, $sort1, $sort2,$purchaseorderno,$discount
449 #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
450 my $ordnum = $dbh->{'mysql_insertid'};
452 INSERT INTO aqorderbreakdown (ordernumber,bookfundid,branchcode)
455 $sth = $dbh->prepare($query);
456 $sth->execute( $ordnum, $bookfund,$branch );
458 return ( $basketno, $ordnum );
461 #------------------------------------------------------------#
467 &ModOrder($title, $ordernumber, $quantity, $listprice,
468 $biblionumber, $basketno, $supplier, $who, $notes,
469 $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget,
470 $unitprice, $booksellerinvoicenumber);
472 Modifies an existing order. Updates the order with order number
473 C<$ordernumber> and biblionumber C<$biblionumber>. All other arguments
474 update the fields with the same name in the aqorders table of the Koha
477 Entries with order number C<$ordernumber> in the aqorderbreakdown
478 table are also updated to the new book fund ID.
486 $title, $ordnum, $quantity, $listprice, $biblionumber,
487 $basketno, $supplier, $who, $notes, $bookfund,
488 $rrp, $ecost, $gst, $budget,
489 $cost, $invoice, $sort1, $sort2,$discount,$branch
492 my $dbh = C4::Context->dbh;
496 quantity=?,listprice=?,basketno=?,
497 rrp=?,ecost=?,unitprice=?,purchaseordernumber=?,gst=?,
498 notes=?,sort1=?, sort2=?,discount=?
499 WHERE ordernumber=? AND biblionumber=?
501 my $sth = $dbh->prepare($query);
503 $title, $quantity, $listprice, $basketno, $rrp,
504 $ecost, $cost, $invoice, $gst, $notes, $sort1,
505 $sort2, $discount,$ordnum, $biblionumber
509 REPLACE aqorderbreakdown
510 SET ordernumber=?, bookfundid=?, branchcode=?
512 $sth = $dbh->prepare($query);
514 $sth->execute( $ordnum,$bookfund, $branch );
519 #------------------------------------------------------------#
524 #------------------------------------------------------------#
526 =head3 ModReceiveOrder
530 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
531 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
532 $freight, $bookfund, $rrp);
534 Updates an order, to reflect the fact that it was received, at least
535 in part. All arguments not mentioned below update the fields with the
536 same name in the aqorders table of the Koha database.
538 Updates the order with bibilionumber C<$biblionumber> and ordernumber
547 sub ModReceiveOrder {
549 $biblionumber, $ordnum, $quantrec, $cost,
550 $invoiceno, $freight, $rrp, $listprice,$input
553 my $dbh = C4::Context->dbh;
556 SET quantityreceived=quantityreceived+?,datereceived=now(),booksellerinvoicenumber=?,
557 unitprice=?,freight=?,rrp=?,listprice=?
558 WHERE biblionumber=? AND ordernumber=?
560 my $sth = $dbh->prepare($query);
561 my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber );
563 ModStatus( $suggestionid, 'AVAILABLE', '', $biblionumber,$input );
565 $sth->execute( $quantrec, $invoiceno, $cost, $freight, $rrp, $listprice, $biblionumber,
572 #------------------------------------------------------------#
578 &DelOrder($biblionumber, $ordernumber);
580 Cancel the order with the given order and biblio numbers. It does not
581 delete any entries in the aqorders table, it merely marks them as
589 my ( $biblionumber, $ordnum,$user ) = @_;
590 my $dbh = C4::Context->dbh;
593 SET datecancellationprinted=now(), cancelledby=?
594 WHERE biblionumber=? AND ordernumber=?
596 my $sth = $dbh->prepare($query);
597 $sth->execute( $user,$biblionumber, $ordnum );
606 =head2 FUNCTIONS ABOUT PARCELS
612 #------------------------------------------------------------#
618 @results = &GetParcel($booksellerid, $code, $date);
620 Looks up all of the received items from the supplier with the given
621 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
623 C<@results> is an array of references-to-hash. The keys of each element are fields from
624 the aqorders, biblio tables of the Koha database.
626 C<@results> is sorted alphabetically by book title.
631 ## This routine is not used will be cleaned
634 #gets all orders from a certain supplier, orders them alphabetically
635 my ( $supplierid, $invoice, $datereceived ) = @_;
636 my $dbh = C4::Context->dbh;
638 $invoice .= '%' if $invoice; # add % if we search on a given invoice
647 aqorders.ordernumber,
649 aqorders.quantityreceived,
654 FROM aqorders,aqbasket
655 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
656 WHERE aqbasket.basketno=aqorders.basketno
657 AND aqbasket.booksellerid=?
658 AND (aqorders.datereceived= \"$datereceived\" OR aqorders.datereceived is NULL)";
659 $strsth.= " AND aqorders.purchaseordernumber LIKE \"$invoice\"" if $invoice ne "%";
661 if ( C4::Context->preference("IndependantBranches") ) {
662 my $userenv = C4::Context->userenv;
663 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
665 " and (borrowers.branchcode = '"
667 . "' or borrowers.branchcode ='')";
670 $strsth .= " order by aqbasket.basketno";
671 ### parcelinformation : $strsth
672 my $sth = $dbh->prepare($strsth);
673 $sth->execute($supplierid);
674 while ( my $data = $sth->fetchrow_hashref ) {
675 push @results, $data ;
677 ### countparcelbiblio: $count
683 #------------------------------------------------------------#
689 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
690 get a lists of parcels.
695 is the bookseller this function has to get parcels.
698 To know on what criteria the results list has to be ordered.
701 is the booksellerinvoicenumber.
703 =item $datefrom & $dateto
704 to know on what date this function has to filter its search.
707 a pointer on a hash list containing parcel informations as such :
713 =item Number of biblio
715 =item Number of items
720 ### This routine is not used will be cleaned
722 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
723 my $dbh = C4::Context->dbh;
725 SELECT aqorders.booksellerinvoicenumber,
727 count(DISTINCT biblionumber) AS biblio,
728 sum(quantity) AS itemsexpected,
729 sum(quantityreceived) AS itemsreceived
730 FROM aqorders, aqbasket
731 WHERE aqbasket.basketno = aqorders.basketno
732 AND aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL
735 $strsth .= "and aqorders.booksellerinvoicenumber like \"$code%\" " if ($code);
737 $strsth .= "and datereceived >=" . $dbh->quote($datefrom) . " " if ($datefrom);
739 $strsth .= "and datereceived <=" . $dbh->quote($dateto) . " " if ($dateto);
741 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
742 $strsth .= "order by $order " if ($order);
743 my $sth = $dbh->prepare($strsth);
748 while ( my $data2 = $sth->fetchrow_hashref ) {
749 push @results, $data2;
756 #------------------------------------------------------------#
762 @results = &GetLateOrders;
764 Searches for bookseller with late orders.
767 the table of supplier with late issues. This table is full of hashref.
774 ## requirse fixing for KOHA 3 API. Currently does not return publisher
776 my $supplierid = shift;
779 my $dbh = C4::Context->dbh;
781 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
783 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
786 if ( $dbdriver eq "mysql" ) {
788 SELECT aqbasket.basketno,
789 DATE(aqbasket.closedate) AS orderdate,
790 aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
791 aqorders.rrp AS unitpricesupplier,
792 aqorders.ecost AS unitpricelib,
793 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
794 aqbookfund.bookfundname AS budget,
795 borrowers.branchcode AS branch,
796 aqbooksellers.name AS supplier,
800 DATEDIFF(CURDATE( ),closedate) AS latesince
802 (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber)
804 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber = aqorderbreakdown.ordernumber)
805 LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
806 (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber)
807 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
808 WHERE aqorders.basketno = aqbasket.basketno
809 AND (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY))
810 AND ((datereceived = '' OR datereceived is null)
811 OR (aqorders.quantityreceived < aqorders.quantity) )
813 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
814 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'"
817 " AND borrowers.branchcode like \'"
818 . C4::Context->userenv->{branch} . "\'"
819 if ( C4::Context->preference("IndependantBranches")
820 && C4::Context->userenv
821 && C4::Context->userenv->{flags} != 1 );
822 $strsth .=" HAVING quantity<>0
823 AND unitpricesupplier<>0
825 ORDER BY latesince,basketno,borrowers.branchcode, supplier
830 SELECT aqbasket.basketno,
831 DATE(aqbasket.closedate) AS orderdate,
832 aqorders.quantity, aqorders.rrp AS unitpricesupplier,
833 aqorders.ecost as unitpricelib,
834 aqorders.quantity * aqorders.rrp AS subtotal
835 aqbookfund.bookfundname AS budget,
836 borrowers.branchcode AS branch,
837 aqbooksellers.name AS supplier,
841 (CURDATE - closedate) AS latesince
843 (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber)
845 LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber)
846 LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
847 (aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
848 WHERE aqorders.basketno = aqbasket.basketno
849 AND (closedate < (CURDATE -(INTERVAL $delay DAY))
850 AND ((datereceived = '' OR datereceived is null)
851 OR (aqorders.quantityreceived < aqorders.quantity) ) ";
852 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
854 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'" if ($branch);
855 $strsth .=" AND borrowers.branchcode like \'". C4::Context->userenv->{branch} . "\'"
856 if (C4::Context->preference("IndependantBranches") && C4::Context->userenv->{flags} != 1 );
857 $strsth .=" ORDER BY latesince,basketno,borrowers.branchcode, supplier";
859 my $sth = $dbh->prepare($strsth);
863 while ( my $data = $sth->fetchrow_hashref ) {
864 $data->{hilighted} = $hilighted if ( $hilighted > 0 );
865 $data->{orderdate} = format_date( $data->{orderdate} );
866 push @results, $data;
867 $hilighted = -$hilighted;
873 #------------------------------------------------------------#
879 (\@order_loop, $total_qty, $total_price, $total_qtyreceived)=&GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on )
881 this function get the search history.
888 my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_;
891 my $total_qtyreceived = 0;
894 # don't run the query if there are no parameters (list would be too long for sure !)
895 if ( $title || $author || $name || $from_placed_on || $to_placed_on ) {
896 my $dbh = C4::Context->dbh;
902 name,aqbasket.creationdate,
903 aqorders.datereceived,
905 aqorders.quantityreceived,
908 FROM aqorders,aqbasket,aqbooksellers,biblio";
910 $query .= ",borrowers "
911 if ( C4::Context->preference("IndependantBranches") );
914 WHERE aqorders.basketno=aqbasket.basketno
915 AND aqbasket.booksellerid=aqbooksellers.id
916 AND biblio.biblionumber=aqorders.biblionumber ";
918 $query .= " AND aqbasket.authorisedby=borrowers.borrowernumber"
919 if ( C4::Context->preference("IndependantBranches") );
921 $query .= " AND biblio.title LIKE " . $dbh->quote( "%" . $title . "%" )
925 " AND biblio.author LIKE " . $dbh->quote( "%" . $author . "%" )
928 $query .= " AND name LIKE " . $dbh->quote( "%" . $name . "%" ) if $name;
930 $query .= " AND creationdate >" . $dbh->quote($from_placed_on)
933 $query .= " AND creationdate<" . $dbh->quote($to_placed_on)
936 if ( C4::Context->preference("IndependantBranches") ) {
937 my $userenv = C4::Context->userenv;
938 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
940 " AND (borrowers.branchcode = '"
942 . "' OR borrowers.branchcode ='')";
945 $query .= " ORDER BY booksellerid";
946 my $sth = $dbh->prepare($query);
949 while ( my $line = $sth->fetchrow_hashref ) {
950 $line->{count} = $cnt++;
951 $line->{toggle} = 1 if $cnt % 2;
952 push @order_loop, $line;
953 $line->{creationdate} = format_date( $line->{creationdate} );
954 $line->{datereceived} = format_date( $line->{datereceived} );
955 $total_qty += $line->{'quantity'};
956 $total_qtyreceived += $line->{'quantityreceived'};
957 $total_price += $line->{'quantity'} * $line->{'ecost'};
960 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
963 #------------------------------------------------------------#
969 ($count, @results) = &bookseller($searchstring);
971 Looks up a book seller. C<$searchstring> may be either a book seller
972 ID, or a string to look for in the book seller's name.
974 C<$count> is the number of elements in C<@results>. C<@results> is an
975 array of references-to-hash, whose keys are the fields of of the
976 aqbooksellers table in the Koha database.
983 my ($searchstring) = @_;
984 my $dbh = C4::Context->dbh;
986 $dbh->prepare("Select * from aqbooksellers where name like ? or id = ?");
987 $sth->execute( "$searchstring%", $searchstring );
989 while ( my $data = $sth->fetchrow_hashref ) {
990 push( @results, $data );
993 return ( scalar(@results), @results );
996 END { } # module clean-up code here (global destructor)
1006 Koha Developement team <info@koha.org>