[ic] Donation: report-table UserTag

Christopher Wenham cwenham at synesmedia.com
Sun Nov 16 04:23:09 EST 2003

 I kept needing a good way to generate flexible report tables that 
could handle adding styles, filters, form widgets and links to the cells. 
I also needed a way to add automatic sub-headers that were either 
horizontal (colspan) or vertical (rowspan).

 So I took a shot at developing one that did just about everything I could
think of. You can have it apply any interchange filter to a column, or
any interchange widget, so it's good for making quick and flexible forms 
as well as reports.

UserTag report-table addAttr
UserTag report-table Documentation <<EOD

By Chris Wenham of Synesmedia, Inc. - www.synesmedia.com
This software is distributed under the terms of the GNU Public License.
Version 1.0, November 16, 2003.

Generate an HTML table based on the results of a query, with bells and
whistles. Can do horizontal (colspan) and vertical (rowspan) subheaders,
apply any Interchange filter or widget to any column, add a CSS class to
any column, link cell contents (and add parameters to the link based on
any column in the query results), add virtual columns based on internal
variables (such as the line number), and skip rows based on an array of
toggles you specify.

Synopsis and minimum syntax

		query="SELECT * FROM addresses"
		columns="address city state zip"

Or something fancier:

	<form action="[process]">
		query="SELECT * FROM addresses"
		columns="state city address sales"
			state => {
				header => 'vert',

			city => {
				header => 'vert',

			zip => {
				title  => "Zip code:",
				header => 'horiz',

			address => {
				widget => 'text',

			sales => {
				prefix => '$',
	  <td colspan="4" align="right">
	    <input type="hidden" name="rows" value="[scratch report_table_linecount]"/>
	    <input type="submit" value="Save addresses"/>

This last example could give you something like this:

 | state | city      | address                | sales    |
 |  NY   | Levittown |          Zip code: 11756          |
 |       |           |-----------------------------------|
 |       |           | [123 Return Lane____]  | $240.12  |
 |       |           | [321 Raspberry Lane_]  | $43.52   |
 |       |-----------+-----------------------------------|
 |       | Bellmore  |          Zip code: 11710          |
 |       |           |-----------------------------------|
 |       |           | [23 Merrick Road____]  | $354.06  |
 |       |           | [43 Bellmore Ave____]  | $11.34   |
 |  PA   | Anytown   |          Zip code: 23456          |
 |       |           |-----------------------------------|
 |       |           | [63 Some Street_____]  | $771.35  |
 |                                    [ Save addresses ] |

The columns to include in the report are passed in the "columns"
tag parameter.

Column definitions are defined in a perl hash of hash references.
The tag will display only the columns you specify, and in that order.
Pagination is not supported, but you can easily construct the logic for
that outside of the report-table tag, and then use OFFSET and LIMIT in
the query.

Vertical headers (state and city in this example) are always sorted
to the left of the table, but they can be nested to any level. The tag
does not yet support vertical headers within the scope of a horizontal

Horizontal headers can also be nested to any level. You might want to
pass a "class" value in the column definition so you can style them
later and make it easier to tell them apart.
NOTE: Columns used for horizontal headers must *not* be included in the
"columns" parameter of the report-table tag. Horizontal

Advanced column definitions

The following parameters are supported for the column definitions.

	title => 'Column Header'
	The tag will default to the database column name, but you
	can override it with a title. All titles are put in <th>
	tags at the top of each column, or in the case of
	horizontal subheaders they're put just before the value
	(eg: "Zip code: 11756" from above)

	header => 'vert'
	Indicates that this column is a header, and whether it's
	vertical ('vert') or horizontal ('horiz').
	Headers are generated every time the value in that column
	changes between rows. Let's say that the following are
	the rows returned by the query:

	NY,Levittown,11756,123 Return Lane
	NY,Levittown,11756,321 Raspberry Lane
	NY,Bellmore,11710,23 Merrick Road

	If city was a header, then it would spit out "Levittown"
	first, then two rows later spit out "Bellmore".

	NOTE: To make headers work properly, you must sort by those
	columns in your query, or you may get redundant headers.

	prefix => '$'
	Something to insert just before the value. Will appear
	after the title in a horizontal header, and before any widget
	or link.

	postfix => '%'
	Similar to the above, but placed to the right of the value.

	filter => 'digits_dot'
	Any Interchange filter. Will be applied to the cell value
	before it's put into any link or widget.

	widget => 'date'
	Any Interchange form widget. The widget will be passed the
	contents of the cell as the default value. The name of the
	form widget will be the column name plus the line number.
	Eg: "address_1", "address_2", and so-on.
	You can pass any addtional parameter supported by the [widget]
	tag (such as rows and cols) by prefixing them with "widget_".
	EG: "widget_cols => '30'".

	Any column can be a widget, even vertical and horizontal

	class => 'currency'
	Will give you <td class="currency"> for each cell in that

	link      => 'show_customer'
	link_parm => 'id'
	link_key  => 'cust_id'
	Link a cell's contents using Interchange's [page] tag, and
	optionally passing a parameter based on any column in the
	query results. So let's say "cust_id" is a column returned in
	the database query, but not actually displayed in the result.
	The cells in your customer column could be linked to the
	"show_customer" page, passing the value of "cust_id" in a
	parameter named "id". Like this:

	NOTE: You can't use a link and a widget at the same time. If you
	set the 'link' parameter, any widget in the same column def will
	be ignored.

	emtpy => '&nbsp;'
	What to use instead if the cell is empty for that row. For
	tables with borders set, you might want to use a nonbreaking
	space (&nbsp;), or 0.00 for currency columns, or whatever.
	NOTE: The tag can't tell the difference between an empty cell
	and a NULL cell.

	dynamic => 'linecount'
	Indicates a column that does not draw its data from the query
	results, but from an internal variable. Most of these aren't
	terribly useful, but 'linecount' is good for adding line numbers.
	Dynamic variables can be used with links, widgets and filters,
	but they can't be used as subheaders.
	Available dynamic values are:

		The absolute current row from the query results. Is not
		affected by the row_toggle parameter (described later).
		Begins at zero.

		The current row, including any used by horizontal
		Begins at zero.

		The current data line. Does not include lines used by
		horizontal subheaders.
		Begins at 1.

		1 if we're on an odd numbered line, 0 if we're on an
		even numbered line.

Other parameters

	This is a comma separated list of toggles ('1' or '0') that
	can be used to make the report skip individual rows in the
	results. The number of toggles must either equal the number
	of results from the query.
	Eg: passing row_toggle="1,1,0,1,1,1" and a query that returns
	six rows will give you a five-row report, where the third
	row from the results had been skipped. If the query returns
	more than six rows, then the remainder will be skipped.

	(Ideally, what you should probably do is just modify your
	query so it doesn't return those rows anyway, but this feature
	was added for a special application.)

	If you want the value of horizontal subheaders to stand on
	their own (without a title), then set title_horiz="0".
	Otherwise the tag will use the database name or title of
	the column.

	By default, the scope of a horizontal header does not cross
	the scope of a vertical header. It looks confusing and
	doesn't follow the typical way subheaders are used. So when
	a vertical header goes out of scope, it resets all the
	horizontal headers so they begin anew with the next row.
	Example: Some zip codes cross city boundaries, so the
	"Levittown" vertical header could end, but the next address
	might still be in the "11756" zip code. By default, the
	report table will simply run the "Zip code: 11756" header
	again before the next row.
	If you don't want it to do this, meaning you want the scope of
	horizontal headers to cross the scope of vertical headers,
	then pass reset_horiz="0".

	Don't bother to display the column headers.

	no_results="<tr><td>Woah dude, nothing to see!</td></tr>"
	Override the default message when there are no results from
	the query.

HTML output

This tag will not generate the <table> tags in the final HTML because
it's trivial to add those yourself, and it was designed to be used in
cases where the table might not be "finished" even when the report-table
tag was (such as when you're using it to create a form).

The column headers row will be written with <tr class="headers">.

Every odd-numbered row will be written with <tr parity="odd">, which
most browsers will let you style with a CSS selector like:

tr[pos='odd'] {
	background-color: yellow;

The total number of columns it will use will always be the same as what
you pass in the "columns" parameter. Even when the query returns no
results, it will still return one complete row with an apropriate
colspan (unless overridden by the no_results parameter).


The following temporary scratch variables are set prior to tag completion.

	[scratch report_table_rowcount]
	The total number of rows created by the tag. This includes rows
	used up by horizontal subheaders, and the column header row.

	[scratch report_table_linecount]
	Total number of data rows returned by the tag, NOT including rows
	used by horizontal subheaders or the column headers. Useful if
	you're using widgets and your mv_nextpage needs to know how many
	values there are.


To get a blank column:

	columns="city state zip x customer"
		x => {
			title      => '&nbsp;',
			empty_cell => '&nbsp;'

UserTag report-table Routine <<EOR
sub prep_cell {
	my ($def,$datum,$linecount,$record) = @_;

	my $cell;
	if ($def->{filter}) {
		$datum = $Tag->filter({ op => $def->{filter}, }, $datum);

	if ($def->{link}) {
		my $page_parms = { href => $def->{link}, };
		if ($def->{link_parm}) {
		  $page_parms->{form} = $def->{link_parm} .'='. $record->{$def->{link_key}};
		$cell = $Tag->page($page_parms);
		$cell .= $datum;
		$cell .= '</a>';
	} elsif ($def->{widget}) {
		if ($def->{widget} =~ /^checkonly$/) {
			my $checked = '';
			if ($datum) {
				$checked = ' CHECKED';
			$cell = '<input type="checkbox" name="'. $def->{colname} .'_'. $linecount ."\" value=\"1\"$checked/>";
		} else {
			$cell = $Tag->widget($def->{colname} .'_'. $linecount, {
				type       => $def->{widget},
				default    => $datum,
				attribute  => $def->{widget_attribute},
				db         => $def->{widget_db},
				field      => $def->{widget_field},
				extra      => $def->{widget_extra},
				cols       => $def->{widget_cols},
				rows       => $def->{widget_rows},
				delimiter  => $def->{widget_delimiter},
				key        => $def->{widget_key},
				year_begin => $def->{widget_year_begin},
				year_end   => $def->{widget_year_end},
				filter     => $def->{widget_filter},
				set        => $def->{widget_set},
	} else {
		$cell = $datum;

	$cell = $def->{prefix} . $cell . $def->{postfix};

	return $cell;

sub {
	#Debug("Entering report-table");
	# Options gathering ------------------------------------------
	my $opt = shift;

	my @columns           = split ' ', $opt->{columns};
	my @row_toggle        = split ',', $opt->{row_toggle};

	if ($opt->{reset_horiz} eq '') {
		$opt->{reset_horiz} = 1;

	if ($opt->{title_horiz} eq '') {
		$opt->{title_horiz} = 1;

	if ($opt->{colheaders} eq '') {
		$opt->{colheaders} = 1;

	#Debug("Gathered options. Query is: ". $opt->{query});

	# Data structure preparation ---------------------------------
	my @vertheads = ();
	my @subheader_cols = ();

	my (%cols,$column_defs);
	if ($opt->{column_defs}) {
		$column_defs = eval( $opt->{column_defs} );
		%cols = %{$column_defs};
	} else {
		foreach my $col (@columns) {
			$cols{$col}->{title} = $col;

	my @tcols;
	my $headpos = 0;
	foreach my $col (@columns) {
		if ($cols{$col}->{header}) {
			$cols{$col}->{pos} = $headpos;
			push @subheader_cols, $col;
			push @vertheads, $col;
		} else {
			push @tcols, $col;
	foreach my $col (keys(%cols)) {
		$cols{$col}->{colname} = $col;
		$cols{$col}->{title} ||= $col;
		if ($cols{$col}->{header} =~ /horiz/) {
			push @subheader_cols, $col;
	@columns = @tcols;
	# ----------------------------------------------------------##

	my $output;
	my $db = ::database_exists_ref('products');
	my $results = $db->query({ sql => $opt->{query}, hashref => 'results' });

	# Output column headers --------------------------------------
	if (($results) and (@{$results}) and ($opt->{colheaders})) {
		$output .= '<tr class="headers">';

		foreach my $c (@vertheads) {
			$output .= "<th>$cols{$c}->{title}</th>";
		foreach my $c (@columns) {
			$output .= "<th>$cols{$c}->{title}</th>";
		$output .= "</tr>\n";

	if (!(($results) and (@{$results}))) {
		return $opt->{no_results} || '<tr><td colspan="'. (scalar(@columns) + scalar(@vertheads)) .'">No results</td></tr>';
	# ----------------------------------------------------------##

	# Process results --------------------------------------------
	my @rows = ();
	my @vh_stack = ();   # Stack of vertical headers we're working on
	my $vh;
	my $rowcount = 0;
	my $linecount = 1;
	for (my $i = 0; $i < scalar(@{$results}); $i++) {
		if (@row_toggle) {
			next if !$row_toggle[$i];
		my $record = $results->[$i];
		my $row;

		# Dynamic values that can be used as column data
		my %dynamic = (
			realrow    => $i,
			rowcount   => $rowcount,
			rownumber  => $linecount,
			linecount  => $linecount,
			parity     => ($linecount + 1) % 2 ? 1 : 0,

		$row->{dynamic} = \%dynamic;

		foreach my $subhead (@subheader_cols) {
			if ($record->{$subhead} ne $cols{$subhead}->{value}) {
			  if ($cols{$subhead}->{header} ne 'vert') {
				my @tag_parms;
				push @tag_parms, 'colspan="'. ($#columns + 1) .'"';
				push @tag_parms, "class=\"$cols{$subhead}->{class}\"" if $cols{$subhead}->{class};
				$row->{html} = '<th '. join(' ', @tag_parms) .'>';
				if ($opt->{title_horiz}) {
					$row->{html} .= $cols{$subhead}->{title} .' ';
				my $datum = $record->{$subhead};
				$row->{html} .= prep_cell($cols{$subhead},$datum,$linecount,$record) .'</th>';
				$cols{$subhead}->{value} = $record->{$subhead};
			  } else {
			  	# Vertical headers must be inserted at the end, because that's
				# the only time we know what the rowspan is going to be.
				# So we keep track of them with a stack and a notation in the
				# row hash.
				my $old;
				if ($cols{$vh->{column}}->{pos} >= $cols{$subhead}->{pos}) {
				  while (($old->{column} ne $subhead) and (@vh_stack)) {
					$old = pop @vh_stack;
					$old->{end} = $rowcount;
					$cols{$old->{column}}->{value} = '';
					#::Debug("Popped vh_stack. Old is: ". ::uneval($old));
				if ($opt->{reset_horiz}) {
					# Don't let horizontal headers apply across vertical headers
					foreach my $tmp (@subheader_cols) {
						if ($cols{$tmp}->{header} eq 'horiz') {
							$cols{$tmp}->{value} = '';
				my $datum = $record->{$subhead};
				my $new = {
					content => prep_cell($cols{$subhead},$datum,$linecount,$record),
					column => $subhead,
					begin => $rowcount,
				push @vh_stack, $new;
				#::Debug("vh_stack now: ". ::uneval(\@vh_stack));
				unshift @{$row->{'vert_headers'}}, $new;
				$cols{$subhead}->{value} = $record->{$subhead};
				$vh = $new;
			  if ($row->{html}) {
				push @rows, $row;
				my %newrow = ();
				$row = \%newrow;
		foreach my $col (@columns) {
			my @tag_parms;
			if ($cols{$col}->{'class'}) {
				push @tag_parms, "class=\"$cols{$col}->{'class'}\"";
			if (@tag_parms) {
				$row->{html} .= '<td '. join( ' ', @tag_parms) .'>';
			} else {
				$row->{html} .= '<td>';

			my $datum;
			if ($cols{$col}->{dynamic}) {
				$datum = $dynamic{$cols{$col}->{dynamic}};
			} else {
				$datum = $record->{$col};
			if ((!$datum) and ($cols{$col}->{empty_cell})) {
				$datum = $cols{$col}->{empty_cell};

			$row->{html} .= prep_cell($cols{$col},$datum,$linecount,$record);

			$row->{html} .= '</td>';

		push @rows, $row;
	# ----------------------------------------------------------##

	# Do post-processing table assembly --------------------------
	foreach my $row (@rows) {
		my $html = $row->{'html'};
		if ($row->{'vert_headers'}) {
			foreach my $vert (@{$row->{'vert_headers'}}) {
				my $end = $vert->{end} || $rowcount;
				my @parms = ('valign="top"');
				push @parms, 'rowspan="'. ($end - $vert->{begin}) .'"';
				if ($cols{$vert->{column}}->{class}) {
					push @parms, 'class="'. $cols{$vert->{column}}->{class} .'"';
				my $cell = '<th '. join(' ', @parms) .'>';
				$cell .= $vert->{content};
				$cell .= '</th>';
				$html = $cell . $html;
		my $odd = '';
		if ($row->{dynamic}->{parity}) {
			$odd = ' parity="odd"';
		$output .= "<tr$odd>$html</tr>\n";
	# ----------------------------------------------------------##

	# Set some side-effect scratch variables
	if ($opt->{colheaders}) { $rowcount++; }
	$Tag->tmp('report_table_linecount',$linecount - 1);
	$Tag->tmp('report_table_colspan',(scalar(@columns) + scalar(@vertheads)));

	return $output;

Chris Wenham - Synesmedia, Inc.
516-620-4110 / 1-888-255-7573
Fax: 516-908-7824

More information about the interchange-users mailing list