package net.sourceforge.jsurveyor;

import java.net.*;
import java.sql.*;
import java.util.*;

/** 
 * This class handles the interaction with the underlying storage technology. 
 * The set of methods defined here, provides persistence transparency for all 
 * other classes within this application. It encapsulates all persistence methods
 * in one single class.   
 * 
 * @version 1.0
 * @author Claus Methmann Christensen
 * @author Anders Spliid Hansen
 * @author Esben Iversen Hansen
 * @author Lars H Jensen
 * @author Marcus Overheu
 * @author Thomas Ramdal
 * @author Peter Thomsen
 */

public class DataAccess {

	/**
	 * Contains the MySQL login information. onCSnetwork() checks if the host is inside the
	 * cs.auc.dk network. If it's not, SSH port forward must be used (local source port 3306).
	*/
	final static String DB_LOGIN =
		onCSnetwork()
			? "//fyr.cs.auc.dk:3306/?user=e2-203&password=damnhot"
			: "//localhost:3306/?user=e2-203&password=damnhot";

	/**
	 * Stores the passed User object in the corresponding User SQL-table 
	 * @pre User usr has the attribute Username set.
	 * @post The User object has been stored or updated as a row in the database.
	 * @param usr User
	 */
	public static void storeUser(User usr) {

		sendUpdate(
			"REPLACE INTO User VALUES ('"
				+ usr.getUserName()
				+ "','"
				+ usr.getPassword()
				+ "','"
				+ (usr.getAccessControl()).getUserType()
				+ "','"
				+ usr.getEmail()
				+ "','"
				+ usr.getFullName()
				+ "','"
				+ usr.getCity()
				+ "','"
				+ usr.getCountry()
				+ "','"
				+ usr.getRefParents()
				+ "','"
				+ usr.getAccessControl().getAdminUserGroup()
				+ "')");
	} //end storeUser

	/**
	 * Stores the passed UserGroup object in the corresponding UserGroup SQL-table. 
	 * @pre none
	 * @post The UserGroup object has been stored as a row in the database. For a new 
	 * object, an integer UserGroupID was attached to the object passed as an argument. 
	 * If the object already was in the database, the row was updated.
	 * @param group UserGroup
	 */
	public static void storeUserGroup(UserGroup group) {

		boolean hasID =
			(countTuples("Usergroup",
				"UsergroupID",
				"" + group.getUserGroupID())
				!= 0);

		int id =
			sendUpdateAndGetID(
				"REPLACE INTO Usergroup VALUES ('"
					+ group.getUserGroupID()
					+ "','"
					+ group.getGroupName()
					+ "','"
					+ group.getRefParents()
					+ "','"
					+ group.getRefChildren()
					+ "')");

		if (!hasID) {
			group.setUserGroupID(id);
		}

	} //end storeUserGroup

	/**
	 * Stores the passed Answer object in the corresponding Answer SQL-table. 
	 * Differs between the two types of answers, AnswerFreeText and AnswerMultipleChoice, 
	 * with a null in the choice- or freetext-field, respectively.
	 * @pre none
	 * @post The Answer object has been stored as a row in the database. For a new 
	 * object, an integer AnswerID was attached to the object passed as an argument. 
	 * If the object already was in the database, the row was updated.  
	 * @param answer Answer
	 */
	public static void storeAnswer(Answer answer) {

		boolean hasID =
			(countTuples("Answer", "AnswerID", "" + answer.getAnswerID()) != 0);
		int id = -1;

		if (answer instanceof AnswerFreeText) {
			id =
				sendUpdateAndGetID(
					"REPLACE INTO Answer VALUES ('"
						+ answer.getAnswerID()
						+ "','"
						+ answer.getQuestionID()
						+ "','"
						+ ((AnswerFreeText) answer).getFreeText()
						+ "',null)");
		} else if (answer instanceof AnswerMultipleChoice) {
			id =
				sendUpdateAndGetID(
					"REPLACE INTO Answer VALUES ('"
						+ answer.getAnswerID()
						+ "','"
						+ answer.getQuestionID()
						+ "',null,'"
						+ ((AnswerMultipleChoice) answer).getChoice()
						+ "')");
		}
		if (!hasID)
			answer.setAnswerID(id);
	} // end storeAnswer

	/**
	 * Stores the passed Evaluation object in the corresponding Evaluation SQL-table. 
	 * @pre none
	 * @post The Evaluation object has been stored as a row in the database. For a new 
	 * object, an integer EvaluationID was attached to the object passed as an argument. 
	 * If the object already was in the database, the row was updated.  
	* @param evaluation Evaluation
	 */
	public static void storeEvaluation(Evaluation evaluation) {
		boolean hasID =
			(countTuples("Evaluation",
				"EvaluationID",
				"" + evaluation.getEvaluationID())
				!= 0);
		int id = -1;

		id =
			sendUpdateAndGetID(
				"REPLACE INTO Evaluation VALUES ('"
					+ evaluation.getEvaluationID()
					+ "','"
					+ evaluation.getUserName()
					+ "','"
					+ evaluation.getTimestamp()
					+ "','"
					+ evaluation.getQuestionnaireID()
					+ "','"
					+ evaluation.getAnswers()
					+ "')");

		if (!hasID)
			evaluation.setEvaluationID(id);
	} // end storeEvaluation

