王通 / gb32960_server · Files

package com.bsth.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.ThreadFactory;
import java.util.concurrent.TimeUnit;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.DisposableBean;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.bsth.entity.CarParkInfo;
import com.bsth.entity.DeviceSimMapping;
import com.bsth.entity.GridInfo;
import com.bsth.entity.HistoryArrival;
import com.bsth.entity.LineInfo;
import com.bsth.entity.SectionGrid;
import com.bsth.entity.SectionInfo;
import com.bsth.entity.SpecialStation;
import com.bsth.entity.StopInfo;
import com.bsth.geo.Point;

@Service
public class BasicDataService implements InitializingBean, DisposableBean {
	
	private final static Logger log = LoggerFactory.getLogger(BasicDataService.class);

	@Autowired
	private JdbcTemplate msJdbcTemplate;
	@Autowired
	private JdbcTemplate controlJdbcTemplate;
	private static Map<Integer, LineInfo> id_info = new HashMap<Integer, LineInfo>();
	private static List<CarParkInfo> parks = new ArrayList<CarParkInfo>();
	private static List<Integer> sline = new ArrayList<Integer>();
	private static Map<String, DeviceSimMapping> mapping = new HashMap<String, DeviceSimMapping>();
	private static Map<String, String> sim_device = new HashMap<String, String>();
	private static Map<String, String> device_sim = new HashMap<String, String>();
	private static GridInfo ginfo = new GridInfo();
	private static int year = 0;
	private ScheduledExecutorService sexec = Executors.newSingleThreadScheduledExecutor(new ThreadFactory() {
		
		@Override
		public Thread newThread(Runnable r) {
			// TODO Auto-generated method stub
			Thread t = new Thread(r);
			t.setName("BasicDataServiceScheduled");
			return t;
		}
	});
	
