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::Bookseller;
33 use Koha::Number::Price;
43 use vars qw(@ISA @EXPORT);
49 &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
50 &GetBasketAsCSV &GetBasketGroupAsCSV
51 &GetBasketsByBookseller &GetBasketsByBasketgroup
52 &GetBasketsInfosByBookseller
54 &GetBasketUsers &ModBasketUsers
59 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
60 &GetBasketgroups &ReOpenBasketgroup
62 &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
63 &GetLateOrders &GetOrderFromItemnumber
64 &SearchOrders &GetHistory &GetRecentAcqui
65 &ModReceiveOrder &CancelReceipt
67 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
82 &GetItemnumbersFromOrder
85 &GetBiblioCountByBasketno
91 &FillWithDefaultValues
99 sub GetOrderFromItemnumber {
100 my ($itemnumber) = @_;
101 my $dbh = C4::Context->dbh;
104 SELECT * from aqorders LEFT JOIN aqorders_items
105 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
106 WHERE itemnumber = ? |;
108 my $sth = $dbh->prepare($query);
112 $sth->execute($itemnumber);
114 my $order = $sth->fetchrow_hashref;
119 # Returns the itemnumber(s) associated with the ordernumber given in parameter
120 sub GetItemnumbersFromOrder {
121 my ($ordernumber) = @_;
122 my $dbh = C4::Context->dbh;
123 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
124 my $sth = $dbh->prepare($query);
125 $sth->execute($ordernumber);
128 while (my $order = $sth->fetchrow_hashref) {
129 push @tab, $order->{'itemnumber'};
143 C4::Acquisition - Koha functions for dealing with orders and acquisitions
151 The functions in this module deal with acquisitions, managing book
152 orders, basket and parcels.
156 =head2 FUNCTIONS ABOUT BASKETS
160 $aqbasket = &GetBasket($basketnumber);
162 get all basket informations in aqbasket for a given basket
164 B<returns:> informations for a given basket returned as a hashref.
170 my $dbh = C4::Context->dbh;
173 concat( b.firstname,' ',b.surname) AS authorisedbyname
175 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
178 my $sth=$dbh->prepare($query);
179 $sth->execute($basketno);
180 my $basket = $sth->fetchrow_hashref;
184 #------------------------------------------------------------#
188 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
189 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace, $is_standing );
191 Create a new basket in aqbasket table
195 =item C<$booksellerid> is a foreign key in the aqbasket table
197 =item C<$authorizedby> is the username of who created the basket
201 The other parameters are optional, see ModBasketHeader for more info on them.
206 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
207 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
208 $billingplace, $is_standing ) = @_;
209 my $dbh = C4::Context->dbh;
211 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
212 . 'VALUES (now(),?,?)';
213 $dbh->do( $query, {}, $booksellerid, $authorisedby );
215 my $basket = $dbh->{mysql_insertid};
216 $basketname ||= q{}; # default to empty strings
218 $basketbooksellernote ||= q{};
219 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
220 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing );
224 #------------------------------------------------------------#
228 &CloseBasket($basketno);
230 close a basket (becomes unmodifiable, except for receives)
236 my $dbh = C4::Context->dbh;
237 $dbh->do('UPDATE aqbasket SET closedate=now() WHERE basketno=?', {}, $basketno );
239 $dbh->do( q{UPDATE aqorders SET orderstatus = 'ordered' WHERE basketno = ? AND orderstatus != 'complete'},
246 &ReopenBasket($basketno);
254 my $dbh = C4::Context->dbh;
255 $dbh->do( q{UPDATE aqbasket SET closedate=NULL WHERE basketno=?}, {}, $basketno );
259 SET orderstatus = 'new'
261 AND orderstatus != 'complete'
266 #------------------------------------------------------------#
268 =head3 GetBasketAsCSV
270 &GetBasketAsCSV($basketno);
272 Export a basket as CSV
274 $cgi parameter is needed for column name translation
279 my ($basketno, $cgi) = @_;
280 my $basket = GetBasket($basketno);
281 my @orders = GetOrders($basketno);
282 my $contract = GetContract({
283 contractnumber => $basket->{'contractnumber'}
286 my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
289 foreach my $order (@orders) {
290 my $bd = GetBiblioData( $order->{'biblionumber'} );
292 contractname => $contract->{'contractname'},
293 ordernumber => $order->{'ordernumber'},
294 entrydate => $order->{'entrydate'},
295 isbn => $order->{'isbn'},
296 author => $bd->{'author'},
297 title => $bd->{'title'},
298 publicationyear => $bd->{'publicationyear'},
299 publishercode => $bd->{'publishercode'},
300 collectiontitle => $bd->{'collectiontitle'},
301 notes => $order->{'order_vendornote'},
302 quantity => $order->{'quantity'},
303 rrp => $order->{'rrp'},
305 for my $place ( qw( deliveryplace billingplace ) ) {
306 if ( my $library = Koha::Libraries->find( $row->{deliveryplace} ) ) {
307 $row->{$place} = $library->branchname
311 contractname author title publishercode collectiontitle notes
312 deliveryplace billingplace
314 # Double the quotes to not be interpreted as a field end
315 $row->{$_} =~ s/"/""/g if $row->{$_};
321 if(defined $a->{publishercode} and defined $b->{publishercode}) {
322 $a->{publishercode} cmp $b->{publishercode};
326 $template->param(rows => \@rows);
328 return $template->output;
332 =head3 GetBasketGroupAsCSV
334 &GetBasketGroupAsCSV($basketgroupid);
336 Export a basket group as CSV
338 $cgi parameter is needed for column name translation
342 sub GetBasketGroupAsCSV {
343 my ($basketgroupid, $cgi) = @_;
344 my $baskets = GetBasketsByBasketgroup($basketgroupid);
346 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
349 for my $basket (@$baskets) {
350 my @orders = GetOrders( $basket->{basketno} );
351 my $contract = GetContract({
352 contractnumber => $basket->{contractnumber}
354 my $bookseller = Koha::Acquisition::Bookseller->fetch({ id => $basket->{booksellerid} });
355 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
357 foreach my $order (@orders) {
358 my $bd = GetBiblioData( $order->{'biblionumber'} );
360 clientnumber => $bookseller->{accountnumber},
361 basketname => $basket->{basketname},
362 ordernumber => $order->{ordernumber},
363 author => $bd->{author},
364 title => $bd->{title},
365 publishercode => $bd->{publishercode},
366 publicationyear => $bd->{publicationyear},
367 collectiontitle => $bd->{collectiontitle},
368 isbn => $order->{isbn},
369 quantity => $order->{quantity},
370 rrp_tax_included => $order->{rrp_tax_included},
371 rrp_tax_excluded => $order->{rrp_tax_excluded},
372 discount => $bookseller->{discount},
373 ecost_tax_included => $order->{ecost_tax_included},
374 ecost_tax_excluded => $order->{ecost_tax_excluded},
375 notes => $order->{order_vendornote},
376 entrydate => $order->{entrydate},
377 booksellername => $bookseller->{name},
378 bookselleraddress => $bookseller->{address1},
379 booksellerpostal => $bookseller->{postal},
380 contractnumber => $contract->{contractnumber},
381 contractname => $contract->{contractname},
384 basketgroupdeliveryplace => $basketgroup->{deliveryplace},
385 basketgroupbillingplace => $basketgroup->{billingplace},
386 basketdeliveryplace => $basket->{deliveryplace},
387 basketbillingplace => $basket->{billingplace},
389 for my $place (qw( basketgroupdeliveryplace basketgroupbillingplace basketdeliveryplace basketbillingplace )) {
390 if ( my $library = Koha::Libraries->find( $temp->{$place} ) ) {
391 $row->{$place} = $library->branchname;
395 basketname author title publishercode collectiontitle notes
396 booksellername bookselleraddress booksellerpostal contractname
397 basketgroupdeliveryplace basketgroupbillingplace
398 basketdeliveryplace basketbillingplace
400 # Double the quotes to not be interpreted as a field end
401 $row->{$_} =~ s/"/""/g if $row->{$_};
406 $template->param(rows => \@rows);
408 return $template->output;
412 =head3 CloseBasketgroup
414 &CloseBasketgroup($basketgroupno);
420 sub CloseBasketgroup {
421 my ($basketgroupno) = @_;
422 my $dbh = C4::Context->dbh;
423 my $sth = $dbh->prepare("
424 UPDATE aqbasketgroups
428 $sth->execute($basketgroupno);
431 #------------------------------------------------------------#
433 =head3 ReOpenBaskergroup($basketgroupno)
435 &ReOpenBaskergroup($basketgroupno);
441 sub ReOpenBasketgroup {
442 my ($basketgroupno) = @_;
443 my $dbh = C4::Context->dbh;
444 my $sth = $dbh->prepare("
445 UPDATE aqbasketgroups
449 $sth->execute($basketgroupno);
452 #------------------------------------------------------------#
457 &DelBasket($basketno);
459 Deletes the basket that has basketno field $basketno in the aqbasket table.
463 =item C<$basketno> is the primary key of the basket in the aqbasket table.
470 my ( $basketno ) = @_;
471 my $query = "DELETE FROM aqbasket WHERE basketno=?";
472 my $dbh = C4::Context->dbh;
473 my $sth = $dbh->prepare($query);
474 $sth->execute($basketno);
478 #------------------------------------------------------------#
482 &ModBasket($basketinfo);
484 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
488 =item C<$basketno> is the primary key of the basket in the aqbasket table.
495 my $basketinfo = shift;
496 my $query = "UPDATE aqbasket SET ";
498 foreach my $key (keys %$basketinfo){
499 if ($key ne 'basketno'){
500 $query .= "$key=?, ";
501 push(@params, $basketinfo->{$key} || undef );
504 # get rid of the "," at the end of $query
505 if (substr($query, length($query)-2) eq ', '){
510 $query .= "WHERE basketno=?";
511 push(@params, $basketinfo->{'basketno'});
512 my $dbh = C4::Context->dbh;
513 my $sth = $dbh->prepare($query);
514 $sth->execute(@params);
519 #------------------------------------------------------------#
521 =head3 ModBasketHeader
523 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
525 Modifies a basket's header.
529 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
531 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
533 =item C<$note> is the "note" field in the "aqbasket" table;
535 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
537 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
539 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
541 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
543 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
545 =item C<$is_standing> is the "is_standing" field in the aqbasket table.
551 sub ModBasketHeader {
552 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing) = @_;
555 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?, is_standing=?
559 my $dbh = C4::Context->dbh;
560 my $sth = $dbh->prepare($query);
561 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $is_standing, $basketno);
563 if ( $contractnumber ) {
564 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
565 my $sth2 = $dbh->prepare($query2);
566 $sth2->execute($contractnumber,$basketno);
571 #------------------------------------------------------------#
573 =head3 GetBasketsByBookseller
575 @results = &GetBasketsByBookseller($booksellerid, $extra);
577 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
581 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
583 =item C<$extra> is the extra sql parameters, can be
585 $extra->{groupby}: group baskets by column
586 ex. $extra->{groupby} = aqbasket.basketgroupid
587 $extra->{orderby}: order baskets by column
588 $extra->{limit}: limit number of results (can be helpful for pagination)
594 sub GetBasketsByBookseller {
595 my ($booksellerid, $extra) = @_;
596 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
598 if ($extra->{groupby}) {
599 $query .= " GROUP by $extra->{groupby}";
601 if ($extra->{orderby}){
602 $query .= " ORDER by $extra->{orderby}";
604 if ($extra->{limit}){
605 $query .= " LIMIT $extra->{limit}";
608 my $dbh = C4::Context->dbh;
609 my $sth = $dbh->prepare($query);
610 $sth->execute($booksellerid);
611 return $sth->fetchall_arrayref({});
614 =head3 GetBasketsInfosByBookseller
616 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
618 The optional second parameter allbaskets is a boolean allowing you to
619 select all baskets from the supplier; by default only active baskets (open or
620 closed but still something to receive) are returned.
622 Returns in a arrayref of hashref all about booksellers baskets, plus:
623 total_biblios: Number of distinct biblios in basket
624 total_items: Number of items in basket
625 expected_items: Number of non-received items in basket
629 sub GetBasketsInfosByBookseller {
630 my ($supplierid, $allbaskets) = @_;
632 return unless $supplierid;
634 my $dbh = C4::Context->dbh;
637 SUM(aqorders.quantity) AS total_items,
639 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
640 ) AS total_items_cancelled,
641 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
643 IF(aqorders.datereceived IS NULL
644 AND aqorders.datecancellationprinted IS NULL
649 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
650 WHERE booksellerid = ?};
652 unless ( $allbaskets ) {
653 $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
655 $query.=" GROUP BY aqbasket.basketno";
657 my $sth = $dbh->prepare($query);
658 $sth->execute($supplierid);
659 my $baskets = $sth->fetchall_arrayref({});
661 # Retrieve the number of biblios cancelled
662 my $cancelled_biblios = $dbh->selectall_hashref( q|
663 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
665 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
666 WHERE booksellerid = ?
667 AND aqorders.orderstatus = 'cancelled'
668 GROUP BY aqbasket.basketno
669 |, 'basketno', {}, $supplierid );
671 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
677 =head3 GetBasketUsers
679 $basketusers_ids = &GetBasketUsers($basketno);
681 Returns a list of all borrowernumbers that are in basket users list
686 my $basketno = shift;
688 return unless $basketno;
691 SELECT borrowernumber
695 my $dbh = C4::Context->dbh;
696 my $sth = $dbh->prepare($query);
697 $sth->execute($basketno);
698 my $results = $sth->fetchall_arrayref( {} );
701 foreach (@$results) {
702 push @borrowernumbers, $_->{'borrowernumber'};
705 return @borrowernumbers;
708 =head3 ModBasketUsers
710 my @basketusers_ids = (1, 2, 3);
711 &ModBasketUsers($basketno, @basketusers_ids);
713 Delete all users from basket users list, and add users in C<@basketusers_ids>
719 my ($basketno, @basketusers_ids) = @_;
721 return unless $basketno;
723 my $dbh = C4::Context->dbh;
725 DELETE FROM aqbasketusers
728 my $sth = $dbh->prepare($query);
729 $sth->execute($basketno);
732 INSERT INTO aqbasketusers (basketno, borrowernumber)
735 $sth = $dbh->prepare($query);
736 foreach my $basketuser_id (@basketusers_ids) {
737 $sth->execute($basketno, $basketuser_id);
742 =head3 CanUserManageBasket
744 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
745 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
747 Check if a borrower can manage a basket, according to system preference
748 AcqViewBaskets, user permissions and basket properties (creator, users list,
751 First parameter can be either a borrowernumber or a hashref as returned by
752 C4::Members::GetMember.
754 Second parameter can be either a basketno or a hashref as returned by
755 C4::Acquisition::GetBasket.
757 The third parameter is optional. If given, it should be a hashref as returned
758 by C4::Auth::getuserflags. If not, getuserflags is called.
760 If user is authorised to manage basket, returns 1.
765 sub CanUserManageBasket {
766 my ($borrower, $basket, $userflags) = @_;
768 if (!ref $borrower) {
769 $borrower = C4::Members::GetMember(borrowernumber => $borrower);
772 $basket = GetBasket($basket);
775 return 0 unless ($basket and $borrower);
777 my $borrowernumber = $borrower->{borrowernumber};
778 my $basketno = $basket->{basketno};
780 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
782 if (!defined $userflags) {
783 my $dbh = C4::Context->dbh;
784 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
785 $sth->execute($borrowernumber);
786 my ($flags) = $sth->fetchrow_array;
789 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
792 unless ($userflags->{superlibrarian}
793 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
794 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
796 if (not exists $userflags->{acquisition}) {
800 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
801 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
805 if ($AcqViewBaskets eq 'user'
806 && $basket->{authorisedby} != $borrowernumber
807 && grep($borrowernumber, GetBasketUsers($basketno)) == 0) {
811 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
812 && $basket->{branch} ne $borrower->{branchcode}) {
820 #------------------------------------------------------------#
822 =head3 GetBasketsByBasketgroup
824 $baskets = &GetBasketsByBasketgroup($basketgroupid);
826 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
830 sub GetBasketsByBasketgroup {
831 my $basketgroupid = shift;
833 SELECT *, aqbasket.booksellerid as booksellerid
835 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
837 my $dbh = C4::Context->dbh;
838 my $sth = $dbh->prepare($query);
839 $sth->execute($basketgroupid);
840 return $sth->fetchall_arrayref({});
843 #------------------------------------------------------------#
845 =head3 NewBasketgroup
847 $basketgroupid = NewBasketgroup(\%hashref);
849 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
851 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
853 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
855 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
857 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
859 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
861 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
863 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
865 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
870 my $basketgroupinfo = shift;
871 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
872 my $query = "INSERT INTO aqbasketgroups (";
874 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
875 if ( defined $basketgroupinfo->{$field} ) {
876 $query .= "$field, ";
877 push(@params, $basketgroupinfo->{$field});
880 $query .= "booksellerid) VALUES (";
885 push(@params, $basketgroupinfo->{'booksellerid'});
886 my $dbh = C4::Context->dbh;
887 my $sth = $dbh->prepare($query);
888 $sth->execute(@params);
889 my $basketgroupid = $dbh->{'mysql_insertid'};
890 if( $basketgroupinfo->{'basketlist'} ) {
891 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
892 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
893 my $sth2 = $dbh->prepare($query2);
894 $sth2->execute($basketgroupid, $basketno);
897 return $basketgroupid;
900 #------------------------------------------------------------#
902 =head3 ModBasketgroup
904 ModBasketgroup(\%hashref);
906 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
908 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
910 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
912 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
914 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
916 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
918 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
920 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
922 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
927 my $basketgroupinfo = shift;
928 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
929 my $dbh = C4::Context->dbh;
930 my $query = "UPDATE aqbasketgroups SET ";
932 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
933 if ( defined $basketgroupinfo->{$field} ) {
934 $query .= "$field=?, ";
935 push(@params, $basketgroupinfo->{$field});
940 $query .= " WHERE id=?";
941 push(@params, $basketgroupinfo->{'id'});
942 my $sth = $dbh->prepare($query);
943 $sth->execute(@params);
945 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
946 $sth->execute($basketgroupinfo->{'id'});
948 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
949 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
950 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
951 $sth->execute($basketgroupinfo->{'id'}, $basketno);
957 #------------------------------------------------------------#
959 =head3 DelBasketgroup
961 DelBasketgroup($basketgroupid);
963 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
967 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
974 my $basketgroupid = shift;
975 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
976 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
977 my $dbh = C4::Context->dbh;
978 my $sth = $dbh->prepare($query);
979 $sth->execute($basketgroupid);
983 #------------------------------------------------------------#
986 =head2 FUNCTIONS ABOUT ORDERS
988 =head3 GetBasketgroup
990 $basketgroup = &GetBasketgroup($basketgroupid);
992 Returns a reference to the hash containing all information about the basketgroup.
997 my $basketgroupid = shift;
998 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
999 my $dbh = C4::Context->dbh;
1000 my $result_set = $dbh->selectall_arrayref(
1001 'SELECT * FROM aqbasketgroups WHERE id=?',
1005 return $result_set->[0]; # id is unique
1008 #------------------------------------------------------------#
1010 =head3 GetBasketgroups
1012 $basketgroups = &GetBasketgroups($booksellerid);
1014 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1018 sub GetBasketgroups {
1019 my $booksellerid = shift;
1020 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1021 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1022 my $dbh = C4::Context->dbh;
1023 my $sth = $dbh->prepare($query);
1024 $sth->execute($booksellerid);
1025 return $sth->fetchall_arrayref({});
1028 #------------------------------------------------------------#
1030 =head2 FUNCTIONS ABOUT ORDERS
1034 @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } );
1036 Looks up the pending (non-cancelled) orders with the given basket
1039 If cancelled is set, only cancelled orders will be returned.
1044 my ( $basketno, $params ) = @_;
1046 return () unless $basketno;
1048 my $orderby = $params->{orderby};
1049 my $cancelled = $params->{cancelled} || 0;
1051 my $dbh = C4::Context->dbh;
1053 SELECT biblio.*,biblioitems.*,
1057 $query .= $cancelled
1059 aqorders_transfers.ordernumber_to AS transferred_to,
1060 aqorders_transfers.timestamp AS transferred_to_timestamp
1063 aqorders_transfers.ordernumber_from AS transferred_from,
1064 aqorders_transfers.timestamp AS transferred_from_timestamp
1068 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1069 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1070 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1072 $query .= $cancelled
1074 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1077 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1085 $orderby ||= q|biblioitems.publishercode, biblio.title|;
1087 AND (datecancellationprinted IS NOT NULL
1088 AND datecancellationprinted <> '0000-00-00')
1093 q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|;
1095 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1099 $query .= " ORDER BY $orderby";
1101 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1106 #------------------------------------------------------------#
1108 =head3 GetOrdersByBiblionumber
1110 @orders = &GetOrdersByBiblionumber($biblionumber);
1112 Looks up the orders with linked to a specific $biblionumber, including
1113 cancelled orders and received orders.
1116 C<@orders> is an array of references-to-hash, whose keys are the
1117 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1121 sub GetOrdersByBiblionumber {
1122 my $biblionumber = shift;
1123 return unless $biblionumber;
1124 my $dbh = C4::Context->dbh;
1126 SELECT biblio.*,biblioitems.*,
1130 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1131 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1132 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1133 WHERE aqorders.biblionumber=?
1136 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1137 return @{$result_set};
1141 #------------------------------------------------------------#
1145 $order = &GetOrder($ordernumber);
1147 Looks up an order by order number.
1149 Returns a reference-to-hash describing the order. The keys of
1150 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1155 my ($ordernumber) = @_;
1156 return unless $ordernumber;
1158 my $dbh = C4::Context->dbh;
1159 my $query = qq{SELECT
1163 aqbasket.basketname,
1164 borrowers.branchcode,
1165 biblioitems.publicationyear,
1166 biblio.copyrightdate,
1167 biblioitems.editionstatement,
1171 biblioitems.publishercode,
1172 aqorders.rrp AS unitpricesupplier,
1173 aqorders.ecost AS unitpricelib,
1174 aqorders.claims_count AS claims_count,
1175 aqorders.claimed_date AS claimed_date,
1176 aqbudgets.budget_name AS budget,
1177 aqbooksellers.name AS supplier,
1178 aqbooksellers.id AS supplierid,
1179 biblioitems.publishercode AS publisher,
1180 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1181 DATE(aqbasket.closedate) AS orderdate,
1182 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1183 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1184 DATEDIFF(CURDATE( ),closedate) AS latesince
1185 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1186 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1187 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1188 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1189 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1190 WHERE aqorders.basketno = aqbasket.basketno
1193 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1195 # result_set assumed to contain 1 match
1196 return $result_set->[0];
1199 =head3 GetLastOrderNotReceivedFromSubscriptionid
1201 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1203 Returns a reference-to-hash describing the last order not received for a subscription.
1207 sub GetLastOrderNotReceivedFromSubscriptionid {
1208 my ( $subscriptionid ) = @_;
1209 my $dbh = C4::Context->dbh;
1211 SELECT * FROM aqorders
1212 LEFT JOIN subscription
1213 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1214 WHERE aqorders.subscriptionid = ?
1215 AND aqorders.datereceived IS NULL
1219 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1221 # result_set assumed to contain 1 match
1222 return $result_set->[0];
1225 =head3 GetLastOrderReceivedFromSubscriptionid
1227 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1229 Returns a reference-to-hash describing the last order received for a subscription.
1233 sub GetLastOrderReceivedFromSubscriptionid {
1234 my ( $subscriptionid ) = @_;
1235 my $dbh = C4::Context->dbh;
1237 SELECT * FROM aqorders
1238 LEFT JOIN subscription
1239 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1240 WHERE aqorders.subscriptionid = ?
1241 AND aqorders.datereceived =
1243 SELECT MAX( aqorders.datereceived )
1245 LEFT JOIN subscription
1246 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1247 WHERE aqorders.subscriptionid = ?
1248 AND aqorders.datereceived IS NOT NULL
1250 ORDER BY ordernumber DESC
1254 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1256 # result_set assumed to contain 1 match
1257 return $result_set->[0];
1261 #------------------------------------------------------------#
1265 &ModOrder(\%hashref);
1267 Modifies an existing order. Updates the order with order number
1268 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1269 other keys of the hash update the fields with the same name in the aqorders
1270 table of the Koha database.
1275 my $orderinfo = shift;
1277 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '';
1279 my $dbh = C4::Context->dbh;
1282 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1283 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1285 # delete($orderinfo->{'branchcode'});
1286 # the hash contains a lot of entries not in aqorders, so get the columns ...
1287 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1289 my $colnames = $sth->{NAME};
1290 #FIXME Be careful. If aqorders would have columns with diacritics,
1291 #you should need to decode what you get back from NAME.
1292 #See report 10110 and guided_reports.pl
1293 my $query = "UPDATE aqorders SET ";
1295 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1296 # ... and skip hash entries that are not in the aqorders table
1297 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1298 next unless grep(/^$orderinfokey$/, @$colnames);
1299 $query .= "$orderinfokey=?, ";
1300 push(@params, $orderinfo->{$orderinfokey});
1303 $query .= "timestamp=NOW() WHERE ordernumber=?";
1304 push(@params, $orderinfo->{'ordernumber'} );
1305 $sth = $dbh->prepare($query);
1306 $sth->execute(@params);
1310 #------------------------------------------------------------#
1314 ModItemOrder($itemnumber, $ordernumber);
1316 Modifies the ordernumber of an item in aqorders_items.
1321 my ($itemnumber, $ordernumber) = @_;
1323 return unless ($itemnumber and $ordernumber);
1325 my $dbh = C4::Context->dbh;
1327 UPDATE aqorders_items
1329 WHERE itemnumber = ?
1331 my $sth = $dbh->prepare($query);
1332 return $sth->execute($ordernumber, $itemnumber);
1335 #------------------------------------------------------------#
1337 =head3 ModReceiveOrder
1339 my ( $date_received, $new_ordernumber ) = ModReceiveOrder(
1341 biblionumber => $biblionumber,
1343 quantityreceived => $quantityreceived,
1345 invoice => $invoice,
1346 budget_id => $budget_id,
1347 received_itemnumbers => \@received_itemnumbers,
1348 order_internalnote => $order_internalnote,
1352 Updates an order, to reflect the fact that it was received, at least
1355 If a partial order is received, splits the order into two.
1357 Updates the order with biblionumber C<$biblionumber> and ordernumber
1358 C<$order->{ordernumber}>.
1363 sub ModReceiveOrder {
1365 my $biblionumber = $params->{biblionumber};
1366 my $order = { %{ $params->{order} } }; # Copy the order, we don't want to modify it
1367 my $invoice = $params->{invoice};
1368 my $quantrec = $params->{quantityreceived};
1369 my $user = $params->{user};
1370 my $budget_id = $params->{budget_id};
1371 my $received_items = $params->{received_items};
1373 my $dbh = C4::Context->dbh;
1374 my $datereceived = ( $invoice and $invoice->{datereceived} ) ? $invoice->{datereceived} : dt_from_string;
1375 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1376 if ($suggestionid) {
1377 ModSuggestion( {suggestionid=>$suggestionid,
1378 STATUS=>'AVAILABLE',
1379 biblionumber=> $biblionumber}
1383 my $new_ordernumber = $order->{ordernumber};
1384 if ( $order->{quantity} > $quantrec ) {
1385 # Split order line in two parts: the first is the original order line
1386 # without received items (the quantity is decreased),
1387 # the second part is a new order line with quantity=quantityrec
1388 # (entirely received)
1392 orderstatus = 'partial'|;
1393 $query .= q|, order_internalnote = ?| if defined $order->{order_internalnote};
1394 $query .= q| WHERE ordernumber = ?|;
1395 my $sth = $dbh->prepare($query);
1398 $order->{quantity} - $quantrec,
1399 ( defined $order->{order_internalnote} ? $order->{order_internalnote} : () ),
1400 $order->{ordernumber}
1403 # Recalculate tax_value
1406 SET tax_value = quantity * ecost_tax_excluded * tax_rate
1407 WHERE ordernumber = ?
1408 |, undef, $order->{ordernumber});
1410 delete $order->{ordernumber};
1411 $order->{budget_id} = ( $budget_id || $order->{budget_id} );
1412 $order->{quantity} = $quantrec;
1413 $order->{quantityreceived} = $quantrec;
1414 $order->{tax_value} = $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate};
1415 $order->{datereceived} = $datereceived;
1416 $order->{invoiceid} = $invoice->{invoiceid};
1417 $order->{orderstatus} = 'complete';
1418 $new_ordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber};
1420 if ($received_items) {
1421 foreach my $itemnumber (@$received_items) {
1422 ModItemOrder($itemnumber, $new_ordernumber);
1428 SET quantityreceived = ?,
1432 orderstatus = 'complete'
1436 , unitprice = ?, unitprice_tax_included = ?, unitprice_tax_excluded = ?
1437 | if defined $order->{unitprice};
1440 , rrp = ?, rrp_tax_included = ?, rrp_tax_excluded = ?
1441 | if defined $order->{rrp};
1444 , ecost = ?, ecost_tax_included = ?, ecost_tax_excluded = ?
1445 | if defined $order->{ecost};
1448 , order_internalnote = ?
1449 | if defined $order->{order_internalnote};
1451 $query .= q| where biblionumber=? and ordernumber=?|;
1453 my $sth = $dbh->prepare( $query );
1454 my @params = ( $quantrec, $datereceived, $invoice->{invoiceid}, $budget_id );
1456 if ( defined $order->{unitprice} ) {
1457 push @params, $order->{unitprice}, $order->{unitprice_tax_included}, $order->{unitprice_tax_excluded};
1459 if ( defined $order->{rrp} ) {
1460 push @params, $order->{rrp}, $order->{rrp_tax_included}, $order->{rrp_tax_excluded};
1462 if ( defined $order->{ecost} ) {
1463 push @params, $order->{ecost}, $order->{ecost_tax_included}, $order->{ecost_tax_excluded};
1465 if ( defined $order->{order_internalnote} ) {
1466 push @params, $order->{order_internalnote};
1469 push @params, ( $biblionumber, $order->{ordernumber} );
1471 $sth->execute( @params );
1473 # All items have been received, sent a notification to users
1474 NotifyOrderUsers( $order->{ordernumber} );
1477 return ($datereceived, $new_ordernumber);
1480 =head3 CancelReceipt
1482 my $parent_ordernumber = CancelReceipt($ordernumber);
1484 Cancel an order line receipt and update the parent order line, as if no
1486 If items are created at receipt (AcqCreateItem = receiving) then delete
1492 my $ordernumber = shift;
1494 return unless $ordernumber;
1496 my $dbh = C4::Context->dbh;
1498 SELECT datereceived, parent_ordernumber, quantity
1500 WHERE ordernumber = ?
1502 my $sth = $dbh->prepare($query);
1503 $sth->execute($ordernumber);
1504 my $order = $sth->fetchrow_hashref;
1506 warn "CancelReceipt: order $ordernumber does not exist";
1509 unless($order->{'datereceived'}) {
1510 warn "CancelReceipt: order $ordernumber is not received";
1514 my $parent_ordernumber = $order->{'parent_ordernumber'};
1516 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1518 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1519 # The order line has no parent, just mark it as not received
1522 SET quantityreceived = ?,
1525 orderstatus = 'ordered'
1526 WHERE ordernumber = ?
1528 $sth = $dbh->prepare($query);
1529 $sth->execute(0, undef, undef, $ordernumber);
1530 _cancel_items_receipt( $ordernumber );
1532 # The order line has a parent, increase parent quantity and delete
1535 SELECT quantity, datereceived
1537 WHERE ordernumber = ?
1539 $sth = $dbh->prepare($query);
1540 $sth->execute($parent_ordernumber);
1541 my $parent_order = $sth->fetchrow_hashref;
1542 unless($parent_order) {
1543 warn "Parent order $parent_ordernumber does not exist.";
1546 if($parent_order->{'datereceived'}) {
1547 warn "CancelReceipt: parent order is received.".
1548 " Can't cancel receipt.";
1554 orderstatus = 'ordered'
1555 WHERE ordernumber = ?
1557 $sth = $dbh->prepare($query);
1558 my $rv = $sth->execute(
1559 $order->{'quantity'} + $parent_order->{'quantity'},
1563 warn "Cannot update parent order line, so do not cancel".
1568 # Recalculate tax_value
1571 SET tax_value = quantity * ecost_tax_excluded * tax_rate
1572 WHERE ordernumber = ?
1573 |, undef, $parent_ordernumber);
1575 _cancel_items_receipt( $ordernumber, $parent_ordernumber );
1578 DELETE FROM aqorders
1579 WHERE ordernumber = ?
1581 $sth = $dbh->prepare($query);
1582 $sth->execute($ordernumber);
1586 if(C4::Context->preference('AcqCreateItem') eq 'ordering') {
1587 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1589 for my $in ( @itemnumbers ) {
1590 my $biblionumber = C4::Biblio::GetBiblionumberFromItemnumber( $in );
1591 my $frameworkcode = GetFrameworkCode($biblionumber);
1592 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber', $frameworkcode );
1593 my $item = C4::Items::GetMarcItem( $biblionumber, $in );
1594 for my $affect ( @affects ) {
1595 my ( $sf, $v ) = split q{=}, $affect, 2;
1596 foreach ( $item->field($itemfield) ) {
1597 $_->update( $sf => $v );
1600 C4::Items::ModItemFromMarc( $item, $biblionumber, $in );
1605 return $parent_ordernumber;
1608 sub _cancel_items_receipt {
1609 my ( $ordernumber, $parent_ordernumber ) = @_;
1610 $parent_ordernumber ||= $ordernumber;
1612 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1613 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1614 # Remove items that were created at receipt
1616 DELETE FROM items, aqorders_items
1617 USING items, aqorders_items
1618 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1620 my $dbh = C4::Context->dbh;
1621 my $sth = $dbh->prepare($query);
1622 foreach my $itemnumber (@itemnumbers) {
1623 $sth->execute($itemnumber, $itemnumber);
1627 foreach my $itemnumber (@itemnumbers) {
1628 ModItemOrder($itemnumber, $parent_ordernumber);
1633 #------------------------------------------------------------#
1637 @results = &SearchOrders({
1638 ordernumber => $ordernumber,
1640 biblionumber => $biblionumber,
1642 booksellerid => $booksellerid,
1643 basketno => $basketno,
1649 Searches for orders.
1651 C<$owner> Finds order for the logged in user.
1652 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1653 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1656 C<@results> is an array of references-to-hash with the keys are fields
1657 from aqorders, biblio, biblioitems and aqbasket tables.
1662 my ( $params ) = @_;
1663 my $ordernumber = $params->{ordernumber};
1664 my $search = $params->{search};
1665 my $ean = $params->{ean};
1666 my $booksellerid = $params->{booksellerid};
1667 my $basketno = $params->{basketno};
1668 my $basketname = $params->{basketname};
1669 my $basketgroupname = $params->{basketgroupname};
1670 my $owner = $params->{owner};
1671 my $pending = $params->{pending};
1672 my $ordered = $params->{ordered};
1673 my $biblionumber = $params->{biblionumber};
1674 my $budget_id = $params->{budget_id};
1676 my $dbh = C4::Context->dbh;
1679 SELECT aqbasket.basketno,
1681 borrowers.firstname,
1684 biblioitems.biblioitemnumber,
1685 aqbasket.authorisedby,
1686 aqbasket.booksellerid,
1688 aqbasket.creationdate,
1689 aqbasket.basketname,
1690 aqbasketgroups.id as basketgroupid,
1691 aqbasketgroups.name as basketgroupname,
1694 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1695 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1696 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1697 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1698 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1701 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1703 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1707 WHERE (datecancellationprinted is NULL)
1710 if ( $pending or $ordered ) {
1713 ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) )
1715 ( quantity > quantityreceived OR quantityreceived is NULL )
1719 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1727 my $userenv = C4::Context->userenv;
1728 if ( C4::Context->preference("IndependentBranches") ) {
1729 unless ( C4::Context->IsSuperLibrarian() ) {
1732 borrowers.branchcode = ?
1733 OR borrowers.branchcode = ''
1736 push @args, $userenv->{branch};
1740 if ( $ordernumber ) {
1741 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1742 push @args, ( $ordernumber, $ordernumber );
1744 if ( $biblionumber ) {
1745 $query .= 'AND aqorders.biblionumber = ?';
1746 push @args, $biblionumber;
1749 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1750 push @args, ("%$search%","%$search%","%$search%");
1753 $query .= ' AND biblioitems.ean = ?';
1756 if ( $booksellerid ) {
1757 $query .= 'AND aqbasket.booksellerid = ?';
1758 push @args, $booksellerid;
1761 $query .= 'AND aqbasket.basketno = ?';
1762 push @args, $basketno;
1765 $query .= 'AND aqbasket.basketname LIKE ?';
1766 push @args, "%$basketname%";
1768 if( $basketgroupname ) {
1769 $query .= ' AND aqbasketgroups.name LIKE ?';
1770 push @args, "%$basketgroupname%";
1774 $query .= ' AND aqbasket.authorisedby=? ';
1775 push @args, $userenv->{'number'};
1779 $query .= ' AND aqorders.budget_id = ?';
1780 push @args, $budget_id;
1783 $query .= ' ORDER BY aqbasket.basketno';
1785 my $sth = $dbh->prepare($query);
1786 $sth->execute(@args);
1787 return $sth->fetchall_arrayref({});
1790 #------------------------------------------------------------#
1794 &DelOrder($biblionumber, $ordernumber);
1796 Cancel the order with the given order and biblio numbers. It does not
1797 delete any entries in the aqorders table, it merely marks them as
1803 my ( $bibnum, $ordernumber, $delete_biblio, $reason ) = @_;
1806 my $dbh = C4::Context->dbh;
1809 SET datecancellationprinted=now(), orderstatus='cancelled'
1812 $query .= ", cancellationreason = ? ";
1815 WHERE biblionumber=? AND ordernumber=?
1817 my $sth = $dbh->prepare($query);
1819 $sth->execute($reason, $bibnum, $ordernumber);
1821 $sth->execute( $bibnum, $ordernumber );
1825 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1826 foreach my $itemnumber (@itemnumbers){
1827 my $delcheck = C4::Items::DelItemCheck( $bibnum, $itemnumber );
1829 if($delcheck != 1) {
1830 $error->{'delitem'} = 1;
1834 if($delete_biblio) {
1835 # We get the number of remaining items
1836 my $itemcount = C4::Items::GetItemsCount($bibnum);
1838 # If there are no items left,
1839 if ( $itemcount == 0 ) {
1840 # We delete the record
1841 my $delcheck = DelBiblio($bibnum);
1844 $error->{'delbiblio'} = 1;
1852 =head3 TransferOrder
1854 my $newordernumber = TransferOrder($ordernumber, $basketno);
1856 Transfer an order line to a basket.
1857 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1858 to BOOKSELLER on DATE' and create new order with internal note
1859 'Transferred from BOOKSELLER on DATE'.
1860 Move all attached items to the new order.
1861 Received orders cannot be transferred.
1862 Return the ordernumber of created order.
1867 my ($ordernumber, $basketno) = @_;
1869 return unless ($ordernumber and $basketno);
1871 my $order = GetOrder( $ordernumber );
1872 return if $order->{datereceived};
1873 my $basket = GetBasket($basketno);
1874 return unless $basket;
1876 my $dbh = C4::Context->dbh;
1877 my ($query, $sth, $rv);
1881 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1882 WHERE ordernumber = ?
1884 $sth = $dbh->prepare($query);
1885 $rv = $sth->execute('cancelled', $ordernumber);
1887 delete $order->{'ordernumber'};
1888 delete $order->{parent_ordernumber};
1889 $order->{'basketno'} = $basketno;
1891 my $newordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber};
1894 UPDATE aqorders_items
1896 WHERE ordernumber = ?
1898 $sth = $dbh->prepare($query);
1899 $sth->execute($newordernumber, $ordernumber);
1902 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1905 $sth = $dbh->prepare($query);
1906 $sth->execute($ordernumber, $newordernumber);
1908 return $newordernumber;
1911 =head2 FUNCTIONS ABOUT PARCELS
1915 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1917 get a lists of parcels.
1924 is the bookseller this function has to get parcels.
1927 To know on what criteria the results list has to be ordered.
1930 is the booksellerinvoicenumber.
1932 =item $datefrom & $dateto
1933 to know on what date this function has to filter its search.
1938 a pointer on a hash list containing parcel informations as such :
1944 =item Last operation
1946 =item Number of biblio
1948 =item Number of items
1955 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1956 my $dbh = C4::Context->dbh;
1957 my @query_params = ();
1959 SELECT aqinvoices.invoicenumber,
1960 datereceived,purchaseordernumber,
1961 count(DISTINCT biblionumber) AS biblio,
1962 sum(quantity) AS itemsexpected,
1963 sum(quantityreceived) AS itemsreceived
1964 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1965 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1966 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1968 push @query_params, $bookseller;
1970 if ( defined $code ) {
1971 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1972 # add a % to the end of the code to allow stemming.
1973 push @query_params, "$code%";
1976 if ( defined $datefrom ) {
1977 $strsth .= ' and datereceived >= ? ';
1978 push @query_params, $datefrom;
1981 if ( defined $dateto ) {
1982 $strsth .= 'and datereceived <= ? ';
1983 push @query_params, $dateto;
1986 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1988 # can't use a placeholder to place this column name.
1989 # but, we could probably be checking to make sure it is a column that will be fetched.
1990 $strsth .= "order by $order " if ($order);
1992 my $sth = $dbh->prepare($strsth);
1994 $sth->execute( @query_params );
1995 my $results = $sth->fetchall_arrayref({});
1999 #------------------------------------------------------------#
2001 =head3 GetLateOrders
2003 @results = &GetLateOrders;
2005 Searches for bookseller with late orders.
2008 the table of supplier with late issues. This table is full of hashref.
2014 my $supplierid = shift;
2016 my $estimateddeliverydatefrom = shift;
2017 my $estimateddeliverydateto = shift;
2019 my $dbh = C4::Context->dbh;
2021 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2022 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2024 my @query_params = ();
2026 SELECT aqbasket.basketno,
2027 aqorders.ordernumber,
2028 DATE(aqbasket.closedate) AS orderdate,
2029 aqbasket.basketname AS basketname,
2030 aqbasket.basketgroupid AS basketgroupid,
2031 aqbasketgroups.name AS basketgroupname,
2032 aqorders.rrp AS unitpricesupplier,
2033 aqorders.ecost AS unitpricelib,
2034 aqorders.claims_count AS claims_count,
2035 aqorders.claimed_date AS claimed_date,
2036 aqbudgets.budget_name AS budget,
2037 borrowers.branchcode AS branch,
2038 aqbooksellers.name AS supplier,
2039 aqbooksellers.id AS supplierid,
2040 biblio.author, biblio.title,
2041 biblioitems.publishercode AS publisher,
2042 biblioitems.publicationyear,
2043 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2047 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2048 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2049 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2050 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2051 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2052 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2053 WHERE aqorders.basketno = aqbasket.basketno
2054 AND ( datereceived = ''
2055 OR datereceived IS NULL
2056 OR aqorders.quantityreceived < aqorders.quantity
2058 AND aqbasket.closedate IS NOT NULL
2059 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2062 if ($dbdriver eq "mysql") {
2064 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2065 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2066 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2068 if ( defined $delay ) {
2069 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2070 push @query_params, $delay;
2073 HAVING quantity <> 0
2074 AND unitpricesupplier <> 0
2075 AND unitpricelib <> 0
2078 # FIXME: account for IFNULL as above
2080 aqorders.quantity AS quantity,
2081 aqorders.quantity * aqorders.rrp AS subtotal,
2082 (CAST(now() AS date) - closedate) AS latesince
2084 if ( defined $delay ) {
2085 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2086 push @query_params, $delay;
2089 if (defined $supplierid) {
2090 $from .= ' AND aqbasket.booksellerid = ? ';
2091 push @query_params, $supplierid;
2093 if (defined $branch) {
2094 $from .= ' AND borrowers.branchcode LIKE ? ';
2095 push @query_params, $branch;
2098 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2099 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2101 if ( defined $estimateddeliverydatefrom ) {
2102 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2103 push @query_params, $estimateddeliverydatefrom;
2105 if ( defined $estimateddeliverydateto ) {
2106 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2107 push @query_params, $estimateddeliverydateto;
2109 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2110 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2112 if (C4::Context->preference("IndependentBranches")
2113 && !C4::Context->IsSuperLibrarian() ) {
2114 $from .= ' AND borrowers.branchcode LIKE ? ';
2115 push @query_params, C4::Context->userenv->{branch};
2117 $from .= " AND orderstatus <> 'cancelled' ";
2118 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2119 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2120 my $sth = $dbh->prepare($query);
2121 $sth->execute(@query_params);
2123 while (my $data = $sth->fetchrow_hashref) {
2124 push @results, $data;
2129 #------------------------------------------------------------#
2133 \@order_loop = GetHistory( %params );
2135 Retreives some acquisition history information
2145 basket - search both basket name and number
2146 booksellerinvoicenumber
2149 orderstatus (note that orderstatus '' will retrieve orders
2150 of any status except cancelled)
2152 get_canceled_order (if set to a true value, cancelled orders will
2156 $order_loop is a list of hashrefs that each look like this:
2158 'author' => 'Twain, Mark',
2160 'biblionumber' => '215',
2162 'creationdate' => 'MM/DD/YYYY',
2163 'datereceived' => undef,
2166 'invoicenumber' => undef,
2168 'ordernumber' => '1',
2170 'quantityreceived' => undef,
2171 'title' => 'The Adventures of Huckleberry Finn'
2177 # don't run the query if there are no parameters (list would be too long for sure !)
2178 croak "No search params" unless @_;
2180 my $title = $params{title};
2181 my $author = $params{author};
2182 my $isbn = $params{isbn};
2183 my $ean = $params{ean};
2184 my $name = $params{name};
2185 my $from_placed_on = $params{from_placed_on};
2186 my $to_placed_on = $params{to_placed_on};
2187 my $basket = $params{basket};
2188 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2189 my $basketgroupname = $params{basketgroupname};
2190 my $budget = $params{budget};
2191 my $orderstatus = $params{orderstatus};
2192 my $biblionumber = $params{biblionumber};
2193 my $get_canceled_order = $params{get_canceled_order} || 0;
2194 my $ordernumber = $params{ordernumber};
2195 my $search_children_too = $params{search_children_too} || 0;
2196 my $created_by = $params{created_by} || [];
2200 my $total_qtyreceived = 0;
2201 my $total_price = 0;
2203 my $dbh = C4::Context->dbh;
2206 COALESCE(biblio.title, deletedbiblio.title) AS title,
2207 COALESCE(biblio.author, deletedbiblio.author) AS author,
2208 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2209 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2211 aqbasket.basketname,
2212 aqbasket.basketgroupid,
2213 aqbasket.authorisedby,
2214 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2215 aqbasketgroups.name as groupname,
2217 aqbasket.creationdate,
2218 aqorders.datereceived,
2220 aqorders.quantityreceived,
2222 aqorders.ordernumber,
2224 aqinvoices.invoicenumber,
2225 aqbooksellers.id as id,
2226 aqorders.biblionumber,
2227 aqorders.orderstatus,
2228 aqorders.parent_ordernumber,
2229 aqbudgets.budget_name
2231 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2234 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2235 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2236 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2237 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2238 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2239 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2240 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2241 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2242 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2243 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2246 $query .= " WHERE 1 ";
2248 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2249 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2252 my @query_params = ();
2254 if ( $biblionumber ) {
2255 $query .= " AND biblio.biblionumber = ?";
2256 push @query_params, $biblionumber;
2260 $query .= " AND biblio.title LIKE ? ";
2261 $title =~ s/\s+/%/g;
2262 push @query_params, "%$title%";
2266 $query .= " AND biblio.author LIKE ? ";
2267 push @query_params, "%$author%";
2271 $query .= " AND biblioitems.isbn LIKE ? ";
2272 push @query_params, "%$isbn%";
2275 $query .= " AND biblioitems.ean = ? ";
2276 push @query_params, "$ean";
2279 $query .= " AND aqbooksellers.name LIKE ? ";
2280 push @query_params, "%$name%";
2284 $query .= " AND aqbudgets.budget_id = ? ";
2285 push @query_params, "$budget";
2288 if ( $from_placed_on ) {
2289 $query .= " AND creationdate >= ? ";
2290 push @query_params, $from_placed_on;
2293 if ( $to_placed_on ) {
2294 $query .= " AND creationdate <= ? ";
2295 push @query_params, $to_placed_on;
2298 if ( defined $orderstatus and $orderstatus ne '') {
2299 $query .= " AND aqorders.orderstatus = ? ";
2300 push @query_params, "$orderstatus";
2304 if ($basket =~ m/^\d+$/) {
2305 $query .= " AND aqorders.basketno = ? ";
2306 push @query_params, $basket;
2308 $query .= " AND aqbasket.basketname LIKE ? ";
2309 push @query_params, "%$basket%";
2313 if ($booksellerinvoicenumber) {
2314 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2315 push @query_params, "%$booksellerinvoicenumber%";
2318 if ($basketgroupname) {
2319 $query .= " AND aqbasketgroups.name LIKE ? ";
2320 push @query_params, "%$basketgroupname%";
2324 $query .= " AND (aqorders.ordernumber = ? ";
2325 push @query_params, $ordernumber;
2326 if ($search_children_too) {
2327 $query .= " OR aqorders.parent_ordernumber = ? ";
2328 push @query_params, $ordernumber;
2333 if ( @$created_by ) {
2334 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2335 push @query_params, @$created_by;
2339 if ( C4::Context->preference("IndependentBranches") ) {
2340 unless ( C4::Context->IsSuperLibrarian() ) {
2341 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2342 push @query_params, C4::Context->userenv->{branch};
2345 $query .= " ORDER BY id";
2347 return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2350 =head2 GetRecentAcqui
2352 $results = GetRecentAcqui($days);
2354 C<$results> is a ref to a table which containts hashref
2358 sub GetRecentAcqui {
2360 my $dbh = C4::Context->dbh;
2364 ORDER BY timestamp DESC
2367 my $sth = $dbh->prepare($query);
2369 my $results = $sth->fetchall_arrayref({});
2373 #------------------------------------------------------------#
2377 &AddClaim($ordernumber);
2379 Add a claim for an order
2384 my ($ordernumber) = @_;
2385 my $dbh = C4::Context->dbh;
2388 claims_count = claims_count + 1,
2389 claimed_date = CURDATE()
2390 WHERE ordernumber = ?
2392 my $sth = $dbh->prepare($query);
2393 $sth->execute($ordernumber);
2398 my @invoices = GetInvoices(
2399 invoicenumber => $invoicenumber,
2400 supplierid => $supplierid,
2401 suppliername => $suppliername,
2402 shipmentdatefrom => $shipmentdatefrom, # ISO format
2403 shipmentdateto => $shipmentdateto, # ISO format
2404 billingdatefrom => $billingdatefrom, # ISO format
2405 billingdateto => $billingdateto, # ISO format
2406 isbneanissn => $isbn_or_ean_or_issn,
2409 publisher => $publisher,
2410 publicationyear => $publicationyear,
2411 branchcode => $branchcode,
2412 order_by => $order_by
2415 Return a list of invoices that match all given criteria.
2417 $order_by is "column_name (asc|desc)", where column_name is any of
2418 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2419 'shipmentcost', 'shipmentcost_budgetid'.
2421 asc is the default if omitted
2428 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2429 closedate shipmentcost shipmentcost_budgetid);
2431 my $dbh = C4::Context->dbh;
2433 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2436 aqorders.datereceived IS NOT NULL,
2437 aqorders.biblionumber,
2440 ) AS receivedbiblios,
2443 aqorders.subscriptionid IS NOT NULL,
2444 aqorders.subscriptionid,
2447 ) AS is_linked_to_subscriptions,
2448 SUM(aqorders.quantityreceived) AS receiveditems
2450 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2451 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2452 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2453 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2454 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2455 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2456 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2461 if($args{supplierid}) {
2462 push @bind_strs, " aqinvoices.booksellerid = ? ";
2463 push @bind_args, $args{supplierid};
2465 if($args{invoicenumber}) {
2466 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2467 push @bind_args, "%$args{invoicenumber}%";
2469 if($args{suppliername}) {
2470 push @bind_strs, " aqbooksellers.name LIKE ? ";
2471 push @bind_args, "%$args{suppliername}%";
2473 if($args{shipmentdatefrom}) {
2474 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2475 push @bind_args, $args{shipmentdatefrom};
2477 if($args{shipmentdateto}) {
2478 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2479 push @bind_args, $args{shipmentdateto};
2481 if($args{billingdatefrom}) {
2482 push @bind_strs, " aqinvoices.billingdate >= ? ";
2483 push @bind_args, $args{billingdatefrom};
2485 if($args{billingdateto}) {
2486 push @bind_strs, " aqinvoices.billingdate <= ? ";
2487 push @bind_args, $args{billingdateto};
2489 if($args{isbneanissn}) {
2490 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2491 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2494 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2495 push @bind_args, $args{title};
2498 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2499 push @bind_args, $args{author};
2501 if($args{publisher}) {
2502 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2503 push @bind_args, $args{publisher};
2505 if($args{publicationyear}) {
2506 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2507 push @bind_args, $args{publicationyear}, $args{publicationyear};
2509 if($args{branchcode}) {
2510 push @bind_strs, " borrowers.branchcode = ? ";
2511 push @bind_args, $args{branchcode};
2513 if($args{message_id}) {
2514 push @bind_strs, " aqinvoices.message_id = ? ";
2515 push @bind_args, $args{message_id};
2518 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2519 $query .= " GROUP BY aqinvoices.invoiceid ";
2521 if($args{order_by}) {
2522 my ($column, $direction) = split / /, $args{order_by};
2523 if(grep /^$column$/, @columns) {
2524 $direction ||= 'ASC';
2525 $query .= " ORDER BY $column $direction";
2529 my $sth = $dbh->prepare($query);
2530 $sth->execute(@bind_args);
2532 my $results = $sth->fetchall_arrayref({});
2538 my $invoice = GetInvoice($invoiceid);
2540 Get informations about invoice with given $invoiceid
2542 Return a hash filled with aqinvoices.* fields
2547 my ($invoiceid) = @_;
2550 return unless $invoiceid;
2552 my $dbh = C4::Context->dbh;
2558 my $sth = $dbh->prepare($query);
2559 $sth->execute($invoiceid);
2561 $invoice = $sth->fetchrow_hashref;
2565 =head3 GetInvoiceDetails
2567 my $invoice = GetInvoiceDetails($invoiceid)
2569 Return informations about an invoice + the list of related order lines
2571 Orders informations are in $invoice->{orders} (array ref)
2575 sub GetInvoiceDetails {
2576 my ($invoiceid) = @_;
2578 if ( !defined $invoiceid ) {
2579 carp 'GetInvoiceDetails called without an invoiceid';
2583 my $dbh = C4::Context->dbh;
2585 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2587 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2590 my $sth = $dbh->prepare($query);
2591 $sth->execute($invoiceid);
2593 my $invoice = $sth->fetchrow_hashref;
2598 biblio.copyrightdate,
2599 biblioitems.publishercode,
2600 biblioitems.publicationyear,
2601 aqbasket.basketname,
2602 aqbasketgroups.id AS basketgroupid,
2603 aqbasketgroups.name AS basketgroupname
2605 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2606 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2607 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2608 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2611 $sth = $dbh->prepare($query);
2612 $sth->execute($invoiceid);
2613 $invoice->{orders} = $sth->fetchall_arrayref({});
2614 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2621 my $invoiceid = AddInvoice(
2622 invoicenumber => $invoicenumber,
2623 booksellerid => $booksellerid,
2624 shipmentdate => $shipmentdate,
2625 billingdate => $billingdate,
2626 closedate => $closedate,
2627 shipmentcost => $shipmentcost,
2628 shipmentcost_budgetid => $shipmentcost_budgetid
2631 Create a new invoice and return its id or undef if it fails.
2638 return unless(%invoice and $invoice{invoicenumber});
2640 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2641 closedate shipmentcost shipmentcost_budgetid message_id);
2645 foreach my $key (keys %invoice) {
2646 if(0 < grep(/^$key$/, @columns)) {
2647 push @set_strs, "$key = ?";
2648 push @set_args, ($invoice{$key} || undef);
2654 my $dbh = C4::Context->dbh;
2655 my $query = "INSERT INTO aqinvoices SET ";
2656 $query .= join (",", @set_strs);
2657 my $sth = $dbh->prepare($query);
2658 $rv = $sth->execute(@set_args);
2660 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2669 invoiceid => $invoiceid, # Mandatory
2670 invoicenumber => $invoicenumber,
2671 booksellerid => $booksellerid,
2672 shipmentdate => $shipmentdate,
2673 billingdate => $billingdate,
2674 closedate => $closedate,
2675 shipmentcost => $shipmentcost,
2676 shipmentcost_budgetid => $shipmentcost_budgetid
2679 Modify an invoice, invoiceid is mandatory.
2681 Return undef if it fails.
2688 return unless(%invoice and $invoice{invoiceid});
2690 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2691 closedate shipmentcost shipmentcost_budgetid);
2695 foreach my $key (keys %invoice) {
2696 if(0 < grep(/^$key$/, @columns)) {
2697 push @set_strs, "$key = ?";
2698 push @set_args, ($invoice{$key} || undef);
2702 my $dbh = C4::Context->dbh;
2703 my $query = "UPDATE aqinvoices SET ";
2704 $query .= join(",", @set_strs);
2705 $query .= " WHERE invoiceid = ?";
2707 my $sth = $dbh->prepare($query);
2708 $sth->execute(@set_args, $invoice{invoiceid});
2713 CloseInvoice($invoiceid);
2717 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2722 my ($invoiceid) = @_;
2724 return unless $invoiceid;
2726 my $dbh = C4::Context->dbh;
2729 SET closedate = CAST(NOW() AS DATE)
2732 my $sth = $dbh->prepare($query);
2733 $sth->execute($invoiceid);
2736 =head3 ReopenInvoice
2738 ReopenInvoice($invoiceid);
2742 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => output_pref({ dt=>dt_from_string, dateonly=>1, otputpref=>'iso' }))
2747 my ($invoiceid) = @_;
2749 return unless $invoiceid;
2751 my $dbh = C4::Context->dbh;
2754 SET closedate = NULL
2757 my $sth = $dbh->prepare($query);
2758 $sth->execute($invoiceid);
2763 DelInvoice($invoiceid);
2765 Delete an invoice if there are no items attached to it.
2770 my ($invoiceid) = @_;
2772 return unless $invoiceid;
2774 my $dbh = C4::Context->dbh;
2780 my $sth = $dbh->prepare($query);
2781 $sth->execute($invoiceid);
2782 my $res = $sth->fetchrow_arrayref;
2783 if ( $res && $res->[0] == 0 ) {
2785 DELETE FROM aqinvoices
2788 my $sth = $dbh->prepare($query);
2789 return ( $sth->execute($invoiceid) > 0 );
2794 =head3 MergeInvoices
2796 MergeInvoices($invoiceid, \@sourceids);
2798 Merge the invoices identified by the IDs in \@sourceids into
2799 the invoice identified by $invoiceid.
2804 my ($invoiceid, $sourceids) = @_;
2806 return unless $invoiceid;
2807 foreach my $sourceid (@$sourceids) {
2808 next if $sourceid == $invoiceid;
2809 my $source = GetInvoiceDetails($sourceid);
2810 foreach my $order (@{$source->{'orders'}}) {
2811 $order->{'invoiceid'} = $invoiceid;
2814 DelInvoice($source->{'invoiceid'});
2819 =head3 GetBiblioCountByBasketno
2821 $biblio_count = &GetBiblioCountByBasketno($basketno);
2823 Looks up the biblio's count that has basketno value $basketno
2829 sub GetBiblioCountByBasketno {
2830 my ($basketno) = @_;
2831 my $dbh = C4::Context->dbh;
2833 SELECT COUNT( DISTINCT( biblionumber ) )
2836 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2839 my $sth = $dbh->prepare($query);
2840 $sth->execute($basketno);
2841 return $sth->fetchrow;
2844 # Note this subroutine should be moved to Koha::Acquisition::Order
2845 # Will do when a DBIC decision will be taken.
2846 sub populate_order_with_prices {
2849 my $order = $params->{order};
2850 my $booksellerid = $params->{booksellerid};
2851 return unless $booksellerid;
2853 my $bookseller = Koha::Acquisition::Bookseller->fetch({ id => $booksellerid });
2855 my $receiving = $params->{receiving};
2856 my $ordering = $params->{ordering};
2857 my $discount = $order->{discount};
2858 $discount /= 100 if $discount > 1;
2861 if ( $bookseller->{listincgst} ) {
2862 # The user entered the rrp tax included
2863 $order->{rrp_tax_included} = $order->{rrp};
2865 # rrp tax excluded = rrp tax included / ( 1 + tax rate )
2866 $order->{rrp_tax_excluded} = $order->{rrp_tax_included} / ( 1 + $order->{tax_rate} );
2868 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
2869 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
2871 # ecost tax included = rrp tax included ( 1 - discount )
2872 $order->{ecost_tax_included} = $order->{rrp_tax_included} * ( 1 - $discount );
2875 # The user entered the rrp tax excluded
2876 $order->{rrp_tax_excluded} = $order->{rrp};
2878 # rrp tax included = rrp tax excluded * ( 1 - tax rate )
2879 $order->{rrp_tax_included} = $order->{rrp_tax_excluded} * ( 1 + $order->{tax_rate} );
2881 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
2882 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
2884 # ecost tax included = rrp tax excluded * ( 1 - tax rate ) * ( 1 - discount )
2885 $order->{ecost_tax_included} =
2886 $order->{rrp_tax_excluded} *
2887 ( 1 + $order->{tax_rate} ) *
2891 # tax value = quantity * ecost tax excluded * tax rate
2892 $order->{tax_value} = $order->{quantity} * $order->{ecost_tax_excluded} * $order->{tax_rate};
2896 if ( $bookseller->{invoiceincgst} ) {
2897 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
2898 # we need to keep the exact ecost value
2899 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_included} )->round ) {
2900 $order->{unitprice} = $order->{ecost_tax_included};
2903 # The user entered the unit price tax included
2904 $order->{unitprice_tax_included} = $order->{unitprice};
2906 # unit price tax excluded = unit price tax included / ( 1 + tax rate )
2907 $order->{unitprice_tax_excluded} = $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate} );
2910 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
2911 # we need to keep the exact ecost value
2912 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_excluded} )->round ) {
2913 $order->{unitprice} = $order->{ecost_tax_excluded};
2916 # The user entered the unit price tax excluded
2917 $order->{unitprice_tax_excluded} = $order->{unitprice};
2919 # unit price tax included = unit price tax included * ( 1 + tax rate )
2920 $order->{unitprice_tax_included} = $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate} );
2923 # tax value = quantity * unit price tax excluded * tax rate
2924 $order->{tax_value} = $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate};
2930 =head3 GetOrderUsers
2932 $order_users_ids = &GetOrderUsers($ordernumber);
2934 Returns a list of all borrowernumbers that are in order users list
2939 my ($ordernumber) = @_;
2941 return unless $ordernumber;
2944 SELECT borrowernumber
2946 WHERE ordernumber = ?
2948 my $dbh = C4::Context->dbh;
2949 my $sth = $dbh->prepare($query);
2950 $sth->execute($ordernumber);
2951 my $results = $sth->fetchall_arrayref( {} );
2953 my @borrowernumbers;
2954 foreach (@$results) {
2955 push @borrowernumbers, $_->{'borrowernumber'};
2958 return @borrowernumbers;
2961 =head3 ModOrderUsers
2963 my @order_users_ids = (1, 2, 3);
2964 &ModOrderUsers($ordernumber, @basketusers_ids);
2966 Delete all users from order users list, and add users in C<@order_users_ids>
2972 my ( $ordernumber, @order_users_ids ) = @_;
2974 return unless $ordernumber;
2976 my $dbh = C4::Context->dbh;
2978 DELETE FROM aqorder_users
2979 WHERE ordernumber = ?
2981 my $sth = $dbh->prepare($query);
2982 $sth->execute($ordernumber);
2985 INSERT INTO aqorder_users (ordernumber, borrowernumber)
2988 $sth = $dbh->prepare($query);
2989 foreach my $order_user_id (@order_users_ids) {
2990 $sth->execute( $ordernumber, $order_user_id );
2994 sub NotifyOrderUsers {
2995 my ($ordernumber) = @_;
2997 my @borrowernumbers = GetOrderUsers($ordernumber);
2998 return unless @borrowernumbers;
3000 my $order = GetOrder( $ordernumber );
3001 for my $borrowernumber (@borrowernumbers) {
3002 my $borrower = C4::Members::GetMember( borrowernumber => $borrowernumber );
3003 my $library = Koha::Libraries->find( $borrower->{branchcode} )->unblessed;
3004 my $biblio = C4::Biblio::GetBiblio( $order->{biblionumber} );
3005 my $letter = C4::Letters::GetPreparedLetter(
3006 module => 'acquisition',
3007 letter_code => 'ACQ_NOTIF_ON_RECEIV',
3008 branchcode => $library->{branchcode},
3010 'branches' => $library,
3011 'borrowers' => $borrower,
3012 'biblio' => $biblio,
3013 'aqorders' => $order,
3017 C4::Letters::EnqueueLetter(
3020 borrowernumber => $borrowernumber,
3021 LibraryName => C4::Context->preference("LibraryName"),
3022 message_transport_type => 'email',
3024 ) or warn "can't enqueue letter $letter";
3029 =head3 FillWithDefaultValues
3031 FillWithDefaultValues( $marc_record );
3033 This will update the record with default value defined in the ACQ framework.
3034 For all existing fields, if a default value exists and there are no subfield, it will be created.
3035 If the field does not exist, it will be created too.
3039 sub FillWithDefaultValues {
3041 my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ', { unsafe => 1 } );
3044 C4::Biblio::GetMarcFromKohaField( 'items.itemnumber', '' );
3045 for my $tag ( sort keys %$tagslib ) {
3047 next if $tag == $itemfield;
3048 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
3049 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
3050 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
3051 if ( defined $defaultvalue and $defaultvalue ne '' ) {
3052 my @fields = $record->field($tag);
3054 for my $field (@fields) {
3055 unless ( defined $field->subfield($subfield) ) {
3056 $field->add_subfields(
3057 $subfield => $defaultvalue );
3062 $record->insert_fields_ordered(
3064 $tag, '', '', $subfield => $defaultvalue
3079 Koha Development Team <http://koha-community.org/>