GZ/nwyj/.svn/pristine/7b/7b2bfa1f1c95e675097ec1cfec1cbcd49b461868.svn-base

package com.sinosoft.lz.system.userFriend.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONArray;

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.assitant.generator.IdentityGenerator;
import com.persistence.service.exception.PersistenceException;
import com.sinosoft.am.org.jdbcUtil.ORGTemplate;
import com.sinosoft.common.excel.JsonPluginsUtil;
import com.sinosoft.em.baobiao.xianlutingyun.dao.BaoBiaoDao;
import com.sinosoft.lz.system.userFriend.vo.MsgInfo;
import com.sinosoft.lz.system.userFriend.vo.Person;
import com.sinosoft.lz.system.userFriend.vo.PlanBean;
import com.sysmodel.datamodel.xmlmodel.ModelFactory;
import com.sysmodel.datamodel.xmlmodel.able.SysModel;
import com.system.push.MessagePush;

public class SendMsgDao {
	private Logger log = Logger.getLogger(SendMsgDao.class);

	/**
	 * 保存消息都数据库
	 * @param mg
	 * @return
	 * @throws ClassNotFoundException
	 */
	public Msg saveMsg(MsgInfo mg) throws ClassNotFoundException {
		Msg m=new Msg();
		String msg = mg.getSendMsg();
		String user = mg.getSendUser();
		List<String> recid = mg.getRecivedUser();
		Connection conn = null;
		PreparedStatement st = null;
		DbConnection db = new DbConnection();
//		log.info("recid.size===="+recid.size());
		try {
			String sql = "INSERT INTO SYS_USER_MSG(FD_OBJECTID,MSG_SEND_TIME,MSG_CONTENT,SEND_USER_ID,RECEIVE_USER_ID,MSG_STATUS,SEND_DELETE_STAT,RECIVED_DELETE_STAT,read_stat) VALUES(?,?,?,?,?,?,?,?,?)";
			conn = db.getConnection();
			int num=0;
			conn.setAutoCommit(false);
			st = conn.prepareStatement(sql);
			for(int i=0;i<recid.size();i++){
				st.setString(1, IdentityGenerator.getIdentityGenerator().gerenalIdentity(99));
//				log.info("createMsgId(getMsgId())==="+createMsgId(getMsgId()));
				st.setString(2, formatDate(new Date()));
//				log.info("formatDate(new Date())==="+formatDate(new Date()));
				st.setString(3, msg);
//				log.info("msg===="+msg);
				st.setString(4, user);
//				log.info("user"+user);
				st.setString(5, recid.get(i));
//				log.info("recid.get(0)"+recid.get(0));
				st.setString(6, "0");
				st.setString(7, "0");
				st.setString(8, "0");
				st.setString(9, "0");
				st.executeUpdate();
				num++;
//				if(!"".equals(mg.getFlag())&&mg.getFlag().equals("1")){
//				}else{
				//if("1".equals(mg.getFlag())){
				String user_ids=recid.get(i);
				log.info(user_ids+"==========user_ids");
					MessagePush.showMsg(null,user_ids,"<a href=\"javascript:void(0)\" onclick=\"openZ('/nwyj/page/system/message/recivedListMsg.jsp')\">"+msg+"</a>","sendMessage");
					
				//}
//				}
			}
//			log.info("num====="+num);
			if(num==recid.size()){
				log.info("保存成功");
				 conn.commit();
				 m.setSucsess(new Boolean(true).toString());
				 m.setInfo(Parameter.DELETE_SUCSESS);
				 return m;
			}else{
				m.setSucsess(new Boolean(false).toString());
				m.setInfo(Parameter.DELETE_FAILURE);
//			 st.executeBatch();
			 conn.rollback(); 
			 log.info("保存失败");
			 return m;}
		} catch (SQLException e) {
			this.log.error(e.getMessage(), e);
			throw new ClassNotFoundException("DAO　Layou: 消息保存", e);
		} finally {
			db.close(st);
			db.close(conn);
		}
		
	}

