wp/nwyj/.svn/pristine/bb/bbc39faef5010d2c1a142391c7a7a7f39ca18c94.svn-base

/**
 * 
 */
package com.sinosoft.am.org.dept.outdept.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.persistence.DbConnection;
import com.sinosoft.am.org.dept.indept.dao.OrgInDeptDao;
import com.sinosoft.am.org.dept.outdept.vo.OrgOutDeptVo;

import org.apache.log4j.Logger;

/**
 * 
 * @author 蒋云涛
 * 
 */
public class OrgOutDeptDao{
	/* log信息 */
	private Logger	log	= Logger.getLogger(OrgInDeptDao.class);

	/**
	 * 根据给定节点的id，获得下面的子机构；
	 * 
	 * @param parentId
	 * @return
	 */
	public ArrayList<OrgOutDeptVo> getOrgOutDeptList(String parentId) {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;

		DbConnection db = new DbConnection();
		StringBuffer sql = new StringBuffer();
		ArrayList<OrgOutDeptVo> list = new ArrayList<OrgOutDeptVo>();
		sql.append("--查询外部组织机构    \n");
		sql.append("SELECT * FROM EMC_AM_OUT_DEPARTMENT WHERE IS_DEL='0' \n");
		sql.append("AND  PARENT_ID ='" + parentId + "'");
		log.info(sql);
		try {
			conn = db.getConnection();
			st = conn.createStatement();
			rs = st.executeQuery(sql.toString());
			while (rs.next()) {
				list.add(getOrgOutDeptVo(rs));
			}
			return list;
		} catch (Exception e) {
			log.error("查询内部组织机构根节点错误", e);
		} finally {
			db.close(conn);
			db.close(st);
			db.close(rs);
		}
		return null;
	}

