private PaginatedCollection<User> fetchGroupMembers(final Group group) {
final String queryString =
"select u from GroupImpl g inner join g.members u where g=:group order by u.name";
final Query query = query(queryString);
query.setParameter("group", group);
return paginateList(0, 100, query.getResultList());
}
private Double fetchGlobalDistance(final Group group) {
final Query query =
query(
"select sum(w.distance) from GroupImpl g left join g.members u left join u.workouts w where g=:group");
query.setParameter("group", group);
return (Double) query.getSingleResult();
}
private Collection<ConversationSummary> fetchCorrespondents(final User user) {
final Map<String, ConversationSummary> correspondants =
new HashMap<String, ConversationSummary>();
{
final Query query =
query(
"select m.sender.name, m.receiver.name, count(m), m.read from PrivateMessageImpl m where (m.receiver=:user OR "
+ "(m.sender=:user)) AND(m.deleter IS NULL OR m.deleter <> :user) "
+ "group by m.sender.name, m.receiver.name, m.read");
query.setParameter("user", user);
for (final Object[] row : (List<Object[]>) query.getResultList()) {
final boolean sent = row[0].equals(((UserImpl) user).getBareName());
final String name = (String) (sent ? row[1] : row[0]);
final ConversationSummary previous = correspondants.get(name);
final long count = ((Number) row[2]).longValue();
final long newCount;
if (row[3].equals(Boolean.FALSE) && !sent) newCount = count;
else newCount = 0;
if (previous != null) {
correspondants.put(
name,
new ConversationSummary(
UserStringImpl.valueOf(name),
count + previous.messageCount,
newCount + previous.newMessageCount));
} else
correspondants.put(
name, new ConversationSummary(UserStringImpl.valueOf(name), count, newCount));
}
}
return new TreeSet<ConversationSummary>(correspondants.values());
}
private Collection<GroupData> fetchGroupDataForUser(
final User user, final boolean restrictToSuscribed) {
final String ifConnectedColumns =
"max(ifnull(USER_ID=:userId, false))>0, sum(ifnull(USER_ID=:userId AND LAST_VISIT<PUBLIC_MESSAGES.`DATE`, false))";
final Query query =
entityManager.createNativeQuery(
"select GROUPS.ID, name, count(DISTINCT GROUP_USER.USER_ID), "
+ (user != null ? ifConnectedColumns : " 0,0")
+ " from GROUPS left join GROUP_USER on GROUP_ID=ID "
+ " left join PUBLIC_MESSAGES on PUBLIC_MESSAGES.GROUP_ID=GROUP_USER.GROUP_ID "
+ (restrictToSuscribed ? " where GROUP_USER.USER_ID=:userId" : "")
+ " group by GROUPS.ID order by CREATION_DATE");
if (user != null) query.setParameter("userId", user.getId());
final List<Object[]> list = query.getResultList();
final Collection<GroupData> result = new ArrayList<GroupData>(list.size());
for (final Object[] o : list)
result.add(
new GroupData(
((Number) o[0]).longValue(),
UserStringImpl.valueOf(String.valueOf(o[1])),
((Number) o[2]).longValue(),
((Number) o[3]).intValue() != 0,
((Number) o[4]).intValue()));
return result;
}
private void separatePrivateMessagesFromWorkout(final Long id) {
final Query query =
entityManager.createNativeQuery(
"UPDATE MESSAGES SET WORKOUT_ID=NULL WHERE WORKOUT_ID=:id AND RECEIVER_ID IS NOT NULL");
query.setParameter("id", id);
query.executeUpdate();
}
public List<NewMessageData> fetchNewMessagesCount(final User user) {
final Query query =
query(
"select new com.nraynaud.sport.data.NewMessageData(m.sender.name, count(m)) from PrivateMessageImpl m where m.receiver = :user and m.read = false group by m.sender.name");
query.setParameter("user", user);
return query.getResultList();
}
public void partGroup(final User user, final Long groupId) {
final Query query =
entityManager.createNativeQuery(
"DELETE FROM GROUP_USER WHERE GROUP_ID=:groupId AND USER_ID=:userId");
query.setParameter("groupId", groupId);
query.setParameter("userId", user.getId());
query.executeUpdate();
}
private boolean isGroupMember(final User user, final GroupImpl group) {
final Query query =
entityManager.createNativeQuery(
"select 1 from GROUP_USER where USER_ID=:userId AND GROUP_ID=:groupId");
query.setParameter("userId", user.getId());
query.setParameter("groupId", group.getId());
return query.getResultList().size() > 0;
}
public void deletePublicMessageFor(final Long messageId, final User user) {
final Query query =
entityManager.createNativeQuery(
"delete from PUBLIC_MESSAGES where ID=:id and sender_id=:user_id");
query.setParameter("user_id", user.getId());
query.setParameter("id", messageId);
query.executeUpdate();
}
@SuppressWarnings({"unchecked"})
public List<String> fechLoginBeginningBy(final String prefix) {
final Query query =
query(
"select u.name from UserImpl u where u.name<>'googlebot' AND u.name LIKE CONCAT(:prefix, '%')");
query.setParameter("prefix", prefix);
return query.getResultList();
}
public void addWorkoutParticipants(
final User user, final Long workoutId, final Long... participants)
throws AccessDeniedException {
final WorkoutImpl workout = entityManager.find(WorkoutImpl.class, workoutId);
if (!workout.getUser().equals(user)) throw new AccessDeniedException();
final Set<Long> participantsSet = new HashSet<Long>(Arrays.asList(participants));
final Query query = createParticipantsInsertUnionQuery(workoutId, participantsSet);
query.executeUpdate();
}
public void joinGroup(final User user, final Long groupId) {
final Query query =
entityManager.createNativeQuery(
"insert GROUP_USER SET GROUP_ID=:groupId, USER_ID=:userId, JOINING_DATE=:joiningDate, LAST_VISIT=:joiningDate");
query.setParameter("groupId", groupId);
query.setParameter("userId", user.getId());
query.setParameter("joiningDate", new Date());
query.executeUpdate();
}
public User fetchUser(final String name) throws UserNotFoundException {
final Query query = query("select u from UserImpl u where u.name=:name");
query.setParameter("name", name);
try {
return (User) query.getSingleResult();
} catch (NoResultException e) {
throw new UserNotFoundException();
}
}
public User fetchRememberedUser(final String rememberCookie) throws UserNotFoundException {
final Query query = query("select u from UserImpl u where u.rememberToken =:rememberCookie");
query.setParameter("rememberCookie", rememberCookie);
try {
return (User) query.getSingleResult();
} catch (NoResultException e) {
throw new UserNotFoundException();
}
}
public User facebookLogin(final Long facebookId) {
final Query query = query("select u from UserImpl u where u.facebookId=:facebookId");
query.setParameter("facebookId", facebookId);
try {
return (User) query.getSingleResult();
} catch (NoResultException e) {
return null;
}
}
public User fetchUser(final Long id) throws UserNotFoundException {
final Query query = query("select u from UserImpl u where u.id=:id");
query.setParameter("id", id);
try {
return (User) query.getSingleResult();
} catch (NoResultException e) {
throw new UserNotFoundException();
}
}
public List<User> fetchUsersBeginningByAndAddableToWorkout(final String prefix, final long id) {
final Query query =
entityManager.createQuery(
"select u from UserImpl u, WorkoutImpl workout where u.name<>'googlebot' AND u.name LIKE CONCAT(:prefix, '%')"
+ " AND u not MEMBER OF workout.participants AND workout.id = :id ");
query.setParameter("prefix", prefix);
query.setParameter("id", id);
return query.getResultList();
}
private Double fetchGlobalDistance(final User user) {
final Query query =
query(
"select sum(w.distance) from WorkoutImpl w where 1=1"
+ (user != null ? " and :user MEMBER OF w.participants" : ""));
if (user != null) query.setParameter("user", user);
final Double result = (Double) query.getSingleResult();
return result == null ? Double.valueOf(0) : result;
}
private <T> List<DisciplineData<T>> aggregate(
final String paramName,
final Object paramValue,
final DisciplineAggregator<T> aggregator,
final String selectionPart) {
final Query query = query("select " + aggregator.projectionPart + selectionPart);
if (paramValue != null) query.setParameter(paramName, paramValue);
return aggregator.castqueryResult(query.getResultList());
}
private PaginatedCollection<PrivateMessage> fetchConversation(
final String where, final int pageSize, final int startIndex, final Object... args) {
final Query query =
query("select m from PrivateMessageImpl m where (" + where + ") order by m.date desc");
if (args.length % 2 != 0)
throw new IllegalArgumentException(
"arg count should be even. \"argname1\",argvalue1, \"argname2\", argavalue2");
for (int i = 0; i < args.length; i += 2) query.setParameter((String) args[i], args[i + 1]);
return paginateQuery(pageSize, startIndex, query);
}
private PaginatedCollection<PublicMessage> fetchPublicMessages(
final Topic.Kind kind, final Long id, final int pageSize, final int pageIndex) {
final Query query =
query(
"select m from PublicMessageImpl m where m."
+ (kind == Topic.Kind.WORKOUT ? "workout" : "group")
+ ".id=:id order by m.date desc");
query.setParameter("id", id);
return paginateQuery(pageSize, pageIndex, query);
}
public void removeWorkoutParticipants(
final User user, final Long workoutId, final Long... participants)
throws AccessDeniedException {
final WorkoutImpl workout = entityManager.find(WorkoutImpl.class, workoutId);
if (!workout.getUser().equals(user)) throw new AccessDeniedException();
final Set<Long> participantsWithoutSelf = new HashSet<Long>(Arrays.asList(participants));
participantsWithoutSelf.remove(user.getId());
final Query query = createParticipantsDeleteQuery(workoutId, participantsWithoutSelf);
query.executeUpdate();
}
private Query createParticipantsDeleteQuery(final Long workoutId, final Set<Long> participants) {
final SQLHelper.Predicate idPred =
createInListPredicate("USER_ID", participants, "participant");
final Query query =
entityManager.createNativeQuery(
"delete FROM WORKOUT_USER WHERE WORKOUT_ID = :workoutId AND " + idPred);
query.setParameter("workoutId", workoutId);
idPred.bindVariables(query);
return query;
}
public StatisticsPageData fetchStatisticsPageData(final Long userId, final String discipline)
throws UserNotFoundException {
final User user = fetchUser(userId);
final String fromWhereClause =
" from WorkoutImpl w where :user MEMBER OF w.participants "
+ (discipline != null ? " and discipline = :discipline " : " ");
final Query disciplineQuery =
query(
"select w.discipline, count(*) from WorkoutImpl w "
+ "WHERE :user MEMBER OF w.participants GROUP BY w.discipline ORDER BY count(*) DESC");
disciplineQuery.setParameter("user", user);
final Query query = query("select sum(w.distance) " + fromWhereClause);
bindQuery(query, user, discipline);
final Query query2 =
query(
"select year(w.date), sum(w.distance), sum(w.duration), sum(w.energy) "
+ fromWhereClause
+ " GROUP BY year(w.date) ORDER BY year(w.date) DESC");
bindQuery(query2, user, discipline);
final Query query3 =
query(
"select year(w.date), month(w.date), sum(w.distance), sum(w.duration), sum(w.energy) "
+ fromWhereClause
+ "GROUP BY year(w.date), month(w.date) "
+ "ORDER BY year(w.date) DESC, month(w.date) DESC");
bindQuery(query3, user, discipline);
return new StatisticsPageData(
user,
disciplineQuery.getResultList(),
query.getSingleResult(),
query2.getResultList(),
query3.getResultList());
}
private void updateLastGroupVisit(final User user, final GroupImpl group) {
if (group != null) {
final Query query =
entityManager.createNativeQuery(
"update GROUP_USER SET LAST_VISIT=:now where GROUP_ID=:groupId and USER_ID=:userId");
query.setParameter("now", new Date());
query.setParameter("groupId", group.getId());
query.setParameter("userId", user.getId());
query.executeUpdate();
}
}
public void createTrack(
final User user, final String title, final String points, final double length) {
final Query query =
entityManager.createNativeQuery(
"INSERT INTO TRACKS SET OWNER_ID=:userId, TITLE=:title, POINTS=:points, LENGTH=:length");
query.setParameter("userId", user.getId());
query.setParameter("title", title);
query.setParameter("points", points);
query.setParameter("length", length);
query.executeUpdate();
}
public void updateMessage(
final User user, final Long messageId, final String content, final MessageKind kind)
throws AccessDeniedException {
final String table = kind == MessageKind.PRIVATE ? "MESSAGES" : "PUBLIC_MESSAGES";
final Query query =
entityManager.createNativeQuery(
"update " + table + " SET CONTENT=:content where ID=:id and SENDER_ID=:userId");
query.setParameter("content", content);
query.setParameter("id", messageId);
query.setParameter("userId", user.getId());
if (query.executeUpdate() != 1) throw new AccessDeniedException();
}
private Query createParticipantsInsertUnionQuery(
final Long workoutId, final Set<Long> participants) {
final SQLHelper.Predicate listPred = createInListPredicate("ID", participants, "participant");
final Query query =
entityManager.createNativeQuery(
"insert INTO WORKOUT_USER (USER_ID, WORKOUT_ID) SELECT ID, :workoutId FROM USERS WHERE"
+ " ID not in (select USER_ID from WORKOUT_USER where WORKOUT_ID = :workoutId) and "
+ listPred);
query.setParameter("workoutId", workoutId);
listPred.bindVariables(query);
return query;
}
public void forgotPassword(final String email) throws UserNotFoundException, MailException {
final Query query =
entityManager.createQuery("select u from UserImpl u where u.email=:cryptedMail");
query.setParameter("cryptedMail", CipherHelper.cipher(email));
final List<UserImpl> list = query.getResultList();
if (list.isEmpty()) throw new UserNotFoundException();
for (final UserImpl user : list) {
final String password = Helper.randomstring();
user.setPassword(password);
MailSender.forgotPasswordMail(user.getName(), password, email);
entityManager.merge(user);
}
}
/** @return null if auth failed, user otherwise */
public User authenticate(final String login, final String password, final boolean rememberMe) {
final Query query = query("select u from UserImpl u where u.name=:user_login");
query.setParameter("user_login", login);
try {
final UserImpl user = (UserImpl) query.getSingleResult();
if (rememberMe && user.getRememberToken() == null) {
final String token = generateToken();
user.setRememberToken(token);
entityManager.merge(user);
}
return user.checkPassword(password) ? user : null;
} catch (NoResultException e) {
return null;
}
}
