[Koha-patches] [PATCH] Allowing users to edit saved sql in guided reports. Bug 1545
Chris Cormack
chris at bigballofwax.co.nz
Sat Apr 18 19:59:55 CEST 2009
---
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" -->&phase=Show%20SQL">Show SQL</a></td>
+<td><a href="/cgi-bin/koha/reports/guided_reports.pl?reports=<!-- TMPL_VAR NAME="id" -->&phase=Show%20SQL">Show SQL</a>
+ <a href="/cgi-bin/koha/reports/guided_reports.pl?reports=<!-- TMPL_VAR NAME="id" -->&phase=Edit%20SQL">Edit SQL</a></td>
<td><a href="/cgi-bin/koha/reports/guided_reports.pl?reports=<!-- TMPL_VAR NAME="id" -->&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" -->&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
More information about the Koha-patches
mailing list