path: root/perl/manager/displaytable.pm

# displaytable(TABLENAME, CONFIG...):
#
#   stolen from sqltohtml in the ucd-snmp package
#

package NetSNMP::manager::displaytable;
use POSIX (isprint);

BEGIN {
    use Exporter ();
    use vars qw(@ISA @EXPORT_OK $tableparms $headerparms);
    @ISA = qw(Exporter);
    @EXPORT=qw(&displaytable &displaygraph);

    require DBI;
    require CGI;

    use GD::Graph();
    use GD::Graph::lines();
    use GD::Graph::bars();
    use GD::Graph::points();
    use GD::Graph::linespoints();
    use GD::Graph::area();
    use GD::Graph::pie();
};

$tableparms="border=1 bgcolor=\"#c0c0e0\"";
$headerparms="border=1 bgcolor=\"#b0e0b0\"";

sub displaygraph {
    my $dbh = shift;
    my $tablename = shift;
    my %config = @_;
    my $type = $config{'-type'} || "lines";
    my $x = $config{'-x'} || "640";
    my $y = $config{'-y'} || "480";
    my $bgcolor = $config{'-bgcolor'} || "white";
    my $datecol = $config{'-xcol'} || "updated";
    my $xtickevery = $config{'-xtickevery'} || 50;
    my ($thetable);

#    print STDERR join(",",@_),"\n";

    return -1 if (!defined($dbh) || !defined($tablename) || 
		  !defined ($config{'-columns'}) || 
		  ref($config{'-columns'}) ne "ARRAY" ||
		  !defined ($config{'-indexes'}) || 
		  ref($config{'-indexes'}) ne "ARRAY");


    my $cmd = "SELECT " . 
	join(",",@{$config{'-columns'}},
	     @{$config{'-indexes'}}, $datecol) .
		 " FROM $tablename $config{'-clauses'}";
    ( $thetable = $dbh->prepare($cmd))
	or return -1;
    ( $thetable->execute )
	or return -1;

    my %data;
    my $count = 0;

    while( $row = $thetable->fetchrow_hashref() ) {
	# XXX: multiple indexe columns -> unique name
	# save all the row's data based on the index column(s)
	foreach my $j (@{$config{'-columns'}}) {
	    if ($config{'-difference'} || $config{'-rate'}) {
		if (defined($lastval{$row->{$config{'-indexes'}[0]}}{$j}{'value'})) {
		    $data{$row->{$config{'-indexes'}[0]}}{$row->{$datecol}}{$j}=
			$row->{$j} - 
			    $lastval{$row->{$config{'-indexes'}[0]}}{$j}{'value'};
		    #
		    # convert to a rate if desired.
		    #
		    if ($config{'-rate'}) {
			if (($row->{$datecol} - $lastval{$row->{$config{'-indexes'}[0]}}{$j}{'index'})) {
			    $data{$row->{$config{'-indexes'}[0]}}{$row->{$datecol}}{$j} = $data{$row->{$config{'-indexes'}[0]}}{$row->{$datecol}}{$j}*$config{'-rate'}/($row->{$datecol} - $lastval{$row->{$config{'-indexes'}[0]}}{$j}{'index'});
			} else {
			    $data{$row->{$config{'-indexes'}[0]}}{$row->{$datecol}}{$j} = -1;
			}
		    }

		}
		$lastval{$row->{$config{'-indexes'}[0]}}{$j}{'value'} = $row->{$j};
		$lastval{$row->{$config{'-indexes'}[0]}}{$j}{'index'} = $row->{$datecol};
	    } else {
		$data{$row->{$config{'-indexes'}[0]}}{$row->{$datecol}}{$j} = $row->{$j};
	    }

	    #
	    # limit the data to a vertical range.
	    #
	    if (defined($config{'-max'}) && 
		$data{$row->{$config{'-indexes'}[0]}}{$row->{$datecol}}{$j} > 
		$config{'-max'}) {
		# set to max value
		$data{$row->{$config{'-indexes'}[0]}}{$row->{$datecol}}{$j} = 
		    $config{'-max'};
	    }
	    
	    if (defined($config{'-min'}) && 
		$data{$row->{$config{'-indexes'}[0]}}{$row->{$datecol}}{$j} < 
		$config{'-min'}) {
		# set to min value
		$data{$row->{$config{'-indexes'}[0]}}{$row->{$datecol}}{$j} = 
		    $config{'-min'};
	    }
	}
	push @xdata,$row->{$datecol};
    }

    my @pngdata;

    if (defined($config{'-createdata'})) {
	&{$config{'-createdata'}}(\@pngdata, \@xdata, \%data);
    } else {
	push @pngdata, \@xdata;

	my @datakeys = keys(%data);

#    open(O,">/tmp/data");
	foreach my $i (@datakeys) {
	    foreach my $j (@{$config{'-columns'}}) {
		my @newrow;
		foreach my $k (@xdata) {
#		print O "i=$i k=$k j=$j :: $data{$i}{$k}{$j}\n";
		    push @newrow, ($data{$i}{$k}{$j} || 0);
		}
		push @pngdata,\@newrow;
	    }
	}
    }
#    close O;

    if ($#pngdata > 0) {
    # create the graph itself
	my $graph = new GD::Graph::lines($x, $y);
	$graph->set('bgclr' => $bgcolor);
#	print STDERR "columns: ", join(",",@{$config{'-columns'}}), "\n";
 	if (defined($config{'-legend'})) {
# 	    print STDERR "legend: ", join(",",@{$config{'-legend'}}), "\n";
 	    $graph->set_legend(@{$config{'-legend'}});
 	} else {
 	    my @legend;
 	    foreach my $xxx (@{$config{'-columns'}}) {
 		push @legend, "$xxx = $config{'-indexes'}[0]";
 	    }
 	    $graph->set_legend(@legend);
 	}
	foreach my $i (qw(title x_label_skip x_labels_vertical x_tick_number x_number_format y_number_format x_min_value x_max_value y_min_value y_max_value)) {
#	    print STDERR "setting $i from -$i = " . $config{"-$i"} . "\n";
	    $graph->set("$i" => $config{"-$i"}) if ($config{"-$i"});
	}
	if ($config{'-pngparms'}) {
	    $graph->set(@{$config{'-pngparms'}});
	}
	print $graph->plot(\@pngdata);
	return $#{$pngdata[0]};
    }
    return -1;
}

