From 6247ecd0661bc3ba9008867c74f3a61fa053e2cd Mon Sep 17 00:00:00 2001 From: Nick Clemens Date: Sat, 27 Jul 2019 13:07:18 +0000 Subject: [PATCH] Bug 23390: Introduce placeholder syntax for report column names This patch adds the ability to rename columns in MySQL reports using a syntax: [[column|alias]] To test: 1 - Write a report like: SELECT cardnumber FROM borrowers WHERE cardnumber LIKE '%9' 2 - Run report 3 - Note you can batch modify the patrons 4 - Update report: SELECT cardnumber AS library_barcode FROM borrowers WHERE cardnumber LIKE '%9' 5 - Run report 6 - Note batch options are no longer present 7 - Apply patch 8 - Run report, no change 9 - Update report like: SELECT [[cardnumber|library_barcode]] FROM borrowers WHERE cardnumber LIKE '%9' 10 - Run report 11 - Batch options work! 12 - Update report like: SELECT cardnumber FROM borrowers WHERE cardnumber LIKE '%9' 13 - Verify batch options still work 14 - Set report 'public' 15 - Verify report can be run via JSON URLs: http://kohadev.mydnsname.org:8080/cgi-bin/koha/svc/report?id=1 http://kohadev.mydnsname.org:8081/cgi-bin/koha/svc/report?id=1 Signed-off-by: George Williams Signed-off-by: George Williams --- .../prog/en/modules/reports/guided_reports_start.tt | 18 +++++++++--------- opac/svc/report | 1 + reports/guided_reports.pl | 16 ++++++++++++++-- svc/report | 1 + 4 files changed, 25 insertions(+), 11 deletions(-) diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt index 45f3789c20..f41d21bef6 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt @@ -737,7 +737,7 @@ canned reports and writing custom SQL reports.

[% # Preserve the whitespace of the following textarea in order to format the values correctly %] - @@ -745,7 +745,7 @@ canned reports and writing custom SQL reports.

[% # Preserve the whitespace of the following textarea in order to format the values correctly %] - @@ -754,7 +754,7 @@ canned reports and writing custom SQL reports.

[% FOREACH result IN results %] [% FOREACH cells IN result.cells %] [% place = loop.index %] - [% IF header_row.$place.cell == 'itemnumber' %] + [% IF header_row.$place.cell == 'itemnumber' || header_types.item(header_row.$place.cell) == 'itemnumber' %] [% SET batch_itemnumbers = 1 %] [% END %] @@ -768,7 +768,7 @@ canned reports and writing custom SQL reports.

[% FOREACH result IN results %] [% FOREACH cells IN result.cells %] [% place = loop.index %] - [% IF header_row.$place.cell == 'itemnumber' %] + [% IF header_row.$place.cell == 'itemnumber' || header_types.item(header_row.$place.cell) == 'itemnumber' %] [% SET batch_itemnumbers = 1 %] [% END %] @@ -780,7 +780,7 @@ canned reports and writing custom SQL reports.

[% FOREACH result IN results %] [% FOREACH cells IN result.cells %] [% place = loop.index %] - [% IF header_row.$place.cell == 'biblionumber' %] + [% IF header_row.$place.cell == 'biblionumber' || header_types.item(header_row.$place.cell) == 'biblionumber' %] [% SET batch_biblionumbers = 1 %] [% END %] @@ -791,7 +791,7 @@ canned reports and writing custom SQL reports.

[% # Preserve the whitespace of the following textarea in order to format the values correctly %] -
@@ -804,7 +804,7 @@ canned reports and writing custom SQL reports.

    [% FOREACH header_ro IN header_row %] - [% IF header_ro.cell == 'biblionumber' %] + [% IF header_ro.cell == 'biblionumber' || header_types.item( header_ro.cell ) == 'biblionumber' %]
  • Bibliographic records
  • Batch record modification @@ -816,7 +816,7 @@ canned reports and writing custom SQL reports.

    Add to list
    • [% END %] - [% IF header_ro.cell == 'itemnumber' %] + [% IF header_ro.cell == 'itemnumber' || header_types.item( header_ro.cell ) == 'itemnumber' %] [% IF ( batch_biblionumbers ) %] [% END %] @@ -828,7 +828,7 @@ canned reports and writing custom SQL reports.

    Batch item deletion [% END %] - [% IF header_ro.cell == 'cardnumber' %] + [% IF header_ro.cell == 'cardnumber' || header_types.item( header_ro.cell ) == 'cardnumber' %] [% IF ( batch_biblionumbers || batch_itemnumbers ) %] [% END %] diff --git a/opac/svc/report b/opac/svc/report index b7c91794e6..dac74277db 100755 --- a/opac/svc/report +++ b/opac/svc/report @@ -61,6 +61,7 @@ unless ($json_text) { # convert SQL parameters to placeholders $sql =~ s/(<<.*?>>)/\?/g; + $sql =~ s/\[\[(.*?)\|(.*?)\]\]/$1 AS $2/g; my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, \@sql_params, $report_id ); diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index 0926419488..3c43de071f 100755 --- a/reports/guided_reports.pl +++ b/reports/guided_reports.pl @@ -825,7 +825,8 @@ elsif ($phase eq 'Run this report'){ 'reports' => $report_id, ); } else { - my $sql = get_prepped_report( $sql, \@param_names, \@sql_params); + my ($sql,$header_types) = get_prepped_report( $sql, \@param_names, \@sql_params); + $template->param(header_types => $header_types); my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, undef, $report_id ); my ($sth2, $errors2) = execute_query($sql); my $total = nb_rows($sql) || 0; @@ -1076,6 +1077,17 @@ sub create_non_existing_group_and_subgroup { # pass $sth and sql_params, get back an executable query sub get_prepped_report { my ($sql, $param_names, $sql_params ) = @_; + + # First we split out the placeholders + my @split = split /\[\[|\]\]/,$sql; + my $headers; + for(my $i=0;$i<$#split/2;$i++){ #The placeholders are always the odd elements of the array + my ($type,$name) = split /\|/,$split[$i*2+1]; # We split them on '|' + $headers->{$name} = $type; # Store as a lookup for the template + $split[$i*2+1] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g; #Quote any special characters so we can replace the placeholders + $sql =~ s/\[\[$split[$i*2+1]\]\]/$type AS $name/; # Remove placeholders from SQL + } + my %lookup; @lookup{@$param_names} = @$sql_params; my @split = split /<<|>>/,$sql; @@ -1090,5 +1102,5 @@ sub get_prepped_report { $quoted = C4::Context->dbh->quote($quoted); $sql =~ s/<<$split[$i*2+1]>>/$quoted/; } - return $sql; + return $sql,$headers; } diff --git a/svc/report b/svc/report index 8718dc4517..d16485d4c0 100755 --- a/svc/report +++ b/svc/report @@ -66,6 +66,7 @@ unless ($json_text) { # convert SQL parameters to placeholders $sql =~ s/(<<.*?>>)/\?/g; + $sql =~ s/\[\[(.*?)\|(.*?)\]\]/$1 AS $2/g; my ( $sth, $errors ) = execute_query( $sql, $offset, $limit, \@sql_params, $report_id ); if ($sth) { -- 2.11.0