package com.vci.server.framework.funcmng.function;

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

import org.hibernate.HibernateException;


import com.vci.common.exception.VciException;
import com.vci.common.log.ServerWithLog4j;
import com.vci.common.objects.UserEntity;
import com.vci.common.util.CollectionUtils;
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;


/**
* <p>Title: 后台数据处理</p>
* <p>Description: 数据库里添加模块数据</p>
* <p>Copyright: Copyright {c} 2011</p>
* <p>Company: VCI</p>
* @author xf
* @time 2012-5-15
* @version 1.0
*/
public class FunctionService extends BaseService {
	
	public FunctionService(UserEntity userEntity){
		super(userEntity);
	}
	
	public FunctionService(){
	}
	
	/**
	 * <p>Description: 创建模块</p>
	 *
	 *@author xf
	 *@time 2012-5-15
	 *@return String
	 * @param entity
	 * @return
	 */
	public String saveModule(final Function entity) {
		return (String) new HibernateTemplate().run(new HibernateCallback() {
			@Override
			public Object execute() throws HibernateException {
				FunctionDaoImpl impl = new FunctionDaoImpl();
				/**检查模块名称是否重复，重复返回1**/
				String hql = "from Function where name = ? and parentId = ? and id <> ? ";
				Object[] values = new Object[3];
				values[0] = entity.getName();
				values[1] = entity.getParentId();
				values[2] = entity.getId();
				List<Function> list = impl.findEntites(hql, values);
				if(list.size() > 0 ){
					return "1";
				}
//				/**检查模块标识是否重复，重复返回2**/
//				hql = "from Function where resourceC = ? and id <> '"+entity.getId()+"'";
//				list = impl.findEntites(hql, new Object[]{entity.getResourceC()});
//				if(list.size() > 0 ){
//					return "2";
//				}
//				/**检查模块别名是否存在重复，重复返回3**/
//				hql = "from Function where aliasName = ? and id <> '"+entity.getId()+"' and aliasName is not null";
//				list = impl.findEntites(hql, new Object[]{entity.getAliasName()});
//				if(list.size() > 0 ){
//					return "3";
//				}
				
//				hql = "from Function where moduleNo = ? and id <> '"+entity.getId()+"'";
//				list = impl.findEntites(hql, new Object[]{entity.getModuleNo()});
//				if(list.size() > 0 ){
//					return "4";
//				}
				
				/**校验成功后保存，返回对象ID**/
				entity.setUserEntity(userEntity);
				impl.save(entity);
				return entity.getId();
			}
		});
	}
	/**
	 * 导入“功能模块管理”数据
	 * add by caill
	 * */
	public String importModules(final Function function) {
		new HibernateTemplate().run(new HibernateCallback() {
			public Object execute() throws HibernateException {				
				FunctionDaoImpl impl = new FunctionDaoImpl();
				//FunctionDaoImpl impl = new FunctionDaoImpl();
				function.setUserEntity(userEntity);
				impl.save(function);
			
				
				return function.getId();
			}
		});
		return null;
	}
	/**
	 * <p>Description: 修改模块</p>
	 *
	 *@author xf
	 *@time 2012-5-15
	 *@return String
	 * @param entity
	 * @return
	 */
	public String updateModule(final Function entity) {
		return (String) new HibernateTemplate().run(new HibernateCallback() {
	        @Override
			public Object execute() throws HibernateException {
				FunctionDaoImpl impl = new FunctionDaoImpl();
				/**检查模块名称是否重复，重复返回1**/
				String hql = "from Function where id = ?";
				Object[] values = new Object[1];
//				values[0] = entity.getName();
//				values[1] = entity.getParentId();
				values[0] = entity.getId();
				List<Function> list = impl.findEntites(hql, values);
				if(list.size() > 1 ){
					return "1";
				}
				
				Function func = list.get(0);
				
				func.setAliasName(entity.getAliasName());
				func.setDesc(entity.getDesc());
				func.setImage(entity.getImage());
				func.setIsValid(entity.getIsValid());
				func.setName(entity.getName());
				func.setParentId(entity.getParentId());
				func.setResourceB(entity.getResourceB());
				func.setResourceC(entity.getResourceC());
				func.setResourceDotNet(entity.getResourceDotNet());
				func.setResourceMobile(entity.getResourceMobile());
				func.setSequence(entity.getSequence());
				func.setSuffixB(entity.getSuffixB());
				func.setSuffixC(entity.getSuffixC());
				
				/**校验成功后更新保存，返回对象ID**/
				func.setUserEntity(userEntity);
				impl.saveOrUpdate(func);
				return func.getId();
			}
		});
	}
	
//	public String updateModule(final Function entity) {
//		return (String) new HibernateTemplate().run(new HibernateCallback() {
//	        @Override
//			public Object execute() throws HibernateException {
//				FunctionDaoImpl impl = new FunctionDaoImpl();
//				/**检查模块名称是否重复，重复返回1**/
//				String hql = "from Function where name = ? and parentId = ? and id = ?";
//				Object[] values = new Object[3];
//				values[0] = entity.getName();
//				values[1] = entity.getParentId();
//				values[2] = entity.getId();
//				List<Function> list = impl.findEntites(hql, values);
//				if(list.size() > 0 ){
//					return "1";
//				}
////				/**检查模块标识是否重复，重复返回2**/
////				hql = "from Function where resourceC = ? and id <> '"+entity.getId()+"'";
////				list = impl.findEntites(hql, new Object[]{entity.getResourceC()});
////				if(list.size() > 0 ){
////					return "2";
////				}
////				/**检查模块别名是否存在重复，重复返回3**/
////				hql = "from Function where aliasName = ? and id <> '"+entity.getId()+"' and aliasName is not null";
////				list = impl.findEntites(hql, new Object[]{entity.getAliasName()});
////				if(list.size() > 0 ){
////					return "3";
////				}
//				
//
////				hql = "from Function where moduleNo = ? and id <> '"+entity.getId()+"'";
////				list = impl.findEntites(hql, new Object[]{entity.getModuleNo()});
////				if(list.size() > 0 ){
////					return "4";
////				}
//				
//				/**校验成功后更新保存，返回对象ID**/
//				entity.setUserEntity(userEntity);
//				impl.saveOrUpdate(entity);
//				return entity.getId();
//			}
//		});
//	}
	
