/// <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);
}
