public static IEnumerable <Player> GetPlayerStatistics(IDbConnection c, string condition, object parameters)
{
var sql = $@"
SELECT
p.id, p.iduser, p.name, p.surname, p.idphotoimgurl, u.id, u.avatarImgUrl,
tp.apparelNumber, tp.idTacticPosition, tp.fieldPosition,
coalesce(sum(points), 0) as points,
coalesce(sum(gamesPlayed), 0) as gamesPlayed,
coalesce(sum(gamesWon), 0) as gamesWon,
coalesce(sum(gamesDraw), 0) as gamesDraw,
coalesce(sum(gamesLost), 0) as gamesLost,
coalesce(sum(pointsAgainst), 0) as pointsAgainst,
coalesce(sum(pointsInOwn), 0) as pointsInOwn,
coalesce(sum(cardsType1), 0) as cardsType1,
coalesce(sum(cardsType2), 0) as cardsType2,
coalesce(sum(cardsType3), 0) as cardsType3,
coalesce(sum(cardsType4), 0) as cardsType4,
coalesce(sum(cardsType5), 0) as cardsType5,
coalesce(sum(data1), 0) as data1,
coalesce(sum(data2), 0) as data2,
coalesce(sum(data3), 0) as data3,
coalesce(sum(data4), 0) as data4,
coalesce(sum(data5), 0) as data5
FROM
players p
JOIN users u ON p.idUser = u.id
JOIN teamplayers tp ON tp.idPlayer = p.id AND (tp.enrollmentStep >= 100 OR (tp.status & 256) = 256) AND tp.idTeam = @idTeam
LEFT JOIN playerdayresults pdr ON pdr.idPlayer = p.id AND pdr.idTeam = @idTeam AND pdr.idTournament = @idTournament
WHERE
{condition}
GROUP BY
p.id, p.name, p.surname, u.id, u.avatarImgUrl, tp.apparelNumber, tp.idTacticPosition, tp.fieldPosition
ORDER BY
tp.apparelNumber
";
var result = c.Query <Player, User, TeamPlayer, PlayerDayResult, Player>(sql,
(player, user, teamPlayer, pdr) =>
{
player.UserData = user;
player.DayResultSummary = pdr;
player.TeamData = teamPlayer;
return(player);
},
parameters,
splitOn: "id,apparelNumber,points");
// Add golbal users props
for (int i = 0; i < result.Count(); i++)
{
var userGlobal = UsersController.GetUserForId(c, result.ElementAt(i).UserData.Id);
result.ElementAt(i).UserData.Email = userGlobal.Email;
result.ElementAt(i).UserData.EmailConfirmed = userGlobal.EmailConfirmed;
}
return(result);
}
private PlayerNotificationData GetPlayerNotification(IDbConnection c, long idCreator, long idPlayer, long idTeam, bool wantsPin = false, long userId = -1)
{
var fromUser = UsersController.GetUserForId(c, idCreator);
var toUser = new User {
};
if (userId == -1) // User should exitst in current org
{
toUser = c.QueryFirst <User>($"SELECT u.id, u.name, u.mobile FROM users u JOIN players p ON p.idUser = u.id AND p.id = {idPlayer};");
var userToGlobal = UsersController.GetUserForId(c, toUser.Id);
toUser.Email = userToGlobal.Email;
toUser.EmailConfirmed = userToGlobal.EmailConfirmed;
}
else // Global User info
{
toUser = UsersController.GetUserForId(c, userId);
}
var mr = c.QueryMultiple(@"
SELECT id, name, logoImgUrl FROM organizations LIMIT 1;
SELECT id, name, logoImgUrl FROM teams WHERE id = @idTeam;
", new { idFrom = idCreator, idPlayer = idPlayer, idTeam = idTeam });
// var fromUser = mr.ReadFirst<User>();
// var toUser = mr.ReadFirst<User>();
var org = mr.ReadFirst <PublicOrganization>();
var team = mr.ReadFirst <Team>();
if (fromUser == null && idCreator >= 10000000)
{
fromUser = UsersController.GetGlobalAdminForId(idCreator);
}
if (team == null)
{
throw new Exception("Error.NotFound.Team");
}
if (toUser == null)
{
throw new Exception("Error.NotFound.ToUser");
}
if (fromUser == null)
{
throw new Exception("Error.NotFound.FromUser");
}
var activationLink = toUser.EmailConfirmed && !wantsPin ? "" : PlayersController.GetActivationLink(Request, mAuthTokenManager, toUser);
var activationPin = toUser.EmailConfirmed && !wantsPin ? "" : UsersController.GetActivationPin(mAuthTokenManager, toUser);
return(new PlayerNotificationData
{
To = toUser,
From = fromUser,
Team = team,
Org = org,
ActivationLink = activationLink,
ActivationPin = activationPin,
Images = new PlayerInviteImages
{
OrgLogo = Utils.GetUploadUrl(Request, org.LogoImgUrl, org.Id, "org"),
TeamLogo = Utils.GetUploadUrl(Request, team.LogoImgUrl, team.Id, "team")
}
});
}
