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 HTML::Restrict;
  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 = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ;
 624     my $hr             = HTML::Restrict->new();
 625
 626     ($sql, undef) = $report->prep_report( \@param_names, \@sql_params );
 627     my ( $sth, $q_errors ) = execute_query( { sql => $sql, report_id => $report_id } );
 628     unless ($q_errors and @$q_errors) {
 629         my ( $type, $content );
 630         if ($format eq 'tab') {
 631             $type = 'application/octet-stream';
 632             $content .= join("\t", header_cell_values($sth)) . "\n";
 633             $content = $hr->process(Encode::decode('UTF-8', $content));
 634             while (my $row = $sth->fetchrow_arrayref()) {
 635                 $content .= join("\t", $hr->process(@$row)) . "\n";
 636             }
 637         } else {
 638             if ( $format eq 'csv' ) {
 639                 my $delimiter = C4::Context->csv_delimiter;
 640                 $type = 'application/csv';
 641                 my $csv = Text::CSV::Encoded->new({ encoding_out => 'UTF-8', sep_char => $delimiter});
 642                 $csv or die "Text::CSV::Encoded->new({binary => 1}) FAILED: " . Text::CSV::Encoded->error_diag();
 643                 if ($csv->combine(header_cell_values($sth))) {
 644                     $content .= $hr->process(Encode::decode('UTF-8', $csv->string())) . "\n";
 645
 646                 } else {
 647                     push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() } ;
 648                 }
 649                 while (my $row = $sth->fetchrow_arrayref()) {
 650                     if ($csv->combine(@$row)) {
 651                         $content .= $hr->process($csv->string()) . "\n";
 652
 653                     } else {
 654                         push @$q_errors, { combine => $csv->error_diag() } ;
 655                     }
 656                 }
 657             }
 658             elsif ( $format eq 'ods' ) {
 659                 $type = 'application/vnd.oasis.opendocument.spreadsheet';
 660                 my $ods_fh = File::Temp->new( UNLINK => 0 );
 661                 my $ods_filepath = $ods_fh->filename;
 662                 my $ods_content;
 663
 664                 # First line is headers
 665                 my @headers = header_cell_values($sth);
 666                 push @$ods_content, \@headers;
 667
 668                 # Other line in Unicode
 669                 my $sql_rows = $sth->fetchall_arrayref();
 670                 foreach my $sql_row ( @$sql_rows ) {
 671                     my @content_row;
 672                     foreach my $sql_cell ( @$sql_row ) {
 673                         push @content_row, $hr->process(Encode::encode( 'UTF8', $sql_cell ));
 674
 675                     }
 676                     push @$ods_content, \@content_row;
 677                 }
 678
 679                 # Process
 680                 generate_ods($ods_filepath, $ods_content);
 681
 682                 # Output
 683                 binmode(STDOUT);
 684                 open $ods_fh, '<', $ods_filepath;
 685                 $content .= $_ while <$ods_fh>;
 686                 unlink $ods_filepath;
 687             }
 688             elsif ( $format eq 'template' ) {
 689                 my $template_id     = $input->param('template');
 690                 my $notice_template = Koha::Notice::Templates->find($template_id);
 691                 my $data            = $sth->fetchall_arrayref( {} );
 692                 $content = process_tt(
 693                     $notice_template->content,
 694                     {
 695                         data         => $data,
 696                         report_id    => $report_id,
 697                         for_download => 1,
 698                     }
 699                 );
 700                 $reportfilename = process_tt(
 701                     $notice_template->title,
 702                     {
 703                         data      => $data,
 704                         report_id => $report_id,
 705                     }
 706                 );
 707             }
 708         }
 709         print $input->header(
 710             -type => $type,
 711             -attachment=> $reportfilename
 712         );
 713         print $content;
 714
 715         foreach my $err (@$q_errors, @errors) {
 716             print "# ERROR: " . (map {$_ . ": " . $err->{$_}} keys %$err) . "\n";
 717         }   # here we print all the non-fatal errors at the end.  Not super smooth, but better than nothing.
 718         exit;
 719     }
 720     $template->param(
 721         'sql'           => $sql,
 722         'execute'       => 1,
 723         'name'          => 'Error exporting report!',
 724         'notes'         => '',
 725         'errors'        => $q_errors,
 726     );
 727 }
 728
 729 elsif ( $op eq 'add_form_sql' || $op eq 'duplicate' ) {
 730
 731     my ($group, $subgroup, $sql, $reportname, $notes);
 732     if ( $input->param('sql') ) {
 733         $group      = $input->param('report_group');
 734         $subgroup   = $input->param('report_subgroup');
 735         $sql        = $input->param('sql') // '';
 736         $reportname = $input->param('reportname') // '';
 737         $notes      = $input->param('notes') // '';
 738     }
 739     elsif ( my $report_id = $input->param('id') ) {
 740         my $report = Koha::Reports->find($report_id);
 741         $group      = $report->report_group;
 742         $subgroup   = $report->report_subgroup;
 743         $sql        = $report->savedsql // '';
 744         $reportname = $report->report_name // '';
 745         $notes      = $report->notes // '';
 746     }
 747
 748     my $tables = get_tables();
 749
 750     $template->param(
 751         sql        => $sql,
 752         reportname => $reportname,
 753         notes      => $notes,
 754         'create' => 1,
 755         'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
 756         'public' => '0',
 757         'cache_expiry' => 300,
 758         'usecache' => $usecache,
 759         'tables' => $tables,
 760
 761     );
 762 }
 763
 764 if ($op eq 'run'){
 765     # execute a saved report
 766     my $limit           = $input->param('limit') || 20;
 767     my $offset          = 0;
 768     my $report_id       = $input->param('id');
 769     my @sql_params      = $input->multi_param('sql_params');
 770     my @param_names     = $input->multi_param('param_name');
 771     my $template_id     = $input->param('template');
 772     my $want_full_chart = $input->param('want_full_chart') || 0;
 773
 774
 775     # offset algorithm
 776     if ($input->param('page')) {
 777         $offset = ($input->param('page') - 1) * $limit;
 778     }
 779
 780     $template->param(
 781         'limit' => $limit,
 782         'id'    => $report_id,
 783     );
 784
 785     my ( $sql, $original_sql, $type, $name, $notes );
 786     if (my $report = Koha::Reports->find($report_id)) {
 787         $sql   = $original_sql = $report->savedsql;
 788         $name  = $report->report_name;
 789         $notes = $report->notes;
 790
 791         my @rows = ();
 792         my @allrows = ();
 793         # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
 794         if ($sql =~ /<</ && !@sql_params) {
 795             # split on ??. Each odd (2,4,6,...) entry should be a parameter to fill
 796             my @split = split /<<|>>/,$sql;
 797             my @tmpl_parameters;
 798             my @authval_errors;
 799             my %uniq_params;
 800             for(my $i=0;$i<($#split/2);$i++) {
 801                 my ($text,$authorised_value_all) = split /\|/,$split[$i*2+1];
 802                 my $sep = $authorised_value_all ? "|" : "";
 803                 if( defined $uniq_params{$text.$sep.$authorised_value_all} ){
 804                     next;
 805                 } else { $uniq_params{$text.$sep.$authorised_value_all} = "$i"; }
 806                 my ($authorised_value, $param_options) = split /:/, $authorised_value_all;
 807                 my $all;
 808                 my $multiple;
 809                 if ( $param_options eq "all" ) {
 810                     $all = "all";
 811                 } elsif ( $param_options eq "in" ) {
 812                     $multiple = "multiple";
 813                 }
 814                 my $input;
 815                 my $labelid;
 816                 if ( not defined $authorised_value ) {
 817                     # no authorised value input, provide a text box
 818                     $input = "text";
 819                 } elsif ( $authorised_value eq "date" ) {
 820                     # require a date, provide a date picker
 821                     $input = 'date';
 822                 } elsif ( $authorised_value eq "list" ) {
 823                     # require a list, provide a textarea
 824                     $input = 'textarea';
 825                 } else {
 826                     # defined $authorised_value, and not 'date'
 827                     my $dbh=C4::Context->dbh;
 828                     my @authorised_values;
 829                     my %authorised_lib;
 830                     # builds list, depending on authorised value...
 831                     if ( $authorised_value eq "branches" ) {
 832                         my $libraries = Koha::Libraries->search( {}, { order_by => ['branchname'] } );
 833                         while ( my $library = $libraries->next ) {
 834                             push @authorised_values, $library->branchcode;
 835                             $authorised_lib{$library->branchcode} = $library->branchname;
 836                         }
 837                     }
 838                     elsif ( $authorised_value eq "itemtypes" ) {
 839                         my $sth = $dbh->prepare("SELECT itemtype,description FROM itemtypes ORDER BY description");
 840                         $sth->execute;
 841                         while ( my ( $itemtype, $description ) = $sth->fetchrow_array ) {
 842                             push @authorised_values, $itemtype;
 843                             $authorised_lib{$itemtype} = $description;
 844                         }
 845                     }
 846                     elsif ( $authorised_value eq "biblio_framework" ) {
 847                         my @frameworks = Koha::BiblioFrameworks->search({}, { order_by => ['frameworktext'] })->as_list;
 848                         my $default_source = '';
 849                         push @authorised_values,$default_source;
 850                         $authorised_lib{$default_source} = 'Default';
 851                         foreach my $framework (@frameworks) {
 852                             push @authorised_values, $framework->frameworkcode;
 853                             $authorised_lib{$framework->frameworkcode} = $framework->frameworktext;
 854                         }
 855                     }
 856                     elsif ( $authorised_value eq "cn_source" ) {
 857                         my $class_sources = GetClassSources();
 858                         my $default_source = C4::Context->preference("DefaultClassificationSource");
 859                         foreach my $class_source (sort keys %$class_sources) {
 860                             next unless $class_sources->{$class_source}->{'used'} or
 861                                         ($class_source eq $default_source);
 862                             push @authorised_values, $class_source;
 863                             $authorised_lib{$class_source} = $class_sources->{$class_source}->{'description'};
 864                         }
 865                     }
 866                     elsif ( $authorised_value eq "categorycode" ) {
 867                         my @patron_categories = Koha::Patron::Categories->search({}, { order_by => ['description']})->as_list;
 868                         %authorised_lib = map { $_->categorycode => $_->description } @patron_categories;
 869                         push @authorised_values, $_->categorycode for @patron_categories;
 870                     }
 871                     elsif ( $authorised_value eq "cash_registers" ) {
 872                         my $sth = $dbh->prepare("SELECT id, name FROM cash_registers ORDER BY description");
 873                         $sth->execute;
 874                         while ( my ( $id, $name ) = $sth->fetchrow_array ) {
 875                             push @authorised_values, $id;
 876                             $authorised_lib{$id} = $name;
 877                         }
 878                     }
 879                     elsif ( $authorised_value eq "debit_types" ) {
 880                         my $sth = $dbh->prepare("SELECT code, description FROM account_debit_types ORDER BY code");
 881                         $sth->execute;
 882                         while ( my ( $code, $description ) = $sth->fetchrow_array ) {
 883                            push @authorised_values, $code;
 884                            $authorised_lib{$code} = $description;
 885                         }
 886                     }
 887                     elsif ( $authorised_value eq "credit_types" ) {
 888                         my $sth = $dbh->prepare("SELECT code, description FROM account_credit_types ORDER BY code");
 889                         $sth->execute;
 890                         while ( my ( $code, $description ) = $sth->fetchrow_array ) {
 891                            push @authorised_values, $code;
 892                            $authorised_lib{$code} = $description;
 893                         }
 894                     }
 895                     else {
 896                         if ( Koha::AuthorisedValues->search({ category => $authorised_value })->count ) {
 897                             my $query = '
 898                             SELECT authorised_value,lib
 899                             FROM authorised_values
 900                             WHERE category=?
 901                             ORDER BY lib
 902                             ';
 903                             my $authorised_values_sth = $dbh->prepare($query);
 904                             $authorised_values_sth->execute( $authorised_value);
 905
 906                             while ( my ( $value, $lib ) = $authorised_values_sth->fetchrow_array ) {
 907                                 push @authorised_values, $value;
 908                                 $authorised_lib{$value} = $lib;
 909                                 # For item location, we show the code and the libelle
 910                                 $authorised_lib{$value} = $lib;
 911                             }
 912                         } else {
 913                             # not exists $authorised_value_categories{$authorised_value})
 914                             push @authval_errors, {'entry' => $text,
 915                                                    'auth_val' => $authorised_value };
 916                             # tell the template there's an error
 917                             $template->param( auth_val_error => 1 );
 918                             # skip scrolling list creation and params push
 919                             next;
 920                         }
 921                     }
 922                     $labelid = $text;
 923                     $labelid =~ s/\W//g;
 924                     $input = {
 925                         name    => "sql_params",
 926                         id      => "sql_params_".$labelid,
 927                         values  => \@authorised_values,
 928                         labels  => \%authorised_lib,
 929                     };
 930                 }
 931
 932                 push @tmpl_parameters, {'entry' => $text, 'input' => $input, 'labelid' => $labelid, 'name' => $text.$sep.$authorised_value_all, 'include_all' => $all, 'select_multiple' => $multiple };
 933             }
 934             $template->param('sql'         => $sql,
 935                             'name'         => $name,
 936                             'notes'         => $notes,
 937                             'sql_params'   => \@tmpl_parameters,
 938                             'auth_val_errors'  => \@authval_errors,
 939                             'enter_params' => 1,
 940                             'id'           => $report_id,
 941                             );
 942         } else {
 943             my ($sql,$header_types) = $report->prep_report( \@param_names, \@sql_params );
 944             $template->param(header_types => $header_types);
 945             my ( $sth, $errors ) = execute_query(
 946                 {
 947                     sql        => $sql,
 948                     offset     => $offset,
 949                     limit      => $limit,
 950                     report_id  => $report_id,
 951                 }
 952             );
 953             my $total;
 954             if (!$sth) {
 955                 die "execute_query failed to return sth for report $report_id: $sql";
 956             } elsif ( !$errors ) {
 957                 $total = nb_rows($sql) || 0;
 958                 my $headers = header_cell_loop($sth);
 959                 $template->param(header_row => $headers);
 960                 while (my $row = $sth->fetchrow_arrayref()) {
 961                     my @cells = map { +{ cell => $_ } } @$row;
 962                     push @rows, { cells => \@cells };
 963                 }
 964                 if( $want_full_chart ){
 965                     my ( $sth2, $errors2 ) = execute_query( { sql => $sql, report_id => $report_id } );
 966                     while (my $row = $sth2->fetchrow_arrayref()) {
 967                         my @cells = map { +{ cell => $_ } } @$row;
 968                         push @allrows, { cells => \@cells };
 969                     }
 970                 }
 971
 972                 my $totpages = int($total/$limit) + (($total % $limit) > 0 ? 1 : 0);
 973                 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";
 974                 if (@param_names) {
 975                     $url = join('&amp;param_name=', $url, map { URI::Escape::uri_escape_utf8($_) } @param_names);
 976                 }
 977                 if (@sql_params) {
 978                     $url = join('&amp;sql_params=', $url, map { URI::Escape::uri_escape_utf8($_) } @sql_params);
 979                 }
 980
 981                 if ($template_id) {
 982                     my $notice_template = Koha::Notice::Templates->find($template_id);
 983                     my ( $sth2, $errors2 ) = execute_query( { sql => $sql, report_id => $report_id } );
 984                     my $data = $sth2->fetchall_arrayref( {} );
 985                     my $notice_rendered =
 986                         process_tt( $notice_template->content, { data => $data, report_id => $report_id } );
 987                     my $title_rendered =
 988                         process_tt( $notice_template->title, { data => $data, report_id => $report_id } );
 989                     $template->param(
 990                         template_id            => $template_id,
 991                         processed_notice       => $notice_rendered,
 992                         processed_notice_title => $title_rendered,
 993                     );
 994                 }
 995
 996                 $template->param(
 997                     'results'        => \@rows,
 998                     'allresults'     => \@allrows,
 999                     'pagination_bar' => pagination_bar($url, $totpages, scalar $input->param('page')),
1000                     'unlimited_total' => $total,
1001                 );
1002             }
1003             $template->param(
1004                 'sql'         => $sql,
1005                 original_sql  => $original_sql,
1006                 'id'          => $report_id,
1007                 'execute'     => 1,
1008                 'name'        => $name,
1009                 'notes'       => $notes,
1010                 'errors'      => defined($errors) ? [$errors] : undef,
1011                 'sql_params'  => \@sql_params,
1012                 'param_names' => \@param_names,
1013             );
1014         }
1015     }
1016     else {
1017         push @errors, { no_sql_for_id => $report_id };
1018     }
1019 }
1020
1021 if ( $op eq 'list' || $op eq 'convert') {
1022
1023     if ( $op eq 'convert' ) {
1024         my $report_id = $input->param('id');
1025         my $report    = Koha::Reports->find($report_id);
1026         if ($report) {
1027             my $updated_sql = C4::Reports::Guided::convert_sql( $report->savedsql );
1028             C4::Reports::Guided::update_sql(
1029                 $report_id,
1030                 {
1031                     sql          => $updated_sql,
1032                     name         => $report->report_name,
1033                     group        => $report->report_group,
1034                     subgroup     => $report->report_subgroup,
1035                     notes        => $report->notes,
1036                     public       => $report->public,
1037                     cache_expiry => $report->cache_expiry,
1038                 }
1039             );
1040             $template->param( report_converted => $report->report_name );
1041         }
1042     }
1043
1044     # use a saved report
1045     # get list of reports and display them
1046     my $group = $input->param('group');
1047     my $subgroup = $input->param('subgroup');
1048     $filter->{group} = $group;
1049     $filter->{subgroup} = $subgroup;
1050     my $reports = get_saved_reports($filter);
1051     my $has_obsolete_reports;
1052     for my $report ( @$reports ) {
1053         $report->{results} = C4::Reports::Guided::get_results( $report->{id} );
1054         if ( $report->{savedsql} =~ m|biblioitems| and $report->{savedsql} =~ m|marcxml| ) {
1055             $report->{seems_obsolete} = 1;
1056             $has_obsolete_reports++;
1057         }
1058     }
1059     $template->param(
1060         'manamsg' => $input->param('manamsg') || '',
1061         'saved1'                => 1,
1062         'savedreports'          => $reports,
1063         'usecache'              => $usecache,
1064         'groups_with_subgroups' => groups_with_subgroups( $group, $subgroup ),
1065         filters                 => $filter,
1066         has_obsolete_reports    => $has_obsolete_reports,
1067     );
1068 }
1069 # pass $sth, get back an array of names for the column headers
1070 sub header_cell_values {
1071     my $sth = shift or return ();
1072     return '' unless ($sth->{NAME});
1073     return @{$sth->{NAME}};
1074 }
1075
1076 # pass $sth, get back a TMPL_LOOP-able set of names for the column headers
1077 sub header_cell_loop {
1078     my @headers = map { +{ cell => decode('UTF-8',$_) } } header_cell_values (shift);
1079     return \@headers;
1080 }
1081
1082 #get a list of available tables for auto-complete
1083 sub get_tables {
1084     my $result = {};
1085     my $cache  = Koha::Caches->get_instance();
1086     my $tables = $cache->get_from_cache("Reports-SQL_tables-for-autocomplete");
1087
1088     return $tables
1089       if $tables;
1090
1091     $tables = C4::Reports::Guided->get_all_tables();
1092     for my $table (@{$tables}) {
1093         my $sql = "SHOW COLUMNS FROM $table";
1094         my $rows = C4::Context->dbh->selectall_arrayref($sql, { Slice => {} });
1095         for my $row (@{$rows}) {
1096             push @{$result->{$table}}, $row->{Field};
1097         }
1098     }
1099     $cache->set_in_cache("Reports-SQL_tables-for-autocomplete",$result);
1100     return $result;
1101 }
1102
1103 foreach (1..6) {
1104      $template->{VARS}->{'build' . $_} and last;
1105 }
1106 $template->param(   'referer' => $input->referer(),
1107                 );
1108
1109 output_html_with_http_headers $input, $cookie, $template->output;
1110
1111 sub groups_with_subgroups {
1112     my ($group, $subgroup) = @_;
1113
1114     my $groups_with_subgroups = get_report_groups();
1115     my @g_sg;
1116     my @sorted_keys = sort {
1117         $groups_with_subgroups->{$a}->{name} cmp $groups_with_subgroups->{$b}->{name}
1118     } keys %$groups_with_subgroups;
1119     foreach my $g_id (@sorted_keys) {
1120         my $v = $groups_with_subgroups->{$g_id};
1121         my @subgroups;
1122         if (my $sg = $v->{subgroups}) {
1123             foreach my $sg_id (sort { $sg->{$a} cmp $sg->{$b} } keys %$sg) {
1124                 push @subgroups, {
1125                     id => $sg_id,
1126                     name => $sg->{$sg_id},
1127                     selected => ($group && $g_id eq $group && $subgroup && $sg_id eq $subgroup ),
1128                 };
1129             }
1130         }
1131         push @g_sg, {
1132             id => $g_id,
1133             name => $v->{name},
1134             selected => ($group && $g_id eq $group),
1135             subgroups => \@subgroups,
1136         };
1137     }
1138     return \@g_sg;
1139 }
1140
1141 sub create_non_existing_group_and_subgroup {
1142     my ($input, $group, $subgroup) = @_;
1143     if (defined $group and $group ne '') {
1144         my $report_groups = C4::Reports::Guided::get_report_groups;
1145         if (not exists $report_groups->{$group}) {
1146             my $groupdesc = $input->param('groupdesc') // $group;
1147             Koha::AuthorisedValue->new({
1148                 category => 'REPORT_GROUP',
1149                 authorised_value => $group,
1150                 lib => $groupdesc,
1151             })->store;
1152             my $cache_key = "AuthorisedValues-REPORT_GROUP-0-".C4::Context->userenv->{"branch"};
1153             my $cache  = Koha::Caches->get_instance();
1154             my $result = $cache->clear_from_cache($cache_key);
1155         }
1156         if (defined $subgroup and $subgroup ne '') {
1157             if (not exists $report_groups->{$group}->{subgroups}->{$subgroup}) {
1158                 my $subgroupdesc = $input->param('subgroupdesc') // $subgroup;
1159                 Koha::AuthorisedValue->new({
1160                     category => 'REPORT_SUBGROUP',
1161                     authorised_value => $subgroup,
1162                     lib => $subgroupdesc,
1163                     lib_opac => $group,
1164                 })->store;
1165             my $cache_key = "AuthorisedValues-REPORT_SUBGROUP-0-".C4::Context->userenv->{"branch"};
1166             my $cache  = Koha::Caches->get_instance();
1167             my $result = $cache->clear_from_cache($cache_key);
1168             }
1169         }
1170     }
1171 }
1172