StationRouteRepository.java
12.8 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
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
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(value = "SELECT a.`stationRoute.id`," +
"a.`stationRoute.line`," +
"a.`stationRoute.station`," +
"a.`stationRoute.stationName`," +
"a.`stationRoute.stationRouteCode`," +
"a.`stationRoute.lineCode`," +
"a.`stationRoute.stationMark`," +
"a.`stationRoute.outStationNmber`," +
"a.`stationRoute.directions`," +
"a.`stationRoute.distances`," +
"a.`stationRoute.toTime`," +
"a.`stationRoute.firstTime`," +
"a.`stationRoute.endTime`," +
"a.`stationRoute.descriptions`," +
"a.`stationRoute.versions`," +
"b.id AS 'station.id'," +
"b.station_cod AS 'station.stationCod'," +
"b.station_name AS 'station.stationName'," +
"b.road_coding AS 'station.roadCoding'," +
"b.db_type AS 'station.dbType'," +
"b.b_jwpoints AS 'station.bJwpoints'," +
"b.g_lonx AS 'station.gLonx'," +
"b.g_lonx AS 'station.gLaty'," +
"b.x AS 'station.x'," +
"b.y AS 'station.y'," +
"b.shapes_type AS 'station.shapesType'," +
"b.radius AS 'station.radius'," +
"ST_AsText(b.g_polygon_grid) AS 'station.gPolygonGrid'," +
"ST_AsText(b.b_polygon_grid) AS 'station.bPolygonGrid'," +
"b.destroy AS 'station.destroy'," +
"b.versions AS 'station.versions'," +
"b.descriptions AS 'station.descriptions' FROM (" +
"SELECT r.id AS 'stationRoute.id'," +
" r.line AS 'stationRoute.line'," +
"r.station AS 'stationRoute.station'," +
"r.station_name AS 'stationRoute.stationName'," +
"r.station_route_code as 'stationRoute.stationRouteCode'," +
"r.line_code AS 'stationRoute.lineCode'," +
"r.station_mark AS 'stationRoute.stationMark'," +
"r.out_station_nmber AS 'stationRoute.outStationNmber'," +
"r.directions AS 'stationRoute.directions'," +
"r.distances AS 'stationRoute.distances'," +
"r.to_time AS 'stationRoute.toTime'," +
"r.first_time AS 'stationRoute.firstTime'," +
"r.end_time AS 'stationRoute.endTime'," +
"r.descriptions AS 'stationRoute.descriptions'," +
"r.versions AS 'stationRoute.versions' FROM bsth_c_stationroute r WHERE r.line = ?1 and r.directions = ?2 and r.destroy=0) a " +
"LEFT JOIN bsth_c_station b " +
"ON a.`stationRoute.station` = b.id ORDER BY a.`stationRoute.stationRouteCode` ASC", nativeQuery=true)
List<Object[]> findPoints(int line,int directions);
/**
* @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 s.b_jwpoints,s.station_name FROM (" +
"SELECT b.station 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", nativeQuery=true)*/
@Query(value = "SELECT s.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)
public 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_cod（站点编码）；
*
* [6]：distances（站点距离）；[7]：station_name（站点名称）；[8]：directions（方向）}
*/
@Query(value = "SELECT * FROM ("+
"SELECT b.g_lonx," +
"b.g_laty,b.b_jwpoints," +
"a.station_mark," +
"a.station_route_code," +
"b.station_cod," +
"a.distances,"+
"b.station_name," +
"a.directions FROM (SELECT " +
"s.station_mark," +
"s.station_route_code," +
"s.directions," +
"s.distances,"+
"s.station FROM bsth_c_stationroute s where s.line = ?1 and s.destroy=0) a " +
"LEFT JOIN bsth_c_station b " +
" on a.station = b.id ORDER BY a.directions ASC ) k ORDER BY k.directions,k.station_route_code ASC", nativeQuery=true)
List<Object[]> usingSingle(Integer lineId);
/**
* @Description : TODO(根据站点路由Id查询详情)
*
* @param id:站点路由ID
*
* @return List<Object[]>
*/
@Query(value = "SELECT a.stationRouteLine," +
" a.stationRouteStation," +
" a.stationRouteCode," +
" a.stationRouteLIneCode," +
" a.stationRouteStationMark," +
" a.stationOutStationNmber," +
" a.stationRoutedirections," +
" a.stationRouteDistances," +
" a.stationRouteToTime," +
" a.staitonRouteFirstTime," +
" a.stationRouteEndTime," +
" a.stationRouteDescriptions," +
" a.stationRouteDestroy," +
" a.stationRouteVersions," +
" a.stationRouteCreateBy," +
" a.stationRouteCreateDate," +
" a.stationRouteUpdateBy," +
" a.stationRouteUpdateDate," +
" b.id AS stationId," +
" b.station_cod AS stationCode," +
" b.station_name AS stationName," +
" b.road_coding AS stationRoadCoding," +
" b.db_type AS stationDbType," +
" b.b_jwpoints AS stationJwpoints," +
" b.g_lonx AS stationGlonx," +
" b.g_laty AS stationGlaty," +
" b.x AS stationX," +
" b.y AS stationY," +
" ST_AsText(b.b_polygon_grid) as stationBPolyonGrid," +
" ST_AsText(b.g_polygon_grid) AS stationGPloyonGrid, " +
" b.destroy AS stationDestroy," +
" b.radius AS stationRadius," +
" b.shapes_type AS stationShapesType," +
" b.versions AS stationVersions," +
" b.descriptions AS sttationDescriptions," +
" b.create_by AS stationCreateBy," +
" b.create_date AS stationCreateDate," +
" b.update_by AS stationUpdateBy," +
" b.update_date AS stationUpdateDate," +
" a.stationRouteId,b.station_name as zdmc "+
" FROM ( SELECT s.id AS stationRouteId," +
" s.line AS stationRouteLine," +
" s.station as stationRouteStation," +
" s.station_route_code as stationRouteCode," +
" s.line_code AS stationRouteLIneCode," +
" s.station_mark AS stationRouteStationMark," +
" s.out_station_nmber AS stationOutStationNmber," +
" s.directions AS stationRoutedirections," +
" s.distances AS stationRouteDistances," +
" s.to_time AS stationRouteToTime," +
" s.first_time AS staitonRouteFirstTime," +
" s.end_time AS stationRouteEndTime," +
" s.descriptions AS stationRouteDescriptions," +
" s.destroy AS stationRouteDestroy," +
" s.versions AS stationRouteVersions," +
" s.create_by AS stationRouteCreateBy," +
" s.create_date AS stationRouteCreateDate," +
" s.update_by AS stationRouteUpdateBy," +
" s.update_date AS stationRouteUpdateDate FROM bsth_c_stationroute s WHERE s.id = ?1 ) a " +
" LEFT JOIN bsth_c_station b ON a.stationRouteStation = b.id", nativeQuery=true)
List<Object[]> findStationRouteInfo(Integer id);
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
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);
@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>> findAllLineWithYgc();
@Modifying
@Query(value="update bsth_c_stationroute set directions = case directions when 1 then 0 when 0 then 1 end where line_code = ?1 ", nativeQuery=true)
public void stationRouteDir(Integer line);
@Modifying
@Query(value="update bsth_c_stationroute set distances =?2 where id = ?1 ", nativeQuery=true)
public void upddis(Integer id,Double dis);
@Modifying
@Query(value="UPDATE bsth_c_stationroute set station_route_code = (station_route_code+10) where line = ?1 and directions = ?2 and station_route_code >=?3 and destroy = 0", nativeQuery=true)
public void stationUpdStationRouteCode(Integer line,Integer dir,Integer routeCod);
/**
* 更新路线前撤销线路原有站点
*
* @param line
* @param dir
*/
@Modifying
@Query(value="UPDATE bsth_c_stationroute set destroy = 1 where line = ?1 and directions = ?2", nativeQuery=true)
public void stationRouteUpdDestroy(Integer line,Integer dir);
@Modifying
@Query(value="UPDATE bsth_c_stationroute set destroy = 1 where id = ?1", nativeQuery=true)
public void stationRouteIsDestroyUpdBatch(Integer ids);
/**
* 更新路线前撤销线路原有站点
*
* @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)
public void stationRouteUpdate(Integer line,Integer dir);
}