Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Calculates month-over-month and year-over-year balances using ledger and SQLite
#!/usr/bin/env perl
use strict;
use warnings;
use DBI;
my ($year, $month, $day) = get_date();
my @balances = (
get_balances($year, $month, $day),
get_balances($year, $month - 1, $day),
get_balances($year, $month - 2, $day),
get_balances($year - 1, $month, $day),
get_balances($year - 2, $month, $day),
);
my $dbh = get_dbh();
my @months = insert_balances($dbh, \@balances);
my $query = sql_query(@months);
my $rows = $dbh->selectall_arrayref($query, {});
print_report($rows, @months);
sub get_date
{
my (undef, undef, undef, $day, $month, $year) = localtime();
$month += 1;
$year += 1900;
return ($year, $month, $day);
}
sub get_balances
{
my ($year, $month, $day) = @_;
my $command = "ledger --no-total --flat -RV -e $year/$month/$day bal assets liabilities";
my @lines = qx{$command};
chomp @lines;
map {
$_ =~ s/^\s+//g;
my ($amount, $account) = split(/\s+/, $_, 2);
[ "${year}_${month}_${day}", $account, $amount ];
} @lines;
}
sub get_dbh
{
my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:","","");
$dbh->do("create table balances (account text, month text, amount text)");
return $dbh;
}
sub insert_balances
{
my ($dbh, $balances) = @_;
my $sth = $dbh->prepare("insert into balances (month, account, amount) values (?, ?, ?)");
my %dates;
for my $balance ( @$balances ) {
$sth->execute(@$balance);
$dates{$balance->[0]}++
}
return sort keys %dates;
}
sub sql_query
{
my @cases = map {
"case when month = '$_' then amount else 0 end as month_$_"
} @months;
return qq{
select
account,
@{[ join(",\n", map { " max(month_$_) as month_$_" } @months) ]}
from (
select
account,
@{[ join(",\n", map { " $_"} @cases) ]}
from
balances
) x
group by account order by account
};
}
sub print_report
{
my ($rows, @months) = @_;
my @widths = get_max_widths($rows);
my $first_width = shift @widths;
my $format = join(' | ', "\%-${first_width}s", map { "\%${_}s" } @widths) . "\n";
printf($format, 'Account', @months);
print(join("-+-", map { '-' x $_ } ( $first_width, @widths )) . "\n");
for my $row ( @$rows ) {
printf($format, @$row);
}
}
sub get_max_widths
{
my $rows = shift;
my @widths;
for my $row ( @$rows ) {
for my $i ( 0..$#$row ) {
if (!defined $widths[$i] || $widths[$i] < length("$row->[$i]")) {
$widths[$i] = length("$row->[$i]");
}
}
}
return @widths;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment