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

(-)a/C4/Reports/Guided.pm (-1 / +20 lines)
Lines 854-860 sub get_sql { Link Here
854
sub get_results {
854
sub get_results {
855
    my ( $report_id ) = @_;
855
    my ( $report_id ) = @_;
856
    my $dbh = C4::Context->dbh;
856
    my $dbh = C4::Context->dbh;
857
    warn $report_id;
858
    return $dbh->selectall_arrayref(q|
857
    return $dbh->selectall_arrayref(q|
859
        SELECT id, report, date_run
858
        SELECT id, report, date_run
860
        FROM saved_reports
859
        FROM saved_reports
Lines 988-993 sub _get_display_value { Link Here
988
    return $original_value;
987
    return $original_value;
989
}
988
}
990
989
990
991
=head3 convert_sql
992
993
my $updated_sql = C4::Reports::Guided::convert_sql( $sql );
994
995
Convert a sql query using biblioitems.marcxml to use the new
996
biblio_metadata.metadata field instead
997
998
=cut
999
1000
sub convert_sql {
1001
    my ( $sql ) = @_;
1002
    my $updated_sql = $sql;
1003
    if ( $sql =~ m|biblioitems| and $sql =~ m|marcxml| ) {
1004
        $updated_sql =~ s|biblioitems|biblio_metadata|;
1005
        $updated_sql =~ s|marcxml|metadata|;
1006
    }
1007
    return $updated_sql;
1008
}
1009
991
1;
1010
1;
992
__END__
1011
__END__
993
1012
(-)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 (+44 lines)
Lines 98-103 $("#delColumn").on("click",function(){ Link Here
98
        ],
98
        ],
99
        "aoColumns": [
99
        "aoColumns": [
100
            null,null,null,null,null,null,null,null,{ "sType": "title-string" },null,null,null,[% IF (usecache) %]null,[% END %]null,null
100
            null,null,null,null,null,null,null,null,{ "sType": "title-string" },null,null,null,[% IF (usecache) %]null,[% END %]null,null
101
            null,null,null,null,null,null,null,null,{ "sType": "title-string" },null,null,null,[% IF (usecache) %]null,[% END %]null,null[% IF has_obsolete_reports %],null[% END %]
101
        ],
102
        ],
102
        'oLanguage': {
103
        'oLanguage': {
103
            'sZeroRecords': _("No matching reports found")
104
            'sZeroRecords': _("No matching reports found")
Lines 166-171 $("#delColumn").on("click",function(){ Link Here
166
            return false;
167
            return false;
167
        }
168
        }
168
    });
169
    });
170
171
    $("body").on("click", ".update_sql", function(e){
172
        e.preventDefault();
173
        var ltitle = $(this).text();
174
        var report_id = $(this).data("report_id");
175
        var page = $(this).attr("href");
176
        $("#update_sql .modal-body").load(page + " div");
177
        $('#update_sql').modal({show:true});
178
        $("#update_sql_button").attr("href", "/cgi-bin/koha/reports/guided_reports.pl?phase=Use saved&op=convert&report_id=" + report_id);
179
    });
180
    $("#update_sql").on("hidden", function(){
181
        $("#update_sql_label").html("");
182
        $("#update_sql .modal-body").html("<div id=\"loading\"><img src=\"[% interface %]/[% theme %]/img/spinner-small.gif\" alt=\"\" /> "+_("Loading")+"</div>");
183
    });
169
[% END %]
184
[% END %]
170
185
171
[% IF ( showsql ) %]
186
[% IF ( showsql ) %]
Lines 267-272 $("#delColumn").on("click",function(){ Link Here
267
[% END %]
282
[% END %]
268
</div>
283
</div>
269
284
285
<div id="update_sql" class="modal hide" tabindex="-1" role="dialog" aria-labelledby="update_sql_label" aria-hidden="true">
286
    <div class="modal-header">
287
        <button type="button" class="closebtn" data-dismiss="modal" aria-hidden="true">×</button>
288
        <h3 id="update_sql_label">Update SQL</h3>
289
    </div>
290
    <div class="modal-body">
291
        <div id="loading"> <img src="[% interface %]/[% theme %]/img/spinner-small.gif" alt="" /> Loading </div>
292
    </div>
293
    <div class="modal-footer">
294
        <a href="#" class="btn" id="update_sql_button" role="button" data-toggle="modal">Update</a>
295
        <button class="btn" data-dismiss="modal" aria-hidden="true">Close</button>
296
    </div>
297
</div>
298
270
<div id="doc3" class="yui-t1">
299
<div id="doc3" class="yui-t1">
271
<div id="bd">
300
<div id="bd">
272
<div id="yui-main">
301
<div id="yui-main">
Lines 306-311 canned reports and writing custom SQL reports.</p> Link Here
306
</form>
335
</form>
307
[% END %]
336
[% END %]
308
337
338
[% IF report_converted %]
339
    <div class="dialog message">
340
        The report "[% report_converted %]" has been converted.
341
    </div>
342
[% END %]
343
309
[% IF ( saved1 ) %]
344
[% IF ( saved1 ) %]
310
[% IF ( savedreports ) %]<h1>Saved reports</h1>
345
[% IF ( savedreports ) %]<h1>Saved reports</h1>
311
346
Lines 342-347 canned reports and writing custom SQL reports.</p> Link Here
342
                    <th>Public</th>
377
                    <th>Public</th>
343
                    [% IF (usecache) %] <th>Cache expiry (seconds)</th> [% END %]
378
                    [% IF (usecache) %] <th>Cache expiry (seconds)</th> [% END %]
344
                    <th>Saved results</th>
379
                    <th>Saved results</th>
380
                    [% IF has_obsolete_reports %]<th>Update</th>[% END %]
345
                    <th>&nbsp;</th>
381
                    <th>&nbsp;</th>
346
                </tr>
382
                </tr>
347
            </thead>
383
            </thead>
Lines 381-386 canned reports and writing custom SQL reports.</p> Link Here
381
                                <br/>
417
                                <br/>
382
                            [% END %]
418
                            [% END %]
383
                        </td>
419
                        </td>
420
                        [% IF has_obsolete_reports %]
421
                        <td>
422
                            [% IF savedreport.seems_obsolete %]
423
                                This report seems obsolete, it uses biblioitems.marcxml field.
424
                                <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>
425
                            [% END %]
426
                        </td>
427
                        [% END %]
384
                        <td>
428
                        <td>
385
                            <div class="dropup">
429
                            <div class="dropup">
386
                                <div class="btn-group">
430
                                <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 / +56 lines)
Lines 18-24 Link Here
18
18
19
use Modern::Perl;
19
use Modern::Perl;
20
20
21
use Test::More tests => 8;
21
use Test::More tests => 9;
22
use Test::Warn;
22
use Test::Warn;
23
23
24
use t::lib::TestBuilder;
24
use t::lib::TestBuilder;
Lines 288-293 subtest 'Ensure last_run is populated' => sub { Link Here
288
    isnt( $report->last_run, $previous_last_run, 'Second run of report updates last_run' );
288
    isnt( $report->last_run, $previous_last_run, 'Second run of report updates last_run' );
289
};
289
};
290
290
291
subtest 'convert_sql' => sub {
292
    plan tests => 3;
293
294
    my $sql = q|
295
    SELECT biblionumber, ExtractValue(marcxml,
296
'count(//datafield[@tag="505"])') AS count505
297
    FROM biblioitems
298
    HAVING count505 > 1|;
299
    my $expected_converted_sql = q|
300
    SELECT biblionumber, ExtractValue(metadata,
301
'count(//datafield[@tag="505"])') AS count505
302
    FROM biblio_metadata
303
    HAVING count505 > 1|;
304
305
    is( C4::Reports::Guided::convert_sql( $sql ), $expected_converted_sql, "Simple query should have been correctly converted");
306
307
    $sql = q|
308
    SELECT biblionumber, substring(
309
ExtractValue(marcxml,'//controlfield[@tag="008"]'), 8,4 ) AS 'PUB DATE',
310
title
311
    FROM biblioitems
312
    INNER JOIN biblio USING (biblionumber)
313
    WHERE biblionumber = 14|;
314
315
    $expected_converted_sql = q|
316
    SELECT biblionumber, substring(
317
ExtractValue(metadata,'//controlfield[@tag="008"]'), 8,4 ) AS 'PUB DATE',
318
title
319
    FROM biblio_metadata
320
    INNER JOIN biblio USING (biblionumber)
321
    WHERE biblionumber = 14|;
322
    is( C4::Reports::Guided::convert_sql( $sql ), $expected_converted_sql, "Query with biblio info should have been correctly converted");
323
324
    $sql = q|
325
    SELECT concat(b.title, ' ', ExtractValue(m.marcxml,
326
'//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author,
327
count(h.reservedate) AS 'holds'
328
    FROM biblio b
329
    LEFT JOIN biblioitems m USING (biblionumber)
330
    LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber)
331
    GROUP BY b.biblionumber
332
    HAVING count(h.reservedate) >= 42|;
333
334
    $expected_converted_sql = q|
335
    SELECT concat(b.title, ' ', ExtractValue(m.metadata,
336
'//datafield[@tag="245"]/subfield[@code="b"]')) AS title, b.author,
337
count(h.reservedate) AS 'holds'
338
    FROM biblio b
339
    LEFT JOIN biblio_metadata m USING (biblionumber)
340
    LEFT JOIN reserves h ON (b.biblionumber=h.biblionumber)
341
    GROUP BY b.biblionumber
342
    HAVING count(h.reservedate) >= 42|;
343
    is( C4::Reports::Guided::convert_sql( $sql ), $expected_converted_sql, "Query with 2 joins should have been correctly converted");
344
};
345
291
$schema->storage->txn_rollback;
346
$schema->storage->txn_rollback;
292
347
293
sub trim {
348
sub trim {
294
- 

Return to bug 17898