潘钊 / 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> {

	/**
	 * 查询待更新线路的路段路由
	 */
	@Query(value = "SELECT sr FROM LsSectionRoute sr where sr.line.id =?1 and sr.lineCode=?2 and sr.versions=?3 and sr.destroy=0")
	public List<LsSectionRoute> findupdated(Integer lineId,String lineCode,Integer versions);
	
	@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 sectionSpeedLimet ,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);
	
	/**
	 * @Description :TODO(查询路段信息)
	 * 
	 * @param map <id:路段路由ID>
	 * 
	 * @return List<Object[]>
	 */
	@Query(value ="SELECT a.sectionRouteId," +
			"a.sectionRouteLineCode," +
			"a.sectionRouteCode," +
			"a.sectionRouteDirections," +
			"a.sectionRouteLine," +
			"a.sectionRouteSection," +
			"a.sectionRouteDescriptions," +
			"a.sectionRouteCreateBy," +
			"a.sectionRouteCreateDate," +
			"a.sectionRouteUpdateBy," +
			"a.sectionRouteUpdateDate," +
			"a.sectionRouteVersions," +
			"a.sectionRouteDestroy," +
			"b.id AS sectionId," +
			"b.section_code AS sectionCode," +
			"b.section_name AS sectionName," +
			"b.road_coding AS sectionRoadCoding," +
			"b.end_node AS sectionEndCode," +
			"b.start_node AS sectionStartNode," +
			"b.middle_node AS sectionMiddleNode," +
			"b.section_type AS sectionType," +
			"ST_AsText(b.csection_vector) AS sectionCsectionVector," +
			"ST_AsText(b.bsection_vector) AS sectionBsectionVector," +
			"ST_AsText(b.gsection_vector) AS sectionGsectionVector," +
			"b.section_distance AS sectionDistance," +
			"b.section_time AS sectionTime," +
			"b.db_type AS sectionDbtype," +
			"b.speed_limit AS sectionSpeedLimit," +
			"b.descriptions AS sectionDescriptions," +
			"b.create_by AS sectionCreateBy," +
			"b.create_date AS sectionCreateDate," +
			"b.update_by AS sectionUpdateBy," +
			"b.update_date AS sectionUpdateDate," +
			"b.versions AS sectionVersion , a.isRoadeSpeed  FROM (" +
			" SELECT s.id AS sectionRouteId," +
			"s.line_code AS sectionRouteLineCode," +
			"s.sectionroute_code AS sectionRouteCode," +
			"s.directions AS sectionRouteDirections," +
			"s.line AS sectionRouteLine," +
			"s.section AS sectionRouteSection," +
			"s.descriptions AS sectionRouteDescriptions," +
			"s.create_by AS sectionRouteCreateBy," +
			"s.create_date AS sectionRouteCreateDate," +
			"s.update_by AS sectionRouteUpdateBy," +
			"s.update_date AS sectionRouteUpdateDate," +
			"s.versions AS sectionRouteVersions," +
			"s.destroy AS sectionRouteDestroy,  s.is_roade_speed AS isRoadeSpeed " +
			" FROM  bsth_c_ls_sectionroute s where s.id =?1) a " +
			" LEFT JOIN bsth_c_section b on a.sectionRouteSection = b.id", nativeQuery=true)
	List<Object[]> findSectionRouteInfoFormId(int id);
	/**
     * 更新路线前删除线路版本下历史原有路段路由
     */
    @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);

    /**
     * 更新路线前撤销线路版本下历史原有路段路由
     */
    @Modifying
    @Query(value="UPDATE bsth_c_ls_sectionroute set destroy = 1 where line = ?1  and directions = ?2 and versions=?3", nativeQuery=true)
	public void batchDestroy(Integer sectionRouteLine, Integer directions, Integer versions);

	@Modifying
	@Query(value="UPDATE bsth_c_ls_sectionroute set sectionroute_code = (sectionroute_code+1) where line_code = ?1  and directions = ?2 and sectionroute_code >=?3 and destroy = 0", nativeQuery=true)
	public void sectionUpdSectionRouteCode(String lineCode, Integer directions, Integer sectionrouteCode);
	
    @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   ", nativeQuery=true)
	public void sectionRouteDir(Integer line);

	/**
	 * @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 sectionSpeedLimet ,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);

}