GZ/nwyj/.svn/pristine/8f/8fbf83bc0744d57f1d4ec0b744765e57810c618f.svn-base

package com.sinosoft.am.resource.resources.external.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.resources.external.vo.ExternalNum;
import com.sysmodel.datamodel.xmlmodel.ModelFactory;
import com.sysmodel.datamodel.xmlmodel.able.SysModel;

public class ExternalDao {

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

	
	/**
	 * 根据传过来的ID进行检测，有子节点且子节点在明细表中有数据的，继续查询该节点下面的数据
	 * @param id
	 * @return
	 */
	public String externalNumInit(String id,String typevalue,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+") ";
			}
		if(checkId(id)){
			List<String[]>tempList= persistence.getSearchResult(99, sql.toString());
			log.info("Num.tempList.size===="+tempList.size());
			List<String>list=new ArrayList<String>();
			for(int i=0;i<tempList.size();i++){
				list.add(tempList.get(i)[0]);
			}
			getExternalNum(list, list.get(0),typevalue);

		}else{
			List<String>list=new ArrayList<String>();
			list.add(id);
			getNoChildExternalNum(list, id,typevalue);
		}}catch(Exception e){
			this.log.error(e.getMessage(), e);
		}
//		log.info("最终JSON==="+json.toString()); 统计栏
		/*if (json.lastIndexOf(",") > -1) {
			json.deleteCharAt(json.lastIndexOf(","));
		}*/
		ExternalNum numBean=new ExternalNum();
		for(int i=0;i<numList.size();i++){

			numBean.setGRFHZB(numBean.getGRFHZB()+numList.get(i).getGRFHZB());
			numBean.setSMJZZB(numBean.getSMJZZB()+numList.get(i).getSMJZZB());
			numBean.setLSSSZB(numBean.getLSSSZB()+numList.get(i).getLSSSZB());
			numBean.setFDZB(numBean.getFDZB()+numList.get(i).getFDZB());
			numBean.setZMZB(numBean.getZMZB()+numList.get(i).getZMZB());
			numBean.setTXZB(numBean.getTXZB()+numList.get(i).getTXZB());
			numBean.setJTYSZB(numBean.getJTYSZB()+numList.get(i).getJTYSZB());
			numBean.setGCZB(numBean.getGCZB()+numList.get(i).getGCZB());
			numBean.setWRQLZB(numBean.getWRQLZB()+numList.get(i).getWRQLZB());
			numBean.setGCCL(numBean.getGCCL()+numList.get(i).getGCCL());		
			numBean.setDIAOCHE(numBean.getDIAOCHE()+numList.get(i).getDIAOCHE());
			numBean.setCHACHE(numBean.getCHACHE()+numList.get(i).getCHACHE());
			numBean.setWJJ(numBean.getWJJ()+numList.get(i).getWJJ());
			numBean.setHUOCHE(numBean.getHUOCHE()+numList.get(i).getHUOCHE());			
			numBean.setTTJ(numBean.getTTJ()+numList.get(i).getTTJ());
			numBean.setNum(numBean.getNum()+numList.get(i).getNum());
		}
		json.append("{	");
		json.append("	\"GRFHZB\":\"" + numBean.getGRFHZB() + "\",");
		json.append("	\"SMJZZB\":\"" + numBean.getSMJZZB() + "\",");
		json.append("	\"LSSSZB\":\"" + numBean.getLSSSZB() + "\",");
		json.append("	\"FDZB\":\"" + numBean.getFDZB() + "\",");
		json.append("	\"ZMZB\":\"" + numBean.getZMZB() + "\",");
		json.append("	\"TXZB\":\"" + numBean.getTXZB() + "\",");
		json.append("	\"JTYSZB\":\"" + numBean.getJTYSZB() + "\",");
		json.append("	\"GCZB\":\"" + numBean.getGCZB() + "\",");
		json.append("	\"WRQLZB\":\"" + numBean.getWRQLZB() + "\",");
		json.append("	\"GCCL\":\"" + numBean.getGCCL() + "\",");
		json.append("	\"DIAOCHE\":\"" + numBean.getDIAOCHE() + "\",");
		json.append("	\"CHACHE\":\"" + numBean.getCHACHE() + "\",");
		json.append("	\"WJJ\":\"" + numBean.getWJJ() + "\",");
		json.append("	\"HUOCHE\":\"" + numBean.getHUOCHE() + "\",");
		json.append("	\"TTJ\":\"" + numBean.getTTJ() + "\",");		
		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 externalNum(String id,String typevalue){
		try{
		if(checkId(id)){
			String sql="select DEPT_ID from sys_department where parent_id='"+id+"'";
			List<String[]>tempList= persistence.getSearchResult(99, sql.toString());
			log.info("externalNum.tempList.size===="+tempList.size());
			for(int i=0;i<tempList.size();i++){
				List<String>list=getCmpList(tempList.get(i)[0], new ArrayList<String>());
				getExternalNum(list, tempList.get(i)[0],typevalue);
			}
		}else{
			List<String>list=new ArrayList<String>();
			list.add(id);
			getNoChildExternalNum(list, id,typevalue);
		}}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 DEPT_ID from sys_department where parent_id='"+id+"'";
			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_EXTEMAL_RESOURCES WHERE SUB_UNIT IN(SELECT DEPT_ID FROM SYS_DEPARTMENT WHERE PARENT_ID='"+id+"')";
		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 getExternalNum(List<String>list,String id ,String typevalue) throws PersistenceException{
	
		try{
		for(int i=0;i<list.size();i++){
			ExternalNum cn=new ExternalNum();
			String sql ="select sub_unit , sum(case resource_type when '1' then num else 0 end) as GRFHZB, sum(case resource_type when '2' then num else 0 end) as SMJZZB,"
					+ "sum(case resource_type when '3' then num else 0 end) as LSSSZB,sum(case resource_type when '4' then num else 0 end) as FDZB,"
					+ "sum(case resource_type when '5' then num else 0 end) as ZMZB,sum(case resource_type when '6' then num else 0 end) as TXZB,"
					+ "sum(case resource_type when '7' then num else 0 end) as JTYSZB,sum(case resource_type when '8' then num else 0 end) as GCZB,"
					+ "sum(case resource_type when '9' then num else 0 end) as WRQLZB,sum(case resource_type when '10' then num else 0 end) as GCCL,"
					+ "sum(case resource_type when '11' then num else 0 end) as DIAOCHE,sum(case resource_type when '12' then num else 0 end) as CHACHE,"
					+ "sum(case resource_type when '13' then num else 0 end) as WJJ,sum(case resource_type when '14' then num else 0 end) as HUOCHE,sum(case resource_type when '15' then num else 0 end) as TTJ,"
					+ "sum(num) as num from EMC_AM_EXTEMAL_RESOURCES where is_del='0' and sub_unit='"+list.get(i)+"'";
			
			//专业
			if(typevalue!=null&&!"null".equals(typevalue)&&!"".equals(typevalue)){
				sql+=" and resource_type in ("+typevalue+")";
			}
			
			sql+=" group by sub_unit ";
			
			log.info("getExternalNum.sql===="+sql);
			List<String[]>tempList= persistence.getSearchResult(99, sql.toString());
			if(tempList.size()>0){				
				cn.setGRFHZB(Integer.parseInt(tempList.get(0)[1]));
				cn.setSMJZZB(Integer.parseInt(tempList.get(0)[2]));
				cn.setLSSSZB(Integer.parseInt(tempList.get(0)[3]));
				cn.setFDZB(Integer.parseInt(tempList.get(0)[4]));
				cn.setZMZB(Integer.parseInt(tempList.get(0)[5]));
				cn.setTXZB(Integer.parseInt(tempList.get(0)[6]));
				cn.setJTYSZB(Integer.parseInt(tempList.get(0)[7]));
				cn.setGCZB(Integer.parseInt(tempList.get(0)[8]));
				cn.setWRQLZB(Integer.parseInt(tempList.get(0)[9]));
				cn.setGCCL(Integer.parseInt(tempList.get(0)[10]));		
				cn.setDIAOCHE(Integer.parseInt(tempList.get(0)[11]));
				cn.setCHACHE(Integer.parseInt(tempList.get(0)[12]));
				cn.setWJJ(Integer.parseInt(tempList.get(0)[13]));
				cn.setHUOCHE(Integer.parseInt(tempList.get(0)[14]));			
				cn.setTTJ(Integer.parseInt(tempList.get(0)[15]));				
				cn.setNum(Integer.parseInt(tempList.get(0)[16]));
				numList.add(cn);
				
				if(cn.getNum()>0){
					if(checkId(id)){
						json.append("{	");				
						json.append("	\"GRFHZB\":\"" + cn.getGRFHZB() + "\",");
						json.append("	\"SMJZZB\":\"" + cn.getSMJZZB() + "\",");
						json.append("	\"LSSSZB\":\"" + cn.getLSSSZB() + "\",");
						json.append("	\"FDZB\":\"" + cn.getFDZB() + "\",");
						json.append("	\"ZMZB\":\"" + cn.getZMZB() + "\",");
						json.append("	\"TXZB\":\"" + cn.getTXZB() + "\",");
						json.append("	\"JTYSZB\":\"" + cn.getJTYSZB() + "\",");
						json.append("	\"GCZB\":\"" + cn.getGCZB() + "\",");
						json.append("	\"WRQLZB\":\"" + cn.getWRQLZB() + "\",");
						json.append("	\"GCCL\":\"" + cn.getGCCL() + "\",");
						json.append("	\"DIAOCHE\":\"" + cn.getDIAOCHE() + "\",");
						json.append("	\"CHACHE\":\"" + cn.getCHACHE() + "\",");
						json.append("	\"WJJ\":\"" + cn.getWJJ() + "\",");
						json.append("	\"HUOCHE\":\"" + cn.getHUOCHE() + "\",");
						json.append("	\"TTJ\":\"" + cn.getTTJ() + "\",");
						json.append("	\"NUM\":\"" + cn.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("	\"GRFHZB\":\"" + cn.getGRFHZB() + "\",");
						json.append("	\"SMJZZB\":\"" + cn.getSMJZZB() + "\",");
						json.append("	\"LSSSZB\":\"" + cn.getLSSSZB() + "\",");
						json.append("	\"FDZB\":\"" + cn.getFDZB() + "\",");
						json.append("	\"ZMZB\":\"" + cn.getZMZB() + "\",");
						json.append("	\"TXZB\":\"" + cn.getTXZB() + "\",");
						json.append("	\"JTYSZB\":\"" + cn.getJTYSZB() + "\",");
						json.append("	\"GCZB\":\"" + cn.getGCZB() + "\",");
						json.append("	\"WRQLZB\":\"" + cn.getWRQLZB() + "\",");
						json.append("	\"GCCL\":\"" + cn.getGCCL() + "\",");
						json.append("	\"DIAOCHE\":\"" + cn.getDIAOCHE() + "\",");
						json.append("	\"CHACHE\":\"" + cn.getCHACHE() + "\",");
						json.append("	\"WJJ\":\"" + cn.getWJJ() + "\",");
						json.append("	\"HUOCHE\":\"" + cn.getHUOCHE() + "\",");
						json.append("	\"TTJ\":\"" + cn.getTTJ() + "\",");				
						json.append("	\"NUM\":\"" + cn.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 getNoChildExternalNum(List<String>list,String id,String typevalue) throws PersistenceException{
		ExternalNum cn=new ExternalNum();
		try{
		for(int i=0;i<list.size();i++){
			String sql ="select sub_unit , sum(case resource_type when '1' then num else 0 end) as GRFHZB, sum(case resource_type when '2' then num else 0 end) as SMJZZB,"
					+ "sum(case resource_type when '3' then num else 0 end) as LSSSZB,sum(case resource_type when '4' then num else 0 end) as FDZB,"
					+ "sum(case resource_type when '5' then num else 0 end) as ZMZB,sum(case resource_type when '6' then num else 0 end) as TXZB,"
					+ "sum(case resource_type when '7' then num else 0 end) as JTYSZB,sum(case resource_type when '8' then num else 0 end) as GCZB,"
					+ "sum(case resource_type when '9' then num else 0 end) as WRQLZB,sum(case resource_type when '10' then num else 0 end) as GCCL,"
					+ "sum(case resource_type when '11' then num else 0 end) as DIAOCHE,sum(case resource_type when '12' then num else 0 end) as CHACHE,"
					+ "sum(case resource_type when '13' then num else 0 end) as WJJ,sum(case resource_type when '14' then num else 0 end) as HUOCHE,sum(case resource_type when '15' then num else 0 end) as TTJ,"
					+ "sum(num) as num from EMC_AM_EXTEMAL_RESOURCES where is_del='0' and sub_unit='"+list.get(i)+"'";
			
			//专业
			if(typevalue!=null && !"null".equals(typevalue)&&!"".equals(typevalue)){
				sql+=" and resource_type in ("+typevalue+")";
			}
			
			sql+=" group by sub_unit ";
			
			log.info("getNoChildExternalNum.sql===="+sql);
			List<String[]>numList= persistence.getSearchResult(99, sql.toString());
			if(numList.size()>0){			
				cn.setGRFHZB(cn.getGRFHZB()+Integer.parseInt(numList.get(0)[1]));
				cn.setSMJZZB(cn.getSMJZZB()+Integer.parseInt(numList.get(0)[2]));
				cn.setLSSSZB(cn.getLSSSZB()+Integer.parseInt(numList.get(0)[3]));
				cn.setFDZB(cn.getFDZB()+Integer.parseInt(numList.get(0)[4]));
				cn.setZMZB(cn.getZMZB()+Integer.parseInt(numList.get(0)[5]));
				cn.setTXZB(cn.getTXZB()+Integer.parseInt(numList.get(0)[6]));
				cn.setJTYSZB(cn.getJTYSZB()+Integer.parseInt(numList.get(0)[7]));
				cn.setGCZB(cn.getGCZB()+Integer.parseInt(numList.get(0)[8]));
				cn.setWRQLZB(cn.getWRQLZB()+Integer.parseInt(numList.get(0)[9]));
				cn.setGCCL(cn.getGCCL()+Integer.parseInt(numList.get(0)[10]));		
				cn.setDIAOCHE(cn.getDIAOCHE()+Integer.parseInt(numList.get(0)[11]));
				cn.setCHACHE(cn.getCHACHE()+Integer.parseInt(numList.get(0)[12]));
				cn.setWJJ(cn.getWJJ()+Integer.parseInt(numList.get(0)[13]));
				cn.setHUOCHE(cn.getHUOCHE()+Integer.parseInt(numList.get(0)[14]));			
				cn.setTTJ(cn.getTTJ()+Integer.parseInt(numList.get(0)[15]));				
				cn.setNum(cn.getNum()+Integer.parseInt(numList.get(0)[16]));
			}
		}}catch(Exception e){
			this.log.error(e.getMessage(), e);
		}
		if(cn.getNum()>0){
			json.append("{	");
			json.append("	\"GRFHZB\":\"" + cn.getGRFHZB() + "\",");
			json.append("	\"SMJZZB\":\"" + cn.getSMJZZB() + "\",");
			json.append("	\"LSSSZB\":\"" + cn.getLSSSZB() + "\",");
			json.append("	\"FDZB\":\"" + cn.getFDZB() + "\",");
			json.append("	\"ZMZB\":\"" + cn.getZMZB() + "\",");
			json.append("	\"TXZB\":\"" + cn.getTXZB() + "\",");
			json.append("	\"JTYSZB\":\"" + cn.getJTYSZB() + "\",");
			json.append("	\"GCZB\":\"" + cn.getGCZB() + "\",");
			json.append("	\"WRQLZB\":\"" + cn.getWRQLZB() + "\",");
			json.append("	\"GCCL\":\"" + cn.getGCCL() + "\",");
			json.append("	\"DIAOCHE\":\"" + cn.getDIAOCHE() + "\",");
			json.append("	\"CHACHE\":\"" + cn.getCHACHE() + "\",");
			json.append("	\"WJJ\":\"" + cn.getWJJ() + "\",");
			json.append("	\"HUOCHE\":\"" + cn.getHUOCHE() + "\",");
			json.append("	\"TTJ\":\"" + cn.getTTJ() + "\",");			
			json.append("	\"NUM\":\"" + cn.getNum() + "\",");
			json.append("	\"name\":\"" + getNameById(id) + "\",");
			json.append("	\"dept_id\":\"" + id + "\",");
			json.append("	\"isParent\":false,");
			json.append("	\"open\":true");
			json.append("},	");
			numList.add(cn);
		}
	}
	
	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 "";
		}
	}
}