Merge pull request #157 from bkuhn/more-non-profit-audit-reports-updates

More non profit audit reports updates

5 files changed, 408 insertions, 45 deletions

diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx
new file mode 100755
index 00000000..7a8da911
--- /dev/null
+++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx
@@ -0,0 +1,232 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+
+use Math::BigFloat;
+use Date::Manip;
+use Data::PowerSet;
+
+Math::BigFloat->precision(-2);
+my $ZERO =  Math::BigFloat->new("0.00");
+my $ONE_HUNDRED =  Math::BigFloat->new("100.00");
+
+my $VERBOSE = 1;
+my $DEBUG = 0;
+
+my $LEDGER_BIN = "/usr/local/bin/ledger";
+
+######################################################################
+sub BruteForceSubSetSumSolver ($$$) {
+  my($numberList, $totalSought, $extractNumber) = @_;
+
+  my($P, $N) = (0, 0);
+  my $size = scalar(@{$numberList});
+  my %Q;
+  my(@L) =
+     map { { val => &$extractNumber($_), obj => $_ } } @{$numberList};
+
+  my $powerset = Data::PowerSet->new(@L);
+
+  while (my $set = $powerset->next) {
+    my $total = $ZERO;
+    foreach my $ee (@{$set}) {
+      $total += $ee->{val};
+    }
+    if ($totalSought == $total) {
+      my(@list) = map { $_->{obj} } @{$set};
+      return (1, \@list);
+    }
+  }
+  return (0, []);
+}
+######################################################################
+sub DynamicProgrammingSubSetSumSolver ($$$) {
+  my($numberList, $totalSought, $extractNumber) = @_;
+
+  my($P, $N) = (0, 0);
+  my $size = scalar(@{$numberList});
+  my %Q;
+  my(@L) =
+     map { { val => &$extractNumber($_), obj => $_ } } @{$numberList};
+
+  print STDERR "  TotalSought:", $totalSought if $VERBOSE;
+  print STDERR "  L in this iteration:\n     [" if $VERBOSE;
+
+  foreach my $ee (@L) {
+    if ($ee->{val} < 0) {
+      $N += $ee->{val}
+    } else {
+      $P += $ee->{val};
+    }
+    print STDERR $ee->{val}, ", " if $VERBOSE;
+  }
+  print STDERR "]\n    P = $P, N = $N\n" if ($VERBOSE);
+
+  for (my $ii = 0 ; $ii <= $size ; $ii++ ) {
+    $Q{$ii}{0}{value} = 1;
+    $Q{$ii}{0}{list} = [];
+  }
+  for (my $jj = $N; $jj <= $P ; $jj++) {
+    $Q{0}{$jj}{value} = ($L[0]{val} == $jj);
+    $Q{0}{$jj}{list} = $Q{0}{$jj}{value} ? [ $L[0]{obj} ] : [];
+  }
+  for (my $ii = 1; $ii <= $size ; $ii++ ) {
+    for (my $jj = $N; $jj <= $P ; $jj++) {
+      if ($Q{$ii-1}{$jj}{value}) {
+        $Q{$ii}{$jj}{value} = 1;
+
+        $Q{$ii}{$jj}{list} = [] unless defined $Q{$ii}{$jj}{list};
+        push(@{$Q{$ii}{$jj}{list}}, @{$Q{$ii-1}{$jj}{list}});
+
+      } elsif ($L[$ii]{val} == $jj) {
+        $Q{$ii}{$jj}{value} = 1;
+
+        $Q{$ii}{$jj}{list} = [] unless defined $Q{$ii}{$jj}{list};
+        push(@{$Q{$ii}{$jj}{list}}, $jj);
+      } elsif ($Q{$ii-1}{$jj - $L[$ii]{val}}{value}) {
+        $Q{$ii}{$jj}{value} = 1;
+        $Q{$ii}{$jj}{list} = [] unless defined $Q{$ii}{$jj}{list};
+        push(@{$Q{$ii}{$jj}{list}}, $L[$ii]{obj}, @{$Q{$ii-1}{$jj - $L[$ii]{val}}{list}});
+      } else {
+        $Q{$ii}{$jj}{value} = 0;
+        $Q{$ii}{$jj}{list} = [];
+      }
+    }
+  }
+  foreach (my $ii = 0; $ii <= $size; $ii++) {
+    foreach (my $jj = $N; $jj <= $P; $jj++) {
+      print "Q($ii, $jj) == $Q{$ii}{$jj}{value} with List of ", join(", ", @{$Q{$ii}{$jj}{list}}), "\n";
+    }
+  }
+  return [ $Q{$size}{$totalSought}{value}, \@{$Q{$size}{$totalSought}{list}}];
+}
+######################################################################
+sub Commify ($) {
+    my $text = reverse $_[0];
+    $text =~ s/(\d\d\d)(?=\d)(?!\d*\.)/$1,/g;
+    return scalar reverse $text;
+}
+######################################################################
+sub ParseNumber($) {
+  $_[0] =~ s/,//g;
+  return Math::BigFloat->new($_[0]);
+}
+######################################################################
+sub ConvertTwoDigitPrecisionToInteger ($) {
+  return sprintf("%d", $_[0] * $ONE_HUNDRED);
+}
+######################################################################
+sub ConvertTwoDigitPrecisionToIntegerInEntry ($) {
+  return ConvertTwoDigitPrecisionToInteger($_[0]->{amount});
+}
+######################################################################
+my $firstArg = shift @ARGV;
+
+my $solver = \&BruteForceSubSetSumSolver;
+
+if (@ARGV < 7) {
+  print STDERR "usage: $0 [-d] <TITLE> <ACCOUNT_REGEX> <END_DATE> <START_SEARCH_FROM_DATE> <END_SEARCH_TO_DATE> <BANK_STATEMENT_BALANCE> <LEDGER_OPTIONS>\n";
+  exit 1;
+}
+if ($firstArg eq '-d') {
+  $solver = \&DynamicProgrammingSubSetSumSolver;
+} else {
+  unshift(@ARGV, $firstArg);
+}
+my($title, $account, $endDate, $startSearchFromDate, $endSearchToDate, $bankBalance, @mainLedgerOptions) = @ARGV;
+
+$bankBalance = ParseNumber($bankBalance);
+
+my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$',
+                    '-e', $endDate, '-F', '%t\n', 'bal', "/$account/");
+
+open(FILE, "-|", @fullCommand) or die "unable to run command ledger command: @fullCommand: $!";
+
+my $total;
+foreach my $line (<FILE>) {
+  chomp $line;
+  die "Unable to parse output line from: \"$line\""
+    unless $line =~ /^\s*\$\s*([\-\d\.\,]+)\s*$/  and not defined $total;
+  $total = $1;
+  $total = ParseNumber($total);
+}
+close FILE;
+if (not defined $total or $? != 0) {
+  die "unable to run ledger @fullCommand: $!";
+}
+my $differenceSought = $total - $bankBalance;
+
+my $err;
+my $formattedEndDate = UnixDate(DateCalc(ParseDate($endDate), ParseDateDelta("- 1 day"), \$err),
+                                "%Y-%m-%d");
+die "Date calculation error on $endDate" if ($err);
+
+my $earliestStartDate = DateCalc(ParseDate($endDate), ParseDateDelta("- 1 month"), \$err);
+
+die "Date calculation error on $endDate" if ($err);
+
+my $startDate = ParseDate($startSearchFromDate);
+
+my @solution;
+while ($startDate ge $earliestStartDate) {
+  $startDate = DateCalc(ParseDate($startDate), ParseDateDelta("- 1 day"), \$err);
+  die "Date calculation error on $endDate" if ($err);
+
+  my $formattedStartDate = UnixDate($startDate, "%Y-%m-%d");
+
+  print STDERR "Testing $formattedStartDate through $endSearchToDate for a total of ", Commify($differenceSought), ": \n"
+    if $VERBOSE;
+
+  my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$',
+                      '-b', $formattedStartDate, '-e', $endSearchToDate,
+                      '-F', '"%(date)","%C","%P","%t"\n',
+                      'reg', "/$account/");
+
+  open(FILE, "-|", @fullCommand)
+    or die "unable to run command ledger command: @fullCommand: $!";
+
+  my @entries;
+
+  foreach my $line (<FILE>) {
+    die "Unable to parse output line from: $line"
+      unless $line =~ /^\s*"([^"]*)","([^"]*)","([^"]*)","([^"]*)"\s*$/;
+    my($date, $checkNum, $payee, $amount) = ($1, $2, $3, $4);
+    die "$amount is not a valid amount"
+      unless $amount =~ s/\s*\$\s*([\-\d\.\,]+)\s*$/$1/;
+    $amount = ParseNumber($amount);
+
+    push(@entries, { date => $date, checkNum => $checkNum, 
+                     payee => $payee, amount => $amount });
+  }
+  close FILE;
+  die "unable to properly run ledger command: @fullCommand: $!" unless ($? == 0);
+
+  @solution = $solver->(\@entries,
+                        ConvertTwoDigitPrecisionToInteger($differenceSought),
+                        \&ConvertTwoDigitPrecisionToIntegerInEntry);
+  if ($VERBOSE) {
+    if ($solution[0]) {
+      use Data::Dumper;
+      print STDERR "Solution for $formattedStartDate to $formattedEndDate, $differenceSought: \n",
+        Data::Dumper->Dump(\@solution);
+    } else {
+      print STDERR "No Solution Found. :(\n";
+    }
+  }
+  last if ($solution[0]);
+}
+if ($solution[0]) {
+  print "\"title:$formattedEndDate: $title\"\n\"BANK RECONCILATION: $account\",\"ENDING\",\"$formattedEndDate\"\n";
+  print "\n\n\"DATE\",\"CHECK NUM\",\"PAYEE\",\"AMOUNT\"\n\n";
+  print "\"$formattedEndDate\",\"\",\"BANK ACCOUNT BALANCE\",\"\$$bankBalance\"\n\n";
+  foreach my $ee (sort { $a->{date} cmp $b->{date} } @{$solution[1]}) {
+    print "\"$ee->{date}\",\"$ee->{checkNum}\",\"$ee->{payee}\",\"\$$ee->{amount}\"\n";
+  }
+    print "\n\"$formattedEndDate\",\"\",\"OUR ACCOUNT BALANCE\",\"\$$total\"\n\n";
+}
+###############################################################################
+#
+# Local variables:
+# compile-command: "perl -c bank-reconcilation.plx"
+# End:
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 2ad18a44..cb19a19e 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
@@ -4,7 +4,7 @@
 #    Script to generate a General Ledger report that accountants like
 #    using Ledger.
 #
