/// <summary>
/// Formats a DateTime value into the correct forum date + Time display format
/// </summary>
/// <param name="date"></param>
/// <param name="seperator">string to place between the date and time</param>
/// <param name="showtime">flag to indicate if time is displayed</param>
/// <param name="authenticated">Is the user authenticated</param>
/// <param name="member">Member Object</param>
/// <returns></returns>
public static string ToForumDateDisplay(this DateTime date, string seperator, bool showtime, bool authenticated, MemberInfo member)
{
string dateFormat = Config.DateFormat;
string timeFormat = Config.TimeFormat;
double timediff = Config.TimeAdjust;
if (authenticated && member != null)
{
timediff = member.TimeOffset;
if (!String.IsNullOrEmpty(member.TimeZone) && member.UseDaylightSaving)
{
TimeZoneInfo timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById(member.TimeZone);
DateTime time = TimeZoneInfo.ConvertTimeFromUtc(date, timeZoneInfo);
var diff = time - date;
timediff = diff.TotalHours;
}
}
TimeSpan forumAdjust = timediff.DoubleToHours();
DateTime dtForum = date + forumAdjust;
if (showtime)
{
return Common.TranslateNumerals(dtForum.ToString(dateFormat + seperator + timeFormat));
}
return Common.TranslateNumerals(dtForum.ToString(dateFormat));
}
public AuthorInfo(MemberInfo member)
{
string title = member.Title;
this.Id = member.Id;
this.Username = member.Username;
this.AllowEmail = member.AllowEmail;
this.Avatar = member.Avatar;
this.Country = member.Country;
this.Email = member.Email;
this.HideEmail = member.HideEmail;
this.HomePage = member.HomePage;
this.MemberLevel = member.MemberLevel;
this.PostCount = member.PostCount;
this.Signature = member.Signature;
this.ReceiveEmails = member.ReceiveEmails;
this.Status = member.Status;
//this.Rank = new RankInfo(member.Username, ref title, member.PostCount);
this.Title = title;
this.Yahoo = member.Yahoo;
this.AIM = member.AIM;
this.Skype = member.Skype;
this.ICQ = member.ICQ;
//this.FaceBook = member.FaceBook;
//this.GooglePlus = member.GooglePlus;
}
///// <summary>
///// Converts a DateTime into a freindly TimeAgo tag
///// </summary>
///// <param name="date">Date to display</param>
///// <param name="authenticated">Is the user logged in</param>
///// <param name="timediff">Users time zone difference</param>
///// <returns></returns>
public static string TimeAgoTag(DateTime? date, bool authenticated, MemberInfo member, string tooltip)
{
if (date == DateTime.MinValue || date == null)
return "";
return string.Format("<abbr class='timeago' title='{0}' dir='inherit'>{1}</abbr>",
date.Value.ToISO8601Date(authenticated, member), tooltip);
}
public int Add(MemberInfo member)
{
List<SqlParameter> memberparms;
string strSql = "INSERT INTO " + Config.MemberTablePrefix + "MEMBERS " +
"(M_NAME,M_STATUS,M_LEVEL,M_TITLE,M_EMAIL,M_PASSWORD,M_VALID,M_KEY,M_FIRSTNAME,M_LASTNAME,M_OCCUPATION" +
",M_SEX,M_AGE,M_DOB,M_MARSTATUS,M_CITY,M_STATE,M_COUNTRY,M_HOMEPAGE,M_SIG" +
",M_HIDE_EMAIL,M_RECEIVE_EMAIL,M_VIEW_SIG,M_SIG_DEFAULT,M_HOBBIES,M_LNEWS" +
",M_QUOTE,M_BIO,M_LINK1,M_LINK2,M_AIM,M_YAHOO,M_ICQ,M_MSN,M_LAST_IP,M_LASTUPDATED" +
",M_LASTHEREDATE,M_AVATAR,M_THEME,M_TIMEOFFSET,M_DATE,M_POSTS,M_IP,M_DEFAULT_VIEW,M_USERNAME,M_PHOTO_URL,M_NEWEMAIL,M_PWKEY,M_SHA256,M_ALLOWEMAIL,M_DAYLIGHTSAVING,M_TIMEZONE)" +
" VALUES " +
"(@Username,@Status,@Mlev,@Title,@Email,@Password,@IsValid,@ValidationKey,@Firstname,@Lastname" +
",@Occupation,@Gender,@Age,@Dob,@Maritalstatus,@City,@State,@Country" +
",@Homepage,@Signature,@Hidemail,@Receivemails,@Viewsignatures,@Usesignature" +
",@Hobbies,@Latestnews,@Favquote,@Bio,@Link1,@Link2,@Aim,@Yahoo,@Icq,@Msn" +
",@LastIP,@Lastupdated,@LastVisit,@Avatar,@Theme,@Timeoffset,@Created,0,@LastIP,0,'','','','',1,0,@DaylightSaving,@TimeZone); " +
"SELECT SCOPE_IDENTITY();";
try
{
memberparms = new List<SqlParameter>
{
new SqlParameter("@Username", SqlDbType.NVarChar) {Value = member.Username},
new SqlParameter("@Status", SqlDbType.SmallInt) {Value = member.Status},
new SqlParameter("@Created", SqlDbType.VarChar) {Value = member.MemberSince.ToString("yyyyMMddHHmmss")},
new SqlParameter("@Mlev", SqlDbType.SmallInt) {Value = member.MemberLevel},
new SqlParameter("@Title", SqlDbType.NVarChar) {Value = member.Title.ConvertDBNull(),IsNullable = true},
new SqlParameter("@Password", SqlDbType.NVarChar) {Value = member.Password},
new SqlParameter("@IsValid", SqlDbType.Int) {Value = member.IsValid},
new SqlParameter("@ValidationKey", SqlDbType.NVarChar) {Value = member.ValidationKey.ConvertDBNull(),IsNullable = true},
new SqlParameter("@Email", SqlDbType.NVarChar) {Value = member.Email},
new SqlParameter("@Firstname", SqlDbType.NVarChar)
{
Value = member.Firstname.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Lastname", SqlDbType.NVarChar)
{
Value = member.Lastname.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Occupation", SqlDbType.NVarChar)
{
Value = member.Occupation.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Gender", SqlDbType.NVarChar)
{
Value = member.Gender.ConvertDBNull(),
IsNullable = true
}
};
memberparms.Add(new SqlParameter("@Age", SqlDbType.NVarChar) { Value = member.Age.ConvertDBNull(), IsNullable = true });
memberparms.Add(new SqlParameter("@Dob", SqlDbType.NVarChar)
{
Value = member.DateOfBirth.ConvertDBNull(),
IsNullable = true
});
memberparms.Add(new SqlParameter("@Maritalstatus", SqlDbType.NVarChar)
{
Value = member.MaritalStatus.ConvertDBNull(),
IsNullable = true
});
memberparms.Add(new SqlParameter("@City", SqlDbType.NVarChar) {Value = member.City.ConvertDBNull(), IsNullable = true});
memberparms.Add(new SqlParameter("@State", SqlDbType.NVarChar) {Value = member.State.ConvertDBNull(), IsNullable = true});
memberparms.Add(new SqlParameter("@Country", SqlDbType.NVarChar)
{
Value = member.Country.ConvertDBNull(),
IsNullable = true
});
memberparms.Add(new SqlParameter("@Homepage", SqlDbType.NVarChar)
{
Value = member.HomePage.ConvertDBNull(),
IsNullable = true
});
memberparms.Add(new SqlParameter("@Signature", SqlDbType.NVarChar)
{
Value = member.Signature.ConvertDBNull(),
IsNullable = true
});
memberparms.Add(new SqlParameter("@Hidemail", SqlDbType.Int) {Value = member.HideEmail});
memberparms.Add(new SqlParameter("@Receivemails", SqlDbType.Int) {Value = member.ReceiveEmails});
memberparms.Add(new SqlParameter("@Viewsignatures", SqlDbType.Int) {Value = member.ViewSignatures});
memberparms.Add(new SqlParameter("@Usesignature", SqlDbType.Int) {Value = member.UseSignature});
memberparms.Add(new SqlParameter("@Hobbies", SqlDbType.NVarChar)
{
Value = member.Hobbies.ConvertDBNull(),
IsNullable = true
});
memberparms.Add(new SqlParameter("@Latestnews", SqlDbType.NVarChar)
{
Value = member.LatestNews.ConvertDBNull(),
IsNullable = true
});
memberparms.Add(new SqlParameter("@Favquote", SqlDbType.NVarChar)
{
Value = member.FavouriteQuote.ConvertDBNull(),
IsNullable = true
});
memberparms.Add(new SqlParameter("@Bio", SqlDbType.NVarChar)
{
Value = member.Biography.ConvertDBNull(),
IsNullable = true
});
memberparms.Add(new SqlParameter("@Link1", SqlDbType.NVarChar)
{
Value = member.FavLink1.ConvertDBNull(),
IsNullable = true
});
memberparms.Add(new SqlParameter("@Link2", SqlDbType.NVarChar)
{
Value = member.FavLink2.ConvertDBNull(),
IsNullable = true
});
memberparms.Add(new SqlParameter("@Aim", SqlDbType.NVarChar) {Value = member.AIM.ConvertDBNull(), IsNullable = true});
memberparms.Add(new SqlParameter("@Yahoo", SqlDbType.NVarChar) {Value = member.Yahoo.ConvertDBNull(), IsNullable = true});
memberparms.Add(new SqlParameter("@Icq", SqlDbType.NVarChar) {Value = member.ICQ.ConvertDBNull(), IsNullable = true});
memberparms.Add(new SqlParameter("@Msn", SqlDbType.NVarChar) {Value = member.ICQ.ConvertDBNull(), IsNullable = true});
memberparms.Add(new SqlParameter("@LastIP", SqlDbType.NVarChar) {Value = Common.GetIP4Address()});
memberparms.Add(new SqlParameter("@Lastupdated", SqlDbType.NVarChar)
{
Value = DateTime.UtcNow.ToString("yyyyMMddHHmmss")
});
memberparms.Add(new SqlParameter("@Lastvisit", SqlDbType.NVarChar)
{
Value = DateTime.UtcNow.ToString("yyyyMMddHHmmss")
});
memberparms.Add(new SqlParameter("@Avatar", SqlDbType.NVarChar)
{
Value = member.Avatar.ConvertDBNull(),
IsNullable = true
});
memberparms.Add(new SqlParameter("@Theme", SqlDbType.NVarChar) {Value = member.Theme.ConvertDBNull(), IsNullable = true});
memberparms.Add(new SqlParameter("@Timeoffset", SqlDbType.Float) { Value = member.TimeOffset });
memberparms.Add(new SqlParameter("@DaylightSaving", SqlDbType.SmallInt) { Value = member.UseDaylightSaving });
memberparms.Add(new SqlParameter("@TimeZone", SqlDbType.NVarChar)
{
Value = member.TimeZone.ConvertDBNull(),
IsNullable = true
});
}
catch (Exception)
{
return 0;
throw;
}
int res = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.ConnString, CommandType.Text, strSql, memberparms.ToArray()));
return res;
}
public IEnumerable<KeyValuePair<int, string>> GetAllowedForumList(MemberInfo member, List<int> roleList, bool isadmin)
{
List<KeyValuePair<int, string>> forums = new List<KeyValuePair<int, string>>();
//return (from role in this.ForumRoles where role.Forum_id == forumid select role.Role_Id).ToList();
string SqlStr = "SELECT FORUM_ID,F_SUBJECT FROM " + Config.ForumTablePrefix + "FORUM ORDER BY F_SUBJECT";
//Execute a query to read the products
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnString, CommandType.Text, SqlStr, null))
{
while (rdr.Read())
{
forums.Add(new KeyValuePair<int, string>(rdr.GetInt32(0),rdr.GetString(1)));
}
}
List<KeyValuePair<int, string>> allowedForums = new List<KeyValuePair<int, string>>();
foreach (var forum in forums)
{
ForumInfo f = new ForumInfo
{
Id = forum.Key,
AllowedRoles = new List<int>(new Forum().AllowedRoles(forum.Key))
};
if (f.AllowedRoles.Count == 0)
allowedForums.Add(forum);
else
{
if (f.AllowedRoles.Any(role => roleList.Contains(role) || isadmin))
{
allowedForums.Add(forum);
}
}
}
return allowedForums;
}
public void Delete(MemberInfo member)
{
if (member.PostCount > 0)
{
member.Status = 0;
//set the password to a random string
member.Password = new Guid().ToString();
member.HideEmail = true;
member.Firstname = null;
member.Lastname = null;
member.DateOfBirth = null;
member.Biography = null;
member.Yahoo = null;
member.AIM = null;
member.Signature = null;
member.ICQ = null;
member.Skype = null;
member.LatestNews = null;
member.HomePage = null;
member.FavLink1 = null;
member.FavLink2 = null;
member.ReceiveEmails = false;
Update(member);
}
else
{
string strSql = "DELETE FROM " + Config.MemberTablePrefix + "MEMBERS WHERE MEMBER_ID=@MemberId";
SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, strSql, new SqlParameter("@MemberId", SqlDbType.Int) { Value = member.Id });
}
}
public static void Update(int topicid, string message, string subject, MemberInfo member, bool isAdministrator, bool usesig)
{
TopicInfo topic = GetTopic(topicid);
topic.Message = message;
topic.Subject = subject;
if (!isAdministrator)
{
topic.LastEditedById = member.Id;
topic.LastEditDate = DateTime.UtcNow;
}
topic.UseSignatures = usesig;
Update(topic);
}
public static void UpdateReply(int replyid, string message, MemberInfo author, bool isAdministrator, bool showsig)
{
ReplyInfo reply = GetReply(replyid);
reply.Message = message;
if (!isAdministrator)
{
reply.LastEditDate = DateTime.UtcNow;
reply.LastEditedById = author.Id;
}
reply.UseSignatures = showsig;
IReply dal = Factory<IReply>.Create("Reply");
dal.Update(reply);
}
public static IEnumerable<ForumInfo> GetCategoryForums(int categoryid, MemberInfo member)
{
IForum dal = Factory<IForum>.Create("Forum");
var allowedforums = Forums.AllowedForums(member);
if (member == null)
{
allowedforums = Forums.ViewableForums();
}
return dal.GetByParent(categoryid).Where(catforum => allowedforums.Contains(catforum.Id));
}
public static MemberInfo CopyMemberToBO(SqlDataReader rdr)
{
MemberInfo member = null;
try
{
member = new MemberInfo
{
Id = rdr.GetInt32(0),
Status = rdr.GetInt16(1),
Username = rdr.GetString(2),
NTUsername = rdr.SafeGetString(3),
Email = rdr.SafeGetString(4),
Country = rdr.SafeGetString(5),
HomePage = rdr.SafeGetString(6),
Signature = rdr.SafeGetString(7),
MemberLevel = rdr.GetInt16(8),
AIM = rdr.SafeGetString(9),
Yahoo = rdr.SafeGetString(10),
ICQ = rdr.SafeGetString(11),
MSN = rdr.SafeGetString(12),
PostCount = rdr.SafeGetInt32(13) ?? 0,
MemberSince = rdr.SafeGetString(14).ToDateTime().Value,
LastVisitDate = rdr.SafeGetString(15).ToDateTime(),
LastPostDate = rdr.SafeGetString(16).ToDateTime(),
Title = rdr.SafeGetString(17), //MemberTitle(rdr.SafeGetString(17)),
AllowSubscriptions = (rdr["M_SUBSCRIPTION"] as int?) == 1,
HideEmail = (rdr["M_HIDE_EMAIL"] as int?) == 1,
ReceiveEmails = (rdr["M_RECEIVE_EMAIL"] as int?) == 1,
MembersIP = rdr.SafeGetString(21),
ViewSignatures = (rdr["M_VIEW_SIG"] as int?) == 1,
UseSignature = (rdr["M_SIG_DEFAULT"] as int?) == 1,
Voted = (rdr["M_VOTED"] as int?) == 1,
AllowEmail = (rdr["M_ALLOWEMAIL"] as int?) == 1,
Avatar = rdr.SafeGetString(26),
Theme = rdr.SafeGetString(27),
TimeOffset = rdr.GetInt32(28),
DateOfBirth = rdr.SafeGetString(29)
};
}
catch(Exception ex)
{
SqlDataReader rdrtest = rdr;
string test = ex.Message;
}
string title = member.Title;
member.Rank = new RankInfo(member.Username,ref title,member.PostCount);
member.Title = title;
return member;
}
public void UpdateValidationKeys(MemberInfo member)
{
if (member.Username.ToLower() == "guest")
return;
StringBuilder updateSql = new StringBuilder();
updateSql.AppendFormat("UPDATE {0}MEMBERS SET ", Config.MemberTablePrefix).AppendLine();
updateSql.AppendLine("M_KEY=@ValidationKey");
updateSql.AppendLine(",M_PWKEY=@PasswordKey");
updateSql.AppendLine("WHERE MEMBER_ID=@MemberId");
List<SqlParameter> memberparms = new List<SqlParameter>
{
new SqlParameter("@ValidationKey", SqlDbType.NVarChar) {Value = member.ValidationKey},
new SqlParameter("@PasswordKey", SqlDbType.NVarChar) {Value = member.PasswordChangeKey},
new SqlParameter("@MemberId", SqlDbType.Int) {Value = member.Id}
};
SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, updateSql.ToString(), memberparms.ToArray());
}
public IEnumerable<TopicInfo> GetRecentTopics(int memberid, MemberInfo member)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("SELECT DISTINCT TOP (20) ");
sql.AppendLine("T.TOPIC_ID,T.CAT_ID,T.FORUM_ID,T.T_STATUS,T.T_SUBJECT,T.T_AUTHOR,T.T_REPLIES");
sql.AppendLine(",T.T_VIEW_COUNT,T.T_LAST_POST,T.T_DATE,T.T_IP,T.T_LAST_POST_AUTHOR,T.T_STICKY,T.T_LAST_EDIT,T.T_LAST_EDITBY");
sql.AppendLine(",T.T_SIG,T.T_LAST_POST_REPLY_ID,T.T_UREPLIES,T.T_MESSAGE,A.M_NAME AS Author, LPA.M_NAME AS LastPostAuthor, EM.M_NAME AS Editor ");
sql.AppendLine("FROM");
sql.AppendFormat("{0}TOPICS AS T LEFT OUTER JOIN",Config.ForumTablePrefix).AppendLine();
sql.AppendFormat("{0}REPLY AS R ON T.TOPIC_ID = R.TOPIC_ID LEFT OUTER JOIN", Config.ForumTablePrefix).AppendLine();
sql.AppendFormat("{0}MEMBERS LPA ON T.T_LAST_POST_AUTHOR = LPA.MEMBER_ID LEFT OUTER JOIN", Config.MemberTablePrefix).AppendLine();
sql.AppendFormat("{0}MEMBERS AS A ON T.T_AUTHOR = A.MEMBER_ID LEFT OUTER JOIN", Config.MemberTablePrefix).AppendLine();
sql.AppendFormat("{0}MEMBERS AS EM ON T.T_LAST_EDITBY = EM.MEMBER_ID", Config.MemberTablePrefix).AppendLine();
sql.AppendLine("WHERE");
sql.AppendLine("T.T_LAST_POST > @SinceDate AND");
sql.AppendLine("(T.T_AUTHOR = @UserId OR R.R_AUTHOR = @UserId) AND");
sql.AppendLine("(R.R_STATUS < 2 OR T.T_STATUS < 2)");
sql.AppendLine("ORDER BY T.T_LAST_POST DESC");
TimeSpan ts = new TimeSpan(30, 0, 0, 0);
DateTime startDate = DateTime.UtcNow - ts;
SqlParameter[] parms = new SqlParameter[2];
SqlParameter date = new SqlParameter("@SinceDate", SqlDbType.VarChar) { Value = startDate.ToString("yyyyMMddHHmmss") };
SqlParameter user = new SqlParameter("@UserId", SqlDbType.VarChar) { Value = memberid };
parms[0] = date;
parms[1] = user;
List<TopicInfo> topics = new List<TopicInfo>();
using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnString, CommandType.Text, sql.ToString(), parms))
{
while (rdr.Read())
{
topics.Add(BoHelper.CopyTopicToBO(rdr));
}
}
int[] allowed = member.AllowedForums;
return topics.Where(t => allowed.Contains(t.ForumId)).Take(10).ToList();
}
public static List<int> AllowedForums(MemberInfo member)
{
List<int> roleList = new List<int> { 0 };
bool isadmin = Roles.IsUserInRole("Administrator");
roleList.AddRange(SnitzCachedLists.UserRoles().Select(role => role.Key));
IMember dal = Factory<IMember>.Create("Member");
return new List<int>(dal.GetAllowedForumIds(roleList,isadmin));
}
/// <summary>
/// Converts a Member object into a MembershipUser object using its assigned settings
/// </summary>
private SnitzMembershipUser GetUserFromMember(MemberInfo m)
{
var smu = new SnitzMembershipUser(this.ProviderName,
m.Username,
m.Id,
m.Email,
m.ValidationKey,
"",
m.IsValid,
m.Status == 0,
m.MemberSince,
m.LastVisitDate == null ? DateTime.MinValue : m.LastVisitDate.Value,
m.LastVisitDate == null ? DateTime.MinValue : m.LastVisitDate.Value, //m.LastUpdateDate.Value,
DateTime.MinValue,
DateTime.MinValue,
m.LastPostDate,
m.Title,
m.Country,
m.PostCount);
return smu;
}
/*************************************************************************
* User related methods : create, update, unlock, delete methods.
*************************************************************************/
/// <summary>
/// Creates a new user with a given set of default values
/// </summary>
public override MembershipUser CreateUser(string username, string password, string email,
string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey,
out MembershipCreateStatus status)
{
var args = new ValidatePasswordEventArgs(username, password, true);
OnValidatingPassword(args);
if (args.Cancel)
{
status = MembershipCreateStatus.InvalidPassword;
return null;
}
if (RequiresUniqueEmail && GetUserNameByEmail(email) != "")
{
status = MembershipCreateStatus.DuplicateEmail;
return null;
}
// If no user with this name already exists
MembershipUser mu = GetUser(username, false);
if (mu == null || mu.UserName.ToLower() == "guest")
{
DateTime createdDate = DateTime.UtcNow;
//if (PasswordFormat == MembershipPasswordFormat.Hashed)
//{
// string salt = GenerateSalt();
// password = password + salt;
//}
var m = new MemberInfo {Username = username, Password = EncodePassword(password), Email = email};
// Set the password retrieval question and answer if they are required
if (RequiresQuestionAndAnswer)
{
m.PasswordChangeKey = passwordQuestion;
m.ValidationKey = EncodePassword(passwordAnswer);
}
m.IsValid = isApproved;
m.Status = 0;
m.ReceiveEmails =false;
m.TimeOffset = 0;
m.MemberSince = createdDate;
m.LastVisitDate = createdDate;
m.LastPostDate = null;
try
{
Snitz.IDAL.IMember dal = Snitz.IDAL.Factory<IDAL.IMember>.Create("Member");
dal.Add(m);
IRoles roleDal = Factory<IRoles>.Create("Role");
roleDal.AddUsersToRoles(new[] {m.Username},new[] {"all"});
// User creation was a success
status = MembershipCreateStatus.Success;
// Return the newly craeted user
return GetUserFromMember(m);
}
catch
{
// Something was wrong and the user was rejected
status = MembershipCreateStatus.UserRejected;
}
}
else
{
// There is already a user with this name
status = MembershipCreateStatus.DuplicateUserName;
}
// Something went wrong if we got this far without some sort of status or retun
if (status != MembershipCreateStatus.UserRejected && status != MembershipCreateStatus.DuplicateUserName)
status = MembershipCreateStatus.ProviderError;
return null;
}
public static List<TopicInfo> GetRecentTopics(int memberid, MemberInfo member)
{
IMember dal = Factory<IMember>.Create("Member");
return new List<TopicInfo>(dal.GetRecentTopics(memberid,member));
}
public static void SaveMember(MemberInfo member)
{
IMember dal = Factory<IMember>.Create("Member");
dal.Update(member);
}
public void Update(MemberInfo member)
{
if (member.Username.ToLower() == "guest")
return;
StringBuilder updateSql = new StringBuilder();
updateSql.AppendFormat("UPDATE {0}MEMBERS SET ",Config.MemberTablePrefix).AppendLine();
updateSql.AppendLine("M_TITLE=@Title");
updateSql.AppendLine(",M_EMAIL=@Email");
updateSql.AppendLine(",M_PASSWORD=@Password");
updateSql.AppendLine(",M_VALID=@IsValid");
updateSql.AppendLine(",M_KEY=@ValidationKey");
updateSql.AppendLine(",M_PWKEY=@PasswordKey");
updateSql.AppendLine(",M_FIRSTNAME=@Firstname");
updateSql.AppendLine(",M_LASTNAME=@Lastname");
updateSql.AppendLine(",M_OCCUPATION=@Occupation");
updateSql.AppendLine(",M_SEX=@Gender");
updateSql.AppendLine(",M_AGE=@Age");
updateSql.AppendLine(",M_DOB=@Dob");
updateSql.AppendLine(",M_MARSTATUS=@Maritalstatus");
updateSql.AppendLine(",M_CITY=@City");
updateSql.AppendLine(",M_STATE=@State");
updateSql.AppendLine(",M_COUNTRY= @Country");
updateSql.AppendLine(",M_HOMEPAGE=@Homepage");
updateSql.AppendLine(",M_SIG=@Signature");
updateSql.AppendLine(",M_HIDE_EMAIL=@Hidemail");
updateSql.AppendLine(",M_RECEIVE_EMAIL=@Receivemails");
updateSql.AppendLine(",M_VIEW_SIG=@Viewsignatures");
updateSql.AppendLine(",M_SIG_DEFAULT=@Usesignature");
updateSql.AppendLine(",M_HOBBIES=@Hobbies");
updateSql.AppendLine(",M_LNEWS=@Latestnews");
updateSql.AppendLine(",M_QUOTE=@Favquote");
updateSql.AppendLine(",M_BIO=@Bio");
updateSql.AppendLine(",M_LINK1=@Link1");
updateSql.AppendLine(",M_LINK2=@Link2");
updateSql.AppendLine(",M_AIM=@Aim,M_YAHOO=@Yahoo,M_ICQ=@Icq,M_MSN=@Msn");
updateSql.AppendLine(",M_LAST_IP=M_IP");
updateSql.AppendLine(",M_IP=@LastIP");
updateSql.AppendLine(",M_LASTUPDATED=@Lastupdated,M_LASTHEREDATE=@LastVisit");
updateSql.AppendLine(",M_AVATAR=@Avatar");
updateSql.AppendLine(",M_THEME=@Theme");
updateSql.AppendLine(",M_TIMEOFFSET=@Timeoffset");
updateSql.AppendLine(",M_DAYLIGHTSAVING=@DaylightSaving");
updateSql.AppendLine(",M_TIMEZONE=@TimeZone");
updateSql.AppendLine(",M_STATUS=@Status");
updateSql.AppendLine("WHERE MEMBER_ID=@MemberId");
List<SqlParameter> memberparms = new List<SqlParameter>
{
new SqlParameter("@Title", SqlDbType.NVarChar) {Value = member.Title.ConvertDBNull(),IsNullable = true},
new SqlParameter("@Password", SqlDbType.NVarChar) {Value = member.Password},
new SqlParameter("@IsValid", SqlDbType.Int) {Value = member.IsValid},
new SqlParameter("@ValidationKey", SqlDbType.NVarChar) {Value = member.ValidationKey.ConvertDBNull(),IsNullable = true},
new SqlParameter("@PasswordKey", SqlDbType.NVarChar) {Value = member.PasswordChangeKey.ConvertDBNull(),IsNullable = true},
new SqlParameter("@Email", SqlDbType.NVarChar) {Value = member.Email},
new SqlParameter("@Firstname", SqlDbType.NVarChar)
{
Value = member.Firstname.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Lastname", SqlDbType.NVarChar)
{
Value = member.Lastname.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Occupation", SqlDbType.NVarChar)
{
Value = member.Occupation.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Gender", SqlDbType.NVarChar)
{
Value = member.Gender.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Age", SqlDbType.VarChar) {Value = member.Age},
new SqlParameter("@Dob", SqlDbType.NVarChar)
{
Value = member.DateOfBirth.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Maritalstatus", SqlDbType.NVarChar)
{
Value = member.MaritalStatus.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@City", SqlDbType.NVarChar) {Value = member.City.ConvertDBNull(), IsNullable = true},
new SqlParameter("@State", SqlDbType.NVarChar) {Value = member.State.ConvertDBNull(), IsNullable = true},
new SqlParameter("@Country", SqlDbType.NVarChar)
{
Value = member.Country.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Homepage", SqlDbType.NVarChar)
{
Value = member.HomePage.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Signature", SqlDbType.NVarChar)
{
Value = member.Signature.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Hidemail", SqlDbType.Int) {Value = member.HideEmail},
new SqlParameter("@Receivemails", SqlDbType.Int) {Value = member.ReceiveEmails},
new SqlParameter("@Viewsignatures", SqlDbType.Int) {Value = member.ViewSignatures},
new SqlParameter("@Usesignature", SqlDbType.Int) {Value = member.UseSignature},
new SqlParameter("@Hobbies", SqlDbType.NVarChar)
{
Value = member.Hobbies.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Latestnews", SqlDbType.NVarChar)
{
Value = member.LatestNews.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Favquote", SqlDbType.NVarChar)
{
Value = member.FavouriteQuote.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Bio", SqlDbType.NVarChar)
{
Value = member.Biography.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Link1", SqlDbType.NVarChar)
{
Value = member.FavLink1.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Link2", SqlDbType.NVarChar)
{
Value = member.FavLink2.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Aim", SqlDbType.NVarChar) {Value = member.AIM.ConvertDBNull(), IsNullable = true},
new SqlParameter("@Yahoo", SqlDbType.NVarChar) {Value = member.Yahoo.ConvertDBNull(), IsNullable = true},
new SqlParameter("@Icq", SqlDbType.NVarChar) {Value = member.ICQ.ConvertDBNull(), IsNullable = true},
new SqlParameter("@Msn", SqlDbType.NVarChar) {Value = member.ICQ.ConvertDBNull(), IsNullable = true},
new SqlParameter("@LastIP", SqlDbType.NVarChar) {Value = Common.GetIP4Address()},
new SqlParameter("@Lastupdated", SqlDbType.NVarChar)
{
Value = DateTime.UtcNow.ToString("yyyyMMddHHmmss")
},
new SqlParameter("@Lastvisit", SqlDbType.NVarChar)
{
Value = DateTime.UtcNow.ToString("yyyyMMddHHmmss")
},
new SqlParameter("@Avatar", SqlDbType.NVarChar)
{
Value = member.Avatar.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Theme", SqlDbType.NVarChar) {Value = member.Theme.ConvertDBNull(), IsNullable = true},
new SqlParameter("@Timeoffset", SqlDbType.Float) {Value = member.TimeOffset},
new SqlParameter("@DaylightSaving", SqlDbType.SmallInt) {Value = member.UseDaylightSaving},
new SqlParameter("@TimeZone", SqlDbType.NVarChar)
{
Value = member.TimeZone.ConvertDBNull(),
IsNullable = true
},
new SqlParameter("@Status", SqlDbType.Int) {Value = member.Status},
new SqlParameter("@MemberId", SqlDbType.Int) {Value = member.Id}
};
SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, updateSql.ToString(), memberparms.ToArray());
}
/// <summary>
/// Converts a forum date string to an ISO 8601 format for Xml
/// </summary>
/// <param name="date"></param>
/// <param name="authenticated"> </param>
/// <param name="member">Member object</param>
/// <returns></returns>
public static string ToISO8601Date(this DateTime date, bool authenticated, MemberInfo member)
{
double timeDiff = Config.TimeAdjust;
if (authenticated && member != null)
{
timeDiff = member.TimeOffset;
if (!String.IsNullOrEmpty(member.TimeZone) && member.UseDaylightSaving)
{
TimeZoneInfo timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById(member.TimeZone);
DateTime time = TimeZoneInfo.ConvertTimeFromUtc(date, timeZoneInfo);
var diff = time - date;
timeDiff = diff.TotalHours;
}
}
TimeSpan forumAdjust = timeDiff.DoubleToHours();
string plusminus = "+";
if (timeDiff < 0)
plusminus = "-";
DateTime dtForum = date + forumAdjust;
string datestr = String.Format("{0}-{1:00}-{2:00}T{3:00}:{4:00}:{5:00}", dtForum.Year, dtForum.Month, dtForum.Day, dtForum.Hour, dtForum.Minute, dtForum.Second);
datestr = datestr + plusminus + Math.Abs(forumAdjust.Hours).ToString().PadLeft(2, '0') + ":" + forumAdjust.Minutes.ToString().PadLeft(2, '0');
return datestr;
}
public void UpdateVisit(MemberInfo member)
{
List<SqlParameter> parms = new List<SqlParameter>();
string updateMemberSql = "UPDATE " + Config.MemberTablePrefix + "MEMBERS SET M_LASTHEREDATE=@LastVisit,M_LAST_IP=M_IP,M_IP=@LastIP WHERE MEMBER_ID=@MemberId ";
SqlParameter memberid = new SqlParameter("@MemberId", SqlDbType.Int){Value = member.Id};
SqlParameter memberip = new SqlParameter("@LastIP", SqlDbType.NVarChar) {Value = Common.GetIP4Address()};
SqlParameter lastvisit = new SqlParameter("@LastVisit", SqlDbType.VarChar){Value = DateTime.UtcNow.ToForumDateStr()};
parms.Add(lastvisit);
parms.Add(memberid);
parms.Add(memberip);
SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, updateMemberSql, parms.ToArray());
}
public static MemberInfo CopyMemberToBO(SqlDataReader rdr)
{
MemberInfo member = null;
try
{
member = new MemberInfo
{
Id = rdr.GetInt32(0),
Status = rdr.SafeGetInt16(1),
Username = rdr.SafeGetString(2),
NTUsername = rdr.SafeGetString(3),
Email = rdr.SafeGetString(4),
Country = rdr.SafeGetString(5),
HomePage = rdr.SafeGetString(6),
Signature = rdr.SafeGetString(7),
MemberLevel = rdr.SafeGetInt16(8),
AIM = rdr.SafeGetString(9),
Yahoo = rdr.SafeGetString(10),
ICQ = rdr.SafeGetString(11),
Skype = rdr.SafeGetString(12),
PostCount = rdr.SafeGetInt32(13) ?? 0,
MemberSince = rdr.GetSnitzDate(14).Value,
LastVisitDate = rdr.GetSnitzDate(15),
LastPostDate = rdr.GetSnitzDate(16),
Title = rdr.SafeGetString(17), //MemberTitle(rdr.SafeGetString(17)),
AllowSubscriptions = rdr.SafeGetInt16(18) == 1,
HideEmail = rdr.SafeGetInt16(19) == 1,
ReceiveEmails = rdr.SafeGetInt16(20) == 1,
MembersIP = rdr.SafeGetString(21),
ViewSignatures = rdr.SafeGetInt16(22) == 1,
UseSignature = rdr.SafeGetInt16(23) == 1,
Voted = rdr.SafeGetInt16(24) == 1,
AllowEmail = rdr.SafeGetInt16(25) == 1,
Avatar = rdr.SafeGetString(26),
Theme = rdr.SafeGetString(27),
TimeOffset = rdr.SafeGetDouble(28),
DateOfBirth = rdr.SafeGetString(29),
Age = rdr.SafeGetString(30),
Password = rdr.GetString(31),
ValidationKey = rdr.SafeGetString(32),
IsValid = rdr.SafeGetInt16(33) == 1,
LastUpdateDate = rdr.GetSnitzDate(34),
MaritalStatus = rdr.SafeGetString(35),
Firstname = rdr.SafeGetString(36),
Lastname = rdr.SafeGetString(37),
Occupation = rdr.SafeGetString(38),
Gender = rdr.SafeGetString(39),
Hobbies = rdr.SafeGetString(40),
LatestNews = rdr.SafeGetString(41),
FavouriteQuote = rdr.SafeGetString(42),
Biography = rdr.SafeGetString(43),
FavLink1 = rdr.SafeGetString(44),
FavLink2 = rdr.SafeGetString(45),
City = rdr.SafeGetString(46),
State = rdr.SafeGetString(47),
UseDaylightSaving = rdr.SafeGetInt16(48) == 1,
TimeZone = rdr.SafeGetString(49)
};
}
catch(Exception ex)
{
//SqlDataReader rdrtest = rdr;
//string test = ex.Message;
}
string title = member.Title;
//member.Rank = new RankInfo(member.Username,ref title,member.PostCount);
member.Title = title;
return member;
}
public void DeleteProfile(MemberInfo member)
{
string strSql = "DELETE FROM " + Config.MemberTablePrefix + "ProfileData WHERE UserId=@Userid";
SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, strSql, new SqlParameter("@Userid",SqlDbType.Int){Value = member.Id});
}
public static MemberInfo CopyMemberToBO(OleDbDataReader rdr)
{
MemberInfo member = null;
try
{
member = new MemberInfo();
member.Id = rdr.GetInt32(0);
member.Status = rdr.GetInt16(1);
member.Username = rdr.GetString(2);
member.NTUsername = rdr.SafeGetString(3);
member.Email = rdr.SafeGetString(4);
member.Country = rdr.SafeGetString(5);
member.HomePage = rdr.SafeGetString(6);
member.Signature = rdr.SafeGetString(7);
member.MemberLevel = rdr.GetInt16(8);
member.AIM = rdr.SafeGetString(9);
member.Yahoo = rdr.SafeGetString(10);
member.ICQ = rdr.SafeGetString(11);
member.Skype = rdr.SafeGetString(12);
member.PostCount = rdr.SafeGetInt32(13) ?? 0;
member.MemberSince = rdr.GetSnitzDate(14) == null ? DateTime.UtcNow : rdr.GetSnitzDate(14).Value;
member.LastVisitDate = rdr.GetSnitzDate(15);
member.LastPostDate = rdr.GetSnitzDate(16);
member.Title = rdr.SafeGetString(17); //member.MemberTitle(rdr.SafeGetString(17));
member.AllowSubscriptions = rdr.SafeGetInt16(18) == 1;
member.HideEmail = rdr.SafeGetInt16(19) == 1;
member.ReceiveEmails = rdr.SafeGetInt16(20) == 1;
member.MembersIP = rdr.SafeGetString(21);
member.ViewSignatures = rdr.SafeGetInt16(22) == 1;
member.UseSignature = rdr.SafeGetInt16(23) == 1;
member.Voted = rdr.SafeGetInt32(24) == 1;
member.AllowEmail = rdr.SafeGetInt16(25) == 1;
member.Avatar = rdr.SafeGetString(26);
member.Theme = rdr.SafeGetString(27);
member.TimeOffset = rdr.SafeGetDouble(28);
member.DateOfBirth = rdr.SafeGetString(29);
member.Age = rdr.SafeGetString(30);
member.Password = rdr.GetString(31);
member.ValidationKey = rdr.SafeGetString(32);
member.IsValid = rdr.SafeGetInt32(33) == 1;
member.LastUpdateDate = rdr.GetSnitzDate(34);
member.MaritalStatus = rdr.SafeGetString(35);
member.Firstname = rdr.SafeGetString(36);
member.Lastname = rdr.SafeGetString(37);
member.Occupation = rdr.SafeGetString(38);
member.Gender = rdr.SafeGetString(39);
member.Hobbies = rdr.SafeGetString(40);
member.LatestNews = rdr.SafeGetString(41);
member.FavouriteQuote = rdr.SafeGetString(42);
member.Biography = rdr.SafeGetString(43);
member.FavLink1 = rdr.SafeGetString(44);
member.FavLink2 = rdr.SafeGetString(45);
member.City = rdr.SafeGetString(46);
member.State = rdr.SafeGetString(47);
member.UseDaylightSaving = rdr.SafeGetInt16(48) == 1;
member.TimeZone = rdr.SafeGetString(49);
}
catch (Exception ex)
{
throw new Exception("Error copying reader to BO");
OleDbDataReader rdrtest = rdr;
string test = ex.Message;
}
string title = member.Title;
//member.Rank = new RankInfo(member.Username, ref title, member.PostCount);
member.Title = title;
return member;
}
