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;
35 use C4::Koha qw( subfield_is_koha_internal_p );
43 use vars qw($VERSION @ISA @EXPORT);
46 # set the version for version checking
47 $VERSION = 3.07.00.049;
51 &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
52 &GetBasketAsCSV &GetBasketGroupAsCSV
53 &GetBasketsByBookseller &GetBasketsByBasketgroup
54 &GetBasketsInfosByBookseller
56 &GetBasketUsers &ModBasketUsers
61 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
62 &GetBasketgroups &ReOpenBasketgroup
64 &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
65 &GetLateOrders &GetOrderFromItemnumber
66 &SearchOrders &GetHistory &GetRecentAcqui
67 &ModReceiveOrder &CancelReceipt
69 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
84 &GetItemnumbersFromOrder
87 &GetBiblioCountByBasketno
93 &FillWithDefaultValues
101 sub GetOrderFromItemnumber {
102 my ($itemnumber) = @_;
103 my $dbh = C4::Context->dbh;
106 SELECT * from aqorders LEFT JOIN aqorders_items
107 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
108 WHERE itemnumber = ? |;
110 my $sth = $dbh->prepare($query);
114 $sth->execute($itemnumber);
116 my $order = $sth->fetchrow_hashref;
121 # Returns the itemnumber(s) associated with the ordernumber given in parameter
122 sub GetItemnumbersFromOrder {
123 my ($ordernumber) = @_;
124 my $dbh = C4::Context->dbh;
125 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
126 my $sth = $dbh->prepare($query);
127 $sth->execute($ordernumber);
130 while (my $order = $sth->fetchrow_hashref) {
131 push @tab, $order->{'itemnumber'};
145 C4::Acquisition - Koha functions for dealing with orders and acquisitions
153 The functions in this module deal with acquisitions, managing book
154 orders, basket and parcels.
158 =head2 FUNCTIONS ABOUT BASKETS
162 $aqbasket = &GetBasket($basketnumber);
164 get all basket informations in aqbasket for a given basket
166 B<returns:> informations for a given basket returned as a hashref.
172 my $dbh = C4::Context->dbh;
175 concat( b.firstname,' ',b.surname) AS authorisedbyname
177 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
180 my $sth=$dbh->prepare($query);
181 $sth->execute($basketno);
182 my $basket = $sth->fetchrow_hashref;
186 #------------------------------------------------------------#
190 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
191 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace );
193 Create a new basket in aqbasket table
197 =item C<$booksellerid> is a foreign key in the aqbasket table
199 =item C<$authorizedby> is the username of who created the basket
203 The other parameters are optional, see ModBasketHeader for more info on them.
208 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
209 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
210 $billingplace ) = @_;
211 my $dbh = C4::Context->dbh;
213 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
214 . 'VALUES (now(),?,?)';
215 $dbh->do( $query, {}, $booksellerid, $authorisedby );
217 my $basket = $dbh->{mysql_insertid};
218 $basketname ||= q{}; # default to empty strings
220 $basketbooksellernote ||= q{};
221 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
222 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace );
226 #------------------------------------------------------------#
230 &CloseBasket($basketno);
232 close a basket (becomes unmodifiable, except for receives)
238 my $dbh = C4::Context->dbh;
244 my $sth = $dbh->prepare($query);
245 $sth->execute($basketno);
247 my @orders = GetOrders($basketno);
248 foreach my $order (@orders) {
251 SET orderstatus = 'ordered'
252 WHERE ordernumber = ?;
254 $sth = $dbh->prepare($query);
255 $sth->execute($order->{'ordernumber'});
261 &ReopenBasket($basketno);
269 my $dbh = C4::Context->dbh;
275 my $sth = $dbh->prepare($query);
276 $sth->execute($basketno);
278 my @orders = GetOrders($basketno);
279 foreach my $order (@orders) {
282 SET orderstatus = 'new'
283 WHERE ordernumber = ?;
285 $sth = $dbh->prepare($query);
286 $sth->execute($order->{'ordernumber'});
290 #------------------------------------------------------------#
292 =head3 GetBasketAsCSV
294 &GetBasketAsCSV($basketno);
296 Export a basket as CSV
298 $cgi parameter is needed for column name translation
303 my ($basketno, $cgi) = @_;
304 my $basket = GetBasket($basketno);
305 my @orders = GetOrders($basketno);
306 my $contract = GetContract({
307 contractnumber => $basket->{'contractnumber'}
310 my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
313 foreach my $order (@orders) {
314 my $bd = GetBiblioData( $order->{'biblionumber'} );
316 contractname => $contract->{'contractname'},
317 ordernumber => $order->{'ordernumber'},
318 entrydate => $order->{'entrydate'},
319 isbn => $order->{'isbn'},
320 author => $bd->{'author'},
321 title => $bd->{'title'},
322 publicationyear => $bd->{'publicationyear'},
323 publishercode => $bd->{'publishercode'},
324 collectiontitle => $bd->{'collectiontitle'},
325 notes => $order->{'order_vendornote'},
326 quantity => $order->{'quantity'},
327 rrp => $order->{'rrp'},
328 deliveryplace => C4::Branch::GetBranchName( $basket->{'deliveryplace'} ),
329 billingplace => C4::Branch::GetBranchName( $basket->{'billingplace'} ),
332 contractname author title publishercode collectiontitle notes
333 deliveryplace billingplace
335 # Double the quotes to not be interpreted as a field end
336 $row->{$_} =~ s/"/""/g if $row->{$_};
342 if(defined $a->{publishercode} and defined $b->{publishercode}) {
343 $a->{publishercode} cmp $b->{publishercode};
347 $template->param(rows => \@rows);
349 return $template->output;
353 =head3 GetBasketGroupAsCSV
355 &GetBasketGroupAsCSV($basketgroupid);
357 Export a basket group as CSV
359 $cgi parameter is needed for column name translation
363 sub GetBasketGroupAsCSV {
364 my ($basketgroupid, $cgi) = @_;
365 my $baskets = GetBasketsByBasketgroup($basketgroupid);
367 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
370 for my $basket (@$baskets) {
371 my @orders = GetOrders( $basket->{basketno} );
372 my $contract = GetContract({
373 contractnumber => $basket->{contractnumber}
375 my $bookseller = Koha::Acquisition::Bookseller->fetch({ id => $basket->{booksellerid} });
376 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
378 foreach my $order (@orders) {
379 my $bd = GetBiblioData( $order->{'biblionumber'} );
381 clientnumber => $bookseller->{accountnumber},
382 basketname => $basket->{basketname},
383 ordernumber => $order->{ordernumber},
384 author => $bd->{author},
385 title => $bd->{title},
386 publishercode => $bd->{publishercode},
387 publicationyear => $bd->{publicationyear},
388 collectiontitle => $bd->{collectiontitle},
389 isbn => $order->{isbn},
390 quantity => $order->{quantity},
391 rrp => $order->{rrp},
392 discount => $bookseller->{discount},
393 ecost => $order->{ecost},
394 notes => $order->{order_vendornote},
395 entrydate => $order->{entrydate},
396 booksellername => $bookseller->{name},
397 bookselleraddress => $bookseller->{address1},
398 booksellerpostal => $bookseller->{postal},
399 contractnumber => $contract->{contractnumber},
400 contractname => $contract->{contractname},
401 basketgroupdeliveryplace => C4::Branch::GetBranchName( $basketgroup->{deliveryplace} ),
402 basketgroupbillingplace => C4::Branch::GetBranchName( $basketgroup->{billingplace} ),
403 basketdeliveryplace => C4::Branch::GetBranchName( $basket->{deliveryplace} ),
404 basketbillingplace => C4::Branch::GetBranchName( $basket->{billingplace} ),
407 basketname author title publishercode collectiontitle notes
408 booksellername bookselleraddress booksellerpostal contractname
409 basketgroupdeliveryplace basketgroupbillingplace
410 basketdeliveryplace basketbillingplace
412 # Double the quotes to not be interpreted as a field end
413 $row->{$_} =~ s/"/""/g if $row->{$_};
418 $template->param(rows => \@rows);
420 return $template->output;
424 =head3 CloseBasketgroup
426 &CloseBasketgroup($basketgroupno);
432 sub CloseBasketgroup {
433 my ($basketgroupno) = @_;
434 my $dbh = C4::Context->dbh;
435 my $sth = $dbh->prepare("
436 UPDATE aqbasketgroups
440 $sth->execute($basketgroupno);
443 #------------------------------------------------------------#
445 =head3 ReOpenBaskergroup($basketgroupno)
447 &ReOpenBaskergroup($basketgroupno);
453 sub ReOpenBasketgroup {
454 my ($basketgroupno) = @_;
455 my $dbh = C4::Context->dbh;
456 my $sth = $dbh->prepare("
457 UPDATE aqbasketgroups
461 $sth->execute($basketgroupno);
464 #------------------------------------------------------------#
469 &DelBasket($basketno);
471 Deletes the basket that has basketno field $basketno in the aqbasket table.
475 =item C<$basketno> is the primary key of the basket in the aqbasket table.
482 my ( $basketno ) = @_;
483 my $query = "DELETE FROM aqbasket WHERE basketno=?";
484 my $dbh = C4::Context->dbh;
485 my $sth = $dbh->prepare($query);
486 $sth->execute($basketno);
490 #------------------------------------------------------------#
494 &ModBasket($basketinfo);
496 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
500 =item C<$basketno> is the primary key of the basket in the aqbasket table.
507 my $basketinfo = shift;
508 my $query = "UPDATE aqbasket SET ";
510 foreach my $key (keys %$basketinfo){
511 if ($key ne 'basketno'){
512 $query .= "$key=?, ";
513 push(@params, $basketinfo->{$key} || undef );
516 # get rid of the "," at the end of $query
517 if (substr($query, length($query)-2) eq ', '){
522 $query .= "WHERE basketno=?";
523 push(@params, $basketinfo->{'basketno'});
524 my $dbh = C4::Context->dbh;
525 my $sth = $dbh->prepare($query);
526 $sth->execute(@params);
531 #------------------------------------------------------------#
533 =head3 ModBasketHeader
535 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
537 Modifies a basket's header.
541 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
543 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
545 =item C<$note> is the "note" field in the "aqbasket" table;
547 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
549 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
551 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
553 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
555 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
561 sub ModBasketHeader {
562 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace) = @_;
565 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?
569 my $dbh = C4::Context->dbh;
570 my $sth = $dbh->prepare($query);
571 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $basketno);
573 if ( $contractnumber ) {
574 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
575 my $sth2 = $dbh->prepare($query2);
576 $sth2->execute($contractnumber,$basketno);
581 #------------------------------------------------------------#
583 =head3 GetBasketsByBookseller
585 @results = &GetBasketsByBookseller($booksellerid, $extra);
587 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
591 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
593 =item C<$extra> is the extra sql parameters, can be
595 $extra->{groupby}: group baskets by column
596 ex. $extra->{groupby} = aqbasket.basketgroupid
597 $extra->{orderby}: order baskets by column
598 $extra->{limit}: limit number of results (can be helpful for pagination)
604 sub GetBasketsByBookseller {
605 my ($booksellerid, $extra) = @_;
606 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
608 if ($extra->{groupby}) {
609 $query .= " GROUP by $extra->{groupby}";
611 if ($extra->{orderby}){
612 $query .= " ORDER by $extra->{orderby}";
614 if ($extra->{limit}){
615 $query .= " LIMIT $extra->{limit}";
618 my $dbh = C4::Context->dbh;
619 my $sth = $dbh->prepare($query);
620 $sth->execute($booksellerid);
621 return $sth->fetchall_arrayref({});
624 =head3 GetBasketsInfosByBookseller
626 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
628 The optional second parameter allbaskets is a boolean allowing you to
629 select all baskets from the supplier; by default only active baskets (open or
630 closed but still something to receive) are returned.
632 Returns in a arrayref of hashref all about booksellers baskets, plus:
633 total_biblios: Number of distinct biblios in basket
634 total_items: Number of items in basket
635 expected_items: Number of non-received items in basket
639 sub GetBasketsInfosByBookseller {
640 my ($supplierid, $allbaskets) = @_;
642 return unless $supplierid;
644 my $dbh = C4::Context->dbh;
647 SUM(aqorders.quantity) AS total_items,
649 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
650 ) AS total_items_cancelled,
651 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
653 IF(aqorders.datereceived IS NULL
654 AND aqorders.datecancellationprinted IS NULL
659 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
660 WHERE booksellerid = ?};
662 unless ( $allbaskets ) {
663 $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
665 $query.=" GROUP BY aqbasket.basketno";
667 my $sth = $dbh->prepare($query);
668 $sth->execute($supplierid);
669 my $baskets = $sth->fetchall_arrayref({});
671 # Retrieve the number of biblios cancelled
672 my $cancelled_biblios = $dbh->selectall_hashref( q|
673 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
675 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
676 WHERE booksellerid = ?
677 AND aqorders.orderstatus = 'cancelled'
678 GROUP BY aqbasket.basketno
679 |, 'basketno', {}, $supplierid );
681 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
687 =head3 GetBasketUsers
689 $basketusers_ids = &GetBasketUsers($basketno);
691 Returns a list of all borrowernumbers that are in basket users list
696 my $basketno = shift;
698 return unless $basketno;
701 SELECT borrowernumber
705 my $dbh = C4::Context->dbh;
706 my $sth = $dbh->prepare($query);
707 $sth->execute($basketno);
708 my $results = $sth->fetchall_arrayref( {} );
711 foreach (@$results) {
712 push @borrowernumbers, $_->{'borrowernumber'};
715 return @borrowernumbers;
718 =head3 ModBasketUsers
720 my @basketusers_ids = (1, 2, 3);
721 &ModBasketUsers($basketno, @basketusers_ids);
723 Delete all users from basket users list, and add users in C<@basketusers_ids>
729 my ($basketno, @basketusers_ids) = @_;
731 return unless $basketno;
733 my $dbh = C4::Context->dbh;
735 DELETE FROM aqbasketusers
738 my $sth = $dbh->prepare($query);
739 $sth->execute($basketno);
742 INSERT INTO aqbasketusers (basketno, borrowernumber)
745 $sth = $dbh->prepare($query);
746 foreach my $basketuser_id (@basketusers_ids) {
747 $sth->execute($basketno, $basketuser_id);
752 =head3 CanUserManageBasket
754 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
755 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
757 Check if a borrower can manage a basket, according to system preference
758 AcqViewBaskets, user permissions and basket properties (creator, users list,
761 First parameter can be either a borrowernumber or a hashref as returned by
762 C4::Members::GetMember.
764 Second parameter can be either a basketno or a hashref as returned by
765 C4::Acquisition::GetBasket.
767 The third parameter is optional. If given, it should be a hashref as returned
768 by C4::Auth::getuserflags. If not, getuserflags is called.
770 If user is authorised to manage basket, returns 1.
775 sub CanUserManageBasket {
776 my ($borrower, $basket, $userflags) = @_;
778 if (!ref $borrower) {
779 $borrower = C4::Members::GetMember(borrowernumber => $borrower);
782 $basket = GetBasket($basket);
785 return 0 unless ($basket and $borrower);
787 my $borrowernumber = $borrower->{borrowernumber};
788 my $basketno = $basket->{basketno};
790 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
792 if (!defined $userflags) {
793 my $dbh = C4::Context->dbh;
794 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
795 $sth->execute($borrowernumber);
796 my ($flags) = $sth->fetchrow_array;
799 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
802 unless ($userflags->{superlibrarian}
803 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
804 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
806 if (not exists $userflags->{acquisition}) {
810 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
811 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
815 if ($AcqViewBaskets eq 'user'
816 && $basket->{authorisedby} != $borrowernumber
817 && grep($borrowernumber, GetBasketUsers($basketno)) == 0) {
821 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
822 && $basket->{branch} ne $borrower->{branchcode}) {
830 #------------------------------------------------------------#
832 =head3 GetBasketsByBasketgroup
834 $baskets = &GetBasketsByBasketgroup($basketgroupid);
836 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
840 sub GetBasketsByBasketgroup {
841 my $basketgroupid = shift;
843 SELECT *, aqbasket.booksellerid as booksellerid
845 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
847 my $dbh = C4::Context->dbh;
848 my $sth = $dbh->prepare($query);
849 $sth->execute($basketgroupid);
850 return $sth->fetchall_arrayref({});
853 #------------------------------------------------------------#
855 =head3 NewBasketgroup
857 $basketgroupid = NewBasketgroup(\%hashref);
859 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
861 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
863 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
865 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
867 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
869 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
871 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
873 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
875 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
880 my $basketgroupinfo = shift;
881 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
882 my $query = "INSERT INTO aqbasketgroups (";
884 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
885 if ( defined $basketgroupinfo->{$field} ) {
886 $query .= "$field, ";
887 push(@params, $basketgroupinfo->{$field});
890 $query .= "booksellerid) VALUES (";
895 push(@params, $basketgroupinfo->{'booksellerid'});
896 my $dbh = C4::Context->dbh;
897 my $sth = $dbh->prepare($query);
898 $sth->execute(@params);
899 my $basketgroupid = $dbh->{'mysql_insertid'};
900 if( $basketgroupinfo->{'basketlist'} ) {
901 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
902 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
903 my $sth2 = $dbh->prepare($query2);
904 $sth2->execute($basketgroupid, $basketno);
907 return $basketgroupid;
910 #------------------------------------------------------------#
912 =head3 ModBasketgroup
914 ModBasketgroup(\%hashref);
916 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
918 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
920 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
922 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
924 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
926 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
928 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
930 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
932 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
937 my $basketgroupinfo = shift;
938 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
939 my $dbh = C4::Context->dbh;
940 my $query = "UPDATE aqbasketgroups SET ";
942 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
943 if ( defined $basketgroupinfo->{$field} ) {
944 $query .= "$field=?, ";
945 push(@params, $basketgroupinfo->{$field});
950 $query .= " WHERE id=?";
951 push(@params, $basketgroupinfo->{'id'});
952 my $sth = $dbh->prepare($query);
953 $sth->execute(@params);
955 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
956 $sth->execute($basketgroupinfo->{'id'});
958 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
959 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
960 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
961 $sth->execute($basketgroupinfo->{'id'}, $basketno);
967 #------------------------------------------------------------#
969 =head3 DelBasketgroup
971 DelBasketgroup($basketgroupid);
973 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
977 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
984 my $basketgroupid = shift;
985 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
986 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
987 my $dbh = C4::Context->dbh;
988 my $sth = $dbh->prepare($query);
989 $sth->execute($basketgroupid);
993 #------------------------------------------------------------#
996 =head2 FUNCTIONS ABOUT ORDERS
998 =head3 GetBasketgroup
1000 $basketgroup = &GetBasketgroup($basketgroupid);
1002 Returns a reference to the hash containing all information about the basketgroup.
1006 sub GetBasketgroup {
1007 my $basketgroupid = shift;
1008 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1009 my $dbh = C4::Context->dbh;
1010 my $result_set = $dbh->selectall_arrayref(
1011 'SELECT * FROM aqbasketgroups WHERE id=?',
1015 return $result_set->[0]; # id is unique
1018 #------------------------------------------------------------#
1020 =head3 GetBasketgroups
1022 $basketgroups = &GetBasketgroups($booksellerid);
1024 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1028 sub GetBasketgroups {
1029 my $booksellerid = shift;
1030 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1031 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1032 my $dbh = C4::Context->dbh;
1033 my $sth = $dbh->prepare($query);
1034 $sth->execute($booksellerid);
1035 return $sth->fetchall_arrayref({});
1038 #------------------------------------------------------------#
1040 =head2 FUNCTIONS ABOUT ORDERS
1044 @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } );
1046 Looks up the pending (non-cancelled) orders with the given basket
1049 If cancelled is set, only cancelled orders will be returned.
1054 my ( $basketno, $params ) = @_;
1056 return () unless $basketno;
1058 my $orderby = $params->{orderby};
1059 my $cancelled = $params->{cancelled} || 0;
1061 my $dbh = C4::Context->dbh;
1063 SELECT biblio.*,biblioitems.*,
1067 $query .= $cancelled
1069 aqorders_transfers.ordernumber_to AS transferred_to,
1070 aqorders_transfers.timestamp AS transferred_to_timestamp
1073 aqorders_transfers.ordernumber_from AS transferred_from,
1074 aqorders_transfers.timestamp AS transferred_from_timestamp
1078 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1079 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1080 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1082 $query .= $cancelled
1084 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1087 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1095 $orderby ||= q|biblioitems.publishercode, biblio.title|;
1097 AND (datecancellationprinted IS NOT NULL
1098 AND datecancellationprinted <> '0000-00-00')
1103 q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|;
1105 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1109 $query .= " ORDER BY $orderby";
1111 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1116 #------------------------------------------------------------#
1118 =head3 GetOrdersByBiblionumber
1120 @orders = &GetOrdersByBiblionumber($biblionumber);
1122 Looks up the orders with linked to a specific $biblionumber, including
1123 cancelled orders and received orders.
1126 C<@orders> is an array of references-to-hash, whose keys are the
1127 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1131 sub GetOrdersByBiblionumber {
1132 my $biblionumber = shift;
1133 return unless $biblionumber;
1134 my $dbh = C4::Context->dbh;
1136 SELECT biblio.*,biblioitems.*,
1140 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1141 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1142 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1143 WHERE aqorders.biblionumber=?
1146 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1147 return @{$result_set};
1151 #------------------------------------------------------------#
1155 $order = &GetOrder($ordernumber);
1157 Looks up an order by order number.
1159 Returns a reference-to-hash describing the order. The keys of
1160 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1165 my ($ordernumber) = @_;
1166 return unless $ordernumber;
1168 my $dbh = C4::Context->dbh;
1169 my $query = qq{SELECT
1173 aqbasket.basketname,
1174 borrowers.branchcode,
1175 biblioitems.publicationyear,
1176 biblio.copyrightdate,
1177 biblioitems.editionstatement,
1181 biblioitems.publishercode,
1182 aqorders.rrp AS unitpricesupplier,
1183 aqorders.ecost AS unitpricelib,
1184 aqorders.claims_count AS claims_count,
1185 aqorders.claimed_date AS claimed_date,
1186 aqbudgets.budget_name AS budget,
1187 aqbooksellers.name AS supplier,
1188 aqbooksellers.id AS supplierid,
1189 biblioitems.publishercode AS publisher,
1190 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1191 DATE(aqbasket.closedate) AS orderdate,
1192 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1193 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1194 DATEDIFF(CURDATE( ),closedate) AS latesince
1195 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1196 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1197 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1198 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1199 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1200 WHERE aqorders.basketno = aqbasket.basketno
1203 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1205 # result_set assumed to contain 1 match
1206 return $result_set->[0];
1209 =head3 GetLastOrderNotReceivedFromSubscriptionid
1211 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1213 Returns a reference-to-hash describing the last order not received for a subscription.
1217 sub GetLastOrderNotReceivedFromSubscriptionid {
1218 my ( $subscriptionid ) = @_;
1219 my $dbh = C4::Context->dbh;
1221 SELECT * FROM aqorders
1222 LEFT JOIN subscription
1223 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1224 WHERE aqorders.subscriptionid = ?
1225 AND aqorders.datereceived IS NULL
1229 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1231 # result_set assumed to contain 1 match
1232 return $result_set->[0];
1235 =head3 GetLastOrderReceivedFromSubscriptionid
1237 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1239 Returns a reference-to-hash describing the last order received for a subscription.
1243 sub GetLastOrderReceivedFromSubscriptionid {
1244 my ( $subscriptionid ) = @_;
1245 my $dbh = C4::Context->dbh;
1247 SELECT * FROM aqorders
1248 LEFT JOIN subscription
1249 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1250 WHERE aqorders.subscriptionid = ?
1251 AND aqorders.datereceived =
1253 SELECT MAX( aqorders.datereceived )
1255 LEFT JOIN subscription
1256 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1257 WHERE aqorders.subscriptionid = ?
1258 AND aqorders.datereceived IS NOT NULL
1260 ORDER BY ordernumber DESC
1264 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1266 # result_set assumed to contain 1 match
1267 return $result_set->[0];
1271 #------------------------------------------------------------#
1275 &ModOrder(\%hashref);
1277 Modifies an existing order. Updates the order with order number
1278 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1279 other keys of the hash update the fields with the same name in the aqorders
1280 table of the Koha database.
1285 my $orderinfo = shift;
1287 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ;
1288 die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq '';
1290 my $dbh = C4::Context->dbh;
1293 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1294 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1296 # delete($orderinfo->{'branchcode'});
1297 # the hash contains a lot of entries not in aqorders, so get the columns ...
1298 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1300 my $colnames = $sth->{NAME};
1301 #FIXME Be careful. If aqorders would have columns with diacritics,
1302 #you should need to decode what you get back from NAME.
1303 #See report 10110 and guided_reports.pl
1304 my $query = "UPDATE aqorders SET ";
1306 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1307 # ... and skip hash entries that are not in the aqorders table
1308 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1309 next unless grep(/^$orderinfokey$/, @$colnames);
1310 $query .= "$orderinfokey=?, ";
1311 push(@params, $orderinfo->{$orderinfokey});
1314 $query .= "timestamp=NOW() WHERE ordernumber=?";
1315 push(@params, $orderinfo->{'ordernumber'} );
1316 $sth = $dbh->prepare($query);
1317 $sth->execute(@params);
1321 #------------------------------------------------------------#
1325 ModItemOrder($itemnumber, $ordernumber);
1327 Modifies the ordernumber of an item in aqorders_items.
1332 my ($itemnumber, $ordernumber) = @_;
1334 return unless ($itemnumber and $ordernumber);
1336 my $dbh = C4::Context->dbh;
1338 UPDATE aqorders_items
1340 WHERE itemnumber = ?
1342 my $sth = $dbh->prepare($query);
1343 return $sth->execute($ordernumber, $itemnumber);
1346 #------------------------------------------------------------#
1348 =head3 ModReceiveOrder
1351 biblionumber => $biblionumber,
1352 ordernumber => $ordernumber,
1353 quantityreceived => $quantityreceived,
1357 invoiceid => $invoiceid,
1359 budget_id => $budget_id,
1360 datereceived => $datereceived,
1361 received_itemnumbers => \@received_itemnumbers,
1362 order_internalnote => $order_internalnote,
1363 order_vendornote => $order_vendornote,
1366 Updates an order, to reflect the fact that it was received, at least
1367 in part. All arguments not mentioned below update the fields with the
1368 same name in the aqorders table of the Koha database.
1370 If a partial order is received, splits the order into two.
1372 Updates the order with bibilionumber C<$biblionumber> and ordernumber
1378 sub ModReceiveOrder {
1379 my ( $params ) = @_;
1380 my $biblionumber = $params->{biblionumber};
1381 my $ordernumber = $params->{ordernumber};
1382 my $quantrec = $params->{quantityreceived};
1383 my $user = $params->{user};
1384 my $cost = $params->{cost};
1385 my $ecost = $params->{ecost};
1386 my $invoiceid = $params->{invoiceid};
1387 my $rrp = $params->{rrp};
1388 my $budget_id = $params->{budget_id};
1389 my $datereceived = $params->{datereceived};
1390 my $received_items = $params->{received_items};
1391 my $order_internalnote = $params->{order_internalnote};
1392 my $order_vendornote = $params->{order_vendornote};
1394 my $dbh = C4::Context->dbh;
1395 $datereceived = output_pref({ dt=>dt_from_string, dateonly=>1, dateformat=>'iso' }) unless $datereceived;
1396 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1397 if ($suggestionid) {
1398 ModSuggestion( {suggestionid=>$suggestionid,
1399 STATUS=>'AVAILABLE',
1400 biblionumber=> $biblionumber}
1404 my $result_set = $dbh->selectall_arrayref(
1405 q{SELECT * FROM aqorders WHERE biblionumber=? AND aqorders.ordernumber=?},
1406 { Slice => {} }, $biblionumber, $ordernumber
1409 # we assume we have a unique order
1410 my $order = $result_set->[0];
1412 my $new_ordernumber = $ordernumber;
1413 if ( $order->{quantity} > $quantrec ) {
1414 # Split order line in two parts: the first is the original order line
1415 # without received items (the quantity is decreased),
1416 # the second part is a new order line with quantity=quantityrec
1417 # (entirely received)
1421 orderstatus = 'partial'|;
1422 $query .= q|, order_internalnote = ?| if defined $order_internalnote;
1423 $query .= q|, order_vendornote = ?| if defined $order_vendornote;
1424 $query .= q| WHERE ordernumber = ?|;
1425 my $sth = $dbh->prepare($query);
1428 $order->{quantity} - $quantrec,
1429 ( defined $order_internalnote ? $order_internalnote : () ),
1430 ( defined $order_vendornote ? $order_vendornote : () ),
1434 delete $order->{'ordernumber'};
1435 $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} );
1436 $order->{'quantity'} = $quantrec;
1437 $order->{'quantityreceived'} = $quantrec;
1438 $order->{'datereceived'} = $datereceived;
1439 $order->{'invoiceid'} = $invoiceid;
1440 $order->{'unitprice'} = $cost;
1441 $order->{'rrp'} = $rrp;
1442 $order->{ecost} = $ecost;
1443 $order->{'orderstatus'} = 'complete';
1444 $new_ordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber};
1446 if ($received_items) {
1447 foreach my $itemnumber (@$received_items) {
1448 ModItemOrder($itemnumber, $new_ordernumber);
1454 set quantityreceived=?,datereceived=?,invoiceid=?,
1455 unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'|;
1456 $query .= q|, order_internalnote = ?| if defined $order_internalnote;
1457 $query .= q|, order_vendornote = ?| if defined $order_vendornote;
1458 $query .= q| where biblionumber=? and ordernumber=?|;
1459 my $sth = $dbh->prepare( $query );
1467 ( $budget_id ? $budget_id : $order->{budget_id} ),
1468 ( defined $order_internalnote ? $order_internalnote : () ),
1469 ( defined $order_vendornote ? $order_vendornote : () ),
1474 # All items have been received, sent a notification to users
1475 NotifyOrderUsers( $ordernumber );
1478 return ($datereceived, $new_ordernumber);
1481 =head3 CancelReceipt
1483 my $parent_ordernumber = CancelReceipt($ordernumber);
1485 Cancel an order line receipt and update the parent order line, as if no
1487 If items are created at receipt (AcqCreateItem = receiving) then delete
1493 my $ordernumber = shift;
1495 return unless $ordernumber;
1497 my $dbh = C4::Context->dbh;
1499 SELECT datereceived, parent_ordernumber, quantity
1501 WHERE ordernumber = ?
1503 my $sth = $dbh->prepare($query);
1504 $sth->execute($ordernumber);
1505 my $order = $sth->fetchrow_hashref;
1507 warn "CancelReceipt: order $ordernumber does not exist";
1510 unless($order->{'datereceived'}) {
1511 warn "CancelReceipt: order $ordernumber is not received";
1515 my $parent_ordernumber = $order->{'parent_ordernumber'};
1517 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1519 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1520 # The order line has no parent, just mark it as not received
1523 SET quantityreceived = ?,
1526 orderstatus = 'ordered'
1527 WHERE ordernumber = ?
1529 $sth = $dbh->prepare($query);
1530 $sth->execute(0, undef, undef, $ordernumber);
1531 _cancel_items_receipt( $ordernumber );
1533 # The order line has a parent, increase parent quantity and delete
1536 SELECT quantity, datereceived
1538 WHERE ordernumber = ?
1540 $sth = $dbh->prepare($query);
1541 $sth->execute($parent_ordernumber);
1542 my $parent_order = $sth->fetchrow_hashref;
1543 unless($parent_order) {
1544 warn "Parent order $parent_ordernumber does not exist.";
1547 if($parent_order->{'datereceived'}) {
1548 warn "CancelReceipt: parent order is received.".
1549 " Can't cancel receipt.";
1555 orderstatus = 'ordered'
1556 WHERE ordernumber = ?
1558 $sth = $dbh->prepare($query);
1559 my $rv = $sth->execute(
1560 $order->{'quantity'} + $parent_order->{'quantity'},
1564 warn "Cannot update parent order line, so do not cancel".
1568 _cancel_items_receipt( $ordernumber, $parent_ordernumber );
1571 DELETE FROM aqorders
1572 WHERE ordernumber = ?
1574 $sth = $dbh->prepare($query);
1575 $sth->execute($ordernumber);
1579 if(C4::Context->preference('AcqCreateItem') eq 'ordering') {
1580 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1582 for my $in ( @itemnumbers ) {
1583 my $biblionumber = C4::Biblio::GetBiblionumberFromItemnumber( $in );
1584 my $frameworkcode = GetFrameworkCode($biblionumber);
1585 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber', $frameworkcode );
1586 my $item = C4::Items::GetMarcItem( $biblionumber, $in );
1587 for my $affect ( @affects ) {
1588 my ( $sf, $v ) = split q{=}, $affect, 2;
1589 foreach ( $item->field($itemfield) ) {
1590 $_->update( $sf => $v );
1593 C4::Items::ModItemFromMarc( $item, $biblionumber, $in );
1598 return $parent_ordernumber;
1601 sub _cancel_items_receipt {
1602 my ( $ordernumber, $parent_ordernumber ) = @_;
1603 $parent_ordernumber ||= $ordernumber;
1605 my @itemnumbers = GetItemnumbersFromOrder($ordernumber);
1606 if(C4::Context->preference('AcqCreateItem') eq 'receiving') {
1607 # Remove items that were created at receipt
1609 DELETE FROM items, aqorders_items
1610 USING items, aqorders_items
1611 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1613 my $dbh = C4::Context->dbh;
1614 my $sth = $dbh->prepare($query);
1615 foreach my $itemnumber (@itemnumbers) {
1616 $sth->execute($itemnumber, $itemnumber);
1620 foreach my $itemnumber (@itemnumbers) {
1621 ModItemOrder($itemnumber, $parent_ordernumber);
1626 #------------------------------------------------------------#
1630 @results = &SearchOrders({
1631 ordernumber => $ordernumber,
1633 biblionumber => $biblionumber,
1635 booksellerid => $booksellerid,
1636 basketno => $basketno,
1642 Searches for orders.
1644 C<$owner> Finds order for the logged in user.
1645 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1646 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1649 C<@results> is an array of references-to-hash with the keys are fields
1650 from aqorders, biblio, biblioitems and aqbasket tables.
1655 my ( $params ) = @_;
1656 my $ordernumber = $params->{ordernumber};
1657 my $search = $params->{search};
1658 my $ean = $params->{ean};
1659 my $booksellerid = $params->{booksellerid};
1660 my $basketno = $params->{basketno};
1661 my $basketname = $params->{basketname};
1662 my $basketgroupname = $params->{basketgroupname};
1663 my $owner = $params->{owner};
1664 my $pending = $params->{pending};
1665 my $ordered = $params->{ordered};
1666 my $biblionumber = $params->{biblionumber};
1667 my $budget_id = $params->{budget_id};
1669 my $dbh = C4::Context->dbh;
1672 SELECT aqbasket.basketno,
1674 borrowers.firstname,
1677 biblioitems.biblioitemnumber,
1678 aqbasket.authorisedby,
1679 aqbasket.booksellerid,
1681 aqbasket.creationdate,
1682 aqbasket.basketname,
1683 aqbasketgroups.id as basketgroupid,
1684 aqbasketgroups.name as basketgroupname,
1687 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1688 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1689 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1690 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1691 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1694 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1696 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1700 WHERE (datecancellationprinted is NULL)
1703 if ( $pending or $ordered ) {
1704 $query .= q{ AND (quantity > quantityreceived OR quantityreceived is NULL)};
1707 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1710 my $userenv = C4::Context->userenv;
1711 if ( C4::Context->preference("IndependentBranches") ) {
1712 unless ( C4::Context->IsSuperLibrarian() ) {
1715 borrowers.branchcode = ?
1716 OR borrowers.branchcode = ''
1719 push @args, $userenv->{branch};
1723 if ( $ordernumber ) {
1724 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1725 push @args, ( $ordernumber, $ordernumber );
1727 if ( $biblionumber ) {
1728 $query .= 'AND aqorders.biblionumber = ?';
1729 push @args, $biblionumber;
1732 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1733 push @args, ("%$search%","%$search%","%$search%");
1736 $query .= ' AND biblioitems.ean = ?';
1739 if ( $booksellerid ) {
1740 $query .= 'AND aqbasket.booksellerid = ?';
1741 push @args, $booksellerid;
1744 $query .= 'AND aqbasket.basketno = ?';
1745 push @args, $basketno;
1748 $query .= 'AND aqbasket.basketname LIKE ?';
1749 push @args, "%$basketname%";
1751 if( $basketgroupname ) {
1752 $query .= ' AND aqbasketgroups.name LIKE ?';
1753 push @args, "%$basketgroupname%";
1757 $query .= ' AND aqbasket.authorisedby=? ';
1758 push @args, $userenv->{'number'};
1762 $query .= ' AND aqorders.budget_id = ?';
1763 push @args, $budget_id;
1766 $query .= ' ORDER BY aqbasket.basketno';
1768 my $sth = $dbh->prepare($query);
1769 $sth->execute(@args);
1770 return $sth->fetchall_arrayref({});
1773 #------------------------------------------------------------#
1777 &DelOrder($biblionumber, $ordernumber);
1779 Cancel the order with the given order and biblio numbers. It does not
1780 delete any entries in the aqorders table, it merely marks them as
1786 my ( $bibnum, $ordernumber, $delete_biblio, $reason ) = @_;
1789 my $dbh = C4::Context->dbh;
1792 SET datecancellationprinted=now(), orderstatus='cancelled'
1795 $query .= ", cancellationreason = ? ";
1798 WHERE biblionumber=? AND ordernumber=?
1800 my $sth = $dbh->prepare($query);
1802 $sth->execute($reason, $bibnum, $ordernumber);
1804 $sth->execute( $bibnum, $ordernumber );
1808 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1809 foreach my $itemnumber (@itemnumbers){
1810 my $delcheck = C4::Items::DelItemCheck( $dbh, $bibnum, $itemnumber );
1812 if($delcheck != 1) {
1813 $error->{'delitem'} = 1;
1817 if($delete_biblio) {
1818 # We get the number of remaining items
1819 my $itemcount = C4::Items::GetItemsCount($bibnum);
1821 # If there are no items left,
1822 if ( $itemcount == 0 ) {
1823 # We delete the record
1824 my $delcheck = DelBiblio($bibnum);
1827 $error->{'delbiblio'} = 1;
1835 =head3 TransferOrder
1837 my $newordernumber = TransferOrder($ordernumber, $basketno);
1839 Transfer an order line to a basket.
1840 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1841 to BOOKSELLER on DATE' and create new order with internal note
1842 'Transferred from BOOKSELLER on DATE'.
1843 Move all attached items to the new order.
1844 Received orders cannot be transferred.
1845 Return the ordernumber of created order.
1850 my ($ordernumber, $basketno) = @_;
1852 return unless ($ordernumber and $basketno);
1854 my $order = GetOrder( $ordernumber );
1855 return if $order->{datereceived};
1856 my $basket = GetBasket($basketno);
1857 return unless $basket;
1859 my $dbh = C4::Context->dbh;
1860 my ($query, $sth, $rv);
1864 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1865 WHERE ordernumber = ?
1867 $sth = $dbh->prepare($query);
1868 $rv = $sth->execute('cancelled', $ordernumber);
1870 delete $order->{'ordernumber'};
1871 delete $order->{parent_ordernumber};
1872 $order->{'basketno'} = $basketno;
1874 my $newordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber};
1877 UPDATE aqorders_items
1879 WHERE ordernumber = ?
1881 $sth = $dbh->prepare($query);
1882 $sth->execute($newordernumber, $ordernumber);
1885 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1888 $sth = $dbh->prepare($query);
1889 $sth->execute($ordernumber, $newordernumber);
1891 return $newordernumber;
1894 =head2 FUNCTIONS ABOUT PARCELS
1898 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
1900 get a lists of parcels.
1907 is the bookseller this function has to get parcels.
1910 To know on what criteria the results list has to be ordered.
1913 is the booksellerinvoicenumber.
1915 =item $datefrom & $dateto
1916 to know on what date this function has to filter its search.
1921 a pointer on a hash list containing parcel informations as such :
1927 =item Last operation
1929 =item Number of biblio
1931 =item Number of items
1938 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
1939 my $dbh = C4::Context->dbh;
1940 my @query_params = ();
1942 SELECT aqinvoices.invoicenumber,
1943 datereceived,purchaseordernumber,
1944 count(DISTINCT biblionumber) AS biblio,
1945 sum(quantity) AS itemsexpected,
1946 sum(quantityreceived) AS itemsreceived
1947 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
1948 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
1949 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
1951 push @query_params, $bookseller;
1953 if ( defined $code ) {
1954 $strsth .= ' and aqinvoices.invoicenumber like ? ';
1955 # add a % to the end of the code to allow stemming.
1956 push @query_params, "$code%";
1959 if ( defined $datefrom ) {
1960 $strsth .= ' and datereceived >= ? ';
1961 push @query_params, $datefrom;
1964 if ( defined $dateto ) {
1965 $strsth .= 'and datereceived <= ? ';
1966 push @query_params, $dateto;
1969 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
1971 # can't use a placeholder to place this column name.
1972 # but, we could probably be checking to make sure it is a column that will be fetched.
1973 $strsth .= "order by $order " if ($order);
1975 my $sth = $dbh->prepare($strsth);
1977 $sth->execute( @query_params );
1978 my $results = $sth->fetchall_arrayref({});
1982 #------------------------------------------------------------#
1984 =head3 GetLateOrders
1986 @results = &GetLateOrders;
1988 Searches for bookseller with late orders.
1991 the table of supplier with late issues. This table is full of hashref.
1997 my $supplierid = shift;
1999 my $estimateddeliverydatefrom = shift;
2000 my $estimateddeliverydateto = shift;
2002 my $dbh = C4::Context->dbh;
2004 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2005 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2007 my @query_params = ();
2009 SELECT aqbasket.basketno,
2010 aqorders.ordernumber,
2011 DATE(aqbasket.closedate) AS orderdate,
2012 aqbasket.basketname AS basketname,
2013 aqbasket.basketgroupid AS basketgroupid,
2014 aqbasketgroups.name AS basketgroupname,
2015 aqorders.rrp AS unitpricesupplier,
2016 aqorders.ecost AS unitpricelib,
2017 aqorders.claims_count AS claims_count,
2018 aqorders.claimed_date AS claimed_date,
2019 aqbudgets.budget_name AS budget,
2020 borrowers.branchcode AS branch,
2021 aqbooksellers.name AS supplier,
2022 aqbooksellers.id AS supplierid,
2023 biblio.author, biblio.title,
2024 biblioitems.publishercode AS publisher,
2025 biblioitems.publicationyear,
2026 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2030 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2031 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2032 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2033 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2034 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2035 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2036 WHERE aqorders.basketno = aqbasket.basketno
2037 AND ( datereceived = ''
2038 OR datereceived IS NULL
2039 OR aqorders.quantityreceived < aqorders.quantity
2041 AND aqbasket.closedate IS NOT NULL
2042 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2045 if ($dbdriver eq "mysql") {
2047 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2048 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2049 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2051 if ( defined $delay ) {
2052 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2053 push @query_params, $delay;
2056 HAVING quantity <> 0
2057 AND unitpricesupplier <> 0
2058 AND unitpricelib <> 0
2061 # FIXME: account for IFNULL as above
2063 aqorders.quantity AS quantity,
2064 aqorders.quantity * aqorders.rrp AS subtotal,
2065 (CAST(now() AS date) - closedate) AS latesince
2067 if ( defined $delay ) {
2068 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2069 push @query_params, $delay;
2072 if (defined $supplierid) {
2073 $from .= ' AND aqbasket.booksellerid = ? ';
2074 push @query_params, $supplierid;
2076 if (defined $branch) {
2077 $from .= ' AND borrowers.branchcode LIKE ? ';
2078 push @query_params, $branch;
2081 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2082 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2084 if ( defined $estimateddeliverydatefrom ) {
2085 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2086 push @query_params, $estimateddeliverydatefrom;
2088 if ( defined $estimateddeliverydateto ) {
2089 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2090 push @query_params, $estimateddeliverydateto;
2092 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2093 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2095 if (C4::Context->preference("IndependentBranches")
2096 && !C4::Context->IsSuperLibrarian() ) {
2097 $from .= ' AND borrowers.branchcode LIKE ? ';
2098 push @query_params, C4::Context->userenv->{branch};
2100 $from .= " AND orderstatus <> 'cancelled' ";
2101 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2102 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2103 my $sth = $dbh->prepare($query);
2104 $sth->execute(@query_params);
2106 while (my $data = $sth->fetchrow_hashref) {
2107 push @results, $data;
2112 #------------------------------------------------------------#
2116 \@order_loop = GetHistory( %params );
2118 Retreives some acquisition history information
2128 basket - search both basket name and number
2129 booksellerinvoicenumber
2132 orderstatus (note that orderstatus '' will retrieve orders
2133 of any status except cancelled)
2135 get_canceled_order (if set to a true value, cancelled orders will
2139 $order_loop is a list of hashrefs that each look like this:
2141 'author' => 'Twain, Mark',
2143 'biblionumber' => '215',
2145 'creationdate' => 'MM/DD/YYYY',
2146 'datereceived' => undef,
2149 'invoicenumber' => undef,
2151 'ordernumber' => '1',
2153 'quantityreceived' => undef,
2154 'title' => 'The Adventures of Huckleberry Finn'
2160 # don't run the query if there are no parameters (list would be too long for sure !)
2161 croak "No search params" unless @_;
2163 my $title = $params{title};
2164 my $author = $params{author};
2165 my $isbn = $params{isbn};
2166 my $ean = $params{ean};
2167 my $name = $params{name};
2168 my $from_placed_on = $params{from_placed_on};
2169 my $to_placed_on = $params{to_placed_on};
2170 my $basket = $params{basket};
2171 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2172 my $basketgroupname = $params{basketgroupname};
2173 my $budget = $params{budget};
2174 my $orderstatus = $params{orderstatus};
2175 my $biblionumber = $params{biblionumber};
2176 my $get_canceled_order = $params{get_canceled_order} || 0;
2177 my $ordernumber = $params{ordernumber};
2178 my $search_children_too = $params{search_children_too} || 0;
2179 my $created_by = $params{created_by} || [];
2183 my $total_qtyreceived = 0;
2184 my $total_price = 0;
2186 my $dbh = C4::Context->dbh;
2189 COALESCE(biblio.title, deletedbiblio.title) AS title,
2190 COALESCE(biblio.author, deletedbiblio.author) AS author,
2191 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2192 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2194 aqbasket.basketname,
2195 aqbasket.basketgroupid,
2196 aqbasket.authorisedby,
2197 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2198 aqbasketgroups.name as groupname,
2200 aqbasket.creationdate,
2201 aqorders.datereceived,
2203 aqorders.quantityreceived,
2205 aqorders.ordernumber,
2207 aqinvoices.invoicenumber,
2208 aqbooksellers.id as id,
2209 aqorders.biblionumber,
2210 aqorders.orderstatus,
2211 aqorders.parent_ordernumber,
2212 aqbudgets.budget_name
2214 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2217 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2218 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2219 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2220 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2221 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2222 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2223 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2224 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2225 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2226 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2229 $query .= " WHERE 1 ";
2231 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2232 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2235 my @query_params = ();
2237 if ( $biblionumber ) {
2238 $query .= " AND biblio.biblionumber = ?";
2239 push @query_params, $biblionumber;
2243 $query .= " AND biblio.title LIKE ? ";
2244 $title =~ s/\s+/%/g;
2245 push @query_params, "%$title%";
2249 $query .= " AND biblio.author LIKE ? ";
2250 push @query_params, "%$author%";
2254 $query .= " AND biblioitems.isbn LIKE ? ";
2255 push @query_params, "%$isbn%";
2258 $query .= " AND biblioitems.ean = ? ";
2259 push @query_params, "$ean";
2262 $query .= " AND aqbooksellers.name LIKE ? ";
2263 push @query_params, "%$name%";
2267 $query .= " AND aqbudgets.budget_id = ? ";
2268 push @query_params, "$budget";
2271 if ( $from_placed_on ) {
2272 $query .= " AND creationdate >= ? ";
2273 push @query_params, $from_placed_on;
2276 if ( $to_placed_on ) {
2277 $query .= " AND creationdate <= ? ";
2278 push @query_params, $to_placed_on;
2281 if ( defined $orderstatus and $orderstatus ne '') {
2282 $query .= " AND aqorders.orderstatus = ? ";
2283 push @query_params, "$orderstatus";
2287 if ($basket =~ m/^\d+$/) {
2288 $query .= " AND aqorders.basketno = ? ";
2289 push @query_params, $basket;
2291 $query .= " AND aqbasket.basketname LIKE ? ";
2292 push @query_params, "%$basket%";
2296 if ($booksellerinvoicenumber) {
2297 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2298 push @query_params, "%$booksellerinvoicenumber%";
2301 if ($basketgroupname) {
2302 $query .= " AND aqbasketgroups.name LIKE ? ";
2303 push @query_params, "%$basketgroupname%";
2307 $query .= " AND (aqorders.ordernumber = ? ";
2308 push @query_params, $ordernumber;
2309 if ($search_children_too) {
2310 $query .= " OR aqorders.parent_ordernumber = ? ";
2311 push @query_params, $ordernumber;
2316 if ( @$created_by ) {
2317 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2318 push @query_params, @$created_by;
2322 if ( C4::Context->preference("IndependentBranches") ) {
2323 unless ( C4::Context->IsSuperLibrarian() ) {
2324 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2325 push @query_params, C4::Context->userenv->{branch};
2328 $query .= " ORDER BY id";
2330 return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2333 =head2 GetRecentAcqui
2335 $results = GetRecentAcqui($days);
2337 C<$results> is a ref to a table which containts hashref
2341 sub GetRecentAcqui {
2343 my $dbh = C4::Context->dbh;
2347 ORDER BY timestamp DESC
2350 my $sth = $dbh->prepare($query);
2352 my $results = $sth->fetchall_arrayref({});
2356 #------------------------------------------------------------#
2360 &AddClaim($ordernumber);
2362 Add a claim for an order
2367 my ($ordernumber) = @_;
2368 my $dbh = C4::Context->dbh;
2371 claims_count = claims_count + 1,
2372 claimed_date = CURDATE()
2373 WHERE ordernumber = ?
2375 my $sth = $dbh->prepare($query);
2376 $sth->execute($ordernumber);
2381 my @invoices = GetInvoices(
2382 invoicenumber => $invoicenumber,
2383 supplierid => $supplierid,
2384 suppliername => $suppliername,
2385 shipmentdatefrom => $shipmentdatefrom, # ISO format
2386 shipmentdateto => $shipmentdateto, # ISO format
2387 billingdatefrom => $billingdatefrom, # ISO format
2388 billingdateto => $billingdateto, # ISO format
2389 isbneanissn => $isbn_or_ean_or_issn,
2392 publisher => $publisher,
2393 publicationyear => $publicationyear,
2394 branchcode => $branchcode,
2395 order_by => $order_by
2398 Return a list of invoices that match all given criteria.
2400 $order_by is "column_name (asc|desc)", where column_name is any of
2401 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2402 'shipmentcost', 'shipmentcost_budgetid'.
2404 asc is the default if omitted
2411 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2412 closedate shipmentcost shipmentcost_budgetid);
2414 my $dbh = C4::Context->dbh;
2416 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2419 aqorders.datereceived IS NOT NULL,
2420 aqorders.biblionumber,
2423 ) AS receivedbiblios,
2426 aqorders.subscriptionid IS NOT NULL,
2427 aqorders.subscriptionid,
2430 ) AS is_linked_to_subscriptions,
2431 SUM(aqorders.quantityreceived) AS receiveditems
2433 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2434 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2435 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2436 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2437 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2438 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2439 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2444 if($args{supplierid}) {
2445 push @bind_strs, " aqinvoices.booksellerid = ? ";
2446 push @bind_args, $args{supplierid};
2448 if($args{invoicenumber}) {
2449 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2450 push @bind_args, "%$args{invoicenumber}%";
2452 if($args{suppliername}) {
2453 push @bind_strs, " aqbooksellers.name LIKE ? ";
2454 push @bind_args, "%$args{suppliername}%";
2456 if($args{shipmentdatefrom}) {
2457 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2458 push @bind_args, $args{shipmentdatefrom};
2460 if($args{shipmentdateto}) {
2461 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2462 push @bind_args, $args{shipmentdateto};
2464 if($args{billingdatefrom}) {
2465 push @bind_strs, " aqinvoices.billingdate >= ? ";
2466 push @bind_args, $args{billingdatefrom};
2468 if($args{billingdateto}) {
2469 push @bind_strs, " aqinvoices.billingdate <= ? ";
2470 push @bind_args, $args{billingdateto};
2472 if($args{isbneanissn}) {
2473 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2474 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2477 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2478 push @bind_args, $args{title};
2481 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2482 push @bind_args, $args{author};
2484 if($args{publisher}) {
2485 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2486 push @bind_args, $args{publisher};
2488 if($args{publicationyear}) {
2489 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2490 push @bind_args, $args{publicationyear}, $args{publicationyear};
2492 if($args{branchcode}) {
2493 push @bind_strs, " borrowers.branchcode = ? ";
2494 push @bind_args, $args{branchcode};
2497 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2498 $query .= " GROUP BY aqinvoices.invoiceid ";
2500 if($args{order_by}) {
2501 my ($column, $direction) = split / /, $args{order_by};
2502 if(grep /^$column$/, @columns) {
2503 $direction ||= 'ASC';
2504 $query .= " ORDER BY $column $direction";
2508 my $sth = $dbh->prepare($query);
2509 $sth->execute(@bind_args);
2511 my $results = $sth->fetchall_arrayref({});
2517 my $invoice = GetInvoice($invoiceid);
2519 Get informations about invoice with given $invoiceid
2521 Return a hash filled with aqinvoices.* fields
2526 my ($invoiceid) = @_;
2529 return unless $invoiceid;
2531 my $dbh = C4::Context->dbh;
2537 my $sth = $dbh->prepare($query);
2538 $sth->execute($invoiceid);
2540 $invoice = $sth->fetchrow_hashref;
2544 =head3 GetInvoiceDetails
2546 my $invoice = GetInvoiceDetails($invoiceid)
2548 Return informations about an invoice + the list of related order lines
2550 Orders informations are in $invoice->{orders} (array ref)
2554 sub GetInvoiceDetails {
2555 my ($invoiceid) = @_;
2557 if ( !defined $invoiceid ) {
2558 carp 'GetInvoiceDetails called without an invoiceid';
2562 my $dbh = C4::Context->dbh;
2564 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2566 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2569 my $sth = $dbh->prepare($query);
2570 $sth->execute($invoiceid);
2572 my $invoice = $sth->fetchrow_hashref;
2577 biblio.copyrightdate,
2578 biblioitems.publishercode,
2579 biblioitems.publicationyear,
2580 aqbasket.basketname,
2581 aqbasketgroups.id AS basketgroupid,
2582 aqbasketgroups.name AS basketgroupname
2584 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2585 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2586 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2587 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2590 $sth = $dbh->prepare($query);
2591 $sth->execute($invoiceid);
2592 $invoice->{orders} = $sth->fetchall_arrayref({});
2593 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2600 my $invoiceid = AddInvoice(
2601 invoicenumber => $invoicenumber,
2602 booksellerid => $booksellerid,
2603 shipmentdate => $shipmentdate,
2604 billingdate => $billingdate,
2605 closedate => $closedate,
2606 shipmentcost => $shipmentcost,
2607 shipmentcost_budgetid => $shipmentcost_budgetid
2610 Create a new invoice and return its id or undef if it fails.
2617 return unless(%invoice and $invoice{invoicenumber});
2619 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2620 closedate shipmentcost shipmentcost_budgetid);
2624 foreach my $key (keys %invoice) {
2625 if(0 < grep(/^$key$/, @columns)) {
2626 push @set_strs, "$key = ?";
2627 push @set_args, ($invoice{$key} || undef);
2633 my $dbh = C4::Context->dbh;
2634 my $query = "INSERT INTO aqinvoices SET ";
2635 $query .= join (",", @set_strs);
2636 my $sth = $dbh->prepare($query);
2637 $rv = $sth->execute(@set_args);
2639 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2648 invoiceid => $invoiceid, # Mandatory
2649 invoicenumber => $invoicenumber,
2650 booksellerid => $booksellerid,
2651 shipmentdate => $shipmentdate,
2652 billingdate => $billingdate,
2653 closedate => $closedate,
2654 shipmentcost => $shipmentcost,
2655 shipmentcost_budgetid => $shipmentcost_budgetid
2658 Modify an invoice, invoiceid is mandatory.
2660 Return undef if it fails.
2667 return unless(%invoice and $invoice{invoiceid});
2669 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2670 closedate shipmentcost shipmentcost_budgetid);
2674 foreach my $key (keys %invoice) {
2675 if(0 < grep(/^$key$/, @columns)) {
2676 push @set_strs, "$key = ?";
2677 push @set_args, ($invoice{$key} || undef);
2681 my $dbh = C4::Context->dbh;
2682 my $query = "UPDATE aqinvoices SET ";
2683 $query .= join(",", @set_strs);
2684 $query .= " WHERE invoiceid = ?";
2686 my $sth = $dbh->prepare($query);
2687 $sth->execute(@set_args, $invoice{invoiceid});
2692 CloseInvoice($invoiceid);
2696 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2701 my ($invoiceid) = @_;
2703 return unless $invoiceid;
2705 my $dbh = C4::Context->dbh;
2708 SET closedate = CAST(NOW() AS DATE)
2711 my $sth = $dbh->prepare($query);
2712 $sth->execute($invoiceid);
2715 =head3 ReopenInvoice
2717 ReopenInvoice($invoiceid);
2721 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => output_pref({ dt=>dt_from_string, dateonly=>1, otputpref=>'iso' }))
2726 my ($invoiceid) = @_;
2728 return unless $invoiceid;
2730 my $dbh = C4::Context->dbh;
2733 SET closedate = NULL
2736 my $sth = $dbh->prepare($query);
2737 $sth->execute($invoiceid);
2742 DelInvoice($invoiceid);
2744 Delete an invoice if there are no items attached to it.
2749 my ($invoiceid) = @_;
2751 return unless $invoiceid;
2753 my $dbh = C4::Context->dbh;
2759 my $sth = $dbh->prepare($query);
2760 $sth->execute($invoiceid);
2761 my $res = $sth->fetchrow_arrayref;
2762 if ( $res && $res->[0] == 0 ) {
2764 DELETE FROM aqinvoices
2767 my $sth = $dbh->prepare($query);
2768 return ( $sth->execute($invoiceid) > 0 );
2773 =head3 MergeInvoices
2775 MergeInvoices($invoiceid, \@sourceids);
2777 Merge the invoices identified by the IDs in \@sourceids into
2778 the invoice identified by $invoiceid.
2783 my ($invoiceid, $sourceids) = @_;
2785 return unless $invoiceid;
2786 foreach my $sourceid (@$sourceids) {
2787 next if $sourceid == $invoiceid;
2788 my $source = GetInvoiceDetails($sourceid);
2789 foreach my $order (@{$source->{'orders'}}) {
2790 $order->{'invoiceid'} = $invoiceid;
2793 DelInvoice($source->{'invoiceid'});
2798 =head3 GetBiblioCountByBasketno
2800 $biblio_count = &GetBiblioCountByBasketno($basketno);
2802 Looks up the biblio's count that has basketno value $basketno
2808 sub GetBiblioCountByBasketno {
2809 my ($basketno) = @_;
2810 my $dbh = C4::Context->dbh;
2812 SELECT COUNT( DISTINCT( biblionumber ) )
2815 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2818 my $sth = $dbh->prepare($query);
2819 $sth->execute($basketno);
2820 return $sth->fetchrow;
2823 # This is *not* the good way to calcul prices
2824 # But it's how it works at the moment into Koha
2825 # This will be fixed later.
2826 # Note this subroutine should be moved to Koha::Acquisition::Order
2827 # Will do when a DBIC decision will be taken.
2828 sub populate_order_with_prices {
2831 my $order = $params->{order};
2832 my $booksellerid = $params->{booksellerid};
2833 return unless $booksellerid;
2835 my $bookseller = Koha::Acquisition::Bookseller->fetch({ id => $booksellerid });
2837 my $receiving = $params->{receiving};
2838 my $ordering = $params->{ordering};
2839 my $discount = $order->{discount};
2840 $discount /= 100 if $discount > 1;
2842 $order->{rrp} = Koha::Number::Price->new( $order->{rrp} )->round;
2843 $order->{ecost} = Koha::Number::Price->new( $order->{ecost} )->round;
2845 if ( $bookseller->{listincgst} ) {
2846 $order->{rrpgsti} = $order->{rrp};
2847 $order->{rrpgste} = Koha::Number::Price->new(
2848 $order->{rrpgsti} / ( 1 + $order->{gstrate} ) )->round;
2849 $order->{ecostgsti} = $order->{ecost};
2850 $order->{ecostgste} = Koha::Number::Price->new(
2851 $order->{ecost} / ( 1 + $order->{gstrate} ) )->round;
2852 $order->{gstvalue} = Koha::Number::Price->new(
2853 ( $order->{ecostgsti} - $order->{ecostgste} ) *
2854 $order->{quantity} )->round;
2855 $order->{totalgste} = $order->{ecostgste} * $order->{quantity};
2856 $order->{totalgsti} = $order->{ecostgsti} * $order->{quantity};
2859 $order->{rrpgste} = $order->{rrp};
2860 $order->{rrpgsti} = Koha::Number::Price->new(
2861 $order->{rrp} * ( 1 + $order->{gstrate} ) )->round;
2862 $order->{ecostgste} = $order->{ecost};
2863 $order->{ecostgsti} = Koha::Number::Price->new(
2864 $order->{ecost} * ( 1 + $order->{gstrate} ) )->round;
2865 $order->{gstvalue} = Koha::Number::Price->new(
2866 ( $order->{ecostgsti} - $order->{ecostgste} ) *
2867 $order->{quantity} )->round;
2868 $order->{totalgste} = $order->{ecostgste} * $order->{quantity};
2869 $order->{totalgsti} = $order->{ecostgsti} * $order->{quantity};
2874 if ( $bookseller->{listincgst} ) {
2875 $order->{unitpricegsti} = Koha::Number::Price->new( $order->{unitprice} )->round;
2876 $order->{unitpricegste} = Koha::Number::Price->new(
2877 $order->{unitpricegsti} / ( 1 + $order->{gstrate} ) )->round;
2880 $order->{unitpricegste} = Koha::Number::Price->new( $order->{unitprice} )->round;
2881 $order->{unitpricegsti} = Koha::Number::Price->new(
2882 $order->{unitpricegste} * ( 1 + $order->{gstrate} ) )->round;
2884 $order->{gstvalue} = Koha::Number::Price->new(
2885 ( $order->{unitpricegsti} - $order->{unitpricegste} )
2886 * $order->{quantityreceived} )->round;
2888 $order->{totalgste} = $order->{unitpricegste} * $order->{quantity};
2889 $order->{totalgsti} = $order->{unitpricegsti} * $order->{quantity};
2895 =head3 GetOrderUsers
2897 $order_users_ids = &GetOrderUsers($ordernumber);
2899 Returns a list of all borrowernumbers that are in order users list
2904 my ($ordernumber) = @_;
2906 return unless $ordernumber;
2909 SELECT borrowernumber
2911 WHERE ordernumber = ?
2913 my $dbh = C4::Context->dbh;
2914 my $sth = $dbh->prepare($query);
2915 $sth->execute($ordernumber);
2916 my $results = $sth->fetchall_arrayref( {} );
2918 my @borrowernumbers;
2919 foreach (@$results) {
2920 push @borrowernumbers, $_->{'borrowernumber'};
2923 return @borrowernumbers;
2926 =head3 ModOrderUsers
2928 my @order_users_ids = (1, 2, 3);
2929 &ModOrderUsers($ordernumber, @basketusers_ids);
2931 Delete all users from order users list, and add users in C<@order_users_ids>
2937 my ( $ordernumber, @order_users_ids ) = @_;
2939 return unless $ordernumber;
2941 my $dbh = C4::Context->dbh;
2943 DELETE FROM aqorder_users
2944 WHERE ordernumber = ?
2946 my $sth = $dbh->prepare($query);
2947 $sth->execute($ordernumber);
2950 INSERT INTO aqorder_users (ordernumber, borrowernumber)
2953 $sth = $dbh->prepare($query);
2954 foreach my $order_user_id (@order_users_ids) {
2955 $sth->execute( $ordernumber, $order_user_id );
2959 sub NotifyOrderUsers {
2960 my ($ordernumber) = @_;
2962 my @borrowernumbers = GetOrderUsers($ordernumber);
2963 return unless @borrowernumbers;
2965 my $order = GetOrder( $ordernumber );
2966 for my $borrowernumber (@borrowernumbers) {
2967 my $borrower = C4::Members::GetMember( borrowernumber => $borrowernumber );
2968 my $branch = C4::Branch::GetBranchDetail( $borrower->{branchcode} );
2969 my $biblio = C4::Biblio::GetBiblio( $order->{biblionumber} );
2970 my $letter = C4::Letters::GetPreparedLetter(
2971 module => 'acquisition',
2972 letter_code => 'ACQ_NOTIF_ON_RECEIV',
2973 branchcode => $branch->{branchcode},
2975 'branches' => $branch,
2976 'borrowers' => $borrower,
2977 'biblio' => $biblio,
2978 'aqorders' => $order,
2982 C4::Letters::EnqueueLetter(
2985 borrowernumber => $borrowernumber,
2986 LibraryName => C4::Context->preference("LibraryName"),
2987 message_transport_type => 'email',
2989 ) or warn "can't enqueue letter $letter";
2994 =head3 FillWithDefaultValues
2996 FillWithDefaultValues( $marc_record );
2998 This will update the record with default value defined in the ACQ framework.
2999 For all existing fields, if a default value exists and there are no subfield, it will be created.
3000 If the field does not exist, it will be created too.
3004 sub FillWithDefaultValues {
3006 my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ' );
3009 C4::Biblio::GetMarcFromKohaField( 'items.itemnumber', '' );
3010 for my $tag ( sort keys %$tagslib ) {
3012 next if $tag == $itemfield;
3013 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
3014 next if ( subfield_is_koha_internal_p($subfield) );
3015 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
3016 if ( defined $defaultvalue and $defaultvalue ne '' ) {
3017 my @fields = $record->field($tag);
3019 for my $field (@fields) {
3020 unless ( defined $field->subfield($subfield) ) {
3021 $field->add_subfields(
3022 $subfield => $defaultvalue );
3027 $record->insert_fields_ordered(
3029 $tag, '', '', $subfield => $defaultvalue
3044 Koha Development Team <http://koha-community.org/>