潘钊 / bsth_control · Files

package com.bsth.service.excep.impl;

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.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.beans.factory.annotation.Autowired;

import com.bsth.data.BasicData;
import com.bsth.entity.excep.Speeding;
import com.bsth.entity.realcontrol.ScheduleRealInfo;
import com.bsth.service.excep.SpeedingService;
import com.bsth.util.EchartConver;
import com.bsth.util.PageHelper;
import com.bsth.util.PageObject;
import com.bsth.util.db.DBUtils_MS;
import com.github.abel533.echarts.Option;
import com.google.gson.Gson;

@Service
public class SpeedingServiceImpl implements SpeedingService {

	@Autowired
	JdbcTemplate jdbcTemplate;
	static List<Speeding> findAll(Map<String, Object> map) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int page=Integer.parseInt(map.get("page").toString());
		List<Speeding> list=new ArrayList<Speeding>();
		String sql="select * from bsth_c_speeding where 1=1 ";
		Object line=map.get("line");
		Object nbbm=map.get("nbbm");
		Object updown=map.get("updown");
		Object date=map.get("date");

		if(line!=null){
			sql +=" and line like'%"+line.toString().trim()+"%'";
		}
		
		if(nbbm!=null){
			 nbbm=BasicData.deviceId2NbbmMap.inverse().get(nbbm);
			 if(nbbm!=null)
			sql +=" and vehicle like '%"+nbbm.toString()+"%'";
		}
		
