From b45f8d066e40d16245a838a95c6fc61706cededb Mon Sep 17 00:00:00 2001 From: Nick Clemens Date: Fri, 8 Feb 2019 20:24:05 +0000 Subject: [PATCH] Bug 22296: Add Invoice Adjustments to GetBudgetHierarchy To test: 1 - prove -v t/db_dependent/Budgets.t 2 - Create some invoices on a single budget Invoice 1: add a non-encumbered adjustment for 1 add an encumbered adjustment 2 leave open Invoice 2: add a non-encumbered adjustment for 4 add an encumbered adjustment for 8 3 - View acq home page, should see 2 order and 12 spent for the budget 4 - View the spent and ordered pages, they should show the correct amounts 5 - Add more orders etc and confirm things total correctly https://bugs.koha-community.org/show_bug.cgi?id=22296 Signed-off-by: Janet McGowan Signed-off-by: Josef Moravec Signed-off-by: Nick Clemens (cherry picked from commit deb968ea45276e56826b592026617a96398b61da) Signed-off-by: Martin Renvoize --- C4/Budgets.pm | 24 ++++++++++++++++++--- t/db_dependent/Budgets.t | 46 +++++++++++++++++++++++++++++++++------- 2 files changed, 59 insertions(+), 11 deletions(-) diff --git a/C4/Budgets.pm b/C4/Budgets.pm index 6c7e9460e7..620daf6b67 100644 --- a/C4/Budgets.pm +++ b/C4/Budgets.pm @@ -584,27 +584,45 @@ sub GetBudgetHierarchy { WHERE closedate IS NULL GROUP BY shipmentcost_budgetid |, 'budget_id'); + my $hr_budget_spent_adjustment = $dbh->selectall_hashref(q| + SELECT budget_id, + SUM(adjustment) as adjustments + FROM aqinvoice_adjustments + JOIN aqinvoices USING (invoiceid) + WHERE closedate IS NOT NULL + GROUP BY budget_id + |, 'budget_id'); + my $hr_budget_ordered_adjustment = $dbh->selectall_hashref(q| + SELECT budget_id, + SUM(adjustment) as adjustments + FROM aqinvoice_adjustments + JOIN aqinvoices USING (invoiceid) + WHERE closedate IS NULL AND encumber_open = 1 + GROUP BY budget_id + |, 'budget_id'); foreach my $budget (@sort) { if ( not defined $budget->{budget_parent_id} ) { - _recursiveAdd( $budget, undef, $hr_budget_spent, $hr_budget_spent_shipment, $hr_budget_ordered, $hr_budget_ordered_shipment ); + _recursiveAdd( $budget, undef, $hr_budget_spent, $hr_budget_spent_shipment, $hr_budget_ordered, $hr_budget_ordered_shipment, $hr_budget_spent_adjustment, $hr_budget_ordered_adjustment ); } } return \@sort; } sub _recursiveAdd { - my ($budget, $parent, $hr_budget_spent, $hr_budget_spent_shipment, $hr_budget_ordered, $hr_budget_ordered_shipment ) = @_; + my ($budget, $parent, $hr_budget_spent, $hr_budget_spent_shipment, $hr_budget_ordered, $hr_budget_ordered_shipment, $hr_budget_spent_adjustment, $hr_budget_ordered_adjustment ) = @_; foreach my $child (@{$budget->{children}}){ - _recursiveAdd($child, $budget, $hr_budget_spent, $hr_budget_spent_shipment, $hr_budget_ordered, $hr_budget_ordered_shipment ); + _recursiveAdd($child, $budget, $hr_budget_spent, $hr_budget_spent_shipment, $hr_budget_ordered, $hr_budget_ordered_shipment, $hr_budget_spent_adjustment, $hr_budget_ordered_adjustment ); } $budget->{budget_spent} += $hr_budget_spent->{$budget->{budget_id}}->{budget_spent}; $budget->{budget_spent} += $hr_budget_spent_shipment->{$budget->{budget_id}}->{shipmentcost}; + $budget->{budget_spent} += $hr_budget_spent_adjustment->{$budget->{budget_id}}->{adjustments}; $budget->{budget_ordered} += $hr_budget_ordered->{$budget->{budget_id}}->{budget_ordered}; $budget->{budget_ordered} += $hr_budget_ordered_shipment->{$budget->{budget_id}}->{shipmentcost}; + $budget->{budget_ordered} += $hr_budget_ordered_adjustment->{$budget->{budget_id}}->{adjustments}; $budget->{total_spent} += $budget->{budget_spent}; $budget->{total_ordered} += $budget->{budget_ordered}; diff --git a/t/db_dependent/Budgets.t b/t/db_dependent/Budgets.t index 6e4ad2c729..6cb87a72d9 100755 --- a/t/db_dependent/Budgets.t +++ b/t/db_dependent/Budgets.t @@ -819,12 +819,23 @@ is( scalar @{$authCat}, 0, "GetBudgetAuthCats returns only non-empty sorting cat # /Test GetBudgetAuthCats subtest 'GetBudgetSpent and GetBudgetOrdered' => sub { - plan tests => 10; + plan tests => 20; + my $budget_period = $builder->build({ + source => 'Aqbudgetperiod', + value => { + budget_period_active => 1, + budget_total => 10000, + } + }); my $budget = $builder->build({ source => 'Aqbudget', value => { budget_amount => 1000, + budget_encumb => undef, + budget_expend => undef, + budget_period_id => $budget_period->{budget_period_id}, + budget_parent_id => undef, } }); my $invoice = $builder->build({ @@ -834,11 +845,14 @@ subtest 'GetBudgetSpent and GetBudgetOrdered' => sub { } }); - my $spent = GetBudgetSpent( $budget->{budget_id} ); - my $ordered = GetBudgetOrdered( $budget->{budget_id} ); + my $spent = GetBudgetSpent( $budget->{budget_id} ); + my $ordered = GetBudgetOrdered( $budget->{budget_id} ); + my $hierarchy = GetBudgetHierarchy($budget_period->{budget_period_id} ); is( $spent, 0, "New budget, no orders/invoices, should be nothing spent"); is( $ordered, 0, "New budget, no orders/invoices, should be nothing ordered"); + is( @$hierarchy[0]->{total_spent},0,"New budgets, no orders/invoices, budget hierarchy shows 0 spent"); + is( @$hierarchy[0]->{total_ordered},0,"New budgets, no orders/invoices, budget hierarchy shows 0 ordered"); my $inv_adj_1 = $builder->build({ source => 'AqinvoiceAdjustment', @@ -852,8 +866,11 @@ subtest 'GetBudgetSpent and GetBudgetOrdered' => sub { $spent = GetBudgetSpent( $budget->{budget_id} ); $ordered = GetBudgetOrdered( $budget->{budget_id} ); + $hierarchy = GetBudgetHierarchy($budget_period->{budget_period_id} ); is( $spent, 0, "After adding invoice adjustment on open invoice, should be nothing spent"); is( $ordered, 0, "After adding invoice adjustment on open invoice not encumbered, should be nothing ordered"); + is( @$hierarchy[0]->{total_spent},0,"After adding invoice adjustment on open invoice, budget hierarchy shows 0 spent"); + is( @$hierarchy[0]->{total_ordered},0,"After adding invoice adjustment on open invoice, budget hierarchy shows 0 ordered"); my $inv_adj_2 = $builder->build({ source => 'AqinvoiceAdjustment', @@ -867,8 +884,11 @@ subtest 'GetBudgetSpent and GetBudgetOrdered' => sub { $spent = GetBudgetSpent( $budget->{budget_id} ); $ordered = GetBudgetOrdered( $budget->{budget_id} ); + $hierarchy = GetBudgetHierarchy($budget_period->{budget_period_id} ); is( $spent, 0, "After adding invoice adjustment on open invoice, should be nothing spent"); is( $ordered, 3, "After adding invoice adjustment on open invoice encumbered, should be 3 ordered"); + is( @$hierarchy[0]->{total_spent},0,"After adding invoice adjustment on open invoice encumbered, budget hierarchy shows 0 spent"); + is( @$hierarchy[0]->{total_ordered},3,"After adding invoice adjustment on open invoice encumbered, budget hierarchy shows 3 ordered"); my $invoice_2 = $builder->build({ source => 'Aqinvoice', @@ -897,13 +917,20 @@ subtest 'GetBudgetSpent and GetBudgetOrdered' => sub { $spent = GetBudgetSpent( $budget->{budget_id} ); $ordered = GetBudgetOrdered( $budget->{budget_id} ); + $hierarchy = GetBudgetHierarchy($budget_period->{budget_period_id} ); is( $spent, 6, "After adding invoice adjustment on closed invoice, should be 6 spent, encumber has no affect once closed"); is( $ordered, 3, "After adding invoice adjustment on closed invoice, should still be 3 ordered"); + is( @$hierarchy[0]->{total_spent},6,"After adding invoice adjustment on closed invoice, budget hierarchy shows 6 spent"); + is( @$hierarchy[0]->{total_ordered},3,"After adding invoice adjustment on closed invoice, budget hierarchy still shows 3 ordered"); - my $budget_2 = $builder->build({ + my $budget0 = $builder->build({ source => 'Aqbudget', value => { budget_amount => 1000, + budget_encumb => undef, + budget_expend => undef, + budget_period_id => $budget_period->{budget_period_id}, + budget_parent_id => $budget->{budget_id}, } }); my $inv_adj_5 = $builder->build({ @@ -912,7 +939,7 @@ subtest 'GetBudgetSpent and GetBudgetOrdered' => sub { invoiceid => $invoice->{invoiceid}, adjustment => 3, encumber_open => 1, - budget_id => $budget_2->{budget_id}, + budget_id => $budget0->{budget_id}, } }); my $inv_adj_6 = $builder->build({ @@ -921,14 +948,17 @@ subtest 'GetBudgetSpent and GetBudgetOrdered' => sub { invoiceid => $invoice_2->{invoiceid}, adjustment => 3, encumber_open => 1, - budget_id => $budget_2->{budget_id}, + budget_id => $budget0->{budget_id}, } }); $spent = GetBudgetSpent( $budget->{budget_id} ); $ordered = GetBudgetOrdered( $budget->{budget_id} ); - is( $spent, 6, "After adding invoice adjustment on a different budget should be 6 spent/budget unaffected"); - is( $ordered, 3, "After adding invoice adjustment on a different budget, should still be 3 ordered/budget unaffected"); + $hierarchy = GetBudgetHierarchy($budget_period->{budget_period_id} ); + is( $spent, 6, "After adding invoice adjustment on a child budget should be 6 spent/budget unaffected"); + is( $ordered, 3, "After adding invoice adjustment on a child budget, should still be 3 ordered/budget unaffected"); + is( @$hierarchy[0]->{total_spent},9,"After adding invoice adjustment on child budget, budget hierarchy shows 9 spent"); + is( @$hierarchy[0]->{total_ordered},6,"After adding invoice adjustment on child budget, budget hierarchy shows 6 ordered"); }; -- 2.39.5