ShangHaiLifeECIF/sql-achievement/存储过程/测试环境/存储过程.sql

CREATE OR REPLACE PROCEDURE shanglifeecif.age_sex_distribution() IS
DECLARE
BEGIN
 	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);
	 DELETE FROM shanglifeecif.age_sex_distribution;
	insert into shanglifeecif.age_sex_distribution (
		id,
		labelName,
		gender,
		cusNum
	) SELECT row_number()over(),t.labelName,t.gender,t.cusNum FROM (
	SELECT si.label4 labelName,si.gender,count(1) cusNum FROM shanglifeecif.individual si WHERE si.label4 IS NOT null GROUP BY si.label4,si.gender
	) t;
	dbms_output.put_line('age_sex_distribution函数跑批完成！');
		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.bdnum_distribution() IS
DECLARE
BEGIN
	--第一步 更新 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 si1.custtype like '%投保人%';

	/*
	 * 如果某个区间没数据，则bdnum_distribution表中缺少该区间记录，而不是这个区间数是0
	DELETE FROM shanglifeecif.bdnum_distribution;
	insert into shanglifeecif.bdnum_distribution (
		id,
		labelName,
		bdnum
	) SELECT row_number()over(),t.labelName,t.bdnum FROM (
		SELECT si.label25 labelName,count(1) bdnum FROM shanglifeecif.individual si WHERE si.label25 IS NOT null GROUP BY si.label25
	) t;
	*/

	DELETE FROM shanglifeecif.bdnum_distribution;
	-- insert时id要保持有序且不重复，重复的话ES会自动驱去重，有序是因为前端需要按照这个顺序排序展示 java程序可以根据id排序
	-- 注意：id是按照数字类型排序，所以id必须是整形数字，不能是字母、符号、小数点
	insert into shanglifeecif.bdnum_distribution (id, labelName, bdnum) VALUES
	('1', '无保单', '0'),
	('2', '1件', '0'),
	('3', '2件', '0'),
	('4', '3-5件', '0'),
	('5', '6-10件', '0'),
	('6', '11-20件', '0'),
	('7', '21-50件', '0'),
	('8', '50件以上', '0');

	UPDATE shanglifeecif.bdnum_distribution b SET bdnum = (
		SELECT t.bdnum FROM (
			SELECT
				CASE
					-- individual。label25对应标签信息，标签信息中要展示为'1件保单'，'2件保单'。而bdnum_distribution对应首页拥有保单件数分布，需要展示为1件，2件
					WHEN trim(si.label25) = '1件保单' THEN '1件'
					WHEN trim(si.label25) = '2件保单' THEN '2件'
					WHEN trim(si.label25) = '3-5件保单' THEN '3-5件'
					WHEN trim(si.label25) = '6-10件保单' THEN '6-10件'
					WHEN trim(si.label25) = '11-20件保单' THEN '11-20件'
					WHEN trim(si.label25) = '21-50件保单' THEN '21-50件'
					WHEN trim(si.label25) = '50件保单以上' THEN '50件以上'
					ELSE trim(si.label25)
				END AS labelName
				, count(1) bdnum
			FROM shanglifeecif.individual si
			WHERE si.label25 IS NOT NULL GROUP BY si.label25
		) t
		WHERE b.labelName = t.labelName
	);


	dbms_output.put_line('bdnum_distribution函数跑批完成！');
	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.bdnum_distribution_channel() IS
DECLARE
BEGIN
	--第一步 更新 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 si1.custtype like '%投保人%';
	DELETE FROM shanglifeecif.bdnum_distribution_channel;
	insert into shanglifeecif.bdnum_distribution_channel (
		id,
		labelName,
		bdnum
	) SELECT row_number()over(),t.labelName,t.bdnum FROM (
		SELECT si.label25 labelName,count(1) bdnum FROM shanglifeecif.individual si WHERE si.label25 IS NOT null GROUP BY si.label25
	) t;

	dbms_output.put_line('bdnum_distribution_channel函数跑批完成！');
	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.customer_risk_temp()
IS
DECLARE
BEGIN
	  DELETE FROM shanglifeecif.customer_risk_temp;
      INSERT INTO shanglifeecif.customer_risk_temp(
	id,
	  scustid,
	  name,
	  gender,
	  idcard,
	  birthday,
	  policyno,
	  productname,
	  riskcategoriesname,
	  risk,
	  salecom,
	  salecomname
	)
	SELECT
		row_number()over(),
		si.scustid,
		si.name,
		CASE si.gender
			WHEN '0' THEN '男'
			WHEN '1' THEN '女'
			END AS gender,
			si.idcard,
			to_char(si.birthday,"yyyy-MM-dd") birthday,
			sit.policyno,
			sit.productname,
			sit.risk_categories_name,
			sit.Risk,
			sit.policybelong,
			sit.salecomname
	FROM shanglifeecif.individual si
	LEFT JOIN shanglifeecif.insurancearrangement sit ON si.scustid = sit.insuredscustid;
	dbms_output.put_line('customer_risk_temp函数跑批完成！');
		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.customertotaltemp() IS
DECLARE
BEGIN
		 DELETE FROM shanglifeecif.customertotaltemp;
		insert into shanglifeecif.customertotaltemp(
			scustid,
		 	name,
		 	gender,
		 	birthday,
		 	idcard,
		 	idtype
		)
		SELECT
			scustid ,
			max(name) as name,
			max(gender) as gender ,
			to_char(max(birthday),"yyyy-MM-dd")  birthday,
			max(idcard) as idcard,
			max(idtype) AS idtype
		FROM (
			SELECT
				trim(customerno) AS scustid,--投保人
				trim(name) AS name,
				trim(sex) AS gender,
				trim(birthday) AS  birthday,
				trim(idtype) AS idtype ,
				trim(idno) AS idcard
			FROM
				dsj.policy_information
			WHERE customerno IS NOT NULL
			UNION
			SELECT
				trim(insuredno) AS scustid,--被保人
				trim(insuredname) AS name,
				trim(insuredsex) AS gender,
				trim(insuredbirthday) AS  birthday,
				trim(insuredidtype) AS idtype ,
				trim(insuredidno) AS idcard
			FROM
				dsj.policy_information
			WHERE insuredno IS NOT NULL
			) tmpTable GROUP BY scustid;

		UPDATE shanglifeecif.customertotaltemp sct SET sct.mobile = (SELECT trim(t.MOBILE) FROM dsj.t_customer_class t WHERE sct.scustid=t.customer_id);
	dbms_output.put_line('customertotaltemp函数跑批完成！');
	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.data_cleaning() -- 创建主存储过程
