wp/nwyj/.svn/pristine/53/534533ee9d6cec217360b755d8f50d447ce9fb2e.svn-base

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


import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONArray;

import org.apache.log4j.Logger;

import com.sinosoft.am.org.jdbcUtil.ORGTemplate;
import com.sinosoft.lz.system.menu.vo.NoticeVo;


public class NoticeDao {

	private final Logger log = Logger.getLogger(getClass()); 
	public String getNotice(String params) throws ClassNotFoundException{
//		System.out.println("**********///////////***********"+params);
		/*String sql = "select A.FD_OBJECTID ,A.EARLY_WARNING_NAME,A.WRITE_TIME ,B.DEPT_NAME , A.CURRENT_EW_LEVEL , A.CURRENT_ER_LEVEL ,A.IS_ALERT "+
					 "from ECM_EM_PUBLISH_ALERT_NOTICE A,SYS_DEPARTMENT B "+
					 "where is_alert='0' AND A.RELEASE_UNIT IN "+
					 	"(select corp_id from sys_department where ( parent_id='"+params+"' or corp_id='"+params+"') and dept_type=1 "+
					 		"union select parent_id from sys_department where dept_id='"+params+"' ) AND "+
					 	"write_time IN (SELECT MAX(WRITE_TIME) FROM ECM_EM_PUBLISH_ALERT_NOTICE WHERE  IS_ALERT='0' AND IS_SEND='5') AND IS_SEND='5' "+
					 "UNION "+
					 "select A.FD_OBJECTID ,A.EARLY_WARNING_NAME,A.WRITE_TIME, B.DEPT_NAME , A.CURRENT_EW_LEVEL , A.CURRENT_ER_LEVEL ,A.IS_ALERT "+
					 "from ECM_EM_PUBLISH_ALERT_NOTICE A,SYS_DEPARTMENT B "+
					 "where is_alert='1' AND A.RELEASE_UNIT IN "+
					 	"(select corp_id from sys_department where ( parent_id='"+params+"' or corp_id='"+params+"') and dept_type=1 "+
					 	"union select parent_id from sys_department where dept_id='"+params+"' )AND "+
					 	 " write_time IN (SELECT MAX(WRITE_TIME) FROM ECM_EM_PUBLISH_ALERT_NOTICE WHERE  IS_ALERT='1' AND IS_SEND='5') AND IS_SEND='5' " ;*/
		String sql =  " select A.FD_OBJECTID , A.EARLY_WARNING_NAME, A.RELEASE_RELIEVE_DATE , B.CORP_NAME , A.CURRENT_EW_LEVEL , A.CURRENT_ER_LEVEL , A.IS_ALERT " +
					  " from ECM_EM_PUBLISH_ALERT_NOTICE A, SYS_DEPARTMENT B " +
					  " where A.is_alert ='0' AND A.RELEASE_RELIEVE_DATE IN " +
																	      " (SELECT MAX(RELEASE_RELIEVE_DATE)  FROM ECM_EM_PUBLISH_ALERT_NOTICE C " +
																	      " WHERE C.IS_ALERT='0' AND C.IS_SEND ='5' and   " +
																	      " RELEASE_UNIT IN (select corp_id from sys_department " +
																	       					 " where (parent_id='" +params+ "' or corp_id='" +params+ "' ) and dept_type='1' " +
																					         " union " +
																					         " select parent_id from sys_department where dept_id='" +params+ "') " +
																	      " )AND A.IS_SEND='5' AND A.RELEASE_UNIT=B.dept_id " +
				      "  UNION  " +
				      "  select A.FD_OBJECTID , A.EARLY_WARNING_NAME, A.RELEASE_RELIEVE_DATE , B.CORP_NAME , A.CURRENT_EW_LEVEL , A.CURRENT_ER_LEVEL ,A.IS_ALERT " +
				      "  from ECM_EM_PUBLISH_ALERT_NOTICE A, SYS_DEPARTMENT B " +
				      "  where A.is_alert ='1' AND A.RELEASE_RELIEVE_DATE IN " +
				     													 " (SELECT MAX(RELEASE_RELIEVE_DATE)  FROM ECM_EM_PUBLISH_ALERT_NOTICE C " +
				     													 " WHERE C.IS_ALERT='1' AND C.IS_SEND ='5' and   " +
				     													 " RELEASE_UNIT IN (select corp_id from sys_department " +
				     																	  " where (parent_id='" +params+ "' or corp_id='" +params+ "')and dept_type='1' " +
				     																	  " union " +
				     																	  " select parent_id from sys_department where dept_id='" +params+ "') " +
												     				     " )AND A.IS_SEND='5' AND A.RELEASE_UNIT=B.dept_id " ;
		
		List<NoticeVo> list2 = new ArrayList<NoticeVo>();
		try {
			List<Map<String,String>> list = new ORGTemplate().query(sql);
			for(int i=0;i<list.size();i++){
				Map<String,String> map = list.get(i);
				NoticeVo vo = new NoticeVo();
				vo.setCorpName(map.get("CORP_NAME"));
				vo.setFd_objectid(map.get("FD_OBJECTID"));
				vo.setIsAlert(map.get("IS_ALERT"));
				vo.setWarningName(map.get("EARLY_WARNING_NAME"));
				vo.setWriteTime(map.get("RELEASE_RELIEVE_DATE"));
				vo.setAlertcol(map.get("CURRENT_EW_LEVEL"));
				vo.setResponsecol(map.get("CURRENT_ER_LEVEL"));
				
				list2.add(vo);
			}
		
		} catch (SQLException e) {
			log.error(e.getMessage(),e);
		}
		return JSONArray.fromObject(list2).toString();
	}