	/**
	 * Stores the passed Questionnaire object in the corresponding Questionnaire SQL-table. 
	 * @pre none
	 * @post The Questionnaire object has been stored as a row in the database. For a new 
	 * object, an integer QuestionnaireID was attached to the object passed as an argument. 
	 * If the object already was in the database, the row was updated.  
	 * @param questionnaire Questionnaire
	 */
	public static void storeQuestionnaire(Questionnaire questionnaire) {

		boolean hasID =
			(countTuples("Questionnaire",
				"QuestionnaireID",
				"" + questionnaire.getQuestionnaireID())
				!= 0);
		int id = -1;
		int isActive = questionnaire.getState() ? 1 : 0;

		id =
			sendUpdateAndGetID(
				"REPLACE INTO Questionnaire VALUES ('"
					+ questionnaire.getQuestionnaireID()
					+ "','"
					+ questionnaire.getOwner()
					+ "','"
					+ questionnaire.getQuestionnaireName()
					+ "','"
					+ isActive
					+ "','"
					+ questionnaire.getRefUserGroups()
					+ "','"
					+ questionnaire.getRefQuestionPages()
					+ "','"
					+ questionnaire.getIntroText()
					+ "')");

		if (!hasID)
			questionnaire.setQuestionnaireID(id);

	} // end storeQuestionnaire

	/**
	 * Stores the passed QuestionPage object in the corresponding QuestionPage SQL-table. 
	 * @pre none
	 * @post The QuestionPage object has been stored as a row in the database. For a new 
	 * object, an integer QuestionPageID was attached to the object passed as an argument. 
	 * If the object already was in the database, the row was updated.  
	 * @param questionPage QuestionPage
	 */
	public static void storeQuestionPage(QuestionPage questionPage) {

		boolean hasID =
			(countTuples("Questionpage",
				"QuestionPageID ",
				"" + questionPage.getQuestionPageID())
				!= 0);
		int id = -1;

		id =
			sendUpdateAndGetID(
				"REPLACE INTO Questionpage VALUES ('"
					+ questionPage.getQuestionPageID()
					+ "','"
					+ questionPage.getRefQuestions()
					+ "')");

		if (!hasID)
			questionPage.setQuestionPageID(id);
	} // end storeQuestionPage

	/**
	 * Stores the passed Question object in the corresponding Question SQL-table. 
	 * Differs between the two types of questions, QuestionFreeText and 
	 * QuestionMultipleChoice, with a null in the choices-field.
	 * @pre none
	 * @post The Question object has been stored as a row in the database. For a new 
	 * object, an integer QuestionID was attached to the object passed as an argument. 
	 * If the object already was in the database, the row was updated.
	 * @param question Question
	 */
	public static void storeQuestion(Question question) {

		boolean hasID =
			(countTuples("Question",
				"QuestionID",
				"" + question.getQuestionID())
				!= 0);
		int id = -1;
		// maps boolean true or false to integer 1 or 0
		int isWriteable = question.isWriteable() ? 1 : 0;
		int isRequired = 0; // not implemented
		int isDependent = question.getDepends() ? 1 : 0;

		if (question instanceof QuestionFreeText) {
			id =
				sendUpdateAndGetID(
					"REPLACE INTO Question VALUES ('"
						+ question.getQuestionID()
						+ "','"
						+ question.getText()
						+ "',"
						+ isWriteable
						+ ","
						+ isRequired
						+ ","
						+ isDependent
						+ ",'"
						+ question.getDependency()
						+ "',NULL)");
		} // end if QuestionFreeText
		
		else if (question instanceof QuestionMultipleChoice) {

			String[] choices = ((QuestionMultipleChoice) question).getChoice();
			String choicesDB = " ";
			// convert the String[] to a String which is appropriate for DB
			// storage
			for (int i = 0; i < choices.length; i++)
				choicesDB += choices[i] + "|*|*|";
			id =
				sendUpdateAndGetID(
					"REPLACE INTO Question VALUES ('"
						+ question.getQuestionID()
						+ "','"
						+ question.getText()
						+ "',"
						+ isWriteable
						+ ","
						+ isRequired
						+ ","
						+ isDependent
						+ ",'"
						+ question.getDependency()
						+ "','"
						+ choicesDB
						+ "')");
		} // end if question multiple choice

		// attach the ID, if the object didn't exist in DB
		if (!hasID)
			question.setQuestionID(id);
	} // end storeQuestion

