潘钊 / bsth_control · Files

package com.bsth.vehicle.gpsdata.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.bsth.util.DateUtils;
import com.bsth.util.TransGPS;
import com.bsth.util.TransGPS.Location;
import com.bsth.util.db.DBUtils_MS;
import com.bsth.vehicle.common.CommonMapped;
import com.bsth.vehicle.gpsdata.entity.ArrivalInfo;

@Service
public class GpsDataServiceImpl implements GpsDataService{

	/** 历史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());
	
	//历史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] = CommonMapped.vehicDeviceBiMap.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;
		
		//setArray 不好用，直接拼 in 语句
		String inv = "";
		for(String device : devices)
			inv += ("'" + device + "',");
		inv = inv.substring(0, inv.length() - 1);
		
		//查询到离站数据
		Map<String, ArrivalInfo> arrivalMap = findArrivalByTs(weekOfYear/*30*/, st, et, inv);
		
		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 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;
			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"));
				
				inOutStop = rs.getInt("INOUT_STOP");
				map.put("inout_stop", inOutStop);
				
				if(inOutStop != -1){
					map.put("inout_stop_info", 
							arrivalMap.get(rs.getString("DEVICE_ID") + "_" + rs.getLong("TS") + "_" + inOutStop));
				}
				map.put("nbbm", CommonMapped.vehicDeviceBiMap.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, ArrivalInfo> findArrivalByTs(int weekOfYear, Long st, Long et, String devicesInSql){
		Map<String, ArrivalInfo> 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();
			ArrivalInfo arr;
			int inOut;
			while (rs.next()) {
				arr = new ArrivalInfo(rs.getString("DEVICE_ID"), rs.getLong("TS"), rs.getString("LINE_ID"), rs.getInt("UP_DOWN")
						, rs.getString("STOP_NO"), rs.getInt("IN_OUT"), rs.getLong("CREATE_DATE") , rs.getInt("WEEKS_YEAR")
						, CommonMapped.stationCodeMap.get(rs.getString("STOP_NO")));
				
				//设备号_时间戳_进出状态  为key
				//反转进出状态
				inOut = arr.getInOut() == 0?1:0;
				map.put(arr.getDeviceId() + "_" + arr.getTs() + "_" + inOut, arr);
			}
		}catch(Exception e){
			logger.error("", e);
		}finally {
			DBUtils_MS.close(rs, ps, conn);
		}
		return map;
	}
}