Index: lams_admin/conf/language/lams/ApplicationResources.properties =================================================================== diff -u -rfd2cfad55c7c517931f69334ce644d509ec28140 -r8e2e72a36d57288787cf76af6484e25c82b385c1 --- lams_admin/conf/language/lams/ApplicationResources.properties (.../ApplicationResources.properties) (revision fd2cfad55c7c517931f69334ce644d509ec28140) +++ lams_admin/conf/language/lams/ApplicationResources.properties (.../ApplicationResources.properties) (revision 8e2e72a36d57288787cf76af6484e25c82b385c1) @@ -634,5 +634,5 @@ outcome.authoring.input =Search and select by outcome name or code outcome.authoring.existing =Added outcomes outcome.authoring.existing.none =none - +label.search =Search... #======= End labels: Exported 629 labels for en AU ===== Index: lams_admin/src/java/org/lamsfoundation/lams/admin/web/controller/LogEventController.java =================================================================== diff -u -rf2ad75cef0c507a64877942631fee13efbc6ed50 -r8e2e72a36d57288787cf76af6484e25c82b385c1 --- lams_admin/src/java/org/lamsfoundation/lams/admin/web/controller/LogEventController.java (.../LogEventController.java) (revision f2ad75cef0c507a64877942631fee13efbc6ed50) +++ lams_admin/src/java/org/lamsfoundation/lams/admin/web/controller/LogEventController.java (.../LogEventController.java) (revision 8e2e72a36d57288787cf76af6484e25c82b385c1) @@ -38,6 +38,7 @@ import org.lamsfoundation.lams.logevent.service.ILogEventService; import org.lamsfoundation.lams.usermanagement.Role; import org.lamsfoundation.lams.usermanagement.User; +import org.lamsfoundation.lams.util.DateUtil; import org.lamsfoundation.lams.util.JsonUtil; import org.lamsfoundation.lams.util.MessageService; import org.lamsfoundation.lams.util.WebUtil; @@ -111,12 +112,20 @@ // paging parameters of tablesorter int size = WebUtil.readIntParam(request, "size"); int page = WebUtil.readIntParam(request, "page"); - Integer isSort1 = WebUtil.readIntParam(request, "column[0]", true); -// String searchString = request.getParameter("fcol[0]"); + Integer isSortDate = WebUtil.readIntParam(request, "column[0]", true); + Integer isSortUser = WebUtil.readIntParam(request, "column[2]", true); + Integer isSortTarget = WebUtil.readIntParam(request, "column[3]", true); + String searchUser = request.getParameter("fcol[2]"); + String searchTarget = request.getParameter("fcol[3]"); + String searchRemarks = request.getParameter("fcol[4]"); - int sorting = ILogEventService.SORT_BY_DATE_ASC; - if ((isSort1 != null) && isSort1.equals(1)) { - sorting = ILogEventService.SORT_BY_DATE_DESC; + int sorting = ILogEventService.SORT_BY_DATE_DESC; + if (isSortDate != null) { + sorting = isSortDate.equals(1) ? ILogEventService.SORT_BY_DATE_DESC : ILogEventService.SORT_BY_DATE_ASC; + } else if (isSortUser != null) { + sorting = isSortUser.equals(1) ? ILogEventService.SORT_BY_USER_DESC : ILogEventService.SORT_BY_USER_ASC; + } else if (isSortTarget != null) { + sorting = isSortTarget.equals(1) ? ILogEventService.SORT_BY_TARGET_DESC : ILogEventService.SORT_BY_TARGET_ASC; } Long dateParameter = WebUtil.readLongParam(request, "startDate", true); @@ -139,20 +148,21 @@ String area = WebUtil.readStrParam(request, "area", true); Integer typeId = WebUtil.readIntParam(request, "typeId", true); - List events = logEventService.getEventsForTablesorter(page, size, sorting, null, startDate, endDate, - area, typeId); + List events = logEventService.getEventsForTablesorter(page, size, sorting, searchUser, searchTarget, + searchRemarks, startDate, endDate, area, typeId); ArrayNode rows = JsonNodeFactory.instance.arrayNode(); ObjectNode responsedata = JsonNodeFactory.instance.objectNode(); - responsedata.put("total_rows", - logEventService.countEventsWithRestrictions(null, startDate, endDate, area, typeId)); + responsedata.put("total_rows", logEventService.countEventsWithRestrictions(searchUser, searchTarget, + searchRemarks, startDate, endDate, area, typeId)); for (Object[] eventDetails : events) { if (eventDetails.length > 0) { LogEvent event = (LogEvent) eventDetails[0]; ObjectNode responseRow = JsonNodeFactory.instance.objectNode(); - responseRow.put("dateOccurred", JsonUtil.toString(event.getOccurredDateTime())); + responseRow.put("dateOccurred", event.getOccurredDateTime() != null ? + DateUtil.convertToStringForJSON(event.getOccurredDateTime(), request.getLocale()) : ""); responseRow.put("typeId", event.getLogEventTypeId()); responseRow.put("description", event.getDescription()); if (event.getLessonId() != null) { Index: lams_admin/web/logevent.jsp =================================================================== diff -u -r79b6147f263fbc3c92e2db7d98227fa8f7358851 -r8e2e72a36d57288787cf76af6484e25c82b385c1 --- lams_admin/web/logevent.jsp (.../logevent.jsp) (revision 79b6147f263fbc3c92e2db7d98227fa8f7358851) +++ lams_admin/web/logevent.jsp (.../logevent.jsp) (revision 8e2e72a36d57288787cf76af6484e25c82b385c1) @@ -71,7 +71,7 @@ sortInitialOrder: 'desc', sortList: [[0]], widgets: [ "uitheme", "resizable", "filter" ], - headers: { 0: { filter: false}, 1: { filter: false, sorter: false}, 2: { filter: false, sorter: false}, 3: { filter: false, sorter: false}, 4: { filter: false, sorter: false}, }, + headers: { 0: { filter: false, sorter: true}, 1: { filter: false, sorter: false}, 2: { filter: true, sorter: true}, 3: { filter: true, sorter: true}, 4: { filter: true, sorter: false}, }, sortList : [[0,1]], showProcessing: true, widgetOptions: { @@ -107,7 +107,7 @@ rows += ''; - rows += ''; + rows += ''; rows += logData['dateOccurred']; rows += ''; Index: lams_common/src/java/org/lamsfoundation/lams/logevent/dao/ILogEventDAO.java =================================================================== diff -u -r7475d08afc280b5e2e5ddf04e8bf35e3166aaf80 -r8e2e72a36d57288787cf76af6484e25c82b385c1 --- lams_common/src/java/org/lamsfoundation/lams/logevent/dao/ILogEventDAO.java (.../ILogEventDAO.java) (revision 7475d08afc280b5e2e5ddf04e8bf35e3166aaf80) +++ lams_common/src/java/org/lamsfoundation/lams/logevent/dao/ILogEventDAO.java (.../ILogEventDAO.java) (revision 8e2e72a36d57288787cf76af6484e25c82b385c1) @@ -76,9 +76,10 @@ Date getOldestEventDate(); /** Used for displaying paged lists of events */ - List getEventsForTablesorter(int page, int size, int sorting, String searchString, Date startDate, + List getEventsForTablesorter(int page, int size, int sorting, String searchUser, String searchTarget, + String searchRemarks, Date startDate, Date endDate, String area, Integer typeId); + + int countEventsWithRestrictions(String searchUser, String searchTarget, String searchRemarks, Date startDate, Date endDate, String area, Integer typeId); - int countEventsWithRestrictions(String searchString, Date startDate, Date endDate, String area, Integer typeId); - } Index: lams_common/src/java/org/lamsfoundation/lams/logevent/dao/hibernate/LogEventDAO.java =================================================================== diff -u -r7475d08afc280b5e2e5ddf04e8bf35e3166aaf80 -r8e2e72a36d57288787cf76af6484e25c82b385c1 --- lams_common/src/java/org/lamsfoundation/lams/logevent/dao/hibernate/LogEventDAO.java (.../LogEventDAO.java) (revision 7475d08afc280b5e2e5ddf04e8bf35e3166aaf80) +++ lams_common/src/java/org/lamsfoundation/lams/logevent/dao/hibernate/LogEventDAO.java (.../LogEventDAO.java) (revision 8e2e72a36d57288787cf76af6484e25c82b385c1) @@ -26,7 +26,8 @@ import java.util.Date; import java.util.List; -import org.hibernate.SQLQuery; +import org.apache.commons.lang.StringEscapeUtils; +import org.hibernate.query.NativeQuery; import org.hibernate.type.StringType; import org.lamsfoundation.lams.dao.hibernate.LAMSBaseDAO; import org.lamsfoundation.lams.logevent.LogEvent; @@ -105,8 +106,8 @@ * Will return List<[LogEvent, String], [LogEvent, String], ... , [LogEvent, String]> * where the String is the lesson name. Lesson name may be null. */ - public List getEventsForTablesorter(int page, int size, int sorting, String searchString, Date startDate, - Date endDate, String area, Integer typeId) { + public List getEventsForTablesorter(int page, int size, int sorting, String searchUser, + String searchTarget, String searchRemarks, Date startDate, Date endDate, String area, Integer typeId) { String sortingOrder; switch (sorting) { case ILogEventService.SORT_BY_DATE_ASC: @@ -115,25 +116,62 @@ case ILogEventService.SORT_BY_DATE_DESC: sortingOrder = "occurred_date_time DESC"; break; + case ILogEventService.SORT_BY_USER_ASC: + sortingOrder = "user.login ASC"; + break; + case ILogEventService.SORT_BY_USER_DESC: + sortingOrder = "user.login DESC"; + break; + case ILogEventService.SORT_BY_TARGET_ASC: + sortingOrder = "target.login ASC"; + break; + case ILogEventService. SORT_BY_TARGET_DESC: + sortingOrder = "target.login DESC"; + break; default: sortingOrder = "occurred_date_time ASC"; } + if ( searchUser != null && searchUser.isBlank()) { + searchUser = null; + } + if ( searchTarget != null && searchTarget.isBlank()) { + searchTarget = null; + } + if ( searchRemarks != null && searchRemarks.isBlank()) { + searchRemarks = null; + } + // Basic select for the user records StringBuilder queryText = new StringBuilder(); - queryText.append("SELECT le.*, lesson.name lessonName, activity.title activityName FROM lams_log_event le ") + queryText.append("SELECT le.*, lesson.name lessonName, activity.title activityName") + .append(" FROM lams_log_event le ") .append(" LEFT JOIN lams_lesson lesson ON le.lesson_id = lesson.lesson_id") .append(" LEFT JOIN lams_learning_activity activity ON le.activity_id = activity.activity_id"); + + if ( searchUser != null || sorting == ILogEventService.SORT_BY_USER_ASC || sorting == ILogEventService.SORT_BY_USER_DESC ) { + queryText.append(" LEFT JOIN lams_user user ON user.user_id = le.user_id"); + } + if ( searchTarget != null || sorting == ILogEventService.SORT_BY_TARGET_ASC || sorting == ILogEventService.SORT_BY_TARGET_DESC ) { + queryText.append(" LEFT JOIN lams_user target ON target.user_id = le.target_user_id"); + } - addWhereClause(startDate, endDate, area, typeId, queryText); -// // If filtering by name add a name based where clause (LDEV-3779: must come before the Notebook JOIN statement) -// buildNameSearch(queryText, searchString); -// + boolean hasAWhereClause = addWhereClause(startDate, endDate, area, typeId, queryText); + if ( searchUser != null ) { + hasAWhereClause = buildNameSearch(hasAWhereClause, queryText, searchUser.strip(), "user"); + } + if ( searchTarget != null ) { + hasAWhereClause = buildNameSearch(hasAWhereClause, queryText, searchTarget.strip(), "target"); + } + if ( searchRemarks != null ) { + hasAWhereClause = buildRemarksSearch(hasAWhereClause, queryText, searchRemarks.strip()); + } + // Now specify the sort based on the switch statement above. queryText.append(" ORDER BY " + sortingOrder); - SQLQuery query = getSession().createSQLQuery(queryText.toString()); + NativeQuery query = getSession().createNativeQuery(queryText.toString()); query.addEntity("event", LogEvent.class); query.addScalar("lessonName", StringType.INSTANCE); query.addScalar("activityName", StringType.INSTANCE); @@ -143,7 +181,8 @@ } - private void addWhereClause(Date startDate, Date endDate, String area, Integer typeId, StringBuilder queryText) { + /* Returns true if a WHERE clause has been added */ + private boolean addWhereClause(Date startDate, Date endDate, String area, Integer typeId, StringBuilder queryText) { boolean needAnd = false; if ((startDate != null && endDate != null) || area != null || typeId != null) { queryText.append(" WHERE "); @@ -161,45 +200,96 @@ queryText.append( " log_event_type_id in (SELECT log_event_type_id FROM lams_log_event_type WHERE area = :area) "); } + return true; } + return false; } - private void addParameters(Date startDate, Date endDate, String area, Integer typeId, SQLQuery query) { + private void addParameters(Date startDate, Date endDate, String area, Integer typeId, NativeQuery query) { if (startDate != null && endDate != null) { - query.setDate("startDate", startDate); - query.setDate("endDate", endDate); + query.setParameter("startDate", startDate); + query.setParameter("endDate", endDate); } if (typeId != null) { - query.setInteger("typeId", typeId); + query.setParameter("typeId", typeId); } else if (area != null) { - query.setString("area", area); + query.setParameter("area", area); } } -// private void buildNameSearch(StringBuilder queryText, String searchString) { -// if (!StringUtils.isBlank(searchString)) { -// String[] tokens = searchString.trim().split("\\s+"); -// for (String token : tokens) { -// String escToken = StringEscapeUtils.escapeSql(token); -// queryText.append(" AND (user.first_name LIKE '%").append(escToken) -// .append("%' OR user.last_name LIKE '%").append(escToken).append("%' OR user.login_name LIKE '%") -// .append(escToken).append("%')"); -// } -// } -// } + /* Returns true if a WHERE clause has been added */ + private boolean buildNameSearch(boolean hasAWhereClause, StringBuilder queryText, String searchString, + String userAlias) { + if (!hasAWhereClause) { + queryText.append(" WHERE "); + } + String[] tokens = searchString.trim().split("\\s+"); + for (String token : tokens) { + String escToken = StringEscapeUtils.escapeSql(token); + if (hasAWhereClause) { + queryText.append(" AND "); + } + queryText.append(" (").append(userAlias).append(".first_name LIKE '%").append(escToken).append("%' OR ") + .append(userAlias).append(".last_name LIKE '%").append(escToken).append("%' OR ").append(userAlias) + .append(".login LIKE '%").append(escToken).append("%') "); + } + return true; + } + + /* Returns true if a WHERE clause has been added */ + private boolean buildRemarksSearch(boolean hasAWhereClause, StringBuilder queryText, String searchString) { + if (!hasAWhereClause) { + queryText.append(" WHERE "); + } + String[] tokens = searchString.trim().split("\\s+"); + for (String token : tokens) { + String escToken = StringEscapeUtils.escapeSql(token); + if (hasAWhereClause) { + queryText.append(" AND "); + } + queryText.append(" (").append("le.description LIKE '%").append(escToken).append("%') "); + } + return true; + } + @Override - @SuppressWarnings("rawtypes") - public int countEventsWithRestrictions(String searchString, Date startDate, Date endDate, String area, - Integer typeId) { + @SuppressWarnings({ "rawtypes", "unchecked" }) + public int countEventsWithRestrictions(String searchUser, String searchTarget, String searchRemarks, Date startDate, Date endDate, String area, Integer typeId) { + if ( searchUser != null && searchUser.isBlank()) { + searchUser = null; + } + if ( searchTarget != null && searchTarget.isBlank()) { + searchTarget = null; + } + if ( searchRemarks != null && searchRemarks.isBlank()) { + searchRemarks = null; + } + // Basic select for the user records StringBuilder queryText = new StringBuilder(); - queryText.append("SELECT count(*) FROM lams_log_event e "); - addWhereClause(startDate, endDate, area, typeId, queryText); + queryText.append("SELECT count(*) FROM lams_log_event le "); + if ( searchUser != null ) { + queryText.append(" LEFT JOIN lams_user user ON user.user_id = le.user_id"); + } + if ( searchTarget != null ) { + queryText.append(" LEFT JOIN lams_user target ON target.user_id = le.target_user_id"); + } - SQLQuery query = getSession().createSQLQuery(queryText.toString()); + boolean hasAWhereClause = addWhereClause(startDate, endDate, area, typeId, queryText); + if ( searchUser != null ) { + hasAWhereClause = buildNameSearch(hasAWhereClause, queryText, searchUser.strip(), "user"); + } + if ( searchTarget != null ) { + hasAWhereClause = buildNameSearch(hasAWhereClause, queryText, searchTarget.strip(), "target"); + } + if ( searchRemarks != null ) { + hasAWhereClause = buildRemarksSearch(hasAWhereClause, queryText, searchRemarks.strip()); + } + + NativeQuery query = getSession().createNativeQuery(queryText.toString()); addParameters(startDate, endDate, area, typeId, query); List list = query.list(); Index: lams_common/src/java/org/lamsfoundation/lams/logevent/service/ILogEventService.java =================================================================== diff -u -r62aaf160878735888d077bf28fac3c1989bb8fbd -r8e2e72a36d57288787cf76af6484e25c82b385c1 --- lams_common/src/java/org/lamsfoundation/lams/logevent/service/ILogEventService.java (.../ILogEventService.java) (revision 62aaf160878735888d077bf28fac3c1989bb8fbd) +++ lams_common/src/java/org/lamsfoundation/lams/logevent/service/ILogEventService.java (.../ILogEventService.java) (revision 8e2e72a36d57288787cf76af6484e25c82b385c1) @@ -38,6 +38,11 @@ /** Constants used for sorting */ static final int SORT_BY_DATE_ASC = 0; static final int SORT_BY_DATE_DESC = 1; + static final int SORT_BY_USER_ASC = 2; + static final int SORT_BY_USER_DESC = 3; + static final int SORT_BY_TARGET_ASC = 4; + static final int SORT_BY_TARGET_DESC = 5; + /** * Records event of specified type in database. @@ -101,11 +106,12 @@ Date getOldestEventDate(); /** Used for displaying paged lists of events */ - List getEventsForTablesorter(int page, int size, int sorting, String searchString, Date startDate, + List getEventsForTablesorter(int page, int size, int sorting, String searchUser, String searchTarget, + String searchRemarks, Date startDate, Date endDate, String area, Integer typeId); + + int countEventsWithRestrictions(String searchUser, String searchTarget, String searchRemarks, Date startDate, Date endDate, String area, Integer typeId); - int countEventsWithRestrictions(String searchString, Date startDate, Date endDate, String area, Integer typeId); - /* ***************************** Helper methods used by tools to keep the audit entries consistent *****************/ void logChangeLearnerContent(Long learnerUserId, String learnerUserLogin, Long toolContentId, String originalText, String newText); Index: lams_common/src/java/org/lamsfoundation/lams/logevent/service/LogEventService.java =================================================================== diff -u -r62aaf160878735888d077bf28fac3c1989bb8fbd -r8e2e72a36d57288787cf76af6484e25c82b385c1 --- lams_common/src/java/org/lamsfoundation/lams/logevent/service/LogEventService.java (.../LogEventService.java) (revision 62aaf160878735888d077bf28fac3c1989bb8fbd) +++ lams_common/src/java/org/lamsfoundation/lams/logevent/service/LogEventService.java (.../LogEventService.java) (revision 8e2e72a36d57288787cf76af6484e25c82b385c1) @@ -136,15 +136,17 @@ } @Override - public List getEventsForTablesorter(int page, int size, int sorting, String searchString, Date startDate, - Date endDate, String area, Integer typeId) { - return logEventDAO.getEventsForTablesorter(page, size, sorting, searchString, startDate, endDate, area, typeId); + public List getEventsForTablesorter(int page, int size, int sorting, String searchUser, + String searchTarget, String searchRemarks, Date startDate, Date endDate, String area, Integer typeId) { + return logEventDAO.getEventsForTablesorter(page, size, sorting, searchUser, searchTarget, searchRemarks, + startDate, endDate, area, typeId); } @Override - public int countEventsWithRestrictions(String searchString, Date startDate, Date endDate, String area, - Integer typeId) { - return logEventDAO.countEventsWithRestrictions(searchString, startDate, endDate, area, typeId); + public int countEventsWithRestrictions(String searchUser, String searchTarget, String searchRemarks, Date startDate, + Date endDate, String area, Integer typeId) { + return logEventDAO.countEventsWithRestrictions(searchUser, searchTarget, searchRemarks, startDate, endDate, + area, typeId); } // ********************** Helper methods used by tools to keep the audit entries consistent *****************