script to save oyster trips to a db

2 files changed, 164 insertions, 0 deletions

diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..68fa6bf
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,2 @@
+*~
+*.db
diff --git a/oyster b/oyster
new file mode 100644
index 0000000..1bcf986
--- /dev/null
+++ b/oyster
@@ -0,0 +1,162 @@
+#!/usr/bin/env perl
+use strict;
+use warnings;
+use WWW::Mechanize;
+use WWW::Mechanize::TreeBuilder;
+use URI;
+use DBI;
+use Text::CSV_XS;
+use Path::Class;
+use Getopt::Long::Descriptive;
+use Try::Tiny;
+use DateTime;
+use DateTime::Format::Strptime;
+use open ':std',':locale';
+
+my $default_db_path = file(__FILE__)->parent->file('oyster.db')->stringify;;
+
+my ($opt,$usage) = describe_options(
+    '%c %o',
+    [ 'database|d=s', 'path to the database to use',
+      { default => $default_db_path } ],
+    [ 'username|u=s', 'username to log in as (updates value in db)' ],
+    [ 'password|p=s', 'password to log in with (updates value in db)' ],
+    [],
+    [ 'verbose|v', 'print progess' ],
+    [ 'help|h', 'print help and exit' ],
+);
+if ($opt->help) {
+    print $usage->text;
+    exit;
+}
+
+sub progress {
+    return unless $opt->verbose;
+    my $str=shift;
+    printf "$str\n",@_;
+}
+
+my $dbh = DBI->connect(
+    'dbi:SQLite:dbname='.$opt->database,
+    '','',
+    { RaiseError => 1, PrintError => 0, AutoCommit => 1 },
+);
+
+my ($username,$password);
+try {
+    progress('getting credentials');
+    ($username,$password) = $dbh->selectrow_array(
+        'select username,password from credentials'
+    );
+}
+catch {
+    progress('new db, creating');
+    # no table, create schema
+    $dbh->do(q{create table credentials (
+      username text unique not null,
+      password text not null
+    )});
+    $dbh->do(q{create table journeys (
+      start_ts integer not null,
+      stop_ts  integer not null,
+      description text not null,
+      charge real,
+      credit real,
+      balance real,
+      note text,
+      unique (start_ts,stop_ts) on conflict replace
+    )});
+    $dbh->do(
+        q{insert into credentials (username,password) values (?,?)},
+        {},
+        $opt->username,
+        $opt->password,
+    );
+};
+if ($opt->username or $opt->password) {
+    progress('updating credentials from command line');
+    $username = $opt->username if $opt->username;
+    $password = $opt->password if $opt->password;
+    $dbh->do(q{update credentials set username=?, password=?},
+             {},
+             $username,$password);
+}
+
+my $mech = WWW::Mechanize->new;
+WWW::Mechanize::TreeBuilder->meta->apply(
+    $mech,
+    tree_class => 'HTML::TreeBuilder::XPath',
+);
+$mech->agent_alias('Linux Mozilla');
+
+progress('logging in');
+$mech->get('https://oyster.tfl.gov.uk/oyster/entry.do');
+$mech->submit_form(
+    form_name => 'sign-in',
+    fields => {
+        j_username => $username,
+        j_password => $password,
+    },
+);
+
+$mech->uri eq 'https://oyster.tfl.gov.uk/oyster/loggedin.do'
+    or die "login failed\n".$mech->content;
+progress('getting journeys history');
+$mech->follow_link(text_regex => qr{journey history}i);
+
+my ($input_button) = $mech->findnodes(
+    q{//form[@name='jhDownloadForm']//input[@type='submit']}
+);
+
+my $js=$input_button->findvalue('@onclick');
+my (undef,$url) = ($js=~m{action=(["'])(\S+?)\1});
+$url=URI->new_abs($url,$mech->uri);
+
+progress('downloading CSV');
+my $res = $mech->post($url, {});
+die "CSV download failed\n".$res->decoded_content
+    if !$res->is_success;
+
+my $date_parser = DateTime::Format::Strptime->new(
+    pattern => '%d-%b-%Y %H:%M',
+    locale => 'en_GB',
+    time_zone => 'Europe/London',
+    on_error => 'croak',
+);
+my $csv=Text::CSV_XS->new({binary=>1});
+my $csv_text=$res->decoded_content;
+open my $fh,'<',\$csv_text;
+$csv->column_names($csv->getline($fh));
+
+progress('parsing CSV');
+while (my $row = $csv->getline_hr($fh)) {
+    progress('got a row (for %s, %s - %s)',
+             $row->{Date},
+             $row->{'Start Time'},
+             $row->{'End Time'},
+         );
+    # bus journeys don't have a end time
+    $row->{'End Time'} ||= $row->{'Start Time'};
+    my $start_dt = $date_parser->parse_datetime(
+        $row->{Date}.' '.$row->{'Start Time'});
+    my $stop_dt = $date_parser->parse_datetime(
+        $row->{Date}.' '.$row->{'End Time'});
+    if ($stop_dt < $start_dt) {
+        # if it ends after midnight, it looks like we went back in time
+        $stop_dt->add(days=>1);
+    }
+    # a duplicate row will get overwritten
+    $dbh->do(q{insert into journeys(start_ts,stop_ts,description,charge,credit,balance,note) values (?,?,?,?,?,?,?)},
+             {},
+             $start_dt->epoch,
+             $stop_dt->epoch,
+             $row->{'Journey/Action'},
+             $row->{Charge},
+             $row->{Credit},
+             $row->{Balance},
+             $row->{Note},
+         );
+}
+progress('done');
+
+exit 0;