public static SQLReturnResult DeleteShow(int show_id)
{
var res = new SQLReturnResult();
using (var con = new SqlConnection(AppConst.ConnStr))
{
using (var cmd = new SqlCommand("DELETE SHOW WHERE SHOW_ID=@show_id", con))
{
cmd.Parameters.AddWithValue("@show_id", show_id);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
res.Result = AppEnums.SQLExecResult.Success;
}
catch (Exception ex)
{
res.Result = AppEnums.SQLExecResult.SyntaxError;
res.Message = ex.Message;
}
}
}
return(res);
}
public ActionResult EditUser(AppUser user)
{
if (ModelState.IsValid)
{
SQLReturnResult res = UserManager.UpdateUser(user);
switch (res.Result)
{
case AppEnums.SQLExecResult.RollBack:
TempData["result"] = new OperationResult()
{
Status = AppEnums.OperationStatus.Error, Message = "Ошибка! Откат транзакции!\r\n" + res.Message
};
break;
case AppEnums.SQLExecResult.SyntaxError:
TempData["result"] = new OperationResult()
{
Status = AppEnums.OperationStatus.Error, Message = "Синтаксическая ошибка!\r\n" + res.Message
};
break;
default:
TempData["result"] = new OperationResult()
{
Status = AppEnums.OperationStatus.Success, Message = "Данные сохранены!"
};
UtilManager.RegisterEvent(UserManager.GetUser(System.Web.HttpContext.Current.User.Identity.Name).USER_ID, AppEnums.Event.Изменение_данных_пользователя);
break;
}
return(RedirectToAction("EditUsers", "Admin"));
}
return(View(user));
}
public static SQLReturnResult InsertHashTag(int user_id, string tag_caption)
{
SQLReturnResult res = new SQLReturnResult();
using (var con = new SqlConnection(AppConst.ConnStr))
{
using (var cmd = new SqlCommand("HashTagInsert", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@user_id", SqlDbType.Int, 4).Value = user_id;
cmd.Parameters.Add("@tag_caption", SqlDbType.VarChar, 100).Value = tag_caption;
con.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
res.Result = AppEnums.SQLExecResult.SyntaxError;
res.Message = ex.Message;
}
con.Close();
}
}
return(res);
}
/// <summary>
/// Удалить пользователя
/// </summary>
public ActionResult DeleteUser(int id)
{
var deletedUser = UserManager.GetUser(id);
SQLReturnResult res = UserManager.DeleteUser(deletedUser.USER_ID);
switch (res.Result)
{
case AppEnums.SQLExecResult.RollBack:
TempData["result"] = new OperationResult()
{
Status = AppEnums.OperationStatus.Error, Message = "Ошибка! Откат транзакции!\r\n" + res.Message
};
break;
case AppEnums.SQLExecResult.SyntaxError:
TempData["result"] = new OperationResult()
{
Status = AppEnums.OperationStatus.Error, Message = "Синтаксическая ошибка!\r\n" + res.Message
};
break;
default:
TempData["result"] = new OperationResult()
{
Status = AppEnums.OperationStatus.Success, Message = "Пользователь удален!"
};
UtilManager.RegisterEvent(UserManager.GetUser(System.Web.HttpContext.Current.User.Identity.Name).USER_ID,
AppEnums.Event.Удалить_пользователя,
string.Format("Пользователь '{0}' удален", deletedUser.USER_LOGIN));
break;
}
return(RedirectToAction("EditUsers", "Admin"));
}
public static SQLReturnResult DeleteTag(string tag_caption)
{
SQLReturnResult res = new SQLReturnResult();
using (var con = new SqlConnection(AppConst.ConnStr))
{
using (var cmd = new SqlCommand("HashTagDelete", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@tag_caption", tag_caption);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
res.Result = AppEnums.SQLExecResult.SyntaxError;
res.Message = ex.Message;
}
}
}
return(res);
}
public static SQLReturnResult DeleteMedia(int media_id)
{
SQLReturnResult res = new SQLReturnResult();
using (var con = new SqlConnection(AppConst.ConnStr))
{
using (var cmd = new SqlCommand("UPDATE MEDIATAG SET DELETED=1 WHERE MEDIA_ID=@media_id", con))
{
cmd.Parameters.AddWithValue("@media_id", media_id);
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
res.Result = AppEnums.SQLExecResult.SyntaxError;
res.Message = ex.Message;
}
}
}
return(res);
}
/// <summary>
/// Добавить медиатег в базу
/// </summary>
public static SQLReturnResult InsertMediaTag(MediaTag mediatag)
{
SQLReturnResult res = new SQLReturnResult();
using (var con = new SqlConnection(AppConst.ConnStr))
{
using (var cmd = new SqlCommand(@"INSERT MEDIATAG ([USER_ID], ORDER_ID, TAG_CAPTION, INSTAGRAM_MEDIA_ID, INSTAGRAM_MEDIA_CREATED_TIME,
INSTAGRAM_MEDIA_LOW_RES_URL, INSTAGRAM_MEDIA_STANDARD_RES_URL, INSTAGRAM_MEDIA_THUMBNAIL_URL, INSTAGRAM_USER_ID, INSTAGRAM_USER_NAME,
INSTAGRAM_USER_PROFILEPICTURE, INSTAGRAM_CAPTION, BAN, DELETED)
VALUES (@user_id, @order_id, @hashtag, @i_media_id, @i_media_created_time, @i_media_low_res_url, @i_media_standard_res_url, @i_media_thumnail_res_url,
@i_user_id, @i_user_name, @i_user_profilepicture, @i_caption, @ban, @deleted)", con))
{
cmd.Parameters.AddWithValue("@user_id", mediatag.USER_ID);
cmd.Parameters.AddWithValue("@order_id", mediatag.ORDER_ID);
cmd.Parameters.AddWithValue("@hashtag", mediatag.TAG_CAPTION);
cmd.Parameters.AddWithValue("@i_media_id", mediatag.INSTAGRAM_MEDIA_ID);
cmd.Parameters.AddWithValue("@i_media_created_time", mediatag.INSTAGRAM_MEDIA_CREATED_TIME);
cmd.Parameters.AddWithValue("@i_media_low_res_url", mediatag.INSTAGRAM_MEDIA_LOW_RES_URL);
cmd.Parameters.AddWithValue("@i_media_standard_res_url", mediatag.INSTAGRAM_MEDIA_STANDARD_RES_URL);
cmd.Parameters.AddWithValue("@i_media_thumnail_res_url", mediatag.INSTAGRAM_MEDIA_THUMBNAIL_URL);
cmd.Parameters.AddWithValue("@i_user_id", mediatag.INSTAGRAM_USER_ID);
cmd.Parameters.AddWithValue("@i_user_name", mediatag.INSTAGRAM_USER_NAME);
cmd.Parameters.AddWithValue("@i_user_profilepicture", mediatag.INSTAGRAM_USER_PROFILEPICTURE);
cmd.Parameters.AddWithValue("@i_caption", mediatag.INSTAGRAM_CAPTION);
cmd.Parameters.AddWithValue("@ban", mediatag.BAN);
cmd.Parameters.AddWithValue("@deleted", mediatag.DELETED);
con.Open();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
res.Result = AppEnums.SQLExecResult.SyntaxError;
res.Message = ex.Message;
}
con.Close();
}
}
return(res);
}
/// <summary>
/// Добавить пользовательский хэштег
/// </summary>
public ActionResult AddTag(string hashtag = "")
{
if (!string.IsNullOrEmpty(hashtag))
{
hashtag = hashtag.Trim().ToLower();
if (HashTagManager.IsTagExist(hashtag))
{
TempData["result"] = new OperationResult()
{
Status = AppEnums.OperationStatus.Warning,
Message = "Такой хэштег уже существует!"
};
return(RedirectToAction("Tags"));
}
SQLReturnResult res = HashTagManager.InsertHashTag(_account.USER_ID, hashtag);
switch (res.Result)
{
case AppEnums.SQLExecResult.RollBack:
TempData["result"] = new OperationResult()
{
Status = AppEnums.OperationStatus.Error, Message = "Ошибка! Откат транзакции!"
};
break;
case AppEnums.SQLExecResult.SyntaxError:
TempData["result"] = new OperationResult()
{
Status = AppEnums.OperationStatus.Error, Message = "Ошибка! Синтаксическая ошибка!"
};
break;
default:
TempData["result"] = new OperationResult()
{
Status = AppEnums.OperationStatus.Success, Message = "Новый тег добавлен!"
};
UtilManager.RegisterEvent(_account.USER_ID, AppEnums.Event.Добавить_хэштег, string.Format("Добавлен хэштег '{0}'", hashtag));
break;
}
}
return(RedirectToAction("Tags"));
}
public static SQLReturnResult SetVarValue(string VarName, string VarValue)
{
var res = new SQLReturnResult();
try
{
using (var con = new SqlConnection(AppConst.ConnStr))
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(AppConst.SQLBeginTran);
sql.AppendLine("UPDATE VARIABLE SET VAR_VALUE=@var_value WHERE LOWER(VAR_NAME)=@var_name");
sql.AppendLine(AppConst.SQLCommitTran);
using (var cmd = new SqlCommand(sql.ToString(), con))
{
cmd.Parameters.AddWithValue("@var_name", VarName.ToLower());
cmd.Parameters.AddWithValue("@var_value", VarValue);
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();
}
}
}
catch { }
return(res);
}
public static SQLReturnResult SaveShow(Show show)
{
var res = new SQLReturnResult();
using (var con = new SqlConnection(AppConst.ConnStr))
{
using (var cmd = new SqlCommand("ShowSave", con))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter("@show_id", SqlDbType.Int, 4);
par.Value = show.SHOW_ID;
par.Direction = ParameterDirection.InputOutput;
cmd.Parameters.Add(par);
cmd.Parameters.Add("@user_id", SqlDbType.Int, 4).Value = show.USER_ID;
cmd.Parameters.Add("@show_start", SqlDbType.DateTime, 8).Value = show.SHOW_START;
cmd.Parameters.Add("@show_end", SqlDbType.DateTime, 8).Value = show.SHOW_END;
cmd.Parameters.Add("@paid", SqlDbType.Bit, 1).Value = show.PAID;
cmd.Parameters.Add("@allowmod", SqlDbType.Bit, 1).Value = show.ALLOWMOD;
try
{
con.Open();
cmd.ExecuteNonQuery();
con.Close();
res.Result = AppEnums.SQLExecResult.Success;
}
catch (Exception ex)
{
res.Result = AppEnums.SQLExecResult.SyntaxError;
res.Message = ex.Message;
}
}
}
return(res);
}
public ActionResult Edit(Show show)
{
if (ModelState.IsValid)
{
//Если не Admin, то поле ALLOWMOD и PAID брать из базы
if (!IsAdmin)
{
Show sh = ShowManager.GetShow(show.SHOW_ID);
if (sh != null)
{
show.ALLOWMOD = sh.ALLOWMOD;
show.PAID = sh.PAID;
}
}
SQLReturnResult res = ShowManager.SaveShow(show);
switch (res.Result)
{
case AppEnums.SQLExecResult.Success:
UtilManager.RegisterEvent(UserManager.GetUser(System.Web.HttpContext.Current.User.Identity.Name).USER_ID,
AppEnums.Event.Сохранить_параметры_заказа,
string.Format("Сохранение параметров заказа № {0}", show.SHOW_ID));
//если заказ добавлял админ, то перенаправить в /Admin/UserShows, иначе /User/UserShows
if (IsAdmin)
{
AppUser user = UserManager.GetUser(show.USER_ID);
return(RedirectToAction("User", "Show", new { user_login = user.USER_LOGIN }));
}
TempData["result"] = "Операция прошла успешно!";
return(RedirectToAction("User", "Show"));
case AppEnums.SQLExecResult.SyntaxError:
TempData["result"] = "При сохранении данных произошла ошибка!" + res.Message;
break;
}
}
return(View(show));
}
public static SQLReturnResult ClearMediaTags(int user_id, string hashtag)
{
SQLReturnResult res = new SQLReturnResult();
StringBuilder sql = new StringBuilder();
sql.AppendLine(AppConst.SQLBeginTran);
sql.AppendLine("DELETE FROM MEDIATAG WHERE USER_ID=@user_id AND LOWER(TAG_CAPTION)=@hashtag");
sql.AppendLine(AppConst.SQLCommitTran);
using (var con = new SqlConnection(AppConst.ConnStr))
{
using (var cmd = new SqlCommand(sql.ToString(), con))
{
cmd.Parameters.AddWithValue("@user_id", user_id);
cmd.Parameters.AddWithValue("@hashtag", hashtag.ToLower());
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);
}
public ActionResult EditUser(AppUser User)
{
TempData["result"] = new OperationResult()
{
Status = AppEnums.OperationStatus.Error, Message = "Некорректные данные!"
};
if (ModelState.IsValid)
{
SQLReturnResult res = UserManager.UpdateUser(User);
switch (res.Result)
{
case AppEnums.SQLExecResult.RollBack:
TempData["result"] = new OperationResult()
{
Status = AppEnums.OperationStatus.Error, Message = "Ошибка! Откат транзакции!"
};
break;
case AppEnums.SQLExecResult.SyntaxError:
TempData["result"] = new OperationResult()
{
Status = AppEnums.OperationStatus.Error, Message = "Синтаксическая ошибка!"
};
break;
default:
TempData["result"] = new OperationResult()
{
Status = AppEnums.OperationStatus.Success, Message = "Данные успешно сохранены!"
};
UtilManager.RegisterEvent(_account.USER_ID, AppEnums.Event.Изменение_личных_регистрационных_данных);
break;
}
//return RedirectToAction("EditProfileSuccess");
}
return(View(User));
}
/// <summary>
/// Удалить пользователя
/// </summary>
public static SQLReturnResult DeleteUser(int user_id)
{
SQLReturnResult res = new SQLReturnResult();
StringBuilder sql = new StringBuilder();
sql.AppendLine(AppConst.SQLBeginTran);
sql.AppendLine("DELETE FROM APPUSER WHERE USER_ID=@user_id");
sql.AppendLine(AppConst.SQLCommitTran);
using (var con = new SqlConnection(AppConst.ConnStr))
{
using (var cmd = new SqlCommand(sql.ToString(), con))
{
cmd.Parameters.AddWithValue("@user_id", user_id);
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);
}
public ActionResult Delete(int show_id)
{
SQLReturnResult res = ShowManager.DeleteShow(show_id);
switch (res.Result)
{
case AppEnums.SQLExecResult.Success:
//если заказ добавлял админ, то перенаправить в /Admin/UserShows, иначе /User/UserShows
UtilManager.RegisterEvent(UserManager.GetUser(System.Web.HttpContext.Current.User.Identity.Name).USER_ID, AppEnums.Event.Удалить_заказ);
if (IsAdmin)
{
AppUser user = UserManager.GetUser(System.Web.HttpContext.Current.User.Identity.Name);
return(RedirectToAction("User", "Show", new { user_login = user.USER_LOGIN }));
}
TempData["result"] = "Заказ успешно удален!";
break;
case AppEnums.SQLExecResult.SyntaxError:
TempData["result"] = "При удалении заказа произошла ошибка!" + res.Message;
break;
}
return(RedirectToAction("User", "Show"));
}
public static SQLReturnResult SaveUserOptions(UserOptions opts)
{
SQLReturnResult res = new SQLReturnResult();
StringBuilder sql = new StringBuilder();
sql.AppendLine(AppConst.SQLBeginTran);
sql.AppendLine("UPDATE APPUSER");
sql.AppendLine("SET USER_SLIDE_ROTATION=@usr,");
if (!string.IsNullOrEmpty(opts.USER_BACKGROUND_IMG_URL))
{
sql.AppendLine(" USER_BACKGROUND_IMG_URL=@ubiu,");
}
if (!string.IsNullOrEmpty(opts.USER_LOGO_IMG_URL))
{
sql.AppendLine(" USER_LOGO_IMG_URL=@uliu,");
}
sql.AppendLine(" USER_SLIDE_BATCH_SIZE=@usbs");
sql.AppendLine("WHERE [USER_ID]=@user_id");
sql.AppendLine(AppConst.SQLCommitTran);
using (var con = new SqlConnection(AppConst.ConnStr))
{
using (var cmd = new SqlCommand(sql.ToString(), con))
{
cmd.Parameters.AddWithValue("@user_id", opts.USER_ID);
if (opts.USER_SLIDE_ROTATION != null)
{
cmd.Parameters.AddWithValue("@usr", opts.USER_SLIDE_ROTATION);
}
if (opts.USER_SLIDE_BATCH_SIZE != null)
{
cmd.Parameters.AddWithValue("@usbs", opts.USER_SLIDE_BATCH_SIZE);
}
if (!string.IsNullOrEmpty(opts.USER_BACKGROUND_IMG_URL))
{
cmd.Parameters.AddWithValue("@ubiu", opts.USER_BACKGROUND_IMG_URL);
}
if (!string.IsNullOrEmpty(opts.USER_LOGO_IMG_URL))
{
cmd.Parameters.AddWithValue("@uliu", opts.USER_LOGO_IMG_URL);
}
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);
}
/// <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);
}