	/**
	 * Deletes the row containing the representation of the Evaluation object 
	 * with the evaluationID passed as argument, along with all answers corresponding 
	 * to this evaluation. 
	 * @pre the row in the Evaluation table with EvaluationID passed as argument, exists. 
	 * @post the row containing the representation of the Evaluation object, along 
	 * with the rows from the Answer table containing all attached answers, has been deleted.
	 * @param evaluationID
	 */
	public static void deleteEvaluation(int evaluationID) {
		Evaluation tempEval = DataAccess.retrieveEvaluation(evaluationID);
		ArrayList answers = tempEval.getAnswers();
		for (int i = 0; i < answers.size(); i++) {
			deleteAnswer(
				Integer.parseInt(((String) answers.get(i)).substring(1)));
		} // end for
		sendUpdate("DELETE FROM Evaluation WHERE EvaluationID=" + evaluationID);
	} // end deleteEvaluation

	/**
	 * Deletes all evaluations generated from the Questionnaire with questionnaireID, by 
	 * using the method deleteEvaluation(int evaluationID) recursively. 
	 * @pre none 
	 * @post the rows containing the representation of the evaluations generated from the 
	 * Questionnaire with the questionnaireID passed as argument, has been deleted.
	 * @param questionnaireID
	 */
	public static void deleteEvaluationsForQuestionnaire(int questionnaireID) {
		int evalID;
		try {
			Connection con = getConnection();
			Statement stmt = getStatement(con);
			ResultSet rs =
				stmt.executeQuery(
					"SELECT * FROM Evaluation WHERE QuestionnaireID="
						+ questionnaireID);

			while (rs.next()) {
				evalID = rs.getInt("EvaluationID");
				deleteEvaluation(evalID);
			} // end while
			stmt.close();
			con.close();
		} catch (SQLException e) {
			printSQLExceptions(e);
		}
	} // end deleteEvaluationsForQuestionnaire

	/**
	 * Deletes the row containing the representation of the Questionnaire object 
	 * with the QuestionnaireID passed as argument, along with all QuestionPages
	 * corresponding to this Questionnaire. 
	 * @pre the row in the Questionnaire table with QuestionnaireID passed as 
	 * argument, exists. 
	 * @post the row containing the representation of the Evaluation object, along 
	 * with the rows from the Answer table containing all attached answers, has been deleted.
	 * @param questionnaireID
	 */
	public static void deleteQuestionnaire(int questionnaireID) {
		Questionnaire questionnaire =
			DataAccess.retrieveQuestionnaire(questionnaireID);
		ArrayList traverse = questionnaire.getRefQuestionPages();
		for (int i = 0; i < traverse.size(); i++) {
			DataAccess.deleteQuestionPage(
				Integer.parseInt(((String) traverse.get(i)).substring(1)));
		} // end for
		sendUpdate(
			"DELETE FROM Questionnaire WHERE QuestionnaireID="
				+ questionnaireID);
	} // end deleteQuestionnaire

	/**
	 * Deletes the row containing the representation of the QuestionPage object,
	 * with the QuestionPageID passed as argument.
	 * @pre none 
	 * @post the row containing the representation of the QuestionPage object has 
	 * been deleted.
	 * @param questionPageID int
	 */
	public static void deleteQuestionPage(int questionPageID) {
		sendUpdate(
			"DELETE FROM Questionpage WHERE QuestionPageID =" + questionPageID);
	}

	/**
	 * Deletes the row containing the representation of the Question object,
	 * with the QuestionID passed as argument.
	 * @pre none 
	 * @post the row containing the representation of the Question object has 
	 * been deleted.
	 * @param questionID int
	 */
	public static void deleteQuestion(int questionID) {
		sendUpdate("DELETE FROM Question WHERE QuestionID=" + questionID);
	}

	/**
	 * Deletes the row containing the representation of the Answer object,
	 * with the AnswerID passed as argument.
	 * @pre none 
	 * @post the row containing the representation of the Answer object has 
	 * been deleted.
	 * @param answerID int
	 */
	public static void deleteAnswer(int answerID) {
		sendUpdate("DELETE FROM Answer WHERE AnswerID=" + answerID);
	}

	/**
	 * Deletes the row containing the representation of the User object,
	 * and updates all UserGroups which the User was a member of.
	 * @pre the row in the User table with UserName key passed as argument, exists. 
	 * @post the row containing the representation of the User object has 
	 * been deleted, and all affected UserGroups has been updated.  
	 * @param key String
	 */
	public static void deleteUser(String key) {
		User user = retrieveUser(key);
		ArrayList parents = user.getRefParents();
		UserGroup traverse;

		//traverse all users parents, and remove every occurence of user
		for (int i = 0; i < parents.size(); i++) {

			String refparent = (String) parents.get(i);

			traverse =
				DataAccess.retrieveUserGroup(
					Integer.parseInt(refparent.substring(1)));

			traverse.removeChild(user);
			storeUserGroup(traverse);
		}
		sendUpdate("DELETE FROM User WHERE Username='" + key + "'");
	} // end deleteUser

