ShangHaiLifeECIF/sql-achievement/存储过程/参与方事件.sql

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 '管理机构',  -- dialect: ORACLE
  managename varchar2(200) DEFAULT NULL COMMENT '管理机构名称',  -- dialect: ORACLE
  salechnl varchar2(200) DEFAULT NULL COMMENT '销售渠道',  -- dialect: ORACLE
  salechnlname varchar2(64) DEFAULT NULL COMMENT '销售渠道名称',  -- dialect: ORACLE
  selltype varchar2(64) DEFAULT NULL COMMENT '合作渠道代码',  -- dialect: ORACLE
  selltypename varchar2(64) DEFAULT NULL COMMENT '合作渠道名称',  -- dialect: ORACLE
  contno varchar2(200) DEFAULT NULL COMMENT '保单号',  -- dialect: ORACLE
  riskcodes varchar2(200) DEFAULT NULL COMMENT '险种代码',  -- dialect: ORACLE
  risknames varchar2(200) DEFAULT NULL COMMENT '险种名称',  -- dialect: ORACLE
  risktypes varchar2(64) DEFAULT NULL COMMENT '险种类型',  -- dialect: ORACLE
  signdate timestamp DEFAULT NULL COMMENT '承保日期', 
  appntname varchar2(200) DEFAULT NULL COMMENT '投保人',  -- dialect: ORACLE
  prem decimal(10,2) DEFAULT NULL COMMENT '保费', 
  edorname varchar2(256) DEFAULT NULL COMMENT '保全类型',  -- dialect: ORACLE
  edoracceptno varchar2(256) DEFAULT NULL COMMENT '保全受理号',  -- dialect: ORACLE
  edorno varchar2(256) DEFAULT NULL COMMENT '批单号',  -- dialect: ORACLE
  edorvalidate timestamp DEFAULT NULL COMMENT '保全生效日期', 
  customerhandleflag varchar2(64) DEFAULT NULL COMMENT '是否亲办',  -- dialect: ORACLE
  operator varchar2(256) DEFAULT NULL COMMENT '操作人',  -- dialect: ORACLE
  approveoperator varchar2(256) DEFAULT NULL COMMENT '审批人',  -- dialect: ORACLE
  getmoney decimal(10,2) DEFAULT NULL COMMENT '保全退补费金额', 
  cusappdate timestamp DEFAULT NULL COMMENT '客户申请日期', 
  payyears varchar2(64) DEFAULT NULL COMMENT '缴费年期',  -- dialect: ORACLE
  agentname varchar2(256) DEFAULT NULL COMMENT '代理人姓名',  -- dialect: ORACLE
  edorstate varchar2(64) DEFAULT NULL COMMENT '保全状态',  -- dialect: ORACLE
  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
   --1.从保全清单表中初始化 参与事件
  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)
 
  --2.从保全表中初始化 参与事件
  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)


  
  --  3.初始化理赔相关信息
  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)
  
  --  4.初始化投保赔相关信息
  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)
  
  --5.从cc通话中心表中初始化 理赔 保全 咨询 参与事件
  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;

