From 0ed4549c028e2523534a09fd6e41c49c9d178ec1 Mon Sep 17 00:00:00 2001 From: Michael Wilson Date: Mon, 27 Oct 2014 18:13:20 +0100 Subject: Sämtliche Logik und Grafiken zur Vergabe und Verwaltung von Berechtigungen eingeführt. Kleinere Bugs sind bekannt, werden aktuell behoben --- dozentenmodulserver/src/main/java/sql/SQL.java | 231 ++++++++++++++++++------- 1 file changed, 169 insertions(+), 62 deletions(-) (limited to 'dozentenmodulserver/src/main/java/sql/SQL.java') 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 getAllOtherSatelliteUsers(String userID) { + public List getAllOtherSatelliteUsers(List 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 getPermissionForUserAndImage(String userID, String imageID) { + Connection con = getConnection(); Statement stm = null; ResultSet res = null; - List list = new ArrayList(); 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 getPermissionForUserAndLecture(String userID, + String lectureID) { + + Connection con = getConnection(); + Statement stm = null; + ResultSet res = null; + List list = new ArrayList(); + + + 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 -- cgit v1.2.3-55-g7522