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);
32 use C4::Bookseller qw(GetBookSellerFromId);
33 use C4::Templates qw(gettemplate);
38 use vars qw($VERSION @ISA @EXPORT);
41 # set the version for version checking
42 $VERSION = 3.07.00.049;
46 &GetBasket &NewBasket &CloseBasket &DelBasket &ModBasket
47 &GetBasketAsCSV &GetBasketGroupAsCSV
48 &GetBasketsByBookseller &GetBasketsByBasketgroup
49 &GetBasketsInfosByBookseller
53 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
54 &GetBasketgroups &ReOpenBasketgroup
56 &NewOrder &DelOrder &ModOrder &GetPendingOrders &GetOrder &GetOrders
57 &GetOrderNumber &GetLateOrders &GetOrderFromItemnumber
58 &SearchOrder &GetHistory &GetRecentAcqui
59 &ModReceiveOrder &ModOrderBiblioitemNumber
62 &NewOrderItem &ModOrderItem &ModItemOrder
64 &GetParcels &GetParcel
65 &GetContracts &GetContract
67 &GetItemnumbersFromOrder
77 sub GetOrderFromItemnumber {
78 my ($itemnumber) = @_;
79 my $dbh = C4::Context->dbh;
82 SELECT * from aqorders LEFT JOIN aqorders_items
83 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
84 WHERE itemnumber = ? |;
86 my $sth = $dbh->prepare($query);
90 $sth->execute($itemnumber);
92 my $order = $sth->fetchrow_hashref;
97 # Returns the itemnumber(s) associated with the ordernumber given in parameter
98 sub GetItemnumbersFromOrder {
99 my ($ordernumber) = @_;
100 my $dbh = C4::Context->dbh;
101 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
102 my $sth = $dbh->prepare($query);
103 $sth->execute($ordernumber);
106 while (my $order = $sth->fetchrow_hashref) {
107 push @tab, $order->{'itemnumber'};
121 C4::Acquisition - Koha functions for dealing with orders and acquisitions
129 The functions in this module deal with acquisitions, managing book
130 orders, basket and parcels.
134 =head2 FUNCTIONS ABOUT BASKETS
138 $aqbasket = &GetBasket($basketnumber);
140 get all basket informations in aqbasket for a given basket
142 B<returns:> informations for a given basket returned as a hashref.
148 my $dbh = C4::Context->dbh;
151 concat( b.firstname,' ',b.surname) AS authorisedbyname,
152 b.branchcode AS branch
154 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
157 my $sth=$dbh->prepare($query);
158 $sth->execute($basketno);
159 my $basket = $sth->fetchrow_hashref;
163 #------------------------------------------------------------#
167 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
168 $basketnote, $basketbooksellernote, $basketcontractnumber );
170 Create a new basket in aqbasket table
174 =item C<$booksellerid> is a foreign key in the aqbasket table
176 =item C<$authorizedby> is the username of who created the basket
180 The other parameters are optional, see ModBasketHeader for more info on them.
184 # FIXME : this function seems to be unused.
187 my ( $booksellerid, $authorisedby, $basketname, $basketnote, $basketbooksellernote, $basketcontractnumber ) = @_;
188 my $dbh = C4::Context->dbh;
191 (creationdate,booksellerid,authorisedby)
192 VALUES (now(),'$booksellerid','$authorisedby')
196 #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
197 my $basket = $dbh->{'mysql_insertid'};
198 ModBasketHeader($basket, $basketname || '', $basketnote || '', $basketbooksellernote || '', $basketcontractnumber || undef, $booksellerid);
202 #------------------------------------------------------------#
206 &CloseBasket($basketno);
208 close a basket (becomes unmodifiable,except for recieves)
214 my $dbh = C4::Context->dbh;
220 my $sth = $dbh->prepare($query);
221 $sth->execute($basketno);
224 #------------------------------------------------------------#
226 =head3 GetBasketAsCSV
228 &GetBasketAsCSV($basketno);
230 Export a basket as CSV
232 $cgi parameter is needed for column name translation
237 my ($basketno, $cgi) = @_;
238 my $basket = GetBasket($basketno);
239 my @orders = GetOrders($basketno);
240 my $contract = GetContract($basket->{'contractnumber'});
242 my $template = C4::Templates::gettemplate("acqui/csv/basket.tmpl", "intranet", $cgi);
245 foreach my $order (@orders) {
246 my $bd = GetBiblioData( $order->{'biblionumber'} );
248 contractname => $contract->{'contractname'},
249 ordernumber => $order->{'ordernumber'},
250 entrydate => $order->{'entrydate'},
251 isbn => $order->{'isbn'},
252 author => $bd->{'author'},
253 title => $bd->{'title'},
254 publicationyear => $bd->{'publicationyear'},
255 publishercode => $bd->{'publishercode'},
256 collectiontitle => $bd->{'collectiontitle'},
257 notes => $order->{'notes'},
258 quantity => $order->{'quantity'},
259 rrp => $order->{'rrp'},
260 deliveryplace => $basket->{'deliveryplace'},
261 billingplace => $basket->{'billingplace'}
264 contractname author title publishercode collectiontitle notes
265 deliveryplace billingplace
267 # Double the quotes to not be interpreted as a field end
268 $row->{$_} =~ s/"/""/g if $row->{$_};
274 if(defined $a->{publishercode} and defined $b->{publishercode}) {
275 $a->{publishercode} cmp $b->{publishercode};
279 $template->param(rows => \@rows);
281 return $template->output;
285 =head3 GetBasketGroupAsCSV
289 &GetBasketGroupAsCSV($basketgroupid);
291 Export a basket group as CSV
293 $cgi parameter is needed for column name translation
299 sub GetBasketGroupAsCSV {
300 my ($basketgroupid, $cgi) = @_;
301 my $baskets = GetBasketsByBasketgroup($basketgroupid);
303 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tmpl', 'intranet', $cgi);
306 for my $basket (@$baskets) {
307 my @orders = GetOrders( $$basket{basketno} );
308 my $contract = GetContract( $$basket{contractnumber} );
309 my $bookseller = GetBookSellerFromId( $$basket{booksellerid} );
311 foreach my $order (@orders) {
312 my $bd = GetBiblioData( $order->{'biblionumber'} );
314 clientnumber => $bookseller->{accountnumber},
315 basketname => $basket->{basketname},
316 ordernumber => $order->{ordernumber},
317 author => $bd->{author},
318 title => $bd->{title},
319 publishercode => $bd->{publishercode},
320 publicationyear => $bd->{publicationyear},
321 collectiontitle => $bd->{collectiontitle},
322 isbn => $order->{isbn},
323 quantity => $order->{quantity},
324 rrp => $order->{rrp},
325 discount => $bookseller->{discount},
326 ecost => $order->{ecost},
327 notes => $order->{notes},
328 entrydate => $order->{entrydate},
329 booksellername => $bookseller->{name},
330 bookselleraddress => $bookseller->{address1},
331 booksellerpostal => $bookseller->{postal},
332 contractnumber => $contract->{contractnumber},
333 contractname => $contract->{contractname},
336 basketname author title publishercode collectiontitle notes
337 booksellername bookselleraddress booksellerpostal contractname
338 basketgroupdeliveryplace basketgroupbillingplace
339 basketdeliveryplace basketbillingplace
341 # Double the quotes to not be interpreted as a field end
342 $row->{$_} =~ s/"/""/g if $row->{$_};
347 $template->param(rows => \@rows);
349 return $template->output;
353 =head3 CloseBasketgroup
355 &CloseBasketgroup($basketgroupno);
361 sub CloseBasketgroup {
362 my ($basketgroupno) = @_;
363 my $dbh = C4::Context->dbh;
364 my $sth = $dbh->prepare("
365 UPDATE aqbasketgroups
369 $sth->execute($basketgroupno);
372 #------------------------------------------------------------#
374 =head3 ReOpenBaskergroup($basketgroupno)
376 &ReOpenBaskergroup($basketgroupno);
382 sub ReOpenBasketgroup {
383 my ($basketgroupno) = @_;
384 my $dbh = C4::Context->dbh;
385 my $sth = $dbh->prepare("
386 UPDATE aqbasketgroups
390 $sth->execute($basketgroupno);
393 #------------------------------------------------------------#
398 &DelBasket($basketno);
400 Deletes the basket that has basketno field $basketno in the aqbasket table.
404 =item C<$basketno> is the primary key of the basket in the aqbasket table.
411 my ( $basketno ) = @_;
412 my $query = "DELETE FROM aqbasket WHERE basketno=?";
413 my $dbh = C4::Context->dbh;
414 my $sth = $dbh->prepare($query);
415 $sth->execute($basketno);
419 #------------------------------------------------------------#
423 &ModBasket($basketinfo);
425 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
429 =item C<$basketno> is the primary key of the basket in the aqbasket table.
436 my $basketinfo = shift;
437 my $query = "UPDATE aqbasket SET ";
439 foreach my $key (keys %$basketinfo){
440 if ($key ne 'basketno'){
441 $query .= "$key=?, ";
442 push(@params, $basketinfo->{$key} || undef );
445 # get rid of the "," at the end of $query
446 if (substr($query, length($query)-2) eq ', '){
451 $query .= "WHERE basketno=?";
452 push(@params, $basketinfo->{'basketno'});
453 my $dbh = C4::Context->dbh;
454 my $sth = $dbh->prepare($query);
455 $sth->execute(@params);
459 #------------------------------------------------------------#
461 =head3 ModBasketHeader
463 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
465 Modifies a basket's header.
469 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
471 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
473 =item C<$note> is the "note" field in the "aqbasket" table;
475 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
477 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
479 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
485 sub ModBasketHeader {
486 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid) = @_;
488 my $query = "UPDATE aqbasket SET basketname=?, note=?, booksellernote=?, booksellerid=? WHERE basketno=?";
489 my $dbh = C4::Context->dbh;
490 my $sth = $dbh->prepare($query);
491 $sth->execute($basketname,$note,$booksellernote,$booksellerid,$basketno);
493 if ( $contractnumber ) {
494 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
495 my $sth2 = $dbh->prepare($query2);
496 $sth2->execute($contractnumber,$basketno);
502 #------------------------------------------------------------#
504 =head3 GetBasketsByBookseller
506 @results = &GetBasketsByBookseller($booksellerid, $extra);
508 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
512 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
514 =item C<$extra> is the extra sql parameters, can be
516 $extra->{groupby}: group baskets by column
517 ex. $extra->{groupby} = aqbasket.basketgroupid
518 $extra->{orderby}: order baskets by column
519 $extra->{limit}: limit number of results (can be helpful for pagination)
525 sub GetBasketsByBookseller {
526 my ($booksellerid, $extra) = @_;
527 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
529 if ($extra->{groupby}) {
530 $query .= " GROUP by $extra->{groupby}";
532 if ($extra->{orderby}){
533 $query .= " ORDER by $extra->{orderby}";
535 if ($extra->{limit}){
536 $query .= " LIMIT $extra->{limit}";
539 my $dbh = C4::Context->dbh;
540 my $sth = $dbh->prepare($query);
541 $sth->execute($booksellerid);
542 my $results = $sth->fetchall_arrayref({});
547 =head3 GetBasketsInfosByBookseller
549 my $baskets = GetBasketsInfosByBookseller($supplierid);
551 Returns in a arrayref of hashref all about booksellers baskets, plus:
552 total_biblios: Number of distinct biblios in basket
553 total_items: Number of items in basket
554 expected_items: Number of non-received items in basket
558 sub GetBasketsInfosByBookseller {
559 my ($supplierid) = @_;
561 return unless $supplierid;
563 my $dbh = C4::Context->dbh;
566 SUM(aqorders.quantity) AS total_items,
567 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
568 SUM(IF(aqorders.datereceived IS NULL, aqorders.quantity, 0)) AS expected_items
570 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
571 WHERE booksellerid = ?
572 GROUP BY aqbasket.basketno
574 my $sth = $dbh->prepare($query);
575 $sth->execute($supplierid);
576 return $sth->fetchall_arrayref({});
580 #------------------------------------------------------------#
582 =head3 GetBasketsByBasketgroup
584 $baskets = &GetBasketsByBasketgroup($basketgroupid);
586 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
590 sub GetBasketsByBasketgroup {
591 my $basketgroupid = shift;
593 SELECT *, aqbasket.booksellerid as booksellerid
595 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
597 my $dbh = C4::Context->dbh;
598 my $sth = $dbh->prepare($query);
599 $sth->execute($basketgroupid);
600 my $results = $sth->fetchall_arrayref({});
605 #------------------------------------------------------------#
607 =head3 NewBasketgroup
609 $basketgroupid = NewBasketgroup(\%hashref);
611 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
613 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
615 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
617 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
619 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
621 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
623 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
628 my $basketgroupinfo = shift;
629 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
630 my $query = "INSERT INTO aqbasketgroups (";
632 foreach my $field ('name', 'deliveryplace', 'deliverycomment', 'closed') {
633 if ( $basketgroupinfo->{$field} ) {
634 $query .= "$field, ";
635 push(@params, $basketgroupinfo->{$field});
638 $query .= "booksellerid) VALUES (";
643 push(@params, $basketgroupinfo->{'booksellerid'});
644 my $dbh = C4::Context->dbh;
645 my $sth = $dbh->prepare($query);
646 $sth->execute(@params);
647 my $basketgroupid = $dbh->{'mysql_insertid'};
648 if( $basketgroupinfo->{'basketlist'} ) {
649 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
650 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
651 my $sth2 = $dbh->prepare($query2);
652 $sth2->execute($basketgroupid, $basketno);
655 return $basketgroupid;
658 #------------------------------------------------------------#
660 =head3 ModBasketgroup
662 ModBasketgroup(\%hashref);
664 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
666 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
668 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
670 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
672 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
674 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
676 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
678 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
683 my $basketgroupinfo = shift;
684 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
685 my $dbh = C4::Context->dbh;
686 my $query = "UPDATE aqbasketgroups SET ";
688 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
689 if ( defined $basketgroupinfo->{$field} ) {
690 $query .= "$field=?, ";
691 push(@params, $basketgroupinfo->{$field});
696 $query .= " WHERE id=?";
697 push(@params, $basketgroupinfo->{'id'});
698 my $sth = $dbh->prepare($query);
699 $sth->execute(@params);
701 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
702 $sth->execute($basketgroupinfo->{'id'});
704 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
705 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
706 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
707 $sth->execute($basketgroupinfo->{'id'}, $basketno);
714 #------------------------------------------------------------#
716 =head3 DelBasketgroup
718 DelBasketgroup($basketgroupid);
720 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
724 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
731 my $basketgroupid = shift;
732 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
733 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
734 my $dbh = C4::Context->dbh;
735 my $sth = $dbh->prepare($query);
736 $sth->execute($basketgroupid);
740 #------------------------------------------------------------#
743 =head2 FUNCTIONS ABOUT ORDERS
745 =head3 GetBasketgroup
747 $basketgroup = &GetBasketgroup($basketgroupid);
749 Returns a reference to the hash containing all infermation about the basketgroup.
754 my $basketgroupid = shift;
755 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
756 my $query = "SELECT * FROM aqbasketgroups WHERE id=?";
757 my $dbh = C4::Context->dbh;
758 my $sth = $dbh->prepare($query);
759 $sth->execute($basketgroupid);
760 my $result = $sth->fetchrow_hashref;
765 #------------------------------------------------------------#
767 =head3 GetBasketgroups
769 $basketgroups = &GetBasketgroups($booksellerid);
771 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
775 sub GetBasketgroups {
776 my $booksellerid = shift;
777 die "bookseller id is required to edit a basketgroup" unless $booksellerid;
778 my $query = "SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY `id` DESC";
779 my $dbh = C4::Context->dbh;
780 my $sth = $dbh->prepare($query);
781 $sth->execute($booksellerid);
782 my $results = $sth->fetchall_arrayref({});
787 #------------------------------------------------------------#
789 =head2 FUNCTIONS ABOUT ORDERS
793 #------------------------------------------------------------#
795 =head3 GetPendingOrders
797 $orders = &GetPendingOrders($booksellerid, $grouped, $owner);
799 Finds pending orders from the bookseller with the given ID. Ignores
800 completed and cancelled orders.
802 C<$booksellerid> contains the bookseller identifier
803 C<$grouped> contains 0 or 1. 0 means returns the list, 1 means return the total
804 C<$owner> contains 0 or 1. 0 means any owner. 1 means only the list of orders entered by the user itself.
806 C<$orders> is a reference-to-array; each element is a
807 reference-to-hash with the following fields:
808 C<$grouped> is a boolean that, if set to 1 will group all order lines of the same basket
809 in a single result line
813 =item C<authorizedby>
821 These give the value of the corresponding field in the aqorders table
822 of the Koha database.
824 Results are ordered from most to least recent.
828 sub GetPendingOrders {
829 my ($supplierid,$grouped,$owner,$basketno) = @_;
830 my $dbh = C4::Context->dbh;
832 SELECT ".($grouped?"count(*),":"")."aqbasket.basketno,
833 surname,firstname,biblio.*,biblioitems.isbn,
834 aqbasket.closedate, aqbasket.creationdate, aqbasket.basketname,
837 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
838 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
839 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
840 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
842 AND (quantity > quantityreceived OR quantityreceived is NULL)
843 AND datecancellationprinted IS NULL";
844 my @query_params = ( $supplierid );
845 my $userenv = C4::Context->userenv;
846 if ( C4::Context->preference("IndependantBranches") ) {
847 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
848 $strsth .= " and (borrowers.branchcode = ?
849 or borrowers.branchcode = '')";
850 push @query_params, $userenv->{branch};
854 $strsth .= " AND aqbasket.authorisedby=? ";
855 push @query_params, $userenv->{'number'};
858 $strsth .= " AND aqbasket.basketno=? ";
859 push @query_params, $basketno;
861 $strsth .= " group by aqbasket.basketno" if $grouped;
862 $strsth .= " order by aqbasket.basketno";
864 my $sth = $dbh->prepare($strsth);
865 $sth->execute( @query_params );
866 my $results = $sth->fetchall_arrayref({});
871 #------------------------------------------------------------#
875 @orders = &GetOrders($basketnumber, $orderby);
877 Looks up the pending (non-cancelled) orders with the given basket
878 number. If C<$booksellerID> is non-empty, only orders from that seller
882 C<&basket> returns a two-element array. C<@orders> is an array of
883 references-to-hash, whose keys are the fields from the aqorders,
884 biblio, and biblioitems tables in the Koha database.
889 my ( $basketno, $orderby ) = @_;
890 my $dbh = C4::Context->dbh;
892 SELECT biblio.*,biblioitems.*,
897 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
898 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
899 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
901 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
904 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
905 $query .= " ORDER BY $orderby";
906 my $sth = $dbh->prepare($query);
907 $sth->execute($basketno);
908 my $results = $sth->fetchall_arrayref({});
913 #------------------------------------------------------------#
915 =head3 GetOrderNumber
917 $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
919 Looks up the ordernumber with the given biblionumber and biblioitemnumber.
921 Returns the number of this order.
925 =item C<$ordernumber> is the order number.
932 my ( $biblionumber,$biblioitemnumber ) = @_;
933 my $dbh = C4::Context->dbh;
938 AND biblioitemnumber=?
940 my $sth = $dbh->prepare($query);
941 $sth->execute( $biblionumber, $biblioitemnumber );
943 return $sth->fetchrow;
946 #------------------------------------------------------------#
950 $order = &GetOrder($ordernumber);
952 Looks up an order by order number.
954 Returns a reference-to-hash describing the order. The keys of
955 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
960 my ($ordernumber) = @_;
961 my $dbh = C4::Context->dbh;
963 SELECT biblioitems.*, biblio.*, aqorders.*
965 LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber
966 LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber
967 WHERE aqorders.ordernumber=?
970 my $sth= $dbh->prepare($query);
971 $sth->execute($ordernumber);
972 my $data = $sth->fetchrow_hashref;
977 #------------------------------------------------------------#
981 &NewOrder(\%hashref);
983 Adds a new order to the database. Any argument that isn't described
984 below is the new value of the field with the same name in the aqorders
985 table of the Koha database.
989 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
991 =item $hashref->{'ordernumber'} is a "minimum order number."
993 =item $hashref->{'budgetdate'} is effectively ignored.
994 If it's undef (anything false) or the string 'now', the current day is used.
995 Else, the upcoming July 1st is used.
997 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
999 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
1001 =item defaults entrydate to Now
1003 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".
1010 my $orderinfo = shift;
1011 #### ------------------------------
1012 my $dbh = C4::Context->dbh;
1016 # if these parameters are missing, we can't continue
1017 for my $key (qw/basketno quantity biblionumber budget_id/) {
1018 croak "Mandatory parameter $key missing" unless $orderinfo->{$key};
1021 if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) {
1022 $orderinfo->{'subscription'} = 1;
1024 $orderinfo->{'subscription'} = 0;
1026 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
1027 if (!$orderinfo->{quantityreceived}) {
1028 $orderinfo->{quantityreceived} = 0;
1031 my $ordernumber=InsertInTable("aqorders",$orderinfo);
1032 return ( $orderinfo->{'basketno'}, $ordernumber );
1037 #------------------------------------------------------------#
1046 my ($itemnumber, $ordernumber) = @_;
1047 my $dbh = C4::Context->dbh;
1049 INSERT INTO aqorders_items
1050 (itemnumber, ordernumber)
1053 my $sth = $dbh->prepare($query);
1054 $sth->execute( $itemnumber, $ordernumber);
1057 #------------------------------------------------------------#
1061 &ModOrder(\%hashref);
1063 Modifies an existing order. Updates the order with order number
1064 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1065 other keys of the hash update the fields with the same name in the aqorders
1066 table of the Koha database.
1071 my $orderinfo = shift;
1073 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1074 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1076 my $dbh = C4::Context->dbh;
1079 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1080 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1082 # delete($orderinfo->{'branchcode'});
1083 # the hash contains a lot of entries not in aqorders, so get the columns ...
1084 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1086 my $colnames = $sth->{NAME};
1087 my $query = "UPDATE aqorders SET ";
1089 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1090 # ... and skip hash entries that are not in the aqorders table
1091 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1092 next unless grep(/^$orderinfokey$/, @$colnames);
1093 $query .= "$orderinfokey=?, ";
1094 push(@params, $orderinfo->{$orderinfokey});
1097 $query .= "timestamp=NOW() WHERE ordernumber=?";
1098 # push(@params, $specorderinfo{'ordernumber'});
1099 push(@params, $orderinfo->{'ordernumber'} );
1100 $sth = $dbh->prepare($query);
1101 $sth->execute(@params);
1105 #------------------------------------------------------------#
1109 &ModOrderItem(\%hashref);
1111 Modifies the itemnumber in the aqorders_items table. The input hash needs three entities:
1115 =item - itemnumber: the old itemnumber
1116 =item - ordernumber: the order this item is attached to
1117 =item - newitemnumber: the new itemnumber we want to attach the line to
1124 my $orderiteminfo = shift;
1125 if (! $orderiteminfo->{'ordernumber'} || ! $orderiteminfo->{'itemnumber'} || ! $orderiteminfo->{'newitemnumber'}){
1126 die "Ordernumber, itemnumber and newitemnumber is required";
1129 my $dbh = C4::Context->dbh;
1131 my $query = "UPDATE aqorders_items set itemnumber=? where itemnumber=? and ordernumber=?";
1132 my @params = ($orderiteminfo->{'newitemnumber'}, $orderiteminfo->{'itemnumber'}, $orderiteminfo->{'ordernumber'});
1133 my $sth = $dbh->prepare($query);
1134 $sth->execute(@params);
1140 ModItemOrder($itemnumber, $ordernumber);
1142 Modifies the ordernumber of an item in aqorders_items.
1147 my ($itemnumber, $ordernumber) = @_;
1149 return unless ($itemnumber and $ordernumber);
1151 my $dbh = C4::Context->dbh;
1153 UPDATE aqorders_items
1155 WHERE itemnumber = ?
1157 my $sth = $dbh->prepare($query);
1158 return $sth->execute($ordernumber, $itemnumber);
1161 #------------------------------------------------------------#
1164 =head3 ModOrderBibliotemNumber
1166 &ModOrderBiblioitemNumber($biblioitemnumber,$ordernumber, $biblionumber);
1168 Modifies the biblioitemnumber for an existing order.
1169 Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
1173 #FIXME: is this used at all?
1174 sub ModOrderBiblioitemNumber {
1175 my ($biblioitemnumber,$ordernumber, $biblionumber) = @_;
1176 my $dbh = C4::Context->dbh;
1179 SET biblioitemnumber = ?
1180 WHERE ordernumber = ?
1181 AND biblionumber = ?";
1182 my $sth = $dbh->prepare($query);
1183 $sth->execute( $biblioitemnumber, $ordernumber, $biblionumber );
1186 =head3 GetCancelledOrders
1188 my @orders = GetCancelledOrders($basketno, $orderby);
1190 Returns cancelled orders for a basket
1194 sub GetCancelledOrders {
1195 my ( $basketno, $orderby ) = @_;
1197 return () unless $basketno;
1199 my $dbh = C4::Context->dbh;
1201 SELECT biblio.*, biblioitems.*, aqorders.*, aqbudgets.*
1203 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1204 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1205 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1207 AND (datecancellationprinted IS NOT NULL
1208 AND datecancellationprinted <> '0000-00-00')
1211 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1213 $query .= " ORDER BY $orderby";
1214 my $sth = $dbh->prepare($query);
1215 $sth->execute($basketno);
1216 my $results = $sth->fetchall_arrayref( {} );
1222 #------------------------------------------------------------#
1224 =head3 ModReceiveOrder
1226 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
1227 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
1228 $freight, $bookfund, $rrp);
1230 Updates an order, to reflect the fact that it was received, at least
1231 in part. All arguments not mentioned below update the fields with the
1232 same name in the aqorders table of the Koha database.
1234 If a partial order is received, splits the order into two. The received
1235 portion must have a booksellerinvoicenumber.
1237 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1243 sub ModReceiveOrder {
1245 $biblionumber, $ordernumber, $quantrec, $user, $cost,
1246 $invoiceno, $freight, $rrp, $budget_id, $datereceived, $received_items
1249 my $dbh = C4::Context->dbh;
1250 $datereceived = C4::Dates->output('iso') unless $datereceived;
1251 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1252 if ($suggestionid) {
1253 ModSuggestion( {suggestionid=>$suggestionid,
1254 STATUS=>'AVAILABLE',
1255 biblionumber=> $biblionumber}
1259 my $sth=$dbh->prepare("
1260 SELECT * FROM aqorders
1261 WHERE biblionumber=? AND aqorders.ordernumber=?");
1263 $sth->execute($biblionumber,$ordernumber);
1264 my $order = $sth->fetchrow_hashref();
1267 if ( $order->{quantity} > $quantrec ) {
1268 $sth=$dbh->prepare("
1270 SET quantityreceived=?
1272 , booksellerinvoicenumber=?
1277 WHERE biblionumber=? AND ordernumber=?");
1279 $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$quantrec,$biblionumber,$ordernumber);
1282 # create a new order for the remaining items, and set its bookfund.
1283 foreach my $orderkey ( "linenumber", "allocation" ) {
1284 delete($order->{'$orderkey'});
1286 $order->{'quantity'} -= $quantrec;
1287 $order->{'quantityreceived'} = 0;
1288 my $newOrder = NewOrder($order);
1289 # Change ordernumber in aqorders_items for items not received
1290 my @orderitems = GetItemnumbersFromOrder( $order->{'ordernumber'} );
1291 my $count = scalar @orderitems;
1293 for (my $i=0; $i<$count; $i++){
1294 foreach (@$received_items){
1295 splice (@orderitems, $i, 1) if ($orderitems[$i] == $_);
1298 foreach (@orderitems) {
1299 ModItemOrder($_, $newOrder);
1302 $sth=$dbh->prepare("update aqorders
1303 set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
1304 unitprice=?,freight=?,rrp=?
1305 where biblionumber=? and ordernumber=?");
1306 $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$biblionumber,$ordernumber);
1309 return $datereceived;
1311 #------------------------------------------------------------#
1315 @results = &SearchOrder($search, $biblionumber, $complete);
1317 Searches for orders.
1319 C<$search> may take one of several forms: if it is an ISBN,
1320 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
1321 order number, C<&ordersearch> returns orders with that order number
1322 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
1323 to be a space-separated list of search terms; in this case, all of the
1324 terms must appear in the title (matching the beginning of title
1327 If C<$complete> is C<yes>, the results will include only completed
1328 orders. In any case, C<&ordersearch> ignores cancelled orders.
1330 C<&ordersearch> returns an array.
1331 C<@results> is an array of references-to-hash with the following keys:
1337 =item C<seriestitle>
1348 #### -------- SearchOrder-------------------------------
1349 my ( $ordernumber, $search, $ean, $supplierid, $basket ) = @_;
1351 my $dbh = C4::Context->dbh;
1356 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1357 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1358 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1359 WHERE (datecancellationprinted is NULL)";
1362 $query .= " AND (aqorders.ordernumber=?)";
1363 push @args, $ordernumber;
1366 $query .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)";
1367 push @args, ("%$search%","%$search%","%$search%");
1370 $query .= " AND biblioitems.ean = ?";
1374 $query .= "AND aqbasket.booksellerid = ?";
1375 push @args, $supplierid;
1378 $query .= "AND aqorders.basketno = ?";
1379 push @args, $basket;
1382 my $sth = $dbh->prepare($query);
1383 $sth->execute(@args);
1384 my $results = $sth->fetchall_arrayref({});
1389 #------------------------------------------------------------#
1393 &DelOrder($biblionumber, $ordernumber);
1395 Cancel the order with the given order and biblio numbers. It does not
1396 delete any entries in the aqorders table, it merely marks them as
1402 my ( $bibnum, $ordernumber ) = @_;
1403 my $dbh = C4::Context->dbh;
1406 SET datecancellationprinted=now()
1407 WHERE biblionumber=? AND ordernumber=?
1409 my $sth = $dbh->prepare($query);
1410 $sth->execute( $bibnum, $ordernumber );
1412 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1413 foreach my $itemnumber (@itemnumbers){
1414 C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
1419 =head2 FUNCTIONS ABOUT PARCELS
1423 #------------------------------------------------------------#
1427 @results = &GetParcel($booksellerid, $code, $date);
1429 Looks up all of the received items from the supplier with the given
1430 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1432 C<@results> is an array of references-to-hash. The keys of each element are fields from
1433 the aqorders, biblio, and biblioitems tables of the Koha database.
1435 C<@results> is sorted alphabetically by book title.
1440 #gets all orders from a certain supplier, orders them alphabetically
1441 my ( $supplierid, $code, $datereceived ) = @_;
1442 my $dbh = C4::Context->dbh;
1445 if $code; # add % if we search on a given code (otherwise, let him empty)
1447 SELECT authorisedby,
1452 aqorders.biblionumber,
1453 aqorders.ordernumber,
1455 aqorders.quantityreceived,
1462 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1463 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1464 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1466 aqbasket.booksellerid = ?
1467 AND aqorders.booksellerinvoicenumber LIKE ?
1468 AND aqorders.datereceived = ? ";
1470 my @query_params = ( $supplierid, $code, $datereceived );
1471 if ( C4::Context->preference("IndependantBranches") ) {
1472 my $userenv = C4::Context->userenv;
1473 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1474 $strsth .= " and (borrowers.branchcode = ?
1475 or borrowers.branchcode = '')";
1476 push @query_params, $userenv->{branch};
1479 $strsth .= " ORDER BY aqbasket.basketno";
1480 # ## parcelinformation : $strsth
1481 my $sth = $dbh->prepare($strsth);
1482 $sth->execute( @query_params );
1483 while ( my $data = $sth->fetchrow_hashref ) {
1484 push( @results, $data );
1486 # ## countparcelbiblio: scalar(@results)
1492 #------------------------------------------------------------#
1496 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1498 get a lists of parcels.
1505 is the bookseller this function has to get parcels.
1508 To know on what criteria the results list has to be ordered.
1511 is the booksellerinvoicenumber.
1513 =item $datefrom & $dateto
1514 to know on what date this function has to filter its search.
1519 a pointer on a hash list containing parcel informations as such :
1525 =item Last operation
1527 =item Number of biblio
1529 =item Number of items
1536 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1537 my $dbh = C4::Context->dbh;
1538 my @query_params = ();
1540 SELECT aqorders.booksellerinvoicenumber,
1541 datereceived,purchaseordernumber,
1542 count(DISTINCT biblionumber) AS biblio,
1543 sum(quantity) AS itemsexpected,
1544 sum(quantityreceived) AS itemsreceived
1545 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1546 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1548 push @query_params, $bookseller;
1550 if ( defined $code ) {
1551 $strsth .= ' and aqorders.booksellerinvoicenumber like ? ';
1552 # add a % to the end of the code to allow stemming.
1553 push @query_params, "$code%";
1556 if ( defined $datefrom ) {
1557 $strsth .= ' and datereceived >= ? ';
1558 push @query_params, $datefrom;
1561 if ( defined $dateto ) {
1562 $strsth .= 'and datereceived <= ? ';
1563 push @query_params, $dateto;
1566 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
1568 # can't use a placeholder to place this column name.
1569 # but, we could probably be checking to make sure it is a column that will be fetched.
1570 $strsth .= "order by $order " if ($order);
1572 my $sth = $dbh->prepare($strsth);
1574 $sth->execute( @query_params );
1575 my $results = $sth->fetchall_arrayref({});
1580 #------------------------------------------------------------#
1582 =head3 GetLateOrders
1584 @results = &GetLateOrders;
1586 Searches for bookseller with late orders.
1589 the table of supplier with late issues. This table is full of hashref.
1595 my $supplierid = shift;
1597 my $estimateddeliverydatefrom = shift;
1598 my $estimateddeliverydateto = shift;
1600 my $dbh = C4::Context->dbh;
1602 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
1603 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
1605 my @query_params = ();
1607 SELECT aqbasket.basketno,
1608 aqorders.ordernumber,
1609 DATE(aqbasket.closedate) AS orderdate,
1610 aqorders.rrp AS unitpricesupplier,
1611 aqorders.ecost AS unitpricelib,
1612 aqorders.claims_count AS claims_count,
1613 aqorders.claimed_date AS claimed_date,
1614 aqbudgets.budget_name AS budget,
1615 borrowers.branchcode AS branch,
1616 aqbooksellers.name AS supplier,
1617 aqbooksellers.id AS supplierid,
1618 biblio.author, biblio.title,
1619 biblioitems.publishercode AS publisher,
1620 biblioitems.publicationyear,
1621 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1625 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1626 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1627 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1628 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1629 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1630 WHERE aqorders.basketno = aqbasket.basketno
1631 AND ( datereceived = ''
1632 OR datereceived IS NULL
1633 OR aqorders.quantityreceived < aqorders.quantity
1635 AND aqbasket.closedate IS NOT NULL
1636 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
1639 if ($dbdriver eq "mysql") {
1641 aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
1642 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1643 DATEDIFF(CAST(now() AS date),closedate) AS latesince
1645 if ( defined $delay ) {
1646 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
1647 push @query_params, $delay;
1650 HAVING quantity <> 0
1651 AND unitpricesupplier <> 0
1652 AND unitpricelib <> 0
1655 # FIXME: account for IFNULL as above
1657 aqorders.quantity AS quantity,
1658 aqorders.quantity * aqorders.rrp AS subtotal,
1659 (CAST(now() AS date) - closedate) AS latesince
1661 if ( defined $delay ) {
1662 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
1663 push @query_params, $delay;
1666 if (defined $supplierid) {
1667 $from .= ' AND aqbasket.booksellerid = ? ';
1668 push @query_params, $supplierid;
1670 if (defined $branch) {
1671 $from .= ' AND borrowers.branchcode LIKE ? ';
1672 push @query_params, $branch;
1674 if ( defined $estimateddeliverydatefrom ) {
1676 AND aqbooksellers.deliverytime IS NOT NULL
1677 AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
1678 push @query_params, $estimateddeliverydatefrom;
1680 if ( defined $estimateddeliverydatefrom and defined $estimateddeliverydateto ) {
1681 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
1682 push @query_params, $estimateddeliverydateto;
1683 } elsif ( defined $estimateddeliverydatefrom ) {
1684 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
1686 if (C4::Context->preference("IndependantBranches")
1687 && C4::Context->userenv
1688 && C4::Context->userenv->{flags} != 1 ) {
1689 $from .= ' AND borrowers.branchcode LIKE ? ';
1690 push @query_params, C4::Context->userenv->{branch};
1692 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
1693 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
1694 my $sth = $dbh->prepare($query);
1695 $sth->execute(@query_params);
1697 while (my $data = $sth->fetchrow_hashref) {
1698 $data->{orderdate} = format_date($data->{orderdate});
1699 $data->{claimed_date} = format_date($data->{claimed_date});
1700 push @results, $data;
1705 #------------------------------------------------------------#
1709 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
1711 Retreives some acquisition history information
1719 basket - search both basket name and number
1720 booksellerinvoicenumber
1723 $order_loop is a list of hashrefs that each look like this:
1725 'author' => 'Twain, Mark',
1727 'biblionumber' => '215',
1729 'creationdate' => 'MM/DD/YYYY',
1730 'datereceived' => undef,
1733 'invoicenumber' => undef,
1735 'ordernumber' => '1',
1737 'quantityreceived' => undef,
1738 'title' => 'The Adventures of Huckleberry Finn'
1740 $total_qty is the sum of all of the quantities in $order_loop
1741 $total_price is the cost of each in $order_loop times the quantity
1742 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
1747 # don't run the query if there are no parameters (list would be too long for sure !)
1748 croak "No search params" unless @_;
1750 my $title = $params{title};
1751 my $author = $params{author};
1752 my $isbn = $params{isbn};
1753 my $ean = $params{ean};
1754 my $name = $params{name};
1755 my $from_placed_on = $params{from_placed_on};
1756 my $to_placed_on = $params{to_placed_on};
1757 my $basket = $params{basket};
1758 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
1761 my $total_qtyreceived = 0;
1762 my $total_price = 0;
1764 my $dbh = C4::Context->dbh;
1772 aqbasket.basketname,
1773 aqbasket.basketgroupid,
1774 aqbasketgroups.name as groupname,
1776 aqbasket.creationdate,
1777 aqorders.datereceived,
1779 aqorders.quantityreceived,
1781 aqorders.ordernumber,
1782 aqorders.booksellerinvoicenumber as invoicenumber,
1783 aqbooksellers.id as id,
1784 aqorders.biblionumber
1786 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
1787 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
1788 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
1789 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
1790 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber";
1792 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
1793 if ( C4::Context->preference("IndependantBranches") );
1795 $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
1797 my @query_params = ();
1800 $query .= " AND biblio.title LIKE ? ";
1801 $title =~ s/\s+/%/g;
1802 push @query_params, "%$title%";
1806 $query .= " AND biblio.author LIKE ? ";
1807 push @query_params, "%$author%";
1811 $query .= " AND biblioitems.isbn LIKE ? ";
1812 push @query_params, "%$isbn%";
1814 if ( defined $ean and $ean ) {
1815 $query .= " AND biblioitems.ean = ? ";
1816 push @query_params, "$ean";
1819 $query .= " AND aqbooksellers.name LIKE ? ";
1820 push @query_params, "%$name%";
1823 if ( $from_placed_on ) {
1824 $query .= " AND creationdate >= ? ";
1825 push @query_params, $from_placed_on;
1828 if ( $to_placed_on ) {
1829 $query .= " AND creationdate <= ? ";
1830 push @query_params, $to_placed_on;
1834 if ($basket =~ m/^\d+$/) {
1835 $query .= " AND aqorders.basketno = ? ";
1836 push @query_params, $basket;
1838 $query .= " AND aqbasket.basketname LIKE ? ";
1839 push @query_params, "%$basket%";
1843 if ($booksellerinvoicenumber) {
1844 $query .= " AND (aqorders.booksellerinvoicenumber LIKE ? OR aqbasket.booksellerinvoicenumber LIKE ?)";
1845 push @query_params, "%$booksellerinvoicenumber%", "%$booksellerinvoicenumber%";
1848 if ( C4::Context->preference("IndependantBranches") ) {
1849 my $userenv = C4::Context->userenv;
1850 if ( $userenv && ($userenv->{flags} || 0) != 1 ) {
1851 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
1852 push @query_params, $userenv->{branch};
1855 $query .= " ORDER BY id";
1856 my $sth = $dbh->prepare($query);
1857 $sth->execute( @query_params );
1859 while ( my $line = $sth->fetchrow_hashref ) {
1860 $line->{count} = $cnt++;
1861 $line->{toggle} = 1 if $cnt % 2;
1862 push @order_loop, $line;
1863 $total_qty += $line->{'quantity'};
1864 $total_qtyreceived += $line->{'quantityreceived'};
1865 $total_price += $line->{'quantity'} * $line->{'ecost'};
1867 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
1870 =head2 GetRecentAcqui
1872 $results = GetRecentAcqui($days);
1874 C<$results> is a ref to a table which containts hashref
1878 sub GetRecentAcqui {
1880 my $dbh = C4::Context->dbh;
1884 ORDER BY timestamp DESC
1887 my $sth = $dbh->prepare($query);
1889 my $results = $sth->fetchall_arrayref({});
1895 $contractlist = &GetContracts($booksellerid, $activeonly);
1897 Looks up the contracts that belong to a bookseller
1899 Returns a list of contracts
1903 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
1905 =item C<$activeonly> if exists get only contracts that are still active.
1912 my ( $booksellerid, $activeonly ) = @_;
1913 my $dbh = C4::Context->dbh;
1915 if (! $activeonly) {
1919 WHERE booksellerid=?
1924 WHERE booksellerid=?
1925 AND contractenddate >= CURDATE( )";
1927 my $sth = $dbh->prepare($query);
1928 $sth->execute( $booksellerid );
1930 while (my $data = $sth->fetchrow_hashref ) {
1931 push(@results, $data);
1937 #------------------------------------------------------------#
1941 $contract = &GetContract($contractID);
1943 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
1950 my ( $contractno ) = @_;
1951 my $dbh = C4::Context->dbh;
1955 WHERE contractnumber=?
1958 my $sth = $dbh->prepare($query);
1959 $sth->execute( $contractno );
1960 my $result = $sth->fetchrow_hashref;
1968 &AddClaim($ordernumber);
1970 Add a claim for an order
1976 my ($ordernumber) = @_;
1977 my $dbh = C4::Context->dbh;
1980 claims_count = claims_count + 1,
1981 claimed_date = CURDATE()
1982 WHERE ordernumber = ?
1984 my $sth = $dbh->prepare($query);
1985 $sth->execute($ordernumber);
1994 Koha Development Team <http://koha-community.org/>