view release on metacpan or  search on metacpan

ReportWriter.pm  view on Meta::CPAN

=over

=item DBH
A database handle that has connected to the database that the report is to be run against.

=item SQL_FRAGMENT
An SQL fragment starting from the FROM clause, continued through the end of the where clause. In
the case of MySQL and/or other databases that support them, GROUP BY and HAVING clauses may also be
added to the SQL fragment.

=item COLUMNS
Column definitions for what is to be selected. A column definition consists of one of two formats, either
a simple one-element array reference or an array reference of hash references containing the following four
elements:

 get - the string used in the get variable to determine the sorted column
 sql - the sql statement that will select the data from the database
 display - What should be displayed in the column's table header
 sortable - whether or not a sorting link should be generated for the column
 order - (optional) sql that will be used to order by the specified column. If not present, then the value of sql is used
 group - (optional) true or false. If true, the column will be grouped after the results are retrieved.
 hide_column - (optional) true or false. If true, the column will not be drawn to the screen.  This is useful if you
                want to pull data for some other column's draw_func.
 draw_func - (optional) code ref, may not be used if group is true. code ref to a function which takes two parameters.
             param 1: the data from the column being rendered
             param 2: the hashref containing the data from the rest of the row
             returns: the string that should be displayed as the cell contents when that column is drawn

These definitions can be arbitrarily complex. For example:

 COLUMNS => [
     {
         get => 'username',
         sql => 'jp.username',
         display => 'Username',
         sortable => 1,
         group => 1,
     },
     {
         get => 'date',
         sql => 'DATE_FORMAT(l.created, \'%m/%e/%Y\') AS date',
         display => 'Date',
         sortable => 1,
         order => 'l.created',
     },
     {
         get => 'type',
         sql => "IF(l.deleted = 'yes', 'delete', 'add') AS type",
         display => 'Type',
         sortable => 1,
     },
     {
         sql => "l.id",
         hide_column => 1,
     },
     {
         get => 'successful',
         sql => "l.successful",
         display => 'Successful',
         sortable => 1,
         draw_func => sub { my ($data, $cols) = @_; my $lid = $cols->{'id'}; my $color = ($data eq 'no' ? 'red' : ($data eq 'yes' ? 'green' : 'black')); return "<a style=\"color: $color;\" href=\"#\" onClick=\"popup('/cgi-bin/reports/message.cgi?id=$...
     },
 ]

and

 COLUMNS => [ 'name', 'address', 'age' ]

are both valid definitions. Additionally, you can combine scalar and hashref-filled arrayrefs, like

 COLUMNS => [
     'name',
     'age',
     {
         get => 'birthday',
         sql => 'DATE_FORMAT(birthday, \'%m/%e/%Y\') AS birthday',
         display => 'Birthday',
         sortable => 1,
         order => 'birthday',
     },
 ]


If you are going to use complex sql structures in a column definiton (for example, the
DATE_FORMAT and IF statements above), it is STRONGLY recommended that you use a column alias (for example, the
'AS date' in the date column example) in order to ensure proper functionality. This module has not been tested
with unaliased complex sql column definitions.

NOTE: If you use formatting that would change a numeric-type column into a string-type column (for example the
date columns above), you should use the order attribute to ensure proper ordering. For example using DATE_FORMAT
as shown above results in the integer-style date column being treated as a string (20041010120000 becomes 
'10-10-2004'), which would cause '10-10-2004' to sort before '10-02-2004'. draw_func is intended to provide you
with a simple alternative to things like DATE_FORMAT -- you can now do the formatting outside the SQL.

=item DEBUG
Will cause useful debugging messages to be printed using the warn facility. default: 0

=item COLUMN_SORT_DEFAULT
If the simplified version of the COLUMNS definition is used (COLUMNS => [ 'foo', 'bar' ]), then this variable
determines whether the table header will allow sorting of any columns. It is global in scope; that is, either
every column is sortable, or every column is not. If the hashref method is used to define columns, this variable
will be ignored.

=item MYSQL_MAJOR_VERSION
Currently either 3, 4 or 5. Determines which method of determining num_results is used. In MySQL 4 a new method
was added which makes the process much more efficient. Defaults to 4 since it's been the stable release for well
over a year.

=item CGI_OBJECT
A handle to a CGI object. Since it is very unlikely that a report will ever be just a static report with no
user interaction, it is assumed that the coder will want to instantiate their own CGI object in order to allow
the user to interact with the report. Use of this argument will prevent needless creation of additional CGI objects.

=item PAGE_TITLE
The title of the current page. Defaults to "HTML::ReportWriter v${VERSION} generated report".

=item CSS
The CSS style applied to the page. Can be an external stylesheet reference or an inline style. Has a default inline
style that I won't waste space listing here.

=item HTML_HEADER

ReportWriter.pm  view on Meta::CPAN

=item *
PagingAndSorting was developed during my employ at HRsmart, Inc. L<http://www.hrsmart.com> and its
public release was graciously approved.

=item *
Robert Egert was an early adopter, and made signifigant contributions in the form of suggestions and
bug reports.

=item *
Mark Stosberg made several contributions including documentation corrections and PostgreSQL support.

=item *
Steven Mackenzie contributed a patch to add support for SQLite.

=back

=head1 COPYRIGHT

Copyright 2004, Shane Allen. All rights reserved.

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

=cut

__DATA__
[% IF doctype -%]
[% doctype %]
[% END -%]
<html>
<head>
<title>[% page_title %]</title>
[% css %]
</head>
<body>
[% html_header %]
<center>
<table border="0" width="[% report_table_width %]">
<tr><td>
<table id="idtable" border="0" cellspacing="0" cellpadding="4" width="100%">
[% sorting %]
[%- IF results.size < 1 %]
<tr><td colspan="[% fields.size %]" align="center">[% no_results %]</td></tr>
[%- ELSE %]
    [%- FOREACH x = results %]
        [% draw_row(fields, x, row_counter, 1, 0, 0) %]
    [%- END %]
[%- END %]
</table>
</td></tr>
<tr><td>
<table border="0" width="100%">
<tr>
<td width="75%"></td><td width="25%">[% paging %]</td>
</tr>
</table>
</td></tr>
</table>
</center>
[%- IF(export_link) %]
<script language="JavaScript">
function export_popup(url)
{
            window.open (url,'filedl','toolbar=yes,location=no,directories=no,status=no,menubar=yes,scrollbars=yes,resizable=yes,copyhistory=no,width=300,height=400,screenX=0,screenY=0,top=0,left=0');
}
</script>
<p align="center">[% export_data_to %]: [ <a href="#" onClick="javascript:export_popup('[% export_link %]'); return false;">[% export_value %]</a> ]</p>
[%- END %]
<br /><br />
[% html_footer %]
</body>
</html>