DROP TABLE IF EXISTS shanglifeecif.indrelationship_tmp;
CREATE  TABLE shanglifeecif.indrelationship_tmp(
  id string DEFAULT NULL COMMENT 'id', 
  rstype string DEFAULT NULL COMMENT '��ϵ���� ��������ż���游ĸ�����游ĸ�������������ˡ���ĸ��Ů', 
  indid1 string DEFAULT NULL COMMENT '����1', 
  scustid1 string DEFAULT NULL COMMENT '����1���οͻ���', 
  name1 string DEFAULT NULL COMMENT '����1����', 
  sex1 string DEFAULT NULL COMMENT '�����Ա�1', 
  mphone1 string DEFAULT NULL COMMENT '����1�ֻ�', 
  idcard1 string DEFAULT NULL COMMENT '����1֤������', 
  role1 string DEFAULT NULL COMMENT '����1��ɫ ���ס�ĸ�ס����ӡ�Ů�������ӡ��ɷ�', 
  indid2 string DEFAULT NULL COMMENT '����2', 
  scustid2 string DEFAULT NULL COMMENT '����2���οͻ���', 
  name2 string DEFAULT NULL COMMENT '����2����', 
  sex2 string DEFAULT NULL COMMENT '�����Ա�2', 
  mphone2 string DEFAULT NULL COMMENT '����2�ֻ�', 
  idcard2 string DEFAULT NULL COMMENT '����2֤������', 
  role2 string DEFAULT NULL COMMENT '����2��ɫ ���ס�ĸ�ס����ӡ�Ů�������ӡ��ɷ�', 
  rsstime timestamp DEFAULT NULL COMMENT '��ʼʱ��', 
  rsetime timestamp DEFAULT NULL COMMENT '����ʱ��'
)
COMMENT '�����ϵ��ʱ��'
stored as ES
with shard number 10
replication 1;

CREATE OR REPLACE PROCEDURE shanglifeecif.init_indrelationship_tmp( ) 
IS
DECLARE
	strsql string 
BEGIN
	--ɾ������
	DELETE shanglifeecif.indrelationship_tmp;
	--��������
	INSERT INTO shanglifeecif.indrelationship_tmp(
		id,
		RSType,
		SCustID1,
		Name1,
		sex1,
		IDCard1,
		Role1,
		SCustID2,
		Name2,
		sex2,
		IDCard2,
		RSSTime,
		Role2
		)
	SELECT 
		row_number()over() AS IRSID,
		max(RELATIONTOAPPNT) AS RSType,
		CUSTOMERNO AS IndID1,
		max(NAME) AS Name1,
		max(sex) AS sex1,
		max(IDNO) AS IDCard1,
		CASE max(RELATIONTOAPPNT) 
			WHEN '��Ů' 
				THEN CASE max(sex) 
					WHEN 1 THEN 'ĸ��' 
					WHEN 0 THEN '����' 
				END
		    WHEN '��ĸ' 
		    	THEN CASE max(sex) 
		    		WHEN 1 THEN '��' 
		    		WHEN 0 THEN '����' 
		    	END
		    WHEN '��ż' 
		    	THEN CASE max(sex) 
		    		WHEN 1 THEN '����' 
		    		WHEN 0 THEN '�ɷ�' 
		    	END
		    WHEN '�游ĸ�����游ĸ' 
		    	THEN CASE max(sex) 
		    		WHEN 1 THEN '(��)��Ů' 
		    		WHEN 0 THEN '(��)����' 
		    	END
		    WHEN '���������' 
		    	THEN CASE max(sex) 
		    		WHEN 1 THEN '(��)��ĸ' 
		    		WHEN 0 THEN '(��)�游' 
		    	END
			ELSE max(RELATIONTOAPPNT) 
		END AS Role1,
		INSUREDNO AS IndID2,
		max(INSUREDNAME) AS Name2,
		max(insuredsex) AS sex2,
		max(INSUREDIDNO) AS IDCard2,
		min(CUSTOMGETPOLDATE) AS RSSTime,
		CASE max(RELATIONTOAPPNT) 
			WHEN '��Ů' 
				THEN CASE max(INSUREDSEX) 
					WHEN 1 THEN '��' 
					WHEN 0 THEN '����' 
				END
		    WHEN '��ĸ' 
		    	THEN CASE max(INSUREDSEX) 
		    		WHEN 1 THEN 'ĸ��' 
		    		WHEN 0 THEN '����' 
		    	END
		    WHEN '��ż' 
		    	THEN CASE max(INSUREDSEX) 
		    		WHEN 1 THEN '����' 
		    		WHEN 0 THEN '�ɷ�' 
		    	END
		    WHEN '�游ĸ�����游ĸ' 
		    	THEN CASE max(INSUREDSEX) 
		    		WHEN 1 THEN '(��)��ĸ' 
		    		WHEN 0 THEN '(��)�游' 
		    	END
		    WHEN '���������' 
		    	THEN CASE max(INSUREDSEX) 
		    		WHEN 1 THEN '(��)��Ů' 
		    		WHEN 0 THEN '(��)����' 
		    	END
			ELSE max(RELATIONTOAPPNT) 
		END AS Role2
	FROM shanghailifeecif.POLICY_INFORMATION 
	WHERE RELATIONTOAPPNT<>'����' AND RELATIONTOAPPNT IS NOT NULL
	GROUP BY CUSTOMERNO,INSUREDNO;
	

	--������ʱ����indid1 indid2
	UPDATE shanglifeecif.indrelationship_tmp a set indid1 = 
		(SELECT b.indid FROM (select 
			indid,row_number() over(partition by scustid) rn ,SCustID
		from shanglifeecif.individual) b where a.SCustID1 = b.SCustID AND b.rn = 1);
	UPDATE shanglifeecif.indrelationship_tmp a set indid2 = 
		(SELECT b.indid FROM (select 
			indid,row_number() over(partition by scustid) rn ,SCustID
		from shanglifeecif.individual) b where a.SCustID2 = b.SCustID AND b.rn = 1);

END;

/
CREATE OR REPLACE PROCEDURE shanglifeecif.init_indrelationship( ) 
IS
DECLARE
BEGIN
	
	DELETE FROM shanglifeecif.indrelationship;
	--��������
	INSERT INTO shanglifeecif.indrelationship(
		IRSID, --'�����ϵID' 
		RSType, --'��ϵ���� ��������ż���游ĸ�����游ĸ�������������ˡ���ĸ��Ů' ,
		IndID1, --'����1' ,
		Name1, --����1����
	
		IDCard1, --����1֤������
		Role1,--����1��ɫ ���ס�ĸ�ס����ӡ�Ů�������ӡ��ɷ�
		IndID2,--����2
		Name2,--����2����	sex2,--����2�ֻ�
		IDCard2,--����2֤������
		Role2,--����2��ɫ ���ס�ĸ�ס����ӡ�Ů�������ӡ��ɷ�
		RSSTime,--��ʼʱ��
		CREATED_BY,--������
		CREATED_TIME--
		)
	SELECT 
		id,
		RSType,
		indid1,
		Name1,
		IDCard1,
		Role1,
		indid2,
		Name2,
		IDCard2,
		Role2,
		RSSTime,
		'admin',
		sysdate
	FROM shanglifeecif.indrelationship_tmp where indid1 is not null and indid2 is not null

	
END;