From 99e1d897e8c4102c95d47df1d07b033bf1b2c35e Mon Sep 17 00:00:00 2001
From: Paul Poulain <paul.poulain@biblibre.com>
Date: Fri, 5 Aug 2011 11:49:40 +0200
Subject: [PATCH] BZ6069: Multiple level budget list on suggestions

Librarians dealing with a suggestion have (and must have) access to all
budgets.
For a large library/network, it means the list can be huge and impossible to
deal with.
A possible solution would be to have multiple level lists : one for the top
level, then one for the 2nd level, that appears when the 1st level is choosen,
then one for the 3rd level (if applicable) when the 2nd level is choosen.
---
 C4/Budgets.pm                                      |   55 ++++++++++++++++++
 .../prog/en/modules/suggestion/suggestion.tt       |   59 ++++++++++++++++++++
 suggestion/suggestion.pl                           |   22 +++----
 3 files changed, 124 insertions(+), 12 deletions(-)

diff --git a/C4/Budgets.pm b/C4/Budgets.pm
index 98e5d63..c4b44c9 100644
--- a/C4/Budgets.pm
+++ b/C4/Budgets.pm
@@ -46,6 +46,10 @@ BEGIN {
 
 	    &GetBudgetPeriod
         &GetBudgetPeriods
+        &GetBudgetFirstChild
+        &GetBudgetParent
+        &GetBudgetSecondChild
+
         &ModBudgetPeriod
         &AddBudgetPeriod
 	    &DelBudgetPeriod
@@ -464,6 +468,57 @@ sub GetBudgetPeriod {
 }
 
 # -------------------------------------------------------------------
+sub GetBudgetFirstChild {
+   my $dbh = C4::Context->dbh;
+   my $query = "SELECT *
+                    FROM `aqbudgets`
+                    WHERE budget_parent_id
+                    IN (
+                    SELECT budget_id FROM `aqbudgets`
+                    JOIN `aqbudgetperiods` ON aqbudgets.budget_period_id = aqbudgetperiods.budget_period_id
+                    WHERE ((`budget_parent_id` IS NULL)AND (`budget_period_active` LIKE 1)))
+                    ORDER BY `aqbudgets`.`budget_parent_id`, budget_name ASC";
+    my $sth= $dbh->prepare($query);
+    $sth->execute();
+    my $data = $sth->fetchall_arrayref({});
+    return $data;
+}
+# -------------------------------------------------------------------
+sub GetBudgetParent {
+    my $dbh = C4::Context->dbh;
+    my $query = "SELECT *
+                    FROM `aqbudgets`
+                    JOIN `aqbudgetperiods` ON aqbudgets.budget_period_id = aqbudgetperiods.budget_period_id
+                    WHERE ((`budget_parent_id` IS NULL)AND (`budget_period_active` LIKE 1))
+                    ORDER BY `aqbudgets`.`budget_id`, budget_name ASC";
+    my $sth= $dbh->prepare($query);
+    $sth->execute();
+    my $data = $sth->fetchall_arrayref({});
+    return $data;
+}
+# -------------------------------------------------------------------
+sub GetBudgetSecondChild {
+    my $dbh = C4::Context->dbh;
+    my $query = "SELECT *
+                    FROM `aqbudgets`
+                    WHERE budget_parent_id
+                    IN (
+                    SELECT budget_id
+                    FROM `aqbudgets`
+                    WHERE budget_parent_id
+                    IN (
+                    SELECT budget_id
+                    FROM `aqbudgets`
+                    JOIN `aqbudgetperiods` ON aqbudgets.budget_period_id = aqbudgetperiods.budget_period_id
+                    WHERE ((`budget_parent_id` IS NULL)AND (`budget_period_active` LIKE 1))))
+                    ORDER BY `aqbudgets`.`budget_parent_id` , budget_name ASC";
+    my $sth= $dbh->prepare($query);
+    $sth->execute();
+    my $data = $sth->fetchall_arrayref({});
+    return $data;
+}
+# -------------------------------------------------------------------
+
 sub DelBudgetPeriod{
 	my ($budget_period_id) = @_;
 	my $dbh = C4::Context->dbh;
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/suggestion/suggestion.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/suggestion/suggestion.tt
index 38d784f..13fc225 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/suggestion/suggestion.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/suggestion/suggestion.tt
@@ -87,6 +87,57 @@ $(document).ready(function() { calcNewsuggTotal(); });
 </script>
 [% END %]
 <script type="text/javascript" src="[% themelang %]/js/acq.js"></script>
+<script type="text/javascript">
+// <![CDATA[
+    $(document).ready(function() {
+    $('#sub_budgetid').hide();
+    $('#su_sub_budgetid').hide();
+    $("#budgetid").val("").attr('selected', 'selected');
+           
+        $('#budgetid').change(function() {
+        var budget= $('#budgetid').val();
+                
+        if (budget ==""){
+        $('#sub_budgetid').hide();
+        $('#su_sub_budgetid').hide();
+        $('#sub_budgetid option:visible').hide();
+        $('#su_sub_budgetid option:visible').hide();
+        $("#sub_budgetid").val("").attr('selected', 'selected');
+        $("#su_sub_budgetid").val("").attr('selected', 'selected');
+        }
+        else {
+        $('#sub_budgetid').show();
+        $('#sub_budgetid option:visible').hide();
+        $('.'+budget).show();
+        $('#a').show();
+        $("#sub_budgetid").val("").attr('selected', 'selected');
+        $('#su_sub_budgetid option:visible').hide();
+        $('#su_sub_budgetid').hide();
+        }
+        });
+
+        $('#sub_budgetid').change(function() {
+        var subbudget= $('#sub_budgetid').val();
+        
+        if (subbudget ==""){
+        $('#su_sub_budgetid').hide();
+        $("#su_sub_budgetid").val("").attr('selected', 'selected');
+        $("#sub_budgetid").val("").attr('selected', 'selected');
+        
+        }
+        else {
+        $('#su_sub_budgetid').show();
+        $('#su_sub_budgetid option:visible').hide();
+        $('.'+subbudget).show();
+        $('#b').show();
+        $("#su_sub_budgetid").val("").attr('selected', 'selected');
+        }
+        });
+    });
+// ]]>
+</script>
+
+
 </head>
 <body>
 [% INCLUDE 'header.inc' %]
@@ -169,6 +220,14 @@ $(document).ready(function() { calcNewsuggTotal(); });
                 <option value="">Any</option>[% FOREACH budgetsloo IN budgetsloop %]
                 [% IF ( budgetsloo.selected ) %]<option value="[% budgetsloo.budget_id %]" selected="selected">[% budgetsloo.budget_name %]</option>[% ELSE %]<option value="[% budgetsloo.budget_id %]">[% budgetsloo.budget_name %]</option>[% END %][% END %]
             </select>
+            <select name="sub_budgetid" id="sub_budgetid" >
+                <option value="" id="a">Any</option>[% FOREACH sub_budget_loo IN sub_budgetsloop %]
+                [% IF ( sub_budgetloo.selected ) %]<option value="[% sub_budget_loo.budget_id %]" selected="selected" class="[% sub_budget_loo.budget_parent_id %]" style ="display : none;">[% sub_budget_loo.budget_name %]</option>[% ELSE %]<option value="[% sub_budget_loo.budget_id %]" class="[% sub_budget_loo.budget_parent_id %]" style ="display : none;" >[% sub_budget_loo.budget_name %]</option>[% END %][% END %]
+            </select>
+            <select name="su_sub_budgetid" id="su_sub_budgetid">
+                <option value="" id="b">Any</option>[% FOREACH su_sub_budgetsloo IN su_sub_budgetsloop %]
+                [% IF ( su_sub_budgetsloo.selected ) %]<option value="[% su_sub_budgetsloo.budget_id %]" selected="selected" class="[% su_sub_budgetsloo.budget_parent_id %]" style ="display : none;">[% su_sub_budgetsloo.budget_name %]</option>[% ELSE %]<option value="[% su_sub_budgetsloo.budget_id %]" class="[% su_sub_budgetsloo.budget_parent_id %]" style ="display : none;">[% su_sub_budgetsloo.budget_name %]</option>[% END %][% END %]
+            </select>
 		</li><li><label for="quantity">Copies:</label>
 			<input type="text" size="10" id="quantity" name="quantity" value="[% quantity %]" onchange="calcNewsuggTotal();" />
                 </li><li><label for="currency">Currency:</label>
diff --git a/suggestion/suggestion.pl b/suggestion/suggestion.pl
index d00b632..0ea370d 100755
--- a/suggestion/suggestion.pl
+++ b/suggestion/suggestion.pl
@@ -84,6 +84,10 @@ my $tabcode    = $input->param('tabcode');
 my $suggestion_ref  = $input->Vars;
 
 delete $$suggestion_ref{$_} foreach qw( suggestedbyme op displayby tabcode edit_field );
+
+$suggestion_ref->{'budgetid'} = $suggestion_ref->{'su_sub_budgetid'}||$suggestion_ref->{'sub_budgetid'}||$suggestion_ref->{'budgetid'};
+delete $$suggestion_ref{$_} foreach qw( suggestedbyme op displayby tabcode edit_field sub_budgetid su_sub_budgetid);
+
 foreach (keys %$suggestion_ref){
     delete $$suggestion_ref{$_} if (!$$suggestion_ref{$_} && ($op eq 'else' || $op eq 'change'));
 }
@@ -288,20 +292,14 @@ my $patron_reason_loop = GetAuthorisedValues("OPAC_SUG",$$suggestion_ref{'patron
 $template->param(patron_reason_loop=>$patron_reason_loop);
 
 #Budgets management
-my $budgets = [];
-if ($branchfilter) {
-    my $searchbudgets = { budget_branchcode => $branchfilter };
-    $budgets = GetBudgets($searchbudgets);
-} else {
-    $budgets = GetBudgets(undef);
-}
+my $bud = GetBudgetParent();
+my $subbud = GetBudgetFirstChild();
+my $susubbud = GetBudgetSecondChild();
 
-foreach my $budget ( @{$budgets} ) {
-## Please see file perltidy.ERR
-    $budget->{'selected'}=1 if ($$suggestion_ref{'budgetid'} && $budget->{'budget_id'} eq $$suggestion_ref{'budgetid'})
-};
+$template->param( budgetsloop => $bud );
+$template->param( sub_budgetsloop => $subbud );
+$template->param( su_sub_budgetsloop => $susubbud );
 
-$template->param( budgetsloop => $budgets);
 $template->param( "statusselected_$$suggestion_ref{'STATUS'}" =>1);
 
 # get currencies and rates
-- 
1.7.4.1