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.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.log4j.Logger;

import com.persistence.DbConnection;
import com.sinosoft.lz.system.sms.vo.Json;
import com.sinosoft.lz.system.sms.vo.JsonVo;

public class JsonDao {
	private Logger	log	= Logger.getLogger(this.getClass());
	
	public String getMsgType(String userId,String deptId) throws ClassNotFoundException{
		String sql = "select distinct msg_type from sys_msg_manage where is_del=? and  ((send_user_tel = ?) or (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=?)))";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		DbConnection db = new DbConnection();
		JsonVo json = null;
		Json js = new Json();
		List<Object> list1 = new ArrayList<Object>();
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, "0");
			ps.setString(2, userId);
			ps.setString(3, deptId);
			rs = ps.executeQuery();
			json = new JsonVo();
			json.setKey("全部");
			json.setValue("0000");
			list1.add(json);
			while (rs.next()){
				json = new JsonVo();
				json.setKey(rs.getString("MSG_TYPE"));
				json.setValue(rs.getString("MSG_TYPE"));
				list1.add(json);
			}
			
			js.setName("短信类型");
			js.setClassName("MSG_TYPE");
			js.setList(list1);
		} 
		catch (SQLException e) {
			log.error(e.getMessage(), e);
		}finally{
			db.close(rs);
			db.close(ps);
			db.close(conn);
		}
		log.info(JSONObject.fromObject(js).toString());
		return JSONObject.fromObject(js).toString();
	}
	
	public String getContactInfo(String userId) throws ClassNotFoundException{
		String sql = "select distinct contact_dept , contact_comp  from sms_common_user_contact where msg_user_id = ? and is_del=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		JsonVo json = null;
		JsonVo json2 = null;
		Json js = new Json();
		Json js2 = new Json();
		DbConnection db = new DbConnection();
		List<Object> list1 = new ArrayList<Object>();
		List<Object> list2 = new ArrayList<Object>();
		Map<String,String> map = new HashMap<String,String>();
		Map<String,String> map1 = new HashMap<String,String>();
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, userId);
			ps.setString(2, "0");
			rs = ps.executeQuery();
			json = new JsonVo();
			json.setKey("全部");
			json.setValue("0000");
			list1.add(json);
			json2 = new JsonVo();
			json2.setKey("全部");
			json2.setValue("0000");
			list2.add(json2);
			while(rs.next()){
				if(!"".equals(rs.getString("contact_dept"))&&rs.getString("contact_dept")!=null&&map.get(rs.getString("contact_dept"))==null){
					json = new JsonVo();
					json.setKey(rs.getString("contact_dept"));
					json.setValue(rs.getString("contact_dept"));
					list1.add(json);
					map.put(rs.getString("contact_dept"), rs.getString("contact_dept"));
				}
				
				if(!"".equals(rs.getString("contact_comp"))&&rs.getString("contact_comp")!=null&&map1.get(rs.getString("contact_comp"))==null){
					json2 = new JsonVo();
					json2.setKey(rs.getString("contact_comp"));
					json2.setValue(rs.getString("contact_comp"));
					list2.add(json2);
					map1.put(rs.getString("contact_comp"), rs.getString("contact_comp"));
				}
			}
			js.setName("联系人部门");
			js.setClassName("CONTACT_DEPT");
			js.setList(list1);
			js2.setName("联系人公司");
			js2.setClassName("CONTACT_COMP");
			js2.setList(list2);
			
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
		}finally{
			db.close(rs);
			db.close(ps);
			db.close(conn);
		}
		return JSONObject.fromObject(js).toString()+"__"+JSONObject.fromObject(js2).toString();
	}
	
	public String getContactGroupComp(String userId) throws ClassNotFoundException{
		String sql = "select distinct group_people_comp from sms_contact_group where user_id = ? and is_del=? " ;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		DbConnection db = new DbConnection();
		JsonVo json = null;
		Json js = new Json();
		List<Object> list = new ArrayList<Object>();
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, userId);
			ps.setString(2, "0");
			rs = ps.executeQuery();
			json = new JsonVo();
			json.setKey("全部");
			json.setValue("0000");
			list.add(json);
			while(rs.next()){
				json = new JsonVo();
				json.setKey(rs.getString("group_people_comp"));
				json.setValue(rs.getString("group_people_comp"));
				list.add(json);
			}
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
		}finally{
			db.close(rs);
			db.close(ps);
			db.close(conn);
		}
		js.setName("联系组人员公司");
		js.setClassName("GROUP_PEOPLE_COMP");
		js.setList(list);
		return JSONObject.fromObject(js).toString();
		
	}
	
}