ShangHaiLifeECIF/sql-achievement/存储过程/20220829升级/update.sql

CREATE OR REPLACE PROCEDURE shanglifeecif.init_customerno_salecom_relation() IS
              DECLARE
BEGIN

delete from shanglifeecif.customerno_salecom_relation;

-- 用户 渠道关系表
insert into shanglifeecif.customerno_salecom_relation (
    csrid,
    indid,
    custid,
    scustid,
    name,
    birthday,
    nobirth,
    sobirth,
    cobirth,
    dday,
    gender,
    ethnic,
    nation,
    homeadress,
    regresidence,
    maritalstat,
    maritalstatdate,
    offspring,
    support,
    empstat,
    empstatdate,
    raid,
    ral,
    al,
    coadress,
    pmphone,
    mhone1,
    mhone2,
    sophone,
    height,
    weight,
    cillness,
    pillness,
    cdiseases,
    bmi,
    fmhistory,
    evadate,
    cohevaluation,
    hphone,
    occupationid,
    occupation,
    odate,
    employer,
    wphone,
    empdate,
    lemployer,
    idcard,
    passport,
    dlicense,
    education,
    university,
    edate,
    wechat,
    weibo,
    email,
    qq,
    tiktok,
    saccount1,
    sa1cat,
    saccount2,
    sa2cat,
    ctype,
    father,
    mother,
    mate,
    child1,
    child2,
    cochild,
    rpid,
    rpdescribe,
    corp,
    vid,
    vdescribe,
    covehicle,
    anniversary1,
    a1describe,
    anniversary2,
    a2describe,
    soanniversary,
    custtype,
    bankname,
    tobankcard,
    accname,
    bankaccno,
    cobaccount,
    sotlirisk,
    soairisk,
    sosiirisk,
    somcirisk,
    someirisk,
    soefirisk,
    sopirisk,
    sowmirisk,
    custclass,
    convalue,
    awarded3,
    awarded2,
    awarded1,
    sovalue,
    enddate,
    systemtag1,
    systemtag2,
    systemtag3,
    systemtag4,
    systemtag5,
    cocommunication,
    lcdate,
    lctype,
    lcmethod,
    cocomplaint,
    lcptdate,
    lcptreason,
    lcptlink,
    lcptresult,
    lcptduration,
    lcptperson,
    soppremium,
    lappdate,
    cndate,
    cnstatus,
    coclaim,
    soclaim,
    cocnotification,
    fadate,
    fachannel,
    faorg,
    lpno,
    lpstate,
    lpname,
    lpchannel,
    lporg,
    copolicy,
    lpostype,
    iaccno1,
    iaccname1,
    iaccno2,
    iaccname2,
    iaccno3,
    iaccname3,
    soiaccount1,
    soiaccount2,
    soiaccount3,
    coiaccount,
    homeid,
    branchcode,
    custstate,
    datasource,
    smoking,
    drinking,
    pregnancy,
    hobby1,
    hobby2,
    hobby3,
    pincome,
    fincome,
    regtype,
    incomesource,
    sistatus,
    novpolicy,
    noivpolicy,
    nostinsurance,
    nospinsurance,
    iself,
    iparent,
    ichildren,
    imate,
    policybelong,
    zipcode,
    othernumber,
    otheridnumber,
    officialcalculus,
    created_by,
    created_time,
    updated_by,
    updated_time,
    label1,
    label2,
    label3,
    label4,
    label5,
    label6,
    label7,
    label8,
    label9,
    label10,
    label11,
    label12,
    label13,
    label14,
    label15,
    label16,
    label17,
    label18,
    label19,
    label20,
    label21,
    label22,
    label23,
--		label24,
--		label25,
--		label26,
--		label27,
    label28,
--		label29,
--		label30,
--		label31,
--		label32,
--		label33,
    label34,
    label35,
    label36,
    label37,
    label38,
    label39,
    label40,
    label41,
    label42,
--		label43,
--		label44,
--		label45,
--		label46,
--		label47,
--		label48,
--		label49,
--		label50,
    label51,
--		label52,
    label53,
--		label54,
--		label55,
--		label56,
--		label57,
--		label58,
--		label59,
    label60,
--		label61,
--		label62,
    label63,
--		label64,
--		label65,
--		label66,
    label67,
--		label68,
    label69,
    label70,
    label71,
    label72,
    label73,
    label74,
    label75,
    label76,
    label77,
--		label78,
--		label79,
    label80,
--		label81,
    label82,
    label83,
    label84,
    label85,
    label86,
    label87,
--		label88,
--		label89,
    label90,
--		label91,
--		label92,
    label93,
--		label94,
--		label95,
    label96,
    label97,
    label98,
    label99,
    label100,
    label101,
    label102,
    label103,
    label104,
    label105,
    label106,
--		label107,
--		label108,
--		label109,
--		label110,
--		label111,
    label112,
--		label113,
    label114,
--		label115,
    label116,
--		label117,
    label118,
    label119,
--		label120,
    salecom
)
SELECT
    row_number()OVER(ORDER BY i.scustid),
    i.indid,
    i.custid,
    i.scustid,
    i.name,
    i.birthday,
    i.nobirth,
    i.sobirth,
    i.cobirth,
    i.dday,
    i.gender,
    i.ethnic,
    i.nation,
    i.homeadress,
    i.regresidence,
    i.maritalstat,
    i.maritalstatdate,
    i.offspring,
    i.support,
    i.empstat,
    i.empstatdate,
    i.raid,
    i.ral,
    i.al,
    i.coadress,
    i.pmphone,
    i.mhone1,
    i.mhone2,
    i.sophone,
    i.height,
    i.weight,
    i.cillness,
    i.pillness,
    i.cdiseases,
    i.bmi,
    i.fmhistory,
    i.evadate,
    i.cohevaluation,
    i.hphone,
    i.occupationid,
    i.occupation,
    i.odate,
    i.employer,
    i.wphone,
    i.empdate,
    i.lemployer,
    i.idcard,
    i.passport,
    i.dlicense,
    i.education,
    i.university,
    i.edate,
    i.wechat,
    i.weibo,
    i.email,
    i.qq,
    i.tiktok,
    i.saccount1,
    i.sa1cat,
    i.saccount2,
    i.sa2cat,
    i.ctype,
    i.father,
    i.mother,
    i.mate,
    i.child1,
    i.child2,
    i.cochild,
    i.rpid,
    i.rpdescribe,
    i.corp,
    i.vid,
    i.vdescribe,
    i.covehicle,
    i.anniversary1,
    i.a1describe,
    i.anniversary2,
    i.a2describe,
    i.soanniversary,
    i.custtype,
    i.bankname,
    i.tobankcard,
    i.accname,
    i.bankaccno,
    i.cobaccount,
    i.sotlirisk,
    i.soairisk,
    i.sosiirisk,
    i.somcirisk,
    i.someirisk,
    i.soefirisk,
    i.sopirisk,
    i.sowmirisk,
    i.custclass,
    i.convalue,
    i.awarded3,
    i.awarded2,
    i.awarded1,
    i.sovalue,
    i.enddate,
    i.systemtag1,
    i.systemtag2,
    i.systemtag3,
    i.systemtag4,
    i.systemtag5,
    i.cocommunication,
    i.lcdate,
    i.lctype,
    i.lcmethod,
    i.cocomplaint,
    i.lcptdate,
    i.lcptreason,
    i.lcptlink,
    i.lcptresult,
    i.lcptduration,
    i.lcptperson,
    i.soppremium,
    i.lappdate,
    i.cndate,
    i.cnstatus,
    i.coclaim,
    i.soclaim,
    i.cocnotification,
    i.fadate,
    i.fachannel,
    i.faorg,
    i.lpno,
    i.lpstate,
    i.lpname,
    i.lpchannel,
    i.lporg,
    i.copolicy,
    i.lpostype,
    i.iaccno1,
    i.iaccname1,
    i.iaccno2,
    i.iaccname2,
    i.iaccno3,
    i.iaccname3,
    i.soiaccount1,
    i.soiaccount2,
    i.soiaccount3,
    i.coiaccount,
    i.homeid,
    i.branchcode,
    i.custstate,
    i.datasource,
    i.smoking,
    i.drinking,
    i.pregnancy,
    i.hobby1,
    i.hobby2,
    i.hobby3,
    i.pincome,
    i.fincome,
    i.regtype,
    i.incomesource,
    i.sistatus,
    i.novpolicy,
    i.noivpolicy,
    i.nostinsurance,
    i.nospinsurance,
    i.iself,
    i.iparent,
    i.ichildren,
    i.imate,
    i.policybelong,
    i.zipcode,
    i.othernumber,
    i.otheridnumber,
    i.officialcalculus,
    i.created_by,
    sysdate as created_time,
    i.updated_by,
    i.updated_time,
    i.label1,
    i.label2,
    i.label3,
    i.label4,
    i.label5,
    i.label6,
    i.label7,
    i.label8,
    i.label9,
    i.label10,
    i.label11,
    i.label12,
    i.label13,
    i.label14,
    i.label15,
    i.label16,
    i.label17,
    i.label18,
    i.label19,
    i.label20,
    i.label21,
    i.label22,
    i.label23,
--		i.label24,
--		i.label25,
--		i.label26,
--		i.label27,
    i.label28,
--		i.label29,
--		i.label30,
--		i.label31,
--		i.label32,
--		i.label33,
    i.label34,
    i.label35,
    i.label36,
    i.label37,
    i.label38,
    i.label39,
    i.label40,
    i.label41,
    i.label42,
--		i.label43,
--		i.label44,
--		i.label45,
--		i.label46,
--		i.label47,
--		i.label48,
--		i.label49,
--		i.label50,
    i.label51,
--		i.label52,
    i.label53,
--		i.label54,
--		i.label55,
--		i.label56,
--		i.label57,
--		i.label58,
--		i.label59,
    i.label60,
--		i.label61,
--		i.label62,
    i.label63,
--		i.label64,
--		i.label65,
--		i.label66,
    i.label67,
--		i.label68,
    i.label69,
    i.label70,
    i.label71,
    i.label72,
    i.label73,
    i.label74,
    i.label75,
    i.label76,
    i.label77,
--		i.label78,
--		i.label79,
    i.label80,
--		i.label81,
    i.label82,
    i.label83,
    i.label84,
    i.label85,
    i.label86,
    i.label87,
--		i.label88,
--		i.label89,
    i.label90,
--		i.label91,
--		i.label92,
    i.label93,
--		i.label94,
--		i.label95,
    i.label96,
    i.label97,
    i.label98,
    i.label99,
    i.label100,
    i.label101,
    i.label102,
    i.label103,
    i.label104,
    i.label105,
    i.label106,
--		i.label107,
--		i.label108,
--		i.label109,
--		i.label110,
--		i.label111,
    i.label112,
--		i.label113,
    i.label114,
--		i.label115,
    i.label116,
--		i.label117,
    i.label118,
    i.label119,
--		i.label120,
    t.salecom
FROM (
         -- 投保人和被保人都作为客户处理
         -- 投保人
         SELECT distinct p.customerno, p.salecom FROM dsj.POLICY_INFORMATION p
         union
         -- 被保人
         SELECT distinct p.insuredno as customerno, p.salecom FROM dsj.POLICY_INFORMATION p
     ) t, shanglifeecif.individual i WHERE t.customerno = i.scustid;


dbms_output.put_line('init_customerno_salecom_relation 函数跑批完成！');

EXCEPTION
			WHEN HIVE_EXCEPTION THEN
INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());

WHEN Others THEN
INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());

END;


CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_main() -- 初始化 数据全部插入
              IS
              DECLARE
              individual_count int
              strsql string
BEGIN
    shanglifeecif.customertotaltemp();


DELETE FROM shanglifeecif.individual;
--初始化客户信息
	  shanglifeecif.init_all_individual();
--更新用户等级信息
	  shanglifeecif.up_t_customers_class_1();
--更新其它信息
	  shanglifeecif.up_other_customerinfo();
--更新被保人信息
	  shanglifeecif.update_insuredinfo();
	  /*
		customerno_salecom_relation表中字段的值是从individual表复制来的，cron.sh中还有一些为individual表字段赋值的步骤。
		 所以初始化和更新customerno_salecom_relation表放在cron.sh最后，这样就能保证初始化时individual表中所有字段都已经赋值完毕

	-- 初始化用户渠道关系表
	shanglifeecif.init_customerno_salecom_relation();
	  --客户信息添加渠道
	  shanglifeecif.init_Individual_salecom();
	-- 分渠道重新计算标签值，并更新到用户渠道关系表中
	shanglifeecif.update_customerno_salecom_relation_labels();
	*/
END;
CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_salecom() IS
              DECLARE
BEGIN

-- 客户信息添加渠道
--
UPDATE shanglifeecif.Individual si1 SET si1.SALECOM = (
    SELECT t.salecoms FROM (
                               SELECT customerno   , concat_ws(',',collect_set(salecom))  AS salecoms  FROM (
                                                                                                                SELECT b.scustid AS customerno  ,b.salecom   AS salecom FROM
                                                                                                                                                                            shanglifeecif.individual  a ,   shanglifeecif.customerno_salecom_relation b
                                                                                                                WHERE  a.scustid = b.scustid

                                                                                                            ) GROUP BY    customerno
                           ) t

    WHERE t.customerno =  si1.scustid);


dbms_output.put_line('init_Individual_salecom 函数跑批完成！');

EXCEPTION
			WHEN HIVE_EXCEPTION THEN
INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());

WHEN Others THEN
INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());

END;
CREATE OR REPLACE PROCEDURE shanglifeecif.init_indrelationship_main()
              IS
              DECLARE
              s_count int
BEGIN
DELETE FROM shanglifeecif.IndRelationShip;
--从保单信息表（POLICY_INFORMATION）获取投保人和被保人的关系并
	--第一步取关系父母，子女
insert into shanglifeecif.IndRelationShip(
    irsid,
    RSType,
    IndID1,
    Name1,
    IDCard1,
    Role1,
    IndID2,
    Name2,
    IDCard2,
    RSSTime,
    Role2,
    salecom
)SELECT
     reflect("java.util.UUID", "randomUUID"),
     '父母',
     p.CUSTOMERNO,
     max(p.NAME),
     max(p.IDNO),
     CASE max(p.sex)
         WHEN '0' THEN '儿子'  WHEN '1' THEN '女儿' END,
     p.INSUREDNO,
     max(p.INSUREDNAME),
     max(p.INSUREDIDNO),
     min(p.polapplydate),
     CASE max(p.INSUREDSEX)
         WHEN '0' THEN '父亲'  WHEN '1' THEN '母亲' END,
     salecom
FROM dsj.POLICY_INFORMATION p
WHERE (p.RELATIONTOAPPNT = '父母' OR p.RELATIONTOAPPNT = '子女')
  AND p.birthday > p.insuredbirthday
GROUP BY p.CUSTOMERNO,p.INSUREDNO, p.salecom;


insert into shanglifeecif.IndRelationShip(
    irsid,
    RSType,
    IndID1,
    Name1,
    IDCard1,
    Role1,
    IndID2,
    Name2,
    IDCard2,
    RSSTime,
    Role2,
    salecom
)
SELECT
    reflect("java.util.UUID", "randomUUID"),
    '子女',
    p.CUSTOMERNO,
    max(p.NAME),
    max(p.IDNO),
    CASE max(p.sex)
        WHEN '0' THEN '父亲'  WHEN '1' THEN '母亲' END,
    p.INSUREDNO,
    max(p.INSUREDNAME),
    max(p.INSUREDIDNO),
    min(p.polapplydate),
    CASE max(p.INSUREDSEX)
        WHEN '0' THEN '儿子'  WHEN '1' THEN '女儿' END,
    salecom
FROM dsj.POLICY_INFORMATION p
WHERE (p.RELATIONTOAPPNT = '父母' OR p.RELATIONTOAPPNT = '子女')
  AND p.birthday < p.insuredbirthday
GROUP BY p.CUSTOMERNO,p.INSUREDNO, p.salecom;

