public static async Task <Result> ChangePassword(int id, string o, string n, bool passwordReset = false)
{
using var c = new DBContext(await NsslEnvironment.OpenConnectionAsync(), false);
var k = await Q.From(User.T).Where(x => x.Id.EqV(id)).FirstOrDefault <User>(c.Connection);// c.Users.FirstOrDefault(x => x.Id == id);
if (k.PasswordHash.SequenceEqual(Salting(o, k.Salt)) || passwordReset)
{
var ctc = ChangeTrackingContext.StartWith(k);
k.PasswordHash = Salting(n, k.Salt);
await ctc.Commit(c.Connection);
c.Connection.Close();
}
else
{
c.Connection.Close();
return(new Result {
Success = false, Error = "old password was incorrect"
});
}
return(new Result {
Success = true
});
}
public static async Task <CreateResult> CreateUser(string username, string email, string pwdhash)
{
using var cont = new DBContext(await NsslEnvironment.OpenConnectionAsync(), false);
var exists = await FindUserByName(cont.Connection, username);
if (exists != null)
{
//await ChangePassword(exists.Id, "2", pwdhash);
return(new CreateResult {
Success = false, Error = "Username already taken"
});
}
exists = await FindUserByEmail(cont.Connection, email);
if (exists != null)
{
return new CreateResult {
Success = false, Error = "Email already in use"
}
}
;
var minedsalt = GenerateSalt();
var saltedpw = Salting(pwdhash, minedsalt);
User c = new User(username.TrimEnd(), saltedpw, email.TrimEnd(), minedsalt);
await Q.InsertOne(cont.Connection, c);
return(new CreateResult {
Success = true, Id = c.Id, EMail = c.Email, Username = c.Username
});
}
public async static Task AddProduct(string name, string gtin, decimal?quantity, string unit)
{
using (var con = new DBContext(await NsslEnvironment.OpenConnectionAsync(), true))
using (var tx = con.Connection.BeginTransaction())
{
var p = new Product {
Name = name, ProviderKey = 0, Quantity = quantity, Unit = unit,
};
p.Fitness = name == name.ToUpper() ? (short)5 : (short)10;
p.Fitness += quantity == null ? (short)0 : (short)5;
p.Fitness += string.IsNullOrWhiteSpace(unit) ? (short)0 : (short)5;
p.Fitness += string.IsNullOrWhiteSpace(
gtin) ? (short)0 : (short)10;
await Q.InsertOne(con.Connection, tx, p);
var g = new GtinEntry {
Gtin = gtin
};
await Q.InsertOne(con.Connection, tx, g);
var pg = new ProductsGtins {
GtinId = g.Id, ProductId = p.Id
};
await Q.InsertOne(con.Connection, tx, pg);
tx.Commit();
con.Connection.Close();
}
}
public async Task OnActionExecutionAsync(ActionExecutingContext context, ActionExecutionDelegate next)
{
var controller = (BaseDbContextController)context.Controller;
using (controller.Context = new DBContext(await NsslEnvironment.OpenConnectionAsync(), true))
{
await next.Invoke();
controller.Context.Connection.Close();
}
//await controller.Context.SaveChangesAsync();
}
public static async Task <Result> SendPasswortResetEmail(string email)
{
using var con = new DBContext(await NsslEnvironment.OpenConnectionAsync(), false);
User exists = null;
if (email != null)
{
exists = await FindUserByName(con.Connection, email);
}
if (exists == null)
{
exists = await FindUserByEmail(con.Connection, email);
if (exists == null)
{
con.Connection.Close();
return(new Result {
Success = false, Error = "user could not be found"
});
}
}
var sender = new OutlookDotComMail(mailUser, mailUserPwd);
var payload = new Dictionary <string, object>()
{
{ "Expires", DateTime.UtcNow.AddDays(1) },
{ "Id", exists.Id },
{ "Created", DateTime.UtcNow }
};
var token = JsonWebToken.Encode(new Dictionary <string, object>(), payload, JwtKeyProvider.SecretKey, JsonWebToken.JwtHashAlgorithm.HS256);
var tokenUser = new TokenUserId(token, exists.Id);
tokenUser.Timestamp = DateTime.UtcNow;
await Q.InsertOne(con.Connection, tokenUser);
sender.SendMail(exists.Email, "NSSL Password Reset",
$"Dear {exists.Username},\r\n\r\n" +
"This email was automatically sent following your request to reset your password.\r\n" +
"To reset your password, click this link or paste it into your browser's address bar:\r\n" +
"https://nssl.susch.eu/password/site/reset?token=" + token +
"\r\n\r\n" +
"If you did not forget your password, please ignore this email. Thank you.\r\n\r\n" +
"Kind Regards,\r\n" +
"NSSL Team");
con.Connection.Close();
return(new Result {
Success = true, Error = ""
});
}
public async Task <Product> FindProductByCode(string code)
{
using (var con = new DBContext(await NsslEnvironment.OpenConnectionAsync(), true))
{
var ret = await From(GtinEntry.T)
.Where(x => x.Gtin.EqV(code))
.InnerJoin(ProductsGtins.T).On((g, pc) => g.Id.Eq(pc.GtinId))
.InnerJoin(Product.T).On((_, pc, p) => p.Id.Eq(pc.ProductId)).Select((g, pc, p) => new RawSql($"{p.TableAlias}.*"))
.OrderBy((_, __, p) => p.Fitness.Desc())
.FirstOrDefault <Product>(con.Connection);
con.Connection.Close();
return(ret);
}
}
public static async Task <LoginResult> Login(string username, string email, string passwordhash)
{
using var con = new DBContext(await NsslEnvironment.OpenConnectionAsync(), false);
User exists = null;
if (username != null)
{
exists = await FindUserByName(con.Connection, username);
}
if (exists == null)
{
if (email == null)
{
con.Connection.Close();
return(new LoginResult {
Success = false, Error = "user could not be found"
});
}
exists = await FindUserByEmail(con.Connection, email);
if (exists == null)
{
con.Connection.Close();
return(new LoginResult {
Success = false, Error = "user could not be found"
});
}
}
if (!Salting(passwordhash, exists.Salt).SequenceEqual(exists.PasswordHash))
{
con.Connection.Close();
return(new LoginResult {
Success = false, Error = "password is incorrect"
});
}
var payload = new Dictionary <string, object>()
{
{ "Expires", DateTime.UtcNow.AddMonths(1) },
{ "Id", exists.Id },
{ "Created", DateTime.UtcNow }
};
con.Connection.Close();
return(new LoginResult {
Success = true, Error = "", Token = JsonWebToken.Encode(new Dictionary <string, object>(), payload, JwtKeyProvider.SecretKey, JsonWebToken.JwtHashAlgorithm.HS256), Id = exists.Id, EMail = exists.Email, Username = exists.Username
});
}
public static async Task <Result> ResetPassword(string token, string n)
{
using var c = new DBContext(await NsslEnvironment.OpenConnectionAsync(), false);
var rpt = await Q.From(TokenUserId.T).Where(x => x.Timestamp.GtV(DateTime.UtcNow.AddDays(-1)).And(x.ResetToken.EqV(token))).FirstOrDefault <TokenUserId>(c.Connection);
if (rpt == null)
{
return new Result {
Success = false, Error = "Token Expired or password reset was not requested"
}
}
;
var user = await Q.From(T).Where(x => x.Id.EqV(rpt.UserId)).FirstOrDefault <User>(c.Connection);
if (user == null)
{
return new Result {
Success = false, Error = "User for the token doesn't exists anymore"
}
}
;
await ChangePassword(user.Id, "", n, true);
await Q.DeleteFrom(TokenUserId.T).Where(x => x.Timestamp.EqV(rpt.Timestamp).And(x.ResetToken.EqV(rpt.ResetToken).And(x.UserId.EqV(rpt.UserId)))).Execute(c.Connection);
var sender = new OutlookDotComMail(mailUser, mailUserPwd);
sender.SendMail(user.Email, "NSSL Password Reset",
$@"Dear {user.Username},
This email was sent to you, because you have successfully changed your password.
If it wasn't you, than this might be an indicator, that someone has access to your email account.
Kind Regards,
NSSL Team");
c.Connection.Close();
return(new Result {
Success = true
});
}
public static async Task <ShoppingList> LoadShoppingList(int listId, bool alreadyBought, int userId)
{
using (var con = new DBContext(await NsslEnvironment.OpenConnectionAsync(), true))
{
var list = await Q.From(Contributor.T)
.InnerJoin(ShoppingList.T).On((c, sl) => c.ListId.Eq(sl.Id))
.Where(
(c, sl) => c.UserId.EqV(userId),
(c, sl) => sl.Id.EqV(listId)
)
.Select(new RawSql(ShoppingList.T.TableAlias + ".*")).Limit(1)
.FirstOrDefault <ShoppingList>(con.Connection);
if (list == null)
{
return new ShoppingList {
}
}
; //TODO Nicht leere Liste zurückgeben
var tempQuery = Q.From(ListItem.T).Where(l => l.ListId.EqV(list.Id)).OrderBy(t => t.Id.Asc());
if (!alreadyBought)
{
list.Products = await tempQuery.Where(l => l.Amount.Neq(Q.P("a", 0))).ToList <ListItem>(con.Connection);
}
else
{
list.Products = await tempQuery.Where(l => l.Amount.Eq(Q.P("a", 0))).ToList <ListItem>(con.Connection);
foreach (var item in list.Products)
{
item.Amount = item.BoughtAmount;
}
}
//list.Products = await Q.From(ListItem.T).Where(l => l.ListId.EqV(list.Id))
// .OrderBy(t => t.Id.Asc())
// .Where(l => l.Amount.Neq(Q.P("a", 0)))
// .ToList<ListItem>(con.Connection);
con.Connection.Close();
return(list);
}
}
public async Task <Paged <Product> > FindProductsByName(string name, int page = 1)
{
using (var con = new DBContext(await NsslEnvironment.OpenConnectionAsync(), true))
{
var tsQuery = string.Join(" & ", name.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries));
var q = From(Product.T)
.Where(a => ToTsVector("german", a.Name)
.Match(ToTsQuery("german", P("qry", tsQuery))));
const int perPage = 30;
var total = await q.Select(a => Count(a.Id)).ScalarResult <long>(con.Connection);
var items2 = q.OrderBy(a => Lower(a.Name).Asc(), a => a.Fitness.Desc()).Limit(perPage, (page - 1) * perPage);
var items = await items2.Select(new RawSql("distinct on (lower(pt.name)) *")).ToList <Product>(con.Connection);
con.Connection.Close();
return(items.Paged(total, page, perPage));
}
}
//public async Task<List<BasicProduct>> FindProductsByName(string name)
// =>( await From(EPT).Where(EPT.Name.Like(P("gtin", "%" + name +"%"), LikeMode.IgnoreCase)).Limit(30).ToList<EdekaProduct>(await OpenConnection())).Select(p => p.ConvertToProduct()).ToList();
public async Task <Paged <IDatabaseProduct> > FindProductsByName(string name, int page = 1)
{
using (var con = await NsslEnvironment.OpenConnectionAsync())
{
var tsQuery = string.Join(" & ", name.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries));
var q = Q.From(EPT)
.Where(a => Q.ToTsVector("German", Q.Concat(a.Name, a.LongDescription, a.ShortDescription))
.Match(Q.ToTsQuery("German", Q.P("qry", tsQuery))));
//.Where(EPT.Name.Like(P("gtin", "%" + name + "%"), LikeMode.IgnoreCase));
const int perPage = 30;
var total = await q.Select(a => Q.Count(a.Id)).ScalarResult <long>(con);
var items = await q.OrderBy(a => a.Name.Asc()).Limit(perPage, (page - 1) * perPage).ToList <EdekaProduct>(con);
return(items.PagedAs <EdekaProduct, IDatabaseProduct>(total, page, perPage));
}
}