Index: lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/QaAppConstants.java =================================================================== diff -u -r40937c574021c496d91487979b905b23a193b766 -r95202c29d0cab6765b9c03fff3a721826ab7ec9d --- lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/QaAppConstants.java (.../QaAppConstants.java) (revision 40937c574021c496d91487979b905b23a193b766) +++ lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/QaAppConstants.java (.../QaAppConstants.java) (revision 95202c29d0cab6765b9c03fff3a721826ab7ec9d) @@ -30,8 +30,10 @@ public interface QaAppConstants { public static final int SORT_BY_NO = 0; - public static final int SORT_BY_ANSWER_ASC = 1; - public static final int SORT_BY_ANSWER_DESC = 2; + public static final int SORT_BY_USERNAME_ASC = 1; + public static final int SORT_BY_USERNAME_DESC = 2; + public static final int SORT_BY_RATING_ASC = 3; + public static final int SORT_BY_RATING_DESC = 4; public static final String MY_SIGNATURE = "laqa11"; public static final String TOOL_CONTENT_ID = "toolContentID"; Index: lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/dao/IQaUsrRespDAO.java =================================================================== diff -u -ra5ae87b5075d9aa1b5bec2b62758a76b16c3138a -r95202c29d0cab6765b9c03fff3a721826ab7ec9d --- lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/dao/IQaUsrRespDAO.java (.../IQaUsrRespDAO.java) (revision a5ae87b5075d9aa1b5bec2b62758a76b16c3138a) +++ lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/dao/IQaUsrRespDAO.java (.../IQaUsrRespDAO.java) (revision 95202c29d0cab6765b9c03fff3a721826ab7ec9d) @@ -48,7 +48,7 @@ List getResponseBySessionAndQuestion(final Long qaSessionId, final Long questionId); - List getResponsesForTablesorter(final Long qaSessionId, final Long questionId, final Long userId, + List getResponsesForTablesorter(final Long toolContentId, final Long qaSessionId, final Long questionId, final Long userId, int page, int size, int sorting, String searchString); int getCountResponsesBySessionAndQuestion(final Long qaSessionId, final Long questionId, final Long excludeUserId, String searchString); Index: lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/dao/hibernate/QaUsrRespDAO.java =================================================================== diff -u -ra5ae87b5075d9aa1b5bec2b62758a76b16c3138a -r95202c29d0cab6765b9c03fff3a721826ab7ec9d --- lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/dao/hibernate/QaUsrRespDAO.java (.../QaUsrRespDAO.java) (revision a5ae87b5075d9aa1b5bec2b62758a76b16c3138a) +++ lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/dao/hibernate/QaUsrRespDAO.java (.../QaUsrRespDAO.java) (revision 95202c29d0cab6765b9c03fff3a721826ab7ec9d) @@ -27,10 +27,10 @@ import org.apache.log4j.Logger; import org.hibernate.FlushMode; +import org.hibernate.Query; import org.lamsfoundation.lams.tool.qa.QaAppConstants; import org.lamsfoundation.lams.tool.qa.QaUsrResp; import org.lamsfoundation.lams.tool.qa.dao.IQaUsrRespDAO; -import org.lamsfoundation.lams.tool.qa.web.QaLearningAction; import org.springframework.orm.hibernate3.support.HibernateDaoSupport; /** @@ -51,6 +51,23 @@ + " AND qaUsrResp.qaQueUser.fullname LIKE CONCAT('%', :searchString, '%') " + " order by "; + private static final String SQL_LOAD_ATTEMPT_FOR_SESSION_AND_QUESTION_LIMIT_WITH_NAME_SEARCH_AVG_RATING = "select resp.*, avg(rating.rating) avg_rating" + + " from tl_laqa11_usr_resp resp" + + " inner join tl_laqa11_que_usr usr" + + " on resp.qa_que_content_id = :questionId and resp.que_usr_id = usr.uid " + + " and usr.que_usr_id!=:excludeUserId " + + " inner join tl_laqa11_session sess " + + " on usr.qa_session_id = sess.uid and sess.qa_session_id = :qaSessionId " + + " and usr.fullname LIKE CONCAT('%', :searchString, '%')" + + " left join (" + + " select rat.item_id, rat.rating from lams_rating rat" + + " inner join lams_rating_criteria crit" + + " on rat.rating_criteria_id = crit.rating_criteria_id and crit.tool_content_id = :toolContentId" + + " ) rating" + + " on rating.item_id = resp.response_id" + + " group by response_id" + + " order by"; + private static final String LOAD_ATTEMPT_FOR_USER = "from qaUsrResp in class QaUsrResp " + "where qaUsrResp.qaQueUser.uid=:userUid order by qaUsrResp.qaQuestion.displayOrder asc"; @@ -102,29 +119,51 @@ } @Override - public List getResponsesForTablesorter(final Long qaSessionId, final Long questionId, final Long excludeUserId, + public List getResponsesForTablesorter(final Long toolContentId, final Long qaSessionId, final Long questionId, final Long excludeUserId, int page, int size, int sorting, String searchString) { - String sortingOrder = ""; + String sortingOrder = " resp.attempt_time"; // default if we get an unexpected sort order + boolean useAverageRatingSort = false; switch (sorting) { case QaAppConstants.SORT_BY_NO: sortingOrder = "qaUsrResp.attemptTime"; break; - case QaAppConstants.SORT_BY_ANSWER_ASC: + case QaAppConstants.SORT_BY_USERNAME_ASC: sortingOrder = "qaUsrResp.qaQueUser.fullname ASC"; break; - case QaAppConstants.SORT_BY_ANSWER_DESC: + case QaAppConstants.SORT_BY_USERNAME_DESC: sortingOrder = "qaUsrResp.qaQueUser.fullname DESC"; break; + case QaAppConstants.SORT_BY_RATING_ASC: + sortingOrder = " avg_rating ASC"; + useAverageRatingSort = true; + break; + case QaAppConstants.SORT_BY_RATING_DESC: + sortingOrder = " avg_rating DESC"; + useAverageRatingSort = true; + break; } - String filter = searchString != null ? searchString.trim() : ""; - // TODO parse out special chars - return getSession().createQuery(LOAD_ATTEMPT_FOR_SESSION_AND_QUESTION_LIMIT_WITH_NAME_SEARCH + sortingOrder) - .setLong("qaSessionId", qaSessionId.longValue()).setLong("questionId", questionId.longValue()) - .setLong("excludeUserId", excludeUserId.longValue()).setString("searchString",filter) - .setFirstResult(page * size).setMaxResults(size) - .list(); + Query query = null; + if ( useAverageRatingSort ) { + query = getSession() + .createSQLQuery(SQL_LOAD_ATTEMPT_FOR_SESSION_AND_QUESTION_LIMIT_WITH_NAME_SEARCH_AVG_RATING+sortingOrder).addEntity(QaUsrResp.class) + .setLong("toolContentId", toolContentId.longValue()); + } else { + query = getSession().createQuery(LOAD_ATTEMPT_FOR_SESSION_AND_QUESTION_LIMIT_WITH_NAME_SEARCH+sortingOrder); + } + + query.setLong("questionId", questionId.longValue()); + query.setLong("qaSessionId", qaSessionId.longValue()); + query.setLong("excludeUserId", excludeUserId.longValue()); + + // support for custom search from a toolbar + searchString = searchString == null ? "" : searchString; + query.setString("searchString", searchString); + + query.setFirstResult(page * size); + query.setMaxResults(size); + return query.list(); } @Override Index: lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/service/IQaService.java =================================================================== diff -u -ra5ae87b5075d9aa1b5bec2b62758a76b16c3138a -r95202c29d0cab6765b9c03fff3a721826ab7ec9d --- lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/service/IQaService.java (.../IQaService.java) (revision a5ae87b5075d9aa1b5bec2b62758a76b16c3138a) +++ lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/service/IQaService.java (.../IQaService.java) (revision 95202c29d0cab6765b9c03fff3a721826ab7ec9d) @@ -25,7 +25,6 @@ import java.util.Collection; import java.util.List; -import java.util.Map; import java.util.Set; import java.util.SortedSet; @@ -108,9 +107,9 @@ List getResponseBySessionAndQuestion(final Long qaSessionId, final Long questionId); - List getResponsesForTablesorter(final Long qaSessionId, final Long questionId, final Long excludeUserId, + List getResponsesForTablesorter(final Long toolContentId, final Long qaSessionId, final Long questionId, final Long excludeUserId, int page, int size, int sorting, String searchString); - + int getCountResponsesBySessionAndQuestion(final Long qaSessionId, final Long questionId, final Long excludeUserId, String searchString); /** Index: lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/service/QaServicePOJO.java =================================================================== diff -u -ra5ae87b5075d9aa1b5bec2b62758a76b16c3138a -r95202c29d0cab6765b9c03fff3a721826ab7ec9d --- lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/service/QaServicePOJO.java (.../QaServicePOJO.java) (revision a5ae87b5075d9aa1b5bec2b62758a76b16c3138a) +++ lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/service/QaServicePOJO.java (.../QaServicePOJO.java) (revision 95202c29d0cab6765b9c03fff3a721826ab7ec9d) @@ -307,11 +307,14 @@ } @Override - public List getResponsesForTablesorter(final Long qaSessionId, final Long questionId, + public List getResponsesForTablesorter(final Long toolContentId, final Long qaSessionId, final Long questionId, final Long excludeUserId, int page, int size, int sorting, String searchString) { - return qaUsrRespDAO.getResponsesForTablesorter(qaSessionId, questionId, excludeUserId, page, size, sorting, searchString); + return qaUsrRespDAO.getResponsesForTablesorter(toolContentId, qaSessionId, questionId, excludeUserId, page, size, sorting, searchString); } + private String createRatingKey(Long qaSessionId, final Long questionId) { + return qaSessionId + "-" + questionId; + } @Override public int getCountResponsesBySessionAndQuestion(final Long qaSessionId, final Long questionId, final Long excludeUserId, String searchString) { Index: lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/web/QaLearningAction.java =================================================================== diff -u -ra5ae87b5075d9aa1b5bec2b62758a76b16c3138a -r95202c29d0cab6765b9c03fff3a721826ab7ec9d --- lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/web/QaLearningAction.java (.../QaLearningAction.java) (revision a5ae87b5075d9aa1b5bec2b62758a76b16c3138a) +++ lams_tool_laqa/src/java/org/lamsfoundation/lams/tool/qa/web/QaLearningAction.java (.../QaLearningAction.java) (revision 95202c29d0cab6765b9c03fff3a721826ab7ec9d) @@ -1042,26 +1042,27 @@ //paging parameters of tablesorter int size = WebUtil.readIntParam(request, "size"); int page = WebUtil.readIntParam(request, "page"); - Integer isSort1 = WebUtil.readIntParam(request, "column[0]", true); + Integer sortByUser = WebUtil.readIntParam(request, "column[0]", true); + Integer sortByRating = WebUtil.readIntParam(request, "column[1]", true); String searchString = request.getParameter("fcol[0]"); - log.debug("filter value "+searchString); int sorting = QaAppConstants.SORT_BY_NO; - if (isSort1 != null && isSort1.equals(0)) { - sorting = QaAppConstants.SORT_BY_ANSWER_ASC; - } else if (isSort1 != null && isSort1.equals(1)) { - sorting = QaAppConstants.SORT_BY_ANSWER_DESC; + if (sortByUser != null ) { + sorting = sortByUser.equals(0) ? QaAppConstants.SORT_BY_USERNAME_ASC : QaAppConstants.SORT_BY_USERNAME_DESC; + } else if ( sortByRating != null ) { + sorting = sortByRating.equals(0) ? QaAppConstants.SORT_BY_RATING_ASC : QaAppConstants.SORT_BY_RATING_DESC; } - - List responses = qaService.getResponsesForTablesorter(qaSessionId, questionUid, userId, page, size, + + List responses = qaService.getResponsesForTablesorter(qaContentId, qaSessionId, questionUid, userId, page, size, sorting, searchString); JSONObject responcedata = new JSONObject(); JSONArray rows = new JSONArray(); responcedata.put("total_rows", qaService.getCountResponsesBySessionAndQuestion(qaSessionId, questionUid, userId, searchString)); - //handle rating criterias + // handle rating criterias - even though we may have searched on ratings earlier we can't use the average ratings + // calculated as they may have been averages over more than one criteria. List itemRatingDtos = null; if (isAllowRateAnswers && !responses.isEmpty()) { //create itemIds list Index: lams_tool_laqa/web/monitoring/MonitoringMaincontent.jsp =================================================================== diff -u -ra5ae87b5075d9aa1b5bec2b62758a76b16c3138a -r95202c29d0cab6765b9c03fff3a721826ab7ec9d --- lams_tool_laqa/web/monitoring/MonitoringMaincontent.jsp (.../MonitoringMaincontent.jsp) (revision a5ae87b5075d9aa1b5bec2b62758a76b16c3138a) +++ lams_tool_laqa/web/monitoring/MonitoringMaincontent.jsp (.../MonitoringMaincontent.jsp) (revision 95202c29d0cab6765b9c03fff3a721826ab7ec9d) @@ -150,7 +150,7 @@ theme: 'blue', widthFixed: true, widgets: ["zebra", "filter"], - headers: { 1: { filter: false, sorter: false }, 2: { filter: false, sorter: false } }, + headers: { 1: { filter: false }, 2: { filter: false, sorter: false } }, widgetOptions : { // include column filters filter_columnFilters: true,