潘钊 / bsth_control · Files

package com.bsth.repository;

import com.bsth.entity.Personnel;
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.Section;

import java.util.List;

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

@Repository
public interface SectionRepository extends BaseRepository<Section, Integer> {

	/**
	 * 查询路段最大ID
	 * @return 最大ID值
	 */
	@Query(value = "SELECT IFNULL(MAX(id), 0) FROM bsth_c_section", nativeQuery = true)
	int findLatestSectionId();
	
	
	/**
	 * @Description :TODO(系统规划路段保存)
	 * 
	 * @param map <sectionCode: 路段编码；sectionName：路段名称；crosesRoad：交叉路；endNode：终止节点；startNode：起始节点；
	 * 
	 *             middleNode：线路ID；gsectionVector：路段矢量(空间坐标点集合)WGS坐标点；bsectionVector：路段矢量(空间坐标点集合)--百度原始坐标坐标点；
	 * 
	 * 			   sectionType：路段类型 ；csectionVector：路段矢量(空间坐标点集合)--城建坐标点；roadCoding：路段编码；sectionDistance：路段距离；
	 * 
	 * 			   sectionTime：路段时间；dbType： 经纬坐标类型；speedLimit：限速；descriptions：描述；versions：版本号>
	 */
	@Transactional
    @Modifying
	@Query(value="INSERT INTO bsth_c_section "+
    
				 "(section_code , section_name , croses_road , end_node, start_node ,"+
				 
				 "middle_node , gsection_vector, bsection_vector , section_type , csection_vector,"+
				 
				 "road_coding , section_distance , section_time , db_type, speed_limit ,"+
				 
				 "descriptions , versions,id) "+
    
			"VALUES (?1 , ?2 , ?3 , ?4 , ?5 , "+
					
					"?6 , ST_GeomFromText(?7) , ST_GeomFromText(?8) , ?9 , ?10 ,"+
					
					"?11 , ?12 , ?13 , ?14 , ?15 ,"+
					
					"?16 , ?17, ?18"+
			")", nativeQuery=true)
	public void systemSave(String sectionCode , String sectionName , String crosesRoad , String endNode , String startNode,
			
						   String middleNode,String gsectionVector,String bsectionVector, String sectionType,String csectionVector,
						   
						   String roadCoding,double sectionDistance,double sectionTime,String dbType,double speedLimit,
						   
						   String descriptions, int versions,int id);
	
	
	@Transactional
    @Modifying
	@Query(value="insert into bsth_c_section(" + 
			"id," + 
			"section_code," + 
			"section_name," + 
			"croses_road," + 
			"end_node," + 
			"start_node," + 
			"middle_node," + 
			"section_type," + 
			"csection_vector," + 
			"bsection_vector," + 
			"gsection_vector," + 
			"road_coding," + 
			"section_distance," + 
			"section_time," + 
			"db_type," + 
			"speed_limit," + 
			"descriptions," + 
			"create_by," + 
			"create_date," + 
			"update_by," + 
			"update_date," + 
			"versions)" + 
			"(select" + 
			" ?2," + 
			" ?2," + 
			"section_name," + 
			"croses_road," + 
			"end_node," + 
			"start_node," + 
			"middle_node," + 
			"section_type," + 
			"csection_vector," + 
			"bsection_vector," + 
			"gsection_vector," + 
			"road_coding," + 
			"section_distance," + 
			"section_time," + 
			"db_type," + 
			"speed_limit," + 
			"descriptions," + 
			"create_by," + 
			"create_date," + 
			"update_by," + 
			"update_date," + 
			"?1" + 
			" from bsth_c_section where id = ?3)", nativeQuery=true)
	public void autoCopy(int versions,int id,int s_id);
	
	
	/**
	 * @Description :TODO(编辑线路走向)
	 * 
	 * @param map <sectionId:路段ID； gsectionVector：折线WGS坐标；bsectionVector：折线百度坐标>
	 * 
	 * @return Map<String, Object> <SUCCESS ; ERROR>
	 */
	@Transactional
    @Modifying
	@Query(value="UPDATE bsth_c_section SET " +
				" gsection_vector = ST_GeomFromText(?2) , " +
				" bsection_vector = ST_GeomFromText(?3)," +
				" section_code = ?4," +
				" section_name = ?5," +
				" croses_road = ?6," +
				" end_node = ?7," +
				" start_node = ?8," +
				" middle_node = ?9," +
				" section_type = ?10," +
				" road_coding = ?11," +
				" section_distance = ?12," +
				" section_time = ?13," +
				" db_type = ?14," +
				" speed_limit = ?15," +
				" descriptions = ?16," +
				" versions = ?17," +
				" create_by = ?18," +
				" create_date =  str_to_date(?19,'%Y-%m-%d %H:%i:%s')," +
				" update_by = ?20," +
				" update_date =  str_to_date(?21,'%Y-%m-%d %H:%i:%s')" +
				" WHERE id = ?1", nativeQuery=true)
	public void sectionUpdate(Integer sectionId,String gsectionVector,String bsectionVector,String sectionCode,String sectionName,
			
							  String crosesRoad,String endNode,String startNode,String middleNode,String sectionType,
							  
							  String roadCoding,Double sectionDistance,Double sectionTime,String dbType,
							  
							  Double speedLimit,String descriptions,Integer version,Integer createBy,String createDate,
							  
							  Integer updateBy,String updateDate);

	@Query(value = "SELECT AsText(s.bsection_vector) as section,r.directions as dir FROM bsth_c_section s left join bsth_c_sectionroute r on s.section_code = r.section_code " +
			"where r.line = ?1 and directions = ?2 ORDER BY r.directions,r.sectionroute_code ",nativeQuery = true)
	List<Object[]> getSectionDirByLineId(Integer lineId , Integer directions);

	@Query(value="select s from Section s where s.sectionCode=?1")
	List<Section> findSectionByCode(String sectionCode);
}