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

(-)a/C4/Reports/Guided.pm (-1 / +20 lines)
Lines 849-855 sub get_sql { Link Here
849
sub get_results {
849
sub get_results {
850
    my ( $report_id ) = @_;
850
    my ( $report_id ) = @_;
851
    my $dbh = C4::Context->dbh;
851
    my $dbh = C4::Context->dbh;
852
    warn $report_id;
853
    return $dbh->selectall_arrayref(q|
852
    return $dbh->selectall_arrayref(q|
854
        SELECT id, report, date_run
853
        SELECT id, report, date_run
855
        FROM saved_reports
854
        FROM saved_reports
Lines 983-988 sub _get_display_value { Link Here
983
    return $original_value;
982
    return $original_value;
984
}
983
}
985
984
985
986
=head3 convert_sql
987
988
my $updated_sql = C4::Reports::Guided::convert_sql( $sql );
989
990
Convert a sql query using biblioitems.marcxml to use the new
991
biblio_metadata.metadata field instead
992
993
=cut
994
995
sub convert_sql {
996
    my ( $sql ) = @_;
997
    my $updated_sql = $sql;
998
    if ( $sql =~ m|biblioitems| and $sql =~ m|marcxml| ) {
999
        $updated_sql =~ s|biblioitems|biblio_metadata|;
1000
        $updated_sql =~ s|marcxml|metadata|;
1001
    }
1002
    return $updated_sql;
1003
}
1004
986
1;
1005
1;
987
__END__
1006
__END__
988
1007
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/convert_report.tt (+19 lines)
Line 0 Link Here
1
[% INCLUDE 'doc-head-open.inc' %]
2
<title>Koha &rsaquo; Reports &rsaquo; Convert report</title>
3
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
4
    </head>
5
    <body id="report_convert" class="catalog">
6
        <div id="main" style="direction: ltr;">
7
            [% IF msg == 'no_report' %]
8
                There is no valid report for this id.
9
            [% ELSIF msg == 'can_be_updated' %]
10
                <h3>Existing sql</h3>
11
                <pre>[% current_sql %]</pre>
12
                <h3>Updated sql</h3>
13
                <pre>[% updated_sql %]</pre>
14
            [% ELSE %]
15
                Something went wrong.
16
            [% END %]
17
        </div>
18
    </body>
19
</html>
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt (-1 / +44 lines)
Lines 95-101 $("#delColumn").on("click",function(){ Link Here
95
            { "aTargets": [ 1, 2 ], "sType": "natural"  }
95
            { "aTargets": [ 1, 2 ], "sType": "natural"  }
96
        ],
96
        ],
97
        "aoColumns": [
97
        "aoColumns": [
98
            null,null,null,null,null,null,null,null,{ "sType": "title-string" },null,[% IF (usecache) %]null,[% END %]null,null
98
            null,null,null,null,null,null,null,null,{ "sType": "title-string" },null,[% IF (usecache) %]null,[% END %]null,null[% IF has_obsolete_reports %],null[% END %]
99
        ],
99
        ],
100
        'oLanguage': {
100
        'oLanguage': {
101
            'sZeroRecords': _("No matching reports found")
101
            'sZeroRecords': _("No matching reports found")
Lines 164-169 $("#delColumn").on("click",function(){ Link Here
164
            return false;
164
            return false;
165
        }
165
        }
166
    });
166
    });
167
168
    $("body").on("click", ".update_sql", function(e){
169
        e.preventDefault();
170
        var ltitle = $(this).text();
171
        var report_id = $(this).data("report_id");
172
        var page = $(this).attr("href");
173
        $("#update_sql .modal-body").load(page + " div");
174
        $('#update_sql').modal({show:true});
175
        $("#update_sql_button").attr("href", "/cgi-bin/koha/reports/guided_reports.pl?phase=Use saved&op=convert&report_id=" + report_id);
176
    });
177
    $("#update_sql").on("hidden", function(){
178
        $("#update_sql_label").html("");
179
        $("#update_sql .modal-body").html("<div id=\"loading\"><img src=\"[% interface %]/[% theme %]/img/spinner-small.gif\" alt=\"\" /> "+_("Loading")+"</div>");
180
    });
167
[% END %]
181
[% END %]
168
182
169
[% IF ( showsql ) %]
183
[% IF ( showsql ) %]
Lines 259-264 $("#delColumn").on("click",function(){ Link Here
259
[% END %]
273
[% END %]
260
</div>
274
</div>
261
275
276
<div id="update_sql" class="modal hide" tabindex="-1" role="dialog" aria-labelledby="update_sql_label" aria-hidden="true">
277
    <div class="modal-header">
278
        <button type="button" class="closebtn" data-dismiss="modal" aria-hidden="true">×</button>
279
        <h3 id="update_sql_label">Update SQL</h3>
280
    </div>
281
    <div class="modal-body">
282
        <div id="loading"> <img src="[% interface %]/[% theme %]/img/spinner-small.gif" alt="" /> Loading </div>
283
    </div>
284
    <div class="modal-footer">
285
        <a href="#" class="btn" id="update_sql_button" role="button" data-toggle="modal">Update</a>
286
        <button class="btn" data-dismiss="modal" aria-hidden="true">Close</button>
287
    </div>
288
</div>
289
262
<div id="doc3" class="yui-t1">
290
<div id="doc3" class="yui-t1">
263
<div id="bd">
291
<div id="bd">
264
<div id="yui-main">
292
<div id="yui-main">
Lines 298-303 canned reports and writing custom SQL reports.</p> Link Here
298
</form>
326
</form>
299
[% END %]
327
[% END %]
300
328
329
[% IF report_converted %]
330
    <div class="dialog message">
331
        The report "[% report_converted %]" has been converted.
332
    </div>
333
[% END %]
334
301
[% IF ( saved1 ) %]
335
[% IF ( saved1 ) %]
302
[% IF ( savedreports ) %]<h1>Saved reports</h1>
336
[% IF ( savedreports ) %]<h1>Saved reports</h1>
303
337
Lines 332-337 canned reports and writing custom SQL reports.</p> Link Here
332
                    <th>Public</th>
366
                    <th>Public</th>
333
                    [% IF (usecache) %] <th>Cache expiry (seconds)</th> [% END %]
367
                    [% IF (usecache) %] <th>Cache expiry (seconds)</th> [% END %]
334
                    <th>Saved results</th>
368
                    <th>Saved results</th>
369
                    [% IF has_obsolete_reports %]<th>Update</th>[% END %]
335
                    <th>&nbsp;</th>
370
                    <th>&nbsp;</th>
336
                </tr>
371
                </tr>
337
            </thead>
372
            </thead>
Lines 369-374 canned reports and writing custom SQL reports.</p> Link Here
369
                                <br/>
404
                                <br/>
370
                            [% END %]
405
                            [% END %]
371
                        </td>
406
                        </td>
407
                        [% IF has_obsolete_reports %]
408
                        <td>
409
                            [% IF savedreport.seems_obsolete %]
410
                                This report seems obsolete, it uses biblioitems.marcxml field.
411
                                <a href="/cgi-bin/koha/svc/convert_report?report_id=[% savedreport.id %]" data-report_id="[% savedreport.id %]" class="update_sql btn btn-mini" title="Update SQL"><i class="fa fa-eye"></i> Update SQL</a>
412
                            [% END %]
413
                        </td>
414
                        [% END %]
372
                        <td>
415
                        <td>
373
                            <div class="dropup">
416
                            <div class="dropup">
374
                                <div class="btn-group">
417
                                <div class="btn-group">
(-)a/reports/guided_reports.pl (+30 lines)
Lines 89-94 elsif ($session and not $input->param('clear_filters')) { Link Here
89
    $filter = $session->param('report_filter');
89
    $filter = $session->param('report_filter');
90
}
90
}
91
91
92
my $op = $input->param('op') || q||;
92
93
93
my @errors = ();
94
my @errors = ();
94
if ( !$phase ) {
95
if ( !$phase ) {
Lines 106-111 elsif ( $phase eq 'Build new' ) { Link Here
106
    );
107
    );
107
} elsif ( $phase eq 'Use saved' ) {
108
} elsif ( $phase eq 'Use saved' ) {
108
109
110
    if ( $op eq 'convert' ) {
111
        my $report_id = $input->param('report_id');
112
        my $report    = C4::Reports::Guided::get_saved_report($report_id);
113
        warn $report_id;
114
        use Data::Printer colored => 1; warn p $report;
115
        if ($report) {
116
            my $updated_sql = C4::Reports::Guided::convert_sql( $report->{savedsql} );
117
            C4::Reports::Guided::update_sql(
118
                $report_id,
119
                {
120
                    sql          => $updated_sql,
121
                    name         => $report->{report_name},
122
                    group        => $report->{report_group},
123
                    subgroup     => $report->{report_subgroup},
124
                    notes        => $report->{notes},
125
                    public       => $report->{public},
126
                    cache_expiry => $report->{cache_expiry},
127
                }
128
            );
129
            $template->param( report_converted => $report->{report_name} );
130
        }
131
    }
132
109
    # use a saved report
133
    # use a saved report
110
    # get list of reports and display them
134
    # get list of reports and display them
111
    my $group = $input->param('group');
135
    my $group = $input->param('group');
Lines 113-120 elsif ( $phase eq 'Build new' ) { Link Here
113
    $filter->{group} = $group;
137
    $filter->{group} = $group;
114
    $filter->{subgroup} = $subgroup;
138
    $filter->{subgroup} = $subgroup;
115
    my $reports = get_saved_reports($filter);
139
    my $reports = get_saved_reports($filter);
140
    my $has_obsolete_reports;
116
    for my $report ( @$reports ) {
141
    for my $report ( @$reports ) {
117
        $report->{results} = C4::Reports::Guided::get_results( $report->{id} );
142
        $report->{results} = C4::Reports::Guided::get_results( $report->{id} );
143
        if ( $report->{savedsql} =~ m|marcxml| ) {
144
            $report->{seems_obsolete} = 1;
145
            $has_obsolete_reports++;
146
        }
118
    }
147
    }
119
    $template->param(
148
    $template->param(
120
        'saved1' => 1,
149
        'saved1' => 1,
Lines 122-127 elsif ( $phase eq 'Build new' ) { Link Here
122
        'usecache' => $usecache,
151
        'usecache' => $usecache,
123
        'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup),
152
        'groups_with_subgroups'=> groups_with_subgroups($group, $subgroup),
124
        filters => $filter,
153
        filters => $filter,
154
        has_obsolete_reports => $has_obsolete_reports,
125
    );
155
    );
126
}
156
}
127
157
(-)a/svc/convert_report (+53 lines)
Line 0 Link Here
1
#!/usr/bin/perl
2
3
# This file is part of Koha.
4
#
5
# Copyright 2017 Koha Development Team
6
#
7
# Koha is free software; you can redistribute it and/or modify it
8
# under the terms of the GNU General Public License as published by
9
# the Free Software Foundation; either version 3 of the License, or
10
# (at your option) any later version.
11
#
12
# Koha is distributed in the hope that it will be useful, but
13
# WITHOUT ANY WARRANTY; without even the implied warranty of
14
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15
# GNU General Public License for more details.
16
#
17
# You should have received a copy of the GNU General Public License
18
# along with Koha; if not, see <http://www.gnu.org/licenses>.
19
20
use Modern::Perl;
21
22
use C4::Auth;
23
use C4::Reports::Guided;
24
use C4::Output;
25
use CGI qw ( -utf8 );
26
27
my $query  = CGI->new();
28
my $report_id = $query->param('report_id');
29
30
my ( $template, $loggedinuser, $cookie ) = get_template_and_user(
31
    {
32
        template_name   => "reports/convert_report.tt",
33
        query           => $query,
34
        type            => "intranet",
35
        authnotrequired => 0,
36
        flagsrequired   => { reports => 'execute_reports' },
37
    }
38
);
39
40
my $report = get_saved_report( $report_id );
41
42
my $params;
43
if ( $report ) {
44
    my $sql = $report->{savedsql};
45
    my $updated_sql = C4::Reports::Guided::convert_sql( $sql );
46
    $params = { msg => 'can_be_updated', updated_sql => $updated_sql, current_sql => $sql };
47
} else {
48
    $params = { msg => 'no_report' };
49
}
50
51
$template->param( %$params );
52
53
output_html_with_http_headers $query, $cookie, $template->output;
(-)a/t/db_dependent/Reports/Guided.t (-2 / +57 lines)
Lines 18-24 Link Here
18
18
19
use Modern::Perl;
19
use Modern::Perl;
20
20
21
use Test::More tests => 7;
21
use Test::More tests => 8;
22
use Test::Warn;
22
use Test::Warn;
23
23
24
use t::lib::TestBuilder;
24
use t::lib::TestBuilder;
Lines 260-265 subtest 'get_saved_reports' => sub { Link Here
260
        "get_report_areas returns the correct array of report areas");
260
        "get_report_areas returns the correct array of report areas");
261
};
261
};
262
262
263
264
subtest 'convert_sql' => sub {
265
    plan tests => 3;
266
267
    my $sql = q|
268
    SELECT biblionumber, ExtractValue(marcxml,
269
'count(//datafield[@tag="505"])') AS count505
270
    FROM biblioitems
271
    HAVING count505 > 1|;
272
    my $expected_converted_sql = q|
273
    SELECT biblionumber, ExtractValue(metadata,
274
'count(//datafield[@tag="505"])') AS count505
275
    FROM biblio_metadata
276
    HAVING count505 > 1|;
277
278
    is( C4::Reports::Guided::convert_sql( $sql ), $expected_converted_sql, "Simple query should have been correctly converted");
279
280
    $sql = q|
281
    SELECT biblionumber, substring(
282
ExtractValue(marcxml,'//controlfield[@tag="008"]'), 8,4 ) AS 'PUB DATE',
283
title
284
    FROM biblioitems
285
    INNER JOIN biblio USING (biblionumber)
286
    WHERE biblionumber = 14|;
287
288
    $expected_converted_sql = q|
289
    SELECT biblionumber, substring(
290
ExtractValue(metadata,'//controlfield[@tag="008"]'), 8,4 ) AS 'PUB DATE',
291
title
292
    FROM biblio_metadata
293
    INNER JOIN biblio USING (biblionumber)
294
    WHERE biblionumber = 14|;
295
    is( C4::Reports::Guided::convert_sql( $sql ), $expected_converted_sql, "Query with biblio info should have been correctly converted");
296
297
    $sql = q|
298
    SELECT concat(b.title, ' ', ExtractValue(m.marcxml,
299
'//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author,
300
count(h.reservedate) AS 'holds'
301
    FROM biblio b
302
    LEFT JOIN biblioitems m USING (biblionumber)
303
    LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber)
304
    GROUP BY b.biblionumber
305
    HAVING count(h.reservedate) >= 42|;
306
307
    $expected_converted_sql = q|
308
    SELECT concat(b.title, ' ', ExtractValue(m.metadata,
309
'//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author,
310
count(h.reservedate) AS 'holds'
311
    FROM biblio b
312
    LEFT JOIN biblio_metadata m USING (biblionumber)
313
    LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber)
314
    GROUP BY b.biblionumber
315
    HAVING count(h.reservedate) >= 42|;
316
    is( C4::Reports::Guided::convert_sql( $sql ), $expected_converted_sql, "Query with 2 joins should have been correctly converted");
317
};
318
263
$schema->storage->txn_rollback;
319
$schema->storage->txn_rollback;
264
320
265
sub trim {
321
sub trim {
266
- 

Return to bug 17898