summaryrefslogtreecommitdiff
path: root/lib
diff options
context:
space:
mode:
Diffstat (limited to 'lib')
-rwxr-xr-xlib/Travelynx.pm281
1 files changed, 127 insertions, 154 deletions
diff --git a/lib/Travelynx.pm b/lib/Travelynx.pm
index da2d76b..91d2bef 100755
--- a/lib/Travelynx.pm
+++ b/lib/Travelynx.pm
@@ -165,90 +165,6 @@ sub startup {
}
);
$self->attr(
- get_all_actions_query => sub {
- my ($self) = @_;
-
- return $self->app->dbh->prepare(
- qq{
- select user_actions.id, action_id, extract(epoch from action_time),
- stations.ds100, stations.name,
- train_type, train_line, train_no, train_id,
- extract(epoch from sched_time), extract(epoch from real_time),
- route, messages, edited
- from user_actions
- left outer join stations on station_id = stations.id
- where user_id = ?
- order by action_time desc
- }
- );
- }
- );
- $self->attr(
- get_last_actions_query => sub {
- my ($self) = @_;
-
- return $self->app->dbh->prepare(
- qq{
- select user_actions.id, action_id, extract(epoch from action_time),
- stations.ds100, stations.name,
- train_type, train_line, train_no, train_id,
- extract(epoch from sched_time), extract(epoch from real_time),
- route, messages, edited
- from user_actions
- left outer join stations on station_id = stations.id
- where user_id = ?
- order by action_time desc
- limit 10
- }
- );
- }
- );
- $self->attr(
- get_interval_actions_query => sub {
- my ($self) = @_;
-
- # Note: Selecting on real_time would be more intuitive, but is not
- # possible at the moment -- non-realtime checkouts
- # lack both sched_time and real_time.
- return $self->app->dbh->prepare(
- qq{
- select user_actions.id, action_id, extract(epoch from action_time),
- stations.ds100, stations.name,
- train_type, train_line, train_no, train_id,
- extract(epoch from sched_time), extract(epoch from real_time),
- route, messages, edited
- from user_actions
- left outer join stations on station_id = stations.id
- where user_id = ?
- and action_time >= to_timestamp(?)
- and action_time < to_timestamp(?)
- order by action_time desc
- }
- );
- }
- );
- $self->attr(
- get_journey_actions_query => sub {
- my ($self) = @_;
-
- return $self->app->dbh->prepare(
- qq{
- select user_actions.id, action_id, extract(epoch from action_time),
- stations.ds100, stations.name,
- train_type, train_line, train_no, train_id,
- extract(epoch from sched_time), extract(epoch from real_time),
- route, messages, edited
- from user_actions
- left outer join stations on station_id = stations.id
- where user_id = ?
- and user_actions.id <= ?
- order by action_time desc
- limit 2
- }
- );
- }
- );
- $self->attr(
get_api_tokens_query => sub {
my ($self) = @_;
@@ -1213,14 +1129,31 @@ sub startup {
my ( $self, %opt ) = @_;
my $uid = $opt{uid} || $self->current_user->{id};
- my $query = $self->app->get_all_actions_query;
+
+ my $selection = qq{
+ user_actions.id as action_log_id, action_id,
+ extract(epoch from action_time) as action_time_ts,
+ stations.ds100 as ds100, stations.name as name,
+ train_type, train_line, train_no, train_id,
+ extract(epoch from sched_time) as sched_time_ts,
+ extract(epoch from real_time) as real_time_ts,
+ route, messages, edited
+ };
+ $selection =~ tr{\n}{}d;
+ my %where = ( user_id => $uid );
+ my %order = (
+ order_by => {
+ -desc => 'action_time',
+ }
+ );
+
if ( $opt{limit} ) {
- $query = $self->app->get_last_actions_query;
+ $order{limit} = 10;
}
if ( $opt{checkout_id} ) {
- $query = $self->app->get_journey_actions_query;
- $query->execute( $uid, $opt{checkout_id} );
+ $where{action_log_id} = { '<=', $opt{checkout_id} };
+ $order{limit} = 2;
}
elsif ( $opt{after} and $opt{before} ) {
@@ -1243,16 +1176,14 @@ sub startup {
# This works under the assumption that there are no DB trains whose
# journey takes more than 24 hours. If this no longer holds,
# please adjust the intervals accordingly.
- $query = $self->app->get_interval_actions_query;
- $query->execute(
- $uid,
- $opt{after}->clone->subtract( days => 1 )->epoch,
- $opt{before}->clone->add( days => 1 )->epoch
- );
- }
- else {
- $query->execute($uid);
+ $where{action_time} = {
+ -between => [
+ $opt{after}->clone->subtract( days => 1 ),
+ $opt{before}->clone->add( days => 1 )
+ ]
+ };
}
+
my @match_actions = (
$self->app->action_type->{checkout},
$self->app->action_type->{checkin}
@@ -1267,61 +1198,71 @@ sub startup {
my @travels;
my $prev_action = 0;
- while ( my @row = $query->fetchrow_array ) {
- my (
- $action_id, $action, $raw_ts,
- $ds100, $name, $train_type,
- $train_line, $train_no, $train_id,
- $raw_sched_ts, $raw_real_ts, $raw_route,
- $raw_messages, $edited
- ) = @row;
+ my $res = $self->pg->db->select(
+ [
+ 'user_actions',
+ [
+ -left => 'stations',
+ id => 'station_id'
+ ]
+ ],
+ $selection,
+ \%where,
+ \%order
+ );
+
+ for my $entry ( $res->hashes->each ) {
- if ( $action == $match_actions[0]
+ if ( $entry->{action_id} == $match_actions[0]
or ( $opt{checkout_id} and not @travels ) )
{
push(
@travels,
{
- ids => [ undef, $action_id ],
- to_name => $name,
- sched_arrival => epoch_to_dt($raw_sched_ts),
- rt_arrival => epoch_to_dt($raw_real_ts),
- checkout => epoch_to_dt($raw_ts),
- type => $train_type,
- line => $train_line,
- no => $train_no,
- messages => $raw_messages
- ? [ split( qr{[|]}, $raw_messages ) ]
+ ids => [ undef, $entry->{action_log_id} ],
+ to_name => $entry->{name},
+ sched_arrival =>
+ epoch_to_dt( $entry->{sched_time_ts} ),
+ rt_arrival => epoch_to_dt( $entry->{real_time_ts} ),
+ checkout => epoch_to_dt( $entry->{action_time_ts} ),
+ type => $entry->{train_type},
+ line => $entry->{train_line},
+ no => $entry->{train_no},
+ messages => $entry->{messages}
+ ? [ split( qr{[|]}, $entry->{messages} ) ]
: undef,
- route => $raw_route
- ? [ split( qr{[|]}, $raw_route ) ]
+ route => $entry->{route}
+ ? [ split( qr{[|]}, $entry->{route} ) ]
: undef,
completed => 0,
- edited => $edited << 8,
+ edited => $entry->{edited} << 8,
}
);
}
elsif (
(
- $action == $match_actions[1]
+ $entry->{action_id} == $match_actions[1]
and $prev_action == $match_actions[0]
)
or $opt{checkout_id}
)
{
my $ref = $travels[-1];
- $ref->{ids}->[0] = $action_id;
- $ref->{from_name} = $name;
- $ref->{completed} = 1;
- $ref->{sched_departure} = epoch_to_dt($raw_sched_ts);
- $ref->{rt_departure} = epoch_to_dt($raw_real_ts);
- $ref->{checkin} = epoch_to_dt($raw_ts);
- $ref->{type} //= $train_type;
- $ref->{line} //= $train_line;
- $ref->{no} //= $train_no;
- $ref->{messages} //= [ split( qr{[|]}, $raw_messages ) ];
- $ref->{route} //= [ split( qr{[|]}, $raw_route ) ];
- $ref->{edited} |= $edited;
+ $ref->{ids}->[0] = $entry->{action_log_id};
+ $ref->{from_name} = $entry->{name};
+ $ref->{completed} = 1;
+ $ref->{sched_departure}
+ = epoch_to_dt( $entry->{sched_time_ts} );
+ $ref->{rt_departure}
+ = epoch_to_dt( $entry->{real_time_ts} );
+ $ref->{checkin} = epoch_to_dt( $entry->{action_time_ts} );
+ $ref->{type} //= $entry->{train_type};
+ $ref->{line} //= $entry->{train_line};
+ $ref->{no} //= $entry->{train_no};
+ $ref->{messages}
+ //= [ split( qr{[|]}, $entry->{messages} ) ];
+ $ref->{route} //= [ split( qr{[|]}, $entry->{route} ) ];
+ $ref->{edited} |= $entry->{edited};
if ( $opt{verbose} ) {
my @parsed_messages;
@@ -1363,13 +1304,13 @@ sub startup {
: -1;
}
if ( $opt{checkout_id}
- and $action
+ and $entry->{action_id}
== $self->app->action_type->{cancelled_from} )
{
$ref->{cancelled} = 1;
}
}
- $prev_action = $action;
+ $prev_action = $entry->{action_id};
}
if ( $opt{before} and $opt{after} ) {
@@ -1411,19 +1352,47 @@ sub startup {
my ( $self, $uid ) = @_;
$uid //= $self->current_user->{id};
- $self->app->get_last_actions_query->execute($uid);
- my $rows = $self->app->get_last_actions_query->fetchall_arrayref;
- if ( @{$rows} ) {
- my $now = DateTime->now( time_zone => 'Europe/Berlin' );
+ my $selection = qq{
+ user_actions.id as action_log_id, action_id,
+ extract(epoch from action_time) as action_time_ts,
+ stations.ds100 as ds100, stations.name as name,
+ train_type, train_line, train_no, train_id,
+ extract(epoch from sched_time) as sched_time_ts,
+ extract(epoch from real_time) as real_time_ts,
+ route
+ };
+ $selection =~ tr{\n}{}d;
- my @cols = @{ $rows->[0] };
+ my $res = $self->pg->db->select(
+ [
+ 'user_actions',
+ [
+ -left => 'stations',
+ id => 'station_id'
+ ]
+ ],
+ $selection,
+ {
+ user_id => $uid,
+ },
+ {
+ order_by => {
+ -desc => 'action_time',
+ },
+ limit => 1,
+ }
+ );
+ my $status = $res->hash;
- my $action_ts = epoch_to_dt( $cols[2] );
- my $sched_ts = epoch_to_dt( $cols[9] );
- my $real_ts = epoch_to_dt( $cols[10] );
- my $checkin_station_name = $cols[4];
- my @route = split( qr{[|]}, $cols[11] // q{} );
+ if ($status) {
+ my $now = DateTime->now( time_zone => 'Europe/Berlin' );
+
+ my $action_ts = epoch_to_dt( $status->{action_time_ts} );
+ my $sched_ts = epoch_to_dt( $status->{sched_time_ts} );
+ my $real_ts = epoch_to_dt( $status->{real_time_ts} );
+ my $checkin_station_name = $status->{name};
+ my @route = split( qr{[|]}, $status->{route} // q{} );
my @route_after;
my $is_after = 0;
for my $station (@route) {
@@ -1436,21 +1405,25 @@ sub startup {
}
}
return {
- checked_in =>
- ( $cols[1] == $self->app->action_type->{checkin} ),
- cancelled =>
- ( $cols[1] == $self->app->action_type->{cancelled_from} ),
+ checked_in => (
+ $status->{action_id}
+ == $self->app->action_type->{checkin}
+ ),
+ cancelled => (
+ $status->{action_id}
+ == $self->app->action_type->{cancelled_from}
+ ),
timestamp => $action_ts,
timestamp_delta => $now->epoch - $action_ts->epoch,
- action_id => $cols[0],
+ action_id => $status->{action_log_id},
sched_ts => $sched_ts,
real_ts => $real_ts,
- station_ds100 => $cols[3],
+ station_ds100 => $status->{ds100},
station_name => $checkin_station_name,
- train_type => $cols[5],
- train_line => $cols[6],
- train_no => $cols[7],
- train_id => $cols[8],
+ train_type => $status->{train_type},
+ train_line => $status->{train_line},
+ train_no => $status->{train_no},
+ train_id => $status->{train_id},
route => \@route,
route_after => \@route_after,
};
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-01 16:33:37 +0000