Index: lams_common/src/java/org/lamsfoundation/lams/qb/dao/IQbDAO.java =================================================================== diff -u -r394ff1771926ee7d32cc7eca41bf83fc964c2ebe -r0d764ec63c5013349de051818fbc82a970d6a803 --- lams_common/src/java/org/lamsfoundation/lams/qb/dao/IQbDAO.java (.../IQbDAO.java) (revision 394ff1771926ee7d32cc7eca41bf83fc964c2ebe) +++ lams_common/src/java/org/lamsfoundation/lams/qb/dao/IQbDAO.java (.../IQbDAO.java) (revision 0d764ec63c5013349de051818fbc82a970d6a803) @@ -8,19 +8,19 @@ import org.lamsfoundation.lams.qb.model.QbQuestion; public interface IQbDAO extends IBaseDAO { - + /** * @param qbQuestionUid * @return QbQuestion object with the specified uid */ QbQuestion getQbQuestionByUid(Long qbQuestionUid); - + /** * @param questionId * @return questions sharing the same questionId */ List getQbQuestionsByQuestionId(Integer questionId); - + // finds next question ID for Question Bank question int getMaxQuestionId(); @@ -31,11 +31,17 @@ List getQuestionVersions(long qbQuestionUid); - Map getAnswerStats(long qbQuestionUid); - - List getPagedQbQuestions(Integer questionType, int page, int size, String sortBy, - String sortOrder, String searchString); - + Map getAnswerStatsForQbQuestion(long qbQuestionUid); + + Map geAnswerStatsForQbToolQuestion(long qbToolQuestionUid); + + Map getAnswerStatsForActivity(long activityId); + + Map getBurningQuestions(long qbQuestionUid); + + List getPagedQbQuestions(Integer questionType, int page, int size, String sortBy, String sortOrder, + String searchString); + int getCountQbQuestions(Integer questionType, String searchString); } \ No newline at end of file Index: lams_common/src/java/org/lamsfoundation/lams/qb/dao/hibernate/QbDAO.java =================================================================== diff -u -r394ff1771926ee7d32cc7eca41bf83fc964c2ebe -r0d764ec63c5013349de051818fbc82a970d6a803 --- lams_common/src/java/org/lamsfoundation/lams/qb/dao/hibernate/QbDAO.java (.../QbDAO.java) (revision 394ff1771926ee7d32cc7eca41bf83fc964c2ebe) +++ lams_common/src/java/org/lamsfoundation/lams/qb/dao/hibernate/QbDAO.java (.../QbDAO.java) (revision 0d764ec63c5013349de051818fbc82a970d6a803) @@ -1,6 +1,7 @@ package org.lamsfoundation.lams.qb.dao.hibernate; import java.util.HashMap; +import java.util.LinkedHashMap; import java.util.List; import java.util.Map; @@ -19,9 +20,15 @@ + "WHERE a.toolContentId = q.toolContentId AND a.learningDesign.lessons IS NOT EMPTY AND q.qbQuestion.uid = :qbQuestionUid"; private static final String FIND_QUESTION_VERSIONS = "SELECT q FROM QbQuestion AS q, QbQuestion AS r " + "WHERE q.questionId = r.questionId AND q.uid <> r.uid AND r.uid = :qbQuestionUid"; - private static final String FIND_ANSWER_STATS = "SELECT a.qbOption.uid, COUNT(a.uid) FROM QbToolAnswer AS a " + private static final String FIND_ANSWER_STATS_BY_QB_QUESTION = "SELECT a.qbOption.uid, COUNT(a.uid) FROM QbToolAnswer AS a " + "WHERE a.qbOption.qbQuestion.uid = :qbQuestionUid GROUP BY a.qbOption.uid"; - + private static final String FIND_ANSWER_STATS_BY_TOOL_QUESTION = "SELECT a.qbOption.uid, COUNT(a.uid) FROM QbToolAnswer AS a " + + "WHERE a.qbToolQuestion.uid = :qbToolQuestionUid GROUP BY a.qbOption.uid"; + private static final String FIND_ANSWER_STATS_BY_ACTIVITY = "SELECT a.qbOption.uid, COUNT(a.uid) FROM QbToolAnswer AS a, " + + " ToolActivity AS act WHERE a.qbToolQuestion.toolContentId = act.toolContentId AND act.activityId = :activityId GROUP BY a.qbOption.uid"; + private static final String FIND_BURNING_QUESTIONS = "SELECT bl.burningQuestion.question, COUNT(bl.uid) FROM BurningQuestionLike AS bl " + + "WHERE bl.burningQuestion.scratchieItem.qbQuestion.uid = :qbQuestionUid GROUP BY bl.burningQuestion.question ORDER BY COUNT(bl.uid) DESC"; + @Override public QbQuestion getQbQuestionByUid(Long qbQuestionUid) { return (QbQuestion) this.find(QbQuestion.class, qbQuestionUid); @@ -30,10 +37,9 @@ @Override public List getQbQuestionsByQuestionId(Integer questionId) { final String FIND_QUESTIONS_BY_QUESTION_ID = "FROM " + QbQuestion.class.getName() - + " WHERE questionId = :questionId AND local = 0 ORDER BY version ASC"; - - Query q = getSession().createQuery(FIND_QUESTIONS_BY_QUESTION_ID, - QbQuestion.class); + + " WHERE questionId = :questionId AND local = 0 ORDER BY version ASC"; + + Query q = getSession().createQuery(FIND_QUESTIONS_BY_QUESTION_ID, QbQuestion.class); q.setParameter("questionId", questionId); return q.list(); } @@ -69,52 +75,48 @@ @Override @SuppressWarnings("unchecked") - public Map getAnswerStats(long qbQuestionUid) { - List result = this.getSession().createQuery(FIND_ANSWER_STATS) + public Map getAnswerStatsForQbQuestion(long qbQuestionUid) { + List result = this.getSession().createQuery(FIND_ANSWER_STATS_BY_QB_QUESTION) .setParameter("qbQuestionUid", qbQuestionUid).list(); Map map = new HashMap<>(result.size()); for (Object[] answerStat : result) { map.put((Long) answerStat[0], (Long) answerStat[1]); } return map; } - + @SuppressWarnings("unchecked") @Override public List getPagedQbQuestions(Integer questionType, int page, int size, String sortBy, String sortOrder, String searchString) { //we sort of strip out HTML tags from the search by using REGEXP_REPLACE which skips all the content between < > - final String SELECT_QUESTIONS = "SELECT DISTINCT question.* " - + " FROM lams_qb_question question " - + " LEFT OUTER JOIN lams_qb_option qboption " - + " ON qboption.qb_question_uid = question.uid " + final String SELECT_QUESTIONS = "SELECT DISTINCT question.* " + " FROM lams_qb_question question " + + " LEFT OUTER JOIN lams_qb_option qboption " + " ON qboption.qb_question_uid = question.uid " + " LEFT JOIN ("//help finding questions with the max available version - + " SELECT biggerQuestion.* FROM lams_qb_question biggerQuestion " + + " SELECT biggerQuestion.* FROM lams_qb_question biggerQuestion " + " LEFT OUTER JOIN lams_qb_option qboption1 " + " ON qboption1.qb_question_uid = biggerQuestion.uid " - + " WHERE biggerQuestion.type = :questionType " - + " AND biggerQuestion.local = 0 " + + " WHERE biggerQuestion.type = :questionType " + " AND biggerQuestion.local = 0 " + " AND (REGEXP_REPLACE(biggerQuestion.description, '<[^>]*>+', '') LIKE CONCAT('%', :searchString, '%')" + " OR biggerQuestion.name LIKE CONCAT('%', :searchString, '%') " + " OR REGEXP_REPLACE(qboption1.name, '<[^>]*>+', '') LIKE CONCAT('%', :searchString, '%')) " + ") AS biggerQuestion ON question.question_id = biggerQuestion.question_id AND question.version < biggerQuestion.version " - + " WHERE biggerQuestion.version is NULL " - + " AND question.type = :questionType " + + " WHERE biggerQuestion.version is NULL " + " AND question.type = :questionType " + " AND question.local = 0 " + " AND (REGEXP_REPLACE(question.description, '<[^>]*>+', '') LIKE CONCAT('%', :searchString, '%')" + " OR question.name LIKE CONCAT('%', :searchString, '%') " + " OR REGEXP_REPLACE(qboption.name, '<[^>]*>+', '') LIKE CONCAT('%', :searchString, '%')) "; final String ORDER_BY_NAME = "ORDER BY question.name "; final String ORDER_BY_SMTH_ELSE = "ORDER BY question.question_id "; - + //TODO check the following query with real data. and see maybe it's better than the current (it's unlikely though) [https://stackoverflow.com/a/28090544/10331386 and https://stackoverflow.com/a/612268/10331386] // SELECT t1.* // FROM lams_qb_question t1 // INNER JOIN // ( // SELECT `question_id`, MAX(version) AS max_version // FROM lams_qb_question as t3 -// LEFT OUTER JOIN lams_qb_option qboption +// LEFT OUTER JOIN lams_qb_option qboption // ON qboption.qb_question_uid = t3.uid // WHERE REGEXP_REPLACE(qboption.name, '<[^>]*>+', '') LIKE CONCAT('%', :searchString, '%') // GROUP BY `question_id` @@ -128,7 +130,7 @@ bldr.append(ORDER_BY_NAME); } bldr.append(sortOrder); - + NativeQuery query = getSession().createNativeQuery(bldr.toString()); query.setParameter("questionType", questionType); // support for custom search from a toolbar @@ -144,22 +146,18 @@ @Override public int getCountQbQuestions(Integer questionType, String searchString) { - final String SELECT_QUESTIONS = "SELECT COUNT(DISTINCT question.uid) " - + " FROM lams_qb_question question " - + " LEFT OUTER JOIN lams_qb_option qboption " - + " ON qboption.qb_question_uid = question.uid " + final String SELECT_QUESTIONS = "SELECT COUNT(DISTINCT question.uid) " + " FROM lams_qb_question question " + + " LEFT OUTER JOIN lams_qb_option qboption " + " ON qboption.qb_question_uid = question.uid " + " LEFT JOIN ("//help finding questions with the max available version - + " SELECT biggerQuestion.* FROM lams_qb_question biggerQuestion " + + " SELECT biggerQuestion.* FROM lams_qb_question biggerQuestion " + " LEFT OUTER JOIN lams_qb_option qboption1 " + " ON qboption1.qb_question_uid = biggerQuestion.uid " - + " WHERE biggerQuestion.type = :questionType " - + " AND biggerQuestion.local = 0 " + + " WHERE biggerQuestion.type = :questionType " + " AND biggerQuestion.local = 0 " + " AND (REGEXP_REPLACE(biggerQuestion.description, '<[^>]*>+', '') LIKE CONCAT('%', :searchString, '%')" + " OR biggerQuestion.name LIKE CONCAT('%', :searchString, '%') " + " OR REGEXP_REPLACE(qboption1.name, '<[^>]*>+', '') LIKE CONCAT('%', :searchString, '%')) " + ") AS biggerQuestion ON question.question_id = biggerQuestion.question_id AND question.version < biggerQuestion.version " - + " WHERE biggerQuestion.version is NULL " - + " AND question.type = :questionType " + + " WHERE biggerQuestion.version is NULL " + " AND question.type = :questionType " + " AND question.local = 0 " + " AND (REGEXP_REPLACE(question.description, '<[^>]*>+', '') LIKE CONCAT('%', :searchString, '%')" + " OR question.name LIKE CONCAT('%', :searchString, '%') " @@ -173,4 +171,40 @@ int result = ((Number) query.uniqueResult()).intValue(); return result; } + + @Override + @SuppressWarnings("unchecked") + public Map geAnswerStatsForQbToolQuestion(long qbToolQuestionUid) { + List result = this.getSession().createQuery(FIND_ANSWER_STATS_BY_TOOL_QUESTION) + .setParameter("qbToolQuestionUid", qbToolQuestionUid).list(); + Map map = new HashMap<>(result.size()); + for (Object[] answerStat : result) { + map.put((Long) answerStat[0], (Long) answerStat[1]); + } + return map; + } + + @Override + @SuppressWarnings("unchecked") + public Map getAnswerStatsForActivity(long activityId) { + List result = this.getSession().createQuery(FIND_ANSWER_STATS_BY_ACTIVITY) + .setParameter("activityId", activityId).list(); + Map map = new HashMap<>(result.size()); + for (Object[] answerStat : result) { + map.put((Long) answerStat[0], (Long) answerStat[1]); + } + return map; + } + + @Override + @SuppressWarnings("unchecked") + public Map getBurningQuestions(long qbQuestionUid) { + List result = this.getSession().createQuery(FIND_BURNING_QUESTIONS) + .setParameter("qbQuestionUid", qbQuestionUid).list(); + Map map = new LinkedHashMap<>(result.size()); + for (Object[] burningQuestion : result) { + map.put((String) burningQuestion[0], (Long) burningQuestion[1]); + } + return map; + } } \ No newline at end of file Index: lams_common/src/java/org/lamsfoundation/lams/qb/service/QbService.java =================================================================== diff -u -r394ff1771926ee7d32cc7eca41bf83fc964c2ebe -r0d764ec63c5013349de051818fbc82a970d6a803 --- lams_common/src/java/org/lamsfoundation/lams/qb/service/QbService.java (.../QbService.java) (revision 394ff1771926ee7d32cc7eca41bf83fc964c2ebe) +++ lams_common/src/java/org/lamsfoundation/lams/qb/service/QbService.java (.../QbService.java) (revision 0d764ec63c5013349de051818fbc82a970d6a803) @@ -1,13 +1,17 @@ package org.lamsfoundation.lams.qb.service; import java.util.HashMap; +import java.util.LinkedList; import java.util.List; import java.util.Map; +import org.lamsfoundation.lams.learningdesign.ToolActivity; import org.lamsfoundation.lams.qb.dao.IQbDAO; import org.lamsfoundation.lams.qb.dto.QbStatsDTO; +import org.lamsfoundation.lams.qb.dto.QbStatsDTO.QbStatsActivityDTO; import org.lamsfoundation.lams.qb.model.QbOption; import org.lamsfoundation.lams.qb.model.QbQuestion; +import org.lamsfoundation.lams.util.WebUtil; import com.fasterxml.jackson.databind.node.ArrayNode; import com.fasterxml.jackson.databind.node.JsonNodeFactory; @@ -51,10 +55,38 @@ @Override public QbStatsDTO getStats(long qbQuestionUid) { QbStatsDTO stats = new QbStatsDTO(); - stats.setQuestion((QbQuestion) qbDAO.find(QbQuestion.class, qbQuestionUid)); - stats.setActivities(qbDAO.getQuestionActivities(qbQuestionUid)); + QbQuestion qbQuestion = (QbQuestion) qbDAO.find(QbQuestion.class, qbQuestionUid); + List qbOptions = qbQuestion.getQbOptions(); + stats.setQuestion(qbQuestion); + Map burningQuestions = qbDAO.getBurningQuestions(qbQuestionUid); + stats.setBurningQuestions(burningQuestions); + + List activities = qbDAO.getQuestionActivities(qbQuestionUid); + List activityDTOs = new LinkedList<>(); + // calculate correct answer average for each activity + for (ToolActivity activity : activities) { + QbStatsActivityDTO activityDTO = new QbStatsActivityDTO(); + activityDTO.setActivity(activity); + Map activityAnswersRaw = qbDAO.getAnswerStatsForActivity(activity.getActivityId()); + double total = 0; + long correctCount = 0; + for (QbOption option : qbOptions) { + Long answerCount = activityAnswersRaw.get(option.getUid()); + if (answerCount == null) { + answerCount = 0L; + } + total += answerCount; + if (option.isCorrect()) { + correctCount = answerCount; + } + } + activityDTO.setAverage(total == 0 ? null : Long.valueOf(Math.round(correctCount / total * 100)).intValue()); + activityDTOs.add(activityDTO); + } + stats.setActivities(activityDTOs); + stats.setVersions(qbDAO.getQuestionVersions(qbQuestionUid)); - Map answersRaw = qbDAO.getAnswerStats(qbQuestionUid); + Map answersRaw = qbDAO.getAnswerStatsForQbQuestion(qbQuestionUid); stats.setAnswersRaw(answersRaw); ArrayNode answersJSON = JsonNodeFactory.instance.arrayNode(); @@ -63,16 +95,17 @@ total += answerCount; } Map answerPercent = new HashMap<>(); - List qbOptions = stats.getQuestion().getQbOptions(); for (int answerIndex = 0; answerIndex < qbOptions.size(); answerIndex++) { QbOption option = qbOptions.get(answerIndex); Long answerCount = answersRaw.get(option.getUid()); int value = answerCount == null ? 0 : Long.valueOf(Math.round(answerCount / total * 100)).intValue(); answerPercent.put(option.getUid(), value); ObjectNode answerJSON = JsonNodeFactory.instance.objectNode(); - answerJSON.put("name", (answerIndex + 1) + ". " - + (option.getName().length() > 20 ? option.getName().substring(0, 20) + "..." : option.getName())); + String name = (answerIndex + 1) + ". " + + (option.getName().length() > 30 ? option.getName().substring(0, 30) + "..." : option.getName()); + name = WebUtil.removeHTMLtags(name); + answerJSON.put("name", name); answerJSON.put("value", value); answersJSON.add(answerJSON); }