-# 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
@@ -21,6 +21,7 @@
 #    Free Software Foundation, Inc., 51 Franklin St, Fifth Floor
 #                                    Boston, MA 02110-1301, USA.
 
+
 use strict;
 use warnings;
 
@@ -76,21 +77,14 @@ die "bad one day less" if $oneDayLess->parse("- 1 day");
 $formattedEndDate = $formattedEndDate->calc($oneDayLess);
 $formattedEndDate = $formattedEndDate->printf("%Y/%m/%d");
 
-foreach my $acct (@accounts) {
-  next unless ($acct =~ /^(?:Assets|Liabilities)/);
-
-  my $acctFilename = LedgerAcctToFilename($acct);
+foreach my $typeData ({ name => 'disbursements', query => 'a<=0' },
+                      { name => 'receipts', query => 'a>0' }) {
+  my $fileNameBase = $typeData->{name};
 
-  foreach my $typeData ({ name => 'disbursements', query => 'a<=0' },
-                         { name => 'receipts', query => 'a>0' }) {
-    my $fileNameBase = $acctFilename . '-' . $typeData->{name};
+  open(CSV_OUT, ">", "$fileNameBase.csv") or die "unable to open $fileNameBase.csv: $!";
 
-    open(TEXT_OUT, ">", "$fileNameBase.txt") or die "unable to open $fileNameBase.txt: $!";
-    open(CSV_OUT, ">", "$fileNameBase.csv") or die "unable to open $fileNameBase.csv: $!";
-
-    print TEXT_OUT "\n\nACCOUNT: $acct\nFROM:    $beginDate TO $formattedEndDate\n\n";
-    print CSV_OUT "\n\"ACCOUNT:\",\"$acct\"\n\"PERIOD START:\",\"$beginDate\"\n\"PERIOD END:\",\"$formattedEndDate\"\n";
-    print CSV_OUT '"DATE","CHECK NUM","NAME","ACCOUNT","AMOUNT"';
+  foreach my $acct (sort { $a cmp $b } @accounts) {
+    next unless ($acct =~ /^(?:Assets|Liabilities)/);
 
     my @entryLedgerOpts = ('-l', $typeData->{query},
                            '-b', $beginDate, '-e', $endDate, @otherLedgerOpts, 'print', $acct);
@@ -106,38 +100,70 @@ foreach my $acct (@accounts) {
 
     goto SKIP_REGISTER_COMMANDS if (-z $tempFile);
 
-    my @txtRegLedgerOpts = ('-f', $tempFile, '-V', '-F',
-                            "%(date)  %-.70P  %-.10C  %-.80A  %18t\n", '-w', '--sort', 'd',
-                            '-b', $beginDate, '-e', $endDate, 'reg');
+    print CSV_OUT "\"ACCOUNT:\",\"$acct\"\n\"PERIOD START:\",\"$beginDate\"\n\"PERIOD END:\",\"$formattedEndDate\"\n";
+    print CSV_OUT '"DATE","CHECK NUM","NAME","ACCOUNT","AMOUNT"';
 
-    my $formatString = '\n"%(date)","%C","%P","%A","%t"\n%/"","","","%A","%t"';
+    my $formatString = '\n"%(date)","%C","%P","%A","%t"';
+    my $tagStrings = "";
     foreach my $tagField (qw/Receipt Invoice Statement Contract PurchaseOrder Approval Check IncomeDistributionAnalysis CurrencyRate/) {
       print CSV_OUT ',"', $tagField, '"';
-      $formatString .= ',"link:%(tag(\'' . $tagField . '\'))"';
+      $tagStrings .= ',"link:%(tag(\'' . $tagField . '\'))"';
     }
-    $formatString .= "\n";
-    print CSV_OUT "\n";
-    my @csvRegLedgerOpts = ('-f', $tempFile, '-V', '-F', $formatString, '-w', '--sort', 'd',
-                            '-b', $beginDate, '-e', $endDate, 'reg');
-
+    $formatString .= $tagStrings . '\n%/"","","","%A","%t"' . $tagStrings . '\n';
 
-    open(TXT_DATA, "-|", $LEDGER_CMD, @txtRegLedgerOpts)
-      or die "unable to run ledger command for $fileNameBase.txt: $!";
+    # I thought '--sort', 'd', '--sort-xact', 'a',  should
+    # have worked below for a good sort.  Then I tried
+    # rather than '--sort', "d,n,a", which didn't work either.
+    #  I opened a bug: http://bugs.ledger-cli.org/show_bug.cgi?id=901
 
-    while (my $line = <TXT_DATA>) { print TEXT_OUT $line; }
-    close(TEXT_OUT); die "Error read write text out to $fileNameBase.txt: $!" unless $? == 0;
+    my @csvRegLedgerOpts = ('-f', $tempFile, '-V', '-F', $formatString, '-w', '--sort', 'd',
+                            '-b', $beginDate, '-e', $endDate, 'reg');
 
     open(CSV_DATA, "-|", $LEDGER_CMD, @csvRegLedgerOpts)
       or die "unable to run ledger command for $fileNameBase.csv: $!";
 
-    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;
+    my($curDepositDate, $curDepositTotal);
+
+    while (my $line = <CSV_DATA>) {
+      $line =~ s/"link:"/""/g;
+
+      my $date = $line;  chomp $date;
+      $date =~ s/^\s*"([^"]*)"\s*,.*$/$1/;
+      if (defined $date and $date !~ /^\s*$/ and
+          defined $curDepositDate and ($date ne $curDepositDate or
+          ($date eq $curDepositDate and $line !~ /DEPOSIT[\s\-]+BRANCH/))) {
+        print CSV_OUT "\"$curDepositDate\",\"SUBTOTAL\",\"BRANCH DEPOSIT TOTAL:\",\"\",\"\$$curDepositTotal\"\n\n";
+        $curDepositTotal = $curDepositDate = undef;
+      }
+      if ($line =~ /DEPOSIT[\s\-]+BRANCH/) {
+        if (not defined $curDepositDate) {
+          $curDepositDate = $line; chomp $curDepositDate;
+          $curDepositDate =~ s/^\s*"([^"]+)"\s*,.*$/$1/;
+        }
+      }
+      # This is a bit of a hack because I can't ssume that the line with the
+      # description on it has the account name in it.
+      if (defined $curDepositDate and $line =~ /$acct/) {
+        my $amt = $line;
+        chomp $amt;
+        $amt =~ s/^\s*"[^"]*","[^"]*","[^"]*","[^"]*","\$\s*([^"]*)".*$/$1/;
+        $amt =~ s/,//g;
+
+        $curDepositTotal = 0.0 unless defined $curDepositTotal;
+        $curDepositTotal += $amt;
+      }
+      print CSV_OUT $line;
+    }
+    # Catch potential last Deposit subtotal
+    print CSV_OUT "\n\"$curDepositDate\",\"SUBTOTAL\",\"BRANCH DEPOSIT TOTAL:\",\"\",\"\$$curDepositTotal\"\n\n"
+      if (defined $curDepositDate);
 
