CGI::List - Easily generate HTML Lists From a DataBase

CGI-List documentation Contained in the CGI-List distribution.

Index

Code Index:

NAME

Top

CGI::List - Easily generate HTML Lists From a DataBase

VERSION

Top

Version 0.05

SYNOPSIS

Top

Easily create html lists whit auto order, auto pagination, grouping and conditional formats.

Perhaps a little code snippet.

    use CGI::List;

    #We need a DBH Handle
    $dbh = DBI->connect(.....);

    #Create List Object
    $list = CGI::List->new(
	dbh => $dbh,
        sql => {
            select => "foo, bar ",
            from => "table1",
            limit => "20",
            where => "some_column1=? AND some_column2=?",
            params=>["Value1","Value2"],
            order_by => "foo DESC",
       },
    );

    #Print 
    print $list->print();

FEATURES

Top

    * Auto Order
    * Auto Pagination
    * CSS based. Contact developer for CSS examples 
    * Column totals(Only SUM, COUNT and AVG are supported)
    * Conditional formats for rows
    * Conditional Formats for cells
    * Auto detect column names
    * 2 row formats for better visualization
    * Row grouping
    * Http Link and highlight on rows based in rows keys
    * Opener action for pop up windows
    * And more

METHODS

Top

new()

This method creates a new $list object, which you then use to generate and process your list.

    my $list = CGI::List->new();

	The following is a description of each option, in alphabetical order:

	name => 'list_name'
            If you use a multi lists pages you need to specify a name for each list
	on_errors => 'print',
	    If you have SQL errors you can print(default), warn or die
        debug => 0 | 1, default 0
            If is set to 1 this print the query executed on SQL errors
	caption => 'list title'
	    This create a list title with the caption html tag
	auto_order => 1 | 0,   default 1
	    Enable, disable auto order mechanism on the list
	pagination => 1 | 0, default 1
	    Enable or disable auto pagination on the list
	nav_pages => $number, default 4
	    Number of pages you can see on pagination
        Number_Format => {THOUSANDS_SEP=>",",DECIMAL_POINT=>".",MON_THOUSANDS_SEP=>",","MON_DECIMAL_POINT"=>".","INT_CURR_SYMBOL"=>'$'};
            On SUM otions you can format the result to price ($1,234.00), whit this parameters THOUSANDS_SEP, DECIMAL_POINT, MON_THOUSANDS_SEP, MON_DECIMAL_POINT, INT_CURR_SYMBOL.

        table => {}
            Propiedades de la tabla, default {width => "100%",class => "cg_table",align => "center",cellpadding=>"0",cellspacing=>"0"}
        labels => {
		       page_of => 'Page _PAGE_ of _OF_',
		       no_data   => 'No records found',
		       link_up   => '↑',
		       link_down => '↓',
		       next_page => '»',
		       previous_page => '«',
		       number_of_rows => "_NUMBER_ rows",
		      };
            This are the text printed on the list, you can traslate to other language

print()

This function renders the list into HTML, and returns a string containing the list.

    print $list->print;

group()

This method Create groups of data:

    $list->group(key=>'key_field',fields=>[qw/key_field other_field other_field/]);

group_total()

This method calculate row totals on each group:

    $list->group_total(key=>'key_field',type=>"MATH",operation=>'SUM',label=>"%% some text",format=>'price');
Operation support only SUM, AVG, and COUNT, the format parameter are optional

total()

This method calculate row totals:

    $list->total(key=>'key_field',type=>"MATH",operation=>'SUM',label=>"%% some text",format=>'price');
Operation suport only SUM, AVG, and COUNT, the format parameter are optional

row_format()

This function specify a format of row depending on their value

    $list->row_format(name=>"field_name",condition=>"'%%' eq 'urgent'",params=>{class=>"cg_row_urgent"});

%% is the cell value, on this example you need to create 2 css class cg_row_urgent and cg_row_urgent_hover for the hover action

cell_format();

This function specify a format of cell depending on their value

    $list->cell_format(name=>"field_name",condition=>"'%%' eq 'urgent'",params=>{class=>"cg_cell_urgent"});

%% is the cell value, on this example you need to create 2 css class cg_cell_urgent and cg_cell_urgent_hover for the hover action

columns_width()

This function specify the width of each column

    $list->columns_width(["100","200","300"]);

On this example you have a 3 columns query and 100, 200, 300 are the width of each column

columns_align()

This function specify the horizontal align of each column

    $list->columns_align(["left","center","right"]);

On this example you have a 3 columns query and left, center, right are the alignment of each column data

columns_headers_align()

This function specify the horizontal align of each column header

    $list->columns_headers_align(["left","center","right"]);

On this example you have a 3 columns query and left, center, right are the alignment of each column header data

Examples

Top

This example provides an list of data with auto order, auto pagination and action on each row click

    my $list = CGI::List->new( 
                  dbh => $dbh,
                  name => "pays_list",
                  sql => {
                      select => "p.pay_id, p.date, pr.name, " .
                          "IF(p.is_cancel,'Cancel','Active') AS 'status'",
                      from => "pays p INNER JOIN partners pr ON p.pay_id=pr.pay_id ",
                      limit => "20",
                      where => "some_column=? AND some_column=?",
                      params=>["Value1","Value2"],
                      order_by => "p.date DESC",
                     },
                  link => {
                       key => "pay_id",
                       hidde_key_col => 1,
                       location => "pays.pl",
                       transit_params => {some_param_to_be_present_everywere=>"value"},
                      },
                 );
	$list->print();

AUTHOR

Top

David Romero Garcia, <romdav at gmail.com>

COLABORATORS

Top

Juan C. Sanchez-DelBarrio

BUGS

Top

Please report any bugs or feature requests to bug-cgi-list at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=CGI-List. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

