From 75df905c97bc8bb4def5a3a6a87f301e364bc2ab Mon Sep 17 00:00:00 2001 From: tspitzer Date: Tue, 18 Feb 2014 13:08:48 +0100 Subject: SQL für VL funktioniert --- Dozentenmodulserver/src/sql/SQL.java | 142 +++++++++++++++++++++++++++++++++++ 1 file changed, 142 insertions(+) (limited to 'Dozentenmodulserver/src/sql') diff --git a/Dozentenmodulserver/src/sql/SQL.java b/Dozentenmodulserver/src/sql/SQL.java index 7d990116..af99b36b 100644 --- a/Dozentenmodulserver/src/sql/SQL.java +++ b/Dozentenmodulserver/src/sql/SQL.java @@ -5,6 +5,10 @@ import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; +import java.text.DateFormat; +import java.text.SimpleDateFormat; +import java.util.Date; +import java.util.Random; public class SQL { @@ -94,4 +98,142 @@ public class SQL { return null; } + + public int setInstitution(Connection con,String university) + { + try { + Statement stm=con.createStatement(); + + + + ResultSet ret=stm.executeQuery("SELECT * FROM bwLehrpool.m_institution where name like'"+university+"';"); + if(ret.next()==false) + { + Random rand=new Random(); + int id=rand.nextInt(); + 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(); + return rs.getInt("institutionID"); + } + else + { + return ret.getInt("institutionID"); + } + + } catch (SQLException e) { + // TODO Auto-generated catch block + e.printStackTrace(); + } + return -1; + } + + + + public int setPerson(Connection con,String login, String lastname, String firstname, String mail, Date lastlogin,int Institution) + { + DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); + try { + 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) + { + Random rand=new Random(); + int id=rand.nextInt(); + stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES('"+id+"','Platzhalter','"+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(); + return rs.getInt("userID"); + } + else + { + return ret.getInt("userID"); + } + + } catch (SQLException e) { + // TODO Auto-generated catch block + e.printStackTrace(); + } + return -1; + } + + public int setImageData(Connection con, int pk_person, boolean license, boolean internet, int cpu, int ram, String imagename) + { + + DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); + + int internet_bol=0; + int license_bol=0; + if(internet==true) + { + internet_bol=1; + } + else if(license==true) + { + license_bol=1; + } + + try { + Statement stm=con.createStatement(); + Random random=new Random(); + int uid=random.nextInt(); + stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`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`)VALUES('"+uid+"',1,'"+imagename+"','/srv/openslx/nfs/temp/','"+formatter.format(new Date())+"','"+formatter.format(new Date())+"','"+formatter.format(new Date())+"','"+pk_person+"','"+pk_person+"','"+formatter.format(new Date())+"','"+formatter.format(new Date())+"','"+pk_person+"','"+pk_person+"',1,1,1,1,1,1,0,0,'"+license_bol+"','"+internet_bol+"','"+ram+"','"+cpu+"');"); + con.commit(); + + } catch (SQLException e) { + // TODO Auto-generated catch block + e.printStackTrace(); + } + return ram; + + } + + public ResultSet getImageListWithoutLectures(Connection con){ + try { + Statement stm=con.createStatement(); + + return stm.executeQuery("SELECT distinct vl.image_name, vl.cond_hasLicenseRestriction, os.name, vl.image_update_time, '', Concat(u.Nachname,' ',u.Vorname) 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.lectureID!=vl.GUID_imageID and vl.image_owner=u.userID;"); + + + } catch (SQLException e) { + // TODO Auto-generated catch block + e.printStackTrace(); + } + return null; + } + + public ResultSet getImageListWithLectures(Connection con){ + try { + Statement stm=con.createStatement(); + + return stm.executeQuery("SELECT vl.image_name, vl.cond_hasLicenseRestriction, os.name, vl.image_update_time, lect.name, Concat(u.Nachname,' ',u.Vorname) 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;"); + + + } catch (SQLException e) { + // TODO Auto-generated catch block + e.printStackTrace(); + } + return null; + } + + public ResultSet getAllOS(Connection con) + { + + + try { + Statement stm=con.createStatement(); + return stm.executeQuery("SELECT name FROM bwLehrpool.m_operatingSystem;"); + } catch (SQLException e) { + // TODO Auto-generated catch block + e.printStackTrace(); + } + + + + return null; + + } } -- cgit v1.2.3-55-g7522