private Player GetVictim(StringReader reader, Kill k)
{
Player P = new Player();
List<string> victim = ReadLines(reader, 8);
P.Name = victim[0].Replace("Victim: ", "");
P.Corp = victim[1].Replace("Corp: ", "");
P.Alliance = victim[2].Replace("Alliance: ", "");
P.Faction = victim[3].Replace("Faction: ", "");
P.Ship = new Ship {Name = victim[4].Replace("Destroyed: ", "")};
k.System = victim[5].Replace("System: ", "");
return P;
}
private Player GetInvolved(StringReader reader)
{
Player p = new Player();
List<string> involved = ReadLines(reader, 8);
p.Name = involved[0].Replace("Name: ", "").Replace(" (laid the final blow)", "");
p.Corp = involved[2].Replace("Corp: ", "");
p.Alliance = involved[3].Replace("Alliance: ", "");
p.Faction = involved[4].Replace("Faction: ", "");
p.Ship = new Ship {Name = involved[5].Replace("Ship: ", "")};
return p;
}
private void UpdateLastSeen(Player Player, SQLiteConnection Conn)
{
using (SQLiteCommand cmd = Conn.CreateCommand())
{
cmd.CommandText = "Select Seen.Ship, Kill.System, Kill.[Time] from Seen" +
" left outer join Kill on Seen.KillID = Kill.ID" +
" Where Seen.Player = @NAME" +
" Order by Kill.[Time] Desc limit 1";
cmd.Parameters.AddWithValue("@NAME", Player.Name);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
Player.Ship = new Ship {Name = reader.GetString(0)};
Player.LastSeen = new Seen
{
System = reader.GetString(1),
Time = reader.GetDateTime(2)
};
}
}
}
}
private List<Player> GetPilots(string SQL, string Query, DateTime FromDate)
{
var ret = new List<Player>();
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandText = SQL;
cmd.Parameters.AddWithValue("@QUERY", Query);
cmd.Parameters.AddWithValue("@FROMDATE", FromDate);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string name = reader.GetString(0);
var P = new Player
{
Name = name,
Corp = reader.GetString(1),
Alliance = reader.GetString(2),
};
ret.Add(P);
}
}
}
foreach (var player in ret)
{
UpdateLastSeen(player, conn);
}
}
return ret;
}
private Player GetFullPlayer(SQLiteConnection Conn, string Name )
{
var player = new Player
{
Name = Name,
KnownShips = new List<Ship>(),
Seen = new List<Seen>(),
CharacterID = 0
};
bool updateCharID = false;
using (SQLiteCommand cmd = Conn.CreateCommand())
{
cmd.CommandText = "select Corp, Alliance, CharacterID from Player where Name = @NAME";
cmd.Parameters.AddWithValue("@NAME", Name);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
player.Corp = reader.GetString(0);
player.Alliance = reader.GetString(1);
if(!reader.IsDBNull(2))
player.CharacterID = reader.GetInt64(2);
if (player.CharacterID == 0)
updateCharID = true;
}
}
}
using (SQLiteCommand cmd = Conn.CreateCommand())
{
cmd.CommandText = "select Seen.Ship, Seen.isKiller, Kill.System, Kill.Time from Seen" +
" left outer join Kill on seen.KillID = Kill.ID" +
" where Seen.Player = @NAME";
cmd.Parameters.AddWithValue("@NAME", Name);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string ShipName = reader.GetString(0);
Ship ship = player.KnownShips.Find(S => S.Name == ShipName);
DateTime lastUsed = reader.GetDateTime(3);
bool killer = reader.GetBoolean(1);
string System = reader.GetString(2);
if (ship == null)
{
ship = new Ship
{
Name = ShipName,
LastUsed = lastUsed,
TimesUsed = 1,
TimesLost = killer ? 0 : 1
};
player.KnownShips.Add(ship);
}
else
{
ship.TimesUsed++;
if (!killer)
ship.TimesLost++;
if (ship.LastUsed < lastUsed)
ship.LastUsed = lastUsed;
}
Seen seen = player.Seen.Find(S => S.System == System);
if (seen == null)
{
seen = new Seen
{
Count = 1,
System = System,
Time = lastUsed
};
player.Seen.Add(seen);
}
else
{
seen.Count++;
if (seen.Time < lastUsed)
seen.Time = lastUsed;
}
if (lastUsed > player.LastSeen.Time)
{
player.LastSeen.Time = lastUsed;
player.LastSeen.System = System;
}
}
}
}
//TODO: Call update charid + update db
if(updateCharID)
{
player.CharacterID= EVEIntel.Repository.EVEApi.Character.GetCharacterID(player.Name);
SetPlayerCharacterID(player.Name, player.CharacterID, Conn);
}
return player;
}
private void InsertPlayer(Player player, SQLiteConnection Conn)
{
using (SQLiteCommand cmd = Conn.CreateCommand())
{
cmd.CommandText = "insert into Player(Name, Corp, Alliance) values(@NAME, @CORP, @ALLIANCE)";
cmd.Parameters.AddWithValue("@NAME", player.Name);
cmd.Parameters.AddWithValue("@CORP", player.Corp);
cmd.Parameters.AddWithValue("@ALLIANCE", player.Alliance);
cmd.ExecuteNonQuery();
}
}
private void UpdatePlayer(Player player, SQLiteConnection Conn, DateTime TimeSeen)
{
using (SQLiteCommand cmd = Conn.CreateCommand())
{
// Get Last seen for player first!
cmd.CommandText = "select * from Seen "+
"inner join kill on Seen.KillId = Kill.Id "+
"where Seen.Player = @NAME and Kill.Time > @TIME";
cmd.Parameters.AddWithValue("@NAME", player.Name);
cmd.Parameters.AddWithValue("@TIME", TimeSeen);
bool newData = true;
using(SQLiteDataReader reader = cmd.ExecuteReader())
{
if(reader.HasRows)
newData = false;
}
if (newData)
{
cmd.CommandText = "update Player set Corp = @CORP, Alliance = @ALLIANCE where Name = @NAME";
cmd.Parameters.AddWithValue("@CORP", player.Corp);
cmd.Parameters.AddWithValue("@ALLIANCE", player.Alliance);
cmd.Parameters.AddWithValue("@NAME", player.Name);
cmd.ExecuteNonQuery();
}
}
}
private Player GetPlayer(string Name, SQLiteConnection Conn)
{
Player P = null;
using (SQLiteCommand cmd = Conn.CreateCommand())
{
cmd.CommandText = "select Corp, Alliance from Player where Name = @NAME";
cmd.Parameters.AddWithValue("@NAME", Name);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
P = new Player
{
Name = Name,
Corp = reader.GetString(0),
Alliance = reader.GetString(1)
};
}
}
}
return P;
}
private void HandlePlayer(Player player, SQLiteConnection Conn, DateTime TimeSeen)
{
Player P = GetPlayer(player.Name, Conn);
if (P == null)
InsertPlayer(player, Conn);
else if (!P.Equals(player))
UpdatePlayer(player, Conn, TimeSeen);
}
private void InsertSeen(Player player, bool isKiller, Int64 KillID, SQLiteConnection conn)
{
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandText =
"insert into Seen(Player, Ship, KillID, isKiller) values(@PLAYER, @SHIP, @KILLID, @ISKILLER)";
cmd.Parameters.AddWithValue("@PLAYER", player.Name);
cmd.Parameters.AddWithValue("@SHIP", player.Ship.Name);
cmd.Parameters.AddWithValue("@KILLID", KillID);
cmd.Parameters.AddWithValue("@ISKILLER", isKiller);
cmd.ExecuteNonQuery();
}
}
private List<Kill> ParseKillMails(List<KillTime> Kills)
{
var ret = new List<Kill>();
ReportProgress("Downloading killmails");
Log.Info(String.Format("Got {0} killmails to parse", Kills.Count));
foreach (KillTime Kill in Kills)
{
if(Worker.CancellationPending)
{
WorkEventArgs.Cancel = true;
return ret;
}
var kill = new Kill();
if(ret.Count % 20 == 0)
{
ReportProgress(String.Format("{0} of {1} killmails downloaded", ret.Count, Kills.Count));
}
Document = DownloadDocument("http://www.battleclinic.com/eve_online/pk/" + Kill.Url);
#region Victim Regex
// Killmail Details[\W\w]+Victim:</td>[\W]+<td>(.*?">)?(?<MATCH>.+?)<
// Killmail Details[\W\w]+Corporation:</td>[\W]+<td>(.*?">)?(?<MATCH>.+?)<
// Killmail Details[\W\w]+Alliance</td>[\W]+<td>(.*?">)?(?<MATCH>.+?)<
// Killmail Details[\W\w]*?Ship:</td>[\W]+<td>(?<MATCH>.+?)[\W]
// Killmail Details[\W\w]*?Location:</td>\W+(.+?)">(?<MATCH>.+?)<
#endregion
string Name = GetCustomRegexContent(@"Killmail Details[\W\w]+Victim:</td>[\W]+<td>(.*?"">)?(?<MATCH>.+?)<");
string Corp =
GetCustomRegexContent("Killmail Details[\\W\\w]+Corporation:</td>[\\W]+<td>(.*?\">)?(?<MATCH>.+?)<");
string Alliance =
GetCustomRegexContent("Killmail Details[\\W\\w]+Alliance</td>[\\W]+<td>(.*?\">)?(?<MATCH>.+?)<");
string Ship = GetCustomRegexContent("Killmail Details[\\W\\w]*?Ship:</td>[\\W]+<td>(?<MATCH>.+?)[\\W]");
string Loc = GetCustomRegexContent("Killmail Details[\\W\\w]*?Location:</td>\\W+(.+?)\">(?<MATCH>.+?)<");
var victim = new Player {Alliance = Alliance, Corp = Corp, Name = Name, Ship = new Ship{ Name = Ship}};
kill.Victim = victim;
kill.System = Loc;
kill.Time = Kill.Time;
#region Killer Regex
// >Name:</td>[\W]+<td\sc(.*">)(?<MATCH>.+?)</a>
// >Corp:</td>[\W]+<td\sc(.*">)(?<MATCH>.+?)</a>
// >Alliance</td>[\W]+<td\sc(.*">)(?<MATCH>.+?)</a>
// >Ship:</td>[\W]+<td\scolspan="2">[\W]+(.+?)[\W]+[(</td>)|(Damage)]
#endregion
var Names = GetMatchList(">Name:</td>[\\W]+<td\\sc(.*\">)(?<MATCH>.+?)</a>");
var Corps = GetMatchList(">Corp:</td>[\\W]+<td\\sc(.*\">)(?<MATCH>.+?)</a>");
var Alliances = GetMatchList(">Alliance</td>[\\W]+<td\\sc(.*\">)(?<MATCH>.+?)</a>");
var Ships = GetMatchList(">Ship:</td>[\\W]+<td\\ colspan=\"2\">[\\W]+(?<MATCH>.+?)[\\W]+[(</td>)|(Damage)]");
for(int i = 0; i < Names.Count; i++)
{
var Killer = new Player
{
Name = Names[i],
Alliance = Alliances[i],
Ship = new Ship {Name = Ships[i]},
Corp = Corps[i]
};
kill.Killers.Add(Killer);
}
ret.Add(kill);
}
return ret;
}
