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
88 GetBiblioCountByBasketno
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{};
204 # Log the basket creation
205 if (C4::Context->preference("AcquisitionLog")) {
206 my $created = Koha::Acquisition::Baskets->find( $basket );
211 to_json($created->unblessed)
215 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
216 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items );
223 &ReopenBasket($basketno);
231 my $dbh = C4::Context->dbh;
232 $dbh->do( q{UPDATE aqbasket SET closedate=NULL WHERE basketno=?}, {}, $basketno );
236 SET orderstatus = 'new'
238 AND orderstatus NOT IN ( 'complete', 'cancelled' )
241 # Log the basket reopening
242 if (C4::Context->preference("AcquisitionLog")) {
243 my $reopened = Koha::Acquisition::Baskets->find( $basketno );
248 to_json($reopened->unblessed)
254 #------------------------------------------------------------#
256 =head3 GetBasketAsCSV
258 &GetBasketAsCSV($basketno);
260 Export a basket as CSV
262 $cgi parameter is needed for column name translation
267 my ($basketno, $cgi, $csv_profile_id) = @_;
268 my $basket = GetBasket($basketno);
269 my @orders = GetOrders($basketno);
270 my $contract = GetContract({
271 contractnumber => $basket->{'contractnumber'}
274 my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
276 if ($csv_profile_id) {
277 my $csv_profile = Koha::CsvProfiles->find( $csv_profile_id );
278 Koha::Exceptions::ObjectNotFound->throw( 'There is no valid csv profile given') unless $csv_profile;
280 my $delimiter = $csv_profile->csv_separator;
281 $delimiter = "\t" if $delimiter eq "\\t";
282 my $csv = Text::CSV_XS->new({'quote_char'=>'"','escape_char'=>'"','sep_char'=>$delimiter,'binary'=>1});
283 my $csv_profile_content = $csv_profile->content;
284 my ( @headers, @fields );
285 while ( $csv_profile_content =~ /
288 ([^\|]*) # fieldname (table.row or row)
292 my $field = ($2 eq '') ? $1 : $2;
294 $header =~ s/^\s+|\s+$//g; # Trim whitespaces
295 push @headers, $header;
297 $field =~ s/[^\.]*\.{1}//; # Remove the table name if exists.
298 $field =~ s/^\s+|\s+$//g; # Trim whitespaces
299 push @fields, $field;
301 for my $order (@orders) {
303 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
304 my $biblioitem = $biblio->biblioitem;
305 $order = { %$order, %{ $biblioitem->unblessed } };
307 $order = {%$order, %$contract};
309 $order = {%$order, %$basket, %{ $biblio->unblessed }};
310 for my $field (@fields) {
311 push @row, $order->{$field};
315 my $content = join( $delimiter, @headers ) . "\n";
316 for my $row ( @rows ) {
317 $csv->combine(@$row);
318 my $string = $csv->string;
319 $content .= $string . "\n";
324 foreach my $order (@orders) {
325 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
328 $biblioitem = $biblio->biblioitem;
331 contractname => $contract->{'contractname'},
332 ordernumber => $order->{'ordernumber'},
333 entrydate => $order->{'entrydate'},
334 isbn => $order->{'isbn'},
335 author => $biblio ? $biblio->author : q{},
336 title => $biblio ? $biblio->title : q{},
337 publicationyear => $biblioitem ? $biblioitem->publicationyear : q{},
338 publishercode => $biblioitem ? $biblioitem->publishercode : q{},
339 collectiontitle => $biblioitem ? $biblioitem->collectiontitle : q{},
340 notes => $order->{'order_vendornote'},
341 quantity => $order->{'quantity'},
342 rrp => $order->{'rrp'},
344 for my $place ( qw( deliveryplace billingplace ) ) {
345 if ( my $library = Koha::Libraries->find( $row->{deliveryplace} ) ) {
346 $row->{$place} = $library->branchname
350 contractname author title publishercode collectiontitle notes
351 deliveryplace billingplace
353 # Double the quotes to not be interpreted as a field end
354 $row->{$_} =~ s/"/""/g if $row->{$_};
360 if(defined $a->{publishercode} and defined $b->{publishercode}) {
361 $a->{publishercode} cmp $b->{publishercode};
365 $template->param(rows => \@rows);
367 return $template->output;
372 =head3 GetBasketGroupAsCSV
374 &GetBasketGroupAsCSV($basketgroupid);
376 Export a basket group as CSV
378 $cgi parameter is needed for column name translation
382 sub GetBasketGroupAsCSV {
383 my ($basketgroupid, $cgi) = @_;
384 my $baskets = GetBasketsByBasketgroup($basketgroupid);
386 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
389 for my $basket (@$baskets) {
390 my @orders = GetOrders( $basket->{basketno} );
391 my $contract = GetContract({
392 contractnumber => $basket->{contractnumber}
394 my $bookseller = Koha::Acquisition::Booksellers->find( $basket->{booksellerid} );
395 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
397 foreach my $order (@orders) {
398 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
401 $biblioitem = $biblio->biblioitem;
404 clientnumber => $bookseller->accountnumber,
405 basketname => $basket->{basketname},
406 ordernumber => $order->{ordernumber},
407 author => $biblio ? $biblio->author : q{},
408 title => $biblio ? $biblio->title : q{},
409 publishercode => $biblioitem ? $biblioitem->publishercode : q{},
410 publicationyear => $biblioitem ? $biblioitem->publicationyear : q{},
411 collectiontitle => $biblioitem ? $biblioitem->collectiontitle : q{},
412 isbn => $order->{isbn},
413 quantity => $order->{quantity},
414 rrp_tax_included => $order->{rrp_tax_included},
415 rrp_tax_excluded => $order->{rrp_tax_excluded},
416 discount => $bookseller->discount,
417 ecost_tax_included => $order->{ecost_tax_included},
418 ecost_tax_excluded => $order->{ecost_tax_excluded},
419 notes => $order->{order_vendornote},
420 entrydate => $order->{entrydate},
421 booksellername => $bookseller->name,
422 bookselleraddress => $bookseller->address1,
423 booksellerpostal => $bookseller->postal,
424 contractnumber => $contract->{contractnumber},
425 contractname => $contract->{contractname},
428 basketgroupdeliveryplace => $basketgroup->{deliveryplace},
429 basketgroupbillingplace => $basketgroup->{billingplace},
430 basketdeliveryplace => $basket->{deliveryplace},
431 basketbillingplace => $basket->{billingplace},
433 for my $place (qw( basketgroupdeliveryplace basketgroupbillingplace basketdeliveryplace basketbillingplace )) {
434 if ( my $library = Koha::Libraries->find( $temp->{$place} ) ) {
435 $row->{$place} = $library->branchname;
439 basketname author title publishercode collectiontitle notes
440 booksellername bookselleraddress booksellerpostal contractname
441 basketgroupdeliveryplace basketgroupbillingplace
442 basketdeliveryplace basketbillingplace
444 # Double the quotes to not be interpreted as a field end
445 $row->{$_} =~ s/"/""/g if $row->{$_};
450 $template->param(rows => \@rows);
452 return $template->output;
456 =head3 CloseBasketgroup
458 &CloseBasketgroup($basketgroupno);
464 sub CloseBasketgroup {
465 my ($basketgroupno) = @_;
466 my $dbh = C4::Context->dbh;
467 my $sth = $dbh->prepare("
468 UPDATE aqbasketgroups
472 $sth->execute($basketgroupno);
475 #------------------------------------------------------------#
477 =head3 ReOpenBaskergroup($basketgroupno)
479 &ReOpenBaskergroup($basketgroupno);
485 sub ReOpenBasketgroup {
486 my ($basketgroupno) = @_;
487 my $dbh = C4::Context->dbh;
488 my $sth = $dbh->prepare("
489 UPDATE aqbasketgroups
493 $sth->execute($basketgroupno);
496 #------------------------------------------------------------#
500 &ModBasket($basketinfo);
502 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
506 =item C<$basketno> is the primary key of the basket in the aqbasket table.
513 my $basketinfo = shift;
514 my $query = "UPDATE aqbasket SET ";
516 foreach my $key (keys %$basketinfo){
517 if ($key ne 'basketno'){
518 $query .= "$key=?, ";
519 push(@params, $basketinfo->{$key} || undef );
522 # get rid of the "," at the end of $query
523 if (substr($query, length($query)-2) eq ', '){
528 $query .= "WHERE basketno=?";
529 push(@params, $basketinfo->{'basketno'});
530 my $dbh = C4::Context->dbh;
531 my $sth = $dbh->prepare($query);
532 $sth->execute(@params);
534 # Log the basket update
535 if (C4::Context->preference("AcquisitionLog")) {
536 my $modified = Koha::Acquisition::Baskets->find(
537 $basketinfo->{basketno}
542 $basketinfo->{basketno},
543 to_json($modified->unblessed)
550 #------------------------------------------------------------#
552 =head3 ModBasketHeader
554 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
556 Modifies a basket's header.
560 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
562 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
564 =item C<$note> is the "note" field in the "aqbasket" table;
566 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
568 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
570 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
572 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
574 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
576 =item C<$is_standing> is the "is_standing" field in the aqbasket table.
578 =item C<$create_items> should be set to 'ordering', 'receiving' or 'cataloguing' (or undef, in which
579 case the AcqCreateItem syspref takes precedence).
585 sub ModBasketHeader {
586 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items) = @_;
591 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?, is_standing=?, create_items=?
595 my $dbh = C4::Context->dbh;
596 my $sth = $dbh->prepare($query);
597 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items || undef, $basketno);
599 if ( $contractnumber ) {
600 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
601 my $sth2 = $dbh->prepare($query2);
602 $sth2->execute($contractnumber,$basketno);
605 # Log the basket update
606 if (C4::Context->preference("AcquisitionLog")) {
607 my $modified = Koha::Acquisition::Baskets->find(
612 'MODIFY_BASKET_HEADER',
614 to_json($modified->unblessed)
621 #------------------------------------------------------------#
623 =head3 GetBasketsByBookseller
625 @results = &GetBasketsByBookseller($booksellerid, $extra);
627 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
631 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
633 =item C<$extra> is the extra sql parameters, can be
635 $extra->{groupby}: group baskets by column
636 ex. $extra->{groupby} = aqbasket.basketgroupid
637 $extra->{orderby}: order baskets by column
638 $extra->{limit}: limit number of results (can be helpful for pagination)
644 sub GetBasketsByBookseller {
645 my ($booksellerid, $extra) = @_;
646 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
648 if ($extra->{groupby}) {
649 $query .= " GROUP by $extra->{groupby}";
651 if ($extra->{orderby}){
652 $query .= " ORDER by $extra->{orderby}";
654 if ($extra->{limit}){
655 $query .= " LIMIT $extra->{limit}";
658 my $dbh = C4::Context->dbh;
659 my $sth = $dbh->prepare($query);
660 $sth->execute($booksellerid);
661 return $sth->fetchall_arrayref({});
664 =head3 GetBasketsInfosByBookseller
666 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
668 The optional second parameter allbaskets is a boolean allowing you to
669 select all baskets from the supplier; by default only active baskets (open or
670 closed but still something to receive) are returned.
672 Returns in a arrayref of hashref all about booksellers baskets, plus:
673 total_biblios: Number of distinct biblios in basket
674 total_items: Number of items in basket
675 expected_items: Number of non-received items in basket
679 sub GetBasketsInfosByBookseller {
680 my ($supplierid, $allbaskets) = @_;
682 return unless $supplierid;
684 my $dbh = C4::Context->dbh;
686 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,
687 SUM(aqorders.quantity) AS total_items,
689 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
690 ) AS total_items_cancelled,
691 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
693 IF(aqorders.datereceived IS NULL
694 AND aqorders.datecancellationprinted IS NULL
698 SUM( aqorders.uncertainprice ) AS uncertainprices
700 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
701 WHERE booksellerid = ?};
703 $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";
705 unless ( $allbaskets ) {
706 # Don't show the basket if it's NOT CLOSED or is FULLY RECEIVED
707 $query.=" HAVING (closedate IS NULL OR (
709 IF(aqorders.datereceived IS NULL
710 AND aqorders.datecancellationprinted IS NULL
716 my $sth = $dbh->prepare($query);
717 $sth->execute($supplierid);
718 my $baskets = $sth->fetchall_arrayref({});
720 # Retrieve the number of biblios cancelled
721 my $cancelled_biblios = $dbh->selectall_hashref( q|
722 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
724 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
725 WHERE booksellerid = ?
726 AND aqorders.orderstatus = 'cancelled'
727 GROUP BY aqbasket.basketno
728 |, 'basketno', {}, $supplierid );
730 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
736 =head3 GetBasketUsers
738 $basketusers_ids = &GetBasketUsers($basketno);
740 Returns a list of all borrowernumbers that are in basket users list
745 my $basketno = shift;
747 return unless $basketno;
750 SELECT borrowernumber
754 my $dbh = C4::Context->dbh;
755 my $sth = $dbh->prepare($query);
756 $sth->execute($basketno);
757 my $results = $sth->fetchall_arrayref( {} );
760 foreach (@$results) {
761 push @borrowernumbers, $_->{'borrowernumber'};
764 return @borrowernumbers;
767 =head3 ModBasketUsers
769 my @basketusers_ids = (1, 2, 3);
770 &ModBasketUsers($basketno, @basketusers_ids);
772 Delete all users from basket users list, and add users in C<@basketusers_ids>
778 my ($basketno, @basketusers_ids) = @_;
780 return unless $basketno;
782 my $dbh = C4::Context->dbh;
784 DELETE FROM aqbasketusers
787 my $sth = $dbh->prepare($query);
788 $sth->execute($basketno);
791 INSERT INTO aqbasketusers (basketno, borrowernumber)
794 $sth = $dbh->prepare($query);
795 foreach my $basketuser_id (@basketusers_ids) {
796 $sth->execute($basketno, $basketuser_id);
799 # Log the basket update
800 if (C4::Context->preference("AcquisitionLog")) {
803 'MODIFY_BASKET_USERS',
806 basketno => $basketno,
807 basketusers => @basketusers_ids
815 =head3 CanUserManageBasket
817 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
818 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
820 Check if a borrower can manage a basket, according to system preference
821 AcqViewBaskets, user permissions and basket properties (creator, users list,
824 First parameter can be either a borrowernumber or a hashref as returned by
825 Koha::Patron->unblessed
827 Second parameter can be either a basketno or a hashref as returned by
828 C4::Acquisition::GetBasket.
830 The third parameter is optional. If given, it should be a hashref as returned
831 by C4::Auth::getuserflags. If not, getuserflags is called.
833 If user is authorised to manage basket, returns 1.
838 sub CanUserManageBasket {
839 my ($borrower, $basket, $userflags) = @_;
841 if (!ref $borrower) {
842 # FIXME This needs to be replaced
843 # We should not accept both scalar and array
844 # Tests need to be updated
845 $borrower = Koha::Patrons->find( $borrower )->unblessed;
848 $basket = GetBasket($basket);
851 return 0 unless ($basket and $borrower);
853 my $borrowernumber = $borrower->{borrowernumber};
854 my $basketno = $basket->{basketno};
856 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
858 if (!defined $userflags) {
859 my $dbh = C4::Context->dbh;
860 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
861 $sth->execute($borrowernumber);
862 my ($flags) = $sth->fetchrow_array;
865 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
868 unless ($userflags->{superlibrarian}
869 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
870 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
872 if (not exists $userflags->{acquisition}) {
876 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
877 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
881 if ($AcqViewBaskets eq 'user'
882 && $basket->{authorisedby} != $borrowernumber
883 && ! grep { $borrowernumber eq $_ } GetBasketUsers($basketno)) {
887 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
888 && $basket->{branch} ne $borrower->{branchcode}) {
896 #------------------------------------------------------------#
898 =head3 GetBasketsByBasketgroup
900 $baskets = &GetBasketsByBasketgroup($basketgroupid);
902 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
906 sub GetBasketsByBasketgroup {
907 my $basketgroupid = shift;
909 SELECT *, aqbasket.booksellerid as booksellerid
911 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
913 my $dbh = C4::Context->dbh;
914 my $sth = $dbh->prepare($query);
915 $sth->execute($basketgroupid);
916 return $sth->fetchall_arrayref({});
919 #------------------------------------------------------------#
921 =head3 NewBasketgroup
923 $basketgroupid = NewBasketgroup(\%hashref);
925 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
927 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
929 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
931 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
933 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
935 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
937 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
939 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
941 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
946 my $basketgroupinfo = shift;
947 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
948 my $query = "INSERT INTO aqbasketgroups (";
950 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
951 if ( defined $basketgroupinfo->{$field} ) {
952 $query .= "$field, ";
953 push(@params, $basketgroupinfo->{$field});
956 $query .= "booksellerid) VALUES (";
961 push(@params, $basketgroupinfo->{'booksellerid'});
962 my $dbh = C4::Context->dbh;
963 my $sth = $dbh->prepare($query);
964 $sth->execute(@params);
965 my $basketgroupid = $dbh->{'mysql_insertid'};
966 if( $basketgroupinfo->{'basketlist'} ) {
967 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
968 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
969 my $sth2 = $dbh->prepare($query2);
970 $sth2->execute($basketgroupid, $basketno);
973 return $basketgroupid;
976 #------------------------------------------------------------#
978 =head3 ModBasketgroup
980 ModBasketgroup(\%hashref);
982 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
984 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
986 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
988 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
990 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
992 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
994 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
996 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
998 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
1002 sub ModBasketgroup {
1003 my $basketgroupinfo = shift;
1004 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
1005 my $dbh = C4::Context->dbh;
1006 my $query = "UPDATE aqbasketgroups SET ";
1008 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
1009 if ( defined $basketgroupinfo->{$field} ) {
1010 $query .= "$field=?, ";
1011 push(@params, $basketgroupinfo->{$field});
1016 $query .= " WHERE id=?";
1017 push(@params, $basketgroupinfo->{'id'});
1018 my $sth = $dbh->prepare($query);
1019 $sth->execute(@params);
1021 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
1022 $sth->execute($basketgroupinfo->{'id'});
1024 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
1025 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
1026 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
1027 $sth->execute($basketgroupinfo->{'id'}, $basketno);
1033 #------------------------------------------------------------#
1035 =head3 DelBasketgroup
1037 DelBasketgroup($basketgroupid);
1039 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
1043 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
1049 sub DelBasketgroup {
1050 my $basketgroupid = shift;
1051 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1052 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
1053 my $dbh = C4::Context->dbh;
1054 my $sth = $dbh->prepare($query);
1055 $sth->execute($basketgroupid);
1059 #------------------------------------------------------------#
1062 =head2 FUNCTIONS ABOUT ORDERS
1064 =head3 GetBasketgroup
1066 $basketgroup = &GetBasketgroup($basketgroupid);
1068 Returns a reference to the hash containing all information about the basketgroup.
1072 sub GetBasketgroup {
1073 my $basketgroupid = shift;
1074 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1075 my $dbh = C4::Context->dbh;
1076 my $result_set = $dbh->selectall_arrayref(
1077 'SELECT * FROM aqbasketgroups WHERE id=?',
1081 return $result_set->[0]; # id is unique
1084 #------------------------------------------------------------#
1086 =head3 GetBasketgroups
1088 $basketgroups = &GetBasketgroups($booksellerid);
1090 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1094 sub GetBasketgroups {
1095 my $booksellerid = shift;
1096 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1097 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1098 my $dbh = C4::Context->dbh;
1099 my $sth = $dbh->prepare($query);
1100 $sth->execute($booksellerid);
1101 return $sth->fetchall_arrayref({});
1104 #------------------------------------------------------------#
1106 =head2 FUNCTIONS ABOUT ORDERS
1110 @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } );
1112 Looks up the pending (non-cancelled) orders with the given basket
1115 If cancelled is set, only cancelled orders will be returned.
1120 my ( $basketno, $params ) = @_;
1122 return () unless $basketno;
1124 my $orderby = $params->{orderby};
1125 my $cancelled = $params->{cancelled} || 0;
1127 my $dbh = C4::Context->dbh;
1129 SELECT biblio.*,biblioitems.*,
1133 $query .= $cancelled
1135 aqorders_transfers.ordernumber_to AS transferred_to,
1136 aqorders_transfers.timestamp AS transferred_to_timestamp
1139 aqorders_transfers.ordernumber_from AS transferred_from,
1140 aqorders_transfers.timestamp AS transferred_from_timestamp
1144 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1145 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1146 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1148 $query .= $cancelled
1150 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1153 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1161 $orderby ||= q|biblioitems.publishercode, biblio.title|;
1163 AND datecancellationprinted IS NOT NULL
1168 q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|;
1170 AND datecancellationprinted IS NULL
1174 $query .= " ORDER BY $orderby";
1176 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1181 #------------------------------------------------------------#
1183 =head3 GetOrdersByBiblionumber
1185 @orders = &GetOrdersByBiblionumber($biblionumber);
1187 Looks up the orders with linked to a specific $biblionumber, including
1188 cancelled orders and received orders.
1191 C<@orders> is an array of references-to-hash, whose keys are the
1192 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1196 sub GetOrdersByBiblionumber {
1197 my $biblionumber = shift;
1198 return unless $biblionumber;
1199 my $dbh = C4::Context->dbh;
1201 SELECT biblio.*,biblioitems.*,
1205 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1206 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1207 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1208 WHERE aqorders.biblionumber=?
1211 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1212 return @{$result_set};
1216 #------------------------------------------------------------#
1220 $order = &GetOrder($ordernumber);
1222 Looks up an order by order number.
1224 Returns a reference-to-hash describing the order. The keys of
1225 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1230 my ($ordernumber) = @_;
1231 return unless $ordernumber;
1233 my $dbh = C4::Context->dbh;
1234 my $query = qq{SELECT
1238 aqbasket.basketname,
1239 borrowers.branchcode,
1240 biblioitems.publicationyear,
1241 biblio.copyrightdate,
1242 biblioitems.editionstatement,
1246 biblioitems.publishercode,
1247 aqorders.rrp AS unitpricesupplier,
1248 aqorders.ecost AS unitpricelib,
1249 aqbudgets.budget_name AS budget,
1250 aqbooksellers.name AS supplier,
1251 aqbooksellers.id AS supplierid,
1252 biblioitems.publishercode AS publisher,
1253 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS calculateddeliverydate,
1254 DATE(aqbasket.closedate) AS orderdate,
1255 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1256 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1257 DATEDIFF(CURDATE( ),closedate) AS latesince
1258 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1259 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1260 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1261 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1262 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1263 WHERE aqorders.basketno = aqbasket.basketno
1266 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1268 # result_set assumed to contain 1 match
1269 return $result_set->[0];
1274 &ModOrder(\%hashref);
1276 Modifies an existing order. Updates the order with order number
1277 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1278 other keys of the hash update the fields with the same name in the aqorders
1279 table of the Koha database.
1284 my $orderinfo = shift;
1286 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '';
1288 my $dbh = C4::Context->dbh;
1291 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1292 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1294 # delete($orderinfo->{'branchcode'});
1295 # the hash contains a lot of entries not in aqorders, so get the columns ...
1296 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1298 my $colnames = $sth->{NAME};
1299 #FIXME Be careful. If aqorders would have columns with diacritics,
1300 #you should need to decode what you get back from NAME.
1301 #See report 10110 and guided_reports.pl
1302 my $query = "UPDATE aqorders SET ";
1304 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1305 # ... and skip hash entries that are not in the aqorders table
1306 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1307 next unless grep { $_ eq $orderinfokey } @$colnames;
1308 $query .= "$orderinfokey=?, ";
1309 push(@params, $orderinfo->{$orderinfokey});
1312 $query .= "timestamp=NOW() WHERE ordernumber=?";
1313 push(@params, $orderinfo->{'ordernumber'} );
1314 $sth = $dbh->prepare($query);
1315 $sth->execute(@params);
1319 #------------------------------------------------------------#
1323 ModItemOrder($itemnumber, $ordernumber);
1325 Modifies the ordernumber of an item in aqorders_items.
1330 my ($itemnumber, $ordernumber) = @_;
1332 return unless ($itemnumber and $ordernumber);
1334 my $dbh = C4::Context->dbh;
1336 UPDATE aqorders_items
1338 WHERE itemnumber = ?
1340 my $sth = $dbh->prepare($query);
1341 return $sth->execute($ordernumber, $itemnumber);
1344 #------------------------------------------------------------#
1346 =head3 ModReceiveOrder
1348 my ( $date_received, $new_ordernumber ) = ModReceiveOrder(
1350 biblionumber => $biblionumber,
1352 quantityreceived => $quantityreceived,
1354 invoice => $invoice,
1355 budget_id => $budget_id,
1356 datereceived => $datereceived,
1357 received_itemnumbers => \@received_itemnumbers,
1361 Updates an order, to reflect the fact that it was received, at least
1364 If a partial order is received, splits the order into two.
1366 Updates the order with biblionumber C<$biblionumber> and ordernumber
1367 C<$order->{ordernumber}>.
1372 sub ModReceiveOrder {
1374 my $biblionumber = $params->{biblionumber};
1375 my $order = { %{ $params->{order} } }; # Copy the order, we don't want to modify it
1376 my $invoice = $params->{invoice};
1377 my $quantrec = $params->{quantityreceived};
1378 my $user = $params->{user};
1379 my $budget_id = $params->{budget_id};
1380 my $datereceived = $params->{datereceived};
1381 my $received_items = $params->{received_items};
1383 my $dbh = C4::Context->dbh;
1384 $datereceived = $datereceived ? dt_from_string( $datereceived ) : dt_from_string;
1385 $datereceived = $datereceived->ymd;
1387 $order->{invoice_unitprice} ||= $order->{unitprice};
1388 $order->{invoice_currency} ||= Koha::Acquisition::Currencies->get_active->currency;
1390 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1391 if ($suggestionid) {
1392 ModSuggestion( {suggestionid=>$suggestionid,
1393 STATUS=>'AVAILABLE',
1394 biblionumber=> $biblionumber}
1398 my $result_set = $dbh->selectrow_arrayref(
1399 q{SELECT aqbasket.is_standing
1401 WHERE basketno=?},{ Slice => {} }, $order->{basketno});
1402 my $is_standing = $result_set->[0]; # we assume we have a unique basket
1404 my $new_ordernumber = $order->{ordernumber};
1405 if ( $is_standing || $order->{quantity} > $quantrec ) {
1406 # Split order line in two parts: the first is the original order line
1407 # without received items (the quantity is decreased),
1408 # the second part is a new order line with quantity=quantityrec
1409 # (entirely received)
1413 orderstatus = 'partial'|;
1414 $query .= q| WHERE ordernumber = ?|;
1415 my $sth = $dbh->prepare($query);
1418 ( $is_standing ? 1 : ($order->{quantity} - $quantrec) ),
1419 $order->{ordernumber}
1422 # Recalculate tax_value
1426 tax_value_on_ordering = quantity * | . get_rounding_sql(q|ecost_tax_excluded|) . q| * tax_rate_on_ordering,
1427 tax_value_on_receiving = quantity * | . get_rounding_sql(q|unitprice_tax_excluded|) . q| * tax_rate_on_receiving
1431 if ( not $order->{subscriptionid} && defined $order->{order_internalnote} )
1433 $query .= q|, order_internalnote = ?|;
1434 push @params, $order->{order_internalnote};
1437 $query .= q|, invoice_unitprice = ?, invoice_currency = ?|;
1438 push @params, $order->{invoice_unitprice}, $order->{invoice_currency};
1441 WHERE ordernumber = ?
1444 $dbh->do($query, undef, @params, $order->{ordernumber});
1446 delete $order->{ordernumber};
1447 $order->{budget_id} = ( $budget_id || $order->{budget_id} );
1448 $order->{quantity} = $quantrec;
1449 $order->{quantityreceived} = $quantrec;
1450 $order->{ecost_tax_excluded} //= 0;
1451 $order->{tax_rate_on_ordering} //= 0;
1452 $order->{unitprice_tax_excluded} //= 0;
1453 $order->{tax_rate_on_receiving} //= 0;
1454 $order->{tax_value_on_ordering} = $order->{quantity} * get_rounded_price($order->{ecost_tax_excluded}) * $order->{tax_rate_on_ordering};
1455 $order->{tax_value_on_receiving} = $order->{quantity} * get_rounded_price($order->{unitprice_tax_excluded}) * $order->{tax_rate_on_receiving};
1456 $order->{datereceived} = $datereceived;
1457 $order->{invoiceid} = $invoice->{invoiceid};
1458 $order->{orderstatus} = 'complete';
1459 $new_ordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber; # TODO What if the store fails?
1461 if ($received_items) {
1462 foreach my $itemnumber (@$received_items) {
1463 ModItemOrder($itemnumber, $new_ordernumber);
1469 SET quantityreceived = ?,
1473 orderstatus = 'complete'
1477 , replacementprice = ?
1478 | if defined $order->{replacementprice};
1481 , unitprice = ?, unitprice_tax_included = ?, unitprice_tax_excluded = ?
1482 | if defined $order->{unitprice};
1485 ,tax_value_on_receiving = ?
1486 | if defined $order->{tax_value_on_receiving};
1489 ,tax_rate_on_receiving = ?
1490 | if defined $order->{tax_rate_on_receiving};
1493 , order_internalnote = ?
1494 | if defined $order->{order_internalnote};
1497 , invoice_unitprice = ?, invoice_currency = ?
1500 $query .= q| where biblionumber=? and ordernumber=?|;
1502 my $sth = $dbh->prepare( $query );
1503 my @params = ( $quantrec, $datereceived, $invoice->{invoiceid}, ( $budget_id ? $budget_id : $order->{budget_id} ) );
1505 if ( defined $order->{replacementprice} ) {
1506 push @params, $order->{replacementprice};
1509 if ( defined $order->{unitprice} ) {
1510 push @params, $order->{unitprice}, $order->{unitprice_tax_included}, $order->{unitprice_tax_excluded};
1513 if ( defined $order->{tax_value_on_receiving} ) {
1514 push @params, $order->{tax_value_on_receiving};
1517 if ( defined $order->{tax_rate_on_receiving} ) {
1518 push @params, $order->{tax_rate_on_receiving};
1521 if ( defined $order->{order_internalnote} ) {
1522 push @params, $order->{order_internalnote};
1525 push @params, $order->{invoice_unitprice}, $order->{invoice_currency};
1527 push @params, ( $biblionumber, $order->{ordernumber} );
1529 $sth->execute( @params );
1531 # All items have been received, sent a notification to users
1532 NotifyOrderUsers( $order->{ordernumber} );
1535 return ($datereceived, $new_ordernumber);
1538 =head3 CancelReceipt
1540 my $parent_ordernumber = CancelReceipt($ordernumber);
1542 Cancel an order line receipt and update the parent order line, as if no
1544 If items are created at receipt (AcqCreateItem = receiving) then delete
1550 my $ordernumber = shift;
1552 return unless $ordernumber;
1554 my $dbh = C4::Context->dbh;
1556 SELECT datereceived, parent_ordernumber, quantity
1558 WHERE ordernumber = ?
1560 my $sth = $dbh->prepare($query);
1561 $sth->execute($ordernumber);
1562 my $order = $sth->fetchrow_hashref;
1564 warn "CancelReceipt: order $ordernumber does not exist";
1567 unless($order->{'datereceived'}) {
1568 warn "CancelReceipt: order $ordernumber is not received";
1572 my $parent_ordernumber = $order->{'parent_ordernumber'};
1574 my $order_obj = Koha::Acquisition::Orders->find( $ordernumber ); # FIXME rewrite all this subroutine using this object
1575 my @itemnumbers = $order_obj->items->get_column('itemnumber');
1577 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1578 # The order line has no parent, just mark it as not received
1581 SET quantityreceived = ?,
1584 orderstatus = 'ordered'
1585 WHERE ordernumber = ?
1587 $sth = $dbh->prepare($query);
1588 $sth->execute(0, undef, undef, $ordernumber);
1589 _cancel_items_receipt( $order_obj );
1591 # The order line has a parent, increase parent quantity and delete
1593 unless ( $order_obj->basket->is_standing ) {
1595 SELECT quantity, datereceived
1597 WHERE ordernumber = ?
1599 $sth = $dbh->prepare($query);
1600 $sth->execute($parent_ordernumber);
1601 my $parent_order = $sth->fetchrow_hashref;
1602 unless($parent_order) {
1603 warn "Parent order $parent_ordernumber does not exist.";
1606 if($parent_order->{'datereceived'}) {
1607 warn "CancelReceipt: parent order is received.".
1608 " Can't cancel receipt.";
1614 orderstatus = 'ordered'
1615 WHERE ordernumber = ?
1617 $sth = $dbh->prepare($query);
1618 my $rv = $sth->execute(
1619 $order->{'quantity'} + $parent_order->{'quantity'},
1623 warn "Cannot update parent order line, so do not cancel".
1628 # Recalculate tax_value
1632 tax_value_on_ordering = quantity * | . get_rounding_sql(q|ecost_tax_excluded|) . q| * tax_rate_on_ordering,
1633 tax_value_on_receiving = quantity * | . get_rounding_sql(q|unitprice_tax_excluded|) . q| * tax_rate_on_receiving
1634 WHERE ordernumber = ?
1635 |, undef, $parent_ordernumber);
1638 _cancel_items_receipt( $order_obj, $parent_ordernumber );
1641 DELETE FROM aqorders
1642 WHERE ordernumber = ?
1644 $sth = $dbh->prepare($query);
1645 $sth->execute($ordernumber);
1649 if( $order_obj->basket->effective_create_items eq 'ordering' ) {
1650 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1652 for my $in ( @itemnumbers ) {
1653 my $item = Koha::Items->find( $in ); # FIXME We do not need that, we already have Koha::Items from $order_obj->items
1654 my $biblio = $item->biblio;
1655 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber' );
1656 my $item_marc = C4::Items::GetMarcItem( $biblio->biblionumber, $in );
1657 for my $affect ( @affects ) {
1658 my ( $sf, $v ) = split q{=}, $affect, 2;
1659 foreach ( $item_marc->field($itemfield) ) {
1660 $_->update( $sf => $v );
1663 C4::Items::ModItemFromMarc( $item_marc, $biblio->biblionumber, $in );
1668 return $parent_ordernumber;
1671 sub _cancel_items_receipt {
1672 my ( $order, $parent_ordernumber ) = @_;
1673 $parent_ordernumber ||= $order->ordernumber;
1675 my $items = $order->items;
1676 if ( $order->basket->effective_create_items eq 'receiving' ) {
1677 # Remove items that were created at receipt
1679 DELETE FROM items, aqorders_items
1680 USING items, aqorders_items
1681 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1683 my $dbh = C4::Context->dbh;
1684 my $sth = $dbh->prepare($query);
1685 while ( my $item = $items->next ) {
1686 $sth->execute($item->itemnumber, $item->itemnumber);
1690 while ( my $item = $items->next ) {
1691 ModItemOrder($item->itemnumber, $parent_ordernumber);
1696 #------------------------------------------------------------#
1700 @results = &SearchOrders({
1701 ordernumber => $ordernumber,
1704 booksellerid => $booksellerid,
1705 basketno => $basketno,
1706 basketname => $basketname,
1707 basketgroupname => $basketgroupname,
1711 biblionumber => $biblionumber,
1712 budget_id => $budget_id
1715 Searches for orders filtered by criteria.
1717 C<$ordernumber> Finds matching orders or transferred orders by ordernumber.
1718 C<$search> Finds orders matching %$search% in title, author, or isbn.
1719 C<$owner> Finds order for the logged in user.
1720 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1721 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1724 C<@results> is an array of references-to-hash with the keys are fields
1725 from aqorders, biblio, biblioitems and aqbasket tables.
1730 my ( $params ) = @_;
1731 my $ordernumber = $params->{ordernumber};
1732 my $search = $params->{search};
1733 my $ean = $params->{ean};
1734 my $booksellerid = $params->{booksellerid};
1735 my $basketno = $params->{basketno};
1736 my $basketname = $params->{basketname};
1737 my $basketgroupname = $params->{basketgroupname};
1738 my $owner = $params->{owner};
1739 my $pending = $params->{pending};
1740 my $ordered = $params->{ordered};
1741 my $biblionumber = $params->{biblionumber};
1742 my $budget_id = $params->{budget_id};
1744 my $dbh = C4::Context->dbh;
1747 SELECT aqbasket.basketno,
1749 borrowers.firstname,
1752 biblioitems.biblioitemnumber,
1753 biblioitems.publishercode,
1754 biblioitems.publicationyear,
1755 aqbasket.authorisedby,
1756 aqbasket.booksellerid,
1758 aqbasket.creationdate,
1759 aqbasket.basketname,
1760 aqbasketgroups.id as basketgroupid,
1761 aqbasketgroups.name as basketgroupname,
1764 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1765 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1766 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1767 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1768 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1771 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1773 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1777 WHERE (datecancellationprinted is NULL)
1780 if ( $pending or $ordered ) {
1783 ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) )
1785 ( quantity > quantityreceived OR quantityreceived is NULL )
1789 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1797 my $userenv = C4::Context->userenv;
1798 if ( C4::Context->preference("IndependentBranches") ) {
1799 unless ( C4::Context->IsSuperLibrarian() ) {
1802 borrowers.branchcode = ?
1803 OR borrowers.branchcode = ''
1806 push @args, $userenv->{branch};
1810 if ( $ordernumber ) {
1811 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1812 push @args, ( $ordernumber, $ordernumber );
1814 if ( $biblionumber ) {
1815 $query .= 'AND aqorders.biblionumber = ?';
1816 push @args, $biblionumber;
1819 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1820 push @args, ("%$search%","%$search%","%$search%");
1823 $query .= ' AND biblioitems.ean = ?';
1826 if ( $booksellerid ) {
1827 $query .= 'AND aqbasket.booksellerid = ?';
1828 push @args, $booksellerid;
1831 $query .= 'AND aqbasket.basketno = ?';
1832 push @args, $basketno;
1835 $query .= 'AND aqbasket.basketname LIKE ?';
1836 push @args, "%$basketname%";
1838 if( $basketgroupname ) {
1839 $query .= ' AND aqbasketgroups.name LIKE ?';
1840 push @args, "%$basketgroupname%";
1844 $query .= ' AND aqbasket.authorisedby=? ';
1845 push @args, $userenv->{'number'};
1849 $query .= ' AND aqorders.budget_id = ?';
1850 push @args, $budget_id;
1853 $query .= ' ORDER BY aqbasket.basketno';
1855 my $sth = $dbh->prepare($query);
1856 $sth->execute(@args);
1857 return $sth->fetchall_arrayref({});
1860 #------------------------------------------------------------#
1862 =head3 TransferOrder
1864 my $newordernumber = TransferOrder($ordernumber, $basketno);
1866 Transfer an order line to a basket.
1867 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1868 to BOOKSELLER on DATE' and create new order with internal note
1869 'Transferred from BOOKSELLER on DATE'.
1870 Move all attached items to the new order.
1871 Received orders cannot be transferred.
1872 Return the ordernumber of created order.
1877 my ($ordernumber, $basketno) = @_;
1879 return unless ($ordernumber and $basketno);
1881 my $order = Koha::Acquisition::Orders->find( $ordernumber ) or return;
1882 return if $order->datereceived;
1884 my $orderhash = $order->unblessed;
1886 my $basket = GetBasket($basketno);
1887 return unless $basket;
1889 my $dbh = C4::Context->dbh;
1890 my ($query, $sth, $rv);
1894 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1895 WHERE ordernumber = ?
1897 $sth = $dbh->prepare($query);
1898 $rv = $sth->execute('cancelled', $ordernumber);
1900 delete $orderhash->{ordernumber};
1901 delete $orderhash->{parent_ordernumber};
1902 $orderhash->{basketno} = $basketno;
1904 my $neworder = Koha::Acquisition::Order->new($orderhash);
1905 my $newordernumber = $neworder->store->ordernumber;
1908 UPDATE aqorders_items
1910 WHERE ordernumber = ?
1912 $sth = $dbh->prepare($query);
1913 $sth->execute($newordernumber, $ordernumber);
1916 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1919 $sth = $dbh->prepare($query);
1920 $sth->execute($ordernumber, $newordernumber);
1922 # Copy additional fields values
1923 foreach my $afv ($order->additional_field_values->as_list) {
1924 Koha::AdditionalFieldValue->new({
1925 field_id => $afv->field_id,
1926 record_id => $newordernumber,
1927 value => $afv->value,
1931 return $newordernumber;
1934 =head3 get_rounding_sql
1936 $rounding_sql = get_rounding_sql($column_name);
1938 returns the correct SQL routine based on OrderPriceRounding system preference.
1942 sub get_rounding_sql {
1943 my ( $round_string ) = @_;
1944 my $rounding_pref = C4::Context->preference('OrderPriceRounding') // q{};
1945 if ( $rounding_pref eq "nearest_cent" ) {
1946 return "CAST($round_string*100 AS SIGNED)/100";
1948 return $round_string;
1951 =head3 get_rounded_price
1953 $rounded_price = get_rounded_price( $price );
1955 returns a price rounded as specified in OrderPriceRounding system preference.
1959 sub get_rounded_price {
1961 my $rounding_pref = C4::Context->preference('OrderPriceRounding') // q{};
1962 if( $rounding_pref eq 'nearest_cent' ) {
1963 return Koha::Number::Price->new( $price )->round();
1969 =head2 FUNCTIONS ABOUT PARCELS
1973 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1975 get a lists of parcels.
1982 is the bookseller this function has to get parcels.
1985 To know on what criteria the results list has to be ordered.
1988 is the booksellerinvoicenumber.
1990 =item $datefrom & $dateto
1991 to know on what date this function has to filter its search.
1996 a pointer on a hash list containing parcel informations as such :
2002 =item Last operation
2004 =item Number of biblio
2006 =item Number of items
2013 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
2014 my $dbh = C4::Context->dbh;
2015 my @query_params = ();
2017 SELECT aqinvoices.invoicenumber,
2018 datereceived,purchaseordernumber,
2019 count(DISTINCT biblionumber) AS biblio,
2020 sum(quantity) AS itemsexpected,
2021 sum(quantityreceived) AS itemsreceived
2022 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
2023 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2024 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
2026 push @query_params, $bookseller;
2028 if ( defined $code ) {
2029 $strsth .= ' and aqinvoices.invoicenumber like ? ';
2030 # add a % to the end of the code to allow stemming.
2031 push @query_params, "$code%";
2034 if ( defined $datefrom ) {
2035 $strsth .= ' and datereceived >= ? ';
2036 push @query_params, $datefrom;
2039 if ( defined $dateto ) {
2040 $strsth .= 'and datereceived <= ? ';
2041 push @query_params, $dateto;
2044 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
2046 # can't use a placeholder to place this column name.
2047 # but, we could probably be checking to make sure it is a column that will be fetched.
2048 $strsth .= "order by $order " if ($order);
2050 my $sth = $dbh->prepare($strsth);
2052 $sth->execute( @query_params );
2053 my $results = $sth->fetchall_arrayref({});
2057 #------------------------------------------------------------#
2061 \@order_loop = GetHistory( %params );
2063 Retreives some acquisition history information
2073 basket - search both basket name and number
2074 booksellerinvoicenumber
2077 orderstatus (note that orderstatus '' will retrieve orders
2078 of any status except cancelled)
2082 get_canceled_order (if set to a true value, cancelled orders will
2086 $order_loop is a list of hashrefs that each look like this:
2088 'author' => 'Twain, Mark',
2090 'biblionumber' => '215',
2092 'creationdate' => 'MM/DD/YYYY',
2093 'datereceived' => undef,
2096 'invoicenumber' => undef,
2098 'ordernumber' => '1',
2100 'quantityreceived' => undef,
2101 'title' => 'The Adventures of Huckleberry Finn',
2102 'managing_library' => 'CPL'
2103 'is_standing' => '1'
2109 # don't run the query if there are no parameters (list would be too long for sure !)
2110 croak "No search params" unless @_;
2112 my $title = $params{title};
2113 my $author = $params{author};
2114 my $isbn = $params{isbn};
2115 my $issn = $params{issn};
2116 my $ean = $params{ean};
2117 my $name = $params{name};
2118 my $internalnote = $params{internalnote};
2119 my $vendornote = $params{vendornote};
2120 my $from_placed_on = $params{from_placed_on};
2121 my $to_placed_on = $params{to_placed_on};
2122 my $basket = $params{basket};
2123 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2124 my $basketgroupname = $params{basketgroupname};
2125 my $budget = $params{budget};
2126 my $orderstatus = $params{orderstatus};
2127 my $is_standing = $params{is_standing};
2128 my $biblionumber = $params{biblionumber};
2129 my $get_canceled_order = $params{get_canceled_order} || 0;
2130 my $ordernumber = $params{ordernumber};
2131 my $search_children_too = $params{search_children_too} || 0;
2132 my $created_by = $params{created_by} || [];
2133 my $managing_library = $params{managing_library};
2134 my $ordernumbers = $params{ordernumbers} || [];
2135 my $additional_fields = $params{additional_fields} // [];
2138 my $total_qtyreceived = 0;
2139 my $total_price = 0;
2141 #get variation of isbn
2145 if ( C4::Context->preference("SearchWithISBNVariations") ){
2146 @isbns = C4::Koha::GetVariationsOfISBN( $isbn );
2147 foreach my $isb (@isbns){
2148 push @isbn_params, '?';
2153 push @isbn_params, '?';
2157 #get variation of issn
2161 if ( C4::Context->preference("SearchWithISSNVariations") ){
2162 @issns = C4::Koha::GetVariationsOfISSN( $issn );
2163 push @issn_params, ('?') x @issns;
2167 push @issn_params, '?';
2171 my $dbh = C4::Context->dbh;
2174 COALESCE(biblio.title, deletedbiblio.title) AS title,
2175 COALESCE(biblio.author, deletedbiblio.author) AS author,
2176 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2177 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2179 aqbasket.basketname,
2180 aqbasket.basketgroupid,
2181 aqbasket.authorisedby,
2182 aqbasket.is_standing,
2183 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2184 branch as managing_library,
2185 aqbasketgroups.name as groupname,
2187 aqbasket.creationdate,
2188 aqorders.datereceived,
2190 aqorders.quantityreceived,
2192 aqorders.ordernumber,
2194 aqinvoices.invoicenumber,
2195 aqbooksellers.id as id,
2196 aqorders.biblionumber,
2197 aqorders.orderstatus,
2198 aqorders.parent_ordernumber,
2199 aqorders.order_internalnote,
2200 aqorders.order_vendornote,
2201 aqbudgets.budget_name
2203 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2206 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2207 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2208 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2209 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2210 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2211 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2212 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2213 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2214 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2215 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2218 $query .= " WHERE 1 ";
2220 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2221 $query .= " AND datecancellationprinted IS NULL ";
2224 my @query_params = ();
2226 if ( $biblionumber ) {
2227 $query .= " AND biblio.biblionumber = ?";
2228 push @query_params, $biblionumber;
2232 $query .= " AND biblio.title LIKE ? ";
2233 $title =~ s/\s+/%/g;
2234 push @query_params, "%$title%";
2238 $query .= " AND biblio.author LIKE ? ";
2239 push @query_params, "%$author%";
2243 $query .= " AND ( biblioitems.isbn LIKE " . join (" OR biblioitems.isbn LIKE ", @isbn_params ) . ")";
2244 foreach my $isb (@isbns){
2245 push @query_params, "%$isb%";
2250 $query .= " AND ( biblioitems.issn LIKE " . join (" OR biblioitems.issn LIKE ", @issn_params ) . ")";
2251 foreach my $isn (@issns){
2252 push @query_params, "%$isn%";
2257 $query .= " AND biblioitems.ean = ? ";
2258 push @query_params, "$ean";
2261 $query .= " AND aqbooksellers.name LIKE ? ";
2262 push @query_params, "%$name%";
2266 $query .= " AND aqbudgets.budget_id = ? ";
2267 push @query_params, "$budget";
2270 if ( $from_placed_on ) {
2271 $query .= " AND creationdate >= ? ";
2272 push @query_params, $from_placed_on;
2275 if ( $to_placed_on ) {
2276 $query .= " AND creationdate <= ? ";
2277 push @query_params, $to_placed_on;
2280 if ( defined $orderstatus and $orderstatus ne '' and $orderstatus ne 'any') {
2281 $query .= " AND aqorders.orderstatus = ? ";
2282 push @query_params, "$orderstatus";
2285 if ( $is_standing ) {
2286 $query .= " AND is_standing = ? ";
2287 push @query_params, $is_standing;
2291 if ($basket =~ m/^\d+$/) {
2292 $query .= " AND aqorders.basketno = ? ";
2293 push @query_params, $basket;
2295 $query .= " AND aqbasket.basketname LIKE ? ";
2296 push @query_params, "%$basket%";
2300 if ( $internalnote ) {
2301 $query .= " AND aqorders.order_internalnote LIKE ? ";
2302 push @query_params, "%$internalnote%";
2305 if ( $vendornote ) {
2306 $query .= " AND aqorders.order_vendornote LIKE ?";
2307 push @query_params, "%$vendornote%";
2310 if ($booksellerinvoicenumber) {
2311 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2312 push @query_params, "%$booksellerinvoicenumber%";
2315 if ($basketgroupname) {
2316 $query .= " AND aqbasketgroups.name LIKE ? ";
2317 push @query_params, "%$basketgroupname%";
2321 $query .= " AND (aqorders.ordernumber = ? ";
2322 push @query_params, $ordernumber;
2323 if ($search_children_too) {
2324 $query .= " OR aqorders.parent_ordernumber = ? ";
2325 push @query_params, $ordernumber;
2330 if ( @$created_by ) {
2331 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2332 push @query_params, @$created_by;
2335 if ( $managing_library ) {
2336 $query .= " AND aqbasket.branch = ? ";
2337 push @query_params, $managing_library;
2340 if ( @$ordernumbers ) {
2341 $query .= ' AND (aqorders.ordernumber IN ( ' . join (',', ('?') x @$ordernumbers ) . '))';
2342 push @query_params, @$ordernumbers;
2344 if ( @$additional_fields ) {
2345 my @baskets = Koha::Acquisition::Baskets->filter_by_additional_fields($additional_fields)->as_list;
2347 return [] unless @baskets;
2349 # No parameterization because record IDs come directly from DB
2350 $query .= ' AND aqbasket.basketno IN ( ' . join( ',', map { $_->basketno } @baskets ) . ' )';
2353 if ( C4::Context->preference("IndependentBranches") ) {
2354 unless ( C4::Context->IsSuperLibrarian() ) {
2355 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2356 push @query_params, C4::Context->userenv->{branch};
2359 $query .= " ORDER BY id";
2361 return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2364 =head2 GetRecentAcqui
2366 $results = GetRecentAcqui($days);
2368 C<$results> is a ref to a table which contains hashref
2372 sub GetRecentAcqui {
2374 my $dbh = C4::Context->dbh;
2378 ORDER BY timestamp DESC
2381 my $sth = $dbh->prepare($query);
2383 my $results = $sth->fetchall_arrayref({});
2387 #------------------------------------------------------------#
2391 &AddClaim($ordernumber);
2393 Add a claim for an order
2398 my ($ordernumber) = @_;
2399 my $dbh = C4::Context->dbh;
2402 claims_count = claims_count + 1,
2403 claimed_date = CURDATE()
2404 WHERE ordernumber = ?
2406 my $sth = $dbh->prepare($query);
2407 $sth->execute($ordernumber);
2412 my @invoices = GetInvoices(
2413 invoicenumber => $invoicenumber,
2414 supplierid => $supplierid,
2415 suppliername => $suppliername,
2416 shipmentdatefrom => $shipmentdatefrom, # ISO format
2417 shipmentdateto => $shipmentdateto, # ISO format
2418 billingdatefrom => $billingdatefrom, # ISO format
2419 billingdateto => $billingdateto, # ISO format
2420 isbneanissn => $isbn_or_ean_or_issn,
2423 publisher => $publisher,
2424 publicationyear => $publicationyear,
2425 branchcode => $branchcode,
2426 order_by => $order_by
2429 Return a list of invoices that match all given criteria.
2431 $order_by is "column_name (asc|desc)", where column_name is any of
2432 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2433 'shipmentcost', 'shipmentcost_budgetid'.
2435 asc is the default if omitted
2442 my $additional_fields = $args{additional_fields} // [];
2443 my $matching_invoice_ids_for_additional_fields = [];
2444 if ( @$additional_fields ) {
2445 my @invoices = Koha::Acquisition::Invoices->filter_by_additional_fields($additional_fields)->as_list;
2447 return () unless @invoices;
2449 $matching_invoice_ids_for_additional_fields = [ map {
2454 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2455 closedate shipmentcost shipmentcost_budgetid);
2457 my $dbh = C4::Context->dbh;
2459 SELECT aqinvoices.invoiceid, aqinvoices.invoicenumber, aqinvoices.booksellerid, aqinvoices.shipmentdate, aqinvoices.billingdate, aqinvoices.closedate, aqinvoices.shipmentcost, aqinvoices.shipmentcost_budgetid, aqinvoices.message_id,
2460 aqbooksellers.name AS suppliername,
2463 aqorders.datereceived IS NOT NULL,
2464 aqorders.biblionumber,
2467 ) AS receivedbiblios,
2470 aqorders.subscriptionid IS NOT NULL,
2471 aqorders.subscriptionid,
2474 ) AS is_linked_to_subscriptions,
2475 SUM(aqorders.quantityreceived) AS receiveditems
2477 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2478 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2479 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2480 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2481 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2482 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2483 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2488 if($args{supplierid}) {
2489 push @bind_strs, " aqinvoices.booksellerid = ? ";
2490 push @bind_args, $args{supplierid};
2492 if($args{invoicenumber}) {
2493 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2494 push @bind_args, "%$args{invoicenumber}%";
2496 if($args{suppliername}) {
2497 push @bind_strs, " aqbooksellers.name LIKE ? ";
2498 push @bind_args, "%$args{suppliername}%";
2500 if($args{shipmentdatefrom}) {
2501 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2502 push @bind_args, $args{shipmentdatefrom};
2504 if($args{shipmentdateto}) {
2505 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2506 push @bind_args, $args{shipmentdateto};
2508 if($args{billingdatefrom}) {
2509 push @bind_strs, " aqinvoices.billingdate >= ? ";
2510 push @bind_args, $args{billingdatefrom};
2512 if($args{billingdateto}) {
2513 push @bind_strs, " aqinvoices.billingdate <= ? ";
2514 push @bind_args, $args{billingdateto};
2516 if($args{isbneanissn}) {
2517 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2518 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2521 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2522 push @bind_args, $args{title};
2525 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2526 push @bind_args, $args{author};
2528 if($args{publisher}) {
2529 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2530 push @bind_args, $args{publisher};
2532 if($args{publicationyear}) {
2533 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2534 push @bind_args, $args{publicationyear}, $args{publicationyear};
2536 if($args{branchcode}) {
2537 push @bind_strs, " borrowers.branchcode = ? ";
2538 push @bind_args, $args{branchcode};
2540 if($args{message_id}) {
2541 push @bind_strs, " aqinvoices.message_id = ? ";
2542 push @bind_args, $args{message_id};
2545 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2547 # Handle additional fields filtering
2548 if ( @$additional_fields ) {
2549 my $operator = ' WHERE';
2550 if ( @bind_strs ) { # there's a WHERE already
2553 $query .= "$operator aqinvoices.invoiceid IN ("
2554 . join( ', ', @$matching_invoice_ids_for_additional_fields )
2558 $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";
2560 if($args{order_by}) {
2561 my ($column, $direction) = split / /, $args{order_by};
2562 if(grep { $_ eq $column } @columns) {
2563 $direction ||= 'ASC';
2564 $query .= " ORDER BY $column $direction";
2568 my $sth = $dbh->prepare($query);
2569 $sth->execute(@bind_args);
2571 my $results = $sth->fetchall_arrayref({});
2577 my $invoice = GetInvoice($invoiceid);
2579 Get informations about invoice with given $invoiceid
2581 Return a hash filled with aqinvoices.* fields
2586 my ($invoiceid) = @_;
2589 return unless $invoiceid;
2591 my $dbh = C4::Context->dbh;
2597 my $sth = $dbh->prepare($query);
2598 $sth->execute($invoiceid);
2600 $invoice = $sth->fetchrow_hashref;
2604 =head3 GetInvoiceDetails
2606 my $invoice = GetInvoiceDetails($invoiceid)
2608 Return informations about an invoice + the list of related order lines
2610 Orders informations are in $invoice->{orders} (array ref)
2614 sub GetInvoiceDetails {
2615 my ($invoiceid) = @_;
2617 if ( !defined $invoiceid ) {
2618 carp 'GetInvoiceDetails called without an invoiceid';
2622 my $dbh = C4::Context->dbh;
2624 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2626 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2629 my $sth = $dbh->prepare($query);
2630 $sth->execute($invoiceid);
2632 my $invoice = $sth->fetchrow_hashref;
2637 biblio.copyrightdate,
2639 biblioitems.publishercode,
2640 biblioitems.publicationyear,
2641 aqbasket.basketname,
2642 aqbasketgroups.id AS basketgroupid,
2643 aqbasketgroups.name AS basketgroupname
2645 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2646 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2647 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2648 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2651 $sth = $dbh->prepare($query);
2652 $sth->execute($invoiceid);
2653 $invoice->{orders} = $sth->fetchall_arrayref({});
2654 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2661 my $invoiceid = AddInvoice(
2662 invoicenumber => $invoicenumber,
2663 booksellerid => $booksellerid,
2664 shipmentdate => $shipmentdate,
2665 billingdate => $billingdate,
2666 closedate => $closedate,
2667 shipmentcost => $shipmentcost,
2668 shipmentcost_budgetid => $shipmentcost_budgetid
2671 Create a new invoice and return its id or undef if it fails.
2678 return unless(%invoice and $invoice{invoicenumber});
2680 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2681 closedate shipmentcost shipmentcost_budgetid message_id);
2685 foreach my $key (keys %invoice) {
2686 if(0 < grep { $_ eq $key } @columns) {
2687 push @set_strs, "$key = ?";
2688 push @set_args, ($invoice{$key} || undef);
2694 my $dbh = C4::Context->dbh;
2695 my $query = "INSERT INTO aqinvoices SET ";
2696 $query .= join (",", @set_strs);
2697 my $sth = $dbh->prepare($query);
2698 $rv = $sth->execute(@set_args);
2700 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2709 invoiceid => $invoiceid, # Mandatory
2710 invoicenumber => $invoicenumber,
2711 booksellerid => $booksellerid,
2712 shipmentdate => $shipmentdate,
2713 billingdate => $billingdate,
2714 closedate => $closedate,
2715 shipmentcost => $shipmentcost,
2716 shipmentcost_budgetid => $shipmentcost_budgetid
2719 Modify an invoice, invoiceid is mandatory.
2721 Return undef if it fails.
2728 return unless(%invoice and $invoice{invoiceid});
2730 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2731 closedate shipmentcost shipmentcost_budgetid);
2735 foreach my $key (keys %invoice) {
2736 if(0 < grep { $_ eq $key } @columns) {
2737 push @set_strs, "$key = ?";
2738 push @set_args, ($invoice{$key} || undef);
2742 my $dbh = C4::Context->dbh;
2743 my $query = "UPDATE aqinvoices SET ";
2744 $query .= join(",", @set_strs);
2745 $query .= " WHERE invoiceid = ?";
2747 my $sth = $dbh->prepare($query);
2748 $sth->execute(@set_args, $invoice{invoiceid});
2753 CloseInvoice($invoiceid);
2757 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2762 my ($invoiceid) = @_;
2764 return unless $invoiceid;
2766 my $dbh = C4::Context->dbh;
2769 SET closedate = CAST(NOW() AS DATE)
2772 my $sth = $dbh->prepare($query);
2773 $sth->execute($invoiceid);
2776 =head3 ReopenInvoice
2778 ReopenInvoice($invoiceid);
2782 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => $closedate );
2787 my ($invoiceid) = @_;
2789 return unless $invoiceid;
2791 my $dbh = C4::Context->dbh;
2794 SET closedate = NULL
2797 my $sth = $dbh->prepare($query);
2798 $sth->execute($invoiceid);
2803 DelInvoice($invoiceid);
2805 Delete an invoice if there are no items attached to it.
2810 my ($invoiceid) = @_;
2812 return unless $invoiceid;
2814 my $dbh = C4::Context->dbh;
2820 my $sth = $dbh->prepare($query);
2821 $sth->execute($invoiceid);
2822 my $res = $sth->fetchrow_arrayref;
2823 if ( $res && $res->[0] == 0 ) {
2825 DELETE FROM aqinvoices
2828 my $sth = $dbh->prepare($query);
2829 return ( $sth->execute($invoiceid) > 0 );
2834 =head3 MergeInvoices
2836 MergeInvoices($invoiceid, \@sourceids);
2838 Merge the invoices identified by the IDs in \@sourceids into
2839 the invoice identified by $invoiceid.
2844 my ($invoiceid, $sourceids) = @_;
2846 return unless $invoiceid;
2847 foreach my $sourceid (@$sourceids) {
2848 next if $sourceid == $invoiceid;
2849 my $source = GetInvoiceDetails($sourceid);
2850 foreach my $order (@{$source->{'orders'}}) {
2851 $order->{'invoiceid'} = $invoiceid;
2854 DelInvoice($source->{'invoiceid'});
2859 =head3 GetBiblioCountByBasketno
2861 $biblio_count = &GetBiblioCountByBasketno($basketno);
2863 Looks up the biblio's count that has basketno value $basketno
2869 sub GetBiblioCountByBasketno {
2870 my ($basketno) = @_;
2871 my $dbh = C4::Context->dbh;
2873 SELECT COUNT( DISTINCT( biblionumber ) )
2876 AND datecancellationprinted IS NULL
2879 my $sth = $dbh->prepare($query);
2880 $sth->execute($basketno);
2881 return $sth->fetchrow;
2884 =head3 GetOrderUsers
2886 $order_users_ids = &GetOrderUsers($ordernumber);
2888 Returns a list of all borrowernumbers that are in order users list
2893 my ($ordernumber) = @_;
2895 return unless $ordernumber;
2898 SELECT borrowernumber
2900 WHERE ordernumber = ?
2902 my $dbh = C4::Context->dbh;
2903 my $sth = $dbh->prepare($query);
2904 $sth->execute($ordernumber);
2905 my $results = $sth->fetchall_arrayref( {} );
2907 my @borrowernumbers;
2908 foreach (@$results) {
2909 push @borrowernumbers, $_->{'borrowernumber'};
2912 return @borrowernumbers;
2915 =head3 ModOrderUsers
2917 my @order_users_ids = (1, 2, 3);
2918 &ModOrderUsers($ordernumber, @basketusers_ids);
2920 Delete all users from order users list, and add users in C<@order_users_ids>
2926 my ( $ordernumber, @order_users_ids ) = @_;
2928 return unless $ordernumber;
2930 my $dbh = C4::Context->dbh;
2932 DELETE FROM aqorder_users
2933 WHERE ordernumber = ?
2935 my $sth = $dbh->prepare($query);
2936 $sth->execute($ordernumber);
2939 INSERT INTO aqorder_users (ordernumber, borrowernumber)
2942 $sth = $dbh->prepare($query);
2943 foreach my $order_user_id (@order_users_ids) {
2944 $sth->execute( $ordernumber, $order_user_id );
2948 sub NotifyOrderUsers {
2949 my ($ordernumber) = @_;
2951 my @borrowernumbers = GetOrderUsers($ordernumber);
2952 return unless @borrowernumbers;
2954 my $order = GetOrder( $ordernumber );
2955 for my $borrowernumber (@borrowernumbers) {
2956 my $patron = Koha::Patrons->find( $borrowernumber );
2957 my $library = $patron->library->unblessed;
2958 my $biblio = Koha::Biblios->find( $order->{biblionumber} )->unblessed;
2959 my $letter = C4::Letters::GetPreparedLetter(
2960 module => 'acquisition',
2961 letter_code => 'ACQ_NOTIF_ON_RECEIV',
2962 branchcode => $library->{branchcode},
2963 lang => $patron->lang,
2965 'branches' => $library,
2966 'borrowers' => $patron->unblessed,
2967 'biblio' => $biblio,
2968 'aqorders' => $order,
2972 C4::Letters::EnqueueLetter(
2975 borrowernumber => $borrowernumber,
2976 LibraryName => C4::Context->preference("LibraryName"),
2977 message_transport_type => 'email',
2979 ) or warn "can't enqueue letter $letter";
2984 =head3 FillWithDefaultValues
2986 FillWithDefaultValues( $marc_record, $params );
2988 This will update the record with default value defined in the ACQ framework.
2989 For all existing fields, if a default value exists and there are no subfield, it will be created.
2990 If the field does not exist, it will be created too.
2992 If the parameter only_mandatory => 1 is passed via $params, only the mandatory
2993 defaults are being applied to the record.
2997 sub FillWithDefaultValues {
2998 my ( $record, $params ) = @_;
2999 my $mandatory = $params->{only_mandatory};
3000 my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ', { unsafe => 1 } );
3003 C4::Biblio::GetMarcFromKohaField( 'items.itemnumber' );
3004 for my $tag ( sort keys %$tagslib ) {
3006 next if $tag == $itemfield;
3007 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
3008 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
3009 next if $mandatory && !$tagslib->{$tag}{$subfield}{mandatory};
3010 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
3011 if ( defined $defaultvalue and $defaultvalue ne '' ) {
3012 my @fields = $record->field($tag);
3014 for my $field (@fields) {
3015 if ( $field->is_control_field ) {
3016 $field->update($defaultvalue) if not defined $field->data;
3018 elsif ( not defined $field->subfield($subfield) ) {
3019 $field->add_subfields(
3020 $subfield => $defaultvalue );
3025 if ( $tag < 10 ) { # is_control_field
3026 $record->insert_fields_ordered(
3033 $record->insert_fields_ordered(
3035 $tag, '', '', $subfield => $defaultvalue
3051 Koha Development Team <http://koha-community.org/>