--第二步取关系祖父母、外祖父母    祖孙、外祖孙
insert into shanglifeecif.IndRelationShip(
    irsid,
    RSType,
    IndID1,
    Name1,
    IDCard1,
    Role1,
    IndID2,
    Name2,
    IDCard2,
    RSSTime,
    Role2,
    salecom
)SELECT
     reflect("java.util.UUID", "randomUUID"),
     '祖父母、外祖父母',
     p.CUSTOMERNO,
     max(p.NAME),
     max(p.IDNO),
     CASE max(p.sex)
         WHEN '0' THEN '(外)祖孙'  WHEN '1' THEN '(外)孙女' END,
     p.INSUREDNO,
     max(p.INSUREDNAME),
     max(p.INSUREDIDNO),
     min(p.polapplydate),
     CASE max(p.INSUREDSEX)
         WHEN '0' THEN '(外)祖父'  WHEN '1' THEN '(外)祖母' END,
     salecom
FROM dsj.POLICY_INFORMATION p
WHERE (p.RELATIONTOAPPNT = '祖父母、外祖父母' OR p.RELATIONTOAPPNT = '祖孙、外祖孙')
  AND p.birthday > p.insuredbirthday
GROUP BY p.CUSTOMERNO,p.INSUREDNO, p.salecom;


insert into shanglifeecif.IndRelationShip(
    irsid,
    RSType,
    IndID1,
    Name1,
    IDCard1,
    Role1,
    IndID2,
    Name2,
    IDCard2,
    RSSTime,
    Role2,
    salecom
)SELECT
     reflect("java.util.UUID", "randomUUID"),
     '祖孙、外祖孙',
     p.CUSTOMERNO,
     max(p.NAME),
     max(p.IDNO),
     CASE max(p.sex)
         WHEN '0' THEN '(外)祖父'  WHEN '1' THEN '(外)祖母' END,
     p.INSUREDNO,
     max(p.INSUREDNAME),
     max(p.INSUREDIDNO),
     min(p.polapplydate),
     CASE max(p.INSUREDSEX)
         WHEN '0' THEN '(外)祖孙'  WHEN '1' THEN '(外)孙女' END,
     salecom
FROM dsj.POLICY_INFORMATION p
WHERE (p.RELATIONTOAPPNT = '祖父母、外祖父母' OR p.RELATIONTOAPPNT = '祖孙、外祖孙')
  AND p.birthday < p.insuredbirthday
GROUP BY p.CUSTOMERNO,p.INSUREDNO, p.salecom;

--第三步取配偶和其他
insert into shanglifeecif.IndRelationShip(
    irsid,
    RSType,
    IndID1,
    Name1,
    IDCard1,
    Role1,
    IndID2,
    Name2,
    IDCard2,
    RSSTime,
    Role2,
    salecom
)SELECT
     reflect("java.util.UUID", "randomUUID"),
     max(p.RELATIONTOAPPNT),
     p.CUSTOMERNO,
     max(p.NAME),
     max(p.IDNO),
     CASE max(p.RELATIONTOAPPNT)
         WHEN '配偶'
             THEN case max(p.sex) WHEN '0' THEN '丈夫'  WHEN '1' THEN '妻子' END
         ELSE max(p.RELATIONTOAPPNT)
         END,
     p.INSUREDNO,
     max(p.INSUREDNAME),
     max(p.INSUREDIDNO),
     min(p.polapplydate),
     CASE max(p.RELATIONTOAPPNT)
         WHEN '配偶'
             THEN case max(p.INSUREDSEX) WHEN '0' THEN '丈夫'  WHEN '1' THEN '妻子' END
         ELSE max(p.RELATIONTOAPPNT)
         END,
     salecom
FROM dsj.POLICY_INFORMATION p
WHERE p.RELATIONTOAPPNT = '配偶'
   or p.RELATIONTOAPPNT = '其他'
GROUP BY p.CUSTOMERNO,p.INSUREDNO, p.salecom;


-- 完善被保人关系
-- waite up  sqls running end then run this sql  if time is less  will be loss data
dbms_lock.sleep(60);

insert into shanglifeecif.IndRelationShip(
    irsid,
    RSType,
    IndID1,
    Name1,
    IDCard1,
    Role1,
    IndID2,
    Name2,
    IDCard2,
    Role2,
    salecom
)SELECT reflect("java.util.UUID", "randomUUID"),
        CASE RSType
            WHEN '父母' THEN '子女'
            WHEN '子女' THEN '父母'
            WHEN '祖孙、外祖孙' THEN '祖父母、外祖父母'
            WHEN '祖父母、外祖父母' THEN '祖孙、外祖孙'
            WHEN '配偶' THEN '配偶'
            ELSE RSType
            END,
        IndID2,
        Name2,
        IDCard2,
        Role2,
        IndID1,
        Name1,
        IDCard1,
        Role1,
        salecom
FROM shanglifeecif.IndRelationShip sis ;



dbms_output.put_line('init_indrelationship_main函数跑批完成！');
EXCEPTION
			WHEN HIVE_EXCEPTION THEN
INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());
WHEN Others THEN
INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());

END;


CREATE OR REPLACE PROCEDURE shanglifeecif.partytimeline_main()
              IS
BEGIN
DELETE FROM shanglifeecif.partytimeline;
shanglifeecif.partytimeline_tb();
shanglifeecif.partytimeline_lp();
shanglifeecif.partytimeline_zx();
shanglifeecif.partytimeline_bq();
shanglifeecif.partytimeline_tuibao();
shanglifeecif.partytimeline_ts();
shanglifeecif.partytimeline_hf();
shanglifeecif.partytimeline_xq();
	-- 更新渠道字段
shanglifeecif.update_partytimeline_add_salecom();
END;

CREATE OR REPLACE PROCEDURE shanglifeecif.update_customerno_salecom_relation_labels() IS
-- 标签值分渠道计算，并将计算结果存入shanglifeecif.customerno_salecom_relation表
-- 该表不存储跟渠道的标签值（也就是”上海人寿“渠道），只存储下面子渠道的标签值
BEGIN
-- 1. 是否持有寿险有效保单	label24
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label24 = '持有寿险有效保单' WHERE exists (
                                                                                                SELECT p.customerno FROM dsj.policy_information p,dsj.riskkind l WHERE si1.scustid = p.customerno AND si1.salecom = p.salecom AND p.riskcode = l.riskcode AND p.appflag = '有效' AND (l.kindtype = '新型寿险' OR l.kindtype = '人寿保险')
                                                                                            );

-- 2. 保单件数区间	label25		参考bdnum_distribution.sql : 4行
-- 在 shanglifeecif.bdnum_distribution() 中处理
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label25 = (
    select
        CASE
            WHEN t.n = 0 THEN '无保单'
            WHEN t.n = 1 THEN '1件保单'
            WHEN t.n = 2 THEN '2件保单'
            WHEN t.n between 3 and 5 THEN '3-5件保单'
            WHEN t.n between 6 and 10 THEN '6-10件保单'
            WHEN t.n between 11 and 20 THEN '11-20件保单'
            WHEN t.n between 21 and 50 THEN '21-50件保单'
            WHEN t.n > 50 THEN '50件保单以上'
            END
    from (
             SELECT customerno, salecom, count(1) as n FROM dsj.policy_information p WHERE appflag IS NOT NULL group by customerno, salecom
         ) t
    where
            si1.scustid = t.customerno
      and si1.salecom = t.salecom
);
--	UPDATE shanglifeecif.customerno_salecom_relation si1 SET (
--		si1.NOVPolicy		-- 有效保单数
--		, si1.noivpolicy	-- 无效保单数
--		, si1.Label25		--总保单数
--	) = (
--		SELECT
--			valid_num
--			, invalid_num
--			, total
--		FROM (
--			SELECT
--				customerno
--				, salecom
--				, sum(CASE WHEN appflag = '有效' THEN 1 ELSE 0 end) AS valid_num	-- 有效保单数
--				, sum(CASE WHEN appflag <> '有效' THEN 1 ELSE 0 end) AS invalid_num	-- 无效保单数
--				, count(1) AS total	--总保单数
--			FROM (
--				SELECT customerno, salecom, contno, appflag
--				FROM dsj.policy_information p WHERE appflag IS NOT NULL GROUP BY customerno, salecom, contno, appflag
--			) t
--		GROUP BY customerno, salecom
--		) s
--		WHERE si1.scustid = s.customerno AND si1.salecom = s.salecom
--	)
--	WHERE 1 = 1;

-- 3. 是否贷款 label26
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label26 = '有保单贷款' WHERE exists (
                                                                                             SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag = '有效' and p.customerno = si1.scustid and p.salecom = si1.salecom and p.contno IN (
                                                                                                 SELECT contno FROM dsj.loloandetail WHERE moneytype='DK'
                                                                                             )
                                                                                         );

-- 4. 是否有极短意保单	label27
--	UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label27 = (
--		SELECT
--			CASE
--				WHEN t.cnt > 0 AND t.cnt = t.tnum THEN '仅极短意外险'
--				WHEN t.cnt > 0 AND t.cnt <> t.tnum THEN '有极短意外险'
--			END
--		FROM (
--			SELECT
--				po.customerno
--				, po.salecom
--				, SUM(
--					CASE
--						WHEN
--							po.SECURITY in('7天','15天','30天','90天','1月','3月')
--							AND po.riskcode not in('1066008','1066007','1066006','1066004','1066003','1066002','1066001','1065005','1065003','1065001','1025003')
--						THEN 1
--						ELSE 0
--					END
--				) AS cnt
--				, COUNT(1) AS tnum
--			FROM dsj.policy_information po
--			WHERE
--				po.riskperiod='短期险'
--			GROUP BY customerno, salecom
--		) t
--		WHERE t.customerno = si1.scustid and t.salecom = si1.salecom
--	);

--------------
	-- 4. 是否有极短意保单	label27（方式二）
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label27 = (
    SELECT
        CASE
            WHEN temp.cnt > 0 AND temp.cnt = temp.tnum THEN '仅极短意外险'
            WHEN temp.cnt > 0 AND temp.cnt <> temp.tnum THEN '有极短意外险'
            END
    FROM (
             SELECT p.customerno, p.salecom,max(cnt) cnt,nvl(count(*),0) tnum FROM (
                                                                                       SELECT
                                                                                           count(1) cnt
                                                                                            , po.customerno
                                                                                            , po.salecom
                                                                                       FROM
                                                                                           dsj.policy_information po
                                                                                       where
                                                                                               po.SECURITY in('7天','15天','30天','90天','1月','3月')
                                                                                         and
                                                                                               po.riskperiod='短期险'
                                                                                         and
                                                                                               po.riskcode not in('1066008','1066007','1066006','1066004','1066003','1066002','1066001','1065005','1065003','1065001','1025003')
                                                                                       GROUP BY
                                                                                           po.customerno, po.salecom
                                                                                   ) t LEFT JOIN dsj.policy_information p ON t.customerno = p.customerno and p.salecom = t.salecom GROUP BY p.customerno, p.salecom
         ) temp where temp.customerno = si1.scustid and temp.salecom = si1.salecom

);

-- 5. 是否有趸交保单	Label29
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label29 = '有趸交保单' WHERE exists (
                                                                                             SELECT p.customerno FROM dsj.policy_information p, dsj.PREMIUM_LIST l
                                                                                             WHERE p.contno = l.contno and p.salecom = l.salecom and p.customerno = si1.scustid and p.salecom = si1.salecom and p.appflag = '有效' and p.payintv = '趸缴' and p.riskperiod = '长期险' AND l.PAYMENT_PERIOD LIKE '%一次交清%'
                                                                                         );

-- 6. 是否给本人投保	Label30
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label30 = (
    SELECT
        CASE
            WHEN tt.bnum = 0  THEN '本人未投保'
            WHEN tt.bnum > 0 and tt.bnum <> tt.tnum THEN '为本人投保'
            WHEN tt.bnum > 0 and tt.bnum = tt.tnum THEN '仅为本人投保'
            END
    FROM (
             SELECT t.customerno, t.salecom, MAX(t.bnum) bnum, count(*) as tnum FROM (
                                                                                         SELECT count(*) AS bnum,p.customerno, p.salecom FROM dsj.policy_information p WHERE p.relationtoappnt = '本人' GROUP BY p.customerno, p.salecom
                                                                                     ) t LEFT JOIN dsj.policy_information pi ON pi.customerno = t.customerno and pi.salecom = t.salecom GROUP BY t.customerno, t.salecom
         ) tt WHERE tt.customerno = si1.scustid and tt.salecom = si1.salecom

);

-- 7. 是否给父母投保	Label31
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label31 = (

    SELECT
        CASE
            WHEN tt.bnum = 0  THEN '父母未投保'
            WHEN tt.bnum > 0 and tt.bnum <> tt.tnum THEN '为父母投保'
            WHEN tt.bnum > 0 and tt.bnum = tt.tnum THEN '仅为父母投保'
            END

    FROM (
             SELECT t.customerno, t.salecom,MAX(t.bnum) bnum,count(*) as tnum FROM (
                                                                                       SELECT count(*) AS bnum,p.customerno, p.salecom FROM dsj.policy_information p WHERE (p.RELATIONTOAPPNT = '子女' OR p.RELATIONTOAPPNT = '父母') AND p.birthday > p.insuredbirthday
                                                                                       GROUP BY p.customerno, p.salecom
                                                                                   ) t LEFT JOIN dsj.policy_information pi ON pi.customerno = t.customerno and pi.salecom = t.salecom GROUP BY t.customerno, t.salecom
         ) tt WHERE tt.customerno = si1.scustid and tt.salecom = si1.salecom

);

-- 8. 是否给子女投保	Label32	仅子女投保，子女已投保，子女未·投保	Ichildren
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label32 = (

    SELECT
        CASE
            WHEN tt.bnum = 0  THEN '子女未投保'
            WHEN tt.bnum > 0 and tt.bnum <> tt.tnum THEN '为子女投保'
            WHEN tt.bnum > 0 and tt.bnum = tt.tnum THEN '仅为子女投保'
            END

    FROM (
             SELECT t.customerno, t.salecom,MAX(t.bnum) bnum,count(*) as tnum FROM (
                                                                                       SELECT nvl(count(*),0) AS bnum,p.customerno, p.salecom FROM dsj.policy_information p WHERE (p.RELATIONTOAPPNT = '子女' OR p.RELATIONTOAPPNT = '父母') AND p.birthday < p.insuredbirthday GROUP BY p.customerno, p.salecom
                                                                                   ) t LEFT JOIN dsj.policy_information pi ON pi.customerno = t.customerno and pi.salecom = t.salecom GROUP BY t.customerno, t.salecom
         ) tt WHERE tt.customerno = si1.scustid and tt.salecom = si1.salecom
);

-- 9. 是否给配偶投保	Label33	仅配偶投保，配偶已投保，配偶未投保	Imate
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label33 = (

    SELECT
        CASE
            WHEN tt.bnum = 0  THEN '配偶未投保'
            WHEN tt.bnum > 0 and tt.bnum <> tt.tnum THEN '为配偶投保'
            WHEN tt.bnum > 0 and tt.bnum = tt.tnum THEN '仅为配偶投保'
            END

    FROM (
             SELECT t.customerno, t.salecom,MAX(t.bnum) bnum,count(*) as tnum FROM (
                                                                                       SELECT count(*) AS bnum,p.customerno, p.salecom FROM dsj.policy_information p WHERE p.relationtoappnt = '配偶' GROUP BY p.customerno, p.salecom
                                                                                   ) t LEFT JOIN dsj.policy_information pi ON pi.customerno = t.customerno and pi.salecom = t.salecom GROUP BY t.customerno, t.salecom
         ) tt WHERE tt.customerno = si1.scustid and tt.salecom = si1.salecom
);

