From 6ccc95939c22f4b771856b3a19e76288c3b5f113 Mon Sep 17 00:00:00 2001 From: Michael Wilson Date: Tue, 21 Oct 2014 18:02:43 +0200 Subject: -Fehler behoben, Liste der Benutzer für Berechtigung nun korrekt -Backend vorbereitet für personenabhängige Liste der Veranstaltungen nach Rechten --- dozentenmodulserver/src/main/java/sql/SQL.java | 892 ++++++++++++++----------- 1 file changed, 491 insertions(+), 401 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 7b58fa06..99b4219f 100644 --- a/dozentenmodulserver/src/main/java/sql/SQL.java +++ b/dozentenmodulserver/src/main/java/sql/SQL.java @@ -22,8 +22,6 @@ import server.generated.Image; import server.generated.Lecture; import server.generated.Person; - - public class SQL { private static Logger log = Logger.getLogger(BinaryListener.class); @@ -37,10 +35,12 @@ public class SQL { e.printStackTrace(); } try { - Connection con = DriverManager - .getConnection("jdbc:mysql://"+Configuration.config.getSql_connection()+"?user="+Configuration.config.getSql_user()+"&password="+Configuration.config.getSql_pass()+""); + Connection con = DriverManager.getConnection("jdbc:mysql://" + + Configuration.config.getSql_connection() + "?user=" + + Configuration.config.getSql_user() + "&password=" + + Configuration.config.getSql_pass() + ""); con.setAutoCommit(false); - + return con; } catch (SQLException e) { // TODO Auto-generated catch block @@ -53,7 +53,7 @@ public class SQL { public int writeFTPUser(String user, String pass) { Statement stm; try { - Connection con=getConnection(); + Connection con = getConnection(); stm = con.createStatement(); int ret = stm @@ -61,7 +61,9 @@ public class SQL { + user + "',SHA1('" + pass - + "'),'10001','12345','"+Configuration.config.getAbsolute_path()+"temp/');"); + + "'),'10001','12345','" + + Configuration.config.getAbsolute_path() + + "temp/');"); con.commit(); con.close(); log.info(new Date() + " - created FTPUser " + user + " : " + pass @@ -78,7 +80,7 @@ public class SQL { public int DeleteUser(String user) { Statement stm; try { - Connection con=getConnection(); + Connection con = getConnection(); stm = con.createStatement(); int ret = stm @@ -98,13 +100,13 @@ public class SQL { public ResultSet getImage() { try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); - ResultSet rs=stm + ResultSet rs = stm .executeQuery("SELECT image_name FROM bwLehrpool.m_VLData_imageInfo;"); con.close(); - return rs; - + return rs; + } catch (SQLException e) { // TODO Auto-generated catch block log.info(new Date() + " - Failed to getImage."); @@ -114,21 +116,20 @@ public class SQL { return null; } - public String getPathOfImage(String image_id, - String version) { + public String getPathOfImage(String image_id, String version) { try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); - ResultSet rs=stm + ResultSet rs = stm .executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID='" + image_id + "' and imageVersion='" + version + "';"); rs.first(); - String path=rs.getString("image_path"); + String path = rs.getString("image_path"); con.close(); - return path; + return path; } catch (SQLException e) { // TODO Auto-generated catch block @@ -141,15 +142,15 @@ public class SQL { public String setInstitution(String university) { try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); ResultSet ret = stm .executeQuery("SELECT * FROM bwLehrpool.m_institution where name like'" + university + "';"); - + if (ret.next() == false) { - + String id = UUID.randomUUID().toString(); stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_institution`(`institutionID`,`name`)VALUES('" + id + "','" + university + "');"); @@ -158,12 +159,12 @@ public class SQL { .executeQuery("SELECT institutionID FROM bwLehrpool.m_institution WHERE name like '" + university + "';"); rs.next(); - String institution=rs.getString("institutionID"); + String institution = rs.getString("institutionID"); con.close(); - + return institution; } else { - String institution=ret.getString("institutionID"); + String institution = ret.getString("institutionID"); con.close(); return institution; } @@ -176,11 +177,11 @@ public class SQL { return "-1"; } - public String setPerson(String login, String lastname, - String firstname, String mail, Date lastlogin, String Institution) { + public String setPerson(String login, String lastname, String firstname, + String mail, Date lastlogin, String Institution) { DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); ResultSet ret = stm @@ -189,10 +190,10 @@ public class SQL { + "' and Vorname like '" + firstname + "';"); - + if (ret.next() == false) { - - //String id = UUID.randomUUID().toString(); + + // String id = UUID.randomUUID().toString(); stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES('" + login + "','" @@ -216,12 +217,12 @@ public class SQL { + firstname + "';"); rs.next(); - String userid=rs.getString("userID"); + String userid = rs.getString("userID"); con.close(); return userid; } else { ret.first(); - String userid=ret.getString("userID"); + String userid = ret.getString("userID"); con.close(); return userid; } @@ -235,8 +236,9 @@ public class SQL { } public boolean setImageData(String pk_person, boolean license, - boolean internet, long cpu, long ram, String imagename,String desc, - String imagePath, long filesize, String shareMode, String pk_os) { + boolean internet, long cpu, long ram, String imagename, + String desc, String imagePath, long filesize, String shareMode, + String pk_os) { DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); @@ -250,18 +252,20 @@ public class SQL { } try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); - //create a UUID + // create a UUID String uid = UUID.randomUUID().toString(); - - log.info(" - Query is: "+"INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`image_description`,`image_path`,`image_lastCall`,`image_create_time`,`image_update_time`,`image_owner`,`image_change_by`,`rec_create_time`,`rec_change_time`,`rec_owner`,`rec_change_by`,`content_operatingSystem`,`status_isCompressed`,`status_isSecure`,`status_isOptimzed`,`status_isValid`,`status_isReady`,`status_isDeleted`,`status_isLastOfficialVersion`,`cond_hasLicenseRestriction`,`cond_hasInternetRestriction`,`cond_minRAM`,`cond_minCPUs`,`image_isTemplate`,`image_filesize`,`image_syncMode`)VALUES('" + + log.info(" - Query is: " + + "INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`image_description`,`image_path`,`image_lastCall`,`image_create_time`,`image_update_time`,`image_owner`,`image_change_by`,`rec_create_time`,`rec_change_time`,`rec_owner`,`rec_change_by`,`content_operatingSystem`,`status_isCompressed`,`status_isSecure`,`status_isOptimzed`,`status_isValid`,`status_isReady`,`status_isDeleted`,`status_isLastOfficialVersion`,`cond_hasLicenseRestriction`,`cond_hasInternetRestriction`,`cond_minRAM`,`cond_minCPUs`,`image_isTemplate`,`image_filesize`,`image_syncMode`)VALUES('" + uid // GUID_imageID + "',1,'" // imageVersion + imagename // image_name - // Ticket #1144 ist hier zu lösen, desc sollte nicht zu lang sein, sonst merkert die DB - + "','" +desc //image_description + // Ticket #1144 ist hier zu lösen, desc sollte nicht zu lang + // sein, sonst merkert die DB + + "','" + desc // image_description + "','" + imagePath // image_path + "','" + formatter.format(new Date()) // image_lastCall + "','" + formatter.format(new Date()) // image_create_time @@ -284,17 +288,18 @@ public class SQL { + "','" + internet_bol // cond_hasInternetRestriction + "','" + ram // cond_minRAM + "','" + cpu // cond_minCPUs - + "','0'" //image_isTemplate - + ",'" + filesize //image_filesize - + "','" + shareMode //shareMode + + "','0'" // image_isTemplate + + ",'" + filesize // image_filesize + + "','" + shareMode // shareMode + "');"); - + stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`image_description`,`image_path`,`image_lastCall`,`image_create_time`,`image_update_time`,`image_owner`,`image_change_by`,`rec_create_time`,`rec_change_time`,`rec_owner`,`rec_change_by`,`content_operatingSystem`,`status_isCompressed`,`status_isSecure`,`status_isOptimzed`,`status_isValid`,`status_isReady`,`status_isDeleted`,`status_isLastOfficialVersion`,`cond_hasLicenseRestriction`,`cond_hasInternetRestriction`,`cond_minRAM`,`cond_minCPUs`,`image_isTemplate`,`image_filesize`,`image_syncMode`)VALUES('" + uid // GUID_imageID + "',1,'" // imageVersion + imagename // image_name - // Ticket #1144 ist hier zu lösen, desc sollte nicht zu lang sein, sonst merkert die DB - + "','" +desc //image_description + // Ticket #1144 ist hier zu lösen, desc sollte nicht zu lang + // sein, sonst merkert die DB + + "','" + desc // image_description + "','" + imagePath // image_path + "','" + formatter.format(new Date()) // image_lastCall + "','" + formatter.format(new Date()) // image_create_time @@ -317,9 +322,9 @@ public class SQL { + "','" + internet_bol // cond_hasInternetRestriction + "','" + ram // cond_minRAM + "','" + cpu // cond_minCPUs - + "','0'" //image_isTemplate - + ",'" + filesize //image_filesize - + "','" + shareMode //shareMode + + "','0'" // image_isTemplate + + ",'" + filesize // image_filesize + + "','" + shareMode // shareMode + "');"); con.commit(); con.close(); @@ -333,106 +338,216 @@ public class SQL { } public List getImageListPermissionWrite(String userID) { - - Connection con=getConnection(); - Statement stm; - - List list = new ArrayList(); - try { - - stm = con.createStatement(); - - //ResultSet res=stm.executeQuery("SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name,os.architecture, '' as lecture,vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.pm_VLData_image as pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and vl.image_owner=u.userID and vl.image_name not in (SELECT vl.image_name FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID) union SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name,os.architecture, lect.name as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID;"); - ResultSet res = stm.executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE pmi.userID='"+userID+"' AND u.userID = pmi.userID AND pmi.image_write = 1 AND pmi.GUID_imageID = vl.GUID_imageID AND vl.content_operatingSystem=os.operatingSystemID;"); - - while (res.next()) { - list.add(new Image(res.getString("GUID_imageID"), res - .getString("imageVersion"), - res.getString("image_name"), res - .getString("cond_hasLicenseRestriction"), res - .getString("name")+" "+res.getString("architecture")+" bit", res.getString("lecture"), - res.getString("image_update_time"), res - .getString("user"), res.getString("image_isTemplate"))); - } - con.close(); - } catch (SQLException e) { - // TODO Auto-generated catch block - e.printStackTrace(); - } - return list; + + Connection con = getConnection(); + Statement stm; + + List list = new ArrayList(); + try { + + stm = con.createStatement(); + + // ResultSet + // res=stm.executeQuery("SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name,os.architecture, '' as lecture,vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.pm_VLData_image as pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and vl.image_owner=u.userID and vl.image_name not in (SELECT vl.image_name FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID) union SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name,os.architecture, lect.name as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID;"); + ResultSet res = stm + .executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE pmi.userID='" + + userID + + "' AND u.userID = pmi.userID AND pmi.image_write = 1 AND pmi.GUID_imageID = vl.GUID_imageID AND vl.content_operatingSystem=os.operatingSystemID;"); + + while (res.next()) { + list.add(new Image(res.getString("GUID_imageID"), res + .getString("imageVersion"), + res.getString("image_name"), res + .getString("cond_hasLicenseRestriction"), res + .getString("name") + + " " + + res.getString("architecture") + " bit", res + .getString("lecture"), res + .getString("image_update_time"), res + .getString("user"), res + .getString("image_isTemplate"))); + } + con.close(); + } catch (SQLException e) { + // TODO Auto-generated catch block + e.printStackTrace(); + } + return list; } - - + public List getImageListPermissionRead(String userID) { - - Connection con=getConnection(); + + Connection con = getConnection(); Statement stm; - + List list = new ArrayList(); try { stm = con.createStatement(); - //ResultSet res=stm.executeQuery("SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name,os.architecture, '' as lecture,vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.pm_VLData_image as pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and vl.image_owner=u.userID and vl.image_name not in (SELECT vl.image_name FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID) union SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name,os.architecture, lect.name as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID;"); - ResultSet res = stm.executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE pmi.userID='"+userID+"' AND u.userID = pmi.userID AND pmi.image_read = 1 AND pmi.GUID_imageID = vl.GUID_imageID AND vl.content_operatingSystem=os.operatingSystemID;"); - + // ResultSet + // res=stm.executeQuery("SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name,os.architecture, '' as lecture,vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.pm_VLData_image as pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and vl.image_owner=u.userID and vl.image_name not in (SELECT vl.image_name FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID) union SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name,os.architecture, lect.name as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID;"); + ResultSet res = stm + .executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE pmi.userID='" + + userID + + "' AND u.userID = pmi.userID AND pmi.image_read = 1 AND pmi.GUID_imageID = vl.GUID_imageID AND vl.content_operatingSystem=os.operatingSystemID;"); + while (res.next()) { - list.add(new Image(res.getString("GUID_imageID"), res - .getString("imageVersion"), - res.getString("image_name"), res - .getString("cond_hasLicenseRestriction"), res - .getString("name")+" "+res.getString("architecture")+" bit", res.getString("lecture"), - res.getString("image_update_time"), res - .getString("user"), res.getString("image_isTemplate"))); - } + list.add(new Image(res.getString("GUID_imageID"), res + .getString("imageVersion"), + res.getString("image_name"), res + .getString("cond_hasLicenseRestriction"), res + .getString("name") + + " " + + res.getString("architecture") + " bit", res + .getString("lecture"), res + .getString("image_update_time"), res + .getString("user"), res + .getString("image_isTemplate"))); + } con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); - } + } return list; } - - -public List getImageListPermissionLink(String userID) { - - Connection con=getConnection(); + + public List getImageListPermissionLink(String userID) { + + Connection con = getConnection(); Statement stm; - + List list = new ArrayList(); try { stm = con.createStatement(); - //ResultSet res=stm.executeQuery("SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name,os.architecture, '' as lecture,vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.pm_VLData_image as pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and vl.image_owner=u.userID and vl.image_name not in (SELECT vl.image_name FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID) union SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name,os.architecture, lect.name as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID;"); - ResultSet res = stm.executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE pmi.userID='"+userID+"' AND u.userID = pmi.userID AND pmi.link_allowed = 1 AND pmi.GUID_imageID = vl.GUID_imageID AND vl.content_operatingSystem=os.operatingSystemID;"); - + // ResultSet + // res=stm.executeQuery("SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name,os.architecture, '' as lecture,vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.pm_VLData_image as pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and vl.image_owner=u.userID and vl.image_name not in (SELECT vl.image_name FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID) union SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name,os.architecture, lect.name as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID;"); + ResultSet res = stm + .executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, os.name, os.architecture, '' as lecture, vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user, vl.image_isTemplate FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE pmi.userID='" + + userID + + "' AND u.userID = pmi.userID AND pmi.link_allowed = 1 AND pmi.GUID_imageID = vl.GUID_imageID AND vl.content_operatingSystem=os.operatingSystemID;"); + while (res.next()) { - list.add(new Image(res.getString("GUID_imageID"), res - .getString("imageVersion"), - res.getString("image_name"), res - .getString("cond_hasLicenseRestriction"), res - .getString("name")+" "+res.getString("architecture")+" bit", res.getString("lecture"), - res.getString("image_update_time"), res - .getString("user"), res.getString("image_isTemplate"))); - } + list.add(new Image(res.getString("GUID_imageID"), res + .getString("imageVersion"), + res.getString("image_name"), res + .getString("cond_hasLicenseRestriction"), res + .getString("name") + + " " + + res.getString("architecture") + " bit", res + .getString("lecture"), res + .getString("image_update_time"), res + .getString("user"), res + .getString("image_isTemplate"))); + } con.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); - } + } return list; } + + public List getLectureListPermissionRead(String userID) { + List list = new ArrayList(); + + try { + 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 " + + "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+";"); + while (res.next()) { + + list.add(new Lecture(res.getString("lectureID"), res + .getString("name"), res.getString("isActive"), res + .getString("startTime"), res.getString("endTime"), res + .getString("lastUsed"), res.getString("description"), + res.getString("image_name"), res.getString("user"))); + + } + con.close(); + return list; + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to getLectureListPermissionRead."); + e.printStackTrace(); + } + return list; + }//end getLectureListPermissionRead + + + public List getLectureListPermissionWrite(String userID) { + List list = new ArrayList(); + + try { + 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 " + + "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+";"); + while (res.next()) { + + list.add(new Lecture(res.getString("lectureID"), res + .getString("name"), res.getString("isActive"), res + .getString("startTime"), res.getString("endTime"), res + .getString("lastUsed"), res.getString("description"), + res.getString("image_name"), res.getString("user"))); + + } + con.close(); + return list; + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to getLectureListPermissionWrite."); + e.printStackTrace(); + } + return list; + }//end getLectureListPermissionWrite + public List getLectureListPermissionAdmin(String userID) { + List list = new ArrayList(); + + try { + 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 " + + "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+";"); + while (res.next()) { + + list.add(new Lecture(res.getString("lectureID"), res + .getString("name"), res.getString("isActive"), res + .getString("startTime"), res.getString("endTime"), res + .getString("lastUsed"), res.getString("description"), + res.getString("image_name"), res.getString("user"))); + + } + con.close(); + return list; + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to getLectureListPermissionAdmin."); + e.printStackTrace(); + } + return list; + }//end getLectureListPermissionAdmin + public List getLectureList() { List list = new ArrayList(); try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); - ResultSet res=stm + 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 FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.m_user u where i.GUID_imageID=l.imageID and l.admin_owner=u.userID;"); while (res.next()) { @@ -456,15 +571,16 @@ public List getImageListPermissionLink(String userID) { public List getAllOS() { List list = new ArrayList<>(); try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); - ResultSet rs=stm + ResultSet rs = stm .executeQuery("SELECT name, architecture FROM bwLehrpool.m_operatingSystem;"); while (rs.next()) { - list.add(rs.getString("name")+" "+rs.getString("architecture")+" bit"); + list.add(rs.getString("name") + " " + + rs.getString("architecture") + " bit"); } return list; - + } catch (SQLException e) { // TODO Auto-generated catch block log.info(new Date() + " - Failed to getAllOS."); @@ -475,13 +591,12 @@ public List getImageListPermissionLink(String userID) { } - public Map getPersonData(String Vorname, - String Nachname) { + public Map getPersonData(String Vorname, String Nachname) { Map map = new HashMap(); try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); - ResultSet rs= stm + ResultSet rs = stm .executeQuery("SELECT u.Nachname, u.Vorname, u.mail, i.name FROM bwLehrpool.m_user u, bwLehrpool.m_institution i where u.Nachname like '" + Nachname + "' and u.Vorname like '" @@ -516,9 +631,9 @@ public List getImageListPermissionLink(String userID) { active_bol = 1; } try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); - + String uid = UUID.randomUUID().toString(); stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_VLData_lecture`(`lectureID`,`name`,`isActive`,`startTime`,`endTime`,`lastUsed`,`shortDescription`,`description`,`imageID`,`imageVersion`,`admin_createTime`,`admin_changeTime`,`admin_owner`,`admin_change_by`)VALUES('" + uid @@ -559,17 +674,18 @@ public List getImageListPermissionLink(String userID) { } - public Map getImageIDandVersion(String name) { + public Map getImageIDandVersion(String name) { Map map = new HashMap(); try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); - ResultSet image= stm + ResultSet image = stm .executeQuery("SELECT GUID_imageID, imageVersion FROM bwLehrpool.m_VLData_imageInfo where image_name like '" + name + "';"); while (image.next()) { map.put("GUID", image.getString("GUID_imageID")); - map.put("version", String.valueOf(image.getString("imageVersion"))); + map.put("version", + String.valueOf(image.getString("imageVersion"))); } con.close(); @@ -582,13 +698,14 @@ public List getImageListPermissionLink(String userID) { return null; } - public Map getImageData(String id, String version) { + public Map getImageData(String id, String version) { Map map = new HashMap(); try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); - ResultSet data=stm - .executeQuery("SELECT u.mail, i.GUID_imageID, i.imageVersion, i.image_name,i.image_description,i.image_update_time,i.cond_hasInternetRestriction,i.cond_hasLicenseRestriction, i.cond_minCPUs,i.cond_minRAM, i.image_syncMode,i.image_filesize,i.image_path, i.image_isTemplate,os.GuestOS, concat(u.Nachname,' ',u.Vorname) as Owner FROM bwLehrpool.m_VLData_imageInfo i,bwLehrpool.m_user u,bwLehrpool.m_operatingSystem os where i.image_owner=u.userID and i.content_operatingSystem=os.operatingSystemID and GUID_imageID = '" + id + "' and imageVersion = '" + version + "' ;"); + ResultSet data = stm + .executeQuery("SELECT u.mail, i.GUID_imageID, i.imageVersion, i.image_name,i.image_description,i.image_update_time,i.cond_hasInternetRestriction,i.cond_hasLicenseRestriction, i.cond_minCPUs,i.cond_minRAM, i.image_syncMode,i.image_filesize,i.image_path, i.image_isTemplate,os.GuestOS, concat(u.Nachname,' ',u.Vorname) as Owner FROM bwLehrpool.m_VLData_imageInfo i,bwLehrpool.m_user u,bwLehrpool.m_operatingSystem os where i.image_owner=u.userID and i.content_operatingSystem=os.operatingSystemID and GUID_imageID = '" + + id + "' and imageVersion = '" + version + "' ;"); while (data.next()) { map.put("name", data.getString("image_name")); map.put("internet", @@ -600,7 +717,7 @@ public List getImageListPermissionLink(String userID) { map.put("filesize", data.getString("image_filesize")); map.put("path", data.getString("image_path")); map.put("template", data.getString("image_isTemplate")); - map.put("os",data.getString("GuestOS")); + map.put("os", data.getString("GuestOS")); map.put("owner", data.getString("Owner")); map.put("lastupdate", data.getString("image_update_time")); map.put("desc", data.getString("image_description")); @@ -619,11 +736,12 @@ public List getImageListPermissionLink(String userID) { return null; } - public int UpdateImageData(String name, String newName,String desc,String image_path, - boolean license, boolean internet, long cpu, long ram, String id, - String version, boolean isTemplate, long filesize, String shareMode,String ospk) { + public int UpdateImageData(String name, String newName, String desc, + String image_path, boolean license, boolean internet, long cpu, + long ram, String id, String version, boolean isTemplate, + long filesize, String shareMode, String ospk) { try { - Connection con=getConnection(); + Connection con = getConnection(); DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); Statement stm = con.createStatement(); int newVersion = Integer.parseInt(version) + 1; @@ -636,43 +754,30 @@ public List getImageListPermissionLink(String userID) { if (license == true) { license_bol = 1; } - log.info(new Date() + " - 'image_name` = '" + newName+ "\n length of name="+newName.length()); - + log.info(new Date() + " - 'image_name` = '" + newName + + "\n length of name=" + newName.length()); + log.info("now in UpdateImageData()"); log.info("UPDATE bwLehrpool.m_VLData_imageInfo SET imageVersion = " - + newVersion - + ",image_name = '" - + newName - + "',`image_description` = '" - + desc - + "',`image_path` = '" - + image_path - + "',`image_update_time` = '" + + newVersion + ",image_name = '" + newName + + "',`image_description` = '" + desc + "',`image_path` = '" + + image_path + "',`image_update_time` = '" + + formatter.format(new Date()) + "',`rec_change_time` = '" + formatter.format(new Date()) - + "',`rec_change_time` = '" - + formatter.format(new Date()) - + "',`cond_hasLicenseRestriction` = '" - + license_bol - + "',`cond_hasInternetRestriction` = '" - + internet_bol + + "',`cond_hasLicenseRestriction` = '" + license_bol + + "',`cond_hasInternetRestriction` = '" + internet_bol + "',`cond_minRAM` = '" + ram + "',`cond_minCPUs` = '" + cpu + "',`image_isTemplate` = '" - //+ isTemplate - + "0'" - + ",`content_operatingSystem` = " - + ospk - + ",`image_filesize` = " - + filesize - + ",`image_syncMode` = '" - + shareMode - + "' WHERE GUID_imageID = '" - + id - + "' AND imageVersion = " + version + ";"); - - + // + isTemplate + + "0'" + ",`content_operatingSystem` = " + ospk + + ",`image_filesize` = " + filesize + + ",`image_syncMode` = '" + shareMode + + "' WHERE GUID_imageID = '" + id + "' AND imageVersion = " + + version + ";"); + stm.executeUpdate("UPDATE bwLehrpool.m_VLData_imageInfo SET imageVersion = " + newVersion + ",image_name = '" @@ -694,7 +799,7 @@ public List getImageListPermissionLink(String userID) { + "',`cond_minCPUs` = '" + cpu + "',`image_isTemplate` = '" - //+ isTemplate + // + isTemplate + "0'" + "',`content_operatingSystem` = " + ospk @@ -704,8 +809,9 @@ public List getImageListPermissionLink(String userID) { + shareMode + " WHERE GUID_imageID = '" + id - + "' AND imageVersion = " + version + ";"); - + + "' AND imageVersion = " + + version + ";"); + con.commit(); con.close(); return 0; @@ -720,7 +826,7 @@ public List getImageListPermissionLink(String userID) { public boolean deleteImage(String id, String version) { try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); stm.executeUpdate("DELETE FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID = '" @@ -739,10 +845,9 @@ public List getImageListPermissionLink(String userID) { return false; } - public int updateLectureData(String pk_image, - String imageversion, String name, String newName, String desc, - String shortdesc, String start, String end, boolean isactive, - String id) { + public int updateLectureData(String pk_image, String imageversion, + String name, String newName, String desc, String shortdesc, + String start, String end, boolean isactive, String id) { DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); int active_bol = 0; @@ -751,7 +856,7 @@ public List getImageListPermissionLink(String userID) { active_bol = 1; } try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_lecture` SET `name` = '" + newName @@ -787,13 +892,13 @@ public List getImageListPermissionLink(String userID) { public boolean connectedToLecture(String id, String version) { try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); ResultSet rs = stm.executeQuery("SELECT lectureID FROM " + "bwLehrpool.m_VLData_lecture WHERE imageID = '" + id + "' AND imageVersion = '" + version + "';"); - boolean ret=rs.first(); + boolean ret = rs.first(); con.close(); return ret; @@ -811,11 +916,11 @@ public List getImageListPermissionLink(String userID) { public boolean deleteLecture(String id) { try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); stm.executeUpdate("DELETE FROM bwLehrpool.m_VLData_lecture WHERE lectureID = '" + id + "';"); - + con.commit(); con.close(); return true; @@ -831,7 +936,7 @@ public List getImageListPermissionLink(String userID) { public String getFile(String imageid, String imageversion) { try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); ResultSet rs = stm @@ -841,7 +946,7 @@ public List getImageListPermissionLink(String userID) { + imageversion + "';"); rs.next(); - String path=rs.getString("image_path"); + String path = rs.getString("image_path"); con.close(); return path; @@ -854,10 +959,10 @@ public List getImageListPermissionLink(String userID) { return null; } - public Map getDeleteXMLData(String id) { + public Map getDeleteXMLData(String id) { Map map = new HashMap(); try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); ResultSet rs = stm @@ -867,7 +972,6 @@ public List getImageListPermissionLink(String userID) { map.put("date", rs.getString("admin_createTime").replace(" ", "") .replace("-", "").replace(":", "")); map.put("name", rs.getString("name")); - con.close(); return map; @@ -880,22 +984,24 @@ public List getImageListPermissionLink(String userID) { return null; } - + public int UpdateImagePath(String name) { try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); - String image_path="prod/"+name; - - log.info(new Date() + " - Updating ImagePath with: UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `image_path` = '" - + image_path - + "' WHERE `image_path` = '" - + "temp/"+name + "';"); - + String image_path = "prod/" + name; + + log.info(new Date() + + " - Updating ImagePath with: UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `image_path` = '" + + image_path + "' WHERE `image_path` = '" + "temp/" + name + + "';"); + stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `image_path` = '" + image_path + "' WHERE `image_path` = '" - + "temp/"+name + "';"); + + "temp/" + + name + + "';"); con.commit(); con.close(); return 0; @@ -906,19 +1012,19 @@ public List getImageListPermissionLink(String userID) { } return -1; } - + public List getAllUniversities() { List list = new ArrayList<>(); try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); - ResultSet rs=stm + ResultSet rs = stm .executeQuery("SELECT name FROM bwLehrpool.m_institution;"); while (rs.next()) { list.add(rs.getString("name")); } return list; - + } catch (SQLException e) { // TODO Auto-generated catch block log.info(new Date() + " - Failed to getAllUniversities."); @@ -928,24 +1034,24 @@ public List getImageListPermissionLink(String userID) { return null; } - - public String getOSpk(String os, - String architecture) { + + public String getOSpk(String os, String architecture) { try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); - log.info(new Date() + " - Getting PrimaryKey for OS: "+os+" architecture: "+architecture); - ResultSet rs=stm + log.info(new Date() + " - Getting PrimaryKey for OS: " + os + + " architecture: " + architecture); + ResultSet rs = stm .executeQuery("SELECT operatingSystemID FROM bwLehrpool.m_operatingSystem where name like '" + os + "' and architecture like '" + architecture + "';"); rs.first(); - String pkos=rs.getString("operatingSystemID"); - log.info(new Date() + " - Key is "+pkos); + String pkos = rs.getString("operatingSystemID"); + log.info(new Date() + " - Key is " + pkos); con.close(); - return pkos; + return pkos; } catch (SQLException e) { // TODO Auto-generated catch block @@ -955,20 +1061,21 @@ public List getImageListPermissionLink(String userID) { return null; } - + public String getOS(String pk) { try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); - //log.info(new Date() + " - OS: "+os+" architecture: "+architecture); - ResultSet rs=stm + // log.info(new Date() + + // " - OS: "+os+" architecture: "+architecture); + ResultSet rs = stm .executeQuery("SELECT name,architecture FROM bwLehrpool.m_operatingSystem where operatingSystemID = '" - + pk - + "';"); + + pk + "';"); rs.first(); - String os=rs.getString("name")+" "+rs.getString("architecture"); + String os = rs.getString("name") + " " + + rs.getString("architecture"); con.close(); - return os; + return os; } catch (SQLException e) { // TODO Auto-generated catch block @@ -978,18 +1085,18 @@ public List getImageListPermissionLink(String userID) { return null; } - - public Map getLectureData(String id) { + + public Map getLectureData(String id) { Map map = new HashMap(); try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); - ResultSet data=stm - .executeQuery("select l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_path, l.imageVersion, concat(u.Nachname,' ',u.Vorname) as owner from m_VLData_lecture l, m_user u, m_VLData_imageInfo i where l.admin_owner=u.userID and l.imageID=i.GUID_imageID and lectureID= '" + id + "' ;"); + ResultSet data = stm + .executeQuery("select l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_path, l.imageVersion, concat(u.Nachname,' ',u.Vorname) as owner from m_VLData_lecture l, m_user u, m_VLData_imageInfo i where l.admin_owner=u.userID and l.imageID=i.GUID_imageID and lectureID= '" + + id + "' ;"); while (data.next()) { map.put("name", data.getString("name")); - map.put("isActive", - data.getString("isActive")); + map.put("isActive", data.getString("isActive")); map.put("startTime", data.getString("startTime")); map.put("endTime", data.getString("endTime")); map.put("lastUsed", data.getString("lastUsed")); @@ -1008,42 +1115,42 @@ public List getImageListPermissionLink(String userID) { } return null; } - + public boolean checkUser(String username) { Statement stm; - boolean ret=false; + boolean ret = false; try { - Connection con=getConnection(); + Connection con = getConnection(); stm = con.createStatement(); - ResultSet rs=stm.executeQuery("Select * from m_user where loginName like '"+username+"'"); - if(rs.next()) - { - ret=true; - log.info(new Date() + " - User " + rs.getString("loginName") + " exist."); - } - else{ - ret=false; - log.info(new Date() + " - User " + rs.getString("loginName") + " not exist."); + ResultSet rs = stm + .executeQuery("Select * from m_user where loginName like '" + + username + "'"); + if (rs.next()) { + ret = true; + log.info(new Date() + " - User " + rs.getString("loginName") + + " exist."); + } else { + ret = false; + log.info(new Date() + " - User " + rs.getString("loginName") + + " not exist."); } con.close(); - - + } catch (SQLException e) { // TODO Auto-generated catch block - + e.printStackTrace(); } return ret; } - - public int setImageRights(String pk_person, String pk_image,int role ,int read, int write,int linkallowed, int admin) { - + public int setImageRights(String pk_person, String pk_image, int role, + int read, int write, int linkallowed, int admin) { try { - Connection con=getConnection(); + 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 @@ -1060,7 +1167,7 @@ public List getImageListPermissionLink(String userID) { + "','" + admin + "','" - + linkallowed+ "');"); + + linkallowed + "');"); con.commit(); con.close(); } catch (SQLException e) { @@ -1071,18 +1178,19 @@ public List getImageListPermissionLink(String userID) { return 0; } + public int getRoleID(String role) { - try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); - - ResultSet rs=stm - .executeQuery("SELECT roleID FROM bwLehrpool.m_role where name like '"+ role+ "';"); + + ResultSet rs = stm + .executeQuery("SELECT roleID FROM bwLehrpool.m_role where name like '" + + role + "';"); rs.first(); - return rs.getInt("roleID"); + return rs.getInt("roleID"); } catch (SQLException e) { // TODO Auto-generated catch block @@ -1091,14 +1199,14 @@ public List getImageListPermissionLink(String userID) { } return -1; } - public int setLectureRights(String pk_person, String pk_lecture,int role ,int read, int write, int changePermission, int admin) { - + public int setLectureRights(String pk_person, String pk_lecture, int role, + int read, int write, int changePermission, int admin) { try { - Connection con=getConnection(); + 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('" + uid @@ -1114,8 +1222,7 @@ public List getImageListPermissionLink(String userID) { + write + "','" + admin - + "','" - + changePermission+ "');"); + + "','" + changePermission + "');"); con.commit(); con.close(); } catch (SQLException e) { @@ -1126,17 +1233,18 @@ public List getImageListPermissionLink(String userID) { return 0; } + public String getLectureID(String name) { - String id=null; + String id = null; try { - Connection con=getConnection(); + Connection con = getConnection(); Statement stm = con.createStatement(); - ResultSet lecture= stm + ResultSet lecture = stm .executeQuery("SELECT lectureID FROM bwLehrpool.m_VLData_lecture where name like '" + name + "';"); while (lecture.next()) { - id=lecture.getString("lectureID"); - + id = lecture.getString("lectureID"); + } con.close(); return id; @@ -1147,16 +1255,16 @@ public List getImageListPermissionLink(String userID) { } return id; } - - //get all users from satellite -- temp, tb replaced by allUsers bwLehrpool - //does -NOT- return the user himself - public List getAllOtherSatelliteUsers(String userID){ - Connection con=getConnection(); - Statement stm=null; + + // get all users from satellite -- temp, tb replaced by allUsers bwLehrpool + // does -NOT- return the user himself + public List getAllOtherSatelliteUsers(String userID) { + Connection con = getConnection(); + Statement stm = null; ResultSet res = null; List list = new ArrayList(); - + try { stm = con.createStatement(); } catch (SQLException e) { @@ -1164,34 +1272,38 @@ public List getImageListPermissionLink(String userID) { e.printStackTrace(); } try { - res = stm.executeQuery("SELECT userID, Nachname, Vorname FROM m_user WHERE userID !='"+userID+"' ORDER BY Nachname ASC;"); + res = stm + .executeQuery("SELECT userID, Nachname, Vorname FROM m_user WHERE userID !='" + + userID + "' ORDER BY Nachname ASC;"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } try { while (res.next()) { - //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)); + // 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)); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } 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; + }// 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 { stm = con.createStatement(); } catch (SQLException e) { @@ -1199,72 +1311,72 @@ public List getImageListPermissionLink(String userID) { e.printStackTrace(); } 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!='"+userID+"' AND pmi.GUID_imageID='"+imageID+"' AND pmi.userID=u.userID AND pmi.image_write=1 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!='"+userID+"' AND pmi.GUID_imageID='"+imageID+"' AND pmi.userID=u.userID AND pmi.image_write=1 ORDER BY u.Nachname ASC;"); + 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!='" + + 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!='" + + userID + + "' AND pmi.GUID_imageID='" + + imageID + + "' AND pmi.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; - + 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 + // 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("image_read")) != 0){ + link_allowed = false; + image_admin = false; + lecture_read = false; + lecture_write = false; + lecture_admin = false; + + if (Integer.parseInt(res.getString("image_read")) != 0) { image_read = true; } - if(Integer.parseInt(res.getString("image_write")) != 0){ - image_write=true; + if (Integer.parseInt(res.getString("image_write")) != 0) { + image_write = true; } - if(Integer.parseInt(res.getString("link_allowed")) != 0){ - link_allowed=true; + if (Integer.parseInt(res.getString("link_allowed")) != 0) { + link_allowed = true; } - if(Integer.parseInt(res.getString("image_admin")) != 0 ){ - image_admin=true; + if (Integer.parseInt(res.getString("image_admin")) != 0) { + image_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"), - //Boolean.parseBoolean(Integer.parseInt(res.getString("image_read"))), - image_read, - image_write, - link_allowed, - image_admin, - false, //lecture_read - false, //lecture_write - false) //lecture_admin + + // 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 + false, // lecture_write + false) // 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"))); + // 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 getPermissionFOrUserAndImage() - - - - - - - - - public boolean updateImagePermissions(){ - boolean success=true; - - Connection con=getConnection(); - Statement stm=null; + }// end getPermissionFOrUserAndImage() + + public boolean updateImagePermissions() { + boolean success = true; + + Connection con = getConnection(); + Statement stm = null; try { stm = con.createStatement(); stm.executeQuery(""); @@ -1272,15 +1384,15 @@ public List getImageListPermissionLink(String userID) { // TODO Auto-generated catch block e.printStackTrace(); } - + return success; }// end setImagePermissions() - - public boolean deleteImagePermissions(){ - boolean success=true; - - Connection con=getConnection(); - Statement stm=null; + + public boolean deleteImagePermissions() { + boolean success = true; + + Connection con = getConnection(); + Statement stm = null; try { stm = con.createStatement(); stm.executeQuery(""); @@ -1288,59 +1400,37 @@ public List getImageListPermissionLink(String userID) { // TODO Auto-generated catch block e.printStackTrace(); } - + return success; }// end setImagePermissions() - - public boolean writeAdditionalImageRights(String imageID, String userID, boolean isRead, boolean isWrite, boolean isLinkAllowed, boolean isAdmin){ - boolean success= true; - + + public boolean writeAdditionalImageRights(String imageID, String userID, + boolean isRead, boolean isWrite, boolean isLinkAllowed, + boolean isAdmin) { + boolean success = true; + String uid = UUID.randomUUID().toString(); - Connection con=getConnection(); - Statement stm=null; - + Connection con = getConnection(); + Statement stm = null; + 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 = 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 + "" + + ");"); con.commit(); con.close(); log.info(new Date() + " - Written additional image rights."); @@ -1349,27 +1439,27 @@ public List getImageListPermissionLink(String userID) { e.printStackTrace(); log.info(new Date() + " - Failed to write additional image rights."); } - - + return success; - - }// end - public void deleteAllAdditionalImagePermissions(String imageID,String userID) - { - Connection con=getConnection(); + }// end + + public void deleteAllAdditionalImagePermissions(String imageID, + String userID) { + Connection con = getConnection(); Statement stm = null; - - - try { - - stm = con.createStatement(); - int ret = stm.executeUpdate("DELETE FROM pm_VLData_image WHERE GUID_imageID = '" + imageID + "' AND userID != '" + userID + "';"); - - } catch (SQLException e) { - // TODO Auto-generated catch block - e.printStackTrace(); - } + + try { + + stm = con.createStatement(); + int ret = stm + .executeUpdate("DELETE FROM pm_VLData_image WHERE GUID_imageID = '" + + imageID + "' AND userID != '" + userID + "';"); + + } catch (SQLException e) { + // TODO Auto-generated catch block + e.printStackTrace(); + } } - -}//end class + +}// end class -- cgit v1.2.3-55-g7522