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