[Ils-source] r1288 - in trunk: db/mssql src/com/resolutions/ils/data webapp
scribe at crosswire.org
scribe at crosswire.org
Tue Jan 13 00:21:07 MST 2015
Author: scribe
Date: 2015-01-13 00:21:07 -0700 (Tue, 13 Jan 2015)
New Revision: 1288
Modified:
trunk/db/mssql/upgrade171.1.sql
trunk/src/com/resolutions/ils/data/CourseAttempt.java
trunk/src/com/resolutions/ils/data/UserProfile.java
trunk/webapp/admin_studentrecords.jsp
trunk/webapp/report_pending_summary.jsp
Log:
Optimizations for group summary and course-centric version of student records
Modified: trunk/db/mssql/upgrade171.1.sql
===================================================================
--- trunk/db/mssql/upgrade171.1.sql 2015-01-06 22:56:47 UTC (rev 1287)
+++ trunk/db/mssql/upgrade171.1.sql 2015-01-13 07:21:07 UTC (rev 1288)
@@ -2,3 +2,6 @@
CREATE VIEW COURSEATTEMPTSTATUS WITH SCHEMABINDING AS SELECT CA.COMPANYID, CA.USERPRID, CA.COURSID, CA.COURSATTID, CA.CACOMPLETEDATE, CA.CAEXPIREDON, COALESCE(CA.CASTATUSID, 0) AS CASTATUSID, CASE COALESCE(CA.CASTATUSID, 0) WHEN 3 THEN 1 WHEN 0 THEN 1 ELSE 0 END AS CRS_OPEN, CASE COALESCE(CA.CASTATUSID, 0) WHEN 1 THEN 1 ELSE 0 END AS CRS_COMPLETED, CASE COALESCE(CA.CASTATUSID, 0) WHEN 2 THEN 1 ELSE 0 END AS CRS_FAILED FROM DBO.COURSEATTEMPT CA;
+CREATE NONCLUSTERED INDEX CAEXPIREDONINDEX ON COURSEATTEMPT (COMPANYID,CAEXPIREDON) INCLUDE (USERPRID,COURSID,CACOMPLETEDATE,CASTATUSID)
+
+
Modified: trunk/src/com/resolutions/ils/data/CourseAttempt.java
===================================================================
--- trunk/src/com/resolutions/ils/data/CourseAttempt.java 2015-01-06 22:56:47 UTC (rev 1287)
+++ trunk/src/com/resolutions/ils/data/CourseAttempt.java 2015-01-13 07:21:07 UTC (rev 1288)
@@ -311,7 +311,10 @@
static public Vector getSearchUserCourseAttempts(ILSSession session, Set<Integer> locIDs, Set<Integer> roleIDs,
String lName, String empNum, int statusID, int accessLevel, Date hireStartDate, Date hireEndDate,
int managerID, int courseID, int complianceID, Date sDate, Date eDate, boolean reqApprov) {
- String sql = "SELECT * FROM COURSEATTEMPT CA, USERPROFILE UP, COURSE CR WHERE CA.USERPRID=UP.USERPRID AND CA.COURSID=CR.COURSID AND UP.COMPANYID={COMPANYID} AND UP.USERPRSTATUSID={USERPRSTATUSID}";
+ String sql = "SELECT *, "+
+ " (SELECT MAX(GROUPNAME) FROM USERGROUP UG JOIN ILSGROUP IG ON UG.COMPANYID=IG.COMPANYID AND UG.ILSGROUPID=IG.ILSGROUPID WHERE UG.USERPRID=UP.USERPRID AND GROUPTYPEID=2) WORKGROUP, " +
+ " (SELECT MAX(GROUPNAME) FROM USERGROUP UG JOIN ILSGROUP IG ON UG.COMPANYID=IG.COMPANYID AND UG.ILSGROUPID=IG.ILSGROUPID WHERE UG.USERPRID=UP.USERPRID AND GROUPTYPEID=1) ROLE " +
+ " FROM COURSEATTEMPT CA, USERPROFILE UP, COURSE CR WHERE CA.USERPRID=UP.USERPRID AND CA.COURSID=CR.COURSID AND UP.COMPANYID={COMPANYID} AND UP.USERPRSTATUSID={USERPRSTATUSID}";
CourseAttempt retVal = new CourseAttempt();
if (locIDs != null) {
sql += " AND exists (select * from usergroup ug where ug.userprid=up.userprid and ilsgroupid in (";
Modified: trunk/src/com/resolutions/ils/data/UserProfile.java
===================================================================
--- trunk/src/com/resolutions/ils/data/UserProfile.java 2015-01-06 22:56:47 UTC (rev 1287)
+++ trunk/src/com/resolutions/ils/data/UserProfile.java 2015-01-13 07:21:07 UTC (rev 1288)
@@ -391,6 +391,20 @@
static String USERGROUPCOURSESUMMARY=
+ "SELECT GRP.ILSGROUPID, GRP.GROUPNAME, " +
+ "COUNT(DISTINCT UP.USERPRID) AS USERS, " +
+ "SUM(COALESCE(CA.CRS_COMPLETED, 0)) SUM_COMPLETED, " +
+ "SUM(CASE WHEN A.USERPRID IS NULL THEN 0 ELSE COALESCE(CA.CRS_OPEN, 1) END) AS SUM_OPEN, " +
+ "SUM(COALESCE(CA.CRS_FAILED, 0)) SUM_FAILED, " +
+ "MAX(CA.CACOMPLETEDATE) LAST_COMPLETED " +
+ "FROM ILSGROUP GRP " +
+ "LEFT JOIN USERGROUP UG ON UG.COMPANYID=GRP.COMPANYID AND UG.ILSGROUPID=GRP.ILSGROUPID " +
+ "LEFT JOIN USERPROFILE UP on UP.COMPANYID=GRP.COMPANYID AND UP.USERPRID=UG.USERPRID " +
+ "LEFT JOIN USERCOURSEASSIGNMENTS A ON A.COMPANYID=GRP.COMPANYID AND A.USERPRID=UG.USERPRID " +
+ "LEFT OUTER JOIN COURSEATTEMPTSTATUS CA ON CA.COMPANYID = A.COMPANYID AND CA.USERPRID = A.USERPRID AND A.COURSID = CA.COURSID AND CA.CAEXPIREDON IS NULL " +
+ "WHERE GRP.COMPANYID={COMPANYID}";
+ /*
+
"SELECT GRP.ILSGROUPID, GRP.GROUPNAME, SUM(crs_completed) SUM_COMPLETED, SUM(CRS_OPEN) AS SUM_OPEN, SUM(CRS_FAILED) SUM_FAILED, MAX(cacompletedate) LAST_COMPLETED" +
" FROM" +
" USERGROUP UG" +
@@ -412,6 +426,7 @@
" ) T1 on T1.COMPANYID=UP2.COMPANYID AND T1.USERPRID=UP2.USERPRID" +
" LEFT JOIN EMPTITLE ET ON ET.COMPANYID=UP2.COMPANYID AND UP2.USERPREMPTITLEID=ET.EMPTITLEID" +
" WHERE UG.COMPANYID={COMPANYID}";
+ */
// default is byLocation
@@ -423,7 +438,7 @@
retVal.setCompanyID(session.getCompanyID());
if (statusID > -1) {
- where += " AND UP2.USERPRSTATUSID = "+statusID;
+ where += " AND UP.USERPRSTATUSID = "+statusID;
}
/*
@@ -438,35 +453,35 @@
*/
if (managerID > -1) {
- where += " AND EXISTS (SELECT 1 FROM USERGROUP WG WHERE WG.USERPRID=UP2.USERPRID AND WG.ILSGROUPID IN (SELECT ILSGROUPID FROM MANAGERGROUP WHERE USERPRID=" + managerID + "))";
+ where += " AND EXISTS (SELECT 1 FROM USERGROUP WG WHERE WG.USERPRID=UP.USERPRID AND WG.ILSGROUPID IN (SELECT ILSGROUPID FROM MANAGERGROUP WHERE USERPRID=" + managerID + "))";
}
if (accessLevel > -1) {
- where += " AND UP2.USERPRACCESSLEVEL=" + accessLevel;
+ where += " AND UP.USERPRACCESSLEVEL=" + accessLevel;
}
if (hireStartDate != null) {
- where += " AND UP2.USERPRHIREDATE >= {HIRESTARTDATE}";
+ where += " AND UP.USERPRHIREDATE >= {HIRESTARTDATE}";
retVal.setDateValue("HIRESTARTDATE", hireStartDate);
}
if (hireEndDate != null) {
- where += " AND UP2.USERPRHIREDATE <= {HIREENDDATE}";
+ where += " AND UP.USERPRHIREDATE <= {HIREENDDATE}";
retVal.setDateValue("HIREENDDATE", hireEndDate);
}
if ((lName != null) && (lName.length() > 0)) {
- where += " AND upper(UP2.USERPRLNAME) like {USERPRLNAME}";
+ where += " AND upper(UP.USERPRLNAME) like {USERPRLNAME}";
retVal.setUserProfileLastName(lName.toUpperCase() + "%");
}
if ((empNum != null) && (empNum.length() > 0)) {
- where += " AND upper(UP2.USERPRNUM) like {USERPRNUM}";
+ where += " AND upper(UP.USERPRNUM) like {USERPRNUM}";
retVal.setUserProfileNum(empNum.toUpperCase() + "%");
}
sql += where;
sql += " AND GRP.GROUPTYPEID=" + ((byRole) ? Group.GROUPTYPE_ROLE : Group.GROUPTYPE_LOCATION);
- sql += " GROUP BY GRP.ILSGROUPID, GRP.GROUPNAME";
+ sql += " GROUP BY GRP.ILSGROUPID, GRP.GROUPNAME ORDER BY GROUPNAME";
return retVal.getDataSet(sql + " OPTION (RECOMPILE)");
}
Modified: trunk/webapp/admin_studentrecords.jsp
===================================================================
--- trunk/webapp/admin_studentrecords.jsp 2015-01-06 22:56:47 UTC (rev 1287)
+++ trunk/webapp/admin_studentrecords.jsp 2015-01-13 07:21:07 UTC (rev 1288)
@@ -187,10 +187,10 @@
cc.copyFrom(ca);
UserProfile up = new UserProfile();
up.copyFrom(ca);
- Vector tmpg = Group.getUserGroups(ilsSession, Group.GROUPTYPE_LOCATION, up.getUserProfileID());
- String upLocation = ((tmpg != null) && (tmpg.size()>0)) ? ((Group)tmpg.get(0)).getGroupName() : "";
- tmpg = Group.getUserGroups(ilsSession, Group.GROUPTYPE_ROLE, up.getUserProfileID());
- String upRole = ((tmpg != null) && (tmpg.size()>0)) ? ((Group)tmpg.get(0)).getGroupName() : "";
+ //Vector tmpg = Group.getUserGroups(ilsSession, Group.GROUPTYPE_LOCATION, up.getUserProfileID());
+ String upLocation = ca.getStringValue("WORKGROUP"); //((tmpg != null) && (tmpg.size()>0)) ? ((Group)tmpg.get(0)).getGroupName() : "";
+ //tmpg = Group.getUserGroups(ilsSession, Group.GROUPTYPE_ROLE, up.getUserProfileID());
+ String upRole = ca.getStringValue("ROLE"); //((tmpg != null) && (tmpg.size()>0)) ? ((Group)tmpg.get(0)).getGroupName() : "";
Date tDate = ca.getCourseAttemptStartDate();
String sDate = (tDate != null) ? df.format(tDate) : "";
tDate = ca.getCourseAttemptCompleteDate();
Modified: trunk/webapp/report_pending_summary.jsp
===================================================================
--- trunk/webapp/report_pending_summary.jsp 2015-01-06 22:56:47 UTC (rev 1287)
+++ trunk/webapp/report_pending_summary.jsp 2015-01-13 07:21:07 UTC (rev 1288)
@@ -137,6 +137,9 @@
if ("grpname".equals(sortColumn)) {
return ((UserProfile)o1).getStringValue("GROUPNAME").compareTo(((UserProfile)o2).getStringValue("GROUPNAME"));
}
+ if ("grpcount".equals(sortColumn)) {
+ return ((UserProfile)o2).getIntValue("USERS") - ((UserProfile)o1).getIntValue("USERS");
+ }
if ("completed".equals(sortColumn)) {
return ((UserProfile)o2).getIntValue("SUM_COMPLETED") - ((UserProfile)o1).getIntValue("SUM_COMPLETED");
}
@@ -266,6 +269,7 @@
<% } else { %>
<t:t>Workgroup</t:t>
<% } %></a></th>
+ <th><a href="report_pending_summary.jsp?sort=grpcount">Members</a></th>
<th><a href="report_pending_summary.jsp?sort=completed">Completed Courses</a></th>
<th><a href="report_pending_summary.jsp?sort=open">Open Courses</a></th>
<th><a href="report_pending_summary.jsp?sort=failed">Failed Courses</a></th>
@@ -280,7 +284,7 @@
<% } else { %>
<t:t>Workgroup</t:t>
<% }
- out.print(",Completed Courses,Open Courses,Failed Courses,Last Course Completed\n");
+ out.print(",Members,Completed Courses,Open Courses,Failed Courses,Last Course Completed\n");
}
int begin = (noHTML) ? 0 : pageNum * 25;
int end = (noHTML) ? userProfiles.size() : begin + 25;
@@ -293,6 +297,7 @@
%>
<tr>
<td><%=up.getStringValue("GROUPNAME")%></td>
+ <td width="60" align="right"><%=up.getIntValue("USERS")%></td>
<td width="60" align="right"><%=up.getIntValue("SUM_COMPLETED")%></td>
<td width="60" align="right"><%=up.getIntValue("SUM_OPEN")%></td>
<td width="60" align="right"><%=up.getIntValue("SUM_FAILED")%></td>
@@ -303,6 +308,7 @@
else {
out.print(
"\""+up.getStringValue("GROUPNAME").replaceAll("\"", "\"\"") + "\"," +
+ up.getIntValue("USERS") + "," +
up.getIntValue("SUM_COMPLETED") + "," +
up.getIntValue("SUM_OPEN") + "," +
up.getIntValue("SUM_FAILED") + "," +
More information about the Ils-source
mailing list