[server] Add location support/filtering

1 files changed, 69 insertions, 2 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 0ed62770..1f0daf66 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
@@ -63,6 +63,8 @@ public class DbLecture {
 		stmt.setString("imageversionid", lecture.imageVersionId);
 		stmt.setBoolean("autoupdate", lecture.autoUpdate);
 		stmt.setBoolean("isenabled", lecture.isEnabled);
+		stmt.setBoolean("isprivate", lecture.limitToAllowedUsers);
+		stmt.setBoolean("islocationprivate", lecture.limitToLocations);
 		stmt.setLong("starttime", lecture.startTime);
 		stmt.setLong("endtime", lecture.endTime);
 		stmt.setString("updaterid", updatingUser.userId);
@@ -75,22 +77,41 @@ public class DbLecture {
 		stmt.setBoolean("canadmindefault", lecture.defaultPermissions.admin);
 	}
 
+	private static void writeLocations(MysqlConnection connection, String lectureId, List<Integer> locationIds)
+			throws SQLException {
+		MysqlStatement delStmt = connection.prepareStatement("DELETE FROM lecture_x_location WHERE lectureid = :lectureid");
+		delStmt.setString("lectureid", lectureId);
+		delStmt.executeUpdate();
+		if (locationIds == null || locationIds.isEmpty())
+			return;
+		MysqlStatement addStmt = connection.prepareStatement("INSERT IGNORE INTO lecture_x_location (lectureid, locationid)"
+				+ " VALUES (:lectureid, :locationid)");
+		addStmt.setString("lectureid", lectureId);
+		for (Integer locationId : locationIds) {
+			addStmt.setInt("locationid", locationId);
+			addStmt.executeUpdate();
+		}
+	}
+
 	public static String create(UserInfo user, LectureWrite lecture) throws SQLException {
 		try (MysqlConnection connection = Database.getConnection()) {
 			MysqlStatement stmt = connection.prepareStatement("INSERT INTO lecture"
 					+ " (lectureid, displayname, description, imageversionid, autoupdate,"
 					+ "  isenabled, starttime, endtime, createtime, updatetime,"
+					+ "  isprivate, islocationprivate,"
 					+ "  ownerid, updaterid, runscript, nics, netrules, isexam,"
 					+ "  hasinternetaccess, caneditdefault, canadmindefault)"
 					+ "         VALUES             "
 					+ " (:lectureid, :displayname, :description, :imageversionid, :autoupdate,"
 					+ "  :isenabled, :starttime, :endtime, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(),"
+					+ "  :isprivate, :islocationprivate,"
 					+ "  :ownerid, :updaterid, :runscript, :nics, :netrules, :isexam,"
 					+ "  :hasinternetaccess, :caneditdefault, :canadmindefault)");
 			String lectureId = UUID.randomUUID().toString();
 			setWriteFields(stmt, lectureId, lecture, user);
 			stmt.setString("ownerid", user.userId);
 			stmt.executeUpdate();
+			writeLocations(connection, lectureId, lecture.locationIds);
 			connection.commit();
 			return lectureId;
 		} catch (SQLException e) {
@@ -105,11 +126,13 @@ public class DbLecture {
 				+ " displayname = :displayname, description = :description, imageversionid = :imageversionid,"
 				+ " autoupdate = :autoupdate, isenabled = :isenabled, starttime = :starttime,"
 				+ " endtime = :endtime, updatetime = UNIX_TIMESTAMP(),"
+				+ " isprivate = :isprivate, islocationprivate = :islocationprivate,"
 				+ " updaterid = :updaterid, runscript = :runscript, nics = :nics,"
 				+ " netrules = :netrules, isexam = :isexam, hasinternetaccess = :hasinternetaccess,"
 				+ " caneditdefault = :caneditdefault, canadmindefault = :canadmindefault"
 				+ " WHERE lectureid = :lectureid");
 		setWriteFields(stmt, lectureId, lecture, user);
+		writeLocations(connection, lectureId, lecture.locationIds);
 		stmt.executeUpdate();
 	}
 
@@ -241,6 +264,7 @@ public class DbLecture {
 					+ " l.lectureid, l.displayname AS lecturename, l.description, l.imageversionid, i.imagebaseid,"
 					+ " l.autoupdate, l.isenabled, l.starttime, l.endtime, l.lastused, l.usecount, l.createtime,"
 					+ " l.updatetime, l.ownerid, l.updaterid, l.runscript, l.nics, l.netrules, l.isexam,"
+					+ " l.isprivate, l.islocationprivate,"
 					+ " l.hasinternetaccess, l.caneditdefault, l.canadmindefault, p.canedit, p.canadmin"
 					+ "                   FROM lecture l            "
 					+ " LEFT JOIN imageversion i USING (imageversionid)"
@@ -259,6 +283,8 @@ public class DbLecture {
 			lecture.setImageBaseId(rs.getString("imagebaseid"));
 			lecture.setAutoUpdate(rs.getBoolean("autoupdate"));
 			lecture.setIsEnabled(rs.getBoolean("isenabled"));
+			//lecture.setLimitToAllowedUsers(rs.getBoolean("isprivate"));
+			//lecture.setLimitToLocations(rs.getBoolean("islocationprivate")); TODO
 			lecture.setStartTime(rs.getLong("starttime"));
 			lecture.setEndTime(rs.getLong("endtime"));
 			lecture.setLastUsed(rs.getLong("lastused"));
@@ -286,6 +312,7 @@ public class DbLecture {
 			lecture.setDefaultPermissions(DbLecturePermissions.fromResultSetDefault(rs));
 			lecture.setUserPermissions(DbLecturePermissions.fromResultSetUser(rs));
 			User.setCombinedUserPermissions(lecture, user);
+			// TODO: Query locations
 			return lecture;
 		} catch (SQLException e) {
 			LOGGER.error("Query failed in DbLecture.getLectureDetails()", e);
@@ -407,10 +434,37 @@ public class DbLecture {
 		}
 	}
 
-	public static VmChooserListXml getUsableListXml(boolean exams) throws SQLException {
+	public static VmChooserListXml getUsableListXml(boolean exams, String locationsString) throws SQLException {
+		// Sanitize and clean locations string
+		// Input is in the form of "1 2 3 4" or "1" or "  1   4 5"
+		// We want "1,2,3,4" or "1" or "1,4,5"
+		// Do this since we embed this directly into the query
+		String cleanLocations = null;
+		if (Util.isEmptyString(locationsString)) {
+			cleanLocations = "0";
+		} else if (locationsString.indexOf(' ') == -1) {
+			cleanLocations = Integer.toString(org.openslx.util.Util.parseInt(locationsString, 0));
+		} else {
+			String[] array = locationsString.split(" +");
+			for (String loc : array) {
+				int val = org.openslx.util.Util.parseInt(loc, -1);
+				if (val == -1)
+					continue;
+				if (cleanLocations == null) {
+					cleanLocations = Integer.toString(val);
+				} else {
+					cleanLocations += "," + Integer.toString(val);
+				}
+			}
+			if (cleanLocations == null) {
+				cleanLocations = "0";
+			}
+		}
+		// Query
 		try (MysqlConnection connection = Database.getConnection()) {
 			MysqlStatement stmt = connection.prepareStatement("SELECT"
 					+ " l.lectureid, l.displayname AS lecturename, l.description,"
+					+ " l.islocationprivate, loc.lectureid AS loctest,"
 					+ " l.endtime, l.usecount, o.displayname AS osname, v.virtname, b.istemplate,"
 					+ " v.virtid, ov.virtoskeyword, i.filepath"
 					+ "                      FROM lecture l                 "
@@ -419,13 +473,26 @@ public class DbLecture {
 					+ " INNER JOIN operatingsystem o USING (osid)"
 					+ " INNER JOIN virtualizer v USING (virtid)"
 					+ " LEFT JOIN os_x_virt ov USING (osid, virtid)"
+					+ " LEFT JOIN ("
+					+ "  SELECT DISTINCT lectureid FROM lecture_x_location WHERE locationid IN (" + cleanLocations + ")"
+					+ " ) loc USING (lectureid)"
 					+ " WHERE l.isenabled = 1 AND l.isprivate = 0 AND l.isexam = :isexam"
 					+ " AND l.starttime < UNIX_TIMESTAMP() AND l.endtime > UNIX_TIMESTAMP() AND i.isvalid = 1");
 			stmt.setBoolean("isexam", exams);
 			ResultSet rs = stmt.executeQuery();
 			VmChooserListXml list = new VmChooserListXml(true);
 			while (rs.next()) {
-				String prio = rs.getBoolean("istemplate") ? "10" : "100";
+				boolean isForThisLocation = rs.getString("loctest") != null;
+				if (!isForThisLocation && rs.getBoolean("islocationprivate"))
+					continue; // Is limited to location, and we're not in one of the required locations
+				String prio;
+				if (isForThisLocation) {
+					prio = "40";
+				} else if (rs.getBoolean("istemplate")) {
+					prio = "60";
+				} else {
+					prio = "80";
+				}
 				list.add(new VmChooserEntryXml(rs.getString("filepath"), prio, "-",
 						rs.getString("lecturename"), rs.getString("description"), rs.getString("lectureid"),
 						rs.getString("virtid"), rs.getString("virtname"), rs.getString("virtoskeyword"),