From b8ef5d05efbc773eb87844911e8b103ce5acfca8 Mon Sep 17 00:00:00 2001 From: Nino Breuer Date: Tue, 11 Nov 2014 16:49:04 +0100 Subject: h --- dozentenmodulserver/src/main/java/sql/SQL.java | 100 +++++++++++++++++-------- 1 file changed, 69 insertions(+), 31 deletions(-) (limited to 'dozentenmodulserver/src/main/java/sql') 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 getAdditionalImageContacts(String imageID) + { + + List 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 -- cgit v1.2.3-55-g7522