123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676 |
- CREATE TABLE shanghailifeecif.edorinfo(
- contno string DEFAULT NULL COMMENT '保单号',
- edortype string DEFAULT NULL COMMENT '管理机构',
- lppostaladdress string DEFAULT NULL COMMENT '原客户地址',
- lcpostaladdress string DEFAULT NULL COMMENT '现客户地址',
- lpmobile string DEFAULT NULL COMMENT '原手机号',
- lcmobile string DEFAULT NULL COMMENT '现手机号 ',
- lpidtype string DEFAULT NULL COMMENT '原证件类型',
- lcidtype string DEFAULT NULL COMMENT '现证件类型 ',
- lpidno string DEFAULT NULL COMMENT '原证件号码',
- lcidno string DEFAULT NULL COMMENT '现证件号码 ',
- EDORAPPDATE string DEFAULT NULL COMMENT '保全申请日期',
- EDORCVALIDATE string DEFAULT NULL COMMENT '保全生效日期 '
- )
- COMMENT '保全'
- STORED AS csvfile;
- CREATE TABLE shanghailifeecif.audit_edorlist(
- managecom varchar2(200) DEFAULT NULL COMMENT '管理机构',
- managename varchar2(200) DEFAULT NULL COMMENT '管理机构名称',
- salechnl varchar2(200) DEFAULT NULL COMMENT '销售渠道',
- salechnlname varchar2(64) DEFAULT NULL COMMENT '销售渠道名称',
- selltype varchar2(64) DEFAULT NULL COMMENT '合作渠道代码',
- selltypename varchar2(64) DEFAULT NULL COMMENT '合作渠道名称',
- contno varchar2(200) DEFAULT NULL COMMENT '保单号',
- riskcodes varchar2(200) DEFAULT NULL COMMENT '险种代码',
- risknames varchar2(200) DEFAULT NULL COMMENT '险种名称',
- risktypes varchar2(64) DEFAULT NULL COMMENT '险种类型',
- signdate timestamp DEFAULT NULL COMMENT '承保日期',
- appntname varchar2(200) DEFAULT NULL COMMENT '投保人',
- prem decimal(10,2) DEFAULT NULL COMMENT '保费',
- edorname varchar2(256) DEFAULT NULL COMMENT '保全类型',
- edoracceptno varchar2(256) DEFAULT NULL COMMENT '保全受理号',
- edorno varchar2(256) DEFAULT NULL COMMENT '批单号',
- edorvalidate timestamp DEFAULT NULL COMMENT '保全生效日期',
- customerhandleflag varchar2(64) DEFAULT NULL COMMENT '是否亲办',
- operator varchar2(256) DEFAULT NULL COMMENT '操作人',
- approveoperator varchar2(256) DEFAULT NULL COMMENT '审批人',
- getmoney decimal(10,2) DEFAULT NULL COMMENT '保全退补费金额',
- cusappdate timestamp DEFAULT NULL COMMENT '客户申请日期',
- payyears varchar2(64) DEFAULT NULL COMMENT '缴费年期',
- agentname varchar2(256) DEFAULT NULL COMMENT '代理人姓名',
- edorstate varchar2(64) DEFAULT NULL COMMENT '保全状态',
- ins_time timestamp DEFAULT NULL
- )
- COMMENT '保全清单';
- ======================================================================
- =================================目的表结构=================================
- ======================================================================
- CREATE TABLE shanglifeecif.PartyTimeLine(
- TripID string NOT NULL COMMENT '旅程ID' ,
- PartyID string NOT NULL COMMENT '参与方ID' ,
- Name string COMMENT '参与方名称' ,
- PCertID string COMMENT '参与方证件号码' ,
- PPhone string COMMENT '参与方手机' ,
- PRole string COMMENT '参与方角色 投保人、被保人、受益人' ,
- Scenario string COMMENT '场景 投保 保全 报案 咨询 投诉 其他' ,
- SDate DATE COMMENT '发生日期' ,
- ThreadID string COMMENT '关联ID 可关联至更详细信息,如保单ID、报案ID等' ,
- ParentID string COMMENT '父ID 如报案对应的保单ID' ,
- Describe string COMMENT '描述' ,
- Channel string COMMENT '来源' ,
- EndDate DATE COMMENT '完成日期' ,
- CREATED_BY string COMMENT '创建人' ,
- CREATED_TIME DATE COMMENT '创建时间' ,
- UPDATED_BY string COMMENT '更新人' ,
- UPDATED_TIME DATE COMMENT '更新时间'
- ) COMMENT '参与方事件 '
- STORED AS ES
- with shard number 10
- replication 1;
- ==================================================初始化数据====================================================================
- DROP SEQUENCE IF EXISTS globaleTripID_sequence;
- CREATE OR REPLACE PROCEDURE shanglifeecif.init_partytimeline_main()
- IS
- DECLARE
- partytimeline_count int
- strsql string
- BEGIN
-
- select count(0) into partytimeline_count from shanglifeecif.PartyTimeLine;
- partytimeline_count:=partytimeline_count+1;
- DBMS_OUTPUT.PUT_LINE(partytimeline_count);
- init_PartyTimeLine_edorlist(partytimeline_count)
-
-
- select count(0) into partytimeline_count from shanglifeecif.PartyTimeLine;
- partytimeline_count:=partytimeline_count+1;
- DBMS_OUTPUT.PUT_LINE(partytimeline_count);
- init_PartyTimeLine_edorinfo(partytimeline_count)
-
-
- select count(0) into partytimeline_count from shanglifeecif.PartyTimeLine;
- partytimeline_count:=partytimeline_count+1;
- DBMS_OUTPUT.PUT_LINE(partytimeline_count);
- init_PartyTimeLine_insuranceclaimthread(partytimeline_count)
-
-
- select count(0) into partytimeline_count from shanglifeecif.PartyTimeLine;
- partytimeline_count:=partytimeline_count+1;
- DBMS_OUTPUT.PUT_LINE(partytimeline_count);
- init_PartyTimeLine_InsuranceArrangement(partytimeline_count)
-
-
- select count(0) into partytimeline_count from shanglifeecif.PartyTimeLine;
- partytimeline_count:=partytimeline_count+1;
- DBMS_OUTPUT.PUT_LINE(partytimeline_count);
- init_PartyTimeLine_cc(partytimeline_count)
-
-
- strsql:='
- CREATE SEQUENCE globaleTripID_sequence
- INCREMENT BY 1
- START WITH '|| partytimeline_count||'
- NOMAXVALUE
- NOMINVALUE
- NOCYCLE
- CACHE 1;
- '
- EXECUTE IMMEDIATE strsql
- EXCEPTION
- WHEN HIVE_EXCEPTION THEN
- log_exception('init_partytimeline_main',sqlerrm(),sqlcode())
- WHEN Others THEN
- log_exception('init_partytimeline_main',sqlerrm(),sqlcode())
- END;
- CREATE TABLE shanglifeecif.PartyTimeLine_edor_tmp(
- id string,
- contno string comment '保单号',
- PartyID string COMMENT '参与方ID' ,
- appntname string COMMENT '参与方名称' ,
- PCertID string COMMENT '参与方证件号码' ,
- PPhone string COMMENT '参与方手机' ,
- cusappdate DATE COMMENT '客户申请日期' ,
- edoracceptno string COMMENT '保全受理号' ,
- edorvalidate DATE COMMENT '保全生效日期'
- ) COMMENT '参与方保全事件临时表 '
- STORED AS ES
- with shard number 10
- replication 1;
- !set plsqlUseSlash true
- SET plsql.catch.hive.exception=true;
- CREATE OR REPLACE PROCEDURE init_PartyTimeLine_edorlist(partytimeline_count OUT int)
- IS
- BEGIN
-
- delete from shanglifeecif.PartyTimeLine_edor_tmp;
- insert into shanglifeecif.PartyTimeLine_edor_tmp(
- id,
- contno,
- appntname,
- cusappdate,
- edoracceptno,
- edorvalidate
- )
- SELECT
- row_number()over(),
- contno,
- appntname,
- cusappdate,
- edoracceptno,
- edorvalidate
- from audit_edorlist;
-
- UPDATE shanglifeecif.PartyTimeLine_edor_tmp a SET (
- PartyID ,
- PCertID ,
- PPhone
- ) = (
- select
- ApplicantID ,
- AppCertID ,
- AppPhone
- from shanglifeecif.InsuranceArrangement b
- where b.PolicyNo = a.contno
- ) WHERE 1=1 ;
-
- insert into shanglifeecif.PartyTimeLine(
- TripID,
- PartyID,
- Name,
- PCertID,
- PPhone,
- PRole,
- Scenario,
- SDate,
- ThreadID,
- ParentID,
- EndDate,
- CREATED_BY,
- CREATED_TIME
- )
- SELECT
- row_number()over(),
- PartyID,
- appntname,
- PCertID,
- PPhone,
- '投保人',
- '保全',
- cusappdate,
- edoracceptno,
- contno,
- edorvalidate,
- 'admin',
- sysdate
- from shanglifeecif.PartyTimeLine_edor_tmp where PartyID is not null;
-
- select count(0) into partytimeline_count from shanglifeecif.PartyTimeLine
- EXCEPTION
- WHEN HIVE_EXCEPTION THEN
- log_exception('init_PartyTimeLine_edorlist',sqlerrm(),sqlcode())
- WHEN Others THEN
- log_exception('init_PartyTimeLine_edorlist',sqlerrm(),sqlcode())
- END;
- =============================================================================================================================================================
- ============================================================根据edorinfo更新========================================================================================
- ==========================================================================================================================================================
- CREATE OR REPLACE PROCEDURE shanglifeecif.init_PartyTimeLine_edorinfo(partytimeline_count OUT int)
- IS
- BEGIN
-
- delete from shanglifeecif.PartyTimeLine_edor_tmp;
- insert into shanglifeecif.PartyTimeLine_edor_tmp(
- id,
- contno,
- PPhone,
- PCertID,
- edoracceptno ,
- edorvalidate
- )
- SELECT
- row_number()over(),
- contno,
- lcmobile,
- lcidno,
- EDORAPPDATE,
- EDORCVALIDATE
- from shanghailifeecif.edorinfo;
-
- UPDATE shanglifeecif.PartyTimeLine_edor_tmp a SET (
- PartyID ,
- appntname
- ) = (
- select
- ApplicantID ,
- appname
- from shanglifeecif.InsuranceArrangement b
- where b.PolicyNo = a.contno
- ) WHERE 1=1 ;
-
- insert into shanglifeecif.PartyTimeLine(
- TripID,
- PartyID,
- Name,
- PCertID,
- PPhone,
- PRole,
- Scenario,
- SDate,
- ThreadID,
- ParentID,
- EndDate,
- CREATED_BY,
- CREATED_TIME
- )
- SELECT
- row_number()over() +partytimeline_count,
- PartyID,
- appntname,
- PCertID,
- PPhone,
- '投保人',
- '保全',
- cusappdate,
- edoracceptno,
- contno,
- edorvalidate,
- 'admin',
- sysdate
- from shanglifeecif.PartyTimeLine_edor_tmp where PartyID is not null;
-
- select count(0) into partytimeline_count from shanglifeecif.PartyTimeLine
- EXCEPTION
- WHEN HIVE_EXCEPTION THEN
- log_exception('init_PartyTimeLine_edorinfo',sqlerrm(),sqlcode())
- WHEN Others THEN
- log_exception('init_PartyTimeLine_edorinfo',sqlerrm(),sqlcode())
- END;
- =============================================================================================================================================================
- ============================================================根据insuranceclaimthread更新理赔========================================================================================
- ==========================================================================================================================================================
- CREATE OR REPLACE PROCEDURE init_PartyTimeLine_insuranceclaimthread(partytimeline_count OUT int)
- IS
- BEGIN
- insert into shanglifeecif.PartyTimeLine(
- TripID,
- PartyID,
- Name,
- PCertID,
- PPhone,
- PRole,
- Scenario,
- SDate,
- ThreadID,
- ParentID,
- EndDate,
- CREATED_BY,
- CREATED_TIME
- )
- SELECT
- row_number()over() +partytimeline_count,
- applicantid,
- appname,
- appcertid,
- appphone,
- '投保人',
- '报案',
- crdate,
- icthreadid,
- policyno,
- csdate,
- 'admin',
- sysdate
- from shanglifeecif.insuranceclaimthread where applicantid is not null;
-
- select count(0) into partytimeline_count from shanglifeecif.PartyTimeLine;
-
- EXCEPTION
- WHEN HIVE_EXCEPTION THEN
- log_exception('init_PartyTimeLine_insuranceclaimthread',sqlerrm(),sqlcode())
- WHEN Others THEN
- log_exception('init_PartyTimeLine_insuranceclaimthread',sqlerrm(),sqlcode())
- END;
- =============================================================================================================================================================
- ============================================================根据InsuranceArrangement更新投保========================================================================================
- ==========================================================================================================================================================
- CREATE OR REPLACE PROCEDURE init_PartyTimeLine_InsuranceArrangement(partytimeline_count OUT int)
- IS
- BEGIN
-
- insert into PartyTimeLine(
- TripID,
- PartyID,
- Name,
- PCertID,
- PPhone,
- PRole,
- Scenario,
- SDate,
- ThreadID,
- ParentID,
- EndDate,
- CREATED_BY,
- CREATED_TIME
- )
- SELECT
- row_number()over() +partytimeline_count,
- applicantid,
- appname,
- appcertid,
- appphone,
- '投保人',
- '投保',
- PADate,
- PANo,
- PolicyNo,
- PMDate ,
- 'admin',
- sysdate
- from shanglifeecif.InsuranceArrangement where applicantid is not null;
-
- select count(0) into partytimeline_count from PartyTimeLine
- EXCEPTION
- WHEN HIVE_EXCEPTION THEN
- log_exception('init_PartyTimeLine_InsuranceArrangement',sqlerrm(),sqlcode())
- WHEN Others THEN
- log_exception('init_PartyTimeLine_InsuranceArrangement',sqlerrm(),sqlcode())
- END;
- ==================================================================================================================================================
- ===========================================================根据cc_record_main表插入 保全 理赔 咨询=========================================================
- ==================================================================================================================================================
- CREATE TABLE PartyTimeLine_cc_tmp(
- id string,
- ThreadID string comment '记录id',
- ParentID string comment '父ID 保单号 ',
- PartyID string COMMENT '参与方ID' ,
- Name string COMMENT '参与方名称' ,
- PCertID string COMMENT '参与方证件号码' ,
- PPhone string COMMENT '参与方手机' ,
- PRole string COMMENT '参与方角色' ,
- EndDate DATE COMMENT '完成日期'
- ) COMMENT '呼叫中心临时表'
- STORED AS ES
- with shard number 10
- replication 1;
- CREATE OR REPLACE PROCEDURE init_PartyTimeLine_cc(partytimeline_count OUT int)
- IS
- BEGIN
-
- delete from PartyTimeLine_cc_tmp;
-
- insert into PartyTimeLine_cc_tmp(
- id ,
- ThreadID,
- ParentID ,
-
- Name ,
-
- PPhone ,
-
- EndDate
- )
- SELECT
- row_number()over(),
- recordinfo_id,
- contNos,
- econtactsName,
- econtactsPhone,
- startTime
- from shanghailifeecif.cc_record_main where contNos is not null;
-
- UPDATE PartyTimeLine_cc_tmp a SET (
- PartyID ,
- PCertID ,
- PRole
- ) = (
- select
- b.ApplicantID ,
- b.appcertid,
- '投保人'
- from (
- SELECT
- policyno,
- row_number()over(partition by policyno ) tmpfiled,
- ApplicantID ,
- appname,
- appcertid
- FROM qxp.insurancearrangement
- )b
- where b.policyno = a.ParentID AND b.appname = a.name AND b.tmpfiled=1
- ) WHERE a.PartyID IS null ;
- UPDATE PartyTimeLine_cc_tmp a SET (
- PartyID ,
- PCertID ,
- PRole
- ) = (
- select
- b.insuredid ,
- b.inscertid,
- '被保人'
- from (
- SELECT
- policyno,
- row_number()over(partition by policyno ) tmpfiled,
- insuredid ,
- insname,
- inscertid
- FROM qxp.insurancearrangement
- )b
- where b.policyno = a.ParentID AND b.insname = a.name AND b.tmpfiled=1
- ) WHERE a.PartyID IS null ;
-
- insert into PartyTimeLine(
- TripID,
- PartyID,
- Name,
- PCertID,
- PPhone,
- PRole,
- Scenario,
- SDate,
- ThreadID,
- ParentID,
- EndDate,
- CREATED_BY,
- CREATED_TIME
- )
- SELECT
- row_number()over() +partytimeline_count,
- PartyID,
- Name,
- PCertID,
- PPhone,
- PRole,
- '咨询',
- EndDate,
- ThreadID,
- ParentID,
- EndDate ,
- 'admin',
- sysdate
- from PartyTimeLine_cc_tmp where PartyID is not null;
-
- select count(0) into partytimeline_count from PartyTimeLine;
-
- EXCEPTION
- WHEN HIVE_EXCEPTION THEN
- log_exception('init_PartyTimeLine_cc',sqlerrm(),sqlcode())
- WHEN Others THEN
- log_exception('init_PartyTimeLine_cc',sqlerrm(),sqlcode())
- END;
- ==================================================================================================================================================
- ===========================================================更新投诉到事件表中=========================================================
- ==================================================================================================================================================
- ==================================================================================================================================================
- ===========================================================更新用户主表最后接触方式=========================================================
- ==================================================================================================================================================
- ===================================================================数据解析========================================================================
- SELECT count(1) FROM audit_edorlist ;
- SELECT count(1) FROM (
- SELECT contno FROM audit_edorlist GROUP BY contno
- );
- SELECT count(1) FROM (
- SELECT contno,cusappdate FROM audit_edorlist GROUP BY contno,cusappdate
- );
- SELECT count(1) a,contno FROM audit_edorlist GROUP BY contno,cusappdate ORDER BY a DESC;
- SELECT * FROM audit_edorlist WHERE contno = '2018020800060078';
- SELECT count(1) FROM PartyTimeLine WHERE scenario = "保全" ;
- SELECT count(1) FROM PartyTimeLine WHERE Scenario ='投保' ;
- SELECT count(1) FROM PartyTimeLine WHERE Scenario ='报案' ;
- SELECT count(1) FROM PartyTimeLine WHERE Scenario ='咨询' ;
- SELECT count(1) FROM PartyTimeLine;
- SELECT count(1) FROM shanglifeecif.InsuranceArrangement;
- SELECT count(1) FROM shanglifeecif.insuranceclaimthread;
|