perlquestion
dinkar
I have a transaction table like this:
<code>
TranID Date AccNum Type Amount ChequeNo DDNo
657520 02-07-1999 0181432 Debit 16000 465774
657524 02-07-1999 0181432 Debit 13000 569086
657538 09-07-1999 0181432 Credit 11000
657548 18-07-1999 0181432 Credit 15500
657519 02-07-1999 0181432 Debit 12000
657523 02-07-1999 0181432 Credit 11000
657529 03-07-1999 0181433 Debit 15000 466777
657539 10-07-1999 0181433 Credit 10000
657541 11-07-1999 0181433 Debit 12000
657525 03-07-1999 0181433 Debit 15000 569999
657533 05-07-1999 0181433 Credit 12500
</code>
My question is: Query the data from transaction table and calculate the total amount debited by cheque, dd and cash for each account and store the result in a spreadsheet? My code is like this:
<code>
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Spreadsheet::WriteExcel;
my $dbh = DBI->connect('dbi:mysql:database:3306','prithvi','prithvi') or die $dbh->errstr;
my $sth = $dbh->prepare("SELECT `AccNum`,`Type`,`Amount`,`ChequeNo`,`DDNo` FROM `transaction`");
$sth->execute or die $sth->errstr;
my $workbook = Spreadsheet::WriteExcel->new('query_result.xls');
my $worksheet = $workbook->add_worksheet();
my $row = 0;
my $col = 0;
my %h;
$worksheet->write_row($row++,$col,['Account Number','Cheque Debit','DD Debit','Cash Debit']);
while(my @data = $sth->fetchrow_array)
{
next unless($data[1] eq 'Debit');
my $result = $data[3] ? "ChequeNo" : $data[4] ? "DDNo" : "Cash";
$h{$data[0]}{$result} += $data[2];
$worksheet->write_row($row++,$col,\@data);
}
$sth->finish;
$dbh->disconnect;
</code>
I am not getting the proper output. Where am I going wrong? Please help. Thanks in advance