潘钊 / 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.Calendar;
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.stereotype.Service;
import org.springframework.beans.factory.annotation.Autowired;

import com.bsth.data.BasicData;
import com.bsth.entity.excep.Outbound;
import com.bsth.entity.excep.Speeding;
import com.bsth.service.excep.SpeedingService;
import com.bsth.service.gps.entity.GpsSpeed;
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_speedingmh where 1=1 ";
		Object line=map.get("line");
		Object updown=map.get("updown");
		Object startDate=map.get("startDate");
		Object endDate=map.get("endDate");
		Object times=map.get("times");
		
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		if(line!=null){
			sql +=" and line like'%"+line.toString().trim()+"%'";
		}
		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 startTimestamp >="+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 endTimestamp <="+t2;
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			
		}
		
		if(times!=null && times.toString().length()>0){
			sql +=" and (endTimestamp-startTimestamp) >="+Integer.valueOf(times.toString())*1000;
		}

		sql +=" order by startTimestamp 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;
		Location location;
		while(rs.next()){
			speeding=new Speeding();
			speeding.setId(rs.getInt("id"));
			speeding.setVehicle(BasicData.deviceId2NbbmMap.get(rs.getObject("vehicle").toString()));
			speeding.setLine(rs.getInt("line"));
			//run 时注解
			speeding.setLineName(BasicData.lineCode2NameMap.get(speeding.getLine().toString()));
			speeding.setLineId(BasicData.lineId2CodeMap.inverse().get(speeding.getLine().toString()));
			speeding.setUpDown(rs.getInt("up_down"));
			//将gps的经纬度转成百度的经纬度
            location = TransGPS.bd_encrypt(TransGPS.transformFromWGSToGCJ(TransGPS.LocationMake(rs.getFloat("startLon"), rs.getFloat("startLat"))));
            speeding.setLon((float)location.getLng());
            speeding.setLat((float)location.getLat());
            location = TransGPS.bd_encrypt(TransGPS.transformFromWGSToGCJ(TransGPS.LocationMake(rs.getFloat("endLon"), rs.getFloat("endLat"))));
            speeding.setEndlon((float)location.getLng());
            speeding.setEndlat((float)location.getLat());
			speeding.setTimestamp((Long.valueOf(rs.getObject("startTimestamp").toString())));
			speeding.setTimestampDate(sdf.format(new Date(speeding.getTimestamp())));
			speeding.setEndtimestamp((Long.valueOf(rs.getObject("endTimestamp").toString())));
			speeding.setEndtimestampDate(sdf.format(new Date(speeding.getEndtimestamp())));
			list.add(speeding);
		}
		return list;
	}

	@Override
	public PageObject<Speeding> Pagequery(Map<String, Object> map) {
		String sql="select count(*) record from bsth_c_speedingmh where 1=1 ";
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Object line=map.get("line");
		Object updown=map.get("updown");
		Object startDate=map.get("startDate");
		Object endDate=map.get("endDate");
		Object times=map.get("times");
		
		if(line!=null){
			sql +=" and line like'%"+line.toString().trim()+"%'";
		}
		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 startTimestamp >="+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 endTimestamp <="+t2;
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
		}
		
		if(times!=null && times.toString().length()>0){
			sql +=" and (endTimestamp-startTimestamp) >="+Integer.valueOf(times.toString())*1000;
		}
		
		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);
		}
		PageHelper pageHelper = new PageHelper(totalData, map);
		List<Speeding> list=findAll(pageHelper.getMap());
		PageObject<Speeding> pageObject = pageHelper.getPageObject();
		pageObject.setDataList(list);
		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;
	}
	
	
    @Override
    public List<Speeding> findPosition(String deviceid, String startdate,
                                       String enddate){
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String sql = "select id,vehicle,line,up_down,lon,lat,speed,timestamp from bsth_c_speeding where vehicle = ? and timestamp >= ? and timestamp <= ? order by timestamp ";
        Connection conn = null;	                                        
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Speeding> listResult = new ArrayList<Speeding>();
        Speeding speed = null;
        try {
            conn = DBUtils_MS.getConnection();
            ps = conn.prepareStatement(sql);
            long startTime = sdf.parse(startdate).getTime();
            long endTime = sdf.parse(enddate).getTime();
            ps.setString(1, deviceid);
            ps.setLong(2,startTime);
            ps.setLong(3,endTime);
            rs = ps.executeQuery();
            Location location;
            while (rs.next()) {
            	speed = new Speeding();
            	speed.setVehicle(BasicData.deviceId2NbbmMap.get(rs.getObject("vehicle").toString()));
               // speed.setVehicle(rs.getString("vehicle"));
                location = TransGPS.LocationMake(rs.getFloat("lon"), rs.getFloat("lat"));
                location = TransGPS.bd_encrypt(TransGPS.transformFromWGSToGCJ(location));
                speed.setLon((float)location.getLng());
                speed.setLat((float)location.getLat());
                speed.setSpeed(rs.getFloat("speed"));
                speed.setTimestamp(rs.getLong("timestamp"));
                // 上下行
                listResult.add(speed);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils_MS.close(rs, ps, conn);
        }
        return listResult;

    }

	@Override
	public PageObject<Speeding> mhpagequery(Map<String, Object> map) {
		String sql="select count(*) record from bsth_c_speedingmh where 1=1 ";
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Object line=map.get("line");
		Object updown=map.get("updown");
		Object startDate=map.get("startDate");
		Object endDate=map.get("endDate");
		Object times=map.get("times");
		
		if(line!=null){
			sql +=" and line like'%"+line.toString().trim()+"%'";
		}
		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 startTimestamp >="+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 endTimestamp <="+t2;
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
		}
		
		if(times!=null && times.toString().length()>0){
			sql +=" and (endTimestamp-startTimestamp) >="+Integer.valueOf(times.toString())*1000;
		}
		
		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);
		}
		PageHelper pageHelper = new PageHelper(totalData, map);
		List<Speeding> list=findList(pageHelper.getMap());
		PageObject<Speeding> pageObject = pageHelper.getPageObject();
		pageObject.setDataList(list);
		return pageObject;
	}

	static List<Speeding> findList(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_speedingmh where 1=1 ";
		Object line=map.get("line");
		Object updown=map.get("updown");
		Object startDate=map.get("startDate");
		Object endDate=map.get("endDate");
		Object times=map.get("times");
		
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		if(line!=null){
			sql +=" and line like'%"+line.toString().trim()+"%'";
		}
		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 startTimestamp >="+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 endTimestamp <="+t2;
				} catch (ParseException e) {
					e.printStackTrace();
				}
			}
			
		}
		
		if(times!=null && times.toString().length()>0){
			sql +=" and (endTimestamp-startTimestamp) >="+Integer.valueOf(times.toString())*1000;
		}

		sql +=" order by startTimestamp 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 = resultToList(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtils_MS.close(rs, ps, conn);
		}
		return list;
	}
	
	static List<Speeding> resultToList(ResultSet rs) throws SQLException{
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List<Speeding> list=new ArrayList<Speeding>();
		Speeding speeding;
		Location location;
		while(rs.next()){
			speeding=new Speeding();
			speeding.setId(rs.getInt("id"));
			speeding.setVehicle(BasicData.deviceId2NbbmMap.get(rs.getObject("vehicle").toString()));
			speeding.setLine(rs.getInt("line"));
			//run 时注解
			speeding.setLineName(BasicData.lineCode2NameMap.get(speeding.getLine().toString()));
			speeding.setLineId(BasicData.lineId2CodeMap.inverse().get(speeding.getLine().toString()));
			speeding.setUpDown(rs.getInt("up_down"));
			//将gps的经纬度转成百度的经纬度
            location = TransGPS.bd_encrypt(TransGPS.transformFromWGSToGCJ(TransGPS.LocationMake(rs.getFloat("startLon"), rs.getFloat("startLat"))));
            speeding.setLon((float)location.getLng());
            speeding.setLat((float)location.getLat());
            location = TransGPS.bd_encrypt(TransGPS.transformFromWGSToGCJ(TransGPS.LocationMake(rs.getFloat("endLon"), rs.getFloat("endLat"))));
            speeding.setEndlon((float)location.getLng());
            speeding.setEndlat((float)location.getLat());
			speeding.setTimestamp((Long.valueOf(rs.getObject("startTimestamp").toString())));
			speeding.setTimestampDate(sdf.format(new Date(speeding.getTimestamp())));
			speeding.setEndtimestamp((Long.valueOf(rs.getObject("endTimestamp").toString())));
			speeding.setEndtimestampDate(sdf.format(new Date(speeding.getEndtimestamp())));
			list.add(speeding);
		}
		return list;
	}
	
}