static public ulong GetAdminbotRoleID()
{
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
//create a channel and pass the stored procedure the channel ID
//Get the role ID for adminbot role on this current server,
//Which is used to grant adminbots access to the users private channel.
using (SqlCommand command = new SqlCommand("GetAdminbotRoleID", conn))
{
command.Parameters.Add(new SqlParameter("@current_server", (Int64)BotConfiguration.ServerID));
SqlParameter returnValue = new SqlParameter
{
SqlDbType = SqlDbType.BigInt,
ParameterName = "@result",
Direction = ParameterDirection.Output
};
command.Parameters.Add(returnValue);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
long returnval = (long)returnValue.Value;
return((ulong)returnval);
}
}
}
static public string GetRandomEmoji(string reaction_type)
{
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
using (SqlCommand command = new SqlCommand("GetRandomEmoji", conn))
{
SqlParameter returnValue = new SqlParameter("return_value", SqlDbType.NVarChar)
{
Size = 50,
Direction = ParameterDirection.Output
};
command.Parameters.Add(returnValue);
var returnDescription = new SqlParameter("return_description", SqlDbType.VarChar)
{
Size = 50,
Direction = ParameterDirection.Output
};
command.Parameters.Add(returnDescription);
command.Parameters.Add(new SqlParameter("@input_reaction_type", reaction_type));
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
Console.WriteLine("Emoji from database: " + (string)returnDescription.Value);
return((string)returnValue.Value);
}
/*string str = (string)returnValue.Value;
* string emoji = string.Join("", (from Match m in Regex.Matches(str, @"\S{4}")
* select (char)int.Parse(m.Value, NumberStyles.HexNumber)).ToArray());
*/
}
}
static public void ExecuteSql(string sql)
{
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.CommandType = CommandType.Text;
conn.Open();
command.ExecuteNonQuery();
}
}
}
static public void AddUserToTournament(int userID)
{
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
//Now that the private channel has been created, add it to the database.
using (SqlCommand command = new SqlCommand("AddUserToTournament", conn))
{
command.Parameters.Add(new SqlParameter("@input_user_id", userID));
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
}
}
}
static public void BumpPatch()
{
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
//Now that the private channel has been created, add it to the database.
using (SqlCommand command = new SqlCommand("BumpVersion", conn))
{
command.Parameters.Add(new SqlParameter("@selector", Convert.ToInt64(0)));
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
}
}
}
static public void AddPersonalRole(int userID, ulong newRoleID)
{
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
//Now that the private channel has been created, add it to the database.
using (SqlCommand command = new SqlCommand("AddPersonalRole", conn))
{
command.Parameters.Add(new SqlParameter("@input_user_id", (Int64)userID));
command.Parameters.Add(new SqlParameter("@input_role_id", (Int64)newRoleID));
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
}
}
}
static public ulong GetFolderID(string folderName)
{
/*
* ulong toReturn;
*
* SqlConnection conn = new SqlConnection(MySQLConnString.Get());
* SqlCommand command = new SqlCommand("GetFolderID", conn);
* command.Parameters.Add(new SqlParameter("@input_folder_name", folderName));
* command.Parameters.Add(new SqlParameter("@input_server_id", (Int64)BotConfiguration.ServerID));
* SqlParameter returnValue = new SqlParameter("@result", SqlDbType.BigInt)
* {
* Direction = ParameterDirection.Output
* };
* command.Parameters.Add(returnValue);
* command.CommandType = CommandType.StoredProcedure;
* conn.Open();
* command.ExecuteNonQuery();
*
* var returnval = (long)returnValue.Value;
* toReturn = (ulong)returnval;
* return toReturn;
*/
ulong toReturn;
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
using (SqlCommand command = new SqlCommand("GetFolderID", conn))
{
command.Parameters.Add(new SqlParameter("@input_folder_name", folderName));
command.Parameters.Add(new SqlParameter("@input_server_id", (Int64)BotConfiguration.ServerID));
SqlParameter returnValue = new SqlParameter("@result", SqlDbType.BigInt)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(returnValue);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
var returnval = (long)returnValue.Value;
toReturn = (ulong)returnval;
}
}
return(toReturn);
}
static public void GetConfiguration(string config_name)
{
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
SqlParameter param_token = new SqlParameter("@return_token", SqlDbType.NVarChar)
{
Direction = ParameterDirection.Output,
Size = 100
};
SqlParameter param_prefix = new SqlParameter("@return_prefix", SqlDbType.Char)
{
Direction = ParameterDirection.Output,
Size = 1
};
SqlParameter param_server_id = new SqlParameter("@return_server_id", SqlDbType.BigInt)
{
Direction = ParameterDirection.Output
};
using (SqlCommand command = new SqlCommand("GetConfiguration", conn))
{
command.Parameters.Add(param_token);
command.Parameters.Add(param_prefix);
command.Parameters.Add(param_server_id);
command.Parameters.Add(new SqlParameter("@input_config_name", config_name));
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
}
BotConfiguration.Token = (string)param_token.Value;
BotConfiguration.Prefix = ((string)param_prefix.Value).ToCharArray()[0];
//Store the server ID
long result2 = (long)param_server_id.Value;
UInt64 ResultID = (UInt64)result2;
BotConfiguration.ServerID = ResultID;
}
}
static public int VerifyIfUserIsInTournament(int UserID)
{
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
using (SqlCommand command = new SqlCommand("VerifyIfUserIsInTournament", conn))
{
command.Parameters.Add(new SqlParameter("@input_user_id", UserID));
SqlParameter returnValue = new SqlParameter("@result", SqlDbType.BigInt)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(returnValue);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
return(Convert.ToInt32(returnValue.Value));
}
}
}
static public ulong GetPersonalRole(int userID)
{
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
using (SqlCommand command = new SqlCommand("GetPersonalRole", conn))
{
command.Parameters.Add(new SqlParameter("@input_user_id", (Int64)userID));
SqlParameter returnValue = new SqlParameter("@result", SqlDbType.BigInt)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(returnValue);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
var returnval = (long)returnValue.Value;
return((ulong)returnval);
}
}
}
static public string GetVersion()
{
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
//Now that the private channel has been created, add it to the database.
using (SqlCommand command = new SqlCommand("GetVersion", conn))
{
SqlParameter returnValue = new SqlParameter("@version_string", SqlDbType.VarChar)
{
Direction = ParameterDirection.Output,
Size = 10
};
command.Parameters.Add(returnValue);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
return((string)returnValue.Value);
}
}
}
static public int GetUserID(ulong DiscordID)
{
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
using (SqlCommand command = new SqlCommand("GetUserID", conn))
{
command.Parameters.Add(new SqlParameter("@input_discord_id", (Int64)DiscordID));
command.Parameters.Add(new SqlParameter("@input_server_id", (Int64)BotConfiguration.ServerID));
SqlParameter returnValue = new SqlParameter("@result", SqlDbType.BigInt)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(returnValue);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
return(Convert.ToInt32(returnValue.Value));
}
}
}
static public ulong GetRoleIdFromName(string roleName)
{
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
using (SqlCommand command = new SqlCommand("GetRoleIdFromName", conn))
{
command.Parameters.Add(new SqlParameter("@input_role_name", roleName));
command.Parameters.Add(new SqlParameter("@input_server_id", (Int64)BotConfiguration.ServerID));
SqlParameter returnValue = new SqlParameter("@result", SqlDbType.BigInt)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(returnValue);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
var returnval = (long)returnValue.Value;
return((ulong)returnval);
}
}
}
static public int EmoteOrEmoji(string reaction_type)
{
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
using (SqlCommand command = new SqlCommand("EmoteOrEmoji", conn))
{
SqlParameter newparam = new SqlParameter("@input_reaction_type", reaction_type);
command.Parameters.Add(newparam);
command.Parameters.Add(new SqlParameter("@input_server_id", (Int64)BotConfiguration.ServerID));
SqlParameter returnValue = new SqlParameter("result", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(returnValue);
conn.Open();
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
return((int)returnValue.Value);
}
}
}
static public long GetRandomEmote(string reaction_type)
{
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
using (SqlCommand command = new SqlCommand("GetRandomEmote", conn))
{
SqlParameter returnValue = new SqlParameter("return_emote_id", SqlDbType.BigInt)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(returnValue);
command.Parameters.Add(new SqlParameter("@input_reaction_type", reaction_type));
command.Parameters.Add(new SqlParameter("@input_server_id", (Int64)BotConfiguration.ServerID));
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
Console.WriteLine("Emote from database: " + returnValue.Value.ToString());
return((long)returnValue.Value);
}
}
}
static public int AddUser(ulong discordID, ulong newChannelID, string alias)
{
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
//Now that the private channel has been created, add it to the database.
using (SqlCommand command = new SqlCommand("AddUser", conn))
{
command.Parameters.Add(new SqlParameter("@input_discord_id", (Int64)discordID));
command.Parameters.Add(new SqlParameter("@input_server_id", (Int64)newChannelID));
command.Parameters.Add(new SqlParameter("@input_alias", alias));
SqlParameter returnValue = new SqlParameter("@result", SqlDbType.BigInt)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(returnValue);
command.CommandType = CommandType.StoredProcedure;
conn.Open();
command.ExecuteNonQuery();
return(Convert.ToInt32(returnValue.Value));
}
}
}
public async Task <RuntimeResult> Setup(IGuildUser user, [Remainder] String arg = "")
{
try
{
using (SqlConnection conn = new SqlConnection(MySQLConnString.Get()))
{
var mention = user.Mention;
string resultString = $"Setting up {mention}:\n";
//User ID section
int userID = DatabaseHandler.GetUserID(user.Id);
//If the user doesn't exist, create and insert him.
if (userID == 0)
{
userID = DatabaseHandler.AddUser(user.Id, Context.Guild.Id, user.Username);
resultString += "User ID created.\n";
}
else
{
resultString += "User ID already exists.\n";
}
//Personal role section
ulong result = DatabaseHandler.GetPersonalRole(userID);
Discord.Rest.RestRole role = null;
//Check if we got a result.
if (result == 0)
{ //No result was found
var name = user.Username.ToString();
//Create the role in discord.
role = await Context.Guild.CreateRoleAsync(name);
//Add the role to the user who called this command.
await((IGuildUser)user).AddRoleAsync(role);
//Add the role to the database
DatabaseHandler.AddPersonalRole(userID, role.Id);
//Command complete.
resultString += "Personal role created.\n";
if (arg != "")
{
arg = arg.TrimStart('#');
await role.ModifyAsync(x =>
{
x.Color = new Color(Convert.ToUInt32(arg, 16));
});
resultString += "I set your color for you.\n";
}
}
else
{
resultString += "Personal role already exists.\n";
}
//Personal Channel section
ulong personalChannel = DatabaseHandler.GetPersonalChannel(userID);
if (personalChannel == 0)
{
ulong adminBotRoleID = DatabaseHandler.GetAdminbotRoleID();
var adminBot = Context.Guild.GetRole(adminBotRoleID);
var newChannel = await Context.Guild.CreateTextChannelAsync(user.Username.ToString(), x =>
{
x.Topic = $"Your personal channel to talk to the bot.";
x.Position = 2;
});
var newChannelID = newChannel.Id;
var everyone = Context.Guild.EveryoneRole;
await newChannel.AddPermissionOverwriteAsync(adminBot, new Discord.OverwritePermissions(viewChannel: Discord.PermValue.Allow));
await newChannel.AddPermissionOverwriteAsync(role, new Discord.OverwritePermissions(viewChannel: Discord.PermValue.Allow));
await newChannel.AddPermissionOverwriteAsync(everyone, new Discord.OverwritePermissions(viewChannel: Discord.PermValue.Deny));
await newChannel.AddPermissionOverwriteAsync(role, new Discord.OverwritePermissions(manageMessages: Discord.PermValue.Allow));
DatabaseHandler.AddPersonalChannel(userID, newChannelID);
resultString += "Channel created.\n";
}
else
{
resultString += "Channel already exists.\n";
}
await Context.Channel.SendMessageAsync(resultString);
return(WestbotCommandResult.AcceptReact(null, true));
}
}
catch (Exception ex)
{ //Display exception.
Console.WriteLine("Exception: " + ex.Message);
await Context.Channel.SendMessageAsync("Exception: " + ex.Message);
return(WestbotCommandResult.ErrorReact(null, true));
}
}