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

(-)a/C4/Reports/Guided.pm (+12 lines)
Lines 557-562 sub execute_query { Link Here
557
        return (undef, { queryerr => 'Missing SELECT'} );
557
        return (undef, { queryerr => 'Missing SELECT'} );
558
    }
558
    }
559
559
560
    foreach my $sql_param ( @$sql_params ){
561
        if ( $sql_param =~ m/\n/ ){
562
            my @list = split /\n/, $sql_param;
563
            my @quoted_list;
564
            foreach my $item ( @list ){
565
                $item =~ s/\r//;
566
              push @quoted_list, C4::Context->dbh->quote($item);
567
            }
568
            $sql_param = "(".join(",",@quoted_list).")";
569
        }
570
    }
571
560
    my ($useroffset, $userlimit);
572
    my ($useroffset, $userlimit);
561
573
562
    # Grab offset/limit from user supplied LIMIT and drop the LIMIT so we can control pagination
574
    # Grab offset/limit from user supplied LIMIT and drop the LIMIT so we can control pagination
(-)a/Koha/Report.pm (+53 lines)
Lines 90-95 sub new_from_mana { Link Here
90
    Koha::Report->new($data)->store;
90
    Koha::Report->new($data)->store;
91
}
91
}
92
92
93
=head3 prep_report
94
95
Prep the report and return executable sql with parameters embedded and a list of header types
96
for building batch action links in the template
97
98
=cut
99
100
sub prep_report {
101
    my ($self, $param_names, $sql_params ) = @_;
102
    my $sql = $self->savedsql;
103
104
    # First we split out the placeholders
105
    # This part of the code supports using [[ table.field | alias ]] in the
106
    # query and replaces it by table.field AS alias. This is used to build
107
    # the batch action links foir cardnumbers, itemnumbers, and biblionumbers in the template
108
    # while allowing the library to alter the column names
109
    my @split = split /\[\[|\]\]/,$sql;
110
    my $headers;
111
    for(my $i=0;$i<$#split/2;$i++){ #The placeholders are always the odd elements of the array
112
        my ($type,$name) = split /\|/,$split[$i*2+1]; # We split them on '|'
113
        $headers->{$name} = $type; # Store as a lookup for the template
114
        $headers->{$name} =~ s/^\w*\.//; # strip the table name just as in $sth->{NAME} array
115
        $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g; #Quote any special characters so we can replace the placeholders
116
        $name = C4::Context->dbh->quote($name);
117
        $sql =~ s/\[\[$split[$i*2+1]\]\]/$type AS $name/; # Remove placeholders from SQL
118
    }
119
120
    my %lookup;
121
    @lookup{@$param_names} = @$sql_params;
122
    @split = split /<<|>>/,$sql;
123
    for(my $i=0;$i<$#split/2;$i++) {
124
        my $quoted = @$param_names ? $lookup{ $split[$i*2+1] } : @$sql_params[$i];
125
        # if there are special regexp chars, we must \ them
126
        $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
127
        if ($split[$i*2+1] =~ /\|\s*date\s*$/) {
128
            $quoted = output_pref({ dt => dt_from_string($quoted), dateformat => 'iso', dateonly => 1 }) if $quoted;
129
        }
130
        unless( $split[$i*2+1] =~ /\|\s*list\s*$/ && $quoted ){
131
            $quoted = C4::Context->dbh->quote($quoted);
132
        } else {
133
            my @list = split /\n/, $quoted;
134
            my @quoted_list;
135
            foreach my $item ( @list ){
136
                $item =~ s/\r//;
137
                push @quoted_list, C4::Context->dbh->quote($item);
138
            }
139
            $quoted="(".join(",",@quoted_list).")";
140
        }
141
        $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
142
    }
143
    return $sql, $headers;
144
}
145
93
=head3 _type
146
=head3 _type
94
147
95
Returns name of corresponding DBIC resultset
148
Returns name of corresponding DBIC resultset
(-)a/opac/svc/report (-5 / +5 lines)
Lines 43-48 my $report_rec = $report_recs->next(); Link Here
43
die "Sorry this report is not public\n" unless $report_rec->public;
43
die "Sorry this report is not public\n" unless $report_rec->public;
44
44
45
my @sql_params  = $query->multi_param('sql_params');
45
my @sql_params  = $query->multi_param('sql_params');
46
my @param_names  = $query->multi_param('param_names');
46
47
47
my $cache = Koha::Caches->get_instance();
48
my $cache = Koha::Caches->get_instance();
48
my $cache_active = $cache->is_cache_active;
49
my $cache_active = $cache->is_cache_active;
Lines 51-57 if ($cache_active) { Link Here
51
    $cache_key =
52
    $cache_key =
52
        "opac:report:"
53
        "opac:report:"
53
      . ( $report_name ? "name:$report_name:" : "id:$report_id:" )
54
      . ( $report_name ? "name:$report_name:" : "id:$report_id:" )
54
      . join( '-', @sql_params );
55
      . join( '-', @sql_params )
56
      . join( '_'. @param_names );
55
    $json_text = $cache->get_from_cache($cache_key);
57
    $json_text = $cache->get_from_cache($cache_key);
56
}
58
}
57
59
Lines 60-71 unless ($json_text) { Link Here
60
    my $limit = C4::Context->preference("SvcMaxReportRows") || 10;
62
    my $limit = C4::Context->preference("SvcMaxReportRows") || 10;
61
    my $sql = $report_rec->savedsql;
63
    my $sql = $report_rec->savedsql;
62
64
63
    # convert SQL parameters to placeholders
65
    my ( $sql, undef ) = $report_rec->prep_report( \@param_names, \@sql_params );
64
    $sql =~ s/(<<.*?>>)/\?/g;
65
    $sql =~ s/\[\[(.*?)\|(.*?)\]\]/$1 AS $2/g;
66
66
67
    my ( $sth, $errors ) =
67
    my ( $sth, $errors ) =
68
      execute_query( $sql, $offset, $limit, \@sql_params, $report_id );
68
      execute_query( $sql, $offset, $limit, undef, $report_id );
69
    if ($sth) {
69
    if ($sth) {
70
        my $lines;
70
        my $lines;
71
        if ($report_annotation) {
71
        if ($report_annotation) {
(-)a/reports/guided_reports.pl (-47 / +2 lines)
Lines 829-835 elsif ($phase eq 'Run this report'){ Link Here
829
                            'reports'      => $report_id,
829
                            'reports'      => $report_id,
830
                            );
830
                            );
831
        } else {
831
        } else {
832
            my ($sql,$header_types) = get_prepped_report( $sql, \@param_names, \@sql_params);
832
            my ($sql,$header_types) = $report->prep_report( \@param_names, \@sql_params );
833
            $template->param(header_types => $header_types);
833
            $template->param(header_types => $header_types);
834
            my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
834
            my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
835
            my $total = nb_rows($sql) || 0;
835
            my $total = nb_rows($sql) || 0;
Lines 894-900 elsif ($phase eq 'Export'){ Link Here
894
    my $reportname     = $input->param('reportname');
894
    my $reportname     = $input->param('reportname');
895
    my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ;
895
    my $reportfilename = $reportname ? "$reportname-reportresults.$format" : "reportresults.$format" ;
896
896
897
    ($sql, undef) = get_prepped_report( $sql, \@param_names, \@sql_params );
897
    ($sql, undef) = $report->prep_report( \@param_names, \@sql_params );
898
	my ($sth, $q_errors) = execute_query($sql);
898
	my ($sth, $q_errors) = execute_query($sql);
899
    unless ($q_errors and @$q_errors) {
899
    unless ($q_errors and @$q_errors) {
900
        my ( $type, $content );
900
        my ( $type, $content );
Lines 1090-1137 sub create_non_existing_group_and_subgroup { Link Here
1090
    }
1090
    }
1091
}
1091
}
1092
1092
1093
# pass $sth and sql_params, get back an executable query
1094
sub get_prepped_report {
1095
    my ($sql, $param_names, $sql_params ) = @_;
1096
1097
    # First we split out the placeholders
1098
    # This part of the code supports using [[ table.field | alias ]] in the
1099
    # query and replaces it by table.field AS alias. Not sure why we would
1100
    # need it if we can type the latter (which is simpler)?
1101
    my @split = split /\[\[|\]\]/,$sql;
1102
    my $headers;
1103
    for(my $i=0;$i<$#split/2;$i++){ #The placeholders are always the odd elements of the array
1104
        my ($type,$name) = split /\|/,$split[$i*2+1]; # We split them on '|'
1105
        $headers->{$name} = $type; # Store as a lookup for the template
1106
        $headers->{$name} =~ s/^\w*\.//; # strip the table name just as in $sth->{NAME} array
1107
        $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g; #Quote any special characters so we can replace the placeholders
1108
        $name = C4::Context->dbh->quote($name);
1109
        $sql =~ s/\[\[$split[$i*2+1]\]\]/$type AS $name/; # Remove placeholders from SQL
1110
    }
1111
1112
    my %lookup;
1113
    @lookup{@$param_names} = @$sql_params;
1114
    @split = split /<<|>>/,$sql;
1115
    my @tmpl_parameters;
1116
    for(my $i=0;$i<$#split/2;$i++) {
1117
        my $quoted = @$param_names ? $lookup{ $split[$i*2+1] } : @$sql_params[$i];
1118
        # if there are special regexp chars, we must \ them
1119
        $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
1120
        if ($split[$i*2+1] =~ /\|\s*date\s*$/) {
1121
            $quoted = output_pref({ dt => dt_from_string($quoted), dateformat => 'iso', dateonly => 1 }) if $quoted;
1122
        }
1123
        unless( $split[$i*2+1] =~ /\|\s*list\s*$/ && $quoted ){
1124
            $quoted = C4::Context->dbh->quote($quoted);
1125
        } else {
1126
            my @list = split /\n/, $quoted;
1127
            my @quoted_list;
1128
            foreach my $item ( @list ){
1129
                $item =~ s/\r//;
1130
              push @quoted_list, C4::Context->dbh->quote($item);
1131
            }
1132
            $quoted="(".join(",",@quoted_list).")";
1133
        }
1134
        $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
1135
    }
1136
    return $sql,$headers;
1137
}
(-)a/svc/report (-6 / +5 lines)
Lines 39-44 if (!$report_recs || $report_recs->count == 0 ) { die "There is no such report.\ Link Here
39
my $report_rec = $report_recs->next();
39
my $report_rec = $report_recs->next();
40
40
41
my @sql_params  = $query->multi_param('sql_params');
41
my @sql_params  = $query->multi_param('sql_params');
42
my @param_names  = $query->multi_param('param_names');
42
43
43
my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
44
my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
44
    {
45
    {
Lines 54-73 my $cache_active = $cache->is_cache_active; Link Here
54
my ($cache_key, $json_text);
55
my ($cache_key, $json_text);
55
if ($cache_active) {
56
if ($cache_active) {
56
    $cache_key = "intranet:report:".($report_name ? "report_name:$report_name:" : "id:$report_id:")
57
    $cache_key = "intranet:report:".($report_name ? "report_name:$report_name:" : "id:$report_id:")
57
    . join( '-', @sql_params );
58
    . join( '-', @sql_params )
59
      . join( '_'. @param_names );
58
    $json_text = $cache->get_from_cache($cache_key);
60
    $json_text = $cache->get_from_cache($cache_key);
59
}
61
}
60
62
61
unless ($json_text) {
63
unless ($json_text) {
62
    my $offset = 0;
64
    my $offset = 0;
63
    my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
65
    my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
64
    my $sql = $report_rec->savedsql;
65
66
66
    # convert SQL parameters to placeholders
67
    my ( $sql, undef ) = $report_rec->prep_report( \@param_names, \@sql_params );
67
    $sql =~ s/(<<.*?>>)/\?/g;
68
    $sql =~ s/\[\[(.*?)\|(.*?)\]\]/$1 AS $2/g;
69
68
70
    my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, \@sql_params, $report_id );
69
    my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id );
71
    if ($sth) {
70
    if ($sth) {
72
        my $lines;
71
        my $lines;
73
        if ($report_annotation) {
72
        if ($report_annotation) {
(-)a/t/db_dependent/Koha/Reports.t (-2 / +22 lines)
Lines 17-23 Link Here
17
17
18
use Modern::Perl;
18
use Modern::Perl;
19
19
20
use Test::More tests => 4;
20
use Test::More tests => 5;
21
21
22
use Koha::Report;
22
use Koha::Report;
23
use Koha::Reports;
23
use Koha::Reports;
Lines 48-51 is( $retrieved_report_1->report_name, $new_report_1->report_name, 'Find a report Link Here
48
$retrieved_report_1->delete;
48
$retrieved_report_1->delete;
49
is( Koha::Reports->search->count, $nb_of_reports + 1, 'Delete should have deleted the report' );
49
is( Koha::Reports->search->count, $nb_of_reports + 1, 'Delete should have deleted the report' );
50
50
51
subtest 'prep_report' => sub {
52
    plan tests => 3;
53
54
    my $report = Koha::Report->new({
55
        report_name => 'report_name_for_test_1',
56
        savedsql => 'SELECT * FROM items WHERE itemnumber IN <<Test|list>>',
57
    })->store;
58
59
    my ($sql, undef) = $report->prep_report( ['Test|list'],["1\n12\n\r243"] );
60
    is( $sql, q{SELECT * FROM items WHERE itemnumber IN ('1','12','243')},'Expected sql generated correctly with single param and name');
61
62
    $report->savedsql('SELECT * FROM items WHERE itemnumber IN <<Test|list>> AND <<Another>> AND <<Test|list>>')->store;
63
64
    ($sql, undef) = $report->prep_report( ['Test|list','Another'],["1\n12\n\r243",'the other'] );
65
    is( $sql, q{SELECT * FROM items WHERE itemnumber IN ('1','12','243') AND 'the other' AND ('1','12','243')},'Expected sql generated correctly with multiple params and names');
66
67
    ($sql, undef) = $report->prep_report( [],["1\n12\n\r243",'the other',"42\n32\n22\n12"] );
68
    is( $sql, q{SELECT * FROM items WHERE itemnumber IN ('1','12','243') AND 'the other' AND ('42','32','22','12')},'Expected sql generated correctly with multiple params and no names');
69
70
};
71
51
$schema->storage->txn_rollback;
72
$schema->storage->txn_rollback;
52
- 

Return to bug 27380