From 45f1f9cacc10179c69ddd00e6c1496b837d212a7 Mon Sep 17 00:00:00 2001
From: Paul Poulain <paul.poulain@biblibre.com>
Date: Mon, 1 Aug 2011 17:19:08 +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                           |   11 ++++
 3 files changed, 125 insertions(+), 0 deletions(-)

diff --git a/C4/Budgets.pm b/C4/Budgets.pm
index d516a12..9ac1ff9 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..5508952 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'));
 }
@@ -300,8 +304,15 @@ foreach my $budget ( @{$budgets} ) {
 ## Please see file perltidy.ERR
     $budget->{'selected'}=1 if ($$suggestion_ref{'budgetid'} && $budget->{'budget_id'} eq $$suggestion_ref{'budgetid'})
 };
+my $bud = GetBudgetParent();
+my $subbud = GetBudgetFirstChild();
+my $susubbud = GetBudgetSecondChild();
 
 $template->param( budgetsloop => $budgets);
+$template->param( budgetsloop => $bud );
+$template->param( sub_budgetsloop => $subbud );
+$template->param( su_sub_budgetsloop => $susubbud );
+
 $template->param( "statusselected_$$suggestion_ref{'STATUS'}" =>1);
 
 # get currencies and rates
-- 
1.7.4.1