-- Author: Ильбарисова Е.А.
-- Change: https://jira.is-mis.ru/browse/PROMEDWEB-108505
-- Decription: Дубли при загрузке файла ПФР: Объединение пациентов


-- DROP TABLE tmp.liza_108505_PersonDouble;


DO
$$
declare
  curs refcursor;

        p_person_id bigint;
		p_person_did bigint;
		p_error_code varchar = null;
        p_error_message varchar = null;

BEGIN
  IF (SELECT dbo.GetRegion() IN (1, 5, 7, 12, 14, 18, 33, 35, 45, 52, 55, 79, 89) OR CURRENT_DATABASE() IN ('promedrelease', 'promedadygea', 'promedtest')) THEN


CREATE TABLE IF NOT EXISTS tmp.liza_108505_PersonDouble (Person_id BIGINT, Person_did BIGINT);

INSERT INTO tmp.liza_108505_PersonDouble (Person_id, Person_did)
SELECT DISTINCT p2.Person_id, p1.Person_id
FROM dbo.v_PersonState p1 
INNER JOIN LATERAL (SELECT p2.Person_id FROM dbo.v_PersonState p2 WHERE p1.Person_Snils = p2.Person_Snils AND 
REPLACE(replace(upper(p1.Person_SurNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_SurNameR),'Ё','Е'),'Й','И') AND 
REPLACE(replace(upper(p1.Person_FirNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_FirNameR),'Ё','Е'),'Й','И') AND
COALESCE(replace(replace(upper(p1.Person_SecNameR),'Ё','Е'),'Й','И'),'')  = COALESCE(REPLACE(replace(upper(p2.Person_SecNameR),'Ё','Е'),'Й','И') ,'')
 AND p1.Person_BirthDay = p2.Person_BirthDay ORDER BY p2.Person_id limit 1) p2 ON true
LEFT JOIN tmp.liza_108505_PersonDouble t ON t.Person_id = p2.Person_id AND t.Person_did = p1.Person_id
WHERE  t.Person_id IS NULL AND p1.Person_id <> p2.Person_id;

INSERT INTO tmp.liza_108505_PersonDouble (Person_id, Person_did)
SELECT DISTINCT p2.Person_id, p1.Person_id
FROM dbo.v_PersonState p1 
INNER JOIN LATERAL (SELECT p2.Person_id FROM dbo.v_PersonState p2 WHERE p1.Person_EdNum = p2.Person_EdNum AND 
REPLACE(replace(upper(p1.Person_SurNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_SurNameR),'Ё','Е'),'Й','И') AND 
REPLACE(replace(upper(p1.Person_FirNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_FirNameR),'Ё','Е'),'Й','И') AND
COALESCE(replace(replace(upper(p1.Person_SecNameR),'Ё','Е'),'Й','И'),'')  = COALESCE(REPLACE(replace(upper(p2.Person_SecNameR),'Ё','Е'),'Й','И') ,'')
 AND p1.Person_BirthDay = p2.Person_BirthDay ORDER BY p2.Person_id limit 1) p2 ON true
LEFT JOIN tmp.liza_108505_PersonDouble t ON t.Person_id = p2.Person_id AND t.Person_did = p1.Person_id
WHERE  t.Person_id IS NULL AND p1.Person_id <> p2.Person_id;

INSERT INTO tmp.liza_108505_PersonDouble (Person_id, Person_did)
SELECT DISTINCT p2.Person_id, p1.Person_id
FROM dbo.v_PersonState p1 
INNER JOIN LATERAL (SELECT p2.Person_id FROM dbo.v_PersonState p2 WHERE p1.Document_Num = p2.Document_Num AND p2.Document_Ser=p1.Document_Ser AND 
REPLACE(replace(upper(p1.Person_SurNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_SurNameR),'Ё','Е'),'Й','И') AND 
REPLACE(replace(upper(p1.Person_FirNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_FirNameR),'Ё','Е'),'Й','И') AND
COALESCE(replace(replace(upper(p1.Person_SecNameR),'Ё','Е'),'Й','И'),'')  = COALESCE(REPLACE(replace(upper(p2.Person_SecNameR),'Ё','Е'),'Й','И') ,'')
 AND p1.Person_BirthDay = p2.Person_BirthDay ORDER BY p2.Person_id limit 1) p2 ON true
