public int GetBadgeCount(int roleId)
{
StringBuilder sb = new StringBuilder();
bool hasParent = true;
sb.Append(roleId);
Database db = new Database();
while (hasParent)
{
db.sqlexecute.Parameters.AddWithValue("@roleid", roleId);
roleId = db.getInt("SELECT InheritRole FROM Roles WHERE RoleID = @roleid;");
db.sqlexecute.Parameters.Clear();
if (roleId > 0)
{
sb.Append(", " + roleId.ToString());
}
else
{
hasParent = false;
}
}
int tmpRole = db.getInt("SELECT COUNT(*) FROM Badges WHERE RoleID IN (" + sb.ToString() + ");");
return tmpRole;
}
public bool IsBanned(int userId, string ip, ref string banReason)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@userid", userId);
db.sqlexecute.Parameters.AddWithValue("@expires", DateTime.Now);
db.sqlexecute.Parameters.AddWithValue("@ip", ip);
int res = db.getInt("SELECT COUNT(*) FROM UserBans WHERE UserBanned = @userid AND BanExpires > @expires;");
if (res > 0)
{
banReason = db.getStringValue("SELECT TOP 1 BanMessage FROM UserBans WHERE UserBanned = @userid ORDER BY BanExpires DESC;");
}
if (res == 0)
{
res = db.getInt("SELECT COUNT(*) FROM UserBans WHERE UserBanned IN (SELECT UserID From Users WHERE LastIP = @ip) AND BanExpires > @expires AND BanIP = 1;");
if (res > 0)
{
banReason = db.getStringValue("SELECT TOP 1 BanMessage FROM UserBans WHERE UserBanned IN (SELECT UserID From Users WHERE LastIP = @ip) ORDER BY BanExpires DESC;");
}
}
db.sqlexecute.Parameters.Clear();
return (res > 0);
}
public void DeleteGift(int giftId)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@giftid", giftId);
db.ExecuteSQL("DELETE FROM Gifts Where GiftID = @giftid;");
db.sqlexecute.Parameters.Clear();
}
public void DeleteFurni(int furniID)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@furniid", furniID);
db.ExecuteSQL("DELETE FROM Furni WHERE FurniID = @furniid;");
db.sqlexecute.Parameters.Clear();
}
public void AddReferral(int referrer, int referred)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@userid", referrer);
db.sqlexecute.Parameters.AddWithValue("@referred", referred);
db.ExecuteSQL("INSERT INTO Referrals (UserId, Referred) VALUES (@userid, @referred);");
db.sqlexecute.Parameters.Clear();
}
public bool HasBeenReferred(int userId)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@referred", userId);
int res = db.getInt("SELECT COUNT(*) FROM Referrals WHERE Referred = @referred;");
db.sqlexecute.Parameters.Clear();
return res > 0;
}
public void AddRights(int roomid, int userid)
{
if (HasRights(roomid, userid)) return;
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@roomid", roomid);
db.sqlexecute.Parameters.AddWithValue("@userid", userid);
db.ExecuteSQL("INSERT INTO RoomRights (RoomID, UserID) VALUES (@roomid, @userid);");
db.sqlexecute.Parameters.Clear();
}
public void LogChat(int source, string text, string method, int roomid)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@source", source);
db.sqlexecute.Parameters.AddWithValue("@text", text);
db.sqlexecute.Parameters.AddWithValue("@method", method);
db.sqlexecute.Parameters.AddWithValue("@timestamp", DateTime.Now);
db.sqlexecute.Parameters.AddWithValue("@roomid", roomid);
db.ExecuteSQL("INSERT INTO ChatLog (Source, Text, Method, TimeStamp, RoomID) VALUES (@source, @text, @method, @timestamp, @roomid);");
db.sqlexecute.Parameters.Clear();
}
public void LogCommand(int moderator, int target, string command, string info, string extra)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@moderator", moderator);
db.sqlexecute.Parameters.AddWithValue("@target", target);
db.sqlexecute.Parameters.AddWithValue("@command", command);
db.sqlexecute.Parameters.AddWithValue("@info", info);
db.sqlexecute.Parameters.AddWithValue("@extra", extra);
db.sqlexecute.Parameters.AddWithValue("@timestamp", DateTime.Now);
db.ExecuteSQL("INSERT INTO ModeratorLog (Moderator, Target, Command, Info, Extra, TimeStamp) VALUES (@moderator, @target, @command, @info, @extra, @timestamp);");
db.sqlexecute.Parameters.Clear();
}
public void AddGift(GiftInfo info)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@giftid", info.GiftId);
db.sqlexecute.Parameters.AddWithValue("@purchasecode", info.PurchaseCode);
db.sqlexecute.Parameters.AddWithValue("@payload", info.Payload);
db.ExecuteSQL("INSERT INTO Gifts (GiftID, PurchaseCode, Payload) VALUES (@giftid, @purchasecode, @payload);");
db.sqlexecute.Parameters.Clear();
}
private void CacheTexts()
{
Logging.LogEvent("Caching external texts...", Logging.LogLevel.Info);
mExternalTexts = new Dictionary<string, string>();
Database db = new Database();
DataTable dt = db.GetDataSet("SELECT * FROM ExternalTexts;").Tables[0];
foreach (DataRow dRow in dt.Rows)
{
mExternalTexts.Add(dRow["TextName"].ToString(), dRow["TextValue"].ToString());
}
Logging.LogEvent("Done caching external texts.", Logging.LogLevel.Info);
}
public void BanUser(int userId, int bannerId, string message, double hours, bool banIp)
{
Database db = new Database();
DateTime expireTime = DateTime.Now.AddHours(hours);
db.sqlexecute.Parameters.AddWithValue("@userid", userId);
db.sqlexecute.Parameters.AddWithValue("@banner", bannerId);
db.sqlexecute.Parameters.AddWithValue("@message", message);
db.sqlexecute.Parameters.AddWithValue("@expires", expireTime);
db.sqlexecute.Parameters.AddWithValue("@banip", banIp);
db.ExecuteSQL("INSERT INTO UserBans (UserBanned, UserBanner, BanMessage, BanExpires, BanIP) VALUES (@userid, @banner, @message, @expires, @banip);");
db.sqlexecute.Parameters.Clear();
}
public int CreateNewFurni(int furniTypeID, int userId)
{
Database db = new Database();
SqlParameter identity = new SqlParameter("@identity", SqlDbType.Int);
identity.Value = 0;
identity.Direction = ParameterDirection.Output;
db.sqlexecute.Parameters.Add(identity);
db.sqlexecute.Parameters.AddWithValue("@furnitype", furniTypeID);
db.sqlexecute.Parameters.AddWithValue("@owner", userId);
db.ExecuteSQL("INSERT INTO Furni (FurniTypeID, OwnerID) VALUES (@furnitype, @owner); SET @identity = @@identity;");
db.sqlexecute.Parameters.Clear();
return (int)identity.Value;
}
public int CreateRoom(string roomName, string roomModel, string roomState, int showOwner, int owner)
{
int roomType = GetRoomType(roomModel);
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@roomname", roomName);
db.sqlexecute.Parameters.AddWithValue("@roomtype", roomType);
db.sqlexecute.Parameters.AddWithValue("@roomstate", roomState);
db.sqlexecute.Parameters.AddWithValue("@showowner", showOwner);
db.sqlexecute.Parameters.AddWithValue("@owner", owner);
SqlParameter identity = new SqlParameter("@identity", SqlDbType.Int);
identity.Value = 0;
identity.Direction = ParameterDirection.Output;
db.sqlexecute.Parameters.Add(identity);
db.ExecuteSQL("INSERT INTO Rooms (RoomName, RoomType, RoomStatus, ShowOwner, RoomOwner) VALUES (@roomname, @roomtype, @roomstate, @showowner, @owner); SET @identity = @@identity;");
db.sqlexecute.Parameters.Clear();
return (int) identity.Value;
}
public static void VerifyDatabaseVersion()
{
try
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@configversion", 1);
int dbVer = db.getInt("SELECT DatabaseVersion FROM GameConfig WHERE ConfigVersion = @configversion");
if (dbVer != int.Parse(Settings.DatabaseVersion))
{
Logging.LogEvent("Incorrect database version. Version is " + dbVer.ToString() + " was expecting " + Settings.DatabaseVersion, Logging.LogLevel.Important);
throw new Exception("Incorrect database version");
}
else
{
Logging.LogEvent("Verified database was version " + Settings.DatabaseVersion, Logging.LogLevel.Info);
}
}
catch (Exception e)
{
Logging.LogEvent("An error occured trying to verify the database version. Stack: " + e.ToString(), Logging.LogLevel.Important);
throw new Exception("Could not verify database version");
}
}
public List<RoomInfo> GetRoomListByCategory(int categoryId)
{
Database db = new Database();
List<RoomInfo> results = new List<RoomInfo>();
db.sqlexecute.Parameters.AddWithValue("@catid", categoryId);
DataTable dTable = db.GetDataSet("SELECT TOP 50 * FROM Rooms JOIN RoomTypes ON Rooms.RoomType = RoomTypes.RoomTypeID WHERE CategoryID = @catid ORDER BY RoomOrder ASC, CurrentUsers DESC;").Tables[0];
db.sqlexecute.Parameters.Clear();
foreach (DataRow dRow in dTable.Rows)
{
results.Add(ProduceRoomFromRow(dRow));
}
return results;
}
public void UpdateRoom(RoomInfo room)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@roomid", room.RoomID);
db.sqlexecute.Parameters.AddWithValue("@roomname", room.RoomName);
db.sqlexecute.Parameters.AddWithValue("@roomtype", room.RoomType);
db.sqlexecute.Parameters.AddWithValue("@roomstate", room.RoomStatus);
db.sqlexecute.Parameters.AddWithValue("@showowner", room.ShowOwner);
db.sqlexecute.Parameters.AddWithValue("@owner", room.RoomOwner);
db.sqlexecute.Parameters.AddWithValue("@description", room.RoomDescription);
db.sqlexecute.Parameters.AddWithValue("@password", room.Password);
db.sqlexecute.Parameters.AddWithValue("@superusers", room.SuperUsers);
db.sqlexecute.Parameters.AddWithValue("@wallpaper", room.Wallpaper);
db.sqlexecute.Parameters.AddWithValue("@floor", room.Floor);
db.sqlexecute.Parameters.AddWithValue("@category", room.RoomCategory);
db.sqlexecute.Parameters.AddWithValue("@maxusers", room.MaxUsers);
db.ExecuteSQL("UPDATE Rooms SET RoomName = @roomname, RoomType = @roomtype, RoomStatus = @roomstate, ShowOwner = @showowner, RoomOwner = @owner, RoomDescription = @description, Password = @password, SuperUsers = @superusers,Wallpaper = @wallpaper, Floor = @floor, CategoryID = @category, MaxUsers = @maxusers WHERE RoomID = @roomid;");
db.sqlexecute.Parameters.Clear();
if (InstanceExists(room.RoomID))
{
GetRoomInstance(room.RoomID).RoomInfo = GetRoom(room.RoomID);
}
}
public List<string> GetBadges(int roleId)
{
List<string> results = new List<string>();
bool hasParent = true;
Database db = new Database();
while (hasParent)
{
db.sqlexecute.Parameters.AddWithValue("@roleid", roleId);
DataTable dTable = db.GetDataSet("SELECT * FROM Badges WHERE RoleID = @roleid").Tables[0];
db.sqlexecute.Parameters.Clear();
foreach (DataRow dRow in dTable.Rows)
{
results.Add(dRow["BadgeName"].ToString());
}
db.sqlexecute.Parameters.AddWithValue("@roleid", roleId);
roleId = db.getInt("SELECT InheritRole FROM Roles WHERE RoleID = @roleid;");
db.sqlexecute.Parameters.Clear();
if (roleId == 0)
{
hasParent = false;
}
}
return results;
}
public List<RoomInfo> GetSearchRoomList(string criteria)
{
Database db = new Database();
List<RoomInfo> results = new List<RoomInfo>();
db.sqlexecute.Parameters.AddWithValue("@criteria", criteria);
db.sqlexecute.Parameters.AddWithValue("@plain", criteria.Replace("%", ""));
DataTable dTable = db.GetDataSet("SELECT TOP 50 * FROM Rooms JOIN RoomTypes ON Rooms.RoomType = RoomTypes.RoomTypeID JOIN Users ON Users.UserID = Rooms.RoomOwner WHERE (Username = @plain OR RoomName LIKE @criteria) AND RoomOwner > 0 ORDER BY CurrentUsers DESC;").Tables[0];
db.sqlexecute.Parameters.Clear();
foreach (DataRow dRow in dTable.Rows)
{
results.Add(ProduceRoomFromRow(dRow));
}
return results;
}
public bool HasRight(int roleId, string right)
{
StringBuilder sb = new StringBuilder();
bool hasParent = true;
sb.Append(roleId);
Database db = new Database();
while (hasParent)
{
db.sqlexecute.Parameters.AddWithValue("@roleid", roleId);
roleId = db.getInt("SELECT InheritRole FROM Roles WHERE RoleID = @roleid;");
db.sqlexecute.Parameters.Clear();
if (roleId > 0)
{
sb.Append(", " + roleId.ToString());
}
else
{
hasParent = false;
}
}
db.sqlexecute.Parameters.AddWithValue("@rightname", right);
int tmpRole = db.getInt("SELECT COUNT(*) FROM RoleAccess WHERE RoleID IN (" + sb.ToString() + ") AND RightName = @rightname;");
return (tmpRole > 0);
}
public List<RoomCategory> GetSubCategoryList(int parentId)
{
Database db = new Database();
List<RoomCategory> results = new List<RoomCategory>();
db.sqlexecute.Parameters.AddWithValue("@parent", parentId);
DataTable dTable = db.GetDataSet("SELECT * FROM RoomCategories WHERE RoomCategoryParent = @parent ORDER BY CategoryOrder ASC;").Tables[0];
db.sqlexecute.Parameters.Clear();
foreach (DataRow dRow in dTable.Rows)
{
results.Add(ProduceCategoryFromRow(dRow));
}
return results;
}
public List<RoomInfo> GetUserRoomList(int userId)
{
Database db = new Database();
List<RoomInfo> results = new List<RoomInfo>();
db.sqlexecute.Parameters.AddWithValue("@userid", userId);
DataTable dTable = db.GetDataSet("SELECT TOP 50 * FROM Rooms JOIN RoomTypes ON Rooms.RoomType = RoomTypes.RoomTypeID WHERE RoomOwner = @userid ORDER BY CurrentUsers DESC;").Tables[0];
db.sqlexecute.Parameters.Clear();
foreach (DataRow dRow in dTable.Rows)
{
results.Add(ProduceRoomFromRow(dRow));
}
return results;
}
public Dictionary<int, string> GetRoles()
{
Dictionary<int, string> results = new Dictionary<int, string>();
Database db = new Database();
DataTable dTable = db.GetDataSet("SELECT * FROM Roles").Tables[0];
foreach (DataRow row in dTable.Rows)
{
results.Add((int)row["RoleID"], row["RoleName"].ToString());
}
return results;
}
public void DeleteRights(int roomid, int userid)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@roomid", roomid);
db.sqlexecute.Parameters.AddWithValue("@userid", userid);
db.ExecuteSQL("DELETE FROM RoomRights WHERE RoomID = @roomid AND UserID = @userid;");
db.sqlexecute.Parameters.Clear();
}
public int GetUserType(string modelName)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@modelname", modelName);
int rest = db.getInt("SELECT UserType FROM RoomTypes WHERE RoomModel = @modelname;");
db.sqlexecute.Parameters.Clear();
return rest;
}
public bool HasRights(int roomid, int userid)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@roomid", roomid);
db.sqlexecute.Parameters.AddWithValue("@userid", userid);
int rowCount = db.getInt("SELECT COUNT(*) FROM RoomRights WHERE RoomID = @roomid AND UserID = @userid;");
db.sqlexecute.Parameters.Clear();
return (rowCount > 0);
}
public void ResetUsers()
{
Database db = new Database();
db.ExecuteSQL("UPDATE Rooms SET CurrentUsers = 0;");
db.sqlexecute.Parameters.Clear();
}
public bool UserRoomExists(string modelName)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@modelname", modelName);
int rest = db.getInt("SELECT COUNT(RoomTypeID) FROM RoomTypes WHERE RoomModel = @modelname AND Guest = 1;");
db.sqlexecute.Parameters.Clear();
return rest > 0;
}
public void UpdateUsers(int roomId, int currentUsers)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@roomid", roomId);
db.sqlexecute.Parameters.AddWithValue("@currentusers", currentUsers);
db.ExecuteSQL("UPDATE Rooms SET CurrentUsers = @currentusers WHERE RoomID = @roomid;");
db.sqlexecute.Parameters.Clear();
}
public bool RoomExists(int roomid)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@roomid", roomid);
int rowCount = db.getInt("SELECT COUNT(*) FROM Rooms WHERE RoomID = @roomid;");
db.sqlexecute.Parameters.Clear();
return (rowCount > 0);
}
