CREATE OR REPLACE PROCEDURE shanglifeecif.init_insurancearrangement_main() -- �������洢����
IS    
BEGIN

	DELETE FROM shanglifeecif.insurancearrangement;
	--����policy_information ���±�����
	shanglifeecif.init_insurancearrangement_policy_information();

	--��������ֶ� ����
	shanglifeecif.update_risk_categories();
	
	
END;
/

CREATE OR REPLACE PROCEDURE shanglifeecif.init_insurancearrangement_policy_information() -- �������洢����
IS    
BEGIN

INSERT INTO shanglifeecif.insurancearrangement(
  iaid ,--'����ID', 
  policyno ,-- '���յ��� INSURANCEINFO.CONTNO', 
  pindate ,-- '������ POLICY_INFORMATION.CVALIDATE', 
  pmdate ,-- '�ձ����� POLICY_INFORMATION.ENDDATE', 
  norenewal ,-- '�������� POLICY_INFORMATION.PAYCOUNT', 
  payment ,-- '�ɷѷ�ʽ POLICY_INFORMATION.PAYINTV', 
  applicantscustid ,-- 'Ͷ���� ���οͻ��ţ����ҵ�individualid', 
  appname ,-- 'Ͷ�������� POLICY_INFORMATION.NAME', 
  appcertid ,-- 'Ͷ����֤������ POLICY_INFORMATION.IDNO', 
  insuredscustid ,-- '���������� �οͻ���', 
  insname ,-- '�������������� POLICY_INFORMATION.INSUREDNAME', 
  inscertid ,-- '����������֤������ POLICY_INFORMATION.INSUREDIDNO', 
  productid ,-- '���ִ��� POLICY_INFORMATION.RISKCODE', 
  productname,--��������
  policybelong ,-- 'ҵ������ POLICY_INFORMATION.SALECOM',  
  payendyear ,-- '�ɷ����� POLICY_INFORMATION.PAYENDYEAR', 
  policystate,--����״̬
  prem,--����
  sumprem, --�ۼƱ��ѣ�
  Risk,--����
  NPDate,--�´νɷ�����
  PADate,--Ͷ������
  pisdate,--ǩ������
  AgentOrg,--��������
  schannel,--��������
  salecomname,--ҵ����������
  POService,--��ȫ��־
  PWComp,--�б��ֹ�˾
  security ,--������ԭʼֵ
  agrmntage ,--��������
  salesperson,--ҵ��Ա��ʶ����
  SPName,--ҵ��Ա����
  created_by ,-- '������', 
  created_time -- '����ʱ��', 

) 
SELECT 
	row_number()over(),
	trim(CONTNO) as CONTNO,--policyno ,-- '���յ��� INSURANCEINFO.CONTNO', 
	trim(CVALIDATE) as CVALIDATE,--pindate ,-- '������ POLICY_INFORMATION.CVALIDATE', 
	trim(ENDDATE) as ENDDATE,--pmdate ,-- '�ձ����� POLICY_INFORMATION.ENDDATE', 
	PAYCOUNT,--norenewal ,-- '�������� POLICY_INFORMATION.PAYCOUNT', 
	trim(PAYINTV) as PAYINTV,--payment ,-- '�ɷѷ�ʽ POLICY_INFORMATION.PAYINTV', 
	trim(CUSTOMERNO) as CUSTOMERNO,--applicantid ,-- 'Ͷ���� ����POLICY_INFORMATION.CUSTOMERNO����individual���οͻ��ţ����ҵ�individualid', 
	trim(NAME) as NAME,--appname ,-- 'Ͷ�������� POLICY_INFORMATION.NAME', 
	trim(IDNO) as IDNO,--appcertid ,-- 'Ͷ����֤������ POLICY_INFORMATION.IDNO', 
	trim(insuredno) as insuredno,--insuredid ,-- '���������� insuredno', 
	trim(INSUREDNAME) as INSUREDNAME,--insname ,-- '�������������� POLICY_INFORMATION.INSUREDNAME', 
	trim(INSUREDIDNO) as INSUREDIDNO,--inscertid ,-- '����������֤������ POLICY_INFORMATION.INSUREDIDNO', 
	trim(RISKCODE) as RISKCODE,--productid ,-- '���ִ��� POLICY_INFORMATION.RISKCODE', 
	trim(RISKNAME) as RISKNAME, --productname ��������
	trim(SALECOM) as SALECOM,--policybelong ,-- 'ҵ������ POLICY_INFORMATION.SALECOM', 
	PAYENDYEAR,--payendyear ,-- '�ɷ����� POLICY_INFORMATION.PAYENDYEAR', 
	trim(APPFLAG) as APPFLAG, --����״̬
	prem,--����
	sumprem,--�ۼƱ���
	AMNT,--����
	trim(PAYTODATE) as PAYTODATE,--�´νɷ�����
	trim(polapplydate) as polapplydate,--Ͷ������
	trim(SIGNDATE) as SIGNDATE,--ǩ������
	trim(AGENTCOM) as AGENTCOM,--��������
	trim(SALECHNL) as SALECHNL,--��������
	trim(salecomname) as salecomname,--ҵ����������
	trim(PRESERVATIONFLAG) as PRESERVATIONFLAG,--��ȫ��־
	"�Ϻ������Ϻ��ֹ�˾",
	trim(security) as security, --������ԭʼֵ
	case
		when security ='����' then 42720
		when security = '��100����' then 36500
		when security = '��80����' then 29200
		when security = '70��' then 25550
		when security = '��70����' then 25550
		when security = '��65����' then 23725
		when security = '��60����' then 21900
		when security = '30��' then 10950
		when security = '20��' then 7300
		when security = '10��' then 3650
		when security = '6��' then 2190
		when security = '5��' then 1825
		when security = '1��' then 365
		when security = '180��' then 180
		when security = '6��' then 180
		when security = '90��' then 90
		when security = '3��' then 90
		when security = '1��' then 30
		when security = '30��' then 30
		when security = '15��' then 15
		when security = '7��' then 7
	end,
	trim(AGENTCODE) as AGENTCODE,--�����˴���
	trim(AGENTNAME) as AGENTNAME,--����������
	'admin',
	sysdate() 
	FROM policy_information; 

	--����INSURANCEINFO ���е���Ϣ

	UPDATE shanglifeecif.insurancearrangement a SET (
		pano ,-- 'Ͷ������ INSURANCEINFO.PRTNO',  
		--pisdate ,-- 'ǩ������ INSURANCEINFO.SIGNDATE', 
		--padate ,-- 'Ͷ������ INSURANCEINFO.POLAPPLYDATE', 
		--policystate ,--INSURANCEINFO,appflag
		--prem ,-- '�ܱ��� INSURANCEINFO.PREM', 
		--productname ,-- '��������	 INSURANCEINFO.RISKNAME'
		agentchannel -- '�������� INSURANCEINFO.SELLTYPE', 
	) = (
		select
			trim(b.PRTNO) as PRTNO,--pano ,-- 'Ͷ������ INSURANCEINFO.PRTNO',  
			--SIGNDATE,--pisdate ,-- 'ǩ������ INSURANCEINFO.SIGNDATE', 
			--POLAPPLYDATE,--padate ,-- 'Ͷ������ INSURANCEINFO.POLAPPLYDATE', 
			--appflag,--policystate ,--INSURANCEINFO,appflag
			--PREM,--prem ,-- '�ܱ��� INSURANCEINFO.PREM', 
			--RISKNAME,--productname ,-- '��������	 INSURANCEINFO.RISKNAME'
			trim(b.SELLTYPE) as SELLTYPE--agentchannel ,-- '�������� INSURANCEINFO.SELLTYPE', 
		from INSURANCEINFO b
		where b.contno  = a.policyno
	) 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.update_risk_categories() -- �������洢����
