StationRouteRepository.java
8.92 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
package com.bsth.repository;
import java.util.List;
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.LineInformation;
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'," +
"AsText(b.g_polygon_grid) AS 'station.gPolygonGrid'," +
"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 t.line=?1 and t.directions = ?2", 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 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)
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) 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," +
" AsText(b.b_polygon_grid) as stationBPolyonGrid," +
" 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"+
" 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> findAll2();
}