1 package C4::Acquisition;
3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it
8 # under the terms of the GNU General Public License as published by
9 # the Free Software Foundation; either version 3 of the License, or
10 # (at your option) any later version.
12 # Koha is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
29 use C4::Templates qw(gettemplate);
30 use Koha::DateUtils qw( dt_from_string output_pref );
31 use Koha::Acquisition::Booksellers;
32 use Koha::Acquisition::Orders;
36 use Koha::Number::Price;
38 use Koha::CsvProfiles;
48 use vars qw(@ISA @EXPORT);
54 &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
55 &GetBasketAsCSV &GetBasketGroupAsCSV
56 &GetBasketsByBookseller &GetBasketsByBasketgroup
57 &GetBasketsInfosByBookseller
59 &GetBasketUsers &ModBasketUsers
64 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
65 &GetBasketgroups &ReOpenBasketgroup
67 &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
68 &GetLateOrders &GetOrderFromItemnumber
69 &SearchOrders &GetHistory &GetRecentAcqui
70 &ModReceiveOrder &CancelReceipt
72 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
88 &GetBiblioCountByBasketno
94 &FillWithDefaultValues
105 sub GetOrderFromItemnumber {
106 my ($itemnumber) = @_;
107 my $dbh = C4::Context->dbh;
110 SELECT * from aqorders LEFT JOIN aqorders_items
111 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
112 WHERE itemnumber = ? |;
114 my $sth = $dbh->prepare($query);
118 $sth->execute($itemnumber);
120 my $order = $sth->fetchrow_hashref;
127 C4::Acquisition - Koha functions for dealing with orders and acquisitions
135 The functions in this module deal with acquisitions, managing book
136 orders, basket and parcels.
140 =head2 FUNCTIONS ABOUT BASKETS
144 $aqbasket = &GetBasket($basketnumber);
146 get all basket informations in aqbasket for a given basket
148 B<returns:> informations for a given basket returned as a hashref.
154 my $dbh = C4::Context->dbh;
157 concat( b.firstname,' ',b.surname) AS authorisedbyname
159 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
162 my $sth=$dbh->prepare($query);
163 $sth->execute($basketno);
164 my $basket = $sth->fetchrow_hashref;
168 #------------------------------------------------------------#
172 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
173 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace, $is_standing, $create_items );
175 Create a new basket in aqbasket table
179 =item C<$booksellerid> is a foreign key in the aqbasket table
181 =item C<$authorizedby> is the username of who created the basket
185 The other parameters are optional, see ModBasketHeader for more info on them.
190 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
191 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
192 $billingplace, $is_standing, $create_items ) = @_;
193 my $dbh = C4::Context->dbh;
195 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
196 . 'VALUES (now(),?,?)';
197 $dbh->do( $query, {}, $booksellerid, $authorisedby );
199 my $basket = $dbh->{mysql_insertid};
200 $basketname ||= q{}; # default to empty strings
202 $basketbooksellernote ||= q{};
203 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
204 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items );
208 #------------------------------------------------------------#
212 &CloseBasket($basketno);
214 close a basket (becomes unmodifiable, except for receives)
220 my $dbh = C4::Context->dbh;
221 $dbh->do('UPDATE aqbasket SET closedate=now() WHERE basketno=?', {}, $basketno );
224 q{UPDATE aqorders SET orderstatus = 'ordered' WHERE basketno = ? AND orderstatus NOT IN ( 'complete', 'cancelled')},
232 &ReopenBasket($basketno);
240 my $dbh = C4::Context->dbh;
241 $dbh->do( q{UPDATE aqbasket SET closedate=NULL WHERE basketno=?}, {}, $basketno );
245 SET orderstatus = 'new'
247 AND orderstatus NOT IN ( 'complete', 'cancelled' )
252 #------------------------------------------------------------#
254 =head3 GetBasketAsCSV
256 &GetBasketAsCSV($basketno);
258 Export a basket as CSV
260 $cgi parameter is needed for column name translation
265 my ($basketno, $cgi, $csv_profile_id) = @_;
266 my $basket = GetBasket($basketno);
267 my @orders = GetOrders($basketno);
268 my $contract = GetContract({
269 contractnumber => $basket->{'contractnumber'}
272 my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
274 if ($csv_profile_id) {
275 my $csv_profile = Koha::CsvProfiles->find( $csv_profile_id );
276 Koha::Exceptions::ObjectNotFound->throw( 'There is no valid csv profile given') unless $csv_profile;
278 my $csv = Text::CSV_XS->new({'quote_char'=>'"','escape_char'=>'"','sep_char'=>$csv_profile->csv_separator,'binary'=>1});
279 my $csv_profile_content = $csv_profile->content;
280 my ( @headers, @fields );
281 while ( $csv_profile_content =~ /
284 ([^\|]*) # fieldname (table.row or row)
288 my $field = ($2 eq '') ? $1 : $2;
290 $header =~ s/^\s+|\s+$//g; # Trim whitespaces
291 push @headers, $header;
293 $field =~ s/[^\.]*\.{1}//; # Remove the table name if exists.
294 $field =~ s/^\s+|\s+$//g; # Trim whitespaces
295 push @fields, $field;
297 for my $order (@orders) {
299 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
300 my $biblioitem = $biblio->biblioitem;
301 $order = { %$order, %{ $biblioitem->unblessed } };
303 $order = {%$order, %$contract};
305 $order = {%$order, %$basket, %{ $biblio->unblessed }};
306 for my $field (@fields) {
307 push @row, $order->{$field};
311 my $content = join( $csv_profile->csv_separator, @headers ) . "\n";
312 for my $row ( @rows ) {
313 $csv->combine(@$row);
314 my $string = $csv->string;
315 $content .= $string . "\n";
320 foreach my $order (@orders) {
321 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
322 my $biblioitem = $biblio->biblioitem;
324 contractname => $contract->{'contractname'},
325 ordernumber => $order->{'ordernumber'},
326 entrydate => $order->{'entrydate'},
327 isbn => $order->{'isbn'},
328 author => $biblio->author,
329 title => $biblio->title,
330 publicationyear => $biblioitem->publicationyear,
331 publishercode => $biblioitem->publishercode,
332 collectiontitle => $biblioitem->collectiontitle,
333 notes => $order->{'order_vendornote'},
334 quantity => $order->{'quantity'},
335 rrp => $order->{'rrp'},
337 for my $place ( qw( deliveryplace billingplace ) ) {
338 if ( my $library = Koha::Libraries->find( $row->{deliveryplace} ) ) {
339 $row->{$place} = $library->branchname
343 contractname author title publishercode collectiontitle notes
344 deliveryplace billingplace
346 # Double the quotes to not be interpreted as a field end
347 $row->{$_} =~ s/"/""/g if $row->{$_};
353 if(defined $a->{publishercode} and defined $b->{publishercode}) {
354 $a->{publishercode} cmp $b->{publishercode};
358 $template->param(rows => \@rows);
360 return $template->output;
365 =head3 GetBasketGroupAsCSV
367 &GetBasketGroupAsCSV($basketgroupid);
369 Export a basket group as CSV
371 $cgi parameter is needed for column name translation
375 sub GetBasketGroupAsCSV {
376 my ($basketgroupid, $cgi) = @_;
377 my $baskets = GetBasketsByBasketgroup($basketgroupid);
379 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
382 for my $basket (@$baskets) {
383 my @orders = GetOrders( $basket->{basketno} );
384 my $contract = GetContract({
385 contractnumber => $basket->{contractnumber}
387 my $bookseller = Koha::Acquisition::Booksellers->find( $basket->{booksellerid} );
388 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
390 foreach my $order (@orders) {
391 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
392 my $biblioitem = $biblio->biblioitem;
394 clientnumber => $bookseller->accountnumber,
395 basketname => $basket->{basketname},
396 ordernumber => $order->{ordernumber},
397 author => $biblio->author,
398 title => $biblio->title,
399 publishercode => $biblioitem->publishercode,
400 publicationyear => $biblioitem->publicationyear,
401 collectiontitle => $biblioitem->collectiontitle,
402 isbn => $order->{isbn},
403 quantity => $order->{quantity},
404 rrp_tax_included => $order->{rrp_tax_included},
405 rrp_tax_excluded => $order->{rrp_tax_excluded},
406 discount => $bookseller->discount,
407 ecost_tax_included => $order->{ecost_tax_included},
408 ecost_tax_excluded => $order->{ecost_tax_excluded},
409 notes => $order->{order_vendornote},
410 entrydate => $order->{entrydate},
411 booksellername => $bookseller->name,
412 bookselleraddress => $bookseller->address1,
413 booksellerpostal => $bookseller->postal,
414 contractnumber => $contract->{contractnumber},
415 contractname => $contract->{contractname},
418 basketgroupdeliveryplace => $basketgroup->{deliveryplace},
419 basketgroupbillingplace => $basketgroup->{billingplace},
420 basketdeliveryplace => $basket->{deliveryplace},
421 basketbillingplace => $basket->{billingplace},
423 for my $place (qw( basketgroupdeliveryplace basketgroupbillingplace basketdeliveryplace basketbillingplace )) {
424 if ( my $library = Koha::Libraries->find( $temp->{$place} ) ) {
425 $row->{$place} = $library->branchname;
429 basketname author title publishercode collectiontitle notes
430 booksellername bookselleraddress booksellerpostal contractname
431 basketgroupdeliveryplace basketgroupbillingplace
432 basketdeliveryplace basketbillingplace
434 # Double the quotes to not be interpreted as a field end
435 $row->{$_} =~ s/"/""/g if $row->{$_};
440 $template->param(rows => \@rows);
442 return $template->output;
446 =head3 CloseBasketgroup
448 &CloseBasketgroup($basketgroupno);
454 sub CloseBasketgroup {
455 my ($basketgroupno) = @_;
456 my $dbh = C4::Context->dbh;
457 my $sth = $dbh->prepare("
458 UPDATE aqbasketgroups
462 $sth->execute($basketgroupno);
465 #------------------------------------------------------------#
467 =head3 ReOpenBaskergroup($basketgroupno)
469 &ReOpenBaskergroup($basketgroupno);
475 sub ReOpenBasketgroup {
476 my ($basketgroupno) = @_;
477 my $dbh = C4::Context->dbh;
478 my $sth = $dbh->prepare("
479 UPDATE aqbasketgroups
483 $sth->execute($basketgroupno);
486 #------------------------------------------------------------#
491 &DelBasket($basketno);
493 Deletes the basket that has basketno field $basketno in the aqbasket table.
497 =item C<$basketno> is the primary key of the basket in the aqbasket table.
504 my ( $basketno ) = @_;
505 my $query = "DELETE FROM aqbasket WHERE basketno=?";
506 my $dbh = C4::Context->dbh;
507 my $sth = $dbh->prepare($query);
508 $sth->execute($basketno);
512 #------------------------------------------------------------#
516 &ModBasket($basketinfo);
518 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
522 =item C<$basketno> is the primary key of the basket in the aqbasket table.
529 my $basketinfo = shift;
530 my $query = "UPDATE aqbasket SET ";
532 foreach my $key (keys %$basketinfo){
533 if ($key ne 'basketno'){
534 $query .= "$key=?, ";
535 push(@params, $basketinfo->{$key} || undef );
538 # get rid of the "," at the end of $query
539 if (substr($query, length($query)-2) eq ', '){
544 $query .= "WHERE basketno=?";
545 push(@params, $basketinfo->{'basketno'});
546 my $dbh = C4::Context->dbh;
547 my $sth = $dbh->prepare($query);
548 $sth->execute(@params);
553 #------------------------------------------------------------#
555 =head3 ModBasketHeader
557 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
559 Modifies a basket's header.
563 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
565 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
567 =item C<$note> is the "note" field in the "aqbasket" table;
569 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
571 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
573 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
575 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
577 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
579 =item C<$is_standing> is the "is_standing" field in the aqbasket table.
581 =item C<$create_items> should be set to 'ordering', 'receiving' or 'cataloguing' (or undef, in which
582 case the AcqCreateItem syspref takes precedence).
588 sub ModBasketHeader {
589 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items) = @_;
594 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?, is_standing=?, create_items=?
598 my $dbh = C4::Context->dbh;
599 my $sth = $dbh->prepare($query);
600 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items || undef, $basketno);
602 if ( $contractnumber ) {
603 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
604 my $sth2 = $dbh->prepare($query2);
605 $sth2->execute($contractnumber,$basketno);
610 #------------------------------------------------------------#
612 =head3 GetBasketsByBookseller
614 @results = &GetBasketsByBookseller($booksellerid, $extra);
616 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
620 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
622 =item C<$extra> is the extra sql parameters, can be
624 $extra->{groupby}: group baskets by column
625 ex. $extra->{groupby} = aqbasket.basketgroupid
626 $extra->{orderby}: order baskets by column
627 $extra->{limit}: limit number of results (can be helpful for pagination)
633 sub GetBasketsByBookseller {
634 my ($booksellerid, $extra) = @_;
635 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
637 if ($extra->{groupby}) {
638 $query .= " GROUP by $extra->{groupby}";
640 if ($extra->{orderby}){
641 $query .= " ORDER by $extra->{orderby}";
643 if ($extra->{limit}){
644 $query .= " LIMIT $extra->{limit}";
647 my $dbh = C4::Context->dbh;
648 my $sth = $dbh->prepare($query);
649 $sth->execute($booksellerid);
650 return $sth->fetchall_arrayref({});
653 =head3 GetBasketsInfosByBookseller
655 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
657 The optional second parameter allbaskets is a boolean allowing you to
658 select all baskets from the supplier; by default only active baskets (open or
659 closed but still something to receive) are returned.
661 Returns in a arrayref of hashref all about booksellers baskets, plus:
662 total_biblios: Number of distinct biblios in basket
663 total_items: Number of items in basket
664 expected_items: Number of non-received items in basket
668 sub GetBasketsInfosByBookseller {
669 my ($supplierid, $allbaskets) = @_;
671 return unless $supplierid;
673 my $dbh = C4::Context->dbh;
675 SELECT aqbasket.basketno, aqbasket.basketname, aqbasket.note, aqbasket.booksellernote, aqbasket.contractnumber, aqbasket.creationdate, aqbasket.closedate, aqbasket.booksellerid, aqbasket.authorisedby, aqbasket.booksellerinvoicenumber, aqbasket.basketgroupid, aqbasket.deliveryplace, aqbasket.billingplace, aqbasket.branch, aqbasket.is_standing, aqbasket.create_items,
676 SUM(aqorders.quantity) AS total_items,
678 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
679 ) AS total_items_cancelled,
680 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
682 IF(aqorders.datereceived IS NULL
683 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
688 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
689 WHERE booksellerid = ?};
691 $query.=" GROUP BY aqbasket.basketno, aqbasket.basketname, aqbasket.note, aqbasket.booksellernote, aqbasket.contractnumber, aqbasket.creationdate, aqbasket.closedate, aqbasket.booksellerid, aqbasket.authorisedby, aqbasket.booksellerinvoicenumber, aqbasket.basketgroupid, aqbasket.deliveryplace, aqbasket.billingplace, aqbasket.branch, aqbasket.is_standing, aqbasket.create_items";
693 unless ( $allbaskets ) {
694 # Don't show the basket if it's NOT CLOSED or is FULLY RECEIVED
695 $query.=" HAVING (closedate IS NULL OR (
697 IF(aqorders.datereceived IS NULL
698 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
704 my $sth = $dbh->prepare($query);
705 $sth->execute($supplierid);
706 my $baskets = $sth->fetchall_arrayref({});
708 # Retrieve the number of biblios cancelled
709 my $cancelled_biblios = $dbh->selectall_hashref( q|
710 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
712 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
713 WHERE booksellerid = ?
714 AND aqorders.orderstatus = 'cancelled'
715 GROUP BY aqbasket.basketno
716 |, 'basketno', {}, $supplierid );
718 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
724 =head3 GetBasketUsers
726 $basketusers_ids = &GetBasketUsers($basketno);
728 Returns a list of all borrowernumbers that are in basket users list
733 my $basketno = shift;
735 return unless $basketno;
738 SELECT borrowernumber
742 my $dbh = C4::Context->dbh;
743 my $sth = $dbh->prepare($query);
744 $sth->execute($basketno);
745 my $results = $sth->fetchall_arrayref( {} );
748 foreach (@$results) {
749 push @borrowernumbers, $_->{'borrowernumber'};
752 return @borrowernumbers;
755 =head3 ModBasketUsers
757 my @basketusers_ids = (1, 2, 3);
758 &ModBasketUsers($basketno, @basketusers_ids);
760 Delete all users from basket users list, and add users in C<@basketusers_ids>
766 my ($basketno, @basketusers_ids) = @_;
768 return unless $basketno;
770 my $dbh = C4::Context->dbh;
772 DELETE FROM aqbasketusers
775 my $sth = $dbh->prepare($query);
776 $sth->execute($basketno);
779 INSERT INTO aqbasketusers (basketno, borrowernumber)
782 $sth = $dbh->prepare($query);
783 foreach my $basketuser_id (@basketusers_ids) {
784 $sth->execute($basketno, $basketuser_id);
789 =head3 CanUserManageBasket
791 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
792 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
794 Check if a borrower can manage a basket, according to system preference
795 AcqViewBaskets, user permissions and basket properties (creator, users list,
798 First parameter can be either a borrowernumber or a hashref as returned by
799 Koha::Patron->unblessed
801 Second parameter can be either a basketno or a hashref as returned by
802 C4::Acquisition::GetBasket.
804 The third parameter is optional. If given, it should be a hashref as returned
805 by C4::Auth::getuserflags. If not, getuserflags is called.
807 If user is authorised to manage basket, returns 1.
812 sub CanUserManageBasket {
813 my ($borrower, $basket, $userflags) = @_;
815 if (!ref $borrower) {
816 # FIXME This needs to be replaced
817 # We should not accept both scalar and array
818 # Tests need to be updated
819 $borrower = Koha::Patrons->find( $borrower )->unblessed;
822 $basket = GetBasket($basket);
825 return 0 unless ($basket and $borrower);
827 my $borrowernumber = $borrower->{borrowernumber};
828 my $basketno = $basket->{basketno};
830 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
832 if (!defined $userflags) {
833 my $dbh = C4::Context->dbh;
834 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
835 $sth->execute($borrowernumber);
836 my ($flags) = $sth->fetchrow_array;
839 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
842 unless ($userflags->{superlibrarian}
843 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
844 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
846 if (not exists $userflags->{acquisition}) {
850 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
851 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
855 if ($AcqViewBaskets eq 'user'
856 && $basket->{authorisedby} != $borrowernumber
857 && ! grep { $borrowernumber eq $_ } GetBasketUsers($basketno)) {
861 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
862 && $basket->{branch} ne $borrower->{branchcode}) {
870 #------------------------------------------------------------#
872 =head3 GetBasketsByBasketgroup
874 $baskets = &GetBasketsByBasketgroup($basketgroupid);
876 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
880 sub GetBasketsByBasketgroup {
881 my $basketgroupid = shift;
883 SELECT *, aqbasket.booksellerid as booksellerid
885 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
887 my $dbh = C4::Context->dbh;
888 my $sth = $dbh->prepare($query);
889 $sth->execute($basketgroupid);
890 return $sth->fetchall_arrayref({});
893 #------------------------------------------------------------#
895 =head3 NewBasketgroup
897 $basketgroupid = NewBasketgroup(\%hashref);
899 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
901 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
903 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
905 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
907 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
909 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
911 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
913 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
915 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
920 my $basketgroupinfo = shift;
921 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
922 my $query = "INSERT INTO aqbasketgroups (";
924 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
925 if ( defined $basketgroupinfo->{$field} ) {
926 $query .= "$field, ";
927 push(@params, $basketgroupinfo->{$field});
930 $query .= "booksellerid) VALUES (";
935 push(@params, $basketgroupinfo->{'booksellerid'});
936 my $dbh = C4::Context->dbh;
937 my $sth = $dbh->prepare($query);
938 $sth->execute(@params);
939 my $basketgroupid = $dbh->{'mysql_insertid'};
940 if( $basketgroupinfo->{'basketlist'} ) {
941 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
942 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
943 my $sth2 = $dbh->prepare($query2);
944 $sth2->execute($basketgroupid, $basketno);
947 return $basketgroupid;
950 #------------------------------------------------------------#
952 =head3 ModBasketgroup
954 ModBasketgroup(\%hashref);
956 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
958 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
960 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
962 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
964 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
966 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
968 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
970 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
972 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
977 my $basketgroupinfo = shift;
978 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
979 my $dbh = C4::Context->dbh;
980 my $query = "UPDATE aqbasketgroups SET ";
982 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
983 if ( defined $basketgroupinfo->{$field} ) {
984 $query .= "$field=?, ";
985 push(@params, $basketgroupinfo->{$field});
990 $query .= " WHERE id=?";
991 push(@params, $basketgroupinfo->{'id'});
992 my $sth = $dbh->prepare($query);
993 $sth->execute(@params);
995 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
996 $sth->execute($basketgroupinfo->{'id'});
998 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
999 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
1000 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
1001 $sth->execute($basketgroupinfo->{'id'}, $basketno);
1007 #------------------------------------------------------------#
1009 =head3 DelBasketgroup
1011 DelBasketgroup($basketgroupid);
1013 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
1017 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
1023 sub DelBasketgroup {
1024 my $basketgroupid = shift;
1025 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1026 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
1027 my $dbh = C4::Context->dbh;
1028 my $sth = $dbh->prepare($query);
1029 $sth->execute($basketgroupid);
1033 #------------------------------------------------------------#
1036 =head2 FUNCTIONS ABOUT ORDERS
1038 =head3 GetBasketgroup
1040 $basketgroup = &GetBasketgroup($basketgroupid);
1042 Returns a reference to the hash containing all information about the basketgroup.
1046 sub GetBasketgroup {
1047 my $basketgroupid = shift;
1048 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1049 my $dbh = C4::Context->dbh;
1050 my $result_set = $dbh->selectall_arrayref(
1051 'SELECT * FROM aqbasketgroups WHERE id=?',
1055 return $result_set->[0]; # id is unique
1058 #------------------------------------------------------------#
1060 =head3 GetBasketgroups
1062 $basketgroups = &GetBasketgroups($booksellerid);
1064 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1068 sub GetBasketgroups {
1069 my $booksellerid = shift;
1070 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1071 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1072 my $dbh = C4::Context->dbh;
1073 my $sth = $dbh->prepare($query);
1074 $sth->execute($booksellerid);
1075 return $sth->fetchall_arrayref({});
1078 #------------------------------------------------------------#
1080 =head2 FUNCTIONS ABOUT ORDERS
1084 @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } );
1086 Looks up the pending (non-cancelled) orders with the given basket
1089 If cancelled is set, only cancelled orders will be returned.
1094 my ( $basketno, $params ) = @_;
1096 return () unless $basketno;
1098 my $orderby = $params->{orderby};
1099 my $cancelled = $params->{cancelled} || 0;
1101 my $dbh = C4::Context->dbh;
1103 SELECT biblio.*,biblioitems.*,
1107 $query .= $cancelled
1109 aqorders_transfers.ordernumber_to AS transferred_to,
1110 aqorders_transfers.timestamp AS transferred_to_timestamp
1113 aqorders_transfers.ordernumber_from AS transferred_from,
1114 aqorders_transfers.timestamp AS transferred_from_timestamp
1118 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1119 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1120 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1122 $query .= $cancelled
1124 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1127 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1135 $orderby ||= q|biblioitems.publishercode, biblio.title|;
1137 AND (datecancellationprinted IS NOT NULL
1138 AND datecancellationprinted <> '0000-00-00')
1143 q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|;
1145 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1149 $query .= " ORDER BY $orderby";
1151 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1156 #------------------------------------------------------------#
1158 =head3 GetOrdersByBiblionumber
1160 @orders = &GetOrdersByBiblionumber($biblionumber);
1162 Looks up the orders with linked to a specific $biblionumber, including
1163 cancelled orders and received orders.
1166 C<@orders> is an array of references-to-hash, whose keys are the
1167 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1171 sub GetOrdersByBiblionumber {
1172 my $biblionumber = shift;
1173 return unless $biblionumber;
1174 my $dbh = C4::Context->dbh;
1176 SELECT biblio.*,biblioitems.*,
1180 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1181 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1182 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1183 WHERE aqorders.biblionumber=?
1186 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1187 return @{$result_set};
1191 #------------------------------------------------------------#
1195 $order = &GetOrder($ordernumber);
1197 Looks up an order by order number.
1199 Returns a reference-to-hash describing the order. The keys of
1200 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1205 my ($ordernumber) = @_;
1206 return unless $ordernumber;
1208 my $dbh = C4::Context->dbh;
1209 my $query = qq{SELECT
1213 aqbasket.basketname,
1214 borrowers.branchcode,
1215 biblioitems.publicationyear,
1216 biblio.copyrightdate,
1217 biblioitems.editionstatement,
1221 biblioitems.publishercode,
1222 aqorders.rrp AS unitpricesupplier,
1223 aqorders.ecost AS unitpricelib,
1224 aqorders.claims_count AS claims_count,
1225 aqorders.claimed_date AS claimed_date,
1226 aqbudgets.budget_name AS budget,
1227 aqbooksellers.name AS supplier,
1228 aqbooksellers.id AS supplierid,
1229 biblioitems.publishercode AS publisher,
1230 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1231 DATE(aqbasket.closedate) AS orderdate,
1232 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1233 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1234 DATEDIFF(CURDATE( ),closedate) AS latesince
1235 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1236 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1237 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1238 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1239 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1240 WHERE aqorders.basketno = aqbasket.basketno
1243 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1245 # result_set assumed to contain 1 match
1246 return $result_set->[0];
1249 =head3 GetLastOrderNotReceivedFromSubscriptionid
1251 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1253 Returns a reference-to-hash describing the last order not received for a subscription.
1257 sub GetLastOrderNotReceivedFromSubscriptionid {
1258 my ( $subscriptionid ) = @_;
1259 my $dbh = C4::Context->dbh;
1261 SELECT * FROM aqorders
1262 LEFT JOIN subscription
1263 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1264 WHERE aqorders.subscriptionid = ?
1265 AND aqorders.datereceived IS NULL
1269 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1271 # result_set assumed to contain 1 match
1272 return $result_set->[0];
1275 =head3 GetLastOrderReceivedFromSubscriptionid
1277 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1279 Returns a reference-to-hash describing the last order received for a subscription.
1283 sub GetLastOrderReceivedFromSubscriptionid {
1284 my ( $subscriptionid ) = @_;
1285 my $dbh = C4::Context->dbh;
1287 SELECT * FROM aqorders
1288 LEFT JOIN subscription
1289 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1290 WHERE aqorders.subscriptionid = ?
1291 AND aqorders.datereceived =
1293 SELECT MAX( aqorders.datereceived )
1295 LEFT JOIN subscription
1296 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1297 WHERE aqorders.subscriptionid = ?
1298 AND aqorders.datereceived IS NOT NULL
1300 ORDER BY ordernumber DESC
1304 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1306 # result_set assumed to contain 1 match
1307 return $result_set->[0];
1311 #------------------------------------------------------------#
1315 &ModOrder(\%hashref);
1317 Modifies an existing order. Updates the order with order number
1318 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1319 other keys of the hash update the fields with the same name in the aqorders
1320 table of the Koha database.
1325 my $orderinfo = shift;
1327 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '';
1329 my $dbh = C4::Context->dbh;
1332 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1333 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1335 # delete($orderinfo->{'branchcode'});
1336 # the hash contains a lot of entries not in aqorders, so get the columns ...
1337 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1339 my $colnames = $sth->{NAME};
1340 #FIXME Be careful. If aqorders would have columns with diacritics,
1341 #you should need to decode what you get back from NAME.
1342 #See report 10110 and guided_reports.pl
1343 my $query = "UPDATE aqorders SET ";
1345 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1346 # ... and skip hash entries that are not in the aqorders table
1347 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1348 next unless grep(/^$orderinfokey$/, @$colnames);
1349 $query .= "$orderinfokey=?, ";
1350 push(@params, $orderinfo->{$orderinfokey});
1353 $query .= "timestamp=NOW() WHERE ordernumber=?";
1354 push(@params, $orderinfo->{'ordernumber'} );
1355 $sth = $dbh->prepare($query);
1356 $sth->execute(@params);
1360 #------------------------------------------------------------#
1364 ModItemOrder($itemnumber, $ordernumber);
1366 Modifies the ordernumber of an item in aqorders_items.
1371 my ($itemnumber, $ordernumber) = @_;
1373 return unless ($itemnumber and $ordernumber);
1375 my $dbh = C4::Context->dbh;
1377 UPDATE aqorders_items
1379 WHERE itemnumber = ?
1381 my $sth = $dbh->prepare($query);
1382 return $sth->execute($ordernumber, $itemnumber);
1385 #------------------------------------------------------------#
1387 =head3 ModReceiveOrder
1389 my ( $date_received, $new_ordernumber ) = ModReceiveOrder(
1391 biblionumber => $biblionumber,
1393 quantityreceived => $quantityreceived,
1395 invoice => $invoice,
1396 budget_id => $budget_id,
1397 received_itemnumbers => \@received_itemnumbers,
1398 order_internalnote => $order_internalnote,
1402 Updates an order, to reflect the fact that it was received, at least
1405 If a partial order is received, splits the order into two.
1407 Updates the order with biblionumber C<$biblionumber> and ordernumber
1408 C<$order->{ordernumber}>.
1413 sub ModReceiveOrder {
1415 my $biblionumber = $params->{biblionumber};
1416 my $order = { %{ $params->{order} } }; # Copy the order, we don't want to modify it
1417 my $invoice = $params->{invoice};
1418 my $quantrec = $params->{quantityreceived};
1419 my $user = $params->{user};
1420 my $budget_id = $params->{budget_id};
1421 my $received_items = $params->{received_items};
1423 my $dbh = C4::Context->dbh;
1424 my $datereceived = ( $invoice and $invoice->{datereceived} ) ? $invoice->{datereceived} : dt_from_string;
1425 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1426 if ($suggestionid) {
1427 ModSuggestion( {suggestionid=>$suggestionid,
1428 STATUS=>'AVAILABLE',
1429 biblionumber=> $biblionumber}
1433 my $result_set = $dbh->selectrow_arrayref(
1434 q{SELECT aqbasket.is_standing
1436 WHERE basketno=?},{ Slice => {} }, $order->{basketno});
1437 my $is_standing = $result_set->[0]; # we assume we have a unique basket
1439 my $new_ordernumber = $order->{ordernumber};
1440 if ( $is_standing || $order->{quantity} > $quantrec ) {
1441 # Split order line in two parts: the first is the original order line
1442 # without received items (the quantity is decreased),
1443 # the second part is a new order line with quantity=quantityrec
1444 # (entirely received)
1448 orderstatus = 'partial'|;
1449 $query .= q| WHERE ordernumber = ?|;
1450 my $sth = $dbh->prepare($query);
1453 ( $is_standing ? 1 : ($order->{quantity} - $quantrec) ),
1454 $order->{ordernumber}
1457 if ( not $order->{subscriptionid} && defined $order->{order_internalnote} ) {
1460 SET order_internalnote = ?
1461 WHERE ordernumber = ?|, {},
1462 $order->{order_internalnote}, $order->{ordernumber}
1466 # Recalculate tax_value
1470 tax_value_on_ordering = quantity * | . get_rounding_sql(q|ecost_tax_excluded|) . q| * tax_rate_on_ordering,
1471 tax_value_on_receiving = quantity * | . get_rounding_sql(q|unitprice_tax_excluded|) . q| * tax_rate_on_receiving
1472 WHERE ordernumber = ?
1473 |, undef, $order->{ordernumber});
1475 delete $order->{ordernumber};
1476 $order->{budget_id} = ( $budget_id || $order->{budget_id} );
1477 $order->{quantity} = $quantrec;
1478 $order->{quantityreceived} = $quantrec;
1479 $order->{ecost_tax_excluded} //= 0;
1480 $order->{tax_rate_on_ordering} //= 0;
1481 $order->{unitprice_tax_excluded} //= 0;
1482 $order->{tax_rate_on_receiving} //= 0;
1483 $order->{tax_value_on_ordering} = $order->{quantity} * get_rounded_price($order->{ecost_tax_excluded}) * $order->{tax_rate_on_ordering};
1484 $order->{tax_value_on_receiving} = $order->{quantity} * get_rounded_price($order->{unitprice_tax_excluded}) * $order->{tax_rate_on_receiving};
1485 $order->{datereceived} = $datereceived;
1486 $order->{invoiceid} = $invoice->{invoiceid};
1487 $order->{orderstatus} = 'complete';
1488 $new_ordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber; # TODO What if the store fails?
1490 if ($received_items) {
1491 foreach my $itemnumber (@$received_items) {
1492 ModItemOrder($itemnumber, $new_ordernumber);
1498 SET quantityreceived = ?,
1502 orderstatus = 'complete'
1506 , replacementprice = ?
1507 | if defined $order->{replacementprice};
1510 , unitprice = ?, unitprice_tax_included = ?, unitprice_tax_excluded = ?
1511 | if defined $order->{unitprice};
1514 ,tax_value_on_receiving = ?
1515 | if defined $order->{tax_value_on_receiving};
1518 ,tax_rate_on_receiving = ?
1519 | if defined $order->{tax_rate_on_receiving};
1522 , order_internalnote = ?
1523 | if defined $order->{order_internalnote};
1525 $query .= q| where biblionumber=? and ordernumber=?|;
1527 my $sth = $dbh->prepare( $query );
1528 my @params = ( $quantrec, $datereceived, $invoice->{invoiceid}, ( $budget_id ? $budget_id : $order->{budget_id} ) );
1530 if ( defined $order->{replacementprice} ) {
1531 push @params, $order->{replacementprice};
1534 if ( defined $order->{unitprice} ) {
1535 push @params, $order->{unitprice}, $order->{unitprice_tax_included}, $order->{unitprice_tax_excluded};
1538 if ( defined $order->{tax_value_on_receiving} ) {
1539 push @params, $order->{tax_value_on_receiving};
1542 if ( defined $order->{tax_rate_on_receiving} ) {
1543 push @params, $order->{tax_rate_on_receiving};
1546 if ( defined $order->{order_internalnote} ) {
1547 push @params, $order->{order_internalnote};
1550 push @params, ( $biblionumber, $order->{ordernumber} );
1552 $sth->execute( @params );
1554 # All items have been received, sent a notification to users
1555 NotifyOrderUsers( $order->{ordernumber} );
1558 return ($datereceived, $new_ordernumber);
1561 =head3 CancelReceipt
1563 my $parent_ordernumber = CancelReceipt($ordernumber);
1565 Cancel an order line receipt and update the parent order line, as if no
1567 If items are created at receipt (AcqCreateItem = receiving) then delete
1573 my $ordernumber = shift;
1575 return unless $ordernumber;
1577 my $dbh = C4::Context->dbh;
1579 SELECT datereceived, parent_ordernumber, quantity
1581 WHERE ordernumber = ?
1583 my $sth = $dbh->prepare($query);
1584 $sth->execute($ordernumber);
1585 my $order = $sth->fetchrow_hashref;
1587 warn "CancelReceipt: order $ordernumber does not exist";
1590 unless($order->{'datereceived'}) {
1591 warn "CancelReceipt: order $ordernumber is not received";
1595 my $parent_ordernumber = $order->{'parent_ordernumber'};
1597 my $order_obj = Koha::Acquisition::Orders->find( $ordernumber ); # FIXME rewrite all this subroutine using this object
1598 my @itemnumbers = $order_obj->items->get_column('itemnumber');
1600 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1601 # The order line has no parent, just mark it as not received
1604 SET quantityreceived = ?,
1607 orderstatus = 'ordered'
1608 WHERE ordernumber = ?
1610 $sth = $dbh->prepare($query);
1611 $sth->execute(0, undef, undef, $ordernumber);
1612 _cancel_items_receipt( $order_obj );
1614 # The order line has a parent, increase parent quantity and delete
1617 SELECT quantity, datereceived
1619 WHERE ordernumber = ?
1621 $sth = $dbh->prepare($query);
1622 $sth->execute($parent_ordernumber);
1623 my $parent_order = $sth->fetchrow_hashref;
1624 unless($parent_order) {
1625 warn "Parent order $parent_ordernumber does not exist.";
1628 if($parent_order->{'datereceived'}) {
1629 warn "CancelReceipt: parent order is received.".
1630 " Can't cancel receipt.";
1636 orderstatus = 'ordered'
1637 WHERE ordernumber = ?
1639 $sth = $dbh->prepare($query);
1640 my $rv = $sth->execute(
1641 $order->{'quantity'} + $parent_order->{'quantity'},
1645 warn "Cannot update parent order line, so do not cancel".
1650 # Recalculate tax_value
1654 tax_value_on_ordering = quantity * | . get_rounding_sql(q|ecost_tax_excluded|) . q| * tax_rate_on_ordering,
1655 tax_value_on_receiving = quantity * | . get_rounding_sql(q|unitprice_tax_excluded|) . q| * tax_rate_on_receiving
1656 WHERE ordernumber = ?
1657 |, undef, $parent_ordernumber);
1659 _cancel_items_receipt( $order_obj, $parent_ordernumber );
1662 DELETE FROM aqorders
1663 WHERE ordernumber = ?
1665 $sth = $dbh->prepare($query);
1666 $sth->execute($ordernumber);
1670 if( $order_obj->basket->effective_create_items eq 'ordering' ) {
1671 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1673 for my $in ( @itemnumbers ) {
1674 my $item = Koha::Items->find( $in ); # FIXME We do not need that, we already have Koha::Items from $order_obj->items
1675 my $biblio = $item->biblio;
1676 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber', $biblio->frameworkcode );
1677 my $item_marc = C4::Items::GetMarcItem( $biblio->biblionumber, $in );
1678 for my $affect ( @affects ) {
1679 my ( $sf, $v ) = split q{=}, $affect, 2;
1680 foreach ( $item_marc->field($itemfield) ) {
1681 $_->update( $sf => $v );
1684 C4::Items::ModItemFromMarc( $item_marc, $biblio->biblionumber, $in );
1689 return $parent_ordernumber;
1692 sub _cancel_items_receipt {
1693 my ( $order, $parent_ordernumber ) = @_;
1694 $parent_ordernumber ||= $order->ordernumber;
1696 my $items = $order->items;
1697 if ( $order->basket->effective_create_items eq 'receiving' ) {
1698 # Remove items that were created at receipt
1700 DELETE FROM items, aqorders_items
1701 USING items, aqorders_items
1702 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1704 my $dbh = C4::Context->dbh;
1705 my $sth = $dbh->prepare($query);
1706 while ( my $item = $items->next ) {
1707 $sth->execute($item->itemnumber, $item->itemnumber);
1711 while ( my $item = $items->next ) {
1712 ModItemOrder($item->itemnumber, $parent_ordernumber);
1717 #------------------------------------------------------------#
1721 @results = &SearchOrders({
1722 ordernumber => $ordernumber,
1725 booksellerid => $booksellerid,
1726 basketno => $basketno,
1727 basketname => $basketname,
1728 basketgroupname => $basketgroupname,
1732 biblionumber => $biblionumber,
1733 budget_id => $budget_id
1736 Searches for orders filtered by criteria.
1738 C<$ordernumber> Finds matching orders or transferred orders by ordernumber.
1739 C<$search> Finds orders matching %$search% in title, author, or isbn.
1740 C<$owner> Finds order for the logged in user.
1741 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1742 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1745 C<@results> is an array of references-to-hash with the keys are fields
1746 from aqorders, biblio, biblioitems and aqbasket tables.
1751 my ( $params ) = @_;
1752 my $ordernumber = $params->{ordernumber};
1753 my $search = $params->{search};
1754 my $ean = $params->{ean};
1755 my $booksellerid = $params->{booksellerid};
1756 my $basketno = $params->{basketno};
1757 my $basketname = $params->{basketname};
1758 my $basketgroupname = $params->{basketgroupname};
1759 my $owner = $params->{owner};
1760 my $pending = $params->{pending};
1761 my $ordered = $params->{ordered};
1762 my $biblionumber = $params->{biblionumber};
1763 my $budget_id = $params->{budget_id};
1765 my $dbh = C4::Context->dbh;
1768 SELECT aqbasket.basketno,
1770 borrowers.firstname,
1773 biblioitems.biblioitemnumber,
1774 biblioitems.publishercode,
1775 biblioitems.publicationyear,
1776 aqbasket.authorisedby,
1777 aqbasket.booksellerid,
1779 aqbasket.creationdate,
1780 aqbasket.basketname,
1781 aqbasketgroups.id as basketgroupid,
1782 aqbasketgroups.name as basketgroupname,
1785 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1786 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1787 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1788 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1789 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1792 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1794 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1798 WHERE (datecancellationprinted is NULL)
1801 if ( $pending or $ordered ) {
1804 ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) )
1806 ( quantity > quantityreceived OR quantityreceived is NULL )
1810 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1818 my $userenv = C4::Context->userenv;
1819 if ( C4::Context->preference("IndependentBranches") ) {
1820 unless ( C4::Context->IsSuperLibrarian() ) {
1823 borrowers.branchcode = ?
1824 OR borrowers.branchcode = ''
1827 push @args, $userenv->{branch};
1831 if ( $ordernumber ) {
1832 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1833 push @args, ( $ordernumber, $ordernumber );
1835 if ( $biblionumber ) {
1836 $query .= 'AND aqorders.biblionumber = ?';
1837 push @args, $biblionumber;
1840 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1841 push @args, ("%$search%","%$search%","%$search%");
1844 $query .= ' AND biblioitems.ean = ?';
1847 if ( $booksellerid ) {
1848 $query .= 'AND aqbasket.booksellerid = ?';
1849 push @args, $booksellerid;
1852 $query .= 'AND aqbasket.basketno = ?';
1853 push @args, $basketno;
1856 $query .= 'AND aqbasket.basketname LIKE ?';
1857 push @args, "%$basketname%";
1859 if( $basketgroupname ) {
1860 $query .= ' AND aqbasketgroups.name LIKE ?';
1861 push @args, "%$basketgroupname%";
1865 $query .= ' AND aqbasket.authorisedby=? ';
1866 push @args, $userenv->{'number'};
1870 $query .= ' AND aqorders.budget_id = ?';
1871 push @args, $budget_id;
1874 $query .= ' ORDER BY aqbasket.basketno';
1876 my $sth = $dbh->prepare($query);
1877 $sth->execute(@args);
1878 return $sth->fetchall_arrayref({});
1881 #------------------------------------------------------------#
1885 &DelOrder($biblionumber, $ordernumber);
1887 Cancel the order with the given order and biblio numbers. It does not
1888 delete any entries in the aqorders table, it merely marks them as
1894 my ( $bibnum, $ordernumber, $delete_biblio, $reason ) = @_;
1896 my $dbh = C4::Context->dbh;
1899 SET datecancellationprinted=now(), orderstatus='cancelled'
1902 $query .= ", cancellationreason = ? ";
1905 WHERE biblionumber=? AND ordernumber=?
1907 my $sth = $dbh->prepare($query);
1909 $sth->execute($reason, $bibnum, $ordernumber);
1911 $sth->execute( $bibnum, $ordernumber );
1915 my $order = Koha::Acquisition::Orders->find($ordernumber);
1916 my $items = $order->items;
1917 while ( my $item = $items->next ) { # Should be moved to Koha::Acquisition::Order->delete
1918 my $delcheck = C4::Items::DelItemCheck( $bibnum, $item->itemnumber );
1920 if($delcheck != 1) {
1921 $error->{'delitem'} = 1;
1925 if($delete_biblio) {
1926 # We get the number of remaining items
1927 my $biblio = Koha::Biblios->find( $bibnum );
1928 my $itemcount = $biblio->items->count;
1930 # If there are no items left,
1931 if ( $itemcount == 0 ) {
1932 # We delete the record
1933 my $delcheck = DelBiblio($bibnum);
1936 $error->{'delbiblio'} = 1;
1944 =head3 TransferOrder
1946 my $newordernumber = TransferOrder($ordernumber, $basketno);
1948 Transfer an order line to a basket.
1949 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1950 to BOOKSELLER on DATE' and create new order with internal note
1951 'Transferred from BOOKSELLER on DATE'.
1952 Move all attached items to the new order.
1953 Received orders cannot be transferred.
1954 Return the ordernumber of created order.
1959 my ($ordernumber, $basketno) = @_;
1961 return unless ($ordernumber and $basketno);
1963 my $order = Koha::Acquisition::Orders->find( $ordernumber ) or return;
1964 return if $order->datereceived;
1966 $order = $order->unblessed;
1968 my $basket = GetBasket($basketno);
1969 return unless $basket;
1971 my $dbh = C4::Context->dbh;
1972 my ($query, $sth, $rv);
1976 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1977 WHERE ordernumber = ?
1979 $sth = $dbh->prepare($query);
1980 $rv = $sth->execute('cancelled', $ordernumber);
1982 delete $order->{'ordernumber'};
1983 delete $order->{parent_ordernumber};
1984 $order->{'basketno'} = $basketno;
1986 my $newordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber;
1989 UPDATE aqorders_items
1991 WHERE ordernumber = ?
1993 $sth = $dbh->prepare($query);
1994 $sth->execute($newordernumber, $ordernumber);
1997 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
2000 $sth = $dbh->prepare($query);
2001 $sth->execute($ordernumber, $newordernumber);
2003 return $newordernumber;
2006 =head3 get_rounding_sql
2008 $rounding_sql = get_rounding_sql($column_name);
2010 returns the correct SQL routine based on OrderPriceRounding system preference.
2014 sub get_rounding_sql {
2015 my ( $round_string ) = @_;
2016 my $rounding_pref = C4::Context->preference('OrderPriceRounding') // q{};
2017 if ( $rounding_pref eq "nearest_cent" ) {
2018 return "CAST($round_string*100 AS SIGNED)/100";
2020 return $round_string;
2023 =head3 get_rounded_price
2025 $rounded_price = get_rounded_price( $price );
2027 returns a price rounded as specified in OrderPriceRounding system preference.
2031 sub get_rounded_price {
2033 my $rounding_pref = C4::Context->preference('OrderPriceRounding') // q{};
2034 if( $rounding_pref eq 'nearest_cent' ) {
2035 return Koha::Number::Price->new( $price )->round();
2041 =head2 FUNCTIONS ABOUT PARCELS
2045 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
2047 get a lists of parcels.
2054 is the bookseller this function has to get parcels.
2057 To know on what criteria the results list has to be ordered.
2060 is the booksellerinvoicenumber.
2062 =item $datefrom & $dateto
2063 to know on what date this function has to filter its search.
2068 a pointer on a hash list containing parcel informations as such :
2074 =item Last operation
2076 =item Number of biblio
2078 =item Number of items
2085 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
2086 my $dbh = C4::Context->dbh;
2087 my @query_params = ();
2089 SELECT aqinvoices.invoicenumber,
2090 datereceived,purchaseordernumber,
2091 count(DISTINCT biblionumber) AS biblio,
2092 sum(quantity) AS itemsexpected,
2093 sum(quantityreceived) AS itemsreceived
2094 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
2095 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2096 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
2098 push @query_params, $bookseller;
2100 if ( defined $code ) {
2101 $strsth .= ' and aqinvoices.invoicenumber like ? ';
2102 # add a % to the end of the code to allow stemming.
2103 push @query_params, "$code%";
2106 if ( defined $datefrom ) {
2107 $strsth .= ' and datereceived >= ? ';
2108 push @query_params, $datefrom;
2111 if ( defined $dateto ) {
2112 $strsth .= 'and datereceived <= ? ';
2113 push @query_params, $dateto;
2116 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
2118 # can't use a placeholder to place this column name.
2119 # but, we could probably be checking to make sure it is a column that will be fetched.
2120 $strsth .= "order by $order " if ($order);
2122 my $sth = $dbh->prepare($strsth);
2124 $sth->execute( @query_params );
2125 my $results = $sth->fetchall_arrayref({});
2129 #------------------------------------------------------------#
2131 =head3 GetLateOrders
2133 @results = &GetLateOrders;
2135 Searches for bookseller with late orders.
2138 the table of supplier with late issues. This table is full of hashref.
2144 my $supplierid = shift;
2146 my $estimateddeliverydatefrom = shift;
2147 my $estimateddeliverydateto = shift;
2149 my $dbh = C4::Context->dbh;
2151 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2152 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2154 my @query_params = ();
2156 SELECT aqbasket.basketno,
2157 aqorders.ordernumber,
2158 DATE(aqbasket.closedate) AS orderdate,
2159 aqbasket.basketname AS basketname,
2160 aqbasket.basketgroupid AS basketgroupid,
2161 aqbasketgroups.name AS basketgroupname,
2162 aqorders.rrp AS unitpricesupplier,
2163 aqorders.ecost AS unitpricelib,
2164 aqorders.claims_count AS claims_count,
2165 aqorders.claimed_date AS claimed_date,
2166 aqbudgets.budget_name AS budget,
2167 borrowers.branchcode AS branch,
2168 aqbooksellers.name AS supplier,
2169 aqbooksellers.id AS supplierid,
2170 biblio.author, biblio.title,
2171 biblioitems.publishercode AS publisher,
2172 biblioitems.publicationyear,
2173 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2177 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2178 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2179 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2180 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2181 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2182 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2183 WHERE aqorders.basketno = aqbasket.basketno
2184 AND ( datereceived = ''
2185 OR datereceived IS NULL
2186 OR aqorders.quantityreceived < aqorders.quantity
2188 AND aqbasket.closedate IS NOT NULL
2189 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2191 if ($dbdriver eq "mysql") {
2193 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2194 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2195 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2197 if ( defined $delay ) {
2198 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2199 push @query_params, $delay;
2201 $from .= " AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0";
2203 # FIXME: account for IFNULL as above
2205 aqorders.quantity AS quantity,
2206 aqorders.quantity * aqorders.rrp AS subtotal,
2207 (CAST(now() AS date) - closedate) AS latesince
2209 if ( defined $delay ) {
2210 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2211 push @query_params, $delay;
2213 $from .= " AND aqorders.quantity <> 0";
2215 if (defined $supplierid) {
2216 $from .= ' AND aqbasket.booksellerid = ? ';
2217 push @query_params, $supplierid;
2219 if (defined $branch) {
2220 $from .= ' AND borrowers.branchcode LIKE ? ';
2221 push @query_params, $branch;
2224 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2225 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2227 if ( defined $estimateddeliverydatefrom ) {
2228 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2229 push @query_params, $estimateddeliverydatefrom;
2231 if ( defined $estimateddeliverydateto ) {
2232 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2233 push @query_params, $estimateddeliverydateto;
2235 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2236 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2238 if (C4::Context->preference("IndependentBranches")
2239 && !C4::Context->IsSuperLibrarian() ) {
2240 $from .= ' AND borrowers.branchcode LIKE ? ';
2241 push @query_params, C4::Context->userenv->{branch};
2243 $from .= " AND orderstatus <> 'cancelled' ";
2244 my $query = "$select $from \nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2245 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2246 my $sth = $dbh->prepare($query);
2247 $sth->execute(@query_params);
2249 while (my $data = $sth->fetchrow_hashref) {
2250 push @results, $data;
2255 #------------------------------------------------------------#
2259 \@order_loop = GetHistory( %params );
2261 Retreives some acquisition history information
2271 basket - search both basket name and number
2272 booksellerinvoicenumber
2275 orderstatus (note that orderstatus '' will retrieve orders
2276 of any status except cancelled)
2278 get_canceled_order (if set to a true value, cancelled orders will
2282 $order_loop is a list of hashrefs that each look like this:
2284 'author' => 'Twain, Mark',
2286 'biblionumber' => '215',
2288 'creationdate' => 'MM/DD/YYYY',
2289 'datereceived' => undef,
2292 'invoicenumber' => undef,
2294 'ordernumber' => '1',
2296 'quantityreceived' => undef,
2297 'title' => 'The Adventures of Huckleberry Finn'
2303 # don't run the query if there are no parameters (list would be too long for sure !)
2304 croak "No search params" unless @_;
2306 my $title = $params{title};
2307 my $author = $params{author};
2308 my $isbn = $params{isbn};
2309 my $ean = $params{ean};
2310 my $name = $params{name};
2311 my $from_placed_on = $params{from_placed_on};
2312 my $to_placed_on = $params{to_placed_on};
2313 my $basket = $params{basket};
2314 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2315 my $basketgroupname = $params{basketgroupname};
2316 my $budget = $params{budget};
2317 my $orderstatus = $params{orderstatus};
2318 my $biblionumber = $params{biblionumber};
2319 my $get_canceled_order = $params{get_canceled_order} || 0;
2320 my $ordernumber = $params{ordernumber};
2321 my $search_children_too = $params{search_children_too} || 0;
2322 my $created_by = $params{created_by} || [];
2323 my $ordernumbers = $params{ordernumbers} || [];
2327 my $total_qtyreceived = 0;
2328 my $total_price = 0;
2330 #get variation of isbn
2334 if ( C4::Context->preference("SearchWithISBNVariations") ){
2335 @isbns = C4::Koha::GetVariationsOfISBN( $isbn );
2336 foreach my $isb (@isbns){
2337 push @isbn_params, '?';
2342 push @isbn_params, '?';
2346 my $dbh = C4::Context->dbh;
2349 COALESCE(biblio.title, deletedbiblio.title) AS title,
2350 COALESCE(biblio.author, deletedbiblio.author) AS author,
2351 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2352 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2354 aqbasket.basketname,
2355 aqbasket.basketgroupid,
2356 aqbasket.authorisedby,
2357 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2358 aqbasketgroups.name as groupname,
2360 aqbasket.creationdate,
2361 aqorders.datereceived,
2363 aqorders.quantityreceived,
2365 aqorders.ordernumber,
2367 aqinvoices.invoicenumber,
2368 aqbooksellers.id as id,
2369 aqorders.biblionumber,
2370 aqorders.orderstatus,
2371 aqorders.parent_ordernumber,
2372 aqbudgets.budget_name
2374 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2377 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2378 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2379 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2380 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2381 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2382 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2383 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2384 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2385 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2386 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2389 $query .= " WHERE 1 ";
2391 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2392 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2395 my @query_params = ();
2397 if ( $biblionumber ) {
2398 $query .= " AND biblio.biblionumber = ?";
2399 push @query_params, $biblionumber;
2403 $query .= " AND biblio.title LIKE ? ";
2404 $title =~ s/\s+/%/g;
2405 push @query_params, "%$title%";
2409 $query .= " AND biblio.author LIKE ? ";
2410 push @query_params, "%$author%";
2414 $query .= " AND ( biblioitems.isbn LIKE " . join (" OR biblioitems.isbn LIKE ", @isbn_params ) . ")";
2415 foreach my $isb (@isbns){
2416 push @query_params, "%$isb%";
2421 $query .= " AND biblioitems.ean = ? ";
2422 push @query_params, "$ean";
2425 $query .= " AND aqbooksellers.name LIKE ? ";
2426 push @query_params, "%$name%";
2430 $query .= " AND aqbudgets.budget_id = ? ";
2431 push @query_params, "$budget";
2434 if ( $from_placed_on ) {
2435 $query .= " AND creationdate >= ? ";
2436 push @query_params, $from_placed_on;
2439 if ( $to_placed_on ) {
2440 $query .= " AND creationdate <= ? ";
2441 push @query_params, $to_placed_on;
2444 if ( defined $orderstatus and $orderstatus ne '') {
2445 $query .= " AND aqorders.orderstatus = ? ";
2446 push @query_params, "$orderstatus";
2450 if ($basket =~ m/^\d+$/) {
2451 $query .= " AND aqorders.basketno = ? ";
2452 push @query_params, $basket;
2454 $query .= " AND aqbasket.basketname LIKE ? ";
2455 push @query_params, "%$basket%";
2459 if ($booksellerinvoicenumber) {
2460 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2461 push @query_params, "%$booksellerinvoicenumber%";
2464 if ($basketgroupname) {
2465 $query .= " AND aqbasketgroups.name LIKE ? ";
2466 push @query_params, "%$basketgroupname%";
2470 $query .= " AND (aqorders.ordernumber = ? ";
2471 push @query_params, $ordernumber;
2472 if ($search_children_too) {
2473 $query .= " OR aqorders.parent_ordernumber = ? ";
2474 push @query_params, $ordernumber;
2479 if ( @$created_by ) {
2480 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2481 push @query_params, @$created_by;
2484 if ( @$ordernumbers ) {
2485 $query .= ' AND (aqorders.ordernumber IN ( ' . join (',', ('?') x @$ordernumbers ) . '))';
2486 push @query_params, @$ordernumbers;
2489 if ( C4::Context->preference("IndependentBranches") ) {
2490 unless ( C4::Context->IsSuperLibrarian() ) {
2491 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2492 push @query_params, C4::Context->userenv->{branch};
2495 $query .= " ORDER BY id";
2497 return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2500 =head2 GetRecentAcqui
2502 $results = GetRecentAcqui($days);
2504 C<$results> is a ref to a table which contains hashref
2508 sub GetRecentAcqui {
2510 my $dbh = C4::Context->dbh;
2514 ORDER BY timestamp DESC
2517 my $sth = $dbh->prepare($query);
2519 my $results = $sth->fetchall_arrayref({});
2523 #------------------------------------------------------------#
2527 &AddClaim($ordernumber);
2529 Add a claim for an order
2534 my ($ordernumber) = @_;
2535 my $dbh = C4::Context->dbh;
2538 claims_count = claims_count + 1,
2539 claimed_date = CURDATE()
2540 WHERE ordernumber = ?
2542 my $sth = $dbh->prepare($query);
2543 $sth->execute($ordernumber);
2548 my @invoices = GetInvoices(
2549 invoicenumber => $invoicenumber,
2550 supplierid => $supplierid,
2551 suppliername => $suppliername,
2552 shipmentdatefrom => $shipmentdatefrom, # ISO format
2553 shipmentdateto => $shipmentdateto, # ISO format
2554 billingdatefrom => $billingdatefrom, # ISO format
2555 billingdateto => $billingdateto, # ISO format
2556 isbneanissn => $isbn_or_ean_or_issn,
2559 publisher => $publisher,
2560 publicationyear => $publicationyear,
2561 branchcode => $branchcode,
2562 order_by => $order_by
2565 Return a list of invoices that match all given criteria.
2567 $order_by is "column_name (asc|desc)", where column_name is any of
2568 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2569 'shipmentcost', 'shipmentcost_budgetid'.
2571 asc is the default if omitted
2578 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2579 closedate shipmentcost shipmentcost_budgetid);
2581 my $dbh = C4::Context->dbh;
2583 SELECT aqinvoices.invoiceid, aqinvoices.invoicenumber, aqinvoices.booksellerid, aqinvoices.shipmentdate, aqinvoices.billingdate, aqinvoices.closedate, aqinvoices.shipmentcost, aqinvoices.shipmentcost_budgetid, aqinvoices.message_id,
2584 aqbooksellers.name AS suppliername,
2587 aqorders.datereceived IS NOT NULL,
2588 aqorders.biblionumber,
2591 ) AS receivedbiblios,
2594 aqorders.subscriptionid IS NOT NULL,
2595 aqorders.subscriptionid,
2598 ) AS is_linked_to_subscriptions,
2599 SUM(aqorders.quantityreceived) AS receiveditems
2601 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2602 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2603 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2604 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2605 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2606 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2607 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2612 if($args{supplierid}) {
2613 push @bind_strs, " aqinvoices.booksellerid = ? ";
2614 push @bind_args, $args{supplierid};
2616 if($args{invoicenumber}) {
2617 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2618 push @bind_args, "%$args{invoicenumber}%";
2620 if($args{suppliername}) {
2621 push @bind_strs, " aqbooksellers.name LIKE ? ";
2622 push @bind_args, "%$args{suppliername}%";
2624 if($args{shipmentdatefrom}) {
2625 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2626 push @bind_args, $args{shipmentdatefrom};
2628 if($args{shipmentdateto}) {
2629 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2630 push @bind_args, $args{shipmentdateto};
2632 if($args{billingdatefrom}) {
2633 push @bind_strs, " aqinvoices.billingdate >= ? ";
2634 push @bind_args, $args{billingdatefrom};
2636 if($args{billingdateto}) {
2637 push @bind_strs, " aqinvoices.billingdate <= ? ";
2638 push @bind_args, $args{billingdateto};
2640 if($args{isbneanissn}) {
2641 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2642 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2645 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2646 push @bind_args, $args{title};
2649 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2650 push @bind_args, $args{author};
2652 if($args{publisher}) {
2653 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2654 push @bind_args, $args{publisher};
2656 if($args{publicationyear}) {
2657 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2658 push @bind_args, $args{publicationyear}, $args{publicationyear};
2660 if($args{branchcode}) {
2661 push @bind_strs, " borrowers.branchcode = ? ";
2662 push @bind_args, $args{branchcode};
2664 if($args{message_id}) {
2665 push @bind_strs, " aqinvoices.message_id = ? ";
2666 push @bind_args, $args{message_id};
2669 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2670 $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";
2672 if($args{order_by}) {
2673 my ($column, $direction) = split / /, $args{order_by};
2674 if(grep /^$column$/, @columns) {
2675 $direction ||= 'ASC';
2676 $query .= " ORDER BY $column $direction";
2680 my $sth = $dbh->prepare($query);
2681 $sth->execute(@bind_args);
2683 my $results = $sth->fetchall_arrayref({});
2689 my $invoice = GetInvoice($invoiceid);
2691 Get informations about invoice with given $invoiceid
2693 Return a hash filled with aqinvoices.* fields
2698 my ($invoiceid) = @_;
2701 return unless $invoiceid;
2703 my $dbh = C4::Context->dbh;
2709 my $sth = $dbh->prepare($query);
2710 $sth->execute($invoiceid);
2712 $invoice = $sth->fetchrow_hashref;
2716 =head3 GetInvoiceDetails
2718 my $invoice = GetInvoiceDetails($invoiceid)
2720 Return informations about an invoice + the list of related order lines
2722 Orders informations are in $invoice->{orders} (array ref)
2726 sub GetInvoiceDetails {
2727 my ($invoiceid) = @_;
2729 if ( !defined $invoiceid ) {
2730 carp 'GetInvoiceDetails called without an invoiceid';
2734 my $dbh = C4::Context->dbh;
2736 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2738 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2741 my $sth = $dbh->prepare($query);
2742 $sth->execute($invoiceid);
2744 my $invoice = $sth->fetchrow_hashref;
2749 biblio.copyrightdate,
2751 biblioitems.publishercode,
2752 biblioitems.publicationyear,
2753 aqbasket.basketname,
2754 aqbasketgroups.id AS basketgroupid,
2755 aqbasketgroups.name AS basketgroupname
2757 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2758 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2759 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2760 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2763 $sth = $dbh->prepare($query);
2764 $sth->execute($invoiceid);
2765 $invoice->{orders} = $sth->fetchall_arrayref({});
2766 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2773 my $invoiceid = AddInvoice(
2774 invoicenumber => $invoicenumber,
2775 booksellerid => $booksellerid,
2776 shipmentdate => $shipmentdate,
2777 billingdate => $billingdate,
2778 closedate => $closedate,
2779 shipmentcost => $shipmentcost,
2780 shipmentcost_budgetid => $shipmentcost_budgetid
2783 Create a new invoice and return its id or undef if it fails.
2790 return unless(%invoice and $invoice{invoicenumber});
2792 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2793 closedate shipmentcost shipmentcost_budgetid message_id);
2797 foreach my $key (keys %invoice) {
2798 if(0 < grep(/^$key$/, @columns)) {
2799 push @set_strs, "$key = ?";
2800 push @set_args, ($invoice{$key} || undef);
2806 my $dbh = C4::Context->dbh;
2807 my $query = "INSERT INTO aqinvoices SET ";
2808 $query .= join (",", @set_strs);
2809 my $sth = $dbh->prepare($query);
2810 $rv = $sth->execute(@set_args);
2812 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2821 invoiceid => $invoiceid, # Mandatory
2822 invoicenumber => $invoicenumber,
2823 booksellerid => $booksellerid,
2824 shipmentdate => $shipmentdate,
2825 billingdate => $billingdate,
2826 closedate => $closedate,
2827 shipmentcost => $shipmentcost,
2828 shipmentcost_budgetid => $shipmentcost_budgetid
2831 Modify an invoice, invoiceid is mandatory.
2833 Return undef if it fails.
2840 return unless(%invoice and $invoice{invoiceid});
2842 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2843 closedate shipmentcost shipmentcost_budgetid);
2847 foreach my $key (keys %invoice) {
2848 if(0 < grep(/^$key$/, @columns)) {
2849 push @set_strs, "$key = ?";
2850 push @set_args, ($invoice{$key} || undef);
2854 my $dbh = C4::Context->dbh;
2855 my $query = "UPDATE aqinvoices SET ";
2856 $query .= join(",", @set_strs);
2857 $query .= " WHERE invoiceid = ?";
2859 my $sth = $dbh->prepare($query);
2860 $sth->execute(@set_args, $invoice{invoiceid});
2865 CloseInvoice($invoiceid);
2869 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2874 my ($invoiceid) = @_;
2876 return unless $invoiceid;
2878 my $dbh = C4::Context->dbh;
2881 SET closedate = CAST(NOW() AS DATE)
2884 my $sth = $dbh->prepare($query);
2885 $sth->execute($invoiceid);
2888 =head3 ReopenInvoice
2890 ReopenInvoice($invoiceid);
2894 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => output_pref({ dt=>dt_from_string, dateonly=>1, otputpref=>'iso' }))
2899 my ($invoiceid) = @_;
2901 return unless $invoiceid;
2903 my $dbh = C4::Context->dbh;
2906 SET closedate = NULL
2909 my $sth = $dbh->prepare($query);
2910 $sth->execute($invoiceid);
2915 DelInvoice($invoiceid);
2917 Delete an invoice if there are no items attached to it.
2922 my ($invoiceid) = @_;
2924 return unless $invoiceid;
2926 my $dbh = C4::Context->dbh;
2932 my $sth = $dbh->prepare($query);
2933 $sth->execute($invoiceid);
2934 my $res = $sth->fetchrow_arrayref;
2935 if ( $res && $res->[0] == 0 ) {
2937 DELETE FROM aqinvoices
2940 my $sth = $dbh->prepare($query);
2941 return ( $sth->execute($invoiceid) > 0 );
2946 =head3 MergeInvoices
2948 MergeInvoices($invoiceid, \@sourceids);
2950 Merge the invoices identified by the IDs in \@sourceids into
2951 the invoice identified by $invoiceid.
2956 my ($invoiceid, $sourceids) = @_;
2958 return unless $invoiceid;
2959 foreach my $sourceid (@$sourceids) {
2960 next if $sourceid == $invoiceid;
2961 my $source = GetInvoiceDetails($sourceid);
2962 foreach my $order (@{$source->{'orders'}}) {
2963 $order->{'invoiceid'} = $invoiceid;
2966 DelInvoice($source->{'invoiceid'});
2971 =head3 GetBiblioCountByBasketno
2973 $biblio_count = &GetBiblioCountByBasketno($basketno);
2975 Looks up the biblio's count that has basketno value $basketno
2981 sub GetBiblioCountByBasketno {
2982 my ($basketno) = @_;
2983 my $dbh = C4::Context->dbh;
2985 SELECT COUNT( DISTINCT( biblionumber ) )
2988 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2991 my $sth = $dbh->prepare($query);
2992 $sth->execute($basketno);
2993 return $sth->fetchrow;
2996 =head3 populate_order_with_prices
2998 $order = populate_order_with_prices({
2999 order => $order #a hashref with the order values
3000 booksellerid => $booksellerid #FIXME - should obtain from order basket
3001 receiving => 1 # boolean representing order stage, should pass only this or ordering
3002 ordering => 1 # boolean representing order stage
3006 Sets calculated values for an order - all values are stored with pull precision regardless of rounding preference except fot
3007 tax value which is calculated on rounded values if requested
3009 For ordering the values set are:
3014 tax_value_on_ordering
3015 For receiving the value set are:
3016 unitprice_tax_included
3017 unitprice_tax_excluded
3018 tax_value_on_receiving
3020 Note: When receiving if the rounded value of the unitprice matches the rounded value of the ecost then then ecost (full precision) is used.
3022 Returns a hashref of the order
3024 FIXME: Move this to Koha::Acquisition::Order.pm
3028 sub populate_order_with_prices {
3031 my $order = $params->{order};
3032 my $booksellerid = $params->{booksellerid};
3033 return unless $booksellerid;
3035 my $bookseller = Koha::Acquisition::Booksellers->find( $booksellerid );
3037 my $receiving = $params->{receiving};
3038 my $ordering = $params->{ordering};
3039 my $discount = $order->{discount};
3040 $discount /= 100 if $discount > 1;
3043 $order->{tax_rate_on_ordering} //= $order->{tax_rate};
3044 if ( $bookseller->listincgst ) {
3045 # The user entered the rrp tax included
3046 $order->{rrp_tax_included} = $order->{rrp};
3048 # rrp tax excluded = rrp tax included / ( 1 + tax rate )
3049 $order->{rrp_tax_excluded} = $order->{rrp_tax_included} / ( 1 + $order->{tax_rate_on_ordering} );
3051 # ecost tax included = rrp tax included ( 1 - discount )
3052 $order->{ecost_tax_included} = $order->{rrp_tax_included} * ( 1 - $discount );
3054 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
3055 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
3057 # tax value = quantity * ecost tax excluded * tax rate
3058 $order->{tax_value_on_ordering} = ( get_rounded_price($order->{ecost_tax_included}) - get_rounded_price($order->{ecost_tax_excluded}) ) * $order->{quantity};
3062 # The user entered the rrp tax excluded
3063 $order->{rrp_tax_excluded} = $order->{rrp};
3065 # rrp tax included = rrp tax excluded * ( 1 - tax rate )
3066 $order->{rrp_tax_included} = $order->{rrp_tax_excluded} * ( 1 + $order->{tax_rate_on_ordering} );
3068 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
3069 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
3071 # ecost tax included = rrp tax excluded * ( 1 + tax rate ) * ( 1 - discount ) = ecost tax excluded * ( 1 + tax rate )
3072 $order->{ecost_tax_included} = $order->{ecost_tax_excluded} * ( 1 + $order->{tax_rate_on_ordering} );
3074 # tax value = quantity * ecost tax included * tax rate
3075 $order->{tax_value_on_ordering} = $order->{quantity} * get_rounded_price($order->{ecost_tax_excluded}) * $order->{tax_rate_on_ordering};
3080 $order->{tax_rate_on_receiving} //= $order->{tax_rate};
3081 if ( $bookseller->invoiceincgst ) {
3082 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
3083 # we need to keep the exact ecost value
3084 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_included} )->round ) {
3085 $order->{unitprice} = $order->{ecost_tax_included};
3088 # The user entered the unit price tax included
3089 $order->{unitprice_tax_included} = $order->{unitprice};
3091 # unit price tax excluded = unit price tax included / ( 1 + tax rate )
3092 $order->{unitprice_tax_excluded} = $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate_on_receiving} );
3095 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
3096 # we need to keep the exact ecost value
3097 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_excluded} )->round ) {
3098 $order->{unitprice} = $order->{ecost_tax_excluded};
3101 # The user entered the unit price tax excluded
3102 $order->{unitprice_tax_excluded} = $order->{unitprice};
3105 # unit price tax included = unit price tax included * ( 1 + tax rate )
3106 $order->{unitprice_tax_included} = $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate_on_receiving} );
3109 # tax value = quantity * unit price tax excluded * tax rate
3110 $order->{tax_value_on_receiving} = $order->{quantity} * get_rounded_price($order->{unitprice_tax_excluded}) * $order->{tax_rate_on_receiving};
3116 =head3 GetOrderUsers
3118 $order_users_ids = &GetOrderUsers($ordernumber);
3120 Returns a list of all borrowernumbers that are in order users list
3125 my ($ordernumber) = @_;
3127 return unless $ordernumber;
3130 SELECT borrowernumber
3132 WHERE ordernumber = ?
3134 my $dbh = C4::Context->dbh;
3135 my $sth = $dbh->prepare($query);
3136 $sth->execute($ordernumber);
3137 my $results = $sth->fetchall_arrayref( {} );
3139 my @borrowernumbers;
3140 foreach (@$results) {
3141 push @borrowernumbers, $_->{'borrowernumber'};
3144 return @borrowernumbers;
3147 =head3 ModOrderUsers
3149 my @order_users_ids = (1, 2, 3);
3150 &ModOrderUsers($ordernumber, @basketusers_ids);
3152 Delete all users from order users list, and add users in C<@order_users_ids>
3158 my ( $ordernumber, @order_users_ids ) = @_;
3160 return unless $ordernumber;
3162 my $dbh = C4::Context->dbh;
3164 DELETE FROM aqorder_users
3165 WHERE ordernumber = ?
3167 my $sth = $dbh->prepare($query);
3168 $sth->execute($ordernumber);
3171 INSERT INTO aqorder_users (ordernumber, borrowernumber)
3174 $sth = $dbh->prepare($query);
3175 foreach my $order_user_id (@order_users_ids) {
3176 $sth->execute( $ordernumber, $order_user_id );
3180 sub NotifyOrderUsers {
3181 my ($ordernumber) = @_;
3183 my @borrowernumbers = GetOrderUsers($ordernumber);
3184 return unless @borrowernumbers;
3186 my $order = GetOrder( $ordernumber );
3187 for my $borrowernumber (@borrowernumbers) {
3188 my $patron = Koha::Patrons->find( $borrowernumber );
3189 my $library = $patron->library->unblessed;
3190 my $biblio = Koha::Biblios->find( $order->{biblionumber} )->unblessed;
3191 my $letter = C4::Letters::GetPreparedLetter(
3192 module => 'acquisition',
3193 letter_code => 'ACQ_NOTIF_ON_RECEIV',
3194 branchcode => $library->{branchcode},
3195 lang => $patron->lang,
3197 'branches' => $library,
3198 'borrowers' => $patron->unblessed,
3199 'biblio' => $biblio,
3200 'aqorders' => $order,
3204 C4::Letters::EnqueueLetter(
3207 borrowernumber => $borrowernumber,
3208 LibraryName => C4::Context->preference("LibraryName"),
3209 message_transport_type => 'email',
3211 ) or warn "can't enqueue letter $letter";
3216 =head3 FillWithDefaultValues
3218 FillWithDefaultValues( $marc_record );
3220 This will update the record with default value defined in the ACQ framework.
3221 For all existing fields, if a default value exists and there are no subfield, it will be created.
3222 If the field does not exist, it will be created too.
3226 sub FillWithDefaultValues {
3228 my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ', { unsafe => 1 } );
3231 C4::Biblio::GetMarcFromKohaField( 'items.itemnumber', '' );
3232 for my $tag ( sort keys %$tagslib ) {
3234 next if $tag == $itemfield;
3235 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
3236 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
3237 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
3238 if ( defined $defaultvalue and $defaultvalue ne '' ) {
3239 my @fields = $record->field($tag);
3241 for my $field (@fields) {
3242 unless ( defined $field->subfield($subfield) ) {
3243 $field->add_subfields(
3244 $subfield => $defaultvalue );
3249 $record->insert_fields_ordered(
3251 $tag, '', '', $subfield => $defaultvalue
3266 Koha Development Team <http://koha-community.org/>