Bug 27380: Move get_prepped_report to object and use for svc/reports

[koha-ffzg.git] / C4 / Reports / Guided.pm

diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm
index 61aa804..a76d86b 100644 (file)
--- a/C4/Reports/Guided.pm
+++ b/C4/Reports/Guided.pm
@@ -445,8 +445,11 @@ sub nb_rows {
     $dbh->{RaiseError} = $RaiseError;
     $dbh->{PrintError} = $PrintError;
     if ($@) { # To catch "Duplicate column name" caused by the derived table, or any other syntax error
-        $sth = $dbh->prepare($sql);
-        $sth->execute;
+        eval {
+            $sth = $dbh->prepare($sql);
+            $sth->execute;
+        };
+        warn $@ if $@;
         # Loop through the complete results, fetching 1,000 rows at a time.  This
         # lowers memory requirements but increases execution time.
         while (my $rows = $sth->fetchall_arrayref(undef, 1000)) {
@@ -554,6 +557,18 @@ sub execute_query {
         return (undef, { queryerr => 'Missing SELECT'} );
     }
 
+    foreach my $sql_param ( @$sql_params ){
+        if ( $sql_param =~ m/\n/ ){
+            my @list = split /\n/, $sql_param;
+            my @quoted_list;
+            foreach my $item ( @list ){
+                $item =~ s/\r//;
+              push @quoted_list, C4::Context->dbh->quote($item);
+            }
+            $sql_param = "(".join(",",@quoted_list).")";
+        }
+    }
+
     my ($useroffset, $userlimit);
 
     # Grab offset/limit from user supplied LIMIT and drop the LIMIT so we can control pagination
@@ -576,7 +591,10 @@ sub execute_query {
     $dbh->do( 'UPDATE saved_sql SET last_run = NOW() WHERE id = ?', undef, $report_id ) if $report_id;
 
     my $sth = $dbh->prepare($sql);
-    $sth->execute(@$sql_params, $offset, $limit);
+    eval {
+        $sth->execute(@$sql_params, $offset, $limit);
+    };
+    warn $@ if $@;
 
     return ( $sth, { queryerr => $sth->errstr } ) if ($sth->err);
     return ( $sth );
@@ -599,17 +617,29 @@ sub save_report {
     my $area = $fields->{area};
     my $group = $fields->{group};
     my $subgroup = $fields->{subgroup};
-    my $cache_expiry = $fields->{cache_expiry} || 300;
+    my $cache_expiry = $fields->{cache_expiry};
     my $public = $fields->{public};
 
-    my $dbh = C4::Context->dbh();
     $sql =~ s/(\s*\;\s*)$//;    # removes trailing whitespace and /;/
-    my $query = "INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,report_area,report_group,report_subgroup,type,notes,cache_expiry,public)  VALUES (?,now(),now(),?,?,?,?,?,?,?,?,?)";
-    $dbh->do($query, undef, $borrowernumber, $sql, $name, $area, $group, $subgroup, $type, $notes, $cache_expiry, $public);
+    my $now = dt_from_string;
+    my $report = Koha::Report->new(
+        {
+            borrowernumber  => $borrowernumber,
+            date_created    => $now, # Must be moved to Koha::Report->store
+            last_modified   => $now, # Must be moved to Koha::Report->store
+            savedsql        => $sql,
+            report_name     => $name,
+            report_area     => $area,
+            report_group    => $group,
+            report_subgroup => $subgroup,
+            type            => $type,
+            notes           => $notes,
+            cache_expiry    => $cache_expiry,
+            public          => $public,
+        }
+    )->store;
 
-    my $id = $dbh->selectrow_array("SELECT max(id) FROM saved_sql WHERE borrowernumber=? AND report_name=?", undef,
-                                   $borrowernumber, $name);
-    return $id;
+    return $report->id;
 }
 
 sub update_sql {
@@ -623,14 +653,22 @@ sub update_sql {
     my $cache_expiry = $fields->{cache_expiry};
     my $public = $fields->{public};
 
+    $sql =~ s/(\s*\;\s*)$//;    # removes trailing whitespace and /;/
+    my $report = Koha::Reports->find($id);
+    $report->last_modified(dt_from_string);
+    $report->savedsql($sql);
+    $report->report_name($name);
+    $report->notes($notes);
+    $report->report_group($group);
+    $report->report_subgroup($subgroup);
+    $report->cache_expiry($cache_expiry) if defined $cache_expiry;
+    $report->public($public);
+    $report->store();
     if( $cache_expiry >= 2592000 ){
-      die "Please specify a cache expiry less than 30 days\n";
+      die "Please specify a cache expiry less than 30 days\n"; # That's a bit harsh
     }
 
-    my $dbh        = C4::Context->dbh();
-    $sql =~ s/(\s*\;\s*)$//;    # removes trailing whitespace and /;/
-    my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now(), report_name = ?, report_group = ?, report_subgroup = ?, notes = ?, cache_expiry = ?, public = ? WHERE id = ? ";
-    $dbh->do($query, undef, $sql, $name, $group, $subgroup, $notes, $cache_expiry, $public, $id );
+    return $report;
 }
 
 sub store_results {
@@ -890,6 +928,7 @@ Returns a hash containig all reserved words
 sub GetReservedAuthorisedValues {
     my %reserved_authorised_values =
             map { $_ => 1 } ( 'date',
+                              'list',
                               'branches',
                               'itemtypes',
                               'cn_source',