#!/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;