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 <janet.mcgowan@ptfs-europe.com>

Signed-off-by: Josef Moravec <josef.moravec@gmail.com>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
(cherry picked from commit deb968ea45276e56826b592026617a96398b61da)
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

diff --git a/C4/Budgets.pm b/C4/Budgets.pm
index 6c7e946..620daf6 100644 (file)
--- 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 6e4ad2c..6cb87a7 100755 (executable)
--- 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");
 
 };