/// <summary>
/// Gets the welcome message to display in the welcome channel
/// </summary>
/// <param name="serverid">The guild ID to look for</param>
/// <returns>The message to display</returns>
public static string GetWelcomeMessage(ulong serverid)
{
using (SqliteDbContext DbContext = new SqliteDbContext())
{
return(DbContext.GuildLocationSettings.Where(x => x.Serverid == serverid).Select(x => x.WelcomeMessage).FirstOrDefault());
}
}
public static void ReloadMenus()
{
using (var DbContext = new SqliteDbContext())
{
Vars.groupMenus = DbContext.KinkGroupMenus.ToList();
}
}
private static void PruebaDeConexionABaseDeDatosSqlite()
{
Console.WriteLine("Conectando a la base de datos...");
using (var db = new SqliteDbContext())
{
// db.Database.EnsureDeleted();
db.Database.EnsureCreated();
var usuario = new User()
{
Name = "bidkar",
Password = "******",
FirstName = "Bidkar",
LastName = "Aragon",
Email = "*****@*****.**",
Status = UserStatus.Active
};
// Create (insert)
db.Add(usuario);
db.SaveChanges();
// Read (select)
var usuarios = db.Users;
foreach (var u in usuarios)
{
Console.WriteLine("Usuario: " + u.Name);
}
}
}
public async Task fileIsActive(int projectID, int fileID, string newStatus)
{
stringToBool isValidInput = new stringToBool();
isValidInput = projectMethod.stringToBool(newStatus);
if (!isValidInput.isBool)
{
await Context.Channel.SendMessageAsync("T/F Input is invalid try **true** or **false**");
return;
}
using (var dbContext = new SqliteDbContext())
{
var list = dbContext.Files.Where(x => x.ProjectId == projectID && x.FileId == fileID);
if (list.Count() < 1)
{
await Context.Channel.SendMessageAsync("target file does not exist, please ensure the input is correct");
}
//input is false/0 change incomplete back to false
if (isValidInput.value)
{
dbContext.Projects.Where(x => x.ProjectId == projectID).FirstOrDefault().isComplete = false;
}
var item = list.FirstOrDefault();
item.isActive = isValidInput.value;
item.LastUpdated = DateTime.Now;
await Context.Channel.SendMessageAsync("File status updated");
await dbContext.SaveChangesAsync();
}
}
public static async Task CheckIfLastSwing()
{
using (var DbContext = new SqliteDbContext())
{
foreach (Participant p in DbContext.Participants)
{
if (p.GuessNumber == -1)
{
return;
}
}
Game game = DbContext.Games.Where(x => x.StateOfGame != 3).FirstOrDefault();
if (game.NumberOfPitches >= 5)
{
game.StateOfGame = 3;
}
else
{
game.StateOfGame = 1;
}
DbContext.Games.Update(game);
await DbContext.SaveChangesAsync();
return;
}
}
/// <summary>
/// 称重数据插入本地数据
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public WeightGridDto InsertWeightGrid(WeightGridDto dto)
{
var model = new WeightGridDto();
try
{
using (var sql = SqliteDbContext.GetInstance())
{
//获取上一条未提交到服务器的数据
var localLastWeight = sql.Queryable <WeightGridDto>().Where(s => s.BatchId == dto.BatchId && s.Uploadflag == 0)
.OrderBy(s => s.WeightTime, OrderByType.Desc).First();
//插入本条数据
sql.Insertable(dto).ExecuteCommand();
if (localLastWeight != null)
{
model = localLastWeight;
}
}
return(model);
}
catch (Exception e)
{
LogNHelper.Exception(e);
}
return(null);
}
private async Task <bool> Save()
{
var appName = txtAppName.Text.Trim();
var appPassword = RSAUtil.Encrypt(txtAppPassword.Text.Trim(), _publicKey);
using (var db = new SqliteDbContext())
{
if (_appPwdId > 0)
{
CurrentSelectAppPwd.AppName = appName;
CurrentSelectAppPwd.Password = appPassword;
CurrentSelectAppPwd.ModifyTime = DateTime.UtcNow;
db.AppPasswords.Update(CurrentSelectAppPwd);
}
else
{
db.AppPasswords.Add(new AppPassword
{
AppName = appName,
Password = appPassword,
CreateTime = DateTime.UtcNow,
ModifyTime = DateTime.UtcNow
});
}
return(await db.SaveChangesAsync() > 0);
}
}
public async Task Reset(IUser User = null)
{
if (User == null)
{
// No user has been mentioned
await Context.Channel.SendMessageAsync($":x: You need to mention which user to reset ounces for! e.g. !ounces reset {Context.User.Mention}");
return;
}
if (User.IsBot)
{
await Context.Channel.SendMessageAsync(":x: Bots aren't people!");
return;
}
SocketGuildUser User1 = Context.User as SocketGuildUser;
if (!User1.GuildPermissions.Administrator)
{
await Context.Channel.SendMessageAsync(":x: You don't have the permissions to use this comand! Please ask a moderator to do so.");
return;
}
await Context.Channel.SendMessageAsync($":skull: {User.Mention}, you have been reset by {Context.User.Username}. Your ounces are now 0.");
using (var DbContext = new SqliteDbContext()) {
DbContext.ounces.RemoveRange(DbContext.ounces.Where(x => x.UserId == User.Id));
await DbContext.SaveChangesAsync();
}
}
private RequestBuilder CreateMockRequestBuilder()
{
SqliteDbContext? _dB = new SqliteDbContext(_dbContextOptions);
MemoryKeySecretProvider?_secretProvider = new MemoryKeySecretProvider();
EntityMapper? _entityMapper = new EntityMapper();
RequestBuilder? requestBuilder = new RequestBuilder(
entityMapper: _entityMapper,
dbContextService: new DbMultiEntityMethods(_dB),
configurationProvider: new DefaultConfigurationProvider(),
logger: new ConsoleInstrumentationClient(),
keySecretReadOnlyProvider: _secretProvider,
apiClient: GetApiClient(TestConfig),
certificateReader: new PemParsingCertificateReader(),
clientProfileRepository: new DbEntityRepository <BankClientProfile>(_dB),
domesticConsentRepo: new DbEntityRepository <DomesticConsent>(_dB),
apiProfileRepository: new DbEntityRepository <ApiProfile>(_dB),
activeSReadOnlyRepo: new KeySecretReadRepository <ActiveSoftwareStatementProfiles>(_secretProvider),
activeSrRepo: new KeySecretWriteRepository <ActiveSoftwareStatementProfiles>(_secretProvider),
sReadOnlyRepo: new KeySecretMultiItemReadRepository <SoftwareStatementProfile>(_secretProvider),
sRepo: new KeySecretMultiItemWriteRepository <SoftwareStatementProfile>(_secretProvider),
softwareStatementProfileService: new SoftwareStatementProfileService(
softwareStatementProfileRepo:
new KeySecretMultiItemReadRepository <SoftwareStatementProfile>(_secretProvider),
activeSoftwareStatementProfilesRepo: new KeySecretReadRepository <ActiveSoftwareStatementProfiles>(
_secretProvider),
mapper: _entityMapper));
return(requestBuilder);
}
/// <summary>
/// 批量插入
/// </summary>
/// <param name="context">当前DbContext</param>
/// <param name="tableName">要插入的表名称</param>
/// <param name="dataTable">DataTable</param>
public static int BulkInsert(this SqliteDbContext context, string tableName, DataTable dataTable)
{
var provider = new BulkInsertSqliteProvider(context);
var result = provider.BulkInsert(tableName, dataTable);
return(result);
}
/// <summary>
/// 批量插入,不建议使用List插入、性能会受一定影响
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="context">当前DbContext</param>
/// <param name="tableName">要插入的表名称</param>
/// <param name="list">List列明必须与数据表列一致,严格大小写区分</param>
public static int BulkInsert <T>(this SqliteDbContext context, string tableName, IList <T> list)
{
var provider = new BulkInsertSqliteProvider(context);
var result = provider.BulkInsert(tableName, list);
return(result);
}
/// <summary>
/// Saves a custom command created in a guild.
/// </summary>
/// <param name="serverId">The guild ID to look for</param>
/// <param name="destination">Where the custom command gets sent to. Either a message in a channel or a DM.</param>
/// <param name="commandName">The name of the command</param>
/// <param name="command">What the command does.</param>
/// <param name="descrption">The description of the command shown in the "CustomCommands" command.</param>
public async static Task AddCustomCommand(ulong serverId, string destination, string commandName, string command, string descrption = "")
{
using (SqliteDbContext DbContext = new SqliteDbContext())
{
try
{ //Check if the guild has a spot
if (DbContext.CustomCommands.Where(x => x.Serverid == serverId && x.CommandName == commandName).Count() < 1)
{
CustomCommands Current = new CustomCommands
{
Serverid = serverId,
Destination = destination,
CommandName = commandName,
Command = command,
CommandDescription = descrption
};
DbContext.CustomCommands.Add(Current);
}
else
{
CustomCommands Current = DbContext.CustomCommands.Where(x => x.Serverid == serverId && x.CommandName == commandName).FirstOrDefault();
Current.Destination = destination;
Current.Command = command;
}
await DbContext.SaveChangesAsync();
}
catch (Exception)
{ }
}
}
/// <summary>
/// Gets the channel to post the mod logs into.
/// </summary>
/// <param name="serverId">The guild ID to look for</param>
/// <returns>A ulong ID of the channel.</returns>
public static ulong GetModLogChannel(ulong serverId)
{
using (SqliteDbContext DbContext = new SqliteDbContext())
{
return(DbContext.GuildLocationSettings.Where(x => x.Serverid == serverId).Select(x => x.ModLogChannel).FirstOrDefault());
}
}
/// <summary>
/// Sets a channel to send all moderator activites the bot is used for into.
/// </summary>
/// <param name="serverId">The guild ID to look for.</param>
/// <param name="channelId">The channel ID to set.</param>
/// <param name="serverName">The name of the guild.</param>
public async static Task SetModLogChannel(ulong serverId, ulong channelId, string serverName)
{
using (SqliteDbContext DbContext = new SqliteDbContext())
{
try
{ //Check if the guild has a spot
if (DbContext.GuildLocationSettings.Where(x => x.Serverid == serverId).Count() < 1)
{
DbContext.GuildLocationSettings.Add(new GuildLocationSettings
{
Serverid = serverId,
WelcomeChannel = 0,
ServerName = serverName,
WelcomeMessage = "",
BotSpamChannel = channelId,
GoldInterest = 5,
ChatLogChannel = 0,
ModLogChannel = 0
});
}
else
{
GuildLocationSettings Current = DbContext.GuildLocationSettings.Where(x => x.Serverid == serverId).FirstOrDefault();
Current.ModLogChannel = channelId;
Current.ServerName = serverName;
DbContext.GuildLocationSettings.Update(Current);
}
await DbContext.SaveChangesAsync();
}
catch (Exception)
{ }
}
}
public async Task Reset(IUser User = null)
{
if (User == null)
{
await Context.Channel.SendMessageAsync("Please specify who you want to reset");
return;
}
if (User.IsBot)
{
return;
}
SocketGuildUser User1 = Context.User as SocketGuildUser;
if (!User1.GuildPermissions.Administrator)
{
await Context.Channel.SendMessageAsync("You dont have admin permission to execute this command");
return;
}
await Context.Channel.SendMessageAsync($":skull: {User.Mention}, you have been reset by {Context.User.Username}! This means you have lost all your clams!");
using (var DbContext = new SqliteDbContext())
{
DbContext.Clams.RemoveRange(DbContext.Clams.Where(x => x.UserID == User.Id));
await DbContext.SaveChangesAsync();
}
}
public static async Task SetAdminRoles(ulong guildId, ulong[] roleIds)
{
try
{
using (var DbContext = new SqliteDbContext())
{
CreateGuildConfig(guildId).GetAwaiter().GetResult();//guild config not found
GuildConfigEntity guild = DbContext.GuildConfig.Where(y => y.GuildId == guildId).FirstOrDefault();
DbContext.AdminRoles.RemoveRange(guild.AdminRoles);
foreach (ulong role in roleIds)
{
DbContext.AdminRoles.Add(new AdminRoleEntity()
{
GuildId = guild.GuildId,
RoleId = role
});
}
await DbContext.SaveChangesAsync();
}
}
catch
{
//TODO: Log exception, more specified description
throw;
}
}
/// <summary>
/// 获取固定的称重勾标
/// </summary>
/// <param name="num"></param>
/// <param name="time"></param>
/// <returns></returns>
public List <string> GetWeightHooks(int num, DateTime time)
{
var hooks = new List <string>();
var stime = new DateTime(time.Year, time.Month, time.Day);
using (var sql = SqliteDbContext.GetInstance())
{
hooks = sql.Queryable <WeightHooks>().Where(s => s.ReadTime > stime).OrderBy(s => s.ReadTime).Select(s => s.HookNumber).Take(num).ToList();
}
if (hooks.Any())
{
if (hooks.Count < num)
{
int diffCount = num - hooks.Count;
for (int i = 0; i < diffCount; i++)
{
var tempTime = time.AddSeconds(i);
hooks.Add(tempTime.ToString("yyMMddHHmmssfff"));
}
}
}
else
{
for (int i = 0; i < num; i++)
{
var tempTime = time.AddSeconds(i);
hooks.Add(tempTime.ToString("yyMMddHHmmssfff"));
}
}
return(hooks);
}
private static async Task CreateGuildConfig(ulong guildId)//creates default guild config
{
try
{
using (var DbContext = new SqliteDbContext())
{
if (DbContext.GuildConfig.Where(x => x.GuildId == guildId).Count() >= 1)
{
return;
}
else
{
DbContext.GuildConfig.Add(new GuildConfigEntity
{
GuildId = guildId,
CommandChannelId = 0,
AlertChannelId = 0,
AdminRoles = new List <AdminRoleEntity>(),
Tasks = new List <Resources.Database.TaskEntity>(),
//DeleteAlertMessageTimespan = 604800,
DateFormat = 0,
Language = 0
});
}
await DbContext.SaveChangesAsync();
}
}
catch
{
//TODO: Log exception, more specified description
throw;
}
}
public static async Task SaveExperience(ulong UserId, int Experience = 10)
{
using (var DbContext = new SqliteDbContext()) {
if (DbContext.Users.Where(x => x.UserID == UserId).Count() < 1)
{
DbContext.Users.Add(new User
{
UserID = UserId,
Stones = 0,
Level = 1,
Experience = 0,
});
}
else
{
User Current = DbContext.Users.Where(x => x.UserID == UserId).FirstOrDefault();
Current.Experience += Experience;
int Level = (int)(MathF.Floor(25 + MathF.Sqrt(625 + 100 * Current.Experience)) / 50);
if (Level != Current.Level)
{
Current.Level += 1;
}
DbContext.Users.Update(Current);
}
await DbContext.SaveChangesAsync();
}
}
public async Task ResetEXP(IUser User = null)
{
if (User == null)
{
await Context.Channel.SendMessageAsync($"You need to specify a user to reset (e.g. ?ResetEXP {Context.User.Mention}");
return;
}
if (User.IsBot)
{
await Context.Channel.SendMessageAsync("Bots cannot be reset.");
return;
}
SocketGuildUser User1 = Context.User as SocketGuildUser;
if (!User1.GuildPermissions.Administrator)
{
await Context.Channel.SendMessageAsync($"You don't have administrator permissions in this server.");
return;
}
await Context.Channel.SendMessageAsync($"{User.Mention}'s experience points have been reset.");
using (var DbContext = new SqliteDbContext())
{
DbContext.ExperiencePoints.RemoveRange(DbContext.ExperiencePoints.Where(x => x.UserId == User.Id));
await DbContext.SaveChangesAsync();
}
}
public static int removeCoins(ulong userID, int coins, int itemID)
{
int ret = 0;
using (var DBContext = new SqliteDbContext())
{
if (DBContext.myUser.Where(x => x.UserID == userID).Count() < 1)
{
DBContext.myUser.Add(new MyUser
{
UserID = userID,
Coins = 0
});
ret = 1;
}
else
{
MyUser current = DBContext.myUser.Where(x => x.UserID == userID).FirstOrDefault();
if (current.Coins >= coins)
{
current.Coins -= coins;
addItem(userID, itemID);
DBContext.myUser.Update(current);
ret = 0;
}
else
{
ret = 1;
}
}
DBContext.SaveChangesAsync();
return(ret);
}
}
private IFrameworkContext CreateContext(ProviderType providerType, IDataStoreScope scope)
{
var optionsBuilder = new DbContextOptionsBuilder <FrameworkContext>();
IFrameworkContext context = null;
switch (providerType)
{
case ProviderType.Sqlite:
var sqliteOptions = scope == null?
optionsBuilder.UseSqlite(_connectionString).Options:
optionsBuilder.UseSqlite(scope.AsDbConnection()).Options;
context = new SqliteDbContext(sqliteOptions);
break;
case ProviderType.MySql:
var mySqlOptions = scope == null?
optionsBuilder.UseMySql(_connectionString).Options:
optionsBuilder.UseMySql(scope.AsDbConnection()).Options;
context = new MySqlDbContext(mySqlOptions);
break;
default:
throw new NotImplementedException($"No provider exists for '{providerType}'.");
}
return(scope != null?
context.UseTransaction(scope.AsTransaction()) :
context);
}
public async Task removeFile(int projectID, int fileID)
{
//check if user is project owner
using (var dbContext = new SqliteDbContext())
{
if (dbContext.Projects.Where(x => x.ProjectId == projectID).Count() < 1)
{
await Context.Channel.SendMessageAsync($"project with id {projectID} do not exists, pleaese try another ID!");
return;
}
else if (dbContext.Files.Where(x => x.ProjectId == projectID && x.FileId == fileID).Count() < 1)
{
await Context.Channel.SendMessageAsync($"There is no file {fileID} in project {projectID}");
return;
}
else if (dbContext.Projects.Where(x => x.ProjectId == projectID).FirstOrDefault().UserId != Context.User.Id)
{
await Context.Channel.SendMessageAsync("You are not the owner of the project, please contact the owner");
}
dbContext.Files.Remove(dbContext.Files.Where(x => x.ProjectId == projectID && x.FileId == fileID).FirstOrDefault());
await Context.Channel.SendMessageAsync($"File {fileID} removed from project {projectID}!");
await dbContext.SaveChangesAsync();
}
}
private async void Connection()
{
SqliteDbContext database = new SqliteDbContext();
Console.WriteLine($"Database Connection is {database.myConnection.State}");
Console.WriteLine($"Database is {database.myConnection.BusyTimeout}");
}
public void IsSqlite_WithSqliteAssemblyLoaded_ShouldReturnExpectedResult()
{
using var dbContext = new SqliteDbContext();
var result = SqliteDetector.IsSqlite(dbContext);
Assert.True(result);
}
private async void SQLConsole()
{
var command = string.Empty;
while (command.Trim() == string.Empty)
{
Console.WriteLine("SQL COMMAND");
command = Console.ReadLine();
SqliteDbContext database = new SqliteDbContext();
string query = $"{command}";
SQLiteCommand myCommand = new SQLiteCommand(query, database.myConnection);
database.OpenConnection();
SQLiteDataReader result = myCommand.ExecuteReader();
if (result.HasRows)
{
while (result.Read())
{
if (command.Contains("*"))
{
Console.WriteLine();
}
else
{
Console.WriteLine(result.ToString());
}
}
}
result.Close();
database.CloseConnection();
}
}
private static void InsertarUsuarioPorConsola()
{
var usuario = new User();
Console.WriteLine("Escribe los siguientes datos");
Console.Write("Nombre de usuario: ");
usuario.Name = Console.ReadLine();
Console.Write("Contraseña: ");
usuario.Password = Console.ReadLine();
Console.Write("Nombre de pila: ");
usuario.FirstName = Console.ReadLine();
Console.Write("Apellidos: ");
usuario.LastName = Console.ReadLine();
Console.Write("Correo electrónico: ");
usuario.Email = Console.ReadLine();
Console.WriteLine($"Rol del usuario: {Role.ToStringList()}");
usuario.RoleId = int.Parse(Console.ReadLine());
using (var db = new SqliteDbContext())
{
db.Add(usuario);
db.SaveChanges();
ImprimirUsuarios();
Console.WriteLine($"El id asignado al usuario {usuario.Name} es {usuario.Id}");
}
}
public IOpenBankingRequestBuilder CreateMockRequestBuilder()
{
HttpClient httpClient = new HttpClient
{
BaseAddress = new Uri(MockRoutes.Url)
};
SqliteDbContext _dB = new SqliteDbContext(_dbContextOptions);
MemoryKeySecretProvider _secretProvider = new MemoryKeySecretProvider();
EntityMapper _entityMapper = new EntityMapper();
RequestBuilder requestBuilder = new RequestBuilder(
entityMapper: _entityMapper,
dbContextService: new DbMultiEntityMethods(_dB),
configurationProvider: new DefaultConfigurationProvider(),
logger: new ConsoleInstrumentationClient(),
keySecretReadOnlyProvider: _secretProvider,
apiClient: new ApiClient(httpClient),
certificateReader: new PemParsingCertificateReader(),
clientProfileRepository: new DbEntityRepository <BankClientProfile>(_dB),
domesticConsentRepo: new DbEntityRepository <DomesticConsent>(_dB),
apiProfileRepository: new DbEntityRepository <ApiProfile>(_dB),
activeSReadOnlyRepo: new KeySecretReadRepository <ActiveSoftwareStatementProfiles>(_secretProvider),
activeSrRepo: new KeySecretWriteRepository <ActiveSoftwareStatementProfiles>(_secretProvider),
sReadOnlyRepo: new KeySecretMultiItemReadRepository <SoftwareStatementProfile>(_secretProvider),
sRepo: new KeySecretMultiItemWriteRepository <SoftwareStatementProfile>(_secretProvider),
softwareStatementProfileService: new SoftwareStatementProfileService(
softwareStatementProfileRepo:
new KeySecretMultiItemReadRepository <SoftwareStatementProfile>(_secretProvider),
activeSoftwareStatementProfilesRepo: new KeySecretReadRepository <ActiveSoftwareStatementProfiles>(
_secretProvider),
mapper: _entityMapper));
return(requestBuilder);
}
public async Task Reset(IUser User = null)
{
//checks
if (User == null)
{
await Context.Channel.SendMessageAsync($":x: You need to tell me which user you want to reset the flowers of! !flowers reset {Context.User.Mention}");
return;
}
if (User.IsBot)
{
await Context.Channel.SendMessageAsync(":x: Bots can't use this bot, so you also can't reset the progress of bots! :robot:");
}
SocketGuildUser User1 = Context.User as SocketGuildUser;
if (!User1.GuildPermissions.Administrator)
{
await Context.Channel.SendMessageAsync($":x: You don't have administrator permissions in this discord server! Ask a moderator or the owner to execute this command!");
return;
}
//Execution
await Context.Channel.SendMessageAsync($":skull: {User.Mention}, you have been reset by {Context.User.Username}! This means you lost all of your flowers!");
//Saving the database
using (var DbContext = new SqliteDbContext())
{
DbContext.Flowers.RemoveRange(DbContext.Flowers.Where(x => x.UserId == User.Id));
await DbContext.SaveChangesAsync();
}
}
/// <summary>
/// Remove warnings from the user's amountr
/// </summary>
/// <param name="userId">The user ID to look for</param>
/// <param name="serverId">The guild ID to look for</param>
/// <param name="username">The username of the user</param>
/// <param name="amount">The amount of warnings to remove, defaulted at 1</param>
/// <returns></returns>
public static async Task RemoveWarnings(ulong userId, ulong serverId, string username, int amount = 1)
{
using (SqliteDbContext DbContext = new SqliteDbContext())
{
//Check if the guild has a spot
if (DbContext.Warnings.Where(x => x.UserId == userId && x.Serverid == serverId).Count() > 0)
{
//Check if the spot has an amount
if (DbContext.Warnings.Where(x => x.UserId == userId && x.Serverid == serverId).Select(x => x.AmountOfWarnings).FirstOrDefault() > 0)
{
Warning Current = DbContext.Warnings.Where(x => x.UserId == userId && x.Serverid == serverId).FirstOrDefault();
Current.AmountOfWarnings -= amount;
if (Current.AmountOfWarnings < 0)
{
Current.AmountOfWarnings = 0;
}
DbContext.Warnings.Update(Current);
}
else
{
return;
}
}
else
{
return;
}
await DbContext.SaveChangesAsync();
}
}
