潘钊 / bsth_control · Files

package com.bsth.repository;

import java.util.List;
import java.util.Map;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.bsth.entity.Line;
import com.bsth.entity.StationRoute;

/**
 * 
 * @Interface: StationRouteRepository(站点路由Repository数据持久层接口)
 * 
 * @Extends : BaseRepository
 * 
 * @Description: TODO(站点路由Repository数据持久层接口)
 * 
 * @Author bsth@lq
 * 
 * @Date 2016年5月03日 上午9:21:17
 *
 * @Version 公交调度系统BS版 0.1
 * 
 */

@Repository
public interface StationRouteRepository extends BaseRepository<StationRoute, Integer> {
	
	@Query(value = "SELECT  a.`stationRoute.id`," +
						    "a.`stationRoute.line`," +
						    "a.`stationRoute.station`," +
							"a.`stationRoute.stationName`," +
							"a.`stationRoute.stationRouteCode`," +
							"a.`stationRoute.lineCode`," +
							"a.`stationRoute.stationMark`," +
							"a.`stationRoute.outStationNmber`," +
							"a.`stationRoute.directions`," +
							"a.`stationRoute.distances`," +
							"a.`stationRoute.toTime`," +
							"a.`stationRoute.firstTime`," +
							"a.`stationRoute.endTime`," +
							"a.`stationRoute.descriptions`," +
							"a.`stationRoute.versions`," +
							"b.id AS 'station.id'," +
							"b.station_cod AS 'station.stationCod'," +
							"b.station_name AS 'station.stationName'," +
							"b.road_coding AS 'station.roadCoding'," +
							"b.db_type AS 'station.dbType'," +
							"b.b_jwpoints AS 'station.bJwpoints'," +
							"b.g_lonx AS 'station.gLonx'," +
							"b.g_lonx AS 'station.gLaty'," +
							"b.x AS 'station.x'," +
							"b.y AS 'station.y'," +
							"b.shapes_type AS 'station.shapesType'," +
							"b.radius AS 'station.radius'," +
							"ST_AsText(b.g_polygon_grid) AS 'station.gPolygonGrid'," +
							"ST_AsText(b.b_polygon_grid) AS 'station.bPolygonGrid'," +
							"b.destroy AS 'station.destroy'," +
							"b.versions AS 'station.versions'," +
							"b.descriptions AS 'station.descriptions', " +
							"a.`stationRoute.industryCode` " +
							" FROM (" +
								"SELECT r.id AS 'stationRoute.id'," +
								" r.line AS 'stationRoute.line'," +
								"r.station AS 'stationRoute.station'," +
								"r.station_name AS 'stationRoute.stationName'," +
								"r.station_route_code as 'stationRoute.stationRouteCode'," +
								"r.line_code AS 'stationRoute.lineCode'," +
								"r.station_mark AS 'stationRoute.stationMark'," +
								"r.out_station_nmber AS 'stationRoute.outStationNmber'," +
								"r.directions AS 'stationRoute.directions'," +
								"r.distances AS 'stationRoute.distances'," +
								"r.to_time AS 'stationRoute.toTime'," +
								"r.first_time AS 'stationRoute.firstTime'," +
								"r.end_time AS 'stationRoute.endTime'," +
								"r.descriptions AS 'stationRoute.descriptions'," +
								"r.versions AS 'stationRoute.versions', " +
								"r.industry_code AS 'stationRoute.industryCode' " +
							" FROM bsth_c_stationroute r  WHERE  r.line = ?1 and r.directions = ?2 and r.destroy=0) a " +
							"LEFT JOIN bsth_c_station b " +
							"ON a.`stationRoute.station` = b.id ORDER BY a.`stationRoute.stationRouteCode` ASC", nativeQuery=true)
	List<Object[]> findPoints(int line,int directions);
	
	@Query("select r from StationRoute r where r.line.id=?1 and r.destroy=0 order by r.directions ASC ,r.stationRouteCode ASC")
	// @Query(value = "SELECT * from bsth_c_stationroute line = ?1 and destroy=0 bsth_c_station ORDER BY directions ASC, stationRouteCode ASC", nativeQuery=true)
	List<StationRoute> findStationExport(int line);
	