	public String getIndexNotice(String corp_id,String parentID, String flag) throws ClassNotFoundException {
		StringBuffer sql = new StringBuffer();
		sql.append(" select distinct A.FD_OBJECTID ,A.PLAN_TYPE_ID,A.UPDATEDATE, A.EARLY_WARNING_NAME, A.RELEASE_RELIEVE_DATE , B.CORP_NAME , A.CURRENT_EW_LEVEL ,A.IS_SEND, A.CURRENT_ER_LEVEL ,A.IS_ALERT ");
		sql.append(" from ECM_EM_PUBLISH_ALERT_NOTICE A left join SYS_DEPARTMENT B  ON B.DEPT_ID = A.RELEASE_UNIT ");
		sql.append(" left join ECM_EM_TREATED_HUMAN H ON A.FD_OBJECTID = H.BUSINESS_ID ");
		sql.append(" where A.is_del='0' and A.IS_SEND =='5' ");
		if("3".equals(flag)){
			sql.append(" and A.RELEASE_UNIT in ('"+corp_id+"') ");
		}else if("2".equals(flag)){
			sql.append(" and A.RELEASE_UNIT in ( '"+parentID+"' ) ");
		}else if("4".equals(flag)){
			sql.append(" and A.RELEASE_UNIT in (select DEPT_ID from SYS_DEPARTMENT where PARENT_ID='"+corp_id+"' ) ");
		}else{
			sql.append(" and ( A.RELEASE_UNIT in ('"+corp_id+"') or A.RELEASE_UNIT in ( '"+parentID+"' ) or A.RELEASE_UNIT in (select DEPT_ID from SYS_DEPARTMENT where PARENT_ID='"+corp_id+"' ) ) ");
		}
		sql.append(" order by A.UPDATEDATE DESC ");
		log.info("新预警与响应："+sql);
		List<NoticeVo> list2 = new ArrayList<NoticeVo>();
		try {
			List<Map<String,String>> list = new ORGTemplate().query(sql.toString());
			for(int i=0;i<list.size();i++){
				Map<String,String> map = list.get(i);
				NoticeVo vo = new NoticeVo();
				vo.setCorpName(map.get("CORP_NAME"));
				vo.setFd_objectid(map.get("FD_OBJECTID"));
				vo.setIsAlert(map.get("IS_ALERT"));
				vo.setWarningName(map.get("EARLY_WARNING_NAME"));
				vo.setWriteTime(map.get("RELEASE_RELIEVE_DATE"));
				vo.setAlertcol(map.get("CURRENT_EW_LEVEL"));
				vo.setResponsecol(map.get("CURRENT_ER_LEVEL"));
				
				list2.add(vo);
			}
		
		} catch (SQLException e) {
			log.error(e.getMessage(),e);
		}
		log.info("新预警与响应数据："+JSONArray.fromObject(list2).toString());
		return JSONArray.fromObject(list2).toString();
	}

