h

1 files changed, 69 insertions, 31 deletions

diff --git a/dozentenmodulserver/src/main/java/sql/SQL.java b/dozentenmodulserver/src/main/java/sql/SQL.java
index 7ec99092..83bd2ccb 100644
--- a/dozentenmodulserver/src/main/java/sql/SQL.java
+++ b/dozentenmodulserver/src/main/java/sql/SQL.java
@@ -351,26 +351,27 @@ public class SQL {
 

 			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.pm_VLData_image as pmi, 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;");

 			ResultSet res = stm

-					.executeQuery("SELECT DISTINCT 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, vl.image_description FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE pmi.userID='"

+					.executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture,  '' as lecture, vl.image_update_time,  Concat(u.Nachname,' ',u.Vorname) as user,  vl.image_isTemplate, vl.image_description FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = '"

 							+ userID

-							+ "' AND u.userID = pmi.userID AND pmi.image_write = 1 AND pmi.GUID_imageID = vl.GUID_imageID AND vl.content_operatingSystem=os.operatingSystemID;");

+							+ "'AND pmi.image_write = 1 AND pmi.GUID_imageID = vl.GUID_imageID;");

+

 

+			

 			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"),

-						res.getString("image_description")));

+				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"),

+								res.getString("image_description"),

+								res.getString("image_filesize")));

 			}

 			con.close();

 		} catch (SQLException e) {

@@ -392,9 +393,9 @@ public class SQL {
 

 			// ResultSet

 			ResultSet res = stm

-					.executeQuery("SELECT DISTINCT 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, vl.image_description FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE pmi.userID='"

+					.executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture,  '' as lecture, vl.image_update_time,  Concat(u.Nachname,' ',u.Vorname) as user,  vl.image_isTemplate, vl.image_description FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = '"

 							+ userID

-							+ "' AND u.userID = pmi.userID AND pmi.image_read = 1 AND pmi.GUID_imageID = vl.GUID_imageID AND vl.content_operatingSystem=os.operatingSystemID;");

+							+ "'AND pmi.image_read = 1 AND pmi.GUID_imageID = vl.GUID_imageID;");

 

 			while (res.next()) {

 				list.add(new Image(res.getString("GUID_imageID"), res

@@ -408,7 +409,8 @@ public class SQL {
 								.getString("image_update_time"), res

 								.getString("user"), res

 								.getString("image_isTemplate"),

-								res.getString("image_description")));

+								res.getString("image_description"),

+								res.getString("image_filesize")));

 			}

 			con.close();

 		} catch (SQLException e) {

@@ -429,9 +431,12 @@ public class SQL {
 			stm = con.createStatement();

 

 			// ResultSet

+			/*

 			ResultSet res = stm

-					.executeQuery("SELECT DISTINCT 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, vl.image_description FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID");

-					

+					.executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture,  '' as lecture, vl.image_update_time,  vl.image_owner as user,  vl.image_isTemplate, vl.image_description FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID");

+				*/

+			ResultSet res = stm

+					.executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture,  '' as lecture, vl.image_update_time,  Concat(u.Nachname,' ',u.Vorname) as user,  vl.image_isTemplate, vl.image_description FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID;"); 

 

 			while (res.next()) {

 				list.add(new Image(res.getString("GUID_imageID"), res

@@ -445,7 +450,8 @@ public class SQL {
 								.getString("image_update_time"), res

 								.getString("user"), res

 								.getString("image_isTemplate"),

-								res.getString("image_description")));

+								res.getString("image_description"),

+								res.getString("image_filesize")));

 			}

 			con.close();

 		} catch (SQLException e) {

@@ -473,7 +479,9 @@ public class SQL {
 

 			// ResultSet WITH templates

 						ResultSet res = stm

-								.executeQuery("SELECT DISTINCT vl.GUID_imageID,	vl.imageVersion, vl.image_name, vl.image_description, 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.pm_VLData_image pmi, bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u WHERE (vl.image_isTemplate=1 OR (pmi.userID='"+userID+"' AND pmi.link_allowed=1)) AND vl.image_owner=u.userID AND pmi.GUID_imageID = vl.GUID_imageID AND vl.content_operatingSystem=os.operatingSystemID;");

+								.executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture,  '' as lecture, vl.image_update_time,  Concat(u.Nachname,' ',u.Vorname) as user,  vl.image_isTemplate, vl.image_description FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = '"

+							+ userID

+							+ "'AND pmi.link_allowed = 1 AND pmi.GUID_imageID = vl.GUID_imageID;");

 			

 			while (res.next()) {

 				list.add(new Image(

@@ -486,7 +494,8 @@ public class SQL {
 						res.getString("image_update_time"),

 						res.getString("user"),

 						res.getString("image_isTemplate"),

-						res.getString("image_description")));

+						res.getString("image_description"),

+						res.getString("image_filesize")));

 			}

 			con.close();

 		} catch (SQLException e) {

@@ -508,11 +517,9 @@ public class SQL {
 

 			// ResultSet

 			ResultSet res = stm

-
-					.executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name,vl.image_description, 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.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE pmi.userID='"

-
+					.executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture,  '' as lecture, vl.image_update_time,  Concat(u.Nachname,' ',u.Vorname) as user,  vl.image_isTemplate, vl.image_description FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE u.userID = vl.image_owner AND vl.content_operatingSystem = os.operatingSystemID AND pmi.userID = '"

 							+ userID

-							+ "' AND u.userID = pmi.userID AND pmi.image_admin = 1 AND pmi.GUID_imageID = vl.GUID_imageID AND vl.content_operatingSystem=os.operatingSystemID;");

+							+ "'AND pmi.image_admin = 1 AND pmi.GUID_imageID = vl.GUID_imageID;");

 

 			while (res.next()) {

 				list.add(new Image(

@@ -525,7 +532,8 @@ public class SQL {
 						res.getString("image_update_time"),

 						res.getString("user"),

 						res.getString("image_isTemplate"),

-						res.getString("image_description")

+						res.getString("image_description"),

+						res.getString("image_filesize")

 						)

 				);

 			}

@@ -550,7 +558,7 @@ public class SQL {
 

 			// ResultSet

 			ResultSet res = stm

-					.executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.image_description, 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.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE vl.image_isTemplate=1 AND vl.content_operatingSystem=os.operatingSystemID AND vl.image_owner=u.userID;");

+					.executeQuery("SELECT DISTINCT vl.GUID_imageID, vl.imageVersion, vl.image_name, vl.image_description, vl.cond_hasLicenseRestriction, vl.image_filesize, os.name, os.architecture,  '' as lecture, vl.image_update_time,  Concat(u.Nachname,' ',u.Vorname) as user,  vl.image_isTemplate FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE vl.image_isTemplate=1 AND vl.content_operatingSystem=os.operatingSystemID AND vl.image_owner=u.userID;");

 

 			while (res.next()) {

 				list.add(new Image(

@@ -563,7 +571,8 @@ public class SQL {
 						res.getString("image_update_time"),

 						res.getString("user"),

 						res.getString("image_isTemplate"),

-						res.getString("image_description")

+						res.getString("image_description"),

+						res.getString("image_filesize")

 						)

 				);

 			}

@@ -1757,6 +1766,35 @@ public class SQL {
 			e.printStackTrace();

 		}

 	}// end deleteAllAdditionalImagePermissions

+

+	public List<String> getAdditionalImageContacts(String imageID)

+	{

+		

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

+	

+			Connection con = getConnection();

+			Statement stm;

+			try {

+				stm = con.createStatement();

+				

+				ResultSet rs = stm.executeQuery("select distinct u.Nachname, u.Vorname, u.mail from bwLehrpool.pm_VLData_image img, bwLehrpool.m_user u where GUID_imageID = '"+imageID+"' and image_write = '1' and img.userID = u.userID;");

+			

+				while (rs.next()) 

+				{

+					//also possible with mail, not used due to size issues

+					//list.add(rs.getString("Nachname") + "," + rs.getString("Vorname") + " " + rs.getString("mail"));

+					list.add(rs.getString("Nachname") + "," + rs.getString("Vorname")+" ");

+				}		

+				

+			} catch (SQLException e) {

+				// TODO Auto-generated catch block

+				e.printStackTrace();

+			}

+

+			return list;

+		

+

+	}

 	

 

 }// end class