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::Baskets;
32 use Koha::Acquisition::Booksellers;
33 use Koha::Acquisition::Orders;
37 use Koha::Number::Price;
39 use Koha::CsvProfiles;
49 use vars qw(@ISA @EXPORT);
55 &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
56 &GetBasketAsCSV &GetBasketGroupAsCSV
57 &GetBasketsByBookseller &GetBasketsByBasketgroup
58 &GetBasketsInfosByBookseller
60 &GetBasketUsers &ModBasketUsers
65 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
66 &GetBasketgroups &ReOpenBasketgroup
68 &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
69 &GetLateOrders &GetOrderFromItemnumber
70 &SearchOrders &GetHistory &GetRecentAcqui
71 &ModReceiveOrder &CancelReceipt
73 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
89 &GetBiblioCountByBasketno
95 &FillWithDefaultValues
105 sub GetOrderFromItemnumber {
106 my ($itemnumber) = @_;
107 my $dbh = C4::Context->dbh;
110 SELECT * from aqorders LEFT JOIN aqorders_items
111 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
112 WHERE itemnumber = ? |;
114 my $sth = $dbh->prepare($query);
118 $sth->execute($itemnumber);
120 my $order = $sth->fetchrow_hashref;
127 C4::Acquisition - Koha functions for dealing with orders and acquisitions
135 The functions in this module deal with acquisitions, managing book
136 orders, basket and parcels.
140 =head2 FUNCTIONS ABOUT BASKETS
144 $aqbasket = &GetBasket($basketnumber);
146 get all basket informations in aqbasket for a given basket
148 B<returns:> informations for a given basket returned as a hashref.
154 my $dbh = C4::Context->dbh;
157 concat( b.firstname,' ',b.surname) AS authorisedbyname
159 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
162 my $sth=$dbh->prepare($query);
163 $sth->execute($basketno);
164 my $basket = $sth->fetchrow_hashref;
168 #------------------------------------------------------------#
172 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
173 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace, $is_standing, $create_items );
175 Create a new basket in aqbasket table
179 =item C<$booksellerid> is a foreign key in the aqbasket table
181 =item C<$authorizedby> is the username of who created the basket
185 The other parameters are optional, see ModBasketHeader for more info on them.
190 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
191 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
192 $billingplace, $is_standing, $create_items ) = @_;
193 my $dbh = C4::Context->dbh;
195 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
196 . 'VALUES (now(),?,?)';
197 $dbh->do( $query, {}, $booksellerid, $authorisedby );
199 my $basket = $dbh->{mysql_insertid};
200 $basketname ||= q{}; # default to empty strings
202 $basketbooksellernote ||= q{};
203 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
204 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items );
208 #------------------------------------------------------------#
212 &CloseBasket($basketno);
214 close a basket (becomes unmodifiable, except for receives)
220 my $dbh = C4::Context->dbh;
221 $dbh->do('UPDATE aqbasket SET closedate=now() WHERE basketno=?', {}, $basketno );
224 q{UPDATE aqorders SET orderstatus = 'ordered' WHERE basketno = ? AND orderstatus NOT IN ( 'complete', 'cancelled')},
232 &ReopenBasket($basketno);
240 my $dbh = C4::Context->dbh;
241 $dbh->do( q{UPDATE aqbasket SET closedate=NULL WHERE basketno=?}, {}, $basketno );
245 SET orderstatus = 'new'
247 AND orderstatus NOT IN ( 'complete', 'cancelled' )
252 #------------------------------------------------------------#
254 =head3 GetBasketAsCSV
256 &GetBasketAsCSV($basketno);
258 Export a basket as CSV
260 $cgi parameter is needed for column name translation
265 my ($basketno, $cgi, $csv_profile_id) = @_;
266 my $basket = GetBasket($basketno);
267 my @orders = GetOrders($basketno);
268 my $contract = GetContract({
269 contractnumber => $basket->{'contractnumber'}
272 my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
274 if ($csv_profile_id) {
275 my $csv_profile = Koha::CsvProfiles->find( $csv_profile_id );
276 Koha::Exceptions::ObjectNotFound->throw( 'There is no valid csv profile given') unless $csv_profile;
278 my $csv = Text::CSV_XS->new({'quote_char'=>'"','escape_char'=>'"','sep_char'=>$csv_profile->csv_separator,'binary'=>1});
279 my $csv_profile_content = $csv_profile->content;
280 my ( @headers, @fields );
281 while ( $csv_profile_content =~ /
284 ([^\|]*) # fieldname (table.row or row)
288 my $field = ($2 eq '') ? $1 : $2;
290 $header =~ s/^\s+|\s+$//g; # Trim whitespaces
291 push @headers, $header;
293 $field =~ s/[^\.]*\.{1}//; # Remove the table name if exists.
294 $field =~ s/^\s+|\s+$//g; # Trim whitespaces
295 push @fields, $field;
297 for my $order (@orders) {
299 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
300 my $biblioitem = $biblio->biblioitem;
301 $order = { %$order, %{ $biblioitem->unblessed } };
303 $order = {%$order, %$contract};
305 $order = {%$order, %$basket, %{ $biblio->unblessed }};
306 for my $field (@fields) {
307 push @row, $order->{$field};
311 my $content = join( $csv_profile->csv_separator, @headers ) . "\n";
312 for my $row ( @rows ) {
313 $csv->combine(@$row);
314 my $string = $csv->string;
315 $content .= $string . "\n";
320 foreach my $order (@orders) {
321 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
322 my $biblioitem = $biblio->biblioitem;
324 contractname => $contract->{'contractname'},
325 ordernumber => $order->{'ordernumber'},
326 entrydate => $order->{'entrydate'},
327 isbn => $order->{'isbn'},
328 author => $biblio->author,
329 title => $biblio->title,
330 publicationyear => $biblioitem->publicationyear,
331 publishercode => $biblioitem->publishercode,
332 collectiontitle => $biblioitem->collectiontitle,
333 notes => $order->{'order_vendornote'},
334 quantity => $order->{'quantity'},
335 rrp => $order->{'rrp'},
337 for my $place ( qw( deliveryplace billingplace ) ) {
338 if ( my $library = Koha::Libraries->find( $row->{deliveryplace} ) ) {
339 $row->{$place} = $library->branchname
343 contractname author title publishercode collectiontitle notes
344 deliveryplace billingplace
346 # Double the quotes to not be interpreted as a field end
347 $row->{$_} =~ s/"/""/g if $row->{$_};
353 if(defined $a->{publishercode} and defined $b->{publishercode}) {
354 $a->{publishercode} cmp $b->{publishercode};
358 $template->param(rows => \@rows);
360 return $template->output;
365 =head3 GetBasketGroupAsCSV
367 &GetBasketGroupAsCSV($basketgroupid);
369 Export a basket group as CSV
371 $cgi parameter is needed for column name translation
375 sub GetBasketGroupAsCSV {
376 my ($basketgroupid, $cgi) = @_;
377 my $baskets = GetBasketsByBasketgroup($basketgroupid);
379 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
382 for my $basket (@$baskets) {
383 my @orders = GetOrders( $basket->{basketno} );
384 my $contract = GetContract({
385 contractnumber => $basket->{contractnumber}
387 my $bookseller = Koha::Acquisition::Booksellers->find( $basket->{booksellerid} );
388 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
390 foreach my $order (@orders) {
391 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
392 my $biblioitem = $biblio->biblioitem;
394 clientnumber => $bookseller->accountnumber,
395 basketname => $basket->{basketname},
396 ordernumber => $order->{ordernumber},
397 author => $biblio->author,
398 title => $biblio->title,
399 publishercode => $biblioitem->publishercode,
400 publicationyear => $biblioitem->publicationyear,
401 collectiontitle => $biblioitem->collectiontitle,
402 isbn => $order->{isbn},
403 quantity => $order->{quantity},
404 rrp_tax_included => $order->{rrp_tax_included},
405 rrp_tax_excluded => $order->{rrp_tax_excluded},
406 discount => $bookseller->discount,
407 ecost_tax_included => $order->{ecost_tax_included},
408 ecost_tax_excluded => $order->{ecost_tax_excluded},
409 notes => $order->{order_vendornote},
410 entrydate => $order->{entrydate},
411 booksellername => $bookseller->name,
412 bookselleraddress => $bookseller->address1,
413 booksellerpostal => $bookseller->postal,
414 contractnumber => $contract->{contractnumber},
415 contractname => $contract->{contractname},
418 basketgroupdeliveryplace => $basketgroup->{deliveryplace},
419 basketgroupbillingplace => $basketgroup->{billingplace},
420 basketdeliveryplace => $basket->{deliveryplace},
421 basketbillingplace => $basket->{billingplace},
423 for my $place (qw( basketgroupdeliveryplace basketgroupbillingplace basketdeliveryplace basketbillingplace )) {
424 if ( my $library = Koha::Libraries->find( $temp->{$place} ) ) {
425 $row->{$place} = $library->branchname;
429 basketname author title publishercode collectiontitle notes
430 booksellername bookselleraddress booksellerpostal contractname
431 basketgroupdeliveryplace basketgroupbillingplace
432 basketdeliveryplace basketbillingplace
434 # Double the quotes to not be interpreted as a field end
435 $row->{$_} =~ s/"/""/g if $row->{$_};
440 $template->param(rows => \@rows);
442 return $template->output;
446 =head3 CloseBasketgroup
448 &CloseBasketgroup($basketgroupno);
454 sub CloseBasketgroup {
455 my ($basketgroupno) = @_;
456 my $dbh = C4::Context->dbh;
457 my $sth = $dbh->prepare("
458 UPDATE aqbasketgroups
462 $sth->execute($basketgroupno);
465 #------------------------------------------------------------#
467 =head3 ReOpenBaskergroup($basketgroupno)
469 &ReOpenBaskergroup($basketgroupno);
475 sub ReOpenBasketgroup {
476 my ($basketgroupno) = @_;
477 my $dbh = C4::Context->dbh;
478 my $sth = $dbh->prepare("
479 UPDATE aqbasketgroups
483 $sth->execute($basketgroupno);
486 #------------------------------------------------------------#
491 &DelBasket($basketno);
493 Deletes the basket that has basketno field $basketno in the aqbasket table.
497 =item C<$basketno> is the primary key of the basket in the aqbasket table.
504 my ( $basketno ) = @_;
505 my $query = "DELETE FROM aqbasket WHERE basketno=?";
506 my $dbh = C4::Context->dbh;
507 my $sth = $dbh->prepare($query);
508 $sth->execute($basketno);
512 #------------------------------------------------------------#
516 &ModBasket($basketinfo);
518 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
522 =item C<$basketno> is the primary key of the basket in the aqbasket table.
529 my $basketinfo = shift;
530 my $query = "UPDATE aqbasket SET ";
532 foreach my $key (keys %$basketinfo){
533 if ($key ne 'basketno'){
534 $query .= "$key=?, ";
535 push(@params, $basketinfo->{$key} || undef );
538 # get rid of the "," at the end of $query
539 if (substr($query, length($query)-2) eq ', '){
544 $query .= "WHERE basketno=?";
545 push(@params, $basketinfo->{'basketno'});
546 my $dbh = C4::Context->dbh;
547 my $sth = $dbh->prepare($query);
548 $sth->execute(@params);
553 #------------------------------------------------------------#
555 =head3 ModBasketHeader
557 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
559 Modifies a basket's header.
563 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
565 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
567 =item C<$note> is the "note" field in the "aqbasket" table;
569 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
571 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
573 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
575 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
577 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
579 =item C<$is_standing> is the "is_standing" field in the aqbasket table.
581 =item C<$create_items> should be set to 'ordering', 'receiving' or 'cataloguing' (or undef, in which
582 case the AcqCreateItem syspref takes precedence).
588 sub ModBasketHeader {
589 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items) = @_;
594 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?, is_standing=?, create_items=?
598 my $dbh = C4::Context->dbh;
599 my $sth = $dbh->prepare($query);
600 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items || undef, $basketno);
602 if ( $contractnumber ) {
603 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
604 my $sth2 = $dbh->prepare($query2);
605 $sth2->execute($contractnumber,$basketno);
610 #------------------------------------------------------------#
612 =head3 GetBasketsByBookseller
614 @results = &GetBasketsByBookseller($booksellerid, $extra);
616 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
620 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
622 =item C<$extra> is the extra sql parameters, can be
624 $extra->{groupby}: group baskets by column
625 ex. $extra->{groupby} = aqbasket.basketgroupid
626 $extra->{orderby}: order baskets by column
627 $extra->{limit}: limit number of results (can be helpful for pagination)
633 sub GetBasketsByBookseller {
634 my ($booksellerid, $extra) = @_;
635 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
637 if ($extra->{groupby}) {
638 $query .= " GROUP by $extra->{groupby}";
640 if ($extra->{orderby}){
641 $query .= " ORDER by $extra->{orderby}";
643 if ($extra->{limit}){
644 $query .= " LIMIT $extra->{limit}";
647 my $dbh = C4::Context->dbh;
648 my $sth = $dbh->prepare($query);
649 $sth->execute($booksellerid);
650 return $sth->fetchall_arrayref({});
653 =head3 GetBasketsInfosByBookseller
655 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
657 The optional second parameter allbaskets is a boolean allowing you to
658 select all baskets from the supplier; by default only active baskets (open or
659 closed but still something to receive) are returned.
661 Returns in a arrayref of hashref all about booksellers baskets, plus:
662 total_biblios: Number of distinct biblios in basket
663 total_items: Number of items in basket
664 expected_items: Number of non-received items in basket
668 sub GetBasketsInfosByBookseller {
669 my ($supplierid, $allbaskets) = @_;
671 return unless $supplierid;
673 my $dbh = C4::Context->dbh;
675 SELECT aqbasket.basketno, aqbasket.basketname, aqbasket.note, aqbasket.booksellernote, aqbasket.contractnumber, aqbasket.creationdate, aqbasket.closedate, aqbasket.booksellerid, aqbasket.authorisedby, aqbasket.booksellerinvoicenumber, aqbasket.basketgroupid, aqbasket.deliveryplace, aqbasket.billingplace, aqbasket.branch, aqbasket.is_standing, aqbasket.create_items,
676 SUM(aqorders.quantity) AS total_items,
678 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
679 ) AS total_items_cancelled,
680 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
682 IF(aqorders.datereceived IS NULL
683 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
688 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
689 WHERE booksellerid = ?};
691 $query.=" GROUP BY aqbasket.basketno, aqbasket.basketname, aqbasket.note, aqbasket.booksellernote, aqbasket.contractnumber, aqbasket.creationdate, aqbasket.closedate, aqbasket.booksellerid, aqbasket.authorisedby, aqbasket.booksellerinvoicenumber, aqbasket.basketgroupid, aqbasket.deliveryplace, aqbasket.billingplace, aqbasket.branch, aqbasket.is_standing, aqbasket.create_items";
693 unless ( $allbaskets ) {
694 # Don't show the basket if it's NOT CLOSED or is FULLY RECEIVED
695 $query.=" HAVING (closedate IS NULL OR (
697 IF(aqorders.datereceived IS NULL
698 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
704 my $sth = $dbh->prepare($query);
705 $sth->execute($supplierid);
706 my $baskets = $sth->fetchall_arrayref({});
708 # Retrieve the number of biblios cancelled
709 my $cancelled_biblios = $dbh->selectall_hashref( q|
710 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
712 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
713 WHERE booksellerid = ?
714 AND aqorders.orderstatus = 'cancelled'
715 GROUP BY aqbasket.basketno
716 |, 'basketno', {}, $supplierid );
718 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
724 =head3 GetBasketUsers
726 $basketusers_ids = &GetBasketUsers($basketno);
728 Returns a list of all borrowernumbers that are in basket users list
733 my $basketno = shift;
735 return unless $basketno;
738 SELECT borrowernumber
742 my $dbh = C4::Context->dbh;
743 my $sth = $dbh->prepare($query);
744 $sth->execute($basketno);
745 my $results = $sth->fetchall_arrayref( {} );
748 foreach (@$results) {
749 push @borrowernumbers, $_->{'borrowernumber'};
752 return @borrowernumbers;
755 =head3 ModBasketUsers
757 my @basketusers_ids = (1, 2, 3);
758 &ModBasketUsers($basketno, @basketusers_ids);
760 Delete all users from basket users list, and add users in C<@basketusers_ids>
766 my ($basketno, @basketusers_ids) = @_;
768 return unless $basketno;
770 my $dbh = C4::Context->dbh;
772 DELETE FROM aqbasketusers
775 my $sth = $dbh->prepare($query);
776 $sth->execute($basketno);
779 INSERT INTO aqbasketusers (basketno, borrowernumber)
782 $sth = $dbh->prepare($query);
783 foreach my $basketuser_id (@basketusers_ids) {
784 $sth->execute($basketno, $basketuser_id);
789 =head3 CanUserManageBasket
791 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
792 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
794 Check if a borrower can manage a basket, according to system preference
795 AcqViewBaskets, user permissions and basket properties (creator, users list,
798 First parameter can be either a borrowernumber or a hashref as returned by
799 Koha::Patron->unblessed
801 Second parameter can be either a basketno or a hashref as returned by
802 C4::Acquisition::GetBasket.
804 The third parameter is optional. If given, it should be a hashref as returned
805 by C4::Auth::getuserflags. If not, getuserflags is called.
807 If user is authorised to manage basket, returns 1.
812 sub CanUserManageBasket {
813 my ($borrower, $basket, $userflags) = @_;
815 if (!ref $borrower) {
816 # FIXME This needs to be replaced
817 # We should not accept both scalar and array
818 # Tests need to be updated
819 $borrower = Koha::Patrons->find( $borrower )->unblessed;
822 $basket = GetBasket($basket);
825 return 0 unless ($basket and $borrower);
827 my $borrowernumber = $borrower->{borrowernumber};
828 my $basketno = $basket->{basketno};
830 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
832 if (!defined $userflags) {
833 my $dbh = C4::Context->dbh;
834 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
835 $sth->execute($borrowernumber);
836 my ($flags) = $sth->fetchrow_array;
839 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
842 unless ($userflags->{superlibrarian}
843 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
844 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
846 if (not exists $userflags->{acquisition}) {
850 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
851 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
855 if ($AcqViewBaskets eq 'user'
856 && $basket->{authorisedby} != $borrowernumber
857 && ! grep { $borrowernumber eq $_ } GetBasketUsers($basketno)) {
861 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
862 && $basket->{branch} ne $borrower->{branchcode}) {
870 #------------------------------------------------------------#
872 =head3 GetBasketsByBasketgroup
874 $baskets = &GetBasketsByBasketgroup($basketgroupid);
876 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
880 sub GetBasketsByBasketgroup {
881 my $basketgroupid = shift;
883 SELECT *, aqbasket.booksellerid as booksellerid
885 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
887 my $dbh = C4::Context->dbh;
888 my $sth = $dbh->prepare($query);
889 $sth->execute($basketgroupid);
890 return $sth->fetchall_arrayref({});
893 #------------------------------------------------------------#
895 =head3 NewBasketgroup
897 $basketgroupid = NewBasketgroup(\%hashref);
899 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
901 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
903 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
905 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
907 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
909 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
911 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
913 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
915 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
920 my $basketgroupinfo = shift;
921 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
922 my $query = "INSERT INTO aqbasketgroups (";
924 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
925 if ( defined $basketgroupinfo->{$field} ) {
926 $query .= "$field, ";
927 push(@params, $basketgroupinfo->{$field});
930 $query .= "booksellerid) VALUES (";
935 push(@params, $basketgroupinfo->{'booksellerid'});
936 my $dbh = C4::Context->dbh;
937 my $sth = $dbh->prepare($query);
938 $sth->execute(@params);
939 my $basketgroupid = $dbh->{'mysql_insertid'};
940 if( $basketgroupinfo->{'basketlist'} ) {
941 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
942 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
943 my $sth2 = $dbh->prepare($query2);
944 $sth2->execute($basketgroupid, $basketno);
947 return $basketgroupid;
950 #------------------------------------------------------------#
952 =head3 ModBasketgroup
954 ModBasketgroup(\%hashref);
956 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
958 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
960 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
962 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
964 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
966 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
968 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
970 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
972 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
977 my $basketgroupinfo = shift;
978 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
979 my $dbh = C4::Context->dbh;
980 my $query = "UPDATE aqbasketgroups SET ";
982 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
983 if ( defined $basketgroupinfo->{$field} ) {
984 $query .= "$field=?, ";
985 push(@params, $basketgroupinfo->{$field});
990 $query .= " WHERE id=?";
991 push(@params, $basketgroupinfo->{'id'});
992 my $sth = $dbh->prepare($query);
993 $sth->execute(@params);
995 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
996 $sth->execute($basketgroupinfo->{'id'});
998 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
999 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
1000 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
1001 $sth->execute($basketgroupinfo->{'id'}, $basketno);
1007 #------------------------------------------------------------#
1009 =head3 DelBasketgroup
1011 DelBasketgroup($basketgroupid);
1013 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
1017 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
1023 sub DelBasketgroup {
1024 my $basketgroupid = shift;
1025 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1026 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
1027 my $dbh = C4::Context->dbh;
1028 my $sth = $dbh->prepare($query);
1029 $sth->execute($basketgroupid);
1033 #------------------------------------------------------------#
1036 =head2 FUNCTIONS ABOUT ORDERS
1038 =head3 GetBasketgroup
1040 $basketgroup = &GetBasketgroup($basketgroupid);
1042 Returns a reference to the hash containing all information about the basketgroup.
1046 sub GetBasketgroup {
1047 my $basketgroupid = shift;
1048 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1049 my $dbh = C4::Context->dbh;
1050 my $result_set = $dbh->selectall_arrayref(
1051 'SELECT * FROM aqbasketgroups WHERE id=?',
1055 return $result_set->[0]; # id is unique
1058 #------------------------------------------------------------#
1060 =head3 GetBasketgroups
1062 $basketgroups = &GetBasketgroups($booksellerid);
1064 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1068 sub GetBasketgroups {
1069 my $booksellerid = shift;
1070 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1071 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1072 my $dbh = C4::Context->dbh;
1073 my $sth = $dbh->prepare($query);
1074 $sth->execute($booksellerid);
1075 return $sth->fetchall_arrayref({});
1078 #------------------------------------------------------------#
1080 =head2 FUNCTIONS ABOUT ORDERS
1084 @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } );
1086 Looks up the pending (non-cancelled) orders with the given basket
1089 If cancelled is set, only cancelled orders will be returned.
1094 my ( $basketno, $params ) = @_;
1096 return () unless $basketno;
1098 my $orderby = $params->{orderby};
1099 my $cancelled = $params->{cancelled} || 0;
1101 my $dbh = C4::Context->dbh;
1103 SELECT biblio.*,biblioitems.*,
1107 $query .= $cancelled
1109 aqorders_transfers.ordernumber_to AS transferred_to,
1110 aqorders_transfers.timestamp AS transferred_to_timestamp
1113 aqorders_transfers.ordernumber_from AS transferred_from,
1114 aqorders_transfers.timestamp AS transferred_from_timestamp
1118 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1119 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1120 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1122 $query .= $cancelled
1124 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1127 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1135 $orderby ||= q|biblioitems.publishercode, biblio.title|;
1137 AND (datecancellationprinted IS NOT NULL
1138 AND datecancellationprinted <> '0000-00-00')
1143 q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|;
1145 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1149 $query .= " ORDER BY $orderby";
1151 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1156 #------------------------------------------------------------#
1158 =head3 GetOrdersByBiblionumber
1160 @orders = &GetOrdersByBiblionumber($biblionumber);
1162 Looks up the orders with linked to a specific $biblionumber, including
1163 cancelled orders and received orders.
1166 C<@orders> is an array of references-to-hash, whose keys are the
1167 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1171 sub GetOrdersByBiblionumber {
1172 my $biblionumber = shift;
1173 return unless $biblionumber;
1174 my $dbh = C4::Context->dbh;
1176 SELECT biblio.*,biblioitems.*,
1180 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1181 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1182 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1183 WHERE aqorders.biblionumber=?
1186 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1187 return @{$result_set};
1191 #------------------------------------------------------------#
1195 $order = &GetOrder($ordernumber);
1197 Looks up an order by order number.
1199 Returns a reference-to-hash describing the order. The keys of
1200 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1205 my ($ordernumber) = @_;
1206 return unless $ordernumber;
1208 my $dbh = C4::Context->dbh;
1209 my $query = qq{SELECT
1213 aqbasket.basketname,
1214 borrowers.branchcode,
1215 biblioitems.publicationyear,
1216 biblio.copyrightdate,
1217 biblioitems.editionstatement,
1221 biblioitems.publishercode,
1222 aqorders.rrp AS unitpricesupplier,
1223 aqorders.ecost AS unitpricelib,
1224 aqorders.claims_count AS claims_count,
1225 aqorders.claimed_date AS claimed_date,
1226 aqbudgets.budget_name AS budget,
1227 aqbooksellers.name AS supplier,
1228 aqbooksellers.id AS supplierid,
1229 biblioitems.publishercode AS publisher,
1230 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1231 DATE(aqbasket.closedate) AS orderdate,
1232 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1233 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1234 DATEDIFF(CURDATE( ),closedate) AS latesince
1235 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1236 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1237 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1238 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1239 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1240 WHERE aqorders.basketno = aqbasket.basketno
1243 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1245 # result_set assumed to contain 1 match
1246 return $result_set->[0];
1249 =head3 GetLastOrderNotReceivedFromSubscriptionid
1251 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1253 Returns a reference-to-hash describing the last order not received for a subscription.
1257 sub GetLastOrderNotReceivedFromSubscriptionid {
1258 my ( $subscriptionid ) = @_;
1259 my $dbh = C4::Context->dbh;
1261 SELECT * FROM aqorders
1262 LEFT JOIN subscription
1263 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1264 WHERE aqorders.subscriptionid = ?
1265 AND aqorders.datereceived IS NULL
1269 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1271 # result_set assumed to contain 1 match
1272 return $result_set->[0];
1275 =head3 GetLastOrderReceivedFromSubscriptionid
1277 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1279 Returns a reference-to-hash describing the last order received for a subscription.
1283 sub GetLastOrderReceivedFromSubscriptionid {
1284 my ( $subscriptionid ) = @_;
1285 my $dbh = C4::Context->dbh;
1287 SELECT * FROM aqorders
1288 LEFT JOIN subscription
1289 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1290 WHERE aqorders.subscriptionid = ?
1291 AND aqorders.datereceived =
1293 SELECT MAX( aqorders.datereceived )
1295 LEFT JOIN subscription
1296 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1297 WHERE aqorders.subscriptionid = ?
1298 AND aqorders.datereceived IS NOT NULL
1300 ORDER BY ordernumber DESC
1304 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1306 # result_set assumed to contain 1 match
1307 return $result_set->[0];
1311 #------------------------------------------------------------#
1315 &ModOrder(\%hashref);
1317 Modifies an existing order. Updates the order with order number
1318 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1319 other keys of the hash update the fields with the same name in the aqorders
1320 table of the Koha database.
1325 my $orderinfo = shift;
1327 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '';
1329 my $dbh = C4::Context->dbh;
1332 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1333 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1335 # delete($orderinfo->{'branchcode'});
1336 # the hash contains a lot of entries not in aqorders, so get the columns ...
1337 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1339 my $colnames = $sth->{NAME};
1340 #FIXME Be careful. If aqorders would have columns with diacritics,
1341 #you should need to decode what you get back from NAME.
1342 #See report 10110 and guided_reports.pl
1343 my $query = "UPDATE aqorders SET ";
1345 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1346 # ... and skip hash entries that are not in the aqorders table
1347 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1348 next unless grep(/^$orderinfokey$/, @$colnames);
1349 $query .= "$orderinfokey=?, ";
1350 push(@params, $orderinfo->{$orderinfokey});
1353 $query .= "timestamp=NOW() WHERE ordernumber=?";
1354 push(@params, $orderinfo->{'ordernumber'} );
1355 $sth = $dbh->prepare($query);
1356 $sth->execute(@params);
1360 #------------------------------------------------------------#
1364 ModItemOrder($itemnumber, $ordernumber);
1366 Modifies the ordernumber of an item in aqorders_items.
1371 my ($itemnumber, $ordernumber) = @_;
1373 return unless ($itemnumber and $ordernumber);
1375 my $dbh = C4::Context->dbh;
1377 UPDATE aqorders_items
1379 WHERE itemnumber = ?
1381 my $sth = $dbh->prepare($query);
1382 return $sth->execute($ordernumber, $itemnumber);
1385 #------------------------------------------------------------#
1387 =head3 ModReceiveOrder
1389 my ( $date_received, $new_ordernumber ) = ModReceiveOrder(
1391 biblionumber => $biblionumber,
1393 quantityreceived => $quantityreceived,
1395 invoice => $invoice,
1396 budget_id => $budget_id,
1397 received_itemnumbers => \@received_itemnumbers,
1398 order_internalnote => $order_internalnote,
1402 Updates an order, to reflect the fact that it was received, at least
1405 If a partial order is received, splits the order into two.
1407 Updates the order with biblionumber C<$biblionumber> and ordernumber
1408 C<$order->{ordernumber}>.
1413 sub ModReceiveOrder {
1415 my $biblionumber = $params->{biblionumber};
1416 my $order = { %{ $params->{order} } }; # Copy the order, we don't want to modify it
1417 my $invoice = $params->{invoice};
1418 my $quantrec = $params->{quantityreceived};
1419 my $user = $params->{user};
1420 my $budget_id = $params->{budget_id};
1421 my $received_items = $params->{received_items};
1423 my $dbh = C4::Context->dbh;
1424 my $datereceived = ( $invoice and $invoice->{datereceived} ) ? $invoice->{datereceived} : dt_from_string;
1425 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1426 if ($suggestionid) {
1427 ModSuggestion( {suggestionid=>$suggestionid,
1428 STATUS=>'AVAILABLE',
1429 biblionumber=> $biblionumber}
1433 my $result_set = $dbh->selectrow_arrayref(
1434 q{SELECT aqbasket.is_standing
1436 WHERE basketno=?},{ Slice => {} }, $order->{basketno});
1437 my $is_standing = $result_set->[0]; # we assume we have a unique basket
1439 my $new_ordernumber = $order->{ordernumber};
1440 if ( $is_standing || $order->{quantity} > $quantrec ) {
1441 # Split order line in two parts: the first is the original order line
1442 # without received items (the quantity is decreased),
1443 # the second part is a new order line with quantity=quantityrec
1444 # (entirely received)
1448 orderstatus = 'partial'|;
1449 $query .= q| WHERE ordernumber = ?|;
1450 my $sth = $dbh->prepare($query);
1453 ( $is_standing ? 1 : ($order->{quantity} - $quantrec) ),
1454 $order->{ordernumber}
1457 if ( not $order->{subscriptionid} && defined $order->{order_internalnote} ) {
1458 $dbh->do(q|UPDATE aqorders
1459 SET order_internalnote = ?|, {}, $order->{order_internalnote});
1462 # Recalculate tax_value
1466 tax_value_on_ordering = quantity * | . _get_rounding_sql(q|ecost_tax_excluded|) . q| * tax_rate_on_ordering,
1467 tax_value_on_receiving = quantity * | . _get_rounding_sql(q|unitprice_tax_excluded|) . q| * tax_rate_on_receiving
1468 WHERE ordernumber = ?
1469 |, undef, $order->{ordernumber});
1471 delete $order->{ordernumber};
1472 $order->{budget_id} = ( $budget_id || $order->{budget_id} );
1473 $order->{quantity} = $quantrec;
1474 $order->{quantityreceived} = $quantrec;
1475 $order->{ecost_tax_excluded} //= 0;
1476 $order->{tax_rate_on_ordering} //= 0;
1477 $order->{unitprice_tax_excluded} //= 0;
1478 $order->{tax_rate_on_receiving} //= 0;
1479 $order->{tax_value_on_ordering} = $order->{quantity} * get_rounded_price($order->{ecost_tax_excluded}) * $order->{tax_rate_on_ordering};
1480 $order->{tax_value_on_receiving} = $order->{quantity} * get_rounded_price($order->{unitprice_tax_excluded}) * $order->{tax_rate_on_receiving};
1481 $order->{datereceived} = $datereceived;
1482 $order->{invoiceid} = $invoice->{invoiceid};
1483 $order->{orderstatus} = 'complete';
1484 $new_ordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber; # TODO What if the store fails?
1486 if ($received_items) {
1487 foreach my $itemnumber (@$received_items) {
1488 ModItemOrder($itemnumber, $new_ordernumber);
1494 SET quantityreceived = ?,
1498 orderstatus = 'complete'
1502 , replacementprice = ?
1503 | if defined $order->{replacementprice};
1506 , unitprice = ?, unitprice_tax_included = ?, unitprice_tax_excluded = ?
1507 | if defined $order->{unitprice};
1510 ,tax_value_on_receiving = ?
1511 | if defined $order->{tax_value_on_receiving};
1514 ,tax_rate_on_receiving = ?
1515 | if defined $order->{tax_rate_on_receiving};
1518 , order_internalnote = ?
1519 | if defined $order->{order_internalnote};
1521 $query .= q| where biblionumber=? and ordernumber=?|;
1523 my $sth = $dbh->prepare( $query );
1524 my @params = ( $quantrec, $datereceived, $invoice->{invoiceid}, ( $budget_id ? $budget_id : $order->{budget_id} ) );
1526 if ( defined $order->{replacementprice} ) {
1527 push @params, $order->{replacementprice};
1530 if ( defined $order->{unitprice} ) {
1531 push @params, $order->{unitprice}, $order->{unitprice_tax_included}, $order->{unitprice_tax_excluded};
1534 if ( defined $order->{tax_value_on_receiving} ) {
1535 push @params, $order->{tax_value_on_receiving};
1538 if ( defined $order->{tax_rate_on_receiving} ) {
1539 push @params, $order->{tax_rate_on_receiving};
1542 if ( defined $order->{order_internalnote} ) {
1543 push @params, $order->{order_internalnote};
1546 push @params, ( $biblionumber, $order->{ordernumber} );
1548 $sth->execute( @params );
1550 # All items have been received, sent a notification to users
1551 NotifyOrderUsers( $order->{ordernumber} );
1554 return ($datereceived, $new_ordernumber);
1557 =head3 CancelReceipt
1559 my $parent_ordernumber = CancelReceipt($ordernumber);
1561 Cancel an order line receipt and update the parent order line, as if no
1563 If items are created at receipt (AcqCreateItem = receiving) then delete
1569 my $ordernumber = shift;
1571 return unless $ordernumber;
1573 my $dbh = C4::Context->dbh;
1575 SELECT datereceived, parent_ordernumber, quantity
1577 WHERE ordernumber = ?
1579 my $sth = $dbh->prepare($query);
1580 $sth->execute($ordernumber);
1581 my $order = $sth->fetchrow_hashref;
1583 warn "CancelReceipt: order $ordernumber does not exist";
1586 unless($order->{'datereceived'}) {
1587 warn "CancelReceipt: order $ordernumber is not received";
1591 my $parent_ordernumber = $order->{'parent_ordernumber'};
1593 my $order_obj = Koha::Acquisition::Orders->find( $ordernumber ); # FIXME rewrite all this subroutine using this object
1594 my @itemnumbers = $order_obj->items->get_column('itemnumber');
1596 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1597 # The order line has no parent, just mark it as not received
1600 SET quantityreceived = ?,
1603 orderstatus = 'ordered'
1604 WHERE ordernumber = ?
1606 $sth = $dbh->prepare($query);
1607 $sth->execute(0, undef, undef, $ordernumber);
1608 _cancel_items_receipt( $order_obj );
1610 # The order line has a parent, increase parent quantity and delete
1613 SELECT quantity, datereceived
1615 WHERE ordernumber = ?
1617 $sth = $dbh->prepare($query);
1618 $sth->execute($parent_ordernumber);
1619 my $parent_order = $sth->fetchrow_hashref;
1620 unless($parent_order) {
1621 warn "Parent order $parent_ordernumber does not exist.";
1624 if($parent_order->{'datereceived'}) {
1625 warn "CancelReceipt: parent order is received.".
1626 " Can't cancel receipt.";
1632 orderstatus = 'ordered'
1633 WHERE ordernumber = ?
1635 $sth = $dbh->prepare($query);
1636 my $rv = $sth->execute(
1637 $order->{'quantity'} + $parent_order->{'quantity'},
1641 warn "Cannot update parent order line, so do not cancel".
1646 # Recalculate tax_value
1650 tax_value_on_ordering = quantity * | . _get_rounding_sql(q|ecost_tax_excluded|) . q| * tax_rate_on_ordering,
1651 tax_value_on_receiving = quantity * | . _get_rounding_sql(q|unitprice_tax_excluded|) . q| * tax_rate_on_receiving
1652 WHERE ordernumber = ?
1653 |, undef, $parent_ordernumber);
1655 _cancel_items_receipt( $order_obj, $parent_ordernumber );
1658 DELETE FROM aqorders
1659 WHERE ordernumber = ?
1661 $sth = $dbh->prepare($query);
1662 $sth->execute($ordernumber);
1666 if( $order_obj->basket->effective_create_items eq 'ordering' ) {
1667 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1669 for my $in ( @itemnumbers ) {
1670 my $item = Koha::Items->find( $in ); # FIXME We do not need that, we already have Koha::Items from $order_obj->items
1671 my $biblio = $item->biblio;
1672 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber', $biblio->frameworkcode );
1673 my $item_marc = C4::Items::GetMarcItem( $biblio->biblionumber, $in );
1674 for my $affect ( @affects ) {
1675 my ( $sf, $v ) = split q{=}, $affect, 2;
1676 foreach ( $item_marc->field($itemfield) ) {
1677 $_->update( $sf => $v );
1680 C4::Items::ModItemFromMarc( $item_marc, $biblio->biblionumber, $in );
1685 return $parent_ordernumber;
1688 sub _cancel_items_receipt {
1689 my ( $order, $parent_ordernumber ) = @_;
1690 $parent_ordernumber ||= $order->ordernumber;
1692 my $items = $order->items;
1693 if ( $order->basket->effective_create_items eq 'receiving' ) {
1694 # Remove items that were created at receipt
1696 DELETE FROM items, aqorders_items
1697 USING items, aqorders_items
1698 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1700 my $dbh = C4::Context->dbh;
1701 my $sth = $dbh->prepare($query);
1702 while ( my $item = $items->next ) {
1703 $sth->execute($item->itemnumber, $item->itemnumber);
1707 while ( my $item = $items->next ) {
1708 ModItemOrder($item->itemnumber, $parent_ordernumber);
1713 #------------------------------------------------------------#
1717 @results = &SearchOrders({
1718 ordernumber => $ordernumber,
1721 booksellerid => $booksellerid,
1722 basketno => $basketno,
1723 basketname => $basketname,
1724 basketgroupname => $basketgroupname,
1728 biblionumber => $biblionumber,
1729 budget_id => $budget_id
1732 Searches for orders filtered by criteria.
1734 C<$ordernumber> Finds matching orders or transferred orders by ordernumber.
1735 C<$search> Finds orders matching %$search% in title, author, or isbn.
1736 C<$owner> Finds order for the logged in user.
1737 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1738 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1741 C<@results> is an array of references-to-hash with the keys are fields
1742 from aqorders, biblio, biblioitems and aqbasket tables.
1747 my ( $params ) = @_;
1748 my $ordernumber = $params->{ordernumber};
1749 my $search = $params->{search};
1750 my $ean = $params->{ean};
1751 my $booksellerid = $params->{booksellerid};
1752 my $basketno = $params->{basketno};
1753 my $basketname = $params->{basketname};
1754 my $basketgroupname = $params->{basketgroupname};
1755 my $owner = $params->{owner};
1756 my $pending = $params->{pending};
1757 my $ordered = $params->{ordered};
1758 my $biblionumber = $params->{biblionumber};
1759 my $budget_id = $params->{budget_id};
1761 my $dbh = C4::Context->dbh;
1764 SELECT aqbasket.basketno,
1766 borrowers.firstname,
1769 biblioitems.biblioitemnumber,
1770 biblioitems.publishercode,
1771 biblioitems.publicationyear,
1772 aqbasket.authorisedby,
1773 aqbasket.booksellerid,
1775 aqbasket.creationdate,
1776 aqbasket.basketname,
1777 aqbasketgroups.id as basketgroupid,
1778 aqbasketgroups.name as basketgroupname,
1781 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1782 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1783 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1784 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1785 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1788 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1790 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1794 WHERE (datecancellationprinted is NULL)
1797 if ( $pending or $ordered ) {
1800 ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) )
1802 ( quantity > quantityreceived OR quantityreceived is NULL )
1806 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1814 my $userenv = C4::Context->userenv;
1815 if ( C4::Context->preference("IndependentBranches") ) {
1816 unless ( C4::Context->IsSuperLibrarian() ) {
1819 borrowers.branchcode = ?
1820 OR borrowers.branchcode = ''
1823 push @args, $userenv->{branch};
1827 if ( $ordernumber ) {
1828 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1829 push @args, ( $ordernumber, $ordernumber );
1831 if ( $biblionumber ) {
1832 $query .= 'AND aqorders.biblionumber = ?';
1833 push @args, $biblionumber;
1836 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1837 push @args, ("%$search%","%$search%","%$search%");
1840 $query .= ' AND biblioitems.ean = ?';
1843 if ( $booksellerid ) {
1844 $query .= 'AND aqbasket.booksellerid = ?';
1845 push @args, $booksellerid;
1848 $query .= 'AND aqbasket.basketno = ?';
1849 push @args, $basketno;
1852 $query .= 'AND aqbasket.basketname LIKE ?';
1853 push @args, "%$basketname%";
1855 if( $basketgroupname ) {
1856 $query .= ' AND aqbasketgroups.name LIKE ?';
1857 push @args, "%$basketgroupname%";
1861 $query .= ' AND aqbasket.authorisedby=? ';
1862 push @args, $userenv->{'number'};
1866 $query .= ' AND aqorders.budget_id = ?';
1867 push @args, $budget_id;
1870 $query .= ' ORDER BY aqbasket.basketno';
1872 my $sth = $dbh->prepare($query);
1873 $sth->execute(@args);
1874 return $sth->fetchall_arrayref({});
1877 #------------------------------------------------------------#
1881 &DelOrder($biblionumber, $ordernumber);
1883 Cancel the order with the given order and biblio numbers. It does not
1884 delete any entries in the aqorders table, it merely marks them as
1890 my ( $bibnum, $ordernumber, $delete_biblio, $reason ) = @_;
1893 my $dbh = C4::Context->dbh;
1896 SET datecancellationprinted=now(), orderstatus='cancelled'
1899 $query .= ", cancellationreason = ? ";
1902 WHERE biblionumber=? AND ordernumber=?
1904 my $sth = $dbh->prepare($query);
1906 $sth->execute($reason, $bibnum, $ordernumber);
1908 $sth->execute( $bibnum, $ordernumber );
1912 my $order = Koha::Acquisition::Orders->find($ordernumber);
1913 my $items = $order->items;
1914 while ( my $item = $items->next ) { # Should be moved to Koha::Acquisition::Order->delete
1915 my $delcheck = C4::Items::DelItemCheck( $bibnum, $item->itemnumber );
1917 if($delcheck != 1) {
1918 $error->{'delitem'} = 1;
1922 if($delete_biblio) {
1923 # We get the number of remaining items
1924 my $biblio = Koha::Biblios->find( $bibnum );
1925 my $itemcount = $biblio->items->count;
1927 # If there are no items left,
1928 if ( $itemcount == 0 ) {
1929 # We delete the record
1930 my $delcheck = DelBiblio($bibnum);
1933 $error->{'delbiblio'} = 1;
1941 =head3 TransferOrder
1943 my $newordernumber = TransferOrder($ordernumber, $basketno);
1945 Transfer an order line to a basket.
1946 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1947 to BOOKSELLER on DATE' and create new order with internal note
1948 'Transferred from BOOKSELLER on DATE'.
1949 Move all attached items to the new order.
1950 Received orders cannot be transferred.
1951 Return the ordernumber of created order.
1956 my ($ordernumber, $basketno) = @_;
1958 return unless ($ordernumber and $basketno);
1960 my $order = Koha::Acquisition::Orders->find( $ordernumber ) or return;
1961 return if $order->datereceived;
1963 $order = $order->unblessed;
1965 my $basket = GetBasket($basketno);
1966 return unless $basket;
1968 my $dbh = C4::Context->dbh;
1969 my ($query, $sth, $rv);
1973 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1974 WHERE ordernumber = ?
1976 $sth = $dbh->prepare($query);
1977 $rv = $sth->execute('cancelled', $ordernumber);
1979 delete $order->{'ordernumber'};
1980 delete $order->{parent_ordernumber};
1981 $order->{'basketno'} = $basketno;
1983 my $newordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber;
1986 UPDATE aqorders_items
1988 WHERE ordernumber = ?
1990 $sth = $dbh->prepare($query);
1991 $sth->execute($newordernumber, $ordernumber);
1994 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1997 $sth = $dbh->prepare($query);
1998 $sth->execute($ordernumber, $newordernumber);
2000 return $newordernumber;
2003 =head3 _get_rounding_sql
2005 $rounding_sql = _get_rounding_sql("mysql_variable_to_round_string");
2007 returns the correct SQL routine based on OrderPriceRounding system preference.
2011 sub _get_rounding_sql {
2012 my ( $round_string ) = @_;
2013 my $rounding_pref = C4::Context->preference('OrderPriceRounding');
2014 if ( $rounding_pref eq "nearest_cent" ) { return ("CAST($round_string*100 AS INTEGER)/100"); }
2015 else { return ("$round_string"); }
2018 =head3 get_rounded_price
2020 $rounded_price = get_rounded_price( $price );
2022 returns a price rounded as specified in OrderPriceRounding system preference.
2026 sub get_rounded_price {
2028 my $rounding_pref = C4::Context->preference('OrderPriceRounding');
2029 if( $rounding_pref eq 'nearest_cent' ) { return Koha::Number::Price->new( $price )->format(); }
2030 else { return $price; }
2034 =head2 FUNCTIONS ABOUT PARCELS
2038 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
2040 get a lists of parcels.
2047 is the bookseller this function has to get parcels.
2050 To know on what criteria the results list has to be ordered.
2053 is the booksellerinvoicenumber.
2055 =item $datefrom & $dateto
2056 to know on what date this function has to filter its search.
2061 a pointer on a hash list containing parcel informations as such :
2067 =item Last operation
2069 =item Number of biblio
2071 =item Number of items
2078 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
2079 my $dbh = C4::Context->dbh;
2080 my @query_params = ();
2082 SELECT aqinvoices.invoicenumber,
2083 datereceived,purchaseordernumber,
2084 count(DISTINCT biblionumber) AS biblio,
2085 sum(quantity) AS itemsexpected,
2086 sum(quantityreceived) AS itemsreceived
2087 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
2088 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2089 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
2091 push @query_params, $bookseller;
2093 if ( defined $code ) {
2094 $strsth .= ' and aqinvoices.invoicenumber like ? ';
2095 # add a % to the end of the code to allow stemming.
2096 push @query_params, "$code%";
2099 if ( defined $datefrom ) {
2100 $strsth .= ' and datereceived >= ? ';
2101 push @query_params, $datefrom;
2104 if ( defined $dateto ) {
2105 $strsth .= 'and datereceived <= ? ';
2106 push @query_params, $dateto;
2109 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
2111 # can't use a placeholder to place this column name.
2112 # but, we could probably be checking to make sure it is a column that will be fetched.
2113 $strsth .= "order by $order " if ($order);
2115 my $sth = $dbh->prepare($strsth);
2117 $sth->execute( @query_params );
2118 my $results = $sth->fetchall_arrayref({});
2122 #------------------------------------------------------------#
2124 =head3 GetLateOrders
2126 @results = &GetLateOrders;
2128 Searches for bookseller with late orders.
2131 the table of supplier with late issues. This table is full of hashref.
2137 my $supplierid = shift;
2139 my $estimateddeliverydatefrom = shift;
2140 my $estimateddeliverydateto = shift;
2142 my $dbh = C4::Context->dbh;
2144 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2145 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2147 my @query_params = ();
2149 SELECT aqbasket.basketno,
2150 aqorders.ordernumber,
2151 DATE(aqbasket.closedate) AS orderdate,
2152 aqbasket.basketname AS basketname,
2153 aqbasket.basketgroupid AS basketgroupid,
2154 aqbasketgroups.name AS basketgroupname,
2155 aqorders.rrp AS unitpricesupplier,
2156 aqorders.ecost AS unitpricelib,
2157 aqorders.claims_count AS claims_count,
2158 aqorders.claimed_date AS claimed_date,
2159 aqbudgets.budget_name AS budget,
2160 borrowers.branchcode AS branch,
2161 aqbooksellers.name AS supplier,
2162 aqbooksellers.id AS supplierid,
2163 biblio.author, biblio.title,
2164 biblioitems.publishercode AS publisher,
2165 biblioitems.publicationyear,
2166 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2170 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2171 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2172 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2173 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2174 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2175 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2176 WHERE aqorders.basketno = aqbasket.basketno
2177 AND ( datereceived = ''
2178 OR datereceived IS NULL
2179 OR aqorders.quantityreceived < aqorders.quantity
2181 AND aqbasket.closedate IS NOT NULL
2182 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2184 if ($dbdriver eq "mysql") {
2186 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2187 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2188 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2190 if ( defined $delay ) {
2191 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2192 push @query_params, $delay;
2194 $from .= " AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0";
2196 # FIXME: account for IFNULL as above
2198 aqorders.quantity AS quantity,
2199 aqorders.quantity * aqorders.rrp AS subtotal,
2200 (CAST(now() AS date) - closedate) AS latesince
2202 if ( defined $delay ) {
2203 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2204 push @query_params, $delay;
2206 $from .= " AND aqorders.quantity <> 0";
2208 if (defined $supplierid) {
2209 $from .= ' AND aqbasket.booksellerid = ? ';
2210 push @query_params, $supplierid;
2212 if (defined $branch) {
2213 $from .= ' AND borrowers.branchcode LIKE ? ';
2214 push @query_params, $branch;
2217 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2218 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2220 if ( defined $estimateddeliverydatefrom ) {
2221 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2222 push @query_params, $estimateddeliverydatefrom;
2224 if ( defined $estimateddeliverydateto ) {
2225 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2226 push @query_params, $estimateddeliverydateto;
2228 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2229 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2231 if (C4::Context->preference("IndependentBranches")
2232 && !C4::Context->IsSuperLibrarian() ) {
2233 $from .= ' AND borrowers.branchcode LIKE ? ';
2234 push @query_params, C4::Context->userenv->{branch};
2236 $from .= " AND orderstatus <> 'cancelled' ";
2237 my $query = "$select $from \nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2238 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2239 my $sth = $dbh->prepare($query);
2240 $sth->execute(@query_params);
2242 while (my $data = $sth->fetchrow_hashref) {
2243 push @results, $data;
2248 #------------------------------------------------------------#
2252 \@order_loop = GetHistory( %params );
2254 Retreives some acquisition history information
2264 basket - search both basket name and number
2265 booksellerinvoicenumber
2268 orderstatus (note that orderstatus '' will retrieve orders
2269 of any status except cancelled)
2271 get_canceled_order (if set to a true value, cancelled orders will
2275 $order_loop is a list of hashrefs that each look like this:
2277 'author' => 'Twain, Mark',
2279 'biblionumber' => '215',
2281 'creationdate' => 'MM/DD/YYYY',
2282 'datereceived' => undef,
2285 'invoicenumber' => undef,
2287 'ordernumber' => '1',
2289 'quantityreceived' => undef,
2290 'title' => 'The Adventures of Huckleberry Finn'
2296 # don't run the query if there are no parameters (list would be too long for sure !)
2297 croak "No search params" unless @_;
2299 my $title = $params{title};
2300 my $author = $params{author};
2301 my $isbn = $params{isbn};
2302 my $ean = $params{ean};
2303 my $name = $params{name};
2304 my $from_placed_on = $params{from_placed_on};
2305 my $to_placed_on = $params{to_placed_on};
2306 my $basket = $params{basket};
2307 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2308 my $basketgroupname = $params{basketgroupname};
2309 my $budget = $params{budget};
2310 my $orderstatus = $params{orderstatus};
2311 my $biblionumber = $params{biblionumber};
2312 my $get_canceled_order = $params{get_canceled_order} || 0;
2313 my $ordernumber = $params{ordernumber};
2314 my $search_children_too = $params{search_children_too} || 0;
2315 my $created_by = $params{created_by} || [];
2316 my $ordernumbers = $params{ordernumbers} || [];
2317 my $additional_fields = $params{additional_fields} // [];
2321 my $total_qtyreceived = 0;
2322 my $total_price = 0;
2324 #get variation of isbn
2328 if ( C4::Context->preference("SearchWithISBNVariations") ){
2329 @isbns = C4::Koha::GetVariationsOfISBN( $isbn );
2330 foreach my $isb (@isbns){
2331 push @isbn_params, '?';
2336 push @isbn_params, '?';
2340 my $dbh = C4::Context->dbh;
2343 COALESCE(biblio.title, deletedbiblio.title) AS title,
2344 COALESCE(biblio.author, deletedbiblio.author) AS author,
2345 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2346 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2348 aqbasket.basketname,
2349 aqbasket.basketgroupid,
2350 aqbasket.authorisedby,
2351 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2352 aqbasketgroups.name as groupname,
2354 aqbasket.creationdate,
2355 aqorders.datereceived,
2357 aqorders.quantityreceived,
2359 aqorders.ordernumber,
2361 aqinvoices.invoicenumber,
2362 aqbooksellers.id as id,
2363 aqorders.biblionumber,
2364 aqorders.orderstatus,
2365 aqorders.parent_ordernumber,
2366 aqbudgets.budget_name
2368 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2371 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2372 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2373 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2374 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2375 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2376 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2377 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2378 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2379 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2380 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2383 $query .= " WHERE 1 ";
2385 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2386 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2389 my @query_params = ();
2391 if ( $biblionumber ) {
2392 $query .= " AND biblio.biblionumber = ?";
2393 push @query_params, $biblionumber;
2397 $query .= " AND biblio.title LIKE ? ";
2398 $title =~ s/\s+/%/g;
2399 push @query_params, "%$title%";
2403 $query .= " AND biblio.author LIKE ? ";
2404 push @query_params, "%$author%";
2408 $query .= " AND ( biblioitems.isbn LIKE " . join (" OR biblioitems.isbn LIKE ", @isbn_params ) . ")";
2409 foreach my $isb (@isbns){
2410 push @query_params, "%$isb%";
2415 $query .= " AND biblioitems.ean = ? ";
2416 push @query_params, "$ean";
2419 $query .= " AND aqbooksellers.name LIKE ? ";
2420 push @query_params, "%$name%";
2424 $query .= " AND aqbudgets.budget_id = ? ";
2425 push @query_params, "$budget";
2428 if ( $from_placed_on ) {
2429 $query .= " AND creationdate >= ? ";
2430 push @query_params, $from_placed_on;
2433 if ( $to_placed_on ) {
2434 $query .= " AND creationdate <= ? ";
2435 push @query_params, $to_placed_on;
2438 if ( defined $orderstatus and $orderstatus ne '') {
2439 $query .= " AND aqorders.orderstatus = ? ";
2440 push @query_params, "$orderstatus";
2444 if ($basket =~ m/^\d+$/) {
2445 $query .= " AND aqorders.basketno = ? ";
2446 push @query_params, $basket;
2448 $query .= " AND aqbasket.basketname LIKE ? ";
2449 push @query_params, "%$basket%";
2453 if ($booksellerinvoicenumber) {
2454 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2455 push @query_params, "%$booksellerinvoicenumber%";
2458 if ($basketgroupname) {
2459 $query .= " AND aqbasketgroups.name LIKE ? ";
2460 push @query_params, "%$basketgroupname%";
2464 $query .= " AND (aqorders.ordernumber = ? ";
2465 push @query_params, $ordernumber;
2466 if ($search_children_too) {
2467 $query .= " OR aqorders.parent_ordernumber = ? ";
2468 push @query_params, $ordernumber;
2473 if ( @$created_by ) {
2474 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2475 push @query_params, @$created_by;
2478 if ( @$ordernumbers ) {
2479 $query .= ' AND (aqorders.ordernumber IN ( ' . join (',', ('?') x @$ordernumbers ) . '))';
2480 push @query_params, @$ordernumbers;
2482 if ( @$additional_fields ) {
2483 my @baskets = Koha::Acquisition::Baskets->filter_by_additional_fields($additional_fields);
2485 return [] unless @baskets;
2487 # No parameterization because record IDs come directly from DB
2488 $query .= ' AND aqbasket.basketno IN ( ' . join( ',', map { $_->basketno } @baskets ) . ' )';
2491 if ( C4::Context->preference("IndependentBranches") ) {
2492 unless ( C4::Context->IsSuperLibrarian() ) {
2493 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2494 push @query_params, C4::Context->userenv->{branch};
2497 $query .= " ORDER BY id";
2499 return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2502 =head2 GetRecentAcqui
2504 $results = GetRecentAcqui($days);
2506 C<$results> is a ref to a table which contains hashref
2510 sub GetRecentAcqui {
2512 my $dbh = C4::Context->dbh;
2516 ORDER BY timestamp DESC
2519 my $sth = $dbh->prepare($query);
2521 my $results = $sth->fetchall_arrayref({});
2525 #------------------------------------------------------------#
2529 &AddClaim($ordernumber);
2531 Add a claim for an order
2536 my ($ordernumber) = @_;
2537 my $dbh = C4::Context->dbh;
2540 claims_count = claims_count + 1,
2541 claimed_date = CURDATE()
2542 WHERE ordernumber = ?
2544 my $sth = $dbh->prepare($query);
2545 $sth->execute($ordernumber);
2550 my @invoices = GetInvoices(
2551 invoicenumber => $invoicenumber,
2552 supplierid => $supplierid,
2553 suppliername => $suppliername,
2554 shipmentdatefrom => $shipmentdatefrom, # ISO format
2555 shipmentdateto => $shipmentdateto, # ISO format
2556 billingdatefrom => $billingdatefrom, # ISO format
2557 billingdateto => $billingdateto, # ISO format
2558 isbneanissn => $isbn_or_ean_or_issn,
2561 publisher => $publisher,
2562 publicationyear => $publicationyear,
2563 branchcode => $branchcode,
2564 order_by => $order_by
2567 Return a list of invoices that match all given criteria.
2569 $order_by is "column_name (asc|desc)", where column_name is any of
2570 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2571 'shipmentcost', 'shipmentcost_budgetid'.
2573 asc is the default if omitted
2580 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2581 closedate shipmentcost shipmentcost_budgetid);
2583 my $dbh = C4::Context->dbh;
2585 SELECT aqinvoices.invoiceid, aqinvoices.invoicenumber, aqinvoices.booksellerid, aqinvoices.shipmentdate, aqinvoices.billingdate, aqinvoices.closedate, aqinvoices.shipmentcost, aqinvoices.shipmentcost_budgetid, aqinvoices.message_id,
2586 aqbooksellers.name AS suppliername,
2589 aqorders.datereceived IS NOT NULL,
2590 aqorders.biblionumber,
2593 ) AS receivedbiblios,
2596 aqorders.subscriptionid IS NOT NULL,
2597 aqorders.subscriptionid,
2600 ) AS is_linked_to_subscriptions,
2601 SUM(aqorders.quantityreceived) AS receiveditems
2603 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2604 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2605 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2606 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2607 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2608 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2609 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2614 if($args{supplierid}) {
2615 push @bind_strs, " aqinvoices.booksellerid = ? ";
2616 push @bind_args, $args{supplierid};
2618 if($args{invoicenumber}) {
2619 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2620 push @bind_args, "%$args{invoicenumber}%";
2622 if($args{suppliername}) {
2623 push @bind_strs, " aqbooksellers.name LIKE ? ";
2624 push @bind_args, "%$args{suppliername}%";
2626 if($args{shipmentdatefrom}) {
2627 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2628 push @bind_args, $args{shipmentdatefrom};
2630 if($args{shipmentdateto}) {
2631 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2632 push @bind_args, $args{shipmentdateto};
2634 if($args{billingdatefrom}) {
2635 push @bind_strs, " aqinvoices.billingdate >= ? ";
2636 push @bind_args, $args{billingdatefrom};
2638 if($args{billingdateto}) {
2639 push @bind_strs, " aqinvoices.billingdate <= ? ";
2640 push @bind_args, $args{billingdateto};
2642 if($args{isbneanissn}) {
2643 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2644 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2647 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2648 push @bind_args, $args{title};
2651 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2652 push @bind_args, $args{author};
2654 if($args{publisher}) {
2655 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2656 push @bind_args, $args{publisher};
2658 if($args{publicationyear}) {
2659 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2660 push @bind_args, $args{publicationyear}, $args{publicationyear};
2662 if($args{branchcode}) {
2663 push @bind_strs, " borrowers.branchcode = ? ";
2664 push @bind_args, $args{branchcode};
2666 if($args{message_id}) {
2667 push @bind_strs, " aqinvoices.message_id = ? ";
2668 push @bind_args, $args{message_id};
2671 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2672 $query .= " GROUP BY aqinvoices.invoiceid, aqinvoices.invoicenumber, aqinvoices.booksellerid, aqinvoices.shipmentdate, aqinvoices.billingdate, aqinvoices.closedate, aqinvoices.shipmentcost, aqinvoices.shipmentcost_budgetid, aqinvoices.message_id, aqbooksellers.name";
2674 if($args{order_by}) {
2675 my ($column, $direction) = split / /, $args{order_by};
2676 if(grep /^$column$/, @columns) {
2677 $direction ||= 'ASC';
2678 $query .= " ORDER BY $column $direction";
2682 my $sth = $dbh->prepare($query);
2683 $sth->execute(@bind_args);
2685 my $results = $sth->fetchall_arrayref({});
2691 my $invoice = GetInvoice($invoiceid);
2693 Get informations about invoice with given $invoiceid
2695 Return a hash filled with aqinvoices.* fields
2700 my ($invoiceid) = @_;
2703 return unless $invoiceid;
2705 my $dbh = C4::Context->dbh;
2711 my $sth = $dbh->prepare($query);
2712 $sth->execute($invoiceid);
2714 $invoice = $sth->fetchrow_hashref;
2718 =head3 GetInvoiceDetails
2720 my $invoice = GetInvoiceDetails($invoiceid)
2722 Return informations about an invoice + the list of related order lines
2724 Orders informations are in $invoice->{orders} (array ref)
2728 sub GetInvoiceDetails {
2729 my ($invoiceid) = @_;
2731 if ( !defined $invoiceid ) {
2732 carp 'GetInvoiceDetails called without an invoiceid';
2736 my $dbh = C4::Context->dbh;
2738 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2740 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2743 my $sth = $dbh->prepare($query);
2744 $sth->execute($invoiceid);
2746 my $invoice = $sth->fetchrow_hashref;
2751 biblio.copyrightdate,
2753 biblioitems.publishercode,
2754 biblioitems.publicationyear,
2755 aqbasket.basketname,
2756 aqbasketgroups.id AS basketgroupid,
2757 aqbasketgroups.name AS basketgroupname
2759 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2760 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2761 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2762 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2765 $sth = $dbh->prepare($query);
2766 $sth->execute($invoiceid);
2767 $invoice->{orders} = $sth->fetchall_arrayref({});
2768 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2775 my $invoiceid = AddInvoice(
2776 invoicenumber => $invoicenumber,
2777 booksellerid => $booksellerid,
2778 shipmentdate => $shipmentdate,
2779 billingdate => $billingdate,
2780 closedate => $closedate,
2781 shipmentcost => $shipmentcost,
2782 shipmentcost_budgetid => $shipmentcost_budgetid
2785 Create a new invoice and return its id or undef if it fails.
2792 return unless(%invoice and $invoice{invoicenumber});
2794 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2795 closedate shipmentcost shipmentcost_budgetid message_id);
2799 foreach my $key (keys %invoice) {
2800 if(0 < grep(/^$key$/, @columns)) {
2801 push @set_strs, "$key = ?";
2802 push @set_args, ($invoice{$key} || undef);
2808 my $dbh = C4::Context->dbh;
2809 my $query = "INSERT INTO aqinvoices SET ";
2810 $query .= join (",", @set_strs);
2811 my $sth = $dbh->prepare($query);
2812 $rv = $sth->execute(@set_args);
2814 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2823 invoiceid => $invoiceid, # Mandatory
2824 invoicenumber => $invoicenumber,
2825 booksellerid => $booksellerid,
2826 shipmentdate => $shipmentdate,
2827 billingdate => $billingdate,
2828 closedate => $closedate,
2829 shipmentcost => $shipmentcost,
2830 shipmentcost_budgetid => $shipmentcost_budgetid
2833 Modify an invoice, invoiceid is mandatory.
2835 Return undef if it fails.
2842 return unless(%invoice and $invoice{invoiceid});
2844 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2845 closedate shipmentcost shipmentcost_budgetid);
2849 foreach my $key (keys %invoice) {
2850 if(0 < grep(/^$key$/, @columns)) {
2851 push @set_strs, "$key = ?";
2852 push @set_args, ($invoice{$key} || undef);
2856 my $dbh = C4::Context->dbh;
2857 my $query = "UPDATE aqinvoices SET ";
2858 $query .= join(",", @set_strs);
2859 $query .= " WHERE invoiceid = ?";
2861 my $sth = $dbh->prepare($query);
2862 $sth->execute(@set_args, $invoice{invoiceid});
2867 CloseInvoice($invoiceid);
2871 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2876 my ($invoiceid) = @_;
2878 return unless $invoiceid;
2880 my $dbh = C4::Context->dbh;
2883 SET closedate = CAST(NOW() AS DATE)
2886 my $sth = $dbh->prepare($query);
2887 $sth->execute($invoiceid);
2890 =head3 ReopenInvoice
2892 ReopenInvoice($invoiceid);
2896 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => output_pref({ dt=>dt_from_string, dateonly=>1, otputpref=>'iso' }))
2901 my ($invoiceid) = @_;
2903 return unless $invoiceid;
2905 my $dbh = C4::Context->dbh;
2908 SET closedate = NULL
2911 my $sth = $dbh->prepare($query);
2912 $sth->execute($invoiceid);
2917 DelInvoice($invoiceid);
2919 Delete an invoice if there are no items attached to it.
2924 my ($invoiceid) = @_;
2926 return unless $invoiceid;
2928 my $dbh = C4::Context->dbh;
2934 my $sth = $dbh->prepare($query);
2935 $sth->execute($invoiceid);
2936 my $res = $sth->fetchrow_arrayref;
2937 if ( $res && $res->[0] == 0 ) {
2939 DELETE FROM aqinvoices
2942 my $sth = $dbh->prepare($query);
2943 return ( $sth->execute($invoiceid) > 0 );
2948 =head3 MergeInvoices
2950 MergeInvoices($invoiceid, \@sourceids);
2952 Merge the invoices identified by the IDs in \@sourceids into
2953 the invoice identified by $invoiceid.
2958 my ($invoiceid, $sourceids) = @_;
2960 return unless $invoiceid;
2961 foreach my $sourceid (@$sourceids) {
2962 next if $sourceid == $invoiceid;
2963 my $source = GetInvoiceDetails($sourceid);
2964 foreach my $order (@{$source->{'orders'}}) {
2965 $order->{'invoiceid'} = $invoiceid;
2968 DelInvoice($source->{'invoiceid'});
2973 =head3 GetBiblioCountByBasketno
2975 $biblio_count = &GetBiblioCountByBasketno($basketno);
2977 Looks up the biblio's count that has basketno value $basketno
2983 sub GetBiblioCountByBasketno {
2984 my ($basketno) = @_;
2985 my $dbh = C4::Context->dbh;
2987 SELECT COUNT( DISTINCT( biblionumber ) )
2990 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2993 my $sth = $dbh->prepare($query);
2994 $sth->execute($basketno);
2995 return $sth->fetchrow;
2998 # Note this subroutine should be moved to Koha::Acquisition::Order
2999 # Will do when a DBIC decision will be taken.
3000 sub populate_order_with_prices {
3003 my $order = $params->{order};
3004 my $booksellerid = $params->{booksellerid};
3005 return unless $booksellerid;
3007 my $bookseller = Koha::Acquisition::Booksellers->find( $booksellerid );
3009 my $receiving = $params->{receiving};
3010 my $ordering = $params->{ordering};
3011 my $discount = $order->{discount};
3012 $discount /= 100 if $discount > 1;
3015 $order->{tax_rate_on_ordering} //= $order->{tax_rate};
3016 if ( $bookseller->listincgst ) {
3017 # The user entered the rrp tax included
3018 $order->{rrp_tax_included} = $order->{rrp};
3020 # rrp tax excluded = rrp tax included / ( 1 + tax rate )
3021 $order->{rrp_tax_excluded} = $order->{rrp_tax_included} / ( 1 + $order->{tax_rate_on_ordering} );
3023 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
3024 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
3026 # ecost tax included = rrp tax included ( 1 - discount )
3027 $order->{ecost_tax_included} = $order->{rrp_tax_included} * ( 1 - $discount );
3030 # The user entered the rrp tax excluded
3031 $order->{rrp_tax_excluded} = $order->{rrp};
3033 # rrp tax included = rrp tax excluded * ( 1 - tax rate )
3034 $order->{rrp_tax_included} = $order->{rrp_tax_excluded} * ( 1 + $order->{tax_rate_on_ordering} );
3036 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
3037 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
3039 # ecost tax included = rrp tax excluded * ( 1 + tax rate ) * ( 1 - discount )
3040 $order->{ecost_tax_included} =
3041 $order->{rrp_tax_excluded} *
3042 ( 1 + $order->{tax_rate_on_ordering} ) *
3046 # tax value = quantity * ecost tax excluded * tax rate
3047 $order->{tax_value_on_ordering} =
3048 $order->{quantity} * get_rounded_price($order->{ecost_tax_excluded}) * $order->{tax_rate_on_ordering};
3052 $order->{tax_rate_on_receiving} //= $order->{tax_rate};
3053 if ( $bookseller->invoiceincgst ) {
3054 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
3055 # we need to keep the exact ecost value
3056 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_included} )->round ) {
3057 $order->{unitprice} = $order->{ecost_tax_included};
3060 # The user entered the unit price tax included
3061 $order->{unitprice_tax_included} = $order->{unitprice};
3063 # unit price tax excluded = unit price tax included / ( 1 + tax rate )
3064 $order->{unitprice_tax_excluded} = $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate_on_receiving} );
3067 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
3068 # we need to keep the exact ecost value
3069 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_excluded} )->round ) {
3070 $order->{unitprice} = $order->{ecost_tax_excluded};
3073 # The user entered the unit price tax excluded
3074 $order->{unitprice_tax_excluded} = $order->{unitprice};
3077 # unit price tax included = unit price tax included * ( 1 + tax rate )
3078 $order->{unitprice_tax_included} = $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate_on_receiving} );
3081 # tax value = quantity * unit price tax excluded * tax rate
3082 $order->{tax_value_on_receiving} = $order->{quantity} * get_rounded_price($order->{unitprice_tax_excluded}) * $order->{tax_rate_on_receiving};
3088 =head3 GetOrderUsers
3090 $order_users_ids = &GetOrderUsers($ordernumber);
3092 Returns a list of all borrowernumbers that are in order users list
3097 my ($ordernumber) = @_;
3099 return unless $ordernumber;
3102 SELECT borrowernumber
3104 WHERE ordernumber = ?
3106 my $dbh = C4::Context->dbh;
3107 my $sth = $dbh->prepare($query);
3108 $sth->execute($ordernumber);
3109 my $results = $sth->fetchall_arrayref( {} );
3111 my @borrowernumbers;
3112 foreach (@$results) {
3113 push @borrowernumbers, $_->{'borrowernumber'};
3116 return @borrowernumbers;
3119 =head3 ModOrderUsers
3121 my @order_users_ids = (1, 2, 3);
3122 &ModOrderUsers($ordernumber, @basketusers_ids);
3124 Delete all users from order users list, and add users in C<@order_users_ids>
3130 my ( $ordernumber, @order_users_ids ) = @_;
3132 return unless $ordernumber;
3134 my $dbh = C4::Context->dbh;
3136 DELETE FROM aqorder_users
3137 WHERE ordernumber = ?
3139 my $sth = $dbh->prepare($query);
3140 $sth->execute($ordernumber);
3143 INSERT INTO aqorder_users (ordernumber, borrowernumber)
3146 $sth = $dbh->prepare($query);
3147 foreach my $order_user_id (@order_users_ids) {
3148 $sth->execute( $ordernumber, $order_user_id );
3152 sub NotifyOrderUsers {
3153 my ($ordernumber) = @_;
3155 my @borrowernumbers = GetOrderUsers($ordernumber);
3156 return unless @borrowernumbers;
3158 my $order = GetOrder( $ordernumber );
3159 for my $borrowernumber (@borrowernumbers) {
3160 my $patron = Koha::Patrons->find( $borrowernumber );
3161 my $library = $patron->library->unblessed;
3162 my $biblio = Koha::Biblios->find( $order->{biblionumber} )->unblessed;
3163 my $letter = C4::Letters::GetPreparedLetter(
3164 module => 'acquisition',
3165 letter_code => 'ACQ_NOTIF_ON_RECEIV',
3166 branchcode => $library->{branchcode},
3167 lang => $patron->lang,
3169 'branches' => $library,
3170 'borrowers' => $patron->unblessed,
3171 'biblio' => $biblio,
3172 'aqorders' => $order,
3176 C4::Letters::EnqueueLetter(
3179 borrowernumber => $borrowernumber,
3180 LibraryName => C4::Context->preference("LibraryName"),
3181 message_transport_type => 'email',
3183 ) or warn "can't enqueue letter $letter";
3188 =head3 FillWithDefaultValues
3190 FillWithDefaultValues( $marc_record );
3192 This will update the record with default value defined in the ACQ framework.
3193 For all existing fields, if a default value exists and there are no subfield, it will be created.
3194 If the field does not exist, it will be created too.
3198 sub FillWithDefaultValues {
3200 my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ', { unsafe => 1 } );
3203 C4::Biblio::GetMarcFromKohaField( 'items.itemnumber', '' );
3204 for my $tag ( sort keys %$tagslib ) {
3206 next if $tag == $itemfield;
3207 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
3208 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
3209 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
3210 if ( defined $defaultvalue and $defaultvalue ne '' ) {
3211 my @fields = $record->field($tag);
3213 for my $field (@fields) {
3214 unless ( defined $field->subfield($subfield) ) {
3215 $field->add_subfields(
3216 $subfield => $defaultvalue );
3221 $record->insert_fields_ordered(
3223 $tag, '', '', $subfield => $defaultvalue
3238 Koha Development Team <http://koha-community.org/>