GZ/nwyj/.svn/pristine/a6/a6755ba2b66bfab663ceceb90ae8f73f9956f284.svn-base

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

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

import net.sf.json.JSONObject;

import org.apache.log4j.Logger;

import com.persistence.DbConnection;
import com.persistence.service.PersistenceFactory;
import com.persistence.service.SysPersistence;
import com.persistence.service.exception.PersistenceException;
import com.sinosoft.em.event.dao.EventDaoImpl;
import com.sinosoft.lz.system.sms.vo.SearchPeople;
import com.sinosoft.lz.system.sms.vo.Tree;
import com.sysmodel.datamodel.xmlmodel.ModelFactory;
import com.sysmodel.datamodel.xmlmodel.able.SysModel;

public class ContactTreeDao {
	private static final Logger log = Logger.getLogger(EventDaoImpl.class);
	private static SysModel sysmodel = ModelFactory.getSysmodel();
	private static SysPersistence persistence = PersistenceFactory.getInstance(sysmodel);
	
	public List<String[]> getContactPeople(String userId) throws PersistenceException{
		String sql = "select contact_tel , contact_user_name  from sms_common_user_contact where msg_user_id ='"+userId+"' and is_del='0'";
		List<String[]> list=persistence.getSearchResult(779, sql);
		if(list.size()>0&&list!=null){
			return list;
		}else{
			return null;
		}
	}
	