IS
DECLARE
BEGIN


	--姓名     sametype:3
	  INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		sameType
)
SELECT
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	3
FROM dsj.t_customer_class t WHERE t.customer_name RLIKE '^.*[!$^|\=`]{1,}.*$' OR t.customer_name RLIKE '^.*[0-9]{1,}.*$' ;

	--性别     sametype:4
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		sameType
)
SELECT
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	4
FROM dsj.t_customer_class t WHERE t.gender IS NULL AND t.customer_id IN (SELECT p.customerno FROM dsj.policy_information p WHERE p.sex IS null );
	--证件号码     sametype:6
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		sameType
)
SELECT
	reflect("java.util.UUID", "randomUUID"),
	si.scustid,
	si.name,
	si.gender,
	si.birthday,
	si.idcard,
	6
FROM shanglifeecif.individual si WHERE length(si.idcard)<18 AND si.custtype LIKE '%投保人%' AND si.idcard IS NOT null;

	--出生日期     sametype:7
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		sameType
)
SELECT
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	7
FROM dsj.t_customer_class t WHERE t.birthdate IS NULL AND t.customer_id IN (SELECT p.customerno FROM dsj.policy_information p WHERE p.birthday IS null UNION SELECT i.insuredno FROM dsj.policy_information i WHERE i.insuredbirthday IS null);

	--手机号     sametype:8
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		mobile,
  		sameType
)
SELECT
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	t.mobile,
	8
FROM dsj.t_customer_class t WHERE t.mobile NOT RLIKE '^1[0-9]{10}$' AND t.customer_id IN (SELECT p.customerno FROM dsj.policy_information p);

	--邮编     sametype:9
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		zipcode,
  		sameType
)
SELECT
	reflect("java.util.UUID", "randomUUID"),
	si.scustid,
	si.name,
	si.gender,
	si.birthday,
	si.idcard,
	si.zipcode,
	9
FROM shanglifeecif.individual si WHERE (length(si.zipcode)>0 and length(si.zipcode)<6) OR length(si.zipcode)>6;
	--主被保险人性别异常数据     sametype:11
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		sameType
)
SELECT
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	11
FROM dsj.t_customer_class t  WHERE (t.gender IS NULL OR t.gender = '2') AND t.customer_id  IN (SELECT p.insuredno FROM dsj.policy_information p WHERE p.insuredsex IS null);

--邮箱     sametype:14
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		email,
  		sameType
)
SELECT
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	t.email,
	14
FROM dsj.t_customer_class t  WHERE t.email not RLIKE '^([a-zA-Z0-9._%-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4})*$' AND t.customer_id IN (SELECT p.customerno FROM dsj.policy_information p UNION SELECT pi.insuredno FROM dsj.policy_information pi);

--主被保险人手机异常数据     sametype:16
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		mobile,
  		sameType
)
SELECT
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	t.mobile,
	16
FROM dsj.t_customer_class t  WHERE t.mobile NOT RLIKE '^1[0-9]{10}$' AND t.customer_id IN (SELECT p.insuredno FROM dsj.policy_information p);
--主被保险人证件号码     sametype:17
INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		sameType
)
SELECT
	reflect("java.util.UUID", "randomUUID"),
	si.scustid,
	si.name,
	si.gender,
	si.birthday,
	si.idcard,
	17
FROM shanglifeecif.individual si WHERE length(si.idcard)<18 AND si.custtype LIKE '%被保人%' AND si.idcard IS NOT null;

	--业务员没错     sametype:18
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		agentname,
  		sameType
)
SELECT
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	p.agentname,
	18
FROM dsj.t_customer_class t,dsj.policy_information p WHERE t.customer_id = p.customerno and p.agentname not RLIKE '^[\u4e00-\u9fa5]|[^\x00-\xff]$';
--业绩归属     sametype:19
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		OtherIdNumber,
  		salecomname,
  		sameType
)
SELECT
	reflect("java.util.UUID", "randomUUID"),
	t.customer_id,
	t.customer_name,
	t.gender,
	to_char(t.birthdate,"yyyy-MM-dd") birthdate,
	CASE t.id_type WHEN '0' THEN t.id_no END AS idcard,
	CASE  WHEN t.id_type <> '0' THEN t.id_no END AS otherIdnumber,
	p.salecomname,
	19
FROM dsj.t_customer_class t,dsj.policy_information p WHERE t.customer_id = p.customerno and  p.salecomname not RLIKE '^[\u4e00-\u9fa5]|[^\x00-\xff]$';
dbms_output.put_line('data_cleaning函数跑批完成！');
END;
CREATE OR REPLACE PROCEDURE shanglifeecif.effective_customer() -- 创建主存储过程
IS
DECLARE
	scount int
BEGIN
	 DELETE FROM shanglifeecif.effectivecustomer WHERE fadateY = to_char(SYSDATE,"yyyy");
	 INSERT INTO shanglifeecif.effectivecustomer(
	  		esid,
	  		fadateY,
	  		custclass,
	  		cnum
	)
	SELECT
		to_char(SYSDATE,"yyyy")||si.custclass,
		to_char(SYSDATE,"yyyy"),
	   si.custclass,
	   count(*) AS cnum
   FROM
		shanglifeecif.individual si  WHERE si.Label91 IS NULL AND si.custclass IS NOT NULL AND si.custclass <> '0' GROUP BY si.custclass;
	dbms_output.put_line('effective_customer函数跑批完成！');
	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_all_individual() -- 初始化所有客户信息
IS
BEGIN

	--查询出此次处理的数据并出表中
	 insert into shanglifeecif.individual (
		indid,
		custid,
		scustid,
	 	name,
	 	gender,
	 	birthday,
	 	idcard,
	 	Passport,
  		Dlicense,
  		OtherIdNumber,
	 	created_time,
	 	created_by
	) SELECT
	row_number()OVER(ORDER BY sc.scustid),
	'CP'||lpad(row_number()over(ORDER BY sc.scustid),10,'0'),
	sc.scustid,
	sc.name,
	sc.gender,
	sc.birthday,
	CASE sc.idtype WHEN '0' THEN sc.idcard END AS idcard,
	CASE sc.idtype WHEN '1' THEN sc.idcard END AS passport,
	CASE sc.idtype WHEN '3' THEN sc.idcard END AS dlicense,
	CASE  WHEN sc.idtype <> '0' AND sc.idtype <> '1' AND sc.idtype <> '3' THEN sc.idcard END AS otherIdnumber,
	sysdate,
	'admin'
