my @errors = ();
my $phase = $input->param('phase');
-
if ( !$phase ) {
$template->param( 'start' => 1 );
# show welcome page
);
}
+elsif ( $phase eq 'Edit SQL'){
+
+ my $id = $input->param('reports');
+ my ($sql,$type,$reportname,$notes) = get_saved_report($id);
+ $template->param(
+ 'sql' => $sql,
+ 'reportname' => $reportname,
+ 'id' => $id,
+ 'editsql' => 1,
+ );
+}
+
+elsif ( $phase eq 'Update SQL'){
+ my $id = $input->param('id');
+ my $sql = $input->param('sql');
+ my $reportname = $input->param('reportname');
+ 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, $reportname );
+ $template->param(
+ 'save_successful' => 1,
+ );
+ }
+
+}
+
elsif ($phase eq 'retrieve results') {
my $id = $input->param('id');
my ($results,$name,$notes) = format_results($id);
);
}
else {
- save_report( $sql, $name, $type, $notes );
+ save_report( $borrowernumber, $sql, $name, $type, $notes );
$template->param(
'save_successful' => 1,
);
}
elsif ($phase eq 'Run this report'){
- binmode STDOUT, ':utf8';
-
# execute a saved report
my $limit = 20; # page size. # TODO: move to DB or syspref?
my $offset = 0;
elsif ($phase eq 'Export'){
binmode STDOUT, ':utf8';
- # export results to tab separated text
- my $sql = $input->param('sql'); # FIXME: use sql from saved report ID#, not new user-supplied SQL!
+ # export results to tab separated text or CSV
+ my $sql = $input->param('sql'); # FIXME: use sql from saved report ID#, not new user-supplied SQL!
my $format = $input->param('format');
- my ($sth, $q_errors) = execute_query($sql);
+ my ($sth, $q_errors) = execute_query($sql);
unless ($q_errors and @$q_errors) {
print $input->header( -type => 'application/octet-stream',
- -attachment=>'reportresults.csv'
+ -attachment=>"reportresults.$format"
);
- my $csv = Text::CSV->new({binary => 1});
- $csv or die "Text::CSV->new({binary => 1}) FAILED: " . Text::CSV->error_diag();
- if ($csv->combine(header_cell_values($sth))) {
- print $csv->string(), "\n";
+ if ($format eq 'tab') {
+ print join("\t", header_cell_values($sth)), "\n";
+ while (my $row = $sth->fetchrow_arrayref()) {
+ print join("\t", @$row), "\n";
+ }
} else {
- push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() } ;
- }
- while (my $row = $sth->fetchrow_arrayref()) {
- if ($csv->combine(@$row)) {
- print $csv->string(), "\n";
+ my $csv = Text::CSV->new({binary => 1});
+ $csv or die "Text::CSV->new({binary => 1}) FAILED: " . Text::CSV->error_diag();
+ if ($csv->combine(header_cell_values($sth))) {
+ print $csv->string(), "\n";
} else {
- push @$q_errors, { combine => $csv->error_diag() } ;
+ push @$q_errors, { combine => 'HEADER ROW: ' . $csv->error_diag() } ;
+ }
+ while (my $row = $sth->fetchrow_arrayref()) {
+ if ($csv->combine(@$row)) {
+ print $csv->string(), "\n";
+ } else {
+ push @$q_errors, { combine => $csv->error_diag() } ;
+ }
}
}
foreach my $err (@$q_errors, @errors) {
}
# pass $sth, get back an array of names for the column headers
-sub header_cell_values ($) {
+sub header_cell_values {
my $sth = shift or return ();
return @{$sth->{NAME}};
}
# pass $sth, get back a TMPL_LOOP-able set of names for the column headers
-sub header_cell_loop ($) {
+sub header_cell_loop {
my @headers = map { +{ cell => $_ } } header_cell_values (shift);
return \@headers;
}