public void RefreshLastUpdateTime()
{
string _sql = "SELECT Dates.* FROM Senka" +
@" JOIN Dates ON Senka.DateID = Dates.ID" +
@" WHERE ServerID = " + _id.ToString() +
@" GROUP BY Date ORDER BY Dates.ID DESC LIMIT 2";
using (var DataBaseConnection = SenkaManager.NewSQLiteConnection())
using (var cmd = new SQLiteCommand(_sql, DataBaseConnection)) {
DataBaseConnection.Open();
using (var reader = cmd.ExecuteReader()) {
if (reader.Read())
{
_lastUpdateTime = new SenkaTimeInfo(reader["ID"], reader["Date"]);
if (reader.Read())
{
_compareToTime = new SenkaTimeInfo(reader["ID"], reader["Date"]);
}
else
{
_compareToTime = _lastUpdateTime;
}
}
else
{
_enabled = false;
}
}
}
}
public SenkaData(System.Data.SQLite.SQLiteDataReader data)
{
_date = new SenkaTimeInfo(data["DateID"], data["Date"]);
_ranking = Convert.ToInt32(data["Ranking"]);
_level = Convert.ToInt32(data["Level"]);
_playerName = Convert.ToString(data["PlayerName"]);
_playerID = Convert.ToInt64(data["PlayerID"]);
_comment = Convert.ToString(data["Comment"]);
_rankPoint = Convert.ToInt32(data["RankPoint"]);
_rankName = Convert.ToString(data["RankName"]);
_medals = Convert.ToInt32(data["Medals"]);
}
public bool GetDateID(DateTime date, out SenkaTimeInfo dateInfo, out SenkaTimeInfo compareDateInfo)
{
if (date.Hour != 3 && date.Hour != 15)
{
dateInfo = compareDateInfo = null;
return(false);
}
else
{
string _sql1 = "SELECT ID FROM Dates WHERE Date = @Date";
string _sql2 = "SELECT MAX(DateID), Date FROM Senka JOIN Dates ON Senka.DateID = Dates.ID" +
" WHERE DateID < @DateID AND ServerID = " + _id.ToString();
try {
using (var DataBaseConnection = SenkaManager.NewSQLiteConnection())
using (var cmd = new SQLiteCommand(_sql1, DataBaseConnection)) {
DataBaseConnection.Open();
cmd.Parameters.Add(new SQLiteParameter("@Date", DbType.DateTime));
cmd.Parameters["@Date"].Value = date;
object _dateID = cmd.ExecuteScalar();
if (_dateID != null)
{
dateInfo = new SenkaTimeInfo(_dateID, date);
cmd.CommandText = _sql2;
cmd.Parameters.Clear();
cmd.Parameters.Add(new SQLiteParameter("@DateID", DbType.Int64));
cmd.Parameters["@DateID"].Value = dateInfo.ID;
using (var reader = cmd.ExecuteReader()) {
if (reader.Read())
{
compareDateInfo = new SenkaTimeInfo(reader[0], reader[1]);
}
else
{
throw new FormatException();
}
}
}
else
{
throw new FormatException();
}
}
return(true);
} catch (Exception) {
dateInfo = compareDateInfo = null;
return(false);
}
}
}
public IList <SenkaData> GetRankingList(int limit, SenkaTimeInfo date, SenkaTimeInfo compareDate)
{
List <SenkaData> latestDataset = new List <SenkaData>();
Dictionary <long, SenkaData> compareDataset = new Dictionary <long, SenkaData>();
string serverSQL = " WHERE DateID = @DateID AND ServerID = @ServerID";
string latestSQL;
if (limit == 0)
{
latestSQL = " AND (Ranking <= 100 OR Ranking = 500 OR Ranking = 990)";
}
else
{
latestSQL = " AND Ranking <= " + limit.ToString();
}
if (date.DateTime.Day == 1 && date.DateTime.Hour == 3)
{
compareDate = date;
}
using (var DataBaseConnection = SenkaManager.NewSQLiteConnection())
using (var cmd = new SQLiteCommand(SenkaManager.DefaultSQL + serverSQL + latestSQL, DataBaseConnection)) {
DataBaseConnection.Open();
SQLiteParameter[] paras = new SQLiteParameter[2] {
new SQLiteParameter("@DateID", DbType.Int64),
new SQLiteParameter("@ServerID", DbType.Int32)
};
cmd.Parameters.AddRange(paras);
cmd.Parameters["@ServerID"].Value = _id;
cmd.Parameters["@DateID"].Value = date.ID;
//Latest Data
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
latestDataset.Add(new SenkaData(reader));
}
}
//Compare Data
if (date.ID != compareDate.ID)
{
cmd.CommandText = SenkaManager.DefaultSQL + serverSQL;
cmd.Parameters["@DateID"].Value = compareDate.ID;
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
SenkaData senkadata = new SenkaData(reader);
compareDataset.Add(senkadata.PlayerID, senkadata);
}
}
foreach (var newdata in latestDataset)
{
long playerID = newdata.PlayerID;
if (compareDataset.ContainsKey(playerID))
{
newdata.SetDelta(compareDataset[playerID]);
}
}
}
}
return(latestDataset.OrderBy(d => d.Ranking).ToList());
}
