/// <summary> /// Сохранить данные из Instagram в базу /// </summary> public static int SaveMediaTagsToDataBase(int user_id, string hashtag, AppEnums.UpdateHashTagType updtype = AppEnums.UpdateHashTagType.Recent30) { int res = 0; if (string.IsNullOrEmpty(hashtag)) { return(res); } List <MediaTag> SavedMediaTagResults = GetDataBaseMediaTags(user_id, hashtag, AppEnums.SelectMediaAttr.HideNothing); //использовать max_tag_id для добовления к основному запросу &max_tag_id= , чтобы уменьшить кол-во загружаемых элементов int min_tag_id = 0; if (updtype == AppEnums.UpdateHashTagType.FromLast30) { min_tag_id = (SavedMediaTagResults.Count == 0 ? 0 : SavedMediaTagResults.OrderBy(x => x.MEDIA_ID).Last().MEDIA_ID); } //instagram_media_id = next_max_id+'_'+instagram_user_id //TODO если у hashtag в табл HASHTAG есть непустое поле NEXT_MAX_ID(добавить)? т.е. уже сохр в базу(если 1-ый раз , то null), то в LoadMediaByHashTag доб-ть это поле и там формир-ть соотв запрос + &max_tag_id= List <Media> NewMediaTagResult = LoadMediaByHashTag(hashtag, min_tag_id); foreach (var mr in NewMediaTagResult) { var MediaExists = (SavedMediaTagResults.Count == 0 ? null : SavedMediaTagResults.FirstOrDefault(x => (x.INSTAGRAM_MEDIA_ID == mr.Id))); if (MediaExists == null) { try { var NewMediaTag = new MediaTag() { INSTAGRAM_MEDIA_ID = mr.Id, INSTAGRAM_MEDIA_CREATED_TIME = mr.CreatedTime, INSTAGRAM_MEDIA_LOW_RES_URL = mr.Images.LowResolution.Url, INSTAGRAM_MEDIA_STANDARD_RES_URL = mr.Images.StandardResolution.Url, INSTAGRAM_MEDIA_THUMBNAIL_URL = mr.Images.Thumbnail.Url, INSTAGRAM_USER_ID = mr.User.Id.ToString(), INSTAGRAM_USER_NAME = mr.User.Username, INSTAGRAM_USER_PROFILEPICTURE = mr.User.ProfilePicture, INSTAGRAM_CAPTION = AppUtils.ConvertToString(mr.Caption.Text), USER_ID = user_id, TAG_CAPTION = hashtag, ORDER_ID = 0, BAN = (InstagramMVC.DataManagers.UserManager.CanUserModerateShow(user_id)), DELETED = false }; InsertMediaTag(NewMediaTag); res += 1; } catch { } } } return(res); }
/// <summary> /// Получить указанную страницу событий в логе /// </summary> /// <param name="page">№ страницы в Pager</param> /// <param name="user_id">ID пользователя, инициировавшего события (0 - все пользователи)</param> /// <param name="event_id">ID события (0 - все события)</param> public static IList <Log> GetLogEvents(int page, int user_id, int event_id) { var res = new List <Log>(); int totalPages = GetLogEventTotalPages(user_id, event_id); if (page > totalPages) { page = totalPages; } using (var con = new SqlConnection(AppConst.ConnStr)) { using (var cmd = new SqlCommand(string.Format("SELECT * FROM " + "(SELECT *, ROW_NUMBER() OVER (ORDER BY LOG_ID DESC) AS RowNum FROM LOG " + (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) : "")) + ") AS tbl " + "WHERE tbl.RowNum BETWEEN {0} AND {1} " + "ORDER BY LOG_ID DESC", AppConst.LogPageSize * (page - 1) + 1, AppConst.LogPageSize * page), con)) // (user_id > 0 ? string.Format(" AND USER_ID={0}", user_id) : "") + //(event_id > 0 ? string.Format(" AND EVENT_ID={0}", event_id) : "") + { con.Open(); var rdr = cmd.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { res.Add(new Log() { LOG_ID = Convert.ToInt32(rdr["LOG_ID"]), EVENT_ID = Convert.ToInt32(rdr["EVENT_ID"]), LOG_TIME = Convert.ToDateTime(rdr["LOG_TIME"]), USER_ID = Convert.ToInt32(rdr["USER_ID"]), LOG_DESCRIPTION = AppUtils.ConvertToString(rdr["LOG_DESCRIPTION"]) }); } } rdr.Close(); con.Close(); } } return(res); }
/// <summary> /// Получить название события /// </summary> public static string GetEventName(int event_id) { var res = ""; using (var con = new SqlConnection(AppConst.ConnStr)) { using (var cmd = new SqlCommand("SELECT EVENT_NAME FROM EVENT WHERE EVENT_ID=@event_id", con)) { cmd.Parameters.AddWithValue("@event_id", event_id); con.Open(); res = AppUtils.ConvertToString(cmd.ExecuteScalar()); con.Close(); } } return(res); }
/// <summary> /// Роли пользователя /// </summary> public static string GetUserRole(int user_id) { string res = string.Empty; using (var con = new SqlConnection(AppConst.ConnStr)) { using (var cmd = new SqlCommand("SELECT ROLE.ROLE_NAME FROM APPUSER INNER JOIN ROLE ON APPUSER.USER_ROLE_ID = ROLE.ROLE_ID WHERE APPUSER.USER_ID=@user_id", con)) { cmd.Parameters.AddWithValue("@user_id", user_id); try { con.Open(); res = AppUtils.ConvertToString(cmd.ExecuteScalar()); con.Close(); } catch { } } } 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); }
/// <summary> /// Зарегистрировать нового пользователя /// </summary> public static AppUser RegisterNewUser(RegisterModel rm, int role_id = (int)AppEnums.Role.Guest) { var res = new AppUser(); if (IsUserLoginEMailExists(rm.Login, rm.EMail)) { res = null; } else { using (var con = new SqlConnection(AppConst.ConnStr)) { using (var cmd = new SqlCommand("RegisterNewUser", con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@user_login", SqlDbType.VarChar, 50).Value = rm.Login.ToLower().Trim(); cmd.Parameters.Add("@user_email", SqlDbType.VarChar, 50).Value = rm.EMail.ToLower().Trim(); cmd.Parameters.Add("@user_lastname", SqlDbType.VarChar, 50).Value = rm.LastName.Trim(); cmd.Parameters.Add("@user_firstname", SqlDbType.VarChar, 50).Value = rm.FirstName.Trim(); cmd.Parameters.Add("@user_patr", SqlDbType.VarChar, 50).Value = AppUtils.ConvertToString(rm.Patr).Trim(); cmd.Parameters.Add("@user_phone", SqlDbType.VarChar, 500).Value = AppUtils.ConvertToString(rm.Phone).Trim(); cmd.Parameters.Add("@user_password", SqlDbType.VarChar, 50).Value = rm.NewPassword; cmd.Parameters.Add("@role_id", SqlDbType.Int, 4).Value = role_id; SqlParameter par = new SqlParameter("@user_id", SqlDbType.Int, 4); par.Direction = ParameterDirection.Output; cmd.Parameters.Add(par); //try //{ con.Open(); cmd.ExecuteNonQuery(); con.Close(); int user_id = Convert.ToInt32(par.Value); res = UserManager.GetUser(user_id); //} //catch //{ // res = null; //} } } } return(res); }