IS    
BEGIN
	--�������ִ���
	UPDATE shanglifeecif.insurancearrangement a SET (
		risk_categories_name
	) = (
		select
				kindtype
		from riskkind b WHERE a.productid = b.riskcode
	) 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.update_risk_categories() -- �������洢����
IS    
BEGIN
--�������ִ���

	UPDATE shanglifeecif.insurancearrangement a SET (
		risk_categories,
		risk_categories_name
	) = (
		
		select
				b.kindcode,
				(CASE b.kindcode 
					WHEN 'A' THEN '�����˺���' 
					WHEN 'U' THEN '���ܱ���' 
					WHEN 'R' THEN '�����' 
					WHEN 'S' THEN '�ؼ�����' 
					WHEN 'L' THEN '���ٱ���' 
					WHEN 'H' THEN '������' 
				END) AS kindname
			from lmriskapp b WHERE a.productid = b.riskcode
	
	) 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;




DROP TABLE IF EXISTS shanglifeecif.insurancearrangement_health_tmp;
CREATE TABLE shanglifeecif.insurancearrangement_health_tmp(
    id string,
    orderid string  DEFAULT NULL COMMENT '���ڷ���������',
    contno string DEFAULT NULL COMMENT '������',
  	AGENTGROUP string DEFAULT NULL COMMENT '�б��ֹ�˾',--pwcomp ,-- '�б��ֹ�˾ HEALTH_INSURANCE_LISTING.AGENTGROUPAREA Ӫҵ��HEALTH_INSURANCE_LISTING.AGENTGROUP Ӫҵ��', 
	SALECHANNELS string DEFAULT NULL COMMENT '��������',--schannel ,-- '�������� HEALTH_INSURANCE_LISTING.SALECHANNELS 1 2 3 4 5',
	AMNT string DEFAULT NULL COMMENT '�ܱ���',--risk ,-- '�ܱ��� HEALTH_INSURANCE_LISTING.AMNT(���Ի����У��˱��������������������磺POLICY_INFORMATION��INSURANCEINFO ȱ������һ����������)', 
	AGENTCODE  string DEFAULT NULL COMMENT'ҵ��Ա����'--spname ,-- 'ҵ��Ա���� HEALTH_INSURANCE_LISTING.AGENTCODE', 
) COMMENT '�����������ճб��嵥�������� ������ʱ�� '
STORED AS ES
with shard number 10
replication 1;


