The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
HTML-ReportWriter-1.5.1
River stage zero No dependents
++
/ HTML::ReportWriter::PagingAndSorting

NAME

HTML::ReportWriter::PagingAndSorting - Contains logic for paging/sorting function of ReportWriter

SYNOPSIS

Example script:

 #!/usr/bin/perl -w

 use strict;
 use HTML::ReportWriter::PagingAndSorting;
 use CGI;
 use Template;
 use DBI;

 my $template = Template->new( { INCLUDE_PATH => '/templates' } );
 my $co = new CGI;
 my $paging = HTML::ReportWriter::PagingAndSorting->new({
             CGI_OBJECT => $co,
             DEFAULT_SORT => 'date',
             SORTABLE_COLUMNS => [
                 {
                     get => 'name',
                     sql => 'people.name',
                     display => 'Full Name',
                     sortable => 0,
                 },
                 {
                     get => 'age',
                     sql => 'people.age',
                     display => 'Age (in years)',
                     sortable => 1,
                 },
             ],
 });

 my $dbh = DBI->connect('DBI:mysql:foo', 'bar', 'baz');

 my $sql = "SELECT SQL_CALC_FOUND_ROWS id, name, age FROM people";

 my $sort = $paging->get_mysql_sort();
 my $limit = $paging->get_mysql_limit();

 my $sth = $dbh->prepare("$sql $sort $limit");
 $sth->execute();
 my ($count) = $dbh->selectrow_array('SELECT FOUND_ROWS() AS num');

 $paging->num_results($count);

 while(my $href = $sth->fetchrow_hashref)
 {
     push @{$vars{'results'}}, $href;
 }
 $vars{'sorting'} = $paging->get_sortable_table_header();
 $vars{'paging'} = $paging->get_paging_table();

 print $co->header;
 $template->process('display.html', \%vars);

