From f9904d9a361d139ab3fe14f099b341fcc6cc1b63 Mon Sep 17 00:00:00 2001 From: Michael Wilson Date: Tue, 18 Nov 2014 13:26:57 +0100 Subject: • Beim Bearbeiten eines Image oder einer Veranstaltung wurde bisher der aktuell eingeloggte Benutzer immer neu als Verantwortlicher eingetragen --> Der Verantwortliche ändert sich nun nicht mehr und wird korrekt angezeigt --- dozentenmodulserver/src/main/java/sql/SQL.java | 208 ++++++++++++++++++------- 1 file changed, 151 insertions(+), 57 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 10afee96..dd40c774 100644 --- a/dozentenmodulserver/src/main/java/sql/SQL.java +++ b/dozentenmodulserver/src/main/java/sql/SQL.java @@ -36,7 +36,7 @@ public class SQL { Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } try { @@ -48,7 +48,7 @@ public class SQL { return con; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to return connection to Client."); e.printStackTrace(); } @@ -78,7 +78,7 @@ public class SQL { + "."); return ret; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to writeFTPUser."); e.printStackTrace(); } @@ -113,7 +113,7 @@ public class SQL { log.info("FTPUser " + user + " deleted."); return ret; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to DeleteUser."); e.printStackTrace(); } @@ -136,7 +136,7 @@ public class SQL { return rs; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getImage."); e.printStackTrace(); } @@ -177,7 +177,7 @@ public class SQL { return path; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getPathOfImage."); e.printStackTrace(); } @@ -251,7 +251,7 @@ public class SQL { } } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to setInstitution."); e.printStackTrace(); } @@ -378,7 +378,7 @@ public class SQL { } } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to setPerson."); e.printStackTrace(); } @@ -507,7 +507,7 @@ public class SQL { //con.commit(); con.close(); } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to setImageData."); e.printStackTrace(); } @@ -563,7 +563,7 @@ public class SQL { } con.close(); } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } return list; @@ -618,7 +618,7 @@ public class SQL { } con.close(); } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } return list; @@ -661,7 +661,7 @@ public class SQL { } con.close(); } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } return list; @@ -722,7 +722,7 @@ public class SQL { } con.close(); } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } return list; @@ -779,7 +779,7 @@ public class SQL { } con.close(); } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } return list; @@ -824,7 +824,7 @@ public class SQL { } con.close(); } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } return list; @@ -870,7 +870,7 @@ public class SQL { con.close(); return list; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getLectureListPermissionRead."); e.printStackTrace(); } @@ -920,7 +920,7 @@ public class SQL { con.close(); return list; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getLectureListPermissionWrite."); e.printStackTrace(); } @@ -969,7 +969,7 @@ public class SQL { con.close(); return list; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getLectureListPermissionAdmin."); e.printStackTrace(); } @@ -1005,7 +1005,7 @@ public class SQL { con.close(); return list; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getLectureList."); e.printStackTrace(); } @@ -1030,7 +1030,7 @@ public class SQL { return list; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getAllOS."); e.printStackTrace(); } @@ -1081,15 +1081,104 @@ public class SQL { con.close(); return map; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getPersonData."); e.printStackTrace(); } return null; - } + }//end getPersonData + + + + + public Map getPersonData(String userID) { + Map map = new HashMap(); + try { + Connection con = getConnection(); + String sql = "SELECT userID, loginName, Nachname, Vorname, mail, m_institution.name as institution FROM bwLehrpool.m_user, m_institution WHERE userID=? AND m_user.institution=m_institution.institutionID;"; + + PreparedStatement prest = con.prepareStatement(sql); + prest.setString(1, userID); + + ResultSet rs = prest.executeQuery(); + con.commit(); + while (rs.next()) + { + map.put("userID", rs.getString("userID")); + map.put("loginName", rs.getString("loginName")); + map.put("Nachname", rs.getString("Nachname")); + map.put("Vorname", rs.getString("Vorname")); + map.put("mail", rs.getString("mail")); + map.put("institution", rs.getString("institution")); + + } + con.close(); + return map; + } catch (SQLException e) { + log.info("Failed to getPersonData with userID='"+userID+"'."); + e.printStackTrace(); + } + return null; + }//end getPersonData + + + + public Map getItemOwner(String itemID) { + + Map map = new HashMap(); + Connection con = getConnection(); + PreparedStatement prest; + String userID; + + try { + //find out if the itemID belongs to an image + String sql = "SELECT image_owner FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID=?;"; + prest = con.prepareStatement(sql); + prest.setString(1, itemID); + + ResultSet rs = prest.executeQuery(); + while (rs.next()){ + map.put("userID",rs.getString("image_owner")); + } + + //if map is empty, then itemID does not belong to an image -> must be a lecture + if(map.isEmpty()==true){ + //check if itemID belongs to a lecture + sql = "SELECT admin_owner FROM bwLehrpool.m_VLData_lecture WHERE lectureID=?;"; + prest = con.prepareStatement(sql); + prest.setString(1, itemID); + + rs = prest.executeQuery(); + while(rs.next()){ + map.put("userID", rs.getString("admin_owner")); + } + } + + //if map is still empty, then the itemID could not be found + if(map.isEmpty()==true){ + log.error("ERROR: getItemOwner could not find the item with the ID '"+itemID+"'."); + } else { + + //now that we have the userID for this item, get the user information and put it in the map + userID = map.get("userID"); //extract the userID + map = getPersonData(userID); //get all the rest of his information + + //return this map with all the infos + return map; + + } + + } catch (SQLException e) { + e.printStackTrace(); + } + + //something went horribly wrong... + return null; + }//end getItemOwner + @@ -1191,7 +1280,7 @@ public class SQL { //con.commit(); con.close(); } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to setLectureData."); e.printStackTrace(); } @@ -1229,7 +1318,7 @@ public class SQL { con.close(); return map; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getImageIDandVersion."); e.printStackTrace(); } @@ -1284,7 +1373,7 @@ public class SQL { con.close(); return map; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getImageData."); e.printStackTrace(); } @@ -1437,7 +1526,7 @@ public class SQL { con.close(); return 0; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to UpdateImageData."); e.printStackTrace(); } @@ -1475,7 +1564,7 @@ public class SQL { return true; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to deleteImage and permissions."); e.printStackTrace(); } @@ -1556,7 +1645,7 @@ public class SQL { log.info("Succeeded to updateLectureData."); } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to updateLectureData."); e.printStackTrace(); } @@ -1586,7 +1675,7 @@ public class SQL { return ret; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to execute method connectedToLecture."); e.printStackTrace(); } @@ -1619,7 +1708,7 @@ public class SQL { con.close(); return true; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to deleteLecture and permissions."); e.printStackTrace(); } @@ -1663,7 +1752,7 @@ public class SQL { return path; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getFile."); e.printStackTrace(); } @@ -1698,7 +1787,7 @@ public class SQL { return map; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getDeleteXMLData."); e.printStackTrace(); } @@ -1733,7 +1822,7 @@ public class SQL { log.info("Succesfully updated image path"); return 0; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to UpdateImagePath."); e.printStackTrace(); } @@ -1755,7 +1844,7 @@ public class SQL { return list; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getAllUniversities."); e.printStackTrace(); } @@ -1788,7 +1877,7 @@ public class SQL { return pkos; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getOSpk."); e.printStackTrace(); } @@ -1821,7 +1910,7 @@ public class SQL { return os; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getOS."); e.printStackTrace(); } @@ -1859,7 +1948,7 @@ public class SQL { con.close(); return map; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getLectureData."); e.printStackTrace(); } @@ -1896,7 +1985,7 @@ public class SQL { con.close(); } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } @@ -1928,7 +2017,7 @@ public class SQL { return rs.getInt("roleID"); } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to getRoleID."); e.printStackTrace(); } @@ -1958,7 +2047,7 @@ public class SQL { con.close(); return id; } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to LectureID."); e.printStackTrace(); } @@ -1980,7 +2069,7 @@ public class SQL { try { stm = con.createStatement(); } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } String query = "SELECT userID, Nachname, Vorname, mail FROM m_user WHERE userID NOT IN ('" @@ -1994,7 +2083,7 @@ public class SQL { try { res = stm.executeQuery(query); } catch (SQLException e1) { - // TODO Auto-generated catch block + e1.printStackTrace(); } @@ -2008,7 +2097,7 @@ public class SQL { false, false)); } } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } return list; @@ -2034,7 +2123,7 @@ public class SQL { try { stm = con.createStatement(); } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } try { @@ -2067,7 +2156,7 @@ public class SQL { + imageID + "' AND pmi.userID=u.userID ORDER BY u.Nachname ASC;");*/ } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } try { @@ -2108,7 +2197,7 @@ public class SQL { // 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; @@ -2150,7 +2239,7 @@ public class SQL { "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 { @@ -2195,7 +2284,7 @@ public class SQL { // 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; @@ -2251,7 +2340,7 @@ public class SQL { con.close(); } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to setImageRights."); e.printStackTrace(); } @@ -2304,7 +2393,7 @@ public class SQL { log.info("Written additional image rights."); } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); log.info("Failed to write additional image rights."); } @@ -2349,7 +2438,7 @@ public class SQL { log.info("Written lecture rights for " + pk_lecture); con.close(); } catch (SQLException e) { - // TODO Auto-generated catch block + log.info("Failed to setLectureRights."); e.printStackTrace(); } @@ -2397,7 +2486,7 @@ public class SQL { log.info("Written additional lecture rights for '" + userID + "'."); } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); log.info("Failed to write additional lecture rights."); } @@ -2425,7 +2514,7 @@ public class SQL { prest.executeUpdate(); } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } @@ -2437,7 +2526,7 @@ public class SQL { con.commit(); } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } }// end deleteAllAdditionalImagePermissions @@ -2471,7 +2560,7 @@ public class SQL { con.commit(); } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } }// end deleteAllAdditionalImagePermissions @@ -2504,7 +2593,7 @@ public class SQL { } } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } @@ -2534,13 +2623,18 @@ public class SQL { return rs.getString("name") + " " + rs.getString("architecture") + " bit"; } catch (SQLException e) { - // TODO Auto-generated catch block + e.printStackTrace(); } return null; } + + + + + }// end class -- cgit v1.2.3-55-g7522