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;
35 use Koha::Number::Price;
37 use Koha::CsvProfiles;
47 use vars qw(@ISA @EXPORT);
53 &GetBasket &NewBasket &CloseBasket &ReopenBasket &DelBasket &ModBasket
54 &GetBasketAsCSV &GetBasketGroupAsCSV
55 &GetBasketsByBookseller &GetBasketsByBasketgroup
56 &GetBasketsInfosByBookseller
58 &GetBasketUsers &ModBasketUsers
63 &ModBasketgroup &NewBasketgroup &DelBasketgroup &GetBasketgroup &CloseBasketgroup
64 &GetBasketgroups &ReOpenBasketgroup
66 &DelOrder &ModOrder &GetOrder &GetOrders &GetOrdersByBiblionumber
67 &GetLateOrders &GetOrderFromItemnumber
68 &SearchOrders &GetHistory &GetRecentAcqui
69 &ModReceiveOrder &CancelReceipt
71 &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid
86 &GetItemnumbersFromOrder
89 &GetBiblioCountByBasketno
95 &FillWithDefaultValues
103 sub GetOrderFromItemnumber {
104 my ($itemnumber) = @_;
105 my $dbh = C4::Context->dbh;
108 SELECT * from aqorders LEFT JOIN aqorders_items
109 ON ( aqorders.ordernumber = aqorders_items.ordernumber )
110 WHERE itemnumber = ? |;
112 my $sth = $dbh->prepare($query);
116 $sth->execute($itemnumber);
118 my $order = $sth->fetchrow_hashref;
123 # Returns the itemnumber(s) associated with the ordernumber given in parameter
124 sub GetItemnumbersFromOrder {
125 my ($ordernumber) = @_;
126 my $dbh = C4::Context->dbh;
127 my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?";
128 my $sth = $dbh->prepare($query);
129 $sth->execute($ordernumber);
132 while (my $order = $sth->fetchrow_hashref) {
133 push @tab, $order->{'itemnumber'};
147 C4::Acquisition - Koha functions for dealing with orders and acquisitions
155 The functions in this module deal with acquisitions, managing book
156 orders, basket and parcels.
160 =head2 FUNCTIONS ABOUT BASKETS
164 $aqbasket = &GetBasket($basketnumber);
166 get all basket informations in aqbasket for a given basket
168 B<returns:> informations for a given basket returned as a hashref.
174 my $dbh = C4::Context->dbh;
177 concat( b.firstname,' ',b.surname) AS authorisedbyname
179 LEFT JOIN borrowers b ON aqbasket.authorisedby=b.borrowernumber
182 my $sth=$dbh->prepare($query);
183 $sth->execute($basketno);
184 my $basket = $sth->fetchrow_hashref;
188 #------------------------------------------------------------#
192 $basket = &NewBasket( $booksellerid, $authorizedby, $basketname,
193 $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace, $is_standing, $create_items );
195 Create a new basket in aqbasket table
199 =item C<$booksellerid> is a foreign key in the aqbasket table
201 =item C<$authorizedby> is the username of who created the basket
205 The other parameters are optional, see ModBasketHeader for more info on them.
210 my ( $booksellerid, $authorisedby, $basketname, $basketnote,
211 $basketbooksellernote, $basketcontractnumber, $deliveryplace,
212 $billingplace, $is_standing, $create_items ) = @_;
213 my $dbh = C4::Context->dbh;
215 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) '
216 . 'VALUES (now(),?,?)';
217 $dbh->do( $query, {}, $booksellerid, $authorisedby );
219 my $basket = $dbh->{mysql_insertid};
220 $basketname ||= q{}; # default to empty strings
222 $basketbooksellernote ||= q{};
223 ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote,
224 $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items );
228 #------------------------------------------------------------#
232 &CloseBasket($basketno);
234 close a basket (becomes unmodifiable, except for receives)
240 my $dbh = C4::Context->dbh;
241 $dbh->do('UPDATE aqbasket SET closedate=now() WHERE basketno=?', {}, $basketno );
244 q{UPDATE aqorders SET orderstatus = 'ordered' WHERE basketno = ? AND orderstatus NOT IN ( 'complete', 'cancelled')},
252 &ReopenBasket($basketno);
260 my $dbh = C4::Context->dbh;
261 $dbh->do( q{UPDATE aqbasket SET closedate=NULL WHERE basketno=?}, {}, $basketno );
265 SET orderstatus = 'new'
267 AND orderstatus NOT IN ( 'complete', 'cancelled' )
272 #------------------------------------------------------------#
274 =head3 GetBasketAsCSV
276 &GetBasketAsCSV($basketno);
278 Export a basket as CSV
280 $cgi parameter is needed for column name translation
285 my ($basketno, $cgi, $csv_profile_id) = @_;
286 my $basket = GetBasket($basketno);
287 my @orders = GetOrders($basketno);
288 my $contract = GetContract({
289 contractnumber => $basket->{'contractnumber'}
292 my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi);
294 if ($csv_profile_id) {
295 my $csv_profile = Koha::CsvProfiles->find( $csv_profile_id );
296 die "There is no valid csv profile given" unless $csv_profile;
298 my $csv = Text::CSV_XS->new({'quote_char'=>'"','escape_char'=>'"','sep_char'=>$csv_profile->csv_separator,'binary'=>1});
299 my $csv_profile_content = $csv_profile->content;
300 my ( @headers, @fields );
301 while ( $csv_profile_content =~ /
304 ([^\|]*) # fieldname (table.row or row)
308 my $field = ($2 eq '') ? $1 : $2;
310 $header =~ s/^\s+|\s+$//g; # Trim whitespaces
311 push @headers, $header;
313 $field =~ s/[^\.]*\.{1}//; # Remove the table name if exists.
314 $field =~ s/^\s+|\s+$//g; # Trim whitespaces
315 push @fields, $field;
317 for my $order (@orders) {
319 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
320 my $biblioitem = $biblio->biblioitem;
321 $order = { %$order, %{ $biblioitem->unblessed } };
323 $order = {%$order, %$contract};
325 $order = {%$order, %$basket, %{ $biblio->unblessed }};
326 for my $field (@fields) {
327 push @row, $order->{$field};
331 my $content = join( $csv_profile->csv_separator, @headers ) . "\n";
332 for my $row ( @rows ) {
333 $csv->combine(@$row);
334 my $string = $csv->string;
335 $content .= $string . "\n";
340 foreach my $order (@orders) {
341 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
342 my $biblioitem = $biblio->biblioitem;
344 contractname => $contract->{'contractname'},
345 ordernumber => $order->{'ordernumber'},
346 entrydate => $order->{'entrydate'},
347 isbn => $order->{'isbn'},
348 author => $biblio->author,
349 title => $biblio->title,
350 publicationyear => $biblioitem->publicationyear,
351 publishercode => $biblioitem->publishercode,
352 collectiontitle => $biblioitem->collectiontitle,
353 notes => $order->{'order_vendornote'},
354 quantity => $order->{'quantity'},
355 rrp => $order->{'rrp'},
357 for my $place ( qw( deliveryplace billingplace ) ) {
358 if ( my $library = Koha::Libraries->find( $row->{deliveryplace} ) ) {
359 $row->{$place} = $library->branchname
363 contractname author title publishercode collectiontitle notes
364 deliveryplace billingplace
366 # Double the quotes to not be interpreted as a field end
367 $row->{$_} =~ s/"/""/g if $row->{$_};
373 if(defined $a->{publishercode} and defined $b->{publishercode}) {
374 $a->{publishercode} cmp $b->{publishercode};
378 $template->param(rows => \@rows);
380 return $template->output;
385 =head3 GetBasketGroupAsCSV
387 &GetBasketGroupAsCSV($basketgroupid);
389 Export a basket group as CSV
391 $cgi parameter is needed for column name translation
395 sub GetBasketGroupAsCSV {
396 my ($basketgroupid, $cgi) = @_;
397 my $baskets = GetBasketsByBasketgroup($basketgroupid);
399 my $template = C4::Templates::gettemplate('acqui/csv/basketgroup.tt', 'intranet', $cgi);
402 for my $basket (@$baskets) {
403 my @orders = GetOrders( $basket->{basketno} );
404 my $contract = GetContract({
405 contractnumber => $basket->{contractnumber}
407 my $bookseller = Koha::Acquisition::Booksellers->find( $basket->{booksellerid} );
408 my $basketgroup = GetBasketgroup( $$basket{basketgroupid} );
410 foreach my $order (@orders) {
411 my $biblio = Koha::Biblios->find( $order->{biblionumber} );
412 my $biblioitem = $biblio->biblioitem;
414 clientnumber => $bookseller->accountnumber,
415 basketname => $basket->{basketname},
416 ordernumber => $order->{ordernumber},
417 author => $biblio->author,
418 title => $biblio->title,
419 publishercode => $biblioitem->publishercode,
420 publicationyear => $biblioitem->publicationyear,
421 collectiontitle => $biblioitem->collectiontitle,
422 isbn => $order->{isbn},
423 quantity => $order->{quantity},
424 rrp_tax_included => $order->{rrp_tax_included},
425 rrp_tax_excluded => $order->{rrp_tax_excluded},
426 discount => $bookseller->discount,
427 ecost_tax_included => $order->{ecost_tax_included},
428 ecost_tax_excluded => $order->{ecost_tax_excluded},
429 notes => $order->{order_vendornote},
430 entrydate => $order->{entrydate},
431 booksellername => $bookseller->name,
432 bookselleraddress => $bookseller->address1,
433 booksellerpostal => $bookseller->postal,
434 contractnumber => $contract->{contractnumber},
435 contractname => $contract->{contractname},
438 basketgroupdeliveryplace => $basketgroup->{deliveryplace},
439 basketgroupbillingplace => $basketgroup->{billingplace},
440 basketdeliveryplace => $basket->{deliveryplace},
441 basketbillingplace => $basket->{billingplace},
443 for my $place (qw( basketgroupdeliveryplace basketgroupbillingplace basketdeliveryplace basketbillingplace )) {
444 if ( my $library = Koha::Libraries->find( $temp->{$place} ) ) {
445 $row->{$place} = $library->branchname;
449 basketname author title publishercode collectiontitle notes
450 booksellername bookselleraddress booksellerpostal contractname
451 basketgroupdeliveryplace basketgroupbillingplace
452 basketdeliveryplace basketbillingplace
454 # Double the quotes to not be interpreted as a field end
455 $row->{$_} =~ s/"/""/g if $row->{$_};
460 $template->param(rows => \@rows);
462 return $template->output;
466 =head3 CloseBasketgroup
468 &CloseBasketgroup($basketgroupno);
474 sub CloseBasketgroup {
475 my ($basketgroupno) = @_;
476 my $dbh = C4::Context->dbh;
477 my $sth = $dbh->prepare("
478 UPDATE aqbasketgroups
482 $sth->execute($basketgroupno);
485 #------------------------------------------------------------#
487 =head3 ReOpenBaskergroup($basketgroupno)
489 &ReOpenBaskergroup($basketgroupno);
495 sub ReOpenBasketgroup {
496 my ($basketgroupno) = @_;
497 my $dbh = C4::Context->dbh;
498 my $sth = $dbh->prepare("
499 UPDATE aqbasketgroups
503 $sth->execute($basketgroupno);
506 #------------------------------------------------------------#
511 &DelBasket($basketno);
513 Deletes the basket that has basketno field $basketno in the aqbasket table.
517 =item C<$basketno> is the primary key of the basket in the aqbasket table.
524 my ( $basketno ) = @_;
525 my $query = "DELETE FROM aqbasket WHERE basketno=?";
526 my $dbh = C4::Context->dbh;
527 my $sth = $dbh->prepare($query);
528 $sth->execute($basketno);
532 #------------------------------------------------------------#
536 &ModBasket($basketinfo);
538 Modifies a basket, using a hashref $basketinfo for the relevant information, only $basketinfo->{'basketno'} is required.
542 =item C<$basketno> is the primary key of the basket in the aqbasket table.
549 my $basketinfo = shift;
550 my $query = "UPDATE aqbasket SET ";
552 foreach my $key (keys %$basketinfo){
553 if ($key ne 'basketno'){
554 $query .= "$key=?, ";
555 push(@params, $basketinfo->{$key} || undef );
558 # get rid of the "," at the end of $query
559 if (substr($query, length($query)-2) eq ', '){
564 $query .= "WHERE basketno=?";
565 push(@params, $basketinfo->{'basketno'});
566 my $dbh = C4::Context->dbh;
567 my $sth = $dbh->prepare($query);
568 $sth->execute(@params);
573 #------------------------------------------------------------#
575 =head3 ModBasketHeader
577 &ModBasketHeader($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid);
579 Modifies a basket's header.
583 =item C<$basketno> is the "basketno" field in the "aqbasket" table;
585 =item C<$basketname> is the "basketname" field in the "aqbasket" table;
587 =item C<$note> is the "note" field in the "aqbasket" table;
589 =item C<$booksellernote> is the "booksellernote" field in the "aqbasket" table;
591 =item C<$contractnumber> is the "contractnumber" (foreign) key in the "aqbasket" table.
593 =item C<$booksellerid> is the id (foreign) key in the "aqbooksellers" table for the vendor.
595 =item C<$deliveryplace> is the "deliveryplace" field in the aqbasket table.
597 =item C<$billingplace> is the "billingplace" field in the aqbasket table.
599 =item C<$is_standing> is the "is_standing" field in the aqbasket table.
601 =item C<$create_items> should be set to 'ordering', 'receiving' or 'cataloguing' (or undef, in which
602 case the AcqCreateItem syspref takes precedence).
608 sub ModBasketHeader {
609 my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items) = @_;
612 SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?, is_standing=?, create_items=?
616 my $dbh = C4::Context->dbh;
617 my $sth = $dbh->prepare($query);
618 $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items || undef, $basketno);
620 if ( $contractnumber ) {
621 my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?";
622 my $sth2 = $dbh->prepare($query2);
623 $sth2->execute($contractnumber,$basketno);
628 #------------------------------------------------------------#
630 =head3 GetBasketsByBookseller
632 @results = &GetBasketsByBookseller($booksellerid, $extra);
634 Returns a list of hashes of all the baskets that belong to bookseller 'booksellerid'.
638 =item C<$booksellerid> is the 'id' field of the bookseller in the aqbooksellers table
640 =item C<$extra> is the extra sql parameters, can be
642 $extra->{groupby}: group baskets by column
643 ex. $extra->{groupby} = aqbasket.basketgroupid
644 $extra->{orderby}: order baskets by column
645 $extra->{limit}: limit number of results (can be helpful for pagination)
651 sub GetBasketsByBookseller {
652 my ($booksellerid, $extra) = @_;
653 my $query = "SELECT * FROM aqbasket WHERE booksellerid=?";
655 if ($extra->{groupby}) {
656 $query .= " GROUP by $extra->{groupby}";
658 if ($extra->{orderby}){
659 $query .= " ORDER by $extra->{orderby}";
661 if ($extra->{limit}){
662 $query .= " LIMIT $extra->{limit}";
665 my $dbh = C4::Context->dbh;
666 my $sth = $dbh->prepare($query);
667 $sth->execute($booksellerid);
668 return $sth->fetchall_arrayref({});
671 =head3 GetBasketsInfosByBookseller
673 my $baskets = GetBasketsInfosByBookseller($supplierid, $allbaskets);
675 The optional second parameter allbaskets is a boolean allowing you to
676 select all baskets from the supplier; by default only active baskets (open or
677 closed but still something to receive) are returned.
679 Returns in a arrayref of hashref all about booksellers baskets, plus:
680 total_biblios: Number of distinct biblios in basket
681 total_items: Number of items in basket
682 expected_items: Number of non-received items in basket
686 sub GetBasketsInfosByBookseller {
687 my ($supplierid, $allbaskets) = @_;
689 return unless $supplierid;
691 my $dbh = C4::Context->dbh;
694 SUM(aqorders.quantity) AS total_items,
696 IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 )
697 ) AS total_items_cancelled,
698 COUNT(DISTINCT aqorders.biblionumber) AS total_biblios,
700 IF(aqorders.datereceived IS NULL
701 AND aqorders.datecancellationprinted IS NULL
706 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
707 WHERE booksellerid = ?};
709 unless ( $allbaskets ) {
710 $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))";
712 $query.=" GROUP BY aqbasket.basketno";
714 my $sth = $dbh->prepare($query);
715 $sth->execute($supplierid);
716 my $baskets = $sth->fetchall_arrayref({});
718 # Retrieve the number of biblios cancelled
719 my $cancelled_biblios = $dbh->selectall_hashref( q|
720 SELECT COUNT(DISTINCT(biblionumber)) AS total_biblios_cancelled, aqbasket.basketno
722 LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno
723 WHERE booksellerid = ?
724 AND aqorders.orderstatus = 'cancelled'
725 GROUP BY aqbasket.basketno
726 |, 'basketno', {}, $supplierid );
728 $_->{total_biblios_cancelled} = $cancelled_biblios->{$_->{basketno}}{total_biblios_cancelled} || 0
734 =head3 GetBasketUsers
736 $basketusers_ids = &GetBasketUsers($basketno);
738 Returns a list of all borrowernumbers that are in basket users list
743 my $basketno = shift;
745 return unless $basketno;
748 SELECT borrowernumber
752 my $dbh = C4::Context->dbh;
753 my $sth = $dbh->prepare($query);
754 $sth->execute($basketno);
755 my $results = $sth->fetchall_arrayref( {} );
758 foreach (@$results) {
759 push @borrowernumbers, $_->{'borrowernumber'};
762 return @borrowernumbers;
765 =head3 ModBasketUsers
767 my @basketusers_ids = (1, 2, 3);
768 &ModBasketUsers($basketno, @basketusers_ids);
770 Delete all users from basket users list, and add users in C<@basketusers_ids>
776 my ($basketno, @basketusers_ids) = @_;
778 return unless $basketno;
780 my $dbh = C4::Context->dbh;
782 DELETE FROM aqbasketusers
785 my $sth = $dbh->prepare($query);
786 $sth->execute($basketno);
789 INSERT INTO aqbasketusers (basketno, borrowernumber)
792 $sth = $dbh->prepare($query);
793 foreach my $basketuser_id (@basketusers_ids) {
794 $sth->execute($basketno, $basketuser_id);
799 =head3 CanUserManageBasket
801 my $bool = CanUserManageBasket($borrower, $basket[, $userflags]);
802 my $bool = CanUserManageBasket($borrowernumber, $basketno[, $userflags]);
804 Check if a borrower can manage a basket, according to system preference
805 AcqViewBaskets, user permissions and basket properties (creator, users list,
808 First parameter can be either a borrowernumber or a hashref as returned by
809 Koha::Patron->unblessed
811 Second parameter can be either a basketno or a hashref as returned by
812 C4::Acquisition::GetBasket.
814 The third parameter is optional. If given, it should be a hashref as returned
815 by C4::Auth::getuserflags. If not, getuserflags is called.
817 If user is authorised to manage basket, returns 1.
822 sub CanUserManageBasket {
823 my ($borrower, $basket, $userflags) = @_;
825 if (!ref $borrower) {
826 # FIXME This needs to be replaced
827 # We should not accept both scalar and array
828 # Tests need to be updated
829 $borrower = Koha::Patrons->find( $borrower )->unblessed;
832 $basket = GetBasket($basket);
835 return 0 unless ($basket and $borrower);
837 my $borrowernumber = $borrower->{borrowernumber};
838 my $basketno = $basket->{basketno};
840 my $AcqViewBaskets = C4::Context->preference('AcqViewBaskets');
842 if (!defined $userflags) {
843 my $dbh = C4::Context->dbh;
844 my $sth = $dbh->prepare("SELECT flags FROM borrowers WHERE borrowernumber = ?");
845 $sth->execute($borrowernumber);
846 my ($flags) = $sth->fetchrow_array;
849 $userflags = C4::Auth::getuserflags($flags, $borrower->{userid}, $dbh);
852 unless ($userflags->{superlibrarian}
853 || (ref $userflags->{acquisition} && $userflags->{acquisition}->{order_manage_all})
854 || (!ref $userflags->{acquisition} && $userflags->{acquisition}))
856 if (not exists $userflags->{acquisition}) {
860 if ( (ref $userflags->{acquisition} && !$userflags->{acquisition}->{order_manage})
861 || (!ref $userflags->{acquisition} && !$userflags->{acquisition}) ) {
865 if ($AcqViewBaskets eq 'user'
866 && $basket->{authorisedby} != $borrowernumber
867 && ! grep { $borrowernumber eq $_ } GetBasketUsers($basketno)) {
871 if ($AcqViewBaskets eq 'branch' && defined $basket->{branch}
872 && $basket->{branch} ne $borrower->{branchcode}) {
880 #------------------------------------------------------------#
882 =head3 GetBasketsByBasketgroup
884 $baskets = &GetBasketsByBasketgroup($basketgroupid);
886 Returns a reference to all baskets that belong to basketgroup $basketgroupid.
890 sub GetBasketsByBasketgroup {
891 my $basketgroupid = shift;
893 SELECT *, aqbasket.booksellerid as booksellerid
895 LEFT JOIN aqcontract USING(contractnumber) WHERE basketgroupid=?
897 my $dbh = C4::Context->dbh;
898 my $sth = $dbh->prepare($query);
899 $sth->execute($basketgroupid);
900 return $sth->fetchall_arrayref({});
903 #------------------------------------------------------------#
905 =head3 NewBasketgroup
907 $basketgroupid = NewBasketgroup(\%hashref);
909 Adds a basketgroup to the aqbasketgroups table, and add the initial baskets to it.
911 $hashref->{'booksellerid'} is the 'id' field of the bookseller in the aqbooksellers table,
913 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
915 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
917 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
919 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
921 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
923 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
925 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
930 my $basketgroupinfo = shift;
931 die "booksellerid is required to create a basketgroup" unless $basketgroupinfo->{'booksellerid'};
932 my $query = "INSERT INTO aqbasketgroups (";
934 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
935 if ( defined $basketgroupinfo->{$field} ) {
936 $query .= "$field, ";
937 push(@params, $basketgroupinfo->{$field});
940 $query .= "booksellerid) VALUES (";
945 push(@params, $basketgroupinfo->{'booksellerid'});
946 my $dbh = C4::Context->dbh;
947 my $sth = $dbh->prepare($query);
948 $sth->execute(@params);
949 my $basketgroupid = $dbh->{'mysql_insertid'};
950 if( $basketgroupinfo->{'basketlist'} ) {
951 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
952 my $query2 = "UPDATE aqbasket SET basketgroupid=? WHERE basketno=?";
953 my $sth2 = $dbh->prepare($query2);
954 $sth2->execute($basketgroupid, $basketno);
957 return $basketgroupid;
960 #------------------------------------------------------------#
962 =head3 ModBasketgroup
964 ModBasketgroup(\%hashref);
966 Modifies a basketgroup in the aqbasketgroups table, and add the baskets to it.
968 $hashref->{'id'} is the 'id' field of the basketgroup in the aqbasketgroup table, this parameter is mandatory,
970 $hashref->{'name'} is the 'name' field of the basketgroup in the aqbasketgroups table,
972 $hashref->{'basketlist'} is a list reference of the 'id's of the baskets that belong to this group,
974 $hashref->{'billingplace'} is the 'billingplace' field of the basketgroup in the aqbasketgroups table,
976 $hashref->{'deliveryplace'} is the 'deliveryplace' field of the basketgroup in the aqbasketgroups table,
978 $hashref->{'freedeliveryplace'} is the 'freedeliveryplace' field of the basketgroup in the aqbasketgroups table,
980 $hashref->{'deliverycomment'} is the 'deliverycomment' field of the basketgroup in the aqbasketgroups table,
982 $hashref->{'closed'} is the 'closed' field of the aqbasketgroups table, it is false if 0, true otherwise.
987 my $basketgroupinfo = shift;
988 die "basketgroup id is required to edit a basketgroup" unless $basketgroupinfo->{'id'};
989 my $dbh = C4::Context->dbh;
990 my $query = "UPDATE aqbasketgroups SET ";
992 foreach my $field (qw(name billingplace deliveryplace freedeliveryplace deliverycomment closed)) {
993 if ( defined $basketgroupinfo->{$field} ) {
994 $query .= "$field=?, ";
995 push(@params, $basketgroupinfo->{$field});
1000 $query .= " WHERE id=?";
1001 push(@params, $basketgroupinfo->{'id'});
1002 my $sth = $dbh->prepare($query);
1003 $sth->execute(@params);
1005 $sth = $dbh->prepare('UPDATE aqbasket SET basketgroupid = NULL WHERE basketgroupid = ?');
1006 $sth->execute($basketgroupinfo->{'id'});
1008 if($basketgroupinfo->{'basketlist'} && @{$basketgroupinfo->{'basketlist'}}){
1009 $sth = $dbh->prepare("UPDATE aqbasket SET basketgroupid=? WHERE basketno=?");
1010 foreach my $basketno (@{$basketgroupinfo->{'basketlist'}}) {
1011 $sth->execute($basketgroupinfo->{'id'}, $basketno);
1017 #------------------------------------------------------------#
1019 =head3 DelBasketgroup
1021 DelBasketgroup($basketgroupid);
1023 Deletes a basketgroup in the aqbasketgroups table, and removes the reference to it from the baskets,
1027 =item C<$basketgroupid> is the 'id' field of the basket in the aqbasketgroup table
1033 sub DelBasketgroup {
1034 my $basketgroupid = shift;
1035 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1036 my $query = "DELETE FROM aqbasketgroups WHERE id=?";
1037 my $dbh = C4::Context->dbh;
1038 my $sth = $dbh->prepare($query);
1039 $sth->execute($basketgroupid);
1043 #------------------------------------------------------------#
1046 =head2 FUNCTIONS ABOUT ORDERS
1048 =head3 GetBasketgroup
1050 $basketgroup = &GetBasketgroup($basketgroupid);
1052 Returns a reference to the hash containing all information about the basketgroup.
1056 sub GetBasketgroup {
1057 my $basketgroupid = shift;
1058 die "basketgroup id is required to edit a basketgroup" unless $basketgroupid;
1059 my $dbh = C4::Context->dbh;
1060 my $result_set = $dbh->selectall_arrayref(
1061 'SELECT * FROM aqbasketgroups WHERE id=?',
1065 return $result_set->[0]; # id is unique
1068 #------------------------------------------------------------#
1070 =head3 GetBasketgroups
1072 $basketgroups = &GetBasketgroups($booksellerid);
1074 Returns a reference to the array of all the basketgroups of bookseller $booksellerid.
1078 sub GetBasketgroups {
1079 my $booksellerid = shift;
1080 die 'bookseller id is required to edit a basketgroup' unless $booksellerid;
1081 my $query = 'SELECT * FROM aqbasketgroups WHERE booksellerid=? ORDER BY id DESC';
1082 my $dbh = C4::Context->dbh;
1083 my $sth = $dbh->prepare($query);
1084 $sth->execute($booksellerid);
1085 return $sth->fetchall_arrayref({});
1088 #------------------------------------------------------------#
1090 =head2 FUNCTIONS ABOUT ORDERS
1094 @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } );
1096 Looks up the pending (non-cancelled) orders with the given basket
1099 If cancelled is set, only cancelled orders will be returned.
1104 my ( $basketno, $params ) = @_;
1106 return () unless $basketno;
1108 my $orderby = $params->{orderby};
1109 my $cancelled = $params->{cancelled} || 0;
1111 my $dbh = C4::Context->dbh;
1113 SELECT biblio.*,biblioitems.*,
1117 $query .= $cancelled
1119 aqorders_transfers.ordernumber_to AS transferred_to,
1120 aqorders_transfers.timestamp AS transferred_to_timestamp
1123 aqorders_transfers.ordernumber_from AS transferred_from,
1124 aqorders_transfers.timestamp AS transferred_from_timestamp
1128 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1129 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1130 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1132 $query .= $cancelled
1134 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber
1137 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1145 $orderby ||= q|biblioitems.publishercode, biblio.title|;
1147 AND (datecancellationprinted IS NOT NULL
1148 AND datecancellationprinted <> '0000-00-00')
1153 q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|;
1155 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
1159 $query .= " ORDER BY $orderby";
1161 $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno );
1166 #------------------------------------------------------------#
1168 =head3 GetOrdersByBiblionumber
1170 @orders = &GetOrdersByBiblionumber($biblionumber);
1172 Looks up the orders with linked to a specific $biblionumber, including
1173 cancelled orders and received orders.
1176 C<@orders> is an array of references-to-hash, whose keys are the
1177 fields from the aqorders, biblio, and biblioitems tables in the Koha database.
1181 sub GetOrdersByBiblionumber {
1182 my $biblionumber = shift;
1183 return unless $biblionumber;
1184 my $dbh = C4::Context->dbh;
1186 SELECT biblio.*,biblioitems.*,
1190 LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id
1191 LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1192 LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber
1193 WHERE aqorders.biblionumber=?
1196 $dbh->selectall_arrayref( $query, { Slice => {} }, $biblionumber );
1197 return @{$result_set};
1201 #------------------------------------------------------------#
1205 $order = &GetOrder($ordernumber);
1207 Looks up an order by order number.
1209 Returns a reference-to-hash describing the order. The keys of
1210 C<$order> are fields from the biblio, biblioitems, aqorders tables of the Koha database.
1215 my ($ordernumber) = @_;
1216 return unless $ordernumber;
1218 my $dbh = C4::Context->dbh;
1219 my $query = qq{SELECT
1223 aqbasket.basketname,
1224 borrowers.branchcode,
1225 biblioitems.publicationyear,
1226 biblio.copyrightdate,
1227 biblioitems.editionstatement,
1231 biblioitems.publishercode,
1232 aqorders.rrp AS unitpricesupplier,
1233 aqorders.ecost AS unitpricelib,
1234 aqorders.claims_count AS claims_count,
1235 aqorders.claimed_date AS claimed_date,
1236 aqbudgets.budget_name AS budget,
1237 aqbooksellers.name AS supplier,
1238 aqbooksellers.id AS supplierid,
1239 biblioitems.publishercode AS publisher,
1240 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
1241 DATE(aqbasket.closedate) AS orderdate,
1242 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity_to_receive,
1243 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
1244 DATEDIFF(CURDATE( ),closedate) AS latesince
1245 FROM aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
1246 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
1247 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
1248 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
1249 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
1250 WHERE aqorders.basketno = aqbasket.basketno
1253 $dbh->selectall_arrayref( $query, { Slice => {} }, $ordernumber );
1255 # result_set assumed to contain 1 match
1256 return $result_set->[0];
1259 =head3 GetLastOrderNotReceivedFromSubscriptionid
1261 $order = &GetLastOrderNotReceivedFromSubscriptionid($subscriptionid);
1263 Returns a reference-to-hash describing the last order not received for a subscription.
1267 sub GetLastOrderNotReceivedFromSubscriptionid {
1268 my ( $subscriptionid ) = @_;
1269 my $dbh = C4::Context->dbh;
1271 SELECT * FROM aqorders
1272 LEFT JOIN subscription
1273 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1274 WHERE aqorders.subscriptionid = ?
1275 AND aqorders.datereceived IS NULL
1279 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid );
1281 # result_set assumed to contain 1 match
1282 return $result_set->[0];
1285 =head3 GetLastOrderReceivedFromSubscriptionid
1287 $order = &GetLastOrderReceivedFromSubscriptionid($subscriptionid);
1289 Returns a reference-to-hash describing the last order received for a subscription.
1293 sub GetLastOrderReceivedFromSubscriptionid {
1294 my ( $subscriptionid ) = @_;
1295 my $dbh = C4::Context->dbh;
1297 SELECT * FROM aqorders
1298 LEFT JOIN subscription
1299 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1300 WHERE aqorders.subscriptionid = ?
1301 AND aqorders.datereceived =
1303 SELECT MAX( aqorders.datereceived )
1305 LEFT JOIN subscription
1306 ON ( aqorders.subscriptionid = subscription.subscriptionid )
1307 WHERE aqorders.subscriptionid = ?
1308 AND aqorders.datereceived IS NOT NULL
1310 ORDER BY ordernumber DESC
1314 $dbh->selectall_arrayref( $query, { Slice => {} }, $subscriptionid, $subscriptionid );
1316 # result_set assumed to contain 1 match
1317 return $result_set->[0];
1321 #------------------------------------------------------------#
1325 &ModOrder(\%hashref);
1327 Modifies an existing order. Updates the order with order number
1328 $hashref->{'ordernumber'} and biblionumber $hashref->{'biblionumber'}. All
1329 other keys of the hash update the fields with the same name in the aqorders
1330 table of the Koha database.
1335 my $orderinfo = shift;
1337 die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '';
1339 my $dbh = C4::Context->dbh;
1342 # update uncertainprice to an integer, just in case (under FF, checked boxes have the value "ON" by default)
1343 $orderinfo->{uncertainprice}=1 if $orderinfo->{uncertainprice};
1345 # delete($orderinfo->{'branchcode'});
1346 # the hash contains a lot of entries not in aqorders, so get the columns ...
1347 my $sth = $dbh->prepare("SELECT * FROM aqorders LIMIT 1;");
1349 my $colnames = $sth->{NAME};
1350 #FIXME Be careful. If aqorders would have columns with diacritics,
1351 #you should need to decode what you get back from NAME.
1352 #See report 10110 and guided_reports.pl
1353 my $query = "UPDATE aqorders SET ";
1355 foreach my $orderinfokey (grep(!/ordernumber/, keys %$orderinfo)){
1356 # ... and skip hash entries that are not in the aqorders table
1357 # FIXME : probably not the best way to do it (would be better to have a correct hash)
1358 next unless grep(/^$orderinfokey$/, @$colnames);
1359 $query .= "$orderinfokey=?, ";
1360 push(@params, $orderinfo->{$orderinfokey});
1363 $query .= "timestamp=NOW() WHERE ordernumber=?";
1364 push(@params, $orderinfo->{'ordernumber'} );
1365 $sth = $dbh->prepare($query);
1366 $sth->execute(@params);
1370 #------------------------------------------------------------#
1374 ModItemOrder($itemnumber, $ordernumber);
1376 Modifies the ordernumber of an item in aqorders_items.
1381 my ($itemnumber, $ordernumber) = @_;
1383 return unless ($itemnumber and $ordernumber);
1385 my $dbh = C4::Context->dbh;
1387 UPDATE aqorders_items
1389 WHERE itemnumber = ?
1391 my $sth = $dbh->prepare($query);
1392 return $sth->execute($ordernumber, $itemnumber);
1395 #------------------------------------------------------------#
1397 =head3 ModReceiveOrder
1399 my ( $date_received, $new_ordernumber ) = ModReceiveOrder(
1401 biblionumber => $biblionumber,
1403 quantityreceived => $quantityreceived,
1405 invoice => $invoice,
1406 budget_id => $budget_id,
1407 received_itemnumbers => \@received_itemnumbers,
1408 order_internalnote => $order_internalnote,
1412 Updates an order, to reflect the fact that it was received, at least
1415 If a partial order is received, splits the order into two.
1417 Updates the order with biblionumber C<$biblionumber> and ordernumber
1418 C<$order->{ordernumber}>.
1423 sub ModReceiveOrder {
1425 my $biblionumber = $params->{biblionumber};
1426 my $order = { %{ $params->{order} } }; # Copy the order, we don't want to modify it
1427 my $invoice = $params->{invoice};
1428 my $quantrec = $params->{quantityreceived};
1429 my $user = $params->{user};
1430 my $budget_id = $params->{budget_id};
1431 my $received_items = $params->{received_items};
1433 my $dbh = C4::Context->dbh;
1434 my $datereceived = ( $invoice and $invoice->{datereceived} ) ? $invoice->{datereceived} : dt_from_string;
1435 my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber );
1436 if ($suggestionid) {
1437 ModSuggestion( {suggestionid=>$suggestionid,
1438 STATUS=>'AVAILABLE',
1439 biblionumber=> $biblionumber}
1443 my $result_set = $dbh->selectrow_arrayref(
1444 q{SELECT aqbasket.is_standing
1446 WHERE basketno=?},{ Slice => {} }, $order->{basketno});
1447 my $is_standing = $result_set->[0]; # we assume we have a unique basket
1449 my $new_ordernumber = $order->{ordernumber};
1450 if ( $is_standing || $order->{quantity} > $quantrec ) {
1451 # Split order line in two parts: the first is the original order line
1452 # without received items (the quantity is decreased),
1453 # the second part is a new order line with quantity=quantityrec
1454 # (entirely received)
1458 orderstatus = 'partial'|;
1459 $query .= q|, order_internalnote = ?| if defined $order->{order_internalnote};
1460 $query .= q| WHERE ordernumber = ?|;
1461 my $sth = $dbh->prepare($query);
1464 ( $is_standing ? 1 : ($order->{quantity} - $quantrec) ),
1465 ( defined $order->{order_internalnote} ? $order->{order_internalnote} : () ),
1466 $order->{ordernumber}
1469 # Recalculate tax_value
1473 tax_value_on_ordering = quantity * ecost_tax_excluded * tax_rate_on_ordering,
1474 tax_value_on_receiving = quantity * unitprice_tax_excluded * tax_rate_on_receiving
1475 WHERE ordernumber = ?
1476 |, undef, $order->{ordernumber});
1478 delete $order->{ordernumber};
1479 $order->{budget_id} = ( $budget_id || $order->{budget_id} );
1480 $order->{quantity} = $quantrec;
1481 $order->{quantityreceived} = $quantrec;
1482 $order->{ecost_tax_excluded} //= 0;
1483 $order->{tax_rate_on_ordering} //= 0;
1484 $order->{unitprice_tax_excluded} //= 0;
1485 $order->{tax_rate_on_receiving} //= 0;
1486 $order->{tax_value_on_ordering} = $order->{quantity} * $order->{ecost_tax_excluded} * $order->{tax_rate_on_ordering};
1487 $order->{tax_value_on_receiving} = $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate_on_receiving};
1488 $order->{datereceived} = $datereceived;
1489 $order->{invoiceid} = $invoice->{invoiceid};
1490 $order->{orderstatus} = 'complete';
1491 $new_ordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber; # TODO What if the store fails?
1493 if ($received_items) {
1494 foreach my $itemnumber (@$received_items) {
1495 ModItemOrder($itemnumber, $new_ordernumber);
1501 SET quantityreceived = ?,
1505 orderstatus = 'complete'
1509 , unitprice = ?, unitprice_tax_included = ?, unitprice_tax_excluded = ?
1510 | if defined $order->{unitprice};
1513 ,tax_value_on_receiving = ?
1514 | if defined $order->{tax_value_on_receiving};
1517 ,tax_rate_on_receiving = ?
1518 | if defined $order->{tax_rate_on_receiving};
1521 , order_internalnote = ?
1522 | if defined $order->{order_internalnote};
1524 $query .= q| where biblionumber=? and ordernumber=?|;
1526 my $sth = $dbh->prepare( $query );
1527 my @params = ( $quantrec, $datereceived, $invoice->{invoiceid}, ( $budget_id ? $budget_id : $order->{budget_id} ) );
1529 if ( defined $order->{unitprice} ) {
1530 push @params, $order->{unitprice}, $order->{unitprice_tax_included}, $order->{unitprice_tax_excluded};
1533 if ( defined $order->{tax_value_on_receiving} ) {
1534 push @params, $order->{tax_value_on_receiving};
1537 if ( defined $order->{tax_rate_on_receiving} ) {
1538 push @params, $order->{tax_rate_on_receiving};
1541 if ( defined $order->{order_internalnote} ) {
1542 push @params, $order->{order_internalnote};
1545 push @params, ( $biblionumber, $order->{ordernumber} );
1547 $sth->execute( @params );
1549 # All items have been received, sent a notification to users
1550 NotifyOrderUsers( $order->{ordernumber} );
1553 return ($datereceived, $new_ordernumber);
1556 =head3 CancelReceipt
1558 my $parent_ordernumber = CancelReceipt($ordernumber);
1560 Cancel an order line receipt and update the parent order line, as if no
1562 If items are created at receipt (AcqCreateItem = receiving) then delete
1568 my $ordernumber = shift;
1570 return unless $ordernumber;
1572 my $dbh = C4::Context->dbh;
1574 SELECT datereceived, parent_ordernumber, quantity
1576 WHERE ordernumber = ?
1578 my $sth = $dbh->prepare($query);
1579 $sth->execute($ordernumber);
1580 my $order = $sth->fetchrow_hashref;
1582 warn "CancelReceipt: order $ordernumber does not exist";
1585 unless($order->{'datereceived'}) {
1586 warn "CancelReceipt: order $ordernumber is not received";
1590 my $parent_ordernumber = $order->{'parent_ordernumber'};
1592 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1593 my $order_obj = Koha::Acquisition::Orders->find( $ordernumber ); # FIXME rewrite all this subroutine using this object
1595 if($parent_ordernumber == $ordernumber || not $parent_ordernumber) {
1596 # The order line has no parent, just mark it as not received
1599 SET quantityreceived = ?,
1602 orderstatus = 'ordered'
1603 WHERE ordernumber = ?
1605 $sth = $dbh->prepare($query);
1606 $sth->execute(0, undef, undef, $ordernumber);
1607 _cancel_items_receipt( $order_obj );
1609 # The order line has a parent, increase parent quantity and delete
1612 SELECT quantity, datereceived
1614 WHERE ordernumber = ?
1616 $sth = $dbh->prepare($query);
1617 $sth->execute($parent_ordernumber);
1618 my $parent_order = $sth->fetchrow_hashref;
1619 unless($parent_order) {
1620 warn "Parent order $parent_ordernumber does not exist.";
1623 if($parent_order->{'datereceived'}) {
1624 warn "CancelReceipt: parent order is received.".
1625 " Can't cancel receipt.";
1631 orderstatus = 'ordered'
1632 WHERE ordernumber = ?
1634 $sth = $dbh->prepare($query);
1635 my $rv = $sth->execute(
1636 $order->{'quantity'} + $parent_order->{'quantity'},
1640 warn "Cannot update parent order line, so do not cancel".
1645 # Recalculate tax_value
1649 tax_value_on_ordering = quantity * ecost_tax_excluded * tax_rate_on_ordering,
1650 tax_value_on_receiving = quantity * unitprice_tax_excluded * tax_rate_on_receiving
1651 WHERE ordernumber = ?
1652 |, undef, $parent_ordernumber);
1654 _cancel_items_receipt( $order_obj, $parent_ordernumber );
1657 DELETE FROM aqorders
1658 WHERE ordernumber = ?
1660 $sth = $dbh->prepare($query);
1661 $sth->execute($ordernumber);
1665 if( $order_obj->basket->effective_create_items eq 'ordering' ) {
1666 my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled");
1668 for my $in ( @itemnumbers ) {
1669 my $item = Koha::Items->find( $in );
1670 my $biblio = $item->biblio;
1671 my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber', $biblio->frameworkcode );
1672 my $item_marc = C4::Items::GetMarcItem( $biblio->biblionumber, $in );
1673 for my $affect ( @affects ) {
1674 my ( $sf, $v ) = split q{=}, $affect, 2;
1675 foreach ( $item_marc->field($itemfield) ) {
1676 $_->update( $sf => $v );
1679 C4::Items::ModItemFromMarc( $item_marc, $biblio->biblionumber, $in );
1684 return $parent_ordernumber;
1687 sub _cancel_items_receipt {
1688 my ( $order, $parent_ordernumber ) = @_;
1689 $parent_ordernumber ||= $order->ordernumber;
1691 my @itemnumbers = GetItemnumbersFromOrder($order->ordernumber); # FIXME Must be $order->items
1692 if ( $order->basket->effective_create_items eq 'receiving' ) {
1693 # Remove items that were created at receipt
1695 DELETE FROM items, aqorders_items
1696 USING items, aqorders_items
1697 WHERE items.itemnumber = ? AND aqorders_items.itemnumber = ?
1699 my $dbh = C4::Context->dbh;
1700 my $sth = $dbh->prepare($query);
1701 foreach my $itemnumber (@itemnumbers) {
1702 $sth->execute($itemnumber, $itemnumber);
1706 foreach my $itemnumber (@itemnumbers) {
1707 ModItemOrder($itemnumber, $parent_ordernumber);
1712 #------------------------------------------------------------#
1716 @results = &SearchOrders({
1717 ordernumber => $ordernumber,
1720 booksellerid => $booksellerid,
1721 basketno => $basketno,
1722 basketname => $basketname,
1723 basketgroupname => $basketgroupname,
1727 biblionumber => $biblionumber,
1728 budget_id => $budget_id
1731 Searches for orders filtered by criteria.
1733 C<$ordernumber> Finds matching orders or transferred orders by ordernumber.
1734 C<$search> Finds orders matching %$search% in title, author, or isbn.
1735 C<$owner> Finds order for the logged in user.
1736 C<$pending> Finds pending orders. Ignores completed and cancelled orders.
1737 C<$ordered> Finds orders to receive only (status 'ordered' or 'partial').
1740 C<@results> is an array of references-to-hash with the keys are fields
1741 from aqorders, biblio, biblioitems and aqbasket tables.
1746 my ( $params ) = @_;
1747 my $ordernumber = $params->{ordernumber};
1748 my $search = $params->{search};
1749 my $ean = $params->{ean};
1750 my $booksellerid = $params->{booksellerid};
1751 my $basketno = $params->{basketno};
1752 my $basketname = $params->{basketname};
1753 my $basketgroupname = $params->{basketgroupname};
1754 my $owner = $params->{owner};
1755 my $pending = $params->{pending};
1756 my $ordered = $params->{ordered};
1757 my $biblionumber = $params->{biblionumber};
1758 my $budget_id = $params->{budget_id};
1760 my $dbh = C4::Context->dbh;
1763 SELECT aqbasket.basketno,
1765 borrowers.firstname,
1768 biblioitems.biblioitemnumber,
1769 biblioitems.publishercode,
1770 biblioitems.publicationyear,
1771 aqbasket.authorisedby,
1772 aqbasket.booksellerid,
1774 aqbasket.creationdate,
1775 aqbasket.basketname,
1776 aqbasketgroups.id as basketgroupid,
1777 aqbasketgroups.name as basketgroupname,
1780 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
1781 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
1782 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
1783 LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber
1784 LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber
1787 # If we search on ordernumber, we retrieve the transferred order if a transfer has been done.
1789 LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber
1793 WHERE (datecancellationprinted is NULL)
1796 if ( $pending or $ordered ) {
1799 ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) )
1801 ( quantity > quantityreceived OR quantityreceived is NULL )
1805 $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )};
1813 my $userenv = C4::Context->userenv;
1814 if ( C4::Context->preference("IndependentBranches") ) {
1815 unless ( C4::Context->IsSuperLibrarian() ) {
1818 borrowers.branchcode = ?
1819 OR borrowers.branchcode = ''
1822 push @args, $userenv->{branch};
1826 if ( $ordernumber ) {
1827 $query .= ' AND ( aqorders.ordernumber = ? OR aqorders_transfers.ordernumber_from = ? ) ';
1828 push @args, ( $ordernumber, $ordernumber );
1830 if ( $biblionumber ) {
1831 $query .= 'AND aqorders.biblionumber = ?';
1832 push @args, $biblionumber;
1835 $query .= ' AND (biblio.title LIKE ? OR biblio.author LIKE ? OR biblioitems.isbn LIKE ?)';
1836 push @args, ("%$search%","%$search%","%$search%");
1839 $query .= ' AND biblioitems.ean = ?';
1842 if ( $booksellerid ) {
1843 $query .= 'AND aqbasket.booksellerid = ?';
1844 push @args, $booksellerid;
1847 $query .= 'AND aqbasket.basketno = ?';
1848 push @args, $basketno;
1851 $query .= 'AND aqbasket.basketname LIKE ?';
1852 push @args, "%$basketname%";
1854 if( $basketgroupname ) {
1855 $query .= ' AND aqbasketgroups.name LIKE ?';
1856 push @args, "%$basketgroupname%";
1860 $query .= ' AND aqbasket.authorisedby=? ';
1861 push @args, $userenv->{'number'};
1865 $query .= ' AND aqorders.budget_id = ?';
1866 push @args, $budget_id;
1869 $query .= ' ORDER BY aqbasket.basketno';
1871 my $sth = $dbh->prepare($query);
1872 $sth->execute(@args);
1873 return $sth->fetchall_arrayref({});
1876 #------------------------------------------------------------#
1880 &DelOrder($biblionumber, $ordernumber);
1882 Cancel the order with the given order and biblio numbers. It does not
1883 delete any entries in the aqorders table, it merely marks them as
1889 my ( $bibnum, $ordernumber, $delete_biblio, $reason ) = @_;
1892 my $dbh = C4::Context->dbh;
1895 SET datecancellationprinted=now(), orderstatus='cancelled'
1898 $query .= ", cancellationreason = ? ";
1901 WHERE biblionumber=? AND ordernumber=?
1903 my $sth = $dbh->prepare($query);
1905 $sth->execute($reason, $bibnum, $ordernumber);
1907 $sth->execute( $bibnum, $ordernumber );
1911 my @itemnumbers = GetItemnumbersFromOrder( $ordernumber );
1912 foreach my $itemnumber (@itemnumbers){
1913 my $delcheck = C4::Items::DelItemCheck( $bibnum, $itemnumber );
1915 if($delcheck != 1) {
1916 $error->{'delitem'} = 1;
1920 if($delete_biblio) {
1921 # We get the number of remaining items
1922 my $biblio = Koha::Biblios->find( $bibnum );
1923 my $itemcount = $biblio->items->count;
1925 # If there are no items left,
1926 if ( $itemcount == 0 ) {
1927 # We delete the record
1928 my $delcheck = DelBiblio($bibnum);
1931 $error->{'delbiblio'} = 1;
1939 =head3 TransferOrder
1941 my $newordernumber = TransferOrder($ordernumber, $basketno);
1943 Transfer an order line to a basket.
1944 Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred
1945 to BOOKSELLER on DATE' and create new order with internal note
1946 'Transferred from BOOKSELLER on DATE'.
1947 Move all attached items to the new order.
1948 Received orders cannot be transferred.
1949 Return the ordernumber of created order.
1954 my ($ordernumber, $basketno) = @_;
1956 return unless ($ordernumber and $basketno);
1958 my $order = Koha::Acquisition::Orders->find( $ordernumber ) or return;
1959 return if $order->datereceived;
1961 $order = $order->unblessed;
1963 my $basket = GetBasket($basketno);
1964 return unless $basket;
1966 my $dbh = C4::Context->dbh;
1967 my ($query, $sth, $rv);
1971 SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ?
1972 WHERE ordernumber = ?
1974 $sth = $dbh->prepare($query);
1975 $rv = $sth->execute('cancelled', $ordernumber);
1977 delete $order->{'ordernumber'};
1978 delete $order->{parent_ordernumber};
1979 $order->{'basketno'} = $basketno;
1981 my $newordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber;
1984 UPDATE aqorders_items
1986 WHERE ordernumber = ?
1988 $sth = $dbh->prepare($query);
1989 $sth->execute($newordernumber, $ordernumber);
1992 INSERT INTO aqorders_transfers (ordernumber_from, ordernumber_to)
1995 $sth = $dbh->prepare($query);
1996 $sth->execute($ordernumber, $newordernumber);
1998 return $newordernumber;
2001 =head2 FUNCTIONS ABOUT PARCELS
2005 $results = &GetParcels($bookseller, $order, $code, $datefrom, $dateto);
2007 get a lists of parcels.
2014 is the bookseller this function has to get parcels.
2017 To know on what criteria the results list has to be ordered.
2020 is the booksellerinvoicenumber.
2022 =item $datefrom & $dateto
2023 to know on what date this function has to filter its search.
2028 a pointer on a hash list containing parcel informations as such :
2034 =item Last operation
2036 =item Number of biblio
2038 =item Number of items
2045 my ($bookseller,$order, $code, $datefrom, $dateto) = @_;
2046 my $dbh = C4::Context->dbh;
2047 my @query_params = ();
2049 SELECT aqinvoices.invoicenumber,
2050 datereceived,purchaseordernumber,
2051 count(DISTINCT biblionumber) AS biblio,
2052 sum(quantity) AS itemsexpected,
2053 sum(quantityreceived) AS itemsreceived
2054 FROM aqorders LEFT JOIN aqbasket ON aqbasket.basketno = aqorders.basketno
2055 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2056 WHERE aqbasket.booksellerid = ? and datereceived IS NOT NULL
2058 push @query_params, $bookseller;
2060 if ( defined $code ) {
2061 $strsth .= ' and aqinvoices.invoicenumber like ? ';
2062 # add a % to the end of the code to allow stemming.
2063 push @query_params, "$code%";
2066 if ( defined $datefrom ) {
2067 $strsth .= ' and datereceived >= ? ';
2068 push @query_params, $datefrom;
2071 if ( defined $dateto ) {
2072 $strsth .= 'and datereceived <= ? ';
2073 push @query_params, $dateto;
2076 $strsth .= "group by aqinvoices.invoicenumber,datereceived ";
2078 # can't use a placeholder to place this column name.
2079 # but, we could probably be checking to make sure it is a column that will be fetched.
2080 $strsth .= "order by $order " if ($order);
2082 my $sth = $dbh->prepare($strsth);
2084 $sth->execute( @query_params );
2085 my $results = $sth->fetchall_arrayref({});
2089 #------------------------------------------------------------#
2091 =head3 GetLateOrders
2093 @results = &GetLateOrders;
2095 Searches for bookseller with late orders.
2098 the table of supplier with late issues. This table is full of hashref.
2104 my $supplierid = shift;
2106 my $estimateddeliverydatefrom = shift;
2107 my $estimateddeliverydateto = shift;
2109 my $dbh = C4::Context->dbh;
2111 #BEWARE, order of parenthesis and LEFT JOIN is important for speed
2112 my $dbdriver = C4::Context->config("db_scheme") || "mysql";
2114 my @query_params = ();
2116 SELECT aqbasket.basketno,
2117 aqorders.ordernumber,
2118 DATE(aqbasket.closedate) AS orderdate,
2119 aqbasket.basketname AS basketname,
2120 aqbasket.basketgroupid AS basketgroupid,
2121 aqbasketgroups.name AS basketgroupname,
2122 aqorders.rrp AS unitpricesupplier,
2123 aqorders.ecost AS unitpricelib,
2124 aqorders.claims_count AS claims_count,
2125 aqorders.claimed_date AS claimed_date,
2126 aqbudgets.budget_name AS budget,
2127 borrowers.branchcode AS branch,
2128 aqbooksellers.name AS supplier,
2129 aqbooksellers.id AS supplierid,
2130 biblio.author, biblio.title,
2131 biblioitems.publishercode AS publisher,
2132 biblioitems.publicationyear,
2133 ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) AS estimateddeliverydate,
2137 aqorders LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber
2138 LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber
2139 LEFT JOIN aqbudgets ON aqorders.budget_id = aqbudgets.budget_id,
2140 aqbasket LEFT JOIN borrowers ON aqbasket.authorisedby = borrowers.borrowernumber
2141 LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id
2142 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2143 WHERE aqorders.basketno = aqbasket.basketno
2144 AND ( datereceived = ''
2145 OR datereceived IS NULL
2146 OR aqorders.quantityreceived < aqorders.quantity
2148 AND aqbasket.closedate IS NOT NULL
2149 AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00')
2152 if ($dbdriver eq "mysql") {
2154 aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity,
2155 (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal,
2156 DATEDIFF(CAST(now() AS date),closedate) AS latesince
2158 if ( defined $delay ) {
2159 $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ;
2160 push @query_params, $delay;
2162 $having = "HAVING quantity <> 0";
2164 # FIXME: account for IFNULL as above
2166 aqorders.quantity AS quantity,
2167 aqorders.quantity * aqorders.rrp AS subtotal,
2168 (CAST(now() AS date) - closedate) AS latesince
2170 if ( defined $delay ) {
2171 $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) ";
2172 push @query_params, $delay;
2175 if (defined $supplierid) {
2176 $from .= ' AND aqbasket.booksellerid = ? ';
2177 push @query_params, $supplierid;
2179 if (defined $branch) {
2180 $from .= ' AND borrowers.branchcode LIKE ? ';
2181 push @query_params, $branch;
2184 if ( defined $estimateddeliverydatefrom or defined $estimateddeliverydateto ) {
2185 $from .= ' AND aqbooksellers.deliverytime IS NOT NULL ';
2187 if ( defined $estimateddeliverydatefrom ) {
2188 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) >= ?';
2189 push @query_params, $estimateddeliverydatefrom;
2191 if ( defined $estimateddeliverydateto ) {
2192 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= ?';
2193 push @query_params, $estimateddeliverydateto;
2195 if ( defined $estimateddeliverydatefrom and not defined $estimateddeliverydateto ) {
2196 $from .= ' AND ADDDATE(aqbasket.closedate, INTERVAL aqbooksellers.deliverytime DAY) <= CAST(now() AS date)';
2198 if (C4::Context->preference("IndependentBranches")
2199 && !C4::Context->IsSuperLibrarian() ) {
2200 $from .= ' AND borrowers.branchcode LIKE ? ';
2201 push @query_params, C4::Context->userenv->{branch};
2203 $from .= " AND orderstatus <> 'cancelled' ";
2204 my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier";
2205 $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params);
2206 my $sth = $dbh->prepare($query);
2207 $sth->execute(@query_params);
2209 while (my $data = $sth->fetchrow_hashref) {
2210 push @results, $data;
2215 #------------------------------------------------------------#
2219 \@order_loop = GetHistory( %params );
2221 Retreives some acquisition history information
2231 basket - search both basket name and number
2232 booksellerinvoicenumber
2235 orderstatus (note that orderstatus '' will retrieve orders
2236 of any status except cancelled)
2238 get_canceled_order (if set to a true value, cancelled orders will
2242 $order_loop is a list of hashrefs that each look like this:
2244 'author' => 'Twain, Mark',
2246 'biblionumber' => '215',
2248 'creationdate' => 'MM/DD/YYYY',
2249 'datereceived' => undef,
2252 'invoicenumber' => undef,
2254 'ordernumber' => '1',
2256 'quantityreceived' => undef,
2257 'title' => 'The Adventures of Huckleberry Finn'
2263 # don't run the query if there are no parameters (list would be too long for sure !)
2264 croak "No search params" unless @_;
2266 my $title = $params{title};
2267 my $author = $params{author};
2268 my $isbn = $params{isbn};
2269 my $ean = $params{ean};
2270 my $name = $params{name};
2271 my $from_placed_on = $params{from_placed_on};
2272 my $to_placed_on = $params{to_placed_on};
2273 my $basket = $params{basket};
2274 my $booksellerinvoicenumber = $params{booksellerinvoicenumber};
2275 my $basketgroupname = $params{basketgroupname};
2276 my $budget = $params{budget};
2277 my $orderstatus = $params{orderstatus};
2278 my $biblionumber = $params{biblionumber};
2279 my $get_canceled_order = $params{get_canceled_order} || 0;
2280 my $ordernumber = $params{ordernumber};
2281 my $search_children_too = $params{search_children_too} || 0;
2282 my $created_by = $params{created_by} || [];
2286 my $total_qtyreceived = 0;
2287 my $total_price = 0;
2289 my $dbh = C4::Context->dbh;
2292 COALESCE(biblio.title, deletedbiblio.title) AS title,
2293 COALESCE(biblio.author, deletedbiblio.author) AS author,
2294 COALESCE(biblioitems.isbn, deletedbiblioitems.isbn) AS isbn,
2295 COALESCE(biblioitems.ean, deletedbiblioitems.ean) AS ean,
2297 aqbasket.basketname,
2298 aqbasket.basketgroupid,
2299 aqbasket.authorisedby,
2300 concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname,
2301 aqbasketgroups.name as groupname,
2303 aqbasket.creationdate,
2304 aqorders.datereceived,
2306 aqorders.quantityreceived,
2308 aqorders.ordernumber,
2310 aqinvoices.invoicenumber,
2311 aqbooksellers.id as id,
2312 aqorders.biblionumber,
2313 aqorders.orderstatus,
2314 aqorders.parent_ordernumber,
2315 aqbudgets.budget_name
2317 $query .= ", aqbudgets.budget_id AS budget" if defined $budget;
2320 LEFT JOIN aqbasket ON aqorders.basketno=aqbasket.basketno
2321 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid=aqbasketgroups.id
2322 LEFT JOIN aqbooksellers ON aqbasket.booksellerid=aqbooksellers.id
2323 LEFT JOIN biblioitems ON biblioitems.biblionumber=aqorders.biblionumber
2324 LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber
2325 LEFT JOIN aqbudgets ON aqorders.budget_id=aqbudgets.budget_id
2326 LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid
2327 LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber
2328 LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber
2329 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2332 $query .= " WHERE 1 ";
2334 unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) {
2335 $query .= " AND (datecancellationprinted is NULL or datecancellationprinted='0000-00-00') ";
2338 my @query_params = ();
2340 if ( $biblionumber ) {
2341 $query .= " AND biblio.biblionumber = ?";
2342 push @query_params, $biblionumber;
2346 $query .= " AND biblio.title LIKE ? ";
2347 $title =~ s/\s+/%/g;
2348 push @query_params, "%$title%";
2352 $query .= " AND biblio.author LIKE ? ";
2353 push @query_params, "%$author%";
2357 $query .= " AND biblioitems.isbn LIKE ? ";
2358 push @query_params, "%$isbn%";
2361 $query .= " AND biblioitems.ean = ? ";
2362 push @query_params, "$ean";
2365 $query .= " AND aqbooksellers.name LIKE ? ";
2366 push @query_params, "%$name%";
2370 $query .= " AND aqbudgets.budget_id = ? ";
2371 push @query_params, "$budget";
2374 if ( $from_placed_on ) {
2375 $query .= " AND creationdate >= ? ";
2376 push @query_params, $from_placed_on;
2379 if ( $to_placed_on ) {
2380 $query .= " AND creationdate <= ? ";
2381 push @query_params, $to_placed_on;
2384 if ( defined $orderstatus and $orderstatus ne '') {
2385 $query .= " AND aqorders.orderstatus = ? ";
2386 push @query_params, "$orderstatus";
2390 if ($basket =~ m/^\d+$/) {
2391 $query .= " AND aqorders.basketno = ? ";
2392 push @query_params, $basket;
2394 $query .= " AND aqbasket.basketname LIKE ? ";
2395 push @query_params, "%$basket%";
2399 if ($booksellerinvoicenumber) {
2400 $query .= " AND aqinvoices.invoicenumber LIKE ? ";
2401 push @query_params, "%$booksellerinvoicenumber%";
2404 if ($basketgroupname) {
2405 $query .= " AND aqbasketgroups.name LIKE ? ";
2406 push @query_params, "%$basketgroupname%";
2410 $query .= " AND (aqorders.ordernumber = ? ";
2411 push @query_params, $ordernumber;
2412 if ($search_children_too) {
2413 $query .= " OR aqorders.parent_ordernumber = ? ";
2414 push @query_params, $ordernumber;
2419 if ( @$created_by ) {
2420 $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')';
2421 push @query_params, @$created_by;
2425 if ( C4::Context->preference("IndependentBranches") ) {
2426 unless ( C4::Context->IsSuperLibrarian() ) {
2427 $query .= " AND (borrowers.branchcode = ? OR borrowers.branchcode ='' ) ";
2428 push @query_params, C4::Context->userenv->{branch};
2431 $query .= " ORDER BY id";
2433 return $dbh->selectall_arrayref( $query, { Slice => {} }, @query_params );
2436 =head2 GetRecentAcqui
2438 $results = GetRecentAcqui($days);
2440 C<$results> is a ref to a table which contains hashref
2444 sub GetRecentAcqui {
2446 my $dbh = C4::Context->dbh;
2450 ORDER BY timestamp DESC
2453 my $sth = $dbh->prepare($query);
2455 my $results = $sth->fetchall_arrayref({});
2459 #------------------------------------------------------------#
2463 &AddClaim($ordernumber);
2465 Add a claim for an order
2470 my ($ordernumber) = @_;
2471 my $dbh = C4::Context->dbh;
2474 claims_count = claims_count + 1,
2475 claimed_date = CURDATE()
2476 WHERE ordernumber = ?
2478 my $sth = $dbh->prepare($query);
2479 $sth->execute($ordernumber);
2484 my @invoices = GetInvoices(
2485 invoicenumber => $invoicenumber,
2486 supplierid => $supplierid,
2487 suppliername => $suppliername,
2488 shipmentdatefrom => $shipmentdatefrom, # ISO format
2489 shipmentdateto => $shipmentdateto, # ISO format
2490 billingdatefrom => $billingdatefrom, # ISO format
2491 billingdateto => $billingdateto, # ISO format
2492 isbneanissn => $isbn_or_ean_or_issn,
2495 publisher => $publisher,
2496 publicationyear => $publicationyear,
2497 branchcode => $branchcode,
2498 order_by => $order_by
2501 Return a list of invoices that match all given criteria.
2503 $order_by is "column_name (asc|desc)", where column_name is any of
2504 'invoicenumber', 'booksellerid', 'shipmentdate', 'billingdate', 'closedate',
2505 'shipmentcost', 'shipmentcost_budgetid'.
2507 asc is the default if omitted
2514 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2515 closedate shipmentcost shipmentcost_budgetid);
2517 my $dbh = C4::Context->dbh;
2519 SELECT aqinvoices.*, aqbooksellers.name AS suppliername,
2522 aqorders.datereceived IS NOT NULL,
2523 aqorders.biblionumber,
2526 ) AS receivedbiblios,
2529 aqorders.subscriptionid IS NOT NULL,
2530 aqorders.subscriptionid,
2533 ) AS is_linked_to_subscriptions,
2534 SUM(aqorders.quantityreceived) AS receiveditems
2536 LEFT JOIN aqbooksellers ON aqbooksellers.id = aqinvoices.booksellerid
2537 LEFT JOIN aqorders ON aqorders.invoiceid = aqinvoices.invoiceid
2538 LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno
2539 LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber
2540 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2541 LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
2542 LEFT JOIN subscription ON biblio.biblionumber = subscription.biblionumber
2547 if($args{supplierid}) {
2548 push @bind_strs, " aqinvoices.booksellerid = ? ";
2549 push @bind_args, $args{supplierid};
2551 if($args{invoicenumber}) {
2552 push @bind_strs, " aqinvoices.invoicenumber LIKE ? ";
2553 push @bind_args, "%$args{invoicenumber}%";
2555 if($args{suppliername}) {
2556 push @bind_strs, " aqbooksellers.name LIKE ? ";
2557 push @bind_args, "%$args{suppliername}%";
2559 if($args{shipmentdatefrom}) {
2560 push @bind_strs, " aqinvoices.shipmentdate >= ? ";
2561 push @bind_args, $args{shipmentdatefrom};
2563 if($args{shipmentdateto}) {
2564 push @bind_strs, " aqinvoices.shipmentdate <= ? ";
2565 push @bind_args, $args{shipmentdateto};
2567 if($args{billingdatefrom}) {
2568 push @bind_strs, " aqinvoices.billingdate >= ? ";
2569 push @bind_args, $args{billingdatefrom};
2571 if($args{billingdateto}) {
2572 push @bind_strs, " aqinvoices.billingdate <= ? ";
2573 push @bind_args, $args{billingdateto};
2575 if($args{isbneanissn}) {
2576 push @bind_strs, " (biblioitems.isbn LIKE CONCAT('%', ?, '%') OR biblioitems.ean LIKE CONCAT('%', ?, '%') OR biblioitems.issn LIKE CONCAT('%', ?, '%') ) ";
2577 push @bind_args, $args{isbneanissn}, $args{isbneanissn}, $args{isbneanissn};
2580 push @bind_strs, " biblio.title LIKE CONCAT('%', ?, '%') ";
2581 push @bind_args, $args{title};
2584 push @bind_strs, " biblio.author LIKE CONCAT('%', ?, '%') ";
2585 push @bind_args, $args{author};
2587 if($args{publisher}) {
2588 push @bind_strs, " biblioitems.publishercode LIKE CONCAT('%', ?, '%') ";
2589 push @bind_args, $args{publisher};
2591 if($args{publicationyear}) {
2592 push @bind_strs, " ((biblioitems.publicationyear LIKE CONCAT('%', ?, '%')) OR (biblio.copyrightdate LIKE CONCAT('%', ?, '%'))) ";
2593 push @bind_args, $args{publicationyear}, $args{publicationyear};
2595 if($args{branchcode}) {
2596 push @bind_strs, " borrowers.branchcode = ? ";
2597 push @bind_args, $args{branchcode};
2599 if($args{message_id}) {
2600 push @bind_strs, " aqinvoices.message_id = ? ";
2601 push @bind_args, $args{message_id};
2604 $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs;
2605 $query .= " GROUP BY aqinvoices.invoiceid ";
2607 if($args{order_by}) {
2608 my ($column, $direction) = split / /, $args{order_by};
2609 if(grep /^$column$/, @columns) {
2610 $direction ||= 'ASC';
2611 $query .= " ORDER BY $column $direction";
2615 my $sth = $dbh->prepare($query);
2616 $sth->execute(@bind_args);
2618 my $results = $sth->fetchall_arrayref({});
2624 my $invoice = GetInvoice($invoiceid);
2626 Get informations about invoice with given $invoiceid
2628 Return a hash filled with aqinvoices.* fields
2633 my ($invoiceid) = @_;
2636 return unless $invoiceid;
2638 my $dbh = C4::Context->dbh;
2644 my $sth = $dbh->prepare($query);
2645 $sth->execute($invoiceid);
2647 $invoice = $sth->fetchrow_hashref;
2651 =head3 GetInvoiceDetails
2653 my $invoice = GetInvoiceDetails($invoiceid)
2655 Return informations about an invoice + the list of related order lines
2657 Orders informations are in $invoice->{orders} (array ref)
2661 sub GetInvoiceDetails {
2662 my ($invoiceid) = @_;
2664 if ( !defined $invoiceid ) {
2665 carp 'GetInvoiceDetails called without an invoiceid';
2669 my $dbh = C4::Context->dbh;
2671 SELECT aqinvoices.*, aqbooksellers.name AS suppliername
2673 LEFT JOIN aqbooksellers ON aqinvoices.booksellerid = aqbooksellers.id
2676 my $sth = $dbh->prepare($query);
2677 $sth->execute($invoiceid);
2679 my $invoice = $sth->fetchrow_hashref;
2684 biblio.copyrightdate,
2686 biblioitems.publishercode,
2687 biblioitems.publicationyear,
2688 aqbasket.basketname,
2689 aqbasketgroups.id AS basketgroupid,
2690 aqbasketgroups.name AS basketgroupname
2692 LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno
2693 LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id
2694 LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber
2695 LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber
2698 $sth = $dbh->prepare($query);
2699 $sth->execute($invoiceid);
2700 $invoice->{orders} = $sth->fetchall_arrayref({});
2701 $invoice->{orders} ||= []; # force an empty arrayref if fetchall_arrayref fails
2708 my $invoiceid = AddInvoice(
2709 invoicenumber => $invoicenumber,
2710 booksellerid => $booksellerid,
2711 shipmentdate => $shipmentdate,
2712 billingdate => $billingdate,
2713 closedate => $closedate,
2714 shipmentcost => $shipmentcost,
2715 shipmentcost_budgetid => $shipmentcost_budgetid
2718 Create a new invoice and return its id or undef if it fails.
2725 return unless(%invoice and $invoice{invoicenumber});
2727 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2728 closedate shipmentcost shipmentcost_budgetid message_id);
2732 foreach my $key (keys %invoice) {
2733 if(0 < grep(/^$key$/, @columns)) {
2734 push @set_strs, "$key = ?";
2735 push @set_args, ($invoice{$key} || undef);
2741 my $dbh = C4::Context->dbh;
2742 my $query = "INSERT INTO aqinvoices SET ";
2743 $query .= join (",", @set_strs);
2744 my $sth = $dbh->prepare($query);
2745 $rv = $sth->execute(@set_args);
2747 $rv = $dbh->last_insert_id(undef, undef, 'aqinvoices', undef);
2756 invoiceid => $invoiceid, # Mandatory
2757 invoicenumber => $invoicenumber,
2758 booksellerid => $booksellerid,
2759 shipmentdate => $shipmentdate,
2760 billingdate => $billingdate,
2761 closedate => $closedate,
2762 shipmentcost => $shipmentcost,
2763 shipmentcost_budgetid => $shipmentcost_budgetid
2766 Modify an invoice, invoiceid is mandatory.
2768 Return undef if it fails.
2775 return unless(%invoice and $invoice{invoiceid});
2777 my @columns = qw(invoicenumber booksellerid shipmentdate billingdate
2778 closedate shipmentcost shipmentcost_budgetid);
2782 foreach my $key (keys %invoice) {
2783 if(0 < grep(/^$key$/, @columns)) {
2784 push @set_strs, "$key = ?";
2785 push @set_args, ($invoice{$key} || undef);
2789 my $dbh = C4::Context->dbh;
2790 my $query = "UPDATE aqinvoices SET ";
2791 $query .= join(",", @set_strs);
2792 $query .= " WHERE invoiceid = ?";
2794 my $sth = $dbh->prepare($query);
2795 $sth->execute(@set_args, $invoice{invoiceid});
2800 CloseInvoice($invoiceid);
2804 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => undef);
2809 my ($invoiceid) = @_;
2811 return unless $invoiceid;
2813 my $dbh = C4::Context->dbh;
2816 SET closedate = CAST(NOW() AS DATE)
2819 my $sth = $dbh->prepare($query);
2820 $sth->execute($invoiceid);
2823 =head3 ReopenInvoice
2825 ReopenInvoice($invoiceid);
2829 Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => output_pref({ dt=>dt_from_string, dateonly=>1, otputpref=>'iso' }))
2834 my ($invoiceid) = @_;
2836 return unless $invoiceid;
2838 my $dbh = C4::Context->dbh;
2841 SET closedate = NULL
2844 my $sth = $dbh->prepare($query);
2845 $sth->execute($invoiceid);
2850 DelInvoice($invoiceid);
2852 Delete an invoice if there are no items attached to it.
2857 my ($invoiceid) = @_;
2859 return unless $invoiceid;
2861 my $dbh = C4::Context->dbh;
2867 my $sth = $dbh->prepare($query);
2868 $sth->execute($invoiceid);
2869 my $res = $sth->fetchrow_arrayref;
2870 if ( $res && $res->[0] == 0 ) {
2872 DELETE FROM aqinvoices
2875 my $sth = $dbh->prepare($query);
2876 return ( $sth->execute($invoiceid) > 0 );
2881 =head3 MergeInvoices
2883 MergeInvoices($invoiceid, \@sourceids);
2885 Merge the invoices identified by the IDs in \@sourceids into
2886 the invoice identified by $invoiceid.
2891 my ($invoiceid, $sourceids) = @_;
2893 return unless $invoiceid;
2894 foreach my $sourceid (@$sourceids) {
2895 next if $sourceid == $invoiceid;
2896 my $source = GetInvoiceDetails($sourceid);
2897 foreach my $order (@{$source->{'orders'}}) {
2898 $order->{'invoiceid'} = $invoiceid;
2901 DelInvoice($source->{'invoiceid'});
2906 =head3 GetBiblioCountByBasketno
2908 $biblio_count = &GetBiblioCountByBasketno($basketno);
2910 Looks up the biblio's count that has basketno value $basketno
2916 sub GetBiblioCountByBasketno {
2917 my ($basketno) = @_;
2918 my $dbh = C4::Context->dbh;
2920 SELECT COUNT( DISTINCT( biblionumber ) )
2923 AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00')
2926 my $sth = $dbh->prepare($query);
2927 $sth->execute($basketno);
2928 return $sth->fetchrow;
2931 # Note this subroutine should be moved to Koha::Acquisition::Order
2932 # Will do when a DBIC decision will be taken.
2933 sub populate_order_with_prices {
2936 my $order = $params->{order};
2937 my $booksellerid = $params->{booksellerid};
2938 return unless $booksellerid;
2940 my $bookseller = Koha::Acquisition::Booksellers->find( $booksellerid );
2942 my $receiving = $params->{receiving};
2943 my $ordering = $params->{ordering};
2944 my $discount = $order->{discount};
2945 $discount /= 100 if $discount > 1;
2948 $order->{tax_rate_on_ordering} //= $order->{tax_rate};
2949 if ( $bookseller->listincgst ) {
2950 # The user entered the rrp tax included
2951 $order->{rrp_tax_included} = $order->{rrp};
2953 # rrp tax excluded = rrp tax included / ( 1 + tax rate )
2954 $order->{rrp_tax_excluded} = $order->{rrp_tax_included} / ( 1 + $order->{tax_rate_on_ordering} );
2956 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
2957 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
2959 # ecost tax included = rrp tax included ( 1 - discount )
2960 $order->{ecost_tax_included} = $order->{rrp_tax_included} * ( 1 - $discount );
2963 # The user entered the rrp tax excluded
2964 $order->{rrp_tax_excluded} = $order->{rrp};
2966 # rrp tax included = rrp tax excluded * ( 1 - tax rate )
2967 $order->{rrp_tax_included} = $order->{rrp_tax_excluded} * ( 1 + $order->{tax_rate_on_ordering} );
2969 # ecost tax excluded = rrp tax excluded * ( 1 - discount )
2970 $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount );
2972 # ecost tax included = rrp tax excluded * ( 1 + tax rate ) * ( 1 - discount )
2973 $order->{ecost_tax_included} =
2974 $order->{rrp_tax_excluded} *
2975 ( 1 + $order->{tax_rate_on_ordering} ) *
2979 # tax value = quantity * ecost tax excluded * tax rate
2980 $order->{tax_value_on_ordering} =
2981 $order->{quantity} * $order->{ecost_tax_excluded} * $order->{tax_rate_on_ordering};
2985 $order->{tax_rate_on_receiving} //= $order->{tax_rate};
2986 if ( $bookseller->invoiceincgst ) {
2987 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
2988 # we need to keep the exact ecost value
2989 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_included} )->round ) {
2990 $order->{unitprice} = $order->{ecost_tax_included};
2993 # The user entered the unit price tax included
2994 $order->{unitprice_tax_included} = $order->{unitprice};
2996 # unit price tax excluded = unit price tax included / ( 1 + tax rate )
2997 $order->{unitprice_tax_excluded} = $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate_on_receiving} );
3000 # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value
3001 # we need to keep the exact ecost value
3002 if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_excluded} )->round ) {
3003 $order->{unitprice} = $order->{ecost_tax_excluded};
3006 # The user entered the unit price tax excluded
3007 $order->{unitprice_tax_excluded} = $order->{unitprice};
3010 # unit price tax included = unit price tax included * ( 1 + tax rate )
3011 $order->{unitprice_tax_included} = $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate_on_receiving} );
3014 # tax value = quantity * unit price tax excluded * tax rate
3015 $order->{tax_value_on_receiving} = $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate_on_receiving};
3021 =head3 GetOrderUsers
3023 $order_users_ids = &GetOrderUsers($ordernumber);
3025 Returns a list of all borrowernumbers that are in order users list
3030 my ($ordernumber) = @_;
3032 return unless $ordernumber;
3035 SELECT borrowernumber
3037 WHERE ordernumber = ?
3039 my $dbh = C4::Context->dbh;
3040 my $sth = $dbh->prepare($query);
3041 $sth->execute($ordernumber);
3042 my $results = $sth->fetchall_arrayref( {} );
3044 my @borrowernumbers;
3045 foreach (@$results) {
3046 push @borrowernumbers, $_->{'borrowernumber'};
3049 return @borrowernumbers;
3052 =head3 ModOrderUsers
3054 my @order_users_ids = (1, 2, 3);
3055 &ModOrderUsers($ordernumber, @basketusers_ids);
3057 Delete all users from order users list, and add users in C<@order_users_ids>
3063 my ( $ordernumber, @order_users_ids ) = @_;
3065 return unless $ordernumber;
3067 my $dbh = C4::Context->dbh;
3069 DELETE FROM aqorder_users
3070 WHERE ordernumber = ?
3072 my $sth = $dbh->prepare($query);
3073 $sth->execute($ordernumber);
3076 INSERT INTO aqorder_users (ordernumber, borrowernumber)
3079 $sth = $dbh->prepare($query);
3080 foreach my $order_user_id (@order_users_ids) {
3081 $sth->execute( $ordernumber, $order_user_id );
3085 sub NotifyOrderUsers {
3086 my ($ordernumber) = @_;
3088 my @borrowernumbers = GetOrderUsers($ordernumber);
3089 return unless @borrowernumbers;
3091 my $order = GetOrder( $ordernumber );
3092 for my $borrowernumber (@borrowernumbers) {
3093 my $patron = Koha::Patrons->find( $borrowernumber );
3094 my $library = $patron->library->unblessed;
3095 my $biblio = Koha::Biblios->find( $order->{biblionumber} )->unblessed;
3096 my $letter = C4::Letters::GetPreparedLetter(
3097 module => 'acquisition',
3098 letter_code => 'ACQ_NOTIF_ON_RECEIV',
3099 branchcode => $library->{branchcode},
3100 lang => $patron->lang,
3102 'branches' => $library,
3103 'borrowers' => $patron->unblessed,
3104 'biblio' => $biblio,
3105 'aqorders' => $order,
3109 C4::Letters::EnqueueLetter(
3112 borrowernumber => $borrowernumber,
3113 LibraryName => C4::Context->preference("LibraryName"),
3114 message_transport_type => 'email',
3116 ) or warn "can't enqueue letter $letter";
3121 =head3 FillWithDefaultValues
3123 FillWithDefaultValues( $marc_record );
3125 This will update the record with default value defined in the ACQ framework.
3126 For all existing fields, if a default value exists and there are no subfield, it will be created.
3127 If the field does not exist, it will be created too.
3131 sub FillWithDefaultValues {
3133 my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ', { unsafe => 1 } );
3136 C4::Biblio::GetMarcFromKohaField( 'items.itemnumber', '' );
3137 for my $tag ( sort keys %$tagslib ) {
3139 next if $tag == $itemfield;
3140 for my $subfield ( sort keys %{ $tagslib->{$tag} } ) {
3141 next if IsMarcStructureInternal($tagslib->{$tag}{$subfield});
3142 my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue};
3143 if ( defined $defaultvalue and $defaultvalue ne '' ) {
3144 my @fields = $record->field($tag);
3146 for my $field (@fields) {
3147 unless ( defined $field->subfield($subfield) ) {
3148 $field->add_subfields(
3149 $subfield => $defaultvalue );
3154 $record->insert_fields_ordered(
3156 $tag, '', '', $subfield => $defaultvalue
3171 Koha Development Team <http://koha-community.org/>