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
23 use C4::Dates qw(format_date);
28 use vars qw($VERSION @ISA @EXPORT);
31 # set the version for version checking
36 &GetBasket &NewBasket &CloseBasket
37 &GetPendingOrders &GetOrder &GetOrders
38 &GetOrderNumber &GetLateOrders &NewOrder &DelOrder
39 &SearchOrder &GetHistory &GetRecentAcqui
40 &ModOrder &ModReceiveOrder &ModOrderBiblioNumber
41 &GetParcels &GetParcel
45 # used in receiveorder subroutine
46 # to provide library specific handling
47 my $library_name = C4::Context->preference("LibraryName");
51 C4::Acquisition - Koha functions for dealing with orders and acquisitions
59 The functions in this module deal with acquisitions, managing book
60 orders, basket and parcels.
66 =head2 FUNCTIONS ABOUT BASKETS
74 $aqbasket = &GetBasket($basketnumber);
76 get all basket informations in aqbasket for a given basket
79 informations for a given basket returned as a hashref.
89 my $dbh = C4::Context->dbh;
92 concat( b.firstname,' ',b.surname) AS authorisedbyname,
93 b.branchcode AS branch
95 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
98 my $sth=$dbh->prepare($query);
99 $sth->execute($basketno);
100 my $basket = $sth->fetchrow_hashref;
104 #------------------------------------------------------------#
110 $basket = &NewBasket();
112 Create a new basket in aqbasket table
118 # FIXME : this function seems to be unused.
121 my ( $booksellerid, $authorisedby ) = @_;
122 my $dbh = C4::Context->dbh;
125 (creationdate,booksellerid,authorisedby)
126 VALUES (now(),'$booksellerid','$authorisedby')
131 #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
132 my $basket = $dbh->{'mysql_insertid'};
136 #------------------------------------------------------------#
142 &CloseBasket($basketno);
144 close a basket (becomes unmodifiable,except for recieves)
152 my $dbh = C4::Context->dbh;
158 my $sth = $dbh->prepare($query);
159 $sth->execute($basketno);
162 #------------------------------------------------------------#
166 =head2 FUNCTIONS ABOUT ORDERS
172 #------------------------------------------------------------#
174 =head3 GetPendingOrders
178 $orders = &GetPendingOrders($booksellerid, $grouped);
180 Finds pending orders from the bookseller with the given ID. Ignores
181 completed and cancelled orders.
183 C<$orders> is a reference-to-array; each element is a
184 reference-to-hash with the following fields:
185 C<$grouped> is a boolean that, if set to 1 will group all order lines of the same basket
186 in a single result line
190 =item C<authorizedby>
196 These give the value of the corresponding field in the aqorders table
197 of the Koha database.
203 Results are ordered from most to least recent.
207 sub GetPendingOrders {
208 my ($supplierid,$grouped) = @_;
209 my $dbh = C4::Context->dbh;
211 SELECT ".($grouped?"count(*),":"")."aqbasket.basketno,
212 surname,firstname,aqorders.*,
213 aqbasket.closedate, aqbasket.creationdate
215 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
216 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
218 AND (quantity > quantityreceived OR quantityreceived is NULL)
219 AND datecancellationprinted IS NULL
220 AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL)
222 ## FIXME Why 180 days ???
223 my @query_params = ( $supplierid );
224 if ( C4::Context->preference("IndependantBranches") ) {
225 my $userenv = C4::Context->userenv;
226 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
227 $strsth .= " and (borrowers.branchcode = ?
228 or borrowers.branchcode = '')";
229 push @query_params, $userenv->{branch};
233 $strsth .= " group by aqbasket.basketno" if $grouped;
234 $strsth .= " order by aqbasket.basketno";
236 my $sth = $dbh->prepare($strsth);
237 $sth->execute( @query_params );
238 my $results = $sth->fetchall_arrayref({});
243 #------------------------------------------------------------#
249 @orders = &GetOrders($basketnumber, $orderby);
251 Looks up the pending (non-cancelled) orders with the given basket
252 number. If C<$booksellerID> is non-empty, only orders from that seller
256 C<&basket> returns a two-element array. C<@orders> is an array of
257 references-to-hash, whose keys are the fields from the aqorders,
258 biblio, and biblioitems tables in the Koha database.
265 my ( $basketno, $orderby ) = @_;
266 my $dbh = C4::Context->dbh;
268 SELECT aqorderbreakdown.*,
269 biblio.*,biblioitems.publishercode,
271 aqbookfund.bookfundname,
274 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
275 LEFT JOIN aqbookfund ON aqbookfund.bookfundid=aqorderbreakdown.bookfundid
276 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
277 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
279 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
282 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
283 $query .= " ORDER BY $orderby";
284 my $sth = $dbh->prepare($query);
285 $sth->execute($basketno);
288 while ( my $data = $sth->fetchrow_hashref ) {
289 push @results, $data;
295 #------------------------------------------------------------#
297 =head3 GetOrderNumber
301 $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
303 Looks up the ordernumber with the given biblionumber and biblioitemnumber.
305 Returns the number of this order.
307 =item C<$ordernumber> is the order number.
313 my ( $biblionumber,$biblioitemnumber ) = @_;
314 my $dbh = C4::Context->dbh;
319 AND biblioitemnumber=?
321 my $sth = $dbh->prepare($query);
322 $sth->execute( $biblionumber, $biblioitemnumber );
324 return $sth->fetchrow;
327 #------------------------------------------------------------#
333 $order = &GetOrder($ordernumber);
335 Looks up an order by order number.
337 Returns a reference-to-hash describing the order. The keys of
338 C<$order> are fields from the biblio, biblioitems, aqorders, and
339 aqorderbreakdown tables of the Koha database.
347 my $dbh = C4::Context->dbh;
351 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
352 LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber
353 LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber
354 WHERE aqorders.ordernumber=?
357 my $sth= $dbh->prepare($query);
358 $sth->execute($ordnum);
359 my $data = $sth->fetchrow_hashref;
364 #------------------------------------------------------------#
370 &NewOrder($basket, $biblionumber, $title, $quantity, $listprice,
371 $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp,
372 $ecost, $gst, $budget, $unitprice, $subscription,
373 $booksellerinvoicenumber, $purchaseorder);
375 Adds a new order to the database. Any argument that isn't described
376 below is the new value of the field with the same name in the aqorders
377 table of the Koha database.
379 C<$ordnum> is a "minimum order number." After adding the new entry to
380 the aqorders table, C<&neworder> finds the first entry in aqorders
381 with order number greater than or equal to C<$ordnum>, and adds an
382 entry to the aqorderbreakdown table, with the order number just found,
383 and the book fund ID of the newly-added order.
385 C<$budget> is effectively ignored.
387 C<$subscription> may be either "yes", or anything else for "no".
395 $basketno, $bibnum, $title, $quantity,
396 $listprice, $booksellerid, $authorisedby, $notes,
397 $bookfund, $bibitemnum, $rrp, $ecost,
398 $gst, $budget, $cost, $sub,
399 $invoice, $sort1, $sort2, $purchaseorder
403 my $year = localtime->year() + 1900;
404 my $month = localtime->mon() + 1; # months starts at 0, add 1
406 if ( !$budget || $budget eq 'now' ) {
410 # if month is july or more, budget start is 1 jul, next year.
411 elsif ( $month >= '7' ) {
412 ++$year; # add 1 to year , coz its next year
413 $budget = "'$year-07-01'";
417 # START OF NEW BUDGET, 1ST OF JULY, THIS YEAR
418 $budget = "'$year-07-01'";
421 if ( $sub eq 'yes' ) {
428 # if $basket empty, it's also a new basket, create it
430 $basketno = NewBasket( $booksellerid, $authorisedby );
433 my $dbh = C4::Context->dbh;
436 ( biblionumber,title,basketno,quantity,listprice,notes,
437 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,sort1,sort2,budgetdate,entrydate,purchaseordernumber)
438 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,$budget,now(),? )
440 my $sth = $dbh->prepare($query);
443 $bibnum, $title, $basketno, $quantity, $listprice,
444 $notes, $bibitemnum, $rrp, $ecost, $gst,
445 $cost, $sub, $sort1, $sort2, $purchaseorder
449 #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
450 my $ordnum = $dbh->{'mysql_insertid'};
452 INSERT INTO aqorderbreakdown (ordernumber,bookfundid)
455 $sth = $dbh->prepare($query);
456 $sth->execute( $ordnum, $bookfund );
458 return ( $basketno, $ordnum );
461 #------------------------------------------------------------#
467 &ModOrder($title, $ordernumber, $quantity, $listprice,
468 $biblionumber, $basketno, $supplier, $who, $notes,
469 $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget,
470 $unitprice, $booksellerinvoicenumber);
472 Modifies an existing order. Updates the order with order number
473 C<$ordernumber> and biblionumber C<$biblionumber>. All other arguments
474 update the fields with the same name in the aqorders table of the Koha
477 Entries with order number C<$ordernumber> in the aqorderbreakdown
478 table are also updated to the new book fund ID.
486 $title, $ordnum, $quantity, $listprice, $bibnum,
487 $basketno, $supplier, $who, $notes, $bookfund,
488 $bibitemnum, $rrp, $ecost, $gst, $budget,
489 $cost, $invoice, $sort1, $sort2, $purchaseorder
492 my $dbh = C4::Context->dbh;
496 quantity=?,listprice=?,basketno=?,
497 rrp=?,ecost=?,unitprice=?,booksellerinvoicenumber=?,
498 notes=?,sort1=?, sort2=?, purchaseordernumber=?
499 WHERE ordernumber=? AND biblionumber=?
501 my $sth = $dbh->prepare($query);
503 $title, $quantity, $listprice, $basketno, $rrp,
504 $ecost, $cost, $invoice, $notes, $sort1,
505 $sort2, $purchaseorder,
510 UPDATE aqorderbreakdown
514 $sth = $dbh->prepare($query);
516 unless ( $sth->execute( $bookfund, $ordnum ) )
517 { # zero rows affected [Bug 734]
519 INSERT INTO aqorderbreakdown
520 (ordernumber,bookfundid)
523 $sth = $dbh->prepare($query);
524 $sth->execute( $ordnum, $bookfund );
529 #------------------------------------------------------------#
531 =head3 ModOrderBiblioNumber
535 &ModOrderBiblioNumber($biblioitemnumber,$ordnum, $biblionumber);
537 Modifies the biblioitemnumber for an existing order.
538 Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
544 sub ModOrderBiblioNumber {
545 my ($biblioitemnumber,$ordnum, $biblionumber) = @_;
546 my $dbh = C4::Context->dbh;
549 SET biblioitemnumber = ?
550 WHERE ordernumber = ?
551 AND biblionumber = ?";
552 my $sth = $dbh->prepare($query);
553 $sth->execute( $biblioitemnumber, $ordnum, $biblionumber );
556 #------------------------------------------------------------#
558 =head3 ModReceiveOrder
562 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
563 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
564 $freight, $bookfund, $rrp);
566 Updates an order, to reflect the fact that it was received, at least
567 in part. All arguments not mentioned below update the fields with the
568 same name in the aqorders table of the Koha database.
570 If a partial order is received, splits the order into two. The received
571 portion must have a booksellerinvoicenumber.
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, $datereceived
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";
593 $datereceived = C4::Dates->output('iso') unless $datereceived;
594 my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber );
596 ModStatus( $suggestionid, 'AVAILABLE', '', $biblionumber );
598 # Allows libraries to change their bookfund during receiving orders
599 # allows them to adjust budgets
600 if ( C4::Context->preference("LooseBudgets") && $bookfund ) {
602 UPDATE aqorderbreakdown
606 my $sth = $dbh->prepare($query);
607 $sth->execute( $bookfund, $ordnum );
611 my $sth=$dbh->prepare("SELECT * FROM aqorders LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
612 WHERE biblionumber=? AND aqorders.ordernumber=?");
613 $sth->execute($biblionumber,$ordnum);
614 my $order = $sth->fetchrow_hashref();
617 if ( $order->{quantity} > $quantrec ) {
618 $sth=$dbh->prepare("update aqorders
619 set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
620 unitprice=?,freight=?,rrp=?,quantity=?
621 where biblionumber=? and ordernumber=?");
622 $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$quantrec,$biblionumber,$ordnum);
624 # create a new order for the remaining items, and set its bookfund.
625 my $newOrder = NewOrder($order->{'basketno'},$order->{'biblionumber'},$order->{'title'}, $order->{'quantity'} - $quantrec,
626 $order->{'listprice'},$order->{'booksellerid'},$order->{'authorisedby'},$order->{'notes'},
627 $order->{'bookfundid'},$order->{'biblioitemnumber'},$order->{'rrp'},$order->{'ecost'},$order->{'gst'},
628 $order->{'budget'},$order->{'unitcost'},$order->{'sub'},'',$order->{'sort1'},$order->{'sort2'},$order->{'purchaseordernumber'});
630 $sth=$dbh->prepare(" insert into aqorderbreakdown (ordernumber, branchcode, bookfundid) values (?,?,?)");
631 $sth->execute($newOrder,$order->{branch},$order->{bookfundid});
633 $sth=$dbh->prepare("update aqorders
634 set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
635 unitprice=?,freight=?,rrp=?
636 where biblionumber=? and ordernumber=?");
637 $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$biblionumber,$ordnum);
640 return $datereceived;
642 #------------------------------------------------------------#
646 @results = &SearchOrder($search, $biblionumber, $complete);
650 C<$search> may take one of several forms: if it is an ISBN,
651 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
652 order number, C<&ordersearch> returns orders with that order number
653 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
654 to be a space-separated list of search terms; in this case, all of the
655 terms must appear in the title (matching the beginning of title
658 If C<$complete> is C<yes>, the results will include only completed
659 orders. In any case, C<&ordersearch> ignores cancelled orders.
661 C<&ordersearch> returns an array.
662 C<@results> is an array of references-to-hash with the following keys:
679 my ( $search, $id, $biblionumber, $catview ) = @_;
680 my $dbh = C4::Context->dbh;
681 my @data = split( ' ', $search );
684 @searchterms = ($id);
686 map { push( @searchterms, "$_%", "%$_%" ) } @data;
687 push( @searchterms, $search, $search, $biblionumber );
689 ### FIXME THIS CAN raise a problem if more THAN ONE biblioitem is linked to one biblio
692 "SELECT *,biblio.title
694 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
695 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
696 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
697 WHERE aqbasket.booksellerid = ?
698 AND ((datecancellationprinted is NULL)
699 OR (datecancellationprinted = '0000-00-00'))
703 map { "(biblio.title like ? or biblio.title like ?)" } @data )
705 . ") OR biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
710 " SELECT *,biblio.title
712 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
713 LEFT JOIN aqbasket on aqorders.basketno=aqbasket.basketno
714 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
715 WHERE ((datecancellationprinted is NULL)
716 OR (datecancellationprinted = '0000-00-00'))
717 AND (aqorders.quantityreceived < aqorders.quantity OR aqorders.quantityreceived is NULL)
721 map { "(biblio.title like ? OR biblio.title like ?)" } @data )
723 . ") or biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
725 $query .= " GROUP BY aqorders.ordernumber";
727 my $sth = $dbh->prepare($query);
728 $sth->execute(@searchterms);
735 my $sth2 = $dbh->prepare($query2);
738 FROM aqorderbreakdown
741 my $sth3 = $dbh->prepare($query3);
743 while ( my $data = $sth->fetchrow_hashref ) {
744 $sth2->execute( $data->{'biblionumber'} );
745 my $data2 = $sth2->fetchrow_hashref;
746 $data->{'author'} = $data2->{'author'};
747 $data->{'seriestitle'} = $data2->{'seriestitle'};
748 $sth3->execute( $data->{'ordernumber'} );
749 my $data3 = $sth3->fetchrow_hashref;
750 $data->{'branchcode'} = $data3->{'branchcode'};
751 $data->{'bookfundid'} = $data3->{'bookfundid'};
752 push( @results, $data );
761 #------------------------------------------------------------#
767 &DelOrder($biblionumber, $ordernumber);
769 Cancel the order with the given order and biblio numbers. It does not
770 delete any entries in the aqorders table, it merely marks them as
778 my ( $bibnum, $ordnum ) = @_;
779 my $dbh = C4::Context->dbh;
782 SET datecancellationprinted=now()
783 WHERE biblionumber=? AND ordernumber=?
785 my $sth = $dbh->prepare($query);
786 $sth->execute( $bibnum, $ordnum );
793 =head2 FUNCTIONS ABOUT PARCELS
799 #------------------------------------------------------------#
805 @results = &GetParcel($booksellerid, $code, $date);
807 Looks up all of the received items from the supplier with the given
808 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
810 C<@results> is an array of references-to-hash. The keys of each element are fields from
811 the aqorders, biblio, and biblioitems tables of the Koha database.
813 C<@results> is sorted alphabetically by book title.
820 #gets all orders from a certain supplier, orders them alphabetically
821 my ( $supplierid, $code, $datereceived ) = @_;
822 my $dbh = C4::Context->dbh;
825 if $code; # add % if we search on a given code (otherwise, let him empty)
832 aqorders.biblionumber,
834 aqorders.ordernumber,
836 aqorders.quantityreceived,
842 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
843 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
845 aqbasket.booksellerid=?
846 AND aqorders.booksellerinvoicenumber LIKE \"$code\"
847 AND aqorders.datereceived= \'$datereceived\'";
849 if ( C4::Context->preference("IndependantBranches") ) {
850 my $userenv = C4::Context->userenv;
851 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
853 " AND (borrowers.branchcode = '"
855 . "' OR borrowers.branchcode ='')";
858 $strsth .= " ORDER BY aqbasket.basketno";
859 ### parcelinformation : $strsth
860 # warn "STH : $strsth";
861 my $sth = $dbh->prepare($strsth);
862 $sth->execute($supplierid);
863 while ( my $data = $sth->fetchrow_hashref ) {
864 push( @results, $data );
866 ### countparcelbiblio: scalar(@results)
872 #------------------------------------------------------------#
878 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
879 get a lists of parcels.
884 is the bookseller this function has to get parcels.
887 To know on what criteria the results list has to be ordered.
890 is the booksellerinvoicenumber.
892 =item $datefrom & $dateto
893 to know on what date this function has to filter its search.
896 a pointer on a hash list containing parcel informations as such :
902 =item Number of biblio
904 =item Number of items
911 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
912 my $dbh = C4::Context->dbh;
914 SELECT aqorders.booksellerinvoicenumber,
915 datereceived,purchaseordernumber,
916 count(DISTINCT biblionumber) AS biblio,
917 sum(quantity) AS itemsexpected,
918 sum(quantityreceived) AS itemsreceived
919 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
920 WHERE aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL
923 $strsth .= "and aqorders.booksellerinvoicenumber like \"$code%\" " if ($code);
925 $strsth .= "and datereceived >=" . $dbh->quote($datefrom) . " " if ($datefrom);
927 $strsth .= "and datereceived <=" . $dbh->quote($dateto) . " " if ($dateto);
929 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
930 $strsth .= "order by $order " if ($order);
932 my $sth = $dbh->prepare($strsth);
935 my $results = $sth->fetchall_arrayref({});
940 #------------------------------------------------------------#
946 @results = &GetLateOrders;
948 Searches for bookseller with late orders.
951 the table of supplier with late issues. This table is full of hashref.
959 my $supplierid = shift;
962 my $dbh = C4::Context->dbh;
964 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
966 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
969 if ( $dbdriver eq "mysql" ) {
971 SELECT aqbasket.basketno,aqorders.ordernumber,
972 DATE(aqbasket.closedate) AS orderdate,
973 aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
974 aqorders.rrp AS unitpricesupplier,
975 aqorders.ecost AS unitpricelib,
976 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
977 aqbookfund.bookfundname AS budget,
978 borrowers.branchcode AS branch,
979 aqbooksellers.name AS supplier,
982 biblioitems.publishercode AS publisher,
983 biblioitems.publicationyear,
984 DATEDIFF(CURDATE( ),closedate) AS latesince
986 (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber)
987 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber)
988 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber = aqorderbreakdown.ordernumber)
989 LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
990 (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber)
991 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
992 WHERE aqorders.basketno = aqbasket.basketno
993 AND (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY))
994 AND ((datereceived = '' OR datereceived is null)
995 OR (aqorders.quantityreceived < aqorders.quantity) )
997 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
998 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'"
1001 " AND borrowers.branchcode like \'"
1002 . C4::Context->userenv->{branch} . "\'"
1003 if ( C4::Context->preference("IndependantBranches")
1004 && C4::Context->userenv
1005 && C4::Context->userenv->{flags} != 1 );
1006 $strsth .=" HAVING quantity<>0
1007 AND unitpricesupplier<>0
1009 ORDER BY latesince,basketno,borrowers.branchcode, supplier
1014 SELECT aqbasket.basketno,
1015 DATE(aqbasket.closedate) AS orderdate,
1016 aqorders.quantity, aqorders.rrp AS unitpricesupplier,
1017 aqorders.ecost as unitpricelib,
1018 aqorders.quantity * aqorders.rrp AS subtotal
1019 aqbookfund.bookfundname AS budget,
1020 borrowers.branchcode AS branch,
1021 aqbooksellers.name AS supplier,
1024 biblioitems.publishercode AS publisher,
1025 biblioitems.publicationyear,
1026 (CURDATE - closedate) AS latesince
1028 (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber)
1029 LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber)
1030 LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber)
1031 LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
1032 (aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1033 WHERE aqorders.basketno = aqbasket.basketno
1034 AND (closedate < (CURDATE -(INTERVAL $delay DAY))
1035 AND ((datereceived = '' OR datereceived is null)
1036 OR (aqorders.quantityreceived < aqorders.quantity) ) ";
1037 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
1039 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'" if ($branch);
1040 $strsth .=" AND borrowers.branchcode like \'". C4::Context->userenv->{branch} . "\'"
1041 if (C4::Context->preference("IndependantBranches") && C4::Context->userenv->{flags} != 1 );
1042 $strsth .=" ORDER BY latesince,basketno,borrowers.branchcode, supplier";
1044 my $sth = $dbh->prepare($strsth);
1048 while ( my $data = $sth->fetchrow_hashref ) {
1049 $data->{hilighted} = $hilighted if ( $hilighted > 0 );
1050 $data->{orderdate} = format_date( $data->{orderdate} );
1051 push @results, $data;
1052 $hilighted = -$hilighted;
1058 #------------------------------------------------------------#
1064 (\@order_loop, $total_qty, $total_price, $total_qtyreceived)=&GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on )
1066 this function get the search history.
1073 my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_;
1076 my $total_qtyreceived = 0;
1077 my $total_price = 0;
1079 # don't run the query if there are no parameters (list would be too long for sure !)
1080 if ( $title || $author || $name || $from_placed_on || $to_placed_on ) {
1081 my $dbh = C4::Context->dbh;
1087 name,aqbasket.creationdate,
1088 aqorders.datereceived,
1090 aqorders.quantityreceived,
1092 aqorders.ordernumber,
1093 aqorders.booksellerinvoicenumber as invoicenumber,
1094 aqbooksellers.id as id,
1095 aqorders.biblionumber
1097 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
1098 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
1099 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber";
1101 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
1102 if ( C4::Context->preference("IndependantBranches") );
1104 $query .= " WHERE 1 ";
1105 $query .= " AND biblio.title LIKE " . $dbh->quote( "%" . $title . "%" )
1109 " AND biblio.author LIKE " . $dbh->quote( "%" . $author . "%" )
1112 $query .= " AND name LIKE " . $dbh->quote( "%" . $name . "%" ) if $name;
1114 $query .= " AND creationdate >" . $dbh->quote($from_placed_on)
1117 $query .= " AND creationdate<" . $dbh->quote($to_placed_on)
1119 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00')";
1121 if ( C4::Context->preference("IndependantBranches") ) {
1122 my $userenv = C4::Context->userenv;
1123 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1125 " AND (borrowers.branchcode = '"
1126 . $userenv->{branch}
1127 . "' OR borrowers.branchcode ='')";
1130 $query .= " ORDER BY booksellerid";
1131 my $sth = $dbh->prepare($query);
1134 while ( my $line = $sth->fetchrow_hashref ) {
1135 $line->{count} = $cnt++;
1136 $line->{toggle} = 1 if $cnt % 2;
1137 push @order_loop, $line;
1138 $line->{creationdate} = format_date( $line->{creationdate} );
1139 $line->{datereceived} = format_date( $line->{datereceived} );
1140 $total_qty += $line->{'quantity'};
1141 $total_qtyreceived += $line->{'quantityreceived'};
1142 $total_price += $line->{'quantity'} * $line->{'ecost'};
1145 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
1148 =head2 GetRecentAcqui
1150 $results = GetRecentAcqui($days);
1152 C<$results> is a ref to a table which containts hashref
1156 sub GetRecentAcqui {
1158 my $dbh = C4::Context->dbh;
1162 ORDER BY timestamp DESC
1165 my $sth = $dbh->prepare($query);
1168 while(my $data = $sth->fetchrow_hashref){
1169 push @results,$data;
1181 Koha Developement team <info@koha.org>