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
17 # with Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
26 use C4::Dates qw(format_date format_date_in_iso);
31 use C4::SQLHelper qw(InsertInTable);
36 use vars qw($VERSION @ISA @EXPORT);
39 # set the version for version checking
40 $VERSION = 3.07.00.049;
44 &GetBasket &NewBasket &CloseBasket &DelBasket &ModBasket
46 &GetBasketsByBookseller &GetBasketsByBasketgroup
47 &GetBasketsInfosByBookseller
51 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
52 &GetBasketgroups &ReOpenBasketgroup
54 &NewOrder &DelOrder &ModOrder &GetPendingOrders &GetOrder &GetOrders
55 &GetOrderNumber &GetLateOrders &GetOrderFromItemnumber
56 &SearchOrder &GetHistory &GetRecentAcqui
57 &ModReceiveOrder &ModOrderBiblioitemNumber
60 &NewOrderItem &ModOrderItem &ModItemOrder
62 &GetParcels &GetParcel
63 &GetContracts &GetContract
65 &GetItemnumbersFromOrder
75 sub GetOrderFromItemnumber {
76 my ($itemnumber) = @_;
77 my $dbh = C4::Context->dbh;
80 SELECT * from aqorders LEFT JOIN aqorders_items
81 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
82 WHERE itemnumber = ? |;
84 my $sth = $dbh->prepare($query);
88 $sth->execute($itemnumber);
90 my $order = $sth->fetchrow_hashref;
95 # Returns the itemnumber(s) associated with the ordernumber given in parameter
96 sub GetItemnumbersFromOrder {
97 my ($ordernumber) = @_;
98 my $dbh = C4::Context->dbh;
99 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
100 my $sth = $dbh->prepare($query);
101 $sth->execute($ordernumber);
104 while (my $order = $sth->fetchrow_hashref) {
105 push @tab, $order->{'itemnumber'};
119 C4::Acquisition - Koha functions for dealing with orders and acquisitions
127 The functions in this module deal with acquisitions, managing book
128 orders, basket and parcels.
132 =head2 FUNCTIONS ABOUT BASKETS
136 $aqbasket = &GetBasket($basketnumber);
138 get all basket informations in aqbasket for a given basket
140 B<returns:> informations for a given basket returned as a hashref.
146 my $dbh = C4::Context->dbh;
149 concat( b.firstname,' ',b.surname) AS authorisedbyname,
150 b.branchcode AS branch
152 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
155 my $sth=$dbh->prepare($query);
156 $sth->execute($basketno);
157 my $basket = $sth->fetchrow_hashref;
161 #------------------------------------------------------------#
165 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
166 $basketnote, $basketbooksellernote, $basketcontractnumber );
168 Create a new basket in aqbasket table
172 =item C<$booksellerid> is a foreign key in the aqbasket table
174 =item C<$authorizedby> is the username of who created the basket
178 The other parameters are optional, see ModBasketHeader for more info on them.
182 # FIXME : this function seems to be unused.
185 my ( $booksellerid, $authorisedby, $basketname, $basketnote, $basketbooksellernote, $basketcontractnumber ) = @_;
186 my $dbh = C4::Context->dbh;
189 (creationdate,booksellerid,authorisedby)
190 VALUES (now(),'$booksellerid','$authorisedby')
194 #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
195 my $basket = $dbh->{'mysql_insertid'};
196 ModBasketHeader($basket, $basketname || '', $basketnote || '', $basketbooksellernote || '', $basketcontractnumber || undef, $booksellerid);
200 #------------------------------------------------------------#
204 &CloseBasket($basketno);
206 close a basket (becomes unmodifiable,except for recieves)
212 my $dbh = C4::Context->dbh;
218 my $sth = $dbh->prepare($query);
219 $sth->execute($basketno);
222 #------------------------------------------------------------#
224 =head3 GetBasketAsCSV
226 &GetBasketAsCSV($basketno);
228 Export a basket as CSV
234 my $basket = GetBasket($basketno);
235 my @orders = GetOrders($basketno);
236 my $contract = GetContract($basket->{'contractnumber'});
237 my $csv = Text::CSV->new();
240 # TODO: Translate headers
241 my @headers = qw(contractname ordernumber entrydate isbn author title publishercode collectiontitle notes quantity rrp);
243 $csv->combine(@headers);
244 $output = $csv->string() . "\n";
247 foreach my $order (@orders) {
249 # newlines are not valid characters for Text::CSV combine()
250 $order->{'notes'} =~ s/[\r\n]+//g;
252 $contract->{'contractname'},
253 $order->{'ordernumber'},
254 $order->{'entrydate'},
258 $order->{'publishercode'},
259 $order->{'collectiontitle'},
261 $order->{'quantity'},
264 push (@rows, \@cols);
267 foreach my $row (@rows) {
268 $csv->combine(@$row);
269 $output .= $csv->string() . "\n";
278 =head3 CloseBasketgroup
280 &CloseBasketgroup($basketgroupno);
286 sub CloseBasketgroup {
287 my ($basketgroupno) = @_;
288 my $dbh = C4::Context->dbh;
289 my $sth = $dbh->prepare("
290 UPDATE aqbasketgroups
294 $sth->execute($basketgroupno);
297 #------------------------------------------------------------#
299 =head3 ReOpenBaskergroup($basketgroupno)
301 &ReOpenBaskergroup($basketgroupno);
307 sub ReOpenBasketgroup {
308 my ($basketgroupno) = @_;
309 my $dbh = C4::Context->dbh;
310 my $sth = $dbh->prepare("
311 UPDATE aqbasketgroups
315 $sth->execute($basketgroupno);
318 #------------------------------------------------------------#
323 &DelBasket($basketno);
325 Deletes the basket that has basketno field $basketno in the aqbasket table.
329 =item C<$basketno> is the primary key of the basket in the aqbasket table.
336 my ( $basketno ) = @_;
337 my $query = "DELETE FROM aqbasket WHERE basketno=?";
338 my $dbh = C4::Context->dbh;
339 my $sth = $dbh->prepare($query);
340 $sth->execute($basketno);
344 #------------------------------------------------------------#
348 &ModBasket($basketinfo);
350 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
354 =item C<$basketno> is the primary key of the basket in the aqbasket table.
361 my $basketinfo = shift;
362 my $query = "UPDATE aqbasket SET ";
364 foreach my $key (keys %$basketinfo){
365 if ($key ne 'basketno'){
366 $query .= "$key=?, ";
367 push(@params, $basketinfo->{$key} || undef );
370 # get rid of the "," at the end of $query
371 if (substr($query, length($query)-2) eq ', '){
376 $query .= "WHERE basketno=?";
377 push(@params, $basketinfo->{'basketno'});
378 my $dbh = C4::Context->dbh;
379 my $sth = $dbh->prepare($query);
380 $sth->execute(@params);
384 #------------------------------------------------------------#
386 =head3 ModBasketHeader
388 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
390 Modifies a basket's header.
394 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
396 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
398 =item C<$note> is the "note" field in the "aqbasket" table;
400 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
402 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
404 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
410 sub ModBasketHeader {
411 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid) = @_;
413 my $query = "UPDATE aqbasket SET basketname=?, note=?, booksellernote=?, booksellerid=? WHERE basketno=?";
414 my $dbh = C4::Context->dbh;
415 my $sth = $dbh->prepare($query);
416 $sth->execute($basketname,$note,$booksellernote,$booksellerid,$basketno);
418 if ( $contractnumber ) {
419 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
420 my $sth2 = $dbh->prepare($query2);
421 $sth2->execute($contractnumber,$basketno);
427 #------------------------------------------------------------#
429 =head3 GetBasketsByBookseller
431 @results = &GetBasketsByBookseller($booksellerid, $extra);
433 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
437 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
439 =item C<$extra> is the extra sql parameters, can be
441 $extra->{groupby}: group baskets by column
442 ex. $extra->{groupby} = aqbasket.basketgroupid
443 $extra->{orderby}: order baskets by column
444 $extra->{limit}: limit number of results (can be helpful for pagination)
450 sub GetBasketsByBookseller {
451 my ($booksellerid, $extra) = @_;
452 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
454 if ($extra->{groupby}) {
455 $query .= " GROUP by $extra->{groupby}";
457 if ($extra->{orderby}){
458 $query .= " ORDER by $extra->{orderby}";
460 if ($extra->{limit}){
461 $query .= " LIMIT $extra->{limit}";
464 my $dbh = C4::Context->dbh;
465 my $sth = $dbh->prepare($query);
466 $sth->execute($booksellerid);
467 my $results = $sth->fetchall_arrayref({});
472 =head3 GetBasketsInfosByBookseller
474 my $baskets = GetBasketsInfosByBookseller($supplierid);
476 Returns in a arrayref of hashref all about booksellers baskets, plus:
477 total_biblios: Number of distinct biblios in basket
478 total_items: Number of items in basket
479 expected_items: Number of non-received items in basket
483 sub GetBasketsInfosByBookseller {
484 my ($supplierid) = @_;
486 return unless $supplierid;
488 my $dbh = C4::Context->dbh;
491 SUM(aqorders.quantity) AS total_items,
492 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
493 SUM(IF(aqorders.datereceived IS NULL, aqorders.quantity, 0)) AS expected_items
495 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
496 WHERE booksellerid = ?
497 GROUP BY aqbasket.basketno
499 my $sth = $dbh->prepare($query);
500 $sth->execute($supplierid);
501 return $sth->fetchall_arrayref({});
505 #------------------------------------------------------------#
507 =head3 GetBasketsByBasketgroup
509 $baskets = &GetBasketsByBasketgroup($basketgroupid);
511 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
515 sub GetBasketsByBasketgroup {
516 my $basketgroupid = shift;
517 my $query = "SELECT * FROM aqbasket
518 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?";
519 my $dbh = C4::Context->dbh;
520 my $sth = $dbh->prepare($query);
521 $sth->execute($basketgroupid);
522 my $results = $sth->fetchall_arrayref({});
527 #------------------------------------------------------------#
529 =head3 NewBasketgroup
531 $basketgroupid = NewBasketgroup(\%hashref);
533 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
535 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
537 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
539 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
541 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
543 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
545 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
550 my $basketgroupinfo = shift;
551 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
552 my $query = "INSERT INTO aqbasketgroups (";
554 foreach my $field ('name', 'deliveryplace', 'deliverycomment', 'closed') {
555 if ( $basketgroupinfo->{$field} ) {
556 $query .= "$field, ";
557 push(@params, $basketgroupinfo->{$field});
560 $query .= "booksellerid) VALUES (";
565 push(@params, $basketgroupinfo->{'booksellerid'});
566 my $dbh = C4::Context->dbh;
567 my $sth = $dbh->prepare($query);
568 $sth->execute(@params);
569 my $basketgroupid = $dbh->{'mysql_insertid'};
570 if( $basketgroupinfo->{'basketlist'} ) {
571 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
572 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
573 my $sth2 = $dbh->prepare($query2);
574 $sth2->execute($basketgroupid, $basketno);
577 return $basketgroupid;
580 #------------------------------------------------------------#
582 =head3 ModBasketgroup
584 ModBasketgroup(\%hashref);
586 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
588 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
590 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
592 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
594 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
596 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
598 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
600 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
605 my $basketgroupinfo = shift;
606 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
607 my $dbh = C4::Context->dbh;
608 my $query = "UPDATE aqbasketgroups SET ";
610 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
611 if ( defined $basketgroupinfo->{$field} ) {
612 $query .= "$field=?, ";
613 push(@params, $basketgroupinfo->{$field});
618 $query .= " WHERE id=?";
619 push(@params, $basketgroupinfo->{'id'});
620 my $sth = $dbh->prepare($query);
621 $sth->execute(@params);
623 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
624 $sth->execute($basketgroupinfo->{'id'});
626 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
627 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
628 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
629 $sth->execute($basketgroupinfo->{'id'}, $basketno);
636 #------------------------------------------------------------#
638 =head3 DelBasketgroup
640 DelBasketgroup($basketgroupid);
642 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
646 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
653 my $basketgroupid = shift;
654 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
655 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
656 my $dbh = C4::Context->dbh;
657 my $sth = $dbh->prepare($query);
658 $sth->execute($basketgroupid);
662 #------------------------------------------------------------#
665 =head2 FUNCTIONS ABOUT ORDERS
667 =head3 GetBasketgroup
669 $basketgroup = &GetBasketgroup($basketgroupid);
671 Returns a reference to the hash containing all infermation about the basketgroup.
676 my $basketgroupid = shift;
677 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
678 my $query = "SELECT * FROM aqbasketgroups WHERE id=?";
679 my $dbh = C4::Context->dbh;
680 my $sth = $dbh->prepare($query);
681 $sth->execute($basketgroupid);
682 my $result = $sth->fetchrow_hashref;
687 #------------------------------------------------------------#
689 =head3 GetBasketgroups
691 $basketgroups = &GetBasketgroups($booksellerid);
693 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
697 sub GetBasketgroups {
698 my $booksellerid = shift;
699 die "bookseller id is required to edit a basketgroup" unless $booksellerid;
700 my $query = "SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY `id` DESC";
701 my $dbh = C4::Context->dbh;
702 my $sth = $dbh->prepare($query);
703 $sth->execute($booksellerid);
704 my $results = $sth->fetchall_arrayref({});
709 #------------------------------------------------------------#
711 =head2 FUNCTIONS ABOUT ORDERS
715 #------------------------------------------------------------#
717 =head3 GetPendingOrders
719 $orders = &GetPendingOrders($booksellerid, $grouped, $owner);
721 Finds pending orders from the bookseller with the given ID. Ignores
722 completed and cancelled orders.
724 C<$booksellerid> contains the bookseller identifier
725 C<$grouped> contains 0 or 1. 0 means returns the list, 1 means return the total
726 C<$owner> contains 0 or 1. 0 means any owner. 1 means only the list of orders entered by the user itself.
728 C<$orders> is a reference-to-array; each element is a
729 reference-to-hash with the following fields:
730 C<$grouped> is a boolean that, if set to 1 will group all order lines of the same basket
731 in a single result line
735 =item C<authorizedby>
743 These give the value of the corresponding field in the aqorders table
744 of the Koha database.
746 Results are ordered from most to least recent.
750 sub GetPendingOrders {
751 my ($supplierid,$grouped,$owner,$basketno) = @_;
752 my $dbh = C4::Context->dbh;
754 SELECT ".($grouped?"count(*),":"")."aqbasket.basketno,
755 surname,firstname,biblio.*,biblioitems.isbn,
756 aqbasket.closedate, aqbasket.creationdate, aqbasket.basketname,
759 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
760 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
761 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
762 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
764 AND (quantity > quantityreceived OR quantityreceived is NULL)
765 AND datecancellationprinted IS NULL";
766 my @query_params = ( $supplierid );
767 my $userenv = C4::Context->userenv;
768 if ( C4::Context->preference("IndependantBranches") ) {
769 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
770 $strsth .= " and (borrowers.branchcode = ?
771 or borrowers.branchcode = '')";
772 push @query_params, $userenv->{branch};
776 $strsth .= " AND aqbasket.authorisedby=? ";
777 push @query_params, $userenv->{'number'};
780 $strsth .= " AND aqbasket.basketno=? ";
781 push @query_params, $basketno;
783 $strsth .= " group by aqbasket.basketno" if $grouped;
784 $strsth .= " order by aqbasket.basketno";
786 my $sth = $dbh->prepare($strsth);
787 $sth->execute( @query_params );
788 my $results = $sth->fetchall_arrayref({});
793 #------------------------------------------------------------#
797 @orders = &GetOrders($basketnumber, $orderby);
799 Looks up the pending (non-cancelled) orders with the given basket
800 number. If C<$booksellerID> is non-empty, only orders from that seller
804 C<&basket> returns a two-element array. C<@orders> is an array of
805 references-to-hash, whose keys are the fields from the aqorders,
806 biblio, and biblioitems tables in the Koha database.
811 my ( $basketno, $orderby ) = @_;
812 my $dbh = C4::Context->dbh;
814 SELECT biblio.*,biblioitems.*,
819 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
820 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
821 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
823 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
826 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
827 $query .= " ORDER BY $orderby";
828 my $sth = $dbh->prepare($query);
829 $sth->execute($basketno);
830 my $results = $sth->fetchall_arrayref({});
835 #------------------------------------------------------------#
837 =head3 GetOrderNumber
839 $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
841 Looks up the ordernumber with the given biblionumber and biblioitemnumber.
843 Returns the number of this order.
847 =item C<$ordernumber> is the order number.
854 my ( $biblionumber,$biblioitemnumber ) = @_;
855 my $dbh = C4::Context->dbh;
860 AND biblioitemnumber=?
862 my $sth = $dbh->prepare($query);
863 $sth->execute( $biblionumber, $biblioitemnumber );
865 return $sth->fetchrow;
868 #------------------------------------------------------------#
872 $order = &GetOrder($ordernumber);
874 Looks up an order by order number.
876 Returns a reference-to-hash describing the order. The keys of
877 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
882 my ($ordernumber) = @_;
883 my $dbh = C4::Context->dbh;
885 SELECT biblioitems.*, biblio.*, aqorders.*
887 LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber
888 LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber
889 WHERE aqorders.ordernumber=?
892 my $sth= $dbh->prepare($query);
893 $sth->execute($ordernumber);
894 my $data = $sth->fetchrow_hashref;
899 #------------------------------------------------------------#
903 &NewOrder(\%hashref);
905 Adds a new order to the database. Any argument that isn't described
906 below is the new value of the field with the same name in the aqorders
907 table of the Koha database.
911 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
913 =item $hashref->{'ordernumber'} is a "minimum order number."
915 =item $hashref->{'budgetdate'} is effectively ignored.
916 If it's undef (anything false) or the string 'now', the current day is used.
917 Else, the upcoming July 1st is used.
919 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
921 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
923 =item defaults entrydate to Now
925 The following keys are used: "biblionumber", "title", "basketno", "quantity", "notes", "biblioitemnumber", "rrp", "ecost", "gst", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "bookfundid".
932 my $orderinfo = shift;
933 #### ------------------------------
934 my $dbh = C4::Context->dbh;
938 # if these parameters are missing, we can't continue
939 for my $key (qw/basketno quantity biblionumber budget_id/) {
940 croak "Mandatory parameter $key missing" unless $orderinfo->{$key};
943 if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) {
944 $orderinfo->{'subscription'} = 1;
946 $orderinfo->{'subscription'} = 0;
948 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
949 if (!$orderinfo->{quantityreceived}) {
950 $orderinfo->{quantityreceived} = 0;
953 my $ordernumber=InsertInTable("aqorders",$orderinfo);
954 return ( $orderinfo->{'basketno'}, $ordernumber );
959 #------------------------------------------------------------#
968 my ($itemnumber, $ordernumber) = @_;
969 my $dbh = C4::Context->dbh;
971 INSERT INTO aqorders_items
972 (itemnumber, ordernumber)
975 my $sth = $dbh->prepare($query);
976 $sth->execute( $itemnumber, $ordernumber);
979 #------------------------------------------------------------#
983 &ModOrder(\%hashref);
985 Modifies an existing order. Updates the order with order number
986 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
987 other keys of the hash update the fields with the same name in the aqorders
988 table of the Koha database.
993 my $orderinfo = shift;
995 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
996 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
998 my $dbh = C4::Context->dbh;
1001 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1002 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1004 # delete($orderinfo->{'branchcode'});
1005 # the hash contains a lot of entries not in aqorders, so get the columns ...
1006 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1008 my $colnames = $sth->{NAME};
1009 my $query = "UPDATE aqorders SET ";
1011 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1012 # ... and skip hash entries that are not in the aqorders table
1013 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1014 next unless grep(/^$orderinfokey$/, @$colnames);
1015 $query .= "$orderinfokey=?, ";
1016 push(@params, $orderinfo->{$orderinfokey});
1019 $query .= "timestamp=NOW() WHERE ordernumber=?";
1020 # push(@params, $specorderinfo{'ordernumber'});
1021 push(@params, $orderinfo->{'ordernumber'} );
1022 $sth = $dbh->prepare($query);
1023 $sth->execute(@params);
1027 #------------------------------------------------------------#
1031 &ModOrderItem(\%hashref);
1033 Modifies the itemnumber in the aqorders_items table. The input hash needs three entities:
1037 =item - itemnumber: the old itemnumber
1038 =item - ordernumber: the order this item is attached to
1039 =item - newitemnumber: the new itemnumber we want to attach the line to
1046 my $orderiteminfo = shift;
1047 if (! $orderiteminfo->{'ordernumber'} || ! $orderiteminfo->{'itemnumber'} || ! $orderiteminfo->{'newitemnumber'}){
1048 die "Ordernumber, itemnumber and newitemnumber is required";
1051 my $dbh = C4::Context->dbh;
1053 my $query = "UPDATE aqorders_items set itemnumber=? where itemnumber=? and ordernumber=?";
1054 my @params = ($orderiteminfo->{'newitemnumber'}, $orderiteminfo->{'itemnumber'}, $orderiteminfo->{'ordernumber'});
1055 my $sth = $dbh->prepare($query);
1056 $sth->execute(@params);
1062 ModItemOrder($itemnumber, $ordernumber);
1064 Modifies the ordernumber of an item in aqorders_items.
1069 my ($itemnumber, $ordernumber) = @_;
1071 return unless ($itemnumber and $ordernumber);
1073 my $dbh = C4::Context->dbh;
1075 UPDATE aqorders_items
1077 WHERE itemnumber = ?
1079 my $sth = $dbh->prepare($query);
1080 return $sth->execute($ordernumber, $itemnumber);
1083 #------------------------------------------------------------#
1086 =head3 ModOrderBibliotemNumber
1088 &ModOrderBiblioitemNumber($biblioitemnumber,$ordernumber, $biblionumber);
1090 Modifies the biblioitemnumber for an existing order.
1091 Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
1095 #FIXME: is this used at all?
1096 sub ModOrderBiblioitemNumber {
1097 my ($biblioitemnumber,$ordernumber, $biblionumber) = @_;
1098 my $dbh = C4::Context->dbh;
1101 SET biblioitemnumber = ?
1102 WHERE ordernumber = ?
1103 AND biblionumber = ?";
1104 my $sth = $dbh->prepare($query);
1105 $sth->execute( $biblioitemnumber, $ordernumber, $biblionumber );
1108 =head3 GetCancelledOrders
1110 my @orders = GetCancelledOrders($basketno, $orderby);
1112 Returns cancelled orders for a basket
1116 sub GetCancelledOrders {
1117 my ( $basketno, $orderby ) = @_;
1119 return () unless $basketno;
1121 my $dbh = C4::Context->dbh;
1123 SELECT biblio.*, biblioitems.*, aqorders.*, aqbudgets.*
1125 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1126 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1127 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1129 AND (datecancellationprinted IS NOT NULL
1130 AND datecancellationprinted <> '0000-00-00')
1133 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1135 $query .= " ORDER BY $orderby";
1136 my $sth = $dbh->prepare($query);
1137 $sth->execute($basketno);
1138 my $results = $sth->fetchall_arrayref( {} );
1144 #------------------------------------------------------------#
1146 =head3 ModReceiveOrder
1148 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
1149 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
1150 $freight, $bookfund, $rrp);
1152 Updates an order, to reflect the fact that it was received, at least
1153 in part. All arguments not mentioned below update the fields with the
1154 same name in the aqorders table of the Koha database.
1156 If a partial order is received, splits the order into two. The received
1157 portion must have a booksellerinvoicenumber.
1159 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1165 sub ModReceiveOrder {
1167 $biblionumber, $ordernumber, $quantrec, $user, $cost,
1168 $invoiceno, $freight, $rrp, $budget_id, $datereceived, $received_items
1171 my $dbh = C4::Context->dbh;
1172 $datereceived = C4::Dates->output('iso') unless $datereceived;
1173 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1174 if ($suggestionid) {
1175 ModSuggestion( {suggestionid=>$suggestionid,
1176 STATUS=>'AVAILABLE',
1177 biblionumber=> $biblionumber}
1181 my $sth=$dbh->prepare("
1182 SELECT * FROM aqorders
1183 WHERE biblionumber=? AND aqorders.ordernumber=?");
1185 $sth->execute($biblionumber,$ordernumber);
1186 my $order = $sth->fetchrow_hashref();
1189 if ( $order->{quantity} > $quantrec ) {
1190 $sth=$dbh->prepare("
1192 SET quantityreceived=?
1194 , booksellerinvoicenumber=?
1199 WHERE biblionumber=? AND ordernumber=?");
1201 $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$quantrec,$biblionumber,$ordernumber);
1204 # create a new order for the remaining items, and set its bookfund.
1205 foreach my $orderkey ( "linenumber", "allocation" ) {
1206 delete($order->{'$orderkey'});
1208 $order->{'quantity'} -= $quantrec;
1209 $order->{'quantityreceived'} = 0;
1210 my $newOrder = NewOrder($order);
1211 # Change ordernumber in aqorders_items for items not received
1212 my @orderitems = GetItemnumbersFromOrder( $order->{'ordernumber'} );
1213 my $count = scalar @orderitems;
1215 for (my $i=0; $i<$count; $i++){
1216 foreach (@$received_items){
1217 splice (@orderitems, $i, 1) if ($orderitems[$i] == $_);
1220 foreach (@orderitems) {
1221 ModItemOrder($_, $newOrder);
1224 $sth=$dbh->prepare("update aqorders
1225 set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
1226 unitprice=?,freight=?,rrp=?
1227 where biblionumber=? and ordernumber=?");
1228 $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$biblionumber,$ordernumber);
1231 return $datereceived;
1233 #------------------------------------------------------------#
1237 @results = &SearchOrder($search, $biblionumber, $complete);
1239 Searches for orders.
1241 C<$search> may take one of several forms: if it is an ISBN,
1242 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
1243 order number, C<&ordersearch> returns orders with that order number
1244 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
1245 to be a space-separated list of search terms; in this case, all of the
1246 terms must appear in the title (matching the beginning of title
1249 If C<$complete> is C<yes>, the results will include only completed
1250 orders. In any case, C<&ordersearch> ignores cancelled orders.
1252 C<&ordersearch> returns an array.
1253 C<@results> is an array of references-to-hash with the following keys:
1259 =item C<seriestitle>
1270 #### -------- SearchOrder-------------------------------
1271 my ( $ordernumber, $search, $ean, $supplierid, $basket ) = @_;
1273 my $dbh = C4::Context->dbh;
1278 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1279 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1280 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1281 WHERE (datecancellationprinted is NULL)";
1284 $query .= " AND (aqorders.ordernumber=?)";
1285 push @args, $ordernumber;
1288 $query .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)";
1289 push @args, ("%$search%","%$search%","%$search%");
1292 $query .= " AND biblioitems.ean = ?";
1296 $query .= "AND aqbasket.booksellerid = ?";
1297 push @args, $supplierid;
1300 $query .= "AND aqorders.basketno = ?";
1301 push @args, $basket;
1304 my $sth = $dbh->prepare($query);
1305 $sth->execute(@args);
1306 my $results = $sth->fetchall_arrayref({});
1311 #------------------------------------------------------------#
1315 &DelOrder($biblionumber, $ordernumber);
1317 Cancel the order with the given order and biblio numbers. It does not
1318 delete any entries in the aqorders table, it merely marks them as
1324 my ( $bibnum, $ordernumber ) = @_;
1325 my $dbh = C4::Context->dbh;
1328 SET datecancellationprinted=now()
1329 WHERE biblionumber=? AND ordernumber=?
1331 my $sth = $dbh->prepare($query);
1332 $sth->execute( $bibnum, $ordernumber );
1334 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1335 foreach my $itemnumber (@itemnumbers){
1336 C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
1341 =head2 FUNCTIONS ABOUT PARCELS
1345 #------------------------------------------------------------#
1349 @results = &GetParcel($booksellerid, $code, $date);
1351 Looks up all of the received items from the supplier with the given
1352 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1354 C<@results> is an array of references-to-hash. The keys of each element are fields from
1355 the aqorders, biblio, and biblioitems tables of the Koha database.
1357 C<@results> is sorted alphabetically by book title.
1362 #gets all orders from a certain supplier, orders them alphabetically
1363 my ( $supplierid, $code, $datereceived ) = @_;
1364 my $dbh = C4::Context->dbh;
1367 if $code; # add % if we search on a given code (otherwise, let him empty)
1369 SELECT authorisedby,
1374 aqorders.biblionumber,
1375 aqorders.ordernumber,
1377 aqorders.quantityreceived,
1384 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1385 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1386 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1388 aqbasket.booksellerid = ?
1389 AND aqorders.booksellerinvoicenumber LIKE ?
1390 AND aqorders.datereceived = ? ";
1392 my @query_params = ( $supplierid, $code, $datereceived );
1393 if ( C4::Context->preference("IndependantBranches") ) {
1394 my $userenv = C4::Context->userenv;
1395 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1396 $strsth .= " and (borrowers.branchcode = ?
1397 or borrowers.branchcode = '')";
1398 push @query_params, $userenv->{branch};
1401 $strsth .= " ORDER BY aqbasket.basketno";
1402 # ## parcelinformation : $strsth
1403 my $sth = $dbh->prepare($strsth);
1404 $sth->execute( @query_params );
1405 while ( my $data = $sth->fetchrow_hashref ) {
1406 push( @results, $data );
1408 # ## countparcelbiblio: scalar(@results)
1414 #------------------------------------------------------------#
1418 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1420 get a lists of parcels.
1427 is the bookseller this function has to get parcels.
1430 To know on what criteria the results list has to be ordered.
1433 is the booksellerinvoicenumber.
1435 =item $datefrom & $dateto
1436 to know on what date this function has to filter its search.
1441 a pointer on a hash list containing parcel informations as such :
1447 =item Last operation
1449 =item Number of biblio
1451 =item Number of items
1458 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1459 my $dbh = C4::Context->dbh;
1460 my @query_params = ();
1462 SELECT aqorders.booksellerinvoicenumber,
1463 datereceived,purchaseordernumber,
1464 count(DISTINCT biblionumber) AS biblio,
1465 sum(quantity) AS itemsexpected,
1466 sum(quantityreceived) AS itemsreceived
1467 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1468 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1470 push @query_params, $bookseller;
1472 if ( defined $code ) {
1473 $strsth .= ' and aqorders.booksellerinvoicenumber like ? ';
1474 # add a % to the end of the code to allow stemming.
1475 push @query_params, "$code%";
1478 if ( defined $datefrom ) {
1479 $strsth .= ' and datereceived >= ? ';
1480 push @query_params, $datefrom;
1483 if ( defined $dateto ) {
1484 $strsth .= 'and datereceived <= ? ';
1485 push @query_params, $dateto;
1488 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
1490 # can't use a placeholder to place this column name.
1491 # but, we could probably be checking to make sure it is a column that will be fetched.
1492 $strsth .= "order by $order " if ($order);
1494 my $sth = $dbh->prepare($strsth);
1496 $sth->execute( @query_params );
1497 my $results = $sth->fetchall_arrayref({});
1502 #------------------------------------------------------------#
1504 =head3 GetLateOrders
1506 @results = &GetLateOrders;
1508 Searches for bookseller with late orders.
1511 the table of supplier with late issues. This table is full of hashref.
1517 my $supplierid = shift;
1519 my $estimateddeliverydatefrom = shift;
1520 my $estimateddeliverydateto = shift;
1522 my $dbh = C4::Context->dbh;
1524 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
1525 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
1527 my @query_params = ();
1529 SELECT aqbasket.basketno,
1530 aqorders.ordernumber,
1531 DATE(aqbasket.closedate) AS orderdate,
1532 aqorders.rrp AS unitpricesupplier,
1533 aqorders.ecost AS unitpricelib,
1534 aqorders.claims_count AS claims_count,
1535 aqorders.claimed_date AS claimed_date,
1536 aqbudgets.budget_name AS budget,
1537 borrowers.branchcode AS branch,
1538 aqbooksellers.name AS supplier,
1539 aqbooksellers.id AS supplierid,
1540 biblio.author, biblio.title,
1541 biblioitems.publishercode AS publisher,
1542 biblioitems.publicationyear,
1543 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1547 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1548 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1549 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1550 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1551 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1552 WHERE aqorders.basketno = aqbasket.basketno
1553 AND ( datereceived = ''
1554 OR datereceived IS NULL
1555 OR aqorders.quantityreceived < aqorders.quantity
1557 AND aqbasket.closedate IS NOT NULL
1558 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
1561 if ($dbdriver eq "mysql") {
1563 aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
1564 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1565 DATEDIFF(CAST(now() AS date),closedate) AS latesince
1567 if ( defined $delay ) {
1568 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
1569 push @query_params, $delay;
1572 HAVING quantity <> 0
1573 AND unitpricesupplier <> 0
1574 AND unitpricelib <> 0
1577 # FIXME: account for IFNULL as above
1579 aqorders.quantity AS quantity,
1580 aqorders.quantity * aqorders.rrp AS subtotal,
1581 (CAST(now() AS date) - closedate) AS latesince
1583 if ( defined $delay ) {
1584 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
1585 push @query_params, $delay;
1588 if (defined $supplierid) {
1589 $from .= ' AND aqbasket.booksellerid = ? ';
1590 push @query_params, $supplierid;
1592 if (defined $branch) {
1593 $from .= ' AND borrowers.branchcode LIKE ? ';
1594 push @query_params, $branch;
1596 if ( defined $estimateddeliverydatefrom ) {
1598 AND aqbooksellers.deliverytime IS NOT NULL
1599 AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
1600 push @query_params, $estimateddeliverydatefrom;
1602 if ( defined $estimateddeliverydatefrom and defined $estimateddeliverydateto ) {
1603 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
1604 push @query_params, $estimateddeliverydateto;
1605 } elsif ( defined $estimateddeliverydatefrom ) {
1606 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
1608 if (C4::Context->preference("IndependantBranches")
1609 && C4::Context->userenv
1610 && C4::Context->userenv->{flags} != 1 ) {
1611 $from .= ' AND borrowers.branchcode LIKE ? ';
1612 push @query_params, C4::Context->userenv->{branch};
1614 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
1615 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
1616 my $sth = $dbh->prepare($query);
1617 $sth->execute(@query_params);
1619 while (my $data = $sth->fetchrow_hashref) {
1620 $data->{orderdate} = format_date($data->{orderdate});
1621 $data->{claimed_date} = format_date($data->{claimed_date});
1622 push @results, $data;
1627 #------------------------------------------------------------#
1631 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
1633 Retreives some acquisition history information
1641 basket - search both basket name and number
1642 booksellerinvoicenumber
1645 $order_loop is a list of hashrefs that each look like this:
1647 'author' => 'Twain, Mark',
1649 'biblionumber' => '215',
1651 'creationdate' => 'MM/DD/YYYY',
1652 'datereceived' => undef,
1655 'invoicenumber' => undef,
1657 'ordernumber' => '1',
1659 'quantityreceived' => undef,
1660 'title' => 'The Adventures of Huckleberry Finn'
1662 $total_qty is the sum of all of the quantities in $order_loop
1663 $total_price is the cost of each in $order_loop times the quantity
1664 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
1669 # don't run the query if there are no parameters (list would be too long for sure !)
1670 croak "No search params" unless @_;
1672 my $title = $params{title};
1673 my $author = $params{author};
1674 my $isbn = $params{isbn};
1675 my $ean = $params{ean};
1676 my $name = $params{name};
1677 my $from_placed_on = $params{from_placed_on};
1678 my $to_placed_on = $params{to_placed_on};
1679 my $basket = $params{basket};
1680 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
1683 my $total_qtyreceived = 0;
1684 my $total_price = 0;
1686 my $dbh = C4::Context->dbh;
1694 aqbasket.basketname,
1695 aqbasket.basketgroupid,
1696 aqbasketgroups.name as groupname,
1698 aqbasket.creationdate,
1699 aqorders.datereceived,
1701 aqorders.quantityreceived,
1703 aqorders.ordernumber,
1704 aqorders.booksellerinvoicenumber as invoicenumber,
1705 aqbooksellers.id as id,
1706 aqorders.biblionumber
1708 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
1709 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
1710 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
1711 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
1712 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber";
1714 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
1715 if ( C4::Context->preference("IndependantBranches") );
1717 $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
1719 my @query_params = ();
1722 $query .= " AND biblio.title LIKE ? ";
1723 $title =~ s/\s+/%/g;
1724 push @query_params, "%$title%";
1728 $query .= " AND biblio.author LIKE ? ";
1729 push @query_params, "%$author%";
1733 $query .= " AND biblioitems.isbn LIKE ? ";
1734 push @query_params, "%$isbn%";
1736 if ( defined $ean and $ean ) {
1737 $query .= " AND biblioitems.ean = ? ";
1738 push @query_params, "$ean";
1741 $query .= " AND aqbooksellers.name LIKE ? ";
1742 push @query_params, "%$name%";
1745 if ( $from_placed_on ) {
1746 $query .= " AND creationdate >= ? ";
1747 push @query_params, $from_placed_on;
1750 if ( $to_placed_on ) {
1751 $query .= " AND creationdate <= ? ";
1752 push @query_params, $to_placed_on;
1756 if ($basket =~ m/^\d+$/) {
1757 $query .= " AND aqorders.basketno = ? ";
1758 push @query_params, $basket;
1760 $query .= " AND aqbasket.basketname LIKE ? ";
1761 push @query_params, "%$basket%";
1765 if ($booksellerinvoicenumber) {
1766 $query .= " AND (aqorders.booksellerinvoicenumber LIKE ? OR aqbasket.booksellerinvoicenumber LIKE ?)";
1767 push @query_params, "%$booksellerinvoicenumber%", "%$booksellerinvoicenumber%";
1770 if ( C4::Context->preference("IndependantBranches") ) {
1771 my $userenv = C4::Context->userenv;
1772 if ( $userenv && ($userenv->{flags} || 0) != 1 ) {
1773 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
1774 push @query_params, $userenv->{branch};
1777 $query .= " ORDER BY id";
1778 my $sth = $dbh->prepare($query);
1779 $sth->execute( @query_params );
1781 while ( my $line = $sth->fetchrow_hashref ) {
1782 $line->{count} = $cnt++;
1783 $line->{toggle} = 1 if $cnt % 2;
1784 push @order_loop, $line;
1785 $total_qty += $line->{'quantity'};
1786 $total_qtyreceived += $line->{'quantityreceived'};
1787 $total_price += $line->{'quantity'} * $line->{'ecost'};
1789 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
1792 =head2 GetRecentAcqui
1794 $results = GetRecentAcqui($days);
1796 C<$results> is a ref to a table which containts hashref
1800 sub GetRecentAcqui {
1802 my $dbh = C4::Context->dbh;
1806 ORDER BY timestamp DESC
1809 my $sth = $dbh->prepare($query);
1811 my $results = $sth->fetchall_arrayref({});
1817 $contractlist = &GetContracts($booksellerid, $activeonly);
1819 Looks up the contracts that belong to a bookseller
1821 Returns a list of contracts
1825 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
1827 =item C<$activeonly> if exists get only contracts that are still active.
1834 my ( $booksellerid, $activeonly ) = @_;
1835 my $dbh = C4::Context->dbh;
1837 if (! $activeonly) {
1841 WHERE booksellerid=?
1846 WHERE booksellerid=?
1847 AND contractenddate >= CURDATE( )";
1849 my $sth = $dbh->prepare($query);
1850 $sth->execute( $booksellerid );
1852 while (my $data = $sth->fetchrow_hashref ) {
1853 push(@results, $data);
1859 #------------------------------------------------------------#
1863 $contract = &GetContract($contractID);
1865 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
1872 my ( $contractno ) = @_;
1873 my $dbh = C4::Context->dbh;
1877 WHERE contractnumber=?
1880 my $sth = $dbh->prepare($query);
1881 $sth->execute( $contractno );
1882 my $result = $sth->fetchrow_hashref;
1890 &AddClaim($ordernumber);
1892 Add a claim for an order
1898 my ($ordernumber) = @_;
1899 my $dbh = C4::Context->dbh;
1902 claims_count = claims_count + 1,
1903 claimed_date = CURDATE()
1904 WHERE ordernumber = ?
1906 my $sth = $dbh->prepare($query);
1907 $sth->execute($ordernumber);
1916 Koha Development Team <http://koha-community.org/>