From 34a6279baa3c836c1850f335c1c7ec3089fb2532 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Mon, 7 Jan 2013 16:38:53 -0500
Subject: Add --single-file-directory option: create single directory with all
 linked files.

This new option copies all files to the directory specified as an argument
to the --single-file-directory option, and also creates dummy shorter
filenames for the files.

This feature was implemented to get around a problem found when zip'ing
the spreadsheet up with the supporting files for users on Windows.  The
Windows users encounter the error 0x80010135 related to some of the ZIP
files going beyond the maximum path name length on windows.  Apparently,
opening ZIP files with long path names just doesn't work on Microsoft
systems.  I've suggested our accountants switch to a Free Software
operating system, but they declined.
---
 contrib/non-profit-audit-reports/csv2ods.py | 51 ++++++++++++++++++++++++++---
 1 file changed, 46 insertions(+), 5 deletions(-)

(limited to 'contrib/non-profit-audit-reports')

diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py
index 8b880648..3a3411ba 100755
--- a/contrib/non-profit-audit-reports/csv2ods.py
+++ b/contrib/non-profit-audit-reports/csv2ods.py
@@ -23,16 +23,23 @@
 import sys, os, os.path, optparse
 import csv
 import ooolib2
+import shutil
+import string
 
 def err(msg):
     print 'error: %s' % msg
     sys.exit(1)
 
-def csv2ods(csvname, odsname, encoding='', verbose = False):
+def csv2ods(csvname, odsname, encoding='', singleFileDirectory=None, verbose = False):
     filesSavedinManifest = {}
 
     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 +62,51 @@ 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
+                        if not singleFileDirectory:
+                            newFile = val
+                        else:
+                            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)
+
+                        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)
@@ -109,7 +147,10 @@ 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')
     (options, args) = parser.parse_args()
+
     if len(args) != 0:
         parser.error("not expecting extra args")  
     if not os.path.exists(options.csv):
@@ -122,7 +163,7 @@ def main():
         print 'csv:', options.csv
         print 'ods:', options.ods
         print 'ods:', options.encoding
-    csv2ods(options.csv, options.ods, options.encoding, options.verbose)
+    csv2ods(options.csv, options.ods, options.encoding, options.single_file_directory, options.verbose)
 
 if __name__ == '__main__':
   main()
-- 
cgit v1.2.3


From b214a2db5b64b52938aecc60711a0237fc95b575 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Tue, 8 Jan 2013 14:01:58 -0500
Subject: Began work on script to reconcile bank accounts.

The goal here is to take as input an account, a monthly balance amount
that appears on a bank statement, and the date of that bank statement and
output the list of transactions that likely weren't cleared properly as of
that date that caused the balance in the accounts to fail to match the
balance that appeared on the statement.

Note that determining this answer requires solving the known NP-Complete
problem called the subset sum problem.  There is a known pseudo-polynomial
dynamic programming solution to this problem, but it's still exponential
in the size of the numbers you have to balance.

So, if you have *big* account balances, this will make take quite a while
to run.  For smaller accounts, the pseudo-polynomial solution might be
helpful.  (BTW, the wikipedia entry on the subset sum problem isn't, at
the time of this commit, particularly good, but it's "good enough" to give
you a sense of what the subset sum problem is:
    http://en.wikipedia.org/wiki/Subset_sum_problem

)

I originally wrote the subset sum problem solution implementation here:
  https://gitorious.org/bkuhn/small-hacks/commit/2dca069d810b61cdfad46e00abcb1a3edaf56d1b

The code is just cut and pasted in here with some minor modifications.

This rest of this first commit just has that aforementioned paste, plus the
beginnings of the CLI and query to run to get the proper entries.
---
 .../bank-reconcilation.plx                         | 122 +++++++++++++++++++++
 1 file changed, 122 insertions(+)
 create mode 100755 contrib/non-profit-audit-reports/bank-reconcilation.plx

(limited to 'contrib/non-profit-audit-reports')

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..7201ef1f
--- /dev/null
+++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx
@@ -0,0 +1,122 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+
+use Math::BigFloat;
+use Date::Manip;
+
+Math::BigFloat->precision(-2);
+my $ZERO =  Math::BigFloat->new("0.00");
+
+my $VERBOSE = 0;
+my $DEBUG = 0;
+
+my $LEDGER_BIN = "/usr/local/bin/ledger";
+
+######################################################################
+sub SubSetSumSolver ($$$) {
+  my($numberList, $totalSought, $extractNumber) = @_;
+
+  my($P, $N) = (0, 0);
+  foreach my $ii (@{$numberList}) {
+    if ($ii < $ZERO) {
+      $N += $ii;
+    } else {
+      $P += $ii;
+    }
+  }
+  my $size = scalar(@{$numberList});
+  my %Q;
+  my(@L) =
+     map { { val => &$extractNumber($_), obj => $_ } } @{$numberList};
+
+  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]);
+}
+if (@ARGV < 4) {
+  print STDERR "usage: $0 <ACCOUNT_REGEX> <END_DATE> <BANK_STATEMENT_BALANCE> <LEDGER_OPTIONS>\n";
+  exit 1;
+}
+######################################################################
+my($account, $endDate, $balanceSought, @mainLedgerOptions) = @ARGV;
+
+$balanceSought = ParseNumber($balanceSought);
+
+my $err;
+my $startDate = UnixDate(DateCalc(ParseDate($endDate), ParseDateDelta("- 1 month"), \$err), "%Y/%m/%d");
+die "Date calculation error on $endDate" if ($err);
+
+my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$',
+                    '-b', $startDate, '-e', $endDate,
+                    '-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);
+
+  print "$date, $checkNum, $payee, $amount\n";
+  push(@entries, { date => $date, checkNum => $checkNum, amount => $amount });
+}
+
+###############################################################################
+#
+# Local variables:
+# compile-command: "perl -c bank-reconcilation.plx"
+# End:
-- 
cgit v1.2.3


