private void UpdatePlayer(PlayerTO player)
{
using var sqLiteCommand = new SQLiteCommand("UPDATE Player SET Guild = @Guild, Lvl = @Level WHERE Name = @Name", connection);
sqLiteCommand.Parameters.AddWithValue("@Guild", player.Guild);
sqLiteCommand.Parameters.AddWithValue("@Level", player.Level);
sqLiteCommand.Parameters.AddWithValue("@Name", player.Name);
sqLiteCommand.ExecuteNonQuery();
}
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 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);
}
}
}
private PlayerTO GetPlayer(string playerName)
{
using var sqLiteCommand = new SQLiteCommand("SELECT * FROM Player WHERE Name = @Name", connection);
sqLiteCommand.Parameters.AddWithValue("@Name", playerName);
using var sqLiteDataReader = sqLiteCommand.ExecuteReader();
while (sqLiteDataReader.Read())
{
var player = new PlayerTO();
player.ID = sqLiteDataReader.GetInt32(sqLiteDataReader.GetOrdinal("Player_ID"));
player.Name = sqLiteDataReader.GetString(sqLiteDataReader.GetOrdinal("Name"));
if (!sqLiteDataReader.IsDBNull("Guild"))
{
player.Guild = sqLiteDataReader.GetString(sqLiteDataReader.GetOrdinal("Guild"));
}
player.PlayerClass = sqLiteDataReader.GetString(sqLiteDataReader.GetOrdinal("Player_Class"));
player.Race = sqLiteDataReader.GetString(sqLiteDataReader.GetOrdinal("Race"));
player.Level = sqLiteDataReader.GetInt32(sqLiteDataReader.GetOrdinal("Lvl"));
return(player);
}
return(null);
}
