Index: lams_build/lib/lams/lams.jar =================================================================== diff -u -r8fc48568c579f2b32041d489a8ebf02075847fa3 -r5e6303fa679ceff627f5a18a2b34db4e654486ed Binary files differ Index: lams_common/src/java/org/lamsfoundation/lams/gradebook/dao/IGradebookDAO.java =================================================================== diff -u -rd0b6f213cba1026b0c9fdbdaa5dd44a49eddd3aa -r5e6303fa679ceff627f5a18a2b34db4e654486ed --- lams_common/src/java/org/lamsfoundation/lams/gradebook/dao/IGradebookDAO.java (.../IGradebookDAO.java) (revision d0b6f213cba1026b0c9fdbdaa5dd44a49eddd3aa) +++ lams_common/src/java/org/lamsfoundation/lams/gradebook/dao/IGradebookDAO.java (.../IGradebookDAO.java) (revision 5e6303fa679ceff627f5a18a2b34db4e654486ed) @@ -53,7 +53,7 @@ */ List getGradebookUserActivitiesForActivity(Long activityID, List userIds); - Double getAverageMarkForLesson(Long lessonID); + Double getMedianMarkForLesson(Long lessonID); long getAverageDurationLesson(Long lessonID); Index: lams_common/src/java/org/lamsfoundation/lams/gradebook/dao/hibernate/GradebookDAO.java =================================================================== diff -u -rd0b6f213cba1026b0c9fdbdaa5dd44a49eddd3aa -r5e6303fa679ceff627f5a18a2b34db4e654486ed --- lams_common/src/java/org/lamsfoundation/lams/gradebook/dao/hibernate/GradebookDAO.java (.../GradebookDAO.java) (revision d0b6f213cba1026b0c9fdbdaa5dd44a49eddd3aa) +++ lams_common/src/java/org/lamsfoundation/lams/gradebook/dao/hibernate/GradebookDAO.java (.../GradebookDAO.java) (revision 5e6303fa679ceff627f5a18a2b34db4e654486ed) @@ -52,9 +52,6 @@ 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_COMPLETION_TIME = "select prog.finishDate, prog.startDate FROM LearnerProgress prog where " + "prog.lesson.lessonId=:lessonID"; @@ -149,8 +146,27 @@ @Override @SuppressWarnings("unchecked") - public Double getAverageMarkForLesson(Long lessonID) { - List result = getSession().createQuery(GET_AVERAGE_MARK_FOR_LESSON).setLong("lessonID", lessonID.longValue()) + public Double getMedianMarkForLesson(Long lessonID) { + + // Hacking SQL and using /*'*/:=/*'*/ instead of := which brakes HQL (https://hibernate.onjira.com/browse/HHH-2697) + // After moving to Hibernate higher than 4.1.3, it can be replaced by \\:= + final String GET_MEDIAN_MARK_FOR_LESSON = "SELECT avg(t1.mark) as medianVal FROM (" + + " SELECT @rownum/*'*/:=/*'*/@rownum+1 as `rowNumber`, gles.mark" + + " FROM lams_gradebook_user_lesson gles, (SELECT @rownum/*'*/:=/*'*/0) r" + + " WHERE 1" + + " AND gles.lesson_id=:lessonID" + + " ORDER BY gles.mark" + + " ) as t1, " + + " (" + + " SELECT count(*) as totalRows" + + " FROM lams_gradebook_user_lesson gles" + + " WHERE 1" + + " AND gles.lesson_id=:lessonID" + + " ) as t2" + + " WHERE 1" + + " AND t1.rowNumber in ( floor((totalRows+1)/2), floor((totalRows+2)/2) )"; + + List result = getSession().createSQLQuery(GET_MEDIAN_MARK_FOR_LESSON).setLong("lessonID", lessonID.longValue()) .list(); if (result != null) { Index: lams_gradebook/conf/language/lams/ApplicationResources_en_AU.properties =================================================================== diff -u -rd0b6f213cba1026b0c9fdbdaa5dd44a49eddd3aa -r5e6303fa679ceff627f5a18a2b34db4e654486ed --- lams_gradebook/conf/language/lams/ApplicationResources_en_AU.properties (.../ApplicationResources_en_AU.properties) (revision d0b6f213cba1026b0c9fdbdaa5dd44a49eddd3aa) +++ lams_gradebook/conf/language/lams/ApplicationResources_en_AU.properties (.../ApplicationResources_en_AU.properties) (revision 5e6303fa679ceff627f5a18a2b34db4e654486ed) @@ -1,9 +1,6 @@ appName = gradebook #language code: en #locale code: AU - - - #=================== labels for Gradebook =================# heading.general.error =Error @@ -62,7 +59,7 @@ gradebook.export.activity =Activity gradebook.export.activities =Activities gradebook.export.outputs =Outputs -gradebook.export.average.lesson.mark =Average lesson mark: +gradebook.export.average.lesson.mark =Median lesson mark: gradebook.export.average.lesson.time.taken =Average time taken for lesson: gradebook.export.total.marks.for.lesson =Total Marks for Lesson gradebook.export.total.mark =Total Mark Index: lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/dto/GBActivityGridRowDTO.java =================================================================== diff -u -rd0b6f213cba1026b0c9fdbdaa5dd44a49eddd3aa -r5e6303fa679ceff627f5a18a2b34db4e654486ed --- lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/dto/GBActivityGridRowDTO.java (.../GBActivityGridRowDTO.java) (revision d0b6f213cba1026b0c9fdbdaa5dd44a49eddd3aa) +++ lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/dto/GBActivityGridRowDTO.java (.../GBActivityGridRowDTO.java) (revision 5e6303fa679ceff627f5a18a2b34db4e654486ed) @@ -120,15 +120,15 @@ ret.add((averageTimeTaken != null) ? convertTimeToString(averageTimeTaken) : CELL_EMPTY); ret.add(competences); - ret.add(averageMark != null ? GradebookUtil.niceFormatting(averageMark) : CELL_EMPTY); + ret.add(medianMark != null ? GradebookUtil.niceFormatting(medianMark) : CELL_EMPTY); } else if (view == GBGridView.LRN_ACTIVITY) { ret.add(rowName); ret.add(status); ret.add(feedback); ret.add((averageTimeTaken != null) ? convertTimeToString(averageTimeTaken) : CELL_EMPTY); ret.add((timeTaken != null) ? convertTimeToString(timeTaken) : CELL_EMPTY); - ret.add(averageMark != null ? GradebookUtil.niceFormatting(averageMark) : CELL_EMPTY); + ret.add(medianMark != null ? GradebookUtil.niceFormatting(medianMark) : CELL_EMPTY); ret.add(mark != null ? GradebookUtil.niceFormatting(mark) : CELL_EMPTY); } Index: lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/dto/GBLessonGridRowDTO.java =================================================================== diff -u -rd0b6f213cba1026b0c9fdbdaa5dd44a49eddd3aa -r5e6303fa679ceff627f5a18a2b34db4e654486ed --- lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/dto/GBLessonGridRowDTO.java (.../GBLessonGridRowDTO.java) (revision d0b6f213cba1026b0c9fdbdaa5dd44a49eddd3aa) +++ lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/dto/GBLessonGridRowDTO.java (.../GBLessonGridRowDTO.java) (revision 5e6303fa679ceff627f5a18a2b34db4e654486ed) @@ -66,7 +66,7 @@ ret.add(startDate); ret.add((averageTimeTaken != null && averageTimeTaken != 0) ? convertTimeToString(averageTimeTaken) : CELL_EMPTY); - ret.add((averageMark != null) ? GradebookUtil.niceFormatting(averageMark) : CELL_EMPTY); + ret.add((medianMark != null) ? GradebookUtil.niceFormatting(medianMark) : CELL_EMPTY); } else if ((view == GBGridView.LRN_COURSE) || (view == GBGridView.MON_USER)) { if (gradebookLearnerURL != null && gradebookLearnerURL.length() != 0) { @@ -83,7 +83,7 @@ ret.add((averageTimeTaken != null && averageTimeTaken != 0) ? toItalic(convertTimeToString(averageTimeTaken)) : CELL_EMPTY); ret.add((timeTaken != null) ? convertTimeToString(timeTaken) : CELL_EMPTY); - ret.add((averageMark != null) ? toItalic(GradebookUtil.niceFormatting(averageMark)) : CELL_EMPTY); + ret.add((medianMark != null) ? toItalic(GradebookUtil.niceFormatting(medianMark)) : CELL_EMPTY); ret.add((mark != null) ? GradebookUtil.niceFormatting(mark) : CELL_EMPTY); //plain lesson list case Index: lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/dto/GradebookGridRowDTO.java =================================================================== diff -u -rd0b6f213cba1026b0c9fdbdaa5dd44a49eddd3aa -r5e6303fa679ceff627f5a18a2b34db4e654486ed --- lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/dto/GradebookGridRowDTO.java (.../GradebookGridRowDTO.java) (revision d0b6f213cba1026b0c9fdbdaa5dd44a49eddd3aa) +++ lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/dto/GradebookGridRowDTO.java (.../GradebookGridRowDTO.java) (revision 5e6303fa679ceff627f5a18a2b34db4e654486ed) @@ -56,7 +56,7 @@ protected Double mark; // Average mark for the corresponding task - protected Double averageMark; + protected Double medianMark; // Number of marks available where applicable protected Long marksAvailable; @@ -138,8 +138,8 @@ } protected String averageMarkToString() { - if (averageMark != null) { - String avgStr = new DecimalFormat("##0.00").format(averageMark); + if (medianMark != null) { + String avgStr = new DecimalFormat("##0.00").format(medianMark); if (marksAvailable != null) { return avgStr + "/" + marksAvailable.toString(); } else { @@ -210,12 +210,12 @@ this.averageTimeTaken = averageTimeTaken; } - public Double getAverageMark() { - return averageMark; + public Double getMedianMark() { + return medianMark; } - public void setAverageMark(Double averageMark) { - this.averageMark = averageMark; + public void setMedianMark(Double medianMark) { + this.medianMark = medianMark; } public Long getMarksAvailable() { Index: lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/dto/comparators/GBAverageMarkComparator.java =================================================================== diff -u -rd0b6f213cba1026b0c9fdbdaa5dd44a49eddd3aa -r5e6303fa679ceff627f5a18a2b34db4e654486ed --- lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/dto/comparators/GBAverageMarkComparator.java (.../GBAverageMarkComparator.java) (revision d0b6f213cba1026b0c9fdbdaa5dd44a49eddd3aa) +++ lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/dto/comparators/GBAverageMarkComparator.java (.../GBAverageMarkComparator.java) (revision 5e6303fa679ceff627f5a18a2b34db4e654486ed) @@ -35,8 +35,8 @@ if (gradebookGridRow instanceof GradebookGridRowDTO && anotherGradebookGridRow instanceof GradebookGridRowDTO) { - Double mark1 = ((GradebookGridRowDTO) gradebookGridRow).getAverageMark(); - Double mark2 = ((GradebookGridRowDTO) anotherGradebookGridRow).getAverageMark(); + Double mark1 = ((GradebookGridRowDTO) gradebookGridRow).getMedianMark(); + Double mark2 = ((GradebookGridRowDTO) anotherGradebookGridRow).getMedianMark(); mark1 = (mark1 == null) ? 0.0 : mark1; mark2 = (mark2 == null) ? 0.0 : mark2; Index: lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/service/GradebookService.java =================================================================== diff -u -r822c99a38640cc4b77403c28a5fbff8e431e3fd6 -r5e6303fa679ceff627f5a18a2b34db4e654486ed --- lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/service/GradebookService.java (.../GradebookService.java) (revision 822c99a38640cc4b77403c28a5fbff8e431e3fd6) +++ lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/service/GradebookService.java (.../GradebookService.java) (revision 5e6303fa679ceff627f5a18a2b34db4e654486ed) @@ -155,7 +155,7 @@ activityDTO.setStatus(getActivityStatusStr(learnerProgress, activity)); // Setting averages - activityDTO.setAverageMark(gradebookDAO.getAverageMarkForActivity(activity.getActivityId())); + activityDTO.setMedianMark(gradebookDAO.getAverageMarkForActivity(activity.getActivityId())); activityDTO.setAverageTimeTaken(gradebookDAO.getAverageDurationForActivity(activity.getActivityId())); // Get the tool outputs for this user if there are any @@ -414,8 +414,8 @@ } @Override - public Double getAverageMarkForLesson(Long lessonID) { - return gradebookDAO.getAverageMarkForLesson(lessonID); + public Double getMedianMarkForLesson(Long lessonID) { + return gradebookDAO.getMedianMarkForLesson(lessonID); } @Override @@ -670,7 +670,7 @@ // Setting the averages for monitor view lessonRow.setAverageTimeTaken(gradebookDAO.getAverageDurationLesson(lesson.getLessonId())); - lessonRow.setAverageMark(gradebookDAO.getAverageMarkForLesson(lesson.getLessonId())); + lessonRow.setMedianMark(gradebookDAO.getMedianMarkForLesson(lesson.getLessonId())); // Set the gradebook monitor url String gbMonURL = Configuration.get(ConfigurationKeys.SERVER_URL) @@ -682,7 +682,7 @@ userId); lessonRow.setAverageTimeTaken(gradebookDAO.getAverageDurationLesson(lesson.getLessonId())); - lessonRow.setAverageMark(gradebookDAO.getAverageMarkForLesson(lesson.getLessonId())); + lessonRow.setMedianMark(gradebookDAO.getMedianMarkForLesson(lesson.getLessonId())); if (gbLesson != null) { lessonRow.setMark(gbLesson.getMark()); @@ -773,10 +773,10 @@ List rowList = new LinkedList(); // Adding the lesson average data to the summary - ExcelCell[] lessonAverageMark = new ExcelCell[2]; - lessonAverageMark[0] = new ExcelCell(getMessage("gradebook.export.average.lesson.mark"), true); - lessonAverageMark[1] = new ExcelCell(getAverageMarkForLesson(lesson.getLessonId()), false); - rowList.add(lessonAverageMark); + ExcelCell[] lessonMedianMark = new ExcelCell[2]; + lessonMedianMark[0] = new ExcelCell(getMessage("gradebook.export.average.lesson.mark"), true); + lessonMedianMark[1] = new ExcelCell(getMedianMarkForLesson(lesson.getLessonId()), false); + rowList.add(lessonMedianMark); ExcelCell[] lessonAverageTimeTaken = new ExcelCell[2]; lessonAverageTimeTaken[0] = new ExcelCell(getMessage("gradebook.export.average.lesson.time.taken"), true); @@ -807,7 +807,7 @@ activityDataRow[0] = new ExcelCell(activityRow.getRowName(), false); activityDataRow[1] = new ExcelCell(activityRow.getCompetences(), false); activityDataRow[2] = new ExcelCell(activityRow.getAverageTimeTakenSeconds(), false); - activityDataRow[3] = new ExcelCell(activityRow.getAverageMark(), false); + activityDataRow[3] = new ExcelCell(activityRow.getMedianMark(), false); rowList.add(activityDataRow); } rowList.add(GradebookService.EMPTY_ROW); @@ -1447,13 +1447,13 @@ // Setting averages for group activityDTO - .setAverageMark(gradebookDAO.getAverageMarkForGroupedActivity(activity.getActivityId(), groupId)); + .setMedianMark(gradebookDAO.getAverageMarkForGroupedActivity(activity.getActivityId(), groupId)); activityDTO.setAverageTimeTaken( gradebookDAO.getAverageDurationForGroupedActivity(activity.getActivityId(), groupId)); } else { // Setting averages for lesson - activityDTO.setAverageMark(gradebookDAO.getAverageMarkForActivity(activity.getActivityId())); + activityDTO.setMedianMark(gradebookDAO.getAverageMarkForActivity(activity.getActivityId())); activityDTO.setAverageTimeTaken(gradebookDAO.getAverageDurationForActivity(activity.getActivityId())); } Index: lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/service/IGradebookService.java =================================================================== diff -u -r822c99a38640cc4b77403c28a5fbff8e431e3fd6 -r5e6303fa679ceff627f5a18a2b34db4e654486ed --- lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/service/IGradebookService.java (.../IGradebookService.java) (revision 822c99a38640cc4b77403c28a5fbff8e431e3fd6) +++ lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/service/IGradebookService.java (.../IGradebookService.java) (revision 5e6303fa679ceff627f5a18a2b34db4e654486ed) @@ -240,7 +240,7 @@ * @param lessonID * @return */ - Double getAverageMarkForLesson(Long lessonID); + Double getMedianMarkForLesson(Long lessonID); /** * Method for updating an activity mark that tools can call Index: lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/web/action/GradebookAction.java =================================================================== diff -u -rd0b6f213cba1026b0c9fdbdaa5dd44a49eddd3aa -r5e6303fa679ceff627f5a18a2b34db4e654486ed --- lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/web/action/GradebookAction.java (.../GradebookAction.java) (revision d0b6f213cba1026b0c9fdbdaa5dd44a49eddd3aa) +++ lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/web/action/GradebookAction.java (.../GradebookAction.java) (revision 5e6303fa679ceff627f5a18a2b34db4e654486ed) @@ -492,7 +492,7 @@ } /** - * Gets the average mark for lesson and writes the result in the response + * Gets the median mark for lesson and writes the result in the response * * @param mapping * @param form @@ -501,20 +501,19 @@ * @return * @throws Exception */ - public ActionForward getLessonMarkAverage(ActionMapping mapping, ActionForm form, HttpServletRequest request, + public ActionForward getMedianMarkForLesson(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception { Long lessonID = WebUtil.readLongParam(request, AttributeNames.PARAM_LESSON_ID); if (!getSecurityService().isLessonMonitor(lessonID, getUser().getUserID(), "get lesson mark average", false)) { response.sendError(HttpServletResponse.SC_FORBIDDEN, "User is not a monitor in the lesson"); return null; } - Lesson lesson = getLessonService().getLesson(lessonID); - Double averageMark = getGradebookService().getAverageMarkForLesson(lessonID); + Double medianMark = getGradebookService().getMedianMarkForLesson(lessonID); - if (averageMark != null) { + if (medianMark != null) { writeResponse(response, LamsDispatchAction.CONTENT_TYPE_TEXT_PLAIN, LamsDispatchAction.ENCODING_UTF8, - GradebookUtil.niceFormatting(averageMark)); + GradebookUtil.niceFormatting(medianMark)); } else { writeResponse(response, LamsDispatchAction.CONTENT_TYPE_TEXT_PLAIN, LamsDispatchAction.ENCODING_UTF8, GradebookConstants.CELL_EMPTY); Index: lams_gradebook/web/gradebookCourseMonitor.jsp =================================================================== diff -u -r1f6366dd975a5f5079cb57b2ca53e77873f5cb96 -r5e6303fa679ceff627f5a18a2b34db4e654486ed --- lams_gradebook/web/gradebookCourseMonitor.jsp (.../gradebookCourseMonitor.jsp) (revision 1f6366dd975a5f5079cb57b2ca53e77873f5cb96) +++ lams_gradebook/web/gradebookCourseMonitor.jsp (.../gradebookCourseMonitor.jsp) (revision 5e6303fa679ceff627f5a18a2b34db4e654486ed) @@ -111,7 +111,7 @@ // update the lesson average mark if (cellname == "mark") { // Update the average activity mark - $.get("/gradebook/gradebook.do", {dispatch:"getLessonMarkAverage", lessonID:lessonID}, function(xml) { + $.get("/gradebook/gradebook.do", {dispatch:"getMedianMarkForLesson", lessonID:lessonID}, function(xml) { if (xml!=null) { jQuery("#organisationGrid").setCell(row_id, "avgMark", xml, "", ""); }