GZ/nwyj/.svn/pristine/8b/8b4e4c8d615fb7b23fad0edc5dc6b7094baa8beb.svn-base

package com.sinosoft.am.resource.expert.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.persistence.service.PersistenceFactory;
import com.persistence.service.SysPersistence;
import com.persistence.service.exception.PersistenceException;
import com.sinosoft.am.resource.expert.vo.ExpertNum;
import com.sysmodel.datamodel.xmlmodel.ModelFactory;
import com.sysmodel.datamodel.xmlmodel.able.SysModel;

public class ExpertDao {

	private Logger	log			= Logger.getLogger(this.getClass());
	SysModel		sysmodel	= ModelFactory.getSysmodel();
	SysPersistence	persistence	= PersistenceFactory.getInstance(sysmodel);
	StringBuffer	json		= new StringBuffer();
	private List<ExpertNum>numList=new ArrayList<ExpertNum>(); 

	

	//
	// var gridData={"rows":[
	// {"value":1,"key":"南京分公司","isParent":true,"open":false},
	// {"value":7,"key":"杭州办事处","isParent":true,"open":false}
	// ]}

	
	/**
	 * 根据传过来的ID进行检测，有子节点且子节点在明细表中有数据的，继续查询该节点下面的数据
	 * @param id
	 * @return
	 */
	public String expertNumInit(String id,String isNwyj,String zhuanye,String deptId){
		String sql="select DEPT_ID from sys_department where (parent_id='"+id+"' or DEPT_ID='"+id+"')";
		try{
			if(deptId!=null && !"null".equals(deptId)){
				sql+=" and DEPT_ID in("+deptId+")";
			}
			sql+="order by sort";
			if(checkId(id)){
			
			List<String[]>tempList= persistence.getSearchResult(99, sql.toString());
			log.info("carNum.tempList.size===="+tempList.size());
			List<String>list=new ArrayList<String>();
			for(int i=0;i<tempList.size();i++){
				list.add(tempList.get(i)[0]);
			}
			getExpertNum(list, list.get(0),isNwyj,zhuanye);
		}
		else{
			List<String>list=new ArrayList<String>();
			list.add(id);
			getNoChildExpertNum(list, id,isNwyj,zhuanye);
		}
		}catch(Exception e){
			this.log.error(e.getMessage(), e);
		}
//		log.info("最终JSON==="+json.toString());
/*		if (json.lastIndexOf(",") > -1) {
			json.deleteCharAt(json.lastIndexOf(","));
		}*/
		ExpertNum numBean = new ExpertNum();
		for(int i=0;i<numList.size();i++){
			numBean.setTran(numBean.getTran()+numList.get(i).getTran());
			numBean.setSub_one(numBean.getSub_one()+numList.get(i).getSub_one());
			numBean.setSub_two(numBean.getSub_two()+numList.get(i).getSub_two());
			numBean.setVoltage_low(numBean.getVoltage_low()+numList.get(i).getVoltage_low());
			numBean.setVoltage_mv(numBean.getVoltage_mv()+numList.get(i).getVoltage_mv());
			numBean.setSignal(numBean.getSignal()+numList.get(i).getSignal());
			numBean.setCivil(numBean.getCivil()+numList.get(i).getCivil());
			numBean.setMechanics(numBean.getMechanics()+numList.get(i).getMechanics());
			numBean.setHydraulic(numBean.getHydraulic()+numList.get(i).getHydraulic());
			numBean.setLogistics(numBean.getLogistics()+numList.get(i).getLogistics());	
			numBean.setMedical(numBean.getMedical()+numList.get(i).getMedical());
			numBean.setOther(numBean.getOther()+numList.get(i).getOther());
			numBean.setNum(numBean.getNum()+numList.get(i).getNum());
		}
		json.append("{	");
		json.append("	\"tran\":\"" + numBean.getTran() + "\",");
		json.append("	\"sub_one\":\"" + numBean.getSub_one() + "\",");
		json.append("	\"sub_two\":\"" + numBean.getSub_two() + "\",");
		json.append("	\"voltage_low\":\"" + numBean.getVoltage_low() + "\",");
		json.append("	\"voltage_mv\":\"" + numBean.getVoltage_mv() + "\",");
		json.append("	\"signal\":\"" + numBean.getSignal() + "\",");
		json.append("	\"civil\":\"" + numBean.getCivil() + "\",");
		json.append("	\"mechanics\":\"" + numBean.getMechanics() + "\",");
		json.append("	\"hydraulic\":\"" + numBean.getHydraulic() + "\",");
		json.append("	\"logistics\":\"" + numBean.getLogistics() + "\",");
		json.append("	\"medical\":\"" + numBean.getMedical() + "\",");
		json.append("	\"other\":\"" + numBean.getOther() + "\",");
		json.append("	\"num\":\"" + numBean.getNum() + "\",");
		json.append("	\"name\":\"" +"总计" + "\",");
		json.append("	\"dept_id\":\"" + "11111" + "\",");
		json.append("	\"isParent\":false,");
		json.append("	\"open\":false");
		json.append("}	");
		String result="{\"rows\":["+json.toString()+"]}";
//		List<String>list=getCmpList(id, new ArrayList<String>());
		log.info("result===="+result);
		return result;
	}
	
