Attachment 27327 Details for Bug 9915 – Bug 9915: Allow sql_params to be passed to a report using the OPAC svc/report interface

[patch] Bug 9915: Allow sql_params to be passed to a report using the OPAC svc/report interface

Bug-9915-Allow-sqlparams-to-be-passed-to-a-report-.patch (text/plain), 4.77 KB, created by Galen Charlton on 2014-04-20 16:08:21 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Galen Charlton

Created: 2014-04-20 16:08:21 UTC

Size: 4.77 KB

patch

obsolete

>From 925049788ffd9ec8c8e4633e3659b9868eba7126 Mon Sep 17 00:00:00 2001
>From: Chris Cormack <chris@bigballofwax.co.nz>
>Date: Sun, 24 Mar 2013 20:29:18 +1300
>Subject: [PATCH] Bug 9915: Allow sql_params to be passed to a report using
> the OPAC svc/report interface
>
>Test plan:
>
>1/ Make a public report that contains SQL parameters
>2/ Test it from the staff side
>3/ Hit the url like
>cgi-bin/koha/svc/report?id=1&sql_params=chris&sql_params=chris2
>4/ Test you get JSON
>
>Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>
>Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
>Signed-off-by: Katrin Fischer <Katrin.Fischer.83@web.de>
>Passes all tests and QA script.
>
>Tested with a public report:
>select * from items
>  - cgi-bin/koha/svc/report?id=6
>  - URL shows JSON and all items
>select * from items where itype = <<itype>>
>  - cgi-bin/koha/svc/report?id=6&sql_params=BK
>  - URL shows JSON and only BK items
>select * from items where itype = <<itype>> and homebranch = <<homebranch>>
>  - cgi-bin/koha/svc/report?id=6&sql_params=BK&sql_params=MPL
>  - URL shows JSON and only BK items in MPL
>select * from items where itype = <<itype|itemtypes>> and homebranch = <<homebranch|branches>>
>  - same as above
>  - URL shows JSON and only BK items in MPL
>select * from items where dateaccessioned = 2013-12-15
>  - cgi-bin/koha/svc/report?id=6&sql_params=BK&sql_params=MPL&sql_params=2013-12-15
>  - URL shows JSON and correct items
>
>Note: This will currently only work for public reports!
>
>Signed-off-by: Galen Charlton <gmc@esilibrary.com>
>---
> opac/svc/report |   48 ++++++++++++++++++++++++++++++++++++------------
> 1 file changed, 36 insertions(+), 12 deletions(-)
>
>diff --git a/opac/svc/report b/opac/svc/report
>index 57ca797..fb50ba2 100755
>--- a/opac/svc/report
>+++ b/opac/svc/report
>@@ -4,11 +4,14 @@
> #
> # Copyright (C) 2011  Chris Cormack <chris@bigballofwax.co.nz>
> # Copyright (C) 2013  Mark Tompsett
>+# Updated 2013 by Chris Cormack <chris@bigballofwax.co.nz>
> #
>-# Koha is free software; you can redistribute it and/or modify it
>-# under the terms of the GNU General Public License as published by
>-# the Free Software Foundation; either version 3 of the License, or
>-# (at your option) any later version.
>+# This file is part of Koha.
>+#
>+# Koha is free software; you can redistribute it and/or modify it under the
>+# terms of the GNU General Public License as published by the Free Software
>+# Foundation; either version 3 of the License, or (at your option) any later
>+# version.
> #
> # Koha is distributed in the hope that it will be useful, but
> # WITHOUT ANY WARRANTY; without even the implied warranty of
>@@ -26,8 +29,8 @@ use CGI;
> 
> use Koha::Cache;
> 
>-my $query  = CGI->new();
>-my $report_id = $query->param('id');
>+my $query       = CGI->new();
>+my $report_id   = $query->param('id');
> my $report_name = $query->param('name');
> my $report_annotation = $query->param('annotated');
> 
>@@ -36,18 +39,38 @@ if (!$report_rec) { die "There is no such report.\n"; }
> 
> die "Sorry this report is not public\n" unless $report_rec->{public};
> 
>+my @sql_params  = $query->param('sql_params');
>+
> my $cache_active = Koha::Cache->is_cache_active;
>-my ($cache_key, $cache, $json_text);
>+my ( $cache_key, $cache, $json_text );
> if ($cache_active) {
>-    $cache_key = "opac:report:".($report_name ? "name:$report_name" : "id:$report_id");
>-    $cache = Koha::Cache->new();
>+    $cache_key =
>+        "opac:report:"
>+      . ( $report_name ? "name:$report_name" : "id:$report_id" )
>+      . @sql_params;
>+    $cache     = Koha::Cache->new();
>     $json_text = $cache->get_from_cache($cache_key);
> }
> 
> unless ($json_text) {
>     my $offset = 0;
>-    my $limit  = C4::Context->preference("SvcMaxReportRows") || 10;
>-    my ( $sth, $errors ) = execute_query( $report_rec->{savedsql}, $offset, $limit );
>+    my $limit = C4::Context->preference("SvcMaxReportRows") || 10;
>+    my $sql = $report_rec->{savedsql};
>+    if (@sql_params) {
>+
>+        # we have sql params need to fix the sql
>+        my @split = split /<<|>>/, $sql;
>+        my @tmpl_parameters;
>+        for ( my $i = 0 ; $i < $#split / 2 ; $i++ ) {
>+            my $quoted = C4::Context->dbh->quote( $sql_params[$i] );
>+
>+            # if there are special regexp chars, we must \ them
>+            $split[ $i * 2 + 1 ] =~ s/(\||\?|\.|\*|$|$|\%)/\\$1/g;
>+            $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
>+        }
>+    }
>+    my ( $sth, $errors ) =
>+      execute_query( $sql, $offset, $limit );
>     if ($sth) {
>         my $lines;
>         if ($report_annotation) {
>@@ -59,7 +82,8 @@ unless ($json_text) {
>         $json_text = to_json($lines);
> 
>         if ($cache_active) {
>-            $cache->set_in_cache( $cache_key, $json_text, $report_rec->{cache_expiry} );
>+            $cache->set_in_cache( $cache_key, $json_text,
>+                $report_rec->{cache_expiry} );
>         }
>     }
>     else {
>-- 
>1.7.10.4

Actions: View | Diff | Splinter Review

Attachments on bug 9915: 16830 | 25918 | 25957 | 27316 | 27327 | 27328 | 27330 | 27331 | 27332