StationRouteRepository.java
9.07 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
package com.bsth.repository;
import java.util.List;
import java.util.Map;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.EntityGraph;
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.StationRoute;
/**
*
* @Interface: StationRouteRepository(站点路由Repository数据持久层接口)
*
* @Extends : BaseRepository
*
* @Description: TODO(站点路由Repository数据持久层接口)
*
* @Author bsth@lq
*
* @Date 2016年5月03日 上午9:21:17
*
* @Version 公交调度系统BS版 0.1
*
*/
@Repository
public interface StationRouteRepository extends BaseRepository<StationRoute, Integer> {
@Query("select r from StationRoute r where r.line.id=?1 and r.destroy=0 order by r.directions ASC ,r.stationRouteCode ASC")
List<StationRoute> findStationExport(int line);
/**
* @Description :TODO(查询线路某方向下的站点序号与类型)
*
* @param map <lineId:线路ID； direction：方向；stationRouteCode：站点编码>
*
* @return List<Map<String, Object>>
*/
@Query(value = "select t.station_route_code,t.station_mark from bsth_c_stationroute t where " +
" t.station_route_code =(" +
"select MAX(station_route_code) as stationRouteCode from bsth_c_stationroute r WHERE " +
"r.line=?1 and r.directions =?2 and station_route_code< ?3 and r.destroy = 0 ) and t.line=?1 and t.directions = ?2 AND t.destroy = 0", nativeQuery=true)
List<Object[]> findUpStationRouteCode(Integer lineId,Integer direction,Integer stationRouteCode);
/**
* @Description :TODO(站点中心点坐标查询)
*
* @param map <lineId:线路ID； direction：方向>
*
* @return List<Object[]>
*/
@Query(value = "SELECT CONCAT(ST_X(s.center_point), ' ', ST_Y(s.center_point)) b_jwpoints,r.station_name,r.station_route_code FROM (" +
"SELECT b.station,b.station_route_code,b.station_name FROM bsth_c_stationroute b WHERE b.line =?1 AND b.directions = ?2 AND b.destroy=0) r " +
"LEFT JOIN bsth_c_station s ON r.station = s.id ORDER BY r.station_route_code ASC", nativeQuery=true)
List<Object[]> getSelectStationRouteCenterPoints(Integer lineId,Integer direction);
/**
* @Description :TODO(撤销站点)
*
* @param map <lineId:线路ID； destroy：是否撤销（0：否；1：是）>
*
*/
@Transactional
@Modifying
@Query(value="UPDATE bsth_c_stationroute SET " +
"destroy = ?2 WHERE id = ?1", nativeQuery=true)
void stationRouteIsDestroyUpd(Integer stationRouteId, Integer destroy);
/**
* @Description : TODO(根据线路ID生成行单)
*
* @param lineId:线路ID
*
* @return List<Object[]>：{[0]：g_lonx（GPS经度）；[1]：g_laty（GPS纬度）；[2]：b_jwpoints（百度经纬度坐标）
*
* [3]：station_mark（站点类型）；[4]：station_route_code（站点序号）；[5]：station_code（站点编码）；
*
* [6]：distances（站点距离）；[7]：station_name（站点名称）；[8]：directions（方向）}
*/
@Query(value = "SELECT ST_X(s.center_point_wgs) g_lonx,ST_Y(s.center_point_wgs) g_laty,CONCAT(ST_X(s.center_point), ' ', ST_Y(s.center_point)) b_jwpoints,r.station_mark,r.station_route_code,s.station_code,r.distances,r.station_name,r.directions,r.station_name_en FROM bsth_c_stationroute r JOIN bsth_c_station s ON r.station = s.id WHERE r.line = ?1 and r.destroy = 0 ORDER BY r.directions,r.station_route_code", nativeQuery=true)
List<Object[]> usingSingle(Integer lineId);
List<StationRoute> findByLine(Line line);
@EntityGraph(value = "stationRoute_station", type = EntityGraph.EntityGraphType.FETCH)
@Query("select s from StationRoute s where s.destroy=0")
List<StationRoute> findAllEffective();
@EntityGraph(value = "stationRoute_station", type = EntityGraph.EntityGraphType.FETCH)
@Override
Page<StationRoute> findAll(Specification<StationRoute> spec, Pageable pageable);
@EntityGraph(value = "stationRoute_station", type = EntityGraph.EntityGraphType.FETCH)
@Override
List<StationRoute> findAll(Specification<StationRoute> spec);
@EntityGraph(value = "stationRoute_station", type = EntityGraph.EntityGraphType.FETCH)
@Override
List<StationRoute> findAll(Specification<StationRoute> spec, Sort sort);
@EntityGraph(value = "stationRoute_station", type = EntityGraph.EntityGraphType.FETCH)
@Override
@Query(value = "select r from StationRoute r where r.destroy=0")
List<StationRoute> findAll();
@Query("select new map(sr.station.id as stationid, sr.stationName as stationname) from StationRoute sr where sr.line.id=?1 and sr.directions=?2")
List<Map<String, Object>> findStations(Integer xlid, Integer xldir);
@Query("select r from StationRoute r where r.lineCode=?1 and r.directions=?2 and r.destroy=0 order by r.stationRouteCode")
List<StationRoute> findByLine(String lineCode, int updown);
@Query("select r from StationRoute r where r.line.id=?1 and r.directions=?2 and r.destroy=0 order by r.stationRouteCode")
List<StationRoute> findByLine(Integer lineId, Integer dir);
@Query("select r from StationRoute r where r.lineCode=?1 and r.directions=?2 and r.destroy=0 order by r.stationRouteCode")
List<StationRoute> findAllByLine(String lineCode, int updown);
@EntityGraph(value = "stationRoute_station", type = EntityGraph.EntityGraphType.FETCH)
@Query("select s from StationRoute s where s.destroy=0 and s.lineCode=?1")
List<StationRoute> findByLineCode(String lineCode);
@Query("SELECT new map(" +
"lineCode as lineCode,directions as directions,stationName as stationName,stationCode as stationCode," +
"line.linePlayType as linePlayType,s.stationMark as stationMark) " +
"FROM " +
"StationRoute s " +
"WHERE " +
"s.destroy = 0 " +
"and s.lineCode in(select lineCode from Line where inUse = 1) " +
"ORDER BY " +
"lineCode,directions,stationRouteCode")
List<Map<String, String>> findLineWithYgcAndInuse();
@Query("SELECT new map(" +
"lineCode as lineCode,directions as directions,stationName as stationName,stationCode as stationCode," +
"line.linePlayType as linePlayType,s.stationMark as stationMark) " +
"FROM " +
"StationRoute s " +
"WHERE " +
"s.destroy = 0 " +
"ORDER BY " +
"lineCode,directions,stationRouteCode")
List<Map<String, String>> findAllLineWithYgc();
@Query("SELECT new map(" +
"lineCode as lineCode,directions as directions,stationName as stationName,stationCode as stationCode," +
"line.linePlayType as linePlayType,s.stationMark as stationMark) " +
"FROM " +
"StationRoute s " +
"WHERE " +
"s.destroy = 0 and s.lineCode = ?1 " +
"ORDER BY " +
"lineCode,directions,stationRouteCode")
List<Map<String, String>> findLineWithYgcByLine(String lineCode);
@Modifying
@Query(value="update bsth_c_stationroute set directions = case directions when 1 then 0 when 0 then 1 end where line = ?1 ", nativeQuery=true)
void stationRouteDir(Integer line);
@Modifying
@Query(value="update bsth_c_stationroute set distances =?2 where id = ?1 ", nativeQuery=true)
void upddis(Integer id,Double dis);
/**
* 更新路线前撤销线路原有站点
*
* @param line
* @param dir
*/
@Modifying
@Query(value="UPDATE bsth_c_stationroute set destroy = 1 where line = ?1 and directions = ?2", nativeQuery=true)
void stationRouteUpdDestroy(Integer line,Integer dir);
/**
*
*
* @param line
* @param dir
*/
@Modifying
@Query(value="insert into (select * from bsth_c_stationroute_cache where line = ?1 and directions = ?2) bsth_c_stationroute", nativeQuery=true)
void stationRouteUpdate(Integer line,Integer dir);
// 更具线路批量撤销
@Modifying
@Query(value="UPDATE StationRoute sr set sr.destroy = 1 where sr.line.id = ?1 and sr.lineCode = ?2")
void batchUpdate(Integer lineId, String lineCode);
// 批量删除
@Modifying
@Query(value="delete from StationRoute sr where sr.line.id = ?1 and sr.lineCode = ?2")
void batchDelete(Integer lineId, String lineCode);
// 批量修改站点行业编码
@Modifying
@Query(value="update bsth_c_stationroute set industry_code =?2 where id = ?1 ", nativeQuery=true)
void updIndustryCode(Integer id,String IndustryCode);
/**
* 删除相应线路和版本的站点路由信息
* @param lineId
* @param version
*/
@Modifying
@Query(value="delete from StationRoute sr where sr.line.id = ?1 and sr.versions = ?2")
void deleteByLineAndVersion(Integer lineId, Integer version);
/**
* 历史路由表变更之后从历史表批量更新当前版本至当前表
* @param lineId
* @param version
*/
@Modifying
@Query(value="insert into bsth_c_stationroute select * from bsth_c_ls_stationroute where line = ?1 and versions = ?2", nativeQuery = true)
void updateFromHistory(Integer lineId, Integer version);
}