SUPPORT

Top

You can find documentation for this module with the perldoc command.

    perldoc CGI::List

You can also look for information at:

http://www.cgi-list.com. http://groups.google.com/group/cgilist.

* AnnoCPAN: Annotated CPAN documentation

http://annocpan.org/dist/CGI-List

* CPAN Ratings

http://cpanratings.perl.org/d/CGI-List

* RT: CPAN's request tracker

http://rt.cpan.org/NoAuth/Bugs.html?Dist=CGI-List

* Search CPAN

http://search.cpan.org/dist/CGI-List

ACKNOWLEDGEMENTS

Top

COPYRIGHT & LICENSE

Top

Copyright 2007 David Romero García, all rights reserved.

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

CGI-List documentation Contained in the CGI-List distribution. 
package CGI::List;

use strict;
use Carp qw(croak carp);

require 5.004;
use CGI qw/:standard *table/;
use Math::Round(qw/nearest nhimult/);


our $VERSION = '0.05';

sub new {
    my $class    = shift;
    my (%params) = @_;
    my $self     = {};
    bless $self,$class;

    #Init params
    defined param("cg_list")  or param(-name=>"cg_list", -value=>"");
    defined param("cg_order") or param(-name=>"cg_order",-value=>"");
    defined param("cg_side")  or param(-name=>"cg_side", -value=>"");
    defined param("cg_page")  or param(-name=>"cg_page", -value=>"1");

    #Prevent attacks
    param(-name=>"cg_order",-value=>int(param("cg_order") || 0));
    param(-name=>"cg_page",-value=>int(param("cg_page") || 0));

    #Predefined values
    $self->{name} = "cg_list";
    $self->{debug} = 0;
    $self->{on_errors} = "print";
    $self->{auto_order} = 1;
    $self->{pagination} = 1;
    $self->{nav_pages}  = 4;
    ($self->{script},$self->{p}) = split(/\?/,$ENV{REQUEST_URI});

    $self->{table} = {
		      width       => "100%",
		      class       => "cg_table",
		      align       => "center",
		      cellpadding => "0",
		      cellspacing => "0",
		     };

    $self->{columns}= {params => {}};
    $self->{th} = {params => {align => "center"}};
    $self->{detail_th} = {params => {}};
    $self->{group_th} = {params => {align=>"left",class=>"cg_group_th"}};
    $self->{group_td} = {params => {align=>"left",class=>"cg_group_td"}};

    $self->{group_item_totals} = {td=>{params=>{class=>"cg_group_item_totals"}}};


    $self->{detail}{td}{params} = {};
    $self->{detail}{Tr}{params_a} = {class=> "cg_row_a"};
    $self->{detail}{Tr}{params_b} = {class=> "cg_row_b"};

    $self->{no_data}{params} = {class=>"cg_no_data", align=>"center"};

    $self->{totals}{td}{params} = {align=>"right",class=>"cg_cell_total"};
    $self->{totals}{Tr}{params} = {class=>"cg_row_total"};

    $self->{foother}{params} = {class=>"cg_foother"};

    $self->{nw_params}="width=600,height=500,toolbar=no,scrollbars=yes,top='+((screen.height/2)-250)+',left='+((screen.width/2)-300)+'";

    $self->{orders} = {};

    $self->{labels} = {
		       page_of => 'Page _PAGE_ of _OF_',
		       no_data   => 'No records found',
		       link_up   => '&uarr;',
		       link_down => '&darr;',
		       next_page => '&raquo;',
		       previous_page => '&laquo;',
		       number_of_rows => "_NUMBER_ rows",
		      };

    $self->{Number_Format}={THOUSANDS_SEP=>",",DECIMAL_POINT=>".",MON_THOUSANDS_SEP=>",","MON_DECIMAL_POINT"=>".","INT_CURR_SYMBOL"=>'$'};

    #Put all Parameters on the object
    foreach my $init_param(keys %params){
		$self->{$init_param} = $params{$init_param};
    }


    #Order params and Query
    if ($self->{auto_order} and param('cg_order') and param('cg_list') eq $self->{name}){
	if(param("cg_order") =~ /\w+/){
	    $self->{sql}{order_by}  = param("cg_order");
	    $self->{sql}{order_by} .= " DESC  " if (param("cg_side"));
	    $self->{sql}{order_by} .= " ASC  " if (!param("cg_side"));
	}
    }

    $self->{transit_params} = "";
    $self->{cgi_cg_params} = "";

    $self->{link}{event} = "onClick" if($self->{link} and !$self->{link}{event});

    return $self;
}

sub print {
    my $self = shift;
    my $grid = "";

    $self->transit_params();
    if(!defined $self->{rs}){
	$grid .= $self->get_data();
    }

    $self->{table}{id} = 'cg_table_' . $self->{name};
#    $grid .= '<link href="styleCG.css" rel="stylesheet" type="text/css">';
    $grid .= "<!-- Created whit CGI::List -->\n";
    $grid .= start_table($self->{table}) . "\n";
    $grid .= "<caption>$self->{caption}</caption>" if($self->{caption});
    if(defined $self->{groups}){
		$grid .= $self->print_group_columns();
		$grid .= $self->print_group_detail();
		if(!$self->{rows}){
		    $self->{no_data}{params}{colspan} = $self->{colspan};
		    $grid .= "    " . Tr ({},td($self->{no_data}{params},$self->{labels}{no_data})) . "\n";
		}else{
		    $grid .= $self->print_group_totals;
		    $grid .= $self->print_pagination();
		}
    }else{
		$grid .= $self->print_columns();
		$grid .= $self->print_detail();
		if(!$self->{rows}){
		    $self->{no_data}{params}{colspan} = $self->{colspan};
		    $grid .= "    " . Tr ({},td($self->{no_data}{params},$self->{labels}{no_data})) . "\n";
		}else{
		    $grid .= $self->print_totals;
		    $grid .= $self->print_pagination();
		}
    }
    $grid .= "</table>\n";
    $grid .= $self->js_row_effect();

    return $grid;
}