-- 10. 是否仅有1年期短险保单	Label43
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label43 = (
    SELECT
        CASE
            WHEN temp.cnt > 0 AND temp.cnt = temp.tnum THEN '仅有1年期短险保单'
            WHEN temp.cnt > 0 AND temp.cnt <> temp.tnum THEN '有1年期短险保单'
            END
    FROM (
             SELECT p.customerno, p.salecom,max(cnt) cnt,nvl(count(*),0) tnum FROM (
                                                                                       SELECT
                                                                                           count(1) cnt,
                                                                                           po.customerno,
                                                                                           po.salecom
                                                                                       FROM
                                                                                           dsj.policy_information po
                                                                                       where
                                                                                               po.SECURITY in('1年','6月','180天')
                                                                                         and
                                                                                               po.riskperiod='短期险'
                                                                                         and
                                                                                               po.riskcode not in('1066008','1066007','1066006','1066004','1066003','1066002','1066001','1065005','1065003','1065001','1025003')
                                                                                       GROUP BY
                                                                                           po.customerno, po.salecom
                                                                                   ) t LEFT JOIN dsj.policy_information p ON t.customerno = p.customerno and t.salecom = p.salecom GROUP BY p.customerno, p.salecom
         ) temp where temp.customerno = si1.scustid and temp.salecom = si1.salecom

);

-- 11. 是否有失效保单	Label44	有失效保单	NOIVPolicy	客户为保单投保人，持有保单中保单状态为失效 （最新文档逻辑）
--  		SELECT p.customerno FROM dsj.policy_information p WHERE p.customerno = si1.scustid and p.salecom = si1.salecom and p.appflag <> '有效' and p.contno IN (select contno from dsj.lccontstate where statetype = 'Available' and state = '1')

UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label44 = '有失效保单' WHERE exists (
                                                                                             SELECT p.customerno FROM dsj.policy_information p WHERE p.customerno = si1.scustid and p.salecom = si1.salecom and p.appflag <> '有效' and p.contno IN (select contno from dsj.lccontstate where statetype = 'Available' )
                                                                                         );

-- 12. 是否有缴费期满长险保单	Label45	有缴费期满长险保单	POLICY_INFORMATION:paycount= payendyear AND riskperiod='长期险'	客户持有保险期限为一年以上，剩余保费期数为0
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label45 = '有缴费期满长险保单' WHERE exists (
                                                                                                 SELECT p.customerno FROM dsj.policy_information p WHERE p.customerno = si1.scustid and p.salecom = si1.salecom and p.appflag = '有效' and p.paycount = p.payendyear AND p.riskperiod='长期险'
                                                                                             );

-- 13. 是否有续期缴费的保单	Label46	有续期缴费的保单		客户为投保人，含有待缴费状态的保单
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label46 = '有续期缴费保单' WHERE exists (
                                                                                               SELECT p.customerno FROM dsj.policy_information p WHERE p.customerno = si1.scustid and p.salecom = si1.salecom and p.appflag = '有效' and p.contno IN (
                                                                                                   select otherno from dsj.ljspay where othernotype = '2'
                                                                                               )
                                                                                           );

-- 14. 有责任期满的保单	Label47	有责任期满的保单		客户持有保单含有保险期限已满的保单
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label47 = '有责任期满的保单' WHERE exists (
                                                                                                SELECT p.customerno FROM dsj.policy_information p WHERE p.customerno = si1.scustid and p.salecom = si1.salecom and p.enddate < to_char(SYSDATE,"yyyy-MM-dd HH:mm:ss.S")
                                                                                            );

-- 15. 第一张保单投保日期	Label48	首次投保日期XXXX-XX-XX	FADate	客户所有保单中投保时间最早的日期
--第一步
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.FADate = (
    SELECT fadate FROM (
                           SELECT p.customerno, p.salecom, min(p.polapplydate) AS fadate FROM dsj.POLICY_INFORMATION p WHERE p.polapplydate IS NOT null GROUP BY p.customerno, p.salecom
                       ) t WHERE t.customerno = si1.scustid and t.salecom = si1.salecom
);
-- 第二步
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.FADate = (
    SELECT fadate FROM (
                           SELECT p.insuredno, p.salecom, min(p.polapplydate) AS fadate  FROM dsj.POLICY_INFORMATION p WHERE p.polapplydate IS NOT null GROUP BY p.insuredno, p.salecom
                       ) t WHERE t.insuredno = si1.scustid and t.salecom = si1.salecom
);
-- 第三步
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label48 = '首次投保日期'||  to_char(si1.fadate, 'yyyy-MM-dd')  WHERE si1.fadate IS NOT NULL;

-- 16. 最后一次寿险投保距今时长	Label49	最后一次投保距今XXX天	LAPPDate	当前日期减去客户所有保单中最后一次投保的保单的投保时间
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.LAPPDate = (
    SELECT fadate FROM (
                           SELECT p.customerno, p.salecom, max(p.polapplydate) AS fadate  FROM dsj.POLICY_INFORMATION p WHERE p.polapplydate IS NOT null GROUP BY p.customerno, p.salecom
                       ) t WHERE t.customerno = si1.scustid and t.salecom = si1.salecom
);

UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.LAPPDate = (
    SELECT fadate FROM (
                           SELECT p.insuredno, p.salecom, max(p.polapplydate) AS fadate  FROM dsj.POLICY_INFORMATION p WHERE p.polapplydate IS NOT null GROUP BY p.insuredno, p.salecom
                       ) t WHERE t.insuredno = si1.scustid and t.salecom = si1.salecom
);

UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label49 = '最后一次投保距今' || DATEDIFF(sysdate(), si1.LAPPDate) || '天' WHERE si1.lappdate IS NOT NULL;

/*
 * 二期分渠道计算标签值时这两个标签只有根渠道显示（也就是”上海人寿“渠道），其他渠道不展示
-- 17. 最近一次保单所属渠道	Label50	最近保单团险渠道、最近保单个人营销、最近保单银行代理、最近保单中介渠道、最近保单网销渠道	--LPChannel，InsuranceArrangement.AgentChannel	客户最后一次投保归属渠道
-- 原始逻辑
UPDATE shanglifeecif.Individual si1 SET si1.label50 =  (SELECT '最近保单' || trim(t.salecomname) FROM (
        SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate DESC) rd,p.salecomname  FROM dsj.POLICY_INFORMATION p
        WHERE p.salecomname IS NOT null
    ) t WHERE t.rd = 1 AND t.customerno = si1.scustid);

-- 18. 最早保单所属渠道	Label52	首单团险渠道、首单个人营销、首单银行代理、首单中介渠道、首单网销渠道	FAChannel	客户第一次投保时保单归属渠道
-- 原始逻辑
UPDATE shanglifeecif.Individual si1 SET si1.label52 =  (SELECT '首单' || trim(t.salecomname) FROM (
        SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate asc) rd,p.salecomname  FROM dsj.POLICY_INFORMATION p WHERE p.salecomname IS NOT null
    ) t WHERE t.rd = 1 AND t.customerno = si1.scustid);
*/

-- 19. 最近保单状态	Label54
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label54 = (
    SELECT '最近保单状态'||trim(t.appflag) FROM (
                                              SELECT row_number()OVER(PARTITION BY p.customerno, p.salecom ORDER BY p.polapplydate DESC) rd,p.customerno, p.salecom,p.appflag FROM dsj.policy_information p
                                          ) t WHERE t.rd = 1 AND si1.scustid = t.customerno AND si1.salecom = t.salecom
);

-- 20. 最近一次保全类型	label55	客户最后一次办理保全业务的业务类型
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label55 = (
    SELECT '客户最近一次办理'||t.edorname FROM (
                                           SELECT
                                               row_number() over(partition by p.customerno, p.salecom ORDER BY l.edorAPPDATE DESC,l.uwtime DESC) rn,
                                               l.edortype,
                                               l.contno,
                                               p.customerno,
                                               p.salecom,
                                               l.edorappdate,
                                               l.edorstate,
                                               lm.edorcode,
                                               lm.edorname
                                           FROM dsj.lpedoritem l,dsj.lmedoritem lm,dsj.policy_information p
                                           WHERE l.edortype = lm.edorcode AND lm.appobj <> 'G' AND l.edorstate = '0' AND p.contno = l.contno
                                       ) t WHERE t.rn = 1 AND si1.scustid = t.customerno and si1.salecom = t.salecom
) WHERE 1 = 1;

-- 21. 是否有退保保单	Label56	有退保保单	如何判断？	客户所有的保单中是否存在保单状态为退保的保单
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label56 = '有退保保单' WHERE exists (
                                                                                             SELECT p.customerno FROM dsj.policy_information p WHERE p.customerno = si1.scustid and p.salecom = si1.salecom and p.contno IN (
                                                                                                 select contno from dsj.lpedoritem where  edortype in ('CT', 'XT', 'GT') and edorstate = '0'
                                                                                             )
                                                                                         );

-- 22. 最近一次理赔类型	Label57	--意外医疗、意外伤残、意外死亡、意外高残、意外大病、意外特种疾病、意外失业失能、意外生命末期重疾、意外豁免、疾病医疗、疾病伤残、疾病死亡、疾病高残、疾病大病、疾病特种疾病、疾病失---业失能、疾病生命末期重疾、疾病豁免	无	客户最后一次投办理理赔业务的业务类型
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label57 = (
    SELECT "最近一次理赔类型" || t.ACCIDENTTYPE FROM (
                                                 SELECT row_number()OVER(PARTITION BY c.insuredno, p.salecom ORDER BY c.applydate desc) rd,c.insuredno,c.ACCIDENTTYPE, p.salecom
                                                 FROM dsj.INSURANCE_CLAIM c, dsj.policy_information p
                                                 WHERE c.contno = p.contno
                                             ) t WHERE t.rd = 1 AND t.insuredno = si1.scustid and t.salecom = si1.salecom
);

-- 23. 最近一次出险类型	Label58	疾病出险、意外出险	无	客户最后一次出险的类型
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label58 = (
    SELECT "最近一次出险类型" || t.lloccurreason FROM (
                                                  SELECT row_number()OVER(PARTITION BY c.insuredno, p.salecom ORDER BY c.applydate desc) rd,c.insuredno,c.lloccurreason, p.salecom
                                                  FROM dsj.INSURANCE_CLAIM c, dsj.policy_information p
                                                  WHERE c.contno = p.contno
                                              ) t WHERE t.rd = 1 AND t.insuredno = si1.scustid and t.salecom = si1.salecom
);

-- 24. 最近一次理赔状态	Label59	理赔报案中、理赔已受理、理赔已立案、理赔审核中、理赔预付审批中、理赔结案、理赔完成、理赔关闭	InsuranceClaimThread.ClaimCaseStatus	--客户最后一次发生理赔的理赔类型
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label59 = (
    SELECT '最近一次理赔状态' || t.LLCLAIMSTATE FROM (
                                                 SELECT row_number()OVER(PARTITION BY c.insuredno, p.salecom ORDER BY c.applydate desc) rd,c.insuredno,c.LLCLAIMSTATE, p.salecom
                                                 FROM dsj.INSURANCE_CLAIM c, dsj.policy_information p WHERE c.contno = p.contno
                                             ) t WHERE t.rd = 1 AND t.insuredno = si1.scustid and t.salecom = si1.salecom
);

-- 25. 是否有满期给付保单	Label61	有满期给付保单
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label61 = '有满期给付保单' WHERE exists (
                                                                                               SELECT p.customerno FROM dsj.policy_information p WHERE si1.scustid = p.customerno and si1.salecom = p.salecom and p.contno IN (
                                                                                                   SELECT contno FROM dsj.lcget WHERE getdutycode IN (select getdutycode from dsj.lmdutygetalive where getdutyname in ('满期保险金','满期生存保险金'))
                                                                                               )
                                                                                           );

-- 26. 是否有生存金给付的保单	Label62	有生存金给付保单	无
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label62 = '有生存金给付的保单' WHERE exists (
                                                                                                 SELECT p.customerno FROM dsj.policy_information p WHERE p.customerno = si1.scustid and p.salecom = si1.salecom and p.appflag = '有效' and p.contno IN (
                                                                                                     SELECT contno FROM dsj.lcget WHERE getdutycode IN (select getdutycode from dsj.lmdutygetalive where getdutyname = '生存保险金')
                                                                                                 )
                                                                                             );

-- 27. 是否有红利可领取的保单	Label64	有红利可领取保单	无
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label64 = '红利可领取的保单' WHERE exists (
                                                                                                SELECT p.customerno FROM dsj.policy_information p, dsj.lmriskapp l
                                                                                                WHERE p.customerno = si1.scustid and p.salecom = si1.salecom and p.appflag = '有效' and p.riskcode = l.riskcode AND l.bonusflag = 1
                                                                                            );

-- 28. 一单寿险客户	Label65	一单寿险客户	客户仅购买了一张保单，且保单类型为寿险型保单
/*
    一张保单对应一个保单号 contno，但表里会有重复保单号，因为一个保单会包含多个险种 riskcode，包含了几个险种就会有几条重复记录。
    如果客户只购买了一张保单，不管保单中包含多少个险种，只要包含“新型寿险”或“人寿保险”险种就认为是一单寿险客户
*/
/*
-- 旧方法，性能低
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label65 = '一单寿险客户' WHERE exists (SELECT temp.customerno FROM (
    SELECT pi.customerno, pi.salecom, count(DISTINCT pi.contno) AS tnum,max(t.pnum) AS pnum FROM (
        SELECT count(DISTINCT p.contno) AS pnum, p.customerno, p.salecom FROM dsj.POLICY_INFORMATION p
    LEFT JOIN dsj.riskkind b on p.riskcode = b.riskcode
    WHERE (b.kindtype = '新型寿险' or b.kindtype = '人寿保险') GROUP BY p.customerno, p.salecom
    ) t LEFT JOIN dsj.POLICY_INFORMATION pi ON pi.customerno = t.customerno and pi.salecom = t.salecom
    GROUP BY pi.customerno, pi.salecom
)temp WHERE temp.tnum = temp.pnum AND tnum = 1 and temp.customerno = si1.scustid and temp.salecom = si1.salecom);
*/

----------- 方法二
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label65 = '一单寿险客户' WHERE exists (
                                                                                              SELECT
                                                                                                  customerno
                                                                                                   , salecom
                                                                                                   , count(DISTINCT contno) AS c	-- 保单号去重后统计该客户总共有多少保单
                                                                                                   , sum(
                                                                                                      CASE
                                                                                                          WHEN b.kindtype IN ('人寿保险', '新型寿险') THEN 1
                                                                                                          ELSE 0
                                                                                                          END
                                                                                                  ) AS s 		-- 统计寿险有几条记录
                                                                                              FROM dsj.POLICY_INFORMATION p, dsj.riskkind b
                                                                                              WHERE p.riskcode = b.riskcode AND p.customerno = si1.scustid AND p.salecom = si1.salecom
                                                                                              GROUP BY customerno, salecom
                                                                                              HAVING c = 1 AND s > 0		-- 筛选只有一个保单且包含寿险的客户
                                                                                          );

-- 29. 缴费期满客户	Label66	缴费期满客户	POLICY_INFORMATION:paycount= payendyear	客户缴费期数已满
/*
-- 旧方法，性能低
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label66 = '缴费期满客户' WHERE exists (
    SELECT temp.customerno FROM (
        SELECT count(*) AS tnum, t.customerno, t.salecom, max(t.pnum) AS pnum FROM (
            SELECT p.customerno, p.salecom, count(*) AS pnum FROM dsj.policy_information p WHERE p.paycount = p.payendyear  GROUP BY p.customerno, p.salecom
    ) t LEFT JOIN dsj.policy_information pi ON pi.customerno = t.customerno and pi.salecom = t.salecom
    GROUP BY t.customerno, t.salecom
    ) temp WHERE temp.tnum = temp.pnum and temp.customerno = si1.scustid and temp.salecom = si1.salecom
);
*/

--------------- 方法二
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label66 = '缴费期满客户' WHERE exists (
                                                                                              SELECT
                                                                                                  p.customerno
                                                                                                   , p.salecom
--			, count(1) AS c -- 不知道为什么会报错“not support udaf count in subquery for join conversion”，count(1)写在having中就没有错
                                                                                                   , sum(
                                                                                                      case
                                                                                                          when p.paycount = p.payendyear then 1
                                                                                                          else 0
                                                                                                          end
                                                                                                  ) AS s
                                                                                              FROM dsj.policy_information p
                                                                                              WHERE p.customerno = si1.scustid and p.salecom = si1.salecom
                                                                                              GROUP BY p.customerno, p.salecom
                                                                                              HAVING count(1) = s
                                                                                          );

