GZ/nwyj/.svn/pristine/b7/b7b7c8ef41143ffe5dcbabf3a782035b4de06a71.svn-base

package com.sinosoft.em.baobiao.rcbb.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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.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.em.alert.report.manage.SQLPreparedUtil;
import com.sinosoft.em.baobiao.rcbb.vo.ReportPlanBean;
import com.sinosoft.em.baobiao.rcbb.vo.ReportProgress;
import com.sinosoft.em.baobiao.rcbb.vo.reportPlan;
import com.sysmodel.datamodel.xmlmodel.ModelFactory;
import com.sysmodel.datamodel.xmlmodel.able.SysModel;

public class RCReportPlanDao{
	private static SysModel			sysmodel	= ModelFactory.getSysmodel();

	@SuppressWarnings("unused")
	private static SysPersistence	persistence	= PersistenceFactory.getInstance(sysmodel);

	private final static Logger		log			= Logger.getLogger(RCReportPlanDao.class);
	
	/**
	 * 查询部门下报告填报信息
	 */
	public List<ReportProgress> loadReportProgramme(String deptId,
				String[] progressTime,String report_type) throws Exception {
	
		String currprogressTime ="";
		String currprogressTimeE = "";
		String preproTime="";
		String sql="";
		//上一个计划时间
			preproTime = progressTime[0];
			//当前计划时间
			currprogressTime = progressTime[1];
			currprogressTimeE = progressTime[2];
			
			if(preproTime.length()==4){
				sql = "SELECT a.FD_OBJECTID,to_char(a.REPORT_TIME,'yyyy-mm-dd hh24:mi:ss'),b.real_name REPORTER_ID,to_char(a.UPDATEDATE,'yyyy-mm-dd hh24:mi:ss'),a.REPORT_TYPE,a.comp_id FROM ECM_EM_REPORT_RECORDS a,sys_user_info b WHERE a.REPORTER_ID=b.user_id and "+
						"a.COMP_ID='"+deptId+"'"+
						" AND a.IS_DEL=0 AND a.APPEAR_STAT=1 AND a.year='"+preproTime+"' and a.season='"+currprogressTimeE+"' and a.report_type='"+report_type+"' order by updatedate desc";
			}else{
			 sql = "SELECT a.FD_OBJECTID,to_char(a.REPORT_TIME,'yyyy-mm-dd hh24:mi:ss'),b.real_name REPORTER_ID,to_char(a.UPDATEDATE,'yyyy-mm-dd hh24:mi:ss'),a.REPORT_TYPE,a.comp_id FROM ECM_EM_REPORT_RECORDS a,sys_user_info b WHERE a.REPORTER_ID=b.user_id and "+
					"a.COMP_ID='"+deptId+"'"+
					" AND a.IS_DEL=0 AND a.APPEAR_STAT=1 AND to_char(a.UPDATEDATE,'yyyy-mm-dd') BETWEEN '"+preproTime+"' AND '"+currprogressTimeE+"' and a.report_type='"+report_type+"' order by updatedate desc";
			}
//		DbConnection db = new DbConnection();
//		Connection conn = null;
//		PreparedStatement ps = null;
//		ResultSet rs = null;
		SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		try {
			
//			String sql = "SELECT a.FD_OBJECTID,to_char(a.REPORT_TIME,'yyyy-mm-dd hh24:mi:ss'),b.real_name REPORTER_ID,to_char(a.UPDATEDATE,'yyyy-mm-dd hh24:mi:ss'),a.REPORT_TYPE,a.comp_id FROM ECM_EM_REPORT_RECORDS a,sys_user_info b WHERE a.REPORTER_ID=b.user_id and "+
//					"a.COMP_ID='"+deptId+"'"+
//					" AND a.IS_DEL=0 AND a.APPEAR_STAT=1 AND to_char(a.UPDATEDATE,'yyyy-mm-dd') BETWEEN '"+preproTime+"' AND '"+currprogressTimeE+"' and a.report_type='"+report_type+"' order by updatedate desc";
			log.info("loadReportProgramme_sql:"+sql);
//			conn = db.getConnection();
//			ps = conn.prepareStatement(sql);
//			rs = ps.executeQuery();
			ArrayList<ReportProgress> list = new ArrayList<ReportProgress>();
			List<String[]> tempList = persistence.getSearchResult(99, sql.toString());
			if(tempList!=null&&tempList.size()>0){
				String[]arr=tempList.get(0);
				ReportProgress pro = new ReportProgress();
				pro.setFd_object_id(arr[0]);
				pro.setWRITE_TIME(arr[1]);
				pro.setUpdateTime(arr[3]);
				pro.setWRITER(arr[2]);
				pro.setCOMP_ID(arr[5]);
				pro.setREPORT_TYPE(arr[4]);
				list.add(pro);
			}
			
//			while (rs.next()) {
//				ReportProgress pro = new ReportProgress();
//				pro.setFd_object_id(rs.getString("FD_OBJECTID"));
//				pro.setWRITE_TIME(dateformat.format(rs.getDate("REPORT_TIME")));
//				pro.setUpdateTime(dateformat.format(rs.getDate("UPDATEDATE")));
//				pro.setWRITER(rs.getString("REPORTER_ID"));
//				pro.setCOMP_ID(rs.getString("COMP_ID"));
//				pro.setREPORT_TYPE(rs.getString("REPORT_TYPE"));
//				list.add(pro);
//			}
			log.info("proList:"+list);
			return list;

			
		} catch (Exception e) {
			throw e;
		}
	}
	