	/**
	 * <p>Description: 修改模块</p>
	 *
	 *@author xf
	 *@time 2012-5-15
	 *@return String
	 * @param entity
	 * @return
	 */
	public String deleteModule(final String id) {
		return (String) new HibernateTemplate().runExt(new HibernateCallbackExt() {
			
			@Override
			public Object execute(Connection connection) throws HibernateException,
					SQLException {
				PreparedStatement pstmt = null;
//				ResultSet rs = null;
				StringBuffer sql = new StringBuffer();
//				sql.append("select * from plroleright r where r.plfuncoid in ( ");
//				sql.append(" select f.ploid from plfunction f start with f.ploid = '").append(id).append("' connect by prior f.ploid = f.plparentid");
//				sql.append(")");
//				pstmt = connection.prepareStatement(sql.toString());
//				rs = pstmt.executeQuery();
//				if(rs.next()){
//					return "1";
//				}
//				rs.close();
				
				// 删除模块授权
				sql = new StringBuffer();
				sql.append("delete from plroleright r where r.plfuncoid in ( ");
				sql.append(" select f.ploid from plfunction f start with f.ploid = '").append(id).append("' connect by prior f.ploid = f.plparentid");
				sql.append(")");
				pstmt = connection.prepareStatement(sql.toString());
				pstmt.execute();
				
				//删除模块下的操作
				sql = new StringBuffer();
				sql.append(" delete from plfuncoperation o where o.plfuncoid in (");
				sql.append(" select f.ploid from plfunction f start with f.ploid = '").append(id).append("' connect by prior f.ploid = f.plparentid");
				sql.append(")");
				pstmt = connection.prepareStatement(sql.toString());
				pstmt.execute();
				//删除模块及子模块
				sql = new StringBuffer();
				sql.append(" delete from plfunction f where f.ploid in (");
				sql.append(" select f.ploid from plfunction f start with f.ploid = '").append(id).append("' connect by prior f.ploid = f.plparentid");
				sql.append(")");
				pstmt = connection.prepareStatement(sql.toString());
				pstmt.execute();
				pstmt.close();
				return id;
			}
		});
	}
	
