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;
969 my $query = qq{SELECT
974 borrowers.branchcode,
975 biblioitems.publicationyear,
976 biblio.copyrightdate,
977 biblioitems.editionstatement,
981 biblioitems.publishercode,
982 aqorders.rrp AS unitpricesupplier,
983 aqorders.ecost AS unitpricelib,
984 aqorders.claims_count AS claims_count,
985 aqorders.claimed_date AS claimed_date,
986 aqbudgets.budget_name AS budget,
987 aqbooksellers.name AS supplier,
988 aqbooksellers.id AS supplierid,
989 biblioitems.publishercode AS publisher,
990 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
991 DATE(aqbasket.closedate) AS orderdate,
992 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
993 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
994 DATEDIFF(CURDATE( ),closedate) AS latesince
995 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
996 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
997 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
998 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
999 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1000 WHERE aqorders.basketno = aqbasket.basketno
1002 my $sth= $dbh->prepare($query);
1003 $sth->execute($ordernumber);
1004 my $data = $sth->fetchrow_hashref;
1005 $data->{orderdate} = format_date( $data->{orderdate} );
1010 =head3 GetLastOrderNotReceivedFromSubscriptionid
1012 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1014 Returns a reference-to-hash describing the last order not received for a subscription.
1018 sub GetLastOrderNotReceivedFromSubscriptionid {
1019 my ( $subscriptionid ) = @_;
1020 my $dbh = C4::Context->dbh;
1022 SELECT * FROM aqorders
1023 LEFT JOIN subscription
1024 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1025 WHERE aqorders.subscriptionid = ?
1026 AND aqorders.datereceived IS NULL
1029 my $sth = $dbh->prepare( $query );
1030 $sth->execute( $subscriptionid );
1031 my $order = $sth->fetchrow_hashref;
1035 =head3 GetLastOrderReceivedFromSubscriptionid
1037 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1039 Returns a reference-to-hash describing the last order received for a subscription.
1043 sub GetLastOrderReceivedFromSubscriptionid {
1044 my ( $subscriptionid ) = @_;
1045 my $dbh = C4::Context->dbh;
1047 SELECT * FROM aqorders
1048 LEFT JOIN subscription
1049 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1050 WHERE aqorders.subscriptionid = ?
1051 AND aqorders.datereceived =
1053 SELECT MAX( aqorders.datereceived )
1055 LEFT JOIN subscription
1056 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1057 WHERE aqorders.subscriptionid = ?
1058 AND aqorders.datereceived IS NOT NULL
1060 ORDER BY ordernumber DESC
1063 my $sth = $dbh->prepare( $query );
1064 $sth->execute( $subscriptionid, $subscriptionid );
1065 my $order = $sth->fetchrow_hashref;
1071 #------------------------------------------------------------#
1075 &NewOrder(\%hashref);
1077 Adds a new order to the database. Any argument that isn't described
1078 below is the new value of the field with the same name in the aqorders
1079 table of the Koha database.
1083 =item $hashref->{'basketno'} is the basketno foreign key in aqorders, it is mandatory
1085 =item $hashref->{'ordernumber'} is a "minimum order number."
1087 =item $hashref->{'budgetdate'} is effectively ignored.
1088 If it's undef (anything false) or the string 'now', the current day is used.
1089 Else, the upcoming July 1st is used.
1091 =item $hashref->{'subscription'} may be either "yes", or anything else for "no".
1093 =item $hashref->{'uncertainprice'} may be 0 for "the price is known" or 1 for "the price is uncertain"
1095 =item defaults entrydate to Now
1097 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".
1104 my $orderinfo = shift;
1106 my $dbh = C4::Context->dbh;
1110 # if these parameters are missing, we can't continue
1111 for my $key (qw/basketno quantity biblionumber budget_id/) {
1112 croak "Mandatory parameter $key missing" unless $orderinfo->{$key};
1115 if ( defined $orderinfo->{subscription} && $orderinfo->{'subscription'} eq 'yes' ) {
1116 $orderinfo->{'subscription'} = 1;
1118 $orderinfo->{'subscription'} = 0;
1120 $orderinfo->{'entrydate'} ||= C4::Dates->new()->output("iso");
1121 if (!$orderinfo->{quantityreceived}) {
1122 $orderinfo->{quantityreceived} = 0;
1125 my $ordernumber=InsertInTable("aqorders",$orderinfo);
1126 if (not $orderinfo->{parent_ordernumber}) {
1127 my $sth = $dbh->prepare("
1129 SET parent_ordernumber = ordernumber
1130 WHERE ordernumber = ?
1132 $sth->execute($ordernumber);
1134 return ( $orderinfo->{'basketno'}, $ordernumber );
1139 #------------------------------------------------------------#
1148 my ($itemnumber, $ordernumber) = @_;
1149 my $dbh = C4::Context->dbh;
1151 INSERT INTO aqorders_items
1152 (itemnumber, ordernumber)
1155 my $sth = $dbh->prepare($query);
1156 $sth->execute( $itemnumber, $ordernumber);
1159 #------------------------------------------------------------#
1163 &ModOrder(\%hashref);
1165 Modifies an existing order. Updates the order with order number
1166 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1167 other keys of the hash update the fields with the same name in the aqorders
1168 table of the Koha database.
1173 my $orderinfo = shift;
1175 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1176 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1178 my $dbh = C4::Context->dbh;
1181 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1182 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1184 # delete($orderinfo->{'branchcode'});
1185 # the hash contains a lot of entries not in aqorders, so get the columns ...
1186 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1188 my $colnames = $sth->{NAME};
1189 #FIXME Be careful. If aqorders would have columns with diacritics,
1190 #you should need to decode what you get back from NAME.
1191 #See report 10110 and guided_reports.pl
1192 my $query = "UPDATE aqorders SET ";
1194 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1195 # ... and skip hash entries that are not in the aqorders table
1196 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1197 next unless grep(/^$orderinfokey$/, @$colnames);
1198 $query .= "$orderinfokey=?, ";
1199 push(@params, $orderinfo->{$orderinfokey});
1202 $query .= "timestamp=NOW() WHERE ordernumber=?";
1203 # push(@params, $specorderinfo{'ordernumber'});
1204 push(@params, $orderinfo->{'ordernumber'} );
1205 $sth = $dbh->prepare($query);
1206 $sth->execute(@params);
1210 #------------------------------------------------------------#
1214 ModItemOrder($itemnumber, $ordernumber);
1216 Modifies the ordernumber of an item in aqorders_items.
1221 my ($itemnumber, $ordernumber) = @_;
1223 return unless ($itemnumber and $ordernumber);
1225 my $dbh = C4::Context->dbh;
1227 UPDATE aqorders_items
1229 WHERE itemnumber = ?
1231 my $sth = $dbh->prepare($query);
1232 return $sth->execute($ordernumber, $itemnumber);
1235 #------------------------------------------------------------#
1237 =head3 GetCancelledOrders
1239 my @orders = GetCancelledOrders($basketno, $orderby);
1241 Returns cancelled orders for a basket
1245 sub GetCancelledOrders {
1246 my ( $basketno, $orderby ) = @_;
1248 return () unless $basketno;
1250 my $dbh = C4::Context->dbh;
1257 aqorders_transfers.ordernumber_to AS transferred_to,
1258 aqorders_transfers.timestamp AS transferred_to_timestamp
1260 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1261 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1262 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1263 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1265 AND (datecancellationprinted IS NOT NULL
1266 AND datecancellationprinted <> '0000-00-00')
1269 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1271 $query .= " ORDER BY $orderby";
1272 my $sth = $dbh->prepare($query);
1273 $sth->execute($basketno);
1274 my $results = $sth->fetchall_arrayref( {} );
1280 #------------------------------------------------------------#
1282 =head3 ModReceiveOrder
1284 &ModReceiveOrder($biblionumber, $ordernumber, $quantityreceived, $user,
1285 $cost, $ecost, $invoiceid, rrp, budget_id, datereceived, \@received_itemnumbers);
1287 Updates an order, to reflect the fact that it was received, at least
1288 in part. All arguments not mentioned below update the fields with the
1289 same name in the aqorders table of the Koha database.
1291 If a partial order is received, splits the order into two.
1293 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1299 sub ModReceiveOrder {
1301 $biblionumber, $ordernumber, $quantrec, $user, $cost, $ecost,
1302 $invoiceid, $rrp, $budget_id, $datereceived, $received_items
1306 my $dbh = C4::Context->dbh;
1307 $datereceived = C4::Dates->output('iso') unless $datereceived;
1308 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1309 if ($suggestionid) {
1310 ModSuggestion( {suggestionid=>$suggestionid,
1311 STATUS=>'AVAILABLE',
1312 biblionumber=> $biblionumber}
1316 my $sth=$dbh->prepare("
1317 SELECT * FROM aqorders
1318 WHERE biblionumber=? AND aqorders.ordernumber=?");
1320 $sth->execute($biblionumber,$ordernumber);
1321 my $order = $sth->fetchrow_hashref();
1324 my $new_ordernumber = $ordernumber;
1325 if ( $order->{quantity} > $quantrec ) {
1326 # Split order line in two parts: the first is the original order line
1327 # without received items (the quantity is decreased),
1328 # the second part is a new order line with quantity=quantityrec
1329 # (entirely received)
1330 $sth=$dbh->prepare("
1333 orderstatus = 'partial'
1334 WHERE ordernumber = ?
1337 $sth->execute($order->{quantity} - $quantrec, $ordernumber);
1341 delete $order->{'ordernumber'};
1342 $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
1343 $order->{'quantity'} = $quantrec;
1344 $order->{'quantityreceived'} = $quantrec;
1345 $order->{'datereceived'} = $datereceived;
1346 $order->{'invoiceid'} = $invoiceid;
1347 $order->{'unitprice'} = $cost;
1348 $order->{'rrp'} = $rrp;
1349 $order->{ecost} = $ecost;
1350 $order->{'orderstatus'} = 'complete';
1352 ( $basketno, $new_ordernumber ) = NewOrder($order);
1354 if ($received_items) {
1355 foreach my $itemnumber (@$received_items) {
1356 ModItemOrder($itemnumber, $new_ordernumber);
1360 $sth=$dbh->prepare("update aqorders
1361 set quantityreceived=?,datereceived=?,invoiceid=?,
1362 unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'
1363 where biblionumber=? and ordernumber=?");
1364 $sth->execute($quantrec,$datereceived,$invoiceid,$cost,$rrp,$ecost,$budget_id,$biblionumber,$ordernumber);
1367 return ($datereceived, $new_ordernumber);
1370 =head3 CancelReceipt
1372 my $parent_ordernumber = CancelReceipt($ordernumber);
1374 Cancel an order line receipt and update the parent order line, as if no
1376 If items are created at receipt (AcqCreateItem = receiving) then delete
1382 my $ordernumber = shift;
1384 return unless $ordernumber;
1386 my $dbh = C4::Context->dbh;
1388 SELECT datereceived, parent_ordernumber, quantity
1390 WHERE ordernumber = ?
1392 my $sth = $dbh->prepare($query);
1393 $sth->execute($ordernumber);
1394 my $order = $sth->fetchrow_hashref;
1396 warn "CancelReceipt: order $ordernumber does not exist";
1399 unless($order->{'datereceived'}) {
1400 warn "CancelReceipt: order $ordernumber is not received";
1404 my $parent_ordernumber = $order->{'parent_ordernumber'};
1406 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1407 # The order line has no parent, just mark it as not received
1410 SET quantityreceived = ?,
1413 orderstatus = 'ordered'
1414 WHERE ordernumber = ?
1416 $sth = $dbh->prepare($query);
1417 $sth->execute(0, undef, undef, $ordernumber);
1419 # The order line has a parent, increase parent quantity and delete
1422 SELECT quantity, datereceived
1424 WHERE ordernumber = ?
1426 $sth = $dbh->prepare($query);
1427 $sth->execute($parent_ordernumber);
1428 my $parent_order = $sth->fetchrow_hashref;
1429 unless($parent_order) {
1430 warn "Parent order $parent_ordernumber does not exist.";
1433 if($parent_order->{'datereceived'}) {
1434 warn "CancelReceipt: parent order is received.".
1435 " Can't cancel receipt.";
1441 orderstatus = 'ordered'
1442 WHERE ordernumber = ?
1444 $sth = $dbh->prepare($query);
1445 my $rv = $sth->execute(
1446 $order->{'quantity'} + $parent_order->{'quantity'},
1450 warn "Cannot update parent order line, so do not cancel".
1454 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1455 # Remove items that were created at receipt
1457 DELETE FROM items, aqorders_items
1458 USING items, aqorders_items
1459 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1461 $sth = $dbh->prepare($query);
1462 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1463 foreach my $itemnumber (@itemnumbers) {
1464 $sth->execute($itemnumber, $itemnumber);
1468 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1469 foreach my $itemnumber (@itemnumbers) {
1470 ModItemOrder($itemnumber, $parent_ordernumber);
1475 DELETE FROM aqorders
1476 WHERE ordernumber = ?
1478 $sth = $dbh->prepare($query);
1479 $sth->execute($ordernumber);
1483 return $parent_ordernumber;
1486 #------------------------------------------------------------#
1490 @results = &SearchOrders({
1491 ordernumber => $ordernumber,
1493 biblionumber => $biblionumber,
1495 booksellerid => $booksellerid,
1496 basketno => $basketno,
1501 Searches for orders.
1503 C<$owner> Finds order for the logged in user.
1504 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1507 C<@results> is an array of references-to-hash with the keys are fields
1508 from aqorders, biblio, biblioitems and aqbasket tables.
1513 my ( $params ) = @_;
1514 my $ordernumber = $params->{ordernumber};
1515 my $search = $params->{search};
1516 my $ean = $params->{ean};
1517 my $booksellerid = $params->{booksellerid};
1518 my $basketno = $params->{basketno};
1519 my $basketname = $params->{basketname};
1520 my $basketgroupname = $params->{basketgroupname};
1521 my $owner = $params->{owner};
1522 my $pending = $params->{pending};
1524 my $dbh = C4::Context->dbh;
1527 SELECT aqbasket.basketno,
1529 borrowers.firstname,
1532 biblioitems.biblioitemnumber,
1534 aqbasket.creationdate,
1535 aqbasket.basketname,
1538 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1539 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1540 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1541 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1542 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1543 WHERE (datecancellationprinted is NULL)
1547 AND (quantity > quantityreceived OR quantityreceived is NULL)
1550 my $userenv = C4::Context->userenv;
1551 if ( C4::Context->preference("IndependentBranches") ) {
1552 if ( ( $userenv ) and ( $userenv->{flags} != 1 ) ) {
1555 borrowers.branchcode = ?
1556 OR borrowers.branchcode = ''
1559 push @args, $userenv->{branch};
1563 if ( $ordernumber ) {
1564 $query .= ' AND (aqorders.ordernumber=?)';
1565 push @args, $ordernumber;
1568 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1569 push @args, ("%$search%","%$search%","%$search%");
1572 $query .= ' AND biblioitems.ean = ?';
1575 if ( $booksellerid ) {
1576 $query .= 'AND aqbasket.booksellerid = ?';
1577 push @args, $booksellerid;
1580 $query .= 'AND aqbasket.basketno = ?';
1581 push @args, $basketno;
1584 $query .= 'AND aqbasket.basketname LIKE ?';
1585 push @args, "%$basketname%";
1587 if( $basketgroupname ) {
1588 $query .= ' AND aqbasketgroups.name LIKE ?';
1589 push @args, "%$basketgroupname%";
1593 $query .= ' AND aqbasket.authorisedby=? ';
1594 push @args, $userenv->{'number'};
1597 $query .= ' ORDER BY aqbasket.basketno';
1599 my $sth = $dbh->prepare($query);
1600 $sth->execute(@args);
1601 return $sth->fetchall_arrayref({});
1604 #------------------------------------------------------------#
1608 &DelOrder($biblionumber, $ordernumber);
1610 Cancel the order with the given order and biblio numbers. It does not
1611 delete any entries in the aqorders table, it merely marks them as
1617 my ( $bibnum, $ordernumber ) = @_;
1618 my $dbh = C4::Context->dbh;
1621 SET datecancellationprinted=now(), orderstatus='cancelled'
1622 WHERE biblionumber=? AND ordernumber=?
1624 my $sth = $dbh->prepare($query);
1625 $sth->execute( $bibnum, $ordernumber );
1627 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1628 foreach my $itemnumber (@itemnumbers){
1629 C4::Items::DelItem( $dbh, $bibnum, $itemnumber );
1634 =head3 TransferOrder
1636 my $newordernumber = TransferOrder($ordernumber, $basketno);
1638 Transfer an order line to a basket.
1639 Mark $ordernumber as cancelled with an internal note 'Cancelled and transfered
1640 to BOOKSELLER on DATE' and create new order with internal note
1641 'Transfered from BOOKSELLER on DATE'.
1642 Move all attached items to the new order.
1643 Received orders cannot be transfered.
1644 Return the ordernumber of created order.
1649 my ($ordernumber, $basketno) = @_;
1651 return unless ($ordernumber and $basketno);
1653 my $order = GetOrder( $ordernumber );
1654 return if $order->{datereceived};
1655 my $basket = GetBasket($basketno);
1656 return unless $basket;
1658 my $dbh = C4::Context->dbh;
1659 my ($query, $sth, $rv);
1663 SET datecancellationprinted = CAST(NOW() AS date)
1664 WHERE ordernumber = ?
1666 $sth = $dbh->prepare($query);
1667 $rv = $sth->execute($ordernumber);
1669 delete $order->{'ordernumber'};
1670 $order->{'basketno'} = $basketno;
1672 (undef, $newordernumber) = NewOrder($order);
1675 UPDATE aqorders_items
1677 WHERE ordernumber = ?
1679 $sth = $dbh->prepare($query);
1680 $sth->execute($newordernumber, $ordernumber);
1683 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1686 $sth = $dbh->prepare($query);
1687 $sth->execute($ordernumber, $newordernumber);
1689 return $newordernumber;
1692 =head2 FUNCTIONS ABOUT PARCELS
1696 #------------------------------------------------------------#
1700 @results = &GetParcel($booksellerid, $code, $date);
1702 Looks up all of the received items from the supplier with the given
1703 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1705 C<@results> is an array of references-to-hash. The keys of each element are fields from
1706 the aqorders, biblio, and biblioitems tables of the Koha database.
1708 C<@results> is sorted alphabetically by book title.
1713 #gets all orders from a certain supplier, orders them alphabetically
1714 my ( $supplierid, $code, $datereceived ) = @_;
1715 my $dbh = C4::Context->dbh;
1718 if $code; # add % if we search on a given code (otherwise, let him empty)
1720 SELECT authorisedby,
1725 aqorders.biblionumber,
1726 aqorders.ordernumber,
1727 aqorders.parent_ordernumber,
1729 aqorders.quantityreceived,
1737 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1738 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1739 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1740 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1742 aqbasket.booksellerid = ?
1743 AND aqinvoices.invoicenumber LIKE ?
1744 AND aqorders.datereceived = ? ";
1746 my @query_params = ( $supplierid, $code, $datereceived );
1747 if ( C4::Context->preference("IndependentBranches") ) {
1748 my $userenv = C4::Context->userenv;
1749 if ( ($userenv) && ( $userenv->{flags} != 1 ) ) {
1750 $strsth .= " and (borrowers.branchcode = ?
1751 or borrowers.branchcode = '')";
1752 push @query_params, $userenv->{branch};
1755 $strsth .= " ORDER BY aqbasket.basketno";
1756 # ## parcelinformation : $strsth
1757 my $sth = $dbh->prepare($strsth);
1758 $sth->execute( @query_params );
1759 while ( my $data = $sth->fetchrow_hashref ) {
1760 push( @results, $data );
1762 # ## countparcelbiblio: scalar(@results)
1768 #------------------------------------------------------------#
1772 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1774 get a lists of parcels.
1781 is the bookseller this function has to get parcels.
1784 To know on what criteria the results list has to be ordered.
1787 is the booksellerinvoicenumber.
1789 =item $datefrom & $dateto
1790 to know on what date this function has to filter its search.
1795 a pointer on a hash list containing parcel informations as such :
1801 =item Last operation
1803 =item Number of biblio
1805 =item Number of items
1812 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1813 my $dbh = C4::Context->dbh;
1814 my @query_params = ();
1816 SELECT aqinvoices.invoicenumber,
1817 datereceived,purchaseordernumber,
1818 count(DISTINCT biblionumber) AS biblio,
1819 sum(quantity) AS itemsexpected,
1820 sum(quantityreceived) AS itemsreceived
1821 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1822 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1823 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1825 push @query_params, $bookseller;
1827 if ( defined $code ) {
1828 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1829 # add a % to the end of the code to allow stemming.
1830 push @query_params, "$code%";
1833 if ( defined $datefrom ) {
1834 $strsth .= ' and datereceived >= ? ';
1835 push @query_params, $datefrom;
1838 if ( defined $dateto ) {
1839 $strsth .= 'and datereceived <= ? ';
1840 push @query_params, $dateto;
1843 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1845 # can't use a placeholder to place this column name.
1846 # but, we could probably be checking to make sure it is a column that will be fetched.
1847 $strsth .= "order by $order " if ($order);
1849 my $sth = $dbh->prepare($strsth);
1851 $sth->execute( @query_params );
1852 my $results = $sth->fetchall_arrayref({});
1857 #------------------------------------------------------------#
1859 =head3 GetLateOrders
1861 @results = &GetLateOrders;
1863 Searches for bookseller with late orders.
1866 the table of supplier with late issues. This table is full of hashref.
1872 my $supplierid = shift;
1874 my $estimateddeliverydatefrom = shift;
1875 my $estimateddeliverydateto = shift;
1877 my $dbh = C4::Context->dbh;
1879 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
1880 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
1882 my @query_params = ();
1884 SELECT aqbasket.basketno,
1885 aqorders.ordernumber,
1886 DATE(aqbasket.closedate) AS orderdate,
1887 aqorders.rrp AS unitpricesupplier,
1888 aqorders.ecost AS unitpricelib,
1889 aqorders.claims_count AS claims_count,
1890 aqorders.claimed_date AS claimed_date,
1891 aqbudgets.budget_name AS budget,
1892 borrowers.branchcode AS branch,
1893 aqbooksellers.name AS supplier,
1894 aqbooksellers.id AS supplierid,
1895 biblio.author, biblio.title,
1896 biblioitems.publishercode AS publisher,
1897 biblioitems.publicationyear,
1898 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1902 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1903 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1904 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1905 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1906 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1907 WHERE aqorders.basketno = aqbasket.basketno
1908 AND ( datereceived = ''
1909 OR datereceived IS NULL
1910 OR aqorders.quantityreceived < aqorders.quantity
1912 AND aqbasket.closedate IS NOT NULL
1913 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
1916 if ($dbdriver eq "mysql") {
1918 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
1919 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1920 DATEDIFF(CAST(now() AS date),closedate) AS latesince
1922 if ( defined $delay ) {
1923 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
1924 push @query_params, $delay;
1927 HAVING quantity <> 0
1928 AND unitpricesupplier <> 0
1929 AND unitpricelib <> 0
1932 # FIXME: account for IFNULL as above
1934 aqorders.quantity AS quantity,
1935 aqorders.quantity * aqorders.rrp AS subtotal,
1936 (CAST(now() AS date) - closedate) AS latesince
1938 if ( defined $delay ) {
1939 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
1940 push @query_params, $delay;
1943 if (defined $supplierid) {
1944 $from .= ' AND aqbasket.booksellerid = ? ';
1945 push @query_params, $supplierid;
1947 if (defined $branch) {
1948 $from .= ' AND borrowers.branchcode LIKE ? ';
1949 push @query_params, $branch;
1952 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
1953 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
1955 if ( defined $estimateddeliverydatefrom ) {
1956 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
1957 push @query_params, $estimateddeliverydatefrom;
1959 if ( defined $estimateddeliverydateto ) {
1960 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
1961 push @query_params, $estimateddeliverydateto;
1963 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
1964 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
1966 if (C4::Context->preference("IndependentBranches")
1967 && C4::Context->userenv
1968 && C4::Context->userenv->{flags} != 1 ) {
1969 $from .= ' AND borrowers.branchcode LIKE ? ';
1970 push @query_params, C4::Context->userenv->{branch};
1972 $from .= " AND orderstatus <> 'cancelled' ";
1973 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
1974 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
1975 my $sth = $dbh->prepare($query);
1976 $sth->execute(@query_params);
1978 while (my $data = $sth->fetchrow_hashref) {
1979 $data->{orderdate} = format_date($data->{orderdate});
1980 $data->{claimed_date} = format_date($data->{claimed_date});
1981 push @results, $data;
1986 #------------------------------------------------------------#
1990 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
1992 Retreives some acquisition history information
2000 basket - search both basket name and number
2001 booksellerinvoicenumber
2004 $order_loop is a list of hashrefs that each look like this:
2006 'author' => 'Twain, Mark',
2008 'biblionumber' => '215',
2010 'creationdate' => 'MM/DD/YYYY',
2011 'datereceived' => undef,
2014 'invoicenumber' => undef,
2016 'ordernumber' => '1',
2018 'quantityreceived' => undef,
2019 'title' => 'The Adventures of Huckleberry Finn'
2021 $total_qty is the sum of all of the quantities in $order_loop
2022 $total_price is the cost of each in $order_loop times the quantity
2023 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
2028 # don't run the query if there are no parameters (list would be too long for sure !)
2029 croak "No search params" unless @_;
2031 my $title = $params{title};
2032 my $author = $params{author};
2033 my $isbn = $params{isbn};
2034 my $ean = $params{ean};
2035 my $name = $params{name};
2036 my $from_placed_on = $params{from_placed_on};
2037 my $to_placed_on = $params{to_placed_on};
2038 my $basket = $params{basket};
2039 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2040 my $basketgroupname = $params{basketgroupname};
2041 my $budget = $params{budget};
2042 my $orderstatus = $params{orderstatus};
2046 my $total_qtyreceived = 0;
2047 my $total_price = 0;
2049 my $dbh = C4::Context->dbh;
2052 COALESCE(biblio.title, deletedbiblio.title) AS title,
2053 COALESCE(biblio.author, deletedbiblio.author) AS author,
2054 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2055 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2057 aqbasket.basketname,
2058 aqbasket.basketgroupid,
2059 aqbasketgroups.name as groupname,
2061 aqbasket.creationdate,
2062 aqorders.datereceived,
2064 aqorders.quantityreceived,
2066 aqorders.ordernumber,
2068 aqinvoices.invoicenumber,
2069 aqbooksellers.id as id,
2070 aqorders.biblionumber,
2071 aqorders.orderstatus,
2072 aqorders.parent_ordernumber,
2073 aqbudgets.budget_name
2075 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2078 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2079 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2080 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2081 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2082 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2083 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2084 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2085 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2086 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2089 if ( C4::Context->preference("IndependentBranches") ) {
2090 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber";
2093 $query .= " WHERE 1 ";
2095 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') " if $orderstatus ne 'cancelled';
2097 my @query_params = ();
2100 $query .= " AND biblio.title LIKE ? ";
2101 $title =~ s/\s+/%/g;
2102 push @query_params, "%$title%";
2106 $query .= " AND biblio.author LIKE ? ";
2107 push @query_params, "%$author%";
2111 $query .= " AND biblioitems.isbn LIKE ? ";
2112 push @query_params, "%$isbn%";
2115 $query .= " AND biblioitems.ean = ? ";
2116 push @query_params, "$ean";
2119 $query .= " AND aqbooksellers.name LIKE ? ";
2120 push @query_params, "%$name%";
2124 $query .= " AND aqbudgets.budget_id = ? ";
2125 push @query_params, "$budget";
2128 if ( $from_placed_on ) {
2129 $query .= " AND creationdate >= ? ";
2130 push @query_params, $from_placed_on;
2133 if ( $to_placed_on ) {
2134 $query .= " AND creationdate <= ? ";
2135 push @query_params, $to_placed_on;
2138 if ( defined $orderstatus and $orderstatus ne '') {
2139 $query .= " AND aqorders.orderstatus = ? ";
2140 push @query_params, "$orderstatus";
2144 if ($basket =~ m/^\d+$/) {
2145 $query .= " AND aqorders.basketno = ? ";
2146 push @query_params, $basket;
2148 $query .= " AND aqbasket.basketname LIKE ? ";
2149 push @query_params, "%$basket%";
2153 if ($booksellerinvoicenumber) {
2154 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2155 push @query_params, "%$booksellerinvoicenumber%";
2158 if ($basketgroupname) {
2159 $query .= " AND aqbasketgroups.name LIKE ? ";
2160 push @query_params, "%$basketgroupname%";
2163 if ( C4::Context->preference("IndependentBranches") ) {
2164 my $userenv = C4::Context->userenv;
2165 if ( $userenv && ($userenv->{flags} || 0) != 1 ) {
2166 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2167 push @query_params, $userenv->{branch};
2170 $query .= " ORDER BY id";
2171 my $sth = $dbh->prepare($query);
2172 $sth->execute( @query_params );
2174 while ( my $line = $sth->fetchrow_hashref ) {
2175 $line->{count} = $cnt++;
2176 $line->{toggle} = 1 if $cnt % 2;
2177 push @order_loop, $line;
2178 $total_qty += ( $line->{quantity} ) ? $line->{quantity} : 0;
2179 $total_qtyreceived += ( $line->{quantityreceived} ) ? $line->{quantityreceived} : 0;
2180 $total_price += ( $line->{quantity} and $line->{ecost} ) ? $line->{quantity} * $line->{ecost} : 0;
2182 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
2185 =head2 GetRecentAcqui
2187 $results = GetRecentAcqui($days);
2189 C<$results> is a ref to a table which containts hashref
2193 sub GetRecentAcqui {
2195 my $dbh = C4::Context->dbh;
2199 ORDER BY timestamp DESC
2202 my $sth = $dbh->prepare($query);
2204 my $results = $sth->fetchall_arrayref({});
2210 $contractlist = &GetContracts($booksellerid, $activeonly);
2212 Looks up the contracts that belong to a bookseller
2214 Returns a list of contracts
2218 =item C<$booksellerid> is the "id" field in the "aqbooksellers" table.
2220 =item C<$activeonly> if exists get only contracts that are still active.
2227 my ( $booksellerid, $activeonly ) = @_;
2228 my $dbh = C4::Context->dbh;
2230 if (! $activeonly) {
2234 WHERE booksellerid=?
2239 WHERE booksellerid=?
2240 AND contractenddate >= CURDATE( )";
2242 my $sth = $dbh->prepare($query);
2243 $sth->execute( $booksellerid );
2245 while (my $data = $sth->fetchrow_hashref ) {
2246 push(@results, $data);
2252 #------------------------------------------------------------#
2256 $contract = &GetContract($contractID);
2258 Looks up the contract that has PRIMKEY (contractnumber) value $contractID
2265 my ( $contractno ) = @_;
2266 my $dbh = C4::Context->dbh;
2270 WHERE contractnumber=?
2273 my $sth = $dbh->prepare($query);
2274 $sth->execute( $contractno );
2275 my $result = $sth->fetchrow_hashref;
2283 &AddClaim($ordernumber);
2285 Add a claim for an order
2292 my ($ordernumber) = @_;
2293 my $dbh = C4::Context->dbh;
2296 claims_count = claims_count + 1,
2297 claimed_date = CURDATE()
2298 WHERE ordernumber = ?
2300 my $sth = $dbh->prepare($query);
2301 $sth->execute($ordernumber);
2306 my @invoices = GetInvoices(
2307 invoicenumber => $invoicenumber,
2308 suppliername => $suppliername,
2309 shipmentdatefrom => $shipmentdatefrom, # ISO format
2310 shipmentdateto => $shipmentdateto, # ISO format
2311 billingdatefrom => $billingdatefrom, # ISO format
2312 billingdateto => $billingdateto, # ISO format
2313 isbneanissn => $isbn_or_ean_or_issn,
2316 publisher => $publisher,
2317 publicationyear => $publicationyear,
2318 branchcode => $branchcode,
2319 order_by => $order_by
2322 Return a list of invoices that match all given criteria.
2324 $order_by is "column_name (asc|desc)", where column_name is any of
2325 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2326 'shipmentcost', 'shipmentcost_budgetid'.
2328 asc is the default if omitted
2335 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2336 closedate shipmentcost shipmentcost_budgetid);
2338 my $dbh = C4::Context->dbh;
2340 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2343 aqorders.datereceived IS NOT NULL,
2344 aqorders.biblionumber,
2347 ) AS receivedbiblios,
2348 SUM(aqorders.quantityreceived) AS receiveditems
2350 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2351 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2352 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2353 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2354 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2359 if($args{supplierid}) {
2360 push @bind_strs, " aqinvoices.booksellerid = ? ";
2361 push @bind_args, $args{supplierid};
2363 if($args{invoicenumber}) {
2364 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2365 push @bind_args, "%$args{invoicenumber}%";
2367 if($args{suppliername}) {
2368 push @bind_strs, " aqbooksellers.name LIKE ? ";
2369 push @bind_args, "%$args{suppliername}%";
2371 if($args{shipmentdatefrom}) {
2372 push @bind_strs, " aqinvoices.shipementdate >= ? ";
2373 push @bind_args, $args{shipmentdatefrom};
2375 if($args{shipmentdateto}) {
2376 push @bind_strs, " aqinvoices.shipementdate <= ? ";
2377 push @bind_args, $args{shipmentdateto};
2379 if($args{billingdatefrom}) {
2380 push @bind_strs, " aqinvoices.billingdate >= ? ";
2381 push @bind_args, $args{billingdatefrom};
2383 if($args{billingdateto}) {
2384 push @bind_strs, " aqinvoices.billingdate <= ? ";
2385 push @bind_args, $args{billingdateto};
2387 if($args{isbneanissn}) {
2388 push @bind_strs, " (biblioitems.isbn LIKE ? OR biblioitems.ean LIKE ? OR biblioitems.issn LIKE ? ) ";
2389 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2392 push @bind_strs, " biblio.title LIKE ? ";
2393 push @bind_args, $args{title};
2396 push @bind_strs, " biblio.author LIKE ? ";
2397 push @bind_args, $args{author};
2399 if($args{publisher}) {
2400 push @bind_strs, " biblioitems.publishercode LIKE ? ";
2401 push @bind_args, $args{publisher};
2403 if($args{publicationyear}) {
2404 push @bind_strs, " biblioitems.publicationyear = ? ";
2405 push @bind_args, $args{publicationyear};
2407 if($args{branchcode}) {
2408 push @bind_strs, " aqorders.branchcode = ? ";
2409 push @bind_args, $args{branchcode};
2412 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2413 $query .= " GROUP BY aqinvoices.invoiceid ";
2415 if($args{order_by}) {
2416 my ($column, $direction) = split / /, $args{order_by};
2417 if(grep /^$column$/, @columns) {
2418 $direction ||= 'ASC';
2419 $query .= " ORDER BY $column $direction";
2423 my $sth = $dbh->prepare($query);
2424 $sth->execute(@bind_args);
2426 my $results = $sth->fetchall_arrayref({});
2432 my $invoice = GetInvoice($invoiceid);
2434 Get informations about invoice with given $invoiceid
2436 Return a hash filled with aqinvoices.* fields
2441 my ($invoiceid) = @_;
2444 return unless $invoiceid;
2446 my $dbh = C4::Context->dbh;
2452 my $sth = $dbh->prepare($query);
2453 $sth->execute($invoiceid);
2455 $invoice = $sth->fetchrow_hashref;
2459 =head3 GetInvoiceDetails
2461 my $invoice = GetInvoiceDetails($invoiceid)
2463 Return informations about an invoice + the list of related order lines
2465 Orders informations are in $invoice->{orders} (array ref)
2469 sub GetInvoiceDetails {
2470 my ($invoiceid) = @_;
2472 if ( !defined $invoiceid ) {
2473 carp 'GetInvoiceDetails called without an invoiceid';
2477 my $dbh = C4::Context->dbh;
2479 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2481 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2484 my $sth = $dbh->prepare($query);
2485 $sth->execute($invoiceid);
2487 my $invoice = $sth->fetchrow_hashref;
2490 SELECT aqorders.*, biblio.*, aqbasket.basketname
2492 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2493 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2496 $sth = $dbh->prepare($query);
2497 $sth->execute($invoiceid);
2498 $invoice->{orders} = $sth->fetchall_arrayref({});
2499 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2506 my $invoiceid = AddInvoice(
2507 invoicenumber => $invoicenumber,
2508 booksellerid => $booksellerid,
2509 shipmentdate => $shipmentdate,
2510 billingdate => $billingdate,
2511 closedate => $closedate,
2512 shipmentcost => $shipmentcost,
2513 shipmentcost_budgetid => $shipmentcost_budgetid
2516 Create a new invoice and return its id or undef if it fails.
2523 return unless(%invoice and $invoice{invoicenumber});
2525 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2526 closedate shipmentcost shipmentcost_budgetid);
2530 foreach my $key (keys %invoice) {
2531 if(0 < grep(/^$key$/, @columns)) {
2532 push @set_strs, "$key = ?";
2533 push @set_args, ($invoice{$key} || undef);
2539 my $dbh = C4::Context->dbh;
2540 my $query = "INSERT INTO aqinvoices SET ";
2541 $query .= join (",", @set_strs);
2542 my $sth = $dbh->prepare($query);
2543 $rv = $sth->execute(@set_args);
2545 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2554 invoiceid => $invoiceid, # Mandatory
2555 invoicenumber => $invoicenumber,
2556 booksellerid => $booksellerid,
2557 shipmentdate => $shipmentdate,
2558 billingdate => $billingdate,
2559 closedate => $closedate,
2560 shipmentcost => $shipmentcost,
2561 shipmentcost_budgetid => $shipmentcost_budgetid
2564 Modify an invoice, invoiceid is mandatory.
2566 Return undef if it fails.
2573 return unless(%invoice and $invoice{invoiceid});
2575 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2576 closedate shipmentcost shipmentcost_budgetid);
2580 foreach my $key (keys %invoice) {
2581 if(0 < grep(/^$key$/, @columns)) {
2582 push @set_strs, "$key = ?";
2583 push @set_args, ($invoice{$key} || undef);
2587 my $dbh = C4::Context->dbh;
2588 my $query = "UPDATE aqinvoices SET ";
2589 $query .= join(",", @set_strs);
2590 $query .= " WHERE invoiceid = ?";
2592 my $sth = $dbh->prepare($query);
2593 $sth->execute(@set_args, $invoice{invoiceid});
2598 CloseInvoice($invoiceid);
2602 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2607 my ($invoiceid) = @_;
2609 return unless $invoiceid;
2611 my $dbh = C4::Context->dbh;
2614 SET closedate = CAST(NOW() AS DATE)
2617 my $sth = $dbh->prepare($query);
2618 $sth->execute($invoiceid);
2621 =head3 ReopenInvoice
2623 ReopenInvoice($invoiceid);
2627 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2632 my ($invoiceid) = @_;
2634 return unless $invoiceid;
2636 my $dbh = C4::Context->dbh;
2639 SET closedate = NULL
2642 my $sth = $dbh->prepare($query);
2643 $sth->execute($invoiceid);
2648 DelInvoice($invoiceid);
2650 Delete an invoice if there are no items attached to it.
2655 my ($invoiceid) = @_;
2657 return unless $invoiceid;
2659 my $dbh = C4::Context->dbh;
2665 my $sth = $dbh->prepare($query);
2666 $sth->execute($invoiceid);
2667 my $res = $sth->fetchrow_arrayref;
2668 if ( $res && $res->[0] == 0 ) {
2670 DELETE FROM aqinvoices
2673 my $sth = $dbh->prepare($query);
2674 return ( $sth->execute($invoiceid) > 0 );
2679 =head3 MergeInvoices
2681 MergeInvoices($invoiceid, \@sourceids);
2683 Merge the invoices identified by the IDs in \@sourceids into
2684 the invoice identified by $invoiceid.
2689 my ($invoiceid, $sourceids) = @_;
2691 return unless $invoiceid;
2692 foreach my $sourceid (@$sourceids) {
2693 next if $sourceid == $invoiceid;
2694 my $source = GetInvoiceDetails($sourceid);
2695 foreach my $order (@{$source->{'orders'}}) {
2696 $order->{'invoiceid'} = $invoiceid;
2699 DelInvoice($source->{'invoiceid'});
2704 =head3 GetBiblioCountByBasketno
2706 $biblio_count = &GetBiblioCountByBasketno($basketno);
2708 Looks up the biblio's count that has basketno value $basketno
2714 sub GetBiblioCountByBasketno {
2715 my ($basketno) = @_;
2716 my $dbh = C4::Context->dbh;
2718 SELECT COUNT( DISTINCT( biblionumber ) )
2721 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2724 my $sth = $dbh->prepare($query);
2725 $sth->execute($basketno);
2726 return $sth->fetchrow;
2734 Koha Development Team <http://koha-community.org/>