regularly generate statistics in the background1.18.11

4 files changed, 109 insertions, 26 deletions

diff --git a/lib/Travelynx/Command/maintenance.pm b/lib/Travelynx/Command/maintenance.pm
index dd65ac5..d8b8e44 100644
--- a/lib/Travelynx/Command/maintenance.pm
+++ b/lib/Travelynx/Command/maintenance.pm
@@ -1,4 +1,5 @@
 package Travelynx::Command::maintenance;
+
 # Copyright (C) 2020 Daniel Friesel
 #
 # SPDX-License-Identifier: MIT
@@ -136,6 +137,22 @@ sub run {
 
 	$tx->commit;
 
+	# Computing stats may take a while, but we've got all time in the
+	# world here. This means users won't have to wait when loading their
+	# own journey log.
+	say 'Generating missing stats ...';
+	for
+	  my $user ( $db->select( 'users', ['id'], { status => 1 } )->hashes->each )
+	{
+		$tx = $db->begin;
+		$self->app->journeys->generate_missing_stats( uid => $user->{id} );
+		$self->app->journeys->get_stats(
+			uid  => $user->{id},
+			year => $now->year
+		);
+		$tx->commit;
+	}
+
 	# Add estimated polylines to journeys logged before 2020-01-28
 
 	$tx = $db->begin;
diff --git a/lib/Travelynx/Command/work.pm b/lib/Travelynx/Command/work.pm
index 16b41b2..27614ea 100644
--- a/lib/Travelynx/Command/work.pm
+++ b/lib/Travelynx/Command/work.pm
@@ -288,19 +288,6 @@ sub run {
 		$self->app->traewelling_api->checkin( %{$candidate},
 			trip_id => $trip_id );
 	}
-
-	# Computing yearly stats may take a while, but we've got all time in the
-	# world here. This means users won't have to wait when loading their
-	# own by-year journey log.
-	for my $user ( $db->select( 'users', 'id', { status => 1 } )->hashes->each )
-	{
-		$self->app->journeys->get_stats(
-			uid  => $user->{id},
-			year => $now->year
-		);
-	}
-
-	# TODO wait until all background jobs have terminated
 }
 
 1;
diff --git a/lib/Travelynx/Model/JourneyStatsCache.pm b/lib/Travelynx/Model/JourneyStatsCache.pm
index 145208d..3a2a1c3 100755
--- a/lib/Travelynx/Model/JourneyStatsCache.pm
+++ b/lib/Travelynx/Model/JourneyStatsCache.pm
@@ -1,4 +1,5 @@
 package Travelynx::Model::JourneyStatsCache;
+
 # Copyright (C) 2020 Daniel Friesel
 #
 # SPDX-License-Identifier: MIT