	/**
	 * <p>Description: 检索所选节点的子节点</p>
	 *
	 * @param parentId
	 * @param isAll 增加判断，是否需要所有的，即是否包含无效节点
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Function> getModuleListByParentId(final String parentId,final boolean isAll) {
		return (List<Function>) new HibernateTemplate().run(new HibernateCallback() {
			@Override
			public Object execute() throws HibernateException {
				FunctionDaoImpl impl = new FunctionDaoImpl();
				String sql = "";
				if(isAll){
					sql = " from Function p where p.parentId = '"+ parentId +"' order by p.sequence" ;
				}else{
					sql = " from Function p where p.parentId = '"+ parentId +"' and p.isValid = 1 order by p.sequence" ;
				}
				return impl.findEntities(sql);
			}
		});
	}
	
	/**
	 * 根据父节点ID获取其所有的子节点模块
	 * @param parentId
	 * @param isAll
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Function> getChildrenFunctionsByParentId(final String parentId, final boolean isAll) {
		return (List<Function>) new HibernateTemplate().run(new HibernateCallback() {
			@Override
			public Object execute() throws HibernateException {
				FunctionDaoImpl impl = new FunctionDaoImpl();
				StringBuffer sb = new StringBuffer();
				sb.append("select {t.*} from plfunction t");
				if (!isAll) {
					sb.append(" where t.plisvalid = 1 ");
				}
				sb.append(" start with  t.plparentid = '").append(parentId).append("' connect by prior t.ploid = t.plparentid")
				.append(" order by t.plmodulesequence");
				return impl.findEntites(sb.toString(), new Object[0], "t", Function.class);
			}
		});
	}
	
	/**
	 * BS框架中的模块信息
	 * @param parentId
	 * @param isAll
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Function> getModuleListByParentIdForBS(final String parentId,final boolean isAll) {
		return (List<Function>) new HibernateTemplate().run(new HibernateCallback() {
			@Override
			public Object execute() throws HibernateException {
				FunctionDaoImpl impl = new FunctionDaoImpl();
				String sql = "";
				if(isAll){
					sql = " from Function p where p.parentId = '"+ parentId +"' and p.resourceB is not null order by p.sequence" ;
				}else{
					sql = " from Function p where p.parentId = '"+ parentId +"' and p.isValid = 1 and p.resourceB is not null order by p.sequence" ;
				}
				return impl.findEntities(sql);
			}
		});
	}
	/**
	 * CS框架中的模块信息
	 * @param parentId
	 * @param isAll
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Function> getModuleListByParentIdForCS(final String parentId,final boolean isAll) {
		return (List<Function>) new HibernateTemplate().run(new HibernateCallback() {
			@Override
			public Object execute() throws HibernateException {
				FunctionDaoImpl impl = new FunctionDaoImpl();
				String sql = "";
				if(isAll){
					sql = " from Function p where p.parentId = '"+ parentId +"' and p.resourceB is null order by p.sequence" ;
				}else{
					sql = " from Function p where p.parentId = '"+ parentId +"' and p.isValid = 1 and p.resourceB is null order by p.sequence" ;
				}
				return impl.findEntities(sql);
			}
		});
	}
	
	/**
	 * <p>Description: 检索所选节点的子节点</p>
	 *
	 * @param id
	 * @param 根据模块Id查询模块
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Function getModuleById(final String id) {
		return (Function) new HibernateTemplate().run(new HibernateCallback() {
			@Override
			public Object execute() throws HibernateException {
				FunctionDaoImpl impl = new FunctionDaoImpl();
				return impl.loadById(id);
			}
		});
	}
	
	/**
	 * 
	 * 获取用户有权限的子级模块
	 * @param parentId 父ID
	 * @param userName 用户名
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Function> getModuleListByRoleRight(final String parentId,final String userName){
		return (List<Function>) new HibernateTemplate().run(new HibernateCallback() {
			
			@Override
			public Object execute() throws HibernateException {
				FunctionDaoImpl impl = new FunctionDaoImpl();
				StringBuffer sql = new StringBuffer();
				sql.append("select {t.*} from plfunction t where t.ploid in (");
				sql.append("  select r.plfuncoid from plroleright r where r.plroleoid in (");
				sql.append("    select u.plroleuid from pluserrole u where u.pluseruid in (");
				sql.append("      select pluid from pluser where plusername = '").append(userName).append("'");
				sql.append("    )");
				sql.append("  ) )")
				.append(" and t.plparentid = '").append(parentId).append("'")
				// add by xchao 2012.08.03 加载有权限的子模块时，不加载禁用的模块
				.append(" and t.plisvalid = 1")
				.append(" order by t.PLMODULESEQUENCE");
				return impl.findEntites(sql.toString(), new Object[0], "t", Function.class);
			}
		});
	}
	/**
	 * 
	 * BS框架获取用户有权限的子级模块
	 * @param parentId 父ID
	 * @param userName 用户名
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Function> getModuleListByRoleRightForBS(final String parentId,final String userName){
		return (List<Function>) new HibernateTemplate().run(new HibernateCallback() {
			
			@Override
			public Object execute() throws HibernateException {
				FunctionDaoImpl impl = new FunctionDaoImpl();
				StringBuffer sql = new StringBuffer();
				sql.append("select {t.*} from plfunction t where t.ploid in (");
				sql.append("  select r.plfuncoid from plroleright r where r.plroleoid in (");
				sql.append("    select u.plroleuid from pluserrole u where u.pluseruid in (");
				sql.append("      select pluid from pluser where plusername = '").append(userName).append("'");
				sql.append("    )");
				sql.append("  ) )")
				.append(" and t.plparentid = '").append(parentId).append("'")
				.append(" and t.plisvalid = 1 and t.plresourceb is not null")
				.append(" order by t.PLMODULESEQUENCE");
				return impl.findEntites(sql.toString(), new Object[0], "t", Function.class);
			}
		});
	}
	
	//根据class查询当前模块对象
	@SuppressWarnings("unchecked")
	public Function getModuleByClass(final String classPath){
		return (Function) new HibernateTemplate().run(new HibernateCallback() {
			@Override
			public Object execute() throws HibernateException {
				FunctionDaoImpl impl = new FunctionDaoImpl();
				String hql = "from Function where resourceC = ? order by moduleNo desc";
				Object[] values = new Object[]{classPath};
				return impl.findEntity(hql,values);
			}
		});
	}
	
	
	/**
	 * 
	 * <p>根据模块funcOid信息查询出模块对象的信息</p>
	 * 
	 * @author xchao
	 * @time 2014-6-6
	 * @param funcOid
	 * @return
	 * @throws VciException
	 */
	public Function getFunctionObjectById(final String funcOid){
		return (Function) new HibernateTemplate().run(new HibernateCallback() {
			@Override
			public Object execute() throws HibernateException {
				FunctionDaoImpl impl = new FunctionDaoImpl();
				String hql = "from Function where id = ? order by moduleNo desc";
				Object[] values = new Object[]{funcOid};
				return impl.findEntity(hql,values);
			}
		});
	}
	
