From 834001ffbb6511bdae7014fda9f9906af366074e Mon Sep 17 00:00:00 2001 From: Chris Cormack Date: Sun, 19 Apr 2009 05:59:55 +1200 Subject: [PATCH] Allowing users to edit saved sql in guided reports. Bug 1545 Signed-off-by: Galen Charlton --- C4/Reports/Guided.pm | 13 ++++++- .../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 669b3eb279..86c5ccbb23 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 c0e5131753..eb93a891fb 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.

"> -&phase=Show%20SQL">Show SQL +&phase=Show%20SQL">Show SQL&phase=Edit%20SQL">Edit SQL &phase=Run%20this%20report">Run this Report ">Schedule this Report &phase=Delete%20Saved">Delete @@ -454,6 +455,22 @@ Sub report: + +
+ +"> +
+Edit SQL + +
+ +
+
+
+ + + +

Your report has been saved

diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl index c97d4aa7ae..e06d9d1810 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); -- 2.39.5