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

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 java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.type.Type;

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.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;
import com.vci.server.framework.systemConfig.stafforgmanage.user.User;
import com.vci.server.framework.systemConfig.stafforgmanage.user.UserDAOImpl;

public class RoleService extends BaseService {

	public void saveRole(final Role role) {
		new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				RoleDAOImpl impl = new RoleDAOImpl();
				role.setUserEntity(userEntity);
				impl.save(role);
				return role;
			}
		});
	}
	
	public boolean deleteRole(final String id) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				RoleDAOImpl impl = new RoleDAOImpl();
				Role role = impl.loadById(id);
				role.setUserEntity(userEntity);
				impl.delete(role);
				return true;
			}
		});
	}
	
	public boolean deleteRoleByMQL(final String id) {
		return deleteRoleByMQL(new String[]{id});
	}
	
	public boolean deleteRoleByMQL(final String[] ids) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				int len = ids.length;
				String inStr = "";
				for (int i = 0; i < len; i++) {
					inStr += "?";
					if (i != len - 1) {
						inStr += ",";
					}
				}
				RoleDAOImpl impl = new RoleDAOImpl();
				
				// add by xchao 2012.09.11
				// 能进入到此处，删除角色对象，表明该对象已经没有补引用
				// 此时删除角色时，先删除角色的权限数据
				String deleteRoleRight = "delete RoleRight r where r.roleId in(" + inStr + ")";
				impl.deleteQueryObject(deleteRoleRight, ids, userEntity);
				
				String hql = "delete Role r where r.id in (" + inStr + ")";
				impl.deleteQueryObject(hql, ids, userEntity);
				return true;
			}
		});
	}
	
	public boolean updateRole(final Role role) {
		return (Boolean)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				RoleDAOImpl impl = new RoleDAOImpl();
				Role cRole = impl.getById(role.getId());
				if (cRole == null) {
					role.setUserEntity(userEntity);
					impl.saveOrUpdate(role);
				} else {
					cRole.setName(role.getName());
					cRole.setDesc(role.getDesc());
					cRole.setType(role.getType());
					cRole.setUpdateTime(role.getUpdateTime() );
					cRole.setUpdateUser(role.getUpdateUser());
					cRole.setUserEntity(userEntity);
					impl.saveOrUpdate(cRole);
				}
				return true;
			}
		});
	}
	
	public Role selectRole(final String id) {
		return (Role)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				RoleDAOImpl impl = new RoleDAOImpl();
				String hsql = "from Role role where role.id = :id";
				return impl.findEntity(hsql, "id", id);
			}
		});
	}
	
	public Role selectRoleByName(final String name) {
		return (Role)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				RoleDAOImpl impl = new RoleDAOImpl();
				String hsql = "from Role role where role.name = :name";
				return impl.findEntity(hsql, "name", name);
			}
		});
	}
	
	public List getRoleList() {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				RoleDAOImpl impl = new RoleDAOImpl();
				return impl.loadAll();
			}
		});
	}
	
	public List getRoleByRoleType(final int type) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				RoleDAOImpl impl = new RoleDAOImpl();
				String hsql = " from Role r where r.type ="+type+" order by r.name";
				Object[] values = {};
				return impl.findEntites(hsql, values);
			}
		});
	}
	
	public List getRoleListByType(final int type) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				/**当前用户只能为下级用户分配角色**/
				RoleDAOImpl impl = new RoleDAOImpl();
				String hsql = " from Role r where r.type in (1,2) order by r.name";
//				int a = type;
//				if (type<2){
//					a = type + 1;
//				}
				
				Object[] values = {};
				return impl.findEntites(hsql, values);
			}
		});
	}
	
	public List getRoleListByTypeForMeasure(final int type) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				RoleDAOImpl impl = new RoleDAOImpl();
				String hsql = " from Role r where r.type >= '"+type+"'";
				Object[] values = new Object[0];
				return impl.findEntites(hsql, values);
			}
		});
	}
	public List getRoleListByUserId(final String  userId) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				RoleDAOImpl impl = new RoleDAOImpl();
				String hsql = " select * from plrole r where r.PLUID in  " +
						" (select ur.plroleuid from pluserrole ur where ur.pluseruid = '"+userId+"')";
				
				Object[] values = new Object[0];
				List<Role> list = impl.findEntites(hsql, values, "r", Role.class);
				return list;
			}
		});
	}
	
	
	public List getRoleListByUserName(final String  userName) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				
				UserDAOImpl impl = new UserDAOImpl();
				String hsql = "from User user where user.userName = :name";
				User user = impl.findEntity(hsql, "name", userName);
