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 $from .= " AND orderstatus <> 4 ";
1948 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
1949 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
1950 my $sth = $dbh->prepare($query);
1951 $sth->execute(@query_params);
1953 while (my $data = $sth->fetchrow_hashref) {
1954 $data->{orderdate} = format_date($data->{orderdate});
1955 $data->{claimed_date} = format_date($data->{claimed_date});
1956 push @results, $data;
1961 #------------------------------------------------------------#
1965 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
1967 Retreives some acquisition history information
1975 basket - search both basket name and number
1976 booksellerinvoicenumber
1979 $order_loop is a list of hashrefs that each look like this:
1981 'author' => 'Twain, Mark',
1983 'biblionumber' => '215',
1985 'creationdate' => 'MM/DD/YYYY',
1986 'datereceived' => undef,
1989 'invoicenumber' => undef,
1991 'ordernumber' => '1',
1993 'quantityreceived' => undef,
1994 'title' => 'The Adventures of Huckleberry Finn'
1996 $total_qty is the sum of all of the quantities in $order_loop
1997 $total_price is the cost of each in $order_loop times the quantity
1998 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
2003 # don't run the query if there are no parameters (list would be too long for sure !)
2004 croak "No search params" unless @_;
2006 my $title = $params{title};
2007 my $author = $params{author};
2008 my $isbn = $params{isbn};
2009 my $ean = $params{ean};
2010 my $name = $params{name};
2011 my $from_placed_on = $params{from_placed_on};
2012 my $to_placed_on = $params{to_placed_on};
2013 my $basket = $params{basket};
2014 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2015 my $basketgroupname = $params{basketgroupname};
2016 my $budget = $params{budget};
2017 my $orderstatus = $params{orderstatus};
2021 my $total_qtyreceived = 0;
2022 my $total_price = 0;
2024 my $dbh = C4::Context->dbh;
2027 COALESCE(biblio.title, deletedbiblio.title) AS title,
2028 COALESCE(biblio.author, deletedbiblio.author) AS author,
2029 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2030 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2032 aqbasket.basketname,
2033 aqbasket.basketgroupid,
2034 aqbasketgroups.name as groupname,
2036 aqbasket.creationdate,
2037 aqorders.datereceived,
2039 aqorders.quantityreceived,
2041 aqorders.ordernumber,
2043 aqinvoices.invoicenumber,
2044 aqbooksellers.id as id,
2045 aqorders.biblionumber,
2046 aqorders.orderstatus,
2047 aqorders.parent_ordernumber,
2048 aqbudgets.budget_name
2050 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2053 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2054 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2055 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2056 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2057 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2058 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2059 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2060 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2061 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2064 if ( C4::Context->preference("IndependentBranches") ) {
2065 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber";
2068 $query .= " WHERE 1 ";
2070 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') " if $orderstatus ne '4';
2072 my @query_params = ();
2075 $query .= " AND biblio.title LIKE ? ";
2076 $title =~ s/\s+/%/g;
2077 push @query_params, "%$title%";
2081 $query .= " AND biblio.author LIKE ? ";
2082 push @query_params, "%$author%";
2086 $query .= " AND biblioitems.isbn LIKE ? ";
2087 push @query_params, "%$isbn%";
2090 $query .= " AND biblioitems.ean = ? ";
2091 push @query_params, "$ean";
2094 $query .= " AND aqbooksellers.name LIKE ? ";
2095 push @query_params, "%$name%";
2099 $query .= " AND aqbudgets.budget_id = ? ";
2100 push @query_params, "$budget";
2103 if ( $from_placed_on ) {
2104 $query .= " AND creationdate >= ? ";
2105 push @query_params, $from_placed_on;
2108 if ( $to_placed_on ) {
2109 $query .= " AND creationdate <= ? ";
2110 push @query_params, $to_placed_on;
2113 if ( defined $orderstatus and $orderstatus ne '') {
2114 $query .= " AND aqorders.orderstatus = ? ";
2115 push @query_params, "$orderstatus";
2119 if ($basket =~ m/^\d+$/) {
2120 $query .= " AND aqorders.basketno = ? ";
2121 push @query_params, $basket;
2123 $query .= " AND aqbasket.basketname LIKE ? ";
2124 push @query_params, "%$basket%";
2128 if ($booksellerinvoicenumber) {
2129 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2130 push @query_params, "%$booksellerinvoicenumber%";
2133 if ($basketgroupname) {
2134 $query .= " AND aqbasketgroups.name LIKE ? ";
2135 push @query_params, "%$basketgroupname%";
2138 if ( C4::Context->preference("IndependentBranches") ) {
2139 my $userenv = C4::Context->userenv;
2140 if ( $userenv && ($userenv->{flags} || 0) != 1 ) {
2141 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2142 push @query_params, $userenv->{branch};
2145 $query .= " ORDER BY id";
2146 my $sth = $dbh->prepare($query);
2147 $sth->execute( @query_params );
2149 while ( my $line = $sth->fetchrow_hashref ) {
2150 $line->{count} = $cnt++;
2151 $line->{toggle} = 1 if $cnt % 2;
2152 push @order_loop, $line;
2153 $total_qty += ( $line->{quantity} ) ? $line->{quantity} : 0;
2154 $total_qtyreceived += ( $line->{quantityreceived} ) ? $line->{quantityreceived} : 0;
2155 $total_price += ( $line->{quantity} and $line->{ecost} ) ? $line->{quantity} * $line->{ecost} : 0;
2157 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
2160 =head2 GetRecentAcqui
2162 $results = GetRecentAcqui($days);
2164 C<$results> is a ref to a table which containts hashref
2168 sub GetRecentAcqui {
2170 my $dbh = C4::Context->dbh;
2174 ORDER BY timestamp DESC
2177 my $sth = $dbh->prepare($query);
2179 my $results = $sth->fetchall_arrayref({});
2185 $contractlist = &GetContracts($booksellerid, $activeonly);
2187 Looks up the contracts that belong to a bookseller
2189 Returns a list of contracts
2193 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
2195 =item C<$activeonly> if exists get only contracts that are still active.
2202 my ( $booksellerid, $activeonly ) = @_;
2203 my $dbh = C4::Context->dbh;
2205 if (! $activeonly) {
2209 WHERE booksellerid=?
2214 WHERE booksellerid=?
2215 AND contractenddate >= CURDATE( )";
2217 my $sth = $dbh->prepare($query);
2218 $sth->execute( $booksellerid );
2220 while (my $data = $sth->fetchrow_hashref ) {
2221 push(@results, $data);
2227 #------------------------------------------------------------#
2231 $contract = &GetContract($contractID);
2233 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
2240 my ( $contractno ) = @_;
2241 my $dbh = C4::Context->dbh;
2245 WHERE contractnumber=?
2248 my $sth = $dbh->prepare($query);
2249 $sth->execute( $contractno );
2250 my $result = $sth->fetchrow_hashref;
2258 &AddClaim($ordernumber);
2260 Add a claim for an order
2266 my ($ordernumber) = @_;
2267 my $dbh = C4::Context->dbh;
2270 claims_count = claims_count + 1,
2271 claimed_date = CURDATE()
2272 WHERE ordernumber = ?
2274 my $sth = $dbh->prepare($query);
2275 $sth->execute($ordernumber);
2280 my @invoices = GetInvoices(
2281 invoicenumber => $invoicenumber,
2282 suppliername => $suppliername,
2283 shipmentdatefrom => $shipmentdatefrom, # ISO format
2284 shipmentdateto => $shipmentdateto, # ISO format
2285 billingdatefrom => $billingdatefrom, # ISO format
2286 billingdateto => $billingdateto, # ISO format
2287 isbneanissn => $isbn_or_ean_or_issn,
2290 publisher => $publisher,
2291 publicationyear => $publicationyear,
2292 branchcode => $branchcode,
2293 order_by => $order_by
2296 Return a list of invoices that match all given criteria.
2298 $order_by is "column_name (asc|desc)", where column_name is any of
2299 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2300 'shipmentcost', 'shipmentcost_budgetid'.
2302 asc is the default if omitted
2309 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2310 closedate shipmentcost shipmentcost_budgetid);
2312 my $dbh = C4::Context->dbh;
2314 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2317 aqorders.datereceived IS NOT NULL,
2318 aqorders.biblionumber,
2321 ) AS receivedbiblios,
2322 SUM(aqorders.quantityreceived) AS receiveditems
2324 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2325 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2326 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2327 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2328 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2333 if($args{supplierid}) {
2334 push @bind_strs, " aqinvoices.booksellerid = ? ";
2335 push @bind_args, $args{supplierid};
2337 if($args{invoicenumber}) {
2338 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2339 push @bind_args, "%$args{invoicenumber}%";
2341 if($args{suppliername}) {
2342 push @bind_strs, " aqbooksellers.name LIKE ? ";
2343 push @bind_args, "%$args{suppliername}%";
2345 if($args{shipmentdatefrom}) {
2346 push @bind_strs, " aqinvoices.shipementdate >= ? ";
2347 push @bind_args, $args{shipmentdatefrom};
2349 if($args{shipmentdateto}) {
2350 push @bind_strs, " aqinvoices.shipementdate <= ? ";
2351 push @bind_args, $args{shipmentdateto};
2353 if($args{billingdatefrom}) {
2354 push @bind_strs, " aqinvoices.billingdate >= ? ";
2355 push @bind_args, $args{billingdatefrom};
2357 if($args{billingdateto}) {
2358 push @bind_strs, " aqinvoices.billingdate <= ? ";
2359 push @bind_args, $args{billingdateto};
2361 if($args{isbneanissn}) {
2362 push @bind_strs, " (biblioitems.isbn LIKE ? OR biblioitems.ean LIKE ? OR biblioitems.issn LIKE ? ) ";
2363 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2366 push @bind_strs, " biblio.title LIKE ? ";
2367 push @bind_args, $args{title};
2370 push @bind_strs, " biblio.author LIKE ? ";
2371 push @bind_args, $args{author};
2373 if($args{publisher}) {
2374 push @bind_strs, " biblioitems.publishercode LIKE ? ";
2375 push @bind_args, $args{publisher};
2377 if($args{publicationyear}) {
2378 push @bind_strs, " biblioitems.publicationyear = ? ";
2379 push @bind_args, $args{publicationyear};
2381 if($args{branchcode}) {
2382 push @bind_strs, " aqorders.branchcode = ? ";
2383 push @bind_args, $args{branchcode};
2386 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2387 $query .= " GROUP BY aqinvoices.invoiceid ";
2389 if($args{order_by}) {
2390 my ($column, $direction) = split / /, $args{order_by};
2391 if(grep /^$column$/, @columns) {
2392 $direction ||= 'ASC';
2393 $query .= " ORDER BY $column $direction";
2397 my $sth = $dbh->prepare($query);
2398 $sth->execute(@bind_args);
2400 my $results = $sth->fetchall_arrayref({});
2406 my $invoice = GetInvoice($invoiceid);
2408 Get informations about invoice with given $invoiceid
2410 Return a hash filled with aqinvoices.* fields
2415 my ($invoiceid) = @_;
2418 return unless $invoiceid;
2420 my $dbh = C4::Context->dbh;
2426 my $sth = $dbh->prepare($query);
2427 $sth->execute($invoiceid);
2429 $invoice = $sth->fetchrow_hashref;
2433 =head3 GetInvoiceDetails
2435 my $invoice = GetInvoiceDetails($invoiceid)
2437 Return informations about an invoice + the list of related order lines
2439 Orders informations are in $invoice->{orders} (array ref)
2443 sub GetInvoiceDetails {
2444 my ($invoiceid) = @_;
2446 if ( !defined $invoiceid ) {
2447 carp 'GetInvoiceDetails called without an invoiceid';
2451 my $dbh = C4::Context->dbh;
2453 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2455 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2458 my $sth = $dbh->prepare($query);
2459 $sth->execute($invoiceid);
2461 my $invoice = $sth->fetchrow_hashref;
2464 SELECT aqorders.*, biblio.*,
2467 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2468 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2471 $sth = $dbh->prepare($query);
2472 $sth->execute($invoiceid);
2473 $invoice->{orders} = $sth->fetchall_arrayref({});
2474 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2481 my $invoiceid = AddInvoice(
2482 invoicenumber => $invoicenumber,
2483 booksellerid => $booksellerid,
2484 shipmentdate => $shipmentdate,
2485 billingdate => $billingdate,
2486 closedate => $closedate,
2487 shipmentcost => $shipmentcost,
2488 shipmentcost_budgetid => $shipmentcost_budgetid
2491 Create a new invoice and return its id or undef if it fails.
2498 return unless(%invoice and $invoice{invoicenumber});
2500 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2501 closedate shipmentcost shipmentcost_budgetid);
2505 foreach my $key (keys %invoice) {
2506 if(0 < grep(/^$key$/, @columns)) {
2507 push @set_strs, "$key = ?";
2508 push @set_args, ($invoice{$key} || undef);
2514 my $dbh = C4::Context->dbh;
2515 my $query = "INSERT INTO aqinvoices SET ";
2516 $query .= join (",", @set_strs);
2517 my $sth = $dbh->prepare($query);
2518 $rv = $sth->execute(@set_args);
2520 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2529 invoiceid => $invoiceid, # Mandatory
2530 invoicenumber => $invoicenumber,
2531 booksellerid => $booksellerid,
2532 shipmentdate => $shipmentdate,
2533 billingdate => $billingdate,
2534 closedate => $closedate,
2535 shipmentcost => $shipmentcost,
2536 shipmentcost_budgetid => $shipmentcost_budgetid
2539 Modify an invoice, invoiceid is mandatory.
2541 Return undef if it fails.
2548 return unless(%invoice and $invoice{invoiceid});
2550 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2551 closedate shipmentcost shipmentcost_budgetid);
2555 foreach my $key (keys %invoice) {
2556 if(0 < grep(/^$key$/, @columns)) {
2557 push @set_strs, "$key = ?";
2558 push @set_args, ($invoice{$key} || undef);
2562 my $dbh = C4::Context->dbh;
2563 my $query = "UPDATE aqinvoices SET ";
2564 $query .= join(",", @set_strs);
2565 $query .= " WHERE invoiceid = ?";
2567 my $sth = $dbh->prepare($query);
2568 $sth->execute(@set_args, $invoice{invoiceid});
2573 CloseInvoice($invoiceid);
2577 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2582 my ($invoiceid) = @_;
2584 return unless $invoiceid;
2586 my $dbh = C4::Context->dbh;
2589 SET closedate = CAST(NOW() AS DATE)
2592 my $sth = $dbh->prepare($query);
2593 $sth->execute($invoiceid);
2596 =head3 ReopenInvoice
2598 ReopenInvoice($invoiceid);
2602 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2607 my ($invoiceid) = @_;
2609 return unless $invoiceid;
2611 my $dbh = C4::Context->dbh;
2614 SET closedate = NULL
2617 my $sth = $dbh->prepare($query);
2618 $sth->execute($invoiceid);
2623 DelInvoice($invoiceid);
2625 Delete an invoice if there are no items attached to it.
2630 my ($invoiceid) = @_;
2632 return unless $invoiceid;
2634 my $dbh = C4::Context->dbh;
2640 my $sth = $dbh->prepare($query);
2641 $sth->execute($invoiceid);
2642 my $res = $sth->fetchrow_arrayref;
2643 if ( $res && $res->[0] == 0 ) {
2645 DELETE FROM aqinvoices
2648 my $sth = $dbh->prepare($query);
2649 return ( $sth->execute($invoiceid) > 0 );
2654 =head3 MergeInvoices
2656 MergeInvoices($invoiceid, \@sourceids);
2658 Merge the invoices identified by the IDs in \@sourceids into
2659 the invoice identified by $invoiceid.
2664 my ($invoiceid, $sourceids) = @_;
2666 return unless $invoiceid;
2667 foreach my $sourceid (@$sourceids) {
2668 next if $sourceid == $invoiceid;
2669 my $source = GetInvoiceDetails($sourceid);
2670 foreach my $order (@{$source->{'orders'}}) {
2671 $order->{'invoiceid'} = $invoiceid;
2674 DelInvoice($source->{'invoiceid'});
2679 =head3 GetBiblioCountByBasketno
2681 $biblio_count = &GetBiblioCountByBasketno($basketno);
2683 Looks up the biblio's count that has basketno value $basketno
2689 sub GetBiblioCountByBasketno {
2690 my ($basketno) = @_;
2691 my $dbh = C4::Context->dbh;
2693 SELECT COUNT( DISTINCT( biblionumber ) )
2696 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2699 my $sth = $dbh->prepare($query);
2700 $sth->execute($basketno);
2701 return $sth->fetchrow;
2709 Koha Development Team <http://koha-community.org/>