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