NAME

DBIx::SQLCrosstab::Format - Formats results created by DBIx::SQLCrosstab

SYNOPSIS

    use DBIx::SQLCrosstab::Format;
    my $dbh=DBI->connect("dbi:driver:database"
        "user","password", {RaiseError=>1})
            or die "error in connection $DBI::errstr\n";

    my $params = {
        dbh    => $dbh,
        op     => [ [ 'SUM', 'salary'] ],
        from   => 'person INNER JOIN departments USING (dept_id)',
        rows   => [
                    { col => 'country'},
                  ],
        cols   => [
                    {
                       id => 'dept',
                       value =>'department',
                       from =>'departments'
                    },
                    {
                        id => 'gender', from => 'person'
                    }
                  ]
    };
    my $xtab = DBIx::SQLCrosstab::Format->new($params)
        or die "error in creation ($DBIx::SQLCrosstab::errstr)\n";

    my $query = $xtab->get_query("#")
        or die "error in query building $DBIx::SQLCrosstab::errstr\n";

    if ( $xtab->get_recs) {
        # do something with records, or use a built-in function
        # to produce a well formatted HTML table
        #
        print $xtab->as_html;

        print $xtab->as_xml;
        print $xtab->as_yaml;
        print $xtab->as_csv('header');
        $xtab->as_xls("xtab.xls");
        use Data::Dumper;
        print Data::Dumper->Dump ([ $xtab->as_perl_struct('hoh')],
                ['hoh']);
        print Data::Dumper->Dump ([ $xtab->as_perl_struct('losh')],
                ['losh']);
        print Data::Dumper->Dump ([ $xtab->as_perl_struct('loh')],
                ['loh']);
    }
    else {
        die "error in execution $DBIx::SQLCrosstab::errstr\n";
    }

DESCRIPTION

DBIx::SQLCrosstab::Format is a class descending from DBIx::SQLCrosstab. Being a child class, it inherits its parent methods and can be used in the same way.

In addition, it provides methods to produce formatted output.

Class methods

new
get_recs
get_query

See DBIx::SQLCrosstab docs for usage and a detailed list of parameters

as_html

Returns a formatted HTML table with headers and values properly inserted, or undef on failure.

as_xml

Returns an XML document containing the whole recordset properly tagged in tree format, or undef on failure.

as_xls($filename)

Creates a MS Excel spreadsheet using Spreadsheet::WriteExcel. Requires a filename (or "-" for stdout).

as_perl_struct($mode)

Returns the recordset as a Perl structure. $mode is one of the following: - lol List of lists - losh List of simple hashes (one key per column) - loh List of hashes, tree-like, with an appropriate tree for each row - hoh Hash of hashes. The resultset as a tree (useful to pass to either XML::Simple or YAML)

as_yaml

Returns the recordset in YAML format. You must have YAML installed for this method to work.

as_csv($headers)

Returns a text of Comma Separated Values, where each value is surronded by double quotes (text) or bare (numbers). If a true value is passed as $header parameter, the first row contains the list of column names, properly quoted and escaped.

Class attributes

In addition to the attributes available in DBIx::SQLCrosstab, the folowing ones become available in this class. They may be useful if you want to implement your own output methods.

Extending DBIx::SQLCrosstab::Format

The appropriate way of extending this class is through inheritance. Just create a descendant of DBIx::SQLCrosstab::Format and implement your new methods. The attributes with the relevant information become available after a call to the private method _find_headers().

The path to extension is something like the following.

First, create a new module:

 package DBIx::SQLCrosstab::Format::Extended;
 use DBI;
 use DBIx::SQLCrosstab;

 our $VERSION = '0.1';
 require Exporter;
 our @ISA= qw(DBIx::SQLCrosstab::Format);
 our @EXPORT=qw();
 our @EXPORT_OK=qw();

 sub as_myformat {
    my $self = shift;
    return undef unless $self->_find_headers();
    my $new_format = 
    do_something_smart_with($self->{recs_tree},
                      $self->{header_formats});
    return $new_format;
 }

 sub do_something_smart_with {
    my $recs_tree = shift;
    my $header_formats = shift;
    # show off your skills here
 }

 1;

Then, use the new module as you would use the parent one.

    use DBIx::SQLCrosstab::Format::Extended;
    my $dbh=DBI->connect("dbi:driver:database"
        "user","password", {RaiseError=>1})
            or die "error in connection $DBI::errstr\n";

    my $xtab = DBIx::SQLCrosstab::Format::Extended->new($params)
        or die "error in creation ($DBIx::SQLCrosstab::errstr)\n";

    my $query = $xtab->get_query("#")
        or die "error in query building $DBIx::SQLCrosstab::errstr\n";

    if ( $xtab->get_recs) {
        print $xtab->as_myformat;
    }
{header_formats}

Contains a reference to an array of arrays, one for each level of headers. Each cell is described with a hash containig name, colspan and rowspan values. Available after a call to _find_headers().

{recs_formats}

Contains a refernce to a hash descrbing the structure of the row level. Each level contains a list of fields and relative rowspans. Available after a call to _find_headers().

{recs_tree}

Contains a Tree::DAG_Node object with the structure of the column headers. Available after a call to _find_headers().

{header_tree}

Contains a Tree::DAG_Node object with the structure of the row headers. Available after a call to _find_headers().

SEE ALSO

DBIx::SQLCrosstab

An article at OnLamp, "Generating Database Server-Side Cross Tabulations" (http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html) and one at PerlMonks, "SQL Crosstab, a hell of a DBI idiom" (http://www.perlmonks.org/index.pl?node_id=313934).