		if(updown!=null){
			sql +="and up_down like '%"+updown.toString()+"%'";
		}
		if(date!=null){
			if (date.toString().length()>0) {
				SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				try {
					Long t1=sdf.parse(date.toString()+" 00:00:00").getTime();
					Long t2=sdf.parse(date.toString()+" 23:59:59").getTime();
					sql += " and timestamp >="+t1 +" and timestamp <="+t2;
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			
		}

		
		sql +=" order by id limit ?,?";
		

		try {
			conn = DBUtils_MS.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, page*10); // O-最大条数  -- M-起始条数
			ps.setInt(2, 10); // O-最小条数 -- M-显示条数
			rs = ps.executeQuery();
			list = resultSet2Set(rs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtils_MS.close(rs, ps, conn);
		}
		
		return list;
	}

	static List<Speeding> resultSet2Set(ResultSet rs) throws SQLException{
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		List<Speeding> list=new ArrayList<Speeding>();
		Speeding speeding;
		while(rs.next()){
			speeding=new Speeding();
			speeding.setId(Integer.valueOf(rs.getObject("id").toString()));
			speeding.setLat(Float.valueOf(rs.getObject("lat").toString()));
			speeding.setLine(Integer.valueOf(rs.getObject("line").toString()));
			//run 时注解
			speeding.setLineName(BasicData.lineCode2NameMap.get(speeding.getLine().toString()));
			speeding.setLon(Float.valueOf(rs.getObject("lon").toString()));
			speeding.setTimestamp((Long.valueOf(rs.getObject("timestamp").toString())));
			speeding.setTimestampDate(sdf.format(new Date(speeding.getTimestamp())));
			speeding.setUpDown(Integer.valueOf(rs.getObject("up_down").toString()));
			speeding.setVehicle(BasicData.deviceId2NbbmMap.get(rs.getObject("vehicle").toString()));
			list.add(speeding);
		}
		return list;
	}
	@Override
	public PageObject<Speeding> Pagequery(Map<String, Object> map) {
		// TODO Auto-generated method stub
		String sql="select count(*) record  from bsth_c_speeding where 1=1 ";
		Object line=map.get("line");
		Object nbbm=map.get("nbbm");
		Object updown=map.get("updown");
		
		Object date=map.get("date");

		if(line!=null){
			sql +=" and line like'%"+line.toString().trim()+"%'";
		}
		
		if(nbbm!=null){
			nbbm=BasicData.deviceId2NbbmMap.inverse().get(nbbm);
			if(nbbm!=null)
			sql +=" and vehicle like '%"+nbbm.toString()+"%'";
		}
		if(updown!=null){
			sql +="and up_down like '%"+updown.toString()+"%'";
		}
		if(date!=null){
			if (date.toString().length()>0) {
				SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:MM:SS");
				try {
					Long t1=sdf.parse(date.toString()+" 00:00:00").getTime();
					Long t2=sdf.parse(date.toString()+" 23:59:59").getTime();
					sql += " and timestamp >="+t1 +" and timestamp <="+t2;
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			
		}
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int totalData = 0;

		try {
			conn = DBUtils_MS.getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			 if(rs.next()){
				 totalData=rs.getInt("record");
			 }		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtils_MS.close(rs, ps, conn);
		}
		
		PageHelper pageHelper = new PageHelper(totalData, map);
		List<Speeding> list=findAll(pageHelper.getMap());
		for (int i = 0; i < list.size(); i++) {
			String nbbm2=list.get(i).getVehicle() ;
			Long d1=list.get(i).getTimestamp();
			Date datess = new Date(d1);
			String dates=new SimpleDateFormat("yyyy-MM-dd").format(datess);
			String sk=new SimpleDateFormat("HH:mm").format(datess);
			String sqlPbb="SELECT * FROM ( select lp_name,cl_zbh,j_gh,j_name,MIN(fcsj) as fcsj,MAX(zdsj) as ddsj"
					+ "  from bsth_c_s_sp_info_real where cl_zbh='"+nbbm2+"'  and"
					+ "   real_exec_date='"+dates+"' GROUP BY cl_zbh,lp_name,j_gh,j_name ) t WHERE t.fcsj<='"+sk+"' AND t.ddsj>='"+sk+"' " ;
			List<ScheduleRealInfo> pbb =jdbcTemplate.query(sqlPbb,  
			        new RowMapper<ScheduleRealInfo>(){  
			            @Override  
			            public ScheduleRealInfo  mapRow(ResultSet rs, int rowNum) throws SQLException {  
			            	ScheduleRealInfo s  = new ScheduleRealInfo(); 
			            	s.setjGh(rs.getString("j_gh"));
			            	s.setjName(rs.getString("j_name"));
			            	s.setLpName(rs.getString("lp_name"));
			                return s;  
			            }  
					});  
			if(pbb.size()>0){
				list.get(i).setJsy(pbb.get(0).getjGh()+"/"+pbb.get(0).getjName());
				list.get(i).setLpname(pbb.get(0).getLpName());
				
			}
			
		}
		PageObject<Speeding> pageObject = pageHelper.getPageObject();
		pageObject.setDataList(list);
		return pageObject;
	}

	@Override
	public Map<String, Object> getReport(Map<String, Object> map) {
		// TODO Auto-generated method stub
		String chartStr = "";
		Map<String, Object> mapValue = new HashMap<String, Object>();
		try {
			String xAxisName = map.get("xAxisName").toString();
			String legendName = map.get("legendName").toString();
			String xAxisItem = "";
			String xAxisOrderBy = "";
			String legendItem = "";
			String legendOrderBy = "";
			if(xAxisName!=null){
				String [] xaxis = xAxisName.split(":");
				if(xaxis.length>1 && xaxis[1].toLowerCase().contains("date")){					
					//本来还要判断下日期格式是否标准
					xAxisItem = "to_char("+xaxis[0]+",'"+xaxis[2]+"') ";
					xAxisOrderBy = (xaxis.length>3?xaxis[3]:"");
				}else{
					xAxisItem = xaxis[0];
				}
			}
			if(legendName!=null){
				String [] legend = legendName.split(":");
				if(legend.length>1 && legend[1].toLowerCase().contains("date")){					
					//本来还要判断下日期格式是否标准					
					legendItem = "to_char("+legend[0]+",'"+legend[2]+"') ";
					legendOrderBy = (legend[3].length()>3?legend[3]:"");
				}else{
					legendItem = legend[0];
				}
			}			
			map.put("xAxisItem", xAxisItem);
			map.put("xAxisOrderBy", xAxisOrderBy);
			map.put("legendItem", legendItem);
			map.put("legendOrderBy", legendOrderBy);
			List dataList = new ArrayList();
			dataList =getReportList(map);
			EchartConver echart = new EchartConver(map);
			Option option = echart.getOption(dataList);
			Gson gson = new Gson();
			chartStr = gson.toJsonTree(option).toString();
			mapValue.put("dataList", dataList);
			mapValue.put("option", chartStr);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return mapValue;
	}
	static List getReportList(Map<String, Object> map) {
		List dataList = new ArrayList();
		String times="";
		Object dataTime=map.get("dataTime");
		Object line=map.get("line");
		String addSql="";
		if(line !=null){
			if(line.toString()!=""){
				addSql =" and line =" +line;
			}
		}
		if(dataTime!=null){
			times=dataTime.toString();
		}
		if(times.equals("")){
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			times=sdf.format(new Date());
		}
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String times1=times+" 00:00:00";
		String times2=times+" 23:59:59";
		
		Connection conn = null;	
		PreparedStatement ps = null;
		ResultSet rs = null;
	try {
		Long t1=simpleDateFormat.parse(times1).getTime();
		Long t2=simpleDateFormat.parse(times2).getTime();
		String sql="SELECT up_down xAxis , line legend,count(*) series"+
				 " FROM  bsth_c_speeding "+
				"where timestamp>? and timestamp<? "+addSql+" group by xAxis ,legend";
		conn = DBUtils_MS.getConnection();
		ps = conn.prepareStatement(sql);
		ps.setLong(1, t1);
		ps.setLong(2, t2);
		rs = ps.executeQuery();
		while(rs.next()){
			Map<String, Object> newMap=new HashMap<String,Object>();
			if(rs.getObject("xAxis").toString().equals("0"))
				newMap.put("xAxis", "上行");
			else if(rs.getObject("xAxis").toString().equals("1"))
				newMap.put("xAxis", "下行");
			else
				newMap.put("xAxis", "无效");
			
			if(BasicData.lineCode2NameMap.get(rs.getObject("legend").toString())!=null && BasicData.lineCode2NameMap.get(rs.getObject("legend").toString())!="" )
				newMap.put("legend", BasicData.lineCode2NameMap.get(rs.getObject("legend").toString()));
			else
				newMap.put("legend", "编码"+rs.getObject("legend").toString());
			
			
			
			newMap.put("series", Integer.parseInt(rs.getObject("series").toString()));
			dataList.add(newMap);
 		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}catch (ParseException e1) {
		// TODO Auto-generated catch block
		e1.printStackTrace();
	}finally {
		DBUtils_MS.close(rs, ps, conn);
	}
	
		return dataList;
	}
	
	

}