1 files changed, 103 insertions, 26 deletions
diff --git a/contrib/non-profit-audit-reports/general-ledger-report.plx b/contrib/non-profit-audit-reports/general-ledger-report.plx
index d1c92975..1fd0e7ce 100755
--- a/contrib/non-profit-audit-reports/general-ledger-report.plx
+++ b/contrib/non-profit-audit-reports/general-ledger-report.plx
@@ -4,8 +4,8 @@
 #    Script to generate a General Ledger report that accountants like
 #    using Ledger.
 #
-# Copyright (C) 2011, 2012 Bradley M. Kuhn
-# Copyright (C) 2012       Tom Marble
+# Copyright (C) 2011, 2012, 2013 Bradley M. Kuhn
+# Copyright (C) 2012             Tom Marble
 #
 # This program gives you software freedom; you can copy, modify, convey,
 # and/or redistribute it under the terms of the GNU General Public License
@@ -44,10 +44,24 @@ if (@ARGV < 3) {
   print STDERR "usage: $0 <BEGIN_DATE> <END_DATE> <OTHER_LEDGER_OPTS>\n";
   exit 1;
 }
+
+
 open(MANIFEST, ">", "MANIFEST") or die "Unable to open MANIFEST for writing: $!";
 
 my($beginDate, $endDate, @otherLedgerOpts) = @ARGV;
 
+my $formattedEndDate = new Date::Manip::Date;
+die "badly formatted end date, $endDate" if $formattedEndDate->parse($endDate);
+my $oneDayLess = new Date::Manip::Delta;
+die "bad one day less" if $oneDayLess->parse("- 1 day");
+$formattedEndDate = $formattedEndDate->calc($oneDayLess);
+$formattedEndDate = $formattedEndDate->printf("%Y/%m/%d");
+
+my $formattedBeginDate = new Date::Manip::Date;
+die "badly formatted end date, $endDate" if $formattedBeginDate->parse($endDate);
+$formattedBeginDate = $formattedBeginDate->printf("%Y/%m/%d");
+
+
 my(@chartOfAccountsOpts) = ('-V', '-F', "%150A\n",  '-w', '-s',
                             '-b', $beginDate, '-e', $endDate, @otherLedgerOpts, 'reg');
 
@@ -64,32 +78,83 @@ while (my $line = <CHART_DATA>) {
 }
 close(CHART_DATA); die "error reading ledger output for chart of accounts: $!" unless $? == 0;
 
-open(CHART_OUTPUT, ">", "chart-of-accounts.txt") or die "unable to write chart-of-accounts.txt: $!";
-print MANIFEST "chart-of-accounts.txt\n";
+open(CHART_OUTPUT, ">", "chart-of-accounts.csv") or die "unable to write chart-of-accounts.csv: $!";
+print MANIFEST "chart-of-accounts.csv\n";
+
+print CHART_OUTPUT "\"CHART OF ACCOUNTS\",";
+print CHART_OUTPUT "\"BEGINNING:\",\"$formattedBeginDate\",";
+print CHART_OUTPUT "\"ENDING:\",\"$formattedEndDate\"\n";
+
+sub preferredAccountSorting ($$) {
+  if ($_[0] =~ /^Assets/ and $_[1] !~ /^Assets/) {
+    return -1;
+  } elsif ($_[1] =~ /^Assets/ and $_[0] !~ /^Assets/) {
+    return 1;
+  } elsif ($_[0] =~ /^Liabilities/ and $_[1] !~ /^(Assets|Liabilities)/) {
+    return -1;
+  } elsif ($_[1] =~ /^Liabilities/ and $_[0] !~ /^(Assets|Liabilities)/) {
+    return 1;
+  } elsif ($_[0] =~ /^(Accrued:[^:]+Receivable)/ and $_[1] !~ /^(Assets|Liabilities|Accrued:[^:]+Receivable)/) {
+    return -1;
+  } elsif ($_[1] =~ /^(Accrued:[^:]+Receivable)/ and $_[0] !~ /^(Assets|Liabilities|Accrued:[^:]+Receivable)/) {
+    return 1;
+  } elsif ($_[0] =~ /^(Accrued)/ and $_[1] !~ /^(Assets|Liabilities|Accrued)/) {
+    return -1;
+  } elsif ($_[1] =~ /^(Accrued)/ and $_[0] !~ /^(Assets|Liabilities|Accrued)/) {
+    return 1;
+  } elsif ($_[0] =~ /^(Unearned Income)/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) {
+    return -1;
+  } elsif ($_[1] =~ /^(Unearned Income)/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) {
+    return 1;
+  } elsif ($_[0] =~ /^Income/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Income)/) {
+    return -1;
+  } elsif ($_[1] =~ /^Income/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Income)/) {
+    return 1;
+  } elsif ($_[0] =~ /^Expense/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Income|Unearned Income|Expense)/) {
+    return -1;
+  } elsif ($_[1] =~ /^Expense/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Income|Unearned Income|Expense)/) {
+    return 1;
+  } else {
+    return $_[0] cmp $_[1];
+  }
+}
+
 
 my @sortedAccounts;
