ScheduleRuleServiceImpl.java
10.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
package com.bsth.service.schedule.rules;
import com.bsth.entity.schedule.SchedulePlan;
import com.bsth.entity.schedule.SchedulePlanInfo;
import com.bsth.entity.schedule.temp.SchedulePlanRuleResult;
import com.bsth.service.schedule.rules.rerun.RerunRule_input;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* Created by xu on 17/4/19.
*/
@Service
public class ScheduleRuleServiceImpl implements ScheduleRuleService {
/** 日志记录器 */
private static final Logger logger = LoggerFactory.getLogger(ScheduleRuleServiceImpl.class);
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public List<RerunRule_input> findRerunrule(Integer xlid) {
String sql = "select " +
"rerun_type as type " +
", rerun_xl as m_xl " +
", rerun_ttinfo as m_ttinfo " +
", rerun_lp as m_lp " +
", rerun_ttinfodetail_fcsjs as m_fcsjs " +
", use_xl as s_dylp_xl " +
", use_lp as s_dylp_lp " +
", use_type as s_dylp_type " +
", use_hrtype as s_dylp_hrtype " +
", cl as s_dybc_cl " +
", cl_zbh as s_dybc_clzbh " +
", j as s_dybc_j " +
", j_gh as s_dybc_jgh " +
", j_name as s_dybc_jname " +
", s as s_dybc_s " +
", s_gh as s_dybc_sgh " +
", s_name as s_dybc_sname " +
"from bsth_c_s_rerun_rule " +
"where is_cancel = 0 and rerun_xl = ? ";
final List<RerunRule_input> rerunRule_inputs = new ArrayList<>();
jdbcTemplate.query(sql, new Object[]{xlid}, new RowCallbackHandler() {
@Override
public void processRow(ResultSet resultSet) throws SQLException {
// 发车时间
String[] fcsjs = resultSet.getString("m_fcsjs").split(",");
for (String fcsj: fcsjs) {
RerunRule_input rerunRule_input = new RerunRule_input();
rerunRule_input.setXl(String.valueOf(resultSet.getInt("m_xl")));
rerunRule_input.setTtinfo(String.valueOf(resultSet.getLong("m_ttinfo")));
rerunRule_input.setLp(String.valueOf(resultSet.getLong("m_lp")));
rerunRule_input.setFcsj(fcsj);
rerunRule_input.setType(resultSet.getString("type"));
// 对应班次类型
if ("dylp".equals(resultSet.getString("type"))) {
rerunRule_input.setS_xl(String.valueOf(resultSet.getInt("s_dylp_xl")));
rerunRule_input.setS_lp(String.valueOf(resultSet.getLong("s_dylp_lp")));
rerunRule_input.setUsetype(resultSet.getString("s_dylp_type"));
rerunRule_input.setUserhrtype(resultSet.getString("s_dylp_hrtype"));
} else if ("dybc".equals(resultSet.getString("type"))) {
rerunRule_input.setCl(resultSet.getInt("s_dybc_cl"));
rerunRule_input.setZbh(resultSet.getString("s_dybc_clzbh"));
rerunRule_input.setJ(resultSet.getInt("s_dybc_j"));
rerunRule_input.setJgh(resultSet.getString("s_dybc_jgh"));
rerunRule_input.setJname(resultSet.getString("s_dybc_jname"));
rerunRule_input.setS(resultSet.getInt("s_dybc_s"));
rerunRule_input.setSgh(resultSet.getString("s_dybc_sgh"));
rerunRule_input.setSname(resultSet.getString("s_dybc_sname"));
}
rerunRule_inputs.add(rerunRule_input);
}
}
});
return rerunRule_inputs;
}
@Override
public List<SchedulePlanRuleResult> findLastByXl(String xlid, Date from) {
String sql = "select * from bsth_c_s_sp_rule_rst a " +
"where exists (select 1 from " +
"(select t.rule_id as rid, max(t.schedule_date) as sd from bsth_c_s_sp_rule_rst t " +
"where t.xl_id = ? and t.schedule_date < ? " +
"group by t.rule_id) a2 " +
"where a.rule_id = rid and a.schedule_date = sd) ";
return jdbcTemplate.query(sql, new Object[]{xlid, from}, new RowMapper<SchedulePlanRuleResult>() {
@Override
public SchedulePlanRuleResult mapRow(ResultSet rs, int i) throws SQLException {
SchedulePlanRuleResult obj = new SchedulePlanRuleResult();
obj.setRuleId(rs.getString("rule_id"));
obj.setCcZbh(rs.getString("cc_zbh"));
obj.setQyrq(rs.getDate("qyrq"));
obj.setGids(rs.getString("gids"));
obj.setEcids(rs.getString("ecids"));
obj.setScheduleDate(rs.getDate("schedule_date"));
obj.setGidindex(rs.getString("gidindex"));
obj.setEcindex(rs.getString("ecindex"));
// 其他字段没用
return obj;
}
});
}
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED)
public void deleteSchedulePlanAll(Long id) {
String sql1 = "delete from bsth_c_s_sp where id = ?";
String sql2 = "delete from bsth_c_s_sp_info where schedule_plan = ?";
jdbcTemplate.update(sql2, id);
jdbcTemplate.update(sql1, id);
}
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED)
public void deelteSchedulePlanInfo(Integer xlid, Date datefrom, Date dateto) {
String sql = "delete from bsth_c_s_sp_info where xl = ? and schedule_date >= ? and schedule_date <= ?";
jdbcTemplate.update(sql, xlid, datefrom, dateto);
}
@Override
public void generateRuleResult(List<SchedulePlanRuleResult> schedulePlanRuleResults) {
// 、批量插入排班规则结果
List<List<SchedulePlanRuleResult>> lists = new ArrayList<>();
int batchSize = 2000;
int loopCount = schedulePlanRuleResults.size() / batchSize;
int otherCount = schedulePlanRuleResults.size() % batchSize;
for (int i = 0; i < loopCount; i++) {
lists.add(schedulePlanRuleResults.subList(i * batchSize, i * batchSize + batchSize));
}
if (otherCount > 0) {
lists.add(schedulePlanRuleResults.subList(loopCount * batchSize, loopCount * batchSize + otherCount));
}
for (final List<SchedulePlanRuleResult> list : lists) {
jdbcTemplate.batchUpdate(SchedulePlanRuleResult.generateInsertSql(), new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
SchedulePlanRuleResult schedulePlanRuleResult = list.get(i);
schedulePlanRuleResult.preparedStatementSet(preparedStatement);
}
@Override
public int getBatchSize() {
return list.size();
}
});
}
}
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED)
@Override
public void generateSchedulePlan(final SchedulePlan schedulePlan, final List<SchedulePlanInfo> schedulePlanInfos) {
// 1、插入排班计划主表，并获取主键id
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(
SchedulePlan.generateInsertSql(), new String[] {"id"});
schedulePlan.preparedStatementSet(ps);
return ps;
}
}, keyHolder);
SchedulePlan master = new SchedulePlan();
master.setId(keyHolder.getKey().longValue());
for (SchedulePlanInfo schedulePlanInfo : schedulePlanInfos) {
schedulePlanInfo.setSchedulePlan(master);
}
// 2、批量插入排班计划从表
List<List<SchedulePlanInfo>> lists = new ArrayList<>();
int batchSize = 2000;
int loopCount = schedulePlanInfos.size() / batchSize;
int otherCount = schedulePlanInfos.size() % batchSize;
for (int i = 0; i < loopCount; i++) {
lists.add(schedulePlanInfos.subList(i * batchSize, i * batchSize + batchSize));
}
if (otherCount > 0) {
lists.add(schedulePlanInfos.subList(loopCount * batchSize, loopCount * batchSize + otherCount));
}
for (final List<SchedulePlanInfo> list : lists) {
jdbcTemplate.batchUpdate(SchedulePlanInfo.generateInsertSql(), new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
SchedulePlanInfo schedulePlanInfo = list.get(i);
schedulePlanInfo.preparedStatementSet(preparedStatement);
}
@Override
public int getBatchSize() {
return list.size();
}
});
}
}
}