	/**
	 * 查询计划
	 */
	public List<reportPlan> loadReportPlan(String deptId,String role_id) throws Exception {
		
		DbConnection db = new DbConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			//系统当前时间
			Long currentTime = System.currentTimeMillis();
			Date currDate = new Date(currentTime);
			SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd");
			SimpleDateFormat dateformat1 = new SimpleDateFormat("yyyy-MM-dd HH:mm"); 
			String strcurr = dateformat.format(currDate);
			String sql="";
				sql="select project_name,FD_OBJECTID,UPDATEDATE,FILL_IN_SCHEDULE,TEMPORARY_TIME from ECM_EM_RC_SCHEDULE where (COMP_ID=? or ','||report_dept||','  like ?) and is_del='0' and send_stat='1'";
			log.info("loadReportProgramme_sql:"+sql);
			conn = db.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, deptId);
			ps.setString(2, "%"+deptId+"%");
			rs = ps.executeQuery();
			ArrayList<reportPlan> list = new ArrayList<reportPlan>();
			
			while(rs.next()){
				String updatedate = rs.getString("UPDATEDATE").split(" ")[0];
				String write_type = rs.getString("FILL_IN_SCHEDULE");
				String temp_time = rs.getString("TEMPORARY_TIME");
					reportPlan plan = new reportPlan();
					plan.setPlanId(rs.getString("FD_OBJECTID"));
					plan.setPlanName(rs.getString("PROJECT_NAME"));
					plan.setCompId(deptId);
					list.add(plan);
//				}
			}
		
			log.info("planList"+list);
			