	/**
	 * @Description :TODO(查询线路某方向下的站点序号与类型)
	 * 
	 * @param map <lineId:线路ID； direction：方向；stationRouteCode：站点编码>
	 * 
	 * @return List<Map<String, Object>> 
	 */
	@Query(value = "select t.station_route_code,t.station_mark from bsth_c_stationroute t where " +
			" t.station_route_code =(" +
				"select  MAX(station_route_code) as stationRouteCode   from bsth_c_stationroute r  WHERE  " +
					"r.line=?1 and r.directions =?2 and station_route_code< ?3 and r.destroy = 0  ) and t.line=?1 and t.directions = ?2 AND t.destroy = 0", nativeQuery=true)
	List<Object[]> findUpStationRouteCode(Integer lineId,Integer direction,Integer stationRouteCode);

	/**
	 * @Description :TODO(查询下个站点)
	 *
	 * @param map <lineId:线路ID； direction：方向；stationRouteCode：站点编码>
	 *
	 * @return List<Map<String, Object>>
	 */
	@Query(value = "SELECT a.stationRouteLine," +
			" a.stationRouteStation," +
			" a.stationRouteCode," +
			" a.stationRouteLIneCode," +
			" a.stationRouteStationMark," +
			" a.stationOutStationNmber," +
			" a.stationRoutedirections," +
			" a.stationRouteDistances," +
			" a.stationRouteToTime," +
			" a.staitonRouteFirstTime," +
			" a.stationRouteEndTime," +
			" a.stationRouteDescriptions," +
			" a.stationRouteDestroy," +
			" a.stationRouteVersions," +
			" a.stationRouteCreateBy," +
			" a.stationRouteCreateDate," +
			" a.stationRouteUpdateBy," +
			" a.stationRouteUpdateDate," +
			" b.id AS stationId," +
			" b.station_cod AS stationCode," +
			" a.stationRouteName," +
			" b.road_coding AS stationRoadCoding," +
			" b.db_type AS stationDbType," +
			" b.b_jwpoints AS stationJwpoints," +
			" b.g_lonx AS stationGlonx," +
			" b.g_laty AS stationGlaty," +
			" b.x AS stationX," +
			" b.y AS stationY," +
			" ST_AsText(b.b_polygon_grid) as stationBPolyonGrid," +
			" ST_AsText(b.g_polygon_grid) AS stationGPloyonGrid, " +
			" b.destroy AS stationDestroy," +
			" b.radius AS stationRadius," +
			" b.shapes_type AS stationShapesType," +
			" b.versions AS stationVersions," +
			" b.descriptions AS sttationDescriptions," +
			" b.create_by AS stationCreateBy," +
			" b.create_date AS stationCreateDate," +
			" b.update_by AS stationUpdateBy," +
			" b.update_date AS stationUpdateDate," +
			" a.stationRouteId, " +
			" b.station_name as zdmc, " +
			" a.industryCode"+
			" FROM " +
			"( SELECT s.id AS stationRouteId," +
				" s.line AS stationRouteLine," +
				" s.station as stationRouteStation," +
				" s.station_name AS stationRouteName," +
				" s.station_route_code as stationRouteCode," +
				" s.industry_code as industryCode," +
				" s.line_code AS stationRouteLIneCode," +
				" s.station_mark AS stationRouteStationMark," +
				" s.out_station_nmber AS stationOutStationNmber," +
				" s.directions AS stationRoutedirections," +
				" s.distances AS stationRouteDistances," +
				" s.to_time AS stationRouteToTime," +
				" s.first_time AS staitonRouteFirstTime," +
				" s.end_time AS stationRouteEndTime," +
				" s.descriptions AS stationRouteDescriptions," +
				" s.destroy AS stationRouteDestroy," +
				" s.versions AS stationRouteVersions," +
				" s.create_by AS stationRouteCreateBy," +
				" s.create_date AS stationRouteCreateDate," +
				" s.update_by AS stationRouteUpdateBy," +
				" s.update_date AS stationRouteUpdateDate FROM bsth_c_stationroute s WHERE s.station_route_code =(" +
				"select  MIN(station_route_code) as stationRouteCode   from bsth_c_stationroute r  WHERE  " +
				"r.line=?1 and r.directions =?2 and station_route_code > ?3 and r.destroy = 0  ) and s.line=?1 and s.directions = ?2 AND s.destroy = 0) a " +
			" LEFT JOIN  bsth_c_station b ON a.stationRouteStation = b.id", nativeQuery=true)
	List<Object[]> findDownStationRoute(Integer id,Integer direction,Integer stationRouteCode);

