Index: lams_gradebook/.classpath =================================================================== diff -u -r1861e85da23ae4210dd11fbd93098ffe2f913e6d -r6b9fd327fcba9ac3899bbe5c346cdc02a833a780 --- lams_gradebook/.classpath (.../.classpath) (revision 1861e85da23ae4210dd11fbd93098ffe2f913e6d) +++ lams_gradebook/.classpath (.../.classpath) (revision 6b9fd327fcba9ac3899bbe5c346cdc02a833a780) @@ -1,7 +1,6 @@ - Fisheye: Tag 6b9fd327fcba9ac3899bbe5c346cdc02a833a780 refers to a dead (removed) revision in file `lams_gradebook/conf/hibernate/mappings/org/lamsfoundation/lams/gradebook/model/GradebookUserActivityArchive.hbm.xml'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 6b9fd327fcba9ac3899bbe5c346cdc02a833a780 refers to a dead (removed) revision in file `lams_gradebook/conf/hibernate/mappings/org/lamsfoundation/lams/gradebook/model/GradebookUserLessonArchive.hbm.xml'. Fisheye: No comparison available. Pass `N' to diff? Index: lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/dao/hibernate/GradebookDAO.java =================================================================== diff -u -r7de1c6fbf11121cbf61e6aceca6b4e063d5ffc7a -r6b9fd327fcba9ac3899bbe5c346cdc02a833a780 --- lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/dao/hibernate/GradebookDAO.java (.../GradebookDAO.java) (revision 7de1c6fbf11121cbf61e6aceca6b4e063d5ffc7a) +++ lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/dao/hibernate/GradebookDAO.java (.../GradebookDAO.java) (revision 6b9fd327fcba9ac3899bbe5c346cdc02a833a780) @@ -24,8 +24,8 @@ import java.util.List; -import org.hibernate.Query; -import org.hibernate.SQLQuery; +import org.hibernate.query.NativeQuery; +import org.hibernate.query.Query; import org.lamsfoundation.lams.dao.hibernate.LAMSBaseDAO; import org.lamsfoundation.lams.gradebook.GradebookUserActivity; import org.lamsfoundation.lams.gradebook.GradebookUserLesson; @@ -39,88 +39,69 @@ @Repository public class GradebookDAO extends LAMSBaseDAO implements IGradebookDAO { - private static final String GET_GRADEBOOK_USER_ACTIVITY = "from GradebookUserActivity gact where " - + "gact.learner.userId=:userID and gact.activity.activityId=:activityID"; + private static final String GET_GRADEBOOK_USER_ACTIVITY = "FROM GradebookUserActivity gact WHERE " + + "gact.learner.userId=:userID AND gact.activity.activityId=:activityID"; - private static final String GET_GRADEBOOK_USER_LESSON = "from GradebookUserLesson gles where " - + "gles.learner.userId=:userID and gles.lesson.lessonId=:lessonID"; + private static final String GET_GRADEBOOK_USER_LESSON = "FROM GradebookUserLesson gles WHERE " + + "gles.learner.userId=:userID AND gles.lesson.lessonId=:lessonID"; - private static final String GET_GRADEBOOK_USER_LESSONS = "from GradebookUserLesson gles where " + private static final String GET_GRADEBOOK_USER_LESSONS = "FROM GradebookUserLesson gles WHERE " + "gles.lesson.lessonId=:lessonID"; - private static final String GET_GRADEBOOK_ACTIVITIES_FROM_LESSON = "from GradebookUserActivity gact where " - + "gact.learner=:userID and gact.activity in (select distinct tses.toolActivity from ToolSession tses where tses.lesson=:lessonID)"; + private static final String GET_GRADEBOOK_ACTIVITIES_FROM_LESSON = "FROM GradebookUserActivity gact WHERE " + + "gact.learner.userId=:userID and gact.activity in (select distinct tses.toolActivity from ToolSession tses WHERE tses.lesson.lessonId=:lessonID)"; // private static final String GET_GRADEBOOK_ACTIVITIES_FROM_LESSON_SUM = "select sum(gact.mark) from GradebookUserActivity gact where " // + "gact.learner=:userID and gact.activity in (select distinct tses.toolActivity from ToolSession tses where tses.lesson=:lessonID)"; - private static final String GET_GRADEBOOK_USER_ACTIVITIES_FOR_ACTIVITY = "FROM GradebookUserActivity gact where " + private static final String GET_GRADEBOOK_USER_ACTIVITIES_FOR_ACTIVITY = "FROM GradebookUserActivity gact WHERE " + "gact.activity.activityId=:activityID"; private static final String GET_AVERAGE_MARK_FOR_LESSON = "SELECT AVG(gles.mark) FROM GradebookUserLesson gles WHERE " + "gles.lesson.lessonId=:lessonID"; - private static final String GET_AVERAGE_MARK_FOR_ACTIVTY = "select avg(gact.mark) from GradebookUserActivity gact where " + private static final String GET_AVERAGE_MARK_FOR_ACTIVTY = "SELECT AVG(gact.mark) FROM GradebookUserActivity gact where " + "gact.activity.activityId=:activityID"; - private static final String GET_AVERAGE_MARK_FOR_GROUPED_ACTIVTY = "select avg(gact.mark) from GradebookUserActivity gact, GroupUser gu, Group grp where " + private static final String GET_AVERAGE_MARK_FOR_GROUPED_ACTIVTY = "SELECT AVG(gact.mark) FROM GradebookUserActivity gact, GroupUser gu, Group grp where " + "gact.activity.activityId=:activityID and grp.groupId=:groupID and gu.user=gact.learner and gu.group=grp"; private static final String GET_ALL_MARKS_FOR_LESSON = "SELECT gles.mark FROM GradebookUserLesson gles WHERE " + "gles.lesson.lessonId=:lessonID"; @Override - @SuppressWarnings("unchecked") public GradebookUserActivity getGradebookUserDataForActivity(Long activityID, Integer userID) { - List result = getSessionFactory().getCurrentSession().createQuery(GET_GRADEBOOK_USER_ACTIVITY) - .setInteger("userID", userID.intValue()).setLong("activityID", activityID.longValue()).list(); - - if (result != null) { - if (result.size() > 0) { - return (GradebookUserActivity) result.get(0); - } - } - - return null; + GradebookUserActivity result = getSession() + .createQuery(GET_GRADEBOOK_USER_ACTIVITY, GradebookUserActivity.class).setParameter("userID", userID) + .setParameter("activityID", activityID).uniqueResult(); + return result; } @Override - @SuppressWarnings("unchecked") public GradebookUserLesson getGradebookUserDataForLesson(Long lessonID, Integer userID) { - List result = getSessionFactory().getCurrentSession().createQuery(GET_GRADEBOOK_USER_LESSON) - .setInteger("userID", userID.intValue()).setLong("lessonID", lessonID.longValue()).list(); - - if (result != null) { - if (result.size() > 0) { - return (GradebookUserLesson) result.get(0); - } - } - - return null; + return getSession().createQuery(GET_GRADEBOOK_USER_LESSON, GradebookUserLesson.class) + .setParameter("userID", userID).setParameter("lessonID", lessonID).uniqueResult(); } @Override - @SuppressWarnings("unchecked") public List getGradebookUserDataForLesson(Long lessonID) { - List result = getSession().createQuery(GET_GRADEBOOK_USER_LESSONS) - .setLong("lessonID", lessonID.longValue()).list(); + List result = getSession().createQuery(GET_GRADEBOOK_USER_LESSONS, GradebookUserLesson.class) + .setParameter("lessonID", lessonID).list(); return result; } @Override - @SuppressWarnings("unchecked") public List getGradebookUserActivitiesForLesson(Long lessonID, Integer userID) { - List result = getSessionFactory().getCurrentSession() - .createQuery(GET_GRADEBOOK_ACTIVITIES_FROM_LESSON).setInteger("userID", userID.intValue()) - .setLong("lessonID", lessonID.longValue()).list(); + List result = getSession() + .createQuery(GET_GRADEBOOK_ACTIVITIES_FROM_LESSON, GradebookUserActivity.class) + .setParameter("userID", userID.intValue()).setParameter("lessonID", lessonID).list(); return result; } // @Override -// @SuppressWarnings("unchecked") // public Double getGradebookUserActivityMarkSum(Long lessonID, Integer userID) { -// List result = getSessionFactory().getCurrentSession().createQuery(GET_GRADEBOOK_ACTIVITIES_FROM_LESSON_SUM) +// List result = getSession().createQuery(GET_GRADEBOOK_ACTIVITIES_FROM_LESSON_SUM) // .setInteger("userID", userID.intValue()).setLong("lessonID", lessonID.longValue()).list(); // // if (result != null) { @@ -133,50 +114,39 @@ // } @Override - @SuppressWarnings("unchecked") public List getAllGradebookUserActivitiesForActivity(Long activityID) { - List result = getSessionFactory().getCurrentSession().createQuery(GET_GRADEBOOK_USER_ACTIVITIES_FOR_ACTIVITY) - .setLong("activityID", activityID.longValue()).list(); - - return result; + return getSession().createQuery(GET_GRADEBOOK_USER_ACTIVITIES_FOR_ACTIVITY, GradebookUserActivity.class) + .setParameter("activityID", activityID).list(); } @Override public List getGradebookUserActivitiesForActivity(Long activityID, List userIds) { final String GET_GRADEBOOK_USER_ACTIVITIES_FOR_ACTIVITY = "FROM GradebookUserActivity gact where " + "gact.activity.activityId=:activityID AND gact.learner.userId IN (:userIds)"; - List result = getSession().createQuery(GET_GRADEBOOK_USER_ACTIVITIES_FOR_ACTIVITY) - .setLong("activityID", activityID.longValue()).setParameterList("userIds", userIds).list(); + List result = getSession() + .createQuery(GET_GRADEBOOK_USER_ACTIVITIES_FOR_ACTIVITY, GradebookUserActivity.class) + .setParameter("activityID", activityID).setParameterList("userIds", userIds).list(); return result; } @Override - @SuppressWarnings("unchecked") public Double getAverageMarkForLesson(Long lessonID) { - List result = getSessionFactory().getCurrentSession().createQuery(GET_AVERAGE_MARK_FOR_LESSON) - .setLong("lessonID", lessonID.longValue()).list(); + Double result = getSession().createQuery(GET_AVERAGE_MARK_FOR_LESSON, Double.class) + .setParameter("lessonID", lessonID).uniqueResult(); - if (result != null) { - if (result.size() > 0) { - return (Double) result.get(0); - } - } - - return 0.0; + return result == null ? 0.0 : result; } @Override public List getAllMarksForLesson(Long lessonID) { - return getSessionFactory().getCurrentSession().createQuery(GET_ALL_MARKS_FOR_LESSON) - .setLong("lessonID", lessonID.longValue()).list(); + return getSession().createQuery(GET_ALL_MARKS_FOR_LESSON, Number.class).setParameter("lessonID", lessonID) + .list(); } @Override - @SuppressWarnings("unchecked") public long getMedianTimeTakenLesson(Long lessonID) { - final String GET_MEDIAN_TIME_TAKEN_FOR_LESSON = "SELECT AVG(t1.timeTaken) AS medianVal FROM (" + " SELECT @rownum\\:=@rownum+1 AS `rowNumber`, TIME_TO_SEC(TIMEDIFF(progress.finish_date_time, progress.start_date_time)) AS timeTaken" + " FROM lams_learner_progress progress, (SELECT @rownum\\:=0) r" @@ -186,21 +156,15 @@ + " WHERE progress.lesson_id=:lessonID AND TIMEDIFF(progress.finish_date_time, progress.start_date_time) IS NOT NULL" + " ) AS t2" + " WHERE t1.rowNumber in ( floor((totalRows+1)/2), floor((totalRows+2)/2) )"; - List result = getSession().createSQLQuery(GET_MEDIAN_TIME_TAKEN_FOR_LESSON).setLong("lessonID", lessonID) - .list(); + Object result = getSession().createSQLQuery(GET_MEDIAN_TIME_TAKEN_FOR_LESSON).setParameter("lessonID", lessonID) + .uniqueResult(); - if (result == null || result.size() == 0 || result.get(0) == null) { - return 0; - } else { - //converting into milliseconds - return ((Number) result.get(0)).intValue() * 1000; - } + //converting into milliseconds + return result == null ? 0 : ((Number) result).intValue() * 1000; } @Override - @SuppressWarnings("unchecked") public long getMedianTimeTakenForActivity(Long activityID) { - final String GET_MEDIAN_TIME_TAKEN_FOR_ACTIVITY = "SELECT AVG(t1.timeTaken) AS medianVal FROM (" + " SELECT @rownum\\:=@rownum+1 AS `rowNumber`, TIME_TO_SEC(TIMEDIFF(progress.completed_date_time, progress.start_date_time)) AS timeTaken" + " FROM lams_progress_completed progress, (SELECT @rownum\\:=0) r" @@ -210,94 +174,61 @@ + " WHERE progress.activity_id=:activityID AND TIMEDIFF(progress.completed_date_time, progress.start_date_time) IS NOT NULL" + " ) AS t2" + " WHERE t1.rowNumber in ( floor((totalRows+1)/2), floor((totalRows+2)/2) )"; - List result = getSession().createSQLQuery(GET_MEDIAN_TIME_TAKEN_FOR_ACTIVITY) - .setLong("activityID", activityID.longValue()).list(); + Object result = getSession().createSQLQuery(GET_MEDIAN_TIME_TAKEN_FOR_ACTIVITY) + .setParameter("activityID", activityID).uniqueResult(); - if (result == null || result.size() == 0 || result.get(0) == null) { - return 0; - } else { - //converting into milliseconds - return ((Number) result.get(0)).intValue() * 1000; - } + //converting into milliseconds + return result == null ? 0 : ((Number) result).intValue() * 1000; } @Override - @SuppressWarnings("unchecked") public long getMinTimeTakenForActivity(Long activityID) { - final String GET_MIN_TIME_TAKEN_FOR_ACTIVITY = "SELECT MIN(t1.timeTaken) AS minVal FROM (" + " SELECT @rownum\\:=@rownum+1 AS `rowNumber`, TIME_TO_SEC(TIMEDIFF(progress.completed_date_time, progress.start_date_time)) AS timeTaken" + " FROM lams_progress_completed progress, (SELECT @rownum\\:=0) r" + " WHERE progress.activity_id=:activityID AND TIMEDIFF(progress.completed_date_time, progress.start_date_time) IS NOT NULL" + " ORDER BY TIMEDIFF(progress.completed_date_time, progress.start_date_time)" + " ) AS t1 "; - List result = getSession().createSQLQuery(GET_MIN_TIME_TAKEN_FOR_ACTIVITY) - .setLong("activityID", activityID.longValue()).list(); + Object result = getSession().createSQLQuery(GET_MIN_TIME_TAKEN_FOR_ACTIVITY) + .setParameter("activityID", activityID).uniqueResult(); - if (result == null || result.size() == 0 || result.get(0) == null) { - return 0; - } else { - //converting into milliseconds - return ((Number) result.get(0)).intValue() * 1000; - } + //converting into milliseconds + return result == null ? 0 : ((Number) result).intValue() * 1000; } @Override - @SuppressWarnings("unchecked") public long getMaxTimeTakenForActivity(Long activityID) { - final String GET_MAX_TIME_TAKEN_FOR_ACTIVITY = "SELECT MAX(t1.timeTaken) AS maxVal FROM (" + " SELECT @rownum\\:=@rownum+1 AS `rowNumber`, TIME_TO_SEC(TIMEDIFF(progress.completed_date_time, progress.start_date_time)) AS timeTaken" + " FROM lams_progress_completed progress, (SELECT @rownum\\:=0) r" + " WHERE progress.activity_id=:activityID AND TIMEDIFF(progress.completed_date_time, progress.start_date_time) IS NOT NULL" + " ORDER BY TIMEDIFF(progress.completed_date_time, progress.start_date_time)" + " ) AS t1 "; - List result = getSession().createSQLQuery(GET_MAX_TIME_TAKEN_FOR_ACTIVITY) - .setLong("activityID", activityID.longValue()).list(); + Object result = getSession().createSQLQuery(GET_MAX_TIME_TAKEN_FOR_ACTIVITY) + .setParameter("activityID", activityID).uniqueResult(); - if (result == null || result.size() == 0 || result.get(0) == null) { - return 0; - } else { - //converting into milliseconds - return ((Number) result.get(0)).intValue() * 1000; - } + //converting into milliseconds + return result == null ? 0 : ((Number) result).intValue() * 1000; } @Override - @SuppressWarnings("unchecked") public Double getAverageMarkForActivity(Long activityID) { - List result = getSessionFactory().getCurrentSession().createQuery(GET_AVERAGE_MARK_FOR_ACTIVTY) - .setLong("activityID", activityID.longValue()).list(); + Double result = getSession().createQuery(GET_AVERAGE_MARK_FOR_ACTIVTY, Double.class) + .setParameter("activityID", activityID).getSingleResult(); - if (result != null) { - if (result.size() > 0) { - return (Double) result.get(0); - } - } - - return 0.0; - + return result; } @Override - @SuppressWarnings("unchecked") public Double getAverageMarkForGroupedActivity(Long activityID, Long groupID) { - List result = getSessionFactory().getCurrentSession().createQuery(GET_AVERAGE_MARK_FOR_GROUPED_ACTIVTY) - .setLong("activityID", activityID.longValue()).setLong("groupID", groupID.longValue()).list(); + Double result = getSession().createQuery(GET_AVERAGE_MARK_FOR_GROUPED_ACTIVTY, Double.class) + .setParameter("activityID", activityID).setParameter("groupID", groupID).uniqueResult(); - if (result != null) { - if (result.size() > 0) { - return (Double) result.get(0); - } - } - - return 0.0; + return result == null ? 0.0 : result; } @Override - @SuppressWarnings("unchecked") public long getMedianTimeTakenForGroupedActivity(Long activityID, Long groupID) { - final String GET_MEDIAN_TIME_TAKEN_FOR_GROUPED_ACTIVITY = "SELECT AVG(t1.timeTaken) AS medianVal FROM (" + " SELECT @rownum\\:=@rownum+1 AS `rowNumber`, TIME_TO_SEC(TIMEDIFF(compProgress.completed_date_time, compProgress.start_date_time)) AS timeTaken" + " FROM lams_progress_completed compProgress, (SELECT @rownum\\:=0) r, lams_learner_progress progr, lams_user_group ug " @@ -310,63 +241,48 @@ + " AND ug.group_id=:groupID AND compProgress.learner_progress_id = progr.learner_progress_id AND progr.user_id=ug.user_id" + " ) AS t2" + " WHERE t1.rowNumber in ( floor((totalRows+1)/2), floor((totalRows+2)/2) )"; - List result = getSession().createSQLQuery(GET_MEDIAN_TIME_TAKEN_FOR_GROUPED_ACTIVITY) - .setLong("activityID", activityID.longValue()).setLong("groupID", groupID.longValue()).list(); + Object result = getSession().createSQLQuery(GET_MEDIAN_TIME_TAKEN_FOR_GROUPED_ACTIVITY) + .setParameter("activityID", activityID).setParameter("groupID", groupID).uniqueResult(); - if (result == null || result.size() == 0 || result.get(0) == null) { - return 0; - } else { - //converting into milliseconds - return ((Number) result.get(0)).intValue() * 1000; - } + //converting into milliseconds + return result == null ? 0 : ((Number) result).intValue() * 1000; } @Override - @SuppressWarnings("unchecked") public long getMinTimeTakenForGroupedActivity(Long activityID, Long groupID) { - final String GET_MIN_TIME_TAKEN_FOR_GROUPED_ACTIVITY = "SELECT MIN(t1.timeTaken) AS minVal FROM (" + " SELECT @rownum\\:=@rownum+1 AS `rowNumber`, TIME_TO_SEC(TIMEDIFF(compProgress.completed_date_time, compProgress.start_date_time)) AS timeTaken" + " FROM lams_progress_completed compProgress, (SELECT @rownum\\:=0) r, lams_learner_progress progr, lams_user_group ug " + " WHERE compProgress.activity_id=:activityID AND TIMEDIFF(compProgress.completed_date_time, compProgress.start_date_time) IS NOT NULL" + " AND ug.group_id=:groupID AND compProgress.learner_progress_id = progr.learner_progress_id AND progr.user_id=ug.user_id " + " ORDER BY TIMEDIFF(compProgress.completed_date_time, compProgress.start_date_time)" + " ) AS t1"; - List result = getSession().createSQLQuery(GET_MIN_TIME_TAKEN_FOR_GROUPED_ACTIVITY) - .setLong("activityID", activityID.longValue()).setLong("groupID", groupID.longValue()).list(); + + Object result = getSession().createSQLQuery(GET_MIN_TIME_TAKEN_FOR_GROUPED_ACTIVITY) + .setParameter("activityID", activityID).setParameter("groupID", groupID).uniqueResult(); - if (result == null || result.size() == 0 || result.get(0) == null) { - return 0; - } else { - //converting into milliseconds - return ((Number) result.get(0)).intValue() * 1000; - } + //converting into milliseconds + return result == null ? 0 : ((Number) result).intValue() * 1000; } @Override - @SuppressWarnings("unchecked") public long getMaxTimeTakenForGroupedActivity(Long activityID, Long groupID) { - final String GET_MAX_TIME_TAKEN_FOR_GROUPED_ACTIVITY = "SELECT MAX(t1.timeTaken) AS maxVal FROM (" + " SELECT @rownum\\:=@rownum+1 AS `rowNumber`, TIME_TO_SEC(TIMEDIFF(compProgress.completed_date_time, compProgress.start_date_time)) AS timeTaken" + " FROM lams_progress_completed compProgress, (SELECT @rownum\\:=0) r, lams_learner_progress progr, lams_user_group ug " + " WHERE compProgress.activity_id=:activityID AND TIMEDIFF(compProgress.completed_date_time, compProgress.start_date_time) IS NOT NULL" + " AND ug.group_id=:groupID AND compProgress.learner_progress_id = progr.learner_progress_id AND progr.user_id=ug.user_id " + " ORDER BY TIMEDIFF(compProgress.completed_date_time, compProgress.start_date_time)" + " ) AS t1"; - List result = getSession().createSQLQuery(GET_MAX_TIME_TAKEN_FOR_GROUPED_ACTIVITY) - .setLong("activityID", activityID.longValue()).setLong("groupID", groupID.longValue()).list(); + + Object result = getSession().createSQLQuery(GET_MAX_TIME_TAKEN_FOR_GROUPED_ACTIVITY) + .setParameter("activityID", activityID).setParameter("groupID", groupID).uniqueResult(); - if (result == null || result.size() == 0 || result.get(0) == null) { - return 0; - } else { - //converting into milliseconds - return ((Number) result.get(0)).intValue() * 1000; - } + //converting into milliseconds + return result == null ? 0 : ((Number) result).intValue() * 1000; } @Override public List getLessonsByGroupAndUser(final Integer userId, final Integer orgId, int page, int size, String sortBy, String sortOrder, String searchString) { - final String LOAD_LESSONS_ORDERED_BY_FIELDS = "SELECT DISTINCT lesson " + "FROM Lesson lesson, LearningDesign ld, Group g, GroupUser ug, Organisation lo " + "WHERE lesson.learningDesign.learningDesignId = ld.learningDesignId " + "AND ld.copyTypeID != 3 " @@ -409,15 +325,15 @@ queryString = LOAD_LESSONS_ORDERED_BY_FIELDS; } - Query query = getSession().createQuery(queryString); - query.setInteger("userId", userId.intValue()); - query.setInteger("orgId", orgId.intValue()); + Query query = getSession().createQuery(queryString, Lesson.class); + query.setParameter("userId", userId); + query.setParameter("orgId", orgId); if (!sortBy.equals("avgTimeTaken") && !sortBy.equals("avgMark")) { - query.setString("sortBy", sortBy); + query.setParameter("sortBy", sortBy); } // support for custom search from a toolbar searchString = searchString == null ? "" : searchString; - query.setString("searchString", searchString); + query.setParameter("searchString", searchString); query.setFirstResult(page * size); query.setMaxResults(size); return query.list(); @@ -478,12 +394,13 @@ queryString = LOAD_LEARNERS_ORDERED_BY_NAME; } - SQLQuery query = getSession().createSQLQuery(queryString); + @SuppressWarnings("unchecked") + NativeQuery query = getSession().createSQLQuery(queryString); query.addEntity(User.class); - query.setLong("lessonId", lessonId); + query.setParameter("lessonId", lessonId); // support for custom search from a toolbar searchString = searchString == null ? "" : searchString; - query.setString("searchString", searchString); + query.setParameter("searchString", searchString); query.setFirstResult(page * size); query.setMaxResults(size); return query.list(); @@ -492,7 +409,6 @@ @Override public List getUsersByActivity(Long lessonId, Long activityId, int page, int size, String sortBy, String sortOrder, String searchString) { - final String LOAD_LEARNERS_ORDERED_BY_NAME = "SELECT DISTINCT user.* " + " FROM lams_lesson lesson, lams_group g, lams_user_group ug " + " INNER JOIN lams_user user ON ug.user_id=user.user_id " + " WHERE lesson.lesson_id = :lessonId " @@ -534,15 +450,16 @@ queryString = LOAD_LEARNERS_ORDERED_BY_NAME; } - SQLQuery query = getSession().createSQLQuery(queryString); + @SuppressWarnings("unchecked") + NativeQuery query = getSession().createSQLQuery(queryString); query.addEntity(User.class); - query.setLong("lessonId", lessonId); + query.setParameter("lessonId", lessonId); if (sortBy.equals("timeTaken") || sortBy.equals("mark")) { - query.setLong("activityId", activityId); + query.setParameter("activityId", activityId); } // support for custom search from a toolbar searchString = searchString == null ? "" : searchString; - query.setString("searchString", searchString); + query.setParameter("searchString", searchString); query.setFirstResult(page * size); query.setMaxResults(size); return query.list(); @@ -551,7 +468,6 @@ @Override public List getUsersByGroup(Long lessonId, Long activityId, Long groupId, int page, int size, String sortBy, String sortOrder, String searchString) { - final String LOAD_LEARNERS_ORDERED_BY_NAME = "SELECT DISTINCT user.* " + " FROM lams_user_group ug " + " INNER JOIN lams_user user ON ug.user_id=user.user_id " + " WHERE ug.group_id=:groupId " + " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) " @@ -587,35 +503,30 @@ queryString = LOAD_LEARNERS_ORDERED_BY_NAME; } - SQLQuery query = getSession().createSQLQuery(queryString); + @SuppressWarnings("unchecked") + NativeQuery query = getSession().createSQLQuery(queryString); query.addEntity(User.class); if (sortBy.equals("timeTaken") || sortBy.equals("mark")) { - query.setLong("activityId", activityId); + query.setParameter("activityId", activityId); } - query.setLong("groupId", groupId); + query.setParameter("groupId", groupId); // support for custom search from a toolbar searchString = searchString == null ? "" : searchString; - query.setString("searchString", searchString); + query.setParameter("searchString", searchString); query.setFirstResult(page * size); query.setMaxResults(size); return query.list(); } @Override public int getCountUsersByLesson(Long lessonId, String searchString) { - final String COUNT_COMMENTS_BY_ITEM_AND_USER = "SELECT COUNT(ug.user) " + "FROM Lesson lesson, Group g, GroupUser ug " + "WHERE lesson.lessonId = :lessonId AND lesson.lessonClass.groupingId = g.grouping.groupingId " + "AND ug.group.groupId = g.groupId "; - List list = getSession().createQuery(COUNT_COMMENTS_BY_ITEM_AND_USER).setLong("lessonId", lessonId).list(); - if (list == null || list.size() == 0) { - return 0; - } else { - return ((Number) list.get(0)).intValue(); - } - + return getSession().createQuery(COUNT_COMMENTS_BY_ITEM_AND_USER, Number.class) + .setParameter("lessonId", lessonId).getSingleResult().intValue(); } @Override @@ -629,35 +540,29 @@ + " AND CONCAT(uo.user.lastName, ' ', uo.user.firstName) LIKE CONCAT('%', :searchString, '%') " + " ORDER BY uo.user.lastName " + sortOrder + " , uo.user.firstName " + sortOrder; - Query query = getSession().createQuery(LOAD_LEARNERS_BY_ORG); - query.setLong("orgId", orgId); + Query query = getSession().createQuery(LOAD_LEARNERS_BY_ORG, User.class); + query.setParameter("orgId", orgId); // support for custom search from a toolbar searchString = searchString == null ? "" : searchString; - query.setString("searchString", searchString); + query.setParameter("searchString", searchString); query.setFirstResult(page * size); query.setMaxResults(size); return query.list(); } @Override public int getCountUsersByOrganisation(Integer orgId, String searchString) { - final String COUNT_LEARNERS_BY_ORG = "SELECT COUNT(uo.user) FROM UserOrganisation uo" + " WHERE uo.organisation.organisationId=:orgId" + " AND CONCAT(uo.user.lastName, ' ', uo.user.firstName) LIKE CONCAT('%', :searchString, '%') "; // support for custom search from a toolbar searchString = searchString == null ? "" : searchString; - List list = getSession().createQuery(COUNT_LEARNERS_BY_ORG).setLong("orgId", orgId) - .setString("searchString", searchString).list(); - if (list == null || list.size() == 0) { - return 0; - } else { - return ((Number) list.get(0)).intValue(); - } - + return getSession().createQuery(COUNT_LEARNERS_BY_ORG, Number.class).setParameter("orgId", orgId) + .setParameter("searchString", searchString).getSingleResult().intValue(); } + @SuppressWarnings("unchecked") @Override public List getGradebookUserLessons(Lesson lesson) { String GET_GRADEBOOK_USER_LESSONS_BY_LESSON_ID = "select ul from GradebookUserLesson ul where ul.lesson.lessonId=?"; @@ -669,8 +574,9 @@ String GET_GRADEBOOK_USER_LESSONS_BY_LESSON_AND_USERS = "select ul from GradebookUserLesson ul " + " where ul.lesson.lessonId=:lessonId AND ul.learner.userId IN (:userIds)"; - List results = getSession().createQuery(GET_GRADEBOOK_USER_LESSONS_BY_LESSON_AND_USERS) - .setLong("lessonId", lesson.getLessonId()).setParameterList("userIds", userIds).list(); + List results = getSession() + .createQuery(GET_GRADEBOOK_USER_LESSONS_BY_LESSON_AND_USERS, GradebookUserLesson.class) + .setParameter("lessonId", lesson.getLessonId()).setParameterList("userIds", userIds).list(); return results; } @@ -680,8 +586,8 @@ + " ul.lesson.lessonId IN (:lessonIds)"; List gradebookUserLessons = getSession() - .createQuery(GET_GRADEBOOK_LEARNER_LESSONS_BY_LESSON_LIST).setParameterList("lessonIds", lessonIds) - .list(); + .createQuery(GET_GRADEBOOK_LEARNER_LESSONS_BY_LESSON_LIST, GradebookUserLesson.class) + .setParameterList("lessonIds", lessonIds).list(); return gradebookUserLessons; } @@ -690,22 +596,22 @@ public boolean hasArchivedMarks(Long lessonId, Integer userId) { final String HAS_ARCHIVED_MARKS = "SELECT COUNT(*) FROM GradebookUserLessonArchive a WHERE " + " a.lesson.lessonId = :lessonId AND a.learner.userId = :userId"; - long count = (Long) getSession().createQuery(HAS_ARCHIVED_MARKS).setLong("lessonId", lessonId) - .setInteger("userId", userId).uniqueResult(); + long count = (Long) getSession().createQuery(HAS_ARCHIVED_MARKS).setParameter("lessonId", lessonId) + .setParameter("userId", userId).uniqueResult(); return count > 0; } public List getArchivedLessonMarks(Long lessonId, Integer userId) { final String GET_ARCHIVED_LESSON_MARKS = "FROM GradebookUserLessonArchive a WHERE " + " a.lesson.lessonId = :lessonId AND a.learner.userId = :userId ORDER BY a.archiveDate DESC"; - return getSession().createQuery(GET_ARCHIVED_LESSON_MARKS).setLong("lessonId", lessonId) - .setInteger("userId", userId).list(); + return getSession().createQuery(GET_ARCHIVED_LESSON_MARKS, GradebookUserLessonArchive.class) + .setParameter("lessonId", lessonId).setParameter("userId", userId).list(); } public List getArchivedActivityMarks(Long activityId, Integer userId) { final String GET_ARCHIVED_ACTIVITY_MARKS = "FROM GradebookUserActivityArchive a WHERE " + " a.activity.activityId = :activityId AND a.learner.userId = :userId ORDER BY a.archiveDate DESC"; - return getSession().createQuery(GET_ARCHIVED_ACTIVITY_MARKS).setLong("activityId", activityId) - .setInteger("userId", userId).list(); + return getSession().createQuery(GET_ARCHIVED_ACTIVITY_MARKS, GradebookUserActivityArchive.class) + .setParameter("activityId", activityId).setParameter("userId", userId).list(); } } Index: lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/model/GradebookUserActivityArchive.java =================================================================== diff -u -r471b903caa3365758fbdec0a22440b1b0b3f2947 -r6b9fd327fcba9ac3899bbe5c346cdc02a833a780 --- lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/model/GradebookUserActivityArchive.java (.../GradebookUserActivityArchive.java) (revision 471b903caa3365758fbdec0a22440b1b0b3f2947) +++ lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/model/GradebookUserActivityArchive.java (.../GradebookUserActivityArchive.java) (revision 6b9fd327fcba9ac3899bbe5c346cdc02a833a780) @@ -24,18 +24,46 @@ import java.util.Date; +import javax.persistence.Column; +import javax.persistence.Entity; +import javax.persistence.Id; +import javax.persistence.JoinColumn; +import javax.persistence.ManyToOne; +import javax.persistence.Table; + import org.lamsfoundation.lams.gradebook.GradebookUserActivity; import org.lamsfoundation.lams.learningdesign.ToolActivity; import org.lamsfoundation.lams.usermanagement.User; +@Entity +@Table(name = "lams_gradebook_user_activity_archive") public class GradebookUserActivityArchive { + + @Id + @Column private long uid; + + @ManyToOne + @JoinColumn(name = "activity_id") private ToolActivity activity; + + @ManyToOne + @JoinColumn(name = "user_id") private User learner; + + @Column private Double mark; + + @Column private String feedback; + + @Column(name = "marked_in_gradebook") private Boolean markedInGradebook; + + @Column(name = "update_date") private Date updateDate; + + @Column(name = "archive_date") private Date archiveDate; public GradebookUserActivityArchive() { Index: lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/model/GradebookUserLessonArchive.java =================================================================== diff -u -r471b903caa3365758fbdec0a22440b1b0b3f2947 -r6b9fd327fcba9ac3899bbe5c346cdc02a833a780 --- lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/model/GradebookUserLessonArchive.java (.../GradebookUserLessonArchive.java) (revision 471b903caa3365758fbdec0a22440b1b0b3f2947) +++ lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/model/GradebookUserLessonArchive.java (.../GradebookUserLessonArchive.java) (revision 6b9fd327fcba9ac3899bbe5c346cdc02a833a780) @@ -22,17 +22,42 @@ package org.lamsfoundation.lams.gradebook.model; -import org.lamsfoundation.lams.gradebook.GradebookUserLesson; import java.util.Date; + +import javax.persistence.Column; +import javax.persistence.Entity; +import javax.persistence.Id; +import javax.persistence.JoinColumn; +import javax.persistence.ManyToOne; +import javax.persistence.Table; + +import org.lamsfoundation.lams.gradebook.GradebookUserLesson; import org.lamsfoundation.lams.lesson.Lesson; import org.lamsfoundation.lams.usermanagement.User; +@Entity +@Table(name = "lams_gradebook_user_lesson_archive") public class GradebookUserLessonArchive { + + @Id + @Column private long uid; + + @ManyToOne + @JoinColumn(name = "lesson_id") private Lesson lesson; + + @ManyToOne + @JoinColumn(name = "user_id") private User learner; + + @Column private Double mark; + + @Column private String feedback; + + @Column(name = "archive_date") private Date archiveDate; public GradebookUserLessonArchive() {