	public String expertNum(String id,String isNwyj,String zhuanye){
		try{
			
		if(checkId(id)){
			String sql="select DEPT_ID from sys_department where parent_id='"+id+"' and DEPT_NAME like '%供电局%'";
			List<String[]>tempList= persistence.getSearchResult(99, sql.toString());
			log.info("carNum.tempList.size===="+tempList.size());
			for(int i=0;i<tempList.size();i++){
				List<String>list=getCmpList(tempList.get(i)[0], new ArrayList<String>());
				getExpertNum(list, tempList.get(i)[0],isNwyj,zhuanye);
			}
		}else{
			List<String>list=new ArrayList<String>();
			list.add(id);
			getNoChildExpertNum(list, id,isNwyj,zhuanye);
		}}catch(Exception e){
			this.log.error(e.getMessage(), e);
		}
//		log.info("最终JSON==="+json.toString());
		if (json.lastIndexOf(",") > -1) {
			json.deleteCharAt(json.lastIndexOf(","));
		}
		String result="{\"rows\":["+json.toString()+"]}";
//		List<String>list=getCmpList(id, new ArrayList<String>());
		log.info("result===="+result);
		return result;
	}
	
	public List<String> getCmpList(String id,List<String>list) {
		try{
		if(checkId(id)){//存在子节点且子节点在明细表中有记录
			list.add(id);
			String sql="select distinct(DEPT_ID) from sys_department where parent_id='"+id+"' and DEPT_NAME like '%供电局%' ";
			List<String[]>tempList= persistence.getSearchResult(99, sql.toString());
			log.info("getCmpList.tempList.size===="+tempList.size());
			for(int i=0;i<tempList.size();i++){
				getCmpList(tempList.get(i)[0],list);
			}
			}
		else{//如果不存在自己点或者子节点在明细表中无记录，则返回该ID
			list.add(id);
		}}catch(Exception e){
			this.log.error(e.getMessage(), e);
		}
		return list;
	}
	
	public boolean checkId(String id) {
		try{
		String sql="select * from EMC_AM_EMERGENCY_EXPERT WHERE EXPERT_UNIT IN(SELECT DEPT_ID FROM SYS_DEPARTMENT WHERE PARENT_ID='"+id+"' and DEPT_NAME like '%供电局%' )";
		List<String[]>tempList= persistence.getSearchResult(99, sql.toString());
		if(tempList==null||tempList.size()==0){
			return false;
		}else{
			return true;
		}}catch(Exception e){
			this.log.error(e.getMessage(), e);
			return false;
		}
	}
	