sub displaytable {
    my $dbh = shift;
    my $tablename = shift;
    my %config = @_;
    my $clauses = $config{'-clauses'};
    my $dolink = $config{'-dolink'};
    my $datalink = $config{'-datalink'};
    my $beginhook = $config{'-beginhook'};
    my $modifiedhook = $config{'-modifiedhook'};
    my $endhook = $config{'-endhook'};
    my $selectwhat = $config{'-select'};
#    my $printonly = $config{'-printonly'};
    $selectwhat = "*" if (!defined($selectwhat));
    my $tableparms = $config{'-tableparms'} || $displaytable::tableparms;
    my $headerparms = $config{'-headerparms'} || $displaytable::headerparms;
    my ($thetable, $data, $ref, $prefs, $xlattable);

    if ($config{'-dontdisplaycol'}) {
	($prefs = $dbh->prepare($config{'-dontdisplaycol'}) )
	    or die "\nnot ok: $DBI::errstr\n";
    }

    # get a list of data from the table we want to display
    ( $thetable = $dbh->prepare("SELECT $selectwhat FROM $tablename $clauses"))
	or return -1;
    ( $thetable->execute )
	or return -1;

    # get a list of data from the table we want to display
    if ($config{'-xlat'}) {
	( $xlattable = 
	 $dbh->prepare("SELECT newname FROM $config{'-xlat'} where oldname = ?"))
	    or die "\nnot ok: $DBI::errstr\n";
    }
    
    # editable/markable setup
    my $edited = 0;
    my $editable = 0;
    my $markable = 0;
    my (@indexkeys, @valuekeys, $uph, %indexhash, $q);
    if (defined($config{'-editable'})) {
	$editable = 1;
    }

    if (defined($config{'-mark'}) || defined($config{'-onmarked'})) {
	$markable = 1;
    }

    if (defined($config{'-CGI'}) && ref($config{'-CGI'}) eq "CGI") {
	$q = $config{'-CGI'};
    }

    if (($editable || $markable)) {
	if (ref($config{'-indexes'}) eq ARRAY && defined($q)) {
	    @indexkeys = @{$config{'-indexes'}};
	    foreach my $kk (@indexkeys) {
		$indexhash{$kk} = 1;
	    }
	} else {
	    $editable = $markable = 0;
	    print STDERR "displaytable error: no -indexes option specified or -CGI not specified\n";
	}
    }

    if (($editable || $markable) && 
	$q->param('edited_' . toalpha($tablename))) {
	$edited = 1;
    }
	
    # table header
    my $doheader = 1;
    my @keys;
    my $rowcount = 0;
    $thetable->execute();
    if ($editable || $markable) {
	print "<input type=hidden name=\"edited_" . toalpha($tablename) . "\" value=1>\n";
    }

    while( $data = $thetable->fetchrow_hashref() ) {
	$rowcount++;
	if ($edited && $editable && !defined($uph)) {
	    foreach my $kk (keys(%$data)) {
		push (@valuekeys, maybe_from_hex($kk)) if (!defined($indexhash{$kk}));
	    }
	    my $cmd = "update $tablename set " . 
		join(" = ?, ",@valuekeys) . 
		    " = ? where " . 
			join(" = ? and ",@indexkeys) .
			    " = ?";
	    $uph = $dbh->prepare($cmd);
#	    print STDERR "setting up: $cmd<br>\n";
	}
	if ($doheader) {
	    if ($config{'-selectorder'} && 
		     ref($config{'-selectorder'}) eq "ARRAY") {
		@keys = @{$config{'-selectorder'}};
	    } elsif ($config{'-selectorder'}) {
		$_ = $selectwhat;
		@keys = split(/, */);
	    } else {
		@keys = (sort keys(%$data));
	    }
	    if (defined($config{'-title'})) {
		print "<br><b>$config{'-title'}</b>\n";
	    } elsif (!defined($config{'-notitle'})) {
		print "<br><b>";
		print "<a href=\"$ref\">" if (defined($dolink) && 
					      defined($ref = &$dolink($tablename)));
		if ($config{'-xlat'}) {
		    my $toval = $xlattable->execute($tablename);
		    if ($toval > 0) {
			print $xlattable->fetchrow_array;
		    } else {
			print "$tablename";
		    }
		} else {
		    print "$tablename";
		}
		print "</a>" if (defined($ref));
		print "</b>\n";
	    }
	    print "<br>\n";
	    print "<table $tableparms>\n";
	    if (!$config{'-noheaders'}) {
		print "<tr $headerparms>";
	    }
	    if (defined($beginhook)) {
		&$beginhook($dbh, $tablename);
	    }
	    if (!$config{'-noheaders'}) {
		if ($markable) {
		    my $ukey = to_unique_key($key, $data, @indexkeys);
		    print "<td>Mark</td>\n";
		}
		foreach $l (@keys) {
		    if (!defined($prefs) || 
			$prefs->execute($tablename, $l) eq "0E0") {
			print "<th>";
			print "<a href=\"$ref\">" if (defined($dolink) && 
						      defined($ref = &$dolink($l)));
			if ($config{'-xlat'}) {
			    my $toval = $xlattable->execute($l);
			    if ($toval > 0) {
				print $xlattable->fetchrow_array;
			    } else {
				print "$l";
			    }
			} else {
			    print "$l";
			}
			print "</a>" if (defined($ref));
			print "</th>";
		    }
		}
	    }
	    if (defined($endhook)) {
		&$endhook($dbh, $tablename);
	    }
	    if (!$config{'-noheaders'}) {
		print "</tr>\n";
	    }
	    $doheader = 0;
	}

	print "<tr>";
	if (defined($beginhook)) {
	    &$beginhook($dbh, $tablename, $data);
	}
	if ($edited && $editable) {
	    my @indexvalues = getvalues($data, @indexkeys);
	    if ($modifiedhook) {
		foreach my $valkey (@valuekeys) {
		    my ($value) = getquery($q, $data, \@indexkeys, $valkey);
		    if ($value ne $data->{$valkey}) {
			&$modifiedhook($dbh, $tablename, $valkey, 
				       $data, @indexvalues);
		    }
		}
	    }
		    
	    my $ret = $uph->execute(getquery($q, $data, \@indexkeys, @valuekeys), 
				    @indexvalues);
	    foreach my $x (@indexkeys) {
		next if (defined($indexhash{$x}));
		$data->{$x} = $q->param(to_unique_key($x, $data, @indexkeys));
	    }
#	    print "ret: $ret, $DBI::errstr<br>\n";
	}
	if ($markable) {
	    my $ukey = to_unique_key("mark", $data, @indexkeys);
	    print "<td><input type=checkbox value=Y name=\"$ukey\"" .
		(($q->param($ukey) eq "Y") ? " checked" : "") . "></td>\n";
	    if ($q->param($ukey) eq "Y" && $config{'-onmarked'}) {
		&{$config{'-onmarked'}}($dbh, $tablename, $data);
	    }
	}
	    
	foreach $key (@keys) {
	    if (!defined($prefs) || 
		$prefs->execute($tablename, $key) eq "0E0") {
		print "<td>";
		print "<a href=\"$ref\">" if (defined($datalink) && 
					      defined($ref = &$datalink($key, $data->{$key})));
		if ($editable && !defined($indexhash{$key})) {
		    my $ukey = to_unique_key($key, $data, @indexkeys);
		    my $sz;
		    if ($config{'-sizehash'}) {
			$sz = "size=" . $config{'-sizehash'}{$key};
		    }
		    if (!$sz && $config{'-inputsize'}) {
			$sz = "size=" . $config{'-inputsize'};
		    }
		    print STDERR "size $key: $sz from $config{'-sizehash'}{$key} / $config{'-inputsize'}\n";
		    print "<input type=text name=\"$ukey\" value=\"" . 
			maybe_to_hex($data->{$key}) . "\" $sz>";
		} else {
		    if ($config{'-printer'}) {
			&{$config{'-printer'}}($key, $data->{$key}, $data);
		    } elsif ($data->{$key} ne "") {
			print $data->{$key};
		    } else {
			print "&nbsp";
		    }
		}
		print "</a>" if (defined($ref));
		print "</td>";
	    }
	}

	if (defined($endhook)) {
	    &$endhook($dbh, $tablename, $data);
	}
	print "</tr>\n";
	last if (defined($config{'-maxrows'}) && 
		 $rowcount >= $config{'-maxrows'});
    }
    if ($rowcount > 0) {
	print "</table>\n";
    }
    return $rowcount;
}

