From 269d0fdd5efe2dc9de148f845f97ad95ddc1b8e9 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Thu, 3 Jan 2013 10:39:58 -0500 Subject: Created Trial balance report for summary reports. --- .../non-profit-audit-reports/summary-reports.plx | 31 +++++++++++++++++++++- 1 file changed, 30 insertions(+), 1 deletion(-) (limited to 'contrib/non-profit-audit-reports/summary-reports.plx') diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx index 7d2267d6..ce6d56da 100755 --- a/contrib/non-profit-audit-reports/summary-reports.plx +++ b/contrib/non-profit-audit-reports/summary-reports.plx @@ -3,7 +3,7 @@ # # Script to generate end-of-year summary reports. # -# 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 @@ -348,6 +348,35 @@ die "calculated total of $overallTotal does equal $firstTotal" print STDERR "\n"; +open(TRIAL, ">", "trial-balance.txt") or die "unable to open accrued.txt for writing: $!"; + +@fullCommand = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$', + '-e', $endDate, + '-F', '%-.80A %22.108t\n', '-s', + 'reg'); + +print TRIAL " TRIAL BALANCE \n", + " Ending $formattedEndDate\n\n"; + +open(FILE, "-|", @fullCommand) + or die "unable to run command ledger command: @fullCommand: $!"; + +print STDERR ($VERBOSE ? "Running: @fullCommand\n" : "."); + +my $accruedTotal = $ZERO; + +foreach my $line () { + die "Unable to parse output line from second funds command: $line" + unless $line =~ /^\s*([^\$]+)\s+\$\s*([\-\d\.\,]+)/; + my($account, $amount) = ($1, $2); + $amount = ParseNumber($amount); + $account =~ s/\s+$//; + next if $account =~ /\|^Equity:/ and (abs($amount) <= 0.02); + print TRIAL $line; + + $accruedTotal += $amount; +} + ############################################################################### # # Local variables: -- cgit v1.2.3 From bfdf20b31cbb60b5d92c82ecd3a5192059a0c7b1 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Thu, 3 Jan 2013 11:44:55 -0500 Subject: Updated sorting function based on advice of auditing accountants. Our auditing accounts tell us they want accounts sorted by: Assets Liabilities Net Assets Income Expenses in a general ledger report. Generally, I think we should just apply the same sorting. Since Ledger doesn't use account codes by default, this is my hack to solve this problem for now. Maybe there should be an account code tag for sorting purposes at least? --- .../general-ledger-report.plx | 46 ++++++++++++++++------ .../non-profit-audit-reports/summary-reports.plx | 30 ++++++++++++++ 2 files changed, 63 insertions(+), 13 deletions(-) (limited to 'contrib/non-profit-audit-reports/summary-reports.plx') diff --git a/contrib/non-profit-audit-reports/general-ledger-report.plx b/contrib/non-profit-audit-reports/general-ledger-report.plx index d1c92975..66fc0031 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 @@ -67,18 +67,38 @@ close(CHART_DATA); die "error reading ledger output for chart of accounts: $!" u open(CHART_OUTPUT, ">", "chart-of-accounts.txt") or die "unable to write chart-of-accounts.txt: $!"; print MANIFEST "chart-of-accounts.txt\n"; +sub preferredAccountSorting ($$) { + if ($_[0] =~ /^Assets/) { + return -1; + } elsif ($_[1] =~ /^Assets/) { + return 1; + } elsif ($_[0] =~ /^Liabilities/ and $_[1] !~ /^Assets/) { + return -1; + } elsif ($_[1] =~ /^Liabilities/ and $_[0] !~ /^Assets/) { + return 1; + } elsif ($_[0] =~ /^(Accrued)/ and $_[1] !~ /^(Assets|Liabilities)/) { + return -1; + } elsif ($_[1] =~ /^(Accrued)/ and $_[0] !~ /^(Assets|Liabilities)/) { + return 1; + } elsif ($_[0] =~ /^(Unearned Income)/ and $_[1] !~ /^(Assets|Liabilities|Accrued)/) { + return -1; + } elsif ($_[1] =~ /^(Unearned Income)/ and $_[0] !~ /^(Assets|Liabilities|Accrued)/) { + return 1; + } elsif ($_[0] =~ /^Income/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { + return -1; + } elsif ($_[1] =~ /^Income/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { + return 1; + } elsif ($_[0] =~ /^Expense/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Expense)/) { + return -1; + } elsif ($_[1] =~ /^Expense/ and $_[0] !~ /^(Assets|Liabilities|Accrued|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) { +foreach my $acct ( sort preferredAccountSorting @accounts) { print CHART_OUTPUT "$acct\n"; push(@sortedAccounts, $acct); } diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx index ce6d56da..30b27505 100755 --- a/contrib/non-profit-audit-reports/summary-reports.plx +++ b/contrib/non-profit-audit-reports/summary-reports.plx @@ -39,6 +39,36 @@ sub Commify ($) { return scalar reverse $text; } +sub preferredAccountSorting ($$) { + if ($_[0] =~ /^Assets/) { + return -1; + } elsif ($_[1] =~ /^Assets/) { + return 1; + } elsif ($_[0] =~ /^Liabilities/ and $_[1] !~ /^Assets/) { + return -1; + } elsif ($_[1] =~ /^Liabilities/ and $_[0] !~ /^Assets/) { + return 1; + } elsif ($_[0] =~ /^(Accrued)/ and $_[1] !~ /^(Assets|Liabilities)/) { + return -1; + } elsif ($_[1] =~ /^(Accrued)/ and $_[0] !~ /^(Assets|Liabilities)/) { + return 1; + } elsif ($_[0] =~ /^(Unearned Income)/ and $_[1] !~ /^(Assets|Liabilities|Accrued)/) { + return -1; + } elsif ($_[1] =~ /^(Unearned Income)/ and $_[0] !~ /^(Assets|Liabilities|Accrued)/) { + return 1; + } elsif ($_[0] =~ /^Income/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { + return -1; + } elsif ($_[1] =~ /^Income/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { + return 1; + } elsif ($_[0] =~ /^Expense/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Expense)/) { + return -1; + } elsif ($_[1] =~ /^Expense/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Expense)/) { + return 1; + } else { + return $_[0] cmp $_[1]; + } +} + sub ParseNumber($) { $_[0] =~ s/,//g; return Math::BigFloat->new($_[0]); -- cgit v1.2.3 From d18e01a00f88ec7d073b88db464679a9aa9910a6 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Thu, 3 Jan 2013 12:19:59 -0500 Subject: Changed balance sheet output from a TXT file to a CSV file. --- contrib/non-profit-audit-reports/summary-reports.plx | 16 ++++++++-------- 1 file changed, 8 insertions(+), 8 deletions(-) (limited to 'contrib/non-profit-audit-reports/summary-reports.plx') diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx index 30b27505..da2dba9d 100755 --- a/contrib/non-profit-audit-reports/summary-reports.plx +++ b/contrib/non-profit-audit-reports/summary-reports.plx @@ -146,15 +146,15 @@ foreach my $item (keys %reportFields) { print STDERR "$item: $reportFields{$item}{total}\n" if $VERBOSE; } -open(BALANCE_SHEET, ">", "balance-sheet.txt") - or die "unable to open balance-sheet.txt for writing: $!"; +open(BALANCE_SHEET, ">", "balance-sheet.csv") + or die "unable to open balance-sheet.csv for writing: $!"; -print BALANCE_SHEET " BALANCE SHEET\n", - " Ending ", $formattedEndDate, "\n", - "\n\nASSETS\n\n"; +print BALANCE_SHEET "\"BALANCE SHEET\"\n", + "\"Ending\",\"", $formattedEndDate, "\"\n", + "\n\n\"ASSETS\"\n\n"; -my $formatStr = " %-42s \$%13s\n"; -my $formatStrTotal = "%-45s \$%13s\n"; +my $formatStr = "\"\",\"%-42s\",\"\$%13s\"\n"; +my $formatStrTotal = "\"\",\"%-45s\",\"\$%13s\"\n"; my $tot = $ZERO; foreach my $item ('Cash', 'Accounts Receivable', 'Loans Receivable') { next if $reportFields{$item}{total} == $ZERO; @@ -186,7 +186,7 @@ print BALANCE_SHEET "\n", sprintf($formatStr, "TOTAL NET ASSETS", Commify($totNe close BALANCE_SHEET; print STDERR "\n"; -die "unable to write to balance-sheet.txt: $!" unless ($? == 0); +die "unable to write to balance-sheet.csv: $!" unless ($? == 0); die "Cash+accounts receivable total does not equal net assets and liabilities total" if (abs( ($reportFields{'Cash'}{total} + $reportFields{'Accounts Receivable'}{total} -- cgit v1.2.3 From b939bbe8c61a819e413b3e2a24d223f6232bd0d9 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Thu, 3 Jan 2013 12:30:43 -0500 Subject: Convert trial-balance report to CSV from TXT file. Also, ignore Ledger's Equity: accounts properly. --- .../non-profit-audit-reports/summary-reports.plx | 23 ++++++++++++++++------ 1 file changed, 17 insertions(+), 6 deletions(-) (limited to 'contrib/non-profit-audit-reports/summary-reports.plx') diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx index da2dba9d..dae8f2ce 100755 --- a/contrib/non-profit-audit-reports/summary-reports.plx +++ b/contrib/non-profit-audit-reports/summary-reports.plx @@ -378,15 +378,15 @@ die "calculated total of $overallTotal does equal $firstTotal" print STDERR "\n"; -open(TRIAL, ">", "trial-balance.txt") or die "unable to open accrued.txt for writing: $!"; +open(TRIAL, ">", "trial-balance.csv") or die "unable to open accrued.txt for writing: $!"; @fullCommand = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$', '-e', $endDate, '-F', '%-.80A %22.108t\n', '-s', 'reg'); -print TRIAL " TRIAL BALANCE \n", - " Ending $formattedEndDate\n\n"; +print TRIAL "\"TRIAL BALANCE REPORT\",", + "\"ENDING:\",\"$formattedEndDate\"\n\n\"ACCOUNT NAME\", \"AMOUNT\"\n\n"; open(FILE, "-|", @fullCommand) or die "unable to run command ledger command: @fullCommand: $!"; @@ -395,17 +395,28 @@ print STDERR ($VERBOSE ? "Running: @fullCommand\n" : "."); my $accruedTotal = $ZERO; +my %trialBalances; + foreach my $line () { die "Unable to parse output line from second funds command: $line" unless $line =~ /^\s*([^\$]+)\s+\$\s*([\-\d\.\,]+)/; my($account, $amount) = ($1, $2); $amount = ParseNumber($amount); $account =~ s/\s+$//; - next if $account =~ /\|^Equity:/ and (abs($amount) <= 0.02); - print TRIAL $line; - + next if $account =~ /\/ and (abs($amount) <= 0.02); + next if $account =~ /^Equity:/; # Stupid auto-account made by ledger. + $trialBalances{$account} = $amount; $accruedTotal += $amount; } +close FILE; +die "unable to run trial balance ledger command: $!" unless ($? == 0); + +foreach my $account (sort preferredAccountSorting keys %trialBalances) { + print TRIAL "\"$account\",\"$trialBalances{$account}\"\n"; +} + +close TRIAL; +die "unable to write trial-balance.csv: $!" unless ($? == 0); ############################################################################### # -- cgit v1.2.3 From 750321c0b1e35b0315138b35524f85d3648d8dda Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Thu, 3 Jan 2013 13:37:08 -0500 Subject: Change Income and Expenses reports to generate CSV files, rather than TXT files. --- .../non-profit-audit-reports/summary-reports.plx | 35 +++++++++++----------- 1 file changed, 18 insertions(+), 17 deletions(-) (limited to 'contrib/non-profit-audit-reports/summary-reports.plx') diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx index dae8f2ce..efad30e7 100755 --- a/contrib/non-profit-audit-reports/summary-reports.plx +++ b/contrib/non-profit-audit-reports/summary-reports.plx @@ -223,7 +223,7 @@ foreach my $type (keys %incomeGroups) { $incomeGroups{"OTHER"}{args} = \@otherArgs; $incomeGroups{"TOTAL"}{args} = ['/^Income/']; -open(INCOME, ">", "income.txt") or die "unable to open income.txt for writing: $!"; +open(INCOME, ">", "income.csv") or die "unable to open income.csv for writing: $!"; foreach my $type (keys %incomeGroups) { my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$', @@ -247,32 +247,32 @@ foreach my $type (keys %incomeGroups) { $account =~ s/\s+$//; next if $account =~ /\/ and (abs($amount) <= 0.02); die "Weird account found, $account with amount of $amount in income command\n" - unless $account =~ s/^\s*Income://; + unless $account =~ /^\s*Income:/; $incomeGroups{$type}{total} += $amount; - $incomeGroups{$type}{output} .= " $line"; + $incomeGroups{$type}{output} .= "\"$account\",\"\$$amount\"\n"; } } -print INCOME " INCOME\n", - " Between $formattedStartDate and $formattedEndDate\n\n"; +print INCOME "\"INCOME\",", + "\"STARTING:\",\"$formattedStartDate\",\"ENDING:\",\"$formattedEndDate\"\n\n"; my $overallTotal = $ZERO; -$formatStrTotal = "%-90s \$%14s\n"; +$formatStrTotal = "\"%-90s\",\"\$%14s\"\n"; foreach my $type ('DONATIONS', 'LICENSE ENFORCEMENT', 'CONFERENCES, REGISTRATION', 'CONFERENCES, RELATED BUSINESS INCOME', 'BOOK ROYALTIES & AFFILIATE PROGRAMS', 'ADVERSITING', 'TRADEMARKS', 'INTEREST INCOME', 'OTHER') { next if ($incomeGroups{$type}{output} =~ /^\s*$/ and $incomeGroups{$type}{total} == $ZERO); - print INCOME "\n$type\n", + print INCOME "\n\"$type\"\n", $incomeGroups{$type}{output}, "\n", sprintf($formatStrTotal, "TOTAL $type:", Commify($incomeGroups{$type}{total})); $overallTotal += $incomeGroups{$type}{total}; } print INCOME "\n\n\n", sprintf($formatStrTotal, "OVERALL TOTAL:", Commify($overallTotal)); -close INCOME; die "unable to write to income.txt: $!" unless ($? == 0); +close INCOME; die "unable to write to income.csv: $!" unless ($? == 0); die "calculated total of $overallTotal does equal $incomeGroups{TOTAL}{total}" if (abs($overallTotal) - abs($incomeGroups{TOTAL}{total}) > $ONE_PENNY); @@ -298,7 +298,7 @@ foreach my $type (keys %expenseGroups, 'TRAVEL') { $expenseGroups{$type}{output} = ""; } -open(EXPENSE, ">", "expense.txt") or die "unable to open expense.txt for writing: $!"; +open(EXPENSE, ">", "expense.csv") or die "unable to open expense.csv for writing: $!"; my(@fullCommand) = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$', '-b', $startDate, '-e', $endDate, @@ -321,6 +321,7 @@ foreach my $line () { die "Weird account found, $account, with amount of $amount in expenses command\n" unless $account =~ /^\s*Expenses:/; + my $outputLine = "\"$account\",\"\$$amount\"\n"; my $taken = 0; # Note: Prioritize to put things under conference expenses if they were for a conference. foreach my $type ('CONFERENCES', keys %expenseGroups) { @@ -329,23 +330,23 @@ foreach my $line () { next unless $line =~ /$expenseGroups{$type}{regex}/; $taken = 1; $expenseGroups{$type}{total} += $amount; - $expenseGroups{$type}{output} .= " $line"; + $expenseGroups{$type}{output} .= $outputLine; } if (not $taken) { if ($account =~ /Travel/) { $expenseGroups{'TRAVEL'}{total} += $amount; - $expenseGroups{'TRAVEL'}{output} .= " $line"; + $expenseGroups{'TRAVEL'}{output} .= $outputLine; } else { $expenseGroups{'OTHER'}{total} += $amount; - $expenseGroups{'OTHER'}{output} .= " $line"; + $expenseGroups{'OTHER'}{output} .= $outputLine; } } $firstTotal += $amount; } -print EXPENSE " EXPENSES\n", - " Between $formattedStartDate and $formattedEndDate\n\n"; +print EXPENSE "\"EXPENSES\",", + "\"STARTING:\",\"$formattedStartDate\",\"ENDING:\",\"$formattedEndDate\"\n\n"; $overallTotal = $ZERO; -$formatStrTotal = "%-90s \$%14s\n"; +$formatStrTotal = "\"%-90s\",\"\$%14s\"\n"; my %verifyAllGroups; foreach my $key (keys %expenseGroups) { @@ -360,7 +361,7 @@ foreach my $type ('PAYROLL', 'SOFTWARE DEVELOPMENT', 'LICENSE ENFORCEMENT', 'CON die "$type is not defined!" if not defined $expenseGroups{$type}; next if ($expenseGroups{$type}{output} =~ /^\s*$/ and $expenseGroups{$type}{total} == $ZERO); - print EXPENSE "\n$type\n", + print EXPENSE "\n\"$type\"\n", $expenseGroups{$type}{output}, "\n", sprintf($formatStrTotal, "TOTAL $type:", Commify($expenseGroups{$type}{total})); $overallTotal += $expenseGroups{$type}{total}; @@ -368,7 +369,7 @@ foreach my $type ('PAYROLL', 'SOFTWARE DEVELOPMENT', 'LICENSE ENFORCEMENT', 'CON print EXPENSE "\n\n\n", sprintf($formatStrTotal, "OVERALL TOTAL:", Commify($overallTotal)); -close EXPENSE; die "unable to write to expense.txt: $!" unless ($? == 0); +close EXPENSE; die "unable to write to expense.csv: $!" unless ($? == 0); die "GROUPS NOT INCLUDED : ", join(keys(%verifyAllGroups), ", "), "\n" unless (keys %verifyAllGroups == 0); -- cgit v1.2.3 From 2fad8fe238cee8509290562a823fe0c3b312ee94 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Thu, 3 Jan 2013 13:37:18 -0500 Subject: Some minor formatting fixes for the trial balance report. --- contrib/non-profit-audit-reports/summary-reports.plx | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'contrib/non-profit-audit-reports/summary-reports.plx') diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx index efad30e7..8e9339a5 100755 --- a/contrib/non-profit-audit-reports/summary-reports.plx +++ b/contrib/non-profit-audit-reports/summary-reports.plx @@ -387,7 +387,7 @@ open(TRIAL, ">", "trial-balance.csv") or die "unable to open accrued.txt for wri 'reg'); print TRIAL "\"TRIAL BALANCE REPORT\",", - "\"ENDING:\",\"$formattedEndDate\"\n\n\"ACCOUNT NAME\", \"AMOUNT\"\n\n"; + "\"ENDING:\",\"$formattedEndDate\"\n\n\"ACCOUNT NAME\",\"AMOUNT\"\n\n"; open(FILE, "-|", @fullCommand) or die "unable to run command ledger command: @fullCommand: $!"; @@ -413,7 +413,7 @@ close FILE; die "unable to run trial balance ledger command: $!" unless ($? == 0); foreach my $account (sort preferredAccountSorting keys %trialBalances) { - print TRIAL "\"$account\",\"$trialBalances{$account}\"\n"; + print TRIAL "\"$account\",\"\$$trialBalances{$account}\"\n"; } close TRIAL; -- cgit v1.2.3 From e317e1f23e236a2797b13113bc2afae14502fc5c Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Fri, 4 Jan 2013 10:18:41 -0500 Subject: Sort of accounts was buggy; it never made the final else due to bad regexes. This fix now has the sort working correctly. --- .../general-ledger-report.plx | 29 +++++++++++++--------- .../non-profit-audit-reports/summary-reports.plx | 28 ++++++++++++--------- 2 files changed, 33 insertions(+), 24 deletions(-) (limited to 'contrib/non-profit-audit-reports/summary-reports.plx') diff --git a/contrib/non-profit-audit-reports/general-ledger-report.plx b/contrib/non-profit-audit-reports/general-ledger-report.plx index 3c9ec74d..a464053b 100755 --- a/contrib/non-profit-audit-reports/general-ledger-report.plx +++ b/contrib/non-profit-audit-reports/general-ledger-report.plx @@ -86,35 +86,40 @@ print CHART_OUTPUT "\"BEGINNING:\",\"$formattedBeginDate\","; print CHART_OUTPUT "\"ENDING:\",\"$formattedEndDate\"\n"; sub preferredAccountSorting ($$) { - if ($_[0] =~ /^Assets/) { + if ($_[0] =~ /^Assets/ and $_[1] !~ /^Assets/) { return -1; - } elsif ($_[1] =~ /^Assets/) { + } elsif ($_[1] =~ /^Assets/ and $_[0] !~ /^Assets/) { return 1; - } elsif ($_[0] =~ /^Liabilities/ and $_[1] !~ /^Assets/) { + } elsif ($_[0] =~ /^Liabilities/ and $_[1] !~ /^(Assets|Liabilities)/) { return -1; - } elsif ($_[1] =~ /^Liabilities/ and $_[0] !~ /^Assets/) { + } elsif ($_[1] =~ /^Liabilities/ and $_[0] !~ /^(Assets|Liabilities)/) { return 1; - } elsif ($_[0] =~ /^(Accrued)/ and $_[1] !~ /^(Assets|Liabilities)/) { + } elsif ($_[0] =~ /^(Accrued:[^:]+Receivable)/ and $_[1] !~ /^(Assets|Liabilities|Accrued:[^:]+Receivable)/) { return -1; - } elsif ($_[1] =~ /^(Accrued)/ and $_[0] !~ /^(Assets|Liabilities)/) { + } elsif ($_[1] =~ /^(Accrued:[^:]+Receivable)/ and $_[0] !~ /^(Assets|Liabilities|Accrued:[^:]+Receivable)/) { return 1; - } elsif ($_[0] =~ /^(Unearned Income)/ and $_[1] !~ /^(Assets|Liabilities|Accrued)/) { + } elsif ($_[0] =~ /^(Accrued)/ and $_[1] !~ /^(Assets|Liabilities|Accrued)/) { return -1; - } elsif ($_[1] =~ /^(Unearned Income)/ and $_[0] !~ /^(Assets|Liabilities|Accrued)/) { + } elsif ($_[1] =~ /^(Accrued)/ and $_[0] !~ /^(Assets|Liabilities|Accrued)/) { return 1; - } elsif ($_[0] =~ /^Income/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { + } elsif ($_[0] =~ /^(Unearned Income)/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { return -1; - } elsif ($_[1] =~ /^Income/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { + } elsif ($_[1] =~ /^(Unearned Income)/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { return 1; - } elsif ($_[0] =~ /^Expense/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Expense)/) { + } elsif ($_[0] =~ /^Income/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Income)/) { return -1; - } elsif ($_[1] =~ /^Expense/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Expense)/) { + } 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 ( sort preferredAccountSorting @accounts) { print CHART_OUTPUT "\"$acct\"\n"; diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx index 8e9339a5..17999c36 100755 --- a/contrib/non-profit-audit-reports/summary-reports.plx +++ b/contrib/non-profit-audit-reports/summary-reports.plx @@ -40,29 +40,33 @@ sub Commify ($) { } sub preferredAccountSorting ($$) { - if ($_[0] =~ /^Assets/) { + if ($_[0] =~ /^Assets/ and $_[1] !~ /^Assets/) { return -1; - } elsif ($_[1] =~ /^Assets/) { + } elsif ($_[1] =~ /^Assets/ and $_[0] !~ /^Assets/) { return 1; - } elsif ($_[0] =~ /^Liabilities/ and $_[1] !~ /^Assets/) { + } elsif ($_[0] =~ /^Liabilities/ and $_[1] !~ /^(Assets|Liabilities)/) { return -1; - } elsif ($_[1] =~ /^Liabilities/ and $_[0] !~ /^Assets/) { + } elsif ($_[1] =~ /^Liabilities/ and $_[0] !~ /^(Assets|Liabilities)/) { return 1; - } elsif ($_[0] =~ /^(Accrued)/ and $_[1] !~ /^(Assets|Liabilities)/) { + } elsif ($_[0] =~ /^(Accrued:[^:]+Receivable)/ and $_[1] !~ /^(Assets|Liabilities|Accrued:[^:]+Receivable)/) { return -1; - } elsif ($_[1] =~ /^(Accrued)/ and $_[0] !~ /^(Assets|Liabilities)/) { + } elsif ($_[1] =~ /^(Accrued:[^:]+Receivable)/ and $_[0] !~ /^(Assets|Liabilities|Accrued:[^:]+Receivable)/) { return 1; - } elsif ($_[0] =~ /^(Unearned Income)/ and $_[1] !~ /^(Assets|Liabilities|Accrued)/) { + } elsif ($_[0] =~ /^(Accrued)/ and $_[1] !~ /^(Assets|Liabilities|Accrued)/) { return -1; - } elsif ($_[1] =~ /^(Unearned Income)/ and $_[0] !~ /^(Assets|Liabilities|Accrued)/) { + } elsif ($_[1] =~ /^(Accrued)/ and $_[0] !~ /^(Assets|Liabilities|Accrued)/) { return 1; - } elsif ($_[0] =~ /^Income/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { + } elsif ($_[0] =~ /^(Unearned Income)/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { return -1; - } elsif ($_[1] =~ /^Income/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { + } elsif ($_[1] =~ /^(Unearned Income)/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income)/) { return 1; - } elsif ($_[0] =~ /^Expense/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Expense)/) { + } elsif ($_[0] =~ /^Income/ and $_[1] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Income)/) { return -1; - } elsif ($_[1] =~ /^Expense/ and $_[0] !~ /^(Assets|Liabilities|Accrued|Unearned Income|Expense)/) { + } 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]; -- cgit v1.2.3 From 986829b1d656611eb243b920703acc198fdf3f37 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Fri, 4 Jan 2013 10:19:16 -0500 Subject: Corrected Trial Balance report based on discussion with accountants. I believe this trial balance report will look "more natural" to accountants. --- .../non-profit-audit-reports/summary-reports.plx | 83 +++++++++++++++------- 1 file changed, 57 insertions(+), 26 deletions(-) (limited to 'contrib/non-profit-audit-reports/summary-reports.plx') diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx index 17999c36..bbe83c88 100755 --- a/contrib/non-profit-audit-reports/summary-reports.plx +++ b/contrib/non-profit-audit-reports/summary-reports.plx @@ -385,41 +385,72 @@ print STDERR "\n"; open(TRIAL, ">", "trial-balance.csv") or die "unable to open accrued.txt for writing: $!"; -@fullCommand = ($LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$', - '-e', $endDate, - '-F', '%-.80A %22.108t\n', '-s', - 'reg'); +print TRIAL "\"TRIAL BALANCE REPORT\",\"ENDING: $formattedEndDate\"\n\n", + "\"ACCOUNT\",\"BALANCE AT $formattedStartDate\",\"CHANGE DURING FY\",\"BALANCE AT $formattedEndDate\"\n\n"; -print TRIAL "\"TRIAL BALANCE REPORT\",", - "\"ENDING:\",\"$formattedEndDate\"\n\n\"ACCOUNT NAME\",\"AMOUNT\"\n\n"; +my %commands = ( + 'totalEndFY' => [ $LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$', + '-e', $endDate, '-F', '%-.80A %22.108t\n', '-s', + 'reg' ], + 'amountInYear' => [ $LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$', + '-b', $startDate, '-e', $endDate, '-F', '%-.80A %22.108t\n', + '-s', 'reg' ], + 'totalBeginFY' => [ $LEDGER_BIN, @mainLedgerOptions, '-V', '-X', '$', + '-e', $startDate, '-F', '%-.80A %22.108t\n', + '-s', 'reg' ]); -open(FILE, "-|", @fullCommand) - or die "unable to run command ledger command: @fullCommand: $!"; +my %trialBalanceData; +my %fullAccountList; -print STDERR ($VERBOSE ? "Running: @fullCommand\n" : "."); +foreach my $id (keys %commands) { + my(@command) = @{$commands{$id}}; -my $accruedTotal = $ZERO; + open(FILE, "-|", @command) + or die "unable to run command ledger command: @command: $!"; -my %trialBalances; + print STDERR ($VERBOSE ? "Running: @command\n" : "."); -foreach my $line () { - die "Unable to parse output line from second funds command: $line" - unless $line =~ /^\s*([^\$]+)\s+\$\s*([\-\d\.\,]+)/; - my($account, $amount) = ($1, $2); - $amount = ParseNumber($amount); - $account =~ s/\s+$//; - next if $account =~ /\/ and (abs($amount) <= 0.02); - next if $account =~ /^Equity:/; # Stupid auto-account made by ledger. - $trialBalances{$account} = $amount; - $accruedTotal += $amount; + foreach my $line () { + die "Unable to parse output line from trial balance $id command: $line" + unless $line =~ /^\s*([^\$]+)\s+\$\s*([\-\d\.\,]+)/; + my($account, $amount) = ($1, $2); + $amount = ParseNumber($amount); + $account =~ s/\s+$//; + next if $account =~ /\/ and (abs($amount) <= 0.02); + next if $account =~ /^Equity:/; # Stupid auto-account made by ledger. + $trialBalanceData{$id}{$account} = $amount; + $fullAccountList{$account} = $id; + } + close FILE; + die "unable to run trial balance ledger command, @command: $!" unless ($? == 0); } -close FILE; -die "unable to run trial balance ledger command: $!" unless ($? == 0); -foreach my $account (sort preferredAccountSorting keys %trialBalances) { - print TRIAL "\"$account\",\"\$$trialBalances{$account}\"\n"; -} +my $curOn = 'Assets'; +foreach my $account (sort preferredAccountSorting keys %fullAccountList) { + # Blank lines right + if ($account !~ /^$curOn/) { + print TRIAL "\n"; + $curOn = $account; + $curOn =~ s/^([^:]+):.*$/$1/; + print "CurOn now: $curOn\n"; + } + if ($account =~ /^Assets|Liabilities|Accrued|Unearned Income/) { + foreach my $id (qw/totalBeginFY totalEndFY amountInYear/) { + $trialBalanceData{$id}{$account} = $ZERO + unless defined $trialBalanceData{$id}{$account}; + } + print TRIAL "\"$account\",\"\$$trialBalanceData{totalBeginFY}{$account}\",", + "\"\$$trialBalanceData{amountInYear}{$account}\",\"\$$trialBalanceData{totalEndFY}{$account}\"\n" + unless $trialBalanceData{totalBeginFY}{$account} == $ZERO and + $trialBalanceData{amountInYear}{$account} == $ZERO and + $trialBalanceData{totalEndFY}{$account} == $ZERO; + } else { + print TRIAL "\"$account\",\"\",\"\$$trialBalanceData{amountInYear}{$account}\",\"\"\n" + if defined $trialBalanceData{amountInYear}{$account} and + $trialBalanceData{amountInYear}{$account} != $ZERO; + } +} close TRIAL; die "unable to write trial-balance.csv: $!" unless ($? == 0); -- cgit v1.2.3 From 6d98bc58ae555d5d4487e7ee90c6ce8b2a49cbe6 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Sun, 6 Jan 2013 19:28:46 -0500 Subject: Correct sorting of output in trial balance report. --- contrib/non-profit-audit-reports/summary-reports.plx | 9 ++++++--- 1 file changed, 6 insertions(+), 3 deletions(-) (limited to 'contrib/non-profit-audit-reports/summary-reports.plx') diff --git a/contrib/non-profit-audit-reports/summary-reports.plx b/contrib/non-profit-audit-reports/summary-reports.plx index bbe83c88..e9e1a3b8 100755 --- a/contrib/non-profit-audit-reports/summary-reports.plx +++ b/contrib/non-profit-audit-reports/summary-reports.plx @@ -430,10 +430,13 @@ my $curOn = 'Assets'; foreach my $account (sort preferredAccountSorting keys %fullAccountList) { # Blank lines right if ($account !~ /^$curOn/) { - print TRIAL "\n"; + print TRIAL "pagebreak\n"; $curOn = $account; - $curOn =~ s/^([^:]+):.*$/$1/; - print "CurOn now: $curOn\n"; + if ($curOn =~ /(Accrued:[^:]+):.*$/) { + $curOn = $1; + } else { + $curOn =~ s/^([^:]+):.*$/$1/; + } } if ($account =~ /^Assets|Liabilities|Accrued|Unearned Income/) { foreach my $id (qw/totalBeginFY totalEndFY amountInYear/) { -- cgit v1.2.3