-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2019-12-19
-- Description: Идентификация
-- =============================================
DROP FUNCTION IF EXISTS sp_SchetIdentify(uuid);
DROP FUNCTION IF EXISTS sp_SchetIdentify(uuid, xml);
DROP FUNCTION IF EXISTS sp_SchetIdentify(uuid, text);
DROP FUNCTION IF EXISTS sp_SchetIdentify(uuid, varchar);
CREATE OR REPLACE FUNCTION sp_SchetIdentify
(
	"FileID" uuid,
	"XmlText" varchar default null
)
RETURNS void AS
$BODY$
declare "Xml" xml;
begin
	"XmlText" := replace("XmlText", '<?xml version="1.0" encoding="Windows-1251" ?>', '');
	"XmlText" := replace("XmlText", '<?xml version="1.0" encoding="windows-1251"?>', '');
  "Xml" := "XmlText"::xml;

	--ЕНП + Номер полиса
	with Upd
	as
	(
		select i.InsuredID, i.InsuredPolicyRecordID, sz.SchetZapID
		from ISZL.T_SCHET_ZAP sz
		inner join
			ISZL.T_SCHET s
			on s.SchetID = sz.Schet
			and s.SchetFile = "FileID" 
		left join lateral
		(
			select i.InsuredID, ipr.InsuredPolicyRecordID
			from ISZL.T_INSURED_POLICY_RECORD ipr
			inner join 
				ISZL.T_INSURED i 
				on i.InsuredID = ipr.Insured
			inner join 
				ISZL.T_POLICY p 
				on p.PolicyID = ipr.Policy
			where i.ENP is not null 

			and	i.ENP = sz.ENP
			and p.Number is not null
			and p.Number = sz.NPOLIS
			order by ipr.DateBegin desc
			limit 1
		) i on true
		where sz.NPOLIS is not null 
		and sz.ENP is not null 
		and sz.InsuredPolicyRecord is null
		and i.InsuredID is not null
	)
	update ISZL.T_SCHET_ZAP sz
	set Insured = u.InsuredID
	,	InsuredPolicyRecord = u.InsuredPolicyRecordID
	from Upd u
	where u.SchetZapID = sz.SchetZapID;

	--ФИО + дата рождения + номер полиса
	with Upd
	as
	(
		select sz.SchetZapID, i.Insured, i.InsuredPolicyRecordID
		from ISZL.T_SCHET_ZAP sz
		join ISZL.T_SCHET s on s.SchetID = sz.Schet
		left join lateral
		(
			select ipr.Insured, ipr.InsuredPolicyRecordID
			from ISZL.T_INSURED_POLICY_RECORD ipr
			inner join
				ISZL.T_POLICY p
				on p.PolicyID = ipr.Policy
			where ipr.FIO is not null					
	        and  (REPLACE(LOWER(ipr.FIO), 'ё', 'е') = REPLACE(LOWER (sz.FAM) || CHR(160) || LOWER (sz.IM) || coalesce(CHR(160) || LOWER(sz.OT),  CHR(160)), 'ё', 'е')
                  or (LOWER(ipr.FIO) = LOWER (sz.FAM) || CHR(160) || LOWER (sz.IM) || coalesce(CHR(160) || LOWER(sz.OT))) )
           
			and ipr.Birthday is not null
			and ipr.Birthday = sz.DR
			and p.Number is not null
			and p.Number = sz.NPOLIS
			order by ipr.DateBegin desc
			limit 1
		) i on true
		where s.SchetFile = "FileID"
		and sz.FAM is not null
		and sz.IM is not null
		and sz.DR is not null
		and sz.NPOLIS is not null
		and	sz.InsuredPolicyRecord is null
		and i.InsuredPolicyRecordID is not null
	)
	update ISZL.T_SCHET_ZAP sz
	set Insured = u.Insured
	,	InsuredPolicyRecord = u.InsuredPolicyRecordID
	from Upd u
	where sz.SchetZapID = u.SchetZapID;
	
	-- Подбор по ФИО + номер полиса
	with Upd
	as
	(
		select sz.SchetZapID, i.Insured, i.InsuredPolicyRecordID
		from ISZL.T_SCHET_ZAP sz
		inner join
			ISZL.T_SCHET s
			on s.SchetID = sz.Schet
		left join lateral
		(
			select ipr.Insured, ipr.InsuredPolicyRecordID
			from ISZL.T_INSURED_POLICY_RECORD ipr
			inner join
				ISZL.T_POLICY p 
				on p.PolicyID = ipr.Policy
			where ipr.FIO is not null
		    and  (REPLACE(LOWER(ipr.FIO), 'ё', 'е') = REPLACE(LOWER (sz.FAM) || CHR(160) || LOWER (sz.IM) || coalesce(CHR(160) || LOWER(sz.OT),  CHR(160)), 'ё', 'е')
                  or (LOWER(ipr.FIO) = LOWER (sz.FAM) || CHR(160) || LOWER (sz.IM) || coalesce(CHR(160) || LOWER(sz.OT))) )
           
			and p.Number is not null
			and p.Number = sz.NPOLIS
			order by ipr.DateBegin desc
			limit 1
		) i on true
		where s.SchetFile = "FileID"
		and sz.FAM is not null
		and sz.IM is not null
		and sz.NPOLIS is not null
		and	sz.InsuredPolicyRecord is null
		and i.InsuredPolicyRecordID is not null
	)
	update ISZL.T_SCHET_ZAP sz
	set Insured = u.Insured
	,	InsuredPolicyRecord = u.InsuredPolicyRecordID
	from Upd u
	where u.SchetZapID = sz.SchetZapID;

	-- Подбор по ФИО + дата рождения
	with Upd
	as
	(
		select sz.SchetZapID, i.Insured, i.InsuredPolicyRecordID
		from ISZL.T_SCHET_ZAP sz
		inner join
			ISZL.T_SCHET s
			on s.SchetID = sz.Schet
		left join lateral
		(
			select ipr.Insured, ipr.InsuredPolicyRecordID
			from ISZL.T_INSURED_POLICY_RECORD ipr
			inner join
				ISZL.T_POLICY p 
				on p.PolicyID = ipr.Policy
			where ipr.FIO is not null
			  and  (REPLACE(LOWER(ipr.FIO), 'ё', 'е') = REPLACE(LOWER (sz.FAM) || CHR(160) || LOWER (sz.IM) || coalesce(CHR(160) || LOWER(sz.OT),  CHR(160)), 'ё', 'е')
                  or (LOWER(ipr.FIO) = LOWER (sz.FAM) || CHR(160) || LOWER (sz.IM) || coalesce(CHR(160) || LOWER(sz.OT))) )
           			
			and ipr.Birthday is not null
			and ipr.Birthday = sz.DR
			order by ipr.DateBegin desc
			limit 1
		) i on true
		where s.SchetFile = "FileID"
		and sz.FAM is not null
		and sz.IM is not null
		and sz.DR is not null
		and	sz.InsuredPolicyRecord is null
		and i.InsuredPolicyRecordID is not null
	)
	update ISZL.T_SCHET_ZAP sz
	set Insured = u.Insured
	,	InsuredPolicyRecord = u.InsuredPolicyRecordID
	from Upd u
	where u.SchetZapID = sz.SchetZapID;
-------------------------------------------------------------------------------------------
----после идентификации можем проанализировать загруженный счёт на наличие фактов ПИ и ДН--
-------------------------------------------------------------------------------------------
--	exec dbo.sp_SchetSearch_PM_DN_facts @FileID
end;
$BODY$
  LANGUAGE plpgsql VOLATILE;