LEFT JOIN tmp.liza_108505_PersonDouble t ON t.Person_id = p2.Person_id AND t.Person_did = p1.Person_id
WHERE  t.Person_id IS NULL AND p1.Person_id <> p2.Person_id;

INSERT INTO tmp.liza_108505_PersonDouble (Person_id, Person_did)
SELECT DISTINCT p2.Person_id, p1.Person_id
FROM dbo.v_PersonState p1 
INNER JOIN LATERAL (SELECT p2.Person_id FROM dbo.v_PersonState p2 WHERE p1.Polis_Num = p2.Polis_Num AND COALESCE(p2.Polis_Ser,'')=COALESCE(p1.Polis_Ser,'') and
REPLACE(replace(upper(p1.Person_SurNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_SurNameR),'Ё','Е'),'Й','И') AND 
REPLACE(replace(upper(p1.Person_FirNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_FirNameR),'Ё','Е'),'Й','И') AND
COALESCE(replace(replace(upper(p1.Person_SecNameR),'Ё','Е'),'Й','И'),'')  = COALESCE(REPLACE(replace(upper(p2.Person_SecNameR),'Ё','Е'),'Й','И') ,'')
 AND p1.Person_BirthDay = p2.Person_BirthDay ORDER BY p2.Person_id limit 1) p2 ON true
LEFT JOIN tmp.liza_108505_PersonDouble t ON t.Person_id = p2.Person_id AND t.Person_did = p1.Person_id
WHERE  t.Person_id IS NULL AND p1.Person_id <> p2.Person_id;

CREATE TABLE IF NOT EXISTS tmp.liza_108505_PersonV (Person_id BIGINT, Person_did BIGINT);

INSERT INTO tmp.liza_108505_PersonV (Person_id, Person_did)
SELECT DISTINCT p2.Person_id, p1.Person_id
FROM dbo.v_PersonState p1 
INNER JOIN LATERAL (SELECT p2.Person_id FROM dbo.v_PersonState p2 WHERE p1.Person_Snils = p2.Person_Snils AND 
REPLACE(replace(upper(p1.Person_SurNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_SurNameR),'Ё','Е'),'Й','И') AND 
REPLACE(replace(upper(p1.Person_FirNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_FirNameR),'Ё','Е'),'Й','И') AND
p1.Person_BirthDay = p2.Person_BirthDay ORDER BY p2.Person_id limit 1) p2 ON true
LEFT JOIN tmp.liza_108505_PersonV t ON t.Person_id = p2.Person_id AND t.Person_did = p1.Person_id
LEFT JOIN tmp.liza_108505_PersonDouble t2 ON t2.Person_id = p2.Person_id AND t2.Person_did = p1.Person_id
WHERE  t.Person_id IS NULL AND t2.Person_id IS NULL  AND p1.Person_id <> p2.Person_id;

INSERT INTO tmp.liza_108505_PersonV (Person_id, Person_did)
SELECT DISTINCT p2.Person_id, p1.Person_id
FROM dbo.v_PersonState p1 
INNER JOIN LATERAL (SELECT p2.Person_id FROM dbo.v_PersonState p2 WHERE p1.Person_Snils = p2.Person_Snils AND 
REPLACE(replace(upper(p1.Person_SurNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_SurNameR),'Ё','Е'),'Й','И') AND 
p1.Person_BirthDay = p2.Person_BirthDay ORDER BY p2.Person_id limit 1) p2 ON true
LEFT JOIN tmp.liza_108505_PersonV t ON t.Person_id = p2.Person_id AND t.Person_did = p1.Person_id
LEFT JOIN tmp.liza_108505_PersonDouble t2 ON t2.Person_id = p2.Person_id AND t2.Person_did = p1.Person_id
WHERE  t.Person_id IS NULL AND t2.Person_id IS NULL  AND p1.Person_id <> p2.Person_id;



