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');
$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(ssl_opts=>{verify_hostname=>0});
WWW::Mechanize::TreeBuilder->meta->apply(
$mech,
tree_class => 'HTML::TreeBuilder::XPath',
);
$mech->agent_alias('Linux Mozilla');
progress('logging in');
$mech->get('https://account.tfl.gov.uk/oyster/login');
$mech->submit_form(
form_name => 'sign-in',
fields => {
j_username => $username,
UserName => $username,
j_password => $password,
Password => $password,
},
);
$mech->uri =~ m{^https://oyster.tfl.gov.uk/oyster/oyster/selectCard}
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']//a[@class=~/button_dwld/]}
);
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;
my $headers=[];
while (defined($headers) and @$headers<2 ) {
$headers = $csv->getline($fh);
}
die "Could not find any data in the CSV"
unless defined $headers;
$csv->column_names($headers);
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'},
);
$row->{'End Time'} ||= $row->{'Start Time'};
$row->{'Start Time'} ||= $row->{'End 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) {
$stop_dt->add(days=>1);
}
$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;
__END__
