SQL für VL funktioniert

6 files changed, 228 insertions, 8 deletions

diff --git a/Dozentenmodulserver/bin/server/ServerInterface.class b/Dozentenmodulserver/bin/server/ServerInterface.class
index 411a676d..146aa871 100644
--- a/Dozentenmodulserver/bin/server/ServerInterface.class
+++ b/Dozentenmodulserver/bin/server/ServerInterface.class
diff --git a/Dozentenmodulserver/bin/server/ServerMethod.class b/Dozentenmodulserver/bin/server/ServerMethod.class
index 4b4096d1..01b5288c 100644
--- a/Dozentenmodulserver/bin/server/ServerMethod.class
+++ b/Dozentenmodulserver/bin/server/ServerMethod.class
diff --git a/Dozentenmodulserver/bin/sql/SQL.class b/Dozentenmodulserver/bin/sql/SQL.class
index 245013e2..cb55c2de 100644
--- a/Dozentenmodulserver/bin/sql/SQL.class
+++ b/Dozentenmodulserver/bin/sql/SQL.class
diff --git a/Dozentenmodulserver/src/server/ServerInterface.java b/Dozentenmodulserver/src/server/ServerInterface.java
index bce713c0..49d7adb9 100644
--- a/Dozentenmodulserver/src/server/ServerInterface.java
+++ b/Dozentenmodulserver/src/server/ServerInterface.java
@@ -2,6 +2,7 @@ package server;
 import java.rmi.*;

 import java.sql.ResultSet;

 import java.util.ArrayList;

+import java.util.List;

 

 import Models.User;

 

@@ -11,5 +12,7 @@ public interface ServerInterface extends Remote
         public int DeleteFtpUser(String user) throws RemoteException;

         public ArrayList<String> getImages() throws RemoteException;

         public String getPathOfImage(String name) throws RemoteException;

-        

+        public boolean writeVLdata(String imagename, String firstname, String lastname,String university, String Mail, String Tel, String Fak, boolean license, boolean internet, int ram, int cpu) throws RemoteException;

+        public List<Object[]> getImageList() throws RemoteException;

+        public List<String> getAllOS() throws RemoteException;

 }

diff --git a/Dozentenmodulserver/src/server/ServerMethod.java b/Dozentenmodulserver/src/server/ServerMethod.java
index 1039e238..1f66fb6b 100644
--- a/Dozentenmodulserver/src/server/ServerMethod.java
+++ b/Dozentenmodulserver/src/server/ServerMethod.java
@@ -7,8 +7,11 @@ import java.security.MessageDigest;
 import java.security.NoSuchAlgorithmException;

 import java.sql.Connection;

 import java.sql.ResultSet;

+import java.sql.ResultSetMetaData;

 import java.sql.SQLException;

 import java.util.ArrayList;

+import java.util.Date;

+import java.util.List;

 import java.util.UUID;

 import Models.*;

 import sql.SQL;

@@ -17,7 +20,8 @@ import sql.SQL;
 public class ServerMethod extends UnicastRemoteObject implements ServerInterface

 {

     protected static String          m_strName;

- 

+    SQL sql=new SQL();

+    Connection con=sql.getConnection();

 

     public ServerMethod() throws RemoteException

     {

@@ -63,8 +67,8 @@ public class ServerMethod extends UnicastRemoteObject implements ServerInterface
 

 	@Override

 	public int DeleteFtpUser(String user) throws RemoteException {

-		SQL sql=new SQL();

-		Connection con=sql.getConnection();

+		

+		

 		int ret = sql.DeleteUser(con, user);

 		return ret;

 	}

@@ -86,8 +90,7 @@ public class ServerMethod extends UnicastRemoteObject implements ServerInterface
 	public ArrayList<String> getImages(){

 		

 		

-		SQL sql=new SQL();

-		Connection con=sql.getConnection();

+

 		ResultSet rs= sql.getImage(con);

 		ArrayList<String> al=new ArrayList<String>();

 		try {

@@ -107,8 +110,7 @@ public class ServerMethod extends UnicastRemoteObject implements ServerInterface
 	public String getPathOfImage(String name){

 		

 		String path = null;

-		SQL sql=new SQL();

-		Connection con=sql.getConnection();

+

 		ResultSet rs= sql.getPathOfImage(con, name);

 		

 		try {

@@ -125,4 +127,77 @@ public class ServerMethod extends UnicastRemoteObject implements ServerInterface
 		return path;

 	}

 

+	@Override

+	public boolean writeVLdata(String imagename, String firstname,

+			String lastname, String university, String Mail, String Tel,

+			String Fak, boolean license, boolean internet, int ram, int cpu)

+			throws RemoteException {

+		String login="tete";

+		System.out.println("1");

+		int pk_institution=sql.setInstitution(con, university);

+		System.out.println("2");

+		int pk_person=sql.setPerson(con, login, lastname, firstname, Mail, new Date(), pk_institution);

+		System.out.println("3");

+		sql.setImageData(con, pk_person, license, internet, cpu, ram, imagename);

+		System.out.println("4");

+		// TODO Auto-generated method stub

+		return true;

+	}

+

+

+

+	@Override

+	public List<Object[]> getImageList() throws RemoteException {

+		

+		ResultSet resWith=sql.getImageListWithLectures(con);

+		ResultSet resWithout=sql.getImageListWithoutLectures(con);

+		 

+		try {

+			List<Object[]> listWith = ResSetToObject(resWith);

+			List<Object[]> listWithout=ResSetToObject(resWithout);

+			listWith.addAll(listWith.size(), listWithout);

+			return listWith;

+		} catch (SQLException e1) {

+			// TODO Auto-generated catch block

+			e1.printStackTrace();

+		}

+

+		return null;

+	}

+

+	public List<Object[]> ResSetToObject(ResultSet res) throws SQLException

+	{

+		

+		ResultSetMetaData rsmd = res.getMetaData();

+		List<Object[]> list=new ArrayList<>();

+		while(res.next()){

+			 Object[] objects = new Object[rsmd.getColumnCount()];

+			 // tanks to umit ozkan for the bug fix!

+			 for(int i=0;i<rsmd.getColumnCount();i++){

+			  objects[i]=res.getObject(i+1);

+			  }

+			 list.add(objects);

+			 

+	}

+		return list;

+	}

+

+

+

+	@Override

+	public List<String> getAllOS() throws RemoteException {

+		List<String> list=new ArrayList<>();

+		ResultSet rs=sql.getAllOS(con);

+		try {

+			while(rs.next())

+			{

+				list.add(rs.getString("name"));

+			}

+			return list;

+		} catch (SQLException e) {

+			// TODO Auto-generated catch block

+			e.printStackTrace();

+		}

+		return null;

+	}

 }
\ No newline at end of file
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;

+		

+	}

 }