RealChartsServiceImpl.java
5.51 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
package com.bsth.service.realcontrol.impl;
import com.bsth.data.BasicData;
import com.bsth.service.realcontrol.RealChartsService;
import com.bsth.service.realcontrol.dto.CarOutRate;
import com.bsth.service.realcontrol.dto.DeviceOnlineRate;
import com.bsth.service.realcontrol.dto.RealOnline;
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;
/**
* 设备上线率
*
* @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;
}
@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;
}
}