	public String getGroupPeole(String params,String userId) throws  ClassNotFoundException{
		String rex = "^\\d+$";
		String sql2 = "select distinct group_people,group_people_tel from sms_contact_group where user_id=? and is_del=? ";
		Tree t = new Tree();
		SearchPeople sp = null;
		List<Object> fromList  = new ArrayList<Object>();
		
		if(params.matches(rex)){
			sql2 +=" and group_people_tel like ?";
		}else{
			if(params.contains("'")){
				params = params.replaceAll("'", "");
			}
			sql2 +=" and (group_people||group_name like ?)";
		}
		
		Connection conn = null;
		PreparedStatement ps = null;
		DbConnection db = new DbConnection();
		ResultSet rs = null;
		
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql2);
			ps.setString(1, userId);
			ps.setString(2, "0");
			ps.setString(3, "%"+params+"%");
			rs = ps.executeQuery();
			while(rs.next()){
				sp = new SearchPeople();
				sp.setValue(rs.getString("group_people_tel"));
				sp.setKey(rs.getString("group_people"));
				fromList.add(sp);
			}
		} catch (SQLException e) {
			log.info(e.getMessage(),e);
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
				if(ps!=null){
					ps.close();
				}
				if(conn!=null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		log.info(sql2+"============getContactPeole.sql2");
		/*List<String[]> list2 = persistence.getSearchResult(781, sql2);
		if(list2!=null&&list2.size()>0){
			for(String[] s:list2){
				
			}
		}*/
		
		t.setTreeNodes(fromList);
		log.info(JSONObject.fromObject(t).toString()+"===================JSONObject.fromObject(tree).toString()");
		return JSONObject.fromObject(t).toString();
		
	} 
	
	
	public String ContactPeoples (String params,String userId) throws  ClassNotFoundException{
		String rex = "^\\d+$";
		String sql = "select contact_user_name ,contact_tel from sms_common_user_contact where msg_user_id =? and is_del=?";
		Tree t = new Tree();
		SearchPeople sp = null;
		List<Object> fromList  = new ArrayList<Object>();
		if(params.matches(rex)){
			sql += " and contact_tel like ?";
		}else {
			if(params.contains("'")){
				params = params.replaceAll("'", "");
			}
			sql += " and contact_user_name like ?";
		}
		
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		DbConnection db = new DbConnection();
		
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, userId);
			ps.setString(2, "0");
			ps.setString(3, "%"+params+"%");
			rs = ps.executeQuery();
			while(rs.next()){
				sp = new SearchPeople();
				sp.setKey(rs.getString("contact_user_name"));
				sp.setValue(rs.getString("contact_tel"));
				fromList.add(sp);
			}
		} catch (SQLException e) {
			log.error(e.getMessage(),e);
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
				if(ps!=null){
					ps.close();
				}
				if(conn!=null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		log.info("sql====================================peoples"+sql);
		//List<String[]> list = persistence.getSearchResult(779, sql);
		/*if(list!=null&&list.size()>0){
			for(String[] s:list){
				
			}
		}*/
		t.setTreeNodes(fromList);
		log.info(JSONObject.fromObject(t).toString()+"===================JSONObject.fromObject(tree).toString()");
		return JSONObject.fromObject(t).toString();
	}
	
	
	
	public String getSearchPeople(String params,String userId,String tel,int pageSize,int page) throws PersistenceException{

		String rex = "^\\d+$";
	//	String sql ="select a.user_id,a.dept_id,b.real_name,b.mobile from sys_dept_user a,sys_user_info b where a.dept_id='"+dept_id+"' and a.user_id=b.user_id and b.mobile is not null";
		String sql = "select   a.real_name,a.mobile  from sys_user_info a , sys_department c  where  a.dept_id = c.dept_id and (a.mobile is not null or a.mobile <> '')";
		String sql1 = "select contact_user_name real_name ,contact_tel mobile  from sms_common_user_contact where msg_user_id='"+userId+"'"; 
		String sql3 = "select group_people real_name , group_people_tel mobile  from sms_contact_group where user_id='"+userId+"'";
		String sql6 = "select name real_name,telphone mobile from SYS_MSG_USER where COMPETENCY is not null and is_del='0'";
		List<Object> list = new ArrayList<Object>();
		Tree t = new Tree();
		SearchPeople sp = null;
		if(params.matches(rex)){
			sql += " and a.mobile like '%"+params+"%'";
			sql1 +=" and contact_tel like'%"+params+"%'";
			sql3 +=" and group_people_tel like '%"+params+"%'";
			sql6 +=" and telphone like '%"+params+"%'";
		}else{
			if(params.contains("'")){
				params = params.replaceAll("'", "");
			}
			sql += " and a.real_name like '%"+params+"%'";
			sql1 += " and contact_user_name like '%"+params+"%'";
			sql3 +=" and group_people like '%"+params+"%'";
			sql6 +=" and name like '%"+params+"%'";
		}
		log.info(tel+"===============tel");
		if(!"".equals(tel)){
			tel = tel.substring(0,tel.lastIndexOf(","));
			tel = "("+tel+")";
			sql += " and a.mobile not in"+tel;
			sql1 += " and  contact_tel not in"+tel;
			sql3 += " and group_people_tel not in"+tel;
			sql6 += " and telphone not in"+tel;
		}
		String sql4 = "select distinct top "+(page-1)*pageSize+","+pageSize+" a.* from  ("+sql +" union "+sql1+" union "+sql3+" union "+sql6+") a order by a.mobile";
		String sql5 = "select count(*) from  ("+sql+" union "+sql1+" union "+sql3+")";
		log.info(sql4+"====================sql4");
		List<String[]> list1 = persistence.getSearchResult(99, sql4);
		List<String[]> list2 = persistence.getSearchResult(99, sql5);
		for(String[] s:list1){
			sp = new SearchPeople();
			sp.setKey(s[0]);
			sp.setValue(s[1]);
			list.add(sp);
		}
		String totalRows = list2.get(0)[0];
		t.setTreeNodes(list);
		t.setTotalRows(totalRows);
		return JSONObject.fromObject(t).toString();
	}
	
	
	public String getSearchPeoples(String params,String userId,String tel,int page,int pageSize) throws PersistenceException{
		String rex = "^\\d+$";
		String sql = "select distinct top "+(page-1)*pageSize+","+pageSize+"  a.real_name,a.mobile, c.dept_name , c.corp_name from sys_user_info a , sys_department c  where  a.dept_id = c.dept_id and a.mobile is not null ";
		String sql2 = "select  a.real_name,a.mobile, c.dept_name , c.corp_name from sys_user_info a , sys_department c  where  a.dept_id = c.dept_id and a.mobile is not null ";
		List<Object> list = new ArrayList<Object>();
		Tree t = new Tree();
		SearchPeople sp = null;
		if(params.matches(rex)){
			sql += " and a.mobile like '%"+params+"%'";
			sql2 += " and a.mobile like '%"+params+"%'";
		}else{
			if(params.contains("'")){
				params = params.replaceAll("'", "");
			}
			sql += " and a.real_name like '%"+params+"%'";
			sql2 +="  and a.real_name like '%"+params+"%'";
		}
		log.info(tel+"===============tel");
		if(!"".equals(tel)){
			tel = tel.substring(0,tel.lastIndexOf(","));
			tel = "("+tel+")";
			sql += " and a.mobile not in"+tel;
			sql2 +=" and a.mobile not in"+tel;
		}
		sql +=" order by a.mobile";
		
		log.info(sql+"====================getSearchPeoples");
		String sql3 = "select count(*) from ("+sql2+")";
		List<String[]> list1 = persistence.getSearchResult(99, sql);
		List<String[]> list2 =  persistence.getSearchResult(99, sql3);
		String totalRows = list2.get(0)[0];
		for(String[] s:list1){
			sp = new SearchPeople();
			sp.setKey(s[0]);
			sp.setValue(s[1]);
			sp.setCorpName(s[3]);
			sp.setDeptName(s[2]);
			list.add(sp);
		}
		
		t.setTreeNodes(list);
		t.setTotalRows(totalRows);
		return JSONObject.fromObject(t).toString();
	}
	
	
	
	
	
	
	
	public List<String[]> getContactGroupPeople(String userId) throws PersistenceException{
		String sql = "select group_name ,group_people,group_people_tel,fd_objectid from sms_contact_group where user_id ='"+userId+"' and is_del='0'";
		List<String[]> list=persistence.getSearchResult(781, sql);
		log.info(list+"==============getContactGroupPeople.list");
		if(list.size()>0&&list!=null){
			return list;
		}else{
			return null;
		}
	}
	
	
	/*public List<String[]> getPeoplebyTel(String[] tel,String userId){
		String sql = "select  contact_tel ,contact_user_name from sms_common_user_contact where msg_user_id =_'"+userId+"' and is_del='0'";
		String sql2 = "select group_name ,group_people,group_people_tel,fd_objectid from sms_contact_group where user_id ='"+userId+"' and is_del='0'";
		
		for(int i=0;i<tel.length;i++){
			String arr = tel[i];
			
		}
		
		
		return null;
	}*/
	
	
	public List<String[]> getContactGroupPeoples(String userId) throws PersistenceException{
		String sql = "select group_name ,group_people,group_people_tel,fd_objectid from sms_contact_group where user_id ='"+userId+"' and is_del='0' ";
		
		List<String[]> list=persistence.getSearchResult(779, sql);
		if(list.size()>0&&list!=null){
			return list;
		}else{
			return null;
		}
	}
}