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
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 borrowers.firstname+' '+borrowers.surname AS authorisedbyname,
98 borrowers.branchcode AS branch
100 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
103 my $sth=$dbh->prepare($query);
104 $sth->execute($basketno);
105 return ( $sth->fetchrow_hashref );
108 #------------------------------------------------------------#
114 $basket = &NewBasket();
116 Create a new basket in aqbasket table
122 # FIXME : this function seems to be unused.
125 my ( $booksellerid, $authorisedby ) = @_;
126 my $dbh = C4::Context->dbh;
129 (creationdate,booksellerid,authorisedby)
130 VALUES (now(),'$booksellerid','$authorisedby')
135 #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
136 my $basket = $dbh->{'mysql_insertid'};
140 #------------------------------------------------------------#
146 &CloseBasket($basketno);
148 close a basket (becomes unmodifiable,except for recieves)
156 my $dbh = C4::Context->dbh;
162 my $sth = $dbh->prepare($query);
163 $sth->execute($basketno);
166 #------------------------------------------------------------#
170 =head2 FUNCTIONS ABOUT ORDERS
176 #------------------------------------------------------------#
178 =head3 GetPendingOrders
182 $orders = &GetPendingOrders($booksellerid, $grouped);
184 Finds pending orders from the bookseller with the given ID. Ignores
185 completed and cancelled orders.
187 C<$orders> is a reference-to-array; each element is a
188 reference-to-hash with the following fields:
189 C<$grouped> is a boolean that, if set to 1 will group all order lines of the same basket
190 in a single result line
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,$grouped) = @_;
213 my $dbh = C4::Context->dbh;
215 SELECT ".($grouped?"count(*),":"")."aqbasket.basketno,
216 surname,firstname,aqorders.*,
217 aqbasket.closedate, aqbasket.creationdate
219 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
220 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
222 AND (quantity > quantityreceived OR quantityreceived is NULL)
223 AND datecancellationprinted IS NULL
224 AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL)
226 ## FIXME Why 180 days ???
227 if ( C4::Context->preference("IndependantBranches") ) {
228 my $userenv = C4::Context->userenv;
229 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
231 " and (borrowers.branchcode = '"
233 . "' or borrowers.branchcode ='')";
236 $strsth .= " group by aqbasket.basketno" if $grouped;
237 $strsth .= " order by aqbasket.basketno";
239 my $sth = $dbh->prepare($strsth);
240 $sth->execute($supplierid);
241 my $results = $sth->fetchall_arrayref({});
246 #------------------------------------------------------------#
252 @orders = &GetOrders($basketnumber, $orderby);
254 Looks up the pending (non-cancelled) orders with the given basket
255 number. If C<$booksellerID> is non-empty, only orders from that seller
259 C<&basket> returns a two-element array. C<@orders> is an array of
260 references-to-hash, whose keys are the fields from the aqorders,
261 biblio, and biblioitems tables in the Koha database.
268 my ( $basketno, $orderby ) = @_;
269 my $dbh = C4::Context->dbh;
271 SELECT aqorderbreakdown.*,
272 biblio.*,biblioitems.*,
274 aqbookfund.bookfundname,
277 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
278 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
279 LEFT JOIN biblioitems ON biblioitems.biblioitemnumber=aqorders.biblioitemnumber
280 LEFT JOIN aqbookfund ON aqbookfund.bookfundid=aqorderbreakdown.bookfundid
282 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
285 $orderby = "biblioitems.publishercode" unless $orderby;
286 $query .= " ORDER BY $orderby";
287 my $sth = $dbh->prepare($query);
288 $sth->execute($basketno);
291 while ( my $data = $sth->fetchrow_hashref ) {
292 push @results, $data;
298 #------------------------------------------------------------#
300 =head3 GetOrderNumber
304 $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
306 Looks up the ordernumber with the given biblionumber and biblioitemnumber.
308 Returns the number of this order.
310 =item C<$ordernumber> is the order number.
316 my ( $biblionumber,$biblioitemnumber ) = @_;
317 my $dbh = C4::Context->dbh;
322 AND biblioitemnumber=?
324 my $sth = $dbh->prepare($query);
325 $sth->execute( $biblionumber, $biblioitemnumber );
327 return $sth->fetchrow;
330 #------------------------------------------------------------#
336 $order = &GetOrder($ordernumber);
338 Looks up an order by order number.
340 Returns a reference-to-hash describing the order. The keys of
341 C<$order> are fields from the biblio, biblioitems, aqorders, and
342 aqorderbreakdown tables of the Koha database.
350 my $dbh = C4::Context->dbh;
354 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
355 LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber
356 LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber
357 WHERE aqorders.ordernumber=?
360 my $sth= $dbh->prepare($query);
361 $sth->execute($ordnum);
362 my $data = $sth->fetchrow_hashref;
367 #------------------------------------------------------------#
373 &NewOrder($basket, $biblionumber, $title, $quantity, $listprice,
374 $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp,
375 $ecost, $gst, $budget, $unitprice, $subscription,
376 $booksellerinvoicenumber);
378 Adds a new order to the database. Any argument that isn't described
379 below is the new value of the field with the same name in the aqorders
380 table of the Koha database.
382 C<$ordnum> is a "minimum order number." After adding the new entry to
383 the aqorders table, C<&neworder> finds the first entry in aqorders
384 with order number greater than or equal to C<$ordnum>, and adds an
385 entry to the aqorderbreakdown table, with the order number just found,
386 and the book fund ID of the newly-added order.
388 C<$budget> is effectively ignored.
390 C<$subscription> may be either "yes", or anything else for "no".
398 $basketno, $bibnum, $title, $quantity,
399 $listprice, $booksellerid, $authorisedby, $notes,
400 $bookfund, $bibitemnum, $rrp, $ecost,
401 $gst, $budget, $cost, $sub,
402 $invoice, $sort1, $sort2
406 my $year = localtime->year() + 1900;
407 my $month = localtime->mon() + 1; # months starts at 0, add 1
409 if ( !$budget || $budget eq 'now' ) {
413 # if month is july or more, budget start is 1 jul, next year.
414 elsif ( $month >= '7' ) {
415 ++$year; # add 1 to year , coz its next year
416 $budget = "'$year-07-01'";
420 # START OF NEW BUDGET, 1ST OF JULY, THIS YEAR
421 $budget = "'$year-07-01'";
424 if ( $sub eq 'yes' ) {
431 # if $basket empty, it's also a new basket, create it
433 $basketno = NewBasket( $booksellerid, $authorisedby );
436 my $dbh = C4::Context->dbh;
439 ( biblionumber,title,basketno,quantity,listprice,notes,
440 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,sort1,sort2,budgetdate,entrydate)
441 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,$budget,now() )
443 my $sth = $dbh->prepare($query);
446 $bibnum, $title, $basketno, $quantity, $listprice,
447 $notes, $bibitemnum, $rrp, $ecost, $gst,
448 $cost, $sub, $sort1, $sort2
452 #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
453 my $ordnum = $dbh->{'mysql_insertid'};
455 INSERT INTO aqorderbreakdown (ordernumber,bookfundid)
458 $sth = $dbh->prepare($query);
459 $sth->execute( $ordnum, $bookfund );
461 return ( $basketno, $ordnum );
464 #------------------------------------------------------------#
470 &ModOrder($title, $ordernumber, $quantity, $listprice,
471 $biblionumber, $basketno, $supplier, $who, $notes,
472 $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget,
473 $unitprice, $booksellerinvoicenumber);
475 Modifies an existing order. Updates the order with order number
476 C<$ordernumber> and biblionumber C<$biblionumber>. All other arguments
477 update the fields with the same name in the aqorders table of the Koha
480 Entries with order number C<$ordernumber> in the aqorderbreakdown
481 table are also updated to the new book fund ID.
489 $title, $ordnum, $quantity, $listprice, $bibnum,
490 $basketno, $supplier, $who, $notes, $bookfund,
491 $bibitemnum, $rrp, $ecost, $gst, $budget,
492 $cost, $invoice, $sort1, $sort2
495 my $dbh = C4::Context->dbh;
499 quantity=?,listprice=?,basketno=?,
500 rrp=?,ecost=?,unitprice=?,booksellerinvoicenumber=?,
501 notes=?,sort1=?, sort2=?
502 WHERE ordernumber=? AND biblionumber=?
504 my $sth = $dbh->prepare($query);
506 $title, $quantity, $listprice, $basketno, $rrp,
507 $ecost, $cost, $invoice, $notes, $sort1,
508 $sort2, $ordnum, $bibnum
512 UPDATE aqorderbreakdown
516 $sth = $dbh->prepare($query);
518 unless ( $sth->execute( $bookfund, $ordnum ) )
519 { # zero rows affected [Bug 734]
521 INSERT INTO aqorderbreakdown
522 (ordernumber,bookfundid)
525 $sth = $dbh->prepare($query);
526 $sth->execute( $ordnum, $bookfund );
531 #------------------------------------------------------------#
533 =head3 ModOrderBiblioNumber
537 &ModOrderBiblioNumber($biblioitemnumber,$ordnum, $biblionumber);
539 Modifies the biblioitemnumber for an existing order.
540 Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
546 sub ModOrderBiblioNumber {
547 my ($biblioitemnumber,$ordnum, $biblionumber) = @_;
548 my $dbh = C4::Context->dbh;
551 SET biblioitemnumber = ?
552 WHERE ordernumber = ?
553 AND biblionumber = ?";
554 my $sth = $dbh->prepare($query);
555 $sth->execute( $biblioitemnumber, $ordnum, $biblionumber );
558 #------------------------------------------------------------#
560 =head3 ModReceiveOrder
564 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
565 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
566 $freight, $bookfund, $rrp);
568 Updates an order, to reflect the fact that it was received, at least
569 in part. All arguments not mentioned below update the fields with the
570 same name in the aqorders table of the Koha database.
572 Updates the order with bibilionumber C<$biblionumber> and ordernumber
575 Also updates the book fund ID in the aqorderbreakdown table.
582 sub ModReceiveOrder {
584 $biblionumber, $ordnum, $quantrec, $user, $cost,
585 $invoiceno, $freight, $rrp, $bookfund, $daterecieved
588 my $dbh = C4::Context->dbh;
589 # warn "DATE BEFORE : $daterecieved";
590 $daterecieved=POSIX::strftime("%Y-%m-%d",CORE::localtime) unless $daterecieved;
591 # warn "DATE REC : $daterecieved";
594 SET quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
595 unitprice=?,freight=?,rrp=?
596 WHERE biblionumber=? AND ordernumber=?
598 my $sth = $dbh->prepare($query);
599 my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber );
601 ModStatus( $suggestionid, 'AVAILABLE', '', $biblionumber );
603 $sth->execute( $quantrec,$daterecieved, $invoiceno, $cost, $freight, $rrp, $biblionumber,
607 # Allows libraries to change their bookfund during receiving orders
608 # allows them to adjust budgets
609 if ( C4::Context->preferene("LooseBudgets") ) {
611 UPDATE aqorderbreakdown
615 my $sth = $dbh->prepare($query);
616 $sth->execute( $bookfund, $ordnum );
619 return $daterecieved;
622 #------------------------------------------------------------#
626 @results = &SearchOrder($search, $biblionumber, $complete);
630 C<$search> may take one of several forms: if it is an ISBN,
631 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
632 order number, C<&ordersearch> returns orders with that order number
633 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
634 to be a space-separated list of search terms; in this case, all of the
635 terms must appear in the title (matching the beginning of title
638 If C<$complete> is C<yes>, the results will include only completed
639 orders. In any case, C<&ordersearch> ignores cancelled orders.
641 C<&ordersearch> returns an array.
642 C<@results> is an array of references-to-hash with the following keys:
659 my ( $search, $id, $biblionumber, $catview ) = @_;
660 my $dbh = C4::Context->dbh;
661 my @data = split( ' ', $search );
664 @searchterms = ($id);
666 map { push( @searchterms, "$_%", "%$_%" ) } @data;
667 push( @searchterms, $search, $search, $biblionumber );
669 ### FIXME THIS CAN raise a problem if more THAN ONE biblioitem is linked to one biblio
672 "SELECT *,biblio.title
674 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
675 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
676 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
677 WHERE aqbasket.booksellerid = ?
678 AND ((datecancellationprinted is NULL)
679 OR (datecancellationprinted = '0000-00-00'))
683 map { "(biblio.title like ? or biblio.title like ?)" } @data )
685 . ") OR biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
690 " SELECT *,biblio.title
692 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
693 LEFT JOIN aqbasket on aqorders.basketno=aqbasket.basketno
694 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
695 WHERE ((datecancellationprinted is NULL)
696 OR (datecancellationprinted = '0000-00-00'))
697 AND (aqorders.quantityreceived < aqorders.quantity OR aqorders.quantityreceived is NULL)
701 map { "(biblio.title like ? OR biblio.title like ?)" } @data )
703 . ") or biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
705 $query .= " GROUP BY aqorders.ordernumber";
707 my $sth = $dbh->prepare($query);
708 $sth->execute(@searchterms);
715 my $sth2 = $dbh->prepare($query2);
718 FROM aqorderbreakdown
721 my $sth3 = $dbh->prepare($query3);
723 while ( my $data = $sth->fetchrow_hashref ) {
724 $sth2->execute( $data->{'biblionumber'} );
725 my $data2 = $sth2->fetchrow_hashref;
726 $data->{'author'} = $data2->{'author'};
727 $data->{'seriestitle'} = $data2->{'seriestitle'};
728 $sth3->execute( $data->{'ordernumber'} );
729 my $data3 = $sth3->fetchrow_hashref;
730 $data->{'branchcode'} = $data3->{'branchcode'};
731 $data->{'bookfundid'} = $data3->{'bookfundid'};
732 push( @results, $data );
741 #------------------------------------------------------------#
747 &DelOrder($biblionumber, $ordernumber);
749 Cancel the order with the given order and biblio numbers. It does not
750 delete any entries in the aqorders table, it merely marks them as
758 my ( $bibnum, $ordnum ) = @_;
759 my $dbh = C4::Context->dbh;
762 SET datecancellationprinted=now()
763 WHERE biblionumber=? AND ordernumber=?
765 my $sth = $dbh->prepare($query);
766 $sth->execute( $bibnum, $ordnum );
773 =head2 FUNCTIONS ABOUT PARCELS
779 #------------------------------------------------------------#
785 @results = &GetParcel($booksellerid, $code, $date);
787 Looks up all of the received items from the supplier with the given
788 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
790 C<@results> is an array of references-to-hash. The keys of each element are fields from
791 the aqorders, biblio, and biblioitems tables of the Koha database.
793 C<@results> is sorted alphabetically by book title.
800 #gets all orders from a certain supplier, orders them alphabetically
801 my ( $supplierid, $code, $datereceived ) = @_;
802 my $dbh = C4::Context->dbh;
805 if $code; # add % if we search on a given code (otherwise, let him empty)
812 aqorders.biblionumber,
814 aqorders.ordernumber,
816 aqorders.quantityreceived,
822 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
823 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
825 aqbasket.booksellerid=?
826 AND aqorders.booksellerinvoicenumber LIKE \"$code\"
827 AND aqorders.datereceived= \'$datereceived\'";
829 if ( C4::Context->preference("IndependantBranches") ) {
830 my $userenv = C4::Context->userenv;
831 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
833 " AND (borrowers.branchcode = '"
835 . "' OR borrowers.branchcode ='')";
838 $strsth .= " ORDER BY aqbasket.basketno";
839 ### parcelinformation : $strsth
840 warn "STH : $strsth";
841 my $sth = $dbh->prepare($strsth);
842 $sth->execute($supplierid);
843 while ( my $data = $sth->fetchrow_hashref ) {
844 push( @results, $data );
846 ### countparcelbiblio: scalar(@results)
852 #------------------------------------------------------------#
858 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
859 get a lists of parcels.
864 is the bookseller this function has to get parcels.
867 To know on what criteria the results list has to be ordered.
870 is the booksellerinvoicenumber.
872 =item $datefrom & $dateto
873 to know on what date this function has to filter its search.
876 a pointer on a hash list containing parcel informations as such :
882 =item Number of biblio
884 =item Number of items
891 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
892 my $dbh = C4::Context->dbh;
894 SELECT aqorders.booksellerinvoicenumber,
896 count(DISTINCT biblionumber) AS biblio,
897 sum(quantity) AS itemsexpected,
898 sum(quantityreceived) AS itemsreceived
899 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
900 WHERE aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL
903 $strsth .= "and aqorders.booksellerinvoicenumber like \"$code%\" " if ($code);
905 $strsth .= "and datereceived >=" . $dbh->quote($datefrom) . " " if ($datefrom);
907 $strsth .= "and datereceived <=" . $dbh->quote($dateto) . " " if ($dateto);
909 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
910 $strsth .= "order by $order " if ($order);
912 my $sth = $dbh->prepare($strsth);
915 my $results = $sth->fetchall_arrayref({});
920 #------------------------------------------------------------#
926 @results = &GetLateOrders;
928 Searches for bookseller with late orders.
931 the table of supplier with late issues. This table is full of hashref.
939 my $supplierid = shift;
942 my $dbh = C4::Context->dbh;
944 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
946 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
949 if ( $dbdriver eq "mysql" ) {
951 SELECT aqbasket.basketno,aqorders.ordernumber,
952 DATE(aqbasket.closedate) AS orderdate,
953 aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
954 aqorders.rrp AS unitpricesupplier,
955 aqorders.ecost AS unitpricelib,
956 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
957 aqbookfund.bookfundname AS budget,
958 borrowers.branchcode AS branch,
959 aqbooksellers.name AS supplier,
962 biblioitems.publishercode AS publisher,
963 biblioitems.publicationyear,
964 DATEDIFF(CURDATE( ),closedate) AS latesince
966 (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber)
967 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber)
968 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber = aqorderbreakdown.ordernumber)
969 LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
970 (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber)
971 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
972 WHERE aqorders.basketno = aqbasket.basketno
973 AND (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY))
974 AND ((datereceived = '' OR datereceived is null)
975 OR (aqorders.quantityreceived < aqorders.quantity) )
977 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
978 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'"
981 " AND borrowers.branchcode like \'"
982 . C4::Context->userenv->{branch} . "\'"
983 if ( C4::Context->preference("IndependantBranches")
984 && C4::Context->userenv
985 && C4::Context->userenv->{flags} != 1 );
986 $strsth .=" HAVING quantity<>0
987 AND unitpricesupplier<>0
989 ORDER BY latesince,basketno,borrowers.branchcode, supplier
994 SELECT aqbasket.basketno,
995 DATE(aqbasket.closedate) AS orderdate,
996 aqorders.quantity, aqorders.rrp AS unitpricesupplier,
997 aqorders.ecost as unitpricelib,
998 aqorders.quantity * aqorders.rrp AS subtotal
999 aqbookfund.bookfundname AS budget,
1000 borrowers.branchcode AS branch,
1001 aqbooksellers.name AS supplier,
1004 biblioitems.publishercode AS publisher,
1005 biblioitems.publicationyear,
1006 (CURDATE - closedate) AS latesince
1008 (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber)
1009 LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber)
1010 LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber)
1011 LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
1012 (aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1013 WHERE aqorders.basketno = aqbasket.basketno
1014 AND (closedate < (CURDATE -(INTERVAL $delay DAY))
1015 AND ((datereceived = '' OR datereceived is null)
1016 OR (aqorders.quantityreceived < aqorders.quantity) ) ";
1017 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
1019 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'" if ($branch);
1020 $strsth .=" AND borrowers.branchcode like \'". C4::Context->userenv->{branch} . "\'"
1021 if (C4::Context->preference("IndependantBranches") && C4::Context->userenv->{flags} != 1 );
1022 $strsth .=" ORDER BY latesince,basketno,borrowers.branchcode, supplier";
1024 my $sth = $dbh->prepare($strsth);
1028 while ( my $data = $sth->fetchrow_hashref ) {
1029 $data->{hilighted} = $hilighted if ( $hilighted > 0 );
1030 $data->{orderdate} = format_date( $data->{orderdate} );
1031 push @results, $data;
1032 $hilighted = -$hilighted;
1038 #------------------------------------------------------------#
1044 (\@order_loop, $total_qty, $total_price, $total_qtyreceived)=&GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on )
1046 this function get the search history.
1053 my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_;
1056 my $total_qtyreceived = 0;
1057 my $total_price = 0;
1059 # don't run the query if there are no parameters (list would be too long for sure !)
1060 if ( $title || $author || $name || $from_placed_on || $to_placed_on ) {
1061 my $dbh = C4::Context->dbh;
1067 name,aqbasket.creationdate,
1068 aqorders.datereceived,
1070 aqorders.quantityreceived,
1072 aqorders.ordernumber,
1073 aqorders.booksellerinvoicenumber as invoicenumber,
1074 aqbooksellers.id as id,
1075 aqorders.biblionumber
1077 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
1078 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
1079 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber";
1081 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
1082 if ( C4::Context->preference("IndependantBranches") );
1084 $query .= " WHERE 1 ";
1085 $query .= " AND biblio.title LIKE " . $dbh->quote( "%" . $title . "%" )
1089 " AND biblio.author LIKE " . $dbh->quote( "%" . $author . "%" )
1092 $query .= " AND name LIKE " . $dbh->quote( "%" . $name . "%" ) if $name;
1094 $query .= " AND creationdate >" . $dbh->quote($from_placed_on)
1097 $query .= " AND creationdate<" . $dbh->quote($to_placed_on)
1099 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00')";
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>