public static async Task <Tweet> Peek()
{
Tweet tweet = null;
var table = await AbbysqlClient.FetchSQL($"SELECT * FROM `twitter`.`archive` ");
if (table.Count < 1)
{
throw new Exception("no tweets in list");
}
//Console.WriteLine($"You have {table.Count} items in the tweetarchive");
AbbyRow row = table[r.Next(0, table.Count)];
tweet = new Tweet()
{
id = (int)row["Id"],
url = (row["ImgUrl"] is string i) ? i : "",
sourceurl = (row["SrcUrl"] is string s) ? s : "",
message = (row["Description"] is string m) ? m : "",
priority = (sbyte)row["Priority"] == 1,
GelId = (row["GelId"] is int gild ? gild : 0),
md5 = (row["md5"] is string smd5) ? smd5 : "",
source = "tweetarchive"
};
return(tweet);
}
public static async Task <Tweet> Peek()
{
Tweet tweet = null;
var table = await AbbysqlClient.FetchSQL($"SELECT * FROM `twitter`.`tweets` ORDER BY Priority DESC, Id ASC LIMIT 1;");
if (table.Count < 1)
{
throw new Exception("no tweets in list");
}
foreach (AbbyRow row in table)
{
tweet = new Tweet()
{
id = (int)row["Id"],
url = (row["ImgUrl"] is string i) ? i : "",
sourceurl = (row["SrcUrl"] is string s) ? s : "",
message = (row["Description"] is string m) ? m : "",
priority = (sbyte)row["Priority"] == 1 ? true : false,
GelId = (row["GelId"] is int gild ? gild : 0),
md5 = (row["md5"] is string smd5) ? smd5 : "",
source = "tweets"
};
}
return(tweet);
}
public static async Task <string> GetLastTime(ulong userId, ulong guildId, string item)
{
var it = AbbysqlClient.EscapeString(item);
var a = await AbbysqlClient.FetchSQL($"select * from `user`.`activity` where `UserId`='{userId}' and `GuildId` = '{guildId}' and `Kind` = '{it}'");
return((a.Count > 0 && a[0]["Time"] is string s) ? s : "");
}
public static async Task <AbbybotGuild> GetGuild(ulong abg)
{
var g = new AbbybotGuild()
{
Id = abg
};
var table = await AbbysqlClient.FetchSQL($"SELECT `GuildId` FROM `abbybot`.`guilds` WHERE `GuildId` = '{g.Id}';");
bool e = table.Count > 0;
if (abg == 0)
{
return(null);
}
var table2 = await AbbysqlClient.FetchSQL($"SELECT * FROM `abbybot`.`guilds` WHERE `GuildId` = '{g.Id}';");
foreach (AbbyRow row in table2)
{
g.NoLoli = (sbyte)row["NoLoli"] == 1 ? true : false;
g.NoNSFW = (sbyte)row["NoNSFW"] == 1 ? true : false;
g.PrefAbbybot = row["PrefAbbybot"] is ulong pabi ? pabi : 0;
g.AutoDeleteTime = row["DeleteAfterSeconds"] is int secs ? secs : -1;
}
return(g);
}
public static async Task <List <(ulong userId, ulong stat)> > GetGuildStat(ulong abbybotId, ulong guildId, string v)
{
List <(ulong userId, ulong stat)> stats = new List <(ulong userId, ulong stat)>();
var ori = AbbysqlClient.EscapeString(v);
var a = await AbbysqlClient.FetchSQL($"select * from `guild`.`userstats` where `AbbybotId` = '{abbybotId}' and `GuildId`='{guildId}' and `Stat`='{ori}';");
foreach (AbbyRow abr in a)
{
var point = abr["Points"] is ulong p ? p : 0;
var user = abr["UserId"] is ulong u ? u : 0;
stats.Add((user, point));
}
var groupedstats = stats.ToList().GroupBy(x => x.userId);
stats.Clear();
foreach (var u in groupedstats)
{
ulong o = 0;
foreach (var s in u)
{
o += s.stat;
}
stats.Add((u.Key, o));
}
return(stats);
}
public static async Task IncreaseStat(ulong abbybotId, ulong guildId, ulong channelId, ulong userId, string stat)
{
if (stat.Length == 0)
{
return;
}
var it = AbbysqlClient.EscapeString(stat);
//var month = DateTime.Now.ToString("MMMM yyyy");
var a = await AbbysqlClient.FetchSQL($"select * from `guild`.`userstats` where `Stat`='{stat}' and `AbbybotId` = '{abbybotId}' and `GuildId`='{guildId}' and `ChannelId`='{channelId}' and `UserId`='{userId}'");
if (a.Count != 0)
{
ulong num = (a[0]["Points"] is ulong points) ? points : 0;
ulong n = ++num;
string s = $"UPDATE `guild`.`userstats` SET `Points`= '{n}' WHERE `Stat`='{stat}' and `AbbybotId` = '{abbybotId}' and `GuildId`='{guildId}' and `ChannelId`='{channelId}' and `UserId`='{userId}';";
await AbbysqlClient.RunSQL(s);
}
else
{
await AbbysqlClient.RunSQL($"insert into `guild`.`userstats` (`AbbybotId`,`GuildId`, `ChannelId`,`UserId`, `Stat`, `Points`) values ('{abbybotId}', '{guildId}', '{channelId}', '{userId}','{stat}', '1');");
}
}
public static async Task <bool> AddRole(ulong guildId, ulong roleId)
{
var t = await AbbysqlClient.FetchSQL($"select * from `guild`.`mostactiveroles` where `GuildId`='{guildId}' and `RoleId` = '{roleId}';");
return(t.Count < 1
? await AbbysqlClient.RunSQL($"insert into `guild`.`mostactiveroles`(`GuildId`, `RoleId`) values ('{guildId}', '{roleId}');") > 0
: false);
}
public static async Task <int> GetGuild(SocketGuild abg, RestInviteMetadata rim)
{
var table = await AbbysqlClient.FetchSQL($"SELECT `Joins` FROM `invites` WHERE `GuildId` = '{abg.Id}' AND `InviteId` = '{rim.Code}';");
bool e = table.Count > 0;
return(e ? (int)table[0]["Joins"] : 0);
}
public static async Task <List <ulong> > GetListAutoFcDmsAsync()
{
List <ulong> fcdms = new List <ulong>();
AbbyTable table = await AbbysqlClient.FetchSQL($"select * from `user`.`autofcdms` where `On` = 1;");
foreach (AbbyRow ar in table)
{
fcdms.Add((ulong)ar["UserId"]);
}
return(fcdms);
}
public static async Task <int> Count()
{
var table = await AbbysqlClient.FetchSQL("SELECT COUNT(*) as 'rows' FROM twitter.archive;");
int rows = 0;
foreach (AbbyRow row in table)
{
rows = int.Parse(row["rows"].ToString());
}
return(rows);
}
public static async Task <List <ulong> > GetLatestMentionIdsAsync(ulong id)
{
List <ulong> ids = new List <ulong>();
AbbyTable table = await AbbysqlClient.FetchSQL($"SELECT * FROM heardtweets WHERE Id = {id};");
foreach (AbbyRow row in table)
{
ids.Add((ulong)row["Id"]);
}
return(ids);
}
public static async Task <List <string> > GetbadtaglistTags()
{
List <string> tags = new List <string>();
var table = await AbbysqlClient.FetchSQL("SELECT * FROM `saybadtaglist`");
foreach (AbbyRow row in table)
{
tags.Add((row["Word"] is string favchan) ? favchan : "");
}
return(tags);
}
public static async Task <(string fc, bool nsfw)> GetFCMAsync(ulong guildId, ulong channelId)
{
AbbyTable table = await AbbysqlClient.FetchSQL($"select * from `channel`.`fcsettings` where `GuildId`= {guildId} and `ChannelId` = {channelId};");
if (table.Count >= 1)
{
var fc = (table[0]["FavoriteCharacter"] is string s && s.Length > 1) ? s : "NO";
var nsfw = table[0]["IsNSFW"] is int nsf && nsf == 1;
return(fc, nsfw);
}
return("NO", false);
}
public static async Task <List <string> > GetbadtaglistTags(ulong id)
{
List <string> tags = new List <string>();
var table = await AbbysqlClient.FetchSQL($"SELECT `Tag` FROM `user`.`gelbadtaglist` WHERE `UserId` = '{id}';");
foreach (AbbyRow row in table)
{
tags.Add((row["Tag"] is string favchan) ? favchan : "");
}
return(tags);
}
public static async Task <List <AbbybotRole> > GetRolesFromUser(AbbybotUser u)
{
List <AbbybotRole> roles = new List <AbbybotRole>();
var table = await AbbysqlClient.FetchSQL($"SELECT * FROM `user`.`roles` WHERE `UserId` = '{u.Id}' && `GuildId` = '{u.GuildId}';");
foreach (AbbyRow row in table)
{
long Roleid = (long)(row["RoleId"]);
roles.AddRange(RoleManager.roles.Where(rx => rx.role == (ulong)Roleid).Select(rx => rx));
}
return(roles);
}
public static async Task RemoveCharacterAsync(ISocketMessageChannel channel, string v)
{
var tag = AbbysqlClient.EscapeString(v);
var tbl = await AbbysqlClient.FetchSQL($"SELECT * FROM `abbybooru`.`characters` WHERE `Tag` = '{tag}' AND `ChannelId` = '{channel.Id}';");
if (tbl.Count < 1)
{
throw new Exception("nocharacter");
}
await AbbysqlClient.RunSQL($"DELETE FROM `abbybooru`.`characters` WHERE `Tag` = '{tag}' AND `ChannelId` = '{channel.Id}';");
}
public static async Task <List <ulong> > GetAbbybotIdAsync()
{
List <ulong> abbybots = new List <ulong>();
AbbyTable table = await AbbysqlClient.FetchSQL($"select * from `abbybot`.`discordbots`");
foreach (AbbyRow row in table)
{
abbybots.Add(table.Count >= 1 && (row["Id"] is ulong u) ? u : 0);
}
abbybots.Remove(Apis.Discord.__client.CurrentUser.Id);
return(abbybots);
}
public static async Task SetAutoFcDmAsync(ulong userId, bool FCMentions)
{
int fcm = FCMentions ? 1 : 0;
AbbyTable table = await AbbysqlClient.FetchSQL($"select * from `user`.`autofcdms` where `UserId` = {userId}");
if (table.Count < 1)
{
await AbbysqlClient.RunSQL($"insert into `user`.`autofcdms` (`UserId`, `On`) values ('{userId}','{fcm}');");
return;
}
await AbbysqlClient.RunSQL($"UPDATE `user`.`autofcdms` SET `On`= '{fcm}' WHERE `UserId`= {userId};");
}
public static async Task <List <(ulong guildId, ulong channelId)> > GetAbbybotChannelIdAsync()
{
List <(ulong guildId, ulong channelId)> abbybots = new List <(ulong guildId, ulong channelId)>();
AbbyTable table = await AbbysqlClient.FetchSQL($"select * from `abbybot`.`channels`");
foreach (AbbyRow row in table)
{
var guild = table.Count >= 1 && (row["GuildId"] is ulong g) ? g : 0;
var channel = table.Count >= 1 && (row["ChannelId"] is ulong c) ? c : 0;
abbybots.Add((guild, channel));
}
return(abbybots);
}
public static async Task <List <AbbybotRole> > GetRoles(SocketGuild g)
{
List <AbbybotRole> roles = new List <AbbybotRole>();
var table = await AbbysqlClient.FetchSQL($"SELECT * FROM `guild`.`roles` WHERE `GuildId` = '{g.Id}';");
foreach (AbbyRow row in table)
{
ulong Roleid = (ulong)(row["RoleId"]);
int Ranking = (int)row["Ranking"];
roles.Add(new AbbybotRole((ulong)Roleid, new CommandRatings[] { (CommandRatings)Ranking }));
}
return(roles);
}
public static async Task AddCharacterAsync(ISocketMessageChannel channel, AbbybotGuild abbybotGuild, string v)
{
var tag = AbbysqlClient.EscapeString(v);
var nsf = (channel as ITextChannel).IsNsfw ? 0 : 1;
var tbl = await AbbysqlClient.FetchSQL($"SELECT * FROM `abbybooru`.`characters` WHERE `Tag` = '{tag}' AND `ChannelId` = '{channel.Id}';");
if (tbl.Count > 0)
{
throw new Exception("CharacterAlreadyAdded");
}
await AbbysqlClient.RunSQL($"INSERT INTO `abbybooru`.`characters` ( `Tag`,`ChannelId`, `GuildId`, `IsLewd` ) VALUES ('{tag}','{channel.Id}','{abbybotGuild.Id}', '{nsf}'); ");
}
internal static async Task <List <ulong> > GetLatestPostIdsAsync(Character character)
{
List <ulong> ulongs = new List <ulong>();
var table = await AbbysqlClient.FetchSQL($"SELECT * FROM `abbybooru`.`characterpostids` WHERE `CharId`='{character.Id}';");
foreach (AbbyRow row in table)
{
if (row["Id"] is ulong id)
{
ulongs.Add(id);
}
}
return(ulongs);
}
public static async Task <List <(ulong channel, ulong stat)> > GetChannelsinGuildStats(ulong abbybotId, ulong guildId, ulong userId, string v)
{
List <(ulong, ulong)> statchannels = new List <(ulong, ulong)>();
var ori = AbbysqlClient.EscapeString(v);
var a = await AbbysqlClient.FetchSQL($"select * from `guild`.`userstats` where `AbbybotId` = '{abbybotId}' and `GuildId`='{guildId}' and `UserId`='{userId}' and `Stat`='{ori}';");
foreach (AbbyRow abr in a)
{
ulong channelId = (abr["ChannelId"] is ulong chanid) ? chanid : 0;
ulong points = (abr["Points"] is ulong poin) ? poin : 0;
statchannels.Add((channelId, points));
}
return(statchannels);
}
public static async Task SetCFCAsync(ulong guildId, ulong channelId, bool nsfw, string fc)
{
string fcm = AbbysqlClient.EscapeString(fc);
int insfw = nsfw ? 1 : 0;
AbbyTable table = await AbbysqlClient.FetchSQL($"select * from `channel`.`fcsettings` where `GuildId` = {guildId} and `ChannelId`= {channelId}");
if (table.Count < 1)
{
await AbbysqlClient.RunSQL($"insert into `channel`.`fcsettings` (`GuildId`, `ChannelId`, `FavoriteCharacter`,`IsNSFW` ) values ('{guildId}', '{channelId}', '{fcm}', {insfw});");
}
else
{
await AbbysqlClient.RunSQL($"UPDATE `channel`.`fcsettings` SET `FavoriteCharacter`= '{fcm}' WHERE `GuildId`= {guildId} and `ChannelId` = {channelId};");
}
}
public static async Task <LuaData> GetLuaData(ulong UserId)
{
LuaData lua = new() { UserId = UserId };
var table = await AbbysqlClient.FetchSQL($"SELECT * FROM user.lua where UserId = {UserId} ORDER BY Id ASC");
table.ToList().
ForEach(r =>
{
if (r["lua"] is string s)
{
lua.LuaPieces.Add(s);
}
});
return(lua);
}
public static async Task <List <(ulong guildId, ulong roleId)> > GetRoles()
{
List <(ulong guildId, ulong roleId)> roleIds = new List <(ulong guildId, ulong roleId)>();
AbbyTable table = await AbbysqlClient.FetchSQL($"select * from `guild`.`mostactiveroles`;");
if (table != null)
{
foreach (AbbyRow row in table)
{
var g = row["GuildId"] is ulong guild ? guild : 0;
var u = row["RoleId"] is ulong role ? role : 0;
roleIds.Add((g, u));
}
}
return(roleIds);
}
internal static async Task <List <Character> > GetListFromSqlAsync()
{
List <Character> c = new();
var table = await AbbysqlClient.FetchSQL("SELECT * FROM `abbybooru`.`characters`");
foreach (AbbyRow row in table)
{
ulong Id = row["Id"] is ulong u ? u : 0;
bool IsLewd = row["IsLewd"] is int il && il > 0;
string tag = row["Tag"] is string ta ? ta : "";
ulong guildId = row["GuildId"] is ulong gid ? gid : 0;
ulong channelId = row["ChannelId"] is ulong cid ? cid : 0;
c.Add(new() { Id = Id, channelId = channelId, guildId = guildId, IsLewd = IsLewd, tag = tag });
}
return(c);
}
public static async Task <Image> Peek()
{
Image image = null;
AbbyTable table = await AbbysqlClient.FetchSQL($"SELECT * FROM `twitter`.`images`ORDER BY Id LIMIT 1;");
foreach (AbbyRow row in table)
{
image = new Image
{
id = (int)row["Id"],
url = (row["ImgUrl"] is string i) ? i : "",
sourceurl = (row["ImgUrl"] is string s) ? s : "",
gelId = row["GelId"] is int isi ? isi : 0,
md5 = row["md5"] is string md5 ? md5 : ""
};
}
return(image);
}
}
public static async Task <List <Egg> > GetEggsAsync()
{
List <Egg> cmds = new List <Egg>();
var table = await AbbysqlClient.FetchSQL("SELECT * FROM eggs");
foreach (AbbyRow row in table)
{
ulong id = (ulong)row["Id"];
string Word = (row["Word"] is string word) ? word : "";
string Reply = (row["Reply"] is string reply) ? reply : "";
int Min = (int)row["Min"];
int Max = (int)row["Max"];
Egg gc = new Egg(Min, Max, Word, Reply);
gc.Rating = (CommandRatings)1;
gc.Type = (CommandType)1;
cmds.Add(gc);
}
return(cmds);
}
public static async Task <GuildMessage> GetGuildMessage(ulong id, string type)
{
GuildMessage g = null;
var table2 = await AbbysqlClient.FetchSQL($"SELECT * FROM servermessages WHERE guildId ='{id}' && type = '{type}';");
foreach (AbbyRow row in table2)
{
g = new GuildMessage()
{
guildId = (ulong)row["guildId"],
type = (row["type"] is string s) ? s : "",
message = (row["message"] is string msg) ? msg : "",
imgurl = (row["imagelink"] is string imlk) ? imlk : "",
channelId = (ulong)row["channel"]
};
}
return(g);
}
}