public static async Task <bool> TradeIsAccepted(uint tradeid)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return(false);
}
const string s = "SELECT Accepted FROM GTS WHERE id=@id";
var c = new MySqlCommand(s, conn.Connection);
c.Parameters.AddWithValue("@id", tradeid);
var r = await c.ExecuteReaderAsync();
var ret = false;
while (await r.ReadAsync())
{
var accepted = (bool)r["Accepted"];
if (accepted)
{
ret = true;
}
}
await conn.Close();
return(ret);
}
public static async Task GetTrainer(Client client, string username)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return;
}
const string command = "SELECT trainer " +
"FROM friendsafari " +
"INNER JOIN users " +
"ON users.user_id = friendsafari.user_id " +
"WHERE users.username = @param_val_1";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", username);
var trainerObj = await m.ExecuteScalarAsync();
if (trainerObj is DBNull)
{
await client.SendMessage($"<BASETRA result=0 trainer=nil>");
}
else
{
await client.SendMessage($"<BASETRA result=1 trainer={Utilities.Encoding.Base64Encode(trainerObj.ToString())}>");
}
}
public static void Connect()
{
var conn = new OpenConnection();
if (conn.IsConnected())
{
const string query = "SHOW TABLES;";
var cmd = new MySqlCommand(query, conn.Connection);
var reader = cmd.ExecuteReader();
var rows = new List <string>();
while (reader.Read())
{
var row = "";
for (var i = 0; i < reader.FieldCount; i++)
{
row += reader.GetValue(i).ToString();
}
rows.Add(row);
}
reader.Close();
foreach (var row in rows)
{
Console.WriteLine("Optimizing table: " + row);
var optimizequery = $"OPTIMIZE TABLE {row};";
var optimizeCommand = new MySqlCommand(optimizequery, conn.Connection);
optimizeCommand.ExecuteNonQuery();
}
conn.Close();
Console.WriteLine("Optimizing complete!");
}
else
{
throw new Exception("Database not initialized, cannot connect. State: " + conn.Connection.State);
}
}
public static async Task <string> CollectTrade(uint tradeid)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return(null);
}
const string s = "SELECT Result FROM GTS WHERE id=@id";
var c = new MySqlCommand(s, conn.Connection);
c.Parameters.AddWithValue("@id", tradeid);
var ret = "";
var r = await c.ExecuteReaderAsync();
while (await r.ReadAsync())
{
ret = (string)r["Result"];
}
r.Close();
const string delete = "DELETE FROM GTS WHERE id = @id";
var deletecom = new MySqlCommand(delete, conn.Connection);
deletecom.Parameters.AddWithValue("@id", tradeid);
await deletecom.ExecuteNonQueryAsync();
await conn.Close();
return(ret);
}
public static async Task <List <uint> > GetFriends(uint userId)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return(new List <uint>());
}
const string comm = "SELECT friends FROM friend_list WHERE user_id = @id";
var m = new MySqlCommand(comm, conn.Connection);
m.Parameters.AddWithValue("@id", userId);
var l = new List <uint>();
var result = await m.ExecuteReaderAsync();
if (!result.HasRows)
{
l = new List <uint>();
}
if (await result.ReadAsync())
{
var s = result["friends"].ToString();
l = s.Split(',').Select(uint.Parse).ToList();
}
await conn.Close();
return(l);
}
public static async Task <uint> GetUserId(string username)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return(0);
}
const string banuser = "******";
var m = new MySqlCommand(banuser, conn.Connection);
m.Parameters.AddWithValue("name", username);
var result = await m.ExecuteReaderAsync();
uint i = 0;
if (result.Read())
{
i = uint.Parse(result["user_id"].ToString());
}
await conn.Close();
return(i);
}
public static async Task <bool> UserOwnsTrade(uint tradeId, uint userId)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return(false);
}
const string s = "SELECT user_id FROM GTS WHERE id=@id";
var c = new MySqlCommand(s, conn.Connection);
c.Parameters.AddWithValue("@id", tradeId);
var r = await c.ExecuteReaderAsync();
var ret = false;
while (await r.ReadAsync())
{
var user = (uint)r["user_id"];
if (user == userId)
{
ret = true;
}
}
await conn.Close();
return(ret);
}
public static async Task <List <GTS.RequestGtsHolder> > GetUserTrades(uint userId)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return(null);
}
const string s = "SELECT id, Offer, Request, Accepted FROM GTS WHERE user_id = @id";
var c = new MySqlCommand(s, conn.Connection);
c.Parameters.AddWithValue("@id", userId);
var ls = new List <GTS.RequestGtsHolder>();
var r = await c.ExecuteReaderAsync();
while (await r.ReadAsync())
{
var req = new GTS.RequestGtsHolder
{
Index = (int)r["id"],
Offer = JsonConvert.DeserializeObject <GTS.GamePokemon>((string)r["Offer"]),
Request = JsonConvert.DeserializeObject <GTS.RequestData>((string)r["Request"]),
Accepted = (bool)r["Accepted"]
};
ls.Add(req);
}
await conn.Close();
return(ls);
}
public static async Task <string> GetDirectGift(Client client)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return(null);
}
const string command = "SELECT gifts FROM directgift WHERE user_id = @id;";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@id", client.UserId);
var result = await m.ExecuteReaderAsync();
string s = null;
while (await result.ReadAsync())
{
s = (string)result["gifts"];
}
result.Close();
if (s != null)
{
const string delcomm = "DELETE FROM directgift WHERE user_id = @id";
var n = new MySqlCommand(delcomm, conn.Connection);
n.Parameters.AddWithValue("@id", client.UserId);
await n.ExecuteNonQueryAsync();
}
await conn.Close();
return(s);
}
public static async Task GetBase(string username, Client client)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
const string command = "SELECT friendsafari.base, friendsafari.message, users.banned " +
"FROM users " +
"INNER JOIN friendsafari " +
"ON friendsafari.user_id = users.user_id " +
"WHERE users.username = @param_val_1;";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", username);
var result = await m.ExecuteReaderAsync();
if (!result.HasRows)
{
//We just send the login message so we don't need a special client handler just for this. User doesn't notice anyway
await client.SendMessage("<LOG result=0>");
await conn.Close();
return;
}
while (await result.ReadAsync())
{
if ((bool)result["banned"])
{
await client.SendMessage($"<TRA user={username} result=1>");
await conn.Close();
return;
}
if (result["base"] is DBNull)
{
await client.SendMessage($"<VBASE user={username} result=1 base=nil>");
await conn.Close();
return;
}
var Base = result["base"];
var messageDb = result["message"];
var message = messageDb is DBNull ? "nil" : messageDb.ToString();
await client.SendMessage($"<VBASE user={username} result=2 base={Base} message={Utilities.Encoding.Base64Encode(message)}>");
break;
}
await conn.Close();
}
public static async Task RemoveMessage(uint userId)
{
var conn = new OpenConnection();
if (conn.IsConnected())
{
const string command = "UPDATE friendsafari SET message = NULL WHERE user_id = @param_val_2";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_2", userId);
await m.ExecuteNonQueryAsync();
}
await conn.Close();
}
public static void CreateTables()
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
conn.Close();
return;
}
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `CounterMetrics` (`id` int(11) NOT NULL,`name` varchar(255) DEFAULT NULL,`value` int(11) DEFAULT NULL, PRIMARY KEY(`id`))",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `friend_list` ( `user_id` unsigned int(11) NOT NULL, `friends` varchar(255) DEFAULT NULL, PRIMARY KEY (`user_id`))",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `GTS` ( `id` int(11) NOT NULL AUTO_INCREMENT, `Offer` json DEFAULT NULL, `Request` json DEFAULT NULL, `Accepted` tinyint(1) NOT NULL DEFAULT '0'," +
" `Result` json DEFAULT NULL, `user_id` int(11) unsigned DEFAULT NULL, `username` varchar(255) DEFAULT NULL, `OfferLevel` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`))",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `users` ( `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(32) COLLATE utf8_unicode_ci NOT NULL," +
" `password` varchar(11) COLLATE utf8_unicode_ci NOT NULL, `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `usergroup` int(10) NOT NULL DEFAULT '0'," +
" `banned` tinyint(1) NOT NULL DEFAULT '0', `uniquecode` char(8) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0', `base` varchar(10000) COLLATE utf8_unicode_ci DEFAULT NULL," +
" `sprite` varchar(15) COLLATE utf8_unicode_ci NOT NULL, `admin` tinyint(1) DEFAULT '0', PRIMARY KEY (`user_id`), UNIQUE KEY `username` (`username`))",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `ips` ( `user_id` int(10) unsigned NOT NULL, `ip` varchar(15) COLLATE utf8_unicode_ci NOT NULL, `ipban` tinyint(1) DEFAULT NULL," +
" PRIMARY KEY (`user_id`,`ip`), CONSTRAINT `ips_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE)",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `user_data` ( `user_id` int(10) unsigned NOT NULL, `lastlogin` datetime NOT NULL, `guild_id` int(10) unsigned DEFAULT NULL," +
" PRIMARY KEY (`user_id`), CONSTRAINT `user_data_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE)",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `tradelog` ( `user1` char(255) DEFAULT NULL, `user2` char(255) DEFAULT NULL, `pokemon1` json DEFAULT NULL, `pokemon2` json DEFAULT NULL," +
" `time` datetime DEFAULT NULL, `i` smallint(10) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`))",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `newtradelog` ( `user1` char(255) DEFAULT NULL, `user2` char(255) DEFAULT NULL, `pokemon1` json DEFAULT NULL, `pokemon2` json DEFAULT NULL," +
" `time` datetime DEFAULT NULL, `i` smallint(10) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`i`))",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `wondertradelog`( `id` int(10) unsigned NOT NULL AUTO_INCREMENT,`username` varchar(255) DEFAULT NULL, `pokemon` varchar(255) DEFAULT NULL," +
" `time` datetime DEFAULT NULL, PRIMARY KEY (`id`))",
conn.Connection).ExecuteNonQuery();
new MySqlCommand(
"CREATE TABLE IF NOT EXISTS `directgift` (`gifts` json DEFAULT NULL,`user_id` int(11) NOT NULL, PRIMARY KEY (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8",
conn.Connection).ExecuteNonQuery();
conn.Close();
}
public static async Task SetMessage(uint userId, string message)
{
message = Utilities.Encoding.RemoveSpecialCharacters(message);
var conn = new OpenConnection();
if (conn.IsConnected())
{
const string command = "UPDATE friendsafari SET message = @param_val_1 WHERE user_id = @param_val_2";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", message);
m.Parameters.AddWithValue("@param_val_2", userId);
await m.ExecuteNonQueryAsync();
}
await conn.Close();
}
public static async Task Ban(uint userId)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return;
}
const string banuser = "******";
var m = new MySqlCommand(banuser, conn.Connection);
m.Parameters.AddWithValue("user", userId);
await m.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task CancelTrade(uint tradeid)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return;
}
const string s = "DELETE FROM GTS WHERE id = @id";
var c = new MySqlCommand(s, conn.Connection);
c.Parameters.AddWithValue("@id", tradeid);
await c.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task SetAccepted(uint index, string pokemon)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return;
}
const string s = "UPDATE GTS SET Accepted=1, Result=@poke WHERE id = @index";
var c = new MySqlCommand(s, conn.Connection);
c.Parameters.AddWithValue("@poke", pokemon);
c.Parameters.AddWithValue("@index", index);
await c.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task <int> GetNumberOfTrades(uint userid)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return(0);
}
const string com = "SELECT COUNT(*) FROM GTS WHERE user_id=@id";
var mcom = new MySqlCommand(com, conn.Connection);
mcom.Parameters.AddWithValue("@id", userid);
var i = int.Parse((await mcom.ExecuteScalarAsync()).ToString());
await conn.Close();
return(i);
}
public static async Task SetTrainer(Client client, string trainer)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return;
}
const string command = "UPDATE friendsafari SET trainer = @param_val_1 WHERE user_id = @param_val_2";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", trainer);
m.Parameters.AddWithValue("@param_val_2", client.UserId);
await m.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task UploadBase(uint userId, string Base)
{
var conn = new OpenConnection();
if (conn.IsConnected())
{
const string command = "INSERT INTO friendsafari (user_id, base) " +
"VALUES (@param_val_2, @param_val_1) " +
"ON DUPLICATE KEY " +
"UPDATE " +
"base = @param_val_1";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", Base);
m.Parameters.AddWithValue("@param_val_2", userId);
await m.ExecuteNonQueryAsync();
}
await conn.Close();
}
public static async Task MetricCountOne(int key)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
const string command =
"INSERT INTO CounterMetrics (id, value) VALUES (@keyname, 1) ON DUPLICATE KEY UPDATE value = value + 1";
var mcom = new MySqlCommand(command, conn.Connection);
mcom.Parameters.AddWithValue("@keyname", key);
await mcom.ExecuteScalarAsync();
await conn.Close();
}
public static async Task Add(uint userid, string offer, string request, int level, string ownername)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return;
}
const string com = "INSERT INTO GTS (user_id, Offer, Request, OfferLevel, ownername) VALUES (@id, @offer, @request, @level, @ownername)";
var mcom = new MySqlCommand(com, conn.Connection);
mcom.Parameters.AddWithValue("@id", userid);
mcom.Parameters.AddWithValue("@offer", offer);
mcom.Parameters.AddWithValue("@request", request);
mcom.Parameters.AddWithValue("@level", level);
mcom.Parameters.AddWithValue("@ownername", ownername);
await mcom.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task <bool> UserExists(string username)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return(false);
}
const string command = "SELECT COUNT(*) FROM users WHERE username = @param_val_1;";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", username);
var result = int.Parse((await m.ExecuteScalarAsync()).ToString());
await conn.Close();
return(result > 0);
}
public static async Task LogWonderTrade(string username, string pokemon)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
const string command = "INSERT INTO wondertradelog (username, pokemon, time) VALUES (@username, @pokemon, @time)";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@username", username);
m.Parameters.AddWithValue("@pokemon", pokemon);
m.Parameters.AddWithValue("@time", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"));
await m.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task LogWarning(uint userId, string reason)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
Logger.Logger.Log($"Warned user with id {userId} for reason '{reason}'");
const string command = "INSERT INTO warnings (user_id, reason, time) VALUES (@param_val_1, @param_val_2, @param_val_3)";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", userId);
m.Parameters.AddWithValue("@param_val_2", reason);
m.Parameters.AddWithValue("@param_val_3", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"));
await m.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task LogTrade(string u1, string u2, string p1, string p2)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
const string command = "INSERT INTO newtradelog (user1, user2, pokemon1, pokemon2, time) VALUES (@param_val_1, @param_val_2, @param_val_3, @param_val_4, @param_val_5)";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", u1);
m.Parameters.AddWithValue("@param_val_2", u2);
m.Parameters.AddWithValue("@param_val_3", p1);
m.Parameters.AddWithValue("@param_val_4", p2);
m.Parameters.AddWithValue("@param_val_5", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"));
await m.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task UpdateFriends(uint userId, List <uint> friendlist)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
const string comm = "INSERT INTO friend_list (user_id,friends)" +
"VALUES(@uid, @friendlist)" +
"ON DUPLICATE KEY UPDATE" +
"friends = VALUES(friends)";
var m = new MySqlCommand(comm, conn.Connection);
m.Parameters.AddWithValue("@uid", userId);
m.Parameters.AddWithValue("@friendlist", string.Join(",", friendlist.ToArray()));
#pragma warning disable 4014
m.ExecuteNonQueryAsync();
#pragma warning restore 4014
await conn.Close();
}
public static async Task MetricCountMultiple(IEnumerable <int> ls)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
var dic = new Dictionary <int, int>();
foreach (var i in ls)
{
if (dic.ContainsKey(i))
{
dic[i]++;
}
else
{
dic.Add(i, 1);
}
}
var sCommand = new StringBuilder("INSERT INTO CounterMetrics (id, value) VALUES ");
var rows = dic.Select(kp => $"('{kp.Key}','{kp.Value}')").ToList();
sCommand.Append(string.Join(",", rows));
sCommand.Append(";");
var m = new MySqlCommand(sCommand.ToString(), conn.Connection)
{
CommandType = CommandType.Text
};
await m.ExecuteNonQueryAsync();
await conn.Close();
}
public static async Task GetGifts(Client client)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
return;
}
//Get the gifts
const string command = "SELECT giftbox FROM friendsafari WHERE user_id = @param_val_1";
var m = new MySqlCommand(command, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", client.UserId);
var gifts = await m.ExecuteScalarAsync();
string giftString;
if (gifts == null || gifts is DBNull)
{
giftString = "nil";
}
else
{
giftString = gifts.ToString();
}
//Send the gifts
await client.SendMessage($"<FSGIFTS gifts={giftString}>");
//Remove the gifts from the database
const string removeCommand = "UPDATE friendsafari SET giftbox = NULL WHERE user_id = @param_val_1";
var rm = new MySqlCommand(removeCommand, conn.Connection);
rm.Parameters.AddWithValue("@param_val_1", client.UserId);
await rm.ExecuteNonQueryAsync();
}
public static async Task <int> GetMetricValue(int key)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return(0);
}
const string command = "SELECT value FROM CounterMetrics WHERE id=@key LIMIT 1";
var mcom = new MySqlCommand(command, conn.Connection);
mcom.Parameters.AddWithValue("@key", key);
var val = await mcom.ExecuteScalarAsync();
await conn.Close();
if (val == null || val is DBNull)
{
return(0);
}
return((int)val);
}
public static async Task GetRandomBase(Client client)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
const string command = "SELECT friendsafari.base, friendsafari.message, users.username, users.banned " +
"FROM friendsafari " +
"INNER JOIN users " +
"ON friendsafari.user_id = users.user_id " +
"WHERE friendsafari.base IS NOT NULL " +
"AND friendsafari.base <> '' " +
"AND users.banned <> 1 " +
"ORDER BY RAND() " +
"LIMIT 1";
var m = new MySqlCommand(command, conn.Connection);
var result = await m.ExecuteReaderAsync();
while (await result.ReadAsync())
{
var Base = result["base"];
var messageDb = result["message"];
var username = result["username"];
var message = messageDb is DBNull ? "nil" : messageDb.ToString();
await client.SendMessage(
$"<VBASE user={username} result=2 base={Base} message={Utilities.Encoding.Base64Encode(message)}>");
break;
}
await conn.Close();
}
