package engine.sql;

import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.ArrayList;
import java.util.Map;
import java.util.logging.Logger;

import util.Util;


import engine.CryptTask;
import engine.containers.CurrentEmployeeDataContainer;
import engine.containers.EmployeeScheduleContainer;
import engine.containers.lists.EmployeeLists;
import engine.containers.lists.PositionsInfos;
import engine.containers.lists.StaticInfos;

/**
 *
 * @author Stephane Brousseau
 *
 */
public class DatabaseFactory
{
	private static DatabaseFactory dbf;



	private String mydatabase = "payrolldb";
	private String url = "jdbc:mysql://localhost:3306/" + mydatabase; // a JDBC url
	private String username = "root";
	private String password = "";

	/**
	 * Logger for this class
	 */
	private static final Logger			log				= Logger.getLogger(DatabaseFactory.class.getName());

	public static DatabaseFactory getInstance()
	{
		if(dbf == null)
		{
			log.info("Initiating Database Factory");
			dbf = new DatabaseFactory();
			dbf.testConnection();
		}
		return dbf;
	}

	private void testConnection()
	{
		Connection con = getConnection();
		if(con != null)
			log.info("Connection to the database established with database: "+con.toString());
		else
			log.info("Connection to the database could not be established");
	}

	public Connection getConnection()
	{
		Connection connection = null;
		try { // Load the JDBC driver
			String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver
			Class.forName(driverName); // Create a connection to the database
			connection = DriverManager.getConnection(url, username, password);
		}
		catch (ClassNotFoundException e)
		{
			log.severe("Could not find the database driver.");
		}
			catch (SQLException e)
			{ log.severe("Could not connect to the database.");
		}
		return connection;
	}

	public List<String> getFreePositionsFor(String name)
	{
		List<String> currentPositions = EmployeeLists.getAllPositionsFor(name);
		if(currentPositions == null)
		{
			currentPositions = new ArrayList<String>();
		}
		boolean addAll = false;
		List<String> freePositions = new ArrayList<String>();
		if(PositionsInfos.getInstance().getAllPositions() == null || PositionsInfos.getInstance().getAllPositions().isEmpty())
			addAll = true;
		for(String pos: PositionsInfos.getInstance().getAllPositions())
		{
			if(addAll || !currentPositions.contains(pos))
				freePositions.add(pos);
		}
		if(freePositions.isEmpty())
			return null;
		return freePositions;
	}

