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