public void GetKillmail_NoErrors()
{
Killmail data = crest.GetKillmail(28694894, "3d9702696cf8e75d6168734ad26a772e17efc9ba");
Assert.AreEqual(30000131, data.SolarSystem.Id);
Assert.AreEqual(99000652, data.Victim.Alliance.Id);
}
private async Task <Killmail> InsertParsedKillmailAsync(IDbConnection connection, ParsedKillMail parsedKillmailModel)
{
var killmailToInsert = new Killmail();
CultureInfo USCultureInfo = new CultureInfo("en-US");
var timezone = "America/Chicago";
// Server changed from chicago time to UTC time instead so this is not being used for now
//var killedAtLocalTime = DateTime.SpecifyKind(DateTime.Parse(parsedKillmailModel.killedAt, USCultureInfo), DateTimeKind.Unspecified);
//killedAtLocalTime.InZone(timezone);
var killedAtLocalTime = DateTime.SpecifyKind(DateTime.Parse(parsedKillmailModel.KilledAt, USCultureInfo), DateTimeKind.Utc);
killmailToInsert.killed_at = killedAtLocalTime;
killmailToInsert.killmail_raw_id = parsedKillmailModel.KillMailRawId;
killmailToInsert.victim_guild_id = await GetOrInsertGuild(connection, parsedKillmailModel.VictimGuild);
killmailToInsert.attacker_guild_id = await GetOrInsertGuild(connection, parsedKillmailModel.AttackerGuild);
killmailToInsert.zone_id = await GetOrInsertZone(connection, parsedKillmailModel.Zone);
killmailToInsert.victim_id = await GetOrInsertCharacter(connection, parsedKillmailModel.VictimName, parsedKillmailModel.VictimIsNpc, killmailToInsert.victim_guild_id, parsedKillmailModel.VictimLevel, parsedKillmailModel.VictimClass);
killmailToInsert.attacker_id = await GetOrInsertCharacter(connection, parsedKillmailModel.AttackerName, parsedKillmailModel.AttackerIsNpc, killmailToInsert.attacker_guild_id, parsedKillmailModel.AttackerLevel, parsedKillmailModel.AttackerClass);
killmailToInsert.victim_level = parsedKillmailModel.VictimLevel;
killmailToInsert.attacker_level = parsedKillmailModel.AttackerLevel;
var dynamicParams = new DynamicParameters();
dynamicParams.AddDynamicParams(new {
killed_at = killmailToInsert.killed_at,
killmail_raw_id = killmailToInsert.killmail_raw_id,
victim_guild_id = killmailToInsert.victim_guild_id,
attacker_guild_id = killmailToInsert.attacker_guild_id,
zone_id = killmailToInsert.zone_id,
victim_id = killmailToInsert.victim_id,
attacker_id = killmailToInsert.attacker_id,
victim_level = killmailToInsert.victim_level,
attacker_level = killmailToInsert.attacker_level,
season = Season
});
dynamicParams.Add("@KillMailId", direction: ParameterDirection.Output);
// Finally, insert killmail
var killmailInsertSql =
@"INSERT INTO killmail (victim_id, victim_guild_id, attacker_id, attacker_guild_id, zone_id, killed_at, killmail_raw_id, victim_level, attacker_level, season)
VALUES (@victim_id, @victim_guild_id, @attacker_id, @attacker_guild_id, @zone_id, @killed_at, @killmail_raw_id, @victim_level, @attacker_level, @season)
RETURNING id;
";
await connection.ExecuteAsync(killmailInsertSql, dynamicParams);
killmailToInsert.id = dynamicParams.Get <int>("KillMailId");
return(killmailToInsert);
}
private void LoadCorporations(Killmail killMail)
{
var corporationIds = killMail.Attackers.Where(w => !w.CharacterId.HasValue && w.CorporationId.HasValue)
.Select(s => s.CorporationId.Value).ToList();
if (!killMail.Victim.CharacterId.HasValue)
{
corporationIds.Add(killMail.Victim.CorporationId.Value);
}
_esiService.LoadCorporations(corporationIds);
}
private void LoadCharacters(Killmail killMail)
{
var characterIds = killMail.Attackers.Where(w => w.CharacterId.HasValue)
.Select(s => s.CharacterId.Value).ToList();
if (killMail.Victim.CharacterId.HasValue)
{
characterIds.Add(killMail.Victim.CharacterId.Value);
}
_esiService.LoadCharacters(characterIds);
}
private void LoadShips(Killmail killMail)
{
var shipIds = killMail.Attackers.Where(w => w.ShipTypeId.HasValue && w.ShipTypeId.Value > 0)
.Select(s => s.ShipTypeId.Value).ToList();
if (killMail.Victim.ShipTypeId.HasValue && killMail.Victim.ShipTypeId > 0)
{
shipIds.Add(killMail.Victim.ShipTypeId.Value);
}
_esiService.LoadShips(shipIds);
}
public async Task <Killmail> GetKillmail(int killmailId, string killmailHash)
{
string requestUri = $"/v1/killmails/{killmailId}/{killmailHash}/";
HttpResponseMessage response = await client.GetAsync(requestUri);
response.EnsureSuccessStatusCode();
using var responseStream = await response.Content.ReadAsStreamAsync();
Killmail killmail = await JsonSerializer.DeserializeAsync <Killmail>(responseStream);
return(killmail);
}
private async Task<Killmail> InsertParsedKillmailAsync(IDbConnection connection, KillmailModel parsedKillmailModel)
{
var killmailToInsert = new Killmail();
CultureInfo USCultureInfo = new CultureInfo("en-US");
var timezone = "America/Chicago";
// Server changed from chicago time to UTC time instead so this is not being used for now
//var killedAtLocalTime = DateTime.SpecifyKind(DateTime.Parse(parsedKillmailModel.killedAt, USCultureInfo), DateTimeKind.Unspecified);
//killedAtLocalTime.InZone(timezone);
var killedAtLocalTime = DateTime.SpecifyKind(DateTime.Parse(parsedKillmailModel.killedAt, USCultureInfo), DateTimeKind.Utc);
killmailToInsert.killed_at = killedAtLocalTime;
killmailToInsert.killmail_raw_id = parsedKillmailModel.killmail_raw_id;
killmailToInsert.victim_guild_id = await GetOrInsertGuild(connection, parsedKillmailModel.victimGuild);
killmailToInsert.attacker_guild_id = await GetOrInsertGuild(connection, parsedKillmailModel.attackerGuild);
killmailToInsert.zone_id = await GetOrInsertZone(connection, parsedKillmailModel.zone);
killmailToInsert.victim_id = await GetOrInsertCharacter(connection, parsedKillmailModel.victimName, killmailToInsert.victim_guild_id);
killmailToInsert.attacker_id = await GetOrInsertCharacter(connection, parsedKillmailModel.attackerName, killmailToInsert.attacker_guild_id);
var dynamicParams = new DynamicParameters();
dynamicParams.AddDynamicParams(new {
killed_at = killmailToInsert.killed_at,
killmail_raw_id = killmailToInsert.killmail_raw_id,
victim_guild_id = killmailToInsert.victim_guild_id,
attacker_guild_id = killmailToInsert.attacker_guild_id,
zone_id = killmailToInsert.zone_id,
victim_id = killmailToInsert.victim_id,
attacker_id = killmailToInsert.attacker_id
});
// Finally, insert killmail
var killmailInsertSql = @"INSERT INTO killmail (victim_id, victim_guild_id, attacker_id, attacker_guild_id, zone_id, killed_at, killmail_raw_id)
VALUES (@victim_id, @victim_guild_id, @attacker_id, @attacker_guild_id, @zone_id, @killed_at, @killmail_raw_id)
RETURNING id;
";
await connection.ExecuteAsync(killmailInsertSql, dynamicParams);
return killmailToInsert;
}
private async Task <KillmailInvolved> InsertParsedKillMailInvolved(IDbConnection connection, Killmail killMail, ParsedKillMailInvolved involved)
{
var killMailInvolvedToInsert = new KillmailInvolved();
killMailInvolvedToInsert.killmail_id = killMail.id;
killMailInvolvedToInsert.attacker_guild_id = await GetOrInsertGuild(connection, involved.AttackerGuild);
killMailInvolvedToInsert.attacker_level = involved.AttackerLevel;
killMailInvolvedToInsert.attacker_id = await GetOrInsertCharacter(connection, involved.AttackerName, involved.AttackerIsNpc, killMailInvolvedToInsert.attacker_guild_id, involved.AttackerLevel, involved.AttackerClass);
killMailInvolvedToInsert.melee_damage = involved.MeleeDamage;
killMailInvolvedToInsert.melee_hits = involved.MeleeHits;
killMailInvolvedToInsert.spell_damage = involved.SpellDamage;
killMailInvolvedToInsert.spell_hits = involved.SpellHits;
killMailInvolvedToInsert.dispel_slots = involved.DispelSlots;
var insertQuery =
@"INSERT INTO killmail_involved (killmail_id, attacker_id, attacker_guild_id, attacker_level, melee_damage, melee_hits, spell_damage, spell_hits, dispel_slots)
VALUES (@killmail_id, @attacker_id, @attacker_guild_id, @attacker_level, @melee_damage, @melee_hits, @spell_damage, @spell_hits, @dispel_slots)
ON CONFLICT (killmail_id, attacker_id) DO NOTHING;
";
await connection.ExecuteAsync(insertQuery, killMailInvolvedToInsert);
return(killMailInvolvedToInsert);
}
public bool IsWormholeKill(Killmail killmail)
{
return(killmail.SolarSystemId > 31000000 && killmail.SolarSystemId < 32000000);
}
public async Task <float> CalculateKillmailValue(Killmail killmail)
{
throw new NotImplementedException();
}
public async Task ProcessKillmailValue(Killmail killmail, float value)
{
// Check wormmhole kill
if (IsWormholeKill(killmail))
{
EveType victimType = await eSIService.GetEveType(killmail.Victim.ShipTypeId);
// Process Victim
if (killmail.Victim.CorporationId != null)
{
_logger.LogDebug("Loss for corp: {0}", killmail.Victim.CorporationId);
var corp = await GetOrCreateCorporation(killmail.Victim.CorporationId.Value);
DailyAggregateCorporation victimAggregate = await GetOrCreateDailyAggregateCorporation(corp, killmail.KillmailTime.Date);
MonthlyAggregateCorporation victimAggregateMonthly = await GetOrCreateMonthlyAggregateCorporation(corp, killmail.KillmailTime.Date);
IncrementAggregate(victimAggregate, victimType, value, killmail.Victim.DamageTaken, false);
IncrementAggregate(victimAggregateMonthly, victimType, value, killmail.Victim.DamageTaken, false);
}
if (killmail.Victim.AllianceId != null)
{
var alliance = await GetOrCreateAlliance(killmail.Victim.AllianceId.Value);
DailyAggregateAlliance victimAggregate = await GetOrCreateDailyAggregateAlliance(alliance, killmail.KillmailTime.Date);
MonthlyAggregateAlliance victimAggregateMonthly = await GetOrCreateMonthlyAggregateAlliance(alliance, killmail.KillmailTime.Date);
IncrementAggregate(victimAggregate, victimType, value, killmail.Victim.DamageTaken, false);
IncrementAggregate(victimAggregateMonthly, victimType, value, killmail.Victim.DamageTaken, false);
}
// Process Attackers
var attackerCorporations = killmail.Attackers.Where(a => a.CorporationId != null && a.CorporationId != killmail.Victim.CorporationId).Select(a => a.CorporationId.Value).Distinct();
foreach (var corporationId in attackerCorporations)
{
var corp = await GetOrCreateCorporation(corporationId);
_logger.LogDebug("Kill for corp: {0}", killmail.Victim.CorporationId);
DailyAggregateCorporation attackerAggregate = await GetOrCreateDailyAggregateCorporation(corp, killmail.KillmailTime.Date);
MonthlyAggregateCorporation monthlyAggregateCorporation = await GetOrCreateMonthlyAggregateCorporation(corp, killmail.KillmailTime.Date);
IncrementAggregate(attackerAggregate, victimType, value, killmail.Attackers.Where(a => a.CorporationId == corp.Id).Sum(a => a.DamageDone), true);
IncrementAggregate(monthlyAggregateCorporation, victimType, value, killmail.Attackers.Where(a => a.CorporationId == corp.Id).Sum(a => a.DamageDone), true);
}
var attackerAlliances = killmail.Attackers.Where(a => a.AllianceId != null && a.AllianceId != killmail.Victim.AllianceId).Select(a => a.AllianceId.Value).Distinct();
foreach (var allianceId in attackerAlliances)
{
var alliance = await GetOrCreateAlliance(allianceId);
DailyAggregateAlliance attackerAggregate = await GetOrCreateDailyAggregateAlliance(alliance, killmail.KillmailTime.Date);
MonthlyAggregateAlliance monthlyAggregateAlliance = await GetOrCreateMonthlyAggregateAlliance(alliance, killmail.KillmailTime.Date);
IncrementAggregate(attackerAggregate, victimType, value, killmail.Attackers.Where(a => a.AllianceId == alliance.Id).Sum(a => a.DamageDone), true);
IncrementAggregate(monthlyAggregateAlliance, victimType, value, killmail.Attackers.Where(a => a.AllianceId == alliance.Id).Sum(a => a.DamageDone), true);
}
await context.SaveChangesAsync();
}
}
public async Task ProcessKillmail(Killmail killmail)
{
float value = await CalculateKillmailValue(killmail);
await ProcessKillmailValue(killmail, value);
}
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);
}
}
}
private void LoadSystem(Killmail killMail)
{
_esiService.LoadSystem(killMail.SolarSystemId);
}