sub get_data {
    my $self = shift;

    $self->build_query();

    $self->{sth} = $self->{dbh}->prepare($self->{sql}{query});
    eval {
	if($self->{sql}{params}){
	    $self->{sth}->execute(@{$self->{sql}{params}});
	}else{
	    $self->{sth}->execute();
	}
    };
    #if sql errors
    if($@){
	if($self->{on_errors} eq "die"){
	    if($self->{debug}){
		croak "CGI::List Error code: ".$self->{dbh}->err.". Error message: ".$self->{dbh}->errstr . " SQL: " . $self->{sql}{query};
	    }else{
		croak "CGI::List Error code: " . $self->{dbh}->err . ". Error message: " . $self->{dbh}->errstr;
	    }
	    return "";
	}elsif($self->{on_errors} eq "warn"){
	    if($self->{debug}){
		carp "CGI::List Error code: ".$self->{dbh}->err.". Error message: ".$self->{dbh}->errstr . " SQL: " . $self->{sql}{query};
	    }else{
		carp "CGI::List Error code: " . $self->{dbh}->err . ". Error message: " . $self->{dbh}->errstr;
	    }
	    return "";
	}elsif($self->{on_errors} eq "print"){
	    if($self->{debug}){
		return "CGI::List Error code: " . $self->{dbh}->err .". Error message: ".$self->{dbh}->errstr." SQL: ".$self->{sql}{query};
	    }else{
		return "CGI::List Error code: " . $self->{dbh}->err .". Error message: ".$self->{dbh}->errstr;
	    }
	}
#    }
#
#    if ( defined $self->{dbh}->errstr ) {
#		
    }else{
	while ( my $rec = $self->{sth}->fetchrow_hashref() ) {
	    push (@{$self->{rs}},$rec);
	}
    }
    return "";
}

sub build_query {
    my $self = shift;
    if (ref \$self->{sql} eq "SCALAR"){
	my $query = $self->{sql};
	$self->{sql} = {
			query => $query,
		       };
	$self->{auto_order} = 0;
	$self->{pagination} = 0;
    }else{
	defined $self->{sql}{select}   or $self->{sql}{select}   = "";
	defined $self->{sql}{from}     or $self->{sql}{from}     = "";
	defined $self->{sql}{where}    or $self->{sql}{where}    = "";
	defined $self->{sql}{order_by} or $self->{sql}{order_by} = "";
	defined $self->{sql}{limit}    or $self->{sql}{limit}    = "";
	defined $self->{sql}{offset}   or $self->{sql}{offset}   = "";
	$self->{sql}{query}  = " SELECT   " . $self->{sql}{select};
	$self->{sql}{query} .= " FROM     " . $self->{sql}{from}     if $self->{sql}{from};
	$self->{sql}{query} .= " WHERE    " . $self->{sql}{where}    if $self->{sql}{where};
	$self->{sql}{query} .= " GROUP BY " . $self->{sql}{group_by} if $self->{sql}{group_by};
	$self->{sql}{query} .= " ORDER BY " . $self->{sql}{order_by} if $self->{sql}{order_by};
	$self->{sql}{query} .= " LIMIT    " . $self->{sql}{limit}    if $self->{sql}{limit};
	if($self->{pagination}){
	    $self->{sql}{query} .= " OFFSET   " . ($self->{sql}{limit} * (param("cg_page") - 1) );
	}else{
	    $self->{sql}{query} .= " OFFSET   " .  $self->{sql}{offset}   if $self->{sql}{offset};
	}
    }
}

sub print_columns {
    my $self = shift;
    $self->get_columns();
    if(defined $self->{headers_groups}){
	my $HTML = "";
	$self->{th}{params}{align} = "center";
	foreach my $hgroup (@{$self->{headers_groups}}){
	    if(ref $hgroup eq "HASH" ){
		$self->{th}{params}{colspan} = $hgroup->{colspan};
		$HTML .= th($self->{th}{params},$hgroup->{label}) . "\n";
		undef $self->{th}{params}{colspan};
	    }else{
		$HTML .= th($self->{th}{params},"") . "\n";
	    }
	}
	my $line1 = Tr ($self->{columns}{params},$HTML);
	$HTML = "";
	undef 	$self->{th}{params}{align};
	my $it = 0;
	foreach my $label (@{$self->{columns}{labels}}){
	    $self->{th}{params}{width} = $self->{columns_width}[$it] if(defined $self->{columns_width}[$it]);
	    $self->{th}{params}{align} = $self->{columns_headers_align}[$it] if(defined $self->{columns_headers_align}[$it]);
	    $HTML .= th($self->{th}{params},$label) . "\n";
	    $it++;
	}
	return $line1 . "\n   " . Tr ($self->{columns}{params},$HTML);
    }else{
	if (defined $self->{columns_width} or defined $self->{columns_headers_align}){
	    my $HTML = "";
	    my $it = 0;
	    foreach my $label (@{$self->{columns}{labels}}){
		$self->{th}{params}{width} = $self->{columns_width}[$it] if(defined $self->{columns_width}[$it]);
		$self->{th}{params}{align} = $self->{columns_headers_align}[$it] if(defined $self->{columns_headers_align}[$it]);
		$HTML .= th($self->{th}{params},$label) . "\n";
		$it++;
	    }
	    return "   " . Tr ($self->{columns}{params},$HTML);
	};
	return "   " . Tr ($self->{columns}{params},[th($self->{th}{params},$self->{columns}{labels})]) . "\n";
    }
}