sub to_unique_key {
    my $ret = shift;
    $ret .= "_";
    my $data = shift;
    if (!defined($data)) {
	$ret .= join("_",@_);
    } else {
	foreach my $i (@_) {
	    $ret .= "_" . $data->{$i};
	}
    }
    return toalpha($ret);
}

sub toalpha {
    my $ret = join("",@_);
    $ret =~ s/([^A-Za-z0-9_])/ord($1)/eg;
    return $ret;
}

sub getvalues {
    my $hash = shift;
    my @ret;
    foreach my $i (@_) {
	push @ret, maybe_from_hex($hash->{$i});
    }
    return @ret;
}

sub getquery {
    my $q = shift;
    my $data = shift;
    my $keys = shift;
    my @ret;
    foreach my $i (@_) {
	push @ret, maybe_from_hex($q->param(to_unique_key($i, $data, @$keys)));
    }
    return @ret;
}

sub maybe_to_hex {
    my $str = shift;
    if (!isprint($str)) {
	$str = "0x" . (unpack("H*", $str))[0];
    }
    $str =~ s/\"/&quot;/g;
    return $str;
}

sub maybe_from_hex {
    my $str = shift;
    if (substr($str,0,2) eq "0x") {
	($str) = pack("H*", substr($str,2));
    }
    return $str;
}

