@@ -, +, @@ calculating fund values - Fund = Choose the fund from step #5 - Quantity = 1 - Vendor price = 10 - Tax rate = 15% - Discount = 20% - Retail price = 10.00 - Replacement cost = 10.00 - Actual cost = 10.00 --- C4/Budgets.pm | 46 ++++++++++++++++--- acqui/ordered.pl | 9 ++-- acqui/spent.pl | 11 +++-- .../prog/en/modules/acqui/ordered.tt | 6 ++- .../prog/en/modules/acqui/spent.tt | 6 ++- 5 files changed, 63 insertions(+), 15 deletions(-) --- a/C4/Budgets.pm +++ a/C4/Budgets.pm @@ -79,6 +79,7 @@ BEGIN { CloneBudgetPeriod CloneBudgetHierarchy MoveOrders + FieldsForCalculatingFundValues ); } @@ -341,10 +342,12 @@ sub ModBudgetPlan { sub GetBudgetSpent { my ($budget_id) = @_; my $dbh = C4::Context->dbh; - # unitprice_tax_included should always been set here - # we should not need to retrieve ecost_tax_included + + # Choose correct unitprice and ecost fields + my ( $unitprice_field, $ecost_field ) = FieldsForCalculatingFundValues(); + my $sth = $dbh->prepare(qq| - SELECT SUM( | . C4::Acquisition::get_rounding_sql("COALESCE(unitprice_tax_included, ecost_tax_included)") . qq| * quantity ) AS sum FROM aqorders + SELECT SUM( | . C4::Acquisition::get_rounding_sql("COALESCE($unitprice_field, $ecost_field)") . qq| * quantity ) AS sum FROM aqorders WHERE budget_id = ? AND quantityreceived > 0 AND datecancellationprinted IS NULL @@ -374,8 +377,12 @@ sub GetBudgetSpent { sub GetBudgetOrdered { my ($budget_id) = @_; my $dbh = C4::Context->dbh; + + # Get correct unitprice and ecost prices as possible + my ( $unitprice_field, $ecost_field ) = FieldsForCalculatingFundValues(); + my $sth = $dbh->prepare(qq| - SELECT SUM(| . C4::Acquisition::get_rounding_sql(qq|ecost_tax_included|) . qq| * quantity) AS sum FROM aqorders + SELECT SUM(| . C4::Acquisition::get_rounding_sql(qq|$ecost_field|) . qq| * quantity) AS sum FROM aqorders WHERE budget_id = ? AND quantityreceived = 0 AND datecancellationprinted IS NULL @@ -550,18 +557,22 @@ sub GetBudgetHierarchy { foreach my $first_parent (@first_parents) { _add_budget_children(\@sort, $first_parent, 0); } + + # Get correct unitprice and ecost prices as possible + my ( $unitprice_field, $ecost_field ) = FieldsForCalculatingFundValues(); + if (!$skiptotals) { # Get all the budgets totals in as few queries as possible my $hr_budget_spent = $dbh->selectall_hashref(q| SELECT aqorders.budget_id, aqbudgets.budget_parent_id, - SUM( | . C4::Acquisition::get_rounding_sql(qq|COALESCE(unitprice_tax_included, ecost_tax_included)|) . q| * quantity ) AS budget_spent + SUM( | . C4::Acquisition::get_rounding_sql(qq|COALESCE($unitprice_field, $ecost_field)|) . q| * quantity ) AS budget_spent FROM aqorders JOIN aqbudgets USING (budget_id) WHERE quantityreceived > 0 AND datecancellationprinted IS NULL GROUP BY budget_id, budget_parent_id |, 'budget_id'); my $hr_budget_ordered = $dbh->selectall_hashref(q| SELECT aqorders.budget_id, aqbudgets.budget_parent_id, - SUM( | . C4::Acquisition::get_rounding_sql(qq|ecost_tax_included|) . q| * quantity) AS budget_ordered + SUM( | . C4::Acquisition::get_rounding_sql(qq|$ecost_field|) . q| * quantity) AS budget_ordered FROM aqorders JOIN aqbudgets USING (budget_id) WHERE quantityreceived = 0 AND datecancellationprinted IS NULL GROUP BY budget_id, budget_parent_id @@ -1399,6 +1410,29 @@ sub MoveOrders { return \@report; } +=head2 FieldsForCalculatingFundValues + + my ( $unitprice_field, $ecost_field ) = FieldsForCalculatingFundValues(); + +Fetch the tax inclusive or tax exclusive database fields for caclulating fund values based +on the value of the CalculateFundValuesIncludingTax system preference. + +=cut + +sub FieldsForCalculatingFundValues { + + # Choose correct unitprice and ecost fields + my $unitprice_field = 'unitprice_tax_included'; + my $ecost_field = 'ecost_tax_included'; + + if ( !C4::Context->preference('CalculateFundValuesIncludingTax') ) { + $unitprice_field = 'unitprice_tax_excluded'; + $ecost_field = 'ecost_tax_excluded'; + } + + return ( $unitprice_field, $ecost_field); +} + END { } # module clean-up code here (global destructor) 1; --- a/acqui/ordered.pl +++ a/acqui/ordered.pl @@ -49,11 +49,14 @@ my ( $template, $loggedinuser, $cookie ) = get_template_and_user( } ); +# Choose correct ecost field +my ( $unitprice_field, $ecost_field ) = C4::Budgets->FieldsForCalculatingFundValues(); + my $query = < quantityreceived OR quantityreceived IS NULL) GROUP BY aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber, tleft, - ecost_tax_included, budgetdate, entrydate, + $ecost_field, budgetdate, entrydate, aqbasket.booksellerid, aqbooksellers.name, title @@ -96,7 +99,7 @@ while ( my $data = $sth->fetchrow_hashref ) { $left = $data->{'quantity'}; } if ( $left && $left > 0 ) { - my $subtotal = $left * get_rounded_price( $data->{'ecost_tax_included'} ); + my $subtotal = $left * get_rounded_price( $data->{$ecost_field} ); $data->{subtotal} = sprintf( "%.2f", $subtotal ); $data->{'left'} = $left; push @ordered, $data; --- a/acqui/spent.pl +++ a/acqui/spent.pl @@ -51,6 +51,9 @@ my ( $template, $loggedinuser, $cookie ) = get_template_and_user( } ); +# Get correct unitprice field +my ( $unitprice_field ) = C4::Budgets->FieldsForCalculatingFundValues(); + my $query = <fetchrow_hashref ) { my $recv = $data->{'quantityreceived'}; $data->{'itemtypes'} = [split('\|', $data->{itypes})]; if ( $recv > 0 ) { - my $rowtotal = $recv * get_rounded_price($data->{'unitprice_tax_included'}); + my $rowtotal = $recv * get_rounded_price($data->{$unitprice_field}); $data->{'rowtotal'} = sprintf( "%.2f", $rowtotal ); - $data->{'unitprice_tax_included'} = sprintf( "%.2f", $data->{'unitprice_tax_included'} ); + $data->{'unitprice_tax_included'} = sprintf( "%.2f", $data->{$unitprice_field} ); $subtotal += $rowtotal; push @spent, $data; } --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/ordered.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/ordered.tt @@ -82,7 +82,11 @@ [% order.left | html %] - [% order.ecost_tax_included | $Price %] + [% IF Koha.Preference('CalculateFundValuesIncludingTax') %] + [% order.ecost_tax_included | $Price %] + [% ELSE %] + [% order.ecost_tax_excluded | $Price %] + [% END %] [% order.entrydate | $KohaDates %] --- a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/spent.tt +++ a/koha-tmpl/intranet-tmpl/prog/en/modules/acqui/spent.tt @@ -83,7 +83,11 @@ [% order.quantityreceived | html %] - [% order.unitprice_tax_included | $Price %] + [% IF Koha.Preference('CalculateFundValuesIncludingTax') %] + [% order.unitprice_tax_included | $Price %] + [% ELSE %] + [% order.unitprice_tax_excluded | $Price %] + [% END %] [% order.entrydate | $KohaDates %] --