Untitled

-- DROP FUNCTION public.fzi_migration_partner_vs_ref_partner(varchar, int8, int8, varchar, varchar, varchar);

CREATE OR REPLACE FUNCTION public.fzi_migration_partner_vs_ref_partner(character varying, bigint, bigint, character varying, character varying, character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
	pSessionId		ALIAS FOR $1;
	pTenantId		ALIAS FOR $2;
	pUserId			ALIAS FOR $3;
	pDatetime		ALIAS FOR $4;
	pTimestampFrom 	ALIAS FOR $5;
	pTimestampTo 	ALIAS FOR $6;

	vResult         RECORD;
	vDatatype		varchar := 'DATA_VS_REF_PARTNER';

begin
	-- ADD
	with tobe_inserted_vs_ref_partner as (
		select pTenantId as tenant_id,
		A.company_id as partner_code,
		A.ref_id as ref_id,
		pDatetime as create_datetime,
		pUserId as create_user_id,
		pDatetime as update_datetime,
		pUserId as update_user_id,
		cast('0' as bigint) as version
		from zicare.m_company A
		where not exists (
			select 1
			from zi_migration B
			where B.data_type = vDatatype
		)
		AND (
        	A.modified_date BETWEEN pTimestampFrom::timestamp AND pTimestampTo::timestamp OR A.created_date BETWEEN pTimestampFrom::timestamp AND pTimestampTo::timestamp
        )
	),
	inserted_vs_ref_partner as (
		insert into public.zi_partner_vs_ref_partner(
			tenant_id,
			partner_id,
			partner_code,
			ref_partner_id,
			create_datetime,
			create_user_id,
			update_datetime,
			update_user_id,
			"version"
		)
		select A.tenant_id,
		B.partner_id,
		A.partner_code,
		C.ref_partner_id,
		A.create_datetime,
		A.create_user_id,
		A.update_datetime,
		A.update_user_id,
		A.version
		from tobe_inserted_vs_ref_partner A
		join public.m_partner B on B.partner_code = A.partner_code and B.tenant_id = A.tenant_id
		join public.zi_ref_partner C on C.ref_id = A.ref_id and C.tenant_id = A.tenant_id
		returning *
	),
	inserted_zi_migration as (
		INSERT INTO zi_migration (
	    	id_data,
	    	session_id,
	    	data_type,
	    	data_unique,
	    	migration_type,
	        data_change,
	        flg_last,
	        remark,
	        create_user_id,
	        create_datetime,
	        update_user_id,
	        update_datetime,
	        "version"
		) 
		SELECT nextval('zi_migration_seq'::regclass),
			pSessionId,
			vDataType,
			A.partner_code as data_unique,
			'ADD',
			A.ref_partner_id as data_change,
			'Y',
			'',
			pUserId,
			pDatetime,
			pUserId,
			pDatetime,
			0
		FROM inserted_vs_ref_partner A
		returning *
	)
	
	SELECT
		(SELECT COUNT(1) FROM tobe_inserted_vs_ref_partner) AS  tobe_inserted_vs_ref_partner,
		(SELECT COUNT(1) FROM inserted_vs_ref_partner) AS inserted_vs_ref_partner,
		(SELECT COUNT(1) FROM inserted_zi_migration) AS inserted_zi_migration
    INTO vResult;
   
   --EDIT
   with tobe_updated_vs_ref_partner as (
		select pTenantId as tenant_id,
		A.company_id as partner_code,
		A.ref_id as ref_id,
		pDatetime as create_datetime,
		pUserId as create_user_id,
		pDatetime as update_datetime,
		pUserId as update_user_id,
		cast('0' as bigint) as version,
		A.ref_id as data_change
		from zicare.m_company A
		where exists (
			select 1
			from zi_migration B
			where B.data_type = vDatatype
		)
		AND (
        	A.modified_date BETWEEN pTimestampFrom::timestamp AND pTimestampTo::timestamp OR A.created_date BETWEEN pTimestampFrom::timestamp AND pTimestampTo::timestamp
        )
   ),
   updated_vs_ref_partner as (
   		update public.zi_partner_vs_ref_partner as A
   		set ref_partner_id = C.ref_partner_id,
   		update_datetime = B.update_datetime,
   		update_user_id = B.update_user_id,
   		version = B.version + 1
   		from tobe_updated_vs_ref_partner B
   		join public.zi_ref_partner C on C.ref_id = B.ref_id and C.tenant_id = B.tenant_id 
   		where A.partner_code = B.partner_code
   			and A.tenant_id = B.tenant_id
   		returning A.*, B.data_change
   ),
   inserted_new_zi_migration as (
   		INSERT INTO zi_migration (
	    	id_data,
	    	session_id,
	    	data_type,
	    	data_unique,
	    	migration_type,
	        data_change,
	        flg_last,
	        remark,
	        create_user_id,
	        create_datetime,
	        update_user_id,
	        update_datetime,
	        "version"
		) 
		SELECT nextval('zi_migration_seq'::regclass),
			pSessionId,
			vDataType,
			A.partner_code as data_unique,
			'EDIT',
			A.data_change as data_change,
			'Y',
			'',
			pUserId,
			pDatetime,
			pUserId,
			pDatetime,
			A."version"
		FROM updated_vs_ref_partner A
		returning *
   ),
   updated_zi_migration as (
   		UPDATE zi_migration AS A
		SET flg_last = 'N'
		FROM inserted_new_zi_migration B
		WHERE A.data_type = B.data_type
			AND A.data_unique = B.data_unique
			AND A.id_data != B.id_data
		RETURNING *
   )
   
   	SELECT
		(SELECT COUNT(1) FROM tobe_updated_vs_ref_partner) AS  tobe_updated_vs_ref_partner,
		(SELECT COUNT(1) FROM updated_vs_ref_partner) AS updated_vs_ref_partner,
		(SELECT COUNT(1) FROM inserted_new_zi_migration) AS inserted_new_zi_migration,
		(SELECT COUNT(1) FROM updated_zi_migration) AS updated_zi_migration
    INTO vResult;

	
END;
$function$
;