public bool updateUserInfo(UserInfo user)
{
bool bRet = false;
string sql = string.Empty;
sql += "UPDATE " + Table_of_UserInfo + " SET simip=?simip,avatar=?avatar,pass=?pass WHERE user=?user;";
MySqlCommand cmd = new MySqlCommand(sql, dbcon);
cmd.Parameters.AddWithValue("?simip", user.SimIP);
cmd.Parameters.AddWithValue("?avatar", user.Avatar);
cmd.Parameters.AddWithValue("?pass", user.PswHash);
cmd.Parameters.AddWithValue("?user", user.UserID);
if (cmd.ExecuteNonQuery() > 0)
bRet = true;
else
bRet = false;
cmd.Dispose();
return bRet;
}
public UserInfo fetchUserInfo(string userID)
{
UserInfo userInfo = new UserInfo();
userInfo.UserID = userID;
string sql = string.Empty;
sql += "SELECT * from " + Table_of_UserInfo + " WHERE user = ?userID;";
MySqlCommand cmd = new MySqlCommand(sql, dbcon);
cmd.Parameters.AddWithValue("?userID", userID);
using (MySqlDataReader r = cmd.ExecuteReader())
{
if (r.Read())
{
try
{
userInfo.SimIP = (string)r["simip"];
userInfo.Avatar = (string)r["avatar"];
userInfo.PswHash = (string)r["pass"];
}
catch (Exception e)
{
m_log.Error("[MYSQL] Fetching UserInfo failed: " + e.ToString());
return null;
}
}
else
{
return null;
}
r.Close();
}
return userInfo;
}
public bool addUserInfo(UserInfo userInfo)
{
bool bRet = false;
string sql = string.Empty;
if (userInfo.Avatar==null) return false;
sql += "INSERT INTO " + Table_of_UserInfo +"(`user`,`simip`,`avatar`,`pass`) VALUES";
sql += "(?user,?simip,?avatar,?password);";
MySqlCommand cmd = new MySqlCommand(sql, dbcon);
cmd.Parameters.AddWithValue("?user", userInfo.UserID);
cmd.Parameters.AddWithValue("?simip", userInfo.SimIP);
cmd.Parameters.AddWithValue("?avatar", userInfo.Avatar);
cmd.Parameters.AddWithValue("?password", userInfo.PswHash);
try
{
if (cmd.ExecuteNonQuery() > 0) bRet = true;
cmd.Dispose();
}
catch (Exception e)
{
m_log.Error("Unable to add user information to database: " + e.ToString());
bRet = false;
}
return bRet;
}
/// <summary>
/// Get the user balance when user entering a parcel.
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public XmlRpcResponse handleClientLogin(XmlRpcRequest request, IPEndPoint remoteClient)
{
//m_log.InfoFormat("[MONEY RPC] handleClientLogin:"******"clientUUID")) clientUUID = (string)requestData["clientUUID"];
if (requestData.ContainsKey("clientSessionID")) sessionID = (string)requestData["clientSessionID"];
if (requestData.ContainsKey("clientSecureSessionID")) secureID = (string)requestData["clientSecureSessionID"];
if (requestData.ContainsKey("userServIP")) userServerIP = (string)requestData["userServIP"];
if (requestData.ContainsKey("openSimServIP")) simIP = (string)requestData["openSimServIP"];
if (requestData.ContainsKey("userName")) avatarName = (string)requestData["userName"];
if (avatarName=="") {
responseData["success"] = false;
//responseData["description"] = "Avatar Name is empty";
responseData["clientBalance"] = 0;
return response;
}
userID = clientUUID + "@" + userServerIP;
//Update the session and secure session dictionary
lock (m_sessionDic)
{
if (!m_sessionDic.ContainsKey(userID))
{
m_sessionDic.Add(userID, sessionID);
}
else m_sessionDic[userID] = sessionID;
}
lock (m_secureSessionDic)
{
if (!m_secureSessionDic.ContainsKey(userID))
{
m_secureSessionDic.Add(userID, secureID);
}
else m_secureSessionDic[userID] = secureID;
}
try
{
//m_log.InfoFormat("[MONEY RPC] handleClientLogin: User {0} has logged in, getting balance...", userID);
balance = m_moneyDBService.getBalance(userID);
//add user if not exist
if (balance==-1)
{
if (m_moneyDBService.addUser(userID, m_defaultBalance, 0))
{
responseData["success"] = true;
responseData["description"] = "add user successfully";
responseData["clientBalance"] = m_defaultBalance;
}
else
{
responseData["success"] = false;
responseData["description"] = "add user failed";
responseData["clientBalance"] = 0;
}
}
else if (balance >= 0) //Success
{
responseData["success"] = true;
responseData["description"] = "get user balance successfully";
responseData["clientBalance"] = balance;
}
UserInfo user = new UserInfo();
user.UserID = userID;
user.SimIP = simIP;
user.Avatar = avatarName;
//TODO: Add password protection here
user.PswHash = UUID.Zero.ToString();
if (!m_moneyDBService.TryAddUserInfo(user))
{
m_log.ErrorFormat("[MONEY RPC] handleClientLogin: Unable to refresh information for user \"{0}\" in DB", avatarName);
responseData["success"] = false;
responseData["description"] = "Update or add user information to db failed";
responseData["clientBalance"] = balance;
}
return response;
}
catch (Exception e)
{
m_log.ErrorFormat("[MONEY RPC] handleClientLogin: Can't get balance of user {0}", clientUUID);
responseData["success"] = false;
responseData["description"] = "Exception occured" + e.ToString();
responseData["clientBalance"] = 0;
}
return response;
}
public bool TryAddUserInfo(UserInfo user)
{
MySQLSuperManager dbm = GetLockedConnection();
try
{
if (dbm.Manager.fetchUserInfo(user.UserID) != null)
{
m_log.InfoFormat("[Money DB] Found user \"{0}\",now update information", user.Avatar);
if (m_moneyManager.updateUserInfo(user))
return true;
}
else if (dbm.Manager.addUserInfo(user))
{
m_log.InfoFormat("[Money DB] Unable to find user \"{0}\", add it to DB successfully", user.Avatar);
return true;
}
return false;
}
catch (Exception e)
{
dbm.Manager.Reconnect();
// Fumi.Iseki
m_moneyManager.Reconnect();
m_log.Error(e.ToString());
return false;
}
finally
{
dbm.Release();
}
}
private void UpdateUserInfoTable1()
{
m_log.Info("[MONEY DB]: Converting UserInfo Table...");
string sql = string.Empty;
sql = "SELECT COUNT(*) FROM " + Table_of_UserInfo;
MySqlCommand cmd = new MySqlCommand(sql, dbcon);
int resultCount = int.Parse(cmd.ExecuteScalar().ToString());
cmd.Dispose();
sql = "SELECT * FROM " + Table_of_UserInfo;
cmd = new MySqlCommand(sql, dbcon);
MySqlDataReader dbReader = cmd.ExecuteReader();
int l = 0;
string[,] row = new string[resultCount, dbReader.FieldCount];
while (dbReader.Read()) {
for (int i=0; i<dbReader.FieldCount; i++) {
row[l, i] = dbReader.GetString(i);
}
l++;
}
dbReader.Close();
cmd.Dispose();
bool updatedb = true;
for (int i=0; i<resultCount; i++) {
string uuid = Regex.Replace(row[i, 0], @"@.+$", "");
if (uuid!=row[i, 0]) {
UserInfo userInfo = fetchUserInfo(uuid);
if (userInfo==null) {
userInfo = new UserInfo();
userInfo.UserID = uuid;
userInfo.SimIP = row[i,1];
userInfo.Avatar = row[i,2];
userInfo.PswHash = row[i,3];
updatedb = addUserInfo(userInfo);
}
}
}
// Delete
if (updatedb) {
for (int i=0; i<resultCount; i++) {
string uuid = Regex.Replace(row[i, 0], @"@.+$", "");
if (uuid!=row[i, 0]) {
sql = "DELETE FROM " + Table_of_UserInfo + " WHERE user = ?uuid";
cmd = new MySqlCommand(sql, dbcon);
cmd.Parameters.AddWithValue("?uuid", row[i,0]);
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
//
sql = "BEGIN;";
sql += "ALTER TABLE `" + Table_of_UserInfo + "`";
sql += "COMMENT = 'Rev.2';";
sql += "COMMIT;";
cmd = new MySqlCommand(sql, dbcon);
cmd.ExecuteNonQuery();
}
}
