# ETL详细设计
## 待沟通
- 目前上游无法提供增量数据，所以每次批处理必须全量，效率低
- 受益人只有三要素证件号码、证件类型、姓名，上游客户号为null
- 客户等级是否有效的判断规则（有效标志IS_DELETE、失效日期END_DATE全为null，生效日期是否有作用），客户ID（CUSTOMER_ID）是否与核心客户号一致
- 数据字典中不同表之间存在大量的重复字段，且从测试环境来看，这些表存在差异，如保单数量:HEALTH_INSURANCE_LISTING << POLICY_INFORMATION < INSURANCEINFO，但没有文档说明，无法保证所取字段的正确性。
- 需提供结案日期
## 个人信息（Individual）   
1. 从保单主表（POLICY_INFORMATION）中获取投保人的上游客户号和五要素，得到`$投保人列表$` 

code


    SELECT CUSTOMERNO,NAME,sex,BIRTHDAY,IDTYPE,IDNO,min(CVALIDATE) FROM POLICY_INFORMATION GROUP BY CUSTOMERNO,NAME,sex,BIRTHDAY,IDTYPE,IDNO;   
2. 从个险承保清单自助分析表（POLICY_INFORMATION）中获取被保人的上游客户号和五要素，得到`$被保人列表$`   

code

    SELECT INSUREDNO,INSUREDNAME,INSUREDSEX,INSUREDBIRTHDAY,INSUREDIDTYPE,INSUREDIDNO,min(CVALIDATE) FROM POLICY_INFORMATION GROUP BY INSUREDNO,INSUREDNAME,INSUREDSEX,INSUREDBIRTHDAY,INSUREDIDTYPE,INSUREDIDNO;

3. ~~从个险承保清单自助分析（HEALTH_INSURANCE_LISTING）中获取受益人的上游客户号和三要素，得到`$受益人列表$`，实际数据上游客户全为null，同时五要素不齐，故暂时不考虑~~

code

    SELECT BENEFICIARY_CUSTOMERNO,BENEFICIARY_NAME,BENEFICIARY_IDTYPE,BENEFICIARY_IDNO FROM HEALTH_INSURANCE_LISTING GROUP BY BENEFICIARY_CUSTOMERNO,BENEFICIARY_NAME,BENEFICIARY_IDTYPE,BENEFICIARY_IDNO;
 
4. 将`$投保人列表$`，`$被保人列表$`  合并去重，得到`$投被保人列表$`，并按保单生效日期（CVALIDATE）从早到晚排序（客户号生成规则之一）
5. 遍历`$投被保人列表$`  
6. **标识及证件信息**：根据五要素生成CPID，并生成代理主键IndID同时将CPID以及列表中其他字段分别插入个人信息表（Individual）。注意：要根据证件类型，将证件号码插入不同的字段。（如：证件类型为0，则证件号码插入身份证字段）
7. **todo 等级信息**：根据上游客户号，从客户等级表（T_CUSTOMER_CLASS）获取客户等级、贡献度分、家庭加分、续期加分、保单加分、总分值。  

code
 
    select CLASS_VALUE,CONTRIBUTION_VALUE,AWARDED3,AWARDED2,AWARDED1,TOTAL_VALUE from T_CUSTOMER_CLASS where CUSTOMER_ID = 上游客户号

8. **籍贯、职业**：根据上游客户号，从个险承保清单自助分析（HEALTH_INSURANCE_LISTING）获取投保人籍贯，投保人职业代码，投保人职业名称。注意，此表投保人和被保人都有职业代码名称，取最近的一条记录

code

    select APPNTHOMEADDRESS,APPNTOCCUPATIONCODE,APPNTOCCUPATIONNAME from HEALTH_INSURANCE_LISTING where APPNTNO = 上游客户号 order by POLAPPLYDATE desc

## 保单（InsuranceArrangement）
1. 从新契约签单明细清单（INSURANCEINFO）表中获取相关字段插入到InsuranceArrangement表，由于INSURANCEINFO表中是以保单号唯一的，所以直接插入。语法见下，字段映射详见模型文档

code

    INSERT INTO InsuranceArrangement(PolicyNo,PANo) 
    SELECT contno,prtno
    FROM  INSURANCEINFO 

1. 用保单信息表（POLICY_INFORMATION）去更新InsuranceArrangement，由于POLICY_INFORMATION不是以保单号唯一的，所以先要去重。由于星环inceptor不支持如下在update中使用group by的语法,所以可以对POLICY_INFORMATION按保单号去重后存入临时表`POLICY_INFORMATION_tmp`

code

    insert into POLICY_INFORMATION_tmp(contno,CVALIDATE,ENDDATE)
    SELECT contno,max(CVALIDATE),max(ENDDATE)	
    ROM POLICY_INFORMATION
    GROUP BY contno

3. 更新临时表`POLICY_INFORMATION_tmp`中投保人（ApplicantID），主被保险人（InsuredID）为individual表的个体ID(IndID),可根据POLICY_INFORMATION.CUSTOMERNO关联individual上游客户号（SCustID），得到IndID，支持update，临时表设置为ORC事务表
   
code

    UPDATE POLICY_INFORMATION_tmp
    SET ApplicantID =
    (SELECT ApplicantID
    FROM individual
    WHERE (POLICY_INFORMATION_tmp.CUSTOMERNO = individual.SCustID))
    WHERE 1=1;

4. 最后用临时表更新InsuranceArrangement（code2）,为了支持update，InsuranceArrangement表设置为ORC事务表。语法见下，字段映射详见模型文档

code2

    UPDATE InsuranceArrangement
    SET (PINDate,PMDate) =
    (SELECT CVALIDATE,ENDDATE
    FROM POLICY_INFORMATION_tmp
    WHERE (POLICY_INFORMATION_tmp.contno = InsuranceArrangement.PolicyNo))
    WHERE 1=1;

## 理赔（InsuranceClaimThread）
1. 首先将个单理赔表（INSURANCE_CLAIM）按保单和赔案号去重并存入临时表`INSURANCE_CLAIM_tmp`

code

    insert into INSURANCE_CLAIM_tmp(ontno,clmno,RISKDATE,INSUREDNO,RPTDATE,LLOCCURREASON,RGTDATE,ACCIDENTDETAIL,LLCLAIMSTATE)
    SELECT contno,clmno,max(RISKDATE),max(INSUREDNO),max(RPTDATE),max(LLOCCURREASON),max(RGTDATE),max(ACCIDENTDETAIL),max(LLCLAIMSTATE)
    FROM INSURANCE_CLAIM GROUP BY contno,clmno

2. 更新`INSURANCE_CLAIM_tmp`中出险人为individual表的个体ID(IndID)
3. 将`INSURANCE_CLAIM_tmp`相应字段直接插入到InsuranceClaimThread表中，字段映射详见模型文档。
## 接触(Communication)
1. 
## 事件(PartyTimeLine)
1. 目前主要有以下事件，一个事件一条记录，包括开始时间和结束时间
- **投保**：参与方为投保人，Scenario='0001',SDate=InsuranceArrangement.PADate,EndDate=InsuranceArrangement.PISDate
- **保全**:todo 等待上游提供数据字典
- **报案**:参与方为报案人，Scenario='0003',SDate=InsuranceClaimThread.CNDate,EndDate= todo 等待上游提供结案日期字段
- **投诉**:
- **咨询**: