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 C4::Reports::Guided qw( delete_report get_report_areas convert_sql update_sql get_saved_reports get_results ValidateSQLParameters format_results get_report_types get_columns get_from_dictionary get_criteria build_query save_report execute_query nb_rows get_report_groups );
  27 use Koha::Reports;
  28 use C4::Auth qw( get_template_and_user get_session );
  29 use C4::Output qw( pagination_bar output_html_with_http_headers );
  30 use C4::Context;
  31 use Koha::Caches;
  32 use C4::Log qw( logaction );
  33 use Koha::AuthorisedValue;
  34 use Koha::AuthorisedValues;
  35 use Koha::BiblioFrameworks;
  36 use Koha::Libraries;
  37 use Koha::Patron::Categories;
  38 use Koha::SharedContent;
  39 use Koha::Util::OpenDocument qw( generate_ods );
  40 use Koha::Notice::Templates;
  41 use Koha::TemplateUtils qw( process_tt );
  42 use C4::ClassSource qw( GetClassSources );
  43 use C4::Scrubber;
  44
  45 =head1 NAME
  46
  47 guided_reports.pl
  48
  49 =head1 DESCRIPTION
  50
  51 Script to control the guided report creation
  52
  53 =cut
  54
  55 my $input = CGI->new;
  56 my $usecache = Koha::Caches->get_instance->memcached_cache;
  57
  58 my $op = $input->param('op') // '';
  59 my $flagsrequired;
  60 if ( ( $op eq 'add_form' ) || ( $op eq 'add_form_sql' ) || ( $op eq 'edit_form' )
  61    || ( $op eq 'duplicate' ) ) {
  62     $flagsrequired = 'create_reports';
  63 }
  64 elsif ( $op eq 'list' ) {
  65     $flagsrequired = 'execute_reports';
  66 }
  67 elsif ( $op eq 'delete' ) {
  68     $flagsrequired = 'delete_reports';
  69 }
  70 else {
  71     $flagsrequired = '*';
  72 }
  73
  74 my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
  75     {
  76         template_name   => "reports/guided_reports_start.tt",
  77         query           => $input,
  78         type            => "intranet",
  79         flagsrequired   => { reports => $flagsrequired },
  80     }
  81 );
  82 my $session_id = $input->cookie('CGISESSID');
  83 my $session = $session_id ? get_session($session_id) : undef;
  84
  85 $template->param( templates => Koha::Notice::Templates->search( { module => 'report' } ) );
  86
  87 my $filter;
  88 if ( $input->param("filter_set") or $input->param('clear_filters') ) {
  89     $filter = {};
  90     $filter->{$_} = $input->param("filter_$_") foreach qw/date author keyword group subgroup/;
  91     $session->param('report_filter', $filter) if $session;
  92     $template->param( 'filter_set' => 1 );
  93 }
  94 elsif ($session and not $input->param('clear_filters')) {
  95     $filter = $session->param('report_filter');
  96 }
  97
  98 my @errors = ();
  99 if ( !$op ) {
 100     $template->param( 'start' => 1 );
 101     # show welcome page
 102 }
 103 elsif ( $op eq 'add_form' ) {
 104     # build a new report
 105     $template->param( 'build1' => 1 );
 106     $template->param(
 107         'areas'        => get_report_areas(),
 108         'usecache'     => $usecache,
 109         'cache_expiry' => 300,
 110         'public'       => '0',
 111     );
 112 }
 113 elsif ( $op eq 'cud-delete') {
 114     my @ids = $input->multi_param('id');
 115     delete_report( @ids );
 116     $op = 'list';
 117 }
 118
 119 elsif ( $op eq 'show'){
 120
 121     my $id = $input->param('id');
 122     my $report = Koha::Reports->find($id);
 123     $template->param(
 124         'id'      => $id,
 125         'reportname' => $report->report_name,
 126         'notes'      => $report->notes,
 127         'sql'     => $report->savedsql,
 128         'showsql' => 1,
 129         'mana_success' => scalar $input->param('mana_success'),
 130         'mana_id' => $report->{mana_id},
 131         'mana_comments' => $report->{comments}
 132     );
 133 }
 134
 135 elsif ( $op eq 'edit_form'){
 136     my $id = $input->param('id');
 137     my $report = Koha::Reports->find($id);
 138     my $group = $report->report_group;
 139     my $subgroup  = $report->report_subgroup;
 140     my $tables = get_tables();
 141     $template->param(
 142         'sql'        => $report->savedsql,
 143         'reportname' => $report->report_name,
 144         'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
 145         'notes'      => $report->notes,
 146         'id'         => $id,
 147         'cache_expiry' => $report->cache_expiry,
 148         'public' => $report->public,
 149         'usecache' => $usecache,
 150         'editsql'    => 1,
 151         'mana_id' => $report->{mana_id},
 152         'mana_comments' => $report->{comments},
 153         'tables' => $tables
 154     );
 155 }
 156
 157 elsif ( $op eq 'cud-update_sql' || $op eq 'cud-update_and_run_sql' ){
 158     my $id         = $input->param('id');
 159     my $sql        = $input->param('sql');
 160     my $reportname = $input->param('reportname');
 161     my $group      = $input->param('group');
 162     my $subgroup   = $input->param('subgroup');
 163     my $notes      = $input->param('notes');
 164     my $cache_expiry = $input->param('cache_expiry');
 165     my $cache_expiry_units = $input->param('cache_expiry_units');
 166     my $public = $input->param('public');
 167     my $save_anyway = $input->param('save_anyway');
 168     my @errors;
 169     my $tables = get_tables();
 170
 171     # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
 172     if( $cache_expiry_units ){
 173       if( $cache_expiry_units eq "minutes" ){
 174         $cache_expiry *= 60;
 175       } elsif( $cache_expiry_units eq "hours" ){
 176         $cache_expiry *= 3600; # 60 * 60
 177       } elsif( $cache_expiry_units eq "days" ){
 178         $cache_expiry *= 86400; # 60 * 60 * 24
 179       }
 180     }
 181     # check $cache_expiry isn't too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp
 182     if( $cache_expiry >= 2592000 ){
 183       push @errors, {cache_expiry => $cache_expiry};
 184     }
 185
 186     create_non_existing_group_and_subgroup($input, $group, $subgroup);
 187
 188     my ( $is_sql_valid, $validation_errors ) = Koha::Report->new({ savedsql => $sql })->is_sql_valid;
 189     push(@errors, @$validation_errors) unless $is_sql_valid;
 190
 191     if (@errors) {
 192         $template->param(
 193             'errors'    => \@errors,
 194             'sql'       => $sql,
 195         );
 196     } else {
 197
 198         # Check defined SQL parameters for authorised value validity
 199         my $problematic_authvals = ValidateSQLParameters($sql);
 200
 201         if ( scalar @$problematic_authvals > 0 && not $save_anyway ) {
 202             # There's at least one problematic parameter, report to the
 203             # GUI and provide all user input for further actions
 204             $template->param(
 205                 'id' => $id,
 206                 'sql' => $sql,
 207                 'reportname' => $reportname,
 208                 'group' => $group,
 209                 'subgroup' => $subgroup,
 210                 'notes' => $notes,
 211                 'public' => $public,
 212                 'problematic_authvals' => $problematic_authvals,
 213                 'warn_authval_problem' => 1,
 214                 'phase_update' => 1,
 215             );
 216
 217         } else {
 218             # No params problem found or asked to save anyway
 219             update_sql( $id, {
 220                     sql => $sql,
 221                     name => $reportname,
 222                     group => $group,
 223                     subgroup => $subgroup,
 224                     notes => $notes,
 225                     public => $public,
 226                     cache_expiry => $cache_expiry,
 227                 } );
 228             $template->param(
 229                 'save_successful'       => 1,
 230                 'reportname'            => $reportname,
 231                 'id'                    => $id,
 232                 'editsql'               => 1,
 233                 'sql'                   => $sql,
 234                 'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
 235                 'notes'                 => $notes,
 236                 'cache_expiry'          => $cache_expiry,
 237                 'public'                => $public,
 238                 'usecache'              => $usecache,
 239                 'tables'                => $tables
 240             );
 241             logaction( "REPORTS", "MODIFY", $id, "$reportname | $sql" ) if C4::Context->preference("ReportsLog");
 242         }
 243         if ( $usecache ) {
 244             $template->param(
 245                 cache_expiry => $cache_expiry,
 246                 cache_expiry_units => $cache_expiry_units,
 247             );
 248         }
 249         if ( $op eq 'cud-update_and_run_sql' ) {
 250             $op = 'run';
 251         }
 252     }
 253 }
 254
 255 elsif ($op eq 'retrieve_results') {
 256     my $id = $input->param('id');
 257     my $result = format_results( $id );
 258     $template->param(
 259         report_name   => $result->{report_name},
 260         notes         => $result->{notes},
 261         saved_results => $result->{results},
 262         date_run      => $result->{date_run},
 263     );
 264 }
 265
 266 elsif ( $op eq 'cud-report' ) {
 267     my $cache_expiry_units = $input->param('cache_expiry_units'),
 268     my $cache_expiry = $input->param('cache_expiry');
 269
 270     # we need to handle converting units
 271     if( $cache_expiry_units eq "minutes" ){
 272       $cache_expiry *= 60;
 273     } elsif( $cache_expiry_units eq "hours" ){
 274       $cache_expiry *= 3600; # 60 * 60
 275     } elsif( $cache_expiry_units eq "days" ){
 276       $cache_expiry *= 86400; # 60 * 60 * 24
 277     }
 278     # check $cache_expiry isn't too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp
 279     if( $cache_expiry >= 2592000 ){ # oops, over the limit of 30 days
 280       # report error to user
 281       $template->param(
 282         'cache_error' => 1,
 283         'build1' => 1,
 284         'areas'   => get_report_areas(),
 285         'cache_expiry' => $cache_expiry,
 286         'usecache' => $usecache,
 287         'public' => scalar $input->param('public'),
 288       );
 289     } else {
 290       # they have chosen a new report and the area to report on
 291       $template->param(
 292           'build2' => 1,
 293           'area'   => scalar $input->param('area'),
 294           'types'  => get_report_types(),
 295           'cache_expiry' => $cache_expiry,
 296           'public' => scalar $input->param('public'),
 297       );
 298     }
 299 }
 300
 301 elsif ( $op eq 'cud-choose_type' ) {
 302     # they have chosen type and area
 303     # get area and type and pass them to the template
 304     my $area = $input->param('area');
 305     my $type = $input->param('types');
 306     $template->param(
 307         'build3' => 1,
 308         'area'   => $area,
 309         'type'   => $type,
 310         columns  => get_columns($area,$input),
 311         'cache_expiry' => scalar $input->param('cache_expiry'),
 312         'public' => scalar $input->param('public'),
 313     );
 314 }
 315
 316 elsif ( $op eq 'cud-choose_columns' ) {
 317     # we now know type, area, and columns
 318     # next step is the constraints
 319     my $area    = $input->param('area');
 320     my $type    = $input->param('type');
 321     my @columns = $input->multi_param('columns');
 322     my $column  = join( ',', @columns );
 323
 324     $template->param(
 325         'build4' => 1,
 326         'area'   => $area,
 327         'type'   => $type,
 328         'column' => $column,
 329         definitions => get_from_dictionary($area),
 330         criteria    => get_criteria($area,$input),
 331         'public' => scalar $input->param('public'),
 332     );
 333     if ( $usecache ) {
 334         $template->param(
 335             cache_expiry => scalar $input->param('cache_expiry'),
 336             cache_expiry_units => scalar $input->param('cache_expiry_units'),
 337         );
 338     }
 339
 340 }
 341
 342 elsif ( $op eq 'cud-choose_criteria' ) {
 343     my $area     = $input->param('area');
 344     my $type     = $input->param('type');
 345     my $column   = $input->param('column');
 346     my @definitions = $input->multi_param('definition');
 347     my $definition = join (',',@definitions);
 348     my @criteria = $input->multi_param('criteria_column');
 349     my $query_criteria;
 350     foreach my $crit (@criteria) {
 351         my $value = $input->param( $crit . "_value" );
 352
 353         # If value is not defined, then it may be range values
 354         if (!defined $value) {
 355             my $fromvalue = $input->param( "from_" . $crit . "_value" );
 356             my $tovalue   = $input->param( "to_"   . $crit . "_value" );
 357
 358             if ($fromvalue && $tovalue) {
 359                 $query_criteria .= " AND $crit >= '$fromvalue' AND $crit <= '$tovalue'";
 360             }
 361         } else {
 362             # don't escape runtime parameters, they'll be at runtime
 363             if ($value =~ /<<.*>>/) {
 364                 $query_criteria .= " AND $crit=$value";
 365             } else {
 366                 $query_criteria .= " AND $crit='$value'";
 367             }
 368         }
 369     }
 370     $template->param(
 371         'build5'         => 1,
 372         'area'           => $area,
 373         'type'           => $type,
 374         'column'         => $column,
 375         'definition'     => $definition,
 376         'criteriastring' => $query_criteria,
 377         'public' => scalar $input->param('public'),
 378     );
 379     if ( $usecache ) {
 380         $template->param(
 381             cache_expiry => scalar $input->param('cache_expiry'),
 382             cache_expiry_units => scalar $input->param('cache_expiry_units'),
 383         );
 384     }
 385
 386     # get columns
 387     my @columns = split( ',', $column );
 388     my @total_by;
 389
 390     # build structue for use by tmpl_loop to choose columns to order by
 391     # need to do something about the order of the order :)
 392         # we also want to use the %columns hash to get the plain english names
 393     foreach my $col (@columns) {
 394         my %total = (name => $col);
 395         my @selects = map {+{ value => $_ }} (qw(sum min max avg count));
 396         $total{'select'} = \@selects;
 397         push @total_by, \%total;
 398     }
 399
 400     $template->param( 'total_by' => \@total_by );
 401 }
 402
 403 elsif ( $op eq 'cud-choose_operations' ) {
 404     my $area     = $input->param('area');
 405     my $type     = $input->param('type');
 406     my $column   = $input->param('column');
 407     my $criteria = $input->param('criteria');
 408         my $definition = $input->param('definition');
 409     my @total_by = $input->multi_param('total_by');
 410     my $totals;
 411     foreach my $total (@total_by) {
 412         my $value = $input->param( $total . "_tvalue" );
 413         $totals .= "$value($total),";
 414     }
 415
 416     $template->param(
 417         'build6'         => 1,
 418         'area'           => $area,
 419         'type'           => $type,
 420         'column'         => $column,
 421         'criteriastring' => $criteria,
 422         'totals'         => $totals,
 423         'definition'     => $definition,
 424         'cache_expiry' => scalar $input->param('cache_expiry'),
 425         'public' => scalar $input->param('public'),
 426     );
 427
 428     # get columns
 429     my @columns = split( ',', $column );
 430     my @order_by;
 431
 432     # build structue for use by tmpl_loop to choose columns to order by
 433     # need to do something about the order of the order :)
 434     foreach my $col (@columns) {
 435         my %order = (name => $col);
 436         my @selects = map {+{ value => $_ }} (qw(asc desc));
 437         $order{'select'} = \@selects;
 438         push @order_by, \%order;
 439     }
 440
 441     $template->param( 'order_by' => \@order_by );
 442 }
 443
 444 elsif ( $op eq 'cud-build_report' ) {
 445
 446     # now we have all the info we need and can build the sql
 447     my $area     = $input->param('area');
 448     my $type     = $input->param('type');
 449     my $column   = $input->param('column');
 450     my $crit     = $input->param('criteria');
 451     my $totals   = $input->param('totals');
 452     my $definition = $input->param('definition');
 453     my $query_criteria=$crit;
 454     # split the columns up by ,
 455     my @columns = split( ',', $column );
 456     my @order_by = $input->multi_param('order_by');
 457
 458     my $query_orderby;
 459     foreach my $order (@order_by) {
 460         my $value = $input->param( $order . "_ovalue" );
 461         if ($query_orderby) {
 462             $query_orderby .= ",$order $value";
 463         }
 464         else {
 465             $query_orderby = " ORDER BY $order $value";
 466         }
 467     }
 468
 469     # get the sql
 470     my $sql =
 471       build_query( \@columns, $query_criteria, $query_orderby, $area, $totals, $definition );
 472     $template->param(
 473         'showreport' => 1,
 474         'area'       => $area,
 475         'sql'        => $sql,
 476         'type'       => $type,
 477         'cache_expiry' => scalar $input->param('cache_expiry'),
 478         'public' => scalar $input->param('public'),
 479     );
 480 }
 481
 482 elsif ( $op eq 'save' ) {
 483     # Save the report that has just been built
 484     my $area = $input->param('area');
 485     my $sql  = $input->param('sql');
 486     my $type = $input->param('type');
 487     $template->param(
 488         'save' => 1,
 489         'area'  => $area,
 490         'sql'  => $sql,
 491         'type' => $type,
 492         'cache_expiry' => scalar $input->param('cache_expiry'),
 493         'public' => scalar $input->param('public'),
 494         'groups_with_subgroups' => groups_with_subgroups($area), # in case we have a report group that matches area
 495     );
 496 }
 497
 498 elsif ( $op eq 'cud-save' ) {
 499     # save the sql pasted in by a user
 500     my $area  = $input->param('area');
 501     my $group = $input->param('group');
 502     my $subgroup = $input->param('subgroup');
 503     my $sql   = $input->param('sql');
 504     my $name  = $input->param('reportname');
 505     my $type  = $input->param('types');
 506     my $notes = $input->param('notes');
 507     my $cache_expiry = $input->param('cache_expiry');
 508     my $cache_expiry_units = $input->param('cache_expiry_units');
 509     my $public = $input->param('public');
 510     my $save_anyway = $input->param('save_anyway');
 511     my $tables = get_tables();
 512
 513
 514     # if we have the units, then we came from creating a report from SQL and thus need to handle converting units
 515     if( $cache_expiry_units ){
 516       if( $cache_expiry_units eq "minutes" ){
 517         $cache_expiry *= 60;
 518       } elsif( $cache_expiry_units eq "hours" ){
 519         $cache_expiry *= 3600; # 60 * 60
 520       } elsif( $cache_expiry_units eq "days" ){
 521         $cache_expiry *= 86400; # 60 * 60 * 24
 522       }
 523     }
 524     # check $cache_expiry isn't too large, Memcached::set requires it to be less than 30 days or it will be treated as if it were an absolute time stamp
 525     if( $cache_expiry && $cache_expiry >= 2592000 ){
 526       push @errors, {cache_expiry => $cache_expiry};
 527     }
 528
 529     create_non_existing_group_and_subgroup($input, $group, $subgroup);
 530     ## FIXME this is AFTER entering a name to save the report under
 531     my ( $is_sql_valid, $validation_errors ) = Koha::Report->new({ savedsql => $sql })->is_sql_valid;
 532     push(@errors, @$validation_errors) unless $is_sql_valid;
 533
 534     if (@errors) {
 535         $template->param(
 536             'errors'    => \@errors,
 537             'sql'       => $sql,
 538             'reportname'=> $name,
 539             'type'      => $type,
 540             'notes'     => $notes,
 541             'cache_expiry' => $cache_expiry,
 542             'public'    => $public,
 543         );
 544     } else {
 545         # Check defined SQL parameters for authorised value validity
 546         my $problematic_authvals = ValidateSQLParameters($sql);
 547
 548         if ( scalar @$problematic_authvals > 0 && not $save_anyway ) {
 549             # There's at least one problematic parameter, report to the
 550             # GUI and provide all user input for further actions
 551             $template->param(
 552                 'area' => $area,
 553                 'group' =>  $group,
 554                 'subgroup' => $subgroup,
 555                 'sql' => $sql,
 556                 'reportname' => $name,
 557                 'type' => $type,
 558                 'notes' => $notes,
 559                 'public' => $public,
 560                 'problematic_authvals' => $problematic_authvals,
 561                 'warn_authval_problem' => 1,
 562                 'phase_save' => 1
 563             );
 564             if ( $usecache ) {
 565                 $template->param(
 566                     cache_expiry => $cache_expiry,
 567                     cache_expiry_units => $cache_expiry_units,
 568                 );
 569             }
 570         } else {
 571             # No params problem found or asked to save anyway
 572             my $id = save_report( {
 573                     borrowernumber => $borrowernumber,
 574                     sql            => $sql,
 575                     name           => $name,
 576                     area           => $area,
 577                     group          => $group,
 578                     subgroup       => $subgroup,
 579                     type           => $type,
 580                     notes          => $notes,
 581                     cache_expiry   => $cache_expiry,
 582                     public         => $public,
 583                 } );
 584                 logaction( "REPORTS", "ADD", $id, "$name | $sql" ) if C4::Context->preference("ReportsLog");
 585             $template->param(
 586                 'save_successful' => 1,
 587                 'reportname'      => $name,
 588                 'id'              => $id,
 589                 'editsql'         => 1,
 590                 'sql'             => $sql,
 591                 'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
 592                 'notes'      => $notes,
 593                 'cache_expiry' => $cache_expiry,
 594                 'public' => $public,
 595                 'usecache' => $usecache,
 596                 'tables' => $tables
 597             );
 598         }
 599     }
 600 }
 601
 602 elsif ($op eq 'cud-share'){
 603     my $lang = $input->param('mana_language') || '';
 604     my $reportid = $input->param('reportid');
 605     my $result = Koha::SharedContent::send_entity($lang, $borrowernumber, $reportid, 'report');
 606     if ( $result ) {
 607         print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?op=listmanamsg=".$result->{msg});
 608     }else{
 609         print $input->redirect("/cgi-bin/koha/reports/guided_reports.pl?op=list&manamsg=noanswer");
 610     }
 611 }
 612
 613 elsif ($op eq 'export'){
 614
 615         # export results to tab separated text or CSV
 616     my $report_id   = $input->param('id');
 617     my $report      = Koha::Reports->find($report_id);
 618     my $sql         = $report->savedsql;
 619     my @param_names = $input->multi_param('param_name');
 620     my @sql_params  = $input->multi_param('sql_params');
 621     my $format      = $input->param('format');
 622     my $reportname  = $input->param('reportname');
 623     my $reportfilename =
 624         $reportname ? "$report_id-$reportname-reportresults.$format" : "$report_id-reportresults.$format";
 625     my $scrubber = C4::Scrubber->new();
 626
 627     ($sql, undef) = $report->prep_report( \@param_names, \@sql_params );
 628     my ( $sth, $q_errors ) = execute_query( { sql => $sql, report_id => $report_id } );
 629     unless ($q_errors and @$q_errors) {
 630         my ( $type, $content );
 631         if ($format eq 'tab') {
 632             $type = 'application/octet-stream';
 633             $content .= join("\t", header_cell_values($sth)) . "\n";
 634             $content = $scrubber->scrub( Encode::decode( 'UTF-8', $content ) );
 635             while ( my $row = $sth->fetchrow_arrayref() ) {
 636                 $content .= join( "\t", $scrubber->scrub(@$row) ) . "\n";
 637             }
 638         } else {
 639             if ( $format eq 'csv' ) {
 640                 my $delimiter = C4::Context->csv_delimiter;
 641                 $type = 'application/csv';
 642                 my $csv = Text::CSV::Encoded->new({ encoding_out => 'UTF-8', sep_char => $delimiter});
 643                 $csv or die "Text::CSV::Encoded->new({binary => 1}) FAILED: " . Text::CSV::Encoded->error_diag();
 644                 if ( $csv->combine( header_cell_values($sth) ) ) {
 645                     $content .= $scrubber->scrub( Encode::decode( 'UTF-8', $csv->string() ) ) . "\n";
 646
 647                 } else {
 648                     push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() };
 649                 }
 650                 while ( my $row = $sth->fetchrow_arrayref() ) {
 651                     if ( $csv->combine(@$row) ) {
 652                         $content .= $scrubber->scrub( $csv->string() ) . "\n";
 653
 654                     } else {
 655                         push @$q_errors, { combine => $csv->error_diag() };
 656                     }
 657                 }
 658             }
 659             elsif ( $format eq 'ods' ) {
 660                 $type = 'application/vnd.oasis.opendocument.spreadsheet';
 661                 my $ods_fh = File::Temp->new( UNLINK => 0 );
 662                 my $ods_filepath = $ods_fh->filename;
 663                 my $ods_content;
 664
 665                 # First line is headers
 666                 my @headers = header_cell_values($sth);
 667                 push @$ods_content, \@headers;
 668
 669                 # Other line in Unicode
 670                 my $sql_rows = $sth->fetchall_arrayref();
 671                 foreach my $sql_row (@$sql_rows) {
 672                     my @content_row;
 673                     foreach my $sql_cell (@$sql_row) {
 674                         push @content_row, $scrubber->scrub( Encode::encode( 'UTF8', $sql_cell ) );
 675
 676                     }
 677                     push @$ods_content, \@content_row;
 678                 }
 679
 680                 # Process
 681                 generate_ods($ods_filepath, $ods_content);
 682
 683                 # Output
 684                 binmode(STDOUT);
 685                 open $ods_fh, '<', $ods_filepath;
 686                 $content .= $_ while <$ods_fh>;
 687                 unlink $ods_filepath;
 688             }
 689             elsif ( $format eq 'template' ) {
 690                 my $template_id     = $input->param('template');
 691                 my $notice_template = Koha::Notice::Templates->find($template_id);
 692                 my $data            = $sth->fetchall_arrayref( {} );
 693                 $content = process_tt(
 694                     $notice_template->content,
 695                     {
 696                         data         => $data,
 697                         report_id    => $report_id,
 698                         for_download => 1,
 699                     }
 700                 );
 701                 $reportfilename = process_tt(
 702                     $notice_template->title,
 703                     {
 704                         data      => $data,
 705                         report_id => $report_id,
 706                     }
 707                 );
 708             }
 709         }
 710         print $input->header(
 711             -type => $type,
 712             -attachment=> $reportfilename
 713         );
 714         print $content;
 715
 716         foreach my $err (@$q_errors, @errors) {
 717             print "# ERROR: " . (map {$_ . ": " . $err->{$_}} keys %$err) . "\n";
 718         }   # here we print all the non-fatal errors at the end.  Not super smooth, but better than nothing.
 719         exit;
 720     }
 721     $template->param(
 722         'sql'           => $sql,
 723         'execute'       => 1,
 724         'name'          => 'Error exporting report!',
 725         'notes'         => '',
 726         'errors'        => $q_errors,
 727     );
 728 }
 729
 730 elsif ( $op eq 'add_form_sql' || $op eq 'duplicate' ) {
 731
 732     my ($group, $subgroup, $sql, $reportname, $notes);
 733     if ( $input->param('sql') ) {
 734         $group      = $input->param('report_group');
 735         $subgroup   = $input->param('report_subgroup');
 736         $sql        = $input->param('sql') // '';
 737         $reportname = $input->param('reportname') // '';
 738         $notes      = $input->param('notes') // '';
 739     }
 740     elsif ( my $report_id = $input->param('id') ) {
 741         my $report = Koha::Reports->find($report_id);
 742         $group      = $report->report_group;
 743         $subgroup   = $report->report_subgroup;
 744         $sql        = $report->savedsql // '';
 745         $reportname = $report->report_name // '';
 746         $notes      = $report->notes // '';
 747     }
 748
 749     my $tables = get_tables();
 750
 751     $template->param(
 752         sql        => $sql,
 753         reportname => $reportname,
 754         notes      => $notes,
 755         'create' => 1,
 756         'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
 757         'public' => '0',
 758         'cache_expiry' => 300,
 759         'usecache' => $usecache,
 760         'tables' => $tables,
 761
 762     );
 763 }
 764
 765 if ($op eq 'run'){
 766     # execute a saved report
 767     my $limit           = $input->param('limit') || 20;
 768     my $offset          = 0;
 769     my $report_id       = $input->param('id');
 770     my @sql_params      = $input->multi_param('sql_params');
 771     my @param_names     = $input->multi_param('param_name');
 772     my $template_id     = $input->param('template');
 773     my $want_full_chart = $input->param('want_full_chart') || 0;
 774
 775
 776     # offset algorithm
 777     if ($input->param('page')) {
 778         $offset = ($input->param('page') - 1) * $limit;
 779     }
 780
 781     $template->param(
 782         'limit' => $limit,
 783         'id'    => $report_id,
 784     );
 785
 786     my ( $sql, $original_sql, $type, $name, $notes );
 787     if (my $report = Koha::Reports->find($report_id)) {
 788         $sql   = $original_sql = $report->savedsql;
 789         $name  = $report->report_name;
 790         $notes = $report->notes;
 791
 792         my @rows = ();
 793         my @allrows = ();
 794         # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
 795         if ($sql =~ /<</ && !@sql_params) {
 796             # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
 797             my @split = split /<<|>>/,$sql;
 798             my @tmpl_parameters;
 799             my @authval_errors;
 800             my %uniq_params;
 801             for(my $i=0;$i<($#split/2);$i++) {
 802                 my ($text,$authorised_value_all) = split /\|/,$split[$i*2+1];
 803                 my $sep = $authorised_value_all ? "|" : "";
 804                 if( defined $uniq_params{$text.$sep.$authorised_value_all} ){
 805                     next;
 806                 } else { $uniq_params{$text.$sep.$authorised_value_all} = "$i"; }
 807                 my ($authorised_value, $param_options) = split /:/, $authorised_value_all;
 808                 my $all;
 809                 my $multiple;
 810                 if ( $param_options eq "all" ) {
 811                     $all = "all";
 812                 } elsif ( $param_options eq "in" ) {
 813                     $multiple = "multiple";
 814                 }
 815                 my $input;
 816                 my $labelid;
 817                 if ( not defined $authorised_value ) {
 818                     # no authorised value input, provide a text box
 819                     $input = "text";
 820                 } elsif ( $authorised_value eq "date" ) {
 821                     # require a date, provide a date picker
 822                     $input = 'date';
 823                 } elsif ( $authorised_value eq "list" ) {
 824                     # require a list, provide a textarea
 825                     $input = 'textarea';
 826                 } else {
 827                     # defined $authorised_value, and not 'date'
 828                     my $dbh=C4::Context->dbh;
 829                     my @authorised_values;
 830                     my %authorised_lib;
 831                     # builds list, depending on authorised value...
 832                     if ( $authorised_value eq "branches" ) {
 833                         my $libraries = Koha::Libraries->search( {}, { order_by => ['branchname'] } );
 834                         while ( my $library = $libraries->next ) {
 835                             push @authorised_values, $library->branchcode;
 836                             $authorised_lib{$library->branchcode} = $library->branchname;
 837                         }
 838                     }
 839                     elsif ( $authorised_value eq "itemtypes" ) {
 840                         my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
 841                         $sth->execute;
 842                         while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
 843                             push @authorised_values, $itemtype;
 844                             $authorised_lib{$itemtype} = $description;
 845                         }
 846                     }
 847                     elsif ( $authorised_value eq "biblio_framework" ) {
 848                         my @frameworks = Koha::BiblioFrameworks->search({}, { order_by => ['frameworktext'] })->as_list;
 849                         my $default_source = '';
 850                         push @authorised_values,$default_source;
 851                         $authorised_lib{$default_source} = 'Default';
 852                         foreach my $framework (@frameworks) {
 853                             push @authorised_values, $framework->frameworkcode;
 854                             $authorised_lib{$framework->frameworkcode} = $framework->frameworktext;
 855                         }
 856                     }
 857                     elsif ( $authorised_value eq "cn_source" ) {
 858                         my $class_sources = GetClassSources();
 859                         my $default_source = C4::Context->preference("DefaultClassificationSource");
 860                         foreach my $class_source (sort keys %$class_sources) {
 861                             next unless $class_sources->{$class_source}->{'used'} or
 862                                         ($class_source eq $default_source);
 863                             push @authorised_values, $class_source;
 864                             $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
 865                         }
 866                     }
 867                     elsif ( $authorised_value eq "categorycode" ) {
 868                         my @patron_categories = Koha::Patron::Categories->search({}, { order_by => ['description']})->as_list;
 869                         %authorised_lib = map { $_->categorycode => $_->description } @patron_categories;
 870                         push @authorised_values, $_->categorycode for @patron_categories;
 871                     }
 872                     elsif ( $authorised_value eq "cash_registers" ) {
 873                         my $sth = $dbh->prepare("SELECT id, name FROM cash_registers ORDER BY description");
 874                         $sth->execute;
 875                         while ( my ( $id, $name ) = $sth->fetchrow_array ) {
 876                             push @authorised_values, $id;
 877                             $authorised_lib{$id} = $name;
 878                         }
 879                     }
 880                     elsif ( $authorised_value eq "debit_types" ) {
 881                         my $sth = $dbh->prepare("SELECT code, description FROM account_debit_types ORDER BY code");
 882                         $sth->execute;
 883                         while ( my ( $code, $description ) = $sth->fetchrow_array ) {
 884                            push @authorised_values, $code;
 885                            $authorised_lib{$code} = $description;
 886                         }
 887                     }
 888                     elsif ( $authorised_value eq "credit_types" ) {
 889                         my $sth = $dbh->prepare("SELECT code, description FROM account_credit_types ORDER BY code");
 890                         $sth->execute;
 891                         while ( my ( $code, $description ) = $sth->fetchrow_array ) {
 892                            push @authorised_values, $code;
 893                            $authorised_lib{$code} = $description;
 894                         }
 895                     }
 896                     else {
 897                         if ( Koha::AuthorisedValues->search({ category => $authorised_value })->count ) {
 898                             my $query = '
 899                             SELECT authorised_value,lib
 900                             FROM authorised_values
 901                             WHERE category=?
 902                             ORDER BY lib
 903                             ';
 904                             my $authorised_values_sth = $dbh->prepare($query);
 905                             $authorised_values_sth->execute( $authorised_value);
 906
 907                             while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
 908                                 push @authorised_values, $value;
 909                                 $authorised_lib{$value} = $lib;
 910                                 # For item location, we show the code and the libelle
 911                                 $authorised_lib{$value} = $lib;
 912                             }
 913                         } else {
 914                             # not exists $authorised_value_categories{$authorised_value})
 915                             push @authval_errors, {'entry' => $text,
 916                                                    'auth_val' => $authorised_value };
 917                             # tell the template there's an error
 918                             $template->param( auth_val_error => 1 );
 919                             # skip scrolling list creation and params push
 920                             next;
 921                         }
 922                     }
 923                     $labelid = $text;
 924                     $labelid =~ s/\W//g;
 925                     $input = {
 926                         name    => "sql_params",
 927                         id      => "sql_params_".$labelid,
 928                         values  => \@authorised_values,
 929                         labels  => \%authorised_lib,
 930                     };
 931                 }
 932
 933                 push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value_all, 'include_all' => $all, 'select_multiple' => $multiple };
 934             }
 935             $template->param('sql'         => $sql,
 936                             'name'         => $name,
 937                             'notes'         => $notes,
 938                             'sql_params'   => \@tmpl_parameters,
 939                             'auth_val_errors'  => \@authval_errors,
 940                             'enter_params' => 1,
 941                             'id'           => $report_id,
 942                             );
 943         } else {
 944             my ($sql,$header_types) = $report->prep_report( \@param_names, \@sql_params );
 945             $template->param(header_types => $header_types);
 946             my ( $sth, $errors ) = execute_query(
 947                 {
 948                     sql        => $sql,
 949                     offset     => $offset,
 950                     limit      => $limit,
 951                     report_id  => $report_id,
 952                 }
 953             );
 954             my $total;
 955             if (!$sth) {
 956                 die "execute_query failed to return sth for report $report_id: $sql";
 957             } elsif ( !$errors ) {
 958                 $total = nb_rows($sql) || 0;
 959                 my $headers = header_cell_loop($sth);
 960                 $template->param(header_row => $headers);
 961                 while (my $row = $sth->fetchrow_arrayref()) {
 962                     my @cells = map { +{ cell => $_ } } @$row;
 963                     push @rows, { cells => \@cells };
 964                 }
 965                 if( $want_full_chart ){
 966                     my ( $sth2, $errors2 ) = execute_query( { sql => $sql, report_id => $report_id } );
 967                     while (my $row = $sth2->fetchrow_arrayref()) {
 968                         my @cells = map { +{ cell => $_ } } @$row;
 969                         push @allrows, { cells => \@cells };
 970                     }
 971                 }
 972
 973                 my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0);
 974                 my $url = "/cgi-bin/koha/reports/guided_reports.pl?id=$report_id&amp;op=run&amp;limit=$limit&amp;want_full_chart=$want_full_chart";
 975                 if (@param_names) {
 976                     $url = join('&amp;param_name=', $url, map { URI::Escape::uri_escape_utf8($_) } @param_names);
 977                 }
 978                 if (@sql_params) {
 979                     $url = join('&amp;sql_params=', $url, map { URI::Escape::uri_escape_utf8($_) } @sql_params);
 980                 }
 981
 982                 if ($template_id) {
 983                     my $notice_template = Koha::Notice::Templates->find($template_id);
 984                     my ( $sth2, $errors2 ) = execute_query( { sql => $sql, report_id => $report_id } );
 985                     my $data = $sth2->fetchall_arrayref( {} );
 986                     my $notice_rendered =
 987                         process_tt( $notice_template->content, { data => $data, report_id => $report_id } );
 988                     my $title_rendered =
 989                         process_tt( $notice_template->title, { data => $data, report_id => $report_id } );
 990                     $template->param(
 991                         template_id            => $template_id,
 992                         processed_notice       => $notice_rendered,
 993                         processed_notice_title => $title_rendered,
 994                     );
 995                 }
 996
 997                 $template->param(
 998                     'results'        => \@rows,
 999                     'allresults'     => \@allrows,
1000                     'pagination_bar' => pagination_bar($url, $totpages, scalar $input->param('page')),
1001                     'unlimited_total' => $total,
1002                 );
1003             }
1004             $template->param(
1005                 'sql'         => $sql,
1006                 original_sql  => $original_sql,
1007                 'id'          => $report_id,
1008                 'execute'     => 1,
1009                 'name'        => $name,
1010                 'notes'       => $notes,
1011                 'errors'      => defined($errors) ? [$errors] : undef,
1012                 'sql_params'  => \@sql_params,
1013                 'param_names' => \@param_names,
1014             );
1015         }
1016     }
1017     else {
1018         push @errors, { no_sql_for_id => $report_id };
1019     }
1020 }
1021
1022 if ( $op eq 'list' || $op eq 'convert') {
1023
1024     if ( $op eq 'convert' ) {
1025         my $report_id = $input->param('id');
1026         my $report    = Koha::Reports->find($report_id);
1027         if ($report) {
1028             my $updated_sql = C4::Reports::Guided::convert_sql( $report->savedsql );
1029             C4::Reports::Guided::update_sql(
1030                 $report_id,
1031                 {
1032                     sql          => $updated_sql,
1033                     name         => $report->report_name,
1034                     group        => $report->report_group,
1035                     subgroup     => $report->report_subgroup,
1036                     notes        => $report->notes,
1037                     public       => $report->public,
1038                     cache_expiry => $report->cache_expiry,
1039                 }
1040             );
1041             $template->param( report_converted => $report->report_name );
1042         }
1043     }
1044
1045     # use a saved report
1046     # get list of reports and display them
1047     my $group = $input->param('group');
1048     my $subgroup = $input->param('subgroup');
1049     $filter->{group} = $group;
1050     $filter->{subgroup} = $subgroup;
1051     my $reports = get_saved_reports($filter);
1052     my $has_obsolete_reports;
1053     for my $report ( @$reports ) {
1054         $report->{results} = C4::Reports::Guided::get_results( $report->{id} );
1055         if ( $report->{savedsql} =~ m|biblioitems| and $report->{savedsql} =~ m|marcxml| ) {
1056             $report->{seems_obsolete} = 1;
1057             $has_obsolete_reports++;
1058         }
1059     }
1060     $template->param(
1061         'manamsg' => $input->param('manamsg') || '',
1062         'saved1'                => 1,
1063         'savedreports'          => $reports,
1064         'usecache'              => $usecache,
1065         'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ),
1066         filters                 => $filter,
1067         has_obsolete_reports    => $has_obsolete_reports,
1068     );
1069 }
1070 # pass $sth, get back an array of names for the column headers
1071 sub header_cell_values {
1072     my $sth = shift or return ();
1073     return '' unless ($sth->{NAME});
1074     return @{$sth->{NAME}};
1075 }
1076
1077 # pass $sth, get back a TMPL_LOOP-able set of names for the column headers
1078 sub header_cell_loop {
1079     my @headers = map { +{ cell => decode('UTF-8',$_) } } header_cell_values (shift);
1080     return \@headers;
1081 }
1082
1083 #get a list of available tables for auto-complete
1084 sub get_tables {
1085     my $result = {};
1086     my $cache  = Koha::Caches->get_instance();
1087     my $tables = $cache->get_from_cache("Reports-SQL_tables-for-autocomplete");
1088
1089     return $tables
1090       if $tables;
1091
1092     $tables = C4::Reports::Guided->get_all_tables();
1093     for my $table (@{$tables}) {
1094         my $sql = "SHOW COLUMNS FROM $table";
1095         my $rows = C4::Context->dbh->selectall_arrayref($sql, { Slice => {} });
1096         for my $row (@{$rows}) {
1097             push @{$result->{$table}}, $row->{Field};
1098         }
1099     }
1100     $cache->set_in_cache("Reports-SQL_tables-for-autocomplete",$result);
1101     return $result;
1102 }
1103
1104 foreach (1..6) {
1105      $template->{VARS}->{'build' . $_} and last;
1106 }
1107 $template->param(   'referer' => $input->referer(),
1108                 );
1109
1110 output_html_with_http_headers $input, $cookie, $template->output;
1111
1112 sub groups_with_subgroups {
1113     my ($group, $subgroup) = @_;
1114
1115     my $groups_with_subgroups = get_report_groups();
1116     my @g_sg;
1117     my @sorted_keys = sort {
1118         $groups_with_subgroups->{$a}->{name} cmp $groups_with_subgroups->{$b}->{name}
1119     } keys %$groups_with_subgroups;
1120     foreach my $g_id (@sorted_keys) {
1121         my $v = $groups_with_subgroups->{$g_id};
1122         my @subgroups;
1123         if (my $sg = $v->{subgroups}) {
1124             foreach my $sg_id (sort { $sg->{$a} cmp $sg->{$b} } keys %$sg) {
1125                 push @subgroups, {
1126                     id => $sg_id,
1127                     name => $sg->{$sg_id},
1128                     selected => ($group && $g_id eq $group && $subgroup && $sg_id eq $subgroup ),
1129                 };
1130             }
1131         }
1132         push @g_sg, {
1133             id => $g_id,
1134             name => $v->{name},
1135             selected => ($group && $g_id eq $group),
1136             subgroups => \@subgroups,
1137         };
1138     }
1139     return \@g_sg;
1140 }
1141
1142 sub create_non_existing_group_and_subgroup {
1143     my ($input, $group, $subgroup) = @_;
1144     if (defined $group and $group ne '') {
1145         my $report_groups = C4::Reports::Guided::get_report_groups;
1146         if (not exists $report_groups->{$group}) {
1147             my $groupdesc = $input->param('groupdesc') // $group;
1148             Koha::AuthorisedValue->new({
1149                 category => 'REPORT_GROUP',
1150                 authorised_value => $group,
1151                 lib => $groupdesc,
1152             })->store;
1153             my $cache_key = "AuthorisedValues-REPORT_GROUP-0-".C4::Context->userenv->{"branch"};
1154             my $cache  = Koha::Caches->get_instance();
1155             my $result = $cache->clear_from_cache($cache_key);
1156         }
1157         if (defined $subgroup and $subgroup ne '') {
1158             if (not exists $report_groups->{$group}->{subgroups}->{$subgroup}) {
1159                 my $subgroupdesc = $input->param('subgroupdesc') // $subgroup;
1160                 Koha::AuthorisedValue->new({
1161                     category => 'REPORT_SUBGROUP',
1162                     authorised_value => $subgroup,
1163                     lib => $subgroupdesc,
1164                     lib_opac => $group,
1165                 })->store;
1166             my $cache_key = "AuthorisedValues-REPORT_SUBGROUP-0-".C4::Context->userenv->{"branch"};
1167             my $cache  = Koha::Caches->get_instance();
1168             my $result = $cache->clear_from_cache($cache_key);
1169             }
1170         }
1171     }
1172 }
1173