Index: idea_project/.idea/misc.xml =================================================================== diff -u -ra1064a52a646aa043ea1fe371726537fd305c5de -r9e0121a4bc18308e295ff09613aac8e2c568ce5b --- idea_project/.idea/misc.xml (.../misc.xml) (revision a1064a52a646aa043ea1fe371726537fd305c5de) +++ idea_project/.idea/misc.xml (.../misc.xml) (revision 9e0121a4bc18308e295ff09613aac8e2c568ce5b) @@ -5,7 +5,7 @@ - + Index: lams_common/src/java/org/lamsfoundation/lams/dao/hibernate/LAMSBaseDAO.java =================================================================== diff -u -r129d0eb39f81fcdb4ef6e3b5ec15c1c94d9324b3 -r9e0121a4bc18308e295ff09613aac8e2c568ce5b --- lams_common/src/java/org/lamsfoundation/lams/dao/hibernate/LAMSBaseDAO.java (.../LAMSBaseDAO.java) (revision 129d0eb39f81fcdb4ef6e3b5ec15c1c94d9324b3) +++ lams_common/src/java/org/lamsfoundation/lams/dao/hibernate/LAMSBaseDAO.java (.../LAMSBaseDAO.java) (revision 9e0121a4bc18308e295ff09613aac8e2c568ce5b) @@ -13,8 +13,8 @@ import jakarta.persistence.criteria.CriteriaBuilder; import jakarta.persistence.criteria.CriteriaQuery; import jakarta.persistence.criteria.Root; - import org.apache.commons.lang3.StringUtils; +import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; import org.hibernate.Hibernate; import org.hibernate.HibernateException; Index: lams_common/src/java/org/lamsfoundation/lams/lesson/dao/hibernate/LessonDAO.java =================================================================== diff -u -r129d0eb39f81fcdb4ef6e3b5ec15c1c94d9324b3 -r9e0121a4bc18308e295ff09613aac8e2c568ce5b --- lams_common/src/java/org/lamsfoundation/lams/lesson/dao/hibernate/LessonDAO.java (.../LessonDAO.java) (revision 129d0eb39f81fcdb4ef6e3b5ec15c1c94d9324b3) +++ lams_common/src/java/org/lamsfoundation/lams/lesson/dao/hibernate/LessonDAO.java (.../LessonDAO.java) (revision 9e0121a4bc18308e295ff09613aac8e2c568ce5b) @@ -23,6 +23,12 @@ package org.lamsfoundation.lams.lesson.dao.hibernate; +import jakarta.persistence.TypedQuery; +import jakarta.persistence.criteria.CriteriaBuilder; +import jakarta.persistence.criteria.CriteriaQuery; +import jakarta.persistence.criteria.JoinType; +import jakarta.persistence.criteria.ParameterExpression; +import jakarta.persistence.criteria.Root; import org.apache.commons.lang3.StringEscapeUtils; import org.apache.commons.lang3.StringUtils; import org.hibernate.FetchMode; @@ -52,65 +58,73 @@ */ @Repository public class LessonDAO extends LAMSBaseDAO implements ILessonDAO { - private final static String FIND_LESSON_BY_CREATOR = "from " + Lesson.class.getName() - + " lesson where lesson.user.userId=? and lesson.lessonStateId <= 6 and " - + " lesson.learningDesign.copyTypeID=" + LearningDesign.COPY_TYPE_LESSON; - private final static String FIND_PREVIEW_BEFORE_START_DATE = "from " + Lesson.class.getName() - + " lesson where lesson.learningDesign.copyTypeID=" + LearningDesign.COPY_TYPE_PREVIEW - + "and lesson.startDateTime is not null and lesson.startDateTime < ?"; - private final static String COUNT_ACTIVE_LEARNERS = "select count(distinct progress.user.id)" + " from " - + LearnerProgress.class.getName() + " progress" + " where progress.lesson.id = :lessonId"; - private final static String LOAD_ACTIVE_LEARNERS = "select distinct progress.user from " - + LearnerProgress.class.getName() + " progress where progress.lesson.id = :lessonId"; - private final static String FIND_LESSON_FOR_ACTIVITY = "select lesson from " + Lesson.class.getName() + " lesson, " - + Activity.class.getName() + " activity " - + " where activity.activityId=:activityId and activity.learningDesign=lesson.learningDesign"; - private final static String FIND_LESSON_ACTIVITY_IDS_BY_TOOL_CONTENT_ID = "SELECT lesson.lessonId, toolActivity.activityId FROM " - + Lesson.class.getName() + " lesson, " + ToolActivity.class.getName() + " toolActivity " - + " WHERE toolActivity.learningDesign = lesson.learningDesign " - + " AND toolActivity.toolContentId = :toolContentId"; - private final static String LESSONS_WITH_ORIGINAL_LEARNING_DESIGN = "select l from " + Lesson.class.getName() - + " l " + "where l.learningDesign.originalLearningDesign.learningDesignId = ? " - + "and l.learningDesign.copyTypeID != " + LearningDesign.COPY_TYPE_PREVIEW + " " + "and l.lessonStateId = " - + Lesson.STARTED_STATE + " " + "and l.organisation.organisationId = ? " + " order by l.lessonName"; - private final static String LESSONS_BY_GROUP = "from " + Lesson.class.getName() - + " where organisation.organisationId=? and lessonStateId <= 6"; + private final static String FIND_LESSON_BY_CREATOR = + "from " + Lesson.class.getName() + " lesson where lesson.user.userId=? and lesson.lessonStateId <= 6 and " + + " lesson.learningDesign.copyTypeID=" + LearningDesign.COPY_TYPE_LESSON; + private final static String FIND_PREVIEW_BEFORE_START_DATE = + "from " + Lesson.class.getName() + " lesson where lesson.learningDesign.copyTypeID=" + + LearningDesign.COPY_TYPE_PREVIEW + + "and lesson.startDateTime is not null and lesson.startDateTime < ?"; + private final static String COUNT_ACTIVE_LEARNERS = + "select count(distinct progress.user.id)" + " from " + LearnerProgress.class.getName() + " progress" + + " where progress.lesson.id = :lessonId"; + private final static String LOAD_ACTIVE_LEARNERS = + "select distinct progress.user from " + LearnerProgress.class.getName() + + " progress where progress.lesson.id = :lessonId"; + private final static String FIND_LESSON_FOR_ACTIVITY = + "select lesson from " + Lesson.class.getName() + " lesson, " + Activity.class.getName() + " activity " + + " where activity.activityId=:activityId and activity.learningDesign=lesson.learningDesign"; + private final static String FIND_LESSON_ACTIVITY_IDS_BY_TOOL_CONTENT_ID = + "SELECT lesson.lessonId, toolActivity.activityId FROM " + Lesson.class.getName() + " lesson, " + + ToolActivity.class.getName() + " toolActivity " + + " WHERE toolActivity.learningDesign = lesson.learningDesign " + + " AND toolActivity.toolContentId = :toolContentId"; + private final static String LESSONS_WITH_ORIGINAL_LEARNING_DESIGN = + "select l from " + Lesson.class.getName() + " l " + + "where l.learningDesign.originalLearningDesign.learningDesignId = ? " + + "and l.learningDesign.copyTypeID != " + LearningDesign.COPY_TYPE_PREVIEW + " " + + "and l.lessonStateId = " + Lesson.STARTED_STATE + " " + "and l.organisation.organisationId = ? " + + " order by l.lessonName"; + private final static String LESSONS_BY_GROUP = + "from " + Lesson.class.getName() + " where organisation.organisationId=? and lessonStateId <= 6"; private final static String LOAD_LEARNERS_BY_LESSON = "FROM Lesson AS lesson " + "INNER JOIN lesson.lessonClass AS lessonClass INNER JOIN lessonClass.groups AS groups " + "INNER JOIN groups.users AS users WHERE lesson.id = :lessonId AND lessonClass.staffGroup != groups"; - private final static String LOAD_USERS_WITH_LESSON_PARTICIPATION = "SELECT users.*, ug.user_id IS NOT NULL AS participant " - + "FROM lams_lesson AS l " - + "JOIN lams_user_organisation AS uo ON l.lesson_id = :lessonId AND l.organisation_id = uo.organisation_id " - + "JOIN lams_user_organisation_role AS r ON r.role_id = :roleId AND r.user_organisation_id = uo.user_organisation_id " - + "JOIN lams_user AS users ON uo.user_id = users.user_id " - + "JOIN lams_grouping AS ging ON l.class_grouping_id = ging.grouping_id " - + "JOIN lams_group AS g ON g.group_id ging.staff_group_id AND g.grouping_id = ging.grouping_id " - + "LEFT JOIN lams_user_group AS ug ON ug.group_id = g.group_id AND users.user_id = ug.user_id"; + private final static String LOAD_USERS_WITH_LESSON_PARTICIPATION = + "SELECT users.*, ug.user_id IS NOT NULL AS participant " + "FROM lams_lesson AS l " + + "JOIN lams_user_organisation AS uo ON l.lesson_id = :lessonId AND l.organisation_id = uo.organisation_id " + + "JOIN lams_user_organisation_role AS r ON r.role_id = :roleId AND r.user_organisation_id = uo.user_organisation_id " + + "JOIN lams_user AS users ON uo.user_id = users.user_id " + + "JOIN lams_grouping AS ging ON l.class_grouping_id = ging.grouping_id " + + "JOIN lams_group AS g ON g.group_id ging.staff_group_id AND g.grouping_id = ging.grouping_id " + + "LEFT JOIN lams_user_group AS ug ON ug.group_id = g.group_id AND users.user_id = ug.user_id"; private final static String COUNT_LESSONS = "SELECT COUNT (*) FROM " + Lesson.class.getName(); - private final static String COUNT_PREVIEW_LESSONS = "SELECT COUNT(*) FROM " + Lesson.class.getName() - + " AS lesson WHERE lesson.learningDesign.copyTypeID = " + LearningDesign.COPY_TYPE_PREVIEW; + private final static String COUNT_PREVIEW_LESSONS = + "SELECT COUNT(*) FROM " + Lesson.class.getName() + " AS lesson WHERE lesson.learningDesign.copyTypeID = " + + LearningDesign.COPY_TYPE_PREVIEW; private final static String FIND_PREVIEW_LESSON_IDS = "SELECT lesson.lessonId FROM " + Lesson.class.getName() + " AS lesson WHERE lesson.learningDesign.copyTypeID = " + LearningDesign.COPY_TYPE_PREVIEW; private final static String FIND_LESSON_IDS_BY_ORG_ID = "SELECT lesson.lessonId FROM " + Lesson.class.getName() + " AS lesson WHERE lesson.organisation.organisationId = :organisationId"; - private final static String FIND_ABSOLUTE_TIME_LIMITS = "SELECT a.tool_content_id AS toolContentId, a.title AS activityTitle, " - + "(SELECT absolute_time_limit_finish FROM tl_lascrt11_scratchie WHERE content_id = tool_content_id UNION " - + " SELECT absolute_time_limit_finish FROM tl_laasse10_assessment WHERE content_id = tool_content_id " - + ") AS absolute_time_limit " - + "FROM lams_lesson AS l JOIN lams_learning_activity AS a USING (learning_design_id) " - + "WHERE l.lesson_id = :lessonId AND a.tool_content_id IS NOT NULL " - + "HAVING absolute_time_limit > UTC_TIMESTAMP() ORDER BY absolute_time_limit"; + private final static String FIND_ABSOLUTE_TIME_LIMITS = + "SELECT a.tool_content_id AS toolContentId, a.title AS activityTitle, " + + "(SELECT absolute_time_limit_finish FROM tl_lascrt11_scratchie WHERE content_id = tool_content_id UNION " + + " SELECT absolute_time_limit_finish FROM tl_laasse10_assessment WHERE content_id = tool_content_id " + + ") AS absolute_time_limit " + + "FROM lams_lesson AS l JOIN lams_learning_activity AS a USING (learning_design_id) " + + "WHERE l.lesson_id = :lessonId AND a.tool_content_id IS NOT NULL " + + "HAVING absolute_time_limit > UTC_TIMESTAMP() ORDER BY absolute_time_limit"; /** * Retrieves the Lesson. Used in instances where it cannot be lazy loaded so it forces an initialize. * * @param lessonId - * identifies the lesson to get + * identifies the lesson to get * @return the lesson */ @Override @@ -121,16 +135,22 @@ @Override public Lesson getLessonWithJoinFetchedProgress(Long lessonId) { + CriteriaBuilder criteriaBuilder = getSession().getCriteriaBuilder(); + CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(Lesson.class); + Root criteriaRoot = criteriaQuery.from(Lesson.class); + criteriaRoot.fetch("learnerProgresses"); + ParameterExpression lessonIdParam = criteriaBuilder.parameter(Long.class); + criteriaQuery.select(criteriaRoot).where(criteriaBuilder.equal(criteriaRoot.get("lessonId"), lessonIdParam)); - return (Lesson) getSession().createCriteria(Lesson.class).add(Restrictions.like("lessonId", lessonId)) - .setFetchMode("learnerProgresses", FetchMode.JOIN).uniqueResult(); + TypedQuery query = getSession().createQuery(criteriaQuery); + return query.getSingleResult(); } /** * Gets all lessons that are active for a learner. * * @param learner - * a User that identifies the learner. + * a User that identifies the learner. * @return a List with all active lessons in it. */ @Override @@ -153,7 +173,7 @@ /** * @see org.lamsfoundation.lams.lesson.dao.ILessonDAO#getActiveLearnerByLesson(long) Note: Hibernate 3.1 - * query.uniqueResult() returns Integer, Hibernate 3.2 query.uniqueResult() returns Long + * query.uniqueResult() returns Integer, Hibernate 3.2 query.uniqueResult() returns Long */ @Override public Integer getCountActiveLearnerByLesson(long lessonId) { @@ -192,8 +212,8 @@ @Override public Integer getCountLearnersByLesson(long lessonId, String searchPhrase) { - StringBuilder queryTextBuilder = new StringBuilder("SELECT COUNT(*) ") - .append(LessonDAO.LOAD_LEARNERS_BY_LESSON); + StringBuilder queryTextBuilder = new StringBuilder("SELECT COUNT(*) ").append( + LessonDAO.LOAD_LEARNERS_BY_LESSON); if (!StringUtils.isBlank(searchPhrase)) { String[] tokens = searchPhrase.trim().split("\\s+"); for (String token : tokens) { @@ -244,7 +264,7 @@ * lessons. * * @param userID - * The user_id of the user + * The user_id of the user * @return List The list of Lessons for the given user */ @Override @@ -257,7 +277,7 @@ * Get all the preview lessons more with the creation date before the given date. * * @param startDate - * UTC date + * UTC date * @return the list of Lessons */ @Override @@ -278,8 +298,8 @@ } /** - * Get the lesson and activity ids that apply to the tool activity associated with this tool content id. - * Returns an array of two longs. + * Get the lesson and activity ids that apply to the tool activity associated with this tool content id. Returns an + * array of two longs. */ @Override public Long[] getLessonActivityIdsForToolContentId(long toolContentId) { @@ -298,7 +318,7 @@ /** * @see org.lamsfoundation.lams.lesson.dao.ILessonDAO#getLessonsByOrgAndUserWithCompletedFlag(Integer, Integer, - * boolean) + * boolean) */ @Override public List getLessonsByOrgAndUserWithCompletedFlag(Integer userId, Integer orgId, Integer userRole) { @@ -322,7 +342,7 @@ /** * @see org.lamsfoundation.lams.lesson.dao.ILessonDAO#getLessonsByOrgAndUserWithCompletedFlag(Integer, Integer, - * boolean) + * boolean) */ @Override public List getLessonsByGroupAndUser(Integer userId, Integer orgId) { @@ -446,15 +466,15 @@ public List getRunningAbsoluteTimeLimits(long lessonId) { StringBuilder additionalToolsBuilder = new StringBuilder(); - boolean toolAvailable = !findByProperty(Tool.class, "toolSignature", CommonConstants.TOOL_SIGNATURE_DOKU, true) - .isEmpty(); + boolean toolAvailable = !findByProperty(Tool.class, "toolSignature", CommonConstants.TOOL_SIGNATURE_DOKU, + true).isEmpty(); if (toolAvailable) { additionalToolsBuilder.append( "UNION SELECT absolute_time_limit_finish FROM tl_ladoku11_dokumaran WHERE content_id = tool_content_id "); } - toolAvailable = !findByProperty(Tool.class, "toolSignature", CommonConstants.TOOL_SIGNATURE_WHITEBOARD, true) - .isEmpty(); + toolAvailable = !findByProperty(Tool.class, "toolSignature", CommonConstants.TOOL_SIGNATURE_WHITEBOARD, + true).isEmpty(); if (toolAvailable) { additionalToolsBuilder.append( "UNION SELECT absolute_time_limit_finish FROM tl_lawhiteboard11_whiteboard WHERE content_id = tool_content_id");