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

CREATE OR REPLACE PROCEDURE shanglifeecif.updateIndividualLable_101_120() IS
DECLARE
BEGIN
	--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 cc_record_main m 
		LEFT JOIN  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 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 = (SELECT "要求较多" FROM (

		SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno) rn
		FROM (
		SELECT max(t.mnum) AS mnum,f.policyno,count(*) AS fnum FROM (
		SELECT count(*) AS mnum,m.contnos FROM cc_record_main m WHERE m.contnos IS NOT null
		GROUP BY m.contnos
		) t LEFT JOIN cc_swfflowmain f ON t.contnos = f.policyno
		WHERE f.flowid LIKE 'ZX%' AND f.policyno IS NOT NULL
		GROUP BY f.policyno
		) temp LEFT JOIN policy_information p ON temp.policyno = p.contno
		WHERE temp.fnum = temp.mnum
) tt WHERE tt.rn = 1 AND tt.customerno IS NOT NULL AND si1.scustid = tt.customerno);
	--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 = (SELECT "易投诉升级" FROM (

	SELECT p.customerno,row_number()OVER(PARTITION BY p.customerno) rn
	FROM (
	SELECT max(t.mnum) AS mnum,f.policyno,count(*) AS fnum FROM (
	SELECT count(*) AS mnum,m.contnos FROM cc_record_main m WHERE m.contnos IS NOT null
	GROUP BY m.contnos
	) t LEFT JOIN cc_swfflowmain f ON t.contnos = f.policyno
	LEFT JOIN cc_swf_sort r ON r.id=f.complaintProject
	WHERE f.flowid LIKE 'ZX%' AND r.idname='投诉' and f.policyno IS NOT NULL
	GROUP BY f.policyno
	) temp LEFT JOIN policy_information p ON temp.policyno = p.contno
	WHERE temp.fnum = temp.mnum
) tt WHERE tt.rn = 1 AND tt.customerno IS NOT NULL AND si1.scustid = tt.customerno);
	--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 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 cc_action_complaints ca,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 cc_action_complaints c,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));
    
    
    --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 cc_action_complaints c,cc_action_data_complaints cd,policy_information p
		         
		where cd.complaintsid=c.complaintsid AND p.contno = cd.productno GROUP BY p.contno

		) t LEFT JOIN policy_information pi ON t.contno = pi.contno GROUP BY pi.customerno
	
	) tt WHERE tt.customerno = si1.scustid

);
	
	--109	高金额	Label107	高金额		银保渠道期缴高于30000，有效保单；其他渠道件均保费2倍以上
	UPDATE shanglifeecif.Individual si1 SET si1.Label107 = '高保额' WHERE si1.scustid IN (SELECT p.customerno FROM policy_information p WHERE (p.salecom = '03' AND p.prem>30000 AND 
	p.appflag = '有效') OR (p.salecom <> '03' AND p.prem>60000 AND p.appflag = '有效'));
	--111	理赔报案追踪	Label109	理赔报案追踪		理赔状态为报案状态，且理赔报案日-当前日期<30天
	UPDATE shanglifeecif.Individual si1 SET si1.Label109 = '理赔报案追踪' WHERE si1.scustid IN (SELECT p.customerno FROM policy_information p WHERE p.contno IN (SELECT c.CONTNO FROM 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 policy_information p WHERE p.contno IN (SELECT c.contno FROM INSURANCE_CLAIM c WHERE c.llclaimstate = '受理' OR c.llclaimstate = '立案')
	);
	--115	生存金未领	Label113	生存金未领	判断逻辑不详	生存金领取方式为自动转账、存在生存金且未领取
	UPDATE shanglifeecif.Individual si1 SET si1.label113 = '生存金未领' WHERE si1.scustid IN (
	SELECT p.customerno FROM policy_information p WHERE p.contno IN (
		SELECT contno FROM Lcinsureacc WHERE acctype = '005' and   insuaccbala>0
	) 
);
	--117	红利垫缴还款	Label115	红利垫缴还款	判断逻辑不详	存在红利，且红利已经垫交保单还款
		UPDATE shanglifeecif.Individual si1 SET si1.label117 = '红利垫缴还款' WHERE si1.scustid IN (
		SELECT p.customerno FROM policy_information p WHERE p.contno IN (
			SELECT contno FROM loloandetail WHERE finfeetype = 'HL'
		) 
	);
	--122	重点银行	Label120	重点银行	判断逻辑不详	保单的销售渠道网点为招商银行
	UPDATE shanglifeecif.Individual si1 SET si1.Label120 = '重点银行' WHERE 
	si1.scustid IN (SELECT p.customerno  FROM policy_information p WHERE p.AGENTCOM LIKE '%招商%');
	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