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.
25 use C4::Dates qw(format_date format_date_in_iso);
31 use C4::Bookseller qw(GetBookSellerFromId);
32 use C4::Templates qw(gettemplate);
33 use Koha::DateUtils qw( dt_from_string output_pref );
34 use Koha::Acquisition::Order;
39 use vars qw($VERSION @ISA @EXPORT);
42 # set the version for version checking
43 $VERSION = 3.07.00.049;
47 &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
48 &GetBasketAsCSV &GetBasketGroupAsCSV
49 &GetBasketsByBookseller &GetBasketsByBasketgroup
50 &GetBasketsInfosByBookseller
52 &GetBasketUsers &ModBasketUsers
57 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
58 &GetBasketgroups &ReOpenBasketgroup
60 &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
61 &GetLateOrders &GetOrderFromItemnumber
62 &SearchOrders &GetHistory &GetRecentAcqui
63 &ModReceiveOrder &CancelReceipt
64 &GetCancelledOrders &TransferOrder
65 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
68 &GetParcels &GetParcel
80 &GetItemnumbersFromOrder
83 &GetBiblioCountByBasketno
91 sub GetOrderFromItemnumber {
92 my ($itemnumber) = @_;
93 my $dbh = C4::Context->dbh;
96 SELECT * from aqorders LEFT JOIN aqorders_items
97 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
98 WHERE itemnumber = ? |;
100 my $sth = $dbh->prepare($query);
104 $sth->execute($itemnumber);
106 my $order = $sth->fetchrow_hashref;
111 # Returns the itemnumber(s) associated with the ordernumber given in parameter
112 sub GetItemnumbersFromOrder {
113 my ($ordernumber) = @_;
114 my $dbh = C4::Context->dbh;
115 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
116 my $sth = $dbh->prepare($query);
117 $sth->execute($ordernumber);
120 while (my $order = $sth->fetchrow_hashref) {
121 push @tab, $order->{'itemnumber'};
135 C4::Acquisition - Koha functions for dealing with orders and acquisitions
143 The functions in this module deal with acquisitions, managing book
144 orders, basket and parcels.
148 =head2 FUNCTIONS ABOUT BASKETS
152 $aqbasket = &GetBasket($basketnumber);
154 get all basket informations in aqbasket for a given basket
156 B<returns:> informations for a given basket returned as a hashref.
162 my $dbh = C4::Context->dbh;
165 concat( b.firstname,' ',b.surname) AS authorisedbyname
167 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
170 my $sth=$dbh->prepare($query);
171 $sth->execute($basketno);
172 my $basket = $sth->fetchrow_hashref;
176 #------------------------------------------------------------#
180 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
181 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace );
183 Create a new basket in aqbasket table
187 =item C<$booksellerid> is a foreign key in the aqbasket table
189 =item C<$authorizedby> is the username of who created the basket
193 The other parameters are optional, see ModBasketHeader for more info on them.
198 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
199 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
200 $billingplace ) = @_;
201 my $dbh = C4::Context->dbh;
203 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
204 . 'VALUES (now(),?,?)';
205 $dbh->do( $query, {}, $booksellerid, $authorisedby );
207 my $basket = $dbh->{mysql_insertid};
208 $basketname ||= q{}; # default to empty strings
210 $basketbooksellernote ||= q{};
211 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
212 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace );
216 #------------------------------------------------------------#
220 &CloseBasket($basketno);
222 close a basket (becomes unmodifiable, except for receives)
228 my $dbh = C4::Context->dbh;
234 my $sth = $dbh->prepare($query);
235 $sth->execute($basketno);
237 my @orders = GetOrders($basketno);
238 foreach my $order (@orders) {
241 SET orderstatus = 'ordered'
242 WHERE ordernumber = ?;
244 $sth = $dbh->prepare($query);
245 $sth->execute($order->{'ordernumber'});
251 &ReopenBasket($basketno);
259 my $dbh = C4::Context->dbh;
265 my $sth = $dbh->prepare($query);
266 $sth->execute($basketno);
268 my @orders = GetOrders($basketno);
269 foreach my $order (@orders) {
272 SET orderstatus = 'new'
273 WHERE ordernumber = ?;
275 $sth = $dbh->prepare($query);
276 $sth->execute($order->{'ordernumber'});
280 #------------------------------------------------------------#
282 =head3 GetBasketAsCSV
284 &GetBasketAsCSV($basketno);
286 Export a basket as CSV
288 $cgi parameter is needed for column name translation
293 my ($basketno, $cgi) = @_;
294 my $basket = GetBasket($basketno);
295 my @orders = GetOrders($basketno);
296 my $contract = GetContract({
297 contractnumber => $basket->{'contractnumber'}
300 my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
303 foreach my $order (@orders) {
304 my $bd = GetBiblioData( $order->{'biblionumber'} );
306 contractname => $contract->{'contractname'},
307 ordernumber => $order->{'ordernumber'},
308 entrydate => $order->{'entrydate'},
309 isbn => $order->{'isbn'},
310 author => $bd->{'author'},
311 title => $bd->{'title'},
312 publicationyear => $bd->{'publicationyear'},
313 publishercode => $bd->{'publishercode'},
314 collectiontitle => $bd->{'collectiontitle'},
315 notes => $order->{'order_vendornote'},
316 quantity => $order->{'quantity'},
317 rrp => $order->{'rrp'},
318 deliveryplace => C4::Branch::GetBranchName( $basket->{'deliveryplace'} ),
319 billingplace => C4::Branch::GetBranchName( $basket->{'billingplace'} ),
322 contractname author title publishercode collectiontitle notes
323 deliveryplace billingplace
325 # Double the quotes to not be interpreted as a field end
326 $row->{$_} =~ s/"/""/g if $row->{$_};
332 if(defined $a->{publishercode} and defined $b->{publishercode}) {
333 $a->{publishercode} cmp $b->{publishercode};
337 $template->param(rows => \@rows);
339 return $template->output;
343 =head3 GetBasketGroupAsCSV
347 &GetBasketGroupAsCSV($basketgroupid);
349 Export a basket group as CSV
351 $cgi parameter is needed for column name translation
357 sub GetBasketGroupAsCSV {
358 my ($basketgroupid, $cgi) = @_;
359 my $baskets = GetBasketsByBasketgroup($basketgroupid);
361 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
364 for my $basket (@$baskets) {
365 my @orders = GetOrders( $basket->{basketno} );
366 my $contract = GetContract({
367 contractnumber => $basket->{contractnumber}
369 my $bookseller = GetBookSellerFromId( $$basket{booksellerid} );
370 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
372 foreach my $order (@orders) {
373 my $bd = GetBiblioData( $order->{'biblionumber'} );
375 clientnumber => $bookseller->{accountnumber},
376 basketname => $basket->{basketname},
377 ordernumber => $order->{ordernumber},
378 author => $bd->{author},
379 title => $bd->{title},
380 publishercode => $bd->{publishercode},
381 publicationyear => $bd->{publicationyear},
382 collectiontitle => $bd->{collectiontitle},
383 isbn => $order->{isbn},
384 quantity => $order->{quantity},
385 rrp => $order->{rrp},
386 discount => $bookseller->{discount},
387 ecost => $order->{ecost},
388 notes => $order->{order_vendornote},
389 entrydate => $order->{entrydate},
390 booksellername => $bookseller->{name},
391 bookselleraddress => $bookseller->{address1},
392 booksellerpostal => $bookseller->{postal},
393 contractnumber => $contract->{contractnumber},
394 contractname => $contract->{contractname},
395 basketgroupdeliveryplace => C4::Branch::GetBranchName( $basketgroup->{deliveryplace} ),
396 basketgroupbillingplace => C4::Branch::GetBranchName( $basketgroup->{billingplace} ),
397 basketdeliveryplace => C4::Branch::GetBranchName( $basket->{deliveryplace} ),
398 basketbillingplace => C4::Branch::GetBranchName( $basket->{billingplace} ),
401 basketname author title publishercode collectiontitle notes
402 booksellername bookselleraddress booksellerpostal contractname
403 basketgroupdeliveryplace basketgroupbillingplace
404 basketdeliveryplace basketbillingplace
406 # Double the quotes to not be interpreted as a field end
407 $row->{$_} =~ s/"/""/g if $row->{$_};
412 $template->param(rows => \@rows);
414 return $template->output;
418 =head3 CloseBasketgroup
420 &CloseBasketgroup($basketgroupno);
426 sub CloseBasketgroup {
427 my ($basketgroupno) = @_;
428 my $dbh = C4::Context->dbh;
429 my $sth = $dbh->prepare("
430 UPDATE aqbasketgroups
434 $sth->execute($basketgroupno);
437 #------------------------------------------------------------#
439 =head3 ReOpenBaskergroup($basketgroupno)
441 &ReOpenBaskergroup($basketgroupno);
447 sub ReOpenBasketgroup {
448 my ($basketgroupno) = @_;
449 my $dbh = C4::Context->dbh;
450 my $sth = $dbh->prepare("
451 UPDATE aqbasketgroups
455 $sth->execute($basketgroupno);
458 #------------------------------------------------------------#
463 &DelBasket($basketno);
465 Deletes the basket that has basketno field $basketno in the aqbasket table.
469 =item C<$basketno> is the primary key of the basket in the aqbasket table.
476 my ( $basketno ) = @_;
477 my $query = "DELETE FROM aqbasket WHERE basketno=?";
478 my $dbh = C4::Context->dbh;
479 my $sth = $dbh->prepare($query);
480 $sth->execute($basketno);
484 #------------------------------------------------------------#
488 &ModBasket($basketinfo);
490 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
494 =item C<$basketno> is the primary key of the basket in the aqbasket table.
501 my $basketinfo = shift;
502 my $query = "UPDATE aqbasket SET ";
504 foreach my $key (keys %$basketinfo){
505 if ($key ne 'basketno'){
506 $query .= "$key=?, ";
507 push(@params, $basketinfo->{$key} || undef );
510 # get rid of the "," at the end of $query
511 if (substr($query, length($query)-2) eq ', '){
516 $query .= "WHERE basketno=?";
517 push(@params, $basketinfo->{'basketno'});
518 my $dbh = C4::Context->dbh;
519 my $sth = $dbh->prepare($query);
520 $sth->execute(@params);
525 #------------------------------------------------------------#
527 =head3 ModBasketHeader
529 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
531 Modifies a basket's header.
535 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
537 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
539 =item C<$note> is the "note" field in the "aqbasket" table;
541 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
543 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
545 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
547 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
549 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
555 sub ModBasketHeader {
556 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace) = @_;
559 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?
563 my $dbh = C4::Context->dbh;
564 my $sth = $dbh->prepare($query);
565 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $basketno);
567 if ( $contractnumber ) {
568 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
569 my $sth2 = $dbh->prepare($query2);
570 $sth2->execute($contractnumber,$basketno);
575 #------------------------------------------------------------#
577 =head3 GetBasketsByBookseller
579 @results = &GetBasketsByBookseller($booksellerid, $extra);
581 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
585 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
587 =item C<$extra> is the extra sql parameters, can be
589 $extra->{groupby}: group baskets by column
590 ex. $extra->{groupby} = aqbasket.basketgroupid
591 $extra->{orderby}: order baskets by column
592 $extra->{limit}: limit number of results (can be helpful for pagination)
598 sub GetBasketsByBookseller {
599 my ($booksellerid, $extra) = @_;
600 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
602 if ($extra->{groupby}) {
603 $query .= " GROUP by $extra->{groupby}";
605 if ($extra->{orderby}){
606 $query .= " ORDER by $extra->{orderby}";
608 if ($extra->{limit}){
609 $query .= " LIMIT $extra->{limit}";
612 my $dbh = C4::Context->dbh;
613 my $sth = $dbh->prepare($query);
614 $sth->execute($booksellerid);
615 return $sth->fetchall_arrayref({});
618 =head3 GetBasketsInfosByBookseller
620 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
622 The optional second parameter allbaskets is a boolean allowing you to
623 select all baskets from the supplier; by default only active baskets (open or
624 closed but still something to receive) are returned.
626 Returns in a arrayref of hashref all about booksellers baskets, plus:
627 total_biblios: Number of distinct biblios in basket
628 total_items: Number of items in basket
629 expected_items: Number of non-received items in basket
633 sub GetBasketsInfosByBookseller {
634 my ($supplierid, $allbaskets) = @_;
636 return unless $supplierid;
638 my $dbh = C4::Context->dbh;
641 SUM(aqorders.quantity) AS total_items,
643 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
644 ) AS total_items_cancelled,
645 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
647 IF(aqorders.datereceived IS NULL
648 AND aqorders.datecancellationprinted IS NULL
653 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
654 WHERE booksellerid = ?};
656 unless ( $allbaskets ) {
657 $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
659 $query.=" GROUP BY aqbasket.basketno";
661 my $sth = $dbh->prepare($query);
662 $sth->execute($supplierid);
663 my $baskets = $sth->fetchall_arrayref({});
665 # Retrieve the number of biblios cancelled
666 my $cancelled_biblios = $dbh->selectall_hashref( q|
667 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
669 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
670 WHERE booksellerid = ?
671 AND aqorders.orderstatus = 'cancelled'
672 GROUP BY aqbasket.basketno
673 |, 'basketno', {}, $supplierid );
675 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
681 =head3 GetBasketUsers
683 $basketusers_ids = &GetBasketUsers($basketno);
685 Returns a list of all borrowernumbers that are in basket users list
690 my $basketno = shift;
692 return unless $basketno;
695 SELECT borrowernumber
699 my $dbh = C4::Context->dbh;
700 my $sth = $dbh->prepare($query);
701 $sth->execute($basketno);
702 my $results = $sth->fetchall_arrayref( {} );
705 foreach (@$results) {
706 push @borrowernumbers, $_->{'borrowernumber'};
709 return @borrowernumbers;
712 =head3 ModBasketUsers
714 my @basketusers_ids = (1, 2, 3);
715 &ModBasketUsers($basketno, @basketusers_ids);
717 Delete all users from basket users list, and add users in C<@basketusers_ids>
723 my ($basketno, @basketusers_ids) = @_;
725 return unless $basketno;
727 my $dbh = C4::Context->dbh;
729 DELETE FROM aqbasketusers
732 my $sth = $dbh->prepare($query);
733 $sth->execute($basketno);
736 INSERT INTO aqbasketusers (basketno, borrowernumber)
739 $sth = $dbh->prepare($query);
740 foreach my $basketuser_id (@basketusers_ids) {
741 $sth->execute($basketno, $basketuser_id);
746 =head3 CanUserManageBasket
748 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
749 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
751 Check if a borrower can manage a basket, according to system preference
752 AcqViewBaskets, user permissions and basket properties (creator, users list,
755 First parameter can be either a borrowernumber or a hashref as returned by
756 C4::Members::GetMember.
758 Second parameter can be either a basketno or a hashref as returned by
759 C4::Acquisition::GetBasket.
761 The third parameter is optional. If given, it should be a hashref as returned
762 by C4::Auth::getuserflags. If not, getuserflags is called.
764 If user is authorised to manage basket, returns 1.
769 sub CanUserManageBasket {
770 my ($borrower, $basket, $userflags) = @_;
772 if (!ref $borrower) {
773 $borrower = C4::Members::GetMember(borrowernumber => $borrower);
776 $basket = GetBasket($basket);
779 return 0 unless ($basket and $borrower);
781 my $borrowernumber = $borrower->{borrowernumber};
782 my $basketno = $basket->{basketno};
784 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
786 if (!defined $userflags) {
787 my $dbh = C4::Context->dbh;
788 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
789 $sth->execute($borrowernumber);
790 my ($flags) = $sth->fetchrow_array;
793 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
796 unless ($userflags->{superlibrarian}
797 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
798 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
800 if (not exists $userflags->{acquisition}) {
804 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
805 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
809 if ($AcqViewBaskets eq 'user'
810 && $basket->{authorisedby} != $borrowernumber
811 && grep($borrowernumber, GetBasketUsers($basketno)) == 0) {
815 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
816 && $basket->{branch} ne $borrower->{branchcode}) {
824 #------------------------------------------------------------#
826 =head3 GetBasketsByBasketgroup
828 $baskets = &GetBasketsByBasketgroup($basketgroupid);
830 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
834 sub GetBasketsByBasketgroup {
835 my $basketgroupid = shift;
837 SELECT *, aqbasket.booksellerid as booksellerid
839 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
841 my $dbh = C4::Context->dbh;
842 my $sth = $dbh->prepare($query);
843 $sth->execute($basketgroupid);
844 return $sth->fetchall_arrayref({});
847 #------------------------------------------------------------#
849 =head3 NewBasketgroup
851 $basketgroupid = NewBasketgroup(\%hashref);
853 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
855 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
857 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
859 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
861 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
863 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
865 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
867 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
869 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
874 my $basketgroupinfo = shift;
875 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
876 my $query = "INSERT INTO aqbasketgroups (";
878 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
879 if ( defined $basketgroupinfo->{$field} ) {
880 $query .= "$field, ";
881 push(@params, $basketgroupinfo->{$field});
884 $query .= "booksellerid) VALUES (";
889 push(@params, $basketgroupinfo->{'booksellerid'});
890 my $dbh = C4::Context->dbh;
891 my $sth = $dbh->prepare($query);
892 $sth->execute(@params);
893 my $basketgroupid = $dbh->{'mysql_insertid'};
894 if( $basketgroupinfo->{'basketlist'} ) {
895 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
896 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
897 my $sth2 = $dbh->prepare($query2);
898 $sth2->execute($basketgroupid, $basketno);
901 return $basketgroupid;
904 #------------------------------------------------------------#
906 =head3 ModBasketgroup
908 ModBasketgroup(\%hashref);
910 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
912 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
914 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
916 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
918 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
920 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
922 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
924 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
926 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
931 my $basketgroupinfo = shift;
932 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
933 my $dbh = C4::Context->dbh;
934 my $query = "UPDATE aqbasketgroups SET ";
936 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
937 if ( defined $basketgroupinfo->{$field} ) {
938 $query .= "$field=?, ";
939 push(@params, $basketgroupinfo->{$field});
944 $query .= " WHERE id=?";
945 push(@params, $basketgroupinfo->{'id'});
946 my $sth = $dbh->prepare($query);
947 $sth->execute(@params);
949 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
950 $sth->execute($basketgroupinfo->{'id'});
952 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
953 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
954 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
955 $sth->execute($basketgroupinfo->{'id'}, $basketno);
961 #------------------------------------------------------------#
963 =head3 DelBasketgroup
965 DelBasketgroup($basketgroupid);
967 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
971 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
978 my $basketgroupid = shift;
979 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
980 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
981 my $dbh = C4::Context->dbh;
982 my $sth = $dbh->prepare($query);
983 $sth->execute($basketgroupid);
987 #------------------------------------------------------------#
990 =head2 FUNCTIONS ABOUT ORDERS
992 =head3 GetBasketgroup
994 $basketgroup = &GetBasketgroup($basketgroupid);
996 Returns a reference to the hash containing all information about the basketgroup.
1000 sub GetBasketgroup {
1001 my $basketgroupid = shift;
1002 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1003 my $dbh = C4::Context->dbh;
1004 my $result_set = $dbh->selectall_arrayref(
1005 'SELECT * FROM aqbasketgroups WHERE id=?',
1009 return $result_set->[0]; # id is unique
1012 #------------------------------------------------------------#
1014 =head3 GetBasketgroups
1016 $basketgroups = &GetBasketgroups($booksellerid);
1018 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1022 sub GetBasketgroups {
1023 my $booksellerid = shift;
1024 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1025 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1026 my $dbh = C4::Context->dbh;
1027 my $sth = $dbh->prepare($query);
1028 $sth->execute($booksellerid);
1029 return $sth->fetchall_arrayref({});
1032 #------------------------------------------------------------#
1034 =head2 FUNCTIONS ABOUT ORDERS
1038 @orders = &GetOrders($basketnumber, $orderby);
1040 Looks up the pending (non-cancelled) orders with the given basket
1041 number. If C<$booksellerID> is non-empty, only orders from that seller
1045 C<&basket> returns a two-element array. C<@orders> is an array of
1046 references-to-hash, whose keys are the fields from the aqorders,
1047 biblio, and biblioitems tables in the Koha database.
1052 my ( $basketno, $orderby ) = @_;
1053 return () unless $basketno;
1054 my $dbh = C4::Context->dbh;
1056 SELECT biblio.*,biblioitems.*,
1059 aqorders_transfers.ordernumber_from AS transferred_from,
1060 aqorders_transfers.timestamp AS transferred_from_timestamp
1062 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1063 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1064 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1065 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1067 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1070 $orderby = "biblioitems.publishercode,biblio.title" unless $orderby;
1071 $query .= " ORDER BY $orderby";
1073 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1074 return @{$result_set};
1078 #------------------------------------------------------------#
1079 =head3 GetOrdersByBiblionumber
1081 @orders = &GetOrdersByBiblionumber($biblionumber);
1083 Looks up the orders with linked to a specific $biblionumber, including
1084 cancelled orders and received orders.
1087 C<@orders> is an array of references-to-hash, whose keys are the
1088 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1092 sub GetOrdersByBiblionumber {
1093 my $biblionumber = shift;
1094 return unless $biblionumber;
1095 my $dbh = C4::Context->dbh;
1097 SELECT biblio.*,biblioitems.*,
1101 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1102 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1103 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1104 WHERE aqorders.biblionumber=?
1107 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1108 return @{$result_set};
1112 #------------------------------------------------------------#
1116 $order = &GetOrder($ordernumber);
1118 Looks up an order by order number.
1120 Returns a reference-to-hash describing the order. The keys of
1121 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1126 my ($ordernumber) = @_;
1127 return unless $ordernumber;
1129 my $dbh = C4::Context->dbh;
1130 my $query = qq{SELECT
1134 aqbasket.basketname,
1135 borrowers.branchcode,
1136 biblioitems.publicationyear,
1137 biblio.copyrightdate,
1138 biblioitems.editionstatement,
1142 biblioitems.publishercode,
1143 aqorders.rrp AS unitpricesupplier,
1144 aqorders.ecost AS unitpricelib,
1145 aqorders.claims_count AS claims_count,
1146 aqorders.claimed_date AS claimed_date,
1147 aqbudgets.budget_name AS budget,
1148 aqbooksellers.name AS supplier,
1149 aqbooksellers.id AS supplierid,
1150 biblioitems.publishercode AS publisher,
1151 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1152 DATE(aqbasket.closedate) AS orderdate,
1153 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1154 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1155 DATEDIFF(CURDATE( ),closedate) AS latesince
1156 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1157 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1158 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1159 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1160 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1161 WHERE aqorders.basketno = aqbasket.basketno
1164 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1166 # result_set assumed to contain 1 match
1167 return $result_set->[0];
1170 =head3 GetLastOrderNotReceivedFromSubscriptionid
1172 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1174 Returns a reference-to-hash describing the last order not received for a subscription.
1178 sub GetLastOrderNotReceivedFromSubscriptionid {
1179 my ( $subscriptionid ) = @_;
1180 my $dbh = C4::Context->dbh;
1182 SELECT * FROM aqorders
1183 LEFT JOIN subscription
1184 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1185 WHERE aqorders.subscriptionid = ?
1186 AND aqorders.datereceived IS NULL
1190 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1192 # result_set assumed to contain 1 match
1193 return $result_set->[0];
1196 =head3 GetLastOrderReceivedFromSubscriptionid
1198 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1200 Returns a reference-to-hash describing the last order received for a subscription.
1204 sub GetLastOrderReceivedFromSubscriptionid {
1205 my ( $subscriptionid ) = @_;
1206 my $dbh = C4::Context->dbh;
1208 SELECT * FROM aqorders
1209 LEFT JOIN subscription
1210 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1211 WHERE aqorders.subscriptionid = ?
1212 AND aqorders.datereceived =
1214 SELECT MAX( aqorders.datereceived )
1216 LEFT JOIN subscription
1217 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1218 WHERE aqorders.subscriptionid = ?
1219 AND aqorders.datereceived IS NOT NULL
1221 ORDER BY ordernumber DESC
1225 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1227 # result_set assumed to contain 1 match
1228 return $result_set->[0];
1232 #------------------------------------------------------------#
1236 &ModOrder(\%hashref);
1238 Modifies an existing order. Updates the order with order number
1239 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1240 other keys of the hash update the fields with the same name in the aqorders
1241 table of the Koha database.
1246 my $orderinfo = shift;
1248 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1249 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1251 my $dbh = C4::Context->dbh;
1254 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1255 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1257 # delete($orderinfo->{'branchcode'});
1258 # the hash contains a lot of entries not in aqorders, so get the columns ...
1259 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1261 my $colnames = $sth->{NAME};
1262 #FIXME Be careful. If aqorders would have columns with diacritics,
1263 #you should need to decode what you get back from NAME.
1264 #See report 10110 and guided_reports.pl
1265 my $query = "UPDATE aqorders SET ";
1267 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1268 # ... and skip hash entries that are not in the aqorders table
1269 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1270 next unless grep(/^$orderinfokey$/, @$colnames);
1271 $query .= "$orderinfokey=?, ";
1272 push(@params, $orderinfo->{$orderinfokey});
1275 $query .= "timestamp=NOW() WHERE ordernumber=?";
1276 push(@params, $orderinfo->{'ordernumber'} );
1277 $sth = $dbh->prepare($query);
1278 $sth->execute(@params);
1282 #------------------------------------------------------------#
1286 ModItemOrder($itemnumber, $ordernumber);
1288 Modifies the ordernumber of an item in aqorders_items.
1293 my ($itemnumber, $ordernumber) = @_;
1295 return unless ($itemnumber and $ordernumber);
1297 my $dbh = C4::Context->dbh;
1299 UPDATE aqorders_items
1301 WHERE itemnumber = ?
1303 my $sth = $dbh->prepare($query);
1304 return $sth->execute($ordernumber, $itemnumber);
1307 #------------------------------------------------------------#
1309 =head3 GetCancelledOrders
1311 my @orders = GetCancelledOrders($basketno, $orderby);
1313 Returns cancelled orders for a basket
1317 sub GetCancelledOrders {
1318 my ( $basketno, $orderby ) = @_;
1320 return () unless $basketno;
1322 my $dbh = C4::Context->dbh;
1329 aqorders_transfers.ordernumber_to AS transferred_to,
1330 aqorders_transfers.timestamp AS transferred_to_timestamp
1332 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1333 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1334 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1335 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1337 AND (datecancellationprinted IS NOT NULL
1338 AND datecancellationprinted <> '0000-00-00')
1341 $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc"
1343 $query .= " ORDER BY $orderby";
1344 my $sth = $dbh->prepare($query);
1345 $sth->execute($basketno);
1346 my $results = $sth->fetchall_arrayref( {} );
1352 #------------------------------------------------------------#
1354 =head3 ModReceiveOrder
1357 biblionumber => $biblionumber,
1358 ordernumber => $ordernumber,
1359 quantityreceived => $quantityreceived,
1363 invoiceid => $invoiceid,
1365 budget_id => $budget_id,
1366 datereceived => $datereceived,
1367 received_itemnumbers => \@received_itemnumbers,
1368 order_internalnote => $order_internalnote,
1369 order_vendornote => $order_vendornote,
1372 Updates an order, to reflect the fact that it was received, at least
1373 in part. All arguments not mentioned below update the fields with the
1374 same name in the aqorders table of the Koha database.
1376 If a partial order is received, splits the order into two.
1378 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1384 sub ModReceiveOrder {
1385 my ( $params ) = @_;
1386 my $biblionumber = $params->{biblionumber};
1387 my $ordernumber = $params->{ordernumber};
1388 my $quantrec = $params->{quantityreceived};
1389 my $user = $params->{user};
1390 my $cost = $params->{cost};
1391 my $ecost = $params->{ecost};
1392 my $invoiceid = $params->{invoiceid};
1393 my $rrp = $params->{rrp};
1394 my $budget_id = $params->{budget_id};
1395 my $datereceived = $params->{datereceived};
1396 my $received_items = $params->{received_items};
1397 my $order_internalnote = $params->{order_internalnote};
1398 my $order_vendornote = $params->{order_vendornote};
1400 my $dbh = C4::Context->dbh;
1401 $datereceived = C4::Dates->output('iso') unless $datereceived;
1402 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1403 if ($suggestionid) {
1404 ModSuggestion( {suggestionid=>$suggestionid,
1405 STATUS=>'AVAILABLE',
1406 biblionumber=> $biblionumber}
1410 my $result_set = $dbh->selectall_arrayref(
1411 q{SELECT * FROM aqorders WHERE biblionumber=? AND aqorders.ordernumber=?},
1412 { Slice => {} }, $biblionumber, $ordernumber
1415 # we assume we have a unique order
1416 my $order = $result_set->[0];
1418 my $new_ordernumber = $ordernumber;
1419 if ( $order->{quantity} > $quantrec ) {
1420 # Split order line in two parts: the first is the original order line
1421 # without received items (the quantity is decreased),
1422 # the second part is a new order line with quantity=quantityrec
1423 # (entirely received)
1427 orderstatus = 'partial'|;
1428 $query .= q|, order_internalnote = ?| if defined $order_internalnote;
1429 $query .= q|, order_vendornote = ?| if defined $order_vendornote;
1430 $query .= q| WHERE ordernumber = ?|;
1431 my $sth = $dbh->prepare($query);
1434 $order->{quantity} - $quantrec,
1435 ( defined $order_internalnote ? $order_internalnote : () ),
1436 ( defined $order_vendornote ? $order_vendornote : () ),
1440 delete $order->{'ordernumber'};
1441 $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
1442 $order->{'quantity'} = $quantrec;
1443 $order->{'quantityreceived'} = $quantrec;
1444 $order->{'datereceived'} = $datereceived;
1445 $order->{'invoiceid'} = $invoiceid;
1446 $order->{'unitprice'} = $cost;
1447 $order->{'rrp'} = $rrp;
1448 $order->{ecost} = $ecost;
1449 $order->{'orderstatus'} = 'complete';
1450 $new_ordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber};
1452 if ($received_items) {
1453 foreach my $itemnumber (@$received_items) {
1454 ModItemOrder($itemnumber, $new_ordernumber);
1460 set quantityreceived=?,datereceived=?,invoiceid=?,
1461 unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'|;
1462 $query .= q|, order_internalnote = ?| if defined $order_internalnote;
1463 $query .= q|, order_vendornote = ?| if defined $order_vendornote;
1464 $query .= q| where biblionumber=? and ordernumber=?|;
1465 my $sth = $dbh->prepare( $query );
1474 ( defined $order_internalnote ? $order_internalnote : () ),
1475 ( defined $order_vendornote ? $order_vendornote : () ),
1480 return ($datereceived, $new_ordernumber);
1483 =head3 CancelReceipt
1485 my $parent_ordernumber = CancelReceipt($ordernumber);
1487 Cancel an order line receipt and update the parent order line, as if no
1489 If items are created at receipt (AcqCreateItem = receiving) then delete
1495 my $ordernumber = shift;
1497 return unless $ordernumber;
1499 my $dbh = C4::Context->dbh;
1501 SELECT datereceived, parent_ordernumber, quantity
1503 WHERE ordernumber = ?
1505 my $sth = $dbh->prepare($query);
1506 $sth->execute($ordernumber);
1507 my $order = $sth->fetchrow_hashref;
1509 warn "CancelReceipt: order $ordernumber does not exist";
1512 unless($order->{'datereceived'}) {
1513 warn "CancelReceipt: order $ordernumber is not received";
1517 my $parent_ordernumber = $order->{'parent_ordernumber'};
1519 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1521 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1522 # The order line has no parent, just mark it as not received
1525 SET quantityreceived = ?,
1528 orderstatus = 'ordered'
1529 WHERE ordernumber = ?
1531 $sth = $dbh->prepare($query);
1532 $sth->execute(0, undef, undef, $ordernumber);
1533 _cancel_items_receipt( $ordernumber );
1535 # The order line has a parent, increase parent quantity and delete
1538 SELECT quantity, datereceived
1540 WHERE ordernumber = ?
1542 $sth = $dbh->prepare($query);
1543 $sth->execute($parent_ordernumber);
1544 my $parent_order = $sth->fetchrow_hashref;
1545 unless($parent_order) {
1546 warn "Parent order $parent_ordernumber does not exist.";
1549 if($parent_order->{'datereceived'}) {
1550 warn "CancelReceipt: parent order is received.".
1551 " Can't cancel receipt.";
1557 orderstatus = 'ordered'
1558 WHERE ordernumber = ?
1560 $sth = $dbh->prepare($query);
1561 my $rv = $sth->execute(
1562 $order->{'quantity'} + $parent_order->{'quantity'},
1566 warn "Cannot update parent order line, so do not cancel".
1570 _cancel_items_receipt( $ordernumber, $parent_ordernumber );
1573 DELETE FROM aqorders
1574 WHERE ordernumber = ?
1576 $sth = $dbh->prepare($query);
1577 $sth->execute($ordernumber);
1581 if(C4::Context->preference('AcqCreateItem') eq 'ordering') {
1582 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1584 for my $in ( @itemnumbers ) {
1585 my $biblionumber = C4::Biblio::GetBiblionumberFromItemnumber( $in );
1586 my $frameworkcode = GetFrameworkCode($biblionumber);
1587 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber', $frameworkcode );
1588 my $item = C4::Items::GetMarcItem( $biblionumber, $in );
1589 for my $affect ( @affects ) {
1590 my ( $sf, $v ) = split q{=}, $affect, 2;
1591 foreach ( $item->field($itemfield) ) {
1592 $_->update( $sf => $v );
1595 C4::Items::ModItemFromMarc( $item, $biblionumber, $in );
1600 return $parent_ordernumber;
1603 sub _cancel_items_receipt {
1604 my ( $ordernumber, $parent_ordernumber ) = @_;
1605 $parent_ordernumber ||= $ordernumber;
1607 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1608 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1609 # Remove items that were created at receipt
1611 DELETE FROM items, aqorders_items
1612 USING items, aqorders_items
1613 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1615 my $dbh = C4::Context->dbh;
1616 my $sth = $dbh->prepare($query);
1617 foreach my $itemnumber (@itemnumbers) {
1618 $sth->execute($itemnumber, $itemnumber);
1622 foreach my $itemnumber (@itemnumbers) {
1623 ModItemOrder($itemnumber, $parent_ordernumber);
1628 #------------------------------------------------------------#
1632 @results = &SearchOrders({
1633 ordernumber => $ordernumber,
1635 biblionumber => $biblionumber,
1637 booksellerid => $booksellerid,
1638 basketno => $basketno,
1644 Searches for orders.
1646 C<$owner> Finds order for the logged in user.
1647 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1648 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1651 C<@results> is an array of references-to-hash with the keys are fields
1652 from aqorders, biblio, biblioitems and aqbasket tables.
1657 my ( $params ) = @_;
1658 my $ordernumber = $params->{ordernumber};
1659 my $search = $params->{search};
1660 my $ean = $params->{ean};
1661 my $booksellerid = $params->{booksellerid};
1662 my $basketno = $params->{basketno};
1663 my $basketname = $params->{basketname};
1664 my $basketgroupname = $params->{basketgroupname};
1665 my $owner = $params->{owner};
1666 my $pending = $params->{pending};
1667 my $ordered = $params->{ordered};
1668 my $biblionumber = $params->{biblionumber};
1669 my $budget_id = $params->{budget_id};
1671 my $dbh = C4::Context->dbh;
1674 SELECT aqbasket.basketno,
1676 borrowers.firstname,
1679 biblioitems.biblioitemnumber,
1680 aqbasket.authorisedby,
1681 aqbasket.booksellerid,
1683 aqbasket.creationdate,
1684 aqbasket.basketname,
1685 aqbasketgroups.id as basketgroupid,
1686 aqbasketgroups.name as basketgroupname,
1689 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1690 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1691 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1692 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1693 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1696 # If we search on ordernumber, we retrieve the transfered order if a transfer has been done.
1698 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1702 WHERE (datecancellationprinted is NULL)
1705 if ( $pending or $ordered ) {
1706 $query .= q{ AND (quantity > quantityreceived OR quantityreceived is NULL)};
1709 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1712 my $userenv = C4::Context->userenv;
1713 if ( C4::Context->preference("IndependentBranches") ) {
1714 unless ( C4::Context->IsSuperLibrarian() ) {
1717 borrowers.branchcode = ?
1718 OR borrowers.branchcode = ''
1721 push @args, $userenv->{branch};
1725 if ( $ordernumber ) {
1726 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1727 push @args, ( $ordernumber, $ordernumber );
1729 if ( $biblionumber ) {
1730 $query .= 'AND aqorders.biblionumber = ?';
1731 push @args, $biblionumber;
1734 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1735 push @args, ("%$search%","%$search%","%$search%");
1738 $query .= ' AND biblioitems.ean = ?';
1741 if ( $booksellerid ) {
1742 $query .= 'AND aqbasket.booksellerid = ?';
1743 push @args, $booksellerid;
1746 $query .= 'AND aqbasket.basketno = ?';
1747 push @args, $basketno;
1750 $query .= 'AND aqbasket.basketname LIKE ?';
1751 push @args, "%$basketname%";
1753 if( $basketgroupname ) {
1754 $query .= ' AND aqbasketgroups.name LIKE ?';
1755 push @args, "%$basketgroupname%";
1759 $query .= ' AND aqbasket.authorisedby=? ';
1760 push @args, $userenv->{'number'};
1764 $query .= ' AND aqorders.budget_id = ?';
1765 push @args, $budget_id;
1768 $query .= ' ORDER BY aqbasket.basketno';
1770 my $sth = $dbh->prepare($query);
1771 $sth->execute(@args);
1772 return $sth->fetchall_arrayref({});
1775 #------------------------------------------------------------#
1779 &DelOrder($biblionumber, $ordernumber);
1781 Cancel the order with the given order and biblio numbers. It does not
1782 delete any entries in the aqorders table, it merely marks them as
1788 my ( $bibnum, $ordernumber ) = @_;
1789 my $dbh = C4::Context->dbh;
1792 SET datecancellationprinted=now(), orderstatus='cancelled'
1793 WHERE biblionumber=? AND ordernumber=?
1795 my $sth = $dbh->prepare($query);
1796 $sth->execute( $bibnum, $ordernumber );
1797 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1798 foreach my $itemnumber (@itemnumbers){
1801 biblionumber => $bibnum,
1802 itemnumber => $itemnumber
1809 =head3 TransferOrder
1811 my $newordernumber = TransferOrder($ordernumber, $basketno);
1813 Transfer an order line to a basket.
1814 Mark $ordernumber as cancelled with an internal note 'Cancelled and transfered
1815 to BOOKSELLER on DATE' and create new order with internal note
1816 'Transfered from BOOKSELLER on DATE'.
1817 Move all attached items to the new order.
1818 Received orders cannot be transfered.
1819 Return the ordernumber of created order.
1824 my ($ordernumber, $basketno) = @_;
1826 return unless ($ordernumber and $basketno);
1828 my $order = GetOrder( $ordernumber );
1829 return if $order->{datereceived};
1830 my $basket = GetBasket($basketno);
1831 return unless $basket;
1833 my $dbh = C4::Context->dbh;
1834 my ($query, $sth, $rv);
1838 SET datecancellationprinted = CAST(NOW() AS date)
1839 WHERE ordernumber = ?
1841 $sth = $dbh->prepare($query);
1842 $rv = $sth->execute($ordernumber);
1844 delete $order->{'ordernumber'};
1845 delete $order->{parent_ordernumber};
1846 $order->{'basketno'} = $basketno;
1848 my $newordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber};
1851 UPDATE aqorders_items
1853 WHERE ordernumber = ?
1855 $sth = $dbh->prepare($query);
1856 $sth->execute($newordernumber, $ordernumber);
1859 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1862 $sth = $dbh->prepare($query);
1863 $sth->execute($ordernumber, $newordernumber);
1865 return $newordernumber;
1868 =head2 FUNCTIONS ABOUT PARCELS
1872 #------------------------------------------------------------#
1876 @results = &GetParcel($booksellerid, $code, $date);
1878 Looks up all of the received items from the supplier with the given
1879 bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders.
1881 C<@results> is an array of references-to-hash. The keys of each element are fields from
1882 the aqorders, biblio, and biblioitems tables of the Koha database.
1884 C<@results> is sorted alphabetically by book title.
1889 #gets all orders from a certain supplier, orders them alphabetically
1890 my ( $supplierid, $code, $datereceived ) = @_;
1891 my $dbh = C4::Context->dbh;
1894 if $code; # add % if we search on a given code (otherwise, let him empty)
1896 SELECT authorisedby,
1901 aqorders.biblionumber,
1902 aqorders.ordernumber,
1903 aqorders.parent_ordernumber,
1905 aqorders.quantityreceived,
1913 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
1914 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1915 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1916 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1918 aqbasket.booksellerid = ?
1919 AND aqinvoices.invoicenumber LIKE ?
1920 AND aqorders.datereceived = ? ";
1922 my @query_params = ( $supplierid, $code, $datereceived );
1923 if ( C4::Context->preference("IndependentBranches") ) {
1924 unless ( C4::Context->IsSuperLibrarian() ) {
1925 $strsth .= " and (borrowers.branchcode = ?
1926 or borrowers.branchcode = '')";
1927 push @query_params, C4::Context->userenv->{branch};
1930 $strsth .= " ORDER BY aqbasket.basketno";
1931 my $result_set = $dbh->selectall_arrayref(
1936 return @{$result_set};
1939 #------------------------------------------------------------#
1943 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1945 get a lists of parcels.
1952 is the bookseller this function has to get parcels.
1955 To know on what criteria the results list has to be ordered.
1958 is the booksellerinvoicenumber.
1960 =item $datefrom & $dateto
1961 to know on what date this function has to filter its search.
1966 a pointer on a hash list containing parcel informations as such :
1972 =item Last operation
1974 =item Number of biblio
1976 =item Number of items
1983 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1984 my $dbh = C4::Context->dbh;
1985 my @query_params = ();
1987 SELECT aqinvoices.invoicenumber,
1988 datereceived,purchaseordernumber,
1989 count(DISTINCT biblionumber) AS biblio,
1990 sum(quantity) AS itemsexpected,
1991 sum(quantityreceived) AS itemsreceived
1992 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1993 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1994 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1996 push @query_params, $bookseller;
1998 if ( defined $code ) {
1999 $strsth .= ' and aqinvoices.invoicenumber like ? ';
2000 # add a % to the end of the code to allow stemming.
2001 push @query_params, "$code%";
2004 if ( defined $datefrom ) {
2005 $strsth .= ' and datereceived >= ? ';
2006 push @query_params, $datefrom;
2009 if ( defined $dateto ) {
2010 $strsth .= 'and datereceived <= ? ';
2011 push @query_params, $dateto;
2014 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
2016 # can't use a placeholder to place this column name.
2017 # but, we could probably be checking to make sure it is a column that will be fetched.
2018 $strsth .= "order by $order " if ($order);
2020 my $sth = $dbh->prepare($strsth);
2022 $sth->execute( @query_params );
2023 my $results = $sth->fetchall_arrayref({});
2027 #------------------------------------------------------------#
2029 =head3 GetLateOrders
2031 @results = &GetLateOrders;
2033 Searches for bookseller with late orders.
2036 the table of supplier with late issues. This table is full of hashref.
2042 my $supplierid = shift;
2044 my $estimateddeliverydatefrom = shift;
2045 my $estimateddeliverydateto = shift;
2047 my $dbh = C4::Context->dbh;
2049 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2050 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2052 my @query_params = ();
2054 SELECT aqbasket.basketno,
2055 aqorders.ordernumber,
2056 DATE(aqbasket.closedate) AS orderdate,
2057 aqbasket.basketname AS basketname,
2058 aqbasket.basketgroupid AS basketgroupid,
2059 aqbasketgroups.name AS basketgroupname,
2060 aqorders.rrp AS unitpricesupplier,
2061 aqorders.ecost AS unitpricelib,
2062 aqorders.claims_count AS claims_count,
2063 aqorders.claimed_date AS claimed_date,
2064 aqbudgets.budget_name AS budget,
2065 borrowers.branchcode AS branch,
2066 aqbooksellers.name AS supplier,
2067 aqbooksellers.id AS supplierid,
2068 biblio.author, biblio.title,
2069 biblioitems.publishercode AS publisher,
2070 biblioitems.publicationyear,
2071 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2075 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2076 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2077 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2078 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2079 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2080 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2081 WHERE aqorders.basketno = aqbasket.basketno
2082 AND ( datereceived = ''
2083 OR datereceived IS NULL
2084 OR aqorders.quantityreceived < aqorders.quantity
2086 AND aqbasket.closedate IS NOT NULL
2087 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2090 if ($dbdriver eq "mysql") {
2092 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2093 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2094 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2096 if ( defined $delay ) {
2097 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2098 push @query_params, $delay;
2101 HAVING quantity <> 0
2102 AND unitpricesupplier <> 0
2103 AND unitpricelib <> 0
2106 # FIXME: account for IFNULL as above
2108 aqorders.quantity AS quantity,
2109 aqorders.quantity * aqorders.rrp AS subtotal,
2110 (CAST(now() AS date) - closedate) AS latesince
2112 if ( defined $delay ) {
2113 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2114 push @query_params, $delay;
2117 if (defined $supplierid) {
2118 $from .= ' AND aqbasket.booksellerid = ? ';
2119 push @query_params, $supplierid;
2121 if (defined $branch) {
2122 $from .= ' AND borrowers.branchcode LIKE ? ';
2123 push @query_params, $branch;
2126 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2127 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2129 if ( defined $estimateddeliverydatefrom ) {
2130 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2131 push @query_params, $estimateddeliverydatefrom;
2133 if ( defined $estimateddeliverydateto ) {
2134 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2135 push @query_params, $estimateddeliverydateto;
2137 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2138 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2140 if (C4::Context->preference("IndependentBranches")
2141 && !C4::Context->IsSuperLibrarian() ) {
2142 $from .= ' AND borrowers.branchcode LIKE ? ';
2143 push @query_params, C4::Context->userenv->{branch};
2145 $from .= " AND orderstatus <> 'cancelled' ";
2146 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2147 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2148 my $sth = $dbh->prepare($query);
2149 $sth->execute(@query_params);
2151 while (my $data = $sth->fetchrow_hashref) {
2152 push @results, $data;
2157 #------------------------------------------------------------#
2161 (\@order_loop, $total_qty, $total_price, $total_qtyreceived) = GetHistory( %params );
2163 Retreives some acquisition history information
2173 basket - search both basket name and number
2174 booksellerinvoicenumber
2177 orderstatus (note that orderstatus '' will retrieve orders
2178 of any status except cancelled)
2180 get_canceled_order (if set to a true value, cancelled orders will
2184 $order_loop is a list of hashrefs that each look like this:
2186 'author' => 'Twain, Mark',
2188 'biblionumber' => '215',
2190 'creationdate' => 'MM/DD/YYYY',
2191 'datereceived' => undef,
2194 'invoicenumber' => undef,
2196 'ordernumber' => '1',
2198 'quantityreceived' => undef,
2199 'title' => 'The Adventures of Huckleberry Finn'
2201 $total_qty is the sum of all of the quantities in $order_loop
2202 $total_price is the cost of each in $order_loop times the quantity
2203 $total_qtyreceived is the sum of all of the quantityreceived entries in $order_loop
2208 # don't run the query if there are no parameters (list would be too long for sure !)
2209 croak "No search params" unless @_;
2211 my $title = $params{title};
2212 my $author = $params{author};
2213 my $isbn = $params{isbn};
2214 my $ean = $params{ean};
2215 my $name = $params{name};
2216 my $from_placed_on = $params{from_placed_on};
2217 my $to_placed_on = $params{to_placed_on};
2218 my $basket = $params{basket};
2219 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2220 my $basketgroupname = $params{basketgroupname};
2221 my $budget = $params{budget};
2222 my $orderstatus = $params{orderstatus};
2223 my $biblionumber = $params{biblionumber};
2224 my $get_canceled_order = $params{get_canceled_order} || 0;
2225 my $ordernumber = $params{ordernumber};
2226 my $search_children_too = $params{search_children_too} || 0;
2230 my $total_qtyreceived = 0;
2231 my $total_price = 0;
2233 my $dbh = C4::Context->dbh;
2236 COALESCE(biblio.title, deletedbiblio.title) AS title,
2237 COALESCE(biblio.author, deletedbiblio.author) AS author,
2238 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2239 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2241 aqbasket.basketname,
2242 aqbasket.basketgroupid,
2243 aqbasketgroups.name as groupname,
2245 aqbasket.creationdate,
2246 aqorders.datereceived,
2248 aqorders.quantityreceived,
2250 aqorders.ordernumber,
2252 aqinvoices.invoicenumber,
2253 aqbooksellers.id as id,
2254 aqorders.biblionumber,
2255 aqorders.orderstatus,
2256 aqorders.parent_ordernumber,
2257 aqbudgets.budget_name
2259 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2262 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2263 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2264 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2265 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2266 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2267 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2268 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2269 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2270 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2273 if ( C4::Context->preference("IndependentBranches") ) {
2274 $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber";
2277 $query .= " WHERE 1 ";
2279 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2280 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2283 my @query_params = ();
2285 if ( $biblionumber ) {
2286 $query .= " AND biblio.biblionumber = ?";
2287 push @query_params, $biblionumber;
2291 $query .= " AND biblio.title LIKE ? ";
2292 $title =~ s/\s+/%/g;
2293 push @query_params, "%$title%";
2297 $query .= " AND biblio.author LIKE ? ";
2298 push @query_params, "%$author%";
2302 $query .= " AND biblioitems.isbn LIKE ? ";
2303 push @query_params, "%$isbn%";
2306 $query .= " AND biblioitems.ean = ? ";
2307 push @query_params, "$ean";
2310 $query .= " AND aqbooksellers.name LIKE ? ";
2311 push @query_params, "%$name%";
2315 $query .= " AND aqbudgets.budget_id = ? ";
2316 push @query_params, "$budget";
2319 if ( $from_placed_on ) {
2320 $query .= " AND creationdate >= ? ";
2321 push @query_params, $from_placed_on;
2324 if ( $to_placed_on ) {
2325 $query .= " AND creationdate <= ? ";
2326 push @query_params, $to_placed_on;
2329 if ( defined $orderstatus and $orderstatus ne '') {
2330 $query .= " AND aqorders.orderstatus = ? ";
2331 push @query_params, "$orderstatus";
2335 if ($basket =~ m/^\d+$/) {
2336 $query .= " AND aqorders.basketno = ? ";
2337 push @query_params, $basket;
2339 $query .= " AND aqbasket.basketname LIKE ? ";
2340 push @query_params, "%$basket%";
2344 if ($booksellerinvoicenumber) {
2345 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2346 push @query_params, "%$booksellerinvoicenumber%";
2349 if ($basketgroupname) {
2350 $query .= " AND aqbasketgroups.name LIKE ? ";
2351 push @query_params, "%$basketgroupname%";
2355 $query .= " AND (aqorders.ordernumber = ? ";
2356 push @query_params, $ordernumber;
2357 if ($search_children_too) {
2358 $query .= " OR aqorders.parent_ordernumber = ? ";
2359 push @query_params, $ordernumber;
2365 if ( C4::Context->preference("IndependentBranches") ) {
2366 unless ( C4::Context->IsSuperLibrarian() ) {
2367 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2368 push @query_params, C4::Context->userenv->{branch};
2371 $query .= " ORDER BY id";
2372 my $sth = $dbh->prepare($query);
2373 $sth->execute( @query_params );
2375 while ( my $line = $sth->fetchrow_hashref ) {
2376 $line->{count} = $cnt++;
2377 $line->{toggle} = 1 if $cnt % 2;
2378 push @order_loop, $line;
2379 $total_qty += ( $line->{quantity} ) ? $line->{quantity} : 0;
2380 $total_qtyreceived += ( $line->{quantityreceived} ) ? $line->{quantityreceived} : 0;
2381 $total_price += ( $line->{quantity} and $line->{ecost} ) ? $line->{quantity} * $line->{ecost} : 0;
2383 return \@order_loop, $total_qty, $total_price, $total_qtyreceived;
2386 =head2 GetRecentAcqui
2388 $results = GetRecentAcqui($days);
2390 C<$results> is a ref to a table which containts hashref
2394 sub GetRecentAcqui {
2396 my $dbh = C4::Context->dbh;
2400 ORDER BY timestamp DESC
2403 my $sth = $dbh->prepare($query);
2405 my $results = $sth->fetchall_arrayref({});
2409 #------------------------------------------------------------#
2415 &AddClaim($ordernumber);
2417 Add a claim for an order
2424 my ($ordernumber) = @_;
2425 my $dbh = C4::Context->dbh;
2428 claims_count = claims_count + 1,
2429 claimed_date = CURDATE()
2430 WHERE ordernumber = ?
2432 my $sth = $dbh->prepare($query);
2433 $sth->execute($ordernumber);
2438 my @invoices = GetInvoices(
2439 invoicenumber => $invoicenumber,
2440 supplierid => $supplierid,
2441 suppliername => $suppliername,
2442 shipmentdatefrom => $shipmentdatefrom, # ISO format
2443 shipmentdateto => $shipmentdateto, # ISO format
2444 billingdatefrom => $billingdatefrom, # ISO format
2445 billingdateto => $billingdateto, # ISO format
2446 isbneanissn => $isbn_or_ean_or_issn,
2449 publisher => $publisher,
2450 publicationyear => $publicationyear,
2451 branchcode => $branchcode,
2452 order_by => $order_by
2455 Return a list of invoices that match all given criteria.
2457 $order_by is "column_name (asc|desc)", where column_name is any of
2458 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2459 'shipmentcost', 'shipmentcost_budgetid'.
2461 asc is the default if omitted
2468 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2469 closedate shipmentcost shipmentcost_budgetid);
2471 my $dbh = C4::Context->dbh;
2473 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2476 aqorders.datereceived IS NOT NULL,
2477 aqorders.biblionumber,
2480 ) AS receivedbiblios,
2483 aqorders.subscriptionid IS NOT NULL,
2484 aqorders.subscriptionid,
2487 ) AS is_linked_to_subscriptions,
2488 SUM(aqorders.quantityreceived) AS receiveditems
2490 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2491 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2492 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2493 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2494 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2495 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2496 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2501 if($args{supplierid}) {
2502 push @bind_strs, " aqinvoices.booksellerid = ? ";
2503 push @bind_args, $args{supplierid};
2505 if($args{invoicenumber}) {
2506 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2507 push @bind_args, "%$args{invoicenumber}%";
2509 if($args{suppliername}) {
2510 push @bind_strs, " aqbooksellers.name LIKE ? ";
2511 push @bind_args, "%$args{suppliername}%";
2513 if($args{shipmentdatefrom}) {
2514 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2515 push @bind_args, $args{shipmentdatefrom};
2517 if($args{shipmentdateto}) {
2518 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2519 push @bind_args, $args{shipmentdateto};
2521 if($args{billingdatefrom}) {
2522 push @bind_strs, " aqinvoices.billingdate >= ? ";
2523 push @bind_args, $args{billingdatefrom};
2525 if($args{billingdateto}) {
2526 push @bind_strs, " aqinvoices.billingdate <= ? ";
2527 push @bind_args, $args{billingdateto};
2529 if($args{isbneanissn}) {
2530 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2531 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2534 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2535 push @bind_args, $args{title};
2538 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2539 push @bind_args, $args{author};
2541 if($args{publisher}) {
2542 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2543 push @bind_args, $args{publisher};
2545 if($args{publicationyear}) {
2546 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2547 push @bind_args, $args{publicationyear}, $args{publicationyear};
2549 if($args{branchcode}) {
2550 push @bind_strs, " borrowers.branchcode = ? ";
2551 push @bind_args, $args{branchcode};
2554 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2555 $query .= " GROUP BY aqinvoices.invoiceid ";
2557 if($args{order_by}) {
2558 my ($column, $direction) = split / /, $args{order_by};
2559 if(grep /^$column$/, @columns) {
2560 $direction ||= 'ASC';
2561 $query .= " ORDER BY $column $direction";
2565 my $sth = $dbh->prepare($query);
2566 $sth->execute(@bind_args);
2568 my $results = $sth->fetchall_arrayref({});
2574 my $invoice = GetInvoice($invoiceid);
2576 Get informations about invoice with given $invoiceid
2578 Return a hash filled with aqinvoices.* fields
2583 my ($invoiceid) = @_;
2586 return unless $invoiceid;
2588 my $dbh = C4::Context->dbh;
2594 my $sth = $dbh->prepare($query);
2595 $sth->execute($invoiceid);
2597 $invoice = $sth->fetchrow_hashref;
2601 =head3 GetInvoiceDetails
2603 my $invoice = GetInvoiceDetails($invoiceid)
2605 Return informations about an invoice + the list of related order lines
2607 Orders informations are in $invoice->{orders} (array ref)
2611 sub GetInvoiceDetails {
2612 my ($invoiceid) = @_;
2614 if ( !defined $invoiceid ) {
2615 carp 'GetInvoiceDetails called without an invoiceid';
2619 my $dbh = C4::Context->dbh;
2621 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2623 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2626 my $sth = $dbh->prepare($query);
2627 $sth->execute($invoiceid);
2629 my $invoice = $sth->fetchrow_hashref;
2632 SELECT aqorders.*, biblio.*, aqbasket.basketname
2634 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2635 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2638 $sth = $dbh->prepare($query);
2639 $sth->execute($invoiceid);
2640 $invoice->{orders} = $sth->fetchall_arrayref({});
2641 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2648 my $invoiceid = AddInvoice(
2649 invoicenumber => $invoicenumber,
2650 booksellerid => $booksellerid,
2651 shipmentdate => $shipmentdate,
2652 billingdate => $billingdate,
2653 closedate => $closedate,
2654 shipmentcost => $shipmentcost,
2655 shipmentcost_budgetid => $shipmentcost_budgetid
2658 Create a new invoice and return its id or undef if it fails.
2665 return unless(%invoice and $invoice{invoicenumber});
2667 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2668 closedate shipmentcost shipmentcost_budgetid);
2672 foreach my $key (keys %invoice) {
2673 if(0 < grep(/^$key$/, @columns)) {
2674 push @set_strs, "$key = ?";
2675 push @set_args, ($invoice{$key} || undef);
2681 my $dbh = C4::Context->dbh;
2682 my $query = "INSERT INTO aqinvoices SET ";
2683 $query .= join (",", @set_strs);
2684 my $sth = $dbh->prepare($query);
2685 $rv = $sth->execute(@set_args);
2687 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2696 invoiceid => $invoiceid, # Mandatory
2697 invoicenumber => $invoicenumber,
2698 booksellerid => $booksellerid,
2699 shipmentdate => $shipmentdate,
2700 billingdate => $billingdate,
2701 closedate => $closedate,
2702 shipmentcost => $shipmentcost,
2703 shipmentcost_budgetid => $shipmentcost_budgetid
2706 Modify an invoice, invoiceid is mandatory.
2708 Return undef if it fails.
2715 return unless(%invoice and $invoice{invoiceid});
2717 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2718 closedate shipmentcost shipmentcost_budgetid);
2722 foreach my $key (keys %invoice) {
2723 if(0 < grep(/^$key$/, @columns)) {
2724 push @set_strs, "$key = ?";
2725 push @set_args, ($invoice{$key} || undef);
2729 my $dbh = C4::Context->dbh;
2730 my $query = "UPDATE aqinvoices SET ";
2731 $query .= join(",", @set_strs);
2732 $query .= " WHERE invoiceid = ?";
2734 my $sth = $dbh->prepare($query);
2735 $sth->execute(@set_args, $invoice{invoiceid});
2740 CloseInvoice($invoiceid);
2744 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2749 my ($invoiceid) = @_;
2751 return unless $invoiceid;
2753 my $dbh = C4::Context->dbh;
2756 SET closedate = CAST(NOW() AS DATE)
2759 my $sth = $dbh->prepare($query);
2760 $sth->execute($invoiceid);
2763 =head3 ReopenInvoice
2765 ReopenInvoice($invoiceid);
2769 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso'))
2774 my ($invoiceid) = @_;
2776 return unless $invoiceid;
2778 my $dbh = C4::Context->dbh;
2781 SET closedate = NULL
2784 my $sth = $dbh->prepare($query);
2785 $sth->execute($invoiceid);
2790 DelInvoice($invoiceid);
2792 Delete an invoice if there are no items attached to it.
2797 my ($invoiceid) = @_;
2799 return unless $invoiceid;
2801 my $dbh = C4::Context->dbh;
2807 my $sth = $dbh->prepare($query);
2808 $sth->execute($invoiceid);
2809 my $res = $sth->fetchrow_arrayref;
2810 if ( $res && $res->[0] == 0 ) {
2812 DELETE FROM aqinvoices
2815 my $sth = $dbh->prepare($query);
2816 return ( $sth->execute($invoiceid) > 0 );
2821 =head3 MergeInvoices
2823 MergeInvoices($invoiceid, \@sourceids);
2825 Merge the invoices identified by the IDs in \@sourceids into
2826 the invoice identified by $invoiceid.
2831 my ($invoiceid, $sourceids) = @_;
2833 return unless $invoiceid;
2834 foreach my $sourceid (@$sourceids) {
2835 next if $sourceid == $invoiceid;
2836 my $source = GetInvoiceDetails($sourceid);
2837 foreach my $order (@{$source->{'orders'}}) {
2838 $order->{'invoiceid'} = $invoiceid;
2841 DelInvoice($source->{'invoiceid'});
2846 =head3 GetBiblioCountByBasketno
2848 $biblio_count = &GetBiblioCountByBasketno($basketno);
2850 Looks up the biblio's count that has basketno value $basketno
2856 sub GetBiblioCountByBasketno {
2857 my ($basketno) = @_;
2858 my $dbh = C4::Context->dbh;
2860 SELECT COUNT( DISTINCT( biblionumber ) )
2863 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2866 my $sth = $dbh->prepare($query);
2867 $sth->execute($basketno);
2868 return $sth->fetchrow;
2876 Koha Development Team <http://koha-community.org/>