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
69 =head2 FUNCTIONS ABOUT BASKETS
75 #------------------------------------------------------------#
81 $aqbasket = &GetBasket($basketnumber);
83 get all basket informations in aqbasket for a given basket
86 informations for a given basket returned as a hashref.
95 my ($basketno) = shift;
96 my $dbh = C4::Context->dbh;
99 concat(borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
100 borrowers.branchcode AS branch
102 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
105 my $sth=$dbh->prepare($query);
106 $sth->execute($basketno);
107 return ( $sth->fetchrow_hashref );
110 #------------------------------------------------------------#
116 $basket = &NewBasket();
118 Create a new basket in aqbasket table
124 # FIXME : this function seems to be unused.
127 my ( $booksellerid, $authorisedby ) = @_;
128 my $dbh = C4::Context->dbh;
131 (creationdate,booksellerid,authorisedby)
132 VALUES (now(),'$booksellerid','$authorisedby')
137 #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
138 my $basket = $dbh->{'mysql_insertid'};
142 #------------------------------------------------------------#
148 &CloseBasket($basketno);
150 close a basket (becomes unmodifiable,except for recieves)
158 my $dbh = C4::Context->dbh;
164 my $sth = $dbh->prepare($query);
165 $sth->execute($basketno);
168 #------------------------------------------------------------#
172 =head2 FUNCTIONS ABOUT ORDERS
178 #------------------------------------------------------------#
180 =head3 GetPendingOrders
184 $orders = &GetPendingOrders($booksellerid);
186 Finds pending orders from the bookseller with the given ID. Ignores
187 completed and cancelled orders.
189 C<$orders> is a reference-to-array; each element is a
190 reference-to-hash with the following fields:
194 =item C<authorizedby>
200 These give the value of the corresponding field in the aqorders table
201 of the Koha database.
207 Results are ordered from most to least recent.
211 sub GetPendingOrders {
212 my $supplierid = shift;
213 my $dbh = C4::Context->dbh;
214 my $strsth = "SELECT aqorders.*,aqbasket.*,borrowers.firstname,borrowers.surname
216 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
217 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
219 AND (quantity > quantityreceived OR quantityreceived is NULL)
220 AND datecancellationprinted IS NULL
221 AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL) ";
223 if ( C4::Context->preference("IndependantBranches") ) {
224 my $userenv = C4::Context->userenv;
225 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
227 " and (borrowers.branchcode = '"
229 . "' or borrowers.branchcode ='')";
232 $strsth .= " group by aqbasket.basketno order by aqbasket.basketno";
233 my $sth = $dbh->prepare($strsth);
234 $sth->execute($supplierid);
236 while (my $data = $sth->fetchrow_hashref ) {
237 push @results, $data ;
243 #------------------------------------------------------------#
249 @orders = &GetOrders($basketnumber, $orderby);
251 Looks up the non-cancelled orders (whether received or not) with the given basket
252 number. If C<$booksellerID> is non-empty, only orders from that seller
256 C<&basket> returns a two-element array. C<@orders> is an array of
257 references-to-hash, whose keys are the fields from the aqorders,
258 biblio, and biblioitems tables in the Koha database.
265 my ( $basketno, $orderby ) = @_;
266 my $dbh = C4::Context->dbh;
268 SELECT aqorderbreakdown.*,
272 LEFT JOIN aqorderbreakdown ON
273 aqorders.ordernumber=aqorderbreakdown.ordernumber
275 AND biblio.biblionumber=aqorders.biblionumber
276 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
279 $orderby = "biblio.title" unless $orderby;
280 $query .= " ORDER BY $orderby";
281 my $sth = $dbh->prepare($query);
282 $sth->execute($basketno);
286 while ( my $data = $sth->fetchrow_hashref ) {
287 push @results, $data;
295 my $dbh = C4::Context->dbh;
296 my $sth=$dbh->prepare("Select * from biblio,aqorders left join aqorderbreakdown
297 on aqorders.ordernumber=aqorderbreakdown.ordernumber
298 where aqorders.ordernumber=?
299 and biblio.biblionumber=aqorders.biblionumber");
300 $sth->execute($ordnum);
301 my $data=$sth->fetchrow_hashref;
306 #------------------------------------------------------------#
308 =head3 GetOrderNumber
312 $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
314 Looks up the ordernumber with the given biblionumber
316 Returns the number of this order.
318 =item C<$ordernumber> is the order number.
324 my ( $biblionumber ) = @_;
325 my $dbh = C4::Context->dbh;
332 my $sth = $dbh->prepare($query);
333 $sth->execute( $biblionumber );
335 return $sth->fetchrow;
338 #------------------------------------------------------------#
344 $order = &GetOrder($ordernumber);
346 Looks up an order by order number.
348 Returns a reference-to-hash describing the order. The keys of
349 C<$order> are fields from the biblio, , aqorders, and
350 aqorderbreakdown tables of the Koha database.
358 my $dbh = C4::Context->dbh;
362 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
363 WHERE aqorders.ordernumber=?
364 AND biblio.biblionumber=aqorders.biblionumber
367 my $sth= $dbh->prepare($query);
368 $sth->execute($ordnum);
369 my $data = $sth->fetchrow_hashref;
374 #------------------------------------------------------------#
380 &NewOrder($basket, $biblionumber, $title, $quantity, $listprice,
381 $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp,
382 $ecost, $gst, $budget, $unitprice, $subscription,
383 $booksellerinvoicenumber);
385 Adds a new order to the database. Any argument that isn't described
386 below is the new value of the field with the same name in the aqorders
387 table of the Koha database.
389 C<$ordnum> is a "minimum order number." After adding the new entry to
390 the aqorders table, C<&neworder> finds the first entry in aqorders
391 with order number greater than or equal to C<$ordnum>, and adds an
392 entry to the aqorderbreakdown table, with the order number just found,
393 and the book fund ID of the newly-added order.
395 C<$budget> is effectively ignored.
397 C<$subscription> may be either "yes", or anything else for "no".
405 $basketno, $biblionumber, $title, $quantity,
406 $listprice, $booksellerid, $authorisedby, $notes,
407 $bookfund, $rrp, $ecost,
408 $gst, $budget, $cost, $sub,
409 $purchaseorderno, $sort1, $sort2,$discount,$branch
413 my $year = localtime->year() + 1900;
414 my $month = localtime->mon() + 1; # months starts at 0, add 1
416 if ( !$budget || $budget eq 'now' ) {
420 if ( $sub eq 'yes' ) {
427 # if $basket empty, it's also a new basket, create it
429 $basketno = NewBasket( $booksellerid, $authorisedby );
432 my $dbh = C4::Context->dbh;
435 ( biblionumber,title,basketno,quantity,listprice,notes,
436 rrp,ecost,gst,unitprice,subscription,sort1,sort2,purchaseordernumber,discount,budgetdate,entrydate)
437 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,$budget,now() )
439 my $sth = $dbh->prepare($query);
442 $biblionumber, $title, $basketno, $quantity, $listprice,
443 $notes, $rrp, $ecost, $gst,
444 $cost, $sub, $sort1, $sort2,$purchaseorderno,$discount
448 #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
449 my $ordnum = $dbh->{'mysql_insertid'};
451 INSERT INTO aqorderbreakdown (ordernumber,bookfundid,branchcode)
454 $sth = $dbh->prepare($query);
455 $sth->execute( $ordnum, $bookfund,$branch );
457 return ( $basketno, $ordnum );
460 #------------------------------------------------------------#
466 &ModOrder($title, $ordernumber, $quantity, $listprice,
467 $biblionumber, $basketno, $supplier, $who, $notes,
468 $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget,
469 $unitprice, $booksellerinvoicenumber);
471 Modifies an existing order. Updates the order with order number
472 C<$ordernumber> and biblionumber C<$biblionumber>. All other arguments
473 update the fields with the same name in the aqorders table of the Koha
476 Entries with order number C<$ordernumber> in the aqorderbreakdown
477 table are also updated to the new book fund ID.
485 $title, $ordnum, $quantity, $listprice, $biblionumber,
486 $basketno, $supplier, $who, $notes, $bookfund,
487 $rrp, $ecost, $gst, $budget,
488 $cost, $invoice, $sort1, $sort2,$discount,$branch
491 my $dbh = C4::Context->dbh;
495 quantity=?,listprice=?,basketno=?,
496 rrp=?,ecost=?,unitprice=?,purchaseordernumber=?,gst=?,
497 notes=?,sort1=?, sort2=?,discount=?
498 WHERE ordernumber=? AND biblionumber=?
500 my $sth = $dbh->prepare($query);
502 $title, $quantity, $listprice, $basketno, $rrp,
503 $ecost, $cost, $invoice, $gst, $notes, $sort1,
504 $sort2, $discount,$ordnum, $biblionumber
508 REPLACE aqorderbreakdown
509 SET ordernumber=?, bookfundid=?, branchcode=?
511 $sth = $dbh->prepare($query);
513 $sth->execute( $ordnum,$bookfund, $branch );
518 #------------------------------------------------------------#
523 #------------------------------------------------------------#
525 =head3 ModReceiveOrder
529 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
530 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
531 $freight, $bookfund, $rrp);
533 Updates an order, to reflect the fact that it was received, at least
534 in part. All arguments not mentioned below update the fields with the
535 same name in the aqorders table of the Koha database.
537 Updates the order with bibilionumber C<$biblionumber> and ordernumber
546 sub ModReceiveOrder {
548 $biblionumber, $ordnum, $quantrec, $cost,
549 $invoiceno, $freight, $rrp, $listprice,$input
552 my $dbh = C4::Context->dbh;
555 SET quantityreceived=quantityreceived+?,datereceived=now(),booksellerinvoicenumber=?,
556 unitprice=?,freight=?,rrp=?,listprice=?
557 WHERE biblionumber=? AND ordernumber=?
559 my $sth = $dbh->prepare($query);
560 my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber );
562 ModStatus( $suggestionid, 'AVAILABLE', '', $biblionumber,$input );
564 $sth->execute( $quantrec, $invoiceno, $cost, $freight, $rrp, $listprice, $biblionumber,
571 #------------------------------------------------------------#
577 &DelOrder($biblionumber, $ordernumber);
579 Cancel the order with the given order and biblio numbers. It does not
580 delete any entries in the aqorders table, it merely marks them as
588 my ( $biblionumber, $ordnum,$user ) = @_;
589 my $dbh = C4::Context->dbh;
592 SET datecancellationprinted=now(), cancelledby=?
593 WHERE biblionumber=? AND ordernumber=?
595 my $sth = $dbh->prepare($query);
596 $sth->execute( $user,$biblionumber, $ordnum );
605 =head2 FUNCTIONS ABOUT PARCELS
611 #------------------------------------------------------------#
617 @results = &GetParcel($booksellerid, $code, $date);
619 Looks up all of the received items from the supplier with the given
620 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
622 C<@results> is an array of references-to-hash. The keys of each element are fields from
623 the aqorders, biblio tables of the Koha database.
625 C<@results> is sorted alphabetically by book title.
630 ## This routine is not used will be cleaned
633 #gets all orders from a certain supplier, orders them alphabetically
634 my ( $supplierid, $invoice, $datereceived ) = @_;
635 my $dbh = C4::Context->dbh;
637 $invoice .= '%' if $invoice; # add % if we search on a given invoice
646 aqorders.ordernumber,
648 aqorders.quantityreceived,
653 FROM aqorders,aqbasket
654 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
655 WHERE aqbasket.basketno=aqorders.basketno
656 AND aqbasket.booksellerid=?
657 AND (aqorders.datereceived= \"$datereceived\" OR aqorders.datereceived is NULL)";
658 $strsth.= " AND aqorders.purchaseordernumber LIKE \"$invoice\"" if $invoice ne "%";
660 if ( C4::Context->preference("IndependantBranches") ) {
661 my $userenv = C4::Context->userenv;
662 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
664 " and (borrowers.branchcode = '"
666 . "' or borrowers.branchcode ='')";
669 $strsth .= " order by aqbasket.basketno";
670 ### parcelinformation : $strsth
671 my $sth = $dbh->prepare($strsth);
672 $sth->execute($supplierid);
673 while ( my $data = $sth->fetchrow_hashref ) {
674 push @results, $data ;
676 ### countparcelbiblio: $count
682 #------------------------------------------------------------#
688 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
689 get a lists of parcels.
694 is the bookseller this function has to get parcels.
697 To know on what criteria the results list has to be ordered.
700 is the booksellerinvoicenumber.
702 =item $datefrom & $dateto
703 to know on what date this function has to filter its search.
706 a pointer on a hash list containing parcel informations as such :
712 =item Number of biblio
714 =item Number of items
719 ### This routine is not used will be cleaned
721 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
722 my $dbh = C4::Context->dbh;
724 SELECT aqorders.booksellerinvoicenumber,
726 count(DISTINCT biblionumber) AS biblio,
727 sum(quantity) AS itemsexpected,
728 sum(quantityreceived) AS itemsreceived
729 FROM aqorders, aqbasket
730 WHERE aqbasket.basketno = aqorders.basketno
731 AND aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL
734 $strsth .= "and aqorders.booksellerinvoicenumber like \"$code%\" " if ($code);
736 $strsth .= "and datereceived >=" . $dbh->quote($datefrom) . " " if ($datefrom);
738 $strsth .= "and datereceived <=" . $dbh->quote($dateto) . " " if ($dateto);
740 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
741 $strsth .= "order by $order " if ($order);
742 my $sth = $dbh->prepare($strsth);
747 while ( my $data2 = $sth->fetchrow_hashref ) {
748 push @results, $data2;
755 #------------------------------------------------------------#
761 @results = &GetLateOrders;
763 Searches for bookseller with late orders.
766 the table of supplier with late issues. This table is full of hashref.
773 ## requirse fixing for KOHA 3 API. Currently does not return publisher
775 my $supplierid = shift;
778 my $dbh = C4::Context->dbh;
780 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
782 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
785 if ( $dbdriver eq "mysql" ) {
787 SELECT aqbasket.basketno,
788 DATE(aqbasket.closedate) AS orderdate,
789 aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
790 aqorders.rrp AS unitpricesupplier,
791 aqorders.ecost AS unitpricelib,
792 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
793 aqbookfund.bookfundname AS budget,
794 borrowers.branchcode AS branch,
795 aqbooksellers.name AS supplier,
799 DATEDIFF(CURDATE( ),closedate) AS latesince
801 (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber)
803 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber = aqorderbreakdown.ordernumber)
804 LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
805 (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber)
806 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
807 WHERE aqorders.basketno = aqbasket.basketno
808 AND (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY))
809 AND ((datereceived = '' OR datereceived is null)
810 OR (aqorders.quantityreceived < aqorders.quantity) )
812 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
813 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'"
816 " AND borrowers.branchcode like \'"
817 . C4::Context->userenv->{branch} . "\'"
818 if ( C4::Context->preference("IndependantBranches")
819 && C4::Context->userenv
820 && C4::Context->userenv->{flags} != 1 );
821 $strsth .=" HAVING quantity<>0
822 AND unitpricesupplier<>0
824 ORDER BY latesince,basketno,borrowers.branchcode, supplier
829 SELECT aqbasket.basketno,
830 DATE(aqbasket.closedate) AS orderdate,
831 aqorders.quantity, aqorders.rrp AS unitpricesupplier,
832 aqorders.ecost as unitpricelib,
833 aqorders.quantity * aqorders.rrp AS subtotal
834 aqbookfund.bookfundname AS budget,
835 borrowers.branchcode AS branch,
836 aqbooksellers.name AS supplier,
840 (CURDATE - closedate) AS latesince
842 (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.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
848 AND (closedate < (CURDATE -(INTERVAL $delay DAY))
849 AND ((datereceived = '' OR datereceived is null)
850 OR (aqorders.quantityreceived < aqorders.quantity) ) ";
851 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
853 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'" if ($branch);
854 $strsth .=" AND borrowers.branchcode like \'". C4::Context->userenv->{branch} . "\'"
855 if (C4::Context->preference("IndependantBranches") && C4::Context->userenv->{flags} != 1 );
856 $strsth .=" ORDER BY latesince,basketno,borrowers.branchcode, supplier";
858 my $sth = $dbh->prepare($strsth);
862 while ( my $data = $sth->fetchrow_hashref ) {
863 $data->{hilighted} = $hilighted if ( $hilighted > 0 );
864 $data->{orderdate} = format_date( $data->{orderdate} );
865 push @results, $data;
866 $hilighted = -$hilighted;
872 #------------------------------------------------------------#
878 (\@order_loop, $total_qty, $total_price, $total_qtyreceived)=&GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on )
880 this function get the search history.
887 my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_;
890 my $total_qtyreceived = 0;
893 # don't run the query if there are no parameters (list would be too long for sure !)
894 if ( $title || $author || $name || $from_placed_on || $to_placed_on ) {
895 my $dbh = C4::Context->dbh;
901 name,aqbasket.creationdate,
902 aqorders.datereceived,
904 aqorders.quantityreceived,
907 FROM aqorders,aqbasket,aqbooksellers,biblio";
909 $query .= ",borrowers "
910 if ( C4::Context->preference("IndependantBranches") );
913 WHERE aqorders.basketno=aqbasket.basketno
914 AND aqbasket.booksellerid=aqbooksellers.id
915 AND biblio.biblionumber=aqorders.biblionumber ";
917 $query .= " AND aqbasket.authorisedby=borrowers.borrowernumber"
918 if ( C4::Context->preference("IndependantBranches") );
920 $query .= " AND biblio.title LIKE " . $dbh->quote( "%" . $title . "%" )
924 " AND biblio.author LIKE " . $dbh->quote( "%" . $author . "%" )
927 $query .= " AND name LIKE " . $dbh->quote( "%" . $name . "%" ) if $name;
929 $query .= " AND creationdate >" . $dbh->quote($from_placed_on)
932 $query .= " AND creationdate<" . $dbh->quote($to_placed_on)
935 if ( C4::Context->preference("IndependantBranches") ) {
936 my $userenv = C4::Context->userenv;
937 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
939 " AND (borrowers.branchcode = '"
941 . "' OR borrowers.branchcode ='')";
944 $query .= " ORDER BY booksellerid";
945 my $sth = $dbh->prepare($query);
948 while ( my $line = $sth->fetchrow_hashref ) {
949 $line->{count} = $cnt++;
950 $line->{toggle} = 1 if $cnt % 2;
951 push @order_loop, $line;
952 $line->{creationdate} = format_date( $line->{creationdate} );
953 $line->{datereceived} = format_date( $line->{datereceived} );
954 $total_qty += $line->{'quantity'};
955 $total_qtyreceived += $line->{'quantityreceived'};
956 $total_price += $line->{'quantity'} * $line->{'ecost'};
959 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
962 END { } # module clean-up code here (global destructor)
972 Koha Developement team <info@koha.org>