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