潘钊 / 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.SectionRoute;

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

@Repository
public interface SectionRouteRepository extends BaseRepository<SectionRoute, Integer> {
	
	/**
	 * @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 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" +
								"  FROM bsth_c_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", nativeQuery=true)
	List<Object[]> getSectionRoute(int lineId, int directions);
	
	
	/**
	 * @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 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" +
			" FROM  bsth_c_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);
	
	
	/**
	 * @Description :TODO(查询线路某方向下的上一个路段序号)
	 * 
	 * @param map <lineId:线路ID； direction：方向；sectionRouteCode：路段编码>
	 * 
	 * @return List<Map<String, Object>> 
	 */
	@Query(value = " select  MAX(r.sectionroute_code) as sectionrouteCode   from bsth_c_sectionroute r  WHERE r.line=?1 and r.directions =?2 and r.sectionroute_code< ?3", nativeQuery=true)
	List<Object[]> findUpSectionRouteCode(Integer lineId,Integer direction,Integer stationRouteCode);
	
	/**
	 * @Description :TODO(获取路段路由信息)
	 * 
	 * @param lineId：线路ID；directions：方向
	 * 
	 * @return List<Map<String, Object>> {[0]：directions（方向）；[1]：bsection_vector(路段坐标)；[2]：speed_limit(限速)；[3]：section_name(路段名称)}
	 */
	@Query(value =  "SELECT " +
					"c.directions," +
					"ST_AsText(s.bsection_vector) as bsection_vector," +
					"s.speed_limit," +
					"s.section_name " +
					" FROM bsth_c_sectionroute c " +
					" LEFT JOIN bsth_c_section s on c.section = s.id where c.line = ?1 and c.directions = ?2", nativeQuery=true)
	List<Object[]> sectionRouteVector(Integer lineId,Integer directions);
	
	@Transactional
    @Modifying 
	@Query(value="UPDATE bsth_c_sectionroute set is_roade_speed = ?1  where line= ?2 and directions=?3 ",nativeQuery = true)
	void isRoadSpeedUpd(Integer isR,Integer line,Integer directions);
}