package com.lsit.RBAC.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.lsit.RBAC.domain.Department;
import com.lsit.RBAC.listener.InitConfigListener;
import com.lsit.RBAC.util.DBConnection;

public class DepartmentDAO {

	public boolean insertDeptData(Department dept) {

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			int count = 0;
			connection = DBConnection.getConnection();
			preparedStatement = connection.prepareStatement(InitConfigListener.dbHashMap.get("insertDepartment"));
			
			preparedStatement.setString(++count, dept.getDeptName());
			preparedStatement.setInt(++count, getUserId(dept.getDeptManager()));
			preparedStatement.setString(++count, dept.getDeptAddr());
			preparedStatement.setString(++count, dept.getTelephone());
			preparedStatement.setString(++count, dept.getFax());
			int index = preparedStatement.executeUpdate();
			if (index > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(connection, preparedStatement, null);
		}
		return false;
	}

	public boolean updateDeptData(Department dept) {

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			int count = 0;
			connection = DBConnection.getConnection();
			preparedStatement = connection.prepareStatement(InitConfigListener.dbHashMap.get("updateDepartment"));
			preparedStatement.setString(++count, dept.getDeptName());
			preparedStatement.setInt(++count, getUserId(dept.getDeptManager()));
			preparedStatement.setString(++count, dept.getDeptAddr());
			preparedStatement.setString(++count, dept.getTelephone());
			preparedStatement.setString(++count, dept.getFax());
			preparedStatement.setInt(++count, dept.getDeptId());
			int index = preparedStatement.executeUpdate();
			if (index > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(connection, preparedStatement, null);
		}
		return false;
	}

	public boolean deleteDeptData(int deptId) {

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = DBConnection.getConnection();
			preparedStatement = connection.prepareStatement(InitConfigListener.dbHashMap.get("deleteDepartment"));
			preparedStatement.setInt(1, deptId);
			int index = preparedStatement.executeUpdate();
			if (index > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(connection, preparedStatement, null);
		}
		return false;
	}

	public Department getDeparment(int deptId) {

		Department dept = new Department();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet=null;
		try {
			connection = DBConnection.getConnection();
			preparedStatement = connection.prepareStatement
					(InitConfigListener.dbHashMap.get("getDepartment"));
			preparedStatement.setInt(1, deptId);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				dept.setDeptId(resultSet.getInt("dept_id"));
				dept.setDeptName(resultSet.getString("dept_name"));
				dept.setDeptManager(resultSet.getString("dept_manager"));
				dept.setDeptAddr(resultSet.getString("dept_addr"));
				dept.setTelephone(resultSet.getString("tel"));
				dept.setFax(resultSet.getString("fax"));
				dept.setAddTime(resultSet.getString("add_time"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.close(connection, preparedStatement, resultSet);
		}
		return dept;
	}

	public boolean setManager(int deptId, int userId) {
		
		Connection connection = null;
		PreparedStatement preparedStatement=null;
		try {
			connection = DBConnection.getConnection();
			 preparedStatement = connection.prepareStatement
					 (InitConfigListener.dbHashMap.get("setManager"));
			preparedStatement.setInt(1, userId);
			preparedStatement.setInt(2, deptId);
			int index = preparedStatement.executeUpdate();
			if (index > 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.close(connection, preparedStatement, null);
		}
		return false;
	}

	public List<HashMap<String, String>> selectDeptNameData() {
		
		List<HashMap<String, String>> deptNameList = new ArrayList<HashMap<String, String>>();
		Connection connection=null;
		PreparedStatement preparedStatement =null;
		ResultSet resultSet=null;
		try {
			connection = DBConnection.getConnection();
			 preparedStatement = connection.prepareStatement
					 (InitConfigListener.dbHashMap.get("selectDeptNames"));
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				HashMap<String, String> hashMap = new HashMap<String, String>();
				hashMap.put("deptName", resultSet.getString("dept_name"));
				deptNameList.add(hashMap);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.close(connection, preparedStatement, resultSet);
		}
		return deptNameList;

	}

	public int getUserId(String deptManager) {
		
		Connection connection=null;
		PreparedStatement  preparedStatement=null;
	    ResultSet resultSet=null;
		try {
		 connection = DBConnection.getConnection();
		 preparedStatement = connection.prepareStatement
				 (InitConfigListener.dbHashMap.get("getUserId"));
			preparedStatement.setString(1, deptManager);
		  resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				return resultSet.getInt("user_id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.close(connection, preparedStatement, resultSet);
		}
		return -1;
	}

	public List<Department> selectDeptData(String telephone, String deptName) {
		
		List<Department> deptList = new ArrayList<Department>();
		String sql = "select t2.* ,(select CONCAT(first_name,last_name) from tt_dept_user as t1 "
				+ "where t1.user_id=t2.dept_mgr_id)" + " as dept_manager from tt_dept as t2 where 1=1";
		if (telephone != null && !"".equals(telephone)) {
			sql += " and tel like '%" + telephone + "%'";
		}
		if (deptName != null && !"".equals(deptName)) {
			sql += " and dept_name like '%" + deptName + "%'";
		}
		Connection connection=null;
		PreparedStatement preparedStatement=null;
	   ResultSet  resultSet=null;
		try {
			 connection = DBConnection.getConnection();
			 preparedStatement = connection.prepareStatement(sql);
			 resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				Department dept = new Department();
				dept.setDeptId(resultSet.getInt("dept_id"));
				dept.setDeptName(resultSet.getString("dept_name"));
				dept.setDeptAddr(resultSet.getString("dept_addr"));
				dept.setDeptManager(resultSet.getString("dept_manager"));
				dept.setTelephone(resultSet.getString("tel"));
				dept.setFax(resultSet.getString("fax"));
				dept.setAddTime(resultSet.getString("add_time"));
				deptList.add(dept);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.close(connection, preparedStatement, resultSet);
		}
		return deptList;
	}

	public boolean isDeptNameExist(String deptName) {
		
		Connection connection = null;
		PreparedStatement preparedStatement =null;
		ResultSet resultSet=null;
		try {
			connection = DBConnection.getConnection();
			 preparedStatement = connection.prepareStatement
					 (InitConfigListener.dbHashMap.get("selectDeptNames"));
			 resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				if (resultSet.getString("dept_Name").equals(deptName)) {
					return true;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.close(connection, preparedStatement, resultSet);
		}
		return false;
	}

	public boolean isUserExist(int deptId) {
		
		Connection connection = null;
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		try {
			connection = DBConnection.getConnection();
			 preparedStatement = connection
					.prepareStatement(InitConfigListener.dbHashMap.get("isUserExistsUnderDept"));
			preparedStatement.setInt(1, deptId);
		    resultSet = preparedStatement.executeQuery();
			return  resultSet.next(); 
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.close(connection, preparedStatement, resultSet);
		}
		return false;
	}

	public boolean isMenuExist(int deptId) {
	
		Connection connection = null;
		PreparedStatement preparedStatement=null;
	    ResultSet resultSet=null;
		try {
			connection = DBConnection.getConnection();
			 preparedStatement = connection
					.prepareStatement(InitConfigListener.dbHashMap.get("isMenuExistsUnderDept"));
			preparedStatement.setInt(1, deptId);
			resultSet = preparedStatement.executeQuery();
			return resultSet.next();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBConnection.close(connection, preparedStatement, resultSet);
		}
		return false;
	}
}