wp/nwyj/.svn/pristine/b9/b97355b4a4b5ec1f1b8a965378d2786984cda972.svn-base

package com.sinosoft.cm.common.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import nl.justobjects.pushlet.util.Sys;










import org.apache.log4j.Logger;

import com.persistence.DBdll.SysOperator;
import com.sinosoft.cm.CMHandlerResultSet;
import com.sinosoft.cm.CMTemplate;
import com.sinosoft.cm.common.DateUtil;
import com.sinosoft.cm.common.StringUtils;
import com.sinosoft.cm.common.dao.HeightChartDao;
import com.sinosoft.cm.common.vo.HeightChart;
import com.sinosoft.cm.ex.SQLStringException;
import com.sinosoft.common.excel.JsonPluginsUtil;
import com.sun.org.apache.bcel.internal.generic.NEW;

import flex.messaging.io.ArrayList;

public  class HeightChartDaoImpl implements HeightChartDao {
	protected Logger	log			= Logger.getLogger(this.getClass());
	protected CMTemplate cmt=null;
	protected Map<String,String> map=null;

	public HeightChartDaoImpl() {
		 cmt=new CMTemplate();
		 map=new HashMap<String, String>();
		 /*获取历史端记录的ID和时间*/
		 map.put("getRecords", "SELECT APPEAR_TIME,REPORT_ID FROM REPORT_RECORDS "
		 		+ "  WHERE APPEAR_TIME BETWEEN TO_DATE(?, 'YYYY-MM-DD HH:MI:SS') "
		 		+ " AND  TO_DATE(?,'YYYY-MM-DD HH:MI:SS') AND COMP_ID    =? AND REPORT_TYPE=? ORDER BY  APPEAR_TIME ");
		 map.put("getColumn", "select COMP_NAME from report_comp_dictionary where comp_id in (?)");
		 
		/*应急演练*/
		 map.put("YJYL", "SELECT DRILL_TYPE, COUNT(*) AS AMOUNT FROM EMC_AM_EMERGENCY_DRILL "
		 		+ "WHERE IS_DEL='0' AND SPONSOR_COMP "
		 		+ "IN ( select DEPT_ID from sys_department where instr(dept_path,?)>0 ) GROUP BY DRILL_TYPE;");
		
		/*应急人员*/
		 map.put("YJRY", "SELECT TEAM_COMP_ID,TEAM_NAME, TEAM_MEMBER_NUMBER FROM ECM_AM_EMERGENCY_TEAM "
		 		+ "WHERE IS_DEL='0' AND TEAM_COMP_ID "
		 		+ "IN (  select DEPT_ID from sys_department where instr(dept_path,?)>0 )");
		
		
		 /*应急预案*/
		map.put("YJYA", "SELECT PLAN_TYPE, COUNT( *) AMOUNT FROM ECM_AM_YJYASERVER "
				+ " WHERE IS_DEL='0' AND UNITS IN ( "
				+ "select DEPT_ID from sys_department where instr(dept_path,?)>0 ) GROUP BY PLAN_TYPE; ");
		
		/*发电车*/
		map.put("CARNUM","SELECT  SUM(CASE WHEN CAR_NUM IS NULL THEN 0 WHEN CAR_NUM='' THEN 0 ELSE TO_NUMBER(CAR_NUM)END) AS AMOUNT "
				+ " FROM EMC_AM_GENERATOR_CAR WHERE  COMP_ID IN( "
				+ "  select DEPT_ID from sys_department where instr(dept_path,?)>0)");
		/*发电车*/
		map.put("NUM", "SELECT  SUM(CASE WHEN NUM IS NULL THEN 0 WHEN NUM='' THEN 0 ELSE TO_NUMBER(NUM)END) AS  AMOUNT  "
				+ "FROM EMC_AM_GENERATOR WHERE  COMP_ID IN(  "
				+ "  select DEPT_ID from sys_department where instr(dept_path,?)>0)");
	
	}
	
	
	
