package com.sinosoft.am.team.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.team.vo.TeamNum;
import com.sysmodel.datamodel.xmlmodel.ModelFactory;
import com.sysmodel.datamodel.xmlmodel.able.SysModel;

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

	

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

	
	/**
	 * 根据传过来的ID进行检测，有子节点且子节点在明细表中有数据的，继续查询该节点下面的数据
	 * @param id
	 * @return
	 */
	public String teamNumInit(String id){
		try{
		if(checkId(id)){
			String sql="select id from sys_department where parent_id='"+id+"' or id='"+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]);
			}
			getTeamNum(list, list.get(0));
		}else{
			List<String>list=new ArrayList<String>();
			list.add(id);
			getNoChildTeamNum(list, id);
		}}catch(Exception e){
			this.log.error(e.getMessage(), e);
		}
//		log.info("最终JSON==="+json.toString());
		/*if (json.lastIndexOf(",") > -1) {
			json.deleteCharAt(json.lastIndexOf(","));
		}*/
		
		TeamNum numBean = new TeamNum();
		for(int i=0;i<numList.size();i++){
			numBean.setTeamCount(numBean.getTeamCount()+numList.get(i).getTeamCount());
			numBean.setTeamMember(numBean.getTeamMember()+numList.get(i).getTeamMember());
			numBean.setSdXianLu(numBean.getSdXianLu()+numList.get(i).getSdXianLu());
			numBean.setSdXianLang(numBean.getSdXianLang()+numList.get(i).getSdXianLang());
			numBean.setSdSum(numBean.getSdSum()+numList.get(i).getSdSum());
			numBean.setBdYiCi(numBean.getBdYiCi()+numList.get(i).getBdYiCi());
			numBean.setBdErCi(numBean.getBdErCi()+numList.get(i).getBdErCi());
			numBean.setBdSum(numBean.getBdSum()+numList.get(i).getBdSum());
			numBean.setPdXianLu(numBean.getPdXianLu()+numList.get(i).getPdXianLu());
			numBean.setPdXianLang(numBean.getPdXianLang()+numList.get(i).getPdXianLang());
			numBean.setPdDianFang(numBean.getPdDianFang()+numList.get(i).getPdDianFang());
			numBean.setPdBaoDian(numBean.getPdBaoDian()+numList.get(i).getPdBaoDian());
			numBean.setPdSum(numBean.getPdSum()+numList.get(i).getPdSum());
			numBean.setTongxing(numBean.getTongxing()+numList.get(i).getTongxing());
			numBean.setWangluo(numBean.getWangluo()+numList.get(i).getWangluo());
			numBean.setShiyan(numBean.getShiyan()+numList.get(i).getShiyan());
			numBean.setKused(numBean.getKused()+numList.get(i).getKused());
			numBean.setBused(numBean.getBused()+numList.get(i).getBused());
		}
		json.append("{	");
		json.append("	\"team_count\":\"" + numBean.getTeamCount() + "\",");
		json.append("	\"team_member\":\"" + numBean.getTeamMember() + "\",");
		json.append("	\"sd_xianlu\":\"" + numBean.getSdXianLu() + "\",");
		json.append("	\"sd_xianlang\":\"" + numBean.getSdXianLang() + "\",");
		json.append("	\"sd_sum\":\"" + numBean.getSdSum() + "\",");
		json.append("	\"bd_yici\":\"" + numBean.getBdYiCi() + "\",");
		json.append("	\"bd_erci\":\"" + numBean.getBdErCi() + "\",");
		json.append("	\"bd_sum\":\"" + numBean.getBdSum() + "\",");
		json.append("	\"pd_xianlu\":\"" + numBean.getPdXianLu() + "\",");
		json.append("	\"pd_xianlang\":\"" + numBean.getPdXianLang() + "\",");
		json.append("	\"pd_dianfang\":\"" + numBean.getPdDianFang() + "\",");
		json.append("	\"pd_baodian\":\"" + numBean.getPdBaoDian() + "\",");
		json.append("	\"pd_sum\":\"" + numBean.getPdSum() + "\",");
		json.append("	\"tongxing\":\"" + numBean.getTongxing() + "\",");
		json.append("	\"wangluo\":\"" + numBean.getWangluo() + "\",");
		json.append("	\"shiyan\":\"" + numBean.getShiyan() + "\",");
		json.append("	\"kused\":\"" + numBean.getKused() + "\",");
		json.append("	\"bused\":\"" + numBean.getBused() + "\",");
		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 teamNum(String id){
		try{
		if(checkId(id)){
			String sql="select id from sys_department_0827 where parent_id='"+id+"'";
			List<String[]>tempList= persistence.getSearchResult(99, sql.toString());
			log.info("teamNum.tempList.size===="+tempList.size());
			for(int i=0;i<tempList.size();i++){
				List<String>list=getCmpList(tempList.get(i)[0], new ArrayList<String>());
				getTeamNum(list, tempList.get(i)[0]);
			}
		}else{
			List<String>list=new ArrayList<String>();
			list.add(id);
			getNoChildTeamNum(list, id);
		}}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 id from sys_department_0827 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 ECM_AM_EMERGENCY_TEAM WHERE TEAM_COMP_ID IN(SELECT ID FROM SYS_DEPARTMENT_0827 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 getTeamNum(List<String>list,String id) throws PersistenceException{
		
		try{
		for(int i=0;i<list.size();i++){
			TeamNum tn = new TeamNum();
			String sql="select TEAM_COMP_ID,count(team_name) as team_count,sum(TEAM_MEMBER_NUMBER) as team_member, sum(SD_XIANLU) as sd_xianlu,sum(SD_XIANLANG) as sd_xianlang ,"
					+ "sum(SD_XIANLANG+SD_XIANLU) as sd_sum, sum(BD_YICI) as bd_yici,sum(BD_ERCI) as bd_erci,sum(BD_YICI+BD_ERCI) as bd_sum,sum(PD_XIANLU) as pd_xianlu,sum(PD_XIANLANG)  as pd_xianlang, "
					+ "sum(PD_DIANFANG) as pd_dianfang,sum(PD_BAODIAN) as pd_baodian,sum(PD_BAODIAN+PD_DIANFANG+PD_XIANLU+PD_XIANLANG) as pd_sum ,sum(TONGXING) as tongxing,sum(WANGLUO) as wangluo ,"
					+ "sum(SHIYAN) as shiyan,sum(case when  TEAM_STAT='1' or TEAM_STAT='3' then 1 else 0 end)as kdy,sum(case when  TEAM_STAT='2' or TEAM_STAT='4' then 1 else 0 end)as bkdy from ecm_am_emergency_team where is_del='0' and TEAM_SYSTEM='123' and TEAM_COMP_ID='"+list.get(i)+"' group by TEAM_COMP_ID";
			log.info("getTeamNum.sql===="+sql);
			List<String[]>tempList= persistence.getSearchResult(99, sql.toString());
			if(tempList.size()>0){
				tn.setTeamCount(Integer.parseInt(tempList.get(0)[1]));
				tn.setTeamMember(Integer.parseInt(tempList.get(0)[2]));
				tn.setSdXianLu(Integer.parseInt(tempList.get(0)[3]));
				tn.setSdXianLang(Integer.parseInt(tempList.get(0)[4]));
				tn.setSdSum(Integer.parseInt(tempList.get(0)[5]));
				tn.setBdYiCi(Integer.parseInt(tempList.get(0)[6]));
				tn.setBdErCi(Integer.parseInt(tempList.get(0)[7]));
				tn.setBdSum(Integer.parseInt(tempList.get(0)[8]));
				tn.setPdXianLu(Integer.parseInt(tempList.get(0)[9]));
				tn.setPdXianLang(Integer.parseInt(tempList.get(0)[10]));
				tn.setPdDianFang(Integer.parseInt(tempList.get(0)[11]));
				tn.setPdBaoDian(Integer.parseInt(tempList.get(0)[12]));
				tn.setPdSum(Integer.parseInt(tempList.get(0)[13]));
				tn.setTongxing(Integer.parseInt(tempList.get(0)[14]));
				tn.setWangluo(Integer.parseInt(tempList.get(0)[15]));
				tn.setShiyan(Integer.parseInt(tempList.get(0)[16]));
				tn.setKused(Integer.parseInt(tempList.get(0)[17]));
				tn.setBused(Integer.parseInt(tempList.get(0)[18]));
				numList.add(tn);
				
				if(tn.getTeamCount()>0){
					if(checkId(id)){
						json.append("{	");
						json.append("	\"team_count\":\"" + tn.getTeamCount() + "\",");
						json.append("	\"team_member\":\"" + tn.getTeamMember() + "\",");
						json.append("	\"sd_xianlu\":\"" + tn.getSdXianLu() + "\",");
						json.append("	\"sd_xianlang\":\"" + tn.getSdXianLang() + "\",");
						json.append("	\"sd_sum\":\"" + tn.getSdSum() + "\",");
						json.append("	\"bd_yici\":\"" + tn.getBdYiCi() + "\",");
						json.append("	\"bd_erci\":\"" + tn.getBdErCi() + "\",");
						json.append("	\"bd_sum\":\"" + tn.getBdSum() + "\",");
						json.append("	\"pd_xianlu\":\"" + tn.getPdXianLu() + "\",");
						json.append("	\"pd_xianlang\":\"" + tn.getPdXianLang() + "\",");
						json.append("	\"pd_dianfang\":\"" + tn.getPdDianFang() + "\",");
						json.append("	\"pd_baodian\":\"" + tn.getPdBaoDian() + "\",");
						json.append("	\"pd_sum\":\"" + tn.getPdSum() + "\",");
						json.append("	\"tongxing\":\"" + tn.getTongxing() + "\",");
						json.append("	\"wangluo\":\"" + tn.getWangluo() + "\",");
						json.append("	\"shiyan\":\"" + tn.getShiyan() + "\",");
						json.append("	\"kused\":\"" + tn.getKused() + "\",");
						json.append("	\"bused\":\"" + tn.getBused() + "\",");
						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("	\"team_count\":\"" + tn.getTeamCount() + "\",");
						json.append("	\"team_member\":\"" + tn.getTeamMember() + "\",");
						json.append("	\"sd_xianlu\":\"" + tn.getSdXianLu() + "\",");
						json.append("	\"sd_xianlang\":\"" + tn.getSdXianLang() + "\",");
						json.append("	\"sd_sum\":\"" + tn.getSdSum() + "\",");
						json.append("	\"bd_yici\":\"" + tn.getBdYiCi() + "\",");
						json.append("	\"bd_erci\":\"" + tn.getBdErCi() + "\",");
						json.append("	\"bd_sum\":\"" + tn.getBdSum() + "\",");
						json.append("	\"pd_xianlu\":\"" + tn.getPdXianLu() + "\",");
						json.append("	\"pd_xianlang\":\"" + tn.getPdXianLang() + "\",");
						json.append("	\"pd_dianfang\":\"" + tn.getPdDianFang() + "\",");
						json.append("	\"pd_baodian\":\"" + tn.getPdBaoDian() + "\",");
						json.append("	\"pd_sum\":\"" + tn.getPdSum() + "\",");
						json.append("	\"tongxing\":\"" + tn.getTongxing() + "\",");
						json.append("	\"wangluo\":\"" + tn.getWangluo() + "\",");
						json.append("	\"shiyan\":\"" + tn.getShiyan() + "\",");
						json.append("	\"kused\":\"" + tn.getKused() + "\",");
						json.append("	\"bused\":\"" + tn.getBused() + "\",");
						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 getNoChildTeamNum(List<String>list,String id) throws PersistenceException{
		TeamNum tn = new TeamNum();
		try{
		for(int i=0;i<list.size();i++){
			String sql="select TEAM_COMP_ID,count(team_name) as team_count,sum(TEAM_MEMBER_NUMBER) as team_member, sum(SD_XIANLU) as sd_xianlu,sum(SD_XIANLANG) as sd_xianlang ,"
					+ "sum(SD_XIANLANG+SD_XIANLU) as sd_sum, sum(BD_YICI) as bd_yici,sum(BD_ERCI) as bd_erci,sum(BD_YICI+BD_ERCI) as bd_sum,sum(PD_XIANLU) as pd_xianlu,sum(PD_XIANLANG)  as pd_xianlang, "
					+ "sum(PD_DIANFANG) as pd_dianfang,sum(PD_BAODIAN) as pd_baodian,sum(PD_BAODIAN+PD_DIANFANG+PD_XIANLU+PD_XIANLANG) as pd_sum ,sum(TONGXING) as tongxing,sum(WANGLUO) as wangluo ,"
					+ "sum(SHIYAN) as shiyan,sum(case when  TEAM_STAT='1' or TEAM_STAT='3' then 1 else 0 end)as kdy,sum(case when  TEAM_STAT='2' or TEAM_STAT='4' then 1 else 0 end)as bkdy from ecm_am_emergency_team where is_del='0' and TEAM_SYSTEM='123' and TEAM_COMP_ID='"+list.get(i)+"' group by TEAM_COMP_ID";
			log.info("getNoChildTeamNum.sql===="+sql);
			List<String[]>numList= persistence.getSearchResult(99, sql.toString());
			if(numList.size()>0){
				tn.setTeamCount(tn.getTeamCount()+Integer.parseInt(numList.get(0)[1]));
				tn.setTeamMember(tn.getTeamMember()+Integer.parseInt(numList.get(0)[2]));
				tn.setSdXianLu(tn.getSdXianLu()+Integer.parseInt(numList.get(0)[3]));
				tn.setSdXianLang(tn.getSdXianLang()+Integer.parseInt(numList.get(0)[4]));
				tn.setSdSum(tn.getSdSum()+Integer.parseInt(numList.get(0)[5]));
				tn.setBdYiCi(tn.getBdYiCi()+Integer.parseInt(numList.get(0)[6]));
				tn.setBdErCi(tn.getBdErCi()+Integer.parseInt(numList.get(0)[7]));
				tn.setBdSum(tn.getBdSum()+Integer.parseInt(numList.get(0)[8]));
				tn.setPdXianLu(tn.getPdXianLu()+Integer.parseInt(numList.get(0)[9]));
				tn.setPdXianLang(tn.getPdXianLang()+Integer.parseInt(numList.get(0)[10]));
				tn.setPdDianFang(tn.getPdDianFang()+Integer.parseInt(numList.get(0)[11]));
				tn.setPdBaoDian(tn.getPdBaoDian()+Integer.parseInt(numList.get(0)[12]));
				tn.setPdSum(tn.getPdSum()+Integer.parseInt(numList.get(0)[13]));
				tn.setTongxing(tn.getTongxing()+Integer.parseInt(numList.get(0)[14]));
				tn.setWangluo(tn.getWangluo()+Integer.parseInt(numList.get(0)[15]));
				tn.setShiyan(tn.getShiyan()+Integer.parseInt(numList.get(0)[16]));
				tn.setKused(tn.getKused()+Integer.parseInt(numList.get(0)[17]));
				tn.setBused(tn.getBused()+Integer.parseInt(numList.get(0)[18]));
			}
		}}catch(Exception e){
			this.log.error(e.getMessage(), e);
		}
//		StringBuffer json=new StringBuffer();
		if(tn.getTeamCount()>0){
			json.append("{	");
			json.append("	\"team_count\":\"" + tn.getTeamCount() + "\",");
			json.append("	\"team_member\":\"" + tn.getTeamMember() + "\",");
			json.append("	\"sd_xianlu\":\"" + tn.getSdXianLu() + "\",");
			json.append("	\"sd_xianlang\":\"" + tn.getSdXianLang() + "\",");
			json.append("	\"sd_sum\":\"" + tn.getSdSum() + "\",");
			json.append("	\"bd_yici\":\"" + tn.getBdYiCi() + "\",");
			json.append("	\"bd_erci\":\"" + tn.getBdErCi() + "\",");
			json.append("	\"bd_sum\":\"" + tn.getBdSum() + "\",");
			json.append("	\"pd_xianlu\":\"" + tn.getPdXianLu() + "\",");
			json.append("	\"pd_xianlang\":\"" + tn.getPdXianLang() + "\",");
			json.append("	\"pd_dianfang\":\"" + tn.getPdDianFang() + "\",");
			json.append("	\"pd_baodian\":\"" + tn.getPdBaoDian() + "\",");
			json.append("	\"pd_sum\":\"" + tn.getPdSum() + "\",");
			json.append("	\"tongxing\":\"" + tn.getTongxing() + "\",");
			json.append("	\"wangluo\":\"" + tn.getWangluo() + "\",");
			json.append("	\"shiyan\":\"" + tn.getShiyan() + "\",");
			json.append("	\"kused\":\"" + tn.getKused() + "\",");
			json.append("	\"bused\":\"" + tn.getBused() + "\",");
			json.append("	\"name\":\"" + getNameById(id) + "\",");
			json.append("	\"dept_id\":\"" + id + "\",");
			json.append("	\"isParent\":false,");
			json.append("	\"open\":true");
			json.append("},	");
			numList.add(tn);
		}
	}
	
	public String getNameById(String id){
		String sql="select name from sys_department_0827 where 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];
				log.info("name===="+name);
				return name;
			}
		} catch (PersistenceException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "";
		}
	}
}