/**
 * 
 */
package com.cmsz.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.cmsz.vo.ResultVO;
import com.cmsz.vo.UserInfoVO;
import com.cmsz.ws.impl.UserServiceImpl;
import com.hp.cmsz.commons.utils.Constant;
import com.hp.cmsz.commons.utils.DES;
import com.hp.cmsz.entity.RoleInfo;
import com.hp.cmsz.entity.UserRoleInfo;

/**
 * @author huacha
 * 
 */
public class UserInfoDaoSupport extends DAOSupport {
	private static Logger logger = LoggerFactory
			.getLogger(UserServiceImpl.class);

	/**
	 * Create a new userInfo
	 * 
	 * @param vo
	 */
	public void createUser(UserInfoVO vo) throws SQLException {
		String sql = "select jcpz_user_info_t_seq.nextval";
		ResultSet rs = this.getResultSet(sql);
		Long userId = null;
		while (rs.next())
			userId = rs.getLong(1);
		sql = "INSERT INTO JCPZ_USER_INFO_T (USER_ID, USER_NAME, CREATE_TIME, MODIFY_TIME, USER_DESCRIPTION, DEPARTMENT_ID, PASSWORD, STAFFNAME, FLAG, VALID_LENGTH, EXPIRE_DATE, LOG_LOCK) VALUES (";
		// 0, '', '', '', '', 0, '', '', '', '', '', '');
		sql += String.valueOf(userId) + ",";
		sql += "'" + vo.getLoginUser() + "',";
		sql += "sysdate,";
		sql += "sysdate,";
		if (vo.getNote() == null || vo.getNote().isEmpty()) {
			sql += "'" + Constant.EMPTY_VALUE_FOR_4A + "',";
		} else {
			sql += "'" + vo.getNote() + "',";
		}
		if (vo.getDepartmentCode() == null || vo.getDepartmentCode().isEmpty()) {
			sql += Constant.EMPTY_VALUE_FOR_4A + ",";
		} else {
			sql += vo.getDepartmentCode() + ",";
		}
		// sql += "'" + vo.getDepartmentCode() + ",";
		/*
		 * String passDes = "0";
		 * 
		 * DES des; try { des = new DES(); passDes =
		 * des.encrypt(vo.getPassword()); } catch (Exception e) { // TODO
		 * Auto-generated catch block e.printStackTrace(); }
		 */
		sql += "'" + vo.getPassword() + "',";
		if (vo.getStaffName() == null || vo.getStaffName().isEmpty()) {
			sql += "'" + Constant.EMPTY_VALUE_FOR_4A + "',";
		} else {
			sql += "'" + vo.getStaffName() + "',";
		}
		if (vo.getFlag() == null || vo.getFlag().isEmpty()) {
			sql += "'1',";
		} else {
			sql += "'" + vo.getFlag() + "',";
		}
		if (vo.getValidLength() == null || vo.getValidLength().isEmpty()) {
			sql += "'" + Constant.EMPTY_VALUE_FOR_4A + "',";
		} else {
			sql += "'" + vo.getValidLength() + "',";
		}
		SimpleDateFormat sdf = new SimpleDateFormat(
				Constant.DATE_TIME_FORMAT_FOR_JAVA);
		if (vo.getExpireDate() == null) {
			sql += "to_timestamp('" + sdf.format(new java.util.Date()) + "','"
					+ Constant.DATE_TIME_FORMAT_FOR_DB + "'),";
		} else {
			sql += "to_timestamp('" + sdf.format(vo.getExpireDate()) + "','"
					+ Constant.DATE_TIME_FORMAT_FOR_DB + "'),";
		}
		if (vo.getLogLock() == null || vo.getLogLock().isEmpty()) {
			sql += "'1')";
		} else {
			sql += "'" + vo.getLogLock() + "')";
		}
		logger.info("--Create user with the SQL is:" + sql);
		this.executeSQL(sql);
		sql = "INSERT INTO JCPZ_USER_ROLE_INFO_T (USER_ROLE_ID, USER_ID, ROLE_ID) select jcpz_user_role_info_t_seq.nextval,"
				+ String.valueOf(userId)
				+ ",ROLE_ID FROM JCPZ_ROLE_INFO_T Where ROLE_TYPE in('"
				+ vo.getWorkCode().replaceAll(",", "','") + "')"; // 0, 0);
		logger.info("--SQL to create user role is:" + sql);
		this.executeSQL(sql);
		logger.info("--After execute all of the SQL");
		this.commit();
		logger.info("--After commit");
		// this.closeAll();

	}

