NAME

Class::DBI::Plugin::FilterOnClick - Generate browsable and searchable HTML Tables using FilterOnClick in conjunction with Class::DBI

SYNOPSIS

# Inside of your sub-class ("package ClassDBIBaseClass;" for example)
# of Class::DBI for use with your database and
# tables add these lines:

use Class::DBI::Plugin::FilterOnClick;
use Class::DBI::Plugin::Pager;
use Class::DBI::AbstractSearch;
use Class::DBI::Plugin::AbstractCount;
use Class::DBI::Plugin::RetrieveAll;

# the rest of your CDBI setup to follow  
.....
  
# Inside your script (separate from your Class::DBI setup file) you will be
# able to use this module's methods on your table class or object as needed.

# use the package/module created above
use ClassDBIBaseClass;

# include URI::Escape for some parameters clean up
use URI::Escape;

# we are using CGI in this example, but you can use Apache::ASP, Embperl, etc.
use CGI;

my $cgi = CGI->new();

my %params;

# clean up and create our parameters to be passed to FilterOnClick
map { $params{$_} = 
      uri_unescape($cgi->param("$_"))
   } $cgi->param();

# create our FilterOnClick object
my $filteronclick = Baseball::Master->filteronclick( 
                                  -config_file => '/srv/www/cgi-bin/baseball.ini',
                                  -rows    => $cgi->param('rows') || 15 ,
                                  -on_page => $cgi->param('page') || 1,
                                  -params => \%params );

$filteronclick->field_to_column(
   lastname   => 'Last Name' . $html->order_by_link('lastname'),
   firstname  => 'First Name' . $html->order_by_link('firstname'),
   bats       => 'Bats',
   throws     => 'Throws',
   ht_ft      => 'Height Ft',
   ht_in      => 'In',
   wt         => 'Weight',
   birthyear  => 'Birthyear',
   birthstate => 'Birthstate',
   _FilterOnClickCustom1_ => 'Other Data',
   _FilterOnClickCustom2_ => 'More Data'
                      );
                      

$filteronclick->data_table->addRow(
                   'Last Name',
                   'First Name',
                   'Bats' ,
                   'Throws' ,
                   'Height (ft)',
                   '(inches)',
                   'Weight',
                   'Birth Year' );

$filteronclick->params( $cgi->Vars; );    
$filteronclick->exclude_from_url([ 'page' ]);

# indicate which columns to exclude, inverse of display above
# can be set in config file as well
$filteronclick->exclude_columns();
   
# indicate the base class to work with, this is optional,
# if you should create you object via a call to
# Class::DBI::Plugin::FilterOnClick vs. a Class::DBI sub class
# this assures the correct sub class is used for data collection

$filteronclick->cdbi_class( 'Baseball::Master' );
   
# indicate the style of navigation to provide
$filteronclick->navigation_style( 'both' );
   
print qq~<fieldset><legend>Filter by First Letter of Last Name</legend>~;

print $filteronclick->string_filter_navigation(
   -column       => 'lastname',
   -position     => 'begins',
);

print qq~</fieldset>~;

$filteronclick->only('firstname');
 

print $filteronclick->build_table(
 
   _FilterOnClickCustom1_ => sub {
       my $pid = shift; # pid = Primary ID of the record in the base table
       my @status_objects = Baseball::Allstars->search(lahmanid => $pid);
       if (@status_objects) {
           my $years;
           foreach my $st (@status_objects) {
               $years .= $st->year() . " ";
           }
           return $years;
       }
       return 'NA';
   },
   
   _FilterOnClickCustom2_ => sub {
       my $pid = shift; # pid = Primary ID of the record in the base table
       my @status_objects = Baseball::Allstars->search(lahmanid => $pid);
       if (@status_objects) {
           my $teams;
           foreach my $st (@status_objects) {
               $teams .= $st->team() . " ";
           }
           return $teams;
       }
       return 'NA';
   },
 );

my $nav = $filteronclick->html_table_navigation();

print qq!<div algin="center">$nav</div>\n!;

$filteronclick->add_bottom_span($nav);
    
print $filteronclick->data_table;

UPGRADE WARNING

If you are using Class::DBI::Plugin::HTML or a pre version 1 Class::DBI::Plugin::FilterOnClick you will need to alter your code to support the new style used in version 1 and greater releases.

Version 1.1 uses Class::DBI::Plugin::Pager, you will need to alter your base class to reflect this change. In other words the use of Class::DBI::Pager is no longer allowed. This was done for an improvement in performance.

DESCRIPTION