	public  void getExpertNum(List<String>list,String id,String isNwyj,String zhuanye) throws PersistenceException{
		
		try{
			
		for(int i=0;i<list.size();i++){
			ExpertNum numBean = new ExpertNum();
			String sql = "select expert_unit,sum(case EXPERT_SPECIALTY when '1' then 1 else 0 end ) tran,sum(case EXPERT_SPECIALTY when '2' then 1 else 0 end ) sub_one,"
					+ "sum(case EXPERT_SPECIALTY when '3' then 1 else 0 end ) sub_two,sum(case EXPERT_SPECIALTY when '5' then 1 else 0 end ) voltage_low,"
					+ "sum(case EXPERT_SPECIALTY when '6' then 1 else 0 end ) voltage_mv,sum(case EXPERT_SPECIALTY when '4' then 1 else 0 end ) signal,"
					+ "sum(case EXPERT_SPECIALTY when '7' then 1 else 0 end ) civil,sum(case EXPERT_SPECIALTY when '8' then 1 else 0 end ) mechanics,"
					+ "sum(case EXPERT_SPECIALTY when '9' then 1 else 0 end ) hydraulic,sum(case EXPERT_SPECIALTY when '10' then 1 else 0 end ) logistics,"
					+ "sum(case EXPERT_SPECIALTY when '11' then 1 else 0 end ) medical,sum(case EXPERT_SPECIALTY when '12' then 1 else 0 end ) other,"
					+ "count(FD_OBJECTID) num from EMC_AM_EMERGENCY_EXPERT where  is_del='0' and expert_unit='"+list.get(i)+"'";
			
			//是否南网员工
			if(isNwyj!=null && !"null".equals(isNwyj)){
				sql+=" and IS_SNSTAFF ="+isNwyj+"";
			}
			
			//专业
			if(zhuanye!=null && !"null".equals(zhuanye)){
				sql+=" and EXPERT_SPECIALTY in ("+zhuanye+")";
			}
			
			sql+=" group by expert_unit ";
			
			log.info("getExpertNum.sql===="+sql);
			List<String[]>tempList= persistence.getSearchResult(99, sql.toString());
			if(tempList.size()>0){
//				numBean.setTran(numBean.getTran()+Integer.parseInt(numList.get(0)[1]));
//				numBean.setSub_one(numBean.getSub_one()+Integer.parseInt(numList.get(0)[2]));
//				numBean.setSub_two(numBean.getSub_two()+Integer.parseInt(numList.get(0)[3]));
//				numBean.setVoltage_low(numBean.getVoltage_low()+Integer.parseInt(numList.get(0)[4]));
//				numBean.setVoltage_mv(numBean.getVoltage_mv()+Integer.parseInt(numList.get(0)[5]));
//				numBean.setSignal(numBean.getSignal()+Integer.parseInt(numList.get(0)[6]));
//				numBean.setCivil(numBean.getCivil()+Integer.parseInt(numList.get(0)[7]));
//				numBean.setMechanics(numBean.getMechanics()+Integer.parseInt(numList.get(0)[8]));
//				numBean.setHydraulic(numBean.getHydraulic()+Integer.parseInt(numList.get(0)[9]));
//				numBean.setLogistics(numBean.getLogistics()+Integer.parseInt(numList.get(0)[10]));	
//				numBean.setMedical(numBean.getMedical()+Integer.parseInt(numList.get(0)[11]));
//				numBean.setOther(numBean.getOther()+Integer.parseInt(numList.get(0)[12]));
//				numBean.setNum(numBean.getNum()+Integer.parseInt(numList.get(0)[13]));
				
				numBean.setTran(Integer.parseInt(tempList.get(0)[1]));
				numBean.setSub_one(Integer.parseInt(tempList.get(0)[2]));
				numBean.setSub_two(Integer.parseInt(tempList.get(0)[3]));
				numBean.setVoltage_low(Integer.parseInt(tempList.get(0)[4]));
				numBean.setVoltage_mv(Integer.parseInt(tempList.get(0)[5]));
				numBean.setSignal(Integer.parseInt(tempList.get(0)[6]));
				numBean.setCivil(Integer.parseInt(tempList.get(0)[7]));
				numBean.setMechanics(Integer.parseInt(tempList.get(0)[8]));
				numBean.setHydraulic(Integer.parseInt(tempList.get(0)[9]));
				numBean.setLogistics(Integer.parseInt(tempList.get(0)[10]));	
				numBean.setMedical(Integer.parseInt(tempList.get(0)[11]));
				numBean.setOther(Integer.parseInt(tempList.get(0)[12]));
				numBean.setNum(Integer.parseInt(tempList.get(0)[13]));
				numList.add(numBean);
				if(numBean.getNum()>0){
					if(checkId(id)){
						json.append("{	");
						json.append("	\"tran\":\"" + numBean.getTran() + "\",");
						json.append("	\"sub_one\":\"" + numBean.getSub_one() + "\",");
						json.append("	\"sub_two\":\"" + numBean.getSub_two() + "\",");
						json.append("	\"voltage_low\":\"" + numBean.getVoltage_low() + "\",");
						json.append("	\"voltage_mv\":\"" + numBean.getVoltage_mv() + "\",");
						json.append("	\"signal\":\"" + numBean.getSignal() + "\",");
						json.append("	\"civil\":\"" + numBean.getCivil() + "\",");
						json.append("	\"mechanics\":\"" + numBean.getMechanics() + "\",");
						json.append("	\"hydraulic\":\"" + numBean.getHydraulic() + "\",");
						json.append("	\"logistics\":\"" + numBean.getLogistics() + "\",");
						json.append("	\"medical\":\"" + numBean.getMedical() + "\",");
						json.append("	\"other\":\"" + numBean.getOther() + "\",");
						json.append("	\"num\":\"" + numBean.getNum() + "\",");
						json.append("	\"name\":\"" + getNameById(list.get(i)) + "\",");
						json.append("	\"dept_id\":\"" + list.get(i) + "\",");
						json.append("	\"isParent\":true,");
						json.append("	\"open\":false");
						json.append("},	");
					}else{
						json.append("{	");
						json.append("	\"tran\":\"" + numBean.getTran() + "\",");
						json.append("	\"sub_one\":\"" + numBean.getSub_one() + "\",");
						json.append("	\"sub_two\":\"" + numBean.getSub_two() + "\",");
						json.append("	\"voltage_low\":\"" + numBean.getVoltage_low() + "\",");
						json.append("	\"voltage_mv\":\"" + numBean.getVoltage_mv() + "\",");
						json.append("	\"signal\":\"" + numBean.getSignal() + "\",");
						json.append("	\"civil\":\"" + numBean.getCivil() + "\",");
						json.append("	\"mechanics\":\"" + numBean.getMechanics() + "\",");
						json.append("	\"hydraulic\":\"" + numBean.getHydraulic() + "\",");
						json.append("	\"logistics\":\"" + numBean.getLogistics() + "\",");
						json.append("	\"medical\":\"" + numBean.getMedical() + "\",");
						json.append("	\"other\":\"" + numBean.getOther() + "\",");
						json.append("	\"num\":\"" + numBean.getNum() + "\",");
						json.append("	\"name\":\"" + getNameById(list.get(i)) + "\",");
						json.append("	\"dept_id\":\"" + list.get(i) + "\",");
						json.append("	\"isParent\":false,");
						json.append("	\"open\":false");
						json.append("},	");
					}
				
			}
		}}}catch(Exception e){
			this.log.error(e.getMessage(), e);
		}
	}
	
	
	public  void getNoChildExpertNum(List<String>list,String id,String isNwyj,String zhuanye) throws PersistenceException{
		ExpertNum numBean = new ExpertNum();
		try{
		for(int i=0;i<list.size();i++){
			String sql = "select expert_unit,sum(case EXPERT_SPECIALTY when '1' then 1 else 0 end ) tran,sum(case EXPERT_SPECIALTY when '2' then 1 else 0 end ) sub_one,"
					+ "sum(case EXPERT_SPECIALTY when '3' then 1 else 0 end ) sub_two,sum(case EXPERT_SPECIALTY when '5' then 1 else 0 end ) voltage_low,"
					+ "sum(case EXPERT_SPECIALTY when '6' then 1 else 0 end ) voltage_mv,sum(case EXPERT_SPECIALTY when '4' then 1 else 0 end ) signal,"
					+ "sum(case EXPERT_SPECIALTY when '7' then 1 else 0 end ) civil,sum(case EXPERT_SPECIALTY when '8' then 1 else 0 end ) mechanics,"
					+ "sum(case EXPERT_SPECIALTY when '9' then 1 else 0 end ) hydraulic,sum(case EXPERT_SPECIALTY when '10' then 1 else 0 end ) logistics,"
					+ "sum(case EXPERT_SPECIALTY when '11' then 1 else 0 end ) medical,sum(case EXPERT_SPECIALTY when '12' then 1 else 0 end ) other,"
					+ "count(FD_OBJECTID) num from EMC_AM_EMERGENCY_EXPERT where  is_del='0' and expert_unit='"+list.get(i)+"'";
			//是否南网员工
			if(isNwyj!=null && !"null".equals(isNwyj)){
				sql+=" and IS_SNSTAFF ="+isNwyj+"";
			}
			
			//专业
			if(zhuanye!=null && !"null".equals(zhuanye)){
				sql+=" and EXPERT_SPECIALTY in ("+zhuanye+")";
			}
			
			
			sql+=" group by expert_unit ";
			log.info("getNoChildExpertNum.sql===="+sql);
			List<String[]>numList= persistence.getSearchResult(99, sql.toString());
			if(numList.size()>0){
				numBean.setTran(numBean.getTran()+Integer.parseInt(numList.get(0)[1]));
				numBean.setSub_one(numBean.getSub_one()+Integer.parseInt(numList.get(0)[2]));
				numBean.setSub_two(numBean.getSub_two()+Integer.parseInt(numList.get(0)[3]));
				numBean.setVoltage_low(numBean.getVoltage_low()+Integer.parseInt(numList.get(0)[4]));
				numBean.setVoltage_mv(numBean.getVoltage_mv()+Integer.parseInt(numList.get(0)[5]));
				numBean.setSignal(numBean.getSignal()+Integer.parseInt(numList.get(0)[6]));
				numBean.setCivil(numBean.getCivil()+Integer.parseInt(numList.get(0)[7]));
				numBean.setMechanics(numBean.getMechanics()+Integer.parseInt(numList.get(0)[8]));
				numBean.setHydraulic(numBean.getHydraulic()+Integer.parseInt(numList.get(0)[9]));
				numBean.setLogistics(numBean.getLogistics()+Integer.parseInt(numList.get(0)[10]));	
				numBean.setMedical(numBean.getMedical()+Integer.parseInt(numList.get(0)[11]));
				numBean.setOther(numBean.getOther()+Integer.parseInt(numList.get(0)[12]));
				numBean.setNum(numBean.getNum()+Integer.parseInt(numList.get(0)[13]));
				
			}
		}}catch(Exception e){
			this.log.error(e.getMessage(), e);
		}
//		StringBuffer json=new StringBuffer();
		if(numBean.getNum()>0){
			json.append("{	");
			json.append("	\"tran\":\"" + numBean.getTran() + "\",");
			json.append("	\"sub_one\":\"" + numBean.getSub_one() + "\",");
			json.append("	\"sub_two\":\"" + numBean.getSub_two() + "\",");
			json.append("	\"voltage_low\":\"" + numBean.getVoltage_low() + "\",");
			json.append("	\"voltage_mv\":\"" + numBean.getVoltage_mv() + "\",");
			json.append("	\"signal\":\"" + numBean.getSignal() + "\",");
			json.append("	\"civil\":\"" + numBean.getCivil() + "\",");
			json.append("	\"mechanics\":\"" + numBean.getMechanics() + "\",");
			json.append("	\"hydraulic\":\"" + numBean.getHydraulic() + "\",");
			json.append("	\"logistics\":\"" + numBean.getLogistics() + "\",");
			json.append("	\"medical\":\"" + numBean.getMedical() + "\",");
			json.append("	\"other\":\"" + numBean.getOther() + "\",");
			json.append("	\"num\":\"" + numBean.getNum() + "\",");
			json.append("	\"name\":\"" + getNameById(id) + "\",");
			json.append("	\"dept_id\":\"" + id + "\",");
			json.append("	\"isParent\":false,");
			json.append("	\"open\":true");
			json.append("},	");
			numList.add(numBean);
		}
	}
	
	public String getNameById(String id){
		String sql="select DEPT_NAME from sys_department where DEPT_ID='"+id+"'";
		try {
			List<String[]>tempList= persistence.getSearchResult(99, sql.toString());
			if(tempList==null||tempList.size()==0){
				return "";
			}else{
				String name=tempList.get(0)[0].replace("供电局", "").replace("电网有限责任公司", "电网");
				log.info("name===="+name);
				return name;
			}
		} catch (PersistenceException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "";
		}
	}

}