/*audit_edorlist 表
 managecom varchar2(200) DEFAULT NULL COMMENT '管理机构',  -- dialect: ORACLE
  managename varchar2(200) DEFAULT NULL COMMENT '管理机构名称',  -- dialect: ORACLE
  salechnl varchar2(200) DEFAULT NULL COMMENT '销售渠道',  -- dialect: ORACLE
  salechnlname varchar2(64) DEFAULT NULL COMMENT '销售渠道名称',  -- dialect: ORACLE
  selltype varchar2(64) DEFAULT NULL COMMENT '合作渠道代码',  -- dialect: ORACLE
  selltypename varchar2(64) DEFAULT NULL COMMENT '合作渠道名称',  -- dialect: ORACLE
  contno varchar2(200) DEFAULT NULL COMMENT '保单号',  -- dialect: ORACLE
  riskcodes varchar2(200) DEFAULT NULL COMMENT '险种代码',  -- dialect: ORACLE
  risknames varchar2(200) DEFAULT NULL COMMENT '险种名称',  -- dialect: ORACLE
  risktypes varchar2(64) DEFAULT NULL COMMENT '险种类型',  -- dialect: ORACLE
  signdate timestamp DEFAULT NULL COMMENT '承保日期', 
  appntname varchar2(200) DEFAULT NULL COMMENT '投保人',  -- dialect: ORACLE
  prem decimal(10,2) DEFAULT NULL COMMENT '保费', 
  edorname varchar2(256) DEFAULT NULL COMMENT '保全类型',  -- dialect: ORACLE
  edoracceptno varchar2(256) DEFAULT NULL COMMENT '保全受理号',  -- dialect: ORACLE
  edorno varchar2(256) DEFAULT NULL COMMENT '批单号',  -- dialect: ORACLE
  edorvalidate timestamp DEFAULT NULL COMMENT '保全生效日期', 
  customerhandleflag varchar2(64) DEFAULT NULL COMMENT '是否亲办',  -- dialect: ORACLE
  operator varchar2(256) DEFAULT NULL COMMENT '操作人',  -- dialect: ORACLE
  approveoperator varchar2(256) DEFAULT NULL COMMENT '审批人',  -- dialect: ORACLE
  getmoney decimal(10,2) DEFAULT NULL COMMENT '保全退补费金额', 
  cusappdate timestamp DEFAULT NULL COMMENT '客户申请日期', 
  payyears varchar2(64) DEFAULT NULL COMMENT '缴费年期',  -- dialect: ORACLE
  agentname varchar2(256) DEFAULT NULL COMMENT '代理人姓名',  -- dialect: ORACLE
  edorstate varchar2(64) DEFAULT NULL COMMENT '保全状态',  -- dialect: ORACLE
  ins_time timestamp DEFAULT NULL



*/


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;
--set plsql.compile.dml.check.semantic=false; --禁止编译过程对PL/SQL内部语法进行检查。
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  ,-- 参与方ID
      PCertID     ,-- 参与方证件号码
      PPhone   
    ) = (
      select 
          ApplicantID ,
          AppCertID ,
          AppPhone
        from shanglifeecif.InsuranceArrangement b
        where b.PolicyNo = a.contno
    ) WHERE 1=1 ;

  --更新参与方事件表
  insert into shanglifeecif.PartyTimeLine(
      TripID,
      PartyID, --参与方id
      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更新========================================================================================
==========================================================================================================================================================

--------------------------------根据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 '保全生效日期 '
*/

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  ,-- 参与方ID
      appntname
    ) = (
      select 
          ApplicantID ,
          appname--投保人名称
        from shanglifeecif.InsuranceArrangement b
        where b.PolicyNo = a.contno
    ) WHERE 1=1 ;

  --更新参与方事件表
  insert into shanglifeecif.PartyTimeLine(
      TripID,
      PartyID, --参与方id
      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, --参与方id
      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, --参与方id
      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,-- string comment ' 记录id',
    ParentID ,--string comment '保单号 '
    --PartyID ,--string  COMMENT '参与方ID' ,
    Name ,-- string    COMMENT '参与方名称' ,
    --PCertID ,--string    COMMENT '参与方证件号码' ,
    PPhone  ,--string    COMMENT '参与方手机' ,
    --PRole ,--string    COMMENT '参与方角色' ,
    EndDate --DATE COMMENT '完成日期' 
  )
  SELECT 
   row_number()over(),
   recordinfo_id,
   contNos,

   econtactsName,

   econtactsPhone,
   startTime
   from shanghailifeecif.cc_record_main where contNos is not null;


   --更新用户id 和 角色 
   UPDATE PartyTimeLine_cc_tmp a SET (
     PartyID ,--string  COMMENT '参与方ID' ,
     PCertID ,--string    COMMENT '参与方证件号码' ,
     PRole --string    COMMENT '参与方角色' ,
    ) = (
      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 ,--string  COMMENT '参与方ID' ,
     PCertID ,--string    COMMENT '参与方证件号码' ,
     PRole --string    COMMENT '参与方角色' ,
    ) = (
      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, --参与方id
      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;







==================================================================================================================================================
===========================================================更新投诉到事件表中=========================================================
==================================================================================================================================================









==================================================================================================================================================
===========================================================更新用户主表最后接触方式=========================================================
==================================================================================================================================================





===================================================================数据解析========================================================================
--保全号是唯一的 是不重复的

--从 audit_edorlist 的表中插入的数据为 619006
--有1060个数据 因为查不到保单无法进入数据库

--带edorinfo 一共插入 619685 

--带理赔和保单一共插入 2086136   保单总数 1520720


SELECT count(1) FROM audit_edorlist ; --620066

SELECT count(1) FROM (
SELECT contno FROM audit_edorlist GROUP BY contno
); --根据保单 去重550805 

SELECT count(1) FROM (
SELECT contno,cusappdate FROM audit_edorlist GROUP BY contno,cusappdate
);--根据保单和申请时间 去重 591350

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 = "保全"  ;  --619713
SELECT count(1) FROM PartyTimeLine WHERE Scenario ='投保'  ; --1466423
SELECT count(1) FROM PartyTimeLine WHERE Scenario ='报案'  ; --11490
SELECT count(1) FROM PartyTimeLine WHERE Scenario ='咨询'  ; --14
SELECT count(1) FROM PartyTimeLine; --2097626

SELECT count(1) FROM shanglifeecif.InsuranceArrangement; --1520720  

SELECT count(1) FROM shanglifeecif.insuranceclaimthread; --13957
--