1#!/usr/bin/perl
2# fund-report.plx                                    -*- Perl -*-
3#
4#    Script to generate a Restricted Fund Report.  Usefulness of this
5#    script may be confined to those who track separate funds in their
6#    accounts by having accounts that match this format:
7#     /^(Income|Expenses|Unearned Income|(Accrued:[^:]+:):PROJECTNAME/
8
9#  Conservancy does this because we carefully track fund balances for our
10#  fiscal sponsored projects.  Those who aren't fiscal sponsors won't find
11#  this report all that useful, I suspect.  Note that the name
12#  "Conservancy" is special-cased in a few places, mainly because our
13#  "General" fund is called "Conservancy".
14
15#
16# Copyright (C) 2011, 2012, 2013 Bradley M. Kuhn
17#
18# This program gives you software freedom; you can copy, modify, convey,
19# and/or redistribute it under the terms of the GNU General Public License
20# as published by the Free Software Foundation; either version 3 of the
21# License, or (at your option) any later version.
22#
23# This program is distributed in the hope that it will be useful, but
24# WITHOUT ANY WARRANTY; without even the implied warranty of
25# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
26# General Public License for more details.
27#
28# You should have received a copy of the GNU General Public License along
29# with this program in a file called 'GPLv3'.  If not, write to the:
30#    Free Software Foundation, Inc., 51 Franklin St, Fifth Floor
31#                                    Boston, MA 02110-1301, USA.
32
33use strict;
34use warnings;
35
36use Math::BigFloat;
37use Date::Manip;
38
39my $LEDGER_CMD = "/usr/local/bin/ledger";
40
41my $ACCT_WIDTH = 70;
42
43sub ParseNumber($) {
44  $_[0] =~ s/,//g;
45  return Math::BigFloat->new($_[0]);
46}
47Math::BigFloat->precision(-2);
48my $ZERO =  Math::BigFloat->new("0.00");
49my $TWO_CENTS =  Math::BigFloat->new("0.02");
50
51if (@ARGV < 2) {
52  print STDERR "usage: $0 <START_DATE> <END_DATE> <LEDGER_OPTIONS>\n";
53  exit 1;
54}
55my($startDate, $endDate, @mainLedgerOptions) = @ARGV;
56
57my $err;
58my $formattedEndDate = UnixDate(DateCalc(ParseDate($endDate), ParseDateDelta("- 1 day"), \$err),
59                                "%Y/%m/%d");
60die "Date calculation error on $endDate" if ($err);
61my $formattedStartDate = UnixDate(ParseDate($startDate), "%Y/%m/%d");
62die "Date calculation error on $startDate" if ($err);
63
64# First, get balances for starting and ending for each fund
65
66my %funds;
67
68foreach my $type ('starting', 'ending') {
69  my(@ledgerOptions) = (@mainLedgerOptions,
70                      '-V', '-X', '$', '-F', "%-.70A %22.108t\n", '-s');
71
72  if ($type eq 'starting') {
73    push(@ledgerOptions, '-e', $startDate);
74  } else {
75    push(@ledgerOptions,'-e', $endDate);
76  }
77  push(@ledgerOptions, 'reg', '/^(Income|Expenses):([^:]+):/');
78
79  open(LEDGER_FUNDS, "-|", $LEDGER_CMD, @ledgerOptions)
80    or die "Unable to run $LEDGER_CMD @ledgerOptions: $!";
81
82  while (my $fundLine = <LEDGER_FUNDS>) {
83    die "Unable to parse output line from first funds command: \"$fundLine\""
84      unless $fundLine =~ /^\s*([^\$]+)\s+\$\s*\s*([\-\d\.\,]+)/;
85    my($account, $amount) = ($1, $2);
86    $amount = ParseNumber($amount);
87    $account =~ s/\s+$//;
88    next if $account =~ /\<Adjustment\>/ and (abs($amount) <= $TWO_CENTS);
89    die "Weird account found, $account with amount of $amount in command: @ledgerOptions\n"
90      unless $account =~ s/^\s*(?:Income|Expenses):([^:]+)://;
91    $account = $1;
92    $account = 'General' if $account eq 'Conservancy';   # FIXME: this is a special case for Consrevancy
93    $funds{$account}{$type} += $amount;
94  }
95  close LEDGER_FUNDS;
96  die "Failure on ledger command @ledgerOptions: $!" unless ($? == 0);
97}
98foreach my $fund (keys %funds) {
99  foreach my $type (keys %{$funds{$fund}}) {
100    $funds{$fund}{$type} = $ZERO - $funds{$fund}{$type};
101  }
102}
103my(@ledgerOptions) = (@mainLedgerOptions,
104                  '-V', '-X', '$', '-F', "%-.70A %22.108t\n",  '-w', '-s',
105                  '-b', $startDate, '-e', $endDate, 'reg');
106
107my @possibleTypes = ('Income', 'Expenses', 'Unearned Income', 'Retained Earnings', 'Retained Costs',
108                     'Accrued:Loans Receivable', 'Accrued:Accounts Payable',
109                     'Accrued:Accounts Receivable', 'Accrued:Expenses');
110
111foreach my $type (@possibleTypes) {
112  foreach my $fund (keys %funds) {
113    my $query;
114    $query = ($fund eq 'General') ? "/^${type}:Conservancy/": "/^${type}:$fund/";
115    open(LEDGER_INCOME, "-|", $LEDGER_CMD, @ledgerOptions, $query)
116      or die "Unable to run $LEDGER_CMD for funds: $!";
117    $funds{$fund}{$type} = $ZERO;
118    while (my $line = <LEDGER_INCOME>) {
119      die "Unable to parse output line from $type line command: $line"
120        unless $line =~ /^\s*([^\$]+)\s+\$\s*\s*([\-\d\.\,]+)/;
121      my($account, $amount) = ($1, $2);
122      $amount = ParseNumber($amount);
123      $funds{$fund}{$type} += $amount;
124    }
125    close LEDGER_INCOME;
126    die "Failure on ledger command for ${type}:$fund: $!" unless ($? == 0);
127  }
128}
129
130my %tot;
131($tot{Start}, $tot{End}) = ($ZERO, $ZERO);
132
133my %beforeEndings = ('Income' => 1, 'Expenses' => 1);
134my %afterEndings;
135
136# For other @possibleTypes, build up @fieldsList to just thoes that are present.
137
138foreach my $fund (keys %funds) {
139  foreach my $type (@possibleTypes) {
140    if ($funds{$fund}{$type} != $ZERO) {
141      if ($type =~ /^(Unearned Income|Accrued)/) {
142        $afterEndings{$type} = 1;
143      } else {
144        $beforeEndings{$type} = 1;
145      }
146    }
147  }
148}
149my(@beforeEndingFields, @afterEndingFields);
150
151foreach my $ii (@possibleTypes) {
152  push(@beforeEndingFields, $ii) if defined $beforeEndings{$ii};
153  push(@afterEndingFields, $ii)  if defined $afterEndings{$ii};
154}
155# Make sure fieldLists present items are zero for those that should be zero.
156foreach my $fund (keys %funds) {
157  foreach my $type ('starting', @beforeEndingFields, 'ending', @afterEndingFields) {
158    $funds{$fund}{$type} = $ZERO unless defined $funds{$fund}{$type};
159  }
160}
161
162print '"RESTRICTED AND GENERAL FUND REPORT",', "\"BEGINNING:\",\"$formattedStartDate\",\"ENDING:\",\"$formattedEndDate\"\n\n";
163print '"FUND","STARTING BALANCE",';
164my @finalPrints;
165foreach my $type (@beforeEndingFields) {
166  $tot{$type} = $ZERO;
167  my $formattedType = $type;
168  print "\"$formattedType\",";
169}
170print '"ENDING BALANCE",""';
171foreach my $type (@afterEndingFields) {
172  $tot{$type} = $ZERO;
173  my $formattedType = $type;
174  $formattedType = "Prepaid Expenses" if $formattedType eq 'Accrued:Expenses';
175  $formattedType =~ s/^Accrued://;
176  print ",\"$formattedType\"";
177}
178print "\n\n";
179
180sub printTotal ($$) {
181  my($label, $tot) = @_;
182  print "\"$label\",\"\$$tot->{Start}\",";
183  foreach my $type (@beforeEndingFields) {
184    print "\"\$$tot->{$type}\",";
185  }
186  print "\"\$$tot->{End}\",\"\"";
187  foreach my $type (@afterEndingFields) {
188    print ",\"\$$tot->{$type}\"";
189  }
190  print "\n";
191}
192
193foreach my $fund (sort {
194                        if ($a eq "General") { return 1 }
195                        elsif ($b eq "General") { return -1 }
196                        else { return $a cmp $b } }
197                  keys %funds) {
198  my $sanityTotal = $funds{$fund}{starting};
199
200  if ($fund eq 'General') {
201    print "\n";
202    printTotal("Restricted Subtotal", \%tot);
203    print "\n";
204  }
205  $tot{Start} += $funds{$fund}{starting};
206  $tot{End} += $funds{$fund}{ending};
207
208  print "\"$fund\",\"\$$funds{$fund}{starting}\",";
209  foreach my $type (@beforeEndingFields) {
210    print "\"\$$funds{$fund}{$type}\",";
211    $tot{$type} += $funds{$fund}{$type};
212  }
213  print "\"\$$funds{$fund}{ending}\",\"\"";
214  foreach my $type (@afterEndingFields) {
215    print ",\"\$$funds{$fund}{$type}\"";
216    $tot{$type} += $funds{$fund}{$type};
217  }
218  print "\n";
219  # Santity check:
220  if (abs($funds{$fund}{ending} -
221      ($funds{$fund}{starting}
222         - $funds{$fund}{Income} - $funds{$fund}{Expenses}))
223      > $TWO_CENTS) {
224    print "$fund FAILED SANITY CHECK: Ending: $funds{$fund}{ending} \n\n\n";
225    warn "$fund FAILED SANITY CHECK";
226  }
227}
228print "\n";
229printTotal("OVERALL TOTAL", \%tot);
230###############################################################################
231#
232# Local variables:
233# compile-command: "perl -c fund-report.plx"
234# End:
235
236