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); }