1;
__END__

=head1 NAME

SNMP - The Perl5 'SNMP' Extension Module v3.1.0 for the UCD SNMPv3 Library

=head1 SYNOPSIS

 use DBI;
 use displaytable;

 $dbh = DBI->connect(...);
 $numshown = displaytable($dbh, 'tablename', [options]);

=head1 DESCRIPTION

The displaytable and displaygraph functions format the output of a DBI
database query into an html or graph output.

=head1 DISPLAYTABLE OPTIONS

=over 4

=item -select => VALUE

Selects a set of columns, or functions to be displayed in the resulting table.

Example: -select => 'column1, column2'

Default: *

=item -title => VALUE

Use VALUE as the title of the table.

=item -notitle => 1

Don't print a title for the table.

=item -noheaders => 1

Don't print a header row at the top of the table.

=item -selectorder => 1

=item -selectorder => [qw(column1 column2)]

Defines the order of the columns.  A value of 1 will use the order of
the -select statement by textually parsing it's comma seperated list.
If an array is passed containing the column names, that order will be
used.

Example: 

  -select => distinct(column1) as foo, -selectorder => [qw(foo)]

=item -maxrows => NUM

Limits the number of display lines to NUM.

=item -tableparms => PARAMS

=item -headerparms => PARAMS