	public List<Function> batchGetFunctionObjectByOid(final List<String> funcOidList){
		if(funcOidList== null || funcOidList.size() == 0){
			return new ArrayList<Function>();
		}
		return (List<Function>) new HibernateTemplate().run(new HibernateCallback(){
			@Override
			public Object execute() throws HibernateException {
				FunctionDaoImpl impl = new FunctionDaoImpl();
				String hql = "from Function where id in (?) order by moduleNo desc";
				List<Function> allFunction = new ArrayList<Function>();
				int muti = 1;
				if(funcOidList.size()> 500){
					muti = (funcOidList.size() - funcOidList.size()%500)/500+1;
				}
				for(int i = 0 ; i < muti ; i ++){
					int start = i * 500;
		            int end = i * 550 + 499;
		            if (end > funcOidList.size()) {
		                end = funcOidList.size();
		            }
		            List<String> subOidList = funcOidList.subList(start, end);
		            StringBuilder sb = new StringBuilder();
		            for(String oid : subOidList){
		            	sb.append("'").append(oid).append("',");
		            }
		            hql = hql.replace("?", sb.substring(0,sb.length()-1));
		            Object[] values = new Object[0];
		            List<Function> tempFunctions = impl.findEntites(hql, values);
		            if(tempFunctions!=null){
		            	allFunction.addAll(tempFunctions);
		            }
				}
				return allFunction;
			}
		});
	}
	
	//根据模块名称查询当前模块对象
	@SuppressWarnings("unchecked")
	public Function getModuleByName(final String moduleName){
		return (Function) new HibernateTemplate().run(new HibernateCallback() {
			@Override
			public Object execute() throws HibernateException {
				FunctionDaoImpl impl = new FunctionDaoImpl();
				String hql = "from Function where name = ? order by moduleNo desc";
				Object[] values = new Object[]{moduleName};
				return impl.findEntity(hql,values);
			}
		});
	}
		
	/**
	 * 获取模块自动生成的编号
	 * @return
	 */
//	public int getAutoModuleNo(){
//		return (Integer)new HibernateTemplate().run(new HibernateCallback() {
//			
//			@Override
//			public Object execute() throws HibernateException {
//				int res = 0;
//				FunctionDaoImpl impl = new FunctionDaoImpl();
//				String hql = "from Function order by moduleNo desc";
//				List<Function> ls = impl.findEntities(hql);
//				if(ls.size() > 0){
//					Function function = ls.get(0);
//					res = function.getModuleNo() + 1;
//				}
//				return res;
//			}
//		});
//	}
//	
	/**
	 * 查询模块是否有效
	 * @param objId 模块标识或操作标识，不是ID
	 * @return
	 */
	public boolean checkModuleOrOperationIsValid(final String objId){
		return (Boolean) new HibernateTemplate().run(new HibernateCallback() {
			
			@Override
			public Object execute() throws HibernateException {
				FunctionDaoImpl impl = new FunctionDaoImpl();
				boolean res = false;
				String sql = " from PlFunctionEntity p where p.resource = '"+objId+"'";
				Function module = impl.findEntity(sql);
				if(module != null){
					if(module.getIsValid()){//有效
						res = true;
					}
				}
				return res;
			}
		});
	}
	
