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 &CloseBasketgroup &ReOpenBasketgroup &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 #------------------------------------------------------------#
221 =head3 CloseBasketgroup
225 &CloseBasketgroup($basketgroupno);
233 sub CloseBasketgroup {
234 my ($basketgroupno) = @_;
235 my $dbh = C4::Context->dbh;
236 my $sth = $dbh->prepare("
237 UPDATE aqbasketgroups
241 $sth->execute($basketgroupno);
244 #------------------------------------------------------------#
246 =head3 ReOpenBaskergroup($basketgroupno)
250 &ReOpenBaskergroup($basketgroupno);
258 sub ReOpenBasketgroup {
259 my ($basketgroupno) = @_;
260 my $dbh = C4::Context->dbh;
261 my $sth = $dbh->prepare("
262 UPDATE aqbasketgroups
266 $sth->execute($basketgroupno);
269 #------------------------------------------------------------#
276 &DelBasket($basketno);
278 Deletes the basket that has basketno field $basketno in the aqbasket table.
282 =item C<$basketno> is the primary key of the basket in the aqbasket table.
290 my ( $basketno ) = @_;
291 my $query = "DELETE FROM aqbasket WHERE basketno=?";
292 my $dbh = C4::Context->dbh;
293 my $sth = $dbh->prepare($query);
294 $sth->execute($basketno);
298 #------------------------------------------------------------#
304 &ModBasket($basketinfo);
306 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
310 =item C<$basketno> is the primary key of the basket in the aqbasket table.
318 my $basketinfo = shift;
319 my $query = "UPDATE aqbasket SET ";
321 foreach my $key (keys %$basketinfo){
322 if ($key ne 'basketno'){
323 $query .= "$key=?, ";
324 push(@params, $basketinfo->{$key} || undef );
327 # get rid of the "," at the end of $query
328 if (substr($query, length($query)-2) eq ', '){
333 $query .= "WHERE basketno=?";
334 push(@params, $basketinfo->{'basketno'});
335 my $dbh = C4::Context->dbh;
336 my $sth = $dbh->prepare($query);
337 $sth->execute(@params);
341 #------------------------------------------------------------#
343 =head3 ModBasketHeader
347 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber);
349 Modifies a basket's header.
353 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
355 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
357 =item C<$note> is the "note" field in the "aqbasket" table;
359 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
361 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
368 sub ModBasketHeader {
369 my ($basketno, $basketname, $note, $booksellernote, $contractnumber) = @_;
370 my $query = "UPDATE aqbasket SET basketname=?, note=?, booksellernote=? WHERE basketno=?";
371 my $dbh = C4::Context->dbh;
372 my $sth = $dbh->prepare($query);
373 $sth->execute($basketname,$note,$booksellernote,$basketno);
374 if ( $contractnumber ) {
375 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
376 my $sth2 = $dbh->prepare($query2);
377 $sth2->execute($contractnumber,$basketno);
383 #------------------------------------------------------------#
385 =head3 GetBasketsByBookseller
389 @results = &GetBasketsByBookseller($booksellerid, $extra);
391 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
395 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
397 =item C<$extra> is the extra sql parameters, can be
399 - $extra->{groupby}: group baskets by column
400 ex. $extra->{groupby} = aqbasket.basketgroupid
401 - $extra->{orderby}: order baskets by column
402 - $extra->{limit}: limit number of results (can be helpful for pagination)
410 sub GetBasketsByBookseller {
411 my ($booksellerid, $extra) = @_;
412 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
414 if ($extra->{groupby}) {
415 $query .= " GROUP by $extra->{groupby}";
417 if ($extra->{orderby}){
418 $query .= " ORDER by $extra->{orderby}";
420 if ($extra->{limit}){
421 $query .= " LIMIT $extra->{limit}";
424 my $dbh = C4::Context->dbh;
425 my $sth = $dbh->prepare($query);
426 $sth->execute($booksellerid);
427 my $results = $sth->fetchall_arrayref({});
432 #------------------------------------------------------------#
434 =head3 GetBasketsByBasketgroup
438 $baskets = &GetBasketsByBasketgroup($basketgroupid);
442 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
450 sub GetBasketsByBasketgroup {
451 my $basketgroupid = shift;
452 my $query = "SELECT * FROM aqbasket
453 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?";
454 my $dbh = C4::Context->dbh;
455 my $sth = $dbh->prepare($query);
456 $sth->execute($basketgroupid);
457 my $results = $sth->fetchall_arrayref({});
462 #------------------------------------------------------------#
464 =head3 NewBasketgroup
468 $basketgroupid = NewBasketgroup(\%hashref);
472 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
474 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
476 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
478 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
480 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
489 my $basketgroupinfo = shift;
490 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
491 my $query = "INSERT INTO aqbasketgroups (";
493 foreach my $field ('name', 'closed') {
494 if ( $basketgroupinfo->{$field} ) {
495 $query .= "$field, ";
496 push(@params, $basketgroupinfo->{$field});
499 $query .= "booksellerid) VALUES (";
504 push(@params, $basketgroupinfo->{'booksellerid'});
505 my $dbh = C4::Context->dbh;
506 my $sth = $dbh->prepare($query);
507 $sth->execute(@params);
508 my $basketgroupid = $dbh->{'mysql_insertid'};
509 if( $basketgroupinfo->{'basketlist'} ) {
510 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
511 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
512 my $sth2 = $dbh->prepare($query2);
513 $sth2->execute($basketgroupid, $basketno);
516 return $basketgroupid;
519 #------------------------------------------------------------#
521 =head3 ModBasketgroup
525 ModBasketgroup(\%hashref);
529 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
531 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
533 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
535 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
537 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
546 my $basketgroupinfo = shift;
547 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
548 my $dbh = C4::Context->dbh;
549 my $query = "UPDATE aqbasketgroups SET ";
551 foreach my $field (qw(name closed)) {
552 if ( $basketgroupinfo->{$field} ne undef) {
553 $query .= "$field=?, ";
554 push(@params, $basketgroupinfo->{$field});
559 $query .= " WHERE id=?";
560 push(@params, $basketgroupinfo->{'id'});
561 my $sth = $dbh->prepare($query);
562 $sth->execute(@params);
564 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
565 $sth->execute($basketgroupinfo->{'id'});
567 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
568 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
569 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
570 $sth->execute($basketgroupinfo->{'id'}, $basketno);
577 #------------------------------------------------------------#
579 =head3 DelBasketgroup
583 DelBasketgroup($basketgroupid);
587 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
589 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
598 my $basketgroupid = shift;
599 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
600 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
601 my $dbh = C4::Context->dbh;
602 my $sth = $dbh->prepare($query);
603 $sth->execute($basketgroupid);
607 #------------------------------------------------------------#
611 =head2 FUNCTIONS ABOUT ORDERS
617 =head3 GetBasketgroup
621 $basketgroup = &GetBasketgroup($basketgroupid);
625 Returns a reference to the hash containing all infermation about the basketgroup.
634 my $basketgroupid = shift;
635 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
636 my $query = "SELECT * FROM aqbasketgroups WHERE id=?";
637 my $dbh = C4::Context->dbh;
638 my $sth = $dbh->prepare($query);
639 $sth->execute($basketgroupid);
640 my $result = $sth->fetchrow_hashref;
645 #------------------------------------------------------------#
647 =head3 GetBasketgroups
651 $basketgroups = &GetBasketgroups($booksellerid);
655 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
663 sub GetBasketgroups {
664 my $booksellerid = shift;
665 die "bookseller id is required to edit a basketgroup" unless $booksellerid;
666 my $query = "SELECT * FROM aqbasketgroups WHERE booksellerid=?";
667 my $dbh = C4::Context->dbh;
668 my $sth = $dbh->prepare($query);
669 $sth->execute($booksellerid);
670 my $results = $sth->fetchall_arrayref({});
675 #------------------------------------------------------------#
679 =head2 FUNCTIONS ABOUT ORDERS
685 #------------------------------------------------------------#
687 =head3 GetPendingOrders
691 $orders = &GetPendingOrders($booksellerid, $grouped, $owner);
693 Finds pending orders from the bookseller with the given ID. Ignores
694 completed and cancelled orders.
696 C<$booksellerid> contains the bookseller identifier
697 C<$grouped> contains 0 or 1. 0 means returns the list, 1 means return the total
698 C<$owner> contains 0 or 1. 0 means any owner. 1 means only the list of orders entered by the user itself.
700 C<$orders> is a reference-to-array; each element is a
701 reference-to-hash with the following fields:
702 C<$grouped> is a boolean that, if set to 1 will group all order lines of the same basket
703 in a single result line
707 =item C<authorizedby>
713 These give the value of the corresponding field in the aqorders table
714 of the Koha database.
720 Results are ordered from most to least recent.
724 sub GetPendingOrders {
725 my ($supplierid,$grouped,$owner,$basketno) = @_;
726 my $dbh = C4::Context->dbh;
728 SELECT ".($grouped?"count(*),":"")."aqbasket.basketno,
729 surname,firstname,aqorders.*,biblio.*,biblioitems.isbn,
730 aqbasket.closedate, aqbasket.creationdate, aqbasket.basketname
732 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
733 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
734 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
735 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
737 AND (quantity > quantityreceived OR quantityreceived is NULL)
738 AND datecancellationprinted IS NULL
739 AND (to_days(now())-to_days(closedate) < 180 OR closedate IS NULL)
741 ## FIXME Why 180 days ???
742 my @query_params = ( $supplierid );
743 my $userenv = C4::Context->userenv;
744 if ( C4::Context->preference("IndependantBranches") ) {
745 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
746 $strsth .= " and (borrowers.branchcode = ?
747 or borrowers.branchcode = '')";
748 push @query_params, $userenv->{branch};
752 $strsth .= " AND aqbasket.authorisedby=? ";
753 push @query_params, $userenv->{'number'};
756 $strsth .= " AND aqbasket.basketno=? ";
757 push @query_params, $basketno;
759 $strsth .= " group by aqbasket.basketno" if $grouped;
760 $strsth .= " order by aqbasket.basketno";
762 my $sth = $dbh->prepare($strsth);
763 $sth->execute( @query_params );
764 my $results = $sth->fetchall_arrayref({});
769 #------------------------------------------------------------#
775 @orders = &GetOrders($basketnumber, $orderby);
777 Looks up the pending (non-cancelled) orders with the given basket
778 number. If C<$booksellerID> is non-empty, only orders from that seller
782 C<&basket> returns a two-element array. C<@orders> is an array of
783 references-to-hash, whose keys are the fields from the aqorders,
784 biblio, and biblioitems tables in the Koha database.
791 my ( $basketno, $orderby ) = @_;
792 my $dbh = C4::Context->dbh;
794 SELECT biblio.*,biblioitems.*,
799 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
800 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
801 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
803 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
806 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
807 $query .= " ORDER BY $orderby";
808 my $sth = $dbh->prepare($query);
809 $sth->execute($basketno);
810 my $results = $sth->fetchall_arrayref({});
815 #------------------------------------------------------------#
817 =head3 GetOrderNumber
821 $ordernumber = &GetOrderNumber($biblioitemnumber, $biblionumber);
825 Looks up the ordernumber with the given biblionumber and biblioitemnumber.
827 Returns the number of this order.
831 =item C<$ordernumber> is the order number.
837 my ( $biblionumber,$biblioitemnumber ) = @_;
838 my $dbh = C4::Context->dbh;
843 AND biblioitemnumber=?
845 my $sth = $dbh->prepare($query);
846 $sth->execute( $biblionumber, $biblioitemnumber );
848 return $sth->fetchrow;
851 #------------------------------------------------------------#
857 $order = &GetOrder($ordernumber);
859 Looks up an order by order number.
861 Returns a reference-to-hash describing the order. The keys of
862 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
870 my $dbh = C4::Context->dbh;
872 SELECT biblioitems.*, biblio.*, aqorders.*
874 LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber
875 LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber
876 WHERE aqorders.ordernumber=?
879 my $sth= $dbh->prepare($query);
880 $sth->execute($ordnum);
881 my $data = $sth->fetchrow_hashref;
886 #------------------------------------------------------------#
892 &NewOrder(\%hashref);
894 Adds a new order to the database. Any argument that isn't described
895 below is the new value of the field with the same name in the aqorders
896 table of the Koha database.
900 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
903 =item $hashref->{'ordnum'} is a "minimum order number."
905 =item $hashref->{'budgetdate'} is effectively ignored.
906 If it's undef (anything false) or the string 'now', the current day is used.
907 Else, the upcoming July 1st is used.
909 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
911 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
913 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".
922 my $orderinfo = shift;
923 #### ------------------------------
924 my $dbh = C4::Context->dbh;
928 # if these parameters are missing, we can't continue
929 for my $key (qw/basketno quantity biblionumber budget_id/) {
930 die "Mandatory parameter $key missing" unless $orderinfo->{$key};
933 if ( $orderinfo->{'subscription'} eq 'yes' ) {
934 $orderinfo->{'subscription'} = 1;
936 $orderinfo->{'subscription'} = 0;
939 my $query = "INSERT INTO aqorders (";
940 foreach my $orderinfokey (keys %{$orderinfo}) {
941 next if $orderinfokey =~ m/branchcode|entrydate/; # skip branchcode and entrydate, branchcode isnt a vaild col, entrydate we add manually with NOW()
942 $query .= "$orderinfokey,";
943 push(@params, $orderinfo->{$orderinfokey});
946 $query .= "entrydate) VALUES (";
950 $query .= " NOW() )"; #ADDING CURRENT DATE TO 'budgetdate, entrydate, purchaseordernumber'...
952 my $sth = $dbh->prepare($query);
954 $sth->execute(@params);
957 #get ordnum MYSQL dependant, but $dbh->last_insert_id returns null
958 my $ordnum = $dbh->{'mysql_insertid'};
961 return ( $orderinfo->{'basketno'}, $ordnum );
966 #------------------------------------------------------------#
980 #my ($biblioitemnumber,$ordnum, $biblionumber) = @_;
981 my ($itemnumber, $ordernumber) = @_;
982 my $dbh = C4::Context->dbh;
984 INSERT INTO aqorders_items
985 (itemnumber, ordernumber)
988 my $sth = $dbh->prepare($query);
989 $sth->execute( $itemnumber, $ordernumber);
992 #------------------------------------------------------------#
998 &ModOrder(\%hashref);
1002 Modifies an existing order. Updates the order with order number
1003 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All other keys of the hash
1004 update the fields with the same name in the aqorders table of the Koha database.
1013 my $orderinfo = shift;
1015 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1016 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1018 my $dbh = C4::Context->dbh;
1020 # delete($orderinfo->{'branchcode'});
1021 # the hash contains a lot of entries not in aqorders, so get the columns ...
1022 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1024 my $colnames = $sth->{NAME};
1025 my $query = "UPDATE aqorders SET ";
1027 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1028 # ... and skip hash entries that are not in the aqorders table
1029 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1030 next unless grep(/^$orderinfokey$/, @$colnames);
1031 $query .= "$orderinfokey=?, ";
1032 push(@params, $orderinfo->{$orderinfokey});
1035 $query .= "timestamp=NOW() WHERE ordernumber=?";
1036 # push(@params, $specorderinfo{'ordernumber'});
1037 push(@params, $orderinfo->{'ordernumber'} );
1038 $sth = $dbh->prepare($query);
1039 $sth->execute(@params);
1043 #------------------------------------------------------------#
1049 &ModOrderItem(\%hashref);
1053 Modifies the itemnumber in the aqorders_items table. The input hash needs three entities:
1054 - itemnumber: the old itemnumber
1055 - ordernumber: the order this item is attached to
1056 - newitemnumber: the new itemnumber we want to attach the line to
1065 my $orderiteminfo = shift;
1066 if (! $orderiteminfo->{'ordernumber'} || ! $orderiteminfo->{'itemnumber'} || ! $orderiteminfo->{'newitemnumber'}){
1067 die "Ordernumber, itemnumber and newitemnumber is required";
1070 my $dbh = C4::Context->dbh;
1072 my $query = "UPDATE aqorders_items set itemnumber=? where itemnumber=? and ordernumber=?";
1073 my @params = ($orderiteminfo->{'newitemnumber'}, $orderiteminfo->{'itemnumber'}, $orderiteminfo->{'ordernumber'});
1075 warn Data::Dumper::Dumper(@params);
1076 my $sth = $dbh->prepare($query);
1077 $sth->execute(@params);
1081 #------------------------------------------------------------#
1084 =head3 ModOrderBibliotemNumber
1088 &ModOrderBiblioitemNumber($biblioitemnumber,$ordnum, $biblionumber);
1090 Modifies the biblioitemnumber for an existing order.
1091 Updates the order with order number C<$ordernum> and biblionumber C<$biblionumber>.
1097 #FIXME: is this used at all?
1098 sub ModOrderBiblioitemNumber {
1099 my ($biblioitemnumber,$ordnum, $biblionumber) = @_;
1100 my $dbh = C4::Context->dbh;
1103 SET biblioitemnumber = ?
1104 WHERE ordernumber = ?
1105 AND biblionumber = ?";
1106 my $sth = $dbh->prepare($query);
1107 $sth->execute( $biblioitemnumber, $ordnum, $biblionumber );
1110 #------------------------------------------------------------#
1112 =head3 ModReceiveOrder
1116 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
1117 $unitprice, $booksellerinvoicenumber, $biblioitemnumber,
1118 $freight, $bookfund, $rrp);
1120 Updates an order, to reflect the fact that it was received, at least
1121 in part. All arguments not mentioned below update the fields with the
1122 same name in the aqorders table of the Koha database.
1124 If a partial order is received, splits the order into two. The received
1125 portion must have a booksellerinvoicenumber.
1127 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1135 sub ModReceiveOrder {
1137 $biblionumber, $ordnum, $quantrec, $user, $cost,
1138 $invoiceno, $freight, $rrp, $budget_id, $datereceived
1141 my $dbh = C4::Context->dbh;
1142 # warn "DATE BEFORE : $daterecieved";
1143 # $daterecieved=POSIX::strftime("%Y-%m-%d",CORE::localtime) unless $daterecieved;
1144 # warn "DATE REC : $daterecieved";
1145 $datereceived = C4::Dates->output('iso') unless $datereceived;
1146 my $suggestionid = GetSuggestionFromBiblionumber( $dbh, $biblionumber );
1147 if ($suggestionid) {
1148 ModStatus( $suggestionid, 'AVAILABLE', '', $biblionumber );
1151 my $sth=$dbh->prepare("
1152 SELECT * FROM aqorders
1153 WHERE biblionumber=? AND aqorders.ordernumber=?");
1155 $sth->execute($biblionumber,$ordnum);
1156 my $order = $sth->fetchrow_hashref();
1159 if ( $order->{quantity} > $quantrec ) {
1160 $sth=$dbh->prepare("
1162 SET quantityreceived=?
1164 , booksellerinvoicenumber=?
1168 , quantityreceived=?
1169 WHERE biblionumber=? AND ordernumber=?");
1171 $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$quantrec,$biblionumber,$ordnum);
1174 # create a new order for the remaining items, and set its bookfund.
1175 foreach my $orderkey ( "linenumber", "allocation" ) {
1176 delete($order->{'$orderkey'});
1178 my $newOrder = NewOrder($order);
1180 $sth=$dbh->prepare("update aqorders
1181 set quantityreceived=?,datereceived=?,booksellerinvoicenumber=?,
1182 unitprice=?,freight=?,rrp=?
1183 where biblionumber=? and ordernumber=?");
1184 $sth->execute($quantrec,$datereceived,$invoiceno,$cost,$freight,$rrp,$biblionumber,$ordnum);
1187 return $datereceived;
1189 #------------------------------------------------------------#
1193 @results = &SearchOrder($search, $biblionumber, $complete);
1195 Searches for orders.
1197 C<$search> may take one of several forms: if it is an ISBN,
1198 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
1199 order number, C<&ordersearch> returns orders with that order number
1200 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
1201 to be a space-separated list of search terms; in this case, all of the
1202 terms must appear in the title (matching the beginning of title
1205 If C<$complete> is C<yes>, the results will include only completed
1206 orders. In any case, C<&ordersearch> ignores cancelled orders.
1208 C<&ordersearch> returns an array.
1209 C<@results> is an array of references-to-hash with the following keys:
1215 =item C<seriestitle>
1226 #### -------- SearchOrder-------------------------------
1227 my ($ordernumber, $search, $supplierid, $basket) = @_;
1229 my $dbh = C4::Context->dbh;
1234 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1235 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1236 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1237 WHERE (datecancellationprinted is NULL)";
1240 $query .= " AND (aqorders.ordernumber=?)";
1241 push @args, $ordernumber;
1244 $query .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)";
1245 push @args, ("%$search%","%$search%","%$search%");
1248 $query .= "AND aqbasket.booksellerid = ?";
1249 push @args, $supplierid;
1252 $query .= "AND aqorders.basketno = ?";
1253 push @args, $basket;
1256 my $sth = $dbh->prepare($query);
1257 $sth->execute(@args);
1258 my $results = $sth->fetchall_arrayref({});
1263 #------------------------------------------------------------#
1269 &DelOrder($biblionumber, $ordernumber);
1271 Cancel the order with the given order and biblio numbers. It does not
1272 delete any entries in the aqorders table, it merely marks them as
1280 my ( $bibnum, $ordnum ) = @_;
1281 my $dbh = C4::Context->dbh;
1284 SET datecancellationprinted=now()
1285 WHERE biblionumber=? AND ordernumber=?
1287 my $sth = $dbh->prepare($query);
1288 $sth->execute( $bibnum, $ordnum );
1292 =head2 FUNCTIONS ABOUT PARCELS
1296 #------------------------------------------------------------#
1302 @results = &GetParcel($booksellerid, $code, $date);
1304 Looks up all of the received items from the supplier with the given
1305 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1307 C<@results> is an array of references-to-hash. The keys of each element are fields from
1308 the aqorders, biblio, and biblioitems tables of the Koha database.
1310 C<@results> is sorted alphabetically by book title.
1317 #gets all orders from a certain supplier, orders them alphabetically
1318 my ( $supplierid, $code, $datereceived ) = @_;
1319 my $dbh = C4::Context->dbh;
1322 if $code; # add % if we search on a given code (otherwise, let him empty)
1324 SELECT authorisedby,
1329 aqorders.biblionumber,
1330 aqorders.ordernumber,
1332 aqorders.quantityreceived,
1339 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1340 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1341 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1343 aqbasket.booksellerid = ?
1344 AND aqorders.booksellerinvoicenumber LIKE ?
1345 AND aqorders.datereceived = ? ";
1347 my @query_params = ( $supplierid, $code, $datereceived );
1348 if ( C4::Context->preference("IndependantBranches") ) {
1349 my $userenv = C4::Context->userenv;
1350 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1351 $strsth .= " and (borrowers.branchcode = ?
1352 or borrowers.branchcode = '')";
1353 push @query_params, $userenv->{branch};
1356 $strsth .= " ORDER BY aqbasket.basketno";
1357 # ## parcelinformation : $strsth
1358 my $sth = $dbh->prepare($strsth);
1359 $sth->execute( @query_params );
1360 while ( my $data = $sth->fetchrow_hashref ) {
1361 push( @results, $data );
1363 # ## countparcelbiblio: scalar(@results)
1369 #------------------------------------------------------------#
1375 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1376 get a lists of parcels.
1385 is the bookseller this function has to get parcels.
1388 To know on what criteria the results list has to be ordered.
1391 is the booksellerinvoicenumber.
1393 =item $datefrom & $dateto
1394 to know on what date this function has to filter its search.
1397 a pointer on a hash list containing parcel informations as such :
1401 =item Last operation
1403 =item Number of biblio
1405 =item Number of items
1412 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1413 my $dbh = C4::Context->dbh;
1414 my @query_params = ();
1416 SELECT aqorders.booksellerinvoicenumber,
1417 datereceived,purchaseordernumber,
1418 count(DISTINCT biblionumber) AS biblio,
1419 sum(quantity) AS itemsexpected,
1420 sum(quantityreceived) AS itemsreceived
1421 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1422 WHERE aqbasket.booksellerid = $bookseller and datereceived IS NOT NULL
1425 if ( defined $code ) {
1426 $strsth .= ' and aqorders.booksellerinvoicenumber like ? ';
1427 # add a % to the end of the code to allow stemming.
1428 push @query_params, "$code%";
1431 if ( defined $datefrom ) {
1432 $strsth .= ' and datereceived >= ? ';
1433 push @query_params, $datefrom;
1436 if ( defined $dateto ) {
1437 $strsth .= 'and datereceived <= ? ';
1438 push @query_params, $dateto;
1441 $strsth .= "group by aqorders.booksellerinvoicenumber,datereceived ";
1443 # can't use a placeholder to place this column name.
1444 # but, we could probably be checking to make sure it is a column that will be fetched.
1445 $strsth .= "order by $order " if ($order);
1447 my $sth = $dbh->prepare($strsth);
1449 $sth->execute( @query_params );
1450 my $results = $sth->fetchall_arrayref({});
1455 #------------------------------------------------------------#
1457 =head3 GetLateOrders
1461 @results = &GetLateOrders;
1463 Searches for bookseller with late orders.
1466 the table of supplier with late issues. This table is full of hashref.
1474 my $supplierid = shift;
1477 my $dbh = C4::Context->dbh;
1479 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
1480 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
1482 my @query_params = ($delay); # delay is the first argument regardless
1484 SELECT aqbasket.basketno,
1485 aqorders.ordernumber,
1486 DATE(aqbasket.closedate) AS orderdate,
1487 aqorders.rrp AS unitpricesupplier,
1488 aqorders.ecost AS unitpricelib,
1489 aqbudgets.budget_name AS budget,
1490 borrowers.branchcode AS branch,
1491 aqbooksellers.name AS supplier,
1493 biblioitems.publishercode AS publisher,
1494 biblioitems.publicationyear,
1498 (aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber)
1499 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber)
1500 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id),
1501 (aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber)
1502 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1503 WHERE aqorders.basketno = aqbasket.basketno
1504 AND ( (datereceived = '' OR datereceived IS NULL)
1505 OR (aqorders.quantityreceived < aqorders.quantity)
1509 if ($dbdriver eq "mysql") {
1511 aqorders.quantity - IFNULL(aqorders.quantityreceived,0) AS quantity,
1512 (aqorders.quantity - IFNULL(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1513 DATEDIFF(CURDATE( ),closedate) AS latesince
1515 $from .= " AND (closedate <= DATE_SUB(CURDATE( ),INTERVAL ? DAY)) ";
1517 HAVING quantity <> 0
1518 AND unitpricesupplier <> 0
1519 AND unitpricelib <> 0
1522 # FIXME: account for IFNULL as above
1524 aqorders.quantity AS quantity,
1525 aqorders.quantity * aqorders.rrp AS subtotal,
1526 (CURDATE - closedate) AS latesince
1528 $from .= " AND (closedate <= (CURDATE -(INTERVAL ? DAY)) ";
1530 if (defined $supplierid) {
1531 $from .= ' AND aqbasket.booksellerid = ? ';
1532 push @query_params, $supplierid;
1534 if (defined $branch) {
1535 $from .= ' AND borrowers.branchcode LIKE ? ';
1536 push @query_params, $branch;
1538 if (C4::Context->preference("IndependantBranches")
1539 && C4::Context->userenv
1540 && C4::Context->userenv->{flags} != 1 ) {
1541 $from .= ' AND borrowers.branchcode LIKE ? ';
1542 push @query_params, C4::Context->userenv->{branch};
1544 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
1545 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
1546 my $sth = $dbh->prepare($query);
1547 $sth->execute(@query_params);
1549 while (my $data = $sth->fetchrow_hashref) {
1550 $data->{orderdate} = format_date($data->{orderdate});
1551 push @results, $data;
1556 #------------------------------------------------------------#
1562 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( $title, $author, $name, $from_placed_on, $to_placed_on );
1564 Retreives some acquisition history information
1567 $order_loop is a list of hashrefs that each look like this:
1569 'author' => 'Twain, Mark',
1571 'biblionumber' => '215',
1573 'creationdate' => 'MM/DD/YYYY',
1574 'datereceived' => undef,
1577 'invoicenumber' => undef,
1579 'ordernumber' => '1',
1581 'quantityreceived' => undef,
1582 'title' => 'The Adventures of Huckleberry Finn'
1584 $total_qty is the sum of all of the quantities in $order_loop
1585 $total_price is the cost of each in $order_loop times the quantity
1586 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
1593 my ( $title, $author, $name, $from_placed_on, $to_placed_on ) = @_;
1596 my $total_qtyreceived = 0;
1597 my $total_price = 0;
1599 # don't run the query if there are no parameters (list would be too long for sure !)
1600 if ( $title || $author || $name || $from_placed_on || $to_placed_on ) {
1601 my $dbh = C4::Context->dbh;
1607 name,aqbasket.creationdate,
1608 aqorders.datereceived,
1610 aqorders.quantityreceived,
1612 aqorders.ordernumber,
1613 aqorders.booksellerinvoicenumber as invoicenumber,
1614 aqbooksellers.id as id,
1615 aqorders.biblionumber
1617 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
1618 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
1619 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber";
1621 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
1622 if ( C4::Context->preference("IndependantBranches") );
1624 $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
1626 my @query_params = ();
1628 if ( defined $title ) {
1629 $query .= " AND biblio.title LIKE ? ";
1630 push @query_params, "%$title%";
1633 if ( defined $author ) {
1634 $query .= " AND biblio.author LIKE ? ";
1635 push @query_params, "%$author%";
1638 if ( defined $name ) {
1639 $query .= " AND name LIKE ? ";
1640 push @query_params, "%$name%";
1643 if ( defined $from_placed_on ) {
1644 $query .= " AND creationdate >= ? ";
1645 push @query_params, $from_placed_on;
1648 if ( defined $to_placed_on ) {
1649 $query .= " AND creationdate <= ? ";
1650 push @query_params, $to_placed_on;
1653 if ( C4::Context->preference("IndependantBranches") ) {
1654 my $userenv = C4::Context->userenv;
1655 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1656 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
1657 push @query_params, $userenv->{branch};
1660 $query .= " ORDER BY booksellerid";
1661 my $sth = $dbh->prepare($query);
1662 $sth->execute( @query_params );
1664 while ( my $line = $sth->fetchrow_hashref ) {
1665 $line->{count} = $cnt++;
1666 $line->{toggle} = 1 if $cnt % 2;
1667 push @order_loop, $line;
1668 $line->{creationdate} = format_date( $line->{creationdate} );
1669 $line->{datereceived} = format_date( $line->{datereceived} );
1670 $total_qty += $line->{'quantity'};
1671 $total_qtyreceived += $line->{'quantityreceived'};
1672 $total_price += $line->{'quantity'} * $line->{'ecost'};
1675 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
1678 =head2 GetRecentAcqui
1680 $results = GetRecentAcqui($days);
1682 C<$results> is a ref to a table which containts hashref
1686 sub GetRecentAcqui {
1688 my $dbh = C4::Context->dbh;
1692 ORDER BY timestamp DESC
1695 my $sth = $dbh->prepare($query);
1697 my $results = $sth->fetchall_arrayref({});
1705 $contractlist = &GetContracts($booksellerid, $activeonly);
1707 Looks up the contracts that belong to a bookseller
1709 Returns a list of contracts
1711 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
1713 =item C<$activeonly> if exists get only contracts that are still active.
1719 my ( $booksellerid, $activeonly ) = @_;
1720 my $dbh = C4::Context->dbh;
1722 if (! $activeonly) {
1726 WHERE booksellerid=?
1731 WHERE booksellerid=?
1732 AND contractenddate >= CURDATE( )";
1734 my $sth = $dbh->prepare($query);
1735 $sth->execute( $booksellerid );
1737 while (my $data = $sth->fetchrow_hashref ) {
1738 push(@results, $data);
1744 #------------------------------------------------------------#
1750 $contract = &GetContract($contractID);
1752 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
1760 my ( $contractno ) = @_;
1761 my $dbh = C4::Context->dbh;
1765 WHERE contractnumber=?
1768 my $sth = $dbh->prepare($query);
1769 $sth->execute( $contractno );
1770 my $result = $sth->fetchrow_hashref;
1779 Koha Developement team <info@koha.org>