CREATE OR REPLACE PROCEDURE shanglifeecif.up_insurancearrangement_health_insurance_listing() -- �������洢����
IS    
BEGIN
	--�����ʱ������
	delete from shanglifeecif.insurancearrangement_health_tmp;

	--��ʼ����ʱ��
	insert into shanglifeecif.insurancearrangement_health_tmp (
		id,
		orderid,
		contno,
		agentgroup,
		SALECHANNELS,
		AMNT,
		AGENTCODE
	) select 
		row_number()over(),
		row_number()over(PARTITION BY contno),
		contno,
		agentgroup,
		SALECHANNELS,
		AMNT,
		AGENTCODE
	from HEALTH_INSURANCE_LISTING

	--������ʱ������insurancearrangement ��
	UPDATE shanglifeecif.insurancearrangement a SET (
		pwcomp ,-- '�б��ֹ�˾ HEALTH_INSURANCE_LISTING.AGENTGROUPAREA Ӫҵ��HEALTH_INSURANCE_LISTING.AGENTGROUP Ӫҵ��', 
		schannel ,-- '�������� HEALTH_INSURANCE_LISTING.SALECHANNELS 1 2 3 4 5',
		risk ,-- '�ܱ��� HEALTH_INSURANCE_LISTING.AMNT(���Ի����У��˱��������������������磺POLICY_INFORMATION��INSURANCEINFO ȱ������һ����������)', 
		spname -- 'ҵ��Ա���� HEALTH_INSURANCE_LISTING.AGENTCODE', 
	) = (
		select
			b.AGENTGROUP,--pwcomp ,-- '�б��ֹ�˾ HEALTH_INSURANCE_LISTING.AGENTGROUPAREA Ӫҵ��HEALTH_INSURANCE_LISTING.AGENTGROUP Ӫҵ��', 
			b.SALECHANNELS,--schannel ,-- '�������� HEALTH_INSURANCE_LISTING.SALECHANNELS 1 2 3 4 5',
			b.AMNT,--risk ,-- '�ܱ��� HEALTH_INSURANCE_LISTING.AMNT(���Ի����У��˱��������������������磺POLICY_INFORMATION��INSURANCEINFO ȱ������һ����������)', 
			b.AGENTCODE--spname ,-- 'ҵ��Ա���� HEALTH_INSURANCE_LISTING.AGENTCODE', 
		from shanglifeecif.insurancearrangement_health_tmp b
		where b.contno  = a.policyno
		and b.orderid = 1
	) WHERE 1=1 ;