-- 30. 持有有效保单产品类型客户标签	Label68	持有有效保单，未持有有效保单		客户名下是否有投保人为自己的有效保单
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label68 = '持有有效保单' WHERE exists (
                                                                                              SELECT salecom FROM dsj.POLICY_INFORMATION p WHERE p.appflag = '有效' and p.customerno = si1.scustid and p.salecom = si1.salecom
                                                                                          );

-- 31. 父母生日提醒	Label78	父亲生日临近 | 母亲生日临近			客户父母生日-当前日期<=5
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label78 = (
    SELECT
        case max(p.insuredsex)
            when '0' then '父亲生日临近'
            when '1' then '母亲生日临近'
            end as s
    from dsj.POLICY_INFORMATION p
    where p.customerno = si1.scustid and p.salecom = si1.salecom
      and (p.RELATIONTOAPPNT = '父母' OR p.RELATIONTOAPPNT = '子女') and p.birthday > p.insuredbirthday -- 父母关系，并且投保人 > 被保人（也就是晚辈给长辈买保险，被保人是长辈）
      and (DATEDIFF(to_char(p.insuredbirthday,"2022-MM-dd"),to_char(SYSDATE,"2022-MM-dd")) between 1 and 5)
);

-- 32. 子女生日提醒	Label79	儿子生日临近 | 女儿生日临近		客户子女生日-当前日期<=5
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label79 = (
    SELECT
        case max(p.insuredsex)
            when '0' then '儿子生日临近'
            when '1' then '女儿生日临近'
            end as s
    from dsj.POLICY_INFORMATION p
    where p.customerno = si1.scustid and p.salecom = si1.salecom
      and (p.RELATIONTOAPPNT = '父母' OR p.RELATIONTOAPPNT = '子女') and p.birthday < p.insuredbirthday -- 父母关系，并且投保人 < 被保人（也就是长辈给晚辈买保险，被保人是晚辈）
      and (DATEDIFF(to_char(p.insuredbirthday,"2022-MM-dd"),to_char(SYSDATE,"2022-MM-dd")) between 1 and 5)
);

-- 33. 近期咨询过理赔	label81	7天内呼入咨询过，呼叫中心服务记录、咨询转办单提取关键字段（包含理赔咨询）或服务记录、咨询转办单中包含理赔字段	近期咨询理赔
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label81 = '近期咨询过理赔' WHERE exists (
                                                                                               SELECT dp.customerno FROM dsj.policy_information dp WHERE dp.customerno = si1.scustid and dp.salecom = si1.salecom and dp.contno IN (
                                                                                                   SELECT rm.contnos FROM dsj.cc_record_main rm
                                                                                                   WHERE rm.reasonsecondname LIKE '%理赔%' and rm.contnos IS not NULL AND DATEDIFF(SYSDATE,rm.starttime) < 7)
                                                                                           );

-- 34. 续期临期未缴费	label88	续期缴费期到宽限期未交费，续期缴费日期-当前日期<5	续期临期未缴费
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label88 = '续期临期未缴费' WHERE exists (
                                                                                               SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag = '有效'AND p.payintv = '期缴' AND p.payendyear<>p.paycount  AND
                                                                                                       DATEDIFF(p.paytodate,to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss.S')) > 0 and DATEDIFF(p.paytodate,to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss.S')) < 5
                                                                                                                                                   and p.customerno = si1.scustid and p.salecom = si1.salecom
                                                                                           );

-- 35. 续期到期未缴费	label89	过了续期宽限期，仍未缴费	续期到期未缴费
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label89 = '续期到期未缴费' WHERE exists (
                                                                                               SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag = '有效'AND p.payintv = '期缴' AND p.payendyear<>p.paycount  AND
                                                                                                       to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss.S') > DATE_ADD(p.paytodate, 60)
                                                                                                                                                   and p.customerno = si1.scustid and p.salecom = si1.salecom
                                                                                           );

-- 36. 失效客户	Label91	失效客户	所有保单都是失效状态	客户名下所有保单均失效的客户
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label91 = '失效客户' WHERE exists (SELECT temp.customerno FROM (
                                                                                                                            SELECT count(*) AS tnum,max(t.customerno) AS customerno, max(t.salecom) as salecom,max(t.pnum) AS pnum FROM (
                                                                                                                                                                                                                                            SELECT p.customerno, p.salecom,count(*) AS pnum FROM  dsj.policy_information p WHERE p.appflag <> '有效' GROUP BY p.customerno, p.salecom
                                                                                                                                                                                                                                        ) t LEFT JOIN dsj.policy_information pi ON pi.customerno = t.customerno and pi.salecom = t.salecom
                                                                                                                            GROUP BY pi.customerno, pi.salecom
                                                                                                                        ) temp WHERE temp.tnum = temp.pnum and temp.customerno = si1.scustid and temp.salecom = si1.salecom);

/*
-- 方法二（性能反而慢，原因待查）
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label91 = '失效客户' WHERE EXISTS (
    SELECT
        p.customerno
        , p.salecom
--			, count(1) as c	 -- 不知道为什么会报错“not support udaf count in subquery for join conversion”，count(1)写在having中就没有错
        , sum(
            case
                when p.appflag <> '有效' then 1
                else 0
            end
        ) as s
    from dsj.policy_information p
    where p.customerno = si1.scustid and p.salecom = si1.salecom
    group by p.customerno, p.salecom
    having count(1) = s
);
*/

-- 37. 失效原因	Label92
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label92 = (
    SELECT '有'||t.terminatestate||'保单' FROM (
                                                SELECT row_number()over(partition by p.customerno, p.salecom order by p.polapplydate desc) rn,p.customerno, p.salecom,p.terminatestate FROM dsj.policy_information p
                                                WHERE p.appflag = '中止'
                                            ) t WHERE t.rn = 1 AND si1.scustid = t.customerno and si1.salecom = t.salecom
);

-- 38. 终止原因	Label94
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label94 = (
    SELECT '有'||t.terminatestate||'保单' FROM (
                                                SELECT row_number()over(partition by p.customerno, p.salecom order by p.polapplydate desc) rn,p.customerno, p.salecom,p.terminatestate FROM dsj.policy_information p
                                                WHERE p.appflag = '终止'
                                            ) t WHERE t.rn = 1 AND si1.scustid = t.customerno and si1.salecom = t.salecom
);

-- 39. 上年续期交费情况 Label95
-- 通过contno与dsj.policy_information表关联会有关联不上的数据，关联不上的数据不做处理
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label95 = (
    SELECT
        CASE
            WHEN t.dnum=0 THEN '上一年如期缴费'
            WHEN t.dnum<0 THEN '上一年提前'||abs(t.dnum)||'天交费'
            WHEN t.dnum>0 AND t.dnum<=60 THEN '上一年滞后'||t.dnum||'天交费'
            WHEN t.dnum>60 THEN '上一年宽限期外交费'
            END
    FROM (
             SELECT
                 row_number()over(PARTITION BY l.appntno ORDER BY l.confdate DESC) rn,l.appntno,DATEDIFF(to_char(l.confdate,'0000-MM-dd'),to_char(l.curpaytodate,'0000-MM-dd')) AS dnum, p.salecom
             FROM dsj.ljapayperson l, dsj.policy_information p WHERE p.contno = l.contno and l.paycount > 1 AND YEAR(l.confdate) = YEAR(sysdate) - 1
         ) t WHERE  t.rn = 1 AND t.appntno = si1.scustid and t.salecom = si1.salecom
);

-- 40. 高金额	Label107	高金额		关于高金额的指标，鉴于大数据无法计算件均保费，调整逻辑如下：
-- 二期只计算这3个渠道
--根据业绩归属渠道、缴费方式、以及期缴保费划分如下
		--02个险渠道            期缴 大于20000以上
		--03银保渠道            期缴 大于50000以上
		--06健康险               期缴 大于 20000以上
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label107 = '高金额' WHERE exists (
                                                                                            SELECT p.customerno FROM dsj.policy_information p WHERE
                                                                                                    p.appflag = '有效' AND p.payintv = '期缴' and
                                                                                                ((p.salecom = '03' AND p.prem>50000) OR (p.salecom = '02' AND p.prem>20000) OR (p.salecom = '06' AND p.prem>20000))
                                                                                                                                                and p.customerno = si1.scustid and p.salecom = si1.salecom
                                                                                        );

-- 41. 最近理赔结案	label108	最近15天做过理赔，当前日期-理赔结案日期<15天	最近理赔结案
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label108 = '最近理赔结案' WHERE exists (
                                                                                               SELECT c.insuredno FROM dsj.INSURANCE_CLAIM c, dsj.policy_information p
                                                                                               WHERE DATEDIFF(to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss.S'),c.endcasedate) < 15
                                                                                                 and p.contno = c.contno and c.insuredno = si1.scustid and p.salecom = si1.salecom
                                                                                           );

-- 42. 理赔报案追踪	Label109	理赔报案追踪		理赔状态为报案状态，且理赔报案日-当前日期<30天
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label109 = '理赔报案追踪' WHERE exists (
                                                                                               SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag = '有效' and p.customerno = si1.scustid and p.salecom = si1.salecom and p.contno IN (
                                                                                                   SELECT c.CONTNO FROM dsj.insurance_claim c WHERE c.LLCLAIMSTATE = '报案' AND DATEDIFF(sysdate(), c.RPTDATE) <30
                                                                                               )
                                                                                           );

-- 43. 理赔处理中	Label110	理赔处理中		理赔状态为受理或立案
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label110 = '理赔处理中' WHERE exists (
                                                                                              SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag = '有效' and p.customerno = si1.scustid and p.salecom = si1.salecom and p.contno IN (
                                                                                                  SELECT c.contno FROM dsj.INSURANCE_CLAIM c WHERE c.llclaimstate = '受理' OR c.llclaimstate = '立案'
                                                                                              )
                                                                                          );

-- 44. 最近保全完成	label111	最近15天做过保全，来电日期-保全申请日期<15天	最近保全完成
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label111 = '最近保全完成' WHERE exists (
                                                                                               SELECT p.customerno FROM dsj.policy_information p WHERE p.customerno = si1.scustid and p.salecom = si1.salecom and p.contno IN (
                                                                                                   SELECT l.contno FROM dsj.lpedoritem l WHERE DATEDIFF(to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss.S'),l.edorappdate) < 15 AND l.edorstate = '0'
                                                                                               )
                                                                                           );

-- 45. 生存金未领	Label113	生存金未领	判断逻辑不详	生存金领取方式为自动转账、存在生存金且未领取
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label113 = '生存金未领' WHERE exists (
                                                                                              SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag = '有效' and p.customerno = si1.scustid and p.salecom = si1.salecom and p.contno IN (
                                                                                                  SELECT contno FROM dsj.Lcinsureacc WHERE acctype = '005' and   insuaccbala>0
                                                                                              )
                                                                                          );

-- 46. 红利垫缴还款	Label115	红利垫缴还款	判断逻辑不详	存在红利，且红利已经垫交保单还款
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label115 = '红利垫缴还款' WHERE exists (
                                                                                               SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag = '有效' and p.customerno = si1.scustid and p.salecom = si1.salecom and p.contno IN (
                                                                                                   SELECT a.contno FROM dsj.LOLOANDETAIL a,dsj.LDCode1 b WHERE
                                                                                                           b.codetype='lnmoneytype' AND b.code=a.finfeetype and b.code1=a.moneytype
                                                                                                                                                           AND ((a.finfeetype = 'HL' AND a.moneytype = 'SX') OR (a.finfeetype = 'SC' AND a.moneytype = 'HK'))
                                                                                               )
                                                                                           );

-- 47. 宽限期	label117	宽限日末日-当前日期<3天	宽限期
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.label117 = '宽限期' WHERE exists (
                                                                                            SELECT p.customerno FROM dsj.policy_information p WHERE
                                                                                                    p.payintv= '期缴' AND p.appflag='有效' AND p.payendyear!=p.paycount and
                                                                                                    DATEDIFF(DATE_ADD(p.paytodate, 60),to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss.S')) > 0 AND DATEDIFF(DATE_ADD(p.paytodate, 60),to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss.S')) < 3
                                                                                                                                                and p.customerno = si1.scustid and p.salecom = si1.salecom
                                                                                        );

--48. 重点银行	Label120	重点银行
	-- 二期只计算银保渠道(03)
UPDATE shanglifeecif.customerno_salecom_relation si1 SET si1.Label120 = '重点银行' WHERE exists (
                                                                                             SELECT p.customerno FROM dsj.policy_information p WHERE p.customerno = si1.scustid and si1.salecom = '03' and p.AGENTCOM LIKE '%招商银行%'
                                                                                         );



dbms_output.put_line('update_customerno_salecom_relation_labels 函数跑批完成！');
EXCEPTION
			WHEN HIVE_EXCEPTION THEN
INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());
WHEN Others THEN
INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());

END;



CREATE OR REPLACE PROCEDURE shanglifeecif.updateindividuallable() IS
              DECLARE
BEGIN
    --1	学历	label1	按照客户最新记录中的学历取值	幼儿园、小学、中专、职高、技校、初中、高中、大专、本科、硕士及以上、其他
UPDATE shanglifeecif.Individual si1 SET si1.label1 = si1.Education where si1.Education is not null;
--2	职业	label2	按照客户最新记录中的职业名称取值	食用调料制作工、味精制作工、糕点、面包烘焙工、米面主食制作工、油脂制品工等

UPDATE shanglifeecif.Individual si1 SET si1.label2 = si1.Occupation where si1.Occupation is not null;
--3	出生日期区间	label3	根据出生日期，判断客户年代归属	60后、70后、80后、90后、00后、10后等
UPDATE shanglifeecif.Individual si1 SET si1.label3 = substr(si1.birthday,3,1) || '0后' where 1 = 1;
--4	年龄区间	label4	"case age when 0<=age and age<13 then '儿童' when  13<=age and age<19 then '少年' when 19<=age and age<41 then '青年' when 41<=age and age<66 then '中年' --else '老年'"	儿童、少年、青年、中年、老年
	--UPDATE shanglifeecif.Individual si1 SET si1.label4 = (SELECT CASE WHEN temp.age>=0 AND temp.age<13 THEN '儿童' WHEN temp.age>=13 AND temp.age <19 THEN '少年' WHEN temp.age -->=19 AND temp.age <41 THEN '青年' WHEN temp.age >=41 AND temp.age <66 THEN '中年' ELSE '老年' END FROM (SELECT TRUNC(months_between(sysdate, si2.birthday)/12) age,si2.indid --FROM shanglifeecif.Individual si2) temp WHERE temp.indid = si1.indid);
--5	联系地址归属省份	label5	按照客户最近一次办理业务给出的最新联系地址取所在省名称或地级市的名称	省、直辖市：江苏、北京、上海等
	--6	联系地址归属地区	label6	按照客户最近一次办理业务给出的最新联系地址取所在市名称或者地级市所在区的名称	市：苏州市、南京市等
	--7	婚姻状况	label9	"根据婚姻状态字段判断 case maritalStat when maritalStat=1 then '未婚' when  maritalStat=2 then '已婚' when maritalStat=3 then '丧偶' when maritalStat=4 --then '离异' else null"	1未婚、2已婚、3丧偶、4离异
UPDATE shanglifeecif.Individual si1 SET si1.label9 = si1.maritalStat WHERE 1 = 1;
--UPDATE shanglifeecif.Individual si1 SET si1.label9 = (
        --case si1.maritalStat
            --when 1 then '未婚'
            --when 2 then '已婚'
            --when 3 then '丧偶'
            --when 4 then '离异'
            --else NULL END
        --) WHERE 1 = 1;
--8	子女数量区间	label10	"根据子女登录（登记录入）数量判断 case COChild when COChild=0  then '无子女' when COChild=1  then '1孩' when COChild>0  then '2孩及以上'
	--else null"	无子女、1孩、2孩及以上
