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()); }