[Koha-patches] [PATCH] Allowing users to edit saved sql in guided reports. Bug 1545

[Chris Cormack]

---
 C4/Reports/Guided.pm                               |   13 ++++++-
 .../en/modules/reports/guided_reports_start.tmpl   |   19 +++++++++-
 reports/guided_reports.pl                          |   37 +++++++++++++++++++-
 3 files changed, 66 insertions(+), 3 deletions(-)

diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm
index 669b3eb..86c5ccb 100644
--- a/C4/Reports/Guided.pm
+++ b/C4/Reports/Guided.pm
@@ -42,7 +42,7 @@ BEGIN {
 		save_report get_saved_reports execute_query get_saved_report create_compound run_compound
 		get_column_type get_distinct_values save_dictionary get_from_dictionary
 		delete_definition delete_report format_results get_sql
-        select_2_select_count_value
+        select_2_select_count_value update_sql
 	);
 }
 
@@ -446,6 +446,17 @@ sub save_report {
     $sth->execute( 0, $sql, $name, $type, $notes );
 }
 
+sub update_sql {
+    my $id = shift || croak "No Id given";
+    my $sql = shift;
+    my $dbh = C4::Context->dbh();
+    $sql =~ s/(\s*\;\s*)$//; # removes trailing whitespace and /;/
+    my $query = "UPDATE saved_sql SET savedsql = ?, last_modified = now() WHERE id = ? ";
+    my $sth = $dbh->prepare($query);
+    $sth->execute( $sql, $id );
+    $sth->finish();
+}
+
 sub store_results {
 	my ($id,$xml)=@_;
 	my $dbh = C4::Context->dbh();
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl
index c0e5131..eb93a89 100644
--- a/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl
+++ b/koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tmpl
@@ -110,7 +110,8 @@ canned reports and writing custom SQL reports.</p>
 <td><!-- TMPL_VAR NAME="notes" --></td>
 <td><!-- TMPL_IF NAME="date_run" --><a href="/cgi-bin/koha/reports/guided_reports.pl?phase=retrieve%20results&id=<!-- TMPL_VAR NAME="id" -->"><!-- TMPL_VAR NAME="date_run" --></a><!-- /TMPL_IF -->
 </td>
-<td><a href="/cgi-bin/koha/reports/guided_reports.pl?reports=<!-- TMPL_VAR NAME="id" -->&amp;phase=Show%20SQL">Show SQL</a></td>
+<td><a href="/cgi-bin/koha/reports/guided_reports.pl?reports=<!-- TMPL_VAR NAME="id" -->&amp;phase=Show%20SQL">Show SQL</a> 
+&nbsp; <a href="/cgi-bin/koha/reports/guided_reports.pl?reports=<!-- TMPL_VAR NAME="id" -->&amp;phase=Edit%20SQL">Edit SQL</a></td>
 <td><a href="/cgi-bin/koha/reports/guided_reports.pl?reports=<!-- TMPL_VAR NAME="id" -->&amp;phase=Run%20this%20report">Run this Report</a></td>
 <td><a href="/cgi-bin/koha/tools/scheduler.pl?id=<!-- TMPL_VAR NAME="id" -->">Schedule this Report</a></td>
 <td><a class="confirmdelete" title="Delete this saved report" href="/cgi-bin/koha/reports/guided_reports.pl?reports=<!-- TMPL_VAR NAME="id" -->&amp;phase=Delete%20Saved">Delete</a></td></tr>
@@ -454,6 +455,22 @@ Sub report:<select name="subreport">
 <textarea id="sql"><!-- TMPL_VAR NAME="sql" --></textarea>
 <!-- /TMPL_IF -->
 
+<!-- TMPL_IF NAME="editsql" -->
+<form action="/cgi-bin/koha/reports/guided_reports.pl" method="post">
+<input type="hidden" name="phase" value="Update SQL" />
+<input type="hidden" name="id" value="<!-- TMPL_VAR NAME="id" -->">
+<fieldset class="rows">
+<legend>Edit SQL</legend>
+<textarea id="sql" name="sql"><!-- TMPL_VAR NAME="sql" --></textarea>
+</fieldset>
+
+<fieldset class="action">
+<input type="submit" name="submit" value="Update SQL" /></fieldset>
+</form>
+
+
+<!-- /TMPL_IF -->
+
 <!-- TMPL_IF NAME="save_successful" -->
 <!-- TMPL_UNLESS NAME="errors" -->
 <h2>Your report has been saved</h2>
diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index c97d4aa..e06d9d1 100755
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -54,7 +54,6 @@ my ( $template, $borrowernumber, $cookie ) = get_template_and_user(
 
     my @errors = ();
 my $phase = $input->param('phase');
-
 if ( !$phase ) {
     $template->param( 'start' => 1 );
     # show welcome page
@@ -90,6 +89,42 @@ elsif ( $phase eq 'Show SQL'){
     );
 }
 
+elsif ( $phase eq 'Edit SQL'){
+	
+	my $id = $input->param('reports');
+	my $sql = get_sql($id);
+	$template->param(
+		'sql'     => $sql,
+	        'id'      => $id,
+		'editsql' => 1,
+    );
+}
+
+elsif ( $phase eq 'Update SQL'){
+    my $id = $input->param('id');
+    my $sql = $input->param('sql');
+    my @errors;
+    if ($sql =~ /;?\W?(UPDATE|DELETE|DROP|INSERT|SHOW|CREATE)\W/i) {
+        push @errors, {sqlerr => $1};
+    }
+    elsif ($sql !~ /^(SELECT)/i) {
+        push @errors, {queryerr => 1};
+    }
+    if (@errors) {
+        $template->param(
+            'errors'    => \@errors,
+            'sql'       => $sql,
+        );
+    }
+    else {
+        update_sql( $id, $sql );
+        $template->param(
+            'save_successful'       => 1,
+        );
+    }
+    
+}
+
 elsif ($phase eq 'retrieve results') {
 	my $id = $input->param('id');
 	my ($results,$name,$notes) = format_results($id);
-- 
1.5.6.3