LsStationRouteRepository.java
9.04 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
package com.bsth.repository;
import java.util.Date;
import java.util.List;
import java.util.Map;
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.LsStationRoute;
/**
*
* @Interface: StationRouteRepository(站点路由Repository数据持久层接口)
*
* @Extends : BaseRepository
*
* @Description: TODO(站点路由Repository数据持久层接口)
*
* @Author bsth@lq
*
* @Version 公交调度系统BS版 0.1
*
*/
@Repository
public interface LsStationRouteRepository extends BaseRepository<LsStationRoute, Integer> {
/**
* 批量撤销站点路由
* @param ids
*/
@Modifying
@Query(value = "UPDATE LsStationRoute lsr set lsr.destroy = 1 where lsr.id in (?1)")
void batchDestroy(List<Integer> ids);
/**
* 批量恢复撤销站点路由
* @param ids
*/
@Modifying
@Query(value = "UPDATE LsStationRoute lsr set lsr.destroy = 0 where lsr.id in (?1)")
void batchRecover(List<Integer> ids);
/**
* 查询待更新线路的站点路由
*/
@EntityGraph(value = "ls_stationRoute_station", type = EntityGraph.EntityGraphType.FETCH)
@Query(value = "SELECT DISTINCT sr FROM LsStationRoute sr where sr.line.id =?1 and sr.lineCode=?2 and sr.versions=?3 and sr.destroy=0")
List<LsStationRoute> findupdated(Integer lineId, String lineCode, Integer versions);
/**
* 更新路线前删除线路版本号历史原有站点路由
*
* @param line
* @param dir
*/
@Modifying
@Transactional
@Query(value="DELETE from bsth_c_ls_stationroute where line = ?1 and directions = ?2 and versions = ?3", nativeQuery=true)
public void batchDelete(Integer line,Integer dir, Integer versions);
/**
* 更新路线前撤销线路版本号历史原有站点路由
*
* @param line
* @param dir
*/
@Modifying
@Query(value="UPDATE bsth_c_ls_stationroute set destroy = 1 where line = ?1 and directions = ?2 and versions = ?3", nativeQuery=true)
public void batchDestroy(Integer sectionRouteLine, Integer directions, Integer versions);
/**
* 按线路编码查询各站点的顺序号
* @param lineCode 线路编码
* @param lineVersion 版本号
* @return
*/
@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 " +
"LsStationRoute s " +
"WHERE " +
"s.destroy = 0 AND s.lineCode = ?1 AND s.versions = ?2 " +
"ORDER BY " +
"lineCode,directions,stationRouteCode")
List<Map<String, String>> findLineWithLineCode4Ygc(String lineCode,Integer lineVersion);
/**
* 将对应线路、版本、方向、未撤销的站点路由中
* 最小路由编号的路由改为'B'站点标记
* @param lsStationRoute
*/
@Modifying
@Query(value="update bsth_c_ls_stationroute a join (select min(station_route_code) station_route_code from bsth_c_ls_stationroute where line = :#{#lsStationRoute.line.id} AND destroy = 0 AND versions = :#{#lsStationRoute.versions} AND directions = :#{#lsStationRoute.directions}) b ON a.station_route_code = b.station_route_code set a.station_mark = 'B' where line = :#{#lsStationRoute.line.id} AND destroy = 0 AND versions = :#{#lsStationRoute.versions} AND directions = :#{#lsStationRoute.directions}", nativeQuery=true)
void updateStartB(LsStationRoute lsStationRoute);
/**
* 将对应线路、版本、方向、未撤销的站点路由中
* 路由编号的路由改为'Z'站点标记
* @param lsStationRoute
*/
@Modifying
@Query(value="update bsth_c_ls_stationroute a set a.station_mark = 'Z' where line = :#{#lsStationRoute.line.id} AND destroy = 0 AND versions = :#{#lsStationRoute.versions} AND directions = :#{#lsStationRoute.directions}", nativeQuery=true)
void updateStartZ(LsStationRoute lsStationRoute);
/**
* 将对应线路、版本、方向、未撤销的站点路由中
* 最大路由编号的路由改为'E'站点标记
* @param lsStationRoute
*/
@Modifying(flushAutomatically = true)
@Query(value="update bsth_c_ls_stationroute a join (select max(station_route_code) station_route_code from bsth_c_ls_stationroute where line = :#{#lsStationRoute.line.id} AND destroy = 0 AND versions = :#{#lsStationRoute.versions} AND directions = :#{#lsStationRoute.directions}) b ON a.station_route_code = b.station_route_code set a.station_mark = 'E' where line = :#{#lsStationRoute.line.id} AND destroy = 0 AND versions = :#{#lsStationRoute.versions} AND directions = :#{#lsStationRoute.directions}", nativeQuery=true)
void updateStartE(LsStationRoute lsStationRoute);
/**
* 在站点路由更新时(站点路由可能改变路由编号，如起点、改变上一站点)
* 将当前路由编号及以后的编号批量递增10
* @param lsStationRoute
*/
@Modifying
@Query(value="UPDATE bsth_c_ls_stationroute set station_route_code = (station_route_code + 10) where line = :#{#lsStationRoute.line.id} and destroy = 0 and versions = :#{#lsStationRoute.versions} and directions = :#{#lsStationRoute.directions} and station_route_code >= :#{#lsStationRoute.stationRouteCode}", nativeQuery=true)
void updateStatiouRouteCode(LsStationRoute lsStationRoute);
/**
* 交换历史站点路由指定版本的上下行站点路由
* @param lineId
* @param version
*/
@Modifying
@Query(value="UPDATE bsth_c_ls_stationroute SET directions = CASE directions WHEN 1 THEN 0 WHEN 0 THEN 1 END WHERE line = ?1 AND versions = ?2", nativeQuery=true)
void exchangeDirection(int lineId, int version);
@Query(value="select * from bsth_c_ls_stationroute where line_code = ?3 and directions = ?4 and destroy = 0 and versions = ?5 limit ?1,?2", nativeQuery=true)
Iterable<LsStationRoute> page(int start , int end , int line ,int dir, int version);
@Query(value="select count(*) from bsth_c_ls_stationroute where line_code = ?1 and directions = ?2 and destroy = 0 and versions = ?3 ", nativeQuery=true)
int count(int line, int dir, int version);
@Query(value = " select r.station_code, r.station_name, r.versions " +
"from bsth_c_ls_stationroute r where r.line_code = ?1 " +
"and r.directions=?2 and r.destroy = 0 and r.versions in(" +
"select v.versions from bsth_c_line_versions v where v.line_code=?1 " +
"and ((date_format(v.start_date, '%Y-%m-%d') >= ?3 and date_format(v.start_date, '%Y-%m-%d') <= ?4) " +
" or (date_format(v.start_date, '%Y-%m-%d') <= ?4 and date_format(v.end_date, '%Y-%m-%d') >= ?4) " +
" or (date_format(v.start_date, '%Y-%m-%d') >= ?3 and date_format(v.end_date, '%Y-%m-%d') <= ?4))" +
") order by r.versions desc, r.station_route_code asc ", nativeQuery = true)
List<Object[]> findHistory(String lineCode, Integer updown, String date1, String date2);
@Query("select r from LsStationRoute r where r.line.id=?1 and r.versions=?2 and r.directions=?3 and r.destroy=0 order by r.stationRouteCode")
List<LsStationRoute> findByLineVersion(int lineId, int version, int dir);
/**
* 克隆历史版本变为新版本(版本恢复或近似恢复)
* @param lineId
* @param oldVersion
* @param newVersion
*/
@Modifying
@Query(value="insert into bsth_c_ls_stationroute (line,station,station_name,station_route_code,line_code,station_code,station_mark,out_station_nmber,directions,distances,to_time,first_time,end_time,descriptions,destroy,versions,industry_code,station_name_en,shaped_type,radius,buffer_polygon,buffer_polygon_wgs,center_point,center_point_wgs) select line,station,station_name,station_route_code,line_code,station_code,station_mark,out_station_nmber,directions,distances,to_time,first_time,end_time,descriptions,destroy,?3 as versions,industry_code,station_name_en,shaped_type,radius,buffer_polygon,buffer_polygon_wgs,center_point,center_point_wgs from bsth_c_ls_stationroute where line = ?1 and versions = ?2 and destroy = 0", nativeQuery = true)
void cloneFromHistoryVersion(Integer lineId, Integer oldVersion, Integer newVersion);
/**
*
* @param lineId
* @param version
* @param dir
* @return
*/
@Query("select r from LsStationRoute r where r.line.id=?1 and r.versions=?2 and r.directions=?3 and r.destroy=0 order by r.stationRouteCode")
List<LsStationRoute> findByLineVersion(Integer lineId, Integer version, Integer direction, Integer stationRouteCode);
/**
*
* @param lineId
* @param direction
* @param begin
* @param end
* @return
*/
@Query(value = "SELECT * FROM bsth_c_ls_stationroute WHERE line = :#{#lineId} AND directions = :#{#direction} AND versions IN (SELECT versions FROM bsth_c_line_versions WHERE line = :#{#lineId} AND (:#{#begin} BETWEEN start_date AND end_date OR :#{#end} BETWEEN start_date AND end_date)) ORDER BY versions,station_route_code", nativeQuery = true)
List<LsStationRoute> findByLineDirectionDate(Integer lineId, Integer direction, Date begin, Date end);
}