	public String getNumbData(String corp_id, String parentID, String flag) {
		String result = "";
		StringBuffer sql1 = new StringBuffer();
		StringBuffer sql2 = new StringBuffer();
		StringBuffer sql3 = new StringBuffer();
		sql1.append(" select distinct A.FD_OBJECTID ,A.PLAN_TYPE_ID,A.UPDATEDATE, A.EARLY_WARNING_NAME, A.RELEASE_RELIEVE_DATE , B.CORP_NAME , A.CURRENT_EW_LEVEL ,A.IS_SEND, A.CURRENT_ER_LEVEL ,A.IS_ALERT ");
		sql1.append(" from ECM_EM_PUBLISH_ALERT_NOTICE A left join SYS_DEPARTMENT B  ON B.DEPT_ID = A.RELEASE_UNIT ");
		sql1.append(" left join ECM_EM_TREATED_HUMAN H ON A.FD_OBJECTID = H.BUSINESS_ID ");
		sql1.append(" where A.is_del='0' and A.IS_SEND =='5' ");
		sql1.append(" and A.RELEASE_UNIT in ( '"+parentID+"' ) ");
		sql1.append(" order by A.UPDATEDATE DESC ");
		
		//sql.append(" UNION ALL ");
		sql2.append(" select distinct A.FD_OBJECTID ,A.PLAN_TYPE_ID,A.UPDATEDATE, A.EARLY_WARNING_NAME, A.RELEASE_RELIEVE_DATE , B.CORP_NAME , A.CURRENT_EW_LEVEL ,A.IS_SEND, A.CURRENT_ER_LEVEL ,A.IS_ALERT ");
		sql2.append(" from ECM_EM_PUBLISH_ALERT_NOTICE A left join SYS_DEPARTMENT B  ON B.DEPT_ID = A.RELEASE_UNIT ");
		sql2.append(" left join ECM_EM_TREATED_HUMAN H ON A.FD_OBJECTID = H.BUSINESS_ID ");
		sql2.append(" where A.is_del='0' and A.IS_SEND =='5' ");
		sql2.append(" and A.RELEASE_UNIT in ('"+corp_id+"') ");
		sql2.append(" order by A.UPDATEDATE DESC ");
		
		//sql.append(" UNION ALL ");
		sql3.append(" select distinct A.FD_OBJECTID ,A.PLAN_TYPE_ID,A.UPDATEDATE, A.EARLY_WARNING_NAME, A.RELEASE_RELIEVE_DATE , B.CORP_NAME , A.CURRENT_EW_LEVEL ,A.IS_SEND, A.CURRENT_ER_LEVEL ,A.IS_ALERT ");
		sql3.append(" from ECM_EM_PUBLISH_ALERT_NOTICE A left join SYS_DEPARTMENT B  ON B.DEPT_ID = A.RELEASE_UNIT ");
		sql3.append(" left join ECM_EM_TREATED_HUMAN H ON A.FD_OBJECTID = H.BUSINESS_ID ");
		sql3.append(" where A.is_del='0' and A.IS_SEND =='5' ");
		sql3.append(" and A.RELEASE_UNIT in (select DEPT_ID from SYS_DEPARTMENT where PARENT_ID='"+corp_id+"' ) ");
		sql3.append(" order by A.UPDATEDATE DESC ");
		//log.info("新预警与响应SQL："+sql);
		List<NoticeVo> list = new ArrayList<NoticeVo>();
		try {
			List<Map<String,String>> list1 = new ORGTemplate().query(sql1.toString());
			for(int i=0;i<list1.size();i++){
				Map<String,String> map = list1.get(i);
				NoticeVo vo = new NoticeVo();
				vo.setCorpName(map.get("CORP_NAME"));
				vo.setFd_objectid(map.get("FD_OBJECTID"));
				vo.setIsAlert(map.get("IS_ALERT"));
				vo.setWarningName(map.get("EARLY_WARNING_NAME"));
				vo.setWriteTime(map.get("RELEASE_RELIEVE_DATE"));
				vo.setAlertcol(map.get("CURRENT_EW_LEVEL"));
				vo.setResponsecol(map.get("CURRENT_ER_LEVEL"));
				
				list.add(vo);
			}
			List<Map<String,String>> list2 = new ORGTemplate().query(sql2.toString());
			for(int i=0;i<list2.size();i++){
				Map<String,String> map = list2.get(i);
				NoticeVo vo = new NoticeVo();
				vo.setCorpName(map.get("CORP_NAME"));
				vo.setFd_objectid(map.get("FD_OBJECTID"));
				vo.setIsAlert(map.get("IS_ALERT"));
				vo.setWarningName(map.get("EARLY_WARNING_NAME"));
				vo.setWriteTime(map.get("RELEASE_RELIEVE_DATE"));
				vo.setAlertcol(map.get("CURRENT_EW_LEVEL"));
				vo.setResponsecol(map.get("CURRENT_ER_LEVEL"));
				
				list.add(vo);
			}
			List<Map<String,String>> list3 = new ORGTemplate().query(sql3.toString());
			for(int i=0;i<list3.size();i++){
				Map<String,String> map = list3.get(i);
				NoticeVo vo = new NoticeVo();
				vo.setCorpName(map.get("CORP_NAME"));
				vo.setFd_objectid(map.get("FD_OBJECTID"));
				vo.setIsAlert(map.get("IS_ALERT"));
				vo.setWarningName(map.get("EARLY_WARNING_NAME"));
				vo.setWriteTime(map.get("RELEASE_RELIEVE_DATE"));
				vo.setAlertcol(map.get("CURRENT_EW_LEVEL"));
				vo.setResponsecol(map.get("CURRENT_ER_LEVEL"));
				
				list.add(vo);
			}
			int num1=list1.size();
			int num2=list2.size();
			int num3=list3.size();
			int num4=num1+num2+num3;
			List<Map<String,String>> list4 = new ArrayList<Map<String,String>>();
			Map<String,String> map1 = new HashMap<String, String>();
			map1.put("num1", num4+"");
			map1.put("num2", num1+"");
			map1.put("num3", num2+"");
			map1.put("num4", num3+"");
			list4.add(map1);
			result = "[{\"listData\":"+JSONArray.fromObject(list).toString()+",\"numbdata\":"+JSONArray.fromObject(list4).toString()+"}]";
		} catch (SQLException e) {
			log.error(e.getMessage(),e);
		}
		//log.info("新预警与响应数据："+JSONArray.fromObject(list).toString());
		log.info("新预警与响应数据："+result);
		
		return result;
	}
	
	
	
}