	public List<String> getPositionsFor(String name)
	{
		CryptTask cTask = CryptTask.getInstance();
		List<String> currentPositions = new ArrayList<String>();
		Connection con = getConnection();
		try
		{
			ResultSet result;
			PreparedStatement statement;
			statement = con.prepareStatement("SELECT * FROM work_positions WHERE name=?");
			statement.setString(1, cTask.scramble(name));
			result = statement.executeQuery();
			while(result.next())
			{
				String position = result.getString("position");
				currentPositions.add(position);
			}
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error retrieving Staff positions for:"+name);
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return currentPositions;

	}

	public List<String> getSalaryEmployees()
	{
		List<String> salaryEmployees = new ArrayList<String>();
		CryptTask cTask = CryptTask.getInstance();
		Connection con = getConnection();
		try
		{
			ResultSet result;
			PreparedStatement statement;
			statement = con.prepareStatement("SELECT * FROM work_positions");
			result = statement.executeQuery();
			while(result.next())
			{
				String name = cTask.deScramble(result.getString("name"));
				String payType = cTask.decryptText(result.getString("pay_type"));
				if(payType.equalsIgnoreCase("w") || payType.equalsIgnoreCase("y"))
				{
					if(!salaryEmployees.contains(name))
						salaryEmployees.add(name);
				}
			}
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error retrieving salary employees from the database");
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return salaryEmployees;
	}

	public Map<String,Integer> getEmployeeNames()
	{
		Connection con = getConnection();
		Map<String,Integer> employees = new HashMap<String,Integer>();
		int lvl = 100;
		try
		{
			ResultSet result;
			PreparedStatement statement;
			statement = con.prepareStatement("SELECT * FROM employee_list");
			result = statement.executeQuery();
			while(result.next())
			{
				String name = result.getString("name");
				int id = result.getInt("id");
				String newName = CryptTask.getInstance().deScramble(name);
				String level = CryptTask.getInstance().decryptText(result.getString("access_level"));
				if(!newName.equalsIgnoreCase("Super Administrator") && !newName.equalsIgnoreCase("superadministrator") && (!newName.contains("super") && !newName.contains("administrator")) && id != 1)
				{
					lvl = getIntValueForAccessLevel(level);
					employees.put(newName, lvl);
				}
			}
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error retrieving Staff names and IDs from the database");
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return employees;
	}

	public List<String> getAllEmployeeNames()
	{
		Connection con = getConnection();
		List<String> employees = new ArrayList<String>();
		try
		{
			ResultSet result;
			PreparedStatement statement;
			statement = con.prepareStatement("SELECT * FROM employee_list");
			result = statement.executeQuery();
			while(result.next())
			{
				String name = result.getString("name");
				String newName = CryptTask.getInstance().deScramble(name);
				if(!newName.equalsIgnoreCase("Super Administrator") && !newName.equalsIgnoreCase("superadministrator") && (!newName.contains("super") && !newName.contains("administrator")))
				{
					employees.add(newName);
				}
			}
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error retrieving Staff names and IDs from the database");
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return employees;
	}

	public Map<String,Object[]> getAllStaffPositions()
	{
		Connection con = getConnection();
		Map<String,Object[]> positions = new HashMap<String,Object[]>();
		try
		{
			ResultSet result;
			PreparedStatement statement;
			statement = con.prepareStatement("SELECT * FROM positions");
			result = statement.executeQuery();
			while(result.next())
			{
				String name = result.getString("position");
				double tip_pool_percent = result.getDouble("tip_pool_percent");
				int order = result.getInt("order");
				int on_schedule = result.getInt("on_schedule");
				int on_reports = result.getInt("on_reports");
				Object[] newObj = {tip_pool_percent,order,on_schedule,on_reports};
				positions.put(name, newObj);
			}
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error retrieving Staff positions from the database");
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(positions.isEmpty())
			return null;
		return positions;
	}

	public double getTipPercentForPosition(String pos)
	{
		Connection con = getConnection();
		double tipPercent = 0;
		try
		{
			ResultSet result;
			PreparedStatement statement;
			statement = con.prepareStatement("SELECT * FROM positions WHERE position=?");
			statement.setString(1, pos);
			result = statement.executeQuery();
			while(result.next())
			{
				tipPercent = result.getDouble("tip_pool_percent");
			}
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error retrieving tip percent from the database");
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tipPercent;
	}

	public Map<Object[],Map<String,Integer>> getBaseInfosForDate(Double dblDate)
	{
		return getBaseInfosForDate(Util.doubleToDate(dblDate));
	}

	public Map<Object[],Map<String,Integer>> getBaseInfosForDate(String date)
	{
		double dblDate = Util.dateToDouble(date);
		Connection con = getConnection();
		CryptTask cTask = CryptTask.getInstance();
		Map<String,Integer> tip_share = new HashMap<String,Integer>();
		double totalTips = 0;
		double totalSales = 0;
		double cashSales = 0;
		try
		{
			ResultSet result;
			PreparedStatement statement;
			statement = con.prepareStatement("SELECT * FROM payroll WHERE date=?");
			statement.setDouble(1, dblDate);
			result = statement.executeQuery();
			while(result.next())
			{
				int num = 1;
				String position = cTask.deScramble(result.getString("position"));
				if(tip_share.containsKey(position))
				{
					num = tip_share.get(position) + 1;
				}
				tip_share.put(position, num);
			}
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error retrieving base infos from the database");
		}
		try
		{
			ResultSet result;
			PreparedStatement statement;
			statement = con.prepareStatement("SELECT * FROM payroll_base_infos WHERE date=?");
			statement.setDouble(1, dblDate);
			result = statement.executeQuery();
			while(result.next())
			{
				String newTips = "";
				String newSales = "";
				String newCashSales = "";
				newTips = cTask.decryptText(result.getString("tips")).replace(",", ".");
				newSales = cTask.decryptText(result.getString("sales")).replace(",", ".");
				newCashSales = cTask.decryptText(result.getString("cash_sales")).replace(",", ".");
				if(newTips.isEmpty() || newTips.equals(""))
					totalTips = 0;
				else
					totalTips = Double.valueOf(newTips);
				if(newSales.isEmpty() || newSales.equals(""))
					totalSales = 0;
				else
					totalSales = Double.valueOf(newSales);
				if(newCashSales.isEmpty() || newCashSales.equals(""))
					cashSales = 0;
				else
					cashSales = Double.valueOf(newCashSales);
			}
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error retrieving base infos from the payroll_base_infos table");
		}
		Object[] baseInfos = {totalTips,totalSales,cashSales};
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		Map<Object[],Map<String,Integer>> resultMap = new HashMap<Object[],Map<String,Integer>>();
		resultMap.put(baseInfos, tip_share);
		return resultMap;
	}

	public void deletePosition(String pos)
	{
		Connection con = getConnection();
		try
		{
			PreparedStatement statement;
			statement = con.prepareStatement("DELETE FROM positions WHERE position=?");
			statement.setString(1, pos);
			statement.execute();
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error deleting data from positions in the database for position: "+pos);
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void addPosition(String pos, double tipPercent, int indexOrder, int onReports, int onSchedule)
	{
		deletePosition(pos);
		Connection con = getConnection();
		try
		{
			PreparedStatement statement = null;
			statement = con.prepareStatement("INSERT INTO positions values (?,?,?,?,?)");
			statement.setString(1, pos);
			statement.setDouble(2, tipPercent);
			statement.setInt(3,indexOrder);
			statement.setInt(4,onReports);
			statement.setInt(5,onSchedule);
			statement.execute();
			statement.close();
		}
		catch (Exception e)
		{
			log.info("Error inserting positions data in the database for position: "+pos);
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void saveBaseInfosForDate(double dblDate, String[] infos)
	{
		saveBaseInfosForDate(Util.doubleToDate(dblDate), infos);
	}

	public void saveBaseInfosForDate(String date, String[] infos)
	{
		double dblDate = Util.dateToDouble(date);
		Connection con = getConnection();
		CryptTask cTask = CryptTask.getInstance();
		try
		{
			PreparedStatement statement;
			statement = con.prepareStatement("DELETE FROM payroll_base_infos WHERE date=?");
			statement.setDouble(1, dblDate);
			statement.execute();
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error deleting global_data from the database");
		}
		try
		{
			PreparedStatement statement = null;
			statement = con.prepareStatement("INSERT INTO payroll_base_infos (date,tips,sales,cash_sales) values (?,?,?,?)");
			statement.setDouble(1, dblDate);
			statement.setString(2, cTask.encryptText(infos[0]));
			statement.setString(3, cTask.encryptText(infos[1]));
			statement.setString(4, cTask.encryptText(infos[2]));
			statement.execute();
			statement.close();
		}
		catch (Exception e)
		{
			log.info("Error inserting payroll base infos data in the database for date: "+date);
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public int getIdForEmployee(String name)
	{
		int ID = -1;
		Connection con = getConnection();
		CryptTask cTask = CryptTask.getInstance();
		try
		{
			ResultSet result;
			PreparedStatement statement = null;
			statement = con.prepareStatement("SELECT * FROM employee_list WHERE name=?");
			statement.setString(1, cTask.scramble(name));
			result = statement.executeQuery();
			while(result.next())
			{
				ID = result.getInt("id");
			}
			statement.close();
		}
		catch (Exception e)
		{
			log.warning("Exception: Retrieve employee ID: " + e.getMessage());
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ID;
	}

	public void deleteAccessLevel(String name)
	{
		if(!getAllAccessLevels().contains(name))
			return;
		Connection con = getConnection();
		try
		{
			PreparedStatement statement;
			statement = con.prepareStatement("DELETE FROM access_levels WHERE name=?");
			statement.setString(1, name);
			statement.execute();
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error deleting access level from the database for name:"+name);
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<String> getAllAccessLevels()
	{
		Connection con = getConnection();
		List<String> accessLevels = new ArrayList<String>();
		try
		{
			ResultSet result;
			PreparedStatement statement;
			statement = con.prepareStatement("SELECT * FROM access_levels");
			result = statement.executeQuery();
			while(result.next())
			{
				String curName = result.getString("name");
				if(!accessLevels.contains(curName))
				{
					accessLevels.add(curName);
				}

			}
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error retrieving access levels from the database");
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return accessLevels;
	}

	public int getIntValueForAccessLevel(String name)
	{
		int value = 100;
		Connection con = getConnection();
		try
		{
			ResultSet result;
			PreparedStatement statement;
			statement = con.prepareStatement("SELECT * FROM access_levels WHERE name=?");
			statement.setString(1, name);
			result = statement.executeQuery();
			while(result.next())
			{
				value = result.getInt("level");
			}
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error retrieving access levels from the database");
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return value;
	}

	public int getAccessLevelValueFor(String name)
	{
		int level = -1;
		Connection con = getConnection();
		try
		{
			ResultSet result;
			PreparedStatement statement = null;
			statement = con.prepareStatement("SELECT * FROM access_levels WHERE name=?");
			statement.setString(1, name);
			result = statement.executeQuery();
			while(result.next())
			{
				level = result.getInt("level");
			}
			statement.close();
		}
		catch (Exception e)
		{
			log.warning("Exception: Retrieve access level value: " + e.getMessage());
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return level;
	}
	public String getAccessLevelFor(String name)
	{
		String level = "";
		CryptTask cTask = CryptTask.getInstance();
		Connection con = getConnection();
		try
		{
			ResultSet result;
			PreparedStatement statement = null;
			statement = con.prepareStatement("SELECT * FROM employee_list WHERE name=?");
			statement.setString(1, cTask.scramble(name));
			result = statement.executeQuery();
			while(result.next())
			{
				level = cTask.decryptText(result.getString("access_level"));
			}
			statement.close();
		}
		catch (Exception e)
		{
			log.warning("Exception: Retrieve access level: " + e.getMessage());
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return level;
	}

	public void saveNewAccessLevel(String name, int level)
	{
		Connection con = getConnection();
		deleteAccessLevel(name);
		try
		{

			PreparedStatement statement = null;
			statement = con.prepareStatement("INSERT INTO access_levels (name,level) values (?,?)");
			statement.setString(1, name);
			statement.setInt(2, level);
			statement.execute();
			statement.close();

		}
		catch (Exception e)
		{
			log.info("Error inserting new access level:"+name);
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		log.info("Added access level name:"+name+" - level:"+level);
	}

	public Map<Integer,String[]> getUserAndPass()
	{
		Connection con = getConnection();
		Map<Integer,String[]> logInfos = new HashMap<Integer,String[]>();
		CryptTask cTask = CryptTask.getInstance();
		try
		{
			ResultSet result;
			PreparedStatement statement;
			statement = con.prepareStatement("SELECT * FROM global_data");
			result = statement.executeQuery();
			while(result.next())
			{
				int id = result.getInt("id");
				String uName = (cTask.decryptText(result.getString("cname")));
				String pName = (cTask.decryptText(result.getString("log")));
				String[] logInfo = {uName,pName};
				logInfos.put(id, logInfo);
			}
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error retrieving log infos from the database");
		}
		if(logInfos.isEmpty())
			return logInfos;
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return logInfos;
	}

	public void deleteUserPass(int ID)
	{
		Connection con = getConnection();
		try
		{
			PreparedStatement statement;
			statement = con.prepareStatement("DELETE FROM global_data WHERE id>=?");
			statement.setInt(1, ID);
			statement.execute();
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error deleting global_data from the database for userID:"+ID);
		}
		log.info("Deleted the old username and password from database successfully.");
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void saveUserPass(int ID, String cname, String l)
	{
		Connection con = getConnection();
		deleteUserPass(ID);
		log.info("Deleted the old username and password from database successfully.");
		CryptTask cTask = CryptTask.getInstance();
		try
		{

			PreparedStatement statement = null;
			statement = con.prepareStatement("INSERT INTO global_data (id,cname,log) values (?,?,?)");
			statement.setInt(1, ID);
			statement.setString(2, cTask.encryptText(cname));
			statement.setString(3, cTask.encryptText(l));
			statement.execute();
			statement.close();

		}
		catch (Exception e)
		{
			log.info("Error inserting log infos in the database for userID:"+ID);
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<Double> getSavedDates()
	{
		List<Double> savedDates = new ArrayList<Double>();
		Connection con = getConnection();
		try
		{
			ResultSet result;
			PreparedStatement statement;
			statement = con.prepareStatement("SELECT date FROM payroll");
			result = statement.executeQuery();
			while(result.next())
			{
				double date = result.getDouble("date");
				if(!savedDates.contains(date))
					savedDates.add(date);
			}
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error with getAllInfosForDate from the database");
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return savedDates;
	}

	public Map<String,List<CurrentEmployeeDataContainer>> getAllInfosForDate(double dblDate)
	{
		return getAllInfosForDate(Util.doubleToDate(dblDate));
	}

	public Map<String,List<CurrentEmployeeDataContainer>> getAllInfosForDate(String date)
	{
		double dblDate = Util.dateToDouble(date);
		if(getSavedDates() != null && getSavedDates().contains(dblDate))
		{
			Connection con = getConnection();
			CryptTask cTask = CryptTask.getInstance();
			Map<String,List<CurrentEmployeeDataContainer>> container = new HashMap<String,List<CurrentEmployeeDataContainer>>();
			for(String pos: getAllStaffPositions().keySet())
			{
				String cryptPos = cTask.scramble(pos);
				List<CurrentEmployeeDataContainer> containerList = new ArrayList<CurrentEmployeeDataContainer>();
				try
				{
					ResultSet result;
					PreparedStatement statement;
					statement = con.prepareStatement("SELECT * FROM payroll WHERE date=? AND position=?");
					statement.setDouble(1, dblDate);
					statement.setString(2, cryptPos);
					result = statement.executeQuery();
					while(result.next())
					{
						CurrentEmployeeDataContainer newContainer = new CurrentEmployeeDataContainer();
						String name = result.getString("name");
						String startTime = cTask.decryptText(result.getString("start_time"));
						String endTime = cTask.decryptText(result.getString("end_time"));
						double tips = Double.parseDouble(cTask.decryptText(result.getString("tips")));
						double cashTips = Double.parseDouble(cTask.decryptText(result.getString("cash_tips")));
						String newName = CryptTask.getInstance().deScramble(name);
						newContainer.name = newName;
						newContainer.startTime = startTime;
						newContainer.endTime = endTime;
						newContainer.tips = tips;
						newContainer.cashTips = cashTips;
						newContainer.position = pos;
						containerList.add(newContainer);
					}
					container.put(pos, containerList);
					statement.close();
				}
				catch (SQLException e)
				{
					log.info("Error with getAllInfosForDate from the database");
				}
			}
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return container;
		}
		else
			return null;

	}

	public void updateInfosForDate(String employeeName, double dblDate, Object[] data) //{startTime,endTime,tips,position}
	{
		updateInfosForDate(employeeName, Util.doubleToDate(dblDate), data);
	}

	public void updateInfosForDate(String employeeName, String date, Object[] data) //{startTime,endTime,tips,position}
	{
		double dblDate = Util.dateToDouble(date);
		Connection con = getConnection();
		CryptTask cTask = CryptTask.getInstance();
		removeEmployeeFromPayrollForDate(employeeName, date);
		try
		{
			PreparedStatement statement = null;
			statement = con.prepareStatement("INSERT INTO payroll (date,name,position,start_time,end_time,tips,cash_tips) values (?,?,?,?,?,?,?)");
			statement.setDouble(1, dblDate);
			statement.setString(2, cTask.scramble(employeeName));
			statement.setString(3, cTask.scramble(String.valueOf(data[3])));
			statement.setString(4, cTask.encryptText(String.valueOf(data[0])));
			statement.setString(5, cTask.encryptText(String.valueOf(data[1])));
			statement.setString(6, cTask.encryptText(String.valueOf(data[2])));
			statement.setString(7, cTask.encryptText(String.valueOf(data[4])));
			statement.execute();
			statement.close();
			con.close();
		}
		catch (Exception e)
		{
			log.info("Error inserting payroll data  in the database for employee "+employeeName+" and date: "+date);
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void saveEmployee(String curName,String access_level,double hireDate)
	{
		Connection con = getConnection();
		CryptTask cTask = CryptTask.getInstance();
		try
		{
			PreparedStatement statement = null;
			statement = con.prepareStatement("INSERT INTO employee_list (name,access_level,hire_date) values (?,?,?)");
			//statement.setInt(1, 0);
			statement.setString(1, cTask.scramble(curName));
			statement.setString(2, cTask.encryptText(access_level));
			statement.setDouble(3, hireDate);
			statement.execute();
			statement.close();
		}
		catch (Exception e)
		{
			log.warning("Exception: Save new employee: " + e.getMessage());
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void updateEmployeeAccessLevel(String curName,String access_level)
	{
		Connection con = getConnection();
		CryptTask cTask = CryptTask.getInstance();
		try
		{
			PreparedStatement statement = null;
			statement = con.prepareStatement("UPDATE employee_list SET access_level=? WHERE name=?");
			statement.setString(1, cTask.encryptText(access_level));
			statement.setString(2, cTask.scramble(curName));
			statement.execute();
			statement.close();
		}
		catch (Exception e)
		{
			log.warning("Exception: Update access level: " + e.getMessage());
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void saveSuperAdmin()
	{
		Connection con = getConnection();
		CryptTask cTask = CryptTask.getInstance();
		try
		{
			PreparedStatement statement = null;
			statement = con.prepareStatement("INSERT INTO employee_list (id,name,access_level) values (?,?,?)");
			statement.setInt(1, 1);
			statement.setString(2, cTask.scramble("SuperAdministrator"));
			statement.setString(3, cTask.encryptText(String.valueOf(0)));
			statement.execute();
			statement.close();
		}
		catch (Exception e)
		{
			log.warning("Exception: Save super administrator: " + e.getMessage());
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public String getEmployeeNameFromId(int ID)
	{
		String name = "";
		Connection con = getConnection();
		CryptTask cTask = CryptTask.getInstance();
		try
		{
			ResultSet result;
			PreparedStatement statement = null;
			statement = con.prepareStatement("SELECT * FROM employee_list WHERE id=?");
			statement.setInt(1, ID);
			result = statement.executeQuery();
			while(result.next())
			{
				name = cTask.deScramble(result.getString("name"));
			}
			statement.close();
		}
		catch (Exception e)
		{
			log.warning("Exception: Save new employee: " + e.getMessage());
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return name;
	}

	public double getHireDateForEmployee(String name)
	{
		double date = 0;
		Connection con = getConnection();
		CryptTask cTask = CryptTask.getInstance();
		try
		{
			ResultSet result;
			PreparedStatement statement = null;
			statement = con.prepareStatement("SELECT * FROM employee_list WHERE name=?");
			statement.setString(1, cTask.scramble(name));
			result = statement.executeQuery();
			while(result.next())
			{
				date = result.getDouble("hire_date");
			}
			statement.close();
		}
		catch (Exception e)
		{
			log.warning("Exception: Save new employee: " + e.getMessage());
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return date;
	}

	public String getEmployeeNameFromUserName(String username)
	{
		String name = "";
		int ID = 0;
		Connection con = getConnection();
		CryptTask cTask = CryptTask.getInstance();
		try
		{
			ResultSet result;
			PreparedStatement statement = null;
			statement = con.prepareStatement("SELECT * FROM global_data");
			result = statement.executeQuery();
			while(result.next())
			{
				String uname = cTask.decryptText(result.getString("cname"));
				if(uname.equals(username))
					ID = result.getInt("id");
			}
			statement.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		try
		{
			ResultSet result;
			PreparedStatement statement = null;
			statement = con.prepareStatement("SELECT * FROM employee_list WHERE id=?");
			statement.setInt(1, ID);
			result = statement.executeQuery();
			while(result.next())
			{
				name = cTask.deScramble(result.getString("name"));
			}
			statement.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return name;
	}

	public void removeEmployeeByName(String name)
	{
		Connection con = getConnection();
		CryptTask cTask = CryptTask.getInstance();
		try
		{
			PreparedStatement statement;
			statement = con.prepareStatement("DELETE FROM employee_list WHERE name=?");
			statement.setString(1, cTask.scramble(name));
			statement.execute();
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error deleting employee "+name+" from the database");
		}
		try
		{
			PreparedStatement statement;
			statement = con.prepareStatement("DELETE FROM work_positions WHERE name=?");
			statement.setString(1, cTask.scramble(name));
			statement.execute();
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error deleting staff positions for employee "+name+" from the database");
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void deleteEmployeePosition(String curName, String position)
	{
		Connection con = getConnection();
		CryptTask cTask = CryptTask.getInstance();
		try
		{
			PreparedStatement statement;
			statement = con.prepareStatement("DELETE FROM work_positions WHERE name=? AND position=?");
			statement.setString(1, cTask.scramble(curName));
			statement.setString(2, position);
			statement.execute();
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error deleting staff position for employee "+curName+" from the database");
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void addEmployeePosition(String curName, String position, double salary, String pay_type)
	{
		Connection con = getConnection();
		CryptTask cTask = CryptTask.getInstance();
		deleteEmployeePosition(curName,position);
		try
		{
			PreparedStatement statement = null;
			statement = con.prepareStatement("INSERT INTO work_positions (name,position,salary,pay_type) values (?,?,?,?)");
			statement.setString(1, cTask.scramble(curName));
			statement.setString(2, position);
			statement.setString(3, cTask.encryptText(String.valueOf(salary)));
			statement.setString(4, cTask.encryptText(pay_type));
			statement.execute();

			statement.close();
		}
		catch (Exception e)
		{
			log.warning("Exception: Save staff positions: " + e.getMessage());
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public String[] getSalaryForEmployee(String name, String position)
	{
		String salary = "";
		String payType = "";
		Connection con = getConnection();
		CryptTask cTask = CryptTask.getInstance();
		try
		{
			ResultSet result;
			PreparedStatement statement = null;
			statement = con.prepareStatement("SELECT * FROM work_positions WHERE name=? AND position=?");
			statement.setString(1, cTask.scramble(name));
			statement.setString(2, position);
			result = statement.executeQuery();
			while(result.next())
			{
				salary = cTask.decryptText(result.getString("salary"));
				payType = cTask.decryptText(result.getString("pay_type"));
			}
			statement.close();
		}
		catch (Exception e)
		{
			log.warning("Exception: Retrieve salary: " + e.getMessage());
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		String[] sal = {salary,payType};
		return sal;
	}

	public void removeEmployeeFromPayrollForDate(String name, double dblDate)
	{
		removeEmployeeFromPayrollForDate(name, Util.doubleToDate(dblDate));
	}

	public void removeEmployeeFromPayrollForDate(String name, String date)
	{
		double dblDate = Util.dateToDouble(date);
		Connection con = getConnection();
		CryptTask cTask = CryptTask.getInstance();
		try
		{
			PreparedStatement statement;
			statement = con.prepareStatement("DELETE FROM payroll WHERE name=? AND date=?");
			statement.setString(1, cTask.scramble(name));
			statement.setDouble(2, dblDate);
			statement.execute();
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error deleting payroll data for employee "+name+" and date: "+date+" from the database");
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public double getScheduleStartDate(List<Double> datesList)
	{
		Connection con = getConnection();
		CryptTask cTask = CryptTask.getInstance();
		double correctDate = 0;
		for(double curDate:datesList)
		{
			try
			{
				ResultSet result;
				PreparedStatement statement = null;
				statement = con.prepareStatement("SELECT * FROM schedule WHERE date=?");
				statement.setDouble(1, curDate);
				result = statement.executeQuery();
				while(result.next())
				{
					correctDate = result.getDouble("date");
				}
				statement.close();
			}
			catch (Exception e)
			{
				log.warning("Exception: Retrieve schedule start date: " + e.getMessage());
				e.printStackTrace();
			}
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(correctDate != 0)
			return correctDate;
		else
			return 0;
	}

	public List<EmployeeScheduleContainer> getScheduleFor(double startDate)
	{
		List<EmployeeScheduleContainer> newSchedule = new ArrayList<EmployeeScheduleContainer>();
		Connection con = getConnection();
		try
		{
			ResultSet result;
			PreparedStatement statement = null;
			statement = con.prepareStatement("SELECT * FROM schedule WHERE date=?");
			statement.setDouble(1, startDate);
			result = statement.executeQuery();
			while(result.next())
			{
				EmployeeScheduleContainer container = new EmployeeScheduleContainer();
				container.setName(result.getString("name"));
				String str1 = result.getString("sunday");
				String str2 = result.getString("monday");
				String str3 = result.getString("tuesday");
				String str4 = result.getString("wednesday");
				String str5 = result.getString("thursday");
				String str6 = result.getString("friday");
				String str7 = result.getString("saturday");
				String[] sched = {str1,str2,str3,str4,str5,str6,str7};
				container.setSchedule(sched);
				container.setStartDay(startDate);
				newSchedule.add(container);
			}
			statement.close();
		}
		catch (Exception e)
		{
			log.warning("Exception: Retrieve schedule: " + e.getMessage());
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return newSchedule;
	}

	public void saveScheduleForDate(double startDate,List<EmployeeScheduleContainer> scheduleList)
	{
		deleteScheduleForDate(startDate);
		Connection con = getConnection();
		for(EmployeeScheduleContainer container:scheduleList)
		{
			try
			{
				PreparedStatement statement = null;
				statement = con.prepareStatement("INSERT INTO schedule (date,name,monday,tuesday,wednesday,thursday,friday,saturday) values (?,?,?,?,?,?,?,?)");
				statement.setDouble(1, startDate);
				statement.setString(2, container.getEmployeeName());
				statement.setString(3, container.getScheduleForDayOfWeek(0));
				statement.setString(4, container.getScheduleForDayOfWeek(1));
				statement.setString(5, container.getScheduleForDayOfWeek(2));
				statement.setString(6, container.getScheduleForDayOfWeek(3));
				statement.setString(7, container.getScheduleForDayOfWeek(4));
				statement.setString(8, container.getScheduleForDayOfWeek(5));
				statement.setString(9, container.getScheduleForDayOfWeek(6));
				statement.execute();

				statement.close();
			}
			catch (Exception e)
			{
				log.warning("Exception: Save schedule: " + e.getMessage());
				e.printStackTrace();
			}		}

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

	}

	public void deleteScheduleForDate(double startDate)
	{
		Connection con = getConnection();
		try
		{
			PreparedStatement statement;
			statement = con.prepareStatement("DELETE FROM schedule WHERE date=?");
			statement.setDouble(1, startDate);
			statement.execute();
			statement.close();
		}
		catch (SQLException e)
		{
			log.info("Error deleting schedule for start date "+startDate+" from the database");
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}