A Simple PDF Report

How to generate a PDF document that reports a set of data from a database?

The program is written in Perl but behind the scene, various other stuff are used.
DBI to connect the PostgreSQL database through DBD::Pg
Inline::Java to access the iText library (Java .jar file)

The output may look dirty but we are able to write the script very quickly.
And, loading the java interpreter may initially take some time.

But don’t forget that this script can easily be developed to a good reporting framework customized for your organization.

This script demonstrates why the Perl is called a glue language (or a duct tape).

use strict;
use warnings;

use DBI;
use Inline (
        JAVA=>'STUDY',
        CLASSPATH=>'/usr/share/java/itext5.jar',
        STUDY=>[
            'com.itextpdf.text.Document',
            'com.itextpdf.text.pdf.PdfWriter',
            'java.io.FileOutputStream',
            'com.itextpdf.text.pdf.PdfPTable',
        ]
);

sub get_rows {
    my $qry = shift;
    my $con = DBI->connect(
                    "DBI:Pg:dbname=mydb;host=0.0.0.0",
                    "mydb", "mydb", {AutoCommit => 0})
                or die "Error $DBI::err [$DBI::errstr]";
    my $stm = $con->prepare($qry);
    $stm->execute;
    my $rows_ref = $stm->fetchall_arrayref;
    $con->disconnect;
    return $rows_ref;
}

sub gen_pdf_report {
    my ($fname, $numcols, $colnames_ref, $rows_ref) = @_;
    my $doc = new com::itextpdf::text::Document();
    com::itextpdf::text::pdf::PdfWriter->getInstance($doc, new java::io::FileOutputStream($fname));
    $doc->open;
    my $pdftable = new com::itextpdf::text::pdf::PdfPTable($numcols);
    for my $cell (@$colnames_ref) {
        $pdftable->addCell($cell);
    }
    for my $row (@$rows_ref) {
        for my $cell (@$row) {
            $pdftable->addCell($cell);
        }
    }
    $doc->add($pdftable);
    $doc->close;
}

sub MAIN {
    gen_pdf_report(
        'itext_pg_emp.pdf',
        4,
        ['Dept No', 'Dept Name', 'Emp No', 'Emp Name'],
        get_rows(qq{
            select dept.deptno, dname, empno, ename
              from emp, dept
             where dept.deptno = emp.deptno
             order by dname, ename
        })
    );
}

MAIN();

Happy Programming!

P.S. How about generating the report with PDF::API2 or PDF::Report?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s