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
8 # under the terms of the GNU General Public License as published by
9 # the Free Software Foundation; either version 3 of the License, or
10 # (at your option) any later version.
12 # Koha is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
29 use C4::Templates qw(gettemplate);
30 use Koha::DateUtils qw( dt_from_string output_pref );
31 use Koha::Acquisition::Order;
32 use Koha::Acquisition::Bookseller;
33 use Koha::Number::Price;
43 use vars qw(@ISA @EXPORT);
49 &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
50 &GetBasketAsCSV &GetBasketGroupAsCSV
51 &GetBasketsByBookseller &GetBasketsByBasketgroup
52 &GetBasketsInfosByBookseller
54 &GetBasketUsers &ModBasketUsers
59 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
60 &GetBasketgroups &ReOpenBasketgroup
62 &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
63 &GetLateOrders &GetOrderFromItemnumber
64 &SearchOrders &GetHistory &GetRecentAcqui
65 &ModReceiveOrder &CancelReceipt
67 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
82 &GetItemnumbersFromOrder
85 &GetBiblioCountByBasketno
91 &FillWithDefaultValues
99 sub GetOrderFromItemnumber {
100 my ($itemnumber) = @_;
101 my $dbh = C4::Context->dbh;
104 SELECT * from aqorders LEFT JOIN aqorders_items
105 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
106 WHERE itemnumber = ? |;
108 my $sth = $dbh->prepare($query);
112 $sth->execute($itemnumber);
114 my $order = $sth->fetchrow_hashref;
119 # Returns the itemnumber(s) associated with the ordernumber given in parameter
120 sub GetItemnumbersFromOrder {
121 my ($ordernumber) = @_;
122 my $dbh = C4::Context->dbh;
123 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
124 my $sth = $dbh->prepare($query);
125 $sth->execute($ordernumber);
128 while (my $order = $sth->fetchrow_hashref) {
129 push @tab, $order->{'itemnumber'};
143 C4::Acquisition - Koha functions for dealing with orders and acquisitions
151 The functions in this module deal with acquisitions, managing book
152 orders, basket and parcels.
156 =head2 FUNCTIONS ABOUT BASKETS
160 $aqbasket = &GetBasket($basketnumber);
162 get all basket informations in aqbasket for a given basket
164 B<returns:> informations for a given basket returned as a hashref.
170 my $dbh = C4::Context->dbh;
173 concat( b.firstname,' ',b.surname) AS authorisedbyname
175 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
178 my $sth=$dbh->prepare($query);
179 $sth->execute($basketno);
180 my $basket = $sth->fetchrow_hashref;
184 #------------------------------------------------------------#
188 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
189 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace, $is_standing );
191 Create a new basket in aqbasket table
195 =item C<$booksellerid> is a foreign key in the aqbasket table
197 =item C<$authorizedby> is the username of who created the basket
201 The other parameters are optional, see ModBasketHeader for more info on them.
206 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
207 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
208 $billingplace, $is_standing ) = @_;
209 my $dbh = C4::Context->dbh;
211 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
212 . 'VALUES (now(),?,?)';
213 $dbh->do( $query, {}, $booksellerid, $authorisedby );
215 my $basket = $dbh->{mysql_insertid};
216 $basketname ||= q{}; # default to empty strings
218 $basketbooksellernote ||= q{};
219 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
220 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing );
224 #------------------------------------------------------------#
228 &CloseBasket($basketno);
230 close a basket (becomes unmodifiable, except for receives)
236 my $dbh = C4::Context->dbh;
237 $dbh->do('UPDATE aqbasket SET closedate=now() WHERE basketno=?', {}, $basketno );
239 $dbh->do( q{UPDATE aqorders SET orderstatus = 'ordered' WHERE basketno = ? AND orderstatus != 'complete'},
246 &ReopenBasket($basketno);
254 my $dbh = C4::Context->dbh;
255 $dbh->do( q{UPDATE aqbasket SET closedate=NULL WHERE basketno=?}, {}, $basketno );
259 SET orderstatus = 'new'
261 AND orderstatus != 'complete'
266 #------------------------------------------------------------#
268 =head3 GetBasketAsCSV
270 &GetBasketAsCSV($basketno);
272 Export a basket as CSV
274 $cgi parameter is needed for column name translation
279 my ($basketno, $cgi) = @_;
280 my $basket = GetBasket($basketno);
281 my @orders = GetOrders($basketno);
282 my $contract = GetContract({
283 contractnumber => $basket->{'contractnumber'}
286 my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
289 foreach my $order (@orders) {
290 my $bd = GetBiblioData( $order->{'biblionumber'} );
292 contractname => $contract->{'contractname'},
293 ordernumber => $order->{'ordernumber'},
294 entrydate => $order->{'entrydate'},
295 isbn => $order->{'isbn'},
296 author => $bd->{'author'},
297 title => $bd->{'title'},
298 publicationyear => $bd->{'publicationyear'},
299 publishercode => $bd->{'publishercode'},
300 collectiontitle => $bd->{'collectiontitle'},
301 notes => $order->{'order_vendornote'},
302 quantity => $order->{'quantity'},
303 rrp => $order->{'rrp'},
305 for my $place ( qw( deliveryplace billingplace ) ) {
306 if ( my $library = Koha::Libraries->find( $row->{deliveryplace} ) ) {
307 $row->{$place} = $library->branchname
311 contractname author title publishercode collectiontitle notes
312 deliveryplace billingplace
314 # Double the quotes to not be interpreted as a field end
315 $row->{$_} =~ s/"/""/g if $row->{$_};
321 if(defined $a->{publishercode} and defined $b->{publishercode}) {
322 $a->{publishercode} cmp $b->{publishercode};
326 $template->param(rows => \@rows);
328 return $template->output;
332 =head3 GetBasketGroupAsCSV
334 &GetBasketGroupAsCSV($basketgroupid);
336 Export a basket group as CSV
338 $cgi parameter is needed for column name translation
342 sub GetBasketGroupAsCSV {
343 my ($basketgroupid, $cgi) = @_;
344 my $baskets = GetBasketsByBasketgroup($basketgroupid);
346 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
349 for my $basket (@$baskets) {
350 my @orders = GetOrders( $basket->{basketno} );
351 my $contract = GetContract({
352 contractnumber => $basket->{contractnumber}
354 my $bookseller = Koha::Acquisition::Bookseller->fetch({ id => $basket->{booksellerid} });
355 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
357 foreach my $order (@orders) {
358 my $bd = GetBiblioData( $order->{'biblionumber'} );
360 clientnumber => $bookseller->{accountnumber},
361 basketname => $basket->{basketname},
362 ordernumber => $order->{ordernumber},
363 author => $bd->{author},
364 title => $bd->{title},
365 publishercode => $bd->{publishercode},
366 publicationyear => $bd->{publicationyear},
367 collectiontitle => $bd->{collectiontitle},
368 isbn => $order->{isbn},
369 quantity => $order->{quantity},
370 rrp_tax_included => $order->{rrp_tax_included},
371 rrp_tax_excluded => $order->{rrp_tax_excluded},
372 discount => $bookseller->{discount},
373 ecost_tax_included => $order->{ecost_tax_included},
374 ecost_tax_excluded => $order->{ecost_tax_excluded},
375 notes => $order->{order_vendornote},
376 entrydate => $order->{entrydate},
377 booksellername => $bookseller->{name},
378 bookselleraddress => $bookseller->{address1},
379 booksellerpostal => $bookseller->{postal},
380 contractnumber => $contract->{contractnumber},
381 contractname => $contract->{contractname},
384 basketgroupdeliveryplace => $basketgroup->{deliveryplace},
385 basketgroupbillingplace => $basketgroup->{billingplace},
386 basketdeliveryplace => $basket->{deliveryplace},
387 basketbillingplace => $basket->{billingplace},
389 for my $place (qw( basketgroupdeliveryplace basketgroupbillingplace basketdeliveryplace basketbillingplace )) {
390 if ( my $library = Koha::Libraries->find( $temp->{$place} ) ) {
391 $row->{$place} = $library->branchname;
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.
545 =item C<$is_standing> is the "is_standing" field in the aqbasket table.
551 sub ModBasketHeader {
552 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing) = @_;
555 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?, is_standing=?
559 my $dbh = C4::Context->dbh;
560 my $sth = $dbh->prepare($query);
561 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $is_standing, $basketno);
563 if ( $contractnumber ) {
564 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
565 my $sth2 = $dbh->prepare($query2);
566 $sth2->execute($contractnumber,$basketno);
571 #------------------------------------------------------------#
573 =head3 GetBasketsByBookseller
575 @results = &GetBasketsByBookseller($booksellerid, $extra);
577 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
581 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
583 =item C<$extra> is the extra sql parameters, can be
585 $extra->{groupby}: group baskets by column
586 ex. $extra->{groupby} = aqbasket.basketgroupid
587 $extra->{orderby}: order baskets by column
588 $extra->{limit}: limit number of results (can be helpful for pagination)
594 sub GetBasketsByBookseller {
595 my ($booksellerid, $extra) = @_;
596 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
598 if ($extra->{groupby}) {
599 $query .= " GROUP by $extra->{groupby}";
601 if ($extra->{orderby}){
602 $query .= " ORDER by $extra->{orderby}";
604 if ($extra->{limit}){
605 $query .= " LIMIT $extra->{limit}";
608 my $dbh = C4::Context->dbh;
609 my $sth = $dbh->prepare($query);
610 $sth->execute($booksellerid);
611 return $sth->fetchall_arrayref({});
614 =head3 GetBasketsInfosByBookseller
616 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
618 The optional second parameter allbaskets is a boolean allowing you to
619 select all baskets from the supplier; by default only active baskets (open or
620 closed but still something to receive) are returned.
622 Returns in a arrayref of hashref all about booksellers baskets, plus:
623 total_biblios: Number of distinct biblios in basket
624 total_items: Number of items in basket
625 expected_items: Number of non-received items in basket
629 sub GetBasketsInfosByBookseller {
630 my ($supplierid, $allbaskets) = @_;
632 return unless $supplierid;
634 my $dbh = C4::Context->dbh;
637 SUM(aqorders.quantity) AS total_items,
639 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
640 ) AS total_items_cancelled,
641 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
643 IF(aqorders.datereceived IS NULL
644 AND aqorders.datecancellationprinted IS NULL
649 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
650 WHERE booksellerid = ?};
652 unless ( $allbaskets ) {
653 $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
655 $query.=" GROUP BY aqbasket.basketno";
657 my $sth = $dbh->prepare($query);
658 $sth->execute($supplierid);
659 my $baskets = $sth->fetchall_arrayref({});
661 # Retrieve the number of biblios cancelled
662 my $cancelled_biblios = $dbh->selectall_hashref( q|
663 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
665 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
666 WHERE booksellerid = ?
667 AND aqorders.orderstatus = 'cancelled'
668 GROUP BY aqbasket.basketno
669 |, 'basketno', {}, $supplierid );
671 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
677 =head3 GetBasketUsers
679 $basketusers_ids = &GetBasketUsers($basketno);
681 Returns a list of all borrowernumbers that are in basket users list
686 my $basketno = shift;
688 return unless $basketno;
691 SELECT borrowernumber
695 my $dbh = C4::Context->dbh;
696 my $sth = $dbh->prepare($query);
697 $sth->execute($basketno);
698 my $results = $sth->fetchall_arrayref( {} );
701 foreach (@$results) {
702 push @borrowernumbers, $_->{'borrowernumber'};
705 return @borrowernumbers;
708 =head3 ModBasketUsers
710 my @basketusers_ids = (1, 2, 3);
711 &ModBasketUsers($basketno, @basketusers_ids);
713 Delete all users from basket users list, and add users in C<@basketusers_ids>
719 my ($basketno, @basketusers_ids) = @_;
721 return unless $basketno;
723 my $dbh = C4::Context->dbh;
725 DELETE FROM aqbasketusers
728 my $sth = $dbh->prepare($query);
729 $sth->execute($basketno);
732 INSERT INTO aqbasketusers (basketno, borrowernumber)
735 $sth = $dbh->prepare($query);
736 foreach my $basketuser_id (@basketusers_ids) {
737 $sth->execute($basketno, $basketuser_id);
742 =head3 CanUserManageBasket
744 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
745 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
747 Check if a borrower can manage a basket, according to system preference
748 AcqViewBaskets, user permissions and basket properties (creator, users list,
751 First parameter can be either a borrowernumber or a hashref as returned by
752 C4::Members::GetMember.
754 Second parameter can be either a basketno or a hashref as returned by
755 C4::Acquisition::GetBasket.
757 The third parameter is optional. If given, it should be a hashref as returned
758 by C4::Auth::getuserflags. If not, getuserflags is called.
760 If user is authorised to manage basket, returns 1.
765 sub CanUserManageBasket {
766 my ($borrower, $basket, $userflags) = @_;
768 if (!ref $borrower) {
769 $borrower = C4::Members::GetMember(borrowernumber => $borrower);
772 $basket = GetBasket($basket);
775 return 0 unless ($basket and $borrower);
777 my $borrowernumber = $borrower->{borrowernumber};
778 my $basketno = $basket->{basketno};
780 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
782 if (!defined $userflags) {
783 my $dbh = C4::Context->dbh;
784 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
785 $sth->execute($borrowernumber);
786 my ($flags) = $sth->fetchrow_array;
789 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
792 unless ($userflags->{superlibrarian}
793 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
794 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
796 if (not exists $userflags->{acquisition}) {
800 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
801 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
805 if ($AcqViewBaskets eq 'user'
806 && $basket->{authorisedby} != $borrowernumber
807 && grep($borrowernumber, GetBasketUsers($basketno)) == 0) {
811 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
812 && $basket->{branch} ne $borrower->{branchcode}) {
820 #------------------------------------------------------------#
822 =head3 GetBasketsByBasketgroup
824 $baskets = &GetBasketsByBasketgroup($basketgroupid);
826 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
830 sub GetBasketsByBasketgroup {
831 my $basketgroupid = shift;
833 SELECT *, aqbasket.booksellerid as booksellerid
835 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
837 my $dbh = C4::Context->dbh;
838 my $sth = $dbh->prepare($query);
839 $sth->execute($basketgroupid);
840 return $sth->fetchall_arrayref({});
843 #------------------------------------------------------------#
845 =head3 NewBasketgroup
847 $basketgroupid = NewBasketgroup(\%hashref);
849 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
851 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
853 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
855 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
857 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
859 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
861 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
863 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
865 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
870 my $basketgroupinfo = shift;
871 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
872 my $query = "INSERT INTO aqbasketgroups (";
874 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
875 if ( defined $basketgroupinfo->{$field} ) {
876 $query .= "$field, ";
877 push(@params, $basketgroupinfo->{$field});
880 $query .= "booksellerid) VALUES (";
885 push(@params, $basketgroupinfo->{'booksellerid'});
886 my $dbh = C4::Context->dbh;
887 my $sth = $dbh->prepare($query);
888 $sth->execute(@params);
889 my $basketgroupid = $dbh->{'mysql_insertid'};
890 if( $basketgroupinfo->{'basketlist'} ) {
891 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
892 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
893 my $sth2 = $dbh->prepare($query2);
894 $sth2->execute($basketgroupid, $basketno);
897 return $basketgroupid;
900 #------------------------------------------------------------#
902 =head3 ModBasketgroup
904 ModBasketgroup(\%hashref);
906 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
908 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
910 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
912 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
914 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
916 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
918 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
920 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
922 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
927 my $basketgroupinfo = shift;
928 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
929 my $dbh = C4::Context->dbh;
930 my $query = "UPDATE aqbasketgroups SET ";
932 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
933 if ( defined $basketgroupinfo->{$field} ) {
934 $query .= "$field=?, ";
935 push(@params, $basketgroupinfo->{$field});
940 $query .= " WHERE id=?";
941 push(@params, $basketgroupinfo->{'id'});
942 my $sth = $dbh->prepare($query);
943 $sth->execute(@params);
945 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
946 $sth->execute($basketgroupinfo->{'id'});
948 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
949 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
950 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
951 $sth->execute($basketgroupinfo->{'id'}, $basketno);
957 #------------------------------------------------------------#
959 =head3 DelBasketgroup
961 DelBasketgroup($basketgroupid);
963 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
967 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
974 my $basketgroupid = shift;
975 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
976 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
977 my $dbh = C4::Context->dbh;
978 my $sth = $dbh->prepare($query);
979 $sth->execute($basketgroupid);
983 #------------------------------------------------------------#
986 =head2 FUNCTIONS ABOUT ORDERS
988 =head3 GetBasketgroup
990 $basketgroup = &GetBasketgroup($basketgroupid);
992 Returns a reference to the hash containing all information about the basketgroup.
997 my $basketgroupid = shift;
998 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
999 my $dbh = C4::Context->dbh;
1000 my $result_set = $dbh->selectall_arrayref(
1001 'SELECT * FROM aqbasketgroups WHERE id=?',
1005 return $result_set->[0]; # id is unique
1008 #------------------------------------------------------------#
1010 =head3 GetBasketgroups
1012 $basketgroups = &GetBasketgroups($booksellerid);
1014 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1018 sub GetBasketgroups {
1019 my $booksellerid = shift;
1020 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1021 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1022 my $dbh = C4::Context->dbh;
1023 my $sth = $dbh->prepare($query);
1024 $sth->execute($booksellerid);
1025 return $sth->fetchall_arrayref({});
1028 #------------------------------------------------------------#
1030 =head2 FUNCTIONS ABOUT ORDERS
1034 @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } );
1036 Looks up the pending (non-cancelled) orders with the given basket
1039 If cancelled is set, only cancelled orders will be returned.
1044 my ( $basketno, $params ) = @_;
1046 return () unless $basketno;
1048 my $orderby = $params->{orderby};
1049 my $cancelled = $params->{cancelled} || 0;
1051 my $dbh = C4::Context->dbh;
1053 SELECT biblio.*,biblioitems.*,
1057 $query .= $cancelled
1059 aqorders_transfers.ordernumber_to AS transferred_to,
1060 aqorders_transfers.timestamp AS transferred_to_timestamp
1063 aqorders_transfers.ordernumber_from AS transferred_from,
1064 aqorders_transfers.timestamp AS transferred_from_timestamp
1068 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1069 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1070 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1072 $query .= $cancelled
1074 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1077 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1085 $orderby ||= q|biblioitems.publishercode, biblio.title|;
1087 AND (datecancellationprinted IS NOT NULL
1088 AND datecancellationprinted <> '0000-00-00')
1093 q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|;
1095 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1099 $query .= " ORDER BY $orderby";
1101 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1106 #------------------------------------------------------------#
1108 =head3 GetOrdersByBiblionumber
1110 @orders = &GetOrdersByBiblionumber($biblionumber);
1112 Looks up the orders with linked to a specific $biblionumber, including
1113 cancelled orders and received orders.
1116 C<@orders> is an array of references-to-hash, whose keys are the
1117 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1121 sub GetOrdersByBiblionumber {
1122 my $biblionumber = shift;
1123 return unless $biblionumber;
1124 my $dbh = C4::Context->dbh;
1126 SELECT biblio.*,biblioitems.*,
1130 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1131 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1132 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1133 WHERE aqorders.biblionumber=?
1136 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1137 return @{$result_set};
1141 #------------------------------------------------------------#
1145 $order = &GetOrder($ordernumber);
1147 Looks up an order by order number.
1149 Returns a reference-to-hash describing the order. The keys of
1150 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1155 my ($ordernumber) = @_;
1156 return unless $ordernumber;
1158 my $dbh = C4::Context->dbh;
1159 my $query = qq{SELECT
1163 aqbasket.basketname,
1164 borrowers.branchcode,
1165 biblioitems.publicationyear,
1166 biblio.copyrightdate,
1167 biblioitems.editionstatement,
1171 biblioitems.publishercode,
1172 aqorders.rrp AS unitpricesupplier,
1173 aqorders.ecost AS unitpricelib,
1174 aqorders.claims_count AS claims_count,
1175 aqorders.claimed_date AS claimed_date,
1176 aqbudgets.budget_name AS budget,
1177 aqbooksellers.name AS supplier,
1178 aqbooksellers.id AS supplierid,
1179 biblioitems.publishercode AS publisher,
1180 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1181 DATE(aqbasket.closedate) AS orderdate,
1182 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1183 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1184 DATEDIFF(CURDATE( ),closedate) AS latesince
1185 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1186 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1187 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1188 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1189 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1190 WHERE aqorders.basketno = aqbasket.basketno
1193 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1195 # result_set assumed to contain 1 match
1196 return $result_set->[0];
1199 =head3 GetLastOrderNotReceivedFromSubscriptionid
1201 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1203 Returns a reference-to-hash describing the last order not received for a subscription.
1207 sub GetLastOrderNotReceivedFromSubscriptionid {
1208 my ( $subscriptionid ) = @_;
1209 my $dbh = C4::Context->dbh;
1211 SELECT * FROM aqorders
1212 LEFT JOIN subscription
1213 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1214 WHERE aqorders.subscriptionid = ?
1215 AND aqorders.datereceived IS NULL
1219 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1221 # result_set assumed to contain 1 match
1222 return $result_set->[0];
1225 =head3 GetLastOrderReceivedFromSubscriptionid
1227 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1229 Returns a reference-to-hash describing the last order received for a subscription.
1233 sub GetLastOrderReceivedFromSubscriptionid {
1234 my ( $subscriptionid ) = @_;
1235 my $dbh = C4::Context->dbh;
1237 SELECT * FROM aqorders
1238 LEFT JOIN subscription
1239 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1240 WHERE aqorders.subscriptionid = ?
1241 AND aqorders.datereceived =
1243 SELECT MAX( aqorders.datereceived )
1245 LEFT JOIN subscription
1246 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1247 WHERE aqorders.subscriptionid = ?
1248 AND aqorders.datereceived IS NOT NULL
1250 ORDER BY ordernumber DESC
1254 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1256 # result_set assumed to contain 1 match
1257 return $result_set->[0];
1261 #------------------------------------------------------------#
1265 &ModOrder(\%hashref);
1267 Modifies an existing order. Updates the order with order number
1268 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1269 other keys of the hash update the fields with the same name in the aqorders
1270 table of the Koha database.
1275 my $orderinfo = shift;
1277 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '';
1279 my $dbh = C4::Context->dbh;
1282 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1283 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1285 # delete($orderinfo->{'branchcode'});
1286 # the hash contains a lot of entries not in aqorders, so get the columns ...
1287 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1289 my $colnames = $sth->{NAME};
1290 #FIXME Be careful. If aqorders would have columns with diacritics,
1291 #you should need to decode what you get back from NAME.
1292 #See report 10110 and guided_reports.pl
1293 my $query = "UPDATE aqorders SET ";
1295 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1296 # ... and skip hash entries that are not in the aqorders table
1297 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1298 next unless grep(/^$orderinfokey$/, @$colnames);
1299 $query .= "$orderinfokey=?, ";
1300 push(@params, $orderinfo->{$orderinfokey});
1303 $query .= "timestamp=NOW() WHERE ordernumber=?";
1304 push(@params, $orderinfo->{'ordernumber'} );
1305 $sth = $dbh->prepare($query);
1306 $sth->execute(@params);
1310 #------------------------------------------------------------#
1314 ModItemOrder($itemnumber, $ordernumber);
1316 Modifies the ordernumber of an item in aqorders_items.
1321 my ($itemnumber, $ordernumber) = @_;
1323 return unless ($itemnumber and $ordernumber);
1325 my $dbh = C4::Context->dbh;
1327 UPDATE aqorders_items
1329 WHERE itemnumber = ?
1331 my $sth = $dbh->prepare($query);
1332 return $sth->execute($ordernumber, $itemnumber);
1335 #------------------------------------------------------------#
1337 =head3 ModReceiveOrder
1339 my ( $date_received, $new_ordernumber ) = ModReceiveOrder(
1341 biblionumber => $biblionumber,
1343 quantityreceived => $quantityreceived,
1345 invoice => $invoice,
1346 budget_id => $budget_id,
1347 received_itemnumbers => \@received_itemnumbers,
1348 order_internalnote => $order_internalnote,
1352 Updates an order, to reflect the fact that it was received, at least
1355 If a partial order is received, splits the order into two.
1357 Updates the order with biblionumber C<$biblionumber> and ordernumber
1358 C<$order->{ordernumber}>.
1363 sub ModReceiveOrder {
1365 my $biblionumber = $params->{biblionumber};
1366 my $order = { %{ $params->{order} } }; # Copy the order, we don't want to modify it
1367 my $invoice = $params->{invoice};
1368 my $quantrec = $params->{quantityreceived};
1369 my $user = $params->{user};
1370 my $budget_id = $params->{budget_id};
1371 my $received_items = $params->{received_items};
1373 my $dbh = C4::Context->dbh;
1374 my $datereceived = ( $invoice and $invoice->{datereceived} ) ? $invoice->{datereceived} : dt_from_string;
1375 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1376 if ($suggestionid) {
1377 ModSuggestion( {suggestionid=>$suggestionid,
1378 STATUS=>'AVAILABLE',
1379 biblionumber=> $biblionumber}
1383 my $result_set = $dbh->selectrow_arrayref(
1384 q{SELECT aqbasket.is_standing
1386 WHERE basketno=?},{ Slice => {} }, $order->{basketno});
1387 my $is_standing = $result_set->[0]; # we assume we have a unique basket
1389 my $new_ordernumber = $order->{ordernumber};
1390 if ( $is_standing || $order->{quantity} > $quantrec ) {
1391 # Split order line in two parts: the first is the original order line
1392 # without received items (the quantity is decreased),
1393 # the second part is a new order line with quantity=quantityrec
1394 # (entirely received)
1398 orderstatus = 'partial'|;
1399 $query .= q|, order_internalnote = ?| if defined $order->{order_internalnote};
1400 $query .= q| WHERE ordernumber = ?|;
1401 my $sth = $dbh->prepare($query);
1404 ( $is_standing ? 1 : ($order->{quantity} - $quantrec) ),
1405 ( defined $order->{order_internalnote} ? $order->{order_internalnote} : () ),
1406 $order->{ordernumber}
1409 # Recalculate tax_value
1412 SET tax_value = quantity * ecost_tax_excluded * tax_rate
1413 WHERE ordernumber = ?
1414 |, undef, $order->{ordernumber});
1416 delete $order->{ordernumber};
1417 $order->{budget_id} = ( $budget_id || $order->{budget_id} );
1418 $order->{quantity} = $quantrec;
1419 $order->{quantityreceived} = $quantrec;
1420 $order->{tax_value} = $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate};
1421 $order->{datereceived} = $datereceived;
1422 $order->{invoiceid} = $invoice->{invoiceid};
1423 $order->{orderstatus} = 'complete';
1424 $new_ordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber};
1426 if ($received_items) {
1427 foreach my $itemnumber (@$received_items) {
1428 ModItemOrder($itemnumber, $new_ordernumber);
1434 SET quantityreceived = ?,
1438 orderstatus = 'complete'
1442 , unitprice = ?, unitprice_tax_included = ?, unitprice_tax_excluded = ?
1443 | if defined $order->{unitprice};
1446 ,tax_value_on_receiving = ?
1447 | if defined $order->{tax_value_on_receiving};
1450 ,tax_rate_on_receiving = ?
1451 | if defined $order->{tax_rate_on_receiving};
1454 , order_internalnote = ?
1455 | if defined $order->{order_internalnote};
1457 $query .= q| where biblionumber=? and ordernumber=?|;
1459 my $sth = $dbh->prepare( $query );
1460 my @params = ( $quantrec, $datereceived, $invoice->{invoiceid}, $budget_id );
1462 if ( defined $order->{unitprice} ) {
1463 push @params, $order->{unitprice}, $order->{unitprice_tax_included}, $order->{unitprice_tax_excluded};
1466 if ( defined $order->{tax_value_on_receiving} ) {
1467 push @params, $order->{tax_value_on_receiving};
1470 if ( defined $order->{tax_rate_on_receiving} ) {
1471 push @params, $order->{tax_rate_on_receiving};
1474 if ( defined $order->{order_internalnote} ) {
1475 push @params, $order->{order_internalnote};
1478 push @params, ( $biblionumber, $order->{ordernumber} );
1480 $sth->execute( @params );
1482 # All items have been received, sent a notification to users
1483 NotifyOrderUsers( $order->{ordernumber} );
1486 return ($datereceived, $new_ordernumber);
1489 =head3 CancelReceipt
1491 my $parent_ordernumber = CancelReceipt($ordernumber);
1493 Cancel an order line receipt and update the parent order line, as if no
1495 If items are created at receipt (AcqCreateItem = receiving) then delete
1501 my $ordernumber = shift;
1503 return unless $ordernumber;
1505 my $dbh = C4::Context->dbh;
1507 SELECT datereceived, parent_ordernumber, quantity
1509 WHERE ordernumber = ?
1511 my $sth = $dbh->prepare($query);
1512 $sth->execute($ordernumber);
1513 my $order = $sth->fetchrow_hashref;
1515 warn "CancelReceipt: order $ordernumber does not exist";
1518 unless($order->{'datereceived'}) {
1519 warn "CancelReceipt: order $ordernumber is not received";
1523 my $parent_ordernumber = $order->{'parent_ordernumber'};
1525 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1527 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1528 # The order line has no parent, just mark it as not received
1531 SET quantityreceived = ?,
1534 orderstatus = 'ordered'
1535 WHERE ordernumber = ?
1537 $sth = $dbh->prepare($query);
1538 $sth->execute(0, undef, undef, $ordernumber);
1539 _cancel_items_receipt( $ordernumber );
1541 # The order line has a parent, increase parent quantity and delete
1544 SELECT quantity, datereceived
1546 WHERE ordernumber = ?
1548 $sth = $dbh->prepare($query);
1549 $sth->execute($parent_ordernumber);
1550 my $parent_order = $sth->fetchrow_hashref;
1551 unless($parent_order) {
1552 warn "Parent order $parent_ordernumber does not exist.";
1555 if($parent_order->{'datereceived'}) {
1556 warn "CancelReceipt: parent order is received.".
1557 " Can't cancel receipt.";
1563 orderstatus = 'ordered'
1564 WHERE ordernumber = ?
1566 $sth = $dbh->prepare($query);
1567 my $rv = $sth->execute(
1568 $order->{'quantity'} + $parent_order->{'quantity'},
1572 warn "Cannot update parent order line, so do not cancel".
1577 # Recalculate tax_value
1580 SET tax_value = quantity * ecost_tax_excluded * tax_rate
1581 WHERE ordernumber = ?
1582 |, undef, $parent_ordernumber);
1584 _cancel_items_receipt( $ordernumber, $parent_ordernumber );
1587 DELETE FROM aqorders
1588 WHERE ordernumber = ?
1590 $sth = $dbh->prepare($query);
1591 $sth->execute($ordernumber);
1595 if(C4::Context->preference('AcqCreateItem') eq 'ordering') {
1596 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1598 for my $in ( @itemnumbers ) {
1599 my $biblionumber = C4::Biblio::GetBiblionumberFromItemnumber( $in );
1600 my $frameworkcode = GetFrameworkCode($biblionumber);
1601 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber', $frameworkcode );
1602 my $item = C4::Items::GetMarcItem( $biblionumber, $in );
1603 for my $affect ( @affects ) {
1604 my ( $sf, $v ) = split q{=}, $affect, 2;
1605 foreach ( $item->field($itemfield) ) {
1606 $_->update( $sf => $v );
1609 C4::Items::ModItemFromMarc( $item, $biblionumber, $in );
1614 return $parent_ordernumber;
1617 sub _cancel_items_receipt {
1618 my ( $ordernumber, $parent_ordernumber ) = @_;
1619 $parent_ordernumber ||= $ordernumber;
1621 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1622 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1623 # Remove items that were created at receipt
1625 DELETE FROM items, aqorders_items
1626 USING items, aqorders_items
1627 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1629 my $dbh = C4::Context->dbh;
1630 my $sth = $dbh->prepare($query);
1631 foreach my $itemnumber (@itemnumbers) {
1632 $sth->execute($itemnumber, $itemnumber);
1636 foreach my $itemnumber (@itemnumbers) {
1637 ModItemOrder($itemnumber, $parent_ordernumber);
1642 #------------------------------------------------------------#
1646 @results = &SearchOrders({
1647 ordernumber => $ordernumber,
1649 biblionumber => $biblionumber,
1651 booksellerid => $booksellerid,
1652 basketno => $basketno,
1658 Searches for orders.
1660 C<$owner> Finds order for the logged in user.
1661 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1662 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1665 C<@results> is an array of references-to-hash with the keys are fields
1666 from aqorders, biblio, biblioitems and aqbasket tables.
1671 my ( $params ) = @_;
1672 my $ordernumber = $params->{ordernumber};
1673 my $search = $params->{search};
1674 my $ean = $params->{ean};
1675 my $booksellerid = $params->{booksellerid};
1676 my $basketno = $params->{basketno};
1677 my $basketname = $params->{basketname};
1678 my $basketgroupname = $params->{basketgroupname};
1679 my $owner = $params->{owner};
1680 my $pending = $params->{pending};
1681 my $ordered = $params->{ordered};
1682 my $biblionumber = $params->{biblionumber};
1683 my $budget_id = $params->{budget_id};
1685 my $dbh = C4::Context->dbh;
1688 SELECT aqbasket.basketno,
1690 borrowers.firstname,
1693 biblioitems.biblioitemnumber,
1694 aqbasket.authorisedby,
1695 aqbasket.booksellerid,
1697 aqbasket.creationdate,
1698 aqbasket.basketname,
1699 aqbasketgroups.id as basketgroupid,
1700 aqbasketgroups.name as basketgroupname,
1703 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1704 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1705 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1706 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1707 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1710 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1712 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1716 WHERE (datecancellationprinted is NULL)
1719 if ( $pending or $ordered ) {
1722 ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) )
1724 ( quantity > quantityreceived OR quantityreceived is NULL )
1728 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1736 my $userenv = C4::Context->userenv;
1737 if ( C4::Context->preference("IndependentBranches") ) {
1738 unless ( C4::Context->IsSuperLibrarian() ) {
1741 borrowers.branchcode = ?
1742 OR borrowers.branchcode = ''
1745 push @args, $userenv->{branch};
1749 if ( $ordernumber ) {
1750 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1751 push @args, ( $ordernumber, $ordernumber );
1753 if ( $biblionumber ) {
1754 $query .= 'AND aqorders.biblionumber = ?';
1755 push @args, $biblionumber;
1758 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1759 push @args, ("%$search%","%$search%","%$search%");
1762 $query .= ' AND biblioitems.ean = ?';
1765 if ( $booksellerid ) {
1766 $query .= 'AND aqbasket.booksellerid = ?';
1767 push @args, $booksellerid;
1770 $query .= 'AND aqbasket.basketno = ?';
1771 push @args, $basketno;
1774 $query .= 'AND aqbasket.basketname LIKE ?';
1775 push @args, "%$basketname%";
1777 if( $basketgroupname ) {
1778 $query .= ' AND aqbasketgroups.name LIKE ?';
1779 push @args, "%$basketgroupname%";
1783 $query .= ' AND aqbasket.authorisedby=? ';
1784 push @args, $userenv->{'number'};
1788 $query .= ' AND aqorders.budget_id = ?';
1789 push @args, $budget_id;
1792 $query .= ' ORDER BY aqbasket.basketno';
1794 my $sth = $dbh->prepare($query);
1795 $sth->execute(@args);
1796 return $sth->fetchall_arrayref({});
1799 #------------------------------------------------------------#
1803 &DelOrder($biblionumber, $ordernumber);
1805 Cancel the order with the given order and biblio numbers. It does not
1806 delete any entries in the aqorders table, it merely marks them as
1812 my ( $bibnum, $ordernumber, $delete_biblio, $reason ) = @_;
1815 my $dbh = C4::Context->dbh;
1818 SET datecancellationprinted=now(), orderstatus='cancelled'
1821 $query .= ", cancellationreason = ? ";
1824 WHERE biblionumber=? AND ordernumber=?
1826 my $sth = $dbh->prepare($query);
1828 $sth->execute($reason, $bibnum, $ordernumber);
1830 $sth->execute( $bibnum, $ordernumber );
1834 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1835 foreach my $itemnumber (@itemnumbers){
1836 my $delcheck = C4::Items::DelItemCheck( $bibnum, $itemnumber );
1838 if($delcheck != 1) {
1839 $error->{'delitem'} = 1;
1843 if($delete_biblio) {
1844 # We get the number of remaining items
1845 my $itemcount = C4::Items::GetItemsCount($bibnum);
1847 # If there are no items left,
1848 if ( $itemcount == 0 ) {
1849 # We delete the record
1850 my $delcheck = DelBiblio($bibnum);
1853 $error->{'delbiblio'} = 1;
1861 =head3 TransferOrder
1863 my $newordernumber = TransferOrder($ordernumber, $basketno);
1865 Transfer an order line to a basket.
1866 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1867 to BOOKSELLER on DATE' and create new order with internal note
1868 'Transferred from BOOKSELLER on DATE'.
1869 Move all attached items to the new order.
1870 Received orders cannot be transferred.
1871 Return the ordernumber of created order.
1876 my ($ordernumber, $basketno) = @_;
1878 return unless ($ordernumber and $basketno);
1880 my $order = GetOrder( $ordernumber );
1881 return if $order->{datereceived};
1882 my $basket = GetBasket($basketno);
1883 return unless $basket;
1885 my $dbh = C4::Context->dbh;
1886 my ($query, $sth, $rv);
1890 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1891 WHERE ordernumber = ?
1893 $sth = $dbh->prepare($query);
1894 $rv = $sth->execute('cancelled', $ordernumber);
1896 delete $order->{'ordernumber'};
1897 delete $order->{parent_ordernumber};
1898 $order->{'basketno'} = $basketno;
1900 my $newordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber};
1903 UPDATE aqorders_items
1905 WHERE ordernumber = ?
1907 $sth = $dbh->prepare($query);
1908 $sth->execute($newordernumber, $ordernumber);
1911 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1914 $sth = $dbh->prepare($query);
1915 $sth->execute($ordernumber, $newordernumber);
1917 return $newordernumber;
1920 =head2 FUNCTIONS ABOUT PARCELS
1924 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1926 get a lists of parcels.
1933 is the bookseller this function has to get parcels.
1936 To know on what criteria the results list has to be ordered.
1939 is the booksellerinvoicenumber.
1941 =item $datefrom & $dateto
1942 to know on what date this function has to filter its search.
1947 a pointer on a hash list containing parcel informations as such :
1953 =item Last operation
1955 =item Number of biblio
1957 =item Number of items
1964 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1965 my $dbh = C4::Context->dbh;
1966 my @query_params = ();
1968 SELECT aqinvoices.invoicenumber,
1969 datereceived,purchaseordernumber,
1970 count(DISTINCT biblionumber) AS biblio,
1971 sum(quantity) AS itemsexpected,
1972 sum(quantityreceived) AS itemsreceived
1973 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1974 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1975 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1977 push @query_params, $bookseller;
1979 if ( defined $code ) {
1980 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1981 # add a % to the end of the code to allow stemming.
1982 push @query_params, "$code%";
1985 if ( defined $datefrom ) {
1986 $strsth .= ' and datereceived >= ? ';
1987 push @query_params, $datefrom;
1990 if ( defined $dateto ) {
1991 $strsth .= 'and datereceived <= ? ';
1992 push @query_params, $dateto;
1995 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1997 # can't use a placeholder to place this column name.
1998 # but, we could probably be checking to make sure it is a column that will be fetched.
1999 $strsth .= "order by $order " if ($order);
2001 my $sth = $dbh->prepare($strsth);
2003 $sth->execute( @query_params );
2004 my $results = $sth->fetchall_arrayref({});
2008 #------------------------------------------------------------#
2010 =head3 GetLateOrders
2012 @results = &GetLateOrders;
2014 Searches for bookseller with late orders.
2017 the table of supplier with late issues. This table is full of hashref.
2023 my $supplierid = shift;
2025 my $estimateddeliverydatefrom = shift;
2026 my $estimateddeliverydateto = shift;
2028 my $dbh = C4::Context->dbh;
2030 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2031 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2033 my @query_params = ();
2035 SELECT aqbasket.basketno,
2036 aqorders.ordernumber,
2037 DATE(aqbasket.closedate) AS orderdate,
2038 aqbasket.basketname AS basketname,
2039 aqbasket.basketgroupid AS basketgroupid,
2040 aqbasketgroups.name AS basketgroupname,
2041 aqorders.rrp AS unitpricesupplier,
2042 aqorders.ecost AS unitpricelib,
2043 aqorders.claims_count AS claims_count,
2044 aqorders.claimed_date AS claimed_date,
2045 aqbudgets.budget_name AS budget,
2046 borrowers.branchcode AS branch,
2047 aqbooksellers.name AS supplier,
2048 aqbooksellers.id AS supplierid,
2049 biblio.author, biblio.title,
2050 biblioitems.publishercode AS publisher,
2051 biblioitems.publicationyear,
2052 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2056 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2057 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2058 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2059 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2060 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2061 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2062 WHERE aqorders.basketno = aqbasket.basketno
2063 AND ( datereceived = ''
2064 OR datereceived IS NULL
2065 OR aqorders.quantityreceived < aqorders.quantity
2067 AND aqbasket.closedate IS NOT NULL
2068 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2071 if ($dbdriver eq "mysql") {
2073 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2074 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2075 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2077 if ( defined $delay ) {
2078 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2079 push @query_params, $delay;
2082 HAVING quantity <> 0
2083 AND unitpricesupplier <> 0
2084 AND unitpricelib <> 0
2087 # FIXME: account for IFNULL as above
2089 aqorders.quantity AS quantity,
2090 aqorders.quantity * aqorders.rrp AS subtotal,
2091 (CAST(now() AS date) - closedate) AS latesince
2093 if ( defined $delay ) {
2094 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2095 push @query_params, $delay;
2098 if (defined $supplierid) {
2099 $from .= ' AND aqbasket.booksellerid = ? ';
2100 push @query_params, $supplierid;
2102 if (defined $branch) {
2103 $from .= ' AND borrowers.branchcode LIKE ? ';
2104 push @query_params, $branch;
2107 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2108 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2110 if ( defined $estimateddeliverydatefrom ) {
2111 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2112 push @query_params, $estimateddeliverydatefrom;
2114 if ( defined $estimateddeliverydateto ) {
2115 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2116 push @query_params, $estimateddeliverydateto;
2118 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2119 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2121 if (C4::Context->preference("IndependentBranches")
2122 && !C4::Context->IsSuperLibrarian() ) {
2123 $from .= ' AND borrowers.branchcode LIKE ? ';
2124 push @query_params, C4::Context->userenv->{branch};
2126 $from .= " AND orderstatus <> 'cancelled' ";
2127 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2128 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2129 my $sth = $dbh->prepare($query);
2130 $sth->execute(@query_params);
2132 while (my $data = $sth->fetchrow_hashref) {
2133 push @results, $data;
2138 #------------------------------------------------------------#
2142 \@order_loop = GetHistory( %params );
2144 Retreives some acquisition history information
2154 basket - search both basket name and number
2155 booksellerinvoicenumber
2158 orderstatus (note that orderstatus '' will retrieve orders
2159 of any status except cancelled)
2161 get_canceled_order (if set to a true value, cancelled orders will
2165 $order_loop is a list of hashrefs that each look like this:
2167 'author' => 'Twain, Mark',
2169 'biblionumber' => '215',
2171 'creationdate' => 'MM/DD/YYYY',
2172 'datereceived' => undef,
2175 'invoicenumber' => undef,
2177 'ordernumber' => '1',
2179 'quantityreceived' => undef,
2180 'title' => 'The Adventures of Huckleberry Finn'
2186 # don't run the query if there are no parameters (list would be too long for sure !)
2187 croak "No search params" unless @_;
2189 my $title = $params{title};
2190 my $author = $params{author};
2191 my $isbn = $params{isbn};
2192 my $ean = $params{ean};
2193 my $name = $params{name};
2194 my $from_placed_on = $params{from_placed_on};
2195 my $to_placed_on = $params{to_placed_on};
2196 my $basket = $params{basket};
2197 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2198 my $basketgroupname = $params{basketgroupname};
2199 my $budget = $params{budget};
2200 my $orderstatus = $params{orderstatus};
2201 my $biblionumber = $params{biblionumber};
2202 my $get_canceled_order = $params{get_canceled_order} || 0;
2203 my $ordernumber = $params{ordernumber};
2204 my $search_children_too = $params{search_children_too} || 0;
2205 my $created_by = $params{created_by} || [];
2209 my $total_qtyreceived = 0;
2210 my $total_price = 0;
2212 my $dbh = C4::Context->dbh;
2215 COALESCE(biblio.title, deletedbiblio.title) AS title,
2216 COALESCE(biblio.author, deletedbiblio.author) AS author,
2217 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2218 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2220 aqbasket.basketname,
2221 aqbasket.basketgroupid,
2222 aqbasket.authorisedby,
2223 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2224 aqbasketgroups.name as groupname,
2226 aqbasket.creationdate,
2227 aqorders.datereceived,
2229 aqorders.quantityreceived,
2231 aqorders.ordernumber,
2233 aqinvoices.invoicenumber,
2234 aqbooksellers.id as id,
2235 aqorders.biblionumber,
2236 aqorders.orderstatus,
2237 aqorders.parent_ordernumber,
2238 aqbudgets.budget_name
2240 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2243 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2244 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2245 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2246 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2247 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2248 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2249 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2250 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2251 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2252 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2255 $query .= " WHERE 1 ";
2257 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2258 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2261 my @query_params = ();
2263 if ( $biblionumber ) {
2264 $query .= " AND biblio.biblionumber = ?";
2265 push @query_params, $biblionumber;
2269 $query .= " AND biblio.title LIKE ? ";
2270 $title =~ s/\s+/%/g;
2271 push @query_params, "%$title%";
2275 $query .= " AND biblio.author LIKE ? ";
2276 push @query_params, "%$author%";
2280 $query .= " AND biblioitems.isbn LIKE ? ";
2281 push @query_params, "%$isbn%";
2284 $query .= " AND biblioitems.ean = ? ";
2285 push @query_params, "$ean";
2288 $query .= " AND aqbooksellers.name LIKE ? ";
2289 push @query_params, "%$name%";
2293 $query .= " AND aqbudgets.budget_id = ? ";
2294 push @query_params, "$budget";
2297 if ( $from_placed_on ) {
2298 $query .= " AND creationdate >= ? ";
2299 push @query_params, $from_placed_on;
2302 if ( $to_placed_on ) {
2303 $query .= " AND creationdate <= ? ";
2304 push @query_params, $to_placed_on;
2307 if ( defined $orderstatus and $orderstatus ne '') {
2308 $query .= " AND aqorders.orderstatus = ? ";
2309 push @query_params, "$orderstatus";
2313 if ($basket =~ m/^\d+$/) {
2314 $query .= " AND aqorders.basketno = ? ";
2315 push @query_params, $basket;
2317 $query .= " AND aqbasket.basketname LIKE ? ";
2318 push @query_params, "%$basket%";
2322 if ($booksellerinvoicenumber) {
2323 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2324 push @query_params, "%$booksellerinvoicenumber%";
2327 if ($basketgroupname) {
2328 $query .= " AND aqbasketgroups.name LIKE ? ";
2329 push @query_params, "%$basketgroupname%";
2333 $query .= " AND (aqorders.ordernumber = ? ";
2334 push @query_params, $ordernumber;
2335 if ($search_children_too) {
2336 $query .= " OR aqorders.parent_ordernumber = ? ";
2337 push @query_params, $ordernumber;
2342 if ( @$created_by ) {
2343 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2344 push @query_params, @$created_by;
2348 if ( C4::Context->preference("IndependentBranches") ) {
2349 unless ( C4::Context->IsSuperLibrarian() ) {
2350 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2351 push @query_params, C4::Context->userenv->{branch};
2354 $query .= " ORDER BY id";
2356 return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2359 =head2 GetRecentAcqui
2361 $results = GetRecentAcqui($days);
2363 C<$results> is a ref to a table which containts hashref
2367 sub GetRecentAcqui {
2369 my $dbh = C4::Context->dbh;
2373 ORDER BY timestamp DESC
2376 my $sth = $dbh->prepare($query);
2378 my $results = $sth->fetchall_arrayref({});
2382 #------------------------------------------------------------#
2386 &AddClaim($ordernumber);
2388 Add a claim for an order
2393 my ($ordernumber) = @_;
2394 my $dbh = C4::Context->dbh;
2397 claims_count = claims_count + 1,
2398 claimed_date = CURDATE()
2399 WHERE ordernumber = ?
2401 my $sth = $dbh->prepare($query);
2402 $sth->execute($ordernumber);
2407 my @invoices = GetInvoices(
2408 invoicenumber => $invoicenumber,
2409 supplierid => $supplierid,
2410 suppliername => $suppliername,
2411 shipmentdatefrom => $shipmentdatefrom, # ISO format
2412 shipmentdateto => $shipmentdateto, # ISO format
2413 billingdatefrom => $billingdatefrom, # ISO format
2414 billingdateto => $billingdateto, # ISO format
2415 isbneanissn => $isbn_or_ean_or_issn,
2418 publisher => $publisher,
2419 publicationyear => $publicationyear,
2420 branchcode => $branchcode,
2421 order_by => $order_by
2424 Return a list of invoices that match all given criteria.
2426 $order_by is "column_name (asc|desc)", where column_name is any of
2427 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2428 'shipmentcost', 'shipmentcost_budgetid'.
2430 asc is the default if omitted
2437 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2438 closedate shipmentcost shipmentcost_budgetid);
2440 my $dbh = C4::Context->dbh;
2442 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2445 aqorders.datereceived IS NOT NULL,
2446 aqorders.biblionumber,
2449 ) AS receivedbiblios,
2452 aqorders.subscriptionid IS NOT NULL,
2453 aqorders.subscriptionid,
2456 ) AS is_linked_to_subscriptions,
2457 SUM(aqorders.quantityreceived) AS receiveditems
2459 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2460 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2461 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2462 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2463 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2464 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2465 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2470 if($args{supplierid}) {
2471 push @bind_strs, " aqinvoices.booksellerid = ? ";
2472 push @bind_args, $args{supplierid};
2474 if($args{invoicenumber}) {
2475 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2476 push @bind_args, "%$args{invoicenumber}%";
2478 if($args{suppliername}) {
2479 push @bind_strs, " aqbooksellers.name LIKE ? ";
2480 push @bind_args, "%$args{suppliername}%";
2482 if($args{shipmentdatefrom}) {
2483 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2484 push @bind_args, $args{shipmentdatefrom};
2486 if($args{shipmentdateto}) {
2487 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2488 push @bind_args, $args{shipmentdateto};
2490 if($args{billingdatefrom}) {
2491 push @bind_strs, " aqinvoices.billingdate >= ? ";
2492 push @bind_args, $args{billingdatefrom};
2494 if($args{billingdateto}) {
2495 push @bind_strs, " aqinvoices.billingdate <= ? ";
2496 push @bind_args, $args{billingdateto};
2498 if($args{isbneanissn}) {
2499 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2500 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2503 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2504 push @bind_args, $args{title};
2507 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2508 push @bind_args, $args{author};
2510 if($args{publisher}) {
2511 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2512 push @bind_args, $args{publisher};
2514 if($args{publicationyear}) {
2515 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2516 push @bind_args, $args{publicationyear}, $args{publicationyear};
2518 if($args{branchcode}) {
2519 push @bind_strs, " borrowers.branchcode = ? ";
2520 push @bind_args, $args{branchcode};
2522 if($args{message_id}) {
2523 push @bind_strs, " aqinvoices.message_id = ? ";
2524 push @bind_args, $args{message_id};
2527 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2528 $query .= " GROUP BY aqinvoices.invoiceid ";
2530 if($args{order_by}) {
2531 my ($column, $direction) = split / /, $args{order_by};
2532 if(grep /^$column$/, @columns) {
2533 $direction ||= 'ASC';
2534 $query .= " ORDER BY $column $direction";
2538 my $sth = $dbh->prepare($query);
2539 $sth->execute(@bind_args);
2541 my $results = $sth->fetchall_arrayref({});
2547 my $invoice = GetInvoice($invoiceid);
2549 Get informations about invoice with given $invoiceid
2551 Return a hash filled with aqinvoices.* fields
2556 my ($invoiceid) = @_;
2559 return unless $invoiceid;
2561 my $dbh = C4::Context->dbh;
2567 my $sth = $dbh->prepare($query);
2568 $sth->execute($invoiceid);
2570 $invoice = $sth->fetchrow_hashref;
2574 =head3 GetInvoiceDetails
2576 my $invoice = GetInvoiceDetails($invoiceid)
2578 Return informations about an invoice + the list of related order lines
2580 Orders informations are in $invoice->{orders} (array ref)
2584 sub GetInvoiceDetails {
2585 my ($invoiceid) = @_;
2587 if ( !defined $invoiceid ) {
2588 carp 'GetInvoiceDetails called without an invoiceid';
2592 my $dbh = C4::Context->dbh;
2594 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2596 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2599 my $sth = $dbh->prepare($query);
2600 $sth->execute($invoiceid);
2602 my $invoice = $sth->fetchrow_hashref;
2607 biblio.copyrightdate,
2608 biblioitems.publishercode,
2609 biblioitems.publicationyear,
2610 aqbasket.basketname,
2611 aqbasketgroups.id AS basketgroupid,
2612 aqbasketgroups.name AS basketgroupname
2614 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2615 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2616 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2617 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2620 $sth = $dbh->prepare($query);
2621 $sth->execute($invoiceid);
2622 $invoice->{orders} = $sth->fetchall_arrayref({});
2623 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2630 my $invoiceid = AddInvoice(
2631 invoicenumber => $invoicenumber,
2632 booksellerid => $booksellerid,
2633 shipmentdate => $shipmentdate,
2634 billingdate => $billingdate,
2635 closedate => $closedate,
2636 shipmentcost => $shipmentcost,
2637 shipmentcost_budgetid => $shipmentcost_budgetid
2640 Create a new invoice and return its id or undef if it fails.
2647 return unless(%invoice and $invoice{invoicenumber});
2649 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2650 closedate shipmentcost shipmentcost_budgetid message_id);
2654 foreach my $key (keys %invoice) {
2655 if(0 < grep(/^$key$/, @columns)) {
2656 push @set_strs, "$key = ?";
2657 push @set_args, ($invoice{$key} || undef);
2663 my $dbh = C4::Context->dbh;
2664 my $query = "INSERT INTO aqinvoices SET ";
2665 $query .= join (",", @set_strs);
2666 my $sth = $dbh->prepare($query);
2667 $rv = $sth->execute(@set_args);
2669 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2678 invoiceid => $invoiceid, # Mandatory
2679 invoicenumber => $invoicenumber,
2680 booksellerid => $booksellerid,
2681 shipmentdate => $shipmentdate,
2682 billingdate => $billingdate,
2683 closedate => $closedate,
2684 shipmentcost => $shipmentcost,
2685 shipmentcost_budgetid => $shipmentcost_budgetid
2688 Modify an invoice, invoiceid is mandatory.
2690 Return undef if it fails.
2697 return unless(%invoice and $invoice{invoiceid});
2699 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2700 closedate shipmentcost shipmentcost_budgetid);
2704 foreach my $key (keys %invoice) {
2705 if(0 < grep(/^$key$/, @columns)) {
2706 push @set_strs, "$key = ?";
2707 push @set_args, ($invoice{$key} || undef);
2711 my $dbh = C4::Context->dbh;
2712 my $query = "UPDATE aqinvoices SET ";
2713 $query .= join(",", @set_strs);
2714 $query .= " WHERE invoiceid = ?";
2716 my $sth = $dbh->prepare($query);
2717 $sth->execute(@set_args, $invoice{invoiceid});
2722 CloseInvoice($invoiceid);
2726 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2731 my ($invoiceid) = @_;
2733 return unless $invoiceid;
2735 my $dbh = C4::Context->dbh;
2738 SET closedate = CAST(NOW() AS DATE)
2741 my $sth = $dbh->prepare($query);
2742 $sth->execute($invoiceid);
2745 =head3 ReopenInvoice
2747 ReopenInvoice($invoiceid);
2751 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => output_pref({ dt=>dt_from_string, dateonly=>1, otputpref=>'iso' }))
2756 my ($invoiceid) = @_;
2758 return unless $invoiceid;
2760 my $dbh = C4::Context->dbh;
2763 SET closedate = NULL
2766 my $sth = $dbh->prepare($query);
2767 $sth->execute($invoiceid);
2772 DelInvoice($invoiceid);
2774 Delete an invoice if there are no items attached to it.
2779 my ($invoiceid) = @_;
2781 return unless $invoiceid;
2783 my $dbh = C4::Context->dbh;
2789 my $sth = $dbh->prepare($query);
2790 $sth->execute($invoiceid);
2791 my $res = $sth->fetchrow_arrayref;
2792 if ( $res && $res->[0] == 0 ) {
2794 DELETE FROM aqinvoices
2797 my $sth = $dbh->prepare($query);
2798 return ( $sth->execute($invoiceid) > 0 );
2803 =head3 MergeInvoices
2805 MergeInvoices($invoiceid, \@sourceids);
2807 Merge the invoices identified by the IDs in \@sourceids into
2808 the invoice identified by $invoiceid.
2813 my ($invoiceid, $sourceids) = @_;
2815 return unless $invoiceid;
2816 foreach my $sourceid (@$sourceids) {
2817 next if $sourceid == $invoiceid;
2818 my $source = GetInvoiceDetails($sourceid);
2819 foreach my $order (@{$source->{'orders'}}) {
2820 $order->{'invoiceid'} = $invoiceid;
2823 DelInvoice($source->{'invoiceid'});
2828 =head3 GetBiblioCountByBasketno
2830 $biblio_count = &GetBiblioCountByBasketno($basketno);
2832 Looks up the biblio's count that has basketno value $basketno
2838 sub GetBiblioCountByBasketno {
2839 my ($basketno) = @_;
2840 my $dbh = C4::Context->dbh;
2842 SELECT COUNT( DISTINCT( biblionumber ) )
2845 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2848 my $sth = $dbh->prepare($query);
2849 $sth->execute($basketno);
2850 return $sth->fetchrow;
2853 # Note this subroutine should be moved to Koha::Acquisition::Order
2854 # Will do when a DBIC decision will be taken.
2855 sub populate_order_with_prices {
2858 my $order = $params->{order};
2859 my $booksellerid = $params->{booksellerid};
2860 return unless $booksellerid;
2862 my $bookseller = Koha::Acquisition::Bookseller->fetch({ id => $booksellerid });
2864 my $receiving = $params->{receiving};
2865 my $ordering = $params->{ordering};
2866 my $discount = $order->{discount};
2867 $discount /= 100 if $discount > 1;
2870 $order->{tax_rate_on_ordering} //= $order->{tax_rate};
2871 if ( $bookseller->{listincgst} ) {
2872 # The user entered the rrp tax included
2873 $order->{rrp_tax_included} = $order->{rrp};
2875 # rrp tax excluded = rrp tax included / ( 1 + tax rate )
2876 $order->{rrp_tax_excluded} = $order->{rrp_tax_included} / ( 1 + $order->{tax_rate_on_ordering} );
2878 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
2879 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
2881 # ecost tax included = rrp tax included ( 1 - discount )
2882 $order->{ecost_tax_included} = $order->{rrp_tax_included} * ( 1 - $discount );
2885 # The user entered the rrp tax excluded
2886 $order->{rrp_tax_excluded} = $order->{rrp};
2888 # rrp tax included = rrp tax excluded * ( 1 - tax rate )
2889 $order->{rrp_tax_included} = $order->{rrp_tax_excluded} * ( 1 + $order->{tax_rate_on_ordering} );
2891 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
2892 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
2894 # ecost tax included = rrp tax excluded * ( 1 - tax rate ) * ( 1 - discount )
2895 $order->{ecost_tax_included} =
2896 $order->{rrp_tax_excluded} *
2897 ( 1 + $order->{tax_rate_on_ordering} ) *
2901 # tax value = quantity * ecost tax excluded * tax rate
2902 $order->{tax_value_on_ordering} =
2903 $order->{quantity} * $order->{ecost_tax_excluded} * $order->{tax_rate_on_ordering};
2907 $order->{tax_rate_on_receiving} //= $order->{tax_rate};
2908 if ( $bookseller->{invoiceincgst} ) {
2909 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
2910 # we need to keep the exact ecost value
2911 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_included} )->round ) {
2912 $order->{unitprice} = $order->{ecost_tax_included};
2915 # The user entered the unit price tax included
2916 $order->{unitprice_tax_included} = $order->{unitprice};
2918 # unit price tax excluded = unit price tax included / ( 1 + tax rate )
2919 $order->{unitprice_tax_excluded} = $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate_on_receiving} );
2922 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
2923 # we need to keep the exact ecost value
2924 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_excluded} )->round ) {
2925 $order->{unitprice} = $order->{ecost_tax_excluded};
2928 # The user entered the unit price tax excluded
2929 $order->{unitprice_tax_excluded} = $order->{unitprice};
2932 # unit price tax included = unit price tax included * ( 1 + tax rate )
2933 $order->{unitprice_tax_included} = $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate_on_receiving} );
2936 # tax value = quantity * unit price tax excluded * tax rate
2937 $order->{tax_value_on_receiving} = $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate_on_receiving};
2943 =head3 GetOrderUsers
2945 $order_users_ids = &GetOrderUsers($ordernumber);
2947 Returns a list of all borrowernumbers that are in order users list
2952 my ($ordernumber) = @_;
2954 return unless $ordernumber;
2957 SELECT borrowernumber
2959 WHERE ordernumber = ?
2961 my $dbh = C4::Context->dbh;
2962 my $sth = $dbh->prepare($query);
2963 $sth->execute($ordernumber);
2964 my $results = $sth->fetchall_arrayref( {} );
2966 my @borrowernumbers;
2967 foreach (@$results) {
2968 push @borrowernumbers, $_->{'borrowernumber'};
2971 return @borrowernumbers;
2974 =head3 ModOrderUsers
2976 my @order_users_ids = (1, 2, 3);
2977 &ModOrderUsers($ordernumber, @basketusers_ids);
2979 Delete all users from order users list, and add users in C<@order_users_ids>
2985 my ( $ordernumber, @order_users_ids ) = @_;
2987 return unless $ordernumber;
2989 my $dbh = C4::Context->dbh;
2991 DELETE FROM aqorder_users
2992 WHERE ordernumber = ?
2994 my $sth = $dbh->prepare($query);
2995 $sth->execute($ordernumber);
2998 INSERT INTO aqorder_users (ordernumber, borrowernumber)
3001 $sth = $dbh->prepare($query);
3002 foreach my $order_user_id (@order_users_ids) {
3003 $sth->execute( $ordernumber, $order_user_id );
3007 sub NotifyOrderUsers {
3008 my ($ordernumber) = @_;
3010 my @borrowernumbers = GetOrderUsers($ordernumber);
3011 return unless @borrowernumbers;
3013 my $order = GetOrder( $ordernumber );
3014 for my $borrowernumber (@borrowernumbers) {
3015 my $borrower = C4::Members::GetMember( borrowernumber => $borrowernumber );
3016 my $library = Koha::Libraries->find( $borrower->{branchcode} )->unblessed;
3017 my $biblio = C4::Biblio::GetBiblio( $order->{biblionumber} );
3018 my $letter = C4::Letters::GetPreparedLetter(
3019 module => 'acquisition',
3020 letter_code => 'ACQ_NOTIF_ON_RECEIV',
3021 branchcode => $library->{branchcode},
3023 'branches' => $library,
3024 'borrowers' => $borrower,
3025 'biblio' => $biblio,
3026 'aqorders' => $order,
3030 C4::Letters::EnqueueLetter(
3033 borrowernumber => $borrowernumber,
3034 LibraryName => C4::Context->preference("LibraryName"),
3035 message_transport_type => 'email',
3037 ) or warn "can't enqueue letter $letter";
3042 =head3 FillWithDefaultValues
3044 FillWithDefaultValues( $marc_record );
3046 This will update the record with default value defined in the ACQ framework.
3047 For all existing fields, if a default value exists and there are no subfield, it will be created.
3048 If the field does not exist, it will be created too.
3052 sub FillWithDefaultValues {
3054 my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ', { unsafe => 1 } );
3057 C4::Biblio::GetMarcFromKohaField( 'items.itemnumber', '' );
3058 for my $tag ( sort keys %$tagslib ) {
3060 next if $tag == $itemfield;
3061 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
3062 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
3063 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
3064 if ( defined $defaultvalue and $defaultvalue ne '' ) {
3065 my @fields = $record->field($tag);
3067 for my $field (@fields) {
3068 unless ( defined $field->subfield($subfield) ) {
3069 $field->add_subfields(
3070 $subfield => $defaultvalue );
3075 $record->insert_fields_ordered(
3077 $tag, '', '', $subfield => $defaultvalue
3092 Koha Development Team <http://koha-community.org/>