Attachment 91917 Details for Bug 23390 – Bug 23390: Introduce placeholder syntax for report column names

[patch] Bug 23390: Introduce placeholder syntax for report column names

Bug-23390-Introduce-placeholder-syntax-for-report-.patch (text/plain), 11.56 KB, created by ByWater Sandboxes on 2019-08-01 19:34:52 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: ByWater Sandboxes

Created: 2019-08-01 19:34:52 UTC

Size: 11.56 KB

patch

obsolete

>From 6247ecd0661bc3ba9008867c74f3a61fa053e2cd Mon Sep 17 00:00:00 2001
>From: Nick Clemens <nick@bywatersolutions.com>
>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 <george@nekls.org>
>
>Signed-off-by: George Williams <george@nekls.org>
>---
> .../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.</p>
>             <input type="hidden" name="recordtype" value="biblio" />
>             <input type="hidden" name="op" value="list" />
>             [% # Preserve the whitespace of the following textarea in order to format the values correctly %]
>-            <textarea style="display:none" name="recordnumber_list" id="recordnumber_list">[% FOREACH result IN results %][% FOREACH cells IN result.cells %][% place = loop.index %][% IF header_row.$place.cell == 'biblionumber' %][% SET batch_biblionumbers = 1 %]
>+            <textarea style="display:none" name="recordnumber_list" id="recordnumber_list">[% FOREACH result IN results %][% FOREACH cells IN result.cells %][% place = loop.index %][% IF header_row.$place.cell == 'biblionumber' || header_types.item(header_row.$place.cell) == 'biblionumber' %][% SET batch_biblionumbers = 1 %]
> [% cells.cell | html %][% END %][% END %][% END %]</textarea>
>         </form>
> 
>@@ -745,7 +745,7 @@ canned reports and writing custom SQL reports.</p>
>             <input type="hidden" name="recordtype" value="biblio" />
>             <input type="hidden" name="op" value="list" />
>             [% # Preserve the whitespace of the following textarea in order to format the values correctly %]
>-            <textarea style="display:none" name="recordnumber_list" id="recordnumber_list">[% FOREACH result IN results %][% FOREACH cells IN result.cells %][% place = loop.index %][% IF header_row.$place.cell == 'biblionumber' %][% SET batch_biblionumbers = 1 %]
>+            <textarea style="display:none" name="recordnumber_list" id="recordnumber_list">[% FOREACH result IN results %][% FOREACH cells IN result.cells %][% place = loop.index %][% IF header_row.$place.cell == 'biblionumber' || header_types.item(header_row.$place.cell) == 'biblionumber' %][% SET batch_biblionumbers = 1 %]
> [% cells.cell | html %][% END %][% END %][% END %]</textarea>
>         </form>
> 
>@@ -754,7 +754,7 @@ canned reports and writing custom SQL reports.</p>
>             [% 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 %]
>                         <input type="hidden" name="[% header_row.$place.cell | html %]" value="[% cells.cell | html %]" />
>                     [% END %]
>@@ -768,7 +768,7 @@ canned reports and writing custom SQL reports.</p>
>             [% 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 %]
>                         <input type="hidden" name="[% header_row.$place.cell | html %]" value="[% cells.cell | html %]" />
>                     [% END %]
>@@ -780,7 +780,7 @@ canned reports and writing custom SQL reports.</p>
>             [% 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 %]
>                         <input type="hidden" class="bib_to_list" name="biblionumber" value="[% cells.cell | html %]" />
>                     [% END %]
>@@ -791,7 +791,7 @@ canned reports and writing custom SQL reports.</p>
>         <form method="POST" action="/cgi-bin/koha/tools/modborrowers.pl" id="batch_patron_modification">
>             <input type="hidden" name="op" value="show" />
>             [% # Preserve the whitespace of the following textarea in order to format the values correctly %]
>-            <textarea style="display:none" name="cardnumberlist" id="cardnumberlist">[% FOREACH result IN results %][% FOREACH cells IN result.cells %][% place = loop.index %][% IF header_row.$place.cell == 'cardnumber' %][% SET batch_cardnumbers = 1 %]
>+            <textarea style="display:none" name="cardnumberlist" id="cardnumberlist">[% FOREACH result IN results %][% FOREACH cells IN result.cells %][% place = loop.index %][% IF header_row.$place.cell == 'cardnumber' || header_types.item(header_row.$place.cell) == 'cardnumber' %][% SET batch_cardnumbers = 1 %]
> [% cells.cell | html %][% END %][% END %][% END %]</textarea>
>         </form>
> 
>@@ -804,7 +804,7 @@ canned reports and writing custom SQL reports.</p>
>                     </button>
>                     <ul class="dropdown-menu" aria-labelledby="batch_mod_menu">
>                         [% FOREACH header_ro IN header_row %]
>-                            [% IF header_ro.cell == 'biblionumber' %]
>+                            [% IF header_ro.cell == 'biblionumber' || header_types.item( header_ro.cell ) == 'biblionumber' %]
>                                 <li class="dropdown-header">Bibliographic records</li>
>                                 <li>
>                                     <a href="#" data-submit="batch_record_modification" data-toggle="tooltip" data-placement="right" title="Send visible records to batch record modification" class="batch_op send_to_record_mod">Batch record modification</a>
>@@ -816,7 +816,7 @@ canned reports and writing custom SQL reports.</p>
>                                     <a href="#" data-submit="batch_add_to_list" data-toggle="tooltip" data-placement="right" title="Send visible records to a list" class="batch_op send_to_list">Add to list</a>
>                                 </li>
>                             [% END %]
>-                            [% IF header_ro.cell == 'itemnumber' %]
>+                            [% IF header_ro.cell == 'itemnumber' || header_types.item( header_ro.cell ) == 'itemnumber' %]
>                                 [% IF ( batch_biblionumbers ) %]
>                                     <li role="separator" class="divider"></li>
>                                 [% END %]
>@@ -828,7 +828,7 @@ canned reports and writing custom SQL reports.</p>
>                                     <a href="#" data-submit="batch_item_deletion" data-toggle="tooltip" data-placement="right" title="Send visible items to batch item deletion" class="batch_op send_to_item_del">Batch item deletion</a>
>                                 </li>
>                             [% END %]
>-                            [% IF header_ro.cell == 'cardnumber' %]
>+                            [% IF header_ro.cell == 'cardnumber' || header_types.item( header_ro.cell ) == 'cardnumber' %]
>                                 [% IF ( batch_biblionumbers || batch_itemnumbers ) %]
>                                     <li role="separator" class="divider"></li>
>                                 [% 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

Actions: View | Diff | Splinter Review

Attachments on bug 23390: 91849 | 91880 | 91882 | 91888 | 91917 | 91918 | 91919 | 92095 | 92096 | 92097