sub get_columns {
    my $self = shift;
    $self->{columns}{names} = ();
    $self->{columns}{labels} = ();
    $self->{colspan} = ($self->{sth}->{NUM_OF_FIELDS}) || 0;
    foreach my $i(0 .. ($self->{colspan} - 1)) {
	defined $self->{sth}->{NAME}->[$i] or $self->{sth}->{NAME}->[$i] = "";
	if ($self->{sth}->{NAME}->[$i] and !($self->{link}{hidde_key_col} and $self->{sth}->{NAME}->[$i] eq $self->{link}{key})){

	    push(@{$self->{columns}{names}},$self->{sth}->{NAME}->[$i]);
	    my $col_label = $self->{sth}->{NAME}->[$i];
	    $col_label =~ s/_/ /g;
	    $col_label = ucfirst($col_label);

	    #Auto order Links
	    my $side = 0;
	    $side = 1 if (!param("cg_side"));
	    if($self->{auto_order}){
		if(($i+1) eq param("cg_order")){
		    if(param("cg_side") eq "0"){
			$col_label .= ' ' . a({href=>$self->{script} . "?cg_order=".($i+1)."&cg_side=1&cg_page=" . param("cg_page") . "&cg_list=" . $self->{name} . $self->{transit_params}},$self->{labels}{link_up});
			$col_label .= $self->{labels}{link_down};
		    }elsif(param("cg_side") eq "1"){
			$col_label .= ' ' . $self->{labels}{link_up};
			$col_label .= a({href=>$self->{script} . "?cg_order=".($i+1)."&cg_side=0&cg_page=" . param("cg_page") . "&cg_list=" . $self->{name} . $self->{transit_params}},$self->{labels}{link_down});
		    }
		}else{
		    $col_label .= ' ' .a({href=>$self->{script} . "?cg_order=".($i+1)."&cg_side=1&cg_page=" . param("cg_page") . "&cg_list=" . $self->{name} . $self->{transit_params}},$self->{labels}{link_up});
		    $col_label .= a({href=>$self->{script} . "?cg_order=".($i+1)."&cg_side=0&cg_page=" . param("cg_page") . "&cg_list=" . $self->{name} . $self->{transit_params}},$self->{labels}{link_down});
		}
	    }
	    push(@{$self->{columns}{labels}},$col_label);
   	}
    }
    $self->{colspan} -= 1 if($self->{link}{hidde_key_col});
}

sub print_detail {
    my $self = shift;
    my $HTML = "";

    $self->{rows} = 0;
    foreach my $rec(@{$self->{rs}}) {
	$self->{rows} ++;
	my @fields;
	my $row_cells = "";
	my $row_params = "params_b";
	my $row_html_params = 0;
	$row_params = "params_a" if (($self->{rows}/2) - int($self->{rows}/2));
	foreach my $i(0 .. (($self->{colspan}-1))) {
	    if(defined $self->{columns_align}){
		$self->{detail}{td}{params}{align} = $self->{columns_align}[$i];
	    }

	    if(defined $self->{cell_format}{$self->{columns}{names}[$i]}){
		#Cell Formats
		my $cell_params = $self->{detail}{td}{params};
		foreach my $cell_format(@{$self->{cell_format}{$self->{columns}{names}[$i]}}){
		    my $check = 0;
		    my $condition = $cell_format->{condition};
		    $condition =~ s/%%/$rec->{$self->{columns}{names}[$i]}/g;
		    $condition =~/([\S\s]+)\s(\S+)\s([\S\s]+)/;
		    my $untained_condition = " $1 $2 $3" || "";
		    eval '$check = 1 if(' . $untained_condition . ');';
		    if( $check ){
			$cell_params = $cell_format->{params};
		    }
		}
		$row_cells .= td($cell_params,$rec->{$self->{columns}{names}[$i]});
	    }else{
		#Normal cell
		$row_cells .= td($self->{detail}{td}{params},$rec->{$self->{columns}{names}[$i]});
	    }
	    if(defined $self->{row_format}{$self->{columns}{names}[$i]}){
		#Row Format
		foreach my $row_format(@{$self->{row_format}{$self->{columns}{names}[$i]}}){
		    my $check = 0;
		    my $condition = $row_format->{condition};
		    $condition =~ s/%%/$rec->{$self->{columns}{names}[$i]}/g;
		    $condition =~/([\S\s]+)\s(\S+)\s([\S\s]+)/;
		    my $untained_condition = " $1 $2 $3" || "";
		    eval '$check = 1 if(' . $untained_condition . ');';
		    if( $check ){
			$row_params = $self->{columns}{names}[$i];
			$self->{detail}{Tr}{$row_params} = $row_format->{params};
			$row_html_params = 1;
		    }
		}
	    }
	}







#Links
	if($self->{link}){
	    if($self->{link}{target}){
		$self->{detail}{Tr}{$row_params}{$self->{link}{event}} = "window.open('" . $self->{link}{location} . "?" . $self->{link}{key} . "=" . $rec->{$self->{link}{key}} . "$self->{transit_params}','" . $self->{key}{target} . "','" . $self->{nw_params} . "');";
	    }elsif($self->{opener}){
		my $opener_transit_params = $self->{transit_params};
		$opener_transit_params =~ s/opener=[\w]*//g;
		$self->{detail}{Tr}{$row_params}{$self->{link}{event}} = "opener.location.href='" . $self->{opener} . "?" . $self->{link}{key} . "=" . $rec->{$self->{link}{key}} . "$opener_transit_params'; window.close();";
	    }elsif($self->{link}{location}){
		$self->{detail}{Tr}{$row_params}{$self->{link}{event}} = "document.location.href='" . $self->{link}{location} . "?" . $self->{link}{key} . "=" . $rec->{$self->{link}{key}} . "$self->{transit_params}';";
	    }
	}
	$HTML .= "   " . Tr ($self->{detail}{Tr}{$row_params},$row_cells) . "\n";
    }

    return $HTML;
}

