Sämtliche Logik und Grafiken zur Vergabe und Verwaltung von Berechtigungen eingeführt. Kleinere Bugs sind bekannt, werden aktuell behoben

1 files changed, 169 insertions, 62 deletions

diff --git a/dozentenmodulserver/src/main/java/sql/SQL.java b/dozentenmodulserver/src/main/java/sql/SQL.java
index 20dad27e..c95d874c 100644
--- a/dozentenmodulserver/src/main/java/sql/SQL.java
+++ b/dozentenmodulserver/src/main/java/sql/SQL.java
@@ -458,10 +458,10 @@ public class SQL {
 			Connection con = getConnection();

 			Statement stm = con.createStatement();

 			ResultSet res = stm

-					.executeQuery("SELECT l.lectureID, l.name,	l.isActive,	l.startTime, l.endTime,	l.lastUsed,	l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user "

+					.executeQuery("SELECT DISTINCT l.lectureID, l.name,	l.isActive,	l.startTime, l.endTime,	l.lastUsed,	l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user "

 							+ "FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u "

 							+ "WHERE	i.GUID_imageID=l.imageID AND pml.rec_read=true AND u.userID=pml.userID AND u.userID='"

-							+ userID + ";");

+							+ userID + "';");

 			while (res.next()) {

 

 				list.add(new Lecture(res.getString("lectureID"), res

@@ -488,10 +488,11 @@ public class SQL {
 			Connection con = getConnection();

 			Statement stm = con.createStatement();

 			ResultSet res = stm

-					.executeQuery("SELECT l.lectureID, l.name,	l.isActive,	l.startTime, l.endTime,	l.lastUsed,	l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user "

+					.executeQuery("SELECT DISTINCT l.lectureID, l.name,	l.isActive,	l.startTime, l.endTime,	l.lastUsed,	l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user "

 							+ "FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u "

-							+ "WHERE i.GUID_imageID=l.imageID AND pml.rec_write=true AND u.userID=pml.userID AND u.userID='"

-							+ userID + ";");

+							+ "WHERE pml.userID='"

+							+ userID

+							+ "' AND u.userID=pml.userID AND pml.rec_write=1 AND i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID;");

 			while (res.next()) {

 

 				list.add(new Lecture(res.getString("lectureID"), res

@@ -518,10 +519,10 @@ public class SQL {
 			Connection con = getConnection();

 			Statement stm = con.createStatement();

 			ResultSet res = stm

-					.executeQuery("SELECT l.lectureID, l.name,	l.isActive,	l.startTime, l.endTime,	l.lastUsed,	l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user "

+					.executeQuery("SELECT DISTINCT l.lectureID, l.name,	l.isActive,	l.startTime, l.endTime,	l.lastUsed,	l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user "

 							+ "FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u "

 							+ "WHERE i.GUID_imageID=l.imageID AND pml.rec_admin=true AND u.userID=pml.userID AND u.userID='"

-							+ userID + ";");

+							+ userID + "';");

 			while (res.next()) {

 

 				list.add(new Lecture(res.getString("lectureID"), res

@@ -1188,7 +1189,7 @@ public class SQL {
 

 	// get all users from satellite -- temp, tb replaced by allUsers bwLehrpool

 	// does -NOT- return the user himself

-	public List<Person> getAllOtherSatelliteUsers(String userID) {

+	public List<Person> getAllOtherSatelliteUsers(List<String> userID) {

 		Connection con = getConnection();

 		Statement stm = null;

 		ResultSet res = null;

@@ -1202,9 +1203,18 @@ public class SQL {
 			e.printStackTrace();

 		}

 		try {

-			res = stm

-					.executeQuery("SELECT userID, Nachname, Vorname FROM m_user WHERE userID !='"

-							+ userID + "' ORDER BY Nachname ASC;");

+			String query = "SELECT userID, Nachname, Vorname, mail FROM m_user WHERE userID NOT IN ('"

+					+ userID.get(0) + "";

+

+			for (int i = 1; i < userID.size(); i++) {

+				query = query + "','" + userID.get(i);

+			}

+			query = query + "') ORDER BY Nachname ASC;";

+

+			// res =

+			// stm.executeQuery("SELECT userID, Nachname, Vorname, mail FROM m_user WHERE userID !='"+

+			// userID + "' ORDER BY Nachname ASC;");

+			res = stm.executeQuery(query);

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

 			e.printStackTrace();

@@ -1214,8 +1224,9 @@ public class SQL {
 				// fill the list with users - permissions are all false because

 				// the image is new

 				list.add(new Person(res.getString("userID"), res

-						.getString("Nachname"), res.getString("Vorname"),

-						false, false, false, false, false, false, false));

+						.getString("Nachname"), res.getString("Vorname"), res

+						.getString("mail"), false, false, false, false, false,

+						false, false));

 			}

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

@@ -1224,14 +1235,15 @@ public class SQL {
 		return list;

 	}// end getAllOtherSatelliteUsers()

 

+	

 	// get the permissions of an image for all other registered users -EXCEPT-

 	// the logged on user

 	public List<Person> getPermissionForUserAndImage(String userID,

 			String imageID) {

+		

 		Connection con = getConnection();

 		Statement stm = null;

 		ResultSet res = null;

-

 		List<Person> list = new ArrayList<Person>();

 

 		try {

@@ -1242,13 +1254,13 @@ public class SQL {
 		}

 		try {

 			res = stm

-					.executeQuery("SELECT DISTINCT pmi.GUID_imageID, pmi.userID, u.userID, u.Nachname, u.Vorname, pmi.image_read, pmi.image_write, pmi.image_admin, pmi.link_allowed FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_user u WHERE pmi.userID!='"

+					.executeQuery("SELECT DISTINCT pmi.GUID_imageID, pmi.userID, u.userID, u.Nachname, u.Vorname, u.mail, pmi.image_read, pmi.image_write, pmi.image_admin, pmi.link_allowed FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_user u WHERE pmi.userID!='"

 							+ userID

 							+ "' AND pmi.GUID_imageID='"

 							+ imageID

 							+ "' AND pmi.userID=u.userID ORDER BY u.Nachname ASC;");

 			System.out

-					.println("SELECT DISTINCT pmi.GUID_imageID, pmi.userID, u.userID, u.Nachname, u.Vorname, pmi.image_read, pmi.image_write, pmi.image_admin, pmi.link_allowed FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_user u WHERE pmi.userID!='"

+					.println("SELECT DISTINCT pmi.GUID_imageID, pmi.userID, u.userID, u.Nachname, u.Vorname, u.mail, pmi.image_read, pmi.image_write, pmi.image_admin, pmi.link_allowed FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_user u WHERE pmi.userID!='"

 							+ userID

 							+ "' AND pmi.GUID_imageID='"

 							+ imageID

@@ -1286,10 +1298,9 @@ public class SQL {
 				// fill the list with users - permissions are all false because

 				// the image is new

 				list.add(new Person(res.getString("userID"), res

-						.getString("Nachname"), res.getString("Vorname"),

-						// Boolean.parseBoolean(Integer.parseInt(res.getString("image_read"))),

-						image_read, image_write, link_allowed, image_admin,

-						false, // lecture_read

+						.getString("Nachname"), res.getString("Vorname"), res

+						.getString("mail"), image_read, image_write,

+						link_allowed, image_admin, false, // lecture_read

 						false, // lecture_write

 						false) // lecture_admin

 				);

@@ -1301,6 +1312,89 @@ public class SQL {
 		}

 		return list;

 	}// end getPermissionFOrUserAndImage()

+	

+	

+	

+	// get the permissions of a lecture for all other registered users -EXCEPT- the logged on user

+	public List<Person> getPermissionForUserAndLecture(String userID,

+			String lectureID) {

+		

+		Connection con = getConnection();

+		Statement stm = null;

+		ResultSet res = null;

+		List<Person> list = new ArrayList<Person>();

+		

+		

+		try {

+			stm = con.createStatement();

+		} catch (SQLException e) {

+			// TODO Auto-generated catch block

+			e.printStackTrace();

+		}

+		try {

+			res = stm

+					.executeQuery("SELECT DISTINCT pml.lectureID, pml.userID, u.userID, u.Nachname,	u.Vorname, u.mail, pml.rec_read, pml.rec_write,	pml.rec_admin " +

+							"FROM bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u WHERE pml.userID!='"+userID+"' AND pml.lectureID='"+lectureID+"' AND pml.userID=u.userID ORDER BY u.Nachname ASC;");

+			System.out

+					.println("SELECT DISTINCT pml.lectureID, pml.userID, u.userID, u.Nachname,	u.Vorname, u.mail, pml.rec_read, pml.rec_write,	pml.rec_admin " +

+							"FROM bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u WHERE pml.userID!='"+userID+"' AND pml.lectureID='"+lectureID+"' AND pml.userID=u.userID ORDER BY u.Nachname ASC;");

+		} catch (SQLException e) {

+			// TODO Auto-generated catch block

+			e.printStackTrace();

+		}

+		try {

+			boolean image_read, image_write, link_allowed, image_admin, lecture_read, lecture_write, lecture_admin;

+

+			while (res.next()) {

+				// reset old values and set the correct new values

+				image_read = false;

+				image_write = false;

+				link_allowed = false;

+				image_admin = false;

+				lecture_read = false;

+				lecture_write = false;

+				lecture_admin = false;

+

+				if (Integer.parseInt(res.getString("rec_read")) != 0) {

+					lecture_read = true;

+				}

+				if (Integer.parseInt(res.getString("rec_write")) != 0) {

+					lecture_write = true;

+				}

+				if (Integer.parseInt(res.getString("rec_admin")) != 0) {

+					lecture_admin = true;

+				}

+

+				// fill the list with users - permissions are all false because

+				// the image is new

+				list.add(new Person(

+						res.getString("userID"),

+						res.getString("Nachname"),

+						res.getString("Vorname"),

+						res.getString("mail"),

+						false, //image read

+						false, //image write

+						false, //image link

+						false, //image admin

+						lecture_read, // lecture_read

+						lecture_write, // lecture_write

+						lecture_admin) // lecture_admin

+				);

+				// System.out.println(res.getString("Nachname")+" "+Boolean.parseBoolean(res.getString("image_read"))+" "+Boolean.parseBoolean(res.getString("image_write"))+" "+Boolean.parseBoolean(res.getString("image_admin")));

+			}

+		} catch (SQLException e) {

+			// TODO Auto-generated catch block

+			e.printStackTrace();

+		}

+		return list;

+	}// end getPermissionForUserAndLecture

+	

+	

+	

+	

+	

+	

+	

 

 	public boolean updateImagePermissions() {

 		boolean success = true;

@@ -1336,11 +1430,11 @@ public class SQL {
 

 	public int setImageRights(String pk_person, String pk_image, int role,

 			int read, int write, int linkallowed, int admin) {

-	

+

 		try {

 			Connection con = getConnection();

 			Statement stm = con.createStatement();

-	

+

 			String uid = UUID.randomUUID().toString();

 			stm.executeUpdate("INSERT INTO `bwLehrpool`.`pm_VLData_image`(`GUID`,`GUID_imageID`,`roleID`,`userID`,`image_read`,`image_write`,`image_admin`,`link_allowed`)VALUES('"

 					+ uid

@@ -1406,45 +1500,36 @@ public class SQL {
 		return success;

 	}// end writeAdditionalImageRights

 

-	

 	public int setLectureRights(String pk_person, String pk_lecture, int role,

-			int read, int write, int changePermission, int admin) {

-	

+			int read, int write, int admin) {

+

 		try {

 			Connection con = getConnection();

 			Statement stm = con.createStatement();

-	

+

 			String uid = UUID.randomUUID().toString();

-			stm.executeUpdate("INSERT INTO `bwLehrpool`.`pm_VLData_lecture`(`GUID`,`lectureID`,`roleID`,`userID`,`rec_read`,`rec_write`,`rec_admin`,`rec_changePermission`)VALUES('"

+

+			stm.executeUpdate("INSERT INTO `bwLehrpool`.`pm_VLData_lecture`(`GUID`,`lectureID`,`userID`,`rec_read`,`rec_write`,`rec_admin`)VALUES('"

 					+ uid

 					+ "','"

 					+ pk_lecture

 					+ "','"

-					+ role

-					+ "','"

 					+ pk_person

 					+ "','"

-					+ read

-					+ "','"

-					+ write

-					+ "','"

-					+ admin

-					+ "','" + changePermission + "');");

+					+ read + "','" + write + "','" + admin + "');");

 			con.commit();

+			log.info("Written lecture rights for " + pk_lecture);

 			con.close();

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

-			log.info(new Date() + " - Failed to setLectureRights.");

+			log.info(new Date() + "Failed to setLectureRights.");

 			e.printStackTrace();

 		}

 		return 0;

 	} // end setLectureRights

-	

-	

-	

-	public boolean writeAdditionalLectureRights(String imageID, String userID,

-			boolean isRead, boolean isWrite, boolean isLinkAllowed,

-			boolean isAdmin) {

+

+	public boolean writeAdditionalLectureRights(String lectureID,

+			String userID, boolean isRead, boolean isWrite, boolean isAdmin) {

 		boolean success = true;

 

 		String uid = UUID.randomUUID().toString();

@@ -1454,33 +1539,23 @@ public class SQL {
 		try {

 			stm = con.createStatement();

 

-			log.info(new Date() + " - INSERT INTO pm_VLData_image (" + "GUID, "

-					+ "GUID_imageID, " + "roleID, " + "userID, "

-					+ "image_read, " + "image_write, " + "Image_admin, "

-					+ "link_allowed)" + "VALUES(" + "'" + uid + "'," + "'"

-					+ imageID + "'," + "1," + "'" + userID + "'," + isRead

-					+ "," + isWrite + "," + isLinkAllowed + "," + isAdmin + ""

-					+ ")");

-

-			stm.executeUpdate("INSERT INTO pm_VLData_image (" + "GUID, "

-					+ "GUID_imageID, " + "roleID, " + "userID, "

-					+ "image_read, " + "image_write, " + "Image_admin, "

-					+ "link_allowed)" + "VALUES(" + "'" + uid + "'," + "'"

-					+ imageID + "'," + "1," + "'" + userID + "'," + isRead

-					+ "," + isWrite + "," + isLinkAllowed + "," + isAdmin + ""

-					+ ");");

+			stm.executeUpdate("INSERT INTO pm_VLData_lecture (" + "GUID, "

+					+ "lectureID, " + "userID, " + "rec_read, " + "rec_write, "

+					+ "rec_admin )" + "VALUES('" + uid + "'," + "'" + lectureID

+					+ "'," + "'" + userID + "'," + isRead + "," + isWrite + ","

+					+ isAdmin + ");");

 			con.commit();

 			con.close();

-			log.info(new Date() + " - Written additional lecture rights.");

+			log.info(new Date() + " - Written additional lecture rights for '"

+					+ userID + "'.");

 		} catch (SQLException e) {

 			// TODO Auto-generated catch block

 			e.printStackTrace();

-			log.info(new Date() + " - Failed to write additional lecture rights.");

+			log.info(new Date()

+					+ " - Failed to write additional lecture rights.");

 		}

 		return success;

 	}// end writeAdditionalLectureRights

-	

-	

 

 	public void deleteAllAdditionalImagePermissions(String imageID,

 			String userID) {

@@ -1512,6 +1587,38 @@ public class SQL {
 			// TODO Auto-generated catch block

 			e.printStackTrace();

 		}

-	}

+	}// end deleteAllAdditionalImagePermissions

+	

+	

+	

+	public void deleteAllAdditionalLecturePermissions(String lectureID, String userID) {

+		Connection con = getConnection();

+		Statement stm = null;

+

+		try {

+

+			stm = con.createStatement();

+			int ret = stm

+					.executeUpdate("DELETE FROM pm_VLData_lecture WHERE lectureID = '"

+							+ lectureID + "' AND userID != '" + userID + "';");

+		} catch (SQLException e) {

+			e.printStackTrace();

+		}

+

+		try {

+

+			stm = con.createStatement();

+			int ret = stm

+					.executeUpdate("DELETE FROM pm_VLData_lecture WHERE lectureID = '"

+							+ lectureID + "' AND userID != '" + userID + "';");

+

+			con.commit();

+

+		} catch (SQLException e) {

+			// TODO Auto-generated catch block

+			e.printStackTrace();

+		}

+	}// end deleteAllAdditionalImagePermissions

+	

 

 }// end class