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();
	--更新被保人其它信息
	shanglifeecif.update_insuredinfo(); 

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 ,
			to_char(max(birthday),"yyyy-MM-dd")  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(ORDER BY sc.scustid),
	'CP'||lpad(row_number()over(ORDER BY sc.scustid),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 sc.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 dsj.t_customer_class b
				where b.CUSTOMER_ID = a.scustid
		) WHERE 1=1 ;
		EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());
			WHEN Others THEN
				INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),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.update_insuredinfo() 
IS    
BEGIN
		--更新被保人信息
		UPDATE shanglifeecif.individual a SET (
			ZIPCODE,--邮编
			HPhone,--电话
			PMPhone,--手机
			email, -- 邮箱  
			RAL,    --联系地址
			sobirth --省份
		) = (
			SELECT 
				t.zipcode,
				t.phone,
				t.mobile,
				t.email,
				regexp_replace(trim(t.address),'[0-9]','*') address,
				regexp_extract(t.address,'(.*?)省|(.*?)市',0) province
			FROM (
				SELECT
				row_number()OVER(PARTITION BY ls.customerno ORDER BY ls.modifydate desc) rn,
				ls.customerno,--客户号
				CASE 
					WHEN ls.postaladdress IS NULL 
						THEN 
							CASE WHEN ls.homeaddress IS NULL THEN ls.companyaddress ELSE ls.homeaddress END
						ELSE ls.postaladdress
					END AS address,	
				ls.phone,
				ls.zipcode,--邮编
				CASE WHEN ls.email IS NULL THEN ls.email2 ELSE ls.email END AS email,
				CASE WHEN ls.mobile IS NULL THEN ls.mobile2 ELSE ls.mobile END AS mobile
				FROM dsj.lcaddress ls
			) t where t.rn = 1 AND t.customerno = a.scustid
		) WHERE a.custtype = '被保人';

	UPDATE shanglifeecif.individual a SET (
			Height,		--身高
			Weight,		--体重
			BMI,
			PIncome,	--个人年收入
			Ethnic,  --民族情况
			Nation, --国籍
			MaritalStat, --婚姻
			Education,--学历
			SIStatus,--社保
			IncomeSource,--收入来源
			regtype  --户籍类型
		) = (
			SELECT 
				t.stature,--身高
				t.avoirdupois,--体重
				t.bmi,
				t.yearincome,
				t.nationality,--民族
				t.nativeplace,--国籍
				t.marriage, --婚姻状况
				t.degree,   --学历
				t.socialinsuflag,--社保
				t.incomesource,--收入来源
				t.dentype
			FROM (
				SELECT
				row_number()OVER(PARTITION BY ld.insuredno ORDER BY ld.modifydate desc) rn,
				ld.insuredno,--被保人客户号
				ld.appntno,--投保人客户号
				ld.name,--被保人名称
				ld.sex,--被保人性别
				ld.birthday,--被保人出生日期
				ld.idtype,--证件类型
				ld.idno,--证件号码
				ld.marriage,--婚姻状况
				ld.occupationtype,--职业
				ld.nativeplace,--国籍
				ld.nationality,--民族
				ld.stature,--身高
				ld.avoirdupois,--体重
				round((ld.avoirdupois/POWER((ld.stature/100),2)),2) bmi,
				ld.degree,--学历
				ld.worktype,--职业 
				ld.dentype,--居民类型 
				ld.socialinsuflag,--社保情况
				ld.incomesource,--收入来源
				ld.yearincome --年收入 
			FROM dsj.lcinsured2 ld
			) t WHERE t.rn = 1 AND t.insuredno = a.scustid
		) WHERE a.custtype = '被保人';
		EXCEPTION
			WHEN HIVE_EXCEPTION THEN 
				 INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),sqlcode(),sqlerrm(),sysdate());
			WHEN Others THEN
				INSERT INTO shanglifeecif.exception_log(log_id,log_code,log_msg,log_time) VALUES (to_char(sysdate(),'yyyyMMddHHmmss'),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 ;