@@ -19,7 +20,7 @@ sub new {
 sub add {
 	my ( $self, %opt ) = @_;
 
-	my $db  = $opt{db} // $self->{pg}->db;
+	my $db = $opt{db} // $self->{pg}->db;
 
 	eval {
 		$db->insert(
@@ -28,20 +29,20 @@ sub add {
 				user_id => $opt{uid},
 				year    => $opt{year},
 				month   => $opt{month},
-				data    => JSON->new->encode($opt{stats}),
+				data    => JSON->new->encode( $opt{stats} ),
 			}
 		);
 	};
 	if ( my $err = $@ ) {
-		if ( $err =~ m{duplicate key value violates unique constraint} )
-		{
-				# If a user opens the same history page several times in
-				# short succession, there is a race condition where several
-				# Mojolicious workers execute this helper, notice that there is
-				# no up-to-date history, compute it, and insert it using the
-				# statement above. This will lead to a uniqueness violation
-				# in each successive insert. However, this is harmless, and
-				# thus ignored.
+		if ( $err =~ m{duplicate key value violates unique constraint} ) {
+
+			# If a user opens the same history page several times in
+			# short succession, there is a race condition where several
+			# Mojolicious workers execute this helper, notice that there is
+			# no up-to-date history, compute it, and insert it using the
+			# statement above. This will lead to a uniqueness violation
+			# in each successive insert. However, this is harmless, and
+			# thus ignored.
 		}
 		else {
 			# Otherwise we probably have a problem.
@@ -53,7 +54,7 @@ sub add {
 sub get {
 	my ( $self, %opt ) = @_;
 
-	my $db  = $opt{db} // $self->{pg}->db;
+	my $db = $opt{db} // $self->{pg}->db;
 
 	my $stats = $db->select(
 		'journey_stats',
@@ -97,4 +98,25 @@ sub invalidate {
 	);
 }
 
+sub get_yyyymm_having_stats {
+	my ( $self, %opt ) = @_;
+	my $uid = $opt{uid};
+	my $db  = $opt{db} // $self->{pg}->db;
+	my $res = $db->select(
+		'journey_stats',
+		[ 'year', 'month' ],
+		{ user_id  => $uid },
+		{ order_by => { -asc => [ 'year', 'month' ] } }
+	);
+
+	my @ret;
+	for my $row ( $res->hashes->each ) {
+		if ( $row->{month} != 0 ) {
+			push( @ret, [ $row->{year}, $row->{month} ] );
+		}
+	}
+
+	return @ret;
+}
+
 1;
diff --git a/lib/Travelynx/Model/Journeys.pm b/lib/Travelynx/Model/Journeys.pm
index f5f5424..e09ca7d 100755
--- a/lib/Travelynx/Model/Journeys.pm
+++ b/lib/Travelynx/Model/Journeys.pm
@@ -763,6 +763,62 @@ sub get_months_for_year {
 	return @ret;
 }
 
+sub get_yyyymm_having_journeys {
+	my ( $self, %opt ) = @_;
+	my $uid = $opt{uid};
+	my $db  = $opt{db} // $self->{pg}->db;
+	my $res = $db->select(
+		'journeys',
+		"distinct to_char(real_departure, 'YYYY.MM') as yearmonth",
+		{ user_id  => $uid },
+		{ order_by => { -asc => 'yearmonth' } }
+	);
+
+	my @ret;
+	for my $row ( $res->hashes->each ) {
+		push( @ret, [ split( qr{[.]}, $row->{yearmonth} ) ] );
+	}
+
+	return @ret;
+}
+
+sub generate_missing_stats {
+	my ( $self, %opt ) = @_;
+	my $uid            = $opt{uid};
+	my $db             = $opt{db} // $self->{pg}->db;
+	my @journey_months = $self->get_yyyymm_having_journeys(
+		uid => $uid,
+		db  => $db
+	);
+	my @stats_months = $self->stats_cache->get_yyyymm_having_stats(
+		uid => $uid,
+		$db => $db
+	);
+
+	my $stats_index = 0;
+
+	for my $journey_index ( 0 .. $#journey_months ) {
+		if (    $stats_index < @stats_months
+			and $journey_months[$journey_index][0]
+			== $stats_months[$stats_index][0]
+			and $journey_months[$journey_index][1]
+			== $stats_months[$stats_index][1] )
+		{
+			$stats_index++;
+		}
+		else {
+			my ( $year, $month ) = @{ $journey_months[$journey_index] };
+			$self->get_stats(
+				uid        => $uid,
+				db         => $db,
+				year       => $year,
+				month      => $month,
+				write_only => 1
+			);
+		}
+	}
+}
+
 sub get_nav_months {
 	my ( $self, %opt ) = @_;
 
@@ -1048,7 +1104,8 @@ sub get_stats {
 	# checks out of a train or manually edits/adds a journey.
 
 	if (
-		my $stats = $self->stats_cache->get(
+		not $opt{write_only}
+		and my $stats = $self->stats_cache->get(
 			uid   => $uid,
 			db    => $db,
 			year  => $year,