@@ -, +, @@ in calculations listprice/rrp = 16.99 discount = 42% quantity = 8 estimated calculated at 9.85 but order total is 78.83, but 8 times 9.85 = 78.80 --- C4/Acquisition.pm | 54 +++++++++++++++++++++++++++++++-------- C4/Budgets.pm | 32 ++++++++++++++++++----- acqui/basket.pl | 8 +++--- acqui/basketgroup.pl | 10 ++++---- acqui/invoice.pl | 14 +++++----- acqui/ordered.pl | 3 ++- acqui/parcel.pl | 10 ++++---- acqui/pdfformat/layout3pages.pm | 7 ++--- acqui/pdfformat/layout3pagesfr.pm | 1 + acqui/spent.pl | 2 +- reports/orders_by_fund.pl | 4 +-- 11 files changed, 101 insertions(+), 44 deletions(-) --- a/C4/Acquisition.pm +++ a/C4/Acquisition.pm @@ -93,6 +93,8 @@ BEGIN { &NotifyOrderUsers &FillWithDefaultValues + + &get_rounded_price ); } @@ -1472,8 +1474,8 @@ sub ModReceiveOrder { $dbh->do(q| UPDATE aqorders SET - tax_value_on_ordering = quantity * ecost_tax_excluded * tax_rate_on_ordering, - tax_value_on_receiving = quantity * unitprice_tax_excluded * tax_rate_on_receiving + 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}); @@ -1485,8 +1487,8 @@ sub ModReceiveOrder { $order->{tax_rate_on_ordering} //= 0; $order->{unitprice_tax_excluded} //= 0; $order->{tax_rate_on_receiving} //= 0; - $order->{tax_value_on_ordering} = $order->{quantity} * $order->{ecost_tax_excluded} * $order->{tax_rate_on_ordering}; - $order->{tax_value_on_receiving} = $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate_on_receiving}; + $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'; @@ -1648,8 +1650,8 @@ sub CancelReceipt { $dbh->do(q| UPDATE aqorders SET - tax_value_on_ordering = quantity * ecost_tax_excluded * tax_rate_on_ordering, - tax_value_on_receiving = quantity * unitprice_tax_excluded * tax_rate_on_receiving + 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); @@ -2000,6 +2002,37 @@ sub TransferOrder { return $newordernumber; } +=head3 _get_rounding_sql + + $rounding_sql = _get_rounding_sql("mysql_variable_to_round_string"); + +returns the correct SQL routine based on OrderPriceRounding system preference. + +=cut + +sub _get_rounding_sql { + my $round_string = @_; + my $rounding_pref = C4::Context->preference('OrderPriceRounding'); + if ( $rounding_pref eq "nearest_cent" ) { return ("ROUND($round_string,2)"); } + else { return ("$round_string"); } +} + +=head3 get_rounded_price + + $rounded_price = get_rounded_price( $price ); + +returns a price rounded as specified in OrderPriceRounding system preference. + +=cut + +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 )->format(); } + else { return $price; } +} + + =head2 FUNCTIONS ABOUT PARCELS =head3 GetParcels @@ -2150,10 +2183,11 @@ sub GetLateOrders { AND aqbasket.closedate IS NOT NULL AND (aqorders.datecancellationprinted IS NULL OR aqorders.datecancellationprinted='0000-00-00') "; + my ($round_sql_start,$round_sql_end) = _get_rounding_sql(); if ($dbdriver eq "mysql") { $select .= " aqorders.quantity - COALESCE(aqorders.quantityreceived,0) AS quantity, - (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * aqorders.rrp AS subtotal, + (aqorders.quantity - COALESCE(aqorders.quantityreceived,0)) * $round_sql_start aqorders.rrp $round_sql_end AS subtotal, DATEDIFF(CAST(now() AS date),closedate) AS latesince "; if ( defined $delay ) { @@ -2165,7 +2199,7 @@ sub GetLateOrders { # FIXME: account for IFNULL as above $select .= " aqorders.quantity AS quantity, - aqorders.quantity * aqorders.rrp AS subtotal, + aqorders.quantity * $round_sql_start aqorders.rrp $round_sql_end AS subtotal, (CAST(now() AS date) - closedate) AS latesince "; if ( defined $delay ) { @@ -2981,7 +3015,7 @@ sub populate_order_with_prices { # tax value = quantity * ecost tax excluded * tax rate $order->{tax_value_on_ordering} = - $order->{quantity} * $order->{ecost_tax_excluded} * $order->{tax_rate_on_ordering}; + $order->{quantity} * get_rounded_price($order->{ecost_tax_excluded}) * $order->{tax_rate_on_ordering}; } if ($receiving) { @@ -3015,7 +3049,7 @@ sub populate_order_with_prices { } # tax value = quantity * unit price tax excluded * tax rate - $order->{tax_value_on_receiving} = $order->{quantity} * $order->{unitprice_tax_excluded} * $order->{tax_rate_on_receiving}; + $order->{tax_value_on_receiving} = $order->{quantity} * get_rounded_price($order->{unitprice_tax_excluded}) * $order->{tax_rate_on_receiving}; } return $order; --- a/C4/Budgets.pm +++ a/C4/Budgets.pm @@ -209,11 +209,12 @@ sub GetBudgetsPlanCell { my ( $cell, $period, $budget ) = @_; my ($actual, $sth); my $dbh = C4::Context->dbh; + my $roundsql = _get_rounding_sql(qq|ecost_tax_included|); if ( $cell->{'authcat'} eq 'MONTHS' ) { # get the actual amount $sth = $dbh->prepare( qq| - SELECT SUM(ecost_tax_included) AS actual FROM aqorders + SELECT SUM(| . $roundsql . qq|) AS actual FROM aqorders WHERE budget_id = ? AND entrydate like "$cell->{'authvalue'}%" | ); @@ -222,7 +223,7 @@ sub GetBudgetsPlanCell { # get the actual amount $sth = $dbh->prepare( qq| - SELECT SUM(ecost_tax_included) FROM aqorders + SELECT SUM(| . $roundsql . qq|) FROM aqorders LEFT JOIN aqorders_items ON (aqorders.ordernumber = aqorders_items.ordernumber) LEFT JOIN items @@ -234,7 +235,7 @@ sub GetBudgetsPlanCell { # get the actual amount $sth = $dbh->prepare( qq| - SELECT SUM( ecost_tax_included * quantity) AS actual + SELECT SUM( | . $roundsql . qq| * quantity) AS actual FROM aqorders JOIN biblioitems ON (biblioitems.biblionumber = aqorders.biblionumber ) WHERE aqorders.budget_id = ? and itemtype = ? | @@ -247,7 +248,7 @@ sub GetBudgetsPlanCell { # get the actual amount $sth = $dbh->prepare( qq| - SELECT SUM(ecost_tax_included * quantity) AS actual + SELECT SUM(| . $roundsql . qq| * quantity) AS actual FROM aqorders JOIN aqbudgets ON (aqbudgets.budget_id = aqorders.budget_id ) WHERE aqorders.budget_id = ? AND @@ -331,7 +332,7 @@ sub GetBudgetSpent { # unitprice_tax_included should always been set here # we should not need to retrieve ecost_tax_included my $sth = $dbh->prepare(qq| - SELECT SUM( COALESCE(unitprice_tax_included, ecost_tax_included) * quantity ) AS sum FROM aqorders + SELECT SUM( | . _get_rounding_sql("COALESCE(unitprice_tax_included, ecost_tax_included)") . qq| * quantity ) AS sum FROM aqorders WHERE budget_id = ? AND quantityreceived > 0 AND datecancellationprinted IS NULL @@ -357,7 +358,7 @@ sub GetBudgetOrdered { my ($budget_id) = @_; my $dbh = C4::Context->dbh; my $sth = $dbh->prepare(qq| - SELECT SUM(ecost_tax_included * quantity) AS sum FROM aqorders + SELECT SUM(| . _get_rounding_sql(qq|ecost_tax_included|) . qq| * quantity) AS sum FROM aqorders WHERE budget_id = ? AND quantityreceived = 0 AND datecancellationprinted IS NULL @@ -1276,6 +1277,25 @@ sub MoveOrders { return \@report; } +=head1 INTERNAL FUNCTIONS + +=cut + +=head3 _get_rounding_sql + + $rounding_sql = _get_rounding_sql("mysql_variable_to_round_string"); + +returns the correct SQL routine based on OrderPriceRounding system preference. + +=cut + +sub _get_rounding_sql { + my $to_round = shift; + my $rounding_pref = C4::Context->preference('OrderPriceRounding'); + if ($rounding_pref eq 'nearest_cent') { return "ROUND($to_round,2)"; } + else { return "$to_round"; } +} + END { } # module clean-up code here (global destructor) 1; --- a/acqui/basket.pl +++ a/acqui/basket.pl @@ -346,9 +346,9 @@ if ( $op eq 'list' ) { push @books_loop, $line; $foot{$$line{tax_rate}}{tax_rate} = $$line{tax_rate}; - $foot{$$line{tax_rate}}{tax_value} += $$line{tax_value}; + $foot{$$line{tax_rate}}{tax_value} += get_rounded_price($$line{tax_value}); $total_tax_value += $$line{tax_value}; - $foot{$$line{tax_rate}}{quantity} += $$line{quantity}; + $foot{$$line{tax_rate}}{quantity} += get_rounded_price($$line{quantity}); $total_quantity += $$line{quantity}; $foot{$$line{tax_rate}}{total_tax_excluded} += $$line{total_tax_excluded}; $total_tax_excluded += $$line{total_tax_excluded}; @@ -451,8 +451,8 @@ sub get_order_infos { $line{basketno} = $basketno; $line{budget_name} = $budget->{budget_name}; - $line{total_tax_included} = $line{ecost_tax_included} * $line{quantity}; - $line{total_tax_excluded} = $line{ecost_tax_excluded} * $line{quantity}; + $line{total_tax_included} = get_rounded_price($line{ecost_tax_included}) * $line{quantity}; + $line{total_tax_excluded} = get_rounded_price($line{ecost_tax_excluded}) * $line{quantity}; $line{tax_value} = $line{tax_value_on_ordering}; $line{tax_rate} = $line{tax_rate_on_ordering}; --- a/acqui/basketgroup.pl +++ a/acqui/basketgroup.pl @@ -50,7 +50,7 @@ use C4::Auth; use C4::Output; use CGI qw ( -utf8 ); -use C4::Acquisition qw/CloseBasketgroup ReOpenBasketgroup GetOrders GetBasketsByBasketgroup GetBasketsByBookseller ModBasketgroup NewBasketgroup DelBasketgroup GetBasketgroups ModBasket GetBasketgroup GetBasket GetBasketGroupAsCSV/; +use C4::Acquisition qw/CloseBasketgroup ReOpenBasketgroup GetOrders GetBasketsByBasketgroup GetBasketsByBookseller ModBasketgroup NewBasketgroup DelBasketgroup GetBasketgroups ModBasket GetBasketgroup GetBasket GetBasketGroupAsCSV get_rounded_price/; use Koha::EDI qw/create_edi_order get_edifact_ean/; use Koha::Biblioitems; @@ -77,9 +77,9 @@ sub BasketTotal { for my $order (@orders){ # FIXME The following is wrong if ( $bookseller->listincgst ) { - $total = $total + ( $order->{ecost_tax_included} * $order->{quantity} ); + $total = $total + ( get_rounded_price($order->{ecost_tax_included}) * $order->{quantity} ); } else { - $total = $total + ( $order->{ecost_tax_excluded} * $order->{quantity} ); + $total = $total + ( get_rounded_price($order->{ecost_tax_excluded}) * $order->{quantity} ); } } $total .= " " . ($bookseller->invoiceprice // 0); @@ -171,8 +171,8 @@ sub printbasketgrouppdf{ $ord->{tax_value} = $ord->{tax_value_on_ordering}; $ord->{tax_rate} = $ord->{tax_rate_on_ordering}; - $ord->{total_tax_included} = $ord->{ecost_tax_included} * $ord->{quantity}; - $ord->{total_tax_excluded} = $ord->{ecost_tax_excluded} * $ord->{quantity}; + $ord->{total_tax_included} = get_rounded_price($ord->{ecost_tax_included}) * $ord->{quantity}; + $ord->{total_tax_excluded} = get_rounded_price($ord->{ecost_tax_excluded}) * $ord->{quantity}; my $biblioitem = Koha::Biblioitems->search({ biblionumber => $ord->{biblionumber} })->next; --- a/acqui/invoice.pl +++ a/acqui/invoice.pl @@ -123,21 +123,21 @@ my $total_tax_value = 0; foreach my $order (@$orders) { my $line = get_infos( $order, $bookseller); - $line->{total_tax_excluded} = $line->{unitprice_tax_excluded} * $line->{quantity}; - $line->{total_tax_included} = $line->{unitprice_tax_included} * $line->{quantity}; + $line->{total_tax_excluded} = get_rounded_price($line->{unitprice_tax_excluded}) * $line->{quantity}; + $line->{total_tax_included} = get_rounded_price($line->{unitprice_tax_included}) * $line->{quantity}; $line->{tax_value} = $line->{tax_value_on_receiving}; $line->{tax_rate} = $line->{tax_rate_on_receiving}; $foot{$$line{tax_rate}}{tax_rate} = $$line{tax_rate}; - $foot{$$line{tax_rate}}{tax_value} += $$line{tax_value}; + $foot{$$line{tax_rate}}{tax_value} += get_rounded_price($$line{tax_value}); $total_tax_value += $$line{tax_value}; $foot{$$line{tax_rate}}{quantity} += $$line{quantity}; $total_quantity += $$line{quantity}; - $foot{$$line{tax_rate}}{total_tax_excluded} += $$line{total_tax_excluded}; - $total_tax_excluded += $$line{total_tax_excluded}; - $foot{$$line{tax_rate}}{total_tax_included} += $$line{total_tax_included}; - $total_tax_included += $$line{total_tax_included}; + $foot{$$line{tax_rate}}{total_tax_excluded} += get_rounded_price($$line{total_tax_excluded}); + $total_tax_excluded += get_rounded_price($$line{total_tax_excluded}); + $foot{$$line{tax_rate}}{total_tax_included} += get_rounded_price($$line{total_tax_included}); + $total_tax_included += get_rounded_price($$line{total_tax_included}); $line->{orderline} = $line->{parent_ordernumber}; push @orders_loop, $line; --- a/acqui/ordered.pl +++ a/acqui/ordered.pl @@ -32,6 +32,7 @@ use Modern::Perl; use CGI qw ( -utf8 ); use C4::Auth; use C4::Output; +use C4::Acquisition; my $dbh = C4::Context->dbh; my $input = new CGI; @@ -88,7 +89,7 @@ while ( my $data = $sth->fetchrow_hashref ) { $left = $data->{'quantity'}; } if ( $left && $left > 0 ) { - my $subtotal = $left * $data->{'ecost'}; + my $subtotal = $left * get_rounded_price($data->{'ecost'}); $data->{subtotal} = sprintf( "%.2f", $subtotal ); $data->{'left'} = $left; push @ordered, $data; --- a/acqui/parcel.pl +++ a/acqui/parcel.pl @@ -134,7 +134,7 @@ for my $order ( @orders ) { $order->{unitprice} = $order->{unitprice_tax_excluded}; } - $order->{total} = $order->{unitprice} * $order->{quantity}; + $order->{total} = get_rounded_price($order->{unitprice}) * $order->{quantity}; my %line = %{ $order }; $line{invoice} = $invoice->{invoicenumber}; @@ -152,8 +152,8 @@ for my $order ( @orders ) { $line{tax_rate} = $line{tax_rate_on_receiving}; $foot{$line{tax_rate}}{tax_rate} = $line{tax_rate}; $foot{$line{tax_rate}}{tax_value} += $line{tax_value}; - $total_tax_excluded += $line{unitprice_tax_excluded} * $line{quantity}; - $total_tax_included += $line{unitprice_tax_included} * $line{quantity}; + $total_tax_excluded += get_rounded_price($line{unitprice_tax_excluded}) * $line{quantity}; + $total_tax_included += get_rounded_price($line{unitprice_tax_included}) * $line{quantity}; my $suggestion = GetSuggestionInfoFromBiblionumber($line{biblionumber}); $line{suggestionid} = $suggestion->{suggestionid}; @@ -172,7 +172,7 @@ for my $order ( @orders ) { my $budget_name = GetBudgetName( $line{budget_id} ); $line{budget_name} = $budget_name; - $subtotal_for_funds->{ $line{budget_name} }{ecost} += $order->{ecost} * $order->{quantity}; + $subtotal_for_funds->{ $line{budget_name} }{ecost} += get_rounded_price($order->{ecost}) * $order->{quantity}; $subtotal_for_funds->{ $line{budget_name} }{unitprice} += $order->{total}; push @loop_received, \%line; @@ -230,7 +230,7 @@ unless( defined $invoice->{closedate} ) { } else { $order->{ecost} = $order->{ecost_tax_excluded}; } - $order->{total} = $order->{ecost} * $order->{quantity}; + $order->{total} = get_rounded_price($order->{ecost}) * $order->{quantity}; my %line = %$order; --- a/acqui/pdfformat/layout3pages.pm +++ a/acqui/pdfformat/layout3pages.pm @@ -28,6 +28,7 @@ use List::MoreUtils qw/uniq/; use Modern::Perl; use utf8; +use C4::Acquisition; use Koha::Number::Price; use Koha::DateUtils; use Koha::Libraries; @@ -221,9 +222,9 @@ sub printbaskets { $total_tax_excluded += $ord->{total_tax_excluded}; $total_tax_included += $ord->{total_tax_included}; $totaltax_value += $ord->{tax_value}; - $totaldiscount += ($ord->{rrp_tax_excluded} - $ord->{ecost_tax_excluded} ) * $ord->{quantity}; - $total_rrp_tax_excluded += $ord->{rrp_tax_excluded} * $ord->{quantity}; - $total_rrp_tax_included += $ord->{rrp_tax_included} * $ord->{quantity}; + $totaldiscount += (get_rounded_price($ord->{rrp_tax_excluded}) - get_rounded_price($ord->{ecost_tax_excluded}) ) * $ord->{quantity}; + $total_rrp_tax_excluded += get_rounded_price($ord->{rrp_tax_excluded}) * $ord->{quantity}; + $total_rrp_tax_included += get_rounded_price($ord->{rrp_tax_included}) * $ord->{quantity}; push @gst, $ord->{tax_rate}; } @gst = uniq map { $_ * 100 } @gst; --- a/acqui/pdfformat/layout3pagesfr.pm +++ a/acqui/pdfformat/layout3pagesfr.pm @@ -27,6 +27,7 @@ use List::MoreUtils qw/uniq/; use Modern::Perl; use utf8; +use C4::Acquisition; use Koha::Number::Price; use Koha::DateUtils; use Koha::Libraries; --- a/acqui/spent.pl +++ a/acqui/spent.pl @@ -91,7 +91,7 @@ my @spent; while ( my $data = $sth->fetchrow_hashref ) { my $recv = $data->{'quantityreceived'}; if ( $recv > 0 ) { - my $rowtotal = $recv * $data->{'unitprice'}; + my $rowtotal = $recv * get_rounded_price($data->{'unitprice'}); $data->{'rowtotal'} = sprintf( "%.2f", $rowtotal ); $data->{'unitprice'} = sprintf( "%.2f", $data->{'unitprice'} ); $subtotal += $rowtotal; --- a/reports/orders_by_fund.pl +++ a/reports/orders_by_fund.pl @@ -107,8 +107,8 @@ if ( $get_orders ) { $order->{title} = $biblio ? $biblio->title : ''; $order->{title} ||= $order->{biblionumber}; - $order->{'total_rrp'} = $order->{'quantity'} * $order->{'rrp'}; - $order->{'total_ecost'} = $order->{'quantity'} * $order->{'ecost'}; + $order->{'total_rrp'} = get_rounded_price($order->{'quantity'}) * $order->{'rrp'}; + $order->{'total_ecost'} = get_rounded_price($order->{'quantity'}) * $order->{'ecost'}; # Format the dates and currencies correctly $order->{'datereceived'} = output_pref(dt_from_string($order->{'datereceived'})); --