diff options
Diffstat (limited to 'Dozentenmodulserver/src/sql/SQL.java')
| -rw-r--r-- | Dozentenmodulserver/src/sql/SQL.java | 142 |
1 files changed, 142 insertions, 0 deletions
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;
+
+ }
}
|