/// <summary>
/// Adds new pending verification form record
/// </summary>
/// <param name="form">Verification form</param>
/// <returns></returns>
public static async Task AddPendingVerificationForm(VerificationForm form)
{
using (var DbContext = new SQLiteDatabaseContext())
{
var query = DbContext.VerificationDB.Where(x =>
x.GuildId == form.GuildId &&
x.Verified == form.Verified &&
x.IsApproved == null
);
if (query.Count() < 1)
{
var submit = new VerificationForm
{
GuildId = form.GuildId,
Verified = form.Verified,
Verifier = form.Verifier,
IssuedUtc = DateTime.UtcNow
};
DbContext.VerificationDB.Add(submit);
await DbContext.SaveChangesAsync();
}
else
{
throw new VerificationFormExistsException();
}
}
}
/// <summary>
/// Returns the pending verification forms for a guild
/// </summary>
/// <param name="guildId">Snowflake ID of the guild</param>
/// <returns></returns>
public static IList <VerificationForm> GetPendingVerificationFormsByGuild(ulong guildId)
{
using (var DbContext = new SQLiteDatabaseContext())
{
return(DbContext.VerificationDB.Where(x => x.GuildId == guildId && x.IsApproved == null).ToList());
}
}
public static DiscordGuildUser GetGuildUserById(ulong UserId, ulong GuildId)
{
using (var DbContext = new SQLiteDatabaseContext())
{
return(DbContext.DiscordUsersDB.Where(x => x.UserId == UserId && x.GuildId == GuildId).FirstOrDefault());
}
}
public static IList <DiscordGuildUser> GetGuildUsers(ulong GuildId)
{
using (var DbContext = new SQLiteDatabaseContext())
{
return(DbContext.DiscordUsersDB.Where(x => x.GuildId == GuildId).ToList());
}
}
public string ReadRandomCustomerListORM(string dbpath, int n)
{
IList <string> lstClienti = new List <string>();
string ret = "Error reading DB";
try
{
//var ctx = new SQLiteDatabaseContext(dbpath);
using (var ctx = new SQLiteDatabaseContext(dbpath))
{
lstClienti = ctx.Database.SqlQuery <string>($"SELECT DISTINCT CUSTOMER FROM ORDINI ORDER BY random() LIMIT {n}").ToList();
}
// legge solo alcuni clienti (si poteva fare tutto nella query)
IList <string> lstOutStrings = new List <string>();
foreach (var client in lstClienti)
{
lstOutStrings.Add("'" + client + "'"); //add it to the new, random list
}
ret = string.Join(",", lstOutStrings);
}
catch (Exception ex)
{
Trace.WriteLine($"Error: {ex.Message}");
}
Trace.WriteLine(ret);
return(ret);
}
public static async Task AddGuildUser(ulong UserId, ulong GuildId, DiscordGuildUser.PermissionLevels permission)
{
using (var DbContext = new SQLiteDatabaseContext())
{
var query = DbContext.DiscordUsersDB.Where(x => x.UserId == UserId && x.GuildId == GuildId);
if (query.Count() < 1)
{
var user = new DiscordGuildUser
{
UserId = UserId,
GuildId = GuildId,
PermissionLevel = permission
};
DbContext.DiscordUsersDB.Add(user);
await DbContext.SaveChangesAsync();
}
else
{
var user = query.FirstOrDefault();
if (user.PermissionLevel != permission)
{
user.PermissionLevel = permission;
DbContext.DiscordUsersDB.Update(user);
await DbContext.SaveChangesAsync();
}
}
}
}
public List <string> ReadAllCustomers(string dbPath)
{
List <string> lstClienti = new List <string>();
List <string> ret = new List <string>();
try
{
//var ctx = new SQLiteDatabaseContext(dbpath);
using (var ctx = new SQLiteDatabaseContext(dbPath))
{
lstClienti = ctx.Database.SqlQuery <string>(@"SELECT DISTINCT customer from ordini ORDER BY customer").ToList();
}
foreach (string s in lstClienti)
{
ret.Add("'" + s + "'");
}
}
catch (Exception ex)
{
Trace.WriteLine($"Error: {ex.Message}");
}
Trace.WriteLine("[Persistance] customer count = " + ret.Count());
return(ret);
}
public static async Task AddGuildRole(ulong roleId, ulong guildId, DiscordRole.ActionType action)
{
using (var DbContext = new SQLiteDatabaseContext())
{
var query = DbContext.DiscordRolesDb.Where(x => x.RoleId == roleId && x.GuildId == guildId);
if (query.Count() < 1)
{
var role = new DiscordRole
{
RoleId = roleId,
GuildId = guildId,
Action = action
};
DbContext.DiscordRolesDb.Add(role);
await DbContext.SaveChangesAsync();
}
else
{
var role = query.FirstOrDefault();
if (role.Action != action)
{
role.Action = action;
DbContext.DiscordRolesDb.Update(role);
await DbContext.SaveChangesAsync();
}
}
}
}
public static DiscordRole GetGuildRoleById(ulong roleId, ulong guildId)
{
using (var DbContext = new SQLiteDatabaseContext())
{
return(DbContext.DiscordRolesDb.Where(x => x.RoleId == roleId && x.GuildId == guildId).FirstOrDefault());
}
}
public static IList <DiscordRole> GetGuildRoles(ulong guildId)
{
using (var DbContext = new SQLiteDatabaseContext())
{
return(DbContext.DiscordRolesDb.Where(x => x.GuildId == guildId).ToList());
}
}
/// <summary>
/// Returns all verification forms for a guild
/// </summary>
/// <param name="guildId">Snowflake ID of the guild</param>
/// <param name="ammount">Number of verification forms to return sorted from the most recent one</param>
/// <returns></returns>
public static IList <VerificationForm> GetVerificationFormsByGuild(ulong guildId, int rows)
{
using (var DbContext = new SQLiteDatabaseContext())
{
return(DbContext.VerificationDB.Where(x => x.GuildId == guildId).OrderByDescending(x => x.IssuedUtc).Take(rows).ToList());
}
}
public override async Task <bool> Update(Assistant entity)
{
await using var ctx = new SQLiteDatabaseContext();
ctx.Update(entity);
return(await ctx.SaveChangesAsync() > 0);
}
// TODO: Clean this up and use an algorithm that is scalable for child items
public async Task UpdateAsync(Notebook item)
{
try
{
SQLiteDatabaseContext context = database.Context;
context.SaveChanges();
Notebook existingNotebook = context.Notebooks
.Where(n => n.ID == item.ID)
.Include(n => n.Stacks)
.SingleOrDefault();
if (existingNotebook == null)
{
throw new Exception($"The notebook with ID { item.ID } could not be found");
}
context.Entry(existingNotebook).CurrentValues.SetValues(item);
// Delete children
foreach (Stack existingStack in existingNotebook.Stacks)
{
if (!item.Stacks.Any(s => s.ID == existingStack.ID))
{
context.Stacks.Remove(existingStack);
}
}
// Update and insert children
foreach (Stack stack in item.Stacks)
{
Stack existingStack = existingNotebook.Stacks
.Where(s => s.ID == stack.ID && s.ID != default)
.SingleOrDefault();
if (existingStack == null)
{
// Insert child
existingNotebook.Stacks.Add(stack);
}
else
{
// Update child
context.Entry(existingStack).CurrentValues.SetValues(stack);
}
await UpdateStackAsync(stack);
}
await context.SaveChangesAsync();
}
catch (Exception e)
{
InformationDispatcher.Default.Dispatch(e); // TODO: Remove
throw new DataSourceException("Could not update notebook", e);
}
}
private async Task UpdateStackAsync(Stack item)
{
try
{
SQLiteDatabaseContext context = database.Context;
Stack existingStack = context.Stacks
.Where(s => s.ID == item.ID)
.Include(s => s.Fields)
.SingleOrDefault();
if (existingStack == null)
{
return;
}
context.Entry(existingStack).CurrentValues.SetValues(item);
// Delete children
foreach (CustomField existingField in existingStack.Fields)
{
if (!item.Fields.Any(f => f.ID == existingField.ID))
{
context.Fields.Remove(existingField);
}
}
// Update and insert children
foreach (CustomField field in item.Fields)
{
CustomField existingField = existingStack.Fields
.Where(f => f.ID == field.ID && f.ID != default)
.SingleOrDefault();
if (existingField == null)
{
// Insert child
existingStack.Fields.Add(field);
}
else
{
// Update child
context.Entry(existingField).CurrentValues.SetValues(field);
}
}
await context.SaveChangesAsync();
}
catch (Exception e)
{
InformationDispatcher.Default.Dispatch(e); // TODO: Remove
throw new DataSourceException("Could not update stack", e);
}
}
public static async Task RemoveGuildRole(ulong roleId, ulong guildId)
{
using (var DbContext = new SQLiteDatabaseContext())
{
DbContext.DiscordRolesDb.RemoveRange(
DbContext.DiscordRolesDb.Where(x =>
x.RoleId == roleId &&
x.GuildId == guildId
));
await DbContext.SaveChangesAsync();
}
}
public static async Task RemoveGuildUser(DiscordGuildUser user)
{
using (var DbContext = new SQLiteDatabaseContext())
{
DbContext.DiscordUsersDB.RemoveRange(
DbContext.DiscordUsersDB.Where(x =>
x.UserId == user.UserId &&
x.GuildId == user.GuildId
));
await DbContext.SaveChangesAsync();
}
}
// Reads an instance from the db
public GAPclass ReadGAPinstance(string dbpath)
{
int i, j;
List <int> lstCap;
List <double> lstCosts;
GAPclass G = new GAPclass();
try
{
using (var ctx = new SQLiteDatabaseContext(dbpath))
{
lstCap = ctx.Database.SqlQuery <int>("SELECT cap from capacita").ToList();
G.m = lstCap.Count();
G.cap = new int[G.m];
for (i = 0; i < G.m; i++)
{
G.cap[i] = lstCap[i];
}
lstCosts = ctx.Database.SqlQuery <double>("SELECT cost from costi").ToList();
G.n = lstCosts.Count / G.m;
G.c = new double[G.m, G.n];
G.req = new int[G.n];
G.sol = new int[G.n];
G.solbest = new int[G.n];
G.zub = Double.MaxValue;
G.zlb = Double.MinValue;
for (i = 0; i < G.m; i++)
{
for (j = 0; j < G.n; j++)
{
G.c[i, j] = lstCosts[i * G.n + j];
}
}
for (j = 0; j < G.n; j++)
{
G.req[j] = -1; // placeholder
}
}
}
catch (Exception ex)
{
Trace.WriteLine("[readGAPinstance] Error:" + ex.Message);
}
Trace.WriteLine("Fine lettura dati istanza GAP");
return(G);
}
public bool CustomerExist(string dbpath, string customerID)
{
int count = 0;
try
{
//var ctx = new SQLiteDatabaseContext(dbpath);
using (var ctx = new SQLiteDatabaseContext(dbpath))
{
count = ctx.Database.SqlQuery <int>(@"SELECT COUNT(*) from ordini where customer='" + customerID + "'").First();
}
} catch (Exception ex)
{
Trace.WriteLine($"Error: {ex.Message}");
}
return(count > 0 ? true : false);
}
public static async Task RemoveGuildRole(DiscordRole role)
{
if (role is null)
{
throw new ArgumentNullException(nameof(role));
}
using (var DbContext = new SQLiteDatabaseContext())
{
DbContext.DiscordRolesDb.RemoveRange(
DbContext.DiscordRolesDb.Where(x =>
x.RoleId == role.RoleId &&
x.GuildId == role.GuildId
));
await DbContext.SaveChangesAsync();
}
}
/// <summary>
/// Adds new Verification form record if a pending record does not exist or updates the existing pending record
/// </summary>
/// <param name="form">Verification Form record</param>
/// <returns></returns>
public static async Task AddFullVerificationForm(VerificationForm form)
{
using (var DbContext = new SQLiteDatabaseContext())
{
var query = DbContext.VerificationDB.Where(x =>
x.GuildId == form.GuildId &&
x.Verified == form.Verified &&
x.IsApproved == null
);
if (query.Count() < 1)
{
if (form.IssuedUtc == null)
{
form.IssuedUtc = DateTime.UtcNow;
}
if (form.ApprovedUtc == null)
{
form.ApprovedUtc = DateTime.UtcNow;
}
DbContext.VerificationDB.Add(form);
await DbContext.SaveChangesAsync();
}
else
{
var qForm = query.First();
if (form.IsApproved != null)
{
qForm.IsApproved = form.IsApproved;
qForm.Approver = form.Approver;
qForm.ApprovedUtc = form.ApprovedUtc ?? DateTime.UtcNow;
DbContext.VerificationDB.Update(qForm);
await DbContext.SaveChangesAsync();
}
}
}
}
public string ReadOrderByCustomer(string dbpath, string customerID)
{
List <int> lstClienti;
string ret = "Error reading DB";
try
{
//var ctx = new SQLiteDatabaseContext(dbpath);
using (var ctx = new SQLiteDatabaseContext(dbpath))
{
lstClienti = ctx.Database.SqlQuery <int>(@"SELECT sum(quant) from ordini where customer='" + customerID + "'").ToList();
}
ret = string.Join(",", lstClienti);
}
catch (Exception ex)
{
Trace.WriteLine($"Error: {ex.Message}");
}
Trace.WriteLine(ret);
return(ret);
}
public Program()
{
Console.CancelKeyPress += (sender, eArgs) =>
{
_quitEvent.Set();
eArgs.Cancel = true;
};
//Creates and/or Updates the database when the program is strated
using (var DbContext = new SQLiteDatabaseContext())
{
DbContext.Database.Migrate();
//int maxRetries = 3;
//for(int i = 0; i < maxRetries; i++)
//{
// try
// {
// DbContext.Database.Migrate();
// break;
// }
// catch (System.NotSupportedException e)
// {
// DbContext.Database.EnsureDeleted();
// }
//}
}
Settings settings;
using (var DH = new SettingsDataHandler())
{
settings = DH.GetSettings();
#region Logger Creation
//Gets and converts Log Levels to match between Serilog and integrated Discord.Net Logger
var LogLevelSerilog = Serilog.Events.LogEventLevel.Information;
var LogLevelDiscord = LogSeverity.Info;
if (Enum.IsDefined(typeof(Serilog.Events.LogEventLevel), settings.LogLevel))
{
LogLevelSerilog = (Serilog.Events.LogEventLevel)settings.LogLevel;
new LogLevelConverter().SerilogToDiscordNet(LogLevelSerilog, out LogLevelDiscord);
}
LoggingLevelSwitch levelSwitch = new LoggingLevelSwitch()
{
MinimumLevel = LogLevelSerilog
};
var loggerConfiguration = new LoggerConfiguration()
.MinimumLevel.Is(LogLevelSerilog)
.Enrich.With(new ThreadIdEnricher())
.Enrich.With(new ProcessIdEnricher())
.WriteTo.Console(
outputTemplate: "{Timestamp:HH:mm} [{Level}] [{ProcessId}-{ThreadId}] {Message}{NewLine}{Exception}");
if (Convert.ToBoolean(settings.RollingLogRetainedFiles))
{
loggerConfiguration.WriteTo.File(
path: DH.GetLogFilePath(),
rollingInterval: RollingInterval.Day,
retainedFileCountLimit: settings.RollingLogRetainedFiles,
outputTemplate: "[{Timestamp:HH:mm:ss} {Level}] {Message}{NewLine}{Exception}");
}
Log.Logger = loggerConfiguration.CreateLogger();
Log.Debug("Logger Created");
#endregion
_client = new DiscordSocketClient(new DiscordSocketConfig
{
LogLevel = LogLevelDiscord
});
_commandService = new CommandService(new CommandServiceConfig
{
CaseSensitiveCommands = false,
DefaultRunMode = RunMode.Async,
LogLevel = LogLevelDiscord
});
}
_serviceProvider = new ServiceProviderFactory(_client, _commandService).Build();
_commandHandler = new CommandHandler(
client: _client,
commandService: _commandService,
serviceProvider: _serviceProvider,
replyService: _serviceProvider.GetRequiredService <IReplyService>()
);
_client.Log += OnClientLogAsync;
_client.Ready += OnClientReadyAsync;
}
public CRUDdb(string pathDB)
{
db = new SQLiteDatabaseContext();
}
