GZ/nwyj/.svn/pristine/03/030d536129aac3ce09fdf01e90ae5bc1ffabfe5c.svn-base

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

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.nio.channels.FileChannel;
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 java.util.Random;
import java.util.UUID;

import net.sf.json.JSONArray;

import org.apache.log4j.Logger;

import com.formaction.Parameter;
import com.formaction.vo.Msg;
import com.persistence.DaMengBasicApp;
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.lz.system.sms.dao.ws.MsgDao;
import com.sinosoft.lz.system.sms.vo.MSGBean;
import com.sysmodel.datamodel.xmlmodel.ModelFactory;
import com.sysmodel.datamodel.xmlmodel.able.SysModel;

public class SmsDao {
	private Logger	log	= Logger.getLogger(this.getClass());
	private String fileName="";

	/**
	 * 保存短信和调用保存短信文件
	 * @param title
	 * @param recList
	 * @param time
	 * @param content
	 * @param sendId
	 * @return
	 * @throws ClassNotFoundException
	 */
	public Msg saveSms(String title,List<String> recList,String time,String content,String sendId) throws ClassNotFoundException{
	Msg m=new Msg();
	Connection conn = null;
	PreparedStatement st = null;
	DbConnection db = new DbConnection();
//	log.info("recid.size===="+recList.size());
	try {
		String sql = "INSERT INTO SYS_MSG_MANAGE(REC_USER_TEL,MSG_TYPE,MSG_CONTENT,SEND_USER_TEL,MSG_STAT,MSG_TIME,FD_OBJECTID,IS_DEL,MSG_TITLE)VALUES(?,?,?,?,?,?,?,?,?)";
		conn = db.getConnection();
		int num=0;
		conn.setAutoCommit(false);
		st = conn.prepareStatement(sql);
		List<String>fdList=new ArrayList<String>();
		for(int i=0;i<recList.size();i++){
			String fd_id= getFdId(776+i,(i+1));/*IdentityGenerator.getIdentityGenerator().gerenalIdentity(776);*/
			fdList.add(fd_id);
			st.setString(1, recList.get(i));
			st.setString(2, "短信发送");
//			log.info("formatDate(new Date())==="+formatDate(new Date()));
			st.setString(3, content);
			st.setString(4,sendId);
			st.setString(5, "0");
			st.setString(6, time);
			st.setString(7, fd_id);
			st.setString(8, "0");
			st.setString(9, title);
			st.executeUpdate();
			num++;
		}
//		log.info("num====="+num);
		if(num==recList.size()){
			log.info("保存成功");
			 conn.commit();
			 m.setSucsess(new Boolean(true).toString());
			 m.setInfo(Parameter.DELETE_SUCSESS);
			
			 if(content.length()<=64){
				 sendNote(recList,fdList,content);
			 }else{
				 saveMsg(recList,fdList,content);
			 }
			 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);
	}
	}
	
	
	/**
	 * 保存短信和调用保存短信文件
	 * @param title
	 * @param recList
	 * @param time
	 * @param content
	 * @param sendId
	 * @return
	 * @throws ClassNotFoundException
	 */
	public Msg saveMessage(String realName,List<String> recList,String time,String content,String sendId,List<String>people,String type) throws ClassNotFoundException{
		log.info("lianjian,修改短信版本日期:2017-11-03");
	
		
	Msg m=new Msg();
	MsgDao msgDao = new MsgDao();
	Connection conn = null;
	PreparedStatement st = null;
	DbConnection db = new DbConnection();
	//String msgId = msgDao.getSmID();
//	log.info("recid.sizesaveMessage===="+recList.size());
	try {
		String sql = "INSERT INTO SYS_MSG_MANAGE(REC_USER_TEL,MSG_TYPE,MSG_CONTENT,SEND_USER_TEL,MSG_STAT,MSG_TIME,FD_OBJECTID,IS_DEL,REAL_NAME,REC_NAME)VALUES(?,?,?,?,?,?,?,?,?,?)";
		conn = db.getConnection();
		int num=0;
		int num1=0;
		//boolean flag = false;
		conn.setAutoCommit(false);
		st = conn.prepareStatement(sql);
		List<String>fdList=new ArrayList<String>();
		
		String[] mobiles = new String[recList.size()];
		
		Map<String,String> map = new HashMap<String, String>();
		for(int i=0;i<recList.size();i++){
			String tel=recList.get(i);
			if(!"".equals(tel)&&tel!=null){//接收人号码
				mobiles[i]=tel;
				num1++;
			String fd_id= getFdId(776+i,(i+1));/*IdentityGenerator.getIdentityGenerator().gerenalIdentity(776)*/
			fdList.add(fd_id);
			
			map.put(fd_id, tel);
			
			st.setString(1, recList.get(i));//接收人号码
			st.setString(2, type); //短信类型
//			log.info("formatDate(new Date())==="+formatDate(new Date()));
			st.setString(3, content);//短信内容
			st.setString(4,sendId); //发送人员的ID
			st.setString(5, "0");//短信状态
			st.setString(6, time);//创建时间
			st.setString(7, fd_id);//主键
			st.setString(8, "0"); //是否删除
			st.setString(9, realName);//短信发送人
			if(i<people.size()){
				st.setString(10, people.get(i));
							//短信收件人
			}else{
				st.setString(10, "");
			}
			/*st.setString(11, deptName);*/
			
			//st.setString(11, msgId);
			st.executeUpdate();
			num++;
		}
		}
		
		if(num==num1){
			log.info("保存成功");
			 conn.commit();
			// m.setSucsess(new Boolean(true).toString());
			 m.setInfo(Parameter.CREATE_SUCSESS);
			//lj
			for (int i = 0; i < fdList.size(); i++) {
				String msgID=fdList.get(i);
				String[] phones={map.get(msgID)};
				boolean flag = msgDao.sendShortMessage(phones, content, msgID);
				if(flag) {
					msgDao.updateMsg("DELIVRD",msgID);
				}else{
					msgDao.updateMsg("fail",msgID);
					
				}
			}
			 m.setSucsess(new Boolean(true).toString()); 
				
			 
		
			 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);
	}
	}
	

	/**
	 * 重写   ----ljp
	 * 保存短信和调用保存短信文件
	 * @param tempList
	 * @param type
	 * @return
	 * @throws ClassNotFoundException
	 */
	public Msg saveMessage(List<MSGBean> tempList,String type) throws ClassNotFoundException{
	Msg m=new Msg();
	Connection conn = null;
	PreparedStatement st = null;
	DbConnection db = new DbConnection();
	String cont = "";
	log.info("recid.sizesaveMessage===="+tempList.size());
	try {
		String sql = "INSERT INTO SYS_MSG_MANAGE(REC_USER_TEL,MSG_TYPE,MSG_CONTENT,SEND_USER_TEL,MSG_STAT,MSG_TIME,FD_OBJECTID,IS_DEL,REAL_NAME,REC_NAME)VALUES(?,?,?,?,?,?,?,?,?,?)";
		conn = db.getConnection();
		int num=0;
		int num1=0;
		//boolean flag = false;
		conn.setAutoCommit(false);
		st = conn.prepareStatement(sql);
		List<String>fdList=new ArrayList<String>();
		List<String>recList1=new ArrayList<String>();
		if(tempList.size()>0){
			cont = tempList.get(0).getMSG_CONTENT();
		}
		for(int i=0;i<tempList.size();i++){
			MSGBean  msg = tempList.get(i);
			String tel=msg.getREC_USER_TEL();
			if(!"".equals(tel)&&tel!=null){
				num1++;
			String fd_id= getFdId(776+i,(i+1));/*IdentityGenerator.getIdentityGenerator().gerenalIdentity(776)*/
			msg.setFD_OBJECTID(fd_id);
			st.setString(1, msg.getREC_USER_TEL());
			st.setString(2, type);
			st.setString(3, msg.getMSG_CONTENT());
			st.setString(4,msg.getSEND_USER_TEL());
			st.setString(5, "0");
			st.setString(6, msg.getMSG_TIME());
			st.setString(7, fd_id);
			st.setString(8, "0");
			st.setString(9, msg.getREAL_NAME());
			st.setString(10, msg.getREC_NAME());
			st.executeUpdate();
			num++;
		}
		}
		if(num==num1){
			log.info("保存成功");
			 conn.commit();
			 m.setSucsess(new Boolean(true).toString());
			 m.setInfo(Parameter.DELETE_SUCSESS);
			
			 if(cont.length()<=64){
				 sendNote(tempList);
				/* if(flag){
					 sendNote(recList2,fdList2,content);
				 }*/
				 
			 }else{
				 saveMsg(tempList);
				/* if(flag){
					 saveMsg(recList2,fdList2,content);
				 }*/
			 }
			 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);
	}
	}
	
