PersonnelUpdateHandler.java
9.41 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
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.XmlToJson;
import com.bsth.wsdl.PJDataSerivce;
import com.bsth.wsdl.PJDataSerivceSoap;
import com.fasterxml.jackson.databind.ObjectMapper;
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.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
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;
@EnableScheduling
@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");
}
@Scheduled(cron = "0 0 4-23 * * *")
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-南汇六分");
unit2bcompany.put("南汇五分公司", "7-南汇五分");
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,a.job_codeori = b.job_code2 where a.destroy = 0 and a.locked = 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,a.job_codeori = b.job_code2 where a.destroy = 0 and a.job_code <> b.job_code1 and a.locked = 0");
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 and a.locked = 0 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 and a.locked = 0");
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 and locked = 0");
logger.info(String.format("分公司代码更新为1,记录数%d",rows));
logger.info(String.format("临港公交的人员全部启用,记录数%d",rows));
logger.info("人事数据同步 结束");
tran.commit(status);
} catch (Exception e) {
tran.rollback(status);
logger.error("", e);
}
}
}