潘钊 / bsth_control · Files

package com.bsth.repository;

import java.util.List;

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.LsSectionRoute;
import com.bsth.entity.LsStationRoute;

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

@Repository
public interface LsSectionRouteRepository extends BaseRepository<LsSectionRoute, Integer> {

	/**
	 * 批量撤销路段路由
	 * @param ids
	 */
	@Modifying
	@Query(value = "UPDATE LsSectionRoute lsr set lsr.destroy = 1 where lsr.id in (?1)")
	void batchDestroy(List<Integer> ids);
	
	@Query(value ="SELECT  a.sectionrouteId," +
			 "a.sectionrouteLine," +
			" a.sectionrouteLineCode," +
			" a.sectionrouteSection," +
			" a.sectionrouteSectionCode," +
			" a.sectionrouteCode," +
			" a.sectionrouteDirections," +
			" b.id AS sectionId," +
			" b.section_code AS sectionCode," +
			" b.section_name AS sectionName," +
			" b.croses_road AS sectionRoad," +
			" b.end_node AS sectionEndNode," +
			" b.start_node AS sectionStartNode," +
			" b.middle_node AS sectionMiddleNode," +
	        " b.section_type AS sectionType," +
			" b.csection_vector AS sectionCsectionVector," +
			" ST_AsText(b.bsection_vector) AS sectionBsectionVector," +
			" ST_AsText(b.gsection_vector) AS sectionGsectionVector," +
			" b.road_coding AS sectionRoadCoding," +
			" b.section_distance AS sectionDistance," +
           " b.section_time AS sectionTime," +
			" b.db_type AS sectiondbType," +
			" b.speed_limit AS sectionSpeedLimit ,a.destroy,a.versions,a.descriptions,a.isRoadeSpeed FROM (" +
						"SELECT  r.id AS sectionrouteId," +
								"r.line AS sectionrouteLine," +
								"r.line_code AS sectionrouteLineCode," +
								"r.section AS sectionrouteSection," +
								"r.section_code AS sectionrouteSectionCode," +
								"r.sectionroute_code AS sectionrouteCode," +
								"r.directions AS sectionrouteDirections," +
								"r.destroy AS destroy," +
								"r.versions AS versions," +
								"r.descriptions AS descriptions, r.is_roade_speed AS isRoadeSpeed" +
								"  FROM bsth_c_ls_sectionroute r where r.line = ?1 and r.directions = ?2 and r.versions=?3 and r.destroy=0 ) a " +
								"	LEFT JOIN bsth_c_section b  ON a.sectionrouteSection = b.id order by a.sectionrouteCode asc", nativeQuery=true)
	List<Object[]> getSectionRoute(int lineId, int directions,int version);

	/**
     * 更新路线前删除线路版本下历史原有路段路由
     */
    @Modifying
    @Transactional
    @Query(value="DELETE from bsth_c_ls_sectionroute where line = ?1  and directions = ?2 and versions=?3", nativeQuery=true)
    public void batchDelete(Integer line, Integer dir, Integer versions);

	/**
	 * 在站点路由更新时(站点路由可能改变路由编号，如起点、改变上一站点)
	 * 将当前路由编号及以后的编号批量递增10
	 * @param lsSectionRoute
	 */
	@Modifying
	@Query(value="UPDATE bsth_c_ls_sectionroute set sectionroute_code = (sectionroute_code + 10) where line = :#{#lsSectionRoute.line.id} and destroy = 0 and versions = :#{#lsSectionRoute.versions} and directions = :#{#lsSectionRoute.directions} and sectionroute_code >= :#{#lsSectionRoute.sectionrouteCode}", nativeQuery=true)
	void updateSectiouRouteCode(LsSectionRoute lsSectionRoute);

	/**
	 * 交换历史站点路由指定版本的上下行站点路由
	 * @param lineId
	 * @param version
	 */
    @Modifying
    @Query(value="UPDATE bsth_c_ls_sectionroute SET directions = CASE directions WHEN 1 THEN 0 WHEN 0 THEN 1 END WHERE line_code = ?1 AND versions = ?2", nativeQuery=true)
	void exchangeDirection(int lineId, int version);