	/**
	 * 重写 ---ljp
	 *  保存短信文件
	 * @param tempList
	 * @return
	 */
	private boolean saveMsg(List<MSGBean> tempList) {
		try {
//			log.debug("发送短信内容" + content);
			long t1=System.currentTimeMillis();
			// 先根据类型获取接收此预警的用户群的手机号列表
			File smsFile = createSMSFile();
			StringBuffer fileContent =new StringBuffer();
			for(int i=0;i<tempList.size();i++){
				MSGBean msg = tempList.get(i);
				 fileContent.append(createSMSContent(msg.getREC_USER_TEL(), msg.getFD_OBJECTID(),msg.getMSG_CONTENT()));
			}
			fileContent.append("end");
//			log.info("短信文件内容====="+fileContent.toString());
			writeFile(smsFile, fileContent+"\n");
//			Process process = Runtime.getRuntime().exec("mv /home/beaimon/user/pzc/sms/"+fileName+" /home/beaimon/gts02/sms/");
			long t2 = System.currentTimeMillis();
			log.info("消耗时间为：" + (t2 - t1));
		} catch (Exception e) {
			log.error("发送短信异常 type:", e);
			return false;
		}
		return true;
		
	}
	
	/**
	 * 重写  ----ljp
	 *  保存短信文件
	 * @param tempList
	 * @return
	 */
	private boolean sendNote(List<MSGBean> tempList) {
		try {
//			log.debug("发送短信内容" + content);
			long t1=System.currentTimeMillis();
			// 先根据类型获取接收此预警的用户群的手机号列表
			File smsFile = createSMSFile();
			StringBuffer fileContent =new StringBuffer();
			for(int i=0;i<tempList.size();i++){
				MSGBean msg = tempList.get(i);
				 fileContent.append(createSMSContent(msg.getREC_USER_TEL(), msg.getFD_OBJECTID(),msg.getMSG_CONTENT()));
			}
			fileContent.append("end");
//			log.info("短信文件内容====="+fileContent.toString());
			writeFile(smsFile, fileContent+"\n");
//			Process process = Runtime.getRuntime().exec("mv /home/beaimon/user/pzc/sms/"+fileName+" /home/beaimon/gts02/sms/");
			long t2 = System.currentTimeMillis();
			log.info("消耗时间为：" + (t2 - t1));
		} catch (Exception e) {
			log.error("发送短信异常 type:", e);
			return false;
		}
		return true;
		
	}

