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 XMLAGG(
                       XMLELEMENT(
                           NAME "ZGLV",
                           XMLFOREST
                           (
                               '2.1' AS "VERSION",
                               TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD') AS "DATA",
                               z.year AS "YEAR",
                               z.month AS "MONTH",
                               z.code_mo AS "CODE_MO",
                               REPLACE(z.filename, 'IDD', 'DD') AS "FILENAME"
                           )
                       )
                   )
            FROM zglv z
           ),
           (SELECT XMLAGG(
                       XMLELEMENT(
                           NAME "PERS",
                           XMLFOREST(
                               p.id_pac AS "ID_PAC",
                               patient.f002smo AS "SMO",
                               TO_CHAR(patient.birthday, 'YYYY-MM-DD') AS "DR",
                               patient.enp AS "ENP",
                               TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD'),
                               CASE
                                   WHEN p.errorstring = 'Верные данные: ' || CHR(13) || CHR(10)
                                       THEN NULL
                                   ELSE p.errorstring
                               END AS "ERROR"
                           )
                       )
                   )
            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;
$$
