public async Task <CharacterAnalysis> GetCharacters(string leagueName, GetCharactersConfig config) { var(sqlb, parameter) = GetCharactersSql(leagueName, config); CharacterAnalysis result = (await PoeContext.Database.GetDbConnection().QueryAsync <CharacterAnalysis>( sqlb.ToString(), parameter )).FirstOrDefault(); SqlMapper.PurgeQueryCache(); return(result); }
public async Task DefaultUpsert(PoeCharacterModel poeChar) { async Task upsert(PoeDbContext PoeContext) { if (!String.IsNullOrWhiteSpace(poeChar.PobCode)) { await PoeContext.Upsert(poeChar). On(c => c.CharacterId). WhenMatched((oldChar, newChar) => new PoeCharacterModel() { Level = newChar.Level, Class = newChar.Class, LifeUnreserved = newChar.LifeUnreserved, EnergyShield = newChar.EnergyShield, Dead = newChar.Dead, Depth = newChar.Depth, Account = newChar.Account, Online = newChar.Online, Rank = newChar.Rank, Experience = newChar.Experience, PobCode = newChar.PobCode, Pob = newChar.Pob, Items = newChar.Items, UpdatedAt = newChar.PobCode != oldChar.PobCode ? System.DateTime.UtcNow : oldChar.UpdatedAt }).RunAsync(); } } using (var tran = PoeContext.Database.BeginTransaction()) { await upsert(PoeContext); var config = new GetCharactersConfig { characterId = poeChar.CharacterId }; var(sqlb, parameter) = LeagueService.GetCharactersSql(poeChar.LeagueName, config); var sql = sqlb.ToString(); CharacterAnalysis result = (await PoeContext.Database.GetDbConnection().QueryAsync <CharacterAnalysis>( sqlb.ToString(), parameter, tran.GetDbTransaction() )).FirstOrDefault(); if (result == null || result.Total != 1) { tran.Rollback(); } else { var config2 = new GetCharactersConfig { characterId = poeChar.CharacterId, mainSkills = result.MainSkillCountEntries != null?result.MainSkillCountEntries.Select(x => x.SkillId).ToList() : new List <string> { } }; var(sqlb2, parameter2) = LeagueService.GetCharactersSql(poeChar.LeagueName, config2); var sql2 = sqlb2.ToString(); CharacterAnalysis result2 = (await PoeContext.Database.GetDbConnection().QueryAsync <CharacterAnalysis>( sqlb2.ToString(), parameter2, tran.GetDbTransaction() )).FirstOrDefault(); if (result2 == null || result2.Total != 1) { tran.Rollback(); } else { CountAnalysis countAnalysis = new CountAnalysis() { ClassCountEntries = result2.ClassCountEntries ?? new ClassEntry[] { }, UniqueCountEntries = result2.UniqueCountEntries ?? new UniqueEntry[] { }, AllKeystoneCountEntries = result2.AllKeystoneCountEntries ?? new KeystoneEntry[] { }, MainSkillCountEntries = result2.MainSkillCountEntries ?? new SkillEntry[] { }, MainSkillSupportCountEntries = result2.MainSkillSupportCountEntries ?? new MainSkillSupportCountEntry[] { }, AllSkillCountEntries = result2.AllSkillCountEntries ?? new SkillEntry[] { }, WeaponTypeCountEntries = result2.WeaponTypeCountEntries ?? new WeaponTypeEntry[] { } }; var updateCountSql = $@"UPDATE ""Characters"" SET ""CountAnalysis"" = @CountAnalysis WHERE ""CharacterId"" = @CharacterId"; await PoeContext.Database.GetDbConnection().ExecuteAsync(updateCountSql, new { CharacterId = poeChar.CharacterId, CountAnalysis = new JsonbParameter(JsonSerializer.Serialize(countAnalysis)) }, tran.GetDbTransaction()); tran.Commit(); } } } }
public async Task <CharacterAnalysis> GetCharactersByAnalysis(string leagueName, GetCharactersConfig config) { var classes = config.classes; var items = config.items; var mainSkills = config.mainSkills; var mainSkillSupports = config.mainSkillSupports; var allSkills = config.allSkills; var keystones = config.keystones; var weaponTypes = config.weaponTypes; var offhandTypes = config.offhandTypes; var limit = config.limit; var offset = config.offset; var sort = config.sort; var characterId = config.characterId; var characterNameLike = config.characterNameLike; var filtedCsBuilder = new SqlBuilder(); var filtedCsTemplate = filtedCsBuilder.AddTemplate(@" SELECT ""CharacterId"", ""LeagueName"", ""CharacterName"", ""Class"", ""AccountName"", ""Account"", ""Level"", ""LifeUnreserved"", ""Depth"", ""EnergyShield"", ""Rank"", ""cs"".""Pob"" -> 'Tree' -> 'Spec' ->> 'nodes' AS ""TreeNodes"", ""UpdatedAt"", ""CountAnalysis"" FROM ""Characters"" AS ""cs"" /**where**/ /**orderby**/ " ); DynamicParameters parameter = new DynamicParameters(); parameter.Add("@Offset", offset); parameter.Add("@Limit", limit); parameter.Add("@LeagueName", leagueName); filtedCsBuilder.Where(@" ""cs"".""LeagueName"" = @LeagueName " ); if (!String.IsNullOrWhiteSpace(characterId)) { parameter.Add("@CharacterId", characterId); filtedCsBuilder.Where(@" ""cs"".""CharacterId"" = @CharacterId " ); } if (classes != null && classes.Count > 0) { var(includes, excludes) = GetCharactersConfig.GetIncludeExcludes(classes); void addFilter(List <string> xs, bool isInclude) { for (int i = 0; i < xs.Count; i++) { string x = xs[i]; var op = isInclude ? "" : "NOT"; filtedCsBuilder.OrWhere($@" {op} ""cs"".""CountAnalysis"" @? '$.ClassCountEntries[*] ? (@.Class == {MyJsonUtils.Escape(x)})' " ); } } if (excludes.Count > 0) { addFilter(excludes, false); } if (includes.Count > 0) { addFilter(includes, true); } } if (!String.IsNullOrWhiteSpace(characterNameLike)) { parameter.Add("@CharacterNameLike", characterNameLike); filtedCsBuilder.Where(@" ""cs"".""CharacterName"" LIKE ('%' || @CharacterNameLike || '%') " ); } if (items != null && items.Count > 0) { var(includes, excludes) = GetCharactersConfig.GetIncludeExcludes(items); void addFilter(List <string> xs, bool isInclude) { for (int i = 0; i < xs.Count; i++) { string x = xs[i]; var op = isInclude ? "" : "NOT"; filtedCsBuilder.Where($@" {op} ""cs"".""CountAnalysis"" @? '$.UniqueCountEntries[*] ? (@.ItemName == {MyJsonUtils.Escape(x)})' " ); } } if (excludes.Count > 0) { addFilter(excludes, false); } if (includes.Count > 0) { addFilter(includes, true); } } if (mainSkills != null && mainSkills.Count > 0) { var(includes, excludes) = GetCharactersConfig.GetIncludeExcludes(mainSkills); void addFilter(List <string> xs, bool isInclude) { for (int i = 0; i < xs.Count; i++) { string x = xs[i]; var op = isInclude ? "" : "NOT"; filtedCsBuilder.Where($@" {op} ""cs"".""CountAnalysis"" @? '$.MainSkillCountEntries[*] ? (@.SkillId == {MyJsonUtils.Escape(x)})' " ); } } if (excludes.Count > 0) { addFilter(excludes, false); } if (includes.Count > 0) { addFilter(includes, true); } } if (mainSkillSupports != null && mainSkillSupports.Count > 0) { void addFilter(string mainSkillId, List <string> xs, bool isInclude) { var op = isInclude ? "" : "NOT"; for (int i = 0; i < xs.Count; i++) { string x = xs[i]; filtedCsBuilder.Where($@" {op} ( ""cs"".""CountAnalysis"" @? '$.MainSkillSupportCountEntries[*] ? (@.MainSkillId == {MyJsonUtils.Escape(mainSkillId)})' AND ""cs"".""CountAnalysis"" @? '$.MainSkillSupportCountEntries[*].SupportCountEntries[*] ? (@.SkillId == {MyJsonUtils.Escape(x)})' ) " ); } } foreach (var mainSkillSupportLst in mainSkillSupports) { if (mainSkillSupportLst != null && mainSkillSupportLst.Count >= 2) { string mainSkill = mainSkillSupportLst.ElementAtOrDefault(0); List <string> supports = mainSkillSupportLst.Skip(1).ToList(); var(includes, excludes) = GetCharactersConfig.GetIncludeExcludes(supports); if (excludes.Count > 0) { addFilter(mainSkill, excludes, false); } if (includes.Count > 0) { addFilter(mainSkill, includes, true); } } } } if (allSkills != null && allSkills.Count > 0) { var(includes, excludes) = GetCharactersConfig.GetIncludeExcludes(allSkills); void addFilter(List <string> xs, bool isInclude) { for (int i = 0; i < xs.Count; i++) { string x = xs[i]; var op = isInclude ? "" : "NOT"; filtedCsBuilder.Where($@" {op} ""cs"".""CountAnalysis"" @? '$.AllSkillCountEntries[*] ? (@.SkillId == {MyJsonUtils.Escape(x)})' " ); } } if (excludes.Count > 0) { addFilter(excludes, false); } if (includes.Count > 0) { addFilter(includes, true); } } if (keystones != null && keystones.Count > 0) { var(includes, excludes) = GetCharactersConfig.GetIncludeExcludes(keystones); void addFilter(List <string> xs, bool isInclude) { for (int i = 0; i < xs.Count; i++) { string x = xs[i]; var op = isInclude ? "" : "NOT"; filtedCsBuilder.Where($@" {op} ""cs"".""CountAnalysis"" @? '$.AllKeystoneCountEntries[*] ? (@.SkillId == {MyJsonUtils.Escape(x)})' " ); } } if (excludes.Count > 0) { addFilter(excludes, false); } if (includes.Count > 0) { addFilter(includes, true); } } if (config.disableWeaponPairMatch) { if (weaponTypes != null && weaponTypes.Count > 0) { var(includes, excludes) = GetCharactersConfig.GetIncludeExcludes(weaponTypes); void addFilter(List <string> xs, bool isInclude) { for (int i = 0; i < xs.Count; i++) { string x = xs[i]; var op = isInclude ? "" : "NOT"; filtedCsBuilder.Where($@" {op} ""cs"".""CountAnalysis"" @? '$.WeaponTypeCountEntries[*] ? (@.WeaponType == {MyJsonUtils.Escape(x)})' " ); } } if (excludes.Count > 0) { addFilter(excludes, false); } if (includes.Count > 0) { addFilter(includes, true); } } if (offhandTypes != null && offhandTypes.Count > 0) { var(includes, excludes) = GetCharactersConfig.GetIncludeExcludes(offhandTypes); void addFilter(List <string> xs, bool isInclude) { for (int i = 0; i < xs.Count; i++) { string x = xs[i]; var op = isInclude ? "" : "NOT"; filtedCsBuilder.Where($@" {op} ""cs"".""CountAnalysis"" @? '$.WeaponTypeCountEntries[*] ? (@.OffhandType == {MyJsonUtils.Escape(x)})' " ); } } if (excludes.Count > 0) { addFilter(excludes, false); } if (includes.Count > 0) { addFilter(includes, true); } } } else { if (weaponTypes != null && offhandTypes != null && weaponTypes.Count == offhandTypes.Count) { for (int i = 0; i < weaponTypes.Count; i++) { var(weaponType, wIsInclude) = GetCharactersConfig.GetIncludeExclude(weaponTypes[i]); var(offhandType, oIsInclude) = GetCharactersConfig.GetIncludeExclude(offhandTypes[i]); var wop = wIsInclude ? "" : "NOT"; var oop = oIsInclude ? "" : "NOT"; filtedCsBuilder.Where($@" ( {wop} ""cs"".""CountAnalysis"" @? '$.WeaponTypeCountEntries[*] ? (@.WeaponType == {MyJsonUtils.Escape(weaponType)})' AND {oop} ""cs"".""CountAnalysis"" @? '$.WeaponTypeCountEntries[*] ? (@.OffhandType == {MyJsonUtils.Escape(offhandType)})' ) " ); } } } if (sort != null && sort.Count > 0) { string _sortField = sort.ElementAtOrDefault(0) != null?sort.ElementAtOrDefault(0).ToLower() : null; string _sortArrange = sort.ElementAtOrDefault(1) != null?sort.ElementAtOrDefault(1).ToUpper() : null; string sortField; if (_sortField != "level" && _sortField != "es" && _sortField != "life") { sortField = "Level"; } else if (_sortField == "level") { sortField = "Level"; } else if (_sortField == "life") { sortField = "LifeUnreserved"; } else if (_sortField == "es") { sortField = "EnergyShield"; } else { sortField = "Level"; } string sortArrange; if (_sortArrange != "DESC" && _sortArrange != "ASC") { sortArrange = "DESC"; } else { sortArrange = _sortArrange; } if (sortField == "Level" && sortArrange == "DESC") { filtedCsBuilder.OrderBy($@" ""cs"".""Level"" DESC, ""cs"".""Rank"" ASC " ); } else { filtedCsBuilder.OrderBy($@" ""cs"".""{sortField}"" {sortArrange} " ); } } else { filtedCsBuilder.OrderBy($@" ""cs"".""Level"" DESC, ""cs"".""Rank"" ASC " ); } // var supportGemBuilder = new SqlBuilder(); // var supportGemTemplate = supportGemBuilder.AddTemplate(@" // SELECT // ""cs"".* // FROM // ""FiltedCs"" AS ""cs"" // /**where**/ // /**orderby**/ // " // ); string mainSkillSupportSql = ""; if (mainSkills != null && mainSkills.Count > 0) { var(includes, excludes) = GetCharactersConfig.GetIncludeExcludes(mainSkills); if (includes.Count > 0) { var supportSqls = includes.Select((mainSkill, index) => { var mainSkillVar = $"@MainSkillSupport{index}"; parameter.Add(mainSkillVar, mainSkill); var supportSql = $@" ""_{index}_supportCounts"" AS ( SELECT ""supportCountEntries"" ->> 'SkillId' AS ""skillId"", jsonb_agg(""supportCountEntries"") -> 0 ->> 'GemId' AS ""gemId"", jsonb_agg(""supportCountEntries"") -> 0 ->> 'NameSpec' AS ""nameSpec"", SUM((""supportCountEntries"" -> 'Count')::INTEGER) AS ""count"" FROM ""FiltedCs"" AS ""cs"", jsonb_array_elements(""cs"".""CountAnalysis"" -> 'MainSkillSupportCountEntries') AS ""xs"", jsonb_array_elements(""xs"" -> 'SupportCountEntries') AS ""supportCountEntries"" WHERE ""xs"" ->> 'MainSkillId' = {mainSkillVar} GROUP BY ""supportCountEntries"" ->> 'SkillId' ORDER BY ""count"" DESC ), ""_{index}_supportCountEntry"" AS ( SELECT {mainSkillVar} AS ""mainSkillId"", jsonb_agg(row_to_json(""_supportCounts"")) AS ""supportCountEntries"" FROM ""_{index}_supportCounts"" AS ""_supportCounts"" ) "; return(supportSql); }).ToList(); string supportTables = String.Join(" , ", supportSqls.ToArray()); string unionAllTable = String.Join(" UNION ALL ", mainSkills.Select((mainSkill, index) => { return($@" SELECT * FROM ""_{index}_supportCountEntry"" "); }).ToArray()); mainSkillSupportSql = $@" ""MainSkillSupportCountEntries"" AS ( WITH {supportTables} SELECT jsonb_agg(row_to_json(""foo"")) AS ""mainSkillSupportCountEntries"" FROM ( {unionAllTable} ) AS ""foo"" ), "; } } var sqlb = new StringBuilder(10000); sqlb.Append($@" WITH ""FiltedCs"" AS ( {filtedCsTemplate.RawSql} ),"); sqlb.Append(mainSkillSupportSql); sqlb.Append(@" ""MainSkillCounts"" AS ( SELECT ""xs"" ->> 'SkillId' AS ""skillId"", jsonb_agg(""xs"") -> 0 ->> 'GemId' AS ""gemId"", jsonb_agg(""xs"") -> 0 ->> 'NameSpec' AS ""nameSpec"", SUM((""xs"" -> 'Count')::INTEGER) AS ""count"" FROM ""FiltedCs"" AS ""cs"", jsonb_array_elements(""cs"".""CountAnalysis"" -> 'MainSkillCountEntries') AS ""xs"" GROUP BY ""xs"" ->> 'SkillId' ORDER BY ""count"" DESC ), ""MainSkillCountEntries"" AS ( SELECT jsonb_agg(row_to_json(""MainSkillCounts"")) AS ""mainSkillCountEntries"" FROM ""MainSkillCounts"" ), ""AllSkillCounts"" AS ( SELECT ""xs"" ->> 'SkillId' AS ""skillId"", jsonb_agg(""xs"") -> 0 ->> 'GemId' AS ""gemId"", jsonb_agg(""xs"") -> 0 ->> 'NameSpec' AS ""nameSpec"", SUM((""xs"" -> 'Count')::INTEGER) AS ""count"" FROM ""FiltedCs"" AS ""cs"", jsonb_array_elements(""cs"".""CountAnalysis"" -> 'AllSkillCountEntries') AS ""xs"" GROUP BY ""xs"" ->> 'SkillId' ORDER BY ""count"" DESC ), ""AllSkillCountEntries"" AS ( SELECT jsonb_agg(row_to_json(""AllSkillCounts"")) AS ""allSkillCountEntries"" FROM ""AllSkillCounts"" ), ""UniqueCounts"" AS ( SELECT ""xs"" ->> 'ItemName' AS ""itemName"", jsonb_agg(""xs"") -> 0 ->> 'ItemType' AS ""itemType"", SUM((""xs"" -> 'Count')::INTEGER) AS ""count"" FROM ""FiltedCs"" AS ""cs"", jsonb_array_elements(""cs"".""CountAnalysis"" -> 'UniqueCountEntries') AS ""xs"" GROUP BY ""xs"" ->> 'ItemName' ORDER BY ""count"" DESC ), ""UniqueCountEntries"" AS ( SELECT jsonb_agg(row_to_json(""UniqueCounts"")) AS ""uniqueCountEntries"" FROM ""UniqueCounts"" ), ""AllKeystoneCounts"" AS ( SELECT ""xs"" ->> 'SkillId' AS ""skillId"", SUM((""xs"" -> 'Count')::INTEGER) AS ""count"" FROM ""FiltedCs"" AS ""cs"", jsonb_array_elements(""cs"".""CountAnalysis"" -> 'AllKeystoneCountEntries') AS ""xs"" GROUP BY ""xs"" ->> 'SkillId' ORDER BY ""count"" DESC ), ""AllKeystoneCountEntries"" AS ( SELECT jsonb_agg(row_to_json(""AllKeystoneCounts"")) AS ""allKeystoneCountEntries"" FROM ""AllKeystoneCounts"" ), ""WeaponTypeCounts"" AS ( SELECT ""xs"" ->> 'WeaponType' AS ""weaponType"", ""xs"" ->> 'OffhandType' AS ""offhandType"", SUM((""xs"" -> 'Count')::INTEGER) AS ""count"" FROM ""FiltedCs"" AS ""cs"", jsonb_array_elements(""cs"".""CountAnalysis"" -> 'WeaponTypeCountEntries') AS ""xs"" GROUP BY ""xs"" ->> 'WeaponType', ""xs"" ->> 'OffhandType' ORDER BY ""count"" DESC ), ""WeaponTypeCountEntries"" AS ( SELECT jsonb_agg(row_to_json(""WeaponTypeCounts"")) AS ""weaponTypeCountEntries"" FROM ""WeaponTypeCounts"" ), ""ClassCounts"" AS ( SELECT ""xs"" ->> 'Class' AS ""class"", SUM((""xs"" -> 'Count')::INTEGER) AS ""count"" FROM ""FiltedCs"" AS ""cs"", jsonb_array_elements(""cs"".""CountAnalysis"" -> 'ClassCountEntries') AS ""xs"" GROUP BY ""xs"" ->> 'Class' ORDER BY ""count"" DESC ), ""ClassCountEntries"" AS ( SELECT jsonb_agg(row_to_json(""ClassCounts"")) AS ""classCountEntries"" FROM ""ClassCounts"" ), ""PagedChars"" AS ( SELECT * FROM ""FiltedCs"" LIMIT @Limit OFFSET @Offset ), ""TotalCount"" AS ( SELECT COUNT(*) AS ""total"" FROM ""FiltedCs"" ), ""PagedEntries"" AS ( SELECT jsonb_agg( jsonb_build_object( 'CharacterId', ""cs"".""CharacterId"", 'CharacterName', ""cs"".""CharacterName"", 'LeagueName', ""cs"".""LeagueName"", 'Level', ""cs"".""Level"", 'Class', ""cs"".""Class"", 'LeagueName', ""cs"".""LeagueName"", 'AccountName', ""cs"".""AccountName"", 'LifeUnreserved', ""cs"".""LifeUnreserved"", 'EnergyShield', ""cs"".""EnergyShield"", 'Depth', ""cs"".""Depth"", 'TreeNodes', ""cs"".""TreeNodes"", 'UpdatedAt', ""cs"".""UpdatedAt"" )) AS ""entries"" FROM ""PagedChars"" AS ""cs"" ) " ); string mainSkillSupportTableName = !String.IsNullOrWhiteSpace(mainSkillSupportSql) ? @", ""MainSkillSupportCountEntries""" : ""; string collectAllSql = $@" SELECT * FROM ""TotalCount"", ""PagedEntries"", ""ClassCountEntries"", ""UniqueCountEntries"", ""MainSkillCountEntries"", ""AllSkillCountEntries"", ""AllKeystoneCountEntries"", ""WeaponTypeCountEntries"" {mainSkillSupportTableName} " ; sqlb.Append(collectAllSql); CharacterAnalysis result = (await PoeContext.Database.GetDbConnection().QueryAsync <CharacterAnalysis>( sqlb.ToString(), parameter )).FirstOrDefault(); SqlMapper.PurgeQueryCache(); return(result); }