	/**
	 * Deletes the row containing the representation of the UserGroup object,
	 * and updates all Users and UserGroups which the UserGroup was affected with.
	 * @pre the row in the UserGroup table with UserGroupID passed as argument, exists. 
	 * @post the row containing the representation of the UserGroup object has 
	 * been deleted, and all affected Users and UserGroups has been updated.  
	 * @param groupID int
	 */
	public static void deleteUserGroup(int groupID) {
		UserGroup userGroup = retrieveUserGroup(groupID);
		ArrayList parents = userGroup.getRefParents();
		ArrayList children = userGroup.getRefChildren();
		UserComponent traverse;
		//remove all references from usergroups parents
		for (int i = 0; i < parents.size(); i++) {
			String refParent = (String) parents.get(i);
			traverse =
				DataAccess.retrieveUserGroup(
					Integer.parseInt(refParent.substring(1)));
			traverse.removeChild(userGroup);
			storeUserGroup((UserGroup) traverse);
		} // end for
		//remove all references from usergroups children
		for (int i = 0; i < children.size(); i++) {
			String refChildren = (String) children.get(i);
			// the child is a user
			if (refChildren.startsWith("U")) {
				traverse = DataAccess.retrieveUser(refChildren.substring(1));
				traverse.removeParent(userGroup);
				storeUser((User) traverse);
			}
			// the child is a usergroup
			if (refChildren.startsWith("G")) {
				traverse =
					DataAccess.retrieveUserGroup(
						Integer.parseInt(refChildren.substring(1)));
				traverse.removeParent(userGroup);
				storeUserGroup((UserGroup) traverse);
			}
		} // end for

		sendUpdate(
			"DELETE FROM Usergroup WHERE UsergroupID='"
				+ userGroup.getUserGroupID()
				+ "'");
	} // end deleteUserGroup

	/**
	 * Retrieves an Answer object from the corresponding Answer SQL-table.
	 * @pre none
	 * @post if the Answer didn't exist in the database, an AnswerFreeText object
	 * with IDs and choice set to -1 is returned.
	 * @param answerID int
	 * @return AnswerFreeText or AnswerMultipleChoice
	 */
	public static Answer retrieveAnswer(int answerID) {
		boolean testChoice = true;
		int qID = -1, aID = -1, choice = -1;
		String result = new String();

		try {
			Connection con = getConnection();
			Statement stmt = getStatement(con);
			ResultSet rs =
				stmt.executeQuery(
					"SELECT * FROM Answer WHERE AnswerID=" + answerID);

			while (rs.next()) {
				aID = rs.getInt("AnswerID");
				qID = rs.getInt("QuestionID");
				result = rs.getString("Freetext");
				testChoice = rs.wasNull();
				choice = rs.getInt("Choice");
				// checks if choice was null

			} // end while
			stmt.close();
			con.close();
		} catch (SQLException e) {
			printSQLExceptions(e);
		}

		if (testChoice == false) {
			return new AnswerFreeText(aID, qID, result);
		} else {
			return new AnswerMultipleChoice(aID, qID, choice);
		}
	} // end storeAnswer

	/**
	 * Retrieves all Users from database, except those of UserType root.
	 * @pre none
	 * @return ArrayList of Users
	 */
	public static ArrayList retrieveAllUsers() {

		ArrayList users = new ArrayList();

		try {
			Connection con = getConnection();
			Statement stmt = getStatement(con);
			ResultSet rs = stmt.executeQuery("SELECT * FROM User");

			while (rs.next()) {
				// add all users, except for root users
				if (!rs.getString("Usertype").equals("root"))
					users.add(convertSelectToUser(rs));
			}
			stmt.close();
			con.close();
		} catch (SQLException e) {
			printSQLExceptions(e);
		}
		return users;
	} //end retrieveAllUser

	/**
	 * Retrieves all evaluations corresponding to the Questionnaire with
	 * QuestionnaireID passed as argument, from the database.
	 * @pre none
	 * @param questionnaireID int
	 * @return ArrayList of Evaluation
	 */
	public static ArrayList retrieveAllEvaluations(int questionnaireID) {
		ArrayList evaluations = new ArrayList();

		try {
			Connection con = getConnection();
			Statement stmt = getStatement(con);
			ResultSet rs = stmt.executeQuery("SELECT * FROM Evaluation");

			while (rs.next()) {
				// add all Evaluations from a certain questionnaire
				if (rs.getInt("QuestionnaireID") == (questionnaireID))
					evaluations.add(convertSelectToEvaluation(rs));
			}
			stmt.close();
			con.close();
		} catch (SQLException e) {
			printSQLExceptions(e);
		}
		return evaluations;
	}

	/**
	 * Retrieves a Evaluation object from the database.
	 * @pre none
	 * @post if the Evaluation didn't exist, a Evaluation object with IDs 
	 * set to -1 is returned.
	 * @param evaluationID int
	 * @return Evaluation
	 */
	public static Evaluation retrieveEvaluation(int evaluationID) {
		int evalID = -1;
		int questionnaireID = -1;
		String userName = new String();
		long timestamp = -1;
		String answers = new String();

		try {
			Connection con = getConnection();
			Statement stmt = getStatement(con);
			ResultSet rs =
				stmt.executeQuery(
					"SELECT * FROM Evaluation WHERE evaluationID="
						+ evaluationID);

			while (rs.next()) {
				evalID = rs.getInt("EvaluationID");
				userName = rs.getString("UserName");
				timestamp = rs.getLong("Timestamp");
				questionnaireID = rs.getInt("QuestionnaireID");
				answers = rs.getString("Answers");
			} // end while
			stmt.close();
			con.close();
		} catch (SQLException e) {
			printSQLExceptions(e);
		}

		return new Evaluation(
			evalID,
			userName,
			questionnaireID,
			timestamp,
			convertStringToArrayList(answers));
	} // end retrieveEvaluation