FROM shanglifeecif.customertotaltemp sc ORDER BY sc.scustid;

		dbms_output.put_line('init_all_individual函数跑批完成！');
		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_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.init_indrelationship_main2( )
IS
DECLARE
	s_count int
BEGIN


	-- 完善被保人关系
/**	*/

	insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		Role2
	)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
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.init_insurancearrangement_main() -- 创建主存储过程
IS
BEGIN

	 DELETE FROM shanglifeecif.insurancearrangement;
	--根据policy_information 更新保单表
	shanglifeecif.init_insurancearrangement_policy_information();

	--其他标的字段 更新
	shanglifeecif.update_risk_categories();


END;
CREATE OR REPLACE PROCEDURE shanglifeecif.init_insurancearrangement_policy_information() -- 创建主存储过程
IS
BEGIN

    INSERT INTO shanglifeecif.insurancearrangement(
  iaid ,--'保单ID',
  policyno ,-- '保险单号 INSURANCEINFO.CONTNO',
  pindate ,-- '起保日期 POLICY_INFORMATION.CVALIDATE',
  pmdate ,-- '终保日期 POLICY_INFORMATION.ENDDATE',
  norenewal ,-- '续保次数 POLICY_INFORMATION.PAYCOUNT',
  payment ,-- '缴费方式 POLICY_INFORMATION.PAYINTV',
  applicantscustid ,-- '投保人 上游客户号，再找到individualid',
  appname ,-- '投保人名称 POLICY_INFORMATION.NAME',
  appcertid ,-- '投保人证件号码 POLICY_INFORMATION.IDNO',
  insuredscustid ,-- '主被保险人 游客户号',
  insname ,-- '主被保险人名称 POLICY_INFORMATION.INSUREDNAME',
  inscertid ,-- '主被保险人证件号码 POLICY_INFORMATION.INSUREDIDNO',
  productid ,-- '险种代码 POLICY_INFORMATION.RISKCODE',
  productname,--险种名称
  policybelong ,-- '业绩归属 POLICY_INFORMATION.SALECOM',
  payendyear ,-- '缴费年期 POLICY_INFORMATION.PAYENDYEAR',
  policystate,--保单状态
  prem,--保费
  sumprem, --累计保费，
  Risk,--保额
  NPDate,--下次缴费日期
  PADate,--投保日期
  pisdate,--签单日期
  AgentOrg,--代理机构
  schannel,--销售渠道
  schannelname,--销售渠道中文
  salecomname,--业绩归属中文
  POService,--保全标志
  PWComp,--承保分公司
  security ,--保险期原始值
  agrmntage ,--保险期限
  salesperson,--业务员标识代码
  SPName,--业务员名称
  renewalDate,--续保日期
  created_by ,-- '创建人',
  created_time -- '创建时间',

)
SELECT
	row_number()over(),
	trim(CONTNO) as CONTNO,--policyno ,-- '保险单号 INSURANCEINFO.CONTNO',
	trim(CVALIDATE) as CVALIDATE,--pindate ,-- '起保日期 POLICY_INFORMATION.CVALIDATE',
	trim(ENDDATE) as ENDDATE,--pmdate ,-- '终保日期 POLICY_INFORMATION.ENDDATE',
	PAYCOUNT,--norenewal ,-- '续保次数 POLICY_INFORMATION.PAYCOUNT',
	trim(PAYINTV) as PAYINTV,--payment ,-- '缴费方式 POLICY_INFORMATION.PAYINTV',
	trim(CUSTOMERNO) as CUSTOMERNO,--applicantid ,-- '投保人 根据POLICY_INFORMATION.CUSTOMERNO关联individual上游客户号，再找到individualid',
	trim(NAME) as NAME,--appname ,-- '投保人名称 POLICY_INFORMATION.NAME',
	trim(IDNO) as IDNO,--appcertid ,-- '投保人证件号码 POLICY_INFORMATION.IDNO',
	trim(insuredno) as insuredno,--insuredid ,-- '主被保险人 insuredno',
	trim(INSUREDNAME) as INSUREDNAME,--insname ,-- '主被保险人名称 POLICY_INFORMATION.INSUREDNAME',
	trim(INSUREDIDNO) as INSUREDIDNO,--inscertid ,-- '主被保险人证件号码 POLICY_INFORMATION.INSUREDIDNO',
	trim(RISKCODE) as RISKCODE,--productid ,-- '险种代码 POLICY_INFORMATION.RISKCODE',
	trim(RISKNAME) as RISKNAME, --productname 险种名称
	trim(SALECOM) as SALECOM,--policybelong ,-- '业绩归属 POLICY_INFORMATION.SALECOM',
	PAYENDYEAR,--payendyear ,-- '缴费年期 POLICY_INFORMATION.PAYENDYEAR',
	trim(APPFLAG) as APPFLAG, --保单状态
	prem,--保费
	sumprem,--累计保费
	AMNT,--保额
	trim(PAYTODATE) as PAYTODATE,--下次缴费日期
	trim(polapplydate) as polapplydate,--投保日期
	trim(SIGNDATE) as SIGNDATE,--签单日期
	trim(AGENTCOM) as AGENTCOM,--代理机构
	trim(SALECHNL) as SALECHNL,--销售渠道
	trim(SALECHNLNAME) as SALECHNLNAME,--销售渠道中文
	trim(salecomname) as salecomname,--业绩归属中文
	trim(PRESERVATIONFLAG) as PRESERVATIONFLAG,--保全标志
	"上海人寿上海分公司",
	trim(security) as security, --保险期原始值
	case
		when security ='终身' then 42720
		when security = '至100周岁' then 36500
		when security = '至80周岁' then 29200
		when security = '70年' then 25550
		when security = '至70周岁' then 25550
		when security = '至65周岁' then 23725
		when security = '至60周岁' then 21900
		when security = '30年' then 10950
		when security = '20年' then 7300
		when security = '10年' then 3650
		when security = '6年' then 2190
		when security = '5年' then 1825
		when security = '1年' then 365
		when security = '180天' then 180
		when security = '6月' then 180
		when security = '90天' then 90
		when security = '3月' then 90
		when security = '1月' then 30
		when security = '30天' then 30
		when security = '15天' then 15
		when security = '7天' then 7
	end,
	trim(AGENTCODE) as AGENTCODE,--代理人代码
	trim(AGENTNAME) as AGENTNAME,--代理人名称
	trim(paytodate) as paytodate,--续保日期
	'admin',
	sysdate()
	FROM dsj.policy_information;

	--更新INSURANCEINFO 表中的信息

	UPDATE shanglifeecif.insurancearrangement a SET (
		pano ,-- '投保单号 INSURANCEINFO.PRTNO',
		--pisdate ,-- '签单日期 INSURANCEINFO.SIGNDATE',
		--padate ,-- '投保日期 INSURANCEINFO.POLAPPLYDATE',
		--policystate ,--INSURANCEINFO,appflag
		--prem ,-- '总保费 INSURANCEINFO.PREM',
		--productname ,-- '险种名称	 INSURANCEINFO.RISKNAME'
		agentchannel -- '代理渠道 INSURANCEINFO.SELLTYPE',
	) = (
		select
			trim(b.PRTNO) as PRTNO,--pano ,-- '投保单号 INSURANCEINFO.PRTNO',
			--SIGNDATE,--pisdate ,-- '签单日期 INSURANCEINFO.SIGNDATE',
			--POLAPPLYDATE,--padate ,-- '投保日期 INSURANCEINFO.POLAPPLYDATE',
			--appflag,--policystate ,--INSURANCEINFO,appflag
			--PREM,--prem ,-- '总保费 INSURANCEINFO.PREM',
			--RISKNAME,--productname ,-- '险种名称	 INSURANCEINFO.RISKNAME'
			trim(b.SELLTYPE) as SELLTYPE--agentchannel ,-- '代理渠道 INSURANCEINFO.SELLTYPE',
		from dsj.INSURANCEINFO b
		where b.contno  = a.policyno
	) WHERE 1=1 ;
	dbms_output.put_line('init_insurancearrangement_policy_information函数跑批完成！');
	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.insuranceclaimthread_main() -- 创建主存储过程