UPDATE shanglifeecif.individual si1 SET si1.cochild = (
    SELECT nvl(t.cnum,0) FROM (
                                  SELECT si.indid1,count(*) AS cnum FROM shanglifeecif.IndRelationShip si
                                  WHERE si.rstype = '子女' GROUP BY si.indid1
                              ) t WHERE t.indid1 = si1.scustid
) ;

UPDATE shanglifeecif.Individual si1 SET si1.label10 = (
    case
        when si1.COChild=0  then '无子女'
        when si1.COChild=1  then '1孩'
        when si1.COChild>=2  then '2孩及以上'
        else null END
    ) WHERE si1.cochild is not null;
--9	最近5年是否曾或正在接受治疗	label11	按照该字段标识最后一次更新的结果判断，如果标识为true则显示“最近五年曾或正在接受治疗”，否则不做显示	近5年有治疗，近5年无治疗
    --10	是否饮酒	label12	按照该字段标识判断，只要记录中有一次标识为true，则显示“饮酒”，否则不做显示	饮酒
	--11	是否吸烟	label13	按照该字段标识判断，只要记录中有一次标识为true，则显示“吸烟”，否则不做显示	吸烟
	--12	是否有先天性疾病	label14	按照该字段标识最后一次更新的结果判断，如果标识为true则显示“有先天性疾病”，否则不做显示	有先天性疾病
	--13	是否患过重大疾病	label15	按照该字段标识最后一次更新的结果判断，如果标识为true则显示“患过重大疾病”，否则不做显示	患过重大疾病
	--14	家族是否有遗传病史	label16	按照该字段标识最后一次更新的结果判断，如果标识为true则显示“有遗传病”，否则不做显示	家族有遗传病史
	--15	是否正在怀孕	label17	按照该字段标识最后一次更新的结果判断，如果标识为true则显示“怀孕”，否则不做显示	正在怀孕
	--16	是否有早产、过期产、难产情况	label18	按照该字段标识判断，只要记录中有一次标识为true，则显示“有早产、过期产、难产情况”，否则不做显示	有早产、过期产、难产情况
	--17	是否进行危险性运动	label20	按照该字段标识判断，只要记录中有一次标识为true，则显示“进行危险性运动”，否则不做显示	进行危险性运动
	--18	个人年收入区间	label21	"根据个人年收入字段进行判断：case PIncome when PIncome<=120000 then '低收入' when PIncome>120000 and PIncome<=1000000 then '中产' when --PIncome>1000000 then '富人' else null"	低收入、中产、富人
	--根据数据判断单位应该是万元，这里都除以10000做判断
UPDATE shanglifeecif.Individual si1 SET si1.Label21 = (
    CASE
        WHEN si1.pincome >=0 AND si1.pincome<=12 THEN '低收入'
        WHEN si1.pincome >12 AND si1.pincome<=100 THEN '中产'
        WHEN si1.pincome > 100 THEN '富人'
        else null END
    ) where 1 = 1;
--19	拥有车辆数量区间	label22	"根据车辆登录数量字段进行判断：case COVehicle when COVehicle=0 then '无车' when COVehicle=1 then '1辆车' when COVehicle>1 then '2辆车以上'
    --else null" 无车，1辆车，2辆车及以上
UPDATE shanglifeecif.Individual si1 SET si1.Label22 = (
    CASE
        WHEN si1.COVehicle ==0  THEN '无车'
        WHEN si1.COVehicle == 1  THEN '1辆车'
        WHEN si1.COVehicle > 1  THEN '2辆车以上'
        ELSE NULL END
    ) where 1 = 1;
--20	拥有住房套数	label23	"根据房屋登录数量字段进行判断：case CORP when CORP=0 then '无房' when CORP=1 then '1套房' when CORP>1 then '2套房以上'
	--else null"	无房，1套房，2套房及以上
UPDATE shanglifeecif.Individual si1 SET si1.Label23 = (
    CASE
        WHEN si1.CORP ==0  THEN '无房'
        WHEN si1.CORP == 1  THEN '1套房'
        WHEN si1.CORP > 1  THEN '2套房以上'
        else null END
    ) where 1 = 1;
--21	是否持有寿险有效保单	label24	筛选客户名下所有保单，保单中客户为投保人，保单险种大类为寿险	持有寿险有效保单
UPDATE shanglifeecif.Individual si1 SET si1.label24 = '持有寿险有效保单' WHERE si1.scustid in
                                                                       (SELECT p.customerno FROM dsj.policy_information p,dsj.riskkind l WHERE p.riskcode = l.riskcode AND p.appflag = '有效' AND (l.kindtype = '新型寿险' OR l.kindtype = '人寿保险'));
--22	保单件数区间	label25	"判断客户作为投保人所有的保单数量(有效保单数量+失效保单数量）：NOVPolicy+NOIVPolicy=pCount(保单总件数)
	--case pCount when pCount=1 then '1件' when pCount=2 then '2件' when pCount>2 and pCount<6 then '3-5件' when pCount>5 and pCount<11 then '6-10件'
	--when pCount>10 and pCount<21 then '10-20件'when pCount>20 and pCount<51 then '21-50件'
	--when pCount>50 then '50件以上' else '无保单'"	无保单、1件保单、2件保单、3-5件保单、6-10件保单、11-20件保单、20-50件保单、50件以上保单
	--第一步 更新 Individual.NOVPolicy 有效记录数
	--UPDATE shanglifeecif.Individual si1 SET si1.NOVPolicy = (SELECT nvl(t.cnum,0) FROM (
                                                                                             --SELECT temp.customerno,count(*) AS cnum FROM (
			--SELECT max(p.customerno) customerno,p.contno FROM dsj.policy_information p WHERE p.appflag = '有效' GROUP BY p.contno
		--) temp GROUP BY temp.customerno
	--) t WHERE t.customerno =  si1.scustid);



--第二步 更新 Individual.NOIVPolicy 无效记录数
	--UPDATE shanglifeecif.Individual si1 SET si1.noivpolicy = (SELECT nvl(t.cnum,0) FROM (
                                                                                              --SELECT temp.customerno,count(*) AS cnum FROM (
			--SELECT max(p.customerno) customerno,p.contno FROM dsj.policy_information p WHERE p.appflag <> '有效' GROUP BY p.contno
		--) temp GROUP BY temp.customerno
	--) t WHERE t.customerno =  si1.scustid);

--第三步
	--UPDATE shanglifeecif.Individual si1 SET si1.Label25 = (
    --CASE
        --WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) == 0 THEN '无保单'
            --WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) == 1 THEN '1件'
            --WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) == 2 THEN '2件'
            --WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 3  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) <= 5 THEN '3-5件'
            --WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 6  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) <= 10 THEN '6-10件'
            --WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 11  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0))<= 20 THEN '11-20件'
            --WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 21  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) <= 50 THEN '21-50件'
            --WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) > 50 THEN '50件以上'
            --END ) where 1 = 1;

--UPDATE shanglifeecif.Individual si1 SET si1.Label25 = (
    --CASE
        --WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) == 0 THEN '无保单'
            --WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) == 1 THEN '1件保单'
            --WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) == 2 THEN '2件保单'
            --WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 3  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) <= 5 THEN '3-5件保单'
            --WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 6  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) <= 10 THEN '6-10件保单'
            --WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 11  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0))<= 20 THEN '11-20件保单'
            --WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) >= 21  AND (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) <= 50 THEN '21-50件保单'
            --WHEN (nvl(si1.NOVPolicy,0) + nvl(si1.NOIVPolicy,0)) > 50 THEN '50件保单以上'
            --END
            --) where 1 = 1;


--23	是否贷款	label26	筛选客户为投保人的有效保单，且办理了贷款业务	有贷款
UPDATE shanglifeecif.Individual si1 SET si1.label26 = '有保单贷款' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag = '有效' and p.contno IN (
        SELECT contno FROM dsj.loloandetail WHERE moneytype='DK'
    )
);
--24	是否有极短意保单/是否仅有极短意保单	 label27	"客户为保单投保人 case AGRMNTAge(保险期限) when avg(sum(AGRMNTAges))<90 then '仅极短意外险'
			--when AGRMNTAge<90  then '有极短意外险'"	仅极短意外险，有极短意外险	志广说只用判断有极短意外险不用判断仅有
		/*UPDATE shanglifeecif.Individual si1 SET si1.Label27 = (
			SELECT
				CASE
					WHEN temp.cnt >0 THEN
							'有极短意外险'
				END
			FROM
				(
				SELECT
					count(1) cnt,
					policy.customerno
				FROM
					policy_information policy
				where
					policy.SECURITY in('7天','15天','30天','90天','1月','3月')
				and
					policy.riskperiod='短期险'
				and
					policy.riskcode not in('1066008','1066007','1066006','1066004','1066003','1066002','1066001','1065005','1065003','1065001','1025003')

				GROUP BY
					policy.customerno
				) temp
			WHERE temp.customerno = si1.scustid
		);*/

UPDATE shanglifeecif.Individual si1 SET si1.Label27 = (
    SELECT
        CASE
            WHEN temp.cnt > 0 AND temp.cnt = temp.tnum THEN '仅极短意外险'
            WHEN temp.cnt > 0 AND temp.cnt <> temp.tnum THEN '有极短意外险'
            END
    FROM (
             SELECT p.customerno,max(cnt) cnt,nvl(count(*),0) tnum FROM (
                                                                            SELECT
                                                                                count(1) cnt,
                                                                                po.customerno
                                                                            FROM
                                                                                dsj.policy_information po
                                                                            where
                                                                                    po.SECURITY in('7天','15天','30天','90天','1月','3月')
                                                                              and
                                                                                    po.riskperiod='短期险'
                                                                              and
                                                                                    po.riskcode not in('1066008','1066007','1066006','1066004','1066003','1066002','1066001','1065005','1065003','1065001','1025003')
                                                                            GROUP BY
                                                                                po.customerno
                                                                        ) t LEFT JOIN dsj.policy_information p ON t.customerno = p.customerno GROUP BY p.customerno
         ) temp where temp.customerno = si1.scustid

);
--29	是否有趸交保单	Label29	有趸交保单	NOSPInsurance，POLICY_INFORMATION.PAYINTV='趸缴',PREMIUM_LIST.PAYMENT_PERIOD='一次交清'
UPDATE shanglifeecif.Individual si1 SET si1.Label29 = '有趸交保单' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.payintv = '趸缴' and p.riskperiod = '长期险' AND p.contno IN (SELECT l.contno FROM dsj.PREMIUM_LIST l WHERE l.PAYMENT_PERIOD LIKE '%一次交清%')
);
--30	是否给本人投保	Label30	仅本人投保，本人已投保，本人未投保	Iself
UPDATE shanglifeecif.Individual si1 SET si1.Label30 = (

    SELECT
        CASE
            WHEN tt.bnum = 0  THEN '本人未投保'
            WHEN tt.bnum > 0 and tt.bnum <> tt.tnum THEN '为本人投保'
            WHEN tt.bnum > 0 and tt.bnum = tt.tnum THEN '仅为本人投保'
            END

    FROM (
             SELECT t.customerno,MAX(t.bnum) bnum,count(*) as tnum FROM (
                                                                            SELECT count(*) AS bnum,p.customerno FROM dsj.policy_information p WHERE p.relationtoappnt = '本人' GROUP BY p.customerno
                                                                        ) t LEFT JOIN dsj.policy_information pi ON pi.customerno = t.customerno GROUP BY t.customerno
         ) tt WHERE tt.customerno = si1.scustid

);
--32	是否给父母投保	Label31	仅父母投保，父母已投保，父母未投保	Iparent
UPDATE shanglifeecif.Individual si1 SET si1.Label31 = (

    SELECT
        CASE
            WHEN tt.bnum = 0  THEN '父母未投保'
            WHEN tt.bnum > 0 and tt.bnum <> tt.tnum THEN '为父母投保'
            WHEN tt.bnum > 0 and tt.bnum = tt.tnum THEN '仅为父母投保'
            END

    FROM (
             SELECT t.customerno,MAX(t.bnum) bnum,count(*) as tnum FROM (
                                                                            SELECT count(*) AS bnum,p.customerno FROM dsj.policy_information p WHERE (p.RELATIONTOAPPNT = '子女' OR p.RELATIONTOAPPNT = '父母') AND p.birthday > p.insuredbirthday
                                                                            GROUP BY p.customerno
                                                                        ) t LEFT JOIN dsj.policy_information pi ON pi.customerno = t.customerno GROUP BY t.customerno
         ) tt WHERE tt.customerno = si1.scustid

);
--34	是否给子女投保	Label32	仅子女投保，子女已投保，子女未·投保	Ichildren
UPDATE shanglifeecif.Individual si1 SET si1.Label32 = (

    SELECT
        CASE
            WHEN tt.bnum = 0  THEN '子女未投保'
            WHEN tt.bnum > 0 and tt.bnum <> tt.tnum THEN '为子女投保'
            WHEN tt.bnum > 0 and tt.bnum = tt.tnum THEN '仅为子女投保'
            END

    FROM (
             SELECT t.customerno,MAX(t.bnum) bnum,count(*) as tnum FROM (
                                                                            SELECT nvl(count(*),0) AS bnum,p.customerno FROM dsj.policy_information p WHERE (p.RELATIONTOAPPNT = '子女' OR p.RELATIONTOAPPNT = '父母') AND p.birthday < p.insuredbirthday GROUP BY p.customerno
                                                                        ) t LEFT JOIN dsj.policy_information pi ON pi.customerno = t.customerno GROUP BY t.customerno
         ) tt WHERE tt.customerno = si1.scustid
);


--36	是否给配偶投保	Label33	仅配偶投保，配偶已投保，配偶未投保	Imate
UPDATE shanglifeecif.Individual si1 SET si1.Label33 = (

    SELECT
        CASE
            WHEN tt.bnum = 0  THEN '配偶未投保'
            WHEN tt.bnum > 0 and tt.bnum <> tt.tnum THEN '为配偶投保'
            WHEN tt.bnum > 0 and tt.bnum = tt.tnum THEN '仅为配偶投保'
            END

    FROM (
             SELECT t.customerno,MAX(t.bnum) bnum,count(*) as tnum FROM (
                                                                            SELECT count(*) AS bnum,p.customerno FROM dsj.policy_information p WHERE p.relationtoappnt = '配偶' GROUP BY p.customerno
                                                                        ) t LEFT JOIN dsj.policy_information pi ON pi.customerno = t.customerno GROUP BY t.customerno
         ) tt WHERE tt.customerno = si1.scustid
);
--47	是否仅有1年期短险保单	Label43	仅有1年期短险保单，有1年期短险保单	"policy_information表 SECURITY in('1年','6月','180天') riskperiod=‘短期险’
	--riskcode not in('1066008','1066007','1066006','1066004','1066003','1066002','1066001','1065005','1065003','1065001','1025003')"	客户为保单投保人，所有保单的保障期限为一年
	/*UPDATE shanglifeecif.Individual si1 SET si1.Label43 = (
		SELECT
			CASE
				WHEN temp.cnt >0 THEN
						'有1年期短险保单'
			END
		FROM
			(
			SELECT
				count(1) cnt,
				policy.customerno
			FROM
				policy_information policy
			where
				policy.SECURITY in('1年','6月','180天')
			and
				policy.riskperiod='短期险'
			and
				policy.riskcode not in('1066008','1066007','1066006','1066004','1066003','1066002','1066001','1065005','1065003','1065001','1025003')

			GROUP BY
				policy.customerno
			) temp
		WHERE
		temp.customerno = si1.scustid
	);*/

