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 UpdateInTable);
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 &ReopenBasket &DelBasket &ModBasket
47 &GetBasketAsCSV &GetBasketGroupAsCSV
48 &GetBasketsByBookseller &GetBasketsByBasketgroup
49 &GetBasketsInfosByBookseller
53 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
54 &GetBasketgroups &ReOpenBasketgroup
56 &NewOrder &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
57 &GetLateOrders &GetOrderFromItemnumber
58 &SearchOrders &GetHistory &GetRecentAcqui
59 &ModReceiveOrder &CancelReceipt
60 &GetCancelledOrders &TransferOrder
61 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
62 &NewOrderItem &ModItemOrder
64 &GetParcels &GetParcel
65 &GetContracts &GetContract
77 &GetItemnumbersFromOrder
80 &GetBiblioCountByBasketno
88 sub GetOrderFromItemnumber {
89 my ($itemnumber) = @_;
90 my $dbh = C4::Context->dbh;
93 SELECT * from aqorders LEFT JOIN aqorders_items
94 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
95 WHERE itemnumber = ? |;
97 my $sth = $dbh->prepare($query);
101 $sth->execute($itemnumber);
103 my $order = $sth->fetchrow_hashref;
108 # Returns the itemnumber(s) associated with the ordernumber given in parameter
109 sub GetItemnumbersFromOrder {
110 my ($ordernumber) = @_;
111 my $dbh = C4::Context->dbh;
112 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
113 my $sth = $dbh->prepare($query);
114 $sth->execute($ordernumber);
117 while (my $order = $sth->fetchrow_hashref) {
118 push @tab, $order->{'itemnumber'};
132 C4::Acquisition - Koha functions for dealing with orders and acquisitions
140 The functions in this module deal with acquisitions, managing book
141 orders, basket and parcels.
145 =head2 FUNCTIONS ABOUT BASKETS
149 $aqbasket = &GetBasket($basketnumber);
151 get all basket informations in aqbasket for a given basket
153 B<returns:> informations for a given basket returned as a hashref.
159 my $dbh = C4::Context->dbh;
162 concat( b.firstname,' ',b.surname) AS authorisedbyname,
163 b.branchcode AS branch
165 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
168 my $sth=$dbh->prepare($query);
169 $sth->execute($basketno);
170 my $basket = $sth->fetchrow_hashref;
174 #------------------------------------------------------------#
178 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
179 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace );
181 Create a new basket in aqbasket table
185 =item C<$booksellerid> is a foreign key in the aqbasket table
187 =item C<$authorizedby> is the username of who created the basket
191 The other parameters are optional, see ModBasketHeader for more info on them.
196 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
197 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
198 $billingplace ) = @_;
199 my $dbh = C4::Context->dbh;
201 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
202 . 'VALUES (now(),?,?)';
203 $dbh->do( $query, {}, $booksellerid, $authorisedby );
205 my $basket = $dbh->{mysql_insertid};
206 $basketname ||= q{}; # default to empty strings
208 $basketbooksellernote ||= q{};
209 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
210 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace );
214 #------------------------------------------------------------#
218 &CloseBasket($basketno);
220 close a basket (becomes unmodifiable, except for receives)
226 my $dbh = C4::Context->dbh;
232 my $sth = $dbh->prepare($query);
233 $sth->execute($basketno);
235 my @orders = GetOrders($basketno);
236 foreach my $order (@orders) {
240 WHERE ordernumber = ?;
242 $sth = $dbh->prepare($query);
243 $sth->execute($order->{'ordernumber'});
249 &ReopenBasket($basketno);
257 my $dbh = C4::Context->dbh;
263 my $sth = $dbh->prepare($query);
264 $sth->execute($basketno);
266 my @orders = GetOrders($basketno);
267 foreach my $order (@orders) {
271 WHERE ordernumber = ?;
273 $sth = $dbh->prepare($query);
274 $sth->execute($order->{'ordernumber'});
278 #------------------------------------------------------------#
280 =head3 GetBasketAsCSV
282 &GetBasketAsCSV($basketno);
284 Export a basket as CSV
286 $cgi parameter is needed for column name translation
291 my ($basketno, $cgi) = @_;
292 my $basket = GetBasket($basketno);
293 my @orders = GetOrders($basketno);
294 my $contract = GetContract($basket->{'contractnumber'});
296 my $template = C4::Templates::gettemplate("acqui/csv/basket.tmpl", "intranet", $cgi);
299 foreach my $order (@orders) {
300 my $bd = GetBiblioData( $order->{'biblionumber'} );
302 contractname => $contract->{'contractname'},
303 ordernumber => $order->{'ordernumber'},
304 entrydate => $order->{'entrydate'},
305 isbn => $order->{'isbn'},
306 author => $bd->{'author'},
307 title => $bd->{'title'},
308 publicationyear => $bd->{'publicationyear'},
309 publishercode => $bd->{'publishercode'},
310 collectiontitle => $bd->{'collectiontitle'},
311 notes => $order->{'notes'},
312 quantity => $order->{'quantity'},
313 rrp => $order->{'rrp'},
314 deliveryplace => C4::Branch::GetBranchName( $basket->{'deliveryplace'} ),
315 billingplace => C4::Branch::GetBranchName( $basket->{'billingplace'} ),
318 contractname author title publishercode collectiontitle notes
319 deliveryplace billingplace
321 # Double the quotes to not be interpreted as a field end
322 $row->{$_} =~ s/"/""/g if $row->{$_};
328 if(defined $a->{publishercode} and defined $b->{publishercode}) {
329 $a->{publishercode} cmp $b->{publishercode};
333 $template->param(rows => \@rows);
335 return $template->output;
339 =head3 GetBasketGroupAsCSV
343 &GetBasketGroupAsCSV($basketgroupid);
345 Export a basket group as CSV
347 $cgi parameter is needed for column name translation
353 sub GetBasketGroupAsCSV {
354 my ($basketgroupid, $cgi) = @_;
355 my $baskets = GetBasketsByBasketgroup($basketgroupid);
357 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tmpl', 'intranet', $cgi);
360 for my $basket (@$baskets) {
361 my @orders = GetOrders( $$basket{basketno} );
362 my $contract = GetContract( $$basket{contractnumber} );
363 my $bookseller = GetBookSellerFromId( $$basket{booksellerid} );
364 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
366 foreach my $order (@orders) {
367 my $bd = GetBiblioData( $order->{'biblionumber'} );
369 clientnumber => $bookseller->{accountnumber},
370 basketname => $basket->{basketname},
371 ordernumber => $order->{ordernumber},
372 author => $bd->{author},
373 title => $bd->{title},
374 publishercode => $bd->{publishercode},
375 publicationyear => $bd->{publicationyear},
376 collectiontitle => $bd->{collectiontitle},
377 isbn => $order->{isbn},
378 quantity => $order->{quantity},
379 rrp => $order->{rrp},
380 discount => $bookseller->{discount},
381 ecost => $order->{ecost},
382 notes => $order->{notes},
383 entrydate => $order->{entrydate},
384 booksellername => $bookseller->{name},
385 bookselleraddress => $bookseller->{address1},
386 booksellerpostal => $bookseller->{postal},
387 contractnumber => $contract->{contractnumber},
388 contractname => $contract->{contractname},
389 basketgroupdeliveryplace => C4::Branch::GetBranchName( $basketgroup->{deliveryplace} ),
390 basketgroupbillingplace => C4::Branch::GetBranchName( $basketgroup->{billingplace} ),
391 basketdeliveryplace => C4::Branch::GetBranchName( $basket->{deliveryplace} ),
392 basketbillingplace => C4::Branch::GetBranchName( $basket->{billingplace} ),
395 basketname author title publishercode collectiontitle notes
396 booksellername bookselleraddress booksellerpostal contractname
397 basketgroupdeliveryplace basketgroupbillingplace
398 basketdeliveryplace basketbillingplace
400 # Double the quotes to not be interpreted as a field end
401 $row->{$_} =~ s/"/""/g if $row->{$_};
406 $template->param(rows => \@rows);
408 return $template->output;
412 =head3 CloseBasketgroup
414 &CloseBasketgroup($basketgroupno);
420 sub CloseBasketgroup {
421 my ($basketgroupno) = @_;
422 my $dbh = C4::Context->dbh;
423 my $sth = $dbh->prepare("
424 UPDATE aqbasketgroups
428 $sth->execute($basketgroupno);
431 #------------------------------------------------------------#
433 =head3 ReOpenBaskergroup($basketgroupno)
435 &ReOpenBaskergroup($basketgroupno);
441 sub ReOpenBasketgroup {
442 my ($basketgroupno) = @_;
443 my $dbh = C4::Context->dbh;
444 my $sth = $dbh->prepare("
445 UPDATE aqbasketgroups
449 $sth->execute($basketgroupno);
452 #------------------------------------------------------------#
457 &DelBasket($basketno);
459 Deletes the basket that has basketno field $basketno in the aqbasket table.
463 =item C<$basketno> is the primary key of the basket in the aqbasket table.
470 my ( $basketno ) = @_;
471 my $query = "DELETE FROM aqbasket WHERE basketno=?";
472 my $dbh = C4::Context->dbh;
473 my $sth = $dbh->prepare($query);
474 $sth->execute($basketno);
478 #------------------------------------------------------------#
482 &ModBasket($basketinfo);
484 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
488 =item C<$basketno> is the primary key of the basket in the aqbasket table.
495 my $basketinfo = shift;
496 my $query = "UPDATE aqbasket SET ";
498 foreach my $key (keys %$basketinfo){
499 if ($key ne 'basketno'){
500 $query .= "$key=?, ";
501 push(@params, $basketinfo->{$key} || undef );
504 # get rid of the "," at the end of $query
505 if (substr($query, length($query)-2) eq ', '){
510 $query .= "WHERE basketno=?";
511 push(@params, $basketinfo->{'basketno'});
512 my $dbh = C4::Context->dbh;
513 my $sth = $dbh->prepare($query);
514 $sth->execute(@params);
519 #------------------------------------------------------------#
521 =head3 ModBasketHeader
523 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
525 Modifies a basket's header.
529 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
531 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
533 =item C<$note> is the "note" field in the "aqbasket" table;
535 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
537 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
539 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
541 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
543 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
549 sub ModBasketHeader {
550 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace) = @_;
553 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?
557 my $dbh = C4::Context->dbh;
558 my $sth = $dbh->prepare($query);
559 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $basketno);
561 if ( $contractnumber ) {
562 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
563 my $sth2 = $dbh->prepare($query2);
564 $sth2->execute($contractnumber,$basketno);
570 #------------------------------------------------------------#
572 =head3 GetBasketsByBookseller
574 @results = &GetBasketsByBookseller($booksellerid, $extra);
576 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
580 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
582 =item C<$extra> is the extra sql parameters, can be
584 $extra->{groupby}: group baskets by column
585 ex. $extra->{groupby} = aqbasket.basketgroupid
586 $extra->{orderby}: order baskets by column
587 $extra->{limit}: limit number of results (can be helpful for pagination)
593 sub GetBasketsByBookseller {
594 my ($booksellerid, $extra) = @_;
595 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
597 if ($extra->{groupby}) {
598 $query .= " GROUP by $extra->{groupby}";
600 if ($extra->{orderby}){
601 $query .= " ORDER by $extra->{orderby}";
603 if ($extra->{limit}){
604 $query .= " LIMIT $extra->{limit}";
607 my $dbh = C4::Context->dbh;
608 my $sth = $dbh->prepare($query);
609 $sth->execute($booksellerid);
610 my $results = $sth->fetchall_arrayref({});
615 =head3 GetBasketsInfosByBookseller
617 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
619 The optional second parameter allbaskets is a boolean allowing you to
620 select all baskets from the supplier; by default only active baskets (open or
621 closed but still something to receive) are returned.
623 Returns in a arrayref of hashref all about booksellers baskets, plus:
624 total_biblios: Number of distinct biblios in basket
625 total_items: Number of items in basket
626 expected_items: Number of non-received items in basket
630 sub GetBasketsInfosByBookseller {
631 my ($supplierid, $allbaskets) = @_;
633 return unless $supplierid;
635 my $dbh = C4::Context->dbh;
638 SUM(aqorders.quantity) AS total_items,
639 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
641 IF(aqorders.datereceived IS NULL
642 AND aqorders.datecancellationprinted IS NULL
647 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
648 WHERE booksellerid = ?};
650 $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
652 $query.=" GROUP BY aqbasket.basketno";
654 my $sth = $dbh->prepare($query);
655 $sth->execute($supplierid);
656 return $sth->fetchall_arrayref({});
660 #------------------------------------------------------------#
662 =head3 GetBasketsByBasketgroup
664 $baskets = &GetBasketsByBasketgroup($basketgroupid);
666 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
670 sub GetBasketsByBasketgroup {
671 my $basketgroupid = shift;
673 SELECT *, aqbasket.booksellerid as booksellerid
675 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
677 my $dbh = C4::Context->dbh;
678 my $sth = $dbh->prepare($query);
679 $sth->execute($basketgroupid);
680 my $results = $sth->fetchall_arrayref({});
685 #------------------------------------------------------------#
687 =head3 NewBasketgroup
689 $basketgroupid = NewBasketgroup(\%hashref);
691 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
693 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
695 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
697 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
699 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
701 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
703 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
705 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
707 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
712 my $basketgroupinfo = shift;
713 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
714 my $query = "INSERT INTO aqbasketgroups (";
716 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
717 if ( defined $basketgroupinfo->{$field} ) {
718 $query .= "$field, ";
719 push(@params, $basketgroupinfo->{$field});
722 $query .= "booksellerid) VALUES (";
727 push(@params, $basketgroupinfo->{'booksellerid'});
728 my $dbh = C4::Context->dbh;
729 my $sth = $dbh->prepare($query);
730 $sth->execute(@params);
731 my $basketgroupid = $dbh->{'mysql_insertid'};
732 if( $basketgroupinfo->{'basketlist'} ) {
733 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
734 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
735 my $sth2 = $dbh->prepare($query2);
736 $sth2->execute($basketgroupid, $basketno);
739 return $basketgroupid;
742 #------------------------------------------------------------#
744 =head3 ModBasketgroup
746 ModBasketgroup(\%hashref);
748 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
750 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
752 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
754 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
756 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
758 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
760 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
762 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
764 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
769 my $basketgroupinfo = shift;
770 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
771 my $dbh = C4::Context->dbh;
772 my $query = "UPDATE aqbasketgroups SET ";
774 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
775 if ( defined $basketgroupinfo->{$field} ) {
776 $query .= "$field=?, ";
777 push(@params, $basketgroupinfo->{$field});
782 $query .= " WHERE id=?";
783 push(@params, $basketgroupinfo->{'id'});
784 my $sth = $dbh->prepare($query);
785 $sth->execute(@params);
787 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
788 $sth->execute($basketgroupinfo->{'id'});
790 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
791 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
792 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
793 $sth->execute($basketgroupinfo->{'id'}, $basketno);
800 #------------------------------------------------------------#
802 =head3 DelBasketgroup
804 DelBasketgroup($basketgroupid);
806 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
810 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
817 my $basketgroupid = shift;
818 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
819 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
820 my $dbh = C4::Context->dbh;
821 my $sth = $dbh->prepare($query);
822 $sth->execute($basketgroupid);
826 #------------------------------------------------------------#
829 =head2 FUNCTIONS ABOUT ORDERS
831 =head3 GetBasketgroup
833 $basketgroup = &GetBasketgroup($basketgroupid);
835 Returns a reference to the hash containing all infermation about the basketgroup.
840 my $basketgroupid = shift;
841 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
842 my $query = "SELECT * FROM aqbasketgroups WHERE id=?";
843 my $dbh = C4::Context->dbh;
844 my $sth = $dbh->prepare($query);
845 $sth->execute($basketgroupid);
846 my $result = $sth->fetchrow_hashref;
851 #------------------------------------------------------------#
853 =head3 GetBasketgroups
855 $basketgroups = &GetBasketgroups($booksellerid);
857 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
861 sub GetBasketgroups {
862 my $booksellerid = shift;
863 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
864 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
865 my $dbh = C4::Context->dbh;
866 my $sth = $dbh->prepare($query);
867 $sth->execute($booksellerid);
868 return $sth->fetchall_arrayref({});
871 #------------------------------------------------------------#
873 =head2 FUNCTIONS ABOUT ORDERS
877 @orders = &GetOrders($basketnumber, $orderby);
879 Looks up the pending (non-cancelled) orders with the given basket
880 number. If C<$booksellerID> is non-empty, only orders from that seller
884 C<&basket> returns a two-element array. C<@orders> is an array of
885 references-to-hash, whose keys are the fields from the aqorders,
886 biblio, and biblioitems tables in the Koha database.
891 my ( $basketno, $orderby ) = @_;
892 my $dbh = C4::Context->dbh;
894 SELECT biblio.*,biblioitems.*,
898 aqorders_transfers.ordernumber_from AS transferred_from,
899 aqorders_transfers.timestamp AS transferred_from_timestamp
901 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
902 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
903 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
904 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
906 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
909 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
910 $query .= " ORDER BY $orderby";
911 my $sth = $dbh->prepare($query);
912 $sth->execute($basketno);
913 my $results = $sth->fetchall_arrayref({});
918 #------------------------------------------------------------#
919 =head3 GetOrdersByBiblionumber
921 @orders = &GetOrdersByBiblionumber($biblionumber);
923 Looks up the orders with linked to a specific $biblionumber, including
924 cancelled orders and received orders.
927 C<@orders> is an array of references-to-hash, whose keys are the
928 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
932 sub GetOrdersByBiblionumber {
933 my $biblionumber = shift;
934 return unless $biblionumber;
935 my $dbh = C4::Context->dbh;
937 SELECT biblio.*,biblioitems.*,
941 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
942 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
943 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
944 WHERE aqorders.biblionumber=?
946 my $sth = $dbh->prepare($query);
947 $sth->execute($biblionumber);
948 my $results = $sth->fetchall_arrayref({});
953 #------------------------------------------------------------#
957 $order = &GetOrder($ordernumber);
959 Looks up an order by order number.
961 Returns a reference-to-hash describing the order. The keys of
962 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
967 my ($ordernumber) = @_;
968 my $dbh = C4::Context->dbh;
970 SELECT biblioitems.*, biblio.*, aqorders.*
972 LEFT JOIN biblio on biblio.biblionumber=aqorders.biblionumber
973 LEFT JOIN biblioitems on biblioitems.biblionumber=aqorders.biblionumber
974 WHERE aqorders.ordernumber=?
977 my $sth= $dbh->prepare($query);
978 $sth->execute($ordernumber);
979 my $data = $sth->fetchrow_hashref;
984 =head3 GetLastOrderNotReceivedFromSubscriptionid
986 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
988 Returns a reference-to-hash describing the last order not received for a subscription.
992 sub GetLastOrderNotReceivedFromSubscriptionid {
993 my ( $subscriptionid ) = @_;
994 my $dbh = C4::Context->dbh;
996 SELECT * FROM aqorders
997 LEFT JOIN subscription
998 ON ( aqorders.subscriptionid = subscription.subscriptionid )
999 WHERE aqorders.subscriptionid = ?
1000 AND aqorders.datereceived IS NULL
1003 my $sth = $dbh->prepare( $query );
1004 $sth->execute( $subscriptionid );
1005 my $order = $sth->fetchrow_hashref;
1009 =head3 GetLastOrderReceivedFromSubscriptionid
1011 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1013 Returns a reference-to-hash describing the last order received for a subscription.
1017 sub GetLastOrderReceivedFromSubscriptionid {
1018 my ( $subscriptionid ) = @_;
1019 my $dbh = C4::Context->dbh;
1021 SELECT * FROM aqorders
1022 LEFT JOIN subscription
1023 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1024 WHERE aqorders.subscriptionid = ?
1025 AND aqorders.datereceived =
1027 SELECT MAX( aqorders.datereceived )
1029 LEFT JOIN subscription
1030 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1031 WHERE aqorders.subscriptionid = ?
1032 AND aqorders.datereceived IS NOT NULL
1034 ORDER BY ordernumber DESC
1037 my $sth = $dbh->prepare( $query );
1038 $sth->execute( $subscriptionid, $subscriptionid );
1039 my $order = $sth->fetchrow_hashref;
1045 #------------------------------------------------------------#
1049 &NewOrder(\%hashref);
1051 Adds a new order to the database. Any argument that isn't described
1052 below is the new value of the field with the same name in the aqorders
1053 table of the Koha database.
1057 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
1059 =item $hashref->{'ordernumber'} is a "minimum order number."
1061 =item $hashref->{'budgetdate'} is effectively ignored.
1062 If it's undef (anything false) or the string 'now', the current day is used.
1063 Else, the upcoming July 1st is used.
1065 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
1067 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
1069 =item defaults entrydate to Now
1071 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".
1078 my $orderinfo = shift;
1079 my $parent_ordernumber = shift;
1081 my $dbh = C4::Context->dbh;
1085 # if these parameters are missing, we can't continue
1086 for my $key (qw/basketno quantity biblionumber budget_id/) {
1087 croak "Mandatory parameter $key missing" unless $orderinfo->{$key};
1090 if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) {
1091 $orderinfo->{'subscription'} = 1;
1093 $orderinfo->{'subscription'} = 0;
1095 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
1096 if (!$orderinfo->{quantityreceived}) {
1097 $orderinfo->{quantityreceived} = 0;
1100 my $ordernumber=InsertInTable("aqorders",$orderinfo);
1101 if (not $orderinfo->{parent_ordernumber}) {
1102 my $sth = $dbh->prepare("
1104 SET parent_ordernumber = ordernumber
1105 WHERE ordernumber = ?
1107 $sth->execute($ordernumber);
1109 return ( $orderinfo->{'basketno'}, $ordernumber );
1114 #------------------------------------------------------------#
1123 my ($itemnumber, $ordernumber) = @_;
1124 my $dbh = C4::Context->dbh;
1126 INSERT INTO aqorders_items
1127 (itemnumber, ordernumber)
1130 my $sth = $dbh->prepare($query);
1131 $sth->execute( $itemnumber, $ordernumber);
1134 #------------------------------------------------------------#
1138 &ModOrder(\%hashref);
1140 Modifies an existing order. Updates the order with order number
1141 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1142 other keys of the hash update the fields with the same name in the aqorders
1143 table of the Koha database.
1148 my $orderinfo = shift;
1150 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1151 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1153 my $dbh = C4::Context->dbh;
1156 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1157 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1159 # delete($orderinfo->{'branchcode'});
1160 # the hash contains a lot of entries not in aqorders, so get the columns ...
1161 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1163 my $colnames = $sth->{NAME};
1164 #FIXME Be careful. If aqorders would have columns with diacritics,
1165 #you should need to decode what you get back from NAME.
1166 #See report 10110 and guided_reports.pl
1167 my $query = "UPDATE aqorders SET ";
1169 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1170 # ... and skip hash entries that are not in the aqorders table
1171 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1172 next unless grep(/^$orderinfokey$/, @$colnames);
1173 $query .= "$orderinfokey=?, ";
1174 push(@params, $orderinfo->{$orderinfokey});
1177 $query .= "timestamp=NOW() WHERE ordernumber=?";
1178 # push(@params, $specorderinfo{'ordernumber'});
1179 push(@params, $orderinfo->{'ordernumber'} );
1180 $sth = $dbh->prepare($query);
1181 $sth->execute(@params);
1185 #------------------------------------------------------------#
1189 ModItemOrder($itemnumber, $ordernumber);
1191 Modifies the ordernumber of an item in aqorders_items.
1196 my ($itemnumber, $ordernumber) = @_;
1198 return unless ($itemnumber and $ordernumber);
1200 my $dbh = C4::Context->dbh;
1202 UPDATE aqorders_items
1204 WHERE itemnumber = ?
1206 my $sth = $dbh->prepare($query);
1207 return $sth->execute($ordernumber, $itemnumber);
1210 #------------------------------------------------------------#
1212 =head3 GetCancelledOrders
1214 my @orders = GetCancelledOrders($basketno, $orderby);
1216 Returns cancelled orders for a basket
1220 sub GetCancelledOrders {
1221 my ( $basketno, $orderby ) = @_;
1223 return () unless $basketno;
1225 my $dbh = C4::Context->dbh;
1232 aqorders_transfers.ordernumber_to AS transferred_to,
1233 aqorders_transfers.timestamp AS transferred_to_timestamp
1235 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1236 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1237 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1238 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1240 AND (datecancellationprinted IS NOT NULL
1241 AND datecancellationprinted <> '0000-00-00')
1244 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1246 $query .= " ORDER BY $orderby";
1247 my $sth = $dbh->prepare($query);
1248 $sth->execute($basketno);
1249 my $results = $sth->fetchall_arrayref( {} );
1255 #------------------------------------------------------------#
1257 =head3 ModReceiveOrder
1259 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
1260 $cost, $ecost, $invoiceid, rrp, budget_id, datereceived, \@received_itemnumbers);
1262 Updates an order, to reflect the fact that it was received, at least
1263 in part. All arguments not mentioned below update the fields with the
1264 same name in the aqorders table of the Koha database.
1266 If a partial order is received, splits the order into two.
1268 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1274 sub ModReceiveOrder {
1276 $biblionumber, $ordernumber, $quantrec, $user, $cost, $ecost,
1277 $invoiceid, $rrp, $budget_id, $datereceived, $received_items
1281 my $dbh = C4::Context->dbh;
1282 $datereceived = C4::Dates->output('iso') unless $datereceived;
1283 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1284 if ($suggestionid) {
1285 ModSuggestion( {suggestionid=>$suggestionid,
1286 STATUS=>'AVAILABLE',
1287 biblionumber=> $biblionumber}
1291 my $sth=$dbh->prepare("
1292 SELECT * FROM aqorders
1293 WHERE biblionumber=? AND aqorders.ordernumber=?");
1295 $sth->execute($biblionumber,$ordernumber);
1296 my $order = $sth->fetchrow_hashref();
1299 my $new_ordernumber = $ordernumber;
1300 if ( $order->{quantity} > $quantrec ) {
1301 # Split order line in two parts: the first is the original order line
1302 # without received items (the quantity is decreased),
1303 # the second part is a new order line with quantity=quantityrec
1304 # (entirely received)
1305 $sth=$dbh->prepare("
1309 WHERE ordernumber = ?
1312 $sth->execute($order->{quantity} - $quantrec, $ordernumber);
1316 delete $order->{'ordernumber'};
1317 $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
1318 $order->{'quantity'} = $quantrec;
1319 $order->{'quantityreceived'} = $quantrec;
1320 $order->{'datereceived'} = $datereceived;
1321 $order->{'invoiceid'} = $invoiceid;
1322 $order->{'unitprice'} = $cost;
1323 $order->{'rrp'} = $rrp;
1324 $order->{ecost} = $ecost;
1325 $order->{'orderstatus'} = 3; # totally received
1327 ( $basketno, $new_ordernumber ) = NewOrder($order);
1329 if ($received_items) {
1330 foreach my $itemnumber (@$received_items) {
1331 ModItemOrder($itemnumber, $new_ordernumber);
1335 $sth=$dbh->prepare("update aqorders
1336 set quantityreceived=?,datereceived=?,invoiceid=?,
1337 unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus=3
1338 where biblionumber=? and ordernumber=?");
1339 $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$budget_id,$biblionumber,$ordernumber);
1342 return ($datereceived, $new_ordernumber);
1345 =head3 CancelReceipt
1347 my $parent_ordernumber = CancelReceipt($ordernumber);
1349 Cancel an order line receipt and update the parent order line, as if no
1351 If items are created at receipt (AcqCreateItem = receiving) then delete
1357 my $ordernumber = shift;
1359 return unless $ordernumber;
1361 my $dbh = C4::Context->dbh;
1363 SELECT datereceived, parent_ordernumber, quantity
1365 WHERE ordernumber = ?
1367 my $sth = $dbh->prepare($query);
1368 $sth->execute($ordernumber);
1369 my $order = $sth->fetchrow_hashref;
1371 warn "CancelReceipt: order $ordernumber does not exist";
1374 unless($order->{'datereceived'}) {
1375 warn "CancelReceipt: order $ordernumber is not received";
1379 my $parent_ordernumber = $order->{'parent_ordernumber'};
1381 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1382 # The order line has no parent, just mark it as not received
1385 SET quantityreceived = ?,
1389 WHERE ordernumber = ?
1391 $sth = $dbh->prepare($query);
1392 $sth->execute(0, undef, undef, $ordernumber);
1394 # The order line has a parent, increase parent quantity and delete
1397 SELECT quantity, datereceived
1399 WHERE ordernumber = ?
1401 $sth = $dbh->prepare($query);
1402 $sth->execute($parent_ordernumber);
1403 my $parent_order = $sth->fetchrow_hashref;
1404 unless($parent_order) {
1405 warn "Parent order $parent_ordernumber does not exist.";
1408 if($parent_order->{'datereceived'}) {
1409 warn "CancelReceipt: parent order is received.".
1410 " Can't cancel receipt.";
1417 WHERE ordernumber = ?
1419 $sth = $dbh->prepare($query);
1420 my $rv = $sth->execute(
1421 $order->{'quantity'} + $parent_order->{'quantity'},
1425 warn "Cannot update parent order line, so do not cancel".
1429 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1430 # Remove items that were created at receipt
1432 DELETE FROM items, aqorders_items
1433 USING items, aqorders_items
1434 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1436 $sth = $dbh->prepare($query);
1437 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1438 foreach my $itemnumber (@itemnumbers) {
1439 $sth->execute($itemnumber, $itemnumber);
1443 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1444 foreach my $itemnumber (@itemnumbers) {
1445 ModItemOrder($itemnumber, $parent_ordernumber);
1450 DELETE FROM aqorders
1451 WHERE ordernumber = ?
1453 $sth = $dbh->prepare($query);
1454 $sth->execute($ordernumber);
1458 return $parent_ordernumber;
1461 #------------------------------------------------------------#
1465 @results = &SearchOrders({
1466 ordernumber => $ordernumber,
1468 biblionumber => $biblionumber,
1470 booksellerid => $booksellerid,
1471 basketno => $basketno,
1476 Searches for orders.
1478 C<$owner> Finds order for the logged in user.
1479 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1482 C<@results> is an array of references-to-hash with the keys are fields
1483 from aqorders, biblio, biblioitems and aqbasket tables.
1488 my ( $params ) = @_;
1489 my $ordernumber = $params->{ordernumber};
1490 my $search = $params->{search};
1491 my $ean = $params->{ean};
1492 my $booksellerid = $params->{booksellerid};
1493 my $basketno = $params->{basketno};
1494 my $basketname = $params->{basketname};
1495 my $basketgroupname = $params->{basketgroupname};
1496 my $owner = $params->{owner};
1497 my $pending = $params->{pending};
1499 my $dbh = C4::Context->dbh;
1502 SELECT aqbasket.basketno,
1504 borrowers.firstname,
1507 biblioitems.biblioitemnumber,
1509 aqbasket.creationdate,
1510 aqbasket.basketname,
1513 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1514 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1515 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1516 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1517 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1518 WHERE (datecancellationprinted is NULL)
1522 AND (quantity > quantityreceived OR quantityreceived is NULL)
1525 my $userenv = C4::Context->userenv;
1526 if ( C4::Context->preference("IndependentBranches") ) {
1527 if ( ( $userenv ) and ( $userenv->{flags} != 1 ) ) {
1530 borrowers.branchcode = ?
1531 OR borrowers.branchcode = ''
1534 push @args, $userenv->{branch};
1538 if ( $ordernumber ) {
1539 $query .= ' AND (aqorders.ordernumber=?)';
1540 push @args, $ordernumber;
1543 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1544 push @args, ("%$search%","%$search%","%$search%");
1547 $query .= ' AND biblioitems.ean = ?';
1550 if ( $booksellerid ) {
1551 $query .= 'AND aqbasket.booksellerid = ?';
1552 push @args, $booksellerid;
1555 $query .= 'AND aqbasket.basketno = ?';
1556 push @args, $basketno;
1559 $query .= 'AND aqbasket.basketname LIKE ?';
1560 push @args, "%$basketname%";
1562 if( $basketgroupname ) {
1563 $query .= ' AND aqbasketgroups.name LIKE ?';
1564 push @args, "%$basketgroupname%";
1568 $query .= ' AND aqbasket.authorisedby=? ';
1569 push @args, $userenv->{'number'};
1572 $query .= ' ORDER BY aqbasket.basketno';
1574 my $sth = $dbh->prepare($query);
1575 $sth->execute(@args);
1576 return $sth->fetchall_arrayref({});
1579 #------------------------------------------------------------#
1583 &DelOrder($biblionumber, $ordernumber);
1585 Cancel the order with the given order and biblio numbers. It does not
1586 delete any entries in the aqorders table, it merely marks them as
1592 my ( $bibnum, $ordernumber ) = @_;
1593 my $dbh = C4::Context->dbh;
1596 SET datecancellationprinted=now(), orderstatus=4
1597 WHERE biblionumber=? AND ordernumber=?
1599 my $sth = $dbh->prepare($query);
1600 $sth->execute( $bibnum, $ordernumber );
1602 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1603 foreach my $itemnumber (@itemnumbers){
1604 C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
1609 =head3 TransferOrder
1611 my $newordernumber = TransferOrder($ordernumber, $basketno);
1613 Transfer an order line to a basket.
1614 Mark $ordernumber as cancelled with an internal note 'Cancelled and transfered
1615 to BOOKSELLER on DATE' and create new order with internal note
1616 'Transfered from BOOKSELLER on DATE'.
1617 Move all attached items to the new order.
1618 Received orders cannot be transfered.
1619 Return the ordernumber of created order.
1624 my ($ordernumber, $basketno) = @_;
1626 return unless ($ordernumber and $basketno);
1628 my $order = GetOrder( $ordernumber );
1629 return if $order->{datereceived};
1630 my $basket = GetBasket($basketno);
1631 return unless $basket;
1633 my $dbh = C4::Context->dbh;
1634 my ($query, $sth, $rv);
1638 SET datecancellationprinted = CAST(NOW() AS date)
1639 WHERE ordernumber = ?
1641 $sth = $dbh->prepare($query);
1642 $rv = $sth->execute($ordernumber);
1644 delete $order->{'ordernumber'};
1645 $order->{'basketno'} = $basketno;
1647 (undef, $newordernumber) = NewOrder($order);
1650 UPDATE aqorders_items
1652 WHERE ordernumber = ?
1654 $sth = $dbh->prepare($query);
1655 $sth->execute($newordernumber, $ordernumber);
1658 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1661 $sth = $dbh->prepare($query);
1662 $sth->execute($ordernumber, $newordernumber);
1664 return $newordernumber;
1667 =head2 FUNCTIONS ABOUT PARCELS
1671 #------------------------------------------------------------#
1675 @results = &GetParcel($booksellerid, $code, $date);
1677 Looks up all of the received items from the supplier with the given
1678 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1680 C<@results> is an array of references-to-hash. The keys of each element are fields from
1681 the aqorders, biblio, and biblioitems tables of the Koha database.
1683 C<@results> is sorted alphabetically by book title.
1688 #gets all orders from a certain supplier, orders them alphabetically
1689 my ( $supplierid, $code, $datereceived ) = @_;
1690 my $dbh = C4::Context->dbh;
1693 if $code; # add % if we search on a given code (otherwise, let him empty)
1695 SELECT authorisedby,
1700 aqorders.biblionumber,
1701 aqorders.ordernumber,
1702 aqorders.parent_ordernumber,
1704 aqorders.quantityreceived,
1712 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1713 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1714 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1715 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1717 aqbasket.booksellerid = ?
1718 AND aqinvoices.invoicenumber LIKE ?
1719 AND aqorders.datereceived = ? ";
1721 my @query_params = ( $supplierid, $code, $datereceived );
1722 if ( C4::Context->preference("IndependentBranches") ) {
1723 my $userenv = C4::Context->userenv;
1724 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1725 $strsth .= " and (borrowers.branchcode = ?
1726 or borrowers.branchcode = '')";
1727 push @query_params, $userenv->{branch};
1730 $strsth .= " ORDER BY aqbasket.basketno";
1731 # ## parcelinformation : $strsth
1732 my $sth = $dbh->prepare($strsth);
1733 $sth->execute( @query_params );
1734 while ( my $data = $sth->fetchrow_hashref ) {
1735 push( @results, $data );
1737 # ## countparcelbiblio: scalar(@results)
1743 #------------------------------------------------------------#
1747 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1749 get a lists of parcels.
1756 is the bookseller this function has to get parcels.
1759 To know on what criteria the results list has to be ordered.
1762 is the booksellerinvoicenumber.
1764 =item $datefrom & $dateto
1765 to know on what date this function has to filter its search.
1770 a pointer on a hash list containing parcel informations as such :
1776 =item Last operation
1778 =item Number of biblio
1780 =item Number of items
1787 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1788 my $dbh = C4::Context->dbh;
1789 my @query_params = ();
1791 SELECT aqinvoices.invoicenumber,
1792 datereceived,purchaseordernumber,
1793 count(DISTINCT biblionumber) AS biblio,
1794 sum(quantity) AS itemsexpected,
1795 sum(quantityreceived) AS itemsreceived
1796 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1797 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1798 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1800 push @query_params, $bookseller;
1802 if ( defined $code ) {
1803 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1804 # add a % to the end of the code to allow stemming.
1805 push @query_params, "$code%";
1808 if ( defined $datefrom ) {
1809 $strsth .= ' and datereceived >= ? ';
1810 push @query_params, $datefrom;
1813 if ( defined $dateto ) {
1814 $strsth .= 'and datereceived <= ? ';
1815 push @query_params, $dateto;
1818 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1820 # can't use a placeholder to place this column name.
1821 # but, we could probably be checking to make sure it is a column that will be fetched.
1822 $strsth .= "order by $order " if ($order);
1824 my $sth = $dbh->prepare($strsth);
1826 $sth->execute( @query_params );
1827 my $results = $sth->fetchall_arrayref({});
1832 #------------------------------------------------------------#
1834 =head3 GetLateOrders
1836 @results = &GetLateOrders;
1838 Searches for bookseller with late orders.
1841 the table of supplier with late issues. This table is full of hashref.
1847 my $supplierid = shift;
1849 my $estimateddeliverydatefrom = shift;
1850 my $estimateddeliverydateto = shift;
1852 my $dbh = C4::Context->dbh;
1854 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
1855 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
1857 my @query_params = ();
1859 SELECT aqbasket.basketno,
1860 aqorders.ordernumber,
1861 DATE(aqbasket.closedate) AS orderdate,
1862 aqorders.rrp AS unitpricesupplier,
1863 aqorders.ecost AS unitpricelib,
1864 aqorders.claims_count AS claims_count,
1865 aqorders.claimed_date AS claimed_date,
1866 aqbudgets.budget_name AS budget,
1867 borrowers.branchcode AS branch,
1868 aqbooksellers.name AS supplier,
1869 aqbooksellers.id AS supplierid,
1870 biblio.author, biblio.title,
1871 biblioitems.publishercode AS publisher,
1872 biblioitems.publicationyear,
1873 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1877 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1878 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1879 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1880 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1881 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1882 WHERE aqorders.basketno = aqbasket.basketno
1883 AND ( datereceived = ''
1884 OR datereceived IS NULL
1885 OR aqorders.quantityreceived < aqorders.quantity
1887 AND aqbasket.closedate IS NOT NULL
1888 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
1891 if ($dbdriver eq "mysql") {
1893 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
1894 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1895 DATEDIFF(CAST(now() AS date),closedate) AS latesince
1897 if ( defined $delay ) {
1898 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
1899 push @query_params, $delay;
1902 HAVING quantity <> 0
1903 AND unitpricesupplier <> 0
1904 AND unitpricelib <> 0
1907 # FIXME: account for IFNULL as above
1909 aqorders.quantity AS quantity,
1910 aqorders.quantity * aqorders.rrp AS subtotal,
1911 (CAST(now() AS date) - closedate) AS latesince
1913 if ( defined $delay ) {
1914 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
1915 push @query_params, $delay;
1918 if (defined $supplierid) {
1919 $from .= ' AND aqbasket.booksellerid = ? ';
1920 push @query_params, $supplierid;
1922 if (defined $branch) {
1923 $from .= ' AND borrowers.branchcode LIKE ? ';
1924 push @query_params, $branch;
1927 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
1928 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
1930 if ( defined $estimateddeliverydatefrom ) {
1931 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
1932 push @query_params, $estimateddeliverydatefrom;
1934 if ( defined $estimateddeliverydateto ) {
1935 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
1936 push @query_params, $estimateddeliverydateto;
1938 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
1939 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
1941 if (C4::Context->preference("IndependentBranches")
1942 && C4::Context->userenv
1943 && C4::Context->userenv->{flags} != 1 ) {
1944 $from .= ' AND borrowers.branchcode LIKE ? ';
1945 push @query_params, C4::Context->userenv->{branch};
1947 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
1948 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
1949 my $sth = $dbh->prepare($query);
1950 $sth->execute(@query_params);
1952 while (my $data = $sth->fetchrow_hashref) {
1953 $data->{orderdate} = format_date($data->{orderdate});
1954 $data->{claimed_date} = format_date($data->{claimed_date});
1955 push @results, $data;
1960 #------------------------------------------------------------#
1964 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
1966 Retreives some acquisition history information
1974 basket - search both basket name and number
1975 booksellerinvoicenumber
1978 $order_loop is a list of hashrefs that each look like this:
1980 'author' => 'Twain, Mark',
1982 'biblionumber' => '215',
1984 'creationdate' => 'MM/DD/YYYY',
1985 'datereceived' => undef,
1988 'invoicenumber' => undef,
1990 'ordernumber' => '1',
1992 'quantityreceived' => undef,
1993 'title' => 'The Adventures of Huckleberry Finn'
1995 $total_qty is the sum of all of the quantities in $order_loop
1996 $total_price is the cost of each in $order_loop times the quantity
1997 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
2002 # don't run the query if there are no parameters (list would be too long for sure !)
2003 croak "No search params" unless @_;
2005 my $title = $params{title};
2006 my $author = $params{author};
2007 my $isbn = $params{isbn};
2008 my $ean = $params{ean};
2009 my $name = $params{name};
2010 my $from_placed_on = $params{from_placed_on};
2011 my $to_placed_on = $params{to_placed_on};
2012 my $basket = $params{basket};
2013 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2014 my $basketgroupname = $params{basketgroupname};
2015 my $budget = $params{budget};
2016 my $orderstatus = $params{orderstatus};
2020 my $total_qtyreceived = 0;
2021 my $total_price = 0;
2023 my $dbh = C4::Context->dbh;
2026 COALESCE(biblio.title, deletedbiblio.title) AS title,
2027 COALESCE(biblio.author, deletedbiblio.author) AS author,
2028 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2029 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2031 aqbasket.basketname,
2032 aqbasket.basketgroupid,
2033 aqbasketgroups.name as groupname,
2035 aqbasket.creationdate,
2036 aqorders.datereceived,
2038 aqorders.quantityreceived,
2040 aqorders.ordernumber,
2042 aqinvoices.invoicenumber,
2043 aqbooksellers.id as id,
2044 aqorders.biblionumber,
2045 aqorders.orderstatus,
2046 aqorders.parent_ordernumber,
2047 aqbudgets.budget_name
2049 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2052 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2053 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2054 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2055 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2056 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2057 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2058 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2059 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2060 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2063 if ( C4::Context->preference("IndependentBranches") ) {
2064 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber";
2067 $query .= " WHERE 1 ";
2069 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') " if $orderstatus ne '4';
2071 my @query_params = ();
2074 $query .= " AND biblio.title LIKE ? ";
2075 $title =~ s/\s+/%/g;
2076 push @query_params, "%$title%";
2080 $query .= " AND biblio.author LIKE ? ";
2081 push @query_params, "%$author%";
2085 $query .= " AND biblioitems.isbn LIKE ? ";
2086 push @query_params, "%$isbn%";
2089 $query .= " AND biblioitems.ean = ? ";
2090 push @query_params, "$ean";
2093 $query .= " AND aqbooksellers.name LIKE ? ";
2094 push @query_params, "%$name%";
2098 $query .= " AND aqbudgets.budget_id = ? ";
2099 push @query_params, "$budget";
2102 if ( $from_placed_on ) {
2103 $query .= " AND creationdate >= ? ";
2104 push @query_params, $from_placed_on;
2107 if ( $to_placed_on ) {
2108 $query .= " AND creationdate <= ? ";
2109 push @query_params, $to_placed_on;
2112 if ( defined $orderstatus and $orderstatus ne '') {
2113 $query .= " AND aqorders.orderstatus = ? ";
2114 push @query_params, "$orderstatus";
2118 if ($basket =~ m/^\d+$/) {
2119 $query .= " AND aqorders.basketno = ? ";
2120 push @query_params, $basket;
2122 $query .= " AND aqbasket.basketname LIKE ? ";
2123 push @query_params, "%$basket%";
2127 if ($booksellerinvoicenumber) {
2128 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2129 push @query_params, "%$booksellerinvoicenumber%";
2132 if ($basketgroupname) {
2133 $query .= " AND aqbasketgroups.name LIKE ? ";
2134 push @query_params, "%$basketgroupname%";
2137 if ( C4::Context->preference("IndependentBranches") ) {
2138 my $userenv = C4::Context->userenv;
2139 if ( $userenv && ($userenv->{flags} || 0) != 1 ) {
2140 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2141 push @query_params, $userenv->{branch};
2144 $query .= " ORDER BY id";
2145 my $sth = $dbh->prepare($query);
2146 $sth->execute( @query_params );
2148 while ( my $line = $sth->fetchrow_hashref ) {
2149 $line->{count} = $cnt++;
2150 $line->{toggle} = 1 if $cnt % 2;
2151 push @order_loop, $line;
2152 $total_qty += ( $line->{quantity} ) ? $line->{quantity} : 0;
2153 $total_qtyreceived += ( $line->{quantityreceived} ) ? $line->{quantityreceived} : 0;
2154 $total_price += ( $line->{quantity} and $line->{ecost} ) ? $line->{quantity} * $line->{ecost} : 0;
2156 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
2159 =head2 GetRecentAcqui
2161 $results = GetRecentAcqui($days);
2163 C<$results> is a ref to a table which containts hashref
2167 sub GetRecentAcqui {
2169 my $dbh = C4::Context->dbh;
2173 ORDER BY timestamp DESC
2176 my $sth = $dbh->prepare($query);
2178 my $results = $sth->fetchall_arrayref({});
2184 $contractlist = &GetContracts($booksellerid, $activeonly);
2186 Looks up the contracts that belong to a bookseller
2188 Returns a list of contracts
2192 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
2194 =item C<$activeonly> if exists get only contracts that are still active.
2201 my ( $booksellerid, $activeonly ) = @_;
2202 my $dbh = C4::Context->dbh;
2204 if (! $activeonly) {
2208 WHERE booksellerid=?
2213 WHERE booksellerid=?
2214 AND contractenddate >= CURDATE( )";
2216 my $sth = $dbh->prepare($query);
2217 $sth->execute( $booksellerid );
2219 while (my $data = $sth->fetchrow_hashref ) {
2220 push(@results, $data);
2226 #------------------------------------------------------------#
2230 $contract = &GetContract($contractID);
2232 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
2239 my ( $contractno ) = @_;
2240 my $dbh = C4::Context->dbh;
2244 WHERE contractnumber=?
2247 my $sth = $dbh->prepare($query);
2248 $sth->execute( $contractno );
2249 my $result = $sth->fetchrow_hashref;
2257 &AddClaim($ordernumber);
2259 Add a claim for an order
2265 my ($ordernumber) = @_;
2266 my $dbh = C4::Context->dbh;
2269 claims_count = claims_count + 1,
2270 claimed_date = CURDATE()
2271 WHERE ordernumber = ?
2273 my $sth = $dbh->prepare($query);
2274 $sth->execute($ordernumber);
2279 my @invoices = GetInvoices(
2280 invoicenumber => $invoicenumber,
2281 suppliername => $suppliername,
2282 shipmentdatefrom => $shipmentdatefrom, # ISO format
2283 shipmentdateto => $shipmentdateto, # ISO format
2284 billingdatefrom => $billingdatefrom, # ISO format
2285 billingdateto => $billingdateto, # ISO format
2286 isbneanissn => $isbn_or_ean_or_issn,
2289 publisher => $publisher,
2290 publicationyear => $publicationyear,
2291 branchcode => $branchcode,
2292 order_by => $order_by
2295 Return a list of invoices that match all given criteria.
2297 $order_by is "column_name (asc|desc)", where column_name is any of
2298 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2299 'shipmentcost', 'shipmentcost_budgetid'.
2301 asc is the default if omitted
2308 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2309 closedate shipmentcost shipmentcost_budgetid);
2311 my $dbh = C4::Context->dbh;
2313 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2316 aqorders.datereceived IS NOT NULL,
2317 aqorders.biblionumber,
2320 ) AS receivedbiblios,
2321 SUM(aqorders.quantityreceived) AS receiveditems
2323 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2324 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2325 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2326 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2327 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2332 if($args{supplierid}) {
2333 push @bind_strs, " aqinvoices.booksellerid = ? ";
2334 push @bind_args, $args{supplierid};
2336 if($args{invoicenumber}) {
2337 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2338 push @bind_args, "%$args{invoicenumber}%";
2340 if($args{suppliername}) {
2341 push @bind_strs, " aqbooksellers.name LIKE ? ";
2342 push @bind_args, "%$args{suppliername}%";
2344 if($args{shipmentdatefrom}) {
2345 push @bind_strs, " aqinvoices.shipementdate >= ? ";
2346 push @bind_args, $args{shipmentdatefrom};
2348 if($args{shipmentdateto}) {
2349 push @bind_strs, " aqinvoices.shipementdate <= ? ";
2350 push @bind_args, $args{shipmentdateto};
2352 if($args{billingdatefrom}) {
2353 push @bind_strs, " aqinvoices.billingdate >= ? ";
2354 push @bind_args, $args{billingdatefrom};
2356 if($args{billingdateto}) {
2357 push @bind_strs, " aqinvoices.billingdate <= ? ";
2358 push @bind_args, $args{billingdateto};
2360 if($args{isbneanissn}) {
2361 push @bind_strs, " (biblioitems.isbn LIKE ? OR biblioitems.ean LIKE ? OR biblioitems.issn LIKE ? ) ";
2362 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2365 push @bind_strs, " biblio.title LIKE ? ";
2366 push @bind_args, $args{title};
2369 push @bind_strs, " biblio.author LIKE ? ";
2370 push @bind_args, $args{author};
2372 if($args{publisher}) {
2373 push @bind_strs, " biblioitems.publishercode LIKE ? ";
2374 push @bind_args, $args{publisher};
2376 if($args{publicationyear}) {
2377 push @bind_strs, " biblioitems.publicationyear = ? ";
2378 push @bind_args, $args{publicationyear};
2380 if($args{branchcode}) {
2381 push @bind_strs, " aqorders.branchcode = ? ";
2382 push @bind_args, $args{branchcode};
2385 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2386 $query .= " GROUP BY aqinvoices.invoiceid ";
2388 if($args{order_by}) {
2389 my ($column, $direction) = split / /, $args{order_by};
2390 if(grep /^$column$/, @columns) {
2391 $direction ||= 'ASC';
2392 $query .= " ORDER BY $column $direction";
2396 my $sth = $dbh->prepare($query);
2397 $sth->execute(@bind_args);
2399 my $results = $sth->fetchall_arrayref({});
2405 my $invoice = GetInvoice($invoiceid);
2407 Get informations about invoice with given $invoiceid
2409 Return a hash filled with aqinvoices.* fields
2414 my ($invoiceid) = @_;
2417 return unless $invoiceid;
2419 my $dbh = C4::Context->dbh;
2425 my $sth = $dbh->prepare($query);
2426 $sth->execute($invoiceid);
2428 $invoice = $sth->fetchrow_hashref;
2432 =head3 GetInvoiceDetails
2434 my $invoice = GetInvoiceDetails($invoiceid)
2436 Return informations about an invoice + the list of related order lines
2438 Orders informations are in $invoice->{orders} (array ref)
2442 sub GetInvoiceDetails {
2443 my ($invoiceid) = @_;
2445 if ( !defined $invoiceid ) {
2446 carp 'GetInvoiceDetails called without an invoiceid';
2450 my $dbh = C4::Context->dbh;
2452 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2454 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2457 my $sth = $dbh->prepare($query);
2458 $sth->execute($invoiceid);
2460 my $invoice = $sth->fetchrow_hashref;
2463 SELECT aqorders.*, biblio.*,
2466 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2467 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2470 $sth = $dbh->prepare($query);
2471 $sth->execute($invoiceid);
2472 $invoice->{orders} = $sth->fetchall_arrayref({});
2473 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2480 my $invoiceid = AddInvoice(
2481 invoicenumber => $invoicenumber,
2482 booksellerid => $booksellerid,
2483 shipmentdate => $shipmentdate,
2484 billingdate => $billingdate,
2485 closedate => $closedate,
2486 shipmentcost => $shipmentcost,
2487 shipmentcost_budgetid => $shipmentcost_budgetid
2490 Create a new invoice and return its id or undef if it fails.
2497 return unless(%invoice and $invoice{invoicenumber});
2499 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2500 closedate shipmentcost shipmentcost_budgetid);
2504 foreach my $key (keys %invoice) {
2505 if(0 < grep(/^$key$/, @columns)) {
2506 push @set_strs, "$key = ?";
2507 push @set_args, ($invoice{$key} || undef);
2513 my $dbh = C4::Context->dbh;
2514 my $query = "INSERT INTO aqinvoices SET ";
2515 $query .= join (",", @set_strs);
2516 my $sth = $dbh->prepare($query);
2517 $rv = $sth->execute(@set_args);
2519 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2528 invoiceid => $invoiceid, # Mandatory
2529 invoicenumber => $invoicenumber,
2530 booksellerid => $booksellerid,
2531 shipmentdate => $shipmentdate,
2532 billingdate => $billingdate,
2533 closedate => $closedate,
2534 shipmentcost => $shipmentcost,
2535 shipmentcost_budgetid => $shipmentcost_budgetid
2538 Modify an invoice, invoiceid is mandatory.
2540 Return undef if it fails.
2547 return unless(%invoice and $invoice{invoiceid});
2549 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2550 closedate shipmentcost shipmentcost_budgetid);
2554 foreach my $key (keys %invoice) {
2555 if(0 < grep(/^$key$/, @columns)) {
2556 push @set_strs, "$key = ?";
2557 push @set_args, ($invoice{$key} || undef);
2561 my $dbh = C4::Context->dbh;
2562 my $query = "UPDATE aqinvoices SET ";
2563 $query .= join(",", @set_strs);
2564 $query .= " WHERE invoiceid = ?";
2566 my $sth = $dbh->prepare($query);
2567 $sth->execute(@set_args, $invoice{invoiceid});
2572 CloseInvoice($invoiceid);
2576 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2581 my ($invoiceid) = @_;
2583 return unless $invoiceid;
2585 my $dbh = C4::Context->dbh;
2588 SET closedate = CAST(NOW() AS DATE)
2591 my $sth = $dbh->prepare($query);
2592 $sth->execute($invoiceid);
2595 =head3 ReopenInvoice
2597 ReopenInvoice($invoiceid);
2601 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2606 my ($invoiceid) = @_;
2608 return unless $invoiceid;
2610 my $dbh = C4::Context->dbh;
2613 SET closedate = NULL
2616 my $sth = $dbh->prepare($query);
2617 $sth->execute($invoiceid);
2622 DelInvoice($invoiceid);
2624 Delete an invoice if there are no items attached to it.
2629 my ($invoiceid) = @_;
2631 return unless $invoiceid;
2633 my $dbh = C4::Context->dbh;
2639 my $sth = $dbh->prepare($query);
2640 $sth->execute($invoiceid);
2641 my $res = $sth->fetchrow_arrayref;
2642 if ( $res && $res->[0] == 0 ) {
2644 DELETE FROM aqinvoices
2647 my $sth = $dbh->prepare($query);
2648 return ( $sth->execute($invoiceid) > 0 );
2653 =head3 MergeInvoices
2655 MergeInvoices($invoiceid, \@sourceids);
2657 Merge the invoices identified by the IDs in \@sourceids into
2658 the invoice identified by $invoiceid.
2663 my ($invoiceid, $sourceids) = @_;
2665 return unless $invoiceid;
2666 foreach my $sourceid (@$sourceids) {
2667 next if $sourceid == $invoiceid;
2668 my $source = GetInvoiceDetails($sourceid);
2669 foreach my $order (@{$source->{'orders'}}) {
2670 $order->{'invoiceid'} = $invoiceid;
2673 DelInvoice($source->{'invoiceid'});
2678 =head3 GetBiblioCountByBasketno
2680 $biblio_count = &GetBiblioCountByBasketno($basketno);
2682 Looks up the biblio's count that has basketno value $basketno
2688 sub GetBiblioCountByBasketno {
2689 my ($basketno) = @_;
2690 my $dbh = C4::Context->dbh;
2692 SELECT COUNT( DISTINCT( biblionumber ) )
2695 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2698 my $sth = $dbh->prepare($query);
2699 $sth->execute($basketno);
2700 return $sth->fetchrow;
2708 Koha Development Team <http://koha-community.org/>