Index: lams_central/src/java/org/lamsfoundation/lams/web/DisplayGroupAction.java =================================================================== diff -u -r84a82bbd94bae1c8d2ae196805f033712b726711 -r89403052c08187d7d7c341b07b21529411e2cef6 --- lams_central/src/java/org/lamsfoundation/lams/web/DisplayGroupAction.java (.../DisplayGroupAction.java) (revision 84a82bbd94bae1c8d2ae196805f033712b726711) +++ lams_central/src/java/org/lamsfoundation/lams/web/DisplayGroupAction.java (.../DisplayGroupAction.java) (revision 89403052c08187d7d7c341b07b21529411e2cef6) @@ -23,30 +23,24 @@ /* $Id$ */ package org.lamsfoundation.lams.web; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collections; -import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Set; -import javax.naming.InitialContext; import javax.naming.NamingException; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; -import javax.sql.DataSource; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import org.apache.struts.action.Action; import org.apache.struts.action.ActionForm; import org.apache.struts.action.ActionForward; import org.apache.struts.action.ActionMapping; -import org.lamsfoundation.lams.learningdesign.LearningDesign; +import org.lamsfoundation.lams.learning.service.ICoreLearnerService; import org.lamsfoundation.lams.lesson.Lesson; import org.lamsfoundation.lams.usermanagement.Organisation; import org.lamsfoundation.lams.usermanagement.OrganisationState; @@ -72,6 +66,7 @@ private static Logger log = Logger.getLogger(DisplayGroupAction.class); private static IUserManagementService service; + private static ICoreLearnerService learnerService; private Integer stateId = OrganisationState.ACTIVE; @SuppressWarnings({"unchecked"}) @@ -209,7 +204,7 @@ throws SQLException, NamingException { // iterate through user's lessons where they are learner - Map map = getLessonsByOrgAndUser(userId, orgId, false); + Map map = getLearnerService().getLessonsByOrgAndUserWithCompletedFlag(userId, orgId, false); for (IndexLessonBean bean : map.values()) { List lessonLinks = new ArrayList(); String url = null; @@ -234,7 +229,7 @@ } // iterate through user's lessons where they are staff, and add staff links to the beans in the map. - Map staffMap = getLessonsByOrgAndUser(userId, orgId, true); + Map staffMap = getLearnerService().getLessonsByOrgAndUserWithCompletedFlag(userId, orgId, true); for (IndexLessonBean bean : staffMap.values()) { if (map.containsKey(bean.getId())) { bean = map.get(bean.getId()); @@ -275,57 +270,12 @@ return service; } - // get lesson beans where user is learner (if isStaff=true, gets lessons where user is staff) - private Map getLessonsByOrgAndUser(Integer userId, Integer orgId, boolean isStaff) - throws SQLException, NamingException { - // TODO refactor using hibernate named query - String learnerQuery = "select l.lesson_id, l.name, l.description, l.lesson_state_id, lp.lesson_completed_flag " - + " from (lams_lesson l, lams_learning_design ld, lams_group g, lams_user_group ug, lams_grouping gi)" - + " left join lams_learner_progress lp on lp.user_id=ug.user_id and lp.lesson_id=l.lesson_id" - + " where l.learning_design_id=ld.learning_design_id" - + " and ld.copy_type_id!=" + LearningDesign.COPY_TYPE_PREVIEW - + " and l.organisation_id=?" - + " and l.class_grouping_id=g.grouping_id" - + " and l.lesson_state_id!=" + Lesson.REMOVED_STATE - + " and ug.group_id=g.group_id" - + " and ug.user_id=?" - + " and gi.grouping_id=g.grouping_id" - + " and g.group_id" + (isStaff ? "" : "!") + "=gi.staff_group_id"; - - InitialContext ctx = new InitialContext(); - DataSource ds = (DataSource) ctx.lookup("java:/jdbc/lams-ds"); - Connection conn = null; - - HashMap map = new HashMap(); - - try { - conn = ds.getConnection(); - PreparedStatement ps = conn.prepareStatement(learnerQuery); - ps.setInt(1, orgId.intValue()); - ps.setInt(2, userId.intValue()); - ResultSet rs = ps.executeQuery(); - - // check if there is any result - while (rs.next() != false) { - long id = rs.getLong(1); - String name = rs.getString(2); - String description = rs.getString(3); - int state = rs.getInt(4); - boolean lessonCompleted = rs.getBoolean(5); - IndexLessonBean bean = new IndexLessonBean( - new Long(id), name, description, new Integer(state), lessonCompleted - ); - map.put(new Long(id), bean); - } - - rs.close(); - } finally { - if (conn != null && !conn.isClosed()) { - conn.close(); - } + private ICoreLearnerService getLearnerService(){ + if(learnerService==null){ + WebApplicationContext ctx = WebApplicationContextUtils.getRequiredWebApplicationContext(getServlet().getServletContext()); + learnerService = (ICoreLearnerService) ctx.getBean("learnerService"); } - - return map; + return learnerService; } } \ No newline at end of file Index: lams_common/conf/hibernate/mappings/org/lamsfoundation/lams/lesson/Lesson.hbm.xml =================================================================== diff -u -rc4c2a97ede68a889bd36949cdefb2f84132fa9fc -r89403052c08187d7d7c341b07b21529411e2cef6 --- lams_common/conf/hibernate/mappings/org/lamsfoundation/lams/lesson/Lesson.hbm.xml (.../Lesson.hbm.xml) (revision c4c2a97ede68a889bd36949cdefb2f84132fa9fc) +++ lams_common/conf/hibernate/mappings/org/lamsfoundation/lams/lesson/Lesson.hbm.xml (.../Lesson.hbm.xml) (revision 89403052c08187d7d7c341b07b21529411e2cef6) @@ -188,4 +188,44 @@ AND (lams_learner_progress.lesson_id = :lessonId) + + + + + + + SELECT l.lesson_id, l.name, l.description, l.lesson_state_id, lp.lesson_completed_flag + FROM (lams_lesson l, lams_learning_design ld, lams_group g, lams_user_group ug, lams_grouping gi) + LEFT JOIN lams_learner_progress lp ON lp.user_id = ug.user_id AND lp.lesson_id = l.lesson_id + WHERE l.learning_design_id = ld.learning_design_id + AND ld.copy_type_id != 3 + AND l.organisation_id = :orgId + AND l.class_grouping_id = g.grouping_id + AND l.lesson_state_id != 7 + AND ug.group_id = g.group_id + AND ug.user_id = :userId + AND gi.grouping_id = g.grouping_id + AND g.group_id != gi.staff_group_id + + + + + + + + + SELECT l.lesson_id, l.name, l.description, l.lesson_state_id, lp.lesson_completed_flag + FROM (lams_lesson l, lams_learning_design ld, lams_group g, lams_user_group ug, lams_grouping gi) + LEFT JOIN lams_learner_progress lp ON lp.user_id = ug.user_id AND lp.lesson_id = l.lesson_id + WHERE l.learning_design_id = ld.learning_design_id + AND ld.copy_type_id != 3 + AND l.organisation_id = :orgId + AND l.class_grouping_id = g.grouping_id + AND l.lesson_state_id != 7 + AND ug.group_id = g.group_id + AND ug.user_id = :userId + AND gi.grouping_id = g.grouping_id + AND g.group_id = gi.staff_group_id + + Index: lams_common/src/java/org/lamsfoundation/lams/lesson/dao/ILessonDAO.java =================================================================== diff -u -rfda92810b8dec25617d853c94555c13ee735efb4 -r89403052c08187d7d7c341b07b21529411e2cef6 --- lams_common/src/java/org/lamsfoundation/lams/lesson/dao/ILessonDAO.java (.../ILessonDAO.java) (revision fda92810b8dec25617d853c94555c13ee735efb4) +++ lams_common/src/java/org/lamsfoundation/lams/lesson/dao/ILessonDAO.java (.../ILessonDAO.java) (revision 89403052c08187d7d7c341b07b21529411e2cef6) @@ -127,5 +127,15 @@ * Get the lesson that applies to this activity. Not all activities have an attached lesson. */ public Lesson getLessonForActivity(final long activityId); + + /** + * Gets all non-removed lessons for a user in an org; set isStaff flag whether you want lessons where user + * is in the staff list, or just in the learner list. + * @param userId a user id that identifies the user. + * @param orgId an org id that identifies the organisation. + * @param isStaff boolean flag for whether user is staff in returned lessons. + * @return a List containing a list of tuples containing lesson details and the lesson completed flag for the user. + */ + public List getLessonsByOrgAndUserWithCompletedFlag(final Integer userId, final Integer orgId, final boolean isStaff); } Index: lams_common/src/java/org/lamsfoundation/lams/lesson/dao/hibernate/LessonDAO.java =================================================================== diff -u -r315fad9f727a91c28b274654eab02cac89d76b9c -r89403052c08187d7d7c341b07b21529411e2cef6 --- lams_common/src/java/org/lamsfoundation/lams/lesson/dao/hibernate/LessonDAO.java (.../LessonDAO.java) (revision 315fad9f727a91c28b274654eab02cac89d76b9c) +++ lams_common/src/java/org/lamsfoundation/lams/lesson/dao/hibernate/LessonDAO.java (.../LessonDAO.java) (revision 89403052c08187d7d7c341b07b21529411e2cef6) @@ -31,12 +31,12 @@ import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.criterion.Expression; +import org.lamsfoundation.lams.dao.hibernate.BaseDAO; import org.lamsfoundation.lams.learningdesign.Activity; import org.lamsfoundation.lams.learningdesign.LearningDesign; import org.lamsfoundation.lams.lesson.LearnerProgress; import org.lamsfoundation.lams.lesson.Lesson; import org.lamsfoundation.lams.lesson.dao.ILessonDAO; -import org.lamsfoundation.lams.dao.hibernate.BaseDAO; import org.lamsfoundation.lams.usermanagement.User; import org.springframework.orm.hibernate3.HibernateCallback; import org.springframework.orm.hibernate3.HibernateTemplate; @@ -102,7 +102,6 @@ /** * Gets all lessons that are active for a learner. - * TODO remove when the dummy interface is no longer needed. Replaced with getActiveLessonsForLearner(user,org) * @param learner a User that identifies the learner. * @return a List with all active lessons in it. */ @@ -277,5 +276,29 @@ } }); } + + /** + * @see org.lamsfoundation.lams.lesson.dao.ILessonDAO#getLessonsByOrgAndUserWithCompletedFlag(Integer, Integer, boolean) + */ + public List getLessonsByOrgAndUserWithCompletedFlag(final Integer userId, final Integer orgId, final boolean isStaff) + { + List dtos = null; + + HibernateTemplate hibernateTemplate = new HibernateTemplate(this.getSessionFactory()); + dtos = (List)hibernateTemplate.execute( + new HibernateCallback() { + public Object doInHibernate(Session session) throws HibernateException { + Query query = session.getNamedQuery( + isStaff ? "staffLessonsByOrgAndUserWithCompletedFlag" : "learnerLessonsByOrgAndUserWithCompletedFlag" + ); + query.setInteger("userId", userId.intValue()); + query.setInteger("orgId", orgId.intValue()); + List result = query.list(); + return result; + } + } + ); + return dtos; + } } Index: lams_learning/src/java/org/lamsfoundation/lams/learning/service/ICoreLearnerService.java =================================================================== diff -u -r23b24b8f4be8dee33e809a975277be4731736b16 -r89403052c08187d7d7c341b07b21529411e2cef6 --- lams_learning/src/java/org/lamsfoundation/lams/learning/service/ICoreLearnerService.java (.../ICoreLearnerService.java) (revision 23b24b8f4be8dee33e809a975277be4731736b16) +++ lams_learning/src/java/org/lamsfoundation/lams/learning/service/ICoreLearnerService.java (.../ICoreLearnerService.java) (revision 89403052c08187d7d7c341b07b21529411e2cef6) @@ -25,6 +25,7 @@ package org.lamsfoundation.lams.learning.service; import java.util.List; +import java.util.Map; import org.lamsfoundation.lams.learningdesign.Activity; import org.lamsfoundation.lams.learningdesign.BranchingActivity; @@ -38,6 +39,7 @@ import org.lamsfoundation.lams.usermanagement.User; import org.lamsfoundation.lams.usermanagement.service.IUserManagementService; import org.lamsfoundation.lams.util.MessageService; +import org.lamsfoundation.lams.web.IndexLessonBean; /** * * All Learner service methods that are available within the core. These methods may require @@ -279,4 +281,12 @@ */ public SequenceActivity selectBranch(Lesson lesson, BranchingActivity branchingActivity, Integer learnerId, Long branchId) throws LearnerServiceException; + /** + * Returns map of lessons in an organisation for a particular learner or staff user. + * @param userId user's id + * @param orgId org's id + * @param isStaff return lessons where user is staff, or where user is learner + * @return map of lesson beans used in the index page + */ + public Map getLessonsByOrgAndUserWithCompletedFlag(Integer userId, Integer orgId, boolean isStaff); } Index: lams_learning/src/java/org/lamsfoundation/lams/learning/service/LearnerService.java =================================================================== diff -u -re0cea12ca5bf8260f519e9b8676bbc45966b628c -r89403052c08187d7d7c341b07b21529411e2cef6 --- lams_learning/src/java/org/lamsfoundation/lams/learning/service/LearnerService.java (.../LearnerService.java) (revision e0cea12ca5bf8260f519e9b8676bbc45966b628c) +++ lams_learning/src/java/org/lamsfoundation/lams/learning/service/LearnerService.java (.../LearnerService.java) (revision 89403052c08187d7d7c341b07b21529411e2cef6) @@ -39,11 +39,11 @@ import org.lamsfoundation.lams.learning.progress.ProgressException; import org.lamsfoundation.lams.learning.web.util.ActivityMapping; import org.lamsfoundation.lams.learningdesign.Activity; +import org.lamsfoundation.lams.learningdesign.BranchActivityEntry; import org.lamsfoundation.lams.learningdesign.BranchCondition; import org.lamsfoundation.lams.learningdesign.BranchingActivity; import org.lamsfoundation.lams.learningdesign.GateActivity; import org.lamsfoundation.lams.learningdesign.Group; -import org.lamsfoundation.lams.learningdesign.BranchActivityEntry; import org.lamsfoundation.lams.learningdesign.Grouping; import org.lamsfoundation.lams.learningdesign.GroupingActivity; import org.lamsfoundation.lams.learningdesign.SequenceActivity; @@ -68,6 +68,7 @@ import org.lamsfoundation.lams.usermanagement.User; import org.lamsfoundation.lams.usermanagement.service.IUserManagementService; import org.lamsfoundation.lams.util.MessageService; +import org.lamsfoundation.lams.web.IndexLessonBean; /** * This class is a facade over the Learning middle tier. * @author chris, Jacky Fang @@ -939,5 +940,30 @@ public void setProgressEngine(ProgressEngine progressEngine) { this.progressEngine = progressEngine; } + + + public Map getLessonsByOrgAndUserWithCompletedFlag(Integer userId, Integer orgId, boolean isStaff) + { + HashMap map = new HashMap(); + List list = this.lessonDAO.getLessonsByOrgAndUserWithCompletedFlag(userId, orgId, isStaff); + if (list != null) { + Iterator iterator = list.iterator(); + while (iterator.hasNext()) { + Object[] tuple = (Object[])iterator.next(); + Long lessonId = (Long)tuple[0]; + String lessonName = (String)tuple[1]; + String lessonDescription = (String)tuple[2]; + Integer lessonState = (Integer)tuple[3]; + Boolean lessonCompleted = (Boolean)tuple[4]; + IndexLessonBean bean = new IndexLessonBean( + lessonId, lessonName, lessonDescription, lessonState, ( + lessonCompleted == null ? false : lessonCompleted.booleanValue() + ) + ); + map.put(new Long(lessonId), bean); + } + } + return map; + } }