1 # displaytable(TABLENAME, CONFIG...):
3 # stolen from sqltohtml in the ucd-snmp package
6 package NetSNMP::manager::displaytable;
11 use vars qw(@ISA @EXPORT_OK $tableparms $headerparms);
13 @EXPORT=qw(&displaytable &displaygraph);
19 use GD::Graph::lines();
20 use GD::Graph::bars();
21 use GD::Graph::points();
22 use GD::Graph::linespoints();
23 use GD::Graph::area();
27 $tableparms="border=1 bgcolor=\"#c0c0e0\"";
28 $headerparms="border=1 bgcolor=\"#b0e0b0\"";
32 my $tablename = shift;
34 my $type = $config{'-type'} || "lines";
35 my $x = $config{'-x'} || "640";
36 my $y = $config{'-y'} || "480";
37 my $bgcolor = $config{'-bgcolor'} || "white";
38 my $datecol = $config{'-xcol'} || "updated";
39 my $xtickevery = $config{'-xtickevery'} || 50;
42 # print STDERR join(",",@_),"\n";
44 return -1 if (!defined($dbh) || !defined($tablename) ||
45 !defined ($config{'-columns'}) ||
46 ref($config{'-columns'}) ne "ARRAY" ||
47 !defined ($config{'-indexes'}) ||
48 ref($config{'-indexes'}) ne "ARRAY");
52 join(",",@{$config{'-columns'}},
53 @{$config{'-indexes'}}, $datecol) .
54 " FROM $tablename $config{'-clauses'}";
55 ( $thetable = $dbh->prepare($cmd))
57 ( $thetable->execute )
63 while( $row = $thetable->fetchrow_hashref() ) {
64 # XXX: multiple indexe columns -> unique name
65 # save all the row's data based on the index column(s)
66 foreach my $j (@{$config{'-columns'}}) {
67 if ($config{'-difference'} || $config{'-rate'}) {
68 if (defined($lastval{$row->{$config{'-indexes'}[0]}}{$j}{'value'})) {
69 $data{$row->{$config{'-indexes'}[0]}}{$row->{$datecol}}{$j}=
71 $lastval{$row->{$config{'-indexes'}[0]}}{$j}{'value'};
73 # convert to a rate if desired.
75 if ($config{'-rate'}) {
76 if (($row->{$datecol} - $lastval{$row->{$config{'-indexes'}[0]}}{$j}{'index'})) {
77 $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'});
79 $data{$row->{$config{'-indexes'}[0]}}{$row->{$datecol}}{$j} = -1;
84 $lastval{$row->{$config{'-indexes'}[0]}}{$j}{'value'} = $row->{$j};
85 $lastval{$row->{$config{'-indexes'}[0]}}{$j}{'index'} = $row->{$datecol};
87 $data{$row->{$config{'-indexes'}[0]}}{$row->{$datecol}}{$j} = $row->{$j};
91 # limit the data to a vertical range.
93 if (defined($config{'-max'}) &&
94 $data{$row->{$config{'-indexes'}[0]}}{$row->{$datecol}}{$j} >
97 $data{$row->{$config{'-indexes'}[0]}}{$row->{$datecol}}{$j} =
101 if (defined($config{'-min'}) &&
102 $data{$row->{$config{'-indexes'}[0]}}{$row->{$datecol}}{$j} <
105 $data{$row->{$config{'-indexes'}[0]}}{$row->{$datecol}}{$j} =
109 push @xdata,$row->{$datecol};
114 if (defined($config{'-createdata'})) {
115 &{$config{'-createdata'}}(\@pngdata, \@xdata, \%data);
117 push @pngdata, \@xdata;
119 my @datakeys = keys(%data);
121 # open(O,">/tmp/data");
122 foreach my $i (@datakeys) {
123 foreach my $j (@{$config{'-columns'}}) {
125 foreach my $k (@xdata) {
126 # print O "i=$i k=$k j=$j :: $data{$i}{$k}{$j}\n";
127 push @newrow, ($data{$i}{$k}{$j} || 0);
129 push @pngdata,\@newrow;
136 # create the graph itself
137 my $graph = new GD::Graph::lines($x, $y);
138 $graph->set('bgclr' => $bgcolor);
139 # print STDERR "columns: ", join(",",@{$config{'-columns'}}), "\n";
140 if (defined($config{'-legend'})) {
141 # print STDERR "legend: ", join(",",@{$config{'-legend'}}), "\n";
142 $graph->set_legend(@{$config{'-legend'}});
145 foreach my $xxx (@{$config{'-columns'}}) {
146 push @legend, "$xxx = $config{'-indexes'}[0]";
148 $graph->set_legend(@legend);
150 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)) {
151 # print STDERR "setting $i from -$i = " . $config{"-$i"} . "\n";
152 $graph->set("$i" => $config{"-$i"}) if ($config{"-$i"});
154 if ($config{'-pngparms'}) {
155 $graph->set(@{$config{'-pngparms'}});
157 print $graph->plot(\@pngdata);
158 return $#{$pngdata[0]};
165 my $tablename = shift;
167 my $clauses = $config{'-clauses'};
168 my $dolink = $config{'-dolink'};
169 my $datalink = $config{'-datalink'};
170 my $beginhook = $config{'-beginhook'};
171 my $modifiedhook = $config{'-modifiedhook'};
172 my $endhook = $config{'-endhook'};
173 my $selectwhat = $config{'-select'};
174 # my $printonly = $config{'-printonly'};
175 $selectwhat = "*" if (!defined($selectwhat));
176 my $tableparms = $config{'-tableparms'} || $displaytable::tableparms;
177 my $headerparms = $config{'-headerparms'} || $displaytable::headerparms;
178 my ($thetable, $data, $ref, $prefs, $xlattable);
180 if ($config{'-dontdisplaycol'}) {
181 ($prefs = $dbh->prepare($config{'-dontdisplaycol'}) )
182 or die "\nnot ok: $DBI::errstr\n";
185 # get a list of data from the table we want to display
186 ( $thetable = $dbh->prepare("SELECT $selectwhat FROM $tablename $clauses"))
188 ( $thetable->execute )
191 # get a list of data from the table we want to display
192 if ($config{'-xlat'}) {
194 $dbh->prepare("SELECT newname FROM $config{'-xlat'} where oldname = ?"))
195 or die "\nnot ok: $DBI::errstr\n";
198 # editable/markable setup
202 my (@indexkeys, @valuekeys, $uph, %indexhash, $q);
203 if (defined($config{'-editable'})) {
207 if (defined($config{'-mark'}) || defined($config{'-onmarked'})) {
211 if (defined($config{'-CGI'}) && ref($config{'-CGI'}) eq "CGI") {
212 $q = $config{'-CGI'};
215 if (($editable || $markable)) {
216 if (ref($config{'-indexes'}) eq ARRAY && defined($q)) {
217 @indexkeys = @{$config{'-indexes'}};
218 foreach my $kk (@indexkeys) {
222 $editable = $markable = 0;
223 print STDERR "displaytable error: no -indexes option specified or -CGI not specified\n";
227 if (($editable || $markable) &&
228 $q->param('edited_' . toalpha($tablename))) {
236 $thetable->execute();
237 if ($editable || $markable) {
238 print "<input type=hidden name=\"edited_" . toalpha($tablename) . "\" value=1>\n";
241 while( $data = $thetable->fetchrow_hashref() ) {
243 if ($edited && $editable && !defined($uph)) {
244 foreach my $kk (keys(%$data)) {
245 push (@valuekeys, maybe_from_hex($kk)) if (!defined($indexhash{$kk}));
247 my $cmd = "update $tablename set " .
248 join(" = ?, ",@valuekeys) .
250 join(" = ? and ",@indexkeys) .
252 $uph = $dbh->prepare($cmd);
253 # print STDERR "setting up: $cmd<br>\n";
256 if ($config{'-selectorder'} &&
257 ref($config{'-selectorder'}) eq "ARRAY") {
258 @keys = @{$config{'-selectorder'}};
259 } elsif ($config{'-selectorder'}) {
261 @keys = split(/, */);
263 @keys = (sort keys(%$data));
265 if (defined($config{'-title'})) {
266 print "<br><b>$config{'-title'}</b>\n";
267 } elsif (!defined($config{'-notitle'})) {
269 print "<a href=\"$ref\">" if (defined($dolink) &&
270 defined($ref = &$dolink($tablename)));
271 if ($config{'-xlat'}) {
272 my $toval = $xlattable->execute($tablename);
274 print $xlattable->fetchrow_array;
281 print "</a>" if (defined($ref));
285 print "<table $tableparms>\n";
286 if (!$config{'-noheaders'}) {
287 print "<tr $headerparms>";
289 if (defined($beginhook)) {
290 &$beginhook($dbh, $tablename);
292 if (!$config{'-noheaders'}) {
294 my $ukey = to_unique_key($key, $data, @indexkeys);
295 print "<td>Mark</td>\n";
298 if (!defined($prefs) ||
299 $prefs->execute($tablename, $l) eq "0E0") {
301 print "<a href=\"$ref\">" if (defined($dolink) &&
302 defined($ref = &$dolink($l)));
303 if ($config{'-xlat'}) {
304 my $toval = $xlattable->execute($l);
306 print $xlattable->fetchrow_array;
313 print "</a>" if (defined($ref));
318 if (defined($endhook)) {
319 &$endhook($dbh, $tablename);
321 if (!$config{'-noheaders'}) {
328 if (defined($beginhook)) {
329 &$beginhook($dbh, $tablename, $data);
331 if ($edited && $editable) {
332 my @indexvalues = getvalues($data, @indexkeys);
334 foreach my $valkey (@valuekeys) {
335 my ($value) = getquery($q, $data, \@indexkeys, $valkey);
336 if ($value ne $data->{$valkey}) {
337 &$modifiedhook($dbh, $tablename, $valkey,
338 $data, @indexvalues);
343 my $ret = $uph->execute(getquery($q, $data, \@indexkeys, @valuekeys),
345 foreach my $x (@indexkeys) {
346 next if (defined($indexhash{$x}));
347 $data->{$x} = $q->param(to_unique_key($x, $data, @indexkeys));
349 # print "ret: $ret, $DBI::errstr<br>\n";
352 my $ukey = to_unique_key("mark", $data, @indexkeys);
353 print "<td><input type=checkbox value=Y name=\"$ukey\"" .
354 (($q->param($ukey) eq "Y") ? " checked" : "") . "></td>\n";
355 if ($q->param($ukey) eq "Y" && $config{'-onmarked'}) {
356 &{$config{'-onmarked'}}($dbh, $tablename, $data);
360 foreach $key (@keys) {
361 if (!defined($prefs) ||
362 $prefs->execute($tablename, $key) eq "0E0") {
364 print "<a href=\"$ref\">" if (defined($datalink) &&
365 defined($ref = &$datalink($key, $data->{$key})));
366 if ($editable && !defined($indexhash{$key})) {
367 my $ukey = to_unique_key($key, $data, @indexkeys);
369 if ($config{'-sizehash'}) {
370 $sz = "size=" . $config{'-sizehash'}{$key};
372 if (!$sz && $config{'-inputsize'}) {
373 $sz = "size=" . $config{'-inputsize'};
375 print STDERR "size $key: $sz from $config{'-sizehash'}{$key} / $config{'-inputsize'}\n";
376 print "<input type=text name=\"$ukey\" value=\"" .
377 maybe_to_hex($data->{$key}) . "\" $sz>";
379 if ($config{'-printer'}) {
380 &{$config{'-printer'}}($key, $data->{$key}, $data);
381 } elsif ($data->{$key} ne "") {
387 print "</a>" if (defined($ref));
392 if (defined($endhook)) {
393 &$endhook($dbh, $tablename, $data);
396 last if (defined($config{'-maxrows'}) &&
397 $rowcount >= $config{'-maxrows'});
409 if (!defined($data)) {
410 $ret .= join("_",@_);
413 $ret .= "_" . $data->{$i};
416 return toalpha($ret);
420 my $ret = join("",@_);
421 $ret =~ s/([^A-Za-z0-9_])/ord($1)/eg;
429 push @ret, maybe_from_hex($hash->{$i});
440 push @ret, maybe_from_hex($q->param(to_unique_key($i, $data, @$keys)));
447 if (!isprint($str)) {
448 $str = "0x" . (unpack("H*", $str))[0];
450 $str =~ s/\"/"/g;
456 if (substr($str,0,2) eq "0x") {
457 ($str) = pack("H*", substr($str,2));
466 SNMP - The Perl5 'SNMP' Extension Module v3.1.0 for the UCD SNMPv3 Library
473 $dbh = DBI->connect(...);
474 $numshown = displaytable($dbh, 'tablename', [options]);
478 The displaytable and displaygraph functions format the output of a DBI
479 database query into an html or graph output.
481 =head1 DISPLAYTABLE OPTIONS
485 =item -select => VALUE
487 Selects a set of columns, or functions to be displayed in the resulting table.
489 Example: -select => 'column1, column2'
493 =item -title => VALUE
495 Use VALUE as the title of the table.
499 Don't print a title for the table.
501 =item -noheaders => 1
503 Don't print a header row at the top of the table.
505 =item -selectorder => 1
507 =item -selectorder => [qw(column1 column2)]
509 Defines the order of the columns. A value of 1 will use the order of
510 the -select statement by textually parsing it's comma seperated list.
511 If an array is passed containing the column names, that order will be
516 -select => distinct(column1) as foo, -selectorder => [qw(foo)]
518 =item -maxrows => NUM
520 Limits the number of display lines to NUM.
522 =item -tableparms => PARAMS
524 =item -headerparms => PARAMS
526 The parameters to be used for formating the table contents and the
531 -tableparms => "border=1 bgcolor='#c0c0e0'"
533 -headerparms => "border=1 bgcolor='#b0e0b0'"
535 =item -dolink => \&FUNC
537 If passed, FUNC(name) will be called on the tablename or header. The
538 function should return a web url that the header/table name should be
541 =item -datalink => \&FUNC
543 Identical to -dolink, but called for the data portion of the table.
544 Arguments are the column name and the data element for that column.
546 =item -printer => \&FUNC
548 Calls FUNC(COLUMNNAME, COLUMNDATA, DATA) to print the data from each
549 column. COLUMNDATA is the data itself, and DATA is a reference to the
550 hash for the entire row (IE, COLUMNDATA = $DATA->{$COLUMNNAME}).
552 =item -beginhook => \&FUNC
554 =item -endhook => \&FUNC
556 displaytable will call these functions at the beginning and end of the
557 printing of a row. Useful for inserting new columns at the beginning
558 or end of the table. When the headers to the table are being printed,
559 they will be called like FUNC($dbh, TABLENAME). When the data is
560 being printed, they will be called like FUNC($dbh, TABLENAME, DATA),
561 which DATA is a reference to the hash containing the row data.
566 my ($d, $t, $data) = @_;
567 if (defined($data)) {
568 print "<td>",(100 * $data->{'column1'} / $data->{'column2'}),"</td>";
570 print "<td>Percentage</td>";
574 =item -clauses => sql_clauses
576 Adds clauses to the sql expression.
578 Example: -clauses => "where column1 = 'value' limit 10 order by column2"
580 =item -xlat => xlattable
582 Translates column headers and the table name by looking in a table for
583 the appropriate translation. Essentially uses:
585 SELECT newname FROM xlattable where oldname = ?
587 to translate everything.
591 =item -indexes => [qw(INDEX_COLUMNS)]
593 =item -CGI => CGI_REFERENCE
595 If both of these are passed as arguments, the table is printed in
596 editable format. The INDEX_COLUMNS should be a list of columns that
597 can be used to uniquely identify a row. They will be the non-editable
598 columns shown in the table. Everything else will be editable. The
599 form and the submit button written by the rest of the script must loop
600 back to the same displaytable clause for the edits to be committed to
601 the database. CGI_REFERENCE should be a reference to the CGI object
602 used to query web parameters from ($CGI_REFERENCE = new CGI);
606 =item -indexes => [qw(INDEX_COLUMNS)]
608 =item -CGI => CGI_REFERENCE
610 =item -onmarked => \&FUNC
612 When the first three of these are specified, the left hand most column
613 will be a check box that allows users to mark the row for future work.
615 FUNC($dbh, TABLENAME, DATA) will be called for each marked entry when
616 a submission data has been processed. $DATA is a hash reference to
617 the rows dataset. See -editable above for more information.
619 -onmarked => \&FUNC implies -mark => 1.
625 wjhardaker@ucdavis.edu