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