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