潘钊 / bsth_control · Files

package com.bsth.repository;

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

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.LsStationRoute;

/**
 * 
 * @Interface: StationRouteRepository(站点路由Repository数据持久层接口)
 * 
 * @Extends : BaseRepository
 * 
 * @Description: TODO(站点路由Repository数据持久层接口)
 * 
 * @Author bsth@lq
 *
 * @Version 公交调度系统BS版 0.1
 * 
 */

@Repository
public interface LsStationRouteRepository extends BaseRepository<LsStationRoute, Integer> {
	
	/**
	 * 查询待更新线路的站点路由
	 */
	@EntityGraph(value = "ls_stationRoute_station", type = EntityGraph.EntityGraphType.FETCH)
	@Query(value = "SELECT DISTINCT sr FROM LsStationRoute sr where sr.line.id =?1 and sr.lineCode=?2 and sr.versions=?3 and sr.destroy=0")
	List<LsStationRoute> findupdated(Integer lineId, String lineCode, Integer versions);
	
	@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`, " +
			"a.`stationRoute.stationNameEn` " +
			" 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_name_en AS 'stationRoute.stationNameEn'," +
				"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_ls_stationroute r  WHERE  r.line = ?1 and r.directions = ?2 and r.versions=?3 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(Integer lineId, Integer direction, Integer versions);

	
	@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, "+
			"a.stationNameEn "+
			" FROM ( SELECT s.id AS stationRouteId," +
				" s.line AS stationRouteLine," +
				" s.station as stationRouteStation," +
				" s.station_name AS stationRouteName," +
				" s.station_name_en AS stationNameEn," +
				" 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_ls_stationroute s WHERE s.line = ?1 and s.directions = ?2 and s.versions=?3 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 ,Integer version);
	/**
     * 更新路线前删除线路版本号历史原有站点路由
     * 
     * @param line
     * @param dir
     */
    @Modifying
    @Transactional
    @Query(value="DELETE from bsth_c_ls_stationroute where line = ?1  and directions = ?2 and versions = ?3", nativeQuery=true)
    public void batchDelete(Integer line,Integer dir, Integer versions);

    /**
     * 更新路线前撤销线路版本号历史原有站点路由
     * 
     * @param line
     * @param dir
     */
    @Modifying
    @Query(value="UPDATE bsth_c_ls_stationroute set destroy = 1 where line = ?1  and directions = ?2 and versions = ?3", nativeQuery=true)
    public void batchDestroy(Integer sectionRouteLine, Integer directions, Integer versions);
    
    /**
     * 按线路编码查询各站点的顺序号
     * @param lineCode 线路编码
     * @param lineVersion 版本号
     * @return
     */
    @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 " +
            "LsStationRoute s " +
            "WHERE " +
            "s.destroy = 0 AND s.lineCode = ?1 AND s.versions = ?2 " +
            "ORDER BY " +
            "lineCode,directions,stationRouteCode")
    List<Map<String, String>> findLineWithLineCode4Ygc(String lineCode,Integer lineVersion);
	/**
	 * @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_ls_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);
	
    // 批量修改站点行业编码
	@Modifying
	@Query(value="update bsth_c_ls_stationroute set industry_code =?2 where id = ?1 ", nativeQuery=true)
	void updIndustryCode(Integer id, String industryCode);
	
    @Modifying
	@Query(value="UPDATE bsth_c_ls_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);
    
    @Modifying
    @Query(value="update bsth_c_ls_stationroute set directions = case directions when 1 then 0 when 0 then 1 end where line = ?1 ", nativeQuery=true)
	void stationRouteDir(Integer line);

	@Query("select r from LsStationRoute r where r.line.id=?1 and r.directions=?2 and r.destroy=0 order by r.stationRouteCode")
	List<LsStationRoute> findByLine(int lineId, int dir);

    @Modifying
    @Query(value="update bsth_c_ls_stationroute set distances =?2 where id = ?1 ", nativeQuery=true)
	void upddis(Integer id,Double dis);

    @Query(value="select * from  bsth_c_ls_stationroute where line_code = ?3 and directions = ?4 and destroy = 0 and versions = ?5 limit ?1,?2", nativeQuery=true)
	Iterable<LsStationRoute> page(int start , int end , int line ,int dir, int version);
    
    @Query(value="select count(*) from  bsth_c_ls_stationroute where line_code = ?1 and directions = ?2 and destroy = 0 and versions = ?3 ", nativeQuery=true)
	int count(int line, int dir, int version);
	
	@Query(value = " select r.station_code, r.station_name, r.versions " +
					"from bsth_c_ls_stationroute r where r.line_code = ?1 " +
					"and r.directions=?2 and r.destroy = 0 and r.versions in(" +
					"select v.versions from bsth_c_line_versions v where v.line_code=?1 " +
					"and ((date_format(v.start_date, '%Y-%m-%d') >= ?3 and date_format(v.start_date, '%Y-%m-%d') <= ?4) " +
					" or  (date_format(v.start_date, '%Y-%m-%d') <= ?4 and date_format(v.end_date, '%Y-%m-%d') >= ?4) " +
					" or  (date_format(v.start_date, '%Y-%m-%d') >= ?3 and date_format(v.end_date, '%Y-%m-%d') <= ?4))" +
					") order by r.versions desc, r.station_route_code asc ", nativeQuery = true)
    List<Object[]> findHistory(String lineCode, Integer updown, String date1, String date2);

	@Query("select r from LsStationRoute r where r.line.id=?1 and r.versions=?2 and r.directions=?3 and r.destroy=0 order by r.stationRouteCode")
	List<LsStationRoute> findByLineVersion(int lineId, int version, int dir);
}