private async Task GetSQLData()
{
if (hasLoaded)
{
return;
}
//Load prefix & options from DB
SqlConnectionStringBuilder sBuilder = new SqlConnectionStringBuilder();
sBuilder.InitialCatalog = dbSettings.db;
sBuilder.UserID = dbSettings.username;
sBuilder.Password = dbSettings.password;
sBuilder.DataSource = dbSettings.host + @"\" + dbSettings.instance + "," + dbSettings.port;
sBuilder.ConnectTimeout = 30;
sBuilder.IntegratedSecurity = false;
SqlConnection conn = new SqlConnection();
conn.ConnectionString = sBuilder.ConnectionString;
using (conn)
{
conn.Open();
#region Get Guilds
SqlCommand cmd = new SqlCommand($"SELECT * FROM Guilds", conn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
GuildOption go = new GuildOption();
Options o = new Options();
go.GuildID = Convert.ToUInt64(dr.GetValue(0));
go.GuildName = Convert.ToString(dr.GetValue(1));
go.OwnerID = Convert.ToUInt64(dr.GetValue(2));
go.Prefix = Convert.ToString(dr.GetValue(3));
o.LogChannelID = Convert.ToUInt64(dr.GetValue(4));
o.LogEmbeds = Convert.ToBoolean(dr.GetValue(5));
o.LogAttachments = Convert.ToBoolean(dr.GetValue(6));
go.Options = o;
if (!GlobalVars.GuildOptions.Any(x => x.GuildID == go.GuildID))
{
GlobalVars.GuildOptions.Add(go);
}
}
dr.Close();
#endregion
#region Get Friends
cmd.CommandText = $"SELECT UserID FROM Friends";
dr = cmd.ExecuteReader();
while (dr.Read())
{
var id = Convert.ToUInt64(dr.GetValue(0));
var user = await CustomUserTypereader.GetUserFromID(id, Client.Guilds);
if (!GlobalVars.FriendUsers.ContainsKey(id))
{
GlobalVars.FriendUsers.Add(id, user);
}
}
dr.Close();
#endregion
#region Get Idiots
cmd.CommandText = $"SELECT UserID FROM Ignores";
dr = cmd.ExecuteReader();
while (dr.Read())
{
var id = Convert.ToUInt64(dr.GetValue(0));
var user = await Classes.Commands.CustomUserTypereader.GetUserFromID(id, Client.Guilds);
if (!GlobalVars.IgnoredUsers.ContainsKey(id))
{
GlobalVars.IgnoredUsers.Add(id, user);
}
}
dr.Close();
#endregion
#region Get Emotes
cmd.CommandText = $"SELECT * FROM Emotes";
dr = cmd.ExecuteReader();
while (dr.Read())
{
ApprovedEmote ae = new ApprovedEmote(dr.GetValue(0).ToString(), dr.GetValue(3).ToString(), dr.GetValue(2).ToString(), Convert.ToBoolean(dr.GetValue(4)), dr.GetValue(5).ToString(), Convert.ToBoolean(dr.GetValue(6)));
if (!GlobalVars.EmoteList.ContainsKey(ae.EmoteID))
{
GlobalVars.EmoteList.Add(ae.EmoteID, ae);
}
}
dr.Close();
#endregion
#region Get Bot Owners
cmd.CommandText = $"SELECT * FROM BotOwners";
dr = cmd.ExecuteReader();
List <ulong> ownerList = new List <ulong>();
while (dr.Read())
{
ownerList.Add(Convert.ToUInt64(dr.GetValue(1)));
}
Constants._BOTOWNERS_ = ownerList.ToArray();
dr.Close();
#endregion
#region Get Morty Users
cmd.CommandText = $"SELECT * FROM mortyUsers";
dr = cmd.ExecuteReader();
List <ulong> mortyUsers = new List <ulong>();
while (dr.Read())
{
mortyUsers.Add(Convert.ToUInt64(dr.GetValue(1)));
}
GlobalVars.RegisteredMortyUsers = mortyUsers;
GlobalVars.MortyTimeouts = new Dictionary <ulong, bool>();
GlobalVars.MortyLastUse = new Dictionary <ulong, DateTime>();
foreach (ulong u in GlobalVars.RegisteredMortyUsers)
{
GlobalVars.MortyTimeouts.Add(u, false);
GlobalVars.MortyLastUse.Add(u, DateTime.MinValue);
}
dr.Close();
#endregion
#region Get Timers
cmd.CommandText = $"SELECT UserID, GuildID, ChannelID, TimerType, TriggerTime, TimerMessage, TimerID FROM Timers";
dr = cmd.ExecuteReader();
List <TimerObj> timerList = new List <TimerObj>();
while (dr.Read())
{
var o = new TimerObj
{
UserID = ulong.Parse(dr.GetValue(0).ToString()),
GuildID = ulong.Parse(dr.GetValue(1).ToString()),
ChannelID = ulong.Parse(dr.GetValue(2).ToString()),
TimerType = dr.GetValue(3).ToString(),
TriggerTime = dr.GetValue(4).ToString(),
TimerMsg = dr.GetValue(5).ToString(),
TimerID = int.Parse(dr.GetValue(6).ToString())
};
timerList.Add(o);
}
foreach (var o in timerList)
{
if (o.TimerType == "remind")
{
Reminders r = new Reminders();
int time = 0;
List <string> timeParts = o.TriggerTime.Split('-').ToList();
timeParts.RemoveAll(s => s == "-");
int[] intTP = new int[7];
for (int i = 0; i < 7; i++)
{
intTP[i] = int.Parse(timeParts[i]);
}
DateTime dt = new DateTime(intTP[0], intTP[1], intTP[2], intTP[3], intTP[4], intTP[5], intTP[6]);
time = Convert.ToInt32(dt.Subtract(DateTime.Now).TotalMilliseconds);
if (time >= 0)
{
var t = Convert.ToUInt64(time);
var txtChan = Client.GetGuild(o.GuildID).GetTextChannel(o.ChannelID);
var u = Client.GetGuild(o.GuildID).GetUser(o.UserID);
var msg = o.TimerMsg.Replace("§²", "'").Replace("§³", ";");
r.TimerStart(t, txtChan, u, msg);
}
else
{
string sql = $"DELETE FROM Timers WHERE TimerID = {o.TimerID}";
DBControl.UpdateDB(sql);
}
}
}
dr.Close();
#endregion
conn.Close();
conn.Dispose();
}
}