	public synchronized void loadBasicData() {
		Map<Integer, LineInfo> id_info = new HashMap<Integer, LineInfo>();
		List<CarParkInfo> parks = new ArrayList<CarParkInfo>();
		List<Integer> sline = new ArrayList<Integer>();
		Map<String, DeviceSimMapping> mapping = new HashMap<String, DeviceSimMapping>();
		Map<String, String> sim_device = new HashMap<String, String>();
		Map<String, String> device_sim = new HashMap<String, String>();
		
		String qline = "select a.id,in_use,line_code,name,start_station_name,start_station_first_time,start_station_end_time,end_station_name,end_station_first_time,end_station_end_time,company,branche_company,length,telephone,speed_limit,line_play_type from bsth_c_line a left join bsth_c_line_information b on a.id = b.line where a.destroy = 0";
		// 查询完整路段
		// 改 查询路段 只有分段路段了
		String qsection = "select b.id,b.section_code,b.section_name,b.section_distance,b.speed_limit,AsText(b.gsection_vector) as gsection_vector,a.line,a.line_code,a.directions,a.is_roade_speed from bsth_c_sectionroute a join bsth_c_section b on a.section = b.id where line is not null and a.destroy = 0 order by a.line,a.directions,a.sectionroute_code";
		// 查询分段路段
		//String qsection = "select b.id,b.road_code as section_code,b.road_name as section_name,0 as section_distance,b.speed as speed_limit,AsText(b.groad_vector) as gsection_vector,a.line,a.line_code,a.directions,-1 as is_roade_speed from bsth_c_sectionspeed a join bsth_c_road b on a.road = b.id order by a.line,a.directions,a.code";
		String qstop = "select b.id,b.station_cod,b.station_name,b.road_coding,b.g_lonx,b.g_laty,b.shapes_type,b.radius,AsText(b.g_polygon_grid) as g_polygon_grid,a.line,a.line_code,a.directions,a.distances from bsth_c_stationroute a join bsth_c_station b on a.station = b.id where a.destroy = 0 order by a.line,a.directions,a.station_route_code";
		String qcarpark = "select b.id,b.park_code,b.park_name,b.shapes_type,b.radius,b.g_center_point,AsText(b.g_park_point) as g_polygon_grid from bsth_c_car_park b where b.destroy = 0";
		// 查询特定线路ID
		String qsline = "select line_id from bsth_c_special_line";
		// 查询设备号和小模块映射信息
		String qmapping = "select device_id, sim, ext_enabled from bsth_c_device_sim_mapping";
		// 查询最后的到离站信息
		String qlastarr = "select device_id, line_id, stop_no, ts, up_down, up_down1, in_out, state, last_stop_no, last_up_down from bsth_c_last_arrival";
		// 查询特殊站点(回环二次进站)
		String qsstation = "select line_id, stop_no, last_stop_no from bsth_c_special_station order by line_id";
		List<LineInfo> lines = controlJdbcTemplate.query(qline, new RowMapperLineInfo());
		List<SectionInfo> secs = controlJdbcTemplate.query(qsection, new RowMapperSectionInfo());
		List<StopInfo> stops = controlJdbcTemplate.query(qstop, new RowMapperStopInfo());
		parks = controlJdbcTemplate.query(qcarpark, new RowMapperCarParkInfo());
		sline = msJdbcTemplate.queryForList(qsline, Integer.class);
		List<DeviceSimMapping> dsms = msJdbcTemplate.query(qmapping, new RowMapperDeviceSimMapping());
		List<HistoryArrival> lastarrs = msJdbcTemplate.query(qlastarr, new RowMapperHistoryArrival());
		List<SpecialStation> sstations = msJdbcTemplate.query(qsstation, new RowMapperSpecialStation());
		// 缓存设备和小模块映射信息
		for (DeviceSimMapping dsm : dsms) {
			mapping.put(dsm.getDeviceId(), dsm);
			sim_device.put(dsm.getSim(), dsm.getDeviceId());
			device_sim.put(dsm.getDeviceId(), dsm.getSim());
		}
		// 缓存线路基本信息
		for (LineInfo line : lines) {
			id_info.put(line.getLineId(), line);
		}
		int oldId = -1;
		LineInfo line = null;
		// 线路信息中添加回环站点信息
		for (SpecialStation ss : sstations) {
			if (ss.getLineId() != oldId) {
				oldId = ss.getLineId();
				line = id_info.get(oldId);
			}
			if (line != null) {
				line.getSpecailStation().add(ss);
			}
		}
		// 线路信息中添加分段的上下行路段信息
		oldId = -1;
		line = null;
		double minLon = 180, maxLon = 0, minLat = 180, maxLat = 0;
		for (SectionInfo sec : secs) {
			if (sec.getLineCode() != oldId) {
				oldId = sec.getLineCode();
				line = id_info.get(oldId);
			}
			if (line != null) {
				if (sec.getDirections() == 0) line.getSectionsUp().add(sec);
				else line.getSectionsDown().add(sec);
			}
			for (Point p : sec.getPoints()) {
				minLon = Math.min(p.getLon(), minLon);
				maxLon = Math.max(p.getLon(), maxLon);
				minLat = Math.min(p.getLat(), minLat);
				maxLat = Math.max(p.getLat(), maxLat);
			}
		}
		log.info("minLon:" + minLon + " maxLon:" + maxLon + " minLat:" + minLat + " maxLat:" + maxLat);
		int row = (int)Math.ceil((maxLon - minLon)/.001), column = (int)Math.ceil((maxLat - minLat)/.001);
		ginfo.setMinLon(minLon);
		ginfo.setMaxLon(maxLon);
		ginfo.setMinLat(minLat);
		ginfo.setMaxLat(maxLat);
		ginfo.setRow(row + 1);
		ginfo.setColumn(column + 1);
		ginfo.setGrid(new SectionGrid[row + 1][column + 1]);
		for (SectionInfo sec : secs) {
			for (Point p : sec.getPoints()) {
				int r = (int)Math.ceil((p.getLon() - minLon)/.001), c = (int)Math.ceil((p.getLat() - minLat)/.001);
				SectionGrid sg = ginfo.getGrid()[r][c];
				if (sg == null) {
					sg = new SectionGrid();
					ginfo.getGrid()[r][c] = sg;
				}
				sg.getSecs().add(sec);
			}
		}
		// 线路信息中添加上下行站点信息
		oldId = -1;
		line = null;
		for (StopInfo stop : stops) {
			if (stop.getLineCode() != oldId) {
				oldId = stop.getLineCode();
				line = id_info.get(oldId);
			}
			if (line != null) {
				if (stop.getDirections() == 0) line.getStopsUp().add(stop);
				else line.getStopsDown().add(stop);
			}
		}
		
		if (id_info.size() > 0) BasicDataService.id_info = id_info;
		if (parks.size() > 0) BasicDataService.parks = parks;
		if (sline.size() > 0) BasicDataService.sline = sline;
		if (mapping.size() > 0) BasicDataService.mapping = mapping;
		if (sim_device.size() > 0) BasicDataService.sim_device = sim_device;
		if (device_sim.size() > 0) BasicDataService.device_sim = device_sim;
	}
	