			return list;
		} catch (Exception e) {
			throw e;
		}finally{
			rs.close();
			ps.close();
			conn.close();
		}
	}
	
	/**
	 * 查询部门
	 */
	public List<ReportPlanBean> loadReportDept(String planId,String comp_id,String role_id) throws Exception {
	
		DbConnection db = new DbConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		try {
			String sql = "select project_name,comp_id,REPORT_DEPT,"+
					"FILL_IN_SCHEDULE,TEMPORARY_TIME,UPDATEDATE FROM ECM_EM_RC_SCHEDULE "+
					"WHERE FD_OBJECTID='"+planId+"' AND IS_DEL=0 AND SEND_STAT=1";
			
			log.info("loadReportProgramme_sql:"+sql);
			conn = db.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			//填报部门ID，可以有多个，要进行字符串截取
			String deptIds = "";
			//计划名称
			String pro_name="";
			//截止日期
			Date expirationData = new Date();
			//填报时间表
			String writeDataType = "";
			//临时性
			String temporaryTime = "";
			String comp="";
			
			List<String>deptList=new ArrayList<String>();
			List<String>tabList=new ArrayList<String>();
			Boolean flag=false;
			String updatedate = "";
			while(rs.next()){
				deptIds = rs.getString("REPORT_DEPT");
//				earlyWarningId = rs.getString("WARNiNG_NUMBER");
//				expirationData = rs.getDate("END_TIME");
				pro_name=rs.getString("PROJECT_NAME").split("_")[0];
//				start_time = rs.getDate("START_TIME");
				writeDataType = rs.getString("FILL_IN_SCHEDULE");
//				circleTime = rs.getString("ROUND_TIME");
				updatedate = rs.getString("UPDATEDATE").split(" ")[0];
				comp=rs.getString("COMP_ID");
				//修改
//				List<String>deptList=new ArrayList<String>();
//				List<String[]>tabList=new ArrayList<String[]>();
//				if(deptIds.startsWith("应急办")){
//					if("R002105430205".equals(role_id)){
//						deptIds=deptIds.split(":")[1];
//						String[]arr=deptIds.split("[|]");
//						for(int i=0;i<arr.length;i++){
//							String info=arr[i];
//							deptList.add(info.split(";")[0]);
//							tabList.add(info.split(";")[1].split(","));
//						}
//					}else{
//						deptIds=deptIds.split(":")[1];
//						String[]arr=deptIds.split("[|]");
//						for(int i=0;i<arr.length;i++){
//							String info=arr[i];
//							if(role_id.equals(info.split(";")[0])){
//								deptList.add(info.split(";")[0]);
//								tabList.add(info.split(";")[1].split(","));
//							}
//						}
//					}
//					
//					flag=true;
//				}else{
//					deptIds=deptIds.split(":")[1];
//					String[]arr=deptIds.split(";");
					for(int i=0;i<deptIds.split(",").length;i++){
						deptList.add(deptIds.split(",")[i]);
					}
					
					tabList.add(getClassIdByName(pro_name));
					temporaryTime = rs.getString("TEMPORARY_TIME");
				}
				
//			}
			//通过判断获取离当前时间最近的计划时间
			String progressTime = judgeTime(writeDataType, temporaryTime);
			log.info("progressTime====="+progressTime);
//			String[] deptIdArr = deptIds.split(",");
//			/*********打桩**********************************************/
//			log.info("deptIdArr:"+Arrays.toString(deptIdArr));
			if(comp_id.equals(comp)){
				flag=true;
			}
			List<ReportPlanBean> depts = loadReportDeptById(flag,deptList,getClassIdByName(pro_name),progressTime,comp_id);
			return depts;
		} catch (Exception e) {
			throw e;
		}finally{
			rs.close();
			ps.close();
			conn.close();
		}
	}
	
	/**
	 * 通过部门ID到部门表中查询出相应的部门名称
	 * **/
	public List<ReportPlanBean> loadReportDeptById(boolean flag,List<String> deptIdArr,String report_type,
			String progressTime,String comp_id) throws Exception{
		DbConnection db = new DbConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = db.getConnection();
			//生成sql语句中in的问号
//			String inCondition = 
//					SQLPreparedUtil.preparePlaceHolders(deptIdArr.length);
//			log.info("inCondition:"+inCondition);
			StringBuffer sb=new StringBuffer();
			for(int i=0;i<deptIdArr.size();i++){
				sb.append("'"+deptIdArr.get(i)+"',");
			}
			if (sb.lastIndexOf(",") > -1) {
				sb.deleteCharAt(sb.lastIndexOf(","));
				
			}
			log.info(sb.toString());
			String sql="";
//			if(flag){
//				sql = "select role_name,role_id FROM sys_role "+
//						"WHERE  role_id "+
//						"IN("+sb.toString()+") order by role_id";
//				
//			}else{
			if(flag){
				sql="select dept_id role_id,dept_name role_name from sys_department where dept_id in("+sb.toString()+")";	
			}else{
				sql="select dept_id role_id,dept_name role_name from sys_department where dept_id ='"+comp_id+"'";
			}
//			log.info("sql_write_dept:"+sql);
			ps = conn.prepareStatement(sql);
//			for (int i = 0; i < deptIdArr.length; i++) {
//				ps.setString(i+1,deptIdArr[i]);
//			}
			
			rs = ps.executeQuery();	
			//用于存储部门信息
			ArrayList<ReportPlanBean> depts = new ArrayList<ReportPlanBean>();
			int i=0;
			while(rs.next()){
				ReportPlanBean dept=new ReportPlanBean();
				dept.setID(rs.getString("ROLE_ID"));
				dept.setRESPONSIBLE_NAME(rs.getString("ROLE_NAME"));
				dept.setIconClass("icon_home");
				dept.setOpen(false);
//				dept.setEarlyWarningId(earlyWarningId);//预警编号
				StringBuffer tabType=new StringBuffer();
				//log.info("tabList==="+tabList.size());
//				if(flag){
//					for(int j=0;j<tabList.get(i).length;j++){
//							tabType.append(getTypeByName(tabList.get(i)[j])+";");
//						}
//				}else{
//					for(int j=0;j<tabList.get(0).length;j++){
//						tabType.append(tabList.get(0)[j]+";");
//					}
//				}
//				for(int j=0;j<tabList.get(i).length;j++){
//					if(flag){
//						tabType.append(getTypeByName(tabList.get(i)[j])+";");
//					}else{
//						tabType.append(tabList.get(0)[j]+";");
//					}
//				}
				if(tabType.lastIndexOf(";")>-1){
					tabType.deleteCharAt(tabType.lastIndexOf(";"));
				}
				//参数，查看部门下报告填报情况时所用
//				if(flag){
//					dept.setParams(tabType.toString()+","+dept.getEarlyWarningId()+","+progressTime+","+comp_id);
//				}else{
					dept.setParams(report_type+","+progressTime+","+rs.getString("ROLE_ID"));
//				}
//				dept.setParams(tabType.toString()+","+dept.getEarlyWarningId()+","+progressTime+","+comp_id);
				depts.add(dept);
				i++;
			}
			//部门id和部门名打桩
			log.info("depts:"+depts);
			
			return depts	;
		} catch (Exception e) {
			throw e;
		}finally{
			rs.close();
			ps.close();
			conn.close();
		}
	}
	
	public String getClassIdByName(String report_name){
		String result="";
		String sql="select report_type from bm_mapforrcreport where REPORT_NAME='"+report_name+"'";
		SysModel sysmodel = ModelFactory.getSysmodel();
		SysPersistence persistence = PersistenceFactory.getInstance(sysmodel);
			try {
				List<String[]>tempList = persistence.getSearchResult(99, sql.toString());
				if(tempList!=null&&tempList.size()>0){
					result=tempList.get(0)[0];
				}
			} catch (PersistenceException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		return result;
	}
	
	public String judgeTime(String writeDataType,
			String temporaryTime) throws ParseException{
		String beforeDate="";
		String afterDate="";
		String scheduleDate="";
		String year="";
		String season="";
		Date currentDate=new Date();
		String curDate="";
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		curDate=sdf.format(currentDate);
		String cur_year=curDate.split("-")[0];
		String cur_month=curDate.split("-")[1];
		String cur_day=curDate.split("-")[2];
		afterDate=sdf.format(new Date());
		if("1".equals(writeDataType)){
			if("01".equals(cur_month)){//年报
				scheduleDate=(Integer.parseInt(cur_year)-1)+"-"+"12"+"-"+"31";
				beforeDate=(Integer.parseInt(cur_year)-1)+"-"+"12"+"-"+"25";
			}else{
				scheduleDate=cur_year+"-"+"12"+"-"+"31";
				beforeDate=cur_year+"-"+"12"+"-"+"25";
			}
//			beforeDate=
		}else if("2".equals(writeDataType)){//半年报
			if("01".equals(cur_month)){
				scheduleDate=(Integer.parseInt(cur_year)-1)+"-"+"12"+"-"+"31";
				beforeDate=(Integer.parseInt(cur_year)-1)+"-"+"12"+"-"+"25";
			}
//			else if("07".equals(cur_month)){
//				scheduleDate=cur_year+"-"+"06"+"-"+"31";
//				beforeDate=cur_year+"-"+"06"+"-"+"25";
//			}
			else if(Integer.parseInt(cur_month)<=7){
				scheduleDate=cur_year+"-"+"06"+"-"+"31";
				beforeDate=cur_year+"-"+"06"+"-"+"25";
			}else{
				scheduleDate=cur_year+"-"+"12"+"-"+"31";
				beforeDate=cur_year+"-"+"12"+"-"+"25";
			}
		}else if("3".equals(writeDataType)){//季报
			if("01".equals(cur_month)){
				year=Integer.parseInt(cur_year)-1+"";
				season="4";
				scheduleDate=(Integer.parseInt(cur_year)-1)+"-"+"12"+"-"+"31";
				beforeDate=(Integer.parseInt(cur_year)-1)+"-"+"12"+"-"+"25";
			}else if(Integer.parseInt(cur_month)<=4){
				year=cur_year;
				season="1";
				scheduleDate=cur_year+"-"+"03"+"-"+"31";
				beforeDate=cur_year+"-"+"03"+"-"+"25";
			}else if(Integer.parseInt(cur_month)<=7){
				year=cur_year;
				season="2";
				scheduleDate=cur_year+"-"+"06"+"-"+"31";
				beforeDate=cur_year+"-"+"06"+"-"+"25";
			}else if(Integer.parseInt(cur_month)<=10){
				year=cur_year;
				season="3";
				scheduleDate=cur_year+"-"+"09"+"-"+"30";
				beforeDate=cur_year+"-"+"09"+"-"+"25";
			}else{
				year=cur_year;
				season="4";
				scheduleDate=cur_year+"-"+"12"+"-"+"31";
				beforeDate=cur_year+"-"+"12"+"-"+"25";
			}
		}else if("4".equals(writeDataType)){//月报
			if("01".equals(cur_month)){
				if(Integer.parseInt(cur_day)<=5){
					scheduleDate=(Integer.parseInt(cur_year)-1)+"-"+"12"+"-"+"31";
					beforeDate=(Integer.parseInt(cur_year)-1)+"-"+"12"+"-"+"25";
				}else{
					scheduleDate=cur_year+"-"+"01"+"-"+"31";
					beforeDate=cur_year+"-"+"01"+"-"+"25";
				}
			}else if(Integer.parseInt(cur_month)<=10){
				if(Integer.parseInt(cur_day)<=5){
					scheduleDate=cur_year+"-"+"0"+(Integer.parseInt(cur_month)-1)+"-"+"31";
					beforeDate=cur_year+"-"+"0"+(Integer.parseInt(cur_month)-1)+"-"+"25";
				}else{
					scheduleDate=cur_year+"-"+"0"+cur_month+"-"+"31";
					beforeDate=cur_year+"-"+"0"+cur_month+"-"+"25";
				}
			}
		}else if("5".equals(writeDataType)){//自定义
			scheduleDate=temporaryTime;
			beforeDate=sdf.format(sdf.parse(temporaryTime).getTime()- 5*24*60*60*1000);
			return beforeDate + "|" + scheduleDate + "|" + afterDate;
		}
		
//		return beforeDate + "," + scheduleDate + "," + afterDate;
		return year+"|"+scheduleDate+"|"+season;
	}
	
	/**
	 * 根据报告类别的常量id查询相应的报告类别名称
	 */
	public String loadReportTypeName(String id) throws Exception {
		DbConnection db = new DbConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String name="";
		try {
//			String conditionOfReportType = "";
//			if(id.length()==1){
//				conditionOfReportType = " FD_CODE='"+id+"'";
//			}else if(id.length()==2){
//				conditionOfReportType = " FD_CODE in ('3','4')";
//			}
			String sql = "select report_name,report_type from BM_MAPFORrcREPORT "+
					"where  report_type='"+id+"'";
			conn = db.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			ArrayList<String[] >list = new ArrayList<String[]>();
			while(rs.next()){
				name = rs.getString("REPORT_NAME");
//				String type = rs.getString("REPORT_TYPE");
//				String[]arr=new String[]{name,type};
//				list.add(arr);
			}
			
		} catch (Exception e) {
			throw e;
		}finally{
			rs.close();
			ps.close();
			conn.close();
		}
		return name;
	}
	
	private String pretime(String time){
		String hour = time.substring(0,time.indexOf(":"));
		String minit = time.substring(time.indexOf(":"),time.length());
		Integer prehour = Integer.parseInt(hour)-1;
		return prehour.toString()+""+minit;
	}
	
	/**
	 * 发布预警/响应通知单时创建报表填报计划
	 * @param areaName 
	 * @param areaId 
	 * @param alertName 
	 * @param real_name 
	 * @param deptName 
	 * @param isAlert 
	 * @param alertId
	 * @param planId
	 * @return
	 * @throws ClassNotFoundException 
	 * @throws ParseException 
	 */
	public Msg createPlanByAlert(String WARNING_NUMBER, String START_TIME,String FILL_IN_SCHEDULE,
			String REPORT_DEPT,String DEPT_ID,String ROUND_TIME,String TEMPORARY_TIME,String USER_ID,
			String role_id,String deptName,String real_name,String alertName,String areaId,String areaName, String isAlert) throws ClassNotFoundException, ParseException{
		Msg m=new Msg();
		log.info("REPORT_DEPT=="+REPORT_DEPT);
		log.info("DEPT_ID==="+DEPT_ID);
		if(DEPT_ID==null||"".equals(DEPT_ID)){
			return m;
		}
		//REPORT_DEPT="11,线路停运及恢复情况表,||@31,线路停运及恢复情况表,||@111,线路停运及恢复情况表,||@78,线路停运及恢复情况表,||@";
		//"11,线路停运及恢复情况表,||@12,线路停运及恢复情况表,||@13,线路停运及恢复情况表,||@14,线路停运及恢复情况表,||@"
		String[]table=REPORT_DEPT.replace("***","-").split("-")[0].replace("@", "").split("[|][|]");
		List<String>table_fdList=new ArrayList<String>();
		for(int i=0;i<table.length;i++){
			table_fdList.add(table[i].split(",")[0]);
		}
		REPORT_DEPT=getTabInfo(table_fdList);
		//应急办:R002105430210;线路停运及恢复情况表,变电站停运及恢复情况表|R002105430211;线路倒杆情况统计表,线路及配变受损情况表|R002105430212;供电损失及客户停电情况表,行政区域停电情况表|R002105430213;预警发布或响应启动情况表,应急资源情况表|
		for(int i=0;i<table_fdList.size();i++){
			
		}
		String sql="insert into REPORT_SCHEDULE(FD_OBJECTID,WARNING_NUMBER,PROJECT_NAME,START_TIME,END_TIME,FILL_IN_SCHEDULE,REPORT_DEPT,comp_id,IS_DEL,SEND_STAT,ROUND_TIME,TEMPORARY_TIME,USER_ID,ROLE_ID,dept,real_name,updatedate)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		//报告填报要求sql
		String sql1="insert into ECM_EM_ALERT_REPORT_PLAN"
				+ "(FD_OBJECTID,EARLY_WARNING_ID,EARLY_WARNING_NAME,"
				+ "PROGRAMME_NAME,START_TIME,EXPIRATION_DATE,REPORT_TYPE,WRITE_DEPT_ID,WRITE_DEPT_NAME,"
				+ "WRITE_DATE_TYPE,CIRCLE_TIME,TEMPORARY_TIME,WRITER,WRITE_TIME,INPUT_DEPT,UPDATEDATE,"
				+ "IS_RELEASE,IS_ALERT,EARLY_WARNING,USER_ID,IS_DEL)"
				+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		Connection conn = null;
		PreparedStatement st = null;
		PreparedStatement st1 = null;
		DbConnection db = new DbConnection();
		ROUND_TIME=ROUND_TIME.replace("#", ",");
		Date crrDate = new Date();
		SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
		String crrStr = dateformat.format(crrDate);//当前日期字符串
		SimpleDateFormat dateformat1 = new SimpleDateFormat("yyyy-MM-dd");  
				
		try {
			conn = db.getConnection();
			int num = 0;
			int num1 = 0;
			conn.setAutoCommit(false);
			st = conn.prepareStatement(sql);
			st1 = conn.prepareStatement(sql1);
			// String fd_id=createMsgId(getMsgId());
			//报表填报要求部分
			st.setString(1, IdentityGenerator.getIdentityGenerator().gerenalIdentity(337));
			st.setString(2, WARNING_NUMBER);// 线路停运为1
			st.setString(3, WARNING_NUMBER+"报表计划");
			st.setString(4, START_TIME.split(" ")[0]);
			st.setString(5, "");
			st.setString(6, FILL_IN_SCHEDULE);
			st.setString(7, REPORT_DEPT);
			st.setString(8, DEPT_ID);
			st.setString(9, "0");
			st.setString(10, "0");
//			st.setString(11, DEPT_ID);
			if("周期性".equals(FILL_IN_SCHEDULE)){
				if(ROUND_TIME.startsWith(",")){
					ROUND_TIME=ROUND_TIME.substring(1);
				}
				st.setString(11, ROUND_TIME);
				st.setString(12, "");
			}else{
				st.setString(11, "");
				List<String>tempList=new ArrayList<String>();
				String[]tempArr=TEMPORARY_TIME.split(",");
				TEMPORARY_TIME="";
				for(int i=0;i<tempArr.length;i++){
					TEMPORARY_TIME+=tempArr[i]+";";
				}
				st.setString(12, TEMPORARY_TIME);
			}
			RcBbDao bbd=new RcBbDao();
			
			st.setString(13, USER_ID);
			st.setString(14, role_id);
			st.setString(15, bbd.getNameByCompId(DEPT_ID));
			st.setString(16, bbd.getNameByUserId(USER_ID));
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			st.setString(17, sdf.format(new Date()));
			st.executeUpdate();
			num++;
			
			//报告填报要求部分
			st1.setString(1, IdentityGenerator.getIdentityGenerator().gerenalIdentity(320));
			st1.setString(2, WARNING_NUMBER);// 预警编号
			st1.setString(3, WARNING_NUMBER);// 预警名称，存的数据是预警编号
			st1.setDate(5, new java.sql.Date(dateformat1.parse(START_TIME).getTime()));// 开始日期
			st1.setDate(6, null);// 结束日期
			if("0".equals(isAlert)){
				st1.setString(4, WARNING_NUMBER+"预警报告填报要求");// 填报要求名称
				st1.setString(7, "1,2");// 预警报告类别，同时生成预警行动信息报告和总结报告
			}else if("1".equals(isAlert)){
				st1.setString(4, WARNING_NUMBER+"响应报告填报要求");// 填报要求名称
				st1.setString(7, "3,4");// 响应报告类别
			}
			st1.setString(8, areaId);// 预警范围部门id
			st1.setString(9, areaName);// 预警范围部门名称
			st1.setString(10, FILL_IN_SCHEDULE);// 填报性质：周期性还是临时性
			if("周期性".equals(FILL_IN_SCHEDULE)){
				st1.setString(11, ROUND_TIME);// 周期性时间
				st1.setString(12, "");// 临时性时间
			}else{
				st1.setString(11, "");
				List<String>tempList=new ArrayList<String>();
				String[]tempArr=TEMPORARY_TIME.split(",");
				TEMPORARY_TIME="";
				for(int i=0;i<tempArr.length;i++){
					TEMPORARY_TIME+=tempArr[i]+";";
				}
				st1.setString(12, TEMPORARY_TIME);
			}
			
			st1.setString(13, real_name);// 填报人
			st1.setString(14, crrStr);// 填报时间
			st1.setString(15, DEPT_ID);// 填报人部门id
			st1.setString(16, crrStr);// 更新时间
			st1.setString(17, "0");// 是否发布
			st1.setString(18, isAlert);// 是否预警
			st1.setString(19, alertName);//真实预警名称
			st1.setString(20, USER_ID);//用户id
			st1.setInt(21, 0);//是否删除
			st1.executeUpdate();
			num1++;
			
			if (num == 1 && num1==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(st1);
			db.close(st);
			db.close(conn);
		}
	}

	public String getTabInfo(List<String> fd_id){
		StringBuffer result=new StringBuffer();
		Map<String, String>tempMap=new HashMap<String, String>();
		List<String>keyList=new ArrayList<String>();
		for(int i=0;i<fd_id.size();i++){
			String sql="select b.role_id,a.report_name from EMC_AM_REPORT_DICTIONARY a,BM_MAPFORREPORT B WHERE A.REPORT_TYPE='"+fd_id.get(i)+"' AND A.REPORT_TYPE=B.REPORT_TYPE";
			try {
				List<String[]> tempList = persistence.getSearchResult(99, sql.toString());
				if(tempList!=null&&tempList.size()>0){
					String key=tempList.get(0)[0];
					String value=tempList.get(0)[1];
					if(tempMap.containsKey(tempList.get(0)[0])){
						tempMap.put(key, tempMap.get(key)+","+value+"|");
					}else{
						tempMap.put(key, value);
						keyList.add(key);
					}
				}
			} catch (PersistenceException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		result.append("应急办:");
		for(int i=0;i<keyList.size();i++){
			result.append(keyList.get(i)+";"+tempMap.get(keyList.get(i)));
		}
		if (result.lastIndexOf("|") > -1) {
			result.deleteCharAt(result.lastIndexOf("|"));
		}
		return result.toString();
	}
	
	public Msg updatePlan(String newWarn,String oldWarn,String comp_id) throws ClassNotFoundException{
		Msg m=new Msg();
		String sql="update REPORT_SCHEDULE set WARNING_NUMBER=?,PROJECT_NAME=?||'计划' where WARNING_NUMBER=? and comp_id=?";
		String sql1="update ECM_EM_ALERT_REPORT_PLAN set EARLY_WARNING_ID=?,PROGRAMME_NAME=?,EARLY_WARNING_NAME=?,EARLY_WARNING=?,UPDATEDATE=? where EARLY_WARNING_ID=? and INPUT_DEPT=?";
		Connection conn = null;
		PreparedStatement st = null;
		PreparedStatement st1 = null;
		DbConnection db = new DbConnection();
		Date crrDate = new Date();
		SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
		String crrStr = dateformat.format(crrDate);//当前日期字符串
		try {
			conn = db.getConnection();
			int num = 0;
			int num1 = 0;
			conn.setAutoCommit(false);
			st = conn.prepareStatement(sql);
			st1 = conn.prepareStatement(sql1);
			//报表
			st.setString(1, newWarn);
			st.setString(2, newWarn);
			st.setString(3, oldWarn);
			st.setString(4, comp_id);
			st.executeUpdate();
			num++;

			//报告
			String earlyName = newWarn.split("-")[0];
			st1.setString(1, newWarn);
			st1.setString(2, newWarn+"报告填报要求");
			st1.setString(3, newWarn);
			st1.setString(4, earlyName);
			st1.setString(5, crrStr);
			st1.setString(6, oldWarn);
			st1.setString(7, comp_id);
			st1.executeUpdate();
			num1++;
			
			if (num == 1 && num1==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(st1);
			db.close(st);
			db.close(conn);
		}
	}
	
	public Msg sendPlan(){
		Msg m=new Msg();
		String sql="update REPORT_RECORDS set SEND_STAT='1' where WARNING_NUMBER=? and comp_id=?";//0-未发布 1-发布 2-发布解除
		return m;
	}
	
	public Msg releasePlan(String WARNING_NUMBER,String COMP_ID) throws ClassNotFoundException{
		Msg m=new Msg();
		String sql="update REPORT_SCHEDULE set SEND_STAT='2',end_time=? where warning_number=? and comp_id=?";//0-未发布 1-发布 2-发布解除
		String sql1="update ECM_EM_ALERT_REPORT_PLAN set EXPIRATION_DATE=?,IS_RELEASE=? where EARLY_WARNING_ID=? and INPUT_DEPT=?";
		Connection conn = null;
		PreparedStatement st = null;
		PreparedStatement st1 = null;
		DbConnection db = new DbConnection();
		try {
			conn = db.getConnection();
			int num=0;
			int num1=0;
			Date dd=new Date();
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			String date=sdf.format(dd);
			conn.setAutoCommit(false);
			st = conn.prepareStatement(sql);
			st1 = conn.prepareStatement(sql1);
			st.setString(1, date);
			st.setString(2, WARNING_NUMBER);
			st.setString(3, COMP_ID);
			st.executeUpdate();
			num++;
			
			st1.setString(1, date);
			st1.setString(2, "2");
			st1.setString(3, WARNING_NUMBER);
			st1.setString(4, COMP_ID);
			st1.executeUpdate();
			num1++;
			if(num==1&&num1==1){
				log.info("预警/响应通知单解除报表记录表成功");
				 conn.commit();
				 m.setSucsess(new Boolean(true).toString());
				 m.setInfo(Parameter.DELETE_SUCSESS);
			}else{
				m.setSucsess(new Boolean(false).toString());
				m.setInfo(Parameter.DELETE_FAILURE);
//			 st.executeBatch();
			 conn.rollback(); 
			 log.info("预警/响应通知单解除报表记录表成功");
			 }
		} catch (SQLException e) {
			this.log.error(e.getMessage(), e);
			throw new ClassNotFoundException("DAO　Layou: 消息保存", e);
		} finally {
				try{
					if(st1!= null){
					st1.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
			}
			db.close(st);
			db.close(conn);
		}
		return m;
	}
	
//	public Msg createPlan(Report_Schedule rs) throws ClassNotFoundException{
//		Msg m=new Msg();
//		String sql="insert into REPORT_SCHEDULE(FD_OBJECTID,WARNING_NUMBER,PROJECT_NAME,START_TIME,END_TIME,FILL_IN_SCHEDULE,REPORT_DEPT,DEPT_ID,IS_DEL,COMP_ID,SEND_STAT,ROUND_TIME,TEMPORARY_TIME,USER_ID)values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
//		Connection conn = null;
//		PreparedStatement st = null;
//		DbConnection db = new DbConnection();
//		try {
//			conn = db.getConnection();
//			int num=0;
//			conn.setAutoCommit(false);
//			st = conn.prepareStatement(sql);
//			st.setString(1, UUID.randomUUID()+"");
//			st.setString(2, rs.getWARNING_NUMBER());
//			st.setString(3, rs.getWARNING_NUMBER()+"报表");
//			st.setString(4, rs.getSTART_TIME());
//			st.setString(5, rs.getEND_TIME());
//			st.setString(6, rs.getFILL_IN_SCHEDULE());
//			st.setString(7, rs.getREPORT_DEPT());
//			st.setString(8, rs.getDEPT_ID());
//			st.setString(9, "0");
//			st.setString(10, rs.getDEPT_ID());
//			st.setString(11, "0");
//			st.setString(12, rs.getROUND_TIME());
//			st.setString(13, rs.getTEMPORARY_TIME());
//			st.setString(14, rs.getUSER_ID());
//			st.executeUpdate();
//			num++;
//			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);
//		}
//	}
	
	/**
	 * 获取所有的日常报表
	 * @return
	 */
	public String getRcReport(){
		String sql = "select REPORT_TYPE,REPORT_NAME from BM_MAPFORRCREPORT";
		ORGTemplate org = new ORGTemplate();
		String result = "";
		try {
			List<Map<String,String>> list = org.getSerachResult(sql);
			if(list!=null && list.size()>0){
				result = JSONArray.fromObject(list).toString();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	
	
	
	
	public String queryReportComp(String comp_id,String comp_name,String report_type){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String cur=sdf.format(date); 
		String month=cur.split("-")[1];
		String day=cur.split("-")[2];
		String year=cur.split("-")[0];
		String time = "";
		String report_time = "";
		String season = "";
		
		try {
			if(date.compareTo(sdf.parse(year+"-01-10"))>0 && date.compareTo(sdf.parse(year+"-04-10"))<=0){
				time += "between '"+year+"-03-20' and '"+year+"-04-10'";
				report_time = year+"-03-20";
				season = "1";
			}else if(date.compareTo(sdf.parse(year+"-04-10"))>0 && date.compareTo(sdf.parse(year+"-07-10"))<=0){
				time += "between '"+year+"-06-20' and '"+year+"-07-10'";
				report_time = year+"-06-20";
				season = "2";
			}else if(date.compareTo(sdf.parse(year+"-07-10"))>0 && date.compareTo(sdf.parse(year+"-10-10"))<=0){
				time += "between '"+year+"-09-20' and '"+year+"-10-10'";
				report_time = year+"-09-20";
				season = "3";
			}else if(date.compareTo(sdf.parse(year+"-10-10"))>0 && date.compareTo(sdf.parse(Integer.valueOf(year)+1+"-01-10"))<=0){
				time += "between '"+year+"-12-20' and '"+(Integer.valueOf(year)+1)+"-01-10'";
				report_time = year+"-12-20";
				season = "4";
			}else{//超时
				time += "between '"+(Integer.valueOf(year)-1)+"-12-20' and '"+year+"-01-10'";
				report_time = (Integer.valueOf(year)-1)+"-12-20";
				season = "4";
			}
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		ReportPlanBean rpb = new ReportPlanBean();
		rpb.setID(comp_id);
		rpb.setOpen(false);
		rpb.setRESPONSIBLE_NAME(comp_name);
		rpb.setIconClass("icon_home");
		rpb.setParams(report_type+","+year+"|"+report_time+"|"+season+","+comp_id);
		return "{\"rows\":"+JSONArray.fromObject(rpb).toString()+"}";
	}
	
	
	
	
	
	
	/**
	 *  获取本单位报表的填报情况
	 * @param report_type
	 * @param comp_id
	 * @param role_id
	 * @return
	 */
	public String queryReportRecords(String report_type,String comp_id,String role_id,String comp_name,String report_name){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String cur=sdf.format(date); 
		String month=cur.split("-")[1];
		String day=cur.split("-")[2];
		String year=cur.split("-")[0];
		String time = "";
		String report_time = "";
		String season = "";
		ORGTemplate org = new ORGTemplate();
		List<com.sinosoft.em.alert.report.manage.vo.ReportProgress> compList = getChildComp(comp_id,comp_name);
		try {
			if(date.compareTo(sdf.parse(year+"-01-31"))>0 && date.compareTo(sdf.parse(year+"-04-30"))<=0){
				time += "between '"+year+"-03-01' and '"+year+"-04-30'";
				report_time = year+"-03-31";
				season = "1";
			}else if(date.compareTo(sdf.parse(year+"-04-30"))>0 && date.compareTo(sdf.parse(year+"-07-31"))<=0){
				time += "between '"+year+"-06-01' and '"+year+"-07-31'";
				report_time = year+"-06-31";
				season = "2";
			}else if(date.compareTo(sdf.parse(year+"-07-31"))>0 && date.compareTo(sdf.parse(year+"-10-31"))<=0){
				time += "between '"+year+"-09-01' and '"+year+"-10-31'";
				report_time = year+"-09-30";
				season = "3";
			}else if(date.compareTo(sdf.parse(year+"-10-31"))>0 && date.compareTo(sdf.parse(Integer.valueOf(year)+1+"-01-31"))<=0){
				time += "between '"+year+"-12-01' and '"+(Integer.valueOf(year)+1)+"-01-31'";
				report_time = year+"-12-31";
				season = "4";
			}else{//超时
				time += "between '"+(Integer.valueOf(year)-1)+"-12-01' and '"+year+"-01-31'";
				report_time = (Integer.valueOf(year)-1)+"-12-31";
				season = "4";
			}
			String sql = "select *,(case when APPEAR_TIME>'"+report_time+"' then '是' when APPEAR_TIME<='"+report_time+"' then '否' else null end) overstat from ECM_EM_REPORT_RECORDS where COMP_ID=? and REPORT_TYPE=? and to_date(APPEAR_TIME,'yyyy-mm-dd') "+time+" and IS_DEL='0'";
			for(int i=0;i<compList.size();i++){
				List<Map<String,String>> list = org.getSerachResult(sql, compList.get(i).getDeptId(),report_type);
				if(list!=null && list.size()>0){
					Map<String,String> map = list.get(0);
					com.sinosoft.em.alert.report.manage.vo.ReportProgress rpb = compList.get(i);
					rpb.setFd_object_id(map.get("FD_OBJECTID"));
					rpb.setReport_type(report_type);
					rpb.setRESPONSIBLE_NAME_322(report_name);
					rpb.setPROGRAMME_TIME_322(report_time);
					rpb.setWRITER_322(map.get("REPORTER"));
					rpb.setWRITER_ID_322(map.get("REPORTER_ID"));
					rpb.setWRITE_TIME_322(map.get("APPEAR_TIME"));
					rpb.setWRITER_STATE_322("已填报");
					rpb.setIS_OVERTIME_322(map.get("OVERSTAT"));
					rpb.setUpdateTime(map.get("APPEAR_TIME"));
				}else{
					com.sinosoft.em.alert.report.manage.vo.ReportProgress rpb = compList.get(i);
					rpb.setReport_type(report_type);
					rpb.setRESPONSIBLE_NAME_322(report_name);
					rpb.setPROGRAMME_TIME_322(report_time);
					rpb.setWRITER_STATE_322("未填报");
					if(date.compareTo(sdf.parse(report_time))<0){
						rpb.setIS_OVERTIME_322("否");
					}else{
						rpb.setIS_OVERTIME_322("是");
					}
				}
			}
			return "{\"rows\":"+JSONArray.fromObject(compList).toString()+"}";
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "{\"rows\":[]}";
	}
	
	public List<com.sinosoft.em.alert.report.manage.vo.ReportProgress> getChildComp(String comp_id,String comp_name){
		String sql = "select dept_id,dept_name from SYS_DEPARTMENT where parent_id=? and dept_name like '%供电局%' and is_del='0' order by sort";
		ORGTemplate org = new ORGTemplate();
		List<com.sinosoft.em.alert.report.manage.vo.ReportProgress> compList = new ArrayList<com.sinosoft.em.alert.report.manage.vo.ReportProgress>();
		try {
				List<Map<String,String>> list = org.getSerachResult(sql, comp_id);
				if(list!=null && list.size()>0){
					for(int i=0;i<list.size();i++){
						com.sinosoft.em.alert.report.manage.vo.ReportProgress rp = new com.sinosoft.em.alert.report.manage.vo.ReportProgress();
						rp.setDeptId(list.get(i).get("DEPT_ID"));
						rp.setDeptName(list.get(i).get("DEPT_NAME"));
						compList.add(rp);
					}
				}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return compList;
	}
	
	
}