	/**
	 * 初始化grid表格和查询grid表格的内容(范本)
	 * @param modelTitle
	 * @param modelContent
	 * @param modelType
	 * @param modelRange
	 * @param deptName
	 * @param userName
	 * @param modelTime
	 * @return
	 */
	public String getmodelGrid(String modelTitle,String modelContent,String modelType,String modelRange,String deptName,String userName,String modelTime ,String end_time,int page,int pageSize,String dept){
		String sql="SELECT TOP "+(page-1)*pageSize+","+pageSize+" a.*,C.REAL_NAME,D.DEPT_NAME FROM SYS_MSG_MODEL A,SYS_DEPT_USER B,SYS_USER_INFO C ,SYS_DEPARTMENT D WHERE A.MSG_MODEL_USER=c.USER_ID AND b.user_id=C.USER_ID and b.dept_id=d.dept_id and a.is_del='0' and (D.DEPT_NAME ='"+dept+"' or a.msg_model_range='1')";
		String sql1="SELECT a.*,C.REAL_NAME,D.DEPT_NAME FROM SYS_MSG_MODEL A,SYS_DEPT_USER B,SYS_USER_INFO C ,SYS_DEPARTMENT D WHERE A.MSG_MODEL_USER=c.USER_ID AND b.user_id=C.USER_ID and b.dept_id=d.dept_id and a.is_del='0' and (D.DEPT_NAME = '"+dept+"' or a.msg_model_range='1')";
//		log.info(sql1+"==============getmodelGrid.sql1");
		Connection conn = null;
		Statement stat = null;
		Statement stat1 = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		DbConnection db = new DbConnection();
		if(!"".equals(modelTitle)){
			sql+="AND A.MSG_MODEL_TITLE LIKE '%"+modelTitle+"%'";
			sql1+="AND A.MSG_MODEL_TITLE LIKE '%"+modelTitle+"%'";
		}
		if(!"".equals(modelContent)){
			sql+="AND A.MSG_MODEL_CONTENT LIKE '%"+modelContent+"%'";
			sql1+="AND A.MSG_MODEL_CONTENT LIKE '%"+modelContent+"%'";
		}
		if(!"".equals(modelType)){
			sql+="AND A.MSG_MODEL_TYPE = '"+modelType+"'";
			sql1+="AND A.MSG_MODEL_TYPE = '"+modelType+"'";
		}
		if(!"".equals(modelRange)){
			sql+="AND A.MSG_MODEL_RANGE = '"+modelRange+"'";
			sql1+="AND A.MSG_MODEL_RANGE = '"+modelRange+"'";
		}
		if(!"".equals(deptName)){
			sql+="AND D.DEPT_NAME LIKE '%"+deptName+"%'";
			sql1+="AND D.DEPT_NAME LIKE '%"+deptName+"%'";
			
		}
		if(!"".equals(userName)){
			sql+="AND C.REAL_NAME LIKE '%"+userName+"%'";
			sql1+="AND C.REAL_NAME LIKE '%"+userName+"%'";
			
		}
		if(!"".equals(modelTime)&&!"".equals(end_time)){
			sql+="AND SUBSTR(A.MSG_MODEL_TIME,'0','10') >='"+modelTime.substring(0,10)+"' AND SUBSTR(A.MSG_MODEL_TIME,'0','10')<='"+end_time.substring(0,10)+"'";
			sql1+="AND SUBSTR(A.MSG_MODEL_TIME,'0','10') >='"+modelTime.substring(0,10)+"' AND SUBSTR(A.MSG_MODEL_TIME,'0','10')<='"+end_time.substring(0,10)+"'";
		}
		if(!"".equals(modelTime)&&"".equals(end_time)){
			sql +="AND SUBSTR(A.MSG_MODEL_TIME,'0','10') >='"+modelTime.substring(0, 10)+"'";
			sql1 +="AND SUBSTR(A.MSG_MODEL_TIME,'0','10') >='"+modelTime.substring(0, 10)+"'";
		}
		if("".equals(modelTime)&&!"".equals(end_time)){
			sql +="AND SUBSTR(A.MSG_MODEL_TIME,'0','10') <= '"+end_time.substring(0, 10)+"'";
			sql1 +="AND SUBSTR(A.MSG_MODEL_TIME,'0','10') <= '"+end_time.substring(0, 10)+"'";
		}
		sql += "ORDER BY MSG_MODEL_TIME DESC";
		sql1 += "ORDER BY MSG_MODEL_TIME DESC";
		log.info("getmodelGrid.sql===="+sql);
		List list = new ArrayList<String>();
		StringBuffer json = new StringBuffer();
		int num = 0;
		try {
			conn = db.getConnection();
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			
			while(rs.next()){
				json.append("{");
				json.append("\"FD_OBJECTID\""+":"+"\""+rs.getString("FD_OBJECTID")+"\""+",");
				json.append("\"MSG_MODEL_TYPE\""+":"+"\""+rs.getString("MSG_MODEL_TYPE")+"\""+",");
				json.append("\"MSG_MODEL_TITLE\""+":"+"\""+rs.getString("MSG_MODEL_TITLE")+"\""+",");
				json.append("\"MSG_MODEL_CONTENT\""+":"+"\""+rs.getString("MSG_MODEL_CONTENT")+"\""+",");
				json.append("\"MSG_MODEL_RANGE\""+":"+"\""+rs.getString("MSG_MODEL_RANGE")+"\""+",");
				json.append("\"MSG_MODEL_COMP\""+":"+"\""+rs.getString("MSG_MODEL_COMP")+"\""+",");
				json.append("\"MSG_MODEL_USER\""+":"+"\""+rs.getString("MSG_MODEL_USER")+"\""+",");
				json.append("\"MSG_MODEL_TIME\""+":"+"\""+rs.getString("MSG_MODEL_TIME")+"\""+",");
				json.append("\"IS_DEL\""+":"+"\""+rs.getString("IS_DEL")+"\""+",");
				json.append("\"REAL_NAME\""+":"+"\""+rs.getString("REAL_NAME")+"\""+",");
				json.append("\"DEPT_NAME\""+":"+"\""+rs.getString("DEPT_NAME")+"\"");
				json.append("},");
			}
			if (json.lastIndexOf(",") > -1) {
				json.deleteCharAt(json.lastIndexOf(","));
			}
			list.add(json);
			
			
			stat1 = conn.createStatement();
			rs1 = stat1.executeQuery(sql1);
			while(rs1.next()){
			num+=1;
			}
		} catch (Exception e) {
			log.error(e.getMessage());
		}finally{
			db.close(rs);
			db.close(stat);
			db.close(rs1);
			db.close(stat1);
			db.close(conn);
		}
//		log.info("获取范本条数===="+num);
		String result = "{\"pager.totalRows\":"+num+",\"pager.pageNo\":"+page+","+"\"rows\":" + list.toString()+ "}";
//		log.info(result+"=========result");
		return result;
	}
	/**获取短信表格
	 * 
	 * @param recName
	 * @param recTel
	 * @param msg_type
	 * @param msg_content
	 * @param send_user
	 * @param send_dept
	 * @param msg_stat
	 * @param msg_time
	 * @return
	 */
	public String getSmsGrid(String title,String recName,String recTel,String sub_stat,String msg_content,String send_user,String send_dept,String msg_stat,String msg_time,String end_time,int page,int pageSize,String id,String dept_id){
		String sql="SELECT TOP  "+(page-1)*pageSize+","+pageSize+" A.FD_OBJECTID,A.MSG_TIME,A.REC_NAME,A.REC_USER_TEL,A.MSG_TYPE,A.MSG_STAT,A.REPLY_CONTENT,B.REAL_NAME SENDNAME,C.DEPT_NAME,A.MSG_CONTENT ,A .MSG_TITLE,A.SUB_STAT  FROM SYS_MSG_MANAGE A,SYS_USER_INFO B,SYS_DEPT_USER D,SYS_DEPARTMENT C WHERE A.SEND_USER_TEL=D.USER_ID AND A.SEND_USER_TEL=B.USER_ID AND C.DEPT_ID=D.DEPT_ID AND A.IS_DEL='0' and ((a.SEND_USER_TEL='"+id+"') or (A.REC_USER_TEL in (select mobile from sys_user_info g, sys_dept_user h ,sys_department k where g.USER_ID=h.USER_ID and h.dept_id=k.dept_id and k.dept_id='"+dept_id+"')))";
		String sql1 = "SELECT  A.FD_OBJECTID,A.MSG_TIME,A.REC_NAME,A.REC_USER_TEL,A.MSG_TYPE,A.MSG_STAT,A.REPLY_CONTENT,B.REAL_NAME SENDNAME,C.DEPT_NAME,A.MSG_CONTENT ,A .MSG_TITLE,A.SUB_STAT  FROM SYS_MSG_MANAGE A,SYS_USER_INFO B,SYS_DEPT_USER D,SYS_DEPARTMENT C WHERE A.SEND_USER_TEL=D.USER_ID AND A.SEND_USER_TEL=B.USER_ID AND C.DEPT_ID=D.DEPT_ID AND A.IS_DEL='0' and ((a.SEND_USER_TEL='"+id+"') or (A.REC_USER_TEL in (select mobile from sys_user_info g, sys_dept_user h ,sys_department k where g.USER_ID=h.USER_ID and h.dept_id=k.dept_id and k.dept_id='"+dept_id+"')))";
	//	String sql = "SELECT TOP "+(page-1)*pageSize+ ","+pageSize+" FD_OBJECTID,MSG_TIME,REC_NAME,REC_USER_TEL,MSG_TYPE,MSG_STAT,REAL_NAME SENDNAME ";
	//	String sql1 ="";
		Connection conn = null;
		Statement stat = null;
		Statement stat1 = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		DbConnection db = new DbConnection();
		
		if(!"".equals(title)){
			sql+=" AND A.MSG_TITLE LIKE '%"+title+"%'";
			sql1+=" AND A.MSG_TITLE LIKE '%"+title+"%'";
		}
		if(!"".equals(recName)){
			sql+=" AND A.REC_NAME LIKE '%"+recName+"%'";
			sql1+=" AND A.REC_NAME LIKE '%"+recName+"%'";
		}
		if(!"".equals(recTel)){
			sql+=" AND A.REC_USER_TEL LIKE '%"+recTel+"%'";
			sql1+=" AND A.REC_USER_TEL LIKE '%"+recTel+"%'";
		}
		if("0".equals(sub_stat)){
			sql+=" AND (A.SUB_STAT IS NULL OR A.SUB_STAT='')";
			sql1+=" AND (A.SUB_STAT IS NULL OR A.SUB_STAT='') ";
		}
		if("1".equals(sub_stat)){
			sql+=" AND A.SUB_STAT ='0'";
			sql1+=" AND A.SUB_STAT ='0'";
		}
		if("2".equals(sub_stat)){
			sql +="AND A.SUB_STAT IS NOT NULL AND A.SUB_STAT <> '0'";
			sql1 +="AND A.SUB_STAT IS NOT NULL AND A.SUB_STAT <> '0'";
		}
		if(!"".equals(msg_content)){
			sql+=" AND A.MSG_CONTENT LIKE '%"+msg_content+"%'";
			sql1+=" AND A.MSG_CONTENT LIKE '%"+msg_content+"%'";
		}
		if(!"".equals(send_user)){
			sql+=" AND B.REAL_NAME LIKE '%"+send_user+"%'";
			sql1+=" AND B.REAL_NAME LIKE '%"+send_user+"%'";
		}
		if(!"".equals(send_dept)){
			sql+=" AND C.DEPT_NAME LIKE '%"+send_dept+"%'";
			sql1+=" AND C.DEPT_NAME LIKE '%"+send_dept+"%'";
		}
		if("1".equals(msg_stat)){
			/*sql+=" AND A.MSG_STAT = '"+msg_stat+"'";
			sql1+=" AND A.MSG_STAT = '"+msg_stat+"'";*/
			log.info(msg_stat+"==========");
			/*if(msg_stat=="0"){
				sql +="AND A.MSG_STAT IS NULL";
				sql1 +="AND A.MSG_STAT IS NULL";
				log.info(sql);
			}*/
			
				sql +="AND A.MSG_STAT = 'DELIVRD'";
				sql1 +="AND A.MSG_STAT = 'DELIVRD'";
				
			
			/*if(msg_stat=="2"){
				sql +="AND A.MSG_STAT IS NOT NULL AND A.MSG_STAT <> 'DELIVRD'";
				sql1 +="AND A.MSG_STAT IS NOT NULL AND A.MSG_STAT <> 'DELIVRD'";
			}*/
		}
		if("0".equals(msg_stat)){
			sql +="AND (A.MSG_STAT IS NULL OR A.MSG_STAT='')";
			sql1 +="AND (A.MSG_STAT IS NULL OR A.MSG_STAT='')";
		}
		
		if("2".equals(msg_stat)){
			sql +="AND A.MSG_STAT IS NOT NULL AND A.MSG_STAT <> 'DELIVRD'";
			sql1 +="AND A.MSG_STAT IS NOT NULL AND A.MSG_STAT <> 'DELIVRD'";
		}
		if(!"".equals(msg_time)&&!"".equals(end_time)){
			sql+="AND SUBSTR(A.MSG_TIME ,'0','10') >='"+msg_time.substring(0, 10)+"' AND SUBSTR(A.MSG_TIME ,'0','10')<='"+end_time.substring(0,10)+"'";
			sql1+="AND SUBSTR(A.MSG_TIME ,'0','10') >='"+msg_time.substring(0, 10)+"' AND SUBSTR(A.MSG_TIME ,'0','10')<='"+end_time.substring(0,10)+"'";
		}
		if(!"".equals(msg_time)&&"".equals(end_time)){
			sql +="AND SUBSTR(A.MSG_TIME ,'0','10') >='"+msg_time.substring(0,10)+"'";
			sql1 +="AND SUBSTR(A.MSG_TIME ,'0','10') >='"+msg_time.substring(0,10)+"'";
		}
		if("".equals(msg_time)&&!"".equals(end_time)){
			sql +="AND SUBSTR(A.MSG_TIME ,'0','10') <='"+end_time.substring(0,10)+"'";
			sql1 +="AND SUBSTR(A.MSG_TIME ,'0','10') <='"+end_time.substring(0,10)+"'";
		}
		sql += "  ORDER BY MSG_TIME DESC";
		sql1 += "  ORDER BY MSG_TIME DESC";
		log.info("getSmsGrid.sql===="+sql);
		
		List list = new ArrayList<String>();
		StringBuffer json = new StringBuffer();
		int num=0;
		try {
			conn = db.getConnection();
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			/*String msg_status ;
			String sub_status ;*/
			while(rs.next()){
				/*msg_status = rs.getString("MSG_STAT");
				if(msg_status==null){
					msg_status = "发送中";
				}else if(msg_status=="DELIVRD"){
					msg_status = "发送成功";
				}else{
					
					msg_status = "发送失败";
				}*/
			/*	sub_status = rs.getString("SUB_STAT");
				if(sub_status ==null){
					sub_status = "提交中";
					
				}else if(sub_status=="0"){
					sub_status = "提交成功";
				}else{
					sub_status = "提交失败";
				}*/
				json.append("{");
				json.append("\"FD_OBJECTID\""+":"+"\""+rs.getString("FD_OBJECTID")+"\""+",");
				json.append("\"MSG_TIME\""+":"+"\""+rs.getString("MSG_TIME")+"\""+",");
				json.append("\"REC_NAME\""+":"+"\""+rs.getString("REC_NAME")+"\""+",");
				json.append("\"REC_USER_TEL\""+":"+"\""+rs.getString("REC_USER_TEL")+"\""+",");
				json.append("\"MSG_TYPE\""+":"+"\""+rs.getString("MSG_TYPE")+"\""+",");
				json.append("\"MSG_STAT\""+":"+"\""+rs.getString("MSG_STAT")+"\""+",");
				json.append("\"SENDNAME\""+":"+"\""+rs.getString("SENDNAME")+"\""+",");
				json.append("\"DEPT_NAME\""+":"+"\""+rs.getString("DEPT_NAME")+"\""+",");
				json.append("\"MSG_CONTENT\""+":"+"\""+rs.getString("MSG_CONTENT")+"\""+",");
				json.append("\"MSG_TITLE\""+":"+"\""+rs.getString("MSG_TITLE")+"\""+",");
				json.append("\"SUB_STAT\""+":"+"\""+rs.getString("SUB_STAT")+"\""+",");
				json.append("\"REPLY_CONTENT\""+":"+"\""+rs.getString("REPLY_CONTENT")+"\"");
				json.append("},");
			}
			// 去除多余逗号
			if (json.lastIndexOf(",") > -1) {
				json.deleteCharAt(json.lastIndexOf(","));
				list.add(json);
			}
			stat1 = conn.createStatement();
			rs1 = stat1.executeQuery(sql1);
			while(rs1.next()){
				num+=1;
			}
			
		} catch (Exception e) {
			log.error(e.getMessage());
		}finally{
			db.close(rs);
			db.close(stat);
			db.close(rs1);
			db.close(stat1);
			db.close(conn);
		}
		log.info("获取短信条数===="+num);
		
		String result = "{\"pager.totalRows\":"+num+",\"pager.pageNo\":"+page+","+"\"rows\":" + list + "}";
//		log.info(result+"=========result");
		return result;
	}
	/**
	 * 保存范本
	 * @param user_id
	 * @param model_title
	 * @param model_type
	 * @param model_content
	 * @param model_range
	 * @param model_time
	 * @return
	 * @throws Exception
	 */
	public Msg saveModel(String user_id,String model_title,String model_type,String model_content,String model_range,String model_time,String model_comp,String real_name,String msg_model_dept) throws Exception{
		Msg m=new Msg();
		Connection conn = null;
		PreparedStatement st = null;
		DbConnection db = new DbConnection();
		try {
		String sql="INSERT INTO SYS_MSG_MODEL(FD_OBJECTID,MSG_MODEL_TYPE,MSG_MODEL_TITLE,MSG_MODEL_CONTENT,MSG_MODEL_RANGE,MSG_MODEL_USER,MSG_MODEL_TIME,IS_DEL,MSG_MODEL_COMP,REAL_NAME,MSG_MODEL_DEPT)VALUES(?,?,?,?,?,?,?,?,?,?,?)";
		conn = db.getConnection();
		int num=0;
		conn.setAutoCommit(false);
		st = conn.prepareStatement(sql);
		List<String>fdList=new ArrayList<String>();
			String fd_id=IdentityGenerator.getIdentityGenerator()
					.gerenalIdentity(778);
			fdList.add(fd_id);
			st.setString(1, fd_id);
			st.setString(2, model_type);
//			log.info("formatDate(new Date())==="+formatDate(new Date()));
			st.setString(3, model_title);
			st.setString(4,model_content);
			st.setString(5, model_range);
			st.setString(6, user_id);
			st.setString(7, model_time);
			st.setString(8, "0");
			st.setString(9, model_comp);
			st.setString(10, real_name);
			st.setString(11, msg_model_dept);
			st.executeUpdate();
			num++;
		log.info("num====="+num);
		log.info(sql+"====saveModel.sql");
		if(num==1){
			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;
	}

	public List<String> getMsgId() throws ClassNotFoundException {
		String sql = "SELECT FD_OBJECTID FROM SYS_MSG_MANAGE";
		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 List<String> getModelId() throws ClassNotFoundException {
		String sql = "SELECT FD_OBJECTID FROM SYS_MSG_MODEL";
		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;
		}
	}
	
	private void writeFile(File file, String content) throws Exception {
		BufferedWriter bw = null;
		try {
			bw = new BufferedWriter(new OutputStreamWriter(
					new FileOutputStream(file), "GB18030"));
			// bw = new BufferedWriter(new OutputStreamWriter(
			// new FileOutputStream(file), "utf-8"));
			bw.write(content);
			bw.flush();
		} finally {
			if (bw != null) {
				bw.close();
			}
		}
	}
	

	/**
	 * 
	 * <DL>
	 * <p>
	 * <DD>组织短信内容</DD>
	 * </DL>
	 * <p>
	 */
	private String createSMSContent(String tel,String fd_id, String msg) {
		StringBuffer sb = new StringBuffer();
		// 添加手机号 12位
		sb.append(getClientName());
		// 添加短信内容 200位
		sb.append(getSerialno(fd_id));
		sb.append(getSendPort());
		sb.append(getMobileNo(tel));
		sb.append(getContent(msg));
		sb.append(getMsgType());
		sb.append(getSrr());
		sb.append("\n");
		//sb.append("end");
//		sb.append(dealStr(msg, 200));
//		// 添加优先级2位
//		sb.append("01");
//		// 添加收条标志1位
//		sb.append("0");
//		// 添加机构码 8位
//		sb.append(dealStr(getOrgan1(), 8));
		return sb.toString();
	}
	
	/**
	 * 生成客户端名称
	 * @return
	 */
	public String getClientName(){
		StringBuffer sb=new StringBuffer();
		sb.append("<clientname>");
		sb.append("S0045");
		sb.append("</clientname>");
		return sb.toString();
	}

	/**
	 * 生成短信文件序列号
	 * @return
	 */
	public String getSerialno(String fd_id){
		StringBuffer sb=new StringBuffer();
		sb.append("<serialno>");
		sb.append("S004500XX");
		sb.append("_");
		sb.append(getTimeStr());
		sb.append("_");
		sb.append(fd_id);
		sb.append("</serialno>");
		return sb.toString();
	}

	/**
	 * 生成短信文件发送端口
	 * @return
	 */
	public String getSendPort(){
		StringBuffer sb=new StringBuffer();
		sb.append("<sendport>955980045</sendport>");
		return sb.toString();
	}
	
	/**
	 * 生成短信文件接收号码
	 * @return
	 */
	public String getMobileNo(String tel){
		StringBuffer sb=new StringBuffer();
		sb.append("<mobileno>");
		sb.append(tel);
		sb.append("</mobileno>");
		return sb.toString();
	}
	
	/**
	 * 生成短信文件短信内容
	 * @return
	 */
	public String getContent(String content){
		StringBuffer sb=new StringBuffer();
		sb.append("<content>");
		sb.append(content);
		sb.append("</content>");
		return sb.toString();
	}
	
	/**
	 * 生成短信文件短信类型
	 * @return
	 */
	public String getMsgType(){
		StringBuffer sb=new StringBuffer();
		sb.append("<mesgtype>1</mesgtype>");
		return sb.toString();
	}
	
	/**
	 * 生成短信文件srr
	 * @return
	 */
	public String getSrr(){
		StringBuffer sb=new StringBuffer();
		sb.append("<srr>1</srr>");
		return sb.toString();
	}
	
	/**
	 * 保存短信文件
	 * @param recList
	 * @param fdList
	 * @param content
	 * @return
	 */
	public  boolean sendNote(List<String>recList , List<String>fdList,String content) {
		try {
//			log.debug("发送短信内容" + content);
			long t1=System.currentTimeMillis();
			// 先根据类型获取接收此预警的用户群的手机号列表
			File smsFile = createSMSFile();
			StringBuffer fileContent =new StringBuffer();
			for(int i=0;i<recList.size();i++){
				 fileContent.append(createSMSContent(recList.get(i), fdList.get(i),content));
			}
			fileContent.append("end");
//			log.info("短信文件内容====="+fileContent.toString());
			writeFile(smsFile, fileContent+"\n");
//			Process process = Runtime.getRuntime().exec("mv /home/beaimon/user/pzc/sms/"+fileName+" /home/beaimon/gts02/sms/");
			long t2 = System.currentTimeMillis();
			log.info("消耗时间为：" + (t2 - t1));
		} catch (Exception e) {
			log.error("发送短信异常 type:", e);
			return false;
		}
		return true;
	}
	
	/**
	 * 生成超长短信序列号
	 * @return
	 */
	 public String getLongSerialno (String fd_id,int i){
		 StringBuffer sb = new StringBuffer();
		 sb.append("<serialno>");
			sb.append("S004500XX");
			sb.append("_");
			sb.append(getTimeStr());
			sb.append("_");
			sb.append(fd_id);
			sb.append("_"+i);
			sb.append("</serialno>");
		 
		 return sb.toString();
		 
	 }

	 /**
	  * 生成超长短信客户端
	  * @return
	  */
	 public String getLongClientname(){
		 StringBuffer sb = new StringBuffer();
		 sb.append("<clientname>");
		 sb.append("S0045");
		 sb.append("</clientname>");
		 return sb.toString();
	 }
	 
	 /**
	  * 生成超长短信发送端口
	  * @return
	  */
	 public String getLongSendPort(){
		 StringBuffer sb = new StringBuffer();
		 sb.append("<sendport>955980045</sendport>");
		 return sb.toString();
	 }
	 
	 /**
	  * 生成超长短信接收号码
	  */
	 public String getLongMobile(String tel){
		 StringBuffer sb = new StringBuffer();
		 sb.append("<mobileno>");
		 sb.append(tel);
		 sb.append("</mobileno>");
		 return sb.toString();
	 }
	 
	 /**
	  * 生成超长短信内容
	  * @return
	  */
	 public String getLongContent(String content,int i,int a,int number){
		 StringBuffer sb = new StringBuffer();
		 sb.append("<content>");
		 sb.append("<1>"+i+"</1>");
		 sb.append("<2>"+a+"</2>");
		 sb.append("<3>"+content+"</3>");
		 sb.append("<4>"+number+"</4>");
		 sb.append("</content>");
		 return sb.toString();
	 }
	 
	  
	 
	 
	 /**
	  * 获取超长短信类型
	  * @return
	  */
	 public String getLongMsgType(){
		 StringBuffer sb = new StringBuffer();
		 sb.append("<mesgtype>1</mesgtype>");
		 return sb.toString();
	 }
	 
	 /**
	  * 获取超长短信的srr
	  */
	 	
	 public String getLongSrr(){
		 StringBuffer sb = new StringBuffer();
		 sb.append("<srr>1</srr>");
		 return sb.toString();
	 }
	 /**
	  * 组装超长短信内容
	  */
	 public  String getMsgContent(String content,String tel,String fd_id){
		 
		 StringBuffer sb = new StringBuffer();
		 
		 int length = content.length()/66+1;
		 
		 List<String> list = new ArrayList<String>();
		 
		 for(int i=0;i<length;i++){
			 if(i==length-1){
					list.add(content.substring(i*66));
				}else{
				list.add(content.substring(i*66,i*66+66));
				}
		 }
		 int number=(int)(Math.random()*255);
		 for(int j=0;j<list.size();j++){
//			 	log.info(number+"==========number");
			 	sb.append(getLongSerialno(fd_id,j));
			 	sb.append(getLongClientname());
			 	sb.append(getLongSendPort());
			 	sb.append(getLongMobile(tel));
			 	sb.append(getLongContent(list.get(j),length,j+1,number));
			 	sb.append(getLongMsgType());
			 	sb.append(getLongSrr());
			 	sb.append("\n");
			 	
		 }
		 	//sb.append("\n");
//		 	sb.append("end");
		 return sb.toString();
	 }
	 
	 /**
	  * 保存短信文件
	  * @param recList
	  * @param fdList
	  * @param content
	  * @return
	  */
	 public boolean saveMsg(List<String>recList , List<String>fdList,String content){
		 try {
//				log.debug("发送短信内容" + content);
				long t1=System.currentTimeMillis();
				// 先根据类型获取接收此预警的用户群的手机号列表
				File smsFile = createSMSFile();
				StringBuffer fileContent =new StringBuffer();
				for(int i=0;i<recList.size();i++){
					 fileContent.append(getMsgContent(content, recList.get(i),fdList.get(i))+"\n");
				}
//				log.info("短信文件内容====="+fileContent.toString());
				writeFile(smsFile, fileContent+"\n");
//				Process process = Runtime.getRuntime().exec("mv /home/beaimon/user/pzc/sms/"+fileName+" /home/beaimon/gts02/sms/");
				long t2 = System.currentTimeMillis();
				log.info("消耗时间为：" + (t2 - t1));
			} catch (Exception e) {
				log.error("发送短信异常 type:", e);
				return false;
			}
			return true;
			
	 }
	 
	 
	 
	/**
	 * 
	 * <DL>
	 * <p>
	 * <DD>创建文件</DD>
	 * </DL>
	 * <p>
	 * 
	 * @return
	 */
	private File createSMSFile() {
		// 组装文件名
		 fileName = createFileName();
		File smsFile = new File(getDirPath(), fileName);
		if (smsFile.exists()) {
			// 如果已经存在 则递归 从新获取名字 建立文件
			return createSMSFile();
		}
		return smsFile;
	}

	/**
	 * 
	 * <DL>
	 * <p>
	 * <DD>获得短信文件目录</DD>
	 * </DL>
	 * <p>
	 * 
	 * @return
	 */
	private String getDirPath() {
//		File configDir = new File(Platform.getConfigurationLocation().getURL()
//				.getFile());
		//File dir = new File("D://test//t1");
///		File dir=new File("/smsclient/data/QFCS/sendfiledir");
//		File dir = new File("/home/beaimon/user/pzc", "sms");
		/*if (!dir.exists()) {
			dir.mkdirs();
		}*/
		return "";
	}

	/**
	 * 
	 * <DL>
	 * <p>
	 * <DD>组装文件名</DD>
	 * </DL>
	 * <p>
	 * 
	 * @return
	 */
	private String createFileName() {
		StringBuffer sb = new StringBuffer();
		//发送标识
		sb.append("send");
		// 时间
		sb.append(getTimeStr());
		// 流水号
		sb.append(getRandom());
		// 机构码
		sb.append(".txt");
		// 时间
//		sb.append(getDateTime());
//		// 添加序列号
//		sb.append(getRandom());
		return sb.toString();
	}
	
	
/**
 * 获取发送时间
 * @return
 */
	public String getTimeStr(){
		Date d = new Date();
		SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss");
		String timeStr=sdf.format(d);
		return timeStr;
	}
	

	/**
	 * 
	 * <DL>
	 * <p>
	 * <DD>获取10000-19999的随机数</DD>
	 * </DL>
	 * <p>
	 * 
	 * @return
	 */
	private static String getRandom() {
		Random random = new Random();
		int i = 0;
		while (i < 10000||i>19999) {
			i = random.nextInt(1000)+10000;
		}
		return "_" + i;
	}

//	public void run() {
//		if (warnMap != null && warnMap.size() > 0) {
//			for (String type : warnMap.keySet()) {
//				sendNote(type, warnMap.get(type));
//			}
//		}
//		if (smsList != null && smsList.size() > 0) {
//			sendNote(smsType, smsList);
//			
//		}
//
//	}

	/**
	 * 拷贝文件到gtp发送目录
	 * @param s源文件
	 * @param t目标文件
	 */
	public static void fileChannelCopy(File s, File t) {
		FileInputStream fi = null;
		FileOutputStream fo = null;
		FileChannel in = null;
		FileChannel out = null;
		try {
			fi = new FileInputStream(s);
			fo = new FileOutputStream(t);
			in = fi.getChannel();
			out = fo.getChannel();
			in.transferTo(0, in.size(), out);
		} catch (Exception e) {
			
			e.printStackTrace();
		} finally {
			try {
				fi.close();
				in.close();
				fo.close();
				out.close();
			} catch (Exception e) {
				
				e.printStackTrace();
			}
		}
	}
	
	public String getSysUserInfo(List<String>uidList){
		StringBuffer condition=new StringBuffer();
		for(int i=0;i<uidList.size();i++){
			condition.append("'"+uidList.get(i)+"',");
		}
		if (condition.lastIndexOf(",") > -1) {
			condition.deleteCharAt(condition.lastIndexOf(","));
		}
		String sql="SELECT * FROM SYS_USER_INFO WHERE USER_ID NOT IN("+condition+")";
		List list = null;
		try {
			list = PersistenceFactory.getInstance( ModelFactory.getSysmodel()).getSearchResultToMap(99, sql);
		} catch (PersistenceException e) {
			log.error(e.getMessage());
		}
		log.info("获取范本条数===="+list.size());
		String result = "{\"rows\":" + JSONArray.fromObject(list).toString() + "}";
		return result;
	}
	/**
	 * 删除范本和短信
	 * @param fd_id
	 * @param stat
	 * @return
	 * @throws Exception
	 */
	public Msg delSms(String fd_id,String stat) throws Exception{
		String sql="";
//		log.info("stat==="+stat);
//		log.info("fd_id==="+fd_id);
		Msg m=new Msg();
		String []arr=fd_id.split(";");
		if("".equals(stat)){
			m.setSucsess(new Boolean(false).toString());
			m.setInfo(Parameter.DELETE_FAILURE);
			return m;
		}else if("SMS".equals(stat)){
			sql="UPDATE SYS_MSG_MANAGE SET IS_DEL='1' WHERE FD_OBJECTID=?";
		}else if("MODEL".equals(stat)){
			sql="UPDATE SYS_MSG_MODEL SET IS_DEL='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);
		}
	}
	
//	public Msg editSms(String fd_id,String title,List<String> recList,String time,String content,String sendId){
//		Msg m=new Msg();
//		String sql1="INSERT INTO SYS_MSG_MANAGE(REC_USER_TEL,MSG_TYPE,MSG_CONTENT,SEND_USER_TEL,MSG_STAT,MSG_TIME,FD_OBJECTID,IS_DEL,MSG_TITLE)VALUES(?,?,?,?,?,?,?,?,?)";
//		String sql="UPDATE SYS_MSG_MANAGE SET ";
//		return m;
//	}
	
	/**
	 * 修改范本
	 * @param fd_id
	 * @param user_id
	 * @param model_title
	 * @param model_type
	 * @param model_content
	 * @param model_range
	 * @param model_time
	 * @return
	 * @throws Exception
	 */
	public Msg editModel(String fd_id,String model_title,String model_type,String model_content,String model_range,String model_time) throws Exception{
		
		Msg m=new Msg();
//		String sql1="INSERT INTO SYS_MSG_MODEL(FD_OBJECTID,MSG_MODEL_TYPE,MSG_MODEL_TITLE,MSG_MODEL_CONTENT,MSG_MODEL_RANGE,MSG_MODEL_USER,MSG_MODEL_TIME,IS_DEL)VALUES(?,?,?,?,?,?,?,?)";
		String sql="UPDATE SYS_MSG_MODEL SET MSG_MODEL_TYPE=?,MSG_MODEL_TITLE=?,MSG_MODEL_CONTENT=?,MSG_MODEL_RANGE=?,MSG_MODEL_TIME=? WHERE FD_OBJECTID=?";
		log.info(sql+"==========editModel.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);
			st.setString(1, model_type);
			st.setString(2, model_title);
			st.setString(3, model_content);
			st.setString(4, model_range);
			/*st.setString(5, user_id);*/
			st.setString(5, model_time);
			st.setString(6, fd_id);
			num=st.executeUpdate();
			if(num==1){
//				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:更新MODEL"
					+ sql, e);
		} finally {
			db.close(st);
			db.close(conn);
		}
	}
	//获取登录人员
	public String getPeople(String real_name,String dept ,String comp ,String mobile,int page,int pageSize,String comp_id){
		String sql="select top "+(page-1)*pageSize+","+pageSize+" a.real_name , a.fd_objectid , a.user_id, a.mobile , c.dept_name dept ,c.corp_name ,c.dept_id  from "
				+ " sys_user_info a, sys_dept_user b ,sys_department c where (( a.user_id = b.user_id"
				+ " and b.dept_id = c.dept_id and c.corp_id='"+comp_id+"') or ( a.user_id = b.user_id"
				+ " and b.dept_id = c.dept_id and c.parent_id='"+comp_id+"'))";
		log.info(sql+"=======getPeoplesql");
		String sql1 = "select  a.real_name , a.fd_objectid , a.user_id, a.mobile , c.dept_name dept, c.corp_name ,c.dept_id  from "
				+ " sys_user_info a, sys_dept_user b ,sys_department c where (( a.user_id = b.user_id"
				+ " and b.dept_id = c.dept_id and c.corp_id='"+comp_id+"') or ( a.user_id = b.user_id"
				+ " and b.dept_id = c.dept_id and c.parent_id='"+comp_id+"'))";
		Connection conn = null;
		Statement stat = null;
		Statement stat1 = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		DbConnection db = new DbConnection();
		int num = 0;
		if(!"".equals(real_name)){
			sql+=" AND A.REAL_NAME LIKE '%"+real_name+"%'";
			sql1+=" AND A.REAL_NAME LIKE '%"+real_name+"%'";
		}
		if(!"".equals(dept)){
			sql+=" AND C.DEPT_NAME LIKE '%"+dept+"%'";
			sql1+=" AND C.DEPT_NAME LIKE '%"+dept+"%'";
		}
		if(!"".equals(comp)){
			sql+=" AND  C.CORP_NAME  LIKE '%"+comp+"%'";
			sql1+=" AND C.CORP_NAME LIKE '%"+comp+"%'";
		}
		if(!"".equals(mobile)){
			sql+=" AND A.MOBILE LIKE '%"+mobile+"%'";
			sql1+=" AND A.MOBILE LIKE '%"+mobile+"%'";
		}
		
//		log.info("getpeope===="+sql);
//		log.info("getpeope1===="+sql1);
		List list = new ArrayList<String>();
		StringBuffer sb = new StringBuffer();
		try {
			//list = PersistenceFactory.getInstance( ModelFactory.getSysmodel()).getSearchResultToMap(99, sql);
			conn = db.getConnection();
			stat = conn.createStatement();
			rs = stat.executeQuery(sql);
			while(rs.next()){
				sb.append("{");
				sb.append("\"FD_OBJECTID\""+":"+"\""+rs.getString("FD_OBJECTID")+"\""+",");
				sb.append("\"REAL_NAME\""+":"+"\""+rs.getString("REAL_NAME")+"\""+",");
				sb.append("\"USER_ID\""+":"+"\""+rs.getString("USER_ID")+"\""+",");
				sb.append("\"MOBILE\""+":"+"\""+rs.getString("MOBILE")+"\""+",");
				sb.append("\"DEPT\""+":"+"\""+rs.getString("DEPT")+"\""+",");
				sb.append("\"DEPT_NAME\""+":"+"\""+rs.getString("CORP_NAME")+"\"");
				sb.append("},");
			}
			if(sb.lastIndexOf(",")>-1){
				sb.deleteCharAt(sb.lastIndexOf(","));
			}
			
			list.add(sb);
			
			stat1 = conn.createStatement();
			rs1 = stat1.executeQuery(sql1);
			while(rs1.next()){
				num+=1;
			}
		} catch (Exception e) {
			log.error(e.getMessage());
			e.printStackTrace();
		}finally{
			db.close(rs);
			db.close(stat);
			db.close(rs1);
			db.close(stat1);
			db.close(conn);
		}
//		log.info("获取人员条数===="+num);
		String result = "{\"pager.totalRows\":"+num+",\"pager.pageNo\":"+page+","+"\"rows\":" + list.toString() + "}";
//		log.info(result+"==========result");
		return result;
	}
	public String getDeptName(String id,String name){
		String result="";
		
		String sql="select b.dept_name from sys_department a,sys_department b where a.parent_id=b.dept_id and a.dept_id='"+id+"'";
//		log.info("getDeptName======"+sql);
		try {
			SysModel		sysmodel	= ModelFactory.getSysmodel();
			SysPersistence	persistence	= PersistenceFactory.getInstance(sysmodel);
			List<String[]>tempList= persistence.getSearchResult(99, sql.toString());
			if(tempList==null||tempList.size()==0){
				result=name;
			}else{
				result=tempList.get(0)[0];
			}
		} catch (PersistenceException e) {
			log.error(e.getMessage());
		}
//		log.info(result+"========getDeptnameResult");
		return result;
	}
	public  boolean updateStat(String statKey, String statVal,String fd_objectid){
		boolean flag = false;
		Connection conn = null;
		PreparedStatement pstm = null;
		DbConnection db = new DbConnection();
//		DaMengBasicApp app = new DaMengBasicApp();
		
		try {
			String sql = "update SYS_MSG_MANAGE set "+statKey+" = ? where fd_objectid = ? ";
			//conn = db.getConnection();
			conn = db.getConnection();
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, statVal);
			pstm.setString(2,fd_objectid);
			pstm.executeUpdate();
			conn.commit();
			flag = true;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			db.close(pstm);
			db.close(conn);
		}
		return flag;
	}
	
	public void saveRevSms(String serialno,String content){
//		log.info("serialno:"+serialno+",clientname:"+clientname+",sendport:"+sendport+",mobileno:"+mobileno+",content:"+content+",crttime:"+crttime);

		Msg m=new Msg();
		Connection conn = null;
		PreparedStatement st = null;
	//	DbConnection db = new DbConnection();
		DbConnection db = new DbConnection();
		try {
//			String sql = "INSERT INTO SYS_MSG_MANAGE(REC_USER_TEL,MSG_TYPE,MSG_CONTENT,SEND_USER_TEL,MSG_STAT,MSG_TIME,FD_OBJECTID,IS_DEL,MSG_TITLE,SUB_STAT)VALUES(?,?,?,?,?,?,?,?,?,?)";
			String sql="update sys_msg_manage set reply_content=reply_content||?||'\n' where fd_objectid=?";
			//conn = db.getConnection();	
			conn = db.getConnection();
			conn.setAutoCommit(false);
			st = conn.prepareStatement(sql);
				st.setString(1, content+"\n");
				st.setString(2, serialno);
				st.executeUpdate();
				conn.commit();
				conn.setAutoCommit(true);
		} catch (SQLException e) {
			this.log.error(e.getMessage(), e);
		} /*catch (ClassNotFoundException e) {
			e.printStackTrace();*/
		//} 
	catch (Exception e) {
			
			e.printStackTrace();
		} finally {
			db.close(st);
			db.close(conn);
		}
	}
	
	/**
	 * 增加常用联系人
	 * @return
	 */
	public Msg addContact(String user_id,String contact_user_id,String contact_user_name,String contact_dept,String contact_comp,String contact_tel){
		Msg m=new Msg();
		String sql="insert into SMS_COMMON_USER_CONTACT(FD_OBJECTID,MSG_USER_ID,CONTACT_USER_ID,CONTACT_USER_NAME,CONTACT_DEPT,CONTACT_COMP,CONTACT_TEL,UPDATEDATE,IS_DEL) values(?,?,?,?,?,?,?,?,?)";
		String[]contact_id=contact_user_id.split(",");
		String[]contact_name=contact_user_name.split(",");
		String[]dept=contact_dept.split(",");
		String[]comp=contact_comp.split(",");
		String[]tel=contact_tel.split(",");
		Connection conn = null;
		PreparedStatement st = null;
		DbConnection db = new DbConnection();
		try {
			conn = db.getConnection();
			int num=0;
			conn.setAutoCommit(false);
			st = conn.prepareStatement(sql);
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			for(int i=0;i<contact_id.length;i++){
				st.setString(1, IdentityGenerator.getIdentityGenerator().gerenalIdentity(337));
				st.setString(2, user_id);
				st.setString(3, contact_id[i]);
				st.setString(4, contact_name[i]);
				st.setString(5, dept[i]);
				st.setString(6, comp[i]);
				st.setString(7, tel[i]);
				st.setString(8, sdf.format(new Date()));
				st.setString(9, "0");
				st.executeUpdate();
				num++;
			}
			if(num==contact_id.length){
				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 (ClassNotFoundException e) {
			
			e.printStackTrace();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			db.close(st);
			db.close(conn);
		}
		return m;
	}
	
	public String getContactList(String user_id){
		String result="";
		String sql="select CONTACT_USER_ID from SMS_COMMON_USER_CONTACT where MSG_USER_ID='"+user_id+"' and is_del='0'";
		try {
			List list = PersistenceFactory.getInstance(ModelFactory.getSysmodel()).getSearchResultToMap(99, sql);
			if(list!=null&&list.size()>0){
				result="{\"list\":"+JSONArray.fromObject(list).toString()+"}";
			}
		} catch (PersistenceException e) {
			
			e.printStackTrace();
		}
		return result;
	}
	
	public String getContactTel(String user_id){
		String result = "";
		String sql = "select group_people_tel from sms_contact_group where user_id ='"+user_id+"' and is_del='0'";
		try {
			List list = PersistenceFactory.getInstance(ModelFactory.getSysmodel()).getSearchResultToMap(99, sql);
			if(list!=null&&list.size()>0){
				result="{\"list\":"+JSONArray.fromObject(list).toString()+"}";
			}
		} catch (PersistenceException e) {
			
			e.printStackTrace();
		}
		return result;
	}
	
	public String getMsgContents(String fd_id){
		String result = "";
		String sql = "select msg_content from sys_msg_manage where fd_objectid = ? and is_del=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<String> list = new ArrayList<String>();
		DbConnection db = new DbConnection();
		StringBuffer sb = new StringBuffer();
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, fd_id);
			ps.setString(2, "0");
			rs = ps.executeQuery();
			while(rs.next()){
				sb.append("{");
				sb.append("\"MSG_CONTENT\""+":"+"\""+rs.getString("MSG_CONTENT")+"\"");
				sb.append("},");
			}
			if(sb.lastIndexOf(",")>-1){
				sb.deleteCharAt(sb.lastIndexOf(","));
				list.add(sb.toString());
			}
		} catch (ClassNotFoundException e) {
			log.error(e.getMessage(),e);
		} catch (SQLException e) {
			log.error(e.getMessage(),e);
		}finally{
			db.close(rs);
			db.close(ps);
			db.close(conn);
		}
		result = "{\"rows\":"+list+"}";
//		log.info(result+"=====getMsgs");
		return result;
	}
	
	
	public String getMsgContentInfo(String fd_id){
		String result = "";
		String sql = "select msg_content,rec_name,rec_user_tel,msg_stat,real_name,msg_time from sys_msg_manage where fd_objectid = ? and is_del=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<String> list = new ArrayList<String>();
		DbConnection db = new DbConnection();
		StringBuffer sb = new StringBuffer();
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, fd_id);
			ps.setString(2, "0");
			rs = ps.executeQuery();
			while(rs.next()){
				sb.append("{");
				sb.append("\"MSG_CONTENT\""+":"+"\""+rs.getString("MSG_CONTENT")+"\",");
				sb.append("\"REC_NAME\""+":"+"\""+rs.getString("REC_NAME")+"\",");
				sb.append("\"REC_USER_TEL\""+":"+"\""+rs.getString("REC_USER_TEL")+"\",");
				sb.append("\"MSG_STAT\""+":"+"\""+rs.getString("MSG_STAT")+"\",");
				sb.append("\"REAL_NAME\""+":"+"\""+rs.getString("REAL_NAME")+"\",");
				sb.append("\"MSG_TIME\""+":"+"\""+rs.getString("MSG_TIME")+"\"");
				sb.append("},");
			}
			if(sb.lastIndexOf(",")>-1){
				sb.deleteCharAt(sb.lastIndexOf(","));
				list.add(sb.toString());
			}
		} catch (ClassNotFoundException e) {
			log.error(e.getMessage(),e);
		} catch (SQLException e) {
			log.error(e.getMessage(),e);
		}finally{
			db.close(rs);
			db.close(ps);
			db.close(conn);
		}
		result = "{\"rows\":"+list+"}";
//		log.info(result+"=====getMsgs");
		return result;
	}
	
	//生成短信的fd_id
	public String getFdId(int classid,int i){
		
		/*Random r = new Random();*/
		/*int i = r.nextInt(1001);*/
		long time = System.currentTimeMillis()+(classid*i);
		String result = String.valueOf(classid)+time;
		return result;
		
	}
	
	public String getPepTel(String comp_id){
		String sql = " select a.real_name,a.MOBILE from sys_user_info a,SYS_USER_ROLE_REL b,sys_department c"
				   + " where a.user_id=b.USER_ID and b.ROLE_ID  ='R002105430205' and a.dept_id = c.dept_id and c.corp_id = ? and a.MOBILE is not null";
		List<Map<String,String>> list = null;
		String peoples = "";
		String tels = "";
		try {
			list = new ORGTemplate().query(sql, comp_id);
			if(list!=null && list.size()>0){
				for(int i=0;i<list.size();i++){
					Map<String,String> map = new HashMap<String,String>();
					map = list.get(i);
					peoples += map.get("REAL_NAME")+";";
					tels += map.get("MOBILE")+";";
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return peoples+":"+tels;
	}
	
}