-foreach my $acct (
-                  # Proper sorting for a chart of accounts
-                  sort {
-                    if ($a =~ /^Assets/ and $b !~ /^Assets/) {
-                      return -1;
-                    } elsif ($a =~ /^Liabilities/ and $b !~ /^Liabilitie/) {
-                      return -1;
-                    } else {
-                      return $a cmp $b;
-                    }
-                    } @accounts) {
-  print CHART_OUTPUT "$acct\n";
+foreach my $acct ( sort preferredAccountSorting @accounts) {
+  print CHART_OUTPUT "\"$acct\"\n";
   push(@sortedAccounts, $acct);
 }
 close(CHART_OUTPUT); die "error writing to chart-of-accounts.txt: $!" unless $? == 0;
 
-my $formattedEndDate = new Date::Manip::Date;
-die "badly formatted end date, $endDate" if $formattedEndDate->parse($endDate);
-my $oneDayLess = new Date::Manip::Delta;
-die "bad one day less" if $oneDayLess->parse("- 1 day");
-$formattedEndDate = $formattedEndDate->calc($oneDayLess);
-$formattedEndDate = $formattedEndDate->printf("%Y/%m/%d");
+my %commands = (
+                'totalEnd' => [ $LEDGER_CMD, @otherLedgerOpts, '-V', '-X', '$',
+                                              '-e', $endDate, '-F', '%-.80A   %22.108t\n', '-s',
+                                              'reg' ],
+                'totalBegin' =>  [ $LEDGER_CMD, @otherLedgerOpts, '-V', '-X', '$',
+                                     '-e', $beginDate, '-F', '%-.80A   %22.108t\n',
+                                     '-s', 'reg' ]);
+
+my %balanceData;
+
+foreach my $id (keys %commands) {
+  my(@command) = @{$commands{$id}};
+
+  open(FILE, "-|", @command) or die "unable to run command ledger command: @command: $!";
+
+  foreach my $line (<FILE>) {
+    die "Unable to parse output line from balance data $id command: $line"
+      unless $line =~ /^\s*([^\$]+)\s+\$\s*([\-\d\.\,]+)/;
+    my($account, $amount) = ($1, $2);
+    $amount = ParseNumber($amount);
+    $account =~ s/\s+$//;
+    next if $account =~ /\<Adjustment\>/ and (abs($amount) <= 0.02);
+    next if $account =~ /^Equity:/;   # Stupid auto-account made by ledger.
+    $balanceData{$id}{$account} = $amount;
+  }
+  close FILE;
+  die "unable to run  balance data ledger command, @command: $!" unless ($? == 0);
+}
 
 open(GL_TEXT_OUT, ">", "general-ledger.txt") or die "unable to write general-ledger.txt: $!";
 print MANIFEST "general-ledger.txt\n";
@@ -110,32 +175,44 @@ foreach my $acct (@sortedAccounts) {
   }
   close(GL_TEXT_DATA); die "error reading ledger output for chart of accounts: $!" unless $? == 0;
 
-  print GL_CSV_OUT "\n\"ACCOUNT:\",\"$acct\"\n\"PERIOD START:\",\"$beginDate\"\n\"PERIOD END:\",\"$formattedEndDate\"\n";
-  print GL_CSV_OUT '"DATE","CHECK NUM","NAME","TRANSACTION AMT","RUNNING TOTAL"';
-  my $formatString = '"%(date)","%C","%P","%t","%T"';
+  print GL_CSV_OUT "\n\"ACCOUNT:\",\"$acct\"\n\"PERIOD START:\",\"$formattedBeginDate\"\n\"PERIOD END:\",\"$formattedEndDate\"\n";
+  print GL_CSV_OUT '"DATE","CHECK NUM","NAME","TRANSACTION AMT","BALANCE"';
+
+  my $formatString = '"%(date)","%C","%P","%t",""';
   foreach my $tagField (qw/Receipt Invoice Statement Contract PurchaseOrder Approval Check IncomeDistributionAnalysis CurrencyRate/) {
     print GL_CSV_OUT ',"', $tagField, '"';
-    $formatString .= ',"%(tag(\'' . $tagField . '\'))"';
+    $formatString .= ',"link:%(tag(\'' . $tagField . '\'))"';
   }
   $formatString .= "\n";
   print GL_CSV_OUT "\n";
+  if ($acct =~ /^(Assets|Liabilities|Accrued|Unearned Income)/) {
+    $balanceData{totalBegin}{$acct} = $ZERO unless defined $balanceData{totalBegin}{$acct};
+    print GL_CSV_OUT "\"$formattedBeginDate\"", ',"","BALANCE","","$', "$balanceData{totalBegin}{$acct}\"\n";
+  }
 
   @acctLedgerOpts = ('-V', '-F', $formatString, '-w', '--sort', 'd', '-b', $beginDate, '-e', $endDate, @otherLedgerOpts, 'reg', $acct);
   open(GL_CSV_DATA, "-|", $LEDGER_CMD, @acctLedgerOpts)
     or die "Unable to run $LEDGER_CMD @acctLedgerOpts: $!";
 
   foreach my $line (<GL_CSV_DATA>) {
+    $line =~ s/"link:"/""/g;
     print GL_CSV_OUT $line;
     next if $line =~ /ACCOUNT:.*PERIOD/;  # Skip column header lines
     $line =~ s/^"[^"]*","[^"]*","[^"]*","[^"]*","[^"]*",//;
     while ($line =~ s/^"([^"]*)"(,|$)//) {
       my $file = $1;
       next if $file =~ /^\s*$/;
+      $file =~ s/^link:(.*)$/$1/;
       warn "$file does not exist and/or is not readable" unless -r $file;
       print MANIFEST "$file\n" if not defined $manifest{$file};
       $manifest{$file} = $line;
     }
   }
+  if ($acct =~ /^(Assets|Liabilities|Accrued|Unearned Income)/) {
+    $balanceData{totalEnd}{$acct} = $ZERO unless defined $balanceData{totalEnd}{$acct};
+    print GL_CSV_OUT "\"$formattedEndDate\"", ',"","BALANCE","","$', "$balanceData{totalEnd}{$acct}\"\n";
+  }
+  print GL_CSV_OUT "pagebreak\n";
   close(GL_CSV_DATA); die "error reading ledger output for chart of accounts: $!" unless $? == 0;
 }
 close(GL_TEXT_OUT); die "error writing to general-ledger.txt: $!" unless $? == 0;