ShangHaiLifeECIF/sql-achievement/存储过程/人寿测试环境使用sql整理/客户标签20210409预生产.sql

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,
					policy.customerno
				FROM
					dsj.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 
			) 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,
					policy.customerno
				FROM
					dsj.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 
			) 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	客户为保单投保人，持有保单中保单状态为失效 （最新文档逻辑）
		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' and state = '1') 
	);
	--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);
		

		
	--	本人生日提醒	Label77	本人生日临近		客户生日-当前日期<5
	--UPDATE shanglifeecif.Individual si1 SET si1.label77 = '本人生日临近' WHERE DATEDIFF(to_char(SYSDATE,"2021-MM-dd"),to_char(si1.birthday,"2021-MM-dd"))<=5 AND 
		--DATEDIFF(to_char(SYSDATE,"2021-MM-dd"),to_char(si1.birthday,"2021-MM-dd"))>0;
	UPDATE shanglifeecif.Individual si1 SET si1.label77 = '本人生日临近' WHERE DATEDIFF(to_char(si1.birthday,"2021-MM-dd"),to_char(SYSDATE,"2021-MM-dd"))<=5 AND 
		DATEDIFF(to_char(si1.birthday,"2021-MM-dd"),to_char(SYSDATE,"2021-MM-dd"))>0;

	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,"2021-MM-dd"),to_char(SYSDATE,"2021-MM-dd"))<=5 AND 
		DATEDIFF(to_char(si.birthday,"2021-MM-dd"),to_char(SYSDATE,"2021-MM-dd"))>0
	);

	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,"2021-MM-dd"),to_char(SYSDATE,"2021-MM-dd"))<=5 AND 
			DATEDIFF(to_char(si.birthday,"2021-MM-dd"),to_char(SYSDATE,"2021-MM-dd"))>0
	);
	--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
		) 
	);
	--	宽限期	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 '%招商银行%');
	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