+    close(CSV_DATA); die "Error read from csv ledger command $!" unless $? == 0;
+    print CSV_OUT "pagebreak\n";
   SKIP_REGISTER_COMMANDS:
-    close(TXT_DATA); die "Error read from txt ledger command $!" unless $? == 0;
-    close(CSV_OUT); die "Error read write csv out to $fileNameBase.csv: $!" unless $? == 0;
     unlink($tempFile);
   }
+  close(CSV_OUT); die "Error read write csv out to $fileNameBase.csv: $!" unless $? == 0;
 }
 ###############################################################################
 #
diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py
index 8b880648..6aabcb59 100755
--- a/contrib/non-profit-audit-reports/csv2ods.py
+++ b/contrib/non-profit-audit-reports/csv2ods.py
@@ -23,16 +23,55 @@
 import sys, os, os.path, optparse
 import csv
 import ooolib2
+import shutil
+import string
+from Crypto.Hash import SHA256
 
 def err(msg):
     print 'error: %s' % msg
     sys.exit(1)
 
-def csv2ods(csvname, odsname, encoding='', verbose = False):
+def ReadChecksums(inputFile):
+    checksums = {}
+    with open(inputFile, "r") as inputFH:
+        entries = inputFH.readlines()
+    for ee in entries:
+        fileName, checksum = ee.split(":")
+        fileName = fileName.replace(' ', "")
+        checksum = checksum.replace(' ', "")
+        checksum = checksum.replace("\n", "")
+        checksums[checksum] = fileName
+    return checksums
+
+def ChecksumFile(filename):
+    sha256 = SHA256.new()
+    chunk_size = 8192
+    with open(filename, 'rb') as myFile:
+        while True:
+            chunk = myFile.read(chunk_size)
+            if len(chunk) == 0:
+                break
+            sha256.update(chunk)
+    return sha256.hexdigest()
+
+def main():
+    program = os.path.basename(sys.argv[0])
+
+    print get_file_checksum(sys.argv[1])
+
+def csv2ods(csvname, odsname, encoding='', singleFileDirectory=None, knownChecksums={}, verbose = False):
     filesSavedinManifest = {}
 
