|
Lines 83-101
my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
Link Here
|
| 83 |
my $session_id = $input->cookie('CGISESSID'); |
83 |
my $session_id = $input->cookie('CGISESSID'); |
| 84 |
my $session = $session_id ? get_session($session_id) : undef; |
84 |
my $session = $session_id ? get_session($session_id) : undef; |
| 85 |
|
85 |
|
| 86 |
$template->param( templates => Koha::Notice::Templates->search( { module => 'report' } ) ); |
|
|
| 87 |
|
| 88 |
my $filter; |
86 |
my $filter; |
| 89 |
if ( $input->param("filter_set") or $input->param('clear_filters') ) { |
87 |
if ( $input->param("filter_set") or $input->param('clear_filters') ) { |
| 90 |
$filter = {}; |
88 |
$filter = {}; |
| 91 |
$filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword group subgroup/; |
89 |
$filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword show_all_reports group subgroup/; |
|
|
90 |
|
| 91 |
if ( $input->param('clear_filters') ) { |
| 92 |
$filter->{show_all_reports} = 1; |
| 93 |
} |
| 94 |
|
| 92 |
$session->param( 'report_filter', $filter ) if $session; |
95 |
$session->param( 'report_filter', $filter ) if $session; |
| 93 |
$template->param( 'filter_set' => 1 ); |
96 |
$template->param( 'filter_set' => 1 ); |
| 94 |
} elsif ( $session and not $input->param('clear_filters') ) { |
97 |
} elsif ( $session and not $input->param('clear_filters') ) { |
| 95 |
$filter = $session->param('report_filter'); |
98 |
$filter = $session->param('report_filter'); |
| 96 |
} |
99 |
} |
| 97 |
|
100 |
|
| 98 |
my @errors = (); |
101 |
my @errors = (); |
|
|
102 |
my %OP_REQUIRES_REPORT = map { $_ => 1 } qw(show edit_form duplicate cud-update_sql cud-update_and_run_sql export run); |
| 103 |
my $report_id = $input->param('id'); |
| 104 |
my $report; |
| 105 |
my $access_blocked; |
| 106 |
if ( $OP_REQUIRES_REPORT{$op} && $report_id ) { |
| 107 |
$report = Koha::Reports->find($report_id); |
| 108 |
if ($report) { |
| 109 |
my $patron = Koha::Patrons->find($borrowernumber); |
| 110 |
unless ( $report->can_access($patron) ) { |
| 111 |
$template->param( access_denied => 1, denied_op => $op, id => $report_id ); |
| 112 |
$access_blocked = 1; |
| 113 |
} |
| 114 |
} else { |
| 115 |
push @errors, { no_sql_for_id => $report_id }; |
| 116 |
} |
| 117 |
} |
| 118 |
|
| 119 |
if ($access_blocked) { |
| 120 |
output_html_with_http_headers $input, $cookie, $template->output; |
| 121 |
exit; |
| 122 |
} |
| 123 |
|
| 99 |
if ( !$op ) { |
124 |
if ( !$op ) { |
| 100 |
$template->param( 'start' => 1 ); |
125 |
$template->param( 'start' => 1 ); |
| 101 |
|
126 |
|
|
Lines 116-262
if ( !$op ) {
Link Here
|
| 116 |
$op = 'list'; |
141 |
$op = 'list'; |
| 117 |
|
142 |
|
| 118 |
} elsif ( $op eq 'show' ) { |
143 |
} elsif ( $op eq 'show' ) { |
| 119 |
|
144 |
if ($report) { |
| 120 |
my $id = $input->param('id'); |
145 |
$template->param( |
| 121 |
my $report = Koha::Reports->find($id); |
146 |
'id' => $report_id, |
| 122 |
$template->param( |
147 |
'reportname' => $report->report_name, |
| 123 |
'id' => $id, |
148 |
'notes' => $report->notes, |
| 124 |
'reportname' => $report->report_name, |
149 |
'sql' => $report->savedsql, |
| 125 |
'notes' => $report->notes, |
150 |
'showsql' => 1, |
| 126 |
'sql' => $report->savedsql, |
151 |
'mana_success' => scalar $input->param('mana_success'), |
| 127 |
'showsql' => 1, |
152 |
'mana_id' => $report->{mana_id}, |
| 128 |
'mana_success' => scalar $input->param('mana_success'), |
153 |
'mana_comments' => $report->{comments} |
| 129 |
'mana_id' => $report->{mana_id}, |
154 |
); |
| 130 |
'mana_comments' => $report->{comments} |
155 |
} |
| 131 |
); |
|
|
| 132 |
|
156 |
|
| 133 |
} elsif ( $op eq 'edit_form' ) { |
157 |
} elsif ( $op eq 'edit_form' ) { |
| 134 |
my $id = $input->param('id'); |
158 |
if ($report) { |
| 135 |
my $report = Koha::Reports->find($id); |
159 |
my $group = $report->report_group; |
| 136 |
my $group = $report->report_group; |
160 |
my $subgroup = $report->report_subgroup; |
| 137 |
my $subgroup = $report->report_subgroup; |
161 |
my $tables = get_tables(); |
| 138 |
my $tables = get_tables(); |
162 |
$template->param( |
| 139 |
$template->param( |
163 |
'sql' => $report->savedsql, |
| 140 |
'sql' => $report->savedsql, |
164 |
'reportname' => $report->report_name, |
| 141 |
'reportname' => $report->report_name, |
165 |
'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ), |
| 142 |
'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ), |
166 |
'notes' => $report->notes, |
| 143 |
'notes' => $report->notes, |
167 |
'id' => $report_id, |
| 144 |
'id' => $id, |
168 |
'cache_expiry' => $report->cache_expiry, |
| 145 |
'cache_expiry' => $report->cache_expiry, |
169 |
'public' => $report->public, |
| 146 |
'public' => $report->public, |
170 |
'usecache' => $usecache, |
| 147 |
'usecache' => $usecache, |
171 |
'editsql' => 1, |
| 148 |
'editsql' => 1, |
172 |
'mana_id' => $report->{mana_id}, |
| 149 |
'mana_id' => $report->{mana_id}, |
173 |
'mana_comments' => $report->{comments}, |
| 150 |
'mana_comments' => $report->{comments}, |
174 |
'tables' => $tables, |
| 151 |
'tables' => $tables |
175 |
'report' => $report, |
| 152 |
); |
176 |
); |
|
|
177 |
} |
| 153 |
|
178 |
|
| 154 |
} elsif ( $op eq 'cud-update_sql' || $op eq 'cud-update_and_run_sql' ) { |
179 |
} elsif ( $op eq 'cud-update_sql' || $op eq 'cud-update_and_run_sql' ) { |
| 155 |
my $id = $input->param('id'); |
180 |
if ($report) { |
| 156 |
my $sql = $input->param('sql'); |
181 |
my $id = $report_id; |
| 157 |
my $reportname = $input->param('reportname'); |
182 |
my $sql = $input->param('sql'); |
| 158 |
my $group = $input->param('group'); |
183 |
my $reportname = $input->param('reportname'); |
| 159 |
my $subgroup = $input->param('subgroup'); |
184 |
my $group = $input->param('group'); |
| 160 |
my $notes = $input->param('notes'); |
185 |
my $subgroup = $input->param('subgroup'); |
| 161 |
my $cache_expiry = $input->param('cache_expiry'); |
186 |
my $notes = $input->param('notes'); |
| 162 |
my $cache_expiry_units = $input->param('cache_expiry_units'); |
187 |
my $cache_expiry = $input->param('cache_expiry'); |
| 163 |
my $public = $input->param('public'); |
188 |
my $cache_expiry_units = $input->param('cache_expiry_units'); |
| 164 |
my $save_anyway = $input->param('save_anyway'); |
189 |
my $public = $input->param('public'); |
| 165 |
my @errors; |
190 |
my $save_anyway = $input->param('save_anyway'); |
| 166 |
my $tables = get_tables(); |
191 |
my @errors; |
|
|
192 |
my $tables = get_tables(); |
| 193 |
my @branches = grep { $_ ne q{} } $input->multi_param('branches'); |
| 194 |
|
| 195 |
# if we have the units, then we came from creating a report from SQL and thus need to handle converting units |
| 196 |
if ($cache_expiry_units) { |
| 197 |
if ( $cache_expiry_units eq "minutes" ) { |
| 198 |
$cache_expiry *= 60; |
| 199 |
} elsif ( $cache_expiry_units eq "hours" ) { |
| 200 |
$cache_expiry *= 3600; # 60 * 60 |
| 201 |
} elsif ( $cache_expiry_units eq "days" ) { |
| 202 |
$cache_expiry *= 86400; # 60 * 60 * 24 |
| 203 |
} |
| 204 |
} |
| 167 |
|
205 |
|
| 168 |
# if we have the units, then we came from creating a report from SQL and thus need to handle converting units |
206 |
# 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 |
| 169 |
if ($cache_expiry_units) { |
207 |
if ( $cache_expiry >= 2592000 ) { |
| 170 |
if ( $cache_expiry_units eq "minutes" ) { |
208 |
push @errors, { cache_expiry => $cache_expiry }; |
| 171 |
$cache_expiry *= 60; |
|
|
| 172 |
} elsif ( $cache_expiry_units eq "hours" ) { |
| 173 |
$cache_expiry *= 3600; # 60 * 60 |
| 174 |
} elsif ( $cache_expiry_units eq "days" ) { |
| 175 |
$cache_expiry *= 86400; # 60 * 60 * 24 |
| 176 |
} |
209 |
} |
| 177 |
} |
|
|
| 178 |
|
210 |
|
| 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 |
211 |
create_non_existing_group_and_subgroup( $input, $group, $subgroup ); |
| 180 |
if ( $cache_expiry >= 2592000 ) { |
|
|
| 181 |
push @errors, { cache_expiry => $cache_expiry }; |
| 182 |
} |
| 183 |
|
212 |
|
| 184 |
create_non_existing_group_and_subgroup( $input, $group, $subgroup ); |
213 |
my ( $is_sql_valid, $validation_errors ) = Koha::Report->new( { savedsql => $sql } )->is_sql_valid; |
|
|
214 |
push( @errors, @$validation_errors ) unless $is_sql_valid; |
| 185 |
|
215 |
|
| 186 |
my ( $is_sql_valid, $validation_errors ) = Koha::Report->new( { savedsql => $sql } )->is_sql_valid; |
216 |
if (@errors) { |
| 187 |
push( @errors, @$validation_errors ) unless $is_sql_valid; |
217 |
$template->param( |
|
|
218 |
'errors' => \@errors, |
| 219 |
'sql' => $sql, |
| 220 |
); |
| 221 |
} else { |
| 188 |
|
222 |
|
| 189 |
if (@errors) { |
223 |
# Check defined SQL parameters for authorised value validity |
| 190 |
$template->param( |
224 |
my $problematic_authvals = ValidateSQLParameters($sql); |
| 191 |
'errors' => \@errors, |
|
|
| 192 |
'sql' => $sql, |
| 193 |
); |
| 194 |
} else { |
| 195 |
|
225 |
|
| 196 |
# Check defined SQL parameters for authorised value validity |
226 |
if ( scalar @$problematic_authvals > 0 && not $save_anyway ) { |
| 197 |
my $problematic_authvals = ValidateSQLParameters($sql); |
|
|
| 198 |
|
227 |
|
| 199 |
if ( scalar @$problematic_authvals > 0 && not $save_anyway ) { |
228 |
# There's at least one problematic parameter, report to the |
|
|
229 |
# GUI and provide all user input for further actions |
| 230 |
$template->param( |
| 231 |
'id' => $id, |
| 232 |
'sql' => $sql, |
| 233 |
'reportname' => $reportname, |
| 234 |
'group' => $group, |
| 235 |
'subgroup' => $subgroup, |
| 236 |
'notes' => $notes, |
| 237 |
'public' => $public, |
| 238 |
'problematic_authvals' => $problematic_authvals, |
| 239 |
'warn_authval_problem' => 1, |
| 240 |
'phase_update' => 1, |
| 241 |
'branches' => @branches, |
| 242 |
'report' => $report, |
| 243 |
); |
| 200 |
|
244 |
|
| 201 |
# There's at least one problematic parameter, report to the |
245 |
} else { |
| 202 |
# GUI and provide all user input for further actions |
|
|
| 203 |
$template->param( |
| 204 |
'id' => $id, |
| 205 |
'sql' => $sql, |
| 206 |
'reportname' => $reportname, |
| 207 |
'group' => $group, |
| 208 |
'subgroup' => $subgroup, |
| 209 |
'notes' => $notes, |
| 210 |
'public' => $public, |
| 211 |
'problematic_authvals' => $problematic_authvals, |
| 212 |
'warn_authval_problem' => 1, |
| 213 |
'phase_update' => 1, |
| 214 |
); |
| 215 |
|
246 |
|
| 216 |
} else { |
247 |
# No params problem found or asked to save anyway |
|
|
248 |
update_sql( |
| 249 |
$id, |
| 250 |
{ |
| 251 |
sql => $sql, |
| 252 |
name => $reportname, |
| 253 |
group => $group, |
| 254 |
subgroup => $subgroup, |
| 255 |
notes => $notes, |
| 256 |
public => $public, |
| 257 |
cache_expiry => $cache_expiry, |
| 258 |
} |
| 259 |
); |
| 217 |
|
260 |
|
| 218 |
# No params problem found or asked to save anyway |
261 |
$report->store; |
| 219 |
update_sql( |
262 |
$report->replace_library_limits( \@branches ); |
| 220 |
$id, |
263 |
|
| 221 |
{ |
264 |
my $editsql = 1; |
| 222 |
sql => $sql, |
265 |
if ( $op eq 'cud-update_and_run_sql' ) { |
| 223 |
name => $reportname, |
266 |
$editsql = 0; |
| 224 |
group => $group, |
|
|
| 225 |
subgroup => $subgroup, |
| 226 |
notes => $notes, |
| 227 |
public => $public, |
| 228 |
cache_expiry => $cache_expiry, |
| 229 |
} |
267 |
} |
| 230 |
); |
|
|
| 231 |
|
268 |
|
| 232 |
my $editsql = 1; |
269 |
$template->param( |
|
|
270 |
'save_successful' => 1, |
| 271 |
'reportname' => $reportname, |
| 272 |
'id' => $id, |
| 273 |
'editsql' => $editsql, |
| 274 |
'sql' => $sql, |
| 275 |
'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ), |
| 276 |
'notes' => $notes, |
| 277 |
'cache_expiry' => $cache_expiry, |
| 278 |
'public' => $public, |
| 279 |
'usecache' => $usecache, |
| 280 |
'tables' => $tables, |
| 281 |
'report' => $report |
| 282 |
); |
| 283 |
logaction( "REPORTS", "MODIFY", $id, "$reportname | $sql" ) if C4::Context->preference("ReportsLog"); |
| 284 |
} |
| 285 |
if ($usecache) { |
| 286 |
$template->param( |
| 287 |
cache_expiry => $cache_expiry, |
| 288 |
cache_expiry_units => $cache_expiry_units, |
| 289 |
); |
| 290 |
} |
| 233 |
if ( $op eq 'cud-update_and_run_sql' ) { |
291 |
if ( $op eq 'cud-update_and_run_sql' ) { |
| 234 |
$editsql = 0; |
292 |
$op = 'run'; |
| 235 |
} |
293 |
} |
| 236 |
|
|
|
| 237 |
$template->param( |
| 238 |
'save_successful' => 1, |
| 239 |
'reportname' => $reportname, |
| 240 |
'id' => $id, |
| 241 |
'editsql' => $editsql, |
| 242 |
'sql' => $sql, |
| 243 |
'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ), |
| 244 |
'notes' => $notes, |
| 245 |
'cache_expiry' => $cache_expiry, |
| 246 |
'public' => $public, |
| 247 |
'usecache' => $usecache, |
| 248 |
'tables' => $tables |
| 249 |
); |
| 250 |
logaction( "REPORTS", "MODIFY", $id, "$reportname | $sql" ) if C4::Context->preference("ReportsLog"); |
| 251 |
} |
| 252 |
if ($usecache) { |
| 253 |
$template->param( |
| 254 |
cache_expiry => $cache_expiry, |
| 255 |
cache_expiry_units => $cache_expiry_units, |
| 256 |
); |
| 257 |
} |
| 258 |
if ( $op eq 'cud-update_and_run_sql' ) { |
| 259 |
$op = 'run'; |
| 260 |
} |
294 |
} |
| 261 |
} |
295 |
} |
| 262 |
|
296 |
|
|
Lines 514-519
if ( !$op ) {
Link Here
|
| 514 |
my $public = $input->param('public'); |
548 |
my $public = $input->param('public'); |
| 515 |
my $save_anyway = $input->param('save_anyway'); |
549 |
my $save_anyway = $input->param('save_anyway'); |
| 516 |
my $tables = get_tables(); |
550 |
my $tables = get_tables(); |
|
|
551 |
my @branches = grep { $_ ne q{} } $input->multi_param('branches'); |
| 517 |
|
552 |
|
| 518 |
# if we have the units, then we came from creating a report from SQL and thus need to handle converting units |
553 |
# if we have the units, then we came from creating a report from SQL and thus need to handle converting units |
| 519 |
if ($cache_expiry_units) { |
554 |
if ($cache_expiry_units) { |
|
Lines 591-596
if ( !$op ) {
Link Here
|
| 591 |
public => $public, |
626 |
public => $public, |
| 592 |
} |
627 |
} |
| 593 |
); |
628 |
); |
|
|
629 |
my $report = Koha::Reports->find($id); |
| 630 |
$report->replace_library_limits( \@branches ); |
| 631 |
|
| 594 |
logaction( "REPORTS", "ADD", $id, "$name | $sql" ) if C4::Context->preference("ReportsLog"); |
632 |
logaction( "REPORTS", "ADD", $id, "$name | $sql" ) if C4::Context->preference("ReportsLog"); |
| 595 |
$template->param( |
633 |
$template->param( |
| 596 |
'save_successful' => 1, |
634 |
'save_successful' => 1, |
|
Lines 603-609
if ( !$op ) {
Link Here
|
| 603 |
'cache_expiry' => $cache_expiry, |
641 |
'cache_expiry' => $cache_expiry, |
| 604 |
'public' => $public, |
642 |
'public' => $public, |
| 605 |
'usecache' => $usecache, |
643 |
'usecache' => $usecache, |
| 606 |
'tables' => $tables |
644 |
'tables' => $tables, |
|
|
645 |
'report' => $report, |
| 607 |
); |
646 |
); |
| 608 |
} |
647 |
} |
| 609 |
} |
648 |
} |
|
Lines 621-765
if ( !$op ) {
Link Here
|
| 621 |
} elsif ( $op eq 'export' ) { |
660 |
} elsif ( $op eq 'export' ) { |
| 622 |
|
661 |
|
| 623 |
# export results to tab separated text or CSV |
662 |
# export results to tab separated text or CSV |
| 624 |
my $report_id = $input->param('id'); |
663 |
if ($report) { |
| 625 |
my $report = Koha::Reports->find($report_id); |
664 |
my $sql = $report->savedsql; |
| 626 |
my $sql = $report->savedsql; |
665 |
my @param_names = $input->multi_param('param_name'); |
| 627 |
my @param_names = $input->multi_param('param_name'); |
666 |
my @sql_params = $input->multi_param('sql_params'); |
| 628 |
my @sql_params = $input->multi_param('sql_params'); |
667 |
my $format = $input->param('format'); |
| 629 |
my $format = $input->param('format'); |
668 |
my $reportname = $input->param('reportname'); |
| 630 |
my $reportname = $input->param('reportname'); |
669 |
my $reportfilename = |
| 631 |
my $reportfilename = |
670 |
$reportname ? "$report_id-$reportname-reportresults.$format" : "$report_id-reportresults.$format"; |
| 632 |
$reportname ? "$report_id-$reportname-reportresults.$format" : "$report_id-reportresults.$format"; |
671 |
my $scrubber = C4::Scrubber->new(); |
| 633 |
my $scrubber = C4::Scrubber->new(); |
672 |
|
| 634 |
|
673 |
( $sql, undef ) = $report->prep_report( \@param_names, \@sql_params, { export => 1 } ); |
| 635 |
( $sql, undef ) = $report->prep_report( \@param_names, \@sql_params, { export => 1 } ); |
674 |
my ( $sth, $q_errors ) = execute_query( { sql => $sql, report_id => $report_id } ); |
| 636 |
my ( $sth, $q_errors ) = execute_query( { sql => $sql, report_id => $report_id } ); |
675 |
unless ( $q_errors and @$q_errors ) { |
| 637 |
unless ( $q_errors and @$q_errors ) { |
676 |
my ( $type, $content ); |
| 638 |
my ( $type, $content ); |
677 |
if ( $format eq 'tab' ) { |
| 639 |
if ( $format eq 'tab' ) { |
678 |
$type = 'application/octet-stream'; |
| 640 |
$type = 'application/octet-stream'; |
679 |
$content .= join( "\t", header_cell_values($sth) ) . "\n"; |
| 641 |
$content .= join( "\t", header_cell_values($sth) ) . "\n"; |
680 |
$content = $scrubber->scrub( Encode::decode( 'UTF-8', $content ) ); |
| 642 |
$content = $scrubber->scrub( Encode::decode( 'UTF-8', $content ) ); |
681 |
while ( my $row = $sth->fetchrow_arrayref() ) { |
| 643 |
while ( my $row = $sth->fetchrow_arrayref() ) { |
682 |
$content .= $scrubber->scrub( join( "\t", map { $_ // '' } @$row ) ) . "\n"; |
| 644 |
$content .= $scrubber->scrub( join( "\t", map { $_ // '' } @$row ) ) . "\n"; |
683 |
} |
| 645 |
} |
684 |
} else { |
| 646 |
} else { |
685 |
if ( $format eq 'csv' ) { |
| 647 |
if ( $format eq 'csv' ) { |
686 |
my $delimiter = C4::Context->csv_delimiter; |
| 648 |
my $delimiter = C4::Context->csv_delimiter; |
687 |
$type = 'application/csv'; |
| 649 |
$type = 'application/csv'; |
|
|
| 650 |
|
| 651 |
# Add BOM for UTF-8 encoded CSV |
| 652 |
$content .= "\xEF\xBB\xBF"; |
| 653 |
|
688 |
|
| 654 |
my $csv = |
689 |
# Add BOM for UTF-8 encoded CSV |
| 655 |
Text::CSV::Encoded->new( { encoding_out => 'UTF-8', sep_char => $delimiter, formula => 'empty' } ); |
690 |
$content .= "\xEF\xBB\xBF"; |
| 656 |
$csv or die "Text::CSV::Encoded->new({binary => 1}) FAILED: " . Text::CSV::Encoded->error_diag(); |
|
|
| 657 |
if ( $csv->combine( header_cell_values($sth) ) ) { |
| 658 |
$content .= $scrubber->scrub( Encode::decode( 'UTF-8', $csv->string() ) ) . "\n"; |
| 659 |
|
691 |
|
| 660 |
} else { |
692 |
my $csv = |
| 661 |
push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() }; |
693 |
Text::CSV::Encoded->new( |
| 662 |
} |
694 |
{ encoding_out => 'UTF-8', sep_char => $delimiter, formula => 'empty' } ); |
| 663 |
while ( my $row = $sth->fetchrow_arrayref() ) { |
695 |
$csv or die "Text::CSV::Encoded->new({binary => 1}) FAILED: " . Text::CSV::Encoded->error_diag(); |
| 664 |
if ( $csv->combine(@$row) ) { |
696 |
if ( $csv->combine( header_cell_values($sth) ) ) { |
| 665 |
$content .= $scrubber->scrub( $csv->string() ) . "\n"; |
697 |
$content .= $scrubber->scrub( Encode::decode( 'UTF-8', $csv->string() ) ) . "\n"; |
| 666 |
|
698 |
|
| 667 |
} else { |
699 |
} else { |
| 668 |
push @$q_errors, { combine => $csv->error_diag() }; |
700 |
push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() }; |
| 669 |
} |
701 |
} |
| 670 |
} |
702 |
while ( my $row = $sth->fetchrow_arrayref() ) { |
| 671 |
} elsif ( $format eq 'ods' && C4::Context->preference('ReportsExportFormatODS') ) { |
703 |
if ( $csv->combine(@$row) ) { |
| 672 |
$type = 'application/vnd.oasis.opendocument.spreadsheet'; |
704 |
$content .= $scrubber->scrub( $csv->string() ) . "\n"; |
| 673 |
my $ods_fh = File::Temp->new( UNLINK => 0 ); |
705 |
|
| 674 |
my $ods_filepath = $ods_fh->filename; |
706 |
} else { |
| 675 |
my $ods_content; |
707 |
push @$q_errors, { combine => $csv->error_diag() }; |
| 676 |
|
708 |
} |
| 677 |
# First line is headers |
709 |
} |
| 678 |
my @headers = header_cell_values($sth); |
710 |
} elsif ( $format eq 'ods' && C4::Context->preference('ReportsExportFormatODS') ) { |
| 679 |
push @$ods_content, \@headers; |
711 |
$type = 'application/vnd.oasis.opendocument.spreadsheet'; |
| 680 |
|
712 |
my $ods_fh = File::Temp->new( UNLINK => 0 ); |
| 681 |
# Other line in Unicode |
713 |
my $ods_filepath = $ods_fh->filename; |
| 682 |
my $sql_rows = $sth->fetchall_arrayref(); |
714 |
my $ods_content; |
| 683 |
foreach my $sql_row (@$sql_rows) { |
715 |
|
| 684 |
my @content_row; |
716 |
# First line is headers |
| 685 |
foreach my $sql_cell (@$sql_row) { |
717 |
my @headers = header_cell_values($sth); |
| 686 |
push @content_row, $scrubber->scrub( Encode::encode( 'UTF8', $sql_cell ) ); |
718 |
push @$ods_content, \@headers; |
|
|
719 |
|
| 720 |
# Other line in Unicode |
| 721 |
my $sql_rows = $sth->fetchall_arrayref(); |
| 722 |
foreach my $sql_row (@$sql_rows) { |
| 723 |
my @content_row; |
| 724 |
foreach my $sql_cell (@$sql_row) { |
| 725 |
push @content_row, $scrubber->scrub( Encode::encode( 'UTF8', $sql_cell ) ); |
| 687 |
|
726 |
|
|
|
727 |
} |
| 728 |
push @$ods_content, \@content_row; |
| 688 |
} |
729 |
} |
| 689 |
push @$ods_content, \@content_row; |
|
|
| 690 |
} |
| 691 |
|
730 |
|
| 692 |
# Process |
731 |
# Process |
| 693 |
generate_ods( $ods_filepath, $ods_content ); |
732 |
generate_ods( $ods_filepath, $ods_content ); |
|
|
733 |
|
| 734 |
# Output |
| 735 |
binmode(STDOUT); |
| 736 |
open $ods_fh, '<', $ods_filepath; |
| 737 |
print $input->header( |
| 738 |
-type => $type, |
| 739 |
-attachment => $reportfilename |
| 740 |
); |
| 741 |
print $_ while <$ods_fh>; |
| 742 |
unlink $ods_filepath; |
| 743 |
} elsif ( $format eq 'template' ) { |
| 744 |
my $template_id = $input->param('template'); |
| 745 |
my $notice_template = Koha::Notice::Templates->find($template_id); |
| 746 |
my $data = $sth->fetchall_arrayref( {} ); |
| 747 |
$content = process_tt( |
| 748 |
$notice_template->content, |
| 749 |
{ |
| 750 |
data => $data, |
| 751 |
report_id => $report_id, |
| 752 |
for_download => 1, |
| 753 |
} |
| 754 |
); |
| 755 |
$reportfilename = process_tt( |
| 756 |
$notice_template->title, |
| 757 |
{ |
| 758 |
data => $data, |
| 759 |
report_id => $report_id, |
| 760 |
} |
| 761 |
); |
| 762 |
} |
| 763 |
} |
| 694 |
|
764 |
|
| 695 |
# Output |
765 |
unless ( $format eq 'ods' ) { |
| 696 |
binmode(STDOUT); |
|
|
| 697 |
open $ods_fh, '<', $ods_filepath; |
| 698 |
print $input->header( |
766 |
print $input->header( |
| 699 |
-type => $type, |
767 |
-type => $type, |
| 700 |
-attachment => $reportfilename |
768 |
-attachment => $reportfilename |
| 701 |
); |
769 |
); |
| 702 |
print $_ while <$ods_fh>; |
770 |
print $content; |
| 703 |
unlink $ods_filepath; |
|
|
| 704 |
} elsif ( $format eq 'template' ) { |
| 705 |
my $template_id = $input->param('template'); |
| 706 |
my $notice_template = Koha::Notice::Templates->find($template_id); |
| 707 |
my $data = $sth->fetchall_arrayref( {} ); |
| 708 |
$content = process_tt( |
| 709 |
$notice_template->content, |
| 710 |
{ |
| 711 |
data => $data, |
| 712 |
report_id => $report_id, |
| 713 |
for_download => 1, |
| 714 |
} |
| 715 |
); |
| 716 |
$reportfilename = process_tt( |
| 717 |
$notice_template->title, |
| 718 |
{ |
| 719 |
data => $data, |
| 720 |
report_id => $report_id, |
| 721 |
} |
| 722 |
); |
| 723 |
} |
771 |
} |
| 724 |
} |
|
|
| 725 |
|
772 |
|
| 726 |
unless ( $format eq 'ods' ) { |
773 |
foreach my $err ( @$q_errors, @errors ) { |
| 727 |
print $input->header( |
774 |
print "# ERROR: " . ( map { $_ . ": " . $err->{$_} } keys %$err ) . "\n"; |
| 728 |
-type => $type, |
775 |
} # here we print all the non-fatal errors at the end. Not super smooth, but better than nothing. |
| 729 |
-attachment => $reportfilename |
776 |
exit; |
| 730 |
); |
|
|
| 731 |
print $content; |
| 732 |
} |
777 |
} |
| 733 |
|
778 |
$template->param( |
| 734 |
foreach my $err ( @$q_errors, @errors ) { |
779 |
'sql' => $sql, |
| 735 |
print "# ERROR: " . ( map { $_ . ": " . $err->{$_} } keys %$err ) . "\n"; |
780 |
'execute' => 1, |
| 736 |
} # here we print all the non-fatal errors at the end. Not super smooth, but better than nothing. |
781 |
'name' => 'Error exporting report!', |
| 737 |
exit; |
782 |
'notes' => '', |
|
|
783 |
'errors' => $q_errors, |
| 784 |
); |
| 738 |
} |
785 |
} |
| 739 |
$template->param( |
|
|
| 740 |
'sql' => $sql, |
| 741 |
'execute' => 1, |
| 742 |
'name' => 'Error exporting report!', |
| 743 |
'notes' => '', |
| 744 |
'errors' => $q_errors, |
| 745 |
); |
| 746 |
|
786 |
|
| 747 |
} elsif ( $op eq 'add_form_sql' || $op eq 'duplicate' ) { |
787 |
} elsif ( $op eq 'add_form_sql' || $op eq 'duplicate' ) { |
| 748 |
|
788 |
|
| 749 |
my ( $group, $subgroup, $sql, $reportname, $notes ); |
789 |
my ( $group, $subgroup, $sql, $reportname, $notes, @branches, $report ); |
| 750 |
if ( $input->param('sql') ) { |
790 |
if ( $input->param('sql') ) { |
| 751 |
$group = $input->param('report_group'); |
791 |
$group = $input->param('report_group'); |
| 752 |
$subgroup = $input->param('report_subgroup'); |
792 |
$subgroup = $input->param('report_subgroup'); |
| 753 |
$sql = $input->param('sql') // ''; |
793 |
$sql = $input->param('sql') // ''; |
| 754 |
$reportname = $input->param('reportname') // ''; |
794 |
$reportname = $input->param('reportname') // ''; |
| 755 |
$notes = $input->param('notes') // ''; |
795 |
$notes = $input->param('notes') // ''; |
|
|
796 |
@branches = grep { $_ ne q{} } $input->multi_param('branches'); |
| 797 |
|
| 756 |
} elsif ( my $report_id = $input->param('id') ) { |
798 |
} elsif ( my $report_id = $input->param('id') ) { |
| 757 |
my $report = Koha::Reports->find($report_id); |
799 |
$report = Koha::Reports->find($report_id); |
| 758 |
$group = $report->report_group; |
800 |
$group = $report->report_group; |
| 759 |
$subgroup = $report->report_subgroup; |
801 |
$subgroup = $report->report_subgroup; |
| 760 |
$sql = $report->savedsql // ''; |
802 |
$sql = $report->savedsql // ''; |
| 761 |
$reportname = $report->report_name // ''; |
803 |
$reportname = $report->report_name // ''; |
| 762 |
$notes = $report->notes // ''; |
804 |
$notes = $report->notes // ''; |
|
|
805 |
@branches = grep { $_ ne q{} } $input->multi_param('branches'); |
| 806 |
|
| 763 |
} |
807 |
} |
| 764 |
|
808 |
|
| 765 |
my $tables = get_tables(); |
809 |
my $tables = get_tables(); |
|
Lines 774-779
if ( !$op ) {
Link Here
|
| 774 |
'cache_expiry' => 300, |
818 |
'cache_expiry' => 300, |
| 775 |
'usecache' => $usecache, |
819 |
'usecache' => $usecache, |
| 776 |
'tables' => $tables, |
820 |
'tables' => $tables, |
|
|
821 |
'report' => $report, |
| 777 |
|
822 |
|
| 778 |
); |
823 |
); |
| 779 |
} |
824 |
} |
|
Lines 783-789
if ( $op eq 'run' ) {
Link Here
|
| 783 |
# execute a saved report |
828 |
# execute a saved report |
| 784 |
my $limit = $input->param('limit') || 20; |
829 |
my $limit = $input->param('limit') || 20; |
| 785 |
my $offset = 0; |
830 |
my $offset = 0; |
| 786 |
my $report_id = $input->param('id'); |
|
|
| 787 |
my @sql_params = $input->multi_param('sql_params'); |
831 |
my @sql_params = $input->multi_param('sql_params'); |
| 788 |
my @param_names = $input->multi_param('param_name'); |
832 |
my @param_names = $input->multi_param('param_name'); |
| 789 |
my $template_id = $input->param('template'); |
833 |
my $template_id = $input->param('template'); |
|
Lines 800-806
if ( $op eq 'run' ) {
Link Here
|
| 800 |
); |
844 |
); |
| 801 |
|
845 |
|
| 802 |
my ( $sql, $original_sql, $type, $name, $notes ); |
846 |
my ( $sql, $original_sql, $type, $name, $notes ); |
| 803 |
if ( my $report = Koha::Reports->find($report_id) ) { |
847 |
if ($report) { |
| 804 |
$sql = $original_sql = $report->savedsql; |
848 |
$sql = $original_sql = $report->savedsql; |
| 805 |
$name = $report->report_name; |
849 |
$name = $report->report_name; |
| 806 |
$notes = $report->notes; |
850 |
$notes = $report->notes; |
|
Lines 1047-1054
if ( $op eq 'run' ) {
Link Here
|
| 1047 |
'param_names' => \@param_names, |
1091 |
'param_names' => \@param_names, |
| 1048 |
); |
1092 |
); |
| 1049 |
} |
1093 |
} |
| 1050 |
} else { |
|
|
| 1051 |
push @errors, { no_sql_for_id => $report_id }; |
| 1052 |
} |
1094 |
} |
| 1053 |
} |
1095 |
} |
| 1054 |
|
1096 |
|
|
Lines 1077-1087
if ( $op eq 'list' || $op eq 'convert' ) {
Link Here
|
| 1077 |
|
1119 |
|
| 1078 |
# use a saved report |
1120 |
# use a saved report |
| 1079 |
# get list of reports and display them |
1121 |
# get list of reports and display them |
| 1080 |
my $group = $input->param('group'); |
1122 |
my $group = $input->param('group'); |
| 1081 |
my $subgroup = $input->param('subgroup'); |
1123 |
my $subgroup = $input->param('subgroup'); |
|
|
1124 |
my $show_all_filter = $filter->{show_all_reports}; |
| 1125 |
my $logged_in_user = Koha::Patrons->find($borrowernumber); |
| 1126 |
my $can_manage_limits = Koha::Report->can_manage_limits($logged_in_user); |
| 1082 |
$filter->{group} = $group; |
1127 |
$filter->{group} = $group; |
| 1083 |
$filter->{subgroup} = $subgroup; |
1128 |
$filter->{subgroup} = $subgroup; |
|
|
1129 |
|
| 1130 |
if ($show_all_filter) { |
| 1131 |
$filter->{show_all_reports} = $can_manage_limits ? $show_all_filter : 0; |
| 1132 |
} else { |
| 1133 |
$filter->{show_all_reports} = 0; |
| 1134 |
} |
| 1135 |
|
| 1084 |
my $reports = get_saved_reports($filter); |
1136 |
my $reports = get_saved_reports($filter); |
|
|
1137 |
|
| 1138 |
my $branch_limit_active = |
| 1139 |
C4::Context->preference('LimitReportsByBranch') |
| 1140 |
&& $can_manage_limits |
| 1141 |
&& !$filter->{show_all_reports}; |
| 1142 |
|
| 1085 |
my $has_obsolete_reports; |
1143 |
my $has_obsolete_reports; |
| 1086 |
for my $report (@$reports) { |
1144 |
for my $report (@$reports) { |
| 1087 |
$report->{results} = C4::Reports::Guided::get_results( $report->{id} ); |
1145 |
$report->{results} = C4::Reports::Guided::get_results( $report->{id} ); |
|
Lines 1091-1103
if ( $op eq 'list' || $op eq 'convert' ) {
Link Here
|
| 1091 |
} |
1149 |
} |
| 1092 |
} |
1150 |
} |
| 1093 |
$template->param( |
1151 |
$template->param( |
| 1094 |
'manamsg' => $input->param('manamsg') || '', |
1152 |
'manamsg' => $input->param('manamsg') || '', |
| 1095 |
'saved1' => 1, |
1153 |
'saved1' => 1, |
| 1096 |
'savedreports' => $reports, |
1154 |
'savedreports' => $reports, |
| 1097 |
'usecache' => $usecache, |
1155 |
'usecache' => $usecache, |
| 1098 |
'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ), |
1156 |
'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ), |
| 1099 |
filters => $filter, |
1157 |
filters => $filter, |
| 1100 |
has_obsolete_reports => $has_obsolete_reports, |
1158 |
has_obsolete_reports => $has_obsolete_reports, |
|
|
1159 |
branch_limit_active => $branch_limit_active, |
| 1160 |
can_manage_report_limits => $can_manage_limits, |
| 1101 |
); |
1161 |
); |
| 1102 |
} |
1162 |
} |
| 1103 |
|
1163 |
|
|
Lines 1208-1211
sub create_non_existing_group_and_subgroup {
Link Here
|
| 1208 |
} |
1268 |
} |
| 1209 |
} |
1269 |
} |
| 1210 |
} |
1270 |
} |
| 1211 |
|
|
|