From 6962fc4c57c5709cb106bd544df3cdb338c7495a Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Tue, 8 Jan 2013 14:26:04 -0500
Subject: Basic implementation probably correct, but needs much RAM.

This is the basic implementation but for large numbers, it needs a *LOT*
of RAM.
---
 .../bank-reconcilation.plx                         | 45 +++++++++++++++++-----
 1 file changed, 36 insertions(+), 9 deletions(-)

(limited to 'contrib/non-profit-audit-reports')

diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx
index 7201ef1f..b09519a3 100755
--- a/contrib/non-profit-audit-reports/bank-reconcilation.plx
+++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx
@@ -8,8 +8,9 @@ use Date::Manip;
 
 Math::BigFloat->precision(-2);
 my $ZERO =  Math::BigFloat->new("0.00");
+my $ONE_HUNDRED =  Math::BigFloat->new("100.00");
 
-my $VERBOSE = 0;
+my $VERBOSE = 1;
 my $DEBUG = 0;
 
 my $LEDGER_BIN = "/usr/local/bin/ledger";
@@ -19,18 +20,27 @@ sub SubSetSumSolver ($$$) {
   my($numberList, $totalSought, $extractNumber) = @_;
 
   my($P, $N) = (0, 0);
-  foreach my $ii (@{$numberList}) {
-    if ($ii < $ZERO) {
-      $N += $ii;
-    } else {
-      $P += $ii;
-    }
-  }
   my $size = scalar(@{$numberList});
   my %Q;
   my(@L) =
      map { { val => &$extractNumber($_), obj => $_ } } @{$numberList};
 
+
+  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} = [];
@@ -85,6 +95,14 @@ if (@ARGV < 4) {
   exit 1;
 }
 ######################################################################
+sub ConvertTwoDigitPrecisionToInteger ($) {
+  return sprintf("%d", $_[0] * $ONE_HUNDRED);
+}
+######################################################################
+sub ConvertTwoDigitPrecisionToIntegerInEntry ($) {
+  return ConvertTwoDigitPrecisionToInteger($_[0]->{amount});
+}
+######################################################################
 my($account, $endDate, $balanceSought, @mainLedgerOptions) = @ARGV;
 
 $balanceSought = ParseNumber($balanceSought);
@@ -111,9 +129,18 @@ foreach my $line (<FILE>) {
     unless $amount =~ s/\s*\$\s*([\-\d\.\,]+)\s*$/$1/;
   $amount = ParseNumber($amount);
 
-  print "$date, $checkNum, $payee, $amount\n";
   push(@entries, { date => $date, checkNum => $checkNum, amount => $amount });
 }
+close FILE;
+die "unable to properly run ledger command: @fullCommand: $!" unless ($? == 0);
+
+my(@solution) = SubSetSumSolver(\@entries, ConvertTwoDigitPrecisionToInteger($balanceSought),
+                                \&ConvertTwoDigitPrecisionToIntegerInEntry);
+
+if ($VERBOSE) {
+  use Data::Dumper;
+  print Data::Dumper->Dump(\@solution);
+}
 
 ###############################################################################
 #
-- 
cgit v1.2.3


From b1b807fcfab49b9682db79b6b2fed000fc230f90 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Tue, 8 Jan 2013 16:37:41 -0500
Subject: Loop through to build smaller sets when testing.

Usually, transactions that didn't appear are nearby in date to the statement
date.  This loop cycles through.  Overall, this would take longer to find a
solution, but since most solutions are in the early dates "back" from the
statement date, this will probably be faster in typical cases.
---
 .../bank-reconcilation.plx                         | 81 ++++++++++++++--------
 1 file changed, 52 insertions(+), 29 deletions(-)

(limited to 'contrib/non-profit-audit-reports')

diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx
index b09519a3..f18f43dc 100755
--- a/contrib/non-profit-audit-reports/bank-reconcilation.plx
+++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx
@@ -25,11 +25,8 @@ sub SubSetSumSolver ($$$) {
   my(@L) =
      map { { val => &$extractNumber($_), obj => $_ } } @{$numberList};
 
-
-  if ($VERBOSE) {
-    }
-  }
-    print STDERR "  L in this iteration:\n     [" if $VERBOSE;
+  print STDERR "  TotalSought:", $totalSought if $VERBOSE;
+  print STDERR "  L in this iteration:\n     [" if $VERBOSE;
 
   foreach my $ee (@L) {
     if ($ee->{val} < 0) {
@@ -108,40 +105,66 @@ my($account, $endDate, $balanceSought, @mainLedgerOptions) = @ARGV;
 $balanceSought = ParseNumber($balanceSought);
 
 my $err;
-my $startDate = UnixDate(DateCalc(ParseDate($endDate), ParseDateDelta("- 1 month"), \$err), "%Y/%m/%d");
+my $earliestStartDate = DateCalc(ParseDate($endDate), ParseDateDelta("- 1 month"), \$err);
+
 die "Date calculation error on $endDate" if ($err);
 
-my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$',
-                    '-b', $startDate, '-e', $endDate,
-                    '-F', '"%(date)","%C","%P","%t"\n',
-                    'reg', "/$account/");
+my $startDate = ParseDate($endDate);
+
+my @solution;
+while ($startDate ge $earliestStartDate) {
+  print "START LOOP ITR: $startDate $earliestStartDate\n";
+  $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 $endDate: \n" if $VERBOSE;
+
+  my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$',
+                      '-b', $formattedStartDate, '-e', $endDate,
+                      '-F', '"%(date)","%C","%P","%t"\n',
+                      'reg', "/$account/");
 
   open(FILE, "-|", @fullCommand)
     or die "unable to run command ledger command: @fullCommand: $!";
 
-my @entries;
+  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);
+  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, amount => $amount });
-}
-close FILE;
-die "unable to properly run ledger command: @fullCommand: $!" unless ($? == 0);
-
-my(@solution) = SubSetSumSolver(\@entries, ConvertTwoDigitPrecisionToInteger($balanceSought),
+    push(@entries, { date => $date, checkNum => $checkNum, amount => $amount });
+  }
+  close FILE;
+  die "unable to properly run ledger command: @fullCommand: $!" unless ($? == 0);
+
+  @solution = ();
+  if (@entries == 1) {
+    @solution = ( (abs($entries[0]->{amount}) == abs($balanceSought)), \@entries);
+  } else {
+    @solution = SubSetSumSolver(\@entries, ConvertTwoDigitPrecisionToInteger($balanceSought),
                                 \&ConvertTwoDigitPrecisionToIntegerInEntry);
-
-if ($VERBOSE) {
-  use Data::Dumper;
-  print Data::Dumper->Dump(\@solution);
+  }
+  if ($VERBOSE) {
+    use Data::Dumper;
+    print STDERR "Solution for $formattedStartDate, $balanceSought: \n", Data::Dumper->Dump(\@solution);
+  }
+  print STDERR "Solution Found: Dying" if ($solution[0]) and $VERBOSE;
+#  last if ($solution[0]);
+}
+print "DONE LOOP: $startDate $earliestStartDate\n";
+if ($solution[0]) {
+  print "FINAL SOLUTION: ";
+  foreach my $ee (@{$solution[1]}) {
+    print "$ee->date, $ee->payee, $ee->amount\n";
+  }
 }
-
 ###############################################################################
 #
 # Local variables:
-- 
cgit v1.2.3


From 18d2867a6315562b4f4588ebf4fc58adf1fb9acf Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Tue, 8 Jan 2013 16:38:24 -0500
Subject: Rename the function to note it's the dynamic programming one.

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

(limited to 'contrib/non-profit-audit-reports')

diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx
index f18f43dc..669a25b0 100755
--- a/contrib/non-profit-audit-reports/bank-reconcilation.plx
+++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx
@@ -16,7 +16,7 @@ my $DEBUG = 0;
 my $LEDGER_BIN = "/usr/local/bin/ledger";
 
 ######################################################################
