/**
 * 
 */
package com.seavision.PermissionManage.components;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.seavision.PermissionManage.components.UserManage;
import com.seavision.PermissionManage.help.Constants;
import net.sf.hibernate.HibernateException;
import com.seavision.PermissionManage.help.HibernateUtil;
import com.seavision.PermissionManage.vo.UserAndRoleVO;
import com.seavision.PermissionManage.vo.UserVO;

/**
 * @author Administrator
 * 
 */
public class UserManageImpl implements UserManage {

	public List getUserList() {
		List result = new ArrayList();
		Connection conn = HibernateUtil.getConnection();
		String sql = "select * from users";
		Statement stm = null;
		ResultSet rs = null;
		try {
			stm = conn.createStatement();
			rs = stm.executeQuery(sql);

			while (rs.next()) {
				UserVO user = new UserVO();
				user.setUserId(rs.getInt("userId"));
				user.setCompanyId(rs.getInt("companyId"));
				user.setDepartmentId(rs.getInt("departmentId"));
				user.setDuty(rs.getString("duty"));
				user.setEmail(rs.getString("email"));
				user.setMk(rs.getInt("mk"));
				user.setSex(rs.getString("sex"));
				user.setUserName(rs.getString("userName"));
				user.setUserPassWord(rs.getString("userPassWord"));
				user.setUserState(rs.getInt("userState"));
				result.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stm.close();
				rs.close();

				// conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (null == result || result.size() == 0) {
			return result;
		} else {
			return result;
		}
		/**
		 * List result=new ArrayList(); String hql=" from UserVO " ;
		 * 
		 * try {
		 *  // 調用HibernateUtil方法進行查詢 result = HibernateUtil.queryHQL(hql);
		 *  } catch (HibernateException e) {
		 * 
		 * e.printStackTrace(); } if (null == result || result.size() == 0) {
		 * return null; } else { return result; }
		 */
	}

	/**
	 * 1.獲取用戶列表
	 * 
	 * @param companyName
	 * @param departmentName
	 * @return根據參數從表user中讀取信息， 封裝到UserVO對象中。成功返回List,失敗返回null。
	 */
	public List getUserList(String companyId, String departmentId) {

		List result = new ArrayList();
		String hql = null;

		if ((companyId == null || companyId.equals(""))
				&& (departmentId == null || departmentId.equals(""))) {
			hql = "from UserVO";
			System.out.println("hql= " + hql);
			// return null;
		} else if ((!(companyId == null || companyId.equals("")))
				&& (departmentId == null || departmentId.equals(""))) {
			hql = "from UserVO as u where " + " u.companyId=" + companyId;
			System.out.println("hql=" + hql);
		} else if (((companyId == null || companyId.equals("")))
				&& (!(departmentId == null || departmentId.equals("")))) {
			hql = "from UserVO as u where " + " u.departmentId = "
					+ departmentId;
			System.out.println("hql=" + hql);
		} else {
			hql = "from UserVO as u where " + "u.companyId=" + companyId
					+ " and u.departmentId= " + departmentId;
			System.out.println("hql=" + hql);
		}
		// String hql="from DepartmentVO";

		try {

			// 調用HibernateUtil方法進行查詢
			result = HibernateUtil.queryHQL(hql);
			if (null == result || result.size() == 0) {
				return result;
			} else {
				return result;
			}

		} catch (HibernateException e) {

			e.printStackTrace();
		}

		return null;

	}

	public UserVO getUser(String userId) {
		UserVO userVO = (UserVO) HibernateUtil.getVOByID(UserVO.class, Integer
				.parseInt(userId));
		return userVO;
	}

	/**
	 * 3.獲取用戶角色
	 * 
	 * @param userId
	 * @return根據參數從表userAndRole中讀取信息， 獲取角色編號（roleId），封裝到List，根據List從表role中讀取數據,
	 *                                封裝到RoleVO中。成功返回List，失敗返回null。
	 */
	public List getUserOfRole(String userId) {
		List result = new ArrayList();
		// UserVO userVO = (UserVO)HibernateUtil.getVOByID( UserVO.class,
		// Long.parseLong(userId));
		String sql = "select * from userAndRole where userId='" + userId + "'";
		/*
		 * String sql="select roleId,roleName,roleNumber from userAndRole,role " +
		 * "where userNadRole.roleId=role.roleId and " + "userId=
		 * '"+userVO.getUserId() + "'";
		 */
		Connection conn = HibernateUtil.getConnection();
		try {
			Statement stm = conn.createStatement();
			ResultSet rs = stm.executeQuery(sql);
			while (rs.next()) {
				UserAndRoleVO userAndRoleVO = new UserAndRoleVO();
				userAndRoleVO.setRoleId(rs.getInt("roleId"));
				userAndRoleVO.setUserId(rs.getInt("userId"));
				result.add(userAndRoleVO);
			}
			rs.close();
			stm.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 4.根據部門編號獲取部門員工列表
	 * 
	 * @param departmentId
	 * @return根據參數從表user中讀取信息， 封裝到UserVO中，成功返回List，失敗返回null。
	 */

	public List getUserListByDepartmentId(String departmentId) {
		List result = new ArrayList();

		String sql = "select * from users where departmentId='" + departmentId
				+ "'";
		Connection conn = HibernateUtil.getConnection();
		try {
			Statement stm = conn.createStatement();
			ResultSet rs = stm.executeQuery(sql);
			while (rs.next()) {
				UserVO uservo = new UserVO();
				uservo.setCompanyId(rs.getInt("companyId"));
				uservo.setDepartmentId(rs.getInt("departmentId"));
				uservo.setDuty(rs.getString("duty"));
				uservo.setEmail(rs.getString("email"));
				uservo.setSex(rs.getString("sex"));
				uservo.setUserId(rs.getInt("userId"));
				uservo.setUserName(rs.getString("userName"));
				uservo.setUserPassWord(rs.getString("userPassWord"));
				uservo.setMk(rs.getInt("Mk"));
				uservo.setUserState(rs.getInt("userState"));
				result.add(uservo);
			}
			rs.close();
			stm.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//
		}
		return result;
	}

	public String saveUser(UserVO userVO, List list) {
		int a[] = new int[100];
		boolean commit = true;
		try {
			HibernateUtil.beginTransaction();
		} catch (HibernateException e) {
			e.printStackTrace();
		}

		for (int i = 0; i < list.size(); i++) {
			String str = String.valueOf(list.get(i));
			a[i] = Integer.parseInt(str);
			if (str.trim().equals("1")) {
				userVO.setMk(1);
			}
			if (!str.trim().equals("1") && str.trim().equals("2")) {
				userVO.setMk(2);
			}
		}
		HibernateUtil.save(userVO); // 將userVO插入user表中
		int userId = userVO.getUserId();
		Connection conn = HibernateUtil.getConnection();
		try {
			Statement stm = conn.createStatement();

			if (!list.isEmpty()) { // 循環添加角色信息，在userAndRole表中

				for (int j = 0; j < list.size(); j++) {
					String sql = "insert into userAndRole values(" + userId
							+ "," + a[j] + ")";
					System.out.println("sql = " + sql);
					stm.execute(sql);
				}
			}
			stm.close();
		} catch (Exception e) {
			commit = false;
			e.printStackTrace();
		}
		try {
			HibernateUtil.endTransaction(commit);
		} catch (HibernateException e) {
			e.printStackTrace();
		} finally {

		}

		if (commit) {
			return Constants.SUCCESS;
		} else {
			return Constants.FAILURE;
		}

	}

	/**
	 * 6.修改用戶
	 * 
	 * @param userVO
	 * @param list
	 *            將userVO更新到表user中， list更新到表userAndrole中 成功返回SUCCESS，失敗返回FALSE。
	 */
	public String updateUser(UserVO userVO, List list) {
		String[] a = new String[100];
		boolean commit = true;
		boolean mkOne = false;
		boolean mkTwo = false;
		try {
			HibernateUtil.beginTransaction();
		} catch (HibernateException e) {
			e.printStackTrace();
		}