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

/**
 * @description TODO(路段限速持久层)
 * 
 * @author Administrator
 *
 * @date 2016年9月21日 13:31:25
 */
@Repository
public interface SectionSpeedRepository extends BaseRepository<SectionSpeed, Integer>{
	
	
	@Transactional
    @Modifying
    @Query(value="INSERT INTO bsth_c_sectionspeed (" +
    			 "line , line_code , directions , `code` , s_name,road,road_code ) " +
    			 " VALUES (" +
    			 "?1 , ?2 , ?3 , ?4 , ?5,?6,?7)", nativeQuery=true)
	/** @description TODO(路段限速保存) @param (<line：线路ID；versions：版本号；destroy：是否撤销；directions：方向；sValue：限速；code：路段序号> )*/
	/** @param (<lineCode：线路编码；sName：路段名称；sType：路段类型；bLineStr：路段矢量百度坐标；gLineStr：路段矢量WGS坐标；descriptions：说明>)*/
	void add(Integer line,String lineCode,Integer directions,Integer code,String sName,Integer road,String road_code);
	
	@Transactional
    @Modifying
    @Query(value="INSERT INTO bsth_c_road("+
    			 "broad_vector,groad_vector,road_code,road_name,speed,id)"+
    			 " VALUES(" +
    			 "ST_GeomFromText(?1),ST_GeomFromText(?2),?3,?4,?5,?6)", nativeQuery=true)
	void insertRoad(String broad_vector,String groad_vector,String road_code,String road_name,Double speed,Integer id);
	
	@Query(value="SELECT b.`name` as linename, a.speedid, a.bLineStr  ,a.line_code,a.s_name,a.speed ,a.line,a.codenumb,a.road,a.directions FROM ("+
				 " SELECT p.id AS speedid,p.line,p.line_code,p.road,p.directions,p.`code` AS codenumb,p.s_name,ST_AsText(r.broad_vector) AS bLineStr,r.speed FROM ("+
				 " SELECT s.id,s.line,s.line_code,s.road,s.directions,s.`code`,s.s_name FROM bsth_c_sectionspeed s where s.line =?1 and s.line_code=?2 and s.directions=?3) p LEFT JOIN bsth_c_road r ON p.road = r.id) a "+
				 " LEFT JOIN bsth_c_line b ON a.line = b.id  ORDER BY codenumb asc",nativeQuery = true)
	List<Object[]> getSectionSpeedInfo(int lineId, String lineCode, int directions);
	
	/** 获取路网数据 */
	@Query(value="SELECT ST_AsText(g.bdjw) as SHAPE , id as id ,ldbh,mc FROM jjwgps_t_gjldb g where g.bdjw is not null ",nativeQuery = true)
	List<Object[]> getroadNet();
	
	/** */
	@Query(value="SELECT ST_Contains(ST_Buffer(ST_GeomFromText(?1),0.0005),ST_GeomFromText(?2)) AS result",nativeQuery = true)
	int ST_Contains(String g1,String g2);
	
	
	@Query(value="SELECT id FROM bsth_c_road r where r.road_code = ?1",nativeQuery = true)
	Integer validateRoade(String raodCode);
	
	@Transactional
    @Modifying
	@Query(value="UPDATE bsth_c_sectionspeed  set `code` =?1 where id=?2",nativeQuery = true)
	void roadUpdCode(Integer code,Integer sSpeedId);
	
	@Transactional
    @Modifying
	@Query(value="UPDATE bsth_c_road set road_name = ?1 ,speed =?2 where id= ?3",nativeQuery = true)
	void sSpeedUpd(String roadName,Double speed ,Integer id);
	
}