DBIx::Repgen - simple report generator from DB-selected data
use Repgen;
$r = DBIx::Repgen->new(
dbh => DBI->connect(...),
query => 'select ... from ...',
repdata => {
today => `date`
},
group => ['id'],
header => "========\n",
footer => sub {my ($r, $data) = @_; return "$data->{NAME} : $data->{VALUE}"},
item => ["%20s %s", qw/NAME VALUE/],
output => \$out;
);
$r->run(cust => 'tolik'); print $out;
This package implements class DBIx::Repgen, which is simple report generator from data
received from relational database by some select-statement. Such a report can contain
hyerarchical grouping by field values, record counters and cumulative totals (sums) of numeric
fields for each group as well as for whole report. Each rerort part formatting may be set
as literal string, arguments of sprint function or be code reference.
Constructor has one argument, hashe. Elements of this hashe define the report and are descriebed below.
The report data are got by executing some select statement against relational database environment. There are following wais for defining this statement.
Constructor receives in sth element prepared ($dbh-prepare>) but not executed
($sth-execute>) statement handle.
Constructor receives database connection handle (from DBI-connect(...)>) and full text
of select statement to be executed. Needed prepare and execute calls will perform
by the report run.
Constructor receives already prepared and executed statement handle. In this case noexec
constructor parameter must be set to true. This feature may be useful by dynamic-made select
queryes in calling programm. No prepare nor execute action will be performed by report run.
Important note: you have to reset (by Set method) this statemeny handle before each next
report run.
Samples:
$dbh = DBI->connect('dbi:Oracle:SID', 'user', 'password'); $sth1 = $dbh->prepare('select name, value from tab where value between ? and ?'); $rep1 = DBIx::Repgen->new(sth => $sth);
$rep2 = DBIx::Repgen->new(dbh => $dbh, query => "select ... ");
$sth3 = $dbh->prepare('select ...'); $sth3->execute(@param); $rep3 = DBIx::Repgen->new(sth => $sth3, noexec => 1);
Using first two methods you may parametrize the report. This means sql-query can contain placeholders, for substituting values in report run time. See below about report parameters.
The report may have set of named parameters. Single parameter definition contain its name,
number (or some numbers) of placeholders in source select query and optional default value.
Parametrs definition is a hash reference, value of param element of constructor. Keys in this
hash are parameter names and values contain placeholder numbers and default values.
In the simpliest case parameter definition can be just zero-based number of the only placeholder
corresponding to this parameter. In more complex cases is is hash reference. This hash must
have n key with value of integer or list of integers and may have dflt key, which
value must be scalar, code reference or array reference (where first element is code reference).
The n key defines zero based number (or numbers) of placeholdes in source select query
corresponding to this parameter. The dflt key defines default value for optional
parameters. If value of dflt is code reference then default value is result of this code call (without
arguments). If value of dflt is array reference then first element of this array must
be code reference. Default value of parameter in this case is result of call this code with arguments -
the rest of array.
Sample of parameter definition.
$rep = DBIx::Repgen->new( ... param => { name => 0, dep => {n => 1}, startdate => {n => [2, 4], dflt => '2000/01/01'}, enddate => {n => 3, dflt => \&DefEndDate}, salary => {n => 5, dflt => [sub {...}, 1000, 2000]} } );
In the example name and dep are required parameters corresponding to zero and first placeholders.
startdate has explicit default value and substituted to second and fouth placeholders.
enddate and salary have defaults defining by code call in report run time, without and
with arguments in correspondence.
The output constructor's parameter sets how and where the report puts its output data.
The whole output data are the result of run method call.
The output data are put into this string.
This code will be called with two arguments: the report object and string to be out.
Output samples.
$r = DBIx::Repgen(...); print $r->run();
$s = ''; $r = DBIx::Repgen(..., output => \$s,); $r->run(); print $s;
sub myprint { my ($r, $s) = @_; print "*** $s ***"; } $r = DBIx::Repgen(..., output => \&myprint,); $r->run();
The report may be groupped. The group is sequence of records having the constant value of some
field. This field called group field. The report may have several includded groups. For
group setting you have to define group parameter of report constructor as a reference to
an array of group fields.
Note that the right record's sequence must be provided by order part in used select query, not
by report itself. Sample of grouping by countries and cities.
$r = DBIx::Repgen->new( ..., query => "select country, city, population from cities order by country, city", group => [qw/COUNTRY CITY/], ... );
Note all field names are in uppercase, regardless used database server.
Value of this argument of constructor is reference to array with report fields to compute totals. Each field summation executed for all the report as well as for each group. See below about access to totals values.
There are following parts generated during report output.
Outputs for each record of the report.
Begin of whole report.
Outputs after all, in the very end of report.
Outputs in the begin of record group by GROUPFIELD field.
Outputs after record group by GROUPFIELD field.
Each of these report pats may be defined by several ways.
The string will be printed "as is", without any processing.
$r = DBIx::Repgen->new( header => "\t\tReport about countries and cities\n", ... );
First element of this array have to be in form of sprintf function format. The rest
of values in the array are names (not values!) of current report data. See below
about current report data.
$r = DBIx::Repgen->new( footer => ["Total %d countries, %d cities, population %d people\n", qw/num_COUNTRY num_CITY sum_POPULATION/], ... );
The code is called with two arguments: report object and hash reference storing
current report data. Subroutine may use Output method for output any needed
information or just return output string as its result.
$r = DBIx::Repgen->new( item => sub { my ($r, $d) = @_; $r->Output("%d %s", $d->{POPULATION}, $d->{POPULATION} > 1_000_000 ? '*' : ' '); }
footer => sub {return "Report ended at " . `date`} ... );
The code is called with following arguments: report object, current report data, the rest of array elements.
$r = DBIx::Repgen->new( header_COUNTRY => [\&hfcountry, 'header'], header_COUNTRY => [\&hfcountry, 'footer'], ... );
sub hfcountry { my ($r, $d, $header_or_footer) = @_; if ($header_or_footer eq 'header') {...} else {...}; }
If this parameter (integer number) is present then no more than max_items records will be
output. It is possible to know via Aborted method call if not all records were output.
All report state data are stored in internal report variables. Access to these data from report parts is possible by data names. There are following fields in current report data.
Fields of current report's record. Name is in uppercase.
Value of FIELDNAME in previous record. When group boundary is detected group field has new value, but its previous value is still stored. This value can be used in group footers.
Number (one-based) of current output record for the whole report. This counter never resets.
Number of record in the innermost group.
Number of group GROUPNAME in including group.
Cumulative total of FIELDNAME field for the whole report. Remember FIELDNAME must be listed
in total constructor's parameter.
Cumulative total by FIELDNAME field into GROUPNAME. These summators are reset each time the group boundary is reached.
$r->run(%param);
The report is run and output. Input parameters are substituted as values for select query
placeholders (see above about report's parameters). If there was no output constructor's parameter
then the text of report returned as a result of this method.
$r->Output("Any values: %s and %d", 'qazwsx', 654);
This method has the same arguments as sprintf function. It adds formatted string to the output
stream (set by output param). This method is useful in the code called during the output of
report parts.
@group = @{$r->Get('group')};
Method returns value of named parameter which is set in constructor or via Set method.
$r->Set( header => "Very new header", item => ["%s %s", qw/NAME VALUE/] );
Method redefines report parameters.
$r->Abort();
Being called in the code it breaks report running.
if ($r->Aborted()) {...}
Method returns true if report execution was aborted by Abort method.
Full example of report and its result. The data are taken from data table having following structure.
create table population ( country varchar2(30) not null, city varchar2(30) not null, population int not null );
Full text of the perl script and output data are following. These are just demo data!
#!/usr/bin/perl -w
use strict; use DBI; use DBIx::Repgen;
my $dbh = DBI->connect('dbi:Oracle:SID', 'user', 'password') or die $@; my $sth = $dbh->prepare(<<EOM); select country, city, population from population order by country, city EOM
my $r = DBIx::Repgen->new ( sth => $sth, group => [qw/COUNTRY/], total => [qw/POPULATION/],
header => [\&makeheader,
'=', "Countries, cities and thier population"],
footer => ["Total %d countries, %d cities, %d people\n",
qw/num_COUNTRY num_report total_POPULATION/],
header_COUNTRY => sub { my (undef, $d) = @_; return makeheader(undef, undef, '-', $d->{COUNTRY}); }, footer_COUNTRY => ["%d cities, %d people in %s\n\n", qw/num_item total_COUNTRY_POPULATION prev_COUNTRY/],
item => ["\t\t%-20s %10d\n", qw/CITY POPULATION/], );
print $r->run();
sub makeheader { my (undef, undef, $c, $s) = @_; return sprintf("%s\n%s\n%s\n", $c x length($s), $s, $c x length($s)); }
======================================
Countries, cities and thier population
======================================
---------
Australia
---------
Kanberra 900000
Sidney 6400000
2 cities, 7300000 people in Australia
------
Russia
------
Moscow 9500000
Rostov-on-Don 1200000
St.Petersberg 4500000
Taganrog 250000
4 cities, 15450000 people in Russia
---
USA
---
Los Angeles 4000000
New York 12000000
Washington 2000000
3 cities, 18000000 people in USA
Total 3 countries, 9 cities, 40750000 people