UPDATE shanglifeecif.Individual si1 SET si1.Label43 = (
    SELECT
        CASE
            WHEN temp.cnt > 0 AND temp.cnt = temp.tnum THEN '仅有1年期短险保单'
            WHEN temp.cnt > 0 AND temp.cnt <> temp.tnum THEN '有1年期短险保单'
            END
    FROM (
             SELECT p.customerno,max(cnt) cnt,nvl(count(*),0) tnum FROM (
                                                                            SELECT
                                                                                count(1) cnt,
                                                                                po.customerno
                                                                            FROM
                                                                                dsj.policy_information po
                                                                            where
                                                                                    po.SECURITY in('1年','6月','180天')
                                                                              and
                                                                                    po.riskperiod='短期险'
                                                                              and
                                                                                    po.riskcode not in('1066008','1066007','1066006','1066004','1066003','1066002','1066001','1065005','1065003','1065001','1025003')
                                                                            GROUP BY
                                                                                po.customerno
                                                                        ) t LEFT JOIN dsj.policy_information p ON t.customerno = p.customerno GROUP BY p.customerno
         ) temp where temp.customerno = si1.scustid

);
--48	是否有失效保单	Label44	有失效保单	NOIVPolicy	客户为保单投保人，持有保单中保单状态为失效 （最新文档逻辑）
	--		SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag <> '有效' and p.contno IN (select contno from dsj.lccontstate where statetype = 'Available' and state = '1')

UPDATE shanglifeecif.Individual si1 SET si1.label44 = '有失效保单' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag <> '有效' and p.contno IN (select contno from dsj.lccontstate where statetype = 'Available' )
);
--49	是否有缴费期满长险保单	Label45	有缴费期满长险保单	POLICY_INFORMATION:paycount= payendyear AND riskperiod='长期险'	客户持有保险期限为一年以上，剩余保费期数为0
UPDATE shanglifeecif.Individual si1 SET si1.label45 = '有缴费期满长险保单' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag = '有效' and p.paycount = p.payendyear AND p.riskperiod='长期险'
);
--50	是否有续期缴费的保单	Label46	有续期缴费的保单		客户为投保人，含有待缴费状态的保单
UPDATE shanglifeecif.Individual si1 SET si1.label46 = '有续期缴费保单' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag = '有效' and p.contno IN (
        select otherno from dsj.ljspay where othernotype = '2'
    )
);
--51	有责任期满的保单	Label47	有责任期满的保单		客户持有保单含有保险期限已满的保单
UPDATE shanglifeecif.Individual si1 SET si1.label47 = '有责任期满的保单' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.enddate < to_char(SYSDATE,"yyyy-MM-dd HH:mm:ss.S")
);

--52	第一张保单投保日期	Label48	首次投保日期XXXX-XX-XX	FADate	客户所有保单中投保时间最早的日期
	--第一步
UPDATE shanglifeecif.Individual si1 SET si1.FADate = (
    SELECT fadate FROM (
                           SELECT p.customerno,min(p.polapplydate) AS fadate  FROM dsj.POLICY_INFORMATION p WHERE p.polapplydate IS NOT null GROUP BY p.customerno
                       ) t WHERE t.customerno = si1.scustid
);

UPDATE shanglifeecif.Individual si1 SET si1.FADate = (
    SELECT fadate FROM (
                           SELECT p.insuredno,min(p.polapplydate) AS fadate  FROM dsj.POLICY_INFORMATION p WHERE p.polapplydate IS NOT null GROUP BY p.insuredno
                       ) t WHERE t.insuredno = si1.scustid
);

UPDATE shanglifeecif.Individual si1 SET si1.label48 = '首次投保日期'||  to_char(si1.fadate, 'yyyy-MM-dd')  WHERE si1.fadate IS NOT NULL;

--53	最后一次寿险投保距今时长	Label49	最后一次投保距今XXX天	LAPPDate	当前日期减去客户所有保单中最后一次投保的保单的投保时间
UPDATE shanglifeecif.Individual si1 SET si1.LAPPDate = (
    SELECT fadate FROM (
                           SELECT p.customerno,max(p.polapplydate) AS fadate  FROM dsj.POLICY_INFORMATION p WHERE p.polapplydate IS NOT null GROUP BY p.customerno
                       ) t WHERE t.customerno = si1.scustid
);

UPDATE shanglifeecif.Individual si1 SET si1.LAPPDate = (
    SELECT fadate FROM (
                           SELECT p.insuredno,max(p.polapplydate) AS fadate  FROM dsj.POLICY_INFORMATION p WHERE p.polapplydate IS NOT null GROUP BY p.insuredno
                       ) t WHERE t.insuredno = si1.scustid
);

UPDATE shanglifeecif.Individual si1 SET si1.label49 = '最后一次投保距今' || DATEDIFF(sysdate(), si1.LAPPDate) || '天' WHERE si1.lappdate IS NOT NULL;

--54	最近一次保单所属渠道	Label50	最近保单团险渠道、最近保单个人营销、最近保单银行代理、最近保单中介渠道、最近保单网销渠道	--LPChannel，InsuranceArrangement.AgentChannel	客户最后一次投保归属渠道
UPDATE shanglifeecif.Individual si1 SET si1.label50 =  (SELECT '最近保单' || trim(t.salecomname) FROM (
                                                                                                      SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate DESC) rd,p.salecomname  FROM dsj.POLICY_INFORMATION p
                                                                                                      WHERE p.salecomname IS NOT null
                                                                                                  ) t WHERE t.rd = 1 AND t.customerno = si1.scustid);


--UPDATE shanglifeecif.Individual si1 SET si1.label50 =  (SELECT t.SALECHNL FROM (
                                                                                     --SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate DESC) rd,p.SALECHNL  FROM POLICY_INFORMATION p WHERE p.salechnl IS NOT null
		--) t WHERE t.rd = 1 AND t.customerno = si1.scustid);

--UPDATE shanglifeecif.Individual si1 SET si1.label50 =  '最近保单' || si1.label50 WHERE si1.label50 IS NOT NULL;
--55	最近一次保单所属机构	Label51	最近XX机构	LPOrg,InsuranceArrangement.AgentOrg	客户最后一次投保归属机构
	--UPDATE shanglifeecif.Individual si1 SET si1.label51 =  (SELECT t.AGENTCOM FROM (
                                                                                         --	SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate DESC) rd,p.AGENTCOM  FROM POLICY_INFORMATION p WHERE p.salechnl IS NOT null
                                                                                         --) t WHERE t.rd = 1 AND t.customerno = si1.scustid);

--UPDATE shanglifeecif.Individual si1 SET si1.label51 =  '最近机构' || si1.label51 WHERE si1.label51 IS NOT NULL;
--56	最早保单所属渠道	Label52	首单团险渠道、首单个人营销、首单银行代理、首单中介渠道、首单网销渠道	FAChannel	客户第一次投保时保单归属渠道
UPDATE shanglifeecif.Individual si1 SET si1.label52 =  (SELECT '首单' || trim(t.salecomname) FROM (
                                                                                                    SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate asc) rd,p.salecomname  FROM dsj.POLICY_INFORMATION p WHERE p.salecomname IS NOT null
                                                                                                ) t WHERE t.rd = 1 AND t.customerno = si1.scustid);


--UPDATE shanglifeecif.Individual si1 SET si1.label52 =  (SELECT t.SALECHNL FROM (
                                                                                     --SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate asc) rd,p.SALECHNL  FROM POLICY_INFORMATION p WHERE p.salechnl IS NOT null
		--) t WHERE t.rd = 1 AND t.customerno = si1.scustid);

--UPDATE shanglifeecif.Individual si1 SET si1.label52 =  '首单' || si1.label52 WHERE si1.label52 IS NOT NULL;
--57	最早保单所属机构	Label53	首单XX机构	FAOrg	客户第一次投保时保单归属机构
	--UPDATE shanglifeecif.Individual si1 SET si1.label53 =  (SELECT t.AGENTCOM FROM (
                                                                                         --	SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate ASC) rd,p.AGENTCOM  FROM POLICY_INFORMATION p WHERE p.salechnl IS NOT null
                                                                                         --) t WHERE t.rd = 1 AND t.customerno = si1.scustid);

--UPDATE shanglifeecif.Individual si1 SET si1.Label53 =  '最近机构' || si1.Label53 WHERE si1.Label53 IS NOT NULL;
--58	最近保单状态	Label54	"未承保承保新增附加险终止续保未对账对账失败"	LPState	客户最后一张保单的保单状态
	/*
	UPDATE shanglifeecif.Individual si1 SET si1.label54 =
	(SELECT
		(CASE temp.appflag
		WHEN '0' THEN '最近保单状态未承保'
		WHEN '1' THEN '最近保单状态承保'
		WHEN '2' THEN '最近保单状态新增附加险'
		WHEN '4' THEN '最近保单状态终止'
		WHEN '9' THEN '最近保单状态续保'
		WHEN 'B' THEN '最近保单状态未对账'
		WHEN 'F' THEN '最近保单状态对账失败'
		END) a
	FROM (
		SELECT t.appflag,p.customerno,row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate desc) rd FROM dsj.policy_information p,dsj.lccont t WHERE p.contno = t.contno
	) temp WHERE temp.rd = 1  AND si1.scustid = temp.customerno);
	*/
UPDATE shanglifeecif.Individual si1 SET si1.label54 = (
    SELECT '最近保单状态'||trim(t.appflag) FROM (
                                              SELECT row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate DESC) rd,p.customerno,p.appflag FROM dsj.policy_information p
                                          ) t WHERE t.rd = 1 AND si1.scustid = t.customerno
);

--	最近一次保全类型	label55	客户最后一次办理保全业务的业务类型

UPDATE shanglifeecif.Individual si1 SET si1.label55 = (
    SELECT '客户最近一次办理'||t.edorname FROM (
                                           SELECT
                                               row_number() over(partition by p.customerno ORDER BY l.edorAPPDATE DESC,l.uwtime DESC) rn,
                                               l.edortype,
                                               l.contno,
                                               p.customerno,
                                               l.edorappdate,
                                               l.edorstate,
                                               lm.edorcode,
                                               lm.edorname
                                           FROM dsj.lpedoritem l,dsj.lmedoritem lm,dsj.policy_information p
                                           WHERE l.edortype = lm.edorcode AND lm.appobj <> 'G' AND l.edorstate = '0' AND p.contno = l.contno
                                       ) t WHERE t.rn = 1 AND si1.scustid = t.customerno
) WHERE 1 = 1;


--60	是否有退保保单	Label56	有退保保单	如何判断？	客户所有的保单中是否存在保单状态为退保的保单
UPDATE shanglifeecif.Individual si1 SET si1.label56 = '有退保保单' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.contno IN (
        select contno from dsj.lpedoritem where  edortype in ('CT', 'XT', 'GT') and edorstate = '0'
    )
);

--61	最近一次理赔类型	Label57	--意外医疗、意外伤残、意外死亡、意外高残、意外大病、意外特种疾病、意外失业失能、意外生命末期重疾、意外豁免、疾病医疗、疾病伤残、疾病死亡、疾病高残、疾病大病、疾病特种疾病、疾病失---业失能、疾病生命末期重疾、疾病豁免	无	客户最后一次投办理理赔业务的业务类型
UPDATE shanglifeecif.Individual si1 SET si1.Label57 = (
    SELECT "最近一次理赔类型" || t.ACCIDENTTYPE FROM (
                                                 SELECT row_number()OVER(PARTITION BY c.insuredno ORDER BY c.applydate desc) rd,c.insuredno,c.ACCIDENTTYPE FROM dsj.INSURANCE_CLAIM c
                                             ) t WHERE t.rd = 1 AND t.insuredno = si1.scustid
);
--62	最近一次出险类型	Label58	疾病出险、意外出险	无	客户最后一次出险的类型
UPDATE shanglifeecif.Individual si1 SET si1.Label58 = (
    SELECT "最近一次出险类型" || t.RISKTYPE FROM (
                                             SELECT row_number()OVER(PARTITION BY c.insuredno ORDER BY c.applydate desc) rd,c.insuredno,c.RISKTYPE FROM dsj.INSURANCE_CLAIM c
                                         ) t WHERE t.rd = 1 AND t.insuredno = si1.scustid
);
--63	最近一次理赔状态	Label59	理赔报案中、理赔已受理、理赔已立案、理赔审核中、理赔预付审批中、理赔结案、理赔完成、理赔关闭	InsuranceClaimThread.ClaimCaseStatus	--客户最后一次发生理赔的理赔类型
UPDATE shanglifeecif.Individual si1 SET si1.Label59 = (
    SELECT '最近一次理赔状态' || t.LLCLAIMSTATE FROM (
                                                 SELECT row_number()OVER(PARTITION BY c.insuredno ORDER BY c.applydate desc) rd,c.insuredno,c.LLCLAIMSTATE FROM dsj.INSURANCE_CLAIM c
                                             ) t WHERE t.rd = 1 AND t.insuredno = si1.scustid
);
--65	是否有满期给付保单	Label61	有满期给付保单	如何判断？	0
	--select getdutycode from lmdutygetalive where getdutyname in ('满期保险金','满期生存保险金');
UPDATE shanglifeecif.Individual si1 SET si1.label61 = '有满期给付保单' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.contno IN (
        SELECT contno FROM dsj.lcget WHERE getdutycode IN (select getdutycode from dsj.lmdutygetalive where getdutyname in ('满期保险金','满期生存保险金'))
    )
);
--67	是否有生存金给付的保单	Label63	有生存金给付保单	无	0
	--select getdutycode from lmdutygetalive where getdutyname = '生存保险金';
UPDATE shanglifeecif.Individual si1 SET si1.label62 = '有生存金给付的保单' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.contno IN (
        SELECT contno FROM dsj.lcget WHERE getdutycode IN (select getdutycode from dsj.lmdutygetalive where getdutyname = '生存保险金')
    ) and p.appflag = '有效'
);
--68	是否有红利可领取的保单	Label64	有红利可领取保单	无	0
UPDATE shanglifeecif.Individual si1 SET si1.label64 = '红利可领取的保单' WHERE si1.scustid IN
                                                                       (SELECT p.customerno FROM dsj.policy_information p,dsj.lmriskapp l WHERE p.appflag = '有效' and p.riskcode = l.riskcode AND l.bonusflag = 1);

--69	一单寿险客户	Label65	一单寿险客户	客户仅购买了一张保单，且保单类型为寿险型保单
UPDATE shanglifeecif.Individual si1 SET si1.label65 = '一单寿险客户' WHERE si1.scustid IN (SELECT temp.customerno FROM (
                                                                                                                     SELECT pi.customerno,count(DISTINCT pi.contno) AS tnum,max(t.pnum) AS pnum FROM (
                                                                                                                                                                                                         SELECT count(DISTINCT p.contno) AS pnum,p.customerno FROM dsj.POLICY_INFORMATION p
                                                                                                                                                                                                                                                                       LEFT JOIN dsj.riskkind b on p.riskcode = b.riskcode
                                                                                                                                                                                                         WHERE (b.kindtype = '新型寿险' or b.kindtype = '人寿保险') GROUP BY p.customerno
                                                                                                                                                                                                     ) t LEFT JOIN dsj.POLICY_INFORMATION pi ON pi.customerno = t.customerno
                                                                                                                     GROUP BY pi.customerno
                                                                                                                 )temp WHERE temp.tnum = temp.pnum AND tnum = 1);
--70	缴费期满客户	Label66	缴费期满客户	POLICY_INFORMATION:paycount= payendyear	客户缴费期数已满
UPDATE shanglifeecif.Individual si1 SET si1.label66 = '缴费期满客户' WHERE si1.scustid IN (
    SELECT temp.customerno FROM (
                                    SELECT count(*) AS tnum,max(t.customerno) AS customerno,max(t.pnum) AS pnum FROM (
                                                                                                                         SELECT p.customerno,count(*) AS pnum FROM  dsj.policy_information p WHERE p.paycount = p.payendyear  GROUP BY p.customerno
                                                                                                                     ) t LEFT JOIN dsj.policy_information pi ON pi.customerno = t.customerno
                                    GROUP BY pi.customerno
                                ) temp WHERE temp.tnum = temp.pnum
);
--71	生日与司庆日同一天客户	Label67	生日与司庆日同天	2月15号 为司庆日	客户生日和公司司庆日为同一天
UPDATE shanglifeecif.Individual si1 SET si1.Label67 = '生日与司庆日同天' where si1.birthday like '%-02-15%';
--72	持有有效保单产品类型客户标签	Label68	持有有效保单，未持有有效保单		客户名下是否有投保人为自己的有效保单
UPDATE shanglifeecif.Individual si1 SET si1.label68 = '持有有效保单' WHERE si1.scustid IN (SELECT t.customerno FROM (
                                                                                                                  SELECT count(*) AS pnum,p.customerno FROM dsj.POLICY_INFORMATION p
                                                                                                                  WHERE p.appflag = '有效' GROUP BY p.customerno
                                                                                                              ) t WHERE t.pnum > 0);
