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

(-)a/C4/Reports/Guided.pm (-28 / +3 lines)
Lines 39-45 BEGIN { Link Here
39
    @ISA    = qw(Exporter);
39
    @ISA    = qw(Exporter);
40
    @EXPORT = qw(
40
    @EXPORT = qw(
41
      get_report_types get_report_areas get_report_groups get_columns build_query get_criteria
41
      get_report_types get_report_areas get_report_groups get_columns build_query get_criteria
42
      save_report get_saved_reports execute_query get_saved_report
42
      save_report get_saved_reports execute_query
43
      get_column_type get_distinct_values save_dictionary get_from_dictionary
43
      get_column_type get_distinct_values save_dictionary get_from_dictionary
44
      delete_definition delete_report format_results get_sql
44
      delete_definition delete_report format_results get_sql
45
      nb_rows update_sql
45
      nb_rows update_sql
Lines 622-629 sub delete_report { Link Here
622
    my (@ids) = @_;
622
    my (@ids) = @_;
623
    return unless @ids;
623
    return unless @ids;
624
    foreach my $id (@ids) {
624
    foreach my $id (@ids) {
625
        my $data = get_saved_report($id);
625
        my $data = Koha::Reports->find($id);
626
        logaction( "REPORTS", "DELETE", $id, "$data->{'report_name'} | $data->{'savedsql'}  " ) if C4::Context->preference("ReportsLog");
626
        logaction( "REPORTS", "DELETE", $id, $data->report_name." | ".$data->savedsql ) if C4::Context->preference("ReportsLog");
627
    }
627
    }
628
    my $dbh = C4::Context->dbh;
628
    my $dbh = C4::Context->dbh;
629
    my $query = 'DELETE FROM saved_sql WHERE id IN (' . join( ',', ('?') x @ids ) . ')';
629
    my $query = 'DELETE FROM saved_sql WHERE id IN (' . join( ',', ('?') x @ids ) . ')';
Lines 694-724 sub get_saved_reports { Link Here
694
    return $result;
694
    return $result;
695
}
695
}
696
696
697
sub get_saved_report {
698
    my $dbh   = C4::Context->dbh();
699
    my $query;
700
    my $report_arg;
701
    if ($#_ == 0 && ref $_[0] ne 'HASH') {
702
        ($report_arg) = @_;
703
        $query = " SELECT * FROM saved_sql WHERE id = ?";
704
    } elsif (ref $_[0] eq 'HASH') {
705
        my ($selector) = @_;
706
        if ($selector->{name}) {
707
            $query = " SELECT * FROM saved_sql WHERE report_name = ?";
708
            $report_arg = $selector->{name};
709
        } elsif ($selector->{id} || $selector->{id} eq '0') {
710
            $query = " SELECT * FROM saved_sql WHERE id = ?";
711
            $report_arg = $selector->{id};
712
        } else {
713
            return;
714
        }
715
    } else {
716
        return;
717
    }
718
    return $dbh->selectrow_hashref($query, undef, $report_arg);
719
}
720
721
722
=head2 get_column_type($column)
697
=head2 get_column_type($column)
723
698
724
This takes a column name of the format table.column and will return what type it is
699
This takes a column name of the format table.column and will return what type it is
(-)a/misc/cronjobs/runreport.pl (-4 / +5 lines)
Lines 21-26 Link Here
21
use Modern::Perl;
21
use Modern::Perl;
22
22
23
use C4::Reports::Guided; # 0.12
23
use C4::Reports::Guided; # 0.12
24
use Koha::Reports;
24
use C4::Context;
25
use C4::Context;
25
use C4::Log;
26
use C4::Log;
26
use Koha::Email;
27
use Koha::Email;
Lines 231-244 my $today = dt_from_string(); Link Here
231
my $date = $today->ymd();
232
my $date = $today->ymd();
232
233
233
foreach my $report_id (@ARGV) {
234
foreach my $report_id (@ARGV) {
234
    my $report = get_saved_report($report_id);
235
    my $report = Koha::Reports->find( $report_id );
235
    unless ($report) {
236
    unless ($report) {
236
        warn "ERROR: No saved report $report_id found";
237
        warn "ERROR: No saved report $report_id found";
237
        next;
238
        next;
238
    }
239
    }
239
    my $sql         = $report->{savedsql};
240
    my $sql         = $report->savedsql;
240
    my $report_name = $report->{report_name};
241
    my $report_name = $report->report_name;
241
    my $type        = $report->{type};
242
    my $type        = $report->type;
242
243
243
    $verbose and print "SQL: $sql\n\n";
244
    $verbose and print "SQL: $sql\n\n";
244
    if ( $subject eq "" )
245
    if ( $subject eq "" )
(-)a/opac/svc/report (-4 / +5 lines)
Lines 24-29 Link Here
24
use Modern::Perl;
24
use Modern::Perl;
25
25
26
use C4::Reports::Guided;
26
use C4::Reports::Guided;
27
use Koha::Reports;
27
use JSON;
28
use JSON;
28
use CGI qw ( -utf8 );
29
use CGI qw ( -utf8 );
29
30
Lines 34-43 my $report_id = $query->param('id'); Link Here
34
my $report_name = $query->param('name');
35
my $report_name = $query->param('name');
35
my $report_annotation = $query->param('annotated');
36
my $report_annotation = $query->param('annotated');
36
37
37
my $report_rec = get_saved_report( $report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
38
my $report_rec = Koha::Reports->find( $report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
38
if (!$report_rec) { die "There is no such report.\n"; }
39
if (!$report_rec) { die "There is no such report.\n"; }
39
40
40
die "Sorry this report is not public\n" unless $report_rec->{public};
41
die "Sorry this report is not public\n" unless $report_rec->public;
41
42
42
my @sql_params  = $query->param('sql_params');
43
my @sql_params  = $query->param('sql_params');
43
44
Lines 55-61 if ($cache_active) { Link Here
55
unless ($json_text) {
56
unless ($json_text) {
56
    my $offset = 0;
57
    my $offset = 0;
57
    my $limit = C4::Context->preference("SvcMaxReportRows") || 10;
58
    my $limit = C4::Context->preference("SvcMaxReportRows") || 10;
58
    my $sql = $report_rec->{savedsql};
59
    my $sql = $report_rec->savedsql;
59
60
60
    # convert SQL parameters to placeholders
61
    # convert SQL parameters to placeholders
61
    $sql =~ s/(<<.*?>>)/\?/g;
62
    $sql =~ s/(<<.*?>>)/\?/g;
Lines 74-80 unless ($json_text) { Link Here
74
75
75
        if ($cache_active) {
76
        if ($cache_active) {
76
            $cache->set_in_cache( $cache_key, $json_text,
77
            $cache->set_in_cache( $cache_key, $json_text,
77
                { expiry => $report_rec->{cache_expiry} } );
78
                { expiry => $report_rec->cache_expiry } );
78
        }
79
        }
79
    }
80
    }
80
    else {
81
    else {
(-)a/reports/guided_reports.pl (-28 / +29 lines)
Lines 25-30 use URI::Escape; Link Here
25
use File::Temp;
25
use File::Temp;
26
use File::Basename qw( dirname );
26
use File::Basename qw( dirname );
27
use C4::Reports::Guided;
27
use C4::Reports::Guided;
28
use Koha::Reports;
28
use C4::Auth qw/:DEFAULT get_session/;
29
use C4::Auth qw/:DEFAULT get_session/;
29
use C4::Output;
30
use C4::Output;
30
use C4::Debug;
31
use C4::Debug;
Lines 109-130 elsif ( $phase eq 'Build new' ) { Link Here
109
110
110
    if ( $op eq 'convert' ) {
111
    if ( $op eq 'convert' ) {
111
        my $report_id = $input->param('report_id');
112
        my $report_id = $input->param('report_id');
112
        my $report    = C4::Reports::Guided::get_saved_report($report_id);
113
        my $report    = Koha::Reports->find($report_id);
113
        if ($report) {
114
        if ($report) {
114
            my $updated_sql = C4::Reports::Guided::convert_sql( $report->{savedsql} );
115
            my $updated_sql = C4::Reports::Guided::convert_sql( $report->savedsql );
115
            C4::Reports::Guided::update_sql(
116
            C4::Reports::Guided::update_sql(
116
                $report_id,
117
                $report_id,
117
                {
118
                {
118
                    sql          => $updated_sql,
119
                    sql          => $updated_sql,
119
                    name         => $report->{report_name},
120
                    name         => $report->report_name,
120
                    group        => $report->{report_group},
121
                    group        => $report->report_group,
121
                    subgroup     => $report->{report_subgroup},
122
                    subgroup     => $report->report_subgroup,
122
                    notes        => $report->{notes},
123
                    notes        => $report->notes,
123
                    public       => $report->{public},
124
                    public       => $report->public,
124
                    cache_expiry => $report->{cache_expiry},
125
                    cache_expiry => $report->cache_expiry,
125
                }
126
                }
126
            );
127
            );
127
            $template->param( report_converted => $report->{report_name} );
128
            $template->param( report_converted => $report->report_name );
128
        }
129
        }
129
    }
130
    }
130
131
Lines 172-200 elsif ( $phase eq 'Delete Saved') { Link Here
172
elsif ( $phase eq 'Show SQL'){
173
elsif ( $phase eq 'Show SQL'){
173
	
174
	
174
    my $id = $input->param('reports');
175
    my $id = $input->param('reports');
175
    my $report = get_saved_report($id);
176
    my $report = Koha::Reports->find($id);
176
    $template->param(
177
    $template->param(
177
        'id'      => $id,
178
        'id'      => $id,
178
        'reportname' => $report->{report_name},
179
        'reportname' => $report->report_name,
179
        'notes'      => $report->{notes},
180
        'notes'      => $report->notes,
180
	'sql'     => $report->{savedsql},
181
        'sql'     => $report->savedsql,
181
	'showsql' => 1,
182
        'showsql' => 1,
182
    );
183
    );
183
}
184
}
184
185
185
elsif ( $phase eq 'Edit SQL'){
186
elsif ( $phase eq 'Edit SQL'){
186
    my $id = $input->param('reports');
187
    my $id = $input->param('reports');
187
    my $report = get_saved_report($id);
188
    my $report = Koha::Reports->find($id);
188
    my $group = $report->{report_group};
189
    my $group = $report->report_group;
189
    my $subgroup  = $report->{report_subgroup};
190
    my $subgroup  = $report->report_subgroup;
190
    $template->param(
191
    $template->param(
191
        'sql'        => $report->{savedsql},
192
        'sql'        => $report->savedsql,
192
        'reportname' => $report->{report_name},
193
        'reportname' => $report->report_name,
193
        'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
194
        'groups_with_subgroups' => groups_with_subgroups($group, $subgroup),
194
        'notes'      => $report->{notes},
195
        'notes'      => $report->notes,
195
        'id'         => $id,
196
        'id'         => $id,
196
        'cache_expiry' => $report->{cache_expiry},
197
        'cache_expiry' => $report->cache_expiry,
197
        'public' => $report->{public},
198
        'public' => $report->public,
198
        'usecache' => $usecache,
199
        'usecache' => $usecache,
199
        'editsql'    => 1,
200
        'editsql'    => 1,
200
    );
201
    );
Lines 686-695 elsif ($phase eq 'Run this report'){ Link Here
686
    );
687
    );
687
688
688
    my ( $sql, $original_sql, $type, $name, $notes );
689
    my ( $sql, $original_sql, $type, $name, $notes );
689
    if (my $report = get_saved_report($report_id)) {
690
    if (my $report = Koha::Reports->find($report_id)) {
690
        $sql   = $original_sql = $report->{savedsql};
691
        $sql   = $original_sql = $report->savedsql;
691
        $name  = $report->{report_name};
692
        $name  = $report->report_name;
692
        $notes = $report->{notes};
693
        $notes = $report->notes;
693
694
694
        my @rows = ();
695
        my @rows = ();
695
        # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
696
        # if we have at least 1 parameter, and it's not filled, then don't execute but ask for parameters
Lines 850-857 elsif ($phase eq 'Export'){ Link Here
850
851
851
	# export results to tab separated text or CSV
852
	# export results to tab separated text or CSV
852
    my $report_id      = $input->param('report_id');
853
    my $report_id      = $input->param('report_id');
853
    my $report         = get_saved_report($report_id);
854
    my $report         = Koha::Reports->find($report_id);
854
    my $sql            = $report->{savedsql};
855
    my $sql            = $report->savedsql;
855
    my @param_names    = $input->multi_param('param_name');
856
    my @param_names    = $input->multi_param('param_name');
856
    my @sql_params     = $input->multi_param('sql_params');
857
    my @sql_params     = $input->multi_param('sql_params');
857
    my $format         = $input->param('format');
858
    my $format         = $input->param('format');
(-)a/svc/convert_report (-2 / +3 lines)
Lines 21-26 use Modern::Perl; Link Here
21
21
22
use C4::Auth;
22
use C4::Auth;
23
use C4::Reports::Guided;
23
use C4::Reports::Guided;
24
use Koha::Reports;
24
use C4::Output;
25
use C4::Output;
25
use CGI qw ( -utf8 );
26
use CGI qw ( -utf8 );
26
27
Lines 37-47 my ( $template, $loggedinuser, $cookie ) = get_template_and_user( Link Here
37
    }
38
    }
38
);
39
);
39
40
40
my $report = get_saved_report( $report_id );
41
my $report = Koha::Reports->find( $report_id );
41
42
42
my $params;
43
my $params;
43
if ( $report ) {
44
if ( $report ) {
44
    my $sql = $report->{savedsql};
45
    my $sql = $report->savedsql;
45
    my $updated_sql = C4::Reports::Guided::convert_sql( $sql );
46
    my $updated_sql = C4::Reports::Guided::convert_sql( $sql );
46
    $params = { msg => 'can_be_updated', updated_sql => $updated_sql, current_sql => $sql };
47
    $params = { msg => 'can_be_updated', updated_sql => $updated_sql, current_sql => $sql };
47
} else {
48
} else {
(-)a/svc/report (-3 / +4 lines)
Lines 22-27 use Modern::Perl; Link Here
22
22
23
use C4::Auth;
23
use C4::Auth;
24
use C4::Reports::Guided;
24
use C4::Reports::Guided;
25
use Koha::Reports;
25
use JSON;
26
use JSON;
26
use CGI qw ( -utf8 );
27
use CGI qw ( -utf8 );
27
28
Lines 33-39 my $report_id = $query->param('id'); Link Here
33
my $report_name = $query->param('name');
34
my $report_name = $query->param('name');
34
my $report_annotation = $query->param('annotated');
35
my $report_annotation = $query->param('annotated');
35
36
36
my $report_rec = get_saved_report( $report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
37
my $report_rec = Koha::Reports->find( $report_name ? { 'name' => $report_name } : { 'id' => $report_id } );
37
if (!$report_rec) { die "There is no such report.\n"; }
38
if (!$report_rec) { die "There is no such report.\n"; }
38
39
39
my @sql_params  = $query->param('sql_params');
40
my @sql_params  = $query->param('sql_params');
Lines 60-66 if ($cache_active) { Link Here
60
unless ($json_text) {
61
unless ($json_text) {
61
    my $offset = 0;
62
    my $offset = 0;
62
    my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
63
    my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
63
    my $sql = $report_rec->{savedsql};
64
    my $sql = $report_rec->savedsql;
64
65
65
    # convert SQL parameters to placeholders
66
    # convert SQL parameters to placeholders
66
    $sql =~ s/(<<.*?>>)/\?/g;
67
    $sql =~ s/(<<.*?>>)/\?/g;
Lines 77-83 unless ($json_text) { Link Here
77
        $json_text = encode_json($lines);
78
        $json_text = encode_json($lines);
78
79
79
        if ($cache_active) {
80
        if ($cache_active) {
80
            $cache->set_in_cache( $cache_key, $json_text, { expiry => $report_rec->{cache_expiry} } );
81
            $cache->set_in_cache( $cache_key, $json_text, { expiry => $report_rec->cache_expiry } );
81
        }
82
        }
82
    }
83
    }
83
    else {
84
    else {
(-)a/t/db_dependent/Reports/Guided.t (-1 / +1 lines)
Lines 24-29 use Test::Warn; Link Here
24
use t::lib::TestBuilder;
24
use t::lib::TestBuilder;
25
use C4::Context;
25
use C4::Context;
26
use Koha::Database;
26
use Koha::Database;
27
use Koha::Reports;
27
28
28
use_ok('C4::Reports::Guided');
29
use_ok('C4::Reports::Guided');
29
can_ok(
30
can_ok(
30
- 

Return to bug 20495