Bug 17600: Standardize our EXPORT_OK

[srvgit] / Koha / Report.pm

diff --git a/Koha/Report.pm b/Koha/Report.pm
index 01b7dff..176fb6e 100644 (file)
--- a/Koha/Report.pm
+++ b/Koha/Report.pm
@@ -17,13 +17,16 @@ package Koha::Report;
 
 use Modern::Perl;
 
-use Carp;
 
 use Koha::Database;
-use JSON;
 use Koha::Reports;
+use Koha::DateUtils qw( dt_from_string output_pref );
 
 use base qw(Koha::Object);
+#
+# FIXME We could only return an error code instead of the arrayref
+# Only 1 error is returned
+# TODO Koha::Report->store should check this before saving
 
 =head1 NAME
 
@@ -33,8 +36,33 @@ Koha::Report - Koha Report Object class
 
 =head2 Class Methods
 
+=head3 is_sql_valid
+
+my ( $is_sql_valid, $errors ) = $report->is_sql_valid;
+
+$errors is a arrayref of hashrefs, keys can be sqlerr or queryerr.
+
+Validate SQL query string so it only contains a select,
+not any of the harmful queries.
+
 =cut
 
+sub is_sql_valid {
+    my ($self) = @_;
+
+    my $sql = $self->savedsql;
+    $sql //= '';
+    my @errors = ();
+
+    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
+        push @errors, { sqlerr => $1 };
+    } elsif ($sql !~ /^\s*SELECT\b\s*/i) {
+        push @errors, { queryerr => 'Missing SELECT' };
+    }
+
+    return ( @errors ? 0 : 1, \@errors );
+}
+
 =head3 get_search_info
 
 Return search info
@@ -90,6 +118,73 @@ sub new_from_mana {
     Koha::Report->new($data)->store;
 }
 
+=head3 prep_report
+
+Prep the report and return executable sql with parameters embedded and a list of header types
+for building batch action links in the template
+
+=cut
+
+sub prep_report {
+    my ( $self, $param_names, $sql_params ) = @_;
+    my $sql = $self->savedsql;
+
+    # First we split out the placeholders
+    # This part of the code supports using [[ table.field | alias ]] in the
+    # query and replaces it by table.field AS alias. This is used to build
+    # the batch action links foir cardnumbers, itemnumbers, and biblionumbers in the template
+    # while allowing the library to alter the column names
+    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
+        $headers->{$name} =~
+          s/^\w*\.//;    # strip the table name just as in $sth->{NAME} array
+        $split[ $i * 2 + 1 ] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g
+          ;    #Quote any special characters so we can replace the placeholders
+        $name = C4::Context->dbh->quote($name);
+        $sql =~ s/\[\[$split[$i*2+1]\]\]/$type AS $name/
+          ;    # Remove placeholders from SQL
+    }
+
+    my %lookup;
+    @lookup{@$param_names} = @$sql_params;
+    @split = split /<<|>>/, $sql;
+    for ( my $i = 0 ; $i < $#split / 2 ; $i++ ) {
+        my $quoted =
+          @$param_names ? $lookup{ $split[ $i * 2 + 1 ] } : @$sql_params[$i];
+
+        # if there are special regexp chars, we must \ them
+        $split[ $i * 2 + 1 ] =~ s/(\||\?|\.|\*|\(|\)|\%)/\\$1/g;
+        if ( $split[ $i * 2 + 1 ] =~ /\|\s*date\s*$/ ) {
+            $quoted = output_pref(
+                {
+                    dt         => dt_from_string($quoted),
+                    dateformat => 'iso',
+                    dateonly   => 1
+                }
+            ) if $quoted;
+        }
+        unless ( $split[ $i * 2 + 1 ] =~ /\|\s*list\s*$/ && $quoted ) {
+            $quoted = C4::Context->dbh->quote($quoted);
+        }
+        else {
+            my @list = split /\n/, $quoted;
+            my @quoted_list;
+            foreach my $item (@list) {
+                $item =~ s/\r//;
+                push @quoted_list, C4::Context->dbh->quote($item);
+            }
+            $quoted = "(" . join( ",", @quoted_list ) . ")";
+        }
+        $sql =~ s/<<$split[$i*2+1]>>/$quoted/;
+    }
+    return $sql, $headers;
+}
+
 =head3 _type
 
 Returns name of corresponding DBIC resultset