public async Task <DataAccessResult> ExecuteReaderAsync()
{
DbDataReader reader = await _command.ExecuteReaderAsync();
return(new DataAccessResult(_command, reader));
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
loggerFactory.AddConsole();
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
MySqlConnection connection;
try
{
// [START example]
connection = new MySqlConnection(Configuration["CLOUDSQL_CONNECTION_STRING"]);
connection.Open();
var createTableCommand = new MySqlCommand(@"CREATE TABLE IF NOT EXISTS visits
(time_stamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, user_ip CHAR(64))", connection);
createTableCommand.ExecuteNonQuery();
// [END example]
}
catch (Exception e)
{
app.Run(async(context) =>
{
await context.Response.WriteAsync(string.Format(@"<html>
<head><title>Error</title></head>
<body><p>Set CLOUDSQL_CONNECTION_STRING to a valid connection string.<p>{0}</body>
</html>", WebUtility.HtmlEncode(e.Message)));
});
return;
}
app.Run(async(HttpContext context) =>
{
// [START example]
// Insert a visit into the database:
using (var insertVisitCommand = new MySqlCommand(
@"INSERT INTO visits (user_ip) values (@user_ip)",
connection))
{
insertVisitCommand.Parameters.AddWithValue("@user_ip",
FormatAddress(context.Connection.RemoteIpAddress));
await insertVisitCommand.ExecuteNonQueryAsync();
}
// Look up the last 10 visits.
using (var lookupCommand = new MySqlCommand(
@"SELECT * FROM visits ORDER BY time_stamp DESC LIMIT 10",
connection))
{
List <string> lines = new List <string>();
var reader = await lookupCommand.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
lines.Add($"{reader.GetString(0)} {reader.GetString(1)}");
}
await context.Response.WriteAsync(string.Format(@"<html>
<head><title>Visitor Log</title></head>
<body>Last 10 visits:<br>{0}</body>
</html>", string.Join("<br>", lines)));
}
// [END example]
});
}
public static async Task AddWarningAsync(IUser User, DBConnection dbCon, bool ban = false)
{
ulong userid = User.Id;
bool Found = false;
string query = "SELECT * FROM user_profiles WHERE user_id = @userid;";
var cmd = new MySqlCommand(query, dbCon.Connection);
cmd.Parameters.Add("@userid", MySqlDbType.UInt64).Value = userid;
var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync() && Found == false)
{
int warnings = reader.GetInt32(6) + 1;
if (warnings < 3 && ban == false)
{
// Warn them
cmd.Dispose();
reader.Close();
await User.SendMessageAsync("A global chat moderator has warned you for your actions. Please keep the chat mature!");
query = "UPDATE user_profiles SET count_warnings = @count_warnings WHERE user_id = @userid";
cmd = new MySqlCommand(query, dbCon.Connection);
cmd.Parameters.Add("@userid", MySqlDbType.UInt64).Value = userid;
cmd.Parameters.Add("@count_warnings", MySqlDbType.UInt32).Value = warnings;
try
{
Console.WriteLine("Warned user " + User.Username + ".");
Found = true;
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.Message);
cmd.Dispose();
}
}
else
{
// Blacklist them
cmd.Dispose();
reader.Close();
await User.SendMessageAsync("A global chat moderator has warned you for your actions. You have been blacklisted!");
query = "UPDATE user_profiles SET count_warnings = @count_warnings, user_blacklisted = @blacklisted WHERE user_id = @userid";
cmd = new MySqlCommand(query, dbCon.Connection);
cmd.Parameters.Add("@userid", MySqlDbType.UInt64).Value = userid;
cmd.Parameters.Add("@count_warnings", MySqlDbType.UInt32).Value = 3;
cmd.Parameters.Add("@blacklisted", MySqlDbType.UInt32).Value = 1;
try
{
Console.WriteLine("Blacklisted user " + User.Username + ".");
Found = true;
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.Message);
cmd.Dispose();
}
}
}
dbCon.Close();
}
/**
* Method to read employees from the database
*/
public async Task <List <Employee> > readEmployees()
{
try {
string query = $@"
SELECT
role.id as roleId,
role.name as roleName,
user.id as userId,
user.email as userEmail,
user.status as userStatus,
employee.id as employeeId,
employee.first_name as firstName,
employee.last_name as lastName,
employee.phone_number as phoneNumber,
employee.address as address,
employee.date_of_birth as dateOfBirth
FROM
{DBTables.EMPLOYEE} as employee
INNER JOIN
{DBTables.USER} as user
ON
{DBTables.EMPLOYEE}.user = {DBTables.USER}.id
INNER JOIN
{DBTables.ROLE} as role
ON
{DBTables.USER}.role = {DBTables.ROLE}.id";
MySqlConnection connection = new MySqlConnection(DB.connectionString);
connection.Open();
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.Prepare();
DbDataReader reader = await cmd.ExecuteReaderAsync();
List <Employee> employees = new List <Employee>();
while (reader.Read())
{
int roleId = reader.GetInt32(reader.GetOrdinal("roleId"));
string roleName = reader.GetString(reader.GetOrdinal("roleName"));
int userId = reader.GetInt32(reader.GetOrdinal("userId"));
int userStatus = reader.GetInt32(reader.GetOrdinal("userStatus"));
string userEmail = reader.GetString(reader.GetOrdinal("userEmail"));
int employeeId = reader.GetInt32(reader.GetOrdinal("employeeId"));
string firstName = reader.GetString(reader.GetOrdinal("firstName"));
string lastName = reader.GetString(reader.GetOrdinal("lastName"));
DateTime dob = reader.GetDateTime(reader.GetOrdinal("dateOfBirth"));
string phoneNumber = reader.GetString(reader.GetOrdinal("phoneNumber"));
string address = reader.GetString(reader.GetOrdinal("address"));
Role currentRole = new Role(roleId, roleName);
User currentUser = new User(userId, currentRole, userStatus, userEmail, null);
Employee currentEmployee = new Employee(
employeeId,
firstName,
lastName,
phoneNumber,
address,
dob,
currentUser
);
employees.Add(currentEmployee);
}
return(employees);
} catch (Exception e) {
throw e;
}
}
public async Task <PlayerPasswordAndInfo?> GetPlayerInfoAndPassword(string username)
{
try
{
var player_info = new PlayerPasswordAndInfo
{
player_info = new PlayerInfo
{
friends = new List <BasicPlayerInfo>(),
ignored = new List <BasicPlayerInfo>(),
pending_friend_requests_incoming = new List <BasicPlayerInfo>(),
pending_friend_requests_outgoing = new List <BasicPlayerInfo>()
}
};
using (var conn = get_conn())
{
using (var personal_info_cmd = new MySqlCommand
{
CommandText = SelectPlayerPersonalInfo,
Connection = conn
})
{
personal_info_cmd.Parameters.AddWithValue("@username", username);
var personal_info_reader = await personal_info_cmd.ExecuteReaderAsync();
if (!await personal_info_reader.ReadAsync())
{
return(null);
}
player_info.player_info.permanent_id = personal_info_reader.GetInt64(0);
player_info.password_hash = personal_info_reader.GetString(1);
player_info.player_info.display_name = personal_info_reader.GetString(2);
}
using (var friends_cmd = new MySqlCommand
{
CommandText = SelectFriendsOfPlayer,
Connection = conn
})
{
friends_cmd.Parameters.AddWithValue("@a", player_info.player_info.permanent_id);
friends_cmd.Parameters.AddWithValue("@b", player_info.player_info.permanent_id);
var friends_task = friends_cmd.ExecuteReaderAsync();
using (var ignored_conn = get_conn())
using (var ignored_cmd = new MySqlCommand
{
CommandText = SelectIgnoredOfPlayer,
Connection = ignored_conn
})
{
ignored_cmd.Parameters.AddWithValue("@ignorer", player_info.player_info.permanent_id);
var ignored_task = ignored_cmd.ExecuteReaderAsync();
using (var pending_conn = get_conn())
using (var pending_cmd = new MySqlCommand
{
CommandText = SelectPendingFriendRequests,
Connection = pending_conn
})
{
pending_cmd.Parameters.AddWithValue("@gamer", player_info.player_info.permanent_id);
var pending_task = pending_cmd.ExecuteReaderAsync();
var tasks = new List <Task <DbDataReader> >(new[] { friends_task, ignored_task, pending_task });
for (int i = 0; i < tasks.Count;)
{
var completed_task = await Task.WhenAny(tasks);
var reader = await completed_task;
var idx = tasks.IndexOf(completed_task);
tasks[idx] = null;
switch (idx)
{
case 0:
{
while (await reader.ReadAsync())
{
var a = reader.GetInt64(0);
var b = reader.GetInt64(1);
if (a == player_info.player_info.permanent_id)
{
if (b == player_info.player_info.permanent_id)
{
Log.Warning("Database contains friend matchings which match with itself.");
continue;
}
var friend = await GetBasicInfoOfPlayer(b);
if (friend == null)
{
continue;
}
player_info.player_info.friends.Add(friend);
}
else if (b == player_info.player_info.permanent_id)
{
if (a == player_info.player_info.permanent_id)
{
Log.Warning("Database contains friend matchings which match with itself.");
continue;
}
var friend = await GetBasicInfoOfPlayer(a);
if (friend == null)
{
continue;
}
player_info.player_info.friends.Add(friend);
}
else
{
Log.Fatal("Expected query to give the actual player in either a or b");
}
}
}
break;
case 1:
{
while (await reader.ReadAsync())
{
var ignored_permanent_id = reader.GetInt64(0);
var ignored = await GetBasicInfoOfPlayer(ignored_permanent_id);
if (ignored == null)
{
continue;
}
player_info.player_info.ignored.Add(ignored);
}
}
break;
case 2:
{
while (await reader.ReadAsync())
{
var to = reader.GetInt64(0);
var from = reader.GetInt64(1);
if (from == player_info.player_info.permanent_id) //Outgoing
{
if (to == player_info.player_info.permanent_id)
{
Log.Warning("Database contains friend matchings which match with itself.");
continue;
}
var other_player = await GetBasicInfoOfPlayer(to);
if (other_player == null)
{
continue;
}
player_info.player_info.pending_friend_requests_outgoing.Add(other_player);
}
else if (to == player_info.player_info.permanent_id) //Incoming
{
if (from == player_info.player_info.permanent_id)
{
Log.Warning("Database contains friend matchings which match with itself.");
continue;
}
var other_player = await GetBasicInfoOfPlayer(to);
if (other_player == null)
{
continue;
}
player_info.player_info.pending_friend_requests_incoming.Add(other_player);
}
else
{
Log.Fatal("Expected query to give the actual player in either from or to");
}
}
}
break;
default:
throw new Exception("Expects there to be three tasks.");
}
}
}
}
}
}
return(player_info);
} catch (Exception e)
{
Log.Exception(e);
return(null);
}
}
/**
* Developer note: THIS METHOD DOES NOT SUPPORT PASSWORD CHECKING YET
*/
public async Task <User> GrabUserDetailsAsync(string username, SecureString password)
{
var user = new User();
var affiliationId = -1;
var participantId = -1;
var affiliations = new ObservableCollection <Affiliation>(await RefreshAffiliations());
var participants = new ObservableCollection <Participant>(await RefreshParticipants());
string getUserDetails = "SELECT * FROM " + Constants.USER + " WHERE username = @username;";
using (var connection1 = new MySqlConnection(Constants.CONNECTION_STRING))
{
await connection1.OpenAsync();
using (var cmd = new MySqlCommand())
{
cmd.Connection = connection1;
cmd.CommandText = getUserDetails;
cmd.Prepare();
cmd.Parameters.AddWithValue("@username", username);
//Add password parameter in the near future
var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
user.Id = reader.GetInt32(0);
user.FirstName = reader.GetString(1);
user.LastName = reader.GetString(2);
affiliationId = reader.GetInt32(3);
user.Email = reader.GetString(5);
if (!(await reader.IsDBNullAsync(6)))
{
participantId = reader.GetInt32(6);
}
}
//In the future, this should be connected to a list of affiliations in that the whole program shares
foreach (Affiliation tempAffiliation in affiliations)
{
if (tempAffiliation.Id == affiliationId)
{
user.Affiliation = tempAffiliation;
break;
}
}
//In the future, this should be connected to a list of participants in that the whole program shares
foreach (Participant participant in participants)
{
if (participant.Id == participantId)
{
user.Participant = participant;
break;
}
}
}
}
return(user);
}
public void ExecuteGetQuery(SQLQuery sql_query, Action <Data.GridData> on_success = null, Action <SQLError> on_fail = null)
{
if (connection != null && sql_query != null)
{
if (connected)
{
MySqlCommand adapter = new MySqlCommand(sql_query.Query, connection);
foreach (KeyValuePair <string, object> entry in sql_query.Parameters)
{
adapter.Parameters.AddWithValue(entry.Key, entry.Value);
}
adapter.ExecuteReaderAsync().ContinueWith(delegate(Task <DbDataReader> task)
{
string error_msg = "";
Exception exception = null;
bool has_errors = task.HasErrors(out error_msg, out exception);
if (!has_errors)
{
List <List <object> > query_data = new List <List <object> >();
if (task.Result.HasRows)
{
while (task.Result.Read())
{
List <object> row = new List <object>(task.Result.FieldCount);
int fields = task.Result.FieldCount;
for (int i = 0; i < fields; ++i)
{
object curr_field = task.Result.GetValue(i);
row.Add(curr_field);
}
query_data.Add(row);
}
}
task.Result.Close();
Data.GridData ret = new Data.GridData(query_data);
if (on_success != null)
{
on_success.Invoke(ret);
}
}
else
{
SQLError ret = new SQLError();
ret.ErrorMessage = error_msg;
ret.ErrorException = exception;
if (on_fail != null)
{
on_fail.Invoke(ret);
}
}
});
}
}
}
public TableWatcher(string table_name)
{
this.table_name = table_name;
table_watcher_task = new Task(async() =>
{
watcher_running = true;
while (watcher_running)
{
if (DateTime.Now.Ticks - last_check_occured_at >= check_interval.Ticks)
{
last_check_occured_at = DateTime.Now.Ticks;
long new_checksum = -1;
var cmd_str = "CHECKSUM TABLE " + table_name + ";";
var sqlconn = await ARDBConnectionManager.default_manager.CheckOut();
try
{
using (MySqlCommand sqlcmd = new MySqlCommand(cmd_str, sqlconn.Connection))
using (MySqlDataReader reader = (MySqlDataReader)(await sqlcmd.ExecuteReaderAsync()))
if (await reader.ReadAsync())
{
new_checksum = reader.GetUInt32(1);
//Console.WriteLine("checksum " + new_checksum);
}
}
catch (Exception ex)
{
}
ARDBConnectionManager.default_manager.CheckIn(sqlconn);
if (new_checksum != last_checksum)
{
Task.Run(() => { tablechangeevent(table_name, new_checksum); });
}
last_checksum = new_checksum;
}
else
{
await Task.Delay(250);
}
}
});
}
/// <summary>
/// Asynchronous version of SelectPreparedStatement.
/// </summary>
protected async Task <MySqlResult> SelectPreparedStatementAsync(PreparedStatementId id, params object[] parameters)
{
if (!preparedStatements.TryGetValue(id, out PreparedStatement preparedStatement))
{
throw new InvalidPreparedStatementException(id);
}
Debug.Assert(parameters.Length == preparedStatement.Types.Count);
try
{
using (var connection = new MySqlConnection(connectionString))
{
using (var command = new MySqlCommand(preparedStatement.Query, connection))
{
for (int i = 0; i < preparedStatement.Types.Count; i++)
{
command.Parameters.Add("", preparedStatement.Types[i]).Value = parameters[i];
}
await connection.OpenAsync();
using (MySqlDataReader commandReader = (MySqlDataReader)await command.ExecuteReaderAsync())
return(new MySqlResult(commandReader));
}
}
}
catch (Exception exception)
{
log.Error(exception, $"An exception occured while selecting prepared statement {id}!");
throw;
}
}
public async Task <Customer> FindAsync(params object[] keyValues)
{
if (keyValues == null || keyValues.Length == 0 || keyValues[0] == null)
{
throw new ArgumentNullException(nameof(keyValues));
}
MySqlCommand cmd = new MySqlCommand("GetCustomerByEmail", con)
{
CommandType = System.Data.CommandType.StoredProcedure
};
cmd.Parameters.Add(new MySqlParameter()
{
ParameterName = "email",
Value = keyValues[0],
MySqlDbType = MySqlDbType.VarChar,
Direction = System.Data.ParameterDirection.Input
});
Customer cust = null;
try
{
using (CancellationTokenSource tkn = new CancellationTokenSource(5000))
{
//open connection
await con.OpenAsync(tkn.Token).ConfigureAwait(true);
//if unable to connect to the db, cancel the request
if (tkn.IsCancellationRequested)
{
return(null);
}
}
DbDataReader reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false);
while (await reader.ReadAsync().ConfigureAwait(false))
{
byte[] pass = new byte[64],
salt = new byte[32];
reader.GetBytes(reader.GetOrdinal("Password"), 0, pass, 0, 64);
reader.GetBytes(reader.GetOrdinal("Salt"), 0, salt, 0, 32);
cust = new Customer(
reader.GetInt32(reader.GetOrdinal("CustomerId")),
reader.GetString(reader.GetOrdinal("Email")),
reader.GetString(reader.GetOrdinal("FirstName")),
reader.GetString(reader.GetOrdinal("LastName")),
reader.GetInt32(reader.GetOrdinal("CityId")),
reader.GetString(reader.GetOrdinal("CityName")),
0 /*reader.GetInt64(reader.GetOrdinal("StreetId"))*/, //street table not yet available
"" /*reader.GetString(reader.GetOrdinal("Street"))*/,
reader.GetString(reader.GetOrdinal("StreetNumber")),
reader.GetDateTime(reader.GetOrdinal("Created")),
pass,
salt
);
}
}
catch (Exception)
{
throw;
}
finally
{
await con.CloseAsync().ConfigureAwait(false);
cmd.Dispose();
}
return(cust);
}
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);
}
public static async Task Register(Client client, string username, string password, string email)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return;
}
username = username.RemoveSpecialCharacters();
var check = new MySqlCommand("SELECT " +
"(SELECT COUNT(*) username FROM users WHERE username = @val) usernames, " +
"(SELECT COUNT(*) FROM ips WHERE ip = @ip AND ipban = 1) ipbans", conn.Connection);
check.Parameters.AddWithValue("val", username);
check.Parameters.AddWithValue("ip", client.Ip.ToString());
var checkres = await check.ExecuteReaderAsync();
var canContinue = true;
while (await checkres.ReadAsync())
{
if ((long)checkres["usernames"] > 0)
{
await client.SendMessage("<REG result=0>");
canContinue = false;
}
else if ((long)checkres["ipbans"] > 0)
{
await client.SendMessage("<LOG result=2>");
canContinue = false;
Logger.Logger.Log($"User was blocked from registering because of IP ban: {username}");
}
}
if (!canContinue)
{
await conn.Close();
return;
}
checkres.Close();
var create = new MySqlCommand("INSERT INTO users (username, password, email, usergroup, base, sprite) " +
"VALUES (@name, @pass, @email, @usergroup, @base, @sprite)",
conn.Connection);
create.Parameters.AddWithValue("name", username);
create.Parameters.AddWithValue("pass", password);
create.Parameters.AddWithValue("email", email);
create.Parameters.AddWithValue("usergroup", 0);
create.Parameters.AddWithValue("base", "");
create.Parameters.AddWithValue("sprite", "");
await create.ExecuteNonQueryAsync();
var getUserid = new MySqlCommand("SELECT user_id FROM users WHERE username = @user", conn.Connection);
getUserid.Parameters.AddWithValue("user", username);
var idreader = await getUserid.ExecuteReaderAsync();
while (await idreader.ReadAsync())
{
client.UserId = (uint)idreader["user_id"];
}
idreader.Close();
var logonline = new MySqlCommand("INSERT INTO user_data (user_id, lastlogin) VALUES (@uid, @time)", conn.Connection);
logonline.Parameters.AddWithValue("uid", client.UserId);
logonline.Parameters.AddWithValue("time", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"));
#pragma warning disable 4014
logonline.ExecuteNonQueryAsync();
#pragma warning restore 4014
const string ipsetcommand = "INSERT INTO ips VALUES (@param_val_1, @param_val_2, @param_val_3)";
var o = new MySqlCommand(ipsetcommand, conn.Connection);
o.Parameters.AddWithValue("@param_val_1", client.UserId);
o.Parameters.AddWithValue("@param_val_2", client.Ip);
o.Parameters.AddWithValue("@param_val_3", 0);
#pragma warning disable 4014
o.ExecuteNonQueryAsync();
#pragma warning restore 4014
await client.SendMessage("<REG result=2>");
await conn.Close();
Logger.Logger.Log($"User registered: {username}");
}
public static async Task <LoginResult> Login(string username, string password, Client client)
{
var conn = new OpenConnection();
if (!conn.IsConnected())
{
await conn.Close();
return(LoginResult.WrongUsername);
}
username = username.RemoveSpecialCharacters();
const string logincommand = "SELECT users.user_id, usergroup, banned, password, admin, " +
"GROUP_CONCAT(ip separator ',') as 'IPs', " +
"(SELECT COUNT(*) FROM ips WHERE ip=@param_val_2 AND ipban=1) ipbans " +
"FROM users " +
"INNER JOIN ips " +
"ON users.user_id=ips.user_id " +
"WHERE username = @param_val_1 ";
var m = new MySqlCommand(logincommand, conn.Connection);
m.Parameters.AddWithValue("@param_val_1", username);
m.Parameters.AddWithValue("@param_val_2", client.Ip.ToString());
var result = await m.ExecuteReaderAsync();
var ret = LoginResult.Unset;
if (!result.HasRows)
{
await conn.Close();
return(LoginResult.WrongUsername);
}
var ips = new List <string>();
while (await result.ReadAsync())
{
if (result["password"].ToString() != password)
{
await conn.Close();
return(LoginResult.WrongPassword);
}
if (result["admin"] is DBNull)
{
client.Admin = false;
}
else if ((bool)result["admin"])
{
client.Admin = true;
}
if (client.Admin)
{
Console.WriteLine("Admin logged in, username: "******"banned"] && !client.Admin)
{
await conn.Close();
return(LoginResult.Banned);
}
if (((long)result["ipbans"] > 0))
{
ret = LoginResult.IpBanned;
}
ips = result["IPs"].ToString().Split(',').ToList();
var id = (uint)result["user_id"];
client.UserId = id;
}
result.Close();
if (!ips.Contains(client.Ip.ToString()))
{
const string ipsetcommand = "INSERT INTO ips VALUES (@param_val_1, @param_val_2, @param_val_3)";
var o = new MySqlCommand(ipsetcommand, conn.Connection);
o.Parameters.AddWithValue("@param_val_1", client.UserId);
o.Parameters.AddWithValue("@param_val_2", client.Ip);
o.Parameters.AddWithValue("@param_val_3", 0);
try
{
await o.ExecuteNonQueryAsync();
}
catch
{
// ignore
}
}
if (ret == LoginResult.Unset)
{
ret = LoginResult.Okay;
}
if (ret == LoginResult.Banned || ret == LoginResult.IpBanned)
{
#pragma warning disable 4014
DbUserManagement.Ban(client.UserId, ips);
DBWarnLog.LogWarning(client.UserId, "Automatic ban: IP ban");
#pragma warning restore 4014
Logger.Logger.Log($"User was banned automatically: {username}");
}
const string loggedInRegisterCom = "UPDATE user_data SET lastlogin = @param_val_2 WHERE user_id = @param_val_1";
var logregcommand = new MySqlCommand(loggedInRegisterCom, conn.Connection);
logregcommand.Parameters.AddWithValue("param_val_1", client.UserId);
logregcommand.Parameters.AddWithValue("param_val_2", DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss"));
await logregcommand.ExecuteNonQueryAsync();
if (ret == LoginResult.Okay)
{
Logger.Logger.Log($"User logged in: {username}");
}
await conn.Close();
return(ret);
}
internal static async Task <MySqlReader> ExecuteAsync(MySqlCommand mySqlCommand, Model model, CancellationToken cancellationToken)
{
var mySqlDataReader = (MySqlDataReader)(await mySqlCommand.ExecuteReaderAsync(cancellationToken));
return(new MySqlReader(mySqlDataReader, model));
}
public async Task <List <Patient> > GetAllPatients()
{
var allPatients = new List <Patient>();
using (MySqlConnection conn = DbConnection.GetConnection())
{
//Open the connection
System.Text.EncodingProvider ppp;
ppp = System.Text.CodePagesEncodingProvider.Instance;
Encoding.RegisterProvider(ppp);
await conn.OpenAsync();
string query = "select id, fname, lname, ssn, DATE(dob), sex, address, city, state, zip, phone from patient";
using (MySqlCommand comm = new MySqlCommand(query, conn))
{
using (var reader = await comm.ExecuteReaderAsync())
{
int idOrdinal = reader.GetOrdinal("id");
int fnameOrdinal = reader.GetOrdinal("fname");
int lnameOrdinal = reader.GetOrdinal("lname");
int ssnOrdinal = reader.GetOrdinal("ssn");
int dobOrdinal = reader.GetOrdinal("DATE(dob)");
int sexOrdinal = reader.GetOrdinal("sex");
int addressOrdinal = reader.GetOrdinal("address");
int cityOrdinal = reader.GetOrdinal("city");
int stateOrdinal = reader.GetOrdinal("state");
int zipOrdinal = reader.GetOrdinal("zip");
int phoneOrdinal = reader.GetOrdinal("phone");
while (await reader.ReadAsync())
{
int id = !reader.IsDBNull(idOrdinal) ? reader.GetInt32(idOrdinal) : 1;
string fname = !reader.IsDBNull(fnameOrdinal) ? reader.GetString(fnameOrdinal) : null;
string lname = !reader.IsDBNull(lnameOrdinal) ? reader.GetString(lnameOrdinal) : null;
string ssn = !reader.IsDBNull(ssnOrdinal) ? reader.GetString(ssnOrdinal) : null;
DateTime dob = !reader.IsDBNull(dobOrdinal) ? reader.GetDateTime(dobOrdinal).Date : new DateTime(2001, 1, 1).Date;
string sex = !reader.IsDBNull(sexOrdinal) ? reader.GetString(sexOrdinal) : null;
string address = !reader.IsDBNull(addressOrdinal) ? reader.GetString(addressOrdinal) : null;
string city = !reader.IsDBNull(cityOrdinal) ? reader.GetString(cityOrdinal) : null;
string state = !reader.IsDBNull(stateOrdinal) ? reader.GetString(stateOrdinal) : null;
int zip = !reader.IsDBNull(zipOrdinal) ? reader.GetInt32(zipOrdinal) : 0;
string phone = !reader.IsDBNull(phoneOrdinal) ? reader.GetString(phoneOrdinal) : null;
var patient = new Patient
{
Id = id,
FirstName = fname,
LastName = lname,
SSN = ssn,
DOB = dob,
Sex = sex,
Address = address,
City = city,
State = state,
Zip = zip,
Phone = phone
};
allPatients.Add(patient);
}
}
}
}
return(allPatients);
}
public async Task <IEnumerable <ClanSave> > SearchClansAsync(Level level, ClanQuery search, CancellationToken cancellationToken)
{
using (var sql = new MySqlConnection(_connectionString))
{
await sql.OpenAsync();
// Look for clan IDs with the basic criteria.
var clanIds = new List <long>(64);
var commandTxt = "SELECT `clan_id` FROM `clans` WHERE `invite_type` < 3 AND name LIKE @TextSearch AND `perk_points` <= @PerkPoints AND `exp_levels` <= @ExpLevels";
using (var command = new MySqlCommand(commandTxt, sql))
{
command.Parameters.AddWithValue("TextSearch", search.TextSearch + '%');
command.Parameters.AddWithValue("PerkPoints", search.PerkPoints);
command.Parameters.AddWithValue("ExpLevels", search.ExpLevels);
if (search.ClanLocation != null)
{
command.CommandText += " AND `location` = @ClanLocation";
command.Parameters.AddWithValue("ClanLocation", search.ClanLocation);
}
if (search.WarFrequency != null)
{
command.CommandText += " AND `war_frequency` = @WarFrequency";
command.Parameters.AddWithValue("WarFrequency", search.WarFrequency);
}
if (search.OnlyCanJoin)
{
command.CommandText += " AND `required_trophies` <= @Trophies";
command.Parameters.AddWithValue("Trophies", search.PerkPoints);
}
// Select 64 random clans.
command.CommandText += " LIMIT 64";
using (var reader = await command.ExecuteReaderAsync(CommandBehavior.KeyInfo, cancellationToken))
{
while (await reader.ReadAsync())
{
clanIds.Add((long)reader[0]);
}
}
}
if (clanIds.Count > 0)
{
var clans = new List <ClanSave>();
var clanToDelete = new List <long>(clanIds.Count);
var clanToLoad = new List <long>(clanIds.Count);
// Load up the clan member count in the db.
for (int i = 0; i < clanIds.Count; i++)
{
using (var command = new MySqlCommand("SELECT COUNT(`clan_id`) FROM `clan_members` WHERE `clan_id` = @ClanId", sql))
{
command.Parameters.AddWithValue("ClanId", clanIds[i]);
using (var reader = await command.ExecuteReaderAsync(cancellationToken))
{
if (await reader.ReadAsync())
{
var count = (long)reader[0];
// If clan is completely full the user can't join it.
if (count >= 50 && search.OnlyCanJoin)
{
continue;
}
// Mark the clan for deletion if it has no clan members.
if (count == 0)
{
clanToDelete.Add(clanIds[i]);
}
else if (count >= search.MinimumMembers && count <= search.MaximumMembers)
{
clanToLoad.Add(clanIds[i]);
}
}
}
}
}
for (int i = 0; i < clanToLoad.Count; i++)
{
cancellationToken.ThrowIfCancellationRequested();
var clanId = clanToLoad[i];
var clan = await LoadClanAsync(clanId, cancellationToken);
clans.Add(clan);
}
return(clans);
}
return(null);
}
}
public async Task <Patient> GetPatientById(int id)
{
Patient patient = null;
var qry = "SELECT * FROM patient WHERE id = @id";
var conn = DbConnection.GetConnection();
await conn.OpenAsync();
using (var command = new MySqlCommand(qry, conn))
{
command.Parameters.Add("@id", (DbType)MySqlDbType.Int16).Value = id;
using (var reader = await command.ExecuteReaderAsync())
{
int idOrdinal = reader.GetOrdinal("id");
int fnameOrdinal = reader.GetOrdinal("fname");
int lnameOrdinal = reader.GetOrdinal("lname");
int ssnOrdinal = reader.GetOrdinal("ssn");
int dobOrdinal = reader.GetOrdinal("dob");
int sexOrdinal = reader.GetOrdinal("sex");
int addressOrdinal = reader.GetOrdinal("address");
int cityOrdinal = reader.GetOrdinal("city");
int stateOrdinal = reader.GetOrdinal("state");
int zipOrdinal = reader.GetOrdinal("zip");
int phoneOrdinal = reader.GetOrdinal("phone");
while (await reader.ReadAsync())
{
int _id = !reader.IsDBNull(idOrdinal) ? reader.GetInt32(idOrdinal) : 1;
string _fname = !reader.IsDBNull(fnameOrdinal) ? reader.GetString(fnameOrdinal) : null;
string _lname = !reader.IsDBNull(lnameOrdinal) ? reader.GetString(lnameOrdinal) : null;
string ssn = !reader.IsDBNull(ssnOrdinal) ? reader.GetString(ssnOrdinal) : null;
DateTime dob = !reader.IsDBNull(dobOrdinal)
? reader.GetDateTime(dobOrdinal)
: new DateTime(2001, 1, 1);
string sex = !reader.IsDBNull(sexOrdinal) ? reader.GetString(sexOrdinal) : null;
string address = !reader.IsDBNull(addressOrdinal) ? reader.GetString(addressOrdinal) : null;
string city = !reader.IsDBNull(cityOrdinal) ? reader.GetString(cityOrdinal) : null;
string state = !reader.IsDBNull(stateOrdinal) ? reader.GetString(stateOrdinal) : null;
int zip = !reader.IsDBNull(zipOrdinal) ? reader.GetInt32(zipOrdinal) : 0;
string phone = !reader.IsDBNull(phoneOrdinal) ? reader.GetString(phoneOrdinal) : null;
patient = new Patient()
{
Id = _id,
FirstName = _fname,
LastName = _lname,
SSN = ssn,
DOB = dob,
Sex = sex,
Address = address,
City = city,
State = state,
Zip = zip,
Phone = phone
};
}
}
}
conn.Close();
return(patient);
}
/// <summary>
/// Fetches a list of products transactions sold.
/// </summary>
/// <param name="productID">
/// Passes a productID as a parameter
/// </param>
/// <returns>
/// <para>Returs a list of all the products that are sold</para>
/// <para>Type: List<QuantitySoldModel></para>
/// </returns>
public async Task <List <QuantitySoldModel> > FetchQuantitySoldOnsiteAsync(int productID)
{
List <QuantitySoldModel> quantitySoldModels = new List <QuantitySoldModel>();
using (MySqlConnection connection = new MySqlConnection(DbConnString.DBCONN_STRING))
{
await connection.OpenAsync();
string queryString = "SELECT products_table.product_name as ProductName, " +
"products_table.product_price as ProductPrice, " +
"products_table.product_id as ProductID, " +
"sales_table.sale_type as SaleType, " +
"sales_table.date as Date, " +
"sales_table.user_username as Administrator, " +
"onsite_products_transaction_table.total_product_count as ProductCount " +
"FROM products_table " +
"INNER JOIN onsite_products_transaction_table ON onsite_products_transaction_table.product_id = products_table.product_id " +
"INNER JOIN sales_table ON sales_table.onsite_transaction_id = onsite_products_transaction_table.transaction_id WHERE products_table.product_id = @productID AND sales_table.sale_type = 'Onsite'";
MySqlCommand command = new MySqlCommand(queryString, connection);
command.Parameters.AddWithValue("@productID", productID);
MySqlDataReader reader = (MySqlDataReader)await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
var quantitySoldModel = new QuantitySoldModel();
quantitySoldModel.ProductName = null;
quantitySoldModel.ProductPrice = 0;
quantitySoldModel.ProductID = -1;
quantitySoldModel.Date = null;
quantitySoldModel.Administrator = "Empty";
quantitySoldModel.TotalSale = 0;
quantitySoldModel.SaleType = "Empty";
quantitySoldModel.ProductCount = 0;
if (reader["ProductName"] != DBNull.Value)
{
quantitySoldModel.ProductName = reader["ProductName"].ToString();
}
if (reader["ProductPrice"] != DBNull.Value)
{
quantitySoldModel.ProductPrice = int.Parse(reader["ProductPrice"].ToString());
}
if (reader["ProductID"] != DBNull.Value)
{
quantitySoldModel.ProductID = int.Parse(reader["ProductID"].ToString());
}
if (reader["Date"] != DBNull.Value)
{
quantitySoldModel.Date = DateTime.Parse(reader["Date"].ToString());
}
if (reader["Administrator"] != DBNull.Value)
{
quantitySoldModel.Administrator = reader["Administrator"].ToString();
}
if (reader["SaleType"] != DBNull.Value)
{
quantitySoldModel.SaleType = reader["SaleType"].ToString();
}
if (reader["ProductCount"] != DBNull.Value)
{
quantitySoldModel.ProductCount = int.Parse(reader["ProductCount"].ToString());
}
quantitySoldModels.Add(quantitySoldModel);
}
return(quantitySoldModels);
}
}
public async Task <List <Patient> > SearchPatient(string dobinput, string fnameinput, string lnameinput)
{
try
{
var allPatients = new List <Patient>();
DateTime?dt;
if (string.IsNullOrEmpty(dobinput) || string.IsNullOrWhiteSpace(dobinput))
{
dt = null;
}
else
{
dt = Convert.ToDateTime(dobinput).Date;
}
using (MySqlConnection conn = DbConnection.GetConnection())
{
//Open the connection
System.Text.EncodingProvider ppp;
ppp = System.Text.CodePagesEncodingProvider.Instance;
Encoding.RegisterProvider(ppp);
conn.Open();
string query = "select id, fname, lname, ssn, dob, sex, address, city, state, zip, phone from `patient` ";
if (dt != null && !(string.IsNullOrEmpty(fnameinput) || string.IsNullOrWhiteSpace(fnameinput)) && !(string.IsNullOrEmpty(lnameinput) || string.IsNullOrWhiteSpace(lnameinput)))
{
query += "where `dob`=@dob and `fname`=@fname and `lname`=@lname;";
}
else if (dt != null && !(string.IsNullOrEmpty(fnameinput) || string.IsNullOrWhiteSpace(fnameinput)) && (string.IsNullOrEmpty(lnameinput) || string.IsNullOrWhiteSpace(lnameinput)))
{
query += "where `dob`=@dob and `fname`=@fname;";
}
else if (dt != null && (string.IsNullOrEmpty(fnameinput) || string.IsNullOrWhiteSpace(fnameinput)) && !(string.IsNullOrEmpty(lnameinput) || string.IsNullOrWhiteSpace(lnameinput)))
{
query += "where `dob`=@dob and `lname`=@lname;";
}
else if (dt == null && !(string.IsNullOrEmpty(fnameinput) || string.IsNullOrWhiteSpace(fnameinput)) && !(string.IsNullOrEmpty(lnameinput) || string.IsNullOrWhiteSpace(lnameinput)))
{
query += "where `fname`=@fname and `lname`=@lname;";
}
else if (dt != null && (string.IsNullOrEmpty(fnameinput) || string.IsNullOrWhiteSpace(fnameinput)) && (string.IsNullOrEmpty(lnameinput) || string.IsNullOrWhiteSpace(lnameinput)))
{
query += "where `dob`=@dob;";
}
else if (dt == null && !(string.IsNullOrEmpty(fnameinput) || string.IsNullOrWhiteSpace(fnameinput)) && (string.IsNullOrEmpty(lnameinput) || string.IsNullOrWhiteSpace(lnameinput)))
{
query += "where `fname`=@fname;";
}
else if (dt == null && (string.IsNullOrEmpty(fnameinput) || string.IsNullOrWhiteSpace(fnameinput)) && !(string.IsNullOrEmpty(lnameinput) || string.IsNullOrWhiteSpace(lnameinput)))
{
query += "where `lname`=@lname;";
}
else
{
query += "";
}
//DateTime dt;
using (MySqlCommand comm = new MySqlCommand(query, conn))
{
comm.Parameters.Add("@dob", (DbType)MySqlDbType.DateTime).Value = dt;
comm.Parameters.Add("@fname", (DbType)MySqlDbType.VarChar).Value = fnameinput;
comm.Parameters.Add("@lname", (DbType)MySqlDbType.VarChar).Value = lnameinput;
using (var reader = await comm.ExecuteReaderAsync())
{
int idOrdinal = reader.GetOrdinal("id");
int fnameOrdinal = reader.GetOrdinal("fname");
int lnameOrdinal = reader.GetOrdinal("lname");
int ssnOrdinal = reader.GetOrdinal("ssn");
int dobOrdinal = reader.GetOrdinal("dob");
int sexOrdinal = reader.GetOrdinal("sex");
int addressOrdinal = reader.GetOrdinal("address");
int cityOrdinal = reader.GetOrdinal("city");
int stateOrdinal = reader.GetOrdinal("state");
int zipOrdinal = reader.GetOrdinal("zip");
int phoneOrdinal = reader.GetOrdinal("phone");
while (await reader.ReadAsync())
{
int id = !reader.IsDBNull(idOrdinal) ? reader.GetInt32(idOrdinal) : 1;
string fname = !reader.IsDBNull(fnameOrdinal) ? reader.GetString(fnameOrdinal) : null;
string lname = !reader.IsDBNull(lnameOrdinal) ? reader.GetString(lnameOrdinal) : null;
string ssn = !reader.IsDBNull(ssnOrdinal) ? reader.GetString(ssnOrdinal) : null;
DateTime dob = !reader.IsDBNull(dobOrdinal) ? reader.GetDateTime(dobOrdinal).Date : new DateTime(2001, 1, 1).Date;
string sex = !reader.IsDBNull(sexOrdinal) ? reader.GetString(sexOrdinal) : null;
string address = !reader.IsDBNull(addressOrdinal) ? reader.GetString(addressOrdinal) : null;
string city = !reader.IsDBNull(cityOrdinal) ? reader.GetString(cityOrdinal) : null;
string state = !reader.IsDBNull(stateOrdinal) ? reader.GetString(stateOrdinal) : null;
int zip = !reader.IsDBNull(zipOrdinal) ? reader.GetInt32(zipOrdinal) : 0;
string phone = !reader.IsDBNull(phoneOrdinal) ? reader.GetString(phoneOrdinal) : null;
var patient = new Patient
{
Id = id,
FirstName = fname,
LastName = lname,
SSN = ssn,
DOB = dob,
Sex = sex,
Address = address,
City = city,
State = state,
Zip = zip,
Phone = phone
};
allPatients.Add(patient);
}
}
}
}
return(allPatients);
} catch (Exception)
{
var dialog = new MessageDialog("Please enter a valid Date: YYYY-MM-DD");
await dialog.ShowAsync();
return(await this.GetAllPatients());
}
}
public async Task <BookUserMood> SaveBookUserMood(BookUserMood bookUserMood)
{
MySqlConnection connection = GetConnection();
await connection.OpenAsync();
MySqlCommand bookCommand = new MySqlCommand("SELECT * FROM Book WHERE bookID = @bookID", connection);
bookCommand.Parameters.AddWithValue("@bookID", bookUserMood.BookID);
var bookReader = await bookCommand.ExecuteReaderAsync();
if (bookReader.HasRows)
{
bookReader.Close();
await connection.CloseAsync();
connection = GetConnection();
await connection.OpenAsync();
var userCommand = new MySqlCommand("SELECT * FROM DBUser WHERE userID = @userID", connection);
userCommand.Parameters.AddWithValue("@userID", bookUserMood.UserID);
var userReader = await userCommand.ExecuteReaderAsync();
if (userReader.HasRows)
{
userReader.Close();
await connection.CloseAsync();
connection = GetConnection();
await connection.OpenAsync();
var moodCommand = new MySqlCommand("SELECT * FROM Mood WHERE moodID = @moodID", connection);
moodCommand.Parameters.AddWithValue("@moodID", bookUserMood.MoodID);
var moodReader = await moodCommand.ExecuteReaderAsync();
if (moodReader.HasRows)
{
moodReader.Close();
await connection.CloseAsync();
connection = GetConnection();
await connection.OpenAsync();
var checkCommand = new MySqlCommand("SELECT* FROM BookUserMood WHERE bookID = @bookID AND userID = @userID", connection);
checkCommand.Parameters.AddWithValue("@bookID", bookUserMood.BookID);
checkCommand.Parameters.AddWithValue("@userID", bookUserMood.UserID);
var checkReader = await checkCommand.ExecuteReaderAsync();
if (checkReader.HasRows)
{
checkReader.Close();
await connection.CloseAsync();
connection = GetConnection();
await connection.OpenAsync();
var updateCommand = new MySqlCommand("UPDATE BookUserMood SET bookID = @bookID, userID = @userID, moodID = @moodID WHERE bookID = @bookID AND userID = @userID", connection);
updateCommand.Parameters.AddWithValue("@bookID", bookUserMood.BookID);
updateCommand.Parameters.AddWithValue("@userID", bookUserMood.UserID);
updateCommand.Parameters.AddWithValue("@moodID", bookUserMood.MoodID);
await updateCommand.ExecuteNonQueryAsync();
}
else
{
checkReader.Close();
await connection.CloseAsync();
connection = GetConnection();
await connection.OpenAsync();
var insertCommand = new MySqlCommand("INSERT INTO BookUserMood(bookID, userID, moodID) VALUES (@bookID, @userID, @moodID)", connection);
insertCommand.Parameters.AddWithValue("@bookID", bookUserMood.BookID);
insertCommand.Parameters.AddWithValue("@userID", bookUserMood.UserID);
insertCommand.Parameters.AddWithValue("@moodID", bookUserMood.MoodID);
await insertCommand.ExecuteNonQueryAsync();
}
}
else
{
moodReader.Close();
throw new MoodNotFoundException("Mood not found in database");
}
}
else
{
userReader.Close();
throw new UserNotFoundException("User not found in database");
}
}
else
{
bookReader.Close();
throw new BookNotFoundException("Book not found in database");
}
await connection.CloseAsync();
return(bookUserMood);
}
public static async Task <System.Data.Common.DbDataReader> TryExecutingSelectQueryDataReader(MySqlConnection connection, DbCommand <T> command, T entity = default(T))
{
MySqlCommand mySqlCommand = command.GetCommand(connection, (new T() as IDbTableAssociate).GetAssociatedDbTableName(), entity);
return(await mySqlCommand.ExecuteReaderAsync());
}
public static async Task PostGlobalMessageAsync(SocketCommandContext Context)
{
ulong user_id = Context.User.Id;
string user_name = Context.User.Username;
string user_rank = "";
string user_server = Context.Guild.Name;
string user_image = Context.User.GetAvatarUrl();
string message_text = Context.Message.Content;
IReadOnlyCollection <SocketUser> message_mentions = Context.Message.MentionedUsers;
string message_channel = "";
string message_footer = Context.Message.Timestamp.ToString();
IReadOnlyCollection <Attachment> message_attachments = Context.Message.Attachments;
List <string> message_images = new List <string>();
foreach (Attachment attachment in message_attachments)
{
if (attachment.Filename.EndsWith(".png") || attachment.Filename.EndsWith(".jpg") || attachment.Filename.EndsWith(".jpeg") || attachment.Filename.EndsWith(".gif"))
{
message_images.Add(Image.SaveImage(attachment.Filename, attachment.Url));
Console.WriteLine(Image.SaveImage(attachment.Filename, attachment.Url));
}
}
//if (message_text.Length == 0) message_text = "_Find attachment above._";
string globals_id = "";
var dbCon = DBConnection.Instance();
dbCon.DatabaseName = BotConfig.Load().DatabaseName;
if (dbCon.IsConnect())
{
message_channel = await GetGlobalChannelInUseAsync(Context.Guild.Id, Context.Channel.Id, dbCon);
if (!message_channel.Equals(""))
{
// Check the user exists
await UserProfile.CheckUserAsync(Context.User.Id, dbCon);
// Check user rank
user_rank = await UserProfile.GetGroupAsync(Context.User.Id, dbCon);
// Get their globals id
globals_id = await UserProfile.GetGlobalsIdAsync(Context.User.Id, dbCon);
// Save the message in the db
string query = "INSERT INTO global_messages (user_id, user_name, user_server, user_image, message_text, message_channel, message_footer) " +
"VALUES(@user_id, @user_name, @user_server, @user_image, @message_text, @message_channel, @message_footer);";
var cmd = new MySqlCommand(query, dbCon.Connection);
cmd.Parameters.Add("@user_id", MySqlDbType.UInt64).Value = user_id;
cmd.Parameters.Add("@user_name", MySqlDbType.String).Value = user_name;
cmd.Parameters.Add("@user_server", MySqlDbType.String).Value = user_server;
cmd.Parameters.Add("@user_image", MySqlDbType.String).Value = user_image;
cmd.Parameters.Add("@message_text", MySqlDbType.String).Value = message_text;
cmd.Parameters.Add("@message_channel", MySqlDbType.String).Value = message_channel;
cmd.Parameters.Add("@message_footer", MySqlDbType.String).Value = globals_id + " | " + message_footer;
try
{
await cmd.ExecuteNonQueryAsync();
await Context.Message.DeleteAsync();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
// Post the messages everywhere
if (!message_channel.Equals(""))
{
var embed = new EmbedBuilder()
{
Color = new Color(114, 137, 218)
};
embed.WithAuthor(user_name + "#" + Context.User.Discriminator + " from " + user_server, user_image);
embed.WithDescription(message_text);
embed.WithFooter(user_rank + " - " + globals_id + " | " + message_footer);
string query = "SELECT * FROM server_configs;";
var cmd = new MySqlCommand(query, dbCon.Connection);
var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
if (!user_rank.ToLower().Equals("blacklisted"))
{
await PostToChannelAsync(message_channel, reader, embed, message_images, message_mentions);
}
}
reader.Close();
cmd.Dispose();
}
dbCon.Close();
}
else
{
Console.WriteLine("Couldnt connect...");
}
}
// core methods
public async static Task <Boolean> Register(Int32 catagory_id,
String name,
String description,
Image image) // For Registering New Product
{
try
{
// Check if Entry with Catagory ID and Name already exists
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = Program.conn;
String sql = "SELECT * FROM `product` WHERE `name` = @name";
cmd.Parameters.AddWithValue("@name", name);
if (catagory_id != 0)
{
sql += " AND `catagory_id` = @catagory_id";
cmd.Parameters.AddWithValue("@catagory_id", catagory_id);
}
else
{
sql += " AND `catagory_id` = '0' OR `catagory_id` is NULL";
}
sql += " LIMIT 1";
cmd.CommandText = sql;
DbDataReader reader = await cmd.ExecuteReaderAsync();
cmd.Dispose();
if (await reader.ReadAsync())
{
CUtils.LastLogMsg = "Product Already Exists!";
if (!reader.IsClosed)
{
reader.Close();
}
return(false);
}
if (!reader.IsClosed)
{
reader.Close();
}
// Insert new record / Register in Product Table
MemoryStream memStream = new MemoryStream();
image.Save(memStream, image.RawFormat);
sql = "INSERT INTO `product` " +
"(`catagory_id`, `name`, `description`, `image`) VALUES" +
"(@catagory_id, @name, @description, @image)";
cmd = new MySqlCommand(sql, Program.conn);
cmd.Parameters.AddWithValue("@catagory_id", catagory_id);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@image", memStream.GetBuffer());
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
CUtils.LastLogMsg = null;
}
catch (Exception ex)
{
#if DEBUG
Console.WriteLine(ex.Message + " " + ex.StackTrace);
#endif
CUtils.LastLogMsg = "Unahandled Exception!";
return(false);
}
return(true);
}
// core methods
public async static Task <Boolean> Register(Int32 customer_id,
Int32 product_id,
Int32 seller_id,
Int32 quantity) // For Registering New Inventory
{
try
{
// Check if Entry with Seller ID and Product ID already exists in Inventory
String sql = "SELECT * FROM `customer_cart` WHERE `customer_id` = @customer_id and `product_id` = @product_id and `seller_id` = @seller_id LIMIT 1";
MySqlCommand cmd = new MySqlCommand(sql, Program.conn);
cmd.Parameters.AddWithValue("@customer_id", customer_id);
cmd.Parameters.AddWithValue("@product_id", product_id);
cmd.Parameters.AddWithValue("@seller_id", seller_id);
DbDataReader reader = await cmd.ExecuteReaderAsync();
cmd.Dispose();
if (await reader.ReadAsync())
{
if (!reader.IsClosed)
{
reader.Close();
}
CUtils.LastLogMsg = "Cart Already Exists!";
return(false);
}
if (!reader.IsClosed)
{
reader.Close();
}
// Insert new record / Register in Inventory Table
sql = "INSERT INTO `customer_cart` " +
"(`customer_id`, `product_id`, `seller_id`, `quantity`) VALUES" +
"(@customer_id, @product_id, @seller_id, @quantity)";
cmd = new MySqlCommand(sql, Program.conn);
cmd.Parameters.AddWithValue("@customer_id", customer_id);
cmd.Parameters.AddWithValue("@product_id", product_id);
cmd.Parameters.AddWithValue("@seller_id", seller_id);
cmd.Parameters.AddWithValue("@quantity", quantity);
await cmd.ExecuteNonQueryAsync();
cmd.Dispose();
// Update Quantity in Inventory
MySqlCommand tempcmd = new MySqlCommand();
tempcmd.Connection = Program.conn;
tempcmd.CommandText = "UPDATE `seller_inventory` SET quantity = quantity - @quantity WHERE seller_id = @seller_id AND product_id = @product_id";
tempcmd.Parameters.AddWithValue("@quantity", quantity);
tempcmd.Parameters.AddWithValue("@seller_id", seller_id);
tempcmd.Parameters.AddWithValue("@product_id", product_id);
await tempcmd.ExecuteNonQueryAsync();
tempcmd.Dispose();
// Update Quantity in Product
tempcmd = new MySqlCommand();
tempcmd.Connection = Program.conn;
tempcmd.CommandText = "UPDATE `product` SET quantity = quantity - @quantity WHERE id = @product_id";
tempcmd.Parameters.AddWithValue("@quantity", quantity);
tempcmd.Parameters.AddWithValue("@product_id", product_id);
await tempcmd.ExecuteNonQueryAsync();
tempcmd.Dispose();
// Execute Procedure to Calculate Minprice
tempcmd = new MySqlCommand();
tempcmd.Connection = Program.conn;
tempcmd.CommandText = "proc_UpdateMinPrice";
tempcmd.CommandType = System.Data.CommandType.StoredProcedure;
tempcmd.Parameters.AddWithValue("@p_product_id", product_id);
tempcmd.Parameters["@p_product_id"].Direction = System.Data.ParameterDirection.Input;
await tempcmd.ExecuteNonQueryAsync();
tempcmd.Dispose();
CUtils.LastLogMsg = null;
}
catch (Exception ex)
{
#if DEBUG
Console.WriteLine(ex.Message + " " + ex.StackTrace);
#endif
CUtils.LastLogMsg = "Unahandled Exception!";
return(false);
}
return(true);
}
private async Task <List <ServicesConfig.ResponseData.GetAnswerInterview> > GetInteviewerDetailAsync(long answerID)
{
List <ServicesConfig.ResponseData.GetAnswerInterview> answerList = new List <ServicesConfig.ResponseData.GetAnswerInterview>();
try
{
using (MySqlConnection conn = ServicesConfig.DatabaseService.Connection())
{
ServicesConfig.ResponseData.GetAnswerInterview getAnswerInterview;
mySqlCommand = new MySqlCommand();
mySqlCommand.Connection = conn;
mySqlCommand.CommandText = "spINFINET_GetAnswersUser";
mySqlCommand.CommandType = CommandType.StoredProcedure;
mySqlCommand.CommandTimeout = 30;
mySqlCommand.Parameters.Add("p_AnswersID", MySqlDbType.Int64).Value = answerID.ToString();
var mySqlDataReader = (MySqlDataReader)await mySqlCommand.ExecuteReaderAsync();
while (await mySqlDataReader.ReadAsync())
{
getAnswerInterview = new ServicesConfig.ResponseData.GetAnswerInterview();
if (mySqlDataReader["NamaLengkap"] != DBNull.Value)
{
getAnswerInterview.NamaLengkap = mySqlDataReader["NamaLengkap"].ToString();
}
if (mySqlDataReader["NomorTelepon"] != DBNull.Value)
{
getAnswerInterview.NomorTelepon = mySqlDataReader["NomorTelepon"].ToString();
}
if (mySqlDataReader["Divisi"] != DBNull.Value)
{
getAnswerInterview.Divisi = mySqlDataReader["Divisi"].ToString();
}
if (mySqlDataReader["AnswersOne"] != DBNull.Value)
{
getAnswerInterview.AnswerOne = mySqlDataReader["AnswersOne"].ToString();
}
if (mySqlDataReader["AnswersTwo"] != DBNull.Value)
{
getAnswerInterview.AnswerTwo = mySqlDataReader["AnswersTwo"].ToString();
}
if (mySqlDataReader["AnswersThree"] != DBNull.Value)
{
getAnswerInterview.AnswerThree = mySqlDataReader["AnswersThree"].ToString();
}
if (mySqlDataReader["AnswersFourth"] != DBNull.Value)
{
getAnswerInterview.AnswerFourth = mySqlDataReader["AnswersFourth"].ToString();
}
if (mySqlDataReader["AnswersFive"] != DBNull.Value)
{
getAnswerInterview.AnswerFive = mySqlDataReader["AnswersFive"].ToString();
}
if (mySqlDataReader["AnswersSix"] != DBNull.Value)
{
getAnswerInterview.AnswerSix = mySqlDataReader["AnswersSix"].ToString();
}
if (mySqlDataReader["AnswersSeven"] != DBNull.Value)
{
getAnswerInterview.AnswerSeven = mySqlDataReader["AnswersSeven"].ToString();
}
if (mySqlDataReader["AnswersEight"] != DBNull.Value)
{
getAnswerInterview.AnswerEight = mySqlDataReader["AnswersEight"].ToString();
}
if (mySqlDataReader["AnswersNine"] != DBNull.Value)
{
getAnswerInterview.AnswerNine = mySqlDataReader["AnswersNine"].ToString();
}
answerList.Add(getAnswerInterview);
}
}
}
catch (MySqlException myEx)
{
Response.Write(myEx.Message);
}
catch (Exception Ex)
{
Response.Write(Ex.Message);
}
return(answerList.ToList());
}
public async Task Stats()
{
var pInfo = new ProcessStartInfo()
{
FileName = "bash",
UseShellExecute = false,
RedirectStandardOutput = true,
Arguments = $"{Directory.GetCurrentDirectory()}/BashScripts/ServerStats.sh"
};
Process bashProcess = new Process()
{
StartInfo = pInfo
};
bashProcess.Start();
string strOut = await bashProcess.StandardOutput.ReadToEndAsync();
bashProcess.WaitForExit();
var osName = "Windows ";
if (RuntimeInformation.IsOSPlatform(OSPlatform.Linux))
{
osName = "Linux ";
}
else if (RuntimeInformation.IsOSPlatform(OSPlatform.OSX))
{
osName = "OSX ";
}
osName = $"{osName}{Enum.GetName(typeof(Architecture), RuntimeInformation.OSArchitecture)}";
var libVersion = typeof(DiscordSocketClient).GetTypeInfo().Assembly.GetCustomAttribute <AssemblyInformationalVersionAttribute>().InformationalVersion;
string embedMessage = $"OS: {osName}\nLibrary Version: Discord.Net - {libVersion}\n{strOut}";
Regex titleRegex = new Regex(@"[A-Za-z].+\:");
foreach (Match match in titleRegex.Matches(embedMessage))
{
embedMessage = embedMessage.Replace(match.Value, $"{match.Value.Bold()}");
}
using (BotDBContext DBContext = DBFactory.Create <BotDBContext>())
using (MySqlConnection conn = new MySqlConnection(DBContext.ConnectionString))
{
MySqlCommand mySQLCommand = new MySqlCommand(File.ReadAllText("SQLScripts/dbsize.txt"), conn);
mySQLCommand.CommandType = System.Data.CommandType.Text;
double totalSize = 0;
await conn.OpenAsync();
using (var rowReader = await mySQLCommand.ExecuteReaderAsync())
{
while (await rowReader.ReadAsync())
{
totalSize += double.Parse(rowReader[4].ToString());
}
}
conn.Close();
embedMessage += $"**Database Size**: {totalSize}MB";
}
await ReplyAsync("", embed : EmbedService.MakeFeedbackEmbed(embedMessage));
bashProcess.Dispose();
}
/// <summary>
/// Copies table data from the current SqlAgent instance to the target SqlAgent instance.
/// </summary>
/// <param name="schema">a schema of the database to copy the data</param>
/// <param name="targetManager">the target Sql schema manager to copy the data to</param>
/// <remarks>Required for <see cref="SqlAgentBase.CloneDatabase">CloneDatabase</see> infrastructure.
/// Basicaly iterates tables, selects data, creates an IDataReader for the table and passes it to the
/// <see cref="InsertTableData">InsertTableData</see> method of the target SqlAgent.</remarks>
protected override async Task CopyData(DbSchema schema, SchemaManagerBase targetManager,
IProgress <DbCloneProgressArgs> progress, CancellationToken ct)
{
if (schema.IsNull())
{
throw new ArgumentNullException(nameof(schema));
}
if (targetManager.IsNull())
{
throw new ArgumentNullException(nameof(targetManager));
}
var currentStatement = string.Empty;
using (var conn = await MyAgent.OpenConnectionAsync().ConfigureAwait(false))
{
try
{
using (var command = new MySqlCommand())
{
command.Connection = conn;
command.CommandTimeout = Agent.QueryTimeOut;
progress?.Report(new DbCloneProgressArgs(DbCloneProgressArgs.Stage.FetchingRowCount, string.Empty, 0));
long totalRowCount = 0;
foreach (var table in schema.Tables)
{
currentStatement = string.Format("SELECT COUNT(*) FROM {0};", table.Name.Trim());
command.CommandText = currentStatement;
totalRowCount += (long)await command.ExecuteScalarAsync().ConfigureAwait(false);
if (CloneCanceled(progress, ct))
{
return;
}
}
long currentRow = 0;
int currentProgress = 0;
foreach (var table in schema.Tables)
{
var fields = string.Join(", ", table.Fields.Select(
field => field.Name.ToConventional(Agent)).ToArray());
currentStatement = string.Format("SELECT {0} FROM {1};", fields, table.Name.ToConventional(Agent));
command.CommandText = currentStatement;
using (var reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
currentRow = await CallInsertTableDataAsync(targetManager, table, reader,
totalRowCount, currentRow, currentProgress, progress, ct).ConfigureAwait(false);
if (currentRow < 0)
{
break;
}
if (totalRowCount > 0)
{
currentProgress = (int)(100 * currentRow / (double)totalRowCount);
}
}
}
}
}
catch (Exception ex)
{
MyAgent.LogAndThrowInt(ex.WrapSqlException(currentStatement));
}
finally
{
if (conn != null)
{
if (conn.State != ConnectionState.Closed)
{
try { await conn.CloseAsync().ConfigureAwait(false); }
catch (Exception) { }
}
try { conn.Dispose(); }
catch (Exception) { }
}
}
}
}
public async Task <bool> VerificarUsuario(string usuario, string senha, string tipo_acesso)
{
conFuncionario.ConnectionString = Dados.strConexao;
Funcionario funcionario = new Funcionario();
string hashTxtSenha = null;
try
{
using (var sqlConnection = conFuncionario as MySqlConnection)
{
MySqlTransaction transaction;
await sqlConnection.OpenAsync();
transaction = sqlConnection.BeginTransaction(IsolationLevel.Serializable);
using (MySqlCommand command = new MySqlCommand("autenticar_login", sqlConnection, transaction))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new MySqlParameter("nome_usuario", usuario));
command.Parameters.Add(new MySqlParameter("tipo_acesso", tipo_acesso));
using (var reader = await command.ExecuteReaderAsync())
{
if (await reader.ReadAsync())
{
funcionario.Senha = reader["fun_senha"] as string; //
funcionario.Usuario = reader["fun_usuario"] as string; //
funcionario.Tipo = reader["fun_tipo"] as string;
hashTxtSenha = hash.CriptografarSenha(senha);
}
else
{
return(false);
}
}
transaction.Commit();
if (hash.VerificarSenha(hashTxtSenha, funcionario.Senha))
{
return(true);
}
return(false);
}
}
}
catch (InvalidOperationException e)
{
throw new InvalidOperationException("Problemas ao se conectar com o banco.", e);
}
catch (SqlException)
{
throw;
}
catch (Exception e)
{
throw new Exception("Problemas para verificar usuário.", e);
}
}
public async Task <ClanSave> LoadClanAsync(long clanId, CancellationToken cancellationToken)
{
if (clanId < 1)
{
throw new ArgumentOutOfRangeException(nameof(clanId));
}
// If the clan is already in the cache, we return that instance.
var clanSave = default(ClanSave);
if (Server.Cache.TryGet(clanId, out clanSave))
{
return(clanSave);
}
// Otherwise we query the db to find the clan save.
using (var sql = new MySqlConnection(_connectionString))
{
await sql.OpenAsync(cancellationToken);
// Search for clan with specified clanId.
using (var command = new MySqlCommand("SELECT * FROM `clans` WHERE `clan_id` = @ClanId", sql))
{
command.Parameters.AddWithValue("ClanId", clanId);
using (var reader = await command.ExecuteReaderAsync(CommandBehavior.SingleRow, cancellationToken))
{
// If the query did not return any rows, it means the clan does not exists.
// So we return null;
if (!reader.HasRows)
{
return(null);
}
else
{
if (await reader.ReadAsync())
{
clanSave = Server.Factories.GetFactory <ClanSaveFactory>().Create();
FromMySqlDataReader(clanSave, reader);
}
}
}
}
var shouldDelete = false;
var members = new List <ClanMember>(4);
// Look for entries in the clans_members table which has the same clan_id.
using (var command = new MySqlCommand("SELECT * FROM `clan_members` WHERE `clan_id` = @ClanId", sql))
{
command.Parameters.AddWithValue("ClanId", clanSave.ClanId);
using (var reader = await command.ExecuteReaderAsync(cancellationToken))
{
if (!reader.HasRows)
{
shouldDelete = true;
}
else
{
while (await reader.ReadAsync(cancellationToken))
{
var member = new ClanMember();
FromMySqlDataReader(member, reader);
var level = await Server.Db.LoadLevelAsync(member.Id, cancellationToken);
member.Name = level.Name;
member.League = level.League;
member.Trophies = level.Trophies;
member.ExpLevels = level.ExpLevels;
members.Add(member);
}
clanSave.Members = members;
}
}
}
if (shouldDelete)
{
using (var command = new MySqlCommand("DELETE FROM `clans` WHERE `clan_id` = @ClanId", sql))
{
command.Parameters.AddWithValue("ClanId", clanSave.ClanId);
var numRows = await command.ExecuteNonQueryAsync(cancellationToken);
if (numRows != 1)
{
Server.Logs.Warn($"Tried to delete an empty clan however, number of rows affected was '{numRows}'.");
}
Server.Cache.Unregister <ClanSave>(clanId);
return(null);
}
}
// Register this instance to our cache.
Server.Cache.Register(clanId, clanSave);
return(clanSave);
}
}
public async Task BotGuildAdded(GuildCreateEventArgs e)
{
Console.WriteLine(e.Guild.Name);
await connection.OpenAsync();
try
{
MySqlCommand guildcmd = new MySqlCommand
{
Connection = connection,
CommandText = $"INSERT INTO `guilds` (`guildID`, `guildName`, `guildDefaultInvite`, `guildOwner`) VALUES (?, ?, ?, NULL, ?) ON DUPLICATE KEY UPDATE guildOwner=guildOwner"
};
guildcmd.Parameters.Add("guildID", MySqlDbType.UInt64).Value = e.Guild.Id;
guildcmd.Parameters.Add("guildID", MySqlDbType.Int64).Value = e.Guild.Id;
guildcmd.Parameters.Add("guildName", MySqlDbType.VarChar).Value = e.Guild.Name;
guildcmd.Parameters.Add("guildOwner", MySqlDbType.Int64).Value = e.Guild.Owner.Id;
await guildcmd.ExecuteNonQueryAsync();
GuildsList.Add(e.Guild.Id, new Guilds
{
GuildName = e.Guild.Name,
GuildDefaultInvite = null,
GuildOwner = e.Guild.Owner.Id,
GuildMembers = new Dictionary <ulong, Members>(),
ChannelConfig = new ChannelConfig(),
ModuleConfig = new ModuleConfig()
});
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
Console.WriteLine(ex.StackTrace);
} //Guild Top
try
{
MySqlCommand guildcmd = new MySqlCommand
{
Connection = connection,
CommandText = $"INSERT INTO `guilds.config` (`guildID`, `ruleChannelID`, `infoChannelID`, `cmdChannelID`, `logChannelID`, `roleID`, `customInfo`) VALUES (?, 0, 0, 0, 0, 0, ?) ON DUPLICATE KEY UPDATE ruleChannelID=ruleChannelID"
};
guildcmd.Parameters.Add("guildID", MySqlDbType.Int64).Value = e.Guild.Id;
guildcmd.Parameters.Add("customInfo", MySqlDbType.VarChar).Value = "to be filled";
await guildcmd.ExecuteNonQueryAsync();
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
Console.WriteLine(ex.StackTrace);
} //Guild Chan Conf
await connection.CloseAsync();
await connection.OpenAsync();
try
{
MySqlCommand guildcmd = new MySqlCommand
{
Connection = connection,
CommandText = $"SELECT * FROM `guilds.config` WHERE `guildID` = {e.Guild.Id}"
};
var reader = await guildcmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
GuildsList[e.Guild.Id].ChannelConfig = (new ChannelConfig
{
RuleChannelID = Convert.ToUInt64(reader["ruleChannelId"]),
InfoChannelID = Convert.ToUInt64(reader["infoChannelId"]),
CmdChannelID = Convert.ToUInt64(reader["cmdChannelId"]),
LogChannelID = Convert.ToUInt64(reader["logChannelID"]),
RoleID = Convert.ToUInt64(reader["roleID"]),
CustomInfo = reader["customInfo"].ToString()
});
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
Console.WriteLine(ex.StackTrace);
} //Guild Channel Config Get
try
{
MySqlCommand guildcmd = new MySqlCommand
{
Connection = connection,
CommandText = $"INSERT INTO `modules.config` (`guildID`, `adminModule`, `greetModule`, `birthdayModule`) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE adminModule=adminModule"
};
guildcmd.Parameters.Add("guildID", MySqlDbType.Int64).Value = e.Guild.Id;
guildcmd.Parameters.Add("adminModule", MySqlDbType.Int16).Value = 0;
guildcmd.Parameters.Add("greetModule", MySqlDbType.Int16).Value = 0;
guildcmd.Parameters.Add("birthdayModule", MySqlDbType.Int16).Value = 0;
await guildcmd.ExecuteNonQueryAsync();
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
Console.WriteLine(ex.StackTrace);
} //Module Conf
await connection.CloseAsync();
await connection.OpenAsync();
try
{
MySqlCommand guildcmd = new MySqlCommand
{
Connection = connection,
CommandText = $"SELECT * FROM `modules.config` WHERE `guildID` = {e.Guild.Id}"
};
var reader = await guildcmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
GuildsList[e.Guild.Id].ModuleConfig = new ModuleConfig
{
AdminModule = Convert.ToBoolean(reader["adminModule"]),
GreetModule = Convert.ToBoolean(reader["greetModule"]),
BirthdayModule = Convert.ToBoolean(reader["birthdayModule"])
};
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
Console.WriteLine(ex.StackTrace);
} //Guild Module Config Get
await connection.CloseAsync();
await connection.OpenAsync();
try
{
MySqlCommand guildcmd = new MySqlCommand
{
Connection = connection,
CommandText = $"SELECT * FROM `modules.greet` WHERE `guildID` = {e.Guild.Id}"
};
var reader = await guildcmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
GuildsList[e.Guild.Id].ModuleConfig.GreetMessages.Add(new GreetMessages
{
AnnounceString = reader["announceString"].ToString()
});
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
Console.WriteLine(ex.StackTrace);
} //Get Annouce
foreach (var emoji in e.Guild.Emojis)
{
try
{
MySqlCommand cmd = new MySqlCommand
{
Connection = connection,
CommandText = $"INSERT INTO `guilds.emotes` (`guildID`, `emoteID`, `emoteURL`, `emoteName`) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE emoteName=emoteName"
};
cmd.Parameters.Add("guildID", MySqlDbType.Int64).Value = e.Guild.Id;
cmd.Parameters.Add("emoteID", MySqlDbType.Int64).Value = emoji.Id;
cmd.Parameters.Add("emoteURL", MySqlDbType.VarChar).Value = emoji.Url;
cmd.Parameters.Add("emoteName", MySqlDbType.VarChar).Value = emoji.Name;
await cmd.ExecuteNonQueryAsync();
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
Console.WriteLine(ex.StackTrace);
}
}
foreach (var user in e.Guild.Members)
{
try
{
MySqlCommand cmd = new MySqlCommand
{
Connection = connection,
CommandText = $"INSERT INTO `guilds.users` (`userID`, `userName`, `userDiscriminator`, `Birthdate`, `changeDate`) VALUES (?, ?, ?, NULL, CURRENT_TIMESTAMP) ON DUPLICATE KEY UPDATE userName=userName"
};
cmd.Parameters.Add("userID", MySqlDbType.Int64).Value = user.Id;
cmd.Parameters.Add("userName", MySqlDbType.VarChar).Value = user.Username;
cmd.Parameters.Add("userDiscriminator", MySqlDbType.VarChar).Value = user.Discriminator;
await cmd.ExecuteNonQueryAsync();
GuildsList[e.Guild.Id].GuildMembers.Add(user.Id, new Members
{
UserName = user.Username,
UserDiscriminator = user.Discriminator,
BdaySent = false
});
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
Console.WriteLine(ex.StackTrace);
}
}
await connection.CloseAsync();
}
public async Task ExecuteReaderAsync()
{
if (st.Version < new Version(5, 0)) return;
if (st.conn.State != ConnectionState.Open)
st.conn.Open();
st.execSQL("CREATE TABLE CMDReaderAsyncTest (id int)");
st.execSQL("CREATE PROCEDURE CMDReaderAsyncSpTest() BEGIN INSERT INTO CMDReaderAsyncTest VALUES(1); SELECT SLEEP(2); SELECT 'done'; END");
MySqlCommand proc = new MySqlCommand("CMDReaderAsyncSpTest", st.conn);
proc.CommandType = CommandType.StoredProcedure;
using (MySqlDataReader reader = await proc.ExecuteReaderAsync() as MySqlDataReader)
{
Assert.NotNull(reader);
Assert.True(reader.Read(), "can read");
Assert.True(reader.NextResult());
Assert.True(reader.Read());
Assert.Equal("done", reader.GetString(0));
reader.Close();
proc.CommandType = CommandType.Text;
proc.CommandText = "SELECT COUNT(*) FROM CMDReaderAsyncTest";
object cnt = proc.ExecuteScalar();
Assert.Equal(1, Convert.ToInt32(cnt));
}
}