ShangHaiLifeECIF/sql-achievement/存储过程/人寿测试环境使用sql整理/客户信息.sql

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 ;