[Ils-source] r1488 - trunk/src/com/resolutions/ils/data

[scribe at crosswire.org]

Author: scribe
Date: 2016-09-22 13:58:22 -0700 (Thu, 22 Sep 2016)
New Revision: 1488

Modified:
   trunk/src/com/resolutions/ils/data/CourseAttempt.java
Log:
added check for active student before sending expired emails


Modified: trunk/src/com/resolutions/ils/data/CourseAttempt.java
===================================================================
--- trunk/src/com/resolutions/ils/data/CourseAttempt.java	2016-07-29 23:52:48 UTC (rev 1487)
+++ trunk/src/com/resolutions/ils/data/CourseAttempt.java	2016-09-22 20:58:22 UTC (rev 1488)
@@ -77,7 +77,7 @@
 	static String LOOKUP = "SELECT * FROM COURSEATTEMPT WHERE COMPANYID={COMPANYID}";
 	static String DELETE = "DELETE FROM COURSEATTEMPT WHERE COURSATTID={COURSATTID} AND COMPANYID={COMPANYID}";
 
-	static String LOOKUPCOURSECOMPLETE = "SELECT CA.COMPANYID, CA.COURSATTID, UP.USERPRID, UP.USERPRNUM, UP.USERPREMAIL, CC.COURSNAME, CC.COURSISCURRICULUM FROM COURSEATTEMPT CA" +
+	static String LOOKUPCOURSECOMPLETE = "SELECT CA.COMPANYID, CA.COURSATTID, UP.USERPRID, UP.USERPRNUM, UP.USERPREMAIL, UP.USERPRSTATUSID, CC.COURSNAME, CC.COURSISCURRICULUM FROM COURSEATTEMPT CA" +
 				" join USERPROFILE UP on UP.USERPRID = CA.USERPRID" +
 				" join COURSE CC on CC.COURSID=CA.COURSID";
 
@@ -198,20 +198,22 @@
 			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);
-					HashMap<String, Object> params = new HashMap<String, Object>();
-					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", "");
-					SMTPMail.sendEmail(sysConfig, from, to, subject, body, true);
+				if (ca.getIntValue("USERPRSTATUSID")==1) {
+					try {
+						String to = ca.getStringValue("USERPREMAIL");
+	System.out.println("**** revokeAllExpired: sending email to: " + to);
+						HashMap<String, Object> params = new HashMap<String, Object>();
+						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", "");
+						SMTPMail.sendEmail(sysConfig, from, to, subject, body, true);
+					}
+					catch (Exception e) {
+						e.printStackTrace();
+					}
 				}
-				catch (Exception e) {
-					e.printStackTrace();
-				}
 			}
 			SMTPMail.closeMailConnection();
 		}
@@ -260,13 +262,13 @@
 
 		if (courseID > 0) {
 			query.setCourseAttemptCourseID(courseID);
-			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";
+			results = query.getDataSet(LOOKUPCOURSECOMPLETEPENDINGEXPCOURSE + "AND CAWARNEDON"+warningNumber+" IS NULL AND CC.COURSISACTIVE='T' AND UP.USERPRSTATUSID=1");
+			String WARNEXPCOURSE = WARN + " WHERE COURSATTID IN (" + LOOKUPPENDINGEXPCOURSE + " AND CC.COURSISACTIVE='T' AND EXISTS(SELECT 1 FROM USERPROFILE XX WHERE XX.USERPRID=CA2.USERPRID AND XX.USERPRSTATUSID=1))" + " AND CAWARNEDON"+warningNumber+" IS NULL";
 			count = query.executeSQL(WARNEXPCOURSE);
 		}
 		else {
-			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";
+			results = query.getDataSet(LOOKUPCOURSECOMPLETEPENDINGEXP + " AND CC.COURSISACTIVE='T' AND UP.USERPRSTATUSID=1 AND CAWARNEDON"+warningNumber+" IS NULL");
+			String WARNEXP = WARN + " WHERE COURSATTID IN (" + LOOKUPPENDINGEXP + " AND CC.COURSISACTIVE='T' AND EXISTS(SELECT 1 FROM USERPROFILE XX WHERE XX.USERPRID=CA2.USERPRID AND XX.USERPRSTATUSID=1))" + " AND CAWARNEDON"+warningNumber+" IS NULL";
 			count = query.executeSQL(WARNEXP);
 		}
 
@@ -1025,7 +1027,7 @@
 		if (httpSession != null) {
 			Properties sysConfig = Utils.getSysConfig(httpSession.getServletContext(), getCompanyID());
 			boolean emailNotice = "on".equals(sysConfig.getProperty("EmailEnableAssignedRetakes", "off"));
-			if (emailNotice) {
+			if (emailNotice && this.getIntValue("USERPRSTATUSID")==1) {
 				try {
 					Company company = Company.getCompany(getCompanyID());
 					UserProfile up = UserProfile.getUserProfile(getCompanyID(), ca.getCourseAttemptUserProfileID());