wp/nwyj/.svn/pristine/e4/e47d315abe1265d45cd1c22eabda526c6b45ab98.svn-base

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

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

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.assitant.generator.IdentityGenerator;
import com.persistence.service.exception.PersistenceException;
import com.sinosoft.am.org.jdbcUtil.ORGTemplate;
import com.sinosoft.common.excel.JsonPluginsUtil;
import com.sysmodel.datamodel.xmlmodel.ModelFactory;

public class DetailDao {
	private Logger log	= Logger.getLogger(this.getClass());
	
	/**
	 * 根据grid表格的列名和表格类型获得表头名称
	 * @param name
	 * @param type
	 * @return
	 * @throws ClassNotFoundException 
	 */
	public String getTableHead(String name,String type) throws ClassNotFoundException{
		String sql = "select distinct table_head from  emc_am_report_dictionary where table_column = ? and report_type=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		DbConnection db = new DbConnection();
		//StringBuffer sb = new StringBuffer();
		//List<String> list = new ArrayList<String>();
		String tableHead ="";
		try {
			conn = db.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ps.setString(2, type);
			rs = ps.executeQuery();
			while(rs.next()){
				/*sb.append("{");
				sb.append("\"TABLE_HEAD\":\""+rs.getString("table_head")+"\"");
				sb.append("},");*/
				tableHead = rs.getString("table_head");
			}
/*			if(sb.lastIndexOf(",")>-1){
				sb.deleteCharAt(sb.lastIndexOf(","));
			}
			list.add(sb.toString());
*/			
		}  catch (SQLException e) {
			log.info(e.getMessage(),e);
		}finally{
			db.close(rs);
			db.close(ps);
			db.close(conn);
			
		}
		return tableHead;
	}
	
	/**
	 * 返回表头和表格所需填充的数据
	 * @param name
	 * @param type
	 * @param level
	 * @param fd_id
	 * @param page
	 * @param pageSize
	 * @return
	 * @throws ClassNotFoundException
	 */
	public String getInfo(String type,String level,String fd_id ,int page,int pageSize,String deatil) throws ClassNotFoundException{
		String result = "";
		String sqls = getSql(type,level,fd_id,page,pageSize);
		String sql1 ="";
		if("1".equals(deatil)){
			 sql1 = sqls.split("[|]")[1];
		}else{
			 sql1 = sqls.split("[|]")[0];
		}
		log.info(sql1+"==============sql1");
		List<Map<String,String>> list = null;
		try {
			if(!"".equals(sql1)){
				list = PersistenceFactory.getInstance(ModelFactory.getSysmodel()).getSearchResultToMap(
						99, sql1);
				
				
				/*list1 = PersistenceFactory.getInstance(ModelFactory.getSysmodel()).getSearchResultToMap(
						99, sql1);*/
				
			
				result = JSONArray.fromObject(list).toString();
				
				result = "{\"rows\":" + result + "}";
				
				//tableHead = getTableHead(level,type);
				//vo = new DetailVo();
			//	vo.setTableHead(tableHead);
			//	vo.setResult(result);
				//li = new ArrayList<DetailVo>();
				//li.add(vo);
			}
			
		
		} catch (PersistenceException e) {
			log.error(e.getMessage(),e);
		}
		if(!"".equals(result)&&result!=null){
			return result;
		}else{
			return null;
		}
	}
	
