1 package com.sinosoft.lis.service.bl.query;
2
3 import com.sinosoft.lis.db.LCAddressDB;
4 import com.sinosoft.lis.db.LCAppntDB;
5 import com.sinosoft.lis.db.LCContDB;
6 import com.sinosoft.lis.db.LPEdorItemDB;
7 import com.sinosoft.lis.pubfun.GlobalInput;
8 import com.sinosoft.lis.pubfun.PubFun;
9 import com.sinosoft.lis.schema.LCAddressSchema;
10 import com.sinosoft.lis.schema.LCAppntSchema;
11 import com.sinosoft.lis.schema.LCContSchema;
12 import com.sinosoft.lis.service.bean.*;
13 import com.sinosoft.lis.service.bl.PEdorApplyService;
14 import com.sinosoft.lis.service.workflow.ProcessService;
15 import com.sinosoft.lis.vschema.LPEdorItemSet;
16 import com.sinosoft.utility.CErrors;
17 import com.sinosoft.utility.ExeSQL;
18 import com.sinosoft.utility.SQLwithBindVariables;
19 import com.sinosoft.utility.SSRS;
20 import com.sinosoft.utility.VData;
21 import org.apache.log4j.Logger;
22 import org.junit.Test;
23
24 import java.util.ArrayList;
25 import java.util.List;
26
27 /**
28 * 犹豫期退保保单明细信息查询
29 * Created by MyPC on 2018/11/20.
30 */
31 public class PEdorWTDetailQueryService implements PEdorQueryInterface{
32 private Logger mLogger = Logger.getLogger(PEdorZPDetailQueryService.class);
33
34 /**
35 * 错误信息
36 */
37 private CErrors mErrors = new CErrors();
38
39 /**
40 * 往后面传输数据的容器
41 */
42 private VData mInputData = new VData();
43
44 private GlobalInput mGlobalInput = new GlobalInput();
45
46 /**
47 * 请求参数
48 */
49 private PEdorRequestBean mPEdorRequestBean = new PEdorRequestBean();
50
51 /**
52 * 返回参数
53 */
54 private PEdorResponseBean mPEdorResponseBean = new PEdorResponseBean();
55
56 //返回参数容器
57 private VData mResult = new VData();
58
59
60 @Override
61 public boolean submitData(VData cInputData, String cOperter) {
62 mInputData = (VData) cInputData.clone();
63 if (!getInputData(mInputData, cOperter)) {
64 return false;
65 }
66 // 进行业务处理
67 if (!dealData()) {
68 return false;
69 }
70 return true;
71 }
72
73 /**
74 * @param cInputData
75 * @param cOperate
76 * @return
77 * @desc 获取参数数据
78 */
79 private boolean getInputData(VData cInputData, String cOperate) {
80
81 mGlobalInput = (GlobalInput) mInputData.getObjectByObjectName("GlobalInput", 0);
82 mPEdorRequestBean = (PEdorRequestBean) mInputData.getObjectByObjectName("PEdorRequestBean", 0);
83 mPEdorRequestBean.setEdorAppDate(PubFun.getCurrentDate());
84 if (mGlobalInput == null || mPEdorRequestBean == null) {
85 mErrors.addOneError("接收前台数据失败!");
86 return false;
87 }
88
89 mPEdorRequestBean.setEdorAppDate(PubFun.getCurrentDate());
90 mPEdorRequestBean.setEdorAppName("Test");
91 mPEdorRequestBean.setAppntIDType("999");
92 mPEdorRequestBean.setAppntName("Test");
93 mPEdorRequestBean.setBankCode("000");
94 mPEdorRequestBean.setBankAccNo("1131232132");
95 mPEdorRequestBean.setAccName("Test");
96 mPEdorRequestBean.setEdorAppIDType("999");
97 mPEdorRequestBean.setEdorAppIDNO("0");
98 mPEdorRequestBean.setPersonName("Test");
99 mPEdorRequestBean.setPersonIDNO("0");
100
101 return true;
102 }
103
104 /**
105 * 数据操作业务处理
106 *
107 * @return: boolean
108 */
109 private boolean dealData() {
110 String tSql = " select RiskCode, "
111 +" (select RiskName "
112 +" from LMRisk "
113 +" where LMRisk.RiskCode = c.RiskCode), "
114 +" InsuredName,CValiDate,EndDate, "
115 +" Amnt,Mult, "
116 +" nvl((select sum(p.prem) "
117 +" from lcprem p "
118 +" where paystartdate <= '?EdorAppDate?' "
119 +" and payenddate >= '?EdorAppDate?' "
120 +" and p.polno = c.polno "
121 +" and p.payplantype in ('0')),0), "
122 +" nvl((select sum(p.prem) "
123 +" from lcprem p "
124 +" where paystartdate <= '?EdorAppDate?' "
125 +" and payenddate >= '?EdorAppDate?' "
126 +" and p.polno = c.polno "
127 +" and p.payplantype in ('01', '03')),0), "
128 +" nvl((select sum(p.prem) "
129 +" from lcprem p "
130 +" where paystartdate <= '?EdorAppDate?' "
131 +" and payenddate >= '?EdorAppDate?' "
132 +" and p.polno = c.polno "
133 +" and p.payplantype in ('02', '04')),0), "
134 +" polno,mainpolno,paytodate "
135 +" from LCPol c "
136 +" where ContNo = '?ContNo?' "
137 +" and ((appflag = '1' and "
138 +" (select count(*) "
139 +" from lccontstate s "
140 +" where trim(statetype) in ('Terminate') "
141 +" and trim(state) = '1' "
142 +" and trim(statereason) not in ('05', '06') "
143 +" and ((startdate <= '?EdorAppDate?' and '?EdorAppDate?' <= enddate) or "
144 +" (startdate <= '?EdorAppDate?' and enddate is null)) "
145 +" and s.polno = c.polno) = 0) or "
146 +" (appflag = '4' and (select count(*) "
147 +" from lccontstate s "
148 +" where trim(statetype) in ('Terminate') "
149 +" and trim(state) = '1' "
150 +" and trim(statereason) in ('05', '06') "
151 +" and ((startdate <= '?EdorAppDate?' and '?EdorAppDate?' <= enddate) or "
152 +" (startdate <= '?EdorAppDate?' and enddate is null)) "
153 +" and s.polno = c.polno) > 0)) ";
154 ExeSQL tExeSQL = new ExeSQL();
155 SQLwithBindVariables tSQLwithBindVariables = new SQLwithBindVariables();
156 tSQLwithBindVariables.sql(tSql);
157 tSQLwithBindVariables.put("EdorAppDate",PubFun.getCurrentDate());
158 tSQLwithBindVariables.put("ContNo",mPEdorRequestBean.getContNo());
159 SSRS tSSRS = new SSRS();
160 tSSRS = tExeSQL.execSQL(tSQLwithBindVariables);
161 if(tSSRS == null || tSSRS.getMaxRow() < 1 ){
162 mErrors.addOneError("该保单没有可操作的险种");
163 return false;
164 }
165 PEdorWTBean tPEdorWTBean = new PEdorWTBean();
166 List tAddRiskList = new ArrayList();
167
168 List tMainRiskList = new ArrayList();
169 for(int i = 1; i<= tSSRS.getMaxRow(); i++){
170 String tRiskCode = tSSRS.GetText(i,1); //险种编码
171 String tRiskName = tSSRS.GetText(i,2); //险种名称
172 String tInsuredName = tSSRS.GetText(i,3); //被保人名称
173 String tCValiDate = tSSRS.GetText(i,4); //险种生效日期
174 String tEndDate = tSSRS.GetText(i,5); //险种终止日期
175 String tAmnt = tSSRS.GetText(i,6);//保额
176 String tMult = tSSRS.GetText(i,7);//份数
177 String tPrem = tSSRS.GetText(i,8);//保费
178 String tHeathPrem = tSSRS.GetText(i,9);//健康加费
179 String tJobPrem = tSSRS.GetText(i,10);//职业加费
180 String tPolNo = tSSRS.GetText(i,11);//险种编码
181 String tMainPolNo = tSSRS.GetText(i,12);//主险编码
182 String tPayToDate = tSSRS.GetText(i,13);//缴费对应日期
183
184 if(tPolNo.equals(tMainPolNo)){
185 tPEdorWTBean.setMainRiskCode(tRiskCode);
186 tPEdorWTBean.setMainRiskName(tRiskName);
187 tPEdorWTBean.setInsuredName(tInsuredName);
188 tPEdorWTBean.setValidDate(tCValiDate);
189 tPEdorWTBean.setExpireDate(tEndDate);
190 tPEdorWTBean.setAmnt(tAmnt);
191 tPEdorWTBean.setMult(tMult);
192 tPEdorWTBean.setPrem(tPrem);
193 tPEdorWTBean.setHealthPrem(tHeathPrem);
194 tPEdorWTBean.setJobPrem(tJobPrem);
195 tPEdorWTBean.setPayDate(tPayToDate);
196
197 MainRisk tMainRisk = new MainRisk();
198 tMainRisk.setRiskCode(tRiskCode);
199 tMainRisk.setRiskName(tRiskName);
200 tMainRisk.setInsuredName(tInsuredName);
201 tMainRisk.setAmnt(tAmnt);
202 tMainRisk.setMult(tMult);
203 tMainRisk.setPrem(tPrem);
204 tMainRisk.setHealthPrem(tHeathPrem);
205 tMainRisk.setJobPrem(tJobPrem);
206 tMainRisk.setPayDate(tPayToDate);
207 tMainRiskList.add(tMainRisk);
208
209 }else{
210 AddtRisk tAddRisk = new AddtRisk();
211 tAddRisk.setRiskCode(tRiskCode);
212 tAddRisk.setRiskName(tRiskName);
213 tAddRisk.setPrem(tPrem);
214 tAddRisk.setMult(tMult);
215 tAddRisk.setAmnt(tAmnt);
216 tAddRisk.setHealthPrem(tHeathPrem);
217 tAddRisk.setJobPrem(tJobPrem);
218 tAddRisk.setPayDate(tPayToDate);
219 tAddRiskList.add(tAddRisk);
220 }
221 }
222 tPEdorWTBean.setAddtRisksList(tAddRiskList);
223
224 tPEdorWTBean.setMainRisksList(tMainRiskList);
225
226 //查询保单信息
227 LCContDB tLCContDB = new LCContDB();
228 tLCContDB.setContNo(mPEdorRequestBean.getContNo());
229 if(!tLCContDB.getInfo()){
230 mErrors.addOneError("该保单不存在！");
231 return false;
232 }
233 LCContSchema tLCContSchema = tLCContDB.getSchema();
234 tPEdorWTBean.setContNo(tLCContSchema.getContNo());
235 tPEdorWTBean.setAppntName(tLCContSchema.getAppntName());
236 //账户信息获取
237 String lSqlString = "select b.bankcode, (Select trim(codename) from ldcode where trim(code) = b.bankcode and codetype='bank'),b.bankaccno, b.accname" +
238 " from lccont a, ljtempfeeclass b" +
239 " where 1=1 and ( b.otherno = a.prtno or b.otherno=a.contno) and a.contno = '?ContNo?'" +
240 " and b.paymode in('04','14','15','16','17')" +
241 " and b.TempFeeType in ('11')" +
242 " order by b.enteraccdate desc, b.maketime desc ";
243 ExeSQL lExeSQL = new ExeSQL();
244 SQLwithBindVariables sqlbv1 = new SQLwithBindVariables();
245 sqlbv1.sql(lSqlString);
246 sqlbv1.put("ContNo",mPEdorRequestBean.getContNo());
247 SSRS lSSRS = lExeSQL.execSQL(sqlbv1);
248 tPEdorWTBean.setNewBankCode(lSSRS.GetText(1,1));//银行编码
249 tPEdorWTBean.setNewBankAccNo(lSSRS.GetText(1,3));//银行账号
250 tPEdorWTBean.setNewBankAccName(lSSRS.GetText(1,4));//户名
251 tPEdorWTBean.setNewBankName(lSSRS.GetText(1,2));//开户行
252
253
254 LCAppntDB tLCAppntDB = new LCAppntDB();
255 tLCAppntDB.setContNo(tLCContSchema.getContNo());
256 if(!tLCAppntDB.getInfo()){
257 mErrors.addOneError("对不起，该单投保人信息缺失！!");
258 return false;
259 }
260 LCAppntSchema tLCAppntSchema = tLCAppntDB.getSchema();
261
262 LCAddressDB tLCAddressDB = new LCAddressDB();
263 tLCAddressDB.setAddressNo(tLCAppntSchema.getAddressNo());
264 tLCAddressDB.setCustomerNo(tLCAppntSchema.getAppntNo());
265 if(!tLCAddressDB.getInfo()){
266 mErrors.addOneError("对不起，该单投保人地址信息缺失！!");
267 return false;
268 }
269
270 //2019-06-18电话修改 2019-06-21
271 String phoneSql=" SELECT distinct phone FROM lcaddress a WHERE a.customerno='?Customerno?' and a.phone is not null"
272 + " union"
273 + " SELECT distinct mobile FROM lcaddress a WHERE a.customerno='?Customerno?' and a.mobile is not null"
274 + " union"
275 + " SELECT distinct companyphone FROM lcaddress a WHERE a.customerno='?Customerno?' and a.companyphone is not null";
276 SQLwithBindVariables sqlbv2=new SQLwithBindVariables();
277 sqlbv2.sql(phoneSql);
278 sqlbv2.put("Customerno",tLCAppntSchema.getAppntNo());
279 SSRS sSSRS = new ExeSQL().execSQL(sqlbv2);
280 String tAppntMobile = "";
281 if(sSSRS == null || sSSRS.getMaxRow()<= 0){
282
283
284 }else{
285 for(int i = 1; i<= sSSRS.getMaxRow(); i++) {
286 //进行手机号拼接
287 if(sSSRS.GetText(i, 1) != null && sSSRS.GetText(i, 1).matches("1\\d{10}")&&!"".equals(sSSRS.GetText(i,1))) {
288 if (i < sSSRS.getMaxRow()) {
289 tAppntMobile += sSSRS.GetText(i, 1) + ",";
290
291 } else {
292
293 tAppntMobile += sSSRS.GetText(i, 1);
294 }
295 }
296
297 }
298 }
299 // 解决手机号尾部挂逗号的问题 2019-06-21
300 if(!"".equals(tAppntMobile)){
301 String last = tAppntMobile.substring(tAppntMobile.length()-1, tAppntMobile.length());
302
303 if(",".equals(last)){
304 tAppntMobile= tAppntMobile.substring(0, tAppntMobile.length()-1);
305
306 }
307 }
308
309
310 tPEdorWTBean.setMobilePhone(tAppntMobile);
311 tPEdorWTBean.setEdorType("WT");
312
313 //保全试算
314 PEdorApplyService tPEdorApplyService = new PEdorApplyService();
315 if (!tPEdorApplyService.edorTest(mPEdorRequestBean)) {
316 mErrors.addOneError(tPEdorApplyService.getErrors().getFirstError());
317 PEdorResponseBean tPEdorResponseBean = (PEdorResponseBean) tPEdorApplyService.getResult().getObjectByObjectName("PEdorResponseBean", 0);
318 String tEdorAcceptNo = tPEdorResponseBean.getEdoracceptNo();
319 mPEdorResponseBean.setEdoracceptNo(tEdorAcceptNo);
320 return false;
321 }
322 PEdorResponseBean tPEdorResponseBean = (PEdorResponseBean) tPEdorApplyService.getResult().getObjectByObjectName("PEdorResponseBean", 0);
323 String tEdorAcceptNo = tPEdorResponseBean.getEdoracceptNo();
324 mPEdorResponseBean.setEdoracceptNo(tEdorAcceptNo);
325
326 tPEdorWTBean.setContNo(mPEdorRequestBean.getContNo());
327 String tSQL = "select (select RiskName from LMRisk b where b.RiskCode = a.RiskCode),a.cvalidate,a.enddate from lcpol a where a.mainpolno = a.polno and a.contno='?Contno?'";
328 tSQLwithBindVariables = new SQLwithBindVariables();
329 tSQLwithBindVariables.sql(tSQL);
330 tSQLwithBindVariables.put("Contno", mPEdorRequestBean.getContNo());
331
332 SSRS ttSSRS = new ExeSQL().execSQL(tSQLwithBindVariables);
333 if (ttSSRS == null || ttSSRS.MaxRow == 0) {
334 mErrors.addOneError("没有查询到主险信息!");
335 return false;
336 }
337
338 //银保通
339 tPEdorWTBean.setMainRiskName(ttSSRS.GetText(1, 1));
340 tPEdorWTBean.setYBTRiskName(ttSSRS.GetText(1, 1));
341 tPEdorWTBean.setValidDate(ttSSRS.GetText(1, 2));
342 tPEdorWTBean.setExpireDate(ttSSRS.GetText(1, 3));
343 tPEdorWTBean.setOccurBala(tPEdorResponseBean.getGetMoney());
344 tPEdorWTBean.setSXMoney(tPEdorResponseBean.getSXFee());
345
346
347 List tWTList = new ArrayList();
348 tWTList.add(tPEdorWTBean);
349 mPEdorResponseBean.setPEdorWTList(tWTList);
350
351 ProcessService tProcessService = new ProcessService();
352 if(!tProcessService.edorCancel(tEdorAcceptNo)){
353 return false;
354 }
355
356 return true;
357 }
358
359 @Override
360 public CErrors getErrors() {
361 return mErrors;
362 }
363
364 @Override
365 public VData getResult() {
366 mResult.clear();
367 mResult.add(mPEdorResponseBean);
368 return mResult;
369 }
370
371 @Test
372 public void aaaa(){
373 // mPEdorRequestBean.setContNo("201937170412000261");
374 // dealData();
375
376 // String str1="12345@qq.com";
377 // //查询@在字符串中出现的位置
378 // int index=str1.indexOf("@"); //下标从0开始
379 // System.out.println("注意String字符串的下表是从0开始的");
380 // System.out.println("@在字符串中出现的位置:"+index);
381 // //查询q在字符串中最后一次出现的位置
382 // int index2=str1.lastIndexOf("q");
383 // System.out.println("q在字符串中最后一次出现的位置:"+index2);
384 // //获取@之后的所有字符串 subString(int beginIndex) 包含beginIndex
385 // System.out.println("获取@之后的所有字符串:"+str1.substring(index+1));
386 // /*
387 // * 查询 qq
388 // * subString(int beginIndex，int endIndex)
389 // * 01.找到点的位置
390 // * 02.不包含 endIndex
391 // */
392 // int index3=str1.indexOf(".");
393 // String result = str1.substring(index+1, index3);
394 // System.out.println("result====>"+result);
395
396 //去除空格
397 // String str1=" abc def ";
398 // System.out.println("空格计算长度："+str1.length());
399 // //去除字符串两边的空格
400 // str1=str1.trim();
401 // System.out.println("去除空格之后内容："+str1);
402 // System.out.println("去除空格之后："+str1.length());//中间的空格去除不了
403 // String str1="abcdef";
404 // //把a替换成6
405 // str1 = str1.replace("a", "6");
406 // System.out.println(str1);
407 // //是否以6开始
408 // System.out.println("是否以6开始:"+str1.startsWith("6"));
409 // System.out.println("是否以a结尾:"+str1.endsWith("a"));
410
411
412 // String str1="abcdef";
413 // //截取一个字符 返回值是char
414 // char a =str1.charAt(3);//下标是从0开始的
415 // System.out.println(a);
416 // String str1="abcdef";
417 // //将字符存储在字节数组中
418 // byte[] bytes = str1.getBytes();
419 // for (byte b : bytes) {
420 // System.out.println((char)b);
421
422 // String str1="abc";
423 // String str2="def";
424 // //之前的字符串相加
425 // System.out.println(str1+str2);
426 // //现在concat（String str）
427 // System.out.println(str1.concat(str2));
428
429 // String str1="abc";
430 // //是否包含某个指定的字符
431 // System.out.println("是否包含a这个字符："+str1.contains("a"));
432
433
434 //01.定义变量
435 String name="xiaohei";
436 //02.定义操作字符串的次数
437 int count=2000000;
438 //03.设置开始时间
439 long beginTime=System.currentTimeMillis();
440 for (int i = 0; i <count/100; i++) {
441 name+="haha";
442 }
443 //03.设置结束时间
444 long endTime=System.currentTimeMillis();
445 System.out.println("String的执行时间："+(endTime-beginTime));
446 System.out.println("****************************");
447 name="xiaohei";
448 //04.使用StringBuffer
449 StringBuffer buffer=new StringBuffer(name);
450 beginTime=System.currentTimeMillis();
451 for (int i = 0; i <count; i++) {
452 buffer=buffer.append("haha");
453 }
454 endTime=System.currentTimeMillis();
455 System.out.println("StringBuffer的执行时间："+(endTime-beginTime));
456 System.out.println("****************************");
457 name="xiaohei";
458 //05.使用StringBuilder
459 StringBuilder builder=new StringBuilder(name);
460 beginTime=System.currentTimeMillis();
461 for (int i = 0; i <count; i++) {
462 builder=builder.append("haha");
463 }
464 endTime=System.currentTimeMillis();
465 System.out.println("StringBuilder的执行时间："+(endTime-beginTime));
466
467 }
468 }