INSERT INTO tmp.liza_108505_PersonV (Person_id, Person_did)
SELECT DISTINCT p2.Person_id, p1.Person_id
FROM dbo.v_PersonState p1 
INNER JOIN LATERAL (SELECT p2.Person_id FROM dbo.v_PersonState p2 WHERE p1.Person_EdNum = p2.Person_EdNum AND 
REPLACE(replace(upper(p1.Person_SurNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_SurNameR),'Ё','Е'),'Й','И') AND 
REPLACE(replace(upper(p1.Person_FirNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_FirNameR),'Ё','Е'),'Й','И') AND
p1.Person_BirthDay = p2.Person_BirthDay ORDER BY p2.Person_id limit 1) p2 ON true
LEFT JOIN tmp.liza_108505_PersonV t ON t.Person_id = p2.Person_id AND t.Person_did = p1.Person_id
LEFT JOIN tmp.liza_108505_PersonDouble t2 ON t2.Person_id = p2.Person_id AND t2.Person_did = p1.Person_id
WHERE  t.Person_id IS NULL AND t2.Person_id IS NULL  AND p1.Person_id <> p2.Person_id;

INSERT INTO tmp.liza_108505_PersonV (Person_id, Person_did)
SELECT DISTINCT p2.Person_id, p1.Person_id
FROM dbo.v_PersonState p1 
INNER JOIN LATERAL (SELECT p2.Person_id FROM dbo.v_PersonState p2 WHERE p1.Person_EdNum = p2.Person_EdNum AND 
REPLACE(replace(upper(p1.Person_SurNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_SurNameR),'Ё','Е'),'Й','И') AND 
p1.Person_BirthDay = p2.Person_BirthDay ORDER BY p2.Person_id limit 1) p2 ON true
LEFT JOIN tmp.liza_108505_PersonV t ON t.Person_id = p2.Person_id AND t.Person_did = p1.Person_id
LEFT JOIN tmp.liza_108505_PersonDouble t2 ON t2.Person_id = p2.Person_id AND t2.Person_did = p1.Person_id
WHERE  t.Person_id IS NULL AND t2.Person_id IS NULL  AND p1.Person_id <> p2.Person_id;

INSERT INTO tmp.liza_108505_PersonV (Person_id, Person_did)
SELECT DISTINCT p2.Person_id, p1.Person_id
FROM dbo.v_PersonState p1 
INNER JOIN LATERAL (SELECT p2.Person_id FROM dbo.v_PersonState p2 WHERE p1.Document_Num = p2.Document_Num AND p2.Document_Ser=p1.Document_Ser AND 
REPLACE(replace(upper(p1.Person_SurNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_SurNameR),'Ё','Е'),'Й','И') AND 
REPLACE(replace(upper(p1.Person_FirNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_FirNameR),'Ё','Е'),'Й','И') AND
p1.Person_BirthDay = p2.Person_BirthDay ORDER BY p2.Person_id limit 1) p2 ON true
LEFT JOIN tmp.liza_108505_PersonV t ON t.Person_id = p2.Person_id AND t.Person_did = p1.Person_id
LEFT JOIN tmp.liza_108505_PersonDouble t2 ON t2.Person_id = p2.Person_id AND t2.Person_did = p1.Person_id
WHERE  t.Person_id IS NULL AND t2.Person_id IS NULL  AND p1.Person_id <> p2.Person_id;

