1 package C4::Acquisition;
3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along with
17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
18 # Suite 330, Boston, MA 02111-1307 USA
24 use C4::Dates qw(format_date format_date_in_iso);
28 use C4::SQLHelper qw(InsertInTable);
33 use vars qw($VERSION @ISA @EXPORT);
36 # set the version for version checking
41 &GetBasket &NewBasket &CloseBasket &DelBasket &ModBasket
42 &GetBasketsByBookseller &GetBasketsByBasketgroup
46 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
47 &GetBasketgroups &ReOpenBasketgroup
49 &NewOrder &DelOrder &ModOrder &GetPendingOrders &GetOrder &GetOrders
50 &GetOrderNumber &GetLateOrders &GetOrderFromItemnumber
51 &SearchOrder &GetHistory &GetRecentAcqui
52 &ModReceiveOrder &ModOrderBiblioitemNumber
54 &NewOrderItem &ModOrderItem
56 &GetParcels &GetParcel
57 &GetContracts &GetContract
59 &GetItemnumbersFromOrder
67 sub GetOrderFromItemnumber {
68 my ($itemnumber) = @_;
69 my $dbh = C4::Context->dbh;
72 SELECT * from aqorders LEFT JOIN aqorders_items
73 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
74 WHERE itemnumber = ? |;
76 my $sth = $dbh->prepare($query);
80 $sth->execute($itemnumber);
82 my $order = $sth->fetchrow_hashref;
87 # Returns the itemnumber(s) associated with the ordernumber given in parameter
88 sub GetItemnumbersFromOrder {
89 my ($ordernumber) = @_;
90 my $dbh = C4::Context->dbh;
91 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
92 my $sth = $dbh->prepare($query);
93 $sth->execute($ordernumber);
96 while (my $order = $sth->fetchrow_hashref) {
97 push @tab, $order->{'itemnumber'};
111 C4::Acquisition - Koha functions for dealing with orders and acquisitions
119 The functions in this module deal with acquisitions, managing book
120 orders, basket and parcels.
124 =head2 FUNCTIONS ABOUT BASKETS
130 $aqbasket = &GetBasket($basketnumber);
132 get all basket informations in aqbasket for a given basket
135 informations for a given basket returned as a hashref.
143 my $dbh = C4::Context->dbh;
146 concat( b.firstname,' ',b.surname) AS authorisedbyname,
147 b.branchcode AS branch
149 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
152 my $sth=$dbh->prepare($query);
153 $sth->execute($basketno);
154 my $basket = $sth->fetchrow_hashref;
158 #------------------------------------------------------------#
164 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname, $basketnote, $basketbooksellernote, $basketcontractnumber );
166 Create a new basket in aqbasket table
168 =item C<$booksellerid> is a foreign key in the aqbasket table
170 =item C<$authorizedby> is the username of who created the basket
172 The other parameters are optional, see ModBasketHeader for more info on them.
178 # FIXME : this function seems to be unused.
181 my ( $booksellerid, $authorisedby, $basketname, $basketnote, $basketbooksellernote, $basketcontractnumber ) = @_;
182 my $dbh = C4::Context->dbh;
185 (creationdate,booksellerid,authorisedby)
186 VALUES (now(),'$booksellerid','$authorisedby')
190 #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
191 my $basket = $dbh->{'mysql_insertid'};
192 ModBasketHeader($basket, $basketname || '', $basketnote || '', $basketbooksellernote || '', $basketcontractnumber || undef);
196 #------------------------------------------------------------#
202 &CloseBasket($basketno);
204 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 CloseBasketgroup
228 &CloseBasketgroup($basketgroupno);
236 sub CloseBasketgroup {
237 my ($basketgroupno) = @_;
238 my $dbh = C4::Context->dbh;
239 my $sth = $dbh->prepare("
240 UPDATE aqbasketgroups
244 $sth->execute($basketgroupno);
247 #------------------------------------------------------------#
249 =head3 ReOpenBaskergroup($basketgroupno)
253 &ReOpenBaskergroup($basketgroupno);
261 sub ReOpenBasketgroup {
262 my ($basketgroupno) = @_;
263 my $dbh = C4::Context->dbh;
264 my $sth = $dbh->prepare("
265 UPDATE aqbasketgroups
269 $sth->execute($basketgroupno);
272 #------------------------------------------------------------#
279 &DelBasket($basketno);
281 Deletes the basket that has basketno field $basketno in the aqbasket table.
285 =item C<$basketno> is the primary key of the basket in the aqbasket table.
293 my ( $basketno ) = @_;
294 my $query = "DELETE FROM aqbasket WHERE basketno=?";
295 my $dbh = C4::Context->dbh;
296 my $sth = $dbh->prepare($query);
297 $sth->execute($basketno);
301 #------------------------------------------------------------#
307 &ModBasket($basketinfo);
309 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
313 =item C<$basketno> is the primary key of the basket in the aqbasket table.
321 my $basketinfo = shift;
322 my $query = "UPDATE aqbasket SET ";
324 foreach my $key (keys %$basketinfo){
325 if ($key ne 'basketno'){
326 $query .= "$key=?, ";
327 push(@params, $basketinfo->{$key} || undef );
330 # get rid of the "," at the end of $query
331 if (substr($query, length($query)-2) eq ', '){
336 $query .= "WHERE basketno=?";
337 push(@params, $basketinfo->{'basketno'});
338 my $dbh = C4::Context->dbh;
339 my $sth = $dbh->prepare($query);
340 $sth->execute(@params);
344 #------------------------------------------------------------#
346 =head3 ModBasketHeader
350 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber);
352 Modifies a basket's header.
356 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
358 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
360 =item C<$note> is the "note" field in the "aqbasket" table;
362 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
364 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
371 sub ModBasketHeader {
372 my ($basketno, $basketname, $note, $booksellernote, $contractnumber) = @_;
373 my $query = "UPDATE aqbasket SET basketname=?, note=?, booksellernote=? WHERE basketno=?";
374 my $dbh = C4::Context->dbh;
375 my $sth = $dbh->prepare($query);
376 $sth->execute($basketname,$note,$booksellernote,$basketno);
377 if ( $contractnumber ) {
378 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
379 my $sth2 = $dbh->prepare($query2);
380 $sth2->execute($contractnumber,$basketno);
386 #------------------------------------------------------------#
388 =head3 GetBasketsByBookseller
392 @results = &GetBasketsByBookseller($booksellerid, $extra);
394 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
398 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
400 =item C<$extra> is the extra sql parameters, can be
402 - $extra->{groupby}: group baskets by column
403 ex. $extra->{groupby} = aqbasket.basketgroupid
404 - $extra->{orderby}: order baskets by column
405 - $extra->{limit}: limit number of results (can be helpful for pagination)
413 sub GetBasketsByBookseller {
414 my ($booksellerid, $extra) = @_;
415 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
417 if ($extra->{groupby}) {
418 $query .= " GROUP by $extra->{groupby}";
420 if ($extra->{orderby}){
421 $query .= " ORDER by $extra->{orderby}";
423 if ($extra->{limit}){
424 $query .= " LIMIT $extra->{limit}";
427 my $dbh = C4::Context->dbh;
428 my $sth = $dbh->prepare($query);
429 $sth->execute($booksellerid);
430 my $results = $sth->fetchall_arrayref({});
435 #------------------------------------------------------------#
437 =head3 GetBasketsByBasketgroup
441 $baskets = &GetBasketsByBasketgroup($basketgroupid);
445 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
453 sub GetBasketsByBasketgroup {
454 my $basketgroupid = shift;
455 my $query = "SELECT * FROM aqbasket
456 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?";
457 my $dbh = C4::Context->dbh;
458 my $sth = $dbh->prepare($query);
459 $sth->execute($basketgroupid);
460 my $results = $sth->fetchall_arrayref({});
465 #------------------------------------------------------------#
467 =head3 NewBasketgroup
471 $basketgroupid = NewBasketgroup(\%hashref);
475 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
477 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
479 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
481 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
483 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
492 my $basketgroupinfo = shift;
493 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
494 my $query = "INSERT INTO aqbasketgroups (";
496 foreach my $field ('name', 'closed') {
497 if ( $basketgroupinfo->{$field} ) {
498 $query .= "$field, ";
499 push(@params, $basketgroupinfo->{$field});
502 $query .= "booksellerid) VALUES (";
507 push(@params, $basketgroupinfo->{'booksellerid'});
508 my $dbh = C4::Context->dbh;
509 my $sth = $dbh->prepare($query);
510 $sth->execute(@params);
511 my $basketgroupid = $dbh->{'mysql_insertid'};
512 if( $basketgroupinfo->{'basketlist'} ) {
513 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
514 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
515 my $sth2 = $dbh->prepare($query2);
516 $sth2->execute($basketgroupid, $basketno);
519 return $basketgroupid;
522 #------------------------------------------------------------#
524 =head3 ModBasketgroup
528 ModBasketgroup(\%hashref);
532 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
534 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
536 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
538 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
540 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
549 my $basketgroupinfo = shift;
550 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
551 my $dbh = C4::Context->dbh;
552 my $query = "UPDATE aqbasketgroups SET ";
554 foreach my $field (qw(name closed)) {
555 if ( $basketgroupinfo->{$field} ne undef) {
556 $query .= "$field=?, ";
557 push(@params, $basketgroupinfo->{$field});
562 $query .= " WHERE id=?";
563 push(@params, $basketgroupinfo->{'id'});
564 my $sth = $dbh->prepare($query);
565 $sth->execute(@params);
567 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
568 $sth->execute($basketgroupinfo->{'id'});
570 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
571 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
572 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
573 $sth->execute($basketgroupinfo->{'id'}, $basketno);
580 #------------------------------------------------------------#
582 =head3 DelBasketgroup
586 DelBasketgroup($basketgroupid);
590 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
592 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
601 my $basketgroupid = shift;
602 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
603 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
604 my $dbh = C4::Context->dbh;
605 my $sth = $dbh->prepare($query);
606 $sth->execute($basketgroupid);
610 #------------------------------------------------------------#
614 =head2 FUNCTIONS ABOUT ORDERS
620 =head3 GetBasketgroup
624 $basketgroup = &GetBasketgroup($basketgroupid);
628 Returns a reference to the hash containing all infermation about the basketgroup.
637 my $basketgroupid = shift;
638 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
639 my $query = "SELECT * FROM aqbasketgroups WHERE id=?";
640 my $dbh = C4::Context->dbh;
641 my $sth = $dbh->prepare($query);
642 $sth->execute($basketgroupid);
643 my $result = $sth->fetchrow_hashref;
648 #------------------------------------------------------------#
650 =head3 GetBasketgroups
654 $basketgroups = &GetBasketgroups($booksellerid);
658 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
666 sub GetBasketgroups {
667 my $booksellerid = shift;
668 die "bookseller id is required to edit a basketgroup" unless $booksellerid;
669 my $query = "SELECT * FROM aqbasketgroups WHERE booksellerid=?";
670 my $dbh = C4::Context->dbh;
671 my $sth = $dbh->prepare($query);
672 $sth->execute($booksellerid);
673 my $results = $sth->fetchall_arrayref({});
678 #------------------------------------------------------------#
682 =head2 FUNCTIONS ABOUT ORDERS
688 #------------------------------------------------------------#
690 =head3 GetPendingOrders
694 $orders = &GetPendingOrders($booksellerid, $grouped, $owner);
696 Finds pending orders from the bookseller with the given ID. Ignores
697 completed and cancelled orders.
699 C<$booksellerid> contains the bookseller identifier
700 C<$grouped> contains 0 or 1. 0 means returns the list, 1 means return the total
701 C<$owner> contains 0 or 1. 0 means any owner. 1 means only the list of orders entered by the user itself.
703 C<$orders> is a reference-to-array; each element is a
704 reference-to-hash with the following fields:
705 C<$grouped> is a boolean that, if set to 1 will group all order lines of the same basket
706 in a single result line
710 =item C<authorizedby>
716 These give the value of the corresponding field in the aqorders table
717 of the Koha database.
723 Results are ordered from most to least recent.
727 sub GetPendingOrders {
728 my ($supplierid,$grouped,$owner,$basketno) = @_;
729 my $dbh = C4::Context->dbh;
731 SELECT ".($grouped?"count(*),":"")."aqbasket.basketno,
732 surname,firstname,aqorders.*,biblio.*,biblioitems.isbn,
733 aqbasket.closedate, aqbasket.creationdate, aqbasket.basketname
735 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
736 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
737 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
738 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
740 AND (quantity > quantityreceived OR quantityreceived is NULL)
741 AND datecancellationprinted IS NULL
742 AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL)
744 ## FIXME Why 180 days ???
745 my @query_params = ( $supplierid );
746 my $userenv = C4::Context->userenv;
747 if ( C4::Context->preference("IndependantBranches") ) {
748 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
749 $strsth .= " and (borrowers.branchcode = ?
750 or borrowers.branchcode = '')";
751 push @query_params, $userenv->{branch};
755 $strsth .= " AND aqbasket.authorisedby=? ";
756 push @query_params, $userenv->{'number'};
759 $strsth .= " AND aqbasket.basketno=? ";
760 push @query_params, $basketno;
762 $strsth .= " group by aqbasket.basketno" if $grouped;
763 $strsth .= " order by aqbasket.basketno";
765 my $sth = $dbh->prepare($strsth);
766 $sth->execute( @query_params );
767 my $results = $sth->fetchall_arrayref({});
772 #------------------------------------------------------------#
778 @orders = &GetOrders($basketnumber, $orderby);
780 Looks up the pending (non-cancelled) orders with the given basket
781 number. If C<$booksellerID> is non-empty, only orders from that seller
785 C<&basket> returns a two-element array. C<@orders> is an array of
786 references-to-hash, whose keys are the fields from the aqorders,
787 biblio, and biblioitems tables in the Koha database.
794 my ( $basketno, $orderby ) = @_;
795 my $dbh = C4::Context->dbh;
797 SELECT biblio.*,biblioitems.*,
802 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
803 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
804 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
806 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
809 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
810 $query .= " ORDER BY $orderby";
811 my $sth = $dbh->prepare($query);
812 $sth->execute($basketno);
813 my $results = $sth->fetchall_arrayref({});
818 #------------------------------------------------------------#
820 =head3 GetOrderNumber
824 $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
828 Looks up the ordernumber with the given biblionumber and biblioitemnumber.
830 Returns the number of this order.
834 =item C<$ordernumber> is the order number.
840 my ( $biblionumber,$biblioitemnumber ) = @_;
841 my $dbh = C4::Context->dbh;
846 AND biblioitemnumber=?
848 my $sth = $dbh->prepare($query);
849 $sth->execute( $biblionumber, $biblioitemnumber );
851 return $sth->fetchrow;
854 #------------------------------------------------------------#
860 $order = &GetOrder($ordernumber);
862 Looks up an order by order number.
864 Returns a reference-to-hash describing the order. The keys of
865 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
872 my ($ordernumber) = @_;
873 my $dbh = C4::Context->dbh;
875 SELECT biblioitems.*, biblio.*, aqorders.*
877 LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber
878 LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber
879 WHERE aqorders.ordernumber=?
882 my $sth= $dbh->prepare($query);
883 $sth->execute($ordernumber);
884 my $data = $sth->fetchrow_hashref;
889 #------------------------------------------------------------#
895 &NewOrder(\%hashref);
897 Adds a new order to the database. Any argument that isn't described
898 below is the new value of the field with the same name in the aqorders
899 table of the Koha database.
903 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
906 =item $hashref->{'ordernumber'} is a "minimum order number."
908 =item $hashref->{'budgetdate'} is effectively ignored.
909 If it's undef (anything false) or the string 'now', the current day is used.
910 Else, the upcoming July 1st is used.
912 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
914 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
916 =item defaults entrydate to Now
918 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".
927 my $orderinfo = shift;
928 #### ------------------------------
929 my $dbh = C4::Context->dbh;
933 # if these parameters are missing, we can't continue
934 for my $key (qw/basketno quantity biblionumber budget_id/) {
935 die "Mandatory parameter $key missing" unless $orderinfo->{$key};
938 if ( $orderinfo->{'subscription'} eq 'yes' ) {
939 $orderinfo->{'subscription'} = 1;
941 $orderinfo->{'subscription'} = 0;
943 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
945 my $ordernumber=InsertInTable("aqorders",$orderinfo);
946 return ( $orderinfo->{'basketno'}, $ordernumber );
951 #------------------------------------------------------------#
965 #my ($biblioitemnumber,$ordernumber, $biblionumber) = @_;
966 my ($itemnumber, $ordernumber) = @_;
967 my $dbh = C4::Context->dbh;
969 INSERT INTO aqorders_items
970 (itemnumber, ordernumber)
973 my $sth = $dbh->prepare($query);
974 $sth->execute( $itemnumber, $ordernumber);
977 #------------------------------------------------------------#
983 &ModOrder(\%hashref);
987 Modifies an existing order. Updates the order with order number
988 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All other keys of the hash
989 update the fields with the same name in the aqorders table of the Koha database.
998 my $orderinfo = shift;
1000 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1001 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1003 my $dbh = C4::Context->dbh;
1005 # delete($orderinfo->{'branchcode'});
1006 # the hash contains a lot of entries not in aqorders, so get the columns ...
1007 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1009 my $colnames = $sth->{NAME};
1010 my $query = "UPDATE aqorders SET ";
1012 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1013 # ... and skip hash entries that are not in the aqorders table
1014 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1015 next unless grep(/^$orderinfokey$/, @$colnames);
1016 $query .= "$orderinfokey=?, ";
1017 push(@params, $orderinfo->{$orderinfokey});
1020 $query .= "timestamp=NOW() WHERE ordernumber=?";
1021 # push(@params, $specorderinfo{'ordernumber'});
1022 push(@params, $orderinfo->{'ordernumber'} );
1023 $sth = $dbh->prepare($query);
1024 $sth->execute(@params);
1028 #------------------------------------------------------------#
1034 &ModOrderItem(\%hashref);
1038 Modifies the itemnumber in the aqorders_items table. The input hash needs three entities:
1039 - itemnumber: the old itemnumber
1040 - ordernumber: the order this item is attached to
1041 - newitemnumber: the new itemnumber we want to attach the line to
1050 my $orderiteminfo = shift;
1051 if (! $orderiteminfo->{'ordernumber'} || ! $orderiteminfo->{'itemnumber'} || ! $orderiteminfo->{'newitemnumber'}){
1052 die "Ordernumber, itemnumber and newitemnumber is required";
1055 my $dbh = C4::Context->dbh;
1057 my $query = "UPDATE aqorders_items set itemnumber=? where itemnumber=? and ordernumber=?";
1058 my @params = ($orderiteminfo->{'newitemnumber'}, $orderiteminfo->{'itemnumber'}, $orderiteminfo->{'ordernumber'});
1060 warn Data::Dumper::Dumper(@params);
1061 my $sth = $dbh->prepare($query);
1062 $sth->execute(@params);
1066 #------------------------------------------------------------#
1069 =head3 ModOrderBibliotemNumber
1073 &ModOrderBiblioitemNumber($biblioitemnumber,$ordernumber, $biblionumber);
1075 Modifies the biblioitemnumber for an existing order.
1076 Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
1082 #FIXME: is this used at all?
1083 sub ModOrderBiblioitemNumber {
1084 my ($biblioitemnumber,$ordernumber, $biblionumber) = @_;
1085 my $dbh = C4::Context->dbh;
1088 SET biblioitemnumber = ?
1089 WHERE ordernumber = ?
1090 AND biblionumber = ?";
1091 my $sth = $dbh->prepare($query);
1092 $sth->execute( $biblioitemnumber, $ordernumber, $biblionumber );
1095 #------------------------------------------------------------#
1097 =head3 ModReceiveOrder
1101 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
1102 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
1103 $freight, $bookfund, $rrp);
1105 Updates an order, to reflect the fact that it was received, at least
1106 in part. All arguments not mentioned below update the fields with the
1107 same name in the aqorders table of the Koha database.
1109 If a partial order is received, splits the order into two. The received
1110 portion must have a booksellerinvoicenumber.
1112 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1120 sub ModReceiveOrder {
1122 $biblionumber, $ordernumber, $quantrec, $user, $cost,
1123 $invoiceno, $freight, $rrp, $budget_id, $datereceived
1126 my $dbh = C4::Context->dbh;
1127 # warn "DATE BEFORE : $daterecieved";
1128 # $daterecieved=POSIX::strftime("%Y-%m-%d",CORE::localtime) unless $daterecieved;
1129 # warn "DATE REC : $daterecieved";
1130 $datereceived = C4::Dates->output('iso') unless $datereceived;
1131 my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber );
1132 if ($suggestionid) {
1133 ModStatus( $suggestionid, 'AVAILABLE', '', $biblionumber );
1136 my $sth=$dbh->prepare("
1137 SELECT * FROM aqorders
1138 WHERE biblionumber=? AND aqorders.ordernumber=?");
1140 $sth->execute($biblionumber,$ordernumber);
1141 my $order = $sth->fetchrow_hashref();
1144 if ( $order->{quantity} > $quantrec ) {
1145 $sth=$dbh->prepare("
1147 SET quantityreceived=?
1149 , booksellerinvoicenumber=?
1153 , quantityreceived=?
1154 WHERE biblionumber=? AND ordernumber=?");
1156 $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$quantrec,$biblionumber,$ordernumber);
1159 # create a new order for the remaining items, and set its bookfund.
1160 foreach my $orderkey ( "linenumber", "allocation" ) {
1161 delete($order->{'$orderkey'});
1163 my $newOrder = NewOrder($order);
1165 $sth=$dbh->prepare("update aqorders
1166 set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
1167 unitprice=?,freight=?,rrp=?
1168 where biblionumber=? and ordernumber=?");
1169 $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$biblionumber,$ordernumber);
1172 return $datereceived;
1174 #------------------------------------------------------------#
1178 @results = &SearchOrder($search, $biblionumber, $complete);
1180 Searches for orders.
1182 C<$search> may take one of several forms: if it is an ISBN,
1183 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
1184 order number, C<&ordersearch> returns orders with that order number
1185 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
1186 to be a space-separated list of search terms; in this case, all of the
1187 terms must appear in the title (matching the beginning of title
1190 If C<$complete> is C<yes>, the results will include only completed
1191 orders. In any case, C<&ordersearch> ignores cancelled orders.
1193 C<&ordersearch> returns an array.
1194 C<@results> is an array of references-to-hash with the following keys:
1200 =item C<seriestitle>
1211 #### -------- SearchOrder-------------------------------
1212 my ($ordernumber, $search, $supplierid, $basket) = @_;
1214 my $dbh = C4::Context->dbh;
1219 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1220 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1221 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1222 WHERE (datecancellationprinted is NULL)";
1225 $query .= " AND (aqorders.ordernumber=?)";
1226 push @args, $ordernumber;
1229 $query .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)";
1230 push @args, ("%$search%","%$search%","%$search%");
1233 $query .= "AND aqbasket.booksellerid = ?";
1234 push @args, $supplierid;
1237 $query .= "AND aqorders.basketno = ?";
1238 push @args, $basket;
1241 my $sth = $dbh->prepare($query);
1242 $sth->execute(@args);
1243 my $results = $sth->fetchall_arrayref({});
1248 #------------------------------------------------------------#
1254 &DelOrder($biblionumber, $ordernumber);
1256 Cancel the order with the given order and biblio numbers. It does not
1257 delete any entries in the aqorders table, it merely marks them as
1265 my ( $bibnum, $ordernumber ) = @_;
1266 my $dbh = C4::Context->dbh;
1269 SET datecancellationprinted=now()
1270 WHERE biblionumber=? AND ordernumber=?
1272 my $sth = $dbh->prepare($query);
1273 $sth->execute( $bibnum, $ordernumber );
1277 =head2 FUNCTIONS ABOUT PARCELS
1281 #------------------------------------------------------------#
1287 @results = &GetParcel($booksellerid, $code, $date);
1289 Looks up all of the received items from the supplier with the given
1290 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1292 C<@results> is an array of references-to-hash. The keys of each element are fields from
1293 the aqorders, biblio, and biblioitems tables of the Koha database.
1295 C<@results> is sorted alphabetically by book title.
1302 #gets all orders from a certain supplier, orders them alphabetically
1303 my ( $supplierid, $code, $datereceived ) = @_;
1304 my $dbh = C4::Context->dbh;
1307 if $code; # add % if we search on a given code (otherwise, let him empty)
1309 SELECT authorisedby,
1314 aqorders.biblionumber,
1315 aqorders.ordernumber,
1317 aqorders.quantityreceived,
1324 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1325 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1326 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1328 aqbasket.booksellerid = ?
1329 AND aqorders.booksellerinvoicenumber LIKE ?
1330 AND aqorders.datereceived = ? ";
1332 my @query_params = ( $supplierid, $code, $datereceived );
1333 if ( C4::Context->preference("IndependantBranches") ) {
1334 my $userenv = C4::Context->userenv;
1335 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1336 $strsth .= " and (borrowers.branchcode = ?
1337 or borrowers.branchcode = '')";
1338 push @query_params, $userenv->{branch};
1341 $strsth .= " ORDER BY aqbasket.basketno";
1342 # ## parcelinformation : $strsth
1343 my $sth = $dbh->prepare($strsth);
1344 $sth->execute( @query_params );
1345 while ( my $data = $sth->fetchrow_hashref ) {
1346 push( @results, $data );
1348 # ## countparcelbiblio: scalar(@results)
1354 #------------------------------------------------------------#
1360 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1361 get a lists of parcels.
1370 is the bookseller this function has to get parcels.
1373 To know on what criteria the results list has to be ordered.
1376 is the booksellerinvoicenumber.
1378 =item $datefrom & $dateto
1379 to know on what date this function has to filter its search.
1382 a pointer on a hash list containing parcel informations as such :
1386 =item Last operation
1388 =item Number of biblio
1390 =item Number of items
1397 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1398 my $dbh = C4::Context->dbh;
1399 my @query_params = ();
1401 SELECT aqorders.booksellerinvoicenumber,
1402 datereceived,purchaseordernumber,
1403 count(DISTINCT biblionumber) AS biblio,
1404 sum(quantity) AS itemsexpected,
1405 sum(quantityreceived) AS itemsreceived
1406 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1407 WHERE aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL
1410 if ( defined $code ) {
1411 $strsth .= ' and aqorders.booksellerinvoicenumber like ? ';
1412 # add a % to the end of the code to allow stemming.
1413 push @query_params, "$code%";
1416 if ( defined $datefrom ) {
1417 $strsth .= ' and datereceived >= ? ';
1418 push @query_params, $datefrom;
1421 if ( defined $dateto ) {
1422 $strsth .= 'and datereceived <= ? ';
1423 push @query_params, $dateto;
1426 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
1428 # can't use a placeholder to place this column name.
1429 # but, we could probably be checking to make sure it is a column that will be fetched.
1430 $strsth .= "order by $order " if ($order);
1432 my $sth = $dbh->prepare($strsth);
1434 $sth->execute( @query_params );
1435 my $results = $sth->fetchall_arrayref({});
1440 #------------------------------------------------------------#
1442 =head3 GetLateOrders
1446 @results = &GetLateOrders;
1448 Searches for bookseller with late orders.
1451 the table of supplier with late issues. This table is full of hashref.
1459 my $supplierid = shift;
1462 my $dbh = C4::Context->dbh;
1464 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
1465 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
1467 my @query_params = ($delay); # delay is the first argument regardless
1469 SELECT aqbasket.basketno,
1470 aqorders.ordernumber,
1471 DATE(aqbasket.closedate) AS orderdate,
1472 aqorders.rrp AS unitpricesupplier,
1473 aqorders.ecost AS unitpricelib,
1474 aqbudgets.budget_name AS budget,
1475 borrowers.branchcode AS branch,
1476 aqbooksellers.name AS supplier,
1478 biblioitems.publishercode AS publisher,
1479 biblioitems.publicationyear,
1483 (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber)
1484 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber)
1485 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id),
1486 (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber)
1487 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1488 WHERE aqorders.basketno = aqbasket.basketno
1489 AND ( (datereceived = '' OR datereceived IS NULL)
1490 OR (aqorders.quantityreceived < aqorders.quantity)
1494 if ($dbdriver eq "mysql") {
1496 aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
1497 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1498 DATEDIFF(CURDATE( ),closedate) AS latesince
1500 $from .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) ";
1502 HAVING quantity <> 0
1503 AND unitpricesupplier <> 0
1504 AND unitpricelib <> 0
1507 # FIXME: account for IFNULL as above
1509 aqorders.quantity AS quantity,
1510 aqorders.quantity * aqorders.rrp AS subtotal,
1511 (CURDATE - closedate) AS latesince
1513 $from .= " AND (closedate <= (CURDATE -(INTERVAL ? DAY)) ";
1515 if (defined $supplierid) {
1516 $from .= ' AND aqbasket.booksellerid = ? ';
1517 push @query_params, $supplierid;
1519 if (defined $branch) {
1520 $from .= ' AND borrowers.branchcode LIKE ? ';
1521 push @query_params, $branch;
1523 if (C4::Context->preference("IndependantBranches")
1524 && C4::Context->userenv
1525 && C4::Context->userenv->{flags} != 1 ) {
1526 $from .= ' AND borrowers.branchcode LIKE ? ';
1527 push @query_params, C4::Context->userenv->{branch};
1529 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
1530 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
1531 my $sth = $dbh->prepare($query);
1532 $sth->execute(@query_params);
1534 while (my $data = $sth->fetchrow_hashref) {
1535 $data->{orderdate} = format_date($data->{orderdate});
1536 push @results, $data;
1541 #------------------------------------------------------------#
1547 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on );
1549 Retreives some acquisition history information
1552 $order_loop is a list of hashrefs that each look like this:
1554 'author' => 'Twain, Mark',
1556 'biblionumber' => '215',
1558 'creationdate' => 'MM/DD/YYYY',
1559 'datereceived' => undef,
1562 'invoicenumber' => undef,
1564 'ordernumber' => '1',
1566 'quantityreceived' => undef,
1567 'title' => 'The Adventures of Huckleberry Finn'
1569 $total_qty is the sum of all of the quantities in $order_loop
1570 $total_price is the cost of each in $order_loop times the quantity
1571 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
1578 my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_;
1581 my $total_qtyreceived = 0;
1582 my $total_price = 0;
1584 # don't run the query if there are no parameters (list would be too long for sure !)
1585 if ( $title || $author || $name || $from_placed_on || $to_placed_on ) {
1586 my $dbh = C4::Context->dbh;
1592 name,aqbasket.creationdate,
1593 aqorders.datereceived,
1595 aqorders.quantityreceived,
1597 aqorders.ordernumber,
1598 aqorders.booksellerinvoicenumber as invoicenumber,
1599 aqbooksellers.id as id,
1600 aqorders.biblionumber
1602 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
1603 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
1604 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber";
1606 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
1607 if ( C4::Context->preference("IndependantBranches") );
1609 $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
1611 my @query_params = ();
1613 if ( defined $title ) {
1614 $query .= " AND biblio.title LIKE ? ";
1615 push @query_params, "%$title%";
1618 if ( defined $author ) {
1619 $query .= " AND biblio.author LIKE ? ";
1620 push @query_params, "%$author%";
1623 if ( defined $name ) {
1624 $query .= " AND name LIKE ? ";
1625 push @query_params, "%$name%";
1628 if ( defined $from_placed_on ) {
1629 $query .= " AND creationdate >= ? ";
1630 push @query_params, $from_placed_on;
1633 if ( defined $to_placed_on ) {
1634 $query .= " AND creationdate <= ? ";
1635 push @query_params, $to_placed_on;
1638 if ( C4::Context->preference("IndependantBranches") ) {
1639 my $userenv = C4::Context->userenv;
1640 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1641 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
1642 push @query_params, $userenv->{branch};
1645 $query .= " ORDER BY booksellerid";
1646 my $sth = $dbh->prepare($query);
1647 $sth->execute( @query_params );
1649 while ( my $line = $sth->fetchrow_hashref ) {
1650 $line->{count} = $cnt++;
1651 $line->{toggle} = 1 if $cnt % 2;
1652 push @order_loop, $line;
1653 $line->{creationdate} = format_date( $line->{creationdate} );
1654 $line->{datereceived} = format_date( $line->{datereceived} );
1655 $total_qty += $line->{'quantity'};
1656 $total_qtyreceived += $line->{'quantityreceived'};
1657 $total_price += $line->{'quantity'} * $line->{'ecost'};
1660 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
1663 =head2 GetRecentAcqui
1665 $results = GetRecentAcqui($days);
1667 C<$results> is a ref to a table which containts hashref
1671 sub GetRecentAcqui {
1673 my $dbh = C4::Context->dbh;
1677 ORDER BY timestamp DESC
1680 my $sth = $dbh->prepare($query);
1682 my $results = $sth->fetchall_arrayref({});
1690 $contractlist = &GetContracts($booksellerid, $activeonly);
1692 Looks up the contracts that belong to a bookseller
1694 Returns a list of contracts
1696 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
1698 =item C<$activeonly> if exists get only contracts that are still active.
1704 my ( $booksellerid, $activeonly ) = @_;
1705 my $dbh = C4::Context->dbh;
1707 if (! $activeonly) {
1711 WHERE booksellerid=?
1716 WHERE booksellerid=?
1717 AND contractenddate >= CURDATE( )";
1719 my $sth = $dbh->prepare($query);
1720 $sth->execute( $booksellerid );
1722 while (my $data = $sth->fetchrow_hashref ) {
1723 push(@results, $data);
1729 #------------------------------------------------------------#
1735 $contract = &GetContract($contractID);
1737 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
1745 my ( $contractno ) = @_;
1746 my $dbh = C4::Context->dbh;
1750 WHERE contractnumber=?
1753 my $sth = $dbh->prepare($query);
1754 $sth->execute( $contractno );
1755 my $result = $sth->fetchrow_hashref;
1764 Koha Developement team <info@koha.org>