[Ils-source] r1287 - in trunk: db/mssql src/com/resolutions/ils/data webapp/WEB-INF/lib

[scribe at crosswire.org]

Author: scribe
Date: 2015-01-06 15:56:47 -0700 (Tue, 06 Jan 2015)
New Revision: 1287

Added:
   trunk/db/mssql/upgrade171.1.sql
Modified:
   trunk/src/com/resolutions/ils/data/UserProfile.java
   trunk/webapp/WEB-INF/lib/ils.jar
Log:
added optimizations for student records report


Added: trunk/db/mssql/upgrade171.1.sql
===================================================================
--- trunk/db/mssql/upgrade171.1.sql	                        (rev 0)
+++ trunk/db/mssql/upgrade171.1.sql	2015-01-06 22:56:47 UTC (rev 1287)
@@ -0,0 +1,4 @@
+CREATE VIEW USERCOURSEASSIGNMENTS WITH SCHEMABINDING AS SELECT DISTINCT T2.COMPANYID, T2.USERPRID, COALESCE (T9.COURSID, T1.COURSID) AS COURSID FROM   dbo.USERGROUP AS T2 INNER JOIN dbo.COURSEGROUP AS T3 ON T3.COMPANYID = T2.COMPANYID AND T3.ILSGROUPID = T2.ILSGROUPID INNER JOIN dbo.COURSE AS T1 ON T1.COMPANYID = T3.COMPANYID AND T1.COURSID = T3.COURSID LEFT OUTER JOIN dbo.CURRICULUMCOURSE AS T5 ON T5.COMPANYID = T1.COMPANYID AND T5.CURRICULUMID = T1.COURSID AND T1.COURSISCURRICULUM = 'T' LEFT OUTER JOIN dbo.COURSE AS T9 ON T5.COMPANYID IS NOT NULL AND T9.COMPANYID = T5.COMPANYID AND T9.COURSID = T5.COURSID WHERE (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM IS NULL) AND (T9.COURSID IS NULL) AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM IS NULL) AND (T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISCURRICULUM <> 'T') AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NOT NULL) AND (T1.COURSISCURRICULUM <> 'T') AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') OR (T9.COURSID IS NULL) AND (T1.COURSISQUICKVIEW IS NULL OR T1.COURSISQUICKVIEW <> 'T') AND (T1.COURSID <> T9.COURSID) OR (T9.COURSID IS NOT NULL) AND (T9.COURSISQUICKVIEW IS NULL OR T9.COURSISQUICKVIEW <> 'T') AND (T1.COURSID <> T9.COURSID);
+
+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;
+

Modified: trunk/src/com/resolutions/ils/data/UserProfile.java
===================================================================
--- trunk/src/com/resolutions/ils/data/UserProfile.java	2014-12-12 23:51:51 UTC (rev 1286)
+++ trunk/src/com/resolutions/ils/data/UserProfile.java	2015-01-06 22:56:47 UTC (rev 1287)
@@ -257,6 +257,7 @@
     }
 
     
+/*
     static String USERCOURSESUMMARY=
     "SELECT UP2.USERPRID, UP2.USERPRLNAME, UP2.USERPRFNAME, UP2.USERPRNUM, UP2.USERPRADDR2, UP2.USERPRHIREDATE, " +
     " (SELECT MAX(GROUPNAME) FROM USERGROUP UG JOIN ILSGROUP IG ON UG.ILSGROUPID=IG.ILSGROUPID AND UG.USERPRID=UP2.USERPRID AND GROUPTYPEID=2) WORKGROUP," + 
@@ -277,7 +278,20 @@
     	" LEFT OUTER JOIN COURSEATTEMPT CA ON CA.COMPANYID=T1.COMPANYID AND CA.USERPRID=T1.USERPRID AND T1.COURSID=CA.COURSID AND CA.CAEXPIREDON IS NULL" +
     	") T2" +
     	" RIGHT JOIN USERPROFILE UP2 on UP2.USERPRID=T2.USERPRID AND UP2.COMPANYID={COMPANYID}";
+*/
 
+	static String USERCOURSESUMMARY =
+	"SELECT U.USERPRID, U.USERPRLNAME, U.USERPRFNAME, U.USERPRNUM, U.USERPRADDR2, U.USERPRHIREDATE,  " +
+	" (SELECT MAX(GROUPNAME) FROM USERGROUP UG JOIN ILSGROUP IG ON UG.ILSGROUPID=IG.ILSGROUPID AND UG.USERPRID=U.USERPRID AND GROUPTYPEID=2) WORKGROUP, " +
+	" (SELECT MAX(GROUPNAME) FROM USERGROUP UG JOIN ILSGROUP IG ON UG.ILSGROUPID=IG.ILSGROUPID AND UG.USERPRID=U.USERPRID AND GROUPTYPEID=1) ROLE, " +
+	" SUM(COALESCE(CA.CRS_COMPLETED, 0)) COURSESCOMPLETED, " +
+	" SUM(COALESCE(CA.CRS_OPEN, 1)) AS COURSESOPEN, " +
+	" SUM(COALESCE(CA.CRS_FAILED, 0)) COURSESFAILED, " +
+	" MAX(CA.CACOMPLETEDATE) COURSESLASTATTEMPT " +
+	" FROM USERPROFILE  U" +
+	" LEFT JOIN USERCOURSEASSIGNMENTS A ON A.COMPANYID=U.COMPANYID AND A.USERPRID=U.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";
+
 	static public Vector<UserProfile> getSearchUserProfiles(ILSSession session, int locID, int roleID,
                                                String lName, String empNum, int statusID, int accessLevel, Date hireStartDate, Date hireEndDate, int managerID, boolean preloadCourseData) {
 		return getSearchUserProfiles(session, locID != -1 ? new HashSet<Integer>(Arrays.asList(new Integer[] { locID })) : null, roleID != -1 ? new HashSet<Integer>(Arrays.asList(new Integer[] { roleID })) : null, lName, empNum, statusID, accessLevel, hireStartDate, hireEndDate, managerID, preloadCourseData);
@@ -355,7 +369,7 @@
 		String sql = null;
         if (preloadCourseData) {
 			sql = "SELECT UP2.USERPRID FROM " + tables + " WHERE " + where;
-			sql = USERCOURSESUMMARY + " WHERE UP2.USERPRID IN ("+sql+") GROUP BY UP2.USERPRID, UP2.USERPRLNAME, UP2.USERPRFNAME, UP2.USERPRNUM, UP2.USERPRADDR2, UP2.USERPRHIREDATE OPTION (RECOMPILE)";
+			sql = USERCOURSESUMMARY + " WHERE U.USERPRID IN ("+sql+") GROUP BY U.USERPRID, U.USERPRLNAME, U.USERPRFNAME, U.USERPRNUM, U.USERPRADDR2, U.USERPRHIREDATE OPTION (RECOMPILE)";
         }
         else sql = "SELECT UP2.USERPRID, UP2.USERPRLNAME, UP2.USERPRFNAME, UP2.USERPRNUM, UP2.USERPRADDR2, UP2.USERPRHIREDATE, " +
                         " (SELECT MAX(GROUPNAME) FROM USERGROUP UG JOIN ILSGROUP IG ON UG.ILSGROUPID=IG.ILSGROUPID AND UG.USERPRID=UP2.USERPRID AND GROUPTYPEID=2) WORKGROUP," + 

Modified: trunk/webapp/WEB-INF/lib/ils.jar
===================================================================
(Binary files differ)