From bfdf20b31cbb60b5d92c82ecd3a5192059a0c7b1 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Thu, 3 Jan 2013 11:44:55 -0500 Subject: Updated sorting function based on advice of auditing accountants. Our auditing accounts tell us they want accounts sorted by: Assets Liabilities Net Assets Income Expenses in a general ledger report. Generally, I think we should just apply the same sorting. Since Ledger doesn't use account codes by default, this is my hack to solve this problem for now. Maybe there should be an account code tag for sorting purposes at least? --- .../general-ledger-report.plx | 46 ++++++++++++++++------ 1 file changed, 33 insertions(+), 13 deletions(-) (limited to 'contrib/non-profit-audit-reports/general-ledger-report.plx') diff --git a/contrib/non-profit-audit-reports/general-ledger-report.plx b/contrib/non-profit-audit-reports/general-ledger-report.plx index d1c92975..66fc0031 100755 --- a/contrib/non-profit-audit-reports/general-ledger-report.plx +++ b/contrib/non-profit-audit-reports/general-ledger-report.plx @@ -4,8 +4,8 @@ # Script to generate a General Ledger report that accountants like # using Ledger. # -# Copyright (C) 2011, 2012 Bradley M. Kuhn -# Copyright (C) 2012 Tom Marble +# Copyright (C) 2011, 2012, 2013 Bradley M. Kuhn +# Copyright (C) 2012 Tom Marble # # This program gives you software freedom; you can copy, modify, convey, # and/or redistribute it under the terms of the GNU General Public License @@ -67,18 +67,38 @@ close(CHART_DATA); die "error reading ledger output for chart of accounts: $!" u open(CHART_OUTPUT, ">", "chart-of-accounts.txt") or die "unable to write chart-of-accounts.txt: $!"; print MANIFEST "chart-of-accounts.txt\n"; +sub preferredAccountSorting ($$) { + if ($_[0] =~ /^Assets/) { + return -1; + } elsif ($_[1] =~ /^Assets/) { + return 1; + } elsif ($_[0] =~ /^Liabilities/ and $_[1] !~ /^Assets/) { + return -1; + } elsif ($_[1] =~ /^Liabilities/ and $_[0] !~ /^Assets/) { + return 1; + } elsif ($_[0] =~ /^(Accrued)/ and $_[1] !~ /^(Assets|Liabilities)/) { + return -1; + } elsif ($_[1] =~ /^(Accrued)/ and $_[0] !~ /^(Assets|Liabilities)/) { + return 1; + } elsif ($_[0] =~ /^(Unearned Income)/ and $_[1] !~ /^(Assets|Liabilities|Accrued)/) { + return -1; + } elsif ($_[1] =~ /^(Unearned Income)/ and $_[0] !~ /^(Assets|Liabilities|Accrued)/) { + return 1; + } elsif ($_[0] =~ /^Income/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { + return -1; + } elsif ($_[1] =~ /^Income/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { + return 1; + } elsif ($_[0] =~ /^Expense/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Expense)/) { + return -1; + } elsif ($_[1] =~ /^Expense/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Expense)/) { + return 1; + } else { + return $_[0] cmp $_[1]; + } +} + my @sortedAccounts; -foreach my $acct ( - # Proper sorting for a chart of accounts - sort { - if ($a =~ /^Assets/ and $b !~ /^Assets/) { - return -1; - } elsif ($a =~ /^Liabilities/ and $b !~ /^Liabilitie/) { - return -1; - } else { - return $a cmp $b; - } - } @accounts) { +foreach my $acct ( sort preferredAccountSorting @accounts) { print CHART_OUTPUT "$acct\n"; push(@sortedAccounts, $acct); } -- cgit v1.2.3 From f01ddd4766741cd0dc09fd42cb7cc87f4dadbb20 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Thu, 3 Jan 2013 12:19:28 -0500 Subject: Change chart of accounts output to be a CSV file instead of TXT file. This includes adding a formatted start date string too. --- .../general-ledger-report.plx | 30 +++++++++++++++------- 1 file changed, 21 insertions(+), 9 deletions(-) (limited to 'contrib/non-profit-audit-reports/general-ledger-report.plx') diff --git a/contrib/non-profit-audit-reports/general-ledger-report.plx b/contrib/non-profit-audit-reports/general-ledger-report.plx index 66fc0031..3c9ec74d 100755 --- a/contrib/non-profit-audit-reports/general-ledger-report.plx +++ b/contrib/non-profit-audit-reports/general-ledger-report.plx @@ -44,10 +44,24 @@ if (@ARGV < 3) { print STDERR "usage: $0 \n"; exit 1; } + + open(MANIFEST, ">", "MANIFEST") or die "Unable to open MANIFEST for writing: $!"; my($beginDate, $endDate, @otherLedgerOpts) = @ARGV; +my $formattedEndDate = new Date::Manip::Date; +die "badly formatted end date, $endDate" if $formattedEndDate->parse($endDate); +my $oneDayLess = new Date::Manip::Delta; +die "bad one day less" if $oneDayLess->parse("- 1 day"); +$formattedEndDate = $formattedEndDate->calc($oneDayLess); +$formattedEndDate = $formattedEndDate->printf("%Y/%m/%d"); + +my $formattedBeginDate = new Date::Manip::Date; +die "badly formatted end date, $endDate" if $formattedBeginDate->parse($endDate); +$formattedBeginDate = $formattedBeginDate->printf("%Y/%m/%d"); + + my(@chartOfAccountsOpts) = ('-V', '-F', "%150A\n", '-w', '-s', '-b', $beginDate, '-e', $endDate, @otherLedgerOpts, 'reg'); @@ -64,8 +78,12 @@ while (my $line = ) { } close(CHART_DATA); die "error reading ledger output for chart of accounts: $!" unless $? == 0; -open(CHART_OUTPUT, ">", "chart-of-accounts.txt") or die "unable to write chart-of-accounts.txt: $!"; -print MANIFEST "chart-of-accounts.txt\n"; +open(CHART_OUTPUT, ">", "chart-of-accounts.csv") or die "unable to write chart-of-accounts.csv: $!"; +print MANIFEST "chart-of-accounts.csv\n"; + +print CHART_OUTPUT "\"CHART OF ACCOUNTS\","; +print CHART_OUTPUT "\"BEGINNING:\",\"$formattedBeginDate\","; +print CHART_OUTPUT "\"ENDING:\",\"$formattedEndDate\"\n"; sub preferredAccountSorting ($$) { if ($_[0] =~ /^Assets/) { @@ -99,17 +117,11 @@ sub preferredAccountSorting ($$) { my @sortedAccounts; foreach my $acct ( sort preferredAccountSorting @accounts) { - print CHART_OUTPUT "$acct\n"; + print CHART_OUTPUT "\"$acct\"\n"; push(@sortedAccounts, $acct); } close(CHART_OUTPUT); die "error writing to chart-of-accounts.txt: $!" unless $? == 0; -my $formattedEndDate = new Date::Manip::Date; -die "badly formatted end date, $endDate" if $formattedEndDate->parse($endDate); -my $oneDayLess = new Date::Manip::Delta; -die "bad one day less" if $oneDayLess->parse("- 1 day"); -$formattedEndDate = $formattedEndDate->calc($oneDayLess); -$formattedEndDate = $formattedEndDate->printf("%Y/%m/%d"); open(GL_TEXT_OUT, ">", "general-ledger.txt") or die "unable to write general-ledger.txt: $!"; print MANIFEST "general-ledger.txt\n"; -- cgit v1.2.3 From e317e1f23e236a2797b13113bc2afae14502fc5c Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Fri, 4 Jan 2013 10:18:41 -0500 Subject: Sort of accounts was buggy; it never made the final else due to bad regexes. This fix now has the sort working correctly. --- .../general-ledger-report.plx | 29 +++++++++++++--------- .../non-profit-audit-reports/summary-reports.plx | 28 ++++++++++++--------- 2 files changed, 33 insertions(+), 24 deletions(-) (limited to 'contrib/non-profit-audit-reports/general-ledger-report.plx') diff --git a/contrib/non-profit-audit-reports/general-ledger-report.plx b/contrib/non-profit-audit-reports/general-ledger-report.plx index 3c9ec74d..a464053b 100755 --- a/contrib/non-profit-audit-reports/general-ledger-report.plx +++ b/contrib/non-profit-audit-reports/general-ledger-report.plx @@ -86,35 +86,40 @@ print CHART_OUTPUT "\"BEGINNING:\",\"$formattedBeginDate\","; print CHART_OUTPUT "\"ENDING:\",\"$formattedEndDate\"\n"; sub preferredAccountSorting ($$) { - if ($_[0] =~ /^Assets/) { + if ($_[0] =~ /^Assets/ and $_[1] !~ /^Assets/) { return -1; - } elsif ($_[1] =~ /^Assets/) { + } elsif ($_[1] =~ /^Assets/ and $_[0] !~ /^Assets/) { return 1; - } elsif ($_[0] =~ /^Liabilities/ and $_[1] !~ /^Assets/) { + } elsif ($_[0] =~ /^Liabilities/ and $_[1] !~ /^(Assets|Liabilities)/) { return -1; - } elsif ($_[1] =~ /^Liabilities/ and $_[0] !~ /^Assets/) { + } elsif ($_[1] =~ /^Liabilities/ and $_[0] !~ /^(Assets|Liabilities)/) { return 1; - } elsif ($_[0] =~ /^(Accrued)/ and $_[1] !~ /^(Assets|Liabilities)/) { + } elsif ($_[0] =~ /^(Accrued:[^:]+Receivable)/ and $_[1] !~ /^(Assets|Liabilities|Accrued:[^:]+Receivable)/) { return -1; - } elsif ($_[1] =~ /^(Accrued)/ and $_[0] !~ /^(Assets|Liabilities)/) { + } elsif ($_[1] =~ /^(Accrued:[^:]+Receivable)/ and $_[0] !~ /^(Assets|Liabilities|Accrued:[^:]+Receivable)/) { return 1; - } elsif ($_[0] =~ /^(Unearned Income)/ and $_[1] !~ /^(Assets|Liabilities|Accrued)/) { + } elsif ($_[0] =~ /^(Accrued)/ and $_[1] !~ /^(Assets|Liabilities|Accrued)/) { return -1; - } elsif ($_[1] =~ /^(Unearned Income)/ and $_[0] !~ /^(Assets|Liabilities|Accrued)/) { + } elsif ($_[1] =~ /^(Accrued)/ and $_[0] !~ /^(Assets|Liabilities|Accrued)/) { return 1; - } elsif ($_[0] =~ /^Income/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { + } elsif ($_[0] =~ /^(Unearned Income)/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { return -1; - } elsif ($_[1] =~ /^Income/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { + } elsif ($_[1] =~ /^(Unearned Income)/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { return 1; - } elsif ($_[0] =~ /^Expense/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Expense)/) { + } elsif ($_[0] =~ /^Income/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Income)/) { return -1; - } elsif ($_[1] =~ /^Expense/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Expense)/) { + } elsif ($_[1] =~ /^Income/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Income)/) { + return 1; + } elsif ($_[0] =~ /^Expense/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Income|Unearned Income|Expense)/) { + return -1; + } elsif ($_[1] =~ /^Expense/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Income|Unearned Income|Expense)/) { return 1; } else { return $_[0] cmp $_[1]; } } + my @sortedAccounts; foreach my $acct ( sort preferredAccountSorting @accounts) { print CHART_OUTPUT "\"$acct\"\n"; diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx index 8e9339a5..17999c36 100755 --- a/contrib/non-profit-audit-reports/summary-reports.plx +++ b/contrib/non-profit-audit-reports/summary-reports.plx @@ -40,29 +40,33 @@ sub Commify ($) { } sub preferredAccountSorting ($$) { - if ($_[0] =~ /^Assets/) { + if ($_[0] =~ /^Assets/ and $_[1] !~ /^Assets/) { return -1; - } elsif ($_[1] =~ /^Assets/) { + } elsif ($_[1] =~ /^Assets/ and $_[0] !~ /^Assets/) { return 1; - } elsif ($_[0] =~ /^Liabilities/ and $_[1] !~ /^Assets/) { + } elsif ($_[0] =~ /^Liabilities/ and $_[1] !~ /^(Assets|Liabilities)/) { return -1; - } elsif ($_[1] =~ /^Liabilities/ and $_[0] !~ /^Assets/) { + } elsif ($_[1] =~ /^Liabilities/ and $_[0] !~ /^(Assets|Liabilities)/) { return 1; - } elsif ($_[0] =~ /^(Accrued)/ and $_[1] !~ /^(Assets|Liabilities)/) { + } elsif ($_[0] =~ /^(Accrued:[^:]+Receivable)/ and $_[1] !~ /^(Assets|Liabilities|Accrued:[^:]+Receivable)/) { return -1; - } elsif ($_[1] =~ /^(Accrued)/ and $_[0] !~ /^(Assets|Liabilities)/) { + } elsif ($_[1] =~ /^(Accrued:[^:]+Receivable)/ and $_[0] !~ /^(Assets|Liabilities|Accrued:[^:]+Receivable)/) { return 1; - } elsif ($_[0] =~ /^(Unearned Income)/ and $_[1] !~ /^(Assets|Liabilities|Accrued)/) { + } elsif ($_[0] =~ /^(Accrued)/ and $_[1] !~ /^(Assets|Liabilities|Accrued)/) { return -1; - } elsif ($_[1] =~ /^(Unearned Income)/ and $_[0] !~ /^(Assets|Liabilities|Accrued)/) { + } elsif ($_[1] =~ /^(Accrued)/ and $_[0] !~ /^(Assets|Liabilities|Accrued)/) { return 1; - } elsif ($_[0] =~ /^Income/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { + } elsif ($_[0] =~ /^(Unearned Income)/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { return -1; - } elsif ($_[1] =~ /^Income/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { + } elsif ($_[1] =~ /^(Unearned Income)/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { return 1; - } elsif ($_[0] =~ /^Expense/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Expense)/) { + } elsif ($_[0] =~ /^Income/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Income)/) { return -1; - } elsif ($_[1] =~ /^Expense/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Expense)/) { + } elsif ($_[1] =~ /^Income/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Income)/) { + return 1; + } elsif ($_[0] =~ /^Expense/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Income|Unearned Income|Expense)/) { + return -1; + } elsif ($_[1] =~ /^Expense/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Income|Unearned Income|Expense)/) { return 1; } else { return $_[0] cmp $_[1]; -- cgit v1.2.3 From 4290a4ec52793baeaaa3ae6dbb75cbef993d3ef4 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Sat, 5 Jan 2013 13:13:05 -0500 Subject: Add balances for permanent (i.e., asset) accounts. Based on a request from our accountants, I've changed the RUNNING TOTAL field (which is generally useless to accountants anyway) to be a BALANCE amount for starting and ending accounts. --- .../general-ledger-report.plx | 44 ++++++++++++++++++++-- 1 file changed, 41 insertions(+), 3 deletions(-) (limited to 'contrib/non-profit-audit-reports/general-ledger-report.plx') diff --git a/contrib/non-profit-audit-reports/general-ledger-report.plx b/contrib/non-profit-audit-reports/general-ledger-report.plx index a464053b..3b230837 100755 --- a/contrib/non-profit-audit-reports/general-ledger-report.plx +++ b/contrib/non-profit-audit-reports/general-ledger-report.plx @@ -127,6 +127,34 @@ foreach my $acct ( sort preferredAccountSorting @accounts) { } close(CHART_OUTPUT); die "error writing to chart-of-accounts.txt: $!" unless $? == 0; +my %commands = ( + 'totalEnd' => [ $LEDGER_CMD, @otherLedgerOpts, '-V', '-X', '$', + '-e', $endDate, '-F', '%-.80A %22.108t\n', '-s', + 'reg' ], + 'totalBegin' => [ $LEDGER_CMD, @otherLedgerOpts, '-V', '-X', '$', + '-e', $beginDate, '-F', '%-.80A %22.108t\n', + '-s', 'reg' ]); + +my %balanceData; + +foreach my $id (keys %commands) { + my(@command) = @{$commands{$id}}; + + open(FILE, "-|", @command) or die "unable to run command ledger command: @command: $!"; + + foreach my $line () { + die "Unable to parse output line from balance data $id command: $line" + unless $line =~ /^\s*([^\$]+)\s+\$\s*([\-\d\.\,]+)/; + my($account, $amount) = ($1, $2); + $amount = ParseNumber($amount); + $account =~ s/\s+$//; + next if $account =~ /\/ and (abs($amount) <= 0.02); + next if $account =~ /^Equity:/; # Stupid auto-account made by ledger. + $balanceData{$id}{$account} = $amount; + } + close FILE; + die "unable to run balance data ledger command, @command: $!" unless ($? == 0); +} open(GL_TEXT_OUT, ">", "general-ledger.txt") or die "unable to write general-ledger.txt: $!"; print MANIFEST "general-ledger.txt\n"; @@ -147,15 +175,20 @@ foreach my $acct (@sortedAccounts) { } close(GL_TEXT_DATA); die "error reading ledger output for chart of accounts: $!" unless $? == 0; - print GL_CSV_OUT "\n\"ACCOUNT:\",\"$acct\"\n\"PERIOD START:\",\"$beginDate\"\n\"PERIOD END:\",\"$formattedEndDate\"\n"; - print GL_CSV_OUT '"DATE","CHECK NUM","NAME","TRANSACTION AMT","RUNNING TOTAL"'; - my $formatString = '"%(date)","%C","%P","%t","%T"'; + print GL_CSV_OUT "\n\"ACCOUNT:\",\"$acct\"\n\"PERIOD START:\",\"$formattedBeginDate\"\n\"PERIOD END:\",\"$formattedEndDate\"\n"; + print GL_CSV_OUT '"DATE","CHECK NUM","NAME","TRANSACTION AMT","BALANCE"'; + + my $formatString = '"%(date)","%C","%P","%t",""'; foreach my $tagField (qw/Receipt Invoice Statement Contract PurchaseOrder Approval Check IncomeDistributionAnalysis CurrencyRate/) { print GL_CSV_OUT ',"', $tagField, '"'; $formatString .= ',"%(tag(\'' . $tagField . '\'))"'; } $formatString .= "\n"; print GL_CSV_OUT "\n"; + if ($acct =~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { + $balanceData{totalBegin}{$acct} = $ZERO unless defined $balanceData{totalBegin}{$acct}; + print GL_CSV_OUT "\"$formattedBeginDate\"", ',"","BALANCE","","$', "$balanceData{totalBegin}{$acct}\"\n"; + } @acctLedgerOpts = ('-V', '-F', $formatString, '-w', '--sort', 'd', '-b', $beginDate, '-e', $endDate, @otherLedgerOpts, 'reg', $acct); open(GL_CSV_DATA, "-|", $LEDGER_CMD, @acctLedgerOpts) @@ -173,6 +206,11 @@ foreach my $acct (@sortedAccounts) { $manifest{$file} = $line; } } + if ($acct =~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { + $balanceData{totalEnd}{$acct} = $ZERO unless defined $balanceData{totalEnd}{$acct}; + print GL_CSV_OUT "\"$formattedEndDate\"", ',"","BALANCE","","$', "$balanceData{totalEnd}{$acct}\"\n"; + } + close(GL_CSV_DATA); die "error reading ledger output for chart of accounts: $!" unless $? == 0; } close(GL_TEXT_OUT); die "error writing to general-ledger.txt: $!" unless $? == 0; -- cgit v1.2.3 From 8cddda4c3eb67234c12285fe52bd2bc328f6678b Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Sun, 6 Jan 2013 19:43:54 -0500 Subject: More flexible CSV -> ODS hyperlinks and pagebreaks; csv2ods.py produces MANIFEST. Previous version of csv2ods.py simply assumed that fields beyond five would have links to files. This obviously lacked flexibility and was a silly hard-code. Now, those CSV fields that have link:SOMETHING will cause a hyperlink to be created to SOMETHING. Meanwhile, the pagebreak support was similarly hard-coded. Now, any CSV field that has the word "pagebreak" in it will generate a pagebreak. The general ledger and cash receipts/disbursement journals have been modified to make use of these new features in csv2ods.py. Finally, the --skip-page-break option is now moot in csv2ods.py, so that is herein removed. --- .../cash-receipts-and-disbursments-journals.plx | 4 +- contrib/non-profit-audit-reports/csv2ods.py | 43 +++++++++++++--------- .../general-ledger-report.plx | 6 ++- 3 files changed, 32 insertions(+), 21 deletions(-) (limited to 'contrib/non-profit-audit-reports/general-ledger-report.plx') diff --git a/contrib/non-profit-audit-reports/cash-receipts-and-disbursments-journals.plx b/contrib/non-profit-audit-reports/cash-receipts-and-disbursments-journals.plx index 346e4064..2ad18a44 100755 --- a/contrib/non-profit-audit-reports/cash-receipts-and-disbursments-journals.plx +++ b/contrib/non-profit-audit-reports/cash-receipts-and-disbursments-journals.plx @@ -113,7 +113,7 @@ foreach my $acct (@accounts) { my $formatString = '\n"%(date)","%C","%P","%A","%t"\n%/"","","","%A","%t"'; foreach my $tagField (qw/Receipt Invoice Statement Contract PurchaseOrder Approval Check IncomeDistributionAnalysis CurrencyRate/) { print CSV_OUT ',"', $tagField, '"'; - $formatString .= ',"%(tag(\'' . $tagField . '\'))"'; + $formatString .= ',"link:%(tag(\'' . $tagField . '\'))"'; } $formatString .= "\n"; print CSV_OUT "\n"; @@ -130,7 +130,7 @@ foreach my $acct (@accounts) { open(CSV_DATA, "-|", $LEDGER_CMD, @csvRegLedgerOpts) or die "unable to run ledger command for $fileNameBase.csv: $!"; - while (my $line = ) { print CSV_OUT $line; } + while (my $line = ) { $line =~ s/"link:"/""/g; print CSV_OUT $line; } close(CSV_DATA); die "Error read from csv ledger command $!" unless $? == 0; SKIP_REGISTER_COMMANDS: diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py index e6d33906..8b880648 100755 --- a/contrib/non-profit-audit-reports/csv2ods.py +++ b/contrib/non-profit-audit-reports/csv2ods.py @@ -24,14 +24,13 @@ import sys, os, os.path, optparse import csv import ooolib2 -file_fields = [ 'Receipt', 'Invoice', 'Statement', 'Contract', 'PurchaseOrder', - 'Approval', 'Check', 'IncomeDistributionAnalysis', 'CurrencyRate' ] - def err(msg): print 'error: %s' % msg sys.exit(1) -def csv2ods(csvname, odsname, encoding='', verbose = False, skip_page_break = False): +def csv2ods(csvname, odsname, encoding='', verbose = False): + filesSavedinManifest = {} + if verbose: print 'converting from %s to %s' % (csvname, odsname) doc = ooolib2.Calc() @@ -45,7 +44,7 @@ def csv2ods(csvname, odsname, encoding='', verbose = False, skip_page_break = Fa style_currency = doc.styles.get_next_style('cell') style_data = tuple([style]) doc.styles.style_config[style_data] = style_currency - + row = 1 csvdir = os.path.dirname(csvname) if len(csvdir) == 0: @@ -61,26 +60,39 @@ def csv2ods(csvname, odsname, encoding='', verbose = False, skip_page_break = Fa if len(val) > 0 and val[0] == '$': doc.set_cell_value(col + 1, row, 'currency', val[1:]) else: - if ((col >= 5) and (not val in file_fields) and len(val) > 0): + if (len(val) > 0 and val[0:5] == "link:"): + val = val[5:] linkrel = '../' + val # ../ means remove the name of the *.ods linkname = os.path.basename(val) # name is just the last component doc.set_cell_value(col + 1, row, 'link', (linkrel, linkname)) linkpath = csvdir + '/' + val + + if not val in filesSavedinManifest: + filesSavedinManifest[val] = col + + if not os.path.exists(linkpath): + print "WARNING: link %s DOES NOT EXIST at %s" % (val, linkpath) if verbose: if os.path.exists(linkpath): print 'relative link %s EXISTS at %s' % (val, linkpath) - else: - print 'relative link %s DOES NOT EXIST at %s' % (val, linkpath) else: - doc.set_cell_value(col + 1, row, 'string', val) + if val == "pagebreak": + doc.sheets[doc.sheet_index].set_sheet_config(('row', row), style_pagebreak) + else: + doc.set_cell_value(col + 1, row, 'string', val) else: # enter an empty string for blank lines doc.set_cell_value(1, row, 'string', '') - # put a pagebreak here - if not skip_page_break: - doc.sheets[doc.sheet_index].set_sheet_config(('row', row), style_pagebreak) row += 1 - # save the file + # save manifest file + if filesSavedinManifest.keys() != []: + manifestFH = open("MANIFEST", "a") + manifestFH.write("# Files from %s\n" % odsname) + for file in filesSavedinManifest.keys(): + manifestFH.write("%s\n" % file) + + manifestFH.close() + # Save spreadsheet file. doc.save(odsname) def main(): @@ -97,9 +109,6 @@ def main(): help='ods output filename') parser.add_option('-e', '--encoding', action='store', help='unicode character encoding type') - parser.add_option('-s', '--skip-page-break', action='store_true', - dest='skip_page_break', - help='do not add any page breaks') (options, args) = parser.parse_args() if len(args) != 0: parser.error("not expecting extra args") @@ -113,7 +122,7 @@ def main(): print 'csv:', options.csv print 'ods:', options.ods print 'ods:', options.encoding - csv2ods(options.csv, options.ods, options.encoding, options.verbose, options.skip_page_break) + csv2ods(options.csv, options.ods, options.encoding, options.verbose) if __name__ == '__main__': main() diff --git a/contrib/non-profit-audit-reports/general-ledger-report.plx b/contrib/non-profit-audit-reports/general-ledger-report.plx index 3b230837..1fd0e7ce 100755 --- a/contrib/non-profit-audit-reports/general-ledger-report.plx +++ b/contrib/non-profit-audit-reports/general-ledger-report.plx @@ -181,7 +181,7 @@ foreach my $acct (@sortedAccounts) { my $formatString = '"%(date)","%C","%P","%t",""'; foreach my $tagField (qw/Receipt Invoice Statement Contract PurchaseOrder Approval Check IncomeDistributionAnalysis CurrencyRate/) { print GL_CSV_OUT ',"', $tagField, '"'; - $formatString .= ',"%(tag(\'' . $tagField . '\'))"'; + $formatString .= ',"link:%(tag(\'' . $tagField . '\'))"'; } $formatString .= "\n"; print GL_CSV_OUT "\n"; @@ -195,12 +195,14 @@ foreach my $acct (@sortedAccounts) { or die "Unable to run $LEDGER_CMD @acctLedgerOpts: $!"; foreach my $line () { + $line =~ s/"link:"/""/g; print GL_CSV_OUT $line; next if $line =~ /ACCOUNT:.*PERIOD/; # Skip column header lines $line =~ s/^"[^"]*","[^"]*","[^"]*","[^"]*","[^"]*",//; while ($line =~ s/^"([^"]*)"(,|$)//) { my $file = $1; next if $file =~ /^\s*$/; + $file =~ s/^link:(.*)$/$1/; warn "$file does not exist and/or is not readable" unless -r $file; print MANIFEST "$file\n" if not defined $manifest{$file}; $manifest{$file} = $line; @@ -210,7 +212,7 @@ foreach my $acct (@sortedAccounts) { $balanceData{totalEnd}{$acct} = $ZERO unless defined $balanceData{totalEnd}{$acct}; print GL_CSV_OUT "\"$formattedEndDate\"", ',"","BALANCE","","$', "$balanceData{totalEnd}{$acct}\"\n"; } - + print GL_CSV_OUT "pagebreak\n"; close(GL_CSV_DATA); die "error reading ledger output for chart of accounts: $!" unless $? == 0; } close(GL_TEXT_OUT); die "error writing to general-ledger.txt: $!" unless $? == 0; -- cgit v1.2.3