--73	客户等级标签	Label69	钻石、白金、黄金、普通	Individual.CustClass	根据CustClass字段已有的等级值显示
	--UPDATE shanglifeecif.Individual si1 SET si1.Label69 = si1.CustClass where 1 = 1;
UPDATE shanglifeecif.Individual si1 SET si1.Label69 = (
    CASE si1.CustClass
        WHEN 1 THEN '钻石客户'
        WHEN 2 THEN '白金客户'
        WHEN 3 THEN '黄金客户'
            --WHEN 4 THEN '普通级别'
        --ELSE '无级别'
        ELSE ''
						END
    ) where 1 = 1;
--74	最近接触业务类型	Label70	最近投诉、最近问询（咨询）、最近营销、最近理赔、最近出险、最近保全	Individual.LCType	客户最后一次接触的业务类型

UPDATE shanglifeecif.Individual si1 SET si1.Label70 = (
    SELECT '最近'||t.scenario FROM (
                                     SELECT
                                         row_number()OVER(PARTITION BY pl.partyid ORDER BY pl.sdate desc) rn,
                                         pl.partyid,
                                         pl.scenario
                                     FROM shanglifeecif.partytimeline pl WHERE pl.partyid IS NOT NULL
                                 ) t WHERE t.rn = 1 AND si1.scustid = t.partyid
);


--75	最近接触方式	Label71	最近外呼、最近呼入、微信	Individual.LCMethod	客户最后一次接触的方式
UPDATE shanglifeecif.Individual si1 SET si1.Label71 = (
    SELECT '最近'||CASE t.scenario WHEN '回访' THEN '外呼' WHEN '咨询' THEN '呼入' END FROM (
                                                                                      SELECT
                                                                                          row_number()OVER(PARTITION BY pl.partyid ORDER BY pl.sdate desc) rn,
                                                                                          pl.partyid,
                                                                                          pl.scenario
                                                                                      FROM shanglifeecif.partytimeline pl WHERE pl.partyid IS NOT NULL AND (pl.scenario = '回访' OR pl.scenario = '咨询')
                                                                                  ) t WHERE t.rn = 1 AND si1.scustid = t.partyid
);


--77	是否关注官微	Label73	关注官微	无	0
	--UPDATE shanglifeecif.Individual si1 SET si1.Label73 = '关注官微'
    --WHERE si1.idcard IN (SELECT a.certificate_no FROM account a)
    --OR si1.passport IN (SELECT a.certificate_no FROM account a)
    --OR si1.Dlicense IN  (SELECT a.certificate_no FROM account a)
    --OR si1.othernumber IN (SELECT a.certificate_no FROM account a);

--78	是否注册官微	Label74	注册官微	无	0
UPDATE shanglifeecif.Individual si1 SET si1.Label74 = '注册官微'
WHERE si1.idcard IN (SELECT a.certificate_no FROM dsj.account a WHERE a.account_status = 0)
   OR si1.passport IN (SELECT a.certificate_no FROM dsj.account a WHERE a.account_status = 0)
   OR si1.Dlicense IN  (SELECT a.certificate_no FROM dsj.account a WHERE a.account_status = 0)
   OR si1.othernumber IN (SELECT a.certificate_no FROM dsj.account a WHERE a.account_status = 0);


UPDATE shanglifeecif.Individual si1 SET si1.label76 = '丈夫生日临近' WHERE si1.scustid IN (
    SELECT sis.indid1 FROM shanglifeecif.indrelationship sis LEFT JOIN shanglifeecif.individual si ON sis.indid2 = si.scustid
    WHERE sis.rstype = '配偶' AND DATEDIFF(to_char(si.birthday,"2022-MM-dd"),to_char(SYSDATE,"2022-MM-dd"))<=5 AND
            DATEDIFF(to_char(si.birthday,"2022-MM-dd"),to_char(SYSDATE,"2022-MM-dd"))>0 AND sis.role2 = '丈夫'
);
UPDATE shanglifeecif.Individual si1 SET si1.label76 = '妻子生日临近' WHERE si1.scustid IN (
    SELECT sis.indid1 FROM shanglifeecif.indrelationship sis LEFT JOIN shanglifeecif.individual si ON sis.indid2 = si.scustid
    WHERE sis.rstype = '配偶' AND DATEDIFF(to_char(si.birthday,"2022-MM-dd"),to_char(SYSDATE,"2022-MM-dd"))<=5 AND
            DATEDIFF(to_char(si.birthday,"2022-MM-dd"),to_char(SYSDATE,"2022-MM-dd"))>0 AND sis.role2 = '妻子'
);
--	本人生日提醒	Label77	本人生日临近		客户生日-当前日期<5
--UPDATE shanglifeecif.Individual si1 SET si1.label77 = '本人生日临近' WHERE DATEDIFF(to_char(SYSDATE,"2022-MM-dd"),to_char(si1.birthday,"2022-MM-dd"))<=5 AND
        --DATEDIFF(to_char(SYSDATE,"2022-MM-dd"),to_char(si1.birthday,"2022-MM-dd"))>0;
--UPDATE shanglifeecif.Individual si1 SET si1.label77 = '本人生日临近' WHERE DATEDIFF(to_char(si1.birthday,"2022-MM-dd"),to_char(SYSDATE,"2022-MM-dd"))<=5 AND  DATEDIFF(to_char(si1.birthday,"2022-MM-dd"),to_char(SYSDATE,"2022-MM-dd"))>0;
UPDATE shanglifeecif.Individual si1 SET si1.label77 = '本人生日临近' WHERE si1.scustid IN (
    SELECT scustid FROM (

                            SELECT *  FROM (
                                               SELECT scustid ,    birthday , month(current_date()    )   nowmonth    ,month(date_add(current_date(),5)) nextmonth ,month(to_date(birthday))  usermonth , day(current_date()    )   nowday    ,day(date_add(current_date(),5)) nextday
                                                    ,  day(to_date(birthday))   userday    from shanglifeecif.Individual
                                           )
                            WHERE
                                (nowmonth  != nextmonth AND
                                 (
                                         (nowmonth==  usermonth AND userday>  nowday   )
                                         or (nextmonth ==  usermonth AND  userday <= nextday  )
                                     )
                                    )
                               OR  (nowmonth  == nextmonth    AND  nowmonth == usermonth  AND   userday   >  nowday  AND  userday <=nextday )
                        )
);


UPDATE shanglifeecif.Individual si1 SET si1.label78 = '父亲生日临近' WHERE si1.scustid IN (
    SELECT sis.indid1 FROM shanglifeecif.indrelationship sis LEFT JOIN shanglifeecif.individual si ON sis.indid2 = si.scustid
    WHERE sis.rstype = '父母' AND DATEDIFF(to_char(si.birthday,"2022-MM-dd"),to_char(SYSDATE,"2022-MM-dd"))<=5 AND
            DATEDIFF(to_char(si.birthday,"2022-MM-dd"),to_char(SYSDATE,"2022-MM-dd"))>0 AND sis.role2 = '父亲'
);

UPDATE shanglifeecif.Individual si1 SET si1.label78 = '母亲生日临近' WHERE si1.scustid IN (
    SELECT sis.indid1 FROM shanglifeecif.indrelationship sis LEFT JOIN shanglifeecif.individual si ON sis.indid2 = si.scustid
    WHERE sis.rstype = '父母' AND DATEDIFF(to_char(si.birthday,"2022-MM-dd"),to_char(SYSDATE,"2022-MM-dd"))<=5 AND
            DATEDIFF(to_char(si.birthday,"2022-MM-dd"),to_char(SYSDATE,"2022-MM-dd"))>0 AND sis.role2 = '母亲'
);

UPDATE shanglifeecif.Individual si1 SET si1.label79 = '儿子生日临近' WHERE si1.scustid IN (
    SELECT sis.indid1 FROM shanglifeecif.indrelationship sis LEFT JOIN shanglifeecif.individual si ON sis.indid2 = si.scustid
    WHERE sis.rstype = '子女' AND DATEDIFF(to_char(si.birthday,"2022-MM-dd"),to_char(SYSDATE,"2022-MM-dd"))<=5 AND
            DATEDIFF(to_char(si.birthday,"2022-MM-dd"),to_char(SYSDATE,"2022-MM-dd"))>0 AND sis.role2 = '儿子'
);

UPDATE shanglifeecif.Individual si1 SET si1.label79 = '女儿生日临近' WHERE si1.scustid IN (
    SELECT sis.indid1 FROM shanglifeecif.indrelationship sis LEFT JOIN shanglifeecif.individual si ON sis.indid2 = si.scustid
    WHERE sis.rstype = '子女' AND DATEDIFF(to_char(si.birthday,"2022-MM-dd"),to_char(SYSDATE,"2022-MM-dd"))<=5 AND
            DATEDIFF(to_char(si.birthday,"2022-MM-dd"),to_char(SYSDATE,"2022-MM-dd"))>0 AND sis.role2 = '女儿'
);
--69	近期咨询过产品	label80	7天内呼入咨询过，呼叫中心服务记录、咨询转办单提取关键字段（包含产品咨询）或服务记录中包含保单基本信息、投保咨询	近期咨询产品

UPDATE shanglifeecif.Individual si1 SET si1.Label80 = '近期咨询过产品' WHERE si1.scustid IN (
    SELECT dp.customerno FROM dsj.policy_information dp WHERE dp.contno IN (
        SELECT rm.contnos FROM dsj.cc_record_main rm WHERE (rm.reasonsecondname LIKE '%保单基本信息%' OR rm.reasonsecondname LIKE '%投保咨询%')
                                                       and rm.contnos IS not NULL AND DATEDIFF(SYSDATE,rm.starttime) < 7 )
);

--70	近期咨询过理赔	label81	7天内呼入咨询过，呼叫中心服务记录、咨询转办单提取关键字段（包含理赔咨询）或服务记录、咨询转办单中包含理赔字段	近期咨询理赔
UPDATE shanglifeecif.Individual si1 SET si1.Label81 = '近期咨询过理赔' WHERE si1.scustid IN (
    SELECT dp.customerno FROM dsj.policy_information dp WHERE dp.contno IN (
        SELECT rm.contnos FROM dsj.cc_record_main rm WHERE rm.reasonsecondname LIKE '%理赔%'
                                                       and rm.contnos IS not NULL AND DATEDIFF(SYSDATE,rm.starttime) < 7)
);

--75	续期临期未缴费	label88	续期缴费期到宽限期未交费，续期缴费日期-当前日期<5	续期临期未缴费

UPDATE shanglifeecif.Individual si1 SET si1.Label88 = '续期临期未缴费' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag = '有效'AND p.payintv = '期缴' AND p.payendyear<>p.paycount  AND
            DATEDIFF(p.paytodate,to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss.S')) > 0 and DATEDIFF(p.paytodate,to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss.S')) < 5
);
--76	续期到期未缴费	label89	过了续期宽限期，仍未缴费	续期到期未缴费
UPDATE shanglifeecif.Individual si1 SET si1.Label89 = '续期到期未缴费' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag = '有效'AND p.payintv = '期缴' AND p.payendyear<>p.paycount  AND
            to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss.S') > DATE_ADD(p.paytodate, 60)
);


--93	失效客户	Label91	失效客户	所有保单都是失效状态	客户名下所有保单均失效的客户
UPDATE shanglifeecif.Individual si1 SET si1.Label91 = '失效客户' WHERE si1.scustid IN (SELECT temp.customerno FROM (
                                                                                                                   SELECT count(*) AS tnum,max(t.customerno) AS customerno,max(t.pnum) AS pnum FROM (
                                                                                                                                                                                                        SELECT p.customerno,count(*) AS pnum FROM  dsj.policy_information p WHERE p.appflag <> '有效' GROUP BY p.customerno
                                                                                                                                                                                                    ) t LEFT JOIN dsj.policy_information pi ON pi.customerno = t.customerno
                                                                                                                   GROUP BY pi.customerno
                                                                                                               ) temp WHERE temp.tnum = temp.pnum);

--失效原因
UPDATE shanglifeecif.Individual si1 SET si1.label92 = (
    SELECT '有'||t.terminatestate||'保单' FROM (
                                                SELECT row_number()over(partition by p.customerno order by p.polapplydate desc) rn,p.customerno,p.terminatestate FROM dsj.policy_information p
                                                WHERE  p.appflag = '中止'
                                            ) t WHERE t.rn = 1 AND si1.scustid = t.customerno
);
--	终止客户	label93	客户的所有保单缴费期满且承保期限已满正常终止的客户	终止客户
--UPDATE shanglifeecif.Individual si1 SET si1.Label93 = '终止客户' WHERE si1.scustid IN (SELECT temp.customerno FROM (
                                                                                                                     --SELECT count(*) AS tnum,max(t.customerno) AS customerno,max(t.pnum) AS pnum FROM (
	--SELECT p.customerno,count(*) AS pnum FROM  dsj.policy_information p WHERE p.appflag = '终止' AND p.enddate < to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss.S') GROUP BY p.customerno
	--) t LEFT JOIN dsj.policy_information pi ON pi.customerno = t.customerno
	--GROUP BY pi.customerno
	--) temp WHERE temp.tnum = temp.pnum);
--终止原因
UPDATE shanglifeecif.Individual si1 SET si1.Label94 = (
    SELECT '有'||t.terminatestate||'保单' FROM (
                                                SELECT row_number()over(partition by p.customerno order by p.polapplydate desc) rn,p.customerno,p.terminatestate FROM dsj.policy_information p
                                                WHERE p.appflag = '终止'
                                            ) t WHERE t.rn = 1 AND si1.scustid = t.customerno
);
--81	上年续期交费情况	label95	相对于上一年的交费日期和实收日期，提前X天交费、滞后X天交费、宽限期外交费、失效客户	提前X天交费、滞后X天交费、宽限期外交费
	--UPDATE shanglifeecif.Individual si1 SET si1.Label95 = (
    --SELECT
        --CASE

        --	WHEN temp.dnum<0 THEN '上一年提前'||abs(temp.dnum)||'天交费'
        --	WHEN temp.dnum>0 THEN '上一年滞后'||temp.dnum||'天交费'
        --	WHEN temp.toconfdate > temp.kxdate THEN '上一年宽限期外交费'
        --END
        --	FROM (
        --		SELECT row_number()over(PARTITION BY t.appntno ORDER BY t.confdate DESC) rn,t.appntno,t.paydate as topaydate,t.confdate as toconfdate,DATE_ADD(t.paydate,60) as --kxdate,tt.paydate as lastpaydate,tt.confdate AS lastconfdate,DATEDIFF(to_char(t.confdate,'0000-MM-dd'),to_char(tt.confdate,'0000-MM-dd')) AS dnum FROM (
        --		SELECT row_number()OVER(PARTITION BY ln.contno ORDER BY ln.paydate DESC) rd,ln.* FROM dsj.ljapayperson ln
        --		WHERE ln.confdate IS NOT NULL AND ln.paydate IS NOT null
        --	) t LEFT JOIN (
        --		SELECT row_number()OVER(PARTITION BY ln2.contno ORDER BY ln2.paydate DESC) rd,ln2.* FROM dsj.ljapayperson ln2
        --		WHERE ln2.confdate IS NOT NULL AND ln2.paydate IS NOT null AND ln2.paycount > 1
        --	) tt ON t.contno = tt.contno AND t.rd = tt.rd - 1 WHERE t.rd = 1 AND tt.rd = 2
        --	) temp WHERE temp.dnum <> 0 AND temp.rn = 1 AND si1.scustid = temp.appntno
        --) WHERE 1 = 1;
