From 34f0021732f3e3de2b2be015f0e3aee463ce670c Mon Sep 17 00:00:00 2001
From: jeremy breuillard <jeremy.breuillard@biblibre.com>
Date: Wed, 22 Dec 2021 11:42:07 +0100
Subject: [PATCH] MT 28590: SQL Koha reports and variable of database table <<
 name|table_name>>

'Insert runtime parameter' has now more options for the SQL reports : 'cash register', 'debit types' and 'credit types'

Test plan:
1)Home > Reports > Create from SQL
2)Click on 'Insert runtime parameter' and notice the current options
3)Apply patch and repeat 2)
4)New parameters are now available
5)A simple SQL request to try 'credit_types' option : SELECT * FROM account_credit_types WHERE code = <<Credit types|credit_types>>

https://bugs.koha-community.org/show_bug.cgi?id=29767
---
 C4/Reports/Guided.pm                          |  5 +++-
 .../modules/reports/guided_reports_start.tt   | 24 ++++++++++++++++-
 reports/guided_reports.pl                     | 27 ++++++++++++++++++-
 t/db_dependent/Reports/Guided.t               |  5 +++-
 4 files changed, 57 insertions(+), 4 deletions(-)

diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm
index 8a6964446b..310b3e9145 100644
--- a/C4/Reports/Guided.pm
+++ b/C4/Reports/Guided.pm
@@ -936,7 +936,10 @@ sub GetReservedAuthorisedValues {
                               'itemtypes',
                               'cn_source',
                               'categorycode',
-                              'biblio_framework' );
+                              'biblio_framework',
+                              'cash_registers',
+                              'debit_types',
+                              'credit_types' );
 
    return \%reserved_authorised_values;
 }
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 18bea35dba..f209f94dc1 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
@@ -1207,7 +1207,6 @@
                                     <span class="required">Required</span>
                                 </li>
                                 [% PROCESS group_and_subgroup_selection %]
-
                                 [% IF (public) %]
                                     <li>
                                         <label for="public">Report is public:</label>
@@ -1632,6 +1631,26 @@
                     $("#paramLabel").val( _("Patron category") );
                     $("#param_category").val("categorycode");
                     break;
+
+                case "insertCashregister":
+                    modalTitle.text( _("Insert cash register parameter") );
+                    $("#paramLabel").val( _("Cash register") );
+                    $("#param_category").val("cash_registers");
+                    break;
+                case "insertDebittypes":
+                    modalTitle.text( _("Insert debit types parameter") );
+                    $("#paramLabel").val( _("Debit types") );
+                    $("#param_category").val("debit_types");
+                    break;
+                case "insertCredittypes":
+                    modalTitle.text( _("Insert credit types parameter") );
+                    $("#paramLabel").val( _("Credit types") );
+                    $("#param_category").val("credit_types");
+                    break;
+
+                
+
+
                 case "insertList":
                     modalTitle.text( _("Insert list parameter") );
                     $("#paramLabel").val( _("List of values") );
@@ -2387,6 +2406,9 @@
             <li><a href="#" class="insertParam" id="insertBranches">Libraries</a></li>
             <li><a href="#" class="insertParam" id="insertList">List</a></li>
             <li><a href="#" class="insertParam" id="insertCategorycode">Patron categories</a></li>
+            <li><a href="#" class="insertParam" id="insertCashregister">Cash register</a></li>
+            <li><a href="#" class="insertParam" id="insertDebittypes">Debit types</a></li>
+            <li><a href="#" class="insertParam" id="insertCredittypes">Credit types</a></li>
             <li><a href="#" class="insertParam" id="insertText">Text field</a></li>
         </ul>
     </div>
diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index b081954df5..1cc093b0ea 100755
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -773,6 +773,30 @@ elsif ($phase eq 'Run this report'){
                         %authorised_lib = map { $_->categorycode => $_->description } @patron_categories;
                         push @authorised_values, $_->categorycode for @patron_categories;
                     }
+                    elsif ( $authorised_value eq "cash_registers" ) {
+                        my $sth = $dbh->prepare("SELECT id, name FROM cash_registers ORDER BY description");
+                        $sth->execute;
+                        while ( my ( $id, $name ) = $sth->fetchrow_array ) {
+                            push @authorised_values, $id;
+                            $authorised_lib{$id} = $name;
+                        }
+                    }
+                    elsif ( $authorised_value eq "debit_types" ) {
+                        my $sth = $dbh->prepare("SELECT code, description FROM account_debit_types ORDER BY code");
+                        $sth->execute;
+                        while ( my ( $code, $description ) = $sth->fetchrow_array ) {
+                           push @authorised_values, $code;
+                           $authorised_lib{$code} = $description;
+                        }
+                    }
+                    elsif ( $authorised_value eq "credit_types" ) {
+                        my $sth = $dbh->prepare("SELECT code, description FROM account_credit_types ORDER BY code");
+                        $sth->execute;
+                        while ( my ( $code, $description ) = $sth->fetchrow_array ) {
+                           push @authorised_values, $code;
+                           $authorised_lib{$code} = $description;
+                        }
+                    }
                     else {
                         if ( Koha::AuthorisedValues->search({ category => $authorised_value })->count ) {
                             my $query = '
@@ -812,6 +836,7 @@ elsif ($phase eq 'Run this report'){
 
                 push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value_all, 'include_all' => $all };
             }
+
             $template->param('sql'         => $sql,
                             'name'         => $name,
                             'sql_params'   => \@tmpl_parameters,
@@ -875,7 +900,7 @@ elsif ($phase eq 'Run this report'){
         push @errors, { no_sql_for_id => $report_id };
     }
 }
-
+                            
 elsif ($phase eq 'Export'){
 
 	# export results to tab separated text or CSV
diff --git a/t/db_dependent/Reports/Guided.t b/t/db_dependent/Reports/Guided.t
index 705bd7f68a..28ef81dbfd 100755
--- a/t/db_dependent/Reports/Guided.t
+++ b/t/db_dependent/Reports/Guided.t
@@ -118,6 +118,9 @@ subtest 'GetReservedAuthorisedValues' => sub {
         'categorycode' => 1,
         'biblio_framework' => 1,
         'list' => 1,
+        'cash_registers' => 1,
+        'debit_types' => 1,
+        'credit_types' => 1
     );
 
     my $reserved_authorised_values = GetReservedAuthorisedValues();
@@ -126,7 +129,7 @@ subtest 'GetReservedAuthorisedValues' => sub {
 };
 
 subtest 'IsAuthorisedValueValid' => sub {
-    plan tests => 9;
+    plan tests => 12;
     ok( IsAuthorisedValueValid('LOC'),
         'User defined authorised value category is valid');
 
-- 
2.17.1