IS
BEGIN
	   insert into shanglifeecif.insuranceclaimthread (
		icthreadid ,--'保险理赔Id',1
		crno ,--'立案号',2
		--companyno ,-- '公司代码',
		policyno ,--'保险单号 INSURANCE_CLAIM.CONTNO',3
		--applicantid ,--'投保人ID',
		--appname ,--'投保人名称',
		--appphone ,--'投保人手机',
		--appcertid ,--'投保人证件号码 INSURANCE_CLAIM.RISKDATE',
		cnno ,--'报案号',4
		losstime ,-- '损失发生时间',5
		--rpid ,-- '报案人',
		rpname ,--'报案人名称',6
		--rpphone ,-- '报案人电话',
		--lpid ,--'出险人	根据INSURANCE_CLAIM.INSUREDNO获取indid',
		lpscutid,--'出险人上游客户号7
		lpname ,-- '出险人名称',
		cndate ,--'报案日期 INSURANCE_CLAIM.RPTDATE',
		lossdescribe ,-- '损失原因 案件发生的原因:INSURANCE_CLAIM.LLOCCURREASON',
		crdate ,-- '立案日期 INSURANCE_CLAIM.RGTDATE',
		cndescribe ,--'报案描述 INSURANCE_CLAIM.ACCIDENTDETAIL',
		claimstatus ,--'理赔状态 案件的状态，例如：录入、已立案等',
		csdate ,--'理赔状态日期',
		relationship ,-- '报案人与出险人关系',
		ccno ,-- '赔案号 AUDIT_CLAIM_INSURANCE.CLMNO',
		ccstatus ,--'赔案状态',
		--ccsdate ,-- '赔案状态日期',
		productid ,--'险种代码	INSURANCE_CLAIM_HEALTH.RISKCODE',
		productname, --'险种名称	INSURANCE_CLAIM_HEALTH.RISKNAME',
		ccamt ,--'赔付金额	INSURANCE_CLAIM_HEALTH.realpay',
		--branchcode,-- '机构代码',
		claimcasestatus ,--'案件状态INSURANCE_CLAIM_HEALTH.LLCLAIMSTATE',
		accidenttype,
		created_by ,
		created_time
		-- updated_time,
		-- updated_by
	) select
   		row_number()over(),--1
   		trim(rgtno) as rgtno,--2
		trim(contno) as contno,--3
		trim(rptno) as rptno,--报案号4
		trim(riskdate) as riskdate,--损失发生的时间5
		trim(rptorname) as rptorname,--报案人名称6
   		trim(INSUREDNO) as INSUREDNO,--出险人 上游客户号
   		trim(INSUREDNAME) as INSUREDNAME,
		trim(rptdate) as rptdate,
		trim(lloccurreason) as lloccurreason,
		trim(rgtdate) as rgtdate,
		trim(ACCDESC) as ACCDESC,--报案描述
		trim(llclaimstate) as llclaimstate,
		trim(auditdate) as auditdate,--审批日期
		trim(relationname) as relationname,
		trim(clmno) as clmno,--赔案号
		trim(llgettype) as llgettype,--赔案状态
		trim(riskcode) as riskcode,
		trim(riskname) as riskname,
		realpay,
		trim(llclaimstate) as llclaimstate,--案件状态
		trim(accidenttype) as accidenttype,
		'admin',
		sysdate()
	from dsj.INSURANCE_CLAIM where contno is not null;

	-- 更新渠道字段
	shanglifeecif.update_insuranceclaimthread_add_salecom();
	dbms_output.put_line('insuranceclaimthread_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.newprocedure( )
IS
DECLARE
	s_count int
BEGIN
	 DELETE FROM shanglifeecif.IndRelationShip;
	--从保单信息表（dsj.POLICY_INFORMATION）获取投保人和被保人的关系并
	--第一步取关系父母，子女
	 insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		RSSTime,
		Role2
	)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
		FROM dsj.POLICY_INFORMATION p
		WHERE (p.RELATIONTOAPPNT = '父母' OR p.RELATIONTOAPPNT = '子女')
		AND p.birthday > p.insuredbirthday
		GROUP BY p.CUSTOMERNO,p.INSUREDNO;


	insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		RSSTime,
		Role2
	)
	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
	FROM dsj.POLICY_INFORMATION p
	WHERE (p.RELATIONTOAPPNT = '父母' OR p.RELATIONTOAPPNT = '子女')
	AND p.birthday < p.insuredbirthday
	GROUP BY p.CUSTOMERNO,p.INSUREDNO;

	--第二步取关系祖父母、外祖父母    祖孙、外祖孙
	insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		RSSTime,
		Role2
	)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
	FROM dsj.POLICY_INFORMATION p
	WHERE (p.RELATIONTOAPPNT = '祖父母、外祖父母' OR p.RELATIONTOAPPNT = '祖孙、外祖孙')
	AND p.birthday > p.insuredbirthday
	GROUP BY p.CUSTOMERNO,p.INSUREDNO;

	insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		RSSTime,
		Role2
	)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
		FROM dsj.POLICY_INFORMATION p
		WHERE (p.RELATIONTOAPPNT = '祖父母、外祖父母' OR p.RELATIONTOAPPNT = '祖孙、外祖孙')
		AND p.birthday < p.insuredbirthday
		GROUP BY p.CUSTOMERNO,p.INSUREDNO;

	--第三步取配偶和其他
	insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		RSSTime,
		Role2
	)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
	FROM dsj.POLICY_INFORMATION p
	WHERE p.RELATIONTOAPPNT = '配偶'
	-- or p.RELATIONTOAPPNT = '其他'
	GROUP BY p.CUSTOMERNO,p.INSUREDNO;

	-- 完善被保人关系
