package com.vci.server.framework.systemConfig.stafforgmanage.dept;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.HibernateException;

import com.vci.common.objects.UserEntity;
import com.vci.server.base.persistence.dao.BaseService;
import com.vci.server.base.persistence.dao.HibernateCallback;
import com.vci.server.base.persistence.dao.HibernateCallbackExt;
import com.vci.server.base.persistence.dao.HibernateSessionFactory;
import com.vci.server.base.persistence.dao.HibernateTemplate;
import com.vci.server.base.persistence.dao.JDBCCallback;
import com.vci.server.base.persistence.dao.JDBCRunType;
import com.vci.server.base.persistence.dao.JDBCTemplate;

public class DepartmentService extends BaseService {
	public DepartmentService() {}
	public DepartmentService(UserEntity userEntity) {
		super(userEntity);
	}

	public void saveDepartment(final Department dept) {
		new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				dept.setUserEntity(userEntity);
				impl.save(dept);
				return dept;
			}
		});
	}
	
	public void batchSaveDepart(Department[] depts) throws HibernateException, SQLException {
		String sql = "INSERT INTO PLDEPT VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
		Timestamp currentTime = new Timestamp(System.currentTimeMillis());
		PreparedStatement pst = null;
		int index = 1;
		int batchSize = 200;
		for (int i = 0; i < depts.length; i++) {
			index = 1;
			if(pst == null){
				pst = HibernateSessionFactory.getSessionConnection().prepareStatement(sql);
			}
			pst.setString(index++, depts[i].getId());
			pst.setString(index++, depts[i].getName());
			pst.setString(index++, depts[i].getNum());
			pst.setString(index++, depts[i].getCode());
			pst.setInt(index++, 0);
			pst.setString(index++, depts[i].getParentId());
			pst.setString(index++, depts[i].getDesc());
			pst.setTimestamp(index++, currentTime);
			pst.setString(index++, userEntity.getUserName());
			pst.setTimestamp(index++, currentTime);
			pst.setString(index++, userEntity.getUserName());
			pst.setString(index++, "");
			pst.addBatch();
			
			// 是否达到批量执行阀值
			if((i+1) % batchSize == 0){
				// 达到则执行
				pst.executeBatch();
			}
		}
		
		pst.executeBatch();

		closePreparedStatement(pst);
	}
	
	private void closePreparedStatement(PreparedStatement pst) throws SQLException{
		if(pst != null){
			pst.clearBatch();
			pst.clearParameters();
			pst.close();
			pst = null;
		}
	}
	
	@SuppressWarnings("rawtypes")
	public List deleteDepartment(final String id) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				String sql = " select * from  PLDEPT d start with d.pluid = ? connect by prior d.pluid = d.plparentuid " ;
				Object[] values = new Object[1];
				values[0] = id;
				List list = impl.findEntites(sql, values, "PLDEPT", Department.class);
				for(int i=0;i<list.size();i++) {
					Department object = (Department)list.get(i);
					String id = object.getId();
					Department entity = impl.loadById(id);
					entity.setUserEntity(userEntity);
					impl.delete(entity);
				}
				return list;
			}
		});
	}
	
	public boolean deleteDepartmentByHQL(final String id) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				String hql = "delete Department dept where dept.id=:id";
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				impl.deleteQueryObject(hql, "id", id, userEntity);
				return true;
			}
		});
	}
	
	/**
	 * 更新部门同步状态
	 * @param ids
	 * @return
	 */
	public boolean updateByDeptCode(final String[] ids) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				String hql1 = "update Department dept set dept.status = 2 where dept.num in(";
				int len1 = ids.length;
				for (int i = 0; i < len1; i++) {
					hql1 += "?";
					if (i != len1 - 1) {
						hql1 += ",";
					}
				}
				hql1 += ")";
				
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				impl.deleteQueryObject(hql1, ids, userEntity);
				return true;
			}
		});
	}

	public boolean updateDeptParentId(final String id, final String parentId) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				String hql1 = "update Department dept set dept.parentId = :parentId where dept.id = :id";
				String[] props = new String[2];
				props[0] = "parentId";
				props[1] = "id";
				
				String[] values = new String[2];
				values[0] = parentId;
				values[1] = id;
				
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				impl.deleteQueryObject(hql1, props, values, userEntity);
				return true;
			}
		});
	}
	
	public boolean deleteDepartmentByHQL(final String[] ids) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				String hql1 = "delete Department dept where dept.parentId in(";
				int len1 = ids.length;
				for (int i = 0; i < len1; i++) {
					hql1 += "?";
					if (i != len1 - 1) {
						hql1 += ",";
					}
				}
				hql1 += ")";
				
				String hql = "delete Department dept where dept.id in(";
				int len = ids.length;
				for (int i = 0; i < len; i++) {
					hql += "?";
					if (i != len - 1) {
						hql += ",";
					}
				}
				hql += ")";
				
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				impl.deleteQueryObject(hql1, ids, userEntity);
				impl.deleteQueryObject(hql, ids, userEntity);
				return true;
			}
		});
	}
	
	public boolean updateDepartment(final Department dept) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				Department curDept = impl.getById(dept.getId());
				if (curDept == null) {
					dept.setUserEntity(userEntity);
					impl.saveOrUpdate(dept);
				} else {
					curDept.setName(dept.getName());
					curDept.setNum(dept.getNum());
					curDept.setCode(dept.getCode());
					curDept.setStatus(curDept.getStatus());
					curDept.setDesc(dept.getDesc());
					curDept.setSpecialties(dept.getSpecialties());
					curDept.setUpdateTime(dept.getUpdateTime());
					curDept.setUpdateUser(dept.getUpdateUser());
					curDept.setGrantor(dept.getGrantor());
					curDept.setUserEntity(userEntity);
					impl.saveOrUpdate(curDept);
				}
				
				return true;
			}
		});
	}
	
	public Department selectDepartmentById(final String id) {
		return (Department)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				return impl.loadById(id);
			}
		});
	}
	public Department fetchManageDept(final String rmTypeId) {
		return (Department)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				String hsql = " select * from pldept d where d.PLUID in  " +
						" (select ud.PlDEPTID from plmanagedept ud where ud.PLCLASSIFYID = '"+rmTypeId+"')";
				Object[] values = new Object[0];
				List<Department> list = impl.findEntites(hsql, values, "d", Department.class);
				if (list.size()> 0){
					return list.get(0);
				}else{
					return null;
				}
				
			}
		});
	}
	public Department fetchManageOfMaterialsDept(final String rmTypeId) {
		return (Department)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				String hsql = " select * from pldept d where d.PLUID in  " +
						" (select ud.PLmaterialsDeptId from plmanagedept ud where ud.PLCLASSIFYID = '"+rmTypeId+"')";
				Object[] values = new Object[0];
				List<Department> list = impl.findEntites(hsql, values, "d", Department.class);
				if (list.size()> 0){
					return list.get(0);
				}else{
					return null;
				}
				
			}
		});
	}
	public Department fetchDeptByUserId(final String userId) {
		return (Department)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				String hsql = " select * from pldept d where d.PLUID in  " +
						" (select ud.pldeptuid from pluserdept ud where ud.pluseruid = '"+userId+"')";
				Object[] values = new Object[0];
				List<Department> list = impl.findEntites(hsql, values, "d", Department.class);
				if (list.size()> 0){
					return list.get(0);
				}else{
					return null;
				}
				
			}
		});
	}
	
	public Department selectDepartmentByName(final String name) {
		return (Department)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				String hsql = "from Department dept where dept.name = :name";
				return impl.findEntity(hsql, "name", name);
			}
		});
	}
	
	public Department fetchDeptByParentIdAndName(final String parentId ,final String name) {
		return (Department)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				String hsql = "from Department dept where dept.name = '"+name+"' ";
				if (!"".equals(parentId)){
					hsql += "and dept.parentId = '"+parentId+"'";
				}else{
					hsql += "and dept.parentId is null";
				}
					
						
				return impl.findEntity(hsql);
			}
		});
	}
	
	@SuppressWarnings("rawtypes")
	public List getDepartmentList() {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				return impl.loadAll();
			}
		});
	}
	
	@SuppressWarnings("rawtypes")
	public List getDepartmentListByFilter(final boolean root, final String prtoid) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				List res = null;
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				String hsql = "from Department dept where ";
				if(root){
					hsql += " dept.parentId is null";
					res = impl.findEntities(hsql);
				}else{
					if(prtoid != null && !prtoid.equals("")){
						hsql += " dept.parentId = ?";
						Object[] values = { prtoid };
						res = impl.findEntites(hsql, values);
					}else if ("".equals(prtoid)){
						hsql += " dept.parentId is null";
						res = impl.findEntities(hsql);
					}
				}
				return res;
			}
		});
	}
	
	@SuppressWarnings("rawtypes")
	public List getDepartmentPageList(final int pageNo, final int pageSize) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				return impl.findList(pageNo, pageSize);
			}
		});
	}
	@SuppressWarnings("rawtypes")
	public List getDepartmentListById(final String id) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				if("root".equals(id)) {
					return impl.loadAll();
				}
				String sql = " select * from  PLDEPT d start with d.pluid = ? connect by prior d.pluid = d.plparentuid " ;
				
				Object[] values = new Object[1];
				values[0] = id;
				List list = impl.findEntites(sql, values, "PLDEPT", Department.class);
				return list;
				
			}
		});
	}
	@SuppressWarnings("rawtypes")
	public List getDepartmentListBySonId(final String id) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				if("root".equals(id)) {
					return impl.loadAll();
				}
				String sql = " select * from  PLDEPT d start with d.pluid = ? connect by prior  d.plparentuid=d.pluid " ;
				Object[] values = new Object[1];
				values[0] = id;
				List list = impl.findEntites(sql, values, "PLDEPT", Department.class);
				return list;
				
			}
		});
	}
	@SuppressWarnings("rawtypes")
	public List getDepartmentListByType(final String type) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				String sql = " select * from  PLDEPT d start with d.PLUSERTYPE = ? connect by prior d.PLOID = d.PLPARENTOID " ;
				int a = Integer.parseInt(type);
				String type1 = "";
				if (a<2){
					a = a + 1;
				}
				type1 = "" + a + "";
				Object[] values = { type1 };
				List list = impl.findEntites(sql, values, "PLDEPT", Department.class);
				return list;
				
			}
		});
	}
	@SuppressWarnings("rawtypes")
	public List getDepartmentInfoRootByType(final boolean root, final String type) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				List res = null;
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				String hsql = "from Department dept where 1=1 ";
				if(root){
					hsql += " and dept.parentId is null and dept.userType = ? ";
					int a = Integer.parseInt(type);
					String type1 = "";
					if (a<2){
						a = a + 1;
					}
					type1 = "" + a + "";
					Object[] values = { type1 };
					res = impl.findEntites(hsql, values);
				}
				return res;
			}
		});
	}
	
	public boolean saveRight(final String deptId , final String[] userIds ){
		return (Boolean)new HibernateTemplate().runExt(new HibernateCallbackExt() {
				public Object execute(Connection conn) throws HibernateException,
							SQLException {
				String  sql = "delete pluserdept ud where ud.pldeptuid = ? ";
				PreparedStatement pstmt = null;
				PreparedStatement pstmt1 = null;
				try {
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, deptId);
					pstmt.execute();
				} catch (SQLException e1) {
					e1.printStackTrace();
				} finally {
					if (pstmt != null){
						pstmt.close();
					}
				}
				try {
					for (int i = 0 ; i < userIds.length ; i ++){
						String hql = "delete pluserdept ud where ud.pluseruid = ?";
						pstmt1 = conn.prepareStatement(hql);
						pstmt1.setString(1, userIds[i]);
						pstmt1.execute();
					}
				}catch (SQLException e1) {
					e1.printStackTrace();
				} finally {
					if (pstmt1 != null){
						pstmt1.close();
					}
				}
				try {
					for (int i = 0 ; i < userIds.length ; i ++){
						String hql = "insert into pluserdept values (? , ?)";
						pstmt1 = conn.prepareStatement(hql);
						pstmt1.setString(1, userIds[i]);
						pstmt1.setString(2, deptId);
						pstmt1.execute();
					}
				}catch (SQLException e1) {
					e1.printStackTrace();
				} finally {
					if (pstmt1 != null){
						pstmt1.close();
					}
				}
				return true;
			}
	  });
	}
	
	
	@SuppressWarnings("unchecked")
	public List<Department> fetchDeptByUserNames(final String[] userNames) {
		return (List<Department>)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				
				String names = "";
				int k = 0;
				for(String name : userNames) {
					names += "'" + name + "'";
					if(k++ < userNames.length - 1) {
						names += ",";
					}
					
				}
				
				String sql = " select p.* from pldept p start with p.pluid in( " +
					         "   select up.pldeptuid from pluserdept up where up.pluseruid in( " +
					    	 "      select u.pluid from pluser u where u.plusername in("+ names +") "+
					    	 "   )" +
					    	 " )" +
					    	 " connect by prior p.plparentuid = p.pluid " ;
				
				Object[] values = new Object[0];
				List<Department> list = impl.findEntites(sql, values, "Department", Department.class);
				return list;
			}
		});
	}
	
	@SuppressWarnings("unchecked")
	public List<Department> fetchDeptByNum(final String num) {
		return (List<Department>)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				
				String sql = "select p.* from pldept p where p.plnum =  ?";
				
				Object[] values = new Object[1];
				values[0]= num;
				List<Department> list = impl.findEntites(sql, values, "Department", Department.class);
				return list;
			}
		});
	}
	
	@SuppressWarnings("unchecked")
	public List<Department> fetchDepartmentInfoByIds(final String otherFiterString){
		return (List<Department>)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				StringBuffer sql=new StringBuffer();
				sql.append(" select p.* from pldept p where 1=1  ");
				sql.append(otherFiterString);
				Object[] values = new Object[0];
				List<Department> list = impl.findEntites(sql.toString(), values, "Department", Department.class);
				return list;
			}
		});
	}
	
	public List fetchChildrenDeptByParentOid(final String prtoid,
			final boolean iscontains, final String otherFiterString){
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				DepartmentDAOImpl impl = new DepartmentDAOImpl();
				StringBuffer sb=new StringBuffer();
				sb.append(" select * from PLDEPT ");
				Object[] values = new Object[]{};
				if(iscontains){
					sb.append(" start with " + (StringUtils.isBlank(prtoid)?" (plparentuid is null or plparentuid = '') ":("pluid = '"+prtoid+"'")));
					sb.append(" connect by prior pluid= plparentuid ");
				}else{
					sb.append(" and ( plparentuid='"+prtoid+"' or pluid='"+prtoid+"' )");
				}
				if(!"".equals(otherFiterString)){
					sb.append( otherFiterString);
				}
				List list = impl.findEntites(sb.toString(), values, "PLDEPT", Department.class);
				return list;
			}
		});
	
	}
	
	@SuppressWarnings("unchecked")
	public List<Department>  gridDeptDataGrids(final String filter, int pageNo, int pageSize){
		final int V_PAGE_ROWNUM_START = (pageNo - 1) * pageSize;
		final int V_PAGE_ROWNUM_END = (pageNo ) * pageSize;
		return (List<Department>)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
			DepartmentDAOImpl impl = new DepartmentDAOImpl();
			StringBuffer sb=new StringBuffer();
			sb.append(" select * from   ");
			sb.append(" ( ");
			sb.append("  select pldept.*,rownum RN from pldept  ");
			sb.append(" where 1=1 ");
			sb.append( filter ); 
			sb.append("  ) ");
			sb.append(" WHERE RN >");
			sb.append(V_PAGE_ROWNUM_START);
			sb.append(" AND RN <= ");
			sb.append(V_PAGE_ROWNUM_END);
			Object[] values = new Object[0];
			List<Department> list = impl.findEntites(sb.toString(), values, "u", Department.class);
			return list;}
		});
	}
	
	public int gridDeptDataGridsCount(final String filter){
		StringBuffer sb=new StringBuffer();
		sb.append("  select count(*) from pldept  ");
		sb.append(" where 1=1 ");
		sb.append( filter ); 
		 int res=0;
		  res = (Integer)new JDBCTemplate().run(new JDBCCallback(
				new HibernateTemplate().getSessionConnection(), 
				sb.toString(), JDBCRunType.SQL, 0, false, false,new Object[]{}) {
			@Override
			public Object execute(ResultSet rst) throws SQLException {
				int ress = 0;
				while(rst.next()){
					ress = rst.getInt(1);
				}
				return ress;
			}
		});
		  return res;
	}
}