	@Override
	public void afterPropertiesSet() throws Exception {
		// TODO Auto-generated method stub
		/*try {
			jdbcTemplate.execute("create table if not exists `bsth_c_gps_info` (`city_code` int(6) NOT NULL,`industry_code` int(5) NOT NULL,`company_code` int(5) NOT NULL,`line_id` int(6) NOT NULL,`device_id` varchar(8) NOT NULL,`driver_no` int(8) NOT NULL,`carpark_serial_no` smallint(5) unsigned NOT NULL,`carpark_no` varchar(255) NOT NULL,`stop_serial_no` smallint(5) unsigned NOT NULL,`stop_no` varchar(255) NOT NULL,`lon` float(10,6) NOT NULL,`lat` float(10,6) NOT NULL,`ts` bigint(20) NOT NULL,`speed_sensor` float(7,1) NOT NULL,`speed_gps` float(7,1) NOT NULL,`direction` float(7,1) NOT NULL,`in_temp` tinyint(4) NOT NULL,`service_state` bigint(20) NOT NULL,`control_state` bigint(20) NOT NULL,`total_miles` float(8,2) NOT NULL,`water_temp` tinyint(4) NOT NULL,`oil_consumption` float(4,1) NOT NULL,`engine_speed` int(11) NOT NULL,`inout_stop` tinyint(4) NOT NULL,`days_year` smallint(4) NOT NULL,`hours_day` tinyint(4) NOT NULL,`server_ts` bigint(20),`create_date` timestamp default CURRENT_TIMESTAMP,`section_code` varchar(255) NOT NULL,KEY `idx_test` (`days_year`,`device_id`,`ts`,`line_id`,`lon`,`lat`,`inout_stop`,`server_ts`,`section_code`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 PARTITION by hash(days_year) PARTITIONS 366");
			log.info("历史gps服务初始化完成。。。");
		} catch(Exception e) {
			//e.printStackTrace();
			log.warn("历史gps服务初始化异常。。。", e);
		}
		try {
			jdbcTemplate.execute("create table if not exists `bsth_c_arrival_info` (`device_id` varchar(8) NOT NULL,`line_id` int(11) NOT NULL,`stop_no` varchar(32) NOT NULL,`ts` bigint(20) NOT NULL,`up_down` int(11) NOT NULL,`in_out` int(11) NOT NULL,`create_date` bigint(20) NOT NULL,`weeks_year` tinyint(4) NOT NULL,`create_timestamp` bigint(20),KEY `idx_test` (`weeks_year`,`device_id`,`ts`,`create_date`,`create_timestamp`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 PARTITION by hash(weeks_year) PARTITIONS 53");
			log.info("历史到离站服务初始化完成。。。");
		} catch (Exception e) {
			log.warn("历史到离站服务初始化异常。。。", e);
		}*/
		try {
			loadBasicData();
		} catch (Exception e) {
			log.error("BasicDataService初始化时加载基础数据异常", e);
		}
		
		// 开启调度
		sexec.scheduleAtFixedRate(new Runnable() {
			
			@Override
			public void run() {
				// TODO Auto-generated method stub
				try {
					loadBasicData();
				} catch (Exception e) {
					log.error("BasicDataService加载基础数据异常", e);
				}
			}
		}, 1, 1, TimeUnit.HOURS);
		sexec.scheduleAtFixedRate(new SplitTableChecker(), 0, 10, TimeUnit.SECONDS);
	}
	
	final class RowMapperSectionInfo implements RowMapper<SectionInfo> {
		
		private final Pattern p = Pattern.compile("(\\d+(\\.\\d+)?) (\\d+(\\.\\d+)?)");
		
