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