reports/guided_reports.pl

   1 #!/usr/bin/perl
   2
   3 # Copyright 2007 Liblime ltd
   4 #
   5 # This file is part of Koha.
   6 #
   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
  10 # version.
  11 #
  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.
  15 #
  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.
  19
  20
  21 use CGI qw/-utf8/;
  22 use Text::CSV;
  23 use URI::Escape;
  24 use C4::Reports::Guided;
  25 use C4::Auth qw/:DEFAULT get_session/;
  26 use C4::Output;
  27 use C4::Dates qw/format_date/;
  28 use C4::Debug;
  29 use C4::Branch; # XXX subfield_is_koha_internal_p
  30 use C4::Koha qw/IsAuthorisedValueCategory/;
  31
  32 =head1 NAME
  33
  34 guided_reports.pl
  35
  36 =head1 DESCRIPTION
  37
  38 Script to control the guided report creation
  39
  40 =cut
  41
  42 my $input = new CGI;
  43 my $usecache = C4::Context->ismemcached;
  44
  45 my $phase = $input->param('phase');
  46 my $flagsrequired;
  47 if ( $phase eq 'Build new' or $phase eq 'Delete Saved' ) {
  48     $flagsrequired = 'create_reports';
  49 }
  50 elsif ( $phase eq 'Use saved' ) {
  51     $flagsrequired = 'execute_reports';
  52 } else {
  53     $flagsrequired = '*';
  54 }
  55
  56 my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
  57     {
  58         template_name   => "reports/guided_reports_start.tmpl",
  59         query           => $input,
  60         type            => "intranet",
  61         authnotrequired => 0,
  62         flagsrequired   => { reports => $flagsrequired },
  63         debug           => 1,
  64     }
  65 );
  66 my $session = $cookie ? get_session($cookie->value) : undef;
  67
  68 my $filter;
  69 if ( $input->param("filter_set") ) {
  70     $filter = {};
  71     $filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword group subgroup/;
  72     $session->param('report_filter', $filter) if $session;
  73     $template->param( 'filter_set' => 1 );
  74 }
  75 elsif ($session) {
  76     $filter = $session->param('report_filter');
  77 }
  78
  79
  80 my @errors = ();
  81 if ( !$phase ) {
  82     $template->param( 'start' => 1 );
  83     # show welcome page
  84 }
  85 elsif ( $phase eq 'Build new' ) {
  86     # build a new report
  87     $template->param( 'build1' => 1 );
  88     my $areas = get_report_areas();
  89     $template->param(
  90         'areas' => [map { id => $_->[0], name => $_->[1] }, @$areas],
  91         'usecache' => $usecache,
  92         'cache_expiry' => 300,
  93         'public' => '0',
  94     );
  95 } elsif ( $phase eq 'Use saved' ) {
  96
  97     # use a saved report
  98     # get list of reports and display them
  99     my $group = $input->param('group');
 100     my $subgroup = $input->param('subgroup');
 101     $filter->{group} = $group;
 102     $filter->{subgroup} = $subgroup;
 103     $template->param(
 104         'saved1' => 1,
 105         'savedreports' => get_saved_reports($filter),
 106         'usecache' => $usecache,
 107         'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup),
 108         dateformat => C4::Context->preference('dateformat'),
 109     );
 110 }
 111
 112 elsif ( $phase eq 'Delete Saved') {
 113
 114         # delete a report from the saved reports list
 115         my $id = $input->param('reports');
 116         delete_report($id);
 117     print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?phase=Use%20saved");
 118         exit;
 119 }
 120
 121 elsif ( $phase eq 'Show SQL'){
 122
 123     my $id = $input->param('reports');
 124     my $report = get_saved_report($id);
 125     $template->param(
 126         'id'      => $id,
 127         'reportname' => $report->{report_name},
 128         'notes'      => $report->{notes},
 129         'sql'     => $report->{savedsql},
 130         'showsql' => 1,
 131     );
 132 }
 133
 134 elsif ( $phase eq 'Edit SQL'){
 135     my $id = $input->param('reports');
 136     my $report = get_saved_report($id);
 137     my $group = $report->{report_group};
 138     my $subgroup  = $report->{report_subgroup};
 139     $template->param(
 140         'sql'        => $report->{savedsql},
 141         'reportname' => $report->{report_name},
 142         'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
 143         'notes'      => $report->{notes},
 144         'id'         => $id,
 145         'cache_expiry' => $report->{cache_expiry},
 146         'public' => $report->{public},
 147         'usecache' => $usecache,
 148         'editsql'    => 1,
 149     );
 150 }
 151
 152 elsif ( $phase eq 'Update SQL'){
 153     my $id         = $input->param('id');
 154     my $sql        = $input->param('sql');
 155     my $reportname = $input->param('reportname');
 156     my $group      = $input->param('group');
 157     my $subgroup   = $input->param('subgroup');
 158     my $notes      = $input->param('notes');
 159     my $cache_expiry = $input->param('cache_expiry');
 160     my $cache_expiry_units = $input->param('cache_expiry_units');
 161     my $public = $input->param('public');
 162     my $save_anyway = $input->param('save_anyway');
 163
 164     my @errors;
 165
 166     # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
 167     if( $cache_expiry_units ){
 168       if( $cache_expiry_units eq "minutes" ){
 169         $cache_expiry *= 60;
 170       } elsif( $cache_expiry_units eq "hours" ){
 171         $cache_expiry *= 3600; # 60 * 60
 172       } elsif( $cache_expiry_units eq "days" ){
 173         $cache_expiry *= 86400; # 60 * 60 * 24
 174       }
 175     }
 176     # 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
 177     if( $cache_expiry >= 2592000 ){
 178       push @errors, {cache_expiry => $cache_expiry};
 179     }
 180
 181     create_non_existing_group_and_subgroup($input, $group, $subgroup);
 182
 183     if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
 184         push @errors, {sqlerr => $1};
 185     }
 186     elsif ($sql !~ /^(SELECT)/i) {
 187         push @errors, {queryerr => 1};
 188     }
 189
 190     if (@errors) {
 191         $template->param(
 192             'errors'    => \@errors,
 193             'sql'       => $sql,
 194         );
 195     } else {
 196
 197         # Check defined SQL parameters for authorised value validity
 198         my $problematic_authvals = ValidateSQLParameters($sql);
 199
 200         if ( scalar @$problematic_authvals > 0 && not $save_anyway ) {
 201             # There's at least one problematic parameter, report to the
 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                 'cache_expiry' => $cache_expiry,
 211                 'cache_expiry_units' => $cache_expiry_units,
 212                 'public' => $public,
 213                 'problematic_authvals' => $problematic_authvals,
 214                 'warn_authval_problem' => 1,
 215                 'phase_update' => 1
 216             );
 217
 218         } else {
 219             # No params problem found or asked to save anyway
 220             update_sql( $id, {
 221                     sql => $sql,
 222                     name => $reportname,
 223                     group => $group,
 224                     subgroup => $subgroup,
 225                     notes => $notes,
 226                     cache_expiry => $cache_expiry,
 227                     public => $public,
 228                 } );
 229             $template->param(
 230                 'save_successful'       => 1,
 231                 'reportname'            => $reportname,
 232                 'id'                    => $id,
 233             );
 234         }
 235     }
 236 }
 237
 238 elsif ($phase eq 'retrieve results') {
 239         my $id = $input->param('id');
 240         my ($results,$name,$notes) = format_results($id);
 241         # do something
 242         $template->param(
 243                 'retresults' => 1,
 244                 'results' => $results,
 245                 'name' => $name,
 246                 'notes' => $notes,
 247     );
 248 }
 249
 250 elsif ( $phase eq 'Report on this Area' ) {
 251     my $cache_expiry_units = $input->param('cache_expiry_units'),
 252     my $cache_expiry = $input->param('cache_expiry');
 253
 254     # we need to handle converting units
 255     if( $cache_expiry_units eq "minutes" ){
 256       $cache_expiry *= 60;
 257     } elsif( $cache_expiry_units eq "hours" ){
 258       $cache_expiry *= 3600; # 60 * 60
 259     } elsif( $cache_expiry_units eq "days" ){
 260       $cache_expiry *= 86400; # 60 * 60 * 24
 261     }
 262     # 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
 263     if( $cache_expiry >= 2592000 ){ # oops, over the limit of 30 days
 264       # report error to user
 265       $template->param(
 266         'cache_error' => 1,
 267         'build1' => 1,
 268         'areas'   => get_report_areas(),
 269         'cache_expiry' => $cache_expiry,
 270         'usecache' => $usecache,
 271         'public' => $input->param('public'),
 272       );
 273     } else {
 274       # they have choosen a new report and the area to report on
 275       $template->param(
 276           'build2' => 1,
 277           'area'   => $input->param('area'),
 278           'types'  => get_report_types(),
 279           'cache_expiry' => $cache_expiry,
 280           'public' => $input->param('public'),
 281       );
 282     }
 283 }
 284
 285 elsif ( $phase eq 'Choose this type' ) {
 286     # they have chosen type and area
 287     # get area and type and pass them to the template
 288     my $area = $input->param('area');
 289     my $type = $input->param('types');
 290     $template->param(
 291         'build3' => 1,
 292         'area'   => $area,
 293         'type'   => $type,
 294         columns  => get_columns($area,$input),
 295         'cache_expiry' => $input->param('cache_expiry'),
 296         'public' => $input->param('public'),
 297     );
 298 }
 299
 300 elsif ( $phase eq 'Choose these columns' ) {
 301     # we now know type, area, and columns
 302     # next step is the constraints
 303     my $area    = $input->param('area');
 304     my $type    = $input->param('type');
 305     my @columns = $input->param('columns');
 306     my $column  = join( ',', @columns );
 307     $template->param(
 308         'build4' => 1,
 309         'area'   => $area,
 310         'type'   => $type,
 311         'column' => $column,
 312         definitions => get_from_dictionary($area),
 313         criteria    => get_criteria($area,$input),
 314         'cache_expiry' => $input->param('cache_expiry'),
 315         'cache_expiry_units' => $input->param('cache_expiry_units'),
 316         'public' => $input->param('public'),
 317     );
 318 }
 319
 320 elsif ( $phase eq 'Choose these criteria' ) {
 321     my $area     = $input->param('area');
 322     my $type     = $input->param('type');
 323     my $column   = $input->param('column');
 324     my @definitions = $input->param('definition');
 325     my $definition = join (',',@definitions);
 326     my @criteria = $input->param('criteria_column');
 327     my $query_criteria;
 328     foreach my $crit (@criteria) {
 329         my $value = $input->param( $crit . "_value" );
 330
 331         # If value is not defined, then it may be range values
 332         if (!defined $value) {
 333
 334             my $fromvalue = $input->param( "from_" . $crit . "_value" );
 335             my $tovalue   = $input->param( "to_"   . $crit . "_value" );
 336
 337             # If the range values are dates
 338             if ($fromvalue =~ C4::Dates->regexp('syspref') && $tovalue =~ C4::Dates->regexp('syspref')) {
 339                 $fromvalue = C4::Dates->new($fromvalue)->output("iso");
 340                 $tovalue = C4::Dates->new($tovalue)->output("iso");
 341             }
 342
 343             if ($fromvalue && $tovalue) {
 344                 $query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'";
 345             }
 346
 347         } else {
 348
 349             # If value is a date
 350             if ($value =~ C4::Dates->regexp('syspref')) {
 351                 $value = C4::Dates->new($value)->output("iso");
 352             }
 353             # don't escape runtime parameters, they'll be at runtime
 354             if ($value =~ /<<.*>>/) {
 355                 $query_criteria .= " AND $crit=$value";
 356             } else {
 357                 $query_criteria .= " AND $crit='$value'";
 358             }
 359         }
 360     }
 361     $template->param(
 362         'build5'         => 1,
 363         'area'           => $area,
 364         'type'           => $type,
 365         'column'         => $column,
 366         'definition'     => $definition,
 367         'criteriastring' => $query_criteria,
 368         'cache_expiry' => $input->param('cache_expiry'),
 369         'cache_expiry_units' => $input->param('cache_expiry_units'),
 370         'public' => $input->param('public'),
 371     );
 372
 373     # get columns
 374     my @columns = split( ',', $column );
 375     my @total_by;
 376
 377     # build structue for use by tmpl_loop to choose columns to order by
 378     # need to do something about the order of the order :)
 379         # we also want to use the %columns hash to get the plain english names
 380     foreach my $col (@columns) {
 381         my %total = (name => $col);
 382         my @selects = map {+{ value => $_ }} (qw(sum min max avg count));
 383         $total{'select'} = \@selects;
 384         push @total_by, \%total;
 385     }
 386
 387     $template->param( 'total_by' => \@total_by );
 388 }
 389
 390 elsif ( $phase eq 'Choose these operations' ) {
 391     my $area     = $input->param('area');
 392     my $type     = $input->param('type');
 393     my $column   = $input->param('column');
 394     my $criteria = $input->param('criteria');
 395         my $definition = $input->param('definition');
 396     my @total_by = $input->param('total_by');
 397     my $totals;
 398     foreach my $total (@total_by) {
 399         my $value = $input->param( $total . "_tvalue" );
 400         $totals .= "$value($total),";
 401     }
 402
 403     $template->param(
 404         'build6'         => 1,
 405         'area'           => $area,
 406         'type'           => $type,
 407         'column'         => $column,
 408         'criteriastring' => $criteria,
 409         'totals'         => $totals,
 410         'definition'     => $definition,
 411         'cache_expiry' => $input->param('cache_expiry'),
 412         'public' => $input->param('public'),
 413     );
 414
 415     # get columns
 416     my @columns = split( ',', $column );
 417     my @order_by;
 418
 419     # build structue for use by tmpl_loop to choose columns to order by
 420     # need to do something about the order of the order :)
 421     foreach my $col (@columns) {
 422         my %order = (name => $col);
 423         my @selects = map {+{ value => $_ }} (qw(asc desc));
 424         $order{'select'} = \@selects;
 425         push @order_by, \%order;
 426     }
 427
 428     $template->param( 'order_by' => \@order_by );
 429 }
 430
 431 elsif ( $phase eq 'Build report' ) {
 432
 433     # now we have all the info we need and can build the sql
 434     my $area     = $input->param('area');
 435     my $type     = $input->param('type');
 436     my $column   = $input->param('column');
 437     my $crit     = $input->param('criteria');
 438     my $totals   = $input->param('totals');
 439     my $definition = $input->param('definition');
 440     my $query_criteria=$crit;
 441     # split the columns up by ,
 442     my @columns = split( ',', $column );
 443     my @order_by = $input->param('order_by');
 444
 445     my $query_orderby;
 446     foreach my $order (@order_by) {
 447         my $value = $input->param( $order . "_ovalue" );
 448         if ($query_orderby) {
 449             $query_orderby .= ",$order $value";
 450         }
 451         else {
 452             $query_orderby = " ORDER BY $order $value";
 453         }
 454     }
 455
 456     # get the sql
 457     my $sql =
 458       build_query( \@columns, $query_criteria, $query_orderby, $area, $totals, $definition );
 459     $template->param(
 460         'showreport' => 1,
 461         'area'       => $area,
 462         'sql'        => $sql,
 463         'type'       => $type,
 464         'cache_expiry' => $input->param('cache_expiry'),
 465         'public' => $input->param('public'),
 466     );
 467 }
 468
 469 elsif ( $phase eq 'Save' ) {
 470     # Save the report that has just been built
 471     my $area           = $input->param('area');
 472     my $sql  = $input->param('sql');
 473     my $type = $input->param('type');
 474     $template->param(
 475         'save' => 1,
 476         'area'  => $area,
 477         'sql'  => $sql,
 478         'type' => $type,
 479         'cache_expiry' => $input->param('cache_expiry'),
 480         'public' => $input->param('public'),
 481         'groups_with_subgroups' => groups_with_subgroups($area), # in case we have a report group that matches area
 482     );
 483 }
 484
 485 elsif ( $phase eq 'Save Report' ) {
 486     # save the sql pasted in by a user
 487     my $area  = $input->param('area');
 488     my $group = $input->param('group');
 489     my $subgroup = $input->param('subgroup');
 490     my $sql   = $input->param('sql');
 491     my $name  = $input->param('reportname');
 492     my $type  = $input->param('types');
 493     my $notes = $input->param('notes');
 494     my $cache_expiry = $input->param('cache_expiry');
 495     my $cache_expiry_units = $input->param('cache_expiry_units');
 496     my $public = $input->param('public');
 497     my $save_anyway = $input->param('save_anyway');
 498
 499
 500     # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
 501     if( $cache_expiry_units ){
 502       if( $cache_expiry_units eq "minutes" ){
 503         $cache_expiry *= 60;
 504       } elsif( $cache_expiry_units eq "hours" ){
 505         $cache_expiry *= 3600; # 60 * 60
 506       } elsif( $cache_expiry_units eq "days" ){
 507         $cache_expiry *= 86400; # 60 * 60 * 24
 508       }
 509     }
 510     # 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
 511     if( $cache_expiry && $cache_expiry >= 2592000 ){
 512       push @errors, {cache_expiry => $cache_expiry};
 513     }
 514
 515     create_non_existing_group_and_subgroup($input, $group, $subgroup);
 516
 517     ## FIXME this is AFTER entering a name to save the report under
 518     if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
 519         push @errors, {sqlerr => $1};
 520     }
 521     elsif ($sql !~ /^(SELECT)/i) {
 522         push @errors, {queryerr => "No SELECT"};
 523     }
 524
 525     if (@errors) {
 526         $template->param(
 527             'errors'    => \@errors,
 528             'sql'       => $sql,
 529             'reportname'=> $name,
 530             'type'      => $type,
 531             'notes'     => $notes,
 532             'cache_expiry' => $cache_expiry,
 533             'public'    => $public,
 534         );
 535     } else {
 536         # Check defined SQL parameters for authorised value validity
 537         my $problematic_authvals = ValidateSQLParameters($sql);
 538
 539         if ( scalar @$problematic_authvals > 0 && not $save_anyway ) {
 540             # There's at least one problematic parameter, report to the
 541             # GUI and provide all user input for further actions
 542             $template->param(
 543                 'area' => $area,
 544                 'group' =>  $group,
 545                 'subgroup' => $subgroup,
 546                 'sql' => $sql,
 547                 'reportname' => $name,
 548                 'type' => $type,
 549                 'notes' => $notes,
 550                 'cache_expiry' => $cache_expiry,
 551                 'cache_expiry_units' => $cache_expiry_units,
 552                 'public' => $public,
 553                 'problematic_authvals' => $problematic_authvals,
 554                 'warn_authval_problem' => 1,
 555                 'phase_save' => 1
 556             );
 557         } else {
 558             # No params problem found or asked to save anyway
 559             my $id = save_report( {
 560                     borrowernumber => $borrowernumber,
 561                     sql            => $sql,
 562                     name           => $name,
 563                     area           => $area,
 564                     group          => $group,
 565                     subgroup       => $subgroup,
 566                     type           => $type,
 567                     notes          => $notes,
 568                     cache_expiry   => $cache_expiry,
 569                     public         => $public,
 570                 } );
 571             $template->param(
 572                 'save_successful' => 1,
 573                 'reportname'      => $name,
 574                 'id'              => $id,
 575             );
 576         }
 577     }
 578 }
 579
 580 elsif ($phase eq 'Run this report'){
 581     # execute a saved report
 582     my $limit      = $input->param('limit') || 20;
 583     my $offset     = 0;
 584     my $report_id  = $input->param('reports');
 585     my @sql_params = $input->param('sql_params');
 586     # offset algorithm
 587     if ($input->param('page')) {
 588         $offset = ($input->param('page') - 1) * $limit;
 589     }
 590
 591     $template->param(
 592         'limit'   => $limit,
 593         'report_id' => $report_id,
 594     );
 595
 596     my ( $sql, $type, $name, $notes );
 597     if (my $report = get_saved_report($report_id)) {
 598         $sql   = $report->{savedsql};
 599         $name  = $report->{report_name};
 600         $notes = $report->{notes};
 601
 602         my @rows = ();
 603         # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
 604         if ($sql =~ /<</ && !@sql_params) {
 605             # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
 606             my @split = split /<<|>>/,$sql;
 607             my @tmpl_parameters;
 608             my @authval_errors;
 609             for(my $i=0;$i<($#split/2);$i++) {
 610                 my ($text,$authorised_value) = split /\|/,$split[$i*2+1];
 611                 my $input;
 612                 my $labelid;
 613                 if ( not defined $authorised_value ) {
 614                     # no authorised value input, provide a text box
 615                     $input = "text";
 616                 } elsif ( $authorised_value eq "date" ) {
 617                     # require a date, provide a date picker
 618                     $input = 'date';
 619                 } else {
 620                     # defined $authorised_value, and not 'date'
 621                     my $dbh=C4::Context->dbh;
 622                     my @authorised_values;
 623                     my %authorised_lib;
 624                     # builds list, depending on authorised value...
 625                     if ( $authorised_value eq "branches" ) {
 626                         my $branches = GetBranchesLoop();
 627                         foreach my $thisbranch (@$branches) {
 628                             push @authorised_values, $thisbranch->{value};
 629                             $authorised_lib{$thisbranch->{value}} = $thisbranch->{branchname};
 630                         }
 631                     }
 632                     elsif ( $authorised_value eq "itemtypes" ) {
 633                         my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
 634                         $sth->execute;
 635                         while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
 636                             push @authorised_values, $itemtype;
 637                             $authorised_lib{$itemtype} = $description;
 638                         }
 639                     }
 640                     elsif ( $authorised_value eq "cn_source" ) {
 641                         my $class_sources = GetClassSources();
 642                         my $default_source = C4::Context->preference("DefaultClassificationSource");
 643                         foreach my $class_source (sort keys %$class_sources) {
 644                             next unless $class_sources->{$class_source}->{'used'} or
 645                                         ($class_source eq $default_source);
 646                             push @authorised_values, $class_source;
 647                             $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
 648                         }
 649                     }
 650                     elsif ( $authorised_value eq "categorycode" ) {
 651                         my $sth = $dbh->prepare("SELECT categorycode, description FROM categories ORDER BY description");
 652                         $sth->execute;
 653                         while ( my ( $categorycode, $description ) = $sth->fetchrow_array ) {
 654                             push @authorised_values, $categorycode;
 655                             $authorised_lib{$categorycode} = $description;
 656                         }
 657
 658                         #---- "true" authorised value
 659                     }
 660                     else {
 661                         if ( IsAuthorisedValueCategory($authorised_value) ) {
 662                             my $query = '
 663                             SELECT authorised_value,lib
 664                             FROM authorised_values
 665                             WHERE category=?
 666                             ORDER BY lib
 667                             ';
 668                             my $authorised_values_sth = $dbh->prepare($query);
 669                             $authorised_values_sth->execute( $authorised_value);
 670
 671                             while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
 672                                 push @authorised_values, $value;
 673                                 $authorised_lib{$value} = $lib;
 674                                 # For item location, we show the code and the libelle
 675                                 $authorised_lib{$value} = $lib;
 676                             }
 677                         } else {
 678                             # not exists $authorised_value_categories{$authorised_value})
 679                             push @authval_errors, {'entry' => $text,
 680                                                    'auth_val' => $authorised_value };
 681                             # tell the template there's an error
 682                             $template->param( auth_val_error => 1 );
 683                             # skip scrolling list creation and params push
 684                             next;
 685                         }
 686                     }
 687                     $labelid = $text;
 688                     $labelid =~ s/\W//g;
 689                     $input =CGI::scrolling_list(      # FIXME: factor out scrolling_list
 690                         -name     => "sql_params",
 691                         -id       => "sql_params_".$labelid,
 692                         -values   => \@authorised_values,
 693 #                     -default  => $value,
 694                         -labels   => \%authorised_lib,
 695                         -override => 1,
 696                         -size     => 1,
 697                         -multiple => 0,
 698                         -tabindex => 1,
 699                     );
 700                 }
 701
 702                 push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid };
 703             }
 704             $template->param('sql'         => $sql,
 705                             'name'         => $name,
 706                             'sql_params'   => \@tmpl_parameters,
 707                             'auth_val_errors'  => \@authval_errors,
 708                             'enter_params' => 1,
 709                             'reports'      => $report_id,
 710                             );
 711         } else {
 712             # OK, we have parameters, or there are none, we run the report
 713             # if there were parameters, replace before running
 714             # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
 715             my @split = split /<<|>>/,$sql;
 716             my @tmpl_parameters;
 717             for(my $i=0;$i<$#split/2;$i++) {
 718                 my $quoted = C4::Context->dbh->quote($sql_params[$i]);
 719                 # if there are special regexp chars, we must \ them
 720                 $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
 721                 $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
 722             }
 723             my ($sth, $errors) = execute_query($sql, $offset, $limit);
 724             my $total = nb_rows($sql) || 0;
 725             unless ($sth) {
 726                 die "execute_query failed to return sth for report $report_id: $sql";
 727             } else {
 728                 my $headers= header_cell_loop($sth);
 729                 $template->param(header_row => $headers);
 730                 while (my $row = $sth->fetchrow_arrayref()) {
 731                     my @cells = map { +{ cell => $_ } } @$row;
 732                     push @rows, { cells => \@cells };
 733                 }
 734             }
 735
 736             my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0);
 737             my $url = "/cgi-bin/koha/reports/guided_reports.pl?reports=$report_id&amp;phase=Run%20this%20report&amp;limit=$limit";
 738             if (@sql_params) {
 739                 $url = join('&amp;sql_params=', $url, map { URI::Escape::uri_escape($_) } @sql_params);
 740             }
 741             $template->param(
 742                 'results' => \@rows,
 743                 'sql'     => $sql,
 744                 'id'      => $report_id,
 745                 'execute' => 1,
 746                 'name'    => $name,
 747                 'notes'   => $notes,
 748                 'errors'  => $errors,
 749                 'pagination_bar'  => pagination_bar($url, $totpages, $input->param('page')),
 750                 'unlimited_total' => $total,
 751                 'sql_params'      => \@sql_params,
 752             );
 753         }
 754     }
 755     else {
 756         push @errors, { no_sql_for_id => $report_id };
 757     }
 758 }
 759
 760 elsif ($phase eq 'Export'){
 761     binmode STDOUT, ':encoding(UTF-8)';
 762
 763         # export results to tab separated text or CSV
 764         my $sql    = $input->param('sql');  # FIXME: use sql from saved report ID#, not new user-supplied SQL!
 765     my $format = $input->param('format');
 766         my ($sth, $q_errors) = execute_query($sql);
 767     unless ($q_errors and @$q_errors) {
 768         print $input->header(       -type => 'application/octet-stream',
 769                                     -attachment=>"reportresults.$format"
 770                             );
 771         if ($format eq 'tab') {
 772             print join("\t", header_cell_values($sth)), "\n";
 773             while (my $row = $sth->fetchrow_arrayref()) {
 774                 print join("\t", @$row), "\n";
 775             }
 776         } else {
 777             my $csv = Text::CSV->new({binary => 1});
 778             $csv or die "Text::CSV->new({binary => 1}) FAILED: " . Text::CSV->error_diag();
 779             if ($csv->combine(header_cell_values($sth))) {
 780                 print $csv->string(), "\n";
 781             } else {
 782                 push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() } ;
 783             }
 784             while (my $row = $sth->fetchrow_arrayref()) {
 785                 if ($csv->combine(@$row)) {
 786                     print $csv->string(), "\n";
 787                 } else {
 788                     push @$q_errors, { combine => $csv->error_diag() } ;
 789                 }
 790             }
 791         }
 792         foreach my $err (@$q_errors, @errors) {
 793             print "# ERROR: " . (map {$_ . ": " . $err->{$_}} keys %$err) . "\n";
 794         }   # here we print all the non-fatal errors at the end.  Not super smooth, but better than nothing.
 795         exit;
 796     }
 797     $template->param(
 798         'sql'           => $sql,
 799         'execute'       => 1,
 800         'name'          => 'Error exporting report!',
 801         'notes'         => '',
 802         'errors'        => $q_errors,
 803     );
 804 }
 805
 806 elsif ( $phase eq 'Create report from SQL' ) {
 807
 808     my ($group, $subgroup);
 809     # allow the user to paste in sql
 810     if ( $input->param('sql') ) {
 811         $group = $input->param('report_group');
 812         $subgroup  = $input->param('report_subgroup');
 813         $template->param(
 814             'sql'           => $input->param('sql') // '',
 815             'reportname'    => $input->param('reportname') // '',
 816             'notes'         => $input->param('notes') // '',
 817         );
 818     }
 819     $template->param(
 820         'create' => 1,
 821         'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
 822         'public' => '0',
 823         'cache_expiry' => 300,
 824         'usecache' => $usecache,
 825     );
 826 }
 827
 828 elsif ($phase eq 'Create Compound Report'){
 829         $template->param( 'savedreports' => get_saved_reports(),
 830                 'compound' => 1,
 831         );
 832 }
 833
 834 elsif ($phase eq 'Save Compound'){
 835     my $master    = $input->param('master');
 836         my $subreport = $input->param('subreport');
 837         my ($mastertables,$subtables) = create_compound($master,$subreport);
 838         $template->param( 'save_compound' => 1,
 839                 master=>$mastertables,
 840                 subsql=>$subtables
 841         );
 842 }
 843
 844 # pass $sth, get back an array of names for the column headers
 845 sub header_cell_values {
 846     my $sth = shift or return ();
 847     my @cols;
 848     foreach my $c (@{$sth->{NAME}}) {
 849         #FIXME apparently DBI still needs a utf8 fix for this?
 850         utf8::decode($c);
 851         push @cols, $c;
 852     }
 853     return @cols;
 854 }
 855
 856 # pass $sth, get back a TMPL_LOOP-able set of names for the column headers
 857 sub header_cell_loop {
 858     my @headers = map { +{ cell => $_ } } header_cell_values (shift);
 859     return \@headers;
 860 }
 861
 862 foreach (1..6) {
 863      $template->{VARS}->{'build' . $_} and $template->{VARS}->{'buildx' . $_} and last;
 864 }
 865 $template->param(   'referer' => $input->referer(),
 866                 );
 867
 868 output_html_with_http_headers $input, $cookie, $template->output;
 869
 870 sub groups_with_subgroups {
 871     my ($group, $subgroup) = @_;
 872
 873     my $groups_with_subgroups = get_report_groups();
 874     my @g_sg;
 875     my @sorted_keys = sort {
 876         $groups_with_subgroups->{$a}->{name} cmp $groups_with_subgroups->{$b}->{name}
 877     } keys %$groups_with_subgroups;
 878     foreach my $g_id (@sorted_keys) {
 879         my $v = $groups_with_subgroups->{$g_id};
 880         my @subgroups;
 881         if (my $sg = $v->{subgroups}) {
 882             foreach my $sg_id (sort { $sg->{$a} cmp $sg->{$b} } keys %$sg) {
 883                 push @subgroups, {
 884                     id => $sg_id,
 885                     name => $sg->{$sg_id},
 886                     selected => ($group && $g_id eq $group && $subgroup && $sg_id eq $subgroup ),
 887                 };
 888             }
 889         }
 890         push @g_sg, {
 891             id => $g_id,
 892             name => $v->{name},
 893             selected => ($group && $g_id eq $group),
 894             subgroups => \@subgroups,
 895         };
 896     }
 897     return \@g_sg;
 898 }
 899
 900 sub create_non_existing_group_and_subgroup {
 901     my ($input, $group, $subgroup) = @_;
 902
 903     if (defined $group and $group ne '') {
 904         my $report_groups = C4::Reports::Guided::get_report_groups;
 905         if (not exists $report_groups->{$group}) {
 906             my $groupdesc = $input->param('groupdesc') // $group;
 907             C4::Koha::AddAuthorisedValue('REPORT_GROUP', $group, $groupdesc);
 908         }
 909         if (defined $subgroup and $subgroup ne '') {
 910             if (not exists $report_groups->{$group}->{subgroups}->{$subgroup}) {
 911                 my $subgroupdesc = $input->param('subgroupdesc') // $subgroup;
 912                 C4::Koha::AddAuthorisedValue('REPORT_SUBGROUP', $subgroup, $subgroupdesc, $group);
 913             }
 914         }
 915     }
 916 }