	public List<Map<String,String>> findAllBySqlName(String sqlName,List<String> sqlLabel,Object...args) throws Exception{
		List<Map<String,String>> list=new ArrayList();
		log.info(HeightChartDaoImpl.class+":findAllBySqlName(String sql,List<String> sqlLabel,Object...args) sql:"+map.get(sqlName));
		if(sqlLabel==null||0==sqlLabel.size())throw new Exception("查询接受字段异常");
		return cmt.queryToMap(map.get(sqlName), sqlLabel, args);
	}
	/**
	 * 通过sql语句和标签查询多个字段
	 * @param sql
	 * @param sqlLabel
	 * @param args
	 * @return
	 * @throws SQLStringException
	 */
	public List<Map<String,String>> findAll(String sql,List<String> sqlLabel,Object...args) throws SQLStringException{
		//检查sql语句
		log.info(HeightChartDaoImpl.class+"sql:--------------------"+sql);
		if(sql==null && "".equals(sql.trim())) {
			throw new SQLStringException(HeightChartDaoImpl.class+"查询语句异常:--------------------"+sql);};
		//检查参数
		log.info(HeightChartDaoImpl.class+"参数：--------------------"+args);
		if(args==null && 0==args.length){
			throw new SQLStringException(HeightChartDaoImpl.class+"查询参数异常"+args);};
		//检查接受查询对象
		log.info(HeightChartDaoImpl.class+"参数：--------------------"+sqlLabel);
		if(args==null&& 0== sqlLabel.size()){
				throw new SQLStringException(HeightChartDaoImpl.class+"接受对象异常"+sqlLabel);};
		return (List<Map<String, String>>) cmt.queryToMap(sql, sqlLabel, args);	
	}
	/**
	 * 重载
	 * 通过sql语句查询单个字段
	 */
	public List<String> findAll(String sql,Object...args) throws SQLStringException{
		//检查sql语句
		log.info(HeightChartDaoImpl.class+"sql:--------------------"+sql);
		if(sql==null && "".equals(sql.trim())) {
			throw new SQLStringException(HeightChartDaoImpl.class+"查询语句异常:--------------------"+sql);};
		//检查参数
		log.info(HeightChartDaoImpl.class+"参数：--------------------"+args);
		if(args==null && 0==args.length){
			throw new SQLStringException(HeightChartDaoImpl.class+"查询参数异常"+args);};
		return (List<String>) cmt.queryToList(sql, args);	
	}
	/**
	 * 通过sql语句名查询IN语句
	 * @param sqlName
	 * @param list
	 * @return
	 * @throws SQLStringException
	 */
	public List<String> findAllInId(String sqlName,List<String> list) throws SQLStringException{
		//检查sql语句
		log.info(HeightChartDaoImpl.class+"sql:--------------------"+map.get(sqlName));
		if(map.get(sqlName)==null && "".equals(map.get(sqlName).trim())) {
			throw new SQLStringException(HeightChartDaoImpl.class+"查询语句异常:--------------------"+map.get(sqlName));};
		//检查参数
		log.info(HeightChartDaoImpl.class+"参数：--------------------"+map.get(sqlName));
		if(list==null && 0==list.size()){
			throw new SQLStringException(HeightChartDaoImpl.class+"查询参数异常"+list);};
		StringBuffer str=new StringBuffer();
		for(int i=0;i<list.size();i++ ){
			if(0==i){
				str.append("'"+list.get(i)+"' ");
			}else{
				
				str.append(", '"+list.get(i)+"' ");
			}
		}
		System.out.println("str###########################"+str);
		String sql=map.get(sqlName).replace("?", str);
		System.out.println("====================="+sql);
		return (List<String>) cmt.queryToList(sql);	
	}
	

