File Coverage

test/test_extended.pl

Criterion	Covered	Total	%
statement	15	15	100.0
branch			n/a
condition			n/a
subroutine	5	5	100.0
pod			n/a
total	20	20	100.0

#!/usr/bin/perl -w
use strict;
use DBI;
use DBIx::SQLCrosstab 1.17;
use DBIx::SQLCrosstab::Format 0.07;
use Data::Dumper;

my $dbh;

my $driver = shift || 'SQLite';

if ($driver eq 'SQLite') {
    $dbh = DBI->connect("dbi:SQLite:test/crosstab.sqlite",
    "","",{RaiseError=>1, PrintError=> 0 });
}
elsif($driver eq 'mysql') {
    # Adjust host, username, and password according to your needs
    $dbh = DBI->connect("dbi:mysql:crosstab; host=localhost"
	    . ";mysql_read_default_file=$ENV{HOME}/.my.cnf"  # only Unix. Remove this line for Windows
        ,  undef,  # username
           undef,  # password
          {RaiseError=>1, PrintError=> 0 })
}
else {
    die "You need a connection statement for driver <$driver>\n";
}
$dbh or die "Error in connection [ driver $driver ] ($DBI::errstr)\n";

my $params = {
    dbh            => $dbh,
    op             => [['COUNT','person_id'], [ 'SUM', 'salary']],
    title          => 'TBD',
    title_in_header=> 1,
    remove_if_null => 1,        # remove columns with all nulls
    remove_if_zero => 1,        # remove columns with all zeroes
    add_colors     => 1,        # distinct colors for string and numbers
    add_real_names => 1,        # real column name as comment in query
    col_total      => 1,
    col_sub_total  => 1,
    row_total      => 1,
    row_sub_total  => 1,
    commify        => 1,        # add thousand separating commas in numbers
    rows           =>
        [
         { col => 'CASE WHEN country="Italy" THEN "S" ELSE "N" END', alias => 'Area' },
         { col => 'country'},
         { col => 'loc',     alias => 'location' }
        ],
    cols           =>
        [
         {
           id    => 'dept_id',
           value => 'department',
           from  => 'xtab_departments'
         },
         {
           id    => 'cat_id',
           value => 'category',
           from  => 'xtab_categories'
         },
         {
           id       => 'gender',
           col_list => [ {id=>'f'}, {id =>'m'}],
           from     => 'xtab_person'
         },
        ],

    from           =>
        qq{xtab_person
            INNER JOIN xtab_locations
                ON (xtab_person.loc_id=xtab_locations.loc_id)
            INNER JOIN xtab_countries
                ON (xtab_countries.country_id=xtab_locations.country_id)
            },
};

$params->{title} =  "personnel by "
        . (join "/", map {exists $_->{alias} ?
                        $_->{alias} : $_->{col}} @{$params->{rows}} )
        . " and "
        . (join "/", map {exists $_->{value} ?
                        $_->{value} : $_->{id}} @{$params->{cols}} );

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

my $query = $xtab1->get_query ('#')
    or die "$DBIx::SQLCrosstab::errstr\n";

my $recs = $xtab1->get_recs
    or die "$DBIx::SQLCrosstab::errstr\n";

my @rows = (
            #{ col     => 'loc', alias => 'location'},
            { col     => 'customer' },
            #{ col     => 'class_name', alias =>'class'}
);

#
# Add a database-dependent expression
#
if ($driver eq 'mysql') {
    unshift @rows,
        { col     => qq{date_format(sale_date,"%Y-%m")},
          alias   => "'yyyy-mm'" };
}
elsif ($driver eq 'SQLite') {
    unshift @rows,
        { col     => qq{substr(sale_date,1,7)},
          alias   => "yyyy_mm" };
}

#
# Using the alternative params setting
#

#
# First, create a dummy object
#
my $xtab2 =  DBIx::SQLCrosstab::Format->new('STUB')
    or die "error in \$xtab2 creation ($DBIx::SQLCrosstab::errstr)\n";

#
# Then, pass parameters to it. You can do it one-by-one ...
#
$xtab2->set_param( dbh => $dbh )
    or die "error adding mandatory parameters ($DBIx::SQLCrosstab::errstr)";

#
# ... or several ones at once
#
$xtab2->set_param(
   op      => [ ['SUM', 'sale_amount'] ],
   # op_col  => 'sale_amount',
   rows    => \@rows,
   cols    => [
                {
                    id    => 'country_id',
                    value => 'country',
                    from  => 'xtab_countries'
                },
                {
                    id    => 'xtab_person.person_id',
                    value => 'name',
                    from  => 'xtab_person'
                },
                {
                    id    => 'xtab_class.class_id',
                    value => 'class_name',
                    from  => 'xtab_class'
                },
              ],
  from    =>
            qq{xtab_sales
                INNER JOIN xtab_customers
                    ON (xtab_sales.customer_id=xtab_customers.customer_id)
                INNER JOIN xtab_person
                    ON (xtab_sales.person_id=xtab_person.person_id)
                INNER JOIN xtab_class
                    ON (xtab_sales.class_id=xtab_class.class_id)
                INNER JOIN xtab_locations
                    ON (xtab_locations.loc_id=xtab_person.loc_id)
                  },
)
    or die "error adding mandatory parameters ($DBIx::SQLCrosstab::errstr)";

