//通过开始时间、结束时间、结果获取总提交数
public static int getTotSubmitByStEdAndNum(string st, string ed, int num)
{
sql = "" +
"SELECT count(solution_id) as cnt FROM solution " +
"WHERE contest_id = " + DBTool.contest_id + " " +
"AND num = " + num +
" AND UNIX_TIMESTAMP(in_date) >= UNIX_TIMESTAMP('" + st + "')" +
" AND UNIX_TIMESTAMP(in_date) <= UNIX_TIMESTAMP('" + ed + "')";
try
{
ds = DBTool.Query(sql);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
if (ds.Tables[0].Rows.Count > 0)
{
return(int.Parse(ds.Tables[0].Rows[0][0].ToString()));
}
else
{
return(0);
}
}
//获取过题队伍数
public static int getProblemTeamByCnt(int cnt)
{
sql = "" +
"SELECT C.num FROM(" + " " +
"SELECT B.cnt, count(B.cnt) as num FROM(" + " " +
"SELECT user_id, count(A.num) as cnt FROM(" + " " +
"SELECT user_id, num, min(in_date) FROM solution" + " " +
"WHERE contest_id = " + DBTool.contest_id + " " +
"AND result = '4'" + " " +
"GROUP BY user_id, num" + " " +
"ORDER BY user_id" + " " +
") AS A GROUP BY A.user_id" + " " +
") AS B GROUP BY B.cnt ) AS C WHERE C.cnt = " + cnt;
try
{
ds = DBTool.Query(sql);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
if (ds.Tables[0].Rows.Count > 0)
{
return(int.Parse(ds.Tables[0].Rows[0][0].ToString()));
}
else
{
return(0);
}
}
//获取contest结束时间
public static string getEndTime()
{
sql = "SELECT end_time FROM contest WHERE contest_id = " + DBTool.contest_id;
try
{
ds = DBTool.Query(sql);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
return(ds.Tables[0].Rows[0][0].ToString());
}
//获取当前时间
public static string getNow()
{
sql = "SELECT NOW()";
try
{
ds = DBTool.Query(sql);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
return(ds.Tables[0].Rows[0][0].ToString());
}
//获取题数
public static int getProblemCnt()
{
sql = "SELECT max(num) as Num FROM contest_problem WHERE contest_id = " + DBTool.contest_id;
try
{
ds = DBTool.Query(sql);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
if (ds.Tables[0].Rows.Count > 0)
{
return(int.Parse(ds.Tables[0].Rows[0][0].ToString()));
}
else
{
return(0);
}
}
//获取首A时间
public static string getFirstSolveTime(int num)
{
sql = "SELECT count(in_date) as num, min(in_date) " +
"FROM solution " +
"WHERE result = 4 " +
"AND contest_id = " + contest_id + " " +
"AND num = " + num;
try
{
ds = DBTool.Query(sql);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
if (int.Parse(ds.Tables[0].Rows[0][0].ToString()) > 0)
{
return(ds.Tables[0].Rows[0][1].ToString());
}
else
{
return(null);
}
}