+    if knownChecksums:
+        checksumCache = {}
+
     if verbose:
         print 'converting from %s to %s' % (csvname, odsname)
+
+    if singleFileDirectory:
+        if not os.path.isdir(os.path.join(os.getcwd(),singleFileDirectory)):
+            os.mkdir(singleFileDirectory)
+
     doc = ooolib2.Calc()
     #  add a pagebreak style
     style = 'pagebreak'
@@ -55,20 +94,71 @@ def csv2ods(csvname, odsname, encoding='', verbose = False):
         if len(fields) > 0:
             for col in range(len(fields)):
                 val = fields[col]
-                if encoding != '':
+                if encoding != '' and val[0:5] != "link:":  # Only utf8 encode if it's not a filename
                     val = unicode(val, 'utf8')
                 if len(val) > 0 and val[0] == '$':
                     doc.set_cell_value(col + 1, row, 'currency', val[1:])
                 else:
                     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
+                        newFile = None
+
+                        if not singleFileDirectory:
+                            newFile = val
+
+                        if knownChecksums:
+                            if not checksumCache.has_key(val):
+                                checksum = ChecksumFile(val)
+                                checksumCache[val] = checksum
+                            else:
+                                checksum = checksumCache[val]
+
+                            if knownChecksums.has_key(checksum):
+                                newFile = knownChecksums[checksum]
+                                print "FOUND new file in known: " + newFile
+
+                        if not newFile:
+                            relativeFileWithPath = os.path.basename(val)
+
+                            fileName, fileExtension = os.path.splitext(relativeFileWithPath)
+                            newFile = fileName[:15]   # 15 is an arbitrary choice.
+                            newFile = newFile + fileExtension
+                            # We'll now test to see if we made this file
+                            # before, and if it matched the same file we
+                            # now want.  If it doesn't, try to make a
+                            # short file name for it.
+                            if filesSavedinManifest.has_key(newFile) and filesSavedinManifest[newFile] != val:
+                                testFile = None
+                                for cc in list(string.letters) + list(string.digits):
+                                    testFile = cc + newFile
+                                    if not filesSavedinManifest.has_key(testFile):
+                                        break
+                                    testFile = None
+                                    if not testFile:
+                                        raise Exception("too many similar file names for linkage; giving up")
+                                    else:
+                                        newFile = testFile
+                                        if not os.path.exists(csvdir + '/' + val):
+                                            raise Exception("File" + csvdir + '/' + val + " does not exist in single file directory mode; giving up")
+                            src = os.path.join(csvdir, val)
+                            dest = os.path.join(csvdir, singleFileDirectory, newFile)
+                            shutil.copyfile(src, dest)
+                            shutil.copystat(src, dest)
+                            shutil.copymode(src, dest)
+
+                            newFile = os.path.join(singleFileDirectory, newFile)
+
+                        if knownChecksums:
+                            checksumCache[checksum]   = newFile
+                            knownChecksums[checksum]  = newFile
+
+                        linkrel = '../' + newFile # ../ means remove the name of the *.ods
                         doc.set_cell_value(col + 1, row, 'link', (linkrel, linkname))
                         linkpath = csvdir + '/' + val
 
                         if not val in filesSavedinManifest:
