Maybe this is what you are looking for.
!/usr/bin/perl
use DBI;
use strict;
use warnings;
my $dbh = DBI->connect('dbi:mysql:database','user','password') or die
+"Couldn't connect";
my @sql =();
$sql[0] = qq[SELECT AccNum as Account, max(date) as Date, Type, count(
+*) as Total_No, sum(Amount) as Amount
FROM transaction GROUP BY AccNum, Type
union
SELECT AccNum, max(date),'Balance', count(*), sum(if(type='Debit',-1*A
+mount,Amount))
FROM transaction GROUP BY AccNum;];
$sql[1] = qq[SELECT AccNum as Account, max(date) as Date, Type, 'CHEQU
+ENO' as Subtype, count(*) as Total_No, sum(Amount) as Amount
FROM transaction where chequeno is not null GROUP BY AccNum, Type
union
SELECT AccNum as Account, max(date) as Date, Type, 'DDNO' as Subtype,
+count(*) as Total_No, sum(Amount) as Amount
FROM transaction where DDNO is not null GROUP BY AccNum, Type;];
$sql[2] = qq[desc transaction;];
open (FH, ">".$ENV{"TMP"}."\\mytmp.html") || die "Cannot open temporar
+y file: $!\n";
print FH '<HTML><Body>';
foreach (@sql) {
my $tran_cur = $dbh->prepare($_);
$tran_cur->execute;
print FH '<table border=1 cellspacing=0 cellpadding=2><tr>';
map {print FH "<td>$_</td>"}@{$tran_cur->{NAME}};
print FH "</tr><br/>";
while( my @data = $tran_cur->fetchrow_array)
{
print FH "<tr>";
map {print FH "<td>$_</td>"}@data;
print FH "</tr>";
}
print FH "</table>";
}
print FH "</Body></HTML>\n";
my $url = "file://".$ENV{"TMP"}."\\mytmp.html";
my $commandline = qq{start "$url" "$url"};
system($commandline) == 0
or die qq{Couldn't launch '$commandline': $!/$?};
close FH;
$dbh->disconnect;
sleep(5);
unlink $ENV{"TMP"}."\\mytmp.html" or warn "Could not unlink ".$ENV{"TM
+P"}."\\mytmp.html: $!";
Result:
Account | Date | Type | Total_No | Amount |
181432 | 1999-07-18 | Credit | 5 | 60000.00 |
181432 | 1999-07-11 | Debit | 6 | 83000.00 |
181432 | 1999-07-18 | Balance | 11 | -23000.00 |
Account | Date | Type | Subtype | Total_No | Amount |
181432 | 1999-07-03 | Debit | CHEQUENO | 2 | 31000.00 |
181432 | 1999-07-03 | Debit | DDNO | 2 | 28000.00 |
Field | Type | Null | Key | Default | Extra |
TranID | int(10) unsigned | NO | PRI | | |
Date | date | NO | | | |
AccNum | int(10) unsigned | NO | | | |
Type | enum('Credit','Debit') | NO | | | |
Amount | decimal(10,2) | NO | | | |
ChequeNo | int(10) unsigned | YES | | | |
DDNo | int(10) unsigned | YES | | | |