sub js_row_effect {
    my $self = shift;
    my $HTML = "";
    #Row Efect
    if($self->{opener} or $self->{link}{location}){
	$HTML .= '<script type="text/javascript">
		var cg_table_' . $self->{name} . ' = document.getElementById(\'cg_table_' . $self->{name} . '\');
		for(var i = 0; i < cg_table_' . $self->{name} . '.rows.length; i++){
                        cg_row_class_name = cg_table_' . $self->{name} . '.rows[i].className;
                        if( cg_row_class_name.substring(0,7) == "cg_row_"){
	        	        cg_table_' . $self->{name} . '.rows[i].onmouseover = function(){this.className = this.className+\'_hover\';};
	        	        cg_table_' . $self->{name} . '.rows[i].onmouseout = function(){this.className = this.className.replace(\'_hover\',\'\');};
                        }
		}
</script>
'
    }
    return $HTML;
}

sub transit_params {
    my $self = shift;
    #Transit Params
    $self->{cgi_cg_params} =  "cg_order=" . param("cg_order") . 
                             "&cg_side=" . param("cg_side") . 
			     "&cg_page=" . param("cg_page") . 
			     "&cg_list=" . $self->{name};
    if (defined $self->{link}{transit_params}){
		foreach my $k (sort keys %{$self->{link}{transit_params}}){
		    $self->{transit_params} .= "&" . $k . "=" . $self->{link}{transit_params}{$k};
		}
    }
    if($self->{opener}){
		$self->{transit_params} .= "&opener=" . $self->{opener};
    }
}

sub print_pagination {
    my $self = shift;
    my $HTML = "";
    $self->{foother}{params}{colspan} = $self->{colspan}+1;
    if($self->{pagination}){
	#Get total rows
	my $sSQL = "SELECT count(*) AS total FROM " . $self->{sql}{from};
	$sSQL .= " WHERE    " . $self->{sql}{where}    if $self->{sql}{where};
	my $total = $self->{dbh}->selectrow_hashref($sSQL,{},@{$self->{sql}{params}});
	my $pages = ($total->{total} / $self->{sql}{limit});
	my $pages_int = int($pages);
	$pages = $pages_int + 1 if($pages > $pages_int);

	my $pagination = $self->{labels}{page_of};
	my $page = param("cg_page") || 1;
	$pagination =~ s/_PAGE_/$page/;
	$pagination =~ s/_OF_/$pages/;
	$pagination .= "&nbsp;&nbsp;&nbsp;";
	if(param("cg_page") > 1){
	    $pagination .= " " .a({-href => $self->{script} . "?cg_page=" . (param("cg_page")-1) . "&cg_order=" . param("cg_order") . "&cg_side=" . param("cg_side") . "&cg_list=" . $self->{name} . $self->{transit_params}},
				  $self->{labels}{previous_page}) . " ";
	    foreach(my $ii = $self->{nav_pages} -1;$ii > 0;$ii--){
		$pagination .= " " .a({-href => $self->{script} . "?cg_page=" . (param("cg_page") - $ii) . "&cg_order=" . param("cg_order") . "&cg_side=" . param("cg_side") . "&cg_list=" . $self->{name} . $self->{transit_params}},
				      (param("cg_page") - $ii)) if((param("cg_page") - $ii) > 0);
	    }
	}
	$pagination .= " | " if($pages > 1);
	if(param("cg_page") < $pages){
	    foreach(my $ii = 1;$ii < $self->{nav_pages};$ii++){
		$pagination .= " " .a({-href => $self->{script} . "?cg_page=" . (param("cg_page") + $ii) . "&cg_order=" . param("cg_order") . "&cg_side=" . param("cg_side") . "&cg_list=" . $self->{name} . $self->{transit_params}},
				      (param("cg_page") + $ii)) if((param("cg_page") + $ii) <= $pages);
	    }
	    $pagination .= " " .a({-href => $self->{script} . "?cg_page=" . (param("cg_page") +1) . "&cg_order=" . param("cg_order") . "&cg_side=" . param("cg_side") . "&cg_list=" . $self->{name} . $self->{transit_params},-alt=>"Siguiente"},$self->{labels}{next_page});
	}


	my $rows = $self->{labels}{number_of_rows};
	$rows =~ s/_NUMBER_/$self->{rows}/g;

	$HTML .= "    " . Tr ({},td($self->{foother}{params},
				    '<span class="cg_number_rows">' . $rows . '</span>' .
				    '<span class="cg_pages">' . $pagination . '</span>'
				   )) . "\n";
    }else{
	my $rows = $self->{labels}{number_of_rows};
	$rows =~ s/_NUMBER_/$self->{rows}/g;

	$HTML .= "    " . Tr ({},td($self->{foother}{params},
				    '<span class="cg_number_rows">' . $rows . '</span>'
				   )) . "\n";
    }
    return $HTML;
}

sub row_format {
    my $self = shift;
    my %params = @_;
    push(@{$self->{row_format}{$params{name}}},{'params' => $params{params},condition => $params{condition}});
}

sub cell_format {
    my $self = shift;
    my %params = @_;
    push(@{$self->{cell_format}{$params{name}}},{'params' => $params{params},condition => $params{condition}});
}

sub group {
    my $self = shift;
    my %params = @_;
    push(@{$self->{groups}},{'key' => $params{key},fields => $params{fields}});
    foreach my $field(@{$params{fields}}){
		push(@{$self->{group_fields_array}},$field);
		$self->{group_fields_hash}{$field} = 1;
    }

    if($self->{sql}{order_by}){
		$self->{sql}{order_by} = ($params{order_by} || $params{key}) . ", $self->{sql}{order_by}";
    }else{
		$self->{sql}{order_by} = "$params{order_by}" || $params{key};
    }
}

sub columns_width {
    my $self = shift;
    $self->{columns_width} = shift;
}

sub columns_align {
    my $self = shift;
    $self->{columns_align} = shift;
}

sub columns_headers_align {
    my $self = shift;
    $self->{columns_headers_align} = shift;
}

sub print_group_columns {
    my $self = shift;
    my @labels;
    foreach my $field(@{$self->{group_fields_array}}){
	push(@labels,ucfirst($field));
    }
    return "   " . Tr ($self->{columns}{params},[th($self->{group_th}{params},\@labels)]) . "\n";
}

sub print_group_item {
    my $self = shift;
    my $rec = shift;
    my @data;
    foreach my $key (@{$self->{group_fields_array}}){
	push(@data,$rec->{$key});
    }
    return "   " . Tr ($self->{columns}{params},[td($self->{group_td}{params},\@data)]) . "\n";
}

sub print_group_detail {
    my $self = shift;
    my $HTML = "";

    $self->{rows} = 0;
    my $group = undef;
    $self->get_detail_columns();
    foreach my $rec(@{$self->{rs}}) {
	#Group items
	if($group ne $rec->{$self->{groups}[0]{key}}){
	    if($group ne undef){
		$HTML .= $self->print_group_item_totals($self->{groups}[0]{key},$group);
		$HTML .= "</table>\n";
		$HTML .= "    </td>\n  </tr>\n";
	    }
	    $group = $rec->{$self->{groups}[0]{key}};
	    $HTML .= $self->print_group_item($rec);
	    $HTML .= "  <tr>\n    <td colspan=" . scalar(@{$self->{group_fields_array}}) . ">\n";
	    $self->{table}{class} = "cg_detail_table";
	    $HTML .= start_table($self->{table}) . "\n";
	    $HTML .= $self->print_detail_columns();
	}

 	$self->{rows} ++;
 	my @fields;
 	my $row_cells = "";
 	my $row_params = "params_b";
 	my $row_html_params = 0;
 	$row_params = "params_a" if (($self->{rows}/2) - int($self->{rows}/2));
 	foreach my $i(0 .. (($self->{colspan})- scalar(@{$self->{group_fields_array}}))) {
	    if(defined $self->{columns_align}){
		$self->{detail}{td}{params}{align} = $self->{columns_align}[$i];
	    }
 	    if(defined $self->{cell_format}{$self->{columns}{names}[$i]}){
 		#Cell Formats
 		my $cell_params = $self->{detail}{td}{params};
 		foreach my $cell_format(@{$self->{cell_format}{$self->{columns}{names}[$i]}}){
 		    my $check = 0;
 		    my $condition = $cell_format->{condition};
 		    $condition =~ s/%%/$rec->{$self->{columns}{names}[$i]}/g;
 		    $condition =~/([\S\s]+)\s(\S+)\s([\S\s]+)/;
 		    my $untained_condition = " $1 $2 $3" || "";
 		    eval '$check = 1 if(' . $untained_condition . ');';
 		    if( $check ){
 			$cell_params = $cell_format->{params};
 		    }
 		}
 		$row_cells .= td($cell_params,$rec->{$self->{columns}{names}[$i]});
 	    }else{
 		#Normal cell
 		$row_cells .= td($self->{detail}{td}{params},$rec->{$self->{columns}{names}[$i]});
 	    }
 	    if(defined $self->{row_format}{$self->{columns}{names}[$i]}){
# 		#Row Format
 		foreach my $row_format(@{$self->{row_format}{$self->{columns}{names}[$i]}}){
 		    my $check = 0;
 		    my $condition = $row_format->{condition};
 		    $condition =~ s/%%/$rec->{$self->{columns}{names}[$i]}/g;
 		    $condition =~/([\S\s]+)\s(\S+)\s([\S\s]+)/;
 		    my $untained_condition = " $1 $2 $3" || "";
 		    eval '$check = 1 if(' . $untained_condition . ');';
 		    if( $check ){
 			$row_params = $self->{columns}{names}[$i];
 			$self->{detail}{Tr}{$row_params} = $row_format->{params};
 			$row_html_params = 1;
 		    }
 		}
 	    }
 	}


# #Links
 	if($self->{link}){
 	    if($self->{link}{target}){
 		$self->{detail}{Tr}{$row_params}{$self->{link}{event}} = "window.open('" . $self->{link}{location} . "?" . $self->{link}{key} . "=" . $rec->{$self->{link}{key}} . "$self->{transit_params}','" . $self->{key}{target} . "','" . $self->{nw_params} . "');";
 	    }elsif($self->{opener}){
 		my $opener_transit_params = $self->{transit_params};
 		$opener_transit_params =~ s/opener=[\w]*//g;
 		$self->{detail}{Tr}{$row_params}{$self->{link}{event}} = "opener.location.href='" . $self->{opener} . "?" . $self->{link}{key} . "=" . $rec->{$self->{link}{key}} . "$opener_transit_params'; window.close();";
 	    }elsif($self->{link}{location}){
 		$self->{detail}{Tr}{$row_params}{$self->{link}{event}} = "document.location.href='" . $self->{link}{location} . "?" . $self->{link}{key} . "=" . $rec->{$self->{link}{key}} . "$self->{transit_params}';";
 	    }
 	}
 	$HTML .= "   " . Tr ($self->{detail}{Tr}{$row_params},$row_cells) . "\n";
     }
    if($HTML){
	$HTML .= $self->print_group_item_totals($self->{groups}[0]{key},$group);
	$HTML .= "</table>\n";
	$HTML .= "    </td>\n  </tr>\n";
    }
    return $HTML;
}

sub print_detail_columns {
    my $self = shift;
    if (defined $self->{columns_width} or defined $self->{columns_headers_align}){
	my $HTML = "";
	my $it = 0;
	foreach my $label (@{$self->{columns}{labels}}){
	    $self->{detail_th}{params}{width} = $self->{columns_width}[$it] if(defined $self->{columns_width}[$it]);
	    $self->{detail_th}{params}{align} = $self->{columns_headers_align}[$it] if(defined $self->{columns_headers_align}[$it]);
	    $HTML .= th($self->{detail_th}{params},$label) . "\n";
	    $it++;
	}
	return "   " . Tr ($self->{columns}{params},$HTML);
    };
    return "   " . Tr ($self->{columns}{params},[th($self->{detail_th}{params},$self->{columns}{labels})]) . "\n";
}

sub get_detail_columns {
    my $self = shift;
    $self->{columns}{names} = ();
    $self->{columns}{labels} = ();
    $self->{colspan} = ($self->{sth}->{NUM_OF_FIELDS});
    foreach my $i(0 .. ($self->{colspan} - 1)) {
	defined $self->{sth}->{NAME}->[$i] or $self->{sth}->{NAME}->[$i] = "";
	if ($self->{sth}->{NAME}->[$i] and !($self->{link}{hidde_key_col} and $self->{sth}->{NAME}->[$i] eq $self->{link}{key}) and !$self->{group_fields_hash}{$self->{sth}->{NAME}->[$i]}){

	    push(@{$self->{columns}{names}},$self->{sth}->{NAME}->[$i]);
	    my $col_label = $self->{sth}->{NAME}->[$i];
	    $col_label =~ s/_/ /g;
	    $col_label = ucfirst($col_label);

	    #Auto order Links
	    my $side = 0;
	    $side = 1 if (!param("cg_side"));
	    if($self->{auto_order}){
		if(($i+1) eq param("cg_order")){
		    if(param("cg_side") eq "0"){
			$col_label .= ' ' . a({href=>$self->{script} . "?cg_order=".($i+1)."&cg_side=1&cg_page=" . param("cg_page") . "&cg_list=" . $self->{name} . $self->{transit_params}},$self->{labels}{link_up});
			$col_label .= $self->{labels}{link_down};
		    }elsif(param("cg_side") eq "1"){
			$col_label .= ' ' . $self->{labels}{link_up};
			$col_label .= a({href=>$self->{script} . "?cg_order=".($i+1)."&cg_side=0&cg_page=" . param("cg_page") . "&cg_list=" . $self->{name} . $self->{transit_params}},$self->{labels}{link_down});
		    }
		}else{
		    $col_label .= ' ' .a({href=>$self->{script} . "?cg_order=".($i+1)."&cg_side=1&cg_page=" . param("cg_page") . "&cg_list=" . $self->{name} . $self->{transit_params}},$self->{labels}{link_up});
		    $col_label .= a({href=>$self->{script} . "?cg_order=".($i+1)."&cg_side=0&cg_page=" . param("cg_page") . "&cg_list=" . $self->{name} . $self->{transit_params}},$self->{labels}{link_down});
		}
	    }
	    push(@{$self->{columns}{labels}},$col_label);
   	}
    }
    $self->{colspan} -= 1 if($self->{link}{hidde_key_col});
}

sub total {
    my $self = shift;
    my %params = @_;
    $self->{totals}{$params{key}} = {type => $params{type},operation=>$params{operation},label=>$params{label},format=>$params{format}};
}

sub group_total {
    my $self = shift;
    my %params = @_;
    $self->{group_totals}{$params{key}} = {type => $params{type},operation=>$params{operation},label=>$params{label},format=>$params{format}};
}

sub print_totals {
    my $self = shift;
    my $HTML = "";
    my @totals;

    foreach my $i(0 .. (($self->{colspan} - 1))) {
	if(!(defined $self->{totals}{$self->{columns}{names}[$i]})){
	    push(@totals,"");
	    next;
	}
	#Operaciones
	my $total = "";
	if(defined $self->{totals}{$self->{columns}{names}[$i]}{operation}){
	    if($self->{totals}{$self->{columns}{names}[$i]}{operation} eq "SUM"){
		$total = $self->SUM($self->{columns}{names}[$i]);
	    }elsif($self->{totals}{$self->{columns}{names}[$i]}{operation} eq "AVG"){
		$total = $self->AVG($self->{columns}{names}[$i]);
	    }elsif($self->{totals}{$self->{columns}{names}[$i]}{operation} eq "COUNT"){
		$total = $self->COUNT($self->{columns}{names}[$i]);
	    }
	}

	#Formatos
	if($self->{totals}{$self->{columns}{names}[$i]}{format} eq "price"){
	    use Number::Format;
	    my $NF = Number::Format->new(%{$self->{Number_Format}});
	    $total = $NF->format_price($total);
	}

	if($self->{totals}{$self->{columns}{names}[$i]}{label}){
	    my $total_label = $total;
	    $total = $self->{totals}{$self->{columns}{names}[$i]}{label};
	    $total =~ s/%%/$total_label/g;
	}
	push(@totals,$total);
    }

    return "   " . Tr ($self->{totals}{Tr}{params},[td($self->{totals}{td}{params},\@totals)]) . "\n";
}

sub print_group_totals {
    my $self = shift;
    my $HTML = "";
    my @totals;

    foreach my $i(0 .. (($self->{colspan} - 1))) {
	if(!(defined $self->{totals}{$self->{columns}{names}[$i]})){
	    next;
	}
	#Operaciones
	my $total = "";
	if(defined $self->{totals}{$self->{columns}{names}[$i]}{operation}){
	    if($self->{totals}{$self->{columns}{names}[$i]}{operation} eq "SUM"){
		$total = $self->SUM($self->{columns}{names}[$i]);
	    }elsif($self->{totals}{$self->{columns}{names}[$i]}{operation} eq "AVG"){
		$total = $self->AVG($self->{columns}{names}[$i]);
	    }elsif($self->{totals}{$self->{columns}{names}[$i]}{operation} eq "COUNT"){
		$total = $self->COUNT($self->{columns}{names}[$i]);
	    }
	}

	#Formatos
	if($self->{totals}{$self->{columns}{names}[$i]}{format} eq "price"){
	    use Number::Format;
	    my $NF = Number::Format->new(%{$self->{Number_Format}});
	    $total = $NF->format_price($total);
	}

	if($self->{totals}{$self->{columns}{names}[$i]}{label}){
	    my $total_label = $total;
	    $total = $self->{totals}{$self->{columns}{names}[$i]}{label};
	    $total =~ s/%%/$total_label/g;
	}
	push(@totals,$total);
    }

    return "   " . Tr ({},td({colspan=>scalar(@{$self->{group_fields_array}})},
			     '<table width="100%">' .
			     Tr ($self->{totals}{Tr}{params},[td($self->{totals}{td}{params},\@totals)]) . "\n" .
			     '</table>'
			    )) . "\n";
}

sub print_group_item_totals {
    my $self = shift;
    my $field = shift;
    my $field_value = shift;
    my $totals = "";
    foreach my $i(0 .. (($self->{colspan})- scalar(@{$self->{group_fields_array}}))) {
#	if(!(defined $self->{group_totals}{$self->{columns}{names}[$i]})){
#	    $totals .= '<td></td>';
#	    next;
#	}
	#Operaciones
	my $total = "";
	if(defined $self->{group_totals}{$self->{columns}{names}[$i]}{operation}){
	    if($self->{group_totals}{$self->{columns}{names}[$i]}{operation} eq "SUM"){
		$total = $self->group_SUM($self->{columns}{names}[$i],$field,$field_value);
	    }elsif($self->{group_totals}{$self->{columns}{names}[$i]}{operation} eq "AVG"){
		$total = $self->group_AVG($self->{columns}{names}[$i],$field,$field_value);
	    }elsif($self->{group_totals}{$self->{columns}{names}[$i]}{operation} eq "COUNT"){
		$total = $self->group_COUNT($self->{columns}{names}[$i],$field,$field_value);
	    }
	}

	#Formatos
	if($self->{group_totals}{$self->{columns}{names}[$i]}{format} eq "price"){
	    use Number::Format;
	    my $NF = Number::Format->new(%{$self->{Number_Format}});
	    $total = $NF->format_price($total);
	}

	if($self->{group_totals}{$self->{columns}{names}[$i]}{label}){
	    my $total_label = $total;
	    $total = $self->{group_totals}{$self->{columns}{names}[$i]}{label};
	    $total =~ s/%%/$total_label/g;
	}
	if(defined $self->{columns_align}){
	    $self->{group_item_totals}{td}{params}{align} = $self->{columns_align}[$i];
	    $totals .= td($self->{group_item_totals}{td}{params},$total);
	}else{
	    $totals .= td($self->{group_item_totals}{td}{params},$total);
	}
    }
    return "   " . Tr ($self->{group_item_totals}{Tr}{params},$totals) . "\n";
}

sub SUM {
    my $self = shift;
    my $field = shift || "";
    return 0 if(!$field);
    my $total = 0;
    foreach my $rec(@{$self->{rs}}) {
		$total += $rec->{$field};
    }
    return $total;
}

sub group_SUM {
    my $self = shift;
    my $field = shift || "";
    my $filter = shift;
    my $filter_value = shift;
    return 0 if(!$field);
    my $total = 0;
    foreach my $rec(@{$self->{rs}}) {
		next if($rec->{$filter} ne $filter_value);
		$total += $rec->{$field};
    }
    return $total;
}

sub COUNT {
    my $self = shift;
    my $field = shift || "";
    return 0 if(!$field);
    return scalar( @{$self->{rs}});
}

sub group_COUNT {
    my $self = shift;
    my $field = shift || "";
    my $filter = shift;
    my $filter_value = shift;
    return 0 if(!$field);
    my $total = 0;
    foreach my $rec(@{$self->{rs}}) {
		next if($rec->{$filter} ne $filter_value);
		$total += 1;
    }
    return $total;
}

sub AVG {
    my $self = shift;
    my $field = shift || "";
    return 0 if(!$field);
    my $avg = 0;
    my $it = 0;

    foreach my $rec(@{$self->{rs}}) {
		$it++;
		$avg += $rec->{$field};
    }

    eval {
		$avg = $avg / $it; 
    };

    if($@){
		$avg = "";
    }

    $avg = neares(.01,$avg);
    return $avg;
}

sub group_AVG {
    my $self = shift;
    my $field = shift || "";
    my $filter = shift;
    my $filter_value = shift;
    return 0 if(!$field);
    my $avg = 0;
    my $it = 0;

    foreach my $rec(@{$self->{rs}}) {
		next if($rec->{$filter} ne $filter_value);
		$it++;
		$avg += $rec->{$field};
    }

    eval {
		$avg = $avg / $it; 
    };

    if($@){
		$avg = "";
    }

    $avg = neares(.01,$avg);
    return $avg;
}

sub headers_groups {
    my $self = shift;
    $self->{headers_groups} = shift;
}

sub orders {
    my $self = shift;
    $self->{orders} = shift;
    if($self->{orders}->{param("cg_order")}){
	$self->{sql}{order_by} = $self->{orders}->{param("cg_order")};
	$self->{sql}{order_by} .= " DESC  " if (param("cg_side"));
	$self->{sql}{order_by} .= " ASC  " if (!param("cg_side"));
    }
}




1; # End of CGI::List