	public String getSql(String type,String level,String fd_id,int page,int pageSize){
		
		String sql = "";
		String sql1 = "";
		
		if("1".equals(type)){
			sql = "select top "+(page-1)*pageSize+","+pageSize +" fd_objectid,report_id,route_name,route_level,comp_id,"
					+ "comp_name,end_time,gz_type,start_time,loss_load,count,ofline_stat_qk from route_ofline_detail where report_id='"+fd_id+"' and types = '"+level+"'";
			sql1 = "select  fd_objectid,report_id,route_name,route_level,comp_id,"
					+ "comp_name,end_time,gz_type,start_time,loss_load,count,ofline_stat_qk from route_ofline_detail where report_id='"+fd_id+"' and types = '"+level+"'";
		}/*else if("2".equals(type)){
			
			sql = "select top "+(page-1)*pageSize+","+pageSize+" fd_objectid,report_id,station_name,route_level,comp_name,stop_time,fail_count,start_time,comp_id from bdz_xq_detail where report_id='"+fd_id+"' and types ='"+level+"'";
			sql1 =  "select  fd_objectid,report_id,station_name,route_level,comp_name,stop_time,fail_count,start_time,comp_id from bdz_xq_detail  where report_id='"+fd_id+"' and types ='"+level+"'";
		
		}*/else if("3".equals(type)){
			sql = "select top "+(page-1)*pageSize+","+pageSize+" fd_objectid,report_id ,comp_id ,sb_name,fail_type,end_time,start_time,fail_count,comp_name  from xldg_xq_detail where report_id ='"+fd_id+"' and types ='"+level+"'";
			sql1 ="select   fd_objectid,report_id ,comp_id ,sb_name,fail_type,end_time,start_time,fail_count,comp_name  from xldg_xq_detail  where report_id ='"+fd_id+"' and types ='"+level+"'";
		
		}else if("5".equals(type)){
			sql = "select top "+(page-1)*pageSize+","+pageSize+" fd_objectid,report_id,comp_id,client_number,client_name,client_level,support_station,stop_time,is_had,route_level from tdqk_xq_detail where report_id ='"+fd_id+"' and types ='"+level+"'";
			sql1 = "select   fd_objectid,report_id,comp_id,client_number,client_name,client_level,support_station,stop_time,is_had,route_level from tdqk_xq_detail where report_id ='"+fd_id+"' and types ='"+level+"'";
		}else if("8".equals(type)){
			sql = "select top "+(page-1)*pageSize+","+pageSize+" fd_objectid,report_id,comp_id,comp_name,sd_power_cable,sd_circuit,bd_first,bd_second,pd_circuit,pd_power_cable,dy_station,yj_bd,communication,inter_info,test,total from yjzy_xq_detail where report_id ='"+fd_id+"' and types='"+level+"'";
			sql1 =  "select   fd_objectid,report_id,comp_id,comp_name,sd_power_cable,sd_circuit,bd_first,bd_second,pd_circuit,pd_power_cable,dy_station,yj_bd,communication,inter_info,test,total from yjzy_xq_detail where report_id ='"+fd_id+"' and types='"+level+"'";
		}else if("4".equals(type)){
			sql = "select top "+(page-1)*pageSize+","+pageSize+" fd_objectid,report_id,comp_id,name,voltage,transformer_name,device_type,damage_type,length,count,is_repair from shousun_detail where report_id = '"+fd_id+"' and types = '"+level+"'";
			sql1 = "select  fd_objectid,report_id,comp_id,name,voltage,transformer_name,device_type,damage_type,length,count,is_repair from shousun_detail where report_id = '"+fd_id+"' and types = '"+level+"'";
		}else if("101".equals(type)){
			sql = "select top "+(page-1)*pageSize+","+pageSize+" bdzmc,dydj,sfyhf,tysj,hfsj from SUBSTATION_STOPPAGE_RECOVERY where report_id = '"+fd_id+"' and dydj = (select distinct table_head from emc_am_report_dictionary where report_type = '"+type+"' and table_column = '"+level+"')";
			sql1 = "select bdzmc,dydj,sfyhf,tysj,hfsj from SUBSTATION_STOPPAGE_RECOVERY where report_id = '"+fd_id+"' and dydj = (select distinct table_head from emc_am_report_dictionary where report_type = '"+type+"' and table_column = '"+level+"')";
		}
		return sql+"|"+sql1;
	}
	
	
	public Msg operateDetail(String json,String report_id, String route_level, String comp_id,String type) throws ClassNotFoundException{
		
		BaoBiaoDao dao = new BaoBiaoDao();
		String date = dao.formatTime(new Date());
		
		Connection conn = null;
		PreparedStatement ps = null;
		PreparedStatement ps1 = null;
		DbConnection db = new DbConnection();
		String sqls = getOperateSql(type);
		String sql = sqls.split("[|]")[0];
		String sql1 = sqls.split("[|]")[1];
		List<Map<String, String>> list = JsonPluginsUtil.jsonToMapList(json);
		Msg m = null;
		try {
			if(!"".equals(sql)&&!"".equals(sql1)){
				conn = db.getConnection();
				conn.setAutoCommit(false);
				ps1 = conn.prepareStatement(sql1);
				log.info(report_id+"=========report_id");
				log.info(route_level+"=========route_level");
				ps1.setString(1, report_id);
				ps1.setString(2, route_level);
				ps1.executeUpdate();
				int num = 0;
				m = new Msg();
				ps = conn.prepareStatement(sql);
				log.info(list.size()+"==================");
			for(int i=0;i<list.size();i++){
				Map<String, String> map = list.get(i);
				if("1".equals(type)){
					ps.setString(1, IdentityGenerator.getIdentityGenerator().gerenalIdentity(338));	
					ps.setString(2, report_id);
					ps.setString(3, map.get("ROUTE_NAME"));
					ps.setString(4, map.get("ROUTE_LEVEL"));
					ps.setString(5, comp_id);
				/*	ps.setString(6, map.get("OFLINE_STAT"));*/
					ps.setString(6, date);
					ps.setString(7, "0");
					ps.setString(8, map.get("COMP_NAME"));
					ps.setString(9, map.get("END_TIME"));
					ps.setString(10, map.get("GZ_TYPE"));
					ps.setString(11, map.get("START_TIME"));
					ps.setString(12, map.get("LOSS_LOAD"));
					ps.setString(13, map.get("COUNT"));
					ps.setString(14, map.get("OFLINE_STAT_QK"));
					ps.setString(15, route_level);
					ps.executeUpdate();
				}/*else if("2".equals(type)){
					ps.setString(1, IdentityGenerator.getIdentityGenerator().gerenalIdentity(338));
					ps.setString(2, report_id);
					ps.setString(3, map.get("STATION_NAME"));
					ps.setString(4, map.get("ROUTE_LEVEL"));
					ps.setString(5, map.get("COMP_NAME"));
					ps.setString(6, map.get("STOP_TIME"));
					ps.setString(7, map.get("FAIL_COUNT"));
					ps.setString(8, map.get("START_TIME"));
					ps.setString(9, "0");
					ps.setString(10, date);
					ps.setString(11, comp_id);
					ps.setString(12, route_level);
					ps.executeUpdate();
				}*/else if("3".equals(type)){
					ps.setString(1, IdentityGenerator.getIdentityGenerator().gerenalIdentity(338));
					ps.setString(2, report_id);
					ps.setString(3, route_level);
					ps.setString(4, comp_id);
					ps.setString(5, map.get("SB_NAME"));
					ps.setString(6, map.get("FAIL_TYPE"));
					ps.setString(7, map.get("END_TIME"));
					ps.setString(8, map.get("START_TIME"));
					ps.setString(9, map.get("FAIL_COUNT"));
					ps.setString(10, "0");
					ps.setString(11, date);
					ps.setString(12, map.get("COMP_NAME"));
					ps.executeUpdate();
				}else if("5".equals(type)){
					ps.setString(1, IdentityGenerator.getIdentityGenerator().gerenalIdentity(338));
					ps.setString(2, report_id);
					ps.setString(3, comp_id);
					ps.setString(4, route_level);
					ps.setString(5, map.get("CLIENT_NUMBER"));
					ps.setString(6, map.get("CLIENT_NAME"));
					ps.setString(7, map.get("CLIENT_LEVEL"));
					ps.setString(8, map.get("SUPPORT_STATION"));
					ps.setString(9, map.get("STOP_TIME"));
					ps.setString(10,map.get("IS_HAD"));
					ps.setString(11,map.get("0"));
					ps.setString(12,date);
					ps.setString(13, map.get("ROUTE_LEVEL"));
					ps.executeUpdate();
				}else if("8".equals(type)){
					ps.setString(1, IdentityGenerator.getIdentityGenerator().gerenalIdentity(338));
					ps.setString(2, report_id);
					ps.setString(3, comp_id);
					ps.setString(4, route_level);
				//	ps.setString(5, map.get("COMP_NAME")==""?"0":);
					log.info(map.get("SD_CIRCUIT").equals("")+"======null");
					ps.setString(5, "".equals(map.get("SD_POWER_CABLE"))?"0":map.get("SD_POWER_CABLE"));
					ps.setString(6, "".equals(map.get("SD_CIRCUIT"))?"0":map.get("SD_CIRCUIT"));
					ps.setString(7, "".equals(map.get("BD_FIRST"))?"0":map.get("BD_FIRST"));
					ps.setString(8, "".equals(map.get("BD_SECOND"))?"0":map.get("BD_SECOND"));
					ps.setString(9,"".equals(map.get("PD_CIRCUIT"))?"0":map.get("PD_CIRCUIT"));
					ps.setString(10,"".equals(map.get("PD_POWER_CABLE"))?"0":map.get("PD_POWER_CABLE"));
					ps.setString(11,"".equals(map.get("DY_STATION"))?"0":map.get("DY_STATION"));
					ps.setString(12,"".equals(map.get("YJ_BD")) ?"0":map.get("YJ_BD"));
					ps.setString(13, "".equals(map.get("COMMUNICATION"))?"0":map.get("COMMUNICATION"));
					ps.setString(14, "".equals(map.get("INTER_INFO"))?"0":map.get("INTER_INFO"));
					ps.setString(15, "".equals(map.get("TEST"))?"0":map.get("TEST"));
					ps.setString(16,map.get("TOTAL"));
					ps.setString(17, "0");
					ps.setString(18, date);
					ps.executeUpdate();
				}else if("4".equals(type)){
					ps.setString(1, IdentityGenerator.getIdentityGenerator().gerenalIdentity(338));
					ps.setString(2, report_id);
					ps.setString(3, comp_id);
					ps.setString(4, route_level);
					ps.setString(5, date);
					ps.setString(6, "0");
					ps.setString(7, map.get("NAME"));
					ps.setString(8, map.get("VOLTAGE"));
					ps.setString(9, map.get("TRANSFORMER_NAME"));
					ps.setString(10,map.get("DEVICE_TYPE"));
					ps.setString(11,map.get("DAMAGE_TYPE"));
					ps.setString(12,map.get("LENGTH"));
					ps.setString(13,map.get("COUNT"));
					ps.setString(14, map.get("IS_REPAIR"));
					ps.executeUpdate();
				}
				num++;
				
			}
			
			if(num==list.size()){
				conn.commit();
				m.setSucsess(new Boolean(true).toString());
				m.setInfo(Parameter.CREATE_SUCSESS);
			}else{
				conn.rollback();
				m.setSucsess(new Boolean(false).toString());
				m.setInfo(Parameter.CREATE_FAILURE);
			}
		}
			
		}  catch (SQLException e) {
			log.error(e.getMessage(),e);
		}finally{
			
			db.close(ps1);
			db.close(ps);
			db.close(conn);
		}
	
		if(m!=null){
			return m;
		}else{
			return null;
		}
	}
	
	
	
	
	
	
	public String getOperateSql(String type){
		String sql = "";
		String sql1 = "";
		
		if("1".equals(type)){
			sql = "insert into route_ofline_detail(fd_objectid,report_id,route_name,route_level,comp_id,updatedate,is_del,"
					+ "comp_name,end_time,gz_type,start_time,loss_load,count,ofline_stat_qk,types) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			sql1 = "delete from route_ofline_detail where report_id = ? and types = ?";
		
		}/*else if("2".equals(type)){
			
			sql = "insert into bdz_xq_detail(fd_objectid,report_id,station_name,route_level,comp_name,stop_time,fail_count,start_time,is_del,updatedate,comp_id,types) values(?,?,?,?,?,?,?,?,?,?,?,?)";
			sql1 = "delete from bdz_xq_detail where report_id = ? and types = ?";
	
		}*/else if("3".equals(type)){
			sql = "insert into xldg_xq_detail(fd_objectid,report_id,types,comp_id,sb_name,fail_type,end_time,start_time,fail_count,is_del,updatedate,comp_name) values(?,?,?,?,?,?,?,?,?,?,?,?) ";
			sql1 = "delete from xldg_xq_detail where report_id = ? and types = ?";
		
		}else if("5".equals(type)){
			sql = "insert into tdqk_xq_detail(fd_objectid,report_id,comp_id,types,client_number,client_name,client_level,support_station,stop_time,is_had,is_del,updatedate,route_level) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
			sql1 = "delete from tdqk_xq_detail where report_id = ? and types = ?";
		
		}else if("8".equals(type)){
			sql = "insert into yjzy_xq_detail(fd_objectid,report_id,comp_id,types,sd_power_cable,sd_circuit,bd_first,bd_second,pd_circuit,pd_power_cable,dy_station,yj_bd,communication,inter_info,test,total,is_del,updatedate)"
					+ " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			sql1 = "delete from yjzy_xq_detail where report_id = ? and types = ? "; 
		}else if("4".equals(type)){

			sql ="insert into shousun_detail(fd_objectid,report_id,comp_id,types,updatedate,is_del,name,voltage,transformer_name,device_type,damage_type,length,count,is_repair) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			
			sql1 ="delete from shousun_detail where report_id = ? and types = ?";
		}
		return sql+"|"+sql1;
	}
	
	
	public String getClassIdByType(String report_type){
		String sql = "select classid from detail_dictionary where report_type = ?";
		String result = "";
		try {
			List<String[]> list= new ORGTemplate().getSearchResult(sql, report_type);
			result = list.get(0)[0];
		} catch (SQLException e) {
			log.info(e.getMessage(),e);
		}
		return result;
	}
	
	
	
}