Index: lams_common/src/java/org/lamsfoundation/lams/qb/dao/IQbDAO.java =================================================================== diff -u -r75e43eb7dffa6bf6eb2a11bb1e616c53bdbd76ed -r394ff1771926ee7d32cc7eca41bf83fc964c2ebe --- lams_common/src/java/org/lamsfoundation/lams/qb/dao/IQbDAO.java (.../IQbDAO.java) (revision 75e43eb7dffa6bf6eb2a11bb1e616c53bdbd76ed) +++ lams_common/src/java/org/lamsfoundation/lams/qb/dao/IQbDAO.java (.../IQbDAO.java) (revision 394ff1771926ee7d32cc7eca41bf83fc964c2ebe) @@ -8,6 +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(); @@ -19,4 +32,10 @@ List getQuestionVersions(long qbQuestionUid); Map getAnswerStats(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 -r75e43eb7dffa6bf6eb2a11bb1e616c53bdbd76ed -r394ff1771926ee7d32cc7eca41bf83fc964c2ebe --- lams_common/src/java/org/lamsfoundation/lams/qb/dao/hibernate/QbDAO.java (.../QbDAO.java) (revision 75e43eb7dffa6bf6eb2a11bb1e616c53bdbd76ed) +++ lams_common/src/java/org/lamsfoundation/lams/qb/dao/hibernate/QbDAO.java (.../QbDAO.java) (revision 394ff1771926ee7d32cc7eca41bf83fc964c2ebe) @@ -4,6 +4,8 @@ import java.util.List; import java.util.Map; +import org.hibernate.query.NativeQuery; +import org.hibernate.query.Query; import org.lamsfoundation.lams.dao.hibernate.LAMSBaseDAO; import org.lamsfoundation.lams.learningdesign.ToolActivity; import org.lamsfoundation.lams.qb.dao.IQbDAO; @@ -19,8 +21,24 @@ + "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 " + "WHERE a.qbOption.qbQuestion.uid = :qbQuestionUid GROUP BY a.qbOption.uid"; + + @Override + public QbQuestion getQbQuestionByUid(Long qbQuestionUid) { + return (QbQuestion) this.find(QbQuestion.class, qbQuestionUid); + } @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); + q.setParameter("questionId", questionId); + return q.list(); + } + + @Override public int getMaxQuestionId() { Object result = this.getSession().createQuery(FIND_MAX_QUESTION_ID).uniqueResult(); Integer max = (Integer) result; @@ -60,4 +78,99 @@ } 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 " + + " LEFT JOIN ("//help finding questions with the max available version + + " 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 " + + " 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 " + + " 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 +// ON qboption.qb_question_uid = t3.uid +// WHERE REGEXP_REPLACE(qboption.name, '<[^>]*>+', '') LIKE CONCAT('%', :searchString, '%') +// GROUP BY `question_id` +// ) t2 +// ON t1.`question_id` = t2.`question_id` AND t1.version = t2.max_version; + + StringBuilder bldr = new StringBuilder(SELECT_QUESTIONS); + if ("smth_else".equalsIgnoreCase(sortBy)) { + bldr.append(ORDER_BY_SMTH_ELSE); + } else { + 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 + searchString = searchString == null ? "" : searchString; + query.setParameter("searchString", searchString); + query.setFirstResult(page * size); + query.setMaxResults(size); + query.addEntity(QbQuestion.class); + List queryResults = (List) query.list(); + + return queryResults; + } + + @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 " + + " LEFT JOIN ("//help finding questions with the max available version + + " 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 " + + " 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 " + + " 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, '%')) "; + + Query query = getSession().createNativeQuery(SELECT_QUESTIONS); + query.setParameter("questionType", questionType); + // support for custom search from a toolbar + searchString = searchString == null ? "" : searchString; + query.setParameter("searchString", searchString); + int result = ((Number) query.uniqueResult()).intValue(); + return result; + } } \ No newline at end of file Index: lams_common/src/java/org/lamsfoundation/lams/qb/service/IQbService.java =================================================================== diff -u -r75e43eb7dffa6bf6eb2a11bb1e616c53bdbd76ed -r394ff1771926ee7d32cc7eca41bf83fc964c2ebe --- lams_common/src/java/org/lamsfoundation/lams/qb/service/IQbService.java (.../IQbService.java) (revision 75e43eb7dffa6bf6eb2a11bb1e616c53bdbd76ed) +++ lams_common/src/java/org/lamsfoundation/lams/qb/service/IQbService.java (.../IQbService.java) (revision 394ff1771926ee7d32cc7eca41bf83fc964c2ebe) @@ -1,7 +1,11 @@ package org.lamsfoundation.lams.qb.service; import org.lamsfoundation.lams.qb.dto.QbStatsDTO; +import java.util.List; +import org.lamsfoundation.lams.qb.model.QbQuestion; + + public interface IQbService { // statuses of comparing QB question coming from authoring with data existing in DB @@ -14,6 +18,18 @@ static final int QUESTION_MODIFIED_VERSION_BUMP = 2; // it is a new question static final int QUESTION_MODIFIED_ID_BUMP = 3; + + /** + * @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(); @@ -22,4 +38,9 @@ int getMaxQuestionVersion(Integer qbQuestionId); QbStatsDTO getStats(long qbQuestionUid); + + List getPagedQbQuestions(Integer questionType, int page, int size, String sortBy, + String sortOrder, String searchString); + + int getCountQbQuestions(Integer questionType, String searchString); } Index: lams_common/src/java/org/lamsfoundation/lams/qb/service/QbService.java =================================================================== diff -u -r75e43eb7dffa6bf6eb2a11bb1e616c53bdbd76ed -r394ff1771926ee7d32cc7eca41bf83fc964c2ebe --- lams_common/src/java/org/lamsfoundation/lams/qb/service/QbService.java (.../QbService.java) (revision 75e43eb7dffa6bf6eb2a11bb1e616c53bdbd76ed) +++ lams_common/src/java/org/lamsfoundation/lams/qb/service/QbService.java (.../QbService.java) (revision 394ff1771926ee7d32cc7eca41bf83fc964c2ebe) @@ -16,6 +16,16 @@ public class QbService implements IQbService { private IQbDAO qbDAO; + + @Override + public QbQuestion getQbQuestionByUid(Long qbQuestionUid) { + return qbDAO.getQbQuestionByUid(qbQuestionUid); + } + + @Override + public List getQbQuestionsByQuestionId(Integer questionId) { + return qbDAO.getQbQuestionsByQuestionId(questionId); + } @Override public int getMaxQuestionId() { @@ -26,6 +36,17 @@ public int getMaxQuestionVersion(Integer qbQuestionId) { return qbDAO.getMaxQuestionVersion(qbQuestionId); } + + @Override + public List getPagedQbQuestions(Integer questionType, int page, int size, String sortBy, + String sortOrder, String searchString) { + return qbDAO.getPagedQbQuestions(questionType, page, size, sortBy, sortOrder, searchString); + } + + @Override + public int getCountQbQuestions(Integer questionType, String searchString) { + return qbDAO.getCountQbQuestions(questionType, searchString); + } @Override public QbStatsDTO getStats(long qbQuestionUid) {