	/**
	 * 查询数据字典查询需要查询的字段
	 * @param labels 需要查询的字段集合
	 * @param args 1.公司编号，2，高字段是否启用，3该字段是否统计
	 * @return
	 */
	public List<Map<String,String>> getSelectLable(List<String> labels,Object...args){
		try {
			StringBuffer select=new StringBuffer("select");
			Object[] arrStr=labels.toArray();
			for(int index=0,len=arrStr.length;index<len;index++){
				if(0==index) select.append(" "+new StringBuffer(arrStr[index].toString()));
				select.append(", "+new StringBuffer(arrStr[index].toString()));
			}
			select.append(" FROM  EMC_AM_REPORT_DICTIONARY WHERE REPORT_TYPE=? AND IS_USED= ? AND IS_COUNT=?;");
			List<Map<String,String>> list=findAll(select.toString(),labels, args);
			return list;
		} catch (SQLStringException e) {
			e.printStackTrace();
			log.error(e.getMessage());
		}
		return null;
		
	}
	
	
	/**
	 * 拼接求和字段（聚合查询活单条记录查询<SUM,DETAIL,AVG……>）
	 * @param list
	 * @param columnName 需要增加的辅助字段
	 * @return
	 */
	public List<String> appendSql(List<Map<String,String>> list,String columnName,String olymerization){
		
		
		List<String> sumList=new ArrayList();
		for(Map<String,String> map:list){
			
			//String str="SUM ( CASE  WHEN "+ map.get(columnName)+" IS NULL THEN 0 WHEN "+ map.get(columnName)+" ='' THEN 0 ELSE TO_NUMBER( "+map.get(columnName)+" ) END ) AS "+map.get(columnName)+"_SUM";
			StringBuffer str=new StringBuffer();
			if("COMP_ID".equals(map.get(columnName)) && ! "DETAIL".equals(olymerization)){
				break;
			}
			if("COMP_ID".equals(map.get(columnName))){
				str.append(" "+ map.get(columnName)+" " );
			}else{
				str.append("CASE  WHEN "+ map.get(columnName)+" IS NULL THEN 0 WHEN "+ map.get(columnName)+" ='' THEN 0 ELSE  TO_NUMBER( "+map.get(columnName)+" ) END");
			}
				if(!"DETAIL".equals(olymerization)){
					str=new StringBuffer(olymerization+" ( ").append(str).append(" ) ");
				}
				str.append(" AS "+map.get(columnName)+"_SUM");		
			
			sumList.add(str.toString());	
		}
		
		return sumList;
		
	}
	/**
	 * 通过数据字典拼接sql语句
	 * @param listColumn 需要查询的集合
	 * @param tableName 表名
	 * @param args 查询
	 * @return
	 */
	public String getSql(List<String> listColumn,String tableName,Object...args){
		StringBuffer select=new StringBuffer("select ");
		Object[] arrStr=listColumn.toArray();
		for(int index=0,len=arrStr.length;index<len;index++){
			if(0==index) select.append(" "+new StringBuffer(arrStr[index].toString()));
			select.append(", "+new StringBuffer(arrStr[index].toString()));
			
		}
		select.append(" FROM "+new StringBuffer(tableName)+" WHERE REPORT_ID IN "
				+ "( SELECT FD_OBJECTID  FROM REPORT_RECORDS  WHERE APPEAR_TIME  IN (SELECT MAX(APPEAR_TIME) FROM REPORT_RECORDS "
				+ " WHERE COMP_ID=? AND  REPORT_TYPE=? AND APPEAR_TIME<TO_DATE(?,'YYYY-MM-DD HH:MI:SS')))");
		System.out.println("查询语句__________________："+select.toString());	
		return select.toString();
	}
	/* AND APPEAR_TIME<TO_DATE(?,''YYYY-MM-DD HH:MI:SS'')*/
	/**
	 * 将list集合转换为map集合
	 * @param list
	 * @return
	 * @throws Exception
	 */
	public Map<String,List<String>> listMapToMapList(List<Map<String,String>> list,Object...columNames) throws Exception{
		Map<String,List<String>>  map=new HashMap<String, List<String>>();
		if(null==list||0==list.size())throw new Exception("listMapToMapList(List<Map<String,String>> list):参数为空或者长度为0;");
		for(String listName : list.get(0).keySet()){
			map.put(listName, new ArrayList());
			for(int i=0;i<columNames.length;i++){
				if(listName==columNames[i]){
					map.put(listName+"_SUM", new ArrayList());
				}
			}
		}
		for(Map<String,String> listItem : list){
			for(String listItemKey:listItem.keySet() ){
				map.get(listItemKey).add(listItem.get(listItemKey));
				for(int i=0;i<columNames.length;i++){
					if(listItemKey==columNames[i]){
						map.get(listItemKey+"_SUM").add(listItem.get(listItemKey)+"_SUM");
					}
				}
			}
		}
		return map;
	}
	
	
	/**
	 * 查询数据字典表中的单条属性
	 * @param columnName 数据字典的字段名 对应表的属性（表名，表的字段。表对应的中文名）
	 * @param args
	 * @return
	 * @throws Exception
	 */
	public Map<String,List<String>> queryLabel(String columnName,Object ...args) throws Exception{
		//数据表对应查询字段集合
				List<String> bb=new ArrayList();
				bb.add("REPORT_TABLE_NAME");
				bb.add("TABLE_COLUMN");
				bb.add("COLUMN_NAME");
				bb.add(columnName);
				//获取数据字典需要查询的字段
				List<Map<String,String>> label=this.getSelectLable(bb,args);
				
		return  listMapToMapList(label,"TABLE_COLUMN");
	}
	/**
	 * 通过公司Id查询报表记录
	 * @param compId
	 * @param Null
	 * @param agrs
	 * @return
	 */
	public List<Map<String,String>> findAllById(String compId,String Null,String dateString,Object...agrs){
		//数据表对应查询字段集合
		List<String> bb=new ArrayList();
		bb.add("REPORT_TABLE_NAME");
		bb.add("TABLE_COLUMN");
		bb.add("COLUMN_NAME");
		//获取数据字典需要查询的字段
		
		List<Map<String,String>> label=this.getSelectLable(bb,agrs);
		Map<String,String> compID=new HashMap<String, String>();
		compID.put("TABLE_COLUMN", "COMP_ID");
		compID.put("REPORT_TABLE_NAME", label.get(0).get("REPORT_TABLE_NAME"));
		label.add(compID);
		try {
			//将map集合转换为list集合，并添加字段
			Map<String,List<String>> map=listMapToMapList(label,"TABLE_COLUMN");
			//拼接统计字段
			List<String> ll=this.appendSql(label, "TABLE_COLUMN",Null);
			//获取sql语句
			if(!"DETAIL".equals(Null)){
				map.get("TABLE_COLUMN_SUM").remove("COMP_ID_SUM");
			}
			String sql=this.getSql(ll,map.get("REPORT_TABLE_NAME").get(0), agrs);
			System.out.println("aa"+map.get("TABLE_COLUMN_SUM"));
			return cmt.queryToMap(sql,map.get("TABLE_COLUMN_SUM") ,compId,agrs[0],dateString);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
		
	}
	
	
	public static void main(String[] args) throws Exception {
		HeightChartDaoImpl dao=new HeightChartDaoImpl();
		Logger	           log= Logger.getLogger(dao.getClass());
		try {
			/*List<Map<String,String>> li=dao.findAllById("11","DETAIL","8","0","0");
			List list=dao.findAllInId("getColumn", dao.listMapToMapList(li, "COMP_ID_SUM").get("COMP_ID_SUM"));
			System.out.println("bbbb"+list);
			System.out.println("CUOREN__________"+dao.listMapToMapList(li, "COMP_ID_SUM"));
			System.out.println("zuihoujieguo"+JsonPluginsUtil.beanListToJson(li));
			//System.out.println(dao.queryLabel("COLUMN_NAME", "8","0","0"));
			System.out.println("_____________"+li.size());*/
			/*List<String> list=new ArrayList();
			list.add("AMOUNT");

			List<Map<String,String>> li=dao.findAllBySqlName("NUM", list, "11");
			System.out.println(li);*/
			String date="2015-11-12 12:5:20";
			List<Map<String,String>> list=new ArrayList();
			List<String> xlist=new ArrayList();
			for(int index=0;index<Integer.valueOf(10);index++){
		     	Date temDate=DateUtil.toData(date);
		     	temDate.setSeconds(DateUtil.toData(date).getSeconds()-index*Integer.valueOf(30));
		     	xlist.add(DateUtil.toString(temDate));
		     	list.addAll(dao.findAllById("11","DETAIL",DateUtil.toString(temDate),"5" ,"0","0"));
			}
			
			System.out.println("________________+++++++"+dao.listMapToMapList(list, "COMP_ID_SUM").get("COMP_ID_SUM"));
			System.out.println(dao.listMapToMapList(list, "COMP_ID_SUM").get("COMP_ID_SUM").size());
			List<String> xAxisCategories=dao.findAllInId("getColumn", dao.listMapToMapList(list, "COMP_ID_SUM").get("COMP_ID_SUM"));
			System.out.println();
			System.out.println(list.size());
			System.out.println(JsonPluginsUtil.beanListToJson(list));
		} catch (Exception e) {
			e.printStackTrace();
		}				
	}
	
	
	
	

	
	

}