GZ/nwyj/.svn/pristine/43/43e0254e7e7b300c3f7c71ffd4da3af9f603f1dc.svn-base

package com.sinosoft.am.file.dao;

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

import org.apache.log4j.Logger;

import com.formaction.Parameter;
import com.formaction.vo.Msg;
import com.persistence.DbConnection;
import com.persistence.service.PersistenceFactory;
import com.persistence.service.SysPersistence;
import com.persistence.service.exception.PersistenceException;
import com.sinosoft.am.file.vo.FileAM;
import com.sysmodel.datamodel.xmlmodel.ModelFactory;
import com.sysmodel.datamodel.xmlmodel.able.SysModel;

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

	

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

	
	/**
	 * 根据传过来的ID进行检测，有子节点且子节点在明细表中有数据的，继续查询该节点下面的数据
	 * @param id
	 * @return
	 */
	public String NumInit(String id,String StartTime,String EndTime,String valueType,String deptId){
		String sql="select DEPT_ID from sys_department where (parent_id='"+id+"' or DEPT_ID='"+id+"')";
		log.info("carNum.tempList.size===="+deptId);
		try{			
			if(deptId != null && !"null".equals(deptId) &&!"".equals(deptId)){
				sql+=" and DEPT_ID in("+deptId+") ";
			}			
		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]);
			}
			getFileNum(list, list.get(0),StartTime,EndTime,valueType);
			
		}else{
			List<String>list=new ArrayList<String>();
			list.add(id);
			getNoChildFileNum(list, id,StartTime,EndTime,valueType);
		}}catch(Exception e){
			this.log.error(e.getMessage(), e);
		}
