RealChartsServiceImpl.java
12.1 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
package com.bsth.service.realcontrol.impl;
import com.bsth.data.BasicData;
import com.bsth.data.LineConfigData;
import com.bsth.entity.realcontrol.LineConfig;
import com.bsth.service.realcontrol.RealChartsService;
import com.bsth.service.realcontrol.dto.*;
import com.bsth.util.db.DBUtils_MS;
import com.google.common.base.Splitter;
import org.apache.commons.lang3.StringUtils;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
/**
* Created by panzhao on 2016/11/9.
*/
@Service
public class RealChartsServiceImpl implements RealChartsService {
@Autowired
NamedParameterJdbcTemplate jdbcTemplate;
@Autowired
LineConfigData lineConfigData;
private static DateTimeFormatter fmtyyyyMMdd = DateTimeFormat.forPattern("yyyy-MM-dd")
,fmtyyyyMMddHHmm = DateTimeFormat.forPattern("yyyy-MM-ddHH:mm");
private final static long DAY_TIME = 1000 * 60 * 60 * 24L;
/**
* 设备上线率
*
* @param
* @param idx 线路id字符串
* @return
*/
@Override
public List<DeviceOnlineRate> deviceOnlineRate(String month, String idx) {
List<String> idArray = Splitter.on(",").splitToList(idx);
//拼接in语句
String inStr = "";
for (String code : idArray) {
inStr += (",'" + code+"'");
}
inStr = " (" + inStr.substring(1) + ")";
String sql = "select DISTINCT XL_BM,SCHEDULE_DATE_STR, CL_ZBH from bsth_c_s_sp_info_real s where s.schedule_date_str like :month and xl_bm in " + inStr;
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("month", month+"-%");
final DateTimeFormatter fmtyyyyMMdd = DateTimeFormat.forPattern("yyyy-MM-dd");
//应该上线的设备
List<DeviceOnlineRate> mustList = jdbcTemplate.query(sql, parameters, new RowMapper<DeviceOnlineRate>() {
@Override
public DeviceOnlineRate mapRow(ResultSet rs, int rowNum) throws SQLException {
DeviceOnlineRate obj = new DeviceOnlineRate();
obj.setLineCode(rs.getString("XL_BM"));
obj.setDateStr(rs.getString("SCHEDULE_DATE_STR"));
obj.setNbbm(rs.getString("CL_ZBH"));
obj.setDayOfYear(fmtyyyyMMdd.parseDateTime(obj.getDateStr()).getDayOfYear());
return obj;
}
});
if(mustList.size() == 0)
return mustList;
//查询ms 库 gps信息
JdbcTemplate msJdbcTemplate = new JdbcTemplate(DBUtils_MS.getDataSource());
//要in的 days_year ，gps表分区字段
Set<Integer> daysSet = new HashSet<>();
Map<String, DeviceOnlineRate> groupData = new HashMap<>();
for(DeviceOnlineRate obj : mustList){
daysSet.add(obj.getDayOfYear());
//分组数据
groupData.put(obj.getDayOfYear()+"_"+obj.getNbbm(), obj);
}
//拼接 days_year in 语句
inStr="";
for(Integer daysOfYear : daysSet){
inStr += (",'" + daysOfYear+"'");
}
inStr = " (" + inStr.substring(1) + ")";
//查询gps表，获取实际上线设备
sql = "select DISTINCT DEVICE_ID, DAYS_YEAR from bsth_c_gps_info where days_year in " + inStr;
List<RealOnline> realList = msJdbcTemplate.query(sql, new RowMapper<RealOnline>() {
@Override
public RealOnline mapRow(ResultSet rs, int rowNum) throws SQLException {
RealOnline obj = new RealOnline();
obj.setDayOfYear(rs.getInt("DAYS_YEAR"));
obj.setDevice(rs.getString("DEVICE_ID"));
obj.setNbbm(BasicData.deviceId2NbbmMap.get(obj.getDevice()));
return obj;
}
});
DeviceOnlineRate donline;
for(RealOnline obj : realList){
if(StringUtils.isEmpty(obj.getNbbm()))
continue;
donline = groupData.get(obj.getDayOfYear() + "_" + obj.getNbbm());
if(donline != null)
donline.setOnline(true);
}
return mustList;
}
/**
* 出车率
* @param month
* @param idx
* @return
*/
@Override
public List<CarOutRate> carOutRate(String month, String idx) {
List<String> idArray = Splitter.on(",").splitToList(idx);
//拼接in语句
String inStr = "";
for (String code : idArray) {
inStr += (",'" + code+"'");
}
inStr = " (" + inStr.substring(1) + ")";
String sql = "SELECT DISTINCT XL_BM,SCHEDULE_DATE_STR,CL_ZBH,right(min(concat(REAL_EXEC_DATE,fcsj_actual)),5) as FIRST_OUT FROM bsth_c_s_sp_info_real s WHERE s.schedule_date_str LIKE :month AND xl_bm IN "+inStr+" group by XL_BM,SCHEDULE_DATE_STR,CL_ZBH";
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("month", month+"-%");
List<CarOutRate> list = jdbcTemplate.query(sql, parameters, new RowMapper<CarOutRate>() {
@Override
public CarOutRate mapRow(ResultSet rs, int rowNum) throws SQLException {
CarOutRate obj = new CarOutRate();
obj.setDateStr(rs.getString("SCHEDULE_DATE_STR"));
obj.setNbbm(rs.getString("CL_ZBH"));
obj.setLineCode(rs.getString("XL_BM"));
obj.setFirstOut(rs.getString("FIRST_OUT"));
return obj;
}
});
return list;
}
/**
* 首末班次准点率
* @param month
* @param idx
* @return
*/
/* @Override
public List<StratEndPunctualityRate> stratEndPunctualityRate(String month, String idx) {
List<String> idArray = Splitter.on(",").splitToList(idx);
//拼接in语句
String inStr = "";
for (String code : idArray) {
inStr += (",'" + code+"'");
}
inStr = " (" + inStr.substring(1) + ")";
String sql = "select SCHEDULE_DATE_STR,XL_BM,CL_ZBH, min(sj) as STARTDATE, max(sj) as ENDDATE " +
"from (select SCHEDULE_DATE_STR,dfsj, concat_ws('/',dfsj, fcsj_actual) as sj,XL_BM,CL_ZBH from bsth_c_s_sp_info_real " +
"where schedule_date_str like :month and bc_type='normal' and dfsj is not null and xl_bm in "+inStr+") t group by SCHEDULE_DATE_STR,XL_BM,CL_ZBH";
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("month", month+"-%");
List<StratEndPunctualityRate> list = jdbcTemplate.query(sql, parameters, new RowMapper<StratEndPunctualityRate>() {
@Override
public StratEndPunctualityRate mapRow(ResultSet rs, int rowNum) throws SQLException {
StratEndPunctualityRate obj = new StratEndPunctualityRate();
obj.setLineCode(rs.getString("XL_BM"));
obj.setDateStr(rs.getString("SCHEDULE_DATE_STR"));
obj.setNbbm(rs.getString("CL_ZBH"));
obj.setStartTime(rs.getString("STARTDATE"));
obj.setEndTime(rs.getString("ENDDATE"));
obj.setEtRealExecDate(obj.getDateStr());
if(obj.getEndTime().length() == 11){
//末班真实执行日期
LineConfig conf =lineConfigData.get(obj.getLineCode());
String fcsjActual=obj.getEndTime().split("/")[1];
if(fcsjActual.compareTo(conf.getStartOpt()) < 0){
//加一天
obj.setEtRealExecDate(fmtyyyyMMdd.print(fmtyyyyMMdd.parseMillis(obj.getEtRealExecDate()) + DAY_TIME));
}
}
return obj;
}
});
return list;
}*/
@Override
public List<StratEndPunctualityRate> sePunctualityRateLine(String month, String idx) {
List<String> idArray = Splitter.on(",").splitToList(idx);
//拼接in语句
String inStr = "";
for (String code : idArray) {
inStr += (",'" + code+"'");
}
inStr = " (" + inStr.substring(1) + ")";
String sql = "select min(DFSJ) as STARTDATE,max(DFSJ) as ENDDATE,SCHEDULE_DATE_STR,XL_BM,XL_DIR,REAL_EXEC_DATE from (SELECT SCHEDULE_DATE_STR,concat_ws('_',concat_ws('/', dfsj, fcsj_actual),fcno) AS DFSJ,XL_BM,XL_DIR,REAL_EXEC_DATE FROM bsth_c_s_sp_info_real WHERE schedule_date_str LIKE :month AND bc_type = 'normal' AND dfsj IS NOT NULL AND xl_bm IN "+inStr+" AND status !=-1) t group by SCHEDULE_DATE_STR,XL_BM,XL_DIR,REAL_EXEC_DATE";
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("month", month+"-%");
List<StratEndPunctualityRate> list = jdbcTemplate.query(sql, parameters, new RowMapper<StratEndPunctualityRate>() {
@Override
public StratEndPunctualityRate mapRow(ResultSet rs, int rowNum) throws SQLException {
StratEndPunctualityRate obj = new StratEndPunctualityRate();
obj.setLineCode(rs.getString("XL_BM"));
obj.setDateStr(rs.getString("SCHEDULE_DATE_STR"));
obj.setStartTime(rs.getString("STARTDATE"));
obj.setEndTime(rs.getString("ENDDATE"));
obj.setUpdown(rs.getInt("XL_DIR"));
obj.setEtRealExecDate(rs.getString("REAL_EXEC_DATE"));
if(obj.getEndTime().length() >= 11){
//末班真实执行日期，末班可能跨过12点。
LineConfig conf =lineConfigData.get(obj.getLineCode());
String fcsjActual=obj.getEndTime().split("/")[1];
if(fcsjActual.compareTo(conf.getStartOpt()) < 0){
//加一天
obj.setEtRealExecDate(fmtyyyyMMdd.print(fmtyyyyMMdd.parseMillis(obj.getEtRealExecDate()) + DAY_TIME));
}
}
return obj;
}
});
return list;
}
@Override
public List<ScheduleExecRate> scheduleExecRates(String date, String idx) {
List<String> idArray = Splitter.on(",").splitToList(idx);
//拼接in语句
String inStr = "";
for (String code : idArray) {
inStr += (",'" + code+"'");
}
inStr = " (" + inStr.substring(1) + ")";
String sql = "SELECT ID,DFSJ,FCSJ_ACTUAL,ZDSJ,ZDSJ_ACTUAL,`STATUS`,XL_BM FROM bsth_c_s_sp_info_real WHERE bc_type!='in' and bc_type!='out' and schedule_date_str=:date AND concat_ws('', real_exec_date, dfsj)<:cdate and xl_bm in "+inStr+" ORDER BY dfsj";
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("date", date);
parameters.addValue("cdate", fmtyyyyMMddHHmm.print(new Date().getTime()));
List<ScheduleExecRate> list = jdbcTemplate.query(sql, parameters, new RowMapper<ScheduleExecRate>() {
@Override
public ScheduleExecRate mapRow(ResultSet rs, int rowNum) throws SQLException {
ScheduleExecRate obj = new ScheduleExecRate();
obj.setId(rs.getLong("ID"));
obj.setDfsj(rs.getString("DFSJ"));
obj.setFcsjActual(rs.getString("FCSJ_ACTUAL"));
obj.setZdsj(rs.getString("ZDSJ"));
obj.setZdsjActual(rs.getString("ZDSJ_ACTUAL"));
obj.setStatus(rs.getInt("STATUS"));
obj.setLineCode(rs.getString("XL_BM"));
return obj;
}
});
return list;
}
}