GZ/nwyj/.svn/pristine/5c/5cc3efe4e2fb37b5a7e36dd5dce10ca2c977b79e.svn-base

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

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


import org.apache.log4j.Logger;

import com.formaction.Parameter;
import com.formaction.vo.Msg;
import com.formaction.vo.MsgDetail;
import com.formaction.vo.NewMsg;
import com.persistence.DbConnection;
import com.persistence.service.assitant.generator.IdentityGenerator;
import com.sinosoft.am.org.jdbcUtil.ORGTemplate;
import com.sinosoft.common.excel.JsonPluginsUtil;
import com.sinosoft.lz.system.sms.dao.ws.MsgDao;

public class NewSmsDao {
	
	private Logger	log	= Logger.getLogger(this.getClass());
	/**
	 * 2017-12-13 吴玉祥加
	 * 保存消息都数据库
	 * @param mg
	 * @return
	 * @throws ClassNotFoundException
	 */
	public Msg saveMsg(NewMsg msg) throws ClassNotFoundException {

		Msg m=new Msg();
		Connection conn = null;
		PreparedStatement st = null;
		DbConnection db = new DbConnection();
		String sql = "INSERT INTO MSG_SEND_INFO"
				+"(FD_OBJECTID,MODULE,SUBMODULE,TASK_NAME,IS_EMERGENCY,EVENT_ID,SEND_TYPE,RECORDER_ID,RECORDER_ORG,SEND_TIME,MSG_CONTENT,MSG_TYPE,MSG_REPORT,PRIORITY,MSG_ACCESS,RECEIVER,RECEIVER_ORG,RECEIVER_PHONE)"
				+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			conn = db.getConnection();
			conn.setAutoCommit(false);
			st = conn.prepareStatement(sql);
			msg.setFd_objectId(IdentityGenerator.getIdentityGenerator().gerenalIdentity(99));
			st.setString(1, msg.getFd_objectId());
			st.setString(2, msg.getModule());
			st.setString(3, msg.getSubmodule());
			st.setString(4, msg.getTask_name());
			st.setString(5, msg.getIs_emergency());
			st.setString(6, msg.getEvent_id());
			st.setString(7, msg.getSend_type());
			st.setString(8, msg.getRecorder_id());
			st.setString(9, msg.getRecorder_org());
			st.setString(10, msg.getSend_time());
			st.setString(11, msg.getMsg_content());
			st.setString(12, msg.getMsg_type());
			st.setString(13, msg.getMsg_report());
			st.setString(14, msg.getPriority());
			st.setString(15, msg.getMsg_access());
			st.setString(16, msg.getReceiver());
			st.setString(17, msg.getReceiver_org());
			st.setString(18, msg.getReceiver_phone());
			//st.setString(19, msg.getSend_state());
			//st.setString(20, msg.getSend_result());
			//st.setString(21, msg.getReceipt());
			//st.setString(22, msg.getMsg_id());
			//st.setString(23, msg.getReceipt_time());
			//st.setString(24, msg.getError_code());

			int result = st.executeUpdate();
			if(result>0){
				log.info("保存成功");
				 conn.commit();
				 m.setSucsess(new Boolean(true).toString());
				 m.setInfo(msg.getFd_objectId());
				 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 saveMsgDetail(MsgDetail msgDetail) throws ClassNotFoundException {
		ORGTemplate template = new ORGTemplate();
		String sql = "INSERT INTO MSG_SEND_DETAIL"
				+"(FD_OBJECTID,TASK_NAME,RECEIVER_ID,RECEIVER_PHONE,SEND_RESULT,RECEIVE_TIME,INFO_ID,RECEIPT,ERROR_CODE,SEND_STATE,RECEIVER_ORG,RECEIVER_NAME)"
				+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			msgDetail.setFd_objectId(IdentityGenerator.getIdentityGenerator().gerenalIdentity(99));
			/*st.setString(1, msgDetail.getFd_objectId());
			st.setString(2, msgDetail.getTask_name());
			st.setString(3, msgDetail.getReceiver_id());
			st.setString(4, msgDetail.getReceiver_phone());
			st.setString(5, msgDetail.getSend_result());
			st.setString(6, msgDetail.getReceive_time());
			st.setString(7, msgDetail.getInfo_id());
			//st.setString(8, msgDetail.getMsg_content());
			st.setString(8, msgDetail.getReceipt());
			st.setString(9, msgDetail.getError_code());
			st.setString(10, msgDetail.getSend_state());*/
			int result = template.update(sql, msgDetail.getFd_objectId(),msgDetail.getTask_name(), msgDetail.getReceiver_id()
										, msgDetail.getReceiver_phone(), msgDetail.getSend_result(), msgDetail.getReceive_time()
										, msgDetail.getInfo_id(), msgDetail.getReceipt(), msgDetail.getError_code(),
										msgDetail.getSend_state(),msgDetail.getReceiver_org(),msgDetail.getReceiver_name()
					);
			if(result>0){
				log.info("保存成功");
				 return "1";
			}else{
			 log.info("保存失败");
			 return "0";}
		} catch (SQLException e) {
			this.log.error(e.getMessage(), e);
			throw new ClassNotFoundException("DAO　Layou: 消息保存", e);
		} 
		
	}
	
	/**
	 * 用于保存短信明细(接口)
	 * 2018-2-1吴玉祥添加
	 * @param msgDetail
	 * @return
	 * @throws ClassNotFoundException
	 */
	public String saveMsgDetail1(MsgDetail msgDetail) throws ClassNotFoundException {
		log.info("开始保存短信明细");
		ORGTemplate template = new ORGTemplate();
		MsgDao msgDao = new MsgDao();
		String sendMsg = "";
		String sql = "INSERT INTO MSG_SEND_DETAIL"
				+"(FD_OBJECTID,TASK_NAME,RECEIVER_ID,RECEIVER_PHONE,SEND_RESULT,RECEIVE_TIME,INFO_ID,RECEIPT,ERROR_CODE,SEND_STATE,RECEIVER_ORG,RECEIVER_NAME)"
				+ "VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			msgDetail.setFd_objectId(IdentityGenerator.getIdentityGenerator().gerenalIdentity(99));
			/*st.setString(1, msgDetail.getFd_objectId());
			st.setString(2, msgDetail.getTask_name());
			st.setString(3, msgDetail.getReceiver_id());
			st.setString(4, msgDetail.getReceiver_phone());
			st.setString(5, msgDetail.getSend_result());
			st.setString(6, msgDetail.getReceive_time());
			st.setString(7, msgDetail.getInfo_id());
			//st.setString(8, msgDetail.getMsg_content());
			st.setString(8, msgDetail.getReceipt());
			st.setString(9, msgDetail.getError_code());
			st.setString(10, msgDetail.getSend_state());*/
			int result = template.update(sql, msgDetail.getFd_objectId(),msgDetail.getTask_name(), msgDetail.getReceiver_id()
										, msgDetail.getReceiver_phone(), msgDetail.getSend_result(), msgDetail.getReceive_time()
										, msgDetail.getInfo_id(), msgDetail.getReceipt(), msgDetail.getError_code(),
										msgDetail.getSend_state(),msgDetail.getReceiver_org(),msgDetail.getReceiver_name()
					);
			if(result>0){
				log.info("保存短信明细成功，马上开始短信接口");
					String msgID=msgDetail.getFd_objectId();
					boolean flag = msgDao.sendShortMessage(msgDetail.getReceiver_phone(), msgDetail.getMsg_content(), msgID);
					if(flag) {
						msgDao.updateMsgDetail("DELIVRD",msgID);
						sendMsg = "1";//发送成功
					}else{
						msgDao.updateMsgDetail("fail",msgID);
						sendMsg = "0";//发送失败
					}
			}else{
				log.info("保存失败");
			}
			return sendMsg;
		} catch (SQLException e) {
			this.log.error(e.getMessage(), e);
			throw new ClassNotFoundException("DAO　Layou: 消息保存", e);
		} 
		
	}
	
