public Voter(VoterInfo vi, ElectionInfo game) { _info = vi; _game = game; }
public IEnumerable <VoterInfo> GetVotes(Int32 threadId, DateTime startTime, DateTime endTime, object game) { String sql = @" SELECT GameRole.roleid, Player.posterid, Poster.postername, (SELECT COUNT(*) FROM Post WHERE (GameRole.threadid = @p2) AND ((Player.endtime IS NULL) OR (player.endtime > @p4)) AND (Post.threadid = GameRole.threadid) AND (Post.posterid = Player.posterid) AND (Post.posttime >= @p4) AND (Post.posttime <= @p3) ) AS postcount, (SELECT MAX(Post.postid) FROM Bolded, Post WHERE (GameRole.threadid = @p2) AND ((Player.endtime IS NULL) OR (player.endtime > @p4)) AND (Post.threadid = GameRole.threadid) AND (Bolded.postid = Post.postid) AND (Post.posterid = Player.posterid) AND (Post.posttime >= @p4) AND (Post.posttime <= @p3) AND (Bolded.ignore = 0) ) AS bolded FROM GameRole JOIN Player ON (GameRole.roleid = Player.roleid) JOIN Poster ON (Poster.posterid = Player.posterid) WHERE (GameRole.threadid = @p2) AND ((Player.endtime IS NULL) OR (player.endtime > @p4)) GROUP BY Poster.postername ; "; SortableBindingList <VoterInfo> voters = new SortableBindingList <VoterInfo>(); Stopwatch watch = new Stopwatch(); watch.Start(); using (SqliteConnection dbRead = new SqliteConnection(_connect)) { dbRead.Open(); using (SqliteCommand cmd = new SqliteCommand(sql, dbRead)) { cmd.Parameters.Add(new SqliteParameter("@p2", threadId)); SqliteParameter pEndTime = new SqliteParameter("@p3", System.Data.DbType.DateTime); pEndTime.Value = endTime.ToUniversalTime(); cmd.Parameters.Add(pEndTime); SqliteParameter pStartTime = new SqliteParameter("@p4", System.Data.DbType.DateTime); pStartTime.Value = startTime.ToUniversalTime(); cmd.Parameters.Add(pStartTime); using (SqliteDataReader r = cmd.ExecuteReader()) { while (r.Read()) { Int32 roleId = r.GetInt32(0); Int32 playerId = r.GetInt32(1); String name = r.GetString(2); Int32 count = r.GetInt32(3); Int32? boldedPost = null; if (!r.IsDBNull(4)) { boldedPost = r.GetInt32(4); } VoterInfo v = new VoterInfo(name, count, boldedPost); voters.Add(v); } } } sql = @" SELECT Bolded.bolded, Bolded.position, Post.postnumber, Post.posttime FROM Bolded JOIN Post ON (Bolded.postid = Post.postid) WHERE (Bolded.postid = @p1) AND (Bolded.ignore = 0) ORDER BY Bolded.position DESC LIMIT 1 ; "; using (SqliteCommand cmd = new SqliteCommand(sql, dbRead)) { foreach (VoterInfo v in voters) { Int32 id = v.PostId; if (id <= 0) { continue; } cmd.Parameters.Add(new SqliteParameter("@p1", id)); using (SqliteDataReader r = cmd.ExecuteReader()) { while (r.Read()) { String bolded = r.GetString(0); Int32 position = r.GetInt32(1); Int32 number = r.GetInt32(2); DateTimeOffset time = r.GetDateTime(3); v.SetVote(bolded, number, time, id, position); } } } } } watch.Stop(); //Trace.TraceInformation("after post counts {0}", watch.Elapsed.ToString()); return(voters); }