public static int GetMediaTagsTotalPages(string user_login, string hashtag, AppEnums.SelectMediaAttr selattr = AppEnums.SelectMediaAttr.HideDeleted) { AppUser user = UserManager.GetUser(user_login); if (user == null) { return(0); } else { return(GetMediaTagsTotalPages(user.USER_ID, hashtag, selattr)); } }
/// <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 int GetMediaTagsTotalPages(int user_id, string hashtag, AppEnums.SelectMediaAttr selattr = AppEnums.SelectMediaAttr.HideDeleted) { int mTagsCnt = GetMediaTagsCount(user_id, hashtag, selattr); return(mTagsCnt / AppConst.ModPageSize + ((mTagsCnt % AppConst.ModPageSize > 0) ? 1 : 0)); }
public static List <MediaTag> GetDataBaseMediaTags(string user_login, string hashtag, int page, AppEnums.SelectMediaAttr selattr) { var res = new List <MediaTag>(); AppUser user = UserManager.GetUser(user_login); if (user != null) { string aSQL = ""; switch (selattr) { case AppEnums.SelectMediaAttr.HideBaned: aSQL = " AND BAN=0"; break; case AppEnums.SelectMediaAttr.HideDeleted: aSQL = " AND DELETED=0"; break; case AppEnums.SelectMediaAttr.HideBanedDeleted: aSQL = " AND BAN=0 AND DELETED=0"; break; } string SQLStr = string.Format("SELECT * FROM " + "(SELECT *, ROW_NUMBER() OVER (ORDER BY MEDIA_ID DESC) AS RowNum FROM MEDIATAG WHERE USER_ID=@user_id AND TAG_CAPTION=@hashtag{0}) AS tbl " + "WHERE tbl.RowNum BETWEEN {1} AND {2} " + "ORDER BY MEDIA_ID DESC", aSQL, AppConst.ModPageSize * (page - 1) + 1, AppConst.ModPageSize * page); using (var con = new SqlConnection(AppConst.ConnStr)) { using (var cmd = new SqlCommand(SQLStr, con))// + " ORDER BY MEDIA_ID" { cmd.Parameters.AddWithValue("@user_id", user.USER_ID); cmd.Parameters.AddWithValue("@hashtag", hashtag); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { res.Add(new MediaTag() { MEDIA_ID = Convert.ToInt32(rdr["MEDIA_ID"]), USER_ID = Convert.ToInt32(rdr["USER_ID"]), ORDER_ID = Convert.ToInt32(rdr["ORDER_ID"]), TAG_CAPTION = Convert.ToString(rdr["TAG_CAPTION"]), INSTAGRAM_MEDIA_ID = Convert.ToString(rdr["INSTAGRAM_MEDIA_ID"]), INSTAGRAM_MEDIA_CREATED_TIME = (rdr["INSTAGRAM_MEDIA_CREATED_TIME"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(rdr["INSTAGRAM_MEDIA_CREATED_TIME"])), INSTAGRAM_MEDIA_LOW_RES_URL = Convert.ToString(rdr["INSTAGRAM_MEDIA_LOW_RES_URL"]), INSTAGRAM_MEDIA_STANDARD_RES_URL = Convert.ToString(rdr["INSTAGRAM_MEDIA_STANDARD_RES_URL"]), INSTAGRAM_MEDIA_THUMBNAIL_URL = Convert.ToString(rdr["INSTAGRAM_MEDIA_THUMBNAIL_URL"]), INSTAGRAM_USER_ID = Convert.ToString(rdr["INSTAGRAM_USER_ID"]), INSTAGRAM_USER_NAME = Convert.ToString(rdr["INSTAGRAM_USER_NAME"]), INSTAGRAM_USER_PROFILEPICTURE = Convert.ToString(rdr["INSTAGRAM_USER_PROFILEPICTURE"]), INSTAGRAM_CAPTION = Convert.ToString(rdr["INSTAGRAM_CAPTION"]), BAN = Convert.ToBoolean(rdr["BAN"]), DELETED = Convert.ToBoolean(rdr["DELETED"]) }); } } rdr.Close(); con.Close(); } } } return(res); }
public static List <MediaTag> GetDataBaseMediaTags(string user_login, string hashtag, AppEnums.SelectMediaAttr selattr = AppEnums.SelectMediaAttr.HideBanedDeleted) { var user = UserManager.GetUser(user_login); return(GetDataBaseMediaTags(user.USER_ID, hashtag, selattr)); }
/// <summary> /// Получить медиа-тэги, сохраненные в БД /// </summary> /// <param name="user_id">ID пользователя</param> /// <param name="hashtag">Хэштег</param> /// <param name="selattr">ban, deleted selector</param> /// <returns></returns> public static List <MediaTag> GetDataBaseMediaTags(int user_id, string hashtag, AppEnums.SelectMediaAttr selattr) { var res = new List <MediaTag>(); //TODO DELETED игнорировать для сранения есть ли они в базе string SQLStr = "SELECT * 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))// + " ORDER BY MEDIA_ID" { cmd.Parameters.AddWithValue("@user_id", user_id); cmd.Parameters.AddWithValue("@hashtag", hashtag); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { res.Add(new MediaTag() { MEDIA_ID = Convert.ToInt32(rdr["MEDIA_ID"]), USER_ID = Convert.ToInt32(rdr["USER_ID"]), ORDER_ID = Convert.ToInt32(rdr["ORDER_ID"]), TAG_CAPTION = Convert.ToString(rdr["TAG_CAPTION"]), INSTAGRAM_MEDIA_ID = Convert.ToString(rdr["INSTAGRAM_MEDIA_ID"]), INSTAGRAM_MEDIA_CREATED_TIME = (rdr["INSTAGRAM_MEDIA_CREATED_TIME"] == DBNull.Value ? DateTime.Now : Convert.ToDateTime(rdr["INSTAGRAM_MEDIA_CREATED_TIME"])), INSTAGRAM_MEDIA_LOW_RES_URL = Convert.ToString(rdr["INSTAGRAM_MEDIA_LOW_RES_URL"]), INSTAGRAM_MEDIA_STANDARD_RES_URL = Convert.ToString(rdr["INSTAGRAM_MEDIA_STANDARD_RES_URL"]), INSTAGRAM_MEDIA_THUMBNAIL_URL = Convert.ToString(rdr["INSTAGRAM_MEDIA_THUMBNAIL_URL"]), INSTAGRAM_USER_ID = Convert.ToString(rdr["INSTAGRAM_USER_ID"]), INSTAGRAM_USER_NAME = Convert.ToString(rdr["INSTAGRAM_USER_NAME"]), INSTAGRAM_USER_PROFILEPICTURE = Convert.ToString(rdr["INSTAGRAM_USER_PROFILEPICTURE"]), INSTAGRAM_CAPTION = Convert.ToString(rdr["INSTAGRAM_CAPTION"]), BAN = Convert.ToBoolean(rdr["BAN"]), DELETED = Convert.ToBoolean(rdr["DELETED"]) }); } } rdr.Close(); con.Close(); } } return(res); }