displaytable.pm
上传用户:wxp200602
上传日期:2007-10-30
资源大小:4028k
文件大小:17k
- # 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::errstrn";
- }
- # 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::errstrn";
- }
-
- # 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 specifiedn";
- }
- }
- 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 " ";
- }
- }
- 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/"/"/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