/**	*/

	insert into shanglifeecif.IndRelationShip(
		irsid,
		RSType,
		IndID1,
		Name1,
		IDCard1,
		Role1,
		IndID2,
		Name2,
		IDCard2,
		Role2
	)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
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_bq()
IS
DECLARE
	p_count int
BEGIN

	 insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PRole,
		Scenario,
		SDate,
		DESCRIBE,
		created_by ,
		created_time
)select
   		reflect("java.util.UUID", "randomUUID"),
		i.contno,
		i.customerno,
		max(i.name),
		'投保人' AS tbr,
   		'保全' AS bq,
		l.edorappdate,
		max(lm.edorname),
		'admin',
		sysdate()
FROM dsj.lpedoritem l,dsj.lmedoritem lm,dsj.policy_information i
WHERE l.edortype = lm.edorcode AND lm.appobj <> 'G' AND l.edorstate = '0' AND i.contno = l.contno
GROUP BY i.customerno,i.contno,l.edorappdate,l.edortype;

dbms_output.put_line('partytimeline_bq函数跑批完成！');
	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_hf()
IS
DECLARE
	p_count int
BEGIN
	 insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PRole,
		Scenario,
		SDate,
		DESCRIBE,
		created_by ,
		created_time
	) SELECT
				reflect("java.util.UUID", "randomUUID"),
		   		cad.productno,
		   		cac.customerno,
		   		max(cac.customername),
		   		'投保人',
		   		'回访',
		   		cad.lastdealtime,
		   		CASE max(cad.actiondefguid)
		   		when '402837815c1a4fc6015c1a735351122d' then '特殊回访'
				when '402837815c1a4fc6015c1a735350012a' then '其他回访'
				when '402837815c2ff6b5015c3005e7fb0004' then '失效回访'
				when '402837815c2ff6b5015c3005e7fb2222' then '回执超期回访'
				when '402837815c2ff6b5015c3005e7fb0003' then '宽限期50天'
				when '402837815c2ff6b5015c3005e7fb0002' then '宽限期30天'
				when '402837815c1a4fc6015c1a735350012f' then '新契约回访'
				when '402837815c2ff6b5015c3005e7fb0001' then '续期回访'
				end ,
		   		'admin',
				sysdate()
		   	FROM dsj.CC_ACTION_DATA cad,dsj.cc_action_customerinfo cac
			WHERE cac.unioncustomerid = cad.unioncustomerid AND cad.lastdealtime IS NOT null GROUP BY cad.productno,cac.customerno,cad.lastdealtime;

	dbms_output.put_line('partytimeline_hf函数跑批完成！');
	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_lp()
IS
DECLARE
	p_count int
BEGIN

	 insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PCertID,
		PRole,
		Scenario,
		SDate,
		describe,
		created_by ,
		created_time
	) select
   		reflect("java.util.UUID", "randomUUID"),
   		trim(contno) as contno,
   		trim(INSUREDNO) as INSUREDNO,
   		trim(INSUREDNAME) as INSUREDNAME,
   		trim(idno) as idno,
   		'投保人',
   		'理赔',
   		CASE
   		WHEN APPLYDATE IS NOT NULL THEN APPLYDATE
   		WHEN APPLYDATE IS NULL THEN RPTDATE
   		END AS SDate,
   		trim(ACCIDENTTYPE) as ACCIDENTTYPE,
   		'admin',
		sysdate()
	from dsj.insurance_claim  WHERE contno IS NOT NULL;
	dbms_output.put_line('partytimeline_lp函数跑批完成！');
	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.partytimeline_tb() -- 创建主存储过程
IS
BEGIN
	 insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PCertID,
		PRole,
		Scenario,
		SDate,
		enddate,
		created_by ,
		created_time
	) select
   		reflect("java.util.UUID", "randomUUID"),
   		trim(contno) as contno,
   		trim(max(customerno)) as customerno,
   		trim(max(name)) as name,
   		trim(max(idno)) as idno,
   		'投保人',
   		'投保',
   		trim(max(polapplydate)) as polapplydate,
		trim(max(enddate)) as enddate,
		'admin',
		sysdate()
	from dsj.policy_information WHERE contno IS NOT NULL GROUP BY contno;
	dbms_output.put_line('partytimeline_tb函数跑批完成！');
	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_ts()
IS
DECLARE
	p_count int
BEGIN


	 insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PRole,
		Scenario,
		SDate,
		DESCRIBE,
		created_by ,
		created_time
	) SELECT
		reflect("java.util.UUID", "randomUUID"),
		max(ac.productnos),
		max(i.customerno),
		max(ac.complaintsname),
		'投保人'||CASE max(ac.complaintsrelation)
		WHEN '201' THEN ''
		WHEN '202' THEN '子女'
		WHEN '203' THEN '配偶'
		WHEN '204' THEN '父母'
		WHEN '205' THEN '其他关系'
	END AS complaintsrelation,
		'投诉',
		ac.inserttime ,
	CASE max(ac.resultclassification)
		WHEN '101' THEN '销售纠纷'
		WHEN '102' THEN '理赔纠纷'
		WHEN '103' THEN '退保纠纷'
		WHEN '104' THEN '承保纠纷'
		WHEN '105' THEN '续收续保纠纷'
		WHEN '106' THEN '保全纠纷'
		WHEN '107' THEN '其他'
	END as resultclassification,
	'admin',
	sysdate
FROM dsj.cc_action_complaints ac
LEFT JOIN dsj.policy_information i ON ac.productnos = i.contno
	WHERE ac.productnos IS NOT NULL AND i.customerno IS NOT NULL
	GROUP BY ac.inserttime ;
	dbms_output.put_line('partytimeline_ts函数跑批完成！');
	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_tuibao()
IS
DECLARE
	p_count int