-                            filesSavedinManifest[val] = col
+                            filesSavedinManifest[newFile] = val
 
                         if not os.path.exists(linkpath):
                             print "WARNING: link %s DOES NOT EXIST at %s" % (val, linkpath)
@@ -79,7 +169,10 @@ def csv2ods(csvname, odsname, encoding='', verbose = False):
                         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)
+                            if val[0:6] == "title:":
+                                doc.sheets[doc.sheet_index].set_name(val[6:])
+                            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', '')
@@ -109,7 +202,12 @@ def main():
                       help='ods output filename')
     parser.add_option('-e', '--encoding', action='store', 
                       help='unicode character encoding type')
+    parser.add_option('-d', '--single-file-directory', action='store',
+                      help='directory name to move all files into')
+    parser.add_option('-s', '--known-checksum-list', action='store',
+                      help='directory name to move all files into')
     (options, args) = parser.parse_args()
+
     if len(args) != 0:
         parser.error("not expecting extra args")  
     if not os.path.exists(options.csv):
@@ -122,7 +220,14 @@ def main():
         print 'csv:', options.csv
         print 'ods:', options.ods
         print 'ods:', options.encoding
-    csv2ods(options.csv, options.ods, options.encoding, options.verbose)
+    if options.known_checksum_list and not options.single_file_directory:
+        err(program + ": --known-checksum-list option is completely useless without --single-file-directory")
+    knownChecksums = {}
+    if options.known_checksum_list:
+        if not os.access(options.known_checksum_list, os.R_OK):
+            err(program + ": unable to read file: " + options.known_checksum_list)
+        knownChecksums = ReadChecksums(options.known_checksum_list)
+    csv2ods(options.csv, options.ods, options.encoding, options.single_file_directory, knownChecksums, 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 1fd0e7ce..1e6d9caf 100755
--- a/contrib/non-profit-audit-reports/general-ledger-report.plx
+++ b/contrib/non-profit-audit-reports/general-ledger-report.plx
@@ -166,7 +166,7 @@ foreach my $acct (@sortedAccounts) {
   print GL_TEXT_OUT "\n\nACCOUNT: $acct\nFROM:    $beginDate TO $formattedEndDate\n\n";
   my @acctLedgerOpts = ('-V', '-F',
                         "%(date)  %-.10C   %-.80P  %-.80N  %18t  %18T\n", '-w', '--sort', 'd',
-                        '-b', $beginDate, '-e', $endDate, @otherLedgerOpts, 'reg', $acct);
+                        '-b', $beginDate, '-e', $endDate, @otherLedgerOpts, 'reg', '/^' . $acct . '$/');
   open(GL_TEXT_DATA, "-|", $LEDGER_CMD, @acctLedgerOpts)
     or die "Unable to run $LEDGER_CMD @acctLedgerOpts: $!";
 
@@ -190,7 +190,7 @@ foreach my $acct (@sortedAccounts) {
     print GL_CSV_OUT "\"$formattedBeginDate\"", ',"","BALANCE","","$', "$balanceData{totalBegin}{$acct}\"\n";
   }
 
-  @acctLedgerOpts = ('-V', '-F', $formatString, '-w', '--sort', 'd', '-b', $beginDate, '-e', $endDate, @otherLedgerOpts, 'reg', $acct);
+  @acctLedgerOpts = ('-V', '-F', $formatString, '-w', '--sort', 'd', '-b', $beginDate, '-e', $endDate, @otherLedgerOpts, 'reg', '/^' . $acct . '$/');
   open(GL_CSV_DATA, "-|", $LEDGER_CMD, @acctLedgerOpts)
     or die "Unable to run $LEDGER_CMD @acctLedgerOpts: $!";
 
diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx
index e9e1a3b8..78848dc5 100755
--- a/contrib/non-profit-audit-reports/summary-reports.plx
+++ b/contrib/non-profit-audit-reports/summary-reports.plx
@@ -104,9 +104,9 @@ my %reportFields =
    'Liabilities, Other' => {args => [ '-e', $endDate, 'bal', '/^Liabilities/',
                                     'and', 'not', '/^Liabilities:Credit Card/']},
    'Unearned Income, Conference Registration' => {args => [ '-e', $endDate, 'bal',
-                                                   '/^Unearned Income.*Conf.*Reg/' ]},
+                                                   '/^Unearned Income.*Reg/' ]},
    'Unearned Income, Other' => {args => [ '-e', $endDate, 'bal', '/^Unearned Income/', 'and', 'not',
-                                                   '/^Unearned Income.*Conf.*Reg/' ]},
+                                                   '/^Unearned Income.*Reg/' ]},
    'Unrestricted Net Assets' => {args => [ '-e', $endDate, 'bal', '/^(Income|Expenses):Conservancy/' ]},
    'Temporarily Restricted Net Assets' =>  {args => [ '-e', $endDate, 'bal', '/^(Income|Expenses)/',
                               'and', 'not', '/^(Unearned Income|(Income|Expenses):Conservancy)/' ]},
