From 521c9294cc726c4f494d0be2e0ba3c4c4effc021 Mon Sep 17 00:00:00 2001
From: Brendan Lawlor <blawlor@clamsnet.org>
Date: Tue, 20 Feb 2024 15:37:40 +0000
Subject: [PATCH] Bug 35746: add multiselect to report dropdowns

Rebased for changes to guided_reports_start.tt

This patch adds the ability to use multi select in report dropdowns.

It uses syntax similar to using a list and when using the :all option
WHERE i.homebranch in <<Select libraries|brnaches:in>>

Test plan:
1. Create an SQL report with with new syntax for multi select
    SELECT
    i.homebranch,
    count(*)
    FROM items i
    WHERE i.homebranch in <<Select libraries|branches:in>>
    GROUP BY i.homebranch
2. Save the report
3. Note that you get the error
    The authorized value category (branches:in) you selected does not exist.
4. Apply the patch and repeat steps 1 and 2
5. Note the report saves successfully
6. Run the report
7. Note the select dropdown is now a multiselect
8. Run the report with multiple selections
10. Click show SQL and note that multi select parameters get inserted into the query like:
     WHERE i.homebranch in ('CPL', 'FFL', 'FPL')
11. Test other types of queries with multiple multi selects and lists etc

Sponsored-by: CLAMS

Signed-off-by: Andrew Fuerste-Henry <andrewfh@dubcolib.org>
Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
---
 C4/Reports/Guided.pm                          |  2 +-
 Koha/Report.pm                                |  2 +-
 .../modules/reports/guided_reports_start.tt   | 24 +++++++++++++++++--
 reports/guided_reports.pl                     | 11 +++++++--
 4 files changed, 33 insertions(+), 6 deletions(-)

diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm
index a55cd17743..1d8b195b3c 100644
--- a/C4/Reports/Guided.pm
+++ b/C4/Reports/Guided.pm
@@ -974,7 +974,7 @@ sub GetParametersFromSQL {
 
     for ( my $i = 0; $i < ($#split/2) ; $i++ ) {
         my ($name,$authval) = split(/\|/,$split[$i*2+1]);
-        $authval =~ s/\:all$// if $authval;
+        $authval =~ s/\:all$|\:in$// if $authval;
         push @sql_parameters, { 'name' => $name, 'authval' => $authval };
     }
 
diff --git a/Koha/Report.pm b/Koha/Report.pm
index 55daece836..8c9bd3b523 100644
--- a/Koha/Report.pm
+++ b/Koha/Report.pm
@@ -169,7 +169,7 @@ sub prep_report {
         #        }
         #    ) if $quoted;
         #}
-        unless ( $split[ $i * 2 + 1 ] =~ /\|\s*list\s*$/ && $quoted ) {
+        unless ( $split[ $i * 2 + 1 ] =~ /\|\s*list\s*$|\s*\:in\s*$/ && $quoted ) {
             $quoted = C4::Context->dbh->quote($quoted);
         }
         else {
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
index 157c538b50..f79005ee58 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt
@@ -849,7 +849,7 @@
                             <a href="/cgi-bin/koha/reports/guided_reports.pl?op=edit_formid=[% id | uri %]" class="btn btn-primary">Edit SQL</a>
                         </fieldset>
                     [% ELSE #  IF ( auth_val_error ) %]
-                        <form method="get" action='/cgi-bin/koha/reports/guided_reports.pl'>
+                        <form method="get" action='/cgi-bin/koha/reports/guided_reports.pl' id='report_param_form'>
                             <input type='hidden' name='id' value="[% id | html %]" />
                             <h1>Enter parameters for report [% name | html %]:</h1>
                             [% IF ( notes ) %]
@@ -876,10 +876,13 @@
                                         [% ELSE %]
                                             <li>
                                                 <label for="sql_params_[% sql_param.labelid | html %]">[% sql_param.entry | html %]:</label>
-                                                <select name="[%- sql_param.input.name | html -%]" tabindex="1"  id="[%- sql_param.input.id | html -%]">
+                                                <select name="[%- sql_param.input.name | html -%]" tabindex="1"  id="[%- sql_param.input.id | html -%]" [%- sql_param.select_multiple | html -%]>
                                                     [% IF (sql_param.include_all) %]
                                                         <option value="%">All</option>
                                                     [% END %]
+                                                    [% IF (sql_param.select_multiple) %]
+                                                        <option value="null" hidden></option>
+                                                    [% END %]
                                                     [% FOREACH value IN sql_param.input.values %]
                                                         <option value="[%- value | html -%]">[%- sql_param.input.labels.$value | html -%]</option>
                                                     [% END %]
@@ -1541,6 +1544,23 @@
 
     <script>
 
+        //  if the report param form has multiselects override default form submission
+        if( $('#report_param_form').find('select[multiple]').length ) {
+            $('#report_param_form').find('select[multiple]').each( function (i) {
+                $(this).on('change', function() {
+                    var $selected = $(this).val().join('\n');
+                    $(this).find('option:first').val($selected);
+                });
+            });
+
+            $('#report_param_form').on('submit', function(e) {
+                $('#report_param_form').find('select[multiple]').each( function (i) {
+                    var $selected = $('option:first', this).val();
+                    $(this).val($selected);
+                });
+            });
+        }
+
         function hide_bar_element() {
             $('#chart-column-horizontal').hide()
             $('.chart-column-group').each(function( index ) {
diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index 3196731163..57b7abd06e 100755
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -798,7 +798,14 @@ if ($op eq 'run'){
                 if( defined $uniq_params{$text.$sep.$authorised_value_all} ){
                     next;
                 } else { $uniq_params{$text.$sep.$authorised_value_all} = "$i"; }
-                my ($authorised_value, $all) = split /:/, $authorised_value_all;
+                my ($authorised_value, $param_options) = split /:/, $authorised_value_all;
+                my $all;
+                my $multiple;
+                if ( $param_options eq "all" ) {
+                    $all = "all";
+                } elsif ( $param_options eq "in" ) {
+                    $multiple = "multiple";
+                }
                 my $input;
                 my $labelid;
                 if ( not defined $authorised_value ) {
@@ -917,7 +924,7 @@ if ($op eq 'run'){
                     };
                 }
 
-                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value_all, 'include_all' => $all };
+                push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value_all, 'include_all' => $all, 'select_multiple' => $multiple };
             }
             $template->param('sql'         => $sql,
                             'name'         => $name,
-- 
2.39.5