/// <summary> /// Получить количество событий в логе /// </summary> public static int GetLogEventsCount(int user_id, int event_id) { int res = 0; string SQLCmd = "SELECT COUNT(*) AS CNT FROM LOG"; string WhereClause = (user_id > 0 ? string.Format(" WHERE USER_ID={0}", user_id) + (event_id > 0 ? string.Format(" AND EVENT_ID={0}", event_id) : "") : (event_id > 0 ? string.Format(" WHERE EVENT_ID={0}", event_id) : "")); using (var con = new SqlConnection(AppConst.ConnStr)) { using (var cmd = new SqlCommand(SQLCmd + WhereClause, con)) { con.Open(); res = AppUtils.ConvertToInteger(cmd.ExecuteScalar()); con.Close(); } } return(res); }
/// <summary> /// Количество медиатегов пользователя /// </summary> public static int GetMediaTagsCount(int user_id, string hashtag, AppEnums.SelectMediaAttr selattr = AppEnums.SelectMediaAttr.HideDeleted) { int res = 0; string SQLStr = "SELECT COUNT(*) FROM MEDIATAG WHERE USER_ID=@user_id AND TAG_CAPTION=@hashtag"; switch (selattr) { case AppEnums.SelectMediaAttr.HideBaned: SQLStr += " AND BAN=0"; break; case AppEnums.SelectMediaAttr.HideDeleted: SQLStr += " AND DELETED=0"; break; case AppEnums.SelectMediaAttr.HideBanedDeleted: SQLStr += " AND BAN=0 AND DELETED=0"; break; } using (var con = new SqlConnection(AppConst.ConnStr)) { using (var cmd = new SqlCommand(SQLStr, con)) { cmd.Parameters.AddWithValue("@user_id", user_id); cmd.Parameters.AddWithValue("@hashtag", hashtag); con.Open(); res = AppUtils.ConvertToInteger(cmd.ExecuteScalar()); con.Close(); } } return(res); }
/// <summary> /// Обновить информацию о пользователе /// </summary> public static SQLReturnResult UpdateUser(AppUser user) { int role_id = AppUtils.ConvertToInteger(user.USER_ROLE_ID, 0); int max_tag_count = AppUtils.ConvertToInteger(user.USER_MAX_TAG_COUNT, 0); SQLReturnResult res = new SQLReturnResult(); StringBuilder sql = new StringBuilder(); sql.AppendLine(AppConst.SQLBeginTran); sql.AppendLine("UPDATE APPUSER"); sql.AppendLine("SET USER_LASTNAME=@lname,"); sql.AppendLine(" USER_FIRSTNAME=@fname,"); sql.AppendLine(" USER_PATR=@patr,"); sql.AppendLine(" USER_EMAIL=@email,"); if (role_id > 0) { sql.AppendLine(" USER_ROLE_ID=@role_id,"); } if (max_tag_count > 0) { sql.AppendLine(" USER_MAX_TAG_COUNT=@max_tag_count,"); } sql.AppendLine(" USER_PHONE=@phone"); sql.AppendLine("WHERE LOWER(USER_LOGIN)=@user_login"); sql.AppendLine(AppConst.SQLCommitTran); using (var con = new SqlConnection(AppConst.ConnStr)) { using (var cmd = new SqlCommand(sql.ToString(), con)) { cmd.Parameters.AddWithValue("@user_login", user.USER_LOGIN.ToLower()); cmd.Parameters.AddWithValue("@lname", user.USER_LASTNAME); cmd.Parameters.AddWithValue("@fname", user.USER_FIRSTNAME); cmd.Parameters.AddWithValue("@patr", AppUtils.ConvertToString(user.USER_PATR)); cmd.Parameters.AddWithValue("@email", Convert.ToString(user.USER_EMAIL)); cmd.Parameters.AddWithValue("@phone", AppUtils.ConvertToString(user.USER_PHONE)); if (role_id > 0) { cmd.Parameters.AddWithValue("@role_id", role_id); } if (max_tag_count > 0) { cmd.Parameters.AddWithValue("@max_tag_count", max_tag_count); } con.Open(); try { SqlDataReader rdr = cmd.ExecuteReader(); rdr.Read(); res.Result = (AppEnums.SQLExecResult)Convert.ToInt32(rdr["RES"]); rdr.Close(); } catch (Exception ex) { res.Result = AppEnums.SQLExecResult.SyntaxError; res.Message = ex.Message; } con.Close(); } } return(res); }