/ 
DBIx-SQLCrosstab-1.14
River stage zero No dependents
/ DBIx::SQLCrosstab::Format

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',
    op_col => '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.

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 any of as_html or as_xml methods.

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 any of as_html or as_xml methods.