From 269d0fdd5efe2dc9de148f845f97ad95ddc1b8e9 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Thu, 3 Jan 2013 10:39:58 -0500
Subject: Created Trial balance report for summary reports.

---
 .../non-profit-audit-reports/summary-reports.plx   | 31 +++++++++++++++++++++-
 1 file changed, 30 insertions(+), 1 deletion(-)

(limited to 'contrib')

diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx
index 7d2267d6..ce6d56da 100755
--- a/contrib/non-profit-audit-reports/summary-reports.plx
+++ b/contrib/non-profit-audit-reports/summary-reports.plx
@@ -3,7 +3,7 @@
 #
 #    Script to generate end-of-year summary reports.
 #
-# Copyright (C) 2011, 2012, Bradley M. Kuhn
+# Copyright (C) 2011, 2012, 2013, Bradley M. Kuhn
 #
 # This program gives you software freedom; you can copy, modify, convey,
 # and/or redistribute it under the terms of the GNU General Public License
@@ -348,6 +348,35 @@ die "calculated total of $overallTotal does equal $firstTotal"
 
 print STDERR "\n";
 
+open(TRIAL, ">", "trial-balance.txt") or die "unable to open accrued.txt for writing: $!";
+
+@fullCommand = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$',
+                    '-e', $endDate,
+                    '-F', '%-.80A   %22.108t\n', '-s',
+                    'reg');
+
+print TRIAL "                           TRIAL BALANCE \n",
+             "                     Ending $formattedEndDate\n\n";
+
+open(FILE, "-|", @fullCommand)
+  or die "unable to run command ledger command: @fullCommand: $!";
+
+print STDERR ($VERBOSE ? "Running: @fullCommand\n" : ".");
+
+my $accruedTotal = $ZERO;
+
+foreach my $line (<FILE>) {
+  die "Unable to parse output line from second funds command: $line"
+    unless $line =~ /^\s*([^\$]+)\s+\$\s*([\-\d\.\,]+)/;
+  my($account, $amount) = ($1, $2);
+  $amount = ParseNumber($amount);
+  $account =~ s/\s+$//;
+  next if $account =~ /\<Adjustment\>|^Equity:/ and (abs($amount) <= 0.02);
+  print TRIAL $line;
+
+  $accruedTotal += $amount;
+}
+
 ###############################################################################
 #
 # Local variables:
-- 
cgit v1.2.3


From bfdf20b31cbb60b5d92c82ecd3a5192059a0c7b1 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
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 ++++++++++++++++------
 .../non-profit-audit-reports/summary-reports.plx   | 30 ++++++++++++++
 2 files changed, 63 insertions(+), 13 deletions(-)

(limited to 'contrib')

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);
 }
diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx
index ce6d56da..30b27505 100755
--- a/contrib/non-profit-audit-reports/summary-reports.plx
+++ b/contrib/non-profit-audit-reports/summary-reports.plx
@@ -39,6 +39,36 @@ sub Commify ($) {
     return scalar reverse $text;
 }
 
+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];
+  }
+}
+
 sub ParseNumber($) {
   $_[0] =~ s/,//g;
   return Math::BigFloat->new($_[0]);
-- 
cgit v1.2.3


From f01ddd4766741cd0dc09fd42cb7cc87f4dadbb20 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
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')

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 <BEGIN_DATE> <END_DATE> <OTHER_LEDGER_OPTS>\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 = <CHART_DATA>) {
 }
 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 d18e01a00f88ec7d073b88db464679a9aa9910a6 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Thu, 3 Jan 2013 12:19:59 -0500
Subject: Changed balance sheet output from a TXT file to a CSV file.

---
 contrib/non-profit-audit-reports/summary-reports.plx | 16 ++++++++--------
 1 file changed, 8 insertions(+), 8 deletions(-)

(limited to 'contrib')

diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx
index 30b27505..da2dba9d 100755
--- a/contrib/non-profit-audit-reports/summary-reports.plx
+++ b/contrib/non-profit-audit-reports/summary-reports.plx
@@ -146,15 +146,15 @@ foreach my $item (keys %reportFields) {
   print STDERR  "$item: $reportFields{$item}{total}\n" if $VERBOSE;
 }
 
-open(BALANCE_SHEET, ">", "balance-sheet.txt")
-  or die "unable to open balance-sheet.txt for writing: $!";
+open(BALANCE_SHEET, ">", "balance-sheet.csv")
+  or die "unable to open balance-sheet.csv for writing: $!";
 
-print BALANCE_SHEET "                          BALANCE SHEET\n",
-                    "                          Ending ", $formattedEndDate, "\n",
-                  "\n\nASSETS\n\n";
+print BALANCE_SHEET "\"BALANCE SHEET\"\n",
+                    "\"Ending\",\"", $formattedEndDate, "\"\n",
+                  "\n\n\"ASSETS\"\n\n";
 
