test2
14.8 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
已分queryPoServerInfoExportToExcel
//表头
List<String> header = new ArrayList<String>();
//数据体
List<List<String>> body = new ArrayList<List<String>>();
header.add("保单号");
header.add("分公司名称");
header.add("中心支公司名称");
header.add("部门名称");
header.add("营销服务部名称");
header.add("渠道");
header.add("投保人姓名");
header.add("投保人客户号");
header.add("业务员姓名");
header.add("业务员工号");
header.add("收展服务人员姓名");
header.add("收展服务人员工号");
header.add("主险名称");
header.add("缴次");
header.add("期交保费合计金额");
header.add("被保人客户号");
header.add("被保人姓名");
header.add("缴费年期");
header.add("保单联系地址");
if(StringUtils.isEmpty(branchCode)){
return ;
}
if(StringUtils.isEmpty(month)){
return ;
}
//数据源
List<JSONObject> resultList = checkService.queryPoServerInfoExportToExcel(branchCode, channelType, month);
//数据源处理
if(resultList != null && resultList.size() != 0){
//每行数据
for (Object objectLineData : resultList) {
JSONObject lineData = (JSONObject) JSONObject.toJSON(objectLineData);
List<String> data = new ArrayList<>();
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("POLICYNO")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("SECONDBRANCHNAME")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("THIRDBRANCHNAME")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("DEPTNAME")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("BRANCHNAME")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("DESCRIPTION")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("APPLICANTNAME")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("APPLICANTNO")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("AGENTNAME")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("AGENTNO")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("EMPNAME")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("SERVICERNO")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("ABBRNAME")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("PAYTIMES")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("MODALTOTALPREM")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("INSUREDNO")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("INSUREDNAME")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("PREMTERM")));
data.add(StringUtils.isNotNullAndEmptyBalck(lineData.get("FULLADDRESS")));
body.add(data);
}
}
Workbook workbook = ExportFileUtils.generateExcel("sheetName", header, body, "xlsx");
response.setContentType("application/octet-stream");
try {
response.setHeader("Content-disposition","attachment;filename="+java.net.URLEncoder.encode(branchCode+"-"+month+"已分单保单信息", "UTF-8")+".xlsx");
response.flushBuffer();
workbook.write(response.getOutputStream());
}catch (IOException e){
logUtils.error(e);
}
SELECT rpsi.policy_no policyNo,
(SELECT branch_name
FROM branch_info
WHERE is_valid = 'Y'
AND branch_level = '02'
START WITH branch_code = po.branch_code
CONNECT BY PRIOR parent_branch = branch_code) secondBranchName,
(SELECT branch_name
FROM branch_info
WHERE is_valid = 'Y'
AND branch_level = '03'
START WITH branch_code = po.branch_code
CONNECT BY PRIOR parent_branch = branch_code) thirdBranchName,
di.dept_name deptName,
decode(bi.branch_level, '04', bi.branch_name, '') branchName,
ctl.description AS description,
(SELECT ci.client_name
FROM client_information ci
WHERE ci.client_no = po.applicant_no) applicantName,
po.applicant_no applicantNo,
(SELECT ssi.emp_name
FROM staff_info ssi
WHERE ssi.emp_no = rpsi.agent_no) agentName,
rpsi.agent_no agentNo,
si.emp_name empName,
rpsi.service_no servicerNo,
p.abbr_name abbrName,
rpsi.pay_times payTimes,
ppi.modal_total_prem modalTotalPrem,
ca.full_address fullAddress,
(SELECT cci.client_name
FROM client_information cci
WHERE cci.client_no = pp.insured_no) insuredName,
pp.insured_no insuredNo,
ppp.prem_term premTerm,
ppi.prem_due_date premDate
FROM policy po
INNER JOIN rn_policy_service_info rpsi
ON rpsi.policy_no = po.policy_no
INNER JOIN branch_code_relation bcr
ON bcr.branch_code = po.branch_code
INNER JOIN policy_product pp
ON pp.policy_no = po.policy_no
AND pp.prod_seq = '1'
INNER JOIN policy_prem_info ppi
ON ppi.policy_no = po.policy_no
INNER JOIN staff_info si
ON si.emp_no = rpsi.service_no
INNER JOIN channel_type_tbl ctl
ON ctl.channel_type = po.channel_type
INNER JOIN branch_info bi
ON bi.branch_code = po.branch_code
INNER JOIN department_info di
ON di.dept_no = si.dept_no
INNER JOIN product p
ON p.product_code = pp.product_code
INNER JOIN policy_product_prem ppp
ON ppp.policy_no = po.policy_no
AND ppp.prod_seq = '1'
LEFT JOIN policy_contact_info pci
ON pci.policy_no = po.policy_no
LEFT JOIN client_address ca
ON ca.address_seq = pci.address_seq
LEFT JOIN gras_assign_failed_record gfr
ON gfr.policy_no = rpsi.policy_no
AND gfr.is_valid = 'Y'
WHERE
bcr.relative_grade > -1
AND gfr.policy_no IS NULL
AND ppi.prem_status = '1'
AND ppi.prem_source = '1'
AND po.duty_status = '1'
AND (rpsi.assign_rule IN ('01', '02') OR rpsi.assign_rule IS NULL)
AND pp.product_code NOT IN ('CBAN_TN1', 'CBAN_UN1', 'CBAN_VN1')
AND bcr.relative_branch_code = #{branchCode,jdbcType=VARCHAR}
AND ppi.PREM_DUE_DATE >= "TO_DATE"(#{nowMonth},'yyyy-MM-dd hh24:mi:ss')
AND ppi.PREM_DUE_DATE < "TO_DATE"(#{nextMonth},'yyyy-MM-dd hh24:mi:ss')
<choose>
<when test="channelTypeList != null and channelTypeList.size() > 0">
AND po.channel_type in
<foreach collection="channelTypeList" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</when>
<otherwise>
AND po.channel_type not in ('06','08','11')
</otherwise>
</choose>
应分getDatasExportToExcel
logUtils.info("(分单)getDatasExportToExcel方法参数开始："+DateUtil.getDateTimeFormat(new Date()));
List<String> header = new ArrayList<String>();// 第一行数据
List<List<String>> body = new ArrayList<List<String>>();
if(premDueDate != null) {
JSONObject params = new JSONObject();
params.put("branchCode", branchCode);
//需要分单的保单条件
params.put("premStatus","1");//缴费状态为已缴费
// params.put("channelType","01,02,04,05,07");//渠道
params.put("channelType","06,08,11");//渠道
params.put("premSource","1");//保费来源为客户1
// params.put("orphanMark","1");//0在职单1孤儿单,保单要为孤儿单
params.put("assignRule","03");//只考虑正常分单（01、null）和跨机构分单（02）
params.put("prodSeq","1");//产品序号，保单险种序号等于1
params.put("productCode","CBAN_TN1,CBAN_UN1,CBAN_VN1");//产品代码 ，保单险种不能为CBAN_TN1,CBAN_UN1,CBAN_VN1
transFormDate(premDueDate, params);
if (pageSize == null || pageSize == 0) {
pageSize = 10;
}
if (pageNum == null || pageNum == 0) {
pageNum = 0;
}
Map<String, JSONObject> result = service.queryAllotPolicys(params);
header.add("保单号");
header.add("分公司名称");
header.add("中心支公司名称");
//部门名称service_dept_name
header.add("部门名称");
//营销服务部名称bi_branch_level=4 bi_branch_name
header.add("营销服务部名称");
header.add("渠道");
header.add("投保人姓名");
header.add("投保人客户号");
header.add("业务员姓名");
header.add("业务员工号");
header.add("收展服务人员姓名");
header.add("收展服务人员工号");
header.add("主险名称");
header.add("缴次");
header.add("期交保费合计金额");
//被保人客户号pp_insured_no
header.add("被保人客户号");
//被保人姓名 insured_client_name
header.add("被保人姓名");
//缴费年期ppp_prem_term
header.add("缴费年期");
//保单联系地址ca_full_address
header.add("保单联系地址");
List<Object> in = (List<Object>) result.get("in").get("att_value");
List<Object> out = (List<Object>) result.get("out").get("att_value");
List<Object> allList = new ArrayList<>();
if (in != null){
allList.addAll(in);
}
if (out != null){
allList.addAll(out);
}
JSONObject attName = result.get("out").getJSONObject("att_name");
if (allList != null) {
/*for (int i = 0; i < 5000; i++) {
Object[] realData = (Object[]) in.get(i);*/
if (allList.size()>1000) {
//创建多线程
//多线程
AllotExportToExcelRunnable task = null;
MultiThreadHandler handler = new MultiThreadHandler();
task = new AllotExportToExcelRunnable(allList, body, attName);
handler.addTask(task);
//多线程执行
handler.run();
}else {
for (Object datasByParam : allList) {
Object[] realData = (Object[]) datasByParam;
List<String> data = new ArrayList<>();
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("po_policy_no")]));//保单号
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("second_branch_name")]));//分公司名称pbi_branch_name
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("third_branch_name")]));//中心支公司名称
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("service_dept_name")]));//部门名称
Object branchLevel=realData[attName.getInteger("bi_branch_level")];
if(branchLevel==null || !branchLevel.equals("04")){
data.add(null);//营销服务部名称
}else{
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("bi_branch_name")]));//营销服务部名称
}
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("ct_description")]));//渠道
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("ci_client_name")]));//客户姓名
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("ca_client_no")]));//客户号
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("agent_emp_name")]));//业务员姓名
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("agent_emp_code")]));//业务员工号
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("service_emp_name")]));//续收人员姓名
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("service_emp_no")]));//续收人员工号
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("pro_abbr_name")]));//主险名称
String rnpsi_pay_times = StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("rnpsi_pay_times")]);
if (rnpsi_pay_times!=null&&!"".equals(rnpsi_pay_times)) {
String split = rnpsi_pay_times.split("[.]")[0];
data.add(split);//缴次
}
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("pp_modal_total_prem")]));//期交保费
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("pp_insured_no")]));//被保人客户号
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("insured_client_name")]));//被保人姓名
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("ppp_prem_term")]));//缴费期
data.add(StringUtils.isNotNullAndEmptyBalck(realData[attName.getInteger("ca_detail_address")]));//保单联系地址
body.add(data);
}
}
}
}
Workbook workbook = ExportFileUtils.generateExcel("sheetName", header, body, "xlsx");
response.setContentType("application/octet-stream");
try {
response.setHeader("Content-disposition","attachment;filename="+java.net.URLEncoder.encode(branchCode+"-"+premDueDate+"应分单保单信息", "UTF-8")+".xlsx");
response.flushBuffer();
workbook.write(response.getOutputStream());
logUtils.info("(分单)getDatasExportToExcel方法参数结束："+DateUtil.getDateTimeFormat(new Date()));
}catch (IOException e){
logUtils.error(e);
}