path: root/dozentenmodulserver/src/main/java/sql/SQL.java

package sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import models.Configuration;

import org.apache.log4j.Logger;
import org.openslx.sat.thrift.iface.Image;
import org.openslx.sat.thrift.iface.Lecture;
import org.openslx.sat.thrift.iface.Person;

import util.Util;

public class SQL {

	private static final Logger log = Logger.getLogger(SQL.class);

	static {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
			log.fatal("Cannot get mysql JDBC driver!", e);
			System.exit(1);
		}
	}

	public Connection getConnection() {
		// TODO: Connection pooling, better yet some abstraction layer for mysql, eg. like dalesbred in master-server
		try {
			Connection con = DriverManager.getConnection(Configuration.getDbUri(),
					Configuration.getDbUsername(), Configuration.getDbPassword());
			con.setAutoCommit(false);

			return con;
		} catch (SQLException e) {

			log.info("Failed to return connection to Client.");
			e.printStackTrace();
		}

		return null;
	}

	// no prepared statement to do here
	public ResultSet getImage() {
		try {
			Connection con = getConnection();
			Statement stm = con.createStatement();
			ResultSet rs = stm.executeQuery("SELECT image_name FROM bwLehrpool.m_VLData_imageInfo;");
			con.close();
			return rs;

		} catch (SQLException e) {

			log.info("Failed to getImage.");
			e.printStackTrace();
		}

		return null;
	}

	public String getPathOfImage(String image_id, String version) {
		try {
			Connection con = getConnection();

			String sql = "SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID= " + "?"
					+ " and imageVersion= " + "?" + ";";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, image_id);
			prest.setString(2, version);

			ResultSet rs = prest.executeQuery();
			con.commit();
			rs.first();
			String path = rs.getString("image_path");
			con.close();
			return path;

		} catch (SQLException e) {

			log.info("Failed to getPathOfImage.");
			e.printStackTrace();
		}

		return null;
	}

	public String setInstitution(String university) {
		// TODO: WTF!?
		// 1) This method is called set* and has a return value (other than boolean)
		// 2) This method generates a random ID for an institution name (which is called university in the
		//    parameter), so every satellite server has a different ID for the same institution
		// 3) If an institution is renamed, it gets a new id, and the old one remains in the db
		// 4) In case it inserts a new institution with an ID, it queries the DB for the ID right
		//    after doing so. So it's expected to be different from what was just inserted!??
		try {
			Connection con = getConnection();

			String sql = "SELECT * FROM bwLehrpool.m_institution where name like ?;";
			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, university);
			ResultSet ret = prest.executeQuery();
			con.commit();

			if (ret.next() == false) {

				String id = UUID.randomUUID().toString();

				sql = "INSERT INTO `bwLehrpool`.`m_institution`(`institutionID`,`name`) VALUES" + "(?,?)";

				prest = con.prepareStatement(sql);
				prest.setString(1, id);
				prest.setString(2, university);
				prest.executeUpdate();

				con.commit();

				sql = "SELECT institutionID FROM bwLehrpool.m_institution WHERE name like ?;";

				prest = con.prepareStatement(sql);
				prest.setString(1, university);

				ResultSet rs = prest.executeQuery();
				con.commit();
				rs.next();
				String institution = rs.getString("institutionID");
				con.close();

				return institution;
			} else {
				String institution = ret.getString("institutionID");
				con.close();
				return institution;
			}

		} catch (SQLException e) {

			log.info("Failed to setInstitution.");
			e.printStackTrace();
		}
		return "-1";
	}

	public String setPerson(String userID, String lastname, String firstname, String mail, Date lastlogin,
			String Institution) {
		DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			Connection con = getConnection();

			String sql = "SELECT userID FROM bwLehrpool.m_user where Nachname like " + "?"
					+ " and Vorname like " + "?" + ";";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, lastname);
			prest.setString(2, firstname);

			ResultSet ret = prest.executeQuery();
			con.commit();
			if (ret.next() == false) {
				log.info("create user statement values are: userID, loginname, lastname, firstname, mail, institution VALUES: "
						+ userID
						+ " "
						+ userID
						+ " "
						+ lastname
						+ " "
						+ firstname
						+ " "
						+ mail
						+ " "
						+ Institution);

				sql = "INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES("
						+ "?"
						+ ","
						+ "?"
						+ ","
						+ "?"
						+ ","
						+ "?"
						+ ","
						+ "?"
						+ ",'"
						+ formatter.format(new Date()) + "'," + "?" + ");";

				prest = con.prepareStatement(sql);
				prest.setString(1, userID);
				prest.setString(2, userID);
				prest.setString(3, lastname);
				prest.setString(4, firstname);
				prest.setString(5, mail);
				prest.setString(6, Institution);

				prest.executeUpdate();

				con.commit();

				// TODO: why... WHY!? We had the user id from the beginning! We just inserted it!
				// Also if there are two users with same first and last name we might get
				// the wrong id! JUST WHY!!!!!?
				sql = "SELECT userID FROM bwLehrpool.m_user where Nachname like " + "?"
						+ " and Vorname like " + "?" + ";";

				prest = con.prepareStatement(sql);
				prest.setString(1, lastname);
				prest.setString(2, firstname);

				ResultSet rs = prest.executeQuery();
				con.commit();
				rs.next();
				String userid = rs.getString("userID");
				con.close();
				return userid;
			} else {
				ret.first();
				String userid = ret.getString("userID");
				con.close();
				return userid;
			}

		} catch (SQLException e) {

			log.info("Failed to setPerson.");
			e.printStackTrace();
		}
		return "-1";
	}

	public boolean writeNewImageData(String pk_person, boolean license, boolean internet, String imagename,
			String desc, String imagePath, long filesize, String shareMode, String pk_os, String imageId) {

		DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		int internet_bol = 0;
		int license_bol = 0;
		if (internet == true) {
			internet_bol = 1;
		}
		if (license == true) {
			license_bol = 1;
		}

		try {
			Connection con = getConnection();

			// create a UUID

			String sql = "INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`image_description`,`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`,`image_isTemplate`,`image_filesize`,`image_syncMode`)VALUES("
					+ "?" // GUID_imageID
					+ ",1," // imageVersion
					+ "?" // image_name
					+ "," + "?" // image_description
					+ "," + "?" // image_path
					+ ",'"
					+ formatter.format(new Date()) // image_lastCall
					+ "','" + formatter.format(new Date()) // image_create_time
					+ "','" + formatter.format(new Date()) // image_update_time
					+ "'," + "?" // image_owner
					+ "," + "?" // image_change_by
					+ ",'" + formatter.format(new Date()) // rec_create_time
					+ "','" + formatter.format(new Date()) // rec_change_time
					+ "'," + "?" // rec_owner
					+ "," + "?" // rec_change_by
					+ "," + "?" // content_operatingSystem
					+ ",1" // status_isCompressed
					+ ",1" // status_isSecure
					+ ",1" // status_isOptimzed
					+ ",1" // status_isValid
					+ ",1" // status_isReady
					+ ",0" // status_isDeleted
					+ ",0," // status_isLastOfficialVersion
					+ "?" // cond_hasLicenseRestriction
					+ "," + "?" // cond_hasInternetRestriction
					+ "," + "?" // cond_minRAM
					+ "," + "?" // cond_minCPUs
					+ ",0" // image_isTemplate
					+ "," + "?" // image_filesize
					+ "," + "?" // shareMode
					+ ");";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, imageId);
			prest.setString(2, imagename);
			prest.setString(3, desc);
			prest.setString(4, imagePath);
			prest.setString(5, pk_person);
			prest.setString(6, pk_person);
			prest.setString(7, pk_person);
			prest.setString(8, pk_person);
			prest.setString(9, pk_os);
			prest.setInt(10, license_bol);
			prest.setInt(11, internet_bol);
			prest.setLong(12, 2);
			prest.setLong(13, 2);
			prest.setLong(14, filesize);
			prest.setString(15, shareMode);

			prest.executeUpdate();
			con.commit();
			con.close();
		} catch (SQLException e) {
			log.info("Failed to insert new image into DB", e);
			return false;
		}
		return true;

	}

	public List<Image> getImageListPermissionWrite(String userID) {

		Connection con = getConnection();

		List<Image> list = new ArrayList<Image>();
		try {

			String sql = "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 = "
					+ "?"
					+ " AND pmi.image_write = 1 AND pmi.GUID_imageID = vl.GUID_imageID ORDER BY vl.image_name;";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, userID);

			ResultSet res = prest.executeQuery();
			con.commit();
			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"),
						res.getString("image_filesize")));
			}
			con.close();
		} catch (SQLException e) {

			e.printStackTrace();
			// TODO: Shouldn't we return null or something that gives the client a chance
			// to detect the failure and notify the user?
		}
		return list;
	}

	public List<Image> getImageListPermissionRead(String userID) {

		Connection con = getConnection();

		List<Image> list = new ArrayList<Image>();
		try {

			String sql = "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 = "
					+ "?"
					+ " AND pmi.image_read = 1 AND pmi.GUID_imageID = vl.GUID_imageID ORDER BY vl.image_name;";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, userID);

			ResultSet res = prest.executeQuery();
			con.commit();
			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"),
						res.getString("image_filesize")));
			}
			con.close();
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return list;
	}

	// no prepared statement to do here
	public List<Image> getImageList(String userID) {

		Connection con = getConnection();
		Statement stm;

		List<Image> list = new ArrayList<Image>();
		try {

			stm = con.createStatement();

			// ResultSet
			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 ORDER BY vl.image_name;");

			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"),
						res.getString("image_filesize")));
			}
			con.close();
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return list;
	}

	public List<Image> getImageListPermissionLink(String userID) {

		Connection con = getConnection();

		List<Image> list = new ArrayList<Image>();
		try {

			String sql = "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 = "
					+ "?"
					+ " AND pmi.link_allowed = 1 AND pmi.GUID_imageID = vl.GUID_imageID ORDER BY vl.image_name;";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, userID);

			ResultSet res = prest.executeQuery();
			con.commit();
			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"),
						res.getString("image_filesize")));
			}
			con.close();
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return list;
	}

	// TODO: OK this method is pretty much the tenth copy&paste version of the stuff above. Maybe refactor something here?
	public List<Image> getImageListPermissionAdmin(String userID) {
		Connection con = getConnection();

		List<Image> list = new ArrayList<Image>();
		try {

			String sql = "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 = "
					+ "?"
					+ " AND pmi.image_admin = 1 AND pmi.GUID_imageID = vl.GUID_imageID ORDER BY vl.image_name;";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, userID);

			ResultSet res = prest.executeQuery();
			con.commit();
			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"),
						res.getString("image_filesize")));
			}
			con.close();
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return list;
	}// end getImageListPermissionAdmin

	// no prepared statement to do here
	public List<Image> getImageListAllTemplates() {
		Connection con = getConnection();
		Statement stm;

		List<Image> list = new ArrayList<Image>();
		try {

			stm = con.createStatement();

			// ResultSet
			ResultSet res = stm.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 ORDER BY vl.image_name;");

			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"),
						res.getString("image_filesize")));
			}
			con.close();
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return list;
	}// end getImageListAllTemplates

	public List<Lecture> getLectureListPermissionRead(String userID) {
		List<Lecture> list = new ArrayList<Lecture>();

		try {
			Connection con = getConnection();

			String sql = "SELECT DISTINCT l.lectureID, l.name,	l.isActive,	l.startTime, l.endTime,	l.lastUsed,	l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user "
					+ "FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u "
					+ "WHERE u.userID = l.admin_owner AND pml.rec_read=1 AND i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID AND pml.userID = ?  ORDER BY l.name;";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, userID);

			ResultSet res = prest.executeQuery();
			con.commit();
			while (res.next()) {

				list.add(new Lecture(res.getString("lectureID"), res.getString("name"),
						res.getString("isActive"), res.getString("startTime"), res.getString("endTime"),
						res.getString("lastUsed"), res.getString("description"), res.getString("image_name"),
						res.getString("user")));

			}
			con.close();
			return list;
		} catch (SQLException e) {

			log.info("Failed to getLectureListPermissionRead.");
			e.printStackTrace();
		}
		return list;
	}// end getLectureListPermissionRead

	public List<Lecture> getLectureListPermissionWrite(String userID) {
		List<Lecture> list = new ArrayList<Lecture>();

		try {
			Connection con = getConnection();

			String sql = "SELECT DISTINCT l.lectureID, l.name,	l.isActive,	l.startTime, l.endTime,	l.lastUsed,	l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user "
					+ "FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u "
					+ "WHERE pml.userID="
					+ "?"
					+ " AND u.userID = l.admin_owner AND pml.rec_write=1 AND i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID ORDER BY l.name;";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, userID);

			ResultSet res = prest.executeQuery();
			con.commit();
			while (res.next()) {

				list.add(new Lecture(res.getString("lectureID"), res.getString("name"),
						res.getString("isActive"), res.getString("startTime"), res.getString("endTime"),
						res.getString("lastUsed"), res.getString("description"), res.getString("image_name"),
						res.getString("user")));

			}
			con.close();
			return list;
		} catch (SQLException e) {

			log.info("Failed to getLectureListPermissionWrite.");
			e.printStackTrace();
		}
		return list;
	}// end getLectureListPermissionWrite

	public List<Lecture> getLectureListPermissionAdmin(String userID) {
		List<Lecture> list = new ArrayList<Lecture>();

		try {
			Connection con = getConnection();

			String sql = "SELECT DISTINCT l.lectureID, l.name,	l.isActive,	l.startTime, l.endTime,	l.lastUsed,	l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user "
					+ "FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u "
					+ "WHERE u.userID = l.admin_owner AND pml.rec_admin=1 AND i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID AND pml.userID= ?  ORDER BY l.name;";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, userID);

			ResultSet res = prest.executeQuery();
			con.commit();
			while (res.next()) {

				list.add(new Lecture(res.getString("lectureID"), res.getString("name"),
						res.getString("isActive"), res.getString("startTime"), res.getString("endTime"),
						res.getString("lastUsed"), res.getString("description"), res.getString("image_name"),
						res.getString("user")));

			}
			con.close();
			return list;
		} catch (SQLException e) {

			log.info("Failed to getLectureListPermissionAdmin.");
			e.printStackTrace();
		}
		return list;
	}// end getLectureListPermissionAdmin

	// No prepared statement to do here
	public List<Lecture> getLectureList() {
		List<Lecture> list = new ArrayList<Lecture>();
		try {
			Connection con = getConnection();
			Statement stm = con.createStatement();
			ResultSet res = stm.executeQuery("SELECT l.lectureID, l.name, l.isActive,l.startTime,l.endTime,l.lastUsed,l.description, i.image_name, concat(u.Nachname,' ',u.Vorname) as user FROM bwLehrpool.m_VLData_lecture l, bwLehrpool.m_VLData_imageInfo i, bwLehrpool.m_user u WHERE i.GUID_imageID=l.imageID and l.admin_owner=u.userID  ORDER BY l.name;");

			while (res.next()) {

				list.add(new Lecture(res.getString("lectureID"), res.getString("name"),
						res.getString("isActive"), res.getString("startTime"), res.getString("endTime"),
						res.getString("lastUsed"), res.getString("description"), res.getString("image_name"),
						res.getString("user")));

			}
			con.close();
			return list;
		} catch (SQLException e) {

			log.info("Failed to getLectureList.");
			e.printStackTrace();
		}
		return null;
	}

	// No prepared statement to do here
	public List<String> getAllOS() {

		// if(authenticated())
		// {
		List<String> list = new ArrayList<>();
		try {
			Connection con = getConnection();
			Statement stm = con.createStatement();
			ResultSet rs = stm.executeQuery("SELECT name, architecture FROM bwLehrpool.m_operatingSystem ORDER BY name, architecture;");
			while (rs.next()) {
				list.add(rs.getString("name") + " " + rs.getString("architecture") + " bit");
			}
			return list;

		} catch (SQLException e) {

			log.info("Failed to getAllOS.");
			e.printStackTrace();
		}

		return null;

		// }
		// return null;
	}

	public Map<String, String> getPersonData(String Vorname, String Nachname) {
		Map<String, String> map = new HashMap<String, String>();
		try {
			Connection con = getConnection();

			String sql = "SELECT u.Nachname, u.Vorname, u.mail, i.name FROM bwLehrpool.m_user u, bwLehrpool.m_institution i where u.Nachname like "
					+ "?" + " and u.Vorname like " + "?" + " and u.institution=i.institutionID;";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, Vorname);
			prest.setString(2, Nachname);

			ResultSet rs = prest.executeQuery();
			con.commit();
			while (rs.next()) {

				map.put("mail", rs.getString("mail"));
				map.put("Nachname", rs.getString("Nachname"));
				map.put("Vorname", rs.getString("Vorname"));
				map.put("Hochschule", rs.getString("name"));

			}
			con.close();
			return map;
		} catch (SQLException e) {

			log.info("Failed to getPersonData.");
			e.printStackTrace();
		}
		return null;
	}// end getPersonData

	public Map<String, String> getPersonData(String userID) {
		Map<String, String> map = new HashMap<String, String>();
		try {
			Connection con = getConnection();

			String sql = "SELECT userID, loginName, Nachname, Vorname, mail, m_institution.name as institution FROM bwLehrpool.m_user, m_institution WHERE userID=? AND m_user.institution=m_institution.institutionID;";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, userID);

			ResultSet rs = prest.executeQuery();
			con.commit();
			while (rs.next()) {
				map.put("userID", rs.getString("userID"));
				map.put("loginName", rs.getString("loginName"));
				map.put("Nachname", rs.getString("Nachname"));
				map.put("Vorname", rs.getString("Vorname"));
				map.put("mail", rs.getString("mail"));
				map.put("institution", rs.getString("institution"));

			}
			con.close();
			return map;
		} catch (SQLException e) {
			log.info("Failed to getPersonData with userID='" + userID + "'.");
			e.printStackTrace();
		}
		return null;
	}// end getPersonData

	public Map<String, String> getItemOwner(String itemID) {

		Map<String, String> map = new HashMap<String, String>();
		Connection con = getConnection();
		PreparedStatement prest;
		String userID;

		try {
			// find out if the itemID belongs to an image
			String sql = "SELECT image_owner FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID=?;";
			prest = con.prepareStatement(sql);
			prest.setString(1, itemID);

			ResultSet rs = prest.executeQuery();
			while (rs.next()) {
				map.put("userID", rs.getString("image_owner"));
			}

			// if map is empty, then itemID does not belong to an image -> must
			// be a lecture
			// TODO: Errr what? Why? Trial and error until we find the right table?
			if (map.isEmpty() == true) {
				// check if itemID belongs to a lecture
				sql = "SELECT admin_owner FROM bwLehrpool.m_VLData_lecture WHERE lectureID=?;";
				prest = con.prepareStatement(sql);
				prest.setString(1, itemID);

				rs = prest.executeQuery();
				while (rs.next()) {
					map.put("userID", rs.getString("admin_owner"));
				}
			}

			// if map is still empty, then the itemID could not be found
			if (map.isEmpty() == true) {
				log.error("Failed to getItemOwner for item '" + itemID + "'.");
			} else {

				// now that we have the userID for this item, get the user
				// information and put it in the map
				userID = map.get("userID"); // extract the userID
				map = getPersonData(userID); // get all the rest of his
												// information

				// return this map with all the infos
				return map;

			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		// something went horribly wrong...
		return null;
	}// end getItemOwner

	public int setLectureData(String pk_person, String pk_image, int imageversion, String name, String desc,
			String shortdesc, String start, String end, boolean isactive, String lectureID) {

		DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		int active_bol = 0;

		if (isactive == true) {
			active_bol = 1;
		}

		try {
			Connection con = getConnection();

			String sql = "INSERT INTO `bwLehrpool`.`m_VLData_lecture`(`lectureID`,`name`,`isActive`,`startTime`,`endTime`,`lastUsed`,`shortDescription`,`description`,`imageID`,`imageVersion`,`admin_createTime`,`admin_changeTime`,`admin_owner`,`admin_change_by`)VALUES("
					+ "?"
					+ ","
					+ "?"
					+ ","
					+ "?"
					+ ","
					+ "?"
					+ ","
					+ "?"
					+ ",'"
					+ formatter.format(new Date())
					+ "',"
					+ "?"
					+ ","
					+ "?"
					+ ","
					+ "?"
					+ ","
					+ "?"
					+ ",'"
					+ formatter.format(new Date())
					+ "','"
					+ formatter.format(new Date())
					+ "',"
					+ "?"
					+ ","
					+ "?" + ");";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, lectureID);
			prest.setString(2, name);
			prest.setInt(3, active_bol);
			prest.setString(4, start);
			prest.setString(5, end);
			prest.setString(6, shortdesc);
			prest.setString(7, desc);
			prest.setString(8, pk_image);
			prest.setInt(9, imageversion);
			prest.setString(10, pk_person);
			prest.setString(11, pk_person);

			prest.executeUpdate();
			con.commit();

			con.close();
		} catch (SQLException e) {

			log.info("Failed to setLectureData.");
			e.printStackTrace();
		}
		return 0;

	}

	public Map<String, String> getImageIDandVersion(String id) {
		Map<String, String> map = new HashMap<String, String>();
		try {
			Connection con = getConnection();

			String sql = "SELECT GUID_imageID, imageVersion FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID = ?";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, id);

			ResultSet image = prest.executeQuery();
			con.commit();
			while (image.next()) {
				map.put("GUID", image.getString("GUID_imageID"));
				map.put("version", String.valueOf(image.getString("imageVersion")));

			}
			con.close();
			return map;
		} catch (SQLException e) {

			log.info("Failed to getImageIDandVersion.");
			e.printStackTrace();
		}
		return null;
	}

	public Map<String, String> getImageData(String id, String version) {
		Map<String, String> map = new HashMap<String, String>();
		try {
			Connection con = getConnection();

			String sql = "SELECT u.mail, i.GUID_imageID, i.imageVersion, i.image_name,i.image_description,i.image_update_time,i.cond_hasInternetRestriction,i.cond_hasLicenseRestriction, i.cond_minCPUs,i.cond_minRAM, i.image_syncMode,i.image_filesize,i.image_path, i.image_isTemplate,os.GuestOS, concat(u.Nachname,' ',u.Vorname) as Owner FROM bwLehrpool.m_VLData_imageInfo i,bwLehrpool.m_user u,bwLehrpool.m_operatingSystem os where i.image_owner=u.userID and i.content_operatingSystem=os.operatingSystemID and GUID_imageID = ? and imageVersion = ? ;";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, id);
			prest.setString(2, version);

			ResultSet data = prest.executeQuery();
			con.commit();
			while (data.next()) {
				// TODO: This is just a horrible idea. Make a typo anywhere for the key, it still compiles but doesn't work.
				// Somebody was too lazy to create a proper struct/class here....
				map.put("name", data.getString("image_name"));
				map.put("internet", data.getString("cond_hasInternetRestriction"));
				map.put("license", data.getString("cond_hasLicenseRestriction"));
				map.put("cpu", data.getString("cond_minCPUs"));
				map.put("ram", data.getString("cond_minRAM"));
				map.put("shareMode", data.getString("image_syncMode"));
				map.put("filesize", data.getString("image_filesize"));
				map.put("path", data.getString("image_path"));
				map.put("template", data.getString("image_isTemplate"));
				map.put("os", data.getString("GuestOS"));
				map.put("owner", data.getString("Owner"));
				map.put("lastupdate", data.getString("image_update_time"));
				map.put("desc", data.getString("image_description"));
				map.put("id", data.getString("GUID_imageID"));
				map.put("version", data.getString("imageVersion"));
				map.put("mail", data.getString("mail"));

			}
			con.close();
			return map;
		} catch (SQLException e) {

			log.info("Failed to getImageData.");
			e.printStackTrace();
		}
		return null;
	}

	public boolean updateImageData(String newName, String desc, boolean license, boolean internet,
			String imageId, String shareMode, String ospk) {
		try {
			Connection con = getConnection();
			DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

			int internet_bol = 0;
			int license_bol = 0;

			if (internet == true) {
				internet_bol = 1;
			}

			if (license == true) {
				license_bol = 1;
			}

			String sql = "UPDATE bwLehrpool.m_VLData_imageInfo SET " + "imageVersion = " + "?" + ", "
					+ "image_name = " + "?" + ",image_description = " + "?"
					+ " ,image_update_time =  '"
					+ formatter.format(new Date())
					+ "' ,rec_change_time =  '"
					+ formatter.format(new Date())
					+ "' ,cond_hasLicenseRestriction =  "
					+ "?"
					+ " ,cond_hasInternetRestriction =  "
					+ "?"
					+ " ,cond_minRAM =  "
					+ "?"
					+ " ,cond_minCPUs =  "
					+ "?"
					+ " ,image_isTemplate =  "
					+ "0 "
					+ ",content_operatingSystem = "
					+ "?"
					+ ",image_syncMode =  "
					+ "?"
					+ "  WHERE GUID_imageID =  " + "?" + "  AND imageVersion = " + "?" + ";";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setInt(1, 1);
			prest.setString(2, newName);
			prest.setString(3, desc);
			prest.setInt(4, license_bol);
			prest.setInt(5, internet_bol);
			prest.setLong(6, 2);
			prest.setLong(7, 2);
			prest.setString(8, ospk);
			prest.setString(9, shareMode);
			prest.setString(10, imageId);
			prest.setString(11, "1");

			prest.executeUpdate();

			con.commit(); // needed, though executeUpdate()!

			// con.commit();
			con.close();
			return true;
		} catch (SQLException e) {

			log.info("Failed to UpdateImageData.");
			e.printStackTrace();
		}
		return false;
	}

	public boolean deleteImage(String id, String version) {

		try {
			Connection con = getConnection();

			// delete the image record
			String sql = "DELETE FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = ? AND imageVersion = ?;";
			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, id);
			prest.setString(2, version);
			prest.executeUpdate();

			// delete all permission records for this image
			sql = "DELETE FROM bwLehrpool.pm_VLData_image WHERE GUID_imageID = ?";
			prest = con.prepareStatement(sql);
			prest.setString(1, id);
			prest.executeUpdate();

			con.commit();
			con.close();
			return true;

		} catch (SQLException e) {

			log.info("Failed to deleteImage and permissions.");
			e.printStackTrace();
		}

		return false;
	}

	public int updateLectureData(String pk_image, String imageversion, String name, String newName,
			String desc, String shortdesc, String start, String end, boolean isactive, String id) {

		DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		int active_bol = 0;

		if (isactive == true) {
			active_bol = 1;
		}
		try {
			Connection con = getConnection();

			String sql = "UPDATE `bwLehrpool`.`m_VLData_lecture` SET `name` = " + "?" + ",isActive = " + "?"
					+ ",startTime = " + "?" + ",endTime = " + "?" + ",description = " + "?" + ",imageID = "
					+ "?" + ",imageVersion = " + "?" + ",admin_changeTime = '" + formatter.format(new Date())
					+ "'WHERE `lectureID` = ?;";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, newName);
			prest.setInt(2, active_bol);
			prest.setString(3, start);
			prest.setString(4, end);
			prest.setString(5, desc);
			prest.setString(6, pk_image);
			prest.setString(7, imageversion);
			prest.setString(8, id);

			prest.executeUpdate();
			con.commit();
			con.close();
			// log.info("Succeeded to updateLectureData.");

		} catch (SQLException e) {

			log.info("Failed to updateLectureData.");
			e.printStackTrace();
		}

		return 0;

	}

	public boolean connectedToLecture(String id, String version) {

		try {
			Connection con = getConnection();

			String sql = "SELECT lectureID FROM "
					+ "bwLehrpool.m_VLData_lecture WHERE imageID = ? AND imageVersion = ?;";
			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, id);
			prest.setString(2, version);

			ResultSet rs = prest.executeQuery();
			con.commit();
			boolean ret = rs.first();
			con.close();
			return ret;

		} catch (SQLException e) {

			log.info("Failed to execute method connectedToLecture.");
			e.printStackTrace();
		}

		return false;

	}

	public boolean deleteLecture(String id) {

		try {
			Connection con = getConnection();

			// delete the lecture record
			String sql = "DELETE FROM bwLehrpool.m_VLData_lecture WHERE lectureID = ?;";
			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, id);

			prest.executeUpdate();

			// delete all permission records for this lecture
			sql = "DELETE FROM bwLehrpool.pm_VLData_lecture WHERE lectureID = ?;";
			prest = con.prepareStatement(sql);
			prest.setString(1, id);

			prest.executeUpdate();

			con.commit();
			con.close();
			return true;
		} catch (SQLException e) {

			log.info("Failed to deleteLecture and permissions.");
			e.printStackTrace();
		}

		return false;
	}

	public String getFile(String imageid, String imageversion) {
		String path = null;
		Connection con = null;

		try {
			con = getConnection();

			String sql = "SELECT image_path FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = " + "?"
					+ " AND imageVersion = " + "?" + ";";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, imageid);
			prest.setString(2, imageversion);

			ResultSet rs = prest.executeQuery();
			con.commit();

			if (rs.next())
				path = rs.getString("image_path");

			return path;

		} catch (SQLException e) {
			log.info("Failed to getFile.", e);
		} finally {
			Util.safeClose(con);
		}

		return null;
	}

	public Map<String, String> getDeleteXMLData(String id) {
		Map<String, String> map = new HashMap<String, String>();
		try {
			Connection con = getConnection();

			String sql = "SELECT name, admin_createTime FROM bwLehrpool.m_VLData_lecture where lectureID = ?;";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, id);

			ResultSet rs = prest.executeQuery();
			con.commit();
			rs.next();

			map.put("date",
					rs.getString("admin_createTime").replace(" ", "").replace("-", "").replace(":", ""));
			map.put("name", rs.getString("name"));

			con.close();
			return map;

		} catch (SQLException e) {

			log.info("Failed to getDeleteXMLData.");
			e.printStackTrace();
		}

		return null;
	}

	public int UpdateImagePath(String name) {
		try {
			Connection con = getConnection();

			String sql = "UPDATE bwLehrpool.m_VLData_imageInfo SET image_path = " + "?" // prod/+name
					+ " WHERE image_path = " + "?" // temp/+name
					+ ";";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, "prod/" + name);
			prest.setString(2, "temp/" + name);

			prest.executeUpdate();

			con.commit();
			con.close();
			// log.info("Succesfully updated image path");
			return 0;
		} catch (SQLException e) {

			log.info("Failed to UpdateImagePath.");
			e.printStackTrace();
		}
		return -1;
	}

	public List<String> getAllUniversities() {
		List<String> list = new ArrayList<>();
		try {
			Connection con = getConnection();
			Statement stm = con.createStatement();
			ResultSet rs = stm.executeQuery("SELECT name FROM bwLehrpool.m_institution;");
			while (rs.next()) {
				list.add(rs.getString("name"));
			}
			return list;

		} catch (SQLException e) {

			log.info("Failed to getAllUniversities.");
			e.printStackTrace();
		}

		return null;

	}

	public String getOSpk(String os, String architecture) {
		try {
			Connection con = getConnection();
			// log.info("Getting PrimaryKey for OS: " + os + " architecture: " +
			// architecture);

			String sql = "SELECT operatingSystemID FROM bwLehrpool.m_operatingSystem where name like ? and architecture like ?;";
			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, os);
			prest.setString(2, architecture);

			ResultSet rs = prest.executeQuery();
			con.commit();
			rs.first();
			String pkos = rs.getString("operatingSystemID");
			// log.info(" - Key is " + pkos);
			con.close();
			return pkos;

		} catch (SQLException e) {

			log.info("Failed to getOSpk.");
			e.printStackTrace();
		}

		return null;
	}

	public String getOS(String pk) {
		try {
			Connection con = getConnection();
			// log.info(new Date() +
			// " - OS: "+os+" architecture: "+architecture);
			String sql = "SELECT name,architecture FROM bwLehrpool.m_operatingSystem where operatingSystemID = ?;";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, pk);

			ResultSet rs = prest.executeQuery();
			con.commit();
			rs.first();
			String os = rs.getString("name") + " " + rs.getString("architecture");
			con.close();
			return os;

		} catch (SQLException e) {

			log.info("Failed to getOS.");
			e.printStackTrace();
		}

		return null;
	}

	public Map<String, String> getLectureData(String id) {
		Map<String, String> map = new HashMap<String, String>();
		try {
			Connection con = getConnection();

			String sql = "select l.name, l.isActive, l.startTime, l.endTime, l.lastUsed, l.description, i.image_path, l.imageVersion, concat(u.Nachname,' ',u.Vorname) as owner from m_VLData_lecture l, m_user u, m_VLData_imageInfo i  where l.admin_owner=u.userID and l.imageID=i.GUID_imageID and lectureID= ?";
			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, id);

			ResultSet data = prest.executeQuery();
			con.commit();
			while (data.next()) {
				map.put("name", data.getString("name"));
				map.put("isActive", data.getString("isActive"));
				map.put("startTime", data.getString("startTime"));
				map.put("endTime", data.getString("endTime"));
				map.put("lastUsed", data.getString("lastUsed"));
				map.put("desc", data.getString("description"));
				map.put("imagepath", data.getString("image_path"));
				map.put("imageversion", data.getString("imageVersion"));
				map.put("owner", data.getString("Owner"));

			}
			con.close();
			return map;
		} catch (SQLException e) {

			log.info("Failed to getLectureData.");
			e.printStackTrace();
		}
		return null;
	}

	public boolean checkUser(String username) {

		boolean ret = false;
		try {
			Connection con = getConnection();

			String sql = "Select * from m_user where loginName like ?";
			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, username);

			ResultSet rs = prest.executeQuery();
			con.commit();
			if (rs.next()) {
				ret = true;
				log.info("Checking user: " + rs.getString("loginName") + " exists.");
			} else {
				ret = false;
				log.info("Checking user: " + rs.getString("loginName") + " does not exist.");
			}
			con.close();

		} catch (SQLException e) {

			e.printStackTrace();
		}
		return ret;
	}

	public int getRoleID(String role) {

		try {
			Connection con = getConnection();

			String sql = "SELECT roleID FROM bwLehrpool.m_role where name like ?;";
			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, role);

			ResultSet rs = prest.executeQuery();
			con.commit();
			rs.first();

			return rs.getInt("roleID");

		} catch (SQLException e) {

			log.info("Failed to getRoleID.");
			e.printStackTrace();
		}
		return -1;
	}

	public String getLectureID(String name) {
		String id = null;
		try {
			Connection con = getConnection();

			String sql = "SELECT lectureID FROM bwLehrpool.m_VLData_lecture where name like ?;";
			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, name);

			ResultSet lecture = prest.executeQuery();
			con.commit();
			while (lecture.next()) {
				id = lecture.getString("lectureID");

			}
			con.close();
			return id;
		} catch (SQLException e) {

			log.info("Failed to getLectureID.");
			e.printStackTrace();
		}
		return id;
	}

	// get all users from satellite -- temp, tb replaced by allUsers bwLehrpool
	// does -NOT- return the user himself
	// TODO prepared statement
	public List<Person> getAllOtherSatelliteUsers(List<String> userID) {
		Connection con = getConnection();
		Statement stm = null;
		ResultSet res = null;

		List<Person> list = new ArrayList<Person>();

		try {
			stm = con.createStatement();
		} catch (SQLException e) {

			e.printStackTrace();
		}
		String query = "SELECT userID, Nachname, Vorname, mail FROM m_user WHERE userID NOT IN ('"
				+ userID.get(0) + "";

		for (int i = 1; i < userID.size(); i++) {
			query = query + "','" + userID.get(i);
		}
		query = query + "') ORDER BY Nachname ASC;";

		try {
			res = stm.executeQuery(query);
		} catch (SQLException e1) {

			e1.printStackTrace();
		}

		try {
			while (res.next()) {
				// fill the list with users - permissions are all false because
				// the image is new
				list.add(new Person(res.getString("userID"), res.getString("Nachname"),
						res.getString("Vorname"), res.getString("mail"), false, false, false, false, false,
						false, false));
			}
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return list;
	}// end getAllOtherSatelliteUsers()

	// get the permissions of an image for all other registered users -EXCEPT-
	// the logged on user
	public List<Person> getPermissionForUserAndImage(String userID, String imageID) {

		Connection con = getConnection();
		ResultSet res = null;
		List<Person> list = new ArrayList<Person>();

		try {

			String sql = "SELECT DISTINCT pmi.GUID_imageID, pmi.userID, u.userID, u.Nachname, u.Vorname, u.mail, pmi.image_read, pmi.image_write, pmi.image_admin, pmi.link_allowed FROM bwLehrpool.pm_VLData_image pmi, bwLehrpool.m_user u WHERE pmi.userID!= "
					+ "?"
					+ " AND pmi.GUID_imageID= "
					+ "?"
					+ " AND pmi.userID=u.userID ORDER BY u.Nachname ASC;";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, userID);
			prest.setString(2, imageID);

			res = prest.executeQuery();
			con.commit();

		} catch (SQLException e) {

			e.printStackTrace();
		}
		try {
			boolean image_read, image_write, link_allowed, image_admin;

			while (res.next()) {
				// reset old values and set the correct new values
				image_read = false;
				image_write = false;
				link_allowed = false;
				image_admin = false;

				// TODO: Are those really non-numeric columns? If yes, keep in mind parseInt can throw an exception...
				if (Integer.parseInt(res.getString("image_read")) != 0) {
					image_read = true;
				}
				if (Integer.parseInt(res.getString("image_write")) != 0) {
					image_write = true;
				}
				if (Integer.parseInt(res.getString("link_allowed")) != 0) {
					link_allowed = true;
				}
				if (Integer.parseInt(res.getString("image_admin")) != 0) {
					image_admin = true;
				}

				// fill the list with users - permissions are all false because
				// the image is new
				list.add(new Person(res.getString("userID"), res.getString("Nachname"),
						res.getString("Vorname"), res.getString("mail"), image_read, image_write,
						link_allowed, image_admin, false, // lecture_read
						false, // lecture_write
						false) // lecture_admin
				);
				// System.out.println(res.getString("Nachname")+" "+Boolean.parseBoolean(res.getString("image_read"))+" "+Boolean.parseBoolean(res.getString("image_write"))+" "+Boolean.parseBoolean(res.getString("image_admin")));
			}
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return list;
	}// end getPermissionFOrUserAndImage()

	// get the permissions of a lecture for all other registered users -EXCEPT-
	// the logged on user
	public List<Person> getPermissionForUserAndLecture(String userID, String lectureID) {

		Connection con = getConnection();
		// Statement stm = null;
		ResultSet res = null;
		List<Person> list = new ArrayList<Person>();

		try {

			String sql = "SELECT DISTINCT pml.lectureID, pml.userID, u.userID, u.Nachname,	u.Vorname, u.mail, pml.rec_read, pml.rec_write,	pml.rec_admin "
					+ "FROM bwLehrpool.pm_VLData_lecture pml, bwLehrpool.m_user u WHERE pml.userID!= ? AND pml.lectureID= ? AND pml.userID=u.userID ORDER BY u.Nachname ASC;";

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, userID);
			prest.setString(2, lectureID);

			res = prest.executeQuery();
			con.commit();

		} catch (SQLException e) {

			e.printStackTrace();
		}
		try {
			boolean lecture_read, lecture_write, lecture_admin;

			while (res.next()) {
				// reset old values and set the correct new values
				lecture_read = false;
				lecture_write = false;
				lecture_admin = false;

				if (Integer.parseInt(res.getString("rec_read")) != 0) {
					lecture_read = true;
				}
				if (Integer.parseInt(res.getString("rec_write")) != 0) {
					lecture_write = true;
				}
				if (Integer.parseInt(res.getString("rec_admin")) != 0) {
					lecture_admin = true;
				}

				// fill the list with users - permissions are all false because
				// the image is new
				list.add(new Person(res.getString("userID"), res.getString("Nachname"),
						res.getString("Vorname"), res.getString("mail"), false, // image read
						false, // image write
						false, // image link
						false, // image admin
						lecture_read, // lecture_read
						lecture_write, // lecture_write
						lecture_admin) // lecture_admin
				);
				// System.out.println(res.getString("Nachname")+" "+Boolean.parseBoolean(res.getString("image_read"))+" "+Boolean.parseBoolean(res.getString("image_write"))+" "+Boolean.parseBoolean(res.getString("image_admin")));
			}
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return list;
	}// end getPermissionForUserAndLecture

	public int setImageRights(String pk_person, String pk_image, int role, int read, int write,
			int linkallowed, int admin) {

		try {
			Connection con = getConnection();
			// Statement stm = con.createStatement();

			String uid = UUID.randomUUID().toString();

			String sql = "INSERT INTO bwLehrpool.pm_VLData_image(GUID,GUID_imageID,roleID,userID,image_read,image_write,image_admin,link_allowed)VALUES"
					+ "(?,?,?,?,?,?,?,?)";

			PreparedStatement prest = con.prepareStatement(sql);

			prest.setString(1, uid);
			prest.setString(2, pk_image);
			prest.setInt(3, role);
			prest.setString(4, pk_person);
			prest.setInt(5, read);
			prest.setInt(6, write);
			prest.setInt(7, admin);
			prest.setInt(8, linkallowed);

			prest.executeUpdate();

			con.commit();
			con.close();

		} catch (SQLException e) {

			log.info("Failed to setImageRights.");
			e.printStackTrace();
		}
		return 0;
	}// end setImageRights

	public boolean writeAdditionalImageRights(String imageID, String userID, boolean isRead, boolean isWrite,
			boolean isLinkAllowed, boolean isAdmin) {
		boolean success = true;

		String uid = UUID.randomUUID().toString();

		Connection con = getConnection();

		try {

			String sql = "INSERT INTO pm_VLData_image (" + "GUID, " + "GUID_imageID, " + "roleID, "
					+ "userID, " + "image_read, " + "image_write, " + "link_allowed," + "Image_admin)"
					+ "VALUES" + "(?,?,?,?,?,?,?,?)";

			PreparedStatement prest = con.prepareStatement(sql);

			prest.setString(1, uid);
			prest.setString(2, imageID);
			prest.setInt(3, 1);
			prest.setString(4, userID);
			prest.setBoolean(5, isRead);
			prest.setBoolean(6, isWrite);
			prest.setBoolean(7, isLinkAllowed);
			prest.setBoolean(8, isAdmin);

			prest.executeUpdate();

			con.commit();
			con.close();

			// log.info("Written additional image rights.");

		} catch (SQLException e) {

			e.printStackTrace();
			log.info("Failed to write additional image rights.");
		}
		return success;
	}// end writeAdditionalImageRights

	public int setLectureRights(String pk_person, String pk_lecture, int role, int read, int write, int admin) {

		try {
			Connection con = getConnection();
			// Statement stm = con.createStatement();

			String uid = UUID.randomUUID().toString();
			String sql = "INSERT INTO `bwLehrpool`.`pm_VLData_lecture`(`GUID`,`lectureID`,`userID`,`rec_read`,`rec_write`,`rec_admin`)VALUES"
					+ "(?,?,?,?,?,?)";

			PreparedStatement prest = con.prepareStatement(sql);

			prest.setString(1, uid);
			prest.setString(2, pk_lecture);
			prest.setString(3, pk_person);
			prest.setInt(4, read);
			prest.setInt(5, write);
			prest.setInt(6, admin);

			prest.executeUpdate();

			con.commit();
			log.info("Written lecture rights for lecture '" + pk_lecture + "'.");
			con.close();
		} catch (SQLException e) {

			log.info("Failed to setLectureRights.");
			e.printStackTrace();
		}
		return 0;
	} // end setLectureRights

	public boolean writeAdditionalLectureRights(String lectureID, String userID, boolean isRead,
			boolean isWrite, boolean isAdmin) {
		boolean success = true;

		String uid = UUID.randomUUID().toString();
		Connection con = getConnection();

		try {

			String sql = "INSERT INTO pm_VLData_lecture (GUID, lectureID, userID, rec_read, rec_write, rec_admin) VALUES"
					+ ("(?,?,?,?,?,?)");

			PreparedStatement prest = con.prepareStatement(sql);

			prest.setString(1, uid);
			prest.setString(2, lectureID);
			prest.setString(3, userID);
			prest.setBoolean(4, isRead);
			prest.setBoolean(5, isWrite);
			prest.setBoolean(6, isAdmin);

			// log.info("userdata: "+uid+" "+lectureID + " " + userID + " " +
			// isRead + isWrite + isAdmin);

			prest.executeUpdate();

			con.commit();
			con.close();
			// log.info("Written additional lecture rights for '"+ userID +
			// "'.");
		} catch (SQLException e) {

			e.printStackTrace();
			log.info("Failed to write additional lecture rights.");
		}
		return success;
	}// end writeAdditionalLectureRights

	public void deleteAllAdditionalImagePermissions(String imageID, String userID) {
		Connection con = getConnection();

		try {

			String sql = "DELETE FROM pm_VLData_image WHERE GUID_imageID = ? AND userID != ?;";
			PreparedStatement prest = con.prepareStatement(sql);

			prest.setString(1, imageID);
			prest.setString(2, userID);

			prest.executeUpdate();

		} catch (SQLException e) {

			e.printStackTrace();
		}

		try {

			con.commit();

		} catch (SQLException e) {

			e.printStackTrace();
		}
	}// end deleteAllAdditionalImagePermissions

	public void deleteAllAdditionalLecturePermissions(String lectureID, String userID) {
		Connection con = getConnection();

		try {

			String sql = "DELETE FROM pm_VLData_lecture WHERE lectureID = ? AND userID != ?;";
			PreparedStatement prest = con.prepareStatement(sql);

			prest.setString(1, lectureID);
			prest.setString(2, userID);

			prest.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		try {

			con.commit();

		} catch (SQLException e) {

			e.printStackTrace();
		}
	}// end deleteAllAdditionalImagePermissions

	public List<String> getAdditionalImageContacts(String imageID) {

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

		Connection con = getConnection();
		try {

			String sql = "select distinct u.Nachname, u.Vorname, u.mail from bwLehrpool.pm_VLData_image img, bwLehrpool.m_user u where GUID_imageID = ? and image_write = '1' and img.userID = u.userID;";
			PreparedStatement prest = con.prepareStatement(sql);

			prest.setString(1, imageID);

			ResultSet rs = prest.executeQuery();
			con.commit();

			while (rs.next()) {
				list.add(rs.getString("Nachname") + "," + rs.getString("Vorname") + " ");
			}

		} catch (SQLException e) {

			e.printStackTrace();
		}

		return list;

	}

	public String getOsNameForGuestOs(String guestOS) {

		Connection con = getConnection();
		ResultSet rs = null;
		String sql = "Select name, architecture from bwLehrpool.m_operatingSystem where guestOS = ?";

		try {

			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, guestOS);
			rs = prest.executeQuery();
			con.commit();
			rs.next();

			return rs.getString("name") + " " + rs.getString("architecture") + " bit";

		} catch (SQLException e) {

			e.printStackTrace();
		}

		return null;

	}

	public String createRandomUUID() {
		return UUID.randomUUID().toString();
	}

	public boolean userIsImageAdmin(String userID, String imageID) {
		Connection con = getConnection();
		ResultSet rs = null;
		String sql = "SELECT image_admin FROM bwLehrpool.pm_VLData_image WHERE userID= ? AND GUID_imageID=?";

		log.info("stmt user is image admin: "
				+ "SELECT image_admin FROM bwLehrpool.pm_VLData_image WHERE userID= '" + userID
				+ "' AND GUID_imageID='" + imageID + "'");

		try {
			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, userID);
			prest.setString(2, imageID);
			rs = prest.executeQuery();
			con.commit();
			rs.next();

			return rs.getBoolean("image_admin");

		} catch (SQLException e) {

			e.printStackTrace();
		}
		return false;
	}

	public boolean userIsLectureAdmin(String userID, String lectureID) {
		Connection con = getConnection();
		ResultSet rs = null;
		String sql = "SELECT rec_admin FROM bwLehrpool.pm_VLData_lecture WHERE userID= ? AND lectureID=?";

		try {
			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, userID);
			prest.setString(2, lectureID);
			rs = prest.executeQuery();
			con.commit();
			rs.next();

			return rs.getBoolean("rec_admin");

		} catch (SQLException e) {

			e.printStackTrace();
		}
		return false;
	}

	public String getInstitutionByID(String institutionID, String errorParam) {

		Connection con = getConnection();
		ResultSet rs = null;
		String sql = "SELECT name FROM bwLehrpool.m_institution WHERE institutionID = ?";

		log.info("intitution ID is: " + institutionID);

		PreparedStatement prest;
		try {

			prest = con.prepareStatement(sql);
			prest.setString(1, institutionID);

			rs = prest.executeQuery();
			// con.commit();
			rs.next();
			return rs.getString("name");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;

	}

}// end class