|
Lines 18-31
Link Here
|
| 18 |
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. |
18 |
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. |
| 19 |
|
19 |
|
| 20 |
use strict; |
20 |
use strict; |
| 21 |
#use warnings; FIXME - Bug 2505 |
21 |
use warnings; |
|
|
22 |
|
| 22 |
use CGI; |
23 |
use CGI; |
| 23 |
use Text::CSV; |
24 |
use Text::CSV; |
| 24 |
use URI::Escape; |
25 |
use URI::Escape; |
| 25 |
use C4::Reports::Guided; |
26 |
use C4::Reports::Guided; |
| 26 |
use C4::Auth qw/:DEFAULT get_session/; |
27 |
use C4::Auth qw/:DEFAULT get_session/; |
| 27 |
use C4::Output; |
28 |
use C4::Output; |
| 28 |
use C4::Dates; |
29 |
use C4::Dates qw/format_date/; |
| 29 |
use C4::Debug; |
30 |
use C4::Debug; |
| 30 |
use C4::Branch; # XXX subfield_is_koha_internal_p |
31 |
use C4::Branch; # XXX subfield_is_koha_internal_p |
| 31 |
use Koha::Cache; |
32 |
use Koha::Cache; |
|
Lines 69-75
my $session = $cookie ? get_session($cookie->value) : undef;
Link Here
|
| 69 |
my $filter; |
70 |
my $filter; |
| 70 |
if ( $input->param("filter_set") ) { |
71 |
if ( $input->param("filter_set") ) { |
| 71 |
$filter = {}; |
72 |
$filter = {}; |
| 72 |
$filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword/; |
73 |
$filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword group subgroup/; |
| 73 |
$session->param('report_filter', $filter) if $session; |
74 |
$session->param('report_filter', $filter) if $session; |
| 74 |
$template->param( 'filter_set' => 1 ); |
75 |
$template->param( 'filter_set' => 1 ); |
| 75 |
} |
76 |
} |
|
Lines 86-106
if ( !$phase ) {
Link Here
|
| 86 |
elsif ( $phase eq 'Build new' ) { |
87 |
elsif ( $phase eq 'Build new' ) { |
| 87 |
# build a new report |
88 |
# build a new report |
| 88 |
$template->param( 'build1' => 1 ); |
89 |
$template->param( 'build1' => 1 ); |
| 89 |
$template->param( 'areas' => get_report_areas(), 'usecache' => $usecache, 'cache_expiry' => 300, 'public' => '0' ); |
90 |
my $areas = get_report_areas(); |
| 90 |
} |
91 |
$template->param( |
| 91 |
elsif ( $phase eq 'Use saved' ) { |
92 |
'areas' => [map { id => $_->[0], name => $_->[1] }, @$areas], |
|
|
93 |
'usecache' => $usecache, |
| 94 |
'cache_expiry' => 300, |
| 95 |
'public' => '0', |
| 96 |
); |
| 97 |
} elsif ( $phase eq 'Use saved' ) { |
| 98 |
|
| 92 |
# use a saved report |
99 |
# use a saved report |
| 93 |
# get list of reports and display them |
100 |
# get list of reports and display them |
|
|
101 |
my $group = $input->param('group'); |
| 102 |
my $subgroup = $input->param('subgroup'); |
| 103 |
$filter->{group} = $group; |
| 104 |
$filter->{subgroup} = $subgroup; |
| 94 |
$template->param( |
105 |
$template->param( |
| 95 |
'saved1' => 1, |
106 |
'saved1' => 1, |
| 96 |
'savedreports' => get_saved_reports($filter), |
107 |
'savedreports' => get_saved_reports($filter), |
| 97 |
'usecache' => $usecache, |
108 |
'usecache' => $usecache, |
|
|
109 |
'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup), |
| 98 |
); |
110 |
); |
| 99 |
if ($filter) { |
|
|
| 100 |
while ( my ($k, $v) = each %$filter ) { |
| 101 |
$template->param( "filter_$k" => $v ) if $v; |
| 102 |
} |
| 103 |
} |
| 104 |
} |
111 |
} |
| 105 |
|
112 |
|
| 106 |
elsif ( $phase eq 'Delete Saved') { |
113 |
elsif ( $phase eq 'Delete Saved') { |
|
Lines 114-143
elsif ( $phase eq 'Delete Saved') {
Link Here
|
| 114 |
|
121 |
|
| 115 |
elsif ( $phase eq 'Show SQL'){ |
122 |
elsif ( $phase eq 'Show SQL'){ |
| 116 |
|
123 |
|
| 117 |
my $id = $input->param('reports'); |
124 |
my $id = $input->param('reports'); |
| 118 |
my ($sql,$type,$reportname,$notes) = get_saved_report($id); |
125 |
my $report = get_saved_report($id); |
| 119 |
$template->param( |
126 |
$template->param( |
| 120 |
'id' => $id, |
127 |
'id' => $id, |
| 121 |
'reportname' => $reportname, |
128 |
'reportname' => $report->{report_name}, |
| 122 |
'notes' => $notes, |
129 |
'notes' => $report->{notes}, |
| 123 |
'sql' => $sql, |
130 |
'sql' => $report->{savedsql}, |
| 124 |
'showsql' => 1, |
131 |
'showsql' => 1, |
| 125 |
); |
132 |
); |
| 126 |
} |
133 |
} |
| 127 |
|
134 |
|
| 128 |
elsif ( $phase eq 'Edit SQL'){ |
135 |
elsif ( $phase eq 'Edit SQL'){ |
| 129 |
|
136 |
|
| 130 |
my $id = $input->param('reports'); |
137 |
my $id = $input->param('reports'); |
| 131 |
my ($sql,$type,$reportname,$notes, $cache_expiry, $public) = get_saved_report($id); |
138 |
my $report = get_saved_report($id); |
|
|
139 |
my $group = $report->{report_group}; |
| 140 |
my $subgroup = $report->{report_subgroup}; |
| 132 |
$template->param( |
141 |
$template->param( |
| 133 |
'sql' => $sql, |
142 |
'sql' => $report->{savedsql}, |
| 134 |
'reportname' => $reportname, |
143 |
'reportname' => $report->{report_name}, |
| 135 |
'notes' => $notes, |
144 |
'groups_with_subgroups' => groups_with_subgroups($group, $subgroup), |
|
|
145 |
'notes' => $report->{notes}, |
| 136 |
'id' => $id, |
146 |
'id' => $id, |
| 137 |
'cache_expiry' => $cache_expiry, |
147 |
'cache_expiry' => $report->{cache_expiry}, |
| 138 |
'public' => $public, |
148 |
'public' => $report->{public}, |
| 139 |
'usecache' => $usecache, |
149 |
'usecache' => $usecache, |
| 140 |
'editsql' => 1, |
150 |
'editsql' => 1, |
| 141 |
); |
151 |
); |
| 142 |
} |
152 |
} |
| 143 |
|
153 |
|
|
Lines 145-150
elsif ( $phase eq 'Update SQL'){
Link Here
|
| 145 |
my $id = $input->param('id'); |
155 |
my $id = $input->param('id'); |
| 146 |
my $sql = $input->param('sql'); |
156 |
my $sql = $input->param('sql'); |
| 147 |
my $reportname = $input->param('reportname'); |
157 |
my $reportname = $input->param('reportname'); |
|
|
158 |
my $group = $input->param('group'); |
| 159 |
my $subgroup = $input->param('subgroup'); |
| 148 |
my $notes = $input->param('notes'); |
160 |
my $notes = $input->param('notes'); |
| 149 |
my $cache_expiry = $input->param('cache_expiry'); |
161 |
my $cache_expiry = $input->param('cache_expiry'); |
| 150 |
my $cache_expiry_units = $input->param('cache_expiry_units'); |
162 |
my $cache_expiry_units = $input->param('cache_expiry_units'); |
|
Lines 178-193
elsif ( $phase eq 'Update SQL'){
Link Here
|
| 178 |
'errors' => \@errors, |
190 |
'errors' => \@errors, |
| 179 |
'sql' => $sql, |
191 |
'sql' => $sql, |
| 180 |
); |
192 |
); |
| 181 |
} |
193 |
} else { |
| 182 |
else { |
194 |
update_sql( $id, { |
| 183 |
update_sql( $id, $sql, $reportname, $notes, $cache_expiry, $public ); |
195 |
sql => $sql, |
|
|
196 |
name => $reportname, |
| 197 |
group => $group, |
| 198 |
subgroup => $subgroup, |
| 199 |
notes => $notes, |
| 200 |
cache_expiry => $cache_expiry, |
| 201 |
public => $public, |
| 202 |
} ); |
| 184 |
$template->param( |
203 |
$template->param( |
| 185 |
'save_successful' => 1, |
204 |
'save_successful' => 1, |
| 186 |
'reportname' => $reportname, |
205 |
'reportname' => $reportname, |
| 187 |
'id' => $id, |
206 |
'id' => $id, |
| 188 |
); |
207 |
); |
| 189 |
} |
208 |
} |
| 190 |
|
|
|
| 191 |
} |
209 |
} |
| 192 |
|
210 |
|
| 193 |
elsif ($phase eq 'retrieve results') { |
211 |
elsif ($phase eq 'retrieve results') { |
|
Lines 229-235
elsif ( $phase eq 'Report on this Area' ) {
Link Here
|
| 229 |
# they have choosen a new report and the area to report on |
247 |
# they have choosen a new report and the area to report on |
| 230 |
$template->param( |
248 |
$template->param( |
| 231 |
'build2' => 1, |
249 |
'build2' => 1, |
| 232 |
'area' => $input->param('areas'), |
250 |
'area' => $input->param('area'), |
| 233 |
'types' => get_report_types(), |
251 |
'types' => get_report_types(), |
| 234 |
'cache_expiry' => $cache_expiry, |
252 |
'cache_expiry' => $cache_expiry, |
| 235 |
'public' => $input->param('public'), |
253 |
'public' => $input->param('public'), |
|
Lines 276-317
elsif ( $phase eq 'Choose these criteria' ) {
Link Here
|
| 276 |
my $area = $input->param('area'); |
294 |
my $area = $input->param('area'); |
| 277 |
my $type = $input->param('type'); |
295 |
my $type = $input->param('type'); |
| 278 |
my $column = $input->param('column'); |
296 |
my $column = $input->param('column'); |
| 279 |
my @definitions = $input->param('definition'); |
297 |
my @definitions = $input->param('definition'); |
| 280 |
my $definition = join (',',@definitions); |
298 |
my $definition = join (',',@definitions); |
| 281 |
my @criteria = $input->param('criteria_column'); |
299 |
my @criteria = $input->param('criteria_column'); |
| 282 |
my $query_criteria; |
300 |
my $query_criteria; |
| 283 |
foreach my $crit (@criteria) { |
301 |
foreach my $crit (@criteria) { |
| 284 |
my $value = $input->param( $crit . "_value" ); |
302 |
my $value = $input->param( $crit . "_value" ); |
| 285 |
|
303 |
|
| 286 |
# If value is not defined, then it may be range values |
304 |
# If value is not defined, then it may be range values |
| 287 |
if (!defined $value) { |
305 |
if (!defined $value) { |
| 288 |
|
306 |
|
| 289 |
my $fromvalue = $input->param( "from_" . $crit . "_value" ); |
307 |
my $fromvalue = $input->param( "from_" . $crit . "_value" ); |
| 290 |
my $tovalue = $input->param( "to_" . $crit . "_value" ); |
308 |
my $tovalue = $input->param( "to_" . $crit . "_value" ); |
| 291 |
|
309 |
|
| 292 |
# If the range values are dates |
310 |
# If the range values are dates |
| 293 |
if ($fromvalue =~ C4::Dates->regexp('syspref') && $tovalue =~ C4::Dates->regexp('syspref')) { |
311 |
if ($fromvalue =~ C4::Dates->regexp('syspref') && $tovalue =~ C4::Dates->regexp('syspref')) { |
| 294 |
$fromvalue = C4::Dates->new($fromvalue)->output("iso"); |
312 |
$fromvalue = C4::Dates->new($fromvalue)->output("iso"); |
| 295 |
$tovalue = C4::Dates->new($tovalue)->output("iso"); |
313 |
$tovalue = C4::Dates->new($tovalue)->output("iso"); |
| 296 |
} |
314 |
} |
| 297 |
|
315 |
|
| 298 |
if ($fromvalue && $tovalue) { |
316 |
if ($fromvalue && $tovalue) { |
| 299 |
$query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'"; |
317 |
$query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'"; |
| 300 |
} |
318 |
} |
| 301 |
|
319 |
|
| 302 |
} else { |
|
|
| 303 |
|
| 304 |
# If value is a date |
| 305 |
if ($value =~ C4::Dates->regexp('syspref')) { |
| 306 |
$value = C4::Dates->new($value)->output("iso"); |
| 307 |
} |
| 308 |
# don't escape runtime parameters, they'll be at runtime |
| 309 |
if ($value =~ /<<.*>>/) { |
| 310 |
$query_criteria .= " AND $crit=$value"; |
| 311 |
} else { |
320 |
} else { |
| 312 |
$query_criteria .= " AND $crit='$value'"; |
321 |
|
|
|
322 |
# If value is a date |
| 323 |
if ($value =~ C4::Dates->regexp('syspref')) { |
| 324 |
$value = C4::Dates->new($value)->output("iso"); |
| 325 |
} |
| 326 |
# don't escape runtime parameters, they'll be at runtime |
| 327 |
if ($value =~ /<<.*>>/) { |
| 328 |
$query_criteria .= " AND $crit=$value"; |
| 329 |
} else { |
| 330 |
$query_criteria .= " AND $crit='$value'"; |
| 331 |
} |
| 313 |
} |
332 |
} |
| 314 |
} |
|
|
| 315 |
} |
333 |
} |
| 316 |
$template->param( |
334 |
$template->param( |
| 317 |
'build5' => 1, |
335 |
'build5' => 1, |
|
Lines 413-418
elsif ( $phase eq 'Build report' ) {
Link Here
|
| 413 |
build_query( \@columns, $query_criteria, $query_orderby, $area, $totals, $definition ); |
431 |
build_query( \@columns, $query_criteria, $query_orderby, $area, $totals, $definition ); |
| 414 |
$template->param( |
432 |
$template->param( |
| 415 |
'showreport' => 1, |
433 |
'showreport' => 1, |
|
|
434 |
'area' => $area, |
| 416 |
'sql' => $sql, |
435 |
'sql' => $sql, |
| 417 |
'type' => $type, |
436 |
'type' => $type, |
| 418 |
'cache_expiry' => $input->param('cache_expiry'), |
437 |
'cache_expiry' => $input->param('cache_expiry'), |
|
Lines 421-443
elsif ( $phase eq 'Build report' ) {
Link Here
|
| 421 |
} |
440 |
} |
| 422 |
|
441 |
|
| 423 |
elsif ( $phase eq 'Save' ) { |
442 |
elsif ( $phase eq 'Save' ) { |
| 424 |
# Save the report that has just been built |
443 |
# Save the report that has just been built |
|
|
444 |
my $area = $input->param('area'); |
| 425 |
my $sql = $input->param('sql'); |
445 |
my $sql = $input->param('sql'); |
| 426 |
my $type = $input->param('type'); |
446 |
my $type = $input->param('type'); |
| 427 |
$template->param( |
447 |
$template->param( |
| 428 |
'save' => 1, |
448 |
'save' => 1, |
|
|
449 |
'area' => $area, |
| 429 |
'sql' => $sql, |
450 |
'sql' => $sql, |
| 430 |
'type' => $type, |
451 |
'type' => $type, |
| 431 |
'cache_expiry' => $input->param('cache_expiry'), |
452 |
'cache_expiry' => $input->param('cache_expiry'), |
| 432 |
'public' => $input->param('public'), |
453 |
'public' => $input->param('public'), |
|
|
454 |
'groups_with_subgroups' => groups_with_subgroups($area), # in case we have a report group that matches area |
| 433 |
); |
455 |
); |
| 434 |
} |
456 |
} |
| 435 |
|
457 |
|
| 436 |
elsif ( $phase eq 'Save Report' ) { |
458 |
elsif ( $phase eq 'Save Report' ) { |
| 437 |
# save the sql pasted in by a user |
459 |
# save the sql pasted in by a user |
| 438 |
my $sql = $input->param('sql'); |
460 |
my $area = $input->param('area'); |
| 439 |
my $name = $input->param('reportname'); |
461 |
my $group = $input->param('group'); |
| 440 |
my $type = $input->param('types'); |
462 |
my $subgroup = $input->param('subgroup'); |
|
|
463 |
my $sql = $input->param('sql'); |
| 464 |
my $name = $input->param('reportname'); |
| 465 |
my $type = $input->param('types'); |
| 441 |
my $notes = $input->param('notes'); |
466 |
my $notes = $input->param('notes'); |
| 442 |
my $cache_expiry = $input->param('cache_expiry'); |
467 |
my $cache_expiry = $input->param('cache_expiry'); |
| 443 |
my $cache_expiry_units = $input->param('cache_expiry_units'); |
468 |
my $cache_expiry_units = $input->param('cache_expiry_units'); |
|
Lines 455-461
elsif ( $phase eq 'Save Report' ) {
Link Here
|
| 455 |
} |
480 |
} |
| 456 |
} |
481 |
} |
| 457 |
# check $cache_expiry isnt 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 |
482 |
# check $cache_expiry isnt 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 |
| 458 |
if( $cache_expiry >= 2592000 ){ |
483 |
if( $cache_expiry && $cache_expiry >= 2592000 ){ |
| 459 |
push @errors, {cache_expiry => $cache_expiry}; |
484 |
push @errors, {cache_expiry => $cache_expiry}; |
| 460 |
} |
485 |
} |
| 461 |
## FIXME this is AFTER entering a name to save the report under |
486 |
## FIXME this is AFTER entering a name to save the report under |
|
Lines 463-469
elsif ( $phase eq 'Save Report' ) {
Link Here
|
| 463 |
push @errors, {sqlerr => $1}; |
488 |
push @errors, {sqlerr => $1}; |
| 464 |
} |
489 |
} |
| 465 |
elsif ($sql !~ /^(SELECT)/i) { |
490 |
elsif ($sql !~ /^(SELECT)/i) { |
| 466 |
push @errors, {queryerr => 1}; |
491 |
push @errors, {queryerr => "No SELECT"}; |
| 467 |
} |
492 |
} |
| 468 |
if (@errors) { |
493 |
if (@errors) { |
| 469 |
$template->param( |
494 |
$template->param( |
|
Lines 477-637
elsif ( $phase eq 'Save Report' ) {
Link Here
|
| 477 |
); |
502 |
); |
| 478 |
} |
503 |
} |
| 479 |
else { |
504 |
else { |
| 480 |
my $id = save_report( $borrowernumber, $sql, $name, $type, $notes, $cache_expiry, $public ); |
505 |
save_report( { |
| 481 |
$template->param( |
506 |
borrowernumber => $borrowernumber, |
| 482 |
'save_successful' => 1, |
507 |
sql => $sql, |
| 483 |
'reportname' => $name, |
508 |
name => $name, |
| 484 |
'id' => $id, |
509 |
area => $area, |
| 485 |
); |
510 |
group => $group, |
|
|
511 |
subgroup => $subgroup, |
| 512 |
type => $type, |
| 513 |
notes => $notes, |
| 514 |
cache_expiry => $cache_expiry, |
| 515 |
public => $public, |
| 516 |
} ); |
| 517 |
$template->param( 'save_successful' => 1, ); |
| 486 |
} |
518 |
} |
| 487 |
} |
519 |
} |
| 488 |
|
520 |
|
| 489 |
elsif ($phase eq 'Run this report'){ |
521 |
elsif ($phase eq 'Run this report'){ |
| 490 |
# execute a saved report |
522 |
# execute a saved report |
| 491 |
my $limit = 20; # page size. # TODO: move to DB or syspref? |
523 |
my $limit = 20; # page size. # TODO: move to DB or syspref? |
| 492 |
my $offset = 0; |
524 |
my $offset = 0; |
| 493 |
my $report = $input->param('reports'); |
525 |
my $report_id = $input->param('reports'); |
| 494 |
my @sql_params = $input->param('sql_params'); |
526 |
my @sql_params = $input->param('sql_params'); |
| 495 |
# offset algorithm |
527 |
# offset algorithm |
| 496 |
if ($input->param('page')) { |
528 |
if ($input->param('page')) { |
| 497 |
$offset = ($input->param('page') - 1) * $limit; |
529 |
$offset = ($input->param('page') - 1) * $limit; |
| 498 |
} |
530 |
} |
| 499 |
my ($sql,$type,$name,$notes) = get_saved_report($report); |
531 |
|
| 500 |
unless ($sql) { |
532 |
my ( $sql, $type, $name, $notes ); |
| 501 |
push @errors, {no_sql_for_id=>$report}; |
533 |
if (my $report = get_saved_report($report_id)) { |
| 502 |
} |
534 |
$sql = $report->{savedsql}; |
| 503 |
my @rows = (); |
535 |
$name = $report->{report_name}; |
| 504 |
# if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters |
536 |
$notes = $report->{notes}; |
| 505 |
if ($sql =~ /<</ && !@sql_params) { |
537 |
|
| 506 |
# split on ??. Each odd (2,4,6,...) entry should be a parameter to fill |
538 |
my @rows = (); |
| 507 |
my @split = split /<<|>>/,$sql; |
539 |
# if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters |
| 508 |
my @tmpl_parameters; |
540 |
if ($sql =~ /<</ && !@sql_params) { |
| 509 |
for(my $i=0;$i<($#split/2);$i++) { |
541 |
# split on ??. Each odd (2,4,6,...) entry should be a parameter to fill |
| 510 |
my ($text,$authorised_value) = split /\|/,$split[$i*2+1]; |
542 |
my @split = split /<<|>>/,$sql; |
| 511 |
my $input; |
543 |
my @tmpl_parameters; |
| 512 |
my $labelid; |
544 |
for(my $i=0;$i<($#split/2);$i++) { |
| 513 |
if ($authorised_value eq "date") { |
545 |
my ($text,$authorised_value) = split /\|/,$split[$i*2+1]; |
| 514 |
$input = 'date'; |
546 |
my $input; |
| 515 |
} |
547 |
my $labelid; |
| 516 |
elsif ($authorised_value) { |
548 |
if ($authorised_value eq "date") { |
| 517 |
my $dbh=C4::Context->dbh; |
549 |
$input = 'date'; |
| 518 |
my @authorised_values; |
|
|
| 519 |
my %authorised_lib; |
| 520 |
# builds list, depending on authorised value... |
| 521 |
if ( $authorised_value eq "branches" ) { |
| 522 |
my $branches = GetBranchesLoop(); |
| 523 |
foreach my $thisbranch (@$branches) { |
| 524 |
push @authorised_values, $thisbranch->{value}; |
| 525 |
$authorised_lib{$thisbranch->{value}} = $thisbranch->{branchname}; |
| 526 |
} |
| 527 |
} |
550 |
} |
| 528 |
elsif ( $authorised_value eq "itemtypes" ) { |
551 |
elsif ($authorised_value) { |
| 529 |
my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description"); |
552 |
my $dbh=C4::Context->dbh; |
| 530 |
$sth->execute; |
553 |
my @authorised_values; |
| 531 |
while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) { |
554 |
my %authorised_lib; |
| 532 |
push @authorised_values, $itemtype; |
555 |
# builds list, depending on authorised value... |
| 533 |
$authorised_lib{$itemtype} = $description; |
556 |
if ( $authorised_value eq "branches" ) { |
|
|
557 |
my $branches = GetBranchesLoop(); |
| 558 |
foreach my $thisbranch (@$branches) { |
| 559 |
push @authorised_values, $thisbranch->{value}; |
| 560 |
$authorised_lib{$thisbranch->{value}} = $thisbranch->{branchname}; |
| 561 |
} |
| 534 |
} |
562 |
} |
| 535 |
} |
563 |
elsif ( $authorised_value eq "itemtypes" ) { |
| 536 |
elsif ( $authorised_value eq "cn_source" ) { |
564 |
my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description"); |
| 537 |
my $class_sources = GetClassSources(); |
565 |
$sth->execute; |
| 538 |
my $default_source = C4::Context->preference("DefaultClassificationSource"); |
566 |
while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) { |
| 539 |
foreach my $class_source (sort keys %$class_sources) { |
567 |
push @authorised_values, $itemtype; |
| 540 |
next unless $class_sources->{$class_source}->{'used'} or |
568 |
$authorised_lib{$itemtype} = $description; |
| 541 |
($class_source eq $default_source); |
569 |
} |
| 542 |
push @authorised_values, $class_source; |
|
|
| 543 |
$authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'}; |
| 544 |
} |
570 |
} |
| 545 |
} |
571 |
elsif ( $authorised_value eq "cn_source" ) { |
| 546 |
elsif ( $authorised_value eq "categorycode" ) { |
572 |
my $class_sources = GetClassSources(); |
| 547 |
my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description"); |
573 |
my $default_source = C4::Context->preference("DefaultClassificationSource"); |
| 548 |
$sth->execute; |
574 |
foreach my $class_source (sort keys %$class_sources) { |
| 549 |
while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) { |
575 |
next unless $class_sources->{$class_source}->{'used'} or |
| 550 |
push @authorised_values, $categorycode; |
576 |
($class_source eq $default_source); |
| 551 |
$authorised_lib{$categorycode} = $description; |
577 |
push @authorised_values, $class_source; |
|
|
578 |
$authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'}; |
| 579 |
} |
| 552 |
} |
580 |
} |
|
|
581 |
elsif ( $authorised_value eq "categorycode" ) { |
| 582 |
my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description"); |
| 583 |
$sth->execute; |
| 584 |
while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) { |
| 585 |
push @authorised_values, $categorycode; |
| 586 |
$authorised_lib{$categorycode} = $description; |
| 587 |
} |
| 588 |
|
| 589 |
#---- "true" authorised value |
| 590 |
} |
| 591 |
else { |
| 592 |
my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib"); |
| 553 |
|
593 |
|
| 554 |
#---- "true" authorised value |
594 |
$authorised_values_sth->execute( $authorised_value); |
| 555 |
} |
|
|
| 556 |
else { |
| 557 |
my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib"); |
| 558 |
|
| 559 |
$authorised_values_sth->execute( $authorised_value); |
| 560 |
|
595 |
|
| 561 |
while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) { |
596 |
while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) { |
| 562 |
push @authorised_values, $value; |
597 |
push @authorised_values, $value; |
| 563 |
$authorised_lib{$value} = $lib; |
598 |
$authorised_lib{$value} = $lib; |
| 564 |
# For item location, we show the code and the libelle |
599 |
# For item location, we show the code and the libelle |
| 565 |
$authorised_lib{$value} = $lib; |
600 |
$authorised_lib{$value} = $lib; |
|
|
601 |
} |
| 566 |
} |
602 |
} |
| 567 |
} |
603 |
$labelid = $text; |
| 568 |
$labelid = $text; |
604 |
$labelid =~ s/\W//g; |
| 569 |
$labelid =~ s/\W//g; |
605 |
$input =CGI::scrolling_list( # FIXME: factor out scrolling_list |
| 570 |
$input =CGI::scrolling_list( # FIXME: factor out scrolling_list |
606 |
-name => "sql_params", |
| 571 |
-name => "sql_params", |
607 |
-id => "sql_params_".$labelid, |
| 572 |
-id => "sql_params_".$labelid, |
608 |
-values => \@authorised_values, |
| 573 |
-values => \@authorised_values, |
|
|
| 574 |
# -default => $value, |
609 |
# -default => $value, |
| 575 |
-labels => \%authorised_lib, |
610 |
-labels => \%authorised_lib, |
| 576 |
-override => 1, |
611 |
-override => 1, |
| 577 |
-size => 1, |
612 |
-size => 1, |
| 578 |
-multiple => 0, |
613 |
-multiple => 0, |
| 579 |
-tabindex => 1, |
614 |
-tabindex => 1, |
| 580 |
); |
615 |
); |
| 581 |
|
616 |
} else { |
| 582 |
} else { |
617 |
$input = "text"; |
| 583 |
$input = "text"; |
618 |
} |
|
|
619 |
push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid }; |
| 584 |
} |
620 |
} |
| 585 |
push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid }; |
621 |
$template->param('sql' => $sql, |
| 586 |
} |
622 |
'name' => $name, |
| 587 |
$template->param('sql' => $sql, |
623 |
'sql_params' => \@tmpl_parameters, |
| 588 |
'name' => $name, |
624 |
'enter_params' => 1, |
| 589 |
'sql_params' => \@tmpl_parameters, |
625 |
'reports' => $report_id, |
| 590 |
'enter_params' => 1, |
626 |
); |
| 591 |
'reports' => $report, |
|
|
| 592 |
); |
| 593 |
} else { |
| 594 |
# OK, we have parameters, or there are none, we run the report |
| 595 |
# if there were parameters, replace before running |
| 596 |
# split on ??. Each odd (2,4,6,...) entry should be a parameter to fill |
| 597 |
my @split = split /<<|>>/,$sql; |
| 598 |
my @tmpl_parameters; |
| 599 |
for(my $i=0;$i<$#split/2;$i++) { |
| 600 |
my $quoted = C4::Context->dbh->quote($sql_params[$i]); |
| 601 |
# if there are special regexp chars, we must \ them |
| 602 |
$split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g; |
| 603 |
$sql =~ s/<<$split[$i*2+1]>>/$quoted/; |
| 604 |
} |
| 605 |
my ($sth, $errors) = execute_query($sql, $offset, $limit); |
| 606 |
my $total = nb_rows($sql) || 0; |
| 607 |
unless ($sth) { |
| 608 |
die "execute_query failed to return sth for report $report: $sql"; |
| 609 |
} else { |
627 |
} else { |
| 610 |
my $headref = $sth->{NAME} || []; |
628 |
# OK, we have parameters, or there are none, we run the report |
| 611 |
my @headers = map { +{ cell => $_ } } @$headref; |
629 |
# if there were parameters, replace before running |
| 612 |
$template->param(header_row => \@headers); |
630 |
# split on ??. Each odd (2,4,6,...) entry should be a parameter to fill |
| 613 |
while (my $row = $sth->fetchrow_arrayref()) { |
631 |
my @split = split /<<|>>/,$sql; |
| 614 |
my @cells = map { +{ cell => $_ } } @$row; |
632 |
my @tmpl_parameters; |
| 615 |
push @rows, { cells => \@cells }; |
633 |
for(my $i=0;$i<$#split/2;$i++) { |
|
|
634 |
my $quoted = C4::Context->dbh->quote($sql_params[$i]); |
| 635 |
# if there are special regexp chars, we must \ them |
| 636 |
$split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g; |
| 637 |
$sql =~ s/<<$split[$i*2+1]>>/$quoted/; |
| 638 |
} |
| 639 |
my ($sth, $errors) = execute_query($sql, $offset, $limit); |
| 640 |
my $total = nb_rows($sql) || 0; |
| 641 |
unless ($sth) { |
| 642 |
die "execute_query failed to return sth for report $report_id: $sql"; |
| 643 |
} else { |
| 644 |
my $headref = $sth->{NAME} || []; |
| 645 |
my @headers = map { +{ cell => $_ } } @$headref; |
| 646 |
$template->param(header_row => \@headers); |
| 647 |
while (my $row = $sth->fetchrow_arrayref()) { |
| 648 |
my @cells = map { +{ cell => $_ } } @$row; |
| 649 |
push @rows, { cells => \@cells }; |
| 650 |
} |
| 616 |
} |
651 |
} |
| 617 |
} |
|
|
| 618 |
|
652 |
|
| 619 |
my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0); |
653 |
my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0); |
| 620 |
my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report&phase=Run%20this%20report"; |
654 |
my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report_id&phase=Run%20this%20report"; |
| 621 |
if (@sql_params) { |
655 |
if (@sql_params) { |
| 622 |
$url = join('&sql_params=', $url, map { URI::Escape::uri_escape($_) } @sql_params); |
656 |
$url = join('&sql_params=', $url, map { URI::Escape::uri_escape($_) } @sql_params); |
|
|
657 |
} |
| 658 |
$template->param( |
| 659 |
'results' => \@rows, |
| 660 |
'sql' => $sql, |
| 661 |
'id' => $report_id, |
| 662 |
'execute' => 1, |
| 663 |
'name' => $name, |
| 664 |
'notes' => $notes, |
| 665 |
'errors' => $errors, |
| 666 |
'pagination_bar' => pagination_bar($url, $totpages, $input->param('page')), |
| 667 |
'unlimited_total' => $total, |
| 668 |
); |
| 623 |
} |
669 |
} |
| 624 |
$template->param( |
670 |
} |
| 625 |
'results' => \@rows, |
671 |
else { |
| 626 |
'sql' => $sql, |
672 |
push @errors, { no_sql_for_id => $report_id }; |
| 627 |
'id' => $report, |
|
|
| 628 |
'execute' => 1, |
| 629 |
'name' => $name, |
| 630 |
'notes' => $notes, |
| 631 |
'errors' => $errors, |
| 632 |
'pagination_bar' => pagination_bar($url, $totpages, $input->param('page')), |
| 633 |
'unlimited_total' => $total, |
| 634 |
); |
| 635 |
} |
673 |
} |
| 636 |
} |
674 |
} |
| 637 |
|
675 |
|
|
Lines 681-696
elsif ($phase eq 'Export'){
Link Here
|
| 681 |
); |
719 |
); |
| 682 |
} |
720 |
} |
| 683 |
|
721 |
|
| 684 |
elsif ($phase eq 'Create report from SQL') { |
722 |
elsif ( $phase eq 'Create report from SQL' ) { |
| 685 |
# allow the user to paste in sql |
723 |
|
| 686 |
if ($input->param('sql')) { |
724 |
my ($group, $subgroup); |
|
|
725 |
# allow the user to paste in sql |
| 726 |
if ( $input->param('sql') ) { |
| 727 |
$group = $input->param('report_group'); |
| 728 |
$subgroup = $input->param('report_subgroup'); |
| 687 |
$template->param( |
729 |
$template->param( |
| 688 |
'sql' => $input->param('sql'), |
730 |
'sql' => $input->param('sql'), |
| 689 |
'reportname' => $input->param('reportname'), |
731 |
'reportname' => $input->param('reportname'), |
| 690 |
'notes' => $input->param('notes'), |
732 |
'notes' => $input->param('notes'), |
| 691 |
); |
733 |
); |
| 692 |
} |
734 |
} |
| 693 |
$template->param('create' => 1, 'public' => '0', 'cache_expiry' => 300, 'usecache' => $usecache); |
735 |
$template->param( |
|
|
736 |
'create' => 1, |
| 737 |
'groups_with_subgroups' => groups_with_subgroups($group, $subgroup), |
| 738 |
'public' => '0', |
| 739 |
'cache_expiry' => 300, |
| 740 |
'usecache' => $usecache, |
| 741 |
); |
| 694 |
} |
742 |
} |
| 695 |
|
743 |
|
| 696 |
elsif ($phase eq 'Create Compound Report'){ |
744 |
elsif ($phase eq 'Create Compound Report'){ |
|
Lines 729-731
$template->param( 'referer' => $input->referer(),
Link Here
|
| 729 |
); |
777 |
); |
| 730 |
|
778 |
|
| 731 |
output_html_with_http_headers $input, $cookie, $template->output; |
779 |
output_html_with_http_headers $input, $cookie, $template->output; |
|
|
780 |
|
| 781 |
sub groups_with_subgroups { |
| 782 |
my ($group, $subgroup) = @_; |
| 783 |
|
| 784 |
my $groups_with_subgroups = get_report_groups(); |
| 785 |
my @g_sg; |
| 786 |
while (my ($g_id, $v) = each %$groups_with_subgroups) { |
| 787 |
my @subgroups; |
| 788 |
if (my $sg = $v->{subgroups}) { |
| 789 |
while (my ($sg_id, $n) = each %$sg) { |
| 790 |
push @subgroups, { |
| 791 |
id => $sg_id, |
| 792 |
name => $n, |
| 793 |
selected => ($group && $g_id eq $group && $subgroup && $sg_id eq $subgroup ), |
| 794 |
}; |
| 795 |
} |
| 796 |
} |
| 797 |
push @g_sg, { |
| 798 |
id => $g_id, |
| 799 |
name => $v->{name}, |
| 800 |
selected => ($group && $g_id eq $group), |
| 801 |
subgroups => \@subgroups, |
| 802 |
}; |
| 803 |
} |
| 804 |
return \@g_sg; |
| 805 |
} |