//		log.info("最终JSON==="+json.toString());
//		if (json.lastIndexOf(",") > -1) {
//			json.deleteCharAt(json.lastIndexOf(","));
//		}
		
		FileAM numBean = new FileAM();
		for(int i=0;i<numList.size();i++){
			numBean.setNetclass(numBean.getNetclass()+numList.get(i).getNetclass());
			numBean.setProvincial(numBean.getProvincial()+numList.get(i).getProvincial());
			numBean.setMunicipal(numBean.getMunicipal()+numList.get(i).getMunicipal());
			numBean.setSubcorps(numBean.getSubcorps()+numList.get(i).getSubcorps());
			numBean.setDishiju(numBean.getDishiju()+numList.get(i).getDishiju());
			numBean.setAdministrative(numBean.getAdministrative()+numList.get(i).getAdministrative());
			numBean.setNum(numBean.getNum()+numList.get(i).getNum());
			
		}
		json.append("{	");
		json.append("	\"netclass\":\"" + numBean.getNetclass() + "\",");
		json.append("	\"provincial\":\"" + numBean.getProvincial() + "\",");
		json.append("	\"municipal\":\"" + numBean.getMunicipal() + "\",");
		json.append("	\"subcorps\":\"" + numBean.getSubcorps() + "\",");
		json.append("	\"dishiju\":\"" + numBean.getDishiju() + "\",");
		json.append("	\"administrative\":\"" + numBean.getAdministrative() + "\",");
		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 teamNum(String id,String StartTime,String EndTime,String valueType,String deptId){
		try{
			String sql="select dept_id from sys_department where parent_id='"+id+"'";
			if(deptId!=null){
				sql+=" and id in("+deptId+")";
			}
		if(checkId(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>());
				getFileNum(list, tempList.get(i)[0],StartTime,EndTime,valueType);
			}
		}else{
			List<String>list=new ArrayList<String>();
			list.add(id);
			getNoChildFileNum(list, id,StartTime,EndTime,valueType);
		}}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 ECM_AM_PPS_FILE_MANAGEMANET WHERE PUBLISH_COMP_ID 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 getFileNum(List<String>list,String id,String StartTime,String EndTime,String valueType) throws PersistenceException{
		
		try{
		for(int i=0;i<list.size();i++){
			FileAM fa = new FileAM();
			String sql="select PUBLISH_COMP_ID,sum(case FILE_CLASSIFY when '1' then 1 else 0 end)as 网级,"
					+ "sum(case FILE_CLASSIFY when '2' then 1 else 0 end)as 省级,"
					+ "sum(case FILE_CLASSIFY when '3' then 1 else 0 end)as 市级,"
					+ "sum(case FILE_CLASSIFY when '4' then 1 else 0 end)as 分子公司, "
					+ "sum(case FILE_CLASSIFY when '5' then 1 else 0 end)as 地市局,"
					+ "sum(case FILE_CLASSIFY when '6' then 1 else 0 end)as 规范, "
					+ "count(FILE_CLASSIFY)as num from ECM_AM_PPS_FILE_MANAGEMANET where is_del='0' and FABU='1' and PUBLISH_COMP_ID='"+list.get(i)+"'";
			
			if(StartTime!=null&&!"null".equals(StartTime)&&!"".equals(StartTime)){
				sql+=" and FILE_ADD_DATE between '"+StartTime+"' and '"+EndTime+"'";
			}
			
			//分类
			if(valueType!=null&&!"null".equals(valueType)&&!"".equals(valueType)){
				sql+=" and FILE_CLASSIFY in ("+valueType+")";
			}
			
			sql+=" group by PUBLISH_COMP_ID";
			
			
			log.info("getTeamNum.sql===="+sql);
			List<String[]>tempList= persistence.getSearchResult(99, sql.toString());
			if(tempList.size()>0){
//				fa.setTeamCount(Integer.parseInt(tempList.get(0)[1]));
//				fa.setTeamMember(Integer.parseInt(tempList.get(0)[2]));
//				fa.setSdXianLu(Integer.parseInt(tempList.get(0)[3]));
//				fa.setSdXianLang(Integer.parseInt(tempList.get(0)[4]));
//				fa.setSdSum(Integer.parseInt(tempList.get(0)[5]));
				fa.setNetclass(Integer.parseInt(tempList.get(0)[1]));
				fa.setProvincial(Integer.parseInt(tempList.get(0)[2]));
				fa.setMunicipal(Integer.parseInt(tempList.get(0)[3]));
				fa.setSubcorps(Integer.parseInt(tempList.get(0)[4]));
				fa.setDishiju(Integer.parseInt(tempList.get(0)[5]));
				fa.setAdministrative(Integer.parseInt(tempList.get(0)[6]));
				fa.setNum(Integer.parseInt(tempList.get(0)[7]));
				
				numList.add(fa);
				
				if(fa.getNum()>0){
					if(checkId(id)){
						json.append("{	");
						json.append("	\"netclass\":\"" + fa.getNetclass() + "\",");
						json.append("	\"provincial\":\"" + fa.getProvincial() + "\",");
						json.append("	\"municipal\":\"" + fa.getMunicipal() + "\",");
						json.append("	\"subcorps\":\"" + fa.getSubcorps() + "\",");
						json.append("	\"dishiju\":\"" + fa.getDishiju() + "\",");
						json.append("	\"administrative\":\"" + fa.getAdministrative() + "\",");
						json.append("	\"num\":\"" + fa.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("	\"netclass\":\"" + fa.getNetclass() + "\",");
						json.append("	\"provincial\":\"" + fa.getProvincial() + "\",");
						json.append("	\"municipal\":\"" + fa.getMunicipal() + "\",");
						json.append("	\"subcorps\":\"" + fa.getSubcorps() + "\",");
						json.append("	\"dishiju\":\"" + fa.getDishiju() + "\",");
						json.append("	\"administrative\":\"" + fa.getAdministrative() + "\",");
						json.append("	\"num\":\"" + fa.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 getNoChildFileNum(List<String>list,String id,String StartTime,String EndTime,String valueType) throws PersistenceException{
		FileAM fa = new FileAM();
		try{
		for(int i=0;i<list.size();i++){
			String sql="select PUBLISH_COMP_ID,sum(case FILE_CLASSIFY when '1' then 1 else 0 end)as 网级,"
					+ "sum(case FILE_CLASSIFY when '2' then 1 else 0 end)as 省级,"
					+ "sum(case FILE_CLASSIFY when '3' then 1 else 0 end)as 市级,"
					+ "sum(case FILE_CLASSIFY when '4' then 1 else 0 end)as 分子公司, "
					+ "sum(case FILE_CLASSIFY when '5' then 1 else 0 end)as 地市局,"
					+ "sum(case FILE_CLASSIFY when '4' then 1 else 0 end)as 规范, "
					+ "count(FILE_TITLE)as num from ECM_AM_PPS_FILE_MANAGEMANET where is_del='0' and FABU='1' and PUBLISH_COMP_ID='"+list.get(i)+"'";
			
			if(StartTime!=null&&!"null".equals(StartTime)&&!"".equals(StartTime)){
				sql+=" and FILE_ADD_DATE between '"+StartTime+"' and '"+EndTime+"'";
			}
			
			//分类
			if(valueType!=null&&!"null".equals(valueType)&&!"".equals(valueType)){
				sql+=" and FILE_CLASSIFY in ("+valueType+")";
			}
			
			sql+=" group by PUBLISH_COMP_ID";
			
			log.info("getNoChildTeamNum.sql===="+sql);
			List<String[]>numList= persistence.getSearchResult(99, sql.toString());
			if(numList.size()>0){

				fa.setNetclass(fa.getNetclass()+Integer.parseInt(numList.get(0)[1]));
				fa.setProvincial(fa.getProvincial()+Integer.parseInt(numList.get(0)[2]));
				fa.setMunicipal(fa.getMunicipal()+Integer.parseInt(numList.get(0)[3]));
				fa.setSubcorps(fa.getSubcorps()+Integer.parseInt(numList.get(0)[4]));
				fa.setDishiju(fa.getDishiju()+Integer.parseInt(numList.get(0)[5]));
				fa.setAdministrative(fa.getAdministrative()+Integer.parseInt(numList.get(0)[6]));
				fa.setNum(fa.getNum()+Integer.parseInt(numList.get(0)[7]));
	
			}
		}}catch(Exception e){
			this.log.error(e.getMessage(), e);
		}
//		StringBuffer json=new StringBuffer();
		if(fa.getNum()>0){
			json.append("{	");
			json.append("	\"netclass\":\"" + fa.getNetclass() + "\",");
			json.append("	\"provincial\":\"" + fa.getProvincial() + "\",");
			json.append("	\"municipal\":\"" + fa.getMunicipal() + "\",");
			json.append("	\"subcorps\":\"" + fa.getSubcorps() + "\",");
			json.append("	\"dishiju\":\"" + fa.getDishiju() + "\",");
			json.append("	\"administrative\":\"" + fa.getAdministrative() + "\",");
			json.append("	\"num\":\"" + fa.getNum() + "\",");
			json.append("	\"name\":\"" + getNameById(id) + "\",");
			json.append("	\"dept_id\":\"" + id + "\",");
			json.append("	\"isParent\":false,");
			json.append("	\"open\":true");
			json.append("},	");
			numList.add(fa);
		}
	}
	
	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];
				log.info("name===="+name);
				return name;
			}
		} catch (PersistenceException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return "";
		}
	}
	
	
	/*****************************************共享***********************************************/
	
	/**
	 * 共享文件 都可以看到
	 * @throws ClassNotFoundException 
	 */
	public Msg getDocuShare(String fd_id) throws ClassNotFoundException{
		Msg m=new Msg();
		String []arr=fd_id.split(",");
		String sql="UPDATE ECM_AM_PPS_FILE_MANAGEMANET SET IS_SHARE='1' where fd_objectid=?";
		Connection conn = null;
		PreparedStatement st = null;
		int num=0;
		DbConnection db = new DbConnection();
		try {
			conn = db.getConnection();
			conn.setAutoCommit(false);
			st = conn.prepareStatement(sql);
			for(int i=0;i<arr.length;i++){
				st.setString(1, arr[i]);
				st.executeUpdate();
				num++;
			}
			if(num==arr.length){
				log.info("发布----num===="+num);
				m.setSucsess(new Boolean(true).toString());
				 m.setInfo(Parameter.OPER_SUCSESS);
				 conn.commit();
				 return m;
			}else{
				m.setSucsess(new Boolean(false).toString());
				m.setInfo(Parameter.OPER_FAILURE);
				conn.rollback();
				return m;
			}
		}catch (SQLException e) {
			this.log.error(e.getMessage(), e);
			throw new ClassNotFoundException("DAO　Layou: 发布消息"
					+ sql, e);
		} finally {
			db.close(st);
			db.close(conn);
		}
	}
	
	/**
	 * 撤销共享
	 * @throws ClassNotFoundException 
	 */
	public Msg getnoDocuShare(String fd_id) throws ClassNotFoundException{
		Msg m=new Msg();
		String []arr=fd_id.split(",");
		String sql="UPDATE ECM_AM_PPS_FILE_MANAGEMANET SET IS_SHARE='0' where fd_objectid=?";
		Connection conn = null;
		PreparedStatement st = null;
		int num=0;
		DbConnection db = new DbConnection();
		try {
			conn = db.getConnection();
			conn.setAutoCommit(false);
			st = conn.prepareStatement(sql);
			for(int i=0;i<arr.length;i++){
				st.setString(1, arr[i]);
				st.executeUpdate();
				num++;
			}
			if(num==arr.length){
				log.info("发布----num===="+num);
				m.setSucsess(new Boolean(true).toString());
				 m.setInfo(Parameter.OPER_SUCSESS);
				 conn.commit();
				 return m;
			}else{
				m.setSucsess(new Boolean(false).toString());
				m.setInfo(Parameter.OPER_FAILURE);
				conn.rollback();
				return m;
			}
		}catch (SQLException e) {
			this.log.error(e.getMessage(), e);
			throw new ClassNotFoundException("DAO　Layou: 发布消息"
					+ sql, e);
		} finally {
			db.close(st);
			db.close(conn);
		}
	}
	
	/**
	 * 撤销发布
	 * @throws ClassNotFoundException 
	 */
	public Msg getnoPublish(String fd_id) throws ClassNotFoundException{
		Msg m=new Msg();
		String []arr=fd_id.split(",");
		String sql="UPDATE ECM_AM_PPS_FILE_MANAGEMANET SET FABU='0',IS_SHARE='0' where fd_objectid=?";
		Connection conn = null;
		PreparedStatement st = null;
		int num=0;
		DbConnection db = new DbConnection();
		try {
			conn = db.getConnection();
			conn.setAutoCommit(false);
			st = conn.prepareStatement(sql);
			for(int i=0;i<arr.length;i++){
				st.setString(1, arr[i]);
				st.executeUpdate();
				num++;
			}
			if(num==arr.length){
				log.info("发布----num===="+num);
				m.setSucsess(new Boolean(true).toString());
				 m.setInfo(Parameter.OPER_SUCSESS);
				 conn.commit();
				 return m;
			}else{
				m.setSucsess(new Boolean(false).toString());
				m.setInfo(Parameter.OPER_FAILURE);
				conn.rollback();
				return m;
			}
		}catch (SQLException e) {
			this.log.error(e.getMessage(), e);
			throw new ClassNotFoundException("DAO　Layou: 发布消息"
					+ sql, e);
		} finally {
			db.close(st);
			db.close(conn);
		}
	}
	
	/***********************************************常量表借用*****************************************************/
	public Msg getaddMapValue(String code,String description,String type,String sort,String parentId) throws ClassNotFoundException{
		Msg m=new Msg();
		
		String sql="INSERT INTO BM_MAPVALUECANST (FD_CODE,FD_DESCRIPTION,FD_TYPE,FD_SORT,PARENT_ID) VALUES (?,?,?,?,?)";
		Connection conn = null;
		PreparedStatement st = null;

		DbConnection db = new DbConnection();
		try {
			conn = db.getConnection();
			conn.setAutoCommit(false);
			st = conn.prepareStatement(sql);

				st.setString(1, code);
				st.setString(2, description);
				st.setString(3, type);
				st.setString(4, sort);
				st.setString(5, parentId);
				st.executeUpdate();
			
			
//			if(num==arr.length){
//				log.info("发布----num===="+num);
				m.setSucsess(new Boolean(true).toString());
				 m.setInfo(Parameter.OPER_SUCSESS);
				 conn.commit();
				 return m;
//			}else{
//				m.setSucsess(new Boolean(false).toString());
//				m.setInfo(Parameter.OPER_FAILURE);
//				conn.rollback();
//				return m;
//			}
		}catch (SQLException e) {
			this.log.error(e.getMessage(), e);
			throw new ClassNotFoundException("DAO　Layou: 发布消息"
					+ sql, e);
		} finally {
			db.close(st);
			db.close(conn);
		}
	}
}