BEGIN


	 insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PRole,
		Scenario,
		SDate,
		DESCRIBE,
		created_by ,
		created_time
	) SELECT
		reflect("java.util.UUID", "randomUUID"),
		trim(t.contno) as contno,
		trim(t.customerno) as customerno,
		trim(t.name) as name,
		trim(t.tbr) as tbr,
		trim(t.tb) as tb,
		trim(t.edorappdate) as edorappdate,
		t.edorreason,
		'admin',
		sysdate()
		FROM (
			SELECT
				row_number() over(partition by i.contno) rn,
				lm.contno,
				i.customerno,
				i.name,
				'投保人' AS tbr,
				'退保' AS tb,
				to_char(lm.edorappdate,"yyyy-MM-dd") edorappdate,
				CASE lm.edorreasoncode
					WHEN '01' THEN '死亡'
					WHEN '02' THEN '失踪'
					WHEN '03' THEN '离异'
					WHEN '04' THEN '债权转移'
					WHEN '05' THEN '被保险人成年'
					WHEN '06' THEN '其它'
					ELSE '其它'
				END AS 	edorreason
			FROM dsj.lpedoritem lm,dsj.policy_information i
			where lm.contno = i.contno AND lm.edortype='CT' AND lm.edorstate = 0
			) t WHERE t.rn = 1;
			dbms_output.put_line('partytimeline_tuibao函数跑批完成！');
	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_xq() -- 创建主存储过程
IS
BEGIN

	 insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PRole,
		Scenario,
		SDate,
		created_by ,
		created_time
	) select
   		reflect("java.util.UUID", "randomUUID"),
   		lp.contno,
   		lp.appntno,
   		 '',
   		'投保人',
   		'续期',
   		to_char(lp.confdate,'yyyy-MM-dd'),
   		'admin',
   		sysdate
	FROM dsj.ljapayperson lp WHERE lp.paycount > 1;

	dbms_output.put_line('partytimeline_xq函数跑批完成！');
	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_zx()
IS
DECLARE
	p_count int
BEGIN


	  insert into shanglifeecif.partytimeline (
		TripID,
		PolicyNo,
		PartyID,
		name,
		PRole,
		Scenario,
		SDate,
		DESCRIBE,
		created_by ,
		created_time
	) SELECT
				reflect("java.util.UUID", "randomUUID"),
		   		m.contnos,
		   		max(i.customerno) customerno,
		   		max(m.econtactsName) econtactsName,
		   		'投保人' AS tbr,
		   		'咨询' AS zx,
		   		m.starttime,
		   		max(m.reasonsecondname) reasonsecondname,
		   		'admin',
				sysdate()
		from dsj.cc_record_main  m
		LEFT JOIN dsj.policy_information i ON m.contnos = i.contno
	WHERE m.contnos IS NOT NULL  GROUP BY m.contnos,m.starttime;
	dbms_output.put_line('partytimeline_zx函数跑批完成！');
	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.riskcode_statistics() IS
DECLARE
BEGIN
      DELETE FROM shanglifeecif.riskcode_statistics;
	 insert into shanglifeecif.riskcode_statistics (
		id,
		kindName,
		khnum,
		tatolprem
	) --险种大类统计
		SELECT
			row_number()over(),
			temp.kindName,
			temp.khnum,
			temp.tatolprem
		FROM (
			SELECT
				crt.riskcategoriesname AS kindName,
				count(DISTINCT scustid) AS khnum,
				sum(risk) AS tatolprem
			FROM shanglifeecif.customer_risk_temp crt
			WHERE crt.riskcategoriesname IS NOT null
			GROUP BY crt.riskcategoriesname

		) temp;
	dbms_output.put_line('riskcode_statistics函数跑批完成！');
	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.riskcode_statistics_channel() IS
DECLARE
BEGIN
      DELETE FROM shanglifeecif.riskcode_statistics_channel;
	 insert into shanglifeecif.riskcode_statistics_channel (
		id,
		kindName,
		khnum,
		tatolprem,
		salecom,
		salecomname
	) --险种大类统计
		SELECT
			row_number()over(),
			temp.kindName,
			temp.khnum,
			temp.tatolprem,
			temp.salecom,
			temp.salecomname
		FROM (
			SELECT
				crt.riskcategoriesname AS kindName,
				count(DISTINCT scustid) AS khnum,
				sum(risk) AS tatolprem,
				crt.salecom AS salecom,
				crt.salecomname AS salecomname
			FROM shanglifeecif.customer_risk_temp crt
			WHERE crt.riskcategoriesname IS NOT null
			and crt.salecom is not null
			GROUP BY crt.riskcategoriesname,crt.salecom,crt.salecomname
		) temp;
	dbms_output.put_line('riskcode_statistics_channel函数跑批完成！');
	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.same_customer() -- 创建主存储过程
IS
DECLARE
	scount int
BEGIN
 DELETE FROM shanglifeecif.samecustomer;

 INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		Passport,
  		Dlicense,
  		OtherIdNumber,
  		sameType
)
SELECT
	reflect("java.util.UUID", "randomUUID"),
	sc1.scustid,
	sc1.name,
	sc1.gender,
	sc1.birthday,
	CASE sc1.idtype WHEN '0' THEN sc1.idcard END AS idcard,
	CASE sc1.idtype WHEN '1' THEN sc1.idcard END AS passport,
	CASE sc1.idtype WHEN '3' THEN sc1.idcard END AS dlicense,
	CASE  WHEN sc1.idtype <> '0' AND sc1.idtype <> '1' AND sc1.idtype <> '3' THEN sc1.idcard END AS otherIdnumber,
	0
FROM shanglifeecif.customertotaltemp sc1
WHERE sc1.mobile IS NOT NULL AND EXISTS (SELECT 1 FROM shanglifeecif.customertotaltemp sc2 WHERE sc1.scustid != sc2.scustid
		AND sc1.name = sc2.name AND sc1.gender = sc2.gender AND sc1.idtype = sc2.idtype AND sc1.mobile = sc2.mobile);

	--三要素疑似相同指姓名、性别、出生日期一致   1
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		Passport,
  		Dlicense,
  		OtherIdNumber,
  		sameType

)
SELECT
	reflect("java.util.UUID", "randomUUID"),
	sc1.scustid,
	sc1.name,
	sc1.gender,
	sc1.birthday,
	CASE sc1.idtype WHEN '0' THEN sc1.idcard END AS idcard,
	CASE sc1.idtype WHEN '1' THEN sc1.idcard END AS passport,
	CASE sc1.idtype WHEN '3' THEN sc1.idcard END AS dlicense,
	CASE  WHEN sc1.idtype <> '0' AND sc1.idtype <> '1' AND sc1.idtype <> '3' THEN sc1.idcard END AS otherIdnumber,
	1
