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;
38 use Koha::Number::Price;
40 use Koha::CsvProfiles;
46 use JSON qw( to_json );
49 our (@ISA, @EXPORT_OK);
54 GetBasket NewBasket ReopenBasket ModBasket
55 GetBasketAsCSV GetBasketGroupAsCSV
56 GetBasketsByBookseller GetBasketsByBasketgroup
57 GetBasketsInfosByBookseller
59 GetBasketUsers ModBasketUsers
64 ModBasketgroup NewBasketgroup DelBasketgroup GetBasketgroup CloseBasketgroup
65 GetBasketgroups ReOpenBasketgroup
67 ModOrder GetOrder GetOrders GetOrdersByBiblionumber
68 GetOrderFromItemnumber
69 SearchOrders GetHistory GetRecentAcqui
70 ModReceiveOrder CancelReceipt
87 GetBiblioCountByBasketno
104 sub GetOrderFromItemnumber {
105 my ($itemnumber) = @_;
106 my $dbh = C4::Context->dbh;
109 SELECT * from aqorders LEFT JOIN aqorders_items
110 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
111 WHERE itemnumber = ? |;
113 my $sth = $dbh->prepare($query);
117 $sth->execute($itemnumber);
119 my $order = $sth->fetchrow_hashref;
126 C4::Acquisition - Koha functions for dealing with orders and acquisitions
134 The functions in this module deal with acquisitions, managing book
135 orders, basket and parcels.
139 =head2 FUNCTIONS ABOUT BASKETS
143 $aqbasket = &GetBasket($basketnumber);
145 get all basket informations in aqbasket for a given basket
147 B<returns:> informations for a given basket returned as a hashref.
153 my $dbh = C4::Context->dbh;
156 concat( b.firstname,' ',b.surname) AS authorisedbyname
158 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
161 my $sth=$dbh->prepare($query);
162 $sth->execute($basketno);
163 my $basket = $sth->fetchrow_hashref;
167 #------------------------------------------------------------#
171 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
172 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace, $is_standing, $create_items );
174 Create a new basket in aqbasket table
178 =item C<$booksellerid> is a foreign key in the aqbasket table
180 =item C<$authorizedby> is the username of who created the basket
184 The other parameters are optional, see ModBasketHeader for more info on them.
189 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
190 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
191 $billingplace, $is_standing, $create_items ) = @_;
192 my $dbh = C4::Context->dbh;
194 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
195 . 'VALUES (now(),?,?)';
196 $dbh->do( $query, {}, $booksellerid, $authorisedby );
198 my $basket = $dbh->{mysql_insertid};
199 $basketname ||= q{}; # default to empty strings
201 $basketbooksellernote ||= q{};
203 # Log the basket creation
204 if (C4::Context->preference("AcquisitionLog")) {
205 my $created = Koha::Acquisition::Baskets->find( $basket );
210 to_json($created->unblessed)
214 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
215 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items );
222 &ReopenBasket($basketno);
230 my $dbh = C4::Context->dbh;
231 $dbh->do( q{UPDATE aqbasket SET closedate=NULL WHERE basketno=?}, {}, $basketno );
235 SET orderstatus = 'new'
237 AND orderstatus NOT IN ( 'complete', 'cancelled' )
240 # Log the basket reopening
241 if (C4::Context->preference("AcquisitionLog")) {
242 my $reopened = Koha::Acquisition::Baskets->find( $basketno );
247 to_json($reopened->unblessed)
253 #------------------------------------------------------------#
255 =head3 GetBasketAsCSV
257 &GetBasketAsCSV($basketno);
259 Export a basket as CSV
261 $cgi parameter is needed for column name translation
266 my ($basketno, $cgi, $csv_profile_id) = @_;
267 my $basket = GetBasket($basketno);
268 my @orders = GetOrders($basketno);
269 my $contract = GetContract({
270 contractnumber => $basket->{'contractnumber'}
273 my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
275 if ($csv_profile_id) {
276 my $csv_profile = Koha::CsvProfiles->find( $csv_profile_id );
277 Koha::Exceptions::ObjectNotFound->throw( 'There is no valid csv profile given') unless $csv_profile;
279 my $delimiter = $csv_profile->csv_separator;
280 $delimiter = "\t" if $delimiter eq "\\t";
281 my $csv = Text::CSV_XS->new({'quote_char'=>'"','escape_char'=>'"','sep_char'=>$delimiter,'binary'=>1});
282 my $csv_profile_content = $csv_profile->content;
283 my ( @headers, @fields );
284 while ( $csv_profile_content =~ /
287 ([^\|]*) # fieldname (table.row or row)
291 my $field = ($2 eq '') ? $1 : $2;
293 $header =~ s/^\s+|\s+$//g; # Trim whitespaces
294 push @headers, $header;
296 $field =~ s/[^\.]*\.{1}//; # Remove the table name if exists.
297 $field =~ s/^\s+|\s+$//g; # Trim whitespaces
298 push @fields, $field;
300 for my $order (@orders) {
302 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
303 my $biblioitem = $biblio->biblioitem;
304 $order = { %$order, %{ $biblioitem->unblessed } };
306 $order = {%$order, %$contract};
308 $order = {%$order, %$basket, %{ $biblio->unblessed }};
309 for my $field (@fields) {
310 push @row, $order->{$field};
314 my $content = join( $delimiter, @headers ) . "\n";
315 for my $row ( @rows ) {
316 $csv->combine(@$row);
317 my $string = $csv->string;
318 $content .= $string . "\n";
323 foreach my $order (@orders) {
324 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
327 $biblioitem = $biblio->biblioitem;
330 contractname => $contract->{'contractname'},
331 ordernumber => $order->{'ordernumber'},
332 entrydate => $order->{'entrydate'},
333 isbn => $order->{'isbn'},
334 author => $biblio ? $biblio->author : q{},
335 title => $biblio ? $biblio->title : q{},
336 publicationyear => $biblioitem ? $biblioitem->publicationyear : q{},
337 publishercode => $biblioitem ? $biblioitem->publishercode : q{},
338 collectiontitle => $biblioitem ? $biblioitem->collectiontitle : q{},
339 notes => $order->{'order_vendornote'},
340 quantity => $order->{'quantity'},
341 rrp => $order->{'rrp'},
343 for my $place ( qw( deliveryplace billingplace ) ) {
344 if ( my $library = Koha::Libraries->find( $row->{deliveryplace} ) ) {
345 $row->{$place} = $library->branchname
349 contractname author title publishercode collectiontitle notes
350 deliveryplace billingplace
352 # Double the quotes to not be interpreted as a field end
353 $row->{$_} =~ s/"/""/g if $row->{$_};
359 if(defined $a->{publishercode} and defined $b->{publishercode}) {
360 $a->{publishercode} cmp $b->{publishercode};
364 $template->param(rows => \@rows);
366 return $template->output;
371 =head3 GetBasketGroupAsCSV
373 &GetBasketGroupAsCSV($basketgroupid);
375 Export a basket group as CSV
377 $cgi parameter is needed for column name translation
381 sub GetBasketGroupAsCSV {
382 my ($basketgroupid, $cgi) = @_;
383 my $baskets = GetBasketsByBasketgroup($basketgroupid);
385 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
388 for my $basket (@$baskets) {
389 my @orders = GetOrders( $basket->{basketno} );
390 my $contract = GetContract({
391 contractnumber => $basket->{contractnumber}
393 my $bookseller = Koha::Acquisition::Booksellers->find( $basket->{booksellerid} );
394 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
396 foreach my $order (@orders) {
397 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
400 $biblioitem = $biblio->biblioitem;
403 clientnumber => $bookseller->accountnumber,
404 basketname => $basket->{basketname},
405 ordernumber => $order->{ordernumber},
406 author => $biblio ? $biblio->author : q{},
407 title => $biblio ? $biblio->title : q{},
408 publishercode => $biblioitem ? $biblioitem->publishercode : q{},
409 publicationyear => $biblioitem ? $biblioitem->publicationyear : q{},
410 collectiontitle => $biblioitem ? $biblioitem->collectiontitle : q{},
411 isbn => $order->{isbn},
412 quantity => $order->{quantity},
413 rrp_tax_included => $order->{rrp_tax_included},
414 rrp_tax_excluded => $order->{rrp_tax_excluded},
415 discount => $bookseller->discount,
416 ecost_tax_included => $order->{ecost_tax_included},
417 ecost_tax_excluded => $order->{ecost_tax_excluded},
418 notes => $order->{order_vendornote},
419 entrydate => $order->{entrydate},
420 booksellername => $bookseller->name,
421 bookselleraddress => $bookseller->address1,
422 booksellerpostal => $bookseller->postal,
423 contractnumber => $contract->{contractnumber},
424 contractname => $contract->{contractname},
427 basketgroupdeliveryplace => $basketgroup->{deliveryplace},
428 basketgroupbillingplace => $basketgroup->{billingplace},
429 basketdeliveryplace => $basket->{deliveryplace},
430 basketbillingplace => $basket->{billingplace},
432 for my $place (qw( basketgroupdeliveryplace basketgroupbillingplace basketdeliveryplace basketbillingplace )) {
433 if ( my $library = Koha::Libraries->find( $temp->{$place} ) ) {
434 $row->{$place} = $library->branchname;
438 basketname author title publishercode collectiontitle notes
439 booksellername bookselleraddress booksellerpostal contractname
440 basketgroupdeliveryplace basketgroupbillingplace
441 basketdeliveryplace basketbillingplace
443 # Double the quotes to not be interpreted as a field end
444 $row->{$_} =~ s/"/""/g if $row->{$_};
449 $template->param(rows => \@rows);
451 return $template->output;
455 =head3 CloseBasketgroup
457 &CloseBasketgroup($basketgroupno);
463 sub CloseBasketgroup {
464 my ($basketgroupno) = @_;
465 my $dbh = C4::Context->dbh;
466 my $sth = $dbh->prepare("
467 UPDATE aqbasketgroups
471 $sth->execute($basketgroupno);
474 #------------------------------------------------------------#
476 =head3 ReOpenBaskergroup($basketgroupno)
478 &ReOpenBaskergroup($basketgroupno);
484 sub ReOpenBasketgroup {
485 my ($basketgroupno) = @_;
486 my $dbh = C4::Context->dbh;
487 my $sth = $dbh->prepare("
488 UPDATE aqbasketgroups
492 $sth->execute($basketgroupno);
495 #------------------------------------------------------------#
499 &ModBasket($basketinfo);
501 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
505 =item C<$basketno> is the primary key of the basket in the aqbasket table.
512 my $basketinfo = shift;
513 my $query = "UPDATE aqbasket SET ";
515 foreach my $key (keys %$basketinfo){
516 if ($key ne 'basketno'){
517 $query .= "$key=?, ";
518 push(@params, $basketinfo->{$key} || undef );
521 # get rid of the "," at the end of $query
522 if (substr($query, length($query)-2) eq ', '){
527 $query .= "WHERE basketno=?";
528 push(@params, $basketinfo->{'basketno'});
529 my $dbh = C4::Context->dbh;
530 my $sth = $dbh->prepare($query);
531 $sth->execute(@params);
533 # Log the basket update
534 if (C4::Context->preference("AcquisitionLog")) {
535 my $modified = Koha::Acquisition::Baskets->find(
536 $basketinfo->{basketno}
541 $basketinfo->{basketno},
542 to_json($modified->unblessed)
549 #------------------------------------------------------------#
551 =head3 ModBasketHeader
553 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
555 Modifies a basket's header.
559 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
561 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
563 =item C<$note> is the "note" field in the "aqbasket" table;
565 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
567 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
569 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
571 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
573 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
575 =item C<$is_standing> is the "is_standing" field in the aqbasket table.
577 =item C<$create_items> should be set to 'ordering', 'receiving' or 'cataloguing' (or undef, in which
578 case the AcqCreateItem syspref takes precedence).
584 sub ModBasketHeader {
585 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items) = @_;
590 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?, is_standing=?, create_items=?
594 my $dbh = C4::Context->dbh;
595 my $sth = $dbh->prepare($query);
596 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items || undef, $basketno);
598 if ( $contractnumber ) {
599 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
600 my $sth2 = $dbh->prepare($query2);
601 $sth2->execute($contractnumber,$basketno);
604 # Log the basket update
605 if (C4::Context->preference("AcquisitionLog")) {
606 my $modified = Koha::Acquisition::Baskets->find(
611 'MODIFY_BASKET_HEADER',
613 to_json($modified->unblessed)
620 #------------------------------------------------------------#
622 =head3 GetBasketsByBookseller
624 @results = &GetBasketsByBookseller($booksellerid, $extra);
626 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
630 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
632 =item C<$extra> is the extra sql parameters, can be
634 $extra->{groupby}: group baskets by column
635 ex. $extra->{groupby} = aqbasket.basketgroupid
636 $extra->{orderby}: order baskets by column
637 $extra->{limit}: limit number of results (can be helpful for pagination)
643 sub GetBasketsByBookseller {
644 my ($booksellerid, $extra) = @_;
645 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
647 if ($extra->{groupby}) {
648 $query .= " GROUP by $extra->{groupby}";
650 if ($extra->{orderby}){
651 $query .= " ORDER by $extra->{orderby}";
653 if ($extra->{limit}){
654 $query .= " LIMIT $extra->{limit}";
657 my $dbh = C4::Context->dbh;
658 my $sth = $dbh->prepare($query);
659 $sth->execute($booksellerid);
660 return $sth->fetchall_arrayref({});
663 =head3 GetBasketsInfosByBookseller
665 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
667 The optional second parameter allbaskets is a boolean allowing you to
668 select all baskets from the supplier; by default only active baskets (open or
669 closed but still something to receive) are returned.
671 Returns in a arrayref of hashref all about booksellers baskets, plus:
672 total_biblios: Number of distinct biblios in basket
673 total_items: Number of items in basket
674 expected_items: Number of non-received items in basket
678 sub GetBasketsInfosByBookseller {
679 my ($supplierid, $allbaskets) = @_;
681 return unless $supplierid;
683 my $dbh = C4::Context->dbh;
685 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,
686 SUM(aqorders.quantity) AS total_items,
688 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
689 ) AS total_items_cancelled,
690 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
692 IF(aqorders.datereceived IS NULL
693 AND aqorders.datecancellationprinted IS NULL
697 SUM( aqorders.uncertainprice ) AS uncertainprices
699 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
700 WHERE booksellerid = ?};
702 $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";
704 unless ( $allbaskets ) {
705 # Don't show the basket if it's NOT CLOSED or is FULLY RECEIVED
706 $query.=" HAVING (closedate IS NULL OR (
708 IF(aqorders.datereceived IS NULL
709 AND aqorders.datecancellationprinted IS NULL
715 my $sth = $dbh->prepare($query);
716 $sth->execute($supplierid);
717 my $baskets = $sth->fetchall_arrayref({});
719 # Retrieve the number of biblios cancelled
720 my $cancelled_biblios = $dbh->selectall_hashref( q|
721 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
723 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
724 WHERE booksellerid = ?
725 AND aqorders.orderstatus = 'cancelled'
726 GROUP BY aqbasket.basketno
727 |, 'basketno', {}, $supplierid );
729 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
735 =head3 GetBasketUsers
737 $basketusers_ids = &GetBasketUsers($basketno);
739 Returns a list of all borrowernumbers that are in basket users list
744 my $basketno = shift;
746 return unless $basketno;
749 SELECT borrowernumber
753 my $dbh = C4::Context->dbh;
754 my $sth = $dbh->prepare($query);
755 $sth->execute($basketno);
756 my $results = $sth->fetchall_arrayref( {} );
759 foreach (@$results) {
760 push @borrowernumbers, $_->{'borrowernumber'};
763 return @borrowernumbers;
766 =head3 ModBasketUsers
768 my @basketusers_ids = (1, 2, 3);
769 &ModBasketUsers($basketno, @basketusers_ids);
771 Delete all users from basket users list, and add users in C<@basketusers_ids>
777 my ($basketno, @basketusers_ids) = @_;
779 return unless $basketno;
781 my $dbh = C4::Context->dbh;
783 DELETE FROM aqbasketusers
786 my $sth = $dbh->prepare($query);
787 $sth->execute($basketno);
790 INSERT INTO aqbasketusers (basketno, borrowernumber)
793 $sth = $dbh->prepare($query);
794 foreach my $basketuser_id (@basketusers_ids) {
795 $sth->execute($basketno, $basketuser_id);
798 # Log the basket update
799 if (C4::Context->preference("AcquisitionLog")) {
802 'MODIFY_BASKET_USERS',
805 basketno => $basketno,
806 basketusers => @basketusers_ids
814 =head3 CanUserManageBasket
816 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
817 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
819 Check if a borrower can manage a basket, according to system preference
820 AcqViewBaskets, user permissions and basket properties (creator, users list,
823 First parameter can be either a borrowernumber or a hashref as returned by
824 Koha::Patron->unblessed
826 Second parameter can be either a basketno or a hashref as returned by
827 C4::Acquisition::GetBasket.
829 The third parameter is optional. If given, it should be a hashref as returned
830 by C4::Auth::getuserflags. If not, getuserflags is called.
832 If user is authorised to manage basket, returns 1.
837 sub CanUserManageBasket {
838 my ($borrower, $basket, $userflags) = @_;
840 if (!ref $borrower) {
841 # FIXME This needs to be replaced
842 # We should not accept both scalar and array
843 # Tests need to be updated
844 $borrower = Koha::Patrons->find( $borrower )->unblessed;
847 $basket = GetBasket($basket);
850 return 0 unless ($basket and $borrower);
852 my $borrowernumber = $borrower->{borrowernumber};
853 my $basketno = $basket->{basketno};
855 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
857 if (!defined $userflags) {
858 my $dbh = C4::Context->dbh;
859 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
860 $sth->execute($borrowernumber);
861 my ($flags) = $sth->fetchrow_array;
864 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
867 unless ($userflags->{superlibrarian}
868 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
869 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
871 if (not exists $userflags->{acquisition}) {
875 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
876 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
880 if ($AcqViewBaskets eq 'user'
881 && $basket->{authorisedby} != $borrowernumber
882 && ! grep { $borrowernumber eq $_ } GetBasketUsers($basketno)) {
886 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
887 && $basket->{branch} ne $borrower->{branchcode}) {
895 #------------------------------------------------------------#
897 =head3 GetBasketsByBasketgroup
899 $baskets = &GetBasketsByBasketgroup($basketgroupid);
901 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
905 sub GetBasketsByBasketgroup {
906 my $basketgroupid = shift;
908 SELECT *, aqbasket.booksellerid as booksellerid
910 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
912 my $dbh = C4::Context->dbh;
913 my $sth = $dbh->prepare($query);
914 $sth->execute($basketgroupid);
915 return $sth->fetchall_arrayref({});
918 #------------------------------------------------------------#
920 =head3 NewBasketgroup
922 $basketgroupid = NewBasketgroup(\%hashref);
924 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
926 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
928 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
930 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
932 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
934 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
936 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
938 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
940 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
945 my $basketgroupinfo = shift;
946 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
947 my $query = "INSERT INTO aqbasketgroups (";
949 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
950 if ( defined $basketgroupinfo->{$field} ) {
951 $query .= "$field, ";
952 push(@params, $basketgroupinfo->{$field});
955 $query .= "booksellerid) VALUES (";
960 push(@params, $basketgroupinfo->{'booksellerid'});
961 my $dbh = C4::Context->dbh;
962 my $sth = $dbh->prepare($query);
963 $sth->execute(@params);
964 my $basketgroupid = $dbh->{'mysql_insertid'};
965 if( $basketgroupinfo->{'basketlist'} ) {
966 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
967 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
968 my $sth2 = $dbh->prepare($query2);
969 $sth2->execute($basketgroupid, $basketno);
972 return $basketgroupid;
975 #------------------------------------------------------------#
977 =head3 ModBasketgroup
979 ModBasketgroup(\%hashref);
981 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
983 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
985 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
987 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
989 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
991 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
993 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
995 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
997 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
1001 sub ModBasketgroup {
1002 my $basketgroupinfo = shift;
1003 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
1004 my $dbh = C4::Context->dbh;
1005 my $query = "UPDATE aqbasketgroups SET ";
1007 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
1008 if ( defined $basketgroupinfo->{$field} ) {
1009 $query .= "$field=?, ";
1010 push(@params, $basketgroupinfo->{$field});
1015 $query .= " WHERE id=?";
1016 push(@params, $basketgroupinfo->{'id'});
1017 my $sth = $dbh->prepare($query);
1018 $sth->execute(@params);
1020 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
1021 $sth->execute($basketgroupinfo->{'id'});
1023 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
1024 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
1025 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
1026 $sth->execute($basketgroupinfo->{'id'}, $basketno);
1032 #------------------------------------------------------------#
1034 =head3 DelBasketgroup
1036 DelBasketgroup($basketgroupid);
1038 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
1042 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
1048 sub DelBasketgroup {
1049 my $basketgroupid = shift;
1050 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1051 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
1052 my $dbh = C4::Context->dbh;
1053 my $sth = $dbh->prepare($query);
1054 $sth->execute($basketgroupid);
1058 #------------------------------------------------------------#
1061 =head2 FUNCTIONS ABOUT ORDERS
1063 =head3 GetBasketgroup
1065 $basketgroup = &GetBasketgroup($basketgroupid);
1067 Returns a reference to the hash containing all information about the basketgroup.
1071 sub GetBasketgroup {
1072 my $basketgroupid = shift;
1073 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1074 my $dbh = C4::Context->dbh;
1075 my $result_set = $dbh->selectall_arrayref(
1076 'SELECT * FROM aqbasketgroups WHERE id=?',
1080 return $result_set->[0]; # id is unique
1083 #------------------------------------------------------------#
1085 =head3 GetBasketgroups
1087 $basketgroups = &GetBasketgroups($booksellerid);
1089 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1093 sub GetBasketgroups {
1094 my $booksellerid = shift;
1095 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1096 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1097 my $dbh = C4::Context->dbh;
1098 my $sth = $dbh->prepare($query);
1099 $sth->execute($booksellerid);
1100 return $sth->fetchall_arrayref({});
1103 #------------------------------------------------------------#
1105 =head2 FUNCTIONS ABOUT ORDERS
1109 @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } );
1111 Looks up the pending (non-cancelled) orders with the given basket
1114 If cancelled is set, only cancelled orders will be returned.
1119 my ( $basketno, $params ) = @_;
1121 return () unless $basketno;
1123 my $orderby = $params->{orderby};
1124 my $cancelled = $params->{cancelled} || 0;
1126 my $dbh = C4::Context->dbh;
1128 SELECT biblio.*,biblioitems.*,
1132 $query .= $cancelled
1134 aqorders_transfers.ordernumber_to AS transferred_to,
1135 aqorders_transfers.timestamp AS transferred_to_timestamp
1138 aqorders_transfers.ordernumber_from AS transferred_from,
1139 aqorders_transfers.timestamp AS transferred_from_timestamp
1143 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1144 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1145 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1147 $query .= $cancelled
1149 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1152 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1160 $orderby ||= q|biblioitems.publishercode, biblio.title|;
1162 AND datecancellationprinted IS NOT NULL
1167 q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|;
1169 AND datecancellationprinted IS NULL
1173 $query .= " ORDER BY $orderby";
1175 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1180 #------------------------------------------------------------#
1182 =head3 GetOrdersByBiblionumber
1184 @orders = &GetOrdersByBiblionumber($biblionumber);
1186 Looks up the orders with linked to a specific $biblionumber, including
1187 cancelled orders and received orders.
1190 C<@orders> is an array of references-to-hash, whose keys are the
1191 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1195 sub GetOrdersByBiblionumber {
1196 my $biblionumber = shift;
1197 return unless $biblionumber;
1198 my $dbh = C4::Context->dbh;
1200 SELECT biblio.*,biblioitems.*,
1204 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1205 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1206 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1207 WHERE aqorders.biblionumber=?
1210 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1211 return @{$result_set};
1215 #------------------------------------------------------------#
1219 $order = &GetOrder($ordernumber);
1221 Looks up an order by order number.
1223 Returns a reference-to-hash describing the order. The keys of
1224 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1229 my ($ordernumber) = @_;
1230 return unless $ordernumber;
1232 my $dbh = C4::Context->dbh;
1233 my $query = qq{SELECT
1237 aqbasket.basketname,
1238 borrowers.branchcode,
1239 biblioitems.publicationyear,
1240 biblio.copyrightdate,
1241 biblioitems.editionstatement,
1245 biblioitems.publishercode,
1246 aqorders.rrp AS unitpricesupplier,
1247 aqorders.ecost AS unitpricelib,
1248 aqbudgets.budget_name AS budget,
1249 aqbooksellers.name AS supplier,
1250 aqbooksellers.id AS supplierid,
1251 biblioitems.publishercode AS publisher,
1252 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS calculateddeliverydate,
1253 DATE(aqbasket.closedate) AS orderdate,
1254 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1255 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1256 DATEDIFF(CURDATE( ),closedate) AS latesince
1257 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1258 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1259 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1260 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1261 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1262 WHERE aqorders.basketno = aqbasket.basketno
1265 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1267 # result_set assumed to contain 1 match
1268 return $result_set->[0];
1273 &ModOrder(\%hashref);
1275 Modifies an existing order. Updates the order with order number
1276 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1277 other keys of the hash update the fields with the same name in the aqorders
1278 table of the Koha database.
1283 my $orderinfo = shift;
1285 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '';
1287 my $dbh = C4::Context->dbh;
1290 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1291 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1293 # delete($orderinfo->{'branchcode'});
1294 # the hash contains a lot of entries not in aqorders, so get the columns ...
1295 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1297 my $colnames = $sth->{NAME};
1298 #FIXME Be careful. If aqorders would have columns with diacritics,
1299 #you should need to decode what you get back from NAME.
1300 #See report 10110 and guided_reports.pl
1301 my $query = "UPDATE aqorders SET ";
1303 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1304 # ... and skip hash entries that are not in the aqorders table
1305 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1306 next unless grep { $_ eq $orderinfokey } @$colnames;
1307 $query .= "$orderinfokey=?, ";
1308 push(@params, $orderinfo->{$orderinfokey});
1311 $query .= "timestamp=NOW() WHERE ordernumber=?";
1312 push(@params, $orderinfo->{'ordernumber'} );
1313 $sth = $dbh->prepare($query);
1314 $sth->execute(@params);
1318 #------------------------------------------------------------#
1322 ModItemOrder($itemnumber, $ordernumber);
1324 Modifies the ordernumber of an item in aqorders_items.
1329 my ($itemnumber, $ordernumber) = @_;
1331 return unless ($itemnumber and $ordernumber);
1333 my $dbh = C4::Context->dbh;
1335 UPDATE aqorders_items
1337 WHERE itemnumber = ?
1339 my $sth = $dbh->prepare($query);
1340 return $sth->execute($ordernumber, $itemnumber);
1343 #------------------------------------------------------------#
1345 =head3 ModReceiveOrder
1347 my ( $date_received, $new_ordernumber ) = ModReceiveOrder(
1349 biblionumber => $biblionumber,
1351 quantityreceived => $quantityreceived,
1353 invoice => $invoice,
1354 budget_id => $budget_id,
1355 datereceived => $datereceived,
1356 received_itemnumbers => \@received_itemnumbers,
1360 Updates an order, to reflect the fact that it was received, at least
1363 If a partial order is received, splits the order into two.
1365 Updates the order with biblionumber C<$biblionumber> and ordernumber
1366 C<$order->{ordernumber}>.
1371 sub ModReceiveOrder {
1373 my $biblionumber = $params->{biblionumber};
1374 my $order = { %{ $params->{order} } }; # Copy the order, we don't want to modify it
1375 my $invoice = $params->{invoice};
1376 my $quantrec = $params->{quantityreceived};
1377 my $user = $params->{user};
1378 my $budget_id = $params->{budget_id};
1379 my $datereceived = $params->{datereceived};
1380 my $received_items = $params->{received_items};
1382 my $dbh = C4::Context->dbh;
1383 $datereceived = $datereceived ? dt_from_string( $datereceived ) : dt_from_string;
1384 $datereceived = $datereceived->ymd;
1386 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1387 if ($suggestionid) {
1388 ModSuggestion( {suggestionid=>$suggestionid,
1389 STATUS=>'AVAILABLE',
1390 biblionumber=> $biblionumber}
1394 my $result_set = $dbh->selectrow_arrayref(
1395 q{SELECT aqbasket.is_standing
1397 WHERE basketno=?},{ Slice => {} }, $order->{basketno});
1398 my $is_standing = $result_set->[0]; # we assume we have a unique basket
1400 my $new_ordernumber = $order->{ordernumber};
1401 if ( $is_standing || $order->{quantity} > $quantrec ) {
1402 # Split order line in two parts: the first is the original order line
1403 # without received items (the quantity is decreased),
1404 # the second part is a new order line with quantity=quantityrec
1405 # (entirely received)
1409 orderstatus = 'partial'|;
1410 $query .= q| WHERE ordernumber = ?|;
1411 my $sth = $dbh->prepare($query);
1414 ( $is_standing ? 1 : ($order->{quantity} - $quantrec) ),
1415 $order->{ordernumber}
1418 if ( not $order->{subscriptionid} && defined $order->{order_internalnote} ) {
1421 SET order_internalnote = ?
1422 WHERE ordernumber = ?|, {},
1423 $order->{order_internalnote}, $order->{ordernumber}
1427 # Recalculate tax_value
1431 tax_value_on_ordering = quantity * | . get_rounding_sql(q|ecost_tax_excluded|) . q| * tax_rate_on_ordering,
1432 tax_value_on_receiving = quantity * | . get_rounding_sql(q|unitprice_tax_excluded|) . q| * tax_rate_on_receiving
1433 WHERE ordernumber = ?
1434 |, undef, $order->{ordernumber});
1436 delete $order->{ordernumber};
1437 $order->{budget_id} = ( $budget_id || $order->{budget_id} );
1438 $order->{quantity} = $quantrec;
1439 $order->{quantityreceived} = $quantrec;
1440 $order->{ecost_tax_excluded} //= 0;
1441 $order->{tax_rate_on_ordering} //= 0;
1442 $order->{unitprice_tax_excluded} //= 0;
1443 $order->{tax_rate_on_receiving} //= 0;
1444 $order->{tax_value_on_ordering} = $order->{quantity} * get_rounded_price($order->{ecost_tax_excluded}) * $order->{tax_rate_on_ordering};
1445 $order->{tax_value_on_receiving} = $order->{quantity} * get_rounded_price($order->{unitprice_tax_excluded}) * $order->{tax_rate_on_receiving};
1446 $order->{datereceived} = $datereceived;
1447 $order->{invoiceid} = $invoice->{invoiceid};
1448 $order->{orderstatus} = 'complete';
1449 $new_ordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber; # TODO What if the store fails?
1451 if ($received_items) {
1452 foreach my $itemnumber (@$received_items) {
1453 ModItemOrder($itemnumber, $new_ordernumber);
1459 SET quantityreceived = ?,
1463 orderstatus = 'complete'
1467 , replacementprice = ?
1468 | if defined $order->{replacementprice};
1471 , unitprice = ?, unitprice_tax_included = ?, unitprice_tax_excluded = ?
1472 | if defined $order->{unitprice};
1475 ,tax_value_on_receiving = ?
1476 | if defined $order->{tax_value_on_receiving};
1479 ,tax_rate_on_receiving = ?
1480 | if defined $order->{tax_rate_on_receiving};
1483 , order_internalnote = ?
1484 | if defined $order->{order_internalnote};
1486 $query .= q| where biblionumber=? and ordernumber=?|;
1488 my $sth = $dbh->prepare( $query );
1489 my @params = ( $quantrec, $datereceived, $invoice->{invoiceid}, ( $budget_id ? $budget_id : $order->{budget_id} ) );
1491 if ( defined $order->{replacementprice} ) {
1492 push @params, $order->{replacementprice};
1495 if ( defined $order->{unitprice} ) {
1496 push @params, $order->{unitprice}, $order->{unitprice_tax_included}, $order->{unitprice_tax_excluded};
1499 if ( defined $order->{tax_value_on_receiving} ) {
1500 push @params, $order->{tax_value_on_receiving};
1503 if ( defined $order->{tax_rate_on_receiving} ) {
1504 push @params, $order->{tax_rate_on_receiving};
1507 if ( defined $order->{order_internalnote} ) {
1508 push @params, $order->{order_internalnote};
1511 push @params, ( $biblionumber, $order->{ordernumber} );
1513 $sth->execute( @params );
1515 # All items have been received, sent a notification to users
1516 NotifyOrderUsers( $order->{ordernumber} );
1519 return ($datereceived, $new_ordernumber);
1522 =head3 CancelReceipt
1524 my $parent_ordernumber = CancelReceipt($ordernumber);
1526 Cancel an order line receipt and update the parent order line, as if no
1528 If items are created at receipt (AcqCreateItem = receiving) then delete
1534 my $ordernumber = shift;
1536 return unless $ordernumber;
1538 my $dbh = C4::Context->dbh;
1540 SELECT datereceived, parent_ordernumber, quantity
1542 WHERE ordernumber = ?
1544 my $sth = $dbh->prepare($query);
1545 $sth->execute($ordernumber);
1546 my $order = $sth->fetchrow_hashref;
1548 warn "CancelReceipt: order $ordernumber does not exist";
1551 unless($order->{'datereceived'}) {
1552 warn "CancelReceipt: order $ordernumber is not received";
1556 my $parent_ordernumber = $order->{'parent_ordernumber'};
1558 my $order_obj = Koha::Acquisition::Orders->find( $ordernumber ); # FIXME rewrite all this subroutine using this object
1559 my @itemnumbers = $order_obj->items->get_column('itemnumber');
1561 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1562 # The order line has no parent, just mark it as not received
1565 SET quantityreceived = ?,
1568 orderstatus = 'ordered'
1569 WHERE ordernumber = ?
1571 $sth = $dbh->prepare($query);
1572 $sth->execute(0, undef, undef, $ordernumber);
1573 _cancel_items_receipt( $order_obj );
1575 # The order line has a parent, increase parent quantity and delete
1577 unless ( $order_obj->basket->is_standing ) {
1579 SELECT quantity, datereceived
1581 WHERE ordernumber = ?
1583 $sth = $dbh->prepare($query);
1584 $sth->execute($parent_ordernumber);
1585 my $parent_order = $sth->fetchrow_hashref;
1586 unless($parent_order) {
1587 warn "Parent order $parent_ordernumber does not exist.";
1590 if($parent_order->{'datereceived'}) {
1591 warn "CancelReceipt: parent order is received.".
1592 " Can't cancel receipt.";
1598 orderstatus = 'ordered'
1599 WHERE ordernumber = ?
1601 $sth = $dbh->prepare($query);
1602 my $rv = $sth->execute(
1603 $order->{'quantity'} + $parent_order->{'quantity'},
1607 warn "Cannot update parent order line, so do not cancel".
1612 # Recalculate tax_value
1616 tax_value_on_ordering = quantity * | . get_rounding_sql(q|ecost_tax_excluded|) . q| * tax_rate_on_ordering,
1617 tax_value_on_receiving = quantity * | . get_rounding_sql(q|unitprice_tax_excluded|) . q| * tax_rate_on_receiving
1618 WHERE ordernumber = ?
1619 |, undef, $parent_ordernumber);
1622 _cancel_items_receipt( $order_obj, $parent_ordernumber );
1625 DELETE FROM aqorders
1626 WHERE ordernumber = ?
1628 $sth = $dbh->prepare($query);
1629 $sth->execute($ordernumber);
1633 if( $order_obj->basket->effective_create_items eq 'ordering' ) {
1634 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1636 for my $in ( @itemnumbers ) {
1637 my $item = Koha::Items->find( $in ); # FIXME We do not need that, we already have Koha::Items from $order_obj->items
1638 my $biblio = $item->biblio;
1639 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber' );
1640 my $item_marc = C4::Items::GetMarcItem( $biblio->biblionumber, $in );
1641 for my $affect ( @affects ) {
1642 my ( $sf, $v ) = split q{=}, $affect, 2;
1643 foreach ( $item_marc->field($itemfield) ) {
1644 $_->update( $sf => $v );
1647 C4::Items::ModItemFromMarc( $item_marc, $biblio->biblionumber, $in );
1652 return $parent_ordernumber;
1655 sub _cancel_items_receipt {
1656 my ( $order, $parent_ordernumber ) = @_;
1657 $parent_ordernumber ||= $order->ordernumber;
1659 my $items = $order->items;
1660 if ( $order->basket->effective_create_items eq 'receiving' ) {
1661 # Remove items that were created at receipt
1663 DELETE FROM items, aqorders_items
1664 USING items, aqorders_items
1665 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1667 my $dbh = C4::Context->dbh;
1668 my $sth = $dbh->prepare($query);
1669 while ( my $item = $items->next ) {
1670 $sth->execute($item->itemnumber, $item->itemnumber);
1674 while ( my $item = $items->next ) {
1675 ModItemOrder($item->itemnumber, $parent_ordernumber);
1680 #------------------------------------------------------------#
1684 @results = &SearchOrders({
1685 ordernumber => $ordernumber,
1688 booksellerid => $booksellerid,
1689 basketno => $basketno,
1690 basketname => $basketname,
1691 basketgroupname => $basketgroupname,
1695 biblionumber => $biblionumber,
1696 budget_id => $budget_id
1699 Searches for orders filtered by criteria.
1701 C<$ordernumber> Finds matching orders or transferred orders by ordernumber.
1702 C<$search> Finds orders matching %$search% in title, author, or isbn.
1703 C<$owner> Finds order for the logged in user.
1704 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1705 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1708 C<@results> is an array of references-to-hash with the keys are fields
1709 from aqorders, biblio, biblioitems and aqbasket tables.
1714 my ( $params ) = @_;
1715 my $ordernumber = $params->{ordernumber};
1716 my $search = $params->{search};
1717 my $ean = $params->{ean};
1718 my $booksellerid = $params->{booksellerid};
1719 my $basketno = $params->{basketno};
1720 my $basketname = $params->{basketname};
1721 my $basketgroupname = $params->{basketgroupname};
1722 my $owner = $params->{owner};
1723 my $pending = $params->{pending};
1724 my $ordered = $params->{ordered};
1725 my $biblionumber = $params->{biblionumber};
1726 my $budget_id = $params->{budget_id};
1728 my $dbh = C4::Context->dbh;
1731 SELECT aqbasket.basketno,
1733 borrowers.firstname,
1736 biblioitems.biblioitemnumber,
1737 biblioitems.publishercode,
1738 biblioitems.publicationyear,
1739 aqbasket.authorisedby,
1740 aqbasket.booksellerid,
1742 aqbasket.creationdate,
1743 aqbasket.basketname,
1744 aqbasketgroups.id as basketgroupid,
1745 aqbasketgroups.name as basketgroupname,
1748 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1749 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1750 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1751 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1752 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1755 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1757 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1761 WHERE (datecancellationprinted is NULL)
1764 if ( $pending or $ordered ) {
1767 ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) )
1769 ( quantity > quantityreceived OR quantityreceived is NULL )
1773 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1781 my $userenv = C4::Context->userenv;
1782 if ( C4::Context->preference("IndependentBranches") ) {
1783 unless ( C4::Context->IsSuperLibrarian() ) {
1786 borrowers.branchcode = ?
1787 OR borrowers.branchcode = ''
1790 push @args, $userenv->{branch};
1794 if ( $ordernumber ) {
1795 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1796 push @args, ( $ordernumber, $ordernumber );
1798 if ( $biblionumber ) {
1799 $query .= 'AND aqorders.biblionumber = ?';
1800 push @args, $biblionumber;
1803 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1804 push @args, ("%$search%","%$search%","%$search%");
1807 $query .= ' AND biblioitems.ean = ?';
1810 if ( $booksellerid ) {
1811 $query .= 'AND aqbasket.booksellerid = ?';
1812 push @args, $booksellerid;
1815 $query .= 'AND aqbasket.basketno = ?';
1816 push @args, $basketno;
1819 $query .= 'AND aqbasket.basketname LIKE ?';
1820 push @args, "%$basketname%";
1822 if( $basketgroupname ) {
1823 $query .= ' AND aqbasketgroups.name LIKE ?';
1824 push @args, "%$basketgroupname%";
1828 $query .= ' AND aqbasket.authorisedby=? ';
1829 push @args, $userenv->{'number'};
1833 $query .= ' AND aqorders.budget_id = ?';
1834 push @args, $budget_id;
1837 $query .= ' ORDER BY aqbasket.basketno';
1839 my $sth = $dbh->prepare($query);
1840 $sth->execute(@args);
1841 return $sth->fetchall_arrayref({});
1844 #------------------------------------------------------------#
1846 =head3 TransferOrder
1848 my $newordernumber = TransferOrder($ordernumber, $basketno);
1850 Transfer an order line to a basket.
1851 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1852 to BOOKSELLER on DATE' and create new order with internal note
1853 'Transferred from BOOKSELLER on DATE'.
1854 Move all attached items to the new order.
1855 Received orders cannot be transferred.
1856 Return the ordernumber of created order.
1861 my ($ordernumber, $basketno) = @_;
1863 return unless ($ordernumber and $basketno);
1865 my $order = Koha::Acquisition::Orders->find( $ordernumber ) or return;
1866 return if $order->datereceived;
1868 $order = $order->unblessed;
1870 my $basket = GetBasket($basketno);
1871 return unless $basket;
1873 my $dbh = C4::Context->dbh;
1874 my ($query, $sth, $rv);
1878 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1879 WHERE ordernumber = ?
1881 $sth = $dbh->prepare($query);
1882 $rv = $sth->execute('cancelled', $ordernumber);
1884 delete $order->{'ordernumber'};
1885 delete $order->{parent_ordernumber};
1886 $order->{'basketno'} = $basketno;
1888 my $newordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber;
1891 UPDATE aqorders_items
1893 WHERE ordernumber = ?
1895 $sth = $dbh->prepare($query);
1896 $sth->execute($newordernumber, $ordernumber);
1899 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1902 $sth = $dbh->prepare($query);
1903 $sth->execute($ordernumber, $newordernumber);
1905 return $newordernumber;
1908 =head3 get_rounding_sql
1910 $rounding_sql = get_rounding_sql($column_name);
1912 returns the correct SQL routine based on OrderPriceRounding system preference.
1916 sub get_rounding_sql {
1917 my ( $round_string ) = @_;
1918 my $rounding_pref = C4::Context->preference('OrderPriceRounding') // q{};
1919 if ( $rounding_pref eq "nearest_cent" ) {
1920 return "CAST($round_string*100 AS SIGNED)/100";
1922 return $round_string;
1925 =head3 get_rounded_price
1927 $rounded_price = get_rounded_price( $price );
1929 returns a price rounded as specified in OrderPriceRounding system preference.
1933 sub get_rounded_price {
1935 my $rounding_pref = C4::Context->preference('OrderPriceRounding') // q{};
1936 if( $rounding_pref eq 'nearest_cent' ) {
1937 return Koha::Number::Price->new( $price )->round();
1943 =head2 FUNCTIONS ABOUT PARCELS
1947 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1949 get a lists of parcels.
1956 is the bookseller this function has to get parcels.
1959 To know on what criteria the results list has to be ordered.
1962 is the booksellerinvoicenumber.
1964 =item $datefrom & $dateto
1965 to know on what date this function has to filter its search.
1970 a pointer on a hash list containing parcel informations as such :
1976 =item Last operation
1978 =item Number of biblio
1980 =item Number of items
1987 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1988 my $dbh = C4::Context->dbh;
1989 my @query_params = ();
1991 SELECT aqinvoices.invoicenumber,
1992 datereceived,purchaseordernumber,
1993 count(DISTINCT biblionumber) AS biblio,
1994 sum(quantity) AS itemsexpected,
1995 sum(quantityreceived) AS itemsreceived
1996 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1997 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1998 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
2000 push @query_params, $bookseller;
2002 if ( defined $code ) {
2003 $strsth .= ' and aqinvoices.invoicenumber like ? ';
2004 # add a % to the end of the code to allow stemming.
2005 push @query_params, "$code%";
2008 if ( defined $datefrom ) {
2009 $strsth .= ' and datereceived >= ? ';
2010 push @query_params, $datefrom;
2013 if ( defined $dateto ) {
2014 $strsth .= 'and datereceived <= ? ';
2015 push @query_params, $dateto;
2018 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
2020 # can't use a placeholder to place this column name.
2021 # but, we could probably be checking to make sure it is a column that will be fetched.
2022 $strsth .= "order by $order " if ($order);
2024 my $sth = $dbh->prepare($strsth);
2026 $sth->execute( @query_params );
2027 my $results = $sth->fetchall_arrayref({});
2031 #------------------------------------------------------------#
2035 \@order_loop = GetHistory( %params );
2037 Retreives some acquisition history information
2047 basket - search both basket name and number
2048 booksellerinvoicenumber
2051 orderstatus (note that orderstatus '' will retrieve orders
2052 of any status except cancelled)
2056 get_canceled_order (if set to a true value, cancelled orders will
2060 $order_loop is a list of hashrefs that each look like this:
2062 'author' => 'Twain, Mark',
2064 'biblionumber' => '215',
2066 'creationdate' => 'MM/DD/YYYY',
2067 'datereceived' => undef,
2070 'invoicenumber' => undef,
2072 'ordernumber' => '1',
2074 'quantityreceived' => undef,
2075 'title' => 'The Adventures of Huckleberry Finn',
2076 'managing_library' => 'CPL'
2077 'is_standing' => '1'
2083 # don't run the query if there are no parameters (list would be too long for sure !)
2084 croak "No search params" unless @_;
2086 my $title = $params{title};
2087 my $author = $params{author};
2088 my $isbn = $params{isbn};
2089 my $issn = $params{issn};
2090 my $ean = $params{ean};
2091 my $name = $params{name};
2092 my $internalnote = $params{internalnote};
2093 my $vendornote = $params{vendornote};
2094 my $from_placed_on = $params{from_placed_on};
2095 my $to_placed_on = $params{to_placed_on};
2096 my $basket = $params{basket};
2097 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2098 my $basketgroupname = $params{basketgroupname};
2099 my $budget = $params{budget};
2100 my $orderstatus = $params{orderstatus};
2101 my $is_standing = $params{is_standing};
2102 my $biblionumber = $params{biblionumber};
2103 my $get_canceled_order = $params{get_canceled_order} || 0;
2104 my $ordernumber = $params{ordernumber};
2105 my $search_children_too = $params{search_children_too} || 0;
2106 my $created_by = $params{created_by} || [];
2107 my $managing_library = $params{managing_library};
2108 my $ordernumbers = $params{ordernumbers} || [];
2109 my $additional_fields = $params{additional_fields} // [];
2112 my $total_qtyreceived = 0;
2113 my $total_price = 0;
2115 #get variation of isbn
2119 if ( C4::Context->preference("SearchWithISBNVariations") ){
2120 @isbns = C4::Koha::GetVariationsOfISBN( $isbn );
2121 foreach my $isb (@isbns){
2122 push @isbn_params, '?';
2127 push @isbn_params, '?';
2131 #get variation of issn
2135 if ( C4::Context->preference("SearchWithISSNVariations") ){
2136 @issns = C4::Koha::GetVariationsOfISSN( $issn );
2137 push @issn_params, ('?') x @issns;
2141 push @issn_params, '?';
2145 my $dbh = C4::Context->dbh;
2148 COALESCE(biblio.title, deletedbiblio.title) AS title,
2149 COALESCE(biblio.author, deletedbiblio.author) AS author,
2150 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2151 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2153 aqbasket.basketname,
2154 aqbasket.basketgroupid,
2155 aqbasket.authorisedby,
2156 aqbasket.is_standing,
2157 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2158 branch as managing_library,
2159 aqbasketgroups.name as groupname,
2161 aqbasket.creationdate,
2162 aqorders.datereceived,
2164 aqorders.quantityreceived,
2166 aqorders.ordernumber,
2168 aqinvoices.invoicenumber,
2169 aqbooksellers.id as id,
2170 aqorders.biblionumber,
2171 aqorders.orderstatus,
2172 aqorders.parent_ordernumber,
2173 aqorders.order_internalnote,
2174 aqorders.order_vendornote,
2175 aqbudgets.budget_name
2177 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2180 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2181 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2182 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2183 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2184 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2185 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2186 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2187 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2188 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2189 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2192 $query .= " WHERE 1 ";
2194 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2195 $query .= " AND datecancellationprinted IS NULL ";
2198 my @query_params = ();
2200 if ( $biblionumber ) {
2201 $query .= " AND biblio.biblionumber = ?";
2202 push @query_params, $biblionumber;
2206 $query .= " AND biblio.title LIKE ? ";
2207 $title =~ s/\s+/%/g;
2208 push @query_params, "%$title%";
2212 $query .= " AND biblio.author LIKE ? ";
2213 push @query_params, "%$author%";
2217 $query .= " AND ( biblioitems.isbn LIKE " . join (" OR biblioitems.isbn LIKE ", @isbn_params ) . ")";
2218 foreach my $isb (@isbns){
2219 push @query_params, "%$isb%";
2224 $query .= " AND ( biblioitems.issn LIKE " . join (" OR biblioitems.issn LIKE ", @issn_params ) . ")";
2225 foreach my $isn (@issns){
2226 push @query_params, "%$isn%";
2231 $query .= " AND biblioitems.ean = ? ";
2232 push @query_params, "$ean";
2235 $query .= " AND aqbooksellers.name LIKE ? ";
2236 push @query_params, "%$name%";
2240 $query .= " AND aqbudgets.budget_id = ? ";
2241 push @query_params, "$budget";
2244 if ( $from_placed_on ) {
2245 $query .= " AND creationdate >= ? ";
2246 push @query_params, $from_placed_on;
2249 if ( $to_placed_on ) {
2250 $query .= " AND creationdate <= ? ";
2251 push @query_params, $to_placed_on;
2254 if ( defined $orderstatus and $orderstatus ne '') {
2255 $query .= " AND aqorders.orderstatus = ? ";
2256 push @query_params, "$orderstatus";
2259 if ( $is_standing ) {
2260 $query .= " AND is_standing = ? ";
2261 push @query_params, $is_standing;
2265 if ($basket =~ m/^\d+$/) {
2266 $query .= " AND aqorders.basketno = ? ";
2267 push @query_params, $basket;
2269 $query .= " AND aqbasket.basketname LIKE ? ";
2270 push @query_params, "%$basket%";
2274 if ( $internalnote ) {
2275 $query .= " AND aqorders.order_internalnote LIKE ? ";
2276 push @query_params, "%$internalnote%";
2279 if ( $vendornote ) {
2280 $query .= " AND aqorders.order_vendornote LIKE ?";
2281 push @query_params, "%$vendornote%";
2284 if ($booksellerinvoicenumber) {
2285 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2286 push @query_params, "%$booksellerinvoicenumber%";
2289 if ($basketgroupname) {
2290 $query .= " AND aqbasketgroups.name LIKE ? ";
2291 push @query_params, "%$basketgroupname%";
2295 $query .= " AND (aqorders.ordernumber = ? ";
2296 push @query_params, $ordernumber;
2297 if ($search_children_too) {
2298 $query .= " OR aqorders.parent_ordernumber = ? ";
2299 push @query_params, $ordernumber;
2304 if ( @$created_by ) {
2305 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2306 push @query_params, @$created_by;
2309 if ( $managing_library ) {
2310 $query .= " AND aqbasket.branch = ? ";
2311 push @query_params, $managing_library;
2314 if ( @$ordernumbers ) {
2315 $query .= ' AND (aqorders.ordernumber IN ( ' . join (',', ('?') x @$ordernumbers ) . '))';
2316 push @query_params, @$ordernumbers;
2318 if ( @$additional_fields ) {
2319 my @baskets = Koha::Acquisition::Baskets->filter_by_additional_fields($additional_fields)->as_list;
2321 return [] unless @baskets;
2323 # No parameterization because record IDs come directly from DB
2324 $query .= ' AND aqbasket.basketno IN ( ' . join( ',', map { $_->basketno } @baskets ) . ' )';
2327 if ( C4::Context->preference("IndependentBranches") ) {
2328 unless ( C4::Context->IsSuperLibrarian() ) {
2329 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2330 push @query_params, C4::Context->userenv->{branch};
2333 $query .= " ORDER BY id";
2335 return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2338 =head2 GetRecentAcqui
2340 $results = GetRecentAcqui($days);
2342 C<$results> is a ref to a table which contains hashref
2346 sub GetRecentAcqui {
2348 my $dbh = C4::Context->dbh;
2352 ORDER BY timestamp DESC
2355 my $sth = $dbh->prepare($query);
2357 my $results = $sth->fetchall_arrayref({});
2361 #------------------------------------------------------------#
2365 &AddClaim($ordernumber);
2367 Add a claim for an order
2372 my ($ordernumber) = @_;
2373 my $dbh = C4::Context->dbh;
2376 claims_count = claims_count + 1,
2377 claimed_date = CURDATE()
2378 WHERE ordernumber = ?
2380 my $sth = $dbh->prepare($query);
2381 $sth->execute($ordernumber);
2386 my @invoices = GetInvoices(
2387 invoicenumber => $invoicenumber,
2388 supplierid => $supplierid,
2389 suppliername => $suppliername,
2390 shipmentdatefrom => $shipmentdatefrom, # ISO format
2391 shipmentdateto => $shipmentdateto, # ISO format
2392 billingdatefrom => $billingdatefrom, # ISO format
2393 billingdateto => $billingdateto, # ISO format
2394 isbneanissn => $isbn_or_ean_or_issn,
2397 publisher => $publisher,
2398 publicationyear => $publicationyear,
2399 branchcode => $branchcode,
2400 order_by => $order_by
2403 Return a list of invoices that match all given criteria.
2405 $order_by is "column_name (asc|desc)", where column_name is any of
2406 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2407 'shipmentcost', 'shipmentcost_budgetid'.
2409 asc is the default if omitted
2416 my $additional_fields = $args{additional_fields} // [];
2417 my $matching_invoice_ids_for_additional_fields = [];
2418 if ( @$additional_fields ) {
2419 my @invoices = Koha::Acquisition::Invoices->filter_by_additional_fields($additional_fields)->as_list;
2421 return () unless @invoices;
2423 $matching_invoice_ids_for_additional_fields = [ map {
2428 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2429 closedate shipmentcost shipmentcost_budgetid);
2431 my $dbh = C4::Context->dbh;
2433 SELECT aqinvoices.invoiceid, aqinvoices.invoicenumber, aqinvoices.booksellerid, aqinvoices.shipmentdate, aqinvoices.billingdate, aqinvoices.closedate, aqinvoices.shipmentcost, aqinvoices.shipmentcost_budgetid, aqinvoices.message_id,
2434 aqbooksellers.name AS suppliername,
2437 aqorders.datereceived IS NOT NULL,
2438 aqorders.biblionumber,
2441 ) AS receivedbiblios,
2444 aqorders.subscriptionid IS NOT NULL,
2445 aqorders.subscriptionid,
2448 ) AS is_linked_to_subscriptions,
2449 SUM(aqorders.quantityreceived) AS receiveditems
2451 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2452 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2453 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2454 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2455 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2456 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2457 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2462 if($args{supplierid}) {
2463 push @bind_strs, " aqinvoices.booksellerid = ? ";
2464 push @bind_args, $args{supplierid};
2466 if($args{invoicenumber}) {
2467 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2468 push @bind_args, "%$args{invoicenumber}%";
2470 if($args{suppliername}) {
2471 push @bind_strs, " aqbooksellers.name LIKE ? ";
2472 push @bind_args, "%$args{suppliername}%";
2474 if($args{shipmentdatefrom}) {
2475 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2476 push @bind_args, $args{shipmentdatefrom};
2478 if($args{shipmentdateto}) {
2479 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2480 push @bind_args, $args{shipmentdateto};
2482 if($args{billingdatefrom}) {
2483 push @bind_strs, " aqinvoices.billingdate >= ? ";
2484 push @bind_args, $args{billingdatefrom};
2486 if($args{billingdateto}) {
2487 push @bind_strs, " aqinvoices.billingdate <= ? ";
2488 push @bind_args, $args{billingdateto};
2490 if($args{isbneanissn}) {
2491 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2492 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2495 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2496 push @bind_args, $args{title};
2499 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2500 push @bind_args, $args{author};
2502 if($args{publisher}) {
2503 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2504 push @bind_args, $args{publisher};
2506 if($args{publicationyear}) {
2507 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2508 push @bind_args, $args{publicationyear}, $args{publicationyear};
2510 if($args{branchcode}) {
2511 push @bind_strs, " borrowers.branchcode = ? ";
2512 push @bind_args, $args{branchcode};
2514 if($args{message_id}) {
2515 push @bind_strs, " aqinvoices.message_id = ? ";
2516 push @bind_args, $args{message_id};
2519 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2521 # Handle additional fields filtering
2522 if ( @$additional_fields ) {
2523 my $operator = ' WHERE';
2524 if ( @bind_strs ) { # there's a WHERE already
2527 $query .= "$operator aqinvoices.invoiceid IN ("
2528 . join( ', ', @$matching_invoice_ids_for_additional_fields )
2532 $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";
2534 if($args{order_by}) {
2535 my ($column, $direction) = split / /, $args{order_by};
2536 if(grep { $_ eq $column } @columns) {
2537 $direction ||= 'ASC';
2538 $query .= " ORDER BY $column $direction";
2542 my $sth = $dbh->prepare($query);
2543 $sth->execute(@bind_args);
2545 my $results = $sth->fetchall_arrayref({});
2551 my $invoice = GetInvoice($invoiceid);
2553 Get informations about invoice with given $invoiceid
2555 Return a hash filled with aqinvoices.* fields
2560 my ($invoiceid) = @_;
2563 return unless $invoiceid;
2565 my $dbh = C4::Context->dbh;
2571 my $sth = $dbh->prepare($query);
2572 $sth->execute($invoiceid);
2574 $invoice = $sth->fetchrow_hashref;
2578 =head3 GetInvoiceDetails
2580 my $invoice = GetInvoiceDetails($invoiceid)
2582 Return informations about an invoice + the list of related order lines
2584 Orders informations are in $invoice->{orders} (array ref)
2588 sub GetInvoiceDetails {
2589 my ($invoiceid) = @_;
2591 if ( !defined $invoiceid ) {
2592 carp 'GetInvoiceDetails called without an invoiceid';
2596 my $dbh = C4::Context->dbh;
2598 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2600 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2603 my $sth = $dbh->prepare($query);
2604 $sth->execute($invoiceid);
2606 my $invoice = $sth->fetchrow_hashref;
2611 biblio.copyrightdate,
2613 biblioitems.publishercode,
2614 biblioitems.publicationyear,
2615 aqbasket.basketname,
2616 aqbasketgroups.id AS basketgroupid,
2617 aqbasketgroups.name AS basketgroupname
2619 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2620 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2621 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2622 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2625 $sth = $dbh->prepare($query);
2626 $sth->execute($invoiceid);
2627 $invoice->{orders} = $sth->fetchall_arrayref({});
2628 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2635 my $invoiceid = AddInvoice(
2636 invoicenumber => $invoicenumber,
2637 booksellerid => $booksellerid,
2638 shipmentdate => $shipmentdate,
2639 billingdate => $billingdate,
2640 closedate => $closedate,
2641 shipmentcost => $shipmentcost,
2642 shipmentcost_budgetid => $shipmentcost_budgetid
2645 Create a new invoice and return its id or undef if it fails.
2652 return unless(%invoice and $invoice{invoicenumber});
2654 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2655 closedate shipmentcost shipmentcost_budgetid message_id);
2659 foreach my $key (keys %invoice) {
2660 if(0 < grep { $_ eq $key } @columns) {
2661 push @set_strs, "$key = ?";
2662 push @set_args, ($invoice{$key} || undef);
2668 my $dbh = C4::Context->dbh;
2669 my $query = "INSERT INTO aqinvoices SET ";
2670 $query .= join (",", @set_strs);
2671 my $sth = $dbh->prepare($query);
2672 $rv = $sth->execute(@set_args);
2674 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2683 invoiceid => $invoiceid, # Mandatory
2684 invoicenumber => $invoicenumber,
2685 booksellerid => $booksellerid,
2686 shipmentdate => $shipmentdate,
2687 billingdate => $billingdate,
2688 closedate => $closedate,
2689 shipmentcost => $shipmentcost,
2690 shipmentcost_budgetid => $shipmentcost_budgetid
2693 Modify an invoice, invoiceid is mandatory.
2695 Return undef if it fails.
2702 return unless(%invoice and $invoice{invoiceid});
2704 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2705 closedate shipmentcost shipmentcost_budgetid);
2709 foreach my $key (keys %invoice) {
2710 if(0 < grep { $_ eq $key } @columns) {
2711 push @set_strs, "$key = ?";
2712 push @set_args, ($invoice{$key} || undef);
2716 my $dbh = C4::Context->dbh;
2717 my $query = "UPDATE aqinvoices SET ";
2718 $query .= join(",", @set_strs);
2719 $query .= " WHERE invoiceid = ?";
2721 my $sth = $dbh->prepare($query);
2722 $sth->execute(@set_args, $invoice{invoiceid});
2727 CloseInvoice($invoiceid);
2731 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2736 my ($invoiceid) = @_;
2738 return unless $invoiceid;
2740 my $dbh = C4::Context->dbh;
2743 SET closedate = CAST(NOW() AS DATE)
2746 my $sth = $dbh->prepare($query);
2747 $sth->execute($invoiceid);
2750 =head3 ReopenInvoice
2752 ReopenInvoice($invoiceid);
2756 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => $closedate );
2761 my ($invoiceid) = @_;
2763 return unless $invoiceid;
2765 my $dbh = C4::Context->dbh;
2768 SET closedate = NULL
2771 my $sth = $dbh->prepare($query);
2772 $sth->execute($invoiceid);
2777 DelInvoice($invoiceid);
2779 Delete an invoice if there are no items attached to it.
2784 my ($invoiceid) = @_;
2786 return unless $invoiceid;
2788 my $dbh = C4::Context->dbh;
2794 my $sth = $dbh->prepare($query);
2795 $sth->execute($invoiceid);
2796 my $res = $sth->fetchrow_arrayref;
2797 if ( $res && $res->[0] == 0 ) {
2799 DELETE FROM aqinvoices
2802 my $sth = $dbh->prepare($query);
2803 return ( $sth->execute($invoiceid) > 0 );
2808 =head3 MergeInvoices
2810 MergeInvoices($invoiceid, \@sourceids);
2812 Merge the invoices identified by the IDs in \@sourceids into
2813 the invoice identified by $invoiceid.
2818 my ($invoiceid, $sourceids) = @_;
2820 return unless $invoiceid;
2821 foreach my $sourceid (@$sourceids) {
2822 next if $sourceid == $invoiceid;
2823 my $source = GetInvoiceDetails($sourceid);
2824 foreach my $order (@{$source->{'orders'}}) {
2825 $order->{'invoiceid'} = $invoiceid;
2828 DelInvoice($source->{'invoiceid'});
2833 =head3 GetBiblioCountByBasketno
2835 $biblio_count = &GetBiblioCountByBasketno($basketno);
2837 Looks up the biblio's count that has basketno value $basketno
2843 sub GetBiblioCountByBasketno {
2844 my ($basketno) = @_;
2845 my $dbh = C4::Context->dbh;
2847 SELECT COUNT( DISTINCT( biblionumber ) )
2850 AND datecancellationprinted IS NULL
2853 my $sth = $dbh->prepare($query);
2854 $sth->execute($basketno);
2855 return $sth->fetchrow;
2858 =head3 GetOrderUsers
2860 $order_users_ids = &GetOrderUsers($ordernumber);
2862 Returns a list of all borrowernumbers that are in order users list
2867 my ($ordernumber) = @_;
2869 return unless $ordernumber;
2872 SELECT borrowernumber
2874 WHERE ordernumber = ?
2876 my $dbh = C4::Context->dbh;
2877 my $sth = $dbh->prepare($query);
2878 $sth->execute($ordernumber);
2879 my $results = $sth->fetchall_arrayref( {} );
2881 my @borrowernumbers;
2882 foreach (@$results) {
2883 push @borrowernumbers, $_->{'borrowernumber'};
2886 return @borrowernumbers;
2889 =head3 ModOrderUsers
2891 my @order_users_ids = (1, 2, 3);
2892 &ModOrderUsers($ordernumber, @basketusers_ids);
2894 Delete all users from order users list, and add users in C<@order_users_ids>
2900 my ( $ordernumber, @order_users_ids ) = @_;
2902 return unless $ordernumber;
2904 my $dbh = C4::Context->dbh;
2906 DELETE FROM aqorder_users
2907 WHERE ordernumber = ?
2909 my $sth = $dbh->prepare($query);
2910 $sth->execute($ordernumber);
2913 INSERT INTO aqorder_users (ordernumber, borrowernumber)
2916 $sth = $dbh->prepare($query);
2917 foreach my $order_user_id (@order_users_ids) {
2918 $sth->execute( $ordernumber, $order_user_id );
2922 sub NotifyOrderUsers {
2923 my ($ordernumber) = @_;
2925 my @borrowernumbers = GetOrderUsers($ordernumber);
2926 return unless @borrowernumbers;
2928 my $order = GetOrder( $ordernumber );
2929 for my $borrowernumber (@borrowernumbers) {
2930 my $patron = Koha::Patrons->find( $borrowernumber );
2931 my $library = $patron->library->unblessed;
2932 my $biblio = Koha::Biblios->find( $order->{biblionumber} )->unblessed;
2933 my $letter = C4::Letters::GetPreparedLetter(
2934 module => 'acquisition',
2935 letter_code => 'ACQ_NOTIF_ON_RECEIV',
2936 branchcode => $library->{branchcode},
2937 lang => $patron->lang,
2939 'branches' => $library,
2940 'borrowers' => $patron->unblessed,
2941 'biblio' => $biblio,
2942 'aqorders' => $order,
2946 C4::Letters::EnqueueLetter(
2949 borrowernumber => $borrowernumber,
2950 LibraryName => C4::Context->preference("LibraryName"),
2951 message_transport_type => 'email',
2953 ) or warn "can't enqueue letter $letter";
2958 =head3 FillWithDefaultValues
2960 FillWithDefaultValues( $marc_record, $params );
2962 This will update the record with default value defined in the ACQ framework.
2963 For all existing fields, if a default value exists and there are no subfield, it will be created.
2964 If the field does not exist, it will be created too.
2966 If the parameter only_mandatory => 1 is passed via $params, only the mandatory
2967 defaults are being applied to the record.
2971 sub FillWithDefaultValues {
2972 my ( $record, $params ) = @_;
2973 my $mandatory = $params->{only_mandatory};
2974 my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ', { unsafe => 1 } );
2977 C4::Biblio::GetMarcFromKohaField( 'items.itemnumber' );
2978 for my $tag ( sort keys %$tagslib ) {
2980 next if $tag == $itemfield;
2981 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
2982 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
2983 next if $mandatory && !$tagslib->{$tag}{$subfield}{mandatory};
2984 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
2985 if ( defined $defaultvalue and $defaultvalue ne '' ) {
2986 my @fields = $record->field($tag);
2988 for my $field (@fields) {
2989 if ( $field->is_control_field ) {
2990 $field->update($defaultvalue) if not defined $field->data;
2992 elsif ( not defined $field->subfield($subfield) ) {
2993 $field->add_subfields(
2994 $subfield => $defaultvalue );
2999 if ( $tag < 10 ) { # is_control_field
3000 $record->insert_fields_ordered(
3007 $record->insert_fields_ordered(
3009 $tag, '', '', $subfield => $defaultvalue
3025 Koha Development Team <http://koha-community.org/>