personnelUpdateHandler.java
15.7 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
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
package com.bsth.handler;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.bsth.entity.Personnel;
import com.bsth.repository.PersonnelRepository;
import com.bsth.service.PersonnelService;
import com.bsth.util.DBUtils_Personnel;
import com.bsth.util.XmlToJson;
import com.bsth.wsdl.PJDataSerivce;
import com.bsth.wsdl.PJDataSerivceSoap;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.hibernate.collection.internal.PersistentBag;
import org.joda.time.DateTime;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import javax.xml.namespace.QName;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
@Component
public class personnelUpdateHandler {
@Autowired
PersonnelService personnelService;
@Autowired
PersonnelRepository repository;
@Autowired
JdbcTemplate jdbcTemplate;
Logger logger = LoggerFactory.getLogger(this.getClass());
private static final QName SERVICE_NAME = new QName("http://www.pd-transport.com/", "PJDataSerivce");
private static Map<String, String> gzname2code = new HashMap<>();
static {
gzname2code.put("路线驾驶员","1");
gzname2code.put("团客车驾驶员","1");
gzname2code.put("交通车驾驶员","1");
gzname2code.put("调度员","2");
gzname2code.put("乘务员","3");
gzname2code.put("机务员/质检员","3");
gzname2code.put("其他服务人员","14");
}
public void sync() {
URL wsdlURL = PJDataSerivce.WSDL_LOCATION;
PJDataSerivce ss = new PJDataSerivce(wsdlURL, SERVICE_NAME);
PJDataSerivceSoap port = ss.getPJDataSerivceSoap12();
try {
Connection conn ;
PreparedStatement ps;
ResultSet rs = null ;
DateTime dt = DateTime.now();
String rq = dt.toString("yyyy-MM-dd");
logger.info("同步人员数据： " + rq);
System.out.println("Invoking getEmployee...");
String _getEmployee_idCard = "";
/*String unintCode_sn = "KD01.0110";
String _getEmployee_sn = port.getEmployee(unintCode_sn, _getEmployee_idCard);
String unintCode_jg = "KD01.0111";
String _getEmployee_jg = port.getEmployee(unintCode_jg, _getEmployee_idCard);
String unintCode_yg = "KD01.0113";
String _getEmployee_yg = port.getEmployee(unintCode_yg, _getEmployee_idCard);
String unintCode_nh = "KD01.0112";
String _getEmployee_nh = port.getEmployee(unintCode_nh, _getEmployee_idCard);*/
logger.info("金蝶数据同步到：bsth_c_personnel 表，开始");
// 把所有人员设为停用状态，然后在用金蝶人员数据匹配，匹配上的设为在职
conn = DBUtils_Personnel.getConnection();
// 服务器
ps = conn.prepareStatement("UPDATE pd_control.bsth_c_personnel SET destroy = 1");
// 本地
// ps = conn.prepareStatement("UPDATE pd_control.bsth_c_personnel SET destroy = 1");
ps.executeUpdate();
// 取出所有调度人员，转存map
Map<String, Personnel> personnelMap= new HashMap<>();
Iterator<Personnel> perIterator=repository.findAll().iterator();
// List<Personnel> personnelList = new ArrayList<>();
while(perIterator.hasNext()){
Personnel per=perIterator.next();
personnelMap.put(per.getJobCode(),per);
// 工号补全6位
// String JobCodeori = per.getJobCodeori();
// while (JobCodeori.length() < 6){
// JobCodeori = "0"+JobCodeori;
// }
// per.setJobCodeori(JobCodeori);
// per.setJobCode(per.getCompanyCode()+"-"+JobCodeori);
// personnelList.add(per);
}
// 工号补全6位
// repository.save(personnelList);
for(int i=0;i<4;i++){
String unintCode = "KD01.011"+i;
String _getEmployee__return = port.getEmployee(unintCode, _getEmployee_idCard);
String _getRetiredInfor_unintCode = null;
System.out.println("Invoking getRetiredInfor...");
String _getRetiredInfor_beginDate = "1900-01-01";
String _getRetiredInfor_endDate = rq;
JSONArray retiredInfor = new JSONArray();
if(_getRetiredInfor_unintCode != null){
String _getRetiredInfor__return = port.getRetiredInfor(unintCode, _getRetiredInfor_beginDate, _getRetiredInfor_endDate);
JSONObject jsonRetiredInfor= XmlToJson.xml2Json(_getRetiredInfor__return);
retiredInfor = JSONArray.parseArray(jsonRetiredInfor.get("Retire").toString());
}
personnelService.matchPersonnel(retiredInfor,_getEmployee__return,i,personnelMap);
}
logger.info("金蝶数据同步到：bsth_c_personnel 表，完成");
logger.info("把bsth_c_personnel备份到：personnel库的bsth_c_personnel"+rq+" 表");
String sql="CREATE TABLE `bsth_c_personnel"+rq+"` (" +
" `id` int(11) NOT NULL ," +//AUTO_INCREMENT
" `branche_company` varchar(255) DEFAULT NULL," +
" `branche_company_code` varchar(255) DEFAULT NULL," +
" `company` varchar(255) DEFAULT NULL," +
" `company_code` varchar(255) NOT NULL," +
" `create_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP," +
" `descriptions` varchar(255) DEFAULT NULL," +
" `ic_card_code` varchar(255) DEFAULT NULL," +
" `job_code` varchar(255) NOT NULL," +
" `line_code` varchar(255) DEFAULT NULL," +
" `papers_code` varchar(255) DEFAULT NULL," +
" `personnel_code` varchar(255) DEFAULT NULL," +
" `personnel_img` varchar(255) DEFAULT NULL," +
" `personnel_name` varchar(255) NOT NULL," +
" `personnel_type` varchar(255) DEFAULT NULL," +
" `posts` varchar(255) DEFAULT NULL," +
" `telphone` varchar(255) DEFAULT NULL," +
" `update_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP," +
" `create_by` int(11) DEFAULT NULL," +
" `update_by` int(11) DEFAULT NULL," +
" `card` varchar(255) DEFAULT NULL," +
" `ic_rfid` varchar(255) DEFAULT NULL," +
" `id_rfid` varchar(255) DEFAULT NULL," +
" `tag_rfid` varchar(255) DEFAULT NULL," +
" `remark` varchar(255) DEFAULT NULL," +
" `job_codeori` varchar(255) NOT NULL," +
" `destroy` int(11)," +
" `jd_codeori` varchar(255)," +
"PRIMARY KEY (`id`)"+
") ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8";
int returnRs;
try {
logger.info("创建personnel库的bsth_c_personnel"+rq+" 表");
// conn = DBUtils_Personnel.getConnection();
ps = conn.prepareStatement("DROP TABLE IF EXISTS `bsth_c_personnel"+rq+"`");
ps.executeUpdate();
ps = conn.prepareStatement(sql);
returnRs = ps.executeUpdate();
if(returnRs == 0){
logger.info("备份数据到personnel库的bsth_c_personnel"+rq+" 表，开始");
// 服务器
sql = "INSERT into `bsth_c_personnel"+rq+"` (SELECT * FROM control.bsth_c_personnel)";
// 本地
// sql = "INSERT into `bsth_c_personnel"+rq+"` (SELECT * FROM pd_control.bsth_c_personnel)";
ps = conn.prepareStatement(sql);
returnRs = ps.executeUpdate();
logger.info("备份数据到personnel库的bsth_c_personnel"+rq+" 表，完成");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
DBUtils_Personnel.close(rs, ps, conn);
}
// logger.info("把bsth_c_personnel_copy数据同步到：bsth_c_personnel 表，开始");
// List<Personnel> retuenList = jdbcTemplate.query("select * from bsth_c_personnel_copy",new BeanPropertyRowMapper(Personnel.class));
// repository.save(retuenList);
// logger.info("bsth_c_personnel_copy数据同步到：bsth_c_personnel 表，完成");
// jdbcTemplate.execute()
// // 备份数据
// jdbcTemplate.update("INSERT into `bsth_c_personnel"+rq+"` (SELECT * FROM pd_control.bsth_c_personnel)");
// jdbcTemplate.update("insert into bsth_c_personnel_copy select * from bsth_c_personnel");
// String _getEmployee__return = _getEmployee_sn + _getEmployee_jg + _getEmployee_yg + _getEmployee_nh;
// JSONObject jsonInfor = XmlToJson.xml2Json(_getEmployee__return);
// System.out.println("getEmployee.result=" + _getEmployee__return);
logger.info("同步完成： " + rq);
} catch (Exception e) {
logger.error("", e);
}
}
public void sync(String s) {
final Map<String, String> unit2company = new HashMap<>(4);
unit2company.put("KD01.0110", "55-上南公司");
unit2company.put("KD01.0111", "22-金高公司");
unit2company.put("KD01.0112", "26-南汇公司");
unit2company.put("KD01.0113", "05-杨高公司");
final Map<String, String> unit2bcompany = new HashMap<>();
unit2bcompany.put("杨高分公司", "5-杨高分公司");
unit2bcompany.put("周浦分公司", "6-周浦分公司");
unit2bcompany.put("川沙分公司", "1-川沙分公司");
unit2bcompany.put("金桥分公司", "2-金桥分公司");
unit2bcompany.put("上南六分", "3-上南六分公司");
unit2bcompany.put("上南三分", "2-上南三分公司");
unit2bcompany.put("上南一分", "4-上南一分公司");
unit2bcompany.put("上南二分", "1-上南二分公司");
unit2bcompany.put("金高二分", "2-二分公司");
unit2bcompany.put("金高四分", "1-四分公司");
unit2bcompany.put("金高三分", "3-三分公司");
unit2bcompany.put("金高一分", "5-一分公司");
unit2bcompany.put("南汇一分", "1-南汇一分");
unit2bcompany.put("南汇二分", "2-南汇二分");
unit2bcompany.put("南汇三分", "3-南汇三分");
unit2bcompany.put("南汇六分", "6-南汇六分");
PJDataSerivce ss = new PJDataSerivce(PJDataSerivce.WSDL_LOCATION, SERVICE_NAME);
PJDataSerivceSoap port = ss.getPJDataSerivceSoap12();
//编程式事务
DataSourceTransactionManager tran = new DataSourceTransactionManager(jdbcTemplate.getDataSource());
DefaultTransactionDefinition def = new DefaultTransactionDefinition();
def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
TransactionStatus status = tran.getTransaction(def);
try {
logger.info("人事数据同步 开始");
List<Personnel> personnels = jdbcTemplate.query("select * from bsth_c_personnel", BeanPropertyRowMapper.newInstance(Personnel.class));
logger.info("personnel 原数据:" + new ObjectMapper().writeValueAsString(personnels));
// KD01.0110上南 KD01.0111金高 KD01.0112南汇 KD01.0113杨高
jdbcTemplate.execute("delete from bsth_c_employee");
for (int i = 0;i < 4;i++) {
final String unintCode = "KD01.011" + i;
String employeeXml = port.getEmployee(unintCode, "");
logger.info(unintCode + ":" + employeeXml);
JSONObject json = XmlToJson.xml2Json(employeeXml);
final JSONArray emps = (JSONArray)json.get("Emp");
jdbcTemplate.batchUpdate("insert into bsth_c_employee values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
JSONObject emp = emps.getJSONObject(i);
String jobCode = emp.getString("Code"), company = unit2company.get(unintCode), positName = emp.getString("PositName"), bcompany = unit2bcompany.get(emp.getString("HRMS_USERFIELD_45"));
String[] jobCodeSplit = jobCode.split("-"), companySplit = company.split("-"), bcompanySplit = bcompany == null ? null : bcompany.split("-");
ps.setString(1, jobCode);
ps.setString(2, companySplit[0] + "-" + jobCodeSplit[1]);
ps.setString(3, jobCodeSplit[1]);
ps.setString(4, emp.getString("EmployeeName"));
ps.setString(5, gzname2code.get(positName) == null ? "14" : gzname2code.get(positName));
ps.setString(6, companySplit[0]);
ps.setString(7, companySplit[1]);
ps.setString(8, emp.getString("IDCardID") == null ? "" : emp.getString("IDCardID"));
ps.setString(9, emp.getString("EnterDeptDate"));
ps.setString(10, emp.getString("EnterGroupDate"));
ps.setString(11, emp.getString("InpositionDate"));
ps.setString(12, bcompanySplit == null ? null : bcompanySplit[0]);
ps.setString(13, bcompanySplit == null ? null : bcompanySplit[1]);
}
@Override
public int getBatchSize() {
return emps.size();
}
});
}
// 根据工号关联更新姓名、公司、工种、身份证号
int rows = jdbcTemplate.update("update bsth_c_personnel a inner join bsth_c_employee b on a.job_code = b.job_code1 set a.personnel_name = b.employee_name,a.personnel_type = b.job_type,a.company = b.company_name,a.company_code = b.company_code,a.card = b.id_card,a.branche_company_code = b.branche_company_code,a.branche_company = b.branche_company,a.jd_codeori = b.job_code where a.destroy = 0");
logger.info(String.format("根据工号关联更新姓名、公司、工种、身份证号,记录数%d",rows));
// 未匹配上工号的数据 用身份证号关联更新工号、姓名、公司、工种
rows = jdbcTemplate.update("update bsth_c_personnel a inner join bsth_c_employee b on a.card = b.id_card set a.job_code = b.job_code1,a.personnel_name = b.employee_name,a.personnel_type = b.job_type,a.company = b.company_name,a.company_code = b.company_code,a.branche_company_code = b.branche_company_code,a.branche_company = b.branche_company,a.jd_codeori = b.job_code where a.destroy = 0 and a.job_code <> b.job_code1");
logger.info(String.format("根据身份证号关联更新工号、姓名、公司、工种,记录数%d",rows));
// 可能会出现数据异常情况 如一个是最新的工号无身份证号，一个是老的工号有身份证号，最后更新的结果会造成工号和身份证号重复
// 将更早创建的人员信息更新为停用
rows = jdbcTemplate.update("update bsth_c_personnel x INNER JOIN (select card,min(id) id from bsth_c_personnel a where destroy = 0 and LENGTH(a.card) > 1 GROUP BY card having count(*) > 1) y on x.id = y.id set destroy = 1");
logger.info(String.format("同一个人,将更早创建的人员停用,记录数%d",rows));
// 仍未匹配上的调度数据作为停用人员
rows = jdbcTemplate.update("update bsth_c_personnel a left join bsth_c_employee b on a.job_code = b.job_code1 set a.destroy = 1 where a.destroy = 0 and b.job_code1 is null");
logger.info(String.format("调度系统未匹配数据标记为停用,记录数%d",rows));
// 仍未匹配上的金蝶数据作为新人员
rows = jdbcTemplate.update("insert into bsth_c_personnel (job_code,personnel_name,personnel_type,company,company_code,job_codeori,branche_company_code,branche_company,jd_codeori) select b.job_code1,b.employee_name,b.job_type,b.company_name,b.company_code,b.job_code2,b.branche_company_code,b.branche_company,b.job_code from bsth_c_employee b left join bsth_c_personnel a on b.job_code1 = a.job_code where a.job_code is null");
logger.info(String.format("金蝶未匹配数据插入,记录数%d",rows));
// 赋值分公司编码1 不然无法查询
rows = jdbcTemplate.update("update bsth_c_personnel set branche_company_code = '1' where branche_company_code is null");
logger.info(String.format("分公司代码更新为1,记录数%d",rows));
// 赋值分公司编码1 不然无法查询
rows = jdbcTemplate.update("update bsth_c_personnel set destroy = 0 where company_code = 77");
logger.info(String.format("临港公交的人员全部启用,记录数%d",rows));
logger.info("人事数据同步 结束");
tran.commit(status);
} catch (Exception e) {
tran.rollback(status);
logger.error("", e);
}
}
}