[server] Handle image version deletion, interface for sending mails (no SMTP yet)

1 files changed, 88 insertions, 72 deletions

diff --git a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLecture.java b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLecture.java
index 2623a9b6..f6431b46 100644
--- a/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLecture.java
+++ b/dozentenmodulserver/src/main/java/org/openslx/bwlp/sat/database/mappers/DbLecture.java
@@ -4,6 +4,7 @@ import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.ArrayList;
 import java.util.Arrays;
+import java.util.Iterator;
 import java.util.List;
 import java.util.UUID;
 
@@ -12,6 +13,7 @@ import org.openslx.bwlp.sat.database.Database;
 import org.openslx.bwlp.sat.database.MysqlConnection;
 import org.openslx.bwlp.sat.database.MysqlStatement;
 import org.openslx.bwlp.sat.database.models.LocalImageVersion;
+import org.openslx.bwlp.sat.maintenance.Mailer;
 import org.openslx.bwlp.sat.permissions.User;
 import org.openslx.bwlp.sat.util.Json;
 import org.openslx.bwlp.thrift.iface.LectureRead;
@@ -129,38 +131,50 @@ public class DbLecture {
 		}
 	}
 
+	private static LectureSummary fillSummary(UserInfo user, ResultSet rs) throws SQLException {
+		LectureSummary lecture = new LectureSummary();
+		lecture.setLectureId(rs.getString("lectureid"));
+		lecture.setLectureName(rs.getString("lecturename"));
+		lecture.setImageVersionId(rs.getString("imageversionid"));
+		lecture.setImageBaseId(rs.getString("imagebaseid"));
+		lecture.setIsEnabled(rs.getBoolean("isenabled"));
+		lecture.setStartTime(rs.getLong("starttime"));
+		lecture.setEndTime(rs.getLong("endtime"));
+		lecture.setLastUsed(rs.getLong("lastused"));
+		lecture.setUseCount(rs.getInt("usecount"));
+		lecture.setOwnerId(rs.getString("ownerid"));
+		lecture.setUpdaterId(rs.getString("updaterid"));
+		lecture.setIsExam(rs.getBoolean("isexam"));
+		lecture.setHasInternetAccess(rs.getBoolean("hasinternetaccess"));
+		lecture.setDefaultPermissions(DbLecturePermissions.fromResultSetDefault(rs));
+		lecture.setUserPermissions(DbLecturePermissions.fromResultSetUser(rs));
+		lecture.setIsImageVersionUsable(rs.getBoolean("imgvalid"));
+		if (user != null) {
+			User.setCombinedUserPermissions(lecture, user);
+		}
+		return lecture;
+	}
+
+	private static final String summaryBaseSql = "SELECT"
+			+ " l.lectureid, l.displayname AS lecturename, l.imageversionid, i.imagebaseid,"
+			+ " l.isenabled, l.starttime, l.endtime, l.lastused, l.usecount, l.ownerid, l.updaterid,"
+			+ " l.isexam, l.hasinternetaccess, l.caneditdefault, l.canadmindefault,"
+			+ " i.isvalid AS imgvalid, perm.canedit, perm.canadmin"
+			+ "                      FROM lecture l                 "
+			+ " INNER JOIN imageversion i USING (imageversionid)"
+			+ " LEFT JOIN lecturepermission perm ON (perm.lectureid = l.lectureid AND perm.userid = :userid)";
+
 	public static LectureSummary getLectureSummary(UserInfo user, String lectureId) throws SQLException,
 			TNotFoundException {
 		try (MysqlConnection connection = Database.getConnection()) {
-			MysqlStatement stmt = connection.prepareStatement("SELECT"
-					+ " l.lectureid, l.displayname AS lecturename, l.imageversionid, l.isenabled,"
-					+ " l.starttime, l.endtime, l.lastused, l.usecount, l.ownerid, l.updaterid,"
-					+ " l.isexam, l.hasinternetaccess, l.caneditdefault, l.canadmindefault,"
-					+ " perm.canedit, perm.canadmin FROM lecture l"
-					+ " LEFT JOIN lecturepermission perm ON (perm.lectureid = l.lectureid AND perm.userid = :userid)"
+			MysqlStatement stmt = connection.prepareStatement(summaryBaseSql
 					+ " WHERE l.lectureid = :lectureid");
 			stmt.setString("lectureid", lectureId);
-			stmt.setString("userid", user.userId);
+			stmt.setString("userid", user == null ? "-" : user.userId);
 			ResultSet rs = stmt.executeQuery();
 			if (!rs.next())
 				throw new TNotFoundException();
-			LectureSummary lecture = new LectureSummary();
-			lecture.setLectureId(rs.getString("lectureid"));
-			lecture.setLectureName(rs.getString("lecturename"));
-			lecture.setImageVersionId(rs.getString("imageversionid"));
-			lecture.setIsEnabled(rs.getBoolean("isenabled"));
-			lecture.setStartTime(rs.getLong("starttime"));
-			lecture.setEndTime(rs.getLong("endtime"));
-			lecture.setLastUsed(rs.getLong("lastused"));
-			lecture.setUseCount(rs.getInt("usecount"));
-			lecture.setOwnerId(rs.getString("ownerid"));
-			lecture.setUpdaterId(rs.getString("updaterid"));
-			lecture.setIsExam(rs.getBoolean("isexam"));
-			lecture.setHasInternetAccess(rs.getBoolean("hasinternetaccess"));
-			lecture.setDefaultPermissions(DbLecturePermissions.fromResultSetDefault(rs));
-			lecture.setUserPermissions(DbLecturePermissions.fromResultSetUser(rs));
-			User.setCombinedUserPermissions(lecture, user);
-			return lecture;
+			return fillSummary(user, rs);
 		} catch (SQLException e) {
 			LOGGER.error("Query failed in DbLecture.getLectureSummary()", e);
 			throw e;
@@ -169,42 +183,13 @@ public class DbLecture {
 
 	public static List<LectureSummary> getAll(UserInfo user, int page) throws SQLException {
 		try (MysqlConnection connection = Database.getConnection()) {
-			MysqlStatement stmt = connection.prepareStatement("SELECT"
-					+ " l.lectureid, l.displayname AS lecturename, l.imageversionid, i.imagebaseid,"
-					+ " l.isenabled, l.starttime, l.endtime, l.lastused, l.usecount, l.ownerid, l.updaterid,"
-					+ " l.isexam, l.hasinternetaccess, l.caneditdefault, l.canadmindefault,"
-					+ " i.isvalid AS imgvalid, p.canedit, p.canadmin"
-					+ "               FROM lecture l                      "
-					+ " INNER JOIN imageversion i USING (imageversionid)"
-					+ " LEFT JOIN lecturepermission p ON (p.lectureid = l.lectureid AND p.userid = :userid)"
+			MysqlStatement stmt = connection.prepareStatement(summaryBaseSql
 					+ (User.isStudent(user) ? " WHERE i.isrestricted = 0" : ""));
-			if (user == null) {
-				stmt.setString("userid", "-");
-			} else {
-				stmt.setString("userid", user.userId);
-			}
+			stmt.setString("userid", user == null ? "-" : user.userId);
 			ResultSet rs = stmt.executeQuery();
 			List<LectureSummary> list = new ArrayList<>(100);
 			while (rs.next()) {
-				LectureSummary lecture = new LectureSummary();
-				lecture.setLectureId(rs.getString("lectureid"));
-				lecture.setLectureName(rs.getString("lecturename"));
-				lecture.setImageVersionId(rs.getString("imageversionid"));
-				lecture.setImageBaseId(rs.getString("imagebaseid"));
-				lecture.setIsEnabled(rs.getBoolean("isenabled"));
-				lecture.setStartTime(rs.getLong("starttime"));
-				lecture.setEndTime(rs.getLong("endtime"));
-				lecture.setLastUsed(rs.getLong("lastused"));
-				lecture.setUseCount(rs.getInt("usecount"));
-				lecture.setOwnerId(rs.getString("ownerid"));
-				lecture.setUpdaterId(rs.getString("updaterid"));
-				lecture.setIsExam(rs.getBoolean("isexam"));
-				lecture.setHasInternetAccess(rs.getBoolean("hasinternetaccess"));
-				lecture.setDefaultPermissions(DbLecturePermissions.fromResultSetDefault(rs));
-				lecture.setUserPermissions(DbLecturePermissions.fromResultSetUser(rs));
-				lecture.setIsImageVersionUsable(rs.getBoolean("imgvalid"));
-				User.setCombinedUserPermissions(lecture, user);
-				list.add(lecture);
+				list.add(fillSummary(user, rs));
 			}
 			return list;
 		} catch (SQLException e) {
@@ -213,14 +198,30 @@ public class DbLecture {
 		}
 	}
 
-	protected static List<String> getAllUsingImageVersion(MysqlConnection connection, String imageVersionId)
+	protected static List<LectureSummary> getAllUsingImageBase(MysqlConnection connection, String imageBaseId, boolean autoUpdateOnly)
 			throws SQLException {
-		MysqlStatement stmt = connection.prepareStatement("SELECT lectureid FROM lecture WHERE imageversionid = :imageversionid");
+		MysqlStatement stmt = connection.prepareStatement(summaryBaseSql
+				+ " WHERE imagebaseid = :imagebaseid" + (autoUpdateOnly ? " AND autoupdate = 1" : ""));
+		stmt.setString("imagebaseid", imageBaseId);
+		stmt.setString("userid", "-");
+		ResultSet rs = stmt.executeQuery();
+		List<LectureSummary> list = new ArrayList<>();
+		while (rs.next()) {
+			list.add(fillSummary(null, rs));
+		}
+		return list;
+	}
+
+	protected static List<LectureSummary> getAllUsingImageVersion(MysqlConnection connection,
+			String imageVersionId) throws SQLException {
+		MysqlStatement stmt = connection.prepareStatement(summaryBaseSql
+				+ " WHERE imageversionid = :imageversionid");
 		stmt.setString("imageversionid", imageVersionId);
+		stmt.setString("userid", "-");
 		ResultSet rs = stmt.executeQuery();
-		List<String> list = new ArrayList<>();
+		List<LectureSummary> list = new ArrayList<>();
 		while (rs.next()) {
-			list.add(rs.getString("lectureid"));
+			list.add(fillSummary(null, rs));
 		}
 		return list;
 	}
@@ -322,8 +323,18 @@ public class DbLecture {
 	 */
 	protected static void autoUpdateUsedImage(MysqlConnection connection, String imageBaseId,
 			LocalImageVersion newVersion) throws SQLException {
-		// TODO: select first so we can email
-		// TODO: If new version is null, do not change, but send warning mail
+		if (newVersion == null)
+			return;
+		List<LectureSummary> lectures = getAllUsingImageBase(connection, imageBaseId, true);
+		if (lectures.isEmpty())
+			return;
+		// Remove lectures that are already on the given latest version from the list...
+		for (Iterator<LectureSummary> it = lectures.iterator(); it.hasNext();) {
+			LectureSummary lecture = it.next();
+			if (lecture.imageVersionId.equals(newVersion.imageVersionId))
+				it.remove();
+		}
+		// Update lectures in DB
 		MysqlStatement stmt = connection.prepareStatement("UPDATE lecture l, imageversion v SET"
 				+ " l.imageversionid = :imageversionid"
 				+ " WHERE v.imageversionid = l.imageversionid AND v.imagebaseid = :imagebaseid"
@@ -331,6 +342,8 @@ public class DbLecture {
 		stmt.setString("imageversionid", newVersion.imageVersionId);
 		stmt.setString("imagebaseid", imageBaseId);
 		stmt.executeUpdate();
+		// Send informative mail to lecture admins
+		Mailer.lectureAutoUpdate(lectures, newVersion);
 	}
 
 	/**
@@ -343,21 +356,24 @@ public class DbLecture {
 				|| (newVersion != null && newVersion.imageVersionId.equals(oldVersion.imageVersionId)))
 			return;
 		// First, get list of lectures using the image version to switch away from
-		List<String> lectures = getAllUsingImageVersion(connection, oldVersion.imageVersionId);
+		List<LectureSummary> lectures = getAllUsingImageVersion(connection, oldVersion.imageVersionId);
 		if (lectures.isEmpty())
 			return;
-		// TODO: If there is no new candidate to switch to, send a warning via mail
+		// TODO: If there is no new candidate to switch to, send a warning via mail, otherwise, inform about switch
+		MysqlStatement stmt;
 		if (newVersion == null) {
-			// ... email stuff
-			return;
+			stmt = connection.prepareStatement("UPDATE lecture SET isenabled = 0 WHERE imageversionid = :oldversionid");
+			stmt.setString("oldversionid", oldVersion.imageVersionId);
+			Mailer.lectureDeactivated(lectures);
+		} else {
+			// Update and send info mail
+			stmt = connection.prepareStatement("UPDATE lecture SET imageversionid = :newversionid"
+					+ " WHERE imageversionid = :oldversionid");
+			stmt.setString("oldversionid", oldVersion.imageVersionId);
+			stmt.setString("newversionid", newVersion.imageVersionId);
+			Mailer.lectureForcedUpdate(lectures, newVersion);
 		}
-		// Update and send info mail
-		MysqlStatement stmt = connection.prepareStatement("UPDATE lecture SET imageversionid = :newversionid"
-				+ " WHERE imageversionid = :oldversionid");
-		stmt.setString("oldversionid", oldVersion.imageVersionId);
-		stmt.setString("newversionid", newVersion.imageVersionId);
 		stmt.executeUpdate();
-		// TODO: Send mails .. Something.sendLectureChangeNotify(lectures, oldVersion, newVersion);
 	}
 
 }