[Ils-source] r1359 - in trunk: src/com/resolutions/ils/data webapp

[scribe at crosswire.org]

Author: scribe
Date: 2015-12-03 18:23:38 -0700 (Thu, 03 Dec 2015)
New Revision: 1359

Modified:
   trunk/src/com/resolutions/ils/data/UserProfile.java
   trunk/webapp/report_recurrent_training.jsp
Log:
Fairly stable release of recurrent training report


Modified: trunk/src/com/resolutions/ils/data/UserProfile.java
===================================================================
--- trunk/src/com/resolutions/ils/data/UserProfile.java	2015-12-01 21:37:47 UTC (rev 1358)
+++ trunk/src/com/resolutions/ils/data/UserProfile.java	2015-12-04 01:23:38 UTC (rev 1359)
@@ -75,7 +75,10 @@
 	static String COUNTWOULDBEDELETES = "SELECT COUNT(*) WOULDBE FROM ("+SELECTOLDIDS+") TMPTABLE";
 	static String LOOKUPUSERGROUPS = "SELECT * FROM ILSGROUP T1, USERGROUP T2 WHERE T1.ILSGROUPID=T2.ILSGROUPID AND T2.USERPRID={USERPRID} AND T1.COMPANYID={COMPANYID} AND T2.COMPANYID={COMPANYID}";
 
