public List <PlayerGameSummaryData> GetPlayerGameSummaryData(int playerId, int gameId, string heroUnitTypeId) { using (frsDatabase db = frsDatabase.Create()) { List <PlayerGameSummaryData> gameSummaryData = ( from game in db.game join gameDetail in db.gameplayerdetail on game.GameID equals gameDetail.FK_GameID join heroType in db.herotype on gameDetail.FK_HeroTypeID equals heroType.HeroTypeID where gameDetail.FK_PlayerID == playerId && game.GameID < gameId && heroType.HeroUnitTypeID.Contains(heroUnitTypeId) orderby game.GameID descending select new PlayerGameSummaryData { GameID = game.GameID, GoldSpent = gameDetail.GoldSpent, PlayedDate = game.PlayedDate, GameResult = gameDetail.Result, HeroKills = gameDetail.Kills, HeroDeaths = gameDetail.Deaths, HeroAssists = gameDetail.Assists, HeroUnitTypeID = heroType.HeroUnitTypeID, HeroLevel = gameDetail.HeroLevel, Team = gameDetail.Team, TeamOneWinCount = game.TeamOneWinCount, TeamTwoWinCount = game.TeamTwoWinCount, DamageDealt = gameDetail.DamageDealt, DamageTaken = gameDetail.DamageTaken }).Take(8).ToList(); foreach (PlayerGameSummaryData data in gameSummaryData) { var teamQuery = ( from gameDetail in db.gameplayerdetail join player in db.player on gameDetail.FK_PlayerID equals player.PlayerID join heroType in db.herotype on gameDetail.FK_HeroTypeID equals heroType.HeroTypeID where gameDetail.FK_GameID == data.GameID select new { player.PlayerName, heroType.HeroUnitTypeID, gameDetail.Team } ); foreach (var teamData in teamQuery) { PlayerGameTeamPlayerData teamPlayer = new PlayerGameTeamPlayerData { PlayerName = teamData.PlayerName, HeroUnitTypeID = teamData.HeroUnitTypeID, Team = teamData.Team }; data.TeamList.Add(teamPlayer); } } return(gameSummaryData); } }
public List <SearchableServantData> GetSearchableServants() { using (frsDatabase db = frsDatabase.Create()) { return(( from heroType in db.herotype join herotypename in db.herotypename on heroType.HeroTypeID equals herotypename.FK_HeroTypeID select new SearchableServantData() { HeroUnitTypeID = heroType.HeroUnitTypeID, HeroNameTitle = herotypename.HeroName + " - " + herotypename.HeroTitle } ).ToList()); } }
public PlayerStatSummaryData GetPlayerSummary(string playerName, string serverName) { using (frsDatabase db = frsDatabase.Create()) { PlayerStatSummaryData summaryData = ( from playerStat in db.playerstat join player in db.player on playerStat.FK_PlayerID equals player.PlayerID join server in db.server on player.FK_ServerID equals server.ServerID join playerHeroStat in db.playerherostat on player.PlayerID equals playerHeroStat.FK_PlayerID where playerName == player.PlayerName && serverName == server.ServerName group playerHeroStat by new { player.PlayerID, player.PlayerName, playerStat.Win, playerStat.Loss } into g select new PlayerStatSummaryData() { PlayerId = g.Key.PlayerID, PlayerName = g.Key.PlayerName, Win = g.Key.Win, Loss = g.Key.Loss, TotalPlayerKills = g.Sum(x => x.TotalHeroKills), TotalPlayerDeaths = g.Sum(x => x.TotalHeroDeaths), TotalPlayerAssists = g.Sum(x => x.TotalHeroAssists), TotalPlayerGameCount = g.Sum(x => x.HeroPlayCount) }).FirstOrDefault(); if (summaryData == null) { return(null); } DateTime lastGamePlayed = ( from game in db.game join gameDetail in db.gameplayerdetail on game.GameID equals gameDetail.FK_GameID where gameDetail.FK_PlayerID == summaryData.PlayerId orderby game.PlayedDate descending select game.PlayedDate ).First(); summaryData.LastGamePlayed = lastGamePlayed; return(summaryData); } }
public Dictionary <string, int> GetTotalGamesPlayedVersion() { var gamePlayCountDict = new Dictionary <string, int>(); using (frsDatabase db = frsDatabase.Create()) { var gamesPlayedByVersion = ( from game in db.game group game by new { game.MapVersion } into g select new { g.Key.MapVersion, PlayedTotal = g.Count() }); foreach (var version in gamesPlayedByVersion) { gamePlayCountDict[version.MapVersion] = version.PlayedTotal; } } return(gamePlayCountDict); }
public List <GamePlayerDetailData> GetGameDetails(int gameId) { using (frsDatabase db = frsDatabase.Create()) { const string sql = @"SELECT A.GameID, A.TeamOneWinCount, A.TeamTwoWinCount, D.PlayerName, B.Kills, B.Deaths, B.Assists, B.HeroLevel AS Level, B.Team, B.DamageDealt, B.DamageTaken, B.GoldSpent, C.HeroUnitTypeID, group_concat(F.GodsHelpAbilID separator ',') AS GodsHelpAbilIDConcat FROM Game A JOIN GamePlayerDetail B ON A.GameID = B.FK_GameID JOIN HeroType C ON B.FK_HeroTypeID = C.HeroTypeID JOIN Player D ON B.FK_PlayerID = D.PlayerID LEFT OUTER JOIN GodsHelpUse E ON B.GamePlayerDetailID = E.FK_GamePlayerDetailID LEFT OUTER JOIN GodsHelpInfo F ON E.FK_GodsHelpInfoID = F.GodsHelpInfoID WHERE A.GameID = @GameID GROUP BY A.GameID, A.TeamOneWinCount, A.TeamTwoWinCount, D.PlayerName, B.Kills, B.Deaths, B.Assists, B.HeroLevel, B.Team, B.DamageDealt, B.DamageTaken, B.GoldSpent, C.HeroUnitTypeID;"; List <GamePlayerDetailData> gamePlayerDetailData = db.Database.SqlQuery <GamePlayerDetailData>(sql, new MySqlParameter("GameID", gameId)).ToList(); return(gamePlayerDetailData); } }
public List <PlayerHeroStatSummaryData> GetPlayerHeroSummary(int playerId) { using (frsDatabase db = frsDatabase.Create()) { var heroSummaryQuery = ( from gameDetail in db.gameplayerdetail join heroType in db.herotype on gameDetail.FK_HeroTypeID equals heroType.HeroTypeID where gameDetail.FK_PlayerID == playerId group gameDetail by new { gameDetail.Result, heroType.HeroUnitTypeID } into g select new { g.Key.HeroUnitTypeID, g.Key.Result, ResultCount = g.Count() }); List <PlayerHeroStatSummaryData> heroStatSummaryList = new List <PlayerHeroStatSummaryData>(); foreach (var summaryData in heroSummaryQuery) { PlayerHeroStatSummaryData heroStatSummary = heroStatSummaryList.FirstOrDefault(x => x.HeroUnitTypeID == summaryData.HeroUnitTypeID); if (heroStatSummary == null) { heroStatSummary = new PlayerHeroStatSummaryData { HeroUnitTypeID = summaryData.HeroUnitTypeID }; heroStatSummaryList.Add(heroStatSummary); } if (summaryData.Result == "WIN") { heroStatSummary.Wins = summaryData.ResultCount; } else if (summaryData.Result == "LOSS") { heroStatSummary.Losses = summaryData.ResultCount; } } var heroSummaryKDAQuery = ( from playerHeroStat in db.playerherostat join heroType in db.herotype on playerHeroStat.FK_HeroTypeID equals heroType.HeroTypeID join heroName in db.herotypename on heroType.HeroTypeID equals heroName.FK_HeroTypeID where playerHeroStat.FK_PlayerID == playerId select new { heroName.HeroName, heroType.HeroUnitTypeID, playerHeroStat.HeroPlayCount, playerHeroStat.TotalHeroKills, playerHeroStat.TotalHeroDeaths, playerHeroStat.TotalHeroAssists }); foreach (var kdaData in heroSummaryKDAQuery) { PlayerHeroStatSummaryData heroStatSummary = heroStatSummaryList.First(x => x.HeroUnitTypeID == kdaData.HeroUnitTypeID); heroStatSummary.HeroTotalPlayCount = kdaData.HeroPlayCount; heroStatSummary.HeroTotalKills = kdaData.TotalHeroKills; heroStatSummary.HeroTotalDeaths = kdaData.TotalHeroDeaths; heroStatSummary.HeroTotalAssists = kdaData.TotalHeroAssists; heroStatSummary.HeroName = kdaData.HeroName; } heroStatSummaryList = heroStatSummaryList.OrderByDescending(x => x.HeroTotalPlayCount).ToList(); return(heroStatSummaryList); } }
public PlayerGameBuildData GetPlayerGameBuildDetail(string playerName, int gameId) { using (frsDatabase db = frsDatabase.Create()) { //Find gamePlayerDetailId first int gamePlayerDetailId = ( from game in db.game join gameDetail in db.gameplayerdetail on game.GameID equals gameDetail.FK_GameID join player in db.player on gameDetail.FK_PlayerID equals player.PlayerID where game.GameID == gameId && player.PlayerName == playerName select gameDetail.GamePlayerDetailID).FirstOrDefault(); if (gamePlayerDetailId == 0) { return(null); } //Find HeroUnitTypeId string heroTypeUnitId = ( from herotype in db.herotype join gameDetail in db.gameplayerdetail on herotype.HeroTypeID equals gameDetail.FK_HeroTypeID where gameDetail.GamePlayerDetailID == gamePlayerDetailId select herotype.HeroUnitTypeID).First(); PlayerGameBuildData buildData = new PlayerGameBuildData { HeroUnitTypeId = heroTypeUnitId }; //Find stat learn information var heroStatLearnQuery = ( from herostatinfo in db.herostatinfo join herostatlearn in db.herostatlearn on herostatinfo.HeroStatInfoID equals herostatlearn.FK_HeroStatInfoID where herostatlearn.FK_GamePlayerDetailID == gamePlayerDetailId select new { herostatinfo.HeroStatAbilID, herostatlearn.LearnCount }); buildData.StatBuildDic = new Dictionary <string, int>(); foreach (var heroStat in heroStatLearnQuery) { buildData.StatBuildDic.Add(heroStat.HeroStatAbilID, heroStat.LearnCount); } //Find attribute information var heroAttributeQuery = ( from attributeinfo in db.attributeinfo join attributelearn in db.attributelearn on attributeinfo.AttributeInfoID equals attributelearn.FK_AttributeInfoID where attributelearn.FK_GamePlayerDetailID == gamePlayerDetailId select new { attributeinfo.AttributeAbilID, attributeinfo.AttributeName }); buildData.LearnedAttributeList = new List <Tuple <string, string> >(); foreach (var attribute in heroAttributeQuery) { Tuple <string, string> attrTuple = new Tuple <string, string>(attribute.AttributeAbilID, attribute.AttributeName); buildData.LearnedAttributeList.Add(attrTuple); } //Find Ward/Familiar Information var itemKeys = new[] { "I003", "I00N", "SWAR", "I002", "I005", "I00R", "I00G" }; var itemsQuery = ( from iteminfo in db.iteminfo join itembuy in db.gameitempurchase on iteminfo.ItemID equals itembuy.FK_ItemID where itembuy.FK_GamePlayerDetailID == gamePlayerDetailId && itemKeys.Contains(iteminfo.ItemTypeID) select new { iteminfo.ItemTypeID, itembuy.ItemPurchaseCount }); buildData.PurchasedItemsDic = new Dictionary <string, int>(); foreach (var item in itemsQuery) { buildData.PurchasedItemsDic.Add(item.ItemTypeID, item.ItemPurchaseCount); } //Find CommandSeal Info var commandSealKeys = new[] { "A05Q", "A094", "A043", "A044" }; var commandSealQuery = ( from commandseal in db.commandsealuse where commandseal.FK_GamePlayerDetailID == gamePlayerDetailId && commandSealKeys.Contains(commandseal.CommandSealAbilID) select new { commandseal.CommandSealAbilID, commandseal.UseCount }); buildData.CommandSealDic = new Dictionary <string, int>(); foreach (var cs in commandSealQuery) { buildData.CommandSealDic.Add(cs.CommandSealAbilID, cs.UseCount); } return(buildData); } }