		@Override
		public SectionInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
			// TODO Auto-generated method stub
			SectionInfo section = new SectionInfo();
			section.setId(rs.getInt("id"));
			section.setSectionCode(rs.getString("section_code"));
			section.setSectionName(rs.getString("section_name"));
			section.setSectionDistance(rs.getDouble("section_distance"));
			section.setSpeedLimit(rs.getDouble("speed_limit"));
			List<Point> points = new ArrayList<Point>();
			String gstr = rs.getString("gsection_vector");
			if (gstr != null) {
				Matcher m = p.matcher(gstr);
				while (m.find()) {
					points.add(new Point(Float.parseFloat(m.group(1)), Float.parseFloat(m.group(3))));
				}
			}
			section.setPoints(points);
			section.setLineId(rs.getInt("line"));
			section.setLineCode(rs.getInt("line_code"));
			section.setDirections(rs.getInt("directions"));
			section.setIsRoadeSpeed(rs.getInt("is_roade_speed"));
			return section;
		}
		
	}

	final class RowMapperLineInfo implements RowMapper<LineInfo> {

		@Override
		public LineInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
			// TODO Auto-generated method stub
			LineInfo line = new LineInfo();
			line.setId(rs.getInt("id"));
			line.setInUse(rs.getInt("in_use"));
			line.setLineId(rs.getInt("line_code"));
			line.setLineName(rs.getString("name"));
			//line.setStartStation(rs.getInt("start_station"));
			line.setStartStationName(rs.getString("start_station_name"));
			line.setStartStationFirstTime(rs.getString("start_station_first_time"));
			line.setStartStationEndTime(rs.getString("start_station_end_time"));
			//line.setEndStation(rs.getInt("end_station"));
			line.setEndStationName(rs.getString("end_station_name"));
			line.setEndStationFirstTime(rs.getString("end_station_first_time"));
			line.setEndStationEndTime(rs.getString("end_station_end_time"));
			line.setCompany(rs.getString("company"));
			line.setBrancheCompany(rs.getString("branche_company"));
			line.setTelephone(rs.getString("telephone"));
			line.setSpeedLimit(rs.getDouble("speed_limit"));
			line.setLinePlayType(rs.getInt("line_play_type"));
			return line;
		}
		
	}
	
	final class RowMapperStopInfo implements RowMapper<StopInfo> {

		private final Pattern p = Pattern.compile("(\\d+(\\.\\d+)?) (\\d+(\\.\\d+)?)");
		@Override
		public StopInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
			// TODO Auto-generated method stub
			StopInfo stop = new StopInfo();
			stop.setId(rs.getInt("id"));
			stop.setStationCod(rs.getString("station_cod"));
			stop.setStationName(rs.getString("station_name"));
			//stop.setStationType(rs.getString("station_type"));
			stop.setRoadCoding(rs.getString("road_coding"));
			float lon = rs.getFloat("g_lonx");
			float lat = rs.getFloat("g_laty");
			stop.setPoint(new Point(lon, lat));
			stop.setLineId(rs.getInt("line"));
			stop.setLineCode(rs.getInt("line_code"));
			stop.setDirections(rs.getInt("directions"));
			stop.setShapesType(rs.getString("shapes_type"));
			stop.setRadius(rs.getInt("radius"));
			List<Point> points = new ArrayList<Point>();
			String gstr = rs.getString("g_polygon_grid");
			if (gstr != null) {
				Matcher m = p.matcher(gstr);
				while (m.find()) {
					points.add(new Point(Float.parseFloat(m.group(1)), Float.parseFloat(m.group(3))));
				}
			}
			stop.setPoints(points);
			stop.setDistances(rs.getDouble("distances")*1000);
			return stop;
		}
		
	}
	
	final class RowMapperCarParkInfo implements RowMapper<CarParkInfo> {

		private final Pattern p = Pattern.compile("(\\d+(\\.\\d+)?) (\\d+(\\.\\d+)?)");
		@Override
		public CarParkInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
			// TODO Auto-generated method stub
			CarParkInfo park = new CarParkInfo();
			park.setId(rs.getInt("id"));
			park.setParkCode(rs.getString("park_code"));
			park.setParkName(rs.getString("park_name"));
			String gstr = rs.getString("g_center_point");
			if (gstr != null) {
				Matcher m = p.matcher(gstr);
				while (m.find()) {
					park.setPoint(new Point(Float.parseFloat(m.group(1)), Float.parseFloat(m.group(3))));
				}
			}
			park.setShapesType(rs.getString("shapes_type"));
			park.setRadius(rs.getInt("radius"));
			List<Point> points = new ArrayList<Point>();
			gstr = rs.getString("g_polygon_grid");
			if (gstr != null) {
				Matcher m = p.matcher(gstr);
				while (m.find()) {
					points.add(new Point(Float.parseFloat(m.group(1)), Float.parseFloat(m.group(3))));
				}
			}
			park.setPoints(points);
			return park;
		}
		
	}
	
	final class RowMapperDeviceSimMapping implements RowMapper<DeviceSimMapping> {

		@Override
		public DeviceSimMapping mapRow(ResultSet rs, int rowNum) throws SQLException {
			// TODO Auto-generated method stub
			DeviceSimMapping dsm = new DeviceSimMapping();
			dsm.setDeviceId(rs.getString("device_id"));
			dsm.setSim(rs.getString("sim"));
			dsm.setExtEnabled(rs.getInt("ext_enabled"));
			
			return dsm;
		}
		
	}
	
	final class RowMapperHistoryArrival implements RowMapper<HistoryArrival> {

		@Override
		public HistoryArrival mapRow(ResultSet rs, int rowNum) throws SQLException {
			// TODO Auto-generated method stub
			HistoryArrival ha = new HistoryArrival();
			ha.setDeviceId(rs.getString("device_id"));
			ha.setLineId(rs.getInt("line_id"));
			ha.setStopNo(rs.getString("stop_no"));
			ha.setTimestamp(rs.getLong("ts"));
			ha.setUpDown(rs.getInt("up_down"));
			ha.setUpDown1(rs.getInt("up_down1"));
			ha.setInOut(rs.getInt("in_out"));
			ha.setState(rs.getInt("state") == 1 ? true : false);
			ha.setLastStopNo(rs.getString("last_stop_no"));
			ha.setLastUpDown(rs.getInt("last_up_down"));
			
			return ha;
		}
		
	}
	
	final class RowMapperSpecialStation implements RowMapper<SpecialStation> {

		@Override
		public SpecialStation mapRow(ResultSet rs, int rowNum) throws SQLException {
			// TODO Auto-generated method stub
			SpecialStation ss = new SpecialStation();
			ss.setLineId(rs.getInt("line_id"));
			ss.setStopNo(rs.getString("stop_no"));
			ss.setLastStopNo(rs.getString("last_stop_no"));
			
			return ss;
		}
		
	}
	
	final class SplitTableChecker implements Runnable {
		
		private long nextTime = 0;
		
		@Override
		public void run() {
			// TODO Auto-generated method stub
			try {
				long now = System.currentTimeMillis();
				Calendar c = Calendar.getInstance();
				c.setTimeInMillis(now);
				if (nextTime < now) {
					nextTime = getNextTime(now);
					year = c.get(Calendar.YEAR);
				}
			} catch (Exception e) {
				log.error("SplitTableChecker schedule exception", e);
			}
		}
		
	}
	
	private long getNextTime(long now) {
		Calendar c = Calendar.getInstance();
		c.setTimeInMillis(now);
		c.add(Calendar.YEAR, 1);
		c.set(Calendar.MONTH, 0);
		c.set(Calendar.DAY_OF_MONTH, 1);
		c.set(Calendar.HOUR_OF_DAY, 0);
		c.set(Calendar.MINUTE, 0);
		c.set(Calendar.SECOND, 0);
		c.set(Calendar.MILLISECOND, 0);
		
		return c.getTimeInMillis();
	}
	
	public static LineInfo getLineById(int id) {
		return id_info.get(id);
	}
	
	public static List<CarParkInfo> getCarParks() {
		return parks;
	}
	
	public static GridInfo getGinfo() {
		return ginfo;
	}
	
	public static List<Integer> getSpecialLine() {
		return sline;
	}
	
	public static DeviceSimMapping getMappingByDeviceId(String deviceId) {
		return mapping.get(deviceId);
	}
	
	public static String getDeviceIdBySim(String sim) {
		return sim_device.get(sim);
	}
	
	public static String getSimByDeviceId(String deviceId) {
		return device_sim.get(deviceId);
	}
	
	public static int getYear() {
		return year;
	}

	@Override
	public void destroy() throws Exception {
		// TODO Auto-generated method stub
		sexec.shutdown();
	}
}