	/**
	 * 
	 * @param vo
	 * @return
	 */
	public void modifyUserInfo(UserInfoVO vo) throws SQLException {
		StringBuffer sqlBuff = new StringBuffer();
		sqlBuff.append("UPDATE JCPZ_USER_INFO_T SET ");
		SimpleDateFormat sdf = new SimpleDateFormat(
				Constant.DATE_TIME_FORMAT_FOR_JAVA);
		sqlBuff.append(" MODIFY_TIME = sysdate,");
		if (vo.getNote() != null)
			sqlBuff.append("USER_DESCRIPTION = '" + vo.getNote() + "',");
		if (vo.getDepartmentCode() != null)
			sqlBuff.append("DEPARTMENT_ID =" + vo.getDepartmentCode() + ",");
		if (vo.getPassword() == null || vo.getPassword().isEmpty()) {
		} else {
			sqlBuff.append("PASSWORD = '" + vo.getPassword() + "',");
		}
		if (vo.getStaffName() != null)
			sqlBuff.append("STAFFNAME = '" + vo.getStaffName() + "',");
		if (vo.getFlag() != null)
			sqlBuff.append("FLAG = '" + vo.getFlag() + "',");
		if (vo.getValidLength() != null)
			sqlBuff.append(" VALID_LENGTH = '" + vo.getValidLength() + "',");
		if (vo.getExpireDate() != null)
			sqlBuff.append("EXPIRE_DATE = to_timestamp('"
					+ sdf.format(vo.getExpireDate()) + "','"
					+ Constant.DATE_TIME_FORMAT_FOR_DB + "'),");
		if (vo.getLogLock() != null)
			sqlBuff.append("LOG_LOCK = '" + vo.getLogLock() + "'");
		String sql = sqlBuff.toString();
		if (sql.trim().endsWith(",")) {
			sql = sql.substring(0, sql.length() - 1);
		}
		logger.info("--SQL to modify user before where is:" + sql);
		logger.info("--User ID is:" + vo.getUserID());
		sql += " WHERE USER_ID = ";
		sql += String.valueOf(vo.getUserID());
		logger.info("--SQL to modify user is:" + sql);
		this.executeSQL(sql);
		String userRolesql = "DELETE FROM JCPZ_USER_ROLE_INFO_T WHERE USER_ID ="
				+ String.valueOf(vo.getUserID());
		this.executeSQL(userRolesql);
		userRolesql = "INSERT INTO JCPZ_USER_ROLE_INFO_T (USER_ROLE_ID, USER_ID, ROLE_ID) select jcpz_user_role_info_t_seq.nextval,"
				+ String.valueOf(vo.getUserID())
				+ ",ROLE_ID FROM JCPZ_ROLE_INFO_T Where ROLE_TYPE in('"
				+ vo.getWorkCode().replaceAll(",","','") + "')"; // 0, 0);
		logger.info("--SQL to create user role is:" + userRolesql);
		this.executeSQL(userRolesql);
		this.commit();
	}

	/**
	 * 
	 * @param loginUser
	 * @param oldPassword
	 * @param newPassword
	 * @return
	 */
	public void modifyUserPassword(String loginUser, String oldPassword,
			String newPassword) throws SQLException {

		String sql = "UPDATE JCPZ_USER_INFO_T SET  PASSWORD = '" + newPassword
				+ "' WHERE USER_NAME ='" + loginUser + "'";
		this.executeSQL(sql);
		this.commit();
	}

	/**
	 * 
	 * @param loginUser
	 */
	public void delUserInfo(String loginUser) throws SQLException {

		// List<UserInfoVO> vos = findByUserName(loginUser);
		// for (UserInfoVO vo : vos) {
		String sql = "UPDATE JCPZ_USER_INFO_T set LOG_LOCK='9' WHERE USER_NAME in('"
				+ loginUser.replaceAll(",", "','") + "')";
		this.executeSQL(sql);
		this.commit();
		/*
		 * sql = "delete from JCPZ_USER_ROLE_INFO_T  WHERE USER_ID =" +
		 * String.valueOf(vo.getUserID()); this.executeSQL(sql);
		 */
		// }

	}

	/**
 * 	
 */
	public List<UserInfoVO> findByUserName(String userName) throws SQLException {
		String sql = "SELECT USER_ID, USER_NAME, CREATE_TIME, MODIFY_TIME, USER_DESCRIPTION, DEPARTMENT_ID, PASSWORD, STAFFNAME, FLAG, VALID_LENGTH, EXPIRE_DATE, LOG_LOCK FROM JCPZ_USER_INFO_T";
		if (null == userName || userName.trim().isEmpty()) {

		} else {
			sql += " where USER_NAME='" + userName + "'";
		}

		List<UserInfoVO> userInfoList = new ArrayList<UserInfoVO>();
		ResultSet rs = this.getResultSet(sql);
		while (rs.next()) {
			UserInfoVO userInfoVO = new UserInfoVO();
			userInfoVO.setUserID(rs.getLong("USER_ID"));
			userInfoVO.setDepartmentCode(rs.getString("DEPARTMENT_ID"));
			userInfoVO.setExpireDate(rs.getTimestamp("EXPIRE_DATE"));
			userInfoVO.setFlag(rs.getString("FLAG"));
			userInfoVO.setLoginUser(rs.getString("USER_NAME"));
			userInfoVO.setLogLock(rs.getString("LOG_LOCK"));
			userInfoVO.setNote(rs.getString("USER_DESCRIPTION"));
			userInfoVO.setPassword(rs.getString("PASSWORD"));
			userInfoVO.setStaffName(rs.getString("STAFFNAME"));
			userInfoVO.setValidLength(rs.getString("VALID_LENGTH"));
			// userInfoVO.setWorkCode(rs.getString(""));
			userInfoList.add(userInfoVO);
		}
		// this.closeAll();
		return userInfoList;
	}

}