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 server.generated.Server.Client;


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) {
			 
			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) {
			 
			log.info("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("Created FTPUser " + user + " : " + pass
		     + ".");
		   return ret;
		  } catch (SQLException e) {
		    
		   log.info("Failed to writeFTPUser.");
		   e.printStackTrace();
		  }
		  return -1;
		 }

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

			String sql = "DELETE FROM `bwLehrpool`.`FtpUsers` where User like ?;";
			PreparedStatement prest = con.prepareStatement(sql);
			prest.setString(1, user);
			
			int ret = prest.executeUpdate();
			
			con.commit();
			con.close();
			log.info("FTPUser " + user + " deleted.");
			return ret;
		} catch (SQLException e) {
			 
			log.info("Failed to DeleteUser "+user+".");
			e.printStackTrace();
		}
		return -1;
	}

	
	
	
	
	
	//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) {
		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();
				
				 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 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, String uid) 
	{

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

		log.info("userID came as pk_person to SQL as: "+pk_person);
		log.info("pk_os is: "+pk_os);
		log.info("uid is: "+uid);
		
		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, uid);
			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, ram);
			prest.setLong(13, cpu);
			prest.setLong(14, filesize);
			prest.setString(15, shareMode);
			
			prest.executeUpdate();
			con.commit();
			//con.commit();
			con.close();
		} catch (SQLException e) {
			 
			log.info("Failed to setImageData.");
			e.printStackTrace();
		}
		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();
		}
		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;
	}
	

	
	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
			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()) 
			{
				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 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("'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 + ";");
			*/

			String sql = "UPDATE bwLehrpool.m_VLData_imageInfo SET imageVersion = "
					+ "?"
					+ ",image_name = "
					+ "?"
					+ ",image_description = "
					+ "?"
					+ " ,image_path =  "
					+ "?"
					+ " ,image_update_time =  '"
					+ formatter.format(new Date())
					+ "' ,rec_change_time =  '"
					+ formatter.format(new Date())
					+ "' ,cond_hasLicenseRestriction =  "
					+ "?"
					+ " ,cond_hasInternetRestriction =  "
					+ "?"
					+ " ,cond_minRAM =  "
					+ "?"
					+ " ,cond_minCPUs =  "
					+ "?"
					+ " ,image_isTemplate =  "
					// + isTemplate
					+ "0 "
					+ ",content_operatingSystem = "
					+ "?"
					+ ",image_filesize = "
					+ "?"
					+ ",image_syncMode =  "
					+ "?"
					+ "  WHERE GUID_imageID =  "
					+ "?"
					+ "  AND imageVersion = "
					+ "?" + ";";
					
			PreparedStatement prest = con.prepareStatement(sql);
			prest.setInt(1, newVersion);
			prest.setString(2, newName);
			prest.setString(3, desc);
			prest.setString(4, image_path);
			prest.setInt(5, license_bol);
			prest.setInt(6, internet_bol);
			prest.setLong(7, ram);
			prest.setLong(8, cpu);
			prest.setString(9, ospk);
			prest.setLong(10, filesize);
			prest.setString(11, shareMode);
			prest.setString(12, id);
			prest.setString(13, version);
			
			prest.executeUpdate();
			
			con.commit(); //needed, though executeUpdate()!

			//con.commit();
			con.close();
			return 0;
		} catch (SQLException e) {
			 
			log.info("Failed to UpdateImageData.");
			e.printStackTrace();
		}
		return -1;
	}

	
	
	
	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) {

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

			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();
			
			
			rs.next();
			String path = rs.getString("image_path");
			con.close();
			return path;

		} catch (SQLException e) {
			 
			log.info("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();

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

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

			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();
		Statement stm = null;
		ResultSet res = null;
		List<Person> list = new ArrayList<Person>();

		try {
			stm = con.createStatement();
		} catch (SQLException e) {
			 
			e.printStackTrace();
		}
		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, 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) {
			 
			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 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) {
			 
			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();
			Statement stm;
			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