	public String formatDate(Date d) {
		SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String date = sd.format(d);
		return date;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<String> getMsgId() throws ClassNotFoundException {
		String sql = "SELECT FD_OBJECTID FROM SYS_USER_MSG";
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		DbConnection db = new DbConnection();
		try {
			conn = db.getConnection();
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			List<String> list = new ArrayList();
			while (rs.next()) {
				list.add(rs.getString("FD_OBJECTID"));
			}
			return list;
		} catch (SQLException e) {
			this.log.error(e.getMessage(), e);
			throw new ClassNotFoundException("DAO　Layou: 获得数据库消息ID集合"
					+ sql, e);
		} finally {
			db.close(rs);
			db.close(stat);
			db.close(conn);
		}
	}
	
	public String createMsgId(List<String> list) throws ClassNotFoundException{
		long l=(long) ((Math.random()+1)*1000000000);
		String msgId=l+"";
		if(list.contains(msgId)){
			return createMsgId(list);
		}
		else{
//			log.info("msgid==="+msgId);
		return msgId;
		}
	}
	
	
	/**
	 * 删除消息（将数据库状态更改）
	 * @param fd_objectid 消息ID
	 * @param stat 删除标志--SEND删除发件信息/REC删除收件信息
	 * @throws ClassNotFoundException
	 */
	public Msg deleteMsg(String stat,String fd_objectid) throws ClassNotFoundException{
		String sql="";
//		log.info("stat==="+stat);
		Msg m=new Msg();
		String []arr=fd_objectid.split(";");
		
		if("".equals(stat)){
			m.setSucsess(new Boolean(false).toString());
			m.setInfo(Parameter.DELETE_FAILURE);
			return m;
		}else if("SEND".equals(stat)){
			sql="UPDATE SYS_USER_MSG SET SEND_DELETE_STAT='1' WHERE FD_OBJECTID=?";
		}else if("REC".equals(stat)){
			sql="UPDATE SYS_USER_MSG SET RECIVED_DELETE_STAT='1' WHERE FD_OBJECTID=?";
		}else{
			m.setSucsess(new Boolean(false).toString());
			m.setInfo(Parameter.DELETE_FAILURE);
			return m;
		}
		log.info(sql);
		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.DELETE_SUCSESS);
				 conn.commit();
				 return m;
			}else{
				m.setSucsess(new Boolean(false).toString());
				m.setInfo(Parameter.DELETE_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);
		}
	}
	
	
	/**
	 * 获取接收信息列表
	 * @param userId
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public String getRecMsgList(String userId,String name,int page,int pageSize){
//		log.info("name==="+name+"====================");
		String sql="SELECT top "+(page-1)*pageSize+","+pageSize+"  a.FD_OBJECTID,a.MSG_SEND_TIME,SUBSTR(a.MSG_CONTENT,'0','20') MSG_CONTENT,a.SEND_USER_ID,a.RECEIVE_USER_ID,a.MSG_STATUS,a.SEND_DELETE_STAT,B.REAL_NAME,A.READ_STAT FROM SYS_USER_MSG a,SYS_USER_INFO B WHERE RECIVED_DELETE_STAT ='0' AND A.RECEIVE_USER_ID='"+userId+"' AND B.REAL_NAME LIKE '%"+name+"%' AND A.SEND_USER_ID=B.USER_ID  ORDER BY A.MSG_SEND_TIME DESC";
		log.info(sql+"==================sql111");
		String sql2 = "SELECT COUNT(*) FROM ( SELECT  a.FD_OBJECTID,a.MSG_SEND_TIME,SUBSTR(a.MSG_CONTENT,'0','20') MSG_CONTENT,a.SEND_USER_ID,a.RECEIVE_USER_ID,a.MSG_STATUS,a.SEND_DELETE_STAT,B.REAL_NAME,A.READ_STAT FROM SYS_USER_MSG a,SYS_USER_INFO B WHERE RECIVED_DELETE_STAT ='0' AND A.RECEIVE_USER_ID=? AND B.REAL_NAME LIKE ? AND A.SEND_USER_ID=B.USER_ID  ORDER BY A.MSG_SEND_TIME DESC)";
		//		String sql1=" AND a.SEND_USER_ID LIKE '%"+userId+"%'";
//		sql+=sql1;
//		log.info("getRecMsgList.sql==="+sql);
		List list = null;
		List <Map<String,String>> list2 = null;
		try {
			list = PersistenceFactory.getInstance( ModelFactory.getSysmodel()).getSearchResultToMap(99, sql);
			try {
				list2 = new ORGTemplate().query(sql2, userId,"%"+name+"%") ;
			} catch (SQLException e) {
				log.error(e.getMessage(),e);
			}
			log.info(list2.get(0)+"=================get(0)");
		} catch (PersistenceException e) {
			log.error(e.getMessage());
		}
//		log.info("获取已接收消息条数===="+list.size());
		String result = "{\"pager.totalRows\":"+list2.get(0).get("COUNT(*)")+",\"pager.pageNo\":"+page+","+"\"rows\":" + JSONArray.fromObject(list).toString() + "}";
//		log.info("获取已接收消息条数===="+result);
		return result;
	}
	
	/**
	 * 获取接收信息详细内容
	 * @param userId
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public String getRecMsgInfo(String userId,String objectId){
		String sql="SELECT A.*,B.REAL_NAME,B.MOBILE,B.DEPT_ID,D.DEPT_NAME FROM SYS_USER_MSG A,SYS_USER_INFO B, SYS_DEPARTMENT D WHERE A.SEND_USER_ID=B.USER_ID AND B.DEPT_ID = D.DEPT_ID AND A.FD_OBJECTID='"+objectId+"'";
		String sql1="update sys_user_msg set read_stat='1' where fd_objectid='"+objectId+"'";
		try {
			int num=PersistenceFactory.getInstance( ModelFactory.getSysmodel()).executeUpdateSQL(99, sql1);
		} catch (PersistenceException e1) {
			
			e1.printStackTrace();
		}
		List list = null;
		try {
			list = PersistenceFactory.getInstance( ModelFactory.getSysmodel()).getSearchResultToMap(99, sql);
		} catch (PersistenceException e) {
			log.error(e.getMessage());
		}
		String result = "{\"rows\":" + JSONArray.fromObject(list).toString() + "}";
//		log.info("获取已接收消息信息==="+result);
		return result;
	}
	
	
	/**
	 * 获取已发送信息列表
	 * @param userId
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public String getSendMsgList(String userId,String userName,int page,int pageSize){
//		log.info("getSendMsgList.userId===="+userId);
		String sql="SELECT top "+(page-1)*pageSize+","+pageSize+"  a.FD_OBJECTID,a.MSG_SEND_TIME,SUBSTR(a.MSG_CONTENT,'0','20') MSG_CONTENT,a.SEND_USER_ID,a.RECEIVE_USER_ID,a.MSG_STATUS,a.SEND_DELETE_STAT,B.REAL_NAME FROM SYS_USER_MSG a,SYS_USER_INFO B WHERE SEND_DELETE_STAT ='0' AND A.SEND_USER_ID='"+userId+"' AND B.REAL_NAME LIKE '%"+userName+"%' AND A.RECEIVE_USER_ID=B.USER_ID ORDER BY A.MSG_SEND_TIME DESC";
		String sql2 ="SELECT COUNT(*) FROM (SELECT  a.FD_OBJECTID,a.MSG_SEND_TIME,SUBSTR(a.MSG_CONTENT,'0','20') MSG_CONTENT,a.SEND_USER_ID,a.RECEIVE_USER_ID,a.MSG_STATUS,a.SEND_DELETE_STAT,B.REAL_NAME FROM SYS_USER_MSG a,SYS_USER_INFO B WHERE SEND_DELETE_STAT ='0' AND A.SEND_USER_ID=? AND B.REAL_NAME LIKE ?  AND A.RECEIVE_USER_ID=B.USER_ID ORDER BY A.MSG_SEND_TIME DESC)";
		List list = null;
		List<Map<String,String>> list2 = null;
		try {
			list = PersistenceFactory.getInstance( ModelFactory.getSysmodel()).getSearchResultToMap(99, sql);
			try {
				list2 = new ORGTemplate().query(sql2, userId,"%"+userName+"%");
			} catch (SQLException e) {
				log.error(e.getMessage(),e);
			}
		} catch (PersistenceException e) {
			log.error(e.getMessage());
		}
		log.info("获取已发送信息条数===="+list.size());
		String result = "{\"pager.totalRows\":"+list2.get(0).get("COUNT(*)")+",\"pager.pageNo\":"+page+","+"\"rows\":" + JSONArray.fromObject(list).toString() + "}";
		return result;
	}
	
	/**
	 * 获取已发送息详细内容
	 * @param userId
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public String getSendMsgInfo(String userId,String objectId){
		String sql="SELECT A.*,B.REAL_NAME,B.MOBILE,B.DEPT_ID,D.DEPT_NAME FROM SYS_USER_MSG A,SYS_USER_INFO B,SYS_DEPARTMENT D WHERE A.RECEIVE_USER_ID=B.USER_ID  AND  B.DEPT_ID = D.DEPT_ID AND A.FD_OBJECTID='"+objectId+"'";
		List list = null;
		try {
			list = PersistenceFactory.getInstance( ModelFactory.getSysmodel()).getSearchResultToMap(99, sql);
		} catch (PersistenceException e) {
			log.error(e.getMessage());
		}
//		log.info(sql);
//		log.info("获取已发送信息==="+list.size());
		String result = "{\"rows\":" + JSONArray.fromObject(list).toString() + "}";
//		log.info("获取已发送信息==="+result);
		return result;
	}
	
	/**
	 * 增加用户最近联系人
	 * @param userId 用户ID
	 * @param frId  需要增加在联系人id串，格式（id;id;id）
	 * @return
	 * @throws ClassNotFoundException
	 */
	public boolean addRecentUser(String userId,String frId) throws ClassNotFoundException{
		List<String>list=new ArrayList<String>();
		String[]arr=frId.split(";");
		for(int i=0;i<arr.length;i++){
			list.add(arr[i]);
		}
		Connection conn = null;
		PreparedStatement st = null;
		DbConnection db = new DbConnection();
		try {
		String sql="MERGE INTO SYS_USER_CONTACTS A USING(SELECT ? a,? b,? c,? d from dual) S ON(A.USER_ID=S.B AND A.USER_CONTACT_ID=S.C) WHEN MATCHED THEN UPDATE SET A.LAST_CONTACTTIME=S.D WHEN NOT MATCHED THEN INSERT(FD_OBJECTID,USER_ID,USER_CONTACT_ID,LAST_CONTACTTIME)VALUES(S.A,S.B,S.C,S.D)";
		conn = db.getConnection();
		conn.setAutoCommit(false);
		st = conn.prepareStatement(sql);
		for(int i=0;i<list.size();i++){
			st.setString(1, IdentityGenerator.getIdentityGenerator().gerenalIdentity(99));
			st.setString(2, userId);
			st.setString(3, list.get(i));
			st.setString(4, formatDate(new Date()));
			st.executeUpdate();
		}
//		 st.executeBatch();
		 conn.commit(); 
//		 log.info("最近联系人爆粗成功");
		 return true;
	} catch (SQLException e) {
		this.log.error(e.getMessage(), e);
		throw new ClassNotFoundException("DAO　Layou: 新增用户最近联系人", e);
	} finally {
		db.close(st);
		db.close(conn);
	}
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<String> getFriendId() throws ClassNotFoundException{
		String sql="SELECT FD_OBJECTID,USER_ID,USER_CONTACT_ID,LAST_CONTACTTIME FROM SYS_USER_CONTACTS";
		Connection conn = null;
		Statement stat = null;
		ResultSet rs = null;
		DbConnection db = new DbConnection();
		try {
			conn = db.getConnection();
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			List<String> list = new ArrayList();
			while (rs.next()) {
				list.add(rs.getString("FD_OBJECTID"));
			}
			return list;
		} catch (SQLException e) {
			this.log.error(e.getMessage(), e);
			throw new ClassNotFoundException("DAO　Layou: 取得用户最近联系人"
					+ sql, e);
		} finally {
			db.close(rs);
			db.close(stat);
			db.close(conn);
		}
	}
	
	@SuppressWarnings("rawtypes")
	public String getUserNameById(String name){
		String sql="SELECT USER_ID,REAL_NAME FROM SYS_USER_INFO WHERE REAL_NAME='"+name+"'";
//		log.info("getUserNameById.sql====="+sql);
		List list = null;
		try {
			list = PersistenceFactory.getInstance( ModelFactory.getSysmodel()).getSearchResultToMap(99, sql);
		} catch (PersistenceException e) {
			log.error(e.getMessage());
		}
		String result = "{\"rows\":" + JSONArray.fromObject(list).toString() + "}";
//		log.info("获取已接收消息信息==="+result);
		return result;
	}
	
	
	//返回通知应急联系人的JSON
	//应急预案
	public String getOrgLinkInfo(String file_id,String grade_type){
		Person per = new  Person();
		String FRIST_GRADE="";
		String SECOND_GRADE="";
		String THRID_GRADE="";
		String FOURTH_GRADE="";

		//存入人的对象
		Map<String,Person> map = new HashMap<String,Person>();
		String sql="select * from EMC_EM_PLAN_GRADE_NEXUS where FILLER1='"+file_id+"' and GRADE_TYPE like '"+grade_type+"'";
		log.info(sql);
		List<Map<String, String>> list = null;
		List<Person>pList=new ArrayList<Person>();
		try {
			list = PersistenceFactory.getInstance( ModelFactory.getSysmodel()).getSearchResultToMap(99, sql);
			if(list!=null&&list.size()>0){
				Map<String, String>tempMap=list.get(0);
				FRIST_GRADE=tempMap.get("FRIST_GRADE");
				SECOND_GRADE=tempMap.get("SECOND_GRADE");
				THRID_GRADE=tempMap.get("THRID_GRADE");
				FOURTH_GRADE=tempMap.get("FOURTH_GRADE");
				//转换成数组对象
				String[] arr1 = FRIST_GRADE.split("\\|");
				String[] arr2 = SECOND_GRADE.split("\\|");
				String[] arr3 = THRID_GRADE.split("\\|");
				String[] arr4 = FOURTH_GRADE.split("\\|");
				
				int ff = arr1.length;
				
				String[] arr11 = null;
				String[] arr22 = null;
				String[] arr33 = null;
				String[] arr44 = null;
				
				String name = "";
				String id = "";
				String tel = "";
				
				//一级
				//遍历数组
				if(arr1.length>0){
					for(int i=0;i<arr1.length;i++){
						String arr1temp = arr1[i];
						arr11 = arr1temp.split("\\*");
						name = arr11[0];
						tel = arr11[1];
						id = arr11[2];
						if(map.containsKey(id)){
							Person p = map.get(id);
							p.setName(name);
							p.setTel(tel);
							p.setLevel1("true");
							map.put(id, p);
						}else{
							Person p = new Person();
							p.setId(id);
							p.setName(name);
							p.setTel(tel);
							p.setLevel1("true");
							map.put(id, p);
							pList.add(p);
						}
					}
				}
				
				//二级
				//遍历数组
				if(arr2.length>0){
					for(int i=0;i<arr2.length;i++){
						String arr2temp = arr2[i];
						arr22 = arr2temp.split("\\*");
						if(map.containsKey(arr22[2])){
							Person p = map.get(arr22[2]);
							p.setName(arr22[0]);
							p.setTel(arr22[1]);
							p.setLevel2("true");
							map.put(arr22[2], p);
						}else{
							Person p = new Person();
							p.setId(arr22[2]);
							p.setName(arr22[0]);
							p.setTel(arr22[1]);
							p.setLevel1("true");
							map.put(arr22[2], p);
							pList.add(p);
						}
					}
				}
				
				//三级
				//遍历数组
				if(arr3.length>0){
					for(int i=0;i<arr3.length;i++){
						String arr3temp = arr3[i];
						arr33 = arr3temp.split("\\*");
						if(map.containsKey(arr33[2])){
							Person p = map.get(arr33[2]);
							p.setName(arr33[0]);
							p.setTel(arr33[1]);
							p.setLevel3("true");
							map.put(arr33[2], p);
						}else{
							Person p = new Person();
							p.setId(arr33[2]);
							p.setName(arr33[0]);
							p.setTel(arr33[1]);
							p.setLevel3("true");
							map.put(arr33[2], p);
							pList.add(p);
						}
					}
				}
				
				//四级
				//遍历数组
				if(arr4.length>0){
					for(int i=0;i<arr4.length;i++){
						String arr4temp = arr4[i];
						arr44 = arr4temp.split("\\*");
						if(map.containsKey(arr44[2])){
							Person p = map.get(arr44[2]);
							p.setName(arr44[0]);
							p.setTel(arr44[1]);
							p.setLevel4("true");
							map.put(arr44[2], p);
						}else{
							Person p = new Person();
							p.setId(arr44[2]);
							p.setName(arr44[0]);
							p.setTel(arr44[1]);
							p.setLevel4("true");
							map.put(arr44[2], p);
							pList.add(p);
						}
					}
				}
				
				
			}
		} catch (PersistenceException e) {
			log.error(e.getMessage());
		}
		
		String result = "{\"rows\":" + JSONArray.fromObject(map).toString() + "}";
		result="{\"rows\":"+JsonPluginsUtil.beanListToJson(pList) + "}";;
//		log.info("获取getPlanInfo信息==="+result);
		return result;
	}
	
	//应急预案215绑定关系表
	public String getPlanInfo(String file_id,String grade_type,String dept_type){
		String sql = "";
		if(dept_type.equals("1")){
			sql="select * from EMC_EM_PLAN_GRADE_NEXUS where FILLER1=(select FD_OBJECTID from ECM_AM_YJYASERVER where units = "
					+ "(select parent_id from SYS_DEPARTMENT where dept_id = (select UNITS from ECM_AM_YJYASERVER where FD_OBJECTID = '"+file_id+"'))"
							+ "and PLAN_REFINE_CATEGORY = (select PLAN_REFINE_CATEGORY from ECM_AM_YJYASERVER where FD_OBJECTID = '"+file_id+"')"
									+ "and is_del = '0' and PLAN_STAT = '1')and GRADE_TYPE like '"+grade_type+"'";
		}else{
			sql="select * from EMC_EM_PLAN_GRADE_NEXUS where FILLER1='"+file_id+"' and GRADE_TYPE like '"+grade_type+"'";
			//sql="select a.* from  ECM_AM_YJYASERVER a,ECM_AM_YJYASERVER b where a.plan_type=b.plan_type and a.bysuness_type=b.bysuness_type and b.fd_objectid='"+file_id+"'and a.units='C984DC9C294A486096C25FAA78C99AED' and a.PLAN_REFINE_CATEGORY=b.PLAN_REFINE_CATEGORY and  a.is_del='0' and a.plan_stat='1'";

		}
		log.info(sql);
		List<Map<String, String>> list = null;
		try {
			list = PersistenceFactory.getInstance( ModelFactory.getSysmodel()).getSearchResultToMap(99, sql);

		} catch (PersistenceException e) {
			log.error(e.getMessage());
		}
		
		String result = "{\"rows\":" + JSONArray.fromObject(list).toString() + "}";
		return result;
	}
	
	//通过单位ID和预案类型查询该单位下的该类型预案，如果有，返回预案ID
	public String getZhuanxiang(String parent_id,String grade_type){
		String sql="select * from ECM_AM_YJYASERVER where PLAN_REFINE_CATEGORY='"+grade_type+"' and UNITS = '"+parent_id+"' and is_del='0'" ;
		log.info(sql);
		List<Map<String, String>> list = null;
		try {
			list = PersistenceFactory.getInstance( ModelFactory.getSysmodel()).getSearchResultToMap(99, sql);

		} catch (PersistenceException e) {
			log.error(e.getMessage());
		}
		
		String result = "{\"rows\":" + JSONArray.fromObject(list).toString() + "}";
		return result;
	}
	
	
	
	//应急预案
	public String getPlanInfoEM(String file_id,String grade_type){
		String	sql="select a.*,b.PLAN_REFINE_CATEGORY from EMC_EM_PLAN_GRADE_NEXUS a,ECM_AM_YJYASERVER b where filler1 in(select a.fd_objectid from  ECM_AM_YJYASERVER a,ECM_AM_YJYASERVER b where a.plan_type=b.plan_type and a.bysuness_type=b.bysuness_type and b.fd_objectid='"+file_id+"'and a.units='C984DC9C294A486096C25FAA78C99AED' and a.PLAN_REFINE_CATEGORY=b.PLAN_REFINE_CATEGORY and  a.is_del='0' and a.plan_stat='1') and grade_type like '"+grade_type+"' and a.filler1=b.fd_objectid";
		if(file_id.indexOf(",")>-1){//假如是非省级发布通知单，预案id字段存的是：plan9,9类型数据，表示预案细分类型等于9
			String plan_num = file_id.substring(file_id.lastIndexOf(",")+1);
			sql="select a.*,b.PLAN_REFINE_CATEGORY from EMC_EM_PLAN_GRADE_NEXUS a,ECM_AM_YJYASERVER b where filler1 in(select a.fd_objectid from ECM_AM_YJYASERVER a where a.units ='C984DC9C294A486096C25FAA78C99AED' and a.is_del ='0' and a.plan_stat ='1' and a.PLAN_REFINE_CATEGORY='"+plan_num+"' and a.PLAN_TYPE=103 order by updatedate desc) and grade_type like '"+grade_type+"' and a.filler1=b.fd_objectid";
		}
		log.info(sql);
		List list = null;
		try {
			list = PersistenceFactory.getInstance( ModelFactory.getSysmodel()).getSearchResultToMap(99, sql);
		} catch (PersistenceException e) {
			log.error(e.getMessage());
		}
		//String result = "{\"rows\":" + JSONArray.fromObject(list).toString() + "}";
//				log.info("获取getPlanInfo信息==="+result);
		return JSONArray.fromObject(list).toString();
	}
	

	//应急预案--短信
	public String getPlanInfoMsg(String file_id,String grade_type){
		String sql="select FRIST_GRADE,SECOND_GRADE,THRID_GRADE,FOURTH_GRADE from EMC_EM_PLAN_GRADE_NEXUS where FILLER1='"+file_id+"' and GRADE_TYPE='"+grade_type+"'";
		log.info(sql);
		PlanBean pb=new PlanBean();
		String result="";
		List<String[]> list = null;
		try {
			list = PersistenceFactory.getInstance( ModelFactory.getSysmodel()).getSearchResult(99,sql);
		} catch (PersistenceException e) {
			log.error(e.getMessage());
		}
		if(list==null){
			return null;
		}else{
			if(list.size()==1){
//				result=changeName(list)
				String firstName=list.get(0)[0];
				String secondName=list.get(0)[1];
				String thirdName=list.get(0)[2];
				String fourthName=list.get(0)[3];
//				log.info("firstName===" + firstName);
//				log.info("secondName===" + secondName);
//				log.info("thirdName===" + thirdName);
//				log.info("fourthName===" + fourthName);
				result=getInfoString(firstName,secondName,thirdName,fourthName);
//				String []firstNameArr=firstName.split("[|][|]");
//				pb.setFIRST_DEPT(changeName2(firstNameArr[0]));
//				pb.setFIRST_EXPERT(changeName2(firstNameArr[1]));
//				pb.setFIRST_TEAM(changeName2(firstNameArr[2]));
//				
//				String []secondNameArr=secondName.split("[|][|]");
//				pb.setSECOND_DEPT(changeName2(secondNameArr[0]));
//				pb.setSECOND_EXPERT(changeName2(secondNameArr[1]));
//				pb.setSECOND_TEAM(changeName2(secondNameArr[2]));
//				
//				String []thirdNameArr=thirdName.split("[|][|]");
//				pb.setTHIRD_DEPT(changeName2(thirdNameArr[0]));
//				pb.setTHIRD_EXPERT(changeName2(thirdNameArr[1]));
//				pb.setTHIRD_TEAM(changeName2(thirdNameArr[2]));
//				
//				String []fourthNameArr=fourthName.split("[|][|]");
//				pb.setFOURTH_DEPT(changeName2(fourthNameArr[0]));
//				pb.setFOURTH_EXPERT(changeName2(fourthNameArr[1]));
//				pb.setFOURTH_TEAM(changeName2(fourthNameArr[2]));
				//龙志伟#13811111114@唐群胜#13800110505@||输电一队#88888888@||李思思#13312345678@龙志伟#13811111111@张三#13574766666@||
//				log.info("result===" + result);
			}
		}
//		String result = "{\"rows\":" + JSONArray.fromObject(list).toString() + "}";
//		log.info("获取getPlanInfo信息==="+result);
		return result;
	}
	
	public String getInfoString(String firstName,String secondName,String thirdName,String fourthName){
		PlanBean pb=new PlanBean();
		String []firstNameArr=firstName.split("[|][|]");
		if(firstNameArr==null||firstNameArr.length!=3){
			return null;
		}
//		log.info("firstNameArr[0]=========="+changeName2(firstNameArr[0]));
		pb.setFIRST_DEPT(firstNameArr[0]);
		pb.setFIRST_EXPERT(firstNameArr[1]);
		pb.setFIRST_TEAM(firstNameArr[2]);
//		log.info("firstNameArr[0]=========="+changeName2(firstNameArr[0]));
		String []secondNameArr=secondName.split("[|][|]");
		if(secondNameArr==null||secondNameArr.length!=3){
			return null;
		}
		pb.setSECOND_DEPT(secondNameArr[0]);
		pb.setSECOND_EXPERT(secondNameArr[1]);
		pb.setSECOND_TEAM(secondNameArr[2]);
		
		String []thirdNameArr=thirdName.split("[|][|]");
		if(thirdNameArr==null||thirdNameArr.length!=3){
			return null;
		}
		pb.setTHIRD_DEPT(thirdNameArr[0]);
		pb.setTHIRD_EXPERT(thirdNameArr[1]);
		pb.setTHIRD_TEAM(thirdNameArr[2]);
		
		String []fourthNameArr=fourthName.split("[|][|]");
		if(fourthNameArr==null||fourthNameArr.length!=3){
			return null;
		}
		pb.setFOURTH_DEPT(fourthNameArr[0]);
		pb.setFOURTH_EXPERT(fourthNameArr[1]);
		pb.setFOURTH_TEAM(fourthNameArr[2]);
		String result=JSONArray.fromObject(pb).toString();
//		log.info(result);
		return result;
	}
	
	public String changeName(String name){
		
		return null;
	}
	
	//同一类型不同部门需要换行
//	public String changeName2(String name){
//		StringBuffer sb=new StringBuffer();
//		Map<String, String>map=new HashMap<String, String>();
//		if(!"".equals(name)){
//			String[]arr=name.split("[@]");
//			log.info("arr====="+arr[0]);
//			for(int i=0;i<arr.length;i++){
//				String[] arr1=arr[i].split("[#]");
//				log.info("arr1"+arr1[i]);
//				if(arr1==null||arr1.length!=3){
//					return null;
//				}
////					String deptName=getDeptById(arr1[0]);
//				log.info("测试");
//				String deptName=arr1[0];
//					String userName=arr1[1];
//					String tel=arr1[2];
//					String ss=deptName+":"+userName+","+tel+";";
//					if(map.containsKey(arr1[0])){
//						 ss=userName+","+tel+";";
//						map.put(arr1[0], map.get(arr1[0])+ss);
//					}else{
//						map.put(arr1[0], ss);
//					}
//					
//			}
//			Set set = map.keySet();
//			for (Object key : set) {
//				sb.append(map.get(key)+"\r\n");
//			}
//		}
//		return sb.toString();
//	}
	
	public String getDeptById(String id){
		SysModel		sysmodel	= ModelFactory.getSysmodel();
		SysPersistence	persistence	= PersistenceFactory.getInstance(sysmodel);
		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) {
			
			e.printStackTrace();
			return "无部门";
		}
	}
	
	
	
	public int getReadedCount(String userId) throws ClassNotFoundException{
		
		String sql = "select count(1) from sys_user_msg a ,sys_user_info b where a.read_stat=? and receive_user_id = ? and recived_delete_stat = ? and a.send_user_id= b.user_id";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		DbConnection db = new DbConnection();
		int num=0;
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, "0");
			ps.setString(2, userId);
			ps.setString(3, "0");
			rs = ps.executeQuery();
			while(rs.next()){
				num = Integer.parseInt(rs.getString("COUNT(1)"));
			}
			
		}  catch (SQLException e) {
			log.info(e.getMessage(),e);
		}finally{
			db.close(rs);
			db.close(ps);
			db.close(conn);
		}
		log.info(num+"===============num");
		return num;
	}

	public String getReportAndPlanTime(String event_type_id, String event_level,String comp_id) {
		if("29".equals(event_level)){//一级响应
			event_level = "1";
		}else if("30".equals(event_level)){//二级响应
			event_level = "2";
		}else if("31".equals(event_level)){//三级响应
			event_level = "3";
		}else if("32".equals(event_level)){//四级响应
			event_level = "4";
		}else if("35".equals(event_level)){//红色预警
			event_level = "5";
		}else if("36".equals(event_level)){//橙色预警
			event_level = "6";
		}else if("37".equals(event_level)){//黄色预警
			event_level = "7";
		}else if("38".equals(event_level)){//蓝色预警
			event_level = "8";
		}
		String dept_level = new BaoBiaoDao().getDeptLevel(comp_id);
		String	sql="select b.report_name, c.report_time, b.SERIAL_NUMBER,b.role_id,b.is_type from ECM_EM_EMERGENCY_TYPE a , ECM_EM_EMERGENCY_TYPE_CONFIG b, ECM_EM_EVENT_REPORT c where a.fd_objectid=b.event_type_id 	and b.fd_objectid=c.report_id 	and a.fd_objectid='"+event_type_id+"' 	and war_rep_level='"+event_level+"' ";
		log.info(sql);
		String sql1  = "";
		List<Map<String,String>> list = null;
		List<Map<String,String>> list1 = null;
		StringBuffer sb = new StringBuffer();
		sb.append("");
		try {
			list = PersistenceFactory.getInstance( ModelFactory.getSysmodel()).getSearchResultToMap(99, sql);
			for (int i = 0; i < list.size(); i++) {
				Map<String,String> map = list.get(i);
				String report_name = map.get("REPORT_NAME");//报表名称
				String report_time = map.get("REPORT_TIME");//填报时间
				String serial_number = map.get("SERIAL_NUMBER");//报表对应编号
				if(("2".equals(dept_level) && "2".equals(serial_number)) || (!"2".equals(dept_level) && "101".equals(serial_number))){
					continue;
				}
				String role_id = map.get("ROLE_ID");//报表对应角色
				String is_type = map.get("IS_TYPE");//报告还是报表
				sb.append(is_type+"~"+serial_number+"~"+report_name+"~"+report_time+"~"+role_id);
				sb.append("_&_");
				if("1".equals(is_type)){//如果是报表
					StringBuffer sb1 = new StringBuffer();
					sb1.append("");
					sql1 = "select 	distinct d.COMP_NAME, d.COMP_ID,d.DEPT_TYPE from ECM_EM_EMERGENCY_TYPE a , ECM_EM_EMERGENCY_TYPE_CONFIG b, REPORT_COMP_DICTIONARY d where a.fd_objectid=b.event_type_id and a.fd_objectid=d.AFFAIR and d.report_type=b.serial_number and a.fd_objectid='"+event_type_id+"' and d.report_view_id='"+comp_id+"' and b.serial_number='"+serial_number+"' order by d.DEPT_TYPE ";
					list1 = PersistenceFactory.getInstance( ModelFactory.getSysmodel()).getSearchResultToMap(99, sql1);
					for (int j = 0; j < list1.size(); j++) {//突发事件类型绑定的单位
						Map<String,String> map1 = list1.get(j);
						String COMP_NAME = map1.get("COMP_NAME");
						String DEPT_TYPE = map1.get("DEPT_TYPE");
						String COMP_ID = map1.get("COMP_ID");
						if("corp".equals(DEPT_TYPE)){
							sb.append(COMP_ID+",");
						}else{
							sb1.append(COMP_ID+",");
						}
					}
					sb.append("__"+sb1);
				}
				sb.append("||@");
			}
			
			
		} catch (PersistenceException e) {
			log.error(e.getMessage());
		}
		//String result = "{\"rows\":" + JSONArray.fromObject(list).toString() + "}";
//				log.info("获取getPlanInfo信息==="+result);
		return sb.toString();
	}
}