	/**
	 * Retrieves a Questionnaire object from the database.
	 * @pre none
	 * @post if the Questionnaire didn't exist, a Questionnaire object with 
	 * ID set to 0 is returned. 
	 * @param key int
	 * @return Questionnaire
	 */
	public static Questionnaire retrieveQuestionnaire(int key) {
		String[] result = new String[5];
		int questionnaireID = 0;
		boolean state = true;
		try {
			Connection con = getConnection();
			Statement stmt = getStatement(con);
			ResultSet rs =
				stmt.executeQuery(
					"SELECT * FROM Questionnaire WHERE QuestionnaireID='"
						+ key
						+ "'");

			while (rs.next()) {
				questionnaireID = rs.getInt("QuestionnaireID");
				result[0] = rs.getString("Owner");
				result[1] = rs.getString("QuestionnaireName");
				state = rs.getInt("State") != 0;
				result[2] = rs.getString("UserGroups");
				result[3] = rs.getString("QuestionPages");
				result[4] = rs.getString("IntroText");
			}
			stmt.close();
			con.close();
		} catch (SQLException e2) {
			System.err.println(e2);
		}
		assert(questionnaireID != 0) : "The questionnaire Doesn�t exist";

		return new Questionnaire(
			questionnaireID,
			result[0],
			result[1],
			state,
			convertStringToArrayList(result[2]),
			convertStringToArrayList(result[3]),
			result[4]);
	} // end retrieveQuestionnaire

	/**
	 * Retrieves a QuestionPage object from the database.
	 * @pre none
	 * @post if the QuestionPage didn't exist, a QuestionPage object with 
	 * ID set to 0 is returned.
	 * @param key int
	 * @return QuestionPage
	 */
	public static QuestionPage retrieveQuestionPage(int key) {
		String result = new String();
		int questionPageID = 0;
		boolean state = true;
		try {
			Connection con = getConnection();
			Statement stmt = getStatement(con);
			ResultSet rs =
				stmt.executeQuery(
					"SELECT * FROM Questionpage WHERE QuestionPageID='"
						+ key
						+ "'");

			while (rs.next()) {
				questionPageID = rs.getInt("QuestionPageID");
				result = rs.getString("Questions");
			}
			stmt.close();
			con.close();
		} catch (SQLException e2) {
			System.err.println(e2);
		}
		assert(questionPageID != 0) : "The questionnaire Does not exist";

		return new QuestionPage(
			questionPageID,
			convertStringToArrayList(result));
	} // end retrieveQuestionPage

	/**
	 * Retrieves a User object from the database.
	 * @pre none
	 * @post if the User didn't exist, a User object with all fields set to
	 * empty strings, is returned.
	 * @param key String
	 * @return User
	 */
	public static User retrieveUser(String key) {

		String[] result = new String[9];

		try {
			Connection con = getConnection();
			Statement stmt = getStatement(con);
			ResultSet rs =
				stmt.executeQuery(
					"SELECT * FROM User WHERE Username='" + key + "'");
			while (rs.next()) {
				result[0] = rs.getString("Username");
				result[1] = rs.getString("Password");
				result[2] = rs.getString("Usertype");
				result[3] = rs.getString("Email");
				result[4] = rs.getString("Fullname");
				result[5] = rs.getString("City");
				result[6] = rs.getString("Country");
				result[7] = rs.getString("Adminusergroup");
				result[8] = rs.getString("Parents");
			}
			stmt.close();
			con.close();
		} catch (SQLException e) {
			printSQLExceptions(e);
		}

		return new User(
			result[0],
			result[1],
			result[2],
			result[3],
			result[4],
			result[5],
			result[6],
			convertStringToArrayList(result[7]),
			convertStringToArrayList(result[8]));
	} //end retrieveUser

	/**
	 * Retrieves a UserGroup object from the database.
	 * @pre none
	 * @post if the UserGroup didn't exist, a UserGroup object with ID set to -1 
	 * and all textfields set to empty strings, is returned.
	 * @param groupID int
	 * @return UserGroup
	 */
	public static UserGroup retrieveUserGroup(int groupID) {

		String[] result = new String[3];
		int gID = -1;

		try {
			Connection con = getConnection();
			Statement stmt = getStatement(con);

			ResultSet rs =
				stmt.executeQuery(
					"SELECT * FROM Usergroup WHERE UsergroupID='"
						+ groupID
						+ "'");

			while (rs.next()) {
				gID = rs.getInt("UsergroupID");
				result[0] = rs.getString("Groupname");
				result[1] = rs.getString("Children");
				result[2] = rs.getString("Parent");
			}
			stmt.close();
			con.close();
		} catch (SQLException e) {
			printSQLExceptions(e);
		}

		return new UserGroup(
			gID,
			result[0],
			convertStringToArrayList(result[1]),
			convertStringToArrayList(result[2]));
	} //end retrieveUserGroup