	/**
	 * @Description :TODO(站点中心点坐标查询)
	 * 
	 * @param map <lineId:线路ID； direction：方向>
	 * 
	 * @return List<Object[]>
	 */
	/*@Query(value = "SELECT s.b_jwpoints,s.station_name FROM (" +
						"SELECT b.station FROM bsth_c_stationroute b where b.line =?1 and b.directions = ?2 and b.destroy=0) r " +
				   "LEFT JOIN bsth_c_station s on r.station = s.id", nativeQuery=true)*/
	@Query(value = "SELECT s.b_jwpoints,r.station_name,r.station_route_code FROM (" +
			"SELECT b.station,b.station_route_code,b.station_name FROM bsth_c_stationroute b where b.line =?1 and b.directions = ?2 and b.destroy=0) r " +
	   "LEFT JOIN bsth_c_station s on r.station = s.id order by r.station_route_code asc", nativeQuery=true)
	List<Object[]> getSelectStationRouteCenterPoints(Integer lineId,Integer direction);
	
	/**
	 * @Description :TODO(撤销站点)
	 * 
	 * @param map <lineId:线路ID； destroy：是否撤销（0：否；1：是）>
	 * 
	 */
	@Transactional
    @Modifying
	@Query(value="UPDATE bsth_c_stationroute SET " +
				"destroy = ?2 WHERE id = ?1", nativeQuery=true)
	void stationRouteIsDestroyUpd(Integer stationRouteId, Integer destroy);
	
	
	/**
	 * @Description : TODO(根据线路ID生成行单)
	 * 
	 * @param  lineId:线路ID 
	 * 
	 * @return List<Object[]>：{[0]：g_lonx（GPS经度）；[1]：g_laty（GPS纬度）；[2]：b_jwpoints（百度经纬度坐标）
	 * 							
	 * 							[3]：station_mark（站点类型）；[4]：station_route_code（站点序号）；[5]：station_cod（站点编码）；
	 * 
	 * 							[6]：distances（站点距离）；[7]：station_name（站点名称）；[8]：directions（方向）}
	 */
	@Query(value = "SELECT * FROM ("+
				   "SELECT b.g_lonx," +
				   "b.g_laty,b.b_jwpoints," +
				   "a.station_mark," +
				   "a.station_route_code," +
				   "b.station_cod," +
				   "a.distances,"+
				   "s.station_name," +
				   "a.directions FROM (SELECT " +
				   "s.station_mark," +
				   "s.station_route_code," +
				   "s.directions," +
				   "s.distances,"+
				   "s.station FROM bsth_c_stationroute s where  s.line = ?1 and s.destroy=0) a " +
				   "LEFT JOIN bsth_c_station b " +
				   " on a.station = b.id ORDER BY a.directions ASC ) k ORDER BY k.directions,k.station_route_code ASC", nativeQuery=true)
	List<Object[]> usingSingle(Integer lineId);
	
