public async Task UpdatePatronInfo(IReadOnlyList <PatronInfo> patronInfo)
{
List <FantasyCriticUserHasRoleEntity> roleEntities = patronInfo
.Where(x => x.IsPlusUser)
.Select(x => new FantasyCriticUserHasRoleEntity(x.User.Id, 4, true))
.ToList();
List <FantasyCriticUserDonorEntity> donorEntities = patronInfo
.Where(x => x.DonorName is not null)
.Select(x => new FantasyCriticUserDonorEntity(x.User, x.DonorName !))
.ToList();
await using (var connection = new MySqlConnection(_connectionString))
{
await connection.OpenAsync();
await using var transaction = await connection.BeginTransactionAsync();
await connection.ExecuteAsync("DELETE FROM tbl_user_hasrole WHERE ProgrammaticallyAssigned = 1;", transaction : transaction);
await connection.ExecuteAsync("DELETE FROM tbl_user_donorname;", transaction : transaction);
await connection.BulkInsertAsync(roleEntities, "tbl_user_hasrole", 500, transaction, insertIgnore : true);
await connection.BulkInsertAsync(donorEntities, "tbl_user_donorname", 500, transaction);
await transaction.CommitAsync();
}
_userCache = null;
}
internal async Task UpdateMasterGames(IEnumerable <MasterGameTag> productionTags, IEnumerable <MasterGame> productionMasterGames,
IEnumerable <MasterGameTag> betaTags, IEnumerable <MasterGame> betaMasterGames, IEnumerable <MasterGameHasTagEntity> productionGamesHaveTagEntities)
{
var tagsOnBetaNotOnProduction = betaTags.Except(productionTags).ToList();
foreach (var tag in tagsOnBetaNotOnProduction)
{
_logger.Warn($"Tag: {tag.ReadableName} on beta but not production.");
}
var gamesOnBetaNotOnProduction = betaMasterGames.Except(productionMasterGames).ToList();
foreach (var game in gamesOnBetaNotOnProduction)
{
_logger.Warn($"Game: {game.GameName} on beta but not production.");
}
var betaKeepTags = tagsOnBetaNotOnProduction.Select(x => x.Name);
var betaKeepGames = gamesOnBetaNotOnProduction.Select(x => x.MasterGameID);
List <MasterGameTagEntity> tagEntities = productionTags.Select(x => new MasterGameTagEntity(x)).ToList();
List <MasterGameEntity> masterGameEntities = productionMasterGames.Select(x => new MasterGameEntity(x)).ToList();
List <MasterSubGameEntity> masterSubGameEntities = productionMasterGames.SelectMany(x => x.SubGames).Select(x => new MasterSubGameEntity(x)).ToList();
var paramsObject = new
{
betaKeepTags,
betaKeepGames
};
await using var connection = new MySqlConnection(_connectionString);
await connection.OpenAsync();
await using var transaction = await connection.BeginTransactionAsync();
await connection.ExecuteAsync("SET foreign_key_checks = 0;", transaction : transaction);
await connection.ExecuteAsync("DELETE FROM tbl_mastergame_hastag WHERE MasterGameID NOT IN @betaKeepGames AND TagName NOT IN @betaKeepTags;", paramsObject, transaction);
await connection.ExecuteAsync("DELETE FROM tbl_mastergame_subgame WHERE MasterGameID NOT IN @betaKeepGames;", paramsObject, transaction);
await connection.ExecuteAsync("DELETE FROM tbl_mastergame WHERE MasterGameID NOT IN @betaKeepGames;", paramsObject, transaction);
await connection.ExecuteAsync("DELETE FROM tbl_mastergame_tag WHERE Name NOT IN @betaKeepTags;", paramsObject, transaction);
await connection.BulkInsertAsync(tagEntities, "tbl_mastergame_tag", 500, transaction);
await connection.BulkInsertAsync(masterGameEntities, "tbl_mastergame", 500, transaction);
await connection.BulkInsertAsync(masterSubGameEntities, "tbl_mastergame_subgame", 500, transaction);
await connection.BulkInsertAsync(productionGamesHaveTagEntities, "tbl_mastergame_hastag", 500, transaction);
await connection.ExecuteAsync("SET foreign_key_checks = 1;", transaction : transaction);
await transaction.CommitAsync();
}
public async Task SetEmailSettings(FantasyCriticUser user, bool sendPublicBidEmails)
{
var settingsEntities = new List <FantasyCriticUserEmailSettingEntity>();
if (sendPublicBidEmails)
{
settingsEntities.Add(new FantasyCriticUserEmailSettingEntity(user, EmailType.PublicBids));
}
var parameters = new
{
userID = user.Id
};
await using var connection = new MySqlConnection(_connectionString);
await connection.OpenAsync();
await using var transaction = await connection.BeginTransactionAsync();
await connection.ExecuteAsync("DELETE FROM tbl_user_emailsettings WHERE UserID = @userID;", parameters, transaction : transaction);
await connection.BulkInsertAsync(settingsEntities, "tbl_user_emailsettings", 500, transaction);
await transaction.CommitAsync();
}
public async Task ReplaceCodesAsync(FantasyCriticUser user, IEnumerable <string> recoveryCodes, CancellationToken cancellationToken)
{
cancellationToken.ThrowIfCancellationRequested();
List <RecoveryCodeEntity> codeEntities = recoveryCodes.Select(x => new RecoveryCodeEntity(user.Id, x)).ToList();
var paramsObject = new
{
userID = user.Id
};
await using (var connection = new MySqlConnection(_connectionString))
{
await connection.OpenAsync(cancellationToken);
await using var transaction = await connection.BeginTransactionAsync(cancellationToken);
await connection.ExecuteAsync("DELETE FROM tbl_user_recoverycode WHERE UserID = @userID;", paramsObject, transaction);
await connection.BulkInsertAsync(codeEntities, "tbl_user_recoverycode", 500, transaction);
await transaction.CommitAsync(cancellationToken);
}
_userCache = null;
}
public async Task UpdateCodeBasedTags(IReadOnlyDictionary <MasterGame, IReadOnlyList <MasterGameTag> > tagsToAdd)
{
string deleteExistingTagsSQL = "DELETE tbl_mastergame_hastag FROM tbl_mastergame_hastag " +
"JOIN tbl_mastergame_tag ON tbl_mastergame_hastag.TagName = tbl_mastergame_tag.Name " +
"JOIN tbl_mastergame ON tbl_mastergame_hastag.MasterGameID = tbl_mastergame.MasterGameID " +
"WHERE tbl_mastergame_tag.HasCustomCode " +
"AND (EarlyAccessReleaseDate IS NOT NULL OR InternationalReleaseDate IS NOT NULL)";
var tagEntities = tagsToAdd
.SelectMany(masterGame => masterGame.Value, (masterGame, tag) => new MasterGameHasTagEntity(masterGame.Key, tag))
.ToList();
var excludeFields = new List <string>()
{
"TimeAdded"
};
await using var connection = new MySqlConnection(_connectionString);
await connection.OpenAsync();
await using var transaction = await connection.BeginTransactionAsync();
await connection.ExecuteAsync(deleteExistingTagsSQL, transaction: transaction);
await connection.BulkInsertAsync <MasterGameHasTagEntity>(tagEntities, "tbl_mastergame_hastag", 500, transaction, excludeFields);
await transaction.CommitAsync();
}
public async Task CreateMasterGame(MasterGame masterGame)
{
string masterGameCreateSQL = "insert into tbl_mastergame" +
"(MasterGameID,GameName,EstimatedReleaseDate,MinimumReleaseDate,MaximumReleaseDate,EarlyAccessReleaseDate,InternationalReleaseDate,AnnouncementDate," +
"ReleaseDate,OpenCriticID,GGToken,CriticScore,Notes,BoxartFileName,GGCoverArtFileName," +
"FirstCriticScoreTimestamp,DoNotRefreshDate,DoNotRefreshAnything,EligibilityChanged,DelayContention,AddedTimestamp) VALUES " +
"(@MasterGameID,@GameName,@EstimatedReleaseDate,@MinimumReleaseDate,@MaximumReleaseDate,@EarlyAccessReleaseDate,@InternationalReleaseDate,@AnnouncementDate," +
"@ReleaseDate,@OpenCriticID,@GGToken,@CriticScore,@Notes,@BoxartFileName,@GGCoverArtFileName," +
"@FirstCriticScoreTimestamp,@DoNotRefreshDate,@DoNotRefreshAnything,@EligibilityChanged,@DelayContention,@AddedTimestamp);";
var entity = new MasterGameEntity(masterGame);
var tagEntities = masterGame.Tags.Select(x => new MasterGameHasTagEntity(masterGame, x));
var excludeFields = new List <string>()
{
"TimeAdded"
};
await using var connection = new MySqlConnection(_connectionString);
await connection.OpenAsync();
await using var transaction = await connection.BeginTransactionAsync();
await connection.ExecuteAsync(masterGameCreateSQL, entity, transaction);
await connection.BulkInsertAsync <MasterGameHasTagEntity>(tagEntities, "tbl_mastergame_hastag", 500, transaction, excludeFields);
await transaction.CommitAsync();
}
public async Task EditMasterGame(MasterGame masterGame)
{
string editSQL = "UPDATE tbl_mastergame SET " +
"GameName = @GameName, " +
"EstimatedReleaseDate = @EstimatedReleaseDate, " +
"MinimumReleaseDate = @MinimumReleaseDate, " +
"MaximumReleaseDate = @MaximumReleaseDate, " +
"EarlyAccessReleaseDate = @EarlyAccessReleaseDate, " +
"InternationalReleaseDate = @InternationalReleaseDate, " +
"AnnouncementDate = @AnnouncementDate, " +
"ReleaseDate = @ReleaseDate, " +
"OpenCriticID = @OpenCriticID, " +
"GGToken = @GGToken, " +
"CriticScore = @CriticScore, " +
"Notes = @Notes, " +
"BoxartFileName = @BoxartFileName, " +
"GGCoverArtFileName = @GGCoverArtFileName, " +
"FirstCriticScoreTimestamp = @FirstCriticScoreTimestamp, " +
"DoNotRefreshDate = @DoNotRefreshDate, " +
"DoNotRefreshAnything = @DoNotRefreshAnything, " +
"EligibilityChanged = @EligibilityChanged, " +
"DelayContention = @DelayContention " +
"WHERE MasterGameID = @MasterGameID;";
string deleteTagsSQL = "delete from tbl_mastergame_hastag where MasterGameID = @MasterGameID;";
var entity = new MasterGameEntity(masterGame);
var tagEntities = masterGame.Tags.Select(x => new MasterGameHasTagEntity(masterGame, x));
var excludeFields = new List <string>()
{
"TimeAdded"
};
await using var connection = new MySqlConnection(_connectionString);
await connection.OpenAsync();
await using var transaction = await connection.BeginTransactionAsync();
await connection.ExecuteAsync(editSQL, entity, transaction);
await connection.ExecuteAsync(deleteTagsSQL, new { masterGame.MasterGameID }, transaction);
await connection.BulkInsertAsync <MasterGameHasTagEntity>(tagEntities, "tbl_mastergame_hastag", 500, transaction, excludeFields);
await transaction.CommitAsync();
}
public async Task UpdateCalculatedStats(IEnumerable <MasterGameCalculatedStats> calculatedStats, int year)
{
List <MasterGameYearEntity> masterGameYearEntities = calculatedStats.Select(x => new MasterGameYearEntity(x)).ToList();
var excludeFields = new List <string>()
{
"DoNotRefreshDate", "DoNotRefreshAnything"
};
await using var connection = new MySqlConnection(_connectionString);
await connection.OpenAsync();
await using var transaction = await connection.BeginTransactionAsync();
await connection.ExecuteAsync("delete from tbl_caching_mastergameyear where Year = @year", new { year }, transaction);
await connection.BulkInsertAsync <MasterGameYearEntity>(masterGameYearEntities, "tbl_caching_mastergameyear", 500, transaction, excludeFields);
await transaction.CommitAsync();
}
public async Task UpdateHypeFactors(IEnumerable <MasterGameHypeScores> hypeScores, int year)
{
List <MasterGameYearEntity> masterGameYearEntities = hypeScores.Select(x => new MasterGameYearEntity(x)).ToList();
var excludeFields = new List <string>()
{
"DoNotRefreshDate", "DoNotRefreshAnything"
};
using (var connection = new MySqlConnection(_connectionString))
{
await connection.OpenAsync();
using (var transaction = await connection.BeginTransactionAsync())
{
await connection.ExecuteAsync("delete from tbl_caching_mastergameyear where Year = @year", new { year });
await connection.BulkInsertAsync(masterGameYearEntities, "tbl_caching_mastergameyear", 500, transaction, excludeFields);
transaction.Commit();
}
}
}