	/**
	 * Retrieves all UserGroup objects from the database.
	 * @pre none
	 * @return UserGroup ArrayList
	 */
	public static ArrayList retrieveAllUserGroups() {

		ArrayList usergroups = new ArrayList();

		try {
			Connection con = getConnection();
			Statement stmt = getStatement(con);
			ResultSet rs = stmt.executeQuery("SELECT * FROM Usergroup");

			while (rs.next()) {
				usergroups.add(convertSelectToUserGroup(rs));
			}
			stmt.close();
			con.close();
		} catch (SQLException e) {
			printSQLExceptions(e);
		}
		return usergroups;
	} //end retrieveAllUser

	/**
	* Retrieves all UserGroup objects which has no parents, from the database.
	* @pre none
	* @return UserGroup ArrayList
	*/
	public static ArrayList retrieveAllRootUserGroups() {

		String parents;
		ArrayList usergroups = new ArrayList();

		try {
			Connection con = getConnection();
			Statement stmt = getStatement(con);
			ResultSet rs = stmt.executeQuery("SELECT * FROM Usergroup");

			while (rs.next()) {
				try {
					if (rs.getString("Parent") == "NULL"
						|| rs.getString("Parent") == "[]")
						usergroups.add(convertSelectToUserGroup(rs));
				} catch (SQLException e) {
					printSQLExceptions(e);
				}
			} // end while
			stmt.close();
			con.close();
		} catch (SQLException e) {
			printSQLExceptions(e);
		}
		return usergroups;
	} //end retrieveAllUser

	/**
	* Retrieves a Question object from the database. 
	* @pre none
	* @post if the Question object didn't exist, a Question object with ID set to -1 
	* and all textfields set to empty strings, is returned.
	* @param questionID int
	* @return Question
	*/
	public static Question retrieveQuestion(int questionID) {

		int qID = -1;
		String text = new String();
		String choice = new String();
		boolean writeable = false;
		boolean required = false;
		boolean depends = false;
		String dependency = new String();
		boolean choiceWasNull = false;
		Question result;

		try {
			Connection con = getConnection();
			Statement stmt = getStatement(con);
			ResultSet rs =
				stmt.executeQuery(
					"SELECT * FROM Question WHERE QuestionID=" + questionID);

			while (rs.next()) {
				qID = rs.getInt("QuestionID");
				text = rs.getString("Text");

				//maps int 1 to true, int 0 to false
				writeable = (rs.getInt("Writeable") == 1);
				required = (rs.getInt("Required") == 1);
				depends = (rs.getInt("Depends") == 1);
				dependency = rs.getString("Dependency");
				choice = rs.getString("Choice");
				choiceWasNull = rs.wasNull();
			} // end while
			stmt.close();
			con.close();
		} catch (SQLException e) {
			printSQLExceptions(e);
		}

		// if choices is null, this is a FreeText question
		if (choiceWasNull)
			result =
				new QuestionFreeText(
					qID,
					text,
					writeable,
					required,
					depends,
					convertStringToArrayList(dependency));
		else {
			ArrayList tmp = convertStringToArrayList(choice, "|*|*|");
			String[] choiceArray = new String[tmp.size()];
			for (int i = 0; i < tmp.size(); i++)
				choiceArray[i] = (String) tmp.get(i);

			result =
				new QuestionMultipleChoice(
					qID,
					text,
					writeable,
					required,
					depends,
					convertStringToArrayList(dependency),
					choiceArray);
		} // end else

		return result;
	} // end retrieveQuestion

	/**
	 * Retrieves all Questionnaire objects from the database with User usr as owner.
	 * @pre none
	 * @post if the User passed as argument doesn't exist, an empty ArrayList is returned.
	 * @param usr User
	 * @return ArrayList
	 */
	public static ArrayList retrieveQuestionnairesForOwner(User usr) {
		
		ArrayList returnList = new ArrayList();
		String result[] = new String[5];
		
		try {
			Connection con = getConnection();
			Statement stmt = getStatement(con);
			ResultSet rs =
				stmt.executeQuery(
					"SELECT * FROM Questionnaire WHERE Owner='"
						+ usr.toString()
						+ "'");
						
			while (rs.next()) {
				
				int questionnaireID = rs.getInt("QuestionnaireID");
				result[0] = rs.getString("Owner");
				result[1] = rs.getString("QuestionnaireName");
				boolean state = rs.getInt("State") != 0;
				result[2] = rs.getString("UserGroups");
				result[3] = rs.getString("QuestionPages");
				result[4] = rs.getString("IntroText");
				returnList.add(
					new Questionnaire(
						questionnaireID,
						result[0],
						result[1],
						state,
						convertStringToArrayList(result[2]),
						convertStringToArrayList(result[3]),
						result[4]));
			} // end while
			stmt.close();
			con.close();
		} catch (SQLException e) {
			printSQLExceptions(e);
		}

		return returnList;
	}

