1 package C4::Acquisition;
3 # Copyright 2000-2002 Katipo Communications
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it
8 # under the terms of the GNU General Public License as published by
9 # the Free Software Foundation; either version 3 of the License, or
10 # (at your option) any later version.
12 # Koha is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
30 use C4::Templates qw(gettemplate);
31 use Koha::DateUtils qw( dt_from_string output_pref );
32 use Koha::Acquisition::Baskets;
33 use Koha::Acquisition::Booksellers;
34 use Koha::Acquisition::Orders;
38 use Koha::Number::Price;
40 use Koha::CsvProfiles;
50 use vars qw(@ISA @EXPORT);
56 &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
57 &GetBasketAsCSV &GetBasketGroupAsCSV
58 &GetBasketsByBookseller &GetBasketsByBasketgroup
59 &GetBasketsInfosByBookseller
61 &GetBasketUsers &ModBasketUsers
66 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
67 &GetBasketgroups &ReOpenBasketgroup
69 &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
70 &GetLateOrders &GetOrderFromItemnumber
71 &SearchOrders &GetHistory &GetRecentAcqui
72 &ModReceiveOrder &CancelReceipt
89 &GetBiblioCountByBasketno
95 &FillWithDefaultValues
106 sub GetOrderFromItemnumber {
107 my ($itemnumber) = @_;
108 my $dbh = C4::Context->dbh;
111 SELECT * from aqorders LEFT JOIN aqorders_items
112 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
113 WHERE itemnumber = ? |;
115 my $sth = $dbh->prepare($query);
119 $sth->execute($itemnumber);
121 my $order = $sth->fetchrow_hashref;
128 C4::Acquisition - Koha functions for dealing with orders and acquisitions
136 The functions in this module deal with acquisitions, managing book
137 orders, basket and parcels.
141 =head2 FUNCTIONS ABOUT BASKETS
145 $aqbasket = &GetBasket($basketnumber);
147 get all basket informations in aqbasket for a given basket
149 B<returns:> informations for a given basket returned as a hashref.
155 my $dbh = C4::Context->dbh;
158 concat( b.firstname,' ',b.surname) AS authorisedbyname
160 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
163 my $sth=$dbh->prepare($query);
164 $sth->execute($basketno);
165 my $basket = $sth->fetchrow_hashref;
169 #------------------------------------------------------------#
173 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
174 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace, $is_standing, $create_items );
176 Create a new basket in aqbasket table
180 =item C<$booksellerid> is a foreign key in the aqbasket table
182 =item C<$authorizedby> is the username of who created the basket
186 The other parameters are optional, see ModBasketHeader for more info on them.
191 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
192 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
193 $billingplace, $is_standing, $create_items ) = @_;
194 my $dbh = C4::Context->dbh;
196 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
197 . 'VALUES (now(),?,?)';
198 $dbh->do( $query, {}, $booksellerid, $authorisedby );
200 my $basket = $dbh->{mysql_insertid};
201 $basketname ||= q{}; # default to empty strings
203 $basketbooksellernote ||= q{};
204 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
205 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items );
209 #------------------------------------------------------------#
213 &CloseBasket($basketno);
215 close a basket (becomes unmodifiable, except for receives)
221 my $dbh = C4::Context->dbh;
222 $dbh->do('UPDATE aqbasket SET closedate=now() WHERE basketno=?', {}, $basketno );
225 q{UPDATE aqorders SET orderstatus = 'ordered' WHERE basketno = ? AND orderstatus NOT IN ( 'complete', 'cancelled')},
233 &ReopenBasket($basketno);
241 my $dbh = C4::Context->dbh;
242 $dbh->do( q{UPDATE aqbasket SET closedate=NULL WHERE basketno=?}, {}, $basketno );
246 SET orderstatus = 'new'
248 AND orderstatus NOT IN ( 'complete', 'cancelled' )
253 #------------------------------------------------------------#
255 =head3 GetBasketAsCSV
257 &GetBasketAsCSV($basketno);
259 Export a basket as CSV
261 $cgi parameter is needed for column name translation
266 my ($basketno, $cgi, $csv_profile_id) = @_;
267 my $basket = GetBasket($basketno);
268 my @orders = GetOrders($basketno);
269 my $contract = GetContract({
270 contractnumber => $basket->{'contractnumber'}
273 my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
275 if ($csv_profile_id) {
276 my $csv_profile = Koha::CsvProfiles->find( $csv_profile_id );
277 Koha::Exceptions::ObjectNotFound->throw( 'There is no valid csv profile given') unless $csv_profile;
279 my $csv = Text::CSV_XS->new({'quote_char'=>'"','escape_char'=>'"','sep_char'=>$csv_profile->csv_separator,'binary'=>1});
280 my $csv_profile_content = $csv_profile->content;
281 my ( @headers, @fields );
282 while ( $csv_profile_content =~ /
285 ([^\|]*) # fieldname (table.row or row)
289 my $field = ($2 eq '') ? $1 : $2;
291 $header =~ s/^\s+|\s+$//g; # Trim whitespaces
292 push @headers, $header;
294 $field =~ s/[^\.]*\.{1}//; # Remove the table name if exists.
295 $field =~ s/^\s+|\s+$//g; # Trim whitespaces
296 push @fields, $field;
298 for my $order (@orders) {
300 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
301 my $biblioitem = $biblio->biblioitem;
302 $order = { %$order, %{ $biblioitem->unblessed } };
304 $order = {%$order, %$contract};
306 $order = {%$order, %$basket, %{ $biblio->unblessed }};
307 for my $field (@fields) {
308 push @row, $order->{$field};
312 my $content = join( $csv_profile->csv_separator, @headers ) . "\n";
313 for my $row ( @rows ) {
314 $csv->combine(@$row);
315 my $string = $csv->string;
316 $content .= $string . "\n";
321 foreach my $order (@orders) {
322 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
323 my $biblioitem = $biblio->biblioitem;
325 contractname => $contract->{'contractname'},
326 ordernumber => $order->{'ordernumber'},
327 entrydate => $order->{'entrydate'},
328 isbn => $order->{'isbn'},
329 author => $biblio->author,
330 title => $biblio->title,
331 publicationyear => $biblioitem->publicationyear,
332 publishercode => $biblioitem->publishercode,
333 collectiontitle => $biblioitem->collectiontitle,
334 notes => $order->{'order_vendornote'},
335 quantity => $order->{'quantity'},
336 rrp => $order->{'rrp'},
338 for my $place ( qw( deliveryplace billingplace ) ) {
339 if ( my $library = Koha::Libraries->find( $row->{deliveryplace} ) ) {
340 $row->{$place} = $library->branchname
344 contractname author title publishercode collectiontitle notes
345 deliveryplace billingplace
347 # Double the quotes to not be interpreted as a field end
348 $row->{$_} =~ s/"/""/g if $row->{$_};
354 if(defined $a->{publishercode} and defined $b->{publishercode}) {
355 $a->{publishercode} cmp $b->{publishercode};
359 $template->param(rows => \@rows);
361 return $template->output;
366 =head3 GetBasketGroupAsCSV
368 &GetBasketGroupAsCSV($basketgroupid);
370 Export a basket group as CSV
372 $cgi parameter is needed for column name translation
376 sub GetBasketGroupAsCSV {
377 my ($basketgroupid, $cgi) = @_;
378 my $baskets = GetBasketsByBasketgroup($basketgroupid);
380 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
383 for my $basket (@$baskets) {
384 my @orders = GetOrders( $basket->{basketno} );
385 my $contract = GetContract({
386 contractnumber => $basket->{contractnumber}
388 my $bookseller = Koha::Acquisition::Booksellers->find( $basket->{booksellerid} );
389 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
391 foreach my $order (@orders) {
392 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
393 my $biblioitem = $biblio->biblioitem;
395 clientnumber => $bookseller->accountnumber,
396 basketname => $basket->{basketname},
397 ordernumber => $order->{ordernumber},
398 author => $biblio->author,
399 title => $biblio->title,
400 publishercode => $biblioitem->publishercode,
401 publicationyear => $biblioitem->publicationyear,
402 collectiontitle => $biblioitem->collectiontitle,
403 isbn => $order->{isbn},
404 quantity => $order->{quantity},
405 rrp_tax_included => $order->{rrp_tax_included},
406 rrp_tax_excluded => $order->{rrp_tax_excluded},
407 discount => $bookseller->discount,
408 ecost_tax_included => $order->{ecost_tax_included},
409 ecost_tax_excluded => $order->{ecost_tax_excluded},
410 notes => $order->{order_vendornote},
411 entrydate => $order->{entrydate},
412 booksellername => $bookseller->name,
413 bookselleraddress => $bookseller->address1,
414 booksellerpostal => $bookseller->postal,
415 contractnumber => $contract->{contractnumber},
416 contractname => $contract->{contractname},
419 basketgroupdeliveryplace => $basketgroup->{deliveryplace},
420 basketgroupbillingplace => $basketgroup->{billingplace},
421 basketdeliveryplace => $basket->{deliveryplace},
422 basketbillingplace => $basket->{billingplace},
424 for my $place (qw( basketgroupdeliveryplace basketgroupbillingplace basketdeliveryplace basketbillingplace )) {
425 if ( my $library = Koha::Libraries->find( $temp->{$place} ) ) {
426 $row->{$place} = $library->branchname;
430 basketname author title publishercode collectiontitle notes
431 booksellername bookselleraddress booksellerpostal contractname
432 basketgroupdeliveryplace basketgroupbillingplace
433 basketdeliveryplace basketbillingplace
435 # Double the quotes to not be interpreted as a field end
436 $row->{$_} =~ s/"/""/g if $row->{$_};
441 $template->param(rows => \@rows);
443 return $template->output;
447 =head3 CloseBasketgroup
449 &CloseBasketgroup($basketgroupno);
455 sub CloseBasketgroup {
456 my ($basketgroupno) = @_;
457 my $dbh = C4::Context->dbh;
458 my $sth = $dbh->prepare("
459 UPDATE aqbasketgroups
463 $sth->execute($basketgroupno);
466 #------------------------------------------------------------#
468 =head3 ReOpenBaskergroup($basketgroupno)
470 &ReOpenBaskergroup($basketgroupno);
476 sub ReOpenBasketgroup {
477 my ($basketgroupno) = @_;
478 my $dbh = C4::Context->dbh;
479 my $sth = $dbh->prepare("
480 UPDATE aqbasketgroups
484 $sth->execute($basketgroupno);
487 #------------------------------------------------------------#
492 &DelBasket($basketno);
494 Deletes the basket that has basketno field $basketno in the aqbasket table.
498 =item C<$basketno> is the primary key of the basket in the aqbasket table.
505 my ( $basketno ) = @_;
506 my $query = "DELETE FROM aqbasket WHERE basketno=?";
507 my $dbh = C4::Context->dbh;
508 my $sth = $dbh->prepare($query);
509 $sth->execute($basketno);
513 #------------------------------------------------------------#
517 &ModBasket($basketinfo);
519 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
523 =item C<$basketno> is the primary key of the basket in the aqbasket table.
530 my $basketinfo = shift;
531 my $query = "UPDATE aqbasket SET ";
533 foreach my $key (keys %$basketinfo){
534 if ($key ne 'basketno'){
535 $query .= "$key=?, ";
536 push(@params, $basketinfo->{$key} || undef );
539 # get rid of the "," at the end of $query
540 if (substr($query, length($query)-2) eq ', '){
545 $query .= "WHERE basketno=?";
546 push(@params, $basketinfo->{'basketno'});
547 my $dbh = C4::Context->dbh;
548 my $sth = $dbh->prepare($query);
549 $sth->execute(@params);
554 #------------------------------------------------------------#
556 =head3 ModBasketHeader
558 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
560 Modifies a basket's header.
564 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
566 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
568 =item C<$note> is the "note" field in the "aqbasket" table;
570 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
572 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
574 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
576 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
578 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
580 =item C<$is_standing> is the "is_standing" field in the aqbasket table.
582 =item C<$create_items> should be set to 'ordering', 'receiving' or 'cataloguing' (or undef, in which
583 case the AcqCreateItem syspref takes precedence).
589 sub ModBasketHeader {
590 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items) = @_;
595 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?, is_standing=?, create_items=?
599 my $dbh = C4::Context->dbh;
600 my $sth = $dbh->prepare($query);
601 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items || undef, $basketno);
603 if ( $contractnumber ) {
604 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
605 my $sth2 = $dbh->prepare($query2);
606 $sth2->execute($contractnumber,$basketno);
611 #------------------------------------------------------------#
613 =head3 GetBasketsByBookseller
615 @results = &GetBasketsByBookseller($booksellerid, $extra);
617 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
621 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
623 =item C<$extra> is the extra sql parameters, can be
625 $extra->{groupby}: group baskets by column
626 ex. $extra->{groupby} = aqbasket.basketgroupid
627 $extra->{orderby}: order baskets by column
628 $extra->{limit}: limit number of results (can be helpful for pagination)
634 sub GetBasketsByBookseller {
635 my ($booksellerid, $extra) = @_;
636 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
638 if ($extra->{groupby}) {
639 $query .= " GROUP by $extra->{groupby}";
641 if ($extra->{orderby}){
642 $query .= " ORDER by $extra->{orderby}";
644 if ($extra->{limit}){
645 $query .= " LIMIT $extra->{limit}";
648 my $dbh = C4::Context->dbh;
649 my $sth = $dbh->prepare($query);
650 $sth->execute($booksellerid);
651 return $sth->fetchall_arrayref({});
654 =head3 GetBasketsInfosByBookseller
656 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
658 The optional second parameter allbaskets is a boolean allowing you to
659 select all baskets from the supplier; by default only active baskets (open or
660 closed but still something to receive) are returned.
662 Returns in a arrayref of hashref all about booksellers baskets, plus:
663 total_biblios: Number of distinct biblios in basket
664 total_items: Number of items in basket
665 expected_items: Number of non-received items in basket
669 sub GetBasketsInfosByBookseller {
670 my ($supplierid, $allbaskets) = @_;
672 return unless $supplierid;
674 my $dbh = C4::Context->dbh;
676 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,
677 SUM(aqorders.quantity) AS total_items,
679 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
680 ) AS total_items_cancelled,
681 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
683 IF(aqorders.datereceived IS NULL
684 AND aqorders.datecancellationprinted IS NULL
688 SUM( aqorders.uncertainprice ) AS uncertainprices
690 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
691 WHERE booksellerid = ?};
693 $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";
695 unless ( $allbaskets ) {
696 # Don't show the basket if it's NOT CLOSED or is FULLY RECEIVED
697 $query.=" HAVING (closedate IS NULL OR (
699 IF(aqorders.datereceived IS NULL
700 AND aqorders.datecancellationprinted IS NULL
706 my $sth = $dbh->prepare($query);
707 $sth->execute($supplierid);
708 my $baskets = $sth->fetchall_arrayref({});
710 # Retrieve the number of biblios cancelled
711 my $cancelled_biblios = $dbh->selectall_hashref( q|
712 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
714 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
715 WHERE booksellerid = ?
716 AND aqorders.orderstatus = 'cancelled'
717 GROUP BY aqbasket.basketno
718 |, 'basketno', {}, $supplierid );
720 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
726 =head3 GetBasketUsers
728 $basketusers_ids = &GetBasketUsers($basketno);
730 Returns a list of all borrowernumbers that are in basket users list
735 my $basketno = shift;
737 return unless $basketno;
740 SELECT borrowernumber
744 my $dbh = C4::Context->dbh;
745 my $sth = $dbh->prepare($query);
746 $sth->execute($basketno);
747 my $results = $sth->fetchall_arrayref( {} );
750 foreach (@$results) {
751 push @borrowernumbers, $_->{'borrowernumber'};
754 return @borrowernumbers;
757 =head3 ModBasketUsers
759 my @basketusers_ids = (1, 2, 3);
760 &ModBasketUsers($basketno, @basketusers_ids);
762 Delete all users from basket users list, and add users in C<@basketusers_ids>
768 my ($basketno, @basketusers_ids) = @_;
770 return unless $basketno;
772 my $dbh = C4::Context->dbh;
774 DELETE FROM aqbasketusers
777 my $sth = $dbh->prepare($query);
778 $sth->execute($basketno);
781 INSERT INTO aqbasketusers (basketno, borrowernumber)
784 $sth = $dbh->prepare($query);
785 foreach my $basketuser_id (@basketusers_ids) {
786 $sth->execute($basketno, $basketuser_id);
791 =head3 CanUserManageBasket
793 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
794 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
796 Check if a borrower can manage a basket, according to system preference
797 AcqViewBaskets, user permissions and basket properties (creator, users list,
800 First parameter can be either a borrowernumber or a hashref as returned by
801 Koha::Patron->unblessed
803 Second parameter can be either a basketno or a hashref as returned by
804 C4::Acquisition::GetBasket.
806 The third parameter is optional. If given, it should be a hashref as returned
807 by C4::Auth::getuserflags. If not, getuserflags is called.
809 If user is authorised to manage basket, returns 1.
814 sub CanUserManageBasket {
815 my ($borrower, $basket, $userflags) = @_;
817 if (!ref $borrower) {
818 # FIXME This needs to be replaced
819 # We should not accept both scalar and array
820 # Tests need to be updated
821 $borrower = Koha::Patrons->find( $borrower )->unblessed;
824 $basket = GetBasket($basket);
827 return 0 unless ($basket and $borrower);
829 my $borrowernumber = $borrower->{borrowernumber};
830 my $basketno = $basket->{basketno};
832 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
834 if (!defined $userflags) {
835 my $dbh = C4::Context->dbh;
836 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
837 $sth->execute($borrowernumber);
838 my ($flags) = $sth->fetchrow_array;
841 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
844 unless ($userflags->{superlibrarian}
845 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
846 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
848 if (not exists $userflags->{acquisition}) {
852 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
853 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
857 if ($AcqViewBaskets eq 'user'
858 && $basket->{authorisedby} != $borrowernumber
859 && ! grep { $borrowernumber eq $_ } GetBasketUsers($basketno)) {
863 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
864 && $basket->{branch} ne $borrower->{branchcode}) {
872 #------------------------------------------------------------#
874 =head3 GetBasketsByBasketgroup
876 $baskets = &GetBasketsByBasketgroup($basketgroupid);
878 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
882 sub GetBasketsByBasketgroup {
883 my $basketgroupid = shift;
885 SELECT *, aqbasket.booksellerid as booksellerid
887 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
889 my $dbh = C4::Context->dbh;
890 my $sth = $dbh->prepare($query);
891 $sth->execute($basketgroupid);
892 return $sth->fetchall_arrayref({});
895 #------------------------------------------------------------#
897 =head3 NewBasketgroup
899 $basketgroupid = NewBasketgroup(\%hashref);
901 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
903 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
905 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
907 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
909 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
911 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
913 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
915 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
917 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
922 my $basketgroupinfo = shift;
923 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
924 my $query = "INSERT INTO aqbasketgroups (";
926 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
927 if ( defined $basketgroupinfo->{$field} ) {
928 $query .= "$field, ";
929 push(@params, $basketgroupinfo->{$field});
932 $query .= "booksellerid) VALUES (";
937 push(@params, $basketgroupinfo->{'booksellerid'});
938 my $dbh = C4::Context->dbh;
939 my $sth = $dbh->prepare($query);
940 $sth->execute(@params);
941 my $basketgroupid = $dbh->{'mysql_insertid'};
942 if( $basketgroupinfo->{'basketlist'} ) {
943 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
944 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
945 my $sth2 = $dbh->prepare($query2);
946 $sth2->execute($basketgroupid, $basketno);
949 return $basketgroupid;
952 #------------------------------------------------------------#
954 =head3 ModBasketgroup
956 ModBasketgroup(\%hashref);
958 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
960 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
962 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
964 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
966 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
968 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
970 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
972 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
974 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
979 my $basketgroupinfo = shift;
980 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
981 my $dbh = C4::Context->dbh;
982 my $query = "UPDATE aqbasketgroups SET ";
984 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
985 if ( defined $basketgroupinfo->{$field} ) {
986 $query .= "$field=?, ";
987 push(@params, $basketgroupinfo->{$field});
992 $query .= " WHERE id=?";
993 push(@params, $basketgroupinfo->{'id'});
994 my $sth = $dbh->prepare($query);
995 $sth->execute(@params);
997 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
998 $sth->execute($basketgroupinfo->{'id'});
1000 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
1001 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
1002 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
1003 $sth->execute($basketgroupinfo->{'id'}, $basketno);
1009 #------------------------------------------------------------#
1011 =head3 DelBasketgroup
1013 DelBasketgroup($basketgroupid);
1015 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
1019 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
1025 sub DelBasketgroup {
1026 my $basketgroupid = shift;
1027 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1028 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
1029 my $dbh = C4::Context->dbh;
1030 my $sth = $dbh->prepare($query);
1031 $sth->execute($basketgroupid);
1035 #------------------------------------------------------------#
1038 =head2 FUNCTIONS ABOUT ORDERS
1040 =head3 GetBasketgroup
1042 $basketgroup = &GetBasketgroup($basketgroupid);
1044 Returns a reference to the hash containing all information about the basketgroup.
1048 sub GetBasketgroup {
1049 my $basketgroupid = shift;
1050 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1051 my $dbh = C4::Context->dbh;
1052 my $result_set = $dbh->selectall_arrayref(
1053 'SELECT * FROM aqbasketgroups WHERE id=?',
1057 return $result_set->[0]; # id is unique
1060 #------------------------------------------------------------#
1062 =head3 GetBasketgroups
1064 $basketgroups = &GetBasketgroups($booksellerid);
1066 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1070 sub GetBasketgroups {
1071 my $booksellerid = shift;
1072 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1073 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1074 my $dbh = C4::Context->dbh;
1075 my $sth = $dbh->prepare($query);
1076 $sth->execute($booksellerid);
1077 return $sth->fetchall_arrayref({});
1080 #------------------------------------------------------------#
1082 =head2 FUNCTIONS ABOUT ORDERS
1086 @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } );
1088 Looks up the pending (non-cancelled) orders with the given basket
1091 If cancelled is set, only cancelled orders will be returned.
1096 my ( $basketno, $params ) = @_;
1098 return () unless $basketno;
1100 my $orderby = $params->{orderby};
1101 my $cancelled = $params->{cancelled} || 0;
1103 my $dbh = C4::Context->dbh;
1105 SELECT biblio.*,biblioitems.*,
1109 $query .= $cancelled
1111 aqorders_transfers.ordernumber_to AS transferred_to,
1112 aqorders_transfers.timestamp AS transferred_to_timestamp
1115 aqorders_transfers.ordernumber_from AS transferred_from,
1116 aqorders_transfers.timestamp AS transferred_from_timestamp
1120 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1121 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1122 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1124 $query .= $cancelled
1126 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1129 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1137 $orderby ||= q|biblioitems.publishercode, biblio.title|;
1139 AND datecancellationprinted IS NOT NULL
1144 q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|;
1146 AND datecancellationprinted IS NULL
1150 $query .= " ORDER BY $orderby";
1152 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1157 #------------------------------------------------------------#
1159 =head3 GetOrdersByBiblionumber
1161 @orders = &GetOrdersByBiblionumber($biblionumber);
1163 Looks up the orders with linked to a specific $biblionumber, including
1164 cancelled orders and received orders.
1167 C<@orders> is an array of references-to-hash, whose keys are the
1168 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1172 sub GetOrdersByBiblionumber {
1173 my $biblionumber = shift;
1174 return unless $biblionumber;
1175 my $dbh = C4::Context->dbh;
1177 SELECT biblio.*,biblioitems.*,
1181 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1182 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1183 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1184 WHERE aqorders.biblionumber=?
1187 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1188 return @{$result_set};
1192 #------------------------------------------------------------#
1196 $order = &GetOrder($ordernumber);
1198 Looks up an order by order number.
1200 Returns a reference-to-hash describing the order. The keys of
1201 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1206 my ($ordernumber) = @_;
1207 return unless $ordernumber;
1209 my $dbh = C4::Context->dbh;
1210 my $query = qq{SELECT
1214 aqbasket.basketname,
1215 borrowers.branchcode,
1216 biblioitems.publicationyear,
1217 biblio.copyrightdate,
1218 biblioitems.editionstatement,
1222 biblioitems.publishercode,
1223 aqorders.rrp AS unitpricesupplier,
1224 aqorders.ecost AS unitpricelib,
1225 aqorders.claims_count AS claims_count,
1226 aqorders.claimed_date AS claimed_date,
1227 aqbudgets.budget_name AS budget,
1228 aqbooksellers.name AS supplier,
1229 aqbooksellers.id AS supplierid,
1230 biblioitems.publishercode AS publisher,
1231 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1232 DATE(aqbasket.closedate) AS orderdate,
1233 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1234 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1235 DATEDIFF(CURDATE( ),closedate) AS latesince
1236 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1237 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1238 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1239 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1240 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1241 WHERE aqorders.basketno = aqbasket.basketno
1244 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1246 # result_set assumed to contain 1 match
1247 return $result_set->[0];
1252 &ModOrder(\%hashref);
1254 Modifies an existing order. Updates the order with order number
1255 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1256 other keys of the hash update the fields with the same name in the aqorders
1257 table of the Koha database.
1262 my $orderinfo = shift;
1264 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '';
1266 my $dbh = C4::Context->dbh;
1269 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1270 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1272 # delete($orderinfo->{'branchcode'});
1273 # the hash contains a lot of entries not in aqorders, so get the columns ...
1274 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1276 my $colnames = $sth->{NAME};
1277 #FIXME Be careful. If aqorders would have columns with diacritics,
1278 #you should need to decode what you get back from NAME.
1279 #See report 10110 and guided_reports.pl
1280 my $query = "UPDATE aqorders SET ";
1282 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1283 # ... and skip hash entries that are not in the aqorders table
1284 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1285 next unless grep { $_ eq $orderinfokey } @$colnames;
1286 $query .= "$orderinfokey=?, ";
1287 push(@params, $orderinfo->{$orderinfokey});
1290 $query .= "timestamp=NOW() WHERE ordernumber=?";
1291 push(@params, $orderinfo->{'ordernumber'} );
1292 $sth = $dbh->prepare($query);
1293 $sth->execute(@params);
1297 #------------------------------------------------------------#
1301 ModItemOrder($itemnumber, $ordernumber);
1303 Modifies the ordernumber of an item in aqorders_items.
1308 my ($itemnumber, $ordernumber) = @_;
1310 return unless ($itemnumber and $ordernumber);
1312 my $dbh = C4::Context->dbh;
1314 UPDATE aqorders_items
1316 WHERE itemnumber = ?
1318 my $sth = $dbh->prepare($query);
1319 return $sth->execute($ordernumber, $itemnumber);
1322 #------------------------------------------------------------#
1324 =head3 ModReceiveOrder
1326 my ( $date_received, $new_ordernumber ) = ModReceiveOrder(
1328 biblionumber => $biblionumber,
1330 quantityreceived => $quantityreceived,
1332 invoice => $invoice,
1333 budget_id => $budget_id,
1334 datereceived => $datereceived,
1335 received_itemnumbers => \@received_itemnumbers,
1339 Updates an order, to reflect the fact that it was received, at least
1342 If a partial order is received, splits the order into two.
1344 Updates the order with biblionumber C<$biblionumber> and ordernumber
1345 C<$order->{ordernumber}>.
1350 sub ModReceiveOrder {
1352 my $biblionumber = $params->{biblionumber};
1353 my $order = { %{ $params->{order} } }; # Copy the order, we don't want to modify it
1354 my $invoice = $params->{invoice};
1355 my $quantrec = $params->{quantityreceived};
1356 my $user = $params->{user};
1357 my $budget_id = $params->{budget_id};
1358 my $datereceived = $params->{datereceived};
1359 my $received_items = $params->{received_items};
1361 my $dbh = C4::Context->dbh;
1362 $datereceived = output_pref(
1364 dt => ( $datereceived ? dt_from_string( $datereceived ) : dt_from_string ),
1365 dateformat => 'iso',
1370 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1371 if ($suggestionid) {
1372 ModSuggestion( {suggestionid=>$suggestionid,
1373 STATUS=>'AVAILABLE',
1374 biblionumber=> $biblionumber}
1378 my $result_set = $dbh->selectrow_arrayref(
1379 q{SELECT aqbasket.is_standing
1381 WHERE basketno=?},{ Slice => {} }, $order->{basketno});
1382 my $is_standing = $result_set->[0]; # we assume we have a unique basket
1384 my $new_ordernumber = $order->{ordernumber};
1385 if ( $is_standing || $order->{quantity} > $quantrec ) {
1386 # Split order line in two parts: the first is the original order line
1387 # without received items (the quantity is decreased),
1388 # the second part is a new order line with quantity=quantityrec
1389 # (entirely received)
1393 orderstatus = 'partial'|;
1394 $query .= q| WHERE ordernumber = ?|;
1395 my $sth = $dbh->prepare($query);
1398 ( $is_standing ? 1 : ($order->{quantity} - $quantrec) ),
1399 $order->{ordernumber}
1402 if ( not $order->{subscriptionid} && defined $order->{order_internalnote} ) {
1405 SET order_internalnote = ?
1406 WHERE ordernumber = ?|, {},
1407 $order->{order_internalnote}, $order->{ordernumber}
1411 # Recalculate tax_value
1415 tax_value_on_ordering = quantity * | . get_rounding_sql(q|ecost_tax_excluded|) . q| * tax_rate_on_ordering,
1416 tax_value_on_receiving = quantity * | . get_rounding_sql(q|unitprice_tax_excluded|) . q| * tax_rate_on_receiving
1417 WHERE ordernumber = ?
1418 |, undef, $order->{ordernumber});
1420 delete $order->{ordernumber};
1421 $order->{budget_id} = ( $budget_id || $order->{budget_id} );
1422 $order->{quantity} = $quantrec;
1423 $order->{quantityreceived} = $quantrec;
1424 $order->{ecost_tax_excluded} //= 0;
1425 $order->{tax_rate_on_ordering} //= 0;
1426 $order->{unitprice_tax_excluded} //= 0;
1427 $order->{tax_rate_on_receiving} //= 0;
1428 $order->{tax_value_on_ordering} = $order->{quantity} * get_rounded_price($order->{ecost_tax_excluded}) * $order->{tax_rate_on_ordering};
1429 $order->{tax_value_on_receiving} = $order->{quantity} * get_rounded_price($order->{unitprice_tax_excluded}) * $order->{tax_rate_on_receiving};
1430 $order->{datereceived} = $datereceived;
1431 $order->{invoiceid} = $invoice->{invoiceid};
1432 $order->{orderstatus} = 'complete';
1433 $new_ordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber; # TODO What if the store fails?
1435 if ($received_items) {
1436 foreach my $itemnumber (@$received_items) {
1437 ModItemOrder($itemnumber, $new_ordernumber);
1443 SET quantityreceived = ?,
1447 orderstatus = 'complete'
1451 , replacementprice = ?
1452 | if defined $order->{replacementprice};
1455 , unitprice = ?, unitprice_tax_included = ?, unitprice_tax_excluded = ?
1456 | if defined $order->{unitprice};
1459 ,tax_value_on_receiving = ?
1460 | if defined $order->{tax_value_on_receiving};
1463 ,tax_rate_on_receiving = ?
1464 | if defined $order->{tax_rate_on_receiving};
1467 , order_internalnote = ?
1468 | if defined $order->{order_internalnote};
1470 $query .= q| where biblionumber=? and ordernumber=?|;
1472 my $sth = $dbh->prepare( $query );
1473 my @params = ( $quantrec, $datereceived, $invoice->{invoiceid}, ( $budget_id ? $budget_id : $order->{budget_id} ) );
1475 if ( defined $order->{replacementprice} ) {
1476 push @params, $order->{replacementprice};
1479 if ( defined $order->{unitprice} ) {
1480 push @params, $order->{unitprice}, $order->{unitprice_tax_included}, $order->{unitprice_tax_excluded};
1483 if ( defined $order->{tax_value_on_receiving} ) {
1484 push @params, $order->{tax_value_on_receiving};
1487 if ( defined $order->{tax_rate_on_receiving} ) {
1488 push @params, $order->{tax_rate_on_receiving};
1491 if ( defined $order->{order_internalnote} ) {
1492 push @params, $order->{order_internalnote};
1495 push @params, ( $biblionumber, $order->{ordernumber} );
1497 $sth->execute( @params );
1499 # All items have been received, sent a notification to users
1500 NotifyOrderUsers( $order->{ordernumber} );
1503 return ($datereceived, $new_ordernumber);
1506 =head3 CancelReceipt
1508 my $parent_ordernumber = CancelReceipt($ordernumber);
1510 Cancel an order line receipt and update the parent order line, as if no
1512 If items are created at receipt (AcqCreateItem = receiving) then delete
1518 my $ordernumber = shift;
1520 return unless $ordernumber;
1522 my $dbh = C4::Context->dbh;
1524 SELECT datereceived, parent_ordernumber, quantity
1526 WHERE ordernumber = ?
1528 my $sth = $dbh->prepare($query);
1529 $sth->execute($ordernumber);
1530 my $order = $sth->fetchrow_hashref;
1532 warn "CancelReceipt: order $ordernumber does not exist";
1535 unless($order->{'datereceived'}) {
1536 warn "CancelReceipt: order $ordernumber is not received";
1540 my $parent_ordernumber = $order->{'parent_ordernumber'};
1542 my $order_obj = Koha::Acquisition::Orders->find( $ordernumber ); # FIXME rewrite all this subroutine using this object
1543 my @itemnumbers = $order_obj->items->get_column('itemnumber');
1545 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1546 # The order line has no parent, just mark it as not received
1549 SET quantityreceived = ?,
1552 orderstatus = 'ordered'
1553 WHERE ordernumber = ?
1555 $sth = $dbh->prepare($query);
1556 $sth->execute(0, undef, undef, $ordernumber);
1557 _cancel_items_receipt( $order_obj );
1559 # The order line has a parent, increase parent quantity and delete
1561 unless ( $order_obj->basket->is_standing ) {
1563 SELECT quantity, datereceived
1565 WHERE ordernumber = ?
1567 $sth = $dbh->prepare($query);
1568 $sth->execute($parent_ordernumber);
1569 my $parent_order = $sth->fetchrow_hashref;
1570 unless($parent_order) {
1571 warn "Parent order $parent_ordernumber does not exist.";
1574 if($parent_order->{'datereceived'}) {
1575 warn "CancelReceipt: parent order is received.".
1576 " Can't cancel receipt.";
1582 orderstatus = 'ordered'
1583 WHERE ordernumber = ?
1585 $sth = $dbh->prepare($query);
1586 my $rv = $sth->execute(
1587 $order->{'quantity'} + $parent_order->{'quantity'},
1591 warn "Cannot update parent order line, so do not cancel".
1596 # Recalculate tax_value
1600 tax_value_on_ordering = quantity * | . get_rounding_sql(q|ecost_tax_excluded|) . q| * tax_rate_on_ordering,
1601 tax_value_on_receiving = quantity * | . get_rounding_sql(q|unitprice_tax_excluded|) . q| * tax_rate_on_receiving
1602 WHERE ordernumber = ?
1603 |, undef, $parent_ordernumber);
1606 _cancel_items_receipt( $order_obj, $parent_ordernumber );
1609 DELETE FROM aqorders
1610 WHERE ordernumber = ?
1612 $sth = $dbh->prepare($query);
1613 $sth->execute($ordernumber);
1617 if( $order_obj->basket->effective_create_items eq 'ordering' ) {
1618 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1620 for my $in ( @itemnumbers ) {
1621 my $item = Koha::Items->find( $in ); # FIXME We do not need that, we already have Koha::Items from $order_obj->items
1622 my $biblio = $item->biblio;
1623 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber' );
1624 my $item_marc = C4::Items::GetMarcItem( $biblio->biblionumber, $in );
1625 for my $affect ( @affects ) {
1626 my ( $sf, $v ) = split q{=}, $affect, 2;
1627 foreach ( $item_marc->field($itemfield) ) {
1628 $_->update( $sf => $v );
1631 C4::Items::ModItemFromMarc( $item_marc, $biblio->biblionumber, $in );
1636 return $parent_ordernumber;
1639 sub _cancel_items_receipt {
1640 my ( $order, $parent_ordernumber ) = @_;
1641 $parent_ordernumber ||= $order->ordernumber;
1643 my $items = $order->items;
1644 if ( $order->basket->effective_create_items eq 'receiving' ) {
1645 # Remove items that were created at receipt
1647 DELETE FROM items, aqorders_items
1648 USING items, aqorders_items
1649 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1651 my $dbh = C4::Context->dbh;
1652 my $sth = $dbh->prepare($query);
1653 while ( my $item = $items->next ) {
1654 $sth->execute($item->itemnumber, $item->itemnumber);
1658 while ( my $item = $items->next ) {
1659 ModItemOrder($item->itemnumber, $parent_ordernumber);
1664 #------------------------------------------------------------#
1668 @results = &SearchOrders({
1669 ordernumber => $ordernumber,
1672 booksellerid => $booksellerid,
1673 basketno => $basketno,
1674 basketname => $basketname,
1675 basketgroupname => $basketgroupname,
1679 biblionumber => $biblionumber,
1680 budget_id => $budget_id
1683 Searches for orders filtered by criteria.
1685 C<$ordernumber> Finds matching orders or transferred orders by ordernumber.
1686 C<$search> Finds orders matching %$search% in title, author, or isbn.
1687 C<$owner> Finds order for the logged in user.
1688 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1689 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1692 C<@results> is an array of references-to-hash with the keys are fields
1693 from aqorders, biblio, biblioitems and aqbasket tables.
1698 my ( $params ) = @_;
1699 my $ordernumber = $params->{ordernumber};
1700 my $search = $params->{search};
1701 my $ean = $params->{ean};
1702 my $booksellerid = $params->{booksellerid};
1703 my $basketno = $params->{basketno};
1704 my $basketname = $params->{basketname};
1705 my $basketgroupname = $params->{basketgroupname};
1706 my $owner = $params->{owner};
1707 my $pending = $params->{pending};
1708 my $ordered = $params->{ordered};
1709 my $biblionumber = $params->{biblionumber};
1710 my $budget_id = $params->{budget_id};
1712 my $dbh = C4::Context->dbh;
1715 SELECT aqbasket.basketno,
1717 borrowers.firstname,
1720 biblioitems.biblioitemnumber,
1721 biblioitems.publishercode,
1722 biblioitems.publicationyear,
1723 aqbasket.authorisedby,
1724 aqbasket.booksellerid,
1726 aqbasket.creationdate,
1727 aqbasket.basketname,
1728 aqbasketgroups.id as basketgroupid,
1729 aqbasketgroups.name as basketgroupname,
1732 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1733 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1734 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1735 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1736 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1739 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1741 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1745 WHERE (datecancellationprinted is NULL)
1748 if ( $pending or $ordered ) {
1751 ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) )
1753 ( quantity > quantityreceived OR quantityreceived is NULL )
1757 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1765 my $userenv = C4::Context->userenv;
1766 if ( C4::Context->preference("IndependentBranches") ) {
1767 unless ( C4::Context->IsSuperLibrarian() ) {
1770 borrowers.branchcode = ?
1771 OR borrowers.branchcode = ''
1774 push @args, $userenv->{branch};
1778 if ( $ordernumber ) {
1779 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1780 push @args, ( $ordernumber, $ordernumber );
1782 if ( $biblionumber ) {
1783 $query .= 'AND aqorders.biblionumber = ?';
1784 push @args, $biblionumber;
1787 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1788 push @args, ("%$search%","%$search%","%$search%");
1791 $query .= ' AND biblioitems.ean = ?';
1794 if ( $booksellerid ) {
1795 $query .= 'AND aqbasket.booksellerid = ?';
1796 push @args, $booksellerid;
1799 $query .= 'AND aqbasket.basketno = ?';
1800 push @args, $basketno;
1803 $query .= 'AND aqbasket.basketname LIKE ?';
1804 push @args, "%$basketname%";
1806 if( $basketgroupname ) {
1807 $query .= ' AND aqbasketgroups.name LIKE ?';
1808 push @args, "%$basketgroupname%";
1812 $query .= ' AND aqbasket.authorisedby=? ';
1813 push @args, $userenv->{'number'};
1817 $query .= ' AND aqorders.budget_id = ?';
1818 push @args, $budget_id;
1821 $query .= ' ORDER BY aqbasket.basketno';
1823 my $sth = $dbh->prepare($query);
1824 $sth->execute(@args);
1825 return $sth->fetchall_arrayref({});
1828 #------------------------------------------------------------#
1832 &DelOrder($biblionumber, $ordernumber);
1834 Cancel the order with the given order and biblio numbers. It does not
1835 delete any entries in the aqorders table, it merely marks them as
1841 my ( $bibnum, $ordernumber, $delete_biblio, $reason ) = @_;
1843 my $dbh = C4::Context->dbh;
1846 SET datecancellationprinted=now(), orderstatus='cancelled'
1849 $query .= ", cancellationreason = ? ";
1852 WHERE biblionumber=? AND ordernumber=?
1854 my $sth = $dbh->prepare($query);
1856 $sth->execute($reason, $bibnum, $ordernumber);
1858 $sth->execute( $bibnum, $ordernumber );
1862 my $order = Koha::Acquisition::Orders->find($ordernumber);
1863 my $items = $order->items;
1864 while ( my $item = $items->next ) { # Should be moved to Koha::Acquisition::Order->delete
1865 my $delcheck = $item->safe_delete;
1867 if($delcheck ne '1') {
1868 $error->{'delitem'} = 1;
1872 if($delete_biblio) {
1873 # We get the number of remaining items
1874 my $biblio = Koha::Biblios->find( $bibnum );
1875 my $itemcount = $biblio->items->count;
1877 # If there are no items left,
1878 if ( $itemcount == 0 ) {
1879 # We delete the record
1880 my $delcheck = DelBiblio($bibnum);
1883 $error->{'delbiblio'} = 1;
1891 =head3 TransferOrder
1893 my $newordernumber = TransferOrder($ordernumber, $basketno);
1895 Transfer an order line to a basket.
1896 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1897 to BOOKSELLER on DATE' and create new order with internal note
1898 'Transferred from BOOKSELLER on DATE'.
1899 Move all attached items to the new order.
1900 Received orders cannot be transferred.
1901 Return the ordernumber of created order.
1906 my ($ordernumber, $basketno) = @_;
1908 return unless ($ordernumber and $basketno);
1910 my $order = Koha::Acquisition::Orders->find( $ordernumber ) or return;
1911 return if $order->datereceived;
1913 $order = $order->unblessed;
1915 my $basket = GetBasket($basketno);
1916 return unless $basket;
1918 my $dbh = C4::Context->dbh;
1919 my ($query, $sth, $rv);
1923 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1924 WHERE ordernumber = ?
1926 $sth = $dbh->prepare($query);
1927 $rv = $sth->execute('cancelled', $ordernumber);
1929 delete $order->{'ordernumber'};
1930 delete $order->{parent_ordernumber};
1931 $order->{'basketno'} = $basketno;
1933 my $newordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber;
1936 UPDATE aqorders_items
1938 WHERE ordernumber = ?
1940 $sth = $dbh->prepare($query);
1941 $sth->execute($newordernumber, $ordernumber);
1944 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1947 $sth = $dbh->prepare($query);
1948 $sth->execute($ordernumber, $newordernumber);
1950 return $newordernumber;
1953 =head3 get_rounding_sql
1955 $rounding_sql = get_rounding_sql($column_name);
1957 returns the correct SQL routine based on OrderPriceRounding system preference.
1961 sub get_rounding_sql {
1962 my ( $round_string ) = @_;
1963 my $rounding_pref = C4::Context->preference('OrderPriceRounding') // q{};
1964 if ( $rounding_pref eq "nearest_cent" ) {
1965 return "CAST($round_string*100 AS SIGNED)/100";
1967 return $round_string;
1970 =head3 get_rounded_price
1972 $rounded_price = get_rounded_price( $price );
1974 returns a price rounded as specified in OrderPriceRounding system preference.
1978 sub get_rounded_price {
1980 my $rounding_pref = C4::Context->preference('OrderPriceRounding') // q{};
1981 if( $rounding_pref eq 'nearest_cent' ) {
1982 return Koha::Number::Price->new( $price )->round();
1988 =head2 FUNCTIONS ABOUT PARCELS
1992 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1994 get a lists of parcels.
2001 is the bookseller this function has to get parcels.
2004 To know on what criteria the results list has to be ordered.
2007 is the booksellerinvoicenumber.
2009 =item $datefrom & $dateto
2010 to know on what date this function has to filter its search.
2015 a pointer on a hash list containing parcel informations as such :
2021 =item Last operation
2023 =item Number of biblio
2025 =item Number of items
2032 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
2033 my $dbh = C4::Context->dbh;
2034 my @query_params = ();
2036 SELECT aqinvoices.invoicenumber,
2037 datereceived,purchaseordernumber,
2038 count(DISTINCT biblionumber) AS biblio,
2039 sum(quantity) AS itemsexpected,
2040 sum(quantityreceived) AS itemsreceived
2041 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
2042 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2043 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
2045 push @query_params, $bookseller;
2047 if ( defined $code ) {
2048 $strsth .= ' and aqinvoices.invoicenumber like ? ';
2049 # add a % to the end of the code to allow stemming.
2050 push @query_params, "$code%";
2053 if ( defined $datefrom ) {
2054 $strsth .= ' and datereceived >= ? ';
2055 push @query_params, $datefrom;
2058 if ( defined $dateto ) {
2059 $strsth .= 'and datereceived <= ? ';
2060 push @query_params, $dateto;
2063 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
2065 # can't use a placeholder to place this column name.
2066 # but, we could probably be checking to make sure it is a column that will be fetched.
2067 $strsth .= "order by $order " if ($order);
2069 my $sth = $dbh->prepare($strsth);
2071 $sth->execute( @query_params );
2072 my $results = $sth->fetchall_arrayref({});
2076 #------------------------------------------------------------#
2078 =head3 GetLateOrders
2080 @results = &GetLateOrders;
2082 Searches for bookseller with late orders.
2085 the table of supplier with late issues. This table is full of hashref.
2091 my $supplierid = shift;
2093 my $estimateddeliverydatefrom = shift;
2094 my $estimateddeliverydateto = shift;
2096 my $dbh = C4::Context->dbh;
2098 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2099 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2101 my @query_params = ();
2103 SELECT aqbasket.basketno,
2104 aqorders.ordernumber AS ordernumber,
2105 DATE(aqbasket.closedate) AS orderdate,
2106 aqbasket.basketname AS basketname,
2107 aqbasket.basketgroupid AS basketgroupid,
2108 aqbasketgroups.name AS basketgroupname,
2109 aqorders.rrp AS unitpricesupplier,
2110 aqorders.ecost AS unitpricelib,
2111 aqorders.claims_count AS claims_count,
2112 aqorders.claimed_date AS claimed_date,
2113 aqorders.order_internalnote AS internalnote,
2114 aqorders.order_vendornote AS vendornote,
2115 aqbudgets.budget_name AS budget,
2116 borrowers.branchcode AS branch,
2117 aqbooksellers.name AS supplier,
2118 aqbooksellers.id AS supplierid,
2119 biblio.author, biblio.title,
2120 biblioitems.publishercode AS publisher,
2121 biblioitems.publicationyear,
2122 biblioitems.isbn AS isbn,
2123 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2127 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2128 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2129 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2130 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2131 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2132 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2133 WHERE aqorders.basketno = aqbasket.basketno
2134 AND ( datereceived IS NULL
2135 OR aqorders.quantityreceived < aqorders.quantity
2137 AND aqbasket.closedate IS NOT NULL
2138 AND aqorders.datecancellationprinted IS NULL
2140 if ($dbdriver eq "mysql") {
2142 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2143 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2144 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2146 if ( defined $delay ) {
2147 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2148 push @query_params, $delay;
2150 $from .= " AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0";
2152 # FIXME: account for IFNULL as above
2154 aqorders.quantity AS quantity,
2155 aqorders.quantity * aqorders.rrp AS subtotal,
2156 (CAST(now() AS date) - closedate) AS latesince
2158 if ( defined $delay ) {
2159 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2160 push @query_params, $delay;
2162 $from .= " AND aqorders.quantity <> 0";
2164 if (defined $supplierid) {
2165 $from .= ' AND aqbasket.booksellerid = ? ';
2166 push @query_params, $supplierid;
2168 if (defined $branch) {
2169 $from .= ' AND borrowers.branchcode LIKE ? ';
2170 push @query_params, $branch;
2173 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2174 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2176 if ( defined $estimateddeliverydatefrom ) {
2177 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2178 push @query_params, $estimateddeliverydatefrom;
2180 if ( defined $estimateddeliverydateto ) {
2181 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2182 push @query_params, $estimateddeliverydateto;
2184 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2185 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2187 if (C4::Context->preference("IndependentBranches")
2188 && !C4::Context->IsSuperLibrarian() ) {
2189 $from .= ' AND borrowers.branchcode LIKE ? ';
2190 push @query_params, C4::Context->userenv->{branch};
2192 $from .= " AND orderstatus <> 'cancelled' ";
2193 my $query = "$select $from \nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2194 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2195 my $sth = $dbh->prepare($query);
2196 $sth->execute(@query_params);
2198 while (my $data = $sth->fetchrow_hashref) {
2199 push @results, $data;
2204 #------------------------------------------------------------#
2208 \@order_loop = GetHistory( %params );
2210 Retreives some acquisition history information
2220 basket - search both basket name and number
2221 booksellerinvoicenumber
2224 orderstatus (note that orderstatus '' will retrieve orders
2225 of any status except cancelled)
2228 get_canceled_order (if set to a true value, cancelled orders will
2232 $order_loop is a list of hashrefs that each look like this:
2234 'author' => 'Twain, Mark',
2236 'biblionumber' => '215',
2238 'creationdate' => 'MM/DD/YYYY',
2239 'datereceived' => undef,
2242 'invoicenumber' => undef,
2244 'ordernumber' => '1',
2246 'quantityreceived' => undef,
2247 'title' => 'The Adventures of Huckleberry Finn',
2248 'managing_library' => 'CPL'
2254 # don't run the query if there are no parameters (list would be too long for sure !)
2255 croak "No search params" unless @_;
2257 my $title = $params{title};
2258 my $author = $params{author};
2259 my $isbn = $params{isbn};
2260 my $ean = $params{ean};
2261 my $name = $params{name};
2262 my $from_placed_on = $params{from_placed_on};
2263 my $to_placed_on = $params{to_placed_on};
2264 my $basket = $params{basket};
2265 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2266 my $basketgroupname = $params{basketgroupname};
2267 my $budget = $params{budget};
2268 my $orderstatus = $params{orderstatus};
2269 my $biblionumber = $params{biblionumber};
2270 my $get_canceled_order = $params{get_canceled_order} || 0;
2271 my $ordernumber = $params{ordernumber};
2272 my $search_children_too = $params{search_children_too} || 0;
2273 my $created_by = $params{created_by} || [];
2274 my $managing_library = $params{managing_library};
2275 my $ordernumbers = $params{ordernumbers} || [];
2276 my $additional_fields = $params{additional_fields} // [];
2280 my $total_qtyreceived = 0;
2281 my $total_price = 0;
2283 #get variation of isbn
2287 if ( C4::Context->preference("SearchWithISBNVariations") ){
2288 @isbns = C4::Koha::GetVariationsOfISBN( $isbn );
2289 foreach my $isb (@isbns){
2290 push @isbn_params, '?';
2295 push @isbn_params, '?';
2299 my $dbh = C4::Context->dbh;
2302 COALESCE(biblio.title, deletedbiblio.title) AS title,
2303 COALESCE(biblio.author, deletedbiblio.author) AS author,
2304 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2305 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2307 aqbasket.basketname,
2308 aqbasket.basketgroupid,
2309 aqbasket.authorisedby,
2310 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2311 branch as managing_library,
2312 aqbasketgroups.name as groupname,
2314 aqbasket.creationdate,
2315 aqorders.datereceived,
2317 aqorders.quantityreceived,
2319 aqorders.ordernumber,
2321 aqinvoices.invoicenumber,
2322 aqbooksellers.id as id,
2323 aqorders.biblionumber,
2324 aqorders.orderstatus,
2325 aqorders.parent_ordernumber,
2326 aqbudgets.budget_name
2328 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2331 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2332 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2333 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2334 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2335 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2336 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2337 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2338 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2339 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2340 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2343 $query .= " WHERE 1 ";
2345 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2346 $query .= " AND datecancellationprinted IS NULL ";
2349 my @query_params = ();
2351 if ( $biblionumber ) {
2352 $query .= " AND biblio.biblionumber = ?";
2353 push @query_params, $biblionumber;
2357 $query .= " AND biblio.title LIKE ? ";
2358 $title =~ s/\s+/%/g;
2359 push @query_params, "%$title%";
2363 $query .= " AND biblio.author LIKE ? ";
2364 push @query_params, "%$author%";
2368 $query .= " AND ( biblioitems.isbn LIKE " . join (" OR biblioitems.isbn LIKE ", @isbn_params ) . ")";
2369 foreach my $isb (@isbns){
2370 push @query_params, "%$isb%";
2375 $query .= " AND biblioitems.ean = ? ";
2376 push @query_params, "$ean";
2379 $query .= " AND aqbooksellers.name LIKE ? ";
2380 push @query_params, "%$name%";
2384 $query .= " AND aqbudgets.budget_id = ? ";
2385 push @query_params, "$budget";
2388 if ( $from_placed_on ) {
2389 $query .= " AND creationdate >= ? ";
2390 push @query_params, $from_placed_on;
2393 if ( $to_placed_on ) {
2394 $query .= " AND creationdate <= ? ";
2395 push @query_params, $to_placed_on;
2398 if ( defined $orderstatus and $orderstatus ne '') {
2399 $query .= " AND aqorders.orderstatus = ? ";
2400 push @query_params, "$orderstatus";
2404 if ($basket =~ m/^\d+$/) {
2405 $query .= " AND aqorders.basketno = ? ";
2406 push @query_params, $basket;
2408 $query .= " AND aqbasket.basketname LIKE ? ";
2409 push @query_params, "%$basket%";
2413 if ($booksellerinvoicenumber) {
2414 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2415 push @query_params, "%$booksellerinvoicenumber%";
2418 if ($basketgroupname) {
2419 $query .= " AND aqbasketgroups.name LIKE ? ";
2420 push @query_params, "%$basketgroupname%";
2424 $query .= " AND (aqorders.ordernumber = ? ";
2425 push @query_params, $ordernumber;
2426 if ($search_children_too) {
2427 $query .= " OR aqorders.parent_ordernumber = ? ";
2428 push @query_params, $ordernumber;
2433 if ( @$created_by ) {
2434 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2435 push @query_params, @$created_by;
2438 if ( $managing_library ) {
2439 $query .= " AND aqbasket.branch = ? ";
2440 push @query_params, $managing_library;
2443 if ( @$ordernumbers ) {
2444 $query .= ' AND (aqorders.ordernumber IN ( ' . join (',', ('?') x @$ordernumbers ) . '))';
2445 push @query_params, @$ordernumbers;
2447 if ( @$additional_fields ) {
2448 my @baskets = Koha::Acquisition::Baskets->filter_by_additional_fields($additional_fields);
2450 return [] unless @baskets;
2452 # No parameterization because record IDs come directly from DB
2453 $query .= ' AND aqbasket.basketno IN ( ' . join( ',', map { $_->basketno } @baskets ) . ' )';
2456 if ( C4::Context->preference("IndependentBranches") ) {
2457 unless ( C4::Context->IsSuperLibrarian() ) {
2458 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2459 push @query_params, C4::Context->userenv->{branch};
2462 $query .= " ORDER BY id";
2464 return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2467 =head2 GetRecentAcqui
2469 $results = GetRecentAcqui($days);
2471 C<$results> is a ref to a table which contains hashref
2475 sub GetRecentAcqui {
2477 my $dbh = C4::Context->dbh;
2481 ORDER BY timestamp DESC
2484 my $sth = $dbh->prepare($query);
2486 my $results = $sth->fetchall_arrayref({});
2490 #------------------------------------------------------------#
2494 &AddClaim($ordernumber);
2496 Add a claim for an order
2501 my ($ordernumber) = @_;
2502 my $dbh = C4::Context->dbh;
2505 claims_count = claims_count + 1,
2506 claimed_date = CURDATE()
2507 WHERE ordernumber = ?
2509 my $sth = $dbh->prepare($query);
2510 $sth->execute($ordernumber);
2515 my @invoices = GetInvoices(
2516 invoicenumber => $invoicenumber,
2517 supplierid => $supplierid,
2518 suppliername => $suppliername,
2519 shipmentdatefrom => $shipmentdatefrom, # ISO format
2520 shipmentdateto => $shipmentdateto, # ISO format
2521 billingdatefrom => $billingdatefrom, # ISO format
2522 billingdateto => $billingdateto, # ISO format
2523 isbneanissn => $isbn_or_ean_or_issn,
2526 publisher => $publisher,
2527 publicationyear => $publicationyear,
2528 branchcode => $branchcode,
2529 order_by => $order_by
2532 Return a list of invoices that match all given criteria.
2534 $order_by is "column_name (asc|desc)", where column_name is any of
2535 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2536 'shipmentcost', 'shipmentcost_budgetid'.
2538 asc is the default if omitted
2545 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2546 closedate shipmentcost shipmentcost_budgetid);
2548 my $dbh = C4::Context->dbh;
2550 SELECT aqinvoices.invoiceid, aqinvoices.invoicenumber, aqinvoices.booksellerid, aqinvoices.shipmentdate, aqinvoices.billingdate, aqinvoices.closedate, aqinvoices.shipmentcost, aqinvoices.shipmentcost_budgetid, aqinvoices.message_id,
2551 aqbooksellers.name AS suppliername,
2554 aqorders.datereceived IS NOT NULL,
2555 aqorders.biblionumber,
2558 ) AS receivedbiblios,
2561 aqorders.subscriptionid IS NOT NULL,
2562 aqorders.subscriptionid,
2565 ) AS is_linked_to_subscriptions,
2566 SUM(aqorders.quantityreceived) AS receiveditems
2568 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2569 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2570 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2571 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2572 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2573 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2574 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2579 if($args{supplierid}) {
2580 push @bind_strs, " aqinvoices.booksellerid = ? ";
2581 push @bind_args, $args{supplierid};
2583 if($args{invoicenumber}) {
2584 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2585 push @bind_args, "%$args{invoicenumber}%";
2587 if($args{suppliername}) {
2588 push @bind_strs, " aqbooksellers.name LIKE ? ";
2589 push @bind_args, "%$args{suppliername}%";
2591 if($args{shipmentdatefrom}) {
2592 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2593 push @bind_args, $args{shipmentdatefrom};
2595 if($args{shipmentdateto}) {
2596 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2597 push @bind_args, $args{shipmentdateto};
2599 if($args{billingdatefrom}) {
2600 push @bind_strs, " aqinvoices.billingdate >= ? ";
2601 push @bind_args, $args{billingdatefrom};
2603 if($args{billingdateto}) {
2604 push @bind_strs, " aqinvoices.billingdate <= ? ";
2605 push @bind_args, $args{billingdateto};
2607 if($args{isbneanissn}) {
2608 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2609 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2612 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2613 push @bind_args, $args{title};
2616 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2617 push @bind_args, $args{author};
2619 if($args{publisher}) {
2620 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2621 push @bind_args, $args{publisher};
2623 if($args{publicationyear}) {
2624 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2625 push @bind_args, $args{publicationyear}, $args{publicationyear};
2627 if($args{branchcode}) {
2628 push @bind_strs, " borrowers.branchcode = ? ";
2629 push @bind_args, $args{branchcode};
2631 if($args{message_id}) {
2632 push @bind_strs, " aqinvoices.message_id = ? ";
2633 push @bind_args, $args{message_id};
2636 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2637 $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";
2639 if($args{order_by}) {
2640 my ($column, $direction) = split / /, $args{order_by};
2641 if(grep { $_ eq $column } @columns) {
2642 $direction ||= 'ASC';
2643 $query .= " ORDER BY $column $direction";
2647 my $sth = $dbh->prepare($query);
2648 $sth->execute(@bind_args);
2650 my $results = $sth->fetchall_arrayref({});
2656 my $invoice = GetInvoice($invoiceid);
2658 Get informations about invoice with given $invoiceid
2660 Return a hash filled with aqinvoices.* fields
2665 my ($invoiceid) = @_;
2668 return unless $invoiceid;
2670 my $dbh = C4::Context->dbh;
2676 my $sth = $dbh->prepare($query);
2677 $sth->execute($invoiceid);
2679 $invoice = $sth->fetchrow_hashref;
2683 =head3 GetInvoiceDetails
2685 my $invoice = GetInvoiceDetails($invoiceid)
2687 Return informations about an invoice + the list of related order lines
2689 Orders informations are in $invoice->{orders} (array ref)
2693 sub GetInvoiceDetails {
2694 my ($invoiceid) = @_;
2696 if ( !defined $invoiceid ) {
2697 carp 'GetInvoiceDetails called without an invoiceid';
2701 my $dbh = C4::Context->dbh;
2703 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2705 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2708 my $sth = $dbh->prepare($query);
2709 $sth->execute($invoiceid);
2711 my $invoice = $sth->fetchrow_hashref;
2716 biblio.copyrightdate,
2718 biblioitems.publishercode,
2719 biblioitems.publicationyear,
2720 aqbasket.basketname,
2721 aqbasketgroups.id AS basketgroupid,
2722 aqbasketgroups.name AS basketgroupname
2724 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2725 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2726 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2727 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2730 $sth = $dbh->prepare($query);
2731 $sth->execute($invoiceid);
2732 $invoice->{orders} = $sth->fetchall_arrayref({});
2733 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2740 my $invoiceid = AddInvoice(
2741 invoicenumber => $invoicenumber,
2742 booksellerid => $booksellerid,
2743 shipmentdate => $shipmentdate,
2744 billingdate => $billingdate,
2745 closedate => $closedate,
2746 shipmentcost => $shipmentcost,
2747 shipmentcost_budgetid => $shipmentcost_budgetid
2750 Create a new invoice and return its id or undef if it fails.
2757 return unless(%invoice and $invoice{invoicenumber});
2759 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2760 closedate shipmentcost shipmentcost_budgetid message_id);
2764 foreach my $key (keys %invoice) {
2765 if(0 < grep { $_ eq $key } @columns) {
2766 push @set_strs, "$key = ?";
2767 push @set_args, ($invoice{$key} || undef);
2773 my $dbh = C4::Context->dbh;
2774 my $query = "INSERT INTO aqinvoices SET ";
2775 $query .= join (",", @set_strs);
2776 my $sth = $dbh->prepare($query);
2777 $rv = $sth->execute(@set_args);
2779 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2788 invoiceid => $invoiceid, # Mandatory
2789 invoicenumber => $invoicenumber,
2790 booksellerid => $booksellerid,
2791 shipmentdate => $shipmentdate,
2792 billingdate => $billingdate,
2793 closedate => $closedate,
2794 shipmentcost => $shipmentcost,
2795 shipmentcost_budgetid => $shipmentcost_budgetid
2798 Modify an invoice, invoiceid is mandatory.
2800 Return undef if it fails.
2807 return unless(%invoice and $invoice{invoiceid});
2809 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2810 closedate shipmentcost shipmentcost_budgetid);
2814 foreach my $key (keys %invoice) {
2815 if(0 < grep { $_ eq $key } @columns) {
2816 push @set_strs, "$key = ?";
2817 push @set_args, ($invoice{$key} || undef);
2821 my $dbh = C4::Context->dbh;
2822 my $query = "UPDATE aqinvoices SET ";
2823 $query .= join(",", @set_strs);
2824 $query .= " WHERE invoiceid = ?";
2826 my $sth = $dbh->prepare($query);
2827 $sth->execute(@set_args, $invoice{invoiceid});
2832 CloseInvoice($invoiceid);
2836 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2841 my ($invoiceid) = @_;
2843 return unless $invoiceid;
2845 my $dbh = C4::Context->dbh;
2848 SET closedate = CAST(NOW() AS DATE)
2851 my $sth = $dbh->prepare($query);
2852 $sth->execute($invoiceid);
2855 =head3 ReopenInvoice
2857 ReopenInvoice($invoiceid);
2861 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => output_pref({ dt=>dt_from_string, dateonly=>1, otputpref=>'iso' }))
2866 my ($invoiceid) = @_;
2868 return unless $invoiceid;
2870 my $dbh = C4::Context->dbh;
2873 SET closedate = NULL
2876 my $sth = $dbh->prepare($query);
2877 $sth->execute($invoiceid);
2882 DelInvoice($invoiceid);
2884 Delete an invoice if there are no items attached to it.
2889 my ($invoiceid) = @_;
2891 return unless $invoiceid;
2893 my $dbh = C4::Context->dbh;
2899 my $sth = $dbh->prepare($query);
2900 $sth->execute($invoiceid);
2901 my $res = $sth->fetchrow_arrayref;
2902 if ( $res && $res->[0] == 0 ) {
2904 DELETE FROM aqinvoices
2907 my $sth = $dbh->prepare($query);
2908 return ( $sth->execute($invoiceid) > 0 );
2913 =head3 MergeInvoices
2915 MergeInvoices($invoiceid, \@sourceids);
2917 Merge the invoices identified by the IDs in \@sourceids into
2918 the invoice identified by $invoiceid.
2923 my ($invoiceid, $sourceids) = @_;
2925 return unless $invoiceid;
2926 foreach my $sourceid (@$sourceids) {
2927 next if $sourceid == $invoiceid;
2928 my $source = GetInvoiceDetails($sourceid);
2929 foreach my $order (@{$source->{'orders'}}) {
2930 $order->{'invoiceid'} = $invoiceid;
2933 DelInvoice($source->{'invoiceid'});
2938 =head3 GetBiblioCountByBasketno
2940 $biblio_count = &GetBiblioCountByBasketno($basketno);
2942 Looks up the biblio's count that has basketno value $basketno
2948 sub GetBiblioCountByBasketno {
2949 my ($basketno) = @_;
2950 my $dbh = C4::Context->dbh;
2952 SELECT COUNT( DISTINCT( biblionumber ) )
2955 AND datecancellationprinted IS NULL
2958 my $sth = $dbh->prepare($query);
2959 $sth->execute($basketno);
2960 return $sth->fetchrow;
2963 =head3 populate_order_with_prices
2965 $order = populate_order_with_prices({
2966 order => $order #a hashref with the order values
2967 booksellerid => $booksellerid #FIXME - should obtain from order basket
2968 receiving => 1 # boolean representing order stage, should pass only this or ordering
2969 ordering => 1 # boolean representing order stage
2973 Sets calculated values for an order - all values are stored with full precision
2974 regardless of rounding preference except for tax value which is calculated
2975 on rounded values if requested
2977 For ordering the values set are:
2982 tax_value_on_ordering
2983 For receiving the value set are:
2984 unitprice_tax_included
2985 unitprice_tax_excluded
2986 tax_value_on_receiving
2988 Note: When receiving, if the rounded value of the unitprice matches the rounded
2989 value of the ecost then then ecost (full precision) is used.
2991 Returns a hashref of the order
2993 FIXME: Move this to Koha::Acquisition::Order.pm
2997 sub populate_order_with_prices {
3000 my $order = $params->{order};
3001 my $booksellerid = $params->{booksellerid};
3002 return unless $booksellerid;
3004 my $bookseller = Koha::Acquisition::Booksellers->find( $booksellerid );
3006 my $receiving = $params->{receiving};
3007 my $ordering = $params->{ordering};
3008 my $discount = $order->{discount};
3009 $discount /= 100 if $discount > 1;
3012 $order->{tax_rate_on_ordering} //= $order->{tax_rate};
3013 if ( $bookseller->listincgst ) {
3015 # The user entered the prices tax included
3016 $order->{unitprice_tax_included} = $order->{unitprice};
3017 $order->{rrp_tax_included} = $order->{rrp};
3019 # price tax excluded = price tax included / ( 1 + tax rate )
3020 $order->{unitprice_tax_excluded} = $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate_on_ordering} );
3021 $order->{rrp_tax_excluded} = $order->{rrp_tax_included} / ( 1 + $order->{tax_rate_on_ordering} );
3023 # ecost tax included = rrp tax included ( 1 - discount )
3024 $order->{ecost_tax_included} = $order->{rrp_tax_included} * ( 1 - $discount );
3026 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
3027 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
3029 # tax value = quantity * ecost tax excluded * tax rate
3030 # we should use the unitprice if included
3031 my $cost_tax_included = $order->{unitprice_tax_included} || $order->{ecost_tax_included};
3032 my $cost_tax_excluded = $order->{unitprice_tax_excluded} || $order->{ecost_tax_excluded};
3033 $order->{tax_value_on_ordering} = ( get_rounded_price($cost_tax_included) - get_rounded_price($cost_tax_excluded) ) * $order->{quantity};
3037 # The user entered the prices tax excluded
3038 $order->{unitprice_tax_excluded} = $order->{unitprice};
3039 $order->{rrp_tax_excluded} = $order->{rrp};
3041 # price tax included = price tax excluded * ( 1 - tax rate )
3042 $order->{unitprice_tax_included} = $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate_on_ordering} );
3043 $order->{rrp_tax_included} = $order->{rrp_tax_excluded} * ( 1 + $order->{tax_rate_on_ordering} );
3045 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
3046 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
3048 # ecost tax included = rrp tax excluded * ( 1 + tax rate ) * ( 1 - discount ) = ecost tax excluded * ( 1 + tax rate )
3049 $order->{ecost_tax_included} = $order->{ecost_tax_excluded} * ( 1 + $order->{tax_rate_on_ordering} );
3051 # tax value = quantity * ecost tax included * tax rate
3052 # we should use the unitprice if included
3053 my $cost_tax_excluded = $order->{unitprice_tax_excluded} || $order->{ecost_tax_excluded};
3054 $order->{tax_value_on_ordering} = $order->{quantity} * get_rounded_price($cost_tax_excluded) * $order->{tax_rate_on_ordering};
3059 $order->{tax_rate_on_receiving} //= $order->{tax_rate};
3060 if ( $bookseller->invoiceincgst ) {
3061 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
3062 # we need to keep the exact ecost value
3063 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_included} )->round ) {
3064 $order->{unitprice} = $order->{ecost_tax_included};
3067 # The user entered the unit price tax included
3068 $order->{unitprice_tax_included} = $order->{unitprice};
3070 # unit price tax excluded = unit price tax included / ( 1 + tax rate )
3071 $order->{unitprice_tax_excluded} = $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate_on_receiving} );
3074 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
3075 # we need to keep the exact ecost value
3076 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_excluded} )->round ) {
3077 $order->{unitprice} = $order->{ecost_tax_excluded};
3080 # The user entered the unit price tax excluded
3081 $order->{unitprice_tax_excluded} = $order->{unitprice};
3084 # unit price tax included = unit price tax included * ( 1 + tax rate )
3085 $order->{unitprice_tax_included} = $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate_on_receiving} );
3088 # tax value = quantity * unit price tax excluded * tax rate
3089 $order->{tax_value_on_receiving} = $order->{quantity} * get_rounded_price($order->{unitprice_tax_excluded}) * $order->{tax_rate_on_receiving};
3095 =head3 GetOrderUsers
3097 $order_users_ids = &GetOrderUsers($ordernumber);
3099 Returns a list of all borrowernumbers that are in order users list
3104 my ($ordernumber) = @_;
3106 return unless $ordernumber;
3109 SELECT borrowernumber
3111 WHERE ordernumber = ?
3113 my $dbh = C4::Context->dbh;
3114 my $sth = $dbh->prepare($query);
3115 $sth->execute($ordernumber);
3116 my $results = $sth->fetchall_arrayref( {} );
3118 my @borrowernumbers;
3119 foreach (@$results) {
3120 push @borrowernumbers, $_->{'borrowernumber'};
3123 return @borrowernumbers;
3126 =head3 ModOrderUsers
3128 my @order_users_ids = (1, 2, 3);
3129 &ModOrderUsers($ordernumber, @basketusers_ids);
3131 Delete all users from order users list, and add users in C<@order_users_ids>
3137 my ( $ordernumber, @order_users_ids ) = @_;
3139 return unless $ordernumber;
3141 my $dbh = C4::Context->dbh;
3143 DELETE FROM aqorder_users
3144 WHERE ordernumber = ?
3146 my $sth = $dbh->prepare($query);
3147 $sth->execute($ordernumber);
3150 INSERT INTO aqorder_users (ordernumber, borrowernumber)
3153 $sth = $dbh->prepare($query);
3154 foreach my $order_user_id (@order_users_ids) {
3155 $sth->execute( $ordernumber, $order_user_id );
3159 sub NotifyOrderUsers {
3160 my ($ordernumber) = @_;
3162 my @borrowernumbers = GetOrderUsers($ordernumber);
3163 return unless @borrowernumbers;
3165 my $order = GetOrder( $ordernumber );
3166 for my $borrowernumber (@borrowernumbers) {
3167 my $patron = Koha::Patrons->find( $borrowernumber );
3168 my $library = $patron->library->unblessed;
3169 my $biblio = Koha::Biblios->find( $order->{biblionumber} )->unblessed;
3170 my $letter = C4::Letters::GetPreparedLetter(
3171 module => 'acquisition',
3172 letter_code => 'ACQ_NOTIF_ON_RECEIV',
3173 branchcode => $library->{branchcode},
3174 lang => $patron->lang,
3176 'branches' => $library,
3177 'borrowers' => $patron->unblessed,
3178 'biblio' => $biblio,
3179 'aqorders' => $order,
3183 C4::Letters::EnqueueLetter(
3186 borrowernumber => $borrowernumber,
3187 LibraryName => C4::Context->preference("LibraryName"),
3188 message_transport_type => 'email',
3190 ) or warn "can't enqueue letter $letter";
3195 =head3 FillWithDefaultValues
3197 FillWithDefaultValues( $marc_record, $params );
3199 This will update the record with default value defined in the ACQ framework.
3200 For all existing fields, if a default value exists and there are no subfield, it will be created.
3201 If the field does not exist, it will be created too.
3203 If the parameter only_mandatory => 1 is passed via $params, only the mandatory
3204 defaults are being applied to the record.
3208 sub FillWithDefaultValues {
3209 my ( $record, $params ) = @_;
3210 my $mandatory = $params->{only_mandatory};
3211 my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ', { unsafe => 1 } );
3214 C4::Biblio::GetMarcFromKohaField( 'items.itemnumber' );
3215 for my $tag ( sort keys %$tagslib ) {
3217 next if $tag == $itemfield;
3218 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
3219 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
3220 next if $mandatory && !$tagslib->{$tag}{$subfield}{mandatory};
3221 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
3222 if ( defined $defaultvalue and $defaultvalue ne '' ) {
3223 my @fields = $record->field($tag);
3225 for my $field (@fields) {
3226 if ( $field->is_control_field ) {
3227 $field->update($defaultvalue) if not defined $field->data;
3229 elsif ( not defined $field->subfield($subfield) ) {
3230 $field->add_subfields(
3231 $subfield => $defaultvalue );
3236 if ( $tag < 10 ) { # is_control_field
3237 $record->insert_fields_ordered(
3244 $record->insert_fields_ordered(
3246 $tag, '', '', $subfield => $defaultvalue
3262 Koha Development Team <http://koha-community.org/>