X-Git-Url: http://git.rot13.org/?a=blobdiff_plain;f=C4%2FAcquisition.pm;h=b28b4b0e1034d9e31758c111c4e1f04af1f12ada;hb=ab4cf23286219b473820b61bca82c884de274e80;hp=10faff54f8fa52e03fd3844bfbe0962db6e8ffff;hpb=469f36d38fe3ea82013d40b95892dfb7605b4e5f;p=koha.git diff --git a/C4/Acquisition.pm b/C4/Acquisition.pm index 10faff54f8..b28b4b0e10 100644 --- a/C4/Acquisition.pm +++ b/C4/Acquisition.pm @@ -4,43 +4,51 @@ package C4::Acquisition; # # This file is part of Koha. # -# Koha is free software; you can redistribute it and/or modify it under the -# terms of the GNU General Public License as published by the Free Software -# Foundation; either version 2 of the License, or (at your option) any later -# version. +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. # -# Koha is distributed in the hope that it will be useful, but WITHOUT ANY -# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR -# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. # -# You should have received a copy of the GNU General Public License along -# with Koha; if not, write to the Free Software Foundation, Inc., -# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . use Modern::Perl; use Carp; use C4::Context; use C4::Debug; -use C4::Dates qw(format_date format_date_in_iso); -use MARC::Record; use C4::Suggestions; use C4::Biblio; use C4::Contract; use C4::Debug; use C4::Templates qw(gettemplate); use Koha::DateUtils qw( dt_from_string output_pref ); -use Koha::Acquisition::Order; -use Koha::Acquisition::Bookseller; +use Koha::Acquisition::Baskets; +use Koha::Acquisition::Booksellers; +use Koha::Acquisition::Orders; +use Koha::Biblios; +use Koha::Exceptions; +use Koha::Items; +use Koha::Number::Price; +use Koha::Libraries; +use Koha::CsvProfiles; +use Koha::Patrons; + +use C4::Koha; + +use MARC::Field; +use MARC::Record; use Time::localtime; -use HTML::Entities; -use vars qw($VERSION @ISA @EXPORT); +use vars qw(@ISA @EXPORT); BEGIN { - # set the version for version checking - $VERSION = 3.07.00.049; require Exporter; @ISA = qw(Exporter); @EXPORT = qw( @@ -61,11 +69,11 @@ BEGIN { &GetLateOrders &GetOrderFromItemnumber &SearchOrders &GetHistory &GetRecentAcqui &ModReceiveOrder &CancelReceipt - &GetCancelledOrders &TransferOrder + &TransferOrder &GetLastOrderNotReceivedFromSubscriptionid &GetLastOrderReceivedFromSubscriptionid &ModItemOrder - &GetParcels &GetParcel + &GetParcels &GetInvoices &GetInvoice @@ -77,10 +85,16 @@ BEGIN { &DelInvoice &MergeInvoices - &GetItemnumbersFromOrder - &AddClaim &GetBiblioCountByBasketno + + &GetOrderUsers + &ModOrderUsers + &NotifyOrderUsers + + &FillWithDefaultValues + + &get_rounded_price ); } @@ -108,28 +122,6 @@ sub GetOrderFromItemnumber { } -# Returns the itemnumber(s) associated with the ordernumber given in parameter -sub GetItemnumbersFromOrder { - my ($ordernumber) = @_; - my $dbh = C4::Context->dbh; - my $query = "SELECT itemnumber FROM aqorders_items WHERE ordernumber=?"; - my $sth = $dbh->prepare($query); - $sth->execute($ordernumber); - my @tab; - - while (my $order = $sth->fetchrow_hashref) { - push @tab, $order->{'itemnumber'}; - } - - return @tab; - -} - - - - - - =head1 NAME C4::Acquisition - Koha functions for dealing with orders and acquisitions @@ -177,8 +169,8 @@ sub GetBasket { =head3 NewBasket - $basket = &NewBasket( $booksellerid, $authorizedby, $basketname, - $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace ); + $basket = &NewBasket( $booksellerid, $authorizedby, $basketname, + $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, $billingplace, $is_standing, $create_items ); Create a new basket in aqbasket table @@ -197,7 +189,7 @@ The other parameters are optional, see ModBasketHeader for more info on them. sub NewBasket { my ( $booksellerid, $authorisedby, $basketname, $basketnote, $basketbooksellernote, $basketcontractnumber, $deliveryplace, - $billingplace ) = @_; + $billingplace, $is_standing, $create_items ) = @_; my $dbh = C4::Context->dbh; my $query = 'INSERT INTO aqbasket (creationdate,booksellerid,authorisedby) ' @@ -209,7 +201,7 @@ sub NewBasket { $basketnote ||= q{}; $basketbooksellernote ||= q{}; ModBasketHeader( $basket, $basketname, $basketnote, $basketbooksellernote, - $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace ); + $basketcontractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items ); return $basket; } @@ -226,24 +218,13 @@ close a basket (becomes unmodifiable, except for receives) sub CloseBasket { my ($basketno) = @_; my $dbh = C4::Context->dbh; - my $query = " - UPDATE aqbasket - SET closedate=now() - WHERE basketno=? - "; - my $sth = $dbh->prepare($query); - $sth->execute($basketno); + $dbh->do('UPDATE aqbasket SET closedate=now() WHERE basketno=?', {}, $basketno ); - my @orders = GetOrders($basketno); - foreach my $order (@orders) { - $query = qq{ - UPDATE aqorders - SET orderstatus = 'ordered' - WHERE ordernumber = ?; - }; - $sth = $dbh->prepare($query); - $sth->execute($order->{'ordernumber'}); - } + $dbh->do( +q{UPDATE aqorders SET orderstatus = 'ordered' WHERE basketno = ? AND orderstatus NOT IN ( 'complete', 'cancelled')}, + {}, $basketno + ); + return; } =head3 ReopenBasket @@ -257,24 +238,15 @@ reopen a basket sub ReopenBasket { my ($basketno) = @_; my $dbh = C4::Context->dbh; - my $query = " - UPDATE aqbasket - SET closedate=NULL - WHERE basketno=? - "; - my $sth = $dbh->prepare($query); - $sth->execute($basketno); + $dbh->do( q{UPDATE aqbasket SET closedate=NULL WHERE basketno=?}, {}, $basketno ); - my @orders = GetOrders($basketno); - foreach my $order (@orders) { - $query = qq{ - UPDATE aqorders - SET orderstatus = 'new' - WHERE ordernumber = ?; - }; - $sth = $dbh->prepare($query); - $sth->execute($order->{'ordernumber'}); - } + $dbh->do( q{ + UPDATE aqorders + SET orderstatus = 'new' + WHERE basketno = ? + AND orderstatus NOT IN ( 'complete', 'cancelled' ) + }, {}, $basketno); + return; } #------------------------------------------------------------# @@ -290,7 +262,7 @@ $cgi parameter is needed for column name translation =cut sub GetBasketAsCSV { - my ($basketno, $cgi) = @_; + my ($basketno, $cgi, $csv_profile_id) = @_; my $basket = GetBasket($basketno); my @orders = GetOrders($basketno); my $contract = GetContract({ @@ -298,60 +270,106 @@ sub GetBasketAsCSV { }); my $template = C4::Templates::gettemplate("acqui/csv/basket.tt", "intranet", $cgi); - my @rows; - foreach my $order (@orders) { - my $bd = GetBiblioData( $order->{'biblionumber'} ); - my $row = { - contractname => $contract->{'contractname'}, - ordernumber => $order->{'ordernumber'}, - entrydate => $order->{'entrydate'}, - isbn => $order->{'isbn'}, - author => $bd->{'author'}, - title => $bd->{'title'}, - publicationyear => $bd->{'publicationyear'}, - publishercode => $bd->{'publishercode'}, - collectiontitle => $bd->{'collectiontitle'}, - notes => $order->{'order_vendornote'}, - quantity => $order->{'quantity'}, - rrp => $order->{'rrp'}, - deliveryplace => C4::Branch::GetBranchName( $basket->{'deliveryplace'} ), - billingplace => C4::Branch::GetBranchName( $basket->{'billingplace'} ), - }; - foreach(qw( - contractname author title publishercode collectiontitle notes - deliveryplace billingplace - ) ) { - # Double the quotes to not be interpreted as a field end - $row->{$_} =~ s/"/""/g if $row->{$_}; + if ($csv_profile_id) { + my $csv_profile = Koha::CsvProfiles->find( $csv_profile_id ); + Koha::Exceptions::ObjectNotFound->throw( 'There is no valid csv profile given') unless $csv_profile; + + my $csv = Text::CSV_XS->new({'quote_char'=>'"','escape_char'=>'"','sep_char'=>$csv_profile->csv_separator,'binary'=>1}); + my $csv_profile_content = $csv_profile->content; + my ( @headers, @fields ); + while ( $csv_profile_content =~ / + ([^=\|]+) # header + =? + ([^\|]*) # fieldname (table.row or row) + \|? /gxms + ) { + my $header = $1; + my $field = ($2 eq '') ? $1 : $2; + + $header =~ s/^\s+|\s+$//g; # Trim whitespaces + push @headers, $header; + + $field =~ s/[^\.]*\.{1}//; # Remove the table name if exists. + $field =~ s/^\s+|\s+$//g; # Trim whitespaces + push @fields, $field; + } + for my $order (@orders) { + my @row; + my $biblio = Koha::Biblios->find( $order->{biblionumber} ); + my $biblioitem = $biblio->biblioitem; + $order = { %$order, %{ $biblioitem->unblessed } }; + if ($contract) { + $order = {%$order, %$contract}; + } + $order = {%$order, %$basket, %{ $biblio->unblessed }}; + for my $field (@fields) { + push @row, $order->{$field}; + } + push @rows, \@row; + } + my $content = join( $csv_profile->csv_separator, @headers ) . "\n"; + for my $row ( @rows ) { + $csv->combine(@$row); + my $string = $csv->string; + $content .= $string . "\n"; } - push @rows, $row; + return $content; } + else { + foreach my $order (@orders) { + my $biblio = Koha::Biblios->find( $order->{biblionumber} ); + my $biblioitem = $biblio->biblioitem; + my $row = { + contractname => $contract->{'contractname'}, + ordernumber => $order->{'ordernumber'}, + entrydate => $order->{'entrydate'}, + isbn => $order->{'isbn'}, + author => $biblio->author, + title => $biblio->title, + publicationyear => $biblioitem->publicationyear, + publishercode => $biblioitem->publishercode, + collectiontitle => $biblioitem->collectiontitle, + notes => $order->{'order_vendornote'}, + quantity => $order->{'quantity'}, + rrp => $order->{'rrp'}, + }; + for my $place ( qw( deliveryplace billingplace ) ) { + if ( my $library = Koha::Libraries->find( $row->{deliveryplace} ) ) { + $row->{$place} = $library->branchname + } + } + foreach(qw( + contractname author title publishercode collectiontitle notes + deliveryplace billingplace + ) ) { + # Double the quotes to not be interpreted as a field end + $row->{$_} =~ s/"/""/g if $row->{$_}; + } + push @rows, $row; + } - @rows = sort { - if(defined $a->{publishercode} and defined $b->{publishercode}) { - $a->{publishercode} cmp $b->{publishercode}; - } - } @rows; + @rows = sort { + if(defined $a->{publishercode} and defined $b->{publishercode}) { + $a->{publishercode} cmp $b->{publishercode}; + } + } @rows; - $template->param(rows => \@rows); + $template->param(rows => \@rows); - return $template->output; + return $template->output; + } } =head3 GetBasketGroupAsCSV -=over - -&GetBasketGroupAsCSV($basketgroupid); + &GetBasketGroupAsCSV($basketgroupid); Export a basket group as CSV $cgi parameter is needed for column name translation -=back - =cut sub GetBasketGroupAsCSV { @@ -366,37 +384,47 @@ sub GetBasketGroupAsCSV { my $contract = GetContract({ contractnumber => $basket->{contractnumber} }); - my $bookseller = Koha::Acquisition::Bookseller->fetch({ id => $basket->{booksellerid} }); + my $bookseller = Koha::Acquisition::Booksellers->find( $basket->{booksellerid} ); my $basketgroup = GetBasketgroup( $$basket{basketgroupid} ); foreach my $order (@orders) { - my $bd = GetBiblioData( $order->{'biblionumber'} ); + my $biblio = Koha::Biblios->find( $order->{biblionumber} ); + my $biblioitem = $biblio->biblioitem; my $row = { - clientnumber => $bookseller->{accountnumber}, + clientnumber => $bookseller->accountnumber, basketname => $basket->{basketname}, ordernumber => $order->{ordernumber}, - author => $bd->{author}, - title => $bd->{title}, - publishercode => $bd->{publishercode}, - publicationyear => $bd->{publicationyear}, - collectiontitle => $bd->{collectiontitle}, + author => $biblio->author, + title => $biblio->title, + publishercode => $biblioitem->publishercode, + publicationyear => $biblioitem->publicationyear, + collectiontitle => $biblioitem->collectiontitle, isbn => $order->{isbn}, quantity => $order->{quantity}, - rrp => $order->{rrp}, - discount => $bookseller->{discount}, - ecost => $order->{ecost}, + rrp_tax_included => $order->{rrp_tax_included}, + rrp_tax_excluded => $order->{rrp_tax_excluded}, + discount => $bookseller->discount, + ecost_tax_included => $order->{ecost_tax_included}, + ecost_tax_excluded => $order->{ecost_tax_excluded}, notes => $order->{order_vendornote}, entrydate => $order->{entrydate}, - booksellername => $bookseller->{name}, - bookselleraddress => $bookseller->{address1}, - booksellerpostal => $bookseller->{postal}, + booksellername => $bookseller->name, + bookselleraddress => $bookseller->address1, + booksellerpostal => $bookseller->postal, contractnumber => $contract->{contractnumber}, contractname => $contract->{contractname}, - basketgroupdeliveryplace => C4::Branch::GetBranchName( $basketgroup->{deliveryplace} ), - basketgroupbillingplace => C4::Branch::GetBranchName( $basketgroup->{billingplace} ), - basketdeliveryplace => C4::Branch::GetBranchName( $basket->{deliveryplace} ), - basketbillingplace => C4::Branch::GetBranchName( $basket->{billingplace} ), }; + my $temp = { + basketgroupdeliveryplace => $basketgroup->{deliveryplace}, + basketgroupbillingplace => $basketgroup->{billingplace}, + basketdeliveryplace => $basket->{deliveryplace}, + basketbillingplace => $basket->{billingplace}, + }; + for my $place (qw( basketgroupdeliveryplace basketgroupbillingplace basketdeliveryplace basketbillingplace )) { + if ( my $library = Koha::Libraries->find( $temp->{$place} ) ) { + $row->{$place} = $library->branchname; + } + } foreach(qw( basketname author title publishercode collectiontitle notes booksellername bookselleraddress booksellerpostal contractname @@ -548,21 +576,28 @@ Modifies a basket's header. =item C<$billingplace> is the "billingplace" field in the aqbasket table. +=item C<$is_standing> is the "is_standing" field in the aqbasket table. + +=item C<$create_items> should be set to 'ordering', 'receiving' or 'cataloguing' (or undef, in which +case the AcqCreateItem syspref takes precedence). + =back =cut sub ModBasketHeader { - my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace) = @_; + my ($basketno, $basketname, $note, $booksellernote, $contractnumber, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items) = @_; + + $is_standing ||= 0; my $query = qq{ UPDATE aqbasket - SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=? + SET basketname=?, note=?, booksellernote=?, booksellerid=?, deliveryplace=?, billingplace=?, is_standing=?, create_items=? WHERE basketno=? }; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare($query); - $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $basketno); + $sth->execute($basketname, $note, $booksellernote, $booksellerid, $deliveryplace, $billingplace, $is_standing, $create_items || undef, $basketno); if ( $contractnumber ) { my $query2 ="UPDATE aqbasket SET contractnumber=? WHERE basketno=?"; @@ -637,7 +672,7 @@ sub GetBasketsInfosByBookseller { my $dbh = C4::Context->dbh; my $query = q{ - SELECT aqbasket.*, + SELECT aqbasket.basketno, aqbasket.basketname, aqbasket.note, aqbasket.booksellernote, aqbasket.contractnumber, aqbasket.creationdate, aqbasket.closedate, aqbasket.booksellerid, aqbasket.authorisedby, aqbasket.booksellerinvoicenumber, aqbasket.basketgroupid, aqbasket.deliveryplace, aqbasket.billingplace, aqbasket.branch, aqbasket.is_standing, aqbasket.create_items, SUM(aqorders.quantity) AS total_items, SUM( IF ( aqorders.orderstatus = 'cancelled', aqorders.quantity, 0 ) @@ -645,7 +680,7 @@ sub GetBasketsInfosByBookseller { COUNT(DISTINCT aqorders.biblionumber) AS total_biblios, SUM( IF(aqorders.datereceived IS NULL - AND aqorders.datecancellationprinted IS NULL + AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00') , aqorders.quantity , 0) ) AS expected_items @@ -653,10 +688,18 @@ sub GetBasketsInfosByBookseller { LEFT JOIN aqorders ON aqorders.basketno = aqbasket.basketno WHERE booksellerid = ?}; + $query.=" GROUP BY aqbasket.basketno, aqbasket.basketname, aqbasket.note, aqbasket.booksellernote, aqbasket.contractnumber, aqbasket.creationdate, aqbasket.closedate, aqbasket.booksellerid, aqbasket.authorisedby, aqbasket.booksellerinvoicenumber, aqbasket.basketgroupid, aqbasket.deliveryplace, aqbasket.billingplace, aqbasket.branch, aqbasket.is_standing, aqbasket.create_items"; + unless ( $allbaskets ) { - $query.=" AND (closedate IS NULL OR (aqorders.quantity > aqorders.quantityreceived AND datecancellationprinted IS NULL))"; + # Don't show the basket if it's NOT CLOSED or is FULLY RECEIVED + $query.=" HAVING (closedate IS NULL OR ( + SUM( + IF(aqorders.datereceived IS NULL + AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00') + , aqorders.quantity + , 0) + ) > 0))" } - $query.=" GROUP BY aqbasket.basketno"; my $sth = $dbh->prepare($query); $sth->execute($supplierid); @@ -753,7 +796,7 @@ AcqViewBaskets, user permissions and basket properties (creator, users list, branch). First parameter can be either a borrowernumber or a hashref as returned by -C4::Members::GetMember. +Koha::Patron->unblessed Second parameter can be either a basketno or a hashref as returned by C4::Acquisition::GetBasket. @@ -770,7 +813,10 @@ sub CanUserManageBasket { my ($borrower, $basket, $userflags) = @_; if (!ref $borrower) { - $borrower = C4::Members::GetMember(borrowernumber => $borrower); + # FIXME This needs to be replaced + # We should not accept both scalar and array + # Tests need to be updated + $borrower = Koha::Patrons->find( $borrower )->unblessed; } if (!ref $basket) { $basket = GetBasket($basket); @@ -808,8 +854,8 @@ sub CanUserManageBasket { if ($AcqViewBaskets eq 'user' && $basket->{authorisedby} != $borrowernumber - && grep($borrowernumber, GetBasketUsers($basketno)) == 0) { - return 0; + && ! grep { $borrowernumber eq $_ } GetBasketUsers($basketno)) { + return 0; } if ($AcqViewBaskets eq 'branch' && defined $basket->{branch} @@ -1035,47 +1081,80 @@ sub GetBasketgroups { =head3 GetOrders - @orders = &GetOrders($basketnumber, $orderby); + @orders = &GetOrders( $basketno, { orderby => 'biblio.title', cancelled => 0|1 } ); Looks up the pending (non-cancelled) orders with the given basket -number. If C<$booksellerID> is non-empty, only orders from that seller -are returned. +number. -return : -C<&basket> returns a two-element array. C<@orders> is an array of -references-to-hash, whose keys are the fields from the aqorders, -biblio, and biblioitems tables in the Koha database. +If cancelled is set, only cancelled orders will be returned. =cut sub GetOrders { - my ( $basketno, $orderby ) = @_; + my ( $basketno, $params ) = @_; + return () unless $basketno; + + my $orderby = $params->{orderby}; + my $cancelled = $params->{cancelled} || 0; + my $dbh = C4::Context->dbh; - my $query =" + my $query = q| SELECT biblio.*,biblioitems.*, aqorders.*, aqbudgets.*, + |; + $query .= $cancelled + ? q| + aqorders_transfers.ordernumber_to AS transferred_to, + aqorders_transfers.timestamp AS transferred_to_timestamp + | + : q| aqorders_transfers.ordernumber_from AS transferred_from, aqorders_transfers.timestamp AS transferred_from_timestamp + |; + $query .= q| FROM aqorders LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber LEFT JOIN biblioitems ON biblioitems.biblionumber =biblio.biblionumber + |; + $query .= $cancelled + ? q| + LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber + | + : q| LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber + + |; + $query .= q| WHERE basketno=? + |; + + if ($cancelled) { + $orderby ||= q|biblioitems.publishercode, biblio.title|; + $query .= q| + AND (datecancellationprinted IS NOT NULL + AND datecancellationprinted <> '0000-00-00') + |; + } + else { + $orderby ||= + q|aqorders.datecancellationprinted desc, aqorders.timestamp desc|; + $query .= q| AND (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') - "; + |; + } - $orderby = "biblioitems.publishercode,biblio.title" unless $orderby; $query .= " ORDER BY $orderby"; - my $result_set = + my $orders = $dbh->selectall_arrayref( $query, { Slice => {} }, $basketno ); - return @{$result_set}; + return @{$orders}; } #------------------------------------------------------------# + =head3 GetOrdersByBiblionumber @orders = &GetOrdersByBiblionumber($biblionumber); @@ -1245,8 +1324,7 @@ table of the Koha database. sub ModOrder { my $orderinfo = shift; - die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq '' ; - die "Biblionumber is required" if $orderinfo->{'biblionumber'} eq ''; + die "Ordernumber is required" if $orderinfo->{'ordernumber'} eq ''; my $dbh = C4::Context->dbh; my @params; @@ -1304,101 +1382,46 @@ sub ModItemOrder { return $sth->execute($ordernumber, $itemnumber); } -#------------------------------------------------------------# - -=head3 GetCancelledOrders - - my @orders = GetCancelledOrders($basketno, $orderby); - -Returns cancelled orders for a basket - -=cut - -sub GetCancelledOrders { - my ( $basketno, $orderby ) = @_; - - return () unless $basketno; - - my $dbh = C4::Context->dbh; - my $query = " - SELECT - biblio.*, - biblioitems.*, - aqorders.*, - aqbudgets.*, - aqorders_transfers.ordernumber_to AS transferred_to, - aqorders_transfers.timestamp AS transferred_to_timestamp - FROM aqorders - LEFT JOIN aqbudgets ON aqbudgets.budget_id = aqorders.budget_id - LEFT JOIN biblio ON biblio.biblionumber = aqorders.biblionumber - LEFT JOIN biblioitems ON biblioitems.biblionumber = biblio.biblionumber - LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_from = aqorders.ordernumber - WHERE basketno = ? - AND (datecancellationprinted IS NOT NULL - AND datecancellationprinted <> '0000-00-00') - "; - - $orderby = "aqorders.datecancellationprinted desc, aqorders.timestamp desc" - unless $orderby; - $query .= " ORDER BY $orderby"; - my $sth = $dbh->prepare($query); - $sth->execute($basketno); - my $results = $sth->fetchall_arrayref( {} ); - - return @$results; -} - - #------------------------------------------------------------# =head3 ModReceiveOrder - &ModReceiveOrder({ - biblionumber => $biblionumber, - ordernumber => $ordernumber, - quantityreceived => $quantityreceived, - user => $user, - cost => $cost, - ecost => $ecost, - invoiceid => $invoiceid, - rrp => $rrp, - budget_id => $budget_id, - datereceived => $datereceived, - received_itemnumbers => \@received_itemnumbers, - order_internalnote => $order_internalnote, - order_vendornote => $order_vendornote, - }); + my ( $date_received, $new_ordernumber ) = ModReceiveOrder( + { + biblionumber => $biblionumber, + order => $order, + quantityreceived => $quantityreceived, + user => $user, + invoice => $invoice, + budget_id => $budget_id, + received_itemnumbers => \@received_itemnumbers, + order_internalnote => $order_internalnote, + } + ); Updates an order, to reflect the fact that it was received, at least -in part. All arguments not mentioned below update the fields with the -same name in the aqorders table of the Koha database. +in part. If a partial order is received, splits the order into two. -Updates the order with bibilionumber C<$biblionumber> and ordernumber -C<$ordernumber>. +Updates the order with biblionumber C<$biblionumber> and ordernumber +C<$order->{ordernumber}>. =cut sub ModReceiveOrder { - my ( $params ) = @_; - my $biblionumber = $params->{biblionumber}; - my $ordernumber = $params->{ordernumber}; - my $quantrec = $params->{quantityreceived}; - my $user = $params->{user}; - my $cost = $params->{cost}; - my $ecost = $params->{ecost}; - my $invoiceid = $params->{invoiceid}; - my $rrp = $params->{rrp}; - my $budget_id = $params->{budget_id}; - my $datereceived = $params->{datereceived}; + my ($params) = @_; + my $biblionumber = $params->{biblionumber}; + my $order = { %{ $params->{order} } }; # Copy the order, we don't want to modify it + my $invoice = $params->{invoice}; + my $quantrec = $params->{quantityreceived}; + my $user = $params->{user}; + my $budget_id = $params->{budget_id}; my $received_items = $params->{received_items}; - my $order_internalnote = $params->{order_internalnote}; - my $order_vendornote = $params->{order_vendornote}; my $dbh = C4::Context->dbh; - $datereceived = C4::Dates->output('iso') unless $datereceived; + my $datereceived = ( $invoice and $invoice->{datereceived} ) ? $invoice->{datereceived} : dt_from_string; my $suggestionid = GetSuggestionFromBiblionumber( $biblionumber ); if ($suggestionid) { ModSuggestion( {suggestionid=>$suggestionid, @@ -1407,16 +1430,14 @@ sub ModReceiveOrder { ); } - my $result_set = $dbh->selectall_arrayref( -q{SELECT * FROM aqorders WHERE biblionumber=? AND aqorders.ordernumber=?}, - { Slice => {} }, $biblionumber, $ordernumber - ); - - # we assume we have a unique order - my $order = $result_set->[0]; + my $result_set = $dbh->selectrow_arrayref( + q{SELECT aqbasket.is_standing + FROM aqbasket + WHERE basketno=?},{ Slice => {} }, $order->{basketno}); + my $is_standing = $result_set->[0]; # we assume we have a unique basket - my $new_ordernumber = $ordernumber; - if ( $order->{quantity} > $quantrec ) { + my $new_ordernumber = $order->{ordernumber}; + if ( $is_standing || $order->{quantity} > $quantrec ) { # Split order line in two parts: the first is the original order line # without received items (the quantity is decreased), # the second part is a new order line with quantity=quantityrec @@ -1425,29 +1446,42 @@ q{SELECT * FROM aqorders WHERE biblionumber=? AND aqorders.ordernumber=?}, UPDATE aqorders SET quantity = ?, orderstatus = 'partial'|; - $query .= q|, order_internalnote = ?| if defined $order_internalnote; - $query .= q|, order_vendornote = ?| if defined $order_vendornote; $query .= q| WHERE ordernumber = ?|; my $sth = $dbh->prepare($query); $sth->execute( - $order->{quantity} - $quantrec, - ( defined $order_internalnote ? $order_internalnote : () ), - ( defined $order_vendornote ? $order_vendornote : () ), - $ordernumber + ( $is_standing ? 1 : ($order->{quantity} - $quantrec) ), + $order->{ordernumber} ); - delete $order->{'ordernumber'}; - $order->{'budget_id'} = ( $budget_id || $order->{'budget_id'} ); - $order->{'quantity'} = $quantrec; - $order->{'quantityreceived'} = $quantrec; - $order->{'datereceived'} = $datereceived; - $order->{'invoiceid'} = $invoiceid; - $order->{'unitprice'} = $cost; - $order->{'rrp'} = $rrp; - $order->{ecost} = $ecost; - $order->{'orderstatus'} = 'complete'; - $new_ordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber}; + if ( not $order->{subscriptionid} && defined $order->{order_internalnote} ) { + $dbh->do(q|UPDATE aqorders + SET order_internalnote = ?|, {}, $order->{order_internalnote}); + } + + # Recalculate tax_value + $dbh->do(q| + UPDATE aqorders + SET + tax_value_on_ordering = quantity * | . _get_rounding_sql(q|ecost_tax_excluded|) . q| * tax_rate_on_ordering, + tax_value_on_receiving = quantity * | . _get_rounding_sql(q|unitprice_tax_excluded|) . q| * tax_rate_on_receiving + WHERE ordernumber = ? + |, undef, $order->{ordernumber}); + + delete $order->{ordernumber}; + $order->{budget_id} = ( $budget_id || $order->{budget_id} ); + $order->{quantity} = $quantrec; + $order->{quantityreceived} = $quantrec; + $order->{ecost_tax_excluded} //= 0; + $order->{tax_rate_on_ordering} //= 0; + $order->{unitprice_tax_excluded} //= 0; + $order->{tax_rate_on_receiving} //= 0; + $order->{tax_value_on_ordering} = $order->{quantity} * get_rounded_price($order->{ecost_tax_excluded}) * $order->{tax_rate_on_ordering}; + $order->{tax_value_on_receiving} = $order->{quantity} * get_rounded_price($order->{unitprice_tax_excluded}) * $order->{tax_rate_on_receiving}; + $order->{datereceived} = $datereceived; + $order->{invoiceid} = $invoice->{invoiceid}; + $order->{orderstatus} = 'complete'; + $new_ordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber; # TODO What if the store fails? if ($received_items) { foreach my $itemnumber (@$received_items) { @@ -1456,26 +1490,66 @@ q{SELECT * FROM aqorders WHERE biblionumber=? AND aqorders.ordernumber=?}, } } else { my $query = q| - update aqorders - set quantityreceived=?,datereceived=?,invoiceid=?, - unitprice=?,rrp=?,ecost=?,budget_id=?,orderstatus='complete'|; - $query .= q|, order_internalnote = ?| if defined $order_internalnote; - $query .= q|, order_vendornote = ?| if defined $order_vendornote; + UPDATE aqorders + SET quantityreceived = ?, + datereceived = ?, + invoiceid = ?, + budget_id = ?, + orderstatus = 'complete' + |; + + $query .= q| + , replacementprice = ? + | if defined $order->{replacementprice}; + + $query .= q| + , unitprice = ?, unitprice_tax_included = ?, unitprice_tax_excluded = ? + | if defined $order->{unitprice}; + + $query .= q| + ,tax_value_on_receiving = ? + | if defined $order->{tax_value_on_receiving}; + + $query .= q| + ,tax_rate_on_receiving = ? + | if defined $order->{tax_rate_on_receiving}; + + $query .= q| + , order_internalnote = ? + | if defined $order->{order_internalnote}; + $query .= q| where biblionumber=? and ordernumber=?|; + my $sth = $dbh->prepare( $query ); - $sth->execute( - $quantrec, - $datereceived, - $invoiceid, - $cost, - $rrp, - $ecost, - $budget_id, - ( defined $order_internalnote ? $order_internalnote : () ), - ( defined $order_vendornote ? $order_vendornote : () ), - $biblionumber, - $ordernumber - ); + my @params = ( $quantrec, $datereceived, $invoice->{invoiceid}, ( $budget_id ? $budget_id : $order->{budget_id} ) ); + + if ( defined $order->{replacementprice} ) { + push @params, $order->{replacementprice}; + } + + if ( defined $order->{unitprice} ) { + push @params, $order->{unitprice}, $order->{unitprice_tax_included}, $order->{unitprice_tax_excluded}; + } + + if ( defined $order->{tax_value_on_receiving} ) { + push @params, $order->{tax_value_on_receiving}; + } + + if ( defined $order->{tax_rate_on_receiving} ) { + push @params, $order->{tax_rate_on_receiving}; + } + + if ( defined $order->{order_internalnote} ) { + push @params, $order->{order_internalnote}; + } + + push @params, ( $biblionumber, $order->{ordernumber} ); + + $sth->execute( @params ); + + # All items have been received, sent a notification to users + NotifyOrderUsers( $order->{ordernumber} ); + } return ($datereceived, $new_ordernumber); } @@ -1516,7 +1590,8 @@ sub CancelReceipt { my $parent_ordernumber = $order->{'parent_ordernumber'}; - my @itemnumbers = GetItemnumbersFromOrder( $ordernumber ); + my $order_obj = Koha::Acquisition::Orders->find( $ordernumber ); # FIXME rewrite all this subroutine using this object + my @itemnumbers = $order_obj->items->get_column('itemnumber'); if($parent_ordernumber == $ordernumber || not $parent_ordernumber) { # The order line has no parent, just mark it as not received @@ -1530,7 +1605,7 @@ sub CancelReceipt { }; $sth = $dbh->prepare($query); $sth->execute(0, undef, undef, $ordernumber); - _cancel_items_receipt( $ordernumber ); + _cancel_items_receipt( $order_obj ); } else { # The order line has a parent, increase parent quantity and delete # the order line. @@ -1567,7 +1642,17 @@ sub CancelReceipt { " receipt"; return; } - _cancel_items_receipt( $ordernumber, $parent_ordernumber ); + + # Recalculate tax_value + $dbh->do(q| + UPDATE aqorders + SET + tax_value_on_ordering = quantity * | . _get_rounding_sql(q|ecost_tax_excluded|) . q| * tax_rate_on_ordering, + tax_value_on_receiving = quantity * | . _get_rounding_sql(q|unitprice_tax_excluded|) . q| * tax_rate_on_receiving + WHERE ordernumber = ? + |, undef, $parent_ordernumber); + + _cancel_items_receipt( $order_obj, $parent_ordernumber ); # Delete order line $query = qq{ DELETE FROM aqorders @@ -1578,21 +1663,21 @@ sub CancelReceipt { } - if(C4::Context->preference('AcqCreateItem') eq 'ordering') { + if( $order_obj->basket->effective_create_items eq 'ordering' ) { my @affects = split q{\|}, C4::Context->preference("AcqItemSetSubfieldsWhenReceiptIsCancelled"); if ( @affects ) { for my $in ( @itemnumbers ) { - my $biblionumber = C4::Biblio::GetBiblionumberFromItemnumber( $in ); - my $frameworkcode = GetFrameworkCode($biblionumber); - my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber', $frameworkcode ); - my $item = C4::Items::GetMarcItem( $biblionumber, $in ); + my $item = Koha::Items->find( $in ); # FIXME We do not need that, we already have Koha::Items from $order_obj->items + my $biblio = $item->biblio; + my ( $itemfield ) = GetMarcFromKohaField( 'items.itemnumber', $biblio->frameworkcode ); + my $item_marc = C4::Items::GetMarcItem( $biblio->biblionumber, $in ); for my $affect ( @affects ) { my ( $sf, $v ) = split q{=}, $affect, 2; - foreach ( $item->field($itemfield) ) { + foreach ( $item_marc->field($itemfield) ) { $_->update( $sf => $v ); } } - C4::Items::ModItemFromMarc( $item, $biblionumber, $in ); + C4::Items::ModItemFromMarc( $item_marc, $biblio->biblionumber, $in ); } } } @@ -1601,11 +1686,11 @@ sub CancelReceipt { } sub _cancel_items_receipt { - my ( $ordernumber, $parent_ordernumber ) = @_; - $parent_ordernumber ||= $ordernumber; + my ( $order, $parent_ordernumber ) = @_; + $parent_ordernumber ||= $order->ordernumber; - my @itemnumbers = GetItemnumbersFromOrder($ordernumber); - if(C4::Context->preference('AcqCreateItem') eq 'receiving') { + my $items = $order->items; + if ( $order->basket->effective_create_items eq 'receiving' ) { # Remove items that were created at receipt my $query = qq{ DELETE FROM items, aqorders_items @@ -1614,13 +1699,13 @@ sub _cancel_items_receipt { }; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare($query); - foreach my $itemnumber (@itemnumbers) { - $sth->execute($itemnumber, $itemnumber); + while ( my $item = $items->next ) { + $sth->execute($item->itemnumber, $item->itemnumber); } } else { # Update items - foreach my $itemnumber (@itemnumbers) { - ModItemOrder($itemnumber, $parent_ordernumber); + while ( my $item = $items->next ) { + ModItemOrder($item->itemnumber, $parent_ordernumber); } } } @@ -1632,17 +1717,22 @@ sub _cancel_items_receipt { @results = &SearchOrders({ ordernumber => $ordernumber, search => $search, - biblionumber => $biblionumber, ean => $ean, booksellerid => $booksellerid, basketno => $basketno, + basketname => $basketname, + basketgroupname => $basketgroupname, owner => $owner, pending => $pending ordered => $ordered + biblionumber => $biblionumber, + budget_id => $budget_id }); -Searches for orders. +Searches for orders filtered by criteria. +C<$ordernumber> Finds matching orders or transferred orders by ordernumber. +C<$search> Finds orders matching %$search% in title, author, or isbn. C<$owner> Finds order for the logged in user. C<$pending> Finds pending orders. Ignores completed and cancelled orders. C<$ordered> Finds orders to receive only (status 'ordered' or 'partial'). @@ -1677,6 +1767,8 @@ sub SearchOrders { biblio.*, biblioitems.isbn, biblioitems.biblioitemnumber, + biblioitems.publishercode, + biblioitems.publicationyear, aqbasket.authorisedby, aqbasket.booksellerid, aqbasket.closedate, @@ -1693,7 +1785,7 @@ sub SearchOrders { LEFT JOIN biblioitems ON biblioitems.biblionumber=biblio.biblionumber }; - # If we search on ordernumber, we retrieve the transfered order if a transfer has been done. + # If we search on ordernumber, we retrieve the transferred order if a transfer has been done. $query .= q{ LEFT JOIN aqorders_transfers ON aqorders_transfers.ordernumber_to = aqorders.ordernumber } if $ordernumber; @@ -1703,10 +1795,20 @@ sub SearchOrders { }; if ( $pending or $ordered ) { - $query .= q{ AND (quantity > quantityreceived OR quantityreceived is NULL)}; - } - if ( $ordered ) { - $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )}; + $query .= q{ + AND ( + ( aqbasket.is_standing AND aqorders.orderstatus IN ( "new", "ordered", "partial" ) ) + OR ( + ( quantity > quantityreceived OR quantityreceived is NULL ) + }; + + if ( $ordered ) { + $query .= q{ AND aqorders.orderstatus IN ( "ordered", "partial" )}; + } + $query .= q{ + ) + ) + }; } my $userenv = C4::Context->userenv; @@ -1807,9 +1909,10 @@ sub DelOrder { } $sth->finish; - my @itemnumbers = GetItemnumbersFromOrder( $ordernumber ); - foreach my $itemnumber (@itemnumbers){ - my $delcheck = C4::Items::DelItemCheck( $dbh, $bibnum, $itemnumber ); + my $order = Koha::Acquisition::Orders->find($ordernumber); + my $items = $order->items; + while ( my $item = $items->next ) { # Should be moved to Koha::Acquisition::Order->delete + my $delcheck = C4::Items::DelItemCheck( $bibnum, $item->itemnumber ); if($delcheck != 1) { $error->{'delitem'} = 1; @@ -1818,7 +1921,8 @@ sub DelOrder { if($delete_biblio) { # We get the number of remaining items - my $itemcount = C4::Items::GetItemsCount($bibnum); + my $biblio = Koha::Biblios->find( $bibnum ); + my $itemcount = $biblio->items->count; # If there are no items left, if ( $itemcount == 0 ) { @@ -1839,11 +1943,11 @@ sub DelOrder { my $newordernumber = TransferOrder($ordernumber, $basketno); Transfer an order line to a basket. -Mark $ordernumber as cancelled with an internal note 'Cancelled and transfered +Mark $ordernumber as cancelled with an internal note 'Cancelled and transferred to BOOKSELLER on DATE' and create new order with internal note -'Transfered from BOOKSELLER on DATE'. +'Transferred from BOOKSELLER on DATE'. Move all attached items to the new order. -Received orders cannot be transfered. +Received orders cannot be transferred. Return the ordernumber of created order. =cut @@ -1853,8 +1957,11 @@ sub TransferOrder { return unless ($ordernumber and $basketno); - my $order = GetOrder( $ordernumber ); - return if $order->{datereceived}; + my $order = Koha::Acquisition::Orders->find( $ordernumber ) or return; + return if $order->datereceived; + + $order = $order->unblessed; + my $basket = GetBasket($basketno); return unless $basket; @@ -1863,17 +1970,17 @@ sub TransferOrder { $query = q{ UPDATE aqorders - SET datecancellationprinted = CAST(NOW() AS date) + SET datecancellationprinted = CAST(NOW() AS date), orderstatus = ? WHERE ordernumber = ? }; $sth = $dbh->prepare($query); - $rv = $sth->execute($ordernumber); + $rv = $sth->execute('cancelled', $ordernumber); delete $order->{'ordernumber'}; delete $order->{parent_ordernumber}; $order->{'basketno'} = $basketno; - my $newordernumber = Koha::Acquisition::Order->new($order)->insert->{ordernumber}; + my $newordernumber = Koha::Acquisition::Order->new($order)->store->ordernumber; $query = q{ UPDATE aqorders_items @@ -1893,78 +2000,38 @@ sub TransferOrder { return $newordernumber; } -=head2 FUNCTIONS ABOUT PARCELS +=head3 _get_rounding_sql -=cut + $rounding_sql = _get_rounding_sql("mysql_variable_to_round_string"); -#------------------------------------------------------------# +returns the correct SQL routine based on OrderPriceRounding system preference. -=head3 GetParcel +=cut - @results = &GetParcel($booksellerid, $code, $date); +sub _get_rounding_sql { + my ( $round_string ) = @_; + my $rounding_pref = C4::Context->preference('OrderPriceRounding'); + if ( $rounding_pref eq "nearest_cent" ) { return ("CAST($round_string*100 AS UNSIGNED)/100"); } + else { return ("$round_string"); } +} -Looks up all of the received items from the supplier with the given -bookseller ID at the given date, for the given code (bookseller Invoice number). Ignores cancelled and completed orders. +=head3 get_rounded_price -C<@results> is an array of references-to-hash. The keys of each element are fields from -the aqorders, biblio, and biblioitems tables of the Koha database. + $rounded_price = get_rounded_price( $price ); -C<@results> is sorted alphabetically by book title. +returns a price rounded as specified in OrderPriceRounding system preference. =cut -sub GetParcel { - #gets all orders from a certain supplier, orders them alphabetically - my ( $supplierid, $code, $datereceived ) = @_; - my $dbh = C4::Context->dbh; - my @results = (); - $code .= '%' - if $code; # add % if we search on a given code (otherwise, let him empty) - my $strsth =" - SELECT authorisedby, - creationdate, - aqbasket.basketno, - closedate,surname, - firstname, - aqorders.biblionumber, - aqorders.ordernumber, - aqorders.parent_ordernumber, - aqorders.quantity, - aqorders.quantityreceived, - aqorders.unitprice, - aqorders.listprice, - aqorders.rrp, - aqorders.ecost, - aqorders.gstrate, - biblio.title - FROM aqorders - LEFT JOIN aqbasket ON aqbasket.basketno=aqorders.basketno - LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber - LEFT JOIN biblio ON aqorders.biblionumber=biblio.biblionumber - LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid - WHERE - aqbasket.booksellerid = ? - AND aqinvoices.invoicenumber LIKE ? - AND aqorders.datereceived = ? "; - - my @query_params = ( $supplierid, $code, $datereceived ); - if ( C4::Context->preference("IndependentBranches") ) { - unless ( C4::Context->IsSuperLibrarian() ) { - $strsth .= " and (borrowers.branchcode = ? - or borrowers.branchcode = '')"; - push @query_params, C4::Context->userenv->{branch}; - } - } - $strsth .= " ORDER BY aqbasket.basketno"; - my $result_set = $dbh->selectall_arrayref( - $strsth, - { Slice => {} }, - @query_params); - - return @{$result_set}; +sub get_rounded_price { + my ( $price ) = @_; + my $rounding_pref = C4::Context->preference('OrderPriceRounding'); + if( $rounding_pref eq 'nearest_cent' ) { return Koha::Number::Price->new( $price )->round(); } + else { return $price; } } -#------------------------------------------------------------# + +=head2 FUNCTIONS ABOUT PARCELS =head3 GetParcels @@ -2114,7 +2181,6 @@ sub GetLateOrders { AND aqbasket.closedate IS NOT NULL AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00') "; - my $having = ""; if ($dbdriver eq "mysql") { $select .= " aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity, @@ -2125,11 +2191,7 @@ sub GetLateOrders { $from .= " AND (closedate <= DATE_SUB(CAST(now() AS date),INTERVAL ? DAY)) " ; push @query_params, $delay; } - $having = " - HAVING quantity <> 0 - AND unitpricesupplier <> 0 - AND unitpricelib <> 0 - "; + $from .= " AND aqorders.quantity - COALESCE(aqorders.quantityreceived,0) <> 0"; } else { # FIXME: account for IFNULL as above $select .= " @@ -2141,6 +2203,7 @@ sub GetLateOrders { $from .= " AND (closedate <= (CAST(now() AS date) -(INTERVAL ? DAY)) "; push @query_params, $delay; } + $from .= " AND aqorders.quantity <> 0"; } if (defined $supplierid) { $from .= ' AND aqbasket.booksellerid = ? '; @@ -2171,7 +2234,7 @@ sub GetLateOrders { push @query_params, C4::Context->userenv->{branch}; } $from .= " AND orderstatus <> 'cancelled' "; - my $query = "$select $from $having\nORDER BY latesince, basketno, borrowers.branchcode, supplier"; + my $query = "$select $from \nORDER BY latesince, basketno, borrowers.branchcode, supplier"; $debug and print STDERR "GetLateOrders query: $query\nGetLateOrders args: " . join(" ",@query_params); my $sth = $dbh->prepare($query); $sth->execute(@query_params); @@ -2249,12 +2312,31 @@ sub GetHistory { my $get_canceled_order = $params{get_canceled_order} || 0; my $ordernumber = $params{ordernumber}; my $search_children_too = $params{search_children_too} || 0; + my $created_by = $params{created_by} || []; + my $ordernumbers = $params{ordernumbers} || []; + my $additional_fields = $params{additional_fields} // []; my @order_loop; my $total_qty = 0; my $total_qtyreceived = 0; my $total_price = 0; + #get variation of isbn + my @isbn_params; + my @isbns; + if ($isbn){ + if ( C4::Context->preference("SearchWithISBNVariations") ){ + @isbns = C4::Koha::GetVariationsOfISBN( $isbn ); + foreach my $isb (@isbns){ + push @isbn_params, '?'; + } + } + unless (@isbns){ + push @isbns, $isbn; + push @isbn_params, '?'; + } + } + my $dbh = C4::Context->dbh; my $query =" SELECT @@ -2265,6 +2347,8 @@ sub GetHistory { aqorders.basketno, aqbasket.basketname, aqbasket.basketgroupid, + aqbasket.authorisedby, + concat( borrowers.firstname,' ',borrowers.surname) AS authorisedbyname, aqbasketgroups.name as groupname, aqbooksellers.name, aqbasket.creationdate, @@ -2293,12 +2377,9 @@ sub GetHistory { LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid LEFT JOIN deletedbiblio ON deletedbiblio.biblionumber=aqorders.biblionumber LEFT JOIN deletedbiblioitems ON deletedbiblioitems.biblionumber=aqorders.biblionumber + LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber "; - if ( C4::Context->preference("IndependentBranches") ) { - $query .= " LEFT JOIN borrowers ON aqbasket.authorisedby=borrowers.borrowernumber"; - } - $query .= " WHERE 1 "; unless ($get_canceled_order or (defined $orderstatus and $orderstatus eq 'cancelled')) { @@ -2323,10 +2404,13 @@ sub GetHistory { push @query_params, "%$author%"; } - if ( $isbn ) { - $query .= " AND biblioitems.isbn LIKE ? "; - push @query_params, "%$isbn%"; + if ( @isbns ) { + $query .= " AND ( biblioitems.isbn LIKE " . join (" OR biblioitems.isbn LIKE ", @isbn_params ) . ")"; + foreach my $isb (@isbns){ + push @query_params, "%$isb%"; + } } + if ( $ean ) { $query .= " AND biblioitems.ean = ? "; push @query_params, "$ean"; @@ -2386,6 +2470,23 @@ sub GetHistory { $query .= ") "; } + if ( @$created_by ) { + $query .= ' AND aqbasket.authorisedby IN ( ' . join( ',', ('?') x @$created_by ) . ')'; + push @query_params, @$created_by; + } + + if ( @$ordernumbers ) { + $query .= ' AND (aqorders.ordernumber IN ( ' . join (',', ('?') x @$ordernumbers ) . '))'; + push @query_params, @$ordernumbers; + } + if ( @$additional_fields ) { + my @baskets = Koha::Acquisition::Baskets->filter_by_additional_fields($additional_fields); + + return [] unless @baskets; + + # No parameterization because record IDs come directly from DB + $query .= ' AND aqbasket.basketno IN ( ' . join( ',', map { $_->basketno } @baskets ) . ' )'; + } if ( C4::Context->preference("IndependentBranches") ) { unless ( C4::Context->IsSuperLibrarian() ) { @@ -2402,7 +2503,7 @@ sub GetHistory { $results = GetRecentAcqui($days); -C<$results> is a ref to a table which containts hashref +C<$results> is a ref to a table which contains hashref =cut @@ -2425,14 +2526,10 @@ sub GetRecentAcqui { =head3 AddClaim -=over - -&AddClaim($ordernumber); + &AddClaim($ordernumber); Add a claim for an order -=back - =cut sub AddClaim { @@ -2485,7 +2582,8 @@ sub GetInvoices { my $dbh = C4::Context->dbh; my $query = qq{ - SELECT aqinvoices.*, aqbooksellers.name AS suppliername, + SELECT aqinvoices.invoiceid, aqinvoices.invoicenumber, aqinvoices.booksellerid, aqinvoices.shipmentdate, aqinvoices.billingdate, aqinvoices.closedate, aqinvoices.shipmentcost, aqinvoices.shipmentcost_budgetid, aqinvoices.message_id, + aqbooksellers.name AS suppliername, COUNT( DISTINCT IF( aqorders.datereceived IS NOT NULL, @@ -2565,9 +2663,13 @@ sub GetInvoices { push @bind_strs, " borrowers.branchcode = ? "; push @bind_args, $args{branchcode}; } + if($args{message_id}) { + push @bind_strs, " aqinvoices.message_id = ? "; + push @bind_args, $args{message_id}; + } $query .= " WHERE " . join(" AND ", @bind_strs) if @bind_strs; - $query .= " GROUP BY aqinvoices.invoiceid "; + $query .= " GROUP BY aqinvoices.invoiceid, aqinvoices.invoicenumber, aqinvoices.booksellerid, aqinvoices.shipmentdate, aqinvoices.billingdate, aqinvoices.closedate, aqinvoices.shipmentcost, aqinvoices.shipmentcost_budgetid, aqinvoices.message_id, aqbooksellers.name"; if($args{order_by}) { my ($column, $direction) = split / /, $args{order_by}; @@ -2644,13 +2746,18 @@ sub GetInvoiceDetails { my $invoice = $sth->fetchrow_hashref; $query = q{ - SELECT aqorders.*, biblio.*, - biblio.copyrightdate, - biblioitems.publishercode, - biblioitems.publicationyear, - aqbasket.basketname + SELECT aqorders.*, + biblio.*, + biblio.copyrightdate, + biblioitems.isbn, + biblioitems.publishercode, + biblioitems.publicationyear, + aqbasket.basketname, + aqbasketgroups.id AS basketgroupid, + aqbasketgroups.name AS basketgroupname FROM aqorders LEFT JOIN aqbasket ON aqorders.basketno = aqbasket.basketno + LEFT JOIN aqbasketgroups ON aqbasket.basketgroupid = aqbasketgroups.id LEFT JOIN biblio ON aqorders.biblionumber = biblio.biblionumber LEFT JOIN biblioitems ON aqorders.biblionumber = biblioitems.biblionumber WHERE invoiceid = ? @@ -2685,7 +2792,7 @@ sub AddInvoice { return unless(%invoice and $invoice{invoicenumber}); my @columns = qw(invoicenumber booksellerid shipmentdate billingdate - closedate shipmentcost shipmentcost_budgetid); + closedate shipmentcost shipmentcost_budgetid message_id); my @set_strs; my @set_args; @@ -2786,7 +2893,7 @@ sub CloseInvoice { Reopen an invoice -Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => C4::Dates->new()->output('iso')) +Equivalent to ModInvoice(invoiceid => $invoiceid, closedate => output_pref({ dt=>dt_from_string, dateonly=>1, otputpref=>'iso' })) =cut @@ -2888,6 +2995,241 @@ sub GetBiblioCountByBasketno { return $sth->fetchrow; } +# Note this subroutine should be moved to Koha::Acquisition::Order +# Will do when a DBIC decision will be taken. +sub populate_order_with_prices { + my ($params) = @_; + + my $order = $params->{order}; + my $booksellerid = $params->{booksellerid}; + return unless $booksellerid; + + my $bookseller = Koha::Acquisition::Booksellers->find( $booksellerid ); + + my $receiving = $params->{receiving}; + my $ordering = $params->{ordering}; + my $discount = $order->{discount}; + $discount /= 100 if $discount > 1; + + if ($ordering) { + $order->{tax_rate_on_ordering} //= $order->{tax_rate}; + if ( $bookseller->listincgst ) { + # The user entered the rrp tax included + $order->{rrp_tax_included} = $order->{rrp}; + + # rrp tax excluded = rrp tax included / ( 1 + tax rate ) + $order->{rrp_tax_excluded} = $order->{rrp_tax_included} / ( 1 + $order->{tax_rate_on_ordering} ); + + # ecost tax excluded = rrp tax excluded * ( 1 - discount ) + $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount ); + + # ecost tax included = rrp tax included ( 1 - discount ) + $order->{ecost_tax_included} = $order->{rrp_tax_included} * ( 1 - $discount ); + } + else { + # The user entered the rrp tax excluded + $order->{rrp_tax_excluded} = $order->{rrp}; + + # rrp tax included = rrp tax excluded * ( 1 - tax rate ) + $order->{rrp_tax_included} = $order->{rrp_tax_excluded} * ( 1 + $order->{tax_rate_on_ordering} ); + + # ecost tax excluded = rrp tax excluded * ( 1 - discount ) + $order->{ecost_tax_excluded} = $order->{rrp_tax_excluded} * ( 1 - $discount ); + + # ecost tax included = rrp tax excluded * ( 1 + tax rate ) * ( 1 - discount ) + $order->{ecost_tax_included} = + $order->{rrp_tax_excluded} * + ( 1 + $order->{tax_rate_on_ordering} ) * + ( 1 - $discount ); + } + + # tax value = quantity * ecost tax excluded * tax rate + $order->{tax_value_on_ordering} = + $order->{quantity} * get_rounded_price($order->{ecost_tax_excluded}) * $order->{tax_rate_on_ordering}; + } + + if ($receiving) { + $order->{tax_rate_on_receiving} //= $order->{tax_rate}; + if ( $bookseller->invoiceincgst ) { + # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value + # we need to keep the exact ecost value + if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_included} )->round ) { + $order->{unitprice} = $order->{ecost_tax_included}; + } + + # The user entered the unit price tax included + $order->{unitprice_tax_included} = $order->{unitprice}; + + # unit price tax excluded = unit price tax included / ( 1 + tax rate ) + $order->{unitprice_tax_excluded} = $order->{unitprice_tax_included} / ( 1 + $order->{tax_rate_on_receiving} ); + } + else { + # Trick for unitprice. If the unit price rounded value is the same as the ecost rounded value + # we need to keep the exact ecost value + if ( Koha::Number::Price->new( $order->{unitprice} )->round == Koha::Number::Price->new( $order->{ecost_tax_excluded} )->round ) { + $order->{unitprice} = $order->{ecost_tax_excluded}; + } + + # The user entered the unit price tax excluded + $order->{unitprice_tax_excluded} = $order->{unitprice}; + + + # unit price tax included = unit price tax included * ( 1 + tax rate ) + $order->{unitprice_tax_included} = $order->{unitprice_tax_excluded} * ( 1 + $order->{tax_rate_on_receiving} ); + } + + # tax value = quantity * unit price tax excluded * tax rate + $order->{tax_value_on_receiving} = $order->{quantity} * get_rounded_price($order->{unitprice_tax_excluded}) * $order->{tax_rate_on_receiving}; + } + + return $order; +} + +=head3 GetOrderUsers + + $order_users_ids = &GetOrderUsers($ordernumber); + +Returns a list of all borrowernumbers that are in order users list + +=cut + +sub GetOrderUsers { + my ($ordernumber) = @_; + + return unless $ordernumber; + + my $query = q| + SELECT borrowernumber + FROM aqorder_users + WHERE ordernumber = ? + |; + my $dbh = C4::Context->dbh; + my $sth = $dbh->prepare($query); + $sth->execute($ordernumber); + my $results = $sth->fetchall_arrayref( {} ); + + my @borrowernumbers; + foreach (@$results) { + push @borrowernumbers, $_->{'borrowernumber'}; + } + + return @borrowernumbers; +} + +=head3 ModOrderUsers + + my @order_users_ids = (1, 2, 3); + &ModOrderUsers($ordernumber, @basketusers_ids); + +Delete all users from order users list, and add users in C<@order_users_ids> +to this users list. + +=cut + +sub ModOrderUsers { + my ( $ordernumber, @order_users_ids ) = @_; + + return unless $ordernumber; + + my $dbh = C4::Context->dbh; + my $query = q| + DELETE FROM aqorder_users + WHERE ordernumber = ? + |; + my $sth = $dbh->prepare($query); + $sth->execute($ordernumber); + + $query = q| + INSERT INTO aqorder_users (ordernumber, borrowernumber) + VALUES (?, ?) + |; + $sth = $dbh->prepare($query); + foreach my $order_user_id (@order_users_ids) { + $sth->execute( $ordernumber, $order_user_id ); + } +} + +sub NotifyOrderUsers { + my ($ordernumber) = @_; + + my @borrowernumbers = GetOrderUsers($ordernumber); + return unless @borrowernumbers; + + my $order = GetOrder( $ordernumber ); + for my $borrowernumber (@borrowernumbers) { + my $patron = Koha::Patrons->find( $borrowernumber ); + my $library = $patron->library->unblessed; + my $biblio = Koha::Biblios->find( $order->{biblionumber} )->unblessed; + my $letter = C4::Letters::GetPreparedLetter( + module => 'acquisition', + letter_code => 'ACQ_NOTIF_ON_RECEIV', + branchcode => $library->{branchcode}, + lang => $patron->lang, + tables => { + 'branches' => $library, + 'borrowers' => $patron->unblessed, + 'biblio' => $biblio, + 'aqorders' => $order, + }, + ); + if ( $letter ) { + C4::Letters::EnqueueLetter( + { + letter => $letter, + borrowernumber => $borrowernumber, + LibraryName => C4::Context->preference("LibraryName"), + message_transport_type => 'email', + } + ) or warn "can't enqueue letter $letter"; + } + } +} + +=head3 FillWithDefaultValues + +FillWithDefaultValues( $marc_record ); + +This will update the record with default value defined in the ACQ framework. +For all existing fields, if a default value exists and there are no subfield, it will be created. +If the field does not exist, it will be created too. + +=cut + +sub FillWithDefaultValues { + my ($record) = @_; + my $tagslib = C4::Biblio::GetMarcStructure( 1, 'ACQ', { unsafe => 1 } ); + if ($tagslib) { + my ($itemfield) = + C4::Biblio::GetMarcFromKohaField( 'items.itemnumber', '' ); + for my $tag ( sort keys %$tagslib ) { + next unless $tag; + next if $tag == $itemfield; + for my $subfield ( sort keys %{ $tagslib->{$tag} } ) { + next if IsMarcStructureInternal($tagslib->{$tag}{$subfield}); + my $defaultvalue = $tagslib->{$tag}{$subfield}{defaultvalue}; + if ( defined $defaultvalue and $defaultvalue ne '' ) { + my @fields = $record->field($tag); + if (@fields) { + for my $field (@fields) { + unless ( defined $field->subfield($subfield) ) { + $field->add_subfields( + $subfield => $defaultvalue ); + } + } + } + else { + $record->insert_fields_ordered( + MARC::Field->new( + $tag, '', '', $subfield => $defaultvalue + ) + ); + } + } + } + } + } +} + 1; __END__