	/**
	 * @Description : TODO(根据站点路由Id查询详情)
	 * 
	 * @param  id:站点路由ID 
	 * 
	 * @return List<Object[]>
	 */
	@Query(value = "SELECT a.stationRouteLine," +
			" a.stationRouteStation," +
			" a.stationRouteCode," +
			" a.stationRouteLIneCode," +
			" a.stationRouteStationMark," +
			" a.stationOutStationNmber," +
			" a.stationRoutedirections," +
			" a.stationRouteDistances," +
			" a.stationRouteToTime," +
			" a.staitonRouteFirstTime," +
			" a.stationRouteEndTime," +
			" a.stationRouteDescriptions," +
			" a.stationRouteDestroy," +
			" a.stationRouteVersions," +
			" a.stationRouteCreateBy," +
			" a.stationRouteCreateDate," +
			" a.stationRouteUpdateBy," +
			" a.stationRouteUpdateDate," +
			" b.id AS stationId," +
			" b.station_cod AS stationCode," +
			" a.stationRouteName," +
			" b.road_coding AS stationRoadCoding," +
			" b.db_type AS stationDbType," +
			" b.b_jwpoints AS stationJwpoints," +
			" b.g_lonx AS stationGlonx," +
			" b.g_laty AS stationGlaty," +
			" b.x AS stationX," +
			" b.y AS stationY," +
			" ST_AsText(b.b_polygon_grid) as stationBPolyonGrid," +
			" ST_AsText(b.g_polygon_grid) AS stationGPloyonGrid, " +
			" b.destroy AS stationDestroy," +
			" b.radius AS stationRadius," +
			" b.shapes_type AS stationShapesType," +
			" b.versions AS stationVersions," +
			" b.descriptions AS sttationDescriptions," +
			" b.create_by AS stationCreateBy," +
			" b.create_date AS stationCreateDate," +
			" b.update_by AS stationUpdateBy," +
			" b.update_date AS stationUpdateDate," +
			" a.stationRouteId,b.station_name as zdmc, " +
			" a.industryCode "+
			" FROM " +
			"( SELECT s.id AS stationRouteId," +
				" s.line AS stationRouteLine," +
				" s.station as stationRouteStation," +
				" s.station_name AS stationRouteName," +
				" s.station_route_code as stationRouteCode," +
				" s.industry_code as industryCode," +
				" s.line_code AS stationRouteLIneCode," +
				" s.station_mark AS stationRouteStationMark," +
				" s.out_station_nmber AS stationOutStationNmber," +
				" s.directions AS stationRoutedirections," +
				" s.distances AS stationRouteDistances," +
				" s.to_time AS stationRouteToTime," +
				" s.first_time AS staitonRouteFirstTime," +
				" s.end_time AS stationRouteEndTime," +
				" s.descriptions AS stationRouteDescriptions," +
				" s.destroy AS stationRouteDestroy," +
				" s.versions AS stationRouteVersions," +
				" s.create_by AS stationRouteCreateBy," +
				" s.create_date AS stationRouteCreateDate," +
				" s.update_by AS stationRouteUpdateBy," +
				" s.update_date AS stationRouteUpdateDate FROM bsth_c_stationroute s WHERE s.id = ?1 ) a " +
			" LEFT JOIN  bsth_c_station b ON a.stationRouteStation = b.id", nativeQuery=true)
	List<Object[]> findStationRouteInfo(Integer id);

