/// <summary> /// 判断是否为管理员 /// </summary> /// <param name="qq">qq号</param> /// <returns></returns> public static bool isAdmin(string qq) { bool flag = false; string sql = "SELECT qq FROM ichunqiu_admin WHERE qq = @qq"; var parameter = new List <MySqlParameter> { new MySqlParameter("@qq", qq) }; MySqlConnection connection = null; try { connection = ConnectionPool.getPool().getConnection(); while (connection == null) { } using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection)) { // 添加参数 if (parameter.Count > 0) { mySqlCommand.Parameters.AddRange(parameter.ToArray()); } // 提取数据 using (MySqlDataReader myDataReader = mySqlCommand.ExecuteReader()) { int count = 0; while (myDataReader.Read() == true) { count++; } if (count > 0) { flag = true; } } } } catch (Exception) { flag = false; } finally { if (connection != null) { connection.Close(); } // 关闭数据库链接 ConnectionPool.getPool().closeConnection(connection); } return(flag); }
/// <summary> /// 获取所有的用户的历史提现总金额 /// </summary> private static Hashtable getAllHistorySumMoney(Hashtable userInfo) { Hashtable userBlank = new Hashtable(); MySqlConnection connection = null; try { connection = ConnectionPool.getPool().getConnection(); // 链接为null就执行等待 while (connection == null) { } foreach (DictionaryEntry item in userInfo) { string sql = string.Format("SELECT SUM(ABS(money)) as money FROM ichunqiu_blank_history WHERE user_qq = '{0}' AND operation = '0'", item.Key); using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection)) { using (MySqlDataReader myDataReader = mySqlCommand.ExecuteReader()) { while (myDataReader.Read() == true) { if (myDataReader["money"] is DBNull) { userBlank.Add(userInfo[item.Key], 0); } else { userBlank.Add(userInfo[item.Key], myDataReader["money"]); } break; } } } } } catch (Exception) { throw; } finally { if (connection != null) { connection.Close(); } // 关闭数据库链接 ConnectionPool.getPool().closeConnection(connection); } return(userBlank); }
/// <summary> /// 通过QQ获取作家的余额信息 /// </summary> /// <param name="qq">qq号</param> /// <returns>余额</returns> public static string getBlankMoney(string qq) { string sql = "SELECT user_money FROM ichunqiu_blank WHERE user_qq = @qq"; var parameter = new List <MySqlParameter>(); string userMoney = ""; parameter.Add(new MySqlParameter("@qq", qq)); MySqlConnection connection = null; try { connection = ConnectionPool.getPool().getConnection(); while (connection == null) { } using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection)) { // 添加参数 if (parameter.Count > 0) { mySqlCommand.Parameters.AddRange(parameter.ToArray()); } // 提取数据 using (MySqlDataReader myDataReader = mySqlCommand.ExecuteReader()) { while (myDataReader.Read() == true) { userMoney = myDataReader["user_money"].ToString(); break; } } } } catch (Exception e) { throw e; } finally { if (connection != null) { connection.Close(); } // 关闭数据库链接 ConnectionPool.getPool().closeConnection(connection); } return(userMoney); }
/// <summary> /// 获得某一个时间使用的人数 /// </summary> /// <returns></returns> public static Hashtable getThisWeek() { string sql = "select user_qq,DATE_FORMAT(create_date,'%Y-%m-%d') as date from ichunqiu_user where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_date) GROUP BY user_qq,DATE_FORMAT(create_date,'%Y-%m-%d');"; Hashtable hashtable = new Hashtable(); MySqlConnection connection = null; try { connection = ConnectionPool.getPool().getConnection(); // 链接为null就执行等待 while (connection == null) { } using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection)) { // 提取数据 using (MySqlDataReader myDataReader = mySqlCommand.ExecuteReader()) { while (myDataReader.Read() == true) { string date = myDataReader["date"].ToString(); if (hashtable.Contains(date)) { hashtable[date] = (Convert.ToInt16(hashtable[date]) + 1).ToString(); } else { hashtable.Add(date, "1"); } } } } } catch (Exception e) { throw e; } finally { if (connection != null) { connection.Close(); } ConnectionPool.getPool().closeConnection(connection); } return(hashtable); }
/// <summary> /// 提现记录 /// </summary> /// <param name="qq">qq号</param> /// <returns>提现记录</returns> public static string getBlankHistory(string qq) { string sql = "SELECT money,create_date FROM ichunqiu_blank_history WHERE user_qq = @qq AND operation = '0' ;"; var parameter = new List <MySqlParameter>(); string resultStr = ""; parameter.Add(new MySqlParameter("@qq", qq)); MySqlConnection connection = null; try { connection = ConnectionPool.getPool().getConnection(); while (connection == null) { } using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection)) { // 添加参数 if (parameter.Count > 0) { mySqlCommand.Parameters.AddRange(parameter.ToArray()); } // 提取数据 using (MySqlDataReader myDataReader = mySqlCommand.ExecuteReader()) { while (myDataReader.Read() == true) { resultStr += myDataReader["money"].ToString() + "\t" + myDataReader["create_date"] + "\n"; } } } } catch (Exception e) { throw e; } finally { if (connection != null) { connection.Close(); } // 关闭数据库链接 ConnectionPool.getPool().closeConnection(connection); } return(resultStr); }
/// <summary> /// 检索用户是否为作家团成员 /// </summary> /// <param name="qq">qq号</param> /// <returns></returns> public static bool exitsUser(string qq) { MySqlConnection connection = null; bool resultFlag = false; try { connection = ConnectionPool.getPool().getConnection(); // 链接为null就执行等待 while (connection == null) { } string sql = "SELECT user_money FROM ichunqiu_blank WHERE user_qq = @qq"; var parameter = new List <MySqlParameter>(); parameter.Add(new MySqlParameter("@qq", qq)); using (MySqlCommand cmd = new MySqlCommand(sql, connection)) { if (parameter.Count > 0) { cmd.Parameters.AddRange(parameter.ToArray()); } using (MySqlDataReader myDataReader = cmd.ExecuteReader()) { while (myDataReader.Read() == true) { resultFlag = true; break; } } } } catch (Exception) { throw; } finally { if (connection != null) { connection.Close(); } // 关闭数据库链接 ConnectionPool.getPool().closeConnection(connection); } return(resultFlag); }
//private readonly String connectionStr = "server=localhost;user id=root;password=root;database=ichunqiu;charset=utf8"; /// <summary> /// 根据SQL获取DataTable数据表 /// </summary> /// <param name="SQL">查询语句</param> /// <param name="Table_name">返回表的表名</param> /// <returns></returns> public static string GetContent(string sql, List <MySqlParameter> parameter, string content) { string resultStr = ""; if (content == "" || "".Equals(content) || content.Length == 0 || content == null) { content = "\n【文章】"; } MySqlConnection connection = null; try { connection = ConnectionPool.getPool().getConnection(); using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection)) { // 添加参数 if (parameter.Count > 0) { mySqlCommand.Parameters.AddRange(parameter.ToArray()); } // 提取数据 using (MySqlDataReader myDataReader = mySqlCommand.ExecuteReader()) { while (myDataReader.Read() == true) { string tmpStr = content + myDataReader["title"] + "\n" + myDataReader["url"] + "\n"; resultStr += tmpStr; } } } } catch (Exception e) { throw e; } finally { if (connection != null) { connection.Close(); } // 关闭数据库链接 ConnectionPool.getPool().closeConnection(connection); } return(resultStr); }
/// <summary> /// 是否为超级管理员 /// </summary> /// <param name="qq">检验qq</param> /// <returns></returns> public static bool isSuperAdmin(string qq) { string sql = "SELECT is_admin FROM ichunqiu_admin WHERE qq = @qq "; var parameter = new List <MySqlParameter>(); parameter.Add(new MySqlParameter("@qq", qq)); MySqlConnection connection = null; bool flag = false; try { connection = ConnectionPool.getPool().getConnection(); while (connection == null) { } using (MySqlCommand cmd = new MySqlCommand(sql, connection)) { using (MySqlDataReader myReader = cmd.ExecuteReader()) { while (myReader.Read() == true) { if ("1".Equals(myReader["is_admin"].ToString())) { flag = true; } } } } } catch (Exception) { flag = false; } finally { if (connection != null) { connection.Close(); } // 关闭数据库链接 ConnectionPool.getPool().closeConnection(connection); } return(flag); }
/// <summary> /// 插入提现记录 /// </summary> /// <returns></returns> public static int inserMoneyHistory(string qq, decimal money, string operation) { string sql = "INSERT INTO ichunqiu_blank_history(id,user_qq,money,operation,create_date,update_date) VALUES(DEFAULT,@qq,@user_money,@operation,NOW(),NOW())"; var parameter = new List <MySqlParameter>(); int count = 0; parameter.Add(new MySqlParameter("@user_money", money)); parameter.Add(new MySqlParameter("@qq", qq)); parameter.Add(new MySqlParameter("@operation", operation)); MySqlConnection connection = null; try { connection = ConnectionPool.getPool().getConnection(); // 链接为null就执行等待 while (connection == null) { } using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection)) { // 添加参数 if (parameter.Count > 0) { mySqlCommand.Parameters.AddRange(parameter.ToArray()); } // 提取数据 count = mySqlCommand.ExecuteNonQuery(); } } catch (Exception) { count = 0; } finally { if (connection != null) { connection.Close(); } ConnectionPool.getPool().closeConnection(connection); } return(count); }
/// <summary> /// 提取课程信息 /// </summary> /// <param name="sql">sql</param> /// <param name="parameter">参数信息</param> /// <returns></returns> private static string geClass(string sql, List <MySqlParameter> parameter) { string resultStr = ""; MySqlConnection connection = null; try { connection = ConnectionPool.getPool().getConnection(); using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection)) { // 添加参数 if (parameter.Count > 0) { mySqlCommand.Parameters.AddRange(parameter.ToArray()); } // 提取数据 using (MySqlDataReader myDataReader = mySqlCommand.ExecuteReader()) { while (myDataReader.Read() == true) { string tmpStr = "\n【课程】" + myDataReader["class_name"] + "\n" + myDataReader["class_url"] + "\n"; resultStr += tmpStr; } } } } catch (Exception e) { throw e; } finally { if (connection != null) { connection.Close(); } // 关闭数据库链接 ConnectionPool.getPool().closeConnection(connection); } return(resultStr); }
/// <summary> /// 日志记录 /// </summary> /// <param name="qq"></param> /// <returns></returns> public static void addUserData(string qq, string content, string msg) { string sql = "INSERT INTO ichunqiu_user(id,user_qq,content,send_msg,create_date,update_date) VALUES(DEFAULT,@qq,@content,@msg,NOW(),NOW())"; var parameter = new List <MySqlParameter>(); parameter.Add(new MySqlParameter("@qq", qq)); parameter.Add(new MySqlParameter("@content", content)); parameter.Add(new MySqlParameter("@msg", msg)); MySqlConnection connection = null; try { connection = ConnectionPool.getPool().getConnection(); // 链接为null就执行等待 while (connection == null) { } using (MySqlCommand cmd = new MySqlCommand(sql, connection)) { // 添加参数 if (parameter.Count > 0) { cmd.Parameters.AddRange(parameter.ToArray()); } cmd.ExecuteNonQuery(); } } catch (Exception) { throw; } finally { if (connection != null) { connection.Close(); } ConnectionPool.getPool().closeConnection(connection); } }
/// <summary> /// 获取使用总数人 /// </summary> /// <returns></returns> public static int getUserTotal() { int count = 0; string sql = "SELECT user_qq FROM ichunqiu_user GROUP BY user_qq;"; MySqlConnection connection = null; try { connection = ConnectionPool.getPool().getConnection(); // 链接为null就执行等待 while (connection == null) { } using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection)) { using (MySqlDataReader myDataReader = mySqlCommand.ExecuteReader()) { while (myDataReader.Read() == true) { count++; } } } } catch (Exception) { throw; } finally { if (connection != null) { connection.Close(); } ConnectionPool.getPool().closeConnection(connection); } return(count); }
/// <summary> /// 获取今日使用的数据 /// </summary> /// <returns></returns> public static int getToday() { int count = 0; string sql = "select user_qq from ichunqiu_user where to_days(create_date) = to_days(now()) GROUP BY user_qq;"; MySqlConnection connection = null; try { connection = ConnectionPool.getPool().getConnection(); // 链接为null就执行等待 while (connection == null) { } using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection)) { using (MySqlDataReader myDataReader = mySqlCommand.ExecuteReader()) { while (myDataReader.Read() == true) { count++; } } } } catch (Exception) { throw; } finally { if (connection != null) { connection.Close(); } ConnectionPool.getPool().closeConnection(connection); } return(count); }
/// <summary> /// 获取财富榜前三十 /// </summary> /// <returns></returns> public static string Top30Money() { // 未提取存款 Hashtable userMoney = new Hashtable(); Hashtable userInfo = new Hashtable(); // 已经提取存款 Hashtable userBlank = new Hashtable(); // 财富榜前20 Hashtable userData = new Hashtable(); string resultMsg = ""; MySqlConnection connection = null; try { connection = ConnectionPool.getPool().getConnection(); // 链接为null就执行等待 while (connection == null) { } string sql = "SELECT bbs_nick_name,user_money,user_qq FROM ichunqiu_blank"; using (MySqlCommand cmd = new MySqlCommand(sql, connection)) { using (MySqlDataReader myDataReader = cmd.ExecuteReader()) { while (myDataReader.Read() == true) { userMoney.Add(myDataReader["bbs_nick_name"], myDataReader["user_money"]); userInfo.Add(myDataReader["user_qq"], myDataReader["bbs_nick_name"]); } } } // 提取历史提取总金额 userBlank = getAllHistorySumMoney(userInfo); // ArrayList allMoney = new ArrayList(); List <Decimal> allMoney = new List <decimal>(); foreach (DictionaryEntry item in userMoney) { decimal blance = Convert.ToDecimal(item.Value); decimal historyBlance = Convert.ToDecimal(userBlank[item.Key]); userData.Add(item.Key, historyBlance + blance); allMoney.Add(historyBlance + blance); } /* * List<decimal> allMoney = new List<decimal>(); * foreach (string item in userData.Values) * { * allMoney.Add(Convert.ToDecimal(item)); * }*/ // 降序 allMoney.Sort((x, y) => - x.CompareTo(y)); int i = 0; foreach (decimal money in allMoney) { foreach (DictionaryEntry item in userData) { if (Convert.ToDecimal(item.Value) == money) { resultMsg += string.Format("NO【{0}】:{1}身价{2},已提取存款{3},未提取存款{4}\n", (i + 1).ToString(), Convert.ToString(item.Key), Convert.ToString(money), Convert.ToString(userBlank[item.Key]), Convert.ToString(userMoney[item.Key])); // 删除用户避免出现重复的情况 userData.Remove(item.Key); break; } } i++; if (i > 29) { break; } } } catch (Exception) { throw; } finally { if (connection != null) { connection.Close(); } // 关闭数据库链接 ConnectionPool.getPool().closeConnection(connection); } return(resultMsg); }
/// <summary> /// 金额提现 /// </summary> /// <param name="qq">QQ号</param> /// <param name="money">金额</param> public static string updateBlank(string qq, decimal money, string operation) { string resultStr = ""; string sql = "UPDATE ichunqiu_blank SET user_money = user_money - @user_money WHERE user_qq =@qq "; // 加钱 if ("1".Equals(operation)) { sql = "UPDATE ichunqiu_blank SET user_money = user_money + @user_money WHERE user_qq =@qq "; } else { string tmpMoney = getBlankMoney(qq); if (tmpMoney != null && tmpMoney.Length > 0) { if (Convert.ToDecimal(tmpMoney) < money) { return("提现金额必须小于存款金额。"); } } else { return("没有该作家!"); } } var parameter = new List <MySqlParameter>(); parameter.Add(new MySqlParameter("@user_money", money)); parameter.Add(new MySqlParameter("@qq", qq)); MySqlConnection connection = null; try { connection = ConnectionPool.getPool().getConnection(); while (connection == null) { } using (MySqlCommand mySqlCommand = new MySqlCommand(sql, connection)) { // 添加参数 if (parameter.Count > 0) { mySqlCommand.Parameters.AddRange(parameter.ToArray()); } // 提取数据 int count = mySqlCommand.ExecuteNonQuery(); // 执行数据插入 int flagCount = inserMoneyHistory(qq, money, operation); if (count > 0 && flagCount > 0) { resultStr = "更新成功!"; } else { resultStr = "更新失败!"; } } } catch (Exception) { throw; } finally { if (connection != null) { connection.Close(); } // 关闭数据库链接 ConnectionPool.getPool().closeConnection(connection); } return(resultStr); }