path: root/Dozentenmodulserver/src/sql/SQL.java

package sql;

import java.sql.*;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Random;
import java.util.UUID;

import models.Configuration;

import org.apache.log4j.Logger;
import server.BinaryListener;

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);
			log.info(new Date() + " - Connection returned to Client.");
			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(Connection con, String user, String pass) {
		Statement stm;
		try {
			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();
			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(Connection con, String user) {
		Statement stm;
		try {
			stm = con.createStatement();

			int ret = stm
					.executeUpdate("DELETE FROM `bwLehrpool`.`FtpUsers` where User like '"
							+ user + "';");
			con.commit();
			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(Connection con) {
		try {
			Statement stm = con.createStatement();

			return stm
					.executeQuery("SELECT image_name FROM bwLehrpool.m_VLData_imageInfo;");

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

		return null;
	}

	public ResultSet getPathOfImage(Connection con, String image_id,
			String version) {
		try {
			Statement stm = con.createStatement();

			return stm
					.executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID='"
							+ image_id
							+ "' and imageVersion='"
							+ version
							+ "';");

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

		return null;
	}

	public String setInstitution(Connection con, String university) {
		try {
			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();
				return rs.getString("institutionID");
			} else {
				return ret.getString("institutionID");
			}

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

	public String setPerson(Connection con, String login, String lastname,
			String firstname, String mail, Date lastlogin, String Institution) {
		DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		try {
			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('"
						+ id
						+ "','"
						+ 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();
				return rs.getString("userID");
			} else {
				ret.first();
				return ret.getString("userID");
			}

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

	public boolean setImageData(Connection con, String pk_person, boolean license,
			boolean internet, long cpu, long ram, String imagename,
			String imagePath, boolean isTemplate, long filesize) {

		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 {
			Statement stm = con.createStatement();
			
			String uid = UUID.randomUUID().toString();
			stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`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`)VALUES('"
					+ uid // GUID_imageID
					+ "',1,'" // imageVersion
					+ imagename // image_name
					+ "','" + 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
					+ "',0" // 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
					+ "','" + isTemplate //image_isTemplate
					+ "','" + filesize //image_filesize
					+ "');");
			con.commit();

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

	}

	public ResultSet getImageList(Connection con) {
		try {
			Statement stm = con.createStatement();

			return stm
					.executeQuery("SELECT vl.GUID_imageID, vl.imageVersion,vl.image_name, vl.cond_hasLicenseRestriction, os.name, '' as lecture,vl.image_update_time,  Concat(u.Nachname,' ',u.Vorname) as user FROM 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, lect.name as lecture,  vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) as user 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;");

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

	public ResultSet getLectureList(Connection con) {
		try {
			Statement stm = con.createStatement();

			return 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;");

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

	public ResultSet getAllOS(Connection con) {

		try {
			Statement stm = con.createStatement();
			return stm
					.executeQuery("SELECT name FROM bwLehrpool.m_operatingSystem;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getAllOS.");
			e.printStackTrace();
		}

		return null;

	}

	public ResultSet getPersonData(Connection con, String Vorname,
			String Nachname) {
		try {
			Statement stm = con.createStatement();
			return 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;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getPersonData.");
			e.printStackTrace();
		}
		return null;
	}

	public int setLectureData(Connection con, 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 {
			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();

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

	}

	public ResultSet getImageIDandVersion(Connection con, String name) {
		try {
			Statement stm = con.createStatement();
			return stm
					.executeQuery("SELECT GUID_imageID, imageVersion FROM bwLehrpool.m_VLData_imageInfo where image_name like '"
							+ name + "';");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getImageIDandVersion.");
			e.printStackTrace();
		}
		return null;
	}

	public ResultSet getImageData(Connection con, String id, String version) {
		try {
			Statement stm = con.createStatement();
			return stm
					.executeQuery("SELECT image_name,cond_hasInternetRestriction,cond_hasLicenseRestriction, cond_minCPUs, cond_minRAM FROM bwLehrpool.m_VLData_imageInfo where GUID_imageID = '"
							+ id + "' and imageVersion = '" + version + "' ;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to getImageData.");
			e.printStackTrace();
		}
		return null;
	}

	public int UpdateImageData(Connection con, String name, String newName,String image_path,
			boolean license, boolean internet, long cpu, long ram, String id,
			String version, boolean isTemplate, long filesize) {
		try {
			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());
			
			stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `imageVersion` = '"
					+ newVersion
					+ "',`image_name` = '"
					+ newName
					+ "',`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
					+ "',`image_filesize` = '"
					+ filesize
					+ "' WHERE `GUID_imageID` = '"
					+ id
					+ "' AND `imageVersion` = '" + version + "';");
			con.commit();
			return 0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to UpdateImageData.");
			e.printStackTrace();
		}
		return -1;
	}

	public boolean deleteImage(Connection con, String id, String version) {

		try {

			Statement stm = con.createStatement();

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

			con.commit();

			return true;

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

		return false;
	}

	public int updateLectureData(Connection con, String pk_image,
			int 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 {
			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();
			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(Connection con, String id, String version) {

		try {

			Statement stm = con.createStatement();

			ResultSet rs = stm.executeQuery("SELECT lectureID FROM "
					+ "bwLehrpool.m_VLData_lecture WHERE imageID = '" + id
					+ "' AND imageVersion = '" + version + "';");

			return rs.first();

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

		return false;

	}

	public boolean deleteLecture(Connection con, String id) {

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

			con.commit();

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

		return false;
	}

	public String getFile(Connection con, String imageid, String imageversion) {

		try {
			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();
			return rs.getString("image_path");

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

		return null;
	}

	public ResultSet getDeleteXMLData(Connection con, String id) {
		try {
			Statement stm = con.createStatement();

			ResultSet rs = stm
					.executeQuery("SELECT name, admin_createTime FROM bwLehrpool.m_VLData_lecture where lectureID='"
							+ id + "';");

			return rs;

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

		return null;
	}
	
	public int UpdateImagePath(Connection con, String name) {
		try {

			Statement stm = con.createStatement();
			String image_path="prod/"+name;
			
			stm.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_imageInfo` SET `image_path` = '"
					+ image_path
					+ "' WHERE `image_path` = '"
					+ "temp/"+name + "';");
			con.commit();
			return 0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log.info(new Date() + " - Failed to UpdateImageData.");
			e.printStackTrace();
		}
		return -1;
	}
}