	/**
	 * @Description : TODO(根据站点路由Id查询详情)
	 *
	 * @param  id:站点路由ID
	 *
	 * @return List<Object[]>
	 */
	@Query(value = "SELECT a.stationRouteLine," +
			" a.stationRouteStation," +
			" a.stationRouteCode," +
			" a.stationRouteLIneCode," +
			" a.stationRouteStationMark," +
			" a.stationOutStationNmber," +
			" a.stationRoutedirections," +
			" a.stationRouteDistances," +
			" a.stationRouteToTime," +
			" a.staitonRouteFirstTime," +
			" a.stationRouteEndTime," +
			" a.stationRouteDescriptions," +
			" a.stationRouteDestroy," +
			" a.stationRouteVersions," +
			" a.stationRouteCreateBy," +
			" a.stationRouteCreateDate," +
			" a.stationRouteUpdateBy," +
			" a.stationRouteUpdateDate," +
			" b.id AS stationId," +
			" b.station_cod AS stationCode," +
			" a.stationRouteName," +
			" b.road_coding AS stationRoadCoding," +
			" b.db_type AS stationDbType," +
			" b.b_jwpoints AS stationJwpoints," +
			" b.g_lonx AS stationGlonx," +
			" b.g_laty AS stationGlaty," +
			" b.x AS stationX," +
			" b.y AS stationY," +
			" ST_AsText(b.b_polygon_grid) as stationBPolyonGrid," +
			" ST_AsText(b.g_polygon_grid) AS stationGPloyonGrid, " +
			" b.destroy AS stationDestroy," +
			" b.radius AS stationRadius," +
			" b.shapes_type AS stationShapesType," +
			" b.versions AS stationVersions," +
			" b.descriptions AS sttationDescriptions," +
			" b.create_by AS stationCreateBy," +
			" b.create_date AS stationCreateDate," +
			" b.update_by AS stationUpdateBy," +
			" b.update_date AS stationUpdateDate," +
			" a.stationRouteId," +
			"b.station_name as zdmc, "+
			"a.industryCode "+
			" FROM ( SELECT s.id AS stationRouteId," +
				" s.line AS stationRouteLine," +
				" s.station as stationRouteStation," +
				" s.station_name AS stationRouteName," +
				" s.station_route_code as stationRouteCode," +
				" s.industry_code as industryCode," +
				" s.line_code AS stationRouteLIneCode," +
				" s.station_mark AS stationRouteStationMark," +
				" s.out_station_nmber AS stationOutStationNmber," +
				" s.directions AS stationRoutedirections," +
				" s.distances AS stationRouteDistances," +
				" s.to_time AS stationRouteToTime," +
				" s.first_time AS staitonRouteFirstTime," +
				" s.end_time AS stationRouteEndTime," +
				" s.descriptions AS stationRouteDescriptions," +
				" s.destroy AS stationRouteDestroy," +
				" s.versions AS stationRouteVersions," +
				" s.create_by AS stationRouteCreateBy," +
				" s.create_date AS stationRouteCreateDate," +
				" s.update_by AS stationRouteUpdateBy," +
				" s.update_date AS stationRouteUpdateDate FROM bsth_c_stationroute s WHERE s.line = ?1 and s.directions = ?2 and s.destroy = 0) a " +
			" LEFT JOIN  bsth_c_station b ON a.stationRouteStation = b.id order by a.stationRouteCode", nativeQuery=true)
	List<Object[]> getStationRouteList(Integer lineId, Integer dir);

	List<StationRoute> findByLine(Line line);
	
	@EntityGraph(value = "stationRoute_station", type = EntityGraph.EntityGraphType.FETCH)
	@Query("select s from StationRoute s where s.destroy=0")
	List<StationRoute> findAllEffective();

	@EntityGraph(value = "stationRoute_station", type = EntityGraph.EntityGraphType.FETCH)
    @Override
	Page<StationRoute> findAll(Specification<StationRoute> spec, Pageable pageable);

	@EntityGraph(value = "stationRoute_station", type = EntityGraph.EntityGraphType.FETCH)
	@Override
	List<StationRoute> findAll(Specification<StationRoute> spec);

	@EntityGraph(value = "stationRoute_station", type = EntityGraph.EntityGraphType.FETCH)
	@Override
	List<StationRoute> findAll(Specification<StationRoute> spec, Sort sort);

	@EntityGraph(value = "stationRoute_station", type = EntityGraph.EntityGraphType.FETCH)
	@Override
	@Query(value = "select r from StationRoute r where r.destroy=0")
	List<StationRoute> findAll();

	@Query("select new map(sr.station.id as stationid, sr.stationName as stationname) from StationRoute sr where sr.line.id=?1 and sr.directions=?2")
    List<Map<String, Object>> findStations(Integer xlid, Integer xldir);
    
	@Query("select r from StationRoute r where r.lineCode=?1 and r.directions=?2 and r.destroy=0 order by r.stationRouteCode")
	List<StationRoute> findByLine(String lineCode, int updown);
	
	@Query("select r from StationRoute r where r.line.id=?1 and r.directions=?2 and r.destroy=0 order by r.stationRouteCode")
	List<StationRoute> findByLine(Integer lineId, Integer dir);
	
