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.
309 =item C<$ordernumber> is the order number.
311 =item C<$order> is a reference-to-hash describing the order;
312 its keys are fields from the biblio, biblioitems, aqorders, and aqorderbreakdown
313 tables of the Koha database.
319 my ( $biblionumber,$biblioitemnumber ) = @_;
320 my $dbh = C4::Context->dbh;
325 AND biblioitemnumber=?
327 my $sth = $dbh->prepare($query);
328 $sth->execute( $biblionumber, $biblioitemnumber );
330 # FIXME - Use fetchrow_array(), since we're only interested in the one
332 my $ordnum = $sth->fetchrow_hashref;
334 my $order = GetSingleOrder( $ordnum->{'ordernumber'} );
335 return ( $order, $ordnum->{'ordernumber'} );
338 #------------------------------------------------------------#
340 =head3 GetSingleOrder
344 $order = &GetSingleOrder($ordernumber);
346 Looks up an order by order number.
348 Returns a reference-to-hash describing the order. The keys of
349 C<$order> are fields from the biblio, biblioitems, aqorders, and
350 aqorderbreakdown tables of the Koha database.
358 my $dbh = C4::Context->dbh;
361 FROM biblio,biblioitems,aqorders
362 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber=aqorderbreakdown.ordernumber
363 WHERE aqorders.ordernumber=?
364 AND biblio.biblionumber=aqorders.biblionumber
365 AND biblioitems.biblioitemnumber=aqorders.biblioitemnumber
367 my $sth= $dbh->prepare($query);
368 $sth->execute($ordnum);
369 my $data = $sth->fetchrow_hashref;
374 #------------------------------------------------------------#
380 @results = &GetAllOrders($booksellerid);
382 Looks up all of the pending orders from the supplier with the given
383 bookseller ID. Ignores cancelled and completed orders.
385 C<@results> is an array of references-to-hash. The keys of each element are fields from
386 the aqorders, biblio, and biblioitems tables of the Koha database.
388 C<@results> is sorted alphabetically by book title.
396 #gets all orders from a certain supplier, orders them alphabetically
397 my ($supplierid) = @_;
398 my $dbh = C4::Context->dbh;
401 SELECT count(*),authorisedby,creationdate,aqbasket.basketno,
402 closedate,surname,firstname,aqorders.biblionumber,aqorders.title, aqorders.ordernumber
404 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
405 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
407 AND (quantity > quantityreceived OR quantityreceived IS NULL)
408 AND datecancellationprinted IS NULL
411 if ( C4::Context->preference("IndependantBranches") ) {
412 my $userenv = C4::Context->userenv;
413 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
415 " and (borrowers.branchcode = '"
417 . "' or borrowers.branchcode ='')";
420 $strsth .= " group by basketno order by aqbasket.basketno";
421 my $sth = $dbh->prepare($strsth);
422 $sth->execute($supplierid);
423 while ( my $data = $sth->fetchrow_hashref ) {
424 push( @results, $data );
430 #------------------------------------------------------------#
436 &NewOrder($basket, $biblionumber, $title, $quantity, $listprice,
437 $booksellerid, $who, $notes, $bookfund, $biblioitemnumber, $rrp,
438 $ecost, $gst, $budget, $unitprice, $subscription,
439 $booksellerinvoicenumber);
441 Adds a new order to the database. Any argument that isn't described
442 below is the new value of the field with the same name in the aqorders
443 table of the Koha database.
445 C<$ordnum> is a "minimum order number." After adding the new entry to
446 the aqorders table, C<&neworder> finds the first entry in aqorders
447 with order number greater than or equal to C<$ordnum>, and adds an
448 entry to the aqorderbreakdown table, with the order number just found,
449 and the book fund ID of the newly-added order.
451 C<$budget> is effectively ignored.
453 C<$subscription> may be either "yes", or anything else for "no".
461 $basketno, $bibnum, $title, $quantity,
462 $listprice, $booksellerid, $authorisedby, $notes,
463 $bookfund, $bibitemnum, $rrp, $ecost,
464 $gst, $budget, $cost, $sub,
465 $invoice, $sort1, $sort2
469 my $year = localtime->year() + 1900;
470 my $month = localtime->mon() + 1; # months starts at 0, add 1
472 if ( !$budget || $budget eq 'now' ) {
476 # if month is july or more, budget start is 1 jul, next year.
477 elsif ( $month >= '7' ) {
478 ++$year; # add 1 to year , coz its next year
479 $budget = "'$year-07-01'";
483 # START OF NEW BUDGET, 1ST OF JULY, THIS YEAR
484 $budget = "'$year-07-01'";
487 if ( $sub eq 'yes' ) {
494 # if $basket empty, it's also a new basket, create it
496 $basketno = NewBasket( $booksellerid, $authorisedby );
499 my $dbh = C4::Context->dbh;
502 ( biblionumber,title,basketno,quantity,listprice,notes,
503 biblioitemnumber,rrp,ecost,gst,unitprice,subscription,sort1,sort2,budgetdate,entrydate)
504 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,$budget,now() )
506 my $sth = $dbh->prepare($query);
509 $bibnum, $title, $basketno, $quantity, $listprice,
510 $notes, $bibitemnum, $rrp, $ecost, $gst,
511 $cost, $sub, $sort1, $sort2
515 #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
516 my $ordnum = $dbh->{'mysql_insertid'};
518 INSERT INTO aqorderbreakdown (ordernumber,bookfundid)
521 $sth = $dbh->prepare($query);
522 $sth->execute( $ordnum, $bookfund );
524 return ( $basketno, $ordnum );
527 #------------------------------------------------------------#
533 &ModOrder($title, $ordernumber, $quantity, $listprice,
534 $biblionumber, $basketno, $supplier, $who, $notes,
535 $bookfundid, $bibitemnum, $rrp, $ecost, $gst, $budget,
536 $unitprice, $booksellerinvoicenumber);
538 Modifies an existing order. Updates the order with order number
539 C<$ordernumber> and biblionumber C<$biblionumber>. All other arguments
540 update the fields with the same name in the aqorders table of the Koha
543 Entries with order number C<$ordernumber> in the aqorderbreakdown
544 table are also updated to the new book fund ID.
552 $title, $ordnum, $quantity, $listprice, $bibnum,
553 $basketno, $supplier, $who, $notes, $bookfund,
554 $bibitemnum, $rrp, $ecost, $gst, $budget,
555 $cost, $invoice, $sort1, $sort2
558 my $dbh = C4::Context->dbh;
562 quantity=?,listprice=?,basketno=?,
563 rrp=?,ecost=?,unitprice=?,booksellerinvoicenumber=?,
564 notes=?,sort1=?, sort2=?
565 WHERE ordernumber=? AND biblionumber=?
567 my $sth = $dbh->prepare($query);
569 $title, $quantity, $listprice, $basketno, $rrp,
570 $ecost, $cost, $invoice, $notes, $sort1,
571 $sort2, $ordnum, $bibnum
575 UPDATE aqorderbreakdown
579 $sth = $dbh->prepare($query);
581 unless ( $sth->execute( $bookfund, $ordnum ) )
582 { # zero rows affected [Bug 734]
584 INSERT INTO aqorderbreakdown
585 (ordernumber,bookfundid)
588 $sth = $dbh->prepare($query);
589 $sth->execute( $ordnum, $bookfund );
594 #------------------------------------------------------------#
596 =head3 ModReceiveOrder
600 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
601 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
602 $freight, $bookfund, $rrp);
604 Updates an order, to reflect the fact that it was received, at least
605 in part. All arguments not mentioned below update the fields with the
606 same name in the aqorders table of the Koha database.
608 Updates the order with bibilionumber C<$biblionumber> and ordernumber
611 Also updates the book fund ID in the aqorderbreakdown table.
618 sub ModReceiveOrder {
620 $biblio, $ordnum, $quantrec, $user, $cost,
621 $invoiceno, $freight, $rrp, $bookfund
624 my $dbh = C4::Context->dbh;
627 SET quantityreceived=?,datereceived=now(),booksellerinvoicenumber=?,
628 unitprice=?,freight=?,rrp=?
629 WHERE biblionumber=? AND ordernumber=?
631 my $sth = $dbh->prepare($query);
632 my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblio );
634 ModStatus( $suggestionid, 'AVAILABLE', '', $biblio );
636 $sth->execute( $quantrec, $invoiceno, $cost, $freight, $rrp, $biblio,
640 # Allows libraries to change their bookfund during receiving orders
641 # allows them to adjust budgets
642 if ( C4::Context->preferene("LooseBudgets") ) {
644 UPDATE aqorderbreakdown
648 my $sth = $dbh->prepare($query);
649 $sth->execute( $bookfund, $ordnum );
654 #------------------------------------------------------------#
658 @results = &SearchOrder($search, $biblionumber, $complete);
662 C<$search> may take one of several forms: if it is an ISBN,
663 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
664 order number, C<&ordersearch> returns orders with that order number
665 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
666 to be a space-separated list of search terms; in this case, all of the
667 terms must appear in the title (matching the beginning of title
670 If C<$complete> is C<yes>, the results will include only completed
671 orders. In any case, C<&ordersearch> ignores cancelled orders.
673 C<&ordersearch> returns an array.
674 C<@results> is an array of references-to-hash with the following keys:
691 my ( $search, $id, $biblio, $catview ) = @_;
692 my $dbh = C4::Context->dbh;
693 my @data = split( ' ', $search );
696 @searchterms = ($id);
698 map { push( @searchterms, "$_%", "% $_%" ) } @data;
699 push( @searchterms, $search, $search, $biblio );
703 "SELECT *,biblio.title FROM aqorders,biblioitems,biblio,aqbasket
704 WHERE aqorders.biblioitemnumber = biblioitems.biblioitemnumber AND
705 aqorders.basketno = aqbasket.basketno
706 AND aqbasket.booksellerid = ?
707 AND biblio.biblionumber=aqorders.biblionumber
708 AND ((datecancellationprinted is NULL)
709 OR (datecancellationprinted = '0000-00-00'))
713 map { "(biblio.title like ? or biblio.title like ?)" } @data )
715 . ") OR biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
720 " SELECT *,biblio.title
721 FROM aqorders,biblioitems,biblio,aqbasket
722 WHERE aqorders.biblioitemnumber = biblioitems.biblioitemnumber
723 AND aqorders.basketno = aqbasket.basketno
724 AND biblio.biblionumber=aqorders.biblionumber
725 AND ((datecancellationprinted is NULL)
726 OR (datecancellationprinted = '0000-00-00'))
727 AND (aqorders.quantityreceived < aqorders.quantity OR aqorders.quantityreceived is NULL)
731 map { "(biblio.title like ? OR biblio.title like ?)" } @data )
733 . ") or biblioitems.isbn=? OR (aqorders.ordernumber=? AND aqorders.biblionumber=?)) ";
735 $query .= " GROUP BY aqorders.ordernumber";
736 my $sth = $dbh->prepare($query);
737 $sth->execute(@searchterms);
744 my $sth2 = $dbh->prepare($query2);
747 FROM aqorderbreakdown
750 my $sth3 = $dbh->prepare($query3);
752 while ( my $data = $sth->fetchrow_hashref ) {
753 $sth2->execute( $data->{'biblionumber'} );
754 my $data2 = $sth2->fetchrow_hashref;
755 $data->{'author'} = $data2->{'author'};
756 $data->{'seriestitle'} = $data2->{'seriestitle'};
757 $sth3->execute( $data->{'ordernumber'} );
758 my $data3 = $sth3->fetchrow_hashref;
759 $data->{'branchcode'} = $data3->{'branchcode'};
760 $data->{'bookfundid'} = $data3->{'bookfundid'};
761 push( @results, $data );
769 #------------------------------------------------------------#
775 &DelOrder($biblionumber, $ordernumber);
777 Cancel the order with the given order and biblio numbers. It does not
778 delete any entries in the aqorders table, it merely marks them as
786 my ( $bibnum, $ordnum ) = @_;
787 my $dbh = C4::Context->dbh;
790 SET datecancellationprinted=now()
791 WHERE biblionumber=? AND ordernumber=?
793 my $sth = $dbh->prepare($query);
794 $sth->execute( $bibnum, $ordnum );
803 =head2 FUNCTIONS ABOUT PARCELS
809 #------------------------------------------------------------#
815 @results = &GetParcel($booksellerid, $code, $date);
817 Looks up all of the received items from the supplier with the given
818 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
820 C<@results> is an array of references-to-hash. The keys of each element are fields from
821 the aqorders, biblio, and biblioitems tables of the Koha database.
823 C<@results> is sorted alphabetically by book title.
831 #gets all orders from a certain supplier, orders them alphabetically
832 my ( $supplierid, $code, $datereceived ) = @_;
833 my $dbh = C4::Context->dbh;
836 if $code; # add % if we search on a given code (otherwise, let him empty)
843 aqorders.biblionumber,
845 aqorders.ordernumber,
847 aqorders.quantityreceived,
852 FROM aqorders,aqbasket
853 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
854 WHERE aqbasket.basketno=aqorders.basketno
855 AND aqbasket.booksellerid=?
856 AND aqorders.booksellerinvoicenumber LIKE \"$code\"
857 AND aqorders.datereceived= \'$datereceived\'";
859 if ( C4::Context->preference("IndependantBranches") ) {
860 my $userenv = C4::Context->userenv;
861 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
863 " and (borrowers.branchcode = '"
865 . "' or borrowers.branchcode ='')";
868 $strsth .= " order by aqbasket.basketno";
869 ### parcelinformation : $strsth
870 my $sth = $dbh->prepare($strsth);
871 $sth->execute($supplierid);
872 while ( my $data = $sth->fetchrow_hashref ) {
873 push( @results, $data );
875 ### countparcelbiblio: $count
881 #------------------------------------------------------------#
887 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
888 get a lists of parcels.
893 is the bookseller this function has to get parcels.
896 To know on what criteria the results list has to be ordered.
899 is the booksellerinvoicenumber.
901 =item $datefrom & $dateto
902 to know on what date this function has to filter its search.
905 a pointer on a hash list containing parcel informations as such :
911 =item Number of biblio
913 =item Number of items
920 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
921 my $dbh = C4::Context->dbh;
923 SELECT aqorders.booksellerinvoicenumber,
925 count(DISTINCT biblionumber) AS biblio,
926 sum(quantity) AS itemsexpected,
927 sum(quantityreceived) AS itemsreceived
928 FROM aqorders, aqbasket
929 WHERE aqbasket.basketno = aqorders.basketno
930 AND aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL
933 $strsth .= "and aqorders.booksellerinvoicenumber like \"$code%\" " if ($code);
935 $strsth .= "and datereceived >=" . $dbh->quote($datefrom) . " " if ($datefrom);
937 $strsth .= "and datereceived <=" . $dbh->quote($dateto) . " " if ($dateto);
939 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
940 $strsth .= "order by $order " if ($order);
941 my $sth = $dbh->prepare($strsth);
946 while ( my $data2 = $sth->fetchrow_hashref ) {
947 push @results, $data2;
954 #------------------------------------------------------------#
960 @results = &GetLateOrders;
962 Searches for bookseller with late orders.
965 the table of supplier with late issues. This table is full of hashref.
973 my $supplierid = shift;
976 my $dbh = C4::Context->dbh;
978 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
980 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
983 if ( $dbdriver eq "mysql" ) {
985 SELECT aqbasket.basketno,
986 DATE(aqbasket.closedate) AS orderdate,
987 aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
988 aqorders.rrp AS unitpricesupplier,
989 aqorders.ecost AS unitpricelib,
990 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
991 aqbookfund.bookfundname AS budget,
992 borrowers.branchcode AS branch,
993 aqbooksellers.name AS supplier,
996 biblioitems.publishercode AS publisher,
997 biblioitems.publicationyear,
998 DATEDIFF(CURDATE( ),closedate) AS latesince
1000 (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber)
1001 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber)
1002 LEFT JOIN aqorderbreakdown ON aqorders.ordernumber = aqorderbreakdown.ordernumber)
1003 LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
1004 (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber)
1005 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1006 WHERE aqorders.basketno = aqbasket.basketno
1007 AND (closedate < DATE_SUB(CURDATE( ),INTERVAL $delay DAY))
1008 AND ((datereceived = '' OR datereceived is null)
1009 OR (aqorders.quantityreceived < aqorders.quantity) )
1011 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
1012 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'"
1015 " AND borrowers.branchcode like \'"
1016 . C4::Context->userenv->{branch} . "\'"
1017 if ( C4::Context->preference("IndependantBranches")
1018 && C4::Context->userenv
1019 && C4::Context->userenv->{flags} != 1 );
1020 $strsth .=" HAVING quantity<>0
1021 AND unitpricesupplier<>0
1023 ORDER BY latesince,basketno,borrowers.branchcode, supplier
1028 SELECT aqbasket.basketno,
1029 DATE(aqbasket.closedate) AS orderdate,
1030 aqorders.quantity, aqorders.rrp AS unitpricesupplier,
1031 aqorders.ecost as unitpricelib,
1032 aqorders.quantity * aqorders.rrp AS subtotal
1033 aqbookfund.bookfundname AS budget,
1034 borrowers.branchcode AS branch,
1035 aqbooksellers.name AS supplier,
1038 biblioitems.publishercode AS publisher,
1039 biblioitems.publicationyear,
1040 (CURDATE - closedate) AS latesince
1042 (aqorders LEFT JOIN biblio on biblio.biblionumber = aqorders.biblionumber)
1043 LEFT JOIN biblioitems on biblioitems.biblionumber=biblio.biblionumber)
1044 LEFT JOIN aqorderbreakdown on aqorders.ordernumber = aqorderbreakdown.ordernumber)
1045 LEFT JOIN aqbookfund ON aqorderbreakdown.bookfundid = aqbookfund.bookfundid),
1046 (aqbasket LEFT JOIN borrowers on aqbasket.authorisedby = borrowers.borrowernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1047 WHERE aqorders.basketno = aqbasket.basketno
1048 AND (closedate < (CURDATE -(INTERVAL $delay DAY))
1049 AND ((datereceived = '' OR datereceived is null)
1050 OR (aqorders.quantityreceived < aqorders.quantity) ) ";
1051 $strsth .= " AND aqbasket.booksellerid = $supplierid " if ($supplierid);
1053 $strsth .= " AND borrowers.branchcode like \'" . $branch . "\'" if ($branch);
1054 $strsth .=" AND borrowers.branchcode like \'". C4::Context->userenv->{branch} . "\'"
1055 if (C4::Context->preference("IndependantBranches") && C4::Context->userenv->{flags} != 1 );
1056 $strsth .=" ORDER BY latesince,basketno,borrowers.branchcode, supplier";
1058 my $sth = $dbh->prepare($strsth);
1062 while ( my $data = $sth->fetchrow_hashref ) {
1063 $data->{hilighted} = $hilighted if ( $hilighted > 0 );
1064 $data->{orderdate} = format_date( $data->{orderdate} );
1065 push @results, $data;
1066 $hilighted = -$hilighted;
1072 #------------------------------------------------------------#
1078 (\@order_loop, $total_qty, $total_price, $total_qtyreceived)=&GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on )
1080 this function get the search history.
1087 my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_;
1090 my $total_qtyreceived = 0;
1091 my $total_price = 0;
1093 # don't run the query if there are no parameters (list would be too long for sure !)
1094 if ( $title || $author || $name || $from_placed_on || $to_placed_on ) {
1095 my $dbh = C4::Context->dbh;
1101 name,aqbasket.creationdate,
1102 aqorders.datereceived,
1104 aqorders.quantityreceived,
1106 aqorders.ordernumber
1107 FROM aqorders,aqbasket,aqbooksellers,biblio";
1109 $query .= ",borrowers "
1110 if ( C4::Context->preference("IndependantBranches") );
1113 WHERE aqorders.basketno=aqbasket.basketno
1114 AND aqbasket.booksellerid=aqbooksellers.id
1115 AND biblio.biblionumber=aqorders.biblionumber ";
1117 $query .= " AND aqbasket.authorisedby=borrowers.borrowernumber"
1118 if ( C4::Context->preference("IndependantBranches") );
1120 $query .= " AND biblio.title LIKE " . $dbh->quote( "%" . $title . "%" )
1124 " AND biblio.author LIKE " . $dbh->quote( "%" . $author . "%" )
1127 $query .= " AND name LIKE " . $dbh->quote( "%" . $name . "%" ) if $name;
1129 $query .= " AND creationdate >" . $dbh->quote($from_placed_on)
1132 $query .= " AND creationdate<" . $dbh->quote($to_placed_on)
1135 if ( C4::Context->preference("IndependantBranches") ) {
1136 my $userenv = C4::Context->userenv;
1137 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1139 " AND (borrowers.branchcode = '"
1140 . $userenv->{branch}
1141 . "' OR borrowers.branchcode ='')";
1144 $query .= " ORDER BY booksellerid";
1145 my $sth = $dbh->prepare($query);
1148 while ( my $line = $sth->fetchrow_hashref ) {
1149 $line->{count} = $cnt++;
1150 $line->{toggle} = 1 if $cnt % 2;
1151 push @order_loop, $line;
1152 $line->{creationdate} = format_date( $line->{creationdate} );
1153 $line->{datereceived} = format_date( $line->{datereceived} );
1154 $total_qty += $line->{'quantity'};
1155 $total_qtyreceived += $line->{'quantityreceived'};
1156 $total_price += $line->{'quantity'} * $line->{'ecost'};
1159 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
1162 END { } # module clean-up code here (global destructor)
1172 Koha Developement team <info@koha.org>