潘钊 / 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`," +
							"a.stationShapesType," +
							"a.stationRadius," +
							"a.stationGPloyonGrid, " +
							"a.stationBPolyonGrid," +
							"b.id AS 'station.id'," +
							"b.station_code AS 'station.stationCod'," +
							"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.descriptions AS 'station.descriptions' FROM (" +
								"SELECT r.id AS 'stationRoute.id'," +

								" ST_AsText(r.b_polygon_grid) as stationBPolyonGrid," +
								" ST_AsText(r.g_polygon_grid) AS stationGPloyonGrid, " +
								" r.radius AS stationRadius," +
								" r.shapes_type AS stationShapesType," +

								" 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' 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 <lineCode:线路编码； direction：方向；stationRouteCode：站点编码>
	 *
	 * @return List<Map<String, Object>>
	 */
	@Query(value = "SELECT a.stationRouteName," +
			" a.stationRouteId, "+
			" b.station_code AS stationCode," +
			" b.b_jwpoints AS stationJwpoints," +
			" b.g_lonx AS stationGlonx," +
			" b.g_laty AS stationGlaty" +
			" FROM ( SELECT s.id AS stationRouteId," +
			" s.station_name AS stationRouteName, s.station AS stationId FROM bsth_c_ls_stationroute s WHERE s.station_route_code =(" +
			"select  MIN(station_route_code) as stationRouteCode   from bsth_c_ls_stationroute r  WHERE  " +
			"r.line_code=?1 and r.directions =?2 and station_route_code > ?3 and r.destroy = 0  ) and s.line_code=?1 and s.directions = ?2 AND s.destroy = 0) a " +
			" LEFT JOIN  bsth_c_station b ON a.stationId = b.id", nativeQuery=true)
	List<Object[]> findDownStationRoute(String lineCode,Integer direction,Integer stationRouteCode);

	/**
	 * @Description :TODO(站点中心点坐标查询)
	 *
	 * @param map <lineId:线路ID； direction：方向>
	 *
	 * @return List<Object[]>
	 */

	@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)
	public 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]：code（站点编码）；
	 * 
	 * 							[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_code," +
//				   "a.distances,"+
//				   "b.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)
	@Query(value = "SELECT * FROM ("+
			"SELECT b.g_lonx," +
			"b.g_laty,b.b_jwpoints," +
			"a.station_mark," +
			"a.station_route_code," +
			"a.station_code," +
			"a.distances,"+
			"a.station_name," +
			"a.directions FROM " +
			" bsth_c_stationroute a , bsth_c_station b " +
			" where  a.line = ?1 and a.destroy=0 and a.station = b.id ) 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.stationRouteName," +
			" a.stationBPolyonGrid," +
			" a.stationGPloyonGrid, " +
			" a.stationRadius," +
			" a.stationShapesType," +
			" b.id AS stationId," +
			" b.station_code AS stationCode," +
			" b.db_type AS stationDbType," +
			" b.b_jwpoints AS stationJwpoints," +
			" b.g_lonx AS stationGlonx," +
			" b.g_laty AS stationGlaty," +
			" a.stationRouteId " +
			" FROM ( SELECT s.id AS stationRouteId," +
			" ST_AsText(s.b_polygon_grid) as stationBPolyonGrid," +
			" ST_AsText(s.g_polygon_grid) AS stationGPloyonGrid, " +
			" s.radius AS stationRadius," +
			" s.shapes_type AS stationShapesType," +
			" s.line AS stationRouteLine," +
			" s.station as stationRouteStation," +
			" s.station_name AS stationRouteName," +
			" s.station_route_code as stationRouteCode," +
			" 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 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.stationRouteName," +
			" a.stationBPolyonGrid," +
			" a.stationGPloyonGrid, " +
			" a.stationRadius," +
			" a.stationShapesType," +
			" b.id AS stationId," +
			" b.station_code AS stationCode," +
			" b.db_type AS stationDbType," +
			" b.b_jwpoints AS stationJwpoints," +
			" b.g_lonx AS stationGlonx," +
			" b.g_laty AS stationGlaty," +
			" a.stationRouteId" +
			" FROM ( SELECT s.id AS stationRouteId," +
			" ST_AsText(s.b_polygon_grid) as stationBPolyonGrid," +
			" ST_AsText(s.g_polygon_grid) AS stationGPloyonGrid, " +
			" s.radius AS stationRadius," +
			" s.shapes_type AS stationShapesType," +
			" s.line AS stationRouteLine," +
			" s.station as stationRouteStation," +
			" s.station_name AS stationRouteName," +
			" s.station_route_code as stationRouteCode," +
			" 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 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 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)
	public void stationRouteDir(Integer line);
    
    @Modifying
    @Query(value="update bsth_c_stationroute set distances =?2 where id = ?1 ", nativeQuery=true)
	public 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)
	public 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)
    public void stationRouteUpdDestroy(Integer line,Integer dir);
    
    @Modifying
   	@Query(value="UPDATE bsth_c_stationroute set destroy = 1 where id = ?1", nativeQuery=true)
   	public 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)
    public void batchAddStationRoute(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);

	/**
	 * @Description :TODO(站点添加)
	 */
	@Transactional
	@Modifying
	@Query(value="INSERT INTO bsth_c_stationroute (line, line_code, station, station_code, station_name, station_route_code, b_polygon_grid, g_polygon_grid, radius, shapes_type, station_mark, directions, distances, to_time, destroy, versions)  " +
			" VALUES(?1,?2,?3,?4,?5,?6,ST_GeomFromText(?7),ST_GeomFromText(?8),?9,?10,?11,?12,?13,?14,?15,?16)", nativeQuery=true)
	public void stationRouteAdd(Integer line, String line_code, Integer station, String station_code, String station_name, Integer station_route_code,

								   String b_polygon_grid, String g_polygon_grid, Integer radius, String shapes_type,

								   String  station_mark ,Integer directions, double distances,double to_time, Integer destroy, Integer versions);

	/**
	 * @Description :TODO(站点更新)
	 */
	@Transactional
	@Modifying
	@Query(value="UPDATE bsth_c_stationroute SET " +
			"line =  ?1 , " +
			"station = ?2  , " +
			"station_code = ?3  , " +
			"station_name = ?4  , " +
			"line_code =  ?5  , " +
			"b_polygon_grid =  ST_GeomFromText(?6)  , " +
			"g_polygon_grid =  ST_GeomFromText(?7)  , " +
			"radius =  ?8  , " +
			"shapes_type = ?9  , " +
			"station_route_code = ?10  , " +
			"station_mark = ?11  , " +
			"destroy = ?12  , " +
			"directions = ?13  , " +
			"distances = ?14  , " +
			"to_time = ?15  , " +
			"versions = ?16  " +
			" WHERE id = ?17   ", nativeQuery=true)
	public void stationRouteUpdate(Integer line, Integer station, String station_code, String station_name, String line_code,

							  String b_polygon_grid, String g_polygon_grid, Integer radius, String shapes_type, Integer station_route_code,

								   String  station_mark, Integer destroy ,Integer directions, double distances,double to_time, Integer versions, Integer id);

}