CREATE OR REPLACE PROCEDURE rds.sp_dispident(
    xml xml
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    CREATE TEMP TABLE zglv AS
    SELECT (XPATH('/ZGLV/VERSION/text()', x))[1]::VARCHAR(5)   AS version,
           (XPATH('/ZGLV/DATA/text()', x))[1]::TEXT::TIMESTAMP AS data,
           (XPATH('/ZGLV/YEAR/text()', x))[1]::TEXT::INT       AS year,
           (XPATH('/ZGLV/MONTH/text()', x))[1]::TEXT::INT      AS month,
           (XPATH('/ZGLV/FILENAME/text()', x))[1]::VARCHAR(26) AS filename,
           (XPATH('/ZGLV/CODE_MO/text()', x))[1]::text::int    AS code_mo,
           CURRENT_TIMESTAMP                                   AS receiveddate,
           CURRENT_TIMESTAMP                                   AS receivedtime
    FROM UNNEST(XPATH('/PERS_LIST/ZGLV', xml)) AS x;

    CREATE TEMP TABLE pers AS
    SELECT gen_random_uuid()                                 AS itemid,
           (XPATH('/PERS/ID_PAC/text()', x))[1]::VARCHAR(36) AS id_pac,
           (XPATH('/PERS/SMO/text()', x))[1]::text::int      AS smo,
           (XPATH('/PERS/FAM/text()', x))[1]::VARCHAR(40)    AS fam,
           (XPATH('/PERS/IM/text()', x))[1]::VARCHAR(40)     AS im,
           (XPATH('/PERS/OT/text()', x))[1]::VARCHAR(40)     AS ot,
           (XPATH('/PERS/W/text()', x))[1]::text::integer    AS w,
           (XPATH('/PERS/DR/text()', x))[1]::text::timestamp AS dr,
           (XPATH('/PERS/ENP/text()', x))[1]::varchar(16)    AS enp,
           (XPATH('/PERS/DOCTYPE/text()', x))[1]::varchar(2) AS doctype,
           (XPATH('/PERS/DOCSER/text()', x))[1]::varchar(10) AS docser,
           (XPATH('/PERS/DOCNUM/text()', x))[1]::varchar(20) AS docnum,
           (XPATH('/PERS/SNILS/text()', x))[1]::varchar(14)  AS snils,
           (XPATH('/PERS/VPOLIS/text()', x))[1]::text::int   AS vpolis,
           (XPATH('/PERS/SPOLIS/text()', x))[1]::varchar(10) AS spolis,
           (XPATH('/PERS/NPOLIS/text()', x))[1]::varchar(20) AS npolis,
           z.code_mo                                         AS code_mo,
           NULL::varchar(36)                                 AS patient,
           E'Верные данные: \n'::text                        AS errorstring
    FROM UNNEST(XPATH('/PERS_LIST/PERS', xml)) AS x
    LEFT JOIN zglv z ON TRUE;

    UPDATE pers p
    SET patient = (SELECT patientid
                   FROM rds.t_patient t2
                   WHERE (t2.lastname = p.fam AND t2.firstname = p.im AND t2.birthday = p.dr AND
                          t2.polisnumber = p.npolis)
                   ORDER BY t2.doctype
                   LIMIT 1)
    WHERE p.patient IS NULL;

    UPDATE pers p
    SET patient = (SELECT patientid
                   FROM rds.t_patient t2
                   WHERE (t2.lastname = p.fam AND t2.firstname = p.im AND t2.birthday = p.dr)
                   ORDER BY t2.doctype
                   LIMIT 1)
    WHERE p.patient IS NULL;

    UPDATE pers p
    SET patient = (SELECT patientid
                   FROM rds.t_patient t2
                   WHERE (t2.lastname = p.fam AND t2.firstname = p.im AND t2.polisnumber = p.npolis)
                   ORDER BY t2.doctype
                   LIMIT 1)
    WHERE p.patient IS NULL;

    UPDATE pers p
    SET patient = (SELECT patientid
                   FROM rds.t_patient t2
                   WHERE (t2.lastname = p.fam AND t2.birthday = p.dr AND t2.polisnumber = p.npolis)
                   ORDER BY t2.doctype
                   LIMIT 1)
    WHERE p.patient IS NULL;

    ALTER TABLE pers
        ADD COLUMN errorstring TEXT NOT NULL DEFAULT 'Верные данные: ' || E'\n';

    UPDATE pers p
    SET errorstring = 'Ошибка идентификации'
    WHERE p.patient IS NULL;

    UPDATE pers p
    SET errorstring = errorstring || 'Пациент умер. Дата смерти: ' || TO_CHAR(patient.datedeath, 'YYYY-MM-DD') || E'\n'
    FROM rds.t_patient patient
    WHERE p.patient = patient.patientid
      AND patient.datedeath IS NOT NULL;

    UPDATE pers p
    SET errorstring = errorstring || 'Фамилия: ' || patient.lastname || E'\n'
    FROM rds.t_patient patient
    WHERE p.patient = patient.patientid
      AND p.fam <> patient.lastname;

    UPDATE pers p
    SET errorstring = errorstring || 'Имя: ' || patient.firstname || E'\n'
    FROM rds.t_patient patient
    WHERE p.patient = patient.patientid
      AND p.im <> patient.firstname;

    UPDATE pers p
    SET errorstring = errorstring || 'Отчество: ' || patient.fathername || E'\n'
    FROM rds.t_patient patient
    WHERE p.patient = patient.patientid
      AND (p.ot <> patient.fathername AND p.ot IS NOT NULL);

    UPDATE pers p
    SET errorstring = errorstring || 'Пол: ' || CASE WHEN patient.sex = 10301 THEN 'мужской' ELSE 'женский' END || E'\n'
    FROM rds.t_patient patient
    LEFT JOIN vclib.t_enums esex ON esex.enumtypeid = 103 AND esex.code = p.w
    WHERE esex.enumid <> patient.sex;

    UPDATE pers p
    SET errorstring = errorstring || 'Дата рождения: ' || TO_CHAR(patient.birthday, 'YYYY-MM-DD') || E'\n'
    FROM rds.t_patient patient
    WHERE p.dr <> patient.birthday;

    UPDATE pers p
    SET errorstring = errorstring || 'Серия документа: ' || patient.docseries || E'\n'
    FROM rds.t_patient patient
    LEFT JOIN vclib.t_enums doctype ON doctype.enumtypeid = 105 AND doctype.enumid = patient.doctype
    WHERE (p.doctype IS NULL AND REPLACE(p.docser, ' ', '') <> REPLACE(patient.docseries, ' ', '') AND
           p.docser IS NOT NULL)
       OR (p.doctype = doctype.alias AND REPLACE(p.docser, ' ', '') <> REPLACE(patient.docseries, ' ', '') AND
           p.docser IS NOT NULL);

    UPDATE pers p
    SET errorstring = errorstring || 'Номер документа: ' || patient.docnumber || E'\n'
    FROM rds.t_patient patient
    LEFT JOIN vclib.t_enums doctype ON doctype.enumtypeid = 105 AND doctype.enumid = patient.doctype
    WHERE (p.doctype IS NULL AND p.docnum <> patient.docnumber AND p.docnum IS NOT NULL)
       OR (p.doctype = doctype.alias AND p.docnum <> patient.docnumber AND p.docnum IS NOT NULL);

    UPDATE pers p
    SET errorstring = errorstring || 'СНИЛС: ' || patient.snils || E'\n'
    FROM rds.t_patient patient
    WHERE REPLACE(REPLACE(p.snils, ' ', ''), '-', '') <> REPLACE(REPLACE(patient.snils, ' ', ''), '-', '')
      AND p.snils IS NOT NULL;

    UPDATE pers p
    SET errorstring = errorstring || 'Тип полиса: ' || CAST(patient.policyform AS VARCHAR(2)) || E'\n'
    FROM rds.t_patient patient
    WHERE p.vpolis <> patient.policyform
      AND p.vpolis IS NOT NULL;

    UPDATE pers p
    SET errorstring = errorstring || 'Серия полиса: ' || patient.polisserial || E'\n'
    FROM rds.t_patient patient
    WHERE REPLACE(p.spolis, ' ', '') <> REPLACE(patient.polisserial, ' ', '')
      AND p.spolis IS NOT NULL
      AND p.vpolis <> 3;

    UPDATE pers p
    SET errorstring = errorstring || 'Номер полиса: ' || patient.polisnumber
    FROM rds.t_patient patient
    WHERE p.npolis <> patient.polisnumber
      AND p.vpolis <> 3;

    UPDATE pers p
    SET errorstring = errorstring || 'Номер полиса(ЕНП): ' || patient.enp
    FROM rds.t_patient patient
    WHERE p.npolis <> patient.enp
      AND p.vpolis = 3;

    SELECT ((SELECT REPLACE(filename, 'IDD', 'DD') FROM zglv)),
           '<?xml version="1.0" encoding="Windows-1251"?>' ||
           (SELECT XMLFOREST(
                           (SELECT XMLAGG(
                                           XMLELEMENT(
                                                   NAME "ZGLV",
                                                   XMLFOREST(
                                                           XMLELEMENT(NAME "VERSION", '2.1'),
                                                           XMLELEMENT(NAME "DATA", TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD')),
                                                           XMLELEMENT(NAME "YEAR", z.year),
                                                           XMLELEMENT(NAME "MONTH", z.month),
                                                           XMLELEMENT(NAME "CODE_MO", z.code_mo),
                                                           XMLELEMENT(NAME "FILENAME", REPLACE(z.filename, 'IDD', 'DD'))
                                                   )
                                           ))
                            FROM zglv z),
                           (SELECT XMLELEMENT(
                                           NAME "PERS",
                                           XMLAGG(
                                                   XMLFOREST(
                                                           XMLELEMENT(NAME "ID_PAC", p.id_pac),
                                                           XMLELEMENT(NAME "SMO", patient.f002smo),
                                                           XMLELEMENT(NAME "DR", TO_CHAR(patient.birthday, 'YYYY-MM-DD')),
                                                           XMLELEMENT(NAME "ENP", patient.enp),
                                                           XMLELEMENT(NAME "DATA_CHECK",
                                                                      TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD')),
                                                           XMLELEMENT(NAME "ERROR",
                                                                      CASE
                                                                          WHEN p.errorstring = 'Верные данные: ' || CHR(13) || CHR(10)
                                                                              THEN NULL
                                                                          ELSE p.errorstring
                                                                          END)
                                                   )
                                           )
                                   )
                            FROM pers p
                            LEFT JOIN rds.t_patient patient ON p.patient = patient.patientid
                            LEFT JOIN vclib.t_enums doctype ON doctype.enumtypeid = 105
                                AND doctype.enumid = patient.doctype
                            LEFT JOIN vclib.t_enums esex ON esex.enumid = patient.sex)
                   ))::text;
END;
$$
