Index: lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/dao/SpreadsheetUserDAO.java =================================================================== diff -u -re1a335ed6fa2072efc12776a3573856a386938dc -r216fed3c7edfdf10855196a0e9450c68f6ed4485 --- lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/dao/SpreadsheetUserDAO.java (.../SpreadsheetUserDAO.java) (revision e1a335ed6fa2072efc12776a3573856a386938dc) +++ lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/dao/SpreadsheetUserDAO.java (.../SpreadsheetUserDAO.java) (revision 216fed3c7edfdf10855196a0e9450c68f6ed4485) @@ -25,6 +25,7 @@ import java.util.List; +import org.lamsfoundation.lams.notebook.service.ICoreNotebookService; import org.lamsfoundation.lams.tool.spreadsheet.model.SpreadsheetUser; public interface SpreadsheetUserDAO extends DAO { @@ -35,7 +36,8 @@ List getBySessionID(Long sessionId); - List getUsersForTablesorter(final Long sessionId, int page, int size, int sorting, String searchString); + List getUsersForTablesorter(final Long sessionId, int page, int size, int sorting, String searchString, + boolean getNotebookEntries, ICoreNotebookService coreNotebookService ); int getCountUsersBySession(final Long sessionId, String searchString); } Index: lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/dao/hibernate/SpreadsheetUserDAOHibernate.java =================================================================== diff -u -re1a335ed6fa2072efc12776a3573856a386938dc -r216fed3c7edfdf10855196a0e9450c68f6ed4485 --- lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/dao/hibernate/SpreadsheetUserDAOHibernate.java (.../SpreadsheetUserDAOHibernate.java) (revision e1a335ed6fa2072efc12776a3573856a386938dc) +++ lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/dao/hibernate/SpreadsheetUserDAOHibernate.java (.../SpreadsheetUserDAOHibernate.java) (revision 216fed3c7edfdf10855196a0e9450c68f6ed4485) @@ -28,7 +28,8 @@ import org.apache.commons.lang.StringEscapeUtils; import org.apache.commons.lang.StringUtils; import org.hibernate.Hibernate; -import org.lamsfoundation.lams.notebook.service.CoreNotebookConstants; +import org.hibernate.SQLQuery; +import org.lamsfoundation.lams.notebook.service.ICoreNotebookService; import org.lamsfoundation.lams.tool.spreadsheet.SpreadsheetConstants; import org.lamsfoundation.lams.tool.spreadsheet.dao.SpreadsheetUserDAO; import org.lamsfoundation.lams.tool.spreadsheet.model.SpreadsheetUser; @@ -59,14 +60,18 @@ } @SuppressWarnings("unchecked") - public List getUsersForTablesorter(final Long sessionId, int page, int size, int sorting, String searchString) { + /** Will return List<[SpreadsheetUser, String], [SpreadsheetUser, String], ... , [SpreadsheetUser, String]> + * where the String is the notebook entry. No notebook entries needed? Will return in their place. + */ + public List getUsersForTablesorter(final Long sessionId, int page, int size, int sorting, String searchString, + boolean getNotebookEntries, ICoreNotebookService coreNotebookService) { String sortingOrder; switch (sorting) { case SpreadsheetConstants.SORT_BY_USERNAME_ASC: - sortingOrder = "user.lastName ASC, user.firstName ASC"; + sortingOrder = "user.last_name ASC, user.first_name ASC"; break; case SpreadsheetConstants.SORT_BY_USERNAME_DESC: - sortingOrder = "user.lastName DESC, user.firstName DESC"; + sortingOrder = "user.last_name DESC, user.first_name DESC"; break; case SpreadsheetConstants.SORT_BY_MARKED_ASC: sortingOrder = " mark.marks ASC"; @@ -75,50 +80,66 @@ sortingOrder = " mark.marks DESC"; break; default: - sortingOrder = "user.lastName, user.firstName"; + sortingOrder = "user.last_name, user.first_name"; } - String filteredSearchString = buildNameSearch(searchString); - String queryText = "SELECT user FROM " + SpreadsheetUser.class.getName() + " as user "; + // If the session uses notebook, then get the sql to join across to get the entries + String[] notebookEntryStrings = null; + if ( getNotebookEntries ) { + notebookEntryStrings = coreNotebookService.getNotebookEntrySQLStrings(sessionId.toString(),SpreadsheetConstants.TOOL_SIGNATURE,"user.user_id" ); + } + + // Basic select for the user records + StringBuilder queryText = new StringBuilder(); + queryText.append("SELECT user.* "); + queryText.append(notebookEntryStrings != null ? notebookEntryStrings[0] : ", NULL notebookEntry"); + queryText.append(" FROM tl_lasprd10_user user "); + queryText.append(" JOIN tl_lasprd10_session session ON user.session_uid = session.uid and session.session_id = :sessionId"); + + // If sorting by mark then join to mark if (sorting == SpreadsheetConstants.SORT_BY_MARKED_ASC || sorting == SpreadsheetConstants.SORT_BY_MARKED_DESC) { - queryText += " LEFT JOIN user.userModifiedSpreadsheet as ums " - + " LEFT JOIN ums.mark as mark "; + queryText.append(" LEFT JOIN tl_lasprd10_user_modified_spreadsheet ms on user.user_modified_spreadsheet_uid=ms.uid "); + queryText.append(" LEFT JOIN tl_lasprd10_spreadsheet_mark mark on ms.mark_id = mark.uid "); } - queryText+= " WHERE user.session.sessionId=:sessionId " - + ( filteredSearchString != null ? filteredSearchString : "" ) - + " ORDER BY " + sortingOrder; + // If using notebook, add the notebook join + if ( notebookEntryStrings != null ) + queryText.append(notebookEntryStrings[1]); - return getSession().createQuery(queryText).setLong("sessionId", sessionId.longValue()).setFirstResult(page * size).setMaxResults(size).list(); + // If filtering by name add a name based where clause + buildNameSearch(searchString, queryText); + + // Now specify the sort based on the switch statement above. + queryText.append(" ORDER BY " + sortingOrder); + + SQLQuery query = getSession().createSQLQuery(queryText.toString()); + query.addEntity("user", SpreadsheetUser.class) + .addScalar("notebookEntry", Hibernate.STRING) + .setLong("sessionId", sessionId.longValue()); + return query.list(); } - private String buildNameSearch(String searchString) { - String filteredSearchString = null; + private void buildNameSearch(String searchString, StringBuilder sqlBuilder) { if (!StringUtils.isBlank(searchString)) { - StringBuilder searchStringBuilder = new StringBuilder(""); String[] tokens = searchString.trim().split("\\s+"); for (String token : tokens) { String escToken = StringEscapeUtils.escapeSql(token); - searchStringBuilder.append(" AND (user.firstName LIKE '%").append(escToken) - .append("%' OR user.lastName LIKE '%").append(escToken) - .append("%' OR user.loginName LIKE '%").append(escToken).append("%')"); + sqlBuilder.append(" WHERE (user.first_name LIKE '%").append(escToken) + .append("%' OR user.last_name LIKE '%").append(escToken) + .append("%' OR user.login_name LIKE '%").append(escToken).append("%') "); } - filteredSearchString = searchStringBuilder.toString(); } - return filteredSearchString; } @SuppressWarnings("rawtypes") public int getCountUsersBySession(final Long sessionId, String searchString) { - String filteredSearchString = buildNameSearch(searchString); - String queryText = "SELECT count(*) FROM " + SpreadsheetUser.class.getName() + " user WHERE user.session.sessionId=:sessionId "; - if ( filteredSearchString != null ) - queryText += filteredSearchString; + StringBuilder queryText = new StringBuilder("SELECT count(*) FROM tl_lasprd10_user user "); + queryText.append(" JOIN tl_lasprd10_session session ON user.session_uid = session.uid and session.session_id = :sessionId"); + buildNameSearch(searchString, queryText); - List list = getSession().createQuery(queryText).setLong("sessionId", sessionId.longValue()).list(); - + List list = getSession().createSQLQuery(queryText.toString()).setLong("sessionId", sessionId.longValue()).list(); if (list == null || list.size() == 0) { return 0; } Index: lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/service/ISpreadsheetService.java =================================================================== diff -u -re1a335ed6fa2072efc12776a3573856a386938dc -r216fed3c7edfdf10855196a0e9450c68f6ed4485 --- lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/service/ISpreadsheetService.java (.../ISpreadsheetService.java) (revision e1a335ed6fa2072efc12776a3573856a386938dc) +++ lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/service/ISpreadsheetService.java (.../ISpreadsheetService.java) (revision 216fed3c7edfdf10855196a0e9450c68f6ed4485) @@ -149,7 +149,7 @@ * Get a paged, optionally sorted and filtered, list of users. * @return */ - List getUsersForTablesorter(final Long sessionId, int page, int size, int sorting, String searchString); + List getUsersForTablesorter(final Long sessionId, int page, int size, int sorting, String searchString, boolean getNotebookEntries); /** * Get the number of users that would be returned by getUsersForTablesorter() if it was not paged. Supports filtering. Index: lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/service/SpreadsheetServiceImpl.java =================================================================== diff -u -re1a335ed6fa2072efc12776a3573856a386938dc -r216fed3c7edfdf10855196a0e9450c68f6ed4485 --- lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/service/SpreadsheetServiceImpl.java (.../SpreadsheetServiceImpl.java) (revision e1a335ed6fa2072efc12776a3573856a386938dc) +++ lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/service/SpreadsheetServiceImpl.java (.../SpreadsheetServiceImpl.java) (revision 216fed3c7edfdf10855196a0e9450c68f6ed4485) @@ -293,8 +293,8 @@ } @Override - public List getUsersForTablesorter(final Long sessionId, int page, int size, int sorting, String searchString) { - return spreadsheetUserDao.getUsersForTablesorter(sessionId, page, size, sorting, searchString); + public List getUsersForTablesorter(final Long sessionId, int page, int size, int sorting, String searchString, boolean getNotebookEntries) { + return spreadsheetUserDao.getUsersForTablesorter(sessionId, page, size, sorting, searchString, getNotebookEntries, coreNotebookService); } @Override Index: lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/web/action/MonitoringAction.java =================================================================== diff -u -re1a335ed6fa2072efc12776a3573856a386938dc -r216fed3c7edfdf10855196a0e9450c68f6ed4485 --- lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/web/action/MonitoringAction.java (.../MonitoringAction.java) (revision e1a335ed6fa2072efc12776a3573856a386938dc) +++ lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/web/action/MonitoringAction.java (.../MonitoringAction.java) (revision 216fed3c7edfdf10855196a0e9450c68f6ed4485) @@ -165,18 +165,18 @@ //return user list according to the given sessionID ISpreadsheetService service = getSpreadsheetService(); - List users = service.getUsersForTablesorter(sessionID, page, size, sorting, searchString); + Spreadsheet spreadsheet = service.getSpreadsheetByContentId(contentId); + List users = service.getUsersForTablesorter(sessionID, page, size, sorting, searchString, spreadsheet.isReflectOnActivity()); JSONArray rows = new JSONArray(); JSONObject responsedata = new JSONObject(); responsedata.put("total_rows", service.getCountUsersBySession(sessionID, searchString)); - Spreadsheet spreadsheet = service.getSpreadsheetByContentId(contentId); - boolean reflect = spreadsheet.isReflectOnActivity(); - - for (SpreadsheetUser user : users) { - + for (Object[] userAndReflection : users) { + JSONObject responseRow = new JSONObject(); + + SpreadsheetUser user = (SpreadsheetUser) userAndReflection[0]; responseRow.put(SpreadsheetConstants.ATTR_USER_UID, user.getUid()); responseRow.put(SpreadsheetConstants.ATTR_USER_NAME, StringEscapeUtils.escapeHtml(user.getLastName() + " " + user.getFirstName())); if ( user.getUserModifiedSpreadsheet() != null ) { @@ -186,11 +186,8 @@ } } - if ( reflect ) { - NotebookEntry notebookEntry = service.getEntry(sessionID, CoreNotebookConstants.NOTEBOOK_TOOL, - SpreadsheetConstants.TOOL_SIGNATURE, user.getUserId().intValue()); - if ( notebookEntry != null ) - responseRow.put("reflection", notebookEntry.getEntry()); + if ( userAndReflection.length > 1 && userAndReflection[1] != null) { + responseRow.put("reflection", userAndReflection[1]); } rows.put(responseRow);