--UPDATE shanglifeecif.Individual si1 SET si1.Label95 = (
    --	SELECT
    --		CASE
    --		WHEN t.dnum<0 THEN '提前'||abs(t.dnum)||'天交费'
    --		WHEN t.dnum>0 AND t.dnum<=60 THEN '滞后'||t.dnum||'天交费'
    --		WHEN t.dnum>60 THEN '宽限期外交费'
    --		END
    --	FROM (
    --		SELECT row_number()over(PARTITION BY p.customerno ORDER BY p.confdate DESC) rn,p.customerno,DATEDIFF(to_char(p.confdate,'0000-MM-dd'),to_char(p.polapplydate,'0000-MM-dd')) AS dnum FROM dsj.policy_information p
    --		WHERE p.appflag = '有效' AND p.payintv = '期缴'
    --	) t WHERE t.dnum <> 0 AND t.rn = 1 AND si1.scustid = t.customerno
    --) WHERE 1 = 1;

UPDATE shanglifeecif.Individual si1 SET si1.Label95 = (
    SELECT
        CASE
            WHEN t.dnum=0 THEN '上一年如期缴费'
            WHEN t.dnum<0 THEN '上一年提前'||abs(t.dnum)||'天交费'
            WHEN t.dnum>0 AND t.dnum<=60 THEN '上一年滞后'||t.dnum||'天交费'
            WHEN t.dnum>60 THEN '上一年宽限期外交费'
            END
    FROM (
             SELECT
                 row_number()over(PARTITION BY l.appntno ORDER BY l.confdate DESC) rn,l.appntno,DATEDIFF(to_char(l.confdate,'0000-MM-dd'),to_char(l.curpaytodate,'0000-MM-dd')) AS dnum
             FROM dsj.ljapayperson l WHERE l.paycount > 1 AND YEAR(l.confdate) = YEAR(sysdate) - 1
         ) t WHERE  t.rn = 1 AND t.appntno = si1.scustid
) WHERE 1 = 1;


--98	大龄	Label96	大龄 来电客户年龄50岁以上
UPDATE shanglifeecif.Individual si1 SET si1.Label96 = '大龄'  where si1.scustid IN (

    SELECT p.customerno FROM dsj.policy_information p WHERE p.contno IN (
        SELECT contnos from dsj.cc_record_main
    )
) AND TRUNC(months_between(sysdate, si1.birthday)/12)>=50 AND TRUNC(months_between(sysdate, si1.birthday)/12)<60;
--99	高龄	Label97	高龄		来电客户年龄60岁以上
UPDATE shanglifeecif.Individual si1 SET si1.Label97 = '高龄' where si1.scustid IN (

    SELECT p.customerno FROM dsj.policy_information p WHERE p.contno IN (
        SELECT contnos from dsj.cc_record_main
    )
) AND TRUNC(months_between(sysdate, si1.birthday)/12)>=60;
--100	敏感职业	Label100	敏感职业		投保人职业为记者、律师、公务员、媒体、金融行业
UPDATE shanglifeecif.Individual si1 SET si1.Label100 = (CASE WHEN si1.occupation like '%记者%' OR si1.occupation like '%律师%' OR si1.occupation like '%公务员%' OR  si1.occupation like '%媒体%' OR  si1.occupation like '%金融行业%' THEN '敏感职业' END ) WHERE 1 = 1;
--102	公司销售人员	Label100	公司销售人员	姓名与保单经办人一致	客户为公司销售人员
	--103	长通话	Label101	长通话	"contnos为客户的保单号 select (select lo.call_length from ucc_rms_recorderlog lo where lo.call_id=m.call_id) as lenth from cc_record_main m
	--where m.contnos='2019082000110188'"	平均通话时长超过10分钟
UPDATE shanglifeecif.Individual si1 SET si1.Label101 = (
    SELECT '长通话' FROM (
                          SELECT
                              t.contnos,
                              row_number()OVER(PARTITION BY p.customerno) rn,
                              p.customerno
                          FROM (
                                   SELECT (sum(lo.call_length)/count(*)) AS avgcalltime,m.contnos from dsj.cc_record_main m
                                                                                                           LEFT JOIN  dsj.ucc_rms_recorderlog lo ON m.call_id = lo.call_id
                                   WHERE m.contnos IS NOT NULL AND lo.call_length IS NOT NULL
                                   GROUP BY m.contnos
                               ) t  LEFT JOIN dsj.policy_information p ON t.contnos = p.contno

                          WHERE t.avgcalltime>600

                      ) temp WHERE temp.customerno IS NOT NULL AND temp.rn = 1 AND si1.scustid = temp.customerno
);

--105	要求较多	Label103	要求较多	"policyno为客户的保单号 flowid like'ZX%'转办标志 Z select (select r.idname from cc_swf_sort r where r.id=f.complaintProject) as --swfName --from cc_swfflowmain f where f.policyno='2020021500000456' and flowid like'ZX%'"	每次来电均下转办单的
UPDATE shanglifeecif.Individual si1 SET si1.Label103 = '要求较多' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.contno IN (
        SELECT temp.policyno FROM (
                                      SELECT t.policyno,max(t.mnum) mnum,max(t.fnum) fnum FROM (
                                                                                                   SELECT m.contnos policyno,count(*) AS mnum,'' AS fnum FROM dsj.cc_record_main m WHERE m.contnos IS NOT NULL GROUP BY m.contnos
                                                                                                   UNION
                                                                                                   SELECT f.policyno,'' AS mnum,count(*) AS fnum FROM  dsj.cc_swfflowmain f WHERE f.modelno = 1 GROUP BY f.policyno
                                                                                               )t GROUP BY t.policyno
                                  ) temp WHERE (temp.fnum/temp.mnum)>0.7
    )
);
--106	易投诉升级	Label104	易投诉升级	"flowid like'ZX%'转办标志 Z select (select r.idname from cc_swf_sort r where r.id=f.complaintProject and r.idname='投诉') as swfName --from cc_swfflowmain f where f.policyno='2020021500000456' and flowid like'ZX%'"	咨询转办单项目为投诉
UPDATE shanglifeecif.Individual si1 SET si1.Label104 = '易投诉升级' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.contno IN (
        SELECT temp.policyno FROM (
                                      SELECT t.policyno,max(t.mnum) mnum,max(t.fnum) fnum FROM (
                                                                                                   SELECT m.contnos policyno,count(*) AS mnum,'' AS fnum FROM dsj.cc_record_main m WHERE m.contnos IS NOT NULL GROUP BY m.contnos
                                                                                                   UNION
                                                                                                   SELECT f.policyno,'' AS mnum,count(*) AS fnum FROM  dsj.cc_swfflowmain f,dsj.cc_swf_sort r WHERE r.id=f.complaintProject and f.modelno = 1 AND r.idname='投诉' GROUP BY f.policyno
                                                                                               )t GROUP BY t.policyno
                                  ) temp WHERE temp.mnum = temp.fnum
    )
);
--107	高频投诉	Label105	高频投诉	"select count(1) from cc_action_data_complaints c where c.productno='2018110400035608'"	--半年之内来过两次以上投诉的，投诉受理日期间隔<180天，且投诉次数>2
	--UPDATE shanglifeecif.Individual si1 SET si1.Label105 = '高频投诉' WHERE si1.scustid in
	--(SELECT p.customerno FROM dsj.policy_information p WHERE p.contno IN
	--(SELECT temp.productno FROM (

		--SELECT t.productno,t.sinserttime,
		--(SELECT tt.inserttime FROM (SELECT cad.inserttime,row_number()OVER(PARTITION BY cad.productno ORDER BY cad.inserttime) rownum,cad.productno FROM dsj.cc_action_complaints --ca,dsj.cc_action_data_complaints cad
			--WHERE  cad.complaintsid=ca.complaintsid AND cad.productno IS NOT NULL) tt WHERE tt.productno = t.productno AND tt.rownum = t.rn+1) AS esinserttime
			-- FROM
			--(
			--	select cd.productno,row_number()OVER(PARTITION BY cd.productno ORDER BY cd.inserttime) rn,cd.inserttime AS sinserttime
			--	from dsj.cc_action_complaints c,dsj.cc_action_data_complaints cd WHERE  cd.complaintsid=c.complaintsid AND cd.productno IS NOT NULL
			--) t
	--) temp WHERE temp.esinserttime IS NOT NULL AND DATEDIFF(temp.esinserttime,temp.sinserttime) < 180));
UPDATE shanglifeecif.Individual si1 SET si1.Label105 = '高频投诉' WHERE si1.scustid in (
    SELECT t.customerno FROM (
                                 SELECT
                                     row_number()OVER(PARTITION BY t1.customerno ORDER BY ca.inserttime) rownum,
                                     t1.customerno,
                                     t1.contno,
                                     ca.inserttime FROM (
                                                            SELECT
                                                                max(p.customerno) customerno,
                                                                p.contno
                                                            FROM dsj.policy_information p GROUP BY p.contno
                                                        ) t1 LEFT JOIN dsj.cc_action_complaints ca ON t1.contno = ca.productnos

                             ) t LEFT JOIN (
        SELECT
            row_number()OVER(PARTITION BY t1.customerno ORDER BY ca.inserttime) rownum,
            t1.customerno,
            t1.contno,
            ca.inserttime FROM (
                                   SELECT
                                       max(p.customerno) customerno,
                                       p.contno
                                   FROM dsj.policy_information p GROUP BY p.contno
                               ) t1 LEFT JOIN dsj.cc_action_complaints ca ON t1.contno = ca.productnos
    ) tt ON t.customerno = tt.customerno AND t.rownum = tt.rownum+1
    WHERE  DATEDIFF(tt.inserttime,t.inserttime) < 180
);

--SELECT c.productno,c.inserttime FROM cc_action_data_complaints c  WHERE c.productno IS NOT NULL GROUP BY c.productno
                                                                                                               --108	重大投诉影响	Label106	重大投诉影响	投诉来源	投诉来源是保监局、保监会、媒体转办的
UPDATE shanglifeecif.Individual si1 SET si1.Label106 = (

    SELECT
        CASE
            WHEN tt.complaintsSource LIKE '%保监会%' THEN '重大投诉'
            WHEN tt.complaintsSource LIKE '%保监局%' THEN '重大投诉'
            WHEN tt.complaintsSource LIKE '%媒体转办%' THEN '重大投诉' END FROM (

                                                                            SELECT pi.customerno,max(complaintsSource) AS complaintsSource FROM (

                                                                                                                                                    select group_concat((case c.complaintsSource
                                                                                                                                                                             when '911' then '来电'
                                                                                                                                                                             when '912' then '来访'
                                                                                                                                                                             when '913' then '来函'
                                                                                                                                                                             when '914' then '保监会转办'
                                                                                                                                                                             when '919' then '保监局转办'
                                                                                                                                                                             when '915' then '媒体转办'
                                                                                                                                                                             when '916' then '同业公会'
                                                                                                                                                                             when '917' then '其他转办'
                                                                                                                                                                             when '918' then '呼出'
                                                                                                                                                                             else c.complaintsSource END),',') as complaintsSource,p.contno from dsj.cc_action_complaints c,dsj.cc_action_data_complaints cd,dsj.policy_information p

                                                                                                                                                    where cd.complaintsid=c.complaintsid AND p.contno = cd.productno GROUP BY p.contno

                                                                                                                                                ) t LEFT JOIN dsj.policy_information pi ON t.contno = pi.contno GROUP BY pi.customerno

                                                                        ) tt WHERE tt.customerno = si1.scustid

);

--109	高金额	Label107	高金额		关于高金额的指标，鉴于大数据无法计算件均保费，调整逻辑如下：
		--根据业绩归属渠道、缴费方式、以及期缴保费划分如下
		--02个险渠道            期缴 大于20000以上
		--03银保渠道            期缴 大于50000以上
		--06健康险               期缴 大于 20000以上
UPDATE shanglifeecif.Individual si1 SET si1.Label107 = '高金额'
WHERE si1.scustid IN (SELECT p.customerno FROM dsj.policy_information p WHERE
        p.appflag = '有效' AND p.payintv = '期缴' and
    ((p.salecom = '03' AND p.prem>50000) OR (p.salecom = '02' AND p.prem>20000) OR (p.salecom = '06' AND p.prem>20000)));

--最近理赔结案	label108	最近15天做过理赔，当前日期-理赔结案日期<15天	最近理赔结案
UPDATE shanglifeecif.Individual si1 SET si1.Label108 = '最近理赔结案' WHERE si1.scustid IN (
    SELECT c.insuredno FROM dsj.INSURANCE_CLAIM c WHERE DATEDIFF(to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss.S'),c.endcasedate) < 15
);
--111	理赔报案追踪	Label109	理赔报案追踪		理赔状态为报案状态，且理赔报案日-当前日期<30天
UPDATE shanglifeecif.Individual si1 SET si1.Label109 = '理赔报案追踪' WHERE si1.scustid IN (SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag = '有效' and p.contno IN (SELECT c.CONTNO FROM dsj.insurance_claim c WHERE c.LLCLAIMSTATE = '报案' AND DATEDIFF(sysdate(), c.RPTDATE) <30) );
--112	理赔处理中	Label110	理赔处理中		理赔状态为受理或立案
UPDATE shanglifeecif.Individual si1 SET si1.label110 = '理赔处理中' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag = '有效' and p.contno IN (SELECT c.contno FROM dsj.INSURANCE_CLAIM c WHERE c.llclaimstate = '受理' OR c.llclaimstate = '立案')
);

--最近保全完成	label111	最近15天做过保全，来电日期-保全申请日期<15天	最近保全完成
UPDATE shanglifeecif.Individual si1 SET si1.Label111 = '最近保全完成' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.contno IN (
        SELECT l.contno FROM dsj.lpedoritem l WHERE DATEDIFF(to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss.S'),l.edorappdate) < 15 AND l.edorstate = '0'
    )
);
--115	生存金未领	Label113	生存金未领	判断逻辑不详	生存金领取方式为自动转账、存在生存金且未领取
UPDATE shanglifeecif.Individual si1 SET si1.label113 = '生存金未领' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag = '有效' and p.contno IN (
        SELECT contno FROM dsj.Lcinsureacc WHERE acctype = '005' and   insuaccbala>0
    )
);
--117	红利垫缴还款	Label115	红利垫缴还款	判断逻辑不详	存在红利，且红利已经垫交保单还款
UPDATE shanglifeecif.Individual si1 SET si1.label115 = '红利垫缴还款' WHERE si1.scustid IN (
    SELECT p.customerno FROM dsj.policy_information p WHERE p.appflag = '有效' and p.contno IN (
        SELECT a.contno FROM dsj.LOLOANDETAIL a,dsj.LDCode1 b WHERE
                b.codetype='lnmoneytype' AND b.code=a.finfeetype and b.code1=a.moneytype
                                                                AND ((a.finfeetype = 'HL' AND a.moneytype = 'SX') OR (a.finfeetype = 'SC' AND a.moneytype = 'HK'))
    )
);
--	宽限期	label117	宽限日末日-当前日期<3天	宽限期
UPDATE shanglifeecif.Individual si1 SET si1.label117 = '宽限期' WHERE si1.scustid IN (

    SELECT p.customerno FROM dsj.policy_information p WHERE p.payintv= '期缴' AND p.appflag='有效' AND p.payendyear!=p.paycount and
            DATEDIFF(DATE_ADD(p.paytodate, 60),to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss.S')) > 0 AND DATEDIFF(DATE_ADD(p.paytodate, 60),to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss.S')) < 3
);

--122	重点银行	Label120	重点银行	判断逻辑不详	保单的销售渠道网点为招商银行
UPDATE shanglifeecif.Individual si1 SET si1.Label120 = '重点银行' WHERE
        si1.scustid IN (SELECT p.customerno  FROM dsj.policy_information p WHERE p.AGENTCOM LIKE '%招商银行%');

dbms_output.put_line('updateindividuallable函数跑批完成！');
EXCEPTION
			WHEN HIVE_EXCEPTION THEN
INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());
WHEN Others THEN
INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());

END;