private async Task InsertClassLevel(IDbConnection connection, CharacterModel character, Killmail insertedKillmail, IMessage message) {
// Initialize variables for time testing as a base for relevance of data
var historyLengthUpdateSetting = new TimeSpan(0, 12, 0, 0);
var serverTime = new DateTimeOffset(DateTime.Now);
var classLevelParser = new KillMailParser();
var levelString = classLevelParser.ExtractLevel(character.classLevel);
if (!string.IsNullOrEmpty(levelString))
{
character.level = Convert.ToInt32(levelString);
}
character.className = classLevelParser.ExtractChar(character.classLevel);
// Update character level and killmail character level if not older than historyLengthUpdateSetting
var insertLevelSql = @"UPDATE character
SET level = @Level
WHERE name = @CharName
";
try {
await connection.ExecuteAsync(insertLevelSql, new { Level = character.level, CharName = character.name });
}
catch (Exception ex) {
Console.WriteLine(ex);
}
if(serverTime - message.CreatedAt < historyLengthUpdateSetting) {
string insertLevelIntoKillmailSql;
if (character.isAttacker) {
insertLevelIntoKillmailSql = @"UPDATE killmail
SET attacker_level = @Level
WHERE killmail_raw_id = @KillmailRawId
";
}
else {
insertLevelIntoKillmailSql = @"UPDATE killmail
SET victim_level = @Level
WHERE killmail_raw_id = @KillmailRawId
";
}
try {
await connection.ExecuteAsync(insertLevelIntoKillmailSql, new { Level = character.level, KillmailRawId = insertedKillmail.killmail_raw_id});
}
catch (Exception ex) {
Console.WriteLine(ex);
}
}
if (!string.IsNullOrEmpty(character.className))
{
var classSelectQuery = @"SELECT id FROM class WHERE name = @Name;";
var classId = await connection.ExecuteScalarAsync<int?>(classSelectQuery, new { Name = character.className});
if (classId != null)
{
character.classId = classId.Value;
}
else
{
// Update class table with class_id
var insertClassSql = @"INSERT INTO class (name)
VALUES (@ClassName)
ON CONFLICT(name) DO UPDATE
SET name = EXCLUDED.name
RETURNING id
";
var parameters = new DynamicParameters();
parameters.Add("@ClassName", character.className);
parameters.Add("@ClassId", direction: ParameterDirection.Output);
try {
await connection.ExecuteAsync(insertClassSql, parameters);
}
catch (Exception ex) {
Console.WriteLine(ex);
}
character.classId = parameters.Get<int>("ClassId");
}
}
// Update character table with class_id
var insertClassIntoCharSql = @"UPDATE character
SET class_id = @ClassId
WHERE name = @CharName
";
try {
await connection.ExecuteAsync(insertClassIntoCharSql, new { ClassId = character.classId, CharName = character.name });
}
catch (Exception ex) {
Console.WriteLine(ex);
}
}
private async Task ProcessMessage(IMessage message)
{
KillMailParser killmailParser = new KillMailParser();
var rawKillMailId = 0;
// Check if killmail exists
using(var connection = DatabaseConnection.CreateConnection(DbConnectionString)) {
var selectRawKillmailSql = @"SELECT *
FROM killmail_raw
WHERE discord_message_id = @messageId";
try {
var messageIdSigned = Convert.ToInt64(message.Id);
var affectedRows = await connection.QueryAsync(selectRawKillmailSql, new { messageId = messageIdSigned });
if (affectedRows.Count() > 0) {
return;
}
}
catch (Exception ex) {
Console.WriteLine(ex);
}
}
// Process message if nothing found
KillmailModel parsedKillmail = null;
Killmail insertedKillmail = null;
using(var connection = DatabaseConnection.CreateConnection(DbConnectionString)) {
connection.Open();
using (var killmailTransaction = connection.BeginTransaction()) {
try
{
rawKillMailId = await InsertRawKillmailAsync(connection, message);
// Parse raw killmail
parsedKillmail = killmailParser.ExtractKillmail(message.Content);
parsedKillmail.killmail_raw_id = rawKillMailId;
insertedKillmail = await InsertParsedKillmailAsync(connection, parsedKillmail);
killmailTransaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex);
killmailTransaction.Rollback();
}
}
}
// Get level and class for each char
var scraper = new Scraper();
var victim = new CharacterModel{
name = parsedKillmail.victimName,
isAttacker = false
};
var attacker = new CharacterModel{
name = parsedKillmail.attackerName,
isAttacker = true
};
victim.classLevel = await scraper.ScrapeCharInfo(victim.name);
attacker.classLevel = await scraper.ScrapeCharInfo(attacker.name);
using(var connection = DatabaseConnection.CreateConnection(DbConnectionString)) {
if (!string.IsNullOrEmpty(victim.classLevel))
{
await InsertClassLevel(connection, victim, insertedKillmail, message);
}
if (!string.IsNullOrEmpty(attacker.classLevel))
{
await InsertClassLevel(connection, attacker, insertedKillmail, message);
}
}
}