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) {
239 SET orderstatus = 'ordered'
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) {
270 SET orderstatus = 'new'
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;
1080 my $dbh = C4::Context->dbh;
1084 # if these parameters are missing, we can't continue
1085 for my $key (qw/basketno quantity biblionumber budget_id/) {
1086 croak "Mandatory parameter $key missing" unless $orderinfo->{$key};
1089 if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) {
1090 $orderinfo->{'subscription'} = 1;
1092 $orderinfo->{'subscription'} = 0;
1094 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
1095 if (!$orderinfo->{quantityreceived}) {
1096 $orderinfo->{quantityreceived} = 0;
1099 my $ordernumber=InsertInTable("aqorders",$orderinfo);
1100 if (not $orderinfo->{parent_ordernumber}) {
1101 my $sth = $dbh->prepare("
1103 SET parent_ordernumber = ordernumber
1104 WHERE ordernumber = ?
1106 $sth->execute($ordernumber);
1108 return ( $orderinfo->{'basketno'}, $ordernumber );
1113 #------------------------------------------------------------#
1122 my ($itemnumber, $ordernumber) = @_;
1123 my $dbh = C4::Context->dbh;
1125 INSERT INTO aqorders_items
1126 (itemnumber, ordernumber)
1129 my $sth = $dbh->prepare($query);
1130 $sth->execute( $itemnumber, $ordernumber);
1133 #------------------------------------------------------------#
1137 &ModOrder(\%hashref);
1139 Modifies an existing order. Updates the order with order number
1140 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1141 other keys of the hash update the fields with the same name in the aqorders
1142 table of the Koha database.
1147 my $orderinfo = shift;
1149 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1150 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1152 my $dbh = C4::Context->dbh;
1155 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1156 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1158 # delete($orderinfo->{'branchcode'});
1159 # the hash contains a lot of entries not in aqorders, so get the columns ...
1160 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1162 my $colnames = $sth->{NAME};
1163 #FIXME Be careful. If aqorders would have columns with diacritics,
1164 #you should need to decode what you get back from NAME.
1165 #See report 10110 and guided_reports.pl
1166 my $query = "UPDATE aqorders SET ";
1168 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1169 # ... and skip hash entries that are not in the aqorders table
1170 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1171 next unless grep(/^$orderinfokey$/, @$colnames);
1172 $query .= "$orderinfokey=?, ";
1173 push(@params, $orderinfo->{$orderinfokey});
1176 $query .= "timestamp=NOW() WHERE ordernumber=?";
1177 # push(@params, $specorderinfo{'ordernumber'});
1178 push(@params, $orderinfo->{'ordernumber'} );
1179 $sth = $dbh->prepare($query);
1180 $sth->execute(@params);
1184 #------------------------------------------------------------#
1188 ModItemOrder($itemnumber, $ordernumber);
1190 Modifies the ordernumber of an item in aqorders_items.
1195 my ($itemnumber, $ordernumber) = @_;
1197 return unless ($itemnumber and $ordernumber);
1199 my $dbh = C4::Context->dbh;
1201 UPDATE aqorders_items
1203 WHERE itemnumber = ?
1205 my $sth = $dbh->prepare($query);
1206 return $sth->execute($ordernumber, $itemnumber);
1209 #------------------------------------------------------------#
1211 =head3 GetCancelledOrders
1213 my @orders = GetCancelledOrders($basketno, $orderby);
1215 Returns cancelled orders for a basket
1219 sub GetCancelledOrders {
1220 my ( $basketno, $orderby ) = @_;
1222 return () unless $basketno;
1224 my $dbh = C4::Context->dbh;
1231 aqorders_transfers.ordernumber_to AS transferred_to,
1232 aqorders_transfers.timestamp AS transferred_to_timestamp
1234 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1235 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1236 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1237 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1239 AND (datecancellationprinted IS NOT NULL
1240 AND datecancellationprinted <> '0000-00-00')
1243 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1245 $query .= " ORDER BY $orderby";
1246 my $sth = $dbh->prepare($query);
1247 $sth->execute($basketno);
1248 my $results = $sth->fetchall_arrayref( {} );
1254 #------------------------------------------------------------#
1256 =head3 ModReceiveOrder
1258 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
1259 $cost, $ecost, $invoiceid, rrp, budget_id, datereceived, \@received_itemnumbers);
1261 Updates an order, to reflect the fact that it was received, at least
1262 in part. All arguments not mentioned below update the fields with the
1263 same name in the aqorders table of the Koha database.
1265 If a partial order is received, splits the order into two.
1267 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1273 sub ModReceiveOrder {
1275 $biblionumber, $ordernumber, $quantrec, $user, $cost, $ecost,
1276 $invoiceid, $rrp, $budget_id, $datereceived, $received_items
1280 my $dbh = C4::Context->dbh;
1281 $datereceived = C4::Dates->output('iso') unless $datereceived;
1282 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1283 if ($suggestionid) {
1284 ModSuggestion( {suggestionid=>$suggestionid,
1285 STATUS=>'AVAILABLE',
1286 biblionumber=> $biblionumber}
1290 my $sth=$dbh->prepare("
1291 SELECT * FROM aqorders
1292 WHERE biblionumber=? AND aqorders.ordernumber=?");
1294 $sth->execute($biblionumber,$ordernumber);
1295 my $order = $sth->fetchrow_hashref();
1298 my $new_ordernumber = $ordernumber;
1299 if ( $order->{quantity} > $quantrec ) {
1300 # Split order line in two parts: the first is the original order line
1301 # without received items (the quantity is decreased),
1302 # the second part is a new order line with quantity=quantityrec
1303 # (entirely received)
1304 $sth=$dbh->prepare("
1307 orderstatus = 'partial'
1308 WHERE ordernumber = ?
1311 $sth->execute($order->{quantity} - $quantrec, $ordernumber);
1315 delete $order->{'ordernumber'};
1316 $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
1317 $order->{'quantity'} = $quantrec;
1318 $order->{'quantityreceived'} = $quantrec;
1319 $order->{'datereceived'} = $datereceived;
1320 $order->{'invoiceid'} = $invoiceid;
1321 $order->{'unitprice'} = $cost;
1322 $order->{'rrp'} = $rrp;
1323 $order->{ecost} = $ecost;
1324 $order->{'orderstatus'} = 'complete';
1326 ( $basketno, $new_ordernumber ) = NewOrder($order);
1328 if ($received_items) {
1329 foreach my $itemnumber (@$received_items) {
1330 ModItemOrder($itemnumber, $new_ordernumber);
1334 $sth=$dbh->prepare("update aqorders
1335 set quantityreceived=?,datereceived=?,invoiceid=?,
1336 unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'
1337 where biblionumber=? and ordernumber=?");
1338 $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$budget_id,$biblionumber,$ordernumber);
1341 return ($datereceived, $new_ordernumber);
1344 =head3 CancelReceipt
1346 my $parent_ordernumber = CancelReceipt($ordernumber);
1348 Cancel an order line receipt and update the parent order line, as if no
1350 If items are created at receipt (AcqCreateItem = receiving) then delete
1356 my $ordernumber = shift;
1358 return unless $ordernumber;
1360 my $dbh = C4::Context->dbh;
1362 SELECT datereceived, parent_ordernumber, quantity
1364 WHERE ordernumber = ?
1366 my $sth = $dbh->prepare($query);
1367 $sth->execute($ordernumber);
1368 my $order = $sth->fetchrow_hashref;
1370 warn "CancelReceipt: order $ordernumber does not exist";
1373 unless($order->{'datereceived'}) {
1374 warn "CancelReceipt: order $ordernumber is not received";
1378 my $parent_ordernumber = $order->{'parent_ordernumber'};
1380 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1381 # The order line has no parent, just mark it as not received
1384 SET quantityreceived = ?,
1387 orderstatus = 'ordered'
1388 WHERE ordernumber = ?
1390 $sth = $dbh->prepare($query);
1391 $sth->execute(0, undef, undef, $ordernumber);
1393 # The order line has a parent, increase parent quantity and delete
1396 SELECT quantity, datereceived
1398 WHERE ordernumber = ?
1400 $sth = $dbh->prepare($query);
1401 $sth->execute($parent_ordernumber);
1402 my $parent_order = $sth->fetchrow_hashref;
1403 unless($parent_order) {
1404 warn "Parent order $parent_ordernumber does not exist.";
1407 if($parent_order->{'datereceived'}) {
1408 warn "CancelReceipt: parent order is received.".
1409 " Can't cancel receipt.";
1415 orderstatus = 'ordered'
1416 WHERE ordernumber = ?
1418 $sth = $dbh->prepare($query);
1419 my $rv = $sth->execute(
1420 $order->{'quantity'} + $parent_order->{'quantity'},
1424 warn "Cannot update parent order line, so do not cancel".
1428 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1429 # Remove items that were created at receipt
1431 DELETE FROM items, aqorders_items
1432 USING items, aqorders_items
1433 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1435 $sth = $dbh->prepare($query);
1436 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1437 foreach my $itemnumber (@itemnumbers) {
1438 $sth->execute($itemnumber, $itemnumber);
1442 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1443 foreach my $itemnumber (@itemnumbers) {
1444 ModItemOrder($itemnumber, $parent_ordernumber);
1449 DELETE FROM aqorders
1450 WHERE ordernumber = ?
1452 $sth = $dbh->prepare($query);
1453 $sth->execute($ordernumber);
1457 return $parent_ordernumber;
1460 #------------------------------------------------------------#
1464 @results = &SearchOrders({
1465 ordernumber => $ordernumber,
1467 biblionumber => $biblionumber,
1469 booksellerid => $booksellerid,
1470 basketno => $basketno,
1475 Searches for orders.
1477 C<$owner> Finds order for the logged in user.
1478 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1481 C<@results> is an array of references-to-hash with the keys are fields
1482 from aqorders, biblio, biblioitems and aqbasket tables.
1487 my ( $params ) = @_;
1488 my $ordernumber = $params->{ordernumber};
1489 my $search = $params->{search};
1490 my $ean = $params->{ean};
1491 my $booksellerid = $params->{booksellerid};
1492 my $basketno = $params->{basketno};
1493 my $basketname = $params->{basketname};
1494 my $basketgroupname = $params->{basketgroupname};
1495 my $owner = $params->{owner};
1496 my $pending = $params->{pending};
1498 my $dbh = C4::Context->dbh;
1501 SELECT aqbasket.basketno,
1503 borrowers.firstname,
1506 biblioitems.biblioitemnumber,
1508 aqbasket.creationdate,
1509 aqbasket.basketname,
1512 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1513 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1514 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1515 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1516 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1517 WHERE (datecancellationprinted is NULL)
1521 AND (quantity > quantityreceived OR quantityreceived is NULL)
1524 my $userenv = C4::Context->userenv;
1525 if ( C4::Context->preference("IndependentBranches") ) {
1526 if ( ( $userenv ) and ( $userenv->{flags} != 1 ) ) {
1529 borrowers.branchcode = ?
1530 OR borrowers.branchcode = ''
1533 push @args, $userenv->{branch};
1537 if ( $ordernumber ) {
1538 $query .= ' AND (aqorders.ordernumber=?)';
1539 push @args, $ordernumber;
1542 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1543 push @args, ("%$search%","%$search%","%$search%");
1546 $query .= ' AND biblioitems.ean = ?';
1549 if ( $booksellerid ) {
1550 $query .= 'AND aqbasket.booksellerid = ?';
1551 push @args, $booksellerid;
1554 $query .= 'AND aqbasket.basketno = ?';
1555 push @args, $basketno;
1558 $query .= 'AND aqbasket.basketname LIKE ?';
1559 push @args, "%$basketname%";
1561 if( $basketgroupname ) {
1562 $query .= ' AND aqbasketgroups.name LIKE ?';
1563 push @args, "%$basketgroupname%";
1567 $query .= ' AND aqbasket.authorisedby=? ';
1568 push @args, $userenv->{'number'};
1571 $query .= ' ORDER BY aqbasket.basketno';
1573 my $sth = $dbh->prepare($query);
1574 $sth->execute(@args);
1575 return $sth->fetchall_arrayref({});
1578 #------------------------------------------------------------#
1582 &DelOrder($biblionumber, $ordernumber);
1584 Cancel the order with the given order and biblio numbers. It does not
1585 delete any entries in the aqorders table, it merely marks them as
1591 my ( $bibnum, $ordernumber ) = @_;
1592 my $dbh = C4::Context->dbh;
1595 SET datecancellationprinted=now(), orderstatus='cancelled'
1596 WHERE biblionumber=? AND ordernumber=?
1598 my $sth = $dbh->prepare($query);
1599 $sth->execute( $bibnum, $ordernumber );
1601 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1602 foreach my $itemnumber (@itemnumbers){
1603 C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
1608 =head3 TransferOrder
1610 my $newordernumber = TransferOrder($ordernumber, $basketno);
1612 Transfer an order line to a basket.
1613 Mark $ordernumber as cancelled with an internal note 'Cancelled and transfered
1614 to BOOKSELLER on DATE' and create new order with internal note
1615 'Transfered from BOOKSELLER on DATE'.
1616 Move all attached items to the new order.
1617 Received orders cannot be transfered.
1618 Return the ordernumber of created order.
1623 my ($ordernumber, $basketno) = @_;
1625 return unless ($ordernumber and $basketno);
1627 my $order = GetOrder( $ordernumber );
1628 return if $order->{datereceived};
1629 my $basket = GetBasket($basketno);
1630 return unless $basket;
1632 my $dbh = C4::Context->dbh;
1633 my ($query, $sth, $rv);
1637 SET datecancellationprinted = CAST(NOW() AS date)
1638 WHERE ordernumber = ?
1640 $sth = $dbh->prepare($query);
1641 $rv = $sth->execute($ordernumber);
1643 delete $order->{'ordernumber'};
1644 $order->{'basketno'} = $basketno;
1646 (undef, $newordernumber) = NewOrder($order);
1649 UPDATE aqorders_items
1651 WHERE ordernumber = ?
1653 $sth = $dbh->prepare($query);
1654 $sth->execute($newordernumber, $ordernumber);
1657 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1660 $sth = $dbh->prepare($query);
1661 $sth->execute($ordernumber, $newordernumber);
1663 return $newordernumber;
1666 =head2 FUNCTIONS ABOUT PARCELS
1670 #------------------------------------------------------------#
1674 @results = &GetParcel($booksellerid, $code, $date);
1676 Looks up all of the received items from the supplier with the given
1677 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1679 C<@results> is an array of references-to-hash. The keys of each element are fields from
1680 the aqorders, biblio, and biblioitems tables of the Koha database.
1682 C<@results> is sorted alphabetically by book title.
1687 #gets all orders from a certain supplier, orders them alphabetically
1688 my ( $supplierid, $code, $datereceived ) = @_;
1689 my $dbh = C4::Context->dbh;
1692 if $code; # add % if we search on a given code (otherwise, let him empty)
1694 SELECT authorisedby,
1699 aqorders.biblionumber,
1700 aqorders.ordernumber,
1701 aqorders.parent_ordernumber,
1703 aqorders.quantityreceived,
1711 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1712 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1713 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1714 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1716 aqbasket.booksellerid = ?
1717 AND aqinvoices.invoicenumber LIKE ?
1718 AND aqorders.datereceived = ? ";
1720 my @query_params = ( $supplierid, $code, $datereceived );
1721 if ( C4::Context->preference("IndependentBranches") ) {
1722 my $userenv = C4::Context->userenv;
1723 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1724 $strsth .= " and (borrowers.branchcode = ?
1725 or borrowers.branchcode = '')";
1726 push @query_params, $userenv->{branch};
1729 $strsth .= " ORDER BY aqbasket.basketno";
1730 # ## parcelinformation : $strsth
1731 my $sth = $dbh->prepare($strsth);
1732 $sth->execute( @query_params );
1733 while ( my $data = $sth->fetchrow_hashref ) {
1734 push( @results, $data );
1736 # ## countparcelbiblio: scalar(@results)
1742 #------------------------------------------------------------#
1746 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1748 get a lists of parcels.
1755 is the bookseller this function has to get parcels.
1758 To know on what criteria the results list has to be ordered.
1761 is the booksellerinvoicenumber.
1763 =item $datefrom & $dateto
1764 to know on what date this function has to filter its search.
1769 a pointer on a hash list containing parcel informations as such :
1775 =item Last operation
1777 =item Number of biblio
1779 =item Number of items
1786 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1787 my $dbh = C4::Context->dbh;
1788 my @query_params = ();
1790 SELECT aqinvoices.invoicenumber,
1791 datereceived,purchaseordernumber,
1792 count(DISTINCT biblionumber) AS biblio,
1793 sum(quantity) AS itemsexpected,
1794 sum(quantityreceived) AS itemsreceived
1795 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1796 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1797 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1799 push @query_params, $bookseller;
1801 if ( defined $code ) {
1802 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1803 # add a % to the end of the code to allow stemming.
1804 push @query_params, "$code%";
1807 if ( defined $datefrom ) {
1808 $strsth .= ' and datereceived >= ? ';
1809 push @query_params, $datefrom;
1812 if ( defined $dateto ) {
1813 $strsth .= 'and datereceived <= ? ';
1814 push @query_params, $dateto;
1817 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1819 # can't use a placeholder to place this column name.
1820 # but, we could probably be checking to make sure it is a column that will be fetched.
1821 $strsth .= "order by $order " if ($order);
1823 my $sth = $dbh->prepare($strsth);
1825 $sth->execute( @query_params );
1826 my $results = $sth->fetchall_arrayref({});
1831 #------------------------------------------------------------#
1833 =head3 GetLateOrders
1835 @results = &GetLateOrders;
1837 Searches for bookseller with late orders.
1840 the table of supplier with late issues. This table is full of hashref.
1846 my $supplierid = shift;
1848 my $estimateddeliverydatefrom = shift;
1849 my $estimateddeliverydateto = shift;
1851 my $dbh = C4::Context->dbh;
1853 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
1854 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
1856 my @query_params = ();
1858 SELECT aqbasket.basketno,
1859 aqorders.ordernumber,
1860 DATE(aqbasket.closedate) AS orderdate,
1861 aqorders.rrp AS unitpricesupplier,
1862 aqorders.ecost AS unitpricelib,
1863 aqorders.claims_count AS claims_count,
1864 aqorders.claimed_date AS claimed_date,
1865 aqbudgets.budget_name AS budget,
1866 borrowers.branchcode AS branch,
1867 aqbooksellers.name AS supplier,
1868 aqbooksellers.id AS supplierid,
1869 biblio.author, biblio.title,
1870 biblioitems.publishercode AS publisher,
1871 biblioitems.publicationyear,
1872 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1876 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1877 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1878 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1879 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1880 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1881 WHERE aqorders.basketno = aqbasket.basketno
1882 AND ( datereceived = ''
1883 OR datereceived IS NULL
1884 OR aqorders.quantityreceived < aqorders.quantity
1886 AND aqbasket.closedate IS NOT NULL
1887 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
1890 if ($dbdriver eq "mysql") {
1892 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
1893 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1894 DATEDIFF(CAST(now() AS date),closedate) AS latesince
1896 if ( defined $delay ) {
1897 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
1898 push @query_params, $delay;
1901 HAVING quantity <> 0
1902 AND unitpricesupplier <> 0
1903 AND unitpricelib <> 0
1906 # FIXME: account for IFNULL as above
1908 aqorders.quantity AS quantity,
1909 aqorders.quantity * aqorders.rrp AS subtotal,
1910 (CAST(now() AS date) - closedate) AS latesince
1912 if ( defined $delay ) {
1913 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
1914 push @query_params, $delay;
1917 if (defined $supplierid) {
1918 $from .= ' AND aqbasket.booksellerid = ? ';
1919 push @query_params, $supplierid;
1921 if (defined $branch) {
1922 $from .= ' AND borrowers.branchcode LIKE ? ';
1923 push @query_params, $branch;
1926 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
1927 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
1929 if ( defined $estimateddeliverydatefrom ) {
1930 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
1931 push @query_params, $estimateddeliverydatefrom;
1933 if ( defined $estimateddeliverydateto ) {
1934 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
1935 push @query_params, $estimateddeliverydateto;
1937 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
1938 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
1940 if (C4::Context->preference("IndependentBranches")
1941 && C4::Context->userenv
1942 && C4::Context->userenv->{flags} != 1 ) {
1943 $from .= ' AND borrowers.branchcode LIKE ? ';
1944 push @query_params, C4::Context->userenv->{branch};
1946 $from .= " AND orderstatus <> 'cancelled' ";
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 'cancelled';
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.*, aqbasket.basketname
2465 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2466 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2469 $sth = $dbh->prepare($query);
2470 $sth->execute($invoiceid);
2471 $invoice->{orders} = $sth->fetchall_arrayref({});
2472 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2479 my $invoiceid = AddInvoice(
2480 invoicenumber => $invoicenumber,
2481 booksellerid => $booksellerid,
2482 shipmentdate => $shipmentdate,
2483 billingdate => $billingdate,
2484 closedate => $closedate,
2485 shipmentcost => $shipmentcost,
2486 shipmentcost_budgetid => $shipmentcost_budgetid
2489 Create a new invoice and return its id or undef if it fails.
2496 return unless(%invoice and $invoice{invoicenumber});
2498 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2499 closedate shipmentcost shipmentcost_budgetid);
2503 foreach my $key (keys %invoice) {
2504 if(0 < grep(/^$key$/, @columns)) {
2505 push @set_strs, "$key = ?";
2506 push @set_args, ($invoice{$key} || undef);
2512 my $dbh = C4::Context->dbh;
2513 my $query = "INSERT INTO aqinvoices SET ";
2514 $query .= join (",", @set_strs);
2515 my $sth = $dbh->prepare($query);
2516 $rv = $sth->execute(@set_args);
2518 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2527 invoiceid => $invoiceid, # Mandatory
2528 invoicenumber => $invoicenumber,
2529 booksellerid => $booksellerid,
2530 shipmentdate => $shipmentdate,
2531 billingdate => $billingdate,
2532 closedate => $closedate,
2533 shipmentcost => $shipmentcost,
2534 shipmentcost_budgetid => $shipmentcost_budgetid
2537 Modify an invoice, invoiceid is mandatory.
2539 Return undef if it fails.
2546 return unless(%invoice and $invoice{invoiceid});
2548 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2549 closedate shipmentcost shipmentcost_budgetid);
2553 foreach my $key (keys %invoice) {
2554 if(0 < grep(/^$key$/, @columns)) {
2555 push @set_strs, "$key = ?";
2556 push @set_args, ($invoice{$key} || undef);
2560 my $dbh = C4::Context->dbh;
2561 my $query = "UPDATE aqinvoices SET ";
2562 $query .= join(",", @set_strs);
2563 $query .= " WHERE invoiceid = ?";
2565 my $sth = $dbh->prepare($query);
2566 $sth->execute(@set_args, $invoice{invoiceid});
2571 CloseInvoice($invoiceid);
2575 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2580 my ($invoiceid) = @_;
2582 return unless $invoiceid;
2584 my $dbh = C4::Context->dbh;
2587 SET closedate = CAST(NOW() AS DATE)
2590 my $sth = $dbh->prepare($query);
2591 $sth->execute($invoiceid);
2594 =head3 ReopenInvoice
2596 ReopenInvoice($invoiceid);
2600 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2605 my ($invoiceid) = @_;
2607 return unless $invoiceid;
2609 my $dbh = C4::Context->dbh;
2612 SET closedate = NULL
2615 my $sth = $dbh->prepare($query);
2616 $sth->execute($invoiceid);
2621 DelInvoice($invoiceid);
2623 Delete an invoice if there are no items attached to it.
2628 my ($invoiceid) = @_;
2630 return unless $invoiceid;
2632 my $dbh = C4::Context->dbh;
2638 my $sth = $dbh->prepare($query);
2639 $sth->execute($invoiceid);
2640 my $res = $sth->fetchrow_arrayref;
2641 if ( $res && $res->[0] == 0 ) {
2643 DELETE FROM aqinvoices
2646 my $sth = $dbh->prepare($query);
2647 return ( $sth->execute($invoiceid) > 0 );
2652 =head3 MergeInvoices
2654 MergeInvoices($invoiceid, \@sourceids);
2656 Merge the invoices identified by the IDs in \@sourceids into
2657 the invoice identified by $invoiceid.
2662 my ($invoiceid, $sourceids) = @_;
2664 return unless $invoiceid;
2665 foreach my $sourceid (@$sourceids) {
2666 next if $sourceid == $invoiceid;
2667 my $source = GetInvoiceDetails($sourceid);
2668 foreach my $order (@{$source->{'orders'}}) {
2669 $order->{'invoiceid'} = $invoiceid;
2672 DelInvoice($source->{'invoiceid'});
2677 =head3 GetBiblioCountByBasketno
2679 $biblio_count = &GetBiblioCountByBasketno($basketno);
2681 Looks up the biblio's count that has basketno value $basketno
2687 sub GetBiblioCountByBasketno {
2688 my ($basketno) = @_;
2689 my $dbh = C4::Context->dbh;
2691 SELECT COUNT( DISTINCT( biblionumber ) )
2694 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2697 my $sth = $dbh->prepare($query);
2698 $sth->execute($basketno);
2699 return $sth->fetchrow;
2707 Koha Development Team <http://koha-community.org/>