[Koha-patches] [PATCH] (BUG #4388) aqbudgets.pl: add a column in the table for display the total spent of level and sublevels

[Alex Arnaud]

---
 C4/Budgets.pm                                      |   32 ++++++++++++++++++++
 admin/aqbudgets.pl                                 |    9 ++++--
 .../prog/en/modules/admin/aqbudgets.tmpl           |    3 ++
 3 files changed, 41 insertions(+), 3 deletions(-)

diff --git a/C4/Budgets.pm b/C4/Budgets.pm
index eb97615..de19f59 100644
--- a/C4/Budgets.pm
+++ b/C4/Budgets.pm
@@ -41,6 +41,7 @@ BEGIN {
         &GetBudgetSpent
         &GetBudgetOrdered
         &GetPeriodsCount
+        &GetChildBudgetsSpent
 
 	    &GetBudgetPeriod
         &GetBudgetPeriods
@@ -678,6 +679,37 @@ gets all budgets
 =cut
 
 # -------------------------------------------------------------------
+sub GetChildBudgetsSpent {
+    my ( $budget_id ) = @_;
+    my $dbh = C4::Context->dbh;
+    my $query = "
+        SELECT *
+        FROM   aqbudgets
+        WHERE  budget_parent_id=?
+        ";
+    my $sth = $dbh->prepare($query);
+    $sth->execute( $budget_id );
+    my $result = $sth->fetchall_arrayref({});
+    my $total_spent = GetBudgetSpent($budget_id);
+    if ($result){
+        $total_spent += GetChildBudgetsSpent($_->{"budget_id"}) foreach @$result;    
+    }
+    return $total_spent;
+}
+
+=head3 GetChildBudgetsSpent
+
+=over 4
+
+&GetChildBudgetsSpent($budget-id);
+
+gets the total spent of the level and sublevels of $budget_id
+
+=back
+
+=cut
+
+# -------------------------------------------------------------------
 sub GetBudgets {
     my ($filters,$orderby) = @_;
     return SearchInTable("aqbudgets",$filters, $orderby, undef,undef, undef, "wide");
diff --git a/admin/aqbudgets.pl b/admin/aqbudgets.pl
index 8c6d2b4..5010ccd 100755
--- a/admin/aqbudgets.pl
+++ b/admin/aqbudgets.pl
@@ -68,7 +68,7 @@ if  (  not defined $template->{param_map}->{'CAN_user_acquisition_budget_add_del
 my $num=FormatNumber;
 
 my $script_name               = "/cgi-bin/koha/admin/aqbudgets.pl";
-my $budget_hash=$input->Vars;
+my $budget_hash               = $input->Vars;
 my $budget_id                 = $$budget_hash{budget_id};
 my $budget_permission         = $input->param('budget_permission');
 my $budget_period_dropbox     = $input->param('budget_period_dropbox');
@@ -243,6 +243,9 @@ if ($op eq 'add_form') {
 	#This Looks WEIRD to me : should budgets be filtered in such a way ppl who donot own it would not see the amount spent on the budget by others ?
 
     foreach my $budget (@budgets) {
+        #Level and sublevels total spent
+        $budget->{'total_levels_spent'} = GetChildBudgetsSpent($budget->{"budget_id"});
+
         # PERMISSIONS
         unless($staffflags->{'superlibrarian'} % 2   == 1 ) {
             #IF NO PERMS, THEN DISABLE EDIT/DELETE
@@ -283,14 +286,14 @@ if ($op eq 'add_form') {
         # adds to total  - only if budget is a 'top-level' budget
         $period_alloc_total += $budget->{'budget_amount_total'} if $budget->{'depth'} == 0;
         $base_spent_total += $budget->{'budget_spent'};
-        $budget->{'budget_remaining'} = $budget->{'budget_amount'} - $budget->{'budget_spent'};
+        $budget->{'budget_remaining'} = $budget->{'budget_amount'} - $budget->{'total_levels_spent'};
 
 # if amount == 0 dont display...
         delete  $budget->{'budget_unalloc_sublevel'} if  $budget->{'budget_unalloc_sublevel'} == 0 ;
 
         $budget->{'remaining_pos'} = 1 if $budget->{'budget_remaining'} > 0;
         $budget->{'remaining_neg'} = 1 if $budget->{'budget_remaining'} < 0;
-		for (grep {/budget_spent|budget_amount|budget_remaining|budget_unalloc/} keys %$budget){
+		for (grep {/total_levels_spent|budget_spent|budget_amount|budget_remaining|budget_unalloc/} keys %$budget){
         $$budget{$_}               = $num->format_price( $$budget{$_} ) if defined($$budget{$_})
 		}
 
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tmpl
index fdb7410..54e321a 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tmpl
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/admin/aqbudgets.tmpl
@@ -163,6 +163,7 @@
             <th>Total<br />allocated</th>
             <th>Base-level<br />allocated</th>
             <th>Base-level<br />spent</th>
+            <th>Total sublevels<br />spent</th>
             <th>Base-level<br />remaining</th>
             <th class="tooltipcontent">&nbsp;</th>
             <th>Actions</th>
@@ -174,6 +175,7 @@
     <th nowrap="nowrap" class="data"> <!-- TMPL_VAR NAME="period_alloc_total"--></th>
     <th nowrap="nowrap"  class="data"> <!-- TMPL_VAR NAME="base_alloc_total"--></th>
     <th class="data"><!-- TMPL_VAR NAME="base_spent_total"--></th>
+    <th class="data"><!-- TMPL_VAR NAME="base_spent_total"--></th>
     <th class="data"><!-- TMPL_VAR NAME="base_remaining_total"--></th>
     <th class="tooltipcontent"></th>
     <th></th>
@@ -193,6 +195,7 @@
     <td class="data"><!-- TMPL_VAR NAME="budget_amount_total" --></td>
     <td class="data"><!-- TMPL_VAR NAME="budget_amount" --> </td>
     <td class="data"><!-- TMPL_VAR NAME="budget_spent" --> </td>
+    <td class="data"><!-- TMPL_VAR NAME="total_levels_spent" --></td>
     <td class="data"
     <!-- TMPL_IF NAME="remaining_pos" --> style="color: green;" <!-- /TMPL_IF -->
     <!-- TMPL_IF NAME="remaining_neg" --> style="color: red;" <!-- /TMPL_IF -->   >
-- 
1.6.3.3