-sub SubSetSumSolver ($$$) {
+sub DynamicProgrammingSubSetSumSolver ($$$) {
   my($numberList, $totalSought, $extractNumber) = @_;
 
   my($P, $N) = (0, 0);
@@ -148,7 +148,7 @@ while ($startDate ge $earliestStartDate) {
   if (@entries == 1) {
     @solution = ( (abs($entries[0]->{amount}) == abs($balanceSought)), \@entries);
   } else {
-    @solution = SubSetSumSolver(\@entries, ConvertTwoDigitPrecisionToInteger($balanceSought),
+    @solution = DynamicProgrammingSubSetSumSolver(\@entries, ConvertTwoDigitPrecisionToInteger($balanceSought),
                                 \&ConvertTwoDigitPrecisionToIntegerInEntry);
   }
   if ($VERBOSE) {
-- 
cgit v1.2.3


From d13ab6a4026cfeec18fdd989862aecbe83caa20f Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Wed, 9 Jan 2013 14:51:33 -0500
Subject: Support for a list of known checksums of files already copied over.

For the times when we want to make shorter names of files by doing copies of
the documentation files for hyperlink usage, allow input of a new command
line option which is a list in the form of:

  PATH_TO_FILE : sha25sum

so that those files can be used rather than new copies made.
---
 contrib/non-profit-audit-reports/csv2ods.py | 79 +++++++++++++++++++++++++----
 1 file changed, 70 insertions(+), 9 deletions(-)

(limited to 'contrib/non-profit-audit-reports')

diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py
index 3a3411ba..7dd840c8 100755
--- a/contrib/non-profit-audit-reports/csv2ods.py
+++ b/contrib/non-profit-audit-reports/csv2ods.py
@@ -25,14 +25,46 @@ 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='', singleFileDirectory=None, 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)
 
@@ -70,10 +102,25 @@ def csv2ods(csvname, odsname, encoding='', singleFileDirectory=None, verbose = F
                     if (len(val) > 0 and val[0:5] == "link:"):
                         val = val[5:]
                         linkname = os.path.basename(val) # name is just the last component
+                        newFile = None
+
                         if not singleFileDirectory:
                             newFile = val
-                        else:
+
+                        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
@@ -88,19 +135,24 @@ def csv2ods(csvname, odsname, encoding='', singleFileDirectory=None, verbose = F
                                     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")
+                                    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
@@ -149,6 +201,8 @@ def main():
                       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:
@@ -163,7 +217,14 @@ def main():
         print 'csv:', options.csv
         print 'ods:', options.ods
         print 'ods:', options.encoding
-    csv2ods(options.csv, options.ods, options.encoding, options.single_file_directory, 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()
-- 
cgit v1.2.3


From 0530b729e2b38931f653e226bc3c1cfc47d55d24 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Wed, 9 Jan 2013 15:20:50 -0500
Subject: Default to brute-force subset sum solution.

The dynamic programming version of the subset sum problem required far too
much RAM for larger bank balances.  Meanwhile, the brute-force is not to bad
now that the loop tries the closer dates *first*.
---
 .../bank-reconcilation.plx                         | 52 ++++++++++++++++++----
 1 file changed, 44 insertions(+), 8 deletions(-)

(limited to 'contrib/non-profit-audit-reports')

diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx
index 669a25b0..18d74067 100755
--- a/contrib/non-profit-audit-reports/bank-reconcilation.plx
+++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx
@@ -5,6 +5,7 @@ use warnings;
 
 use Math::BigFloat;
 use Date::Manip;
+use Data::PowerSet;
 
 Math::BigFloat->precision(-2);
 my $ZERO =  Math::BigFloat->new("0.00");
@@ -15,6 +16,30 @@ 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) = @_;
@@ -88,7 +113,7 @@ sub ParseNumber($) {
   return Math::BigFloat->new($_[0]);
 }
 if (@ARGV < 4) {
-  print STDERR "usage: $0 <ACCOUNT_REGEX> <END_DATE> <BANK_STATEMENT_BALANCE> <LEDGER_OPTIONS>\n";
+  print STDERR "usage: $0 [-d] <ACCOUNT_REGEX> <END_DATE> <BANK_STATEMENT_BALANCE> <LEDGER_OPTIONS>\n";
   exit 1;
 }
 ######################################################################
@@ -100,8 +125,18 @@ sub ConvertTwoDigitPrecisionToIntegerInEntry ($) {
   return ConvertTwoDigitPrecisionToInteger($_[0]->{amount});
 }
 ######################################################################
+my $firstArg = shift @ARGV;
+
+my $solver = \&BruteForceSubSetSumSolver;
+
+if ($firstArg eq '-d') {
+  $solver = \&DynamicProgrammingSubSetSumSolver;
+} else {
+  unshift(@ARGV, $firstArg);
+}
 my($account, $endDate, $balanceSought, @mainLedgerOptions) = @ARGV;
 
+
 $balanceSought = ParseNumber($balanceSought);
 
 my $err;
@@ -139,7 +174,8 @@ while ($startDate ge $earliestStartDate) {
       unless $amount =~ s/\s*\$\s*([\-\d\.\,]+)\s*$/$1/;
     $amount = ParseNumber($amount);
 
-    push(@entries, { date => $date, checkNum => $checkNum, amount => $amount });
+    push(@entries, { date => $date, checkNum => $checkNum, 
+                     payee => $payee, amount => $amount });
   }
   close FILE;
   die "unable to properly run ledger command: @fullCommand: $!" unless ($? == 0);
@@ -148,21 +184,21 @@ while ($startDate ge $earliestStartDate) {
   if (@entries == 1) {
     @solution = ( (abs($entries[0]->{amount}) == abs($balanceSought)), \@entries);
   } else {
-    @solution = DynamicProgrammingSubSetSumSolver(\@entries, ConvertTwoDigitPrecisionToInteger($balanceSought),
-                                \&ConvertTwoDigitPrecisionToIntegerInEntry);
+    @solution = $solver->(\@entries,
+                          ConvertTwoDigitPrecisionToInteger($balanceSought),
+                          \&ConvertTwoDigitPrecisionToIntegerInEntry);
   }
   if ($VERBOSE) {
     use Data::Dumper;
     print STDERR "Solution for $formattedStartDate, $balanceSought: \n", Data::Dumper->Dump(\@solution);
   }
-  print STDERR "Solution Found: Dying" if ($solution[0]) and $VERBOSE;
-#  last if ($solution[0]);
+  last if ($solution[0]);
 }
-print "DONE LOOP: $startDate $earliestStartDate\n";
 if ($solution[0]) {
   print "FINAL SOLUTION: ";
   foreach my $ee (@{$solution[1]}) {
-    print "$ee->date, $ee->payee, $ee->amount\n";
+    print Data::Dumper->Dump($solution[1]);
+    print "$ee->{date}, $ee->{payee}, $ee->{amount}\n";
   }
 }
 ###############################################################################
-- 
cgit v1.2.3


From 94094ce3650e26e584b68f3c0d94d593732dc3bc Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Wed, 9 Jan 2013 16:44:54 -0500
Subject: Finish reporting details for STDOUT; change command line arg to bank
 balance.

Report in CSV now goes to STDOUT.

The command line argument that was the difference to seek is now the bank
balance.
---
 .../bank-reconcilation.plx                         | 60 ++++++++++++++--------
 1 file changed, 40 insertions(+), 20 deletions(-)

(limited to 'contrib/non-profit-audit-reports')

diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx
index 18d74067..ada923f3 100755
--- a/contrib/non-profit-audit-reports/bank-reconcilation.plx
+++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx
@@ -112,10 +112,6 @@ sub ParseNumber($) {
   $_[0] =~ s/,//g;
   return Math::BigFloat->new($_[0]);
 }
-if (@ARGV < 4) {
-  print STDERR "usage: $0 [-d] <ACCOUNT_REGEX> <END_DATE> <BANK_STATEMENT_BALANCE> <LEDGER_OPTIONS>\n";
-  exit 1;
-}
 ######################################################################
 sub ConvertTwoDigitPrecisionToInteger ($) {
   return sprintf("%d", $_[0] * $ONE_HUNDRED);
@@ -129,17 +125,43 @@ my $firstArg = shift @ARGV;
 
 my $solver = \&BruteForceSubSetSumSolver;
 
+if (@ARGV < 5) {
+  print STDERR "usage: $0 [-d] <TITLE> <ACCOUNT_REGEX> <END_DATE> <BANK_STATEMENT_BALANCE> <LEDGER_OPTIONS>\n";
+  exit 1;
+}
 if ($firstArg eq '-d') {
   $solver = \&DynamicProgrammingSubSetSumSolver;
 } else {
   unshift(@ARGV, $firstArg);
 }
-my($account, $endDate, $balanceSought, @mainLedgerOptions) = @ARGV;
+my($title, $account, $endDate, $bankBalance, @mainLedgerOptions) = @ARGV;
+
+$bankBalance = ParseNumber($bankBalance);
 
+my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$',
+                    '-e', $endDate, '-F', '%t\n', 'bal', "/$account/");
 
-$balanceSought = ParseNumber($balanceSought);
+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);
@@ -148,11 +170,11 @@ my $startDate = ParseDate($endDate);
 
 my @solution;
 while ($startDate ge $earliestStartDate) {
-  print "START LOOP ITR: $startDate $earliestStartDate\n";
+  print STDERR "START LOOP ITR: $startDate $earliestStartDate\n" if ($VERBOSE);
   $startDate = DateCalc(ParseDate($startDate), ParseDateDelta("- 1 day"), \$err);
   die "Date calculation error on $endDate" if ($err);
 
-  my $formattedStartDate = UnixDate($startDate, "%Y/%m/%d");
+  my $formattedStartDate = UnixDate($startDate, "%Y-%m-%d");
 
   print STDERR "Testing $formattedStartDate through $endDate: \n" if $VERBOSE;
 
@@ -180,26 +202,24 @@ while ($startDate ge $earliestStartDate) {
   close FILE;
   die "unable to properly run ledger command: @fullCommand: $!" unless ($? == 0);
 
-  @solution = ();
-  if (@entries == 1) {
-    @solution = ( (abs($entries[0]->{amount}) == abs($balanceSought)), \@entries);
-  } else {
-    @solution = $solver->(\@entries,
-                          ConvertTwoDigitPrecisionToInteger($balanceSought),
-                          \&ConvertTwoDigitPrecisionToIntegerInEntry);
-  }
+  @solution = $solver->(\@entries,
+                        ConvertTwoDigitPrecisionToInteger($differenceSought),
+                        \&ConvertTwoDigitPrecisionToIntegerInEntry);
   if ($VERBOSE) {
     use Data::Dumper;
-    print STDERR "Solution for $formattedStartDate, $balanceSought: \n", Data::Dumper->Dump(\@solution);
+    print STDERR "Solution for $formattedStartDate to $formattedEndDate, $differenceSought: \n",
+                 Data::Dumper->Dump(\@solution);
   }
   last if ($solution[0]);
 }
 if ($solution[0]) {
-  print "FINAL SOLUTION: ";
+  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 (@{$solution[1]}) {
-    print Data::Dumper->Dump($solution[1]);
-    print "$ee->{date}, $ee->{payee}, $ee->{amount}\n";
+    print "\"$ee->{date}\",\"$ee->{checkNum}\",\"$ee->{payee}\",\"$ee->{amount}\"\n";
   }
+    print "\n\"$formattedEndDate\",\"\",\"OUR ACCOUNT BALANCE\",\"$total\"\n\n";
 }
 ###############################################################################
 #
-- 
cgit v1.2.3


From 8ebb54638ca8a57b15126cb11fd6329faf639be5 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Thu, 10 Jan 2013 11:25:19 -0500
Subject: Start search from date: easy way to resume searches.

Instead of always starting a search from the end date, allow a CLI option
that is the data to use for the start of searching (back from the end date).
This is useful when resuming a search (since they take a long time).
---
 contrib/non-profit-audit-reports/bank-reconcilation.plx | 10 +++++-----
 1 file changed, 5 insertions(+), 5 deletions(-)

(limited to 'contrib/non-profit-audit-reports')

diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx
index ada923f3..701f053e 100755
--- a/contrib/non-profit-audit-reports/bank-reconcilation.plx
+++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx
@@ -125,8 +125,8 @@ my $firstArg = shift @ARGV;
 
 my $solver = \&BruteForceSubSetSumSolver;
 
-if (@ARGV < 5) {
-  print STDERR "usage: $0 [-d] <TITLE> <ACCOUNT_REGEX> <END_DATE> <BANK_STATEMENT_BALANCE> <LEDGER_OPTIONS>\n";
+if (@ARGV < 6) {
+  print STDERR "usage: $0 [-d] <TITLE> <ACCOUNT_REGEX> <END_DATE> <START_SEARCH_FROM_DATE> <BANK_STATEMENT_BALANCE> <LEDGER_OPTIONS>\n";
   exit 1;
 }
 if ($firstArg eq '-d') {
@@ -134,7 +134,7 @@ if ($firstArg eq '-d') {
 } else {
   unshift(@ARGV, $firstArg);
 }
-my($title, $account, $endDate, $bankBalance, @mainLedgerOptions) = @ARGV;
+my($title, $account, $endDate, $startSearchFromDate, $bankBalance, @mainLedgerOptions) = @ARGV;
 
 $bankBalance = ParseNumber($bankBalance);
 
@@ -166,7 +166,7 @@ my $earliestStartDate = DateCalc(ParseDate($endDate), ParseDateDelta("- 1 month"
 
 die "Date calculation error on $endDate" if ($err);
 
-my $startDate = ParseDate($endDate);
+my $startDate = ParseDate($startSearchFromDate);
 
 my @solution;
 while ($startDate ge $earliestStartDate) {
@@ -179,7 +179,7 @@ while ($startDate ge $earliestStartDate) {
   print STDERR "Testing $formattedStartDate through $endDate: \n" if $VERBOSE;
 
   my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$',
-                      '-b', $formattedStartDate, '-e', $endDate,
+                      '-b', $formattedStartDate, '-e', $startSearchFromDate,
                       '-F', '"%(date)","%C","%P","%t"\n',
                       'reg', "/$account/");
 
-- 
cgit v1.2.3


From 9fcdfc893c41447099169bf4ad51f5856db399a1 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Thu, 10 Jan 2013 11:26:14 -0500
Subject: Support for title: field, to name sheets in the ODS file.

If "title:SOMETHING" occurs in the CSV file, use SOMETHING as the title of
the sheet.
---
 contrib/non-profit-audit-reports/csv2ods.py | 5 ++++-
 1 file changed, 4 insertions(+), 1 deletion(-)

(limited to 'contrib/non-profit-audit-reports')

diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py
index 7dd840c8..6aabcb59 100755
--- a/contrib/non-profit-audit-reports/csv2ods.py
+++ b/contrib/non-profit-audit-reports/csv2ods.py
@@ -169,7 +169,10 @@ def csv2ods(csvname, odsname, encoding='', singleFileDirectory=None, knownChecks
                         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', '')
-- 
cgit v1.2.3


From c0206418f730b0df45898dcf4280d9d96e801bb1 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Fri, 18 Jan 2013 17:04:08 -0500
Subject: Correct regular expression for account queries to avoid inclusion of
 sub-accounts.

The previous queries had a bug whereby an account in the form "A:B" would
include all transactions for sub accounts such as "A:B:C".

That's not the intended effect.  Entries should appear in the lowest level
account, and not in their parent.

The regular expression now is anchored at start and finish in both queries to
ensure this works correctly.
---
 contrib/non-profit-audit-reports/general-ledger-report.plx | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

(limited to 'contrib/non-profit-audit-reports')

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: $!";
 
-- 
cgit v1.2.3


From 86b20430423428f4ad371d98071679c9429f861b Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Mon, 21 Jan 2013 16:28:14 -0500
Subject: Remove output of TXT files.  CSV output is adequate.

---
 .../cash-receipts-and-disbursments-journals.plx             | 13 -------------
 1 file changed, 13 deletions(-)

(limited to 'contrib/non-profit-audit-reports')

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..b620a75e 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
@@ -85,10 +85,8 @@ foreach my $acct (@accounts) {
                          { name => 'receipts', query => 'a>0' }) {
     my $fileNameBase = $acctFilename . '-' . $typeData->{name};
 
-    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"';
 
@@ -106,10 +104,6 @@ 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');
-
     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, '"';
@@ -121,12 +115,6 @@ foreach my $acct (@accounts) {
                             '-b', $beginDate, '-e', $endDate, 'reg');
 
 
-    open(TXT_DATA, "-|", $LEDGER_CMD, @txtRegLedgerOpts)
-      or die "unable to run ledger command for $fileNameBase.txt: $!";
-
-    while (my $line = <TXT_DATA>) { print TEXT_OUT $line; }
-    close(TEXT_OUT); die "Error read write text out to $fileNameBase.txt: $!" unless $? == 0;
-
     open(CSV_DATA, "-|", $LEDGER_CMD, @csvRegLedgerOpts)
       or die "unable to run ledger command for $fileNameBase.csv: $!";
 
@@ -134,7 +122,6 @@ foreach my $acct (@accounts) {
     close(CSV_DATA); die "Error read from csv ledger command $!" unless $? == 0;
 
   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);
   }
-- 
cgit v1.2.3


From ccd0685b6d779c520c0792e2fe6c632a60bb0362 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Sun, 27 Jan 2013 15:22:05 -0500
Subject: Correct account names due to renaming of Conference accounts in chart
 of accounts.

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

(limited to 'contrib/non-profit-audit-reports')

diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx
index e9e1a3b8..5e6c447b 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.*(Conference:.*Sponsor|Booth|RBI)/'] },
                     'LICENSE ENFORCEMENT' => { args => [ '/^Income.*Enforce/' ]},
                     'TRADEMARKS' => {args => [ '/^Income.*Trademark/' ]},
                     'ADVERSITING' => {args => [ '/^Income.*Advertising/' ]});
-- 
cgit v1.2.3


From 7d04b92ad1445c60e0b61ab298f7384f0afbaa03 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Sun, 27 Jan 2013 15:22:13 -0500
Subject: This could be for any period, not just the FY.

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

(limited to 'contrib/non-profit-audit-reports')

diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx
index 5e6c447b..2860c9b4 100755
--- a/contrib/non-profit-audit-reports/summary-reports.plx
+++ b/contrib/non-profit-audit-reports/summary-reports.plx
@@ -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', '$',
-- 
cgit v1.2.3


From 11639785bba1b97600e9b06a85be248e8d2d7688 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Sun, 27 Jan 2013 20:24:07 -0500
Subject: Improve spreadsheet and debugging output.

---
 contrib/non-profit-audit-reports/bank-reconcilation.plx | 16 ++++++++++------
 1 file changed, 10 insertions(+), 6 deletions(-)

(limited to 'contrib/non-profit-audit-reports')

diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx
index 701f053e..5b8d3d6f 100755
--- a/contrib/non-profit-audit-reports/bank-reconcilation.plx
+++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx
@@ -206,20 +206,24 @@ while ($startDate ge $earliestStartDate) {
                         ConvertTwoDigitPrecisionToInteger($differenceSought),
                         \&ConvertTwoDigitPrecisionToIntegerInEntry);
   if ($VERBOSE) {
-    use Data::Dumper;
-    print STDERR "Solution for $formattedStartDate to $formattedEndDate, $differenceSought: \n",
-                 Data::Dumper->Dump(\@solution);
+    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";
+  print "\"$formattedEndDate\",\"\",\"BANK ACCOUNT BALANCE\",\"\$$bankBalance\"\n\n";
   foreach my $ee (@{$solution[1]}) {
-    print "\"$ee->{date}\",\"$ee->{checkNum}\",\"$ee->{payee}\",\"$ee->{amount}\"\n";
+    print "\"$ee->{date}\",\"$ee->{checkNum}\",\"$ee->{payee}\",\"\$$ee->{amount}\"\n";
   }
-    print "\n\"$formattedEndDate\",\"\",\"OUR ACCOUNT BALANCE\",\"$total\"\n\n";
+    print "\n\"$formattedEndDate\",\"\",\"OUR ACCOUNT BALANCE\",\"\$$total\"\n\n";
 }
 ###############################################################################
 #
-- 
cgit v1.2.3


From afe912f163da2259f29bfd3ac3f5bfbc50190156 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Sun, 27 Jan 2013 20:24:34 -0500
Subject: Searching is better when you can set the begin date, end date and
 then go back from begin date.

---
 contrib/non-profit-audit-reports/bank-reconcilation.plx | 12 ++++++------
 1 file changed, 6 insertions(+), 6 deletions(-)

(limited to 'contrib/non-profit-audit-reports')

diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx
index 5b8d3d6f..2a3d0d38 100755
--- a/contrib/non-profit-audit-reports/bank-reconcilation.plx
+++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx
@@ -125,8 +125,8 @@ my $firstArg = shift @ARGV;
 
 my $solver = \&BruteForceSubSetSumSolver;
 
-if (@ARGV < 6) {
-  print STDERR "usage: $0 [-d] <TITLE> <ACCOUNT_REGEX> <END_DATE> <START_SEARCH_FROM_DATE> <BANK_STATEMENT_BALANCE> <LEDGER_OPTIONS>\n";
+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') {
@@ -134,7 +134,7 @@ if ($firstArg eq '-d') {
 } else {
   unshift(@ARGV, $firstArg);
 }
-my($title, $account, $endDate, $startSearchFromDate, $bankBalance, @mainLedgerOptions) = @ARGV;
+my($title, $account, $endDate, $startSearchFromDate, $endSearchToDate, $bankBalance, @mainLedgerOptions) = @ARGV;
 
 $bankBalance = ParseNumber($bankBalance);
 
@@ -170,16 +170,16 @@ my $startDate = ParseDate($startSearchFromDate);
 
 my @solution;
 while ($startDate ge $earliestStartDate) {
-  print STDERR "START LOOP ITR: $startDate $earliestStartDate\n" if ($VERBOSE);
   $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 $endDate: \n" if $VERBOSE;
+  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', $startSearchFromDate,
+                      '-b', $formattedStartDate, '-e', $endSearchToDate,
                       '-F', '"%(date)","%C","%P","%t"\n',
                       'reg', "/$account/");
 
-- 
cgit v1.2.3


From e87b6abb7f1c7bfd7a626ba5d27dc03fd48f701b Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Sat, 16 Feb 2013 21:28:27 -0500
Subject: Sort solution by date in output.

---
 contrib/non-profit-audit-reports/bank-reconcilation.plx | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'contrib/non-profit-audit-reports')

diff --git a/contrib/non-profit-audit-reports/bank-reconcilation.plx b/contrib/non-profit-audit-reports/bank-reconcilation.plx
index 2a3d0d38..7a8da911 100755
--- a/contrib/non-profit-audit-reports/bank-reconcilation.plx
+++ b/contrib/non-profit-audit-reports/bank-reconcilation.plx
@@ -220,7 +220,7 @@ 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 (@{$solution[1]}) {
+  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";
-- 
cgit v1.2.3


From aea1445b042d3e5fac02da00775299bfa0807c64 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Sat, 16 Feb 2013 22:15:15 -0500
Subject: Rework report to be just two files (disbursements and receipts).
 Ensure that tagged linked files appear for all lines.

---
 .../cash-receipts-and-disbursments-journals.plx    | 37 +++++++++++-----------
 1 file changed, 18 insertions(+), 19 deletions(-)

(limited to 'contrib/non-profit-audit-reports')

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 b620a75e..65f0bda0 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,19 +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 = $acctFilename . '-' . $typeData->{name};
+foreach my $typeData ({ name => 'disbursements', query => 'a<=0' },
+                      { name => 'receipts', query => 'a>0' }) {
+  my $fileNameBase = $typeData->{name};
 
-    open(CSV_OUT, ">", "$fileNameBase.csv") or die "unable to open $fileNameBase.csv: $!";
+  open(CSV_OUT, ">", "$fileNameBase.csv") or die "unable to open $fileNameBase.csv: $!";
 
-    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);
@@ -104,27 +100,30 @@ foreach my $acct (@accounts) {
 
     goto SKIP_REGISTER_COMMANDS if (-z $tempFile);
 
-    my $formatString = '\n"%(date)","%C","%P","%A","%t"\n%/"","","","%A","%t"';
+    print CSV_OUT "\n\"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"';
+    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";
+    $formatString .= $tagStrings . '\n%/"","","","%A","%t"' . $tagStrings . '\n';
+
     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;
-
+    print CSV_OUT "\npagebreak\n";
   SKIP_REGISTER_COMMANDS:
-    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;
 }
 ###############################################################################
 #
-- 
cgit v1.2.3


From a08dd787deb89446bc516612a1ea01937cd2e963 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Mon, 18 Feb 2013 14:05:15 -0500
Subject: Remove extra newline

---
 .../cash-receipts-and-disbursments-journals.plx                         | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'contrib/non-profit-audit-reports')

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 65f0bda0..61b45b3c 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
@@ -100,7 +100,7 @@ foreach my $typeData ({ name => 'disbursements', query => 'a<=0' },
 
     goto SKIP_REGISTER_COMMANDS if (-z $tempFile);
 
-    print CSV_OUT "\n\"ACCOUNT:\",\"$acct\"\n\"PERIOD START:\",\"$beginDate\"\n\"PERIOD END:\",\"$formattedEndDate\"\n";
+    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"';
-- 
cgit v1.2.3


From 77827f9c80eac22834d17c4985778b70145e3243 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Mon, 18 Feb 2013 14:07:27 -0500
Subject: Subtotaling of BRANCH DEPOSITs.

The auditors seem to like to see the check deposits made to be subtotaled, so
that's done here.  I attempted to aid this by using a --sort and/or
--sort-xacts option (or combo thereof) on the ledger command line, but this
didn't work as expected.  I opened a bug in ledger about this:
   http://bugs.ledger-cli.org/show_bug.cgi?id=901
---
 .../cash-receipts-and-disbursments-journals.plx    | 45 +++++++++++++++++++++-
 1 file changed, 43 insertions(+), 2 deletions(-)

(limited to 'contrib/non-profit-audit-reports')

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 61b45b3c..58974a85 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
@@ -111,15 +111,56 @@ foreach my $typeData ({ name => 'disbursements', query => 'a<=0' },
     }
     $formatString .= $tagStrings . '\n%/"","","","%A","%t"' . $tagStrings . '\n';
 
+    # 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
+
     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; }
+    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 "$amt and $curDepositTotal for deposit on $curDepositDate\n";
+      }
+      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 "\npagebreak\n";
+    print CSV_OUT "pagebreak\n";
   SKIP_REGISTER_COMMANDS:
     unlink($tempFile);
   }
-- 
cgit v1.2.3


From 8fa91dcb31bc176634fbc8503035a1cc52256de8 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Mon, 18 Feb 2013 14:20:15 -0500
Subject: Remove spurious print statement.

---
 .../non-profit-audit-reports/cash-receipts-and-disbursments-journals.plx | 1 -
 1 file changed, 1 deletion(-)

(limited to 'contrib/non-profit-audit-reports')

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 58974a85..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
@@ -151,7 +151,6 @@ foreach my $typeData ({ name => 'disbursements', query => 'a<=0' },
 
         $curDepositTotal = 0.0 unless defined $curDepositTotal;
         $curDepositTotal += $amt;
-        print "$amt and $curDepositTotal for deposit on $curDepositDate\n";
       }
       print CSV_OUT $line;
     }
-- 
cgit v1.2.3


From cbdffb9a41b445dec9a9a99addf41112bac4f128 Mon Sep 17 00:00:00 2001
From: "Bradley M. Kuhn" <bkuhn@ebb.org>
Date: Mon, 18 Feb 2013 14:36:39 -0500
Subject: Catch a few additional accounts under Conference RBI category.

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

(limited to 'contrib/non-profit-audit-reports')

diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx
index 2860c9b4..78848dc5 100755
--- a/contrib/non-profit-audit-reports/summary-reports.plx
+++ b/contrib/non-profit-audit-reports/summary-reports.plx
@@ -214,7 +214,7 @@ my %incomeGroups = ('INTEREST INCOME' => { args => ['/^Income.*Interest/' ] },
                     'BOOK ROYALTIES & AFFILIATE PROGRAMS' =>
                     { args => [ '/^Income.*(Royalt|Affilate)/' ] },
                     'CONFERENCES, REGISTRATION' => {args => [ '/^Income.*Reg/' ] },
-                    'CONFERENCES, RELATED BUSINESS INCOME' => { args => [ '/^Income.*(Conference:.*Sponsor|Booth|RBI)/'] },
+                    'CONFERENCES, RELATED BUSINESS INCOME' => { args => [ '/^Income.*(Conferences?:.*Sponsor|Booth|RBI)/'] },
                     'LICENSE ENFORCEMENT' => { args => [ '/^Income.*Enforce/' ]},
                     'TRADEMARKS' => {args => [ '/^Income.*Trademark/' ]},
                     'ADVERSITING' => {args => [ '/^Income.*Advertising/' ]});
-- 
cgit v1.2.3