Example template (display.html in the above example):

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
 <html>
 <head>
 <title>Simple Report</title>
 <link rel="STYLESHEET" type="text/css" href="/style.css" xmlns="">
 </head>
 <body>
 [% rowcounter = 1 %]
 <center>
 <table border="0" width="800">
 <tr><td>
 <table id="idtable" border="0" cellspacing="0" cellpadding="4" width="100%">
 [% sorting %]
 [%- FOREACH x = results %]
     [%- IF rowcounter mod 2 %]
         [%- rowclass = "table_odd" %]
     [%- ELSE %]
         [%- rowclass = "table_even" %]
     [%- END %]
 <tr class="[% rowclass %]">
 <td>[% x.name %]</td><td>[% x.age %]</td>
 </tr>
     [%- rowcounter = rowcounter + 1 %]
 [% 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>
 <br /><br />
 </body>
 </html>

The DB is left to the reader's imagination.

DESCRIPTION

Generates HTML table headers and footers to handle sorting and paging for tabular reports. Additionally, generates SQL fragments to insert into queries to handle paging and sorting.

METHODS

new($options)

Creates and returns a new paging object. new() accepts a single hashref as an argument, options. Options may be one or more of the following:

CGI_OBJECT:

A previously-created CGI object. Saves the overhead of loading a new one.

RESULTS_PER_PAGE:

The number of rows to be displayed per page. Setting this number to 0 will disable paging, but only if you call the get_limit() method. If you directly call get_DRIVER_limit, it is not guaranteed to work. default: 25

PAGES_IN_LIST:

The number of pages that will appear in the paging array. default: 5 Note: this number must be odd. If it is even, it will be decremented by 1.

PAGE_VARIABLE:

The GET parameter that defines which page of the results we are viewing. default: page

NUM_RESULTS:

This should not be used when instantiating the object, because it means that in doing so you have bypassed the get_mysql_limit function, which is against the design of this object.

SORT_VARIABLE:

The GET parameter that defines which column is being used for sorting and the direction of the sort. default: sort

DEFAULT_SORT:

Which column should be sorted by when the page is drawn for the first time. User the version of the name that is provided as the get param in the SORTABLE_COLUMNS definition. Sort direction is ascending by default. No column is sorted on by default. Examples:

 DEFAULT_SORT => 'date',
 DEFAULT_SORT => 'date,ASC',
 DEFAULT_SORT => 'date,DESC',
CURRENT_PAGE:

Which page are we currently viewing? Should never need to be overridden. default: $cgi->param(PAGE_VARIABLE)

CURRENT_SORT_COL:

Which column are we currently sorting by? Should never need to be overridden.

CURRENT_SORT_DIR:

Which direction are we currently sorting? Should never need to be overridden.

LANGUAGE_TOKENS:

This is a hashref used to make this module multilingual. Currently, there is only one language token: display_token. It is, by default, 'Displaying Results $1 to $2 of $3' where $1, $2, and $3 are replaced with the appropriate values.

The following options control formatting, and should be self-explanatory. Their defaults are listed next to the item.

PREV_HTML &lt;
NEXT_HTML &gt;
FIRST_HTML &laquo;
LAST_HTML &raquo;
ASC_HTML &uarr;
DESC_HTML &darr;

Finally, it accepts a list of sortable columns. A column consists of the following properties:

get:

The name of the column on the get string. This is used in conjunction with SORT_VARIABLE as in: http://example.com/cgi-bin/reports/foo.cgi?SORT_VARIABLE=SORTABLE_COLUMNS->[0]->{'get'}

sql:

The name of the column in sql. Include any necessary table aliases in this definition. Example: sql => 'tablename.columnname'

display:

The name of the column in the display. Used by get_sortable_html_table_header

sortable:

True/False (1/0). Defines the behaviour of the column. Does it allow sorting?

order:

Optional property that defines the sql used in the order clause. If not set, the 'sql' property is used. See the ReportWriter documentation for more information on this property.

Each sortable column definition is a hashref, and SORTABLE_COLUMNS itself is an arrayref containing one or more of these hashrefs. Example:

 SORTABLE_COLUMNS => [
         {
             'get' => 'name',
             'sql' => 'person.name',
             'display' => 'Name',
             'sortable' => 0,
         },
         {
             'get' => 'age',
             'sql' => 'person.age',
             'display' => 'Age (in years)',
             'sortable' => 1,
         },
 ]
num_results($int)

Accepts the number of results that will be generated for the query being used. Sets the number of rows internally to the number supplied, and returns that number as confirmation of the setting.

If the CURRENT_PAGE has been initialized to something greater than the total number of available pages (where $total_pages = ceil(NUM_RESULTS / RESULTS_PER_PAGE)), then the function will reset CURRENT_PAGE to the last available page and return FALSE. This should signal the calling script to regenerate the result set if the result set has already been generated (as would be the case if using the SQL_CALC_FOUND_ROWS feature of MySQL 4.0+).

Without a function like FOUND_ROWS(), this number could be generated by an additional SQL query that counts the total number of rows, using the same WHERE clause:

 my $count =  $dbh->selectrow_array("SELECT count(*) FROM articles WHERE ...");
 $paging->num_results($count);
generate_paging_array()
 @pages = $pager->generate_paging_array();

Determines what page the viewer is currently on, and generates an array representing which pages are previous and next, first and last. Returns that array.

get_page_link($page_number)
 $url = $pager->get_page_link($page_number);

Saves the existing sort and page settings, and then uses some CGI module magic to generate a URL saving all parameters that were passed in except the page number, which is set to the requested page. Used to generate paging html.

get_paging_table()

Gets the paging array, generates links for each part of that array, and then generates HTML for the paging block based on the display settings that were configured during instantiation.

get_limit()
 $sql_limit_snippet = $self->get_limit();

Given the driver name, returns a limit clause compatible with that driver. If the driver is not supported, generates a warning and returns false.

get_sort()
 $sql_limit_snippet = $self->get_sort();

Given the driver name, returns a sort clause compatible with that driver. If the driver is not supported, generates a warning and returns false.

get_mysql_limit()
 $sql_limit_snippet = $self->get_mysql_limit();

Generates a MySQL-compliant LIMIT clause to be appended to SQL queries in order to get the appropriate rows for a paged report. Example above, in the SYNOPSIS.

get_mysql_sort()
 $sql_order_by_snippet = $pager->get_mysql_sort();

Returns a MySQL-compliant ORDER BY clause based on the current sorting settings, to be appended to the SQL query used to generate the report that this module is being used for. Example above in the SYNOPSIS.

get_Pg_limit()
 $sql_limit_snippet = $self->get_Pg_limit();

Generates a PostgreSQL-compliant LIMIT clause to be appended to SQL queries in order to get the appropriate rows for a paged report. Example above, in the SYNOPSIS.

get_Pg_sort()
 $sql_order_by_snippet = $pager->get_Pg_sort();

Returns a PostgreSQL-compliant ORDER BY clause based on the current sorting settings, to be appended to the SQL query used to generate the report that this module is being used for. Example above in the SYNOPSIS.

get_SQLite_limit()
 $sql_limit_snippet = $self->get_SQLite_limit();

Generates a SQLite-compliant LIMIT clause to be appended to SQL queries in order to get the appropriate rows for a paged report. Example above, in the SYNOPSIS.

get_SQLite_sort()
 $sql_order_by_snippet = $pager->get_SQLite_sort();

Returns a SQLite-compliant ORDER BY clause based on the current sorting settings, to be appended to the SQL query used to generate the report that this module is being used for. Example above in the SYNOPSIS.

get_sort_link($column)
 $url = $pager->get_sort_link($column);

Same as get_page_link() above, except allows you to specify the new sort instead of the new page. When specifying the sort column, specifying the same column that is currently selected results in the link being generated for the opposite of its current direction. Otherwise, each column defaults to sort ascending.

Additionally, when changing the sort, page is not preserved, the logic being that you likely want to start back at the beginning of the report to view the first n records instead of being stuck in the middle of the record set.

get_sortable_table_header()
 $html_table_header_row = $pager->get_sortable_table_header();

Generates the HTML for the table header, containing the column names and (where applicable) links to change the sort column/direction.

Since the header defines the columns, the columns need to be the same width as they are for the data. Therefore, we only draw a table row, not a full table as we do with the paging html. This row should probably be the first row of the table that contains the result set.

The output relies on stylesheet elements that currently do not have a definition anywhere. You will need to define these stylesheet elements on your own. This will be fixed in a future release, which hopefully will come soon.

TODO

  • allow for overrideable class names on the table elements

  • purely CSS design?

BUGS

None are known at this time.

Please report any additional bugs discovered to the author.

SEE ALSO

This module relies indirectly on the use of the DBI and the Template modules or equivalent. It relies directly on the use of CGI, POSIX, and List::MoreUtils.

AUTHOR

Shane Allen <opiate@gmail.com>

ACKNOWLEDGEMENTS

This module was developed during my employ at HRsmart, Inc. http://www.hrsmart.com and its public release was graciously approved.

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.