package cn.myapps.runtime.rest.department.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

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

import com.alibaba.fastjson.JSONObject;

import cn.myapps.authtime.common.dao.ConnectionManager;
import cn.myapps.authtime.common.service.AuthTimeServiceManager;
import cn.myapps.authtime.domain.model.DomainVO;
import cn.myapps.authtime.domain.service.DomainProcess;
import cn.myapps.runtime.rest.department.model.Department;
import cn.myapps.runtime.rest.user.dao.AbstractUserDAO;

public abstract class AbstractDepartmentDAO {
	
	Logger log = LoggerFactory.getLogger(AbstractUserDAO.class);

	protected String dbType = "Oracle: ";

	protected String schema = "";

	protected Connection connection;

	public AbstractDepartmentDAO() throws Exception {
		this.connection = ConnectionManager.getConnection();
		dbType = ConnectionManager.dbType;
	}
	
	public String add(Department dept) throws Exception {
		String domainId = null;
		DomainProcess dp = AuthTimeServiceManager.domainRuntimeService();
		Collection<DomainVO> domainList = dp.getAllDomain();
		for(DomainVO domain : domainList){
			domainId = domain.getId();
		}
		PreparedStatement stmt = null;
		int level = 0;
		String ID = dept.getID();
		String name = dept.getName();
		String parentId = dept.getParentId();
		String order = dept.getOrder();
		String description = dept.getDescription();
		String isVirtual = dept.getIsVirtual();
		String master = dept.getMaster();
		String leader = dept.getLeader();
		String extend = dept.getExtend();
		String selectSql = "SELECT LEVELS FROM t_department WHERE ID = '" + parentId + "'";
		stmt = connection.prepareStatement(selectSql);
		ResultSet rs = stmt.executeQuery();
		if(rs.next()){
			level = Integer.parseInt(rs.getString("LEVELS")) + 1 ;
		}
		String sql = null;
		if(parentId.equals("")||parentId.equals(null)){
			sql = "INSERT INTO t_department (ID,NAME,DOMAIN_ID,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7,LEVELS) "
					   + "VALUES ('" + ID +"','" + name + "','" + domainId + "','" + order + "','" + description + "','" + isVirtual + "','" + master + "','"
					    + leader + "','" + extend + "'," + level + ")";
		}else{
		sql = "INSERT INTO t_department (ID,NAME,SUPERIOR,DOMAIN_ID,FIELD2,FIELD3,FIELD4,FIELD5,FIELD6,FIELD7,LEVELS) "
				   + "VALUES ('" + ID +"','" + name + "','" + parentId + "','" + domainId + "','" + order + "','" + description + "','" + isVirtual + "','" + master + "','"
				    + leader + "','" + extend + "'," + level + ")";
		}
		try{
			stmt = connection.prepareStatement(sql);
			int num = stmt.executeUpdate();
			if(num==1) {
				return "id:"+ID;
			}else{
				return "失败";
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			ConnectionManager.closeStatement(stmt);
		}
		return null;
	}
	
	public Department find(String ID) throws Exception {
		PreparedStatement stmt = null;
		String sql = "SELECT * FROM t_department WHERE ID = '" + ID + "'";
		stmt = connection.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery();
		rs.next();
		Department detp = new Department();
		detp.setID(rs.getString("ID"));
		detp.setName(rs.getString("NAME"));
		detp.setParentId(rs.getString("FIELD1"));
		detp.setOrder(rs.getString("FIELD2"));
		detp.setDescription(rs.getString("FIELD3"));
		detp.setIsVirtual(rs.getString("FIELD4"));
		detp.setMaster(rs.getString("FIELD5"));
		detp.setLeader(rs.getString("FIELD6"));
		detp.setExtend(rs.getString("FIELD7"));
		return detp;
	}
	
	public String update(JSONObject jsonObject) throws Exception {
		String ID = null;
		Map<String,String> map = new HashMap<String,String>();
		Set<Entry<String,Object>> set = jsonObject.entrySet();
		for(Entry<String,Object> entry : set){
			String key = (String)entry.getKey();
			if(key.equals("id")){
				ID = (String)entry.getValue();
			}else{
				if(key.equals("name")){
					map.put("NAME", (String)entry.getValue());
				}
				if(key.equals("parentid")){
					map.put("FIELD1", (String)entry.getValue());
				}
				if(key.equals("order")){
					map.put("FIELD2", (String)entry.getValue());
				}
				if(key.equals("description")){
					map.put("FIELD3", (String)entry.getValue());
				}
				if(key.equals("isvirtual")){
					map.put("FIELD4", (String)entry.getValue());
				}
				if(key.equals("master")){
					map.put("FIELD5", (String)entry.getValue());
				}
				if(key.equals("leader")){
					map.put("FIELD6", (String)entry.getValue());
				}
				if(key.equals("extend")){
					map.put("FIELD7", (String)entry.getValue());
				}
			}
		}
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE t_department SET ");
		for (Entry<String, String> entryUpdate : map.entrySet()) {
			sql.append(entryUpdate.getKey() + " = '" + entryUpdate.getValue() + "'");
			sql.append(" , ");
		}
		String sqlStr = sql.toString();
		sqlStr = sqlStr.substring(0, sqlStr.length()-3) + " WHERE ID = '" + ID + "'";
		PreparedStatement stmt = null;
		stmt = connection.prepareStatement(sqlStr);
		int num = stmt.executeUpdate();
		if(num!=1) {
			return "失败";
		}else{
			return "成功";
		}
	}
	
	public String delete(String id) throws Exception {
		PreparedStatement stmt = null;
		String userSql = "SELECT * FROM t_user WHERE DEFAULTDEPARTMENT = '" + id + "'";
		stmt = connection.prepareStatement(userSql);
		ResultSet userRS = stmt.executeQuery();
		if(userRS.next()){
			return "部门下有成员，不允许删除";
		}
		String deptSql = "SELECT * FROM t_department WHERE SUPERIOR = '" + id + "'";
		stmt = connection.prepareStatement(deptSql);
		ResultSet deptRS = stmt.executeQuery();
		if(deptRS.next()){
			return "部门下有子部门，不允许删除";
		}
		String delSql = "DELETE FROM t_department WHERE ID = '" + id + "'";
		stmt = connection.prepareStatement(delSql);
		int num = stmt.executeUpdate();
		if(num!=1) {
			return "失败";
		}else{
			return "成功";
		}
	}
	
	public Collection<Department> get(String ID) throws Exception {
		Collection<Department> deptList = new ArrayList<Department>();
		PreparedStatement stmt = null;
		if(ID.equals("0")){
			String sql = "SELECT * FROM t_department";
			stmt = connection.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()){
			Department detp = new Department();
			detp.setID(rs.getString("ID"));
			detp.setName(rs.getString("NAME"));
			detp.setParentId(rs.getString("FIELD1"));
			detp.setOrder(rs.getString("FIELD2"));
			detp.setDescription(rs.getString("FIELD3"));
			detp.setIsVirtual(rs.getString("FIELD4"));
			detp.setMaster(rs.getString("FIELD5"));
			detp.setLeader(rs.getString("FIELD6"));
			detp.setExtend(rs.getString("FIELD7"));
			deptList.add(detp);
			}
			return deptList;
		}else{
			String sql = "SELECT * FROM t_department WHERE ID = '" + ID + "'";
			stmt = connection.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			while(rs.next()){
			Department detp = new Department();
			detp.setID(rs.getString("ID"));
			detp.setName(rs.getString("NAME"));
			detp.setParentId(rs.getString("FIELD1"));
			detp.setOrder(rs.getString("FIELD2"));
			detp.setDescription(rs.getString("FIELD3"));
			detp.setIsVirtual(rs.getString("FIELD4"));
			detp.setMaster(rs.getString("FIELD5"));
			detp.setLeader(rs.getString("FIELD6"));
			detp.setExtend(rs.getString("FIELD7"));
			deptList.add(detp);
			String superiorId = rs.getString("ID");
			findSubDept(deptList,superiorId);
			}
			return deptList;
		}
	}
	public void findSubDept(Collection<Department> deptList,String superiorId) throws Exception {
		PreparedStatement stmt = null;
		String subSql = "SELECT * FROM t_department WHERE SUPERIOR = '" + superiorId + "'";
		stmt = connection.prepareStatement(subSql);
		ResultSet subRS = stmt.executeQuery();
		while(subRS.next()){
			Department detp = new Department();
			detp.setID(subRS.getString("ID"));
			detp.setName(subRS.getString("NAME"));
			detp.setParentId(subRS.getString("FIELD1"));
			detp.setOrder(subRS.getString("FIELD2"));
			detp.setDescription(subRS.getString("FIELD3"));
			detp.setIsVirtual(subRS.getString("FIELD4"));
			detp.setMaster(subRS.getString("FIELD5"));
			detp.setLeader(subRS.getString("FIELD6"));
			detp.setExtend(subRS.getString("FIELD7"));
			deptList.add(detp);
			String superior = subRS.getString("ID");
			findSubDept(deptList,superior);
		}
	}
}