	/**
	 * 检查是否有子级模块
	 * @param moduleId
	 * @return 0表示该模块下无子级对象，1表示有模块，2表示有操作
	 */
	public int checkChildObject(final String moduleId){
		return (Integer)new HibernateTemplate().runExt(new HibernateCallbackExt() {
			
			@Override
			public Object execute(Connection connection) throws HibernateException,
					SQLException {
				PreparedStatement pstmt = null;
				ResultSet rs = null;
				//判断下级是否有模块
				String sql = " select ploid from plfunction f where f.plparentid = '"+moduleId+"'";
				pstmt = connection.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next()){
					return 1;//有模块
				}
				sql = " select ploid from plfuncoperation p where p.plfuncoid = '"+moduleId+"'";
				pstmt = connection.prepareStatement(sql);
				rs = pstmt.executeQuery();
				if(rs.next()){
					return 2;
				}
				return 0;
			}
		});
	}
	
	/**
	 * 获得所有功能模块类型的数量
	 * add by caill start 2015.12.8
	 * */
	public Integer getAllModelManagementNum(){
		
		return (Integer) new HibernateTemplate().runExt(new HibernateCallbackExt() {
			
			@Override
			public Object execute(Connection connection) throws HibernateException,
					SQLException {			
				
				String[] firstLevel=new String[2];
				PreparedStatement pstmt = null;
				ResultSet rs = null;
				ResultSet rs2 = null;
				ResultSet rs3 = null;
				//使用level标示每个节点在表中的级别
				String sql = "select * from (select  f.*  ,level leaf from plfunction f start with f.plparentid ='modelManagmentNode' connect by  f.plparentid=prior f.ploid)";
				pstmt = connection.prepareStatement(sql);
				rs = pstmt.executeQuery();
				int size=0;
				//计算总的数据条数
				while(rs.next()) {			
					size++;	
					ServerWithLog4j.logger.info(rs.getString(1));
					String sql2 = "select * from plfuncoperation where plfuncoid='"+rs.getString(1)+"'";
					pstmt = connection.prepareStatement(sql2);
					rs3 = pstmt.executeQuery();
					while(rs3.next()) {
						size++;
					}
					pstmt.close();			//此处必须关闭statement,否则会报“超出打开游标的最大数异常”
				}
				return size;
			}
		});
	}
	/**
	 * 查询整个树结构的数据并导出到.sql文件中
	 * add by caill start 2015.12.8
	 * */
	public String[][] getAllDatas(final int size){
		
		return (String[][]) new HibernateTemplate().runExt(new HibernateCallbackExt() {
			
			@Override
			public Object execute(Connection connection) throws HibernateException,
					SQLException {			
				String[] firstLevel=new String[2];
				PreparedStatement pstmt = null;
				ResultSet rs = null;
				ResultSet rs2 = null;
				/*ResultSet rs3 = null;*/
				ResultSet rs4 = null;
				ResultSet rs5 = null;
				//使用level标示每个节点在表中的级别
			/*	String sql = "select * from (select  f.*  ,level leaf from plfunction f start with f.plparentid ='modelManagmentNode' connect by  f.plparentid=prior f.ploid)";
				pstmt = connection.prepareStatement(sql);
				rs = pstmt.executeQuery();
				int size=0;
				//计算总的数据条数
				while(rs.next()) {			
					size++;	
					ServerWithLog4j.logger.info(rs.getString(1));
					String sql2 = "select * from plfuncoperation where plfuncoid='"+rs.getString(1)+"'";
					pstmt = connection.prepareStatement(sql2);
					rs3 = pstmt.executeQuery();
					while(rs3.next()) {
						size++;
					}
					pstmt.close();			//此处必须关闭statement,否则会报“超出打开游标的最大数异常”
				}*/
				String[][] bb=new String[size][23];		
				String sql4 = "select * from (select  f.*  ,level leaf from plfunction f start with f.plparentid ='modelManagmentNode' connect by  f.plparentid=prior f.ploid)";
				pstmt = connection.prepareStatement(sql4);
				rs2 = pstmt.executeQuery();
				int count=0;
				while(rs2.next()){
					for(int i=1;i<19;i++){
						if(i>17) {			//超过16列的部分属于操作表中的数据，所以模块部分在这些列中没有数据
							for(int d=17;d<23;d++){
								bb[count][d]="";
							}
						} else {
							/*if(i==1){
								if(rs2.getString(2)==null){
									bb[count][i-1]="";
								} else {
									bb[count][i-1]=rs2.getString(i);
								}
								
							} else {*/
								if(rs2.getString(i)==null){				
									bb[count][i-1]="";
								} else {
									bb[count][i-1]=rs2.getString(i);
								}
							//}	
						}	
					}
					String sql3 = "select * from plfuncoperation where plfuncoid='"+rs2.getString(1)+"'";
					pstmt = connection.prepareStatement(sql3);
					rs4 = pstmt.executeQuery();
					while(rs4.next()) {					//操作部门数据的填充
						count++;						//做判断后，发现带有操作的模块后就把行数+1
						for(int c=0;c<16;c++){
							bb[count][c]="";
						}
						bb[count][16]="-1";
						bb[count][17]=rs4.getString(1);
						bb[count][18]=rs4.getString(2);
						bb[count][19]=rs4.getString(3);
						bb[count][20]=rs4.getString(4);
						bb[count][21]=rs4.getString(5);
						bb[count][22]=rs4.getString(6);
						String sql5 = "select * from ploperation where ploid='"+rs4.getString(3)+"'";
						/*pstmt = connection.prepareStatement(sql5);
						rs5 = pstmt.executeQuery();
						while(rs5.next()) {
							int sign=2;
							for(int j=18;j<23;j++) {
								if(rs5.getString(sign)==null){
									bb[count][j]="";
								} else {
									//bb[count][j]=rs5.getString(sign);
									bb[count][j]="rrrrrrrrrrrrrrrrr";
								}
								
								sign++;
							}
						}*/
					}
					pstmt.close();
					count++;	
				}
				return bb;
			}
		});
	}
	
	
	/**
	 * 查询整个树结构并导出到excel表格中
	 * add by caill start
	 * */
	public String[][] checkLevel(){
		
		return (String[][]) new HibernateTemplate().runExt(new HibernateCallbackExt() {
			
			@Override
			public Object execute(Connection connection) throws HibernateException,
					SQLException {			
				String[] firstLevel=new String[2];
				PreparedStatement pstmt = null;
				ResultSet rs = null;
				ResultSet rs2 = null;
				ResultSet rs3 = null;
				ResultSet rs4 = null;
				ResultSet rs5 = null;
				//使用level标示每个节点在表中的级别
				String sql = "select * from (select  f.*  ,level leaf from plfunction f start with f.plparentid ='modelManagmentNode' connect by  f.plparentid=prior f.ploid)";
				pstmt = connection.prepareStatement(sql);
				rs = pstmt.executeQuery();
				int size=0;
				//计算总的数据条数
				while(rs.next()) {			
					size++;	
					ServerWithLog4j.logger.info(rs.getString(1));
					String sql2 = "select * from plfuncoperation where plfuncoid='"+rs.getString(1)+"'";
					pstmt = connection.prepareStatement(sql2);
					rs3 = pstmt.executeQuery();
					while(rs3.next()) {
						size++;
					}
					pstmt.close();			//此处必须关闭statement,否则会报“超出打开游标的最大数异常”
				}
				String[][] bb=new String[size][23];		
				String sql4 = "select * from (select  f.*  ,level leaf from plfunction f start with f.plparentid ='modelManagmentNode' connect by  f.plparentid=prior f.ploid)";
				pstmt = connection.prepareStatement(sql4);
				rs2 = pstmt.executeQuery();
				int count=0;
				while(rs2.next()){
					for(int i=1;i<17;i++){
						if(i>=16) {			//超过16列的部分属于操作表中的数据，所以模块部分在这些列中没有数据
							for(int d=15;d<23;d++){
								bb[count][d]="";
							}
						} else {
							if(i==1){
								if(rs2.getString(2)==null){
									bb[count][i-1]="";
								} else {
									bb[count][i-1]=rs2.getString(2);
								}
								
							} else {
								if(rs2.getString(i+2)==null){				
									bb[count][i-1]="";
								} else {
									bb[count][i-1]=rs2.getString(i+2);
								}
							}	
						}	
					}
					String sql3 = "select * from plfuncoperation where plfuncoid='"+rs2.getString(1)+"'";
					pstmt = connection.prepareStatement(sql3);
					rs4 = pstmt.executeQuery();
					while(rs4.next()) {					//操作部门数据的填充
						count++;						//做判断后，发现带有操作的模块后就把行数+1
						for(int c=0;c<14;c++){
							bb[count][c]="";
						}
						bb[count][14]="-1";
						bb[count][15]=rs4.getString(5);
						bb[count][16]=rs4.getString(4);
						bb[count][17]=rs4.getString(6);
						String sql5 = "select * from ploperation where ploid='"+rs4.getString(3)+"'";
						pstmt = connection.prepareStatement(sql5);
						rs5 = pstmt.executeQuery();
						while(rs5.next()) {
							int sign=2;
							for(int j=18;j<23;j++) {
								if(rs5.getString(sign)==null){
									bb[count][j]="";
								} else {
									bb[count][j]=rs5.getString(sign);
								}
								
								sign++;
							}
						}
					}
					pstmt.close();
					count++;		
				}
				return bb;
			}
		});
	}
	//add by caill end
	/**
	 * 判断第一级是否相同
	 * add by caill
	 * */
	public boolean firstLevel(final String plName){
		return (Boolean)new HibernateTemplate().runExt(new HibernateCallbackExt() {
			
			@Override
			public Object execute(Connection connection) throws HibernateException,
					SQLException {
				boolean bl=false;
				PreparedStatement pstmt = null;
				ResultSet rs = null;
				//String sql="update plfunction set plresourcec = ? , plsuffixc = ? , plresourceb = ? , plsuffixb = ? , plmoduleno = ? , pldesc = ? , plisvalid = ? , plimage = ? , plmodulesequence = ? , plaliasname = ? , plmodulename = ? , plresourcedotnet = ? , plresourcemobil = ?  where t.ploid = ?";
				String sql = "select * from plfunction where plname='"+plName+"' and plparentid= 'modelManagmentNode'";	
				pstmt = connection.prepareStatement(sql);
				//pstmt.setString(1, plName);			
				rs = pstmt.executeQuery();
				while(rs.next()){
					bl=true;
				}
				rs.close();
				pstmt.close();		
				return bl;
			}
		});
	}
	/**
	 * 覆盖第一级
	 * add by caill
	 * */
	public String changeFirstLevel(final Function function,final String plName){
		return (String)new HibernateTemplate().runExt(new HibernateCallbackExt() {
			
			@Override
			public Object execute(Connection connection) throws HibernateException,
					SQLException {
				String fParentId="";
				PreparedStatement pstmt = null;
				ResultSet rs = null;
				ResultSet rs2 = null;
				String sql2="select * from plfunction where plname='"+plName+"' and plparentid= 'modelManagmentNode'";
				pstmt = connection.prepareStatement(sql2);		
				rs2 = pstmt.executeQuery();
				while(rs2.next()){
					
					ServerWithLog4j.logger.info("*****"+rs2.getString(1));
					fParentId=rs2.getString(1);
				}
				rs2.close();
				String sql="update plfunction set plresourcec = ? , plsuffixc = ? , plresourceb = ? , plsuffixb = ? , pldesc = ? , plisvalid = ? , plimage = ? , plmodulesequence = ? , plaliasname = ? , plresourcedotnet = ? , plresourcemobil = ?  where plname='"+plName+"' and plparentid= 'modelManagmentNode'";		
				
				pstmt = connection.prepareStatement(sql);
				pstmt.setString(1, function.getResourceC());
				pstmt.setString(2, function.getSuffixC());
				pstmt.setString(3, function.getResourceB());
				pstmt.setString(4, function.getSuffixB());
				pstmt.setString(5, function.getDesc());
				pstmt.setInt(6, function.getIsValid() ? 1 : 0);
				pstmt.setString(7, function.getImage());
				pstmt.setInt(8, (int)function.getSequence());
				pstmt.setString(9, function.getAliasName());
				pstmt.setString(10, function.getResourceDotNet());
				pstmt.setString(11, function.getResourceMobile());
				pstmt.executeUpdate();
				//rs.close();
				pstmt.close();		
				return fParentId;
			}
		});
	}
	/**
	 * 判断下一级是否相同
	 * add by caill
	 * */
	public boolean secondLevel(final String plName,final String fParentId){
		return (Boolean)new HibernateTemplate().runExt(new HibernateCallbackExt() {
			
			@Override
			public Object execute(Connection connection) throws HibernateException,
					SQLException {
				boolean bl=false;
				PreparedStatement pstmt = null;
				ResultSet rs = null;
				//String sql="update plfunction set plresourcec = ? , plsuffixc = ? , plresourceb = ? , plsuffixb = ? , plmoduleno = ? , pldesc = ? , plisvalid = ? , plimage = ? , plmodulesequence = ? , plaliasname = ? , plmodulename = ? , plresourcedotnet = ? , plresourcemobil = ?  where t.ploid = ?";
				String sql = "select * from plfunction where plname='"+plName+"' and plparentid= '"+fParentId+"'";	
				pstmt = connection.prepareStatement(sql);
				//pstmt.setString(1, plName);			
				rs = pstmt.executeQuery();
				while(rs.next()){
					bl=true;
				}
				rs.close();
				pstmt.close();		
				return bl;
			}
		});
	}
	/**
	 * 覆盖下一级
	 * add by caill
	 * */
	public String changeSecondLevel(final Function function,final String plName,final String fParentId){
		return (String)new HibernateTemplate().runExt(new HibernateCallbackExt() {
			
			@Override
			public Object execute(Connection connection) throws HibernateException,
					SQLException {
				ServerWithLog4j.logger.info("******"+function.getAliasName());
				String sParentId="";
				PreparedStatement pstmt = null;
				ResultSet rs = null;
				ResultSet rs2 = null;
				String sql2="select * from plfunction where plname='"+plName+"' and plparentid= '"+fParentId+"'";
				pstmt = connection.prepareStatement(sql2);		
				rs2 = pstmt.executeQuery();
				while(rs2.next()){
					ServerWithLog4j.logger.info("*****"+rs2.getString(1));
					sParentId=rs2.getString(1);
				}
				rs2.close();
				String sql="update plfunction set plresourcec = ? , plsuffixc = ? , plresourceb = ? , plsuffixb = ? , pldesc = ? , plisvalid = ? , plimage = ? , plmodulesequence = ? , plaliasname = ? , plresourcedotnet = ? , plresourcemobil = ?  where plname='"+plName+"' and plparentid= '"+fParentId+"'";		
				
				pstmt = connection.prepareStatement(sql);
				pstmt.setString(1, function.getResourceC());
				pstmt.setString(2, function.getSuffixC());
				pstmt.setString(3, function.getResourceB());
				pstmt.setString(4, function.getSuffixB());
				pstmt.setString(5, function.getDesc());
				pstmt.setInt(6, function.getIsValid() ? 1 : 0);
				pstmt.setString(7, function.getImage());
				pstmt.setInt(8, (int)function.getSequence());
				pstmt.setString(9, function.getAliasName());
				pstmt.setString(10, function.getResourceDotNet());
				pstmt.setString(11, function.getResourceMobile());
				pstmt.executeUpdate();
				
				pstmt.close();		
				return sParentId;
			}
		});
	}
	
	 /**
     * oracle in 查询不能超过1000，转换一下集合
     * 由于SQL语句1000个可能很长，超过oracle10g，所以牺牲性能分配为500个数组
     * @param list 需要转换的列表内容
     * @return 分组后的list
     */
    public static <T> List<List<T>> switchListForOracleIn(List<T> list) {
        List<List<T>> listHasList = new ArrayList<List<T>>();
        if(list == null){
            return listHasList;
        }
        List<T> newList = new ArrayList<T>();
        for(Object obj : list){
            //为了让list还可以添加内容，因为使用sublist后，list不能再Add了
            newList.add((T)obj);
        }
        int muti = 1;
        if(newList.size() >500){
            int balance = newList.size()%500;
            muti = (newList.size() - balance)/500 + (balance == 0?0:1);
        }
        for(int i = 0 ; i < muti; i ++){
            int start = i*500;
            int end = start + 500;
            if(i == muti-1 || end >newList.size() ){
                end = newList.size();
            }
            List subList = newList.subList(start,end);
            listHasList.add(subList);
        }
        return listHasList;
    }
    /**
     * 注意s的长度不能超过一千
     * @param s 字符串
     * @return insql
     */
    public static String toInSql(String[] s){
        if(s != null && s.length >0){
            StringBuilder sb = new StringBuilder();
            if(s!=null&&s.length>0){
                for(int i = 0 ; i < s.length ; i ++){
                    if(s[i]!=null&&s[i].trim().length() >0 && !s[i].startsWith("'")) {
                        sb.append("'")
                                .append(s[i]).append("'");
                        if(i != s.length-1)
                        	sb.append(",");
                    }
                }
            }
            return sb.toString();
        }else{
            return "";
        }
    }

	public List<Function> listRootFunctionByOids(List<String> functionOids) {
		List<Function> functions = new ArrayList<Function>();
		List<List<String>> functionOidLists = switchListForOracleIn(functionOids);
		FunctionDaoImpl impl = new FunctionDaoImpl();
		for(List<String> oids : functionOidLists){
			/**检查模块名称是否重复，重复返回1**/
			String hql = "from Function where id in (" + toInSql(oids.toArray(new String[0])) + ") and parentId = ?";
			Object[] values = new Object[1];
			values[0] = "business";
			List<Function> list = impl.findEntites(hql, values);
			if(!CollectionUtils.isEmpty(list)){
				functions.addAll(list);
			}
		}
		return functions;
	}
}