diff options
Diffstat (limited to 'lib/Travelynx/Model')
| -rw-r--r-- | lib/Travelynx/Model/InTransit.pm | 1109 | ||||
| -rwxr-xr-x | lib/Travelynx/Model/Journeys.pm | 591 | ||||
| -rw-r--r-- | lib/Travelynx/Model/Stations.pm | 468 | ||||
| -rw-r--r-- | lib/Travelynx/Model/Traewelling.pm | 1 | ||||
| -rw-r--r-- | lib/Travelynx/Model/Users.pm | 104 |
5 files changed, 1957 insertions, 316 deletions
diff --git a/lib/Travelynx/Model/InTransit.pm b/lib/Travelynx/Model/InTransit.pm index aec193f..d0a7c2b 100644 --- a/lib/Travelynx/Model/InTransit.pm +++ b/lib/Travelynx/Model/InTransit.pm @@ -1,6 +1,7 @@ package Travelynx::Model::InTransit; -# Copyright (C) 2020-2023 Birte Kristina Friesel +# Copyright (C) 2020-2025 Birte Kristina Friesel +# Copyright (C) 2025 networkException <git@nwex.de> # # SPDX-License-Identifier: AGPL-3.0-or-later @@ -9,14 +10,16 @@ use warnings; use 5.020; use DateTime; +use GIS::Distance; use JSON; my %visibility_itoa = ( - 100 => 'public', - 80 => 'travelynx', - 60 => 'followers', - 30 => 'unlisted', - 10 => 'private', + 100 => 'public', + 80 => 'travelynx', + 60 => 'followers', + 30 => 'unlisted', + 10 => 'private', + default => 'default', ); my %visibility_atoi = ( @@ -30,7 +33,7 @@ my %visibility_atoi = ( sub _epoch { my ($dt) = @_; - return $dt ? $dt->epoch : 0; + return $dt ? $dt->epoch : undef; } sub epoch_to_dt { @@ -47,6 +50,16 @@ sub epoch_to_dt { ); } +sub epoch_or_dt_to_dt { + my ($input) = @_; + + if ( ref($input) eq 'DateTime' ) { + return $input; + } + + return epoch_to_dt($input); +} + sub new { my ( $class, %opt ) = @_; @@ -83,39 +96,184 @@ sub add { my $uid = $opt{uid}; my $db = $opt{db} // $self->{pg}->db; + my $backend_id = $opt{backend_id}; my $train = $opt{train}; + my $train_suffix = $opt{train_suffix}; my $journey = $opt{journey}; my $stop = $opt{stop}; + my $stopover = $opt{stopover}; + my $manual = $opt{manual}; my $checkin_station_id = $opt{departure_eva}; my $route = $opt{route}; + my $data = $opt{data}; + my $persistent_data; my $json = JSON->new; + my $now = DateTime->now( time_zone => 'Europe/Berlin' ); if ($train) { $db->insert( 'in_transit', { user_id => $uid, - cancelled => $train->departure_is_cancelled - ? 1 + cancelled => $train->departure_is_cancelled ? 1 : 0, checkin_station_id => $checkin_station_id, - checkin_time => DateTime->now( time_zone => 'Europe/Berlin' ), - dep_platform => $train->platform, - train_type => $train->type, - train_line => $train->line_no, - train_no => $train->train_no, - train_id => $train->train_id, - sched_departure => $train->sched_departure, - real_departure => $train->departure, - route => $json->encode($route), - messages => $json->encode( + checkin_time => $now, + dep_platform => $train->platform, + train_type => $train->type, + train_line => $train->line_no, + train_no => $train->train_no, + train_id => $train->train_id, + sched_departure => $train->sched_departure, + real_departure => $train->departure, + route => $json->encode($route), + messages => $json->encode( [ map { [ $_->[0]->epoch, $_->[1] ] } $train->messages ] - ) + ), + data => $json->encode( + { + rt => $train->departure_has_realtime ? 1 + : 0, + %{ $data // {} } + } + ), + backend_id => $backend_id, + } + ); + } + elsif ( $journey + and $stop + and ref($journey) eq 'Travel::Status::DE::EFA::Trip' ) + { + my @route; + for my $j_stop ( $journey->route ) { + push( + @route, + [ + $j_stop->full_name, + $j_stop->id_num, + { + sched_arr => _epoch( $j_stop->sched_arr ), + sched_dep => _epoch( $j_stop->sched_dep ), + rt_arr => _epoch( $j_stop->rt_arr ), + rt_dep => _epoch( $j_stop->rt_dep ), + arr_delay => $j_stop->arr_delay, + dep_delay => $j_stop->dep_delay, + platform => $j_stop->platform, + efa_load => $j_stop->occupancy, + lat => $j_stop->latlon->[0], + lon => $j_stop->latlon->[1], + } + ] + ); + if ( $j_stop->is_cancelled ) { + $route[-1][2]{isCancelled} = 1; + } + } + $persistent_data->{operator} = $journey->operator; + $db->insert( + 'in_transit', + { + user_id => $uid, + cancelled => $stop->is_cancelled ? 1 : 0, + checkin_station_id => $stop->id_num, + checkin_time => $now, + dep_platform => $stop->platform, + train_type => $journey->type // q{}, + train_line => $journey->line, + train_no => $journey->number // q{}, + train_id => $opt{trip_id}, + sched_departure => $stop->sched_dep, + real_departure => $stop->rt_dep // $stop->sched_dep, + route => $json->encode( \@route ), + data => $json->encode( + { + rt => $stop->rt_dep ? 1 : 0, + %{ $data // {} } + } + ), + user_data => $json->encode($persistent_data), + backend_id => $backend_id, } ); } - elsif ( $journey and $stop ) { + elsif ( $journey + and $stop + and ref($journey) eq 'Travel::Status::DE::HAFAS::Journey' ) + { + my @route; + my $product = $journey->product_at( $stop->loc->eva ) + // $journey->product; + for my $j_stop ( $journey->route ) { + push( + @route, + [ + $j_stop->loc->name, + $j_stop->loc->eva, + { + sched_arr => _epoch( $j_stop->sched_arr ), + sched_dep => _epoch( $j_stop->sched_dep ), + rt_arr => _epoch( $j_stop->rt_arr ), + rt_dep => _epoch( $j_stop->rt_dep ), + arr_delay => $j_stop->arr_delay, + dep_delay => $j_stop->dep_delay, + platform => $j_stop->platform, + load => $j_stop->load, + lat => $j_stop->loc->lat, + lon => $j_stop->loc->lon, + } + ] + ); + if ( defined $j_stop->tz_offset ) { + $route[-1][2]{tz_offset} = $j_stop->tz_offset; + } + } + if ( scalar $journey->operators ) { + $persistent_data->{operators} = [ $journey->operators ]; + } + $db->insert( + 'in_transit', + { + user_id => $uid, + cancelled => $stop->{dep_cancelled} + ? 1 + : 0, + checkin_station_id => $stop->loc->eva, + checkin_time => $now, + dep_platform => $stop->{platform}, + train_type => $product->type // q{}, + train_line => $product->line_no, + train_no => $product->number // q{}, + train_id => $journey->id, + sched_departure => $stop->{sched_dep}, + real_departure => $stop->{rt_dep} // $stop->{sched_dep}, + route => $json->encode( \@route ), + data => $json->encode( + { + rt => $stop->{rt_dep} ? 1 : 0, + %{ $data // {} } + } + ), + user_data => $json->encode($persistent_data), + backend_id => $backend_id, + } + ); + } + elsif ( $journey + and $stop + and ref($journey) eq 'Travel::Status::DE::DBRIS::Journey' ) + { + my $number = $journey->train_no // $journey->number // $train_suffix; + + my $line; + if ( defined $journey->line_no and $journey->line_no ne $number ) { + $line = $journey->line_no; + } + elsif ( defined $train_suffix and $train_suffix ne $number ) { + $line = $train_suffix; + } + my @route; for my $j_stop ( $journey->route ) { push( @@ -124,39 +282,172 @@ sub add { $j_stop->name, $j_stop->eva, { - sched_arr => _epoch( $j_stop->{sched_arr} ), - sched_dep => _epoch( $j_stop->{sched_dep} ), - rt_arr => _epoch( $j_stop->{rt_arr} ), - rt_dep => _epoch( $j_stop->{rt_dep} ), - arr_delay => $j_stop->{arr_delay}, - dep_delay => $j_stop->{dep_delay}, - load => $j_stop->{load} + sched_arr => _epoch( $j_stop->sched_arr ), + sched_dep => _epoch( $j_stop->sched_dep ), + rt_arr => _epoch( $j_stop->rt_arr ), + rt_dep => _epoch( $j_stop->rt_dep ), + arr_delay => $j_stop->arr_delay, + dep_delay => $j_stop->dep_delay, + platform => $j_stop->platform, + load => { + FIRST => $j_stop->occupancy_first, + SECOND => $j_stop->occupancy_second + }, + lat => $j_stop->lat, + lon => $j_stop->lon, } ] ); + if ( $j_stop->is_additional ) { + $route[-1][2]{isAdditional} = 1; + } + if ( $j_stop->is_cancelled ) { + $route[-1][2]{isCancelled} = 1; + } + } + my @messages; + for my $msg ( $journey->messages ) { + if ( not $msg->{ueberschrift} ) { + push( + @{ $data->{him_msg} }, + { + header => q{}, + prio => $msg->{prioritaet}, + lead => $msg->{text} + } + ); + push( + @{ $persistent_data->{him_msg} }, + { + prio => $msg->{prioritaet}, + lead => $msg->{text} + } + ); + } } $db->insert( 'in_transit', { user_id => $uid, - cancelled => $stop->{dep_cancelled} + cancelled => $stop->is_cancelled ? 1 : 0, checkin_station_id => $stop->eva, + checkin_time => $now, + dep_platform => $stop->platform, + train_type => $journey->type // q{}, + train_line => $line, + train_no => $number, + train_id => $data->{trip_id}, + sched_departure => $stop->sched_dep, + real_departure => $stop->rt_dep // $stop->sched_dep, + route => $json->encode( \@route ), + data => $json->encode( + { + rt => $stop->{rt_dep} ? 1 : 0, + %{ $data // {} } + } + ), + user_data => $json->encode($persistent_data), + backend_id => $backend_id, + } + ); + } + elsif ( $journey + and $stopover + and ref($journey) eq 'Travel::Status::MOTIS::Trip' ) + { + my @route; + for my $journey_stopover ( $journey->stopovers ) { + push( + @route, + [ + $journey_stopover->stop->name, + $journey_stopover->stop->{eva} + // die('eva not set for stopover'), + { + sched_arr => + _epoch( $journey_stopover->scheduled_arrival ), + sched_dep => + _epoch( $journey_stopover->scheduled_departure ), + rt_arr => _epoch( $journey_stopover->realtime_arrival ), + rt_dep => + _epoch( $journey_stopover->realtime_departure ), + arr_delay => $journey_stopover->arrival_delay, + dep_delay => $journey_stopover->departure_delay, + platform => $journey_stopover->track, + lat => $journey_stopover->stop->lat, + lon => $journey_stopover->stop->lon, + } + ] + ); + } + + $persistent_data->{operator} = $journey->agency; + + $db->insert( + 'in_transit', + { + user_id => $uid, + cancelled => $stopover->{is_cancelled} + ? 1 + : 0, + checkin_station_id => $stopover->stop->{eva}, checkin_time => DateTime->now( time_zone => 'Europe/Berlin' ), - dep_platform => $stop->{platform}, - train_type => $journey->type, - train_line => $journey->line_no, - train_no => $journey->number // q{}, + dep_platform => $stopover->track, + train_type => $journey->mode, + train_no => q{}, train_id => $journey->id, - sched_departure => $stop->{sched_dep}, - real_departure => $stop->{rt_dep} // $stop->{sched_dep}, - route => $json->encode( [@route] ), + train_line => $journey->route_name, + sched_departure => $stopover->scheduled_departure, + real_departure => $stopover->departure, + route => $json->encode( \@route ), + data => $json->encode( + { + rt => $stopover->{is_realtime} ? 1 : 0, + %{ $data // {} } + } + ), + user_data => $json->encode($persistent_data), + backend_id => $backend_id, } ); } + elsif ($manual) { + if ( $manual->{comment} ) { + $persistent_data->{comment} = $manual->{comment}; + } + $db->insert( + 'in_transit', + { + user_id => $uid, + cancelled => 0, + checkin_station_id => $manual->{dep_id}, + checkout_station_id => $manual->{arr_id}, + checkin_time => DateTime->now( time_zone => 'Europe/Berlin' ), + train_type => $manual->{train_type}, + train_no => $manual->{train_no} || q{}, + train_id => 'manual', + train_line => $manual->{train_line} || undef, + sched_departure => $manual->{sched_departure}, + real_departure => $manual->{sched_departure}, + sched_arrival => $manual->{sched_arrival}, + real_arrival => $manual->{sched_arrival}, + route => $json->encode( $manual->{route} // [] ), + data => $json->encode( + { + manual => \1, + %{ $data // {} } + } + ), + user_data => $json->encode($persistent_data), + backend_id => $backend_id, + } + ); + return; + } else { - die('neither train nor journey specified'); + die('invalid arguments / argument types passed to InTransit->add'); } } @@ -200,8 +491,15 @@ sub postprocess { if ($is_after) { push( @route_after, $station ); } - if ( $ret->{dep_name} - and $station->[0] eq $ret->{dep_name} ) + + # Note that the departure stop may be present more than once in @route, + # e.g. when traveling along ring lines such as S41 / S42 in Berlin. + if ( + $ret->{dep_name} + and $station->[0] eq $ret->{dep_name} + and not($station->[2]{sched_dep} + and $station->[2]{sched_dep} < $ret->{sched_dep_ts} ) + ) { $is_after = 1; if ( @{$station} > 1 and not $dep_info ) { @@ -224,13 +522,25 @@ sub postprocess { $ret->{route_after} = \@route_after; $ret->{extra_data} = $ret->{data}; $ret->{comment} = $ret->{user_data}{comment}; + $ret->{wagongroups} = $ret->{user_data}{wagongroups}; + + if ( $ret->{sched_dep_ts} and $ret->{real_dep_ts} ) { + $ret->{dep_delay} = $ret->{real_dep_ts} - $ret->{sched_dep_ts}; + } + + if ( $ret->{sched_arr_ts} and $ret->{real_arr_ts} ) { + $ret->{arr_delay} = $ret->{real_arr_ts} - $ret->{sched_arr_ts}; + } + + $ret->{platform_type} = 'Gleis'; + if ( $ret->{train_type} and $ret->{train_type} =~ m{ ast | bus | ruf }ix ) { + $ret->{platform_type} = 'Steig'; + } $ret->{visibility_str} - = $ret->{visibility} - ? $visibility_itoa{ $ret->{visibility} } - : 'default'; + = $visibility_itoa{ $ret->{visibility} // 'default' }; $ret->{effective_visibility_str} - = $visibility_itoa{ $ret->{effective_visibility} }; + = $visibility_itoa{ $ret->{effective_visibility} // 'default' }; my @parsed_messages; for my $message ( @{ $ret->{messages} // [] } ) { @@ -254,7 +564,7 @@ sub postprocess { = $dep_info->{rt_arr}->epoch - $epoch; } - for my $station (@route_after) { + for my $station (@route) { if ( @{$station} > 1 ) { # Note: $station->[2]{sched_arr} may already have been @@ -262,29 +572,27 @@ sub postprocess { # station is present several times in a train's route, e.g. # for Frankfurt Flughafen in some nightly connections. my $times = $station->[2] // {}; - if ( $times->{sched_arr} - and ref( $times->{sched_arr} ) ne 'DateTime' ) - { - $times->{sched_arr} - = epoch_to_dt( $times->{sched_arr} ); - if ( $times->{rt_arr} ) { - $times->{rt_arr} - = epoch_to_dt( $times->{rt_arr} ); - $times->{rt_arr_countdown} - = $times->{rt_arr}->epoch - $epoch; + for my $key (qw(sched_arr rt_arr sched_dep rt_dep)) { + if ( $times->{$key} ) { + $times->{$key} + = epoch_or_dt_to_dt( $times->{$key} ); } } - if ( $times->{sched_dep} - and ref( $times->{sched_dep} ) ne 'DateTime' ) - { - $times->{sched_dep} - = epoch_to_dt( $times->{sched_dep} ); - if ( $times->{rt_dep} ) { - $times->{rt_dep} - = epoch_to_dt( $times->{rt_dep} ); - $times->{rt_dep_countdown} - = $times->{rt_dep}->epoch - $epoch; - } + if ( $times->{sched_arr} and $times->{rt_arr} ) { + $times->{arr_delay} + = $times->{rt_arr}->epoch - $times->{sched_arr}->epoch; + } + if ( $times->{sched_arr} or $times->{rt_arr} ) { + $times->{arr} = $times->{rt_arr} || $times->{sched_arr}; + $times->{arr_countdown} = $times->{arr}->epoch - $epoch; + } + if ( $times->{sched_dep} and $times->{rt_dep} ) { + $times->{dep_delay} + = $times->{rt_dep}->epoch - $times->{sched_dep}->epoch; + } + if ( $times->{sched_dep} or $times->{rt_dep} ) { + $times->{dep} = $times->{rt_dep} || $times->{sched_dep}; + $times->{dep_countdown} = $times->{dep}->epoch - $epoch; } } } @@ -324,7 +632,7 @@ sub get { my $table = 'in_transit'; - if ( $opt{with_timestamps} ) { + if ( $opt{with_timestamps} or $opt{with_polyline} ) { $table = 'in_transit_str'; } @@ -338,13 +646,17 @@ sub get { $ret = $res->hash; } + if ( $opt{with_polyline} and $ret ) { + $ret->{dep_latlon} = [ $ret->{dep_lat}, $ret->{dep_lon} ]; + $ret->{arr_latlon} = [ $ret->{arr_lat}, $ret->{arr_lon} ]; + $ret->{now_latlon} = $self->estimate_trip_position($ret); + } + if ( $opt{with_visibility} and $ret ) { $ret->{visibility_str} - = $ret->{visibility} - ? $visibility_itoa{ $ret->{visibility} } - : 'default'; + = $visibility_itoa{ $ret->{visibility} // 'default' }; $ret->{effective_visibility_str} - = $visibility_itoa{ $ret->{effective_visibility} }; + = $visibility_itoa{ $ret->{effective_visibility} // 'default' }; } if ( $opt{postprocess} and $ret ) { @@ -393,17 +705,20 @@ sub get_all_active { ->hashes->each; } -sub get_checkout_station_id { +sub get_checkout_ids { my ( $self, %opt ) = @_; my $uid = $opt{uid}; my $db = $opt{db} // $self->{pg}->db; - my $status = $db->select( 'in_transit', ['checkout_station_id'], - { user_id => $uid } )->hash; + my $status = $db->select( + 'in_transit', + [ 'checkout_station_id', 'backend_id' ], + { user_id => $uid } + )->hash; if ($status) { - return $status->{checkout_station_id}; + return $status->{checkout_station_id}, $status->{backend_id}; } return; } @@ -442,13 +757,6 @@ sub set_arrival { my $uid = $opt{uid}; my $db = $opt{db} // $self->{pg}->db; my $train = $opt{train}; - my $route = $opt{route}; - - $route = $self->_merge_old_route( - db => $db, - uid => $uid, - route => $route - ); my $json = JSON->new; @@ -459,7 +767,6 @@ sub set_arrival { arr_platform => $train->platform, sched_arrival => $train->sched_arrival, real_arrival => $train->arrival, - route => $json->encode($route), messages => $json->encode( [ map { [ $_->[0]->epoch, $_->[1] ] } $train->messages ] ) @@ -484,6 +791,22 @@ sub set_arrival_eva { ); } +sub set_arrival_platform { + my ( $self, %opt ) = @_; + + my $uid = $opt{uid}; + my $db = $opt{db} // $self->{pg}->db; + my $platform = $opt{arrival_platform}; + + $db->update( + 'in_transit', + { + arr_platform => $platform, + }, + { user_id => $uid } + ); +} + sub set_arrival_times { my ( $self, %opt ) = @_; @@ -502,6 +825,62 @@ sub set_arrival_times { ); } +sub set_polyline { + my ( $self, %opt ) = @_; + + my $uid = $opt{uid}; + my $db = $opt{db} // $self->{pg}->db; + my $polyline = $opt{polyline}; + my $old_id = $opt{old_id}; + + my $coords = $polyline->{coords}; + my $from_eva = $polyline->{from_eva}; + my $to_eva = $polyline->{to_eva}; + + my $polyline_str = JSON->new->encode($coords); + + my $pl_res = $db->select( + 'polylines', + ['id'], + { + origin_eva => $from_eva, + destination_eva => $to_eva, + polyline => $polyline_str, + }, + { limit => 1 } + ); + + my $polyline_id; + if ( my $h = $pl_res->hash ) { + $polyline_id = $h->{id}; + } + else { + eval { + $polyline_id = $db->insert( + 'polylines', + { + origin_eva => $from_eva, + destination_eva => $to_eva, + polyline => $polyline_str + }, + { returning => 'id' } + )->hash->{id}; + }; + if ($@) { + $self->{log}->warn("add_route_timestamps: insert polyline: $@"); + } + } + if ( $polyline_id and ( not defined $old_id or $polyline_id != $old_id ) ) { + $self->set_polyline_id( + uid => $uid, + db => $db, + polyline_id => $polyline_id, + train_id => $opt{train_id}, + ); + } + +} + sub set_polyline_id { my ( $self, %opt ) = @_; @@ -509,11 +888,13 @@ sub set_polyline_id { my $db = $opt{db} // $self->{pg}->db; my $polyline_id = $opt{polyline_id}; - $db->update( - 'in_transit', - { polyline_id => $polyline_id }, - { user_id => $uid } - ); + my %where = ( user_id => $uid ); + + if ( $opt{train_id} ) { + $where{train_id} = $opt{train_id}; + } + + $db->update( 'in_transit', { polyline_id => $polyline_id }, \%where ); } sub set_route_data { @@ -526,6 +907,12 @@ sub set_route_data { my $qos_msg = $opt{qos_messages}; my $him_msg = $opt{him_messages}; + my %where = ( user_id => $uid ); + + if ( $opt{train_id} ) { + $where{train_id} = $opt{train_id}; + } + my $res_h = $db->select( 'in_transit', ['data'], { user_id => $uid } ) ->expand->hash; @@ -542,7 +929,7 @@ sub set_route_data { route => JSON->new->encode($route), data => JSON->new->encode($data) }, - { user_id => $uid } + \%where ); } @@ -629,6 +1016,128 @@ sub update_departure_cancelled { return $rows; } +sub update_departure_dbris { + my ( $self, %opt ) = @_; + my $uid = $opt{uid}; + my $db = $opt{db} // $self->{pg}->db; + my $dep_eva = $opt{dep_eva}; + my $arr_eva = $opt{arr_eva}; + my $journey = $opt{journey}; + my $stop = $opt{stop}; + my $json = JSON->new; + + my $res_h = $db->select( 'in_transit', [ 'data', 'user_data' ], + { user_id => $uid } )->expand->hash; + my $ephemeral_data = $res_h ? $res_h->{data} : {}; + my $persistent_data = $res_h ? $res_h->{user_data} : {}; + + if ( $stop->{rt_dep} ) { + $ephemeral_data->{rt} = 1; + } + + $ephemeral_data->{him_msg} = []; + $persistent_data->{him_msg} = []; + for my $msg ( $journey->messages ) { + if ( not $msg->{ueberschrift} ) { + push( + @{ $ephemeral_data->{him_msg} }, + { + header => q{}, + prio => $msg->{prioritaet}, + lead => $msg->{text} + } + ); + push( + @{ $persistent_data->{him_msg} }, + { + prio => $msg->{prioritaet}, + lead => $msg->{text} + } + ); + } + } + + # selecting on user_id and train_no avoids a race condition if a user checks + # into a new train while we are fetching data for their previous journey. In + # this case, the new train would receive data from the previous journey. + $db->update( + 'in_transit', + { + real_departure => $stop->{rt_dep}, + data => $json->encode($ephemeral_data), + user_data => $json->encode($persistent_data), + }, + { + user_id => $uid, + train_id => $opt{train_id}, + checkin_station_id => $dep_eva, + checkout_station_id => $arr_eva, + } + ); +} + +sub update_departure_efa { + my ( $self, %opt ) = @_; + my $uid = $opt{uid}; + my $db = $opt{db} // $self->{pg}->db; + my $dep_eva = $opt{dep_eva}; + my $arr_eva = $opt{arr_eva}; + my $journey = $opt{journey}; + my $stop = $opt{stop}; + my $json = JSON->new; + + my $res_h = $db->select( 'in_transit', ['data'], { user_id => $uid } ) + ->expand->hash; + my $ephemeral_data = $res_h ? $res_h->{data} : {}; + if ( $stop->rt_dep ) { + $ephemeral_data->{rt} = 1; + } + + # selecting on user_id and train_no avoids a race condition if a user checks + # into a new train while we are fetching data for their previous journey. In + # this case, the new train would receive data from the previous journey. + $db->update( + 'in_transit', + { + data => $json->encode($ephemeral_data), + real_departure => $stop->rt_dep, + }, + { + user_id => $uid, + train_id => $opt{trip_id}, + checkin_station_id => $dep_eva, + checkout_station_id => $arr_eva, + } + ); +} + +sub update_departure_motis { + my ( $self, %opt ) = @_; + my $uid = $opt{uid}; + my $db = $opt{db} // $self->{pg}->db; + my $dep_eva = $opt{dep_eva}; + my $arr_eva = $opt{arr_eva}; + my $journey = $opt{journey}; + my $stopover = $opt{stopover}; + my $json = JSON->new; + + # selecting on user_id and train_no avoids a race condition if a user checks + # into a new train while we are fetching data for their previous journey. In + # this case, the new train would receive data from the previous journey. + $db->update( + 'in_transit', + { + real_departure => $stopover->{realtime_departure}, + }, + { + user_id => $uid, + train_id => $opt{train_id}, + checkin_station_id => $dep_eva, + checkout_station_id => $arr_eva, + } + ); +} + sub update_departure_hafas { my ( $self, %opt ) = @_; my $uid = $opt{uid}; @@ -639,12 +1148,20 @@ sub update_departure_hafas { my $stop = $opt{stop}; my $json = JSON->new; + my $res_h = $db->select( 'in_transit', ['data'], { user_id => $uid } ) + ->expand->hash; + my $ephemeral_data = $res_h ? $res_h->{data} : {}; + if ( $stop->{rt_dep} ) { + $ephemeral_data->{rt} = 1; + } + # selecting on user_id and train_no avoids a race condition if a user checks # into a new train while we are fetching data for their previous journey. In # this case, the new train would receive data from the previous journey. $db->update( 'in_transit', { + data => $json->encode($ephemeral_data), real_departure => $stop->{rt_dep}, }, { @@ -698,7 +1215,7 @@ sub update_arrival { return $rows; } -sub update_arrival_hafas { +sub update_arrival_dbris { my ( $self, %opt ) = @_; my $uid = $opt{uid}; my $db = $opt{db} // $self->{pg}->db; @@ -708,7 +1225,37 @@ sub update_arrival_hafas { my $stop = $opt{stop}; my $json = JSON->new; - # TODO use old rt data if available + my $res_h = $db->select( 'in_transit', [ 'data', 'user_data' ], + { user_id => $uid } )->expand->hash; + my $ephemeral_data = $res_h ? $res_h->{data} : {}; + my $persistent_data = $res_h ? $res_h->{user_data} : {}; + + if ( $stop->{rt_arr} ) { + $ephemeral_data->{rt} = 1; + } + + $ephemeral_data->{him_msg} = []; + $persistent_data->{him_msg} = []; + for my $msg ( $journey->messages ) { + if ( not $msg->{ueberschrift} ) { + push( + @{ $ephemeral_data->{him_msg} }, + { + header => q{}, + prio => $msg->{prioritaet}, + lead => $msg->{text} + } + ); + push( + @{ $persistent_data->{him_msg} }, + { + prio => $msg->{prioritaet}, + lead => $msg->{text} + } + ); + } + } + my @route; for my $j_stop ( $journey->route ) { push( @@ -717,21 +1264,219 @@ sub update_arrival_hafas { $j_stop->name, $j_stop->eva, { - sched_arr => _epoch( $j_stop->{sched_arr} ), - sched_dep => _epoch( $j_stop->{sched_dep} ), - rt_arr => _epoch( $j_stop->{rt_arr} ), - rt_dep => _epoch( $j_stop->{rt_dep} ), - arr_delay => $j_stop->{arr_delay}, - dep_delay => $j_stop->{dep_delay}, - load => $j_stop->{load} + sched_arr => _epoch( $j_stop->sched_arr ), + sched_dep => _epoch( $j_stop->sched_dep ), + rt_arr => _epoch( $j_stop->rt_arr ), + rt_dep => _epoch( $j_stop->rt_dep ), + platform => $j_stop->platform, + arr_delay => $j_stop->arr_delay, + dep_delay => $j_stop->dep_delay, + platform => $j_stop->platform, + load => { + FIRST => $j_stop->occupancy_first, + SECOND => $j_stop->occupancy_second + }, + lat => $j_stop->lat, + lon => $j_stop->lon, } ] ); + if ( $j_stop->is_additional ) { + $route[-1][2]{isAdditional} = 1; + } + if ( $j_stop->is_cancelled ) { + $route[-1][2]{isCancelled} = 1; + } } - my $res_h = $db->select( 'in_transit', ['route'], { user_id => $uid } ) + # selecting on user_id and train_no avoids a race condition if a user checks + # into a new train while we are fetching data for their previous journey. In + # this case, the new train would receive data from the previous journey. + $db->update( + 'in_transit', + { + real_arrival => $stop->rt_arr, + arr_platform => $stop->platform, + route => $json->encode( [@route] ), + data => $json->encode($ephemeral_data), + user_data => $json->encode($persistent_data), + }, + { + user_id => $uid, + train_id => $opt{train_id}, + checkin_station_id => $dep_eva, + checkout_station_id => $arr_eva, + } + ); +} + +sub update_arrival_efa { + my ( $self, %opt ) = @_; + my $uid = $opt{uid}; + my $db = $opt{db} // $self->{pg}->db; + my $dep_eva = $opt{dep_eva}; + my $arr_eva = $opt{arr_eva}; + my $journey = $opt{journey}; + my $stop = $opt{stop}; + my $json = JSON->new; + + my $res_h + = $db->select( 'in_transit', [ 'data', 'route' ], { user_id => $uid } ) ->expand->hash; - my $old_route = $res_h ? $res_h->{route} : []; + my $ephemeral_data = $res_h ? $res_h->{data} : {}; + my $old_route = $res_h ? $res_h->{route} : []; + + if ( $stop->rt_arr ) { + $ephemeral_data->{rt} = 1; + } + + my @route; + for my $j_stop ( $journey->route ) { + push( + @route, + [ + $j_stop->full_name, + $j_stop->id_num, + { + sched_arr => _epoch( $j_stop->sched_arr ), + sched_dep => _epoch( $j_stop->sched_dep ), + rt_arr => _epoch( $j_stop->rt_arr ), + rt_dep => _epoch( $j_stop->rt_dep ), + arr_delay => $j_stop->arr_delay, + dep_delay => $j_stop->dep_delay, + platform => $j_stop->platform, + efa_load => $j_stop->occupancy, + lat => $j_stop->latlon->[0], + lon => $j_stop->latlon->[1], + } + ] + ); + if ( $j_stop->is_cancelled ) { + $route[-1][2]{isCancelled} = 1; + } + } + + # TODO set efa_load from old route entry if missing in current route entry + # (at least in VVO, occupancy data is only provided for future stops) + + # selecting on user_id and train_no avoids a race condition if a user checks + # into a new train while we are fetching data for their previous journey. In + # this case, the new train would receive data from the previous journey. + $db->update( + 'in_transit', + { + data => $json->encode($ephemeral_data), + real_arrival => $stop->rt_arr, + arr_platform => $stop->platform, + route => $json->encode( [@route] ), + }, + { + user_id => $uid, + train_id => $opt{trip_id}, + checkin_station_id => $dep_eva, + checkout_station_id => $arr_eva, + } + ); +} + +sub update_arrival_motis { + my ( $self, %opt ) = @_; + my $uid = $opt{uid}; + my $db = $opt{db} // $self->{pg}->db; + my $dep_eva = $opt{dep_eva}; + my $arr_eva = $opt{arr_eva}; + my $journey = $opt{journey}; + my $stopover = $opt{stopover}; + my $json = JSON->new; + + my @route; + for my $journey_stopover ( $journey->stopovers ) { + push( + @route, + [ + $journey_stopover->stop->name, + $journey_stopover->stop->{eva} + // die('eva not set for stopover'), + { + sched_arr => _epoch( $journey_stopover->scheduled_arrival ), + sched_dep => + _epoch( $journey_stopover->scheduled_departure ), + rt_arr => _epoch( $journey_stopover->realtime_arrival ), + rt_dep => _epoch( $journey_stopover->realtime_departure ), + arr_delay => $journey_stopover->arrival_delay, + dep_delay => $journey_stopover->departure_delay, + platform => $journey_stopover->track, + lat => $journey_stopover->stop->lat, + lon => $journey_stopover->stop->lon, + } + ] + ); + } + + # selecting on user_id and train_no avoids a race condition if a user checks + # into a new train while we are fetching data for their previous journey. In + # this case, the new train would receive data from the previous journey. + $db->update( + 'in_transit', + { + real_arrival => $stopover->realtime_arrival, + arr_platform => $stopover->track, + route => $json->encode( [@route] ), + }, + { + user_id => $uid, + train_id => $opt{train_id}, + checkin_station_id => $dep_eva, + checkout_station_id => $arr_eva, + } + ); +} + +sub update_arrival_hafas { + my ( $self, %opt ) = @_; + my $uid = $opt{uid}; + my $db = $opt{db} // $self->{pg}->db; + my $dep_eva = $opt{dep_eva}; + my $arr_eva = $opt{arr_eva}; + my $journey = $opt{journey}; + my $stop = $opt{stop}; + my $json = JSON->new; + + my $res_h + = $db->select( 'in_transit', [ 'data', 'route' ], { user_id => $uid } ) + ->expand->hash; + my $ephemeral_data = $res_h ? $res_h->{data} : {}; + my $old_route = $res_h ? $res_h->{route} : []; + + if ( $stop->{rt_arr} ) { + $ephemeral_data->{rt} = 1; + } + + my @route; + for my $j_stop ( $journey->route ) { + push( + @route, + [ + $j_stop->loc->name, + $j_stop->loc->eva, + { + sched_arr => _epoch( $j_stop->sched_arr ), + sched_dep => _epoch( $j_stop->sched_dep ), + rt_arr => _epoch( $j_stop->rt_arr ), + rt_dep => _epoch( $j_stop->rt_dep ), + arr_delay => $j_stop->arr_delay, + dep_delay => $j_stop->dep_delay, + platform => $j_stop->platform, + load => $j_stop->load, + lat => $j_stop->loc->lat, + lon => $j_stop->loc->lon, + } + ] + ); + if ( defined $j_stop->tz_offset ) { + $route[-1][2]{tz_offset} = $j_stop->tz_offset; + } + } for my $i ( 0 .. $#route ) { if ( $old_route->[$i] and $old_route->[$i][1] == $route[$i][1] ) { @@ -747,7 +1492,9 @@ sub update_arrival_hafas { $db->update( 'in_transit', { - real_arrival => $stop->{rt_arr}, + data => $json->encode($ephemeral_data), + real_arrival => $stop->rt_arr, + arr_platform => $stop->platform, route => $json->encode( [@route] ), }, { @@ -766,6 +1513,12 @@ sub update_data { my $db = $opt{db} // $self->{pg}->db; my $new_data = $opt{data} // {}; + my %where = ( user_id => $uid ); + + if ( $opt{train_id} ) { + $where{train_id} = $opt{train_id}; + } + my $res_h = $db->select( 'in_transit', ['data'], { user_id => $uid } ) ->expand->hash; @@ -775,11 +1528,7 @@ sub update_data { $data->{$k} = $v; } - $db->update( - 'in_transit', - { data => JSON->new->encode($data) }, - { user_id => $uid } - ); + $db->update( 'in_transit', { data => JSON->new->encode($data) }, \%where ); } sub update_user_data { @@ -789,6 +1538,12 @@ sub update_user_data { my $db = $opt{db} // $self->{pg}->db; my $new_data = $opt{user_data} // {}; + my %where = ( user_id => $uid ); + + if ( $opt{train_id} ) { + $where{train_id} = $opt{train_id}; + } + my $res_h = $db->select( 'in_transit', ['user_data'], { user_id => $uid } ) ->expand->hash; @@ -798,11 +1553,8 @@ sub update_user_data { $data->{$k} = $v; } - $db->update( - 'in_transit', - { user_data => JSON->new->encode($data) }, - { user_id => $uid } - ); + $db->update( 'in_transit', + { user_data => JSON->new->encode($data) }, \%where ); } sub update_visibility { @@ -824,4 +1576,141 @@ sub update_visibility { ); } +sub estimate_trip_position_between_stops { + my ( $self, %opt ) = @_; + + my $time_complete = $opt{now} - $opt{from_ts}; + my $time_total = $opt{to_ts} - $opt{from_ts}; + my $ratio = $time_complete / $time_total; + + my $distance = GIS::Distance->new; + my $polyline = $opt{polyline}; + my ( $i_from, $i_to ); + + for my $i ( 0 .. $#{$polyline} ) { + if ( not defined $i_from + and $polyline->[$i][2] + and $polyline->[$i][2] == $opt{from}[1] ) + { + $i_from = $i; + } + elsif ( not defined $i_to + and $polyline->[$i][2] + and $polyline->[$i][2] == $opt{to}[1] ) + { + $i_to = $i; + last; + } + } + if ( defined $i_from and defined $i_to ) { + my $total_distance = 0; + for my $i ( $i_from + 1 .. $i_to ) { + my $prev = $polyline->[ $i - 1 ]; + my $this = $polyline->[$i]; + if ( $prev and $this ) { + $total_distance + += $distance->distance_metal( $prev->[1], $prev->[0], + $this->[1], $this->[0] ); + } + } + + my $marker_distance = $total_distance * $ratio; + $total_distance = 0; + for my $i ( $i_from + 1 .. $i_to ) { + my $prev = $polyline->[ $i - 1 ]; + my $this = $polyline->[$i]; + if ( $prev and $this ) { + my $prev_distance = $total_distance; + $total_distance + += $distance->distance_metal( $prev->[1], $prev->[0], + $this->[1], $this->[0] ); + if ( $total_distance > $marker_distance ) { + my $sub_ratio = 1; + if ( $total_distance != $prev_distance ) { + $sub_ratio = ( $marker_distance - $prev_distance ) + / ( $total_distance - $prev_distance ); + } + return ( + $prev->[1] + ( $this->[1] - $prev->[1] ) * $sub_ratio, + $prev->[0] + ( $this->[0] - $prev->[0] ) * $sub_ratio, + ); + } + } + } + } + return ( + $opt{from}[2]{lat} + ( $opt{to}[2]{lat} - $opt{from}[2]{lat} ) * $ratio, + $opt{from}[2]{lon} + ( $opt{to}[2]{lon} - $opt{from}[2]{lon} ) * $ratio + ); +} + +sub estimate_trip_position { + my ( $self, $in_transit ) = @_; + + my @now_latlon; + my @route = @{ $in_transit->{route} }; + + # estimate_train_position runs before postprocess, so all route + # timestamps are provided in UNIX seconds and not as DateTime objects. + my $now = DateTime->now( time_zone => 'Europe/Berlin' )->epoch; + + my $prev_ts; + for my $i ( 0 .. $#route ) { + my $ts = $route[$i][2]{rt_arr} // $route[$i][2]{sched_arr} + // $route[$i][2]{rt_dep} // $route[$i][2]{sched_dep} // 0; + my $ts_dep = $route[$i][2]{rt_dep} // $route[$i][2]{sched_dep} + // $route[$i][2]{rt_arr} // $route[$i][2]{sched_arr} // 0; + if ( $ts and $ts_dep and $now >= $ts and $now <= $ts_dep ) { + + # Currently at a stop + @now_latlon = ( $route[$i][2]{lat}, $route[$i][2]{lon} ); + last; + } + if ( $ts + and $prev_ts + and $now > $prev_ts + and $now < $ts ) + { + @now_latlon = $self->estimate_trip_position_between_stops( + now => $now, + from => $route[ $i - 1 ], + from_ts => $prev_ts, + to => $route[$i], + to_ts => $ts, + polyline => $in_transit->{polyline}, + ); + last; + } + $prev_ts = $ts_dep; + } + + if ( not @now_latlon + and $in_transit->{sched_dep_ts} + and $in_transit->{sched_arr_ts} ) + { + my $time_complete = $now + - ( $in_transit->{real_dep_ts} // $in_transit->{sched_dep_ts} ); + my $time_total + = ( $in_transit->{real_arr_ts} // $in_transit->{sched_arr_ts} ) + - ( $in_transit->{real_dep_ts} // $in_transit->{sched_dep_ts} ); + + if ( $time_total == 0 ) { + return [ $in_transit->{dep_lat}, $in_transit->{dep_lon} ]; + } + + my $completion = $time_complete / $time_total; + $completion = $completion < 0 ? 0 : $completion > 1 ? 1 : $completion; + @now_latlon = ( + $in_transit->{dep_lat} + + ( $in_transit->{arr_lat} - $in_transit->{dep_lat} ) + * $completion, + $in_transit->{dep_lon} + + ( $in_transit->{arr_lon} - $in_transit->{dep_lon} ) + * $completion, + ); + } + + return \@now_latlon; +} + 1; diff --git a/lib/Travelynx/Model/Journeys.pm b/lib/Travelynx/Model/Journeys.pm index 1c975f4..77907cd 100755 --- a/lib/Travelynx/Model/Journeys.pm +++ b/lib/Travelynx/Model/Journeys.pm @@ -4,16 +4,16 @@ package Travelynx::Model::Journeys; # # SPDX-License-Identifier: AGPL-3.0-or-later -use GIS::Distance; -use List::MoreUtils qw(after_incl before_incl); - use strict; use warnings; use 5.020; use utf8; use DateTime; +use DateTime::Format::Strptime; +use GIS::Distance; use JSON; +use List::MoreUtils qw(after_incl before_incl first_index last_index); my %visibility_itoa = ( 100 => 'public', @@ -50,6 +50,8 @@ sub epoch_to_dt { ); } +# TODO turn into a travelynx helper called from templates so that +# loc_handle is available for localization sub min_to_human { my ( $self, $minutes ) = @_; @@ -118,8 +120,10 @@ sub add { my $db = $opt{db}; my $uid = $opt{uid}; my $now = DateTime->now( time_zone => 'Europe/Berlin' ); - my $dep_station = $self->{stations}->search( $opt{dep_station} ); - my $arr_station = $self->{stations}->search( $opt{arr_station} ); + my $dep_station = $self->{stations} + ->search( $opt{dep_station}, backend_id => $opt{backend_id} ); + my $arr_station = $self->{stations} + ->search( $opt{arr_station}, backend_id => $opt{backend_id} ); if ( not $dep_station ) { return ( undef, 'Unbekannter Startbahnhof' ); @@ -167,16 +171,60 @@ sub add { my @route; if ( not $route_has_start ) { - push( @route, [ $dep_station->{name}, $dep_station->{eva}, {} ] ); + push( + @route, + [ + $dep_station->{name}, + $dep_station->{eva}, + { + lat => $dep_station->{lat}, + lon => $dep_station->{lon}, + } + ] + ); } if ( $opt{route} ) { + my $parser = DateTime::Format::Strptime->new( + pattern => '%d.%m.%Y %H:%M', + locale => 'de_DE', + time_zone => 'Europe/Berlin' + ); my @unknown_stations; + my $prev_epoch = 0; + for my $station ( @{ $opt{route} } ) { - my $station_info = $self->{stations}->search($station); + my $ts; + my %station_data; + if ( $station + =~ m{ ^ (?<stop> [^@]+? ) \s* [@] \s* (?<timestamp> .+ ) $ }x ) + { + $station = $+{stop}; + $ts = $parser->parse_datetime( $+{timestamp} ); + if ($ts) { + my $epoch = $ts->epoch; + if ( $epoch < $prev_epoch ) { + return ( undef, +'Zeitstempel der Unterwegshalte müssen monoton steigend sein (keine Zeitreisen und keine Portale)' + ); + } + $station_data{sched_arr} = $epoch; + $station_data{sched_dep} = $epoch; + $prev_epoch = $epoch; + } + } + my $station_info = $self->{stations} + ->search( $station, backend_id => $opt{backend_id} ); if ($station_info) { - push( @route, - [ $station_info->{name}, $station_info->{eva}, {} ] ); + $station_data{lat} = $station_info->{lat}; + $station_data{lon} = $station_info->{lon}; + push( + @route, + [ + $station_info->{name}, $station_info->{eva}, + \%station_data, + ] + ); } else { push( @route, [ $station, undef, {} ] ); @@ -198,7 +246,17 @@ sub add { } if ( not $route_has_stop ) { - push( @route, [ $arr_station->{name}, $arr_station->{eva}, {} ] ); + push( + @route, + [ + $arr_station->{name}, + $arr_station->{eva}, + { + lat => $arr_station->{lat}, + lon => $arr_station->{lon}, + } + ] + ); } my $entry = { @@ -218,6 +276,7 @@ sub add { edited => 0x3fff, cancelled => $opt{cancelled} ? 1 : 0, route => JSON->new->encode( \@route ), + backend_id => $opt{backend_id}, }; if ( $opt{comment} ) { @@ -250,8 +309,14 @@ sub add_from_in_transit { my $db = $opt{db}; my $journey = $opt{journey}; + if ( $journey->{train_id} eq 'manual' ) { + $journey->{edited} = 0x3fff; + } + else { + $journey->{edited} = 0; + } + delete $journey->{data}; - $journey->{edited} = 0; $journey->{checkout_time} = DateTime->now( time_zone => 'Europe/Berlin' ); return $db->insert( 'journeys', $journey, { returning => 'id' } ) @@ -276,7 +341,8 @@ sub update { eval { if ( exists $opt{from_name} ) { - my $from_station = $self->{stations}->search( $opt{from_name} ); + my $from_station = $self->{stations} + ->search( $opt{from_name}, backend_id => $journey->{backend_id} ); if ( not $from_station ) { die("Unbekannter Startbahnhof\n"); } @@ -292,7 +358,8 @@ sub update { )->rows; } if ( exists $opt{to_name} ) { - my $to_station = $self->{stations}->search( $opt{to_name} ); + my $to_station = $self->{stations} + ->search( $opt{to_name}, backend_id => $journey->{backend_id} ); if ( not $to_station ) { die("Unbekannter Zielbahnhof\n"); } @@ -365,7 +432,40 @@ sub update { )->rows; } if ( exists $opt{route} ) { - my @new_route = map { [ $_, undef, {} ] } @{ $opt{route} }; + + # If $opt{route} is a subset of $journey->{route}, we can recycle all data + my @new_route; + my $new_route_i = 0; + for my $old_route_i ( 0 .. $#{ $journey->{route} } ) { + if ( $journey->{route}[$old_route_i][0] eq + $opt{route}[$new_route_i] ) + { + $new_route_i += 1; + push( @new_route, $journey->{route}[$old_route_i] ); + } + } + + # Otherwise, fetch stop IDs so that polylines remain usable + if ( @new_route != @{ $opt{route} } ) { + my %stop + = map { $_->{name} => $_ } $self->{stations}->get_by_names( + backend_id => $journey->{backend_id}, + names => [ $opt{route} ] + ); + @new_route = map { + [ + $_, + $stop{$_}{eva}, + defined $stop{$_}{eva} + ? { + lat => $stop{$_}{lat}, + lon => $stop{$_}{lon} + } + : {} + ] + } @{ $opt{route} }; + } + $rows = $db->update( 'journeys', { @@ -503,6 +603,83 @@ sub pop { return $journey; } +sub set_polyline { + my ( $self, %opt ) = @_; + + my $uid = $opt{uid}; + my $db = $opt{db} // $self->{pg}->db; + my $polyline = $opt{polyline}; + + my $from_eva = $opt{from_eva}; + my $to_eva = $opt{to_eva}; + + my $polyline_str = JSON->new->encode($polyline); + + my $pl_res = $db->select( + 'polylines', + ['id'], + { + origin_eva => $from_eva, + destination_eva => $to_eva, + polyline => $polyline_str, + }, + { limit => 1 } + ); + + my $polyline_id; + if ( my $h = $pl_res->hash ) { + $polyline_id = $h->{id}; + } + else { + $polyline_id = $db->insert( + 'polylines', + { + origin_eva => $from_eva, + destination_eva => $to_eva, + polyline => $polyline_str + }, + { returning => 'id' } + )->hash->{id}; + } + if ($polyline_id) { + $self->set_polyline_id( + uid => $uid, + db => $db, + polyline_id => $polyline_id, + journey_id => $opt{journey_id}, + edited => $opt{edited}, + ); + $self->stats_cache->invalidate( + ts => epoch_to_dt( $opt{stats_ts} ), + db => $db, + uid => $uid + ); + } + +} + +sub set_polyline_id { + my ( $self, %opt ) = @_; + + my $uid = $opt{uid}; + my $db = $opt{db} // $self->{pg}->db; + my $polyline_id = $opt{polyline_id}; + my $journey_id = $opt{journey_id}; + my $edited = $opt{edited}; + + $db->update( + 'journeys', + { + polyline_id => $polyline_id, + edited => $edited | 0x0040 + }, + { + user_id => $uid, + id => $opt{journey_id} + } + ); +} + sub get { my ( $self, %opt ) = @_; @@ -515,7 +692,7 @@ sub get { my @select = ( - qw(journey_id train_type train_line train_no checkin_ts sched_dep_ts real_dep_ts dep_eva dep_ds100 dep_name dep_lat dep_lon checkout_ts sched_arr_ts real_arr_ts arr_eva arr_ds100 arr_name arr_lat arr_lon cancelled edited route messages user_data visibility effective_visibility) + qw(journey_id is_dbris is_iris is_hafas is_motis backend_name backend_id train_type train_line train_no checkin_ts sched_dep_ts real_dep_ts dep_eva dep_ds100 dep_name dep_platform dep_lat dep_lon checkout_ts sched_arr_ts real_arr_ts arr_eva arr_ds100 arr_name arr_platform arr_lat arr_lon cancelled edited route messages user_data visibility effective_visibility) ); my %where = ( user_id => $uid, @@ -573,12 +750,19 @@ sub get { my $ref = { id => $entry->{journey_id}, - type => $entry->{train_type}, + is_dbris => $entry->{is_dbris}, + is_iris => $entry->{is_iris}, + is_hafas => $entry->{is_hafas}, + is_motis => $entry->{is_motis}, + backend_name => $entry->{backend_name}, + backend_id => $entry->{backend_id}, + type => $entry->{train_type} =~ s{ \s+ $ }{}rx, line => $entry->{train_line}, no => $entry->{train_no}, from_eva => $entry->{dep_eva}, from_ds100 => $entry->{dep_ds100}, from_name => $entry->{dep_name}, + from_platform => $entry->{dep_platform}, from_latlon => [ $entry->{dep_lat}, $entry->{dep_lon} ], checkin_ts => $entry->{checkin_ts}, sched_dep_ts => $entry->{sched_dep_ts}, @@ -586,6 +770,7 @@ sub get { to_eva => $entry->{arr_eva}, to_ds100 => $entry->{arr_ds100}, to_name => $entry->{arr_name}, + to_platform => $entry->{arr_platform}, to_latlon => [ $entry->{arr_lat}, $entry->{arr_lon} ], checkout_ts => $entry->{checkout_ts}, sched_arr_ts => $entry->{sched_arr_ts}, @@ -619,10 +804,18 @@ sub get { $ref->{checkout} = epoch_to_dt( $ref->{checkout_ts} ); $ref->{sched_arrival} = epoch_to_dt( $ref->{sched_arr_ts} ); $ref->{rt_arrival} = epoch_to_dt( $ref->{rt_arr_ts} ); + if ( $ref->{rt_dep_ts} and $ref->{sched_dep_ts} ) { + $ref->{delay_dep} = $ref->{rt_dep_ts} - $ref->{sched_dep_ts}; + } + if ( $ref->{rt_arr_ts} and $ref->{sched_arr_ts} ) { + $ref->{delay_arr} = $ref->{rt_arr_ts} - $ref->{sched_arr_ts}; + } + } + if ( $opt{with_route_datetime} ) { for my $stop ( @{ $ref->{route} } ) { for my $k (qw(rt_arr rt_dep sched_arr sched_dep)) { if ( $stop->[2]{$k} ) { - $stop->[2]{$k} = epoch_to_dt( $stop->[2]{$k} ); + $stop->[2]{"${k}_dt"} = epoch_to_dt( $stop->[2]{$k} ); } } } @@ -632,7 +825,10 @@ sub get { my $rename = $self->{renamed_station}; for my $stop ( @{ $ref->{route} } ) { if ( $stop->[0] =~ m{^Betriebsstelle nicht bekannt (\d+)$} ) { - if ( my $s = $self->{stations}->get_by_eva($1) ) { + if ( my $s + = $self->{stations} + ->get_by_eva( $1, backend_id => $ref->{backend_id} ) ) + { $stop->[0] = $s->{name}; } } @@ -767,14 +963,40 @@ sub get_oldest_ts { return undef; } -sub get_latest_checkout_station_id { +sub get_latest_checkout_latlon { + my ( $self, %opt ) = @_; + my $uid = $opt{uid}; + my $db = $opt{db} // $self->{pg}->db; + + my $res_h = $db->select( + 'journeys_str', + [ 'arr_lat', 'arr_lon', ], + { + user_id => $uid, + cancelled => 0 + }, + { + limit => 1, + order_by => { -desc => 'journey_id' } + } + )->hash; + + if ( not $res_h ) { + return; + } + + return $res_h->{arr_lat}, $res_h->{arr_lon}; + +} + +sub get_latest_checkout_ids { my ( $self, %opt ) = @_; my $uid = $opt{uid}; my $db = $opt{db} // $self->{pg}->db; my $res_h = $db->select( 'journeys', - ['checkout_station_id'], + [ 'checkout_station_id', 'backend_id', ], { user_id => $uid, cancelled => 0 @@ -789,7 +1011,7 @@ sub get_latest_checkout_station_id { return; } - return $res_h->{checkout_station_id}; + return $res_h->{checkout_station_id}, $res_h->{backend_id}; } sub get_latest_checkout_stations { @@ -800,7 +1022,13 @@ sub get_latest_checkout_stations { my $res = $db->select( 'journeys_str', - [ 'arr_name', 'arr_eva', 'train_id' ], + [ + 'arr_name', 'arr_eva', + 'arr_external_id', 'train_id', + 'backend_id', 'backend_name', + 'is_dbris', 'is_efa', + 'is_hafas', 'is_motis' + ], { user_id => $uid, cancelled => 0 @@ -821,9 +1049,15 @@ sub get_latest_checkout_stations { push( @ret, { - name => $row->{arr_name}, - eva => $row->{arr_eva}, - hafas => ( $row->{train_id} =~ m{[|]} ? 1 : 0 ), + name => $row->{arr_name}, + eva => $row->{arr_eva}, + external_id_or_eva => $row->{arr_external_id} + // $row->{arr_eva}, + dbris => $row->{is_dbris} ? $row->{backend_name} : 0, + efa => $row->{is_efa} ? $row->{backend_name} : 0, + hafas => $row->{is_hafas} ? $row->{backend_name} : 0, + motis => $row->{is_motis} ? $row->{backend_name} : 0, + backend_id => $row->{backend_id}, } ); } @@ -940,6 +1174,8 @@ sub generate_missing_stats { my $stats_index = 0; + my %need_year; + for my $journey_index ( 0 .. $#journey_months ) { if ( $stats_index < @stats_months and $journey_months[$journey_index][0] @@ -951,6 +1187,7 @@ sub generate_missing_stats { } else { my ( $year, $month ) = @{ $journey_months[$journey_index] }; + $need_year{$year} = 1; $self->get_stats( uid => $uid, db => $db, @@ -960,6 +1197,14 @@ sub generate_missing_stats { ); } } + for my $year ( keys %need_year ) { + $self->get_stats( + uid => $uid, + db => $db, + year => $year, + write_only => 1 + ); + } } sub get_nav_months { @@ -1021,37 +1266,39 @@ sub sanity_check { if ( defined $journey->{sched_duration} and $journey->{sched_duration} <= 0 ) { - return -'Die geplante Dauer dieser Zugfahrt ist ≤ 0. Teleportation und Zeitreisen werden aktuell nicht unterstützt.'; + return 'Die geplante Dauer dieser Fahrt ist ≤ 0.' + . ' Teleportation und Zeitreisen werden in diesem Universum nicht unterstützt.'; } if ( defined $journey->{rt_duration} and $journey->{rt_duration} <= 0 ) { - return -'Die Dauer dieser Zugfahrt ist ≤ 0. Teleportation und Zeitreisen werden aktuell nicht unterstützt.'; + return 'Die Dauer dieser Fahrt ist ≤ 0.' + . ' Teleportation und Zeitreisen werden in diesem Universum nicht unterstützt.'; } if ( $journey->{sched_duration} - and $journey->{sched_duration} > 60 * 60 * 24 ) + and $journey->{sched_duration} > 60 * 60 * 72 ) { - return 'Die Zugfahrt ist länger als 24 Stunden.'; + return 'Die Fahrt ist länger als drei Tage.'; } if ( $journey->{rt_duration} - and $journey->{rt_duration} > 60 * 60 * 24 ) + and $journey->{rt_duration} > 60 * 60 * 72 ) { - return 'Die Zugfahrt ist länger als 24 Stunden.'; + return 'Die Fahrt ist länger als drei Tage.'; } if ( $journey->{kmh_route} > 500 or $journey->{kmh_beeline} > 500 ) { - return 'Zugfahrten mit über 500 km/h? Schön wär\'s.'; + return 'Die berechnete Geschwindigkeit beträgt über 500 km/h.' + . ' Das wirkt unrealistisch.'; } - if ( $journey->{route} and @{ $journey->{route} } > 99 ) { + if ( $journey->{route} and @{ $journey->{route} } > 199 ) { my $stop_count = @{ $journey->{route} }; - return -"Die Zugfahrt hat $stop_count Unterwegshalte. Also ich weiß ja nicht so recht."; + return "Die Fahrt hat $stop_count Unterwegshalte. " + . ' Stimmt das wirklich?'; } if ( $journey->{edited} & 0x0010 and not $lax ) { - my @unknown_stations - = $self->{stations} - ->grep_unknown( map { $_->[0] } @{ $journey->{route} } ); + my @unknown_stations = $self->{stations}->grep_unknown( + backend_id => $journey->{backend_id}, + names => [ map { $_->[0] } @{ $journey->{route} } ] + ); if (@unknown_stations) { return 'Unbekannte Station(en): ' . join( ', ', @unknown_stations ); } @@ -1066,9 +1313,11 @@ sub get_travel_distance { my $from = $journey->{from_name}; my $from_eva = $journey->{from_eva}; my $from_latlon = $journey->{from_latlon}; + my $from_ts = $journey->{sched_dep_ts} // $journey->{rt_dep_ts}; my $to = $journey->{to_name}; my $to_eva = $journey->{to_eva}; my $to_latlon = $journey->{to_latlon}; + my $to_ts = $journey->{sched_arr_ts} // $journey->{rt_arr_ts}; my $route_ref = $journey->{route}; my $polyline_ref = $journey->{polyline}; @@ -1082,54 +1331,149 @@ sub get_travel_distance { ->warn("Journey $journey->{id} has no from_name for EVA $from_eva"); } + # Work around inconsistencies caused by a multiple EVA IDs mapping to the same station name + if ( + @{ $polyline_ref // [] } + and not List::MoreUtils::any { $_->[2] and $_->[2] == $from_eva } + @{ $polyline_ref // [] } + ) + { + $self->{log}->debug( +"Journey $journey->{id} from_eva ($from_eva) is not part of polyline" + ); + for my $entry ( @{$route_ref} ) { + if ( $entry->[0] eq $from and $entry->[1] ) { + $from_eva = $entry->[1]; + $self->{log}->debug("... setting to $from_eva"); + last; + } + } + } + if ( + @{ $polyline_ref // [] } + and not List::MoreUtils::any { $_->[2] and $_->[2] == $to_eva } + @{ $polyline_ref // [] } + ) + { + $self->{log}->debug( + "Journey $journey->{id} to_eva ($to_eva) is not part of polyline"); + for my $entry ( @{$route_ref} ) { + if ( $entry->[0] eq $to and $entry->[1] ) { + $to_eva = $entry->[1]; + $self->{log}->debug("... setting to $to_eva"); + last; + } + } + } + my $distance_polyline = 0; my $distance_intermediate = 0; - my $distance_beeline = 0; - my $skipped = 0; my $geo = GIS::Distance->new(); - my @stations = map { $_->[0] } @{$route_ref}; - my @route = after_incl { $_ eq $from } @stations; - @route = before_incl { $_ eq $to } @route; + my $distance_beeline + = $geo->distance_metal( @{$from_latlon}, @{$to_latlon} ); + + # A trip may pass the same stop multiple times. + # Thus, two criteria must be met to select the start/end of the actual route: + # * stop name or ID matches, and + # * one of: + # - arrival/departure time at the stop matches, or + # - the stop does not have arrival/departure time + # In the latter case, we still face the risk of selecting the wrong + # start/end stop. However, we have no way of finding the right one. As the + # majority of trips do not pass the same stop multiple times, it's better + # to risk having a few inaccurate distances than not calculating the + # distance for any journey that lacks sched_dep/rt_dep or + # sched_from/rt_from. + + my $route_start = first_index { + ( + ( $_->[1] and $_->[1] == $from_eva or $_->[0] eq $from ) + and ( not( defined $_->[2]{sched_dep} or defined $_->[2]{rt_dep} ) + or ( $_->[2]{sched_dep} // $_->[2]{rt_dep} ) == $from_ts ) + ) + } + @{$route_ref}; - if ( @route < 2 ) { + # Here, we need to use last_index. In case of ring lines, the first index + # will not have sched_arr/rt_arr set, but we should not select it as route + # end... + my $route_end = last_index { + ( + ( $_->[1] and $_->[1] == $to_eva or $_->[0] eq $to ) + and ( not( defined $_->[2]{sched_arr} or defined $_->[2]{rt_arr} ) + or ( $_->[2]{sched_arr} // $_->[2]{rt_arr} ) == $to_ts ) + ) + } + @{$route_ref}; - # I AM ERROR - return ( 0, 0, 0 ); + if ( not defined $route_start and defined $route_end ) { + return ( 0, 0, $distance_beeline ); } - my @polyline = after_incl { $_->[2] and $_->[2] == $from_eva } - @{ $polyline_ref // [] }; - @polyline - = before_incl { $_->[2] and $_->[2] == $to_eva } @polyline; - - my $prev_station = shift @polyline; - for my $station (@polyline) { - $distance_polyline += $geo->distance_metal( - $prev_station->[1], $prev_station->[0], - $station->[1], $station->[0] - ); - $prev_station = $station; + my %seen; + for my $stop ( @{$route_ref} ) { + if ( not defined $stop->[1] ) { + return ( 0, 0, $distance_beeline ); + } + $seen{ $stop->[1] } //= 1; + $stop->[2]{n} = $seen{ $stop->[1] }; + $seen{ $stop->[1] } += 1; + } + + # Assumption: polyline entries are always [lat, lon] or [lat, lon, stop ID] + %seen = (); + for my $entry ( @{ $polyline_ref // [] } ) { + if ( $entry->[2] ) { + $seen{ $entry->[2] } //= 1; + $entry->[3] = $seen{ $entry->[2] }; + $seen{ $entry->[2] } += 1; + } } - $prev_station = $self->{latlon_by_station}->{ shift @route }; - if ( not $prev_station ) { - return ( $distance_polyline, 0, 0 ); + $journey->{route_dep_index} = $route_start; + $journey->{route_arr_index} = $route_end; + + my @route = @{$route_ref}[ $route_start .. $route_end ]; + + # Just like the route, the polyline may contain the same stop more than + # once. So we need to select based on the seen counter. + my $poly_start = first_index { + $_->[2] and $_->[2] == $from_eva and $_->[3] == $route[0][2]{n} } + @{ $polyline_ref // [] }; + my $poly_end = first_index { + $_->[2] and $_->[2] == $to_eva and $_->[3] == $route[-1][2]{n} + } + @{ $polyline_ref // [] }; - for my $station_name (@route) { - if ( my $station = $self->{latlon_by_station}->{$station_name} ) { - $distance_intermediate += $geo->distance_metal( - $prev_station->[0], $prev_station->[1], - $station->[0], $station->[1] + if ( defined $poly_start and defined $poly_end ) { + $journey->{poly_dep_index} = $poly_start; + $journey->{poly_arr_index} = $poly_end; + my @polyline = @{$polyline_ref}[ $poly_start .. $poly_end ]; + my $prev_station = shift @polyline; + for my $station (@polyline) { + $distance_polyline += $geo->distance_metal( + $prev_station->[1], $prev_station->[0], + $station->[1], $station->[0] ); $prev_station = $station; } } - $distance_beeline = $geo->distance_metal( @{$from_latlon}, @{$to_latlon} ); + if ( defined $route[0][2]{lat} and defined $route[0][2]{lon} ) { + my $prev_station = shift @route; + for my $station (@route) { + if ( defined $station->[2]{lat} and defined $station->[2]{lon} ) { + $distance_intermediate += $geo->distance_metal( + $prev_station->[2]{lat}, $prev_station->[2]{lon}, + $station->[2]{lat}, $station->[2]{lon} + ); + $prev_station = $station; + } + } + } - return ( $distance_polyline, $distance_intermediate, - $distance_beeline, $skipped ); + return ( $distance_polyline, $distance_intermediate, $distance_beeline ); } sub grep_single { @@ -1548,7 +1892,10 @@ sub compute_stats { @inconsistencies, { conflict => { - train => $journey->{type} . ' ' + train => ( + $journey->{is_motis} ? '' : $journey->{type} + ) + . ' ' . ( $journey->{line} // $journey->{no} ), arr => epoch_to_dt( $journey->{rt_arr_ts} ) ->strftime('%d.%m.%Y %H:%M'), @@ -1574,7 +1921,8 @@ sub compute_stats { $next_departure = $journey->{rt_dep_ts}; $next_id = $journey->{id}; $next_train - = $journey->{type} . ' ' . ( $journey->{line} // $journey->{no} ),; + = ( $journey->{is_motis} ? '' : $journey->{type} ) . ' ' + . ( $journey->{line} // $journey->{no} ),; } my $ret = { km_route => $km_route, @@ -1607,6 +1955,8 @@ sub compute_stats { sub get_stats { my ( $self, %opt ) = @_; + $self->{log}->debug("get_stats"); + if ( $opt{cancelled} ) { $self->{log} ->warn('get_journey_stats called with illegal option cancelled => 1'); @@ -1633,9 +1983,12 @@ sub get_stats { ) ) { + $self->{log}->debug("got cached journey stats for $year/$month"); return $stats; } + $self->{log}->debug("computing journey stats for $year/$month"); + my $interval_start = DateTime->new( time_zone => 'Europe/Berlin', year => 2000, @@ -1694,28 +2047,57 @@ sub get_stats { return $stats; } -sub get_latest_dest_id { +sub get_latest_dest_ids { my ( $self, %opt ) = @_; my $uid = $opt{uid}; my $db = $opt{db} // $self->{pg}->db; if ( - my $id = $self->{in_transit}->get_checkout_station_id( + my ( $id, $backend_id ) = $self->{in_transit}->get_checkout_ids( uid => $uid, db => $db ) ) { - return $id; + return ( $id, $backend_id ); } - return $self->get_latest_checkout_station_id( + return $self->get_latest_checkout_ids( uid => $uid, db => $db ); } +sub get_frequent_backend_ids { + my ( $self, %opt ) = @_; + + my $uid = $opt{uid}; + my $threshold = $opt{threshold} + // DateTime->now( time_zone => 'Europe/Berlin' )->subtract( months => 4 ); + my $limit = $opt{limit} // 5; + my $db = $opt{db} //= $self->{pg}->db; + + my $res = $db->select( + 'journeys', + 'count(*) as count, backend_id', + { + user_id => $uid, + real_departure => { '>', $threshold }, + }, + { + group_by => ['backend_id'], + order_by => { -desc => 'count' }, + limit => $limit, + } + ); + + my @backend_ids = $res->hashes->map( sub { shift->{backend_id} } )->each; + + return @backend_ids; +} + +# Returns a listref of {eva, name} hashrefs for the specified backend. sub get_connection_targets { my ( $self, %opt ) = @_; @@ -1724,38 +2106,55 @@ sub get_connection_targets { // DateTime->now( time_zone => 'Europe/Berlin' )->subtract( months => 4 ); my $db = $opt{db} //= $self->{pg}->db; my $min_count = $opt{min_count} // 3; + my $dest_id = $opt{eva}; if ( $opt{destination_name} ) { - return ( $opt{destination_name} ); + return { + eva => $opt{eva}, + name => $opt{destination_name} + }; } - my $dest_id = $opt{eva} // $self->get_latest_dest_id(%opt); + my $backend_id = $opt{backend_id}; + + if ( not $dest_id ) { + ( $dest_id, $backend_id ) = $self->get_latest_dest_ids(%opt); + } if ( not $dest_id ) { return; } - my $res = $db->query( - qq{ - select - count(checkout_station_id) as count, - checkout_station_id as dest - from journeys - where user_id = ? - and checkin_station_id = ? - and real_departure > ? - group by checkout_station_id - order by count desc; - }, - $uid, + my $dest_ids = [ $dest_id, - $threshold + $self->{stations}->get_meta( + eva => $dest_id, + backend_id => $backend_id, + ) + ]; + + my $res = $db->select( + 'journeys', + 'count(checkout_station_id) as count, checkout_station_id as dest', + { + user_id => $uid, + checkin_station_id => $dest_ids, + real_departure => { '>', $threshold }, + backend_id => $opt{backend_id}, + }, + { + group_by => ['checkout_station_id'], + order_by => { -desc => 'count' } + } ); my @destinations = $res->hashes->grep( sub { shift->{count} >= $min_count } ) - ->map( sub { shift->{dest} } )->each; - @destinations = $self->{stations}->get_by_evas(@destinations); - @destinations = map { $_->{name} } @destinations; + ->map( sub { shift->{dest} } ) + ->each; + @destinations = $self->{stations}->get_by_evas( + backend_id => $opt{backend_id}, + evas => [@destinations] + ); return @destinations; } diff --git a/lib/Travelynx/Model/Stations.pm b/lib/Travelynx/Model/Stations.pm index 75b4174..5316118 100644 --- a/lib/Travelynx/Model/Stations.pm +++ b/lib/Travelynx/Model/Stations.pm @@ -1,6 +1,7 @@ package Travelynx::Model::Stations; # Copyright (C) 2022 Birte Kristina Friesel +# Copyright (C) 2025 networkException <git@nwex.de> # # SPDX-License-Identifier: AGPL-3.0-or-later @@ -14,40 +15,370 @@ sub new { return bless( \%opt, $class ); } +sub get_backend_id { + my ( $self, %opt ) = @_; + + if ( $opt{iris} ) { + + # special case + return 0; + } + if ( $opt{dbris} and $self->{backend_id}{dbris}{ $opt{dbris} } ) { + return $self->{backend_id}{dbris}{ $opt{dbris} }; + } + if ( $opt{efa} and $self->{backend_id}{efa}{ $opt{efa} } ) { + return $self->{backend_id}{efa}{ $opt{efa} }; + } + if ( $opt{hafas} and $self->{backend_id}{hafas}{ $opt{hafas} } ) { + return $self->{backend_id}{hafas}{ $opt{hafas} }; + } + if ( $opt{motis} and $self->{backend_id}{motis}{ $opt{motis} } ) { + return $self->{backend_id}{motis}{ $opt{motis} }; + } + + my $db = $opt{db} // $self->{pg}->db; + my $backend_id = 0; + + if ( $opt{dbris} ) { + $backend_id = $db->select( + 'backends', + ['id'], + { + dbris => 1, + name => $opt{dbris} + } + )->hash->{id}; + $self->{backend_id}{dbris}{ $opt{dbris} } = $backend_id; + } + elsif ( $opt{efa} ) { + $backend_id = $db->select( + 'backends', + ['id'], + { + efa => 1, + name => $opt{efa} + } + )->hash->{id}; + $self->{backend_id}{efa}{ $opt{efa} } = $backend_id; + } + elsif ( $opt{hafas} ) { + $backend_id = $db->select( + 'backends', + ['id'], + { + hafas => 1, + name => $opt{hafas} + } + )->hash->{id}; + $self->{backend_id}{hafas}{ $opt{hafas} } = $backend_id; + } + elsif ( $opt{motis} ) { + $backend_id = $db->select( + 'backends', + ['id'], + { + motis => 1, + name => $opt{motis} + } + )->hash->{id}; + $self->{backend_id}{motis}{ $opt{motis} } = $backend_id; + } + + return $backend_id; +} + +sub get_backend { + my ( $self, %opt ) = @_; + + if ( $self->{backend_cache}{ $opt{backend_id} } ) { + return $self->{backend_cache}{ $opt{backend_id} }; + } + + my $db = $opt{db} // $self->{pg}->db; + my $ret = $db->select( + 'backends', + '*', + { + id => $opt{backend_id}, + } + )->hash; + + $self->{backend_cache}{ $opt{backend_id} } = $ret; + + return $ret; +} + +sub get_backends { + my ( $self, %opt ) = @_; + + $opt{db} //= $self->{pg}->db; + + my $res = $opt{db}->select( 'backends', + [ 'id', 'name', 'dbris', 'efa', 'hafas', 'iris', 'motis' ] ); + my @ret; + + while ( my $row = $res->hash ) { + push( + @ret, + { + id => $row->{id}, + name => $row->{name}, + dbris => $row->{dbris}, + efa => $row->{efa}, + hafas => $row->{hafas}, + iris => $row->{iris}, + motis => $row->{motis}, + } + ); + } + + return @ret; +} + +# Slow for MOTIS backends sub add_or_update { my ( $self, %opt ) = @_; - my $stop = $opt{stop}; - my $source = 1; - my $db = $opt{db} // $self->{pg}->db; + my $stop = $opt{stop}; + $opt{db} //= $self->{pg}->db; - if ( my $s = $self->get_by_eva( $stop->eva, db => $db ) ) { - if ( $source == 1 and $s->{source} == 0 and not $s->{archived} ) { + $opt{backend_id} //= $self->get_backend_id(%opt); + + if ( $opt{dbris} ) { + if ( + my $s = $self->get_by_eva( + $stop->eva, + db => $opt{db}, + backend_id => $opt{backend_id} + ) + ) + { + $opt{db}->update( + 'stations', + { + name => $stop->name, + lat => $stop->lat, + lon => $stop->lon, + archived => 0 + }, + { + eva => $stop->eva, + source => $opt{backend_id} + } + ); return; } - $db->update( + $opt{db}->insert( 'stations', { + eva => $stop->eva, name => $stop->name, lat => $stop->lat, lon => $stop->lon, - source => $source, + source => $opt{backend_id}, + archived => 0 + } + ); + return; + } + + if ( $opt{efa} ) { + if ( + my $s = $self->get_by_eva( + $stop->id_num, + db => $opt{db}, + backend_id => $opt{backend_id} + ) + ) + { + $opt{db}->update( + 'stations', + { + name => $stop->full_name, + lat => $stop->latlon->[0], + lon => $stop->latlon->[1], + archived => 0 + }, + { + eva => $stop->id_num, + source => $opt{backend_id} + } + ); + return; + } + if (not $stop->latlon) { + die('Backend Error: Stop "' . $stop->full_name . '" has no geo coordinates'); + } + $opt{db}->insert( + 'stations', + { + eva => $stop->id_num, + name => $stop->full_name, + lat => $stop->latlon->[0], + lon => $stop->latlon->[1], + source => $opt{backend_id}, + archived => 0 + } + ); + return; + } + + if ( $opt{motis} ) { + if ( + my $s = $self->get_by_external_id( + external_id => $stop->id, + db => $opt{db}, + backend_id => $opt{backend_id} + ) + ) + { + $opt{db}->update( + 'stations', + { + name => $stop->name, + lat => $stop->lat, + lon => $stop->lon, + archived => 0 + }, + { + eva => $s->{eva}, + source => $opt{backend_id} + } + ); + + # MOTIS backends do not provide a numeric ID, so we set our ID here. + $stop->{eva} = $s->{eva}; + return; + } + + my $s = $opt{db}->query( + qq { + with new_station as ( + insert into stations_external_ids (backend_id, external_id) + values (?, ?) + returning eva, backend_id + ) + + insert into stations (eva, name, lat, lon, source, archived) + values ((select eva from new_station), ?, ?, ?, (select backend_id from new_station), ?) + returning * + }, + ( + $opt{backend_id}, $stop->id, $stop->name, + $stop->lat, $stop->lon, 0, + ) + ); + + # MOTIS backends do not provide a numeric ID, so we set our ID here. + $stop->{eva} = $s->hash->{eva}; + return; + } + + my $loc = $stop->loc; + if ( + my $s = $self->get_by_eva( + $loc->eva, + db => $opt{db}, + backend_id => $opt{backend_id} + ) + ) + { + $opt{db}->update( + 'stations', + { + name => $loc->name, + lat => $loc->lat, + lon => $loc->lon, archived => 0 }, - { eva => $stop->eva } + { + eva => $loc->eva, + source => $opt{backend_id} + } ); return; } - $db->insert( + $opt{db}->insert( 'stations', { - eva => $stop->eva, - name => $stop->name, - lat => $stop->lat, - lon => $stop->lon, - source => $source, + eva => $loc->eva, + name => $loc->name, + lat => $loc->lat, + lon => $loc->lon, + source => $opt{backend_id}, archived => 0 } ); + + return; +} + +sub add_meta { + my ( $self, %opt ) = @_; + my $eva = $opt{eva}; + my @meta = @{ $opt{meta} }; + + $opt{db} //= $self->{pg}->db; + $opt{backend_id} //= $self->get_backend_id(%opt); + + for my $meta (@meta) { + if ( $meta != $eva ) { + $opt{db}->insert( + 'related_stations', + { + eva => $eva, + meta => $meta, + backend_id => $opt{backend_id}, + }, + { on_conflict => undef } + ); + } + } +} + +sub get_db_iterator { + my ($self) = @_; + + return $self->{pg}->db->select( 'stations_str', '*' ); +} + +sub get_meta { + my ( $self, %opt ) = @_; + my $db = $opt{db} // $self->{pg}->db; + my $eva = $opt{eva}; + + $opt{backend_id} //= $self->get_backend_id( %opt, db => $db ); + + my $res = $db->select( + 'related_stations', + ['meta'], + { + eva => $eva, + backend_id => $opt{backend_id} + } + ); + my @ret; + + while ( my $row = $res->hash ) { + push( @ret, $row->{meta} ); + } + + return @ret; +} + +sub get_for_autocomplete { + my ( $self, %opt ) = @_; + + $opt{backend_id} //= $self->get_backend_id(%opt); + + my $res = $self->{pg} + ->db->select( 'stations', ['name'], { source => $opt{backend_id} } ); + my %ret; + + while ( my $row = $res->hash ) { + $ret{ $row->{name} } = undef; + } + + return \%ret; } # Fast @@ -58,52 +389,88 @@ sub get_by_eva { return; } - my $db = $opt{db} // $self->{pg}->db; + $opt{db} //= $self->{pg}->db; + $opt{backend_id} //= $self->get_backend_id(%opt); - return $db->select( 'stations', '*', { eva => $eva } )->hash; + return $opt{db}->select( + 'stations', + '*', + { + eva => $eva, + source => $opt{backend_id} + } + )->hash; } -# Fast -sub get_by_evas { - my ( $self, @evas ) = @_; +# Slow +sub get_by_external_id { + my ( $self, %opt ) = @_; - my @ret - = $self->{pg}->db->select( 'stations', '*', { eva => { '=', \@evas } } ) - ->hashes->each; - return @ret; + if ( not $opt{external_id} ) { + return; + } + + $opt{db} //= $self->{pg}->db; + $opt{backend_id} //= $self->get_backend_id(%opt); + + return $opt{db}->select( + 'stations_with_external_ids', + '*', + { + external_id => $opt{external_id}, + source => $opt{backend_id}, + } + )->hash; } -# Slow -sub get_latlon_by_name { +# Fast +sub get_by_evas { my ( $self, %opt ) = @_; - my $db = $opt{db} // $self->{pg}->db; + $opt{db} //= $self->{pg}->db; + $opt{backend_id} //= $self->get_backend_id(%opt); - my %location; - my $res = $db->select( 'stations', [ 'name', 'lat', 'lon' ] ); - while ( my $row = $res->hash ) { - $location{ $row->{name} } = [ $row->{lat}, $row->{lon} ]; - } - return \%location; + my @ret = $self->{pg}->db->select( + 'stations', + '*', + { + eva => { '=', $opt{evas} }, + source => $opt{backend_id} + } + )->hashes->each; + return @ret; } # Slow sub get_by_name { my ( $self, $name, %opt ) = @_; - my $db = $opt{db} // $self->{pg}->db; + $opt{db} //= $self->{pg}->db; + $opt{backend_id} //= $self->get_backend_id(%opt); - return $db->select( 'stations', '*', { name => $name }, { limit => 1 } ) - ->hash; + return $opt{db}->select( + 'stations', + '*', + { + name => $name, + source => $opt{backend_id} + }, + { limit => 1 } + )->hash; } # Slow sub get_by_names { - my ( $self, @names ) = @_; + my ( $self, %opt ) = @_; - my @ret - = $self->{pg}->db->select( 'stations', '*', { name => { '=', \@names } } ) - ->hashes->each; + my @ret = $self->{pg}->db->select( + 'stations', + '*', + { + name => { '=', $opt{names} }, + source => $opt{backend_id} + } + )->hashes->each; return @ret; } @@ -111,16 +478,27 @@ sub get_by_names { sub get_by_ds100 { my ( $self, $ds100, %opt ) = @_; - my $db = $opt{db} // $self->{pg}->db; + $opt{db} //= $self->{pg}->db; + $opt{backend_id} //= $self->get_backend_id(%opt); - return $db->select( 'stations', '*', { ds100 => $ds100 }, { limit => 1 } ) - ->hash; + return $opt{db}->select( + 'stations', + '*', + { + ds100 => $ds100, + source => $opt{backend_id} + }, + { limit => 1 } + )->hash; } # Can be slow sub search { my ( $self, $identifier, %opt ) = @_; + $opt{db} //= $self->{pg}->db; + $opt{backend_id} //= $self->get_backend_id(%opt); + if ( $identifier =~ m{ ^ \d+ $ }x ) { return $self->get_by_eva( $identifier, %opt ) // $self->get_by_ds100( $identifier, %opt ) @@ -133,10 +511,10 @@ sub search { # Slow sub grep_unknown { - my ( $self, @stations ) = @_; + my ( $self, %opt ) = @_; - my %station = map { $_->{name} => 1 } $self->get_by_names(@stations); - my @unknown_stations = grep { not $station{$_} } @stations; + my %station = map { $_->{name} => 1 } $self->get_by_names(%opt); + my @unknown_stations = grep { not $station{$_} } @{ $opt{names} }; return @unknown_stations; } diff --git a/lib/Travelynx/Model/Traewelling.pm b/lib/Travelynx/Model/Traewelling.pm index 25648cc..608da15 100644 --- a/lib/Travelynx/Model/Traewelling.pm +++ b/lib/Travelynx/Model/Traewelling.pm @@ -224,6 +224,7 @@ sub get_pushable_accounts { join in_transit_str as i on t.user_id = i.user_id where t.push_sync = True and i.arr_eva is not null + and i.backend_id = (select id from backends where dbris = true and name = 'bahn.de') and i.cancelled = False } ); diff --git a/lib/Travelynx/Model/Users.pm b/lib/Travelynx/Model/Users.pm index 4b108d4..3ef7f33 100644 --- a/lib/Travelynx/Model/Users.pm +++ b/lib/Travelynx/Model/Users.pm @@ -40,14 +40,6 @@ my %predicate_atoi = ( is_blocked_by => 3, ); -my @sb_templates = ( - undef, - [ 'DBF', 'https://dbf.finalrewind.org/{name}?rt=1#{tt}{tn}' ], - [ 'bahn.expert', 'https://bahn.expert/{name}#{id}' ], - [ 'DBF HAFAS', 'https://dbf.finalrewind.org/{name}?rt=1&hafas=1#{tt}{tn}' ], - [ 'bahn.expert/regional', 'https://bahn.expert/regional/{name}#{id}' ], -); - my %token_id = ( status => 1, history => 2, @@ -213,6 +205,25 @@ sub get_privacy_by { return; } +sub set_backend { + my ( $self, %opt ) = @_; + $opt{db} //= $self->{pg}->db; + + $opt{db}->update( + 'users', + { backend_id => $opt{backend_id} }, + { id => $opt{uid} } + ); +} + +sub set_language { + my ( $self, %opt ) = @_; + $opt{db} //= $self->{pg}->db; + + $opt{db} + ->update( 'users', { language => $opt{language} }, { id => $opt{uid} } ); +} + sub set_privacy { my ( $self, %opt ) = @_; my $db = $opt{db} // $self->{pg}->db; @@ -409,20 +420,22 @@ sub get { my $uid = $opt{uid}; my $user = $db->select( - 'users', - 'id, name, status, public_level, email, ' - . 'external_services, accept_follows, notifications, ' + 'users_with_backend', + 'id, name, status, public_level, email, language, ' + . 'accept_follows, notifications, ' . 'extract(epoch from registered_at) as registered_at_ts, ' . 'extract(epoch from last_seen) as last_seen_ts, ' - . 'extract(epoch from deletion_requested) as deletion_requested_ts', + . 'extract(epoch from deletion_requested) as deletion_requested_ts, ' + . 'backend_id, backend_name, dbris, efa, hafas, motis', { id => $uid } )->hash; if ($user) { return { - id => $user->{id}, - name => $user->{name}, - status => $user->{status}, - notifications => $user->{notifications}, + id => $user->{id}, + name => $user->{name}, + languages => [ split( qr{[|]}, $user->{language} // q{} ) ], + status => $user->{status}, + notifications => $user->{notifications}, accept_follows => $user->{accept_follows} == 2 ? 1 : 0, accept_follow_requests => $user->{accept_follows} == 1 ? 1 : 0, default_visibility => $user->{public_level} & 0x7f, @@ -435,12 +448,8 @@ sub get { past_status => $user->{public_level} & 0x08000 ? 1 : 0, past_all => $user->{public_level} & 0x10000 ? 1 : 0, email => $user->{email}, - sb_name => $user->{external_services} - ? $sb_templates[ $user->{external_services} & 0x07 ][0] - : undef, - sb_template => $user->{external_services} - ? $sb_templates[ $user->{external_services} & 0x07 ][1] - : undef, + sb_template => +'https://dbf.finalrewind.org/{name}?dbris={dbris}&efa={efa}&hafas={hafas}&motis={motis}#{id_or_tttn}', registered_at => DateTime->from_epoch( epoch => $user->{registered_at_ts}, time_zone => 'Europe/Berlin' @@ -455,6 +464,12 @@ sub get { time_zone => 'Europe/Berlin' ) : undef, + backend_id => $user->{backend_id}, + backend_name => $user->{backend_name}, + backend_dbris => $user->{dbris}, + backend_efa => $user->{efa}, + backend_hafas => $user->{hafas}, + backend_motis => $user->{motis}, }; } return undef; @@ -567,7 +582,6 @@ sub delete { $res{transit} = $db->delete( 'in_transit', { user_id => $uid } ); $res{hooks} = $db->delete( 'webhooks', { user_id => $uid } ); $res{trwl} = $db->delete( 'traewelling', { user_id => $uid } ); - $res{lt} = $db->delete( 'localtransit', { user_id => $uid } ); $res{password} = $db->delete( 'pending_passwords', { user_id => $uid } ); $res{relations} = $db->delete( 'relations', [ { subject_id => $uid }, { object_id => $uid } ] ); @@ -651,52 +665,12 @@ sub use_history { my $uid = $opt{uid}; my $value = $opt{set}; - if ( $opt{destinations} ) { - $db->insert( - 'localtransit', - { - user_id => $uid, - data => - JSON->new->encode( { destinations => $opt{destinations} } ) - }, - { on_conflict => \'(user_id) do update set data = EXCLUDED.data' } - ); - } - if ($value) { $db->update( 'users', { use_history => $value }, { id => $uid } ); } else { - if ( $opt{with_local_transit} ) { - my $res = $db->select( - 'user_transit', - [ 'use_history', 'data' ], - { id => $uid } - )->expand->hash; - return ( $res->{use_history}, $res->{data}{destinations} // [] ); - } - else { - return $db->select( 'users', ['use_history'], { id => $uid } ) - ->hash->{use_history}; - } - } -} - -sub use_external_services { - my ( $self, %opt ) = @_; - my $db = $opt{db} // $self->{pg}->db; - my $uid = $opt{uid}; - my $value = $opt{set}; - - if ( defined $value ) { - if ( $value < 0 or $value > 4 ) { - $value = 0; - } - $db->update( 'users', { external_services => $value }, { id => $uid } ); - } - else { - return $db->select( 'users', ['external_services'], { id => $uid } ) - ->hash->{external_services}; + return $db->select( 'users', ['use_history'], { id => $uid } ) + ->hash->{use_history}; } } |