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::Booksellers;
32 use Koha::Acquisition::Orders;
36 use Koha::Number::Price;
38 use Koha::CsvProfiles;
48 use vars qw(@ISA @EXPORT);
54 &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
55 &GetBasketAsCSV &GetBasketGroupAsCSV
56 &GetBasketsByBookseller &GetBasketsByBasketgroup
57 &GetBasketsInfosByBookseller
59 &GetBasketUsers &ModBasketUsers
64 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
65 &GetBasketgroups &ReOpenBasketgroup
67 &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
68 &GetLateOrders &GetOrderFromItemnumber
69 &SearchOrders &GetHistory &GetRecentAcqui
70 &ModReceiveOrder &CancelReceipt
72 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
87 &GetItemnumbersFromOrder
90 &GetBiblioCountByBasketno
96 &FillWithDefaultValues
104 sub GetOrderFromItemnumber {
105 my ($itemnumber) = @_;
106 my $dbh = C4::Context->dbh;
109 SELECT * from aqorders LEFT JOIN aqorders_items
110 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
111 WHERE itemnumber = ? |;
113 my $sth = $dbh->prepare($query);
117 $sth->execute($itemnumber);
119 my $order = $sth->fetchrow_hashref;
124 # Returns the itemnumber(s) associated with the ordernumber given in parameter
125 sub GetItemnumbersFromOrder {
126 my ($ordernumber) = @_;
127 my $dbh = C4::Context->dbh;
128 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
129 my $sth = $dbh->prepare($query);
130 $sth->execute($ordernumber);
133 while (my $order = $sth->fetchrow_hashref) {
134 push @tab, $order->{'itemnumber'};
148 C4::Acquisition - Koha functions for dealing with orders and acquisitions
156 The functions in this module deal with acquisitions, managing book
157 orders, basket and parcels.
161 =head2 FUNCTIONS ABOUT BASKETS
165 $aqbasket = &GetBasket($basketnumber);
167 get all basket informations in aqbasket for a given basket
169 B<returns:> informations for a given basket returned as a hashref.
175 my $dbh = C4::Context->dbh;
178 concat( b.firstname,' ',b.surname) AS authorisedbyname
180 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
183 my $sth=$dbh->prepare($query);
184 $sth->execute($basketno);
185 my $basket = $sth->fetchrow_hashref;
189 #------------------------------------------------------------#
193 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
194 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace, $is_standing, $create_items );
196 Create a new basket in aqbasket table
200 =item C<$booksellerid> is a foreign key in the aqbasket table
202 =item C<$authorizedby> is the username of who created the basket
206 The other parameters are optional, see ModBasketHeader for more info on them.
211 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
212 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
213 $billingplace, $is_standing, $create_items ) = @_;
214 my $dbh = C4::Context->dbh;
216 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
217 . 'VALUES (now(),?,?)';
218 $dbh->do( $query, {}, $booksellerid, $authorisedby );
220 my $basket = $dbh->{mysql_insertid};
221 $basketname ||= q{}; # default to empty strings
223 $basketbooksellernote ||= q{};
224 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
225 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items );
229 #------------------------------------------------------------#
233 &CloseBasket($basketno);
235 close a basket (becomes unmodifiable, except for receives)
241 my $dbh = C4::Context->dbh;
242 $dbh->do('UPDATE aqbasket SET closedate=now() WHERE basketno=?', {}, $basketno );
245 q{UPDATE aqorders SET orderstatus = 'ordered' WHERE basketno = ? AND orderstatus NOT IN ( 'complete', 'cancelled')},
253 &ReopenBasket($basketno);
261 my $dbh = C4::Context->dbh;
262 $dbh->do( q{UPDATE aqbasket SET closedate=NULL WHERE basketno=?}, {}, $basketno );
266 SET orderstatus = 'new'
268 AND orderstatus NOT IN ( 'complete', 'cancelled' )
273 #------------------------------------------------------------#
275 =head3 GetBasketAsCSV
277 &GetBasketAsCSV($basketno);
279 Export a basket as CSV
281 $cgi parameter is needed for column name translation
286 my ($basketno, $cgi, $csv_profile_id) = @_;
287 my $basket = GetBasket($basketno);
288 my @orders = GetOrders($basketno);
289 my $contract = GetContract({
290 contractnumber => $basket->{'contractnumber'}
293 my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
295 if ($csv_profile_id) {
296 my $csv_profile = Koha::CsvProfiles->find( $csv_profile_id );
297 Koha::Exceptions::ObjectNotFound->throw( 'There is no valid csv profile given') unless $csv_profile;
299 my $csv = Text::CSV_XS->new({'quote_char'=>'"','escape_char'=>'"','sep_char'=>$csv_profile->csv_separator,'binary'=>1});
300 my $csv_profile_content = $csv_profile->content;
301 my ( @headers, @fields );
302 while ( $csv_profile_content =~ /
305 ([^\|]*) # fieldname (table.row or row)
309 my $field = ($2 eq '') ? $1 : $2;
311 $header =~ s/^\s+|\s+$//g; # Trim whitespaces
312 push @headers, $header;
314 $field =~ s/[^\.]*\.{1}//; # Remove the table name if exists.
315 $field =~ s/^\s+|\s+$//g; # Trim whitespaces
316 push @fields, $field;
318 for my $order (@orders) {
320 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
321 my $biblioitem = $biblio->biblioitem;
322 $order = { %$order, %{ $biblioitem->unblessed } };
324 $order = {%$order, %$contract};
326 $order = {%$order, %$basket, %{ $biblio->unblessed }};
327 for my $field (@fields) {
328 push @row, $order->{$field};
332 my $content = join( $csv_profile->csv_separator, @headers ) . "\n";
333 for my $row ( @rows ) {
334 $csv->combine(@$row);
335 my $string = $csv->string;
336 $content .= $string . "\n";
341 foreach my $order (@orders) {
342 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
343 my $biblioitem = $biblio->biblioitem;
345 contractname => $contract->{'contractname'},
346 ordernumber => $order->{'ordernumber'},
347 entrydate => $order->{'entrydate'},
348 isbn => $order->{'isbn'},
349 author => $biblio->author,
350 title => $biblio->title,
351 publicationyear => $biblioitem->publicationyear,
352 publishercode => $biblioitem->publishercode,
353 collectiontitle => $biblioitem->collectiontitle,
354 notes => $order->{'order_vendornote'},
355 quantity => $order->{'quantity'},
356 rrp => $order->{'rrp'},
358 for my $place ( qw( deliveryplace billingplace ) ) {
359 if ( my $library = Koha::Libraries->find( $row->{deliveryplace} ) ) {
360 $row->{$place} = $library->branchname
364 contractname author title publishercode collectiontitle notes
365 deliveryplace billingplace
367 # Double the quotes to not be interpreted as a field end
368 $row->{$_} =~ s/"/""/g if $row->{$_};
374 if(defined $a->{publishercode} and defined $b->{publishercode}) {
375 $a->{publishercode} cmp $b->{publishercode};
379 $template->param(rows => \@rows);
381 return $template->output;
386 =head3 GetBasketGroupAsCSV
388 &GetBasketGroupAsCSV($basketgroupid);
390 Export a basket group as CSV
392 $cgi parameter is needed for column name translation
396 sub GetBasketGroupAsCSV {
397 my ($basketgroupid, $cgi) = @_;
398 my $baskets = GetBasketsByBasketgroup($basketgroupid);
400 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
403 for my $basket (@$baskets) {
404 my @orders = GetOrders( $basket->{basketno} );
405 my $contract = GetContract({
406 contractnumber => $basket->{contractnumber}
408 my $bookseller = Koha::Acquisition::Booksellers->find( $basket->{booksellerid} );
409 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
411 foreach my $order (@orders) {
412 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
413 my $biblioitem = $biblio->biblioitem;
415 clientnumber => $bookseller->accountnumber,
416 basketname => $basket->{basketname},
417 ordernumber => $order->{ordernumber},
418 author => $biblio->author,
419 title => $biblio->title,
420 publishercode => $biblioitem->publishercode,
421 publicationyear => $biblioitem->publicationyear,
422 collectiontitle => $biblioitem->collectiontitle,
423 isbn => $order->{isbn},
424 quantity => $order->{quantity},
425 rrp_tax_included => $order->{rrp_tax_included},
426 rrp_tax_excluded => $order->{rrp_tax_excluded},
427 discount => $bookseller->discount,
428 ecost_tax_included => $order->{ecost_tax_included},
429 ecost_tax_excluded => $order->{ecost_tax_excluded},
430 notes => $order->{order_vendornote},
431 entrydate => $order->{entrydate},
432 booksellername => $bookseller->name,
433 bookselleraddress => $bookseller->address1,
434 booksellerpostal => $bookseller->postal,
435 contractnumber => $contract->{contractnumber},
436 contractname => $contract->{contractname},
439 basketgroupdeliveryplace => $basketgroup->{deliveryplace},
440 basketgroupbillingplace => $basketgroup->{billingplace},
441 basketdeliveryplace => $basket->{deliveryplace},
442 basketbillingplace => $basket->{billingplace},
444 for my $place (qw( basketgroupdeliveryplace basketgroupbillingplace basketdeliveryplace basketbillingplace )) {
445 if ( my $library = Koha::Libraries->find( $temp->{$place} ) ) {
446 $row->{$place} = $library->branchname;
450 basketname author title publishercode collectiontitle notes
451 booksellername bookselleraddress booksellerpostal contractname
452 basketgroupdeliveryplace basketgroupbillingplace
453 basketdeliveryplace basketbillingplace
455 # Double the quotes to not be interpreted as a field end
456 $row->{$_} =~ s/"/""/g if $row->{$_};
461 $template->param(rows => \@rows);
463 return $template->output;
467 =head3 CloseBasketgroup
469 &CloseBasketgroup($basketgroupno);
475 sub CloseBasketgroup {
476 my ($basketgroupno) = @_;
477 my $dbh = C4::Context->dbh;
478 my $sth = $dbh->prepare("
479 UPDATE aqbasketgroups
483 $sth->execute($basketgroupno);
486 #------------------------------------------------------------#
488 =head3 ReOpenBaskergroup($basketgroupno)
490 &ReOpenBaskergroup($basketgroupno);
496 sub ReOpenBasketgroup {
497 my ($basketgroupno) = @_;
498 my $dbh = C4::Context->dbh;
499 my $sth = $dbh->prepare("
500 UPDATE aqbasketgroups
504 $sth->execute($basketgroupno);
507 #------------------------------------------------------------#
512 &DelBasket($basketno);
514 Deletes the basket that has basketno field $basketno in the aqbasket table.
518 =item C<$basketno> is the primary key of the basket in the aqbasket table.
525 my ( $basketno ) = @_;
526 my $query = "DELETE FROM aqbasket WHERE basketno=?";
527 my $dbh = C4::Context->dbh;
528 my $sth = $dbh->prepare($query);
529 $sth->execute($basketno);
533 #------------------------------------------------------------#
537 &ModBasket($basketinfo);
539 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
543 =item C<$basketno> is the primary key of the basket in the aqbasket table.
550 my $basketinfo = shift;
551 my $query = "UPDATE aqbasket SET ";
553 foreach my $key (keys %$basketinfo){
554 if ($key ne 'basketno'){
555 $query .= "$key=?, ";
556 push(@params, $basketinfo->{$key} || undef );
559 # get rid of the "," at the end of $query
560 if (substr($query, length($query)-2) eq ', '){
565 $query .= "WHERE basketno=?";
566 push(@params, $basketinfo->{'basketno'});
567 my $dbh = C4::Context->dbh;
568 my $sth = $dbh->prepare($query);
569 $sth->execute(@params);
574 #------------------------------------------------------------#
576 =head3 ModBasketHeader
578 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
580 Modifies a basket's header.
584 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
586 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
588 =item C<$note> is the "note" field in the "aqbasket" table;
590 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
592 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
594 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
596 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
598 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
600 =item C<$is_standing> is the "is_standing" field in the aqbasket table.
602 =item C<$create_items> should be set to 'ordering', 'receiving' or 'cataloguing' (or undef, in which
603 case the AcqCreateItem syspref takes precedence).
609 sub ModBasketHeader {
610 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items) = @_;
613 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?, is_standing=?, create_items=?
617 my $dbh = C4::Context->dbh;
618 my $sth = $dbh->prepare($query);
619 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items || undef, $basketno);
621 if ( $contractnumber ) {
622 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
623 my $sth2 = $dbh->prepare($query2);
624 $sth2->execute($contractnumber,$basketno);
629 #------------------------------------------------------------#
631 =head3 GetBasketsByBookseller
633 @results = &GetBasketsByBookseller($booksellerid, $extra);
635 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
639 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
641 =item C<$extra> is the extra sql parameters, can be
643 $extra->{groupby}: group baskets by column
644 ex. $extra->{groupby} = aqbasket.basketgroupid
645 $extra->{orderby}: order baskets by column
646 $extra->{limit}: limit number of results (can be helpful for pagination)
652 sub GetBasketsByBookseller {
653 my ($booksellerid, $extra) = @_;
654 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
656 if ($extra->{groupby}) {
657 $query .= " GROUP by $extra->{groupby}";
659 if ($extra->{orderby}){
660 $query .= " ORDER by $extra->{orderby}";
662 if ($extra->{limit}){
663 $query .= " LIMIT $extra->{limit}";
666 my $dbh = C4::Context->dbh;
667 my $sth = $dbh->prepare($query);
668 $sth->execute($booksellerid);
669 return $sth->fetchall_arrayref({});
672 =head3 GetBasketsInfosByBookseller
674 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
676 The optional second parameter allbaskets is a boolean allowing you to
677 select all baskets from the supplier; by default only active baskets (open or
678 closed but still something to receive) are returned.
680 Returns in a arrayref of hashref all about booksellers baskets, plus:
681 total_biblios: Number of distinct biblios in basket
682 total_items: Number of items in basket
683 expected_items: Number of non-received items in basket
687 sub GetBasketsInfosByBookseller {
688 my ($supplierid, $allbaskets) = @_;
690 return unless $supplierid;
692 my $dbh = C4::Context->dbh;
695 SUM(aqorders.quantity) AS total_items,
697 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
698 ) AS total_items_cancelled,
699 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
701 IF(aqorders.datereceived IS NULL
702 AND aqorders.datecancellationprinted IS NULL
707 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
708 WHERE booksellerid = ?};
710 unless ( $allbaskets ) {
711 $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
713 $query.=" GROUP BY aqbasket.basketno";
715 my $sth = $dbh->prepare($query);
716 $sth->execute($supplierid);
717 my $baskets = $sth->fetchall_arrayref({});
719 # Retrieve the number of biblios cancelled
720 my $cancelled_biblios = $dbh->selectall_hashref( q|
721 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
723 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
724 WHERE booksellerid = ?
725 AND aqorders.orderstatus = 'cancelled'
726 GROUP BY aqbasket.basketno
727 |, 'basketno', {}, $supplierid );
729 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
735 =head3 GetBasketUsers
737 $basketusers_ids = &GetBasketUsers($basketno);
739 Returns a list of all borrowernumbers that are in basket users list
744 my $basketno = shift;
746 return unless $basketno;
749 SELECT borrowernumber
753 my $dbh = C4::Context->dbh;
754 my $sth = $dbh->prepare($query);
755 $sth->execute($basketno);
756 my $results = $sth->fetchall_arrayref( {} );
759 foreach (@$results) {
760 push @borrowernumbers, $_->{'borrowernumber'};
763 return @borrowernumbers;
766 =head3 ModBasketUsers
768 my @basketusers_ids = (1, 2, 3);
769 &ModBasketUsers($basketno, @basketusers_ids);
771 Delete all users from basket users list, and add users in C<@basketusers_ids>
777 my ($basketno, @basketusers_ids) = @_;
779 return unless $basketno;
781 my $dbh = C4::Context->dbh;
783 DELETE FROM aqbasketusers
786 my $sth = $dbh->prepare($query);
787 $sth->execute($basketno);
790 INSERT INTO aqbasketusers (basketno, borrowernumber)
793 $sth = $dbh->prepare($query);
794 foreach my $basketuser_id (@basketusers_ids) {
795 $sth->execute($basketno, $basketuser_id);
800 =head3 CanUserManageBasket
802 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
803 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
805 Check if a borrower can manage a basket, according to system preference
806 AcqViewBaskets, user permissions and basket properties (creator, users list,
809 First parameter can be either a borrowernumber or a hashref as returned by
810 Koha::Patron->unblessed
812 Second parameter can be either a basketno or a hashref as returned by
813 C4::Acquisition::GetBasket.
815 The third parameter is optional. If given, it should be a hashref as returned
816 by C4::Auth::getuserflags. If not, getuserflags is called.
818 If user is authorised to manage basket, returns 1.
823 sub CanUserManageBasket {
824 my ($borrower, $basket, $userflags) = @_;
826 if (!ref $borrower) {
827 # FIXME This needs to be replaced
828 # We should not accept both scalar and array
829 # Tests need to be updated
830 $borrower = Koha::Patrons->find( $borrower )->unblessed;
833 $basket = GetBasket($basket);
836 return 0 unless ($basket and $borrower);
838 my $borrowernumber = $borrower->{borrowernumber};
839 my $basketno = $basket->{basketno};
841 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
843 if (!defined $userflags) {
844 my $dbh = C4::Context->dbh;
845 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
846 $sth->execute($borrowernumber);
847 my ($flags) = $sth->fetchrow_array;
850 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
853 unless ($userflags->{superlibrarian}
854 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
855 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
857 if (not exists $userflags->{acquisition}) {
861 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
862 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
866 if ($AcqViewBaskets eq 'user'
867 && $basket->{authorisedby} != $borrowernumber
868 && ! grep { $borrowernumber eq $_ } GetBasketUsers($basketno)) {
872 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
873 && $basket->{branch} ne $borrower->{branchcode}) {
881 #------------------------------------------------------------#
883 =head3 GetBasketsByBasketgroup
885 $baskets = &GetBasketsByBasketgroup($basketgroupid);
887 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
891 sub GetBasketsByBasketgroup {
892 my $basketgroupid = shift;
894 SELECT *, aqbasket.booksellerid as booksellerid
896 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
898 my $dbh = C4::Context->dbh;
899 my $sth = $dbh->prepare($query);
900 $sth->execute($basketgroupid);
901 return $sth->fetchall_arrayref({});
904 #------------------------------------------------------------#
906 =head3 NewBasketgroup
908 $basketgroupid = NewBasketgroup(\%hashref);
910 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
912 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
914 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
916 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
918 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
920 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
922 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
924 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
926 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
931 my $basketgroupinfo = shift;
932 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
933 my $query = "INSERT INTO aqbasketgroups (";
935 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
936 if ( defined $basketgroupinfo->{$field} ) {
937 $query .= "$field, ";
938 push(@params, $basketgroupinfo->{$field});
941 $query .= "booksellerid) VALUES (";
946 push(@params, $basketgroupinfo->{'booksellerid'});
947 my $dbh = C4::Context->dbh;
948 my $sth = $dbh->prepare($query);
949 $sth->execute(@params);
950 my $basketgroupid = $dbh->{'mysql_insertid'};
951 if( $basketgroupinfo->{'basketlist'} ) {
952 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
953 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
954 my $sth2 = $dbh->prepare($query2);
955 $sth2->execute($basketgroupid, $basketno);
958 return $basketgroupid;
961 #------------------------------------------------------------#
963 =head3 ModBasketgroup
965 ModBasketgroup(\%hashref);
967 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
969 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
971 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
973 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
975 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
977 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
979 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
981 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
983 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
988 my $basketgroupinfo = shift;
989 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
990 my $dbh = C4::Context->dbh;
991 my $query = "UPDATE aqbasketgroups SET ";
993 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
994 if ( defined $basketgroupinfo->{$field} ) {
995 $query .= "$field=?, ";
996 push(@params, $basketgroupinfo->{$field});
1001 $query .= " WHERE id=?";
1002 push(@params, $basketgroupinfo->{'id'});
1003 my $sth = $dbh->prepare($query);
1004 $sth->execute(@params);
1006 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
1007 $sth->execute($basketgroupinfo->{'id'});
1009 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
1010 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
1011 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
1012 $sth->execute($basketgroupinfo->{'id'}, $basketno);
1018 #------------------------------------------------------------#
1020 =head3 DelBasketgroup
1022 DelBasketgroup($basketgroupid);
1024 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
1028 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
1034 sub DelBasketgroup {
1035 my $basketgroupid = shift;
1036 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1037 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
1038 my $dbh = C4::Context->dbh;
1039 my $sth = $dbh->prepare($query);
1040 $sth->execute($basketgroupid);
1044 #------------------------------------------------------------#
1047 =head2 FUNCTIONS ABOUT ORDERS
1049 =head3 GetBasketgroup
1051 $basketgroup = &GetBasketgroup($basketgroupid);
1053 Returns a reference to the hash containing all information about the basketgroup.
1057 sub GetBasketgroup {
1058 my $basketgroupid = shift;
1059 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1060 my $dbh = C4::Context->dbh;
1061 my $result_set = $dbh->selectall_arrayref(
1062 'SELECT * FROM aqbasketgroups WHERE id=?',
1066 return $result_set->[0]; # id is unique
1069 #------------------------------------------------------------#
1071 =head3 GetBasketgroups
1073 $basketgroups = &GetBasketgroups($booksellerid);
1075 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1079 sub GetBasketgroups {
1080 my $booksellerid = shift;
1081 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1082 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1083 my $dbh = C4::Context->dbh;
1084 my $sth = $dbh->prepare($query);
1085 $sth->execute($booksellerid);
1086 return $sth->fetchall_arrayref({});
1089 #------------------------------------------------------------#
1091 =head2 FUNCTIONS ABOUT ORDERS
1095 @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } );
1097 Looks up the pending (non-cancelled) orders with the given basket
1100 If cancelled is set, only cancelled orders will be returned.
1105 my ( $basketno, $params ) = @_;
1107 return () unless $basketno;
1109 my $orderby = $params->{orderby};
1110 my $cancelled = $params->{cancelled} || 0;
1112 my $dbh = C4::Context->dbh;
1114 SELECT biblio.*,biblioitems.*,
1118 $query .= $cancelled
1120 aqorders_transfers.ordernumber_to AS transferred_to,
1121 aqorders_transfers.timestamp AS transferred_to_timestamp
1124 aqorders_transfers.ordernumber_from AS transferred_from,
1125 aqorders_transfers.timestamp AS transferred_from_timestamp
1129 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1130 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1131 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1133 $query .= $cancelled
1135 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1138 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1146 $orderby ||= q|biblioitems.publishercode, biblio.title|;
1148 AND (datecancellationprinted IS NOT NULL
1149 AND datecancellationprinted <> '0000-00-00')
1154 q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|;
1156 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1160 $query .= " ORDER BY $orderby";
1162 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1167 #------------------------------------------------------------#
1169 =head3 GetOrdersByBiblionumber
1171 @orders = &GetOrdersByBiblionumber($biblionumber);
1173 Looks up the orders with linked to a specific $biblionumber, including
1174 cancelled orders and received orders.
1177 C<@orders> is an array of references-to-hash, whose keys are the
1178 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1182 sub GetOrdersByBiblionumber {
1183 my $biblionumber = shift;
1184 return unless $biblionumber;
1185 my $dbh = C4::Context->dbh;
1187 SELECT biblio.*,biblioitems.*,
1191 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1192 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1193 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1194 WHERE aqorders.biblionumber=?
1197 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1198 return @{$result_set};
1202 #------------------------------------------------------------#
1206 $order = &GetOrder($ordernumber);
1208 Looks up an order by order number.
1210 Returns a reference-to-hash describing the order. The keys of
1211 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1216 my ($ordernumber) = @_;
1217 return unless $ordernumber;
1219 my $dbh = C4::Context->dbh;
1220 my $query = qq{SELECT
1224 aqbasket.basketname,
1225 borrowers.branchcode,
1226 biblioitems.publicationyear,
1227 biblio.copyrightdate,
1228 biblioitems.editionstatement,
1232 biblioitems.publishercode,
1233 aqorders.rrp AS unitpricesupplier,
1234 aqorders.ecost AS unitpricelib,
1235 aqorders.claims_count AS claims_count,
1236 aqorders.claimed_date AS claimed_date,
1237 aqbudgets.budget_name AS budget,
1238 aqbooksellers.name AS supplier,
1239 aqbooksellers.id AS supplierid,
1240 biblioitems.publishercode AS publisher,
1241 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1242 DATE(aqbasket.closedate) AS orderdate,
1243 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1244 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1245 DATEDIFF(CURDATE( ),closedate) AS latesince
1246 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1247 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1248 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1249 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1250 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1251 WHERE aqorders.basketno = aqbasket.basketno
1254 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1256 # result_set assumed to contain 1 match
1257 return $result_set->[0];
1260 =head3 GetLastOrderNotReceivedFromSubscriptionid
1262 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1264 Returns a reference-to-hash describing the last order not received for a subscription.
1268 sub GetLastOrderNotReceivedFromSubscriptionid {
1269 my ( $subscriptionid ) = @_;
1270 my $dbh = C4::Context->dbh;
1272 SELECT * FROM aqorders
1273 LEFT JOIN subscription
1274 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1275 WHERE aqorders.subscriptionid = ?
1276 AND aqorders.datereceived IS NULL
1280 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1282 # result_set assumed to contain 1 match
1283 return $result_set->[0];
1286 =head3 GetLastOrderReceivedFromSubscriptionid
1288 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1290 Returns a reference-to-hash describing the last order received for a subscription.
1294 sub GetLastOrderReceivedFromSubscriptionid {
1295 my ( $subscriptionid ) = @_;
1296 my $dbh = C4::Context->dbh;
1298 SELECT * FROM aqorders
1299 LEFT JOIN subscription
1300 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1301 WHERE aqorders.subscriptionid = ?
1302 AND aqorders.datereceived =
1304 SELECT MAX( aqorders.datereceived )
1306 LEFT JOIN subscription
1307 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1308 WHERE aqorders.subscriptionid = ?
1309 AND aqorders.datereceived IS NOT NULL
1311 ORDER BY ordernumber DESC
1315 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1317 # result_set assumed to contain 1 match
1318 return $result_set->[0];
1322 #------------------------------------------------------------#
1326 &ModOrder(\%hashref);
1328 Modifies an existing order. Updates the order with order number
1329 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1330 other keys of the hash update the fields with the same name in the aqorders
1331 table of the Koha database.
1336 my $orderinfo = shift;
1338 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '';
1340 my $dbh = C4::Context->dbh;
1343 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1344 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1346 # delete($orderinfo->{'branchcode'});
1347 # the hash contains a lot of entries not in aqorders, so get the columns ...
1348 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1350 my $colnames = $sth->{NAME};
1351 #FIXME Be careful. If aqorders would have columns with diacritics,
1352 #you should need to decode what you get back from NAME.
1353 #See report 10110 and guided_reports.pl
1354 my $query = "UPDATE aqorders SET ";
1356 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1357 # ... and skip hash entries that are not in the aqorders table
1358 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1359 next unless grep(/^$orderinfokey$/, @$colnames);
1360 $query .= "$orderinfokey=?, ";
1361 push(@params, $orderinfo->{$orderinfokey});
1364 $query .= "timestamp=NOW() WHERE ordernumber=?";
1365 push(@params, $orderinfo->{'ordernumber'} );
1366 $sth = $dbh->prepare($query);
1367 $sth->execute(@params);
1371 #------------------------------------------------------------#
1375 ModItemOrder($itemnumber, $ordernumber);
1377 Modifies the ordernumber of an item in aqorders_items.
1382 my ($itemnumber, $ordernumber) = @_;
1384 return unless ($itemnumber and $ordernumber);
1386 my $dbh = C4::Context->dbh;
1388 UPDATE aqorders_items
1390 WHERE itemnumber = ?
1392 my $sth = $dbh->prepare($query);
1393 return $sth->execute($ordernumber, $itemnumber);
1396 #------------------------------------------------------------#
1398 =head3 ModReceiveOrder
1400 my ( $date_received, $new_ordernumber ) = ModReceiveOrder(
1402 biblionumber => $biblionumber,
1404 quantityreceived => $quantityreceived,
1406 invoice => $invoice,
1407 budget_id => $budget_id,
1408 received_itemnumbers => \@received_itemnumbers,
1409 order_internalnote => $order_internalnote,
1413 Updates an order, to reflect the fact that it was received, at least
1416 If a partial order is received, splits the order into two.
1418 Updates the order with biblionumber C<$biblionumber> and ordernumber
1419 C<$order->{ordernumber}>.
1424 sub ModReceiveOrder {
1426 my $biblionumber = $params->{biblionumber};
1427 my $order = { %{ $params->{order} } }; # Copy the order, we don't want to modify it
1428 my $invoice = $params->{invoice};
1429 my $quantrec = $params->{quantityreceived};
1430 my $user = $params->{user};
1431 my $budget_id = $params->{budget_id};
1432 my $received_items = $params->{received_items};
1434 my $dbh = C4::Context->dbh;
1435 my $datereceived = ( $invoice and $invoice->{datereceived} ) ? $invoice->{datereceived} : dt_from_string;
1436 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1437 if ($suggestionid) {
1438 ModSuggestion( {suggestionid=>$suggestionid,
1439 STATUS=>'AVAILABLE',
1440 biblionumber=> $biblionumber}
1444 my $result_set = $dbh->selectrow_arrayref(
1445 q{SELECT aqbasket.is_standing
1447 WHERE basketno=?},{ Slice => {} }, $order->{basketno});
1448 my $is_standing = $result_set->[0]; # we assume we have a unique basket
1450 my $new_ordernumber = $order->{ordernumber};
1451 if ( $is_standing || $order->{quantity} > $quantrec ) {
1452 # Split order line in two parts: the first is the original order line
1453 # without received items (the quantity is decreased),
1454 # the second part is a new order line with quantity=quantityrec
1455 # (entirely received)
1459 orderstatus = 'partial'|;
1460 $query .= q|, order_internalnote = ?| if defined $order->{order_internalnote};
1461 $query .= q| WHERE ordernumber = ?|;
1462 my $sth = $dbh->prepare($query);
1465 ( $is_standing ? 1 : ($order->{quantity} - $quantrec) ),
1466 ( defined $order->{order_internalnote} ? $order->{order_internalnote} : () ),
1467 $order->{ordernumber}
1470 # Recalculate tax_value
1474 tax_value_on_ordering = quantity * ecost_tax_excluded * tax_rate_on_ordering,
1475 tax_value_on_receiving = quantity * unitprice_tax_excluded * tax_rate_on_receiving
1476 WHERE ordernumber = ?
1477 |, undef, $order->{ordernumber});
1479 delete $order->{ordernumber};
1480 $order->{budget_id} = ( $budget_id || $order->{budget_id} );
1481 $order->{quantity} = $quantrec;
1482 $order->{quantityreceived} = $quantrec;
1483 $order->{ecost_tax_excluded} //= 0;
1484 $order->{tax_rate_on_ordering} //= 0;
1485 $order->{unitprice_tax_excluded} //= 0;
1486 $order->{tax_rate_on_receiving} //= 0;
1487 $order->{tax_value_on_ordering} = $order->{quantity} * $order->{ecost_tax_excluded} * $order->{tax_rate_on_ordering};
1488 $order->{tax_value_on_receiving} = $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate_on_receiving};
1489 $order->{datereceived} = $datereceived;
1490 $order->{invoiceid} = $invoice->{invoiceid};
1491 $order->{orderstatus} = 'complete';
1492 $new_ordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber; # TODO What if the store fails?
1494 if ($received_items) {
1495 foreach my $itemnumber (@$received_items) {
1496 ModItemOrder($itemnumber, $new_ordernumber);
1502 SET quantityreceived = ?,
1506 orderstatus = 'complete'
1510 , unitprice = ?, unitprice_tax_included = ?, unitprice_tax_excluded = ?
1511 | if defined $order->{unitprice};
1514 ,tax_value_on_receiving = ?
1515 | if defined $order->{tax_value_on_receiving};
1518 ,tax_rate_on_receiving = ?
1519 | if defined $order->{tax_rate_on_receiving};
1522 , order_internalnote = ?
1523 | if defined $order->{order_internalnote};
1525 $query .= q| where biblionumber=? and ordernumber=?|;
1527 my $sth = $dbh->prepare( $query );
1528 my @params = ( $quantrec, $datereceived, $invoice->{invoiceid}, ( $budget_id ? $budget_id : $order->{budget_id} ) );
1530 if ( defined $order->{unitprice} ) {
1531 push @params, $order->{unitprice}, $order->{unitprice_tax_included}, $order->{unitprice_tax_excluded};
1534 if ( defined $order->{tax_value_on_receiving} ) {
1535 push @params, $order->{tax_value_on_receiving};
1538 if ( defined $order->{tax_rate_on_receiving} ) {
1539 push @params, $order->{tax_rate_on_receiving};
1542 if ( defined $order->{order_internalnote} ) {
1543 push @params, $order->{order_internalnote};
1546 push @params, ( $biblionumber, $order->{ordernumber} );
1548 $sth->execute( @params );
1550 # All items have been received, sent a notification to users
1551 NotifyOrderUsers( $order->{ordernumber} );
1554 return ($datereceived, $new_ordernumber);
1557 =head3 CancelReceipt
1559 my $parent_ordernumber = CancelReceipt($ordernumber);
1561 Cancel an order line receipt and update the parent order line, as if no
1563 If items are created at receipt (AcqCreateItem = receiving) then delete
1569 my $ordernumber = shift;
1571 return unless $ordernumber;
1573 my $dbh = C4::Context->dbh;
1575 SELECT datereceived, parent_ordernumber, quantity
1577 WHERE ordernumber = ?
1579 my $sth = $dbh->prepare($query);
1580 $sth->execute($ordernumber);
1581 my $order = $sth->fetchrow_hashref;
1583 warn "CancelReceipt: order $ordernumber does not exist";
1586 unless($order->{'datereceived'}) {
1587 warn "CancelReceipt: order $ordernumber is not received";
1591 my $parent_ordernumber = $order->{'parent_ordernumber'};
1593 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1594 my $order_obj = Koha::Acquisition::Orders->find( $ordernumber ); # FIXME rewrite all this subroutine using this object
1596 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1597 # The order line has no parent, just mark it as not received
1600 SET quantityreceived = ?,
1603 orderstatus = 'ordered'
1604 WHERE ordernumber = ?
1606 $sth = $dbh->prepare($query);
1607 $sth->execute(0, undef, undef, $ordernumber);
1608 _cancel_items_receipt( $order_obj );
1610 # The order line has a parent, increase parent quantity and delete
1613 SELECT quantity, datereceived
1615 WHERE ordernumber = ?
1617 $sth = $dbh->prepare($query);
1618 $sth->execute($parent_ordernumber);
1619 my $parent_order = $sth->fetchrow_hashref;
1620 unless($parent_order) {
1621 warn "Parent order $parent_ordernumber does not exist.";
1624 if($parent_order->{'datereceived'}) {
1625 warn "CancelReceipt: parent order is received.".
1626 " Can't cancel receipt.";
1632 orderstatus = 'ordered'
1633 WHERE ordernumber = ?
1635 $sth = $dbh->prepare($query);
1636 my $rv = $sth->execute(
1637 $order->{'quantity'} + $parent_order->{'quantity'},
1641 warn "Cannot update parent order line, so do not cancel".
1646 # Recalculate tax_value
1650 tax_value_on_ordering = quantity * ecost_tax_excluded * tax_rate_on_ordering,
1651 tax_value_on_receiving = quantity * unitprice_tax_excluded * tax_rate_on_receiving
1652 WHERE ordernumber = ?
1653 |, undef, $parent_ordernumber);
1655 _cancel_items_receipt( $order_obj, $parent_ordernumber );
1658 DELETE FROM aqorders
1659 WHERE ordernumber = ?
1661 $sth = $dbh->prepare($query);
1662 $sth->execute($ordernumber);
1666 if( $order_obj->basket->effective_create_items eq 'ordering' ) {
1667 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1669 for my $in ( @itemnumbers ) {
1670 my $item = Koha::Items->find( $in );
1671 my $biblio = $item->biblio;
1672 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber', $biblio->frameworkcode );
1673 my $item_marc = C4::Items::GetMarcItem( $biblio->biblionumber, $in );
1674 for my $affect ( @affects ) {
1675 my ( $sf, $v ) = split q{=}, $affect, 2;
1676 foreach ( $item_marc->field($itemfield) ) {
1677 $_->update( $sf => $v );
1680 C4::Items::ModItemFromMarc( $item_marc, $biblio->biblionumber, $in );
1685 return $parent_ordernumber;
1688 sub _cancel_items_receipt {
1689 my ( $order, $parent_ordernumber ) = @_;
1690 $parent_ordernumber ||= $order->ordernumber;
1692 my @itemnumbers = GetItemnumbersFromOrder($order->ordernumber); # FIXME Must be $order->items
1693 if ( $order->basket->effective_create_items eq 'receiving' ) {
1694 # Remove items that were created at receipt
1696 DELETE FROM items, aqorders_items
1697 USING items, aqorders_items
1698 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1700 my $dbh = C4::Context->dbh;
1701 my $sth = $dbh->prepare($query);
1702 foreach my $itemnumber (@itemnumbers) {
1703 $sth->execute($itemnumber, $itemnumber);
1707 foreach my $itemnumber (@itemnumbers) {
1708 ModItemOrder($itemnumber, $parent_ordernumber);
1713 #------------------------------------------------------------#
1717 @results = &SearchOrders({
1718 ordernumber => $ordernumber,
1721 booksellerid => $booksellerid,
1722 basketno => $basketno,
1723 basketname => $basketname,
1724 basketgroupname => $basketgroupname,
1728 biblionumber => $biblionumber,
1729 budget_id => $budget_id
1732 Searches for orders filtered by criteria.
1734 C<$ordernumber> Finds matching orders or transferred orders by ordernumber.
1735 C<$search> Finds orders matching %$search% in title, author, or isbn.
1736 C<$owner> Finds order for the logged in user.
1737 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1738 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1741 C<@results> is an array of references-to-hash with the keys are fields
1742 from aqorders, biblio, biblioitems and aqbasket tables.
1747 my ( $params ) = @_;
1748 my $ordernumber = $params->{ordernumber};
1749 my $search = $params->{search};
1750 my $ean = $params->{ean};
1751 my $booksellerid = $params->{booksellerid};
1752 my $basketno = $params->{basketno};
1753 my $basketname = $params->{basketname};
1754 my $basketgroupname = $params->{basketgroupname};
1755 my $owner = $params->{owner};
1756 my $pending = $params->{pending};
1757 my $ordered = $params->{ordered};
1758 my $biblionumber = $params->{biblionumber};
1759 my $budget_id = $params->{budget_id};
1761 my $dbh = C4::Context->dbh;
1764 SELECT aqbasket.basketno,
1766 borrowers.firstname,
1769 biblioitems.biblioitemnumber,
1770 biblioitems.publishercode,
1771 biblioitems.publicationyear,
1772 aqbasket.authorisedby,
1773 aqbasket.booksellerid,
1775 aqbasket.creationdate,
1776 aqbasket.basketname,
1777 aqbasketgroups.id as basketgroupid,
1778 aqbasketgroups.name as basketgroupname,
1781 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1782 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1783 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1784 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1785 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1788 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1790 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1794 WHERE (datecancellationprinted is NULL)
1797 if ( $pending or $ordered ) {
1800 ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) )
1802 ( quantity > quantityreceived OR quantityreceived is NULL )
1806 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1814 my $userenv = C4::Context->userenv;
1815 if ( C4::Context->preference("IndependentBranches") ) {
1816 unless ( C4::Context->IsSuperLibrarian() ) {
1819 borrowers.branchcode = ?
1820 OR borrowers.branchcode = ''
1823 push @args, $userenv->{branch};
1827 if ( $ordernumber ) {
1828 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1829 push @args, ( $ordernumber, $ordernumber );
1831 if ( $biblionumber ) {
1832 $query .= 'AND aqorders.biblionumber = ?';
1833 push @args, $biblionumber;
1836 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1837 push @args, ("%$search%","%$search%","%$search%");
1840 $query .= ' AND biblioitems.ean = ?';
1843 if ( $booksellerid ) {
1844 $query .= 'AND aqbasket.booksellerid = ?';
1845 push @args, $booksellerid;
1848 $query .= 'AND aqbasket.basketno = ?';
1849 push @args, $basketno;
1852 $query .= 'AND aqbasket.basketname LIKE ?';
1853 push @args, "%$basketname%";
1855 if( $basketgroupname ) {
1856 $query .= ' AND aqbasketgroups.name LIKE ?';
1857 push @args, "%$basketgroupname%";
1861 $query .= ' AND aqbasket.authorisedby=? ';
1862 push @args, $userenv->{'number'};
1866 $query .= ' AND aqorders.budget_id = ?';
1867 push @args, $budget_id;
1870 $query .= ' ORDER BY aqbasket.basketno';
1872 my $sth = $dbh->prepare($query);
1873 $sth->execute(@args);
1874 return $sth->fetchall_arrayref({});
1877 #------------------------------------------------------------#
1881 &DelOrder($biblionumber, $ordernumber);
1883 Cancel the order with the given order and biblio numbers. It does not
1884 delete any entries in the aqorders table, it merely marks them as
1890 my ( $bibnum, $ordernumber, $delete_biblio, $reason ) = @_;
1893 my $dbh = C4::Context->dbh;
1896 SET datecancellationprinted=now(), orderstatus='cancelled'
1899 $query .= ", cancellationreason = ? ";
1902 WHERE biblionumber=? AND ordernumber=?
1904 my $sth = $dbh->prepare($query);
1906 $sth->execute($reason, $bibnum, $ordernumber);
1908 $sth->execute( $bibnum, $ordernumber );
1912 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1913 foreach my $itemnumber (@itemnumbers){
1914 my $delcheck = C4::Items::DelItemCheck( $bibnum, $itemnumber );
1916 if($delcheck != 1) {
1917 $error->{'delitem'} = 1;
1921 if($delete_biblio) {
1922 # We get the number of remaining items
1923 my $biblio = Koha::Biblios->find( $bibnum );
1924 my $itemcount = $biblio->items->count;
1926 # If there are no items left,
1927 if ( $itemcount == 0 ) {
1928 # We delete the record
1929 my $delcheck = DelBiblio($bibnum);
1932 $error->{'delbiblio'} = 1;
1940 =head3 TransferOrder
1942 my $newordernumber = TransferOrder($ordernumber, $basketno);
1944 Transfer an order line to a basket.
1945 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1946 to BOOKSELLER on DATE' and create new order with internal note
1947 'Transferred from BOOKSELLER on DATE'.
1948 Move all attached items to the new order.
1949 Received orders cannot be transferred.
1950 Return the ordernumber of created order.
1955 my ($ordernumber, $basketno) = @_;
1957 return unless ($ordernumber and $basketno);
1959 my $order = Koha::Acquisition::Orders->find( $ordernumber ) or return;
1960 return if $order->datereceived;
1962 $order = $order->unblessed;
1964 my $basket = GetBasket($basketno);
1965 return unless $basket;
1967 my $dbh = C4::Context->dbh;
1968 my ($query, $sth, $rv);
1972 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1973 WHERE ordernumber = ?
1975 $sth = $dbh->prepare($query);
1976 $rv = $sth->execute('cancelled', $ordernumber);
1978 delete $order->{'ordernumber'};
1979 delete $order->{parent_ordernumber};
1980 $order->{'basketno'} = $basketno;
1982 my $newordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber;
1985 UPDATE aqorders_items
1987 WHERE ordernumber = ?
1989 $sth = $dbh->prepare($query);
1990 $sth->execute($newordernumber, $ordernumber);
1993 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1996 $sth = $dbh->prepare($query);
1997 $sth->execute($ordernumber, $newordernumber);
1999 return $newordernumber;
2002 =head2 FUNCTIONS ABOUT PARCELS
2006 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
2008 get a lists of parcels.
2015 is the bookseller this function has to get parcels.
2018 To know on what criteria the results list has to be ordered.
2021 is the booksellerinvoicenumber.
2023 =item $datefrom & $dateto
2024 to know on what date this function has to filter its search.
2029 a pointer on a hash list containing parcel informations as such :
2035 =item Last operation
2037 =item Number of biblio
2039 =item Number of items
2046 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
2047 my $dbh = C4::Context->dbh;
2048 my @query_params = ();
2050 SELECT aqinvoices.invoicenumber,
2051 datereceived,purchaseordernumber,
2052 count(DISTINCT biblionumber) AS biblio,
2053 sum(quantity) AS itemsexpected,
2054 sum(quantityreceived) AS itemsreceived
2055 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
2056 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2057 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
2059 push @query_params, $bookseller;
2061 if ( defined $code ) {
2062 $strsth .= ' and aqinvoices.invoicenumber like ? ';
2063 # add a % to the end of the code to allow stemming.
2064 push @query_params, "$code%";
2067 if ( defined $datefrom ) {
2068 $strsth .= ' and datereceived >= ? ';
2069 push @query_params, $datefrom;
2072 if ( defined $dateto ) {
2073 $strsth .= 'and datereceived <= ? ';
2074 push @query_params, $dateto;
2077 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
2079 # can't use a placeholder to place this column name.
2080 # but, we could probably be checking to make sure it is a column that will be fetched.
2081 $strsth .= "order by $order " if ($order);
2083 my $sth = $dbh->prepare($strsth);
2085 $sth->execute( @query_params );
2086 my $results = $sth->fetchall_arrayref({});
2090 #------------------------------------------------------------#
2092 =head3 GetLateOrders
2094 @results = &GetLateOrders;
2096 Searches for bookseller with late orders.
2099 the table of supplier with late issues. This table is full of hashref.
2105 my $supplierid = shift;
2107 my $estimateddeliverydatefrom = shift;
2108 my $estimateddeliverydateto = shift;
2110 my $dbh = C4::Context->dbh;
2112 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2113 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2115 my @query_params = ();
2117 SELECT aqbasket.basketno,
2118 aqorders.ordernumber,
2119 DATE(aqbasket.closedate) AS orderdate,
2120 aqbasket.basketname AS basketname,
2121 aqbasket.basketgroupid AS basketgroupid,
2122 aqbasketgroups.name AS basketgroupname,
2123 aqorders.rrp AS unitpricesupplier,
2124 aqorders.ecost AS unitpricelib,
2125 aqorders.claims_count AS claims_count,
2126 aqorders.claimed_date AS claimed_date,
2127 aqbudgets.budget_name AS budget,
2128 borrowers.branchcode AS branch,
2129 aqbooksellers.name AS supplier,
2130 aqbooksellers.id AS supplierid,
2131 biblio.author, biblio.title,
2132 biblioitems.publishercode AS publisher,
2133 biblioitems.publicationyear,
2134 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2138 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2139 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2140 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2141 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2142 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2143 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2144 WHERE aqorders.basketno = aqbasket.basketno
2145 AND ( datereceived = ''
2146 OR datereceived IS NULL
2147 OR aqorders.quantityreceived < aqorders.quantity
2149 AND aqbasket.closedate IS NOT NULL
2150 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2153 if ($dbdriver eq "mysql") {
2155 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2156 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2157 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2159 if ( defined $delay ) {
2160 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2161 push @query_params, $delay;
2163 $having = "HAVING quantity <> 0";
2165 # FIXME: account for IFNULL as above
2167 aqorders.quantity AS quantity,
2168 aqorders.quantity * aqorders.rrp AS subtotal,
2169 (CAST(now() AS date) - closedate) AS latesince
2171 if ( defined $delay ) {
2172 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2173 push @query_params, $delay;
2176 if (defined $supplierid) {
2177 $from .= ' AND aqbasket.booksellerid = ? ';
2178 push @query_params, $supplierid;
2180 if (defined $branch) {
2181 $from .= ' AND borrowers.branchcode LIKE ? ';
2182 push @query_params, $branch;
2185 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2186 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2188 if ( defined $estimateddeliverydatefrom ) {
2189 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2190 push @query_params, $estimateddeliverydatefrom;
2192 if ( defined $estimateddeliverydateto ) {
2193 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2194 push @query_params, $estimateddeliverydateto;
2196 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2197 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2199 if (C4::Context->preference("IndependentBranches")
2200 && !C4::Context->IsSuperLibrarian() ) {
2201 $from .= ' AND borrowers.branchcode LIKE ? ';
2202 push @query_params, C4::Context->userenv->{branch};
2204 $from .= " AND orderstatus <> 'cancelled' ";
2205 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2206 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2207 my $sth = $dbh->prepare($query);
2208 $sth->execute(@query_params);
2210 while (my $data = $sth->fetchrow_hashref) {
2211 push @results, $data;
2216 #------------------------------------------------------------#
2220 \@order_loop = GetHistory( %params );
2222 Retreives some acquisition history information
2232 basket - search both basket name and number
2233 booksellerinvoicenumber
2236 orderstatus (note that orderstatus '' will retrieve orders
2237 of any status except cancelled)
2239 get_canceled_order (if set to a true value, cancelled orders will
2243 $order_loop is a list of hashrefs that each look like this:
2245 'author' => 'Twain, Mark',
2247 'biblionumber' => '215',
2249 'creationdate' => 'MM/DD/YYYY',
2250 'datereceived' => undef,
2253 'invoicenumber' => undef,
2255 'ordernumber' => '1',
2257 'quantityreceived' => undef,
2258 'title' => 'The Adventures of Huckleberry Finn'
2264 # don't run the query if there are no parameters (list would be too long for sure !)
2265 croak "No search params" unless @_;
2267 my $title = $params{title};
2268 my $author = $params{author};
2269 my $isbn = $params{isbn};
2270 my $ean = $params{ean};
2271 my $name = $params{name};
2272 my $from_placed_on = $params{from_placed_on};
2273 my $to_placed_on = $params{to_placed_on};
2274 my $basket = $params{basket};
2275 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2276 my $basketgroupname = $params{basketgroupname};
2277 my $budget = $params{budget};
2278 my $orderstatus = $params{orderstatus};
2279 my $biblionumber = $params{biblionumber};
2280 my $get_canceled_order = $params{get_canceled_order} || 0;
2281 my $ordernumber = $params{ordernumber};
2282 my $search_children_too = $params{search_children_too} || 0;
2283 my $created_by = $params{created_by} || [];
2287 my $total_qtyreceived = 0;
2288 my $total_price = 0;
2290 my $dbh = C4::Context->dbh;
2293 COALESCE(biblio.title, deletedbiblio.title) AS title,
2294 COALESCE(biblio.author, deletedbiblio.author) AS author,
2295 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2296 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2298 aqbasket.basketname,
2299 aqbasket.basketgroupid,
2300 aqbasket.authorisedby,
2301 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2302 aqbasketgroups.name as groupname,
2304 aqbasket.creationdate,
2305 aqorders.datereceived,
2307 aqorders.quantityreceived,
2309 aqorders.ordernumber,
2311 aqinvoices.invoicenumber,
2312 aqbooksellers.id as id,
2313 aqorders.biblionumber,
2314 aqorders.orderstatus,
2315 aqorders.parent_ordernumber,
2316 aqbudgets.budget_name
2318 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2321 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2322 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2323 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2324 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2325 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2326 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2327 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2328 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2329 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2330 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2333 $query .= " WHERE 1 ";
2335 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2336 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2339 my @query_params = ();
2341 if ( $biblionumber ) {
2342 $query .= " AND biblio.biblionumber = ?";
2343 push @query_params, $biblionumber;
2347 $query .= " AND biblio.title LIKE ? ";
2348 $title =~ s/\s+/%/g;
2349 push @query_params, "%$title%";
2353 $query .= " AND biblio.author LIKE ? ";
2354 push @query_params, "%$author%";
2358 $query .= " AND biblioitems.isbn LIKE ? ";
2359 push @query_params, "%$isbn%";
2362 $query .= " AND biblioitems.ean = ? ";
2363 push @query_params, "$ean";
2366 $query .= " AND aqbooksellers.name LIKE ? ";
2367 push @query_params, "%$name%";
2371 $query .= " AND aqbudgets.budget_id = ? ";
2372 push @query_params, "$budget";
2375 if ( $from_placed_on ) {
2376 $query .= " AND creationdate >= ? ";
2377 push @query_params, $from_placed_on;
2380 if ( $to_placed_on ) {
2381 $query .= " AND creationdate <= ? ";
2382 push @query_params, $to_placed_on;
2385 if ( defined $orderstatus and $orderstatus ne '') {
2386 $query .= " AND aqorders.orderstatus = ? ";
2387 push @query_params, "$orderstatus";
2391 if ($basket =~ m/^\d+$/) {
2392 $query .= " AND aqorders.basketno = ? ";
2393 push @query_params, $basket;
2395 $query .= " AND aqbasket.basketname LIKE ? ";
2396 push @query_params, "%$basket%";
2400 if ($booksellerinvoicenumber) {
2401 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2402 push @query_params, "%$booksellerinvoicenumber%";
2405 if ($basketgroupname) {
2406 $query .= " AND aqbasketgroups.name LIKE ? ";
2407 push @query_params, "%$basketgroupname%";
2411 $query .= " AND (aqorders.ordernumber = ? ";
2412 push @query_params, $ordernumber;
2413 if ($search_children_too) {
2414 $query .= " OR aqorders.parent_ordernumber = ? ";
2415 push @query_params, $ordernumber;
2420 if ( @$created_by ) {
2421 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2422 push @query_params, @$created_by;
2426 if ( C4::Context->preference("IndependentBranches") ) {
2427 unless ( C4::Context->IsSuperLibrarian() ) {
2428 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2429 push @query_params, C4::Context->userenv->{branch};
2432 $query .= " ORDER BY id";
2434 return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2437 =head2 GetRecentAcqui
2439 $results = GetRecentAcqui($days);
2441 C<$results> is a ref to a table which contains hashref
2445 sub GetRecentAcqui {
2447 my $dbh = C4::Context->dbh;
2451 ORDER BY timestamp DESC
2454 my $sth = $dbh->prepare($query);
2456 my $results = $sth->fetchall_arrayref({});
2460 #------------------------------------------------------------#
2464 &AddClaim($ordernumber);
2466 Add a claim for an order
2471 my ($ordernumber) = @_;
2472 my $dbh = C4::Context->dbh;
2475 claims_count = claims_count + 1,
2476 claimed_date = CURDATE()
2477 WHERE ordernumber = ?
2479 my $sth = $dbh->prepare($query);
2480 $sth->execute($ordernumber);
2485 my @invoices = GetInvoices(
2486 invoicenumber => $invoicenumber,
2487 supplierid => $supplierid,
2488 suppliername => $suppliername,
2489 shipmentdatefrom => $shipmentdatefrom, # ISO format
2490 shipmentdateto => $shipmentdateto, # ISO format
2491 billingdatefrom => $billingdatefrom, # ISO format
2492 billingdateto => $billingdateto, # ISO format
2493 isbneanissn => $isbn_or_ean_or_issn,
2496 publisher => $publisher,
2497 publicationyear => $publicationyear,
2498 branchcode => $branchcode,
2499 order_by => $order_by
2502 Return a list of invoices that match all given criteria.
2504 $order_by is "column_name (asc|desc)", where column_name is any of
2505 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2506 'shipmentcost', 'shipmentcost_budgetid'.
2508 asc is the default if omitted
2515 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2516 closedate shipmentcost shipmentcost_budgetid);
2518 my $dbh = C4::Context->dbh;
2520 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2523 aqorders.datereceived IS NOT NULL,
2524 aqorders.biblionumber,
2527 ) AS receivedbiblios,
2530 aqorders.subscriptionid IS NOT NULL,
2531 aqorders.subscriptionid,
2534 ) AS is_linked_to_subscriptions,
2535 SUM(aqorders.quantityreceived) AS receiveditems
2537 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2538 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2539 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2540 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2541 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2542 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2543 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2548 if($args{supplierid}) {
2549 push @bind_strs, " aqinvoices.booksellerid = ? ";
2550 push @bind_args, $args{supplierid};
2552 if($args{invoicenumber}) {
2553 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2554 push @bind_args, "%$args{invoicenumber}%";
2556 if($args{suppliername}) {
2557 push @bind_strs, " aqbooksellers.name LIKE ? ";
2558 push @bind_args, "%$args{suppliername}%";
2560 if($args{shipmentdatefrom}) {
2561 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2562 push @bind_args, $args{shipmentdatefrom};
2564 if($args{shipmentdateto}) {
2565 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2566 push @bind_args, $args{shipmentdateto};
2568 if($args{billingdatefrom}) {
2569 push @bind_strs, " aqinvoices.billingdate >= ? ";
2570 push @bind_args, $args{billingdatefrom};
2572 if($args{billingdateto}) {
2573 push @bind_strs, " aqinvoices.billingdate <= ? ";
2574 push @bind_args, $args{billingdateto};
2576 if($args{isbneanissn}) {
2577 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2578 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2581 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2582 push @bind_args, $args{title};
2585 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2586 push @bind_args, $args{author};
2588 if($args{publisher}) {
2589 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2590 push @bind_args, $args{publisher};
2592 if($args{publicationyear}) {
2593 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2594 push @bind_args, $args{publicationyear}, $args{publicationyear};
2596 if($args{branchcode}) {
2597 push @bind_strs, " borrowers.branchcode = ? ";
2598 push @bind_args, $args{branchcode};
2600 if($args{message_id}) {
2601 push @bind_strs, " aqinvoices.message_id = ? ";
2602 push @bind_args, $args{message_id};
2605 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2606 $query .= " GROUP BY aqinvoices.invoiceid ";
2608 if($args{order_by}) {
2609 my ($column, $direction) = split / /, $args{order_by};
2610 if(grep /^$column$/, @columns) {
2611 $direction ||= 'ASC';
2612 $query .= " ORDER BY $column $direction";
2616 my $sth = $dbh->prepare($query);
2617 $sth->execute(@bind_args);
2619 my $results = $sth->fetchall_arrayref({});
2625 my $invoice = GetInvoice($invoiceid);
2627 Get informations about invoice with given $invoiceid
2629 Return a hash filled with aqinvoices.* fields
2634 my ($invoiceid) = @_;
2637 return unless $invoiceid;
2639 my $dbh = C4::Context->dbh;
2645 my $sth = $dbh->prepare($query);
2646 $sth->execute($invoiceid);
2648 $invoice = $sth->fetchrow_hashref;
2652 =head3 GetInvoiceDetails
2654 my $invoice = GetInvoiceDetails($invoiceid)
2656 Return informations about an invoice + the list of related order lines
2658 Orders informations are in $invoice->{orders} (array ref)
2662 sub GetInvoiceDetails {
2663 my ($invoiceid) = @_;
2665 if ( !defined $invoiceid ) {
2666 carp 'GetInvoiceDetails called without an invoiceid';
2670 my $dbh = C4::Context->dbh;
2672 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2674 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2677 my $sth = $dbh->prepare($query);
2678 $sth->execute($invoiceid);
2680 my $invoice = $sth->fetchrow_hashref;
2685 biblio.copyrightdate,
2687 biblioitems.publishercode,
2688 biblioitems.publicationyear,
2689 aqbasket.basketname,
2690 aqbasketgroups.id AS basketgroupid,
2691 aqbasketgroups.name AS basketgroupname
2693 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2694 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2695 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2696 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2699 $sth = $dbh->prepare($query);
2700 $sth->execute($invoiceid);
2701 $invoice->{orders} = $sth->fetchall_arrayref({});
2702 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2709 my $invoiceid = AddInvoice(
2710 invoicenumber => $invoicenumber,
2711 booksellerid => $booksellerid,
2712 shipmentdate => $shipmentdate,
2713 billingdate => $billingdate,
2714 closedate => $closedate,
2715 shipmentcost => $shipmentcost,
2716 shipmentcost_budgetid => $shipmentcost_budgetid
2719 Create a new invoice and return its id or undef if it fails.
2726 return unless(%invoice and $invoice{invoicenumber});
2728 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2729 closedate shipmentcost shipmentcost_budgetid message_id);
2733 foreach my $key (keys %invoice) {
2734 if(0 < grep(/^$key$/, @columns)) {
2735 push @set_strs, "$key = ?";
2736 push @set_args, ($invoice{$key} || undef);
2742 my $dbh = C4::Context->dbh;
2743 my $query = "INSERT INTO aqinvoices SET ";
2744 $query .= join (",", @set_strs);
2745 my $sth = $dbh->prepare($query);
2746 $rv = $sth->execute(@set_args);
2748 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2757 invoiceid => $invoiceid, # Mandatory
2758 invoicenumber => $invoicenumber,
2759 booksellerid => $booksellerid,
2760 shipmentdate => $shipmentdate,
2761 billingdate => $billingdate,
2762 closedate => $closedate,
2763 shipmentcost => $shipmentcost,
2764 shipmentcost_budgetid => $shipmentcost_budgetid
2767 Modify an invoice, invoiceid is mandatory.
2769 Return undef if it fails.
2776 return unless(%invoice and $invoice{invoiceid});
2778 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2779 closedate shipmentcost shipmentcost_budgetid);
2783 foreach my $key (keys %invoice) {
2784 if(0 < grep(/^$key$/, @columns)) {
2785 push @set_strs, "$key = ?";
2786 push @set_args, ($invoice{$key} || undef);
2790 my $dbh = C4::Context->dbh;
2791 my $query = "UPDATE aqinvoices SET ";
2792 $query .= join(",", @set_strs);
2793 $query .= " WHERE invoiceid = ?";
2795 my $sth = $dbh->prepare($query);
2796 $sth->execute(@set_args, $invoice{invoiceid});
2801 CloseInvoice($invoiceid);
2805 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2810 my ($invoiceid) = @_;
2812 return unless $invoiceid;
2814 my $dbh = C4::Context->dbh;
2817 SET closedate = CAST(NOW() AS DATE)
2820 my $sth = $dbh->prepare($query);
2821 $sth->execute($invoiceid);
2824 =head3 ReopenInvoice
2826 ReopenInvoice($invoiceid);
2830 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => output_pref({ dt=>dt_from_string, dateonly=>1, otputpref=>'iso' }))
2835 my ($invoiceid) = @_;
2837 return unless $invoiceid;
2839 my $dbh = C4::Context->dbh;
2842 SET closedate = NULL
2845 my $sth = $dbh->prepare($query);
2846 $sth->execute($invoiceid);
2851 DelInvoice($invoiceid);
2853 Delete an invoice if there are no items attached to it.
2858 my ($invoiceid) = @_;
2860 return unless $invoiceid;
2862 my $dbh = C4::Context->dbh;
2868 my $sth = $dbh->prepare($query);
2869 $sth->execute($invoiceid);
2870 my $res = $sth->fetchrow_arrayref;
2871 if ( $res && $res->[0] == 0 ) {
2873 DELETE FROM aqinvoices
2876 my $sth = $dbh->prepare($query);
2877 return ( $sth->execute($invoiceid) > 0 );
2882 =head3 MergeInvoices
2884 MergeInvoices($invoiceid, \@sourceids);
2886 Merge the invoices identified by the IDs in \@sourceids into
2887 the invoice identified by $invoiceid.
2892 my ($invoiceid, $sourceids) = @_;
2894 return unless $invoiceid;
2895 foreach my $sourceid (@$sourceids) {
2896 next if $sourceid == $invoiceid;
2897 my $source = GetInvoiceDetails($sourceid);
2898 foreach my $order (@{$source->{'orders'}}) {
2899 $order->{'invoiceid'} = $invoiceid;
2902 DelInvoice($source->{'invoiceid'});
2907 =head3 GetBiblioCountByBasketno
2909 $biblio_count = &GetBiblioCountByBasketno($basketno);
2911 Looks up the biblio's count that has basketno value $basketno
2917 sub GetBiblioCountByBasketno {
2918 my ($basketno) = @_;
2919 my $dbh = C4::Context->dbh;
2921 SELECT COUNT( DISTINCT( biblionumber ) )
2924 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2927 my $sth = $dbh->prepare($query);
2928 $sth->execute($basketno);
2929 return $sth->fetchrow;
2932 # Note this subroutine should be moved to Koha::Acquisition::Order
2933 # Will do when a DBIC decision will be taken.
2934 sub populate_order_with_prices {
2937 my $order = $params->{order};
2938 my $booksellerid = $params->{booksellerid};
2939 return unless $booksellerid;
2941 my $bookseller = Koha::Acquisition::Booksellers->find( $booksellerid );
2943 my $receiving = $params->{receiving};
2944 my $ordering = $params->{ordering};
2945 my $discount = $order->{discount};
2946 $discount /= 100 if $discount > 1;
2949 $order->{tax_rate_on_ordering} //= $order->{tax_rate};
2950 if ( $bookseller->listincgst ) {
2951 # The user entered the rrp tax included
2952 $order->{rrp_tax_included} = $order->{rrp};
2954 # rrp tax excluded = rrp tax included / ( 1 + tax rate )
2955 $order->{rrp_tax_excluded} = $order->{rrp_tax_included} / ( 1 + $order->{tax_rate_on_ordering} );
2957 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
2958 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
2960 # ecost tax included = rrp tax included ( 1 - discount )
2961 $order->{ecost_tax_included} = $order->{rrp_tax_included} * ( 1 - $discount );
2964 # The user entered the rrp tax excluded
2965 $order->{rrp_tax_excluded} = $order->{rrp};
2967 # rrp tax included = rrp tax excluded * ( 1 - tax rate )
2968 $order->{rrp_tax_included} = $order->{rrp_tax_excluded} * ( 1 + $order->{tax_rate_on_ordering} );
2970 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
2971 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
2973 # ecost tax included = rrp tax excluded * ( 1 + tax rate ) * ( 1 - discount )
2974 $order->{ecost_tax_included} =
2975 $order->{rrp_tax_excluded} *
2976 ( 1 + $order->{tax_rate_on_ordering} ) *
2980 # tax value = quantity * ecost tax excluded * tax rate
2981 $order->{tax_value_on_ordering} =
2982 $order->{quantity} * $order->{ecost_tax_excluded} * $order->{tax_rate_on_ordering};
2986 $order->{tax_rate_on_receiving} //= $order->{tax_rate};
2987 if ( $bookseller->invoiceincgst ) {
2988 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
2989 # we need to keep the exact ecost value
2990 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_included} )->round ) {
2991 $order->{unitprice} = $order->{ecost_tax_included};
2994 # The user entered the unit price tax included
2995 $order->{unitprice_tax_included} = $order->{unitprice};
2997 # unit price tax excluded = unit price tax included / ( 1 + tax rate )
2998 $order->{unitprice_tax_excluded} = $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate_on_receiving} );
3001 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
3002 # we need to keep the exact ecost value
3003 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_excluded} )->round ) {
3004 $order->{unitprice} = $order->{ecost_tax_excluded};
3007 # The user entered the unit price tax excluded
3008 $order->{unitprice_tax_excluded} = $order->{unitprice};
3011 # unit price tax included = unit price tax included * ( 1 + tax rate )
3012 $order->{unitprice_tax_included} = $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate_on_receiving} );
3015 # tax value = quantity * unit price tax excluded * tax rate
3016 $order->{tax_value_on_receiving} = $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate_on_receiving};
3022 =head3 GetOrderUsers
3024 $order_users_ids = &GetOrderUsers($ordernumber);
3026 Returns a list of all borrowernumbers that are in order users list
3031 my ($ordernumber) = @_;
3033 return unless $ordernumber;
3036 SELECT borrowernumber
3038 WHERE ordernumber = ?
3040 my $dbh = C4::Context->dbh;
3041 my $sth = $dbh->prepare($query);
3042 $sth->execute($ordernumber);
3043 my $results = $sth->fetchall_arrayref( {} );
3045 my @borrowernumbers;
3046 foreach (@$results) {
3047 push @borrowernumbers, $_->{'borrowernumber'};
3050 return @borrowernumbers;
3053 =head3 ModOrderUsers
3055 my @order_users_ids = (1, 2, 3);
3056 &ModOrderUsers($ordernumber, @basketusers_ids);
3058 Delete all users from order users list, and add users in C<@order_users_ids>
3064 my ( $ordernumber, @order_users_ids ) = @_;
3066 return unless $ordernumber;
3068 my $dbh = C4::Context->dbh;
3070 DELETE FROM aqorder_users
3071 WHERE ordernumber = ?
3073 my $sth = $dbh->prepare($query);
3074 $sth->execute($ordernumber);
3077 INSERT INTO aqorder_users (ordernumber, borrowernumber)
3080 $sth = $dbh->prepare($query);
3081 foreach my $order_user_id (@order_users_ids) {
3082 $sth->execute( $ordernumber, $order_user_id );
3086 sub NotifyOrderUsers {
3087 my ($ordernumber) = @_;
3089 my @borrowernumbers = GetOrderUsers($ordernumber);
3090 return unless @borrowernumbers;
3092 my $order = GetOrder( $ordernumber );
3093 for my $borrowernumber (@borrowernumbers) {
3094 my $patron = Koha::Patrons->find( $borrowernumber );
3095 my $library = $patron->library->unblessed;
3096 my $biblio = Koha::Biblios->find( $order->{biblionumber} )->unblessed;
3097 my $letter = C4::Letters::GetPreparedLetter(
3098 module => 'acquisition',
3099 letter_code => 'ACQ_NOTIF_ON_RECEIV',
3100 branchcode => $library->{branchcode},
3101 lang => $patron->lang,
3103 'branches' => $library,
3104 'borrowers' => $patron->unblessed,
3105 'biblio' => $biblio,
3106 'aqorders' => $order,
3110 C4::Letters::EnqueueLetter(
3113 borrowernumber => $borrowernumber,
3114 LibraryName => C4::Context->preference("LibraryName"),
3115 message_transport_type => 'email',
3117 ) or warn "can't enqueue letter $letter";
3122 =head3 FillWithDefaultValues
3124 FillWithDefaultValues( $marc_record );
3126 This will update the record with default value defined in the ACQ framework.
3127 For all existing fields, if a default value exists and there are no subfield, it will be created.
3128 If the field does not exist, it will be created too.
3132 sub FillWithDefaultValues {
3134 my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ', { unsafe => 1 } );
3137 C4::Biblio::GetMarcFromKohaField( 'items.itemnumber', '' );
3138 for my $tag ( sort keys %$tagslib ) {
3140 next if $tag == $itemfield;
3141 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
3142 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
3143 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
3144 if ( defined $defaultvalue and $defaultvalue ne '' ) {
3145 my @fields = $record->field($tag);
3147 for my $field (@fields) {
3148 unless ( defined $field->subfield($subfield) ) {
3149 $field->add_subfields(
3150 $subfield => $defaultvalue );
3155 $record->insert_fields_ordered(
3157 $tag, '', '', $subfield => $defaultvalue
3172 Koha Development Team <http://koha-community.org/>