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());
        }
Ejemplo n.º 2
0
        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());
        }