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
63 &SearchOrder &GetHistory &GetRecentAcqui
64 &ModOrder &ModReceiveOrder &ModOrderBiblioNumber
65 &GetParcels &GetParcel
68 =head2 FUNCTIONS ABOUT BASKETS
74 #------------------------------------------------------------#
80 $aqbasket = &GetBasket($basketnumber);
82 get all basket informations in aqbasket for a given basket
85 informations for a given basket returned as a hashref.
95 my $dbh = C4::Context->dbh;
98 borrowers.firstname+' '+borrowers.surname AS authorisedbyname,
99 borrowers.branchcode AS branch
101 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
104 my $sth=$dbh->prepare($query);
105 $sth->execute($basketno);
106 return ( $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.*,
275 aqbookfund.bookfundname,
278 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
279 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
280 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=aqorders.biblioitemnumber
281 LEFT JOIN aqbookfund ON aqbookfund.bookfundid=aqorderbreakdown.bookfundid
283 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
286 $orderby = "biblioitems.publishercode" 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);
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
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)
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
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
496 my $dbh = C4::Context->dbh;
500 quantity=?,listprice=?,basketno=?,
501 rrp=?,ecost=?,unitprice=?,booksellerinvoicenumber=?,
502 notes=?,sort1=?, sort2=?
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, $ordnum, $bibnum
513 UPDATE aqorderbreakdown
517 $sth = $dbh->prepare($query);
519 unless ( $sth->execute( $bookfund, $ordnum ) )
520 { # zero rows affected [Bug 734]
522 INSERT INTO aqorderbreakdown
523 (ordernumber,bookfundid)
526 $sth = $dbh->prepare($query);
527 $sth->execute( $ordnum, $bookfund );
532 #------------------------------------------------------------#
534 =head3 ModOrderBiblioNumber
538 &ModOrderBiblioNumber($biblioitemnumber,$ordnum, $biblionumber);
540 Modifies the biblioitemnumber for an existing order.
541 Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
547 sub ModOrderBiblioNumber {
548 my ($biblioitemnumber,$ordnum, $biblionumber) = @_;
549 my $dbh = C4::Context->dbh;
552 SET biblioitemnumber = ?
553 WHERE ordernumber = ?
554 AND biblionumber = ?";
555 my $sth = $dbh->prepare($query);
556 $sth->execute( $biblioitemnumber, $ordnum, $biblionumber );
559 #------------------------------------------------------------#
561 =head3 ModReceiveOrder
565 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
566 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
567 $freight, $bookfund, $rrp);
569 Updates an order, to reflect the fact that it was received, at least
570 in part. All arguments not mentioned below update the fields with the
571 same name in the aqorders table of the Koha database.
573 Updates the order with bibilionumber C<$biblionumber> and ordernumber
576 Also updates the book fund ID in the aqorderbreakdown table.
583 sub ModReceiveOrder {
585 $biblionumber, $ordnum, $quantrec, $user, $cost,
586 $invoiceno, $freight, $rrp, $bookfund, $daterecieved
589 my $dbh = C4::Context->dbh;
590 # warn "DATE BEFORE : $daterecieved";
591 $daterecieved=POSIX::strftime("%Y-%m-%d",CORE::localtime) unless $daterecieved;
592 # warn "DATE REC : $daterecieved";
595 SET quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
596 unitprice=?,freight=?,rrp=?
597 WHERE biblionumber=? AND ordernumber=?
599 my $sth = $dbh->prepare($query);
600 my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber );
602 ModStatus( $suggestionid, 'AVAILABLE', '', $biblionumber );
604 $sth->execute( $quantrec,$daterecieved, $invoiceno, $cost, $freight, $rrp, $biblionumber,
608 # Allows libraries to change their bookfund during receiving orders
609 # allows them to adjust budgets
610 if ( C4::Context->preferene("LooseBudgets") ) {
612 UPDATE aqorderbreakdown
616 my $sth = $dbh->prepare($query);
617 $sth->execute( $bookfund, $ordnum );
620 return $daterecieved;
623 #------------------------------------------------------------#
627 @results = &SearchOrder($search, $biblionumber, $complete);
631 C<$search> may take one of several forms: if it is an ISBN,
632 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
633 order number, C<&ordersearch> returns orders with that order number
634 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
635 to be a space-separated list of search terms; in this case, all of the
636 terms must appear in the title (matching the beginning of title
639 If C<$complete> is C<yes>, the results will include only completed
640 orders. In any case, C<&ordersearch> ignores cancelled orders.
642 C<&ordersearch> returns an array.
643 C<@results> is an array of references-to-hash with the following keys:
660 my ( $search, $id, $biblionumber, $catview ) = @_;
661 my $dbh = C4::Context->dbh;
662 my @data = split( ' ', $search );
665 @searchterms = ($id);
667 map { push( @searchterms, "$_%", "% $_%" ) } @data;
668 push( @searchterms, $search, $search, $biblionumber );
672 "SELECT *,biblio.title FROM aqorders,biblioitems,biblio,aqbasket
673 WHERE aqorders.biblioitemnumber = biblioitems.biblioitemnumber AND
674 aqorders.basketno = aqbasket.basketno
675 AND aqbasket.booksellerid = ?
676 AND biblio.biblionumber=aqorders.biblionumber
677 AND ((datecancellationprinted is NULL)
678 OR (datecancellationprinted = '0000-00-00'))
682 map { "(biblio.title like ? or biblio.title like ?)" } @data )
684 . ") OR biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
689 " SELECT *,biblio.title
690 FROM aqorders,biblioitems,biblio,aqbasket
691 WHERE aqorders.biblioitemnumber = biblioitems.biblioitemnumber
692 AND aqorders.basketno = aqbasket.basketno
693 AND biblio.biblionumber=aqorders.biblionumber
694 AND ((datecancellationprinted is NULL)
695 OR (datecancellationprinted = '0000-00-00'))
696 AND (aqorders.quantityreceived < aqorders.quantity OR aqorders.quantityreceived is NULL)
700 map { "(biblio.title like ? OR biblio.title like ?)" } @data )
702 . ") or biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
704 $query .= " GROUP BY aqorders.ordernumber";
706 my $sth = $dbh->prepare($query);
707 $sth->execute(@searchterms);
714 my $sth2 = $dbh->prepare($query2);
717 FROM aqorderbreakdown
720 my $sth3 = $dbh->prepare($query3);
722 while ( my $data = $sth->fetchrow_hashref ) {
723 $sth2->execute( $data->{'biblionumber'} );
724 my $data2 = $sth2->fetchrow_hashref;
725 $data->{'author'} = $data2->{'author'};
726 $data->{'seriestitle'} = $data2->{'seriestitle'};
727 $sth3->execute( $data->{'ordernumber'} );
728 my $data3 = $sth3->fetchrow_hashref;
729 $data->{'branchcode'} = $data3->{'branchcode'};
730 $data->{'bookfundid'} = $data3->{'bookfundid'};
731 push( @results, $data );
740 #------------------------------------------------------------#
746 &DelOrder($biblionumber, $ordernumber);
748 Cancel the order with the given order and biblio numbers. It does not
749 delete any entries in the aqorders table, it merely marks them as
757 my ( $bibnum, $ordnum ) = @_;
758 my $dbh = C4::Context->dbh;
761 SET datecancellationprinted=now()
762 WHERE biblionumber=? AND ordernumber=?
764 my $sth = $dbh->prepare($query);
765 $sth->execute( $bibnum, $ordnum );
772 =head2 FUNCTIONS ABOUT PARCELS
778 #------------------------------------------------------------#
784 @results = &GetParcel($booksellerid, $code, $date);
786 Looks up all of the received items from the supplier with the given
787 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
789 C<@results> is an array of references-to-hash. The keys of each element are fields from
790 the aqorders, biblio, and biblioitems tables of the Koha database.
792 C<@results> is sorted alphabetically by book title.
799 #gets all orders from a certain supplier, orders them alphabetically
800 my ( $supplierid, $code, $datereceived ) = @_;
801 my $dbh = C4::Context->dbh;
804 if $code; # add % if we search on a given code (otherwise, let him empty)
811 aqorders.biblionumber,
813 aqorders.ordernumber,
815 aqorders.quantityreceived,
820 FROM aqorders,aqbasket
821 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
822 WHERE aqbasket.basketno=aqorders.basketno
823 AND aqbasket.booksellerid=?
824 AND aqorders.booksellerinvoicenumber LIKE \"$code\"
825 AND aqorders.datereceived= \'$datereceived\'";
827 if ( C4::Context->preference("IndependantBranches") ) {
828 my $userenv = C4::Context->userenv;
829 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
831 " and (borrowers.branchcode = '"
833 . "' or borrowers.branchcode ='')";
836 $strsth .= " order by aqbasket.basketno";
837 ### parcelinformation : $strsth
838 my $sth = $dbh->prepare($strsth);
839 $sth->execute($supplierid);
840 while ( my $data = $sth->fetchrow_hashref ) {
841 push( @results, $data );
843 ### countparcelbiblio: scalar(@results)
849 #------------------------------------------------------------#
855 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
856 get a lists of parcels.
861 is the bookseller this function has to get parcels.
864 To know on what criteria the results list has to be ordered.
867 is the booksellerinvoicenumber.
869 =item $datefrom & $dateto
870 to know on what date this function has to filter its search.
873 a pointer on a hash list containing parcel informations as such :
879 =item Number of biblio
881 =item Number of items
888 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
889 my $dbh = C4::Context->dbh;
891 SELECT aqorders.booksellerinvoicenumber,
893 count(DISTINCT biblionumber) AS biblio,
894 sum(quantity) AS itemsexpected,
895 sum(quantityreceived) AS itemsreceived
896 FROM aqorders, aqbasket
897 WHERE aqbasket.basketno = aqorders.basketno
898 AND aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL
901 $strsth .= "and aqorders.booksellerinvoicenumber like \"$code%\" " if ($code);
903 $strsth .= "and datereceived >=" . $dbh->quote($datefrom) . " " if ($datefrom);
905 $strsth .= "and datereceived <=" . $dbh->quote($dateto) . " " if ($dateto);
907 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
908 $strsth .= "order by $order " if ($order);
910 my $sth = $dbh->prepare($strsth);
913 my $results = $sth->fetchall_arrayref({});
918 #------------------------------------------------------------#
924 @results = &GetLateOrders;
926 Searches for bookseller with late orders.
929 the table of supplier with late issues. This table is full of hashref.
937 my $supplierid = shift;
940 my $dbh = C4::Context->dbh;
942 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
944 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
947 if ( $dbdriver eq "mysql" ) {
949 SELECT aqbasket.basketno,aqorders.ordernumber,
950 DATE(aqbasket.closedate) AS orderdate,
951 aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
952 aqorders.rrp AS unitpricesupplier,
953 aqorders.ecost AS unitpricelib,
954 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
955 aqbookfund.bookfundname AS budget,
956 borrowers.branchcode AS branch,
957 aqbooksellers.name AS supplier,
960 biblioitems.publishercode AS publisher,
961 biblioitems.publicationyear,
962 DATEDIFF(CURDATE( ),closedate) AS latesince
964 (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber)
965 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber)
966 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber = aqorderbreakdown.ordernumber)
967 LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
968 (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber)
969 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
970 WHERE aqorders.basketno = aqbasket.basketno
971 AND (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY))
972 AND ((datereceived = '' OR datereceived is null)
973 OR (aqorders.quantityreceived < aqorders.quantity) )
975 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
976 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'"
979 " AND borrowers.branchcode like \'"
980 . C4::Context->userenv->{branch} . "\'"
981 if ( C4::Context->preference("IndependantBranches")
982 && C4::Context->userenv
983 && C4::Context->userenv->{flags} != 1 );
984 $strsth .=" HAVING quantity<>0
985 AND unitpricesupplier<>0
987 ORDER BY latesince,basketno,borrowers.branchcode, supplier
992 SELECT aqbasket.basketno,
993 DATE(aqbasket.closedate) AS orderdate,
994 aqorders.quantity, aqorders.rrp AS unitpricesupplier,
995 aqorders.ecost as unitpricelib,
996 aqorders.quantity * aqorders.rrp AS subtotal
997 aqbookfund.bookfundname AS budget,
998 borrowers.branchcode AS branch,
999 aqbooksellers.name AS supplier,
1002 biblioitems.publishercode AS publisher,
1003 biblioitems.publicationyear,
1004 (CURDATE - closedate) AS latesince
1006 (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber)
1007 LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber)
1008 LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber)
1009 LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
1010 (aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1011 WHERE aqorders.basketno = aqbasket.basketno
1012 AND (closedate < (CURDATE -(INTERVAL $delay DAY))
1013 AND ((datereceived = '' OR datereceived is null)
1014 OR (aqorders.quantityreceived < aqorders.quantity) ) ";
1015 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
1017 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'" if ($branch);
1018 $strsth .=" AND borrowers.branchcode like \'". C4::Context->userenv->{branch} . "\'"
1019 if (C4::Context->preference("IndependantBranches") && C4::Context->userenv->{flags} != 1 );
1020 $strsth .=" ORDER BY latesince,basketno,borrowers.branchcode, supplier";
1022 my $sth = $dbh->prepare($strsth);
1026 while ( my $data = $sth->fetchrow_hashref ) {
1027 $data->{hilighted} = $hilighted if ( $hilighted > 0 );
1028 $data->{orderdate} = format_date( $data->{orderdate} );
1029 push @results, $data;
1030 $hilighted = -$hilighted;
1036 #------------------------------------------------------------#
1042 (\@order_loop, $total_qty, $total_price, $total_qtyreceived)=&GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on )
1044 this function get the search history.
1051 my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_;
1054 my $total_qtyreceived = 0;
1055 my $total_price = 0;
1057 # don't run the query if there are no parameters (list would be too long for sure !)
1058 if ( $title || $author || $name || $from_placed_on || $to_placed_on ) {
1059 my $dbh = C4::Context->dbh;
1065 name,aqbasket.creationdate,
1066 aqorders.datereceived,
1068 aqorders.quantityreceived,
1070 aqorders.ordernumber,
1071 aqorders.booksellerinvoicenumber as invoicenumber,
1072 aqbooksellers.id as id
1073 FROM aqorders,aqbasket,aqbooksellers,biblio";
1075 $query .= ",borrowers "
1076 if ( C4::Context->preference("IndependantBranches") );
1079 WHERE aqorders.basketno=aqbasket.basketno
1080 AND aqbasket.booksellerid=aqbooksellers.id
1081 AND biblio.biblionumber=aqorders.biblionumber ";
1083 $query .= " AND aqbasket.authorisedby=borrowers.borrowernumber"
1084 if ( C4::Context->preference("IndependantBranches") );
1086 $query .= " AND biblio.title LIKE " . $dbh->quote( "%" . $title . "%" )
1090 " AND biblio.author LIKE " . $dbh->quote( "%" . $author . "%" )
1093 $query .= " AND name LIKE " . $dbh->quote( "%" . $name . "%" ) if $name;
1095 $query .= " AND creationdate >" . $dbh->quote($from_placed_on)
1098 $query .= " AND creationdate<" . $dbh->quote($to_placed_on)
1101 if ( C4::Context->preference("IndependantBranches") ) {
1102 my $userenv = C4::Context->userenv;
1103 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1105 " AND (borrowers.branchcode = '"
1106 . $userenv->{branch}
1107 . "' OR borrowers.branchcode ='')";
1110 $query .= " ORDER BY booksellerid";
1111 my $sth = $dbh->prepare($query);
1114 while ( my $line = $sth->fetchrow_hashref ) {
1115 $line->{count} = $cnt++;
1116 $line->{toggle} = 1 if $cnt % 2;
1117 push @order_loop, $line;
1118 $line->{creationdate} = format_date( $line->{creationdate} );
1119 $line->{datereceived} = format_date( $line->{datereceived} );
1120 $total_qty += $line->{'quantity'};
1121 $total_qtyreceived += $line->{'quantityreceived'};
1122 $total_price += $line->{'quantity'} * $line->{'ecost'};
1125 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
1128 =head2 GetRecentAcqui
1130 $results = GetRecentAcqui($days);
1132 C<$results> is a ref to a table which containts hashref
1136 sub GetRecentAcqui {
1138 my $dbh = C4::Context->dbh;
1142 ORDER BY timestamp DESC
1145 my $sth = $dbh->prepare($query);
1148 while(my $data = $sth->fetchrow_hashref){
1149 push @results,$data;
1154 END { } # module clean-up code here (global destructor)
1164 Koha Developement team <info@koha.org>