View | Details | Raw Unified | Return to bug 5920
Collapse All | Expand All

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

Return to bug 5920