	/**
	 * @Description :TODO(查询路段信息)
	 * 
	 * @param map <lineId:线路ID； directions：方向>
	 * 
	 * @return List<Object[]>
	 */
	@Query(value ="SELECT  a.sectionrouteId," +
			 "a.sectionrouteLine," +
			" a.sectionrouteLineCode," +
			" a.sectionrouteSection," +
			" a.sectionrouteSectionCode," +
			" a.sectionrouteCode," +
			" a.sectionrouteDirections," +
			" b.id AS sectionId," +
			" b.section_code AS sectionCode," +
			" b.section_name AS sectionName," +
			" b.croses_road AS sectionRoad," +
			" b.end_node AS sectionEndNode," +
			" b.start_node AS sectionStartNode," +
			" b.middle_node AS sectionMiddleNode," +
	        " b.section_type AS sectionType," +
			" b.csection_vector AS sectionCsectionVector," +
			" ST_AsText(b.bsection_vector) AS sectionBsectionVector," +
			" ST_AsText(b.gsection_vector) AS sectionGsectionVector," +
			" b.road_coding AS sectionRoadCoding," +
			" b.section_distance AS sectionDistance," +
            " b.section_time AS sectionTime," +
			" b.db_type AS sectiondbType," +
			" b.speed_limit AS sectionSpeedLimit ,a.destroy,a.versions,a.descriptions,a.isRoadeSpeed FROM (" +
						"SELECT  r.id AS sectionrouteId," +
								"r.line AS sectionrouteLine," +
								"r.line_code AS sectionrouteLineCode," +
								"r.section AS sectionrouteSection," +
								"r.section_code AS sectionrouteSectionCode," +
								"r.sectionroute_code AS sectionrouteCode," +
								"r.directions AS sectionrouteDirections," +
								"r.destroy AS destroy," +
								"r.versions AS versions," +
								"r.descriptions AS descriptions, r.is_roade_speed AS isRoadeSpeed" +
								"  FROM bsth_c_ls_sectionroute r where r.line = ?1 and r.directions = ?2 and r.destroy=0 ) a " +
								"	LEFT JOIN bsth_c_section b  ON a.sectionrouteSection = b.id order by a.sectionrouteCode asc", nativeQuery=true)
	List<Object[]> getSectionRoute(int lineId, int directions);
	
	// 查询最大ID
	@Query(value = "SELECT IFNULL(num,0) as maxId from (SELECT MAX(sectionroute_code) as num FROM bsth_c_ls_sectionroute where line_code = 601010 and directions = 1 and destroy = 0) k" , nativeQuery=true)
	public int sectionRouteCodeMaxId();
	

    @Query(value="select * from  bsth_c_ls_sectionroute where line_code = ?3 and directions = ?4 and destroy = 0 and versions = ?5 limit ?1,?2", nativeQuery=true)
	public Iterable<LsSectionRoute> page(int i, int pageSize, int line, int dir, int version);
    
    @Query(value="select count(*) from  bsth_c_ls_sectionroute where line_code = ?1 and directions = ?2 and destroy = 0 and versions = ?3 ", nativeQuery=true)
	int count(int line, int dir, int version);

	/**
	 * 克隆历史版本变为新版本(版本恢复或近似恢复)
	 * @param lineId
	 * @param oldVersion
	 * @param newVersion
	 */
	@Modifying
	@Query(value="insert into bsth_c_ls_sectionroute (line_code,section_code,sectionroute_code,directions,line,section,descriptions,versions,destroy,is_roade_speed) select line_code,section_code,sectionroute_code,directions,line,section,descriptions,?3 as versions,destroy,is_roade_speed from bsth_c_ls_sectionroute where line = ?1 and versions = ?2 and destroy = 0", nativeQuery = true)
	void cloneFromHistoryVersion(Integer lineId, Integer oldVersion, Integer newVersion);

	/**
	 * 引用另一上下行路段(上行引用下行，下行引用上行)
	 * @param lineId
	 * @param version
	 * @param direction
	 * @param otherDirection
	 */
	@Modifying
	@Query(value="INSERT INTO bsth_c_ls_sectionroute (line_code,section_code,sectionroute_code,directions,line,section,descriptions,versions,destroy,is_roade_speed) SELECT line_code,section_code,a.sectionroute_code + b.sectionroute_code,?3 AS directions,a.line,section,descriptions,versions,destroy,is_roade_speed FROM bsth_c_ls_sectionroute a JOIN (SELECT IFNULL(MAX(sectionroute_code), 0) sectionroute_code FROM bsth_c_ls_sectionroute WHERE line = ?1 AND versions = ?2 AND directions = ?3 AND destroy = 0) b WHERE a.line = ?1 AND versions = ?2 AND directions = ?4 AND destroy = 0", nativeQuery = true)
	void quoteOtherSide(Integer lineId, Integer version, Integer direction, Integer otherDirection);
}