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>.
30 use C4::Log qw(logaction);
31 use C4::Templates qw(gettemplate);
32 use Koha::DateUtils qw( dt_from_string output_pref );
33 use Koha::Acquisition::Baskets;
34 use Koha::Acquisition::Booksellers;
35 use Koha::Acquisition::Orders;
39 use Koha::Number::Price;
41 use Koha::CsvProfiles;
51 use vars qw(@ISA @EXPORT);
57 &GetBasket &NewBasket &ReopenBasket &ModBasket
58 &GetBasketAsCSV &GetBasketGroupAsCSV
59 &GetBasketsByBookseller &GetBasketsByBasketgroup
60 &GetBasketsInfosByBookseller
62 &GetBasketUsers &ModBasketUsers
67 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
68 &GetBasketgroups &ReOpenBasketgroup
70 &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
71 &GetOrderFromItemnumber
72 &SearchOrders &GetHistory &GetRecentAcqui
73 &ModReceiveOrder &CancelReceipt
90 &GetBiblioCountByBasketno
96 &FillWithDefaultValues
107 sub GetOrderFromItemnumber {
108 my ($itemnumber) = @_;
109 my $dbh = C4::Context->dbh;
112 SELECT * from aqorders LEFT JOIN aqorders_items
113 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
114 WHERE itemnumber = ? |;
116 my $sth = $dbh->prepare($query);
120 $sth->execute($itemnumber);
122 my $order = $sth->fetchrow_hashref;
129 C4::Acquisition - Koha functions for dealing with orders and acquisitions
137 The functions in this module deal with acquisitions, managing book
138 orders, basket and parcels.
142 =head2 FUNCTIONS ABOUT BASKETS
146 $aqbasket = &GetBasket($basketnumber);
148 get all basket informations in aqbasket for a given basket
150 B<returns:> informations for a given basket returned as a hashref.
156 my $dbh = C4::Context->dbh;
159 concat( b.firstname,' ',b.surname) AS authorisedbyname
161 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
164 my $sth=$dbh->prepare($query);
165 $sth->execute($basketno);
166 my $basket = $sth->fetchrow_hashref;
170 #------------------------------------------------------------#
174 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
175 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace, $is_standing, $create_items );
177 Create a new basket in aqbasket table
181 =item C<$booksellerid> is a foreign key in the aqbasket table
183 =item C<$authorizedby> is the username of who created the basket
187 The other parameters are optional, see ModBasketHeader for more info on them.
192 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
193 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
194 $billingplace, $is_standing, $create_items ) = @_;
195 my $dbh = C4::Context->dbh;
197 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
198 . 'VALUES (now(),?,?)';
199 $dbh->do( $query, {}, $booksellerid, $authorisedby );
201 my $basket = $dbh->{mysql_insertid};
202 $basketname ||= q{}; # default to empty strings
204 $basketbooksellernote ||= q{};
205 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
206 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items );
208 # Log the basket creation
209 if (C4::Context->preference("AcqLog")) {
210 logaction('ACQUISITIONS', 'ADD_BASKET', $basket);
218 &ReopenBasket($basketno);
226 my $dbh = C4::Context->dbh;
227 $dbh->do( q{UPDATE aqbasket SET closedate=NULL WHERE basketno=?}, {}, $basketno );
231 SET orderstatus = 'new'
233 AND orderstatus NOT IN ( 'complete', 'cancelled' )
238 #------------------------------------------------------------#
240 =head3 GetBasketAsCSV
242 &GetBasketAsCSV($basketno);
244 Export a basket as CSV
246 $cgi parameter is needed for column name translation
251 my ($basketno, $cgi, $csv_profile_id) = @_;
252 my $basket = GetBasket($basketno);
253 my @orders = GetOrders($basketno);
254 my $contract = GetContract({
255 contractnumber => $basket->{'contractnumber'}
258 my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
260 if ($csv_profile_id) {
261 my $csv_profile = Koha::CsvProfiles->find( $csv_profile_id );
262 Koha::Exceptions::ObjectNotFound->throw( 'There is no valid csv profile given') unless $csv_profile;
264 my $csv = Text::CSV_XS->new({'quote_char'=>'"','escape_char'=>'"','sep_char'=>$csv_profile->csv_separator,'binary'=>1});
265 my $csv_profile_content = $csv_profile->content;
266 my ( @headers, @fields );
267 while ( $csv_profile_content =~ /
270 ([^\|]*) # fieldname (table.row or row)
274 my $field = ($2 eq '') ? $1 : $2;
276 $header =~ s/^\s+|\s+$//g; # Trim whitespaces
277 push @headers, $header;
279 $field =~ s/[^\.]*\.{1}//; # Remove the table name if exists.
280 $field =~ s/^\s+|\s+$//g; # Trim whitespaces
281 push @fields, $field;
283 for my $order (@orders) {
285 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
286 my $biblioitem = $biblio->biblioitem;
287 $order = { %$order, %{ $biblioitem->unblessed } };
289 $order = {%$order, %$contract};
291 $order = {%$order, %$basket, %{ $biblio->unblessed }};
292 for my $field (@fields) {
293 push @row, $order->{$field};
297 my $content = join( $csv_profile->csv_separator, @headers ) . "\n";
298 for my $row ( @rows ) {
299 $csv->combine(@$row);
300 my $string = $csv->string;
301 $content .= $string . "\n";
306 foreach my $order (@orders) {
307 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
308 my $biblioitem = $biblio->biblioitem;
310 contractname => $contract->{'contractname'},
311 ordernumber => $order->{'ordernumber'},
312 entrydate => $order->{'entrydate'},
313 isbn => $order->{'isbn'},
314 author => $biblio->author,
315 title => $biblio->title,
316 publicationyear => $biblioitem->publicationyear,
317 publishercode => $biblioitem->publishercode,
318 collectiontitle => $biblioitem->collectiontitle,
319 notes => $order->{'order_vendornote'},
320 quantity => $order->{'quantity'},
321 rrp => $order->{'rrp'},
323 for my $place ( qw( deliveryplace billingplace ) ) {
324 if ( my $library = Koha::Libraries->find( $row->{deliveryplace} ) ) {
325 $row->{$place} = $library->branchname
329 contractname author title publishercode collectiontitle notes
330 deliveryplace billingplace
332 # Double the quotes to not be interpreted as a field end
333 $row->{$_} =~ s/"/""/g if $row->{$_};
339 if(defined $a->{publishercode} and defined $b->{publishercode}) {
340 $a->{publishercode} cmp $b->{publishercode};
344 $template->param(rows => \@rows);
346 return $template->output;
351 =head3 GetBasketGroupAsCSV
353 &GetBasketGroupAsCSV($basketgroupid);
355 Export a basket group as CSV
357 $cgi parameter is needed for column name translation
361 sub GetBasketGroupAsCSV {
362 my ($basketgroupid, $cgi) = @_;
363 my $baskets = GetBasketsByBasketgroup($basketgroupid);
365 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
368 for my $basket (@$baskets) {
369 my @orders = GetOrders( $basket->{basketno} );
370 my $contract = GetContract({
371 contractnumber => $basket->{contractnumber}
373 my $bookseller = Koha::Acquisition::Booksellers->find( $basket->{booksellerid} );
374 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
376 foreach my $order (@orders) {
377 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
378 my $biblioitem = $biblio->biblioitem;
380 clientnumber => $bookseller->accountnumber,
381 basketname => $basket->{basketname},
382 ordernumber => $order->{ordernumber},
383 author => $biblio->author,
384 title => $biblio->title,
385 publishercode => $biblioitem->publishercode,
386 publicationyear => $biblioitem->publicationyear,
387 collectiontitle => $biblioitem->collectiontitle,
388 isbn => $order->{isbn},
389 quantity => $order->{quantity},
390 rrp_tax_included => $order->{rrp_tax_included},
391 rrp_tax_excluded => $order->{rrp_tax_excluded},
392 discount => $bookseller->discount,
393 ecost_tax_included => $order->{ecost_tax_included},
394 ecost_tax_excluded => $order->{ecost_tax_excluded},
395 notes => $order->{order_vendornote},
396 entrydate => $order->{entrydate},
397 booksellername => $bookseller->name,
398 bookselleraddress => $bookseller->address1,
399 booksellerpostal => $bookseller->postal,
400 contractnumber => $contract->{contractnumber},
401 contractname => $contract->{contractname},
404 basketgroupdeliveryplace => $basketgroup->{deliveryplace},
405 basketgroupbillingplace => $basketgroup->{billingplace},
406 basketdeliveryplace => $basket->{deliveryplace},
407 basketbillingplace => $basket->{billingplace},
409 for my $place (qw( basketgroupdeliveryplace basketgroupbillingplace basketdeliveryplace basketbillingplace )) {
410 if ( my $library = Koha::Libraries->find( $temp->{$place} ) ) {
411 $row->{$place} = $library->branchname;
415 basketname author title publishercode collectiontitle notes
416 booksellername bookselleraddress booksellerpostal contractname
417 basketgroupdeliveryplace basketgroupbillingplace
418 basketdeliveryplace basketbillingplace
420 # Double the quotes to not be interpreted as a field end
421 $row->{$_} =~ s/"/""/g if $row->{$_};
426 $template->param(rows => \@rows);
428 return $template->output;
432 =head3 CloseBasketgroup
434 &CloseBasketgroup($basketgroupno);
440 sub CloseBasketgroup {
441 my ($basketgroupno) = @_;
442 my $dbh = C4::Context->dbh;
443 my $sth = $dbh->prepare("
444 UPDATE aqbasketgroups
448 $sth->execute($basketgroupno);
451 #------------------------------------------------------------#
453 =head3 ReOpenBaskergroup($basketgroupno)
455 &ReOpenBaskergroup($basketgroupno);
461 sub ReOpenBasketgroup {
462 my ($basketgroupno) = @_;
463 my $dbh = C4::Context->dbh;
464 my $sth = $dbh->prepare("
465 UPDATE aqbasketgroups
469 $sth->execute($basketgroupno);
472 #------------------------------------------------------------#
476 &ModBasket($basketinfo);
478 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
482 =item C<$basketno> is the primary key of the basket in the aqbasket table.
489 my $basketinfo = shift;
490 my $query = "UPDATE aqbasket SET ";
492 foreach my $key (keys %$basketinfo){
493 if ($key ne 'basketno'){
494 $query .= "$key=?, ";
495 push(@params, $basketinfo->{$key} || undef );
498 # get rid of the "," at the end of $query
499 if (substr($query, length($query)-2) eq ', '){
504 $query .= "WHERE basketno=?";
505 push(@params, $basketinfo->{'basketno'});
506 my $dbh = C4::Context->dbh;
507 my $sth = $dbh->prepare($query);
508 $sth->execute(@params);
510 # Log the basket update
511 if (C4::Context->preference("AcqLog")) {
512 my $infos = $basketinfo->{borrowernumber} ?
513 sprintf("%010d", $basketinfo->{borrowernumber}) :
518 $basketinfo->{'basketno'},
526 #------------------------------------------------------------#
528 =head3 ModBasketHeader
530 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
532 Modifies a basket's header.
536 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
538 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
540 =item C<$note> is the "note" field in the "aqbasket" table;
542 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
544 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
546 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
548 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
550 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
552 =item C<$is_standing> is the "is_standing" field in the aqbasket table.
554 =item C<$create_items> should be set to 'ordering', 'receiving' or 'cataloguing' (or undef, in which
555 case the AcqCreateItem syspref takes precedence).
561 sub ModBasketHeader {
562 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items, $borrowernumber) = @_;
567 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?, is_standing=?, create_items=?
571 my $dbh = C4::Context->dbh;
572 my $sth = $dbh->prepare($query);
573 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items || undef, $basketno);
575 if ( $contractnumber ) {
576 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
577 my $sth2 = $dbh->prepare($query2);
578 $sth2->execute($contractnumber,$basketno);
584 #------------------------------------------------------------#
586 =head3 GetBasketsByBookseller
588 @results = &GetBasketsByBookseller($booksellerid, $extra);
590 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
594 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
596 =item C<$extra> is the extra sql parameters, can be
598 $extra->{groupby}: group baskets by column
599 ex. $extra->{groupby} = aqbasket.basketgroupid
600 $extra->{orderby}: order baskets by column
601 $extra->{limit}: limit number of results (can be helpful for pagination)
607 sub GetBasketsByBookseller {
608 my ($booksellerid, $extra) = @_;
609 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
611 if ($extra->{groupby}) {
612 $query .= " GROUP by $extra->{groupby}";
614 if ($extra->{orderby}){
615 $query .= " ORDER by $extra->{orderby}";
617 if ($extra->{limit}){
618 $query .= " LIMIT $extra->{limit}";
621 my $dbh = C4::Context->dbh;
622 my $sth = $dbh->prepare($query);
623 $sth->execute($booksellerid);
624 return $sth->fetchall_arrayref({});
627 =head3 GetBasketsInfosByBookseller
629 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
631 The optional second parameter allbaskets is a boolean allowing you to
632 select all baskets from the supplier; by default only active baskets (open or
633 closed but still something to receive) are returned.
635 Returns in a arrayref of hashref all about booksellers baskets, plus:
636 total_biblios: Number of distinct biblios in basket
637 total_items: Number of items in basket
638 expected_items: Number of non-received items in basket
642 sub GetBasketsInfosByBookseller {
643 my ($supplierid, $allbaskets) = @_;
645 return unless $supplierid;
647 my $dbh = C4::Context->dbh;
649 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,
650 SUM(aqorders.quantity) AS total_items,
652 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
653 ) AS total_items_cancelled,
654 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
656 IF(aqorders.datereceived IS NULL
657 AND aqorders.datecancellationprinted IS NULL
661 SUM( aqorders.uncertainprice ) AS uncertainprices
663 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
664 WHERE booksellerid = ?};
666 $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";
668 unless ( $allbaskets ) {
669 # Don't show the basket if it's NOT CLOSED or is FULLY RECEIVED
670 $query.=" HAVING (closedate IS NULL OR (
672 IF(aqorders.datereceived IS NULL
673 AND aqorders.datecancellationprinted IS NULL
679 my $sth = $dbh->prepare($query);
680 $sth->execute($supplierid);
681 my $baskets = $sth->fetchall_arrayref({});
683 # Retrieve the number of biblios cancelled
684 my $cancelled_biblios = $dbh->selectall_hashref( q|
685 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
687 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
688 WHERE booksellerid = ?
689 AND aqorders.orderstatus = 'cancelled'
690 GROUP BY aqbasket.basketno
691 |, 'basketno', {}, $supplierid );
693 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
699 =head3 GetBasketUsers
701 $basketusers_ids = &GetBasketUsers($basketno);
703 Returns a list of all borrowernumbers that are in basket users list
708 my $basketno = shift;
710 return unless $basketno;
713 SELECT borrowernumber
717 my $dbh = C4::Context->dbh;
718 my $sth = $dbh->prepare($query);
719 $sth->execute($basketno);
720 my $results = $sth->fetchall_arrayref( {} );
723 foreach (@$results) {
724 push @borrowernumbers, $_->{'borrowernumber'};
727 return @borrowernumbers;
730 =head3 ModBasketUsers
732 my @basketusers_ids = (1, 2, 3);
733 &ModBasketUsers($basketno, @basketusers_ids);
735 Delete all users from basket users list, and add users in C<@basketusers_ids>
741 my ($basketno, @basketusers_ids) = @_;
743 return unless $basketno;
745 my $dbh = C4::Context->dbh;
747 DELETE FROM aqbasketusers
750 my $sth = $dbh->prepare($query);
751 $sth->execute($basketno);
754 INSERT INTO aqbasketusers (basketno, borrowernumber)
757 $sth = $dbh->prepare($query);
758 foreach my $basketuser_id (@basketusers_ids) {
759 $sth->execute($basketno, $basketuser_id);
764 =head3 CanUserManageBasket
766 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
767 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
769 Check if a borrower can manage a basket, according to system preference
770 AcqViewBaskets, user permissions and basket properties (creator, users list,
773 First parameter can be either a borrowernumber or a hashref as returned by
774 Koha::Patron->unblessed
776 Second parameter can be either a basketno or a hashref as returned by
777 C4::Acquisition::GetBasket.
779 The third parameter is optional. If given, it should be a hashref as returned
780 by C4::Auth::getuserflags. If not, getuserflags is called.
782 If user is authorised to manage basket, returns 1.
787 sub CanUserManageBasket {
788 my ($borrower, $basket, $userflags) = @_;
790 if (!ref $borrower) {
791 # FIXME This needs to be replaced
792 # We should not accept both scalar and array
793 # Tests need to be updated
794 $borrower = Koha::Patrons->find( $borrower )->unblessed;
797 $basket = GetBasket($basket);
800 return 0 unless ($basket and $borrower);
802 my $borrowernumber = $borrower->{borrowernumber};
803 my $basketno = $basket->{basketno};
805 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
807 if (!defined $userflags) {
808 my $dbh = C4::Context->dbh;
809 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
810 $sth->execute($borrowernumber);
811 my ($flags) = $sth->fetchrow_array;
814 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
817 unless ($userflags->{superlibrarian}
818 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
819 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
821 if (not exists $userflags->{acquisition}) {
825 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
826 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
830 if ($AcqViewBaskets eq 'user'
831 && $basket->{authorisedby} != $borrowernumber
832 && ! grep { $borrowernumber eq $_ } GetBasketUsers($basketno)) {
836 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
837 && $basket->{branch} ne $borrower->{branchcode}) {
845 #------------------------------------------------------------#
847 =head3 GetBasketsByBasketgroup
849 $baskets = &GetBasketsByBasketgroup($basketgroupid);
851 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
855 sub GetBasketsByBasketgroup {
856 my $basketgroupid = shift;
858 SELECT *, aqbasket.booksellerid as booksellerid
860 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
862 my $dbh = C4::Context->dbh;
863 my $sth = $dbh->prepare($query);
864 $sth->execute($basketgroupid);
865 return $sth->fetchall_arrayref({});
868 #------------------------------------------------------------#
870 =head3 NewBasketgroup
872 $basketgroupid = NewBasketgroup(\%hashref);
874 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
876 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
878 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
880 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
882 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
884 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
886 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
888 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
890 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
895 my $basketgroupinfo = shift;
896 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
897 my $query = "INSERT INTO aqbasketgroups (";
899 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
900 if ( defined $basketgroupinfo->{$field} ) {
901 $query .= "$field, ";
902 push(@params, $basketgroupinfo->{$field});
905 $query .= "booksellerid) VALUES (";
910 push(@params, $basketgroupinfo->{'booksellerid'});
911 my $dbh = C4::Context->dbh;
912 my $sth = $dbh->prepare($query);
913 $sth->execute(@params);
914 my $basketgroupid = $dbh->{'mysql_insertid'};
915 if( $basketgroupinfo->{'basketlist'} ) {
916 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
917 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
918 my $sth2 = $dbh->prepare($query2);
919 $sth2->execute($basketgroupid, $basketno);
922 return $basketgroupid;
925 #------------------------------------------------------------#
927 =head3 ModBasketgroup
929 ModBasketgroup(\%hashref);
931 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
933 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
935 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
937 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
939 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
941 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
943 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
945 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
947 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
952 my $basketgroupinfo = shift;
953 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
954 my $dbh = C4::Context->dbh;
955 my $query = "UPDATE aqbasketgroups SET ";
957 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
958 if ( defined $basketgroupinfo->{$field} ) {
959 $query .= "$field=?, ";
960 push(@params, $basketgroupinfo->{$field});
965 $query .= " WHERE id=?";
966 push(@params, $basketgroupinfo->{'id'});
967 my $sth = $dbh->prepare($query);
968 $sth->execute(@params);
970 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
971 $sth->execute($basketgroupinfo->{'id'});
973 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
974 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
975 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
976 $sth->execute($basketgroupinfo->{'id'}, $basketno);
982 #------------------------------------------------------------#
984 =head3 DelBasketgroup
986 DelBasketgroup($basketgroupid);
988 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
992 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
999 my $basketgroupid = shift;
1000 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1001 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
1002 my $dbh = C4::Context->dbh;
1003 my $sth = $dbh->prepare($query);
1004 $sth->execute($basketgroupid);
1008 #------------------------------------------------------------#
1011 =head2 FUNCTIONS ABOUT ORDERS
1013 =head3 GetBasketgroup
1015 $basketgroup = &GetBasketgroup($basketgroupid);
1017 Returns a reference to the hash containing all information about the basketgroup.
1021 sub GetBasketgroup {
1022 my $basketgroupid = shift;
1023 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1024 my $dbh = C4::Context->dbh;
1025 my $result_set = $dbh->selectall_arrayref(
1026 'SELECT * FROM aqbasketgroups WHERE id=?',
1030 return $result_set->[0]; # id is unique
1033 #------------------------------------------------------------#
1035 =head3 GetBasketgroups
1037 $basketgroups = &GetBasketgroups($booksellerid);
1039 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1043 sub GetBasketgroups {
1044 my $booksellerid = shift;
1045 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1046 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1047 my $dbh = C4::Context->dbh;
1048 my $sth = $dbh->prepare($query);
1049 $sth->execute($booksellerid);
1050 return $sth->fetchall_arrayref({});
1053 #------------------------------------------------------------#
1055 =head2 FUNCTIONS ABOUT ORDERS
1059 @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } );
1061 Looks up the pending (non-cancelled) orders with the given basket
1064 If cancelled is set, only cancelled orders will be returned.
1069 my ( $basketno, $params ) = @_;
1071 return () unless $basketno;
1073 my $orderby = $params->{orderby};
1074 my $cancelled = $params->{cancelled} || 0;
1076 my $dbh = C4::Context->dbh;
1078 SELECT biblio.*,biblioitems.*,
1082 $query .= $cancelled
1084 aqorders_transfers.ordernumber_to AS transferred_to,
1085 aqorders_transfers.timestamp AS transferred_to_timestamp
1088 aqorders_transfers.ordernumber_from AS transferred_from,
1089 aqorders_transfers.timestamp AS transferred_from_timestamp
1093 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1094 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1095 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1097 $query .= $cancelled
1099 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1102 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1110 $orderby ||= q|biblioitems.publishercode, biblio.title|;
1112 AND datecancellationprinted IS NOT NULL
1117 q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|;
1119 AND datecancellationprinted IS NULL
1123 $query .= " ORDER BY $orderby";
1125 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1130 #------------------------------------------------------------#
1132 =head3 GetOrdersByBiblionumber
1134 @orders = &GetOrdersByBiblionumber($biblionumber);
1136 Looks up the orders with linked to a specific $biblionumber, including
1137 cancelled orders and received orders.
1140 C<@orders> is an array of references-to-hash, whose keys are the
1141 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1145 sub GetOrdersByBiblionumber {
1146 my $biblionumber = shift;
1147 return unless $biblionumber;
1148 my $dbh = C4::Context->dbh;
1150 SELECT biblio.*,biblioitems.*,
1154 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1155 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1156 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1157 WHERE aqorders.biblionumber=?
1160 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1161 return @{$result_set};
1165 #------------------------------------------------------------#
1169 $order = &GetOrder($ordernumber);
1171 Looks up an order by order number.
1173 Returns a reference-to-hash describing the order. The keys of
1174 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1179 my ($ordernumber) = @_;
1180 return unless $ordernumber;
1182 my $dbh = C4::Context->dbh;
1183 my $query = qq{SELECT
1187 aqbasket.basketname,
1188 borrowers.branchcode,
1189 biblioitems.publicationyear,
1190 biblio.copyrightdate,
1191 biblioitems.editionstatement,
1195 biblioitems.publishercode,
1196 aqorders.rrp AS unitpricesupplier,
1197 aqorders.ecost AS unitpricelib,
1198 aqbudgets.budget_name AS budget,
1199 aqbooksellers.name AS supplier,
1200 aqbooksellers.id AS supplierid,
1201 biblioitems.publishercode AS publisher,
1202 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1203 DATE(aqbasket.closedate) AS orderdate,
1204 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1205 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1206 DATEDIFF(CURDATE( ),closedate) AS latesince
1207 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1208 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1209 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1210 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1211 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1212 WHERE aqorders.basketno = aqbasket.basketno
1215 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1217 # result_set assumed to contain 1 match
1218 return $result_set->[0];
1223 &ModOrder(\%hashref);
1225 Modifies an existing order. Updates the order with order number
1226 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1227 other keys of the hash update the fields with the same name in the aqorders
1228 table of the Koha database.
1233 my $orderinfo = shift;
1235 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '';
1237 my $dbh = C4::Context->dbh;
1240 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1241 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1243 # delete($orderinfo->{'branchcode'});
1244 # the hash contains a lot of entries not in aqorders, so get the columns ...
1245 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1247 my $colnames = $sth->{NAME};
1248 #FIXME Be careful. If aqorders would have columns with diacritics,
1249 #you should need to decode what you get back from NAME.
1250 #See report 10110 and guided_reports.pl
1251 my $query = "UPDATE aqorders SET ";
1253 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1254 # ... and skip hash entries that are not in the aqorders table
1255 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1256 next unless grep { $_ eq $orderinfokey } @$colnames;
1257 $query .= "$orderinfokey=?, ";
1258 push(@params, $orderinfo->{$orderinfokey});
1261 $query .= "timestamp=NOW() WHERE ordernumber=?";
1262 push(@params, $orderinfo->{'ordernumber'} );
1263 $sth = $dbh->prepare($query);
1264 $sth->execute(@params);
1268 #------------------------------------------------------------#
1272 ModItemOrder($itemnumber, $ordernumber);
1274 Modifies the ordernumber of an item in aqorders_items.
1279 my ($itemnumber, $ordernumber) = @_;
1281 return unless ($itemnumber and $ordernumber);
1283 my $dbh = C4::Context->dbh;
1285 UPDATE aqorders_items
1287 WHERE itemnumber = ?
1289 my $sth = $dbh->prepare($query);
1290 return $sth->execute($ordernumber, $itemnumber);
1293 #------------------------------------------------------------#
1295 =head3 ModReceiveOrder
1297 my ( $date_received, $new_ordernumber ) = ModReceiveOrder(
1299 biblionumber => $biblionumber,
1301 quantityreceived => $quantityreceived,
1303 invoice => $invoice,
1304 budget_id => $budget_id,
1305 datereceived => $datereceived,
1306 received_itemnumbers => \@received_itemnumbers,
1310 Updates an order, to reflect the fact that it was received, at least
1313 If a partial order is received, splits the order into two.
1315 Updates the order with biblionumber C<$biblionumber> and ordernumber
1316 C<$order->{ordernumber}>.
1321 sub ModReceiveOrder {
1323 my $biblionumber = $params->{biblionumber};
1324 my $order = { %{ $params->{order} } }; # Copy the order, we don't want to modify it
1325 my $invoice = $params->{invoice};
1326 my $quantrec = $params->{quantityreceived};
1327 my $user = $params->{user};
1328 my $budget_id = $params->{budget_id};
1329 my $datereceived = $params->{datereceived};
1330 my $received_items = $params->{received_items};
1332 my $dbh = C4::Context->dbh;
1333 $datereceived = output_pref(
1335 dt => ( $datereceived ? dt_from_string( $datereceived ) : dt_from_string ),
1336 dateformat => 'iso',
1341 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1342 if ($suggestionid) {
1343 ModSuggestion( {suggestionid=>$suggestionid,
1344 STATUS=>'AVAILABLE',
1345 biblionumber=> $biblionumber}
1349 my $result_set = $dbh->selectrow_arrayref(
1350 q{SELECT aqbasket.is_standing
1352 WHERE basketno=?},{ Slice => {} }, $order->{basketno});
1353 my $is_standing = $result_set->[0]; # we assume we have a unique basket
1355 my $new_ordernumber = $order->{ordernumber};
1356 if ( $is_standing || $order->{quantity} > $quantrec ) {
1357 # Split order line in two parts: the first is the original order line
1358 # without received items (the quantity is decreased),
1359 # the second part is a new order line with quantity=quantityrec
1360 # (entirely received)
1364 orderstatus = 'partial'|;
1365 $query .= q| WHERE ordernumber = ?|;
1366 my $sth = $dbh->prepare($query);
1369 ( $is_standing ? 1 : ($order->{quantity} - $quantrec) ),
1370 $order->{ordernumber}
1373 if ( not $order->{subscriptionid} && defined $order->{order_internalnote} ) {
1376 SET order_internalnote = ?
1377 WHERE ordernumber = ?|, {},
1378 $order->{order_internalnote}, $order->{ordernumber}
1382 # Recalculate tax_value
1386 tax_value_on_ordering = quantity * | . get_rounding_sql(q|ecost_tax_excluded|) . q| * tax_rate_on_ordering,
1387 tax_value_on_receiving = quantity * | . get_rounding_sql(q|unitprice_tax_excluded|) . q| * tax_rate_on_receiving
1388 WHERE ordernumber = ?
1389 |, undef, $order->{ordernumber});
1391 delete $order->{ordernumber};
1392 $order->{budget_id} = ( $budget_id || $order->{budget_id} );
1393 $order->{quantity} = $quantrec;
1394 $order->{quantityreceived} = $quantrec;
1395 $order->{ecost_tax_excluded} //= 0;
1396 $order->{tax_rate_on_ordering} //= 0;
1397 $order->{unitprice_tax_excluded} //= 0;
1398 $order->{tax_rate_on_receiving} //= 0;
1399 $order->{tax_value_on_ordering} = $order->{quantity} * get_rounded_price($order->{ecost_tax_excluded}) * $order->{tax_rate_on_ordering};
1400 $order->{tax_value_on_receiving} = $order->{quantity} * get_rounded_price($order->{unitprice_tax_excluded}) * $order->{tax_rate_on_receiving};
1401 $order->{datereceived} = $datereceived;
1402 $order->{invoiceid} = $invoice->{invoiceid};
1403 $order->{orderstatus} = 'complete';
1404 $new_ordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber; # TODO What if the store fails?
1406 if ($received_items) {
1407 foreach my $itemnumber (@$received_items) {
1408 ModItemOrder($itemnumber, $new_ordernumber);
1414 SET quantityreceived = ?,
1418 orderstatus = 'complete'
1422 , replacementprice = ?
1423 | if defined $order->{replacementprice};
1426 , unitprice = ?, unitprice_tax_included = ?, unitprice_tax_excluded = ?
1427 | if defined $order->{unitprice};
1430 ,tax_value_on_receiving = ?
1431 | if defined $order->{tax_value_on_receiving};
1434 ,tax_rate_on_receiving = ?
1435 | if defined $order->{tax_rate_on_receiving};
1438 , order_internalnote = ?
1439 | if defined $order->{order_internalnote};
1441 $query .= q| where biblionumber=? and ordernumber=?|;
1443 my $sth = $dbh->prepare( $query );
1444 my @params = ( $quantrec, $datereceived, $invoice->{invoiceid}, ( $budget_id ? $budget_id : $order->{budget_id} ) );
1446 if ( defined $order->{replacementprice} ) {
1447 push @params, $order->{replacementprice};
1450 if ( defined $order->{unitprice} ) {
1451 push @params, $order->{unitprice}, $order->{unitprice_tax_included}, $order->{unitprice_tax_excluded};
1454 if ( defined $order->{tax_value_on_receiving} ) {
1455 push @params, $order->{tax_value_on_receiving};
1458 if ( defined $order->{tax_rate_on_receiving} ) {
1459 push @params, $order->{tax_rate_on_receiving};
1462 if ( defined $order->{order_internalnote} ) {
1463 push @params, $order->{order_internalnote};
1466 push @params, ( $biblionumber, $order->{ordernumber} );
1468 $sth->execute( @params );
1470 # All items have been received, sent a notification to users
1471 NotifyOrderUsers( $order->{ordernumber} );
1474 return ($datereceived, $new_ordernumber);
1477 =head3 CancelReceipt
1479 my $parent_ordernumber = CancelReceipt($ordernumber);
1481 Cancel an order line receipt and update the parent order line, as if no
1483 If items are created at receipt (AcqCreateItem = receiving) then delete
1489 my $ordernumber = shift;
1491 return unless $ordernumber;
1493 my $dbh = C4::Context->dbh;
1495 SELECT datereceived, parent_ordernumber, quantity
1497 WHERE ordernumber = ?
1499 my $sth = $dbh->prepare($query);
1500 $sth->execute($ordernumber);
1501 my $order = $sth->fetchrow_hashref;
1503 warn "CancelReceipt: order $ordernumber does not exist";
1506 unless($order->{'datereceived'}) {
1507 warn "CancelReceipt: order $ordernumber is not received";
1511 my $parent_ordernumber = $order->{'parent_ordernumber'};
1513 my $order_obj = Koha::Acquisition::Orders->find( $ordernumber ); # FIXME rewrite all this subroutine using this object
1514 my @itemnumbers = $order_obj->items->get_column('itemnumber');
1516 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1517 # The order line has no parent, just mark it as not received
1520 SET quantityreceived = ?,
1523 orderstatus = 'ordered'
1524 WHERE ordernumber = ?
1526 $sth = $dbh->prepare($query);
1527 $sth->execute(0, undef, undef, $ordernumber);
1528 _cancel_items_receipt( $order_obj );
1530 # The order line has a parent, increase parent quantity and delete
1532 unless ( $order_obj->basket->is_standing ) {
1534 SELECT quantity, datereceived
1536 WHERE ordernumber = ?
1538 $sth = $dbh->prepare($query);
1539 $sth->execute($parent_ordernumber);
1540 my $parent_order = $sth->fetchrow_hashref;
1541 unless($parent_order) {
1542 warn "Parent order $parent_ordernumber does not exist.";
1545 if($parent_order->{'datereceived'}) {
1546 warn "CancelReceipt: parent order is received.".
1547 " Can't cancel receipt.";
1553 orderstatus = 'ordered'
1554 WHERE ordernumber = ?
1556 $sth = $dbh->prepare($query);
1557 my $rv = $sth->execute(
1558 $order->{'quantity'} + $parent_order->{'quantity'},
1562 warn "Cannot update parent order line, so do not cancel".
1567 # Recalculate tax_value
1571 tax_value_on_ordering = quantity * | . get_rounding_sql(q|ecost_tax_excluded|) . q| * tax_rate_on_ordering,
1572 tax_value_on_receiving = quantity * | . get_rounding_sql(q|unitprice_tax_excluded|) . q| * tax_rate_on_receiving
1573 WHERE ordernumber = ?
1574 |, undef, $parent_ordernumber);
1577 _cancel_items_receipt( $order_obj, $parent_ordernumber );
1580 DELETE FROM aqorders
1581 WHERE ordernumber = ?
1583 $sth = $dbh->prepare($query);
1584 $sth->execute($ordernumber);
1588 if( $order_obj->basket->effective_create_items eq 'ordering' ) {
1589 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1591 for my $in ( @itemnumbers ) {
1592 my $item = Koha::Items->find( $in ); # FIXME We do not need that, we already have Koha::Items from $order_obj->items
1593 my $biblio = $item->biblio;
1594 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber' );
1595 my $item_marc = C4::Items::GetMarcItem( $biblio->biblionumber, $in );
1596 for my $affect ( @affects ) {
1597 my ( $sf, $v ) = split q{=}, $affect, 2;
1598 foreach ( $item_marc->field($itemfield) ) {
1599 $_->update( $sf => $v );
1602 C4::Items::ModItemFromMarc( $item_marc, $biblio->biblionumber, $in );
1607 return $parent_ordernumber;
1610 sub _cancel_items_receipt {
1611 my ( $order, $parent_ordernumber ) = @_;
1612 $parent_ordernumber ||= $order->ordernumber;
1614 my $items = $order->items;
1615 if ( $order->basket->effective_create_items eq 'receiving' ) {
1616 # Remove items that were created at receipt
1618 DELETE FROM items, aqorders_items
1619 USING items, aqorders_items
1620 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1622 my $dbh = C4::Context->dbh;
1623 my $sth = $dbh->prepare($query);
1624 while ( my $item = $items->next ) {
1625 $sth->execute($item->itemnumber, $item->itemnumber);
1629 while ( my $item = $items->next ) {
1630 ModItemOrder($item->itemnumber, $parent_ordernumber);
1635 #------------------------------------------------------------#
1639 @results = &SearchOrders({
1640 ordernumber => $ordernumber,
1643 booksellerid => $booksellerid,
1644 basketno => $basketno,
1645 basketname => $basketname,
1646 basketgroupname => $basketgroupname,
1650 biblionumber => $biblionumber,
1651 budget_id => $budget_id
1654 Searches for orders filtered by criteria.
1656 C<$ordernumber> Finds matching orders or transferred orders by ordernumber.
1657 C<$search> Finds orders matching %$search% in title, author, or isbn.
1658 C<$owner> Finds order for the logged in user.
1659 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1660 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1663 C<@results> is an array of references-to-hash with the keys are fields
1664 from aqorders, biblio, biblioitems and aqbasket tables.
1669 my ( $params ) = @_;
1670 my $ordernumber = $params->{ordernumber};
1671 my $search = $params->{search};
1672 my $ean = $params->{ean};
1673 my $booksellerid = $params->{booksellerid};
1674 my $basketno = $params->{basketno};
1675 my $basketname = $params->{basketname};
1676 my $basketgroupname = $params->{basketgroupname};
1677 my $owner = $params->{owner};
1678 my $pending = $params->{pending};
1679 my $ordered = $params->{ordered};
1680 my $biblionumber = $params->{biblionumber};
1681 my $budget_id = $params->{budget_id};
1683 my $dbh = C4::Context->dbh;
1686 SELECT aqbasket.basketno,
1688 borrowers.firstname,
1691 biblioitems.biblioitemnumber,
1692 biblioitems.publishercode,
1693 biblioitems.publicationyear,
1694 aqbasket.authorisedby,
1695 aqbasket.booksellerid,
1697 aqbasket.creationdate,
1698 aqbasket.basketname,
1699 aqbasketgroups.id as basketgroupid,
1700 aqbasketgroups.name as basketgroupname,
1703 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1704 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1705 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1706 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1707 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1710 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1712 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1716 WHERE (datecancellationprinted is NULL)
1719 if ( $pending or $ordered ) {
1722 ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) )
1724 ( quantity > quantityreceived OR quantityreceived is NULL )
1728 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1736 my $userenv = C4::Context->userenv;
1737 if ( C4::Context->preference("IndependentBranches") ) {
1738 unless ( C4::Context->IsSuperLibrarian() ) {
1741 borrowers.branchcode = ?
1742 OR borrowers.branchcode = ''
1745 push @args, $userenv->{branch};
1749 if ( $ordernumber ) {
1750 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1751 push @args, ( $ordernumber, $ordernumber );
1753 if ( $biblionumber ) {
1754 $query .= 'AND aqorders.biblionumber = ?';
1755 push @args, $biblionumber;
1758 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1759 push @args, ("%$search%","%$search%","%$search%");
1762 $query .= ' AND biblioitems.ean = ?';
1765 if ( $booksellerid ) {
1766 $query .= 'AND aqbasket.booksellerid = ?';
1767 push @args, $booksellerid;
1770 $query .= 'AND aqbasket.basketno = ?';
1771 push @args, $basketno;
1774 $query .= 'AND aqbasket.basketname LIKE ?';
1775 push @args, "%$basketname%";
1777 if( $basketgroupname ) {
1778 $query .= ' AND aqbasketgroups.name LIKE ?';
1779 push @args, "%$basketgroupname%";
1783 $query .= ' AND aqbasket.authorisedby=? ';
1784 push @args, $userenv->{'number'};
1788 $query .= ' AND aqorders.budget_id = ?';
1789 push @args, $budget_id;
1792 $query .= ' ORDER BY aqbasket.basketno';
1794 my $sth = $dbh->prepare($query);
1795 $sth->execute(@args);
1796 return $sth->fetchall_arrayref({});
1799 #------------------------------------------------------------#
1801 =head3 TransferOrder
1803 my $newordernumber = TransferOrder($ordernumber, $basketno);
1805 Transfer an order line to a basket.
1806 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1807 to BOOKSELLER on DATE' and create new order with internal note
1808 'Transferred from BOOKSELLER on DATE'.
1809 Move all attached items to the new order.
1810 Received orders cannot be transferred.
1811 Return the ordernumber of created order.
1816 my ($ordernumber, $basketno) = @_;
1818 return unless ($ordernumber and $basketno);
1820 my $order = Koha::Acquisition::Orders->find( $ordernumber ) or return;
1821 return if $order->datereceived;
1823 $order = $order->unblessed;
1825 my $basket = GetBasket($basketno);
1826 return unless $basket;
1828 my $dbh = C4::Context->dbh;
1829 my ($query, $sth, $rv);
1833 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1834 WHERE ordernumber = ?
1836 $sth = $dbh->prepare($query);
1837 $rv = $sth->execute('cancelled', $ordernumber);
1839 delete $order->{'ordernumber'};
1840 delete $order->{parent_ordernumber};
1841 $order->{'basketno'} = $basketno;
1843 my $newordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber;
1846 UPDATE aqorders_items
1848 WHERE ordernumber = ?
1850 $sth = $dbh->prepare($query);
1851 $sth->execute($newordernumber, $ordernumber);
1854 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1857 $sth = $dbh->prepare($query);
1858 $sth->execute($ordernumber, $newordernumber);
1860 return $newordernumber;
1863 =head3 get_rounding_sql
1865 $rounding_sql = get_rounding_sql($column_name);
1867 returns the correct SQL routine based on OrderPriceRounding system preference.
1871 sub get_rounding_sql {
1872 my ( $round_string ) = @_;
1873 my $rounding_pref = C4::Context->preference('OrderPriceRounding') // q{};
1874 if ( $rounding_pref eq "nearest_cent" ) {
1875 return "CAST($round_string*100 AS SIGNED)/100";
1877 return $round_string;
1880 =head3 get_rounded_price
1882 $rounded_price = get_rounded_price( $price );
1884 returns a price rounded as specified in OrderPriceRounding system preference.
1888 sub get_rounded_price {
1890 my $rounding_pref = C4::Context->preference('OrderPriceRounding') // q{};
1891 if( $rounding_pref eq 'nearest_cent' ) {
1892 return Koha::Number::Price->new( $price )->round();
1898 =head2 FUNCTIONS ABOUT PARCELS
1902 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1904 get a lists of parcels.
1911 is the bookseller this function has to get parcels.
1914 To know on what criteria the results list has to be ordered.
1917 is the booksellerinvoicenumber.
1919 =item $datefrom & $dateto
1920 to know on what date this function has to filter its search.
1925 a pointer on a hash list containing parcel informations as such :
1931 =item Last operation
1933 =item Number of biblio
1935 =item Number of items
1942 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1943 my $dbh = C4::Context->dbh;
1944 my @query_params = ();
1946 SELECT aqinvoices.invoicenumber,
1947 datereceived,purchaseordernumber,
1948 count(DISTINCT biblionumber) AS biblio,
1949 sum(quantity) AS itemsexpected,
1950 sum(quantityreceived) AS itemsreceived
1951 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1952 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1953 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1955 push @query_params, $bookseller;
1957 if ( defined $code ) {
1958 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1959 # add a % to the end of the code to allow stemming.
1960 push @query_params, "$code%";
1963 if ( defined $datefrom ) {
1964 $strsth .= ' and datereceived >= ? ';
1965 push @query_params, $datefrom;
1968 if ( defined $dateto ) {
1969 $strsth .= 'and datereceived <= ? ';
1970 push @query_params, $dateto;
1973 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1975 # can't use a placeholder to place this column name.
1976 # but, we could probably be checking to make sure it is a column that will be fetched.
1977 $strsth .= "order by $order " if ($order);
1979 my $sth = $dbh->prepare($strsth);
1981 $sth->execute( @query_params );
1982 my $results = $sth->fetchall_arrayref({});
1986 #------------------------------------------------------------#
1990 \@order_loop = GetHistory( %params );
1992 Retreives some acquisition history information
2002 basket - search both basket name and number
2003 booksellerinvoicenumber
2006 orderstatus (note that orderstatus '' will retrieve orders
2007 of any status except cancelled)
2011 get_canceled_order (if set to a true value, cancelled orders will
2015 $order_loop is a list of hashrefs that each look like this:
2017 'author' => 'Twain, Mark',
2019 'biblionumber' => '215',
2021 'creationdate' => 'MM/DD/YYYY',
2022 'datereceived' => undef,
2025 'invoicenumber' => undef,
2027 'ordernumber' => '1',
2029 'quantityreceived' => undef,
2030 'title' => 'The Adventures of Huckleberry Finn',
2031 'managing_library' => 'CPL'
2032 'is_standing' => '1'
2038 # don't run the query if there are no parameters (list would be too long for sure !)
2039 croak "No search params" unless @_;
2041 my $title = $params{title};
2042 my $author = $params{author};
2043 my $isbn = $params{isbn};
2044 my $ean = $params{ean};
2045 my $name = $params{name};
2046 my $from_placed_on = $params{from_placed_on};
2047 my $to_placed_on = $params{to_placed_on};
2048 my $basket = $params{basket};
2049 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2050 my $basketgroupname = $params{basketgroupname};
2051 my $budget = $params{budget};
2052 my $orderstatus = $params{orderstatus};
2053 my $is_standing = $params{is_standing};
2054 my $biblionumber = $params{biblionumber};
2055 my $get_canceled_order = $params{get_canceled_order} || 0;
2056 my $ordernumber = $params{ordernumber};
2057 my $search_children_too = $params{search_children_too} || 0;
2058 my $created_by = $params{created_by} || [];
2059 my $managing_library = $params{managing_library};
2060 my $ordernumbers = $params{ordernumbers} || [];
2061 my $additional_fields = $params{additional_fields} // [];
2064 my $total_qtyreceived = 0;
2065 my $total_price = 0;
2067 #get variation of isbn
2071 if ( C4::Context->preference("SearchWithISBNVariations") ){
2072 @isbns = C4::Koha::GetVariationsOfISBN( $isbn );
2073 foreach my $isb (@isbns){
2074 push @isbn_params, '?';
2079 push @isbn_params, '?';
2083 my $dbh = C4::Context->dbh;
2086 COALESCE(biblio.title, deletedbiblio.title) AS title,
2087 COALESCE(biblio.author, deletedbiblio.author) AS author,
2088 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2089 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2091 aqbasket.basketname,
2092 aqbasket.basketgroupid,
2093 aqbasket.authorisedby,
2094 aqbasket.is_standing,
2095 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2096 branch as managing_library,
2097 aqbasketgroups.name as groupname,
2099 aqbasket.creationdate,
2100 aqorders.datereceived,
2102 aqorders.quantityreceived,
2104 aqorders.ordernumber,
2106 aqinvoices.invoicenumber,
2107 aqbooksellers.id as id,
2108 aqorders.biblionumber,
2109 aqorders.orderstatus,
2110 aqorders.parent_ordernumber,
2111 aqbudgets.budget_name
2113 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2116 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2117 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2118 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2119 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2120 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2121 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2122 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2123 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2124 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2125 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2128 $query .= " WHERE 1 ";
2130 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2131 $query .= " AND datecancellationprinted IS NULL ";
2134 my @query_params = ();
2136 if ( $biblionumber ) {
2137 $query .= " AND biblio.biblionumber = ?";
2138 push @query_params, $biblionumber;
2142 $query .= " AND biblio.title LIKE ? ";
2143 $title =~ s/\s+/%/g;
2144 push @query_params, "%$title%";
2148 $query .= " AND biblio.author LIKE ? ";
2149 push @query_params, "%$author%";
2153 $query .= " AND ( biblioitems.isbn LIKE " . join (" OR biblioitems.isbn LIKE ", @isbn_params ) . ")";
2154 foreach my $isb (@isbns){
2155 push @query_params, "%$isb%";
2160 $query .= " AND biblioitems.ean = ? ";
2161 push @query_params, "$ean";
2164 $query .= " AND aqbooksellers.name LIKE ? ";
2165 push @query_params, "%$name%";
2169 $query .= " AND aqbudgets.budget_id = ? ";
2170 push @query_params, "$budget";
2173 if ( $from_placed_on ) {
2174 $query .= " AND creationdate >= ? ";
2175 push @query_params, $from_placed_on;
2178 if ( $to_placed_on ) {
2179 $query .= " AND creationdate <= ? ";
2180 push @query_params, $to_placed_on;
2183 if ( defined $orderstatus and $orderstatus ne '') {
2184 $query .= " AND aqorders.orderstatus = ? ";
2185 push @query_params, "$orderstatus";
2188 if ( $is_standing ) {
2189 $query .= " AND is_standing = ? ";
2190 push @query_params, $is_standing;
2194 if ($basket =~ m/^\d+$/) {
2195 $query .= " AND aqorders.basketno = ? ";
2196 push @query_params, $basket;
2198 $query .= " AND aqbasket.basketname LIKE ? ";
2199 push @query_params, "%$basket%";
2203 if ($booksellerinvoicenumber) {
2204 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2205 push @query_params, "%$booksellerinvoicenumber%";
2208 if ($basketgroupname) {
2209 $query .= " AND aqbasketgroups.name LIKE ? ";
2210 push @query_params, "%$basketgroupname%";
2214 $query .= " AND (aqorders.ordernumber = ? ";
2215 push @query_params, $ordernumber;
2216 if ($search_children_too) {
2217 $query .= " OR aqorders.parent_ordernumber = ? ";
2218 push @query_params, $ordernumber;
2223 if ( @$created_by ) {
2224 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2225 push @query_params, @$created_by;
2228 if ( $managing_library ) {
2229 $query .= " AND aqbasket.branch = ? ";
2230 push @query_params, $managing_library;
2233 if ( @$ordernumbers ) {
2234 $query .= ' AND (aqorders.ordernumber IN ( ' . join (',', ('?') x @$ordernumbers ) . '))';
2235 push @query_params, @$ordernumbers;
2237 if ( @$additional_fields ) {
2238 my @baskets = Koha::Acquisition::Baskets->filter_by_additional_fields($additional_fields);
2240 return [] unless @baskets;
2242 # No parameterization because record IDs come directly from DB
2243 $query .= ' AND aqbasket.basketno IN ( ' . join( ',', map { $_->basketno } @baskets ) . ' )';
2246 if ( C4::Context->preference("IndependentBranches") ) {
2247 unless ( C4::Context->IsSuperLibrarian() ) {
2248 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2249 push @query_params, C4::Context->userenv->{branch};
2252 $query .= " ORDER BY id";
2254 return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2257 =head2 GetRecentAcqui
2259 $results = GetRecentAcqui($days);
2261 C<$results> is a ref to a table which contains hashref
2265 sub GetRecentAcqui {
2267 my $dbh = C4::Context->dbh;
2271 ORDER BY timestamp DESC
2274 my $sth = $dbh->prepare($query);
2276 my $results = $sth->fetchall_arrayref({});
2280 #------------------------------------------------------------#
2284 &AddClaim($ordernumber);
2286 Add a claim for an order
2291 my ($ordernumber) = @_;
2292 my $dbh = C4::Context->dbh;
2295 claims_count = claims_count + 1,
2296 claimed_date = CURDATE()
2297 WHERE ordernumber = ?
2299 my $sth = $dbh->prepare($query);
2300 $sth->execute($ordernumber);
2305 my @invoices = GetInvoices(
2306 invoicenumber => $invoicenumber,
2307 supplierid => $supplierid,
2308 suppliername => $suppliername,
2309 shipmentdatefrom => $shipmentdatefrom, # ISO format
2310 shipmentdateto => $shipmentdateto, # ISO format
2311 billingdatefrom => $billingdatefrom, # ISO format
2312 billingdateto => $billingdateto, # ISO format
2313 isbneanissn => $isbn_or_ean_or_issn,
2316 publisher => $publisher,
2317 publicationyear => $publicationyear,
2318 branchcode => $branchcode,
2319 order_by => $order_by
2322 Return a list of invoices that match all given criteria.
2324 $order_by is "column_name (asc|desc)", where column_name is any of
2325 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2326 'shipmentcost', 'shipmentcost_budgetid'.
2328 asc is the default if omitted
2335 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2336 closedate shipmentcost shipmentcost_budgetid);
2338 my $dbh = C4::Context->dbh;
2340 SELECT aqinvoices.invoiceid, aqinvoices.invoicenumber, aqinvoices.booksellerid, aqinvoices.shipmentdate, aqinvoices.billingdate, aqinvoices.closedate, aqinvoices.shipmentcost, aqinvoices.shipmentcost_budgetid, aqinvoices.message_id,
2341 aqbooksellers.name AS suppliername,
2344 aqorders.datereceived IS NOT NULL,
2345 aqorders.biblionumber,
2348 ) AS receivedbiblios,
2351 aqorders.subscriptionid IS NOT NULL,
2352 aqorders.subscriptionid,
2355 ) AS is_linked_to_subscriptions,
2356 SUM(aqorders.quantityreceived) AS receiveditems
2358 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2359 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2360 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2361 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2362 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2363 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2364 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2369 if($args{supplierid}) {
2370 push @bind_strs, " aqinvoices.booksellerid = ? ";
2371 push @bind_args, $args{supplierid};
2373 if($args{invoicenumber}) {
2374 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2375 push @bind_args, "%$args{invoicenumber}%";
2377 if($args{suppliername}) {
2378 push @bind_strs, " aqbooksellers.name LIKE ? ";
2379 push @bind_args, "%$args{suppliername}%";
2381 if($args{shipmentdatefrom}) {
2382 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2383 push @bind_args, $args{shipmentdatefrom};
2385 if($args{shipmentdateto}) {
2386 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2387 push @bind_args, $args{shipmentdateto};
2389 if($args{billingdatefrom}) {
2390 push @bind_strs, " aqinvoices.billingdate >= ? ";
2391 push @bind_args, $args{billingdatefrom};
2393 if($args{billingdateto}) {
2394 push @bind_strs, " aqinvoices.billingdate <= ? ";
2395 push @bind_args, $args{billingdateto};
2397 if($args{isbneanissn}) {
2398 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2399 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2402 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2403 push @bind_args, $args{title};
2406 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2407 push @bind_args, $args{author};
2409 if($args{publisher}) {
2410 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2411 push @bind_args, $args{publisher};
2413 if($args{publicationyear}) {
2414 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2415 push @bind_args, $args{publicationyear}, $args{publicationyear};
2417 if($args{branchcode}) {
2418 push @bind_strs, " borrowers.branchcode = ? ";
2419 push @bind_args, $args{branchcode};
2421 if($args{message_id}) {
2422 push @bind_strs, " aqinvoices.message_id = ? ";
2423 push @bind_args, $args{message_id};
2426 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2427 $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";
2429 if($args{order_by}) {
2430 my ($column, $direction) = split / /, $args{order_by};
2431 if(grep { $_ eq $column } @columns) {
2432 $direction ||= 'ASC';
2433 $query .= " ORDER BY $column $direction";
2437 my $sth = $dbh->prepare($query);
2438 $sth->execute(@bind_args);
2440 my $results = $sth->fetchall_arrayref({});
2446 my $invoice = GetInvoice($invoiceid);
2448 Get informations about invoice with given $invoiceid
2450 Return a hash filled with aqinvoices.* fields
2455 my ($invoiceid) = @_;
2458 return unless $invoiceid;
2460 my $dbh = C4::Context->dbh;
2466 my $sth = $dbh->prepare($query);
2467 $sth->execute($invoiceid);
2469 $invoice = $sth->fetchrow_hashref;
2473 =head3 GetInvoiceDetails
2475 my $invoice = GetInvoiceDetails($invoiceid)
2477 Return informations about an invoice + the list of related order lines
2479 Orders informations are in $invoice->{orders} (array ref)
2483 sub GetInvoiceDetails {
2484 my ($invoiceid) = @_;
2486 if ( !defined $invoiceid ) {
2487 carp 'GetInvoiceDetails called without an invoiceid';
2491 my $dbh = C4::Context->dbh;
2493 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2495 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2498 my $sth = $dbh->prepare($query);
2499 $sth->execute($invoiceid);
2501 my $invoice = $sth->fetchrow_hashref;
2506 biblio.copyrightdate,
2508 biblioitems.publishercode,
2509 biblioitems.publicationyear,
2510 aqbasket.basketname,
2511 aqbasketgroups.id AS basketgroupid,
2512 aqbasketgroups.name AS basketgroupname
2514 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2515 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2516 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2517 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2520 $sth = $dbh->prepare($query);
2521 $sth->execute($invoiceid);
2522 $invoice->{orders} = $sth->fetchall_arrayref({});
2523 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2530 my $invoiceid = AddInvoice(
2531 invoicenumber => $invoicenumber,
2532 booksellerid => $booksellerid,
2533 shipmentdate => $shipmentdate,
2534 billingdate => $billingdate,
2535 closedate => $closedate,
2536 shipmentcost => $shipmentcost,
2537 shipmentcost_budgetid => $shipmentcost_budgetid
2540 Create a new invoice and return its id or undef if it fails.
2547 return unless(%invoice and $invoice{invoicenumber});
2549 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2550 closedate shipmentcost shipmentcost_budgetid message_id);
2554 foreach my $key (keys %invoice) {
2555 if(0 < grep { $_ eq $key } @columns) {
2556 push @set_strs, "$key = ?";
2557 push @set_args, ($invoice{$key} || undef);
2563 my $dbh = C4::Context->dbh;
2564 my $query = "INSERT INTO aqinvoices SET ";
2565 $query .= join (",", @set_strs);
2566 my $sth = $dbh->prepare($query);
2567 $rv = $sth->execute(@set_args);
2569 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2578 invoiceid => $invoiceid, # Mandatory
2579 invoicenumber => $invoicenumber,
2580 booksellerid => $booksellerid,
2581 shipmentdate => $shipmentdate,
2582 billingdate => $billingdate,
2583 closedate => $closedate,
2584 shipmentcost => $shipmentcost,
2585 shipmentcost_budgetid => $shipmentcost_budgetid
2588 Modify an invoice, invoiceid is mandatory.
2590 Return undef if it fails.
2597 return unless(%invoice and $invoice{invoiceid});
2599 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2600 closedate shipmentcost shipmentcost_budgetid);
2604 foreach my $key (keys %invoice) {
2605 if(0 < grep { $_ eq $key } @columns) {
2606 push @set_strs, "$key = ?";
2607 push @set_args, ($invoice{$key} || undef);
2611 my $dbh = C4::Context->dbh;
2612 my $query = "UPDATE aqinvoices SET ";
2613 $query .= join(",", @set_strs);
2614 $query .= " WHERE invoiceid = ?";
2616 my $sth = $dbh->prepare($query);
2617 $sth->execute(@set_args, $invoice{invoiceid});
2622 CloseInvoice($invoiceid);
2626 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2631 my ($invoiceid) = @_;
2633 return unless $invoiceid;
2635 my $dbh = C4::Context->dbh;
2638 SET closedate = CAST(NOW() AS DATE)
2641 my $sth = $dbh->prepare($query);
2642 $sth->execute($invoiceid);
2645 =head3 ReopenInvoice
2647 ReopenInvoice($invoiceid);
2651 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => output_pref({ dt=>dt_from_string, dateonly=>1, otputpref=>'iso' }))
2656 my ($invoiceid) = @_;
2658 return unless $invoiceid;
2660 my $dbh = C4::Context->dbh;
2663 SET closedate = NULL
2666 my $sth = $dbh->prepare($query);
2667 $sth->execute($invoiceid);
2672 DelInvoice($invoiceid);
2674 Delete an invoice if there are no items attached to it.
2679 my ($invoiceid) = @_;
2681 return unless $invoiceid;
2683 my $dbh = C4::Context->dbh;
2689 my $sth = $dbh->prepare($query);
2690 $sth->execute($invoiceid);
2691 my $res = $sth->fetchrow_arrayref;
2692 if ( $res && $res->[0] == 0 ) {
2694 DELETE FROM aqinvoices
2697 my $sth = $dbh->prepare($query);
2698 return ( $sth->execute($invoiceid) > 0 );
2703 =head3 MergeInvoices
2705 MergeInvoices($invoiceid, \@sourceids);
2707 Merge the invoices identified by the IDs in \@sourceids into
2708 the invoice identified by $invoiceid.
2713 my ($invoiceid, $sourceids) = @_;
2715 return unless $invoiceid;
2716 foreach my $sourceid (@$sourceids) {
2717 next if $sourceid == $invoiceid;
2718 my $source = GetInvoiceDetails($sourceid);
2719 foreach my $order (@{$source->{'orders'}}) {
2720 $order->{'invoiceid'} = $invoiceid;
2723 DelInvoice($source->{'invoiceid'});
2728 =head3 GetBiblioCountByBasketno
2730 $biblio_count = &GetBiblioCountByBasketno($basketno);
2732 Looks up the biblio's count that has basketno value $basketno
2738 sub GetBiblioCountByBasketno {
2739 my ($basketno) = @_;
2740 my $dbh = C4::Context->dbh;
2742 SELECT COUNT( DISTINCT( biblionumber ) )
2745 AND datecancellationprinted IS NULL
2748 my $sth = $dbh->prepare($query);
2749 $sth->execute($basketno);
2750 return $sth->fetchrow;
2753 =head3 populate_order_with_prices
2755 $order = populate_order_with_prices({
2756 order => $order #a hashref with the order values
2757 booksellerid => $booksellerid #FIXME - should obtain from order basket
2758 receiving => 1 # boolean representing order stage, should pass only this or ordering
2759 ordering => 1 # boolean representing order stage
2763 Sets calculated values for an order - all values are stored with full precision
2764 regardless of rounding preference except for tax value which is calculated
2765 on rounded values if requested
2767 For ordering the values set are:
2772 tax_value_on_ordering
2773 For receiving the value set are:
2774 unitprice_tax_included
2775 unitprice_tax_excluded
2776 tax_value_on_receiving
2778 Note: When receiving, if the rounded value of the unitprice matches the rounded
2779 value of the ecost then then ecost (full precision) is used.
2781 Returns a hashref of the order
2783 FIXME: Move this to Koha::Acquisition::Order.pm
2787 sub populate_order_with_prices {
2790 my $order = $params->{order};
2791 my $booksellerid = $params->{booksellerid};
2792 return unless $booksellerid;
2794 my $bookseller = Koha::Acquisition::Booksellers->find( $booksellerid );
2796 my $receiving = $params->{receiving};
2797 my $ordering = $params->{ordering};
2798 my $discount = $order->{discount};
2799 $discount /= 100 if $discount > 1;
2802 $order->{tax_rate_on_ordering} //= $order->{tax_rate};
2803 if ( $bookseller->listincgst ) {
2805 # The user entered the prices tax included
2806 $order->{unitprice} += 0;
2807 $order->{unitprice_tax_included} = $order->{unitprice};
2808 $order->{rrp_tax_included} = $order->{rrp};
2810 # price tax excluded = price tax included / ( 1 + tax rate )
2811 $order->{unitprice_tax_excluded} = $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate_on_ordering} );
2812 $order->{rrp_tax_excluded} = $order->{rrp_tax_included} / ( 1 + $order->{tax_rate_on_ordering} );
2814 # ecost tax included = rrp tax included ( 1 - discount )
2815 $order->{ecost_tax_included} = $order->{rrp_tax_included} * ( 1 - $discount );
2817 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
2818 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
2820 # tax value = quantity * ecost tax excluded * tax rate
2821 # we should use the unitprice if included
2822 my $cost_tax_included = $order->{unitprice_tax_included} || $order->{ecost_tax_included};
2823 my $cost_tax_excluded = $order->{unitprice_tax_excluded} || $order->{ecost_tax_excluded};
2824 $order->{tax_value_on_ordering} = ( get_rounded_price($cost_tax_included) - get_rounded_price($cost_tax_excluded) ) * $order->{quantity};
2828 # The user entered the prices tax excluded
2829 $order->{unitprice_tax_excluded} = $order->{unitprice};
2830 $order->{rrp_tax_excluded} = $order->{rrp};
2832 # price tax included = price tax excluded * ( 1 - tax rate )
2833 $order->{unitprice_tax_included} = $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate_on_ordering} );
2834 $order->{rrp_tax_included} = $order->{rrp_tax_excluded} * ( 1 + $order->{tax_rate_on_ordering} );
2836 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
2837 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
2839 # ecost tax included = rrp tax excluded * ( 1 + tax rate ) * ( 1 - discount ) = ecost tax excluded * ( 1 + tax rate )
2840 $order->{ecost_tax_included} = $order->{ecost_tax_excluded} * ( 1 + $order->{tax_rate_on_ordering} );
2842 # tax value = quantity * ecost tax included * tax rate
2843 # we should use the unitprice if included
2844 my $cost_tax_excluded = $order->{unitprice_tax_excluded} || $order->{ecost_tax_excluded};
2845 $order->{tax_value_on_ordering} = $order->{quantity} * get_rounded_price($cost_tax_excluded) * $order->{tax_rate_on_ordering};
2850 $order->{tax_rate_on_receiving} //= $order->{tax_rate};
2851 if ( $bookseller->invoiceincgst ) {
2852 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
2853 # we need to keep the exact ecost value
2854 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_included} )->round ) {
2855 $order->{unitprice} = $order->{ecost_tax_included};
2858 # The user entered the unit price tax included
2859 $order->{unitprice_tax_included} = $order->{unitprice};
2861 # unit price tax excluded = unit price tax included / ( 1 + tax rate )
2862 $order->{unitprice_tax_excluded} = $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate_on_receiving} );
2865 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
2866 # we need to keep the exact ecost value
2867 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_excluded} )->round ) {
2868 $order->{unitprice} = $order->{ecost_tax_excluded};
2871 # The user entered the unit price tax excluded
2872 $order->{unitprice_tax_excluded} = $order->{unitprice};
2875 # unit price tax included = unit price tax included * ( 1 + tax rate )
2876 $order->{unitprice_tax_included} = $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate_on_receiving} );
2879 # tax value = quantity * unit price tax excluded * tax rate
2880 $order->{tax_value_on_receiving} = $order->{quantity} * get_rounded_price($order->{unitprice_tax_excluded}) * $order->{tax_rate_on_receiving};
2886 =head3 GetOrderUsers
2888 $order_users_ids = &GetOrderUsers($ordernumber);
2890 Returns a list of all borrowernumbers that are in order users list
2895 my ($ordernumber) = @_;
2897 return unless $ordernumber;
2900 SELECT borrowernumber
2902 WHERE ordernumber = ?
2904 my $dbh = C4::Context->dbh;
2905 my $sth = $dbh->prepare($query);
2906 $sth->execute($ordernumber);
2907 my $results = $sth->fetchall_arrayref( {} );
2909 my @borrowernumbers;
2910 foreach (@$results) {
2911 push @borrowernumbers, $_->{'borrowernumber'};
2914 return @borrowernumbers;
2917 =head3 ModOrderUsers
2919 my @order_users_ids = (1, 2, 3);
2920 &ModOrderUsers($ordernumber, @basketusers_ids);
2922 Delete all users from order users list, and add users in C<@order_users_ids>
2928 my ( $ordernumber, @order_users_ids ) = @_;
2930 return unless $ordernumber;
2932 my $dbh = C4::Context->dbh;
2934 DELETE FROM aqorder_users
2935 WHERE ordernumber = ?
2937 my $sth = $dbh->prepare($query);
2938 $sth->execute($ordernumber);
2941 INSERT INTO aqorder_users (ordernumber, borrowernumber)
2944 $sth = $dbh->prepare($query);
2945 foreach my $order_user_id (@order_users_ids) {
2946 $sth->execute( $ordernumber, $order_user_id );
2950 sub NotifyOrderUsers {
2951 my ($ordernumber) = @_;
2953 my @borrowernumbers = GetOrderUsers($ordernumber);
2954 return unless @borrowernumbers;
2956 my $order = GetOrder( $ordernumber );
2957 for my $borrowernumber (@borrowernumbers) {
2958 my $patron = Koha::Patrons->find( $borrowernumber );
2959 my $library = $patron->library->unblessed;
2960 my $biblio = Koha::Biblios->find( $order->{biblionumber} )->unblessed;
2961 my $letter = C4::Letters::GetPreparedLetter(
2962 module => 'acquisition',
2963 letter_code => 'ACQ_NOTIF_ON_RECEIV',
2964 branchcode => $library->{branchcode},
2965 lang => $patron->lang,
2967 'branches' => $library,
2968 'borrowers' => $patron->unblessed,
2969 'biblio' => $biblio,
2970 'aqorders' => $order,
2974 C4::Letters::EnqueueLetter(
2977 borrowernumber => $borrowernumber,
2978 LibraryName => C4::Context->preference("LibraryName"),
2979 message_transport_type => 'email',
2981 ) or warn "can't enqueue letter $letter";
2986 =head3 FillWithDefaultValues
2988 FillWithDefaultValues( $marc_record, $params );
2990 This will update the record with default value defined in the ACQ framework.
2991 For all existing fields, if a default value exists and there are no subfield, it will be created.
2992 If the field does not exist, it will be created too.
2994 If the parameter only_mandatory => 1 is passed via $params, only the mandatory
2995 defaults are being applied to the record.
2999 sub FillWithDefaultValues {
3000 my ( $record, $params ) = @_;
3001 my $mandatory = $params->{only_mandatory};
3002 my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ', { unsafe => 1 } );
3005 C4::Biblio::GetMarcFromKohaField( 'items.itemnumber' );
3006 for my $tag ( sort keys %$tagslib ) {
3008 next if $tag == $itemfield;
3009 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
3010 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
3011 next if $mandatory && !$tagslib->{$tag}{$subfield}{mandatory};
3012 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
3013 if ( defined $defaultvalue and $defaultvalue ne '' ) {
3014 my @fields = $record->field($tag);
3016 for my $field (@fields) {
3017 if ( $field->is_control_field ) {
3018 $field->update($defaultvalue) if not defined $field->data;
3020 elsif ( not defined $field->subfield($subfield) ) {
3021 $field->add_subfields(
3022 $subfield => $defaultvalue );
3027 if ( $tag < 10 ) { # is_control_field
3028 $record->insert_fields_ordered(
3035 $record->insert_fields_ordered(
3037 $tag, '', '', $subfield => $defaultvalue
3053 Koha Development Team <http://koha-community.org/>