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) = @_;
1176 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)
1184 AND (aqorders.ordernumber=?))";
1185 my $sth = $dbh->prepare($query);
1186 $sth->execute($ordernumber);
1187 my $results = $sth->fetchall_arrayref({});
1191 my $dbh = C4::Context->dbh;
1195 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1196 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1197 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1198 WHERE ((datecancellationprinted is NULL)
1199 AND (biblio.title like ? OR biblioitems.isbn like ?))";
1200 my $sth = $dbh->prepare($query);
1201 $sth->execute("%$search%","%$search%");
1202 my $results = $sth->fetchall_arrayref({});
1208 #------------------------------------------------------------#
1214 &DelOrder($biblionumber, $ordernumber);
1216 Cancel the order with the given order and biblio numbers. It does not
1217 delete any entries in the aqorders table, it merely marks them as
1225 my ( $bibnum, $ordnum ) = @_;
1226 my $dbh = C4::Context->dbh;
1229 SET datecancellationprinted=now()
1230 WHERE biblionumber=? AND ordernumber=?
1232 my $sth = $dbh->prepare($query);
1233 $sth->execute( $bibnum, $ordnum );
1237 =head2 FUNCTIONS ABOUT PARCELS
1241 #------------------------------------------------------------#
1247 @results = &GetParcel($booksellerid, $code, $date);
1249 Looks up all of the received items from the supplier with the given
1250 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1252 C<@results> is an array of references-to-hash. The keys of each element are fields from
1253 the aqorders, biblio, and biblioitems tables of the Koha database.
1255 C<@results> is sorted alphabetically by book title.
1262 #gets all orders from a certain supplier, orders them alphabetically
1263 my ( $supplierid, $code, $datereceived ) = @_;
1264 my $dbh = C4::Context->dbh;
1267 if $code; # add % if we search on a given code (otherwise, let him empty)
1269 SELECT authorisedby,
1274 aqorders.biblionumber,
1275 aqorders.ordernumber,
1277 aqorders.quantityreceived,
1284 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1285 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1286 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1288 aqbasket.booksellerid = ?
1289 AND aqorders.booksellerinvoicenumber LIKE ?
1290 AND aqorders.datereceived = ? ";
1292 my @query_params = ( $supplierid, $code, $datereceived );
1293 if ( C4::Context->preference("IndependantBranches") ) {
1294 my $userenv = C4::Context->userenv;
1295 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1296 $strsth .= " and (borrowers.branchcode = ?
1297 or borrowers.branchcode = '')";
1298 push @query_params, $userenv->{branch};
1301 $strsth .= " ORDER BY aqbasket.basketno";
1302 # ## parcelinformation : $strsth
1303 my $sth = $dbh->prepare($strsth);
1304 $sth->execute( @query_params );
1305 while ( my $data = $sth->fetchrow_hashref ) {
1306 push( @results, $data );
1308 # ## countparcelbiblio: scalar(@results)
1314 #------------------------------------------------------------#
1320 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1321 get a lists of parcels.
1330 is the bookseller this function has to get parcels.
1333 To know on what criteria the results list has to be ordered.
1336 is the booksellerinvoicenumber.
1338 =item $datefrom & $dateto
1339 to know on what date this function has to filter its search.
1342 a pointer on a hash list containing parcel informations as such :
1346 =item Last operation
1348 =item Number of biblio
1350 =item Number of items
1357 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1358 my $dbh = C4::Context->dbh;
1359 my @query_params = ();
1361 SELECT aqorders.booksellerinvoicenumber,
1362 datereceived,purchaseordernumber,
1363 count(DISTINCT biblionumber) AS biblio,
1364 sum(quantity) AS itemsexpected,
1365 sum(quantityreceived) AS itemsreceived
1366 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1367 WHERE aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL
1370 if ( defined $code ) {
1371 $strsth .= ' and aqorders.booksellerinvoicenumber like ? ';
1372 # add a % to the end of the code to allow stemming.
1373 push @query_params, "$code%";
1376 if ( defined $datefrom ) {
1377 $strsth .= ' and datereceived >= ? ';
1378 push @query_params, $datefrom;
1381 if ( defined $dateto ) {
1382 $strsth .= 'and datereceived <= ? ';
1383 push @query_params, $dateto;
1386 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
1388 # can't use a placeholder to place this column name.
1389 # but, we could probably be checking to make sure it is a column that will be fetched.
1390 $strsth .= "order by $order " if ($order);
1392 my $sth = $dbh->prepare($strsth);
1394 $sth->execute( @query_params );
1395 my $results = $sth->fetchall_arrayref({});
1400 #------------------------------------------------------------#
1402 =head3 GetLateOrders
1406 @results = &GetLateOrders;
1408 Searches for bookseller with late orders.
1411 the table of supplier with late issues. This table is full of hashref.
1419 my $supplierid = shift;
1422 my $dbh = C4::Context->dbh;
1424 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
1425 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
1427 my @query_params = ($delay); # delay is the first argument regardless
1429 SELECT aqbasket.basketno,
1430 aqorders.ordernumber,
1431 DATE(aqbasket.closedate) AS orderdate,
1432 aqorders.rrp AS unitpricesupplier,
1433 aqorders.ecost AS unitpricelib,
1434 aqbudgets.budget_name AS budget,
1435 borrowers.branchcode AS branch,
1436 aqbooksellers.name AS supplier,
1438 biblioitems.publishercode AS publisher,
1439 biblioitems.publicationyear,
1443 (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber)
1444 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber)
1445 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id),
1446 (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber)
1447 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1448 WHERE aqorders.basketno = aqbasket.basketno
1449 AND ( (datereceived = '' OR datereceived IS NULL)
1450 OR (aqorders.quantityreceived < aqorders.quantity)
1454 if ($dbdriver eq "mysql") {
1456 aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
1457 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1458 DATEDIFF(CURDATE( ),closedate) AS latesince
1460 $from .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) ";
1462 HAVING quantity <> 0
1463 AND unitpricesupplier <> 0
1464 AND unitpricelib <> 0
1467 # FIXME: account for IFNULL as above
1469 aqorders.quantity AS quantity,
1470 aqorders.quantity * aqorders.rrp AS subtotal,
1471 (CURDATE - closedate) AS latesince
1473 $from .= " AND (closedate <= (CURDATE -(INTERVAL ? DAY)) ";
1475 if (defined $supplierid) {
1476 $from .= ' AND aqbasket.booksellerid = ? ';
1477 push @query_params, $supplierid;
1479 if (defined $branch) {
1480 $from .= ' AND borrowers.branchcode LIKE ? ';
1481 push @query_params, $branch;
1483 if (C4::Context->preference("IndependantBranches")
1484 && C4::Context->userenv
1485 && C4::Context->userenv->{flags} != 1 ) {
1486 $from .= ' AND borrowers.branchcode LIKE ? ';
1487 push @query_params, C4::Context->userenv->{branch};
1489 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
1490 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
1491 my $sth = $dbh->prepare($query);
1492 $sth->execute(@query_params);
1494 while (my $data = $sth->fetchrow_hashref) {
1495 $data->{orderdate} = format_date($data->{orderdate});
1496 push @results, $data;
1501 #------------------------------------------------------------#
1507 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on );
1509 Retreives some acquisition history information
1512 $order_loop is a list of hashrefs that each look like this:
1514 'author' => 'Twain, Mark',
1516 'biblionumber' => '215',
1518 'creationdate' => 'MM/DD/YYYY',
1519 'datereceived' => undef,
1522 'invoicenumber' => undef,
1524 'ordernumber' => '1',
1526 'quantityreceived' => undef,
1527 'title' => 'The Adventures of Huckleberry Finn'
1529 $total_qty is the sum of all of the quantities in $order_loop
1530 $total_price is the cost of each in $order_loop times the quantity
1531 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
1538 my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_;
1541 my $total_qtyreceived = 0;
1542 my $total_price = 0;
1544 # don't run the query if there are no parameters (list would be too long for sure !)
1545 if ( $title || $author || $name || $from_placed_on || $to_placed_on ) {
1546 my $dbh = C4::Context->dbh;
1552 name,aqbasket.creationdate,
1553 aqorders.datereceived,
1555 aqorders.quantityreceived,
1557 aqorders.ordernumber,
1558 aqorders.booksellerinvoicenumber as invoicenumber,
1559 aqbooksellers.id as id,
1560 aqorders.biblionumber
1562 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
1563 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
1564 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber";
1566 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
1567 if ( C4::Context->preference("IndependantBranches") );
1569 $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
1571 my @query_params = ();
1573 if ( defined $title ) {
1574 $query .= " AND biblio.title LIKE ? ";
1575 push @query_params, "%$title%";
1578 if ( defined $author ) {
1579 $query .= " AND biblio.author LIKE ? ";
1580 push @query_params, "%$author%";
1583 if ( defined $name ) {
1584 $query .= " AND name LIKE ? ";
1585 push @query_params, "%$name%";
1588 if ( defined $from_placed_on ) {
1589 $query .= " AND creationdate >= ? ";
1590 push @query_params, $from_placed_on;
1593 if ( defined $to_placed_on ) {
1594 $query .= " AND creationdate <= ? ";
1595 push @query_params, $to_placed_on;
1598 if ( C4::Context->preference("IndependantBranches") ) {
1599 my $userenv = C4::Context->userenv;
1600 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1601 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
1602 push @query_params, $userenv->{branch};
1605 $query .= " ORDER BY booksellerid";
1606 my $sth = $dbh->prepare($query);
1607 $sth->execute( @query_params );
1609 while ( my $line = $sth->fetchrow_hashref ) {
1610 $line->{count} = $cnt++;
1611 $line->{toggle} = 1 if $cnt % 2;
1612 push @order_loop, $line;
1613 $line->{creationdate} = format_date( $line->{creationdate} );
1614 $line->{datereceived} = format_date( $line->{datereceived} );
1615 $total_qty += $line->{'quantity'};
1616 $total_qtyreceived += $line->{'quantityreceived'};
1617 $total_price += $line->{'quantity'} * $line->{'ecost'};
1620 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
1623 =head2 GetRecentAcqui
1625 $results = GetRecentAcqui($days);
1627 C<$results> is a ref to a table which containts hashref
1631 sub GetRecentAcqui {
1633 my $dbh = C4::Context->dbh;
1637 ORDER BY timestamp DESC
1640 my $sth = $dbh->prepare($query);
1642 my $results = $sth->fetchall_arrayref({});
1650 $contractlist = &GetContracts($booksellerid, $activeonly);
1652 Looks up the contracts that belong to a bookseller
1654 Returns a list of contracts
1656 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
1658 =item C<$activeonly> if exists get only contracts that are still active.
1664 my ( $booksellerid, $activeonly ) = @_;
1665 my $dbh = C4::Context->dbh;
1667 if (! $activeonly) {
1671 WHERE booksellerid=?
1676 WHERE booksellerid=?
1677 AND contractenddate >= CURDATE( )";
1679 my $sth = $dbh->prepare($query);
1680 $sth->execute( $booksellerid );
1682 while (my $data = $sth->fetchrow_hashref ) {
1683 push(@results, $data);
1689 #------------------------------------------------------------#
1695 $contract = &GetContract($contractID);
1697 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
1705 my ( $contractno ) = @_;
1706 my $dbh = C4::Context->dbh;
1710 WHERE contractnumber=?
1713 my $sth = $dbh->prepare($query);
1714 $sth->execute( $contractno );
1715 my $result = $sth->fetchrow_hashref;
1724 Koha Developement team <info@koha.org>