/// <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}'");
}
}
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));
}
}
