From 71d41c0a9b193d1572b6ad7e6e8a23a8bc13af07 Mon Sep 17 00:00:00 2001
From: Chris Cormack <crc@liblime.com>
Date: Mon, 29 Oct 2007 13:22:42 -0500
Subject: [PATCH] Can now view sql, and reports run page shows name

Signed-off-by: Chris Cormack <crc@liblime.com>
Signed-off-by: Joshua Ferraro <jmf@liblime.com>
---
 C4/Reports.pm                                 | 20 +++++++++++++++----
 .../modules/reports/guided_reports_start.tmpl | 12 +++++++----
 reports/guided_reports.pl                     | 16 +++++++++++++--
 3 files changed, 38 insertions(+), 10 deletions(-)

diff --git a/C4/Reports.pm b/C4/Reports.pm
index 0944c5264e..aca6c205e6 100644
--- a/C4/Reports.pm
+++ b/C4/Reports.pm
@@ -29,14 +29,14 @@ use XML::Dumper;
 # use Data::Dumper;
 
 # set the version for version checking
-$VERSION = 0.01;
+$VERSION = 0.11;
 
 @ISA = qw(Exporter);
 @EXPORT =
   qw(get_report_types get_report_areas get_columns build_query get_criteria
   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);
+  delete_definition delete_report format_results get_sql );
 
 our %table_areas;
 $table_areas{'1'} =
@@ -483,7 +483,7 @@ sub get_saved_report {
     $sth->execute($id);
     my $data = $sth->fetchrow_hashref();
     $sth->finish();
-    return ( $data->{'savedsql'}, $data->{'type'} );
+    return ( $data->{'savedsql'}, $data->{'type'}, $data->{'report_name'}, $data->{'notes'} );
 }
 
 =item create_compound($masterID,$subreportID)
@@ -610,7 +610,19 @@ sub delete_definition {
 	my $sth = $dbh->prepare($query);
 	$sth->execute($id);
 	$sth->finish();
-	}
+}
+
+sub get_sql {
+	my ($id) = @_;
+	my $dbh = C4::Context->dbh();
+	my $query = "SELECT * FROM saved_sql WHERE id = ?";
+	my $sth = $dbh->prepare($query);
+	$sth->execute($id);
+	my $data=$sth->fetchrow_hashref();
+	$sth->finish();	
+	return $data->{'savedsql'};
+}
+
 =head1 AUTHOR
 
 Chris Cormack <crc@liblime.com>
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 922e204ec4..122b24f4db 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
@@ -44,14 +44,14 @@ reports</p>
 <p>Choose the report to run from the list</p>
 <table>
 <form action="/cgi-bin/koha/reports/guided_reports.pl">
-<th>Report Name</th><th>Type</th><th>Notes</th><th>Saved Results</th><th>Delete</th>
+<th>Report Name</th><th>Type</th><th>Notes</th><th>Saved Results</th><th>Saved SQL</th><th>Delete</th>
 <!-- TMPL_LOOP NAME="savedreports" -->
 <tr><td><a href="/cgi-bin/koha/reports/guided_reports.pl?reports=<!-- TMPL_VAR NAME="id" -->&phase=Run%20this%20report"><!-- TMPL_VAR NAME="report_name" --></td>
 <td><!-- TMPL_VAR NAME="type" --></td>
 <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>Show SQL</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=Delete%20Saved">Delete</a></td></tr>
 <!-- /TMPL_LOOP -->
 </form>
@@ -268,8 +268,8 @@ Notes: <textarea name="notes"></textarea><br />
 
 <!-- TMPL_IF NAME="execute" -->
 <h1>Show results</h1>
-<h2>Report Name</h2>
-<p>Report description</p>
+<h2><!-- TMPL_VAR NAME="name" --></h2>
+<p><!-- TMPL_VAR NAME="notes" --></p>
 <table>
 <!-- TMPL_LOOP NAME="results" -->
 <!-- TMPL_VAR NAME="row" -->
@@ -334,6 +334,10 @@ Sub report:<select name="subreport">
 </table>
 <!-- /TMPL_IF -->
 
+<!-- TMPL_IF NAME="showsql" -->
+<!-- TMPL_VAR NAME="sql" -->
+<!-- /TMPL_IF -->
+
 </div>
 </div>
 </div>
diff --git a/reports/guided_reports.pl b/reports/guided_reports.pl
index 4857ecaa26..861c67f6e1 100755
--- a/reports/guided_reports.pl
+++ b/reports/guided_reports.pl
@@ -86,6 +86,16 @@ elsif ( $phase eq 'Delete Saved') {
 	
 }		
 
+elsif ( $phase eq 'Show SQL'){
+	
+	my $id = $input->param('reports');
+	my $sql = get_sql($id);
+	$template->param(
+		'sql' => $sql,
+		'showsql' => 1,
+		);
+}
+
 elsif ($phase eq 'retrieve results') {
 	my $id = $input->param('id');
 	my $results = format_results($id);
@@ -324,12 +334,14 @@ elsif ( $phase eq 'Execute' ) {
 elsif ($phase eq 'Run this report'){
     # execute a saved report
 	my $report = $input->param('reports');
-	my ($sql,$type) = get_saved_report($report);
+	my ($sql,$type,$name,$notes) = get_saved_report($report);
 	my $results = execute_query($sql,$type);
     $template->param(
         'results' => $results,
 		'sql' => $sql,
-        'execute' => 1
+        'execute' => 1,
+		'name' => $name,
+		'notes' => $notes,
     );
 }	
 
-- 
2.39.5