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