package sql;

import java.sql.*;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.UUID;

import models.Configuration;

import org.apache.log4j.Logger;
import org.apache.thrift.TException;

import server.BinaryListener;
import server.generated.Image;
import server.generated.Lecture;
import server.generated.Person;

public class SQL {

	private static Logger log = Logger.getLogger(BinaryListener.class);

	public Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException
				| ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			Connection con = DriverManager.getConnection("jdbc:mysql://"
					+ Configuration.config.getSql_connection() + "?user="
					+ Configuration.config.getSql_user() + "&password="
					+ Configuration.config.getSql_pass() + "");
			con.setAutoCommit(false);

			return con;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to return connection to Client.");
			e.printStackTrace();
		}
		return null;
	}

	public int writeFTPUser(String user, String pass) {
		Statement stm;
		try {
			Connection con = getConnection();
			stm = con.createStatement();

			int ret = stm
					.executeUpdate("INSERT INTO `bwLehrpool`.`FtpUsers`(`User`,`Password`,`Uid`,`Gid`,`Dir`)VALUES('"
							+ user
							+ "',SHA1('"
							+ pass
							+ "'),'10001','12345','"
							+ Configuration.config.getAbsolute_path()
							+ "temp/');");
			con.commit();
			con.close();
			log.info(new Date() + " - created FTPUser " + user + " : " + pass
					+ ".");
			return ret;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to writeFTPUser.");
			e.printStackTrace();
		}
		return -1;
	}

	public int DeleteUser(String user) {
		Statement stm;
		try {
			Connection con = getConnection();
			stm = con.createStatement();

			int ret = stm
					.executeUpdate("DELETE FROM `bwLehrpool`.`FtpUsers` where User like '"
							+ user + "';");
			con.commit();
			con.close();
			log.info(new Date() + " - FTPUser " + user + " deleted.");
			return ret;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to DeleteUser.");
			e.printStackTrace();
		}
		return -1;
	}

	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) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getImage.");
			e.printStackTrace();
		}

		return null;
	}

	public String getPathOfImage(String image_id, String version) {
		try {
			Connection con = getConnection();
			Statement stm = con.createStatement();
			ResultSet rs = stm
					.executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID='"
							+ image_id
							+ "' and imageVersion='"
							+ version
							+ "';");
			rs.first();
			String path = rs.getString("image_path");
			con.close();
			return path;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getPathOfImage.");
			e.printStackTrace();
		}

		return null;
	}

	public String setInstitution(String university) {
		try {
			Connection con = getConnection();
			Statement stm = con.createStatement();

			ResultSet ret = stm
					.executeQuery("SELECT * FROM bwLehrpool.m_institution where name like'"
							+ university + "';");

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

				String id = UUID.randomUUID().toString();
				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();
				String institution = rs.getString("institutionID");
				con.close();

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

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to setInstitution.");
			e.printStackTrace();
		}
		return "-1";
	}

	public String setPerson(String login, String lastname, String firstname,
			String mail, Date lastlogin, String Institution) {
		DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		try {
			Connection con = getConnection();
			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) {

				// String id = UUID.randomUUID().toString();
				stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES('"
						+ login
						+ "','"
						+ login
						+ "','"
						+ 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();
				String userid = rs.getString("userID");
				con.close();
				return userid;
			} else {
				ret.first();
				String userid = ret.getString("userID");
				con.close();
				return userid;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to setPerson.");
			e.printStackTrace();
		}
		return "-1";
	}

	public boolean setImageData(String pk_person, boolean license,
			boolean internet, long cpu, long ram, String imagename,
			String desc, String imagePath, long filesize, String shareMode,
			String pk_os) {

		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();
			Statement stm = con.createStatement();

			// create a UUID
			String uid = UUID.randomUUID().toString();

			log.info(" - Query is: "
					+ "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('"
					+ uid // GUID_imageID
					+ "',1,'" // imageVersion
					+ imagename // image_name
					// Ticket #1144 ist hier zu lösen, desc sollte nicht zu lang
					// sein, sonst merkert die DB
					+ "','" + desc // image_description
					+ "','" + imagePath // image_path
					+ "','" + formatter.format(new Date()) // image_lastCall
					+ "','" + formatter.format(new Date()) // image_create_time
					+ "','" + formatter.format(new Date()) // image_update_time
					+ "','" + pk_person // image_owner
					+ "','" + pk_person // image_change_by
					+ "','" + formatter.format(new Date()) // rec_create_time
					+ "','" + formatter.format(new Date()) // rec_change_time
					+ "','" + pk_person // rec_owner
					+ "','" + pk_person // rec_change_by
					+ "','" + pk_os // content_operatingSystem
					+ "',1" // status_isCompressed
					+ ",1" // status_isSecure
					+ ",1" // status_isOptimzed
					+ ",1" // status_isValid
					+ ",1" // status_isReady
					+ ",0" // status_isDeleted
					+ ",0,'" // status_isLastOfficialVersion
					+ license_bol // cond_hasLicenseRestriction
					+ "','" + internet_bol // cond_hasInternetRestriction
					+ "','" + ram // cond_minRAM
					+ "','" + cpu // cond_minCPUs
					+ "','0'" // image_isTemplate
					+ ",'" + filesize // image_filesize
					+ "','" + shareMode // shareMode
					+ "');");

			stm.executeUpdate("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('"
					+ uid // GUID_imageID
					+ "',1,'" // imageVersion
					+ imagename // image_name
					// Ticket #1144 ist hier zu lösen, desc sollte nicht zu lang
					// sein, sonst merkert die DB
					+ "','" + desc // image_description
					+ "','" + imagePath // image_path
					+ "','" + formatter.format(new Date()) // image_lastCall
					+ "','" + formatter.format(new Date()) // image_create_time
					+ "','" + formatter.format(new Date()) // image_update_time
					+ "','" + pk_person // image_owner
					+ "','" + pk_person // image_change_by
					+ "','" + formatter.format(new Date()) // rec_create_time
					+ "','" + formatter.format(new Date()) // rec_change_time
					+ "','" + pk_person // rec_owner
					+ "','" + pk_person // rec_change_by
					+ "','" + pk_os // content_operatingSystem
					+ "',1" // status_isCompressed
					+ ",1" // status_isSecure
					+ ",1" // status_isOptimzed
					+ ",1" // status_isValid
					+ ",1" // status_isReady
					+ ",0" // status_isDeleted
					+ ",0,'" // status_isLastOfficialVersion
					+ license_bol // cond_hasLicenseRestriction
					+ "','" + internet_bol // cond_hasInternetRestriction
					+ "','" + ram // cond_minRAM
					+ "','" + cpu // cond_minCPUs
					+ "','0'" // image_isTemplate
					+ ",'" + filesize // image_filesize
					+ "','" + shareMode // shareMode
					+ "');");
			con.commit();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to setImageData.");
			e.printStackTrace();
		}
		return true;

	}

	public List<Image> getImageListPermissionWrite(String userID) {

		Connection con = getConnection();
		Statement stm;

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

			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='"
							+ userID
							+ "' AND u.userID = pmi.userID AND pmi.image_write = 1 AND pmi.GUID_imageID = vl.GUID_imageID AND vl.content_operatingSystem=os.operatingSystemID;");

			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")));
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	public List<Image> getImageListPermissionRead(String userID) {

		Connection con = getConnection();
		Statement stm;

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

			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='"
							+ userID
							+ "' AND u.userID = pmi.userID AND pmi.image_read = 1 AND pmi.GUID_imageID = vl.GUID_imageID AND vl.content_operatingSystem=os.operatingSystemID;");

			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")));
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	public List<Image> getImageListPermissionLink(String userID) {

		Connection con = getConnection();
		Statement stm;

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

			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 FROM  bwLehrpool.pm_VLData_image pmi,  bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os,  bwLehrpool.m_user u WHERE pmi.userID='"
							+ userID
							+ "' AND u.userID = pmi.userID AND pmi.link_allowed = 1 AND pmi.GUID_imageID = vl.GUID_imageID AND vl.content_operatingSystem=os.operatingSystemID;");

			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")));
			}
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	
	public List<Image> getImageListPermissionAdmin(String userID){
		//fill me
		return null;
	}
	
	

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

		try {
			Connection con = getConnection();
			Statement stm = con.createStatement();
			ResultSet res = stm
					.executeQuery("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	i.GUID_imageID=l.imageID AND pml.rec_read=true AND u.userID=pml.userID AND u.userID='"
							+ userID + "';");
			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) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - 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();
			Statement stm = con.createStatement();
			ResultSet res = stm
					.executeQuery("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='"
							+ userID
							+ "' AND u.userID=pml.userID AND pml.rec_write=1 AND i.GUID_imageID=l.imageID AND l.lectureID=pml.lectureID;");
			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) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - 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();
			Statement stm = con.createStatement();
			ResultSet res = stm
					.executeQuery("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 i.GUID_imageID=l.imageID AND pml.rec_admin=true AND u.userID=pml.userID AND u.userID='"
							+ userID + "';");
			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) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getLectureListPermissionAdmin.");
			e.printStackTrace();
		}
		return list;
	}// end getLectureListPermissionAdmin

	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;");
			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) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getLectureList.");
			e.printStackTrace();
		}
		return null;
	}

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

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getAllOS.");
			e.printStackTrace();
		}

		return null;

	}

	public Map<String, String> getPersonData(String Vorname, String Nachname) {
		Map<String, String> map = new HashMap<String, String>();
		try {
			Connection con = getConnection();
			Statement stm = con.createStatement();
			ResultSet rs = stm
					.executeQuery("SELECT u.Nachname, u.Vorname, u.mail, i.name FROM bwLehrpool.m_user u, bwLehrpool.m_institution i where u.Nachname like '"
							+ Nachname
							+ "' and u.Vorname like '"
							+ Vorname
							+ "' and u.institution=i.institutionID;");
			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) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getPersonData.");
			e.printStackTrace();
		}
		return null;
	}

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

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

		if (isactive == true) {
			active_bol = 1;
		}
		try {
			Connection con = getConnection();
			Statement stm = con.createStatement();

			String uid = UUID.randomUUID().toString();
			stm.executeUpdate("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('"
					+ uid
					+ "','"
					+ name
					+ "','"
					+ active_bol
					+ "','"
					+ start
					+ "','"
					+ end
					+ "','"
					+ formatter.format(new Date())
					+ "','"
					+ shortdesc
					+ "','"
					+ desc
					+ "','"
					+ pk_image
					+ "','"
					+ imageversion
					+ "','"
					+ formatter.format(new Date())
					+ "','"
					+ formatter.format(new Date())
					+ "','"
					+ pk_person
					+ "','"
					+ pk_person + "');");
			con.commit();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to setLectureData.");
			e.printStackTrace();
		}
		return 0;

	}

	public Map<String, String> getImageIDandVersion(String name) {
		Map<String, String> map = new HashMap<String, String>();
		try {
			Connection con = getConnection();
			Statement stm = con.createStatement();
			ResultSet image = stm
					.executeQuery("SELECT GUID_imageID, imageVersion FROM bwLehrpool.m_VLData_imageInfo where image_name like '"
							+ name + "';");
			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) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - 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();
			Statement stm = con.createStatement();
			ResultSet data = stm
					.executeQuery("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 = '"
							+ id + "' and imageVersion = '" + version + "' ;");
			while (data.next()) {
				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) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getImageData.");
			e.printStackTrace();
		}
		return null;
	}

	public int UpdateImageData(String name, String newName, String desc,
			String image_path, boolean license, boolean internet, long cpu,
			long ram, String id, String version, boolean isTemplate,
			long filesize, String shareMode, String ospk) {
		try {
			Connection con = getConnection();
			DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			Statement stm = con.createStatement();
			int newVersion = Integer.parseInt(version) + 1;

			int internet_bol = 0;
			int license_bol = 0;
			if (internet == true) {
				internet_bol = 1;
			}
			if (license == true) {
				license_bol = 1;
			}
			log.info(new Date() + " - 'image_name` = '" + newName
					+ "\n length of name=" + newName.length());

			log.info("now in UpdateImageData()");
			log.info("UPDATE bwLehrpool.m_VLData_imageInfo SET imageVersion = "
					+ newVersion + ",image_name = '" + newName
					+ "',`image_description` = '" + desc + "',`image_path` = '"
					+ image_path + "',`image_update_time` = '"
					+ formatter.format(new Date()) + "',`rec_change_time` = '"
					+ formatter.format(new Date())
					+ "',`cond_hasLicenseRestriction` = '" + license_bol
					+ "',`cond_hasInternetRestriction` = '" + internet_bol
					+ "',`cond_minRAM` = '"
					+ ram
					+ "',`cond_minCPUs` = '"
					+ cpu
					+ "',`image_isTemplate` = '"
					// + isTemplate
					+ "0'" + ",`content_operatingSystem` = " + ospk
					+ ",`image_filesize` = " + filesize
					+ ",`image_syncMode` = '" + shareMode
					+ "' WHERE GUID_imageID = '" + id + "' AND imageVersion = "
					+ version + ";");

			stm.executeUpdate("UPDATE bwLehrpool.m_VLData_imageInfo SET imageVersion = "
					+ newVersion
					+ ",image_name = '"
					+ newName
					+ "',`image_description` = '"
					+ desc
					+ "',`image_path` = '"
					+ image_path
					+ "',`image_update_time` = '"
					+ formatter.format(new Date())
					+ "',`rec_change_time` = '"
					+ formatter.format(new Date())
					+ "',`cond_hasLicenseRestriction` = '"
					+ license_bol
					+ "',`cond_hasInternetRestriction` = '"
					+ internet_bol
					+ "',`cond_minRAM` = '"
					+ ram
					+ "',`cond_minCPUs` = '"
					+ cpu
					+ "',`image_isTemplate` = '"
					// + isTemplate
					+ "0'"
					+ "',`content_operatingSystem` = "
					+ ospk
					+ ",`image_filesize` = "
					+ filesize
					+ ",`image_syncMode` = "
					+ shareMode
					+ " WHERE GUID_imageID = '"
					+ id
					+ "' AND imageVersion = "
					+ version + ";");

			con.commit();
			con.close();
			return 0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to UpdateImageData.");
			e.printStackTrace();
		}
		return -1;
	}

	public boolean deleteImage(String id, String version) {

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

			stm.executeUpdate("DELETE FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID = '"
					+ id + "' " + "AND imageVersion = '" + version + "';");

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

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to deleteImage.");
			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();
			Statement stm = con.createStatement();
			stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_lecture` SET `name` = '"
					+ newName
					+ "',`isActive` = '"
					+ active_bol
					+ "',`startTime` = '"
					+ start
					+ "',`endTime` = '"
					+ end
					+ "',`description` = '"
					+ desc
					+ "',`imageID` = '"
					+ pk_image
					+ "',`imageVersion` = '"
					+ imageversion
					+ "',`admin_changeTime` = '"
					+ formatter.format(new Date())
					+ "'WHERE `lectureID` = '" + id + "';");
			con.commit();
			con.close();
			log.info(new Date() + " - Succeeded to updateLectureData.");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to updateLectureData.");
			e.printStackTrace();
		}

		return 0;

	}

	public boolean connectedToLecture(String id, String version) {

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

			ResultSet rs = stm.executeQuery("SELECT lectureID FROM "
					+ "bwLehrpool.m_VLData_lecture WHERE imageID = '" + id
					+ "' AND imageVersion = '" + version + "';");
			boolean ret = rs.first();
			con.close();
			return ret;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date()
					+ " - Failed to execute method connectedToLecture.");
			e.printStackTrace();
		}

		return false;

	}

	public boolean deleteLecture(String id) {

		try {
			Connection con = getConnection();
			Statement stm = con.createStatement();
			stm.executeUpdate("DELETE FROM bwLehrpool.m_VLData_lecture WHERE lectureID = '"
					+ id + "';");

			con.commit();
			con.close();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to deleteLecture.");
			e.printStackTrace();
		}

		return false;
	}

	public String getFile(String imageid, String imageversion) {

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

			ResultSet rs = stm
					.executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo WHERE GUID_imageID = '"
							+ imageid
							+ "' AND imageVersion = '"
							+ imageversion
							+ "';");
			rs.next();
			String path = rs.getString("image_path");
			con.close();
			return path;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getFile.");
			e.printStackTrace();
		}

		return null;
	}

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

			ResultSet rs = stm
					.executeQuery("SELECT name, admin_createTime FROM bwLehrpool.m_VLData_lecture where lectureID='"
							+ id + "';");
			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) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getDeleteXMLData.");
			e.printStackTrace();
		}

		return null;
	}

	public int UpdateImagePath(String name) {
		try {
			Connection con = getConnection();
			Statement stm = con.createStatement();
			String image_path = "prod/" + name;

			log.info(new Date()
					+ " - Updating ImagePath with: UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `image_path` = '"
					+ image_path + "' WHERE `image_path` = '" + "temp/" + name
					+ "';");

			stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `image_path` = '"
					+ image_path
					+ "' WHERE `image_path` = '"
					+ "temp/"
					+ name
					+ "';");
			con.commit();
			con.close();
			return 0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - 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) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getAllUniversities.");
			e.printStackTrace();
		}

		return null;

	}

	public String getOSpk(String os, String architecture) {
		try {
			Connection con = getConnection();
			Statement stm = con.createStatement();
			log.info(new Date() + " - Getting PrimaryKey for OS: " + os
					+ " architecture: " + architecture);
			ResultSet rs = stm
					.executeQuery("SELECT operatingSystemID FROM bwLehrpool.m_operatingSystem where name like '"
							+ os
							+ "' and architecture like '"
							+ architecture
							+ "';");
			rs.first();
			String pkos = rs.getString("operatingSystemID");
			log.info(new Date() + " - Key is " + pkos);
			con.close();
			return pkos;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getOSpk.");
			e.printStackTrace();
		}

		return null;
	}

	public String getOS(String pk) {
		try {
			Connection con = getConnection();
			Statement stm = con.createStatement();
			// log.info(new Date() +
			// " - OS: "+os+" architecture: "+architecture);
			ResultSet rs = stm
					.executeQuery("SELECT name,architecture FROM bwLehrpool.m_operatingSystem where operatingSystemID = '"
							+ pk + "';");
			rs.first();
			String os = rs.getString("name") + " "
					+ rs.getString("architecture");
			con.close();
			return os;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - 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();
			Statement stm = con.createStatement();
			ResultSet data = stm
					.executeQuery("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= '"
							+ id + "' ;");
			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) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getLectureData.");
			e.printStackTrace();
		}
		return null;
	}

	public boolean checkUser(String username) {
		Statement stm;
		boolean ret = false;
		try {
			Connection con = getConnection();
			stm = con.createStatement();
			ResultSet rs = stm
					.executeQuery("Select * from m_user where loginName like '"
							+ username + "'");
			if (rs.next()) {
				ret = true;
				log.info(new Date() + " - User " + rs.getString("loginName")
						+ " exist.");
			} else {
				ret = false;
				log.info(new Date() + " - User " + rs.getString("loginName")
						+ " not exist.");
			}
			con.close();

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

			e.printStackTrace();
		}
		return ret;
	}

	public int getRoleID(String role) {

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

			ResultSet rs = stm
					.executeQuery("SELECT roleID FROM bwLehrpool.m_role where name like '"
							+ role + "';");
			rs.first();

			return rs.getInt("roleID");

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getRoleID.");
			e.printStackTrace();
		}
		return -1;
	}

	public String getLectureID(String name) {
		String id = null;
		try {
			Connection con = getConnection();
			Statement stm = con.createStatement();
			ResultSet lecture = stm
					.executeQuery("SELECT lectureID FROM bwLehrpool.m_VLData_lecture where name like '"
							+ name + "';");
			while (lecture.next()) {
				id = lecture.getString("lectureID");

			}
			con.close();
			return id;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to LectureID.");
			e.printStackTrace();
		}
		return id;
	}

	// get all users from satellite -- temp, tb replaced by allUsers bwLehrpool
	// does -NOT- return the user himself
	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) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			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;";

			// res =
			// stm.executeQuery("SELECT userID, Nachname, Vorname, mail FROM m_user WHERE userID !='"+
			// userID + "' ORDER BY Nachname ASC;");
			res = stm.executeQuery(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.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) {
			// TODO Auto-generated catch block
			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();
		Statement stm = null;
		ResultSet res = null;
		List<Person> list = new ArrayList<Person>();

		try {
			stm = con.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			res = stm
					.executeQuery("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!='"
							+ userID
							+ "' AND pmi.GUID_imageID='"
							+ imageID
							+ "' AND pmi.userID=u.userID ORDER BY u.Nachname ASC;");
			System.out
					.println("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!='"
							+ userID
							+ "' AND pmi.GUID_imageID='"
							+ imageID
							+ "' AND pmi.userID=u.userID ORDER BY u.Nachname ASC;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			boolean image_read, image_write, link_allowed, image_admin, lecture_read, lecture_write, lecture_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;
				lecture_read = false;
				lecture_write = false;
				lecture_admin = false;

				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) {
			// TODO Auto-generated catch block
			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 {
			stm = con.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			res = stm
					.executeQuery("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!='"+userID+"' AND pml.lectureID='"+lectureID+"' AND pml.userID=u.userID ORDER BY u.Nachname ASC;");
			System.out
					.println("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!='"+userID+"' AND pml.lectureID='"+lectureID+"' AND pml.userID=u.userID ORDER BY u.Nachname ASC;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			boolean image_read, image_write, link_allowed, image_admin, lecture_read, lecture_write, lecture_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;
				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) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}// end getPermissionForUserAndLecture
	
	
	
	
	
	
	

	public boolean updateImagePermissions() {
		boolean success = true;

		Connection con = getConnection();
		Statement stm = null;
		try {
			stm = con.createStatement();
			stm.executeQuery("");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return success;
	}// end setImagePermissions()

	public boolean deleteImagePermissions() {
		boolean success = true;

		Connection con = getConnection();
		Statement stm = null;
		try {
			stm = con.createStatement();
			stm.executeQuery("");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return success;
	}// end setImagePermissions()

	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();
			stm.executeUpdate("INSERT INTO `bwLehrpool`.`pm_VLData_image`(`GUID`,`GUID_imageID`,`roleID`,`userID`,`image_read`,`image_write`,`image_admin`,`link_allowed`)VALUES('"
					+ uid
					+ "','"
					+ pk_image
					+ "','"
					+ role
					+ "','"
					+ pk_person
					+ "','"
					+ read
					+ "','"
					+ write
					+ "','"
					+ admin
					+ "','"
					+ linkallowed + "');");
			con.commit();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - 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();
		Statement stm = null;

		try {
			stm = con.createStatement();

			log.info(new Date() + " - INSERT INTO pm_VLData_image (" + "GUID, "
					+ "GUID_imageID, " + "roleID, " + "userID, "
					+ "image_read, " + "image_write, " + "Image_admin, "
					+ "link_allowed)" + "VALUES(" + "'" + uid + "'," + "'"
					+ imageID + "'," + "1," + "'" + userID + "'," + isRead
					+ "," + isWrite + "," + isLinkAllowed + "," + isAdmin + ""
					+ ")");

			stm.executeUpdate("INSERT INTO pm_VLData_image (" + "GUID, "
					+ "GUID_imageID, " + "roleID, " + "userID, "
					+ "image_read, " + "image_write, " + "Image_admin, "
					+ "link_allowed)" + "VALUES(" + "'" + uid + "'," + "'"
					+ imageID + "'," + "1," + "'" + userID + "'," + isRead
					+ "," + isWrite + "," + isLinkAllowed + "," + isAdmin + ""
					+ ");");
			con.commit();
			con.close();
			log.info(new Date() + " - Written additional image rights.");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			log.info(new Date() + " - 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();

			stm.executeUpdate("INSERT INTO `bwLehrpool`.`pm_VLData_lecture`(`GUID`,`lectureID`,`userID`,`rec_read`,`rec_write`,`rec_admin`)VALUES('"
					+ uid
					+ "','"
					+ pk_lecture
					+ "','"
					+ pk_person
					+ "','"
					+ read + "','" + write + "','" + admin + "');");
			con.commit();
			log.info("Written lecture rights for " + pk_lecture);
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + "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();
		Statement stm = null;

		try {
			stm = con.createStatement();

			stm.executeUpdate("INSERT INTO pm_VLData_lecture (" + "GUID, "
					+ "lectureID, " + "userID, " + "rec_read, " + "rec_write, "
					+ "rec_admin )" + "VALUES('" + uid + "'," + "'" + lectureID
					+ "'," + "'" + userID + "'," + isRead + "," + isWrite + ","
					+ isAdmin + ");");
			con.commit();
			con.close();
			log.info(new Date() + " - Written additional lecture rights for '"
					+ userID + "'.");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			log.info(new Date()
					+ " - Failed to write additional lecture rights.");
		}
		return success;
	}// end writeAdditionalLectureRights

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

		try {

			stm = con.createStatement();
			int ret = stm
					.executeUpdate("DELETE FROM pm_VLData_image WHERE GUID_imageID = '"
							+ imageID + "' AND userID != '" + userID + "';");

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

		try {

			stm = con.createStatement();
			int ret = stm
					.executeUpdate("DELETE FROM pm_VLData_image WHERE GUID_imageID = '"
							+ imageID + "' AND userID != '" + userID + "';");

			con.commit();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}// end deleteAllAdditionalImagePermissions
	
	
	
	public void deleteAllAdditionalLecturePermissions(String lectureID, String userID) {
		Connection con = getConnection();
		Statement stm = null;

		try {

			stm = con.createStatement();
			int ret = stm
					.executeUpdate("DELETE FROM pm_VLData_lecture WHERE lectureID = '"
							+ lectureID + "' AND userID != '" + userID + "';");
		} catch (SQLException e) {
			e.printStackTrace();
		}

		try {

			stm = con.createStatement();
			int ret = stm
					.executeUpdate("DELETE FROM pm_VLData_lecture WHERE lectureID = '"
							+ lectureID + "' AND userID != '" + userID + "';");

			con.commit();

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

}// end class