Lines 23-31
use Text::CSV::Encoded;
Link Here
|
23 |
use Encode qw( decode ); |
23 |
use Encode qw( decode ); |
24 |
use URI::Escape; |
24 |
use URI::Escape; |
25 |
use File::Temp; |
25 |
use File::Temp; |
26 |
use C4::Reports::Guided qw( delete_report get_report_areas convert_sql update_sql get_saved_reports get_results ValidateSQLParameters format_results get_report_types get_columns get_from_dictionary get_criteria build_query save_report execute_query nb_rows get_report_groups ); |
26 |
use C4::Reports::Guided |
|
|
27 |
qw( delete_report get_report_areas convert_sql update_sql get_saved_reports get_results ValidateSQLParameters format_results get_report_types get_columns get_from_dictionary get_criteria build_query save_report execute_query nb_rows get_report_groups ); |
27 |
use Koha::Reports; |
28 |
use Koha::Reports; |
28 |
use C4::Auth qw( get_template_and_user get_session ); |
29 |
use C4::Auth qw( get_template_and_user get_session ); |
29 |
use C4::Output qw( pagination_bar output_html_with_http_headers ); |
30 |
use C4::Output qw( pagination_bar output_html_with_http_headers ); |
30 |
use C4::Context; |
31 |
use C4::Context; |
31 |
use Koha::Caches; |
32 |
use Koha::Caches; |
Lines 39-45
use Koha::SharedContent;
Link Here
|
39 |
use Koha::Util::OpenDocument qw( generate_ods ); |
40 |
use Koha::Util::OpenDocument qw( generate_ods ); |
40 |
use Koha::Notice::Templates; |
41 |
use Koha::Notice::Templates; |
41 |
use Koha::TemplateUtils qw( process_tt ); |
42 |
use Koha::TemplateUtils qw( process_tt ); |
42 |
use C4::ClassSource qw( GetClassSources ); |
43 |
use C4::ClassSource qw( GetClassSources ); |
43 |
use HTML::Restrict; |
44 |
use HTML::Restrict; |
44 |
|
45 |
|
45 |
=head1 NAME |
46 |
=head1 NAME |
Lines 52-86
Script to control the guided report creation
Link Here
|
52 |
|
53 |
|
53 |
=cut |
54 |
=cut |
54 |
|
55 |
|
55 |
my $input = CGI->new; |
56 |
my $input = CGI->new; |
56 |
my $usecache = Koha::Caches->get_instance->memcached_cache; |
57 |
my $usecache = Koha::Caches->get_instance->memcached_cache; |
57 |
|
58 |
|
58 |
my $op = $input->param('op') // ''; |
59 |
my $op = $input->param('op') // ''; |
59 |
my $flagsrequired; |
60 |
my $flagsrequired; |
60 |
if ( ( $op eq 'add_form' ) || ( $op eq 'add_form_sql' ) || ( $op eq 'edit_form' ) |
61 |
if ( ( $op eq 'add_form' ) |
61 |
|| ( $op eq 'duplicate' ) ) { |
62 |
|| ( $op eq 'add_form_sql' ) |
|
|
63 |
|| ( $op eq 'edit_form' ) |
64 |
|| ( $op eq 'duplicate' ) ) |
65 |
{ |
62 |
$flagsrequired = 'create_reports'; |
66 |
$flagsrequired = 'create_reports'; |
63 |
} |
67 |
} elsif ( $op eq 'list' ) { |
64 |
elsif ( $op eq 'list' ) { |
|
|
65 |
$flagsrequired = 'execute_reports'; |
68 |
$flagsrequired = 'execute_reports'; |
66 |
} |
69 |
} elsif ( $op eq 'delete' ) { |
67 |
elsif ( $op eq 'delete' ) { |
|
|
68 |
$flagsrequired = 'delete_reports'; |
70 |
$flagsrequired = 'delete_reports'; |
69 |
} |
71 |
} else { |
70 |
else { |
|
|
71 |
$flagsrequired = '*'; |
72 |
$flagsrequired = '*'; |
72 |
} |
73 |
} |
73 |
|
74 |
|
74 |
my ( $template, $borrowernumber, $cookie ) = get_template_and_user( |
75 |
my ( $template, $borrowernumber, $cookie ) = get_template_and_user( |
75 |
{ |
76 |
{ |
76 |
template_name => "reports/guided_reports_start.tt", |
77 |
template_name => "reports/guided_reports_start.tt", |
77 |
query => $input, |
78 |
query => $input, |
78 |
type => "intranet", |
79 |
type => "intranet", |
79 |
flagsrequired => { reports => $flagsrequired }, |
80 |
flagsrequired => { reports => $flagsrequired }, |
80 |
} |
81 |
} |
81 |
); |
82 |
); |
82 |
my $session_id = $input->cookie('CGISESSID'); |
83 |
my $session_id = $input->cookie('CGISESSID'); |
83 |
my $session = $session_id ? get_session($session_id) : undef; |
84 |
my $session = $session_id ? get_session($session_id) : undef; |
84 |
|
85 |
|
85 |
$template->param( templates => Koha::Notice::Templates->search( { module => 'report' } ) ); |
86 |
$template->param( templates => Koha::Notice::Templates->search( { module => 'report' } ) ); |
86 |
|
87 |
|
Lines 88-106
my $filter;
Link Here
|
88 |
if ( $input->param("filter_set") or $input->param('clear_filters') ) { |
89 |
if ( $input->param("filter_set") or $input->param('clear_filters') ) { |
89 |
$filter = {}; |
90 |
$filter = {}; |
90 |
$filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword group subgroup/; |
91 |
$filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword group subgroup/; |
91 |
$session->param('report_filter', $filter) if $session; |
92 |
$session->param( 'report_filter', $filter ) if $session; |
92 |
$template->param( 'filter_set' => 1 ); |
93 |
$template->param( 'filter_set' => 1 ); |
93 |
} |
94 |
} elsif ( $session and not $input->param('clear_filters') ) { |
94 |
elsif ($session and not $input->param('clear_filters')) { |
|
|
95 |
$filter = $session->param('report_filter'); |
95 |
$filter = $session->param('report_filter'); |
96 |
} |
96 |
} |
97 |
|
97 |
|
98 |
my @errors = (); |
98 |
my @errors = (); |
99 |
if ( !$op ) { |
99 |
if ( !$op ) { |
100 |
$template->param( 'start' => 1 ); |
100 |
$template->param( 'start' => 1 ); |
|
|
101 |
|
101 |
# show welcome page |
102 |
# show welcome page |
102 |
} |
103 |
} elsif ( $op eq 'add_form' ) { |
103 |
elsif ( $op eq 'add_form' ) { |
104 |
|
104 |
# build a new report |
105 |
# build a new report |
105 |
$template->param( 'build1' => 1 ); |
106 |
$template->param( 'build1' => 1 ); |
106 |
$template->param( |
107 |
$template->param( |
Lines 109-197
elsif ( $op eq 'add_form' ) {
Link Here
|
109 |
'cache_expiry' => 300, |
110 |
'cache_expiry' => 300, |
110 |
'public' => '0', |
111 |
'public' => '0', |
111 |
); |
112 |
); |
112 |
} |
113 |
} elsif ( $op eq 'cud-delete' ) { |
113 |
elsif ( $op eq 'cud-delete') { |
|
|
114 |
my @ids = $input->multi_param('id'); |
114 |
my @ids = $input->multi_param('id'); |
115 |
delete_report( @ids ); |
115 |
delete_report(@ids); |
116 |
$op = 'list'; |
116 |
$op = 'list'; |
117 |
} |
|
|
118 |
|
117 |
|
119 |
elsif ( $op eq 'show'){ |
118 |
} elsif ( $op eq 'show' ) { |
120 |
|
119 |
|
121 |
my $id = $input->param('id'); |
120 |
my $id = $input->param('id'); |
122 |
my $report = Koha::Reports->find($id); |
121 |
my $report = Koha::Reports->find($id); |
123 |
$template->param( |
122 |
$template->param( |
124 |
'id' => $id, |
123 |
'id' => $id, |
125 |
'reportname' => $report->report_name, |
124 |
'reportname' => $report->report_name, |
126 |
'notes' => $report->notes, |
125 |
'notes' => $report->notes, |
127 |
'sql' => $report->savedsql, |
126 |
'sql' => $report->savedsql, |
128 |
'showsql' => 1, |
127 |
'showsql' => 1, |
129 |
'mana_success' => scalar $input->param('mana_success'), |
128 |
'mana_success' => scalar $input->param('mana_success'), |
130 |
'mana_id' => $report->{mana_id}, |
129 |
'mana_id' => $report->{mana_id}, |
131 |
'mana_comments' => $report->{comments} |
130 |
'mana_comments' => $report->{comments} |
132 |
); |
131 |
); |
133 |
} |
|
|
134 |
|
132 |
|
135 |
elsif ( $op eq 'edit_form'){ |
133 |
} elsif ( $op eq 'edit_form' ) { |
136 |
my $id = $input->param('id'); |
134 |
my $id = $input->param('id'); |
137 |
my $report = Koha::Reports->find($id); |
135 |
my $report = Koha::Reports->find($id); |
138 |
my $group = $report->report_group; |
136 |
my $group = $report->report_group; |
139 |
my $subgroup = $report->report_subgroup; |
137 |
my $subgroup = $report->report_subgroup; |
140 |
my $tables = get_tables(); |
138 |
my $tables = get_tables(); |
141 |
$template->param( |
139 |
$template->param( |
142 |
'sql' => $report->savedsql, |
140 |
'sql' => $report->savedsql, |
143 |
'reportname' => $report->report_name, |
141 |
'reportname' => $report->report_name, |
144 |
'groups_with_subgroups' => groups_with_subgroups($group, $subgroup), |
142 |
'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ), |
145 |
'notes' => $report->notes, |
143 |
'notes' => $report->notes, |
146 |
'id' => $id, |
144 |
'id' => $id, |
147 |
'cache_expiry' => $report->cache_expiry, |
145 |
'cache_expiry' => $report->cache_expiry, |
148 |
'public' => $report->public, |
146 |
'public' => $report->public, |
149 |
'usecache' => $usecache, |
147 |
'usecache' => $usecache, |
150 |
'editsql' => 1, |
148 |
'editsql' => 1, |
151 |
'mana_id' => $report->{mana_id}, |
149 |
'mana_id' => $report->{mana_id}, |
152 |
'mana_comments' => $report->{comments}, |
150 |
'mana_comments' => $report->{comments}, |
153 |
'tables' => $tables |
151 |
'tables' => $tables |
154 |
); |
152 |
); |
155 |
} |
|
|
156 |
|
153 |
|
157 |
elsif ( $op eq 'cud-update_sql' || $op eq 'cud-update_and_run_sql' ){ |
154 |
} elsif ( $op eq 'cud-update_sql' || $op eq 'cud-update_and_run_sql' ) { |
158 |
my $id = $input->param('id'); |
155 |
my $id = $input->param('id'); |
159 |
my $sql = $input->param('sql'); |
156 |
my $sql = $input->param('sql'); |
160 |
my $reportname = $input->param('reportname'); |
157 |
my $reportname = $input->param('reportname'); |
161 |
my $group = $input->param('group'); |
158 |
my $group = $input->param('group'); |
162 |
my $subgroup = $input->param('subgroup'); |
159 |
my $subgroup = $input->param('subgroup'); |
163 |
my $notes = $input->param('notes'); |
160 |
my $notes = $input->param('notes'); |
164 |
my $cache_expiry = $input->param('cache_expiry'); |
161 |
my $cache_expiry = $input->param('cache_expiry'); |
165 |
my $cache_expiry_units = $input->param('cache_expiry_units'); |
162 |
my $cache_expiry_units = $input->param('cache_expiry_units'); |
166 |
my $public = $input->param('public'); |
163 |
my $public = $input->param('public'); |
167 |
my $save_anyway = $input->param('save_anyway'); |
164 |
my $save_anyway = $input->param('save_anyway'); |
168 |
my @errors; |
165 |
my @errors; |
169 |
my $tables = get_tables(); |
166 |
my $tables = get_tables(); |
170 |
|
167 |
|
171 |
# if we have the units, then we came from creating a report from SQL and thus need to handle converting units |
168 |
# if we have the units, then we came from creating a report from SQL and thus need to handle converting units |
172 |
if( $cache_expiry_units ){ |
169 |
if ($cache_expiry_units) { |
173 |
if( $cache_expiry_units eq "minutes" ){ |
170 |
if ( $cache_expiry_units eq "minutes" ) { |
174 |
$cache_expiry *= 60; |
171 |
$cache_expiry *= 60; |
175 |
} elsif( $cache_expiry_units eq "hours" ){ |
172 |
} elsif ( $cache_expiry_units eq "hours" ) { |
176 |
$cache_expiry *= 3600; # 60 * 60 |
173 |
$cache_expiry *= 3600; # 60 * 60 |
177 |
} elsif( $cache_expiry_units eq "days" ){ |
174 |
} elsif ( $cache_expiry_units eq "days" ) { |
178 |
$cache_expiry *= 86400; # 60 * 60 * 24 |
175 |
$cache_expiry *= 86400; # 60 * 60 * 24 |
179 |
} |
176 |
} |
180 |
} |
177 |
} |
|
|
178 |
|
181 |
# check $cache_expiry isn't too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp |
179 |
# check $cache_expiry isn't too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp |
182 |
if( $cache_expiry >= 2592000 ){ |
180 |
if ( $cache_expiry >= 2592000 ) { |
183 |
push @errors, {cache_expiry => $cache_expiry}; |
181 |
push @errors, { cache_expiry => $cache_expiry }; |
184 |
} |
182 |
} |
185 |
|
183 |
|
186 |
create_non_existing_group_and_subgroup($input, $group, $subgroup); |
184 |
create_non_existing_group_and_subgroup( $input, $group, $subgroup ); |
187 |
|
185 |
|
188 |
my ( $is_sql_valid, $validation_errors ) = Koha::Report->new({ savedsql => $sql })->is_sql_valid; |
186 |
my ( $is_sql_valid, $validation_errors ) = Koha::Report->new( { savedsql => $sql } )->is_sql_valid; |
189 |
push(@errors, @$validation_errors) unless $is_sql_valid; |
187 |
push( @errors, @$validation_errors ) unless $is_sql_valid; |
190 |
|
188 |
|
191 |
if (@errors) { |
189 |
if (@errors) { |
192 |
$template->param( |
190 |
$template->param( |
193 |
'errors' => \@errors, |
191 |
'errors' => \@errors, |
194 |
'sql' => $sql, |
192 |
'sql' => $sql, |
195 |
); |
193 |
); |
196 |
} else { |
194 |
} else { |
197 |
|
195 |
|
Lines 199-237
elsif ( $op eq 'cud-update_sql' || $op eq 'cud-update_and_run_sql' ){
Link Here
|
199 |
my $problematic_authvals = ValidateSQLParameters($sql); |
197 |
my $problematic_authvals = ValidateSQLParameters($sql); |
200 |
|
198 |
|
201 |
if ( scalar @$problematic_authvals > 0 && not $save_anyway ) { |
199 |
if ( scalar @$problematic_authvals > 0 && not $save_anyway ) { |
|
|
200 |
|
202 |
# There's at least one problematic parameter, report to the |
201 |
# There's at least one problematic parameter, report to the |
203 |
# GUI and provide all user input for further actions |
202 |
# GUI and provide all user input for further actions |
204 |
$template->param( |
203 |
$template->param( |
205 |
'id' => $id, |
204 |
'id' => $id, |
206 |
'sql' => $sql, |
205 |
'sql' => $sql, |
207 |
'reportname' => $reportname, |
206 |
'reportname' => $reportname, |
208 |
'group' => $group, |
207 |
'group' => $group, |
209 |
'subgroup' => $subgroup, |
208 |
'subgroup' => $subgroup, |
210 |
'notes' => $notes, |
209 |
'notes' => $notes, |
211 |
'public' => $public, |
210 |
'public' => $public, |
212 |
'problematic_authvals' => $problematic_authvals, |
211 |
'problematic_authvals' => $problematic_authvals, |
213 |
'warn_authval_problem' => 1, |
212 |
'warn_authval_problem' => 1, |
214 |
'phase_update' => 1, |
213 |
'phase_update' => 1, |
215 |
); |
214 |
); |
216 |
|
215 |
|
217 |
} else { |
216 |
} else { |
|
|
217 |
|
218 |
# No params problem found or asked to save anyway |
218 |
# No params problem found or asked to save anyway |
219 |
update_sql( $id, { |
219 |
update_sql( |
220 |
sql => $sql, |
220 |
$id, |
221 |
name => $reportname, |
221 |
{ |
222 |
group => $group, |
222 |
sql => $sql, |
223 |
subgroup => $subgroup, |
223 |
name => $reportname, |
224 |
notes => $notes, |
224 |
group => $group, |
225 |
public => $public, |
225 |
subgroup => $subgroup, |
|
|
226 |
notes => $notes, |
227 |
public => $public, |
226 |
cache_expiry => $cache_expiry, |
228 |
cache_expiry => $cache_expiry, |
227 |
} ); |
229 |
} |
|
|
230 |
); |
228 |
$template->param( |
231 |
$template->param( |
229 |
'save_successful' => 1, |
232 |
'save_successful' => 1, |
230 |
'reportname' => $reportname, |
233 |
'reportname' => $reportname, |
231 |
'id' => $id, |
234 |
'id' => $id, |
232 |
'editsql' => 1, |
235 |
'editsql' => 1, |
233 |
'sql' => $sql, |
236 |
'sql' => $sql, |
234 |
'groups_with_subgroups' => groups_with_subgroups($group, $subgroup), |
237 |
'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ), |
235 |
'notes' => $notes, |
238 |
'notes' => $notes, |
236 |
'cache_expiry' => $cache_expiry, |
239 |
'cache_expiry' => $cache_expiry, |
237 |
'public' => $public, |
240 |
'public' => $public, |
Lines 240-248
elsif ( $op eq 'cud-update_sql' || $op eq 'cud-update_and_run_sql' ){
Link Here
|
240 |
); |
243 |
); |
241 |
logaction( "REPORTS", "MODIFY", $id, "$reportname | $sql" ) if C4::Context->preference("ReportsLog"); |
244 |
logaction( "REPORTS", "MODIFY", $id, "$reportname | $sql" ) if C4::Context->preference("ReportsLog"); |
242 |
} |
245 |
} |
243 |
if ( $usecache ) { |
246 |
if ($usecache) { |
244 |
$template->param( |
247 |
$template->param( |
245 |
cache_expiry => $cache_expiry, |
248 |
cache_expiry => $cache_expiry, |
246 |
cache_expiry_units => $cache_expiry_units, |
249 |
cache_expiry_units => $cache_expiry_units, |
247 |
); |
250 |
); |
248 |
} |
251 |
} |
Lines 250-319
elsif ( $op eq 'cud-update_sql' || $op eq 'cud-update_and_run_sql' ){
Link Here
|
250 |
$op = 'run'; |
253 |
$op = 'run'; |
251 |
} |
254 |
} |
252 |
} |
255 |
} |
253 |
} |
|
|
254 |
|
256 |
|
255 |
elsif ($op eq 'retrieve_results') { |
257 |
} elsif ( $op eq 'retrieve_results' ) { |
256 |
my $id = $input->param('id'); |
258 |
my $id = $input->param('id'); |
257 |
my $result = format_results( $id ); |
259 |
my $result = format_results($id); |
258 |
$template->param( |
260 |
$template->param( |
259 |
report_name => $result->{report_name}, |
261 |
report_name => $result->{report_name}, |
260 |
notes => $result->{notes}, |
262 |
notes => $result->{notes}, |
261 |
saved_results => $result->{results}, |
263 |
saved_results => $result->{results}, |
262 |
date_run => $result->{date_run}, |
264 |
date_run => $result->{date_run}, |
263 |
); |
265 |
); |
264 |
} |
|
|
265 |
|
266 |
|
266 |
elsif ( $op eq 'cud-report' ) { |
267 |
} elsif ( $op eq 'cud-report' ) { |
267 |
my $cache_expiry_units = $input->param('cache_expiry_units'), |
268 |
my $cache_expiry_units = $input->param('cache_expiry_units'), |
268 |
my $cache_expiry = $input->param('cache_expiry'); |
269 |
my $cache_expiry = $input->param('cache_expiry'); |
269 |
|
270 |
|
270 |
# we need to handle converting units |
271 |
# we need to handle converting units |
271 |
if( $cache_expiry_units eq "minutes" ){ |
272 |
if ( $cache_expiry_units eq "minutes" ) { |
272 |
$cache_expiry *= 60; |
273 |
$cache_expiry *= 60; |
273 |
} elsif( $cache_expiry_units eq "hours" ){ |
274 |
} elsif ( $cache_expiry_units eq "hours" ) { |
274 |
$cache_expiry *= 3600; # 60 * 60 |
275 |
$cache_expiry *= 3600; # 60 * 60 |
275 |
} elsif( $cache_expiry_units eq "days" ){ |
276 |
} elsif ( $cache_expiry_units eq "days" ) { |
276 |
$cache_expiry *= 86400; # 60 * 60 * 24 |
277 |
$cache_expiry *= 86400; # 60 * 60 * 24 |
277 |
} |
278 |
} |
|
|
279 |
|
278 |
# check $cache_expiry isn't too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp |
280 |
# check $cache_expiry isn't too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp |
279 |
if( $cache_expiry >= 2592000 ){ # oops, over the limit of 30 days |
281 |
if ( $cache_expiry >= 2592000 ) { # oops, over the limit of 30 days |
280 |
# report error to user |
282 |
# report error to user |
281 |
$template->param( |
283 |
$template->param( |
282 |
'cache_error' => 1, |
284 |
'cache_error' => 1, |
283 |
'build1' => 1, |
285 |
'build1' => 1, |
284 |
'areas' => get_report_areas(), |
286 |
'areas' => get_report_areas(), |
285 |
'cache_expiry' => $cache_expiry, |
287 |
'cache_expiry' => $cache_expiry, |
286 |
'usecache' => $usecache, |
288 |
'usecache' => $usecache, |
287 |
'public' => scalar $input->param('public'), |
289 |
'public' => scalar $input->param('public'), |
288 |
); |
290 |
); |
289 |
} else { |
291 |
} else { |
290 |
# they have chosen a new report and the area to report on |
292 |
|
291 |
$template->param( |
293 |
# they have chosen a new report and the area to report on |
292 |
'build2' => 1, |
294 |
$template->param( |
293 |
'area' => scalar $input->param('area'), |
295 |
'build2' => 1, |
294 |
'types' => get_report_types(), |
296 |
'area' => scalar $input->param('area'), |
295 |
'cache_expiry' => $cache_expiry, |
297 |
'types' => get_report_types(), |
296 |
'public' => scalar $input->param('public'), |
298 |
'cache_expiry' => $cache_expiry, |
297 |
); |
299 |
'public' => scalar $input->param('public'), |
|
|
300 |
); |
298 |
} |
301 |
} |
299 |
} |
|
|
300 |
|
302 |
|
301 |
elsif ( $op eq 'cud-choose_type' ) { |
303 |
} elsif ( $op eq 'cud-choose_type' ) { |
|
|
304 |
|
302 |
# they have chosen type and area |
305 |
# they have chosen type and area |
303 |
# get area and type and pass them to the template |
306 |
# get area and type and pass them to the template |
304 |
my $area = $input->param('area'); |
307 |
my $area = $input->param('area'); |
305 |
my $type = $input->param('types'); |
308 |
my $type = $input->param('types'); |
306 |
$template->param( |
309 |
$template->param( |
307 |
'build3' => 1, |
310 |
'build3' => 1, |
308 |
'area' => $area, |
311 |
'area' => $area, |
309 |
'type' => $type, |
312 |
'type' => $type, |
310 |
columns => get_columns($area,$input), |
313 |
columns => get_columns( $area, $input ), |
311 |
'cache_expiry' => scalar $input->param('cache_expiry'), |
314 |
'cache_expiry' => scalar $input->param('cache_expiry'), |
312 |
'public' => scalar $input->param('public'), |
315 |
'public' => scalar $input->param('public'), |
313 |
); |
316 |
); |
314 |
} |
|
|
315 |
|
317 |
|
316 |
elsif ( $op eq 'cud-choose_columns' ) { |
318 |
} elsif ( $op eq 'cud-choose_columns' ) { |
|
|
319 |
|
317 |
# we now know type, area, and columns |
320 |
# we now know type, area, and columns |
318 |
# next step is the constraints |
321 |
# next step is the constraints |
319 |
my $area = $input->param('area'); |
322 |
my $area = $input->param('area'); |
Lines 322-366
elsif ( $op eq 'cud-choose_columns' ) {
Link Here
|
322 |
my $column = join( ',', @columns ); |
325 |
my $column = join( ',', @columns ); |
323 |
|
326 |
|
324 |
$template->param( |
327 |
$template->param( |
325 |
'build4' => 1, |
328 |
'build4' => 1, |
326 |
'area' => $area, |
329 |
'area' => $area, |
327 |
'type' => $type, |
330 |
'type' => $type, |
328 |
'column' => $column, |
331 |
'column' => $column, |
329 |
definitions => get_from_dictionary($area), |
332 |
definitions => get_from_dictionary($area), |
330 |
criteria => get_criteria($area,$input), |
333 |
criteria => get_criteria( $area, $input ), |
331 |
'public' => scalar $input->param('public'), |
334 |
'public' => scalar $input->param('public'), |
332 |
); |
335 |
); |
333 |
if ( $usecache ) { |
336 |
if ($usecache) { |
334 |
$template->param( |
337 |
$template->param( |
335 |
cache_expiry => scalar $input->param('cache_expiry'), |
338 |
cache_expiry => scalar $input->param('cache_expiry'), |
336 |
cache_expiry_units => scalar $input->param('cache_expiry_units'), |
339 |
cache_expiry_units => scalar $input->param('cache_expiry_units'), |
337 |
); |
340 |
); |
338 |
} |
341 |
} |
339 |
|
342 |
|
340 |
} |
343 |
} elsif ( $op eq 'cud-choose_criteria' ) { |
341 |
|
344 |
my $area = $input->param('area'); |
342 |
elsif ( $op eq 'cud-choose_criteria' ) { |
345 |
my $type = $input->param('type'); |
343 |
my $area = $input->param('area'); |
346 |
my $column = $input->param('column'); |
344 |
my $type = $input->param('type'); |
|
|
345 |
my $column = $input->param('column'); |
346 |
my @definitions = $input->multi_param('definition'); |
347 |
my @definitions = $input->multi_param('definition'); |
347 |
my $definition = join (',',@definitions); |
348 |
my $definition = join( ',', @definitions ); |
348 |
my @criteria = $input->multi_param('criteria_column'); |
349 |
my @criteria = $input->multi_param('criteria_column'); |
349 |
my $query_criteria; |
350 |
my $query_criteria; |
350 |
foreach my $crit (@criteria) { |
351 |
foreach my $crit (@criteria) { |
351 |
my $value = $input->param( $crit . "_value" ); |
352 |
my $value = $input->param( $crit . "_value" ); |
352 |
|
353 |
|
353 |
# If value is not defined, then it may be range values |
354 |
# If value is not defined, then it may be range values |
354 |
if (!defined $value) { |
355 |
if ( !defined $value ) { |
355 |
my $fromvalue = $input->param( "from_" . $crit . "_value" ); |
356 |
my $fromvalue = $input->param( "from_" . $crit . "_value" ); |
356 |
my $tovalue = $input->param( "to_" . $crit . "_value" ); |
357 |
my $tovalue = $input->param( "to_" . $crit . "_value" ); |
357 |
|
358 |
|
358 |
if ($fromvalue && $tovalue) { |
359 |
if ( $fromvalue && $tovalue ) { |
359 |
$query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'"; |
360 |
$query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'"; |
360 |
} |
361 |
} |
361 |
} else { |
362 |
} else { |
|
|
363 |
|
362 |
# don't escape runtime parameters, they'll be at runtime |
364 |
# don't escape runtime parameters, they'll be at runtime |
363 |
if ($value =~ /<<.*>>/) { |
365 |
if ( $value =~ /<<.*>>/ ) { |
364 |
$query_criteria .= " AND $crit=$value"; |
366 |
$query_criteria .= " AND $crit=$value"; |
365 |
} else { |
367 |
} else { |
366 |
$query_criteria .= " AND $crit='$value'"; |
368 |
$query_criteria .= " AND $crit='$value'"; |
Lines 374-384
elsif ( $op eq 'cud-choose_criteria' ) {
Link Here
|
374 |
'column' => $column, |
376 |
'column' => $column, |
375 |
'definition' => $definition, |
377 |
'definition' => $definition, |
376 |
'criteriastring' => $query_criteria, |
378 |
'criteriastring' => $query_criteria, |
377 |
'public' => scalar $input->param('public'), |
379 |
'public' => scalar $input->param('public'), |
378 |
); |
380 |
); |
379 |
if ( $usecache ) { |
381 |
if ($usecache) { |
380 |
$template->param( |
382 |
$template->param( |
381 |
cache_expiry => scalar $input->param('cache_expiry'), |
383 |
cache_expiry => scalar $input->param('cache_expiry'), |
382 |
cache_expiry_units => scalar $input->param('cache_expiry_units'), |
384 |
cache_expiry_units => scalar $input->param('cache_expiry_units'), |
383 |
); |
385 |
); |
384 |
} |
386 |
} |
Lines 389-412
elsif ( $op eq 'cud-choose_criteria' ) {
Link Here
|
389 |
|
391 |
|
390 |
# build structue for use by tmpl_loop to choose columns to order by |
392 |
# build structue for use by tmpl_loop to choose columns to order by |
391 |
# need to do something about the order of the order :) |
393 |
# need to do something about the order of the order :) |
392 |
# we also want to use the %columns hash to get the plain english names |
394 |
# we also want to use the %columns hash to get the plain english names |
393 |
foreach my $col (@columns) { |
395 |
foreach my $col (@columns) { |
394 |
my %total = (name => $col); |
396 |
my %total = ( name => $col ); |
395 |
my @selects = map {+{ value => $_ }} (qw(sum min max avg count)); |
397 |
my @selects = map { +{ value => $_ } } (qw(sum min max avg count)); |
396 |
$total{'select'} = \@selects; |
398 |
$total{'select'} = \@selects; |
397 |
push @total_by, \%total; |
399 |
push @total_by, \%total; |
398 |
} |
400 |
} |
399 |
|
401 |
|
400 |
$template->param( 'total_by' => \@total_by ); |
402 |
$template->param( 'total_by' => \@total_by ); |
401 |
} |
|
|
402 |
|
403 |
|
403 |
elsif ( $op eq 'cud-choose_operations' ) { |
404 |
} elsif ( $op eq 'cud-choose_operations' ) { |
404 |
my $area = $input->param('area'); |
405 |
my $area = $input->param('area'); |
405 |
my $type = $input->param('type'); |
406 |
my $type = $input->param('type'); |
406 |
my $column = $input->param('column'); |
407 |
my $column = $input->param('column'); |
407 |
my $criteria = $input->param('criteria'); |
408 |
my $criteria = $input->param('criteria'); |
408 |
my $definition = $input->param('definition'); |
409 |
my $definition = $input->param('definition'); |
409 |
my @total_by = $input->multi_param('total_by'); |
410 |
my @total_by = $input->multi_param('total_by'); |
410 |
my $totals; |
411 |
my $totals; |
411 |
foreach my $total (@total_by) { |
412 |
foreach my $total (@total_by) { |
412 |
my $value = $input->param( $total . "_tvalue" ); |
413 |
my $value = $input->param( $total . "_tvalue" ); |
Lines 421-428
elsif ( $op eq 'cud-choose_operations' ) {
Link Here
|
421 |
'criteriastring' => $criteria, |
422 |
'criteriastring' => $criteria, |
422 |
'totals' => $totals, |
423 |
'totals' => $totals, |
423 |
'definition' => $definition, |
424 |
'definition' => $definition, |
424 |
'cache_expiry' => scalar $input->param('cache_expiry'), |
425 |
'cache_expiry' => scalar $input->param('cache_expiry'), |
425 |
'public' => scalar $input->param('public'), |
426 |
'public' => scalar $input->param('public'), |
426 |
); |
427 |
); |
427 |
|
428 |
|
428 |
# get columns |
429 |
# get columns |
Lines 432-458
elsif ( $op eq 'cud-choose_operations' ) {
Link Here
|
432 |
# build structue for use by tmpl_loop to choose columns to order by |
433 |
# build structue for use by tmpl_loop to choose columns to order by |
433 |
# need to do something about the order of the order :) |
434 |
# need to do something about the order of the order :) |
434 |
foreach my $col (@columns) { |
435 |
foreach my $col (@columns) { |
435 |
my %order = (name => $col); |
436 |
my %order = ( name => $col ); |
436 |
my @selects = map {+{ value => $_ }} (qw(asc desc)); |
437 |
my @selects = map { +{ value => $_ } } (qw(asc desc)); |
437 |
$order{'select'} = \@selects; |
438 |
$order{'select'} = \@selects; |
438 |
push @order_by, \%order; |
439 |
push @order_by, \%order; |
439 |
} |
440 |
} |
440 |
|
441 |
|
441 |
$template->param( 'order_by' => \@order_by ); |
442 |
$template->param( 'order_by' => \@order_by ); |
442 |
} |
|
|
443 |
|
443 |
|
444 |
elsif ( $op eq 'cud-build_report' ) { |
444 |
} elsif ( $op eq 'cud-build_report' ) { |
445 |
|
445 |
|
446 |
# now we have all the info we need and can build the sql |
446 |
# now we have all the info we need and can build the sql |
447 |
my $area = $input->param('area'); |
447 |
my $area = $input->param('area'); |
448 |
my $type = $input->param('type'); |
448 |
my $type = $input->param('type'); |
449 |
my $column = $input->param('column'); |
449 |
my $column = $input->param('column'); |
450 |
my $crit = $input->param('criteria'); |
450 |
my $crit = $input->param('criteria'); |
451 |
my $totals = $input->param('totals'); |
451 |
my $totals = $input->param('totals'); |
452 |
my $definition = $input->param('definition'); |
452 |
my $definition = $input->param('definition'); |
453 |
my $query_criteria=$crit; |
453 |
my $query_criteria = $crit; |
|
|
454 |
|
454 |
# split the columns up by , |
455 |
# split the columns up by , |
455 |
my @columns = split( ',', $column ); |
456 |
my @columns = split( ',', $column ); |
456 |
my @order_by = $input->multi_param('order_by'); |
457 |
my @order_by = $input->multi_param('order_by'); |
457 |
|
458 |
|
458 |
my $query_orderby; |
459 |
my $query_orderby; |
Lines 460-575
elsif ( $op eq 'cud-build_report' ) {
Link Here
|
460 |
my $value = $input->param( $order . "_ovalue" ); |
461 |
my $value = $input->param( $order . "_ovalue" ); |
461 |
if ($query_orderby) { |
462 |
if ($query_orderby) { |
462 |
$query_orderby .= ",$order $value"; |
463 |
$query_orderby .= ",$order $value"; |
463 |
} |
464 |
} else { |
464 |
else { |
|
|
465 |
$query_orderby = " ORDER BY $order $value"; |
465 |
$query_orderby = " ORDER BY $order $value"; |
466 |
} |
466 |
} |
467 |
} |
467 |
} |
468 |
|
468 |
|
469 |
# get the sql |
469 |
# get the sql |
470 |
my $sql = |
470 |
my $sql = build_query( \@columns, $query_criteria, $query_orderby, $area, $totals, $definition ); |
471 |
build_query( \@columns, $query_criteria, $query_orderby, $area, $totals, $definition ); |
|
|
472 |
$template->param( |
471 |
$template->param( |
473 |
'showreport' => 1, |
472 |
'showreport' => 1, |
474 |
'area' => $area, |
473 |
'area' => $area, |
475 |
'sql' => $sql, |
474 |
'sql' => $sql, |
476 |
'type' => $type, |
475 |
'type' => $type, |
477 |
'cache_expiry' => scalar $input->param('cache_expiry'), |
476 |
'cache_expiry' => scalar $input->param('cache_expiry'), |
478 |
'public' => scalar $input->param('public'), |
477 |
'public' => scalar $input->param('public'), |
479 |
); |
478 |
); |
480 |
} |
|
|
481 |
|
479 |
|
482 |
elsif ( $op eq 'save' ) { |
480 |
} elsif ( $op eq 'save' ) { |
|
|
481 |
|
483 |
# Save the report that has just been built |
482 |
# Save the report that has just been built |
484 |
my $area = $input->param('area'); |
483 |
my $area = $input->param('area'); |
485 |
my $sql = $input->param('sql'); |
484 |
my $sql = $input->param('sql'); |
486 |
my $type = $input->param('type'); |
485 |
my $type = $input->param('type'); |
487 |
$template->param( |
486 |
$template->param( |
488 |
'save' => 1, |
487 |
'save' => 1, |
489 |
'area' => $area, |
488 |
'area' => $area, |
490 |
'sql' => $sql, |
489 |
'sql' => $sql, |
491 |
'type' => $type, |
490 |
'type' => $type, |
492 |
'cache_expiry' => scalar $input->param('cache_expiry'), |
491 |
'cache_expiry' => scalar $input->param('cache_expiry'), |
493 |
'public' => scalar $input->param('public'), |
492 |
'public' => scalar $input->param('public'), |
494 |
'groups_with_subgroups' => groups_with_subgroups($area), # in case we have a report group that matches area |
493 |
'groups_with_subgroups' => groups_with_subgroups($area), # in case we have a report group that matches area |
495 |
); |
494 |
); |
496 |
} |
|
|
497 |
|
495 |
|
498 |
elsif ( $op eq 'cud-save' ) { |
496 |
} elsif ( $op eq 'cud-save' ) { |
|
|
497 |
|
499 |
# save the sql pasted in by a user |
498 |
# save the sql pasted in by a user |
500 |
my $area = $input->param('area'); |
499 |
my $area = $input->param('area'); |
501 |
my $group = $input->param('group'); |
500 |
my $group = $input->param('group'); |
502 |
my $subgroup = $input->param('subgroup'); |
501 |
my $subgroup = $input->param('subgroup'); |
503 |
my $sql = $input->param('sql'); |
502 |
my $sql = $input->param('sql'); |
504 |
my $name = $input->param('reportname'); |
503 |
my $name = $input->param('reportname'); |
505 |
my $type = $input->param('types'); |
504 |
my $type = $input->param('types'); |
506 |
my $notes = $input->param('notes'); |
505 |
my $notes = $input->param('notes'); |
507 |
my $cache_expiry = $input->param('cache_expiry'); |
506 |
my $cache_expiry = $input->param('cache_expiry'); |
508 |
my $cache_expiry_units = $input->param('cache_expiry_units'); |
507 |
my $cache_expiry_units = $input->param('cache_expiry_units'); |
509 |
my $public = $input->param('public'); |
508 |
my $public = $input->param('public'); |
510 |
my $save_anyway = $input->param('save_anyway'); |
509 |
my $save_anyway = $input->param('save_anyway'); |
511 |
my $tables = get_tables(); |
510 |
my $tables = get_tables(); |
512 |
|
|
|
513 |
|
511 |
|
514 |
# if we have the units, then we came from creating a report from SQL and thus need to handle converting units |
512 |
# if we have the units, then we came from creating a report from SQL and thus need to handle converting units |
515 |
if( $cache_expiry_units ){ |
513 |
if ($cache_expiry_units) { |
516 |
if( $cache_expiry_units eq "minutes" ){ |
514 |
if ( $cache_expiry_units eq "minutes" ) { |
517 |
$cache_expiry *= 60; |
515 |
$cache_expiry *= 60; |
518 |
} elsif( $cache_expiry_units eq "hours" ){ |
516 |
} elsif ( $cache_expiry_units eq "hours" ) { |
519 |
$cache_expiry *= 3600; # 60 * 60 |
517 |
$cache_expiry *= 3600; # 60 * 60 |
520 |
} elsif( $cache_expiry_units eq "days" ){ |
518 |
} elsif ( $cache_expiry_units eq "days" ) { |
521 |
$cache_expiry *= 86400; # 60 * 60 * 24 |
519 |
$cache_expiry *= 86400; # 60 * 60 * 24 |
522 |
} |
520 |
} |
523 |
} |
521 |
} |
|
|
522 |
|
524 |
# check $cache_expiry isn't too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp |
523 |
# check $cache_expiry isn't too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp |
525 |
if( $cache_expiry && $cache_expiry >= 2592000 ){ |
524 |
if ( $cache_expiry && $cache_expiry >= 2592000 ) { |
526 |
push @errors, {cache_expiry => $cache_expiry}; |
525 |
push @errors, { cache_expiry => $cache_expiry }; |
527 |
} |
526 |
} |
528 |
|
527 |
|
529 |
create_non_existing_group_and_subgroup($input, $group, $subgroup); |
528 |
create_non_existing_group_and_subgroup( $input, $group, $subgroup ); |
530 |
## FIXME this is AFTER entering a name to save the report under |
529 |
## FIXME this is AFTER entering a name to save the report under |
531 |
my ( $is_sql_valid, $validation_errors ) = Koha::Report->new({ savedsql => $sql })->is_sql_valid; |
530 |
my ( $is_sql_valid, $validation_errors ) = Koha::Report->new( { savedsql => $sql } )->is_sql_valid; |
532 |
push(@errors, @$validation_errors) unless $is_sql_valid; |
531 |
push( @errors, @$validation_errors ) unless $is_sql_valid; |
533 |
|
532 |
|
534 |
if (@errors) { |
533 |
if (@errors) { |
535 |
$template->param( |
534 |
$template->param( |
536 |
'errors' => \@errors, |
535 |
'errors' => \@errors, |
537 |
'sql' => $sql, |
536 |
'sql' => $sql, |
538 |
'reportname'=> $name, |
537 |
'reportname' => $name, |
539 |
'type' => $type, |
538 |
'type' => $type, |
540 |
'notes' => $notes, |
539 |
'notes' => $notes, |
541 |
'cache_expiry' => $cache_expiry, |
540 |
'cache_expiry' => $cache_expiry, |
542 |
'public' => $public, |
541 |
'public' => $public, |
543 |
); |
542 |
); |
544 |
} else { |
543 |
} else { |
|
|
544 |
|
545 |
# Check defined SQL parameters for authorised value validity |
545 |
# Check defined SQL parameters for authorised value validity |
546 |
my $problematic_authvals = ValidateSQLParameters($sql); |
546 |
my $problematic_authvals = ValidateSQLParameters($sql); |
547 |
|
547 |
|
548 |
if ( scalar @$problematic_authvals > 0 && not $save_anyway ) { |
548 |
if ( scalar @$problematic_authvals > 0 && not $save_anyway ) { |
|
|
549 |
|
549 |
# There's at least one problematic parameter, report to the |
550 |
# There's at least one problematic parameter, report to the |
550 |
# GUI and provide all user input for further actions |
551 |
# GUI and provide all user input for further actions |
551 |
$template->param( |
552 |
$template->param( |
552 |
'area' => $area, |
553 |
'area' => $area, |
553 |
'group' => $group, |
554 |
'group' => $group, |
554 |
'subgroup' => $subgroup, |
555 |
'subgroup' => $subgroup, |
555 |
'sql' => $sql, |
556 |
'sql' => $sql, |
556 |
'reportname' => $name, |
557 |
'reportname' => $name, |
557 |
'type' => $type, |
558 |
'type' => $type, |
558 |
'notes' => $notes, |
559 |
'notes' => $notes, |
559 |
'public' => $public, |
560 |
'public' => $public, |
560 |
'problematic_authvals' => $problematic_authvals, |
561 |
'problematic_authvals' => $problematic_authvals, |
561 |
'warn_authval_problem' => 1, |
562 |
'warn_authval_problem' => 1, |
562 |
'phase_save' => 1 |
563 |
'phase_save' => 1 |
563 |
); |
564 |
); |
564 |
if ( $usecache ) { |
565 |
if ($usecache) { |
565 |
$template->param( |
566 |
$template->param( |
566 |
cache_expiry => $cache_expiry, |
567 |
cache_expiry => $cache_expiry, |
567 |
cache_expiry_units => $cache_expiry_units, |
568 |
cache_expiry_units => $cache_expiry_units, |
568 |
); |
569 |
); |
569 |
} |
570 |
} |
570 |
} else { |
571 |
} else { |
|
|
572 |
|
571 |
# No params problem found or asked to save anyway |
573 |
# No params problem found or asked to save anyway |
572 |
my $id = save_report( { |
574 |
my $id = save_report( |
|
|
575 |
{ |
573 |
borrowernumber => $borrowernumber, |
576 |
borrowernumber => $borrowernumber, |
574 |
sql => $sql, |
577 |
sql => $sql, |
575 |
name => $name, |
578 |
name => $name, |
Lines 580-618
elsif ( $op eq 'cud-save' ) {
Link Here
|
580 |
notes => $notes, |
583 |
notes => $notes, |
581 |
cache_expiry => $cache_expiry, |
584 |
cache_expiry => $cache_expiry, |
582 |
public => $public, |
585 |
public => $public, |
583 |
} ); |
586 |
} |
584 |
logaction( "REPORTS", "ADD", $id, "$name | $sql" ) if C4::Context->preference("ReportsLog"); |
587 |
); |
|
|
588 |
logaction( "REPORTS", "ADD", $id, "$name | $sql" ) if C4::Context->preference("ReportsLog"); |
585 |
$template->param( |
589 |
$template->param( |
586 |
'save_successful' => 1, |
590 |
'save_successful' => 1, |
587 |
'reportname' => $name, |
591 |
'reportname' => $name, |
588 |
'id' => $id, |
592 |
'id' => $id, |
589 |
'editsql' => 1, |
593 |
'editsql' => 1, |
590 |
'sql' => $sql, |
594 |
'sql' => $sql, |
591 |
'groups_with_subgroups' => groups_with_subgroups($group, $subgroup), |
595 |
'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ), |
592 |
'notes' => $notes, |
596 |
'notes' => $notes, |
593 |
'cache_expiry' => $cache_expiry, |
597 |
'cache_expiry' => $cache_expiry, |
594 |
'public' => $public, |
598 |
'public' => $public, |
595 |
'usecache' => $usecache, |
599 |
'usecache' => $usecache, |
596 |
'tables' => $tables |
600 |
'tables' => $tables |
597 |
); |
601 |
); |
598 |
} |
602 |
} |
599 |
} |
603 |
} |
600 |
} |
|
|
601 |
|
604 |
|
602 |
elsif ($op eq 'cud-share'){ |
605 |
} elsif ( $op eq 'cud-share' ) { |
603 |
my $lang = $input->param('mana_language') || ''; |
606 |
my $lang = $input->param('mana_language') || ''; |
604 |
my $reportid = $input->param('reportid'); |
607 |
my $reportid = $input->param('reportid'); |
605 |
my $result = Koha::SharedContent::send_entity($lang, $borrowernumber, $reportid, 'report'); |
608 |
my $result = Koha::SharedContent::send_entity( $lang, $borrowernumber, $reportid, 'report' ); |
606 |
if ( $result ) { |
609 |
if ($result) { |
607 |
print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?op=listmanamsg=".$result->{msg}); |
610 |
print $input->redirect( "/cgi-bin/koha/reports/guided_reports.pl?op=listmanamsg=" . $result->{msg} ); |
608 |
}else{ |
611 |
} else { |
609 |
print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?op=list&manamsg=noanswer"); |
612 |
print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?op=list&manamsg=noanswer"); |
610 |
} |
613 |
} |
611 |
} |
|
|
612 |
|
614 |
|
613 |
elsif ($op eq 'export'){ |
615 |
} elsif ( $op eq 'export' ) { |
614 |
|
616 |
|
615 |
# export results to tab separated text or CSV |
617 |
# export results to tab separated text or CSV |
616 |
my $report_id = $input->param('id'); |
618 |
my $report_id = $input->param('id'); |
617 |
my $report = Koha::Reports->find($report_id); |
619 |
my $report = Koha::Reports->find($report_id); |
618 |
my $sql = $report->savedsql; |
620 |
my $sql = $report->savedsql; |
Lines 620-663
elsif ($op eq 'export'){
Link Here
|
620 |
my @sql_params = $input->multi_param('sql_params'); |
622 |
my @sql_params = $input->multi_param('sql_params'); |
621 |
my $format = $input->param('format'); |
623 |
my $format = $input->param('format'); |
622 |
my $reportname = $input->param('reportname'); |
624 |
my $reportname = $input->param('reportname'); |
623 |
my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ; |
625 |
my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format"; |
624 |
my $hr = HTML::Restrict->new(); |
626 |
my $hr = HTML::Restrict->new(); |
625 |
|
627 |
|
626 |
($sql, undef) = $report->prep_report( \@param_names, \@sql_params ); |
628 |
( $sql, undef ) = $report->prep_report( \@param_names, \@sql_params ); |
627 |
my ( $sth, $q_errors ) = execute_query( { sql => $sql, report_id => $report_id } ); |
629 |
my ( $sth, $q_errors ) = execute_query( { sql => $sql, report_id => $report_id } ); |
628 |
unless ($q_errors and @$q_errors) { |
630 |
unless ( $q_errors and @$q_errors ) { |
629 |
my ( $type, $content ); |
631 |
my ( $type, $content ); |
630 |
if ($format eq 'tab') { |
632 |
if ( $format eq 'tab' ) { |
631 |
$type = 'application/octet-stream'; |
633 |
$type = 'application/octet-stream'; |
632 |
$content .= join("\t", header_cell_values($sth)) . "\n"; |
634 |
$content .= join( "\t", header_cell_values($sth) ) . "\n"; |
633 |
$content = $hr->process(Encode::decode('UTF-8', $content)); |
635 |
$content = $hr->process( Encode::decode( 'UTF-8', $content ) ); |
634 |
while (my $row = $sth->fetchrow_arrayref()) { |
636 |
while ( my $row = $sth->fetchrow_arrayref() ) { |
635 |
$content .= join("\t", $hr->process(@$row)) . "\n"; |
637 |
$content .= join( "\t", $hr->process(@$row) ) . "\n"; |
636 |
} |
638 |
} |
637 |
} else { |
639 |
} else { |
638 |
if ( $format eq 'csv' ) { |
640 |
if ( $format eq 'csv' ) { |
639 |
my $delimiter = C4::Context->csv_delimiter; |
641 |
my $delimiter = C4::Context->csv_delimiter; |
640 |
$type = 'application/csv'; |
642 |
$type = 'application/csv'; |
641 |
my $csv = Text::CSV::Encoded->new({ encoding_out => 'UTF-8', sep_char => $delimiter}); |
643 |
my $csv = Text::CSV::Encoded->new( { encoding_out => 'UTF-8', sep_char => $delimiter } ); |
642 |
$csv or die "Text::CSV::Encoded->new({binary => 1}) FAILED: " . Text::CSV::Encoded->error_diag(); |
644 |
$csv or die "Text::CSV::Encoded->new({binary => 1}) FAILED: " . Text::CSV::Encoded->error_diag(); |
643 |
if ($csv->combine(header_cell_values($sth))) { |
645 |
if ( $csv->combine( header_cell_values($sth) ) ) { |
644 |
$content .= $hr->process(Encode::decode('UTF-8', $csv->string())) . "\n"; |
646 |
$content .= $hr->process( Encode::decode( 'UTF-8', $csv->string() ) ) . "\n"; |
645 |
|
647 |
|
646 |
} else { |
648 |
} else { |
647 |
push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() } ; |
649 |
push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() }; |
648 |
} |
650 |
} |
649 |
while (my $row = $sth->fetchrow_arrayref()) { |
651 |
while ( my $row = $sth->fetchrow_arrayref() ) { |
650 |
if ($csv->combine(@$row)) { |
652 |
if ( $csv->combine(@$row) ) { |
651 |
$content .= $hr->process($csv->string()) . "\n"; |
653 |
$content .= $hr->process( $csv->string() ) . "\n"; |
652 |
|
654 |
|
653 |
} else { |
655 |
} else { |
654 |
push @$q_errors, { combine => $csv->error_diag() } ; |
656 |
push @$q_errors, { combine => $csv->error_diag() }; |
655 |
} |
657 |
} |
656 |
} |
658 |
} |
657 |
} |
659 |
} elsif ( $format eq 'ods' ) { |
658 |
elsif ( $format eq 'ods' ) { |
|
|
659 |
$type = 'application/vnd.oasis.opendocument.spreadsheet'; |
660 |
$type = 'application/vnd.oasis.opendocument.spreadsheet'; |
660 |
my $ods_fh = File::Temp->new( UNLINK => 0 ); |
661 |
my $ods_fh = File::Temp->new( UNLINK => 0 ); |
661 |
my $ods_filepath = $ods_fh->filename; |
662 |
my $ods_filepath = $ods_fh->filename; |
662 |
my $ods_content; |
663 |
my $ods_content; |
663 |
|
664 |
|
Lines 667-691
elsif ($op eq 'export'){
Link Here
|
667 |
|
668 |
|
668 |
# Other line in Unicode |
669 |
# Other line in Unicode |
669 |
my $sql_rows = $sth->fetchall_arrayref(); |
670 |
my $sql_rows = $sth->fetchall_arrayref(); |
670 |
foreach my $sql_row ( @$sql_rows ) { |
671 |
foreach my $sql_row (@$sql_rows) { |
671 |
my @content_row; |
672 |
my @content_row; |
672 |
foreach my $sql_cell ( @$sql_row ) { |
673 |
foreach my $sql_cell (@$sql_row) { |
673 |
push @content_row, $hr->process(Encode::encode( 'UTF8', $sql_cell )); |
674 |
push @content_row, $hr->process( Encode::encode( 'UTF8', $sql_cell ) ); |
674 |
|
675 |
|
675 |
} |
676 |
} |
676 |
push @$ods_content, \@content_row; |
677 |
push @$ods_content, \@content_row; |
677 |
} |
678 |
} |
678 |
|
679 |
|
679 |
# Process |
680 |
# Process |
680 |
generate_ods($ods_filepath, $ods_content); |
681 |
generate_ods( $ods_filepath, $ods_content ); |
681 |
|
682 |
|
682 |
# Output |
683 |
# Output |
683 |
binmode(STDOUT); |
684 |
binmode(STDOUT); |
684 |
open $ods_fh, '<', $ods_filepath; |
685 |
open $ods_fh, '<', $ods_filepath; |
685 |
$content .= $_ while <$ods_fh>; |
686 |
$content .= $_ while <$ods_fh>; |
686 |
unlink $ods_filepath; |
687 |
unlink $ods_filepath; |
687 |
} |
688 |
} elsif ( $format eq 'template' ) { |
688 |
elsif ( $format eq 'template' ) { |
|
|
689 |
my $template_id = $input->param('template'); |
689 |
my $template_id = $input->param('template'); |
690 |
my $notice_template = Koha::Notice::Templates->find($template_id); |
690 |
my $notice_template = Koha::Notice::Templates->find($template_id); |
691 |
my $data = $sth->fetchall_arrayref( {} ); |
691 |
my $data = $sth->fetchall_arrayref( {} ); |
Lines 707-767
elsif ($op eq 'export'){
Link Here
|
707 |
} |
707 |
} |
708 |
} |
708 |
} |
709 |
print $input->header( |
709 |
print $input->header( |
710 |
-type => $type, |
710 |
-type => $type, |
711 |
-attachment=> $reportfilename |
711 |
-attachment => $reportfilename |
712 |
); |
712 |
); |
713 |
print $content; |
713 |
print $content; |
714 |
|
714 |
|
715 |
foreach my $err (@$q_errors, @errors) { |
715 |
foreach my $err ( @$q_errors, @errors ) { |
716 |
print "# ERROR: " . (map {$_ . ": " . $err->{$_}} keys %$err) . "\n"; |
716 |
print "# ERROR: " . ( map { $_ . ": " . $err->{$_} } keys %$err ) . "\n"; |
717 |
} # here we print all the non-fatal errors at the end. Not super smooth, but better than nothing. |
717 |
} # here we print all the non-fatal errors at the end. Not super smooth, but better than nothing. |
718 |
exit; |
718 |
exit; |
719 |
} |
719 |
} |
720 |
$template->param( |
720 |
$template->param( |
721 |
'sql' => $sql, |
721 |
'sql' => $sql, |
722 |
'execute' => 1, |
722 |
'execute' => 1, |
723 |
'name' => 'Error exporting report!', |
723 |
'name' => 'Error exporting report!', |
724 |
'notes' => '', |
724 |
'notes' => '', |
725 |
'errors' => $q_errors, |
725 |
'errors' => $q_errors, |
726 |
); |
726 |
); |
727 |
} |
|
|
728 |
|
727 |
|
729 |
elsif ( $op eq 'add_form_sql' || $op eq 'duplicate' ) { |
728 |
} elsif ( $op eq 'add_form_sql' || $op eq 'duplicate' ) { |
730 |
|
729 |
|
731 |
my ($group, $subgroup, $sql, $reportname, $notes); |
730 |
my ( $group, $subgroup, $sql, $reportname, $notes ); |
732 |
if ( $input->param('sql') ) { |
731 |
if ( $input->param('sql') ) { |
733 |
$group = $input->param('report_group'); |
732 |
$group = $input->param('report_group'); |
734 |
$subgroup = $input->param('report_subgroup'); |
733 |
$subgroup = $input->param('report_subgroup'); |
735 |
$sql = $input->param('sql') // ''; |
734 |
$sql = $input->param('sql') // ''; |
736 |
$reportname = $input->param('reportname') // ''; |
735 |
$reportname = $input->param('reportname') // ''; |
737 |
$notes = $input->param('notes') // ''; |
736 |
$notes = $input->param('notes') // ''; |
738 |
} |
737 |
} elsif ( my $report_id = $input->param('id') ) { |
739 |
elsif ( my $report_id = $input->param('id') ) { |
|
|
740 |
my $report = Koha::Reports->find($report_id); |
738 |
my $report = Koha::Reports->find($report_id); |
741 |
$group = $report->report_group; |
739 |
$group = $report->report_group; |
742 |
$subgroup = $report->report_subgroup; |
740 |
$subgroup = $report->report_subgroup; |
743 |
$sql = $report->savedsql // ''; |
741 |
$sql = $report->savedsql // ''; |
744 |
$reportname = $report->report_name // ''; |
742 |
$reportname = $report->report_name // ''; |
745 |
$notes = $report->notes // ''; |
743 |
$notes = $report->notes // ''; |
746 |
} |
744 |
} |
747 |
|
745 |
|
748 |
my $tables = get_tables(); |
746 |
my $tables = get_tables(); |
749 |
|
747 |
|
750 |
$template->param( |
748 |
$template->param( |
751 |
sql => $sql, |
749 |
sql => $sql, |
752 |
reportname => $reportname, |
750 |
reportname => $reportname, |
753 |
notes => $notes, |
751 |
notes => $notes, |
754 |
'create' => 1, |
752 |
'create' => 1, |
755 |
'groups_with_subgroups' => groups_with_subgroups($group, $subgroup), |
753 |
'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ), |
756 |
'public' => '0', |
754 |
'public' => '0', |
757 |
'cache_expiry' => 300, |
755 |
'cache_expiry' => 300, |
758 |
'usecache' => $usecache, |
756 |
'usecache' => $usecache, |
759 |
'tables' => $tables, |
757 |
'tables' => $tables, |
760 |
|
758 |
|
761 |
); |
759 |
); |
762 |
} |
760 |
} |
763 |
|
761 |
|
764 |
if ($op eq 'run'){ |
762 |
if ( $op eq 'run' ) { |
|
|
763 |
|
765 |
# execute a saved report |
764 |
# execute a saved report |
766 |
my $limit = $input->param('limit') || 20; |
765 |
my $limit = $input->param('limit') || 20; |
767 |
my $offset = 0; |
766 |
my $offset = 0; |
Lines 771-780
if ($op eq 'run'){
Link Here
|
771 |
my $template_id = $input->param('template'); |
770 |
my $template_id = $input->param('template'); |
772 |
my $want_full_chart = $input->param('want_full_chart') || 0; |
771 |
my $want_full_chart = $input->param('want_full_chart') || 0; |
773 |
|
772 |
|
774 |
|
|
|
775 |
# offset algorithm |
773 |
# offset algorithm |
776 |
if ($input->param('page')) { |
774 |
if ( $input->param('page') ) { |
777 |
$offset = ($input->param('page') - 1) * $limit; |
775 |
$offset = ( $input->param('page') - 1 ) * $limit; |
778 |
} |
776 |
} |
779 |
|
777 |
|
780 |
$template->param( |
778 |
$template->param( |
Lines 783-892
if ($op eq 'run'){
Link Here
|
783 |
); |
781 |
); |
784 |
|
782 |
|
785 |
my ( $sql, $original_sql, $type, $name, $notes ); |
783 |
my ( $sql, $original_sql, $type, $name, $notes ); |
786 |
if (my $report = Koha::Reports->find($report_id)) { |
784 |
if ( my $report = Koha::Reports->find($report_id) ) { |
787 |
$sql = $original_sql = $report->savedsql; |
785 |
$sql = $original_sql = $report->savedsql; |
788 |
$name = $report->report_name; |
786 |
$name = $report->report_name; |
789 |
$notes = $report->notes; |
787 |
$notes = $report->notes; |
790 |
|
788 |
|
791 |
my @rows = (); |
789 |
my @rows = (); |
792 |
my @allrows = (); |
790 |
my @allrows = (); |
|
|
791 |
|
793 |
# if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters |
792 |
# if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters |
794 |
if ($sql =~ /<</ && !@sql_params) { |
793 |
if ( $sql =~ /<</ && !@sql_params ) { |
|
|
794 |
|
795 |
# split on ??. Each odd (2,4,6,...) entry should be a parameter to fill |
795 |
# split on ??. Each odd (2,4,6,...) entry should be a parameter to fill |
796 |
my @split = split /<<|>>/,$sql; |
796 |
my @split = split /<<|>>/, $sql; |
797 |
my @tmpl_parameters; |
797 |
my @tmpl_parameters; |
798 |
my @authval_errors; |
798 |
my @authval_errors; |
799 |
my %uniq_params; |
799 |
my %uniq_params; |
800 |
for(my $i=0;$i<($#split/2);$i++) { |
800 |
for ( my $i = 0 ; $i < ( $#split / 2 ) ; $i++ ) { |
801 |
my ($text,$authorised_value_all) = split /\|/,$split[$i*2+1]; |
801 |
my ( $text, $authorised_value_all ) = split /\|/, $split[ $i * 2 + 1 ]; |
802 |
my $sep = $authorised_value_all ? "|" : ""; |
802 |
my $sep = $authorised_value_all ? "|" : ""; |
803 |
if( defined $uniq_params{$text.$sep.$authorised_value_all} ){ |
803 |
if ( defined $uniq_params{ $text . $sep . $authorised_value_all } ) { |
804 |
next; |
804 |
next; |
805 |
} else { $uniq_params{$text.$sep.$authorised_value_all} = "$i"; } |
805 |
} else { |
806 |
my ($authorised_value, $all) = split /:/, $authorised_value_all; |
806 |
$uniq_params{ $text . $sep . $authorised_value_all } = "$i"; |
|
|
807 |
} |
808 |
my ( $authorised_value, $all ) = split /:/, $authorised_value_all; |
807 |
my $input; |
809 |
my $input; |
808 |
my $labelid; |
810 |
my $labelid; |
809 |
if ( not defined $authorised_value ) { |
811 |
if ( not defined $authorised_value ) { |
|
|
812 |
|
810 |
# no authorised value input, provide a text box |
813 |
# no authorised value input, provide a text box |
811 |
$input = "text"; |
814 |
$input = "text"; |
812 |
} elsif ( $authorised_value eq "date" ) { |
815 |
} elsif ( $authorised_value eq "date" ) { |
|
|
816 |
|
813 |
# require a date, provide a date picker |
817 |
# require a date, provide a date picker |
814 |
$input = 'date'; |
818 |
$input = 'date'; |
815 |
} elsif ( $authorised_value eq "list" ) { |
819 |
} elsif ( $authorised_value eq "list" ) { |
|
|
820 |
|
816 |
# require a list, provide a textarea |
821 |
# require a list, provide a textarea |
817 |
$input = 'textarea'; |
822 |
$input = 'textarea'; |
818 |
} else { |
823 |
} else { |
|
|
824 |
|
819 |
# defined $authorised_value, and not 'date' |
825 |
# defined $authorised_value, and not 'date' |
820 |
my $dbh=C4::Context->dbh; |
826 |
my $dbh = C4::Context->dbh; |
821 |
my @authorised_values; |
827 |
my @authorised_values; |
822 |
my %authorised_lib; |
828 |
my %authorised_lib; |
|
|
829 |
|
823 |
# builds list, depending on authorised value... |
830 |
# builds list, depending on authorised value... |
824 |
if ( $authorised_value eq "branches" ) { |
831 |
if ( $authorised_value eq "branches" ) { |
825 |
my $libraries = Koha::Libraries->search( {}, { order_by => ['branchname'] } ); |
832 |
my $libraries = Koha::Libraries->search( {}, { order_by => ['branchname'] } ); |
826 |
while ( my $library = $libraries->next ) { |
833 |
while ( my $library = $libraries->next ) { |
827 |
push @authorised_values, $library->branchcode; |
834 |
push @authorised_values, $library->branchcode; |
828 |
$authorised_lib{$library->branchcode} = $library->branchname; |
835 |
$authorised_lib{ $library->branchcode } = $library->branchname; |
829 |
} |
836 |
} |
830 |
} |
837 |
} elsif ( $authorised_value eq "itemtypes" ) { |
831 |
elsif ( $authorised_value eq "itemtypes" ) { |
|
|
832 |
my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description"); |
838 |
my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description"); |
833 |
$sth->execute; |
839 |
$sth->execute; |
834 |
while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) { |
840 |
while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) { |
835 |
push @authorised_values, $itemtype; |
841 |
push @authorised_values, $itemtype; |
836 |
$authorised_lib{$itemtype} = $description; |
842 |
$authorised_lib{$itemtype} = $description; |
837 |
} |
843 |
} |
838 |
} |
844 |
} elsif ( $authorised_value eq "biblio_framework" ) { |
839 |
elsif ( $authorised_value eq "biblio_framework" ) { |
845 |
my @frameworks = |
840 |
my @frameworks = Koha::BiblioFrameworks->search({}, { order_by => ['frameworktext'] })->as_list; |
846 |
Koha::BiblioFrameworks->search( {}, { order_by => ['frameworktext'] } )->as_list; |
841 |
my $default_source = ''; |
847 |
my $default_source = ''; |
842 |
push @authorised_values,$default_source; |
848 |
push @authorised_values, $default_source; |
843 |
$authorised_lib{$default_source} = 'Default'; |
849 |
$authorised_lib{$default_source} = 'Default'; |
844 |
foreach my $framework (@frameworks) { |
850 |
foreach my $framework (@frameworks) { |
845 |
push @authorised_values, $framework->frameworkcode; |
851 |
push @authorised_values, $framework->frameworkcode; |
846 |
$authorised_lib{$framework->frameworkcode} = $framework->frameworktext; |
852 |
$authorised_lib{ $framework->frameworkcode } = $framework->frameworktext; |
847 |
} |
853 |
} |
848 |
} |
854 |
} elsif ( $authorised_value eq "cn_source" ) { |
849 |
elsif ( $authorised_value eq "cn_source" ) { |
855 |
my $class_sources = GetClassSources(); |
850 |
my $class_sources = GetClassSources(); |
|
|
851 |
my $default_source = C4::Context->preference("DefaultClassificationSource"); |
856 |
my $default_source = C4::Context->preference("DefaultClassificationSource"); |
852 |
foreach my $class_source (sort keys %$class_sources) { |
857 |
foreach my $class_source ( sort keys %$class_sources ) { |
853 |
next unless $class_sources->{$class_source}->{'used'} or |
858 |
next |
854 |
($class_source eq $default_source); |
859 |
unless $class_sources->{$class_source}->{'used'} |
|
|
860 |
or ( $class_source eq $default_source ); |
855 |
push @authorised_values, $class_source; |
861 |
push @authorised_values, $class_source; |
856 |
$authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'}; |
862 |
$authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'}; |
857 |
} |
863 |
} |
858 |
} |
864 |
} elsif ( $authorised_value eq "categorycode" ) { |
859 |
elsif ( $authorised_value eq "categorycode" ) { |
865 |
my @patron_categories = |
860 |
my @patron_categories = Koha::Patron::Categories->search({}, { order_by => ['description']})->as_list; |
866 |
Koha::Patron::Categories->search( {}, { order_by => ['description'] } )->as_list; |
861 |
%authorised_lib = map { $_->categorycode => $_->description } @patron_categories; |
867 |
%authorised_lib = map { $_->categorycode => $_->description } @patron_categories; |
862 |
push @authorised_values, $_->categorycode for @patron_categories; |
868 |
push @authorised_values, $_->categorycode for @patron_categories; |
863 |
} |
869 |
} elsif ( $authorised_value eq "cash_registers" ) { |
864 |
elsif ( $authorised_value eq "cash_registers" ) { |
|
|
865 |
my $sth = $dbh->prepare("SELECT id, name FROM cash_registers ORDER BY description"); |
870 |
my $sth = $dbh->prepare("SELECT id, name FROM cash_registers ORDER BY description"); |
866 |
$sth->execute; |
871 |
$sth->execute; |
867 |
while ( my ( $id, $name ) = $sth->fetchrow_array ) { |
872 |
while ( my ( $id, $name ) = $sth->fetchrow_array ) { |
868 |
push @authorised_values, $id; |
873 |
push @authorised_values, $id; |
869 |
$authorised_lib{$id} = $name; |
874 |
$authorised_lib{$id} = $name; |
870 |
} |
875 |
} |
871 |
} |
876 |
} elsif ( $authorised_value eq "debit_types" ) { |
872 |
elsif ( $authorised_value eq "debit_types" ) { |
|
|
873 |
my $sth = $dbh->prepare("SELECT code, description FROM account_debit_types ORDER BY code"); |
877 |
my $sth = $dbh->prepare("SELECT code, description FROM account_debit_types ORDER BY code"); |
874 |
$sth->execute; |
878 |
$sth->execute; |
875 |
while ( my ( $code, $description ) = $sth->fetchrow_array ) { |
879 |
while ( my ( $code, $description ) = $sth->fetchrow_array ) { |
876 |
push @authorised_values, $code; |
880 |
push @authorised_values, $code; |
877 |
$authorised_lib{$code} = $description; |
881 |
$authorised_lib{$code} = $description; |
878 |
} |
882 |
} |
879 |
} |
883 |
} elsif ( $authorised_value eq "credit_types" ) { |
880 |
elsif ( $authorised_value eq "credit_types" ) { |
|
|
881 |
my $sth = $dbh->prepare("SELECT code, description FROM account_credit_types ORDER BY code"); |
884 |
my $sth = $dbh->prepare("SELECT code, description FROM account_credit_types ORDER BY code"); |
882 |
$sth->execute; |
885 |
$sth->execute; |
883 |
while ( my ( $code, $description ) = $sth->fetchrow_array ) { |
886 |
while ( my ( $code, $description ) = $sth->fetchrow_array ) { |
884 |
push @authorised_values, $code; |
887 |
push @authorised_values, $code; |
885 |
$authorised_lib{$code} = $description; |
888 |
$authorised_lib{$code} = $description; |
886 |
} |
889 |
} |
887 |
} |
890 |
} else { |
888 |
else { |
891 |
if ( Koha::AuthorisedValues->search( { category => $authorised_value } )->count ) { |
889 |
if ( Koha::AuthorisedValues->search({ category => $authorised_value })->count ) { |
|
|
890 |
my $query = ' |
892 |
my $query = ' |
891 |
SELECT authorised_value,lib |
893 |
SELECT authorised_value,lib |
892 |
FROM authorised_values |
894 |
FROM authorised_values |
Lines 894-913
if ($op eq 'run'){
Link Here
|
894 |
ORDER BY lib |
896 |
ORDER BY lib |
895 |
'; |
897 |
'; |
896 |
my $authorised_values_sth = $dbh->prepare($query); |
898 |
my $authorised_values_sth = $dbh->prepare($query); |
897 |
$authorised_values_sth->execute( $authorised_value); |
899 |
$authorised_values_sth->execute($authorised_value); |
898 |
|
900 |
|
899 |
while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) { |
901 |
while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) { |
900 |
push @authorised_values, $value; |
902 |
push @authorised_values, $value; |
901 |
$authorised_lib{$value} = $lib; |
903 |
$authorised_lib{$value} = $lib; |
|
|
904 |
|
902 |
# For item location, we show the code and the libelle |
905 |
# For item location, we show the code and the libelle |
903 |
$authorised_lib{$value} = $lib; |
906 |
$authorised_lib{$value} = $lib; |
904 |
} |
907 |
} |
905 |
} else { |
908 |
} else { |
|
|
909 |
|
906 |
# not exists $authorised_value_categories{$authorised_value}) |
910 |
# not exists $authorised_value_categories{$authorised_value}) |
907 |
push @authval_errors, {'entry' => $text, |
911 |
push @authval_errors, { |
908 |
'auth_val' => $authorised_value }; |
912 |
'entry' => $text, |
|
|
913 |
'auth_val' => $authorised_value |
914 |
}; |
915 |
|
909 |
# tell the template there's an error |
916 |
# tell the template there's an error |
910 |
$template->param( auth_val_error => 1 ); |
917 |
$template->param( auth_val_error => 1 ); |
|
|
918 |
|
911 |
# skip scrolling list creation and params push |
919 |
# skip scrolling list creation and params push |
912 |
next; |
920 |
next; |
913 |
} |
921 |
} |
Lines 915-974
if ($op eq 'run'){
Link Here
|
915 |
$labelid = $text; |
923 |
$labelid = $text; |
916 |
$labelid =~ s/\W//g; |
924 |
$labelid =~ s/\W//g; |
917 |
$input = { |
925 |
$input = { |
918 |
name => "sql_params", |
926 |
name => "sql_params", |
919 |
id => "sql_params_".$labelid, |
927 |
id => "sql_params_" . $labelid, |
920 |
values => \@authorised_values, |
928 |
values => \@authorised_values, |
921 |
labels => \%authorised_lib, |
929 |
labels => \%authorised_lib, |
922 |
}; |
930 |
}; |
923 |
} |
931 |
} |
924 |
|
932 |
|
925 |
push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value_all, 'include_all' => $all }; |
933 |
push @tmpl_parameters, |
|
|
934 |
{ |
935 |
'entry' => $text, 'input' => $input, 'labelid' => $labelid, |
936 |
'name' => $text . $sep . $authorised_value_all, 'include_all' => $all |
937 |
}; |
926 |
} |
938 |
} |
927 |
$template->param('sql' => $sql, |
939 |
$template->param( |
928 |
'name' => $name, |
940 |
'sql' => $sql, |
929 |
'notes' => $notes, |
941 |
'name' => $name, |
930 |
'sql_params' => \@tmpl_parameters, |
942 |
'notes' => $notes, |
931 |
'auth_val_errors' => \@authval_errors, |
943 |
'sql_params' => \@tmpl_parameters, |
932 |
'enter_params' => 1, |
944 |
'auth_val_errors' => \@authval_errors, |
933 |
'id' => $report_id, |
945 |
'enter_params' => 1, |
934 |
); |
946 |
'id' => $report_id, |
|
|
947 |
); |
935 |
} else { |
948 |
} else { |
936 |
my ($sql,$header_types) = $report->prep_report( \@param_names, \@sql_params ); |
949 |
my ( $sql, $header_types ) = $report->prep_report( \@param_names, \@sql_params ); |
937 |
$template->param(header_types => $header_types); |
950 |
$template->param( header_types => $header_types ); |
938 |
my ( $sth, $errors ) = execute_query( |
951 |
my ( $sth, $errors ) = execute_query( |
939 |
{ |
952 |
{ |
940 |
sql => $sql, |
953 |
sql => $sql, |
941 |
offset => $offset, |
954 |
offset => $offset, |
942 |
limit => $limit, |
955 |
limit => $limit, |
943 |
report_id => $report_id, |
956 |
report_id => $report_id, |
944 |
} |
957 |
} |
945 |
); |
958 |
); |
946 |
my $total; |
959 |
my $total; |
947 |
if (!$sth) { |
960 |
if ( !$sth ) { |
948 |
die "execute_query failed to return sth for report $report_id: $sql"; |
961 |
die "execute_query failed to return sth for report $report_id: $sql"; |
949 |
} elsif ( !$errors ) { |
962 |
} elsif ( !$errors ) { |
950 |
$total = nb_rows($sql) || 0; |
963 |
$total = nb_rows($sql) || 0; |
951 |
my $headers = header_cell_loop($sth); |
964 |
my $headers = header_cell_loop($sth); |
952 |
$template->param(header_row => $headers); |
965 |
$template->param( header_row => $headers ); |
953 |
while (my $row = $sth->fetchrow_arrayref()) { |
966 |
while ( my $row = $sth->fetchrow_arrayref() ) { |
954 |
my @cells = map { +{ cell => $_ } } @$row; |
967 |
my @cells = map { +{ cell => $_ } } @$row; |
955 |
push @rows, { cells => \@cells }; |
968 |
push @rows, { cells => \@cells }; |
956 |
} |
969 |
} |
957 |
if( $want_full_chart ){ |
970 |
if ($want_full_chart) { |
958 |
my ( $sth2, $errors2 ) = execute_query( { sql => $sql, report_id => $report_id } ); |
971 |
my ( $sth2, $errors2 ) = execute_query( { sql => $sql, report_id => $report_id } ); |
959 |
while (my $row = $sth2->fetchrow_arrayref()) { |
972 |
while ( my $row = $sth2->fetchrow_arrayref() ) { |
960 |
my @cells = map { +{ cell => $_ } } @$row; |
973 |
my @cells = map { +{ cell => $_ } } @$row; |
961 |
push @allrows, { cells => \@cells }; |
974 |
push @allrows, { cells => \@cells }; |
962 |
} |
975 |
} |
963 |
} |
976 |
} |
964 |
|
977 |
|
965 |
my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0); |
978 |
my $totpages = int( $total / $limit ) + ( ( $total % $limit ) > 0 ? 1 : 0 ); |
966 |
my $url = "/cgi-bin/koha/reports/guided_reports.pl?id=$report_id&op=run&limit=$limit&want_full_chart=$want_full_chart"; |
979 |
my $url = |
|
|
980 |
"/cgi-bin/koha/reports/guided_reports.pl?id=$report_id&op=run&limit=$limit&want_full_chart=$want_full_chart"; |
967 |
if (@param_names) { |
981 |
if (@param_names) { |
968 |
$url = join('&param_name=', $url, map { URI::Escape::uri_escape_utf8($_) } @param_names); |
982 |
$url = join( '&param_name=', $url, map { URI::Escape::uri_escape_utf8($_) } @param_names ); |
969 |
} |
983 |
} |
970 |
if (@sql_params) { |
984 |
if (@sql_params) { |
971 |
$url = join('&sql_params=', $url, map { URI::Escape::uri_escape_utf8($_) } @sql_params); |
985 |
$url = join( '&sql_params=', $url, map { URI::Escape::uri_escape_utf8($_) } @sql_params ); |
972 |
} |
986 |
} |
973 |
|
987 |
|
974 |
if ($template_id) { |
988 |
if ($template_id) { |
Lines 987-995
if ($op eq 'run'){
Link Here
|
987 |
} |
1001 |
} |
988 |
|
1002 |
|
989 |
$template->param( |
1003 |
$template->param( |
990 |
'results' => \@rows, |
1004 |
'results' => \@rows, |
991 |
'allresults' => \@allrows, |
1005 |
'allresults' => \@allrows, |
992 |
'pagination_bar' => pagination_bar($url, $totpages, scalar $input->param('page')), |
1006 |
'pagination_bar' => pagination_bar( $url, $totpages, scalar $input->param('page') ), |
993 |
'unlimited_total' => $total, |
1007 |
'unlimited_total' => $total, |
994 |
); |
1008 |
); |
995 |
} |
1009 |
} |
Lines 1005-1017
if ($op eq 'run'){
Link Here
|
1005 |
'param_names' => \@param_names, |
1019 |
'param_names' => \@param_names, |
1006 |
); |
1020 |
); |
1007 |
} |
1021 |
} |
1008 |
} |
1022 |
} else { |
1009 |
else { |
|
|
1010 |
push @errors, { no_sql_for_id => $report_id }; |
1023 |
push @errors, { no_sql_for_id => $report_id }; |
1011 |
} |
1024 |
} |
1012 |
} |
1025 |
} |
1013 |
|
1026 |
|
1014 |
if ( $op eq 'list' || $op eq 'convert') { |
1027 |
if ( $op eq 'list' || $op eq 'convert' ) { |
1015 |
|
1028 |
|
1016 |
if ( $op eq 'convert' ) { |
1029 |
if ( $op eq 'convert' ) { |
1017 |
my $report_id = $input->param('id'); |
1030 |
my $report_id = $input->param('id'); |
Lines 1036-1048
if ( $op eq 'list' || $op eq 'convert') {
Link Here
|
1036 |
|
1049 |
|
1037 |
# use a saved report |
1050 |
# use a saved report |
1038 |
# get list of reports and display them |
1051 |
# get list of reports and display them |
1039 |
my $group = $input->param('group'); |
1052 |
my $group = $input->param('group'); |
1040 |
my $subgroup = $input->param('subgroup'); |
1053 |
my $subgroup = $input->param('subgroup'); |
1041 |
$filter->{group} = $group; |
1054 |
$filter->{group} = $group; |
1042 |
$filter->{subgroup} = $subgroup; |
1055 |
$filter->{subgroup} = $subgroup; |
1043 |
my $reports = get_saved_reports($filter); |
1056 |
my $reports = get_saved_reports($filter); |
1044 |
my $has_obsolete_reports; |
1057 |
my $has_obsolete_reports; |
1045 |
for my $report ( @$reports ) { |
1058 |
for my $report (@$reports) { |
1046 |
$report->{results} = C4::Reports::Guided::get_results( $report->{id} ); |
1059 |
$report->{results} = C4::Reports::Guided::get_results( $report->{id} ); |
1047 |
if ( $report->{savedsql} =~ m|biblioitems| and $report->{savedsql} =~ m|marcxml| ) { |
1060 |
if ( $report->{savedsql} =~ m|biblioitems| and $report->{savedsql} =~ m|marcxml| ) { |
1048 |
$report->{seems_obsolete} = 1; |
1061 |
$report->{seems_obsolete} = 1; |
Lines 1050-1056
if ( $op eq 'list' || $op eq 'convert') {
Link Here
|
1050 |
} |
1063 |
} |
1051 |
} |
1064 |
} |
1052 |
$template->param( |
1065 |
$template->param( |
1053 |
'manamsg' => $input->param('manamsg') || '', |
1066 |
'manamsg' => $input->param('manamsg') || '', |
1054 |
'saved1' => 1, |
1067 |
'saved1' => 1, |
1055 |
'savedreports' => $reports, |
1068 |
'savedreports' => $reports, |
1056 |
'usecache' => $usecache, |
1069 |
'usecache' => $usecache, |
Lines 1059-1074
if ( $op eq 'list' || $op eq 'convert') {
Link Here
|
1059 |
has_obsolete_reports => $has_obsolete_reports, |
1072 |
has_obsolete_reports => $has_obsolete_reports, |
1060 |
); |
1073 |
); |
1061 |
} |
1074 |
} |
|
|
1075 |
|
1062 |
# pass $sth, get back an array of names for the column headers |
1076 |
# pass $sth, get back an array of names for the column headers |
1063 |
sub header_cell_values { |
1077 |
sub header_cell_values { |
1064 |
my $sth = shift or return (); |
1078 |
my $sth = shift or return (); |
1065 |
return '' unless ($sth->{NAME}); |
1079 |
return '' unless ( $sth->{NAME} ); |
1066 |
return @{$sth->{NAME}}; |
1080 |
return @{ $sth->{NAME} }; |
1067 |
} |
1081 |
} |
1068 |
|
1082 |
|
1069 |
# pass $sth, get back a TMPL_LOOP-able set of names for the column headers |
1083 |
# pass $sth, get back a TMPL_LOOP-able set of names for the column headers |
1070 |
sub header_cell_loop { |
1084 |
sub header_cell_loop { |
1071 |
my @headers = map { +{ cell => decode('UTF-8',$_) } } header_cell_values (shift); |
1085 |
my @headers = map { +{ cell => decode( 'UTF-8', $_ ) } } header_cell_values(shift); |
1072 |
return \@headers; |
1086 |
return \@headers; |
1073 |
} |
1087 |
} |
1074 |
|
1088 |
|
Lines 1079-1130
sub get_tables {
Link Here
|
1079 |
my $tables = $cache->get_from_cache("Reports-SQL_tables-for-autocomplete"); |
1093 |
my $tables = $cache->get_from_cache("Reports-SQL_tables-for-autocomplete"); |
1080 |
|
1094 |
|
1081 |
return $tables |
1095 |
return $tables |
1082 |
if $tables; |
1096 |
if $tables; |
1083 |
|
1097 |
|
1084 |
$tables = C4::Reports::Guided->get_all_tables(); |
1098 |
$tables = C4::Reports::Guided->get_all_tables(); |
1085 |
for my $table (@{$tables}) { |
1099 |
for my $table ( @{$tables} ) { |
1086 |
my $sql = "SHOW COLUMNS FROM $table"; |
1100 |
my $sql = "SHOW COLUMNS FROM $table"; |
1087 |
my $rows = C4::Context->dbh->selectall_arrayref($sql, { Slice => {} }); |
1101 |
my $rows = C4::Context->dbh->selectall_arrayref( $sql, { Slice => {} } ); |
1088 |
for my $row (@{$rows}) { |
1102 |
for my $row ( @{$rows} ) { |
1089 |
push @{$result->{$table}}, $row->{Field}; |
1103 |
push @{ $result->{$table} }, $row->{Field}; |
1090 |
} |
1104 |
} |
1091 |
} |
1105 |
} |
1092 |
$cache->set_in_cache("Reports-SQL_tables-for-autocomplete",$result); |
1106 |
$cache->set_in_cache( "Reports-SQL_tables-for-autocomplete", $result ); |
1093 |
return $result; |
1107 |
return $result; |
1094 |
} |
1108 |
} |
1095 |
|
1109 |
|
1096 |
foreach (1..6) { |
1110 |
foreach ( 1 .. 6 ) { |
1097 |
$template->{VARS}->{'build' . $_} and last; |
1111 |
$template->{VARS}->{ 'build' . $_ } and last; |
1098 |
} |
1112 |
} |
1099 |
$template->param( 'referer' => $input->referer(), |
1113 |
$template->param( |
1100 |
); |
1114 |
'referer' => $input->referer(), |
|
|
1115 |
); |
1101 |
|
1116 |
|
1102 |
output_html_with_http_headers $input, $cookie, $template->output; |
1117 |
output_html_with_http_headers $input, $cookie, $template->output; |
1103 |
|
1118 |
|
1104 |
sub groups_with_subgroups { |
1119 |
sub groups_with_subgroups { |
1105 |
my ($group, $subgroup) = @_; |
1120 |
my ( $group, $subgroup ) = @_; |
1106 |
|
1121 |
|
1107 |
my $groups_with_subgroups = get_report_groups(); |
1122 |
my $groups_with_subgroups = get_report_groups(); |
1108 |
my @g_sg; |
1123 |
my @g_sg; |
1109 |
my @sorted_keys = sort { |
1124 |
my @sorted_keys = sort { $groups_with_subgroups->{$a}->{name} cmp $groups_with_subgroups->{$b}->{name} } |
1110 |
$groups_with_subgroups->{$a}->{name} cmp $groups_with_subgroups->{$b}->{name} |
1125 |
keys %$groups_with_subgroups; |
1111 |
} keys %$groups_with_subgroups; |
|
|
1112 |
foreach my $g_id (@sorted_keys) { |
1126 |
foreach my $g_id (@sorted_keys) { |
1113 |
my $v = $groups_with_subgroups->{$g_id}; |
1127 |
my $v = $groups_with_subgroups->{$g_id}; |
1114 |
my @subgroups; |
1128 |
my @subgroups; |
1115 |
if (my $sg = $v->{subgroups}) { |
1129 |
if ( my $sg = $v->{subgroups} ) { |
1116 |
foreach my $sg_id (sort { $sg->{$a} cmp $sg->{$b} } keys %$sg) { |
1130 |
foreach my $sg_id ( sort { $sg->{$a} cmp $sg->{$b} } keys %$sg ) { |
1117 |
push @subgroups, { |
1131 |
push @subgroups, { |
1118 |
id => $sg_id, |
1132 |
id => $sg_id, |
1119 |
name => $sg->{$sg_id}, |
1133 |
name => $sg->{$sg_id}, |
1120 |
selected => ($group && $g_id eq $group && $subgroup && $sg_id eq $subgroup ), |
1134 |
selected => ( $group && $g_id eq $group && $subgroup && $sg_id eq $subgroup ), |
1121 |
}; |
1135 |
}; |
1122 |
} |
1136 |
} |
1123 |
} |
1137 |
} |
1124 |
push @g_sg, { |
1138 |
push @g_sg, { |
1125 |
id => $g_id, |
1139 |
id => $g_id, |
1126 |
name => $v->{name}, |
1140 |
name => $v->{name}, |
1127 |
selected => ($group && $g_id eq $group), |
1141 |
selected => ( $group && $g_id eq $group ), |
1128 |
subgroups => \@subgroups, |
1142 |
subgroups => \@subgroups, |
1129 |
}; |
1143 |
}; |
1130 |
} |
1144 |
} |
Lines 1132-1163
sub groups_with_subgroups {
Link Here
|
1132 |
} |
1146 |
} |
1133 |
|
1147 |
|
1134 |
sub create_non_existing_group_and_subgroup { |
1148 |
sub create_non_existing_group_and_subgroup { |
1135 |
my ($input, $group, $subgroup) = @_; |
1149 |
my ( $input, $group, $subgroup ) = @_; |
1136 |
if (defined $group and $group ne '') { |
1150 |
if ( defined $group and $group ne '' ) { |
1137 |
my $report_groups = C4::Reports::Guided::get_report_groups; |
1151 |
my $report_groups = C4::Reports::Guided::get_report_groups; |
1138 |
if (not exists $report_groups->{$group}) { |
1152 |
if ( not exists $report_groups->{$group} ) { |
1139 |
my $groupdesc = $input->param('groupdesc') // $group; |
1153 |
my $groupdesc = $input->param('groupdesc') // $group; |
1140 |
Koha::AuthorisedValue->new({ |
1154 |
Koha::AuthorisedValue->new( |
1141 |
category => 'REPORT_GROUP', |
1155 |
{ |
1142 |
authorised_value => $group, |
1156 |
category => 'REPORT_GROUP', |
1143 |
lib => $groupdesc, |
1157 |
authorised_value => $group, |
1144 |
})->store; |
1158 |
lib => $groupdesc, |
1145 |
my $cache_key = "AuthorisedValues-REPORT_GROUP-0-".C4::Context->userenv->{"branch"}; |
1159 |
} |
1146 |
my $cache = Koha::Caches->get_instance(); |
1160 |
)->store; |
1147 |
my $result = $cache->clear_from_cache($cache_key); |
1161 |
my $cache_key = "AuthorisedValues-REPORT_GROUP-0-" . C4::Context->userenv->{"branch"}; |
|
|
1162 |
my $cache = Koha::Caches->get_instance(); |
1163 |
my $result = $cache->clear_from_cache($cache_key); |
1148 |
} |
1164 |
} |
1149 |
if (defined $subgroup and $subgroup ne '') { |
1165 |
if ( defined $subgroup and $subgroup ne '' ) { |
1150 |
if (not exists $report_groups->{$group}->{subgroups}->{$subgroup}) { |
1166 |
if ( not exists $report_groups->{$group}->{subgroups}->{$subgroup} ) { |
1151 |
my $subgroupdesc = $input->param('subgroupdesc') // $subgroup; |
1167 |
my $subgroupdesc = $input->param('subgroupdesc') // $subgroup; |
1152 |
Koha::AuthorisedValue->new({ |
1168 |
Koha::AuthorisedValue->new( |
1153 |
category => 'REPORT_SUBGROUP', |
1169 |
{ |
1154 |
authorised_value => $subgroup, |
1170 |
category => 'REPORT_SUBGROUP', |
1155 |
lib => $subgroupdesc, |
1171 |
authorised_value => $subgroup, |
1156 |
lib_opac => $group, |
1172 |
lib => $subgroupdesc, |
1157 |
})->store; |
1173 |
lib_opac => $group, |
1158 |
my $cache_key = "AuthorisedValues-REPORT_SUBGROUP-0-".C4::Context->userenv->{"branch"}; |
1174 |
} |
1159 |
my $cache = Koha::Caches->get_instance(); |
1175 |
)->store; |
1160 |
my $result = $cache->clear_from_cache($cache_key); |
1176 |
my $cache_key = "AuthorisedValues-REPORT_SUBGROUP-0-" . C4::Context->userenv->{"branch"}; |
|
|
1177 |
my $cache = Koha::Caches->get_instance(); |
1178 |
my $result = $cache->clear_from_cache($cache_key); |
1161 |
} |
1179 |
} |
1162 |
} |
1180 |
} |
1163 |
} |
1181 |
} |
1164 |
- |
|
|