The intention of this module is to simplify the creation of browsable and searchable HTML tables without having to write the HTML or SQL, either in your script or in templates.

It is intended for use inside of other frameworks such as Embperl, Apache::ASP or even CGI. It does not aspire to be its own framework. If you are looking for a frameworks which allow using Class::DBI I suggest you look into the Maypole or the Catalyst module.

See FilterOnClick below for more on the purpose of this module.

Tables are created using HTML::Table. The use of HTML::Table was selected because it allows for several advanced sorting techniques that can provide for easy manipulation of the data outside of the SQL statement. This is very useful in scenarios where you want to provide/test a sort routine and not write SQL for it. The more I use this utility the less likely it seems that one would need to leverage this, but it is an option if you want to explore it.

Feedback on this module, its interface, usage, documentation etc. is welcome.

FilterOnClick

FilterOnClick is a process for allowing database filtering via an HTML table. Within a script, filters are predefined based on the type of data and the users desired interaction with the data. When users click on an item in the table it filters (or unfilters if the value had used to filter previously) the records displayed to match the associated filter. Filters can be applied and unapplied in almost any order. In addition to filtering FilterOnClick also allows for ordering the data.

The concept at its core is relatively simple in nature. You filter the results in the table by clicking on values that are of interest to you. Each click turns on or off a filter, which narrows or expands the total number of matching records. This allows for identifying abnormal entries, trends, or errors, simply by paging, searching or filtering through your data. If you configure the table appropriately you can even link to applications or web pages to allow editing the records.

An example FilterOnClick session would consist of something like this: You get a table of records, for our example lets assume we have four columns: "First Name" aka FN, "Last Name" aka LN , "Address" , and "Email". These columns are pulled from the database and placed into an HTML table on a web page. The values in the FN , LN and Email address columns are links back to the script that generated the original table, but contain filter information within the query string. In other words the link holds information that will modify the SQL query for the next representation of data.

Presently there are six (6) built in filter types for within tables and three (3) more that are specific to string based matches outside of the table itself. (see string_filter_navigation method below for info on the second three)

The six html table level filters are 'only','contains','beginswith','endswith' 'variancepercent','variancenumerical'. The where clause is created within FilterOnClick automatically through the Class::DBI::AbstractSearch module. You are not required to create any SQL statements or add any code to your Class::DBI base class for simple database structures.

Back to the example at hand. Lets say the database has 20K records and the sort order was set to LN by default. The FN column has been configured with an 'only' filter. In the FN list you see the FN you are looking for so you click on it, when the script runs and auto-generates a new filter (query) that now only shows records that match the FN you clicked on. Clicking on the FN column a second time removes the filter.

Filters are cascading, allowing you to filter on multiple columns. So if you want to find all the 'Smith's' with email addresses like 'aol.com' you could click first on an email address containing 'aol.com' and then a last name of 'Smith', provided you configured a proper filter code for the table.

If the searchable option has been enabled you can also perform text based searched on any column.

You can see FilterOnClick in action at: http://cdbi.gina.net/cdbitest.pl (user: cdbi password: demo)

Example code to create a FilterOnClick column value ( see the build_table method ):

Match Exactly

$filteronclick->only('column_name');

# within the build_table method you can do this
column_name => 'only'

Match Beginning of column value with string provided

$filteronclick->beginswith('column_name' , 'string');

Match ending of column value with string provided