-my $formatStr      = "   %-42s \$%13s\n";
-my $formatStrTotal = "%-45s \$%13s\n";
+my $formatStr      = "\"\",\"%-42s\",\"\$%13s\"\n";
+my $formatStrTotal = "\"\",\"%-45s\",\"\$%13s\"\n";
 my $tot = $ZERO;
 foreach my $item ('Cash', 'Accounts Receivable', 'Loans Receivable') {
   next if $reportFields{$item}{total} == $ZERO;
@@ -186,7 +186,7 @@ print BALANCE_SHEET "\n", sprintf($formatStr, "TOTAL NET ASSETS", Commify($totNe
 
 close BALANCE_SHEET;
 print STDERR "\n";
-die "unable to write to balance-sheet.txt: $!" unless ($? == 0);
+die "unable to write to balance-sheet.csv: $!" unless ($? == 0);
 
 die "Cash+accounts receivable total does not equal net assets and liabilities total"
   if (abs( ($reportFields{'Cash'}{total} + $reportFields{'Accounts Receivable'}{total}
-- 
cgit v1.2.3


From b939bbe8c61a819e413b3e2a24d223f6232bd0d9 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Thu, 3 Jan 2013 12:30:43 -0500
Subject: Convert trial-balance report to CSV from TXT file.

Also, ignore Ledger's Equity: accounts properly.
---
 .../non-profit-audit-reports/summary-reports.plx   | 23 ++++++++++++++++------
 1 file changed, 17 insertions(+), 6 deletions(-)

(limited to 'contrib')

diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx
index da2dba9d..dae8f2ce 100755
--- a/contrib/non-profit-audit-reports/summary-reports.plx
+++ b/contrib/non-profit-audit-reports/summary-reports.plx
@@ -378,15 +378,15 @@ die "calculated total of $overallTotal does equal $firstTotal"
 
 print STDERR "\n";
 
-open(TRIAL, ">", "trial-balance.txt") or die "unable to open accrued.txt for writing: $!";
+open(TRIAL, ">", "trial-balance.csv") or die "unable to open accrued.txt for writing: $!";
 
 @fullCommand = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$',
                     '-e', $endDate,
                     '-F', '%-.80A   %22.108t\n', '-s',
                     'reg');
 
-print TRIAL "                           TRIAL BALANCE \n",
-             "                     Ending $formattedEndDate\n\n";
+print TRIAL "\"TRIAL BALANCE REPORT\",",
+             "\"ENDING:\",\"$formattedEndDate\"\n\n\"ACCOUNT NAME\", \"AMOUNT\"\n\n";
 
 open(FILE, "-|", @fullCommand)
   or die "unable to run command ledger command: @fullCommand: $!";
@@ -395,17 +395,28 @@ print STDERR ($VERBOSE ? "Running: @fullCommand\n" : ".");
 
 my $accruedTotal = $ZERO;
 
+my %trialBalances;
+
 foreach my $line (<FILE>) {
   die "Unable to parse output line from second funds command: $line"
     unless $line =~ /^\s*([^\$]+)\s+\$\s*([\-\d\.\,]+)/;
   my($account, $amount) = ($1, $2);
   $amount = ParseNumber($amount);
   $account =~ s/\s+$//;
-  next if $account =~ /\<Adjustment\>|^Equity:/ and (abs($amount) <= 0.02);
-  print TRIAL $line;
-
+  next if $account =~ /\<Adjustment\>/ and (abs($amount) <= 0.02);
+  next if $account =~ /^Equity:/;   # Stupid auto-account made by ledger.
+  $trialBalances{$account} = $amount;
   $accruedTotal += $amount;
 }
+close FILE;
+die "unable to run trial balance ledger command: $!" unless ($? == 0);
+
+foreach my $account (sort preferredAccountSorting keys %trialBalances) {
+  print TRIAL "\"$account\",\"$trialBalances{$account}\"\n";
+}
+
+close TRIAL;
+die "unable to write trial-balance.csv: $!" unless ($? == 0);
 
 ###############################################################################
 #
-- 
cgit v1.2.3


From fbd6c309031393950ac387b9de5f07214c5fa3cf Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Thu, 3 Jan 2013 13:36:38 -0500
Subject: Add option to skip generating the page breaks.

---
 contrib/non-profit-audit-reports/csv2ods.py | 14 +++++++++-----
 1 file changed, 9 insertions(+), 5 deletions(-)

(limited to 'contrib')

diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py
index 2b3024d4..aded8e65 100755
--- a/contrib/non-profit-audit-reports/csv2ods.py
+++ b/contrib/non-profit-audit-reports/csv2ods.py
@@ -2,8 +2,8 @@
 # csv2ods.py
 # Convert example csv file to ods
 #
-# Copyright (c) 2012 Tom Marble
-# Copyright (c) 2012 Bradley M. Kuhn
+# Copyright (c) 2012       Tom Marble
+# Copyright (c) 2012, 2013 Bradley M. Kuhn
 #
 # This program gives you software freedom; you can copy, modify, convey,
 # and/or redistribute it under the terms of the GNU General Public License
@@ -31,7 +31,7 @@ def err(msg):
     print 'error: %s' % msg
     sys.exit(1)
 
-def csv2ods(csvname, odsname, encoding='', verbose = False):
+def csv2ods(csvname, odsname, encoding='', verbose = False, skip_page_break = False):
     if verbose:
         print 'converting from %s to %s' % (csvname, odsname)
     doc = ooolib2.Calc()
@@ -77,7 +77,8 @@ def csv2ods(csvname, odsname, encoding='', verbose = False):
             # enter an empty string for blank lines
             doc.set_cell_value(1, row, 'string', '')
             # put a pagebreak here
-            doc.sheets[doc.sheet_index].set_sheet_config(('row', row), style_pagebreak)
+            if not skip_page_break:
+                doc.sheets[doc.sheet_index].set_sheet_config(('row', row), style_pagebreak)
         row += 1
     # save the file
     doc.save(odsname)
@@ -96,6 +97,9 @@ 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")  
@@ -109,7 +113,7 @@ def main():
         print 'csv:', options.csv
         print 'ods:', options.ods
         print 'ods:', options.encoding
-    csv2ods(options.csv, options.ods, options.verbose, options.encoding)
+    csv2ods(options.csv, options.ods, options.verbose, options.encoding, options.skip_page_break)
 
 if __name__ == '__main__':
   main()
-- 
cgit v1.2.3


From 750321c0b1e35b0315138b35524f85d3648d8dda Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Thu, 3 Jan 2013 13:37:08 -0500
Subject: Change Income and Expenses reports to generate CSV files, rather than
 TXT files.

---
 .../non-profit-audit-reports/summary-reports.plx   | 35 +++++++++++-----------
 1 file changed, 18 insertions(+), 17 deletions(-)

(limited to 'contrib')

diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx
index dae8f2ce..efad30e7 100755
--- a/contrib/non-profit-audit-reports/summary-reports.plx
+++ b/contrib/non-profit-audit-reports/summary-reports.plx
@@ -223,7 +223,7 @@ foreach my $type (keys %incomeGroups) {
 $incomeGroups{"OTHER"}{args} = \@otherArgs;
 $incomeGroups{"TOTAL"}{args} = ['/^Income/'];
 
-open(INCOME, ">", "income.txt") or die "unable to open income.txt for writing: $!";
+open(INCOME, ">", "income.csv") or die "unable to open income.csv for writing: $!";
 
 foreach my $type (keys %incomeGroups) {
   my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$',
@@ -247,32 +247,32 @@ foreach my $type (keys %incomeGroups) {
     $account =~ s/\s+$//;
     next if $account =~ /\<Adjustment\>/ and (abs($amount) <= 0.02);
     die "Weird account found, $account with amount of $amount in income command\n"
-      unless $account =~ s/^\s*Income://;
+      unless $account =~ /^\s*Income:/;
 
     $incomeGroups{$type}{total} += $amount;
-    $incomeGroups{$type}{output} .= "    $line";
+    $incomeGroups{$type}{output} .= "\"$account\",\"\$$amount\"\n";
   }
 }
-print INCOME "                           INCOME\n",
-             "           Between $formattedStartDate and $formattedEndDate\n\n";
+print INCOME "\"INCOME\",",
+             "\"STARTING:\",\"$formattedStartDate\",\"ENDING:\",\"$formattedEndDate\"\n\n";
 
 
 my $overallTotal = $ZERO;
 
-$formatStrTotal = "%-90s    \$%14s\n";
+$formatStrTotal = "\"%-90s\",\"\$%14s\"\n";
 foreach my $type ('DONATIONS', 'LICENSE ENFORCEMENT',
                   'CONFERENCES, REGISTRATION', 'CONFERENCES, RELATED BUSINESS INCOME',
                   'BOOK ROYALTIES & AFFILIATE PROGRAMS', 'ADVERSITING',
                   'TRADEMARKS', 'INTEREST INCOME', 'OTHER') {
   next if ($incomeGroups{$type}{output} =~ /^\s*$/ and $incomeGroups{$type}{total} == $ZERO);
-  print INCOME "\n$type\n",
+  print INCOME "\n\"$type\"\n",
                $incomeGroups{$type}{output}, "\n",
                sprintf($formatStrTotal, "TOTAL $type:", Commify($incomeGroups{$type}{total}));
   $overallTotal += $incomeGroups{$type}{total};
 }
 print INCOME "\n\n\n", sprintf($formatStrTotal, "OVERALL TOTAL:", Commify($overallTotal));
 
-close INCOME;    die "unable to write to income.txt: $!" unless ($? == 0);
+close INCOME;    die "unable to write to income.csv: $!" unless ($? == 0);
 
 die "calculated total of $overallTotal does equal $incomeGroups{TOTAL}{total}"
   if (abs($overallTotal) - abs($incomeGroups{TOTAL}{total}) > $ONE_PENNY);
@@ -298,7 +298,7 @@ foreach my $type (keys %expenseGroups, 'TRAVEL') {
   $expenseGroups{$type}{output} = "";
 }
 
-open(EXPENSE, ">", "expense.txt") or die "unable to open expense.txt for writing: $!";
+open(EXPENSE, ">", "expense.csv") or die "unable to open expense.csv for writing: $!";
 
 my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$',
                     '-b', $startDate, '-e', $endDate,
@@ -321,6 +321,7 @@ foreach my $line (<FILE>) {
   die "Weird account found, $account, with amount of $amount in expenses command\n"
     unless $account =~ /^\s*Expenses:/;
 
+  my $outputLine = "\"$account\",\"\$$amount\"\n";
   my $taken = 0;
   # Note: Prioritize to put things under conference expenses if they were for a conference. 
   foreach my $type ('CONFERENCES', keys %expenseGroups) {
@@ -329,23 +330,23 @@ foreach my $line (<FILE>) {
     next unless $line =~ /$expenseGroups{$type}{regex}/;
     $taken = 1;
     $expenseGroups{$type}{total} += $amount;
-    $expenseGroups{$type}{output} .= "    $line";
+    $expenseGroups{$type}{output} .= $outputLine;
   }
   if (not $taken) {
     if ($account =~ /Travel/) {
       $expenseGroups{'TRAVEL'}{total} += $amount;
-      $expenseGroups{'TRAVEL'}{output} .= "    $line";
+      $expenseGroups{'TRAVEL'}{output} .= $outputLine;
     } else {
       $expenseGroups{'OTHER'}{total} += $amount;
-      $expenseGroups{'OTHER'}{output} .= "    $line";
+      $expenseGroups{'OTHER'}{output} .= $outputLine;
     }
   }
   $firstTotal += $amount;
 }
-print EXPENSE "                           EXPENSES\n",
-             "           Between $formattedStartDate and $formattedEndDate\n\n";
+print EXPENSE "\"EXPENSES\",",
+             "\"STARTING:\",\"$formattedStartDate\",\"ENDING:\",\"$formattedEndDate\"\n\n";
 $overallTotal = $ZERO;
-$formatStrTotal = "%-90s    \$%14s\n";
+$formatStrTotal = "\"%-90s\",\"\$%14s\"\n";
 
 my %verifyAllGroups;
 foreach my $key (keys %expenseGroups) {
@@ -360,7 +361,7 @@ foreach my $type ('PAYROLL', 'SOFTWARE DEVELOPMENT', 'LICENSE ENFORCEMENT', 'CON
   die "$type is not defined!" if not defined $expenseGroups{$type};
   next if ($expenseGroups{$type}{output} =~ /^\s*$/ and $expenseGroups{$type}{total} == $ZERO);
 
-  print EXPENSE "\n$type\n",
+  print EXPENSE "\n\"$type\"\n",
                $expenseGroups{$type}{output}, "\n",
                sprintf($formatStrTotal, "TOTAL $type:", Commify($expenseGroups{$type}{total}));
   $overallTotal += $expenseGroups{$type}{total};
@@ -368,7 +369,7 @@ foreach my $type ('PAYROLL', 'SOFTWARE DEVELOPMENT', 'LICENSE ENFORCEMENT', 'CON
 
 print EXPENSE "\n\n\n", sprintf($formatStrTotal, "OVERALL TOTAL:", Commify($overallTotal));
 
-close EXPENSE;    die "unable to write to expense.txt: $!" unless ($? == 0);
+close EXPENSE;    die "unable to write to expense.csv: $!" unless ($? == 0);
 
 die "GROUPS NOT INCLUDED : ", join(keys(%verifyAllGroups), ", "), "\n"
   unless (keys %verifyAllGroups == 0);
-- 
cgit v1.2.3


From 2fad8fe238cee8509290562a823fe0c3b312ee94 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Thu, 3 Jan 2013 13:37:18 -0500
Subject: Some minor formatting fixes for the trial balance report.

---
 contrib/non-profit-audit-reports/summary-reports.plx | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

(limited to 'contrib')

diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx
index efad30e7..8e9339a5 100755
--- a/contrib/non-profit-audit-reports/summary-reports.plx
+++ b/contrib/non-profit-audit-reports/summary-reports.plx
@@ -387,7 +387,7 @@ open(TRIAL, ">", "trial-balance.csv") or die "unable to open accrued.txt for wri
                     'reg');
 
 print TRIAL "\"TRIAL BALANCE REPORT\",",
-             "\"ENDING:\",\"$formattedEndDate\"\n\n\"ACCOUNT NAME\", \"AMOUNT\"\n\n";
+             "\"ENDING:\",\"$formattedEndDate\"\n\n\"ACCOUNT NAME\",\"AMOUNT\"\n\n";
 
 open(FILE, "-|", @fullCommand)
   or die "unable to run command ledger command: @fullCommand: $!";
@@ -413,7 +413,7 @@ close FILE;
 die "unable to run trial balance ledger command: $!" unless ($? == 0);
 
 foreach my $account (sort preferredAccountSorting keys %trialBalances) {
-  print TRIAL "\"$account\",\"$trialBalances{$account}\"\n";
+  print TRIAL "\"$account\",\"\$$trialBalances{$account}\"\n";
 }
 
 close TRIAL;
-- 
cgit v1.2.3


From e317e1f23e236a2797b13113bc2afae14502fc5c Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
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')

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 986829b1d656611eb243b920703acc198fdf3f37 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Fri, 4 Jan 2013 10:19:16 -0500
Subject: Corrected Trial Balance report based on discussion with accountants.

I believe this trial balance report will look "more natural" to
accountants.
---
 .../non-profit-audit-reports/summary-reports.plx   | 83 +++++++++++++++-------
 1 file changed, 57 insertions(+), 26 deletions(-)

(limited to 'contrib')

diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx
index 17999c36..bbe83c88 100755
--- a/contrib/non-profit-audit-reports/summary-reports.plx
+++ b/contrib/non-profit-audit-reports/summary-reports.plx
@@ -385,41 +385,72 @@ print STDERR "\n";
 
 open(TRIAL, ">", "trial-balance.csv") or die "unable to open accrued.txt for writing: $!";
 
-@fullCommand = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$',
-                    '-e', $endDate,
-                    '-F', '%-.80A   %22.108t\n', '-s',
-                    'reg');
+print TRIAL "\"TRIAL BALANCE REPORT\",\"ENDING: $formattedEndDate\"\n\n",
+             "\"ACCOUNT\",\"BALANCE AT $formattedStartDate\",\"CHANGE DURING FY\",\"BALANCE AT $formattedEndDate\"\n\n";
 
-print TRIAL "\"TRIAL BALANCE REPORT\",",
-             "\"ENDING:\",\"$formattedEndDate\"\n\n\"ACCOUNT NAME\",\"AMOUNT\"\n\n";
+my %commands = (
+                'totalEndFY' => [ $LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$',
+                                              '-e', $endDate, '-F', '%-.80A   %22.108t\n', '-s',
+                                              'reg' ],
+                'amountInYear' =>  [ $LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$',
+                                     '-b', $startDate, '-e', $endDate, '-F', '%-.80A   %22.108t\n',
+                                     '-s', 'reg' ],
+                'totalBeginFY' =>  [ $LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$',
+                                     '-e', $startDate, '-F', '%-.80A   %22.108t\n',
+                                     '-s', 'reg' ]);
 
-open(FILE, "-|", @fullCommand)
-  or die "unable to run command ledger command: @fullCommand: $!";
+my %trialBalanceData;
+my %fullAccountList;
 
-print STDERR ($VERBOSE ? "Running: @fullCommand\n" : ".");
+foreach my $id (keys %commands) {
+  my(@command) = @{$commands{$id}};
 
-my $accruedTotal = $ZERO;
+  open(FILE, "-|", @command)
+  or die "unable to run command ledger command: @command: $!";
 
-my %trialBalances;
+  print STDERR ($VERBOSE ? "Running: @command\n" : ".");
 
-foreach my $line (<FILE>) {
-  die "Unable to parse output line from second funds command: $line"
-    unless $line =~ /^\s*([^\$]+)\s+\$\s*([\-\d\.\,]+)/;
-  my($account, $amount) = ($1, $2);
-  $amount = ParseNumber($amount);
-  $account =~ s/\s+$//;
-  next if $account =~ /\<Adjustment\>/ and (abs($amount) <= 0.02);
-  next if $account =~ /^Equity:/;   # Stupid auto-account made by ledger.
-  $trialBalances{$account} = $amount;
-  $accruedTotal += $amount;
+  foreach my $line (<FILE>) {
+    die "Unable to parse output line from trial balance $id command: $line"
+      unless $line =~ /^\s*([^\$]+)\s+\$\s*([\-\d\.\,]+)/;
+    my($account, $amount) = ($1, $2);
+    $amount = ParseNumber($amount);
+    $account =~ s/\s+$//;
+    next if $account =~ /\<Adjustment\>/ and (abs($amount) <= 0.02);
+    next if $account =~ /^Equity:/;   # Stupid auto-account made by ledger.
+    $trialBalanceData{$id}{$account} = $amount;
+    $fullAccountList{$account} = $id;
+  }
+  close FILE;
+  die "unable to run trial balance ledger command, @command: $!" unless ($? == 0);
 }
-close FILE;
-die "unable to run trial balance ledger command: $!" unless ($? == 0);
 
-foreach my $account (sort preferredAccountSorting keys %trialBalances) {
-  print TRIAL "\"$account\",\"\$$trialBalances{$account}\"\n";
-}
+my $curOn = 'Assets';
 
+foreach my $account (sort preferredAccountSorting keys %fullAccountList) {
+  # Blank lines right
+  if ($account !~ /^$curOn/) {
+    print TRIAL "\n";
+    $curOn = $account;
+    $curOn =~ s/^([^:]+):.*$/$1/;
+    print "CurOn now: $curOn\n";
+  }
+  if ($account =~ /^Assets|Liabilities|Accrued|Unearned Income/) {
+    foreach my $id (qw/totalBeginFY totalEndFY amountInYear/) {
+      $trialBalanceData{$id}{$account} = $ZERO
+      unless defined $trialBalanceData{$id}{$account};
+    }
+    print TRIAL "\"$account\",\"\$$trialBalanceData{totalBeginFY}{$account}\",",
+     "\"\$$trialBalanceData{amountInYear}{$account}\",\"\$$trialBalanceData{totalEndFY}{$account}\"\n"
+       unless $trialBalanceData{totalBeginFY}{$account} == $ZERO and
+         $trialBalanceData{amountInYear}{$account} == $ZERO and
+           $trialBalanceData{totalEndFY}{$account} == $ZERO;
+  } else {
+    print TRIAL "\"$account\",\"\",\"\$$trialBalanceData{amountInYear}{$account}\",\"\"\n"
+      if defined $trialBalanceData{amountInYear}{$account} and
+        $trialBalanceData{amountInYear}{$account} != $ZERO;
+  }
+}
 close TRIAL;
 die "unable to write trial-balance.csv: $!" unless ($? == 0);
 
-- 
cgit v1.2.3


From 87f0c4434d131ba78ba191780dbff2b8c47f3123 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Fri, 4 Jan 2013 12:23:37 -0500
Subject: Updated expected test output data for general-ledger report.

---
 .../tests/non-profit-test-data_chart-of-accounts.csv |   6 ++++++
 .../tests/non-profit-test-data_chart-of-accounts.txt |   4 ----
 .../tests/non-profit-test-data_general-ledger.ods    | Bin 11412 -> 5770 bytes
 3 files changed, 6 insertions(+), 4 deletions(-)
 create mode 100644 contrib/non-profit-audit-reports/tests/non-profit-test-data_chart-of-accounts.csv
 delete mode 100644 contrib/non-profit-audit-reports/tests/non-profit-test-data_chart-of-accounts.txt

(limited to 'contrib')

diff --git a/contrib/non-profit-audit-reports/tests/non-profit-test-data_chart-of-accounts.csv b/contrib/non-profit-audit-reports/tests/non-profit-test-data_chart-of-accounts.csv
new file mode 100644
index 00000000..445bc412
--- /dev/null
+++ b/contrib/non-profit-audit-reports/tests/non-profit-test-data_chart-of-accounts.csv
@@ -0,0 +1,6 @@
+"CHART OF ACCOUNTS","BEGINNING:","2012/03/01","ENDING:","2012/02/29"
+"Assets:Checking"
+"Income:Donation"
+"Income:Foo:Donation"
+"Expenses:Blah:Hosting"
+"Expenses:Foo:Hosting"
diff --git a/contrib/non-profit-audit-reports/tests/non-profit-test-data_chart-of-accounts.txt b/contrib/non-profit-audit-reports/tests/non-profit-test-data_chart-of-accounts.txt
deleted file mode 100644
index 57e636b9..00000000
--- a/contrib/non-profit-audit-reports/tests/non-profit-test-data_chart-of-accounts.txt
+++ /dev/null
@@ -1,4 +0,0 @@
-Assets:Checking
-Expenses:Foo:Hosting
-Income:Donation
-Income:Foo:Donation
diff --git a/contrib/non-profit-audit-reports/tests/non-profit-test-data_general-ledger.ods b/contrib/non-profit-audit-reports/tests/non-profit-test-data_general-ledger.ods
index 80771a6d..8eae706f 100644
Binary files a/contrib/non-profit-audit-reports/tests/non-profit-test-data_general-ledger.ods and b/contrib/non-profit-audit-reports/tests/non-profit-test-data_general-ledger.ods differ
-- 
cgit v1.2.3


From 2b237aa3ba15fd0964690eac379f9226990e6f05 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Fri, 4 Jan 2013 12:24:30 -0500
Subject: MANIFEST file is now also generated by general-ledger report.

We should give the sample MANIFEST for users that want to make sure they
got the script working properly, and to show the sample output.
---
 .gitignore                                                     |  1 +
 .../tests/non-profit-test-data_MANIFEST                        | 10 ++++++++++
 2 files changed, 11 insertions(+)
 create mode 100644 contrib/non-profit-audit-reports/tests/non-profit-test-data_MANIFEST

(limited to 'contrib')

diff --git a/.gitignore b/.gitignore
index 02f6433a..c60fe7d4 100644
--- a/.gitignore
+++ b/.gitignore
@@ -103,4 +103,5 @@ contrib/non-profit-audit-reports/tests/chart-of-accounts.txt
 contrib/non-profit-audit-reports/tests/general-ledger.csv
 contrib/non-profit-audit-reports/tests/general-ledger.ods
 contrib/non-profit-audit-reports/tests/general-ledger.txt
+contrib/non-profit-audit-reports/tests/MANIFEST
 contrib/non-profit-audit-reports/general-ledger.zip
diff --git a/contrib/non-profit-audit-reports/tests/non-profit-test-data_MANIFEST b/contrib/non-profit-audit-reports/tests/non-profit-test-data_MANIFEST
new file mode 100644
index 00000000..b8bfc107
--- /dev/null
+++ b/contrib/non-profit-audit-reports/tests/non-profit-test-data_MANIFEST
@@ -0,0 +1,10 @@
+chart-of-accounts.csv
+general-ledger.txt
+general-ledger.csv
+Financial/BankStuff/bank-statement.pdf
+Financial/Invoices/Invoice20110510.pdf
+Projects/Foo/Invoices/Invoice20100101.pdf
+Projects/Foo/earmark-record.txt
+Projects/Blah/Expenses/hosting/AprilHostingReceipt.pdf
+Projects/Blah/Expenses/hosting/april-invoice.pdf
+Projects/Foo/Expenses/hosting/AprilHostingReceipt.pdf
-- 
cgit v1.2.3


From 4290a4ec52793baeaaa3ae6dbb75cbef993d3ef4 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
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')

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 (<FILE>) {
+    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 =~ /\<Adjustment\>/ 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 b04fbb1b7306ce202a015dd7587fe9c396e27103 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Sat, 5 Jan 2013 17:39:12 -0500
Subject: First crack at an unpaid accruals report.

---
 .../unpaid-accruals-report.plx                     | 84 ++++++++++++++++++++++
 1 file changed, 84 insertions(+)
 create mode 100755 contrib/non-profit-audit-reports/unpaid-accruals-report.plx

(limited to 'contrib')

diff --git a/contrib/non-profit-audit-reports/unpaid-accruals-report.plx b/contrib/non-profit-audit-reports/unpaid-accruals-report.plx
new file mode 100755
index 00000000..3d4c9cb0
--- /dev/null
+++ b/contrib/non-profit-audit-reports/unpaid-accruals-report.plx
@@ -0,0 +1,84 @@
+#!/usr/bin/perl
+# unpaid-acccurals-report.plx                                    -*- Perl -*-
+
+#  This report is designed to create what our accounts call a "Schedule of
+#  accounts payable".  and "Schedule of accounts receivable".
+
+
+
+# Copyright (C) 2013  Bradley M. Kuhn
+#
+# This program gives you software freedom; you can copy, modify, convey,
+# and/or redistribute it under the terms of the GNU General Public License
+# as published by the Free Software Foundation; either version 3 of the
+# License, or (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful, but
+# WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+# General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License along
+# with this program in a file called 'GPLv3'.  If not, write to the:
+#    Free Software Foundation, Inc., 51 Franklin St, Fifth Floor
+#                                    Boston, MA 02110-1301, USA.
+
+use strict;
+use warnings;
+
+use Math::BigFloat;
+use Date::Manip;
+
+my $LEDGER_CMD = "/usr/local/bin/ledger";
+
+my $ACCT_WIDTH = 70;
+
+sub ParseNumber($) {
+  $_[0] =~ s/,//g;
+  return Math::BigFloat->new($_[0]);
+}
+Math::BigFloat->precision(-2);
+my $ZERO =  Math::BigFloat->new("0.00");
+my $TWO_CENTS =  Math::BigFloat->new("0.02");
+
+if (@ARGV < 2) {
+  print STDERR "usage: $0 <START_DATE> <END_DATE> <LEDGER_OPTIONS>\n";
+  exit 1;
+}
+my($startDate, $endDate, @mainLedgerOptions) = @ARGV;
+
+my $err;
+my $formattedEndDate = UnixDate(DateCalc(ParseDate($endDate), ParseDateDelta("- 1 day"), \$err),
+                                "%Y/%m/%d");
+die "Date calculation error on $endDate" if ($err);
+my $formattedStartDate = UnixDate(ParseDate($startDate), "%Y/%m/%d");
+die "Date calculation error on $startDate" if ($err);
+
+my(@ledgerOptions) = (@mainLedgerOptions,
+                      '-V', '-X', '$', '-F', 
+                      '\"%(tag("Invoice"))\",\"%A\",\"%(date)\",\"%(payee)\",\"%22.108t\"\n',
+                      '--limit', 'tag("Invoice") !~ /^\s*$/', 'reg');
+
+my @possibleTypes = ('Accrued:Loans Receivable', 'Accrued:Accounts Payable',
+                     'Accrued:Accounts Receivable', 'Accrued:Expenses');
+
+
+foreach my $type (@possibleTypes) {
+  open(LEDGER_FUNDS, "-|", $LEDGER_CMD, @ledgerOptions, "/^$type/")
+    or die "Unable to run $LEDGER_CMD @ledgerOptions: $!";
+
+  while (my $line = <LEDGER_FUNDS>) {
+    next if $line =~ /"\<Adjustment\>"/;
+    die "Unable to parse output line $line from @ledgerOptions"
+
+      unless $line =~ /^\s*"([^"]+)","([^"]+)","([^"]+)","([^"]+)","\s*\$\s*([\-\d\.\,]+)"\s*$/;
+    my($invoice, $account, $date, $payee, $amount) = ($1, $2, $3, $4, $5);
+      $amount = ParseNumber($amount);
+  }
+}
+###############################################################################
+#
+# Local variables:
+# compile-command: "perl -c unpaid-accruals-report.plx"
+# End:
+
-- 
cgit v1.2.3


From 39db5bbce77c3173b006755fc71ce95ba0e4041a Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Sun, 6 Jan 2013 08:21:35 -0500
Subject: Ordering of options had always been incorrect on this call; Fixed.

---
 contrib/non-profit-audit-reports/csv2ods.py | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'contrib')

diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py
index aded8e65..e6d33906 100755
--- a/contrib/non-profit-audit-reports/csv2ods.py
+++ b/contrib/non-profit-audit-reports/csv2ods.py
@@ -113,7 +113,7 @@ def main():
         print 'csv:', options.csv
         print 'ods:', options.ods
         print 'ods:', options.encoding
-    csv2ods(options.csv, options.ods, options.verbose, options.encoding, options.skip_page_break)
+    csv2ods(options.csv, options.ods, options.encoding, options.verbose, options.skip_page_break)
 
 if __name__ == '__main__':
   main()
-- 
cgit v1.2.3


From 2142a36c1c81bae4ea5bdb6fde65141e8dca5227 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Sun, 6 Jan 2013 19:28:21 -0500
Subject: Completed report on unpaid accruals.

---
 .../unpaid-accruals-report.plx                     | 32 ++++++++++++++++++++--
 1 file changed, 29 insertions(+), 3 deletions(-)

(limited to 'contrib')

diff --git a/contrib/non-profit-audit-reports/unpaid-accruals-report.plx b/contrib/non-profit-audit-reports/unpaid-accruals-report.plx
index 3d4c9cb0..f481e02f 100755
--- a/contrib/non-profit-audit-reports/unpaid-accruals-report.plx
+++ b/contrib/non-profit-audit-reports/unpaid-accruals-report.plx
@@ -55,14 +55,14 @@ my $formattedStartDate = UnixDate(ParseDate($startDate), "%Y/%m/%d");
 die "Date calculation error on $startDate" if ($err);
 
 my(@ledgerOptions) = (@mainLedgerOptions,
-                      '-V', '-X', '$', '-F', 
+                      '-V', '-X', '$',  '-e', $endDate, '-F',
                       '\"%(tag("Invoice"))\",\"%A\",\"%(date)\",\"%(payee)\",\"%22.108t\"\n',
                       '--limit', 'tag("Invoice") !~ /^\s*$/', 'reg');
 
 my @possibleTypes = ('Accrued:Loans Receivable', 'Accrued:Accounts Payable',
                      'Accrued:Accounts Receivable', 'Accrued:Expenses');
 
-
+my %data;
 foreach my $type (@possibleTypes) {
   open(LEDGER_FUNDS, "-|", $LEDGER_CMD, @ledgerOptions, "/^$type/")
     or die "Unable to run $LEDGER_CMD @ledgerOptions: $!";
@@ -73,8 +73,34 @@ foreach my $type (@possibleTypes) {
 
       unless $line =~ /^\s*"([^"]+)","([^"]+)","([^"]+)","([^"]+)","\s*\$\s*([\-\d\.\,]+)"\s*$/;
     my($invoice, $account, $date, $payee, $amount) = ($1, $2, $3, $4, $5);
-      $amount = ParseNumber($amount);
+    $amount = ParseNumber($amount);
+
+    push(@{$data{$type}{$invoice}{entries}}, { account => $account, date => $date, payee => $payee, amount => $amount});
+    $data{$type}{$invoice}{total} = $ZERO unless defined $data{$type}{$invoice}{total};
+    $data{$type}{$invoice}{total} += $amount;
+  }
+  close LEDGER_FUNDS;
+  die "Failure on ledger command for $type: $!" unless ($? == 0);
+
+}
+foreach my $type (keys %data) {
+  foreach my $invoice (keys %{$data{$type}}) {
+    delete $data{$type}{$invoice} if abs($data{$type}{$invoice}{total}) <= $TWO_CENTS;
+  }
+}
+foreach my $type (keys %data) {
+  delete $data{$type} if scalar(keys %{$data{$type}}) == 0;
+}
+foreach my $type (keys %data) {
+  print "\"SCHEDULE OF $type\"\n\"ENDING:\",\"$formattedEndDate\"\n\n",
+    '"DATE","PAYEE","ACCOUNT","AMOUNT","INVOICE"', "\n";
+  foreach my $invoice (keys %{$data{$type}}) {
+    my $vals;
+    foreach my $vals (@{$data{$type}{$invoice}{entries}}) {
+      print "\"$vals->{date}\",\"$vals->{payee}\",\"$vals->{account}\",\"\$$vals->{amount}\",\"link:$invoice\"\n";
+    }
   }
+  print "pagebreak\n";
 }
 ###############################################################################
 #
-- 
cgit v1.2.3


From 6d98bc58ae555d5d4487e7ee90c6ce8b2a49cbe6 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Sun, 6 Jan 2013 19:28:46 -0500
Subject: Correct sorting of output in trial balance report.

---
 contrib/non-profit-audit-reports/summary-reports.plx | 9 ++++++---
 1 file changed, 6 insertions(+), 3 deletions(-)

(limited to 'contrib')

diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx
index bbe83c88..e9e1a3b8 100755
--- a/contrib/non-profit-audit-reports/summary-reports.plx
+++ b/contrib/non-profit-audit-reports/summary-reports.plx
@@ -430,10 +430,13 @@ my $curOn = 'Assets';
 foreach my $account (sort preferredAccountSorting keys %fullAccountList) {
   # Blank lines right
   if ($account !~ /^$curOn/) {
-    print TRIAL "\n";
+    print TRIAL "pagebreak\n";
     $curOn = $account;
-    $curOn =~ s/^([^:]+):.*$/$1/;
-    print "CurOn now: $curOn\n";
+    if ($curOn =~ /(Accrued:[^:]+):.*$/) {
+      $curOn = $1;
+    } else {
+      $curOn =~ s/^([^:]+):.*$/$1/;
+    }
   }
   if ($account =~ /^Assets|Liabilities|Accrued|Unearned Income/) {
     foreach my $id (qw/totalBeginFY totalEndFY amountInYear/) {
-- 
cgit v1.2.3


From 8cddda4c3eb67234c12285fe52bd2bc328f6678b Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
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')

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 = <CSV_DATA>) { print CSV_OUT $line; }
+    while (my $line = <CSV_DATA>) { $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 (<GL_CSV_DATA>) {
+    $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


From 9d78dc639593e5ae6f4ccbf7867131763df33dcd Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Sun, 6 Jan 2013 19:48:22 -0500
Subject: The restricted fund report has been almost entirely rewritten.

The previous version was somewhat confusing anyway. Now it builds a
relatively clear spreadsheet of all categories.  It also now outputs CSV.
---
 contrib/non-profit-audit-reports/fund-report.plx | 221 ++++++++++++++++-------
 1 file changed, 151 insertions(+), 70 deletions(-)

(limited to 'contrib')

diff --git a/contrib/non-profit-audit-reports/fund-report.plx b/contrib/non-profit-audit-reports/fund-report.plx
index 0c03d009..ce59da96 100755
--- a/contrib/non-profit-audit-reports/fund-report.plx
+++ b/contrib/non-profit-audit-reports/fund-report.plx
@@ -1,9 +1,19 @@
 #!/usr/bin/perl
 # fund-report.plx                                    -*- Perl -*-
 #
-#    Script to generate a Trial Balance report for a ledger.
+#    Script to generate a Restricted Fund Report.  Usefulness of this
+#    script may be confined to those who track separate funds in their
+#    accounts by having accounts that match this format:
+#     /^(Income|Expenses|Unearned Income|(Accrued:[^:]+:):PROJECTNAME/
+
+#  Conservancy does this because we carefully track fund balances for our
+#  fiscal sponsored projects.  Those who aren't fiscal sponsors won't find
+#  this report all that useful, I suspect.  Note that the name
+#  "Conservancy" is special-cased in a few places, mainly because our
+#  "General" fund is called "Conservancy".
+
 #
-# Copyright (C) 2011, 2012, Bradley M. Kuhn
+# Copyright (C) 2011, 2012, 2013 Bradley M. Kuhn
 #
 # This program gives you software freedom; you can copy, modify, convey,
 # and/or redistribute it under the terms of the GNU General Public License
@@ -36,6 +46,7 @@ sub ParseNumber($) {
 }
 Math::BigFloat->precision(-2);
 my $ZERO =  Math::BigFloat->new("0.00");
+my $TWO_CENTS =  Math::BigFloat->new("0.02");
 
 if (@ARGV < 2) {
   print STDERR "usage: $0 <START_DATE> <END_DATE> <LEDGER_OPTIONS>\n";
@@ -45,71 +56,65 @@ my($startDate, $endDate, @mainLedgerOptions) = @ARGV;
 
 my $err;
 my $formattedEndDate = UnixDate(DateCalc(ParseDate($endDate), ParseDateDelta("- 1 day"), \$err),
-                                "%B %e, %Y");
+                                "%Y/%m/%d");
 die "Date calculation error on $endDate" if ($err);
-my $formattedStartDate = UnixDate(ParseDate($startDate), "%B %e, %Y");
+my $formattedStartDate = UnixDate(ParseDate($startDate), "%Y/%m/%d");
 die "Date calculation error on $startDate" if ($err);
 
-# First, get fund list from ending balance
-my(@ledgerOptions) = (@mainLedgerOptions,
-                      '-V', '-X', '$', '-F', "%-.70A %22.108t\n", '-s',
-                      '-e', $endDate, 'reg', '/^Funds:Restricted:/');
+# First, get balances for starting and ending for each fund
+
 my %funds;
 
-open(LEDGER_FUNDS, "-|", $LEDGER_CMD, @ledgerOptions)
-  or die "Unable to run $LEDGER_CMD for funds: $!";
-
-while (my $fundLine = <LEDGER_FUNDS>) {
-  die "Unable to parse output line from first funds command: \"$fundLine\""
-    unless $fundLine =~ /^\s*([^\$]+)\s+\$\s*\s*([\-\d\.\,]+)/;
-  my($account, $amount) = ($1, $2);
-  $amount = ParseNumber($amount);
-  $account =~ s/\s+$//;
-  next if $account =~ /\<Adjustment\>/ and (abs($amount) <= 0.02);
-  die "Weird account found, $account with amount of $amount in first funds command\n"
-    unless $account =~ s/^\s*Funds:Restricted://;
-  $funds{$account}{ending} = $amount;
-}
-close LEDGER_FUNDS;
+foreach my $type ('starting', 'ending') {
+  my(@ledgerOptions) = (@mainLedgerOptions,
+                      '-V', '-X', '$', '-F', "%-.70A %22.108t\n", '-s');
 
-# First, get fund list from starting balance
-@ledgerOptions = (@mainLedgerOptions,
-                  '-V', '-X', '$', '-F', "%-.70A %22.108t\n",  '-w', '-s',
-                  '-e', $startDate, 'reg', '^Funds:Restricted:');
-
-open(LEDGER_FUNDS, "-|", $LEDGER_CMD, @ledgerOptions)
-  or die "Unable to run $LEDGER_CMD for funds: $!";
-
-while (my $fundLine = <LEDGER_FUNDS>) {
-  die "Unable to parse output line from second funds command: $fundLine"
-    unless $fundLine =~ /^\s*([^\$]+)\s+\$\s*([\-\d\.\,]+)/;
-  my($account, $amount) = ($1, $2);
-  $amount = ParseNumber($amount);
-  $account =~ s/\s+$//;
-  next if $account =~ /\<Adjustment\>/ and (abs($amount) <= 0.02);
-  die "Weird account found, $account with amount of $amount in first second command\n"
-    unless $account =~ s/^\s*Funds:Restricted://;
-  $funds{$account}{starting} = $amount;
+  if ($type eq 'starting') {
+    push(@ledgerOptions, '-e', $startDate);
+  } else {
+    push(@ledgerOptions,'-e', $endDate);
+  }
+  push(@ledgerOptions, 'reg', '/^(Income|Expenses):([^:]+):/');
+
+  open(LEDGER_FUNDS, "-|", $LEDGER_CMD, @ledgerOptions)
+    or die "Unable to run $LEDGER_CMD @ledgerOptions: $!";
+
+  while (my $fundLine = <LEDGER_FUNDS>) {
+    die "Unable to parse output line from first funds command: \"$fundLine\""
+      unless $fundLine =~ /^\s*([^\$]+)\s+\$\s*\s*([\-\d\.\,]+)/;
+    my($account, $amount) = ($1, $2);
+    $amount = ParseNumber($amount);
+    $account =~ s/\s+$//;
+    next if $account =~ /\<Adjustment\>/ and (abs($amount) <= $TWO_CENTS);
+    die "Weird account found, $account with amount of $amount in command: @ledgerOptions\n"
+      unless $account =~ s/^\s*(?:Income|Expenses):([^:]+)://;
+    $account = $1;
+    $account = 'General' if $account eq 'Conservancy';   # FIXME: this is a special case for Consrevancy
+    $funds{$account}{$type} += $amount;
+  }
+  close LEDGER_FUNDS;
+  die "Failure on ledger command @ledgerOptions: $!" unless ($? == 0);
 }
-close LEDGER_FUNDS;
-
-
 foreach my $fund (keys %funds) {
-  $funds{$fund}{starting} = $ZERO if not defined $funds{$fund}{starting};
+  foreach my $type (keys %{$funds{$fund}}) {
+    $funds{$fund}{$type} = $ZERO - $funds{$fund}{$type};
+  }
 }
-
-@ledgerOptions = (@mainLedgerOptions,
+my(@ledgerOptions) = (@mainLedgerOptions,
                   '-V', '-X', '$', '-F', "%-.70A %22.108t\n",  '-w', '-s',
                   '-b', $startDate, '-e', $endDate, 'reg');
 
-my @possibleTypes = ('Unearned Income', 'Retained Earnings', 'Retained Costs',
-                     'Accrued:Accounts Payable', 'Accrued:Accounts Receivable');
+my @possibleTypes = ('Income', 'Expenses', 'Unearned Income', 'Retained Earnings', 'Retained Costs',
+                     'Accrued:Loans Receivable', 'Accrued:Accounts Payable',
+                     'Accrued:Accounts Receivable', 'Accrued:Expenses');
 
-foreach my $type ('Income', 'Expenses', @possibleTypes) {
+foreach my $type (@possibleTypes) {
   foreach my $fund (keys %funds) {
-    open(LEDGER_INCOME, "-|", $LEDGER_CMD, @ledgerOptions, "^${type}:$fund")
+    my $query;
+    $query = ($fund eq 'General') ? "/^${type}:Conservancy/": "/^${type}:$fund/";
+    open(LEDGER_INCOME, "-|", $LEDGER_CMD, @ledgerOptions, $query)
       or die "Unable to run $LEDGER_CMD for funds: $!";
-      $funds{$fund}{$type} = $ZERO;
+    $funds{$fund}{$type} = $ZERO;
     while (my $line = <LEDGER_INCOME>) {
       die "Unable to parse output line from $type line command: $line"
         unless $line =~ /^\s*([^\$]+)\s+\$\s*\s*([\-\d\.\,]+)/;
@@ -118,34 +123,110 @@ foreach my $type ('Income', 'Expenses', @possibleTypes) {
       $funds{$fund}{$type} += $amount;
     }
     close LEDGER_INCOME;
+    die "Failure on ledger command for ${type}:$fund: $!" unless ($? == 0);
+  }
+}
+
+my %tot;
+($tot{Start}, $tot{End}) = ($ZERO, $ZERO);
+
+my %beforeEndings = ('Income' => 1, 'Expenses' => 1);
+my %afterEndings;
+
+# For other @possibleTypes, build up @fieldsList to just thoes that are present. 
+
+foreach my $fund (keys %funds) {
+  foreach my $type (@possibleTypes) {
+    if ($funds{$fund}{$type} != $ZERO) {
+      if ($type =~ /^(Unearned Income|Accrued)/) {
+        $afterEndings{$type} = 1;
+      } else {
+        $beforeEndings{$type} = 1;
+      }
+    }
   }
 }
+my(@beforeEndingFields, @afterEndingFields);
+
+foreach my $ii (@possibleTypes) {
+  push(@beforeEndingFields, $ii) if defined $beforeEndings{$ii};
+  push(@afterEndingFields, $ii)  if defined $afterEndings{$ii};
+}
+# Make sure fieldLists present items are zero for those that should be zero.
+foreach my $fund (keys %funds) {
+  foreach my $type ('starting', @beforeEndingFields, 'ending', @afterEndingFields) {
+    $funds{$fund}{$type} = $ZERO unless defined $funds{$fund}{$type};
+  }
+}
+
+print '"RESTRICTED AND GENERAL FUND REPORT",', "\"BEGINNING:\",\"$formattedStartDate\",\"ENDING:\",\"$formattedEndDate\"\n\n";
+print '"FUND","STARTING BALANCE",';
+my @finalPrints;
+foreach my $type (@beforeEndingFields) {
+  $tot{$type} = $ZERO;
+  my $formattedType = $type;
+  print "\"$formattedType\",";
+}
+print '"ENDING BALANCE",""';
+foreach my $type (@afterEndingFields) {
+  $tot{$type} = $ZERO;
+  my $formattedType = $type;
+  $formattedType = "Prepaid Expenses" if $formattedType eq 'Accrued:Expenses'; 
+  $formattedType =~ s/^Accrued://;
+  print ",\"$formattedType\"";
+}
+print "\n\n";
 
-my($totStart, $totEnd) = ($ZERO, $ZERO);
+sub printTotal ($$) {
+  my($label, $tot) = @_;
+  print "\"$label\",\"\$$tot->{Start}\",";
+  foreach my $type (@beforeEndingFields) {
+    print "\"\$$tot->{$type}\",";
+  }
+  print "\"\$$tot->{End}\",\"\"";
+  foreach my $type (@afterEndingFields) {
+    print ",\"\$$tot->{$type}\"";
+  }
+  print "\n";
+}
 
-foreach my $fund (sort keys %funds) {
+foreach my $fund (sort {
+                        if ($a eq "General") { return 1 }
+                        elsif ($b eq "General") { return -1 }
+                        else { return $a cmp $b } }
+                  keys %funds) {
   my $sanityTotal = $funds{$fund}{starting};
-  print "Fund: $fund\n", sprintf("%-35s\$%26.2f\n\n", "Balance as of $formattedStartDate:",
-      $funds{$fund}{starting});
-  foreach my $type ('Income', 'Expenses', @possibleTypes) {
-    my $formattedType = $type;   $formattedType =~ s/^Accrued://;
-    next if $type ne 'Income' and $type ne 'Expenses' and $funds{$fund}{$type} == $ZERO;
-    print sprintf("%19s during period: \$%26.2f\n", $formattedType, $funds{$fund}{$type});
+
+  if ($fund eq 'General') {
+    print "\n";
+    printTotal("Restricted Subtotal", \%tot);
+    print "\n";
+  }
+  $tot{Start} += $funds{$fund}{starting};
+  $tot{End} += $funds{$fund}{ending};
+
+  print "\"$fund\",\"\$$funds{$fund}{starting}\",";
+  foreach my $type (@beforeEndingFields) {
+    print "\"\$$funds{$fund}{$type}\",";
+    $tot{$type} += $funds{$fund}{$type};
+  }
+  print "\"\$$funds{$fund}{ending}\",\"\"";
+  foreach my $type (@afterEndingFields) {
+    print ",\"\$$funds{$fund}{$type}\"";
+    $tot{$type} += $funds{$fund}{$type};
   }
-  print sprintf("\n%-35s\$%26.2f\n", "Balance as of $formattedEndDate:",
-      $funds{$fund}{ending}), "\n\n";
+  print "\n";
   # Santity check:
-  if ($funds{$fund}{ending} !=
+  if (abs($funds{$fund}{ending} -
       ($funds{$fund}{starting}
-         - $funds{$fund}{Income} - $funds{$fund}{'Unearned Income'} - $funds{$fund}{Expenses})) {
-    print "$fund FAILED SANITY CHECK\n\n\n";
-    die "$fund FAILED SANITY CHECK";
+         - $funds{$fund}{Income} - $funds{$fund}{Expenses}))
+      > $TWO_CENTS) {
+    print "$fund FAILED SANITY CHECK: Ending: $funds{$fund}{ending} \n\n\n";
+    warn "$fund FAILED SANITY CHECK";
   }
-  $totStart += $funds{$fund}{starting};
-  $totEnd += $funds{$fund}{ending};
 }
-print "\n\n\nTotal Restricted Funds as of $formattedStartDate: ", sprintf("\$%15.2f\n", $totStart);
-print "\nTotal Restricted Funds as of $formattedStartDate: ", sprintf("\$%15.2f\n", $totEnd);
+print "\n";
+printTotal("OVERALL TOTAL", \%tot);
 ###############################################################################
 #
 # Local variables:
-- 
cgit v1.2.3