Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Re: SQL Database table to Perl Spreadsheet script

by Generoso (Vicar)
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
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 wandering the Monastery: (12)
As of 2014-08-27 21:21 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (253 votes), past polls