$filteronclick->endswith('column_name , 'string');

Filter to columns that contain a particular string (no anchor point)

$filteronclick->contains('column_name' , 'string'); 

Show records with a numerical variance of a column value

$filteronclick->variancenumerical('column_name' , number);

Show records with a percentage variance of a column value

$filteronclick->variancepercent('column_name' , number);

CONFIGURATION FILE

As of version .9 you can assign many of the attributes via a configuration file See the t/examples directory for a sample ini file

METHOD NOTES

The parameters are passed in via a hash, arrayref or scalar for the methods. The Class::DBI::Plugin::FilterOnClick specific keys in the hash are preceeded by a hypen (-). The build_table method allows for column names to be passed in with their own anonymous subroutine (callback) if you need to produce any special formating or linkage. Column name anonymous subroutines should NOT begin with a hypen.

METHODS

filteronclick

Creates a new Class::DBI::Plugin::FilterOnClick object

$filteronclick = ClassDBIBase::Class->filteronclick();

debug

Wants: 0, 1 or 2

Defaults to: 0

Valid in Conifguration File: Yes

Set to one to turn on debugging output. This will result in a considerable amount of information being sent to the browser output so be sure to disable in production. Can be set via method or configuration file. If set to 1 it will print debug data via 'warn' if set to 2 it will print debug data via 'print'

$filteronclick->debug(1);

params

Wants: Hash reference of page paramters

Defaults to: {} (empty hash ref)

This should be passed in via the filteronclick method as -params to allow auto generation of various attributes, this documentation is provided for those that want to handle various stages of the build process manually.

Set the params that have been passed on the current request to the page/script

$filteronclick->params( {
    param1 => 'twenty'
} );

Using CGI

use URI::Escape;
my %params;

map { $params{$_} =
       uri_unescape($cgi->param("$_"))
    } $cgi->param();

$filteronclick->params( \%params );

Using Apache::ASP

$filteronclick->params( $Request->Form() );

Using Embperl

$filteronclick->params( \%fdat );

config

Wants: configuration key, value is optional

Defatuls to: na

Configuration values can be accessed directly or via the config method. This is allowed so you know where the value you are calling is being assigned from.

To get get a value:

$filteronclick->config("searchable");

To set a value do this:

$filteronclick->config('searchable',1);

exclude_from_url

Wants: Array reference

Defaults to: [] (emptry array ref)

Key/value pair to be removed from auto generated URL query strings. The key for the page should be one of the items here to avoid navigation issues

$filteronclick->exclude_from_url( [ 'page' ] );

form_table

Wants: HTML::Table object

Defaults to: HTML::Table object

Returns: HTML::Table object

$filteronclick->form_table(); # get current form table object
$filteronclick->form_table($html_table_object); # set form table object

There is no need to set this manually for simple forms. This method is a lingering item and may be removed in future releases. If you use it please inform the author.

field_to_column

Wants: Hash

Defaults to: empty

$filteronclick->field_to_column(
    'firstname' => 'First Name',
    'lastname' => 'Last Name'
);

cdbi_class

Wants: string

Defaults: n/a

Returns: current value

Sets or returns the table class the HTML is being generated for

$filteronclick->cdbi_class();

config_file

Returns the name of the config_file currently in use

rows

Wants: Number

Defaults to: 15

Sets the number of rows the table output by build_table will contain per page

$filteronclick->rows(20);

html_table

Wants: HTML::Table object

Defaults to: HTML::Table object

This is useful if you want to either create your own HTML::Table object and pass it in or you want to heavily modify the resulting table from build_table. See the HTML::Table module for more information.

build_table

Wants: Hash

Defatuls to: na

Returns: HTML::Table object

Accepts a hash of options to define the table parameters and content. This method returns an HTML::Table object. It also sets the data_table method to the HTML::Table object generated so you can ignore the return value and make further modifications to the table via the built in methods.

See Synopsis above for an example usage.

The build_table method has a wide range of paramters that are mostly optional.

exclude_columns

Wants: Arrary reference

Defaults to: na

Valid in configuration File: Yes

Returns: When called with no argument, returns current value; an array ref

Removes fields even if included in the display_columns list. Useful if you are not setting the columns or the columns are dynamic and you want to insure a particular column (field) is not revealed even if someone adds it somewhere else.

extend_query_string

Wants: hash of key and values to add

Defaults to: na

Valid in configuration File: No

Returns: Current query string + the arguments passed in

Adds elements to the query string to allow for creating custom predefined links with the current filter options applied.

data_table

Wants: HTML::Table object

Defaults to: na

Returns: HTML::Table object is assigned

Allows for you to pass in an HTML::Table object, this is handy if you have setup the column headers or have done some special formating prior to retrieving the results.

pager_object

Wants: Class::DBI::Pager object

Defaults to: Class::DBI::Pager object

Returns: Current pager_object

Allows you to pass in a Class::DBI::Pager based object. This is useful in conjunction with the html_table_navigation method. If not passed in and no -records have been based it will use the calling class to perform the lookup of records.

As of version .9 you do not need to assign this manually, it will be auto populated when call to 'filteronclick' is made.

records

Wants: Array reference

Defaults to: na

Returns: present value

Expects an anonymous array of record objects. This allows for your own creation of record retrieval methods without relying on the underlying techniques of the build_table attempts to automate it. In other words you can send in records from none Class::DBI sources, but you lose some functionality.

where

Wants: Hash reference

Defaults to: Dynamically created hash ref based on query string values, part of the FilterOnClick process.

Expects an anonymous hash that is compatiable with Class::DBI::AbstractSearch

order_by

Wants: scalar

Returns: current value if set

Passed along with the -where OR it is sent to the retrieve_all_sort_by method if present. The retrieve_all_sort_by method is part of the Class::DBI::Plugin::RetrieveAll module.

page_name

Wants: scalar

Returns: current value if set

Valid in Configuration file: Yes

Used within form and querystring creation. This is the name of the script that is being called.

query_string

Wants: scalar

Returns: current value if set

It is not required to set this, it is auto generated through the FilterOnClick process. This method is generally used for debugging.

rowcolor_even

Wants: Valid HTML code attribute

Defaults to: '#ffffff'

Returns: Current value if set

Valid in Configuration file: Yes

Define the even count row backgroud color

rowcolor_odd

Wants: Valid HTML code attributes

Defaults to: '#c0c0c0'

Valid in Configuration file: Yes

Define the odd count row backgroud color

rowclass

Valid in Configuration file: Yes

(optional) - overrides the -rowcolor above and assigns a class (css) to table rows

no_mouseover

Valid in Configuration file: Yes

Turns off the mouseover feature on the table output by build_table

mouseover_class

Valid in Configuration file: Yes

The CSS class to use when mousing over a table row

searchable

Valid in Configuration file: Yes

Enables free form searching within a column

search_exclude

Wants: arrayref of column names to not allow searching on

Defaults to: []

Returns: current columns to not allow searching for when called without parameters, returns nothing when new values are passed in.

list of columns that should allow for searching if searchable is set to 1

mouseover_bgcolor

Valid in Configuration file: Yes

Color for mouseover if not using a CSS definition. Defaults to red if not set

filtered_class

Valid in Configuration file: Yes

Defines the CSS class to use for columns that currently have an active Filter

ascending_string

Wants: string (can be image name)

Default to: '^'

Valid in Configuration file: Yes

The string used to represent the ascending sort filter option. If value ends with a file extension assumes it is an image and adds approriate img tag.

descending_string

Wants: string (can be an image name)

Defaults to: 'v'

Valid in Configuration file: Yes

The string used to represent the descending sort filter option. If value ends with a file extension assumes it is an image and adds approriate img tag.

rowclass_odd

Valid in Configuration file: Yes

The CSS class to use for odd rows within the table

navigation_separator

Valid in Configuration file: Yes

The seperator character(s) for page navigation

page_navigation_separator

Valid in Configuration file: Yes

The seperator for page navigation

table field name (dynamic method)

(code ref || (like,only) , optional) - You can pass in anonymous subroutines for a particular field by using the table field name (column). Three items are passed back to the sub; value of the column in the database, current url, and the entire database record as a Class::DBI result object.

Example:

first_name => sub {
   my ($name,$turl,$record) = @_;

   my $extra = $record->other_column();                         

   return qq!<a href="test2.pl?$turl">$name - $extra</a>!;
},

html_table_navigation

Creates HTML anchor tag (link) based navigation for datasets. Requires Class::DBI::Pager. Navigation can be in google style (1 2 3 4) or block (previous,next).

my $nav = $cdbi_plugin_html->html_table_navigation(
                    -pager_object      => $pager,
                    # pass in -navigation with block as the value for
                    # next/previous style 
                    # "google" style is the default
                    -navigation_style   => 'block',
                    -page_name          => 'test2.pl', 
               );

print "'$nav'\n";

add_bottom_span

Places the content you pass in at the bottom of the HTML::Table object passed in. Used for adding "submit" buttons or navigation to the bottom of a table.

search_ref

Creates the URL and where statement based on the parameters based into the script. This method sets the query_string accessor value and returns the where hash ref.

   $cdbi_plugin_html->search_ref( 
           # hash ref of incoming parameters (form data or query string)
           # can also be set via the params method instead of passed in
	   -params => \%params,
          
           # the like parameters by column (field) name that the
           # SQL statement should include in the where statement
           -like_column_map  => { 'first_name' => 'A%' },
          
   );

url_query

Creates the query portion of the URL based on the incoming parameters, this method sets the query_string accessor value and returns the query string

    $cdbi_plugin_html->url_query(
        
	# pass in the parameters coming into the script as a hashref 
	-params => \%params,
	
        # items to remove from the url, extra data that
        # doesn't apply to the database fields
        -exclude_from_url => [ 'page' ], 
    );

navigation_style

Wants: string, either 'block' or 'both'

Defaults to: block

Valid in Configuration File: Yes

Returns: Current setting

$filteronclick->navigation_style('both');

The navigation style applies to the string_filer_navigation method.

string_filter_navigation

my ($filter_navigation) = $cdbi_plugin_html->string_filter_navigation(
   -position => 'ends'
);

This method creates navigation in a series of elements, each element indicating a item that should appear in a particular column value. This filter uses anchor points to determine how to qualify the search. The anchor points are: BEGINSWITH ENDSWITH CONTAINS

The items in the 'strings' list will only be hrefs if the items in the database match the search. If you prefer them not to be displayed at all pass in the -hide_zero_match

The allowed parameters to pass into the method are:

hide_zero_match

Removes items that have no matches in the database from the strings allowed in the final navigation.

-position (optional - default is 'begin') - Tells the method how to do the match, allowed options are any case of 'begin' , 'end' or 'contains'. These options can be the entire anchor points as outlined above, but for ease of use only the aforemention is enforced at a code level.

query_string

(optional) - See methods above for documentation

navigation_list

(optional, array_ref - default is A-Z) - Array ref containing the strings to filter on.

navigation_column

Indicates which column the string filter will occur on. If you want to provide a filter on multiple columns it is recommended that you create multiple string_filter_navigation. Can be set via method, string_filter_navigation argument or configuration file

-page_name - The name of page that the navigation should link to

navigation_alignment

Set HTML attribute alignment for the page navigation.

navigation_seperator

$filteronclick->navigation_seperator('::');
-or-
-navigation_seperator => '::' # argument passed into string_filter_navigation
-or-
navigation_sperator=:: in the configuration file

(optional, default two non-breaking spaces) - The characters to place between each item in the list.

align

(optional, defaults to center) - defines the alignment of the navigation

no_reset

don't include the filter reset link in the output

form_select

This method is used in conjunction with build_form and is slated for removal in the next release. Please contact the author if you use this method or are interested in seeing it improved rather then removed.

this methods expects the following:

-value_column    # column containing the value for the option in the select
-text_column     # column containing the text for the optoin in the select (optional)
-selected_value  # the value to be selected (optional)
-no_select_tag   # returns option list only (optional)

FILTERS

Filters are generated with the build_table method. Filters allow for cascading drill down of data based on individual cell values. See Example page for a demo.

beginswith

Declare a begins with match on a column

$filteronclick->beginswith('column_name','A');
# where 'A' is the value to match at the beginning

endswith

$filteronclick->endswith('column_name','A');
# where 'A' is the value to match at the end of the column contents

contains

$filteronclick->contains('column_name','A');
# where 'A' is the value to match anywhere in the column contents

variancepercent

$filteronclick->variancepercent('column_name',2);
# where '2' is the allowed percentage of variance to filter on

variancenumerical

$filteronclick->variancenumerical('column_name',2);
# where '2' is the allowed variance to filter on based
# if value for 'column_name' is clicked

only

$filteronclick->only('column_name');
# creates a filter on 'column_name' cells to match the value in the cell
# clicked

Additional Column Value Methods

colorize

Wants: list with column name, regular expression and CSS class name

Defaults to: na

Returns: na

$filteronclick->colorize('column_name','regex','className');
# will colorize a cell value based on a css entry when the value
# matches the regex passed in

This method will colorize a cell with matching content based on a CSS class passed into it. The appropriate html markup for the css is added to the output.

INTERNAL METHODS/SUBS

If you want to change behaviors or hack the source these methods and subs should be reviewed as well.

get_records

Finds all matching records in the database

create_order_by_links

add_number

determine_columns

Finds the columns that are to be displayed

auto_hidden_fields

add_hidden

create_auto_hidden_fields

add_link

allowed_methods

build_form

build_query_string

colorize_value

column_css_class

current_column

current_filters

current_record

fill_in_form

filter_lookup

hidden_fields

html

no_form_tag

no_submit

on_page

order_by_link

order_by_links

output_debug_info

query_string_intelligence

read_config

search_primary

use_formbuilder

BUGS

Unknown at this time.

SEE ALSO

Class::DBI, Class::DBI::AbstractSearch, Class::DBI::AsForm, HTML::Table, Class::DBI::Plugin::Pager

AUTHOR

Aaron Johnson aaronjjohnson@gmail.com

THANKS

Thanks to my Dad for buying that TRS-80 in 1981 and getting me addicted to computers.

Thanks to my wife for leaving me alone while I write my code :^)

The CDBI community for all the feedback on the list and contributors that make these utilities possible.

Roy Johnson (no relation) for reviewing the documentation prior to the 1.1 release.

CHANGES

Changes file included in distro

COPYRIGHT

Copyright (c) 2004-2007 Aaron Johnson. All rights Reserved. This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself.

cpanm Class::DBI::Plugin::FilterOnClick

perl -MCPAN -e shell
install Class::DBI::Plugin::FilterOnClick