/// <summary>
/// 获取客户端回答指定题目的信息
/// </summary>
/// <param name="clientId">int 客户端编号</param>
/// <param name="questionId">int 题目编号</param>
/// <returns>Hash 答题信息</returns>
public static Hash GetByClientIdAndQuestionId(int clientId, int questionId)
{
string sql = "SELECT * FROM tc_client_question WHERE clientId=@0 AND questionId=@1";
using (MySqlADO ado = new MySqlADO())
{
return(ado.GetHash(sql, clientId, questionId));
}
}
/// <summary>
/// 获取题目信息
/// </summary>
/// <param name="questionId">int 题目编号</param>
/// <returns>Hash 题目详细信息</returns>
public static Hash GetByQuestionId(int questionId)
{
string sql = "SELECT * FROM tq_question WHERE questionId=@0";
using (MySqlADO ado = new MySqlADO())
{
return(ado.GetHash(sql, questionId));
}
}
/// <summary>
/// 根据 ClientId 获取用户信息
/// </summary>
/// <param name="clientId">int 用户编号</param>
/// <returns>Hash 用户信息</returns>
public static Hash GetByClientId(int clientId)
{
string sql = "SELECT * FROM tc_client WHERE clientId=@0";
using (MySqlADO ado = new MySqlADO())
{
return(ado.GetHash(sql, clientId));
}
}
/// <summary>
/// 根据客户端获取排位信息
/// </summary>
/// <param name="clientId">int 邀请客户端编号</param>
/// <param name="score">int 成绩</param>
/// <returns>Hash 排名信息</returns>
public static Hash GetRank(int clientId, int score)
{
string sql = "SELECT ((SELECT COUNT(*) FROM tc_client WHERE score<@1)*100)/(SELECT COUNT(*) FROM tc_client) AS position ";
using (MySqlADO ado = new MySqlADO())
{
return(ado.GetHash(sql, clientId, score));
}
}
/// <summary>
/// 根据三方会话标识获取用户信息
/// </summary>
/// <param name="session3rd">string 三方会话标识</param>
/// <returns>Hash 用户信息</returns>
public static Hash GetBySession3rd(string session3rd)
{
string sql = "SELECT * FROM tc_client WHERE session3rd=@0";
using (MySqlADO ado = new MySqlADO())
{
return(ado.GetHash(sql, session3rd));
}
}
/// <summary>
/// 根据 OpenId 获取用户信息
/// </summary>
/// <param name="openId">string 微信标识</param>
/// <returns>Hash 用户信息</returns>
public static Hash GetByOpenId(string openId)
{
string sql = "SELECT * FROM tc_client WHERE openId=@0";
using (MySqlADO ado = new MySqlADO())
{
return(ado.GetHash(sql, openId));
}
}
/// <summary>
/// 获取客户端进度信息
/// </summary>
/// <param name="clientId">int 客户端编号</param>
/// <returns>Hash 进度信息</returns>
public static Hash GetByClientId(int clientId)
{
string sql = "SELECT clientId,score, " +
" (SELECT COUNT(*) FROM tc_client_dinosaur WHERE clientId=@0 AND result=1) AS doneCount," +
" (SELECT COUNT(*) FROM td_dinosaur) AS allCount " +
"FROM tc_client WHERE clientId=@0 ";
using (MySqlADO ado = new MySqlADO())
{
return(ado.GetHash(sql, clientId));
}
}
/// <summary>
/// 获取客户端进度信息
/// </summary>
/// <param name="clientId">int 客户端编号</param>
/// <returns>Hash 进度信息</returns>
public static Hash GetPositionByClientId(int clientId)
{
string sql = "SELECT tc.*, " +
" (SELECT COUNT(*) FROM tc_client WHERE score>tc.score) AS rankIndex, " +
" ((SELECT COUNT(*) FROM tc_client WHERE score<=tc.score AND clientId<>tc.clientId) * 100 / (SELECT COUNT(*) FROM tc_client)) AS rankPosition, " +
" (SELECT COUNT(*) FROM tc_client_question WHERE clientId=@0 AND result=1) AS questionCorrect, " +
" (SELECT COUNT(*) FROM tc_client_question WHERE clientId=@0) AS questionAnswered, " +
" (SELECT COUNT(*) FROM tq_question) AS questionAmount " +
"FROM tc_client tc WHERE clientId=@0 ";
using (MySqlADO ado = new MySqlADO())
{
return(ado.GetHash(sql, clientId));
}
}