public static async Task <List <AdminModels.AdminLogModel> > GetLog()
{
var conn = new OpenConnection();
if (!conn.IsConnected)
{
conn.Close();
return(null);
}
const string commandString = "SELECT * FROM adminlog ORDER BY id DESC";
var mcom = new MySqlCommand(commandString, conn.Connection);
var ls = new List <AdminModels.AdminLogModel>();
using (var r = await mcom.ExecuteReaderAsync())
{
while (await r.ReadAsync())
{
ls.Add(new AdminModels.AdminLogModel
{
Id = (uint)r["id"],
Type = (LogType)((int)r["type"]),
Moderator = (string)r["moderator"],
Data = (string)r["data"]
});
}
}
conn.Close();
return(ls);
}
public static async Task <int> RegisterWebAdmin(string id, string name)
{
var conn = new OpenConnection();
if (conn.IsConnected)
{
const string com = "SELECT * FROM newwebadmin WHERE id = @param_val_1";
var mCom = new MySqlCommand(com, conn.Connection);
mCom.Parameters.AddWithValue("param_val_1", id);
using (var res = await mCom.ExecuteReaderAsync())
{
if (res.HasRows)
{
while (await res.ReadAsync())
{
var a = (int)res["access"];
conn.Close();
return(a);
}
}
}
const string addcom = "INSERT INTO newwebadmin (id, name, access) VALUES (@param_val_1, @param_val_2, false)";
var addCom = new MySqlCommand(addcom, conn.Connection);
addCom.Parameters.AddWithValue("param_val_1", id);
addCom.Parameters.AddWithValue("param_val_2", name);
await addCom.ExecuteNonQueryAsync();
conn.Close();
return(0);
}
conn.Close();
return(0);
}
public static async Task <List <GTSObject> > GetOpenGTSTrades(int start, int amount)
{
var conn = new OpenConnection();
if (!conn.IsConnected)
{
conn.Close();
return(new List <GTSObject>());
}
var l = new List <GTSObject>();
const string command = "SELECT id, Offer, Request, user_id, username FROM GTS WHERE Accepted=0 LIMIT @pos, @amount";
var mcom = new MySqlCommand(command, conn.Connection);
mcom.Parameters.AddWithValue("@pos", start);
mcom.Parameters.AddWithValue("@amount", amount);
var r = await mcom.ExecuteReaderAsync();
while (r.Read())
{
l.Add(new GTSObject
{
Id = (int)r["id"],
Offer = Deserializer.DeserializePokemon(r["Offer"].ToString()),
Request = JsonConvert.DeserializeObject <GTSFilter>(r["Request"].ToString()),
UserId = (uint)r["user_id"],
Accepted = false,
OwnerName = r["ownername"].ToString()
});
}
conn.Close();
return(l);
}
public static async Task <List <AdminModels.UserPermissions> > GetPermissions()
{
var conn = new OpenConnection();
if (!conn.IsConnected)
{
conn.Close();
return(new List <AdminModels.UserPermissions>());
}
var l = new List <AdminModels.UserPermissions>();
const string command = "SELECT id, access, name FROM newwebadmin";
var mcom = new MySqlCommand(command, conn.Connection);
using (var r = await mcom.ExecuteReaderAsync())
{
while (await r.ReadAsync())
{
l.Add(new AdminModels.UserPermissions
{
Id = r["id"].ToString(),
Permission = (AdminModels.PermissionsEnum)r["access"],
Name = r["name"].ToString()
});
}
}
conn.Close();
return(l);
}
public static async Task <List <Metrics> > GetMetrics()
{
var l = new List <Metrics>();
var conn = new OpenConnection();
if (!conn.IsConnected)
{
conn.Close();
return(l);
}
const string command = "SELECT * FROM CounterMetrics";
var mcom = new MySqlCommand(command, conn.Connection);
var r = await mcom.ExecuteReaderAsync();
while (r.Read())
{
var m = new Metrics
{
Key = (int)r["id"],
Value = (int)r["value"]
};
if (r["name"].GetType() != typeof(DBNull))
{
m.Name = r["name"].ToString();
}
l.Add(m);
}
conn.Close();
return(l);
}
public static async Task <List <WarningsModel> > GetMetrics()
{
var l = new List <WarningsModel>();
var conn = new OpenConnection();
if (!conn.IsConnected)
{
conn.Close();
return(l);
}
const string command =
"SELECT warnings.*, users.username FROM warnings INNER JOIN users ON warnings.user_id = users.user_id ORDER BY warnings.id DESC";
var mcom = new MySqlCommand(command, conn.Connection);
var r = await mcom.ExecuteReaderAsync();
while (r.Read())
{
var m = new WarningsModel
{
Id = (uint)r["id"],
Username = (string)r["username"],
UserId = (uint)r["user_id"],
Reason = (string)r["reason"],
Time = (DateTime)r["time"]
};
l.Add(m);
}
conn.Close();
return(l);
}
public static async Task AddNote(uint user, string moderator, string note)
{
if (note == null)
{
return;
}
if (moderator == null)
{
return;
}
var conn = new OpenConnection();
if (!conn.IsConnected)
{
conn.Close();
return;
}
const string commandString = "INSERT INTO usernotes (user_id, moderator, note, time) VALUES " +
"(@user, @moderator, @note, @time)";
var command = new MySqlCommand(commandString, conn.Connection);
command.Parameters.AddWithValue("user", user);
command.Parameters.AddWithValue("moderator", moderator);
command.Parameters.AddWithValue("note", note);
command.Parameters.AddWithValue("time", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"));
await command.ExecuteNonQueryAsync();
conn.Close();
}
public static async Task <List <GTSObject> > GetUserGTS(uint userId)
{
var conn = new OpenConnection();
if (!conn.IsConnected)
{
conn.Close();
return(new List <GTSObject>());
}
var l = new List <GTSObject>();
const string command = "SELECT id, Offer, Request, user_id, ownername, Accepted FROM GTS WHERE user_id=@id";
var mcom = new MySqlCommand(command, conn.Connection);
mcom.Parameters.AddWithValue("id", userId);
var r = await mcom.ExecuteReaderAsync();
while (r.Read())
{
l.Add(new GTSObject
{
Id = (int)r["id"],
Offer = Deserializer.DeserializePokemon(r["Offer"].ToString()),
Request = JsonConvert.DeserializeObject <GTSFilter>(r["Request"].ToString()),
UserId = (uint)r["user_id"],
Accepted = (bool)r["Accepted"],
OwnerName = r["ownername"].ToString()
});
}
conn.Close();
return(l);
}
public static async Task <List <DirectGiftBase> > GetGifts(string username)
{
var conn = new OpenConnection();
if (!conn.IsConnected)
{
conn.Close();
return(new List <DirectGiftBase>());
}
var l = new List <DirectGiftBase>();
if (username == null)
{
return(l);
}
const string command = "SELECT directgift.user_id, directgift.gifts, users.username " +
"FROM directgift JOIN users " +
"ON directgift.user_id = users.user_id " +
"AND users.username = @username";
var mcom = new MySqlCommand(command, conn.Connection);
mcom.Parameters.AddWithValue("@username", username);
object o = null;
using (var r = await mcom.ExecuteReaderAsync())
{
while (r.Read())
{
o = r["gifts"];
}
}
if (o == null)
{
conn.Close();
return(new List <DirectGiftBase>());
}
var s = o.ToString();
var json = JArray.Parse(s);
foreach (var thing in json)
{
if ((DirectGiftType)Enum.Parse(typeof(DirectGiftType), thing.SelectToken("Type").ToString()) ==
DirectGiftType.Pokemon)
{
l.Add(thing.ToObject <PokemonDirectGift>());
}
else
{
l.Add(thing.ToObject <ItemDirectGift>());
}
}
conn.Close();
return(l);
}
public static async Task BanAlts(uint id, bool value)
{
var conn = new OpenConnection();
if (conn.IsConnected)
{
const string com = "UPDATE users SET banned=@para WHERE user_id IN (SELECT user_id FROM ips WHERE ip IN (SELECT ip FROM ips WHERE user_id = @val))";
var m = new MySqlCommand(com, conn.Connection);
m.Parameters.AddWithValue("val", id);
m.Parameters.AddWithValue("para", value);
await m.ExecuteNonQueryAsync();
}
conn.Close();
}
public static async Task BanSingleIp(string ip, bool value)
{
var conn = new OpenConnection();
if (conn.IsConnected)
{
const string com = "UPDATE ips SET ipban= @value WHERE ip = @parameter";
var m = new MySqlCommand(com, conn.Connection);
m.Parameters.AddWithValue("parameter", ip);
m.Parameters.AddWithValue("value", value);
await m.ExecuteNonQueryAsync();
}
conn.Close();
}
public static async Task BanAccount(string username, bool newvalue)
{
var conn = new OpenConnection();
if (conn.IsConnected)
{
const string com = "UPDATE users SET banned= @value WHERE username = @parameter";
var m = new MySqlCommand(com, conn.Connection);
m.Parameters.AddWithValue("parameter", username);
m.Parameters.AddWithValue("value", newvalue);
await m.ExecuteNonQueryAsync();
}
conn.Close();
}
public static async Task RemoveUserGTS(uint userId)
{
var conn = new OpenConnection();
if (!conn.IsConnected)
{
conn.Close();
return;
}
const string command = "DELETE FROM GTS WHERE user_id = @id";
var mcom = new MySqlCommand(command, conn.Connection);
mcom.Parameters.AddWithValue("@id", userId);
await mcom.ExecuteNonQueryAsync();
conn.Close();
}
public static async Task UpdatePermissions(string userid, int permission)
{
var conn = new OpenConnection();
if (!conn.IsConnected)
{
conn.Close();
return;
}
const string command = "UPDATE newwebadmin SET access= @access WHERE id= @id";
var mcom = new MySqlCommand(command, conn.Connection);
mcom.Parameters.AddWithValue("@id", userid);
mcom.Parameters.AddWithValue("@access", permission);
await mcom.ExecuteNonQueryAsync();
conn.Close();
}
public static async Task Log(LogType type, string moderator, string data)
{
var conn = new OpenConnection();
if (!conn.IsConnected)
{
conn.Close();
return;
}
const string commandString = "INSERT INTO adminlog (type, moderator, data) VALUES (@type, @moderator, @data)";
var command = new MySqlCommand(commandString, conn.Connection);
command.Parameters.AddWithValue("type", type);
command.Parameters.AddWithValue("moderator", moderator);
command.Parameters.AddWithValue("data", data);
await command.ExecuteNonQueryAsync();
conn.Close();
}
public static async Task <GTSObject> GetSingleGTSTrade(int i)
{
var conn = new OpenConnection();
if (!conn.IsConnected)
{
conn.Close();
return(null);
}
const string command = "SELECT id, Offer, Request, user_id, Accepted, ownername, username, Result FROM GTS WHERE id = @id";
var mcom = new MySqlCommand(command, conn.Connection);
mcom.Parameters.AddWithValue("@id", i);
var r = await mcom.ExecuteReaderAsync();
GTSObject obj = null;
while (r.Read())
{
Models.Pokemon result = null;
if (!(r["Result"] is DBNull))
{
result = Deserializer.DeserializePokemon(r["Result"].ToString());
}
obj = new GTSObject
{
Id = (int)r["id"],
Offer = Deserializer.DeserializePokemon(r["Offer"].ToString()),
Request = JsonConvert.DeserializeObject <GTSFilter>(r["Request"].ToString()),
UserId = (uint)r["user_id"],
Accepted = (bool)r["Accepted"],
TraderName = r["username"].ToString(),
OwnerName = r["ownername"].ToString(),
Result = result
};
}
conn.Close();
return(obj);
}
public static async Task SetDirectGifts(string username, List <DirectGiftBase> gifts)
{
var conn = new OpenConnection();
if (!conn.IsConnected)
{
conn.Close();
return;
}
const string command = "INSERT INTO directgift (user_id, gifts) " +
"VALUES ((SELECT user_id FROM users WHERE username= @username), " +
" @gifts) " +
"ON DUPLICATE KEY UPDATE gifts = @gifts";
var json = JsonConvert.SerializeObject(gifts);
var mcom = new MySqlCommand(command, conn.Connection);
mcom.Parameters.AddWithValue("@username", username);
mcom.Parameters.AddWithValue("@gifts", json);
await mcom.ExecuteNonQueryAsync();
conn.Close();
}
public static async Task <string> GetUsernameFromId(uint id)
{
var conn = new OpenConnection();
if (conn.IsConnected)
{
const string com = "SELECT * FROM users WHERE user_id=@val";
var m = new MySqlCommand(com, conn.Connection);
m.Parameters.AddWithValue("val", id);
using (var r = await m.ExecuteReaderAsync())
{
var str = "";
while (await r.ReadAsync())
{
str = (string)r["username"];
}
conn.Close();
return(str);
}
}
conn.Close();
return("");
}
public static async Task <UserRequest> GetUser(string username)
{
var conn = new OpenConnection();
if (!conn.IsConnected)
{
conn.Close();
return(null);
}
var u = new UserRequest {
UserInfo = new UserInfo {
Username = username
}
};
const string usercommand = "SELECT user_id, banned, base FROM users WHERE username = @param_val_1;";
var m = new MySqlCommand(usercommand, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", username);
using (var result = await m.ExecuteReaderAsync())
{
if (!result.HasRows)
{
conn.Close();
return(null);
}
while (await result.ReadAsync())
{
u.UserInfo.User_Id = (uint)result["user_id"];
if (result["banned"].GetType() != typeof(DBNull))
{
u.UserInfo.Banned = (bool)result["banned"];
}
else
{
u.UserInfo.Banned = false;
}
u.FriendSafariString = (string)result["base"];
}
}
u.FriendSafari = new FriendSafari(u.FriendSafariString);
const string getLoginData = "SELECT lastlogin FROM user_data WHERE user_id = @param_val_1";
var glc = new MySqlCommand(getLoginData, conn.Connection);
glc.Parameters.AddWithValue("@param_val_1", u.UserInfo.User_Id);
using (var glcresult = await glc.ExecuteReaderAsync())
{
while (await glcresult.ReadAsync())
{
if (glcresult["lastlogin"].GetType() != typeof(DBNull))
{
u.UserInfo.LastLoggedIn = (DateTime)glcresult["lastlogin"];
}
}
}
//Find IPs
const string ipcommand = "SELECT ip, ipban FROM ips WHERE user_id = @param_val_1;";
var n = new MySqlCommand(ipcommand, conn.Connection);
n.Parameters.AddWithValue("@param_val_1", u.UserInfo.User_Id);
u.IPs = new List <IPInfo>();
using (var ipresult = await n.ExecuteReaderAsync())
{
while (await ipresult.ReadAsync())
{
bool ban;
if (ipresult["ipban"].GetType() != typeof(DBNull))
{
if (ipresult["ipban"] is sbyte)
{
ban = Convert.ToBoolean((sbyte)ipresult["ipban"]);
}
else
{
ban = (bool)ipresult["ipban"];
}
}
else
{
ban = false;
}
var ip = new IPInfo
{
IP = (string)ipresult["ip"],
Banned = ban
};
u.IPs.Add(ip);
}
}
//Find Alts----------------------------------
const string altipcommand = "SELECT user_id FROM ips WHERE FIND_IN_SET (ip, @param_val_1) != 0";
var o = new MySqlCommand(altipcommand, conn.Connection);
var ipl = u.IPs.Select(ip => ip.IP).ToList();
o.Parameters.AddWithValue("param_val_1", string.Join(",", ipl));
var l = new List <uint>();
using (var altipresult = await o.ExecuteReaderAsync())
{
while (altipresult.Read())
{
l.Add((uint)altipresult["user_id"]);
}
}
const string altcommand =
"SELECT username, user_id, banned FROM users WHERE FIND_IN_SET (user_id, @param_val_1) != 0";
var p = new MySqlCommand(altcommand, conn.Connection);
p.Parameters.AddWithValue("param_val_1", string.Join(",", l));
u.Alts = new List <UserInfo>();
using (var altresult = await p.ExecuteReaderAsync())
{
while (altresult.Read())
{
var uinfo = new UserInfo
{
User_Id = (uint)altresult["user_id"],
Username = (string)altresult["username"],
Banned = (bool)altresult["banned"]
};
u.Alts.Add(uinfo);
}
}
//Get Tradelog
u.Trades = await DbTradelog.GetUserTradeLog(u.UserInfo.Username);
u.WonderTrades = await DbTradelog.GetUserWonderTradeLog(u.UserInfo.Username);
//Get warnings
const string warningCommand = "SELECT * FROM warnings WHERE user_id = @id";
var j = new MySqlCommand(warningCommand, conn.Connection);
j.Parameters.AddWithValue("id", u.UserInfo.User_Id);
u.Warnings = new List <WarningsModel>();
using (var res = await j.ExecuteReaderAsync())
{
while (await res.ReadAsync())
{
var warn = new WarningsModel
{
Id = (uint)res["id"],
Reason = (string)res["reason"],
Time = (DateTime)res["time"],
UserId = u.UserInfo.User_Id,
Username = username
};
u.Warnings.Add(warn);
}
}
//Get user notes
const string notesCommand = "SELECT * FROM usernotes WHERE user_id = @id";
var k = new MySqlCommand(notesCommand, conn.Connection);
k.Parameters.AddWithValue("id", u.UserInfo.User_Id);
u.Notes = new List <NotesModel>();
using (var res = await k.ExecuteReaderAsync())
{
while (await res.ReadAsync())
{
var note = new NotesModel
{
Moderator = (string)res["moderator"],
Time = (DateTime)res["time"],
Note = (string)res["note"]
};
u.Notes.Add(note);
}
}
u.GTS = await DbGTS.GetUserGTS(u.UserInfo.User_Id);
conn.Close();
return(u);
}
