예제 #1
0
 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;
 }
예제 #2
0
 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();
 }
예제 #3
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();
 }
예제 #4
0
 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();
 }
예제 #5
0
 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();
 }
예제 #6
0
 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();
 }
예제 #7
0
 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();
   }
 }
예제 #8
0
 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();
 }
예제 #9
0
 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();
 }
예제 #10
0
 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();
 }
예제 #11
0
 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());
 }
예제 #12
0
 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());
 }
예제 #13
0
 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());
 }
예제 #14
0
 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;
 }
예제 #15
0
 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();
 }
예제 #16
0
 public void deleteMessageFor(final Long id, final User user) {
   final Query query =
       entityManager.createNativeQuery(
           "update MESSAGES SET deleted_by=:user_id where ID=:id and deleted_by IS NULL and receiver_id <> sender_id and (receiver_id=:user_id or sender_id=:user_id)");
   query.setParameter("user_id", user.getId());
   query.setParameter("id", id);
   final int updated = query.executeUpdate();
   if (updated == 0) {
     final Query query2 =
         entityManager.createNativeQuery(
             "delete from MESSAGES where ID=:id and (receiver_id=:user_id or sender_id=:user_id)");
     query2.setParameter("id", id);
     query2.setParameter("user_id", user.getId());
     query2.executeUpdate();
   }
 }
예제 #17
0
 @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();
 }
예제 #18
0
 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;
   }
 }
예제 #19
0
 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;
 }
예제 #20
0
 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());
 }
예제 #21
0
 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();
   }
 }
예제 #22
0
 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();
   }
 }
예제 #23
0
 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();
   }
 }
예제 #24
0
 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);
 }
예제 #25
0
 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);
 }
예제 #26
0
 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;
 }
예제 #27
0
 public void updateGroup(
     final User user, final Long groupId, final String name, final String description)
     throws GroupNotFoundException, AccessDeniedException, NameClashException {
   final GroupImpl group = entityManager.find(GroupImpl.class, groupId);
   if (group == null) throw new GroupNotFoundException();
   if (group.getOwner().equals(user)) {
     final Query query =
         entityManager.createNativeQuery(
             "update GROUPS SET NAME=:name, DESCRIPTION=:description where ID=:groupId and OWNER_ID=:userId");
     query.setParameter("name", name);
     query.setParameter("description", description);
     query.setParameter("groupId", groupId);
     query.setParameter("userId", user.getId());
     try {
       query.executeUpdate();
     } catch (EntityExistsException e) {
       throw new NameClashException();
     }
   } else throw new AccessDeniedException();
 }
예제 #28
0
 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;
 }
예제 #29
0
 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);
   }
 }
예제 #30
0
 /** @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;
   }
 }