FROM shanglifeecif.customertotaltemp sc1
WHERE EXISTS(SELECT 1 FROM shanglifeecif.customertotaltemp sc2 WHERE sc2.scustid!=sc1.scustid AND sc1.name = sc2.name AND sc1.gender = sc2.gender AND sc1.birthday = sc2.birthday )
ORDER BY sc1.name,sc1.gender,sc1.birthday
	--两要素疑似相同指姓名、证件号码一致 2
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		Passport,
  		Dlicense,
  		OtherIdNumber,
  		sameType

)
SELECT
	reflect("java.util.UUID", "randomUUID"),
	sc1.scustid,
	sc1.name,
	sc1.gender,
	sc1.birthday,
	CASE sc1.idtype WHEN '0' THEN sc1.idcard END AS idcard,
	CASE sc1.idtype WHEN '1' THEN sc1.idcard END AS passport,
	CASE sc1.idtype WHEN '3' THEN sc1.idcard END AS dlicense,
	CASE  WHEN sc1.idtype <> '0' AND sc1.idtype <> '1' AND sc1.idtype <> '3' THEN sc1.idcard END AS otherIdnumber,
	2
FROM shanglifeecif.customertotaltemp sc1

WHERE sc1.idcard is not null and  EXISTS (SELECT 1 FROM shanglifeecif.customertotaltemp sc2 WHERE sc2.scustid!=sc1.scustid AND sc1.name = sc2.name AND sc1.idcard = sc2.idcard);

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

CREATE OR REPLACE PROCEDURE shanglifeecif.surrender_protection_temp() IS
DECLARE
BEGIN
		 DELETE FROM shanglifeecif.surrenderprotectiontemp;
		 insert into shanglifeecif.surrenderprotectiontemp(
			tbid,
		 	scustid,
		 	tbday
		)SELECT row_number()over(),p.customerno,max(DATEDIFF(t.edorappdate,p.cvalidate)) AS tbday FROM dsj.lpedoritem t,dsj.policy_information p
	WHERE t.contno = p.contno and t.edortype='CT' AND edorstate = 0 GROUP BY p.customerno;
	dbms_output.put_line('surrender_protection_temp函数跑批完成！');
	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.tb_customerinfo() IS
DECLARE
BEGIN
		DELETE FROM shanglifeecif.tbcustomerinfo;
		insert into shanglifeecif.tbcustomerinfo(
			tbid,
		 	scustid,
		 	contno,
		 	sdate,
		 	edate,
		 	salecom,
		 	salecomname
		)SELECT row_number()over(),p.customerno,p.contno,p.cvalidate,t.edorappdate ,p.salecom,p.salecomname FROM dsj.lpedoritem t,dsj.policy_information p
	WHERE t.contno = p.contno and t.edortype='CT' AND t.edorstate = 0 ;
	dbms_output.put_line('tb_customerinfo函数跑批完成！');
	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.up_other_customerinfo() -- 创建存储过程
IS
BEGIN
	--更新客户类型投保人
	  UPDATE shanglifeecif.individual i SET i.custtype = '投保人' WHERE
	i.scustid IN (SELECT p.customerno FROM dsj.policy_information p);
	--更新客户类型投保人、被保人
	 UPDATE shanglifeecif.individual i SET i.custtype = '被保人' WHERE
	i.scustid IN (SELECT pi.insuredno FROM dsj.policy_information pi);
	--更新客户类型投保人、被保人
	UPDATE shanglifeecif.individual i SET i.custtype = '投保人、被保人' WHERE
	i.scustid IN (SELECT p.customerno FROM dsj.policy_information p)
	AND i.scustid IN (SELECT pi.insuredno FROM dsj.policy_information pi);

	--更新业绩归属
	UPDATE shanglifeecif.Individual si1 SET si1.policybelong = (
		SELECT trim(t.SALECOM) FROM (
			SELECT row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate asc) rd,p.customerno,p.SALECOM FROM dsj.policy_information p
		) t WHERE t.rd = 1 AND si1.scustid = t.customerno
	);

	UPDATE shanglifeecif.Individual si1 SET si1.policybelong = (
		SELECT trim(t.SALECOM) FROM (
			SELECT row_number()OVER(PARTITION BY p.insuredno ORDER BY p.polapplydate asc) rd,p.insuredno,p.SALECOM FROM dsj.policy_information p
		) t WHERE t.rd = 1 AND si1.scustid = t.insuredno
	);

	--更新职业，民族等信息
	UPDATE shanglifeecif.Individual si1 SET (si1.Occupation,si1.Ethnic,si1.education) = (
		SELECT trim(t.occupationname),trim(t.NATIONALITYNAME),trim(t.degree) FROM (
			SELECT row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate desc) rd,p.customerno,p.occupationname,p.NATIONALITYNAME,p.degree FROM dsj.policy_information p
		) t WHERE t.rd = 1 AND si1.scustid = t.customerno
	);

	--更新官微积分
	UPDATE shanglifeecif.individual si SET si.officialCalculus = (
	   SELECT temp.available_points FROM (SELECT t.available_points,t.certificate_no,row_number()OVER(PARTITION BY t.certificate_no ORDER BY t.gmt_created DESC) rd FROM dsj.t_account t) temp  WHERE temp.rd = 1 AND temp.certificate_no = si.idcard
	);
	dbms_output.put_line('up_other_customerinfo函数跑批完成！');
	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.up_t_customers_class_1() -- 创建存储过程