-	static String RECURRENT_TRAINING_REPORT = "SELECT DISTINCT CASS.USERPRID, CASS.COURSID, UP.USERPRLNAME, UP.USERPRFNAME, UP.USERPRNUM, C.COURSNAME, CA.CACOMPLETEDATE, CASE C.COURSPASSEXPDAYS WHEN 0 THEN CA.CAEXPIREDON ELSE COALESCE(CA.CAEXPIREDON, CA.CACOMPLETEDATE+C.COURSPASSEXPDAYS) END AS CAEXPIREDON, DATEDIFF(day, CA.CAEXPIREDON, CURRENT_TIMESTAMP) as DAYSEXPIRED, CASE C.COURSPASSEXPDAYS WHEN 0 THEN NULL ELSE C.COURSPASSEXPDAYS - DATEDIFF(day, CA.CACOMPLETEDATE, CURRENT_TIMESTAMP) END as DAYSSHOULDEXPIRE from USERCOURSEASSIGNMENTSWITHGROUPS CASS LEFT JOIN COURSE C ON C.COURSID = CASS.COURSID LEFT JOIN COURSEATTEMPT CA ON CA.USERPRID=CASS.USERPRID AND CA.COURSID=CASS.COURSID INNER JOIN (SELECT USERPRID, COURSID, MAX(CACOMPLETEDATE) as max_date FROM COURSEATTEMPT GROUP BY USERPRID, COURSID)a on a.USERPRID = CA.USERPRID AND a.COURSID=CA.COURSID and a.max_date = CA.CACOMPLETEDATE LEFT JOIN USERPROFILE UP ON UP.COMPANYID=CASS.COMPANYID AND UP.USERPRID = CASS.USERPRID WHERE CASS.COMPANYID={COMPANYID} AND (DATEDIFF(day, CA.CAEXPIREDON, CURRENT_TIMESTAMP) >= 0 OR CASE C.COURSPASSEXPDAYS WHEN 0 THEN NULL ELSE C.COURSPASSEXPDAYS - DATEDIFF(day, CA.CACOMPLETEDATE, CURRENT_TIMESTAMP) END <={DAYSTILEXP}) AND UP.USERPRSTATUSID=" + STATUS_ACTIVE;
+	static String RECURRENT_TRAINING_REPORT = "SELECT DISTINCT CASS.USERPRID, CASS.COURSID, UP.USERPRLNAME, UP.USERPRFNAME, UP.USERPRNUM, "
+		+ " (SELECT MAX(GROUPNAME) FROM USERGROUP UG JOIN ILSGROUP IG ON UG.ILSGROUPID=IG.ILSGROUPID AND UG.USERPRID=UP.USERPRID AND GROUPTYPEID=2) WORKGROUP," 
+		+ " (SELECT MAX(GROUPNAME) FROM USERGROUP UG JOIN ILSGROUP IG ON UG.ILSGROUPID=IG.ILSGROUPID AND UG.USERPRID=UP.USERPRID AND GROUPTYPEID=1) ROLE, "
+		+ "C.COURSNAME, CA.CACOMPLETEDATE, CASE C.COURSPASSEXPDAYS WHEN 0 THEN CA.CAEXPIREDON ELSE COALESCE(CA.CAEXPIREDON, CA.CACOMPLETEDATE+C.COURSPASSEXPDAYS) END AS CAEXPIREDON, DATEDIFF(day, CA.CAEXPIREDON, CURRENT_TIMESTAMP) as DAYSEXPIRED, CASE C.COURSPASSEXPDAYS WHEN 0 THEN NULL ELSE C.COURSPASSEXPDAYS - DATEDIFF(day, CA.CACOMPLETEDATE, CURRENT_TIMESTAMP) END as DAYSSHOULDEXPIRE from USERCOURSEASSIGNMENTSWITHGROUPS CASS LEFT JOIN COURSE C ON C.COURSID = CASS.COURSID LEFT JOIN COURSEATTEMPT CA ON CA.USERPRID=CASS.USERPRID AND CA.COURSID=CASS.COURSID INNER JOIN (SELECT USERPRID, COURSID, MAX(CACOMPLETEDATE) as max_date FROM COURSEATTEMPT GROUP BY USERPRID, COURSID)a on a.USERPRID = CA.USERPRID AND a.COURSID=CA.COURSID and a.max_date = CA.CACOMPLETEDATE LEFT JOIN USERPROFILE UP ON UP.COMPANYID=CASS.COMPANYID AND UP.USERPRID = CASS.USERPRID WHERE CASS.COMPANYID={COMPANYID} AND (DATEDIFF(day, CA.CAEXPIREDON, CURRENT_TIMESTAMP) >= 0 OR CASE C.COURSPASSEXPDAYS WHEN 0 THEN NULL ELSE C.COURSPASSEXPDAYS - DATEDIFF(day, CA.CACOMPLETEDATE, CURRENT_TIMESTAMP) END <={DAYSTILEXP}) AND UP.USERPRSTATUSID=" + STATUS_ACTIVE;
 static String RECURRENT_TRAINING_REPORT_LIMIT_TO_MANAGER = " AND CASS.USERPRID IN (SELECT DISTINCT UG.USERPRID FROM MANAGERGROUP MG LEFT JOIN USERGROUP UG ON UG.COMPANYID = MG.COMPANYID AND UG.ILSGROUPID=MG.ILSGROUPID WHERE MG.COMPANYID={COMPANYID} AND MG.USERPRID={USERPRID}) AND (GROUPTYPEID <> 2 OR ILSGROUPID IN (SELECT ILSGROUPID FROM MANAGERGROUP WHERE COMPANYID={COMPANYID} AND USERPRID={USERPRID}))";
 
 
@@ -512,7 +515,12 @@
 			sql += RECURRENT_TRAINING_REPORT_LIMIT_TO_MANAGER;
 			retVal.setUserProfileID(limitToManagerID);
 		}
-		return retVal.getDataSet(sql + " OPTION (RECOMPILE)");
+		Vector<UserProfile> results = retVal.getDataSet(sql + " OPTION (RECOMPILE)");
+		for (UserProfile up: results) {
+			up.isRoleLoaded = true;
+			up.isWorkgroupLoaded = true;
+		}
+		return results;
 	}
 
     public UserProfile []getManagers() {

Modified: trunk/webapp/report_recurrent_training.jsp
===================================================================
--- trunk/webapp/report_recurrent_training.jsp	2015-12-01 21:37:47 UTC (rev 1358)
+++ trunk/webapp/report_recurrent_training.jsp	2015-12-04 01:23:38 UTC (rev 1359)
@@ -11,6 +11,7 @@
 <%@ page import="java.util.Collections" %>
  
 <%
+	Logger logger = Logger.getLogger(this.getClass());
     String statusMsg = "";
     String validError = "";
     int daysTilDue = 30;