//				int type = user.getUserType();
//				if (type<2){
//					type = type + 1;
//				}
				
				
				RoleDAOImpl roleImpl = new RoleDAOImpl();
				String sql = " select * from plrole r where  r.PLUID in " + 
								"(select ur.plroleuid from pluserrole ur where ur.pluseruid = '" + user.getId() + "')";
				
				Object[] values = new Object[0];
				List<Role> list = roleImpl.findEntites(sql, values, "r", Role.class);
				return list;
			}
		});
	}
	
	
	public List getRoleListByUserType(final String  userName) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				
				UserDAOImpl impl = new UserDAOImpl();
				String hsql = "from User user where user.userName = :name";
				User user = impl.findEntity(hsql, "name", userName);
				int type = user.getUserType();
				if (type < 2){
					type = type + 1;
				}
				
				
				RoleDAOImpl roleImpl = new RoleDAOImpl();
				String sql = " select * from plrole r where  r.pltype = "+type+"" ;
				
				Object[] values = new Object[0];
				List<Role> list = roleImpl.findEntites(sql, values, "r", Role.class);
				return list;
			}
		});
	}

	
	/**获取角色分页功能的实现**/
	@SuppressWarnings("rawtypes")
	public List getRoleListByUserName(final int pageNo,final int pageSize ,final String  userName) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				int V_PAGE_ROWNUM_START = (pageNo - 1) * pageSize;
		        int V_PAGE_ROWNUM_END = (pageNo ) * pageSize;
		        
				UserDAOImpl impl = new UserDAOImpl();
				String hsql = "from User user where user.userName = :name";
				User user = impl.findEntity(hsql, "name", userName);
				int type = user.getUserType();
				if (type<2){
					type = type + 1;
				}
				
				RoleDAOImpl roleImpl = new RoleDAOImpl();
				StringBuffer sb = new StringBuffer();
				sb.append(" SELECT * ");
				sb.append("   FROM (SELECT ROW_.*, ROWNUM RN ");
				sb.append("      FROM (select * from plrole r where  r.pltype = ");
				sb.append(type);
				sb.append("  ORDER BY R.PLNAME )");
				sb.append("  ROW_)");
				sb.append("   WHERE RN <= ");
				sb.append( V_PAGE_ROWNUM_END);
				sb.append(" AND RN > ");
				sb.append(V_PAGE_ROWNUM_START);
				
				Object[] values = new Object[0];
				List<Role> list = roleImpl.findEntites(sb.toString(), values, "r", Role.class);
				return list;
			}
		});
	}
	
	/***根据用户名查询对应的角色总数*/
	public int getRoleTotalByUserName(String userName) {
		UserDAOImpl impl = new UserDAOImpl();
		String hsql = "from User user where user.userName = :name";
		User user = impl.findEntity(hsql, "name", userName);
		int type = user.getUserType();
		if (type<2){
			type = type + 1;
		}
		
		int res = 0;
		String sql = "SELECT COUNT(*) FROM PLROLE R WHERE R.PLTYPE = '" + type + "'";
//		String sql = "SELECT COUNT(*) FROM PLROLE R WHERE R.PLTYPE in ('1','2')";
		res = (Integer)new JDBCTemplate().run(new JDBCCallback(
				new HibernateTemplate().getSessionConnection(), 
				sql, 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;
	}
		
	public List getRolePageList(final int pageNo, final int pageSize) {
		return (List)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				RoleDAOImpl impl = new RoleDAOImpl();
				return impl.findList(pageNo, pageSize);
			}
		});
	}
	
	public boolean saveRight(final String roleId , final String[] userIds ){
		return (Boolean)new HibernateTemplate().runExt(new HibernateCallbackExt() {
				public Object execute(Connection conn) throws HibernateException,
							SQLException {
				String  sql = "delete pluserrole ur where ur.plroleuid = ? ";
				PreparedStatement pstmt = null;
				try {
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, roleId);
					pstmt.execute();
				} finally {
					if (pstmt != null){
						pstmt.close();
					}
				}
				Statement st = null;
				try {
					st = conn.createStatement();
					for (int i = 0 ; i < userIds.length ; i ++){
						StringBuffer sqlBuffer = new StringBuffer("insert into pluserrole values ('");
						sqlBuffer.append(userIds[i]).append("','").append(roleId).append("')");
						st.addBatch(sqlBuffer.toString());
						if ((i + 1) % 200 == 0) {
							st.executeBatch();
						}
					}
					
					if (userIds.length % 200 != 0) {
						st.executeBatch();
					}
				} finally {
					if (st != null) {
						st.close();
						st = null;
					}
				}
				return true;
			}
	  });
	}
	/**
	 * 保存三员对应的成员
	 * @param roleId
	 * @param userIds
	 * @return
	 */
	public boolean saveSpecialRole(final String roleId , final String[] userIds ){
		return (Boolean)new HibernateTemplate().runExt(new HibernateCallbackExt() {
			public Object execute(Connection conn) throws HibernateException,
			SQLException {
				PreparedStatement pstmt = null;
				try {
					for (int i = 0 ; i < userIds.length ; i ++){
						String hql = "insert into pluserrole (PLUSERUID, PLROLEUID) values (? , ?) ";
						pstmt = conn.prepareStatement(hql);
						pstmt.setString(1, userIds[i]);
						pstmt.setString(2, roleId);
						pstmt.execute();
					}
				}catch (SQLException e1) {
					e1.printStackTrace();
				} finally {
					if (pstmt != null){
						pstmt.close();
					}
				}
				return true;
			}
		});
	}
	
	public int checkRoleIsquotedCount(final String id ){
		return (Integer)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
				int count = 0;
				RoleDAOImpl impl = new RoleDAOImpl();
				String hsql = "select count(*) from pluserrole u where u.PLROLEUID = ?" ;
				//List list = new ArrayList();
				Object[] values = new Object[] {id};
				Map<String,Type> map = new HashMap<String,Type>();
				List<?> list = impl.findEntitesBySQL(hsql, values,map);
				if (list != null){
					count = Integer.valueOf(String.valueOf(list.get(0))).intValue();
				}
				return count;
			}
		});
	}
	
	@SuppressWarnings("unchecked")
	public List<Role> queryRoleInfos(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<Role>)new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {
			RoleDAOImpl impl = new RoleDAOImpl();
			StringBuffer sb=new StringBuffer();
			sb.append(" select * from   ");
			sb.append(" ( ");
			sb.append("  select plrole.*, ROWNUM rn  from plrole  ");
			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<Role> list = impl.findEntites(sb.toString(), values, "r", Role.class);
			return list;
			}
		});
	}
	public int queryRoleInfosCount(String filter){
		StringBuffer sb=new StringBuffer();
		sb.append("  select count(*) from plrole  ");
		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;
	
	}
}