private void WriteRaid(RaidTO raid)
{
var foundRaid = GetRaid(raid);
if (foundRaid == null)
{
using var sqLiteCommand = new SQLiteCommand("INSERT INTO Raid(Name, Start_Date, End_Date) VALUES(@Name,@StartDate,@EndDate)", connection);
sqLiteCommand.Parameters.AddWithValue("@Name", raid.Name);
sqLiteCommand.Parameters.AddWithValue("@StartDate", raid.StartDate.ToFileTimeUtc());
sqLiteCommand.Parameters.AddWithValue("@EndDate", raid.EndDate.ToFileTimeUtc());
Console.WriteLine("Add new Raid: " + raid.Name + " from " + raid.StartDate + " until " + raid.EndDate);
sqLiteCommand.ExecuteNonQuery();
sqLiteCommand.CommandText = "SELECT last_insert_rowid()";
var lastRowId64 = (long)sqLiteCommand.ExecuteScalar();
raid.ID = (int)lastRowId64;
}
else
{
raid.ID = foundRaid.ID;
}
}
public RaidTO ParseLog(string filename)
{
var raid = new RaidTO();
var lines = File.ReadAllLines(filename);
var playerParse = false;
var lootParse = false;
for (var i = 0; i < lines.Length; i++)
{
var line = lines[i];
if (i == 2)
{
raid = ParseRaidInfo(line);
}
if (string.IsNullOrEmpty(line))
{
playerParse = false;
lootParse = false;
}
if (playerParse)
{
var player = ParsePlayerInfo(line);
if (player != null)
{
_players.Add(player);
}
}
if (lootParse)
{
var loot = ParseLootInfo(line);
if (loot != null)
{
_loot.Add(loot);
}
}
if (line.Contains("Players:"))
{
playerParse = true;
}
if (line.Contains("Loot:"))
{
lootParse = true;
}
}
raid.Loot = _loot;
raid.Players = _players;
return(raid);
}
private bool GetAttendance(RaidTO raid, PlayerTO player)
{
using var sqLiteCommand = new SQLiteCommand("SELECT COUNT(*) FROM Attendance WHERE Player_ID = @PlayerID AND Raid_ID = @RaidID AND Start_Date = @StartDate AND End_Date = @EndDate", connection);
sqLiteCommand.Parameters.AddWithValue("@PlayerID", player.ID);
sqLiteCommand.Parameters.AddWithValue("@RaidID", raid.ID);
sqLiteCommand.Parameters.AddWithValue("@StartDate", player.Joined.ToFileTimeUtc());
sqLiteCommand.Parameters.AddWithValue("@EndDate", player.Left.ToFileTimeUtc());
using var sqLiteDataReader = sqLiteCommand.ExecuteReader();
while (sqLiteDataReader.Read())
{
return(sqLiteDataReader.GetInt32(0) > 0);
}
return(false);
}
private RaidTO GetRaid(RaidTO raid)
{
using var sqLiteCommand = new SQLiteCommand("SELECT * FROM Raid WHERE Name = @Name AND Start_Date = @StartDate AND End_Date = @EndDate", connection);
sqLiteCommand.Parameters.AddWithValue("@Name", raid.Name);
sqLiteCommand.Parameters.AddWithValue("@StartDate", raid.StartDate.ToFileTimeUtc());
sqLiteCommand.Parameters.AddWithValue("@EndDate", raid.EndDate.ToFileTimeUtc());
using var sqLiteDataReader = sqLiteCommand.ExecuteReader();
while (sqLiteDataReader.Read())
{
raid.ID = sqLiteDataReader.GetInt32(sqLiteDataReader.GetOrdinal("Raid_ID"));
return(raid);
}
return(null);
}
private void WriteAttendance(RaidTO raid, PlayerTO player)
{
if (GetAttendance(raid, player))
{
return;
}
using var sqLiteCommand = new SQLiteCommand("INSERT INTO Attendance(Player_ID, Raid_ID, Start_Date, End_Date) VALUES(@PlayerID,@RaidID,@StartDate,@EndDate)", connection);
sqLiteCommand.Parameters.AddWithValue("@PlayerID", player.ID);
sqLiteCommand.Parameters.AddWithValue("@RaidID", raid.ID);
sqLiteCommand.Parameters.AddWithValue("@StartDate", player.Joined.ToFileTimeUtc());
sqLiteCommand.Parameters.AddWithValue("@EndDate", player.Left.ToFileTimeUtc());
Console.WriteLine("Add new Attendance: " + player.Name + " in Raid " + raid.Name);
sqLiteCommand.ExecuteNonQuery();
}
private void WritePlayer(RaidTO raid, DateTime lastRaidTime, PlayerTO player)
{
var foundPlayer = GetPlayer(player.Name);
if (foundPlayer == null)
{
using var sqLiteCommand = new SQLiteCommand("INSERT INTO Player(Name, Guild, Player_Class, Race, Lvl) VALUES(@Name,@Guild,@PlayerClass,@Race,@Lvl)", connection);
sqLiteCommand.Parameters.AddWithValue("@Name", player.Name);
sqLiteCommand.Parameters.AddWithValue("@Guild", player.Guild);
sqLiteCommand.Parameters.AddWithValue("@PlayerClass", player.PlayerClass);
sqLiteCommand.Parameters.AddWithValue("@Race", player.Race);
sqLiteCommand.Parameters.AddWithValue("@Lvl", player.Level);
Console.WriteLine("Add new Player: " + player.Name + " from " + player.Guild);
sqLiteCommand.ExecuteNonQuery();
sqLiteCommand.CommandText = "SELECT last_insert_rowid()";
var lastRowId64 = (long)sqLiteCommand.ExecuteScalar();
player.ID = (int)lastRowId64;
}
else
{
player.ID = foundPlayer.ID;
//Update only if player info is newer than the last raid
if (raid.EndDate <= lastRaidTime)
{
return;
}
if (!string.Equals(player.Guild, foundPlayer.Guild))
{
Console.WriteLine("Update player " + player.Name + "'s guild: '" + foundPlayer.Guild + "' -> '" + player.Guild + "'");
UpdatePlayer(player);
}
if (!Equals(player.Level, foundPlayer.Level))
{
Console.WriteLine("Update player " + player.Name + "'s level: '" + foundPlayer.Level + "' -> '" + player.Level + "'");
UpdatePlayer(player);
}
}
}
public void Persist(RaidTO raid)
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
WriteRaid(raid);
var lastRaidTime = GetLastRaidTime();
foreach (var player in raid.Players)
{
WritePlayer(raid, lastRaidTime, player);
WriteAttendance(raid, player);
}
foreach (var loot in raid.Loot)
{
WriteMob(loot.Mob);
WriteItem(loot.Item);
WriteLoot(raid, loot);
}
}
private void WriteLoot(RaidTO raid, LootTO loot)
{
if (GetLoot(loot))
{
return;
}
using var sqLiteCommand = new SQLiteCommand("INSERT INTO Loot(Date, Item_ID, Mob_ID, Raid_ID, Player_ID) VALUES(@Date,@ItemID,@MobID,@RaidID,@PlayerID)", connection);
sqLiteCommand.Parameters.AddWithValue("@Date", loot.Date.ToFileTimeUtc());
sqLiteCommand.Parameters.AddWithValue("@ItemID", loot.Item.ID);
sqLiteCommand.Parameters.AddWithValue("@MobID", loot.Mob.ID);
sqLiteCommand.Parameters.AddWithValue("@RaidID", raid.ID);
sqLiteCommand.Parameters.AddWithValue("@PlayerID", loot.Player.ID);
Console.WriteLine("Add new Loot: " + loot.Item.Name + " given to " + loot.Player.Name + " (" + loot.Player.Guild + ")");
sqLiteCommand.ExecuteNonQuery();
sqLiteCommand.CommandText = "SELECT last_insert_rowid()";
var lastRowId64 = (long)sqLiteCommand.ExecuteScalar();
loot.ID = (int)lastRowId64;
}
