From e90286005f1cbedc77bfff91f867cb6aec25a595 Mon Sep 17 00:00:00 2001 From: Jonathan Bauer Date: Thu, 11 Sep 2014 15:04:50 +0200 Subject: oops --- dozentenmodulserver/src/main/java/sql/SQL.java | 1013 ++++++++++++++++++++++++ 1 file changed, 1013 insertions(+) create mode 100644 dozentenmodulserver/src/main/java/sql/SQL.java (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 new file mode 100644 index 00000000..9a115f09 --- /dev/null +++ b/dozentenmodulserver/src/main/java/sql/SQL.java @@ -0,0 +1,1013 @@ +package sql; + +import java.sql.*; + +import java.text.DateFormat; +import java.text.ParseException; +import java.text.SimpleDateFormat; +import java.util.ArrayList; +import java.util.Calendar; +import java.util.Date; +import java.util.HashMap; +import java.util.List; +import java.util.Map; +import java.util.Random; +import java.util.UUID; + +import models.Configuration; + +import org.apache.log4j.Logger; +import server.BinaryListener; +import server.Image; +import server.Lecture; + +public class SQL { + + private static Logger log = Logger.getLogger(BinaryListener.class); + + public Connection getConnection() { + try { + Class.forName("com.mysql.jdbc.Driver").newInstance(); + } catch (InstantiationException | IllegalAccessException + | ClassNotFoundException e) { + // TODO Auto-generated catch block + e.printStackTrace(); + } + try { + 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 + log.info(new Date() + " - Failed to return connection to Client."); + e.printStackTrace(); + } + return null; + } + + public int writeFTPUser(String user, String pass) { + Statement stm; + try { + Connection con=getConnection(); + stm = con.createStatement(); + + int ret = stm + .executeUpdate("INSERT INTO `bwLehrpool`.`FtpUsers`(`User`,`Password`,`Uid`,`Gid`,`Dir`)VALUES('" + + user + + "',SHA1('" + + pass + + "'),'10001','12345','"+Configuration.config.getAbsolute_path()+"temp/');"); + con.commit(); + con.close(); + log.info(new Date() + " - created FTPUser " + user + " : " + pass + + "."); + return ret; + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to writeFTPUser."); + e.printStackTrace(); + } + return -1; + } + + public int DeleteUser(String user) { + Statement stm; + try { + Connection con=getConnection(); + stm = con.createStatement(); + + int ret = stm + .executeUpdate("DELETE FROM `bwLehrpool`.`FtpUsers` where User like '" + + user + "';"); + con.commit(); + con.close(); + log.info(new Date() + " - FTPUser " + user + " deleted."); + return ret; + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to DeleteUser."); + e.printStackTrace(); + } + return -1; + } + + public ResultSet getImage() { + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + ResultSet rs=stm + .executeQuery("SELECT image_name FROM bwLehrpool.m_VLData_imageInfo;"); + con.close(); + return rs; + + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to getImage."); + e.printStackTrace(); + } + + return null; + } + + public String getPathOfImage(String image_id, + String version) { + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + 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"); + con.close(); + return path; + + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to getPathOfImage."); + e.printStackTrace(); + } + + return null; + } + + public String setInstitution(String university) { + try { + 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 + "');"); + con.commit(); + ResultSet rs = stm + .executeQuery("SELECT institutionID FROM bwLehrpool.m_institution WHERE name like '" + + university + "';"); + rs.next(); + String institution=rs.getString("institutionID"); + con.close(); + + return institution; + } else { + String institution=ret.getString("institutionID"); + con.close(); + return institution; + } + + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to setInstitution."); + e.printStackTrace(); + } + return "-1"; + } + + 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(); + Statement stm = con.createStatement(); + + ResultSet ret = stm + .executeQuery("SELECT userID FROM bwLehrpool.m_user where Nachname like '" + + lastname + + "' and Vorname like '" + + firstname + + "';"); + + if (ret.next() == false) { + + String id = UUID.randomUUID().toString(); + stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES('" + + id + + "','" + + login + + "','" + + lastname + + "','" + + firstname + + "','" + + mail + + "','" + + formatter.format(new Date()) + + "','" + + Institution + + "');"); + con.commit(); + ResultSet rs = stm + .executeQuery("SELECT userID FROM bwLehrpool.m_user where Nachname like '" + + lastname + + "' and Vorname like '" + + firstname + + "';"); + rs.next(); + String userid=rs.getString("userID"); + con.close(); + return userid; + } else { + ret.first(); + String userid=ret.getString("userID"); + con.close(); + return userid; + } + + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to setPerson."); + e.printStackTrace(); + } + return "-1"; + } + + public boolean setImageData(String pk_person, boolean license, + boolean internet, long cpu, long ram, String imagename,String desc, + String imagePath, boolean isTemplate, long filesize, String shareMode, String pk_os) { + + DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); + + int internet_bol = 0; + int license_bol = 0; + if (internet == true) { + internet_bol = 1; + } + if (license == true) { + license_bol = 1; + } + + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + + String uid = UUID.randomUUID().toString(); + 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 + + "','" +desc //image_description + + "','" + imagePath // image_path + + "','" + formatter.format(new Date()) // image_lastCall + + "','" + formatter.format(new Date()) // image_create_time + + "','" + formatter.format(new Date()) // image_update_time + + "','" + pk_person // image_owner + + "','" + pk_person // image_change_by + + "','" + formatter.format(new Date()) // rec_create_time + + "','" + formatter.format(new Date()) // rec_change_time + + "','" + pk_person // rec_owner + + "','" + pk_person // rec_change_by + + "','" + pk_os // content_operatingSystem + + "',1" // status_isCompressed + + ",1" // status_isSecure + + ",1" // status_isOptimzed + + ",1" // status_isValid + + ",1" // status_isReady + + ",0" // status_isDeleted + + ",0,'" // status_isLastOfficialVersion + + license_bol // cond_hasLicenseRestriction + + "','" + internet_bol // cond_hasInternetRestriction + + "','" + ram // cond_minRAM + + "','" + cpu // cond_minCPUs + + "','" + isTemplate //image_isTemplate + + "','" + filesize //image_filesize + + "','" + shareMode //shareMode + + "');"); + con.commit(); + con.close(); + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to setImageData."); + e.printStackTrace(); + } + return true; + + } + + public List getImageList() { + + 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.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;"); + + + + 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 getLectureList() { + 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.m_user u where i.GUID_imageID=l.imageID and l.admin_owner=u.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 getLectureList."); + e.printStackTrace(); + } + return null; + } + + public List getAllOS() { + List list = new ArrayList<>(); + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + ResultSet rs=stm + .executeQuery("SELECT name, architecture FROM bwLehrpool.m_operatingSystem;"); + while (rs.next()) { + 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."); + e.printStackTrace(); + } + + return null; + + } + + public Map getPersonData(String Vorname, + String Nachname) { + Map map = new HashMap(); + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + 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 '" + + Vorname + + "' and u.institution=i.institutionID;"); + while (rs.next()) { + + map.put("mail", rs.getString("mail")); + map.put("Nachname", rs.getString("Nachname")); + map.put("Vorname", rs.getString("Vorname")); + map.put("Hochschule", rs.getString("name")); + + } + con.close(); + return map; + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to getPersonData."); + e.printStackTrace(); + } + return null; + } + + public int setLectureData(String pk_person, String pk_image, + int imageversion, String name, String desc, String shortdesc, + String start, String end, boolean isactive) { + + DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); + int active_bol = 0; + + if (isactive == true) { + active_bol = 1; + } + try { + 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 + + "','" + + name + + "','" + + active_bol + + "','" + + start + + "','" + + end + + "','" + + formatter.format(new Date()) + + "','" + + shortdesc + + "','" + + desc + + "','" + + pk_image + + "','" + + imageversion + + "','" + + formatter.format(new Date()) + + "','" + + formatter.format(new Date()) + + "','" + + pk_person + + "','" + + pk_person + "');"); + con.commit(); + con.close(); + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to setLectureData."); + e.printStackTrace(); + } + return 0; + + } + + public Map getImageIDandVersion(String name) { + Map map = new HashMap(); + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + 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"))); + + } + con.close(); + return map; + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to getImageIDandVersion."); + e.printStackTrace(); + } + return null; + } + + public Map getImageData(String id, String version) { + Map map = new HashMap(); + try { + 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 + "' ;"); + while (data.next()) { + map.put("name", data.getString("image_name")); + map.put("internet", + data.getString("cond_hasInternetRestriction")); + map.put("license", data.getString("cond_hasLicenseRestriction")); + map.put("cpu", data.getString("cond_minCPUs")); + map.put("ram", data.getString("cond_minRAM")); + map.put("shareMode", data.getString("image_syncMode")); + 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("owner", data.getString("Owner")); + map.put("lastupdate", data.getString("image_update_time")); + map.put("desc", data.getString("image_description")); + map.put("id", data.getString("GUID_imageID")); + map.put("version", data.getString("imageVersion")); + map.put("mail", data.getString("mail")); + + } + con.close(); + return map; + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to getImageData."); + e.printStackTrace(); + } + 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) { + try { + Connection con=getConnection(); + DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); + Statement stm = con.createStatement(); + int newVersion = Integer.parseInt(version) + 1; + + int internet_bol = 0; + int license_bol = 0; + if (internet == true) { + internet_bol = 1; + } + if (license == true) { + license_bol = 1; + } + log.info(new Date() + " - 'image_name` = '" + newName+ "\n length of name="+newName.length()); + + stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `imageVersion` = '" + + 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()) + + "',`cond_hasLicenseRestriction` = '" + + license_bol + + "',`cond_hasInternetRestriction` = '" + + internet_bol + + "',`cond_minRAM` = '" + + ram + + "',`cond_minCPUs` = '" + + cpu + + "',`image_isTemplate` = '" + + isTemplate + + "',`content_operatingSystem` = '" + + ospk + + "',`image_filesize` = '" + + filesize + + "',`image_syncMode` = '" + + shareMode + + "' WHERE `GUID_imageID` = '" + + id + + "' AND `imageVersion` = '" + version + "';"); + con.commit(); + con.close(); + return 0; + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to UpdateImageData."); + e.printStackTrace(); + } + return -1; + } + + public boolean deleteImage(String id, String version) { + + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + + stm.executeUpdate("DELETE FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID = '" + + id + "' " + "AND imageVersion = '" + version + "';"); + + con.commit(); + con.close(); + return true; + + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to deleteImage."); + e.printStackTrace(); + } + + 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) { + + DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); + int active_bol = 0; + + if (isactive == true) { + active_bol = 1; + } + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_lecture` SET `name` = '" + + newName + + "',`isActive` = '" + + active_bol + + "',`startTime` = '" + + start + + "',`endTime` = '" + + end + + "',`description` = '" + + desc + + "',`imageID` = '" + + pk_image + + "',`imageVersion` = '" + + imageversion + + "',`admin_changeTime` = '" + + formatter.format(new Date()) + + "'WHERE `lectureID` = '" + id + "';"); + con.commit(); + con.close(); + log.info(new Date() + " - Succeeded to updateLectureData."); + + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to updateLectureData."); + e.printStackTrace(); + } + + return 0; + + } + + public boolean connectedToLecture(String id, String version) { + + try { + 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(); + con.close(); + return ret; + + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + + " - Failed to execute method connectedToLecture."); + e.printStackTrace(); + } + + return false; + + } + + public boolean deleteLecture(String id) { + + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + stm.executeUpdate("DELETE FROM bwLehrpool.m_VLData_lecture WHERE lectureID = '" + + id + "';"); + + con.commit(); + con.close(); + return true; + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to deleteLecture."); + e.printStackTrace(); + } + + return false; + } + + public String getFile(String imageid, String imageversion) { + + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + + ResultSet rs = stm + .executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = '" + + imageid + + "' AND imageVersion = '" + + imageversion + + "';"); + rs.next(); + String path=rs.getString("image_path"); + con.close(); + return path; + + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to getFile."); + e.printStackTrace(); + } + + return null; + } + + public Map getDeleteXMLData(String id) { + Map map = new HashMap(); + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + + ResultSet rs = stm + .executeQuery("SELECT name, admin_createTime FROM bwLehrpool.m_VLData_lecture where lectureID='" + + id + "';"); + rs.next(); + map.put("date", rs.getString("admin_createTime").replace(" ", "") + .replace("-", "").replace(":", "")); + map.put("name", rs.getString("name")); + + + con.close(); + return map; + + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to getDeleteXMLData."); + e.printStackTrace(); + } + + return null; + } + + public int UpdateImagePath(String name) { + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + String image_path="prod/"+name; + + stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `image_path` = '" + + image_path + + "' WHERE `image_path` = '" + + "temp/"+name + "';"); + con.commit(); + con.close(); + return 0; + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to UpdateImagePath."); + e.printStackTrace(); + } + return -1; + } + + public List getAllUniversities() { + List list = new ArrayList<>(); + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + 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."); + e.printStackTrace(); + } + + return null; + + } + + public String getOSpk(String os, + String architecture) { + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + 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); + con.close(); + return pkos; + + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to getOSpk."); + e.printStackTrace(); + } + + return null; + } + + public String getOS(String pk) { + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + //log.info(new Date() + " - OS: "+os+" architecture: "+architecture); + ResultSet rs=stm + .executeQuery("SELECT name,architecture FROM bwLehrpool.m_operatingSystem where operatingSystemID = '" + + pk + + "';"); + rs.first(); + String os=rs.getString("name")+" "+rs.getString("architecture"); + con.close(); + return os; + + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to getOS."); + e.printStackTrace(); + } + + return null; + } + + public Map getLectureData(String id) { + Map map = new HashMap(); + try { + 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 + "' ;"); + while (data.next()) { + map.put("name", data.getString("name")); + map.put("isActive", + data.getString("isActive")); + map.put("startTime", data.getString("startTime")); + map.put("endTime", data.getString("endTime")); + map.put("lastUsed", data.getString("lastUsed")); + map.put("desc", data.getString("description")); + map.put("imagepath", data.getString("image_path")); + map.put("imageversion", data.getString("imageVersion")); + map.put("owner", data.getString("Owner")); + + } + con.close(); + return map; + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to getLectureData."); + e.printStackTrace(); + } + return null; + } + + public boolean checkUser(String username) { + Statement stm; + boolean ret=false; + try { + 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."); + } + 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 changePermission, int admin,int linkallowed) { + + + + 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`,`image_changePermission`,`link_allowed`)VALUES('" + + uid + + "','" + + pk_image + + "','" + + role + + "','" + + pk_person + + "','" + + read + + "','" + + write + + "','" + + admin + + "','" + + changePermission + + "','" + + linkallowed+ "');"); + con.commit(); + con.close(); + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to setImageRights."); + e.printStackTrace(); + } + return 0; + + } + public int getRoleID(String role) { + + + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + + ResultSet rs=stm + .executeQuery("SELECT roleID FROM bwLehrpool.m_role where name like '"+ role+ "';"); + rs.first(); + + return rs.getInt("roleID"); + + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to getRoleID."); + e.printStackTrace(); + } + return -1; + } + public int setLectureRights(String pk_person, String pk_lecture,int role ,int read, int write, int changePermission, 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('" + + uid + + "','" + + pk_lecture + + "','" + + role + + "','" + + pk_person + + "','" + + read + + "','" + + write + + "','" + + admin + + "','" + + changePermission+ "');"); + con.commit(); + con.close(); + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to setLectureRights."); + e.printStackTrace(); + } + return 0; + + } + public String getLectureID(String name) { + String id=null; + try { + Connection con=getConnection(); + Statement stm = con.createStatement(); + ResultSet lecture= stm + .executeQuery("SELECT lectureID FROM bwLehrpool.m_VLData_lecture where name like '" + + name + "';"); + while (lecture.next()) { + id=lecture.getString("lectureID"); + + } + con.close(); + return id; + } catch (SQLException e) { + // TODO Auto-generated catch block + log.info(new Date() + " - Failed to LectureID."); + e.printStackTrace(); + } + return id; + } +} -- cgit v1.2.3-55-g7522