package com.sinosoft.em.alert.notice.manage.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.Map;

import com.persistence.DbConnection;
import com.persistence.service.JDBCHelper;
import com.persistence.service.exception.PersistenceException;
import com.sinosoft.em.alert.notice.manage.vo.TreatedHuman;
import com.sinosoft.em.alert.report.manage.vo.ReportProgress;

public class TreatedHumanDao {

	public ArrayList<TreatedHuman> queryData(String businessId) throws Exception {
		String sql = "select FD_OBJECTID,PERSON_ID,PERSON_NAME,UPDATEDATE,STATUES,DISPOSE,EXPLAIN,BUSINESS_ID from ECM_EM_TREATED_HUMAN where BUSINESS_ID=? and is_del=0  order by STATUES";
		DbConnection db = new DbConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, businessId);
			rs = ps.executeQuery();
			ArrayList<TreatedHuman> datalist = new ArrayList<TreatedHuman>();
			while (rs.next()) {
				TreatedHuman person = new TreatedHuman();
				person.setFD_OBJECTID(rs.getString("FD_OBJECTID"));
				person.setPERSON_ID(rs.getString("PERSON_ID"));
				person.setPERSON_NAME(rs.getString("PERSON_NAME"));
				person.setSTATUES(rs.getString("STATUES"));
				String d = rs.getString("UPDATEDATE");
				if(d!=null&&d.length()>16){
					d = d.substring(0,19);
				}
				person.setUPDATEDATE(d);
				person.setDISPOSE(rs.getString("DISPOSE"));
				person.setEXPLAIN(rs.getString("EXPLAIN"));
				person.setBUSINESS_ID(rs.getString("BUSINESS_ID"));
				datalist.add(person);
			}
			return datalist;
		} catch (Exception e) {
			throw e;
		}finally{
			rs.close();
			ps.close();
			conn.close();
		}
	}
	/**
	 * 根据公司Id，查询应急成员及手机号
	 * @param companyIds
	 * @return
	 * @throws Exception
	 */
	public Map<String,String> queryEmergencyPersonPhone(String companyIds) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT o.MOBILE,o.REAL_NAME FROM SYS_USER_INFO o LEFT JOIN SYS_USER_ROLE_REL l ON o.USER_ID = l.USER_ID LEFT JOIN SYS_DEPT_USER r ON l.USER_ID = r.USER_ID LEFT JOIN SYS_DEPARTMENT t ON r.DEPT_ID = t.DEPT_ID ");
		sql.append(" WHERE l.ROLE_ID IN ('R002105430201', 'R002105430202', 'R002105430203', 'R002105430204', 'R002105430205')");
		sql.append(" AND t.CORP_ID IN ("+companyIds+");");
		DbConnection db = new DbConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			Map<String,String> mapList = new HashMap<String,String>();
			while (rs.next()) {
				mapList.put(rs.getString("MOBILE"),rs.getString("REAL_NAME"));
				
			}
			return mapList;
		} catch (Exception e) {
			throw e;
		}finally{
			rs.close();
			ps.close();
			conn.close();
		}
	}
	/**
	 * 根据公司Id，查询应急成员及手机号
	 * @param companyIds
	 * @return
	 * @throws Exception
	 */
	public Map<String, Map<String, String>> queryEmergencyPersonName(String companyIds) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT o.USER_ID,o.REAL_NAME,o.MOBILE,t.corp_name FROM SYS_USER_INFO o LEFT JOIN SYS_USER_ROLE_REL l ON o.USER_ID = l.USER_ID LEFT JOIN SYS_DEPT_USER r ON l.USER_ID = r.USER_ID LEFT JOIN SYS_DEPARTMENT t ON r.DEPT_ID = t.DEPT_ID ");
		sql.append(" WHERE l.ROLE_ID IN ('R002105430201', 'R002105430202', 'R002105430203', 'R002105430204', 'R002105430205')");
		sql.append(" AND t.CORP_ID IN ("+companyIds+");");
		DbConnection db = new DbConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			Map<String,String> mapList = new HashMap<String,String>();
			Map<String, Map<String, String>>tempMap=new HashMap<String, Map<String,String>>();//<公司名<人名,电话>>
			while (rs.next()) {
				if(tempMap.containsKey(rs.getString("CORP_NAME"))){
					Map<String, String>map=tempMap.get(rs.getString("CORP_NAME"));
//					map.put(rs.getString("USER_ID"),rs.getString("REAL_NAME"));
					map.put(rs.getString("MOBILE"),rs.getString("REAL_NAME"));
					tempMap.put(rs.getString("CORP_NAME"), map);
				}else{
					Map<String, String>map= new HashMap<String,String>();
					tempMap.put(rs.getString("CORP_NAME"), map);
				}
//				mapList.put(rs.getString("USER_ID"),rs.getString("REAL_NAME"));
				
			}
			return tempMap;
		} catch (Exception e) {
			throw e;
		}finally{
			rs.close();
			ps.close();
			conn.close();
		}
	}
	
	public Map<String, Map<String, String>> queryEmergencyPersonName1(String companyIds) throws Exception {
//		StringBuffer sql = new StringBuffer("SELECT o.USER_ID,o.REAL_NAME,o.MOBILE,t.corp_name FROM SYS_USER_INFO o LEFT JOIN SYS_USER_ROLE_REL l ON o.USER_ID = l.USER_ID LEFT JOIN SYS_DEPT_USER r ON l.USER_ID = r.USER_ID LEFT JOIN SYS_DEPARTMENT t ON r.DEPT_ID = t.DEPT_ID ");
//		sql.append(" WHERE l.ROLE_ID IN ('R002105430201', 'R002105430202', 'R002105430203', 'R002105430204', 'R002105430205')");
//		sql.append(" AND t.CORP_ID IN ("+companyIds+");");
		
		String sql = "select COMPID,NAME,TELPHONE,ISLEADER from SYS_MSG_USER where (COMPETENCY like '%R002105430201%' or COMPETENCY like '%R002105430201%' or COMPETENCY like '%R002105430202%' or COMPETENCY like '%R002105430203%' or COMPETENCY like '%R002105430204%' or COMPETENCY like '%R002105430205%') and COMPID in("+companyIds+") and is_del = '0'";
		
		StringBuffer sb = new StringBuffer();
		StringBuffer sbTel = new StringBuffer();
		DbConnection db = new DbConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			Map<String,String> mapList = new HashMap<String,String>();
			Map<String, Map<String, String>>tempMap=new HashMap<String, Map<String,String>>();//<公司名<人名,电话>>
			while (rs.next()) {
				if(tempMap.containsKey(getUniteName(rs.getString("COMPID")))){
					Map<String, String>map=tempMap.get(getUniteName(rs.getString("COMPID")));
					if(rs.getString("ISLEADER").equals("0")){//是领导
						map.put(rs.getString("TELPHONE")+"?",rs.getString("NAME"));
					}else{
						map.put(rs.getString("TELPHONE"),rs.getString("NAME"));
					}
					
					tempMap.put(getUniteName(rs.getString("COMPID")), map);
				}else{
					Map<String, String>map= new HashMap<String,String>();
					if(rs.getString("ISLEADER").equals("0")){//是领导
						map.put(rs.getString("TELPHONE")+"?",rs.getString("NAME"));
					}else{
						map.put(rs.getString("TELPHONE"),rs.getString("NAME"));
					}
					tempMap.put(getUniteName(rs.getString("COMPID")), map);
				}
			}
			return tempMap;
		} catch (Exception e) {
			throw e;
		}finally{
			rs.close();
			ps.close();
			conn.close();
		}
	}
	
	public String getUniteName(String unitID){//查询单位名称（通过单位ID）
		String sql = "select distinct CORP_NAME from SYS_DEPARTMENT where CORP_ID=?";
		
		StringBuffer sb = new StringBuffer();
		
		DbConnection db = new DbConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, unitID);
			rs = ps.executeQuery();
			while(rs.next()){
				sb.append(rs.getString("CORP_NAME") == null ? "" : rs.getString("CORP_NAME"));
			}
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)
					rs.close();
				if(ps!=null)
					ps.close();
				if(conn!=null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return sb.toString();
	}
	
}