public static bool FriendshipExists(SqlDatabaseClient MySqlClient, uint UserId1, uint UserId2, bool ConfirmedOnly)
{
MySqlClient.SetParameter("user1", UserId1);
MySqlClient.SetParameter("user2", UserId2);
MySqlClient.SetParameter("confirmed", (ConfirmedOnly ? 0 : 2));
return (MySqlClient.ExecuteQueryRow("SELECT null FROM messenger_friendships WHERE user_1_id = @user1 AND user_2_id = @user2 AND confirmed != @confirmed OR user_2_id = @user1 AND user_1_id = @user2 AND confirmed != @confirmed LIMIT 1") != null);
}
public static void CreateFriendship(SqlDatabaseClient MySqlClient, uint UserId1, uint UserId2, bool Confirmed)
{
for (int i = 0; i < (Confirmed ? 2 : 1); i++)
{
MySqlClient.SetParameter("user1", (i == 1 ? UserId1 : UserId2));
MySqlClient.SetParameter("user2", (i == 1 ? UserId2 : UserId1));
MySqlClient.SetParameter("accepted", (int)(Confirmed ? 1 : 0));
MySqlClient.ExecuteNonQuery("INSERT INTO messenger_friendships (user_1_id,user_2_id,confirmed) VALUES (@user1,@user2,@accepted)");
}
}
public static void DeleteRoom(SqlDatabaseClient MySqlClient, uint RoomId)
{
MySqlClient.SetParameter("id", RoomId);
MySqlClient.ExecuteNonQuery("DELETE FROM rooms WHERE id = @id LIMIT 1");
MySqlClient.SetParameter("id", RoomId);
MySqlClient.ExecuteNonQuery("DELETE FROM navigator_frontpage WHERE room_id = @id");
RoomInfoLoader.RemoveFromCache(RoomId);
Navigator.ReloadOfficialItems(MySqlClient);
}
public static bool DestroyFriendship(SqlDatabaseClient MySqlClient, uint UserId1, uint UserId2)
{
int aff = 0;
for (int i = 0; i < 2; i++)
{
MySqlClient.SetParameter("user1", (i == 1 ? UserId1 : UserId2));
MySqlClient.SetParameter("user2", (i == 1 ? UserId2 : UserId1));
aff += MySqlClient.ExecuteNonQuery("DELETE FROM messenger_friendships WHERE user_1_id = @user1 AND user_2_id = @user2 LIMIT 1");
}
return (aff > 0);
}
public static void BanUser(SqlDatabaseClient MySqlClient, uint UserId, string MessageText, uint ModeratorId, double Length)
{
MySqlClient.SetParameter("userid", UserId);
MySqlClient.SetParameter("reason", MessageText);
MySqlClient.SetParameter("timestamp", UnixTimestamp.GetCurrent());
MySqlClient.SetParameter("timestampex", UnixTimestamp.GetCurrent() + Length);
MySqlClient.SetParameter("moderator", ModeratorId);
MySqlClient.ExecuteNonQuery("INSERT INTO bans (user_id,reason_text,timestamp_created,timestamp_expire,moderator_id) VALUES (@userid,@reason,@timestamp,@timestampex,@moderator)");
lock (mSyncRoot)
{
mCharacterBlacklist.Add(UserId);
}
}
public static List<uint> GetFriendsForUser(SqlDatabaseClient MySqlClient, uint UserId, int Confirmed)
{
List<uint> Friends = new List<uint>();
MySqlClient.SetParameter("id", UserId);
MySqlClient.SetParameter("confirmed", Confirmed);
DataTable Table = MySqlClient.ExecuteQueryTable("SELECT user_2_id FROM messenger_friendships WHERE user_1_id = @id AND confirmed = @confirmed");
foreach (DataRow Row in Table.Rows)
{
Friends.Add((uint)Row[0]);
}
return Friends;
}
public static CharacterInfo GetCharacterInfo(SqlDatabaseClient MySqlClient, uint CharacterId, uint LinkedClientId, bool IgnoreCache)
{
if (SessionManager.ContainsCharacterId(CharacterId))
{
Session Session = SessionManager.GetSessionByCharacterId(CharacterId);
return Session.CharacterInfo;
}
if (!IgnoreCache)
{
CharacterInfo CachedInfo = TryGetInfoFromCache(CharacterId);
if (CachedInfo != null)
{
return CachedInfo;
}
}
MySqlClient.SetParameter("id", CharacterId);
DataRow Row = MySqlClient.ExecuteQueryRow("SELECT * FROM characters WHERE id = @id LIMIT 1");
if (Row != null)
{
return GenerateCharacterInfoFromRow(MySqlClient, LinkedClientId, Row);
}
return null;
}
public static AvatarEffect CreateEffect(SqlDatabaseClient MySqlClient, uint UserId, int SpriteId, double Duration)
{
MySqlClient.SetParameter("userid", UserId);
MySqlClient.SetParameter("spriteid", SpriteId);
MySqlClient.SetParameter("duration", Duration);
string RawId = MySqlClient.ExecuteScalar("INSERT INTO avatar_effects (user_id,sprite_id,duration) VALUES (@userid,@spriteid,@duration); SELECT LAST_INSERT_ID();").ToString();
uint Id = 0;
uint.TryParse(RawId, out Id);
if (Id == 0)
{
return null;
}
return new AvatarEffect(Id, SpriteId, Duration, 1, false, 0.0);
}
public void MarkNewItem(SqlDatabaseClient MySqlClient, int TabId, uint ItemId, bool SynchronizeDatabase = true)
{
lock (mSyncRoot)
{
if (!mInner.ContainsKey(TabId))
{
mInner[TabId] = new List<uint>();
}
mInner[TabId].Add(ItemId);
if (SynchronizeDatabase)
{
MySqlClient.SetParameter("userid", mUserId);
MySqlClient.SetParameter("tabid", TabId);
MySqlClient.SetParameter("itemid", ItemId);
MySqlClient.ExecuteNonQuery("INSERT INTO new_items (user_id,tab_id,item_id) VALUES (@userid,@tabid,@itemid)");
}
}
}
public static Pet CreatePet(SqlDatabaseClient MySqlClient, uint UserId, int Type, string Name, int Race)
{
MySqlClient.SetParameter("userid", UserId);
MySqlClient.SetParameter("type", Type);
MySqlClient.SetParameter("name", Name);
MySqlClient.SetParameter("race", Race);
MySqlClient.SetParameter("timestamp", UnixTimestamp.GetCurrent());
string RawId = MySqlClient.ExecuteScalar("INSERT INTO pets (user_id,type,name,race,timestamp) VALUES (@userid,@type,@name,@race,@timestamp); SELECT LAST_INSERT_ID();").ToString();
uint Id = 0;
uint.TryParse(RawId, out Id);
if (Id == 0)
{
return null;
}
return new Pet(Id, Name, Type, Race, UserId, 0, new Vector3(0, 0, 0), UnixTimestamp.GetCurrent(), 0, 120, 100, 0);
}
public void AddOrUpdateData(SqlDatabaseClient MySqlClient, string Group, int Level, int Progress)
{
MySqlClient.SetParameter("userid", mUserId);
MySqlClient.SetParameter("group", Group);
MySqlClient.SetParameter("level", Level);
MySqlClient.SetParameter("progress", Progress);
lock (mSyncRoot)
{
if (mInner.ContainsKey(Group))
{
mInner[Group].Level = Level;
mInner[Group].Progress = Progress;
MySqlClient.ExecuteNonQuery("UPDATE user_achievements SET level = @level, progress = @progress WHERE user_id = @userid AND group_id = @group LIMIT 1");
}
else
{
mInner.Add(Group, new UserAchievement(Group, Level, Progress));
MySqlClient.ExecuteNonQuery("INSERT INTO user_achievements (user_id,group_id,level,progress) VALUES (@userid,@group,@level,@progress)");
}
}
}
public void ReloadCache(SqlDatabaseClient MySqlClient)
{
lock (mSyncRoot)
{
mInner.Clear();
MySqlClient.SetParameter("userid", mUserId);
DataTable Table = MySqlClient.ExecuteQueryTable("SELECT group_id,level,progress FROM user_achievements WHERE user_id = @userid");
foreach (DataRow Row in Table.Rows)
{
string Group = (string)Row["group_id"];
mInner.Add(Group, new UserAchievement(Group, (int)Row["level"], (int)Row["progress"]));
}
}
}
public static void FillCache(SqlDatabaseClient MySqlClient, uint LinkedItemId)
{
if (mCache.ContainsKey(LinkedItemId))
{
mCache.Remove(LinkedItemId);
}
uint IdValue = 0;
MySqlClient.SetParameter("id", LinkedItemId);
object Result = MySqlClient.ExecuteScalar("SELECT room_id FROM items WHERE id = @id LIMIT 1");
if (Result != null)
{
IdValue = (uint)Result;
}
if (IdValue > 0)
{
mCache.Add(LinkedItemId, IdValue);
}
}
public static Item CreateItem(SqlDatabaseClient MySqlClient, uint DefinitionId, uint UserId, string Flags, string FlagsDisplay, double ExpireTimestamp, bool Untradable = false, WiredManager WiredManager = null)
{
MySqlClient.SetParameter("definitionid", DefinitionId);
MySqlClient.SetParameter("userid", UserId);
MySqlClient.SetParameter("flags", Flags);
MySqlClient.SetParameter("flagsd", FlagsDisplay);
MySqlClient.SetParameter("untradable", Untradable ? "1" : "0");
MySqlClient.SetParameter("expiretimestamp", ExpireTimestamp);
string RawId = MySqlClient.ExecuteScalar("INSERT INTO items (definition_id,user_id,flags,flags_display,untradable,expire_timestamp) VALUES (@definitionid,@userid,@flags,@flagsd,@untradable,@expiretimestamp); SELECT LAST_INSERT_ID();").ToString();
uint Id = 0;
uint.TryParse(RawId, out Id);
if (Id == 0)
{
return null;
}
return new Item(Id, DefinitionId, UserId, 0, new Vector3(), string.Empty, 0, Flags, Flags, Untradable, 0, 0,
ExpireTimestamp, WiredManager);
}
public void AddOrUpdateData(SqlDatabaseClient MySqlClient, uint QuestId, int Progress, bool ActiveQuest)
{
if (ActiveQuest)
{
if (mCurrentQuest > 0)
{
MySqlClient.SetParameter("userid", mUserId);
MySqlClient.SetParameter("questid", mCurrentQuest);
MySqlClient.ExecuteNonQuery("UPDATE user_quests SET is_current = '0' WHERE user_id = @userid AND quest_id = @questid LIMIT 1");
}
mCurrentQuest = QuestId;
}
else if (mCurrentQuest == QuestId)
{
mCurrentQuest = 0;
}
MySqlClient.SetParameter("userid", mUserId);
MySqlClient.SetParameter("questid", QuestId);
MySqlClient.SetParameter("progress", Progress);
MySqlClient.SetParameter("iscurrent", ActiveQuest ? "1" : "0");
lock (mSyncRoot)
{
if (mInner.ContainsKey(QuestId))
{
mInner[QuestId] = Progress;
MySqlClient.ExecuteNonQuery("UPDATE user_quests SET progress = @progress, is_current = @iscurrent WHERE user_id = @userid AND quest_id = @questid LIMIT 1");
}
else
{
mInner.Add(QuestId, Progress);
MySqlClient.ExecuteNonQuery("INSERT INTO user_quests (user_id,quest_id,progress,is_current) VALUES (@userid,@questid,@progress,@iscurrent)");
}
}
}
public static void ReloadFlatCategories(SqlDatabaseClient MySqlClient)
{
int NumberLoaded = 0;
lock (mFlatCategories)
{
mFlatCategories.Clear();
mEventSearchQueries.Clear();
MySqlClient.SetParameter("enabled", "1");
DataTable Table = MySqlClient.ExecuteQueryTable("SELECT * FROM flat_categories WHERE enabled = @enabled ORDER BY order_num ASC");
foreach (DataRow Row in Table.Rows)
{
mFlatCategories.Add(new FlatCategory((int)Row["id"], (Row["visible"].ToString() == "1"),
(string)Row["title"], (Row["allow_trading"].ToString() == "1")));
NumberLoaded++;
}
DataTable EventQueries = MySqlClient.ExecuteQueryTable("SELECT * FROM navigator_event_search_categories");
foreach (DataRow Row in EventQueries.Rows)
{
mEventSearchQueries.Add(Row["query"].ToString().ToLower(), (int)Row["category_id"]);
}
}
Output.WriteLine("Loaded " + NumberLoaded + " flat " + ((NumberLoaded != 1) ? "categories" : "category") + ".", OutputLevel.DebugInformation);
}
public void UpdateWanted(SqlDatabaseClient MySqlClient, int Value)
{
Wanted = Value;
MySqlClient.SetParameter("id", Id);
MySqlClient.SetParameter("wanted", Wanted);
MySqlClient.ExecuteNonQuery("UPDATE characters SET wanted = @wanted WHERE id = @id LIMIT 1");
}
public void ReloadCache(SqlDatabaseClient MySqlClient)
{
lock (mSyncRoot)
{
mInner.Clear();
MySqlClient.SetParameter("user_id", mUserId);
DataTable Table = MySqlClient.ExecuteQueryTable("SELECT ignore_id FROM ignores WHERE user_id = @user_id");
foreach (DataRow Row in Table.Rows)
{
mInner.Add((uint)Row["ignore_id"]);
}
}
}
public void Stop(SqlDatabaseClient MySqlClient)
{
if (Stopped)
{
return;
}
mSocket.Close();
mSocket = null;
if (Authenticated)
{
mCharacterInfo.SynchronizeStatistics(MySqlClient);
if (CurrentRoomId > 0)
{
RoomManager.RemoveUserFromRoom(this, false);
}
MySqlClient.SetParameter("userid", CharacterId);
MySqlClient.ExecuteNonQuery("UPDATE users SET online = 0 WHERE id = @userid");
MessengerHandler.MarkUpdateNeeded(this, 0, true);
}
Output.WriteLine("Stopped and disconnected client " + Id + ".", OutputLevel.DebugInformation);
mStoppedTimestamp = UnixTimestamp.GetCurrent();
}
public void UpdateBadgeOrder(SqlDatabaseClient MySqlClient, Dictionary<int, Badge> NewSettings)
{
MySqlClient.SetParameter("userid", mUserId);
MySqlClient.ExecuteNonQuery("UPDATE badges SET slot_id = 0 WHERE user_id = @userid");
foreach (KeyValuePair<int, Badge> EquippedBadge in NewSettings)
{
MySqlClient.SetParameter("userid", mUserId);
MySqlClient.SetParameter("slotid", EquippedBadge.Key);
MySqlClient.SetParameter("badgeid", EquippedBadge.Value.Id);
MySqlClient.ExecuteNonQuery("UPDATE badges SET slot_id = @slotid WHERE user_id = @userid AND badge_id = @badgeid LIMIT 1");
}
lock (mSyncRoot)
{
mEquippedBadges = NewSettings;
}
}
public void UpdateAchievementBadge(SqlDatabaseClient MySqlClient, string AchievementGroup, Badge NewBadge)
{
MySqlClient.SetParameter("userid", mUserId);
MySqlClient.SetParameter("sourcetype", "achievement");
MySqlClient.SetParameter("sourcedata", AchievementGroup);
MySqlClient.SetParameter("badgeid", NewBadge.Id);
lock (mSyncRoot)
{
if (mAchievementBadges.ContainsKey(AchievementGroup))
{
Badge OldBadge = mAchievementBadges[AchievementGroup];
if (OldBadge == NewBadge)
{
MySqlClient.ClearParameters();
return;
}
mIndexCache.Remove(OldBadge.Code);
mAchievementBadges[AchievementGroup] = NewBadge;
MySqlClient.ExecuteNonQuery("UPDATE badges SET badge_id = @badgeid WHERE user_id = @userid AND source_type = @sourcetype AND source_data = @sourcedata LIMIT 1");
foreach (KeyValuePair<int, Badge> Badge in mEquippedBadges)
{
if (Badge.Value.Id == OldBadge.Id)
{
mEquippedBadges[Badge.Key] = NewBadge;
break;
}
}
}
else
{
mAchievementBadges.Add(AchievementGroup, NewBadge);
MySqlClient.ExecuteNonQuery("INSERT INTO badges (user_id,badge_id,source_type,source_data) VALUES (@userid,@badgeid,@sourcetype,@sourcedata)");
}
mRightsCache = RegenerateRights();
mIndexCache.Add(NewBadge.Code);
}
}
public void ReloadCache(SqlDatabaseClient MySqlClient, AchievementCache UserAchievementCache)
{
Dictionary<int, Badge> EquippedBadges = new Dictionary<int, Badge>();
List<Badge> StaticBadges = new List<Badge>();
Dictionary<string, Badge> AchievementBadges = new Dictionary<string, Badge>();
List<string> IndexCache = new List<string>();
MySqlClient.SetParameter("userid", mUserId);
DataTable Table = MySqlClient.ExecuteQueryTable("SELECT badge_id,slot_id,source_type,source_data FROM badges WHERE user_id = @userid");
foreach (DataRow Row in Table.Rows)
{
Badge Badge = RightsManager.GetBadgeById((uint)Row["badge_id"]);
if (Badge == null)
{
continue;
}
string SourceType = Row["source_type"].ToString();
string SourceData = Row["source_data"].ToString();
Badge BadgeToEquip = null;
if (SourceType == "static")
{
BadgeToEquip = Badge;
StaticBadges.Add(BadgeToEquip);
}
else if (SourceType == "achievement")
{
if (AchievementBadges.ContainsKey(SourceData))
{
continue;
}
UserAchievement UserAchievement = UserAchievementCache.GetAchievementData(SourceData);
if (UserAchievement == null || UserAchievement.Level < 1)
{
MySqlClient.SetParameter("userid", mUserId);
MySqlClient.SetParameter("badgeid", Badge.Id);
MySqlClient.ExecuteNonQuery("DELETE FROM badges WHERE user_id = @userid AND badge_id = @badgeid");
continue;
}
string Code = UserAchievement.GetBadgeCodeForLevel();
BadgeToEquip = (Badge.Code == Code ? Badge : RightsManager.GetBadgeByCode(Code));
AchievementBadges.Add(SourceData, BadgeToEquip);
}
if (BadgeToEquip != null)
{
int SlotId = (int)Row["slot_id"];
if (!EquippedBadges.ContainsKey(SlotId) && SlotId >= 1 && SlotId <= 5)
{
EquippedBadges.Add(SlotId, BadgeToEquip);
}
IndexCache.Add(BadgeToEquip.Code);
}
}
lock (mSyncRoot)
{
mEquippedBadges = EquippedBadges;
mStaticBadges = StaticBadges;
mAchievementBadges = AchievementBadges;
mRightsCache = RegenerateRights();
mIndexCache = IndexCache;
}
}
public static void LogChatMessage(SqlDatabaseClient MySqlClient, uint UserId, uint RoomId, string Message)
{
if (!(bool)ConfigManager.GetValue("moderation.chatlogs.enabled"))
{
return;
}
MySqlClient.SetParameter("userid", UserId);
MySqlClient.SetParameter("roomid", RoomId);
MySqlClient.SetParameter("message", Message);
MySqlClient.SetParameter("timestamp", UnixTimestamp.GetCurrent());
MySqlClient.ExecuteNonQuery("INSERT INTO moderation_chatlogs (user_id,room_id,message,timestamp) VALUES (@userid,@roomid,@message,@timestamp)");
}
public static void LogModerationAction(SqlDatabaseClient MySqlClient, Session Session, string ActionDescr, string ActionDetail)
{
MySqlClient.SetParameter("userid", Session.CharacterId);
MySqlClient.SetParameter("username", Session.CharacterInfo.Username);
MySqlClient.SetParameter("timestamp", UnixTimestamp.GetCurrent());
MySqlClient.SetParameter("actiondescr", ActionDescr);
MySqlClient.SetParameter("actiondetail", ActionDetail);
MySqlClient.ExecuteNonQuery("INSERT INTO moderation_action_log (moderator_id,moderator_name,action_descr,action_detail,timestamp) VALUES (@userid,@username,@actiondescr,@actiondetail,@timestamp)");
}
public void UpdateWeed(SqlDatabaseClient MySqlClient, int Amount)
{
mWeed += Amount;
MySqlClient.SetParameter("userid", mId);
MySqlClient.SetParameter("weed", mWeed);
MySqlClient.ExecuteNonQuery("UPDATE characters SET weed = @weed WHERE id = @userid LIMIT 1");
}
public static void ReloadOfficialItems(SqlDatabaseClient MySqlClient)
{
int NumberLoaded = 0;
lock (mOfficialItems)
{
mOfficialItems.Clear();
MySqlClient.SetParameter("enabled", "1");
DataTable Table = MySqlClient.ExecuteQueryTable("SELECT * FROM navigator_frontpage WHERE enabled = @enabled ORDER BY order_num ASC");
foreach (DataRow Row in Table.Rows)
{
mOfficialItems.Add(new NavigatorOfficialItem((uint)Row["id"], (uint)Row["parent_id"], (uint)Row["room_id"],
(Row["is_category"].ToString() == "1"), (Row["display_type"].ToString() == "details" ?
NavigatorOfficialItemDisplayType.Detailed : NavigatorOfficialItemDisplayType.Banner),
(string)Row["name"], (string)Row["descr"], (Row["image_type"].ToString() == "internal" ?
NavigatorOfficialItemImageType.Internal : NavigatorOfficialItemImageType.External),
(string)Row["image_src"], (string)Row["banner_label"], (Row["category_autoexpand"].ToString() == "1")));
NumberLoaded++;
}
}
Output.WriteLine("Loaded " + NumberLoaded + " navigator frontpage item(s).", OutputLevel.DebugInformation);
}
public void UpdateWorking(SqlDatabaseClient MySqlClient, int Value)
{
Working = Value;
MySqlClient.SetParameter("id", Id);
MySqlClient.SetParameter("working", Working);
MySqlClient.ExecuteNonQuery("UPDATE characters SET working = @working WHERE id = @id LIMIT 1");
}
public void UpdateXP(SqlDatabaseClient MySqlClient, int Value)
{
XP = Value;
MySqlClient.SetParameter("id", Id);
MySqlClient.SetParameter("xp", XP);
MySqlClient.ExecuteNonQuery("UPDATE characters SET xp = @xp WHERE id = @id LIMIT 1");
}
public void SynchronizeDatabase(SqlDatabaseClient MySqlClient)
{
MySqlClient.SetParameter("id", mItemId);
MySqlClient.SetParameter("data1", mData1);
MySqlClient.SetParameter("data2", mData2);
MySqlClient.SetParameter("data3", mData3);
MySqlClient.SetParameter("data4", mData4);
MySqlClient.SetParameter("data5", mData5);
MySqlClient.SetParameter("time", mTime);
MySqlClient.ExecuteNonQuery("UPDATE wired_items SET data1 = @data1, data2 = @data2, data3 = @data3, data4 = @data4, data5 = @data5, time = @time WHERE item_id = @id LIMIT 1");
}
public void SynchronizeDatabase(SqlDatabaseClient MySqlClient)
{
MySqlClient.SetParameter("id", mId);
MySqlClient.SetParameter("roomid", mRoomId);
MySqlClient.SetParameter("roompos", mRoomPosition.ToString());
MySqlClient.SetParameter("exp", mExperience);
MySqlClient.SetParameter("energy", mEnergy);
MySqlClient.SetParameter("happy", mHappiness);
MySqlClient.SetParameter("score", mScore);
MySqlClient.ExecuteNonQuery("UPDATE pets SET room_id = @roomid, room_pos = @roompos, experience = @exp, energy = @energy, happiness = @happy, score = @score WHERE id = @id LIMIT 1");
}
