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::Order;
32 use Koha::Acquisition::Booksellers;
34 use Koha::Number::Price;
36 use Koha::CsvProfiles;
45 use vars qw(@ISA @EXPORT);
51 &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
52 &GetBasketAsCSV &GetBasketGroupAsCSV
53 &GetBasketsByBookseller &GetBasketsByBasketgroup
54 &GetBasketsInfosByBookseller
56 &GetBasketUsers &ModBasketUsers
61 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
62 &GetBasketgroups &ReOpenBasketgroup
64 &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
65 &GetLateOrders &GetOrderFromItemnumber
66 &SearchOrders &GetHistory &GetRecentAcqui
67 &ModReceiveOrder &CancelReceipt
69 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
84 &GetItemnumbersFromOrder
87 &GetBiblioCountByBasketno
93 &FillWithDefaultValues
101 sub GetOrderFromItemnumber {
102 my ($itemnumber) = @_;
103 my $dbh = C4::Context->dbh;
106 SELECT * from aqorders LEFT JOIN aqorders_items
107 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
108 WHERE itemnumber = ? |;
110 my $sth = $dbh->prepare($query);
114 $sth->execute($itemnumber);
116 my $order = $sth->fetchrow_hashref;
121 # Returns the itemnumber(s) associated with the ordernumber given in parameter
122 sub GetItemnumbersFromOrder {
123 my ($ordernumber) = @_;
124 my $dbh = C4::Context->dbh;
125 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
126 my $sth = $dbh->prepare($query);
127 $sth->execute($ordernumber);
130 while (my $order = $sth->fetchrow_hashref) {
131 push @tab, $order->{'itemnumber'};
145 C4::Acquisition - Koha functions for dealing with orders and acquisitions
153 The functions in this module deal with acquisitions, managing book
154 orders, basket and parcels.
158 =head2 FUNCTIONS ABOUT BASKETS
162 $aqbasket = &GetBasket($basketnumber);
164 get all basket informations in aqbasket for a given basket
166 B<returns:> informations for a given basket returned as a hashref.
172 my $dbh = C4::Context->dbh;
175 concat( b.firstname,' ',b.surname) AS authorisedbyname
177 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
180 my $sth=$dbh->prepare($query);
181 $sth->execute($basketno);
182 my $basket = $sth->fetchrow_hashref;
186 #------------------------------------------------------------#
190 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
191 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace, $is_standing );
193 Create a new basket in aqbasket table
197 =item C<$booksellerid> is a foreign key in the aqbasket table
199 =item C<$authorizedby> is the username of who created the basket
203 The other parameters are optional, see ModBasketHeader for more info on them.
208 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
209 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
210 $billingplace, $is_standing ) = @_;
211 my $dbh = C4::Context->dbh;
213 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
214 . 'VALUES (now(),?,?)';
215 $dbh->do( $query, {}, $booksellerid, $authorisedby );
217 my $basket = $dbh->{mysql_insertid};
218 $basketname ||= q{}; # default to empty strings
220 $basketbooksellernote ||= q{};
221 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
222 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing );
226 #------------------------------------------------------------#
230 &CloseBasket($basketno);
232 close a basket (becomes unmodifiable, except for receives)
238 my $dbh = C4::Context->dbh;
239 $dbh->do('UPDATE aqbasket SET closedate=now() WHERE basketno=?', {}, $basketno );
241 $dbh->do( q{UPDATE aqorders SET orderstatus = 'ordered' WHERE basketno = ? AND orderstatus != 'complete'},
248 &ReopenBasket($basketno);
256 my $dbh = C4::Context->dbh;
257 $dbh->do( q{UPDATE aqbasket SET closedate=NULL WHERE basketno=?}, {}, $basketno );
261 SET orderstatus = 'new'
263 AND orderstatus != 'complete'
268 #------------------------------------------------------------#
270 =head3 GetBasketAsCSV
272 &GetBasketAsCSV($basketno);
274 Export a basket as CSV
276 $cgi parameter is needed for column name translation
281 my ($basketno, $cgi, $csv_profile_id) = @_;
282 my $basket = GetBasket($basketno);
283 my @orders = GetOrders($basketno);
284 my $contract = GetContract({
285 contractnumber => $basket->{'contractnumber'}
288 my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
290 if ($csv_profile_id) {
291 my $csv_profile = Koha::CsvProfiles->find( $csv_profile_id );
292 die "There is no valid csv profile given" unless $csv_profile;
294 my $csv = Text::CSV_XS->new({'quote_char'=>'"','escape_char'=>'"','sep_char'=>$csv_profile->csv_separator,'binary'=>1});
295 my $csv_profile_content = $csv_profile->content;
296 my ( @headers, @fields );
297 while ( $csv_profile_content =~ /
300 ([^\|]*) # fieldname (table.row or row)
304 my $field = ($2 eq '') ? $1 : $2;
306 $header =~ s/^\s+|\s+$//g; # Trim whitespaces
307 push @headers, $header;
309 $field =~ s/[^\.]*\.{1}//; # Remove the table name if exists.
310 $field =~ s/^\s+|\s+$//g; # Trim whitespaces
311 push @fields, $field;
313 for my $order (@orders) {
315 my $bd = GetBiblioData( $order->{'biblionumber'} );
316 my @biblioitems = GetBiblioItemByBiblioNumber( $order->{'biblionumber'});
317 for my $biblioitem (@biblioitems) {
318 if ($biblioitem->{isbn} eq $order->{isbn}) {
319 $order = {%$order, %$biblioitem};
323 $order = {%$order, %$contract};
325 $order = {%$order, %$basket, %$bd};
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 $bd = GetBiblioData( $order->{'biblionumber'} );
343 contractname => $contract->{'contractname'},
344 ordernumber => $order->{'ordernumber'},
345 entrydate => $order->{'entrydate'},
346 isbn => $order->{'isbn'},
347 author => $bd->{'author'},
348 title => $bd->{'title'},
349 publicationyear => $bd->{'publicationyear'},
350 publishercode => $bd->{'publishercode'},
351 collectiontitle => $bd->{'collectiontitle'},
352 notes => $order->{'order_vendornote'},
353 quantity => $order->{'quantity'},
354 rrp => $order->{'rrp'},
356 for my $place ( qw( deliveryplace billingplace ) ) {
357 if ( my $library = Koha::Libraries->find( $row->{deliveryplace} ) ) {
358 $row->{$place} = $library->branchname
362 contractname author title publishercode collectiontitle notes
363 deliveryplace billingplace
365 # Double the quotes to not be interpreted as a field end
366 $row->{$_} =~ s/"/""/g if $row->{$_};
372 if(defined $a->{publishercode} and defined $b->{publishercode}) {
373 $a->{publishercode} cmp $b->{publishercode};
377 $template->param(rows => \@rows);
379 return $template->output;
384 =head3 GetBasketGroupAsCSV
386 &GetBasketGroupAsCSV($basketgroupid);
388 Export a basket group as CSV
390 $cgi parameter is needed for column name translation
394 sub GetBasketGroupAsCSV {
395 my ($basketgroupid, $cgi) = @_;
396 my $baskets = GetBasketsByBasketgroup($basketgroupid);
398 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
401 for my $basket (@$baskets) {
402 my @orders = GetOrders( $basket->{basketno} );
403 my $contract = GetContract({
404 contractnumber => $basket->{contractnumber}
406 my $bookseller = Koha::Acquisition::Booksellers->find( $basket->{booksellerid} );
407 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
409 foreach my $order (@orders) {
410 my $bd = GetBiblioData( $order->{'biblionumber'} );
412 clientnumber => $bookseller->accountnumber,
413 basketname => $basket->{basketname},
414 ordernumber => $order->{ordernumber},
415 author => $bd->{author},
416 title => $bd->{title},
417 publishercode => $bd->{publishercode},
418 publicationyear => $bd->{publicationyear},
419 collectiontitle => $bd->{collectiontitle},
420 isbn => $order->{isbn},
421 quantity => $order->{quantity},
422 rrp_tax_included => $order->{rrp_tax_included},
423 rrp_tax_excluded => $order->{rrp_tax_excluded},
424 discount => $bookseller->discount,
425 ecost_tax_included => $order->{ecost_tax_included},
426 ecost_tax_excluded => $order->{ecost_tax_excluded},
427 notes => $order->{order_vendornote},
428 entrydate => $order->{entrydate},
429 booksellername => $bookseller->name,
430 bookselleraddress => $bookseller->address1,
431 booksellerpostal => $bookseller->postal,
432 contractnumber => $contract->{contractnumber},
433 contractname => $contract->{contractname},
436 basketgroupdeliveryplace => $basketgroup->{deliveryplace},
437 basketgroupbillingplace => $basketgroup->{billingplace},
438 basketdeliveryplace => $basket->{deliveryplace},
439 basketbillingplace => $basket->{billingplace},
441 for my $place (qw( basketgroupdeliveryplace basketgroupbillingplace basketdeliveryplace basketbillingplace )) {
442 if ( my $library = Koha::Libraries->find( $temp->{$place} ) ) {
443 $row->{$place} = $library->branchname;
447 basketname author title publishercode collectiontitle notes
448 booksellername bookselleraddress booksellerpostal contractname
449 basketgroupdeliveryplace basketgroupbillingplace
450 basketdeliveryplace basketbillingplace
452 # Double the quotes to not be interpreted as a field end
453 $row->{$_} =~ s/"/""/g if $row->{$_};
458 $template->param(rows => \@rows);
460 return $template->output;
464 =head3 CloseBasketgroup
466 &CloseBasketgroup($basketgroupno);
472 sub CloseBasketgroup {
473 my ($basketgroupno) = @_;
474 my $dbh = C4::Context->dbh;
475 my $sth = $dbh->prepare("
476 UPDATE aqbasketgroups
480 $sth->execute($basketgroupno);
483 #------------------------------------------------------------#
485 =head3 ReOpenBaskergroup($basketgroupno)
487 &ReOpenBaskergroup($basketgroupno);
493 sub ReOpenBasketgroup {
494 my ($basketgroupno) = @_;
495 my $dbh = C4::Context->dbh;
496 my $sth = $dbh->prepare("
497 UPDATE aqbasketgroups
501 $sth->execute($basketgroupno);
504 #------------------------------------------------------------#
509 &DelBasket($basketno);
511 Deletes the basket that has basketno field $basketno in the aqbasket table.
515 =item C<$basketno> is the primary key of the basket in the aqbasket table.
522 my ( $basketno ) = @_;
523 my $query = "DELETE FROM aqbasket WHERE basketno=?";
524 my $dbh = C4::Context->dbh;
525 my $sth = $dbh->prepare($query);
526 $sth->execute($basketno);
530 #------------------------------------------------------------#
534 &ModBasket($basketinfo);
536 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
540 =item C<$basketno> is the primary key of the basket in the aqbasket table.
547 my $basketinfo = shift;
548 my $query = "UPDATE aqbasket SET ";
550 foreach my $key (keys %$basketinfo){
551 if ($key ne 'basketno'){
552 $query .= "$key=?, ";
553 push(@params, $basketinfo->{$key} || undef );
556 # get rid of the "," at the end of $query
557 if (substr($query, length($query)-2) eq ', '){
562 $query .= "WHERE basketno=?";
563 push(@params, $basketinfo->{'basketno'});
564 my $dbh = C4::Context->dbh;
565 my $sth = $dbh->prepare($query);
566 $sth->execute(@params);
571 #------------------------------------------------------------#
573 =head3 ModBasketHeader
575 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
577 Modifies a basket's header.
581 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
583 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
585 =item C<$note> is the "note" field in the "aqbasket" table;
587 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
589 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
591 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
593 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
595 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
597 =item C<$is_standing> is the "is_standing" field in the aqbasket table.
603 sub ModBasketHeader {
604 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing) = @_;
607 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?, is_standing=?
611 my $dbh = C4::Context->dbh;
612 my $sth = $dbh->prepare($query);
613 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $is_standing, $basketno);
615 if ( $contractnumber ) {
616 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
617 my $sth2 = $dbh->prepare($query2);
618 $sth2->execute($contractnumber,$basketno);
623 #------------------------------------------------------------#
625 =head3 GetBasketsByBookseller
627 @results = &GetBasketsByBookseller($booksellerid, $extra);
629 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
633 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
635 =item C<$extra> is the extra sql parameters, can be
637 $extra->{groupby}: group baskets by column
638 ex. $extra->{groupby} = aqbasket.basketgroupid
639 $extra->{orderby}: order baskets by column
640 $extra->{limit}: limit number of results (can be helpful for pagination)
646 sub GetBasketsByBookseller {
647 my ($booksellerid, $extra) = @_;
648 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
650 if ($extra->{groupby}) {
651 $query .= " GROUP by $extra->{groupby}";
653 if ($extra->{orderby}){
654 $query .= " ORDER by $extra->{orderby}";
656 if ($extra->{limit}){
657 $query .= " LIMIT $extra->{limit}";
660 my $dbh = C4::Context->dbh;
661 my $sth = $dbh->prepare($query);
662 $sth->execute($booksellerid);
663 return $sth->fetchall_arrayref({});
666 =head3 GetBasketsInfosByBookseller
668 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
670 The optional second parameter allbaskets is a boolean allowing you to
671 select all baskets from the supplier; by default only active baskets (open or
672 closed but still something to receive) are returned.
674 Returns in a arrayref of hashref all about booksellers baskets, plus:
675 total_biblios: Number of distinct biblios in basket
676 total_items: Number of items in basket
677 expected_items: Number of non-received items in basket
681 sub GetBasketsInfosByBookseller {
682 my ($supplierid, $allbaskets) = @_;
684 return unless $supplierid;
686 my $dbh = C4::Context->dbh;
689 SUM(aqorders.quantity) AS total_items,
691 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
692 ) AS total_items_cancelled,
693 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
695 IF(aqorders.datereceived IS NULL
696 AND aqorders.datecancellationprinted IS NULL
701 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
702 WHERE booksellerid = ?};
704 unless ( $allbaskets ) {
705 $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
707 $query.=" GROUP BY aqbasket.basketno";
709 my $sth = $dbh->prepare($query);
710 $sth->execute($supplierid);
711 my $baskets = $sth->fetchall_arrayref({});
713 # Retrieve the number of biblios cancelled
714 my $cancelled_biblios = $dbh->selectall_hashref( q|
715 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
717 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
718 WHERE booksellerid = ?
719 AND aqorders.orderstatus = 'cancelled'
720 GROUP BY aqbasket.basketno
721 |, 'basketno', {}, $supplierid );
723 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
729 =head3 GetBasketUsers
731 $basketusers_ids = &GetBasketUsers($basketno);
733 Returns a list of all borrowernumbers that are in basket users list
738 my $basketno = shift;
740 return unless $basketno;
743 SELECT borrowernumber
747 my $dbh = C4::Context->dbh;
748 my $sth = $dbh->prepare($query);
749 $sth->execute($basketno);
750 my $results = $sth->fetchall_arrayref( {} );
753 foreach (@$results) {
754 push @borrowernumbers, $_->{'borrowernumber'};
757 return @borrowernumbers;
760 =head3 ModBasketUsers
762 my @basketusers_ids = (1, 2, 3);
763 &ModBasketUsers($basketno, @basketusers_ids);
765 Delete all users from basket users list, and add users in C<@basketusers_ids>
771 my ($basketno, @basketusers_ids) = @_;
773 return unless $basketno;
775 my $dbh = C4::Context->dbh;
777 DELETE FROM aqbasketusers
780 my $sth = $dbh->prepare($query);
781 $sth->execute($basketno);
784 INSERT INTO aqbasketusers (basketno, borrowernumber)
787 $sth = $dbh->prepare($query);
788 foreach my $basketuser_id (@basketusers_ids) {
789 $sth->execute($basketno, $basketuser_id);
794 =head3 CanUserManageBasket
796 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
797 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
799 Check if a borrower can manage a basket, according to system preference
800 AcqViewBaskets, user permissions and basket properties (creator, users list,
803 First parameter can be either a borrowernumber or a hashref as returned by
804 C4::Members::GetMember.
806 Second parameter can be either a basketno or a hashref as returned by
807 C4::Acquisition::GetBasket.
809 The third parameter is optional. If given, it should be a hashref as returned
810 by C4::Auth::getuserflags. If not, getuserflags is called.
812 If user is authorised to manage basket, returns 1.
817 sub CanUserManageBasket {
818 my ($borrower, $basket, $userflags) = @_;
820 if (!ref $borrower) {
821 $borrower = C4::Members::GetMember(borrowernumber => $borrower);
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, GetBasketUsers($basketno)) == 0) {
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
1140 AND datecancellationprinted <> '0000-00-00')
1145 q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|;
1147 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1151 $query .= " ORDER BY $orderby";
1153 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1158 #------------------------------------------------------------#
1160 =head3 GetOrdersByBiblionumber
1162 @orders = &GetOrdersByBiblionumber($biblionumber);
1164 Looks up the orders with linked to a specific $biblionumber, including
1165 cancelled orders and received orders.
1168 C<@orders> is an array of references-to-hash, whose keys are the
1169 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1173 sub GetOrdersByBiblionumber {
1174 my $biblionumber = shift;
1175 return unless $biblionumber;
1176 my $dbh = C4::Context->dbh;
1178 SELECT biblio.*,biblioitems.*,
1182 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1183 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1184 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1185 WHERE aqorders.biblionumber=?
1188 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1189 return @{$result_set};
1193 #------------------------------------------------------------#
1197 $order = &GetOrder($ordernumber);
1199 Looks up an order by order number.
1201 Returns a reference-to-hash describing the order. The keys of
1202 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1207 my ($ordernumber) = @_;
1208 return unless $ordernumber;
1210 my $dbh = C4::Context->dbh;
1211 my $query = qq{SELECT
1215 aqbasket.basketname,
1216 borrowers.branchcode,
1217 biblioitems.publicationyear,
1218 biblio.copyrightdate,
1219 biblioitems.editionstatement,
1223 biblioitems.publishercode,
1224 aqorders.rrp AS unitpricesupplier,
1225 aqorders.ecost AS unitpricelib,
1226 aqorders.claims_count AS claims_count,
1227 aqorders.claimed_date AS claimed_date,
1228 aqbudgets.budget_name AS budget,
1229 aqbooksellers.name AS supplier,
1230 aqbooksellers.id AS supplierid,
1231 biblioitems.publishercode AS publisher,
1232 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1233 DATE(aqbasket.closedate) AS orderdate,
1234 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1235 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1236 DATEDIFF(CURDATE( ),closedate) AS latesince
1237 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1238 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1239 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1240 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1241 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1242 WHERE aqorders.basketno = aqbasket.basketno
1245 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1247 # result_set assumed to contain 1 match
1248 return $result_set->[0];
1251 =head3 GetLastOrderNotReceivedFromSubscriptionid
1253 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1255 Returns a reference-to-hash describing the last order not received for a subscription.
1259 sub GetLastOrderNotReceivedFromSubscriptionid {
1260 my ( $subscriptionid ) = @_;
1261 my $dbh = C4::Context->dbh;
1263 SELECT * FROM aqorders
1264 LEFT JOIN subscription
1265 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1266 WHERE aqorders.subscriptionid = ?
1267 AND aqorders.datereceived IS NULL
1271 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1273 # result_set assumed to contain 1 match
1274 return $result_set->[0];
1277 =head3 GetLastOrderReceivedFromSubscriptionid
1279 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1281 Returns a reference-to-hash describing the last order received for a subscription.
1285 sub GetLastOrderReceivedFromSubscriptionid {
1286 my ( $subscriptionid ) = @_;
1287 my $dbh = C4::Context->dbh;
1289 SELECT * FROM aqorders
1290 LEFT JOIN subscription
1291 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1292 WHERE aqorders.subscriptionid = ?
1293 AND aqorders.datereceived =
1295 SELECT MAX( aqorders.datereceived )
1297 LEFT JOIN subscription
1298 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1299 WHERE aqorders.subscriptionid = ?
1300 AND aqorders.datereceived IS NOT NULL
1302 ORDER BY ordernumber DESC
1306 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1308 # result_set assumed to contain 1 match
1309 return $result_set->[0];
1313 #------------------------------------------------------------#
1317 &ModOrder(\%hashref);
1319 Modifies an existing order. Updates the order with order number
1320 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1321 other keys of the hash update the fields with the same name in the aqorders
1322 table of the Koha database.
1327 my $orderinfo = shift;
1329 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '';
1331 my $dbh = C4::Context->dbh;
1334 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1335 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1337 # delete($orderinfo->{'branchcode'});
1338 # the hash contains a lot of entries not in aqorders, so get the columns ...
1339 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1341 my $colnames = $sth->{NAME};
1342 #FIXME Be careful. If aqorders would have columns with diacritics,
1343 #you should need to decode what you get back from NAME.
1344 #See report 10110 and guided_reports.pl
1345 my $query = "UPDATE aqorders SET ";
1347 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1348 # ... and skip hash entries that are not in the aqorders table
1349 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1350 next unless grep(/^$orderinfokey$/, @$colnames);
1351 $query .= "$orderinfokey=?, ";
1352 push(@params, $orderinfo->{$orderinfokey});
1355 $query .= "timestamp=NOW() WHERE ordernumber=?";
1356 push(@params, $orderinfo->{'ordernumber'} );
1357 $sth = $dbh->prepare($query);
1358 $sth->execute(@params);
1362 #------------------------------------------------------------#
1366 ModItemOrder($itemnumber, $ordernumber);
1368 Modifies the ordernumber of an item in aqorders_items.
1373 my ($itemnumber, $ordernumber) = @_;
1375 return unless ($itemnumber and $ordernumber);
1377 my $dbh = C4::Context->dbh;
1379 UPDATE aqorders_items
1381 WHERE itemnumber = ?
1383 my $sth = $dbh->prepare($query);
1384 return $sth->execute($ordernumber, $itemnumber);
1387 #------------------------------------------------------------#
1389 =head3 ModReceiveOrder
1391 my ( $date_received, $new_ordernumber ) = ModReceiveOrder(
1393 biblionumber => $biblionumber,
1395 quantityreceived => $quantityreceived,
1397 invoice => $invoice,
1398 budget_id => $budget_id,
1399 received_itemnumbers => \@received_itemnumbers,
1400 order_internalnote => $order_internalnote,
1404 Updates an order, to reflect the fact that it was received, at least
1407 If a partial order is received, splits the order into two.
1409 Updates the order with biblionumber C<$biblionumber> and ordernumber
1410 C<$order->{ordernumber}>.
1415 sub ModReceiveOrder {
1417 my $biblionumber = $params->{biblionumber};
1418 my $order = { %{ $params->{order} } }; # Copy the order, we don't want to modify it
1419 my $invoice = $params->{invoice};
1420 my $quantrec = $params->{quantityreceived};
1421 my $user = $params->{user};
1422 my $budget_id = $params->{budget_id};
1423 my $received_items = $params->{received_items};
1425 my $dbh = C4::Context->dbh;
1426 my $datereceived = ( $invoice and $invoice->{datereceived} ) ? $invoice->{datereceived} : dt_from_string;
1427 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1428 if ($suggestionid) {
1429 ModSuggestion( {suggestionid=>$suggestionid,
1430 STATUS=>'AVAILABLE',
1431 biblionumber=> $biblionumber}
1435 my $result_set = $dbh->selectrow_arrayref(
1436 q{SELECT aqbasket.is_standing
1438 WHERE basketno=?},{ Slice => {} }, $order->{basketno});
1439 my $is_standing = $result_set->[0]; # we assume we have a unique basket
1441 my $new_ordernumber = $order->{ordernumber};
1442 if ( $is_standing || $order->{quantity} > $quantrec ) {
1443 # Split order line in two parts: the first is the original order line
1444 # without received items (the quantity is decreased),
1445 # the second part is a new order line with quantity=quantityrec
1446 # (entirely received)
1450 orderstatus = 'partial'|;
1451 $query .= q|, order_internalnote = ?| if defined $order->{order_internalnote};
1452 $query .= q| WHERE ordernumber = ?|;
1453 my $sth = $dbh->prepare($query);
1456 ( $is_standing ? 1 : ($order->{quantity} - $quantrec) ),
1457 ( defined $order->{order_internalnote} ? $order->{order_internalnote} : () ),
1458 $order->{ordernumber}
1461 # Recalculate tax_value
1465 tax_value_on_ordering = quantity * ecost_tax_excluded * tax_rate_on_ordering,
1466 tax_value_on_receiving = quantity * unitprice_tax_excluded * tax_rate_on_receiving
1467 WHERE ordernumber = ?
1468 |, undef, $order->{ordernumber});
1470 delete $order->{ordernumber};
1471 $order->{budget_id} = ( $budget_id || $order->{budget_id} );
1472 $order->{quantity} = $quantrec;
1473 $order->{quantityreceived} = $quantrec;
1474 $order->{ecost_tax_excluded} //= 0;
1475 $order->{tax_rate_on_ordering} //= 0;
1476 $order->{unitprice_tax_excluded} //= 0;
1477 $order->{tax_rate_on_receiving} //= 0;
1478 $order->{tax_value_on_ordering} = $order->{quantity} * $order->{ecost_tax_excluded} * $order->{tax_rate_on_ordering};
1479 $order->{tax_value_on_receiving} = $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate_on_receiving};
1480 $order->{datereceived} = $datereceived;
1481 $order->{invoiceid} = $invoice->{invoiceid};
1482 $order->{orderstatus} = 'complete';
1483 $new_ordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber};
1485 if ($received_items) {
1486 foreach my $itemnumber (@$received_items) {
1487 ModItemOrder($itemnumber, $new_ordernumber);
1493 SET quantityreceived = ?,
1497 orderstatus = 'complete'
1501 , unitprice = ?, unitprice_tax_included = ?, unitprice_tax_excluded = ?
1502 | if defined $order->{unitprice};
1505 ,tax_value_on_receiving = ?
1506 | if defined $order->{tax_value_on_receiving};
1509 ,tax_rate_on_receiving = ?
1510 | if defined $order->{tax_rate_on_receiving};
1513 , order_internalnote = ?
1514 | if defined $order->{order_internalnote};
1516 $query .= q| where biblionumber=? and ordernumber=?|;
1518 my $sth = $dbh->prepare( $query );
1519 my @params = ( $quantrec, $datereceived, $invoice->{invoiceid}, ( $budget_id ? $budget_id : $order->{budget_id} ) );
1521 if ( defined $order->{unitprice} ) {
1522 push @params, $order->{unitprice}, $order->{unitprice_tax_included}, $order->{unitprice_tax_excluded};
1525 if ( defined $order->{tax_value_on_receiving} ) {
1526 push @params, $order->{tax_value_on_receiving};
1529 if ( defined $order->{tax_rate_on_receiving} ) {
1530 push @params, $order->{tax_rate_on_receiving};
1533 if ( defined $order->{order_internalnote} ) {
1534 push @params, $order->{order_internalnote};
1537 push @params, ( $biblionumber, $order->{ordernumber} );
1539 $sth->execute( @params );
1541 # All items have been received, sent a notification to users
1542 NotifyOrderUsers( $order->{ordernumber} );
1545 return ($datereceived, $new_ordernumber);
1548 =head3 CancelReceipt
1550 my $parent_ordernumber = CancelReceipt($ordernumber);
1552 Cancel an order line receipt and update the parent order line, as if no
1554 If items are created at receipt (AcqCreateItem = receiving) then delete
1560 my $ordernumber = shift;
1562 return unless $ordernumber;
1564 my $dbh = C4::Context->dbh;
1566 SELECT datereceived, parent_ordernumber, quantity
1568 WHERE ordernumber = ?
1570 my $sth = $dbh->prepare($query);
1571 $sth->execute($ordernumber);
1572 my $order = $sth->fetchrow_hashref;
1574 warn "CancelReceipt: order $ordernumber does not exist";
1577 unless($order->{'datereceived'}) {
1578 warn "CancelReceipt: order $ordernumber is not received";
1582 my $parent_ordernumber = $order->{'parent_ordernumber'};
1584 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1586 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1587 # The order line has no parent, just mark it as not received
1590 SET quantityreceived = ?,
1593 orderstatus = 'ordered'
1594 WHERE ordernumber = ?
1596 $sth = $dbh->prepare($query);
1597 $sth->execute(0, undef, undef, $ordernumber);
1598 _cancel_items_receipt( $ordernumber );
1600 # The order line has a parent, increase parent quantity and delete
1603 SELECT quantity, datereceived
1605 WHERE ordernumber = ?
1607 $sth = $dbh->prepare($query);
1608 $sth->execute($parent_ordernumber);
1609 my $parent_order = $sth->fetchrow_hashref;
1610 unless($parent_order) {
1611 warn "Parent order $parent_ordernumber does not exist.";
1614 if($parent_order->{'datereceived'}) {
1615 warn "CancelReceipt: parent order is received.".
1616 " Can't cancel receipt.";
1622 orderstatus = 'ordered'
1623 WHERE ordernumber = ?
1625 $sth = $dbh->prepare($query);
1626 my $rv = $sth->execute(
1627 $order->{'quantity'} + $parent_order->{'quantity'},
1631 warn "Cannot update parent order line, so do not cancel".
1636 # Recalculate tax_value
1640 tax_value_on_ordering = quantity * ecost_tax_excluded * tax_rate_on_ordering,
1641 tax_value_on_receiving = quantity * unitprice_tax_excluded * tax_rate_on_receiving
1642 WHERE ordernumber = ?
1643 |, undef, $parent_ordernumber);
1645 _cancel_items_receipt( $ordernumber, $parent_ordernumber );
1648 DELETE FROM aqorders
1649 WHERE ordernumber = ?
1651 $sth = $dbh->prepare($query);
1652 $sth->execute($ordernumber);
1656 if(C4::Context->preference('AcqCreateItem') eq 'ordering') {
1657 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1659 for my $in ( @itemnumbers ) {
1660 my $biblionumber = C4::Biblio::GetBiblionumberFromItemnumber( $in );
1661 my $frameworkcode = GetFrameworkCode($biblionumber);
1662 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber', $frameworkcode );
1663 my $item = C4::Items::GetMarcItem( $biblionumber, $in );
1664 for my $affect ( @affects ) {
1665 my ( $sf, $v ) = split q{=}, $affect, 2;
1666 foreach ( $item->field($itemfield) ) {
1667 $_->update( $sf => $v );
1670 C4::Items::ModItemFromMarc( $item, $biblionumber, $in );
1675 return $parent_ordernumber;
1678 sub _cancel_items_receipt {
1679 my ( $ordernumber, $parent_ordernumber ) = @_;
1680 $parent_ordernumber ||= $ordernumber;
1682 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1683 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1684 # Remove items that were created at receipt
1686 DELETE FROM items, aqorders_items
1687 USING items, aqorders_items
1688 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1690 my $dbh = C4::Context->dbh;
1691 my $sth = $dbh->prepare($query);
1692 foreach my $itemnumber (@itemnumbers) {
1693 $sth->execute($itemnumber, $itemnumber);
1697 foreach my $itemnumber (@itemnumbers) {
1698 ModItemOrder($itemnumber, $parent_ordernumber);
1703 #------------------------------------------------------------#
1707 @results = &SearchOrders({
1708 ordernumber => $ordernumber,
1711 booksellerid => $booksellerid,
1712 basketno => $basketno,
1713 basketname => $basketname,
1714 basketgroupname => $basketgroupname,
1718 biblionumber => $biblionumber,
1719 budget_id => $budget_id
1722 Searches for orders filtered by criteria.
1724 C<$ordernumber> Finds matching orders or transferred orders by ordernumber.
1725 C<$search> Finds orders matching %$search% in title, author, or isbn.
1726 C<$owner> Finds order for the logged in user.
1727 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1728 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1731 C<@results> is an array of references-to-hash with the keys are fields
1732 from aqorders, biblio, biblioitems and aqbasket tables.
1737 my ( $params ) = @_;
1738 my $ordernumber = $params->{ordernumber};
1739 my $search = $params->{search};
1740 my $ean = $params->{ean};
1741 my $booksellerid = $params->{booksellerid};
1742 my $basketno = $params->{basketno};
1743 my $basketname = $params->{basketname};
1744 my $basketgroupname = $params->{basketgroupname};
1745 my $owner = $params->{owner};
1746 my $pending = $params->{pending};
1747 my $ordered = $params->{ordered};
1748 my $biblionumber = $params->{biblionumber};
1749 my $budget_id = $params->{budget_id};
1751 my $dbh = C4::Context->dbh;
1754 SELECT aqbasket.basketno,
1756 borrowers.firstname,
1759 biblioitems.biblioitemnumber,
1760 biblioitems.publishercode,
1761 biblioitems.publicationyear,
1762 aqbasket.authorisedby,
1763 aqbasket.booksellerid,
1765 aqbasket.creationdate,
1766 aqbasket.basketname,
1767 aqbasketgroups.id as basketgroupid,
1768 aqbasketgroups.name as basketgroupname,
1771 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1772 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1773 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1774 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1775 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1778 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1780 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1784 WHERE (datecancellationprinted is NULL)
1787 if ( $pending or $ordered ) {
1790 ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) )
1792 ( quantity > quantityreceived OR quantityreceived is NULL )
1796 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1804 my $userenv = C4::Context->userenv;
1805 if ( C4::Context->preference("IndependentBranches") ) {
1806 unless ( C4::Context->IsSuperLibrarian() ) {
1809 borrowers.branchcode = ?
1810 OR borrowers.branchcode = ''
1813 push @args, $userenv->{branch};
1817 if ( $ordernumber ) {
1818 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1819 push @args, ( $ordernumber, $ordernumber );
1821 if ( $biblionumber ) {
1822 $query .= 'AND aqorders.biblionumber = ?';
1823 push @args, $biblionumber;
1826 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1827 push @args, ("%$search%","%$search%","%$search%");
1830 $query .= ' AND biblioitems.ean = ?';
1833 if ( $booksellerid ) {
1834 $query .= 'AND aqbasket.booksellerid = ?';
1835 push @args, $booksellerid;
1838 $query .= 'AND aqbasket.basketno = ?';
1839 push @args, $basketno;
1842 $query .= 'AND aqbasket.basketname LIKE ?';
1843 push @args, "%$basketname%";
1845 if( $basketgroupname ) {
1846 $query .= ' AND aqbasketgroups.name LIKE ?';
1847 push @args, "%$basketgroupname%";
1851 $query .= ' AND aqbasket.authorisedby=? ';
1852 push @args, $userenv->{'number'};
1856 $query .= ' AND aqorders.budget_id = ?';
1857 push @args, $budget_id;
1860 $query .= ' ORDER BY aqbasket.basketno';
1862 my $sth = $dbh->prepare($query);
1863 $sth->execute(@args);
1864 return $sth->fetchall_arrayref({});
1867 #------------------------------------------------------------#
1871 &DelOrder($biblionumber, $ordernumber);
1873 Cancel the order with the given order and biblio numbers. It does not
1874 delete any entries in the aqorders table, it merely marks them as
1880 my ( $bibnum, $ordernumber, $delete_biblio, $reason ) = @_;
1883 my $dbh = C4::Context->dbh;
1886 SET datecancellationprinted=now(), orderstatus='cancelled'
1889 $query .= ", cancellationreason = ? ";
1892 WHERE biblionumber=? AND ordernumber=?
1894 my $sth = $dbh->prepare($query);
1896 $sth->execute($reason, $bibnum, $ordernumber);
1898 $sth->execute( $bibnum, $ordernumber );
1902 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1903 foreach my $itemnumber (@itemnumbers){
1904 my $delcheck = C4::Items::DelItemCheck( $bibnum, $itemnumber );
1906 if($delcheck != 1) {
1907 $error->{'delitem'} = 1;
1911 if($delete_biblio) {
1912 # We get the number of remaining items
1913 my $biblio = Koha::Biblios->find( $bibnum );
1914 my $itemcount = $biblio->items->count;
1916 # If there are no items left,
1917 if ( $itemcount == 0 ) {
1918 # We delete the record
1919 my $delcheck = DelBiblio($bibnum);
1922 $error->{'delbiblio'} = 1;
1930 =head3 TransferOrder
1932 my $newordernumber = TransferOrder($ordernumber, $basketno);
1934 Transfer an order line to a basket.
1935 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1936 to BOOKSELLER on DATE' and create new order with internal note
1937 'Transferred from BOOKSELLER on DATE'.
1938 Move all attached items to the new order.
1939 Received orders cannot be transferred.
1940 Return the ordernumber of created order.
1945 my ($ordernumber, $basketno) = @_;
1947 return unless ($ordernumber and $basketno);
1949 my $order = GetOrder( $ordernumber );
1950 return if $order->{datereceived};
1951 my $basket = GetBasket($basketno);
1952 return unless $basket;
1954 my $dbh = C4::Context->dbh;
1955 my ($query, $sth, $rv);
1959 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1960 WHERE ordernumber = ?
1962 $sth = $dbh->prepare($query);
1963 $rv = $sth->execute('cancelled', $ordernumber);
1965 delete $order->{'ordernumber'};
1966 delete $order->{parent_ordernumber};
1967 $order->{'basketno'} = $basketno;
1969 my $newordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber};
1972 UPDATE aqorders_items
1974 WHERE ordernumber = ?
1976 $sth = $dbh->prepare($query);
1977 $sth->execute($newordernumber, $ordernumber);
1980 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1983 $sth = $dbh->prepare($query);
1984 $sth->execute($ordernumber, $newordernumber);
1986 return $newordernumber;
1989 =head2 FUNCTIONS ABOUT PARCELS
1993 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1995 get a lists of parcels.
2002 is the bookseller this function has to get parcels.
2005 To know on what criteria the results list has to be ordered.
2008 is the booksellerinvoicenumber.
2010 =item $datefrom & $dateto
2011 to know on what date this function has to filter its search.
2016 a pointer on a hash list containing parcel informations as such :
2022 =item Last operation
2024 =item Number of biblio
2026 =item Number of items
2033 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
2034 my $dbh = C4::Context->dbh;
2035 my @query_params = ();
2037 SELECT aqinvoices.invoicenumber,
2038 datereceived,purchaseordernumber,
2039 count(DISTINCT biblionumber) AS biblio,
2040 sum(quantity) AS itemsexpected,
2041 sum(quantityreceived) AS itemsreceived
2042 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
2043 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2044 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
2046 push @query_params, $bookseller;
2048 if ( defined $code ) {
2049 $strsth .= ' and aqinvoices.invoicenumber like ? ';
2050 # add a % to the end of the code to allow stemming.
2051 push @query_params, "$code%";
2054 if ( defined $datefrom ) {
2055 $strsth .= ' and datereceived >= ? ';
2056 push @query_params, $datefrom;
2059 if ( defined $dateto ) {
2060 $strsth .= 'and datereceived <= ? ';
2061 push @query_params, $dateto;
2064 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
2066 # can't use a placeholder to place this column name.
2067 # but, we could probably be checking to make sure it is a column that will be fetched.
2068 $strsth .= "order by $order " if ($order);
2070 my $sth = $dbh->prepare($strsth);
2072 $sth->execute( @query_params );
2073 my $results = $sth->fetchall_arrayref({});
2077 #------------------------------------------------------------#
2079 =head3 GetLateOrders
2081 @results = &GetLateOrders;
2083 Searches for bookseller with late orders.
2086 the table of supplier with late issues. This table is full of hashref.
2092 my $supplierid = shift;
2094 my $estimateddeliverydatefrom = shift;
2095 my $estimateddeliverydateto = shift;
2097 my $dbh = C4::Context->dbh;
2099 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2100 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2102 my @query_params = ();
2104 SELECT aqbasket.basketno,
2105 aqorders.ordernumber,
2106 DATE(aqbasket.closedate) AS orderdate,
2107 aqbasket.basketname AS basketname,
2108 aqbasket.basketgroupid AS basketgroupid,
2109 aqbasketgroups.name AS basketgroupname,
2110 aqorders.rrp AS unitpricesupplier,
2111 aqorders.ecost AS unitpricelib,
2112 aqorders.claims_count AS claims_count,
2113 aqorders.claimed_date AS claimed_date,
2114 aqbudgets.budget_name AS budget,
2115 borrowers.branchcode AS branch,
2116 aqbooksellers.name AS supplier,
2117 aqbooksellers.id AS supplierid,
2118 biblio.author, biblio.title,
2119 biblioitems.publishercode AS publisher,
2120 biblioitems.publicationyear,
2121 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2125 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2126 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2127 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2128 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2129 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2130 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2131 WHERE aqorders.basketno = aqbasket.basketno
2132 AND ( datereceived = ''
2133 OR datereceived IS NULL
2134 OR aqorders.quantityreceived < aqorders.quantity
2136 AND aqbasket.closedate IS NOT NULL
2137 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
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 $having = "HAVING quantity <> 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;
2163 if (defined $supplierid) {
2164 $from .= ' AND aqbasket.booksellerid = ? ';
2165 push @query_params, $supplierid;
2167 if (defined $branch) {
2168 $from .= ' AND borrowers.branchcode LIKE ? ';
2169 push @query_params, $branch;
2172 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2173 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2175 if ( defined $estimateddeliverydatefrom ) {
2176 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2177 push @query_params, $estimateddeliverydatefrom;
2179 if ( defined $estimateddeliverydateto ) {
2180 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2181 push @query_params, $estimateddeliverydateto;
2183 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2184 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2186 if (C4::Context->preference("IndependentBranches")
2187 && !C4::Context->IsSuperLibrarian() ) {
2188 $from .= ' AND borrowers.branchcode LIKE ? ';
2189 push @query_params, C4::Context->userenv->{branch};
2191 $from .= " AND orderstatus <> 'cancelled' ";
2192 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2193 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2194 my $sth = $dbh->prepare($query);
2195 $sth->execute(@query_params);
2197 while (my $data = $sth->fetchrow_hashref) {
2198 push @results, $data;
2203 #------------------------------------------------------------#
2207 \@order_loop = GetHistory( %params );
2209 Retreives some acquisition history information
2219 basket - search both basket name and number
2220 booksellerinvoicenumber
2223 orderstatus (note that orderstatus '' will retrieve orders
2224 of any status except cancelled)
2226 get_canceled_order (if set to a true value, cancelled orders will
2230 $order_loop is a list of hashrefs that each look like this:
2232 'author' => 'Twain, Mark',
2234 'biblionumber' => '215',
2236 'creationdate' => 'MM/DD/YYYY',
2237 'datereceived' => undef,
2240 'invoicenumber' => undef,
2242 'ordernumber' => '1',
2244 'quantityreceived' => undef,
2245 'title' => 'The Adventures of Huckleberry Finn'
2251 # don't run the query if there are no parameters (list would be too long for sure !)
2252 croak "No search params" unless @_;
2254 my $title = $params{title};
2255 my $author = $params{author};
2256 my $isbn = $params{isbn};
2257 my $ean = $params{ean};
2258 my $name = $params{name};
2259 my $from_placed_on = $params{from_placed_on};
2260 my $to_placed_on = $params{to_placed_on};
2261 my $basket = $params{basket};
2262 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2263 my $basketgroupname = $params{basketgroupname};
2264 my $budget = $params{budget};
2265 my $orderstatus = $params{orderstatus};
2266 my $biblionumber = $params{biblionumber};
2267 my $get_canceled_order = $params{get_canceled_order} || 0;
2268 my $ordernumber = $params{ordernumber};
2269 my $search_children_too = $params{search_children_too} || 0;
2270 my $created_by = $params{created_by} || [];
2274 my $total_qtyreceived = 0;
2275 my $total_price = 0;
2277 my $dbh = C4::Context->dbh;
2280 COALESCE(biblio.title, deletedbiblio.title) AS title,
2281 COALESCE(biblio.author, deletedbiblio.author) AS author,
2282 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2283 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2285 aqbasket.basketname,
2286 aqbasket.basketgroupid,
2287 aqbasket.authorisedby,
2288 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2289 aqbasketgroups.name as groupname,
2291 aqbasket.creationdate,
2292 aqorders.datereceived,
2294 aqorders.quantityreceived,
2296 aqorders.ordernumber,
2298 aqinvoices.invoicenumber,
2299 aqbooksellers.id as id,
2300 aqorders.biblionumber,
2301 aqorders.orderstatus,
2302 aqorders.parent_ordernumber,
2303 aqbudgets.budget_name
2305 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2308 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2309 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2310 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2311 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2312 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2313 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2314 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2315 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2316 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2317 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2320 $query .= " WHERE 1 ";
2322 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2323 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2326 my @query_params = ();
2328 if ( $biblionumber ) {
2329 $query .= " AND biblio.biblionumber = ?";
2330 push @query_params, $biblionumber;
2334 $query .= " AND biblio.title LIKE ? ";
2335 $title =~ s/\s+/%/g;
2336 push @query_params, "%$title%";
2340 $query .= " AND biblio.author LIKE ? ";
2341 push @query_params, "%$author%";
2345 $query .= " AND biblioitems.isbn LIKE ? ";
2346 push @query_params, "%$isbn%";
2349 $query .= " AND biblioitems.ean = ? ";
2350 push @query_params, "$ean";
2353 $query .= " AND aqbooksellers.name LIKE ? ";
2354 push @query_params, "%$name%";
2358 $query .= " AND aqbudgets.budget_id = ? ";
2359 push @query_params, "$budget";
2362 if ( $from_placed_on ) {
2363 $query .= " AND creationdate >= ? ";
2364 push @query_params, $from_placed_on;
2367 if ( $to_placed_on ) {
2368 $query .= " AND creationdate <= ? ";
2369 push @query_params, $to_placed_on;
2372 if ( defined $orderstatus and $orderstatus ne '') {
2373 $query .= " AND aqorders.orderstatus = ? ";
2374 push @query_params, "$orderstatus";
2378 if ($basket =~ m/^\d+$/) {
2379 $query .= " AND aqorders.basketno = ? ";
2380 push @query_params, $basket;
2382 $query .= " AND aqbasket.basketname LIKE ? ";
2383 push @query_params, "%$basket%";
2387 if ($booksellerinvoicenumber) {
2388 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2389 push @query_params, "%$booksellerinvoicenumber%";
2392 if ($basketgroupname) {
2393 $query .= " AND aqbasketgroups.name LIKE ? ";
2394 push @query_params, "%$basketgroupname%";
2398 $query .= " AND (aqorders.ordernumber = ? ";
2399 push @query_params, $ordernumber;
2400 if ($search_children_too) {
2401 $query .= " OR aqorders.parent_ordernumber = ? ";
2402 push @query_params, $ordernumber;
2407 if ( @$created_by ) {
2408 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2409 push @query_params, @$created_by;
2413 if ( C4::Context->preference("IndependentBranches") ) {
2414 unless ( C4::Context->IsSuperLibrarian() ) {
2415 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2416 push @query_params, C4::Context->userenv->{branch};
2419 $query .= " ORDER BY id";
2421 return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2424 =head2 GetRecentAcqui
2426 $results = GetRecentAcqui($days);
2428 C<$results> is a ref to a table which containts hashref
2432 sub GetRecentAcqui {
2434 my $dbh = C4::Context->dbh;
2438 ORDER BY timestamp DESC
2441 my $sth = $dbh->prepare($query);
2443 my $results = $sth->fetchall_arrayref({});
2447 #------------------------------------------------------------#
2451 &AddClaim($ordernumber);
2453 Add a claim for an order
2458 my ($ordernumber) = @_;
2459 my $dbh = C4::Context->dbh;
2462 claims_count = claims_count + 1,
2463 claimed_date = CURDATE()
2464 WHERE ordernumber = ?
2466 my $sth = $dbh->prepare($query);
2467 $sth->execute($ordernumber);
2472 my @invoices = GetInvoices(
2473 invoicenumber => $invoicenumber,
2474 supplierid => $supplierid,
2475 suppliername => $suppliername,
2476 shipmentdatefrom => $shipmentdatefrom, # ISO format
2477 shipmentdateto => $shipmentdateto, # ISO format
2478 billingdatefrom => $billingdatefrom, # ISO format
2479 billingdateto => $billingdateto, # ISO format
2480 isbneanissn => $isbn_or_ean_or_issn,
2483 publisher => $publisher,
2484 publicationyear => $publicationyear,
2485 branchcode => $branchcode,
2486 order_by => $order_by
2489 Return a list of invoices that match all given criteria.
2491 $order_by is "column_name (asc|desc)", where column_name is any of
2492 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2493 'shipmentcost', 'shipmentcost_budgetid'.
2495 asc is the default if omitted
2502 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2503 closedate shipmentcost shipmentcost_budgetid);
2505 my $dbh = C4::Context->dbh;
2507 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2510 aqorders.datereceived IS NOT NULL,
2511 aqorders.biblionumber,
2514 ) AS receivedbiblios,
2517 aqorders.subscriptionid IS NOT NULL,
2518 aqorders.subscriptionid,
2521 ) AS is_linked_to_subscriptions,
2522 SUM(aqorders.quantityreceived) AS receiveditems
2524 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2525 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2526 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2527 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2528 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2529 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2530 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2535 if($args{supplierid}) {
2536 push @bind_strs, " aqinvoices.booksellerid = ? ";
2537 push @bind_args, $args{supplierid};
2539 if($args{invoicenumber}) {
2540 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2541 push @bind_args, "%$args{invoicenumber}%";
2543 if($args{suppliername}) {
2544 push @bind_strs, " aqbooksellers.name LIKE ? ";
2545 push @bind_args, "%$args{suppliername}%";
2547 if($args{shipmentdatefrom}) {
2548 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2549 push @bind_args, $args{shipmentdatefrom};
2551 if($args{shipmentdateto}) {
2552 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2553 push @bind_args, $args{shipmentdateto};
2555 if($args{billingdatefrom}) {
2556 push @bind_strs, " aqinvoices.billingdate >= ? ";
2557 push @bind_args, $args{billingdatefrom};
2559 if($args{billingdateto}) {
2560 push @bind_strs, " aqinvoices.billingdate <= ? ";
2561 push @bind_args, $args{billingdateto};
2563 if($args{isbneanissn}) {
2564 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2565 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2568 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2569 push @bind_args, $args{title};
2572 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2573 push @bind_args, $args{author};
2575 if($args{publisher}) {
2576 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2577 push @bind_args, $args{publisher};
2579 if($args{publicationyear}) {
2580 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2581 push @bind_args, $args{publicationyear}, $args{publicationyear};
2583 if($args{branchcode}) {
2584 push @bind_strs, " borrowers.branchcode = ? ";
2585 push @bind_args, $args{branchcode};
2587 if($args{message_id}) {
2588 push @bind_strs, " aqinvoices.message_id = ? ";
2589 push @bind_args, $args{message_id};
2592 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2593 $query .= " GROUP BY aqinvoices.invoiceid ";
2595 if($args{order_by}) {
2596 my ($column, $direction) = split / /, $args{order_by};
2597 if(grep /^$column$/, @columns) {
2598 $direction ||= 'ASC';
2599 $query .= " ORDER BY $column $direction";
2603 my $sth = $dbh->prepare($query);
2604 $sth->execute(@bind_args);
2606 my $results = $sth->fetchall_arrayref({});
2612 my $invoice = GetInvoice($invoiceid);
2614 Get informations about invoice with given $invoiceid
2616 Return a hash filled with aqinvoices.* fields
2621 my ($invoiceid) = @_;
2624 return unless $invoiceid;
2626 my $dbh = C4::Context->dbh;
2632 my $sth = $dbh->prepare($query);
2633 $sth->execute($invoiceid);
2635 $invoice = $sth->fetchrow_hashref;
2639 =head3 GetInvoiceDetails
2641 my $invoice = GetInvoiceDetails($invoiceid)
2643 Return informations about an invoice + the list of related order lines
2645 Orders informations are in $invoice->{orders} (array ref)
2649 sub GetInvoiceDetails {
2650 my ($invoiceid) = @_;
2652 if ( !defined $invoiceid ) {
2653 carp 'GetInvoiceDetails called without an invoiceid';
2657 my $dbh = C4::Context->dbh;
2659 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2661 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2664 my $sth = $dbh->prepare($query);
2665 $sth->execute($invoiceid);
2667 my $invoice = $sth->fetchrow_hashref;
2672 biblio.copyrightdate,
2674 biblioitems.publishercode,
2675 biblioitems.publicationyear,
2676 aqbasket.basketname,
2677 aqbasketgroups.id AS basketgroupid,
2678 aqbasketgroups.name AS basketgroupname
2680 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2681 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2682 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2683 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2686 $sth = $dbh->prepare($query);
2687 $sth->execute($invoiceid);
2688 $invoice->{orders} = $sth->fetchall_arrayref({});
2689 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2696 my $invoiceid = AddInvoice(
2697 invoicenumber => $invoicenumber,
2698 booksellerid => $booksellerid,
2699 shipmentdate => $shipmentdate,
2700 billingdate => $billingdate,
2701 closedate => $closedate,
2702 shipmentcost => $shipmentcost,
2703 shipmentcost_budgetid => $shipmentcost_budgetid
2706 Create a new invoice and return its id or undef if it fails.
2713 return unless(%invoice and $invoice{invoicenumber});
2715 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2716 closedate shipmentcost shipmentcost_budgetid message_id);
2720 foreach my $key (keys %invoice) {
2721 if(0 < grep(/^$key$/, @columns)) {
2722 push @set_strs, "$key = ?";
2723 push @set_args, ($invoice{$key} || undef);
2729 my $dbh = C4::Context->dbh;
2730 my $query = "INSERT INTO aqinvoices SET ";
2731 $query .= join (",", @set_strs);
2732 my $sth = $dbh->prepare($query);
2733 $rv = $sth->execute(@set_args);
2735 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2744 invoiceid => $invoiceid, # Mandatory
2745 invoicenumber => $invoicenumber,
2746 booksellerid => $booksellerid,
2747 shipmentdate => $shipmentdate,
2748 billingdate => $billingdate,
2749 closedate => $closedate,
2750 shipmentcost => $shipmentcost,
2751 shipmentcost_budgetid => $shipmentcost_budgetid
2754 Modify an invoice, invoiceid is mandatory.
2756 Return undef if it fails.
2763 return unless(%invoice and $invoice{invoiceid});
2765 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2766 closedate shipmentcost shipmentcost_budgetid);
2770 foreach my $key (keys %invoice) {
2771 if(0 < grep(/^$key$/, @columns)) {
2772 push @set_strs, "$key = ?";
2773 push @set_args, ($invoice{$key} || undef);
2777 my $dbh = C4::Context->dbh;
2778 my $query = "UPDATE aqinvoices SET ";
2779 $query .= join(",", @set_strs);
2780 $query .= " WHERE invoiceid = ?";
2782 my $sth = $dbh->prepare($query);
2783 $sth->execute(@set_args, $invoice{invoiceid});
2788 CloseInvoice($invoiceid);
2792 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2797 my ($invoiceid) = @_;
2799 return unless $invoiceid;
2801 my $dbh = C4::Context->dbh;
2804 SET closedate = CAST(NOW() AS DATE)
2807 my $sth = $dbh->prepare($query);
2808 $sth->execute($invoiceid);
2811 =head3 ReopenInvoice
2813 ReopenInvoice($invoiceid);
2817 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => output_pref({ dt=>dt_from_string, dateonly=>1, otputpref=>'iso' }))
2822 my ($invoiceid) = @_;
2824 return unless $invoiceid;
2826 my $dbh = C4::Context->dbh;
2829 SET closedate = NULL
2832 my $sth = $dbh->prepare($query);
2833 $sth->execute($invoiceid);
2838 DelInvoice($invoiceid);
2840 Delete an invoice if there are no items attached to it.
2845 my ($invoiceid) = @_;
2847 return unless $invoiceid;
2849 my $dbh = C4::Context->dbh;
2855 my $sth = $dbh->prepare($query);
2856 $sth->execute($invoiceid);
2857 my $res = $sth->fetchrow_arrayref;
2858 if ( $res && $res->[0] == 0 ) {
2860 DELETE FROM aqinvoices
2863 my $sth = $dbh->prepare($query);
2864 return ( $sth->execute($invoiceid) > 0 );
2869 =head3 MergeInvoices
2871 MergeInvoices($invoiceid, \@sourceids);
2873 Merge the invoices identified by the IDs in \@sourceids into
2874 the invoice identified by $invoiceid.
2879 my ($invoiceid, $sourceids) = @_;
2881 return unless $invoiceid;
2882 foreach my $sourceid (@$sourceids) {
2883 next if $sourceid == $invoiceid;
2884 my $source = GetInvoiceDetails($sourceid);
2885 foreach my $order (@{$source->{'orders'}}) {
2886 $order->{'invoiceid'} = $invoiceid;
2889 DelInvoice($source->{'invoiceid'});
2894 =head3 GetBiblioCountByBasketno
2896 $biblio_count = &GetBiblioCountByBasketno($basketno);
2898 Looks up the biblio's count that has basketno value $basketno
2904 sub GetBiblioCountByBasketno {
2905 my ($basketno) = @_;
2906 my $dbh = C4::Context->dbh;
2908 SELECT COUNT( DISTINCT( biblionumber ) )
2911 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2914 my $sth = $dbh->prepare($query);
2915 $sth->execute($basketno);
2916 return $sth->fetchrow;
2919 # Note this subroutine should be moved to Koha::Acquisition::Order
2920 # Will do when a DBIC decision will be taken.
2921 sub populate_order_with_prices {
2924 my $order = $params->{order};
2925 my $booksellerid = $params->{booksellerid};
2926 return unless $booksellerid;
2928 my $bookseller = Koha::Acquisition::Booksellers->find( $booksellerid );
2930 my $receiving = $params->{receiving};
2931 my $ordering = $params->{ordering};
2932 my $discount = $order->{discount};
2933 $discount /= 100 if $discount > 1;
2936 $order->{tax_rate_on_ordering} //= $order->{tax_rate};
2937 if ( $bookseller->listincgst ) {
2938 # The user entered the rrp tax included
2939 $order->{rrp_tax_included} = $order->{rrp};
2941 # rrp tax excluded = rrp tax included / ( 1 + tax rate )
2942 $order->{rrp_tax_excluded} = $order->{rrp_tax_included} / ( 1 + $order->{tax_rate_on_ordering} );
2944 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
2945 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
2947 # ecost tax included = rrp tax included ( 1 - discount )
2948 $order->{ecost_tax_included} = $order->{rrp_tax_included} * ( 1 - $discount );
2951 # The user entered the rrp tax excluded
2952 $order->{rrp_tax_excluded} = $order->{rrp};
2954 # rrp tax included = rrp tax excluded * ( 1 - tax rate )
2955 $order->{rrp_tax_included} = $order->{rrp_tax_excluded} * ( 1 + $order->{tax_rate_on_ordering} );
2957 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
2958 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
2960 # ecost tax included = rrp tax excluded * ( 1 - tax rate ) * ( 1 - discount )
2961 $order->{ecost_tax_included} =
2962 $order->{rrp_tax_excluded} *
2963 ( 1 + $order->{tax_rate_on_ordering} ) *
2967 # tax value = quantity * ecost tax excluded * tax rate
2968 $order->{tax_value_on_ordering} =
2969 $order->{quantity} * $order->{ecost_tax_excluded} * $order->{tax_rate_on_ordering};
2973 $order->{tax_rate_on_receiving} //= $order->{tax_rate};
2974 if ( $bookseller->invoiceincgst ) {
2975 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
2976 # we need to keep the exact ecost value
2977 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_included} )->round ) {
2978 $order->{unitprice} = $order->{ecost_tax_included};
2981 # The user entered the unit price tax included
2982 $order->{unitprice_tax_included} = $order->{unitprice};
2984 # unit price tax excluded = unit price tax included / ( 1 + tax rate )
2985 $order->{unitprice_tax_excluded} = $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate_on_receiving} );
2988 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
2989 # we need to keep the exact ecost value
2990 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_excluded} )->round ) {
2991 $order->{unitprice} = $order->{ecost_tax_excluded};
2994 # The user entered the unit price tax excluded
2995 $order->{unitprice_tax_excluded} = $order->{unitprice};
2998 # unit price tax included = unit price tax included * ( 1 + tax rate )
2999 $order->{unitprice_tax_included} = $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate_on_receiving} );
3002 # tax value = quantity * unit price tax excluded * tax rate
3003 $order->{tax_value_on_receiving} = $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate_on_receiving};
3009 =head3 GetOrderUsers
3011 $order_users_ids = &GetOrderUsers($ordernumber);
3013 Returns a list of all borrowernumbers that are in order users list
3018 my ($ordernumber) = @_;
3020 return unless $ordernumber;
3023 SELECT borrowernumber
3025 WHERE ordernumber = ?
3027 my $dbh = C4::Context->dbh;
3028 my $sth = $dbh->prepare($query);
3029 $sth->execute($ordernumber);
3030 my $results = $sth->fetchall_arrayref( {} );
3032 my @borrowernumbers;
3033 foreach (@$results) {
3034 push @borrowernumbers, $_->{'borrowernumber'};
3037 return @borrowernumbers;
3040 =head3 ModOrderUsers
3042 my @order_users_ids = (1, 2, 3);
3043 &ModOrderUsers($ordernumber, @basketusers_ids);
3045 Delete all users from order users list, and add users in C<@order_users_ids>
3051 my ( $ordernumber, @order_users_ids ) = @_;
3053 return unless $ordernumber;
3055 my $dbh = C4::Context->dbh;
3057 DELETE FROM aqorder_users
3058 WHERE ordernumber = ?
3060 my $sth = $dbh->prepare($query);
3061 $sth->execute($ordernumber);
3064 INSERT INTO aqorder_users (ordernumber, borrowernumber)
3067 $sth = $dbh->prepare($query);
3068 foreach my $order_user_id (@order_users_ids) {
3069 $sth->execute( $ordernumber, $order_user_id );
3073 sub NotifyOrderUsers {
3074 my ($ordernumber) = @_;
3076 my @borrowernumbers = GetOrderUsers($ordernumber);
3077 return unless @borrowernumbers;
3079 my $order = GetOrder( $ordernumber );
3080 for my $borrowernumber (@borrowernumbers) {
3081 my $borrower = C4::Members::GetMember( borrowernumber => $borrowernumber );
3082 my $library = Koha::Libraries->find( $borrower->{branchcode} )->unblessed;
3083 my $biblio = C4::Biblio::GetBiblio( $order->{biblionumber} );
3084 my $letter = C4::Letters::GetPreparedLetter(
3085 module => 'acquisition',
3086 letter_code => 'ACQ_NOTIF_ON_RECEIV',
3087 branchcode => $library->{branchcode},
3088 lang => $borrower->{lang},
3090 'branches' => $library,
3091 'borrowers' => $borrower,
3092 'biblio' => $biblio,
3093 'aqorders' => $order,
3097 C4::Letters::EnqueueLetter(
3100 borrowernumber => $borrowernumber,
3101 LibraryName => C4::Context->preference("LibraryName"),
3102 message_transport_type => 'email',
3104 ) or warn "can't enqueue letter $letter";
3109 =head3 FillWithDefaultValues
3111 FillWithDefaultValues( $marc_record );
3113 This will update the record with default value defined in the ACQ framework.
3114 For all existing fields, if a default value exists and there are no subfield, it will be created.
3115 If the field does not exist, it will be created too.
3119 sub FillWithDefaultValues {
3121 my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ', { unsafe => 1 } );
3124 C4::Biblio::GetMarcFromKohaField( 'items.itemnumber', '' );
3125 for my $tag ( sort keys %$tagslib ) {
3127 next if $tag == $itemfield;
3128 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
3129 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
3130 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
3131 if ( defined $defaultvalue and $defaultvalue ne '' ) {
3132 my @fields = $record->field($tag);
3134 for my $field (@fields) {
3135 unless ( defined $field->subfield($subfield) ) {
3136 $field->add_subfields(
3137 $subfield => $defaultvalue );
3142 $record->insert_fields_ordered(
3144 $tag, '', '', $subfield => $defaultvalue
3159 Koha Development Team <http://koha-community.org/>