The parameters to be used for formating the table contents and the
header contents.

Defaults:

  -tableparms  => "border=1 bgcolor='#c0c0e0'"

  -headerparms => "border=1 bgcolor='#b0e0b0'"

=item -dolink => \&FUNC

If passed, FUNC(name) will be called on the tablename or header.  The
function should return a web url that the header/table name should be
linked to.

=item -datalink => \&FUNC

Identical to -dolink, but called for the data portion of the table.
Arguments are the column name and the data element for that column.

=item -printer => \&FUNC

Calls FUNC(COLUMNNAME, COLUMNDATA, DATA) to print the data from each
column.  COLUMNDATA is the data itself, and DATA is a reference to the
hash for the entire row (IE, COLUMNDATA = $DATA->{$COLUMNNAME}).

=item -beginhook => \&FUNC

=item -endhook => \&FUNC

displaytable will call these functions at the beginning and end of the
printing of a row.  Useful for inserting new columns at the beginning
or end of the table.  When the headers to the table are being printed,
they will be called like FUNC($dbh, TABLENAME).  When the data is
being printed, they will be called like FUNC($dbh, TABLENAME, DATA),
which DATA is a reference to the hash containing the row data.

Example: 

  -endhook => sub { 
      my ($d, $t, $data) = @_; 
      if (defined($data)) { 
	  print "<td>",(100 * $data->{'column1'} / $data->{'column2'}),"</td>";
      } else { 
	  print "<td>Percentage</td>"; 
      } 
  }

=item -clauses => sql_clauses

Adds clauses to the sql expression.

Example: -clauses => "where column1 = 'value' limit 10 order by column2"

=item -xlat => xlattable

Translates column headers and the table name by looking in a table for
the appropriate translation.  Essentially uses:

  SELECT newname FROM xlattable where oldname = ?

to translate everything.

=item -editable => 1

=item -indexes   => [qw(INDEX_COLUMNS)]

=item -CGI      => CGI_REFERENCE

If both of these are passed as arguments, the table is printed in
editable format.  The INDEX_COLUMNS should be a list of columns that
can be used to uniquely identify a row.  They will be the non-editable
columns shown in the table.  Everything else will be editable.  The
form and the submit button written by the rest of the script must loop
back to the same displaytable clause for the edits to be committed to
the database.  CGI_REFERENCE should be a reference to the CGI object
used to query web parameters from ($CGI_REFERENCE = new CGI);

=item -mark     => 1

=item -indexes  => [qw(INDEX_COLUMNS)]

=item -CGI      => CGI_REFERENCE

=item -onmarked => \&FUNC

When the first three of these are specified, the left hand most column
will be a check box that allows users to mark the row for future work.

FUNC($dbh, TABLENAME, DATA) will be called for each marked entry when
a submission data has been processed.  $DATA is a hash reference to
the rows dataset.  See -editable above for more information.

-onmarked => \&FUNC implies -mark => 1.

=back

=head1 Author

wjhardaker@ucdavis.edu

=cut