public async Task <List <AchievementDto> > GetAccountAchievements(long accountId)
{
string achievementsSql =
@"SELECT
aa.AchievementId,
s.Section,
s.SectionName,
a.[Order],
a.[Name],
[Description] = a.[Description] + CASE WHEN a.Condition IS NULL THEN '' ELSE CHAR(13) + a.Condition END,
aa.[Count],
[Image] = CASE WHEN ao.[Image] IS NOT NULL THEN ao.[Image] ELSE a.[Image] END,
IsAchievementOption = CONVERT(BIT, CASE WHEN ao.[Image] IS NOT NULL THEN 1 ELSE 0 END)
FROM wotb.AccountInfoAchievement aa
INNER JOIN wotb.Achievement a ON aa.AchievementId = a.AchievementId
INNER JOIN wotb.AchievementSection s ON a.Section = s.Section
LEFT JOIN (SELECT wotb.AchievementOption.AchievementId,
wotb.AchievementOption.[Image],
CONVERT(INT, LEFT(RIGHT([Image],5), 1)) AS Grade
FROM wotb.AchievementOption) ao ON ao.AchievementId = aa.AchievementId AND aa.[Count] = ao.Grade
WHERE aa.AccountId = @accountId and aa.TankId IS NULL AND aa.IsMaxSeries = 0
ORDER BY s.[Order], a.[Order]
";
var accountIdParameter = new SqlParameter("@accountId", accountId);
return(await _dbContext.Query <AchievementDto>()
.FromSql(achievementsSql, accountIdParameter)
.ToListAsync());
}
public async Task <List <PlayerStatHistoryDto> > GetAccountStatHistory(long accountId, DateTime dateFrom)
{
string achievementsSql =
@"-- Account Stat history v1
WITH AccountHistory AS
(
SELECT ROW_NUMBER() OVER(ORDER BY ais.UpdatedAt DESC) AS RowNumber,
ais.UpdatedAt,
ais.Battles,
ais.Wins,
ais.AvgTier,
ais.Wn7,
CONVERT(decimal(19,2), ais.Wins)/CONVERT(decimal(19,2), ais.Battles)WinRate,
ais.DamageDealt/ais.Battles AvgDamage,
ais.Xp/ais.Battles AvgXp,
CONVERT(decimal(19,2), ais.SurvivedBattles)/CONVERT(decimal(19,2), ais.Battles) SurvivalRate
FROM wotb.AccountInfoStatistics AS ais
WHERE ais.AccountId = @accountId
AND ais.UpdatedAt >= @dateFrom
)
SELECT
cur.UpdatedAt,
cur.Battles,
cur.Battles - prev.Battles BattlesDiff,
cur.Wins,
CONVERT(decimal(19,2), cur.AvgTier) AvgTier,
CONVERT(decimal(19,2), cur.AvgTier - prev.AvgTier) AvgTierDiff,
CONVERT(decimal(19,2), cur.Wn7) Wn7,
CONVERT(decimal(19,2), cur.Wn7 - prev.Wn7) Wn7Diff,
cur.WinRate,
cur.WinRate - prev.WinRate WinRateDiff,
cur.AvgDamage,
cur.AvgDamage - prev.AvgDamage AvgDamageDiff,
cur.AvgXp,
cur.AvgXp - prev.AvgXp AvgXpDiff,
cur.SurvivalRate,
cur.SurvivalRate - prev.SurvivalRate SurvivalRateDiff
FROM AccountHistory AS cur
LEFT JOIN AccountHistory AS prev ON cur.RowNumber = prev.RowNumber - 1
";
var accountIdParameter = new SqlParameter("@accountId", accountId);
var dateFromParameter = new SqlParameter("@dateFrom", dateFrom);
return(await _dbContext.Query <PlayerStatHistoryDto>()
.FromSql(achievementsSql, accountIdParameter, dateFromParameter)
.ToListAsync());
}