	/**
	 * Fetch all Questionnaire objects which the User passed as argument may answer.
	 * @pre none
	 * @post if the User passed as argument doesn't exist, a empty ArrayList is returned.
	 * @param usr User
	 * @return ArrayList
	 */
	public static ArrayList fetchQuestionnaires(User usr) {

		String traverseGroupID;
		int questionnaireID;
		ArrayList returnList = new ArrayList();

		ArrayList usergroups = usr.getRefParents();

		for (int i = 0; i < usergroups.size(); i++) {
			//parse the UserGroup ID number from User usr's refParents
			traverseGroupID = (String) usergroups.get(i);
			try {
				Connection con = getConnection();
				Statement stmt = getStatement(con);
				ResultSet rs =
					stmt.executeQuery(
						"SELECT * FROM Questionnaire WHERE UserGroups LIKE '%"
							+ traverseGroupID
							+ "%'");
				while (rs.next()) {
					questionnaireID = rs.getInt("QuestionnaireID");
					returnList.add(Integer.toString(questionnaireID));
				}
				stmt.close();
				con.close();
			} catch (SQLException e) {
				printSQLExceptions(e);
			}
		} // end for

		return returnList;
	}

	/**
	 * Method to test for existance of a User object representation in database. 
	 * @pre none
	 * @post returns true if the User exist in database, otherwise false.
	 * @param key String
	 * @return boolean
	 */
	public static boolean existsUser(String key) {
		return (countTuples("User", "Username", key) != 0);
	} //end existsUser

	/**
	 * Method to test for existance of a UserGroup object representation in database. 
	 * @pre none
	 * @post returns true if the UserGroup exist in database, otherwise false.
	 * @param key String
	 * @return boolean
	 */
	public static boolean existsUserGroup(String key) {
		return (countTuples("Usergroup", "Groupname", key) != 0);
	} //end existsUserGroup

	/**
	 * Private helper method that returns the first Evaluation object from 
	 * the ResultSet passed as argument.
	 * @pre none
	 * @post if the ResultSet is empty, an Evaluation object with IDs set to 0 is returned.
	 * @param rs ResultSet
	 * @return Evaluation
	 */
	private static Evaluation convertSelectToEvaluation(ResultSet rs) {
		int evaluationID = 0, questionnaireID = 0;
		boolean finished = false;
		long timestamp = 0;
		String[] result = new String[6];
		try {
			evaluationID = rs.getInt("EvaluationID");
			result[0] = rs.getString("UserName");
			timestamp = rs.getLong("Timestamp");
			questionnaireID = rs.getInt("QuestionnaireID");
			result[1] = rs.getString("Answers");
		} catch (SQLException e) {
			printSQLExceptions(e);
		}
		return new Evaluation(
			evaluationID,
			result[0],
			questionnaireID,
			timestamp,
			convertStringToArrayList(result[1]));
	} // end convertSelectToEvaluation

	/**
	 * Private helper method that returns the first User object from the ResultSet
	 * passed as argument.
	 * @pre none
	 * @post if the ResultSet is empty, a User object with all text fields set 
	 * to empty strings is returned.
	 * @param rs ResultSet
	 * @return User
	 */
	private static User convertSelectToUser(ResultSet rs) {

		String[] result = new String[9];
		try {
			result[0] = rs.getString("Username");
			result[1] = rs.getString("Password");
			result[2] = rs.getString("Usertype");
			result[3] = rs.getString("Email");
			result[4] = rs.getString("Fullname");
			result[5] = rs.getString("City");
			result[6] = rs.getString("Country");
			result[7] = rs.getString("Adminusergroup");
			result[8] = rs.getString("Parents");

		} catch (SQLException e) {
			printSQLExceptions(e);
		}

		return new User(
			result[0],
			result[1],
			result[2],
			result[3],
			result[4],
			result[5],
			result[6],
			convertStringToArrayList(result[7]),
			convertStringToArrayList(result[8]));
	} // end convertSelectToUser

	/**
	 * Private helper method that returns the first UserGroup object from the ResultSet
	 * passed as argument.
	 * @pre none
	 * @post returns first UserGroup object from ResultSet
	 * @param rs ResultSet
	 * @return UserGroup
	 */
	private static UserGroup convertSelectToUserGroup(ResultSet rs) {
		String[] result = new String[3];
		int gID = -1;

		try {
			gID = rs.getInt("UsergroupID");
			result[0] = rs.getString("Groupname");
			result[1] = rs.getString("Children");
			result[2] = rs.getString("Parent");
		} catch (SQLException e) {
			printSQLExceptions(e);
		}

		return new UserGroup(
			gID,
			result[0],
			convertStringToArrayList(result[1]),
			convertStringToArrayList(result[2]));
	} // end convertSelectToUserGroup

