Calculates month-over-month and year-over-year balances using ledger and SQLite

balance_sheet.pl

#!/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;
}