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

CREATE OR REPLACE PROCEDURE shanglifeecif.updateIndividualLable_61_80() IS
DECLARE
BEGIN
	
	--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 policy_information p WHERE p.contno IN (
			SELECT contno FROM lcget WHERE getdutycode IN (select getdutycode from 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 policy_information p WHERE p.contno IN (
			SELECT contno FROM lcget WHERE getdutycode IN (select getdutycode from lmdutygetalive where getdutyname = '生存保险金')
		) 
	);
	--68	是否有红利可领取的保单	Label64	有红利可领取保单	无	0
	UPDATE shanglifeecif.Individual si1 SET si1.label64 = '红利可领取的保单' WHERE si1.scustid IN 
	(SELECT p.customerno FROM policy_information p,lmriskapp l WHERE p.riskcode = l.riskcode AND l.bonusflag = 1);
	
	--69	一单寿险客户	Label65	一单寿险客户	客户仅购买了一张保单，且保单类型为寿险型保单
	UPDATE shanglifeecif.Individual si1 SET si1.label65 = '一单寿险客户' WHERE si1.scustid IN (SELECT t.customerno FROM (
		SELECT count(*) AS pnum,p.customerno FROM POLICY_INFORMATION p 
	LEFT JOIN lmriskapp b on p.riskcode = b.riskcode
	WHERE b.kindcode = 'L' GROUP BY p.customerno
	) t WHERE t.pnum = 1);
	--70	缴费期满客户	Label66	缴费期满客户	POLICY_INFORMATION:paycount= payendyear	客户缴费期数已满
	UPDATE shanglifeecif.Individual si1 SET si1.label66 = '缴费期满客户' WHERE si1.scustid IN (SELECT t.customerno FROM (
			SELECT count(*) AS pnum,p.customerno FROM POLICY_INFORMATION p 
		WHERE p.paycount = p.payendyear GROUP BY p.customerno
		) t WHERE t.pnum > 0);
	--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 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	客户最后一次接触的业务类型
	--75	最近接触方式	Label71	最近外呼、最近呼入、微信	Individual.LCMethod	客户最后一次接触的方式
	--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 account a WHERE a.account_status = 0) 
		OR si1.passport IN (SELECT a.certificate_no FROM account a WHERE a.account_status = 0) 
		OR si1.Dlicense IN  (SELECT a.certificate_no FROM account a WHERE a.account_status = 0) 
		OR si1.othernumber IN (SELECT a.certificate_no FROM account a WHERE a.account_status = 0);
	--	本人生日提醒	Label77	本人生日临近		客户生日-当前日期<5
	UPDATE shanglifeecif.Individual si1 SET si1.label77 = '本人生日临近' WHERE (DAYOFYEAR(sysdate(),'yyyy-MM-dd')-DAYOFYEAR(si1.birthday,'yyyy-MM-dd')) BETWEEN 1 AND 5;
	
	EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());
			WHEN Others THEN
				INSERT INTO shanglifeecif.exception_log(log_code,log_msg,log_time) VALUES (sqlcode(),sqlerrm(),sysdate());	
END