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
24 use C4::Dates qw(format_date);
29 use vars qw($VERSION @ISA @EXPORT);
32 # set the version for version checking
37 &GetBasket &NewBasket &CloseBasket
38 &GetPendingOrders &GetOrder &GetOrders
39 &GetOrderNumber &GetLateOrders &NewOrder &DelOrder
40 &SearchOrder &GetHistory &GetRecentAcqui
41 &ModOrder &ModReceiveOrder &ModOrderBiblioNumber
42 &GetParcels &GetParcel
46 # used in receiveorder subroutine
47 # to provide library specific handling
48 my $library_name = C4::Context->preference("LibraryName");
52 C4::Acquisition - Koha functions for dealing with orders and acquisitions
60 The functions in this module deal with acquisitions, managing book
61 orders, basket and parcels.
65 =head2 FUNCTIONS ABOUT BASKETS
71 $aqbasket = &GetBasket($basketnumber);
73 get all basket informations in aqbasket for a given basket
76 informations for a given basket returned as a hashref.
84 my $dbh = C4::Context->dbh;
87 concat( b.firstname,' ',b.surname) AS authorisedbyname,
88 b.branchcode AS branch
90 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
93 my $sth=$dbh->prepare($query);
94 $sth->execute($basketno);
95 my $basket = $sth->fetchrow_hashref;
99 #------------------------------------------------------------#
105 $basket = &NewBasket();
107 Create a new basket in aqbasket table
113 # FIXME : this function seems to be unused.
116 my ( $booksellerid, $authorisedby ) = @_;
117 my $dbh = C4::Context->dbh;
120 (creationdate,booksellerid,authorisedby)
121 VALUES (now(),'$booksellerid','$authorisedby')
126 #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
127 my $basket = $dbh->{'mysql_insertid'};
131 #------------------------------------------------------------#
137 &CloseBasket($basketno);
139 close a basket (becomes unmodifiable,except for recieves)
147 my $dbh = C4::Context->dbh;
153 my $sth = $dbh->prepare($query);
154 $sth->execute($basketno);
157 #------------------------------------------------------------#
159 =head2 FUNCTIONS ABOUT ORDERS
163 #------------------------------------------------------------#
165 =head3 GetPendingOrders
169 $orders = &GetPendingOrders($booksellerid, $grouped);
171 Finds pending orders from the bookseller with the given ID. Ignores
172 completed and cancelled orders.
174 C<$orders> is a reference-to-array; each element is a
175 reference-to-hash with the following fields:
176 C<$grouped> is a boolean that, if set to 1 will group all order lines of the same basket
177 in a single result line
181 =item C<authorizedby>
187 These give the value of the corresponding field in the aqorders table
188 of the Koha database.
194 Results are ordered from most to least recent.
198 sub GetPendingOrders {
199 my ($supplierid,$grouped, $closed) = @_;
200 my $dbh = C4::Context->dbh;
202 SELECT ".($grouped?"count(*),":"")."aqbasket.basketno,
203 surname,firstname,aqorders.*,
204 aqbasket.closedate, aqbasket.creationdate
206 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
207 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
209 AND datecancellationprinted IS NULL
213 AND (quantity > quantityreceived OR quantityreceived is NULL)
214 AND closedate IS NOT NULL ";
216 $strsth .= "AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL)";
218 ## FIXME Why 180 days ???
219 my @query_params = ( $supplierid );
220 if ( C4::Context->preference("IndependantBranches") ) {
221 my $userenv = C4::Context->userenv;
222 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
223 $strsth .= " and (borrowers.branchcode = ?
224 or borrowers.branchcode = '')";
225 push @query_params, $userenv->{branch};
228 $strsth .= " group by aqbasket.basketno" if $grouped;
229 $strsth .= " order by aqbasket.basketno";
231 my $sth = $dbh->prepare($strsth);
232 $sth->execute( @query_params );
233 my $results = $sth->fetchall_arrayref({});
238 #------------------------------------------------------------#
244 @orders = &GetOrders($basketnumber, $orderby);
246 Looks up the pending (non-cancelled) orders with the given basket
247 number. If C<$booksellerID> is non-empty, only orders from that seller
251 C<&basket> returns a two-element array. C<@orders> is an array of
252 references-to-hash, whose keys are the fields from the aqorders,
253 biblio, and biblioitems tables in the Koha database.
260 my ( $basketno, $orderby ) = @_;
261 my $dbh = C4::Context->dbh;
263 SELECT aqorderbreakdown.*,
264 biblio.*,biblioitems.*,
266 aqbookfund.bookfundname,
269 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
270 LEFT JOIN aqbookfund ON aqbookfund.bookfundid=aqorderbreakdown.bookfundid
271 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
272 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
274 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
277 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
278 $query .= " ORDER BY $orderby";
279 my $sth = $dbh->prepare($query);
280 $sth->execute($basketno);
283 while ( my $data = $sth->fetchrow_hashref ) {
284 push @results, $data;
290 #------------------------------------------------------------#
292 =head3 GetOrderNumber
296 $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
300 Looks up the ordernumber with the given biblionumber and biblioitemnumber.
302 Returns the number of this order.
306 =item C<$ordernumber> is the order number.
312 my ( $biblionumber,$biblioitemnumber ) = @_;
313 my $dbh = C4::Context->dbh;
318 AND biblioitemnumber=?
320 my $sth = $dbh->prepare($query);
321 $sth->execute( $biblionumber, $biblioitemnumber );
323 return $sth->fetchrow;
326 #------------------------------------------------------------#
332 $order = &GetOrder($ordernumber);
334 Looks up an order by order number.
336 Returns a reference-to-hash describing the order. The keys of
337 C<$order> are fields from the biblio, biblioitems, aqorders, and
338 aqorderbreakdown tables of the Koha database.
346 my $dbh = C4::Context->dbh;
348 SELECT biblioitems.*, biblio.*, aqorderbreakdown.*, aqorders.*
350 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
351 LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber
352 LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber
353 WHERE aqorders.ordernumber=?
356 my $sth= $dbh->prepare($query);
357 $sth->execute($ordnum);
358 my $data = $sth->fetchrow_hashref;
363 #------------------------------------------------------------#
369 &NewOrder($basket, $biblionumber, $title, $quantity, $listprice,
370 $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp,
371 $ecost, $gst, $budget, $unitprice, $subscription,
372 $booksellerinvoicenumber, $purchaseorder, $branchcode);
374 Adds a new order to the database. Any argument that isn't described
375 below is the new value of the field with the same name in the aqorders
376 table of the Koha database.
378 C<$ordnum> is a "minimum order number." After adding the new entry to
379 the aqorders table, C<&neworder> finds the first entry in aqorders
380 with order number greater than or equal to C<$ordnum>, and adds an
381 entry to the aqorderbreakdown table, with the order number just found,
382 and the book fund ID of the newly-added order.
384 C<$budget> is effectively ignored.
385 If it's undef (anything false) or the string 'now', the current day is used.
386 Else, the upcoming July 1st is used.
388 C<$subscription> may be either "yes", or anything else for "no".
396 $basketno, $bibnum, $title, $quantity,
397 $listprice, $booksellerid, $authorisedby, $notes,
398 $bookfund, $bibitemnum, $rrp, $ecost,
399 $gst, $budget, $cost, $sub,
400 $invoice, $sort1, $sort2, $purchaseorder,
405 my $year = localtime->year() + 1900;
406 my $month = localtime->mon() + 1; # months starts at 0, add 1
408 if ( !$budget || $budget eq 'now' ) {
412 # if month is july or more, budget start is 1 jul, next year.
413 elsif ( $month >= '7' ) {
414 ++$year; # add 1 to year , coz its next year
415 $budget = "$year-07-01";
419 # START OF NEW BUDGET, 1ST OF JULY, THIS YEAR
420 $budget = "$year-07-01";
423 if ( $sub eq 'yes' ) {
430 # if $basket empty, it's also a new basket, create it
432 $basketno = NewBasket( $booksellerid, $authorisedby );
435 my $dbh = C4::Context->dbh;
438 ( biblionumber, title, basketno, quantity, listprice,
439 notes, biblioitemnumber, rrp, ecost, gst,
440 unitprice, subscription, sort1, sort2, budgetdate,
441 entrydate, purchaseordernumber)
442 VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,COALESCE(?,NOW()),NOW(),? )
444 my $sth = $dbh->prepare($query);
447 $bibnum, $title, $basketno, $quantity, $listprice,
448 $notes, $bibitemnum, $rrp, $ecost, $gst,
449 $cost, $sub, $sort1, $sort2, $budget,
454 #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
455 my $ordnum = $dbh->{'mysql_insertid'};
457 INSERT INTO aqorderbreakdown (ordernumber,bookfundid, branchcode)
460 $sth = $dbh->prepare($query);
461 $sth->execute( $ordnum, $bookfund, $branchcode );
463 return ( $basketno, $ordnum );
466 #------------------------------------------------------------#
472 &ModOrder($title, $ordernumber, $quantity, $listprice,
473 $biblionumber, $basketno, $supplier, $who, $notes,
474 $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget,
475 $unitprice, $booksellerinvoicenumber, $branchcode);
477 Modifies an existing order. Updates the order with order number
478 C<$ordernumber> and biblionumber C<$biblionumber>. All other arguments
479 update the fields with the same name in the aqorders table of the Koha
482 Entries with order number C<$ordernumber> in the aqorderbreakdown
483 table are also updated to the new book fund ID or branchcode.
491 $title, $ordnum, $quantity, $listprice, $bibnum,
492 $basketno, $supplier, $who, $notes, $bookfund,
493 $bibitemnum, $rrp, $ecost, $gst, $budget,
494 $cost, $invoice, $sort1, $sort2, $purchaseorder, $branchcode
497 # FIXME : Refactor to pass a hashref instead of fifty params.
498 my $dbh = C4::Context->dbh;
502 quantity=?,listprice=?,basketno=?,
503 rrp=?,ecost=?,unitprice=?,booksellerinvoicenumber=?,
504 notes=?,sort1=?, sort2=?, purchaseordernumber=?
505 WHERE ordernumber=? AND biblionumber=?
507 my $sth = $dbh->prepare($query);
509 $title, $quantity, $listprice, $basketno, $rrp,
510 $ecost, $cost, $invoice, $notes, $sort1,
511 $sort2, $purchaseorder,
516 UPDATE aqorderbreakdown
517 SET bookfundid=?,branchcode=?
520 $sth = $dbh->prepare($query);
522 my $rv = $sth->execute( $bookfund,$branchcode, $ordnum );
523 unless($rv && ( $rv ne '0E0' )) { # zero rows affected [Bug 734]
525 INSERT INTO aqorderbreakdown
526 (ordernumber,branchcode,bookfundid)
529 $sth = $dbh->prepare($query);
530 $sth->execute( $ordnum,$branchcode, $bookfund );
535 #------------------------------------------------------------#
537 =head3 ModOrderBiblioNumber
541 &ModOrderBiblioNumber($biblioitemnumber,$ordnum, $biblionumber);
543 Modifies the biblioitemnumber for an existing order.
544 Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
550 sub ModOrderBiblioNumber {
551 my ($biblioitemnumber,$ordnum, $biblionumber) = @_;
552 my $dbh = C4::Context->dbh;
555 SET biblioitemnumber = ?
556 WHERE ordernumber = ?
557 AND biblionumber = ?";
558 my $sth = $dbh->prepare($query);
559 $sth->execute( $biblioitemnumber, $ordnum, $biblionumber );
562 #------------------------------------------------------------#
564 =head3 ModReceiveOrder
568 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
569 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
570 $freight, $bookfund, $rrp);
572 Updates an order, to reflect the fact that it was received, at least
573 in part. All arguments not mentioned below update the fields with the
574 same name in the aqorders table of the Koha database.
576 If a partial order is received, splits the order into two. The received
577 portion must have a booksellerinvoicenumber.
579 Updates the order with bibilionumber C<$biblionumber> and ordernumber
582 Also updates the book fund ID in the aqorderbreakdown table.
589 sub ModReceiveOrder {
591 $biblionumber, $ordnum, $quantrec, $user, $cost,
592 $invoiceno, $freight, $rrp, $bookfund, $datereceived
595 my $dbh = C4::Context->dbh;
596 # warn "DATE BEFORE : $daterecieved";
597 # $daterecieved=POSIX::strftime("%Y-%m-%d",CORE::localtime) unless $daterecieved;
598 # warn "DATE REC : $daterecieved";
599 $datereceived = C4::Dates->output('iso') unless $datereceived;
600 my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber );
602 ModStatus( $suggestionid, 'AVAILABLE', '', $biblionumber );
604 # Allows libraries to change their bookfund during receiving orders
605 # allows them to adjust budgets
606 if ( C4::Context->preference("LooseBudgets") && $bookfund ) {
608 UPDATE aqorderbreakdown
612 my $sth = $dbh->prepare($query);
613 $sth->execute( $bookfund, $ordnum );
617 my $sth=$dbh->prepare("SELECT * FROM aqorders LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
618 WHERE biblionumber=? AND aqorders.ordernumber=?");
619 $sth->execute($biblionumber,$ordnum);
620 my $order = $sth->fetchrow_hashref();
623 if ( $order->{quantity} > $quantrec ) {
624 $sth=$dbh->prepare("update aqorders
625 set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
626 unitprice=?,freight=?,rrp=?,quantity=?
627 where biblionumber=? and ordernumber=?");
628 $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$quantrec,$biblionumber,$ordnum);
630 # create a new order for the remaining items, and set its bookfund.
631 my $newOrder = NewOrder($order->{'basketno'},$order->{'biblionumber'},$order->{'title'}, $order->{'quantity'} - $quantrec,
632 $order->{'listprice'},$order->{'booksellerid'},$order->{'authorisedby'},$order->{'notes'},
633 $order->{'bookfundid'},$order->{'biblioitemnumber'},$order->{'rrp'},$order->{'ecost'},$order->{'gst'},
634 $order->{'budget'},$order->{'unitcost'},$order->{'sub'},'',$order->{'sort1'},$order->{'sort2'},$order->{'purchaseordernumber'});
636 $sth=$dbh->prepare("update aqorders
637 set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
638 unitprice=?,freight=?,rrp=?
639 where biblionumber=? and ordernumber=?");
640 $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$biblionumber,$ordnum);
643 return $datereceived;
645 #------------------------------------------------------------#
649 @results = &SearchOrder($search, $biblionumber, $complete);
653 C<$search> may take one of several forms: if it is an ISBN,
654 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
655 order number, C<&ordersearch> returns orders with that order number
656 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
657 to be a space-separated list of search terms; in this case, all of the
658 terms must appear in the title (matching the beginning of title
661 If C<$complete> is C<yes>, the results will include only completed
662 orders. In any case, C<&ordersearch> ignores cancelled orders.
664 C<&ordersearch> returns an array.
665 C<@results> is an array of references-to-hash with the following keys:
682 my ( $search, $id, $biblionumber ) = @_;
683 my $dbh = C4::Context->dbh;
684 my @data = split( ' ', $search );
687 @searchterms = ($id);
689 map { push( @searchterms, "$_%", "%$_%" ) } @data;
690 push( @searchterms, $search, $search, $biblionumber );
692 ### FIXME THIS CAN raise a problem if more THAN ONE biblioitem is linked to one biblio
693 if(not $id and $biblionumber and $search){
694 $query = "SELECT *,biblio.title
696 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
697 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
698 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
699 WHERE ((datecancellationprinted is NULL)
700 OR (datecancellationprinted = '0000-00-00'))
701 AND aqorders.biblionumber = ?
702 AND aqorders.ordernumber = ?
704 @searchterms = ($biblionumber, $search);
708 "SELECT *,biblio.title
710 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
711 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
712 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
713 WHERE aqbasket.booksellerid = ?
714 AND ((datecancellationprinted is NULL)
715 OR (datecancellationprinted = '0000-00-00'))
719 map { "(biblio.title like ? or biblio.title like ?)" } @data )
721 . ") OR biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
725 " SELECT *,biblio.title
727 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
728 LEFT JOIN aqbasket on aqorders.basketno=aqbasket.basketno
729 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
730 WHERE ((datecancellationprinted is NULL)
731 OR (datecancellationprinted = '0000-00-00'))
732 AND (aqorders.quantityreceived < aqorders.quantity OR aqorders.quantityreceived is NULL)
736 map { "(biblio.title like ? OR biblio.title like ?)" } @data )
738 . ") or biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
741 $query .= " GROUP BY aqorders.ordernumber";
743 my $sth = $dbh->prepare($query);
744 $sth->execute(@searchterms);
751 my $sth2 = $dbh->prepare($query2);
754 FROM aqorderbreakdown
757 my $sth3 = $dbh->prepare($query3);
759 while ( my $data = $sth->fetchrow_hashref ) {
760 $sth2->execute( $data->{'biblionumber'} );
761 my $data2 = $sth2->fetchrow_hashref;
762 $data->{'author'} = $data2->{'author'};
763 $data->{'seriestitle'} = $data2->{'seriestitle'};
764 $sth3->execute( $data->{'ordernumber'} );
765 my $data3 = $sth3->fetchrow_hashref;
766 $data->{'branchcode'} = $data3->{'branchcode'};
767 $data->{'bookfundid'} = $data3->{'bookfundid'};
768 push( @results, $data );
777 #------------------------------------------------------------#
783 &DelOrder($biblionumber, $ordernumber);
785 Cancel the order with the given order and biblio numbers. It does not
786 delete any entries in the aqorders table, it merely marks them as
794 my ( $bibnum, $ordnum ) = @_;
795 my $dbh = C4::Context->dbh;
798 SET datecancellationprinted=now()
799 WHERE biblionumber=? AND ordernumber=?
801 my $sth = $dbh->prepare($query);
802 $sth->execute( $bibnum, $ordnum );
806 =head2 FUNCTIONS ABOUT PARCELS
810 #------------------------------------------------------------#
816 @results = &GetParcel($booksellerid, $code, $date);
818 Looks up all of the received items from the supplier with the given
819 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
821 C<@results> is an array of references-to-hash. The keys of each element are fields from
822 the aqorders, biblio, and biblioitems tables of the Koha database.
824 C<@results> is sorted alphabetically by book title.
831 #gets all orders from a certain supplier, orders them alphabetically
832 my ( $supplierid, $code, $datereceived ) = @_;
833 my $dbh = C4::Context->dbh;
836 if $code; # add % if we search on a given code (otherwise, let him empty)
843 aqorders.biblionumber,
845 aqorders.ordernumber,
847 aqorders.quantityreceived,
853 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
854 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
856 aqbasket.booksellerid = ?
857 AND aqorders.booksellerinvoicenumber LIKE ?
858 AND aqorders.datereceived = ? ";
860 my @query_params = ( $supplierid, $code, $datereceived );
861 if ( C4::Context->preference("IndependantBranches") ) {
862 my $userenv = C4::Context->userenv;
863 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
864 $strsth .= " and (borrowers.branchcode = ?
865 or borrowers.branchcode = '')";
866 push @query_params, $userenv->{branch};
869 $strsth .= " ORDER BY aqbasket.basketno";
870 ### parcelinformation : $strsth
871 my $sth = $dbh->prepare($strsth);
872 $sth->execute( @query_params );
873 while ( my $data = $sth->fetchrow_hashref ) {
874 push( @results, $data );
876 ### countparcelbiblio: scalar(@results)
882 #------------------------------------------------------------#
888 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
889 get a lists of parcels.
898 is the bookseller this function has to get parcels.
901 To know on what criteria the results list has to be ordered.
904 is the booksellerinvoicenumber.
906 =item $datefrom & $dateto
907 to know on what date this function has to filter its search.
910 a pointer on a hash list containing parcel informations as such :
916 =item Number of biblio
918 =item Number of items
925 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
926 my $dbh = C4::Context->dbh;
927 my @query_params = ();
929 SELECT aqorders.booksellerinvoicenumber,
930 datereceived,purchaseordernumber,
931 count(DISTINCT biblionumber) AS biblio,
932 sum(quantity) AS itemsexpected,
933 sum(quantityreceived) AS itemsreceived
934 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
935 WHERE aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL
938 if ( defined $code ) {
939 $strsth .= ' and aqorders.booksellerinvoicenumber like ? ';
940 # add a % to the end of the code to allow stemming.
941 push @query_params, "$code%";
944 if ( defined $datefrom ) {
945 $strsth .= ' and datereceived >= ? ';
946 push @query_params, $datefrom;
949 if ( defined $dateto ) {
950 $strsth .= 'and datereceived <= ? ';
951 push @query_params, $dateto;
954 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
956 # can't use a placeholder to place this column name.
957 # but, we could probably be checking to make sure it is a column that will be fetched.
958 $strsth .= "order by $order " if ($order);
960 my $sth = $dbh->prepare($strsth);
962 $sth->execute( @query_params );
963 my $results = $sth->fetchall_arrayref({});
968 #------------------------------------------------------------#
974 @results = &GetLateOrders;
976 Searches for bookseller with late orders.
979 the table of supplier with late issues. This table is full of hashref.
987 my $supplierid = shift;
990 my $dbh = C4::Context->dbh;
992 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
993 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
995 my @query_params = ($delay); # delay is the first argument regardless
997 SELECT aqbasket.basketno,
998 aqorders.ordernumber,
999 DATE(aqbasket.closedate) AS orderdate,
1000 aqorders.rrp AS unitpricesupplier,
1001 aqorders.ecost AS unitpricelib,
1002 aqbookfund.bookfundname AS budget,
1003 borrowers.branchcode AS branch,
1004 aqbooksellers.name AS supplier,
1007 biblioitems.publishercode AS publisher,
1008 biblioitems.publicationyear,
1012 (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber)
1013 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber)
1014 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber = aqorderbreakdown.ordernumber)
1015 LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
1016 (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber)
1017 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1018 WHERE aqorders.basketno = aqbasket.basketno
1019 AND ( (datereceived = '' OR datereceived IS NULL)
1020 OR (aqorders.quantityreceived < aqorders.quantity)
1024 if ($dbdriver eq "mysql") {
1026 aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
1027 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1028 DATEDIFF(CURDATE( ),closedate) AS latesince
1030 $from .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) ";
1032 HAVING quantity <> 0
1033 AND unitpricesupplier <> 0
1034 AND unitpricelib <> 0
1037 # FIXME: account for IFNULL as above
1039 aqorders.quantity AS quantity,
1040 aqorders.quantity * aqorders.rrp AS subtotal,
1041 (CURDATE - closedate) AS latesince
1043 $from .= " AND (closedate <= (CURDATE -(INTERVAL ? DAY)) ";
1045 if (defined $supplierid) {
1046 $from .= ' AND aqbasket.booksellerid = ? ';
1047 push @query_params, $supplierid;
1049 if (defined $branch) {
1050 $from .= ' AND borrowers.branchcode LIKE ? ';
1051 push @query_params, $branch;
1053 if (C4::Context->preference("IndependantBranches")
1054 && C4::Context->userenv
1055 && C4::Context->userenv->{flags} != 1 ) {
1056 $from .= ' AND borrowers.branchcode LIKE ? ';
1057 push @query_params, C4::Context->userenv->{branch};
1059 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
1060 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
1061 my $sth = $dbh->prepare($query);
1062 $sth->execute(@query_params);
1064 while (my $data = $sth->fetchrow_hashref) {
1065 $data->{orderdate} = format_date($data->{orderdate});
1066 push @results, $data;
1071 #------------------------------------------------------------#
1077 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on );
1079 Retreives some acquisition history information
1082 $order_loop is a list of hashrefs that each look like this:
1084 'author' => 'Twain, Mark',
1086 'biblionumber' => '215',
1088 'creationdate' => 'MM/DD/YYYY',
1089 'datereceived' => undef,
1092 'invoicenumber' => undef,
1094 'ordernumber' => '1',
1096 'quantityreceived' => undef,
1097 'title' => 'The Adventures of Huckleberry Finn'
1099 $total_qty is the sum of all of the quantities in $order_loop
1100 $total_price is the cost of each in $order_loop times the quantity
1101 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
1108 my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_;
1111 my $total_qtyreceived = 0;
1112 my $total_price = 0;
1114 # don't run the query if there are no parameters (list would be too long for sure !)
1115 if ( $title || $author || $name || $from_placed_on || $to_placed_on ) {
1116 my $dbh = C4::Context->dbh;
1122 name,aqbasket.creationdate,
1123 aqorders.datereceived,
1125 aqorders.quantityreceived,
1127 aqorders.ordernumber,
1128 aqorders.booksellerinvoicenumber as invoicenumber,
1129 aqbooksellers.id as id,
1130 aqorders.biblionumber
1132 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
1133 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
1134 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber";
1136 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
1137 if ( C4::Context->preference("IndependantBranches") );
1139 $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
1141 my @query_params = ();
1143 if ( defined $title ) {
1144 $query .= " AND biblio.title LIKE ? ";
1145 $title =~ s/\s+/%/g;
1146 push @query_params, "%$title%";
1149 if ( defined $author ) {
1150 $query .= " AND biblio.author LIKE ? ";
1151 push @query_params, "%$author%";
1154 if ( defined $name ) {
1155 $query .= " AND name LIKE ? ";
1156 push @query_params, "%$name%";
1159 if ( defined $from_placed_on ) {
1160 $query .= " AND creationdate >= ? ";
1161 push @query_params, $from_placed_on;
1164 if ( defined $to_placed_on ) {
1165 $query .= " AND creationdate <= ? ";
1166 push @query_params, $to_placed_on;
1169 if ( C4::Context->preference("IndependantBranches") ) {
1170 my $userenv = C4::Context->userenv;
1171 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1172 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
1173 push @query_params, $userenv->{branch};
1176 $query .= " ORDER BY booksellerid";
1177 my $sth = $dbh->prepare($query);
1178 $sth->execute( @query_params );
1180 while ( my $line = $sth->fetchrow_hashref ) {
1181 $line->{count} = $cnt++;
1182 $line->{toggle} = 1 if $cnt % 2;
1183 push @order_loop, $line;
1184 $line->{creationdate} = format_date( $line->{creationdate} );
1185 $line->{datereceived} = format_date( $line->{datereceived} );
1186 $total_qty += $line->{'quantity'};
1187 $total_qtyreceived += $line->{'quantityreceived'};
1188 $total_price += $line->{'quantity'} * $line->{'ecost'};
1191 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
1194 =head2 GetRecentAcqui
1196 $results = GetRecentAcqui($days);
1198 C<$results> is a ref to a table which containts hashref
1202 sub GetRecentAcqui {
1204 my $dbh = C4::Context->dbh;
1208 ORDER BY timestamp DESC
1211 my $sth = $dbh->prepare($query);
1214 while(my $data = $sth->fetchrow_hashref){
1215 push @results,$data;
1225 Koha Developement team <info@koha.org>