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