[Ils-source] r1305 - in branches/1.6: db/mssql src/com/resolutions/ils src/com/resolutions/ils/data webapp webapp/META-INF webapp/WEB-INF/classes webapp/WEB-INF/lib
scribe at crosswire.org
scribe at crosswire.org
Fri Apr 3 01:26:29 MST 2015
Author: scribe
Date: 2015-04-03 01:26:29 -0700 (Fri, 03 Apr 2015)
New Revision: 1305
Removed:
branches/1.6/db/mssql/upgrade164.1
Modified:
branches/1.6/src/com/resolutions/ils/Utils.java
branches/1.6/src/com/resolutions/ils/data/CourseAttempt.java
branches/1.6/src/com/resolutions/ils/data/UserProfile.java
branches/1.6/webapp/META-INF/context.xml
branches/1.6/webapp/SQL.jsp
branches/1.6/webapp/WEB-INF/classes/versions.properties
branches/1.6/webapp/WEB-INF/lib/ils.jar
branches/1.6/webapp/admin_profilemanagement.jsp
branches/1.6/webapp/admin_studentrecords.jsp
branches/1.6/webapp/login.jsp
branches/1.6/webapp/report_pending_summary.jsp
Log:
Residual changes to complete absolute sync with trunk. v1.80
Deleted: branches/1.6/db/mssql/upgrade164.1
===================================================================
--- branches/1.6/db/mssql/upgrade164.1 2015-04-03 07:31:38 UTC (rev 1304)
+++ branches/1.6/db/mssql/upgrade164.1 2015-04-03 08:26:29 UTC (rev 1305)
@@ -1,3 +0,0 @@
-alter table COURSEATTEMPT alter column CAFORCEPASSREASON nvarchar(80);
-alter table COURSEATTEMPT alter column CAEXPREASON nvarchar(80);
-
Modified: branches/1.6/src/com/resolutions/ils/Utils.java
===================================================================
--- branches/1.6/src/com/resolutions/ils/Utils.java 2015-04-03 07:31:38 UTC (rev 1304)
+++ branches/1.6/src/com/resolutions/ils/Utils.java 2015-04-03 08:26:29 UTC (rev 1305)
@@ -267,11 +267,11 @@
}
catch (Exception e) {
- logger.error("Problem sending email with server: " + server + "; userid: " + userID + "; [" + subject + "] to: " + to, e);
+ logger.error("Problem sending email [" + subject + "] to: " + to, e);
}
}
catch (Exception e) {
- logger.error("Problem sending email with server: " + server + "; userid: " + userID + "; [" + subject + "] to: " + to, e);
+ logger.error("Problem sending email [" + subject + "] to: " + to, e);
}
finally {
if (!leaveConnectionOpen) {
Modified: branches/1.6/src/com/resolutions/ils/data/CourseAttempt.java
===================================================================
--- branches/1.6/src/com/resolutions/ils/data/CourseAttempt.java 2015-04-03 07:31:38 UTC (rev 1304)
+++ branches/1.6/src/com/resolutions/ils/data/CourseAttempt.java 2015-04-03 08:26:29 UTC (rev 1305)
@@ -14,6 +14,9 @@
import java.util.HashSet;
import java.util.Date;
import java.util.TimeZone;
+import java.util.Calendar;
+import java.util.GregorianCalendar;
+import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import javax.servlet.ServletContext;
@@ -46,7 +49,7 @@
" AND CA2.CASTATUSID=1" +
" AND CA2.CAEXPIREDON IS NULL" +
" AND CC.COURSPASSEXPDAYS > 0" +
- " AND CA2.CACOMPLETEDATE <= (current_timestamp - CC.COURSPASSEXPDAYS - COALESCE(CC.COURSREASBEFDAYS,0))";
+ " AND CA2.CACOMPLETEDATE <= (current_timestamp - (CC.COURSPASSEXPDAYS - COALESCE(CC.COURSREASBEFDAYS,0)))";
static String LOOKUPEXPCOURSE = LOOKUPEXP + " AND CA2.COURSID={COURSID}";
@@ -63,7 +66,7 @@
" AND CA2.CASTATUSID=1" +
" AND CA2.CAEXPIREDON IS NOT NULL" +
" AND CC.COURSPASSEXPDAYS > 0" +
- " AND CA2.CACOMPLETEDATE <= (current_timestamp - CC.COURSPASSEXPDAYS - {WARNDAYS})";
+ " AND CA2.CACOMPLETEDATE <= (current_timestamp - (CC.COURSPASSEXPDAYS - {WARNDAYS}))";
static String LOOKUPPENDINGEXPCOURSE = LOOKUPPENDINGEXP + " AND CA2.COURSID={COURSID}";
@@ -113,7 +116,8 @@
* @param email, pass the HTTP session if you wish to send email, otherwise pass null
* @return
*/
- static public int revokeAllExpired(int companyID, int courseID, ServletContext context, boolean sendEmail) {
+static public synchronized int revokeAllExpired(int companyID, int courseID, ServletContext context, boolean sendEmail) {
+System.out.println("**** revokeAllExpired: start");
Properties sysConfig = Utils.getSysConfig(context, companyID);
CourseAttempt query = new CourseAttempt();
query.setCompanyID(companyID);
@@ -121,28 +125,35 @@
int count = 0;
if (courseID > 0) {
query.setCourseAttemptCourseID(courseID);
- if (sendEmail) results = query.getDataSet(LOOKUPCOURSECOMPLETEEXPCOURSE);
+ if (sendEmail) results = query.getDataSet(LOOKUPCOURSECOMPLETEEXPCOURSE + " AND CC.COURSISACTIVE='T'");
count = query.executeSQL(REVOKEEXPCOURSE);
}
else {
- if (sendEmail) results = query.getDataSet(LOOKUPCOURSECOMPLETEEXP);
+ if (sendEmail) results = query.getDataSet(LOOKUPCOURSECOMPLETEEXP + " AND CC.COURSISACTIVE='T'");
count = query.executeSQL(REVOKEEXP);
}
+
+System.out.println("**** revokeAllExpired: emailSet.size(): " + (results != null ? results.size() : 0));
+System.out.println("**** revokeAllExpired: revoke.size(): " + count);
+
// Send Emails
if (results != null) {
String subjectTemplate = sysConfig.getProperty("EmailTemplate5Subject");
String bodyTemplate = sysConfig.getProperty("EmailTemplate5Body");
+ String from = sysConfig.getProperty("SendMailFrom");
for (Object o: results) {
CourseAttempt ca = (CourseAttempt)o;
try {
+ String to = ca.getStringValue("USERPREMAIL");
+System.out.println("**** revokeAllExpired: sending email to: " + to);
java.util.Hashtable params = new java.util.Hashtable();
params.put("SITEURL", sysConfig.getProperty("SiteURL", "your training site"));
params.putAll(ca.getData());
String subject = Utils.replaceParams(subjectTemplate, params);
String body = Utils.replaceParams(bodyTemplate, params);
body += sysConfig.getProperty("SendMailSig", "");
- Utils.sendEmail(sysConfig, sysConfig.getProperty("SendMailFrom"), ca.getStringValue("USERPREMAIL"), subject, body, true);
+ Utils.sendEmail(sysConfig, from, to, subject, body, true);
}
catch (Exception e) {
e.printStackTrace();
@@ -150,9 +161,11 @@
}
Utils.closeMailConnection();
}
+System.out.println("**** revokeAllExpired: end");
return count;
- }
+}
+
/********************************
*
* @param session
@@ -170,7 +183,7 @@
* @param email, pass the HTTP session if you wish to send email, otherwise pass null
* @return
*/
- static public int warnAllPendingExpired(int companyID, int courseID, ServletContext context, int warningNumber) {
+ static public synchronized int warnAllPendingExpired(int companyID, int courseID, ServletContext context, int warningNumber) {
Properties sysConfig = Utils.getSysConfig(context, companyID);
CourseAttempt query = new CourseAttempt();
query.setCompanyID(companyID);
@@ -193,13 +206,13 @@
if (courseID > 0) {
query.setCourseAttemptCourseID(courseID);
- results = query.getDataSet(LOOKUPCOURSECOMPLETEPENDINGEXPCOURSE + "AND CAWARNEDON"+warningNumber+" IS NULL");
- String WARNEXPCOURSE = WARN + " WHERE COURSATTID IN (" + LOOKUPPENDINGEXPCOURSE + ")" + "AND CAWARNEDON"+warningNumber+" IS NULL";
+ results = query.getDataSet(LOOKUPCOURSECOMPLETEPENDINGEXPCOURSE + "AND CAWARNEDON"+warningNumber+" IS NULL AND CC.COURSISACTIVE='T'");
+ String WARNEXPCOURSE = WARN + " WHERE COURSATTID IN (" + LOOKUPPENDINGEXPCOURSE + " AND CC.COURSISACTIVE='T')" + " AND CAWARNEDON"+warningNumber+" IS NULL";
count = query.executeSQL(WARNEXPCOURSE);
}
else {
- results = query.getDataSet(LOOKUPCOURSECOMPLETEPENDINGEXP);
- String WARNEXP = WARN + " WHERE COURSATTID IN (" + LOOKUPPENDINGEXP + ")" + "AND CAWARNEDON"+warningNumber+" IS NULL";
+ results = query.getDataSet(LOOKUPCOURSECOMPLETEPENDINGEXP + " AND CC.COURSISACTIVE='T' AND CAWARNEDON"+warningNumber+" IS NULL");
+ String WARNEXP = WARN + " WHERE COURSATTID IN (" + LOOKUPPENDINGEXP + " AND CC.COURSISACTIVE='T')" + " AND CAWARNEDON"+warningNumber+" IS NULL";
count = query.executeSQL(WARNEXP);
}
@@ -252,6 +265,44 @@
return (rows.size() > 0) ? (CourseAttempt) rows.get(0) : null;
}
+ static public boolean hasTime(Date date) {
+ if (date == null) {
+ return false;
+ }
+ Calendar c = Calendar.getInstance();
+ c.setTime(date);
+ if (c.get(Calendar.HOUR_OF_DAY) > 0) {
+ return true;
+ }
+ if (c.get(Calendar.MINUTE) > 0) {
+ return true;
+ }
+ if (c.get(Calendar.SECOND) > 0) {
+ return true;
+ }
+ if (c.get(Calendar.MILLISECOND) > 0) {
+ return true;
+ }
+ return false;
+ }
+
+ static public Date makeEndOfDay(Date eDate) {
+ // don't change if time is already specified
+ if (hasTime(eDate)) return eDate;
+
+ Calendar c = Calendar.getInstance();
+ c.setTime(eDate);
+ c.set(Calendar.HOUR_OF_DAY, 23);
+ c.set(Calendar.MINUTE, 59);
+ c.set(Calendar.SECOND, 59);
+ c.set(Calendar.MILLISECOND, 999);
+
+ return c.getTime();
+
+ }
+
+
+
static public Vector getSearchUserCourseAttempts(ILSSession session, int locID, int roleID,
String lName, String empNum, int statusID, int accessLevel, Date hireStartDate, Date hireEndDate,
int managerID, int courseID, int complianceID, Date sDate, Date eDate, boolean reqApprov) {
@@ -260,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 (";
@@ -329,7 +383,7 @@
}
if (eDate != null) {
sql += " AND CA.CACOMPLETEDATE<={CAENDDATE}";
- retVal.setTSValue("CAENDDATE", eDate);
+ retVal.setTSValue("CAENDDATE", makeEndOfDay(eDate));
}
@@ -408,6 +462,25 @@
}
public void setCourseAttemptStage(String val) {
+ if (val == null || val.trim().length() == 0) {
+ try {
+ String oldValue = getCourseAttemptStage();
+ if (oldValue != null && oldValue.length() > 0) {
+ throw new Exception("Refusing to set CourseAtteptStage to empty when previous value was not empty.");
+ }
+ }
+ catch (Exception e) { e.printStackTrace(); return; }
+ return;
+ }
+ if ("".equals(getINIValue(val, "lesson_location"))) {
+ String oldValue = getCourseAttemptStage();
+ if (oldValue != null && oldValue.length() > 0) {
+ oldValue = getINIValue(oldValue, "lesson_location");
+ if (oldValue != null && oldValue.length() > 0 && !"0".equals(oldValue)) {
+ val = CourseAttempt.setINIValue(val, "lesson_location", oldValue);
+ }
+ }
+ }
setValue("CASTAGE", val);
}
@@ -524,25 +597,28 @@
StringBuffer params = new StringBuffer();
UserProfile user = UserProfile.getUserProfile(session, getCourseAttemptUserProfileID());
Course course = Course.getCourse(session, getCourseAttemptCourseID());
- params.append("USERDATA=").append(HTTPUtils.canonize(user.getStringValue("USERDATA")));
+ params.append("USERDATA=").append(URLEncoder.encode(user.getStringValue("USERDATA"), "UTF-8"));
params.append("&COMPANYID=").append(getCompanyID());
params.append("&USERPRID=").append(user.getUserProfileID());
- params.append("&USERPRNUM=").append(HTTPUtils.canonize(user.getUserProfileNum()));
+ params.append("&USERPRNUM=").append(URLEncoder.encode(user.getUserProfileNum(), "UTF-8"));
params.append("&COURSID=").append(course.getCourseID());
- params.append("&COURSNUM=").append(HTTPUtils.canonize(course.getCourseNum()));
- params.append("&COURSNAME=").append(HTTPUtils.canonize(course.getCourseName()));
- params.append("&CASTARTDATE=").append(HTTPUtils.canonize(getCourseAttemptStartDate().toString()));
- params.append("&CACOMPLETEDATE=").append(HTTPUtils.canonize(getCourseAttemptCompleteDate().toString()));
+ params.append("&COURSNUM=").append(URLEncoder.encode(course.getCourseNum(), "UTF-8"));
+ params.append("&COURSNAME=").append(URLEncoder.encode(course.getCourseName(), "UTF-8"));
+ params.append("&CASTARTDATE=").append(URLEncoder.encode(getCourseAttemptStartDate().toString(), "UTF-8"));
+ params.append("&CACOMPLETEDATE=").append(URLEncoder.encode(getCourseAttemptCompleteDate().toString(), "UTF-8"));
params.append("&CASCORE=").append(getCourseAttemptScore());
params.append("&CASTATUSID=").append(getCourseAttemptStatusID());
params.append("&CASTATUSDESC=").append(passedFailed);
- params.append("&CASTAGE=").append(HTTPUtils.canonize(getCourseAttemptStage()));
+ params.append("&CASTAGE=").append(URLEncoder.encode(getCourseAttemptStage(), "UTF-8"));
params.append("&CAISMGRAPPRVL=").append(isCourseAttemptManagerApproved());
params.append("&COURSATTID=").append(getCourseAttemptID());
// deprecated. for backward compat.
params.append("&COURSEATTEMPTID=").append(getCourseAttemptID());
- params.append("&COURSENAME=").append(HTTPUtils.canonize(course.getCourseName()));
+ params.append("&COURSENAME=").append(URLEncoder.encode(course.getCourseName(), "UTF-8"));
+logger.debug("POSTING to callback URL: " + callbackURL);
+logger.debug("POSTING data: " + params);
result = HTTPUtils.postURL(callbackURL, params.toString());
+logger.debug("POSTING result: " + result.toString());
}
catch (Exception e) { e.printStackTrace(); }
if (!"OK".equals(result.toString().trim())) {
@@ -567,6 +643,7 @@
}
if (eDate != null) {
sql += " AND CA.CACOMPLETEDATE<{CAENDDATE}";
+// retVal.setTSValue("CAENDDATE", makeEndOfDay(eDate)); // TODO: should probably use makeEndOfDay and change above to <=, but don't want to change any other logic right now. Hunt down callers
retVal.setTSValue("CAENDDATE", eDate);
}
@@ -650,4 +727,67 @@
}
return count;
}
+
+ public void assignRetake(String reason, ILSSession ilsSession, HttpSession httpSession) {
+ CourseAttempt ca = (CourseAttempt)this.clone();
+ setCourseAttemptExpired(reason);
+ save(ca);
+ Properties sysConfig = Utils.getSysConfig(httpSession.getServletContext(), getCompanyID());
+ boolean emailNotice = "on".equals(sysConfig.getProperty("EmailEnableAssignedRetakes", "off"));
+ if (emailNotice) {
+ try {
+ Course course = Course.getCourse(ilsSession, ca.getCourseAttemptCourseID());
+ UserProfile up = UserProfile.getUserProfile(ilsSession, ca.getCourseAttemptUserProfileID());
+ String subject = "Assigned Retake Notice";
+ String body = "You have been reassigned to take " + course.getCourseName() + " training. ";
+ body += "Please Log on to " + sysConfig.getProperty("SiteURL", "your training site")+" to complete your training.\n\n";
+ body += "Login Account: " + up.getUserProfileNum() + "\n";
+ body += "Course Name : " + course.getCourseName() + "\n\n";
+ body += sysConfig.getProperty("SendMailSig", "");
+ Utils.sendEmail(httpSession, sysConfig.getProperty("SendMailFrom", ilsSession.getCurrentCompany().getCompanyAdminContactEMail()), up.getUserProfileEMail(), subject, body, true);
+ }
+ catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
+ }
+
+ public static String getINIValue(String data, String key) {
+ String value = "";
+ int i = data.toLowerCase().indexOf(key.toLowerCase() + "=");
+ if (i > -1) {
+ i += key.length() + 1;
+ int j = data.indexOf("\r", i);
+ if (j < i) {
+ j = data.indexOf("\n", i);
+ }
+ if (j < i) {
+ value = data.substring(i);
+ }
+ else
+ value = data.substring(i, j);
+ }
+ return value;
+ }
+
+
+ public static String setINIValue(String data, String key, String value) {
+ String cValue = "";
+ String data1 = data;
+ int i = data.indexOf(key + "=");
+ if (i > -1) {
+ int j = i + key.length() + 1;
+ j = data.indexOf("\r\n", j);
+ if (j < i) {
+ data = data.substring(0, i);
+ }
+ else
+ data = data.substring(0, i) + data1.substring(j+"\r\n".length());
+ }
+ if (!data.endsWith("\r\n"))
+ data += "\r\n";
+ data += key + "=" + value+"\r\n";
+ return data;
+ }
+
}
Modified: branches/1.6/src/com/resolutions/ils/data/UserProfile.java
===================================================================
--- branches/1.6/src/com/resolutions/ils/data/UserProfile.java 2015-04-03 07:31:38 UTC (rev 1304)
+++ branches/1.6/src/com/resolutions/ils/data/UserProfile.java 2015-04-03 08:26:29 UTC (rev 1305)
@@ -48,6 +48,7 @@
public static final int ACCESS_MODE_USERPR_ADD_USER = 512;
public static final int ACCESS_MODE_USERPR_MODIFY_USER = 1024;
public static final int ACCESS_MODE_USERPR_MGR_ADD_MGR = 2048;
+ public static final int ACCESS_MODE_STUDENTREC_PASS_OVERRIDE = 4096;
static String LOOKUP = "SELECT * FROM USERPROFILE LEFT JOIN EMPTITLE ON USERPROFILE.USERPREMPTITLEID = EMPTITLE.EMPTITLEID AND EMPTITLE.COMPANYID={COMPANYID} WHERE USERPROFILE.COMPANYID={COMPANYID}";
@@ -256,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," +
@@ -276,19 +278,32 @@
" 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) {
- 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);
+ 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);
}
static public Vector<UserProfile> getSearchUserProfiles(ILSSession session, Set<Integer> locIDs, Set<Integer> roleIDs,
- String lName, String empNum, int statusID, int accessLevel, Date hireStartDate, Date hireEndDate, int managerID) {
+ String lName, String empNum, int statusID, int accessLevel, Date hireStartDate, Date hireEndDate, int managerID, boolean preloadCourseData) {
long timer = System.currentTimeMillis();
logger.debug("getSearchUserProfiles start(0)");
UserProfile retVal = new UserProfile();
- String sql = "SELECT UP2.USERPRID";
+ String columns = "UP2.USERPRID";
String tables = "USERPROFILE UP2";
String where = "UP2.COMPANYID={COMPANYID}";
retVal.setCompanyID(session.getCompanyID());
@@ -350,14 +365,23 @@
where += " AND upper(USERPRNUM) like {USERPRNUM}";
retVal.setUserProfileNum(empNum.toUpperCase() + "%");
}
- sql += " FROM " + tables + " WHERE " + where;
- String outerSQL = USERCOURSESUMMARY + " WHERE UP2.USERPRID IN ("+sql+") GROUP BY UP2.USERPRID, UP2.USERPRLNAME, UP2.USERPRFNAME, UP2.USERPRNUM, UP2.USERPRADDR2, UP2.USERPRHIREDATE";
logger.debug("getSearchUserProfiles about to getDataSet ("+(System.currentTimeMillis() - timer)+")"); timer = System.currentTimeMillis();
- Vector<UserProfile> results = retVal.getDataSet(outerSQL);
+ String sql = null;
+ if (preloadCourseData) {
+ sql = "SELECT UP2.USERPRID FROM " + tables + " WHERE " + where;
+ 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," +
+ " (SELECT MAX(GROUPNAME) FROM USERGROUP UG JOIN ILSGROUP IG ON UG.ILSGROUPID=IG.ILSGROUPID AND UG.USERPRID=UP2.USERPRID AND GROUPTYPEID=1) ROLE " +
+ " FROM " + tables + " WHERE " + where;
+ Vector<UserProfile> results = retVal.getDataSet(sql);
+
logger.debug("getSearchUserProfiles after getDataSet["+results.size()+"] ("+(System.currentTimeMillis() - timer)+")"); timer = System.currentTimeMillis();
+
// we've precached all our delay loads, so let's set our bits high to say so.
for (UserProfile up: results) {
- up.isCourseSummaryDataLoaded = true;
+ up.isCourseSummaryDataLoaded = (preloadCourseData);
up.isRoleLoaded = true;
up.isWorkgroupLoaded = true;
}
@@ -367,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" +
@@ -388,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
@@ -399,7 +438,7 @@
retVal.setCompanyID(session.getCompanyID());
if (statusID > -1) {
- where += " AND UP2.USERPRSTATUSID = "+statusID;
+ where += " AND UP.USERPRSTATUSID = "+statusID;
}
/*
@@ -414,36 +453,36 @@
*/
if (managerID > -1) {
- where += " AND UG.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";
- return retVal.getDataSet(sql);
+ sql += " GROUP BY GRP.ILSGROUPID, GRP.GROUPNAME ORDER BY GROUPNAME";
+ return retVal.getDataSet(sql + " OPTION (RECOMPILE)");
}
public UserProfile []getManagers() {
@@ -468,10 +507,18 @@
return getStringValue("USERPRFNAME");
}
+ public String getUserProfileMiddleName() {
+ return getStringValue("USERPRMNAME");
+ }
+
public String getUserProfileLastName() {
return getStringValue("USERPRLNAME");
}
+ public String getUserProfileSuffixName() {
+ return getStringValue("USERPRSNAME");
+ }
+
public int getUserProfileStatusID() {
return getIntValue("USERPRSTATUSID");
}
@@ -572,6 +619,18 @@
setValue("USERPRFNAME", userProfileFirstName);
}
+ public void setUserProfileMiddleName(String val) {
+ setValue("USERPRMNAME", val);
+ }
+
+ public void setUserProfileLastName(String userProfileLastName) {
+ setValue("USERPRLNAME", userProfileLastName);
+ }
+
+ public void setUserProfileSuffixName(String val) {
+ setValue("USERPRSNAME", val);
+ }
+
public void setUserProfileID(int userProfileID) {
setIntValue("USERPRID", userProfileID);
}
@@ -596,10 +655,6 @@
setValue("USERPRPHONE", userProfilePhone);
}
- public void setUserProfileLastName(String userProfileLastName) {
- setValue("USERPRLNAME", userProfileLastName);
- }
-
public void setUserProfilePasswd(String userProfilePasswd) {
setValue("USERPRPASSWD", userProfilePasswd);
}
@@ -608,6 +663,14 @@
setValue("USERPRSTATE", userProfileState);
}
+ public void setIPRestrictOverride(boolean val) {
+ setValue("USERPRIPRESTRICTOVERRIDE", (val) ? "T" : "F");
+ }
+
+ public boolean isIPRestrictOverride() {
+ return ("T".equals(getValue("USERPRIPRESTRICTOVERRIDE")));
+ }
+
public void setSecondaryValidationOverride(boolean val) {
setValue("USERPRSECONDLOGINOVERRIDE", (val) ? "T" : "F");
}
Modified: branches/1.6/webapp/META-INF/context.xml
===================================================================
--- branches/1.6/webapp/META-INF/context.xml 2015-04-03 07:31:38 UTC (rev 1304)
+++ branches/1.6/webapp/META-INF/context.xml 2015-04-03 08:26:29 UTC (rev 1305)
@@ -40,7 +40,7 @@
validationQuery="SELECT COMPANYID FROM SYSCONF"
username="$DBUSER"
password="$DBPASSWD"
- url="jdbc:sqlserver://192.168.100.85:1433;user=$DBUSER;password=$DBPASSWD;"
+ url="jdbc:sqlserver://10.134.51.131:1433;user=$DBUSER;password=$DBPASSWD;"
maxActive="10"/>
Modified: branches/1.6/webapp/SQL.jsp
===================================================================
--- branches/1.6/webapp/SQL.jsp 2015-04-03 07:31:38 UTC (rev 1304)
+++ branches/1.6/webapp/SQL.jsp 2015-04-03 08:26:29 UTC (rev 1305)
@@ -731,6 +731,7 @@
errOut.println("</td> </tr>");
}
}
+ errOut.println("</table><br />");
}
if (rs != null) {
ResultSetMetaData meta = rs.getMetaData();
Modified: branches/1.6/webapp/WEB-INF/classes/versions.properties
===================================================================
--- branches/1.6/webapp/WEB-INF/classes/versions.properties 2015-04-03 07:31:38 UTC (rev 1304)
+++ branches/1.6/webapp/WEB-INF/classes/versions.properties 2015-04-03 08:26:29 UTC (rev 1305)
@@ -1 +1 @@
-LMS=V1.79
+LMS=V1.80
Modified: branches/1.6/webapp/WEB-INF/lib/ils.jar
===================================================================
(Binary files differ)
Modified: branches/1.6/webapp/admin_profilemanagement.jsp
===================================================================
--- branches/1.6/webapp/admin_profilemanagement.jsp 2015-04-03 07:31:38 UTC (rev 1304)
+++ branches/1.6/webapp/admin_profilemanagement.jsp 2015-04-03 08:26:29 UTC (rev 1305)
@@ -178,7 +178,7 @@
}
if ("lookup".equals(action)) {
- userProfiles = UserProfile.getSearchUserProfiles(ilsSession, requestedLocations, requestedRoles, currentLName, currentEmpID, statusID, currentAccessLevel, hireStartDate, hireEndDate, limitToManager);
+ userProfiles = UserProfile.getSearchUserProfiles(ilsSession, requestedLocations, requestedRoles, currentLName, currentEmpID, statusID, currentAccessLevel, hireStartDate, hireEndDate, limitToManager, false);
if (userProfiles != null) {
/*
for (int j = 0; j < userProfiles.size(); j++) {
@@ -260,6 +260,7 @@
<script type="text/javascript" src="js/jquery/jquery.min.js"></script>
<script type="text/javascript" src="js/chosen/chosen.jquery.min.js"></script>
<script type="text/javascript" src="crosswire.js"></script>
+ <script type="text/javascript" src="js/jquery/jquery.blockUI.js"></script>
<script type="text/javascript" language="JavaScript">
<!--
var rdCtl = null;
@@ -389,7 +390,7 @@
<td><table width="106">
<tr>
<td width="106">
- <input name="GO" type="image" value="GO" src="images/search_btn.gif" /> </td>
+ <input onClick="$.blockUI({ message: '<h3><img width="30" height="30" style="margin-right:10px;vertical-align:middle;" src="images/loading.gif"/> Performing Search...</h3>' }); document.lookup.submit(); return false;" name="GO" type="image" value="GO" src="images/search_btn.gif" /> </td>
</tr>
<tr>
<td><a class="formbtn" href="admin_profile.jsp">
Modified: branches/1.6/webapp/admin_studentrecords.jsp
===================================================================
--- branches/1.6/webapp/admin_studentrecords.jsp 2015-04-03 07:31:38 UTC (rev 1304)
+++ branches/1.6/webapp/admin_studentrecords.jsp 2015-04-03 08:26:29 UTC (rev 1305)
@@ -168,7 +168,7 @@
}
if (byStudent) {
if ("lookup".equals(action)) {
- userProfiles = UserProfile.getSearchUserProfiles(ilsSession, requestedLocations, requestedRoles, currentLName, currentEmpID, statusID, -1, hireStartDate, hireEndDate, limitToManager);
+ userProfiles = UserProfile.getSearchUserProfiles(ilsSession, requestedLocations, requestedRoles, currentLName, currentEmpID, statusID, -1, hireStartDate, hireEndDate, limitToManager, true);
}
if (userProfiles != null) {
session.setAttribute("lastDataset", userProfiles);
@@ -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();
@@ -274,8 +274,9 @@
if (sort != null && sort.startsWith("extraCol")) {
Comparable c1 = ((Comparable)p1.getValue(extraCols.get(Integer.parseInt(sort.substring(8))-1).dbCol));
Comparable c2 = ((Comparable)p2.getValue(extraCols.get(Integer.parseInt(sort.substring(8))-1).dbCol));
- if (c1 == null) c1 = "";
- if (c2 == null) c2 = "";
+ if (c1 == null && c2 == null) return 0;
+ if (c1 == null) return -1;
+ if (c2 == null) return 1;
return c1.compareTo(c2);
}
return 0;
@@ -355,7 +356,7 @@
if (requestedLocations == null) {
Vector tmpg = Group.getUserGroups(ilsSession, Group.GROUPTYPE_LOCATION, user.getUserProfileID());
requestedLocations = ((tmpg != null) && (tmpg.size()>0))? new HashSet<Integer>(tmpg) : null;
- if (user.getUserProfileAccessLevel() > UserProfile.ACCESS_MANAGER) {
+ if (user.getUserProfileAccessLevel() >= UserProfile.ACCESS_MANAGER) {
requestedLocations = null;
}
}
@@ -373,6 +374,7 @@
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
<link rel="stylesheet" href="css/chosen/chosen.min.css">
<script type="text/javascript" src="js/jquery/jquery.min.js"></script>
+ <script type="text/javascript" src="js/jquery/jquery.blockUI.js"></script>
<script type="text/javascript" src="js/chosen/chosen.jquery.min.js"></script>
<script type="text/javascript" src="crosswire.js"></script>
<link href="lms_style.css" rel="stylesheet" type="text/css"/>
@@ -533,7 +535,7 @@
</tr>
<tr>
<td> </td>
- <td><input onClick="document.lookup.action.value='lookup'; document.lookup.submit();return false;" name="GO" type="image" value="GO" src="images/generate_btn.gif" align="left" />
+ <td><input onClick="$.blockUI({ message: '<h3><img width="30" height="30" style="margin-right:10px;vertical-align:middle;" src="images/loading.gif"/> Generating Report...</h3>' }); document.lookup.action.value='lookup'; document.lookup.submit();return false;" name="GO" type="image" value="GO" src="images/generate_btn.gif" align="left" />
<br></td>
</tr>
</table>
Modified: branches/1.6/webapp/login.jsp
===================================================================
--- branches/1.6/webapp/login.jsp 2015-04-03 07:31:38 UTC (rev 1304)
+++ branches/1.6/webapp/login.jsp 2015-04-03 08:26:29 UTC (rev 1305)
@@ -12,11 +12,10 @@
<%@ page import="java.io.DataOutputStream"%>
<%@ page import="java.io.BufferedReader"%>
<%@ page import="java.io.InputStreamReader"%>
+<%@ page import="java.util.Properties"%>
<%@ page import="org.apache.log4j.Logger" %>
<%
- boolean emailNotice = "on".equals(Utils.getSysConfig(session).getProperty("EmailEnableRecoverPassUser", "off"));
- boolean selfReg = "on".equals(Utils.getSysConfig(session).getProperty("SREnable", "off"));
String requestURL = request.getRequestURL().toString() + "?" + request.getQueryString();
String co = com.resolutions.ils.Utils.getCompanyFromConfig(session, requestURL);
//out.print(requestURL + co+"<br/>");
@@ -34,6 +33,11 @@
Company company = Company.getCompany((co != null) ? Integer.parseInt(co):1);
//out.print(co);
+ Properties sysConfig = Utils.getSysConfig(session.getServletContext(), company.getCompanyID());
+System.out.println(sysConfig);
+ boolean emailNotice = "on".equals(sysConfig.getProperty("EmailEnableRecoverPassUser", "off"));
+ boolean selfReg = "on".equals(sysConfig.getProperty("SREnable", "off"));
+
String action = request.getParameter("action");
if ("logoff".equals(action)) {
session.invalidate();
Modified: branches/1.6/webapp/report_pending_summary.jsp
===================================================================
--- branches/1.6/webapp/report_pending_summary.jsp 2015-04-03 07:31:38 UTC (rev 1304)
+++ branches/1.6/webapp/report_pending_summary.jsp 2015-04-03 08:26:29 UTC (rev 1305)
@@ -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");
}
@@ -174,10 +177,13 @@
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en_US" lang="en_US">
<head>
-<title><%= ilsSession.getCurrentCompany().getCompanyName() %> eLearning Portal</title>
-<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
+ <title><%= ilsSession.getCurrentCompany().getCompanyName() %> eLearning Portal</title>
+ <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
+
<script type="text/javascript" src="crosswire.js"></script>
<link href="lms_style.css" rel="stylesheet" type="text/css"/>
+ <script type="text/javascript" src="js/jquery/jquery.min.js"></script>
+ <script type="text/javascript" src="js/jquery/jquery.blockUI.js"></script>
<script type="text/javascript">
<!--
var rdCtl = null;
@@ -215,7 +221,7 @@
</tr>
<tr>
<td> </td>
- <td><input onClick="document.lookup.action.value='lookup'; document.lookup.submit(); return false;" name="GO" type="image" value="GO" src="images/generate_btn.gif" align="left" />
+ <td><input onClick="$.blockUI({ message: '<h3><img width="30" height="30" style="margin-right:10px;vertical-align:middle;" src="images/loading.gif"/> Generating Report...</h3>' }); document.lookup.action.value='lookup'; document.lookup.submit(); return false;" name="GO" type="image" value="GO" src="images/generate_btn.gif" align="left" />
<br></td>
</tr>
</table>
@@ -266,6 +272,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 +287,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 +300,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 +311,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