@@ -213,8 +213,8 @@ my %incomeGroups = ('INTEREST INCOME' => { args => ['/^Income.*Interest/' ] },
                     'DONATIONS' => { args => [ '/^Income.*Donation/' ] },
                     'BOOK ROYALTIES & AFFILIATE PROGRAMS' =>
                     { args => [ '/^Income.*(Royalt|Affilate)/' ] },
-                    'CONFERENCES, REGISTRATION' => {args => [ '/^Income.*Conf.*Reg/' ] },
-                    'CONFERENCES, RELATED BUSINESS INCOME' => { args => [ '/^Income.*(Booth|RBI)/'] },
+                    'CONFERENCES, REGISTRATION' => {args => [ '/^Income.*Reg/' ] },
+                    'CONFERENCES, RELATED BUSINESS INCOME' => { args => [ '/^Income.*(Conferences?:.*Sponsor|Booth|RBI)/'] },
                     'LICENSE ENFORCEMENT' => { args => [ '/^Income.*Enforce/' ]},
                     'TRADEMARKS' => {args => [ '/^Income.*Trademark/' ]},
                     'ADVERSITING' => {args => [ '/^Income.*Advertising/' ]});
@@ -386,7 +386,7 @@ print STDERR "\n";
 open(TRIAL, ">", "trial-balance.csv") or die "unable to open accrued.txt for writing: $!";
 
 print TRIAL "\"TRIAL BALANCE REPORT\",\"ENDING: $formattedEndDate\"\n\n",
-             "\"ACCOUNT\",\"BALANCE AT $formattedStartDate\",\"CHANGE DURING FY\",\"BALANCE AT $formattedEndDate\"\n\n";
+             "\"ACCOUNT\",\"BALANCE AT $formattedStartDate\",\"CHANGE DURING PERIOD\",\"BALANCE AT $formattedEndDate\"\n\n";
 
 my %commands = (
                 'totalEndFY' => [ $LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$',