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