$xtab2->set_param(
  title          => 'Sales',
  remove_if_null => 1,
  remove_if_zero => 1,
  use_real_names => 1,
  add_colors     => 1,
  col_total      => 1,
  col_sub_total  => 1,
  row_total      => 1,
  row_sub_total  => 1,
  commify        => 1,
  table_border   => 3,
  header_color   => "#009999",
  text_color     => "#3399cc",
  number_color   => "#ff00ff",
  footer_color   => "#33cc33",
)
    or die "error adding optional parameters ($DBIx::SQLCrosstab::errstr)";

#
# Check that everything is OK.
# Alternatively, you can save the query and the recordset
# to a variable for further use
#

unless ($xtab2->get_query and $xtab2->get_recs) {
    die "$DBIx::SQLCrosstab::errstr";
}

#
# Save the current parameters to a file
#
$xtab2->save_params("test/xtab2.pl") or die "$DBIx::SQLCrosstab::errstr";

#
# Create a third object
#
my $xtab3 = DBIx::SQLCrosstab::Format->new('STUB')
    or die "error in \$xtab3 creation ($DBIx::SQLCrosstab::errstr)\n";

#
# Use the saved parameters to set it up
#
$xtab3->load_params("test/xtab2.pl")
    or die "\$xtab3 -> $DBIx::SQLCrosstab::errstr";
$xtab3->set_param( dbh => $dbh)
    or die "\$xtab3 -> $DBIx::SQLCrosstab::errstr";

unless ($xtab3->get_query and $xtab3->get_recs) {
    die "$DBIx::SQLCrosstab::errstr";
}

my $fname = 'table00';

for my $xt (($xtab1, $xtab2, $xtab3)) {
    $fname++;
    #
    # create a html example
    #
    open HTML, ">test/$fname.html"
        or die "can't create $fname.html\n";
    print HTML  $xt->html_header;
    print HTML  "",$xt->op_list, " FROM ", $xt->{title}, "";

    my $table = $xt->as_html;
    $table =~ s/\bzzzz\b/total/g;
    print HTML $table;
    my $bare_table = $xt->as_bare_html;
    $bare_table =~ s/\bzzzz\b/total/g;
    print HTML "\n",$bare_table;
    print HTML $xt->html_footer;
    close HTML;
    print "$fname.html created\n";

    #
    # create a xml example
    #
    my $xml = $xt->as_xml
        or die "$DBIx::SQLCrosstab::errstr";
    open XML, ">test/$fname.xml"
        or die "can't create $fname.xml";
    print XML $xml;
    close XML;
    print "$fname.xml created\n";

    #
    # create a xls example (requires Spreadsheet::WriteExcel)
    #
    eval { require Spreadsheet::WriteExcel; } ;
    # only if Spreadsheet::WriteExcel is installed
    if ($@) {
        print "Spreadsheet::WriteExcel not installed - test skipped\n"
    }
    else {
        if ( $xt->as_xls("test/$fname.xls", "both") ) {
            print "$fname.xls created\n";
        }
        else {
            print "$DBIx::SQLCrosstab::errstr\n";
        }
    }
    #
    # create a csv example
    #
    open CSV, ">test/$fname.csv"
        or die "can't create $fname.csv\n";
    my $csv = $xt->as_csv('header')
        or die "$DBIx::SQLCrosstab::errstr\n";
    print CSV $csv;
    close CSV;
    print "$fname.csv created\n";

    #
    # create a yaml example
    #
    eval { require YAML; };
    # only if YAML is installed
    if ($@) {
        print "YAML not installed - test skipped\n";
    }
    else {
        open YAML, ">test/$fname.yaml"
            or die "can't create $fname.yaml\n";
        my $yaml = $xt->as_yaml;
        if ($yaml) {
             print YAML $yaml;
        }
        else {
             print "$DBIx::SQLCrosstab::errstr\n";
        }
        close YAML;
        print "$fname.yaml created\n" if $yaml;
    }
    #
    # create a sample of generated Perl structures
    #
    open STRUCT, ">test/$fname.pl"
        or die "can't create $fname.pl\n";
    local $Data::Dumper::Indent=1;
    print STRUCT Data::Dumper->Dump(
          [
          $xt->as_perl_struct('loh'),
          $xt->as_perl_struct('losh'),
          $xt->as_perl_struct('hoh')
          ],
          ['loh','losh','hoh']);
    close STRUCT;
    print "$fname.pl created\n";
    #print map {"$_\n"} @{ $xt->{header_tree}->draw_ascii_tree};
    #print map {"$_\n"} @{ $xt->{recs_tree}->draw_ascii_tree};
    #print YAML::Dump( $xt->{header_tree});
    #print YAML::Dump($xt->{recs_tree});
    #print Dumper($xt->{recs_formats});
    #print Dumper($xt->{header_formats});
}