Re: [SL] Output reports to csv

[Dr E. L. <el...@li...>]

Paul,

you assume that the accountant uses SL. Very few do. But the *DO* use Excel.

And with regards to the renaissance nonsense-man, this took me 10 
minutes, while fink was pulling the preadsheet::WriteExcel module:

#!/usr/bin/perl
use lib '/sw/lib/perl5';
use DBI;
$host = 'localhost';
####################
$dbase = "";
####################
use Spreadsheet::WriteExcel;
my $excel = new Spreadsheet::WriteExcel( "gl.xls" );
my $sheet  = $excel -> addworksheet("gl");
$db  = DBI->connect("dbi:Pg:dbname=$dbase;host=$host", $undef, $undef);
$query = qq|SELECT  c.accno, c.description, ac.transdate, g.id,
     rount(ac.amount::numeric,2), g.reference,
     g.description, substr(g.notes,1,55), ac.memo
FROM gl g
JOIN acc_trans ac ON (g.id = ac.trans_id)
JOIN chart c ON (ac.chart_id = c.id)
LEFT JOIN department d ON (d.id = g.department_id)
WHERE 1 = 1
     AND ac.transdate >= '2005-07-01'
     AND ac.transdate <= '2006-06-30'
UNION ALL
SELECT c.accno,  c.description, ac.transdate, a.id,
     round(ac.amount::numeric,2) as Amount,
     a.invnumber,
     ct.name, substr(a.notes,1,55) as Notes,
    ac.memo
FROM ar a
JOIN acc_trans ac ON (a.id = ac.trans_id)
JOIN chart c ON (ac.chart_id = c.id)
JOIN customer ct ON (a.customer_id = ct.id)
LEFT JOIN department d ON (d.id = a.department_id)
WHERE 1 = 1
    AND ac.transdate >= '2005-07-01'
    AND ac.transdate <= '2006-06-30'
UNION ALL
SELECT c.accno,  c.description, ac.transdate,  a.id,
     round(ac.amount::numeric,2) as Amount,
     a.invnumber,
     ct.name, substr(a.notes,1,55) as Notes,
    ac.memo
FROM ap a
JOIN acc_trans ac ON (a.id = ac.trans_id)
JOIN chart c ON (ac.chart_id = c.id)
JOIN vendor ct ON (a.vendor_id = ct.id)
LEFT JOIN department d ON (d.id = a.department_id)
WHERE 1 = 1
    AND ac.transdate >= '2005-07-01'
    AND ac.transdate <= '2006-06-30'
ORDER BY 1, 3, 4|;
$dbh = $db->prepare($query);		
$in = $dbh->execute or die;		
$line = 0;
while (@record = $dbh->fetchrow_array) {
     for ($column=0; $column<=7; $column++) {
	$sheet->write($line,$column,$record[$column]);	
     }
     $line++;
}

el


on 3/3/07 11:48 AM Paul Tammes said the following:
> C. Duncan Hudson schreef:
>> david wrote:
>>   
>>> On Fri, 2007-03-02 at 12:18 +0200, Dr Eberhard Lisse wrote:
>>>   
>>>     
>>>> It is almost trivial, to put this statement of mine into a perl script,
>>>> and using the appropriate CPAN module, to generate Excell or OpenOffice
>>>> spreadsheets.
>>>>
>>>>     
>>>>       
>>> It's NOT trivial. I'm sure I could manage it, and I'm sure you, being
>>> the ultimate renaissance man, could manage it too.. but I have to say
>>> that CSV export is a very sensible addition to the wish list. 
>>>   
>>>     
>> I believe it is sensible, and I would say should be required - because 
>> every other accounting package does it.  It has has been discussed many 
>> times on the list before, and we always get the 'Just cut and paste' 
>> responses.  And then eventually Dieter will chime in and say, "you have 
>> the source add it".
>>
>> Dunc
>>
>>   
> Being both a bookkeeper and a user I might be a bit prejudiced, but if 
> your accountant or bookkeeper is not able to use the sql-reports that 
> are built-in that should be reason for concern. The management can not 
> read a trial balance or a profit and loss account, that is why they like 
> to see it in excel or even better a pie-diagram or something equally simple.
> 
> If your accountant needs the same treatment, you probably hired a 
> manager and not a person with bookkeeping skills?
> Anyway, install a generic printer (yes, i am talking history) and print 
> to file should do almost any trick.
> If the copy paste to open office is too much of a hassle that is. Yes, 
> it works, open office and firefox...
> 
> Greetings from Holland,
> Paul Tammes
> 
> 
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys-and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> sql-ledger-users mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sql-ledger-users

-- 
Dr. Eberhard W. Lisse  \        / Obstetrician & Gynaecologist (Saar)
el...@li... el108-ARIN / *     |   Telephone: +264 81 124 6733 (cell)
PO Box 8421             \     /   Please do NOT email to this address
Bachbrecht, Namibia     ;____/        if it is DNS related in ANY way