/// <summary>
/// 获取下一个题目
/// </summary>
/// <param name="clientId">int 客户端编号</param>
/// <returns>Hash 题目集合</returns>
public static Hash Next(int clientId)
{
string sql = "SELECT td.*,IFNULL(tcd.result, -1) AS result " +
"FROM tq_question td LEFT JOIN tc_client_question tcd ON td.questionId=tcd.questionId AND clientId=@0 " +
"WHERE IFNULL(tcd.result, -1) IN(-1, 0, 2) " +
"ORDER BY IFNULL(tcd.result, -1) DESC, td.degree ASC ";
using (MySqlADO ado = new MySqlADO())
{
HashCollection items = ado.GetHashCollection(sql, clientId).ToHashCollection("data");
if (items.Count > 0)
{
int degree = items[0].ToInt("degree");
int maxDegree = 1;
for (int i = 0; i < items.Count; i++)
{
if (items[i].ToInt("degree") > degree)
{
maxDegree = i;
break;
}
}
if (items[0].ToInt("result") == 0 || items[0].ToInt("result") == 2)
{
return(items[0]);
}
Random random = new Random();
return(items[random.Next(0, maxDegree)]);
}
return(new Hash());
}
}
/// <summary>
/// 复活继续
/// </summary>
/// <param name="clientId">int 客户端编号</param>
/// <returns>int 受影响的行数</returns>
public static int Continue(int clientId)
{
string sql = "UPDATE tc_client SET cards=cards-1,status=0 WHERE clientId=@0";
using (MySqlADO ado = new MySqlADO())
{
return(ado.NonQuery(sql, clientId));
}
}
/// <summary>
/// 今天为好友激活的次数
/// </summary>
/// <param name="clientId">int 客户端编号</param>
/// <param name="toClientId">int 激活客户端编号</param>
/// <returns>int 激活次数</returns>
public static int GetCountAtToday(int clientId, int toClientId)
{
string sql = "SELECT COUNT(*) FROM tc_client_live WHERE clientId=@0 AND toClientId=@1 AND createTime>@2";
using (MySqlADO ado = new MySqlADO())
{
return(ado.GetInt(sql, clientId, toClientId, DateTime.Now.ToString("yyyy-MM-dd")));
}
}
/// <summary>
/// 分享记录
/// </summary>
/// <param name="clientId">int 客户端编号</param>
/// <param name="shareFrom">string 分享来源</param>
/// <param name="shareAction">string 分享目的</param>
/// <param name="shareQuestionId">int 分享题目编号</param>
/// <returns>int 受影响的行数</returns>
public static int Create(int clientId, string shareFrom, string shareAction, int shareQuestionId)
{
string sql = "INSERT INTO tc_client_share (clientId, shareFrom, shareAction, shareQuestionId) VALUES(@0, @1, @2, @3)";
using (MySqlADO ado = new MySqlADO())
{
return(ado.NonQuery(sql, clientId, shareFrom, shareAction, shareQuestionId));
}
}
/// <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="openGId">string 群标识</param>
/// <returns>int 受影响的行数</returns>
public static int Create(int clientId, string openGId)
{
string sql = "INSERT INTO tc_client_group (clientId, openGId) VALUES(@0, @1) ON DUPLICATE KEY UPDATE clientId=VALUES(clientId), openGId=VALUES(openGId), updateTime=Now()";
using (MySqlADO ado = new MySqlADO())
{
return(ado.NonQuery(sql, clientId, openGId));
}
}
/// <summary>
/// 清空记录
/// </summary>
/// <param name="clientId">int 客户端编号</param>
/// <returns>int 受影响的行数</returns>
public static int Clear(int clientId)
{
string sql = "DELETE FROM tc_client_question WHERE clientId=@0";
using (MySqlADO ado = new MySqlADO())
{
return(ado.NonQuery(sql, clientId));
}
}
/// <summary>
/// 重新开始
/// </summary>
/// <param name="clientId">int 客户端编号</param>
/// <returns>int 受影响的行数</returns>
public static int Restart(int clientId)
{
string sql = "UPDATE tc_client SET score=0,status=0 WHERE clientId=@0";
using (MySqlADO ado = new MySqlADO())
{
return(ado.NonQuery(sql, clientId));
}
}
/// <summary>
/// 复活继续
/// </summary>
/// <param name="clientId">int 客户端编号</param>
/// <param name="isSkip">bool 是否跳过</param>
/// <returns>int 受影响的行数</returns>
public static int Revive(int clientId, bool isSkip)
{
string sql = "UPDATE tc_client SET lives=lives-@1,status=0 WHERE clientId=@0";
using (MySqlADO ado = new MySqlADO())
{
return(ado.NonQuery(sql, clientId, (isSkip ? 1: 0)));
}
}
/// <summary>
/// 设置客户端资料
/// </summary>
/// <param name="clientId">int 客户端编号</param>
/// <param name="nick">string 昵称</param>
/// <param name="gender">int 性别</param>
/// <param name="avatarUrl">string 头像图片</param>
/// <returns>int 受影响的行数</returns>
public static int SetUserInfo(int clientId, string nick, int gender, string avatarUrl)
{
string sql = "UPDATE tc_client SET nick=@1,gender=@2,avatarUrl=@3,lives=IF(IFNULL(actived,0)=0,2,lives),actived=1 WHERE clientId=@0";
using (MySqlADO ado = new MySqlADO())
{
return(ado.NonQuery(sql, clientId, nick, gender, avatarUrl));
}
}
/// <summary>
/// 生成一个新的客户端编号
/// </summary>
/// <returns>int 新客户端编号</returns>
public static int GetClientIdForCreate()
{
string sql = "SELECT MAX(clientId) FROM tc_client";
using (MySqlADO ado = new MySqlADO())
{
return(Parse.ToInt(ado.GetValue(sql), 1000000000) + 1);
}
}
/// <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>
/// <param name="toClientId">int 激活客户端编号</param>
/// <param name="openGId">string 群标识</param>
/// <returns>int 受影响的行数</returns>
public static int Activate(int clientId, int toClientId, string openGId)
{
string sql = "INSERT INTO tc_client_live (clientId,toClientId,openGId) VALUES(@0,@1,@2)";
using (MySqlADO ado = new MySqlADO())
{
return(ado.NonQuery(sql, clientId, toClientId, openGId));
}
}
/// <summary>
/// 激活复活卡
/// </summary>
/// <param name="clientId">int 客户端编号</param>
/// <returns>int 受影响的行数</returns>
public static int Activate(int clientId)
{
string sql = "UPDATE tc_client SET lives=lives+1 WHERE clientId=@0 ";
using (MySqlADO ado = new MySqlADO())
{
return(ado.NonQuery(sql, clientId));
}
}
/// <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>
/// 根据三方会话标识获取客户端编号
/// </summary>
/// <param name="session3rd">string 三方会话标识</param>
/// <returns>int 客户端编号</returns>
public static int GetClientIdBySession3rd(string session3rd)
{
string sql = "SELECT clientId FROM tc_client WHERE session3rd=@0";
using (MySqlADO ado = new MySqlADO())
{
return(ado.GetInt(sql, session3rd));
}
}
/// <summary>
/// 答题结果
/// </summary>
/// <param name="clientId">int 客户端编号</param>
/// <param name="questionId">int 题目编号</param>
/// <param name="result">ResultType 答题结果</param>
/// <returns>int 受影响的行数</returns>
public static int Answer(int clientId, int questionId, ResultType result)
{
string sql = "UPDATE tc_client_question SET result=@2 WHERE clientId=@0 AND questionId=@1";
using (MySqlADO ado = new MySqlADO())
{
return(ado.NonQuery(sql, clientId, questionId, (int)result));
}
}
/// <summary>
/// 根据 OpenId 获取客户端编号
/// </summary>
/// <param name="openId">string 微信标识</param>
/// <returns>int 客户端编号</returns>
public static int GetClientIdByOpenId(string openId)
{
string sql = "SELECT clientId FROM tc_client WHERE openId=@0";
using (MySqlADO ado = new MySqlADO())
{
return(ado.GetInt(sql, openId));
}
}
/// <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="clientId">int 客户端编号</param>
/// <returns>Hash 激活列表</returns>
public static Hash List(int clientId)
{
string sql = "SELECT tcs.*,tc.avatarUrl,tc.nick,tc.gender FROM tc_client_live tcs LEFT JOIN tc_client tc ON tcs.clientId=tc.clientId WHERE tcs.toClientId=@0 ORDER BY tcs.createTime DESC ";
using (MySqlADO ado = new MySqlADO())
{
return(ado.GetHashCollection(sql, clientId));
}
}
/// <summary>
/// 分享记录
/// </summary>
/// <param name="clientId">int 客户端编号</param>
/// <param name="shareFrom">string 分享来源</param>
/// <returns>int 受影响的行数</returns>
public static int Create(int clientId, string shareFrom)
{
string sql = "INSERT INTO tc_client_share (clientId, shareFrom) VALUES(@0, @1)";
using (MySqlADO ado = new MySqlADO())
{
return(ado.NonQuery(sql, clientId, shareFrom));
}
}
/// <summary>
/// 设置配置项目
/// </summary>
/// <param name="key">string 键名</param>
/// <param name="value">string 键值</param>
/// <returns>int 受影响的行数</returns>
public static int SetItem(string key, string value)
{
string sql = "UPDATE tz_setting SET `value`=@1 WHERE `key`=@0";
using (MySqlADO ado = new MySqlADO())
{
return(ado.NonQuery(sql, key, value));
}
}
/// <summary>
/// 判断两个客户端的关系类型
/// </summary>
/// <param name="fromClientId">int 邀请客户端编号</param>
/// <param name="toClientId">int 被邀请客户端编号</param>
/// <returns>int 关系类型</returns>
public static RelateType GetRelateType(int fromClientId, int toClientId)
{
string sql = "SELECT relateType FROM tc_client_relate WHERE fromClientId=@0 AND toClientId=@1";
using (MySqlADO ado = new MySqlADO())
{
return((RelateType)ado.GetInt(sql, fromClientId, toClientId));
}
}
/// <summary>
/// 更新关系条目
/// </summary>
/// <param name="fromClientId">int 邀请客户端编号</param>
/// <param name="toClientId">int 被邀请客户端编号</param>
/// <param name="relateType">RelateType 关系类型</param>
/// <returns>int 受影响的行数</returns>
public static int Update(int fromClientId, int toClientId, RelateType relateType)
{
string sql = "UPDATE tc_client_relate SET relateType=IF(relateType>@2,@2,relateType) WHERE fromClientId=@0 AND toClientId=@1 ";
using (MySqlADO ado = new MySqlADO())
{
return(ado.NonQuery(sql, fromClientId, toClientId, (int)relateType));
}
}
/// <summary>
/// 创建关系条目
/// </summary>
/// <param name="fromClientId">int 邀请客户端编号</param>
/// <param name="toClientId">int 被邀请客户端编号</param>
/// <param name="relateType">RelateType 关系类型</param>
/// <returns>int 受影响的行数</returns>
public static int Create(int fromClientId, int toClientId, RelateType relateType)
{
string sql = "INSERT INTO tc_client_relate (fromClientId, toClientId, relateType, openGId) VALUES(@0, @1, @2, @3) ON DUPLICATE KEY UPDATE fromClientId=VALUES(fromClientId), toClientId=VALUES(toClientId), relateType=VALUES(relateType), openGId=VALUES(openGId), updateTime=Now()";
using (MySqlADO ado = new MySqlADO())
{
return(ado.NonQuery(sql, fromClientId, toClientId, (int)relateType, String.Empty));
}
}
/// <summary>
/// 获取所有题目选项
/// </summary>
/// <param name="exceptDinosaurId">int 排除题目编号</param>
/// <returns>Hash 题目集合</returns>
public static Hash AllName(int exceptDinosaurId)
{
string sql = "SELECT name FROM td_dinosaur WHERE dinosaurId<>@0";
using (MySqlADO ado = new MySqlADO())
{
return(ado.GetHashCollection(sql, exceptDinosaurId));
}
}
/// <summary>
/// 重新开始
/// </summary>
/// <param name="clientId">int 客户端编号</param>
/// <returns>int 受影响的行数</returns>
public static int Restart(int clientId)
{
string sql = "DELETE FROM tc_client_dinosaur WHERE clientId=@0";
using (MySqlADO ado = new MySqlADO())
{
return(ado.NonQuery(sql, clientId));
}
}
/// <summary>
/// 准备答题
/// </summary>
/// <param name="clientId">int 客户端编号</param>
/// <param name="questionId">int 题目编号</param>
/// <returns>int 受影响的行数</returns>
public static int Ready(int clientId, int questionId)
{
string sql = "INSERT INTO tc_client_question (clientId,questionId,result) VALUES(@0,@1,0) " +
"ON DUPLICATE KEY UPDATE clientId=VALUES(clientId),questionId=VALUES(questionId),result=VALUES(result)";
using (MySqlADO ado = new MySqlADO())
{
return(ado.NonQuery(sql, clientId, questionId));
}
}
