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 $result_set = $dbh->selectrow_arrayref(
1384 q{SELECT aqbasket.is_standing
1386 WHERE basketno=?},{ Slice => {} }, $order->{basketno});
1387 my $is_standing = $result_set->[0]; # we assume we have a unique basket
1389 my $new_ordernumber = $order->{ordernumber};
1390 if ( $is_standing || $order->{quantity} > $quantrec ) {
1391 # Split order line in two parts: the first is the original order line
1392 # without received items (the quantity is decreased),
1393 # the second part is a new order line with quantity=quantityrec
1394 # (entirely received)
1398 orderstatus = 'partial'|;
1399 $query .= q|, order_internalnote = ?| if defined $order->{order_internalnote};
1400 $query .= q| WHERE ordernumber = ?|;
1401 my $sth = $dbh->prepare($query);
1404 ( $is_standing ? 1 : ($order->{quantity} - $quantrec) ),
1405 ( defined $order->{order_internalnote} ? $order->{order_internalnote} : () ),
1406 $order->{ordernumber}
1409 # Recalculate tax_value
1412 SET tax_value = quantity * ecost_tax_excluded * tax_rate
1413 WHERE ordernumber = ?
1414 |, undef, $order->{ordernumber});
1416 delete $order->{ordernumber};
1417 $order->{budget_id} = ( $budget_id || $order->{budget_id} );
1418 $order->{quantity} = $quantrec;
1419 $order->{quantityreceived} = $quantrec;
1420 $order->{tax_value} = $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate};
1421 $order->{datereceived} = $datereceived;
1422 $order->{invoiceid} = $invoice->{invoiceid};
1423 $order->{orderstatus} = 'complete';
1424 $new_ordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber};
1426 if ($received_items) {
1427 foreach my $itemnumber (@$received_items) {
1428 ModItemOrder($itemnumber, $new_ordernumber);
1434 SET quantityreceived = ?,
1438 orderstatus = 'complete'
1442 , unitprice = ?, unitprice_tax_included = ?, unitprice_tax_excluded = ?
1443 | if defined $order->{unitprice};
1446 , rrp = ?, rrp_tax_included = ?, rrp_tax_excluded = ?
1447 | if defined $order->{rrp};
1450 , ecost = ?, ecost_tax_included = ?, ecost_tax_excluded = ?
1451 | if defined $order->{ecost};
1454 , order_internalnote = ?
1455 | if defined $order->{order_internalnote};
1457 $query .= q| where biblionumber=? and ordernumber=?|;
1459 my $sth = $dbh->prepare( $query );
1460 my @params = ( $quantrec, $datereceived, $invoice->{invoiceid}, $budget_id );
1462 if ( defined $order->{unitprice} ) {
1463 push @params, $order->{unitprice}, $order->{unitprice_tax_included}, $order->{unitprice_tax_excluded};
1465 if ( defined $order->{rrp} ) {
1466 push @params, $order->{rrp}, $order->{rrp_tax_included}, $order->{rrp_tax_excluded};
1468 if ( defined $order->{ecost} ) {
1469 push @params, $order->{ecost}, $order->{ecost_tax_included}, $order->{ecost_tax_excluded};
1471 if ( defined $order->{order_internalnote} ) {
1472 push @params, $order->{order_internalnote};
1475 push @params, ( $biblionumber, $order->{ordernumber} );
1477 $sth->execute( @params );
1479 # All items have been received, sent a notification to users
1480 NotifyOrderUsers( $order->{ordernumber} );
1483 return ($datereceived, $new_ordernumber);
1486 =head3 CancelReceipt
1488 my $parent_ordernumber = CancelReceipt($ordernumber);
1490 Cancel an order line receipt and update the parent order line, as if no
1492 If items are created at receipt (AcqCreateItem = receiving) then delete
1498 my $ordernumber = shift;
1500 return unless $ordernumber;
1502 my $dbh = C4::Context->dbh;
1504 SELECT datereceived, parent_ordernumber, quantity
1506 WHERE ordernumber = ?
1508 my $sth = $dbh->prepare($query);
1509 $sth->execute($ordernumber);
1510 my $order = $sth->fetchrow_hashref;
1512 warn "CancelReceipt: order $ordernumber does not exist";
1515 unless($order->{'datereceived'}) {
1516 warn "CancelReceipt: order $ordernumber is not received";
1520 my $parent_ordernumber = $order->{'parent_ordernumber'};
1522 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1524 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1525 # The order line has no parent, just mark it as not received
1528 SET quantityreceived = ?,
1531 orderstatus = 'ordered'
1532 WHERE ordernumber = ?
1534 $sth = $dbh->prepare($query);
1535 $sth->execute(0, undef, undef, $ordernumber);
1536 _cancel_items_receipt( $ordernumber );
1538 # The order line has a parent, increase parent quantity and delete
1541 SELECT quantity, datereceived
1543 WHERE ordernumber = ?
1545 $sth = $dbh->prepare($query);
1546 $sth->execute($parent_ordernumber);
1547 my $parent_order = $sth->fetchrow_hashref;
1548 unless($parent_order) {
1549 warn "Parent order $parent_ordernumber does not exist.";
1552 if($parent_order->{'datereceived'}) {
1553 warn "CancelReceipt: parent order is received.".
1554 " Can't cancel receipt.";
1560 orderstatus = 'ordered'
1561 WHERE ordernumber = ?
1563 $sth = $dbh->prepare($query);
1564 my $rv = $sth->execute(
1565 $order->{'quantity'} + $parent_order->{'quantity'},
1569 warn "Cannot update parent order line, so do not cancel".
1574 # Recalculate tax_value
1577 SET tax_value = quantity * ecost_tax_excluded * tax_rate
1578 WHERE ordernumber = ?
1579 |, undef, $parent_ordernumber);
1581 _cancel_items_receipt( $ordernumber, $parent_ordernumber );
1584 DELETE FROM aqorders
1585 WHERE ordernumber = ?
1587 $sth = $dbh->prepare($query);
1588 $sth->execute($ordernumber);
1592 if(C4::Context->preference('AcqCreateItem') eq 'ordering') {
1593 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1595 for my $in ( @itemnumbers ) {
1596 my $biblionumber = C4::Biblio::GetBiblionumberFromItemnumber( $in );
1597 my $frameworkcode = GetFrameworkCode($biblionumber);
1598 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber', $frameworkcode );
1599 my $item = C4::Items::GetMarcItem( $biblionumber, $in );
1600 for my $affect ( @affects ) {
1601 my ( $sf, $v ) = split q{=}, $affect, 2;
1602 foreach ( $item->field($itemfield) ) {
1603 $_->update( $sf => $v );
1606 C4::Items::ModItemFromMarc( $item, $biblionumber, $in );
1611 return $parent_ordernumber;
1614 sub _cancel_items_receipt {
1615 my ( $ordernumber, $parent_ordernumber ) = @_;
1616 $parent_ordernumber ||= $ordernumber;
1618 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1619 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1620 # Remove items that were created at receipt
1622 DELETE FROM items, aqorders_items
1623 USING items, aqorders_items
1624 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1626 my $dbh = C4::Context->dbh;
1627 my $sth = $dbh->prepare($query);
1628 foreach my $itemnumber (@itemnumbers) {
1629 $sth->execute($itemnumber, $itemnumber);
1633 foreach my $itemnumber (@itemnumbers) {
1634 ModItemOrder($itemnumber, $parent_ordernumber);
1639 #------------------------------------------------------------#
1643 @results = &SearchOrders({
1644 ordernumber => $ordernumber,
1646 biblionumber => $biblionumber,
1648 booksellerid => $booksellerid,
1649 basketno => $basketno,
1655 Searches for orders.
1657 C<$owner> Finds order for the logged in user.
1658 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1659 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1662 C<@results> is an array of references-to-hash with the keys are fields
1663 from aqorders, biblio, biblioitems and aqbasket tables.
1668 my ( $params ) = @_;
1669 my $ordernumber = $params->{ordernumber};
1670 my $search = $params->{search};
1671 my $ean = $params->{ean};
1672 my $booksellerid = $params->{booksellerid};
1673 my $basketno = $params->{basketno};
1674 my $basketname = $params->{basketname};
1675 my $basketgroupname = $params->{basketgroupname};
1676 my $owner = $params->{owner};
1677 my $pending = $params->{pending};
1678 my $ordered = $params->{ordered};
1679 my $biblionumber = $params->{biblionumber};
1680 my $budget_id = $params->{budget_id};
1682 my $dbh = C4::Context->dbh;
1685 SELECT aqbasket.basketno,
1687 borrowers.firstname,
1690 biblioitems.biblioitemnumber,
1691 aqbasket.authorisedby,
1692 aqbasket.booksellerid,
1694 aqbasket.creationdate,
1695 aqbasket.basketname,
1696 aqbasketgroups.id as basketgroupid,
1697 aqbasketgroups.name as basketgroupname,
1700 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1701 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1702 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1703 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1704 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1707 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1709 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1713 WHERE (datecancellationprinted is NULL)
1716 if ( $pending or $ordered ) {
1719 ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) )
1721 ( quantity > quantityreceived OR quantityreceived is NULL )
1725 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1733 my $userenv = C4::Context->userenv;
1734 if ( C4::Context->preference("IndependentBranches") ) {
1735 unless ( C4::Context->IsSuperLibrarian() ) {
1738 borrowers.branchcode = ?
1739 OR borrowers.branchcode = ''
1742 push @args, $userenv->{branch};
1746 if ( $ordernumber ) {
1747 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1748 push @args, ( $ordernumber, $ordernumber );
1750 if ( $biblionumber ) {
1751 $query .= 'AND aqorders.biblionumber = ?';
1752 push @args, $biblionumber;
1755 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1756 push @args, ("%$search%","%$search%","%$search%");
1759 $query .= ' AND biblioitems.ean = ?';
1762 if ( $booksellerid ) {
1763 $query .= 'AND aqbasket.booksellerid = ?';
1764 push @args, $booksellerid;
1767 $query .= 'AND aqbasket.basketno = ?';
1768 push @args, $basketno;
1771 $query .= 'AND aqbasket.basketname LIKE ?';
1772 push @args, "%$basketname%";
1774 if( $basketgroupname ) {
1775 $query .= ' AND aqbasketgroups.name LIKE ?';
1776 push @args, "%$basketgroupname%";
1780 $query .= ' AND aqbasket.authorisedby=? ';
1781 push @args, $userenv->{'number'};
1785 $query .= ' AND aqorders.budget_id = ?';
1786 push @args, $budget_id;
1789 $query .= ' ORDER BY aqbasket.basketno';
1791 my $sth = $dbh->prepare($query);
1792 $sth->execute(@args);
1793 return $sth->fetchall_arrayref({});
1796 #------------------------------------------------------------#
1800 &DelOrder($biblionumber, $ordernumber);
1802 Cancel the order with the given order and biblio numbers. It does not
1803 delete any entries in the aqorders table, it merely marks them as
1809 my ( $bibnum, $ordernumber, $delete_biblio, $reason ) = @_;
1812 my $dbh = C4::Context->dbh;
1815 SET datecancellationprinted=now(), orderstatus='cancelled'
1818 $query .= ", cancellationreason = ? ";
1821 WHERE biblionumber=? AND ordernumber=?
1823 my $sth = $dbh->prepare($query);
1825 $sth->execute($reason, $bibnum, $ordernumber);
1827 $sth->execute( $bibnum, $ordernumber );
1831 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1832 foreach my $itemnumber (@itemnumbers){
1833 my $delcheck = C4::Items::DelItemCheck( $bibnum, $itemnumber );
1835 if($delcheck != 1) {
1836 $error->{'delitem'} = 1;
1840 if($delete_biblio) {
1841 # We get the number of remaining items
1842 my $itemcount = C4::Items::GetItemsCount($bibnum);
1844 # If there are no items left,
1845 if ( $itemcount == 0 ) {
1846 # We delete the record
1847 my $delcheck = DelBiblio($bibnum);
1850 $error->{'delbiblio'} = 1;
1858 =head3 TransferOrder
1860 my $newordernumber = TransferOrder($ordernumber, $basketno);
1862 Transfer an order line to a basket.
1863 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1864 to BOOKSELLER on DATE' and create new order with internal note
1865 'Transferred from BOOKSELLER on DATE'.
1866 Move all attached items to the new order.
1867 Received orders cannot be transferred.
1868 Return the ordernumber of created order.
1873 my ($ordernumber, $basketno) = @_;
1875 return unless ($ordernumber and $basketno);
1877 my $order = GetOrder( $ordernumber );
1878 return if $order->{datereceived};
1879 my $basket = GetBasket($basketno);
1880 return unless $basket;
1882 my $dbh = C4::Context->dbh;
1883 my ($query, $sth, $rv);
1887 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1888 WHERE ordernumber = ?
1890 $sth = $dbh->prepare($query);
1891 $rv = $sth->execute('cancelled', $ordernumber);
1893 delete $order->{'ordernumber'};
1894 delete $order->{parent_ordernumber};
1895 $order->{'basketno'} = $basketno;
1897 my $newordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber};
1900 UPDATE aqorders_items
1902 WHERE ordernumber = ?
1904 $sth = $dbh->prepare($query);
1905 $sth->execute($newordernumber, $ordernumber);
1908 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1911 $sth = $dbh->prepare($query);
1912 $sth->execute($ordernumber, $newordernumber);
1914 return $newordernumber;
1917 =head2 FUNCTIONS ABOUT PARCELS
1921 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1923 get a lists of parcels.
1930 is the bookseller this function has to get parcels.
1933 To know on what criteria the results list has to be ordered.
1936 is the booksellerinvoicenumber.
1938 =item $datefrom & $dateto
1939 to know on what date this function has to filter its search.
1944 a pointer on a hash list containing parcel informations as such :
1950 =item Last operation
1952 =item Number of biblio
1954 =item Number of items
1961 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1962 my $dbh = C4::Context->dbh;
1963 my @query_params = ();
1965 SELECT aqinvoices.invoicenumber,
1966 datereceived,purchaseordernumber,
1967 count(DISTINCT biblionumber) AS biblio,
1968 sum(quantity) AS itemsexpected,
1969 sum(quantityreceived) AS itemsreceived
1970 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1971 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1972 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1974 push @query_params, $bookseller;
1976 if ( defined $code ) {
1977 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1978 # add a % to the end of the code to allow stemming.
1979 push @query_params, "$code%";
1982 if ( defined $datefrom ) {
1983 $strsth .= ' and datereceived >= ? ';
1984 push @query_params, $datefrom;
1987 if ( defined $dateto ) {
1988 $strsth .= 'and datereceived <= ? ';
1989 push @query_params, $dateto;
1992 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1994 # can't use a placeholder to place this column name.
1995 # but, we could probably be checking to make sure it is a column that will be fetched.
1996 $strsth .= "order by $order " if ($order);
1998 my $sth = $dbh->prepare($strsth);
2000 $sth->execute( @query_params );
2001 my $results = $sth->fetchall_arrayref({});
2005 #------------------------------------------------------------#
2007 =head3 GetLateOrders
2009 @results = &GetLateOrders;
2011 Searches for bookseller with late orders.
2014 the table of supplier with late issues. This table is full of hashref.
2020 my $supplierid = shift;
2022 my $estimateddeliverydatefrom = shift;
2023 my $estimateddeliverydateto = shift;
2025 my $dbh = C4::Context->dbh;
2027 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2028 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2030 my @query_params = ();
2032 SELECT aqbasket.basketno,
2033 aqorders.ordernumber,
2034 DATE(aqbasket.closedate) AS orderdate,
2035 aqbasket.basketname AS basketname,
2036 aqbasket.basketgroupid AS basketgroupid,
2037 aqbasketgroups.name AS basketgroupname,
2038 aqorders.rrp AS unitpricesupplier,
2039 aqorders.ecost AS unitpricelib,
2040 aqorders.claims_count AS claims_count,
2041 aqorders.claimed_date AS claimed_date,
2042 aqbudgets.budget_name AS budget,
2043 borrowers.branchcode AS branch,
2044 aqbooksellers.name AS supplier,
2045 aqbooksellers.id AS supplierid,
2046 biblio.author, biblio.title,
2047 biblioitems.publishercode AS publisher,
2048 biblioitems.publicationyear,
2049 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2053 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2054 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2055 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2056 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2057 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2058 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2059 WHERE aqorders.basketno = aqbasket.basketno
2060 AND ( datereceived = ''
2061 OR datereceived IS NULL
2062 OR aqorders.quantityreceived < aqorders.quantity
2064 AND aqbasket.closedate IS NOT NULL
2065 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2068 if ($dbdriver eq "mysql") {
2070 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2071 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2072 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2074 if ( defined $delay ) {
2075 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2076 push @query_params, $delay;
2079 HAVING quantity <> 0
2080 AND unitpricesupplier <> 0
2081 AND unitpricelib <> 0
2084 # FIXME: account for IFNULL as above
2086 aqorders.quantity AS quantity,
2087 aqorders.quantity * aqorders.rrp AS subtotal,
2088 (CAST(now() AS date) - closedate) AS latesince
2090 if ( defined $delay ) {
2091 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2092 push @query_params, $delay;
2095 if (defined $supplierid) {
2096 $from .= ' AND aqbasket.booksellerid = ? ';
2097 push @query_params, $supplierid;
2099 if (defined $branch) {
2100 $from .= ' AND borrowers.branchcode LIKE ? ';
2101 push @query_params, $branch;
2104 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2105 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2107 if ( defined $estimateddeliverydatefrom ) {
2108 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2109 push @query_params, $estimateddeliverydatefrom;
2111 if ( defined $estimateddeliverydateto ) {
2112 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2113 push @query_params, $estimateddeliverydateto;
2115 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2116 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2118 if (C4::Context->preference("IndependentBranches")
2119 && !C4::Context->IsSuperLibrarian() ) {
2120 $from .= ' AND borrowers.branchcode LIKE ? ';
2121 push @query_params, C4::Context->userenv->{branch};
2123 $from .= " AND orderstatus <> 'cancelled' ";
2124 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2125 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2126 my $sth = $dbh->prepare($query);
2127 $sth->execute(@query_params);
2129 while (my $data = $sth->fetchrow_hashref) {
2130 push @results, $data;
2135 #------------------------------------------------------------#
2139 \@order_loop = GetHistory( %params );
2141 Retreives some acquisition history information
2151 basket - search both basket name and number
2152 booksellerinvoicenumber
2155 orderstatus (note that orderstatus '' will retrieve orders
2156 of any status except cancelled)
2158 get_canceled_order (if set to a true value, cancelled orders will
2162 $order_loop is a list of hashrefs that each look like this:
2164 'author' => 'Twain, Mark',
2166 'biblionumber' => '215',
2168 'creationdate' => 'MM/DD/YYYY',
2169 'datereceived' => undef,
2172 'invoicenumber' => undef,
2174 'ordernumber' => '1',
2176 'quantityreceived' => undef,
2177 'title' => 'The Adventures of Huckleberry Finn'
2183 # don't run the query if there are no parameters (list would be too long for sure !)
2184 croak "No search params" unless @_;
2186 my $title = $params{title};
2187 my $author = $params{author};
2188 my $isbn = $params{isbn};
2189 my $ean = $params{ean};
2190 my $name = $params{name};
2191 my $from_placed_on = $params{from_placed_on};
2192 my $to_placed_on = $params{to_placed_on};
2193 my $basket = $params{basket};
2194 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2195 my $basketgroupname = $params{basketgroupname};
2196 my $budget = $params{budget};
2197 my $orderstatus = $params{orderstatus};
2198 my $biblionumber = $params{biblionumber};
2199 my $get_canceled_order = $params{get_canceled_order} || 0;
2200 my $ordernumber = $params{ordernumber};
2201 my $search_children_too = $params{search_children_too} || 0;
2202 my $created_by = $params{created_by} || [];
2206 my $total_qtyreceived = 0;
2207 my $total_price = 0;
2209 my $dbh = C4::Context->dbh;
2212 COALESCE(biblio.title, deletedbiblio.title) AS title,
2213 COALESCE(biblio.author, deletedbiblio.author) AS author,
2214 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2215 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2217 aqbasket.basketname,
2218 aqbasket.basketgroupid,
2219 aqbasket.authorisedby,
2220 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2221 aqbasketgroups.name as groupname,
2223 aqbasket.creationdate,
2224 aqorders.datereceived,
2226 aqorders.quantityreceived,
2228 aqorders.ordernumber,
2230 aqinvoices.invoicenumber,
2231 aqbooksellers.id as id,
2232 aqorders.biblionumber,
2233 aqorders.orderstatus,
2234 aqorders.parent_ordernumber,
2235 aqbudgets.budget_name
2237 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2240 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2241 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2242 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2243 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2244 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2245 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2246 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2247 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2248 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2249 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2252 $query .= " WHERE 1 ";
2254 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2255 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2258 my @query_params = ();
2260 if ( $biblionumber ) {
2261 $query .= " AND biblio.biblionumber = ?";
2262 push @query_params, $biblionumber;
2266 $query .= " AND biblio.title LIKE ? ";
2267 $title =~ s/\s+/%/g;
2268 push @query_params, "%$title%";
2272 $query .= " AND biblio.author LIKE ? ";
2273 push @query_params, "%$author%";
2277 $query .= " AND biblioitems.isbn LIKE ? ";
2278 push @query_params, "%$isbn%";
2281 $query .= " AND biblioitems.ean = ? ";
2282 push @query_params, "$ean";
2285 $query .= " AND aqbooksellers.name LIKE ? ";
2286 push @query_params, "%$name%";
2290 $query .= " AND aqbudgets.budget_id = ? ";
2291 push @query_params, "$budget";
2294 if ( $from_placed_on ) {
2295 $query .= " AND creationdate >= ? ";
2296 push @query_params, $from_placed_on;
2299 if ( $to_placed_on ) {
2300 $query .= " AND creationdate <= ? ";
2301 push @query_params, $to_placed_on;
2304 if ( defined $orderstatus and $orderstatus ne '') {
2305 $query .= " AND aqorders.orderstatus = ? ";
2306 push @query_params, "$orderstatus";
2310 if ($basket =~ m/^\d+$/) {
2311 $query .= " AND aqorders.basketno = ? ";
2312 push @query_params, $basket;
2314 $query .= " AND aqbasket.basketname LIKE ? ";
2315 push @query_params, "%$basket%";
2319 if ($booksellerinvoicenumber) {
2320 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2321 push @query_params, "%$booksellerinvoicenumber%";
2324 if ($basketgroupname) {
2325 $query .= " AND aqbasketgroups.name LIKE ? ";
2326 push @query_params, "%$basketgroupname%";
2330 $query .= " AND (aqorders.ordernumber = ? ";
2331 push @query_params, $ordernumber;
2332 if ($search_children_too) {
2333 $query .= " OR aqorders.parent_ordernumber = ? ";
2334 push @query_params, $ordernumber;
2339 if ( @$created_by ) {
2340 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2341 push @query_params, @$created_by;
2345 if ( C4::Context->preference("IndependentBranches") ) {
2346 unless ( C4::Context->IsSuperLibrarian() ) {
2347 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2348 push @query_params, C4::Context->userenv->{branch};
2351 $query .= " ORDER BY id";
2353 return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2356 =head2 GetRecentAcqui
2358 $results = GetRecentAcqui($days);
2360 C<$results> is a ref to a table which containts hashref
2364 sub GetRecentAcqui {
2366 my $dbh = C4::Context->dbh;
2370 ORDER BY timestamp DESC
2373 my $sth = $dbh->prepare($query);
2375 my $results = $sth->fetchall_arrayref({});
2379 #------------------------------------------------------------#
2383 &AddClaim($ordernumber);
2385 Add a claim for an order
2390 my ($ordernumber) = @_;
2391 my $dbh = C4::Context->dbh;
2394 claims_count = claims_count + 1,
2395 claimed_date = CURDATE()
2396 WHERE ordernumber = ?
2398 my $sth = $dbh->prepare($query);
2399 $sth->execute($ordernumber);
2404 my @invoices = GetInvoices(
2405 invoicenumber => $invoicenumber,
2406 supplierid => $supplierid,
2407 suppliername => $suppliername,
2408 shipmentdatefrom => $shipmentdatefrom, # ISO format
2409 shipmentdateto => $shipmentdateto, # ISO format
2410 billingdatefrom => $billingdatefrom, # ISO format
2411 billingdateto => $billingdateto, # ISO format
2412 isbneanissn => $isbn_or_ean_or_issn,
2415 publisher => $publisher,
2416 publicationyear => $publicationyear,
2417 branchcode => $branchcode,
2418 order_by => $order_by
2421 Return a list of invoices that match all given criteria.
2423 $order_by is "column_name (asc|desc)", where column_name is any of
2424 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2425 'shipmentcost', 'shipmentcost_budgetid'.
2427 asc is the default if omitted
2434 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2435 closedate shipmentcost shipmentcost_budgetid);
2437 my $dbh = C4::Context->dbh;
2439 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2442 aqorders.datereceived IS NOT NULL,
2443 aqorders.biblionumber,
2446 ) AS receivedbiblios,
2449 aqorders.subscriptionid IS NOT NULL,
2450 aqorders.subscriptionid,
2453 ) AS is_linked_to_subscriptions,
2454 SUM(aqorders.quantityreceived) AS receiveditems
2456 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2457 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2458 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2459 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2460 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2461 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2462 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2467 if($args{supplierid}) {
2468 push @bind_strs, " aqinvoices.booksellerid = ? ";
2469 push @bind_args, $args{supplierid};
2471 if($args{invoicenumber}) {
2472 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2473 push @bind_args, "%$args{invoicenumber}%";
2475 if($args{suppliername}) {
2476 push @bind_strs, " aqbooksellers.name LIKE ? ";
2477 push @bind_args, "%$args{suppliername}%";
2479 if($args{shipmentdatefrom}) {
2480 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2481 push @bind_args, $args{shipmentdatefrom};
2483 if($args{shipmentdateto}) {
2484 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2485 push @bind_args, $args{shipmentdateto};
2487 if($args{billingdatefrom}) {
2488 push @bind_strs, " aqinvoices.billingdate >= ? ";
2489 push @bind_args, $args{billingdatefrom};
2491 if($args{billingdateto}) {
2492 push @bind_strs, " aqinvoices.billingdate <= ? ";
2493 push @bind_args, $args{billingdateto};
2495 if($args{isbneanissn}) {
2496 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2497 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2500 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2501 push @bind_args, $args{title};
2504 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2505 push @bind_args, $args{author};
2507 if($args{publisher}) {
2508 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2509 push @bind_args, $args{publisher};
2511 if($args{publicationyear}) {
2512 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2513 push @bind_args, $args{publicationyear}, $args{publicationyear};
2515 if($args{branchcode}) {
2516 push @bind_strs, " borrowers.branchcode = ? ";
2517 push @bind_args, $args{branchcode};
2519 if($args{message_id}) {
2520 push @bind_strs, " aqinvoices.message_id = ? ";
2521 push @bind_args, $args{message_id};
2524 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2525 $query .= " GROUP BY aqinvoices.invoiceid ";
2527 if($args{order_by}) {
2528 my ($column, $direction) = split / /, $args{order_by};
2529 if(grep /^$column$/, @columns) {
2530 $direction ||= 'ASC';
2531 $query .= " ORDER BY $column $direction";
2535 my $sth = $dbh->prepare($query);
2536 $sth->execute(@bind_args);
2538 my $results = $sth->fetchall_arrayref({});
2544 my $invoice = GetInvoice($invoiceid);
2546 Get informations about invoice with given $invoiceid
2548 Return a hash filled with aqinvoices.* fields
2553 my ($invoiceid) = @_;
2556 return unless $invoiceid;
2558 my $dbh = C4::Context->dbh;
2564 my $sth = $dbh->prepare($query);
2565 $sth->execute($invoiceid);
2567 $invoice = $sth->fetchrow_hashref;
2571 =head3 GetInvoiceDetails
2573 my $invoice = GetInvoiceDetails($invoiceid)
2575 Return informations about an invoice + the list of related order lines
2577 Orders informations are in $invoice->{orders} (array ref)
2581 sub GetInvoiceDetails {
2582 my ($invoiceid) = @_;
2584 if ( !defined $invoiceid ) {
2585 carp 'GetInvoiceDetails called without an invoiceid';
2589 my $dbh = C4::Context->dbh;
2591 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2593 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2596 my $sth = $dbh->prepare($query);
2597 $sth->execute($invoiceid);
2599 my $invoice = $sth->fetchrow_hashref;
2604 biblio.copyrightdate,
2605 biblioitems.publishercode,
2606 biblioitems.publicationyear,
2607 aqbasket.basketname,
2608 aqbasketgroups.id AS basketgroupid,
2609 aqbasketgroups.name AS basketgroupname
2611 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2612 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2613 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2614 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2617 $sth = $dbh->prepare($query);
2618 $sth->execute($invoiceid);
2619 $invoice->{orders} = $sth->fetchall_arrayref({});
2620 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2627 my $invoiceid = AddInvoice(
2628 invoicenumber => $invoicenumber,
2629 booksellerid => $booksellerid,
2630 shipmentdate => $shipmentdate,
2631 billingdate => $billingdate,
2632 closedate => $closedate,
2633 shipmentcost => $shipmentcost,
2634 shipmentcost_budgetid => $shipmentcost_budgetid
2637 Create a new invoice and return its id or undef if it fails.
2644 return unless(%invoice and $invoice{invoicenumber});
2646 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2647 closedate shipmentcost shipmentcost_budgetid message_id);
2651 foreach my $key (keys %invoice) {
2652 if(0 < grep(/^$key$/, @columns)) {
2653 push @set_strs, "$key = ?";
2654 push @set_args, ($invoice{$key} || undef);
2660 my $dbh = C4::Context->dbh;
2661 my $query = "INSERT INTO aqinvoices SET ";
2662 $query .= join (",", @set_strs);
2663 my $sth = $dbh->prepare($query);
2664 $rv = $sth->execute(@set_args);
2666 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2675 invoiceid => $invoiceid, # Mandatory
2676 invoicenumber => $invoicenumber,
2677 booksellerid => $booksellerid,
2678 shipmentdate => $shipmentdate,
2679 billingdate => $billingdate,
2680 closedate => $closedate,
2681 shipmentcost => $shipmentcost,
2682 shipmentcost_budgetid => $shipmentcost_budgetid
2685 Modify an invoice, invoiceid is mandatory.
2687 Return undef if it fails.
2694 return unless(%invoice and $invoice{invoiceid});
2696 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2697 closedate shipmentcost shipmentcost_budgetid);
2701 foreach my $key (keys %invoice) {
2702 if(0 < grep(/^$key$/, @columns)) {
2703 push @set_strs, "$key = ?";
2704 push @set_args, ($invoice{$key} || undef);
2708 my $dbh = C4::Context->dbh;
2709 my $query = "UPDATE aqinvoices SET ";
2710 $query .= join(",", @set_strs);
2711 $query .= " WHERE invoiceid = ?";
2713 my $sth = $dbh->prepare($query);
2714 $sth->execute(@set_args, $invoice{invoiceid});
2719 CloseInvoice($invoiceid);
2723 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2728 my ($invoiceid) = @_;
2730 return unless $invoiceid;
2732 my $dbh = C4::Context->dbh;
2735 SET closedate = CAST(NOW() AS DATE)
2738 my $sth = $dbh->prepare($query);
2739 $sth->execute($invoiceid);
2742 =head3 ReopenInvoice
2744 ReopenInvoice($invoiceid);
2748 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => output_pref({ dt=>dt_from_string, dateonly=>1, otputpref=>'iso' }))
2753 my ($invoiceid) = @_;
2755 return unless $invoiceid;
2757 my $dbh = C4::Context->dbh;
2760 SET closedate = NULL
2763 my $sth = $dbh->prepare($query);
2764 $sth->execute($invoiceid);
2769 DelInvoice($invoiceid);
2771 Delete an invoice if there are no items attached to it.
2776 my ($invoiceid) = @_;
2778 return unless $invoiceid;
2780 my $dbh = C4::Context->dbh;
2786 my $sth = $dbh->prepare($query);
2787 $sth->execute($invoiceid);
2788 my $res = $sth->fetchrow_arrayref;
2789 if ( $res && $res->[0] == 0 ) {
2791 DELETE FROM aqinvoices
2794 my $sth = $dbh->prepare($query);
2795 return ( $sth->execute($invoiceid) > 0 );
2800 =head3 MergeInvoices
2802 MergeInvoices($invoiceid, \@sourceids);
2804 Merge the invoices identified by the IDs in \@sourceids into
2805 the invoice identified by $invoiceid.
2810 my ($invoiceid, $sourceids) = @_;
2812 return unless $invoiceid;
2813 foreach my $sourceid (@$sourceids) {
2814 next if $sourceid == $invoiceid;
2815 my $source = GetInvoiceDetails($sourceid);
2816 foreach my $order (@{$source->{'orders'}}) {
2817 $order->{'invoiceid'} = $invoiceid;
2820 DelInvoice($source->{'invoiceid'});
2825 =head3 GetBiblioCountByBasketno
2827 $biblio_count = &GetBiblioCountByBasketno($basketno);
2829 Looks up the biblio's count that has basketno value $basketno
2835 sub GetBiblioCountByBasketno {
2836 my ($basketno) = @_;
2837 my $dbh = C4::Context->dbh;
2839 SELECT COUNT( DISTINCT( biblionumber ) )
2842 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2845 my $sth = $dbh->prepare($query);
2846 $sth->execute($basketno);
2847 return $sth->fetchrow;
2850 # Note this subroutine should be moved to Koha::Acquisition::Order
2851 # Will do when a DBIC decision will be taken.
2852 sub populate_order_with_prices {
2855 my $order = $params->{order};
2856 my $booksellerid = $params->{booksellerid};
2857 return unless $booksellerid;
2859 my $bookseller = Koha::Acquisition::Bookseller->fetch({ id => $booksellerid });
2861 my $receiving = $params->{receiving};
2862 my $ordering = $params->{ordering};
2863 my $discount = $order->{discount};
2864 $discount /= 100 if $discount > 1;
2867 if ( $bookseller->{listincgst} ) {
2868 # The user entered the rrp tax included
2869 $order->{rrp_tax_included} = $order->{rrp};
2871 # rrp tax excluded = rrp tax included / ( 1 + tax rate )
2872 $order->{rrp_tax_excluded} = $order->{rrp_tax_included} / ( 1 + $order->{tax_rate} );
2874 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
2875 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
2877 # ecost tax included = rrp tax included ( 1 - discount )
2878 $order->{ecost_tax_included} = $order->{rrp_tax_included} * ( 1 - $discount );
2881 # The user entered the rrp tax excluded
2882 $order->{rrp_tax_excluded} = $order->{rrp};
2884 # rrp tax included = rrp tax excluded * ( 1 - tax rate )
2885 $order->{rrp_tax_included} = $order->{rrp_tax_excluded} * ( 1 + $order->{tax_rate} );
2887 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
2888 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
2890 # ecost tax included = rrp tax excluded * ( 1 - tax rate ) * ( 1 - discount )
2891 $order->{ecost_tax_included} =
2892 $order->{rrp_tax_excluded} *
2893 ( 1 + $order->{tax_rate} ) *
2897 # tax value = quantity * ecost tax excluded * tax rate
2898 $order->{tax_value} = $order->{quantity} * $order->{ecost_tax_excluded} * $order->{tax_rate};
2902 if ( $bookseller->{invoiceincgst} ) {
2903 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
2904 # we need to keep the exact ecost value
2905 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_included} )->round ) {
2906 $order->{unitprice} = $order->{ecost_tax_included};
2909 # The user entered the unit price tax included
2910 $order->{unitprice_tax_included} = $order->{unitprice};
2912 # unit price tax excluded = unit price tax included / ( 1 + tax rate )
2913 $order->{unitprice_tax_excluded} = $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate} );
2916 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
2917 # we need to keep the exact ecost value
2918 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_excluded} )->round ) {
2919 $order->{unitprice} = $order->{ecost_tax_excluded};
2922 # The user entered the unit price tax excluded
2923 $order->{unitprice_tax_excluded} = $order->{unitprice};
2925 # unit price tax included = unit price tax included * ( 1 + tax rate )
2926 $order->{unitprice_tax_included} = $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate} );
2929 # tax value = quantity * unit price tax excluded * tax rate
2930 $order->{tax_value} = $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate};
2936 =head3 GetOrderUsers
2938 $order_users_ids = &GetOrderUsers($ordernumber);
2940 Returns a list of all borrowernumbers that are in order users list
2945 my ($ordernumber) = @_;
2947 return unless $ordernumber;
2950 SELECT borrowernumber
2952 WHERE ordernumber = ?
2954 my $dbh = C4::Context->dbh;
2955 my $sth = $dbh->prepare($query);
2956 $sth->execute($ordernumber);
2957 my $results = $sth->fetchall_arrayref( {} );
2959 my @borrowernumbers;
2960 foreach (@$results) {
2961 push @borrowernumbers, $_->{'borrowernumber'};
2964 return @borrowernumbers;
2967 =head3 ModOrderUsers
2969 my @order_users_ids = (1, 2, 3);
2970 &ModOrderUsers($ordernumber, @basketusers_ids);
2972 Delete all users from order users list, and add users in C<@order_users_ids>
2978 my ( $ordernumber, @order_users_ids ) = @_;
2980 return unless $ordernumber;
2982 my $dbh = C4::Context->dbh;
2984 DELETE FROM aqorder_users
2985 WHERE ordernumber = ?
2987 my $sth = $dbh->prepare($query);
2988 $sth->execute($ordernumber);
2991 INSERT INTO aqorder_users (ordernumber, borrowernumber)
2994 $sth = $dbh->prepare($query);
2995 foreach my $order_user_id (@order_users_ids) {
2996 $sth->execute( $ordernumber, $order_user_id );
3000 sub NotifyOrderUsers {
3001 my ($ordernumber) = @_;
3003 my @borrowernumbers = GetOrderUsers($ordernumber);
3004 return unless @borrowernumbers;
3006 my $order = GetOrder( $ordernumber );
3007 for my $borrowernumber (@borrowernumbers) {
3008 my $borrower = C4::Members::GetMember( borrowernumber => $borrowernumber );
3009 my $library = Koha::Libraries->find( $borrower->{branchcode} )->unblessed;
3010 my $biblio = C4::Biblio::GetBiblio( $order->{biblionumber} );
3011 my $letter = C4::Letters::GetPreparedLetter(
3012 module => 'acquisition',
3013 letter_code => 'ACQ_NOTIF_ON_RECEIV',
3014 branchcode => $library->{branchcode},
3016 'branches' => $library,
3017 'borrowers' => $borrower,
3018 'biblio' => $biblio,
3019 'aqorders' => $order,
3023 C4::Letters::EnqueueLetter(
3026 borrowernumber => $borrowernumber,
3027 LibraryName => C4::Context->preference("LibraryName"),
3028 message_transport_type => 'email',
3030 ) or warn "can't enqueue letter $letter";
3035 =head3 FillWithDefaultValues
3037 FillWithDefaultValues( $marc_record );
3039 This will update the record with default value defined in the ACQ framework.
3040 For all existing fields, if a default value exists and there are no subfield, it will be created.
3041 If the field does not exist, it will be created too.
3045 sub FillWithDefaultValues {
3047 my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ', { unsafe => 1 } );
3050 C4::Biblio::GetMarcFromKohaField( 'items.itemnumber', '' );
3051 for my $tag ( sort keys %$tagslib ) {
3053 next if $tag == $itemfield;
3054 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
3055 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
3056 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
3057 if ( defined $defaultvalue and $defaultvalue ne '' ) {
3058 my @fields = $record->field($tag);
3060 for my $field (@fields) {
3061 unless ( defined $field->subfield($subfield) ) {
3062 $field->add_subfields(
3063 $subfield => $defaultvalue );
3068 $record->insert_fields_ordered(
3070 $tag, '', '', $subfield => $defaultvalue
3085 Koha Development Team <http://koha-community.org/>