潘钊 / bsth_control · Files

package com.bsth.service.gps;

import com.bsth.common.ResponseCode;
import com.bsth.data.BasicData;
import com.bsth.data.arrival.ArrivalEntity;
import com.bsth.data.gpsdata.GpsEntity;
import com.bsth.data.gpsdata.GpsRealData;
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.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.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 java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
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;

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

    @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/* 30 */, st, et, inv);

        String sql = "select DEVICE_ID,LON,LAT,TS,INOUT_STOP,SERVICE_STATE ,STOP_NO,DIRECTION,LINE_ID,SPEED_GPS from bsth_c_gps_info where days_year=? and device_id in ("
                + inv + ") and ts > ? and ts < ?";
        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 upDown, inOutStop;
            ArrivalEntity arrival;
            while (rs.next()) {
                upDown = getUpOrDown(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("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", 0);
                // 上下行
                map.put("upDown", upDown);
                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);

                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();
            //排序字段
            Field orderField = null;
            //参与过滤的字段
            List<Field> fs = new ArrayList<>();
            for (Field f : fields) {
                f.setAccessible(true);
                if (map.containsKey(f.getName()))
                    fs.add(f);

                if (f.getName().equals(order))
                    orderField = f;
            }
            //过滤数据
            for (GpsEntity gps : list) {
                if (fieldEquals(fs, gps, map))
                    rs.add(gps);
            }

            //排序
/*            if (null != orderField)
                sortGpsList(orderField, rs);*/
            //时间戳排序
            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) {
        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;
            }

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

            if(list.size() == 0){
                rs.put("status", ResponseCode.ERROR);
                rs.put("msg", "缺少模板数据！！！");
                return rs;
            }
            Map<String, Object> fs = list.get(0);
            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) + ")";
            sqlValues = sqlValues.substring(0, sqlValues.length() - 1) + ")";
            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();
            ps.clearBatch();

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

    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 {
            for (Field f : fs) {
                if (StringUtils.isEmpty(map.get(f.getName()).toString()))
                    continue;

                if (f.get(obj) == null || f.get(obj).toString().indexOf(map.get(f.getName()).toString()) == -1)
                    return false;
            }
        } catch (Exception e) {
            logger.error("", e);
            return false;
        }
        return true;
    }
}