潘钊 / 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.GpsEntity;
import com.bsth.data.gpsdata.GpsRealData;
import com.bsth.data.gpsdata.arrival.GeoCacheData;
import com.bsth.data.gpsdata.arrival.utils.GeoUtils;
import com.bsth.data.safe_driv.SafeDriv;
import com.bsth.data.safe_driv.SafeDrivCenter;
import com.bsth.data.schedule.DayOfSchedule;
import com.bsth.entity.realcontrol.ScheduleRealInfo;
import com.bsth.repository.CarParkRepository;
import com.bsth.repository.StationRepository;
import com.bsth.repository.realcontrol.ScheduleRealInfoRepository;
import com.bsth.service.gps.entity.*;
import com.bsth.util.DateUtils;
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.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;

    // 历史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);
    }

    @Override
    public List<Map<String, Object>> history(String[] nbbmArray, Long st, Long et) {
        List<Map<String, Object>> list = new ArrayList<>();
        // 超过最大查询范围，直接忽略
        if (et - st > GPS_RANGE)
            return list;

        // 车辆编码转换成设备号
        String[] devices = new String[nbbmArray.length];
        for (int i = 0; i < nbbmArray.length; i++) {
            devices[i] = BasicData.deviceId2NbbmMap.inverse().get(nbbmArray[i]);
        }
        // day_of_year
        Calendar sCal = Calendar.getInstance();
        sCal.setTime(new Date(st * 1000));
        int sDayOfYear = sCal.get(Calendar.DAY_OF_YEAR)/* 200 */;

        Calendar eCal = Calendar.getInstance();
        eCal.setTime(new Date(et * 1000));
        int eDayOfYear = eCal.get(Calendar.DAY_OF_YEAR)/* 200 */;

        Calendar weekCal = Calendar.getInstance();

        // 如果是同一天
        if (sDayOfYear == eDayOfYear) {
            weekCal.setTimeInMillis(st * 1000);
            list = findByTs(weekCal.get(Calendar.WEEK_OF_YEAR), sDayOfYear, st, et, devices);
        } else {
            // 跨天
            Long tempSt = 0L, tempEt = 0L;
            for (int i = sDayOfYear; i <= eDayOfYear; i++) {

                if (i == sDayOfYear) {
                    tempSt = st;
                    tempEt = DateUtils.getTimesnight(sCal);
                } else if (i == eDayOfYear) {
                    tempSt = DateUtils.getTimesmorning(sCal);
                    tempEt = et;
                } else {
                    tempSt = DateUtils.getTimesmorning(sCal);
                    tempEt = DateUtils.getTimesnight(sCal);
                }

                weekCal.setTimeInMillis(tempSt * 1000);
                list.addAll(findByTs(weekCal.get(Calendar.WEEK_OF_YEAR), i, tempSt, tempEt, devices));
                // 加一天
                sCal.add(Calendar.DATE, 1);
            }
        }

        // 按时间排序
        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()));
            }
        });
        return list;
    }

    public List<Map<String, Object>> findByTs(int weekOfYear, int dayOfYear, Long st, Long et, String[] devices) {
        List<Map<String, Object>> list = new ArrayList<>();
        Map<String, Object> map = null;

        // 直接拼 in 语句
        String inv = "";
        for (String device : devices)
            inv += ("'" + device + "',");
        inv = inv.substring(0, inv.length() - 1);

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

        String sql = "select DEVICE_ID,LON,LAT,TS,INOUT_STOP,SERVICE_STATE ,STOP_NO,DIRECTION,LINE_ID,SPEED_GPS,SECTION_CODE from bsth_c_gps_info where days_year=? and device_id in ("
                + inv + ") and ts > ? and ts < ? ORDER BY device_id,ts,stop_no";
        try {
            conn = DBUtils_MS.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, dayOfYear);
            /* ps.setArray(2, conn.createArrayOf("VARCHAR", devices)); */
            ps.setLong(2, st * 1000);
            ps.setLong(3, et * 1000);

            rs = ps.executeQuery();
            Float lon, lat;
            Location bdLoc, gdLoc;
            int inOutStop;
            long serviceState;
            ArrivalEntity arrival;
            while (rs.next()) {
                serviceState = rs.getLong("SERVICE_STATE");
                map = new HashMap<>();

                lon = rs.getFloat("LON");
                lat = rs.getFloat("LAT");
                // 高德坐标
                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", rs.getString("DEVICE_ID"));
                map.put("ts", rs.getLong("TS"));
                map.put("timestamp", rs.getLong("TS"));
                map.put("stopNo", rs.getString("STOP_NO"));
                map.put("direction", rs.getFloat("DIRECTION"));

                map.put("lineId", rs.getString("LINE_ID"));
                map.put("speed", rs.getFloat("SPEED_GPS"));

                inOutStop = rs.getInt("INOUT_STOP");
                map.put("inout_stop", inOutStop);

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

                map.put("nbbm", BasicData.deviceId2NbbmMap.get(rs.getString("DEVICE_ID")));
                map.put("state", getService(serviceState));
                // 上下行
                map.put("upDown", getUpOrDown(serviceState));
                //路段编码
                map.put("section_code", rs.getString("SECTION_CODE"));
                list.add(map);
            }
        } catch (Exception e) {
            logger.error("", e);
        } finally {
            DBUtils_MS.close(rs, ps, conn);
        }
        return list;
    }

    public Map<String, ArrivalEntity> findArrivalByTs(int weekOfYear, Long st, Long et, String devicesInSql) {
        Map<String, ArrivalEntity> map = new HashMap<>();

        String sql = "SELECT DEVICE_ID,LINE_ID,STOP_NO,TS,UP_DOWN,IN_OUT,WEEKS_YEAR,CREATE_DATE FROM bsth_c_arrival_info where weeks_year=? and device_id in ("
                + devicesInSql + ") and ts > ? and ts < ?";
        try {
            conn = DBUtils_MS.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, weekOfYear);
            ps.setLong(2, st * 1000);
            ps.setLong(3, et * 1000);

            rs = ps.executeQuery();
            ArrivalEntity arr;
            int inOut, updown;
            String prefix, stationName, lineId;
            while (rs.next()) {
                lineId = rs.getString("LINE_ID");
                updown = rs.getInt("UP_DOWN");
                prefix = lineId + "_" + updown + "_";
                stationName = BasicData.getStationNameByCode(rs.getString("STOP_NO"), prefix);

                if (StringUtils.isEmpty(stationName))
                    continue;

                arr = new ArrivalEntity(rs.getString("DEVICE_ID"), rs.getLong("TS"), lineId,
                        updown, rs.getString("STOP_NO"), rs.getInt("IN_OUT"), rs.getLong("CREATE_DATE"),
                        rs.getInt("WEEKS_YEAR"), stationName);

                // 设备号_时间戳_进出状态 为key
                // 反转进出状态
                inOut = arr.getInOut() == 0 ? 1 : 0;
                map.put(arr.getDeviceId() + "_" + arr.getTs(), arr);
            }
        } catch (Exception e) {
            logger.error("", e);
        } finally {
            DBUtils_MS.close(rs, ps, conn);
        }
        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);
            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(history(new String[]{nbbm}, st, et));
            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 数据
            List<HistoryGps_DTOV3> list = HistoryGps_DTOV3.craete(history(new String[]{nbbm}, st, et));
            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_DTOV3 gps : list) {
                    matchRoadToGps(gps, roads);
                }*/
                //关联路段名称
                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));
        } 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(history(new String[]{nbbm}, st, et));
        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("速度");
        //数据
        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(gps.getNbbm());
            row.createCell(2).setCellValue(gps.getSection_name());
            row.createCell(3).setCellValue(gps.getLon());
            row.createCell(4).setCellValue(gps.getLat());
            row.createCell(5).setCellValue(fmtHHmmss.print(gps.getTimestamp()));
            row.createCell(6).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) {
        String deviceId = BasicData.deviceId2NbbmMap.inverse().get(nbbm);
        String sql = "select vehicle, line, up_down, lon, lat, speed,timestamp from bsth_c_speeding where vehicle=? and timestamp>? and timestamp<?";

        return GpsSpeed_DTO.create(new JdbcTemplate(DBUtils_MS.getDataSource()).queryForList(sql, deviceId, st * 1000, et * 1000));
    }

    @Override
    public List<GpsOutbound_DTO> outbounds(String nbbm, long st, long et) {
        /**
         * 如果 st 和 et 跨了周分区，也只查询st 分区的数据。
         */
        st = st * 1000;
        et = et * 1000;
        //按周分区
        Calendar weekCal = Calendar.getInstance();
        weekCal.setTimeInMillis(st);
        int weekYear = weekCal.get(Calendar.WEEK_OF_YEAR);
        //设备号
        String deviceId = BasicData.deviceId2NbbmMap.inverse().get(nbbm);
        String sql = "select vehicle,line,up_down,lon,lat,timestamp from bsth_c_outbound where weeks_year=" + weekYear + " and vehicle=? and timestamp>? and timestamp<?";
        return GpsOutbound_DTO.create(new JdbcTemplate(DBUtils_MS.getDataSource()).queryForList(sql, deviceId, st, et));
    }

    @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;
    }

    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;
    }

}