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);
32 use vars qw($VERSION @ISA @EXPORT);
35 # set the version for version checking
40 &GetBasket &NewBasket &CloseBasket &DelBasket &ModBasket
41 &ModBasketHeader &GetBasketsByBookseller &GetBasketsByBasketgroup
42 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup
45 &GetPendingOrders &GetOrder &GetOrders
46 &GetOrderNumber &GetLateOrders &NewOrder &DelOrder
47 &SearchOrder &GetHistory &GetRecentAcqui
48 &ModOrder &ModOrderItem &ModReceiveOrder &ModOrderBiblioitemNumber
52 &GetParcels &GetParcel
53 &GetContracts &GetContract
55 &GetOrderFromItemnumber
56 &GetItemnumbersFromOrder
64 sub GetOrderFromItemnumber {
65 my ($itemnumber) = @_;
66 my $dbh = C4::Context->dbh;
69 SELECT * from aqorders LEFT JOIN aqorders_items
70 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
71 WHERE itemnumber = ? |;
73 my $sth = $dbh->prepare($query);
77 $sth->execute($itemnumber);
79 my $order = $sth->fetchrow_hashref;
84 # Returns the itemnumber(s) associated with the ordernumber given in parameter
85 sub GetItemnumbersFromOrder {
86 my ($ordernumber) = @_;
87 my $dbh = C4::Context->dbh;
88 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
89 my $sth = $dbh->prepare($query);
90 $sth->execute($ordernumber);
93 while (my $order = $sth->fetchrow_hashref) {
94 push @tab, $order->{'itemnumber'};
108 C4::Acquisition - Koha functions for dealing with orders and acquisitions
116 The functions in this module deal with acquisitions, managing book
117 orders, basket and parcels.
121 =head2 FUNCTIONS ABOUT BASKETS
127 $aqbasket = &GetBasket($basketnumber);
129 get all basket informations in aqbasket for a given basket
132 informations for a given basket returned as a hashref.
140 my $dbh = C4::Context->dbh;
143 concat( b.firstname,' ',b.surname) AS authorisedbyname,
144 b.branchcode AS branch
146 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
149 my $sth=$dbh->prepare($query);
150 $sth->execute($basketno);
151 my $basket = $sth->fetchrow_hashref;
155 #------------------------------------------------------------#
161 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname, $basketnote, $basketbooksellernote, $basketcontractnumber );
163 Create a new basket in aqbasket table
165 =item C<$booksellerid> is a foreign key in the aqbasket table
167 =item C<$authorizedby> is the username of who created the basket
169 The other parameters are optional, see ModBasketHeader for more info on them.
175 # FIXME : this function seems to be unused.
178 my ( $booksellerid, $authorisedby, $basketname, $basketnote, $basketbooksellernote, $basketcontractnumber ) = @_;
179 my $dbh = C4::Context->dbh;
182 (creationdate,booksellerid,authorisedby)
183 VALUES (now(),'$booksellerid','$authorisedby')
187 #find & return basketno MYSQL dependant, but $dbh->last_insert_id always returns null :-(
188 my $basket = $dbh->{'mysql_insertid'};
189 ModBasketHeader($basket, $basketname || '', $basketnote || '', $basketbooksellernote || '', $basketcontractnumber || undef);
193 #------------------------------------------------------------#
199 &CloseBasket($basketno);
201 close a basket (becomes unmodifiable,except for recieves)
209 my $dbh = C4::Context->dbh;
215 my $sth = $dbh->prepare($query);
216 $sth->execute($basketno);
219 #------------------------------------------------------------#
225 &DelBasket($basketno);
227 Deletes the basket that has basketno field $basketno in the aqbasket table.
231 =item C<$basketno> is the primary key of the basket in the aqbasket table.
239 my ( $basketno ) = @_;
240 my $query = "DELETE FROM aqbasket WHERE basketno=?";
241 my $dbh = C4::Context->dbh;
242 my $sth = $dbh->prepare($query);
243 $sth->execute($basketno);
247 #------------------------------------------------------------#
253 &ModBasket($basketinfo);
255 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
259 =item C<$basketno> is the primary key of the basket in the aqbasket table.
267 my $basketinfo = shift;
268 my $query = "UPDATE aqbasket SET ";
270 foreach my $key (keys %$basketinfo){
271 if ($key ne 'basketno'){
272 $query .= "$key=?, ";
273 push(@params, $basketinfo->{$key} || undef );
276 # get rid of the "," at the end of $query
277 if (substr($query, length($query)-2) eq ', '){
282 $query .= "WHERE basketno=?";
283 push(@params, $basketinfo->{'basketno'});
284 my $dbh = C4::Context->dbh;
285 my $sth = $dbh->prepare($query);
286 $sth->execute(@params);
290 #------------------------------------------------------------#
292 =head3 ModBasketHeader
296 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber);
298 Modifies a basket's header.
302 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
304 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
306 =item C<$note> is the "note" field in the "aqbasket" table;
308 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
310 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
317 sub ModBasketHeader {
318 my ($basketno, $basketname, $note, $booksellernote, $contractnumber) = @_;
319 my $query = "UPDATE aqbasket SET basketname=?, note=?, booksellernote=? WHERE basketno=?";
320 my $dbh = C4::Context->dbh;
321 my $sth = $dbh->prepare($query);
322 $sth->execute($basketname,$note,$booksellernote,$basketno);
323 if ( $contractnumber ) {
324 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
325 my $sth2 = $dbh->prepare($query2);
326 $sth2->execute($contractnumber,$basketno);
332 #------------------------------------------------------------#
334 =head3 GetBasketsByBookseller
338 @results = &GetBasketsByBookseller($booksellerid, $extra);
340 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
344 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
346 =item C<$extra> is the extra sql parameters, can be
348 - $extra->{groupby}: group baskets by column
349 ex. $extra->{groupby} = aqbasket.basketgroupid
350 - $extra->{orderby}: order baskets by column
351 - $extra->{limit}: limit number of results (can be helpful for pagination)
359 sub GetBasketsByBookseller {
360 my ($booksellerid, $extra) = @_;
361 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
363 if ($extra->{groupby}) {
364 $query .= " GROUP by $extra->{groupby}";
366 if ($extra->{orderby}){
367 $query .= " ORDER by $extra->{orderby}";
369 if ($extra->{limit}){
370 $query .= " LIMIT $extra->{limit}";
373 my $dbh = C4::Context->dbh;
374 my $sth = $dbh->prepare($query);
375 $sth->execute($booksellerid);
376 my $results = $sth->fetchall_arrayref({});
381 #------------------------------------------------------------#
383 =head3 GetBasketsByBasketgroup
387 $baskets = &GetBasketsByBasketgroup($basketgroupid);
391 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
399 sub GetBasketsByBasketgroup {
400 my $basketgroupid = shift;
401 my $query = "SELECT * FROM aqbasket
402 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?";
403 my $dbh = C4::Context->dbh;
404 my $sth = $dbh->prepare($query);
405 $sth->execute($basketgroupid);
406 my $results = $sth->fetchall_arrayref({});
411 #------------------------------------------------------------#
413 =head3 NewBasketgroup
417 $basketgroupid = NewBasketgroup(\%hashref);
421 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
423 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
425 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
427 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
429 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
438 my $basketgroupinfo = shift;
439 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
440 my $query = "INSERT INTO aqbasketgroups (";
442 foreach my $field ('name', 'closed') {
443 if ( $basketgroupinfo->{$field} ) {
444 $query .= "$field, ";
445 push(@params, $basketgroupinfo->{$field});
448 $query .= "booksellerid) VALUES (";
453 push(@params, $basketgroupinfo->{'booksellerid'});
454 my $dbh = C4::Context->dbh;
455 my $sth = $dbh->prepare($query);
456 $sth->execute(@params);
457 my $basketgroupid = $dbh->{'mysql_insertid'};
458 if( $basketgroupinfo->{'basketlist'} ) {
459 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
460 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
461 my $sth2 = $dbh->prepare($query2);
462 $sth2->execute($basketgroupid, $basketno);
465 return $basketgroupid;
468 #------------------------------------------------------------#
470 =head3 ModBasketgroup
474 ModBasketgroup(\%hashref);
478 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
480 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
482 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
484 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
486 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
495 my $basketgroupinfo = shift;
496 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
497 my $dbh = C4::Context->dbh;
498 my $query = "UPDATE aqbasketgroups SET ";
500 foreach my $field (qw(name closed)) {
501 if ( $basketgroupinfo->{$field} ne undef) {
502 $query .= "$field=?, ";
503 push(@params, $basketgroupinfo->{$field});
508 $query .= " WHERE id=?";
509 push(@params, $basketgroupinfo->{'id'});
510 my $sth = $dbh->prepare($query);
511 $sth->execute(@params);
512 if($basketgroupinfo->{'basketlist'} && @{$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($basketgroupinfo->{'id'}, $basketno);
523 #------------------------------------------------------------#
525 =head3 DelBasketgroup
529 DelBasketgroup($basketgroupid);
533 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
535 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
544 my $basketgroupid = shift;
545 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
546 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
547 my $dbh = C4::Context->dbh;
548 my $sth = $dbh->prepare($query);
549 $sth->execute($basketgroupid);
553 #------------------------------------------------------------#
557 =head2 FUNCTIONS ABOUT ORDERS
563 =head3 GetBasketgroup
567 $basketgroup = &GetBasketgroup($basketgroupid);
571 Returns a reference to the hash containing all infermation about the basketgroup.
580 my $basketgroupid = shift;
581 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
582 my $query = "SELECT * FROM aqbasketgroups WHERE id=?";
583 my $dbh = C4::Context->dbh;
584 my $sth = $dbh->prepare($query);
585 $sth->execute($basketgroupid);
586 my $result = $sth->fetchrow_hashref;
591 #------------------------------------------------------------#
593 =head3 GetBasketgroups
597 $basketgroups = &GetBasketgroups($booksellerid);
601 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
609 sub GetBasketgroups {
610 my $booksellerid = shift;
611 die "bookseller id is required to edit a basketgroup" unless $booksellerid;
612 my $query = "SELECT * FROM aqbasketgroups WHERE booksellerid=?";
613 my $dbh = C4::Context->dbh;
614 my $sth = $dbh->prepare($query);
615 $sth->execute($booksellerid);
616 my $results = $sth->fetchall_arrayref({});
621 #------------------------------------------------------------#
625 =head2 FUNCTIONS ABOUT ORDERS
631 #------------------------------------------------------------#
633 =head3 GetPendingOrders
637 $orders = &GetPendingOrders($booksellerid, $grouped, $owner);
639 Finds pending orders from the bookseller with the given ID. Ignores
640 completed and cancelled orders.
642 C<$booksellerid> contains the bookseller identifier
643 C<$grouped> contains 0 or 1. 0 means returns the list, 1 means return the total
644 C<$owner> contains 0 or 1. 0 means any owner. 1 means only the list of orders entered by the user itself.
646 C<$orders> is a reference-to-array; each element is a
647 reference-to-hash with the following fields:
648 C<$grouped> is a boolean that, if set to 1 will group all order lines of the same basket
649 in a single result line
653 =item C<authorizedby>
659 These give the value of the corresponding field in the aqorders table
660 of the Koha database.
666 Results are ordered from most to least recent.
670 sub GetPendingOrders {
671 my ($supplierid,$grouped,$owner,$basketno) = @_;
672 my $dbh = C4::Context->dbh;
674 SELECT ".($grouped?"count(*),":"")."aqbasket.basketno,
675 surname,firstname,aqorders.*,biblio.*,biblioitems.isbn,
676 aqbasket.closedate, aqbasket.creationdate, aqbasket.basketname
678 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
679 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
680 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
681 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
683 AND (quantity > quantityreceived OR quantityreceived is NULL)
684 AND datecancellationprinted IS NULL
685 AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL)
687 ## FIXME Why 180 days ???
688 my @query_params = ( $supplierid );
689 my $userenv = C4::Context->userenv;
690 if ( C4::Context->preference("IndependantBranches") ) {
691 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
692 $strsth .= " and (borrowers.branchcode = ?
693 or borrowers.branchcode = '')";
694 push @query_params, $userenv->{branch};
698 $strsth .= " AND aqbasket.authorisedby=? ";
699 push @query_params, $userenv->{'number'};
702 $strsth .= " AND aqbasket.basketno=? ";
703 push @query_params, $basketno;
705 $strsth .= " group by aqbasket.basketno" if $grouped;
706 $strsth .= " order by aqbasket.basketno";
708 my $sth = $dbh->prepare($strsth);
709 $sth->execute( @query_params );
710 my $results = $sth->fetchall_arrayref({});
715 #------------------------------------------------------------#
721 @orders = &GetOrders($basketnumber, $orderby);
723 Looks up the pending (non-cancelled) orders with the given basket
724 number. If C<$booksellerID> is non-empty, only orders from that seller
728 C<&basket> returns a two-element array. C<@orders> is an array of
729 references-to-hash, whose keys are the fields from the aqorders,
730 biblio, and biblioitems tables in the Koha database.
737 my ( $basketno, $orderby ) = @_;
738 my $dbh = C4::Context->dbh;
740 SELECT biblio.*,biblioitems.*,
745 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
746 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
747 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
749 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
752 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
753 $query .= " ORDER BY $orderby";
754 my $sth = $dbh->prepare($query);
755 $sth->execute($basketno);
756 my $results = $sth->fetchall_arrayref({});
761 #------------------------------------------------------------#
763 =head3 GetOrderNumber
767 $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
771 Looks up the ordernumber with the given biblionumber and biblioitemnumber.
773 Returns the number of this order.
777 =item C<$ordernumber> is the order number.
783 my ( $biblionumber,$biblioitemnumber ) = @_;
784 my $dbh = C4::Context->dbh;
789 AND biblioitemnumber=?
791 my $sth = $dbh->prepare($query);
792 $sth->execute( $biblionumber, $biblioitemnumber );
794 return $sth->fetchrow;
797 #------------------------------------------------------------#
803 $order = &GetOrder($ordernumber);
805 Looks up an order by order number.
807 Returns a reference-to-hash describing the order. The keys of
808 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
816 my $dbh = C4::Context->dbh;
818 SELECT biblioitems.*, biblio.*, aqorders.*
820 LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber
821 LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber
822 WHERE aqorders.ordernumber=?
825 my $sth= $dbh->prepare($query);
826 $sth->execute($ordnum);
827 my $data = $sth->fetchrow_hashref;
832 #------------------------------------------------------------#
838 &NewOrder(\%hashref);
840 Adds a new order to the database. Any argument that isn't described
841 below is the new value of the field with the same name in the aqorders
842 table of the Koha database.
846 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
849 =item $hashref->{'ordnum'} is a "minimum order number."
851 =item $hashref->{'budgetdate'} is effectively ignored.
852 If it's undef (anything false) or the string 'now', the current day is used.
853 Else, the upcoming July 1st is used.
855 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
857 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
859 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".
868 my $orderinfo = shift;
869 #### ------------------------------
870 my $dbh = C4::Context->dbh;
874 # if these parameters are missing, we can't continue
875 for my $key (qw/basketno quantity biblionumber budget_id/) {
876 die "Mandatory parameter $key missing" unless $orderinfo->{$key};
879 if ( $orderinfo->{'subscription'} eq 'yes' ) {
880 $orderinfo->{'subscription'} = 1;
882 $orderinfo->{'subscription'} = 0;
885 my $query = "INSERT INTO aqorders (";
886 foreach my $orderinfokey (keys %{$orderinfo}) {
887 next if $orderinfokey =~ m/branchcode|entrydate/; # skip branchcode and entrydate, branchcode isnt a vaild col, entrydate we add manually with NOW()
888 $query .= "$orderinfokey,";
889 push(@params, $orderinfo->{$orderinfokey});
892 $query .= "entrydate) VALUES (";
896 $query .= " NOW() )"; #ADDING CURRENT DATE TO 'budgetdate, entrydate, purchaseordernumber'...
898 my $sth = $dbh->prepare($query);
900 $sth->execute(@params);
903 #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
904 my $ordnum = $dbh->{'mysql_insertid'};
907 return ( $orderinfo->{'basketno'}, $ordnum );
912 #------------------------------------------------------------#
926 #my ($biblioitemnumber,$ordnum, $biblionumber) = @_;
927 my ($itemnumber, $ordernumber) = @_;
928 my $dbh = C4::Context->dbh;
930 INSERT INTO aqorders_items
931 (itemnumber, ordernumber)
934 my $sth = $dbh->prepare($query);
935 $sth->execute( $itemnumber, $ordernumber);
938 #------------------------------------------------------------#
944 &ModOrder(\%hashref);
948 Modifies an existing order. Updates the order with order number
949 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All other keys of the hash
950 update the fields with the same name in the aqorders table of the Koha database.
959 my $orderinfo = shift;
961 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
962 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
964 my $dbh = C4::Context->dbh;
966 # delete($orderinfo->{'branchcode'});
967 # the hash contains a lot of entries not in aqorders, so get the columns ...
968 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
970 my $colnames = $sth->{NAME};
971 my $query = "UPDATE aqorders SET ";
973 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
974 # ... and skip hash entries that are not in the aqorders table
975 # FIXME : probably not the best way to do it (would be better to have a correct hash)
976 next unless grep(/^$orderinfokey$/, @$colnames);
977 $query .= "$orderinfokey=?, ";
978 push(@params, $orderinfo->{$orderinfokey});
981 $query .= "timestamp=NOW() WHERE ordernumber=?";
982 # push(@params, $specorderinfo{'ordernumber'});
983 push(@params, $orderinfo->{'ordernumber'} );
984 $sth = $dbh->prepare($query);
985 $sth->execute(@params);
989 #------------------------------------------------------------#
995 &ModOrderItem(\%hashref);
999 Modifies the itemnumber in the aqorders_items table. The input hash needs three entities:
1000 - itemnumber: the old itemnumber
1001 - ordernumber: the order this item is attached to
1002 - newitemnumber: the new itemnumber we want to attach the line to
1011 my $orderiteminfo = shift;
1012 if (! $orderiteminfo->{'ordernumber'} || ! $orderiteminfo->{'itemnumber'} || ! $orderiteminfo->{'newitemnumber'}){
1013 die "Ordernumber, itemnumber and newitemnumber is required";
1016 my $dbh = C4::Context->dbh;
1018 my $query = "UPDATE aqorders_items set itemnumber=? where itemnumber=? and ordernumber=?";
1019 my @params = ($orderiteminfo->{'newitemnumber'}, $orderiteminfo->{'itemnumber'}, $orderiteminfo->{'ordernumber'});
1021 warn Data::Dumper::Dumper(@params);
1022 my $sth = $dbh->prepare($query);
1023 $sth->execute(@params);
1027 #------------------------------------------------------------#
1030 =head3 ModOrderBibliotemNumber
1034 &ModOrderBiblioitemNumber($biblioitemnumber,$ordnum, $biblionumber);
1036 Modifies the biblioitemnumber for an existing order.
1037 Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
1043 #FIXME: is this used at all?
1044 sub ModOrderBiblioitemNumber {
1045 my ($biblioitemnumber,$ordnum, $biblionumber) = @_;
1046 my $dbh = C4::Context->dbh;
1049 SET biblioitemnumber = ?
1050 WHERE ordernumber = ?
1051 AND biblionumber = ?";
1052 my $sth = $dbh->prepare($query);
1053 $sth->execute( $biblioitemnumber, $ordnum, $biblionumber );
1056 #------------------------------------------------------------#
1058 =head3 ModReceiveOrder
1062 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
1063 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
1064 $freight, $bookfund, $rrp);
1066 Updates an order, to reflect the fact that it was received, at least
1067 in part. All arguments not mentioned below update the fields with the
1068 same name in the aqorders table of the Koha database.
1070 If a partial order is received, splits the order into two. The received
1071 portion must have a booksellerinvoicenumber.
1073 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1081 sub ModReceiveOrder {
1083 $biblionumber, $ordnum, $quantrec, $user, $cost,
1084 $invoiceno, $freight, $rrp, $budget_id, $datereceived
1087 my $dbh = C4::Context->dbh;
1088 # warn "DATE BEFORE : $daterecieved";
1089 # $daterecieved=POSIX::strftime("%Y-%m-%d",CORE::localtime) unless $daterecieved;
1090 # warn "DATE REC : $daterecieved";
1091 $datereceived = C4::Dates->output('iso') unless $datereceived;
1092 my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber );
1093 if ($suggestionid) {
1094 ModStatus( $suggestionid, 'AVAILABLE', '', $biblionumber );
1097 my $sth=$dbh->prepare("
1098 SELECT * FROM aqorders
1099 WHERE biblionumber=? AND aqorders.ordernumber=?");
1101 $sth->execute($biblionumber,$ordnum);
1102 my $order = $sth->fetchrow_hashref();
1105 if ( $order->{quantity} > $quantrec ) {
1106 $sth=$dbh->prepare("
1108 SET quantityreceived=?
1110 , booksellerinvoicenumber=?
1114 , quantityreceived=?
1115 WHERE biblionumber=? AND ordernumber=?");
1117 $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$quantrec,$biblionumber,$ordnum);
1120 # create a new order for the remaining items, and set its bookfund.
1121 foreach my $orderkey ( "linenumber", "allocation" ) {
1122 delete($order->{'$orderkey'});
1124 my $newOrder = NewOrder($order);
1126 $sth=$dbh->prepare("update aqorders
1127 set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
1128 unitprice=?,freight=?,rrp=?
1129 where biblionumber=? and ordernumber=?");
1130 $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$biblionumber,$ordnum);
1133 return $datereceived;
1135 #------------------------------------------------------------#
1139 @results = &SearchOrder($search, $biblionumber, $complete);
1141 Searches for orders.
1143 C<$search> may take one of several forms: if it is an ISBN,
1144 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
1145 order number, C<&ordersearch> returns orders with that order number
1146 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
1147 to be a space-separated list of search terms; in this case, all of the
1148 terms must appear in the title (matching the beginning of title
1151 If C<$complete> is C<yes>, the results will include only completed
1152 orders. In any case, C<&ordersearch> ignores cancelled orders.
1154 C<&ordersearch> returns an array.
1155 C<@results> is an array of references-to-hash with the following keys:
1161 =item C<seriestitle>
1172 #### -------- SearchOrder-------------------------------
1173 my ($ordernumber, $search, $supplierid, $basket) = @_;
1175 my $dbh = C4::Context->dbh;
1180 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1181 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1182 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1183 WHERE (datecancellationprinted is NULL)";
1186 $query .= " AND (aqorders.ordernumber=?)";
1187 push @args, $ordernumber;
1190 $query .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)";
1191 push @args, ("%$search%","%$search%","%$search%");
1194 $query .= "AND aqbasket.booksellerid = ?";
1195 push @args, $supplierid;
1198 $query .= "AND aqorders.basketno = ?";
1199 push @args, $basket;
1202 my $sth = $dbh->prepare($query);
1203 $sth->execute(@args);
1204 my $results = $sth->fetchall_arrayref({});
1209 #------------------------------------------------------------#
1215 &DelOrder($biblionumber, $ordernumber);
1217 Cancel the order with the given order and biblio numbers. It does not
1218 delete any entries in the aqorders table, it merely marks them as
1226 my ( $bibnum, $ordnum ) = @_;
1227 my $dbh = C4::Context->dbh;
1230 SET datecancellationprinted=now()
1231 WHERE biblionumber=? AND ordernumber=?
1233 my $sth = $dbh->prepare($query);
1234 $sth->execute( $bibnum, $ordnum );
1238 =head2 FUNCTIONS ABOUT PARCELS
1242 #------------------------------------------------------------#
1248 @results = &GetParcel($booksellerid, $code, $date);
1250 Looks up all of the received items from the supplier with the given
1251 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1253 C<@results> is an array of references-to-hash. The keys of each element are fields from
1254 the aqorders, biblio, and biblioitems tables of the Koha database.
1256 C<@results> is sorted alphabetically by book title.
1263 #gets all orders from a certain supplier, orders them alphabetically
1264 my ( $supplierid, $code, $datereceived ) = @_;
1265 my $dbh = C4::Context->dbh;
1268 if $code; # add % if we search on a given code (otherwise, let him empty)
1270 SELECT authorisedby,
1275 aqorders.biblionumber,
1276 aqorders.ordernumber,
1278 aqorders.quantityreceived,
1285 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1286 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1287 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1289 aqbasket.booksellerid = ?
1290 AND aqorders.booksellerinvoicenumber LIKE ?
1291 AND aqorders.datereceived = ? ";
1293 my @query_params = ( $supplierid, $code, $datereceived );
1294 if ( C4::Context->preference("IndependantBranches") ) {
1295 my $userenv = C4::Context->userenv;
1296 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1297 $strsth .= " and (borrowers.branchcode = ?
1298 or borrowers.branchcode = '')";
1299 push @query_params, $userenv->{branch};
1302 $strsth .= " ORDER BY aqbasket.basketno";
1303 # ## parcelinformation : $strsth
1304 my $sth = $dbh->prepare($strsth);
1305 $sth->execute( @query_params );
1306 while ( my $data = $sth->fetchrow_hashref ) {
1307 push( @results, $data );
1309 # ## countparcelbiblio: scalar(@results)
1315 #------------------------------------------------------------#
1321 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1322 get a lists of parcels.
1331 is the bookseller this function has to get parcels.
1334 To know on what criteria the results list has to be ordered.
1337 is the booksellerinvoicenumber.
1339 =item $datefrom & $dateto
1340 to know on what date this function has to filter its search.
1343 a pointer on a hash list containing parcel informations as such :
1347 =item Last operation
1349 =item Number of biblio
1351 =item Number of items
1358 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1359 my $dbh = C4::Context->dbh;
1360 my @query_params = ();
1362 SELECT aqorders.booksellerinvoicenumber,
1363 datereceived,purchaseordernumber,
1364 count(DISTINCT biblionumber) AS biblio,
1365 sum(quantity) AS itemsexpected,
1366 sum(quantityreceived) AS itemsreceived
1367 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1368 WHERE aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL
1371 if ( defined $code ) {
1372 $strsth .= ' and aqorders.booksellerinvoicenumber like ? ';
1373 # add a % to the end of the code to allow stemming.
1374 push @query_params, "$code%";
1377 if ( defined $datefrom ) {
1378 $strsth .= ' and datereceived >= ? ';
1379 push @query_params, $datefrom;
1382 if ( defined $dateto ) {
1383 $strsth .= 'and datereceived <= ? ';
1384 push @query_params, $dateto;
1387 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
1389 # can't use a placeholder to place this column name.
1390 # but, we could probably be checking to make sure it is a column that will be fetched.
1391 $strsth .= "order by $order " if ($order);
1393 my $sth = $dbh->prepare($strsth);
1395 $sth->execute( @query_params );
1396 my $results = $sth->fetchall_arrayref({});
1401 #------------------------------------------------------------#
1403 =head3 GetLateOrders
1407 @results = &GetLateOrders;
1409 Searches for bookseller with late orders.
1412 the table of supplier with late issues. This table is full of hashref.
1420 my $supplierid = shift;
1423 my $dbh = C4::Context->dbh;
1425 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
1426 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
1428 my @query_params = ($delay); # delay is the first argument regardless
1430 SELECT aqbasket.basketno,
1431 aqorders.ordernumber,
1432 DATE(aqbasket.closedate) AS orderdate,
1433 aqorders.rrp AS unitpricesupplier,
1434 aqorders.ecost AS unitpricelib,
1435 aqbudgets.budget_name AS budget,
1436 borrowers.branchcode AS branch,
1437 aqbooksellers.name AS supplier,
1439 biblioitems.publishercode AS publisher,
1440 biblioitems.publicationyear,
1444 (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber)
1445 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber)
1446 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id),
1447 (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber)
1448 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1449 WHERE aqorders.basketno = aqbasket.basketno
1450 AND ( (datereceived = '' OR datereceived IS NULL)
1451 OR (aqorders.quantityreceived < aqorders.quantity)
1455 if ($dbdriver eq "mysql") {
1457 aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
1458 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1459 DATEDIFF(CURDATE( ),closedate) AS latesince
1461 $from .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) ";
1463 HAVING quantity <> 0
1464 AND unitpricesupplier <> 0
1465 AND unitpricelib <> 0
1468 # FIXME: account for IFNULL as above
1470 aqorders.quantity AS quantity,
1471 aqorders.quantity * aqorders.rrp AS subtotal,
1472 (CURDATE - closedate) AS latesince
1474 $from .= " AND (closedate <= (CURDATE -(INTERVAL ? DAY)) ";
1476 if (defined $supplierid) {
1477 $from .= ' AND aqbasket.booksellerid = ? ';
1478 push @query_params, $supplierid;
1480 if (defined $branch) {
1481 $from .= ' AND borrowers.branchcode LIKE ? ';
1482 push @query_params, $branch;
1484 if (C4::Context->preference("IndependantBranches")
1485 && C4::Context->userenv
1486 && C4::Context->userenv->{flags} != 1 ) {
1487 $from .= ' AND borrowers.branchcode LIKE ? ';
1488 push @query_params, C4::Context->userenv->{branch};
1490 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
1491 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
1492 my $sth = $dbh->prepare($query);
1493 $sth->execute(@query_params);
1495 while (my $data = $sth->fetchrow_hashref) {
1496 $data->{orderdate} = format_date($data->{orderdate});
1497 push @results, $data;
1502 #------------------------------------------------------------#
1508 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on );
1510 Retreives some acquisition history information
1513 $order_loop is a list of hashrefs that each look like this:
1515 'author' => 'Twain, Mark',
1517 'biblionumber' => '215',
1519 'creationdate' => 'MM/DD/YYYY',
1520 'datereceived' => undef,
1523 'invoicenumber' => undef,
1525 'ordernumber' => '1',
1527 'quantityreceived' => undef,
1528 'title' => 'The Adventures of Huckleberry Finn'
1530 $total_qty is the sum of all of the quantities in $order_loop
1531 $total_price is the cost of each in $order_loop times the quantity
1532 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
1539 my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_;
1542 my $total_qtyreceived = 0;
1543 my $total_price = 0;
1545 # don't run the query if there are no parameters (list would be too long for sure !)
1546 if ( $title || $author || $name || $from_placed_on || $to_placed_on ) {
1547 my $dbh = C4::Context->dbh;
1553 name,aqbasket.creationdate,
1554 aqorders.datereceived,
1556 aqorders.quantityreceived,
1558 aqorders.ordernumber,
1559 aqorders.booksellerinvoicenumber as invoicenumber,
1560 aqbooksellers.id as id,
1561 aqorders.biblionumber
1563 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
1564 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
1565 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber";
1567 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
1568 if ( C4::Context->preference("IndependantBranches") );
1570 $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
1572 my @query_params = ();
1574 if ( defined $title ) {
1575 $query .= " AND biblio.title LIKE ? ";
1576 push @query_params, "%$title%";
1579 if ( defined $author ) {
1580 $query .= " AND biblio.author LIKE ? ";
1581 push @query_params, "%$author%";
1584 if ( defined $name ) {
1585 $query .= " AND name LIKE ? ";
1586 push @query_params, "%$name%";
1589 if ( defined $from_placed_on ) {
1590 $query .= " AND creationdate >= ? ";
1591 push @query_params, $from_placed_on;
1594 if ( defined $to_placed_on ) {
1595 $query .= " AND creationdate <= ? ";
1596 push @query_params, $to_placed_on;
1599 if ( C4::Context->preference("IndependantBranches") ) {
1600 my $userenv = C4::Context->userenv;
1601 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1602 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
1603 push @query_params, $userenv->{branch};
1606 $query .= " ORDER BY booksellerid";
1607 my $sth = $dbh->prepare($query);
1608 $sth->execute( @query_params );
1610 while ( my $line = $sth->fetchrow_hashref ) {
1611 $line->{count} = $cnt++;
1612 $line->{toggle} = 1 if $cnt % 2;
1613 push @order_loop, $line;
1614 $line->{creationdate} = format_date( $line->{creationdate} );
1615 $line->{datereceived} = format_date( $line->{datereceived} );
1616 $total_qty += $line->{'quantity'};
1617 $total_qtyreceived += $line->{'quantityreceived'};
1618 $total_price += $line->{'quantity'} * $line->{'ecost'};
1621 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
1624 =head2 GetRecentAcqui
1626 $results = GetRecentAcqui($days);
1628 C<$results> is a ref to a table which containts hashref
1632 sub GetRecentAcqui {
1634 my $dbh = C4::Context->dbh;
1638 ORDER BY timestamp DESC
1641 my $sth = $dbh->prepare($query);
1643 my $results = $sth->fetchall_arrayref({});
1651 $contractlist = &GetContracts($booksellerid, $activeonly);
1653 Looks up the contracts that belong to a bookseller
1655 Returns a list of contracts
1657 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
1659 =item C<$activeonly> if exists get only contracts that are still active.
1665 my ( $booksellerid, $activeonly ) = @_;
1666 my $dbh = C4::Context->dbh;
1668 if (! $activeonly) {
1672 WHERE booksellerid=?
1677 WHERE booksellerid=?
1678 AND contractenddate >= CURDATE( )";
1680 my $sth = $dbh->prepare($query);
1681 $sth->execute( $booksellerid );
1683 while (my $data = $sth->fetchrow_hashref ) {
1684 push(@results, $data);
1690 #------------------------------------------------------------#
1696 $contract = &GetContract($contractID);
1698 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
1706 my ( $contractno ) = @_;
1707 my $dbh = C4::Context->dbh;
1711 WHERE contractnumber=?
1714 my $sth = $dbh->prepare($query);
1715 $sth->execute( $contractno );
1716 my $result = $sth->fetchrow_hashref;
1725 Koha Developement team <info@koha.org>