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 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 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 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 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<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 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 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 int GetStickyType(string colour)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@colour", colour);
int res = db.getInt("SELECT COUNT(*) FROM FurniDefinitions WHERE Col = @colour AND Flags LIKE '%N%';");
if (res > 0)
{
res = db.getInt("SELECT FurniDefinitionID FROM FurniDefinitions WHERE Col = @colour AND Flags LIKE '%N%';");
}
db.sqlexecute.Parameters.Clear();
return res;
}
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 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 VoucherValid(string voucherCode)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@vouchercode", voucherCode);
int res = db.getInt("SELECT COUNT(*) FROM Vouchers WHERE VoucherCode = @vouchercode AND Redeemed = 0;");
db.sqlexecute.Parameters.Clear();
return res > 0;
}
public int GetVoucherAmount(string voucherCode)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@vouchercode", voucherCode);
int res = db.getInt("SELECT VoucherAmount FROM Vouchers WHERE VoucherCode = @vouchercode;");
db.sqlexecute.Parameters.Clear();
return res;
}
public bool FurniExists(int furniId)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@furniid", furniId);
int res = db.getInt("SELECT COUNT(*) FROM Furni WHERE FurniID = @furniid;");
db.sqlexecute.Parameters.Clear();
return res > 0;
}
public int GetTypeFromSprite(string sprite)
{
Database db = new Database();
db.sqlexecute.Parameters.AddWithValue("@sprite", sprite);
int res = db.getInt("SELECT COUNT(*) FROM FurniDefinitions WHERE Sprite = @sprite;");
if (res > 0)
{
res = db.getInt("SELECT FurniDefinitionID FROM FurniDefinitions WHERE Sprite = @sprite;");
}
db.sqlexecute.Parameters.Clear();
return res;
}
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);
}
public int GetRandomPresentDefinition()
{
Database db = new Database();
return db.getInt("SELECT TOP 1 FurniDefinitionID FROM FurniDefinitions WHERE Flags LIKE '%G%' ORDER BY NEWID();");
}