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()

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 ,
			max(birthday) as 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
				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
				policy_information
			WHERE insuredno IS NOT NULL
			) tmpTable GROUP BY scustid;
			
		UPDATE shanglifeecif.customertotaltemp sct SET sct.mobile = (SELECT trim(t.MOBILE) FROM t_customer_class t WHERE sct.scustid=t.customer_id);

	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	;
/
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(),
	'CP'||lpad(row_number()over(),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 sc1.idtype <> '3' THEN sc.idcard END AS otherIdnumber,
	sysdate,
	'admin'
	
FROM shanglifeecif.customertotaltemp sc ORDER BY sc.scustid;
	
		
		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;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_0( individual_count OUT int) -- ��ʼ�� ����֤
IS    
BEGIN
	
	--��ѯ���˴δ��������ݲ�������
	insert into shanglifeecif.individual (
		indid,
		custid,
		scustid,
	 	name,
	 	gender,
	 	birthday,
	 	idcard,
	 	created_time,
	 	created_by
	)
	SELECT 
		row_number()over(),
		'CP'||lpad(row_number()over(),10,'0'),	
		scustid ,
		max(name) as name,
		max(gender) as gender ,
		max(birthday) as birthday,
		max(idcard) as idcard,
		sysdate,
		'admin'
		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
				policy_information 
			WHERE customerno IS NOT NULL AND idtype=0
			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
				policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype=0
			) tmpTable GROUP BY scustid
		--�Ѿ����ڵ���������
		select count(0) into individual_count from shanglifeecif.individual;
		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;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_1(individual_count INOUT  int) -- ��ʼ�� ����
IS    
BEGIN
	
	--��ѯ���˴δ��������ݲ�������
	insert into shanglifeecif.individual (
			indid,
			custid,
			scustid,
		 	name,
		 	gender,
		 	birthday,
		 	passport,
		 	created_time,
		 	created_by
	)
	SELECT 
		row_number()over(),
		'CP'||lpad(row_number()over(),10,'0'),	
		scustid ,
		max(name) as name,
		max(gender) as gender ,
		max(birthday) as birthday,
		max(idcard) as idcard,
		sysdate,
		'admin'
		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
				policy_information 
			WHERE customerno IS NOT NULL AND idtype=1
			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
				policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype=1
			) tmpTable GROUP BY scustid

	--�Ѿ����ڵ���������
	select count(0) into individual_count from shanglifeecif.individual;
	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;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_3(individual_count INOUT  int) -- ��ʼ�� ����
IS    
BEGIN
	
	--��ѯ���˴δ��������ݲ�������
	insert into shanglifeecif.individual(
		indid,
		custid,
		scustid,
	 	name,
	 	gender,
	 	birthday,
	 	dlicense,
	 	created_time,
	 	created_by
	)
	SELECT 
		row_number()over(),
		'CP'||lpad(row_number()over(),10,'0'),	
		scustid ,
		max(name) as name,
		max(gender) as gender ,
		max(birthday) as birthday,
		max(idcard) as idcard,
		sysdate,
		'admin'
		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
				policy_information 
			WHERE customerno IS NOT NULL AND idtype=3
			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
				policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype=3
			) tmpTable GROUP BY scustid

	--�Ѿ����ڵ���������
	select count(0) into individual_count from shanglifeecif.individual;
	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;
/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_individual_other(individual_count INOUT  int) -- ��ʼ��������������֤����Ϣ
IS    
BEGIN
	
	--��ѯ���˴δ��������ݲ�������
	insert into shanglifeecif.individual(
			indid,
			custid,
			scustid,
		 	name,
		 	gender,
		 	birthday,
		 	otheridnumber,
		 	created_time,
		 	created_by
	)
	SELECT 
		row_number()over(),
		'CP'||lpad(row_number()over(),10,'0'),	
		scustid ,
		max(name) as name,
		max(gender) as gender ,
		max(birthday) as birthday,
		max(idcard) as idcard,
		sysdate,
		'admin'
		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
				policy_information 
			WHERE customerno IS NOT NULL AND idtype not in (0,1,3)
			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
				policy_information
			WHERE insuredno IS NOT NULL AND insuredidtype not in (0,1,3)
			) tmpTable GROUP BY scustid

	--�Ѿ����ڵ���������
	select count(0) into individual_count from shanglifeecif.individual;
	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;
/
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 t_customer_class b
				where b.CUSTOMER_ID = a.scustid
		) WHERE 1=1 ;
		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;
/
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 policy_information p);
	--���¿ͻ�����Ͷ���ˡ�������
	UPDATE shanglifeecif.individual i SET i.custtype = '������' WHERE 
	i.scustid IN (SELECT pi.insuredno FROM policy_information pi);
	--���¿ͻ�����Ͷ���ˡ�������
	UPDATE shanglifeecif.individual i SET i.custtype = 'Ͷ���ˡ�������' WHERE 
	i.scustid IN (SELECT p.customerno FROM policy_information p)
	AND i.scustid IN (SELECT pi.insuredno FROM 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 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 policy_information p
		) t WHERE t.rd = 1 AND si1.scustid = t.insuredno
	);
	
	--����ְҵ���������Ϣ
	UPDATE shanglifeecif.Individual si1 SET (si1.Occupation,si1.Ethnic) = (
		SELECT trim(t.occupationname),trim(t.NATIONALITYNAME) FROM (
			SELECT row_number()OVER(PARTITION BY p.customerno ORDER BY p.polapplydate desc) rd,p.customerno,p.occupationname,p.NATIONALITYNAME FROM 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 t_account t) temp  WHERE temp.rd = 1 AND temp.certificate_no = si.idcard
	);
	
	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;
/
/*
CREATE OR REPLACE PROCEDURE shanglifeecif.up_health_insurance_listing_1() -- �����洢����
IS    
BEGIN
	--���¸�����Ϣ��
	UPDATE shanglifeecif.individual a SET (
		OccupationId	,--	ְҵ����
		Occupation,	--	ְҵ����
		HomeAdress
	) = (
		SELECT 
			t.appntoccupationcode,
			t.appntoccupationname,
			t.APPNTHOMEADDRESS
		FROM (
			select 
				 appntno,
				 max(appntoccupationcode) appntoccupationcode,--ְҵ����
				 max(appntoccupationname) appntoccupationname,--ְҵ����
				max(APPNTHOMEADDRESS) APPNTHOMEADDRESS --סַ 
			from HEALTH_INSURANCE_LISTING 
			WHERE appntoccupationcode is not null
			and appntoccupationname is not NULL
			group by appntno
		) t WHERE t.appntno = a.scustid 
	);
	
END;
*/
BEGIN
	shanglifeecif.init_individual_main();
end ;