1 package C4::Acquisition;
3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along
17 # with Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
26 use C4::Dates qw(format_date format_date_in_iso);
31 use C4::SQLHelper qw(InsertInTable);
32 use C4::Bookseller qw(GetBookSellerFromId);
33 use C4::Templates qw(gettemplate);
38 use vars qw($VERSION @ISA @EXPORT);
41 # set the version for version checking
42 $VERSION = 3.07.00.049;
46 &GetBasket &NewBasket &CloseBasket &DelBasket &ModBasket
47 &GetBasketAsCSV &GetBasketGroupAsCSV
48 &GetBasketsByBookseller &GetBasketsByBasketgroup
49 &GetBasketsInfosByBookseller
53 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
54 &GetBasketgroups &ReOpenBasketgroup
56 &NewOrder &DelOrder &ModOrder &GetPendingOrders &GetOrder &GetOrders &GetOrdersByBiblionumber
57 &GetLateOrders &GetOrderFromItemnumber
58 &SearchOrder &GetHistory &GetRecentAcqui
59 &ModReceiveOrder &CancelReceipt
61 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
62 &NewOrderItem &ModOrderItem &ModItemOrder
64 &GetParcels &GetParcel
65 &GetContracts &GetContract
75 &GetItemnumbersFromOrder
85 sub GetOrderFromItemnumber {
86 my ($itemnumber) = @_;
87 my $dbh = C4::Context->dbh;
90 SELECT * from aqorders LEFT JOIN aqorders_items
91 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
92 WHERE itemnumber = ? |;
94 my $sth = $dbh->prepare($query);
98 $sth->execute($itemnumber);
100 my $order = $sth->fetchrow_hashref;
105 # Returns the itemnumber(s) associated with the ordernumber given in parameter
106 sub GetItemnumbersFromOrder {
107 my ($ordernumber) = @_;
108 my $dbh = C4::Context->dbh;
109 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
110 my $sth = $dbh->prepare($query);
111 $sth->execute($ordernumber);
114 while (my $order = $sth->fetchrow_hashref) {
115 push @tab, $order->{'itemnumber'};
129 C4::Acquisition - Koha functions for dealing with orders and acquisitions
137 The functions in this module deal with acquisitions, managing book
138 orders, basket and parcels.
142 =head2 FUNCTIONS ABOUT BASKETS
146 $aqbasket = &GetBasket($basketnumber);
148 get all basket informations in aqbasket for a given basket
150 B<returns:> informations for a given basket returned as a hashref.
156 my $dbh = C4::Context->dbh;
159 concat( b.firstname,' ',b.surname) AS authorisedbyname,
160 b.branchcode AS branch
162 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
165 my $sth=$dbh->prepare($query);
166 $sth->execute($basketno);
167 my $basket = $sth->fetchrow_hashref;
171 #------------------------------------------------------------#
175 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
176 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace );
178 Create a new basket in aqbasket table
182 =item C<$booksellerid> is a foreign key in the aqbasket table
184 =item C<$authorizedby> is the username of who created the basket
188 The other parameters are optional, see ModBasketHeader for more info on them.
193 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
194 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
195 $billingplace ) = @_;
196 my $dbh = C4::Context->dbh;
198 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
199 . 'VALUES (now(),?,?)';
200 $dbh->do( $query, {}, $booksellerid, $authorisedby );
202 my $basket = $dbh->{mysql_insertid};
203 $basketname ||= q{}; # default to empty strings
205 $basketbooksellernote ||= q{};
206 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
207 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace );
211 #------------------------------------------------------------#
215 &CloseBasket($basketno);
217 close a basket (becomes unmodifiable,except for recieves)
223 my $dbh = C4::Context->dbh;
229 my $sth = $dbh->prepare($query);
230 $sth->execute($basketno);
233 #------------------------------------------------------------#
235 =head3 GetBasketAsCSV
237 &GetBasketAsCSV($basketno);
239 Export a basket as CSV
241 $cgi parameter is needed for column name translation
246 my ($basketno, $cgi) = @_;
247 my $basket = GetBasket($basketno);
248 my @orders = GetOrders($basketno);
249 my $contract = GetContract($basket->{'contractnumber'});
251 my $template = C4::Templates::gettemplate("acqui/csv/basket.tmpl", "intranet", $cgi);
254 foreach my $order (@orders) {
255 my $bd = GetBiblioData( $order->{'biblionumber'} );
257 contractname => $contract->{'contractname'},
258 ordernumber => $order->{'ordernumber'},
259 entrydate => $order->{'entrydate'},
260 isbn => $order->{'isbn'},
261 author => $bd->{'author'},
262 title => $bd->{'title'},
263 publicationyear => $bd->{'publicationyear'},
264 publishercode => $bd->{'publishercode'},
265 collectiontitle => $bd->{'collectiontitle'},
266 notes => $order->{'notes'},
267 quantity => $order->{'quantity'},
268 rrp => $order->{'rrp'},
269 deliveryplace => C4::Branch::GetBranchName( $basket->{'deliveryplace'} ),
270 billingplace => C4::Branch::GetBranchName( $basket->{'billingplace'} ),
273 contractname author title publishercode collectiontitle notes
274 deliveryplace billingplace
276 # Double the quotes to not be interpreted as a field end
277 $row->{$_} =~ s/"/""/g if $row->{$_};
283 if(defined $a->{publishercode} and defined $b->{publishercode}) {
284 $a->{publishercode} cmp $b->{publishercode};
288 $template->param(rows => \@rows);
290 return $template->output;
294 =head3 GetBasketGroupAsCSV
298 &GetBasketGroupAsCSV($basketgroupid);
300 Export a basket group as CSV
302 $cgi parameter is needed for column name translation
308 sub GetBasketGroupAsCSV {
309 my ($basketgroupid, $cgi) = @_;
310 my $baskets = GetBasketsByBasketgroup($basketgroupid);
312 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tmpl', 'intranet', $cgi);
315 for my $basket (@$baskets) {
316 my @orders = GetOrders( $$basket{basketno} );
317 my $contract = GetContract( $$basket{contractnumber} );
318 my $bookseller = GetBookSellerFromId( $$basket{booksellerid} );
319 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
321 foreach my $order (@orders) {
322 my $bd = GetBiblioData( $order->{'biblionumber'} );
324 clientnumber => $bookseller->{accountnumber},
325 basketname => $basket->{basketname},
326 ordernumber => $order->{ordernumber},
327 author => $bd->{author},
328 title => $bd->{title},
329 publishercode => $bd->{publishercode},
330 publicationyear => $bd->{publicationyear},
331 collectiontitle => $bd->{collectiontitle},
332 isbn => $order->{isbn},
333 quantity => $order->{quantity},
334 rrp => $order->{rrp},
335 discount => $bookseller->{discount},
336 ecost => $order->{ecost},
337 notes => $order->{notes},
338 entrydate => $order->{entrydate},
339 booksellername => $bookseller->{name},
340 bookselleraddress => $bookseller->{address1},
341 booksellerpostal => $bookseller->{postal},
342 contractnumber => $contract->{contractnumber},
343 contractname => $contract->{contractname},
344 basketgroupdeliveryplace => C4::Branch::GetBranchName( $basketgroup->{deliveryplace} ),
345 basketgroupbillingplace => C4::Branch::GetBranchName( $basketgroup->{billingplace} ),
346 basketdeliveryplace => C4::Branch::GetBranchName( $basket->{deliveryplace} ),
347 basketbillingplace => C4::Branch::GetBranchName( $basket->{billingplace} ),
350 basketname author title publishercode collectiontitle notes
351 booksellername bookselleraddress booksellerpostal contractname
352 basketgroupdeliveryplace basketgroupbillingplace
353 basketdeliveryplace basketbillingplace
355 # Double the quotes to not be interpreted as a field end
356 $row->{$_} =~ s/"/""/g if $row->{$_};
361 $template->param(rows => \@rows);
363 return $template->output;
367 =head3 CloseBasketgroup
369 &CloseBasketgroup($basketgroupno);
375 sub CloseBasketgroup {
376 my ($basketgroupno) = @_;
377 my $dbh = C4::Context->dbh;
378 my $sth = $dbh->prepare("
379 UPDATE aqbasketgroups
383 $sth->execute($basketgroupno);
386 #------------------------------------------------------------#
388 =head3 ReOpenBaskergroup($basketgroupno)
390 &ReOpenBaskergroup($basketgroupno);
396 sub ReOpenBasketgroup {
397 my ($basketgroupno) = @_;
398 my $dbh = C4::Context->dbh;
399 my $sth = $dbh->prepare("
400 UPDATE aqbasketgroups
404 $sth->execute($basketgroupno);
407 #------------------------------------------------------------#
412 &DelBasket($basketno);
414 Deletes the basket that has basketno field $basketno in the aqbasket table.
418 =item C<$basketno> is the primary key of the basket in the aqbasket table.
425 my ( $basketno ) = @_;
426 my $query = "DELETE FROM aqbasket WHERE basketno=?";
427 my $dbh = C4::Context->dbh;
428 my $sth = $dbh->prepare($query);
429 $sth->execute($basketno);
433 #------------------------------------------------------------#
437 &ModBasket($basketinfo);
439 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
443 =item C<$basketno> is the primary key of the basket in the aqbasket table.
450 my $basketinfo = shift;
451 my $query = "UPDATE aqbasket SET ";
453 foreach my $key (keys %$basketinfo){
454 if ($key ne 'basketno'){
455 $query .= "$key=?, ";
456 push(@params, $basketinfo->{$key} || undef );
459 # get rid of the "," at the end of $query
460 if (substr($query, length($query)-2) eq ', '){
465 $query .= "WHERE basketno=?";
466 push(@params, $basketinfo->{'basketno'});
467 my $dbh = C4::Context->dbh;
468 my $sth = $dbh->prepare($query);
469 $sth->execute(@params);
473 #------------------------------------------------------------#
475 =head3 ModBasketHeader
477 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
479 Modifies a basket's header.
483 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
485 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
487 =item C<$note> is the "note" field in the "aqbasket" table;
489 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
491 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
493 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
495 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
497 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
503 sub ModBasketHeader {
504 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace) = @_;
507 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?
511 my $dbh = C4::Context->dbh;
512 my $sth = $dbh->prepare($query);
513 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $basketno);
515 if ( $contractnumber ) {
516 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
517 my $sth2 = $dbh->prepare($query2);
518 $sth2->execute($contractnumber,$basketno);
524 #------------------------------------------------------------#
526 =head3 GetBasketsByBookseller
528 @results = &GetBasketsByBookseller($booksellerid, $extra);
530 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
534 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
536 =item C<$extra> is the extra sql parameters, can be
538 $extra->{groupby}: group baskets by column
539 ex. $extra->{groupby} = aqbasket.basketgroupid
540 $extra->{orderby}: order baskets by column
541 $extra->{limit}: limit number of results (can be helpful for pagination)
547 sub GetBasketsByBookseller {
548 my ($booksellerid, $extra) = @_;
549 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
551 if ($extra->{groupby}) {
552 $query .= " GROUP by $extra->{groupby}";
554 if ($extra->{orderby}){
555 $query .= " ORDER by $extra->{orderby}";
557 if ($extra->{limit}){
558 $query .= " LIMIT $extra->{limit}";
561 my $dbh = C4::Context->dbh;
562 my $sth = $dbh->prepare($query);
563 $sth->execute($booksellerid);
564 my $results = $sth->fetchall_arrayref({});
569 =head3 GetBasketsInfosByBookseller
571 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
573 The optional second parameter allbaskets is a boolean allowing you to
574 select all baskets from the supplier; by default only active baskets (open or
575 closed but still something to receive) are returned.
577 Returns in a arrayref of hashref all about booksellers baskets, plus:
578 total_biblios: Number of distinct biblios in basket
579 total_items: Number of items in basket
580 expected_items: Number of non-received items in basket
584 sub GetBasketsInfosByBookseller {
585 my ($supplierid, $allbaskets) = @_;
587 return unless $supplierid;
589 my $dbh = C4::Context->dbh;
592 SUM(aqorders.quantity) AS total_items,
593 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
595 IF(aqorders.datereceived IS NULL
596 AND aqorders.datecancellationprinted IS NULL
601 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
602 WHERE booksellerid = ?};
604 $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
606 $query.=" GROUP BY aqbasket.basketno";
608 my $sth = $dbh->prepare($query);
609 $sth->execute($supplierid);
610 return $sth->fetchall_arrayref({});
614 #------------------------------------------------------------#
616 =head3 GetBasketsByBasketgroup
618 $baskets = &GetBasketsByBasketgroup($basketgroupid);
620 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
624 sub GetBasketsByBasketgroup {
625 my $basketgroupid = shift;
627 SELECT *, aqbasket.booksellerid as booksellerid
629 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
631 my $dbh = C4::Context->dbh;
632 my $sth = $dbh->prepare($query);
633 $sth->execute($basketgroupid);
634 my $results = $sth->fetchall_arrayref({});
639 #------------------------------------------------------------#
641 =head3 NewBasketgroup
643 $basketgroupid = NewBasketgroup(\%hashref);
645 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
647 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
649 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
651 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
653 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
655 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
657 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
659 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
661 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
666 my $basketgroupinfo = shift;
667 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
668 my $query = "INSERT INTO aqbasketgroups (";
670 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
671 if ( defined $basketgroupinfo->{$field} ) {
672 $query .= "$field, ";
673 push(@params, $basketgroupinfo->{$field});
676 $query .= "booksellerid) VALUES (";
681 push(@params, $basketgroupinfo->{'booksellerid'});
682 my $dbh = C4::Context->dbh;
683 my $sth = $dbh->prepare($query);
684 $sth->execute(@params);
685 my $basketgroupid = $dbh->{'mysql_insertid'};
686 if( $basketgroupinfo->{'basketlist'} ) {
687 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
688 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
689 my $sth2 = $dbh->prepare($query2);
690 $sth2->execute($basketgroupid, $basketno);
693 return $basketgroupid;
696 #------------------------------------------------------------#
698 =head3 ModBasketgroup
700 ModBasketgroup(\%hashref);
702 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
704 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
706 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
708 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
710 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
712 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
714 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
716 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
718 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
723 my $basketgroupinfo = shift;
724 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
725 my $dbh = C4::Context->dbh;
726 my $query = "UPDATE aqbasketgroups SET ";
728 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
729 if ( defined $basketgroupinfo->{$field} ) {
730 $query .= "$field=?, ";
731 push(@params, $basketgroupinfo->{$field});
736 $query .= " WHERE id=?";
737 push(@params, $basketgroupinfo->{'id'});
738 my $sth = $dbh->prepare($query);
739 $sth->execute(@params);
741 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
742 $sth->execute($basketgroupinfo->{'id'});
744 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
745 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
746 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
747 $sth->execute($basketgroupinfo->{'id'}, $basketno);
754 #------------------------------------------------------------#
756 =head3 DelBasketgroup
758 DelBasketgroup($basketgroupid);
760 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
764 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
771 my $basketgroupid = shift;
772 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
773 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
774 my $dbh = C4::Context->dbh;
775 my $sth = $dbh->prepare($query);
776 $sth->execute($basketgroupid);
780 #------------------------------------------------------------#
783 =head2 FUNCTIONS ABOUT ORDERS
785 =head3 GetBasketgroup
787 $basketgroup = &GetBasketgroup($basketgroupid);
789 Returns a reference to the hash containing all infermation about the basketgroup.
794 my $basketgroupid = shift;
795 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
796 my $query = "SELECT * FROM aqbasketgroups WHERE id=?";
797 my $dbh = C4::Context->dbh;
798 my $sth = $dbh->prepare($query);
799 $sth->execute($basketgroupid);
800 my $result = $sth->fetchrow_hashref;
805 #------------------------------------------------------------#
807 =head3 GetBasketgroups
809 $basketgroups = &GetBasketgroups($booksellerid);
811 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
815 sub GetBasketgroups {
816 my $booksellerid = shift;
817 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
818 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
819 my $dbh = C4::Context->dbh;
820 my $sth = $dbh->prepare($query);
821 $sth->execute($booksellerid);
822 return $sth->fetchall_arrayref({});
825 #------------------------------------------------------------#
827 =head2 FUNCTIONS ABOUT ORDERS
831 #------------------------------------------------------------#
833 =head3 GetPendingOrders
835 $orders = &GetPendingOrders($supplierid,$grouped,$owner,$basketno,$ordernumber,$search,$ean);
837 Finds pending orders from the bookseller with the given ID. Ignores
838 completed and cancelled orders.
840 C<$booksellerid> contains the bookseller identifier
841 C<$owner> contains 0 or 1. 0 means any owner. 1 means only the list of orders entered by the user itself.
842 C<$grouped> is a boolean that, if set to 1 will group all order lines of the same basket
843 in a single result line
844 C<$orders> is a reference-to-array; each element is a reference-to-hash.
846 Used also by the filter in parcel.pl
853 These give the value of the corresponding field in the aqorders table
854 of the Koha database.
856 Results are ordered from most to least recent.
860 sub GetPendingOrders {
861 my ($supplierid,$grouped,$owner,$basketno,$ordernumber,$search,$ean) = @_;
862 my $dbh = C4::Context->dbh;
864 SELECT ".($grouped?"count(*),":"")."aqbasket.basketno,
865 surname,firstname,biblio.*,biblioitems.isbn,
866 aqbasket.closedate, aqbasket.creationdate, aqbasket.basketname,
869 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
870 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
871 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
872 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
873 WHERE (quantity > quantityreceived OR quantityreceived is NULL)
874 AND datecancellationprinted IS NULL";
876 my $userenv = C4::Context->userenv;
877 if ( C4::Context->preference("IndependentBranches") ) {
878 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
879 $strsth .= " AND (borrowers.branchcode = ?
880 or borrowers.branchcode = '')";
881 push @query_params, $userenv->{branch};
885 $strsth .= " AND aqbasket.booksellerid = ?";
886 push @query_params, $supplierid;
889 $strsth .= " AND (aqorders.ordernumber=?)";
890 push @query_params, $ordernumber;
893 $strsth .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)";
894 push @query_params, ("%$search%","%$search%","%$search%");
897 $strsth .= " AND biblioitems.ean = ?";
898 push @query_params, $ean;
901 $strsth .= " AND aqbasket.basketno=? ";
902 push @query_params, $basketno;
905 $strsth .= " AND aqbasket.authorisedby=? ";
906 push @query_params, $userenv->{'number'};
908 $strsth .= " group by aqbasket.basketno" if $grouped;
909 $strsth .= " order by aqbasket.basketno";
910 my $sth = $dbh->prepare($strsth);
911 $sth->execute( @query_params );
912 my $results = $sth->fetchall_arrayref({});
917 #------------------------------------------------------------#
921 @orders = &GetOrders($basketnumber, $orderby);
923 Looks up the pending (non-cancelled) orders with the given basket
924 number. If C<$booksellerID> is non-empty, only orders from that seller
928 C<&basket> returns a two-element array. C<@orders> is an array of
929 references-to-hash, whose keys are the fields from the aqorders,
930 biblio, and biblioitems tables in the Koha database.
935 my ( $basketno, $orderby ) = @_;
936 my $dbh = C4::Context->dbh;
938 SELECT biblio.*,biblioitems.*,
943 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
944 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
945 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
947 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
950 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
951 $query .= " ORDER BY $orderby";
952 my $sth = $dbh->prepare($query);
953 $sth->execute($basketno);
954 my $results = $sth->fetchall_arrayref({});
959 #------------------------------------------------------------#
960 =head3 GetOrdersByBiblionumber
962 @orders = &GetOrdersByBiblionumber($biblionumber);
964 Looks up the orders with linked to a specific $biblionumber, including
965 cancelled orders and received orders.
968 C<@orders> is an array of references-to-hash, whose keys are the
969 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
973 sub GetOrdersByBiblionumber {
974 my $biblionumber = shift;
975 return unless $biblionumber;
976 my $dbh = C4::Context->dbh;
978 SELECT biblio.*,biblioitems.*,
982 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
983 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
984 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
985 WHERE aqorders.biblionumber=?
987 my $sth = $dbh->prepare($query);
988 $sth->execute($biblionumber);
989 my $results = $sth->fetchall_arrayref({});
994 #------------------------------------------------------------#
998 $order = &GetOrder($ordernumber);
1000 Looks up an order by order number.
1002 Returns a reference-to-hash describing the order. The keys of
1003 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1008 my ($ordernumber) = @_;
1009 my $dbh = C4::Context->dbh;
1011 SELECT biblioitems.*, biblio.*, aqorders.*
1013 LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber
1014 LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber
1015 WHERE aqorders.ordernumber=?
1018 my $sth= $dbh->prepare($query);
1019 $sth->execute($ordernumber);
1020 my $data = $sth->fetchrow_hashref;
1025 =head3 GetLastOrderNotReceivedFromSubscriptionid
1027 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1029 Returns a reference-to-hash describing the last order not received for a subscription.
1033 sub GetLastOrderNotReceivedFromSubscriptionid {
1034 my ( $subscriptionid ) = @_;
1035 my $dbh = C4::Context->dbh;
1037 SELECT * FROM aqorders
1038 LEFT JOIN subscription
1039 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1040 WHERE aqorders.subscriptionid = ?
1041 AND aqorders.datereceived IS NULL
1044 my $sth = $dbh->prepare( $query );
1045 $sth->execute( $subscriptionid );
1046 my $order = $sth->fetchrow_hashref;
1050 =head3 GetLastOrderReceivedFromSubscriptionid
1052 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1054 Returns a reference-to-hash describing the last order received for a subscription.
1058 sub GetLastOrderReceivedFromSubscriptionid {
1059 my ( $subscriptionid ) = @_;
1060 my $dbh = C4::Context->dbh;
1062 SELECT * FROM aqorders
1063 LEFT JOIN subscription
1064 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1065 WHERE aqorders.subscriptionid = ?
1066 AND aqorders.datereceived =
1068 SELECT MAX( aqorders.datereceived )
1070 LEFT JOIN subscription
1071 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1072 WHERE aqorders.subscriptionid = ?
1073 AND aqorders.datereceived IS NOT NULL
1075 ORDER BY ordernumber DESC
1078 my $sth = $dbh->prepare( $query );
1079 $sth->execute( $subscriptionid, $subscriptionid );
1080 my $order = $sth->fetchrow_hashref;
1086 #------------------------------------------------------------#
1090 &NewOrder(\%hashref);
1092 Adds a new order to the database. Any argument that isn't described
1093 below is the new value of the field with the same name in the aqorders
1094 table of the Koha database.
1098 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
1100 =item $hashref->{'ordernumber'} is a "minimum order number."
1102 =item $hashref->{'budgetdate'} is effectively ignored.
1103 If it's undef (anything false) or the string 'now', the current day is used.
1104 Else, the upcoming July 1st is used.
1106 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
1108 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
1110 =item defaults entrydate to Now
1112 The following keys are used: "biblionumber", "title", "basketno", "quantity", "notes", "rrp", "ecost", "gstrate", "unitprice", "subscription", "sort1", "sort2", "booksellerinvoicenumber", "listprice", "budgetdate", "purchaseordernumber", "branchcode", "booksellerinvoicenumber", "budget_id".
1119 my $orderinfo = shift;
1120 #### ------------------------------
1121 my $dbh = C4::Context->dbh;
1125 # if these parameters are missing, we can't continue
1126 for my $key (qw/basketno quantity biblionumber budget_id/) {
1127 croak "Mandatory parameter $key missing" unless $orderinfo->{$key};
1130 if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) {
1131 $orderinfo->{'subscription'} = 1;
1133 $orderinfo->{'subscription'} = 0;
1135 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
1136 if (!$orderinfo->{quantityreceived}) {
1137 $orderinfo->{quantityreceived} = 0;
1140 my $ordernumber=InsertInTable("aqorders",$orderinfo);
1141 if (not $orderinfo->{parent_ordernumber}) {
1142 my $sth = $dbh->prepare("
1144 SET parent_ordernumber = ordernumber
1145 WHERE ordernumber = ?
1147 $sth->execute($ordernumber);
1149 return ( $orderinfo->{'basketno'}, $ordernumber );
1154 #------------------------------------------------------------#
1163 my ($itemnumber, $ordernumber) = @_;
1164 my $dbh = C4::Context->dbh;
1166 INSERT INTO aqorders_items
1167 (itemnumber, ordernumber)
1170 my $sth = $dbh->prepare($query);
1171 $sth->execute( $itemnumber, $ordernumber);
1174 #------------------------------------------------------------#
1178 &ModOrder(\%hashref);
1180 Modifies an existing order. Updates the order with order number
1181 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1182 other keys of the hash update the fields with the same name in the aqorders
1183 table of the Koha database.
1188 my $orderinfo = shift;
1190 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1191 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1193 my $dbh = C4::Context->dbh;
1196 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1197 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1199 # delete($orderinfo->{'branchcode'});
1200 # the hash contains a lot of entries not in aqorders, so get the columns ...
1201 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1203 my $colnames = $sth->{NAME};
1204 #FIXME Be careful. If aqorders would have columns with diacritics,
1205 #you should need to decode what you get back from NAME.
1206 #See report 10110 and guided_reports.pl
1207 my $query = "UPDATE aqorders SET ";
1209 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1210 # ... and skip hash entries that are not in the aqorders table
1211 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1212 next unless grep(/^$orderinfokey$/, @$colnames);
1213 $query .= "$orderinfokey=?, ";
1214 push(@params, $orderinfo->{$orderinfokey});
1217 $query .= "timestamp=NOW() WHERE ordernumber=?";
1218 # push(@params, $specorderinfo{'ordernumber'});
1219 push(@params, $orderinfo->{'ordernumber'} );
1220 $sth = $dbh->prepare($query);
1221 $sth->execute(@params);
1225 #------------------------------------------------------------#
1229 &ModOrderItem(\%hashref);
1231 Modifies the itemnumber in the aqorders_items table. The input hash needs three entities:
1235 =item - itemnumber: the old itemnumber
1236 =item - ordernumber: the order this item is attached to
1237 =item - newitemnumber: the new itemnumber we want to attach the line to
1244 my $orderiteminfo = shift;
1245 if (! $orderiteminfo->{'ordernumber'} || ! $orderiteminfo->{'itemnumber'} || ! $orderiteminfo->{'newitemnumber'}){
1246 die "Ordernumber, itemnumber and newitemnumber is required";
1249 my $dbh = C4::Context->dbh;
1251 my $query = "UPDATE aqorders_items set itemnumber=? where itemnumber=? and ordernumber=?";
1252 my @params = ($orderiteminfo->{'newitemnumber'}, $orderiteminfo->{'itemnumber'}, $orderiteminfo->{'ordernumber'});
1253 my $sth = $dbh->prepare($query);
1254 $sth->execute(@params);
1260 ModItemOrder($itemnumber, $ordernumber);
1262 Modifies the ordernumber of an item in aqorders_items.
1267 my ($itemnumber, $ordernumber) = @_;
1269 return unless ($itemnumber and $ordernumber);
1271 my $dbh = C4::Context->dbh;
1273 UPDATE aqorders_items
1275 WHERE itemnumber = ?
1277 my $sth = $dbh->prepare($query);
1278 return $sth->execute($ordernumber, $itemnumber);
1281 #------------------------------------------------------------#
1283 =head3 GetCancelledOrders
1285 my @orders = GetCancelledOrders($basketno, $orderby);
1287 Returns cancelled orders for a basket
1291 sub GetCancelledOrders {
1292 my ( $basketno, $orderby ) = @_;
1294 return () unless $basketno;
1296 my $dbh = C4::Context->dbh;
1298 SELECT biblio.*, biblioitems.*, aqorders.*, aqbudgets.*
1300 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1301 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1302 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1304 AND (datecancellationprinted IS NOT NULL
1305 AND datecancellationprinted <> '0000-00-00')
1308 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1310 $query .= " ORDER BY $orderby";
1311 my $sth = $dbh->prepare($query);
1312 $sth->execute($basketno);
1313 my $results = $sth->fetchall_arrayref( {} );
1319 #------------------------------------------------------------#
1321 =head3 ModReceiveOrder
1323 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
1324 $cost, $ecost, $invoiceid, rrp, budget_id, datereceived, \@received_itemnumbers);
1326 Updates an order, to reflect the fact that it was received, at least
1327 in part. All arguments not mentioned below update the fields with the
1328 same name in the aqorders table of the Koha database.
1330 If a partial order is received, splits the order into two.
1332 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1338 sub ModReceiveOrder {
1340 $biblionumber, $ordernumber, $quantrec, $user, $cost, $ecost,
1341 $invoiceid, $rrp, $budget_id, $datereceived, $received_items
1345 my $dbh = C4::Context->dbh;
1346 $datereceived = C4::Dates->output('iso') unless $datereceived;
1347 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1348 if ($suggestionid) {
1349 ModSuggestion( {suggestionid=>$suggestionid,
1350 STATUS=>'AVAILABLE',
1351 biblionumber=> $biblionumber}
1355 my $sth=$dbh->prepare("
1356 SELECT * FROM aqorders
1357 WHERE biblionumber=? AND aqorders.ordernumber=?");
1359 $sth->execute($biblionumber,$ordernumber);
1360 my $order = $sth->fetchrow_hashref();
1363 my $new_ordernumber = $ordernumber;
1364 if ( $order->{quantity} > $quantrec ) {
1365 # Split order line in two parts: the first is the original order line
1366 # without received items (the quantity is decreased),
1367 # the second part is a new order line with quantity=quantityrec
1368 # (entirely received)
1369 $sth=$dbh->prepare("
1372 WHERE ordernumber = ?
1375 $sth->execute($order->{quantity} - $quantrec, $ordernumber);
1379 delete $order->{'ordernumber'};
1380 $order->{'quantity'} = $quantrec;
1381 $order->{'quantityreceived'} = $quantrec;
1382 $order->{'datereceived'} = $datereceived;
1383 $order->{'invoiceid'} = $invoiceid;
1384 $order->{'unitprice'} = $cost;
1385 $order->{'rrp'} = $rrp;
1386 $order->{ecost} = $ecost;
1387 $order->{'orderstatus'} = 3; # totally received
1388 $new_ordernumber = NewOrder($order);
1390 if ($received_items) {
1391 foreach my $itemnumber (@$received_items) {
1392 ModItemOrder($itemnumber, $new_ordernumber);
1396 $sth=$dbh->prepare("update aqorders
1397 set quantityreceived=?,datereceived=?,invoiceid=?,
1398 unitprice=?,rrp=?,ecost=?
1399 where biblionumber=? and ordernumber=?");
1400 $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$biblionumber,$ordernumber);
1403 return ($datereceived, $new_ordernumber);
1406 =head3 CancelReceipt
1408 my $parent_ordernumber = CancelReceipt($ordernumber);
1410 Cancel an order line receipt and update the parent order line, as if no
1412 If items are created at receipt (AcqCreateItem = receiving) then delete
1418 my $ordernumber = shift;
1420 return unless $ordernumber;
1422 my $dbh = C4::Context->dbh;
1424 SELECT datereceived, parent_ordernumber, quantity
1426 WHERE ordernumber = ?
1428 my $sth = $dbh->prepare($query);
1429 $sth->execute($ordernumber);
1430 my $order = $sth->fetchrow_hashref;
1432 warn "CancelReceipt: order $ordernumber does not exist";
1435 unless($order->{'datereceived'}) {
1436 warn "CancelReceipt: order $ordernumber is not received";
1440 my $parent_ordernumber = $order->{'parent_ordernumber'};
1442 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1443 # The order line has no parent, just mark it as not received
1446 SET quantityreceived = ?,
1449 WHERE ordernumber = ?
1451 $sth = $dbh->prepare($query);
1452 $sth->execute(0, undef, undef, $ordernumber);
1454 # The order line has a parent, increase parent quantity and delete
1457 SELECT quantity, datereceived
1459 WHERE ordernumber = ?
1461 $sth = $dbh->prepare($query);
1462 $sth->execute($parent_ordernumber);
1463 my $parent_order = $sth->fetchrow_hashref;
1464 unless($parent_order) {
1465 warn "Parent order $parent_ordernumber does not exist.";
1468 if($parent_order->{'datereceived'}) {
1469 warn "CancelReceipt: parent order is received.".
1470 " Can't cancel receipt.";
1476 WHERE ordernumber = ?
1478 $sth = $dbh->prepare($query);
1479 my $rv = $sth->execute(
1480 $order->{'quantity'} + $parent_order->{'quantity'},
1484 warn "Cannot update parent order line, so do not cancel".
1488 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1489 # Remove items that were created at receipt
1491 DELETE FROM items, aqorders_items
1492 USING items, aqorders_items
1493 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1495 $sth = $dbh->prepare($query);
1496 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1497 foreach my $itemnumber (@itemnumbers) {
1498 $sth->execute($itemnumber, $itemnumber);
1502 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1503 foreach my $itemnumber (@itemnumbers) {
1504 ModItemOrder($itemnumber, $parent_ordernumber);
1509 DELETE FROM aqorders
1510 WHERE ordernumber = ?
1512 $sth = $dbh->prepare($query);
1513 $sth->execute($ordernumber);
1517 return $parent_ordernumber;
1520 #------------------------------------------------------------#
1524 @results = &SearchOrder($search, $biblionumber, $complete);
1526 Searches for orders.
1528 C<$search> may take one of several forms: if it is an ISBN,
1529 C<&ordersearch> returns orders with that ISBN. If C<$search> is an
1530 order number, C<&ordersearch> returns orders with that order number
1531 and biblionumber C<$biblionumber>. Otherwise, C<$search> is considered
1532 to be a space-separated list of search terms; in this case, all of the
1533 terms must appear in the title (matching the beginning of title
1536 If C<$complete> is C<yes>, the results will include only completed
1537 orders. In any case, C<&ordersearch> ignores cancelled orders.
1539 C<&ordersearch> returns an array.
1540 C<@results> is an array of references-to-hash with the following keys:
1546 =item C<seriestitle>
1557 #### -------- SearchOrder-------------------------------
1558 my ( $ordernumber, $search, $ean, $supplierid, $basket ) = @_;
1560 my $dbh = C4::Context->dbh;
1565 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1566 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1567 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1568 WHERE (datecancellationprinted is NULL)";
1571 $query .= " AND (aqorders.ordernumber=?)";
1572 push @args, $ordernumber;
1575 $query .= " AND (biblio.title like ? OR biblio.author LIKE ? OR biblioitems.isbn like ?)";
1576 push @args, ("%$search%","%$search%","%$search%");
1579 $query .= " AND biblioitems.ean = ?";
1583 $query .= "AND aqbasket.booksellerid = ?";
1584 push @args, $supplierid;
1587 $query .= "AND aqorders.basketno = ?";
1588 push @args, $basket;
1591 my $sth = $dbh->prepare($query);
1592 $sth->execute(@args);
1593 my $results = $sth->fetchall_arrayref({});
1598 #------------------------------------------------------------#
1602 &DelOrder($biblionumber, $ordernumber);
1604 Cancel the order with the given order and biblio numbers. It does not
1605 delete any entries in the aqorders table, it merely marks them as
1611 my ( $bibnum, $ordernumber ) = @_;
1612 my $dbh = C4::Context->dbh;
1615 SET datecancellationprinted=now()
1616 WHERE biblionumber=? AND ordernumber=?
1618 my $sth = $dbh->prepare($query);
1619 $sth->execute( $bibnum, $ordernumber );
1621 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1622 foreach my $itemnumber (@itemnumbers){
1623 C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
1628 =head2 FUNCTIONS ABOUT PARCELS
1632 #------------------------------------------------------------#
1636 @results = &GetParcel($booksellerid, $code, $date);
1638 Looks up all of the received items from the supplier with the given
1639 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1641 C<@results> is an array of references-to-hash. The keys of each element are fields from
1642 the aqorders, biblio, and biblioitems tables of the Koha database.
1644 C<@results> is sorted alphabetically by book title.
1649 #gets all orders from a certain supplier, orders them alphabetically
1650 my ( $supplierid, $code, $datereceived ) = @_;
1651 my $dbh = C4::Context->dbh;
1654 if $code; # add % if we search on a given code (otherwise, let him empty)
1656 SELECT authorisedby,
1661 aqorders.biblionumber,
1662 aqorders.ordernumber,
1663 aqorders.parent_ordernumber,
1665 aqorders.quantityreceived,
1673 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1674 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1675 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1676 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1678 aqbasket.booksellerid = ?
1679 AND aqinvoices.invoicenumber LIKE ?
1680 AND aqorders.datereceived = ? ";
1682 my @query_params = ( $supplierid, $code, $datereceived );
1683 if ( C4::Context->preference("IndependentBranches") ) {
1684 my $userenv = C4::Context->userenv;
1685 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1686 $strsth .= " and (borrowers.branchcode = ?
1687 or borrowers.branchcode = '')";
1688 push @query_params, $userenv->{branch};
1691 $strsth .= " ORDER BY aqbasket.basketno";
1692 # ## parcelinformation : $strsth
1693 my $sth = $dbh->prepare($strsth);
1694 $sth->execute( @query_params );
1695 while ( my $data = $sth->fetchrow_hashref ) {
1696 push( @results, $data );
1698 # ## countparcelbiblio: scalar(@results)
1704 #------------------------------------------------------------#
1708 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1710 get a lists of parcels.
1717 is the bookseller this function has to get parcels.
1720 To know on what criteria the results list has to be ordered.
1723 is the booksellerinvoicenumber.
1725 =item $datefrom & $dateto
1726 to know on what date this function has to filter its search.
1731 a pointer on a hash list containing parcel informations as such :
1737 =item Last operation
1739 =item Number of biblio
1741 =item Number of items
1748 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1749 my $dbh = C4::Context->dbh;
1750 my @query_params = ();
1752 SELECT aqinvoices.invoicenumber,
1753 datereceived,purchaseordernumber,
1754 count(DISTINCT biblionumber) AS biblio,
1755 sum(quantity) AS itemsexpected,
1756 sum(quantityreceived) AS itemsreceived
1757 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1758 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1759 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1761 push @query_params, $bookseller;
1763 if ( defined $code ) {
1764 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1765 # add a % to the end of the code to allow stemming.
1766 push @query_params, "$code%";
1769 if ( defined $datefrom ) {
1770 $strsth .= ' and datereceived >= ? ';
1771 push @query_params, $datefrom;
1774 if ( defined $dateto ) {
1775 $strsth .= 'and datereceived <= ? ';
1776 push @query_params, $dateto;
1779 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1781 # can't use a placeholder to place this column name.
1782 # but, we could probably be checking to make sure it is a column that will be fetched.
1783 $strsth .= "order by $order " if ($order);
1785 my $sth = $dbh->prepare($strsth);
1787 $sth->execute( @query_params );
1788 my $results = $sth->fetchall_arrayref({});
1793 #------------------------------------------------------------#
1795 =head3 GetLateOrders
1797 @results = &GetLateOrders;
1799 Searches for bookseller with late orders.
1802 the table of supplier with late issues. This table is full of hashref.
1808 my $supplierid = shift;
1810 my $estimateddeliverydatefrom = shift;
1811 my $estimateddeliverydateto = shift;
1813 my $dbh = C4::Context->dbh;
1815 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
1816 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
1818 my @query_params = ();
1820 SELECT aqbasket.basketno,
1821 aqorders.ordernumber,
1822 DATE(aqbasket.closedate) AS orderdate,
1823 aqorders.rrp AS unitpricesupplier,
1824 aqorders.ecost AS unitpricelib,
1825 aqorders.claims_count AS claims_count,
1826 aqorders.claimed_date AS claimed_date,
1827 aqbudgets.budget_name AS budget,
1828 borrowers.branchcode AS branch,
1829 aqbooksellers.name AS supplier,
1830 aqbooksellers.id AS supplierid,
1831 biblio.author, biblio.title,
1832 biblioitems.publishercode AS publisher,
1833 biblioitems.publicationyear,
1834 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1838 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1839 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1840 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1841 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1842 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1843 WHERE aqorders.basketno = aqbasket.basketno
1844 AND ( datereceived = ''
1845 OR datereceived IS NULL
1846 OR aqorders.quantityreceived < aqorders.quantity
1848 AND aqbasket.closedate IS NOT NULL
1849 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
1852 if ($dbdriver eq "mysql") {
1854 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
1855 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1856 DATEDIFF(CAST(now() AS date),closedate) AS latesince
1858 if ( defined $delay ) {
1859 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
1860 push @query_params, $delay;
1863 HAVING quantity <> 0
1864 AND unitpricesupplier <> 0
1865 AND unitpricelib <> 0
1868 # FIXME: account for IFNULL as above
1870 aqorders.quantity AS quantity,
1871 aqorders.quantity * aqorders.rrp AS subtotal,
1872 (CAST(now() AS date) - closedate) AS latesince
1874 if ( defined $delay ) {
1875 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
1876 push @query_params, $delay;
1879 if (defined $supplierid) {
1880 $from .= ' AND aqbasket.booksellerid = ? ';
1881 push @query_params, $supplierid;
1883 if (defined $branch) {
1884 $from .= ' AND borrowers.branchcode LIKE ? ';
1885 push @query_params, $branch;
1888 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
1889 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
1891 if ( defined $estimateddeliverydatefrom ) {
1892 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
1893 push @query_params, $estimateddeliverydatefrom;
1895 if ( defined $estimateddeliverydateto ) {
1896 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
1897 push @query_params, $estimateddeliverydateto;
1899 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
1900 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
1902 if (C4::Context->preference("IndependentBranches")
1903 && C4::Context->userenv
1904 && C4::Context->userenv->{flags} != 1 ) {
1905 $from .= ' AND borrowers.branchcode LIKE ? ';
1906 push @query_params, C4::Context->userenv->{branch};
1908 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
1909 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
1910 my $sth = $dbh->prepare($query);
1911 $sth->execute(@query_params);
1913 while (my $data = $sth->fetchrow_hashref) {
1914 $data->{orderdate} = format_date($data->{orderdate});
1915 $data->{claimed_date} = format_date($data->{claimed_date});
1916 push @results, $data;
1921 #------------------------------------------------------------#
1925 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
1927 Retreives some acquisition history information
1935 basket - search both basket name and number
1936 booksellerinvoicenumber
1939 $order_loop is a list of hashrefs that each look like this:
1941 'author' => 'Twain, Mark',
1943 'biblionumber' => '215',
1945 'creationdate' => 'MM/DD/YYYY',
1946 'datereceived' => undef,
1949 'invoicenumber' => undef,
1951 'ordernumber' => '1',
1953 'quantityreceived' => undef,
1954 'title' => 'The Adventures of Huckleberry Finn'
1956 $total_qty is the sum of all of the quantities in $order_loop
1957 $total_price is the cost of each in $order_loop times the quantity
1958 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
1963 # don't run the query if there are no parameters (list would be too long for sure !)
1964 croak "No search params" unless @_;
1966 my $title = $params{title};
1967 my $author = $params{author};
1968 my $isbn = $params{isbn};
1969 my $ean = $params{ean};
1970 my $name = $params{name};
1971 my $from_placed_on = $params{from_placed_on};
1972 my $to_placed_on = $params{to_placed_on};
1973 my $basket = $params{basket};
1974 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
1975 my $basketgroupname = $params{basketgroupname};
1978 my $total_qtyreceived = 0;
1979 my $total_price = 0;
1981 my $dbh = C4::Context->dbh;
1989 aqbasket.basketname,
1990 aqbasket.basketgroupid,
1991 aqbasketgroups.name as groupname,
1993 aqbasket.creationdate,
1994 aqorders.datereceived,
1996 aqorders.quantityreceived,
1998 aqorders.ordernumber,
2000 aqinvoices.invoicenumber,
2001 aqbooksellers.id as id,
2002 aqorders.biblionumber
2004 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2005 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2006 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2007 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2008 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2009 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid";
2011 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"
2012 if ( C4::Context->preference("IndependentBranches") );
2014 $query .= " WHERE (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2016 my @query_params = ();
2019 $query .= " AND biblio.title LIKE ? ";
2020 $title =~ s/\s+/%/g;
2021 push @query_params, "%$title%";
2025 $query .= " AND biblio.author LIKE ? ";
2026 push @query_params, "%$author%";
2030 $query .= " AND biblioitems.isbn LIKE ? ";
2031 push @query_params, "%$isbn%";
2033 if ( defined $ean and $ean ) {
2034 $query .= " AND biblioitems.ean = ? ";
2035 push @query_params, "$ean";
2038 $query .= " AND aqbooksellers.name LIKE ? ";
2039 push @query_params, "%$name%";
2042 if ( $from_placed_on ) {
2043 $query .= " AND creationdate >= ? ";
2044 push @query_params, $from_placed_on;
2047 if ( $to_placed_on ) {
2048 $query .= " AND creationdate <= ? ";
2049 push @query_params, $to_placed_on;
2053 if ($basket =~ m/^\d+$/) {
2054 $query .= " AND aqorders.basketno = ? ";
2055 push @query_params, $basket;
2057 $query .= " AND aqbasket.basketname LIKE ? ";
2058 push @query_params, "%$basket%";
2062 if ($booksellerinvoicenumber) {
2063 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2064 push @query_params, "%$booksellerinvoicenumber%";
2067 if ($basketgroupname) {
2068 $query .= " AND aqbasketgroups.name LIKE ? ";
2069 push @query_params, "%$basketgroupname%";
2072 if ( C4::Context->preference("IndependentBranches") ) {
2073 my $userenv = C4::Context->userenv;
2074 if ( $userenv && ($userenv->{flags} || 0) != 1 ) {
2075 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2076 push @query_params, $userenv->{branch};
2079 $query .= " ORDER BY id";
2080 my $sth = $dbh->prepare($query);
2081 $sth->execute( @query_params );
2083 while ( my $line = $sth->fetchrow_hashref ) {
2084 $line->{count} = $cnt++;
2085 $line->{toggle} = 1 if $cnt % 2;
2086 push @order_loop, $line;
2087 $total_qty += $line->{'quantity'};
2088 $total_qtyreceived += $line->{'quantityreceived'};
2089 $total_price += $line->{'quantity'} * $line->{'ecost'};
2091 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
2094 =head2 GetRecentAcqui
2096 $results = GetRecentAcqui($days);
2098 C<$results> is a ref to a table which containts hashref
2102 sub GetRecentAcqui {
2104 my $dbh = C4::Context->dbh;
2108 ORDER BY timestamp DESC
2111 my $sth = $dbh->prepare($query);
2113 my $results = $sth->fetchall_arrayref({});
2119 $contractlist = &GetContracts($booksellerid, $activeonly);
2121 Looks up the contracts that belong to a bookseller
2123 Returns a list of contracts
2127 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
2129 =item C<$activeonly> if exists get only contracts that are still active.
2136 my ( $booksellerid, $activeonly ) = @_;
2137 my $dbh = C4::Context->dbh;
2139 if (! $activeonly) {
2143 WHERE booksellerid=?
2148 WHERE booksellerid=?
2149 AND contractenddate >= CURDATE( )";
2151 my $sth = $dbh->prepare($query);
2152 $sth->execute( $booksellerid );
2154 while (my $data = $sth->fetchrow_hashref ) {
2155 push(@results, $data);
2161 #------------------------------------------------------------#
2165 $contract = &GetContract($contractID);
2167 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
2174 my ( $contractno ) = @_;
2175 my $dbh = C4::Context->dbh;
2179 WHERE contractnumber=?
2182 my $sth = $dbh->prepare($query);
2183 $sth->execute( $contractno );
2184 my $result = $sth->fetchrow_hashref;
2192 &AddClaim($ordernumber);
2194 Add a claim for an order
2200 my ($ordernumber) = @_;
2201 my $dbh = C4::Context->dbh;
2204 claims_count = claims_count + 1,
2205 claimed_date = CURDATE()
2206 WHERE ordernumber = ?
2208 my $sth = $dbh->prepare($query);
2209 $sth->execute($ordernumber);
2214 my @invoices = GetInvoices(
2215 invoicenumber => $invoicenumber,
2216 suppliername => $suppliername,
2217 shipmentdatefrom => $shipmentdatefrom, # ISO format
2218 shipmentdateto => $shipmentdateto, # ISO format
2219 billingdatefrom => $billingdatefrom, # ISO format
2220 billingdateto => $billingdateto, # ISO format
2221 isbneanissn => $isbn_or_ean_or_issn,
2224 publisher => $publisher,
2225 publicationyear => $publicationyear,
2226 branchcode => $branchcode,
2227 order_by => $order_by
2230 Return a list of invoices that match all given criteria.
2232 $order_by is "column_name (asc|desc)", where column_name is any of
2233 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2234 'shipmentcost', 'shipmentcost_budgetid'.
2236 asc is the default if omitted
2243 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2244 closedate shipmentcost shipmentcost_budgetid);
2246 my $dbh = C4::Context->dbh;
2248 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2251 aqorders.datereceived IS NOT NULL,
2252 aqorders.biblionumber,
2255 ) AS receivedbiblios,
2256 SUM(aqorders.quantityreceived) AS receiveditems
2258 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2259 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2260 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2261 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2262 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2267 if($args{supplierid}) {
2268 push @bind_strs, " aqinvoices.booksellerid = ? ";
2269 push @bind_args, $args{supplierid};
2271 if($args{invoicenumber}) {
2272 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2273 push @bind_args, "%$args{invoicenumber}%";
2275 if($args{suppliername}) {
2276 push @bind_strs, " aqbooksellers.name LIKE ? ";
2277 push @bind_args, "%$args{suppliername}%";
2279 if($args{shipmentdatefrom}) {
2280 push @bind_strs, " aqinvoices.shipementdate >= ? ";
2281 push @bind_args, $args{shipmentdatefrom};
2283 if($args{shipmentdateto}) {
2284 push @bind_strs, " aqinvoices.shipementdate <= ? ";
2285 push @bind_args, $args{shipmentdateto};
2287 if($args{billingdatefrom}) {
2288 push @bind_strs, " aqinvoices.billingdate >= ? ";
2289 push @bind_args, $args{billingdatefrom};
2291 if($args{billingdateto}) {
2292 push @bind_strs, " aqinvoices.billingdate <= ? ";
2293 push @bind_args, $args{billingdateto};
2295 if($args{isbneanissn}) {
2296 push @bind_strs, " (biblioitems.isbn LIKE ? OR biblioitems.ean LIKE ? OR biblioitems.issn LIKE ? ) ";
2297 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2300 push @bind_strs, " biblio.title LIKE ? ";
2301 push @bind_args, $args{title};
2304 push @bind_strs, " biblio.author LIKE ? ";
2305 push @bind_args, $args{author};
2307 if($args{publisher}) {
2308 push @bind_strs, " biblioitems.publishercode LIKE ? ";
2309 push @bind_args, $args{publisher};
2311 if($args{publicationyear}) {
2312 push @bind_strs, " biblioitems.publicationyear = ? ";
2313 push @bind_args, $args{publicationyear};
2315 if($args{branchcode}) {
2316 push @bind_strs, " aqorders.branchcode = ? ";
2317 push @bind_args, $args{branchcode};
2320 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2321 $query .= " GROUP BY aqinvoices.invoiceid ";
2323 if($args{order_by}) {
2324 my ($column, $direction) = split / /, $args{order_by};
2325 if(grep /^$column$/, @columns) {
2326 $direction ||= 'ASC';
2327 $query .= " ORDER BY $column $direction";
2331 my $sth = $dbh->prepare($query);
2332 $sth->execute(@bind_args);
2334 my $results = $sth->fetchall_arrayref({});
2340 my $invoice = GetInvoice($invoiceid);
2342 Get informations about invoice with given $invoiceid
2344 Return a hash filled with aqinvoices.* fields
2349 my ($invoiceid) = @_;
2352 return unless $invoiceid;
2354 my $dbh = C4::Context->dbh;
2360 my $sth = $dbh->prepare($query);
2361 $sth->execute($invoiceid);
2363 $invoice = $sth->fetchrow_hashref;
2367 =head3 GetInvoiceDetails
2369 my $invoice = GetInvoiceDetails($invoiceid)
2371 Return informations about an invoice + the list of related order lines
2373 Orders informations are in $invoice->{orders} (array ref)
2377 sub GetInvoiceDetails {
2378 my ($invoiceid) = @_;
2380 if ( !defined $invoiceid ) {
2381 carp 'GetInvoiceDetails called without an invoiceid';
2385 my $dbh = C4::Context->dbh;
2387 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2389 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2392 my $sth = $dbh->prepare($query);
2393 $sth->execute($invoiceid);
2395 my $invoice = $sth->fetchrow_hashref;
2398 SELECT aqorders.*, biblio.*
2400 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2403 $sth = $dbh->prepare($query);
2404 $sth->execute($invoiceid);
2405 $invoice->{orders} = $sth->fetchall_arrayref({});
2406 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2413 my $invoiceid = AddInvoice(
2414 invoicenumber => $invoicenumber,
2415 booksellerid => $booksellerid,
2416 shipmentdate => $shipmentdate,
2417 billingdate => $billingdate,
2418 closedate => $closedate,
2419 shipmentcost => $shipmentcost,
2420 shipmentcost_budgetid => $shipmentcost_budgetid
2423 Create a new invoice and return its id or undef if it fails.
2430 return unless(%invoice and $invoice{invoicenumber});
2432 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2433 closedate shipmentcost shipmentcost_budgetid);
2437 foreach my $key (keys %invoice) {
2438 if(0 < grep(/^$key$/, @columns)) {
2439 push @set_strs, "$key = ?";
2440 push @set_args, ($invoice{$key} || undef);
2446 my $dbh = C4::Context->dbh;
2447 my $query = "INSERT INTO aqinvoices SET ";
2448 $query .= join (",", @set_strs);
2449 my $sth = $dbh->prepare($query);
2450 $rv = $sth->execute(@set_args);
2452 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2461 invoiceid => $invoiceid, # Mandatory
2462 invoicenumber => $invoicenumber,
2463 booksellerid => $booksellerid,
2464 shipmentdate => $shipmentdate,
2465 billingdate => $billingdate,
2466 closedate => $closedate,
2467 shipmentcost => $shipmentcost,
2468 shipmentcost_budgetid => $shipmentcost_budgetid
2471 Modify an invoice, invoiceid is mandatory.
2473 Return undef if it fails.
2480 return unless(%invoice and $invoice{invoiceid});
2482 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2483 closedate shipmentcost shipmentcost_budgetid);
2487 foreach my $key (keys %invoice) {
2488 if(0 < grep(/^$key$/, @columns)) {
2489 push @set_strs, "$key = ?";
2490 push @set_args, ($invoice{$key} || undef);
2494 my $dbh = C4::Context->dbh;
2495 my $query = "UPDATE aqinvoices SET ";
2496 $query .= join(",", @set_strs);
2497 $query .= " WHERE invoiceid = ?";
2499 my $sth = $dbh->prepare($query);
2500 $sth->execute(@set_args, $invoice{invoiceid});
2505 CloseInvoice($invoiceid);
2509 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2514 my ($invoiceid) = @_;
2516 return unless $invoiceid;
2518 my $dbh = C4::Context->dbh;
2521 SET closedate = CAST(NOW() AS DATE)
2524 my $sth = $dbh->prepare($query);
2525 $sth->execute($invoiceid);
2528 =head3 ReopenInvoice
2530 ReopenInvoice($invoiceid);
2534 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2539 my ($invoiceid) = @_;
2541 return unless $invoiceid;
2543 my $dbh = C4::Context->dbh;
2546 SET closedate = NULL
2549 my $sth = $dbh->prepare($query);
2550 $sth->execute($invoiceid);
2558 Koha Development Team <http://koha-community.org/>