潘钊 / 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.Iterator;
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.TransGPS;
import com.bsth.util.TransGPS.Location;
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 speed > 60 ";
		Object line=map.get("line");
		Object nbbm=map.get("nbbm");
		Object updown=map.get("updown");
		Object startDate=map.get("startDate");
		Object endDate=map.get("endDate");
		
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		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(startDate!=null){
			if (startDate.toString().length()>0) {
				try {
					Long t1=sdf.parse(startDate.toString()+" 00:00:00").getTime();
					sql += " and timestamp >="+t1;
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			
		}
		if(endDate!=null){
			if (endDate.toString().length()>0) {
				try {
					Long t2=sdf.parse(endDate.toString()+" 23:59:59").getTime();
					sql += " and timestamp <="+t2;
				} catch (ParseException e) {
					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) {
			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;
		Float lon, lat;
		Location location;
		while(rs.next()){
			lon = rs.getFloat("lon");
			lat = rs.getFloat("lat");
            location = TransGPS.LocationMake(lon, lat);
            location = TransGPS.bd_encrypt(TransGPS.transformFromWGSToGCJ(location));
			speeding=new Speeding();
			speeding.setId(Integer.valueOf(rs.getObject("id").toString()));
			speeding.setLon((float)location.getLng());
			speeding.setLat((float)location.getLat());
			speeding.setLine(Integer.valueOf(rs.getObject("line").toString()));
			//run 时注解
			speeding.setLineName(BasicData.lineCode2NameMap.get(speeding.getLine().toString()));
			speeding.setSpeed(Float.valueOf(rs.getObject("speed").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) {
/*		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 startDate=map.get("startDate");
		Object endDate=map.get("endDate");
		
		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(startDate!=null){
			if (startDate.toString().length()>0) {
				try {
					Long t1=sdf.parse(startDate.toString()+" 00:00:00").getTime();
					sql += " and timestamp >="+t1;
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			
		}
		if(endDate!=null){
			if (endDate.toString().length()>0) {
				try {
					Long t2=sdf.parse(endDate.toString()+" 23:59:59").getTime();
					sql += " and timestamp <="+t2;
				} catch (ParseException e) {
					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) {
			e.printStackTrace();
		}finally {
			DBUtils_MS.close(rs, ps, conn);
		}*/
		
		//时间转换
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List<Speeding> list=findAll(map);
		List<Speeding> listResult = new ArrayList<Speeding>();
		int curPage;//页码  
		int pageData;//每页的记录条数
		int start;//起始数据下标
		int totalPage;//总页数
		int totalData = 0;
		if(list.size()>1){
			Speeding speedingNow;//下标为i的车
			Speeding speedingLast;//下标为i-1的车
			String strNow;
			String strLast;
			for(int i = 1;i<list.size();i++){
				speedingNow = list.get(i);
				speedingLast = list.get(i-1);
				strNow = speedingNow.getVehicle()+speedingNow.getLine()+speedingNow.getUpDown();//同一车辆同一线路同一方向
				strLast = speedingLast.getVehicle()+speedingLast.getLine()+speedingLast.getUpDown();
				//同一车辆同一线路同一方向并且该记录的超速的开始时间减去上一条超速记录的结束时间小于等于10s，证明该车超速。//PS:祛除数据库中的重复发送数据
				if(strNow.equals(strLast) && Math.abs(speedingNow.getTimestamp()-speedingLast.getTimestamp())<=10*1000 && (speedingNow.getTimestamp()-speedingLast.getTimestamp()!=0)){
					speedingLast.setEndtimestamp(speedingNow.getTimestamp());//设置结束时间时间戳
					speedingLast.setEndtimestampDate(sdf.format(new Date(speedingNow.getTimestamp())));//设置结束时间
					speedingLast.setEndlon(speedingNow.getLon());//设置结束时的经度
					speedingLast.setEndlat(speedingNow.getLat());//设置结束时的纬度
					listResult.add(speedingLast);
				}
			}
			Iterator<Speeding> speedIt = listResult.iterator(); 
			while(speedIt.hasNext()){
				Speeding speeding = speedIt.next(); 
			    if(speeding.getEndtimestamp()-speeding.getTimestamp()<=1000){  
			    	speedIt.remove();  
			    }  
			}
			
			totalData = listResult.size();//总记录条数。
			if(map.get("curPage") == null || map.get("curPage").equals("0")){
				curPage = 0;
			}else{
				curPage = Integer.parseInt((String) map.get("curPage"));
			}
			pageData = Integer.parseInt((String) map.get("pageData"));//每页的记录条数
			start = (curPage - 0) * pageData; //起始记录下标。
			totalPage = totalData % pageData == 0 ? totalData / pageData : totalData / pageData +1;//总页数
			if(curPage == totalPage-1){//如果当前页等于总页数。
				listResult = listResult.subList(start, totalData);
			}else{
				listResult = listResult.subList(start, start+pageData);
			}
		}else{
			curPage = 1;
			pageData = Integer.parseInt((String) map.get("pageData"));//每页的记录条数
			totalPage =1;
		}
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("totalPage", totalPage);
		paramMap.put("curPage", curPage);
		paramMap.put("pageData", pageData);
		PageHelper pageHelper = new PageHelper(totalData, paramMap);
		pageHelper.getMap();
		PageObject<Speeding> pageObject = pageHelper.getPageObject();
		pageObject.setDataList(listResult);
		return pageObject;
	}
	
	
	@Override
	public Map<String, Object> getReport(Map<String, Object> map) {
		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) {
		e.printStackTrace();
	}catch (ParseException e1) {
		e1.printStackTrace();
	}finally {
		DBUtils_MS.close(rs, ps, conn);
	}
	
		return dataList;
	}
	
	

}