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	�ش�Ͷ��Ӱ��	Ͷ����Դ	Ͷ����Դ�DZ���֡�����ᡢý��ת���
	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