3 # Copyright 2007 Liblime ltd
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along
17 # with Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
21 #use warnings; FIXME - Bug 2505
25 use C4
::Reports
::Guided
;
26 use C4
::Auth qw
/:DEFAULT get_session/;
30 use C4
::Branch
; # XXX subfield_is_koha_internal_p
38 Script to control the guided report creation
44 my $phase = $input->param('phase');
46 if ( $phase eq 'Build new' or $phase eq 'Delete Saved' ) {
47 $flagsrequired = 'create_reports';
49 elsif ( $phase eq 'Use saved' ) {
50 $flagsrequired = 'execute_reports';
55 my ( $template, $borrowernumber, $cookie ) = get_template_and_user
(
57 template_name
=> "reports/guided_reports_start.tmpl",
61 flagsrequired
=> { reports
=> $flagsrequired },
65 my $session = $cookie ? get_session
($cookie->value) : undef;
68 if ( $input->param("filter_set") ) {
70 $filter->{$_} = $input->param("filter_$_") foreach qw
/date author keyword/;
71 $session->param('report_filter', $filter) if $session;
72 $template->param( 'filter_set' => 1 );
75 $filter = $session->param('report_filter');
81 $template->param( 'start' => 1 );
84 elsif ( $phase eq 'Build new' ) {
86 $template->param( 'build1' => 1 );
87 $template->param( 'areas' => get_report_areas
() );
89 elsif ( $phase eq 'Use saved' ) {
91 # get list of reports and display them
94 'savedreports' => get_saved_reports
($filter),
97 while ( my ($k, $v) = each %$filter ) {
98 $template->param( "filter_$k" => $v ) if $v;
103 elsif ( $phase eq 'Delete Saved') {
105 # delete a report from the saved reports list
106 my $id = $input->param('reports');
108 print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?phase=Use%20saved");
112 elsif ( $phase eq 'Show SQL'){
114 my $id = $input->param('reports');
115 my $sql = get_sql
($id);
122 elsif ( $phase eq 'Edit SQL'){
124 my $id = $input->param('reports');
125 my ($sql,$type,$reportname,$notes) = get_saved_report
($id);
128 'reportname' => $reportname,
135 elsif ( $phase eq 'Update SQL'){
136 my $id = $input->param('id');
137 my $sql = $input->param('sql');
138 my $reportname = $input->param('reportname');
139 my $notes = $input->param('notes');
141 if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
142 push @errors, {sqlerr
=> $1};
144 elsif ($sql !~ /^(SELECT)/i) {
145 push @errors, {queryerr
=> 1};
149 'errors' => \
@errors,
154 update_sql
( $id, $sql, $reportname, $notes );
156 'save_successful' => 1,
163 elsif ($phase eq 'retrieve results') {
164 my $id = $input->param('id');
165 my ($results,$name,$notes) = format_results
($id);
169 'results' => $results,
175 elsif ( $phase eq 'Report on this Area' ) {
177 # they have choosen a new report and the area to report on
180 'area' => $input->param('areas'),
181 'types' => get_report_types
(),
185 elsif ( $phase eq 'Choose this type' ) {
187 # they have chosen type and area
188 # get area and type and pass them to the template
189 my $area = $input->param('area');
190 my $type = $input->param('types');
195 columns
=> get_columns
($area,$input),
199 elsif ( $phase eq 'Choose these columns' ) {
201 # we now know type, area, and columns
202 # next step is the constraints
203 my $area = $input->param('area');
204 my $type = $input->param('type');
205 my @columns = $input->param('columns');
206 my $column = join( ',', @columns );
212 definitions
=> get_from_dictionary
($area),
213 criteria
=> get_criteria
($area,$input),
217 elsif ( $phase eq 'Choose these criteria' ) {
218 my $area = $input->param('area');
219 my $type = $input->param('type');
220 my $column = $input->param('column');
221 my @definitions = $input->param('definition');
222 my $definition = join (',',@definitions);
223 my @criteria = $input->param('criteria_column');
225 foreach my $crit (@criteria) {
226 my $value = $input->param( $crit . "_value" );
228 # If value is not defined, then it may be range values
229 if (!defined $value) {
231 my $fromvalue = $input->param( "from_" . $crit . "_value" );
232 my $tovalue = $input->param( "to_" . $crit . "_value" );
234 # If the range values are dates
235 if ($fromvalue =~ C4
::Dates
->regexp('syspref') && $tovalue =~ C4
::Dates
->regexp('syspref')) {
236 $fromvalue = C4
::Dates
->new($fromvalue)->output("iso");
237 $tovalue = C4
::Dates
->new($tovalue)->output("iso");
240 if ($fromvalue && $tovalue) {
241 $query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'";
247 if ($value =~ C4
::Dates
->regexp('syspref')) {
248 $value = C4
::Dates
->new($value)->output("iso");
250 # don't escape runtime parameters, they'll be at runtime
251 if ($value =~ /<<.*>>/) {
252 $query_criteria .= " AND $crit=$value";
254 $query_criteria .= " AND $crit='$value'";
264 'definition' => $definition,
265 'criteriastring' => $query_criteria,
269 my @columns = split( ',', $column );
272 # build structue for use by tmpl_loop to choose columns to order by
273 # need to do something about the order of the order :)
274 # we also want to use the %columns hash to get the plain english names
275 foreach my $col (@columns) {
276 my %total = (name
=> $col);
277 my @selects = map {+{ value
=> $_ }} (qw(sum min max avg count));
278 $total{'select'} = \
@selects;
279 push @total_by, \
%total;
282 $template->param( 'total_by' => \
@total_by );
285 elsif ( $phase eq 'Choose These Operations' ) {
286 my $area = $input->param('area');
287 my $type = $input->param('type');
288 my $column = $input->param('column');
289 my $criteria = $input->param('criteria');
290 my $definition = $input->param('definition');
291 my @total_by = $input->param('total_by');
293 foreach my $total (@total_by) {
294 my $value = $input->param( $total . "_tvalue" );
295 $totals .= "$value($total),";
303 'criteriastring' => $criteria,
305 'definition' => $definition,
309 my @columns = split( ',', $column );
312 # build structue for use by tmpl_loop to choose columns to order by
313 # need to do something about the order of the order :)
314 foreach my $col (@columns) {
315 my %order = (name
=> $col);
316 my @selects = map {+{ value
=> $_ }} (qw(asc desc));
317 $order{'select'} = \
@selects;
318 push @order_by, \
%order;
321 $template->param( 'order_by' => \
@order_by );
324 elsif ( $phase eq 'Build Report' ) {
326 # now we have all the info we need and can build the sql
327 my $area = $input->param('area');
328 my $type = $input->param('type');
329 my $column = $input->param('column');
330 my $crit = $input->param('criteria');
331 my $totals = $input->param('totals');
332 my $definition = $input->param('definition');
333 my $query_criteria=$crit;
334 # split the columns up by ,
335 my @columns = split( ',', $column );
336 my @order_by = $input->param('order_by');
339 foreach my $order (@order_by) {
340 my $value = $input->param( $order . "_ovalue" );
341 if ($query_orderby) {
342 $query_orderby .= ",$order $value";
345 $query_orderby = " ORDER BY $order $value";
351 build_query
( \
@columns, $query_criteria, $query_orderby, $area, $totals, $definition );
359 elsif ( $phase eq 'Save' ) {
360 # Save the report that has just been built
361 my $sql = $input->param('sql');
362 my $type = $input->param('type');
370 elsif ( $phase eq 'Save Report' ) {
371 # save the sql pasted in by a user
372 my $sql = $input->param('sql');
373 my $name = $input->param('reportname');
374 my $type = $input->param('types');
375 my $notes = $input->param('notes');
376 if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
377 push @errors, {sqlerr
=> $1};
379 elsif ($sql !~ /^(SELECT)/i) {
380 push @errors, {queryerr
=> 1};
384 'errors' => \
@errors,
386 'reportname'=> $name,
392 my $id = save_report
( $borrowernumber, $sql, $name, $type, $notes );
394 'save_successful' => 1,
400 elsif ($phase eq 'Run this report'){
401 # execute a saved report
402 my $limit = 20; # page size. # TODO: move to DB or syspref?
404 my $report = $input->param('reports');
405 my @sql_params = $input->param('sql_params');
407 if ($input->param('page')) {
408 $offset = ($input->param('page') - 1) * $limit;
410 my ($sql,$type,$name,$notes) = get_saved_report
($report);
412 push @errors, {no_sql_for_id
=>$report};
415 # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
416 if ($sql =~ /<</ && !@sql_params) {
417 # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
418 my @split = split /<<|>>/,$sql;
420 for(my $i=0;$i<($#split/2);$i++) {
421 my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
423 if ($authorised_value) {
424 my $dbh=C4
::Context
->dbh;
425 my @authorised_values;
427 # builds list, depending on authorised value...
428 if ( $authorised_value eq "branches" ) {
429 my $branches = GetBranchesLoop
();
430 foreach my $thisbranch (@
$branches) {
431 push @authorised_values, $thisbranch->{value
};
432 $authorised_lib{$thisbranch->{value
}} = $thisbranch->{branchname
};
435 elsif ( $authorised_value eq "itemtypes" ) {
436 my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
438 while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
439 push @authorised_values, $itemtype;
440 $authorised_lib{$itemtype} = $description;
443 elsif ( $authorised_value eq "cn_source" ) {
444 my $class_sources = GetClassSources
();
445 my $default_source = C4
::Context
->preference("DefaultClassificationSource");
446 foreach my $class_source (sort keys %$class_sources) {
447 next unless $class_sources->{$class_source}->{'used'} or
448 ($class_source eq $default_source);
449 push @authorised_values, $class_source;
450 $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
453 elsif ( $authorised_value eq "categorycode" ) {
454 my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description");
456 while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) {
457 push @authorised_values, $categorycode;
458 $authorised_lib{$categorycode} = $description;
461 #---- "true" authorised value
464 my $authorised_values_sth = $dbh->prepare("SELECT authorised_value,lib FROM authorised_values WHERE category=? ORDER BY lib");
466 $authorised_values_sth->execute( $authorised_value);
468 while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
469 push @authorised_values, $value;
470 $authorised_lib{$value} = $lib;
471 # For item location, we show the code and the libelle
472 $authorised_lib{$value} = $lib;
475 $input =CGI
::scrolling_list
( # FIXME: factor out scrolling_list
476 -name
=> "sql_params",
477 -values => \
@authorised_values,
478 # -default => $value,
479 -labels
=> \
%authorised_lib,
487 $input = "<input type='text' name='sql_params'/>";
489 push @tmpl_parameters, {'entry' => $text, 'input' => $input };
491 $template->param('sql' => $sql,
493 'sql_params' => \
@tmpl_parameters,
495 'reports' => $report,
498 # OK, we have parameters, or there are none, we run the report
499 # if there were parameters, replace before running
500 # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
501 my @split = split /<<|>>/,$sql;
503 for(my $i=0;$i<$#split/2;$i++) {
504 my $quoted = C4
::Context
->dbh->quote($sql_params[$i]);
505 # if there are special regexp chars, we must \ them
506 $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
507 $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
509 my ($sth, $errors) = execute_query
($sql, $offset, $limit);
510 my $total = nb_rows
($sql) || 0;
512 die "execute_query failed to return sth for report $report: $sql";
514 my $headref = $sth->{NAME
} || [];
515 my @headers = map { +{ cell
=> $_ } } @
$headref;
516 $template->param(header_row
=> \
@headers);
517 while (my $row = $sth->fetchrow_arrayref()) {
518 my @cells = map { +{ cell
=> $_ } } @
$row;
519 push @rows, { cells
=> \
@cells };
523 my $totpages = int($total/$limit) + (($total % $limit) > 0 ?
1 : 0);
524 my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report&phase=Run%20this%20report";
526 $url = join('&sql_params=', $url, map { URI
::Escape
::uri_escape
($_) } @sql_params);
535 'pagination_bar' => pagination_bar
($url, $totpages, $input->param('page')),
536 'unlimited_total' => $total,
541 elsif ($phase eq 'Export'){
542 binmode STDOUT
, ':encoding(UTF-8)';
544 # export results to tab separated text or CSV
545 my $sql = $input->param('sql'); # FIXME: use sql from saved report ID#, not new user-supplied SQL!
546 my $format = $input->param('format');
547 my ($sth, $q_errors) = execute_query
($sql);
548 unless ($q_errors and @
$q_errors) {
549 print $input->header( -type
=> 'application/octet-stream',
550 -attachment
=>"reportresults.$format"
552 if ($format eq 'tab') {
553 print join("\t", header_cell_values
($sth)), "\n";
554 while (my $row = $sth->fetchrow_arrayref()) {
555 print join("\t", @
$row), "\n";
558 my $csv = Text
::CSV
->new({binary
=> 1});
559 $csv or die "Text::CSV->new({binary => 1}) FAILED: " . Text
::CSV
->error_diag();
560 if ($csv->combine(header_cell_values
($sth))) {
561 print $csv->string(), "\n";
563 push @
$q_errors, { combine
=> 'HEADER ROW: ' . $csv->error_diag() } ;
565 while (my $row = $sth->fetchrow_arrayref()) {
566 if ($csv->combine(@
$row)) {
567 print $csv->string(), "\n";
569 push @
$q_errors, { combine
=> $csv->error_diag() } ;
573 foreach my $err (@
$q_errors, @errors) {
574 print "# ERROR: " . (map {$_ . ": " . $err->{$_}} keys %$err) . "\n";
575 } # here we print all the non-fatal errors at the end. Not super smooth, but better than nothing.
581 'name' => 'Error exporting report!',
583 'errors' => $q_errors,
587 elsif ($phase eq 'Create report from SQL') {
588 # allow the user to paste in sql
589 if ($input->param('sql')) {
591 'sql' => $input->param('sql'),
592 'reportname' => $input->param('reportname'),
593 'notes' => $input->param('notes'),
596 $template->param('create' => 1);
599 elsif ($phase eq 'Create Compound Report'){
600 $template->param( 'savedreports' => get_saved_reports
(),
605 elsif ($phase eq 'Save Compound'){
606 my $master = $input->param('master');
607 my $subreport = $input->param('subreport');
608 my ($mastertables,$subtables) = create_compound
($master,$subreport);
609 $template->param( 'save_compound' => 1,
610 master
=>$mastertables,
615 # pass $sth, get back an array of names for the column headers
616 sub header_cell_values
{
617 my $sth = shift or return ();
618 return @
{$sth->{NAME
}};
621 # pass $sth, get back a TMPL_LOOP-able set of names for the column headers
622 sub header_cell_loop
{
623 my @headers = map { +{ cell
=> $_ } } header_cell_values
(shift);
628 $template->{VARS
}->{'build' . $_} and $template->{VARS
}->{'buildx' . $_} and last;
630 $template->param( 'referer' => $input->referer(),
631 'DHTMLcalendar_dateformat' => C4
::Dates
->DHTMLcalendar(),
634 output_html_with_http_headers
$input, $cookie, $template->output;