IS
BEGIN
	   UPDATE shanglifeecif.individual a SET (
			CustClass	,--	客户等级
			ConValue	,--	贡献度分
			Awarded3	,--	家庭加分2
			Awarded2	,--	续期加分
			Awarded1	,--	保单加分
			SOValue	,--	总分值
			EndDate,	--	客户等级失效日期
			Height,		--身高
			Weight,		--体重
			BMI,
			PIncome,	--个人年收入
			FIncome,	--家庭年收入
			IncomeSource,	--收入来源
			SIStatus,	--社保情况
			--Ethnic,  --民族情况
			Nation, --国籍
			MaritalStat, --婚姻
			Employer,--工作单位
			--Education,--学历
			Dday, --死亡日期
			regtype,  --户籍类型
			ZIPCODE,--邮编
			HPhone,--电话
			PMPhone,--手机
			email, -- 邮箱
			RAL,    --联系地址
			sobirth --省份
		) = (
			select
					b.CLASS_VALUE,--客户等级
					b.CONTRIBUTION_VALUE,--贡献度分
					b.AWARDED3, --家庭加分
					b.AWARDED2, --续期加分
					b.AWARDED1, --保单加分
					b.TOTAL_VALUE, --总分值
					trim(b.END_DATE) as END_DATE, --失效日期
					b.STATURE,	--身高
					b.AVOIRDUPOIS,--体重
					b.BMI,        --根据身高体重计算
					b.YEARINCOME,	--个人年收入
					b.FAMILYYEARSALARY,	--家庭年收入
					trim(b.INCOMESOURCE) as INCOMESOURCE,	--收入来源
					trim(b.SOCIALINSUFLAG) as SOCIALINSUFLAG,	--社保情况
					--trim(b.NATIONALITY) as NATIONALITY, --民族情况
					trim(b.NATIVEPLACE) as NATIVEPLACE, --国籍
					trim(b.MARRIAGE) as MARRIAGE,--婚姻
					trim(b.GRPNAME) as GRPNAME,--工作单位名称
					--trim(b.DEGREE) as DEGREE,--学历
					trim(b.DEATHDATE) as DEATHDATE,--死亡日期
					trim(b.DENTYPE) as DENTYPE,--户籍类型
					trim(b.ZIPCODE) as ZIPCODE,--邮编
					trim(b.PHONE) as PHONE,--电话
					trim(b.MOBILE) as MOBILE,--手机
					trim(b.EMAIL) as EMAIL,--邮箱
					regexp_replace(trim(b.POSTALADDRESS),'[0-9０-９]','*') as POSTALADDRESS, --联系地址
					regexp_extract(b.POSTALADDRESS,'(.*?)省|(.*?)市',0) -- 省份
				from dsj.t_customer_class b
				where b.CUSTOMER_ID = a.scustid
		) WHERE 1=1 ;
		dbms_output.put_line('up_t_customers_class_1函数跑批完成！');
		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.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.update_insuranceclaimthread_add_salecom()
IS
BEGIN
	UPDATE shanglifeecif.insuranceclaimthread  insd
SET (
	insd.SALECOM,
	insd.salecomname
	)
    =(SELECT t.salecom ,  t.salecomname FROM
		 (
		 	SELECT
			   contno,  salecom ,  salecomname
			  FROM  dsj.policy_information t
			GROUP BY   contno  ,  salecom  ,  salecomname
		 ) t WHERE t.contno = insd.policyno
		-- AND t.contno in('2019012400157198','2019071600119318')
     )
	dbms_output.put_line('update_insuranceclaimthread add saleCome 函数跑批完成！');
	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.update_insuredinfo()
IS
BEGIN
		--更新被保人信息
		   UPDATE shanglifeecif.individual a SET (
			ZIPCODE,--邮编
			HPhone,--电话
			PMPhone,--手机
			email, -- 邮箱
			RAL,    --联系地址
			sobirth --省份
		) = (
			SELECT
				t.zipcode,
				t.phone,
				t.mobile,
				t.email,
				regexp_replace(trim(t.address),'[0-9]','*') address,
				regexp_extract(t.address,'(.*?)省|(.*?)市',0) province
			FROM (
				SELECT
				row_number()OVER(PARTITION BY ls.customerno ORDER BY ls.modifydate desc) rn,
				ls.customerno,--客户号
				CASE
					WHEN ls.postaladdress IS NULL
						THEN
							CASE WHEN ls.homeaddress IS NULL THEN ls.companyaddress ELSE ls.homeaddress END
						ELSE ls.postaladdress
					END AS address,
				ls.phone,
				ls.zipcode,--邮编
				CASE WHEN ls.email IS NULL THEN ls.email2 ELSE ls.email END AS email,
				CASE WHEN ls.mobile IS NULL THEN ls.mobile2 ELSE ls.mobile END AS mobile
				FROM dsj.lcaddress ls
			) t where t.rn = 1 AND t.customerno = a.scustid
		) WHERE a.custtype = '被保人';


	UPDATE shanglifeecif.individual a SET (
			Height,		--身高
			Weight,		--体重
			BMI,
			PIncome,	--个人年收入
			Ethnic,  --民族情况
			Nation, --国籍
			MaritalStat, --婚姻
			Education,--学历
			SIStatus,--社保
			IncomeSource,--收入来源
			Occupation,--职业
			regtype  --户籍类型
		) = (
			SELECT
				t.stature,--身高
				t.avoirdupois,--体重
				t.bmi,
				t.yearincome,
				t.nationality,--民族
				t.nativeplace,--国籍
				t.marriage, --婚姻状况
				t.degree,   --学历
				t.socialinsuflag,--社保
				t.incomesource,--收入来源
				t.occupationcode,--职业
				t.dentype
			FROM (
				SELECT
				row_number()OVER(PARTITION BY ld.insuredno ORDER BY ld.modifydate desc) rn,
				ld.insuredno,--被保人客户号
				ld.appntno,--投保人客户号
				ld.name,--被保人名称
				ld.sex,--被保人性别
				ld.birthday,--被保人出生日期
				ld.idtype,--证件类型
				ld.idno,--证件号码
				ld.marriage,--婚姻状况
				ld.occupationcode,--职业
				ld.nativeplace,--国籍
				ld.nationality,--民族
				ld.stature,--身高
				ld.avoirdupois,--体重
				round((ld.avoirdupois/POWER((ld.stature/100),2)),2) bmi,
				ld.degree,--学历
				ld.dentype,--居民类型
				ld.socialinsuflag,--社保情况
				ld.incomesource,--收入来源
				ld.yearincome --年收入
			FROM dsj.lcinsured2 ld
			) t WHERE t.rn = 1 AND t.insuredno = a.scustid
		) WHERE a.custtype = '被保人';

		dbms_output.put_line('update_insuredinfo函数跑批完成！');
		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.update_partytimeline_add_salecom()
IS
BEGIN
	UPDATE shanglifeecif.partytimeline pt SET (pt.salecom,pt.salecomname) =
	(
		SELECT t.salecom, t.salecomname FROM
		(
			SELECT
				contno, salecom, salecomname
			FROM  dsj.policy_information t
			GROUP BY contno, salecom, salecomname
		) t WHERE t.contno = pt.policyno
 	)
	dbms_output.put_line('update_partytimeline_add_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.update_risk_categories() -- 创建主存储过程
IS
BEGIN
	--更新险种大类
	  UPDATE shanglifeecif.insurancearrangement a SET (

		risk_categories_name
	) = (
		select
				kindtype
		from dsj.riskkind b WHERE a.productid = b.riskcode
	) WHERE 1=1;
	dbms_output.put_line('update_risk_categories函数跑批完成！');
	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