public async static Task <bool> UpdateJournalIntegrationDataAsync(MSSQLDB db, long journalId, string integrationKey, IntegrationJournalData integrationJournalData)
{
return((await db.ExecuteNonQueryAsync(
"UPDATE user_journal SET integration_data = JSON_MODIFY(ISNULL(integration_data, JSON_QUERY('{}')), '$.\"" + integrationKey + "\"', JSON_QUERY(@integration_data)) WHERE journal_id = @journal_id",
new SqlParameter("journal_id", journalId),
new SqlParameter("integration_data", JsonSerializer.Serialize(integrationJournalData))
)) > 0);
}
public static async Task SendLoginNotification(MSSQLDB db, IConfiguration configuration, Profile user)
{
var email = @"Hi there!
This is an automated email, since you have logged in to Journal Limpet at least once.
I'm sorry that I have to send you this email, but we need you to log in to Journal Limpet <https://journal-limpet.com> again.
.. at least if you want us to be able to continue:
- Storing your Elite: Dangerous journals
- Sync progress with other applications
And if you don't want us to sync your account any longer, we'll delete your account after 6 months from your last fetched journal.
This is the only email we will ever send you (every time that you need to login)
Regards,
NoLifeKing85
Journal Limpet";
var htmlEmail = @"<html>
<head></head>
<body>
Hi there!<br />
<br />
This is an automated email, since you have logged in to <b>Journal Limpet</b> at least once.<br />
<br />
I'm sorry that I have to send you this email, but we need you to log in to <a href=""https://journal-limpet.com/Login"" target=""_blank"">Journal Limpet</a> again.<br />
<br />
.. at least if you want us to be able to continue:<br />
- Storing your Elite: Dangerous journals<br />
- Sync progress with other applications<br />
<br />
And if you don't want us to sync your account any longer, we'll delete your account after 6 months from your last fetched journal.<br />
<br />
This is the only email we will ever send you (every time that you need to login)<br />
<br />
Regards,<br />
NoLifeKing85<br />
<a href=""https://journal-limpet.com/"" target=""_blank"">Journal Limpet</a>
</body></html>";
var sendgridClient = new SendGridClient(configuration["SendGrid:ApiKey"]);
var mail = MailHelper.CreateSingleEmail(
new EmailAddress("*****@*****.**", "Journal Limpet"),
new EmailAddress(user.NotificationEmail),
"Login needed for further journal storage",
email,
htmlEmail
);
await sendgridClient.SendEmailAsync(mail);
await db.ExecuteNonQueryAsync("UPDATE user_profile SET last_notification_mail = GETUTCDATE() WHERE user_identifier = @userIdentifier",
new SqlParameter("@userIdentifier", user.UserIdentifier)
);
}
public async Task InsertOrUpdateSystemAsync(EDSystemData systemData)
{
var jsPos = JsonSerializer.Serialize(systemData.Coordinates);
await _db.ExecuteNonQueryAsync($@"UPDATE EliteSystem WITH (UPDLOCK, SERIALIZABLE) SET StarSystem = @starSystem, StarPos = @starPos WHERE SystemAddress = @systemAddress;
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO EliteSystem (SystemAddress, StarSystem, StarPos) VALUES (@systemAddress, @starSystem, @starPos);
END;", new SqlParameter("systemAddress", systemData.Id64), new SqlParameter("starSystem", systemData.Name), new SqlParameter("starPos", jsPos));
}
private static async Task ExecuteBatchInsert(List <string> batchInserts, MSSQLDB db)
{
var batch = $@"BEGIN TRANSACTION;
INSERT INTO EliteSystem (SystemAddress, StarSystem, StarPos)
VALUES
{string.Join(",\n", batchInserts)}
COMMIT TRANSACTION";
await db.ExecuteNonQueryAsync(batch);
batchInserts.Clear();
}
public async Task OnPostAsync()
{
var profile = await _db.ExecuteSingleRowAsync <Profile>("SELECT * FROM user_profile WHERE user_identifier = @user_identifier", new SqlParameter("user_identifier", User.Identity.Name));
var integrationSettings = profile.IntegrationSettings;
integrationSettings["EDSM"] = EDSM.AsJsonElement();
integrationSettings["Canonn R&D"] = CanonnRD.AsJsonElement();
var integrationJson = JsonSerializer.Serialize(integrationSettings);
await _db.ExecuteNonQueryAsync(
"UPDATE user_profile SET notification_email = @notification_email, integration_settings = @integration_settings, send_to_eddn = @send_to_eddn WHERE user_identifier = @user_identifier",
new SqlParameter("user_identifier", User.Identity.Name),
new SqlParameter("notification_email", NotificationEmail ?? string.Empty),
new SqlParameter("integration_settings", integrationJson),
new SqlParameter("send_to_eddn", EDDNEnabled)
);
}
public async Task <IActionResult> Authenticate()
{
var code = Request.Query["code"];
var state = Request.Query["state"];
if (!_memoryCache.TryGetValue("frontierLogin-" + HttpContext.Connection.RemoteIpAddress.ToString(), out string storedState))
{
return(BadRequest("Could not find login token, try again"));
}
if (state != storedState)
{
return(Unauthorized("Invalid state, please relogin"));
}
var redirectUrl = string.Format("{0}://{1}{2}", Request.Scheme, Request.Host, Url.Content("~/api/journal/authenticate"));
using var c = new HttpClient();
var formData = new Dictionary <string, string>();
formData.Add("grant_type", "authorization_code");
formData.Add("code", code);
formData.Add("client_id", _configuration["EliteDangerous:ClientId"]);
formData.Add("client_secret", _configuration["EliteDangerous:ClientSecret"]);
formData.Add("state", state);
formData.Add("redirect_uri", redirectUrl);
var result = await c.PostAsync("https://auth.frontierstore.net/token", new FormUrlEncodedContent(formData));
var tokenInfo = JsonSerializer.Deserialize <OAuth2Response>(await result.Content.ReadAsStringAsync());
if (result.IsSuccessStatusCode)
{
c.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", tokenInfo.AccessToken);
result = await c.GetAsync("https://auth.frontierstore.net/me");
var profile = JsonSerializer.Deserialize <FrontierProfile>(await result.Content.ReadAsStringAsync());
var settings = new Settings
{
AuthToken = tokenInfo.AccessToken,
TokenExpiration = DateTimeOffset.UtcNow.AddSeconds(tokenInfo.ExpiresIn),
RefreshToken = tokenInfo.RefreshToken,
FrontierProfile = profile
};
// Move this so a service later
var matchingUser = (await _db.ExecuteListAsync <Profile>(@"
SELECT *
FROM user_profile
WHERE JSON_VALUE(user_settings, '$.FrontierProfile.customer_id') = @customerId",
new SqlParameter("customerId", profile.CustomerId))
).FirstOrDefault();
if (matchingUser != null)
{
// Update user with new token info
await _db.ExecuteNonQueryAsync("UPDATE user_profile SET user_settings = @settings, last_notification_mail = NULL, skip_download = 0 WHERE user_identifier = @userIdentifier",
new SqlParameter("settings", JsonSerializer.Serialize(settings)),
new SqlParameter("userIdentifier", matchingUser.UserIdentifier)
);
matchingUser.UserSettings = settings;
}
else
{
// Create new user
matchingUser = await _db.ExecuteSingleRowAsync <Profile>("INSERT INTO user_profile (user_settings) OUTPUT INSERTED.* VALUES (@settings)",
new SqlParameter("settings", JsonSerializer.Serialize(settings))
);
var userCount = await _db.ExecuteScalarAsync <long>("SELECT COUNT_BIG(user_identifier) FROM user_profile WHERE deleted = 0");
await SSEActivitySender.SendGlobalActivityAsync("A new user has registered!", $"We now have {userCount:N0} users registered!");
await SSEActivitySender.SendStatsActivityAsync(_db);
BackgroundJob.Enqueue(() => JournalDownloader.DownloadJournalAsync(matchingUser.UserIdentifier, null));
}
var claims = new List <Claim>()
{
new Claim(ClaimTypes.Name, matchingUser.UserIdentifier.ToString())
};
var claimsIdentity = new ClaimsIdentity(claims, CookieAuthenticationDefaults.AuthenticationScheme);
var authProperties = new AuthenticationProperties
{
AllowRefresh = true,
IsPersistent = false,
IssuedUtc = DateTimeOffset.UtcNow
};
await HttpContext.SignInAsync(CookieAuthenticationDefaults.AuthenticationScheme, new ClaimsPrincipal(claimsIdentity), authProperties);
return(LocalRedirect("~/Index"));
}
else
{
return(new JsonResult(await result.Content.ReadAsStringAsync()));
}
}
public static async Task RefreshUserTokensAsync(PerformContext context)
{
context.WriteLine("Looking for tokens to refresh!");
using (var scope = Startup.ServiceProvider.CreateScope())
{
MSSQLDB db = scope.ServiceProvider.GetRequiredService <MSSQLDB>();
IConfiguration configuration = scope.ServiceProvider.GetRequiredService <IConfiguration>();
var soonExpiringUsers = await db.ExecuteListAsync <Shared.Models.User.Profile>(
@"SELECT *
FROM user_profile
WHERE DATEDIFF(MINUTE, GETUTCDATE(), CAST(JSON_VALUE(user_settings, '$.TokenExpiration') as DATETIMEOFFSET)) < 10
AND last_notification_mail IS NULL
AND deleted = 0"
);
context.WriteLine($"Found {soonExpiringUsers.Count} user(s) to refresh tokens for");
IHttpClientFactory _hcf = scope.ServiceProvider.GetRequiredService <IHttpClientFactory>();
var hc = _hcf.CreateClient();
foreach (var user in soonExpiringUsers.WithProgress(context))
{
var res = await hc.PostAsync("https://auth.frontierstore.net/token",
new FormUrlEncodedContent(new Dictionary <string, string>
{
{ "grant_type", "refresh_token" },
{ "refresh_token", user.UserSettings.RefreshToken.ToString() },
{ "client_id", configuration["EliteDangerous:ClientId"] }
})
);
if (!res.IsSuccessStatusCode)
{
// The user is not authorized to perform more automatic refreshes of the token
// Send notification to the user that they need to re-login if they want to keep getting their journals stored
if (!string.IsNullOrWhiteSpace(user.NotificationEmail))
{
await SendLoginNotificationMethod.SendLoginNotification(db, configuration, user);
}
}
else
{
// We managed to grab a new token, lets save it!
var tokenInfo = JsonSerializer.Deserialize <OAuth2Response>(await res.Content.ReadAsStringAsync());
var settings = new Settings
{
AuthToken = tokenInfo.AccessToken,
TokenExpiration = DateTimeOffset.UtcNow.AddSeconds(tokenInfo.ExpiresIn),
RefreshToken = tokenInfo.RefreshToken,
FrontierProfile = user.UserSettings.FrontierProfile
};
// Update user with new token info
await db.ExecuteNonQueryAsync("UPDATE user_profile SET user_settings = @settings, last_notification_mail = NULL WHERE user_identifier = @userIdentifier",
new SqlParameter("@settings", JsonSerializer.Serialize(settings)),
new SqlParameter("@userIdentifier", user.UserIdentifier)
);
}
}
}
}
static async Task <(HttpStatusCode code, HttpResponseMessage message)> GetJournalAsync(DateTime journalDate, Shared.Models.User.Profile user, MSSQLDB db, HttpClient hc, MinioClient minioClient, DiscordWebhook discord)
{
var oldJournalRow = await db.ExecuteListAsync <UserJournal>(
"SELECT TOP 1 * FROM user_journal WHERE user_identifier = @user_identifier AND journal_date = @journal_date",
new SqlParameter("user_identifier", user.UserIdentifier),
new SqlParameter("journal_date", journalDate)
);
if (oldJournalRow.Count > 1)
{
throw new TooManyOldJournalItemsException(journalDate, user.UserIdentifier);
}
var previousRow = oldJournalRow.FirstOrDefault();
if (previousRow?.CompleteEntry ?? false)
{
return(HttpStatusCode.OK, null);
}
var pollicy = Policy <HttpResponseMessage>
.Handle <HttpRequestException>()
.OrResult(r => !r.IsSuccessStatusCode)
.OrResult(r => r.StatusCode == HttpStatusCode.PartialContent)
.WaitAndRetryAsync(100, attempt => TimeSpan.FromSeconds(5));
var journalRequest = await pollicy.ExecuteAsync(() => hc.GetAsync($"/journal/{journalDate.Year}/{journalDate.Month}/{journalDate.Day}"));
var journalContent = await journalRequest.Content.ReadAsStringAsync();
if (!journalRequest.IsSuccessStatusCode || journalRequest.StatusCode == HttpStatusCode.PartialContent)
{
return(journalRequest.StatusCode, journalRequest);
}
var journalRows = journalContent.Trim().Split('\n', StringSplitOptions.RemoveEmptyEntries);
bool updateFileOnS3 = (previousRow?.LastProcessedLineNumber ?? 0) != journalRows.Length && (previousRow?.LastProcessedLine != (journalRows.LastOrDefault() ?? string.Empty)) && journalContent.Trim() != "{}";
if (!string.IsNullOrWhiteSpace(journalContent) && journalContent.Trim() != "{}")
{
var firstValidRow = string.Empty;
foreach (var row in journalRows)
{
try
{
_ = JsonDocument.Parse(row).RootElement;
firstValidRow = row;
break;
}
catch
{
}
}
if (!string.IsNullOrWhiteSpace(firstValidRow))
{
try
{
var row = JsonDocument.Parse(firstValidRow).RootElement;
var apiFileHeader = new
{
Timestamp = row.GetProperty("timestamp").GetString(),
Event = "JournalLimpetFileheader",
Description = "Missing fileheader from cAPI journal"
};
var serializedApiFileHeader = JsonSerializer.Serialize(apiFileHeader, new JsonSerializerOptions {
PropertyNamingPolicy = JsonNamingPolicy.CamelCase
});
serializedApiFileHeader = serializedApiFileHeader.Insert(serializedApiFileHeader.Length - 1, " ").Insert(1, " ");
journalContent =
serializedApiFileHeader +
"\n" +
journalContent;
}
catch (Exception ex)
{
if (ex.ToString().Contains("Json"))
{
var errorMessage = "Line failed: " + firstValidRow;
await SendAdminNotification(discord,
"**[JOURNAL]** JSON Reader Exception while fetching first item",
errorMessage
);
return(HttpStatusCode.InternalServerError, new HttpResponseMessage(HttpStatusCode.InternalServerError)
{
Content = new StringContent("faulty row: " + firstValidRow)
});
}
}
}
}
var journalLineCount = journalContent.Trim().Split('\n', StringSplitOptions.RemoveEmptyEntries).Length;
var journalBytes = ZipManager.Zip(journalContent.Trim());
string fileName = $"{user.UserIdentifier}/journal/{journalDate.Year}/{journalDate.Month.ToString().PadLeft(2, '0')}/{journalDate.Day.ToString().PadLeft(2, '0')}.journal";
if (updateFileOnS3)
{
using (var ms = new MemoryStream(journalBytes))
{
var policy = Policy
.Handle <ConnectionException>()
.WaitAndRetryAsync(new[] {
TimeSpan.FromSeconds(1),
TimeSpan.FromSeconds(2),
TimeSpan.FromSeconds(4),
TimeSpan.FromSeconds(8),
TimeSpan.FromSeconds(16),
});
await policy.ExecuteAsync(() => minioClient.PutObjectAsync("journal-limpet", fileName, ms, ms.Length, "application/gzip"));
}
await SSEActivitySender.SendUserActivityAsync(user.UserIdentifier,
$"Downloaded journals for {journalDate:yyyy-MM-dd}",
$"We downloaded {journalLineCount:N0} lines of journal for this day",
"success"
);
await SSEActivitySender.SendStatsActivityAsync(db);
}
if (previousRow == null)
{
await db.ExecuteNonQueryAsync(@"INSERT INTO user_journal (user_identifier, journal_date, s3_path, last_processed_line, last_processed_line_number, complete_entry, last_update)
VALUES (@user_identifier, @journal_date, @s3_path, @last_processed_line, @last_processed_line_number, @complete_entry, GETUTCDATE())",
new SqlParameter("user_identifier", user.UserIdentifier),
new SqlParameter("journal_date", journalDate),
new SqlParameter("s3_path", fileName),
new SqlParameter("last_processed_line", journalRows.LastOrDefault() ?? string.Empty),
new SqlParameter("last_processed_line_number", journalLineCount),
new SqlParameter("complete_entry", DateTime.UtcNow.Date > journalDate.Date)
);
}
else
{
await db.ExecuteNonQueryAsync(@"UPDATE user_journal SET
last_processed_line = @last_processed_line,
last_processed_line_number = @last_processed_line_number,
complete_entry = @complete_entry,
last_update = GETUTCDATE()
WHERE journal_id = @journal_id AND user_identifier = @user_identifier",
new SqlParameter("journal_id", previousRow.JournalId),
new SqlParameter("user_identifier", user.UserIdentifier),
new SqlParameter("last_processed_line", journalRows.LastOrDefault() ?? string.Empty),
new SqlParameter("last_processed_line_number", journalLineCount),
new SqlParameter("complete_entry", DateTime.UtcNow.Date > journalDate.Date)
);
}
Thread.Sleep(5000);
return(HttpStatusCode.OK, journalRequest);
}
static async Task <(bool failedRequest, bool shouldBail)> TryGetJournalAsync(DiscordWebhook discord, DateTime journalDate, Shared.Models.User.Profile user, MSSQLDB db, HttpClient hc, MinioClient minioClient, PerformContext context)
{
try
{
var res = await GetJournalAsync(journalDate, user, db, hc, minioClient, discord);
int loop_counter = 0;
while (res.code != HttpStatusCode.OK)
{
Thread.Sleep(5000);
var content = await res.message.Content.ReadAsStringAsync();
if (content.Contains("to purchase Elite: Dangerous"))
{
await db.ExecuteNonQueryAsync("UPDATE user_profile SET skip_download = 1 WHERE user_identifier = @user_identifier", new SqlParameter("user_identifier", user.UserIdentifier));
await SendAdminNotification(
discord,
"Failed to download journal, cannot access cAPI",
"User probably has a Epic Games account",
new Dictionary <string, string>
{
{ "Response code", res.code.ToString() },
{ "Content", content },
{ "User identifier", user.UserIdentifier.ToString() },
{ "Journal date", journalDate.ToString("yyyy-MM-dd") }
}
);
return(false, true);
}
if (loop_counter > 10)
{
await SendAdminNotification(
discord,
"Failed to download journal",
"Encountered an error too many times",
new Dictionary <string, string>
{
{ "Response code", res.code.ToString() },
{ "Content", content },
{ "User identifier", user.UserIdentifier.ToString() },
{ "Journal date", journalDate.ToString("yyyy-MM-dd") }
}
);
return(false, false);
}
switch (res.code)
{
case HttpStatusCode.PartialContent:
Thread.Sleep(5000);
res = await GetJournalAsync(journalDate, user, db, hc, minioClient, discord);
break;
}
loop_counter++;
}
}
catch (TooManyOldJournalItemsException ex)
{
await SendAdminNotification(discord,
"**[JOURNAL]** Exception: Too many old journal items",
"The user somehow has duplicates of journals stored for a single date",
new Dictionary <string, string> {
{ "Exception", ex.ToString() },
{ "User identifier", user.UserIdentifier.ToString() }
}
);
return(false, true);
}
catch (Exception ex)
{
var errorMessage = ex.ToString() + "\n\n" + JsonSerializer.Serialize(user, new JsonSerializerOptions()
{
WriteIndented = true
});
await SendAdminNotification(discord,
"**[JOURNAL]** Unhandled exception while downloading journals",
errorMessage
);
return(false, false);
}
return(true, false);
}
