From d6682bdd073b7d2aea9bab18ba6dec07471c7a8e Mon Sep 17 00:00:00 2001 From: jeremy breuillard Date: Wed, 22 Dec 2021 11:42:07 +0100 Subject: [PATCH] Bug 29767: 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 = <> --- 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 @@ Required [% PROCESS group_and_subgroup_selection %] - [% IF (public) %]
  • @@ -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 @@
  • Libraries
  • List
  • Patron categories
  • +
  • Cash register
  • +
  • Debit types
  • +
  • Credit types
  • Text field
  • 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