	/**
	 * Private helper methods that counts the number of rows (tuples)
	 * satisfying the condition given as arguments. 
	 * @pre none
	 * @post returns 0 if any of the arguments is badly formed (ex. wrong table name).
	 * @param table String
	 * @param field String
	 * @param key String
	 * @return int
	 */
	private static int countTuples(String table, String field, String key) {

		int result = 0;

		try {
			Connection con = getConnection();
			Statement stmt = getStatement(con);

			ResultSet rs =
				stmt.executeQuery(
					"SELECT COUNT( "
						+ field
						+ " ) AS rowcount FROM "
						+ table
						+ " WHERE "
						+ field
						+ "='"
						+ key
						+ "'");
			rs.next();
			result = rs.getInt("rowcount");

			stmt.close();
			con.close();
		} catch (SQLException e) {
			printSQLExceptions(e);
		}
		return result;
	} // end countTuples

	/** 
	 * Private helper method for sending SQL updates. Uses sendUpdateAndGetID, 
	 * and simply trashes the returned int ID primitive.
	 * @pre none
	 * @post the update string was sent to the database through JDBC
	 * @param update String
	 */
	private static void sendUpdate(String update) {
		sendUpdateAndGetID(update);
	} //end sendUpdate

	/** 
	 * Private helper method for sending SQL updates and getting the ID created 
	 * by the database.
	 * @pre none
	 * @post the update string was sent to the database through JDBC
	 * @param update String
	 * @return int
	 */
	private static int sendUpdateAndGetID(String update) {

		int id = -1;

		try {
			Connection con = getConnection();
			Statement stmt = getStatement(con);

			stmt.executeUpdate(update);
			ResultSet newids = stmt.getGeneratedKeys();

			while (newids.next())
				id = newids.getInt(1);
			stmt.close();
			con.close();
		} catch (SQLException e) {
			printSQLExceptions(e);
			System.out.println("SQL statement that caused trouble: " + update);
		}

		return id;
	} //end sendUpdateAndGetID

	/**
	 * Private helper method that opens a statement and selects the database,
	 * for the the Connection passed as argument.
	 * @pre none
	 * @post the e2203 database was selected through JDBC. 
	 * @param con Connection
	 * @return Statement
	 * @throws SQLException
	 */
	private static Statement getStatement(Connection con) throws SQLException {

		Statement stmt = con.createStatement();
		stmt.executeUpdate("USE e2203");

		return stmt;
	} // end getStatement

	/**
	 * Private helper method that creates a JDBC connection, and submits the DB_LOGIN
	 * string.
	 * @pre none
	 * @post returns a JDBC connection that is ready for parsing statements. 
	 * @return Connection
	 * @throws SQLException
	 */
	private static Connection getConnection() throws SQLException {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println(e);
		}
		return DriverManager.getConnection("jdbc:mysql:" + DB_LOGIN);
	}

	/**
	 * Private helper method that splits a given String in tokens, using the
	 * delimiter String passed as argument.
	 * @pre tokens is a delimited String, which is generated by the toString()
	 * method of ArrayList.
	 * @post returns an ArrayList consisting of String objects. If tokens was
	 * null, the ArrayList is empty.
	 * @param tokens String
	 * @param delimiter String
	 * @return ArrayList
	 */
	private static ArrayList convertStringToArrayList(
		String tokens,
		String delimiter) {

		ArrayList result = new ArrayList();

		if ((tokens != null)
			&& (!tokens.equals("null"))
			&& (tokens.length() >= 1)) {

			tokens = tokens.substring(1, tokens.length() - 1);
			StringTokenizer st = new StringTokenizer(tokens, delimiter);
			while (st.hasMoreTokens()) {
				result.add(st.nextToken());
			}
		}
		return result;
	} // end convertStringToArrayList

	/**
	 * Private helper method that splits a given String in tokens, using ", "
	 * as delimiter.
	 * @pre tokens is a delimited String, which is generated by the toString()
	 * method of ArrayList.
	 * @post returns an ArrayList consisting of String objects. If tokens was
	 * null, the ArrayList is empty.
	 * @param tokens String
	 * @return ArrayList
	 */
	private static ArrayList convertStringToArrayList(String tokens) {
		return convertStringToArrayList(tokens, ", ");
	}

	/**
	* Private helper method that determines whether or not the host is inside
	* the cs.auc.dk network, by resolving the canonical hostname.
	* @pre none
	* @post returns true if the canonical hostname ends with "cs.auc.dk"
	* @return boolean
	*/
	private static boolean onCSnetwork() {

		boolean result = false;
		try {
			InetAddress addr = InetAddress.getLocalHost();
			result = (addr.getCanonicalHostName()).endsWith("cs.auc.dk");
		} catch (UnknownHostException e) {
		}

		return result;
	} // end onCSnetwork

	/**
	 * Private helper method for printing SQL exceptions to System.out
	 * @pre none
	 * @param ex SQLException
	 */
	private static void printSQLExceptions(SQLException ex) {
		if (ex != null) {
			System.out.println("\n*** SQLException caught ***\n");

			while (ex != null) {
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("Message:  " + ex.getMessage());
				System.out.println("Vendor:   " + ex.getErrorCode());
				System.out.println("");
				ex = ex.getNextException();
			}
		}
	} //end printSQLExceptions

} //end class