END;
*/
/
CREATE OR REPLACE PROCEDURE shanglifeecif.up_insurancearrangement_other() -- �������洢����
IS    
BEGIN
	


	UPDATE shanglifeecif.insurancearrangement a SET (
		policytype  -- '��������', 
	) = (
	   select
	   	c.CONTTYPE
	   from (
		   	select
				row_number()over(PARTITION BY b.contno) rn,
				b.CONTTYPE,
				b.contno
			from PERSONAL_INSURANCE b
	   ) c
	   WHERE c.contno  = a.policyno and c.rn=1
	) WHERE 1=1 ;

	UPDATE shanglifeecif.insurancearrangement a SET (
		poservice	  -- '�Ƿ�ȫ', 
	) = (
	   SELECT
	   	IF(count(b.CONTNO) >0,1,0)
	   FROM AUDIT_EDORLIST b
	   where b.contno  = a.policyno
	) WHERE 1=1 ;


	UPDATE shanglifeecif.insurancearrangement a SET (
		soinsured-- '��������', 
	) = (
	   SELECT
	   		PEOPLES3
	   FROM HEALTH_GROUP_LISTING b
	   where b.contno  = a.policyno
	) WHERE 1=1 ;

END;

/
BEGIN
	shanglifeecif.init_insurancearrangement();
end 

/*
UPDATE shanglifeecif.insurancearrangement a SET (
		pindate ,-- '������ POLICY_INFORMATION.CVALIDATE', 
		  pmdate ,-- '�ձ����� POLICY_INFORMATION.ENDDATE', 
		  norenewal ,-- '�������� POLICY_INFORMATION.PAYCOUNT', 
		  payment ,-- '�ɷѷ�ʽ POLICY_INFORMATION.PAYINTV', 
		  applicantscustid ,-- 'Ͷ���� ���οͻ��ţ����ҵ�individualid', 
		  appname ,-- 'Ͷ�������� POLICY_INFORMATION.NAME', 
		  appcertid ,-- 'Ͷ����֤������ POLICY_INFORMATION.IDNO', 
		  insuredscustid ,-- '���������� �οͻ���', 
		  insname ,-- '�������������� POLICY_INFORMATION.INSUREDNAME', 
		  inscertid ,-- '����������֤������ POLICY_INFORMATION.INSUREDIDNO', 
		  productid ,-- '���ִ��� POLICY_INFORMATION.RISKCODE', 
		  productname,--��������
		  policybelong ,-- 'ҵ������ POLICY_INFORMATION.SALECOM',  
		  payendyear ,-- '�ɷ����� POLICY_INFORMATION.PAYENDYEAR', 
		  policystate,--����״̬
		  prem,--����
		  Risk,--����
		  NPDate,--�´νɷ�����
		  PADate,--Ͷ������
		  AgentOrg,--��������
		  schannel,--��������
		  PWComp--�б��ֹ�˾ 
	) = (
		select
			CVALIDATE,--pindate ,-- '������ POLICY_INFORMATION.CVALIDATE', 
			ENDDATE,--pmdate ,-- '�ձ����� POLICY_INFORMATION.ENDDATE', 
			PAYCOUNT,--norenewal ,-- '�������� POLICY_INFORMATION.PAYCOUNT', 
			PAYINTV,--payment ,-- '�ɷѷ�ʽ POLICY_INFORMATION.PAYINTV', 
			CUSTOMERNO,--applicantid ,-- 'Ͷ���� ����POLICY_INFORMATION.CUSTOMERNO����individual���οͻ��ţ����ҵ�individualid', 
			NAME,--appname ,-- 'Ͷ�������� POLICY_INFORMATION.NAME', 
			IDNO,--appcertid ,-- 'Ͷ����֤������ POLICY_INFORMATION.IDNO', 
			insuredno,--insuredid ,-- '���������� insuredno', 
			INSUREDNAME,--insname ,-- '�������������� POLICY_INFORMATION.INSUREDNAME', 
			INSUREDIDNO,--inscertid ,-- '����������֤������ POLICY_INFORMATION.INSUREDIDNO', 
			RISKCODE,--productid ,-- '���ִ��� POLICY_INFORMATION.RISKCODE', 
			RISKNAME, --productname ��������
			SALECOM,--policybelong ,-- 'ҵ������ POLICY_INFORMATION.SALECOM', 
			PAYENDYEAR,--payendyear ,-- '�ɷ����� POLICY_INFORMATION.PAYENDYEAR', 
			APPFLAG, --����״̬
			prem,--����
			AMNT,--����
			PAYTODATE,--�´νɷ�����
			polapplydate,--Ͷ������
			AGENTCOM,--��������
			SALECHNL,--��������
			"�Ϻ������Ϻ��ֹ�˾"
		from shanghailifeecif.policy_information b
		where b.contno  = a.policyno
	) WHERE 1=1 ;
*/