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;
35 use Koha::Number::Price;
37 use Koha::CsvProfiles;
47 use vars qw(@ISA @EXPORT);
53 &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
54 &GetBasketAsCSV &GetBasketGroupAsCSV
55 &GetBasketsByBookseller &GetBasketsByBasketgroup
56 &GetBasketsInfosByBookseller
58 &GetBasketUsers &ModBasketUsers
63 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
64 &GetBasketgroups &ReOpenBasketgroup
66 &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
67 &GetLateOrders &GetOrderFromItemnumber
68 &SearchOrders &GetHistory &GetRecentAcqui
69 &ModReceiveOrder &CancelReceipt
71 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
86 &GetItemnumbersFromOrder
89 &GetBiblioCountByBasketno
95 &FillWithDefaultValues
103 sub GetOrderFromItemnumber {
104 my ($itemnumber) = @_;
105 my $dbh = C4::Context->dbh;
108 SELECT * from aqorders LEFT JOIN aqorders_items
109 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
110 WHERE itemnumber = ? |;
112 my $sth = $dbh->prepare($query);
116 $sth->execute($itemnumber);
118 my $order = $sth->fetchrow_hashref;
123 # Returns the itemnumber(s) associated with the ordernumber given in parameter
124 sub GetItemnumbersFromOrder {
125 my ($ordernumber) = @_;
126 my $dbh = C4::Context->dbh;
127 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
128 my $sth = $dbh->prepare($query);
129 $sth->execute($ordernumber);
132 while (my $order = $sth->fetchrow_hashref) {
133 push @tab, $order->{'itemnumber'};
147 C4::Acquisition - Koha functions for dealing with orders and acquisitions
155 The functions in this module deal with acquisitions, managing book
156 orders, basket and parcels.
160 =head2 FUNCTIONS ABOUT BASKETS
164 $aqbasket = &GetBasket($basketnumber);
166 get all basket informations in aqbasket for a given basket
168 B<returns:> informations for a given basket returned as a hashref.
174 my $dbh = C4::Context->dbh;
177 concat( b.firstname,' ',b.surname) AS authorisedbyname
179 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
182 my $sth=$dbh->prepare($query);
183 $sth->execute($basketno);
184 my $basket = $sth->fetchrow_hashref;
188 #------------------------------------------------------------#
192 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
193 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace, $is_standing, $create_items );
195 Create a new basket in aqbasket table
199 =item C<$booksellerid> is a foreign key in the aqbasket table
201 =item C<$authorizedby> is the username of who created the basket
205 The other parameters are optional, see ModBasketHeader for more info on them.
210 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
211 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
212 $billingplace, $is_standing, $create_items ) = @_;
213 my $dbh = C4::Context->dbh;
215 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
216 . 'VALUES (now(),?,?)';
217 $dbh->do( $query, {}, $booksellerid, $authorisedby );
219 my $basket = $dbh->{mysql_insertid};
220 $basketname ||= q{}; # default to empty strings
222 $basketbooksellernote ||= q{};
223 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
224 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items );
228 #------------------------------------------------------------#
232 &CloseBasket($basketno);
234 close a basket (becomes unmodifiable, except for receives)
240 my $dbh = C4::Context->dbh;
241 $dbh->do('UPDATE aqbasket SET closedate=now() WHERE basketno=?', {}, $basketno );
244 q{UPDATE aqorders SET orderstatus = 'ordered' WHERE basketno = ? AND orderstatus NOT IN ( 'complete', 'cancelled')},
252 &ReopenBasket($basketno);
260 my $dbh = C4::Context->dbh;
261 $dbh->do( q{UPDATE aqbasket SET closedate=NULL WHERE basketno=?}, {}, $basketno );
265 SET orderstatus = 'new'
267 AND orderstatus NOT IN ( 'complete', 'cancelled' )
272 #------------------------------------------------------------#
274 =head3 GetBasketAsCSV
276 &GetBasketAsCSV($basketno);
278 Export a basket as CSV
280 $cgi parameter is needed for column name translation
285 my ($basketno, $cgi, $csv_profile_id) = @_;
286 my $basket = GetBasket($basketno);
287 my @orders = GetOrders($basketno);
288 my $contract = GetContract({
289 contractnumber => $basket->{'contractnumber'}
292 my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
294 if ($csv_profile_id) {
295 my $csv_profile = Koha::CsvProfiles->find( $csv_profile_id );
296 die "There is no valid csv profile given" unless $csv_profile;
298 my $csv = Text::CSV_XS->new({'quote_char'=>'"','escape_char'=>'"','sep_char'=>$csv_profile->csv_separator,'binary'=>1});
299 my $csv_profile_content = $csv_profile->content;
300 my ( @headers, @fields );
301 while ( $csv_profile_content =~ /
304 ([^\|]*) # fieldname (table.row or row)
308 my $field = ($2 eq '') ? $1 : $2;
310 $header =~ s/^\s+|\s+$//g; # Trim whitespaces
311 push @headers, $header;
313 $field =~ s/[^\.]*\.{1}//; # Remove the table name if exists.
314 $field =~ s/^\s+|\s+$//g; # Trim whitespaces
315 push @fields, $field;
317 for my $order (@orders) {
319 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
320 my $biblioitem = $biblio->biblioitem;
321 $order = { %$order, %{ $biblioitem->unblessed } };
323 $order = {%$order, %$contract};
325 $order = {%$order, %$basket, %{ $biblio->unblessed }};
326 for my $field (@fields) {
327 push @row, $order->{$field};
331 my $content = join( $csv_profile->csv_separator, @headers ) . "\n";
332 for my $row ( @rows ) {
333 $csv->combine(@$row);
334 my $string = $csv->string;
335 $content .= $string . "\n";
340 foreach my $order (@orders) {
341 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
342 my $biblioitem = $biblio->biblioitem;
344 contractname => $contract->{'contractname'},
345 ordernumber => $order->{'ordernumber'},
346 entrydate => $order->{'entrydate'},
347 isbn => $order->{'isbn'},
348 author => $biblio->author,
349 title => $biblio->title,
350 publicationyear => $biblioitem->publicationyear,
351 publishercode => $biblioitem->publishercode,
352 collectiontitle => $biblioitem->collectiontitle,
353 notes => $order->{'order_vendornote'},
354 quantity => $order->{'quantity'},
355 rrp => $order->{'rrp'},
357 for my $place ( qw( deliveryplace billingplace ) ) {
358 if ( my $library = Koha::Libraries->find( $row->{deliveryplace} ) ) {
359 $row->{$place} = $library->branchname
363 contractname author title publishercode collectiontitle notes
364 deliveryplace billingplace
366 # Double the quotes to not be interpreted as a field end
367 $row->{$_} =~ s/"/""/g if $row->{$_};
373 if(defined $a->{publishercode} and defined $b->{publishercode}) {
374 $a->{publishercode} cmp $b->{publishercode};
378 $template->param(rows => \@rows);
380 return $template->output;
385 =head3 GetBasketGroupAsCSV
387 &GetBasketGroupAsCSV($basketgroupid);
389 Export a basket group as CSV
391 $cgi parameter is needed for column name translation
395 sub GetBasketGroupAsCSV {
396 my ($basketgroupid, $cgi) = @_;
397 my $baskets = GetBasketsByBasketgroup($basketgroupid);
399 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
402 for my $basket (@$baskets) {
403 my @orders = GetOrders( $basket->{basketno} );
404 my $contract = GetContract({
405 contractnumber => $basket->{contractnumber}
407 my $bookseller = Koha::Acquisition::Booksellers->find( $basket->{booksellerid} );
408 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
410 foreach my $order (@orders) {
411 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
412 my $biblioitem = $biblio->biblioitem;
414 clientnumber => $bookseller->accountnumber,
415 basketname => $basket->{basketname},
416 ordernumber => $order->{ordernumber},
417 author => $biblio->author,
418 title => $biblio->title,
419 publishercode => $biblioitem->publishercode,
420 publicationyear => $biblioitem->publicationyear,
421 collectiontitle => $biblioitem->collectiontitle,
422 isbn => $order->{isbn},
423 quantity => $order->{quantity},
424 rrp_tax_included => $order->{rrp_tax_included},
425 rrp_tax_excluded => $order->{rrp_tax_excluded},
426 discount => $bookseller->discount,
427 ecost_tax_included => $order->{ecost_tax_included},
428 ecost_tax_excluded => $order->{ecost_tax_excluded},
429 notes => $order->{order_vendornote},
430 entrydate => $order->{entrydate},
431 booksellername => $bookseller->name,
432 bookselleraddress => $bookseller->address1,
433 booksellerpostal => $bookseller->postal,
434 contractnumber => $contract->{contractnumber},
435 contractname => $contract->{contractname},
438 basketgroupdeliveryplace => $basketgroup->{deliveryplace},
439 basketgroupbillingplace => $basketgroup->{billingplace},
440 basketdeliveryplace => $basket->{deliveryplace},
441 basketbillingplace => $basket->{billingplace},
443 for my $place (qw( basketgroupdeliveryplace basketgroupbillingplace basketdeliveryplace basketbillingplace )) {
444 if ( my $library = Koha::Libraries->find( $temp->{$place} ) ) {
445 $row->{$place} = $library->branchname;
449 basketname author title publishercode collectiontitle notes
450 booksellername bookselleraddress booksellerpostal contractname
451 basketgroupdeliveryplace basketgroupbillingplace
452 basketdeliveryplace basketbillingplace
454 # Double the quotes to not be interpreted as a field end
455 $row->{$_} =~ s/"/""/g if $row->{$_};
460 $template->param(rows => \@rows);
462 return $template->output;
466 =head3 CloseBasketgroup
468 &CloseBasketgroup($basketgroupno);
474 sub CloseBasketgroup {
475 my ($basketgroupno) = @_;
476 my $dbh = C4::Context->dbh;
477 my $sth = $dbh->prepare("
478 UPDATE aqbasketgroups
482 $sth->execute($basketgroupno);
485 #------------------------------------------------------------#
487 =head3 ReOpenBaskergroup($basketgroupno)
489 &ReOpenBaskergroup($basketgroupno);
495 sub ReOpenBasketgroup {
496 my ($basketgroupno) = @_;
497 my $dbh = C4::Context->dbh;
498 my $sth = $dbh->prepare("
499 UPDATE aqbasketgroups
503 $sth->execute($basketgroupno);
506 #------------------------------------------------------------#
511 &DelBasket($basketno);
513 Deletes the basket that has basketno field $basketno in the aqbasket table.
517 =item C<$basketno> is the primary key of the basket in the aqbasket table.
524 my ( $basketno ) = @_;
525 my $query = "DELETE FROM aqbasket WHERE basketno=?";
526 my $dbh = C4::Context->dbh;
527 my $sth = $dbh->prepare($query);
528 $sth->execute($basketno);
532 #------------------------------------------------------------#
536 &ModBasket($basketinfo);
538 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
542 =item C<$basketno> is the primary key of the basket in the aqbasket table.
549 my $basketinfo = shift;
550 my $query = "UPDATE aqbasket SET ";
552 foreach my $key (keys %$basketinfo){
553 if ($key ne 'basketno'){
554 $query .= "$key=?, ";
555 push(@params, $basketinfo->{$key} || undef );
558 # get rid of the "," at the end of $query
559 if (substr($query, length($query)-2) eq ', '){
564 $query .= "WHERE basketno=?";
565 push(@params, $basketinfo->{'basketno'});
566 my $dbh = C4::Context->dbh;
567 my $sth = $dbh->prepare($query);
568 $sth->execute(@params);
573 #------------------------------------------------------------#
575 =head3 ModBasketHeader
577 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
579 Modifies a basket's header.
583 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
585 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
587 =item C<$note> is the "note" field in the "aqbasket" table;
589 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
591 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
593 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
595 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
597 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
599 =item C<$is_standing> is the "is_standing" field in the aqbasket table.
601 =item C<$create_items> should be set to 'ordering', 'receiving' or 'cataloguing' (or undef, in which
602 case the AcqCreateItem syspref takes precedence).
608 sub ModBasketHeader {
609 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items) = @_;
614 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?, is_standing=?, create_items=?
618 my $dbh = C4::Context->dbh;
619 my $sth = $dbh->prepare($query);
620 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items || undef, $basketno);
622 if ( $contractnumber ) {
623 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
624 my $sth2 = $dbh->prepare($query2);
625 $sth2->execute($contractnumber,$basketno);
630 #------------------------------------------------------------#
632 =head3 GetBasketsByBookseller
634 @results = &GetBasketsByBookseller($booksellerid, $extra);
636 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
640 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
642 =item C<$extra> is the extra sql parameters, can be
644 $extra->{groupby}: group baskets by column
645 ex. $extra->{groupby} = aqbasket.basketgroupid
646 $extra->{orderby}: order baskets by column
647 $extra->{limit}: limit number of results (can be helpful for pagination)
653 sub GetBasketsByBookseller {
654 my ($booksellerid, $extra) = @_;
655 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
657 if ($extra->{groupby}) {
658 $query .= " GROUP by $extra->{groupby}";
660 if ($extra->{orderby}){
661 $query .= " ORDER by $extra->{orderby}";
663 if ($extra->{limit}){
664 $query .= " LIMIT $extra->{limit}";
667 my $dbh = C4::Context->dbh;
668 my $sth = $dbh->prepare($query);
669 $sth->execute($booksellerid);
670 return $sth->fetchall_arrayref({});
673 =head3 GetBasketsInfosByBookseller
675 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
677 The optional second parameter allbaskets is a boolean allowing you to
678 select all baskets from the supplier; by default only active baskets (open or
679 closed but still something to receive) are returned.
681 Returns in a arrayref of hashref all about booksellers baskets, plus:
682 total_biblios: Number of distinct biblios in basket
683 total_items: Number of items in basket
684 expected_items: Number of non-received items in basket
688 sub GetBasketsInfosByBookseller {
689 my ($supplierid, $allbaskets) = @_;
691 return unless $supplierid;
693 my $dbh = C4::Context->dbh;
695 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,
696 SUM(aqorders.quantity) AS total_items,
698 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
699 ) AS total_items_cancelled,
700 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
702 IF(aqorders.datereceived IS NULL
703 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
708 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
709 WHERE booksellerid = ?};
711 $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";
713 unless ( $allbaskets ) {
714 # Don't show the basket if it's NOT CLOSED or is FULLY RECEIVED
715 $query.=" HAVING (closedate IS NULL OR (expected_items > 0))"
718 my $sth = $dbh->prepare($query);
719 $sth->execute($supplierid);
720 my $baskets = $sth->fetchall_arrayref({});
722 # Retrieve the number of biblios cancelled
723 my $cancelled_biblios = $dbh->selectall_hashref( q|
724 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
726 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
727 WHERE booksellerid = ?
728 AND aqorders.orderstatus = 'cancelled'
729 GROUP BY aqbasket.basketno
730 |, 'basketno', {}, $supplierid );
732 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
738 =head3 GetBasketUsers
740 $basketusers_ids = &GetBasketUsers($basketno);
742 Returns a list of all borrowernumbers that are in basket users list
747 my $basketno = shift;
749 return unless $basketno;
752 SELECT borrowernumber
756 my $dbh = C4::Context->dbh;
757 my $sth = $dbh->prepare($query);
758 $sth->execute($basketno);
759 my $results = $sth->fetchall_arrayref( {} );
762 foreach (@$results) {
763 push @borrowernumbers, $_->{'borrowernumber'};
766 return @borrowernumbers;
769 =head3 ModBasketUsers
771 my @basketusers_ids = (1, 2, 3);
772 &ModBasketUsers($basketno, @basketusers_ids);
774 Delete all users from basket users list, and add users in C<@basketusers_ids>
780 my ($basketno, @basketusers_ids) = @_;
782 return unless $basketno;
784 my $dbh = C4::Context->dbh;
786 DELETE FROM aqbasketusers
789 my $sth = $dbh->prepare($query);
790 $sth->execute($basketno);
793 INSERT INTO aqbasketusers (basketno, borrowernumber)
796 $sth = $dbh->prepare($query);
797 foreach my $basketuser_id (@basketusers_ids) {
798 $sth->execute($basketno, $basketuser_id);
803 =head3 CanUserManageBasket
805 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
806 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
808 Check if a borrower can manage a basket, according to system preference
809 AcqViewBaskets, user permissions and basket properties (creator, users list,
812 First parameter can be either a borrowernumber or a hashref as returned by
813 Koha::Patron->unblessed
815 Second parameter can be either a basketno or a hashref as returned by
816 C4::Acquisition::GetBasket.
818 The third parameter is optional. If given, it should be a hashref as returned
819 by C4::Auth::getuserflags. If not, getuserflags is called.
821 If user is authorised to manage basket, returns 1.
826 sub CanUserManageBasket {
827 my ($borrower, $basket, $userflags) = @_;
829 if (!ref $borrower) {
830 # FIXME This needs to be replaced
831 # We should not accept both scalar and array
832 # Tests need to be updated
833 $borrower = Koha::Patrons->find( $borrower )->unblessed;
836 $basket = GetBasket($basket);
839 return 0 unless ($basket and $borrower);
841 my $borrowernumber = $borrower->{borrowernumber};
842 my $basketno = $basket->{basketno};
844 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
846 if (!defined $userflags) {
847 my $dbh = C4::Context->dbh;
848 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
849 $sth->execute($borrowernumber);
850 my ($flags) = $sth->fetchrow_array;
853 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
856 unless ($userflags->{superlibrarian}
857 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
858 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
860 if (not exists $userflags->{acquisition}) {
864 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
865 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
869 if ($AcqViewBaskets eq 'user'
870 && $basket->{authorisedby} != $borrowernumber
871 && ! grep { $borrowernumber eq $_ } GetBasketUsers($basketno)) {
875 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
876 && $basket->{branch} ne $borrower->{branchcode}) {
884 #------------------------------------------------------------#
886 =head3 GetBasketsByBasketgroup
888 $baskets = &GetBasketsByBasketgroup($basketgroupid);
890 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
894 sub GetBasketsByBasketgroup {
895 my $basketgroupid = shift;
897 SELECT *, aqbasket.booksellerid as booksellerid
899 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
901 my $dbh = C4::Context->dbh;
902 my $sth = $dbh->prepare($query);
903 $sth->execute($basketgroupid);
904 return $sth->fetchall_arrayref({});
907 #------------------------------------------------------------#
909 =head3 NewBasketgroup
911 $basketgroupid = NewBasketgroup(\%hashref);
913 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
915 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
917 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
919 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
921 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
923 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
925 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
927 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
929 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
934 my $basketgroupinfo = shift;
935 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
936 my $query = "INSERT INTO aqbasketgroups (";
938 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
939 if ( defined $basketgroupinfo->{$field} ) {
940 $query .= "$field, ";
941 push(@params, $basketgroupinfo->{$field});
944 $query .= "booksellerid) VALUES (";
949 push(@params, $basketgroupinfo->{'booksellerid'});
950 my $dbh = C4::Context->dbh;
951 my $sth = $dbh->prepare($query);
952 $sth->execute(@params);
953 my $basketgroupid = $dbh->{'mysql_insertid'};
954 if( $basketgroupinfo->{'basketlist'} ) {
955 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
956 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
957 my $sth2 = $dbh->prepare($query2);
958 $sth2->execute($basketgroupid, $basketno);
961 return $basketgroupid;
964 #------------------------------------------------------------#
966 =head3 ModBasketgroup
968 ModBasketgroup(\%hashref);
970 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
972 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
974 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
976 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
978 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
980 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
982 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
984 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
986 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
991 my $basketgroupinfo = shift;
992 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
993 my $dbh = C4::Context->dbh;
994 my $query = "UPDATE aqbasketgroups SET ";
996 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
997 if ( defined $basketgroupinfo->{$field} ) {
998 $query .= "$field=?, ";
999 push(@params, $basketgroupinfo->{$field});
1004 $query .= " WHERE id=?";
1005 push(@params, $basketgroupinfo->{'id'});
1006 my $sth = $dbh->prepare($query);
1007 $sth->execute(@params);
1009 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
1010 $sth->execute($basketgroupinfo->{'id'});
1012 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
1013 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
1014 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
1015 $sth->execute($basketgroupinfo->{'id'}, $basketno);
1021 #------------------------------------------------------------#
1023 =head3 DelBasketgroup
1025 DelBasketgroup($basketgroupid);
1027 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
1031 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
1037 sub DelBasketgroup {
1038 my $basketgroupid = shift;
1039 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1040 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
1041 my $dbh = C4::Context->dbh;
1042 my $sth = $dbh->prepare($query);
1043 $sth->execute($basketgroupid);
1047 #------------------------------------------------------------#
1050 =head2 FUNCTIONS ABOUT ORDERS
1052 =head3 GetBasketgroup
1054 $basketgroup = &GetBasketgroup($basketgroupid);
1056 Returns a reference to the hash containing all information about the basketgroup.
1060 sub GetBasketgroup {
1061 my $basketgroupid = shift;
1062 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1063 my $dbh = C4::Context->dbh;
1064 my $result_set = $dbh->selectall_arrayref(
1065 'SELECT * FROM aqbasketgroups WHERE id=?',
1069 return $result_set->[0]; # id is unique
1072 #------------------------------------------------------------#
1074 =head3 GetBasketgroups
1076 $basketgroups = &GetBasketgroups($booksellerid);
1078 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1082 sub GetBasketgroups {
1083 my $booksellerid = shift;
1084 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1085 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1086 my $dbh = C4::Context->dbh;
1087 my $sth = $dbh->prepare($query);
1088 $sth->execute($booksellerid);
1089 return $sth->fetchall_arrayref({});
1092 #------------------------------------------------------------#
1094 =head2 FUNCTIONS ABOUT ORDERS
1098 @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } );
1100 Looks up the pending (non-cancelled) orders with the given basket
1103 If cancelled is set, only cancelled orders will be returned.
1108 my ( $basketno, $params ) = @_;
1110 return () unless $basketno;
1112 my $orderby = $params->{orderby};
1113 my $cancelled = $params->{cancelled} || 0;
1115 my $dbh = C4::Context->dbh;
1117 SELECT biblio.*,biblioitems.*,
1121 $query .= $cancelled
1123 aqorders_transfers.ordernumber_to AS transferred_to,
1124 aqorders_transfers.timestamp AS transferred_to_timestamp
1127 aqorders_transfers.ordernumber_from AS transferred_from,
1128 aqorders_transfers.timestamp AS transferred_from_timestamp
1132 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1133 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1134 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1136 $query .= $cancelled
1138 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1141 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1149 $orderby ||= q|biblioitems.publishercode, biblio.title|;
1151 AND (datecancellationprinted IS NOT NULL
1152 AND datecancellationprinted <> '0000-00-00')
1157 q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|;
1159 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1163 $query .= " ORDER BY $orderby";
1165 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1170 #------------------------------------------------------------#
1172 =head3 GetOrdersByBiblionumber
1174 @orders = &GetOrdersByBiblionumber($biblionumber);
1176 Looks up the orders with linked to a specific $biblionumber, including
1177 cancelled orders and received orders.
1180 C<@orders> is an array of references-to-hash, whose keys are the
1181 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1185 sub GetOrdersByBiblionumber {
1186 my $biblionumber = shift;
1187 return unless $biblionumber;
1188 my $dbh = C4::Context->dbh;
1190 SELECT biblio.*,biblioitems.*,
1194 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1195 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1196 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1197 WHERE aqorders.biblionumber=?
1200 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1201 return @{$result_set};
1205 #------------------------------------------------------------#
1209 $order = &GetOrder($ordernumber);
1211 Looks up an order by order number.
1213 Returns a reference-to-hash describing the order. The keys of
1214 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1219 my ($ordernumber) = @_;
1220 return unless $ordernumber;
1222 my $dbh = C4::Context->dbh;
1223 my $query = qq{SELECT
1227 aqbasket.basketname,
1228 borrowers.branchcode,
1229 biblioitems.publicationyear,
1230 biblio.copyrightdate,
1231 biblioitems.editionstatement,
1235 biblioitems.publishercode,
1236 aqorders.rrp AS unitpricesupplier,
1237 aqorders.ecost AS unitpricelib,
1238 aqorders.claims_count AS claims_count,
1239 aqorders.claimed_date AS claimed_date,
1240 aqbudgets.budget_name AS budget,
1241 aqbooksellers.name AS supplier,
1242 aqbooksellers.id AS supplierid,
1243 biblioitems.publishercode AS publisher,
1244 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1245 DATE(aqbasket.closedate) AS orderdate,
1246 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1247 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1248 DATEDIFF(CURDATE( ),closedate) AS latesince
1249 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1250 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1251 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1252 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1253 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1254 WHERE aqorders.basketno = aqbasket.basketno
1257 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1259 # result_set assumed to contain 1 match
1260 return $result_set->[0];
1263 =head3 GetLastOrderNotReceivedFromSubscriptionid
1265 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1267 Returns a reference-to-hash describing the last order not received for a subscription.
1271 sub GetLastOrderNotReceivedFromSubscriptionid {
1272 my ( $subscriptionid ) = @_;
1273 my $dbh = C4::Context->dbh;
1275 SELECT * FROM aqorders
1276 LEFT JOIN subscription
1277 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1278 WHERE aqorders.subscriptionid = ?
1279 AND aqorders.datereceived IS NULL
1283 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1285 # result_set assumed to contain 1 match
1286 return $result_set->[0];
1289 =head3 GetLastOrderReceivedFromSubscriptionid
1291 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1293 Returns a reference-to-hash describing the last order received for a subscription.
1297 sub GetLastOrderReceivedFromSubscriptionid {
1298 my ( $subscriptionid ) = @_;
1299 my $dbh = C4::Context->dbh;
1301 SELECT * FROM aqorders
1302 LEFT JOIN subscription
1303 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1304 WHERE aqorders.subscriptionid = ?
1305 AND aqorders.datereceived =
1307 SELECT MAX( aqorders.datereceived )
1309 LEFT JOIN subscription
1310 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1311 WHERE aqorders.subscriptionid = ?
1312 AND aqorders.datereceived IS NOT NULL
1314 ORDER BY ordernumber DESC
1318 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1320 # result_set assumed to contain 1 match
1321 return $result_set->[0];
1325 #------------------------------------------------------------#
1329 &ModOrder(\%hashref);
1331 Modifies an existing order. Updates the order with order number
1332 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1333 other keys of the hash update the fields with the same name in the aqorders
1334 table of the Koha database.
1339 my $orderinfo = shift;
1341 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '';
1343 my $dbh = C4::Context->dbh;
1346 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1347 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1349 # delete($orderinfo->{'branchcode'});
1350 # the hash contains a lot of entries not in aqorders, so get the columns ...
1351 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1353 my $colnames = $sth->{NAME};
1354 #FIXME Be careful. If aqorders would have columns with diacritics,
1355 #you should need to decode what you get back from NAME.
1356 #See report 10110 and guided_reports.pl
1357 my $query = "UPDATE aqorders SET ";
1359 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1360 # ... and skip hash entries that are not in the aqorders table
1361 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1362 next unless grep(/^$orderinfokey$/, @$colnames);
1363 $query .= "$orderinfokey=?, ";
1364 push(@params, $orderinfo->{$orderinfokey});
1367 $query .= "timestamp=NOW() WHERE ordernumber=?";
1368 push(@params, $orderinfo->{'ordernumber'} );
1369 $sth = $dbh->prepare($query);
1370 $sth->execute(@params);
1374 #------------------------------------------------------------#
1378 ModItemOrder($itemnumber, $ordernumber);
1380 Modifies the ordernumber of an item in aqorders_items.
1385 my ($itemnumber, $ordernumber) = @_;
1387 return unless ($itemnumber and $ordernumber);
1389 my $dbh = C4::Context->dbh;
1391 UPDATE aqorders_items
1393 WHERE itemnumber = ?
1395 my $sth = $dbh->prepare($query);
1396 return $sth->execute($ordernumber, $itemnumber);
1399 #------------------------------------------------------------#
1401 =head3 ModReceiveOrder
1403 my ( $date_received, $new_ordernumber ) = ModReceiveOrder(
1405 biblionumber => $biblionumber,
1407 quantityreceived => $quantityreceived,
1409 invoice => $invoice,
1410 budget_id => $budget_id,
1411 received_itemnumbers => \@received_itemnumbers,
1412 order_internalnote => $order_internalnote,
1416 Updates an order, to reflect the fact that it was received, at least
1419 If a partial order is received, splits the order into two.
1421 Updates the order with biblionumber C<$biblionumber> and ordernumber
1422 C<$order->{ordernumber}>.
1427 sub ModReceiveOrder {
1429 my $biblionumber = $params->{biblionumber};
1430 my $order = { %{ $params->{order} } }; # Copy the order, we don't want to modify it
1431 my $invoice = $params->{invoice};
1432 my $quantrec = $params->{quantityreceived};
1433 my $user = $params->{user};
1434 my $budget_id = $params->{budget_id};
1435 my $received_items = $params->{received_items};
1437 my $dbh = C4::Context->dbh;
1438 my $datereceived = ( $invoice and $invoice->{datereceived} ) ? $invoice->{datereceived} : dt_from_string;
1439 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1440 if ($suggestionid) {
1441 ModSuggestion( {suggestionid=>$suggestionid,
1442 STATUS=>'AVAILABLE',
1443 biblionumber=> $biblionumber}
1447 my $result_set = $dbh->selectrow_arrayref(
1448 q{SELECT aqbasket.is_standing
1450 WHERE basketno=?},{ Slice => {} }, $order->{basketno});
1451 my $is_standing = $result_set->[0]; # we assume we have a unique basket
1453 my $new_ordernumber = $order->{ordernumber};
1454 if ( $is_standing || $order->{quantity} > $quantrec ) {
1455 # Split order line in two parts: the first is the original order line
1456 # without received items (the quantity is decreased),
1457 # the second part is a new order line with quantity=quantityrec
1458 # (entirely received)
1462 orderstatus = 'partial'|;
1463 $query .= q|, order_internalnote = ?| if defined $order->{order_internalnote};
1464 $query .= q| WHERE ordernumber = ?|;
1465 my $sth = $dbh->prepare($query);
1468 ( $is_standing ? 1 : ($order->{quantity} - $quantrec) ),
1469 ( defined $order->{order_internalnote} ? $order->{order_internalnote} : () ),
1470 $order->{ordernumber}
1473 # Recalculate tax_value
1477 tax_value_on_ordering = quantity * ecost_tax_excluded * tax_rate_on_ordering,
1478 tax_value_on_receiving = quantity * unitprice_tax_excluded * tax_rate_on_receiving
1479 WHERE ordernumber = ?
1480 |, undef, $order->{ordernumber});
1482 delete $order->{ordernumber};
1483 $order->{budget_id} = ( $budget_id || $order->{budget_id} );
1484 $order->{quantity} = $quantrec;
1485 $order->{quantityreceived} = $quantrec;
1486 $order->{ecost_tax_excluded} //= 0;
1487 $order->{tax_rate_on_ordering} //= 0;
1488 $order->{unitprice_tax_excluded} //= 0;
1489 $order->{tax_rate_on_receiving} //= 0;
1490 $order->{tax_value_on_ordering} = $order->{quantity} * $order->{ecost_tax_excluded} * $order->{tax_rate_on_ordering};
1491 $order->{tax_value_on_receiving} = $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate_on_receiving};
1492 $order->{datereceived} = $datereceived;
1493 $order->{invoiceid} = $invoice->{invoiceid};
1494 $order->{orderstatus} = 'complete';
1495 $new_ordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber; # TODO What if the store fails?
1497 if ($received_items) {
1498 foreach my $itemnumber (@$received_items) {
1499 ModItemOrder($itemnumber, $new_ordernumber);
1505 SET quantityreceived = ?,
1509 orderstatus = 'complete'
1513 , unitprice = ?, unitprice_tax_included = ?, unitprice_tax_excluded = ?
1514 | if defined $order->{unitprice};
1517 ,tax_value_on_receiving = ?
1518 | if defined $order->{tax_value_on_receiving};
1521 ,tax_rate_on_receiving = ?
1522 | if defined $order->{tax_rate_on_receiving};
1525 , order_internalnote = ?
1526 | if defined $order->{order_internalnote};
1528 $query .= q| where biblionumber=? and ordernumber=?|;
1530 my $sth = $dbh->prepare( $query );
1531 my @params = ( $quantrec, $datereceived, $invoice->{invoiceid}, ( $budget_id ? $budget_id : $order->{budget_id} ) );
1533 if ( defined $order->{unitprice} ) {
1534 push @params, $order->{unitprice}, $order->{unitprice_tax_included}, $order->{unitprice_tax_excluded};
1537 if ( defined $order->{tax_value_on_receiving} ) {
1538 push @params, $order->{tax_value_on_receiving};
1541 if ( defined $order->{tax_rate_on_receiving} ) {
1542 push @params, $order->{tax_rate_on_receiving};
1545 if ( defined $order->{order_internalnote} ) {
1546 push @params, $order->{order_internalnote};
1549 push @params, ( $biblionumber, $order->{ordernumber} );
1551 $sth->execute( @params );
1553 # All items have been received, sent a notification to users
1554 NotifyOrderUsers( $order->{ordernumber} );
1557 return ($datereceived, $new_ordernumber);
1560 =head3 CancelReceipt
1562 my $parent_ordernumber = CancelReceipt($ordernumber);
1564 Cancel an order line receipt and update the parent order line, as if no
1566 If items are created at receipt (AcqCreateItem = receiving) then delete
1572 my $ordernumber = shift;
1574 return unless $ordernumber;
1576 my $dbh = C4::Context->dbh;
1578 SELECT datereceived, parent_ordernumber, quantity
1580 WHERE ordernumber = ?
1582 my $sth = $dbh->prepare($query);
1583 $sth->execute($ordernumber);
1584 my $order = $sth->fetchrow_hashref;
1586 warn "CancelReceipt: order $ordernumber does not exist";
1589 unless($order->{'datereceived'}) {
1590 warn "CancelReceipt: order $ordernumber is not received";
1594 my $parent_ordernumber = $order->{'parent_ordernumber'};
1596 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1597 my $order_obj = Koha::Acquisition::Orders->find( $ordernumber ); # FIXME rewrite all this subroutine using this object
1599 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1600 # The order line has no parent, just mark it as not received
1603 SET quantityreceived = ?,
1606 orderstatus = 'ordered'
1607 WHERE ordernumber = ?
1609 $sth = $dbh->prepare($query);
1610 $sth->execute(0, undef, undef, $ordernumber);
1611 _cancel_items_receipt( $order_obj );
1613 # The order line has a parent, increase parent quantity and delete
1616 SELECT quantity, datereceived
1618 WHERE ordernumber = ?
1620 $sth = $dbh->prepare($query);
1621 $sth->execute($parent_ordernumber);
1622 my $parent_order = $sth->fetchrow_hashref;
1623 unless($parent_order) {
1624 warn "Parent order $parent_ordernumber does not exist.";
1627 if($parent_order->{'datereceived'}) {
1628 warn "CancelReceipt: parent order is received.".
1629 " Can't cancel receipt.";
1635 orderstatus = 'ordered'
1636 WHERE ordernumber = ?
1638 $sth = $dbh->prepare($query);
1639 my $rv = $sth->execute(
1640 $order->{'quantity'} + $parent_order->{'quantity'},
1644 warn "Cannot update parent order line, so do not cancel".
1649 # Recalculate tax_value
1653 tax_value_on_ordering = quantity * ecost_tax_excluded * tax_rate_on_ordering,
1654 tax_value_on_receiving = quantity * unitprice_tax_excluded * tax_rate_on_receiving
1655 WHERE ordernumber = ?
1656 |, undef, $parent_ordernumber);
1658 _cancel_items_receipt( $order_obj, $parent_ordernumber );
1661 DELETE FROM aqorders
1662 WHERE ordernumber = ?
1664 $sth = $dbh->prepare($query);
1665 $sth->execute($ordernumber);
1669 if( $order_obj->basket->effective_create_items eq 'ordering' ) {
1670 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1672 for my $in ( @itemnumbers ) {
1673 my $item = Koha::Items->find( $in );
1674 my $biblio = $item->biblio;
1675 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber', $biblio->frameworkcode );
1676 my $item_marc = C4::Items::GetMarcItem( $biblio->biblionumber, $in );
1677 for my $affect ( @affects ) {
1678 my ( $sf, $v ) = split q{=}, $affect, 2;
1679 foreach ( $item_marc->field($itemfield) ) {
1680 $_->update( $sf => $v );
1683 C4::Items::ModItemFromMarc( $item_marc, $biblio->biblionumber, $in );
1688 return $parent_ordernumber;
1691 sub _cancel_items_receipt {
1692 my ( $order, $parent_ordernumber ) = @_;
1693 $parent_ordernumber ||= $order->ordernumber;
1695 my @itemnumbers = GetItemnumbersFromOrder($order->ordernumber); # FIXME Must be $order->items
1696 if ( $order->basket->effective_create_items eq 'receiving' ) {
1697 # Remove items that were created at receipt
1699 DELETE FROM items, aqorders_items
1700 USING items, aqorders_items
1701 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1703 my $dbh = C4::Context->dbh;
1704 my $sth = $dbh->prepare($query);
1705 foreach my $itemnumber (@itemnumbers) {
1706 $sth->execute($itemnumber, $itemnumber);
1710 foreach my $itemnumber (@itemnumbers) {
1711 ModItemOrder($itemnumber, $parent_ordernumber);
1716 #------------------------------------------------------------#
1720 @results = &SearchOrders({
1721 ordernumber => $ordernumber,
1724 booksellerid => $booksellerid,
1725 basketno => $basketno,
1726 basketname => $basketname,
1727 basketgroupname => $basketgroupname,
1731 biblionumber => $biblionumber,
1732 budget_id => $budget_id
1735 Searches for orders filtered by criteria.
1737 C<$ordernumber> Finds matching orders or transferred orders by ordernumber.
1738 C<$search> Finds orders matching %$search% in title, author, or isbn.
1739 C<$owner> Finds order for the logged in user.
1740 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1741 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1744 C<@results> is an array of references-to-hash with the keys are fields
1745 from aqorders, biblio, biblioitems and aqbasket tables.
1750 my ( $params ) = @_;
1751 my $ordernumber = $params->{ordernumber};
1752 my $search = $params->{search};
1753 my $ean = $params->{ean};
1754 my $booksellerid = $params->{booksellerid};
1755 my $basketno = $params->{basketno};
1756 my $basketname = $params->{basketname};
1757 my $basketgroupname = $params->{basketgroupname};
1758 my $owner = $params->{owner};
1759 my $pending = $params->{pending};
1760 my $ordered = $params->{ordered};
1761 my $biblionumber = $params->{biblionumber};
1762 my $budget_id = $params->{budget_id};
1764 my $dbh = C4::Context->dbh;
1767 SELECT aqbasket.basketno,
1769 borrowers.firstname,
1772 biblioitems.biblioitemnumber,
1773 biblioitems.publishercode,
1774 biblioitems.publicationyear,
1775 aqbasket.authorisedby,
1776 aqbasket.booksellerid,
1778 aqbasket.creationdate,
1779 aqbasket.basketname,
1780 aqbasketgroups.id as basketgroupid,
1781 aqbasketgroups.name as basketgroupname,
1784 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1785 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1786 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1787 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1788 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1791 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1793 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1797 WHERE (datecancellationprinted is NULL)
1800 if ( $pending or $ordered ) {
1803 ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) )
1805 ( quantity > quantityreceived OR quantityreceived is NULL )
1809 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1817 my $userenv = C4::Context->userenv;
1818 if ( C4::Context->preference("IndependentBranches") ) {
1819 unless ( C4::Context->IsSuperLibrarian() ) {
1822 borrowers.branchcode = ?
1823 OR borrowers.branchcode = ''
1826 push @args, $userenv->{branch};
1830 if ( $ordernumber ) {
1831 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1832 push @args, ( $ordernumber, $ordernumber );
1834 if ( $biblionumber ) {
1835 $query .= 'AND aqorders.biblionumber = ?';
1836 push @args, $biblionumber;
1839 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1840 push @args, ("%$search%","%$search%","%$search%");
1843 $query .= ' AND biblioitems.ean = ?';
1846 if ( $booksellerid ) {
1847 $query .= 'AND aqbasket.booksellerid = ?';
1848 push @args, $booksellerid;
1851 $query .= 'AND aqbasket.basketno = ?';
1852 push @args, $basketno;
1855 $query .= 'AND aqbasket.basketname LIKE ?';
1856 push @args, "%$basketname%";
1858 if( $basketgroupname ) {
1859 $query .= ' AND aqbasketgroups.name LIKE ?';
1860 push @args, "%$basketgroupname%";
1864 $query .= ' AND aqbasket.authorisedby=? ';
1865 push @args, $userenv->{'number'};
1869 $query .= ' AND aqorders.budget_id = ?';
1870 push @args, $budget_id;
1873 $query .= ' ORDER BY aqbasket.basketno';
1875 my $sth = $dbh->prepare($query);
1876 $sth->execute(@args);
1877 return $sth->fetchall_arrayref({});
1880 #------------------------------------------------------------#
1884 &DelOrder($biblionumber, $ordernumber);
1886 Cancel the order with the given order and biblio numbers. It does not
1887 delete any entries in the aqorders table, it merely marks them as
1893 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 @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1916 foreach my $itemnumber (@itemnumbers){
1917 my $delcheck = C4::Items::DelItemCheck( $bibnum, $itemnumber );
1919 if($delcheck != 1) {
1920 $error->{'delitem'} = 1;
1924 if($delete_biblio) {
1925 # We get the number of remaining items
1926 my $biblio = Koha::Biblios->find( $bibnum );
1927 my $itemcount = $biblio->items->count;
1929 # If there are no items left,
1930 if ( $itemcount == 0 ) {
1931 # We delete the record
1932 my $delcheck = DelBiblio($bibnum);
1935 $error->{'delbiblio'} = 1;
1943 =head3 TransferOrder
1945 my $newordernumber = TransferOrder($ordernumber, $basketno);
1947 Transfer an order line to a basket.
1948 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1949 to BOOKSELLER on DATE' and create new order with internal note
1950 'Transferred from BOOKSELLER on DATE'.
1951 Move all attached items to the new order.
1952 Received orders cannot be transferred.
1953 Return the ordernumber of created order.
1958 my ($ordernumber, $basketno) = @_;
1960 return unless ($ordernumber and $basketno);
1962 my $order = Koha::Acquisition::Orders->find( $ordernumber ) or return;
1963 return if $order->datereceived;
1965 $order = $order->unblessed;
1967 my $basket = GetBasket($basketno);
1968 return unless $basket;
1970 my $dbh = C4::Context->dbh;
1971 my ($query, $sth, $rv);
1975 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1976 WHERE ordernumber = ?
1978 $sth = $dbh->prepare($query);
1979 $rv = $sth->execute('cancelled', $ordernumber);
1981 delete $order->{'ordernumber'};
1982 delete $order->{parent_ordernumber};
1983 $order->{'basketno'} = $basketno;
1985 my $newordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber;
1988 UPDATE aqorders_items
1990 WHERE ordernumber = ?
1992 $sth = $dbh->prepare($query);
1993 $sth->execute($newordernumber, $ordernumber);
1996 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1999 $sth = $dbh->prepare($query);
2000 $sth->execute($ordernumber, $newordernumber);
2002 return $newordernumber;
2005 =head2 FUNCTIONS ABOUT PARCELS
2009 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
2011 get a lists of parcels.
2018 is the bookseller this function has to get parcels.
2021 To know on what criteria the results list has to be ordered.
2024 is the booksellerinvoicenumber.
2026 =item $datefrom & $dateto
2027 to know on what date this function has to filter its search.
2032 a pointer on a hash list containing parcel informations as such :
2038 =item Last operation
2040 =item Number of biblio
2042 =item Number of items
2049 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
2050 my $dbh = C4::Context->dbh;
2051 my @query_params = ();
2053 SELECT aqinvoices.invoicenumber,
2054 datereceived,purchaseordernumber,
2055 count(DISTINCT biblionumber) AS biblio,
2056 sum(quantity) AS itemsexpected,
2057 sum(quantityreceived) AS itemsreceived
2058 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
2059 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2060 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
2062 push @query_params, $bookseller;
2064 if ( defined $code ) {
2065 $strsth .= ' and aqinvoices.invoicenumber like ? ';
2066 # add a % to the end of the code to allow stemming.
2067 push @query_params, "$code%";
2070 if ( defined $datefrom ) {
2071 $strsth .= ' and datereceived >= ? ';
2072 push @query_params, $datefrom;
2075 if ( defined $dateto ) {
2076 $strsth .= 'and datereceived <= ? ';
2077 push @query_params, $dateto;
2080 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
2082 # can't use a placeholder to place this column name.
2083 # but, we could probably be checking to make sure it is a column that will be fetched.
2084 $strsth .= "order by $order " if ($order);
2086 my $sth = $dbh->prepare($strsth);
2088 $sth->execute( @query_params );
2089 my $results = $sth->fetchall_arrayref({});
2093 #------------------------------------------------------------#
2095 =head3 GetLateOrders
2097 @results = &GetLateOrders;
2099 Searches for bookseller with late orders.
2102 the table of supplier with late issues. This table is full of hashref.
2108 my $supplierid = shift;
2110 my $estimateddeliverydatefrom = shift;
2111 my $estimateddeliverydateto = shift;
2113 my $dbh = C4::Context->dbh;
2115 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2116 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2118 my @query_params = ();
2120 SELECT aqbasket.basketno,
2121 aqorders.ordernumber,
2122 DATE(aqbasket.closedate) AS orderdate,
2123 aqbasket.basketname AS basketname,
2124 aqbasket.basketgroupid AS basketgroupid,
2125 aqbasketgroups.name AS basketgroupname,
2126 aqorders.rrp AS unitpricesupplier,
2127 aqorders.ecost AS unitpricelib,
2128 aqorders.claims_count AS claims_count,
2129 aqorders.claimed_date AS claimed_date,
2130 aqbudgets.budget_name AS budget,
2131 borrowers.branchcode AS branch,
2132 aqbooksellers.name AS supplier,
2133 aqbooksellers.id AS supplierid,
2134 biblio.author, biblio.title,
2135 biblioitems.publishercode AS publisher,
2136 biblioitems.publicationyear,
2137 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2141 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2142 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2143 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2144 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2145 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2146 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2147 WHERE aqorders.basketno = aqbasket.basketno
2148 AND ( datereceived = ''
2149 OR datereceived IS NULL
2150 OR aqorders.quantityreceived < aqorders.quantity
2152 AND aqbasket.closedate IS NOT NULL
2153 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2155 if ($dbdriver eq "mysql") {
2157 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2158 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2159 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2161 if ( defined $delay ) {
2162 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2163 push @query_params, $delay;
2165 $from .= " AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0";
2167 # FIXME: account for IFNULL as above
2169 aqorders.quantity AS quantity,
2170 aqorders.quantity * aqorders.rrp AS subtotal,
2171 (CAST(now() AS date) - closedate) AS latesince
2173 if ( defined $delay ) {
2174 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2175 push @query_params, $delay;
2177 $from .= " AND aqorders.quantity <> 0";
2179 if (defined $supplierid) {
2180 $from .= ' AND aqbasket.booksellerid = ? ';
2181 push @query_params, $supplierid;
2183 if (defined $branch) {
2184 $from .= ' AND borrowers.branchcode LIKE ? ';
2185 push @query_params, $branch;
2188 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2189 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2191 if ( defined $estimateddeliverydatefrom ) {
2192 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2193 push @query_params, $estimateddeliverydatefrom;
2195 if ( defined $estimateddeliverydateto ) {
2196 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2197 push @query_params, $estimateddeliverydateto;
2199 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2200 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2202 if (C4::Context->preference("IndependentBranches")
2203 && !C4::Context->IsSuperLibrarian() ) {
2204 $from .= ' AND borrowers.branchcode LIKE ? ';
2205 push @query_params, C4::Context->userenv->{branch};
2207 $from .= " AND orderstatus <> 'cancelled' ";
2208 my $query = "$select $from \nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2209 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2210 my $sth = $dbh->prepare($query);
2211 $sth->execute(@query_params);
2213 while (my $data = $sth->fetchrow_hashref) {
2214 push @results, $data;
2219 #------------------------------------------------------------#
2223 \@order_loop = GetHistory( %params );
2225 Retreives some acquisition history information
2235 basket - search both basket name and number
2236 booksellerinvoicenumber
2239 orderstatus (note that orderstatus '' will retrieve orders
2240 of any status except cancelled)
2242 get_canceled_order (if set to a true value, cancelled orders will
2246 $order_loop is a list of hashrefs that each look like this:
2248 'author' => 'Twain, Mark',
2250 'biblionumber' => '215',
2252 'creationdate' => 'MM/DD/YYYY',
2253 'datereceived' => undef,
2256 'invoicenumber' => undef,
2258 'ordernumber' => '1',
2260 'quantityreceived' => undef,
2261 'title' => 'The Adventures of Huckleberry Finn'
2267 # don't run the query if there are no parameters (list would be too long for sure !)
2268 croak "No search params" unless @_;
2270 my $title = $params{title};
2271 my $author = $params{author};
2272 my $isbn = $params{isbn};
2273 my $ean = $params{ean};
2274 my $name = $params{name};
2275 my $from_placed_on = $params{from_placed_on};
2276 my $to_placed_on = $params{to_placed_on};
2277 my $basket = $params{basket};
2278 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2279 my $basketgroupname = $params{basketgroupname};
2280 my $budget = $params{budget};
2281 my $orderstatus = $params{orderstatus};
2282 my $biblionumber = $params{biblionumber};
2283 my $get_canceled_order = $params{get_canceled_order} || 0;
2284 my $ordernumber = $params{ordernumber};
2285 my $search_children_too = $params{search_children_too} || 0;
2286 my $created_by = $params{created_by} || [];
2290 my $total_qtyreceived = 0;
2291 my $total_price = 0;
2293 #get variation of isbn
2297 if ( C4::Context->preference("SearchWithISBNVariations") ){
2298 @isbns = C4::Koha::GetVariationsOfISBN( $isbn );
2299 foreach my $isb (@isbns){
2300 push @isbn_params, '?';
2305 push @isbn_params, '?';
2309 my $dbh = C4::Context->dbh;
2312 COALESCE(biblio.title, deletedbiblio.title) AS title,
2313 COALESCE(biblio.author, deletedbiblio.author) AS author,
2314 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2315 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2317 aqbasket.basketname,
2318 aqbasket.basketgroupid,
2319 aqbasket.authorisedby,
2320 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2321 aqbasketgroups.name as groupname,
2323 aqbasket.creationdate,
2324 aqorders.datereceived,
2326 aqorders.quantityreceived,
2328 aqorders.ordernumber,
2330 aqinvoices.invoicenumber,
2331 aqbooksellers.id as id,
2332 aqorders.biblionumber,
2333 aqorders.orderstatus,
2334 aqorders.parent_ordernumber,
2335 aqbudgets.budget_name
2337 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2340 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2341 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2342 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2343 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2344 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2345 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2346 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2347 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2348 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2349 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2352 $query .= " WHERE 1 ";
2354 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2355 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2358 my @query_params = ();
2360 if ( $biblionumber ) {
2361 $query .= " AND biblio.biblionumber = ?";
2362 push @query_params, $biblionumber;
2366 $query .= " AND biblio.title LIKE ? ";
2367 $title =~ s/\s+/%/g;
2368 push @query_params, "%$title%";
2372 $query .= " AND biblio.author LIKE ? ";
2373 push @query_params, "%$author%";
2377 $query .= " AND ( biblioitems.isbn LIKE " . join (" OR biblioitems.isbn LIKE ", @isbn_params ) . ")";
2378 foreach my $isb (@isbns){
2379 push @query_params, "%$isb%";
2384 $query .= " AND biblioitems.ean = ? ";
2385 push @query_params, "$ean";
2388 $query .= " AND aqbooksellers.name LIKE ? ";
2389 push @query_params, "%$name%";
2393 $query .= " AND aqbudgets.budget_id = ? ";
2394 push @query_params, "$budget";
2397 if ( $from_placed_on ) {
2398 $query .= " AND creationdate >= ? ";
2399 push @query_params, $from_placed_on;
2402 if ( $to_placed_on ) {
2403 $query .= " AND creationdate <= ? ";
2404 push @query_params, $to_placed_on;
2407 if ( defined $orderstatus and $orderstatus ne '') {
2408 $query .= " AND aqorders.orderstatus = ? ";
2409 push @query_params, "$orderstatus";
2413 if ($basket =~ m/^\d+$/) {
2414 $query .= " AND aqorders.basketno = ? ";
2415 push @query_params, $basket;
2417 $query .= " AND aqbasket.basketname LIKE ? ";
2418 push @query_params, "%$basket%";
2422 if ($booksellerinvoicenumber) {
2423 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2424 push @query_params, "%$booksellerinvoicenumber%";
2427 if ($basketgroupname) {
2428 $query .= " AND aqbasketgroups.name LIKE ? ";
2429 push @query_params, "%$basketgroupname%";
2433 $query .= " AND (aqorders.ordernumber = ? ";
2434 push @query_params, $ordernumber;
2435 if ($search_children_too) {
2436 $query .= " OR aqorders.parent_ordernumber = ? ";
2437 push @query_params, $ordernumber;
2442 if ( @$created_by ) {
2443 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2444 push @query_params, @$created_by;
2448 if ( C4::Context->preference("IndependentBranches") ) {
2449 unless ( C4::Context->IsSuperLibrarian() ) {
2450 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2451 push @query_params, C4::Context->userenv->{branch};
2454 $query .= " ORDER BY id";
2456 return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2459 =head2 GetRecentAcqui
2461 $results = GetRecentAcqui($days);
2463 C<$results> is a ref to a table which contains hashref
2467 sub GetRecentAcqui {
2469 my $dbh = C4::Context->dbh;
2473 ORDER BY timestamp DESC
2476 my $sth = $dbh->prepare($query);
2478 my $results = $sth->fetchall_arrayref({});
2482 #------------------------------------------------------------#
2486 &AddClaim($ordernumber);
2488 Add a claim for an order
2493 my ($ordernumber) = @_;
2494 my $dbh = C4::Context->dbh;
2497 claims_count = claims_count + 1,
2498 claimed_date = CURDATE()
2499 WHERE ordernumber = ?
2501 my $sth = $dbh->prepare($query);
2502 $sth->execute($ordernumber);
2507 my @invoices = GetInvoices(
2508 invoicenumber => $invoicenumber,
2509 supplierid => $supplierid,
2510 suppliername => $suppliername,
2511 shipmentdatefrom => $shipmentdatefrom, # ISO format
2512 shipmentdateto => $shipmentdateto, # ISO format
2513 billingdatefrom => $billingdatefrom, # ISO format
2514 billingdateto => $billingdateto, # ISO format
2515 isbneanissn => $isbn_or_ean_or_issn,
2518 publisher => $publisher,
2519 publicationyear => $publicationyear,
2520 branchcode => $branchcode,
2521 order_by => $order_by
2524 Return a list of invoices that match all given criteria.
2526 $order_by is "column_name (asc|desc)", where column_name is any of
2527 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2528 'shipmentcost', 'shipmentcost_budgetid'.
2530 asc is the default if omitted
2537 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2538 closedate shipmentcost shipmentcost_budgetid);
2540 my $dbh = C4::Context->dbh;
2542 SELECT aqinvoices.invoiceid, aqinvoices.invoicenumber, aqinvoices.booksellerid, aqinvoices.shipmentdate, aqinvoices.billingdate, aqinvoices.closedate, aqinvoices.shipmentcost, aqinvoices.shipmentcost_budgetid, aqinvoices.message_id,
2543 aqbooksellers.name AS suppliername,
2546 aqorders.datereceived IS NOT NULL,
2547 aqorders.biblionumber,
2550 ) AS receivedbiblios,
2553 aqorders.subscriptionid IS NOT NULL,
2554 aqorders.subscriptionid,
2557 ) AS is_linked_to_subscriptions,
2558 SUM(aqorders.quantityreceived) AS receiveditems
2560 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2561 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2562 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2563 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2564 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2565 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2566 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2571 if($args{supplierid}) {
2572 push @bind_strs, " aqinvoices.booksellerid = ? ";
2573 push @bind_args, $args{supplierid};
2575 if($args{invoicenumber}) {
2576 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2577 push @bind_args, "%$args{invoicenumber}%";
2579 if($args{suppliername}) {
2580 push @bind_strs, " aqbooksellers.name LIKE ? ";
2581 push @bind_args, "%$args{suppliername}%";
2583 if($args{shipmentdatefrom}) {
2584 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2585 push @bind_args, $args{shipmentdatefrom};
2587 if($args{shipmentdateto}) {
2588 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2589 push @bind_args, $args{shipmentdateto};
2591 if($args{billingdatefrom}) {
2592 push @bind_strs, " aqinvoices.billingdate >= ? ";
2593 push @bind_args, $args{billingdatefrom};
2595 if($args{billingdateto}) {
2596 push @bind_strs, " aqinvoices.billingdate <= ? ";
2597 push @bind_args, $args{billingdateto};
2599 if($args{isbneanissn}) {
2600 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2601 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2604 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2605 push @bind_args, $args{title};
2608 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2609 push @bind_args, $args{author};
2611 if($args{publisher}) {
2612 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2613 push @bind_args, $args{publisher};
2615 if($args{publicationyear}) {
2616 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2617 push @bind_args, $args{publicationyear}, $args{publicationyear};
2619 if($args{branchcode}) {
2620 push @bind_strs, " borrowers.branchcode = ? ";
2621 push @bind_args, $args{branchcode};
2623 if($args{message_id}) {
2624 push @bind_strs, " aqinvoices.message_id = ? ";
2625 push @bind_args, $args{message_id};
2628 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2629 $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";
2631 if($args{order_by}) {
2632 my ($column, $direction) = split / /, $args{order_by};
2633 if(grep /^$column$/, @columns) {
2634 $direction ||= 'ASC';
2635 $query .= " ORDER BY $column $direction";
2639 my $sth = $dbh->prepare($query);
2640 $sth->execute(@bind_args);
2642 my $results = $sth->fetchall_arrayref({});
2648 my $invoice = GetInvoice($invoiceid);
2650 Get informations about invoice with given $invoiceid
2652 Return a hash filled with aqinvoices.* fields
2657 my ($invoiceid) = @_;
2660 return unless $invoiceid;
2662 my $dbh = C4::Context->dbh;
2668 my $sth = $dbh->prepare($query);
2669 $sth->execute($invoiceid);
2671 $invoice = $sth->fetchrow_hashref;
2675 =head3 GetInvoiceDetails
2677 my $invoice = GetInvoiceDetails($invoiceid)
2679 Return informations about an invoice + the list of related order lines
2681 Orders informations are in $invoice->{orders} (array ref)
2685 sub GetInvoiceDetails {
2686 my ($invoiceid) = @_;
2688 if ( !defined $invoiceid ) {
2689 carp 'GetInvoiceDetails called without an invoiceid';
2693 my $dbh = C4::Context->dbh;
2695 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2697 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2700 my $sth = $dbh->prepare($query);
2701 $sth->execute($invoiceid);
2703 my $invoice = $sth->fetchrow_hashref;
2708 biblio.copyrightdate,
2710 biblioitems.publishercode,
2711 biblioitems.publicationyear,
2712 aqbasket.basketname,
2713 aqbasketgroups.id AS basketgroupid,
2714 aqbasketgroups.name AS basketgroupname
2716 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2717 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2718 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2719 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2722 $sth = $dbh->prepare($query);
2723 $sth->execute($invoiceid);
2724 $invoice->{orders} = $sth->fetchall_arrayref({});
2725 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2732 my $invoiceid = AddInvoice(
2733 invoicenumber => $invoicenumber,
2734 booksellerid => $booksellerid,
2735 shipmentdate => $shipmentdate,
2736 billingdate => $billingdate,
2737 closedate => $closedate,
2738 shipmentcost => $shipmentcost,
2739 shipmentcost_budgetid => $shipmentcost_budgetid
2742 Create a new invoice and return its id or undef if it fails.
2749 return unless(%invoice and $invoice{invoicenumber});
2751 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2752 closedate shipmentcost shipmentcost_budgetid message_id);
2756 foreach my $key (keys %invoice) {
2757 if(0 < grep(/^$key$/, @columns)) {
2758 push @set_strs, "$key = ?";
2759 push @set_args, ($invoice{$key} || undef);
2765 my $dbh = C4::Context->dbh;
2766 my $query = "INSERT INTO aqinvoices SET ";
2767 $query .= join (",", @set_strs);
2768 my $sth = $dbh->prepare($query);
2769 $rv = $sth->execute(@set_args);
2771 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2780 invoiceid => $invoiceid, # Mandatory
2781 invoicenumber => $invoicenumber,
2782 booksellerid => $booksellerid,
2783 shipmentdate => $shipmentdate,
2784 billingdate => $billingdate,
2785 closedate => $closedate,
2786 shipmentcost => $shipmentcost,
2787 shipmentcost_budgetid => $shipmentcost_budgetid
2790 Modify an invoice, invoiceid is mandatory.
2792 Return undef if it fails.
2799 return unless(%invoice and $invoice{invoiceid});
2801 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2802 closedate shipmentcost shipmentcost_budgetid);
2806 foreach my $key (keys %invoice) {
2807 if(0 < grep(/^$key$/, @columns)) {
2808 push @set_strs, "$key = ?";
2809 push @set_args, ($invoice{$key} || undef);
2813 my $dbh = C4::Context->dbh;
2814 my $query = "UPDATE aqinvoices SET ";
2815 $query .= join(",", @set_strs);
2816 $query .= " WHERE invoiceid = ?";
2818 my $sth = $dbh->prepare($query);
2819 $sth->execute(@set_args, $invoice{invoiceid});
2824 CloseInvoice($invoiceid);
2828 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2833 my ($invoiceid) = @_;
2835 return unless $invoiceid;
2837 my $dbh = C4::Context->dbh;
2840 SET closedate = CAST(NOW() AS DATE)
2843 my $sth = $dbh->prepare($query);
2844 $sth->execute($invoiceid);
2847 =head3 ReopenInvoice
2849 ReopenInvoice($invoiceid);
2853 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => output_pref({ dt=>dt_from_string, dateonly=>1, otputpref=>'iso' }))
2858 my ($invoiceid) = @_;
2860 return unless $invoiceid;
2862 my $dbh = C4::Context->dbh;
2865 SET closedate = NULL
2868 my $sth = $dbh->prepare($query);
2869 $sth->execute($invoiceid);
2874 DelInvoice($invoiceid);
2876 Delete an invoice if there are no items attached to it.
2881 my ($invoiceid) = @_;
2883 return unless $invoiceid;
2885 my $dbh = C4::Context->dbh;
2891 my $sth = $dbh->prepare($query);
2892 $sth->execute($invoiceid);
2893 my $res = $sth->fetchrow_arrayref;
2894 if ( $res && $res->[0] == 0 ) {
2896 DELETE FROM aqinvoices
2899 my $sth = $dbh->prepare($query);
2900 return ( $sth->execute($invoiceid) > 0 );
2905 =head3 MergeInvoices
2907 MergeInvoices($invoiceid, \@sourceids);
2909 Merge the invoices identified by the IDs in \@sourceids into
2910 the invoice identified by $invoiceid.
2915 my ($invoiceid, $sourceids) = @_;
2917 return unless $invoiceid;
2918 foreach my $sourceid (@$sourceids) {
2919 next if $sourceid == $invoiceid;
2920 my $source = GetInvoiceDetails($sourceid);
2921 foreach my $order (@{$source->{'orders'}}) {
2922 $order->{'invoiceid'} = $invoiceid;
2925 DelInvoice($source->{'invoiceid'});
2930 =head3 GetBiblioCountByBasketno
2932 $biblio_count = &GetBiblioCountByBasketno($basketno);
2934 Looks up the biblio's count that has basketno value $basketno
2940 sub GetBiblioCountByBasketno {
2941 my ($basketno) = @_;
2942 my $dbh = C4::Context->dbh;
2944 SELECT COUNT( DISTINCT( biblionumber ) )
2947 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2950 my $sth = $dbh->prepare($query);
2951 $sth->execute($basketno);
2952 return $sth->fetchrow;
2955 # Note this subroutine should be moved to Koha::Acquisition::Order
2956 # Will do when a DBIC decision will be taken.
2957 sub populate_order_with_prices {
2960 my $order = $params->{order};
2961 my $booksellerid = $params->{booksellerid};
2962 return unless $booksellerid;
2964 my $bookseller = Koha::Acquisition::Booksellers->find( $booksellerid );
2966 my $receiving = $params->{receiving};
2967 my $ordering = $params->{ordering};
2968 my $discount = $order->{discount};
2969 $discount /= 100 if $discount > 1;
2972 $order->{tax_rate_on_ordering} //= $order->{tax_rate};
2973 if ( $bookseller->listincgst ) {
2974 # The user entered the rrp tax included
2975 $order->{rrp_tax_included} = $order->{rrp};
2977 # rrp tax excluded = rrp tax included / ( 1 + tax rate )
2978 $order->{rrp_tax_excluded} = $order->{rrp_tax_included} / ( 1 + $order->{tax_rate_on_ordering} );
2980 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
2981 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
2983 # ecost tax included = rrp tax included ( 1 - discount )
2984 $order->{ecost_tax_included} = $order->{rrp_tax_included} * ( 1 - $discount );
2987 # The user entered the rrp tax excluded
2988 $order->{rrp_tax_excluded} = $order->{rrp};
2990 # rrp tax included = rrp tax excluded * ( 1 - tax rate )
2991 $order->{rrp_tax_included} = $order->{rrp_tax_excluded} * ( 1 + $order->{tax_rate_on_ordering} );
2993 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
2994 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
2996 # ecost tax included = rrp tax excluded * ( 1 + tax rate ) * ( 1 - discount )
2997 $order->{ecost_tax_included} =
2998 $order->{rrp_tax_excluded} *
2999 ( 1 + $order->{tax_rate_on_ordering} ) *
3003 # tax value = quantity * ecost tax excluded * tax rate
3004 $order->{tax_value_on_ordering} =
3005 $order->{quantity} * $order->{ecost_tax_excluded} * $order->{tax_rate_on_ordering};
3009 $order->{tax_rate_on_receiving} //= $order->{tax_rate};
3010 if ( $bookseller->invoiceincgst ) {
3011 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
3012 # we need to keep the exact ecost value
3013 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_included} )->round ) {
3014 $order->{unitprice} = $order->{ecost_tax_included};
3017 # The user entered the unit price tax included
3018 $order->{unitprice_tax_included} = $order->{unitprice};
3020 # unit price tax excluded = unit price tax included / ( 1 + tax rate )
3021 $order->{unitprice_tax_excluded} = $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate_on_receiving} );
3024 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
3025 # we need to keep the exact ecost value
3026 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_excluded} )->round ) {
3027 $order->{unitprice} = $order->{ecost_tax_excluded};
3030 # The user entered the unit price tax excluded
3031 $order->{unitprice_tax_excluded} = $order->{unitprice};
3034 # unit price tax included = unit price tax included * ( 1 + tax rate )
3035 $order->{unitprice_tax_included} = $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate_on_receiving} );
3038 # tax value = quantity * unit price tax excluded * tax rate
3039 $order->{tax_value_on_receiving} = $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate_on_receiving};
3045 =head3 GetOrderUsers
3047 $order_users_ids = &GetOrderUsers($ordernumber);
3049 Returns a list of all borrowernumbers that are in order users list
3054 my ($ordernumber) = @_;
3056 return unless $ordernumber;
3059 SELECT borrowernumber
3061 WHERE ordernumber = ?
3063 my $dbh = C4::Context->dbh;
3064 my $sth = $dbh->prepare($query);
3065 $sth->execute($ordernumber);
3066 my $results = $sth->fetchall_arrayref( {} );
3068 my @borrowernumbers;
3069 foreach (@$results) {
3070 push @borrowernumbers, $_->{'borrowernumber'};
3073 return @borrowernumbers;
3076 =head3 ModOrderUsers
3078 my @order_users_ids = (1, 2, 3);
3079 &ModOrderUsers($ordernumber, @basketusers_ids);
3081 Delete all users from order users list, and add users in C<@order_users_ids>
3087 my ( $ordernumber, @order_users_ids ) = @_;
3089 return unless $ordernumber;
3091 my $dbh = C4::Context->dbh;
3093 DELETE FROM aqorder_users
3094 WHERE ordernumber = ?
3096 my $sth = $dbh->prepare($query);
3097 $sth->execute($ordernumber);
3100 INSERT INTO aqorder_users (ordernumber, borrowernumber)
3103 $sth = $dbh->prepare($query);
3104 foreach my $order_user_id (@order_users_ids) {
3105 $sth->execute( $ordernumber, $order_user_id );
3109 sub NotifyOrderUsers {
3110 my ($ordernumber) = @_;
3112 my @borrowernumbers = GetOrderUsers($ordernumber);
3113 return unless @borrowernumbers;
3115 my $order = GetOrder( $ordernumber );
3116 for my $borrowernumber (@borrowernumbers) {
3117 my $patron = Koha::Patrons->find( $borrowernumber );
3118 my $library = $patron->library->unblessed;
3119 my $biblio = Koha::Biblios->find( $order->{biblionumber} )->unblessed;
3120 my $letter = C4::Letters::GetPreparedLetter(
3121 module => 'acquisition',
3122 letter_code => 'ACQ_NOTIF_ON_RECEIV',
3123 branchcode => $library->{branchcode},
3124 lang => $patron->lang,
3126 'branches' => $library,
3127 'borrowers' => $patron->unblessed,
3128 'biblio' => $biblio,
3129 'aqorders' => $order,
3133 C4::Letters::EnqueueLetter(
3136 borrowernumber => $borrowernumber,
3137 LibraryName => C4::Context->preference("LibraryName"),
3138 message_transport_type => 'email',
3140 ) or warn "can't enqueue letter $letter";
3145 =head3 FillWithDefaultValues
3147 FillWithDefaultValues( $marc_record );
3149 This will update the record with default value defined in the ACQ framework.
3150 For all existing fields, if a default value exists and there are no subfield, it will be created.
3151 If the field does not exist, it will be created too.
3155 sub FillWithDefaultValues {
3157 my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ', { unsafe => 1 } );
3160 C4::Biblio::GetMarcFromKohaField( 'items.itemnumber', '' );
3161 for my $tag ( sort keys %$tagslib ) {
3163 next if $tag == $itemfield;
3164 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
3165 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
3166 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
3167 if ( defined $defaultvalue and $defaultvalue ne '' ) {
3168 my @fields = $record->field($tag);
3170 for my $field (@fields) {
3171 unless ( defined $field->subfield($subfield) ) {
3172 $field->add_subfields(
3173 $subfield => $defaultvalue );
3178 $record->insert_fields_ordered(
3180 $tag, '', '', $subfield => $defaultvalue
3195 Koha Development Team <http://koha-community.org/>