	@EntityGraph(value = "stationRoute_station", type = EntityGraph.EntityGraphType.FETCH)
	@Query("select s from StationRoute s where s.destroy=0 and s.lineCode=?1")
	List<StationRoute> findByLineCode(String lineCode);

	@Query("SELECT new map(" +
			"lineCode as lineCode,directions as directions,stationName as stationName,stationCode as stationCode," +
			"line.linePlayType as linePlayType,s.stationMark as stationMark) " +
			"FROM " +
			"StationRoute s " +
			"WHERE " +
			"s.destroy = 0 " +
			"and s.lineCode in(select lineCode from Line where inUse = 1) " +
			"ORDER BY " +
			"lineCode,directions,stationRouteCode")
	List<Map<String, String>> findLineWithYgcAndInuse();

	@Query("SELECT new map(" +
			"lineCode as lineCode,directions as directions,stationName as stationName,stationCode as stationCode," +
			"line.linePlayType as linePlayType,s.stationMark as stationMark) " +
			"FROM " +
			"StationRoute s " +
			"WHERE " +
			"s.destroy = 0 " +
			"ORDER BY " +
			"lineCode,directions,stationRouteCode")
	List<Map<String, String>> findAllLineWithYgc();

	@Query("SELECT new map(" +
			"lineCode as lineCode,directions as directions,stationName as stationName,stationCode as stationCode," +
			"line.linePlayType as linePlayType,s.stationMark as stationMark) " +
			"FROM " +
			"StationRoute s " +
			"WHERE " +
			"s.destroy = 0 and s.lineCode = ?1 " +
			"ORDER BY " +
			"lineCode,directions,stationRouteCode")
	List<Map<String, String>> findLineWithYgcByLine(String lineCode);
	
    @Modifying
    @Query(value="update bsth_c_stationroute set directions = case directions when 1 then 0 when 0 then 1 end where line = ?1 ", nativeQuery=true)
	void stationRouteDir(Integer line);
    
    @Modifying
    @Query(value="update bsth_c_stationroute set distances =?2 where id = ?1 ", nativeQuery=true)
	void upddis(Integer id,Double dis);
    
    @Modifying
	@Query(value="UPDATE bsth_c_stationroute  set station_route_code = (station_route_code+10) where line = ?1  and directions = ?2 and station_route_code >=?3 and destroy = 0", nativeQuery=true)
	void stationUpdStationRouteCode(Integer line,Integer dir,Integer routeCod);
    
    /**
     * 更新路线前撤销线路原有站点
     * 
     * @param line
     * @param dir
     */
    @Modifying
    @Query(value="UPDATE bsth_c_stationroute  set destroy = 1 where line = ?1  and directions = ?2", nativeQuery=true)
    void stationRouteUpdDestroy(Integer line,Integer dir);
    
    @Modifying
   	@Query(value="UPDATE bsth_c_stationroute set destroy = 1 where id = ?1", nativeQuery=true)
   	void stationRouteIsDestroyUpdBatch(Integer ids);
    
    /**
     * 
     * 
     * @param line
     * @param dir
     */
    @Modifying
    @Query(value="insert into (select * from bsth_c_stationroute_cache where line = ?1  and directions = ?2) bsth_c_stationroute", nativeQuery=true)
    void stationRouteUpdate(Integer line,Integer dir);

    // 更具线路批量撤销
    @Modifying
    @Query(value="UPDATE StationRoute sr set sr.destroy = 1 where sr.line.id = ?1  and sr.lineCode = ?2")
	void batchUpdate(Integer lineId, String lineCode);

    // 批量删除    
    @Modifying
    @Query(value="delete from StationRoute sr where sr.line.id = ?1  and sr.lineCode = ?2")
	void batchDelete(Integer lineId, String lineCode);


    // 批量修改站点行业编码
	@Modifying
	@Query(value="update bsth_c_stationroute set industry_code =?2 where id = ?1 ", nativeQuery=true)
	void updIndustryCode(Integer id,String IndustryCode);
}