Index: lams_common/src/java/org/lamsfoundation/lams/qb/dao/IQbDAO.java =================================================================== diff -u -r03d284a4bdb2a2f2d6f57d0810e465f437c25155 -r8bdf2c81e6073c5bde35413e62767e4804b638d7 --- lams_common/src/java/org/lamsfoundation/lams/qb/dao/IQbDAO.java (.../IQbDAO.java) (revision 03d284a4bdb2a2f2d6f57d0810e465f437c25155) +++ lams_common/src/java/org/lamsfoundation/lams/qb/dao/IQbDAO.java (.../IQbDAO.java) (revision 8bdf2c81e6073c5bde35413e62767e4804b638d7) @@ -8,6 +8,7 @@ import org.lamsfoundation.lams.dao.IBaseDAO; import org.lamsfoundation.lams.learningdesign.ToolActivity; +import org.lamsfoundation.lams.qb.dto.QbAnswersForOptionDTO; import org.lamsfoundation.lams.qb.model.QbCollection; import org.lamsfoundation.lams.qb.model.QbQuestion; import org.lamsfoundation.lams.tool.ToolContent; @@ -50,8 +51,10 @@ Map getAnswerStatsForQuestion(long qbQuestionUid); - Map getAnswersForActivity(long activityId, long qbQuestionUid); + Map getAnswersForActivityAndQuestion(long activityId, long qbQuestionUid); + List getAnswerCountForOptions(long toolContentId); + Map getBurningQuestions(long qbQuestionUid); List getPagedQuestions(String questionTypes, String collectionUids, int page, int size, String sortBy, Index: lams_common/src/java/org/lamsfoundation/lams/qb/dao/hibernate/QbDAO.java =================================================================== diff -u -r03d284a4bdb2a2f2d6f57d0810e465f437c25155 -r8bdf2c81e6073c5bde35413e62767e4804b638d7 --- lams_common/src/java/org/lamsfoundation/lams/qb/dao/hibernate/QbDAO.java (.../QbDAO.java) (revision 03d284a4bdb2a2f2d6f57d0810e465f437c25155) +++ lams_common/src/java/org/lamsfoundation/lams/qb/dao/hibernate/QbDAO.java (.../QbDAO.java) (revision 8bdf2c81e6073c5bde35413e62767e4804b638d7) @@ -5,8 +5,10 @@ import java.util.HashMap; import java.util.HashSet; import java.util.LinkedHashMap; +import java.util.LinkedList; import java.util.List; import java.util.Map; +import java.util.Map.Entry; import java.util.Set; import javax.persistence.Query; @@ -18,6 +20,7 @@ import org.lamsfoundation.lams.dao.hibernate.LAMSBaseDAO; import org.lamsfoundation.lams.learningdesign.ToolActivity; import org.lamsfoundation.lams.qb.dao.IQbDAO; +import org.lamsfoundation.lams.qb.dto.QbAnswersForOptionDTO; import org.lamsfoundation.lams.qb.model.QbCollection; import org.lamsfoundation.lams.qb.model.QbQuestion; import org.lamsfoundation.lams.qb.model.QbToolQuestion; @@ -51,7 +54,7 @@ + "LEFT JOIN tl_laasse10_option_answer AS aa ON a.answer_uid = aa.question_result_uid AND aa.answer_boolean = 1 " + "WHERE tq.qb_question_uid = :qbQuestionUid GROUP BY opt HAVING opt IS NOT NULL"; - private static final String FIND_ANSWERS_BY_ACTIVITY = "SELECT COALESCE(mcu.que_usr_id, su.user_id, au.user_id) AS user_id, " + private static final String FIND_ANSWERS_BY_ACTIVITY_AND_QUESTION = "SELECT COALESCE(mcu.que_usr_id, su.user_id, au.user_id) AS user_id, " + "IF(su.user_id IS NULL, COALESCE(a.qb_option_uid, aa.question_option_uid), IF(COUNT(a.qb_option_uid) > 1, -1, a.qb_option_uid)) AS opt " + "FROM lams_learning_activity AS act JOIN lams_qb_tool_question AS tq USING (tool_content_id) " + "JOIN lams_qb_tool_answer AS a USING (tool_question_uid) " @@ -67,6 +70,19 @@ + "WHERE act.activity_id = :activityId AND tq.qb_question_uid = :qbQuestionUid GROUP BY user_id " + "HAVING opt IS NOT NULL AND user_id IS NOT NULL"; + private static final String FIND_OPTION_ANSWER_COUNT_BY_QUESTION = "SELECT tq.qb_question_uid, o.uid AS qb_option_uid, o.max_mark = 1 AS is_correct, " + + "SUM(IF(aa.uid IS NULL AND sa.uid IS NULL, 0, 1)) AS chosen_count " + + "FROM lams_qb_tool_question AS tq JOIN lams_qb_option AS o USING (qb_question_uid) " + + "LEFT JOIN lams_qb_tool_answer AS a ON a.tool_question_uid = tq.tool_question_uid AND (a.qb_option_uid IS NULL OR a.qb_option_uid = o.uid) " + + "LEFT JOIN tl_laasse10_question_result AS aq ON a.answer_uid = aq.uid " + + "LEFT JOIN tl_laasse10_option_answer AS aa ON aa.question_result_uid = aq.uid AND aa.question_option_uid = o.uid AND aa.answer_boolean = 1 " + + "LEFT JOIN tl_laasse10_assessment_result AS ar ON aq.result_uid = ar.uid AND ar.latest = 1 " + + "LEFT JOIN tl_lascrt11_answer_log AS sa ON a.answer_uid = sa.uid AND a.answer_uid = " + + " (SELECT sa2.uid FROM tl_lascrt11_answer_log AS sa2 JOIN lams_qb_tool_answer AS a2 " + + " ON a2.answer_uid = sa2.uid AND a2.tool_question_uid = a.tool_question_uid AND sa2.session_id = sa.session_id " + + " ORDER BY sa2.access_date, sa2.uid LIMIT 1) " + + "WHERE tq.tool_content_id = :toolContentId GROUP BY o.uid ORDER BY tq.display_order, o.display_order"; + private static final String FIND_BURNING_QUESTIONS = "SELECT b.question, COUNT(bl.uid) FROM ScratchieBurningQuestion b LEFT OUTER JOIN " + "BurningQuestionLike AS bl ON bl.burningQuestion = b WHERE b.scratchieItem.qbQuestion.uid = :qbQuestionUid " + "GROUP BY b.question ORDER BY COUNT(bl.uid) DESC"; @@ -399,8 +415,8 @@ @Override @SuppressWarnings("unchecked") - public Map getAnswersForActivity(long activityId, long qbQuestionUid) { - List result = this.getSession().createSQLQuery(FIND_ANSWERS_BY_ACTIVITY) + public Map getAnswersForActivityAndQuestion(long activityId, long qbQuestionUid) { + List result = this.getSession().createSQLQuery(FIND_ANSWERS_BY_ACTIVITY_AND_QUESTION) .setParameter("activityId", activityId).setParameter("qbQuestionUid", qbQuestionUid).list(); Map map = new HashMap<>(result.size()); for (Object[] answerStat : result) { @@ -411,6 +427,57 @@ @Override @SuppressWarnings("unchecked") + public List getAnswerCountForOptions(long toolContentId) { + List result = this.getSession().createSQLQuery(FIND_OPTION_ANSWER_COUNT_BY_QUESTION) + .setParameter("toolContentId", toolContentId).list(); + List dtos = new LinkedList<>(); + Map> questionsToOptionsMap = new LinkedHashMap<>(); + // it contains all UID of correct answers + Set correctOptionUids = new HashSet<>(); + + // build a map of question ID -> option ID -> answer count + for (Object[] answerEntry : result) { + Long qbQuestionUid = ((Number) answerEntry[0]).longValue(); + Map answersForOptions = questionsToOptionsMap.get(qbQuestionUid); + if (answersForOptions == null) { + answersForOptions = new LinkedHashMap<>(); + questionsToOptionsMap.put(qbQuestionUid, answersForOptions); + } + Long qbOptionUid = ((Number) answerEntry[1]).longValue(); + answersForOptions.put(qbOptionUid, ((Number) answerEntry[3]).intValue()); + + boolean isCorrect = ((Number) answerEntry[2]).intValue() == 1; + if (isCorrect) { + correctOptionUids.add(qbOptionUid); + } + } + + // calculate answer percentage for question and all options + int displayOrder = 1; + for (Entry> questionToOptionEntry : questionsToOptionsMap.entrySet()) { + double totalAnswers = questionToOptionEntry.getValue().values().stream().mapToInt(Integer::intValue).sum(); + QbAnswersForOptionDTO dto = new QbAnswersForOptionDTO(questionToOptionEntry.getKey(), displayOrder++); + dtos.add(dto); + + for (Entry answersForOptionEntry : questionToOptionEntry.getValue().entrySet()) { + Long qbOptionUid = answersForOptionEntry.getKey(); + if (totalAnswers == 0) { + dto.getOptionAnswerPercent().put(qbOptionUid, -1); + continue; + } + + Long answerPercent = Math.round(answersForOptionEntry.getValue() / totalAnswers * 100); + dto.getOptionAnswerPercent().put(qbOptionUid, answerPercent.intValue()); + if (correctOptionUids.contains(qbOptionUid)) { + dto.setCorrectAnswerPercent(answerPercent.intValue()); + } + } + } + return dtos; + } + + @Override + @SuppressWarnings("unchecked") public Map getBurningQuestions(long qbQuestionUid) { List result = this.getSession().createQuery(FIND_BURNING_QUESTIONS) .setParameter("qbQuestionUid", qbQuestionUid).list(); Index: lams_common/src/java/org/lamsfoundation/lams/qb/service/QbService.java =================================================================== diff -u -r03d284a4bdb2a2f2d6f57d0810e465f437c25155 -r8bdf2c81e6073c5bde35413e62767e4804b638d7 --- lams_common/src/java/org/lamsfoundation/lams/qb/service/QbService.java (.../QbService.java) (revision 03d284a4bdb2a2f2d6f57d0810e465f437c25155) +++ lams_common/src/java/org/lamsfoundation/lams/qb/service/QbService.java (.../QbService.java) (revision 8bdf2c81e6073c5bde35413e62767e4804b638d7) @@ -40,6 +40,7 @@ import org.lamsfoundation.lams.qb.QbConstants; import org.lamsfoundation.lams.qb.QbUtils; import org.lamsfoundation.lams.qb.dao.IQbDAO; +import org.lamsfoundation.lams.qb.dto.QbAnswersForOptionDTO; import org.lamsfoundation.lams.qb.dto.QbStatsActivityDTO; import org.lamsfoundation.lams.qb.dto.QbStatsDTO; import org.lamsfoundation.lams.qb.form.QbQuestionForm; @@ -274,7 +275,8 @@ // if there is only 1 participant, there is no point in calculating question indexes if (participantCount >= Configuration.getAsInt(ConfigurationKeys.QB_STATS_MIN_PARTICIPANTS)) { // mapping of user ID -> option UID - Map activityAnswers = qbDAO.getAnswersForActivity(activity.getActivityId(), qbQuestionUid); + Map activityAnswers = qbDAO.getAnswersForActivityAndQuestion(activity.getActivityId(), + qbQuestionUid); // take only learners who finished (not only submitted) this activity userLessonGrades = userLessonGrades.stream() .filter(g -> activityAnswers.containsKey(g.getLearner().getUserId())).collect(Collectors.toList()); @@ -348,6 +350,11 @@ } @Override + public List getAnswerCountForOptions(long toolContentId) { + return qbDAO.getAnswerCountForOptions(toolContentId); + } + + @Override public QbCollection getCollectionByUid(Long collectionUid) { return qbDAO.find(QbCollection.class, collectionUid); }