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 &GetBasketContent &NewBasket &CloseBasket
61 &GetPendingOrders &GetAllOrders
62 &GetOrder &GetLateOrders &NewOrder &DelOrder
63 &SearchOrder &GetHistory
64 &ModOrder &GetSingleOrder &ModReceiveOrder
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 #------------------------------------------------------------#
111 =head3 GetBasketContent
115 @orders = &GetBasketContent($basketnumber, $orderby);
117 Looks up the pending (non-cancelled) orders with the given basket
118 number. If C<$booksellerID> is non-empty, only orders from that seller
122 C<&basket> returns a two-element array. C<@orders> is an array of
123 references-to-hash, whose keys are the fields from the aqorders,
124 biblio, and biblioitems tables in the Koha database.
130 sub GetBasketContent {
131 my ( $basketno, $orderby ) = @_;
132 my $dbh = C4::Context->dbh;
134 SELECT aqorderbreakdown.*,
135 biblio.*,biblioitems.*,
138 FROM aqorders,biblio,biblioitems
139 LEFT JOIN aqorderbreakdown ON
140 aqorders.ordernumber=aqorderbreakdown.ordernumber
142 AND biblio.biblionumber=aqorders.biblionumber
143 AND biblioitems.biblioitemnumber=aqorders.biblioitemnumber
144 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
147 $orderby = "biblioitems.publishercode" unless $orderby;
148 $query .= " ORDER BY $orderby";
149 my $sth = $dbh->prepare($query);
150 $sth->execute($basketno);
155 while ( my $data = $sth->fetchrow_hashref ) {
156 $results[$i++] = $data;
162 #------------------------------------------------------------#
168 $basket = &NewBasket();
170 Create a new basket in aqbasket table
176 # FIXME : this function seems to be unused.
179 my ( $booksellerid, $authorisedby ) = @_;
180 my $dbh = C4::Context->dbh;
183 (creationdate,booksellerid,authorisedby)
184 VALUES (now(),'$booksellerid','$authorisedby')
189 #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
190 my $basket = $dbh->{'mysql_insertid'};
194 #------------------------------------------------------------#
200 &CloseBasket($basketno);
202 close a basket (becomes unmodifiable,except for recieves)
210 my $dbh = C4::Context->dbh;
216 my $sth = $dbh->prepare($query);
217 $sth->execute($basketno);
220 #------------------------------------------------------------#
224 =head2 FUNCTIONS ABOUT ORDERS
230 #------------------------------------------------------------#
232 =head3 GetPendingOrders
236 $orders = &GetPendingOrders($booksellerid);
238 Finds pending orders from the bookseller with the given ID. Ignores
239 completed and cancelled orders.
241 C<$orders> is a reference-to-array; each element is a
242 reference-to-hash with the following fields:
246 =item C<authorizedby>
252 These give the value of the corresponding field in the aqorders table
253 of the Koha database.
259 Results are ordered from most to least recent.
263 sub GetPendingOrders {
265 my $dbh = C4::Context->dbh;
267 SELECT count(*),authorisedby,creationdate,aqbasket.basketno,
268 closedate,surname,firstname,aqorders.title
270 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
271 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
273 AND (quantity > quantityreceived OR quantityreceived is NULL)
274 AND datecancellationprinted IS NULL
275 AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL)
277 if ( C4::Context->preference("IndependantBranches") ) {
278 my $userenv = C4::Context->userenv;
279 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
281 " and (borrowers.branchcode = '"
283 . "' or borrowers.branchcode ='')";
286 $strsth .= " group by basketno order by aqbasket.basketno";
287 my $sth = $dbh->prepare($strsth);
288 $sth->execute($supplierid);
290 while ( my $data = $sth->fetchrow_hashref ) {
291 push( @results, $data );
297 #------------------------------------------------------------#
303 ($order, $ordernumber) = &GetOrder($biblioitemnumber, $biblionumber);
305 Looks up the order with the given biblionumber and biblioitemnumber.
307 Returns a two-element array. C<$ordernumber> is the order number.
308 C<$order> is a reference-to-hash describing the order; its keys are
309 fields from the biblio, biblioitems, aqorders, and aqorderbreakdown
310 tables of the Koha database.
315 # @_ = biblioitemnumber, biblionumber.
317 my ( $bi, $bib ) = @_;
318 my $dbh = C4::Context->dbh;
323 AND biblioitemnumber=?
325 my $sth = $dbh->prepare($query);
326 $sth->execute( $bib, $bi );
328 # FIXME - Use fetchrow_array(), since we're only interested in the one
330 my $ordnum = $sth->fetchrow_hashref;
332 my $order = GetSingleOrder( $ordnum->{'ordernumber'} );
333 return ( $order, $ordnum->{'ordernumber'} );
336 #------------------------------------------------------------#
338 =head3 GetSingleOrder
342 $order = &GetSingleOrder($ordernumber);
344 Looks up an order by order number.
346 Returns a reference-to-hash describing the order. The keys of
347 C<$order> are fields from the biblio, biblioitems, aqorders, and
348 aqorderbreakdown tables of the Koha database.
356 my $dbh = C4::Context->dbh;
359 FROM biblio,biblioitems,aqorders
360 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
361 WHERE aqorders.ordernumber=?
362 AND biblio.biblionumber=aqorders.biblionumber
363 AND biblioitems.biblioitemnumber=aqorders.biblioitemnumber
365 my $sth= $dbh->prepare($query);
366 $sth->execute($ordnum);
367 my $data = $sth->fetchrow_hashref;
372 #------------------------------------------------------------#
378 @results = &GetAllOrders($booksellerid);
380 Looks up all of the pending orders from the supplier with the given
381 bookseller ID. Ignores cancelled and completed orders.
383 C<@results> is an array of references-to-hash. The keys of each element are fields from
384 the aqorders, biblio, and biblioitems tables of the Koha database.
386 C<@results> is sorted alphabetically by book title.
394 #gets all orders from a certain supplier, orders them alphabetically
395 my ($supplierid) = @_;
396 my $dbh = C4::Context->dbh;
399 SELECT count(*),authorisedby,creationdate,aqbasket.basketno,
400 closedate,surname,firstname,aqorders.biblionumber,aqorders.title, aqorders.ordernumber
402 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
403 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
405 AND (quantity > quantityreceived OR quantityreceived IS NULL)
406 AND datecancellationprinted IS NULL
409 if ( C4::Context->preference("IndependantBranches") ) {
410 my $userenv = C4::Context->userenv;
411 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
413 " and (borrowers.branchcode = '"
415 . "' or borrowers.branchcode ='')";
418 $strsth .= " group by basketno order by aqbasket.basketno";
419 my $sth = $dbh->prepare($strsth);
420 $sth->execute($supplierid);
421 while ( my $data = $sth->fetchrow_hashref ) {
422 push( @results, $data );
428 #------------------------------------------------------------#
434 &NewOrder($basket, $biblionumber, $title, $quantity, $listprice,
435 $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp,
436 $ecost, $gst, $budget, $unitprice, $subscription,
437 $booksellerinvoicenumber);
439 Adds a new order to the database. Any argument that isn't described
440 below is the new value of the field with the same name in the aqorders
441 table of the Koha database.
443 C<$ordnum> is a "minimum order number." After adding the new entry to
444 the aqorders table, C<&neworder> finds the first entry in aqorders
445 with order number greater than or equal to C<$ordnum>, and adds an
446 entry to the aqorderbreakdown table, with the order number just found,
447 and the book fund ID of the newly-added order.
449 C<$budget> is effectively ignored.
451 C<$subscription> may be either "yes", or anything else for "no".
459 $basketno, $bibnum, $title, $quantity,
460 $listprice, $booksellerid, $authorisedby, $notes,
461 $bookfund, $bibitemnum, $rrp, $ecost,
462 $gst, $budget, $cost, $sub,
463 $invoice, $sort1, $sort2
467 my $year = localtime->year() + 1900;
468 my $month = localtime->mon() + 1; # months starts at 0, add 1
470 if ( !$budget || $budget eq 'now' ) {
474 # if month is july or more, budget start is 1 jul, next year.
475 elsif ( $month >= '7' ) {
476 ++$year; # add 1 to year , coz its next year
477 $budget = "'$year-07-01'";
481 # START OF NEW BUDGET, 1ST OF JULY, THIS YEAR
482 $budget = "'$year-07-01'";
485 if ( $sub eq 'yes' ) {
492 # if $basket empty, it's also a new basket, create it
494 $basketno = NewBasket( $booksellerid, $authorisedby );
497 my $dbh = C4::Context->dbh;
500 ( biblionumber,title,basketno,quantity,listprice,notes,
501 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,sort1,sort2,budgetdate,entrydate)
502 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,$budget,now() )
504 my $sth = $dbh->prepare($query);
507 $bibnum, $title, $basketno, $quantity, $listprice,
508 $notes, $bibitemnum, $rrp, $ecost, $gst,
509 $cost, $sub, $sort1, $sort2
513 #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
514 my $ordnum = $dbh->{'mysql_insertid'};
516 INSERT INTO aqorderbreakdown (ordernumber,bookfundid)
519 $sth = $dbh->prepare($query);
520 $sth->execute( $ordnum, $bookfund );
522 return ( $basketno, $ordnum );
525 #------------------------------------------------------------#
531 &ModOrder($title, $ordernumber, $quantity, $listprice,
532 $biblionumber, $basketno, $supplier, $who, $notes,
533 $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget,
534 $unitprice, $booksellerinvoicenumber);
536 Modifies an existing order. Updates the order with order number
537 C<$ordernumber> and biblionumber C<$biblionumber>. All other arguments
538 update the fields with the same name in the aqorders table of the Koha
541 Entries with order number C<$ordernumber> in the aqorderbreakdown
542 table are also updated to the new book fund ID.
550 $title, $ordnum, $quantity, $listprice, $bibnum,
551 $basketno, $supplier, $who, $notes, $bookfund,
552 $bibitemnum, $rrp, $ecost, $gst, $budget,
553 $cost, $invoice, $sort1, $sort2
556 my $dbh = C4::Context->dbh;
560 quantity=?,listprice=?,basketno=?,
561 rrp=?,ecost=?,unitprice=?,booksellerinvoicenumber=?,
562 notes=?,sort1=?, sort2=?
563 WHERE ordernumber=? AND biblionumber=?
565 my $sth = $dbh->prepare($query);
567 $title, $quantity, $listprice, $basketno, $rrp,
568 $ecost, $cost, $invoice, $notes, $sort1,
569 $sort2, $ordnum, $bibnum
573 UPDATE aqorderbreakdown
577 $sth = $dbh->prepare($query);
579 unless ( $sth->execute( $bookfund, $ordnum ) )
580 { # zero rows affected [Bug 734]
582 INSERT INTO aqorderbreakdown
583 (ordernumber,bookfundid)
586 $sth = $dbh->prepare($query);
587 $sth->execute( $ordnum, $bookfund );
592 #------------------------------------------------------------#
594 =head3 ModReceiveOrder
598 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
599 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
600 $freight, $bookfund, $rrp);
602 Updates an order, to reflect the fact that it was received, at least
603 in part. All arguments not mentioned below update the fields with the
604 same name in the aqorders table of the Koha database.
606 Updates the order with bibilionumber C<$biblionumber> and ordernumber
609 Also updates the book fund ID in the aqorderbreakdown table.
616 sub ModReceiveOrder {
618 $biblio, $ordnum, $quantrec, $user, $cost,
619 $invoiceno, $freight, $rrp, $bookfund
622 my $dbh = C4::Context->dbh;
625 SET quantityreceived=?,datereceived=now(),booksellerinvoicenumber=?,
626 unitprice=?,freight=?,rrp=?
627 WHERE biblionumber=? AND ordernumber=?
629 my $sth = $dbh->prepare($query);
630 my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblio );
632 ModStatus( $suggestionid, 'AVAILABLE', '', $biblio );
634 $sth->execute( $quantrec, $invoiceno, $cost, $freight, $rrp, $biblio,
638 # Allows libraries to change their bookfund during receiving orders
639 # allows them to adjust budgets
640 if ( C4::Context->preferene("LooseBudgets") ) {
642 UPDATE aqorderbreakdown
646 my $sth = $dbh->prepare($query);
647 $sth->execute( $bookfund, $ordnum );
652 #------------------------------------------------------------#
656 @results = &SearchOrder($search, $biblionumber, $complete);
660 C<$search> may take one of several forms: if it is an ISBN,
661 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
662 order number, C<&ordersearch> returns orders with that order number
663 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
664 to be a space-separated list of search terms; in this case, all of the
665 terms must appear in the title (matching the beginning of title
668 If C<$complete> is C<yes>, the results will include only completed
669 orders. In any case, C<&ordersearch> ignores cancelled orders.
671 C<&ordersearch> returns an array. C<@results> is an array of references-to-hash with the
689 my ( $search, $id, $biblio, $catview ) = @_;
690 my $dbh = C4::Context->dbh;
691 my @data = split( ' ', $search );
694 @searchterms = ($id);
696 map { push( @searchterms, "$_%", "% $_%" ) } @data;
697 push( @searchterms, $search, $search, $biblio );
701 "SELECT *,biblio.title FROM aqorders,biblioitems,biblio,aqbasket
702 WHERE aqorders.biblioitemnumber = biblioitems.biblioitemnumber AND
703 aqorders.basketno = aqbasket.basketno
704 AND aqbasket.booksellerid = ?
705 AND biblio.biblionumber=aqorders.biblionumber
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
719 FROM aqorders,biblioitems,biblio,aqbasket
720 WHERE aqorders.biblioitemnumber = biblioitems.biblioitemnumber
721 AND aqorders.basketno = aqbasket.basketno
722 AND biblio.biblionumber=aqorders.biblionumber
723 AND ((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";
734 my $sth = $dbh->prepare($query);
735 $sth->execute(@searchterms);
742 my $sth2 = $dbh->prepare($query2);
745 FROM aqorderbreakdown
748 my $sth3 = $dbh->prepare($query3);
750 while ( my $data = $sth->fetchrow_hashref ) {
751 $sth2->execute( $data->{'biblionumber'} );
752 my $data2 = $sth2->fetchrow_hashref;
753 $data->{'author'} = $data2->{'author'};
754 $data->{'seriestitle'} = $data2->{'seriestitle'};
755 $sth3->execute( $data->{'ordernumber'} );
756 my $data3 = $sth3->fetchrow_hashref;
757 $data->{'branchcode'} = $data3->{'branchcode'};
758 $data->{'bookfundid'} = $data3->{'bookfundid'};
759 push( @results, $data );
767 #------------------------------------------------------------#
773 &DelOrder($biblionumber, $ordernumber);
775 Cancel the order with the given order and biblio numbers. It does not
776 delete any entries in the aqorders table, it merely marks them as
784 my ( $bibnum, $ordnum ) = @_;
785 my $dbh = C4::Context->dbh;
788 SET datecancellationprinted=now()
789 WHERE biblionumber=? AND ordernumber=?
791 my $sth = $dbh->prepare($query);
792 $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. 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.
829 #gets all orders from a certain supplier, orders them alphabetically
830 my ( $supplierid, $code, $datereceived ) = @_;
831 my $dbh = C4::Context->dbh;
834 if $code; # add % if we search on a given code (otherwise, let him empty)
841 aqorders.biblionumber,
843 aqorders.ordernumber,
845 aqorders.quantityreceived,
850 FROM aqorders,aqbasket
851 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
852 WHERE aqbasket.basketno=aqorders.basketno
853 AND 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 my $sth = $dbh->prepare($strsth);
869 $sth->execute($supplierid);
870 while ( my $data = $sth->fetchrow_hashref ) {
871 push( @results, $data );
873 ### countparcelbiblio: $count
879 #------------------------------------------------------------#
885 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto, $limit);
887 get a lists of parcels
888 Returns a pointer on a hash list containing parcel informations as such :
899 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
900 my $dbh = C4::Context->dbh;
902 SELECT aqorders.booksellerinvoicenumber,
904 count(DISTINCT biblionumber) AS biblio,
905 sum(quantity) AS itemsexpected,
906 sum(quantityreceived) AS itemsreceived
907 FROM aqorders, aqbasket
908 WHERE aqbasket.basketno = aqorders.basketno
909 AND aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL
912 $strsth .= "and aqorders.booksellerinvoicenumber like \"$code%\" " if ($code);
914 $strsth .= "and datereceived >=" . $dbh->quote($datefrom) . " " if ($datefrom);
916 $strsth .= "and datereceived <=" . $dbh->quote($dateto) . " " if ($dateto);
918 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
919 $strsth .= "order by $order " if ($order);
920 my $sth = $dbh->prepare($strsth);
925 while ( my $data2 = $sth->fetchrow_hashref ) {
926 push @results, $data2;
933 #------------------------------------------------------------#
939 @results = &GetLateOrders;
941 Searches for suppliers with late orders.
944 the table of supplier with late issues. This table is full of hashref.
952 my $supplierid = shift;
955 my $dbh = C4::Context->dbh;
957 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
959 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
962 if ( $dbdriver eq "mysql" ) {
964 SELECT aqbasket.basketno,
965 DATE(aqbasket.closedate) AS orderdate,
966 aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
967 aqorders.rrp AS unitpricesupplier,
968 aqorders.ecost AS unitpricelib,
969 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
970 aqbookfund.bookfundname AS budget,
971 borrowers.branchcode AS branch,
972 aqbooksellers.name AS supplier,
975 biblioitems.publishercode AS publisher,
976 biblioitems.publicationyear,
977 DATEDIFF(CURDATE( ),closedate) AS latesince
979 (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber)
980 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber)
981 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber = aqorderbreakdown.ordernumber)
982 LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
983 (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber)
984 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
985 WHERE aqorders.basketno = aqbasket.basketno
986 AND (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY))
987 AND ((datereceived = '' OR datereceived is null)
988 OR (aqorders.quantityreceived < aqorders.quantity) )
990 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
991 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'"
994 " AND borrowers.branchcode like \'"
995 . C4::Context->userenv->{branch} . "\'"
996 if ( C4::Context->preference("IndependantBranches")
997 && C4::Context->userenv
998 && C4::Context->userenv->{flags} != 1 );
999 $strsth .=" HAVING quantity<>0
1000 AND unitpricesupplier<>0
1002 ORDER BY latesince,basketno,borrowers.branchcode, supplier
1007 SELECT aqbasket.basketno,
1008 DATE(aqbasket.closedate) AS orderdate,
1009 aqorders.quantity, aqorders.rrp AS unitpricesupplier,
1010 aqorders.ecost as unitpricelib,
1011 aqorders.quantity * aqorders.rrp AS subtotal
1012 aqbookfund.bookfundname AS budget,
1013 borrowers.branchcode AS branch,
1014 aqbooksellers.name AS supplier,
1017 biblioitems.publishercode AS publisher,
1018 biblioitems.publicationyear,
1019 (CURDATE - closedate) AS latesince
1021 (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber)
1022 LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber)
1023 LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber)
1024 LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
1025 (aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1026 WHERE aqorders.basketno = aqbasket.basketno
1027 AND (closedate < (CURDATE -(INTERVAL $delay DAY))
1028 AND ((datereceived = '' OR datereceived is null)
1029 OR (aqorders.quantityreceived < aqorders.quantity) ) ";
1030 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
1032 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'" if ($branch);
1033 $strsth .=" AND borrowers.branchcode like \'". C4::Context->userenv->{branch} . "\'"
1034 if (C4::Context->preference("IndependantBranches") && C4::Context->userenv->{flags} != 1 );
1035 $strsth .=" ORDER BY latesince,basketno,borrowers.branchcode, supplier";
1037 my $sth = $dbh->prepare($strsth);
1041 while ( my $data = $sth->fetchrow_hashref ) {
1042 $data->{hilighted} = $hilighted if ( $hilighted > 0 );
1043 $data->{orderdate} = format_date( $data->{orderdate} );
1044 push @results, $data;
1045 $hilighted = -$hilighted;
1051 #------------------------------------------------------------#
1057 (\@order_loop, $total_qty, $total_price, $total_qtyreceived)=&GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on )
1059 this function get the search history.
1066 my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_;
1069 my $total_qtyreceived = 0;
1070 my $total_price = 0;
1072 # don't run the query if there are no parameters (list would be too long for sure !)
1073 if ( $title || $author || $name || $from_placed_on || $to_placed_on ) {
1074 my $dbh = C4::Context->dbh;
1080 name,aqbasket.creationdate,
1081 aqorders.datereceived,
1083 aqorders.quantityreceived,
1085 aqorders.ordernumber
1086 FROM aqorders,aqbasket,aqbooksellers,biblio";
1088 $query .= ",borrowers "
1089 if ( C4::Context->preference("IndependantBranches") );
1092 WHERE aqorders.basketno=aqbasket.basketno
1093 AND aqbasket.booksellerid=aqbooksellers.id
1094 AND biblio.biblionumber=aqorders.biblionumber ";
1096 $query .= " AND aqbasket.authorisedby=borrowers.borrowernumber"
1097 if ( C4::Context->preference("IndependantBranches") );
1099 $query .= " AND biblio.title LIKE " . $dbh->quote( "%" . $title . "%" )
1103 " AND biblio.author LIKE " . $dbh->quote( "%" . $author . "%" )
1106 $query .= " AND name LIKE " . $dbh->quote( "%" . $name . "%" ) if $name;
1108 $query .= " AND creationdate >" . $dbh->quote($from_placed_on)
1111 $query .= " AND creationdate<" . $dbh->quote($to_placed_on)
1114 if ( C4::Context->preference("IndependantBranches") ) {
1115 my $userenv = C4::Context->userenv;
1116 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1118 " AND (borrowers.branchcode = '"
1119 . $userenv->{branch}
1120 . "' OR borrowers.branchcode ='')";
1123 $query .= " ORDER BY booksellerid";
1124 my $sth = $dbh->prepare($query);
1127 while ( my $line = $sth->fetchrow_hashref ) {
1128 $line->{count} = $cnt++;
1129 $line->{toggle} = 1 if $cnt % 2;
1130 push @order_loop, $line;
1131 $line->{creationdate} = format_date( $line->{creationdate} );
1132 $line->{datereceived} = format_date( $line->{datereceived} );
1133 $total_qty += $line->{'quantity'};
1134 $total_qtyreceived += $line->{'quantityreceived'};
1135 $total_price += $line->{'quantity'} * $line->{'ecost'};
1138 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
1141 END { } # module clean-up code here (global destructor)
1151 Koha Developement team <info@koha.org>