Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Re: SQL Database table to Perl Spreadsheet script

by Generoso (Parson)
on Oct 14, 2012 at 08:02 UTC ( #998928=note: print w/ replies, xml ) Need Help??


in reply to SQL Database table to Perl Spreadsheet script

Maybe this is what you are looking for.

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 $l = $_; $l =~ s/\n/<br>/g; print FH '<p>'.$l.'</p>'; my $tran_cur = $dbh->prepare($_)or die "Cannot prepare: " . $DBI:: +errstr; $tran_cur->execute or die "Cannot execute: " . $DBI::errstr; print FH '<table border=1 cellspacing=1 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>"; my $r = defined ($_) ? $_ : '&nbsp;'; print FH length ($r)>0 ? $r: '&nbsp;'; print FH "</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:

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*Amount,Amount))
FROM transaction GROUP BY AccNum;


AccountDateTypeTotal_NoAmount
1814321999-07-18Credit560000.00
1814321999-07-11Debit683000.00
1814321999-07-18Balance11-23000.00

SELECT AccNum as Account, max(date) as Date, Type, 'CHEQUENO' 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;


AccountDateTypeSubtypeTotal_NoAmount
1814321999-07-03DebitCHEQUENO231000.00
1814321999-07-03DebitDDNO228000.00

desc transaction;


FieldTypeNullKeyDefaultExtra
TranIDint(10) unsignedNOPRI  
DatedateNO   
AccNumint(10) unsignedNO   
Typeenum('Credit','Debit')NO   
Amountdecimal(10,2)NO   
ChequeNoint(10) unsignedYES   
DDNoint(10) unsignedYES   


Comment on Re: SQL Database table to Perl Spreadsheet script
Download Code
Replies are listed 'Best First'.
Re^2: SQL Database table to Perl Spreadsheet script
by dinkar (Initiate) on Oct 14, 2012 at 08:17 UTC
    Sir.My question is to store the result in a spreadsheet. This is not the script I am looking for. You can make out by the code I have given for which I am looking for.
      I don't think prefixing your posts with "sir" is having the effect you want it to have.


      - Boldra

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://998928]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (7)
As of 2015-07-31 03:11 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (274 votes), past polls