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