public IList <Tuple <SenkaData, string> > GetAllServerRanking(int limit)
{
List <Tuple <SenkaData, string> > dataset = new List <Tuple <SenkaData, string> >();
string _sql = "SELECT Servers.NickName, Senka.*, Dates.Date, RankType.RankName FROM Senka" +
@" JOIN RankType ON Senka.RankType = RankType.ID" +
@" JOIN Dates ON Senka.DateID = Dates.ID" +
@" JOIN Servers ON Senka.ServerID = Servers.ID" +
@" WHERE Senka.DateID = (SELECT MIN(LastUpdate) FROM Servers)" +
@" ORDER BY RankPoint DESC LIMIT " + limit.ToString();
using (var DataBaseConnection = NewSQLiteConnection())
using (var cmd = new SQLiteCommand(_sql, DataBaseConnection)) {
DataBaseConnection.Open();
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
SenkaData data = new SenkaData(reader);
string server = Convert.ToString(reader["NickName"]);
Tuple <SenkaData, string> tuple = new Tuple <SenkaData, string>(data, server);
dataset.Add(tuple);
}
}
}
return(dataset);
}
public void SetDelta(SenkaData compareData)
{
_rankingDelta = compareData._ranking - this._ranking;
_rankPointDelta = this._rankPoint - compareData._rankPoint;
}
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());
}
public IList <SenkaData> GetPlayerBoundList(int serverID, SenkaData lastData)
{
DateTime start = new DateTime(lastData.Date.DateTime.Year, lastData.Date.DateTime.Month, 1);
List <SenkaData> dataset = new List <SenkaData>();
int upper, lower;
if (lastData.Ranking == 1)
{
upper = 0;
lower = 5;
}
else if (lastData.Ranking <= 5)
{
upper = 1;
lower = 5;
}
else if (lastData.Ranking <= 20)
{
upper = 5;
lower = 20;
}
else if (lastData.Ranking <= 100)
{
upper = 20;
lower = 100;
}
else if (lastData.Ranking <= 500)
{
upper = 100;
lower = 500;
}
else
{
upper = 500;
lower = 990;
}
string playerSQL = " WHERE (Ranking = @RankingUpper OR Ranking = @RankingLower)" +
@" AND Date > @DateStart AND Date <= @DateEnd" +
@" AND ServerID = @ServerID";
using (var DataBaseConnection = NewSQLiteConnection())
using (var cmd = new SQLiteCommand(DefaultSQL + playerSQL, DataBaseConnection)) {
DataBaseConnection.Open();
SQLiteParameter[] paras = new SQLiteParameter[5] {
new SQLiteParameter("@RankingUpper", DbType.Int32),
new SQLiteParameter("@RankingLower", DbType.Int32),
new SQLiteParameter("@DateStart", DbType.DateTime),
new SQLiteParameter("@DateEnd", DbType.DateTime),
new SQLiteParameter("@ServerID", DbType.Int32)
};
cmd.Parameters.AddRange(paras);
cmd.Parameters["@RankingUpper"].Value = upper;
cmd.Parameters["@RankingLower"].Value = lower;
cmd.Parameters["@DateStart"].Value = start;
cmd.Parameters["@DateEnd"].Value = lastData.Date.DateTime;
cmd.Parameters["@ServerID"].Value = serverID;
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
dataset.Add(new SenkaData(reader));
}
}
}
return(dataset);
}
