潘钊 / bsth_control · Files

package com.bsth.service.gps;

import com.bsth.common.ResponseCode;
import com.bsth.data.BasicData;
import com.bsth.data.forecast.entity.ArrivalEntity;
import com.bsth.data.gpsdata_v2.GpsRealData;
import com.bsth.data.gpsdata_v2.cache.GeoCacheData;
import com.bsth.data.gpsdata_v2.cache.GpsCacheData;
import com.bsth.data.gpsdata_v2.entity.GpsEntity;
import com.bsth.data.gpsdata_v2.utils.GeoUtils;
import com.bsth.data.pilot80.PilotReport;
import com.bsth.data.safe_driv.SafeDriv;
import com.bsth.data.safe_driv.SafeDrivCenter;
import com.bsth.data.schedule.DayOfSchedule;
import com.bsth.entity.Line;
import com.bsth.entity.LineVersions;
import com.bsth.entity.directive.D80;
import com.bsth.entity.realcontrol.ScheduleRealInfo;
import com.bsth.repository.CarParkRepository;
import com.bsth.repository.LineRepository;
import com.bsth.repository.LineVersionsRepository;
import com.bsth.repository.StationRepository;
import com.bsth.repository.realcontrol.ScheduleRealInfoRepository;
import com.bsth.service.gps.entity.*;
import com.bsth.util.TransGPS;
import com.bsth.util.TransGPS.Location;
import com.bsth.util.db.DBUtils_MS;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class GpsServiceImpl implements GpsService {
    /**
     * 历史gps查询最大范围 24小时
     */
    final static Long GPS_RANGE = 60 * 60 * 24L;

    /**
     * jdbc
     */
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    Logger logger = LoggerFactory.getLogger(this.getClass());

    @Autowired
    GpsRealData gpsRealData;

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    DayOfSchedule dayOfSchedule;

    @Autowired
    ScheduleRealInfoRepository scheduleRealInfoRepository;


    @Autowired
    LineVersionsRepository lineVersionsRepository;

    @Autowired
    LineRepository lineRepository;

    // 历史gps查询
    @Override
    public List<Map<String, Object>> history(String device, Long startTime, Long endTime, int directions) {
        Calendar sCal = Calendar.getInstance();
        sCal.setTime(new Date(startTime));

        Calendar eCal = Calendar.getInstance();
        eCal.setTime(new Date(endTime));

        int dayOfYear = sCal.get(Calendar.DAY_OF_YEAR);
        /*
         * if(dayOfYear != eCal.get(Calendar.DAY_OF_YEAR)){
         * System.out.println("暂时不支持跨天查询..."); return null; }
         */

        String sql = "select DEVICE_ID,LON,LAT,TS,INOUT_STOP,SERVICE_STATE ,STOP_NO from bsth_c_gps_info where days_year=? and device_id=? and ts > ? and ts < ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String, Object>> list = new ArrayList<>();
        Map<String, Object> map = null;
        try {
            conn = DBUtils_MS.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, dayOfYear);
            ps.setString(2, device);
            ps.setLong(3, startTime);
            ps.setLong(4, endTime);

            rs = ps.executeQuery();
            Float lon, lat;
            Location location;
            int upDown;
            while (rs.next()) {
                upDown = getUpOrDown(rs.getLong("SERVICE_STATE"));
                if (upDown != directions)
                    continue;

                // to 百度坐标
                lon = rs.getFloat("LON");
                lat = rs.getFloat("LAT");
                location = TransGPS.LocationMake(lon, lat);
                location = TransGPS.bd_encrypt(TransGPS.transformFromWGSToGCJ(location));

                map = new HashMap<>();
                map.put("device", rs.getString("DEVICE_ID"));
                map.put("lon", location.getLng());
                map.put("lat", location.getLat());
                map.put("ts", rs.getLong("TS"));
                map.put("stopNo", rs.getString("STOP_NO"));
                map.put("inout_stop", rs.getInt("INOUT_STOP"));
                // 上下行
                map.put("upDown", upDown);
                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils_MS.close(rs, ps, conn);
        }
        return list;
    }

    /**
     * 王通 2016/6/29 9:23:24 获取车辆线路上下行
     *
     * @return -1无效 0上行 1下行
     */
    public static byte getUpOrDown(long serviceState) {
        /*if ((serviceState & 0x00020000) == 0x00020000 || (serviceState & 0x80000000) == 0x80000000
                || (serviceState & 0x01000000) == 0x01000000 || (serviceState & 0x08000000) == 0x08000000)
            return -1;*/
        return (byte) (((serviceState & 0x10000000) == 0x10000000) ? 1 : 0);
    }

    /**
     * 获取运营状态
     *
     * @return -1无效 0运营 1未运营
     */
    public static byte getService(long serviceState) {
        /*if ((serviceState & 0x00020000) == 0x00020000 || (serviceState & 0x80000000) == 0x80000000)
            return -1;*/
        return (byte) (((serviceState & 0x02000000) == 0x02000000) ? 1 : 0);
    }

    private static DateTimeFormatter fmtyyyy = DateTimeFormat.forPattern("yyyy");
    @Override
    public Map<String, Object> history(String[] nbbmArray, Long st, Long et) {
        Map<String, Object> rsMap = new HashMap<>();
        List<Map<String, Object>> list = new ArrayList<>();
        rsMap.put("list", list);
        if (et - st > GPS_RANGE)
            return rsMap;

        st = st * 1000;
        et = et * 1000;
        // day_of_year 分区字段
        Calendar sCal = Calendar.getInstance();
        sCal.setTime(new Date(st));
        int sDayOfYear = sCal.get(Calendar.DAY_OF_YEAR);
        Calendar eCal = Calendar.getInstance();
        eCal.setTime(new Date(et));
        int eDayOfYear = eCal.get(Calendar.DAY_OF_YEAR);

        String nbbm = nbbmArray[0];

        List<DeviceChange> dcs = findDeviceChangeLogs(nbbm, et, st);

        //按年分表
        String tableName = "bsth_c_gps_info_" + fmtyyyy.print(st);
        //String tableName = "bsth_c_gps_info";

        StringBuilder sql = new StringBuilder("");
        long t1,t2;
        DeviceChange dc;
        for(int i = 0,len=dcs.size(); i < len; i++){
            t1 = st;
            t2 = et;
            dc = dcs.get(i);
            if(dc.getSt() > st)
                t1 = dc.getSt();
            if(dc.getEt() < et && dc.getEt()!=0)
                t2 = dc.getEt();

            sql.append("select DEVICE_ID,LON,LAT,TS,INOUT_STOP,SERVICE_STATE ,STOP_NO,DIRECTION,LINE_ID,SPEED_GPS,SECTION_CODE from "+tableName+" where days_year in ("+sDayOfYear+","+eDayOfYear+") " +
                    " and device_id='"+dc.getDevice()+"' and ts >= "+t1+" and ts <= "+t2+" ");

            if(i == len - 1)
                sql.append(" ORDER BY device_id,ts,stop_no");
            else
                sql.append(" UNION ");
        }

        logger.info("轨迹回放 nbbm: " + nbbm + " -st: " + st + " -et: " + et + " -sql: " + sql.toString());

        // 查询到离站数据
        Map<String, ArrivalEntity> arrivalMap = findArrivalByTs(st, et, dcs);

        //查询GPS数据
        JdbcTemplate jdbcTemplate_ms = new JdbcTemplate(DBUtils_MS.getDataSource());
        List<Map<String, Object>> dataList = jdbcTemplate_ms.queryForList(sql.toString());

        Float lon, lat;
        Location bdLoc, gdLoc;
        int inOutStop;
        long serviceState;
        ArrivalEntity arrival;
        Set<String> lineSet=new HashSet();
        List<Map<String,Object>> lineSwitch=new ArrayList<>();

        List<Map> gpsNotValidList=new ArrayList();
        List<Map> versionSwitchList=new ArrayList();
        List<Map> gpsEqualsZeroList=new ArrayList();
        Map<String,Object> zeroMap=null;
        Map<String,Object> gpsNotValidMap=null;
        boolean isFirstNotValid=true;
        boolean isFirstLonlatZero=true;
        Map<String, Object> map = null;
        int index=0;
        for(Map<String, Object> rs : dataList){
            if (index< dataList.size()-1&&!map_get_str( rs,"LINE_ID").equals(map_get_str( dataList.get(index+1),"LINE_ID"))){
                Line cLine =lineRepository.findOne(Integer.valueOf(map_get_str( rs,"LINE_ID")));
                Line nextLine =lineRepository.findOne(Integer.valueOf(map_get_str( dataList.get(index+1),"LINE_ID")));
                if (cLine!=null&&nextLine!=null){
                    Map<String,Object> LSmap=new HashMap<>();
                    String name=cLine.getName();
                    String NextName=nextLine.getName();
                    LSmap.put("abnormalType","linesSwitch");
                    LSmap.put("line_line",name+"-->"+NextName);
                    LSmap.put("st",map_get_long(rs, "TS"));
                    LSmap.put("et",index== dataList.size()-1?map_get_long(rs, "TS"):map_get_long( dataList.get(index+1), "TS"));
                    lineSwitch.add(LSmap);
                }
            }
            serviceState = map_get_long(rs, "SERVICE_STATE");
            if(getGpsValid(serviceState) == 1){
                if (isFirstNotValid) {
                    gpsNotValidMap=new HashMap<>();
                    gpsNotValidMap.put("abnormalType","gpsNotValid");
                    gpsNotValidMap.put("st",map_get_long(rs,"TS"));
                    isFirstNotValid=false;
                }
                if (index== dataList.size()-1||index< dataList.size()-1&&getGpsValid(map_get_long(dataList.get(index+1), "SERVICE_STATE"))!=1){
                    gpsNotValidMap.put("et",map_get_long(rs,"TS"));
                    gpsNotValidList.add(gpsNotValidMap);
                    isFirstNotValid=true;
                    gpsNotValidMap=null;
                }
            }
            //continue;

            map = new HashMap<>();
            lon = map_get_float(rs, "LON");
            lat = map_get_float(rs, "LAT");
            if (lon==0||lat==0){
                if (isFirstLonlatZero){
                    zeroMap=new HashMap<>();
                    zeroMap.put("abnormalType","gpsZero");
                    zeroMap.put("st",map_get_long(rs,"TS"));
                    isFirstLonlatZero=false;
                }
                if (index<dataList.size()-1&&(map_get_float(dataList.get(index+1),"LON")!=0&&map_get_float(dataList.get(index+1),"LAT")!=0)){
                    zeroMap.put("et",map_get_long(rs,"TS"));
                    gpsEqualsZeroList.add(zeroMap);
                    isFirstLonlatZero=true;
                }
            }
            // 高德坐标
            gdLoc = TransGPS.transformFromWGSToGCJ(TransGPS.LocationMake(lon, lat));
            map.put("gcj_lon", gdLoc.getLng());
            map.put("gcj_lat", gdLoc.getLat());
            // 百度坐标
            bdLoc = TransGPS.bd_encrypt(gdLoc);
            map.put("bd_lon", bdLoc.getLng());
            map.put("bd_lat", bdLoc.getLat());
            //原始坐标
            map.put("lon", lon);
            map.put("lat", lat);

            map.put("deviceId", map_get_str(rs, "DEVICE_ID"));
            map.put("ts", map_get_long(rs, "TS"));
            map.put("timestamp", map_get_long(rs, "TS"));
            map.put("stopNo", map_get_str(rs, "STOP_NO"));
            map.put("direction", map_get_float(rs,"DIRECTION"));

            map.put("lineId", map_get_str(rs, "LINE_ID"));
            map.put("speed", map_get_float(rs,"SPEED_GPS"));
            lineSet.add(map_get_str(rs, "LINE_ID"));
            inOutStop = Integer.parseInt(rs.get("INOUT_STOP").toString());
            map.put("inout_stop", inOutStop);

            arrival = arrivalMap.get(map_get_str(rs, "DEVICE_ID") + "_" + map_get_long(rs, "TS"));
            if (arrival != null) {
                map.put("inout_stop_info", arrival);
                map.put("inout_stop", arrival.getInOut());
            }

            //map.put("nbbm", nbbm);
            map.put("state", getService(serviceState));
            // 上下行
            map.put("upDown", getUpOrDown(serviceState));
            //路段编码
            map.put("section_code", map_get_str(rs,"SECTION_CODE"));
            list.add(map);
            index++;
        }

        if (lineSet.size()>0){
            List<Map<String,Object>> vlist=new ArrayList<>();
            for (String s : lineSet) {
                int lineId=Integer.parseInt(s);
                List<LineVersions> lvs=lineVersionsRepository.findBylineId(lineId);
                Map<String,Object> vMap;
                Long qt=0L;
                if (lvs!=null&&!lvs.isEmpty()){
                    for (LineVersions lv : lvs) {
                        vMap=new HashMap();
                        Long sd=lv.getStartDate().getTime();
                        Long ed=lv.getEndDate().getTime();
                        if (sd<st&&et<ed){
                            vMap.put("line",s);
                            vMap.put("version",lv.getVersions());
                            vMap.put("vtime","all");
                        }else if(sd<st&&et>ed&&st<ed){
                            vMap.put("line",s);
                            vMap.put("version",lv.getVersions());
                            vMap.put("endTime",lv.getEndDate().getTime());
                            vMap.put("abnormalType","versionSwitch");
                            vMap.put("startTime",st);
                            vMap.put("st",lv.getEndDate().getTime());
                            qt=lv.getEndDate().getTime();
                        }else if(st<sd&&et<ed&&sd<et){
                            vMap.put("line",s);
                            vMap.put("version",lv.getVersions());
                            vMap.put("startTime",lv.getStartDate().getTime());
                            vMap.put("endTime",et);
                        }
                        if (!vMap.isEmpty()) {
                            vlist.add(vMap);
                        }
                    }
                }
                if (vlist.size()>1){
                    Map<String,Object> VSmap=new HashMap<>();
                    VSmap.put("abnormalType","vserionSwitch");
                    VSmap.put("st",qt);
                    versionSwitchList.add(VSmap);
                }
            }
            rsMap.put("lineVerson",vlist);
        }
        // 按时间排序
        Collections.sort(list, new Comparator<Map<String, Object>>() {

            @Override
            public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                return (int) (Long.parseLong(o1.get("ts").toString()) - Long.parseLong(o2.get("ts").toString()));
            }
        });

        rsMap.put("list", list);
        rsMap.put("dcs", dcs);
        rsMap.put("gpsNotValid",gpsNotValidList);
        rsMap.put("lineSwitch",lineSwitch);
        rsMap.put("lonlatZero",gpsEqualsZeroList);
        return rsMap;
    }

    private String map_get_str(Map<String, Object> map, String key){
        return map.containsKey(key)?map.get(key).toString():"";
    }

    private Long map_get_long(Map<String, Object> map, String key){
        return map.containsKey(key)?Long.parseLong(map.get(key).toString()):-1;
    }

    private Float map_get_float(Map<String, Object> map, String key){
        return map.containsKey(key)?Float.parseFloat(map.get(key).toString()):-1;
    }

    private List<DeviceChange> findDeviceChangeLogs(String nbbm, long et, long st){
        List<DeviceChange> dcs = null;
        List<DeviceChange> rs = new ArrayList<>();
        try{

            //JdbcTemplate jdbcTemplate_ms = new JdbcTemplate(DBUtils_MS.getDataSource());
            dcs = jdbcTemplate.query("select cl_zbh as nbbm,new_device_no as device,old_device_no as old_device,UNIX_TIMESTAMP(qyrq) * 1000 as st from bsth_c_car_device where is_cancel=0 and cl_zbh='"+nbbm+"' order by qyrq"
                    , BeanPropertyRowMapper.newInstance(DeviceChange.class));


            //生成一条初始记录
            if(dcs.size() > 0){
                DeviceChange first = dcs.get(0);

                DeviceChange initDv = new DeviceChange();
                initDv.setDevice(first.getOldDevice());
                if(StringUtils.isNotEmpty(initDv.getDevice())){
                    initDv.setNbbm(first.getNbbm());
                    initDv.setSt(0);
                    initDv.setEt(first.getSt());
                    dcs.add(0, initDv);
                }
            }
            for(int i = 0,len=dcs.size(); i < len - 1; i++){
                dcs.get(i).setEt(dcs.get(i + 1).getSt());
            }

            for(DeviceChange dc : dcs){
                if(dc.getEt() < st && dc.getEt() != 0)
                    continue;
                if(dc.getSt() > et)
                    continue;

                rs.add(dc);
            }

            //没有设备变更记录，则参考车辆信息上的设备号
            if(null == rs || rs.size() == 0){
                DeviceChange dc = new DeviceChange();
                dc.setNbbm(nbbm);
                dc.setDevice(BasicData.deviceId2NbbmMap.inverse().get(nbbm));
                dc.setSt(st);
                dc.setEt(et);
                dc.setType(1);

                rs.add(dc);
            }
        }catch (Exception e){
            logger.error("", e);
        }
        return rs;
    }

    public static byte getGpsValid(long serviceState) {
        return (byte)(((serviceState & 0x80000000) == 0x80000000) ? 1 : 0);
    }

    public static void main(String[] args){
        System.out.println(getGpsValid(-2147483648));
    }

    public Map<String, ArrivalEntity> findArrivalByTs(Long st, Long et, List<DeviceChange> dcs) {
        Map<String, ArrivalEntity> map = new HashMap<>();

        // weeks_year 分区字段
        Calendar sCal = Calendar.getInstance();
        sCal.setTime(new Date(st));
        int sWeekOfYear = sCal.get(Calendar.WEEK_OF_YEAR);
        Calendar eCal = Calendar.getInstance();
        eCal.setTime(new Date(et));
        int eWeekOfYear = eCal.get(Calendar.WEEK_OF_YEAR);

        //按年分表
        String tableName = "bsth_c_arrival_info_" + fmtyyyy.print(st);
        //String tableName = "bsth_c_arrival_info";

        StringBuilder sql = new StringBuilder("");
        long t1,t2;
        DeviceChange dc;
        for(int i = 0,len=dcs.size(); i < len; i++){
            t1 = st;
            t2 = et;
            dc = dcs.get(i);
            if(dc.getSt() > st)
                t1 = dc.getSt();
            if(dc.getEt() < et && dc.getEt() != 0)
                t2 = dc.getEt();

            sql.append("SELECT DEVICE_ID,LINE_ID as LINE_CODE,STOP_NO,TS,UP_DOWN,IN_OUT,WEEKS_YEAR,CREATE_DATE FROM " + tableName +
                    "  where weeks_year in ("+sWeekOfYear+", "+eWeekOfYear+") and device_id='"+dc.getDevice()+"' and ts > "+t1+" and ts < " + t2);

            if(i == len - 1)
                sql.append(" ORDER BY device_id,ts,stop_no ");
            else
                sql.append(" UNION ");
        }

        logger.info("arrivl sql : " + sql.toString());
        JdbcTemplate jdbcTemplate_ms = new JdbcTemplate(DBUtils_MS.getDataSource());
        List<ArrivalEntity> list = jdbcTemplate_ms.query(sql.toString(), BeanPropertyRowMapper.newInstance(ArrivalEntity.class));

        String stationName, prefix;
        for(ArrivalEntity arr : list){
            prefix = arr.getLineCode() + "_" + arr.getUpDown() + "_";
            stationName = BasicData.getStationNameByCode(arr.getStopNo(), prefix);

            arr.setStopName(stationName);

            // 反转进出状态
            map.put(arr.getDeviceId() + "_" + arr.getTs(), arr);
        }
        return map;
    }


    @Autowired
    StationRepository stationRepository;

    @Autowired
    CarParkRepository carParkRepository;

    @Override
    public Map<String, Object> findBuffAeraByCode(String code, String type) {
        Object[][] obj = null;
        if (type.equals("station"))
            obj = stationRepository.bufferAera(code);
        else if (type.equals("park"))
            obj = carParkRepository.bufferAera(code);

        Map<String, Object> rs = new HashMap<>();

        Object[] subObj = obj[0];
        if (subObj != null && subObj.length == 6) {
            rs.put("polygon", subObj[0]);
            rs.put("type", subObj[1]);
            rs.put("cPoint", subObj[2]);
            rs.put("radius", subObj[3]);
            rs.put("code", subObj[4]);
            rs.put("text", subObj[5]);
        }

        return rs;
    }

    @Override
    public Map<String, Object> search(Map<String, Object> map, int page, int size, String order, String direction) {
        Map<String, Object> rsMap = new HashMap<>();
        try {
            //全量
            List<GpsEntity> list = new ArrayList<>(gpsRealData.all());
            //过滤后的
            List<GpsEntity> rs = new ArrayList<>();
            Field[] fields = GpsEntity.class.getDeclaredFields();
            //参与过滤的字段
            List<Field> fs = new ArrayList<>();
            for (Field f : fields) {
                f.setAccessible(true);
                if (map.containsKey(f.getName()))
                    fs.add(f);
            }
            //过滤数据
            for (GpsEntity gps : list) {
                if (fieldEquals(fs, gps, map))
                    rs.add(gps);
            }

            //时间戳排序
            Collections.sort(rs, new Comparator<GpsEntity>() {
                @Override
                public int compare(GpsEntity o1, GpsEntity o2) {
                    return o2.getTimestamp().intValue() - o1.getTimestamp().intValue();
                }
            });

            //分页
            int count = rs.size(), s = page * size, e = s + size;
            if (e > count)
                e = count;

            rsMap.put("list", rs.subList(s, e));
            rsMap.put("totalPages", count % size == 0 ? count / size - 1 : count / size);
            rsMap.put("page", page);
            rsMap.put("status", ResponseCode.SUCCESS);
        } catch (Exception e) {
            logger.error("", e);
            rsMap.put("status", ResponseCode.ERROR);
        }
        return rsMap;
    }

    @Override
    public Map<String, Object> removeRealGps(String device) {
        Map<String, Object> rs = new HashMap<>();
        try {

            gpsRealData.remove(device);
            GpsCacheData.remove(BasicData.deviceId2NbbmMap.get(device));
            rs.put("status", ResponseCode.SUCCESS);
        } catch (Exception e) {
            rs.put("status", ResponseCode.ERROR);
        }
        return rs;
    }

    @Override
    public Map<String, Object> findRoadSpeed(String lineCode) {
        Map<String, Object> rs = new HashMap<>();

        try {
            String sql = "select ID, ST_AsText(GROAD_VECTOR) as GROAD_VECTOR,ROAD_CODE,ROAD_NAME,SPEED from bsth_c_road where road_code in(select section_code from bsth_c_sectionroute where line_code=? and destroy=0)";
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, lineCode);
            rs.put("status", ResponseCode.SUCCESS);
            rs.put("roads", list);
        } catch (DataAccessException e) {
            logger.error("", e);
            rs.put("status", ResponseCode.ERROR);
        }
        return rs;
    }

    /**
     * gps补全
     *
     * @param schId
     * @return
     */
    @Override
    public Map<String, Object> gpsCompletion(long schId, int type) {
        Map<String, Object> rs = new HashMap<>();

        try {
            ScheduleRealInfo sch = dayOfSchedule.get(schId);
            if (sch == null) {
                rs.put("status", ResponseCode.ERROR);
                rs.put("msg", "找不到对应班次！！！");
                return rs;
            }

            if (sch.isReissue()) {
                rs.put("status", ResponseCode.ERROR);
                rs.put("msg", "你不能重复这个操作");
                return rs;
            }

            String sql = "select * from bsth_gps_template where line_id='" + sch.getXlBm() + "' and up_down=" + sch.getXlDir();
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);

            if (list.size() == 0) {
                rs.put("status", ResponseCode.ERROR);
                rs.put("msg", "缺少模板数据，请联系系统管理员！！");
                return rs;
            }
            //排序
            Collections.sort(list, new Comparator<Map<String, Object>>() {
                @Override
                public int compare(Map<String, Object> o1, Map<String, Object> o2) {
                    return (int) (Long.parseLong(o1.get("ts").toString()) - Long.parseLong(o2.get("ts").toString()));
                }
            });
            Map<String, Object> fs = list.get(0);
            //替换设备号和时间
            long diff = ((sch.getDfsjT() - Long.parseLong(fs.get("ts").toString())) - 1000 * 70);

            String deviceId = BasicData.deviceId2NbbmMap.inverse().get(sch.getClZbh());
            int serviceState;
            for (Map<String, Object> map : list) {
                map.put("device_id", deviceId);
                map.put("ts", Long.parseLong(map.get("ts").toString()) + diff);
                if(type==1){
                    //走补传协议
                    serviceState = Integer.parseInt(map.get("service_state").toString());
                    map.put("service_state", serviceState |= 0x00100000);
                }
            }

            String sqlBefore = "insert into bsth_c_template(", sqlValues = " values(";

            Set<String> ks = fs.keySet();
            for (String k : ks) {
                sqlBefore += (k + ",");
                sqlValues += "?,";
            }
            sqlBefore = sqlBefore.substring(0, sqlBefore.length() - 1) + ", create_ts)";
            sqlValues = sqlValues.substring(0, sqlValues.length() - 1) + ", " + System.currentTimeMillis() + ")";
            sql = sqlBefore + " " + sqlValues;

            Connection conn = DBUtils_MS.getConnection();
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            int fsize = ks.size();
            List<Object> vs;
            for (Map<String, Object> map : list) {
                vs = new ArrayList<>(map.values());
                for (int i = 0; i < fsize; i++) {
                    ps.setObject(i + 1, vs.get(i));
                }
                ps.addBatch();
            }
            ps.executeBatch();
            conn.commit();

            rs.put("status", ResponseCode.SUCCESS);

            //标记班次
            sch.setReissue(true);
            scheduleRealInfoRepository.save(sch);

            rs.put("status", ResponseCode.SUCCESS);
        } catch (Exception e) {
            logger.error("", e);
            rs.put("status", ResponseCode.ERROR);
        }
        return rs;
    }

    @Override
    public Map<String, Object> history_v2(String nbbm, long st, long et) {
        Map<String, Object> rs = new HashMap<>();

        try {
            //获取历史gps 数据
            List<HistoryGps_DTO> list = HistoryGps_DTO.craete((List<Map<String, Object>>) history(new String[]{nbbm}, st, et).get("list"));
            if (list != null && list.size() > 0) {
                //获取路段信息
                String sql = "select ID, ST_AsText(GROAD_VECTOR) as GROAD_VECTOR,ROAD_CODE,ROAD_NAME,SPEED from bsth_c_road where road_code in(select section_code from bsth_c_sectionroute where line_code=? and destroy=0)";
                List<Road_DTO> roads = Road_DTO.craete(jdbcTemplate.queryForList(sql, list.get(0).getLineId()));

                //为GPS数据关联路段信息
                for (HistoryGps_DTO gps : list) {
                    matchRoadToGps(gps, roads);
                }
            }

            //超速数据
            List<GpsSpeed_DTO> speedList = speeds(nbbm, st, et);
            //越界数据
            List<GpsOutbound_DTO> outboundList = outbounds(nbbm, st, et);
            //计算里程
            List<HistoryGps_DTO> effList = new ArrayList<>();
            for(HistoryGps_DTO gps : list){
                if(gps.getLat() != 0 && gps.getLon() != 0)
                    effList.add(gps);
            }
            double sum = 0, dist;
            for (int i = 0; i < effList.size() - 1; i++) {
                dist = GeoUtils.getDistance(effList.get(i).getPoint(), effList.get(i + 1).getPoint());
                //点位相同时，dist会NaN
                if(String.valueOf(dist).matches("^[0.0-9.0]+$"))
                    sum += dist;
            }

            rs.put("status", ResponseCode.SUCCESS);
            rs.put("list", removeDuplicate(effList));
            rs.put("speedList", speedList);
            rs.put("outboundList", outboundList);
            rs.put("sumMileage", new DecimalFormat(".##").format(sum / 1000));
        } catch (Exception e) {
            logger.error("", e);
            rs.put("status", ResponseCode.ERROR);
        }
        return rs;
    }


    @Override
    public Map<String, Object> history_v3(String nbbm, long st, long et) {
        Map<String, Object> rs = new HashMap<>();

        try {
            //获取历史gps 数据
            Map<String, Object> gpsMap = history(new String[]{nbbm}, st, et);
            List<HistoryGps_DTOV3> list = HistoryGps_DTOV3.craete((List<Map<String, Object>>) gpsMap.get("list"));
            if (list != null && list.size() > 0) {
                //关联路段名称
                Map<String, String> sectionCode2Name = GeoCacheData.sectionCode2NameMap();
                for(HistoryGps_DTOV3 gps : list){
                    if(StringUtils.isNotEmpty(gps.getSection_code()))
                        gps.setSection_name(sectionCode2Name.get(gps.getSection_code()));
                    else{
                        gps.setSection_code("-00404");
                        gps.setSection_name("未知路段");
                    }
                }
            }

            //超速数据
            List<GpsSpeed_DTO> speedList = speeds(nbbm, st, et);

            //越界数据
            List<GpsOutbound_DTO> outboundList = outbounds(nbbm, st, et);

            //计算里程
            List<HistoryGps_DTOV3> effList = new ArrayList<>();
            for(HistoryGps_DTOV3 gps : list){
                if(gps.getLat() != 0 && gps.getLon() != 0)
                    effList.add(gps);
            }
            double sum = 0, dist;
            for (int i = 0; i < effList.size() - 1; i++) {
                dist = GeoUtils.getDistance(effList.get(i).getPoint(), effList.get(i + 1).getPoint());
                //点位相同时，dist会NaN
                if(String.valueOf(dist).matches("^[0.0-9.0]+$")){
                    if(dist > 0.8)
                        sum += dist;
                }
            }

            rs.put("status", ResponseCode.SUCCESS);
            rs.put("list", removeDuplicateV3(effList));
            rs.put("speedList", speedList);
            rs.put("outboundList", outboundList);
            rs.put("sumMileage", new DecimalFormat(".##").format(sum / 1000));
            rs.put("dcs", gpsMap.get("dcs"));
            rs.put("lineVerson",gpsMap.get("lineVerson"));
            rs.put("gpsInvalid",gpsMap.get("gpsNotValid"));
            rs.put("gpslineSwitch",gpsMap.get("lineSwitch"));
            rs.put("gpslonlatex",gpsMap.get("lonlatZero"));
        } catch (Exception e) {
            logger.error("", e);
            rs.put("status", ResponseCode.ERROR);
            rs.put("msg", e.getMessage());
        }
        return rs;
    }

    @Override
    public void trailExcel(String nbbm, long st, long et, HttpServletResponse resp) {
        //获取历史gps 数据
        List<HistoryGps_DTOV3> list = HistoryGps_DTOV3.craete((List<Map<String, Object>>) history(new String[]{nbbm}, st, et).get("list"));
        if (list != null && list.size() > 0) {
            //关联路段名称
            Map<String, String> sectionCode2Name = GeoCacheData.sectionCode2NameMap();
            for(HistoryGps_DTOV3 gps : list){
                if(StringUtils.isNotEmpty(gps.getSection_code()))
                    gps.setSection_name(sectionCode2Name.get(gps.getSection_code()));
                else{
                    gps.setSection_code("-00404");
                    gps.setSection_name("未知路段");
                }
            }
        }

        //创建excel工作簿
        Workbook wb = new HSSFWorkbook();
        Sheet sheet = wb.createSheet("行车轨迹");
        //表头
        Row row = sheet.createRow(0);
        row.setHeight((short) (1.5 * 256));
        row.createCell(0).setCellValue("序号");
        row.createCell(1).setCellValue("车辆");
        row.createCell(2).setCellValue("牌照号");
        row.createCell(3).setCellValue("所在道路");
        row.createCell(4).setCellValue("经度");
        row.createCell(5).setCellValue("纬度");
        row.createCell(6).setCellValue("时间");
        row.createCell(7).setCellValue("速度");
        //数据
        DateTimeFormatter fmtHHmmss = DateTimeFormat.forPattern("HH:mm.ss"),
                fmt = DateTimeFormat.forPattern("yyyyMMddHHmm");
        HistoryGps_DTOV3 gps;
        for(int i = 0; i < list.size(); i ++){
            gps = list.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(i + 1);
            row.createCell(1).setCellValue(nbbm);
            row.createCell(2).setCellValue(BasicData.nbbmCompanyPlateMap.get(nbbm));
            row.createCell(3).setCellValue(gps.getSection_name());
            row.createCell(4).setCellValue(gps.getLon());
            row.createCell(5).setCellValue(gps.getLat());
            row.createCell(6).setCellValue(fmtHHmmss.print(gps.getTimestamp()));
            row.createCell(7).setCellValue(gps.getSpeed());
        }

        st = st * 1000;
        et = et * 1000;
        String filename = nbbm + "轨迹数据" + fmt.print(st) + "至" + fmt.print(et) + ".xls";
        try {
            resp.setContentType("application/x-msdownload");
            resp.addHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));

            OutputStream out=resp.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        } catch (UnsupportedEncodingException e) {
            logger.error("", e);
        } catch (IOException e) {
            logger.error("", e);
        }
    }

    @Override
    public void abnormalExcel(String nbbm, long st, long et, HttpServletResponse resp) {
        //超速数据
        List<GpsSpeed_DTO> speedList = speeds(nbbm, st, et);
        //越界数据
        List<GpsOutbound_DTO> outboundList = outbounds(nbbm, st, et);

        //创建excel工作簿
        Workbook wb = new HSSFWorkbook();

        DateTimeFormatter fmtHHmmss = DateTimeFormat.forPattern("HH:mm.ss"),
                fmt = DateTimeFormat.forPattern("yyyyMMddHHmm");
        if(speedList.size() > 0){
            Sheet sheet = wb.createSheet("超速");
            //表头
            Row row = sheet.createRow(0);
            row.setHeight((short) (1.5 * 256));
            row.createCell(0).setCellValue("异常信息");
            row.createCell(1).setCellValue("最大速度");
            row.createCell(2).setCellValue("开始时间");
            row.createCell(3).setCellValue("结束时间");
            row.createCell(4).setCellValue("持续（秒）");
            row.createCell(5).setCellValue("所在路段");

            GpsSpeed_DTO speed;
            for(int i = 0; i < speedList.size(); i++){
                speed = speedList.get(i);
                row = sheet.createRow(i + 1);
                row.createCell(0).setCellValue("超速");
                row.createCell(1).setCellValue(speed.getSpeed());
                row.createCell(2).setCellValue(fmtHHmmss.print(speed.getSt()));
                row.createCell(3).setCellValue(fmtHHmmss.print(speed.getEt()));
                if(speed.getEt() != 0)
                    row.createCell(4).setCellValue((speed.getEt() - speed.getSt()) / 1000);
                row.createCell(5).setCellValue("");
            }
        }

        if(outboundList.size() > 0){
            Sheet sheet = wb.createSheet("越界");
            //表头
            Row row = sheet.createRow(0);
            row.setHeight((short) (1.5 * 256));
            row.createCell(0).setCellValue("异常信息");
            row.createCell(1).setCellValue("开始时间");
            row.createCell(2).setCellValue("结束时间");
            row.createCell(3).setCellValue("持续（秒）");
            row.createCell(4).setCellValue("所在路段");
            row.createCell(5).setCellValue("路径");

            GpsOutbound_DTO outbound;
            //设置路径单元格 水平对齐 填充
            CellStyle cs = wb.createCellStyle();
            cs.setAlignment(HSSFCellStyle.ALIGN_FILL);
            for(int i = 0; i < outboundList.size(); i++){
                outbound = outboundList.get(i);
                row = sheet.createRow(i + 1);
                row.createCell(0).setCellValue("超速");
                row.createCell(1).setCellValue(fmtHHmmss.print(outbound.getSt()));
                row.createCell(2).setCellValue(fmtHHmmss.print(outbound.getEt()));
                if(outbound.getEt() != 0)
                    row.createCell(3).setCellValue((outbound.getEt() - outbound.getSt()) / 1000);
                row.createCell(4).setCellValue("");
                row.createCell(5).setCellValue(outbound.getLocations());

                row.getCell(5).setCellStyle(cs);
            }
        }

        st = st * 1000;
        et = et * 1000;
        String filename = nbbm + "异常信息" + fmt.print(st) + "至" + fmt.print(et) + ".xls";
        try {
            resp.setContentType("application/x-msdownload");
            resp.addHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));

            OutputStream out=resp.getOutputStream();
            wb.write(out);
            out.flush();
            out.close();
        } catch (UnsupportedEncodingException e) {
            logger.error("", e);
        } catch (IOException e) {
            logger.error("", e);
        }
    }

    @Override
    public void arrivalExcel(String nbbm, long st, long et, HttpServletResponse resp) {

    }

    @Override
    public List<GpsSpeed_DTO> speeds(String nbbm, long st, long et) {
        st = st * 1000;
        et = et * 1000;
        //按周分区
        /*Calendar sCal = Calendar.getInstance();
        sCal.setTime(new Date(st));
        int sWeekYear = sCal.get(Calendar.WEEK_OF_YEAR);
        Calendar eCal = Calendar.getInstance();
        eCal.setTime(new Date(et));
        int eWeekYear = eCal.get(Calendar.WEEK_OF_YEAR);*/

        //按年分表
        String tableName = "bsth_c_speeding_" + fmtyyyy.print(st);
        //String tableName = "bsth_c_speeding";

        List<DeviceChange> dcs = findDeviceChangeLogs(nbbm, et, st);
        StringBuilder sql = new StringBuilder("");
        long t1,t2;
        DeviceChange dc;
        for(int i = 0,len=dcs.size(); i < len; i++){
            t1 = st;
            t2 = et;
            dc = dcs.get(i);
            if(dc.getSt() > st)
                t1 = dc.getSt();
            if(dc.getEt() < et && dc.getEt()!=0)
                t2 = dc.getEt();

            sql.append(" select vehicle, line, up_down, lon, lat, speed,timestamp from "+tableName+" where vehicle='"+dc.getDevice()+"' and timestamp>="+t1+" and timestamp<= " + t2);

            if(i == len - 1)
                sql.append(" ORDER BY vehicle,timestamp");
            else
                sql.append(" UNION ");
        }

        logger.info("speed sql : " + sql.toString());
        return GpsSpeed_DTO.create(new JdbcTemplate(DBUtils_MS.getDataSource()).queryForList(sql.toString()));
    }

    @Override
    public List<GpsOutbound_DTO> outbounds(String nbbm, long st, long et) {
        st = st * 1000;
        et = et * 1000;
        //按周分区
        Calendar sCal = Calendar.getInstance();
        sCal.setTime(new Date(st));
        int sWeekYear = sCal.get(Calendar.WEEK_OF_YEAR);
        Calendar eCal = Calendar.getInstance();
        eCal.setTime(new Date(et));
        int eWeekYear = eCal.get(Calendar.WEEK_OF_YEAR);

        //按年分表
        String tableName = "bsth_c_outbound_" + fmtyyyy.print(st);
        //String tableName = "bsth_c_outbound";

        List<DeviceChange> dcs = findDeviceChangeLogs(nbbm, et, st);
        StringBuilder sql = new StringBuilder("");
        long t1,t2;
        DeviceChange dc;
        for(int i = 0,len=dcs.size(); i < len; i++){
            t1 = st;
            t2 = et;
            dc = dcs.get(i);
            if(dc.getSt() > st)
                t1 = dc.getSt();
            if(dc.getEt() < et && dc.getEt()!=0)
                t2 = dc.getEt();

            sql.append("select vehicle,line,up_down,lon,lat,timestamp from "+tableName+" where " +
                    " weeks_year in ("+sWeekYear+", "+eWeekYear+") and vehicle='"+dc.getDevice()+"' and timestamp>="+t1+" and timestamp<=" + t2);

            if(i == len - 1)
                sql.append(" ORDER BY vehicle,timestamp");
            else
                sql.append(" UNION ");
        }

        logger.info("outbounds sql : " + sql.toString());
        return GpsOutbound_DTO.create(new JdbcTemplate(DBUtils_MS.getDataSource()).queryForList(sql.toString()));
    }

    @Override
    public Map<String, Object> safeDrivList(Map<String, Object> map, int page, int size, String order, String direction) {
        Map<String, Object> rsMap = new HashMap<>();
        try {
            //全量
            List<SafeDriv> list = new ArrayList<>(SafeDrivCenter.findAll());
            //过滤后的
            List<SafeDriv> rs = new ArrayList<>();
            Field[] fields = SafeDriv.class.getDeclaredFields();
            //参与过滤的字段
            List<Field> fs = new ArrayList<>();
            for (Field f : fields) {
                f.setAccessible(true);
                if (map.containsKey(f.getName()))
                    fs.add(f);
            }
            //过滤数据
            for (SafeDriv sd : list) {
                if (fieldEquals(fs, sd, map))
                    rs.add(sd);
            }

            //时间戳排序
            Collections.sort(rs, new Comparator<SafeDriv>() {
                @Override
                public int compare(SafeDriv o1, SafeDriv o2) {
                    return o2.getTs().intValue() - o1.getTs().intValue();
                }
            });

            //分页
            int count = rs.size(), s = page * size, e = s + size;
            if (e > count)
                e = count;

            rsMap.put("list", rs.subList(s, e));
            rsMap.put("totalPages", count % size == 0 ? count / size - 1 : count / size);
            rsMap.put("page", page);
            rsMap.put("status", ResponseCode.SUCCESS);
        } catch (Exception e) {
            logger.error("", e);
            rsMap.put("status", ResponseCode.ERROR);
        }
        return rsMap;
    }

    private void matchRoadToGps(HistoryGps_DTO gps, List<Road_DTO> roads) {
        double min = -1, distance;
        Road_DTO nearRoad = null;
        for (Road_DTO road : roads) {
            distance = GeoUtils.getDistanceFromLine(road.getLineStr(), gps.getPoint());

            if (min > distance || min == -1) {
                min = distance;
                nearRoad = road;
            }
        }

        gps.setRoad(nearRoad);
        gps.setRoadMinDistance(min);
    }


    private void matchRoadToGps(HistoryGps_DTOV3 gps, List<Road_DTO> roads) {
        double min = -1, distance;
        Road_DTO nearRoad = null;
        for (Road_DTO road : roads) {
            distance = GeoUtils.getDistanceFromLine(road.getLineStr(), gps.getPoint());

            if (min > distance || min == -1) {
                min = distance;
                nearRoad = road;
            }
        }

        if(min < 200){
            gps.setSection_code(nearRoad.getROAD_CODE());
            gps.setSection_name(nearRoad.getROAD_NAME());
        }
        else {
            gps.setSection_code("-00404");
            gps.setSection_name("未知路段");
        }
        //gps.setRoad(nearRoad);
        //gps.setRoadMinDistance(min);
    }

    /**
     * 去重复
     *
     * @param list
     * @return
     */
    private Set<HistoryGps_DTO> removeDuplicate(List<HistoryGps_DTO> list) {
        Set<HistoryGps_DTO> set = new HashSet<>();
        for (HistoryGps_DTO gps : list) {
            set.add(gps);
        }
        return set;
    }

    /**
     * 去重复
     *
     * @param list
     * @return
     */
    private Set<HistoryGps_DTOV3> removeDuplicateV3(List<HistoryGps_DTOV3> list) {
        Set<HistoryGps_DTOV3> set = new HashSet<>();
        for (HistoryGps_DTOV3 gps : list) {
            set.add(gps);
        }
        return set;
    }


    private void sortGpsList(final Field f, List<GpsEntity> rs) {
        Collections.sort(rs, new Comparator<GpsEntity>() {

            @Override
            public int compare(GpsEntity o1, GpsEntity o2) {
                try {
                    if (f.get(o1) == f.get(o2))
                        return 0;

                    if (null == f.get(o1))
                        return 1;

                    if (null == f.get(o2))
                        return -1;

                    return f.get(o1).toString().compareTo(f.get(o2).toString());
                } catch (Exception e) {
                    logger.error("", e);
                    return -1;
                }
            }
        });
    }

    public boolean fieldEquals(List<Field> fs, Object obj, Map<String, Object> map) {
        try {
            String fv, v;
            for (Field f : fs) {
                if (StringUtils.isEmpty(map.get(f.getName()).toString()))
                    continue;

                if(f.get(obj) == null)
                    return false;

                fv = f.get(obj).toString();
                v = map.get(f.getName()).toString();

                if(!fv.startsWith(v)/* && !fv.endsWith(v)*/)
                    return false;
            }
        } catch (Exception e) {
            logger.error("", e);
            return false;
        }
        return true;
    }

    @Override
    public List<GpsSpeed> findPosition(String deviceid, String startdate,
                                       String enddate) throws ParseException{
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Calendar c = Calendar.getInstance();
        Date date = sdf.parse(startdate);
        c.setTime(date);
        int daysYear = c.get(Calendar.DAY_OF_YEAR);//获取当前是今年的第几天。

        long startTime = sdf.parse(startdate).getTime();
        long endTime = sdf.parse(enddate).getTime();

        String sql = "select DEVICE_ID,LON,LAT,TS,INOUT_STOP,SERVICE_STATE ,speed_gps from bsth_c_gps_info where days_year=? and device_id=? and ts >= ? and ts <= ?" +
                "     ORDER BY TS ";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<GpsSpeed> listResult = new ArrayList<GpsSpeed>();
        GpsSpeed gpsSpeed = null;
        try {
            conn = DBUtils_MS.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, daysYear);
            ps.setString(2, deviceid);
            ps.setLong(3,startTime);
            ps.setLong(4,endTime);
            rs = ps.executeQuery();
            Float lon, lat;
            Location location;
            while (rs.next()) {
                gpsSpeed = new GpsSpeed();
                // to 百度坐标
                lon = rs.getFloat("LON");
                lat = rs.getFloat("LAT");
                location = TransGPS.LocationMake(lon, lat);
                location = TransGPS.bd_encrypt(TransGPS.transformFromWGSToGCJ(location));
                gpsSpeed.setVehicle(rs.getString("device_id"));
                gpsSpeed.setLon((float)location.getLng());
                gpsSpeed.setLat((float)location.getLat());
                gpsSpeed.setSpeed(rs.getFloat("speed_gps"));
                gpsSpeed.setTimestamp(rs.getLong("TS"));
                // 上下行
                listResult.add(gpsSpeed);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtils_MS.close(rs, ps, conn);
        }
        return listResult;

    }

    @Override
    public Map<String, Object> Pagequery(Map<String, Object> map) {
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Integer totalDays = 0;//数据跨越天数
        try {
            totalDays = (int) ((sdf.parse(map.get("endDate").toString()+" 23:59:59").getTime()-sdf.parse(map.get("startDate").toString()+" 00:00:00").getTime()+1)/(3600*24*1000))+1;
        } catch (ParseException e) {
            e.printStackTrace();
        }//总页数
        map.put("totalDays",totalDays);
        List<GpsSpeed> list=findAll(map);
        List<GpsSpeed> listResult = new ArrayList<GpsSpeed>();
        int curPage = 0;//页码
        int pageData = 0;//每页的记录条数
        if(list.size()>1){
            GpsSpeed GpsSpeedNow;//下标为i的车辆行驶记录
            GpsSpeed GpsSpeedLast;//下标为i-1的车辆行驶记录
            GpsSpeed spped = null;//整合后的车辆行驶记录
            String strNow;
            String strLast;
            boolean Flag = false;//判断是否有连续超速记录,默认没有
            for(int i = 1;i<list.size();i++){
                GpsSpeedNow = list.get(i);
                GpsSpeedLast = list.get(i-1);
                strNow = GpsSpeedNow.getVehicle()+GpsSpeedNow.getLine()+GpsSpeedNow.getUp_down();
                strLast = GpsSpeedLast.getVehicle()+GpsSpeedLast.getLine()+GpsSpeedLast.getUp_down();
                if(GpsSpeedNow.getSpeed()>60 && GpsSpeedLast.getSpeed()>60 && strNow.equals(strLast)){//如果两条连续的记录都是超速且属于同一辆车。
                    if(Flag==false){//
                        spped = new GpsSpeed();
                        spped.setLine(GpsSpeedLast.getLine());//设置连续超速记录线路
                        spped.setLineName(GpsSpeedLast.getLineName());//设置连续超速记录线路名称
                        spped.setVehicle(GpsSpeedLast.getVehicle());//设置连续超速记录的车辆编号
                        spped.setUp_down(GpsSpeedLast.getUp_down());//设置上下行
                        spped.setLon(GpsSpeedLast.getLon());//设置开始时经度
                        spped.setLat(GpsSpeedLast.getLat());//设置开始时纬度
                        spped.setTimestamp(GpsSpeedLast.getTimestamp());//设置连续超速记录的开始时间
                        spped.setTimestampDate(GpsSpeedLast.getTimestampDate());//设置连续超速记录的开始时间戳
                    }
                    spped.setEndtimestamp(GpsSpeedNow.getTimestamp());//设置结束时间戳
                    spped.setEndtimestampDate(sdf.format(new Date(GpsSpeedNow.getTimestamp())));//设置结束时间
                    spped.setEndlon(GpsSpeedNow.getLon());//设置结束时的经度
                    spped.setEndlat(GpsSpeedNow.getLat());//设置结束时的纬度
                    Flag = true;
                }else{
                    if(Flag){//如果上一条记录超速。
                        listResult.add(spped);
                        Flag = false;
                    }
                }
            }
            if(listResult.size()>0){
                Iterator<GpsSpeed> speedIt = listResult.iterator();
                while(speedIt.hasNext()){
                    GpsSpeed GpsSpeed = speedIt.next();
                    if(GpsSpeed.getEndtimestamp()-GpsSpeed.getTimestamp()<=1000){
                        speedIt.remove();
                    }
                }
            }
        }
        if(map.get("curPage") == null || map.get("curPage").equals("0")){
            curPage = 0;
        }else{
            curPage = Integer.parseInt((String) map.get("curPage"));
        }
        Integer totalPage = totalDays;
        pageData = listResult.size();//每页的记录条数就是当前页查出的全部数据。
        Map<String,Object> paramMap = new HashMap<String,Object>();
        paramMap.put("totalPage", totalPage);
        paramMap.put("page", curPage);
        paramMap.put("pageData", pageData);
        paramMap.put("list", listResult);
        return paramMap;
    }

    @Override
    public Map<String, Object> allCarsByLine(String lineCode) {
        Map<String, Object> map = new HashMap();
        try{
            List<Map<String, Object>> list = new ArrayList<>();
            Map<String, Object> item;
            GpsEntity gps;
            //当天线路下营运的车辆
            Set<String> cars = dayOfSchedule.findCarByLineCode(lineCode);
            ScheduleRealInfo sch;
            String device;

            Map<String, Integer> allDevices = new HashMap<>();
            String execStr = "";
            D80 d80;
            for(String nbbm : cars){
                item = new HashMap<>();
                device = BasicData.deviceId2NbbmMap.inverse().get(nbbm);
                allDevices.put(device, 1);
                item.put("nbbm", nbbm);
                item.put("device", device);

                sch = dayOfSchedule.executeCurr(nbbm);
                if(null != sch){
                    execStr = (sch.getXlDir().equals("0")?"上行":"下行") + "("+sch.getDfsj()+")";
                    if(!sch.getXlBm().equals(lineCode))
                        execStr = sch.getXlName()+execStr;
                    else
                        item.put("schId", sch.getId());
                    item.put("exec", execStr);
                }

                gps = gpsRealData.get(device);
                if(null != gps){
                    item.put("loc", gps.getStationName());
                    item.put("lineCodeReal", gps.getLineId());
                    item.put("status", gps.isOffline()?"离线":"在线");
                    item.put("gpsTs", gps.getTimestamp());
                }
                //请求出场时间
                d80 = PilotReport.qqccMap.get(device);
                if(null != d80)
                    item.put("qqcc", d80.getTimestamp());

                list.add(item);
            }

            //车载编码落在该线路的设备
            Set<String> devices = gpsRealData.findDevices(lineCode);
            for(String d : devices){
                if(allDevices.containsKey(d))
                    continue;

                gps = gpsRealData.get(d);
                if(null == gps)
                    continue;

                item = new HashMap<>();
                item.put("nbbm", gps.getNbbm());
                item.put("device", d);
                item.put("loc", gps.getStationName());
                item.put("lineCodeReal", gps.getLineId());
                item.put("status", gps.isOffline()?"离线":"在线");
                item.put("gpsTs", gps.getTimestamp());

                //请求出场时间
                d80 = PilotReport.qqccMap.get(d);
                if(null != d80)
                    item.put("qqcc", d80.getTimestamp());

                list.add(item);
            }

            map.put("list", list);
            map.put("status", ResponseCode.SUCCESS);
        }catch (Exception e){
            logger.error("", e);
            map.put("status", ResponseCode.ERROR);
            map.put("msg", e.getMessage());
        }
        return map;
    }

    static List<GpsSpeed> findAll(Map<String, Object> map) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<GpsSpeed> list=new ArrayList<GpsSpeed>();
        String sql="select * from bsth_c_gps_info 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");
        Integer totalDays = Integer.valueOf(map.get("totalDays").toString());
        Integer curPage = 0;//页码
        if(map.get("curPage") == null || map.get("curPage").equals("0")){
            curPage = 0;
        }else{
            curPage = Integer.parseInt((String) map.get("curPage"));
        }

        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        if(line!=null){
            sql +=" and line_id 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 industry_code like '%"+updown.toString()+"%'";
        }
        if(startDate!=null){
            if (startDate.toString().length()>0) {
                try {
                    Long t1=sdf.parse(startDate.toString()+" 00:00:00").getTime()+curPage*3600*24*1000;
                    sql += " and ts >="+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()-(totalDays-1-curPage)*3600*24*1000;
                    sql += " and ts <="+t2;
                } catch (ParseException e) {
                    e.printStackTrace();
                }
            }

        }

        try {
            conn = DBUtils_MS.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            list = resultSet2Set(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils_MS.close(rs, ps, conn);
        }

        return list;
    }

    static List<GpsSpeed> resultSet2Set(ResultSet rs) throws SQLException{
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        List<GpsSpeed> list=new ArrayList<GpsSpeed>();
        GpsSpeed GpsSpeed;
        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));
            GpsSpeed=new GpsSpeed();
            GpsSpeed.setLon((float)location.getLng());
            GpsSpeed.setLat((float)location.getLat());
            GpsSpeed.setLine(rs.getObject("line_id").toString());
            //run 时注解
            GpsSpeed.setLineName(BasicData.lineCode2NameMap.get(GpsSpeed.getLine().toString()));
            GpsSpeed.setSpeed(Float.valueOf(rs.getObject("speed_gps").toString()));
            GpsSpeed.setTimestamp((Long.valueOf(rs.getObject("ts").toString())));
            GpsSpeed.setTimestampDate(sdf.format(new Date(GpsSpeed.getTimestamp())));
            GpsSpeed.setUp_down(((Integer.valueOf(rs.getObject("service_state").toString())) & 0x10000000)==0?0:1);
            GpsSpeed.setVehicle(BasicData.deviceId2NbbmMap.get(rs.getObject("device_id").toString()));
            list.add(GpsSpeed);
        }
        return list;
    }

}