	public String getSendData(String user_id){
		ORGTemplate template = new ORGTemplate();
		String sql ="select distinct a.fd_objectid,a.task_name,a.event_id,a.module,a.send_type,a.send_time,a.msg_content "
				+ " from msg_send_info a,msg_send_detail b"
				+ " where a.fd_objectid = b.info_id and a.recorder_id =? "
				+ " order by a.send_time desc";
		String sql1 = "";
		String sql2 = "";
		log.info("sql=============="+sql);
		ArrayList<Map<String,String>> list = null;
		ArrayList<Map<String,String>> success = null;
		ArrayList<Map<String,String>> failure = null;
		StringBuffer json = new StringBuffer();
		try {
			list =template.getSerachResult(sql, user_id);
			for(int i=0;i<list.size();i++){
				sql1 = "select a.fd_objectId "
						+ " from msg_send_detail a,msg_send_info b "
						+ " where (a.send_result ='发送成功' or a.send_result ='重新发送成功') "
						+ " and b.fd_objectid=?"
						+ " and b.fd_objectid = a.info_id";
				sql2 = "select a.fd_objectId "
						+ " from msg_send_detail a,msg_send_info b "
						+ " where (a.send_result ='发送失败' or a.send_result ='重新发送失败') "
						+ " and b.fd_objectid=?"
						+ " and b.fd_objectid = a.info_id";
				success = template.getSerachResult(sql1, list.get(i).get("FD_OBJECTID"));
				failure = template.getSerachResult(sql2, list.get(i).get("FD_OBJECTID"));
				String send_result =(success.size())+"/"+(success.size()+failure.size());
				
				json.append("{");
				json.append("\"fd_objectId\":\""+list.get(i).get("FD_OBJECTID")+"\",");
				json.append("\"task_name\":\""+list.get(i).get("TASK_NAME").replaceAll("\"", "引号").replaceAll("\\\\", "斜杠")+"\",");
				json.append("\"event_id\":\""+list.get(i).get("EVENT_ID")+"\",");
				json.append("\"module\":\""+list.get(i).get("MODULE")+"\",");
				json.append("\"send_type\":\""+list.get(i).get("SEND_TYPE")+"\",");
				json.append("\"send_time\":\""+list.get(i).get("SEND_TIME")+"\",");
				json.append("\"send_result\":\""+send_result+"\",");
				json.append("\"msg_content\":\""+list.get(i).get("MSG_CONTENT").replaceAll("\n","<br>").replaceAll("\"", "引号").replaceAll("\\\\", "斜杠")+"\"");
				json.append("},");
			}
			if(list.size()>0){
				json.deleteCharAt(json.length()-1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} 
		String result ="{\"rows\":["+json.toString()+"]}";
		log.info("result==========="+result);
		return result;
	}
	public String getReceiveData(String user_id){
		ORGTemplate template = new ORGTemplate();
		String sql ="select a.fd_objectid,a.task_name,a.event_id,a.module,a.send_type,a.send_time,c.real_name as recorder_name,a.msg_content "
				+ " from msg_send_info a,msg_send_detail b,sys_user_info c "
				+ " where a.fd_objectid = b.info_id and a.recorder_id = c.user_id and b.receiver_id=? "
				+ " order by a.send_time desc";
		log.info("sql================="+sql);
		ArrayList<Map<String,String>> list = null;
		StringBuffer json = new StringBuffer();
		try {
			list =template.getSerachResult(sql, user_id);
			for(int i=0;i<list.size();i++){
				json.append("{");
				json.append("\"fd_objectId\":\""+list.get(i).get("FD_OBJECTID")+"\",");
				json.append("\"task_name\":\""+list.get(i).get("TASK_NAME").replaceAll("\"", "引号").replaceAll("\\\\", "斜杠")+"\",");
				json.append("\"event_id\":\""+list.get(i).get("EVENT_ID")+"\",");
				json.append("\"module\":\""+list.get(i).get("MODULE")+"\",");
				json.append("\"send_type\":\""+list.get(i).get("SEND_TYPE")+"\",");
				json.append("\"send_time\":\""+list.get(i).get("SEND_TIME")+"\",");
				json.append("\"recorder_name\":\""+list.get(i).get("RECORDER_NAME")+"\",");
				json.append("\"msg_content\":\""+list.get(i).get("MSG_CONTENT").replaceAll("\n","<br>").replaceAll("\"", "引号").replaceAll("\\\\", "斜杠")+"\"");
				json.append("},");
			}
			if(list.size()>0){
				json.deleteCharAt(json.length()-1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} 
		String result ="{\"rows\":["+json.toString()+"]}";
		return result;
	}
	
	public String match(String phone){
		ORGTemplate template = new ORGTemplate();
		String sql = "select a.user_id,a.real_name,b.corp_name,b.dept_name from sys_user_info a,sys_department b "
				+ "where a.dept_id = b.dept_id "
				+ "and a.mobile = ?";
		log.info("sql========"+sql);
		ArrayList<Map<String,String>> list =null;
		StringBuffer json = new StringBuffer();
		try {
			list = template.getSerachResult(sql, phone);
			if(!(list==null||list.size()==0)){
				json.append("{");
				json.append("\"user_id\":\""+list.get(0).get("USER_ID")+"\",");
				json.append("\"user_name\":\""+list.get(0).get("REAL_NAME")+"\",");
				json.append("\"user_dept\":\""+list.get(0).get("DEPT_NAME")+"\",");
				json.append("\"user_corp\":\""+list.get(0).get("CORP_NAME")+"\"");
				json.append("}");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return json.toString();
	}
	
	public String getNumData(String user_id){
		ORGTemplate template = new ORGTemplate();
		String sTaskNum = "";
		String rTaskNum = "";
		String sMsgNum = "";
		String rMsgNum = "";
		//用户短信发送任务数
		String sql1 ="select count(fd_objectId) from msg_send_info where recorder_id = ?";
		//用户短信接收任务数
		String sql2 ="select count(a.fd_objectId) from msg_send_info a, msg_send_detail b where a.fd_objectId = b.info_id and  b.receiver_id = ?";
		//用户短信发送条数
		String sql3 ="select count(b.fd_objectId) from msg_send_info a, msg_send_detail b where a.fd_objectId = b.info_id and  a.recorder_id = ?";
		//用户短息接收条数
		String sql4 ="select count(fd_objectId) from msg_send_detail where receiver_id = ?";
		log.info("用户短信发送任务数sql1============="+sql1);
		log.info("用户短信接收任务数sql2============="+sql2);
		log.info("用户短信发送条数sql3============="+sql3);
		log.info("用户短信接收条数sql4============="+sql4);
		StringBuffer json = new StringBuffer();
		try {
			sTaskNum = template.getResultList(sql1, user_id).get(0);
			rTaskNum = template.getResultList(sql2, user_id).get(0);
			sMsgNum = template.getResultList(sql3, user_id).get(0);
			rMsgNum = template.getResultList(sql4, user_id).get(0);
			json.append("{");
			json.append("\"sTaskNum\":\""+sTaskNum+"\",");
			json.append("\"rTaskNum\":\""+rTaskNum+"\",");
			json.append("\"sMsgNum\":\""+sMsgNum+"\",");
			json.append("\"rMsgNum\":\""+rMsgNum+"\"");
			json.append("}");
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return json.toString();
	}

	public String getRecentData(String user_id, String name) {
		ORGTemplate template = new ORGTemplate();
		ArrayList<Map<String,String >> list = new ArrayList<Map<String,String>>();
		String sql ="";
		String nameCondition= " and (login_name like'%"+name+"%' or user_name like'%"+name+"%')";
		
		//本人发的短信的接收者
		String receiverSql ="select distinct top 200 b.receiver_id as user_id,d.real_name as user_name,d.login_name as login_name,c.corp_name as corp_name,c.dept_name as dept_name,d.mobile as phone,b.receive_time as temp_date"
				+ " from msg_send_info a,msg_send_detail b,sys_department c,sys_user_info d"
				+ " where a.recorder_id = ? and d.user_id = b.receiver_id and c.dept_id = d.dept_id";
		//本人收到的短信的发送者
		String recorderSql ="select distinct top 200 a.recorder_id as user_id,d.real_name as user_name,d.login_name as login_name,c.corp_name as corp_name,c.dept_name as dept_name,d.mobile as phone,b.receive_time as temp_date"
				+ " from msg_send_info a,msg_send_detail b,sys_department c,sys_user_info d"
				+ " where b.receiver_id = ? and d.user_id = b.receiver_id and c.dept_id = d.dept_id";
		//按时间倒序，按id去重
		String sql1 ="select user_id,login_name,user_name ,corp_name ,dept_name,phone,temp_date "
				+ " from "
				+ " ("
				+ " select row_number() over(partition by user_id order by temp_date) as num, * "
				+ " from"
				+ " ("
				+ receiverSql
				+ " union "
				+ recorderSql
				+ " )"
				+ " ) as t"
				+ " where t.num =1";
		
		if("".equals(name)||name==null){//初始加载
			sql = sql1+ " order by temp_date desc";
		}else{
			sql = sql1+nameCondition+" order by temp_date desc";
		}
		
		log.info("sql============="+sql);
		StringBuffer json = new StringBuffer();
		try {
			list = template.getSerachResult(sql, user_id,user_id);
			for(int i=0;i<list.size();i++){
				String userId = list.get(i).get("USER_ID");
				String userName = list.get(i).get("USER_NAME");
				String org_name = list.get(i).get("CORP_NAME");
				String dept_name = list.get(i).get("DEPT_NAME");
				String phone = getReceivePhone(list.get(i).get("USER_ID"));
				json.append("{");
				json.append("\"type\":\""+"1"+"\",");
				json.append("\"userId\":\""+userId+"\",");
				json.append("\"userName\":\""+userName+"\",");
				json.append("\"org_name\":\""+org_name+"\",");
				json.append("\"dept_name\":\""+dept_name+"\",");
				if(phone==null||"".equals(phone)){
					json.append("\"phone\":\""+list.get(i).get("MOBILE")+"\"");
				}else{
					json.append("\"phone\":\""+phone+"\"");
				}
				json.append("},");
			}
			if(list.size()>0){
				json.deleteCharAt(json.length()-1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		String result = "{\"rows\":["+json.toString()+"]}";
		log.info("result========="+result);
		return result;
	}



	public String getOrgData(String dept_id) {
		if(!"".equals(dept_id)){
			dept_id = dept_id.replace("999|", "");
		}
		ORGTemplate template = new ORGTemplate();
		ArrayList<Map<String,String>> list = new  ArrayList<Map<String, String>>();
		StringBuffer json = new StringBuffer();
		String sql ="select top 200 a.user_id ,a.real_name,b.corp_name,b.dept_name,a.mobile "
				+ " from sys_user_info a,sys_department b "
				+ " where a.dept_id =b.dept_id and a.dept_id = ? ";
		log.info("sql============="+sql);
		try {
				list = template.getSerachResult(sql, dept_id);
		} catch (Exception e) {
			e.printStackTrace();
		}
		for(int i=0;i<list.size();i++){
			String userId = list.get(i).get("USER_ID");
			String userName = list.get(i).get("REAL_NAME");
			String org_name = list.get(i).get("CORP_NAME");
			String dept_name = list.get(i).get("DEPT_NAME");
			String phone = getReceivePhone(list.get(i).get("USER_ID"));
			json.append("{");
			json.append("\"type\":\""+"2"+"\",");
			json.append("\"userId\":\""+userId+"\",");
			json.append("\"userName\":\""+userName+"\",");
			json.append("\"org_name\":\""+org_name+"\",");
			json.append("\"dept_name\":\""+dept_name+"\",");
			if(phone==null||"".equals(phone)){
				json.append("\"phone\":\""+list.get(i).get("MOBILE")+"\"");
			}else{
				json.append("\"phone\":\""+phone+"\"");
			}
			json.append("},");
		}
		if(list.size()>0){
			json.deleteCharAt(json.length()-1);
		}
		String result ="{\"rows\":["+json.toString()+"]}";
		return result;
	}
	
	public String getNameData(String name) {
		ORGTemplate template = new ORGTemplate();
		ArrayList<Map<String,String>> list = new  ArrayList<Map<String, String>>();
		StringBuffer json = new StringBuffer();
		String sql ="select top 200 a.user_id ,a.real_name,b.corp_name,b.dept_name,a.mobile "
				+ " from sys_user_info a,sys_department b "
				+ " where a.dept_id = b.dept_id"
				+ " and (a.login_name like '%"+name+"%' or a.real_name like '%"+name+"%')";
		log.info("sql============="+sql);
		try {
			list = template.getSerachResult(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		for(int i=0;i<list.size();i++){
			String userId = list.get(i).get("USER_ID");
			String userName = list.get(i).get("REAL_NAME");
			String org_name = list.get(i).get("CORP_NAME");
			String dept_name = list.get(i).get("DEPT_NAME");
			String phone = getReceivePhone(list.get(i).get("USER_ID"));
			json.append("{");
			json.append("\"type\":\""+"2"+"\",");
			json.append("\"userId\":\""+userId+"\",");
			json.append("\"userName\":\""+userName+"\",");
			json.append("\"org_name\":\""+org_name+"\",");
			json.append("\"dept_name\":\""+dept_name+"\",");
			if(phone==null||"".equals(phone)){
				json.append("\"phone\":\""+list.get(i).get("MOBILE")+"\"");
			}else{
				json.append("\"phone\":\""+phone+"\"");
			}
			json.append("},");
		}
		if(list.size()>0){
			json.deleteCharAt(json.length()-1);
		}
		String result ="{\"rows\":["+json.toString()+"]}";
		return result;
	}



	public String getGroup(String user_id) {
		ORGTemplate template = new ORGTemplate();
		ArrayList<Map<String,String>> list = new ArrayList<Map<String,String>>();
		StringBuffer json = new StringBuffer();
		String sql ="select group_id ,group_name,update_time ,order_number,parent_id"
				+ " from msg_group "
				+ " where creater_id = ? and is_del = '0' "
				+ " order by order_number,update_time desc";
		//应急指挥中心
		json.append("{");
		json.append("\"id\":\""+"0"+"\",");
		json.append("\"name\":\""+"贵州电网公司应急指挥中心"+"\",");
		json.append("\"order_number\":\""+"0"+"\","); 
		json.append("\"parentId\":\""+""+"\","); 
		json.append("\"parentId\":\""+""+"\","); 
		json.append("\"open\":\""+"true"+"\","); 
		json.append("\"icon\":\"/nwyj/scripts/qui/libs/icons/user_group.png\"");
		json.append("},");
		
		json.append("{");
		json.append("\"id\":\""+"1"+"\",");
		json.append("\"name\":\""+"贵州电网公司应急办"+"\",");
		json.append("\"order_number\":\""+""+"\","); 
		json.append("\"parentId\":\""+"0"+"\","); 
		json.append("\"icon\":\"/nwyj/scripts/qui/libs/icons/user_group.png\"");
		json.append("},");
		
		
		
		try {
			list = template.getSerachResult(sql, user_id);
			for(int i=0;i<list.size();i++){
				json.append("{");
				json.append("\"id\":\""+list.get(i).get("GROUP_ID")+"\",");
				json.append("\"name\":\""+list.get(i).get("GROUP_NAME")+"\",");
				json.append("\"order_number\":\""+list.get(i).get("ORDER_NUMBER")+"\","); 
				json.append("\"parentId\":\""+list.get(i).get("PARENT_ID")+"\","); 
				json.append("\"icon\":\"/nwyj/scripts/qui/libs/icons/user_group.png\"");
				json.append("},");
			}
			json.deleteCharAt(json.length()-1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		String result = "{\"nodes\":["+json.toString()+"]}";
		return result;
	}

	public String getGroupData(String group_id) {
		ORGTemplate template = new ORGTemplate();
		ArrayList<Map<String,String>> list = new ArrayList<Map<String,String>>();
		String sql = "";
		StringBuffer json = new StringBuffer();
		if(group_id.equals("0")){//应急指挥中心
			sql = "select a.inlink_index as user_id,a.inlink_name as user_name,a.phone  as mobile, d.corp_name    as corp_name,d.dept_name "
				+ " from emc_am_org_inlink a,SYS_USER_INFO b,sys_department d"
				+ " where a.is_del='0' and virtual_org_id ='dd84a5d4d4b046cca28a52a2445ee55b' and a.inlink_index = b.user_id and b.dept_id = d.dept_id"
				+ " order by sort_no asc";
		}else if(group_id.equals("1")){//应急办
			sql = "select a.inlink_index as user_id,a.inlink_name as user_name,a.phone  as mobile, d.corp_name    as corp_name,d.dept_name "
					+ " from emc_am_org_inlink a,SYS_USER_INFO b,sys_department d"
					+ " where a.is_del='0' and virtual_org_id ='9fe1b6fe993b48cfaeba714a25359d7c' and a.inlink_index = b.user_id and b.dept_id = d.dept_id"
					+ " order by sort_no asc";
		}else{
			sql = "select a.user_id,a.user_name,a.update_time,c.corp_name,c.dept_name,b.mobile "
					+ " from msg_group_data a,sys_user_info b,sys_department c "
					+ " where a.group_id =? and a.user_id = b.user_id and b.dept_id = c.dept_id and a.is_del ='0'"
					+ " order by a.update_time desc";
		}
		log.info("sql============="+sql);
		try {
			if(group_id.equals("0")||group_id.equals("1")){
				list = template.getSerachResult(sql);
			}else{
				list = template.getSerachResult(sql, group_id);
			}
			
			for(int i=0;i<list.size();i++){
				String userId = list.get(i).get("USER_ID");
				String userName = list.get(i).get("USER_NAME");
				String org_name = list.get(i).get("CORP_NAME");
				String dept_name = list.get(i).get("DEPT_NAME");
				String phone = getReceivePhone(list.get(i).get("USER_ID"));
				json.append("{");
				json.append("\"type\":\"3\",");
				json.append("\"group_id\":\""+group_id+"\",");
				json.append("\"userId\":\""+userId+"\",");
				json.append("\"userName\":\""+userName+"\",");
				json.append("\"org_name\":\""+org_name+"\",");
				json.append("\"dept_name\":\""+dept_name+"\",");
				if(phone==null||"".equals(phone)||"无".equals(phone)){
					json.append("\"phone\":\""+list.get(i).get("MOBILE")+"\"");
				}else{
					json.append("\"phone\":\""+phone+"\"");
				}
				json.append("},");
			}
			if(list.size()>0){
				json.deleteCharAt(json.length()-1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		String result ="{\"rows\":["+json.toString()+"]}";
		return result;
	}



	public String createGroup(String group_name, String creater_id,String now,String json,String parent_id) {
		ORGTemplate  template = new ORGTemplate();
		String group_id = IdentityGenerator.getIdentityGenerator().gerenalIdentity(99);
		int flag1 = 0;
		int flag2 = 0;
		List<Map<String, String>> list = JsonPluginsUtil.jsonToMapList(json);
		int length = list.size();
		int num = 0;
		String sql ="select * from MSG_GROUP where is_del ='0'";
		String sql1 = "insert into msg_group (group_id,group_name,update_time,creater_id,is_del,order_number,parent_id) values (?,?,?,?,?,?,?)";
		String sql2 = "insert into msg_group_data(fd_objectid,user_id,user_name,group_id,update_time,is_del) values (?,?,?,?,?,?)";
		log.info("sql=============="+sql1);
		log.info("sql1============="+sql2);
		try {
			num = template.getSearchResult(sql).size();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		try {
			flag1 = template.update(sql1, group_id,group_name,now,creater_id,"0",num,parent_id);
			for(int i=0;i<list.size();i++){
				int result = template.update(sql2, IdentityGenerator.getIdentityGenerator().gerenalIdentity(99), 
						list.get(i).get("id"), list.get(i).get("name"),
						group_id, now,"0");
				if (result > -1) {
					flag2++;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		log.info("flag1="+flag1+"||"+"flag2="+flag2+"||"+"length="+length);
		if(length==flag2&&flag1>-1){
			return "1";//成功
		}else{
			return "0";//失败
		}
	}



	public String updateGroup(String group_id, String group_name, String json,String now) {
		ORGTemplate template = new ORGTemplate();
		int flag1 = 0;
		int flag2 = 0;
		int flag3 = 0;
		String sql = "update msg_group set group_name='"+group_name+"' where group_id =? ";
		String sql1 = "update msg_group_data set is_del = '1' where group_id =?";
		String sql2 = "insert into msg_group_data(fd_objectid,user_id,user_name,group_id,update_time,is_del) values (?,?,?,?,?,?)";
		log.info("sql=============="+sql);
		log.info("sql1============="+sql1);
		log.info("sql2============="+sql2);
		List<Map<String,String>> list = JsonPluginsUtil.jsonToMapList(json);
		int length = list.size();
		try {
			flag1 = template.update(sql, group_id);
			flag2 = template.update(sql1, group_id);
			for(int i=0;i<list.size();i++){
				int num = template.update(sql2, IdentityGenerator.getIdentityGenerator().gerenalIdentity(99), 
						list.get(i).get("id"), list.get(i).get("name"),
						group_id, now,"0");
				if(num>-1){
					flag3++;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		log.info("flag1="+flag1+"||"+"flag2="+flag2+"||"+"flag3+"+flag3+"||"+"length="+length);
		if(flag1>-1&&flag2>-1&&flag3==length){
			return "1";//成功
		}else{
			return "0";
		}
	}



	public String deleteGroup(String group_id) {
		ORGTemplate template = new ORGTemplate();
		String sql1 = "update msg_group set is_del ='1' where group_id =?";
		String sql2 = "update msg_group_data set is_del='1' where group_id =?";
		int flag1 = 0;
		int flag2 = 0;
		try {
			flag1 = template.update(sql1, group_id);
			flag2 = template.update(sql2, group_id);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(flag1>-1&&flag2>-1){
			return "1";
		}else{
			return "0";
		}
	}



	public String updateGroupOrder(String json,String targetNode) {
		List<Map<String,String>> list = JsonPluginsUtil.jsonToMapList(json);
		List<Map<String,String>> list1 = JsonPluginsUtil.jsonToMapList(targetNode);
		String sql = "update msg_group set order_number = ?  where group_id = ?";
		String sql1 = "update msg_group set parent_id = ?  where group_id = ?";
		ORGTemplate template = new ORGTemplate();
		String result = "";
		int flag1 = 0;
		int flag2 = 0;
		for(int i=0;i<list.size();i++){
			try {
				int num = template.update(sql, list.get(i).get("order_number"),list.get(i).get("id"));
				if(num>-1){
					flag1++;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		try {
			int num = template.update(sql1, list1.get(0).get("parentId"),list1.get(0).get("id"));
			if(num>-1){
				flag2++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(flag1==list.size()&&flag2>0){
			result = "1";
		}
		return result;
	}



	public String updateGroupName(String group_id, String group_name) {
		ORGTemplate template = new ORGTemplate();
		String sql = "update msg_group set group_name =? where group_id =?";
		String result = "";
		log.info("sql========"+sql);
		try {
			int num = template.update(sql, group_name,group_id);
			if(num>0){
				result = "1";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}



	public String updateUserPhone(String user_id, String real_name,String default_phone,
			String receive_phone, String all_phone) {
		String result ="";
		ORGTemplate template = new ORGTemplate();
		ArrayList<Map<String,String>> list = null;
		String sql1 = "select user_id from msg_user_phone where user_id = ?";
		String sql2 = "update msg_user_phone"
				+ " set real_name=?,default_phone=?,receive_phone=?,all_phone=?"
				+ " where user_id =?";
		String sql3 = "insert into msg_user_phone "
					+ " (user_id,real_name,default_phone,receive_phone,is_del,all_phone) "
					+ " values(?,?,?,?,?,?)";
		log.info("sql1=========="+sql1);
		log.info("sql2=========="+sql2);
		log.info("sql3=========="+sql3);
		int flag2 = 0;
		int flag3 = 0;
		try {
			list = template.getSerachResult(sql1, user_id);
			if(list.size()>0){//修改
				flag2 = template.update(sql2, real_name,default_phone,receive_phone,all_phone,user_id);
			}else{//新增
				flag3 = template.update(sql3, user_id,real_name,default_phone,receive_phone,0,all_phone);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		log.info("flag2======="+flag2+",flag3==========="+flag3);
		if(flag2>0||flag3>0){
			result = "1";
		}
		return result;
	}



	public String getPhone(String user_id) {
		ORGTemplate template = new ORGTemplate();
		StringBuffer json = new StringBuffer();
		ArrayList<Map<String,String>> list = new ArrayList<Map<String,String>>();
		String sql = "select default_phone,receive_phone,all_phone from msg_user_phone where user_id = ?";
		log.info("sql==============="+sql);
		try {
			list = template.getSerachResult(sql, user_id);
			if(list.size()>0){
				json.append("{");
				json.append("\"default_phone\":\""+list.get(0).get("DEFAULT_PHONE")+"\",");
				json.append("\"receive_phone\":\""+list.get(0).get("RECEIVE_PHONE")+"\",");
				json.append("\"all_phone\":\""+list.get(0).get("ALL_PHONE")+"\"");
				json.append("}");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		String result = json.toString();
		log.info("result======="+result);
		return result;
	}

	public String getDeptId(String user_id) {
		ORGTemplate template = new ORGTemplate();
		ArrayList<Map<String,String>> list = new ArrayList<Map<String,String>>();
		String sql ="select a.dept_id,b.corp_name from sys_user_info a,sys_department b where user_id =? and a.dept_id = b.dept_id";
		try {
			list = template.getSerachResult(sql, user_id);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "{"
				+ "\"dept_id\":\""+list.get(0).get("DEPT_ID")+"\","
				+ "\"corp_name\":\""+list.get(0).get("CORP_NAME")+"\""
				+ "}";
	} 

	public String getDataByTaskId(String task_id) {
		ORGTemplate template = new ORGTemplate();
		StringBuffer json = new StringBuffer();
		ArrayList<Map<String,String>> list = null;
		ArrayList<Map<String,String>> success = null;
		ArrayList<Map<String,String>> failure = null;
		String sql ="select distinct a.fd_objectid,a.task_name,a.module,a.submodule,a.is_emergency,a.event_id, a.send_type,c.real_name,a.recorder_org,a.send_time,a.msg_content"
				+ " from msg_send_info a,msg_send_detail b ,sys_user_info c"
				+ " where a.fd_objectid = b.info_id and a.recorder_id=c.user_id and a.fd_objectid =? "
				+ " order by a.send_time desc";
		String sql1 = "select a.fd_objectId "
				+ " from msg_send_detail a,msg_send_info b "
				+ " where a.send_result <>'发送失败' "
				+ " and b.fd_objectid=?"
				+ " and b.fd_objectid = a.info_id";
		String sql2 = "select a.fd_objectId "
				+ " from msg_send_detail a,msg_send_info b "
				+ " where a.send_result ='发送失败' "
				+ " and b.fd_objectid=?"
				+ " and b.fd_objectid = a.info_id";
		
		log.info("sql==============="+sql);
		log.info("sql1=============="+sql1);
		log.info("sql2=============="+sql2);
		try {
			list = template.getSerachResult(sql, task_id);
			success = template.getSerachResult(sql1, task_id);
			failure = template.getSerachResult(sql2, task_id);
			String send_result =(success.size())+"/"+(success.size()+failure.size());
			if(list.size()>0){
				json.append("{");
				json.append("\"task_name\":\""+list.get(0).get("TASK_NAME").replaceAll("\"", "引号").replaceAll("\\\\", "斜杠")+"\",");
				json.append("\"module\":\""+list.get(0).get("MODULE")+"\",");
				json.append("\"submodule\":\""+list.get(0).get("SUBMODULE")+"\",");
				String is_emergency = list.get(0).get("IS_EMERGENCY");
				if("突发事件任务短信".equals(is_emergency)){
					is_emergency="是";
				}else{
					is_emergency="否";
				}
				json.append("\"is_emergency\":\""+is_emergency+"\",");
				json.append("\"event_name\":\""+list.get(0).get("EVENT_ID")+"\",");
				json.append("\"send_type\":\""+list.get(0).get("SEND_TYPE")+"\",");
				json.append("\"send_result\":\""+send_result+"\",");
				json.append("\"recorder_name\":\""+list.get(0).get("REAL_NAME")+"\",");
				json.append("\"recorder_org\":\""+list.get(0).get("RECORDER_ORG")+"\",");
				json.append("\"send_time\":\""+list.get(0).get("SEND_TIME")+"\",");
				json.append("\"msg_content\":\""+list.get(0).get("MSG_CONTENT").replaceAll("\n","<br>").replaceAll("\"", "引号").replaceAll("\\\\", "斜杠").replaceAll(" ", "&nbsp;")+"\"");
				json.append("}");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return json.toString();
	}



	public String getDetailByTaskId(String task_id) {
		ORGTemplate template = new ORGTemplate();
		ArrayList<Map<String,String>> list = new ArrayList<Map<String,String>>();
		StringBuffer json = new StringBuffer();
		String sql ="select a.fd_objectId,a.receiver_org,a.receiver_name,a.receiver_phone,a.receive_time,a.send_result,b.event_id,b.module from msg_send_detail a,msg_send_info b "
				+ " where a.info_id = b.fd_objectId and a.info_id = ?"
				+ " order by a.receive_time desc,b.event_id desc,b.module  ";
		log.info("sql==========="+sql);
		try {
			list = template.getSerachResult(sql, task_id);
			for(int i=0;i<list.size();i++){
				json.append("{");
				json.append("\"fd_objectId\":\""+list.get(i).get("FD_OBJECTID")+"\",");
				json.append("\"receiver_org\":\""+list.get(i).get("RECEIVER_ORG")+"\",");
				json.append("\"receiver_name\":\""+list.get(i).get("RECEIVER_NAME")+"\",");
				json.append("\"receiver_phone\":\""+list.get(i).get("RECEIVER_PHONE")+"\",");
				json.append("\"receive_time\":\""+list.get(i).get("RECEIVE_TIME")+"\",");
				json.append("\"send_result\":\""+list.get(i).get("SEND_RESULT")+"\"");
				json.append("},");
			}
			if(list.size()>0){
				json.deleteCharAt(json.lastIndexOf(","));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		String result ="{\"rows\":["+json.toString()+"]}";
		return result;
	}



	public String resend(String fd_objectId) {
		ORGTemplate template = new ORGTemplate();
		/*
		 * 获取短信的相关信息
		 */
		ArrayList<Map<String,String>> list = new ArrayList<Map<String,String>>();
		String getMsgInfo = "select a.*,b.msg_content from msg_send_detail a,msg_send_info b where a.fd_objectId = ? and a.info_id = b.fd_objectId";
		log.info("获取本条短信明细sql====getMsgInfo==="+getMsgInfo);
		try {
			list = template.getSerachResult(getMsgInfo, fd_objectId);
		} catch (Exception e) {
			e.printStackTrace();
		}
		String msg_content = list.get(0).get("MSG_CONTENT");
		String receive_phone = list.get(0).get("RECEIVER_PHONE");
		/*
		 * 重新发送短信
		 */
		MsgDao  md = new MsgDao();
		boolean flag = md.sendShortMessage(msg_content,receive_phone,fd_objectId);
		int result = 0;
		if(flag==true){
			String sql ="update msg_send_detail set send_result = '重新发送成功' where fd_objectId =?";
			log.info("sql=========="+sql);
			try {
				result = template.update(sql, fd_objectId);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return String.valueOf(result);
	}
	
	public String getReceivePhone(String user_id) {
		ORGTemplate  template =  new ORGTemplate();
		String sql = "select receive_phone from msg_user_phone where user_id =?";
		log.info("getReceivePhone sql=============================="+sql);
		List<String> list = null;
		try {
			list=template.getResultList(sql, user_id);
		} catch (Exception e) {
			// TODO: handle exception
		}
		if(list.size()>0){
			return list.get(0);
		}else{
			return "";
		}
		
	}

	public String getRole(String user_id) {
		ORGTemplate template = new ORGTemplate();
		ArrayList<Map<String,String>> list =new  ArrayList<Map<String,String>>();
		String sql = "select role_id from sys_user_role_rel where user_id =?";
		log.info("getRole   sql=================="+sql);
		try {
			list = template.getSerachResult(sql, user_id);
		} catch (Exception e) {
			e.printStackTrace();
		}
		String role_id = "";
		for(int i=0;i<list.size();i++){
			role_id += list.get(i).get("ROLE_ID")+",";
		}
		if(list.size()>0){
			role_id = role_id.substring(0, role_id.length()-1);
		}
		String result ="{\"role_id\":\""+role_id+"\"}";
		return result;
	}

	public String saveRolePhone(String user_id, String receive_phone) {
		ORGTemplate template = new ORGTemplate();
		int flag1 = 0;
		int flag2 = 0;
		String result = "";
		String sql1 = "update sys_user_info set mobile = ? where user_id = ?";
		String sql2 = "update msg_user_phone set default_phone =?,receive_phone=? where user_id =?";
		
		log.info("sql1==================="+sql1);
		log.info("sql2==================="+sql2);
		try {
			flag1 = template.update(sql1, receive_phone.split(",")[0],user_id);
			flag2 = template.update(sql2, receive_phone.split(",")[0],receive_phone,user_id);
			log.info("flag1========================="+flag1);
			log.info("flag2========================="+flag2);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if(flag1>0&&flag2>0){
			result = "success";
		}
		log.info("result====================="+result);
		return result;
	}
}