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 => $order->{rrp},
371 discount => $bookseller->{discount},
372 ecost => $order->{ecost},
373 notes => $order->{order_vendornote},
374 entrydate => $order->{entrydate},
375 booksellername => $bookseller->{name},
376 bookselleraddress => $bookseller->{address1},
377 booksellerpostal => $bookseller->{postal},
378 contractnumber => $contract->{contractnumber},
379 contractname => $contract->{contractname},
382 basketgroupdeliveryplace => $basketgroup->{deliveryplace},
383 basketgroupbillingplace => $basketgroup->{billingplace},
384 basketdeliveryplace => $basket->{deliveryplace},
385 basketbillingplace => $basket->{billingplace},
387 for my $place (qw( basketgroupdeliveryplace basketgroupbillingplace basketdeliveryplace basketbillingplace )) {
388 if ( my $library = Koha::Libraries->find( $temp->{$place} ) ) {
389 $row->{$place} = $library->branchname;
393 basketname author title publishercode collectiontitle notes
394 booksellername bookselleraddress booksellerpostal contractname
395 basketgroupdeliveryplace basketgroupbillingplace
396 basketdeliveryplace basketbillingplace
398 # Double the quotes to not be interpreted as a field end
399 $row->{$_} =~ s/"/""/g if $row->{$_};
404 $template->param(rows => \@rows);
406 return $template->output;
410 =head3 CloseBasketgroup
412 &CloseBasketgroup($basketgroupno);
418 sub CloseBasketgroup {
419 my ($basketgroupno) = @_;
420 my $dbh = C4::Context->dbh;
421 my $sth = $dbh->prepare("
422 UPDATE aqbasketgroups
426 $sth->execute($basketgroupno);
429 #------------------------------------------------------------#
431 =head3 ReOpenBaskergroup($basketgroupno)
433 &ReOpenBaskergroup($basketgroupno);
439 sub ReOpenBasketgroup {
440 my ($basketgroupno) = @_;
441 my $dbh = C4::Context->dbh;
442 my $sth = $dbh->prepare("
443 UPDATE aqbasketgroups
447 $sth->execute($basketgroupno);
450 #------------------------------------------------------------#
455 &DelBasket($basketno);
457 Deletes the basket that has basketno field $basketno in the aqbasket table.
461 =item C<$basketno> is the primary key of the basket in the aqbasket table.
468 my ( $basketno ) = @_;
469 my $query = "DELETE FROM aqbasket WHERE basketno=?";
470 my $dbh = C4::Context->dbh;
471 my $sth = $dbh->prepare($query);
472 $sth->execute($basketno);
476 #------------------------------------------------------------#
480 &ModBasket($basketinfo);
482 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
486 =item C<$basketno> is the primary key of the basket in the aqbasket table.
493 my $basketinfo = shift;
494 my $query = "UPDATE aqbasket SET ";
496 foreach my $key (keys %$basketinfo){
497 if ($key ne 'basketno'){
498 $query .= "$key=?, ";
499 push(@params, $basketinfo->{$key} || undef );
502 # get rid of the "," at the end of $query
503 if (substr($query, length($query)-2) eq ', '){
508 $query .= "WHERE basketno=?";
509 push(@params, $basketinfo->{'basketno'});
510 my $dbh = C4::Context->dbh;
511 my $sth = $dbh->prepare($query);
512 $sth->execute(@params);
517 #------------------------------------------------------------#
519 =head3 ModBasketHeader
521 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
523 Modifies a basket's header.
527 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
529 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
531 =item C<$note> is the "note" field in the "aqbasket" table;
533 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
535 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
537 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
539 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
541 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
543 =item C<$is_standing> is the "is_standing" field in the aqbasket table.
549 sub ModBasketHeader {
550 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing) = @_;
553 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?, is_standing=?
557 my $dbh = C4::Context->dbh;
558 my $sth = $dbh->prepare($query);
559 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $is_standing, $basketno);
561 if ( $contractnumber ) {
562 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
563 my $sth2 = $dbh->prepare($query2);
564 $sth2->execute($contractnumber,$basketno);
569 #------------------------------------------------------------#
571 =head3 GetBasketsByBookseller
573 @results = &GetBasketsByBookseller($booksellerid, $extra);
575 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
579 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
581 =item C<$extra> is the extra sql parameters, can be
583 $extra->{groupby}: group baskets by column
584 ex. $extra->{groupby} = aqbasket.basketgroupid
585 $extra->{orderby}: order baskets by column
586 $extra->{limit}: limit number of results (can be helpful for pagination)
592 sub GetBasketsByBookseller {
593 my ($booksellerid, $extra) = @_;
594 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
596 if ($extra->{groupby}) {
597 $query .= " GROUP by $extra->{groupby}";
599 if ($extra->{orderby}){
600 $query .= " ORDER by $extra->{orderby}";
602 if ($extra->{limit}){
603 $query .= " LIMIT $extra->{limit}";
606 my $dbh = C4::Context->dbh;
607 my $sth = $dbh->prepare($query);
608 $sth->execute($booksellerid);
609 return $sth->fetchall_arrayref({});
612 =head3 GetBasketsInfosByBookseller
614 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
616 The optional second parameter allbaskets is a boolean allowing you to
617 select all baskets from the supplier; by default only active baskets (open or
618 closed but still something to receive) are returned.
620 Returns in a arrayref of hashref all about booksellers baskets, plus:
621 total_biblios: Number of distinct biblios in basket
622 total_items: Number of items in basket
623 expected_items: Number of non-received items in basket
627 sub GetBasketsInfosByBookseller {
628 my ($supplierid, $allbaskets) = @_;
630 return unless $supplierid;
632 my $dbh = C4::Context->dbh;
635 SUM(aqorders.quantity) AS total_items,
637 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
638 ) AS total_items_cancelled,
639 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
641 IF(aqorders.datereceived IS NULL
642 AND aqorders.datecancellationprinted IS NULL
647 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
648 WHERE booksellerid = ?};
650 unless ( $allbaskets ) {
651 $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
653 $query.=" GROUP BY aqbasket.basketno";
655 my $sth = $dbh->prepare($query);
656 $sth->execute($supplierid);
657 my $baskets = $sth->fetchall_arrayref({});
659 # Retrieve the number of biblios cancelled
660 my $cancelled_biblios = $dbh->selectall_hashref( q|
661 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
663 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
664 WHERE booksellerid = ?
665 AND aqorders.orderstatus = 'cancelled'
666 GROUP BY aqbasket.basketno
667 |, 'basketno', {}, $supplierid );
669 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
675 =head3 GetBasketUsers
677 $basketusers_ids = &GetBasketUsers($basketno);
679 Returns a list of all borrowernumbers that are in basket users list
684 my $basketno = shift;
686 return unless $basketno;
689 SELECT borrowernumber
693 my $dbh = C4::Context->dbh;
694 my $sth = $dbh->prepare($query);
695 $sth->execute($basketno);
696 my $results = $sth->fetchall_arrayref( {} );
699 foreach (@$results) {
700 push @borrowernumbers, $_->{'borrowernumber'};
703 return @borrowernumbers;
706 =head3 ModBasketUsers
708 my @basketusers_ids = (1, 2, 3);
709 &ModBasketUsers($basketno, @basketusers_ids);
711 Delete all users from basket users list, and add users in C<@basketusers_ids>
717 my ($basketno, @basketusers_ids) = @_;
719 return unless $basketno;
721 my $dbh = C4::Context->dbh;
723 DELETE FROM aqbasketusers
726 my $sth = $dbh->prepare($query);
727 $sth->execute($basketno);
730 INSERT INTO aqbasketusers (basketno, borrowernumber)
733 $sth = $dbh->prepare($query);
734 foreach my $basketuser_id (@basketusers_ids) {
735 $sth->execute($basketno, $basketuser_id);
740 =head3 CanUserManageBasket
742 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
743 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
745 Check if a borrower can manage a basket, according to system preference
746 AcqViewBaskets, user permissions and basket properties (creator, users list,
749 First parameter can be either a borrowernumber or a hashref as returned by
750 C4::Members::GetMember.
752 Second parameter can be either a basketno or a hashref as returned by
753 C4::Acquisition::GetBasket.
755 The third parameter is optional. If given, it should be a hashref as returned
756 by C4::Auth::getuserflags. If not, getuserflags is called.
758 If user is authorised to manage basket, returns 1.
763 sub CanUserManageBasket {
764 my ($borrower, $basket, $userflags) = @_;
766 if (!ref $borrower) {
767 $borrower = C4::Members::GetMember(borrowernumber => $borrower);
770 $basket = GetBasket($basket);
773 return 0 unless ($basket and $borrower);
775 my $borrowernumber = $borrower->{borrowernumber};
776 my $basketno = $basket->{basketno};
778 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
780 if (!defined $userflags) {
781 my $dbh = C4::Context->dbh;
782 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
783 $sth->execute($borrowernumber);
784 my ($flags) = $sth->fetchrow_array;
787 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
790 unless ($userflags->{superlibrarian}
791 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
792 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
794 if (not exists $userflags->{acquisition}) {
798 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
799 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
803 if ($AcqViewBaskets eq 'user'
804 && $basket->{authorisedby} != $borrowernumber
805 && grep($borrowernumber, GetBasketUsers($basketno)) == 0) {
809 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
810 && $basket->{branch} ne $borrower->{branchcode}) {
818 #------------------------------------------------------------#
820 =head3 GetBasketsByBasketgroup
822 $baskets = &GetBasketsByBasketgroup($basketgroupid);
824 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
828 sub GetBasketsByBasketgroup {
829 my $basketgroupid = shift;
831 SELECT *, aqbasket.booksellerid as booksellerid
833 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
835 my $dbh = C4::Context->dbh;
836 my $sth = $dbh->prepare($query);
837 $sth->execute($basketgroupid);
838 return $sth->fetchall_arrayref({});
841 #------------------------------------------------------------#
843 =head3 NewBasketgroup
845 $basketgroupid = NewBasketgroup(\%hashref);
847 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
849 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
851 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
853 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
855 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
857 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
859 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
861 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
863 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
868 my $basketgroupinfo = shift;
869 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
870 my $query = "INSERT INTO aqbasketgroups (";
872 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
873 if ( defined $basketgroupinfo->{$field} ) {
874 $query .= "$field, ";
875 push(@params, $basketgroupinfo->{$field});
878 $query .= "booksellerid) VALUES (";
883 push(@params, $basketgroupinfo->{'booksellerid'});
884 my $dbh = C4::Context->dbh;
885 my $sth = $dbh->prepare($query);
886 $sth->execute(@params);
887 my $basketgroupid = $dbh->{'mysql_insertid'};
888 if( $basketgroupinfo->{'basketlist'} ) {
889 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
890 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
891 my $sth2 = $dbh->prepare($query2);
892 $sth2->execute($basketgroupid, $basketno);
895 return $basketgroupid;
898 #------------------------------------------------------------#
900 =head3 ModBasketgroup
902 ModBasketgroup(\%hashref);
904 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
906 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
908 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
910 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
912 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
914 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
916 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
918 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
920 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
925 my $basketgroupinfo = shift;
926 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
927 my $dbh = C4::Context->dbh;
928 my $query = "UPDATE aqbasketgroups SET ";
930 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
931 if ( defined $basketgroupinfo->{$field} ) {
932 $query .= "$field=?, ";
933 push(@params, $basketgroupinfo->{$field});
938 $query .= " WHERE id=?";
939 push(@params, $basketgroupinfo->{'id'});
940 my $sth = $dbh->prepare($query);
941 $sth->execute(@params);
943 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
944 $sth->execute($basketgroupinfo->{'id'});
946 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
947 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
948 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
949 $sth->execute($basketgroupinfo->{'id'}, $basketno);
955 #------------------------------------------------------------#
957 =head3 DelBasketgroup
959 DelBasketgroup($basketgroupid);
961 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
965 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
972 my $basketgroupid = shift;
973 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
974 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
975 my $dbh = C4::Context->dbh;
976 my $sth = $dbh->prepare($query);
977 $sth->execute($basketgroupid);
981 #------------------------------------------------------------#
984 =head2 FUNCTIONS ABOUT ORDERS
986 =head3 GetBasketgroup
988 $basketgroup = &GetBasketgroup($basketgroupid);
990 Returns a reference to the hash containing all information about the basketgroup.
995 my $basketgroupid = shift;
996 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
997 my $dbh = C4::Context->dbh;
998 my $result_set = $dbh->selectall_arrayref(
999 'SELECT * FROM aqbasketgroups WHERE id=?',
1003 return $result_set->[0]; # id is unique
1006 #------------------------------------------------------------#
1008 =head3 GetBasketgroups
1010 $basketgroups = &GetBasketgroups($booksellerid);
1012 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1016 sub GetBasketgroups {
1017 my $booksellerid = shift;
1018 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1019 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1020 my $dbh = C4::Context->dbh;
1021 my $sth = $dbh->prepare($query);
1022 $sth->execute($booksellerid);
1023 return $sth->fetchall_arrayref({});
1026 #------------------------------------------------------------#
1028 =head2 FUNCTIONS ABOUT ORDERS
1032 @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } );
1034 Looks up the pending (non-cancelled) orders with the given basket
1037 If cancelled is set, only cancelled orders will be returned.
1042 my ( $basketno, $params ) = @_;
1044 return () unless $basketno;
1046 my $orderby = $params->{orderby};
1047 my $cancelled = $params->{cancelled} || 0;
1049 my $dbh = C4::Context->dbh;
1051 SELECT biblio.*,biblioitems.*,
1055 $query .= $cancelled
1057 aqorders_transfers.ordernumber_to AS transferred_to,
1058 aqorders_transfers.timestamp AS transferred_to_timestamp
1061 aqorders_transfers.ordernumber_from AS transferred_from,
1062 aqorders_transfers.timestamp AS transferred_from_timestamp
1066 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1067 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1068 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1070 $query .= $cancelled
1072 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1075 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1083 $orderby ||= q|biblioitems.publishercode, biblio.title|;
1085 AND (datecancellationprinted IS NOT NULL
1086 AND datecancellationprinted <> '0000-00-00')
1091 q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|;
1093 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1097 $query .= " ORDER BY $orderby";
1099 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1104 #------------------------------------------------------------#
1106 =head3 GetOrdersByBiblionumber
1108 @orders = &GetOrdersByBiblionumber($biblionumber);
1110 Looks up the orders with linked to a specific $biblionumber, including
1111 cancelled orders and received orders.
1114 C<@orders> is an array of references-to-hash, whose keys are the
1115 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1119 sub GetOrdersByBiblionumber {
1120 my $biblionumber = shift;
1121 return unless $biblionumber;
1122 my $dbh = C4::Context->dbh;
1124 SELECT biblio.*,biblioitems.*,
1128 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1129 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1130 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1131 WHERE aqorders.biblionumber=?
1134 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1135 return @{$result_set};
1139 #------------------------------------------------------------#
1143 $order = &GetOrder($ordernumber);
1145 Looks up an order by order number.
1147 Returns a reference-to-hash describing the order. The keys of
1148 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1153 my ($ordernumber) = @_;
1154 return unless $ordernumber;
1156 my $dbh = C4::Context->dbh;
1157 my $query = qq{SELECT
1161 aqbasket.basketname,
1162 borrowers.branchcode,
1163 biblioitems.publicationyear,
1164 biblio.copyrightdate,
1165 biblioitems.editionstatement,
1169 biblioitems.publishercode,
1170 aqorders.rrp AS unitpricesupplier,
1171 aqorders.ecost AS unitpricelib,
1172 aqorders.claims_count AS claims_count,
1173 aqorders.claimed_date AS claimed_date,
1174 aqbudgets.budget_name AS budget,
1175 aqbooksellers.name AS supplier,
1176 aqbooksellers.id AS supplierid,
1177 biblioitems.publishercode AS publisher,
1178 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1179 DATE(aqbasket.closedate) AS orderdate,
1180 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1181 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1182 DATEDIFF(CURDATE( ),closedate) AS latesince
1183 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1184 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1185 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1186 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1187 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1188 WHERE aqorders.basketno = aqbasket.basketno
1191 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1193 # result_set assumed to contain 1 match
1194 return $result_set->[0];
1197 =head3 GetLastOrderNotReceivedFromSubscriptionid
1199 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1201 Returns a reference-to-hash describing the last order not received for a subscription.
1205 sub GetLastOrderNotReceivedFromSubscriptionid {
1206 my ( $subscriptionid ) = @_;
1207 my $dbh = C4::Context->dbh;
1209 SELECT * FROM aqorders
1210 LEFT JOIN subscription
1211 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1212 WHERE aqorders.subscriptionid = ?
1213 AND aqorders.datereceived IS NULL
1217 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1219 # result_set assumed to contain 1 match
1220 return $result_set->[0];
1223 =head3 GetLastOrderReceivedFromSubscriptionid
1225 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1227 Returns a reference-to-hash describing the last order received for a subscription.
1231 sub GetLastOrderReceivedFromSubscriptionid {
1232 my ( $subscriptionid ) = @_;
1233 my $dbh = C4::Context->dbh;
1235 SELECT * FROM aqorders
1236 LEFT JOIN subscription
1237 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1238 WHERE aqorders.subscriptionid = ?
1239 AND aqorders.datereceived =
1241 SELECT MAX( aqorders.datereceived )
1243 LEFT JOIN subscription
1244 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1245 WHERE aqorders.subscriptionid = ?
1246 AND aqorders.datereceived IS NOT NULL
1248 ORDER BY ordernumber DESC
1252 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1254 # result_set assumed to contain 1 match
1255 return $result_set->[0];
1259 #------------------------------------------------------------#
1263 &ModOrder(\%hashref);
1265 Modifies an existing order. Updates the order with order number
1266 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1267 other keys of the hash update the fields with the same name in the aqorders
1268 table of the Koha database.
1273 my $orderinfo = shift;
1275 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '';
1277 my $dbh = C4::Context->dbh;
1280 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1281 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1283 # delete($orderinfo->{'branchcode'});
1284 # the hash contains a lot of entries not in aqorders, so get the columns ...
1285 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1287 my $colnames = $sth->{NAME};
1288 #FIXME Be careful. If aqorders would have columns with diacritics,
1289 #you should need to decode what you get back from NAME.
1290 #See report 10110 and guided_reports.pl
1291 my $query = "UPDATE aqorders SET ";
1293 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1294 # ... and skip hash entries that are not in the aqorders table
1295 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1296 next unless grep(/^$orderinfokey$/, @$colnames);
1297 $query .= "$orderinfokey=?, ";
1298 push(@params, $orderinfo->{$orderinfokey});
1301 $query .= "timestamp=NOW() WHERE ordernumber=?";
1302 push(@params, $orderinfo->{'ordernumber'} );
1303 $sth = $dbh->prepare($query);
1304 $sth->execute(@params);
1308 #------------------------------------------------------------#
1312 ModItemOrder($itemnumber, $ordernumber);
1314 Modifies the ordernumber of an item in aqorders_items.
1319 my ($itemnumber, $ordernumber) = @_;
1321 return unless ($itemnumber and $ordernumber);
1323 my $dbh = C4::Context->dbh;
1325 UPDATE aqorders_items
1327 WHERE itemnumber = ?
1329 my $sth = $dbh->prepare($query);
1330 return $sth->execute($ordernumber, $itemnumber);
1333 #------------------------------------------------------------#
1335 =head3 ModReceiveOrder
1337 my ( $date_received, $new_ordernumber ) = ModReceiveOrder(
1339 biblionumber => $biblionumber,
1341 quantityreceived => $quantityreceived,
1343 invoice => $invoice,
1344 budget_id => $budget_id,
1345 received_itemnumbers => \@received_itemnumbers,
1346 order_internalnote => $order_internalnote,
1350 Updates an order, to reflect the fact that it was received, at least
1353 If a partial order is received, splits the order into two.
1355 Updates the order with biblionumber C<$biblionumber> and ordernumber
1356 C<$order->{ordernumber}>.
1361 sub ModReceiveOrder {
1363 my $biblionumber = $params->{biblionumber};
1364 my $order = { %{ $params->{order} } }; # Copy the order, we don't want to modify it
1365 my $invoice = $params->{invoice};
1366 my $quantrec = $params->{quantityreceived};
1367 my $user = $params->{user};
1368 my $budget_id = $params->{budget_id};
1369 my $received_items = $params->{received_items};
1371 my $dbh = C4::Context->dbh;
1372 my $datereceived = ( $invoice and $invoice->{datereceived} ) ? $invoice->{datereceived} : dt_from_string;
1373 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1374 if ($suggestionid) {
1375 ModSuggestion( {suggestionid=>$suggestionid,
1376 STATUS=>'AVAILABLE',
1377 biblionumber=> $biblionumber}
1381 my $new_ordernumber = $order->{ordernumber};
1382 if ( $order->{quantity} > $quantrec ) {
1383 # Split order line in two parts: the first is the original order line
1384 # without received items (the quantity is decreased),
1385 # the second part is a new order line with quantity=quantityrec
1386 # (entirely received)
1390 orderstatus = 'partial'|;
1391 $query .= q|, order_internalnote = ?| if defined $order->{order_internalnote};
1392 $query .= q| WHERE ordernumber = ?|;
1393 my $sth = $dbh->prepare($query);
1396 $order->{quantity} - $quantrec,
1397 ( defined $order->{order_internalnote} ? $order->{order_internalnote} : () ),
1398 $order->{ordernumber}
1401 delete $order->{ordernumber};
1402 $order->{budget_id} = ( $budget_id || $order->{budget_id} );
1403 $order->{quantity} = $quantrec;
1404 $order->{quantityreceived} = $quantrec;
1405 $order->{datereceived} = $datereceived;
1406 $order->{invoiceid} = $invoice->{invoiceid};
1407 $order->{orderstatus} = 'complete';
1408 $new_ordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber};
1410 if ($received_items) {
1411 foreach my $itemnumber (@$received_items) {
1412 ModItemOrder($itemnumber, $new_ordernumber);
1418 SET quantityreceived = ?,
1422 orderstatus = 'complete'
1426 , unitprice = ?, unitprice_tax_included = ?, unitprice_tax_excluded = ?
1427 | if defined $order->{unitprice};
1430 , rrp = ?, rrp_tax_included = ?, rrp_tax_excluded = ?
1431 | if defined $order->{rrp};
1434 , ecost = ?, ecost_tax_included = ?, ecost_tax_excluded = ?
1435 | if defined $order->{ecost};
1438 , order_internalnote = ?
1439 | if defined $order->{order_internalnote};
1441 $query .= q| where biblionumber=? and ordernumber=?|;
1443 my $sth = $dbh->prepare( $query );
1444 my @params = ( $quantrec, $datereceived, $invoice->{invoiceid}, $budget_id );
1446 if ( defined $order->{unitprice} ) {
1447 push @params, $order->{unitprice}, $order->{unitprice_tax_included}, $order->{unitprice_tax_excluded};
1449 if ( defined $order->{rrp} ) {
1450 push @params, $order->{rrp}, $order->{rrp_tax_included}, $order->{rrp_tax_excluded};
1452 if ( defined $order->{ecost} ) {
1453 push @params, $order->{ecost}, $order->{ecost_tax_included}, $order->{ecost_tax_excluded};
1455 if ( defined $order->{order_internalnote} ) {
1456 push @params, $order->{order_internalnote};
1459 push @params, ( $biblionumber, $order->{ordernumber} );
1461 $sth->execute( @params );
1463 # All items have been received, sent a notification to users
1464 NotifyOrderUsers( $order->{ordernumber} );
1467 return ($datereceived, $new_ordernumber);
1470 =head3 CancelReceipt
1472 my $parent_ordernumber = CancelReceipt($ordernumber);
1474 Cancel an order line receipt and update the parent order line, as if no
1476 If items are created at receipt (AcqCreateItem = receiving) then delete
1482 my $ordernumber = shift;
1484 return unless $ordernumber;
1486 my $dbh = C4::Context->dbh;
1488 SELECT datereceived, parent_ordernumber, quantity
1490 WHERE ordernumber = ?
1492 my $sth = $dbh->prepare($query);
1493 $sth->execute($ordernumber);
1494 my $order = $sth->fetchrow_hashref;
1496 warn "CancelReceipt: order $ordernumber does not exist";
1499 unless($order->{'datereceived'}) {
1500 warn "CancelReceipt: order $ordernumber is not received";
1504 my $parent_ordernumber = $order->{'parent_ordernumber'};
1506 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1508 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1509 # The order line has no parent, just mark it as not received
1512 SET quantityreceived = ?,
1515 orderstatus = 'ordered'
1516 WHERE ordernumber = ?
1518 $sth = $dbh->prepare($query);
1519 $sth->execute(0, undef, undef, $ordernumber);
1520 _cancel_items_receipt( $ordernumber );
1522 # The order line has a parent, increase parent quantity and delete
1525 SELECT quantity, datereceived
1527 WHERE ordernumber = ?
1529 $sth = $dbh->prepare($query);
1530 $sth->execute($parent_ordernumber);
1531 my $parent_order = $sth->fetchrow_hashref;
1532 unless($parent_order) {
1533 warn "Parent order $parent_ordernumber does not exist.";
1536 if($parent_order->{'datereceived'}) {
1537 warn "CancelReceipt: parent order is received.".
1538 " Can't cancel receipt.";
1544 orderstatus = 'ordered'
1545 WHERE ordernumber = ?
1547 $sth = $dbh->prepare($query);
1548 my $rv = $sth->execute(
1549 $order->{'quantity'} + $parent_order->{'quantity'},
1553 warn "Cannot update parent order line, so do not cancel".
1557 _cancel_items_receipt( $ordernumber, $parent_ordernumber );
1560 DELETE FROM aqorders
1561 WHERE ordernumber = ?
1563 $sth = $dbh->prepare($query);
1564 $sth->execute($ordernumber);
1568 if(C4::Context->preference('AcqCreateItem') eq 'ordering') {
1569 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1571 for my $in ( @itemnumbers ) {
1572 my $biblionumber = C4::Biblio::GetBiblionumberFromItemnumber( $in );
1573 my $frameworkcode = GetFrameworkCode($biblionumber);
1574 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber', $frameworkcode );
1575 my $item = C4::Items::GetMarcItem( $biblionumber, $in );
1576 for my $affect ( @affects ) {
1577 my ( $sf, $v ) = split q{=}, $affect, 2;
1578 foreach ( $item->field($itemfield) ) {
1579 $_->update( $sf => $v );
1582 C4::Items::ModItemFromMarc( $item, $biblionumber, $in );
1587 return $parent_ordernumber;
1590 sub _cancel_items_receipt {
1591 my ( $ordernumber, $parent_ordernumber ) = @_;
1592 $parent_ordernumber ||= $ordernumber;
1594 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1595 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1596 # Remove items that were created at receipt
1598 DELETE FROM items, aqorders_items
1599 USING items, aqorders_items
1600 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1602 my $dbh = C4::Context->dbh;
1603 my $sth = $dbh->prepare($query);
1604 foreach my $itemnumber (@itemnumbers) {
1605 $sth->execute($itemnumber, $itemnumber);
1609 foreach my $itemnumber (@itemnumbers) {
1610 ModItemOrder($itemnumber, $parent_ordernumber);
1615 #------------------------------------------------------------#
1619 @results = &SearchOrders({
1620 ordernumber => $ordernumber,
1622 biblionumber => $biblionumber,
1624 booksellerid => $booksellerid,
1625 basketno => $basketno,
1631 Searches for orders.
1633 C<$owner> Finds order for the logged in user.
1634 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1635 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1638 C<@results> is an array of references-to-hash with the keys are fields
1639 from aqorders, biblio, biblioitems and aqbasket tables.
1644 my ( $params ) = @_;
1645 my $ordernumber = $params->{ordernumber};
1646 my $search = $params->{search};
1647 my $ean = $params->{ean};
1648 my $booksellerid = $params->{booksellerid};
1649 my $basketno = $params->{basketno};
1650 my $basketname = $params->{basketname};
1651 my $basketgroupname = $params->{basketgroupname};
1652 my $owner = $params->{owner};
1653 my $pending = $params->{pending};
1654 my $ordered = $params->{ordered};
1655 my $biblionumber = $params->{biblionumber};
1656 my $budget_id = $params->{budget_id};
1658 my $dbh = C4::Context->dbh;
1661 SELECT aqbasket.basketno,
1663 borrowers.firstname,
1666 biblioitems.biblioitemnumber,
1667 aqbasket.authorisedby,
1668 aqbasket.booksellerid,
1670 aqbasket.creationdate,
1671 aqbasket.basketname,
1672 aqbasketgroups.id as basketgroupid,
1673 aqbasketgroups.name as basketgroupname,
1676 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1677 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1678 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1679 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1680 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1683 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1685 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1689 WHERE (datecancellationprinted is NULL)
1692 if ( $pending or $ordered ) {
1695 ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) )
1697 ( quantity > quantityreceived OR quantityreceived is NULL )
1701 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1709 my $userenv = C4::Context->userenv;
1710 if ( C4::Context->preference("IndependentBranches") ) {
1711 unless ( C4::Context->IsSuperLibrarian() ) {
1714 borrowers.branchcode = ?
1715 OR borrowers.branchcode = ''
1718 push @args, $userenv->{branch};
1722 if ( $ordernumber ) {
1723 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1724 push @args, ( $ordernumber, $ordernumber );
1726 if ( $biblionumber ) {
1727 $query .= 'AND aqorders.biblionumber = ?';
1728 push @args, $biblionumber;
1731 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1732 push @args, ("%$search%","%$search%","%$search%");
1735 $query .= ' AND biblioitems.ean = ?';
1738 if ( $booksellerid ) {
1739 $query .= 'AND aqbasket.booksellerid = ?';
1740 push @args, $booksellerid;
1743 $query .= 'AND aqbasket.basketno = ?';
1744 push @args, $basketno;
1747 $query .= 'AND aqbasket.basketname LIKE ?';
1748 push @args, "%$basketname%";
1750 if( $basketgroupname ) {
1751 $query .= ' AND aqbasketgroups.name LIKE ?';
1752 push @args, "%$basketgroupname%";
1756 $query .= ' AND aqbasket.authorisedby=? ';
1757 push @args, $userenv->{'number'};
1761 $query .= ' AND aqorders.budget_id = ?';
1762 push @args, $budget_id;
1765 $query .= ' ORDER BY aqbasket.basketno';
1767 my $sth = $dbh->prepare($query);
1768 $sth->execute(@args);
1769 return $sth->fetchall_arrayref({});
1772 #------------------------------------------------------------#
1776 &DelOrder($biblionumber, $ordernumber);
1778 Cancel the order with the given order and biblio numbers. It does not
1779 delete any entries in the aqorders table, it merely marks them as
1785 my ( $bibnum, $ordernumber, $delete_biblio, $reason ) = @_;
1788 my $dbh = C4::Context->dbh;
1791 SET datecancellationprinted=now(), orderstatus='cancelled'
1794 $query .= ", cancellationreason = ? ";
1797 WHERE biblionumber=? AND ordernumber=?
1799 my $sth = $dbh->prepare($query);
1801 $sth->execute($reason, $bibnum, $ordernumber);
1803 $sth->execute( $bibnum, $ordernumber );
1807 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1808 foreach my $itemnumber (@itemnumbers){
1809 my $delcheck = C4::Items::DelItemCheck( $bibnum, $itemnumber );
1811 if($delcheck != 1) {
1812 $error->{'delitem'} = 1;
1816 if($delete_biblio) {
1817 # We get the number of remaining items
1818 my $itemcount = C4::Items::GetItemsCount($bibnum);
1820 # If there are no items left,
1821 if ( $itemcount == 0 ) {
1822 # We delete the record
1823 my $delcheck = DelBiblio($bibnum);
1826 $error->{'delbiblio'} = 1;
1834 =head3 TransferOrder
1836 my $newordernumber = TransferOrder($ordernumber, $basketno);
1838 Transfer an order line to a basket.
1839 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1840 to BOOKSELLER on DATE' and create new order with internal note
1841 'Transferred from BOOKSELLER on DATE'.
1842 Move all attached items to the new order.
1843 Received orders cannot be transferred.
1844 Return the ordernumber of created order.
1849 my ($ordernumber, $basketno) = @_;
1851 return unless ($ordernumber and $basketno);
1853 my $order = GetOrder( $ordernumber );
1854 return if $order->{datereceived};
1855 my $basket = GetBasket($basketno);
1856 return unless $basket;
1858 my $dbh = C4::Context->dbh;
1859 my ($query, $sth, $rv);
1863 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1864 WHERE ordernumber = ?
1866 $sth = $dbh->prepare($query);
1867 $rv = $sth->execute('cancelled', $ordernumber);
1869 delete $order->{'ordernumber'};
1870 delete $order->{parent_ordernumber};
1871 $order->{'basketno'} = $basketno;
1873 my $newordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber};
1876 UPDATE aqorders_items
1878 WHERE ordernumber = ?
1880 $sth = $dbh->prepare($query);
1881 $sth->execute($newordernumber, $ordernumber);
1884 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1887 $sth = $dbh->prepare($query);
1888 $sth->execute($ordernumber, $newordernumber);
1890 return $newordernumber;
1893 =head2 FUNCTIONS ABOUT PARCELS
1897 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1899 get a lists of parcels.
1906 is the bookseller this function has to get parcels.
1909 To know on what criteria the results list has to be ordered.
1912 is the booksellerinvoicenumber.
1914 =item $datefrom & $dateto
1915 to know on what date this function has to filter its search.
1920 a pointer on a hash list containing parcel informations as such :
1926 =item Last operation
1928 =item Number of biblio
1930 =item Number of items
1937 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1938 my $dbh = C4::Context->dbh;
1939 my @query_params = ();
1941 SELECT aqinvoices.invoicenumber,
1942 datereceived,purchaseordernumber,
1943 count(DISTINCT biblionumber) AS biblio,
1944 sum(quantity) AS itemsexpected,
1945 sum(quantityreceived) AS itemsreceived
1946 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1947 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1948 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1950 push @query_params, $bookseller;
1952 if ( defined $code ) {
1953 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1954 # add a % to the end of the code to allow stemming.
1955 push @query_params, "$code%";
1958 if ( defined $datefrom ) {
1959 $strsth .= ' and datereceived >= ? ';
1960 push @query_params, $datefrom;
1963 if ( defined $dateto ) {
1964 $strsth .= 'and datereceived <= ? ';
1965 push @query_params, $dateto;
1968 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1970 # can't use a placeholder to place this column name.
1971 # but, we could probably be checking to make sure it is a column that will be fetched.
1972 $strsth .= "order by $order " if ($order);
1974 my $sth = $dbh->prepare($strsth);
1976 $sth->execute( @query_params );
1977 my $results = $sth->fetchall_arrayref({});
1981 #------------------------------------------------------------#
1983 =head3 GetLateOrders
1985 @results = &GetLateOrders;
1987 Searches for bookseller with late orders.
1990 the table of supplier with late issues. This table is full of hashref.
1996 my $supplierid = shift;
1998 my $estimateddeliverydatefrom = shift;
1999 my $estimateddeliverydateto = shift;
2001 my $dbh = C4::Context->dbh;
2003 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2004 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2006 my @query_params = ();
2008 SELECT aqbasket.basketno,
2009 aqorders.ordernumber,
2010 DATE(aqbasket.closedate) AS orderdate,
2011 aqbasket.basketname AS basketname,
2012 aqbasket.basketgroupid AS basketgroupid,
2013 aqbasketgroups.name AS basketgroupname,
2014 aqorders.rrp AS unitpricesupplier,
2015 aqorders.ecost AS unitpricelib,
2016 aqorders.claims_count AS claims_count,
2017 aqorders.claimed_date AS claimed_date,
2018 aqbudgets.budget_name AS budget,
2019 borrowers.branchcode AS branch,
2020 aqbooksellers.name AS supplier,
2021 aqbooksellers.id AS supplierid,
2022 biblio.author, biblio.title,
2023 biblioitems.publishercode AS publisher,
2024 biblioitems.publicationyear,
2025 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2029 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2030 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2031 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2032 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2033 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2034 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2035 WHERE aqorders.basketno = aqbasket.basketno
2036 AND ( datereceived = ''
2037 OR datereceived IS NULL
2038 OR aqorders.quantityreceived < aqorders.quantity
2040 AND aqbasket.closedate IS NOT NULL
2041 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2044 if ($dbdriver eq "mysql") {
2046 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2047 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2048 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2050 if ( defined $delay ) {
2051 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2052 push @query_params, $delay;
2055 HAVING quantity <> 0
2056 AND unitpricesupplier <> 0
2057 AND unitpricelib <> 0
2060 # FIXME: account for IFNULL as above
2062 aqorders.quantity AS quantity,
2063 aqorders.quantity * aqorders.rrp AS subtotal,
2064 (CAST(now() AS date) - closedate) AS latesince
2066 if ( defined $delay ) {
2067 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2068 push @query_params, $delay;
2071 if (defined $supplierid) {
2072 $from .= ' AND aqbasket.booksellerid = ? ';
2073 push @query_params, $supplierid;
2075 if (defined $branch) {
2076 $from .= ' AND borrowers.branchcode LIKE ? ';
2077 push @query_params, $branch;
2080 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2081 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2083 if ( defined $estimateddeliverydatefrom ) {
2084 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2085 push @query_params, $estimateddeliverydatefrom;
2087 if ( defined $estimateddeliverydateto ) {
2088 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2089 push @query_params, $estimateddeliverydateto;
2091 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2092 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2094 if (C4::Context->preference("IndependentBranches")
2095 && !C4::Context->IsSuperLibrarian() ) {
2096 $from .= ' AND borrowers.branchcode LIKE ? ';
2097 push @query_params, C4::Context->userenv->{branch};
2099 $from .= " AND orderstatus <> 'cancelled' ";
2100 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2101 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2102 my $sth = $dbh->prepare($query);
2103 $sth->execute(@query_params);
2105 while (my $data = $sth->fetchrow_hashref) {
2106 push @results, $data;
2111 #------------------------------------------------------------#
2115 \@order_loop = GetHistory( %params );
2117 Retreives some acquisition history information
2127 basket - search both basket name and number
2128 booksellerinvoicenumber
2131 orderstatus (note that orderstatus '' will retrieve orders
2132 of any status except cancelled)
2134 get_canceled_order (if set to a true value, cancelled orders will
2138 $order_loop is a list of hashrefs that each look like this:
2140 'author' => 'Twain, Mark',
2142 'biblionumber' => '215',
2144 'creationdate' => 'MM/DD/YYYY',
2145 'datereceived' => undef,
2148 'invoicenumber' => undef,
2150 'ordernumber' => '1',
2152 'quantityreceived' => undef,
2153 'title' => 'The Adventures of Huckleberry Finn'
2159 # don't run the query if there are no parameters (list would be too long for sure !)
2160 croak "No search params" unless @_;
2162 my $title = $params{title};
2163 my $author = $params{author};
2164 my $isbn = $params{isbn};
2165 my $ean = $params{ean};
2166 my $name = $params{name};
2167 my $from_placed_on = $params{from_placed_on};
2168 my $to_placed_on = $params{to_placed_on};
2169 my $basket = $params{basket};
2170 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2171 my $basketgroupname = $params{basketgroupname};
2172 my $budget = $params{budget};
2173 my $orderstatus = $params{orderstatus};
2174 my $biblionumber = $params{biblionumber};
2175 my $get_canceled_order = $params{get_canceled_order} || 0;
2176 my $ordernumber = $params{ordernumber};
2177 my $search_children_too = $params{search_children_too} || 0;
2178 my $created_by = $params{created_by} || [];
2182 my $total_qtyreceived = 0;
2183 my $total_price = 0;
2185 my $dbh = C4::Context->dbh;
2188 COALESCE(biblio.title, deletedbiblio.title) AS title,
2189 COALESCE(biblio.author, deletedbiblio.author) AS author,
2190 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2191 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2193 aqbasket.basketname,
2194 aqbasket.basketgroupid,
2195 aqbasket.authorisedby,
2196 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2197 aqbasketgroups.name as groupname,
2199 aqbasket.creationdate,
2200 aqorders.datereceived,
2202 aqorders.quantityreceived,
2204 aqorders.ordernumber,
2206 aqinvoices.invoicenumber,
2207 aqbooksellers.id as id,
2208 aqorders.biblionumber,
2209 aqorders.orderstatus,
2210 aqorders.parent_ordernumber,
2211 aqbudgets.budget_name
2213 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2216 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2217 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2218 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2219 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2220 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2221 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2222 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2223 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2224 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2225 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2228 $query .= " WHERE 1 ";
2230 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2231 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2234 my @query_params = ();
2236 if ( $biblionumber ) {
2237 $query .= " AND biblio.biblionumber = ?";
2238 push @query_params, $biblionumber;
2242 $query .= " AND biblio.title LIKE ? ";
2243 $title =~ s/\s+/%/g;
2244 push @query_params, "%$title%";
2248 $query .= " AND biblio.author LIKE ? ";
2249 push @query_params, "%$author%";
2253 $query .= " AND biblioitems.isbn LIKE ? ";
2254 push @query_params, "%$isbn%";
2257 $query .= " AND biblioitems.ean = ? ";
2258 push @query_params, "$ean";
2261 $query .= " AND aqbooksellers.name LIKE ? ";
2262 push @query_params, "%$name%";
2266 $query .= " AND aqbudgets.budget_id = ? ";
2267 push @query_params, "$budget";
2270 if ( $from_placed_on ) {
2271 $query .= " AND creationdate >= ? ";
2272 push @query_params, $from_placed_on;
2275 if ( $to_placed_on ) {
2276 $query .= " AND creationdate <= ? ";
2277 push @query_params, $to_placed_on;
2280 if ( defined $orderstatus and $orderstatus ne '') {
2281 $query .= " AND aqorders.orderstatus = ? ";
2282 push @query_params, "$orderstatus";
2286 if ($basket =~ m/^\d+$/) {
2287 $query .= " AND aqorders.basketno = ? ";
2288 push @query_params, $basket;
2290 $query .= " AND aqbasket.basketname LIKE ? ";
2291 push @query_params, "%$basket%";
2295 if ($booksellerinvoicenumber) {
2296 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2297 push @query_params, "%$booksellerinvoicenumber%";
2300 if ($basketgroupname) {
2301 $query .= " AND aqbasketgroups.name LIKE ? ";
2302 push @query_params, "%$basketgroupname%";
2306 $query .= " AND (aqorders.ordernumber = ? ";
2307 push @query_params, $ordernumber;
2308 if ($search_children_too) {
2309 $query .= " OR aqorders.parent_ordernumber = ? ";
2310 push @query_params, $ordernumber;
2315 if ( @$created_by ) {
2316 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2317 push @query_params, @$created_by;
2321 if ( C4::Context->preference("IndependentBranches") ) {
2322 unless ( C4::Context->IsSuperLibrarian() ) {
2323 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2324 push @query_params, C4::Context->userenv->{branch};
2327 $query .= " ORDER BY id";
2329 return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2332 =head2 GetRecentAcqui
2334 $results = GetRecentAcqui($days);
2336 C<$results> is a ref to a table which containts hashref
2340 sub GetRecentAcqui {
2342 my $dbh = C4::Context->dbh;
2346 ORDER BY timestamp DESC
2349 my $sth = $dbh->prepare($query);
2351 my $results = $sth->fetchall_arrayref({});
2355 #------------------------------------------------------------#
2359 &AddClaim($ordernumber);
2361 Add a claim for an order
2366 my ($ordernumber) = @_;
2367 my $dbh = C4::Context->dbh;
2370 claims_count = claims_count + 1,
2371 claimed_date = CURDATE()
2372 WHERE ordernumber = ?
2374 my $sth = $dbh->prepare($query);
2375 $sth->execute($ordernumber);
2380 my @invoices = GetInvoices(
2381 invoicenumber => $invoicenumber,
2382 supplierid => $supplierid,
2383 suppliername => $suppliername,
2384 shipmentdatefrom => $shipmentdatefrom, # ISO format
2385 shipmentdateto => $shipmentdateto, # ISO format
2386 billingdatefrom => $billingdatefrom, # ISO format
2387 billingdateto => $billingdateto, # ISO format
2388 isbneanissn => $isbn_or_ean_or_issn,
2391 publisher => $publisher,
2392 publicationyear => $publicationyear,
2393 branchcode => $branchcode,
2394 order_by => $order_by
2397 Return a list of invoices that match all given criteria.
2399 $order_by is "column_name (asc|desc)", where column_name is any of
2400 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2401 'shipmentcost', 'shipmentcost_budgetid'.
2403 asc is the default if omitted
2410 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2411 closedate shipmentcost shipmentcost_budgetid);
2413 my $dbh = C4::Context->dbh;
2415 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2418 aqorders.datereceived IS NOT NULL,
2419 aqorders.biblionumber,
2422 ) AS receivedbiblios,
2425 aqorders.subscriptionid IS NOT NULL,
2426 aqorders.subscriptionid,
2429 ) AS is_linked_to_subscriptions,
2430 SUM(aqorders.quantityreceived) AS receiveditems
2432 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2433 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2434 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2435 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2436 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2437 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2438 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2443 if($args{supplierid}) {
2444 push @bind_strs, " aqinvoices.booksellerid = ? ";
2445 push @bind_args, $args{supplierid};
2447 if($args{invoicenumber}) {
2448 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2449 push @bind_args, "%$args{invoicenumber}%";
2451 if($args{suppliername}) {
2452 push @bind_strs, " aqbooksellers.name LIKE ? ";
2453 push @bind_args, "%$args{suppliername}%";
2455 if($args{shipmentdatefrom}) {
2456 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2457 push @bind_args, $args{shipmentdatefrom};
2459 if($args{shipmentdateto}) {
2460 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2461 push @bind_args, $args{shipmentdateto};
2463 if($args{billingdatefrom}) {
2464 push @bind_strs, " aqinvoices.billingdate >= ? ";
2465 push @bind_args, $args{billingdatefrom};
2467 if($args{billingdateto}) {
2468 push @bind_strs, " aqinvoices.billingdate <= ? ";
2469 push @bind_args, $args{billingdateto};
2471 if($args{isbneanissn}) {
2472 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2473 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2476 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2477 push @bind_args, $args{title};
2480 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2481 push @bind_args, $args{author};
2483 if($args{publisher}) {
2484 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2485 push @bind_args, $args{publisher};
2487 if($args{publicationyear}) {
2488 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2489 push @bind_args, $args{publicationyear}, $args{publicationyear};
2491 if($args{branchcode}) {
2492 push @bind_strs, " borrowers.branchcode = ? ";
2493 push @bind_args, $args{branchcode};
2495 if($args{message_id}) {
2496 push @bind_strs, " aqinvoices.message_id = ? ";
2497 push @bind_args, $args{message_id};
2500 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2501 $query .= " GROUP BY aqinvoices.invoiceid ";
2503 if($args{order_by}) {
2504 my ($column, $direction) = split / /, $args{order_by};
2505 if(grep /^$column$/, @columns) {
2506 $direction ||= 'ASC';
2507 $query .= " ORDER BY $column $direction";
2511 my $sth = $dbh->prepare($query);
2512 $sth->execute(@bind_args);
2514 my $results = $sth->fetchall_arrayref({});
2520 my $invoice = GetInvoice($invoiceid);
2522 Get informations about invoice with given $invoiceid
2524 Return a hash filled with aqinvoices.* fields
2529 my ($invoiceid) = @_;
2532 return unless $invoiceid;
2534 my $dbh = C4::Context->dbh;
2540 my $sth = $dbh->prepare($query);
2541 $sth->execute($invoiceid);
2543 $invoice = $sth->fetchrow_hashref;
2547 =head3 GetInvoiceDetails
2549 my $invoice = GetInvoiceDetails($invoiceid)
2551 Return informations about an invoice + the list of related order lines
2553 Orders informations are in $invoice->{orders} (array ref)
2557 sub GetInvoiceDetails {
2558 my ($invoiceid) = @_;
2560 if ( !defined $invoiceid ) {
2561 carp 'GetInvoiceDetails called without an invoiceid';
2565 my $dbh = C4::Context->dbh;
2567 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2569 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2572 my $sth = $dbh->prepare($query);
2573 $sth->execute($invoiceid);
2575 my $invoice = $sth->fetchrow_hashref;
2580 biblio.copyrightdate,
2581 biblioitems.publishercode,
2582 biblioitems.publicationyear,
2583 aqbasket.basketname,
2584 aqbasketgroups.id AS basketgroupid,
2585 aqbasketgroups.name AS basketgroupname
2587 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2588 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2589 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2590 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2593 $sth = $dbh->prepare($query);
2594 $sth->execute($invoiceid);
2595 $invoice->{orders} = $sth->fetchall_arrayref({});
2596 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2603 my $invoiceid = AddInvoice(
2604 invoicenumber => $invoicenumber,
2605 booksellerid => $booksellerid,
2606 shipmentdate => $shipmentdate,
2607 billingdate => $billingdate,
2608 closedate => $closedate,
2609 shipmentcost => $shipmentcost,
2610 shipmentcost_budgetid => $shipmentcost_budgetid
2613 Create a new invoice and return its id or undef if it fails.
2620 return unless(%invoice and $invoice{invoicenumber});
2622 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2623 closedate shipmentcost shipmentcost_budgetid message_id);
2627 foreach my $key (keys %invoice) {
2628 if(0 < grep(/^$key$/, @columns)) {
2629 push @set_strs, "$key = ?";
2630 push @set_args, ($invoice{$key} || undef);
2636 my $dbh = C4::Context->dbh;
2637 my $query = "INSERT INTO aqinvoices SET ";
2638 $query .= join (",", @set_strs);
2639 my $sth = $dbh->prepare($query);
2640 $rv = $sth->execute(@set_args);
2642 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2651 invoiceid => $invoiceid, # Mandatory
2652 invoicenumber => $invoicenumber,
2653 booksellerid => $booksellerid,
2654 shipmentdate => $shipmentdate,
2655 billingdate => $billingdate,
2656 closedate => $closedate,
2657 shipmentcost => $shipmentcost,
2658 shipmentcost_budgetid => $shipmentcost_budgetid
2661 Modify an invoice, invoiceid is mandatory.
2663 Return undef if it fails.
2670 return unless(%invoice and $invoice{invoiceid});
2672 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2673 closedate shipmentcost shipmentcost_budgetid);
2677 foreach my $key (keys %invoice) {
2678 if(0 < grep(/^$key$/, @columns)) {
2679 push @set_strs, "$key = ?";
2680 push @set_args, ($invoice{$key} || undef);
2684 my $dbh = C4::Context->dbh;
2685 my $query = "UPDATE aqinvoices SET ";
2686 $query .= join(",", @set_strs);
2687 $query .= " WHERE invoiceid = ?";
2689 my $sth = $dbh->prepare($query);
2690 $sth->execute(@set_args, $invoice{invoiceid});
2695 CloseInvoice($invoiceid);
2699 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2704 my ($invoiceid) = @_;
2706 return unless $invoiceid;
2708 my $dbh = C4::Context->dbh;
2711 SET closedate = CAST(NOW() AS DATE)
2714 my $sth = $dbh->prepare($query);
2715 $sth->execute($invoiceid);
2718 =head3 ReopenInvoice
2720 ReopenInvoice($invoiceid);
2724 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => output_pref({ dt=>dt_from_string, dateonly=>1, otputpref=>'iso' }))
2729 my ($invoiceid) = @_;
2731 return unless $invoiceid;
2733 my $dbh = C4::Context->dbh;
2736 SET closedate = NULL
2739 my $sth = $dbh->prepare($query);
2740 $sth->execute($invoiceid);
2745 DelInvoice($invoiceid);
2747 Delete an invoice if there are no items attached to it.
2752 my ($invoiceid) = @_;
2754 return unless $invoiceid;
2756 my $dbh = C4::Context->dbh;
2762 my $sth = $dbh->prepare($query);
2763 $sth->execute($invoiceid);
2764 my $res = $sth->fetchrow_arrayref;
2765 if ( $res && $res->[0] == 0 ) {
2767 DELETE FROM aqinvoices
2770 my $sth = $dbh->prepare($query);
2771 return ( $sth->execute($invoiceid) > 0 );
2776 =head3 MergeInvoices
2778 MergeInvoices($invoiceid, \@sourceids);
2780 Merge the invoices identified by the IDs in \@sourceids into
2781 the invoice identified by $invoiceid.
2786 my ($invoiceid, $sourceids) = @_;
2788 return unless $invoiceid;
2789 foreach my $sourceid (@$sourceids) {
2790 next if $sourceid == $invoiceid;
2791 my $source = GetInvoiceDetails($sourceid);
2792 foreach my $order (@{$source->{'orders'}}) {
2793 $order->{'invoiceid'} = $invoiceid;
2796 DelInvoice($source->{'invoiceid'});
2801 =head3 GetBiblioCountByBasketno
2803 $biblio_count = &GetBiblioCountByBasketno($basketno);
2805 Looks up the biblio's count that has basketno value $basketno
2811 sub GetBiblioCountByBasketno {
2812 my ($basketno) = @_;
2813 my $dbh = C4::Context->dbh;
2815 SELECT COUNT( DISTINCT( biblionumber ) )
2818 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2821 my $sth = $dbh->prepare($query);
2822 $sth->execute($basketno);
2823 return $sth->fetchrow;
2826 # This is *not* the good way to calcul prices
2827 # But it's how it works at the moment into Koha
2828 # This will be fixed later.
2829 # Note this subroutine should be moved to Koha::Acquisition::Order
2830 # Will do when a DBIC decision will be taken.
2831 sub populate_order_with_prices {
2834 my $order = $params->{order};
2835 my $booksellerid = $params->{booksellerid};
2836 return unless $booksellerid;
2838 my $bookseller = Koha::Acquisition::Bookseller->fetch({ id => $booksellerid });
2840 my $receiving = $params->{receiving};
2841 my $ordering = $params->{ordering};
2842 my $discount = $order->{discount};
2843 $discount /= 100 if $discount > 1;
2845 $order->{rrp} = Koha::Number::Price->new( $order->{rrp} )->round;
2846 $order->{ecost} = Koha::Number::Price->new( $order->{ecost} )->round;
2848 if ( $bookseller->{listincgst} ) {
2849 $order->{rrp_tax_included} = $order->{rrp};
2850 $order->{rrp_tax_excluded} = Koha::Number::Price->new(
2851 $order->{rrp_tax_included} / ( 1 + $order->{tax_rate} ) )->round;
2852 $order->{ecost_tax_included} = $order->{ecost};
2853 $order->{ecost_tax_excluded} = Koha::Number::Price->new(
2854 $order->{ecost} / ( 1 + $order->{tax_rate} ) )->round;
2855 $order->{tax_value} = Koha::Number::Price->new(
2856 ( $order->{ecost_tax_included} - $order->{ecost_tax_excluded} ) *
2857 $order->{quantity} )->round;
2860 $order->{rrp_tax_excluded} = $order->{rrp};
2861 $order->{rrp_tax_included} = Koha::Number::Price->new(
2862 $order->{rrp} * ( 1 + $order->{tax_rate} ) )->round;
2863 $order->{ecost_tax_excluded} = $order->{ecost};
2864 $order->{ecost_tax_included} = Koha::Number::Price->new(
2865 $order->{ecost} * ( 1 + $order->{tax_rate} ) )->round;
2866 $order->{tax_value} = Koha::Number::Price->new(
2867 ( $order->{ecost_tax_included} - $order->{ecost_tax_excluded} ) *
2868 $order->{quantity} )->round;
2873 if ( $bookseller->{listincgst} ) {
2874 $order->{unitprice_tax_included} = Koha::Number::Price->new( $order->{unitprice} )->round;
2875 $order->{unitprice_tax_excluded} = Koha::Number::Price->new(
2876 $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate} ) )->round;
2879 $order->{unitprice_tax_excluded} = Koha::Number::Price->new( $order->{unitprice} )->round;
2880 $order->{unitprice_tax_included} = Koha::Number::Price->new(
2881 $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate} ) )->round;
2883 $order->{tax_value} = Koha::Number::Price->new(
2884 ( $order->{unitprice_tax_included} - $order->{unitprice_tax_excluded} )
2885 * $order->{quantityreceived} )->round;
2892 =head3 GetOrderUsers
2894 $order_users_ids = &GetOrderUsers($ordernumber);
2896 Returns a list of all borrowernumbers that are in order users list
2901 my ($ordernumber) = @_;
2903 return unless $ordernumber;
2906 SELECT borrowernumber
2908 WHERE ordernumber = ?
2910 my $dbh = C4::Context->dbh;
2911 my $sth = $dbh->prepare($query);
2912 $sth->execute($ordernumber);
2913 my $results = $sth->fetchall_arrayref( {} );
2915 my @borrowernumbers;
2916 foreach (@$results) {
2917 push @borrowernumbers, $_->{'borrowernumber'};
2920 return @borrowernumbers;
2923 =head3 ModOrderUsers
2925 my @order_users_ids = (1, 2, 3);
2926 &ModOrderUsers($ordernumber, @basketusers_ids);
2928 Delete all users from order users list, and add users in C<@order_users_ids>
2934 my ( $ordernumber, @order_users_ids ) = @_;
2936 return unless $ordernumber;
2938 my $dbh = C4::Context->dbh;
2940 DELETE FROM aqorder_users
2941 WHERE ordernumber = ?
2943 my $sth = $dbh->prepare($query);
2944 $sth->execute($ordernumber);
2947 INSERT INTO aqorder_users (ordernumber, borrowernumber)
2950 $sth = $dbh->prepare($query);
2951 foreach my $order_user_id (@order_users_ids) {
2952 $sth->execute( $ordernumber, $order_user_id );
2956 sub NotifyOrderUsers {
2957 my ($ordernumber) = @_;
2959 my @borrowernumbers = GetOrderUsers($ordernumber);
2960 return unless @borrowernumbers;
2962 my $order = GetOrder( $ordernumber );
2963 for my $borrowernumber (@borrowernumbers) {
2964 my $borrower = C4::Members::GetMember( borrowernumber => $borrowernumber );
2965 my $library = Koha::Libraries->find( $borrower->{branchcode} )->unblessed;
2966 my $biblio = C4::Biblio::GetBiblio( $order->{biblionumber} );
2967 my $letter = C4::Letters::GetPreparedLetter(
2968 module => 'acquisition',
2969 letter_code => 'ACQ_NOTIF_ON_RECEIV',
2970 branchcode => $library->{branchcode},
2972 'branches' => $library,
2973 'borrowers' => $borrower,
2974 'biblio' => $biblio,
2975 'aqorders' => $order,
2979 C4::Letters::EnqueueLetter(
2982 borrowernumber => $borrowernumber,
2983 LibraryName => C4::Context->preference("LibraryName"),
2984 message_transport_type => 'email',
2986 ) or warn "can't enqueue letter $letter";
2991 =head3 FillWithDefaultValues
2993 FillWithDefaultValues( $marc_record );
2995 This will update the record with default value defined in the ACQ framework.
2996 For all existing fields, if a default value exists and there are no subfield, it will be created.
2997 If the field does not exist, it will be created too.
3001 sub FillWithDefaultValues {
3003 my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ', { unsafe => 1 } );
3006 C4::Biblio::GetMarcFromKohaField( 'items.itemnumber', '' );
3007 for my $tag ( sort keys %$tagslib ) {
3009 next if $tag == $itemfield;
3010 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
3011 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
3012 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
3013 if ( defined $defaultvalue and $defaultvalue ne '' ) {
3014 my @fields = $record->field($tag);
3016 for my $field (@fields) {
3017 unless ( defined $field->subfield($subfield) ) {
3018 $field->add_subfields(
3019 $subfield => $defaultvalue );
3024 $record->insert_fields_ordered(
3026 $tag, '', '', $subfield => $defaultvalue
3041 Koha Development Team <http://koha-community.org/>