test.txt 18.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 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406
 <plugin>
		        <groupId>org.apache.maven.plugins</groupId>
		        <artifactId>maven-surefire-plugin</artifactId>
		        <version>2.18.1</version>
		        <configuration>
		          <skipTests>true</skipTests>
		        </configuration>
		      </plugin>
		      
		      currentpolicyaddress
		      
		      ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
		      
		      

select 
    rpsi.POLICY_NO policyNo,rpsi.service_no servicerNo,po.BRANCH_CODE branchCode,si.EMP_NAME empName,
    po.CHANNEL_TYPE AS channelType,bi.BRANCH_NAME branchName,ppi.PREM_DUE_DATE premDate,ctl.DESCRIPTION AS description
     
    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_prem_info ppi on ppi.policy_no =po.policy_no
      left 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
      left join gras_assign_failed_record gfr  on gfr.policy_no=rpsi.policy_no 
    where 
    bcr.relative_grade>-1
    and gfr.policy_no is null
		and bcr.relative_branch_code = '864200'
		AND ppi.PREM_DUE_DATE >= "TO_DATE"('2018-12-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
		AND ppi.PREM_DUE_DATE <= "TO_DATE"('2018-12-30 00:00:00','yyyy-MM-dd hh24:mi:ss')
    and po.CHANNEL_TYPE in('01','02','03','04','05','07','09');
    
    
    
    
    select 
count(*)
     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
      left join gras_assign_failed_record gfr  on gfr.policy_no=rpsi.policy_no 
    where 
      bcr.relative_grade>-1
    and gfr.policy_no is  null
    and ppi.prem_status='1'
  
and ppi.prem_source='1'
and pp.prod_seq='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 = '865104'
		AND ppi.PREM_DUE_DATE >= "TO_DATE"('2018-12-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
		AND ppi.PREM_DUE_DATE < "TO_DATE"('2019-01-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
    and po.CHANNEL_TYPE in('01','02','03','04','05','07','09');
    
    
    
    
    
    P000000001352005-H000000000005697
    
from policy po
inner join   rn_policy_service_info rpsi on  rpsi.policy_no=po.policy_no
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   branch_code_relation bcr on bcr.branch_code=po.branch_code
inner join   CHANNEL_TYPE_TBL CTL on CTL.CHANNEL_TYPE = PO.CHANNEL_TYPE
--inner join   staff_info si on si.emp_no = rpsi.service_no
inner join   branch_info bi  on  bi.branch_code = po.branch_code
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 bcr.relative_branch_code = '864200'
and gfr.policy_no is  null
and ppi.prem_status='1'
and ppi.prem_source='1'
and pp.prod_seq='1'
and rpsi.ASSIGN_BRANCH_CODE is null
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 ppi.PREM_DUE_DATE >= "TO_DATE"('2018-12-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
AND ppi.PREM_DUE_DATE < "TO_DATE"('2019-01-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
AND po.CHANNEL_TYPE not in('06','08','11');


分单结果批量调整满足条件
1.失效件:
(1)保单进入失效月次月至失效日起两年内;
(2)失效原因满足下列其中一项:
     保全:未在规定宽限期内缴纳续期保费;
     保全:借款本金和利息和超过保单现金价值;
     保全:保单的现金价值不足以垫缴保费;
(3)保单渠道与收展服务人员岗位相匹配。
2.缓缴件
(1)保单有效状态的长险(万能险);
(2)保单渠道与收展服务人员岗位相匹配。





自定义查询保单落图:gras/falling/queryPageData----》FallingMapService.queryPageDatas()
获取分区区域列表:gras/branchGeo/getBranchGeoInfoPageByCode----》grasBranchGeoService.selectBranchGeoInfoPageByCode()
获取区域点面数据:gras/branch/getBranchGeoByBranchCode---》branchInfoService.getBranchGeoByBranchCode()
根据机构代码获取渠道:/gras/branchRegion.getBranchRegion--》grasBranchGeoService.getBranchRegion()

根据机构号查询保单:/gras/resultCheck/selectByBranchCode----》ResultCheckService.selectByBranchCode()

根据传入条件查询保单信息:/gras/resultCheck/queryPolicy--》ResultCheckService.queryPolicy()

FallingMapService.outInResult()
FallingMapService.resultput()
ElasticSearchServiceImpl类
ElasticSearchPolicyServiceImpl类

/gras/menu/updateMenus---》menuService.updateByRoleIdAndMenus()

/gras/resultCheck/getDatasExportToExcel----》ResultCheckService.selectForExcel()

批量调整:gras/resultCheck/adjustEmp----》ResultCheckService.adjustEmp






 1:目前系统中删除操作涉及的表有:GRAS_BRANCH_GEO、GRAS_BRANCH_STYLE、GRAS_AREA_STAFF、GRAS_SYS_ROLE_MENU、GRAS_SYS_MENU、GRAS_ASSIGN_FAILED_RECORD
 
 
 

 
 
 

]863605用户,调整保单号:P000000042641127,收展服务人员:H000000000005697,保单渠道为银代,收展服务人员岗位为银代收展专员


select 

rpsi.POLICY_NO policyNo,rpsi.service_no servicerNo,po.BRANCH_CODE branchCode,si.EMP_NAME empName,
    po.CHANNEL_TYPE AS channelType,bi.BRANCH_NAME branchName,ppi.PREM_DUE_DATE premDate,ctl.DESCRIPTION AS description
from policy po
inner join   rn_policy_service_info rpsi on  rpsi.policy_no=po.policy_no
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   CHANNEL_TYPE_TBL CTL on CTL.CHANNEL_TYPE = PO.CHANNEL_TYPE
inner join   staff_info si on si.emp_no = rpsi.service_no
inner join   branch_info bi  on  bi.branch_code = po.branch_code
left join gras_assign_failed_record gfr  on gfr.policy_no=rpsi.policy_no  and gfr.is_valid='Y'
where 

 gfr.policy_no is  null
and ppi.prem_status='1'
and ppi.prem_source='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 ppi.PREM_DUE_DATE >= "TO_DATE"('2018-12-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
AND ppi.PREM_DUE_DATE < "TO_DATE"('2019-01-01 00:00:00','yyyy-MM-dd hh24:mi:ss')
AND po.CHANNEL_TYPE not in('06','08','11')
and rpsi.ASSIGN_BRANCH_CODE in
(select bcr.branch_code from  branch_code_relation bcr where 
 bcr.relative_grade>-1 and bcr.relative_branch_code='863600');
 
 
 
 
 
 
要在代码中写一个机制,连接-更新-完成数据库的一个事物时写一次用户信息,确保一次数据库操作(事务)都有写用户信息。这样触发器才能获取到正确的用户

    
    
    
    
    
    
    
    
    
    
    
    "UIWL_AN1","UIED_AN1","UIAN_DN1","UIAN_EN1","UBWL_AN1","UBEN_AN1","UBED_CN1"
    
    
    
    
四、保单离职交接规则:
1判断是否为离职交接保单前置条件:此规则定义的离职人员是指离职时间为操作离职保单交接这个动作的当月或前一个月;
例如:人员在操作批量调整的当月和上一月离职为离职交接
2.离职人员名下所有已实收及未收保单均要交接,并计入新接收人考核;
3.离职人员名下未收件交接遵循“应缴月为同一个月份的保单只能交接给同一个人,不允许分别交接给多个人员”,
注意:同一个月份保单交接,批量导入时,RN系统是从第一行开始往下校验,即“如果操作同一个月份的保单交接,
服务人员填了多个接收人员工号,系统会先校验第一行的工号,后面出现的工号不允许再交接该月的保单”。
4.不同月份的保单可以分别交接给不同的人或交接给同一个人
例如:A在2018年4月离职,A名下所有保单有2月、3月、4月、5月、6月的保单需交接。2月的单交接给B,
(2月的单不可以一部分交接给B,一部分又交接给C)、3月、4月的单可以交接给C、5月的单可以交接给D、6月的单可以交接给E、或者2月-6月的单全部交接给B。



System[gras]


void setUser(String userName);
<parameterMap id="stsReturn_param" type="java.lang.String">
          <parameter property="userName" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"></parameter>
   </parameterMap>
  <update  id="setUser" parameterMap="stsReturn_param">
         call gracde.gra_sys_package.set_user(?)
  </update>
  
  
  
 private List<Map<String, String>> quitAssign(List<String> policyNoList, List<JSONObject> policyList)
    {
        //key:应缴日(1812)年份加月份   value{现服务人员代码,服务人员代码}
        Map<String, Map> map = new HashMap<String, Map>();
        //{现服务人员代码,服务人员代码}
        Map<String, String> policyMap = new HashMap<String, String>();
        //最后符合条件的保单信息
        List<Map<String, String>> policySuccessList = new ArrayList<>();
        Map<String, String> policySuccessMap = null;
        Date nowDate = new Date();
        //获取当前年份
        int nowYear = DateUtil.getYear(nowDate);
        int nowMonth = DateUtil.getMonth(nowDate);
        Map<String,String> paramMap=new HashMap<String,String>();
        for (String item : policyNoList)
        {
            for (JSONObject policyJson : policyList)
            {
                String policyNo = policyJson.getString("policyNo");
                String servicerNo = policyJson.getString("servicerNo");
                try
                {
                    if (item.equals(policyNo))
                    {
                        policySuccessMap = new HashMap<>();
                        /**
                         * 处理离职交接规则
                         */
                        //根据保单号从数据库取现服务人员
                        RNPolicyServiceInfo rnPolicyServiceInfo = servicerInfoService.selectByPolicyNo(policyNo);
                        StaffInfo staffInfo = null;
                        if (rnPolicyServiceInfo != null)
                        {
                            staffInfo = staffInfoService.selectByPrimaryKey(rnPolicyServiceInfo.getServiceNo());
                        }
                        int month = 0;
                        int year = 0;
                        if (staffInfo != null && staffInfo.getLeaveDate() != null)
                        {
                            Date leaveDate = DateUtil.formatDate(staffInfo.getLeaveDate(), "yyyy-MM-dd");
                            month = DateUtil.getMonth(leaveDate);
                            year = DateUtil.getYear(leaveDate);
                        }
                        //1:先判断该保单的现服务人员是否在当月或者上月离职    人员离职时间:STAFF_INFO.LEAVE_DATE
                        if (quitTime(nowYear, nowMonth, year, month))
                        {
                            //2:离职人员名下所有已实收及未收保单均要交接,并计入新接收人考核;    ---这点可以不管,只是解释离职人员名下的保单都可以交接

                            //3.离职人员名下未收件交接遵循“应缴月为同一个月份的保单只能交接给同一个人,不允许分别交接给多个人员”;未收件:保单缴费状态为1的时候为未收件,即POLICY_PREM_INFO.PREM_STATUS=1
                            //例如:A人员当月离职,他12月名下有3笔保单,分别为1,2,3,分别会出现两种情况,1:A人员应缴日在12月的保单从未有过交接记录     2:A人员应缴日在12月的保单存在交接记录
                            //情况1:上传文件中,把1,2,3分别分配给B,C,D,文件格式如下:

                            //数据库获取保单应缴日,得到应缴月
                            PolicyPremInfo policyPremInfo = premInfoService.selectByPolicyNo(policyNo);
                            Date premDueDate = DateUtil.formatDate(policyPremInfo.getPremDueDate(), "yyyy-MM-dd");
                            int premDueMonth = DateUtil.getMonth(premDueDate);
                            int premDueYear = DateUtil.getYear(premDueDate);
                            //那么这种情况,1先分给了B,那根据规则同一月保单只能分配给同一人,那么2,3保单就只能B
                            //校验如下,校验保单交出服务人员是否和该保单现服务人员相同,若不相同则没有发生保单离职交接记录
                            //注:数据库获取保单服务人员交接记录表,该保单最近一次交接记录,获取交出服务人员字段   RN_POLICY_SERVICER_CHG_RECORD.EARLIER_AGENT
                            String startMonths = DateUtil.getPerFirstDayOfDate(policyPremInfo.getPremDueDate(), DateUtil.DATETIME_DEFAULT_FORMAT);
                            String endMonth = DateUtil.getPerLastDayOfDate(policyPremInfo.getPremDueDate(), DateUtil.DATETIME_DEFAULT_FORMAT);
                            
                            paramMap.put("sreceiveNo", rnPolicyServiceInfo.getServiceNo());
                            paramMap.put("startMonths", startMonths);
                            paramMap.put("endMonth", endMonth);
                            RNPolicyServicerChgRecord record = changeService.queryLastTimeServicerChg(paramMap);
                            /**保       原         接收
                             * p1  H1  H2
                             * p2  H1  H3
                             */

                            //交出服务人员代码
                            String outSer = null;
                            //接收服务人员代码
                            String receiveSer = null;

                            //现服务人员代码
                            String nowServicerNo = rnPolicyServiceInfo.getServiceNo();

                            if (record == null
                                    || DateUtil.compareToDate(staffInfo.getLeaveDate(),
                                            DateUtil.getDateFormat(record.getUpdatedDate())) != -1)
                            {

                                //情况一:没有记录,或者离职时间大于调整时间(离职之前交接的保单)      小于根据应缴月从map中取值(应缴月值应该每次去查保单的应缴日),从值中取该保单号的现服务人员,

                                Map<String, String> m = map.get(String.valueOf(premDueYear)
                                        + String.valueOf(premDueMonth));
                                //现服务人员代码
                                String nowServicer = null;
                                //准备交接的服务人员代码
                                String servicer = null;
                                /**
                                 * map  H1:H2
                                 */
                                if(m!=null){
                                    for (Map.Entry<String, String> s : m.entrySet())
                                    {
                                        if (s.getKey().equals(nowServicerNo))//H1==1
                                        {
                                            nowServicer = s.getKey();
                                            servicer = s.getValue();//H2
                                        }
    
                                    }
                                }
                                //情况1:如果没值,那就符合条件
                                //情况2:如果有值,根据同月只能分配给同一人员原则,这里就设置为map中的服务人员
                                if (nowServicer != null)
                                {
                                    servicerNo = servicer;
                                }

                            }
                            else
                            {
                                outSer = record.getEarlierAgent();
                                receiveSer = record.getNextAgent();
                                //情况二:若保单交出服务人员和该保单现服务人员相同,则说明之前有发生过保单离职交接,那根据一个月中只能分给同一个服务人员原则,那只能分配给接收服务人员
                                //grasSysTemp.setServicerNo(ReceiveSer);
                                servicerNo = receiveSer;
                            }
                            policyMap.put(nowServicerNo, servicerNo);
                            map.put(String.valueOf(premDueYear) + String.valueOf(premDueMonth), policyMap);
                        }
                        /**
                         * end
                         */

                        policySuccessMap.put("policyNo", policyNo);
                        policySuccessMap.put("servicerNo", servicerNo);
                        policySuccessList.add(policySuccessMap);
                        break;
                    }
                }

                catch (Exception e)
                {
                    e.printStackTrace();
                    policySuccessMap.put("policyNo", policyNo);
                    policySuccessMap.put("servicerNo", servicerNo);
                    policySuccessList.add(policySuccessMap);
                    break;
                }
            }
        }
        return policySuccessList;

    }

    private boolean quitTime(int nowYear, int nowMonth, int year, int month)
    {
        //同年--当月&上月
        if (nowYear == year && (nowMonth == month || nowMonth == (month - 1)))
        {
            return true;
        }

        //去年  ---上月
        if ((nowYear - year) == 1 && month == 12)
        {
            return true;
        }

        return false;
    }
RNPolicyServicerChgRecord queryLastTimeServicerChg(Map<String,String> paramMap);


 select * from (    
		select * from RN_POLICY_SERVICER_CHG_RECORD rnpscr 
		where rnpscr.policy_no in (
		select rpsi.policy_no from rn_policy_service_info rpsi,policy_prem_info ppi
		where ppi.policy_no=rpsi.policy_no
		and rpsi.service_no=#{sreceiveNo}
		and ppi.prem_due_date >= "TO_DATE"(#{startMonths},'yyyy-MM-dd hh24:mi:ss')
		and ppi.prem_due_date &lt; "TO_DATE"(#{endMonth},'yyyy-MM-dd hh24:mi:ss')
		)
	   and rnpscr.change_mode='0'
	   order by rnpscr.UPDATED_DATE DESC
	   )where rownum = 1