public static User GetUserByUsername(string username)
{
using (var db = new ChaliceDb())
{
return(db.Users.FirstOrDefault(u => u.UserName == username));
}
}
/// <summary>
/// Recounts the amount of up and downvotes for a glyph and updates the data
/// </summary>
/// <param name="glyph"></param>
public static void RebuildGlyphVotes(string glyph)
{
using (var db = new ChaliceDb())
{
db.Query <int>($"WITH Votes (Up, Down, Closed) AS (SELECT COALESCE(SUM(CASE WHEN [Value] = 'up' THEN 1 ELSE 0 END), 1) AS Up, COALESCE(SUM(CASE WHEN [Value] = 'down' THEN 1 ELSE 0 END), 0) AS Down, COALESCE(SUM(CASE WHEN [VALUE] = 'closed' THEN 1 ELSE 0 END), 0) AS Closed FROM UserHistory WHERE [Target] = '{glyph}' AND [Action] = 'vote') UPDATE DungeonGlyphs SET Upvotes = (SELECT CASE WHEN Up = 0 THEN 1 ELSE Up END FROM Votes), Downvotes = (SELECT Down FROM Votes), Closedvotes = (SELECT Closed FROM Votes) WHERE Glyph = '{glyph}'");
}
}
/// <summary>
/// Checks if loot exists, if not, create an entry for it
/// </summary>
/// <param name="lootIds">A list of numbers, if a string is passed, then create a new entry for it</param>
/// <returns>A string containing only loot ids</returns>
public static string VerifyLootList(string[] lootIds)
{
using (var db = new ChaliceDb())
{
var lootList = new List <int>();
foreach (var loot in lootIds)
{
if (int.TryParse(loot, out int itemId) == false)
{
var lootEntry = new Loot
{
ItemName = loot,
Updated = DateTime.Now
};
lootList.Add(Convert.ToInt32(db.InsertWithIdentity(lootEntry)));
}
else
{
var entry = db.Loot.FirstOrDefault(l => l.Id == int.Parse(loot));
// If it doesn't, add entry
if (entry == null)
{
// Shouldn't happen, but if it happens often, consider checking/adding here again
throw new Exception("Item should be added to db");
}
lootList.Add(entry.Id);
}
}
return(string.Join(';', lootList));
}
}
public static void RemoveVote(UserHistory voteEntry)
{
using (var db = new ChaliceDb())
{
db.BeginTransaction();
db.Delete(voteEntry);
db.CommitTransaction();
}
}
public static IEnumerable <UserHistory> GetUserHistory(string userName)
{
using (var db = new ChaliceDb())
{
return(db.UserHistory
.Where(u => u.UserName == userName)
.OrderBy(u => u.Created)
.ToList());
}
}
public static string NewGlyphFromModel(SubmitGlyphModel glyph)
{
var hs = new Ganss.XSS.HtmlSanitizer();
var nGlyph = new DungeonGlyph
{
Glyph = hs.Sanitize(glyph.DungeonGlyph),
ShortDescription = hs.Sanitize(glyph.ShortDescription),
Layers = glyph.Layers,
RootChalice = glyph.RootChalice,
Fetid = glyph.Fetid,
Rotted = glyph.Rotted,
Cursed = glyph.Cursed,
Sinister = glyph.Sinister,
SaveEdited = glyph.SaveEdited,
Bosses = $";{string.Join(';', glyph.Bosses)};",
Notes = hs.Sanitize(glyph.Notes),
Submitter = glyph.Submitter,
Upvotes = 1,
Downvotes = 0,
Updated = DateTime.Now
};
using (var db = new ChaliceDb())
{
// Check if glyph exists
if (db.DungeonGlyphs.Any(g => g.Glyph == nGlyph.Glyph))
{
return("<EXISTS>");
}
nGlyph.Loot = $";{VerifyLootList(glyph.Loot)};";
db.Insert(nGlyph);
db.InsertWithIdentity(new UserHistory
{
UserName = nGlyph.Submitter,
Target = nGlyph.Glyph,
Action = "new_glyph",
Value = nGlyph.Glyph,
Created = DateTime.Now
});
return(nGlyph.Glyph);
}
}
public static bool RegisterNewUser(NewUserRegistrationModel registration)
{
var user = new User
{
UserName = new Ganss.XSS.HtmlSanitizer().Sanitize(registration.UserName),
Password = Security.GetPasswordHashString(registration.Password),
Email = registration.Email,
Role = "user",
Created = DateTime.Now
};
var history = new UserHistory
{
UserName = user.UserName,
Target = "account",
Action = "created",
Value = user.UserName,
Created = DateTime.Now
};
using (var db = new ChaliceDb())
{
var userEntry = db.Users.FirstOrDefault(d => d.UserName == user.UserName || d.Email == user.Email);
if (userEntry != null)
{
return(false);
}
db.BeginTransaction();
db.InsertWithIdentity(user);
db.InsertWithIdentity(history);
db.CommitTransaction();
}
return(true);
}
internal static bool UpdateGlyphFromModel(SubmitGlyphModel glp)
{
var hs = new Ganss.XSS.HtmlSanitizer();
using (var db = new ChaliceDb())
{
db.DungeonGlyphs
.Where(d => d.Glyph == glp.DungeonGlyph)
.Set(d => d.RootChalice, hs.Sanitize(glp.RootChalice))
.Set(d => d.Fetid, glp.Fetid)
.Set(d => d.Rotted, glp.Rotted)
.Set(d => d.Cursed, glp.Cursed)
.Set(d => d.Sinister, glp.Sinister)
.Set(d => d.SaveEdited, glp.SaveEdited)
.Set(d => d.Bosses, $";{string.Join(';', glp.Bosses)};")
.Set(d => d.Loot, $";{VerifyLootList(glp.Loot)};")
.Set(d => d.Notes, hs.Sanitize(glp.Notes))
.Set(d => d.Updated, DateTime.Now)
.Update();
}
return(true);
}
public static GeneralSearchModel SearchGlyph(string query, string type)
{
using (var db = new ChaliceDb())
{
var glyphResults = new List <SearchResultEntry>();
var lootResults = new List <Loot>();
// If it's a directly matching glyph, just return it immediately instead of searching on
var entry = db.DungeonGlyphs.FirstOrDefault(d => d.Glyph == query);
if (entry != null)
{
return(GeneralSearchModel.FromSingleEntry(new SearchResultEntry
{
Glyph = entry.Glyph,
ShortDescription = entry.ShortDescription,
Submitter = entry.Submitter,
Updated = entry.Updated
}));
}
if (type == "glyph")
{
// Search for semi-matching glyphs
var glyphs = db.DungeonGlyphs.Where(d => d.Glyph.Contains(query)).ToList();
if (glyphs.Count == 0)
{
return(new GeneralSearchModel());
}
foreach (var g in glyphs)
{
if (glyphResults.Any(x => x.Glyph == g.Glyph))
{
continue;
}
glyphResults.Add(new SearchResultEntry
{
Glyph = g.Glyph,
ShortDescription = g.ShortDescription,
RootChalice = db.RootChalices.FirstOrDefault(r => r.ChaliceId == g.RootChalice).ChaliceName,
Submitter = g.Submitter,
Upvotes = g.Upvotes,
Downvotes = g.Downvotes,
Updated = g.Updated
});
}
}
else if (type == "loot")
{
// Get loot ids
var lootEntries = db.Loot.Where(l => l.ItemName.Contains(query)).ToList();
if (lootEntries.Any() == false)
{
return(new GeneralSearchModel());
}
// Check glyphs for matching ids
foreach (var loot in lootEntries)
{
var glyphs = db
.Query <DungeonGlyph>(
$"SELECT Glyph, ShortDescription, RootChalice, Submitter, Updated FROM DungeonGlyphs WHERE(',' + RTRIM(Loot) + ';') LIKE '%;{loot.Id};%'")
.ToList();
if (glyphs.Count == 0)
{
continue;
}
foreach (var g in glyphs)
{
if (lootResults.Any(l => l.Id == loot.Id) == false)
{
lootResults.Add(db.Loot.FirstOrDefault(l => l.Id == loot.Id));
}
if (glyphResults.Any(x => x.Glyph == g.Glyph))
{
continue;
}
glyphResults.Add(new SearchResultEntry
{
Glyph = g.Glyph,
ShortDescription = g.ShortDescription,
RootChalice = db.RootChalices.FirstOrDefault(r => r.ChaliceId == g.RootChalice).ChaliceName,
Submitter = g.Submitter,
Upvotes = g.Upvotes,
Downvotes = g.Downvotes,
Updated = g.Updated
});
}
}
}
return(new GeneralSearchModel(glyphResults, lootResults));
}
}