INSERT INTO tmp.liza_108505_PersonV (Person_id, Person_did)
SELECT DISTINCT p2.Person_id, p1.Person_id
FROM dbo.v_PersonState p1 
INNER JOIN LATERAL (SELECT p2.Person_id FROM dbo.v_PersonState p2 WHERE p1.Document_Num = p2.Document_Num AND p2.Document_Ser=p1.Document_Ser AND 
REPLACE(replace(upper(p1.Person_SurNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_SurNameR),'Ё','Е'),'Й','И') AND 
p1.Person_BirthDay = p2.Person_BirthDay ORDER BY p2.Person_id limit 1) p2 ON true
LEFT JOIN tmp.liza_108505_PersonV t ON t.Person_id = p2.Person_id AND t.Person_did = p1.Person_id
LEFT JOIN tmp.liza_108505_PersonDouble t2 ON t2.Person_id = p2.Person_id AND t2.Person_did = p1.Person_id
WHERE  t.Person_id IS NULL AND t2.Person_id IS NULL  AND p1.Person_id <> p2.Person_id;

INSERT INTO tmp.liza_108505_PersonV (Person_id, Person_did)
SELECT DISTINCT p2.Person_id, p1.Person_id
FROM dbo.v_PersonState p1 
INNER JOIN LATERAL (SELECT p2.Person_id FROM dbo.v_PersonState p2 WHERE p1.Polis_Num = p2.Polis_Num AND COALESCE(p2.Polis_Ser,'')=COALESCE(p1.Polis_Ser,'') and
REPLACE(replace(upper(p1.Person_SurNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_SurNameR),'Ё','Е'),'Й','И') AND 
REPLACE(replace(upper(p1.Person_FirNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_FirNameR),'Ё','Е'),'Й','И') AND
p1.Person_BirthDay = p2.Person_BirthDay ORDER BY p2.Person_id limit 1) p2 ON true
LEFT JOIN tmp.liza_108505_PersonV t ON t.Person_id = p2.Person_id AND t.Person_did = p1.Person_id
LEFT JOIN tmp.liza_108505_PersonDouble t2 ON t2.Person_id = p2.Person_id AND t2.Person_did = p1.Person_id
WHERE  t.Person_id IS NULL AND t2.Person_id IS NULL  AND p1.Person_id <> p2.Person_id;

INSERT INTO tmp.liza_108505_PersonV (Person_id, Person_did)
SELECT DISTINCT p2.Person_id, p1.Person_id
FROM dbo.v_PersonState p1 
INNER JOIN LATERAL (SELECT p2.Person_id FROM dbo.v_PersonState p2 WHERE p1.Polis_Num = p2.Polis_Num AND COALESCE(p2.Polis_Ser,'')=COALESCE(p1.Polis_Ser,'') and
REPLACE(replace(upper(p1.Person_SurNameR),'Ё','Е'),'Й','И') = replace(replace(upper(p2.Person_SurNameR),'Ё','Е'),'Й','И') AND 
p1.Person_BirthDay = p2.Person_BirthDay ORDER BY p2.Person_id limit 1) p2 ON true
LEFT JOIN tmp.liza_108505_PersonV t ON t.Person_id = p2.Person_id AND t.Person_did = p1.Person_id
LEFT JOIN tmp.liza_108505_PersonDouble t2 ON t2.Person_id = p2.Person_id AND t2.Person_did = p1.Person_id
WHERE  t.Person_id IS NULL AND t2.Person_id IS NULL AND p1.Person_id <> p2.Person_id;

  WHILE exists (SELECT 1
                FROM tmp.liza_108505_PersonDouble t
                INNER JOIN dbo.v_PersonState p ON p.Person_id = t.Person_did LIMIT 1)

       loop

	   open curs for

	   SELECT t.Person_id, t.Person_did 
	   FROM tmp.liza_108505_PersonDouble t
	   INNER JOIN dbo.v_PersonState p ON p.Person_id = t.Person_did
       LIMIT 1000;

	   loop fetch curs into p_person_id, p_person_did;
			
							select fnc.error_code, fnc.error_message
							into p_error_code, p_error_message
							from pd.xp_PersonMerge(
									person_id:=p_person_id,
									person_did:=p_person_did,
									pmuser_id:=1
							)fnc;
					
					end loop;
					close curs;
		end loop;					

  END IF;
END;
$$
