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>.
22 use Carp qw( carp croak );
25 use C4::Suggestions qw( GetSuggestion GetSuggestionFromBiblionumber ModSuggestion );
26 use C4::Biblio qw( GetMarcFromKohaField GetMarcStructure IsMarcStructureInternal );
27 use C4::Contract qw( GetContract );
28 use C4::Log qw( logaction );
29 use C4::Templates qw(gettemplate);
30 use Koha::DateUtils qw( dt_from_string );
31 use Koha::Acquisition::Baskets;
32 use Koha::Acquisition::Booksellers;
33 use Koha::Acquisition::Invoices;
34 use Koha::Acquisition::Orders;
35 use Koha::AdditionalFieldValue;
39 use Koha::Number::Price;
41 use Koha::CsvProfiles;
47 use JSON qw( to_json );
50 our (@ISA, @EXPORT_OK);
55 GetBasket NewBasket ReopenBasket ModBasket
56 GetBasketAsCSV GetBasketGroupAsCSV
57 GetBasketsByBookseller GetBasketsByBasketgroup
58 GetBasketsInfosByBookseller
60 GetBasketUsers ModBasketUsers
65 ModBasketgroup NewBasketgroup DelBasketgroup GetBasketgroup CloseBasketgroup
66 GetBasketgroups ReOpenBasketgroup
68 ModOrder GetOrder GetOrders GetOrdersByBiblionumber
69 GetOrderFromItemnumber
70 SearchOrders GetHistory GetRecentAcqui
71 ModReceiveOrder CancelReceipt
86 GetBiblioCountByBasketno
103 sub GetOrderFromItemnumber {
104 my ($itemnumber) = @_;
105 my $dbh = C4::Context->dbh;
108 SELECT * from aqorders LEFT JOIN aqorders_items
109 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
110 WHERE itemnumber = ? |;
112 my $sth = $dbh->prepare($query);
116 $sth->execute($itemnumber);
118 my $order = $sth->fetchrow_hashref;
125 C4::Acquisition - Koha functions for dealing with orders and acquisitions
133 The functions in this module deal with acquisitions, managing book
134 orders, basket and parcels.
138 =head2 FUNCTIONS ABOUT BASKETS
142 $aqbasket = &GetBasket($basketnumber);
144 get all basket informations in aqbasket for a given basket
146 B<returns:> informations for a given basket returned as a hashref.
152 my $dbh = C4::Context->dbh;
155 concat( b.firstname,' ',b.surname) AS authorisedbyname
157 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
160 my $sth=$dbh->prepare($query);
161 $sth->execute($basketno);
162 my $basket = $sth->fetchrow_hashref;
166 #------------------------------------------------------------#
170 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
171 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace, $is_standing, $create_items );
173 Create a new basket in aqbasket table
177 =item C<$booksellerid> is a foreign key in the aqbasket table
179 =item C<$authorizedby> is the username of who created the basket
183 The other parameters are optional, see ModBasketHeader for more info on them.
188 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
189 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
190 $billingplace, $is_standing, $create_items ) = @_;
191 my $dbh = C4::Context->dbh;
193 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
194 . 'VALUES (now(),?,?)';
195 $dbh->do( $query, {}, $booksellerid, $authorisedby );
197 my $basket = $dbh->{mysql_insertid};
198 $basketname ||= q{}; # default to empty strings
200 $basketbooksellernote ||= q{};
202 # Log the basket creation
203 if (C4::Context->preference("AcquisitionLog")) {
204 my $created = Koha::Acquisition::Baskets->find( $basket );
209 to_json($created->unblessed)
213 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
214 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items );
221 &ReopenBasket($basketno);
229 my $dbh = C4::Context->dbh;
230 $dbh->do( q{UPDATE aqbasket SET closedate=NULL WHERE basketno=?}, {}, $basketno );
234 SET orderstatus = 'new'
236 AND orderstatus NOT IN ( 'complete', 'cancelled' )
239 # Log the basket reopening
240 if (C4::Context->preference("AcquisitionLog")) {
241 my $reopened = Koha::Acquisition::Baskets->find( $basketno );
246 to_json($reopened->unblessed)
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 $delimiter = $csv_profile->csv_separator;
279 $delimiter = "\t" if $delimiter eq "\\t";
280 my $csv = Text::CSV_XS->new({'quote_char'=>'"','escape_char'=>'"','sep_char'=>$delimiter,'binary'=>1});
281 my $csv_profile_content = $csv_profile->content;
282 my ( @headers, @fields );
283 while ( $csv_profile_content =~ /
286 ([^\|]*) # fieldname (table.row or row)
290 my $field = ($2 eq '') ? $1 : $2;
292 $header =~ s/^\s+|\s+$//g; # Trim whitespaces
293 push @headers, $header;
295 $field =~ s/[^\.]*\.{1}//; # Remove the table name if exists.
296 $field =~ s/^\s+|\s+$//g; # Trim whitespaces
297 push @fields, $field;
299 for my $order (@orders) {
301 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
302 my $biblioitem = $biblio->biblioitem;
303 $order = { %$order, %{ $biblioitem->unblessed } };
305 $order = {%$order, %$contract};
307 $order = {%$order, %$basket, %{ $biblio->unblessed }};
308 for my $field (@fields) {
309 push @row, $order->{$field};
313 my $content = join( $delimiter, @headers ) . "\n";
314 for my $row ( @rows ) {
315 $csv->combine(@$row);
316 my $string = $csv->string;
317 $content .= $string . "\n";
322 foreach my $order (@orders) {
323 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
326 $biblioitem = $biblio->biblioitem;
329 contractname => $contract->{'contractname'},
330 ordernumber => $order->{'ordernumber'},
331 entrydate => $order->{'entrydate'},
332 isbn => $order->{'isbn'},
333 author => $biblio ? $biblio->author : q{},
334 title => $biblio ? $biblio->title : q{},
335 publicationyear => $biblioitem ? $biblioitem->publicationyear : q{},
336 publishercode => $biblioitem ? $biblioitem->publishercode : q{},
337 collectiontitle => $biblioitem ? $biblioitem->collectiontitle : q{},
338 notes => $order->{'order_vendornote'},
339 quantity => $order->{'quantity'},
340 rrp => $order->{'rrp'},
342 for my $place ( qw( deliveryplace billingplace ) ) {
343 if ( my $library = Koha::Libraries->find( $row->{deliveryplace} ) ) {
344 $row->{$place} = $library->branchname
348 contractname author title publishercode collectiontitle notes
349 deliveryplace billingplace
351 # Double the quotes to not be interpreted as a field end
352 $row->{$_} =~ s/"/""/g if $row->{$_};
358 if(defined $a->{publishercode} and defined $b->{publishercode}) {
359 $a->{publishercode} cmp $b->{publishercode};
363 $template->param(rows => \@rows);
365 return $template->output;
370 =head3 GetBasketGroupAsCSV
372 &GetBasketGroupAsCSV($basketgroupid);
374 Export a basket group as CSV
376 $cgi parameter is needed for column name translation
380 sub GetBasketGroupAsCSV {
381 my ($basketgroupid, $cgi) = @_;
382 my $baskets = GetBasketsByBasketgroup($basketgroupid);
384 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
387 for my $basket (@$baskets) {
388 my @orders = GetOrders( $basket->{basketno} );
389 my $contract = GetContract({
390 contractnumber => $basket->{contractnumber}
392 my $bookseller = Koha::Acquisition::Booksellers->find( $basket->{booksellerid} );
393 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
395 foreach my $order (@orders) {
396 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
399 $biblioitem = $biblio->biblioitem;
402 clientnumber => $bookseller->accountnumber,
403 basketname => $basket->{basketname},
404 ordernumber => $order->{ordernumber},
405 author => $biblio ? $biblio->author : q{},
406 title => $biblio ? $biblio->title : q{},
407 publishercode => $biblioitem ? $biblioitem->publishercode : q{},
408 publicationyear => $biblioitem ? $biblioitem->publicationyear : q{},
409 collectiontitle => $biblioitem ? $biblioitem->collectiontitle : q{},
410 isbn => $order->{isbn},
411 quantity => $order->{quantity},
412 rrp_tax_included => $order->{rrp_tax_included},
413 rrp_tax_excluded => $order->{rrp_tax_excluded},
414 discount => $bookseller->discount,
415 ecost_tax_included => $order->{ecost_tax_included},
416 ecost_tax_excluded => $order->{ecost_tax_excluded},
417 notes => $order->{order_vendornote},
418 entrydate => $order->{entrydate},
419 booksellername => $bookseller->name,
420 bookselleraddress => $bookseller->address1,
421 booksellerpostal => $bookseller->postal,
422 contractnumber => $contract->{contractnumber},
423 contractname => $contract->{contractname},
426 basketgroupdeliveryplace => $basketgroup->{deliveryplace},
427 basketgroupbillingplace => $basketgroup->{billingplace},
428 basketdeliveryplace => $basket->{deliveryplace},
429 basketbillingplace => $basket->{billingplace},
431 for my $place (qw( basketgroupdeliveryplace basketgroupbillingplace basketdeliveryplace basketbillingplace )) {
432 if ( my $library = Koha::Libraries->find( $temp->{$place} ) ) {
433 $row->{$place} = $library->branchname;
437 basketname author title publishercode collectiontitle notes
438 booksellername bookselleraddress booksellerpostal contractname
439 basketgroupdeliveryplace basketgroupbillingplace
440 basketdeliveryplace basketbillingplace
442 # Double the quotes to not be interpreted as a field end
443 $row->{$_} =~ s/"/""/g if $row->{$_};
448 $template->param(rows => \@rows);
450 return $template->output;
454 =head3 CloseBasketgroup
456 &CloseBasketgroup($basketgroupno);
462 sub CloseBasketgroup {
463 my ($basketgroupno) = @_;
464 my $dbh = C4::Context->dbh;
465 my $sth = $dbh->prepare("
466 UPDATE aqbasketgroups
470 $sth->execute($basketgroupno);
473 #------------------------------------------------------------#
475 =head3 ReOpenBaskergroup($basketgroupno)
477 &ReOpenBaskergroup($basketgroupno);
483 sub ReOpenBasketgroup {
484 my ($basketgroupno) = @_;
485 my $dbh = C4::Context->dbh;
486 my $sth = $dbh->prepare("
487 UPDATE aqbasketgroups
491 $sth->execute($basketgroupno);
494 #------------------------------------------------------------#
498 &ModBasket($basketinfo);
500 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
504 =item C<$basketno> is the primary key of the basket in the aqbasket table.
511 my $basketinfo = shift;
512 my $query = "UPDATE aqbasket SET ";
514 foreach my $key (keys %$basketinfo){
515 if ($key ne 'basketno'){
516 $query .= "$key=?, ";
517 push(@params, $basketinfo->{$key} || undef );
520 # get rid of the "," at the end of $query
521 if (substr($query, length($query)-2) eq ', '){
526 $query .= "WHERE basketno=?";
527 push(@params, $basketinfo->{'basketno'});
528 my $dbh = C4::Context->dbh;
529 my $sth = $dbh->prepare($query);
530 $sth->execute(@params);
532 # Log the basket update
533 if (C4::Context->preference("AcquisitionLog")) {
534 my $modified = Koha::Acquisition::Baskets->find(
535 $basketinfo->{basketno}
540 $basketinfo->{basketno},
541 to_json($modified->unblessed)
548 #------------------------------------------------------------#
550 =head3 ModBasketHeader
552 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
554 Modifies a basket's header.
558 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
560 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
562 =item C<$note> is the "note" field in the "aqbasket" table;
564 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
566 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
568 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
570 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
572 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
574 =item C<$is_standing> is the "is_standing" field in the aqbasket table.
576 =item C<$create_items> should be set to 'ordering', 'receiving' or 'cataloguing' (or undef, in which
577 case the AcqCreateItem syspref takes precedence).
583 sub ModBasketHeader {
584 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items) = @_;
589 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?, is_standing=?, create_items=?
593 my $dbh = C4::Context->dbh;
594 my $sth = $dbh->prepare($query);
595 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items || undef, $basketno);
597 if ( $contractnumber ) {
598 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
599 my $sth2 = $dbh->prepare($query2);
600 $sth2->execute($contractnumber,$basketno);
603 # Log the basket update
604 if (C4::Context->preference("AcquisitionLog")) {
605 my $modified = Koha::Acquisition::Baskets->find(
610 'MODIFY_BASKET_HEADER',
612 to_json($modified->unblessed)
619 #------------------------------------------------------------#
621 =head3 GetBasketsByBookseller
623 @results = &GetBasketsByBookseller($booksellerid, $extra);
625 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
629 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
631 =item C<$extra> is the extra sql parameters, can be
633 $extra->{groupby}: group baskets by column
634 ex. $extra->{groupby} = aqbasket.basketgroupid
635 $extra->{orderby}: order baskets by column
636 $extra->{limit}: limit number of results (can be helpful for pagination)
642 sub GetBasketsByBookseller {
643 my ($booksellerid, $extra) = @_;
644 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
646 if ($extra->{groupby}) {
647 $query .= " GROUP by $extra->{groupby}";
649 if ($extra->{orderby}){
650 $query .= " ORDER by $extra->{orderby}";
652 if ($extra->{limit}){
653 $query .= " LIMIT $extra->{limit}";
656 my $dbh = C4::Context->dbh;
657 my $sth = $dbh->prepare($query);
658 $sth->execute($booksellerid);
659 return $sth->fetchall_arrayref({});
662 =head3 GetBasketsInfosByBookseller
664 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
666 The optional second parameter allbaskets is a boolean allowing you to
667 select all baskets from the supplier; by default only active baskets (open or
668 closed but still something to receive) are returned.
670 Returns in a arrayref of hashref all about booksellers baskets, plus:
671 total_biblios: Number of distinct biblios in basket
672 total_items: Number of items in basket
673 expected_items: Number of non-received items in basket
677 sub GetBasketsInfosByBookseller {
678 my ($supplierid, $allbaskets) = @_;
680 return unless $supplierid;
682 my $dbh = C4::Context->dbh;
684 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,
685 SUM(aqorders.quantity) AS total_items,
687 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
688 ) AS total_items_cancelled,
689 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
691 IF(aqorders.datereceived IS NULL
692 AND aqorders.datecancellationprinted IS NULL
696 SUM( aqorders.uncertainprice ) AS uncertainprices
698 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
699 WHERE booksellerid = ?};
701 $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";
703 unless ( $allbaskets ) {
704 # Don't show the basket if it's NOT CLOSED or is FULLY RECEIVED
705 $query.=" HAVING (closedate IS NULL OR (
707 IF(aqorders.datereceived IS NULL
708 AND aqorders.datecancellationprinted IS NULL
714 my $sth = $dbh->prepare($query);
715 $sth->execute($supplierid);
716 my $baskets = $sth->fetchall_arrayref({});
718 # Retrieve the number of biblios cancelled
719 my $cancelled_biblios = $dbh->selectall_hashref( q|
720 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
722 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
723 WHERE booksellerid = ?
724 AND aqorders.orderstatus = 'cancelled'
725 GROUP BY aqbasket.basketno
726 |, 'basketno', {}, $supplierid );
728 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
734 =head3 GetBasketUsers
736 $basketusers_ids = &GetBasketUsers($basketno);
738 Returns a list of all borrowernumbers that are in basket users list
743 my $basketno = shift;
745 return unless $basketno;
748 SELECT borrowernumber
752 my $dbh = C4::Context->dbh;
753 my $sth = $dbh->prepare($query);
754 $sth->execute($basketno);
755 my $results = $sth->fetchall_arrayref( {} );
758 foreach (@$results) {
759 push @borrowernumbers, $_->{'borrowernumber'};
762 return @borrowernumbers;
765 =head3 ModBasketUsers
767 my @basketusers_ids = (1, 2, 3);
768 &ModBasketUsers($basketno, @basketusers_ids);
770 Delete all users from basket users list, and add users in C<@basketusers_ids>
776 my ($basketno, @basketusers_ids) = @_;
778 return unless $basketno;
780 my $dbh = C4::Context->dbh;
782 DELETE FROM aqbasketusers
785 my $sth = $dbh->prepare($query);
786 $sth->execute($basketno);
789 INSERT INTO aqbasketusers (basketno, borrowernumber)
792 $sth = $dbh->prepare($query);
793 foreach my $basketuser_id (@basketusers_ids) {
794 $sth->execute($basketno, $basketuser_id);
797 # Log the basket update
798 if (C4::Context->preference("AcquisitionLog")) {
801 'MODIFY_BASKET_USERS',
804 basketno => $basketno,
805 basketusers => @basketusers_ids
813 =head3 CanUserManageBasket
815 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
816 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
818 Check if a borrower can manage a basket, according to system preference
819 AcqViewBaskets, user permissions and basket properties (creator, users list,
822 First parameter can be either a borrowernumber or a hashref as returned by
823 Koha::Patron->unblessed
825 Second parameter can be either a basketno or a hashref as returned by
826 C4::Acquisition::GetBasket.
828 The third parameter is optional. If given, it should be a hashref as returned
829 by C4::Auth::getuserflags. If not, getuserflags is called.
831 If user is authorised to manage basket, returns 1.
836 sub CanUserManageBasket {
837 my ($borrower, $basket, $userflags) = @_;
839 if (!ref $borrower) {
840 # FIXME This needs to be replaced
841 # We should not accept both scalar and array
842 # Tests need to be updated
843 $borrower = Koha::Patrons->find( $borrower )->unblessed;
846 $basket = GetBasket($basket);
849 return 0 unless ($basket and $borrower);
851 my $borrowernumber = $borrower->{borrowernumber};
852 my $basketno = $basket->{basketno};
854 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
856 if (!defined $userflags) {
857 my $dbh = C4::Context->dbh;
858 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
859 $sth->execute($borrowernumber);
860 my ($flags) = $sth->fetchrow_array;
863 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
866 unless ($userflags->{superlibrarian}
867 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
868 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
870 if (not exists $userflags->{acquisition}) {
874 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
875 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
879 if ($AcqViewBaskets eq 'user'
880 && $basket->{authorisedby} != $borrowernumber
881 && ! grep { $borrowernumber eq $_ } GetBasketUsers($basketno)) {
885 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
886 && $basket->{branch} ne $borrower->{branchcode}) {
894 #------------------------------------------------------------#
896 =head3 GetBasketsByBasketgroup
898 $baskets = &GetBasketsByBasketgroup($basketgroupid);
900 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
904 sub GetBasketsByBasketgroup {
905 my $basketgroupid = shift;
907 SELECT *, aqbasket.booksellerid as booksellerid
909 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
911 my $dbh = C4::Context->dbh;
912 my $sth = $dbh->prepare($query);
913 $sth->execute($basketgroupid);
914 return $sth->fetchall_arrayref({});
917 #------------------------------------------------------------#
919 =head3 NewBasketgroup
921 $basketgroupid = NewBasketgroup(\%hashref);
923 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
925 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
927 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
929 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
931 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
933 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
935 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
937 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
939 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
944 my $basketgroupinfo = shift;
945 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
946 my $query = "INSERT INTO aqbasketgroups (";
948 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
949 if ( defined $basketgroupinfo->{$field} ) {
950 $query .= "$field, ";
951 push(@params, $basketgroupinfo->{$field});
954 $query .= "booksellerid) VALUES (";
959 push(@params, $basketgroupinfo->{'booksellerid'});
960 my $dbh = C4::Context->dbh;
961 my $sth = $dbh->prepare($query);
962 $sth->execute(@params);
963 my $basketgroupid = $dbh->{'mysql_insertid'};
964 if( $basketgroupinfo->{'basketlist'} ) {
965 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
966 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
967 my $sth2 = $dbh->prepare($query2);
968 $sth2->execute($basketgroupid, $basketno);
971 return $basketgroupid;
974 #------------------------------------------------------------#
976 =head3 ModBasketgroup
978 ModBasketgroup(\%hashref);
980 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
982 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
984 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
986 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
988 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
990 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
992 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
994 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
996 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
1000 sub ModBasketgroup {
1001 my $basketgroupinfo = shift;
1002 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
1003 my $dbh = C4::Context->dbh;
1004 my $query = "UPDATE aqbasketgroups SET ";
1006 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
1007 if ( defined $basketgroupinfo->{$field} ) {
1008 $query .= "$field=?, ";
1009 push(@params, $basketgroupinfo->{$field});
1014 $query .= " WHERE id=?";
1015 push(@params, $basketgroupinfo->{'id'});
1016 my $sth = $dbh->prepare($query);
1017 $sth->execute(@params);
1019 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
1020 $sth->execute($basketgroupinfo->{'id'});
1022 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
1023 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
1024 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
1025 $sth->execute($basketgroupinfo->{'id'}, $basketno);
1031 #------------------------------------------------------------#
1033 =head3 DelBasketgroup
1035 DelBasketgroup($basketgroupid);
1037 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
1041 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
1047 sub DelBasketgroup {
1048 my $basketgroupid = shift;
1049 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1050 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
1051 my $dbh = C4::Context->dbh;
1052 my $sth = $dbh->prepare($query);
1053 $sth->execute($basketgroupid);
1057 #------------------------------------------------------------#
1060 =head2 FUNCTIONS ABOUT ORDERS
1062 =head3 GetBasketgroup
1064 $basketgroup = &GetBasketgroup($basketgroupid);
1066 Returns a reference to the hash containing all information about the basketgroup.
1070 sub GetBasketgroup {
1071 my $basketgroupid = shift;
1072 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1073 my $dbh = C4::Context->dbh;
1074 my $result_set = $dbh->selectall_arrayref(
1075 'SELECT * FROM aqbasketgroups WHERE id=?',
1079 return $result_set->[0]; # id is unique
1082 #------------------------------------------------------------#
1084 =head3 GetBasketgroups
1086 $basketgroups = &GetBasketgroups($booksellerid);
1088 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1092 sub GetBasketgroups {
1093 my $booksellerid = shift;
1094 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1095 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1096 my $dbh = C4::Context->dbh;
1097 my $sth = $dbh->prepare($query);
1098 $sth->execute($booksellerid);
1099 return $sth->fetchall_arrayref({});
1102 #------------------------------------------------------------#
1104 =head2 FUNCTIONS ABOUT ORDERS
1108 @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } );
1110 Looks up the pending (non-cancelled) orders with the given basket
1113 If cancelled is set, only cancelled orders will be returned.
1118 my ( $basketno, $params ) = @_;
1120 return () unless $basketno;
1122 my $orderby = $params->{orderby};
1123 my $cancelled = $params->{cancelled} || 0;
1125 my $dbh = C4::Context->dbh;
1127 SELECT biblio.*,biblioitems.*,
1131 $query .= $cancelled
1133 aqorders_transfers.ordernumber_to AS transferred_to,
1134 aqorders_transfers.timestamp AS transferred_to_timestamp
1137 aqorders_transfers.ordernumber_from AS transferred_from,
1138 aqorders_transfers.timestamp AS transferred_from_timestamp
1142 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1143 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1144 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1146 $query .= $cancelled
1148 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1151 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1159 $orderby ||= q|biblioitems.publishercode, biblio.title|;
1161 AND datecancellationprinted IS NOT NULL
1166 q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|;
1168 AND datecancellationprinted IS NULL
1172 $query .= " ORDER BY $orderby";
1174 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1179 #------------------------------------------------------------#
1181 =head3 GetOrdersByBiblionumber
1183 @orders = &GetOrdersByBiblionumber($biblionumber);
1185 Looks up the orders with linked to a specific $biblionumber, including
1186 cancelled orders and received orders.
1189 C<@orders> is an array of references-to-hash, whose keys are the
1190 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1194 sub GetOrdersByBiblionumber {
1195 my $biblionumber = shift;
1196 return unless $biblionumber;
1197 my $dbh = C4::Context->dbh;
1199 SELECT biblio.*,biblioitems.*,
1203 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1204 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1205 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1206 WHERE aqorders.biblionumber=?
1209 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1210 return @{$result_set};
1214 #------------------------------------------------------------#
1218 $order = &GetOrder($ordernumber);
1220 Looks up an order by order number.
1222 Returns a reference-to-hash describing the order. The keys of
1223 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1228 my ($ordernumber) = @_;
1229 return unless $ordernumber;
1231 my $dbh = C4::Context->dbh;
1232 my $query = qq{SELECT
1236 aqbasket.basketname,
1237 borrowers.branchcode,
1238 biblioitems.publicationyear,
1239 biblio.copyrightdate,
1240 biblioitems.editionstatement,
1244 biblioitems.publishercode,
1245 aqorders.rrp AS unitpricesupplier,
1246 aqorders.ecost AS unitpricelib,
1247 aqbudgets.budget_name AS budget,
1248 aqbooksellers.name AS supplier,
1249 aqbooksellers.id AS supplierid,
1250 biblioitems.publishercode AS publisher,
1251 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS calculateddeliverydate,
1252 DATE(aqbasket.closedate) AS orderdate,
1253 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1254 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1255 DATEDIFF(CURDATE( ),closedate) AS latesince
1256 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1257 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1258 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1259 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1260 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1261 WHERE aqorders.basketno = aqbasket.basketno
1264 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1266 # result_set assumed to contain 1 match
1267 return $result_set->[0];
1272 &ModOrder(\%hashref);
1274 Modifies an existing order. Updates the order with order number
1275 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1276 other keys of the hash update the fields with the same name in the aqorders
1277 table of the Koha database.
1282 my $orderinfo = shift;
1284 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '';
1286 my $dbh = C4::Context->dbh;
1289 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1290 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1292 # delete($orderinfo->{'branchcode'});
1293 # the hash contains a lot of entries not in aqorders, so get the columns ...
1294 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1296 my $colnames = $sth->{NAME};
1297 #FIXME Be careful. If aqorders would have columns with diacritics,
1298 #you should need to decode what you get back from NAME.
1299 #See report 10110 and guided_reports.pl
1300 my $query = "UPDATE aqorders SET ";
1302 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1303 # ... and skip hash entries that are not in the aqorders table
1304 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1305 next unless grep { $_ eq $orderinfokey } @$colnames;
1306 $query .= "$orderinfokey=?, ";
1307 push(@params, $orderinfo->{$orderinfokey});
1310 $query .= "timestamp=NOW() WHERE ordernumber=?";
1311 push(@params, $orderinfo->{'ordernumber'} );
1312 $sth = $dbh->prepare($query);
1313 $sth->execute(@params);
1317 #------------------------------------------------------------#
1321 ModItemOrder($itemnumber, $ordernumber);
1323 Modifies the ordernumber of an item in aqorders_items.
1328 my ($itemnumber, $ordernumber) = @_;
1330 return unless ($itemnumber and $ordernumber);
1332 my $dbh = C4::Context->dbh;
1334 UPDATE aqorders_items
1336 WHERE itemnumber = ?
1338 my $sth = $dbh->prepare($query);
1339 return $sth->execute($ordernumber, $itemnumber);
1342 #------------------------------------------------------------#
1344 =head3 ModReceiveOrder
1346 my ( $date_received, $new_ordernumber ) = ModReceiveOrder(
1348 biblionumber => $biblionumber,
1350 quantityreceived => $quantityreceived,
1352 invoice => $invoice,
1353 budget_id => $budget_id,
1354 datereceived => $datereceived,
1355 received_itemnumbers => \@received_itemnumbers,
1359 Updates an order, to reflect the fact that it was received, at least
1362 If a partial order is received, splits the order into two.
1364 Updates the order with biblionumber C<$biblionumber> and ordernumber
1365 C<$order->{ordernumber}>.
1370 sub ModReceiveOrder {
1372 my $biblionumber = $params->{biblionumber};
1373 my $order = { %{ $params->{order} } }; # Copy the order, we don't want to modify it
1374 my $invoice = $params->{invoice};
1375 my $quantrec = $params->{quantityreceived};
1376 my $user = $params->{user};
1377 my $budget_id = $params->{budget_id};
1378 my $datereceived = $params->{datereceived};
1379 my $received_items = $params->{received_items};
1381 my $dbh = C4::Context->dbh;
1382 $datereceived = $datereceived ? dt_from_string( $datereceived ) : dt_from_string;
1383 $datereceived = $datereceived->ymd;
1385 $order->{invoice_unitprice} ||= $order->{unitprice};
1386 $order->{invoice_currency} ||= Koha::Acquisition::Currencies->get_active->currency;
1388 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1389 if ($suggestionid) {
1390 ModSuggestion( {suggestionid=>$suggestionid,
1391 STATUS=>'AVAILABLE',
1392 biblionumber=> $biblionumber}
1396 my $result_set = $dbh->selectrow_arrayref(
1397 q{SELECT aqbasket.is_standing
1399 WHERE basketno=?},{ Slice => {} }, $order->{basketno});
1400 my $is_standing = $result_set->[0]; # we assume we have a unique basket
1402 my $new_ordernumber = $order->{ordernumber};
1403 if ( $is_standing || $order->{quantity} > $quantrec ) {
1404 # Split order line in two parts: the first is the original order line
1405 # without received items (the quantity is decreased),
1406 # the second part is a new order line with quantity=quantityrec
1407 # (entirely received)
1411 orderstatus = 'partial'|;
1412 $query .= q| WHERE ordernumber = ?|;
1413 my $sth = $dbh->prepare($query);
1416 ( $is_standing ? 1 : ($order->{quantity} - $quantrec) ),
1417 $order->{ordernumber}
1420 # Recalculate tax_value
1424 tax_value_on_ordering = quantity * | . get_rounding_sql(q|ecost_tax_excluded|) . q| * tax_rate_on_ordering,
1425 tax_value_on_receiving = quantity * | . get_rounding_sql(q|unitprice_tax_excluded|) . q| * tax_rate_on_receiving
1429 if ( not $order->{subscriptionid} && defined $order->{order_internalnote} )
1431 $query .= q|, order_internalnote = ?|;
1432 push @params, $order->{order_internalnote};
1435 $query .= q|, invoice_unitprice = ?, invoice_currency = ?|;
1436 push @params, $order->{invoice_unitprice}, $order->{invoice_currency};
1439 WHERE ordernumber = ?
1442 $dbh->do($query, undef, @params, $order->{ordernumber});
1444 delete $order->{ordernumber};
1445 $order->{budget_id} = ( $budget_id || $order->{budget_id} );
1446 $order->{quantity} = $quantrec;
1447 $order->{quantityreceived} = $quantrec;
1448 $order->{ecost_tax_excluded} //= 0;
1449 $order->{tax_rate_on_ordering} //= 0;
1450 $order->{unitprice_tax_excluded} //= 0;
1451 $order->{tax_rate_on_receiving} //= 0;
1452 $order->{tax_value_on_ordering} = $order->{quantity} * get_rounded_price($order->{ecost_tax_excluded}) * $order->{tax_rate_on_ordering};
1453 $order->{tax_value_on_receiving} = $order->{quantity} * get_rounded_price($order->{unitprice_tax_excluded}) * $order->{tax_rate_on_receiving};
1454 $order->{datereceived} = $datereceived;
1455 $order->{invoiceid} = $invoice->{invoiceid};
1456 $order->{orderstatus} = 'complete';
1457 $new_ordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber; # TODO What if the store fails?
1459 if ($received_items) {
1460 foreach my $itemnumber (@$received_items) {
1461 ModItemOrder($itemnumber, $new_ordernumber);
1467 SET quantityreceived = ?,
1471 orderstatus = 'complete'
1475 , replacementprice = ?
1476 | if defined $order->{replacementprice};
1479 , unitprice = ?, unitprice_tax_included = ?, unitprice_tax_excluded = ?
1480 | if defined $order->{unitprice};
1483 ,tax_value_on_receiving = ?
1484 | if defined $order->{tax_value_on_receiving};
1487 ,tax_rate_on_receiving = ?
1488 | if defined $order->{tax_rate_on_receiving};
1491 , order_internalnote = ?
1492 | if defined $order->{order_internalnote};
1495 , invoice_unitprice = ?, invoice_currency = ?
1498 $query .= q| where biblionumber=? and ordernumber=?|;
1500 my $sth = $dbh->prepare( $query );
1501 my @params = ( $quantrec, $datereceived, $invoice->{invoiceid}, ( $budget_id ? $budget_id : $order->{budget_id} ) );
1503 if ( defined $order->{replacementprice} ) {
1504 push @params, $order->{replacementprice};
1507 if ( defined $order->{unitprice} ) {
1508 push @params, $order->{unitprice}, $order->{unitprice_tax_included}, $order->{unitprice_tax_excluded};
1511 if ( defined $order->{tax_value_on_receiving} ) {
1512 push @params, $order->{tax_value_on_receiving};
1515 if ( defined $order->{tax_rate_on_receiving} ) {
1516 push @params, $order->{tax_rate_on_receiving};
1519 if ( defined $order->{order_internalnote} ) {
1520 push @params, $order->{order_internalnote};
1523 push @params, $order->{invoice_unitprice}, $order->{invoice_currency};
1525 push @params, ( $biblionumber, $order->{ordernumber} );
1527 $sth->execute( @params );
1529 # All items have been received, sent a notification to users
1530 NotifyOrderUsers( $order->{ordernumber} );
1533 return ($datereceived, $new_ordernumber);
1536 =head3 CancelReceipt
1538 my $parent_ordernumber = CancelReceipt($ordernumber);
1540 Cancel an order line receipt and update the parent order line, as if no
1542 If items are created at receipt (AcqCreateItem = receiving) then delete
1548 my $ordernumber = shift;
1550 return unless $ordernumber;
1552 my $dbh = C4::Context->dbh;
1554 SELECT datereceived, parent_ordernumber, quantity
1556 WHERE ordernumber = ?
1558 my $sth = $dbh->prepare($query);
1559 $sth->execute($ordernumber);
1560 my $order = $sth->fetchrow_hashref;
1562 warn "CancelReceipt: order $ordernumber does not exist";
1565 unless($order->{'datereceived'}) {
1566 warn "CancelReceipt: order $ordernumber is not received";
1570 my $parent_ordernumber = $order->{'parent_ordernumber'};
1572 my $order_obj = Koha::Acquisition::Orders->find( $ordernumber ); # FIXME rewrite all this subroutine using this object
1573 my @itemnumbers = $order_obj->items->get_column('itemnumber');
1575 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1576 # The order line has no parent, just mark it as not received
1579 SET quantityreceived = ?,
1582 orderstatus = 'ordered'
1583 WHERE ordernumber = ?
1585 $sth = $dbh->prepare($query);
1586 $sth->execute(0, undef, undef, $ordernumber);
1587 _cancel_items_receipt( $order_obj );
1589 # The order line has a parent, increase parent quantity and delete
1591 unless ( $order_obj->basket->is_standing ) {
1593 SELECT quantity, datereceived
1595 WHERE ordernumber = ?
1597 $sth = $dbh->prepare($query);
1598 $sth->execute($parent_ordernumber);
1599 my $parent_order = $sth->fetchrow_hashref;
1600 unless($parent_order) {
1601 warn "Parent order $parent_ordernumber does not exist.";
1604 if($parent_order->{'datereceived'}) {
1605 warn "CancelReceipt: parent order is received.".
1606 " Can't cancel receipt.";
1612 orderstatus = 'ordered'
1613 WHERE ordernumber = ?
1615 $sth = $dbh->prepare($query);
1616 my $rv = $sth->execute(
1617 $order->{'quantity'} + $parent_order->{'quantity'},
1621 warn "Cannot update parent order line, so do not cancel".
1626 # Recalculate tax_value
1630 tax_value_on_ordering = quantity * | . get_rounding_sql(q|ecost_tax_excluded|) . q| * tax_rate_on_ordering,
1631 tax_value_on_receiving = quantity * | . get_rounding_sql(q|unitprice_tax_excluded|) . q| * tax_rate_on_receiving
1632 WHERE ordernumber = ?
1633 |, undef, $parent_ordernumber);
1636 _cancel_items_receipt( $order_obj, $parent_ordernumber );
1639 DELETE FROM aqorders
1640 WHERE ordernumber = ?
1642 $sth = $dbh->prepare($query);
1643 $sth->execute($ordernumber);
1647 if( $order_obj->basket->effective_create_items eq 'ordering' ) {
1648 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1650 for my $in ( @itemnumbers ) {
1651 my $item = Koha::Items->find( $in ); # FIXME We do not need that, we already have Koha::Items from $order_obj->items
1652 my $biblio = $item->biblio;
1653 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber' );
1654 my $item_marc = C4::Items::GetMarcItem( $biblio->biblionumber, $in );
1655 for my $affect ( @affects ) {
1656 my ( $sf, $v ) = split q{=}, $affect, 2;
1657 foreach ( $item_marc->field($itemfield) ) {
1658 $_->update( $sf => $v );
1661 C4::Items::ModItemFromMarc( $item_marc, $biblio->biblionumber, $in );
1666 return $parent_ordernumber;
1669 sub _cancel_items_receipt {
1670 my ( $order, $parent_ordernumber ) = @_;
1671 $parent_ordernumber ||= $order->ordernumber;
1673 my $items = $order->items;
1674 if ( $order->basket->effective_create_items eq 'receiving' ) {
1675 # Remove items that were created at receipt
1677 DELETE FROM items, aqorders_items
1678 USING items, aqorders_items
1679 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1681 my $dbh = C4::Context->dbh;
1682 my $sth = $dbh->prepare($query);
1683 while ( my $item = $items->next ) {
1684 $sth->execute($item->itemnumber, $item->itemnumber);
1688 while ( my $item = $items->next ) {
1689 ModItemOrder($item->itemnumber, $parent_ordernumber);
1694 #------------------------------------------------------------#
1698 @results = &SearchOrders({
1699 ordernumber => $ordernumber,
1702 booksellerid => $booksellerid,
1703 basketno => $basketno,
1704 basketname => $basketname,
1705 basketgroupname => $basketgroupname,
1709 biblionumber => $biblionumber,
1710 budget_id => $budget_id
1713 Searches for orders filtered by criteria.
1715 C<$ordernumber> Finds matching orders or transferred orders by ordernumber.
1716 C<$search> Finds orders matching %$search% in title, author, or isbn.
1717 C<$owner> Finds order for the logged in user.
1718 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1719 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1722 C<@results> is an array of references-to-hash with the keys are fields
1723 from aqorders, biblio, biblioitems and aqbasket tables.
1728 my ( $params ) = @_;
1729 my $ordernumber = $params->{ordernumber};
1730 my $search = $params->{search};
1731 my $ean = $params->{ean};
1732 my $booksellerid = $params->{booksellerid};
1733 my $basketno = $params->{basketno};
1734 my $basketname = $params->{basketname};
1735 my $basketgroupname = $params->{basketgroupname};
1736 my $owner = $params->{owner};
1737 my $pending = $params->{pending};
1738 my $ordered = $params->{ordered};
1739 my $biblionumber = $params->{biblionumber};
1740 my $budget_id = $params->{budget_id};
1742 my $dbh = C4::Context->dbh;
1745 SELECT aqbasket.basketno,
1747 borrowers.firstname,
1750 biblioitems.biblioitemnumber,
1751 biblioitems.publishercode,
1752 biblioitems.publicationyear,
1753 aqbasket.authorisedby,
1754 aqbasket.booksellerid,
1756 aqbasket.creationdate,
1757 aqbasket.basketname,
1758 aqbasketgroups.id as basketgroupid,
1759 aqbasketgroups.name as basketgroupname,
1762 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1763 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1764 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1765 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1766 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1769 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1771 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1775 WHERE (datecancellationprinted is NULL)
1778 if ( $pending or $ordered ) {
1781 ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) )
1783 ( quantity > quantityreceived OR quantityreceived is NULL )
1787 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1795 my $userenv = C4::Context->userenv;
1796 if ( C4::Context->preference("IndependentBranches") ) {
1797 unless ( C4::Context->IsSuperLibrarian() ) {
1800 borrowers.branchcode = ?
1801 OR borrowers.branchcode = ''
1804 push @args, $userenv->{branch};
1808 if ( $ordernumber ) {
1809 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1810 push @args, ( $ordernumber, $ordernumber );
1812 if ( $biblionumber ) {
1813 $query .= 'AND aqorders.biblionumber = ?';
1814 push @args, $biblionumber;
1817 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1818 push @args, ("%$search%","%$search%","%$search%");
1821 $query .= ' AND biblioitems.ean = ?';
1824 if ( $booksellerid ) {
1825 $query .= 'AND aqbasket.booksellerid = ?';
1826 push @args, $booksellerid;
1829 $query .= 'AND aqbasket.basketno = ?';
1830 push @args, $basketno;
1833 $query .= 'AND aqbasket.basketname LIKE ?';
1834 push @args, "%$basketname%";
1836 if( $basketgroupname ) {
1837 $query .= ' AND aqbasketgroups.name LIKE ?';
1838 push @args, "%$basketgroupname%";
1842 $query .= ' AND aqbasket.authorisedby=? ';
1843 push @args, $userenv->{'number'};
1847 $query .= ' AND aqorders.budget_id = ?';
1848 push @args, $budget_id;
1851 $query .= ' ORDER BY aqbasket.basketno';
1853 my $sth = $dbh->prepare($query);
1854 $sth->execute(@args);
1855 return $sth->fetchall_arrayref({});
1858 #------------------------------------------------------------#
1860 =head3 TransferOrder
1862 my $newordernumber = TransferOrder($ordernumber, $basketno);
1864 Transfer an order line to a basket.
1865 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1866 to BOOKSELLER on DATE' and create new order with internal note
1867 'Transferred from BOOKSELLER on DATE'.
1868 Move all attached items to the new order.
1869 Received orders cannot be transferred.
1870 Return the ordernumber of created order.
1875 my ($ordernumber, $basketno) = @_;
1877 return unless ($ordernumber and $basketno);
1879 my $order = Koha::Acquisition::Orders->find( $ordernumber ) or return;
1880 return if $order->datereceived;
1882 my $orderhash = $order->unblessed;
1884 my $basket = GetBasket($basketno);
1885 return unless $basket;
1887 my $dbh = C4::Context->dbh;
1888 my ($query, $sth, $rv);
1892 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1893 WHERE ordernumber = ?
1895 $sth = $dbh->prepare($query);
1896 $rv = $sth->execute('cancelled', $ordernumber);
1898 delete $orderhash->{ordernumber};
1899 delete $orderhash->{parent_ordernumber};
1900 $orderhash->{basketno} = $basketno;
1902 my $neworder = Koha::Acquisition::Order->new($orderhash);
1903 my $newordernumber = $neworder->store->ordernumber;
1906 UPDATE aqorders_items
1908 WHERE ordernumber = ?
1910 $sth = $dbh->prepare($query);
1911 $sth->execute($newordernumber, $ordernumber);
1914 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1917 $sth = $dbh->prepare($query);
1918 $sth->execute($ordernumber, $newordernumber);
1920 # Copy additional fields values
1921 foreach my $afv ($order->additional_field_values->as_list) {
1922 Koha::AdditionalFieldValue->new({
1923 field_id => $afv->field_id,
1924 record_id => $newordernumber,
1925 value => $afv->value,
1929 return $newordernumber;
1932 =head3 get_rounding_sql
1934 $rounding_sql = get_rounding_sql($column_name);
1936 returns the correct SQL routine based on OrderPriceRounding system preference.
1940 sub get_rounding_sql {
1941 my ( $round_string ) = @_;
1942 my $rounding_pref = C4::Context->preference('OrderPriceRounding') // q{};
1943 if ( $rounding_pref eq "nearest_cent" ) {
1944 return "CAST($round_string*100 AS SIGNED)/100";
1946 return $round_string;
1949 =head3 get_rounded_price
1951 $rounded_price = get_rounded_price( $price );
1953 returns a price rounded as specified in OrderPriceRounding system preference.
1957 sub get_rounded_price {
1959 my $rounding_pref = C4::Context->preference('OrderPriceRounding') // q{};
1960 if( $rounding_pref eq 'nearest_cent' ) {
1961 return Koha::Number::Price->new( $price )->round();
1968 \@order_loop = GetHistory( %params );
1970 Retreives some acquisition history information
1980 basket - search both basket name and number
1981 booksellerinvoicenumber
1984 orderstatus (note that orderstatus '' will retrieve orders
1985 of any status except cancelled)
1989 get_canceled_order (if set to a true value, cancelled orders will
1993 $order_loop is a list of hashrefs that each look like this:
1995 'author' => 'Twain, Mark',
1997 'biblionumber' => '215',
1999 'creationdate' => 'MM/DD/YYYY',
2000 'datereceived' => undef,
2003 'invoicenumber' => undef,
2005 'ordernumber' => '1',
2007 'quantityreceived' => undef,
2008 'title' => 'The Adventures of Huckleberry Finn',
2009 'managing_library' => 'CPL'
2010 'is_standing' => '1'
2016 # don't run the query if there are no parameters (list would be too long for sure !)
2017 croak "No search params" unless @_;
2019 my $title = $params{title};
2020 my $author = $params{author};
2021 my $isbn = $params{isbn};
2022 my $issn = $params{issn};
2023 my $ean = $params{ean};
2024 my $name = $params{name};
2025 my $internalnote = $params{internalnote};
2026 my $vendornote = $params{vendornote};
2027 my $from_placed_on = $params{from_placed_on};
2028 my $to_placed_on = $params{to_placed_on};
2029 my $basket = $params{basket};
2030 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2031 my $basketgroupname = $params{basketgroupname};
2032 my $budget = $params{budget};
2033 my $orderstatus = $params{orderstatus};
2034 my $is_standing = $params{is_standing};
2035 my $biblionumber = $params{biblionumber};
2036 my $get_canceled_order = $params{get_canceled_order} || 0;
2037 my $ordernumber = $params{ordernumber};
2038 my $search_children_too = $params{search_children_too} || 0;
2039 my $created_by = $params{created_by} || [];
2040 my $managing_library = $params{managing_library};
2041 my $ordernumbers = $params{ordernumbers} || [];
2042 my $additional_fields = $params{additional_fields} // [];
2045 my $total_qtyreceived = 0;
2046 my $total_price = 0;
2048 #get variation of isbn
2052 if ( C4::Context->preference("SearchWithISBNVariations") ){
2053 @isbns = C4::Koha::GetVariationsOfISBN( $isbn );
2054 foreach my $isb (@isbns){
2055 push @isbn_params, '?';
2060 push @isbn_params, '?';
2064 #get variation of issn
2068 if ( C4::Context->preference("SearchWithISSNVariations") ){
2069 @issns = C4::Koha::GetVariationsOfISSN( $issn );
2070 push @issn_params, ('?') x @issns;
2074 push @issn_params, '?';
2078 my $dbh = C4::Context->dbh;
2081 COALESCE(biblio.title, deletedbiblio.title) AS title,
2082 COALESCE(biblio.author, deletedbiblio.author) AS author,
2083 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2084 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2086 aqbasket.basketname,
2087 aqbasket.basketgroupid,
2088 aqbasket.authorisedby,
2089 aqbasket.is_standing,
2090 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2091 branch as managing_library,
2092 aqbasketgroups.name as groupname,
2094 aqbasket.creationdate,
2095 aqorders.datereceived,
2097 aqorders.quantityreceived,
2099 aqorders.ordernumber,
2101 aqinvoices.invoicenumber,
2102 aqbooksellers.id as id,
2103 aqorders.biblionumber,
2104 aqorders.orderstatus,
2105 aqorders.parent_ordernumber,
2106 aqorders.order_internalnote,
2107 aqorders.order_vendornote,
2108 aqbudgets.budget_name
2110 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2113 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2114 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2115 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2116 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2117 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2118 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2119 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2120 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2121 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2122 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2125 $query .= " WHERE 1 ";
2127 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2128 $query .= " AND datecancellationprinted IS NULL ";
2131 my @query_params = ();
2133 if ( $biblionumber ) {
2134 $query .= " AND biblio.biblionumber = ?";
2135 push @query_params, $biblionumber;
2139 $query .= " AND biblio.title LIKE ? ";
2140 $title =~ s/\s+/%/g;
2141 push @query_params, "%$title%";
2145 $query .= " AND biblio.author LIKE ? ";
2146 push @query_params, "%$author%";
2150 $query .= " AND ( biblioitems.isbn LIKE " . join (" OR biblioitems.isbn LIKE ", @isbn_params ) . ")";
2151 foreach my $isb (@isbns){
2152 push @query_params, "%$isb%";
2157 $query .= " AND ( biblioitems.issn LIKE " . join (" OR biblioitems.issn LIKE ", @issn_params ) . ")";
2158 foreach my $isn (@issns){
2159 push @query_params, "%$isn%";
2164 $query .= " AND biblioitems.ean = ? ";
2165 push @query_params, "$ean";
2168 $query .= " AND aqbooksellers.name LIKE ? ";
2169 push @query_params, "%$name%";
2173 $query .= " AND aqbudgets.budget_id = ? ";
2174 push @query_params, "$budget";
2177 if ( $from_placed_on ) {
2178 $query .= " AND creationdate >= ? ";
2179 push @query_params, $from_placed_on;
2182 if ( $to_placed_on ) {
2183 $query .= " AND creationdate <= ? ";
2184 push @query_params, $to_placed_on;
2187 if ( defined $orderstatus and $orderstatus ne '' and $orderstatus ne 'any') {
2188 $query .= " AND aqorders.orderstatus = ? ";
2189 push @query_params, "$orderstatus";
2192 if ( $is_standing ) {
2193 $query .= " AND is_standing = ? ";
2194 push @query_params, $is_standing;
2198 if ($basket =~ m/^\d+$/) {
2199 $query .= " AND aqorders.basketno = ? ";
2200 push @query_params, $basket;
2202 $query .= " AND aqbasket.basketname LIKE ? ";
2203 push @query_params, "%$basket%";
2207 if ( $internalnote ) {
2208 $query .= " AND aqorders.order_internalnote LIKE ? ";
2209 push @query_params, "%$internalnote%";
2212 if ( $vendornote ) {
2213 $query .= " AND aqorders.order_vendornote LIKE ?";
2214 push @query_params, "%$vendornote%";
2217 if ($booksellerinvoicenumber) {
2218 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2219 push @query_params, "%$booksellerinvoicenumber%";
2222 if ($basketgroupname) {
2223 $query .= " AND aqbasketgroups.name LIKE ? ";
2224 push @query_params, "%$basketgroupname%";
2228 $query .= " AND (aqorders.ordernumber = ? ";
2229 push @query_params, $ordernumber;
2230 if ($search_children_too) {
2231 $query .= " OR aqorders.parent_ordernumber = ? ";
2232 push @query_params, $ordernumber;
2237 if ( @$created_by ) {
2238 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2239 push @query_params, @$created_by;
2242 if ( $managing_library ) {
2243 $query .= " AND aqbasket.branch = ? ";
2244 push @query_params, $managing_library;
2247 if ( @$ordernumbers ) {
2248 $query .= ' AND (aqorders.ordernumber IN ( ' . join (',', ('?') x @$ordernumbers ) . '))';
2249 push @query_params, @$ordernumbers;
2251 if ( @$additional_fields ) {
2252 my @baskets = Koha::Acquisition::Baskets->filter_by_additional_fields($additional_fields)->as_list;
2254 return [] unless @baskets;
2256 # No parameterization because record IDs come directly from DB
2257 $query .= ' AND aqbasket.basketno IN ( ' . join( ',', map { $_->basketno } @baskets ) . ' )';
2260 if ( C4::Context->preference("IndependentBranches") ) {
2261 unless ( C4::Context->IsSuperLibrarian() ) {
2262 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2263 push @query_params, C4::Context->userenv->{branch};
2266 $query .= " ORDER BY id";
2268 return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2271 =head2 GetRecentAcqui
2273 $results = GetRecentAcqui($days);
2275 C<$results> is a ref to a table which contains hashref
2279 sub GetRecentAcqui {
2281 my $dbh = C4::Context->dbh;
2285 ORDER BY timestamp DESC
2288 my $sth = $dbh->prepare($query);
2290 my $results = $sth->fetchall_arrayref({});
2294 #------------------------------------------------------------#
2298 &AddClaim($ordernumber);
2300 Add a claim for an order
2305 my ($ordernumber) = @_;
2306 my $dbh = C4::Context->dbh;
2309 claims_count = claims_count + 1,
2310 claimed_date = CURDATE()
2311 WHERE ordernumber = ?
2313 my $sth = $dbh->prepare($query);
2314 $sth->execute($ordernumber);
2319 my @invoices = GetInvoices(
2320 invoicenumber => $invoicenumber,
2321 supplierid => $supplierid,
2322 suppliername => $suppliername,
2323 shipmentdatefrom => $shipmentdatefrom, # ISO format
2324 shipmentdateto => $shipmentdateto, # ISO format
2325 billingdatefrom => $billingdatefrom, # ISO format
2326 billingdateto => $billingdateto, # ISO format
2327 isbneanissn => $isbn_or_ean_or_issn,
2330 publisher => $publisher,
2331 publicationyear => $publicationyear,
2332 branchcode => $branchcode,
2333 order_by => $order_by
2336 Return a list of invoices that match all given criteria.
2338 $order_by is "column_name (asc|desc)", where column_name is any of
2339 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2340 'shipmentcost', 'shipmentcost_budgetid'.
2342 asc is the default if omitted
2349 my $additional_fields = $args{additional_fields} // [];
2350 my $matching_invoice_ids_for_additional_fields = [];
2351 if ( @$additional_fields ) {
2352 my @invoices = Koha::Acquisition::Invoices->filter_by_additional_fields($additional_fields)->as_list;
2354 return () unless @invoices;
2356 $matching_invoice_ids_for_additional_fields = [ map {
2361 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2362 closedate shipmentcost shipmentcost_budgetid);
2364 my $dbh = C4::Context->dbh;
2366 SELECT aqinvoices.invoiceid, aqinvoices.invoicenumber, aqinvoices.booksellerid, aqinvoices.shipmentdate, aqinvoices.billingdate, aqinvoices.closedate, aqinvoices.shipmentcost, aqinvoices.shipmentcost_budgetid, aqinvoices.message_id,
2367 aqbooksellers.name AS suppliername,
2370 aqorders.datereceived IS NOT NULL,
2371 aqorders.biblionumber,
2374 ) AS receivedbiblios,
2377 aqorders.subscriptionid IS NOT NULL,
2378 aqorders.subscriptionid,
2381 ) AS is_linked_to_subscriptions,
2382 SUM(aqorders.quantityreceived) AS receiveditems
2384 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2385 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2386 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2387 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2388 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2389 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2390 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2395 if($args{supplierid}) {
2396 push @bind_strs, " aqinvoices.booksellerid = ? ";
2397 push @bind_args, $args{supplierid};
2399 if($args{invoicenumber}) {
2400 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2401 push @bind_args, "%$args{invoicenumber}%";
2403 if($args{suppliername}) {
2404 push @bind_strs, " aqbooksellers.name LIKE ? ";
2405 push @bind_args, "%$args{suppliername}%";
2407 if($args{shipmentdatefrom}) {
2408 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2409 push @bind_args, $args{shipmentdatefrom};
2411 if($args{shipmentdateto}) {
2412 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2413 push @bind_args, $args{shipmentdateto};
2415 if($args{billingdatefrom}) {
2416 push @bind_strs, " aqinvoices.billingdate >= ? ";
2417 push @bind_args, $args{billingdatefrom};
2419 if($args{billingdateto}) {
2420 push @bind_strs, " aqinvoices.billingdate <= ? ";
2421 push @bind_args, $args{billingdateto};
2423 if($args{isbneanissn}) {
2424 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2425 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2428 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2429 push @bind_args, $args{title};
2432 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2433 push @bind_args, $args{author};
2435 if($args{publisher}) {
2436 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2437 push @bind_args, $args{publisher};
2439 if($args{publicationyear}) {
2440 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2441 push @bind_args, $args{publicationyear}, $args{publicationyear};
2443 if($args{branchcode}) {
2444 push @bind_strs, " borrowers.branchcode = ? ";
2445 push @bind_args, $args{branchcode};
2447 if($args{message_id}) {
2448 push @bind_strs, " aqinvoices.message_id = ? ";
2449 push @bind_args, $args{message_id};
2452 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2454 # Handle additional fields filtering
2455 if ( @$additional_fields ) {
2456 my $operator = ' WHERE';
2457 if ( @bind_strs ) { # there's a WHERE already
2460 $query .= "$operator aqinvoices.invoiceid IN ("
2461 . join( ', ', @$matching_invoice_ids_for_additional_fields )
2465 $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";
2467 if($args{order_by}) {
2468 my ($column, $direction) = split / /, $args{order_by};
2469 if(grep { $_ eq $column } @columns) {
2470 $direction ||= 'ASC';
2471 $query .= " ORDER BY $column $direction";
2475 my $sth = $dbh->prepare($query);
2476 $sth->execute(@bind_args);
2478 my $results = $sth->fetchall_arrayref({});
2484 my $invoice = GetInvoice($invoiceid);
2486 Get informations about invoice with given $invoiceid
2488 Return a hash filled with aqinvoices.* fields
2493 my ($invoiceid) = @_;
2496 return unless $invoiceid;
2498 my $dbh = C4::Context->dbh;
2504 my $sth = $dbh->prepare($query);
2505 $sth->execute($invoiceid);
2507 $invoice = $sth->fetchrow_hashref;
2511 =head3 GetInvoiceDetails
2513 my $invoice = GetInvoiceDetails($invoiceid)
2515 Return informations about an invoice + the list of related order lines
2517 Orders informations are in $invoice->{orders} (array ref)
2521 sub GetInvoiceDetails {
2522 my ($invoiceid) = @_;
2524 if ( !defined $invoiceid ) {
2525 carp 'GetInvoiceDetails called without an invoiceid';
2529 my $dbh = C4::Context->dbh;
2531 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2533 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2536 my $sth = $dbh->prepare($query);
2537 $sth->execute($invoiceid);
2539 my $invoice = $sth->fetchrow_hashref;
2544 biblio.copyrightdate,
2546 biblioitems.publishercode,
2547 biblioitems.publicationyear,
2548 aqbasket.basketname,
2549 aqbasketgroups.id AS basketgroupid,
2550 aqbasketgroups.name AS basketgroupname
2552 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2553 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2554 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2555 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2558 $sth = $dbh->prepare($query);
2559 $sth->execute($invoiceid);
2560 $invoice->{orders} = $sth->fetchall_arrayref({});
2561 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2568 my $invoiceid = AddInvoice(
2569 invoicenumber => $invoicenumber,
2570 booksellerid => $booksellerid,
2571 shipmentdate => $shipmentdate,
2572 billingdate => $billingdate,
2573 closedate => $closedate,
2574 shipmentcost => $shipmentcost,
2575 shipmentcost_budgetid => $shipmentcost_budgetid
2578 Create a new invoice and return its id or undef if it fails.
2585 return unless(%invoice and $invoice{invoicenumber});
2587 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2588 closedate shipmentcost shipmentcost_budgetid message_id);
2592 foreach my $key (keys %invoice) {
2593 if(0 < grep { $_ eq $key } @columns) {
2594 push @set_strs, "$key = ?";
2595 push @set_args, ($invoice{$key} || undef);
2601 my $dbh = C4::Context->dbh;
2602 my $query = "INSERT INTO aqinvoices SET ";
2603 $query .= join (",", @set_strs);
2604 my $sth = $dbh->prepare($query);
2605 $rv = $sth->execute(@set_args);
2607 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2616 invoiceid => $invoiceid, # Mandatory
2617 invoicenumber => $invoicenumber,
2618 booksellerid => $booksellerid,
2619 shipmentdate => $shipmentdate,
2620 billingdate => $billingdate,
2621 closedate => $closedate,
2622 shipmentcost => $shipmentcost,
2623 shipmentcost_budgetid => $shipmentcost_budgetid
2626 Modify an invoice, invoiceid is mandatory.
2628 Return undef if it fails.
2635 return unless(%invoice and $invoice{invoiceid});
2637 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2638 closedate shipmentcost shipmentcost_budgetid);
2642 foreach my $key (keys %invoice) {
2643 if(0 < grep { $_ eq $key } @columns) {
2644 push @set_strs, "$key = ?";
2645 push @set_args, ($invoice{$key} || undef);
2649 my $dbh = C4::Context->dbh;
2650 my $query = "UPDATE aqinvoices SET ";
2651 $query .= join(",", @set_strs);
2652 $query .= " WHERE invoiceid = ?";
2654 my $sth = $dbh->prepare($query);
2655 $sth->execute(@set_args, $invoice{invoiceid});
2660 CloseInvoice($invoiceid);
2664 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2669 my ($invoiceid) = @_;
2671 return unless $invoiceid;
2673 my $dbh = C4::Context->dbh;
2676 SET closedate = CAST(NOW() AS DATE)
2679 my $sth = $dbh->prepare($query);
2680 $sth->execute($invoiceid);
2683 =head3 ReopenInvoice
2685 ReopenInvoice($invoiceid);
2689 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => $closedate );
2694 my ($invoiceid) = @_;
2696 return unless $invoiceid;
2698 my $dbh = C4::Context->dbh;
2701 SET closedate = NULL
2704 my $sth = $dbh->prepare($query);
2705 $sth->execute($invoiceid);
2710 DelInvoice($invoiceid);
2712 Delete an invoice if there are no items attached to it.
2717 my ($invoiceid) = @_;
2719 return unless $invoiceid;
2721 my $dbh = C4::Context->dbh;
2727 my $sth = $dbh->prepare($query);
2728 $sth->execute($invoiceid);
2729 my $res = $sth->fetchrow_arrayref;
2730 if ( $res && $res->[0] == 0 ) {
2732 DELETE FROM aqinvoices
2735 my $sth = $dbh->prepare($query);
2736 return ( $sth->execute($invoiceid) > 0 );
2741 =head3 MergeInvoices
2743 MergeInvoices($invoiceid, \@sourceids);
2745 Merge the invoices identified by the IDs in \@sourceids into
2746 the invoice identified by $invoiceid.
2751 my ($invoiceid, $sourceids) = @_;
2753 return unless $invoiceid;
2754 foreach my $sourceid (@$sourceids) {
2755 next if $sourceid == $invoiceid;
2756 my $source = GetInvoiceDetails($sourceid);
2757 foreach my $order (@{$source->{'orders'}}) {
2758 $order->{'invoiceid'} = $invoiceid;
2761 DelInvoice($source->{'invoiceid'});
2766 =head3 GetBiblioCountByBasketno
2768 $biblio_count = &GetBiblioCountByBasketno($basketno);
2770 Looks up the biblio's count that has basketno value $basketno
2776 sub GetBiblioCountByBasketno {
2777 my ($basketno) = @_;
2778 my $dbh = C4::Context->dbh;
2780 SELECT COUNT( DISTINCT( biblionumber ) )
2783 AND datecancellationprinted IS NULL
2786 my $sth = $dbh->prepare($query);
2787 $sth->execute($basketno);
2788 return $sth->fetchrow;
2791 =head3 GetOrderUsers
2793 $order_users_ids = &GetOrderUsers($ordernumber);
2795 Returns a list of all borrowernumbers that are in order users list
2800 my ($ordernumber) = @_;
2802 return unless $ordernumber;
2805 SELECT borrowernumber
2807 WHERE ordernumber = ?
2809 my $dbh = C4::Context->dbh;
2810 my $sth = $dbh->prepare($query);
2811 $sth->execute($ordernumber);
2812 my $results = $sth->fetchall_arrayref( {} );
2814 my @borrowernumbers;
2815 foreach (@$results) {
2816 push @borrowernumbers, $_->{'borrowernumber'};
2819 return @borrowernumbers;
2822 =head3 ModOrderUsers
2824 my @order_users_ids = (1, 2, 3);
2825 &ModOrderUsers($ordernumber, @basketusers_ids);
2827 Delete all users from order users list, and add users in C<@order_users_ids>
2833 my ( $ordernumber, @order_users_ids ) = @_;
2835 return unless $ordernumber;
2837 my $dbh = C4::Context->dbh;
2839 DELETE FROM aqorder_users
2840 WHERE ordernumber = ?
2842 my $sth = $dbh->prepare($query);
2843 $sth->execute($ordernumber);
2846 INSERT INTO aqorder_users (ordernumber, borrowernumber)
2849 $sth = $dbh->prepare($query);
2850 foreach my $order_user_id (@order_users_ids) {
2851 $sth->execute( $ordernumber, $order_user_id );
2855 sub NotifyOrderUsers {
2856 my ($ordernumber) = @_;
2858 my @borrowernumbers = GetOrderUsers($ordernumber);
2859 return unless @borrowernumbers;
2861 my $order = GetOrder( $ordernumber );
2862 for my $borrowernumber (@borrowernumbers) {
2863 my $patron = Koha::Patrons->find( $borrowernumber );
2864 my $library = $patron->library->unblessed;
2865 my $biblio = Koha::Biblios->find( $order->{biblionumber} )->unblessed;
2866 my $letter = C4::Letters::GetPreparedLetter(
2867 module => 'acquisition',
2868 letter_code => 'ACQ_NOTIF_ON_RECEIV',
2869 branchcode => $library->{branchcode},
2870 lang => $patron->lang,
2872 'branches' => $library,
2873 'borrowers' => $patron->unblessed,
2874 'biblio' => $biblio,
2875 'aqorders' => $order,
2879 C4::Letters::EnqueueLetter(
2882 borrowernumber => $borrowernumber,
2883 LibraryName => C4::Context->preference("LibraryName"),
2884 message_transport_type => 'email',
2886 ) or warn "can't enqueue letter $letter";
2891 =head3 FillWithDefaultValues
2893 FillWithDefaultValues( $marc_record, $params );
2895 This will update the record with default value defined in the ACQ framework.
2896 For all existing fields, if a default value exists and there are no subfield, it will be created.
2897 If the field does not exist, it will be created too.
2899 If the parameter only_mandatory => 1 is passed via $params, only the mandatory
2900 defaults are being applied to the record.
2904 sub FillWithDefaultValues {
2905 my ( $record, $params ) = @_;
2906 my $mandatory = $params->{only_mandatory};
2907 my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ', { unsafe => 1 } );
2910 C4::Biblio::GetMarcFromKohaField( 'items.itemnumber' );
2911 for my $tag ( sort keys %$tagslib ) {
2913 next if $tag == $itemfield;
2914 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
2915 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
2916 next if $mandatory && !$tagslib->{$tag}{$subfield}{mandatory};
2917 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
2918 if ( defined $defaultvalue and $defaultvalue ne '' ) {
2919 my @fields = $record->field($tag);
2921 for my $field (@fields) {
2922 if ( $field->is_control_field ) {
2923 $field->update($defaultvalue) if not defined $field->data;
2925 elsif ( not defined $field->subfield($subfield) ) {
2926 $field->add_subfields(
2927 $subfield => $defaultvalue );
2932 if ( $tag < 10 ) { # is_control_field
2933 $record->insert_fields_ordered(
2940 $record->insert_fields_ordered(
2942 $tag, '', '', $subfield => $defaultvalue
2958 Koha Development Team <http://koha-community.org/>