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);
31 # set the version for version checking
34 # used in receiveorder subroutine
35 # to provide library specific handling
36 my $library_name = C4::Context->preference("LibraryName");
40 C4::Acquisition - Koha functions for dealing with orders and acquisitions
48 The functions in this module deal with acquisitions, managing book
49 orders, basket and parcels.
59 &GetBasket &NewBasket &CloseBasket
60 &GetPendingOrders &GetOrder &GetOrders
61 &GetOrderNumber &GetLateOrders &NewOrder &DelOrder
62 &SearchOrder &GetHistory &GetRecentAcqui
63 &ModOrder &ModReceiveOrder &ModOrderBiblioNumber
64 &GetParcels &GetParcel
67 =head2 FUNCTIONS ABOUT BASKETS
73 #------------------------------------------------------------#
79 $aqbasket = &GetBasket($basketnumber);
81 get all basket informations in aqbasket for a given basket
84 informations for a given basket returned as a hashref.
94 my $dbh = C4::Context->dbh;
97 concat( b.firstname,' ',b.surname) AS authorisedbyname,
98 b.branchcode AS branch
100 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
103 my $sth=$dbh->prepare($query);
104 $sth->execute($basketno);
105 my $basket = $sth->fetchrow_hashref;
109 #------------------------------------------------------------#
115 $basket = &NewBasket();
117 Create a new basket in aqbasket table
123 # FIXME : this function seems to be unused.
126 my ( $booksellerid, $authorisedby ) = @_;
127 my $dbh = C4::Context->dbh;
130 (creationdate,booksellerid,authorisedby)
131 VALUES (now(),'$booksellerid','$authorisedby')
136 #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
137 my $basket = $dbh->{'mysql_insertid'};
141 #------------------------------------------------------------#
147 &CloseBasket($basketno);
149 close a basket (becomes unmodifiable,except for recieves)
157 my $dbh = C4::Context->dbh;
163 my $sth = $dbh->prepare($query);
164 $sth->execute($basketno);
167 #------------------------------------------------------------#
171 =head2 FUNCTIONS ABOUT ORDERS
177 #------------------------------------------------------------#
179 =head3 GetPendingOrders
183 $orders = &GetPendingOrders($booksellerid, $grouped);
185 Finds pending orders from the bookseller with the given ID. Ignores
186 completed and cancelled orders.
188 C<$orders> is a reference-to-array; each element is a
189 reference-to-hash with the following fields:
190 C<$grouped> is a boolean that, if set to 1 will group all order lines of the same basket
191 in a single result line
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,$grouped) = @_;
214 my $dbh = C4::Context->dbh;
216 SELECT ".($grouped?"count(*),":"")."aqbasket.basketno,
217 surname,firstname,aqorders.*,
218 aqbasket.closedate, aqbasket.creationdate
220 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
221 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
223 AND (quantity > quantityreceived OR quantityreceived is NULL)
224 AND datecancellationprinted IS NULL
225 AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL)
227 ## FIXME Why 180 days ???
228 if ( C4::Context->preference("IndependantBranches") ) {
229 my $userenv = C4::Context->userenv;
230 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
232 " and (borrowers.branchcode = '"
234 . "' or borrowers.branchcode ='')";
237 $strsth .= " group by aqbasket.basketno" if $grouped;
238 $strsth .= " order by aqbasket.basketno";
240 my $sth = $dbh->prepare($strsth);
241 $sth->execute($supplierid);
242 my $results = $sth->fetchall_arrayref({});
247 #------------------------------------------------------------#
253 @orders = &GetOrders($basketnumber, $orderby);
255 Looks up the pending (non-cancelled) orders with the given basket
256 number. If C<$booksellerID> is non-empty, only orders from that seller
260 C<&basket> returns a two-element array. C<@orders> is an array of
261 references-to-hash, whose keys are the fields from the aqorders,
262 biblio, and biblioitems tables in the Koha database.
269 my ( $basketno, $orderby ) = @_;
270 my $dbh = C4::Context->dbh;
272 SELECT aqorderbreakdown.*,
273 biblio.*,biblioitems.publishercode,
275 aqbookfund.bookfundname,
278 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
279 LEFT JOIN aqbookfund ON aqbookfund.bookfundid=aqorderbreakdown.bookfundid
280 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
281 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
283 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
286 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
287 $query .= " ORDER BY $orderby";
288 my $sth = $dbh->prepare($query);
289 $sth->execute($basketno);
292 while ( my $data = $sth->fetchrow_hashref ) {
293 push @results, $data;
299 #------------------------------------------------------------#
301 =head3 GetOrderNumber
305 $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
307 Looks up the ordernumber with the given biblionumber and biblioitemnumber.
309 Returns the number of this order.
311 =item C<$ordernumber> is the order number.
317 my ( $biblionumber,$biblioitemnumber ) = @_;
318 my $dbh = C4::Context->dbh;
323 AND biblioitemnumber=?
325 my $sth = $dbh->prepare($query);
326 $sth->execute( $biblionumber, $biblioitemnumber );
328 return $sth->fetchrow;
331 #------------------------------------------------------------#
337 $order = &GetOrder($ordernumber);
339 Looks up an order by order number.
341 Returns a reference-to-hash describing the order. The keys of
342 C<$order> are fields from the biblio, biblioitems, aqorders, and
343 aqorderbreakdown tables of the Koha database.
351 my $dbh = C4::Context->dbh;
355 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
356 LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber
357 LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber
358 WHERE aqorders.ordernumber=?
361 my $sth= $dbh->prepare($query);
362 $sth->execute($ordnum);
363 my $data = $sth->fetchrow_hashref;
368 #------------------------------------------------------------#
374 &NewOrder($basket, $biblionumber, $title, $quantity, $listprice,
375 $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp,
376 $ecost, $gst, $budget, $unitprice, $subscription,
377 $booksellerinvoicenumber, $purchaseorder);
379 Adds a new order to the database. Any argument that isn't described
380 below is the new value of the field with the same name in the aqorders
381 table of the Koha database.
383 C<$ordnum> is a "minimum order number." After adding the new entry to
384 the aqorders table, C<&neworder> finds the first entry in aqorders
385 with order number greater than or equal to C<$ordnum>, and adds an
386 entry to the aqorderbreakdown table, with the order number just found,
387 and the book fund ID of the newly-added order.
389 C<$budget> is effectively ignored.
391 C<$subscription> may be either "yes", or anything else for "no".
399 $basketno, $bibnum, $title, $quantity,
400 $listprice, $booksellerid, $authorisedby, $notes,
401 $bookfund, $bibitemnum, $rrp, $ecost,
402 $gst, $budget, $cost, $sub,
403 $invoice, $sort1, $sort2, $purchaseorder
407 my $year = localtime->year() + 1900;
408 my $month = localtime->mon() + 1; # months starts at 0, add 1
410 if ( !$budget || $budget eq 'now' ) {
414 # if month is july or more, budget start is 1 jul, next year.
415 elsif ( $month >= '7' ) {
416 ++$year; # add 1 to year , coz its next year
417 $budget = "'$year-07-01'";
421 # START OF NEW BUDGET, 1ST OF JULY, THIS YEAR
422 $budget = "'$year-07-01'";
425 if ( $sub eq 'yes' ) {
432 # if $basket empty, it's also a new basket, create it
434 $basketno = NewBasket( $booksellerid, $authorisedby );
437 my $dbh = C4::Context->dbh;
440 ( biblionumber,title,basketno,quantity,listprice,notes,
441 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,sort1,sort2,budgetdate,entrydate,purchaseordernumber)
442 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,$budget,now(),? )
444 my $sth = $dbh->prepare($query);
447 $bibnum, $title, $basketno, $quantity, $listprice,
448 $notes, $bibitemnum, $rrp, $ecost, $gst,
449 $cost, $sub, $sort1, $sort2, $purchaseorder
453 #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
454 my $ordnum = $dbh->{'mysql_insertid'};
456 INSERT INTO aqorderbreakdown (ordernumber,bookfundid)
459 $sth = $dbh->prepare($query);
460 $sth->execute( $ordnum, $bookfund );
462 return ( $basketno, $ordnum );
465 #------------------------------------------------------------#
471 &ModOrder($title, $ordernumber, $quantity, $listprice,
472 $biblionumber, $basketno, $supplier, $who, $notes,
473 $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget,
474 $unitprice, $booksellerinvoicenumber);
476 Modifies an existing order. Updates the order with order number
477 C<$ordernumber> and biblionumber C<$biblionumber>. All other arguments
478 update the fields with the same name in the aqorders table of the Koha
481 Entries with order number C<$ordernumber> in the aqorderbreakdown
482 table are also updated to the new book fund ID.
490 $title, $ordnum, $quantity, $listprice, $bibnum,
491 $basketno, $supplier, $who, $notes, $bookfund,
492 $bibitemnum, $rrp, $ecost, $gst, $budget,
493 $cost, $invoice, $sort1, $sort2, $purchaseorder
496 my $dbh = C4::Context->dbh;
500 quantity=?,listprice=?,basketno=?,
501 rrp=?,ecost=?,unitprice=?,booksellerinvoicenumber=?,
502 notes=?,sort1=?, sort2=?, purchaseordernumber=?
503 WHERE ordernumber=? AND biblionumber=?
505 my $sth = $dbh->prepare($query);
507 $title, $quantity, $listprice, $basketno, $rrp,
508 $ecost, $cost, $invoice, $notes, $sort1,
509 $sort2, $purchaseorder,
514 UPDATE aqorderbreakdown
518 $sth = $dbh->prepare($query);
520 unless ( $sth->execute( $bookfund, $ordnum ) )
521 { # zero rows affected [Bug 734]
523 INSERT INTO aqorderbreakdown
524 (ordernumber,bookfundid)
527 $sth = $dbh->prepare($query);
528 $sth->execute( $ordnum, $bookfund );
533 #------------------------------------------------------------#
535 =head3 ModOrderBiblioNumber
539 &ModOrderBiblioNumber($biblioitemnumber,$ordnum, $biblionumber);
541 Modifies the biblioitemnumber for an existing order.
542 Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
548 sub ModOrderBiblioNumber {
549 my ($biblioitemnumber,$ordnum, $biblionumber) = @_;
550 my $dbh = C4::Context->dbh;
553 SET biblioitemnumber = ?
554 WHERE ordernumber = ?
555 AND biblionumber = ?";
556 my $sth = $dbh->prepare($query);
557 $sth->execute( $biblioitemnumber, $ordnum, $biblionumber );
560 #------------------------------------------------------------#
562 =head3 ModReceiveOrder
566 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
567 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
568 $freight, $bookfund, $rrp);
570 Updates an order, to reflect the fact that it was received, at least
571 in part. All arguments not mentioned below update the fields with the
572 same name in the aqorders table of the Koha database.
574 If a partial order is received, splits the order into two. The received
575 portion must have a booksellerinvoicenumber.
577 Updates the order with bibilionumber C<$biblionumber> and ordernumber
580 Also updates the book fund ID in the aqorderbreakdown table.
587 sub ModReceiveOrder {
589 $biblionumber, $ordnum, $quantrec, $user, $cost,
590 $invoiceno, $freight, $rrp, $bookfund, $datereceived
593 my $dbh = C4::Context->dbh;
594 # warn "DATE BEFORE : $daterecieved";
595 # $daterecieved=POSIX::strftime("%Y-%m-%d",CORE::localtime) unless $daterecieved;
596 # warn "DATE REC : $daterecieved";
597 $datereceived = C4::Dates->output('iso') unless $datereceived;
599 my $sth=$dbh->prepare("SELECT * FROM aqorders LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
600 WHERE biblionumber=? AND aqorders.ordernumber=?");
601 $sth->execute($biblionumber,$ordnum);
602 my $order = $sth->fetchrow_hashref();
605 if ( $order->{quantity} > $quantrec ) {
606 $sth=$dbh->prepare("update aqorders
607 set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
608 unitprice=?,freight=?,rrp=?,quantity=?
609 where biblionumber=? and ordernumber=?");
610 $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$quantrec,$biblionumber,$ordnum);
612 # create a new order for the remaining items, and set its bookfund.
613 my $newOrder = NewOrder($order->{'basketno'},$order->{'biblionumber'},$order->{'title'}, $order->{'quantity'} - $quantrec,
614 $order->{'listprice'},$order->{'booksellerid'},$order->{'authorisedby'},$order->{'notes'},
615 $order->{'bookfundid'},$order->{'biblioitemnumber'},$order->{'rrp'},$order->{'ecost'},$order->{'gst'},
616 $order->{'budget'},$order->{'unitcost'},$order->{'sub'},'',$order->{'sort1'},$order->{'sort2'},$order->{'purchaseordernumber'});
618 $sth = $dbh->prepare("select branchcode, bookfundid from aqorderbreakdown where ordernumber=?");
619 $sth->execute($ordnum);
620 my ($branch,$bookfund) = $sth->fetchrow_array;
622 $sth=$dbh->prepare(" insert into aqorderbreakdown (ordernumber, branchcode, bookfundid) values (?,?,?)");
623 $sth->execute($newOrder,$branch,$bookfund);
625 $sth=$dbh->prepare("update aqorders
626 set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
627 unitprice=?,freight=?,rrp=?
628 where biblionumber=? and ordernumber=?");
629 $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$biblionumber,$ordnum);
632 my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber );
634 ModStatus( $suggestionid, 'AVAILABLE', '', $biblionumber );
636 # Allows libraries to change their bookfund during receiving orders
637 # allows them to adjust budgets
638 if ( C4::Context->preference("LooseBudgets") ) {
640 UPDATE aqorderbreakdown
644 my $sth = $dbh->prepare($query);
645 $sth->execute( $bookfund, $ordnum );
648 return $datereceived;
650 #------------------------------------------------------------#
654 @results = &SearchOrder($search, $biblionumber, $complete);
658 C<$search> may take one of several forms: if it is an ISBN,
659 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
660 order number, C<&ordersearch> returns orders with that order number
661 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
662 to be a space-separated list of search terms; in this case, all of the
663 terms must appear in the title (matching the beginning of title
666 If C<$complete> is C<yes>, the results will include only completed
667 orders. In any case, C<&ordersearch> ignores cancelled orders.
669 C<&ordersearch> returns an array.
670 C<@results> is an array of references-to-hash with the following keys:
687 my ( $search, $id, $biblionumber, $catview ) = @_;
688 my $dbh = C4::Context->dbh;
689 my @data = split( ' ', $search );
692 @searchterms = ($id);
694 map { push( @searchterms, "$_%", "%$_%" ) } @data;
695 push( @searchterms, $search, $search, $biblionumber );
697 ### FIXME THIS CAN raise a problem if more THAN ONE biblioitem is linked to one biblio
700 "SELECT *,biblio.title
702 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
703 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
704 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
705 WHERE aqbasket.booksellerid = ?
706 AND ((datecancellationprinted is NULL)
707 OR (datecancellationprinted = '0000-00-00'))
711 map { "(biblio.title like ? or biblio.title like ?)" } @data )
713 . ") OR biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
718 " SELECT *,biblio.title
720 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
721 LEFT JOIN aqbasket on aqorders.basketno=aqbasket.basketno
722 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
723 WHERE ((datecancellationprinted is NULL)
724 OR (datecancellationprinted = '0000-00-00'))
725 AND (aqorders.quantityreceived < aqorders.quantity OR aqorders.quantityreceived is NULL)
729 map { "(biblio.title like ? OR biblio.title like ?)" } @data )
731 . ") or biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
733 $query .= " GROUP BY aqorders.ordernumber";
735 my $sth = $dbh->prepare($query);
736 $sth->execute(@searchterms);
743 my $sth2 = $dbh->prepare($query2);
746 FROM aqorderbreakdown
749 my $sth3 = $dbh->prepare($query3);
751 while ( my $data = $sth->fetchrow_hashref ) {
752 $sth2->execute( $data->{'biblionumber'} );
753 my $data2 = $sth2->fetchrow_hashref;
754 $data->{'author'} = $data2->{'author'};
755 $data->{'seriestitle'} = $data2->{'seriestitle'};
756 $sth3->execute( $data->{'ordernumber'} );
757 my $data3 = $sth3->fetchrow_hashref;
758 $data->{'branchcode'} = $data3->{'branchcode'};
759 $data->{'bookfundid'} = $data3->{'bookfundid'};
760 push( @results, $data );
769 #------------------------------------------------------------#
775 &DelOrder($biblionumber, $ordernumber);
777 Cancel the order with the given order and biblio numbers. It does not
778 delete any entries in the aqorders table, it merely marks them as
786 my ( $bibnum, $ordnum ) = @_;
787 my $dbh = C4::Context->dbh;
790 SET datecancellationprinted=now()
791 WHERE biblionumber=? AND ordernumber=?
793 my $sth = $dbh->prepare($query);
794 $sth->execute( $bibnum, $ordnum );
801 =head2 FUNCTIONS ABOUT PARCELS
807 #------------------------------------------------------------#
813 @results = &GetParcel($booksellerid, $code, $date);
815 Looks up all of the received items from the supplier with the given
816 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
818 C<@results> is an array of references-to-hash. The keys of each element are fields from
819 the aqorders, biblio, and biblioitems tables of the Koha database.
821 C<@results> is sorted alphabetically by book title.
828 #gets all orders from a certain supplier, orders them alphabetically
829 my ( $supplierid, $code, $datereceived ) = @_;
830 my $dbh = C4::Context->dbh;
833 if $code; # add % if we search on a given code (otherwise, let him empty)
840 aqorders.biblionumber,
842 aqorders.ordernumber,
844 aqorders.quantityreceived,
850 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
851 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
853 aqbasket.booksellerid=?
854 AND aqorders.booksellerinvoicenumber LIKE \"$code\"
855 AND aqorders.datereceived= \'$datereceived\'";
857 if ( C4::Context->preference("IndependantBranches") ) {
858 my $userenv = C4::Context->userenv;
859 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
861 " AND (borrowers.branchcode = '"
863 . "' OR borrowers.branchcode ='')";
866 $strsth .= " ORDER BY aqbasket.basketno";
867 ### parcelinformation : $strsth
868 warn "STH : $strsth";
869 my $sth = $dbh->prepare($strsth);
870 $sth->execute($supplierid);
871 while ( my $data = $sth->fetchrow_hashref ) {
872 push( @results, $data );
874 ### countparcelbiblio: scalar(@results)
880 #------------------------------------------------------------#
886 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
887 get a lists of parcels.
892 is the bookseller this function has to get parcels.
895 To know on what criteria the results list has to be ordered.
898 is the booksellerinvoicenumber.
900 =item $datefrom & $dateto
901 to know on what date this function has to filter its search.
904 a pointer on a hash list containing parcel informations as such :
910 =item Number of biblio
912 =item Number of items
919 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
920 my $dbh = C4::Context->dbh;
922 SELECT aqorders.booksellerinvoicenumber,
923 datereceived,purchaseordernumber,
924 count(DISTINCT biblionumber) AS biblio,
925 sum(quantity) AS itemsexpected,
926 sum(quantityreceived) AS itemsreceived
927 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
928 WHERE aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL
931 $strsth .= "and aqorders.booksellerinvoicenumber like \"$code%\" " if ($code);
933 $strsth .= "and datereceived >=" . $dbh->quote($datefrom) . " " if ($datefrom);
935 $strsth .= "and datereceived <=" . $dbh->quote($dateto) . " " if ($dateto);
937 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
938 $strsth .= "order by $order " if ($order);
940 my $sth = $dbh->prepare($strsth);
943 my $results = $sth->fetchall_arrayref({});
948 #------------------------------------------------------------#
954 @results = &GetLateOrders;
956 Searches for bookseller with late orders.
959 the table of supplier with late issues. This table is full of hashref.
967 my $supplierid = shift;
970 my $dbh = C4::Context->dbh;
972 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
974 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
977 if ( $dbdriver eq "mysql" ) {
979 SELECT aqbasket.basketno,aqorders.ordernumber,
980 DATE(aqbasket.closedate) AS orderdate,
981 aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
982 aqorders.rrp AS unitpricesupplier,
983 aqorders.ecost AS unitpricelib,
984 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
985 aqbookfund.bookfundname AS budget,
986 borrowers.branchcode AS branch,
987 aqbooksellers.name AS supplier,
990 biblioitems.publishercode AS publisher,
991 biblioitems.publicationyear,
992 DATEDIFF(CURDATE( ),closedate) AS latesince
994 (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber)
995 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber)
996 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber = aqorderbreakdown.ordernumber)
997 LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
998 (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber)
999 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1000 WHERE aqorders.basketno = aqbasket.basketno
1001 AND (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY))
1002 AND ((datereceived = '' OR datereceived is null)
1003 OR (aqorders.quantityreceived < aqorders.quantity) )
1005 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
1006 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'"
1009 " AND borrowers.branchcode like \'"
1010 . C4::Context->userenv->{branch} . "\'"
1011 if ( C4::Context->preference("IndependantBranches")
1012 && C4::Context->userenv
1013 && C4::Context->userenv->{flags} != 1 );
1014 $strsth .=" HAVING quantity<>0
1015 AND unitpricesupplier<>0
1017 ORDER BY latesince,basketno,borrowers.branchcode, supplier
1022 SELECT aqbasket.basketno,
1023 DATE(aqbasket.closedate) AS orderdate,
1024 aqorders.quantity, aqorders.rrp AS unitpricesupplier,
1025 aqorders.ecost as unitpricelib,
1026 aqorders.quantity * aqorders.rrp AS subtotal
1027 aqbookfund.bookfundname AS budget,
1028 borrowers.branchcode AS branch,
1029 aqbooksellers.name AS supplier,
1032 biblioitems.publishercode AS publisher,
1033 biblioitems.publicationyear,
1034 (CURDATE - closedate) AS latesince
1036 (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber)
1037 LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber)
1038 LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber)
1039 LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
1040 (aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1041 WHERE aqorders.basketno = aqbasket.basketno
1042 AND (closedate < (CURDATE -(INTERVAL $delay DAY))
1043 AND ((datereceived = '' OR datereceived is null)
1044 OR (aqorders.quantityreceived < aqorders.quantity) ) ";
1045 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
1047 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'" if ($branch);
1048 $strsth .=" AND borrowers.branchcode like \'". C4::Context->userenv->{branch} . "\'"
1049 if (C4::Context->preference("IndependantBranches") && C4::Context->userenv->{flags} != 1 );
1050 $strsth .=" ORDER BY latesince,basketno,borrowers.branchcode, supplier";
1052 my $sth = $dbh->prepare($strsth);
1056 while ( my $data = $sth->fetchrow_hashref ) {
1057 $data->{hilighted} = $hilighted if ( $hilighted > 0 );
1058 $data->{orderdate} = format_date( $data->{orderdate} );
1059 push @results, $data;
1060 $hilighted = -$hilighted;
1066 #------------------------------------------------------------#
1072 (\@order_loop, $total_qty, $total_price, $total_qtyreceived)=&GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on )
1074 this function get the search history.
1081 my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_;
1084 my $total_qtyreceived = 0;
1085 my $total_price = 0;
1087 # don't run the query if there are no parameters (list would be too long for sure !)
1088 if ( $title || $author || $name || $from_placed_on || $to_placed_on ) {
1089 my $dbh = C4::Context->dbh;
1095 name,aqbasket.creationdate,
1096 aqorders.datereceived,
1098 aqorders.quantityreceived,
1100 aqorders.ordernumber,
1101 aqorders.booksellerinvoicenumber as invoicenumber,
1102 aqbooksellers.id as id,
1103 aqorders.biblionumber
1105 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
1106 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
1107 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber";
1109 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
1110 if ( C4::Context->preference("IndependantBranches") );
1112 $query .= " WHERE 1 ";
1113 $query .= " AND biblio.title LIKE " . $dbh->quote( "%" . $title . "%" )
1117 " AND biblio.author LIKE " . $dbh->quote( "%" . $author . "%" )
1120 $query .= " AND name LIKE " . $dbh->quote( "%" . $name . "%" ) if $name;
1122 $query .= " AND creationdate >" . $dbh->quote($from_placed_on)
1125 $query .= " AND creationdate<" . $dbh->quote($to_placed_on)
1127 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00')";
1129 if ( C4::Context->preference("IndependantBranches") ) {
1130 my $userenv = C4::Context->userenv;
1131 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1133 " AND (borrowers.branchcode = '"
1134 . $userenv->{branch}
1135 . "' OR borrowers.branchcode ='')";
1138 $query .= " ORDER BY booksellerid";
1139 my $sth = $dbh->prepare($query);
1142 while ( my $line = $sth->fetchrow_hashref ) {
1143 $line->{count} = $cnt++;
1144 $line->{toggle} = 1 if $cnt % 2;
1145 push @order_loop, $line;
1146 $line->{creationdate} = format_date( $line->{creationdate} );
1147 $line->{datereceived} = format_date( $line->{datereceived} );
1148 $total_qty += $line->{'quantity'};
1149 $total_qtyreceived += $line->{'quantityreceived'};
1150 $total_price += $line->{'quantity'} * $line->{'ecost'};
1153 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
1156 =head2 GetRecentAcqui
1158 $results = GetRecentAcqui($days);
1160 C<$results> is a ref to a table which containts hashref
1164 sub GetRecentAcqui {
1166 my $dbh = C4::Context->dbh;
1170 ORDER BY timestamp DESC
1173 my $sth = $dbh->prepare($query);
1176 while(my $data = $sth->fetchrow_hashref){
1177 push @results,$data;
1182 END { } # module clean-up code here (global destructor)
1192 Koha Developement team <info@koha.org>