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