Perl CWMoney DB hack

I usually use CWMoney to keep track of where I spend my money and to help to stay on budget. However for the last year I have been rather lazy and haven’t been entering my financial data. I keep putting it off because it is tedious.

I had previously asked the developers to add a import feature so I could add *.csv files to the app but nothing happened. I decided to give it a go in Perl.

I used SQLite Browser to help me visualize changes and understand the structure of the database.

The app turns out to be quite simple. The transactions are stored in the “rec_table” and the categories of transactions are stored in the “kind” and “kinds” table.

Screenshot from 2018-10-11 04-57-59.png

I first used SQLite Browser to craft a SQL query to add an entry and checked that it was picked up by the program. After crashing it a few times I figured out which fields are necessary. I then used DBI::SQLite to drive the database.

Using small iterations I was able to get to a point where %80 of my transaction were filtered and entered into the relevant categories. These transactions come from multiple banks with different account types.

I was thinking about adding a dynamic row picker to make the program compatible with any CSV file type, but for now it does it’s job. I couldn’t imagine how many hours it would take to enter those transactions by hand.

Here is the code:

use strict;
use warnings;
use 5.26.01;
use Switch;
use DateTime;
use DBI;
my $dbh = DBI->connect("dbi:SQLite:dbname=$ARGV[0]","","");
&main;
sub main {
open(my $FH, "<", $ARGV[1]) or die "Please specifiy db!";
while (<$FH>) {
my @string = split (/,/, $_);
my ($money, $date, $kind, $kinds, $account, $desc, $created, $type, $txtype);
say scalar @string;
# type 1
if (scalar @string > 6) {
$money = $string[1];
$date = convertdate($string[0]);
$account = 1;
$desc = $string[5];
$txtype = $string[4] . $string[5];
$created = time();
#type 2
} elsif (scalar @string == 4) {
$money = $string[1];
$date = convertdate($string[0]);
$account = 1;
$desc = $string[2];
$txtype = $desc;
$created = time();
}
## If a CREDIT (not internal transaction)
if ($txtype =~ m/((Credit|DEPOSIT|INTEREST PAID|REFUND|Refund|MISCELLANEOUS CREDIT|TRANSFER CREDIT|INTER-BANK CREDIT|SALARY))/
&& ($txtype !~ m/Linked/)) {
$type = 2;
$money =~ s/[^0-9.]//g;
$kind = 1;
$kinds = 1;
switch ($txtype) {
case /SALARY/ { $kinds = 1 }
}
insert($money, $date, $kind, $kinds, $account, $desc, $created, $type);
say "Credit $money, $date, $kind, $kinds, $account, $desc, $created, $type";
## If a DEBIT (not an internal transaction)
} elsif ( ($money =~ /-/ ) && ($txtype !~ m/(Linked|OTHER ACCOUNT|rpy)/) ) {
$type = 1;
$money =~ s/[^0-9.]//g;
$kind = 10;
$kinds = 39;
switch ($txtype) {
case /(WOOLWORTHS|COLES|IGA)/ { $kind = 1; $kinds = 3; }
case /(KFC|MCDONALD)/ { $kind = 1; $kinds = 5; }
case /(PAYPAL)/ { $kind = 7; $kinds = 7; $kinds = 42; }
case /(DIGITALOCEAN|AMAYSIM)/ { $kind = 4; $kinds = 15; }
case /(ALDI)/ { $kind = 4; $kinds = 14; }
case /(ALLIANZ)/ { $kind = 9; $kinds = 34 }
case /(LOAN REPAYMENT)/ { $kind = 9; $kinds = 34 }
case /(WW PETROL)/ { $kind = 3; $kinds = 11 }
}
insert($money, $date, $kind, $kinds, $account, $desc, $created, $type);
say "Debit $money, $date, $kind, $kinds, $account, $desc, $created, $type";
} else {
say "Aborting line: $_";
}
}
}
sub insert {
#my ($money, $date, $kind, $kinds, $account, $desc, $created, $type) = @_;
my $insert = $dbh->prepare('INSERT INTO rec_table (
i_money, i_date, i_kind, i_kinds, i_account, i_remark,
i_item, i_create, i_type, i_photo, i_invoice, i_rev1, i_gps
) values ( ?, ?, ?, ?, ?, ?, 1, ?, ?, "", ",", "0", "0,0")');
$insert->bind_param(1, $_[0]);
$insert->bind_param(2, $_[1]);
$insert->bind_param(3, $_[2]);
$insert->bind_param(4, $_[3]);
$insert->bind_param(5, $_[4]);
$insert->bind_param(6, $_[5]);
$insert->bind_param(7, $_[6]);
$insert->bind_param(8, $_[7]);
$insert->execute();
}
# convert date to epoch time
sub convertdate {
my $date = shift;
my (@dt, $et);
if ($date =~ m/\//) {
@dt = split (/\//, $date);
$et = DateTime->new(
year => $dt[2],
month => $dt[1],
day => $dt[0]);
} else {
@dt = split (/ /, $date);
my %months = ( "Jan" => "01",
"Feb" => "02",
"Mar" => "03",
"Apr" => "04",
"May" => "05",
"Jun" => "06",
"Jul" => "07",
"Aug" => "08",
"Sep" => "09",
"Oct" => "10",
"Nov" => "11",
"Dec" => "12" );
$et = DateTime->new(
year => "20$dt[2]",
month => $months{$dt[1]},
day => $dt[0]);
}
my $epoch = $et->epoch;
return $epoch;
}
sub usage {
say "Usage: mod.pl db.dbi transaction.csv";
}
view raw mod_cwmoney.pl hosted with ❤ by GitHub

First web app

I reverted to my language of choice – Perl to write my first web app and back end service.

I used Mojolicous Lite to generate the page and handle JSON get requests. On the backend I used sqlite and Perl to process and monitor the dnsmasq log file. The Perl hypnotoad web server sits behind a Nginx web proxy for deployment to the internet.

The end result is a simple web app that lets users of my Adblocker to see statistics for the service.

Source code is here.

See the web app here.

Mojolicious Lite + CanvasJS

My app Block Ads, Tracking and Malware has been downloaded over 150 times from the Galaxy Apps store in over 40 countries. To keep track of usage data I created some graphs using CanvasJS and the Mojolicious Lite web framework.

There is a Perl log scraper that extracts data from the log file for the date specified in the URL.

This data is sent in JSON format to the graph where it is sorted and then plotted. This allows for dynamic interactive graphs that can zoom and pan.

I use a VPN to access the server on my intranet.

If I had more time I would like to use SQLite to store the statistics continuously and increase performance. Instead on analysing the whole file at once it would be better to monitor the log file and generate the statistics on the fly.

Even better – rewrite the DNS server in Go and include this functionality from the beginning. Logging to disk won’t scale very well as my service grows.

I currently have over 1GB of log files just for this week…

Anyway this works for now.

The source code (it was hacked together at 1AM): here

web front end using Mojolicious Lite

I originally trying to get pi-hole running on FreeBSD but got frustrated part way through porting it. It relies heavily on bash, PHP, and Javascript. Technologies I am not terrible interested in.

The whole program is rather simple it just downloads files ad lists from websites and loads them into dnsmasq. This effectively disables access to the domain by routing traffic to another domain (local host or the dnsmasq server on the LAN).

The most interesting part is that you can then pull statistics from the dnsmasq log file and get internet usage data or help find evidence of malware on your PCs or mobiles.

I got a bit carried away and after creating a simple dns adblocker then went on to create a web front end using Mojolicious Lite.

The website is bare bones so far but allows you to select a date to get statistics for each date:

Screenshot_2018-04-01_12-15-18.png

It currently will limit the generation of files from a query to one per hour to prevent overloading my poor router. It has reasonable error handling. I would like to add a date picker and authentication.

It has so far helped me to identify data collection from Telstra:

2018-02-2802-bd.png

If you look at the most blocked domain for the 28th of March it’s ‘secure-dcr.imrworldwide.com’.

Following hard coding the DNS servers into each device to get more specific data regarding who was performing DNS queries I saw the following line in a “tail -f ” of my logfile:

Apr 1 01:48:05 dnsmasq[6259]: 97661 192.168.0.8/49536 /usr/local/etc/dnsmasq.d/blocklist.txt secure-dcr.imrworldwide.com is 192.168.0.2

This was from my mobile phone! What the heck.

After doing some research I found out mention that Telstra use this domain for data collection. I still had the “Telstra 24/7” app installed on my mobile. After removing it the queries stopped!

I feel even more paranoid than ever.

Lenovo 500S on FreeBSD -CURRENT, hashtree in Go, updating tracker lists using perl

Hashtree in Go

So I’ve been mildly addicted to Go – and I know why. It tricks you into thinking it is a scripting language by having awesome libraries but it is really more like C with some super cool features (that are easy to use).

I wrote my program hashtree in Perl and bootstrapped s3cmd to do the uploading. The program resembled something made with glue and masking tape. But it works, it’s fast, it’s cross platform and I use it every day.

I rewrote it in Go and it has less than 30% of the features – but it can now be compiled into a single binary – this is possible with Perl as well but I couldn’t get any Perl S3 libraries to work.

The reason why it has less features is that some things are painful do to in Go and are very easy and fun to do in Perl. Also I wanted this to be scriptable and work more like a traditional Unix tool – do one thing and do it well.

https://github.com/wilyarti/hashtree is where you can find both versions.

I still need to add file corruption checking and better error reporting in the Go version – but for now it is complete and has the following features:

  • Data deduplication
  • Filesystem snapshots
  • Client side 256bit AES encryption
  • Support for any S3 compatible server
hashtree snapshots and upload files to a S3 server
hashseed deploys a snapshot
hashlist finds all snapshots on server

Screenshot_2018-03-04_06-00-23.png

Perl and tracker lists

I was having problems with transmission contacting UDP trackers so I wrote this perl script to insert trackers:

while () {
    unless ($_ eq "\n") {
        $t = $_;
        for (1 .. 4) {
                `transmission-remote -t $_ -td $t`;
        }
    }
}

Just run the script and point it at a trackers.txt for or pipe a list trackers into it!

Lenovo 500S

So the last bit of news is I did all of this on FreeBSD -CURRENT on a Lenovo 500S.
Thanks to the guys at drm-next, the Linux Intel i915 drivers work a treat. The iwm driver support 5G wireless and everything else just works.

The transmission from Ubuntu/Fedora has been seamless, and I get less screen tearing with the FreeBSD drivers.

I hope to start contributing to FreeBSD as soon as I get some free time. I’ve been flat out so far.

It is good to be finally running FreeBSD on my main machine. I attempted getting it running for over a year and only finally got it working with the latest -CURRENT. I am now tracking CURRENT and upgrading the kernel once a week.

I use beadm to create snapshots of the working system in case the new kernels refuse to work. ZFS is fantastic!

ntop and proc

So I have been a bit bad and have written a Linux specific network monitor that use /proc. In my defense I use Linux on my laptop.

I will rebuild it later to support *BSD. Maybe someone knows the API’s I can use for the BSD network stack?

Anyhow it’s a curses based program I want to make as useful as possible for when I connect to my servers remotely.

ntop

It’s not really serious. Just for fun.

Data De-duplication, file diff-ing and S3 style object storage using Digital Ocean Spaces

I have finally added Amazon S3 storage support to my program (SCRIPT??) hashtree.

After messing around with broken Perl libraries I finally broke down and wrote two Python scripts that upload files to my specified bucket and have the access keys hard coded into the source code.

The result is I now have data de-duplication, remote backups, (potential versioning – if I can be bothered to add it) and reduplication across all my workstations.

I was using Amazon S3 but they are too expensive. I have a FreeBSD droplet with Digital Ocean and chose to use their “Spaces” which has S3 compatible API.

I get 250GB of storage a month for $5. That’s a good deal.

Feel free to use my program. It’s BSD licensed and available here:

https://github.com/wilyarti/hashtree

Also if some one could get the S3 stuff working in Perl that would be great!

Screenshot from 2017-12-08 17-08-00Screenshot from 2017-12-08 17-08-26Screenshot from 2017-12-08 17-45-09Screenshot from 2017-12-08 17-46-08