	/**
	 * 新增外部机构只是简单通过objectId parentId deptId新增
	 * 
	 * @param deptId
	 * @param parentId
	 * @param deptName
	 * @return 是否新增成功
	 */
	public boolean addOrgOutDept(String deptId, String parentId, String deptName) {

		Connection conn = null;
		PreparedStatement ps = null;
		DbConnection db = new DbConnection();
		StringBuffer sql = new StringBuffer();
		/*String objectId = UUID.randomUUID().toString();*/
		String objectId = deptId;
		sql.append("--新增外部组织机构    \n");
		sql.append("INSERT INTO EMC_AM_OUT_DEPARTMENT(FD_OBJECTID,DEPT_ID,PARENT_ID,ORG_DEPT_NAME,IS_DEL)VALUES(?,?,?,?,?)   \n");
		log.info(sql + "-------------------sql");
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, objectId);
			ps.setString(2, deptId);
			ps.setString(3, parentId);
			ps.setString(4, deptName);
			ps.setString(5, "0");
			return ps.executeUpdate() == 1;
		} catch (Exception e) {
			log.error("新增内部组织机构根节点错误", e);
		} finally {
			db.close(conn);
			db.close(ps);
		}
		return false;
	}

	/**
	 * 修改外部机构名称
	 * 
	 * @param deptId
	 * @param parentId
	 * @param deptName
	 * @return
	 */
	public boolean editOrgOutDeptName(String deptId, String deptName) {

		Connection conn = null;
		PreparedStatement ps = null;
		DbConnection db = new DbConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("--修改外部组织机构名称    \n");
		sql.append("UPDATE EMC_AM_OUT_DEPARTMENT SET ORG_DEPT_NAME=? WHERE DEPT_ID=?  \n");
		log.info(sql + "-------------------sql");
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, deptName);
			ps.setString(2, deptId);
			return ps.executeUpdate() == 1;
		} catch (Exception e) {
			log.error("修改内部组织机构根节点错误", e);
		} finally {
			db.close(conn);
			db.close(ps);

		}

		return false;

	}
	
	
	public boolean updateOrgOutDeptName(String deptId, String deptName) {

		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		DbConnection db = new DbConnection();
		//修改机构名称sql语句
		StringBuffer sql1 = new StringBuffer();
		//修改外部人员表的 部门 和部门名称
		StringBuffer sql2 = new StringBuffer();
		sql1.append("--修改外部组织机构名称    \n");
		sql1.append("UPDATE EMC_AM_OUT_DEPARTMENT SET ORG_DEPT_NAME=? WHERE DEPT_ID=? AND IS_DEL=? \n");
		log.info(sql1 + "-------------------sql1");
		sql2.append("--修改外部人员表的机构名称    \n");
		sql2.append("UPDATE EMC_AM_ORG_OUTLINK SET ORG_NAME=? WHERE ORG_DEPT_ID=?  \n");
		log.info(sql2 + "-------------------sql2");
		try {
			//原子性还没做
			conn = db.getConnection();
//			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql1.toString());
			ps.setString(1, deptName);
			ps.setString(2, deptId);
			ps.setString(3,"0");
			boolean updateOrgName = ps.executeUpdate()>=1;
			boolean updateOutlinkName=true;
			if(hasStaff(deptId)){
				ps2 = conn.prepareStatement(sql2.toString());
				ps2.setString(1, deptName);
				ps2.setString(2, deptId);
				updateOutlinkName = ps2.executeUpdate()>=1;
			}
			
			boolean isUpdate = updateOrgName && updateOutlinkName;
//			conn.commit();
			return  isUpdate;
		} catch (Exception e) {
			log.error("修改外部组织机构的名称出错", e);
		} finally {
			db.close(conn);
			db.close(ps);
			db.close(ps2);

		}

		return false;

	}
	

	/**
	 * 通过部门Id删除外部机构
	 * 
	 * @param deptId
	 * @return 是否成功删除外部机构
	 */
	public boolean deleteOrgOutDept(String deptId) {
		Connection conn = null;
		PreparedStatement ps = null;
		DbConnection db = new DbConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("--删除外部组织机构名称    \n");
		sql.append("DELETE FROM EMC_AM_OUT_DEPARTMENT WHERE DEPT_ID=?   \n");
		log.info(sql + "-------------------sql");
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, deptId);
			return ps.executeUpdate() == 1;
		} catch (Exception e) {
			log.error("删除内部组织机构根节点错误", e);
		} finally {
			db.close(conn);
			db.close(ps);
		}
		return false;
	}
	/**
	 * 删除外部机构--修改删除状态为"1"
	 * @param deptId
	 * @return true为删除成功；false为删除失败； 
	 */
	public boolean shamDeleteOrgOutDept(String deptId){
		//先进行判断能否删除（判断机构下是否还有子机构存在）
		if(hasChildNode(deptId)){
			return false;
		}
		//判断机构下是否还有员工存在
		if(hasStaff(deptId)){
			
			return false;
		}
		Connection conn = null;
		PreparedStatement ps = null;
		DbConnection db = new DbConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("--删除外部组织机构名称    \n");
		sql.append("UPDATE EMC_AM_OUT_DEPARTMENT SET IS_DEL=? WHERE FD_OBJECTID=?  \n");
		log.info(sql + "-------------------sql");
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, "1");
			ps.setString(2, deptId);
			return ps.executeUpdate() == 1;
		} catch (Exception e) {
			log.error("删除外部组织机构节点错误", e);
		} finally {
			db.close(conn);
			db.close(ps);

		}
		return false;
	}

	/**
	 * @param deptId 部门id
	 * @return 是否存在员工
	 */
	private boolean hasStaff(String deptId) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		DbConnection db = new DbConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("--查询该机构是否有没有删除的员工    \n");
		sql.append("SELECT COUNT(1) c FROM EMC_AM_ORG_OUTLINK WHERE ORG_DEPT_ID=? AND IS_DEL=? \n");
		
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, deptId);
			ps.setString(2, "0");
			
			log.info("查询内部机构是否有未删除的员工sql="+sql);
			rs =ps.executeQuery();
			rs.next();
			//log.info("内部机构是否有员工"+rs.getBoolean(1));
			return rs.getBoolean(1);
		} catch (Exception e) {
			log.error("查询内部机构是否存在员工", e);
		} finally {
			db.close(conn);
			db.close(ps);
			db.close(rs);
		}
		return false;
	}

	/**
	 * 
	 * @param deptId
	 * @return 是否存在未删除的员工
	 */
	private boolean hasChildNode(String deptId) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		DbConnection db = new DbConnection();
		StringBuffer sql = new StringBuffer();
		sql.append("--查询该外部机构是否有子机构    \n");
		sql.append("SELECT COUNT(1) c FROM EMC_AM_OUT_DEPARTMENT WHERE PARENT_ID=? AND IS_DEL=? \n");
		
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, deptId);
			ps.setString(2, "0");
			log.info("查询该外部机构是否有子机构 sql="+sql);
			rs =ps.executeQuery();
			rs.next();
			//log.info("是否有子机构"+rs.getBoolean(1));
			return rs.getBoolean(1);
		} catch (Exception e) {
			log.error("查询是否存在未删除的外部机构", e);
		} finally {
			db.close(conn);
			db.close(ps);
			db.close(rs);

		}
		return false;
	}

	/**
	 * 将result结果集封装到实体类里面
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private OrgOutDeptVo getOrgOutDeptVo(ResultSet rs) throws SQLException {
		OrgOutDeptVo ov = new OrgOutDeptVo();
		ov.setCode(rs.getString("CODE"));
		ov.setDeptId(rs.getString("DEPT_ID"));
		ov.setFdObjectid(rs.getString("FD_OBJECTID"));
		ov.setIdDel(rs.getString("IS_DEL"));
		ov.setLinkDept(rs.getString("LINK_DEPT"));
		ov.setLinkMan(rs.getString("LINK_MAN"));
		ov.setLocation(rs.getString("LOCATION"));
		ov.setOrgDeptName(rs.getString("ORG_DEPT_NAME"));
		ov.setOrgDeptType(rs.getString("ORG_DEPT_TYPE"));
		ov.setParentId(rs.getString("PARENT_ID"));
		ov.setSite(rs.getString("SITE"));
		ov.setUpdateDate(rs.getString("UPDATEDATE"));
		ov.setWorkAddress(rs.getString("WORK_ADDRESS"));
		return ov;

	}

}