DROP PROCEDURE IF EXISTS rds.sp_exportfrllodataprocess;

CREATE OR REPLACE PROCEDURE rds.sp_exportfrllodataprocess(
    "ExportId" uuid,
    "InfoSysCode" varchar(100),
    "Region" varchar(5),
    "WithoutError" boolean,
    OUT "Result" text
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    v_type                     varchar(100);
    p_exportid                 uuid         := "ExportId";
    p_infosyscode              varchar(100) := "InfoSysCode";
    p_region                   varchar(5)   := "Region";
    p_withouterror             boolean      := "WithoutError";
    c_rr_patient               varchar(20)  := 'RR_PATIENT';
    c_bsk_register             varchar(20)  := 'BSK_REGISTER';
    c_palliative_register      varchar(30)  := 'PALLIATIVE_REGISTER';
    c_special_benefits_registr varchar(50)  := 'SPECIAL_BENEFITS_REGISTR';

BEGIN
    CREATE TEMP TABLE temp_outputtable
    (
        val xml
    );
    
    SELECT ee.entname
    INTO v_type
    FROM rds.t_export_frllo e
    JOIN vclib.t_entities ee ON ee.entityid = e.entities
    WHERE e.exportfrlloid = p_exportid;
    
    IF (v_type = c_rr_patient) THEN
        INSERT INTO temp_outputtable(val)
        SELECT XMLELEMENT(
                   NAME "root",
                   XMLFOREST(
                       p_infosyscode AS "info_sys_code",
                       XMLELEMENT(
                           NAME "documents",
                           (SELECT XMLAGG
                                   (
                                       XMLELEMENT
                                       (
                                           NAME "document",
                                           XMLFOREST(
                                               efd.exportfrllodataid AS "document_id",
                                               ef.actualdate AS "doc_date_time",
                                               (SELECT XMLELEMENT
                                                       (
                                                           NAME "citizen",
                                                           XMLFOREST
                                                           (
                                                               r.rrpatientid AS "ext_citizen_id",
                                                               r.firstname AS "name",
                                                               r.lastname AS "surname",
                                                               r.fathername AS "patronymic",
                                                               r.birthsurname AS "firstsurname",
                                                               TO_CHAR(r.birthday, 'YYYY-MM-DD') AS "birthdate",
                                                               esex.code AS "sex",
                                                               643 AS "citizenship",
                                                               REPLACE(REPLACE(r.snils, '-', ''), ' ', '') AS "snils",
                                                               r.enp AS "policy_sn",
                                                               XMLELEMENT(
                                                                   NAME "identify_docs",
                                                                   XMLELEMENT(
                                                                       NAME "doc",
                                                                       XMLFOREST
                                                                       (
                                                                           (CASE
                                                                                WHEN
                                                                                    er.codeehr IS NOT NULL AND
                                                                                    r.docnumber IS NOT NULL AND
                                                                                    r.docissuedate IS NOT NULL
                                                                                    THEN er.codeehr
                                                                                ELSE NULL
                                                                            END) AS "doc_type",
                                                                           (CASE
                                                                                WHEN
                                                                                    er.codeehr IS NOT NULL AND
                                                                                    r.docnumber IS NOT NULL AND
                                                                                    r.docissuedate IS NOT NULL
                                                                                    THEN r.docseries
                                                                                ELSE NULL
                                                                            END) AS "serial",
                                                                           (CASE
                                                                                WHEN
                                                                                    er.codeehr IS NOT NULL AND
                                                                                    r.docnumber IS NOT NULL AND
                                                                                    r.docissuedate IS NOT NULL
                                                                                    THEN r.docnumber
                                                                                ELSE NULL
                                                                            END) AS "num",
                                                                           (CASE
                                                                                WHEN
                                                                                    er.codeehr IS NOT NULL AND
                                                                                    r.docnumber IS NOT NULL AND
                                                                                    r.docissuedate IS NOT NULL
                                                                                    THEN TO_CHAR(r.docissuedate, 'YYYY-MM-DD')
                                                                                ELSE NULL
                                                                            END) AS "date_issue",
                                                                           (CASE WHEN er.codeehr IS NOT NULL THEN r.docissueorg ELSE NULL END) AS
                                                                           "authority"
                                                                       )
                                                                   )
                                                               ),
                                                               p_region AS "region"
                                                           )
                                                       )
                                                FROM rds.t_rr_patient r
                                                JOIN vclib.t_enums esex ON esex.enumid = r.sex
                                                LEFT JOIN ehr.t_ehr_rus_5o5e88vhc6 er
                                                    ON er.ehrrusid = r.ehrrus5o5e88vhc6 AND er.is_dul = 1
                                                WHERE r.id = efd.objid
                                               ),
                                               XMLELEMENT
                                               (
                                                   NAME "benefits",
                                                   (SELECT XMLAGG
                                                           (
                                                               XMLELEMENT(
                                                                   NAME "benefit",
                                                                   XMLFOREST(
                                                                       ehrr.codeehr AS "benefit_code",
                                                                       SUBSTR(rpb.mkb, 1, 6) AS "diagnosis",
                                                                       TO_CHAR(rpb.begindate, 'YYYY-MM-DD') AS
                                                                       "receive_date",
                                                                       TO_CHAR(rpb.enddate, 'YYYY-MM-DD') AS
                                                                       "cancel_date"
                                                                   )
                                                               )
                                                           )
                                                    FROM rds.t_rr_patient_benefit rpb
                                                    JOIN rds.t_benefits b ON b.benefitsid = rpb.benefits
                                                    JOIN ehr.t_ehr_rus_sdo8u3n5ro ehrr ON ehrr.ehrrusid = b.ehrrussdo8u3n5ro
                                                    JOIN rds.t_export_frllo_data efdb
                                                        ON efdb.objid = rpb.rrpatientbenefitid AND
                                                           efdb.exportfrllo = efd.exportfrllo AND
                                                           ex.filenumber = efdb.filenumber AND
                                                           (p_withouterror = FALSE OR efdb.frlloerror IS NULL)
                                                    WHERE rpb.rrpatient = efd.objid
                                                   )
                                               )
                                           )
                                       )
                                   )
                            FROM rds.t_export_frllo_data efd
                            JOIN rds.t_export_frllo ef ON ef.exportfrlloid = efd.exportfrllo
                            WHERE efd.type = 0
                              AND ef.exportfrlloid = p_exportid
                              AND ex.filenumber = efd.filenumber
                              AND (p_withouterror = FALSE OR efd.frlloerror IS NULL)
                           )
                       )
                   )
               )
        FROM rds.t_export_frllo_data ex
        WHERE ex.type = 0
          AND ex.exportfrllo = p_exportid
          AND (p_withouterror = FALSE OR ex.frlloerror IS NULL)
        GROUP BY ex.filenumber;
    ELSIF (v_type = c_bsk_register) THEN
        
        INSERT INTO temp_outputtable(val)
        SELECT XMLELEMENT(
                   NAME "root",
                   XMLFOREST(
                       p_infosyscode AS "info_sys_code",
                       XMLELEMENT(
                           NAME "documents",
                           (SELECT XMLAGG
                                   (
                                       XMLELEMENT
                                       (
                                           NAME "document",
                                           XMLFOREST(
                                               efd.exportfrllodataid AS "document_id",
                                               ef.actualdate AS "doc_date_time",
                                               (SELECT XMLELEMENT
                                                       (
                                                           NAME "citizen",
                                                           XMLFOREST
                                                           (
                                                               r.bskregisterid AS "ext_citizen_id",
                                                               r.firstname AS "name",
                                                               r.lastname AS "surname",
                                                               r.fathername AS "patronymic",
                                                               TO_CHAR(r.birthday, 'YYYY-MM-DD') AS "birthdate",
                                                               esex.code AS "sex",
                                                               643 AS "citizenship",
                                                               REPLACE(REPLACE(r.snils, '-', ''), ' ', '') AS "snils",
                                                               r.enp AS "policy_sn",
                                                               XMLELEMENT(
                                                                   NAME "identify_docs",
                                                                   XMLELEMENT(
                                                                       NAME "doc",
                                                                       XMLFOREST
                                                                       (
                                                                           (CASE
                                                                                WHEN
                                                                                    er.codeehr IS NOT NULL AND
                                                                                    r.docnumber IS NOT NULL AND
                                                                                    r.docissuedate IS NOT NULL
                                                                                    THEN er.codeehr
                                                                                ELSE NULL
                                                                            END) AS "doc_type",
                                                                           (CASE
                                                                                WHEN
                                                                                    er.codeehr IS NOT NULL AND
                                                                                    r.docnumber IS NOT NULL AND
                                                                                    r.docissuedate IS NOT NULL
                                                                                    THEN r.docseries
                                                                                ELSE NULL
                                                                            END) AS "serial",
                                                                           (CASE
                                                                                WHEN
                                                                                    er.codeehr IS NOT NULL AND
                                                                                    r.docnumber IS NOT NULL AND
                                                                                    r.docissuedate IS NOT NULL
                                                                                    THEN r.docnumber
                                                                                ELSE NULL
                                                                            END) AS "num",
                                                                           (CASE
                                                                                WHEN
                                                                                    er.codeehr IS NOT NULL AND
                                                                                    r.docnumber IS NOT NULL AND
                                                                                    r.docissuedate IS NOT NULL
                                                                                    THEN TO_CHAR(r.docissuedate, 'YYYY-MM-DD')
                                                                                ELSE NULL
                                                                            END) AS "date_issue",
                                                                           (CASE WHEN er.codeehr IS NOT NULL THEN r.docissueorg ELSE NULL END) AS
                                                                           "authority"
                                                                       )
                                                                   )
                                                               ),
                                                               p_region AS "region"
                                                           )
                                                       )
                                                FROM rds.t_bsk_register r
                                                JOIN vclib.t_enums esex ON esex.enumid = r.sex
                                                LEFT JOIN ehr.t_ehr_rus_5o5e88vhc6 er
                                                    ON er.ehrrusid = r.ehrrus5o5e88vhc6 AND er.is_dul = 1
                                                WHERE r.bskregisterid = efd.objid
                                               ),
                                               XMLELEMENT
                                               (
                                                   NAME "benefits",
                                                   (SELECT XMLAGG
                                                           (
                                                               XMLELEMENT(
                                                                   NAME "benefit",
                                                                   XMLFOREST(
                                                                       ehrr.codeehr AS "benefit_code",
                                                                       SUBSTR(brd.mkb, 1, 6) AS "diagnosis",
                                                                       TO_CHAR(brd.begindate, 'YYYY-MM-DD') AS
                                                                       "receive_date",
                                                                       TO_CHAR(brd.enddate, 'YYYY-MM-DD') AS
                                                                       "cancel_date"
                                                                   )
                                                               )
                                                           )
                                                    FROM rds.t_bsk_register_data brd
                                                    JOIN rds.t_benefits b ON b.benefitsid = brd.benefits
                                                    JOIN ehr.t_ehr_rus_sdo8u3n5ro ehrr ON ehrr.ehrrusid = b.ehrrussdo8u3n5ro
                                                    JOIN rds.t_export_frllo_data efdb
                                                        ON efdb.objid = brd.bskregisterdataid AND
                                                           efdb.exportfrllo = efd.exportfrllo AND
                                                           ex.filenumber = efdb.filenumber AND
                                                           (p_withouterror = FALSE OR efdb.frlloerror IS NULL)
                                                    WHERE brd.bskregister = efd.objid
                                                   )
                                               )
                                           )
                                       )
                                   )
                            FROM rds.t_export_frllo_data efd
                            JOIN rds.t_export_frllo ef ON ef.exportfrlloid = efd.exportfrllo
                            WHERE efd.type = 0
                              AND ef.exportfrlloid = p_exportid
                              AND ex.filenumber = efd.filenumber
                              AND (p_withouterror = FALSE OR efd.frlloerror IS NULL)
                           )
                       )
                   )
               )
        FROM rds.t_export_frllo_data ex
        WHERE ex.type = 0
          AND ex.exportfrllo = p_exportid
          AND (p_withouterror = FALSE OR ex.frlloerror IS NULL)
        GROUP BY ex.filenumber;
    
    ELSIF (v_type = c_palliative_register) THEN
        
        INSERT INTO temp_outputtable(val)
        SELECT XMLELEMENT(
                   NAME "root",
                   XMLFOREST(
                       p_infosyscode AS "info_sys_code",
                       XMLELEMENT(
                           NAME "documents",
                           (SELECT XMLAGG
                                   (
                                       XMLELEMENT
                                       (
                                           NAME "document",
                                           XMLFOREST(
                                               efd.exportfrllodataid AS "document_id",
                                               ef.actualdate AS "doc_date_time",
                                               (SELECT XMLELEMENT
                                                       (
                                                           NAME "citizen",
                                                           XMLFOREST
                                                           (
                                                               r.palliativeregisterid AS "ext_citizen_id",
                                                               r.firstname AS "name",
                                                               r.lastname AS "surname",
                                                               r.fathername AS "patronymic",
                                                               TO_CHAR(r.birthday, 'YYYY-MM-DD') AS "birthdate",
                                                               esex.code AS "sex",
                                                               643 AS "citizenship",
                                                               REPLACE(REPLACE(r.snils, '-', ''), ' ', '') AS "snils",
                                                               r.enp AS "policy_sn",
                                                               XMLELEMENT(
                                                                   NAME "identify_docs",
                                                                   XMLELEMENT(
                                                                       NAME "doc",
                                                                       XMLFOREST
                                                                       (
                                                                           (CASE
                                                                                WHEN
                                                                                    er.codeehr IS NOT NULL AND
                                                                                    r.docnumber IS NOT NULL AND
                                                                                    r.docissuedate IS NOT NULL
                                                                                    THEN er.codeehr
                                                                                ELSE NULL
                                                                            END) AS "doc_type",
                                                                           (CASE
                                                                                WHEN
                                                                                    er.codeehr IS NOT NULL AND
                                                                                    r.docnumber IS NOT NULL AND
                                                                                    r.docissuedate IS NOT NULL
                                                                                    THEN r.docseries
                                                                                ELSE NULL
                                                                            END) AS "serial",
                                                                           (CASE
                                                                                WHEN
                                                                                    er.codeehr IS NOT NULL AND
                                                                                    r.docnumber IS NOT NULL AND
                                                                                    r.docissuedate IS NOT NULL
                                                                                    THEN r.docnumber
                                                                                ELSE NULL
                                                                            END) AS "num",
                                                                           (CASE
                                                                                WHEN
                                                                                    er.codeehr IS NOT NULL AND
                                                                                    r.docnumber IS NOT NULL AND
                                                                                    r.docissuedate IS NOT NULL
                                                                                    THEN TO_CHAR(r.docissuedate, 'YYYY-MM-DD')
                                                                                ELSE NULL
                                                                            END) AS "date_issue",
                                                                           (CASE WHEN er.codeehr IS NOT NULL THEN r.docissueorg ELSE NULL END) AS
                                                                           "authority"
                                                                       )
                                                                   )
                                                               ),
                                                               p_region AS "region"
                                                           )
                                                       )
                                                FROM rds.t_palliative_register r
                                                JOIN vclib.t_enums esex ON esex.enumid = r.sex
                                                LEFT JOIN ehr.t_ehr_rus_5o5e88vhc6 er
                                                    ON er.ehrrusid = r.ehrrus5o5e88vhc6 AND er.is_dul = 1
                                                WHERE r.palliativeregisterid = efd.objid
                                               ),
                                               XMLELEMENT
                                               (
                                                   NAME "benefits",
                                                   (SELECT XMLAGG
                                                           (
                                                               XMLELEMENT(
                                                                   NAME "benefit",
                                                                   XMLFOREST(
                                                                       ehrr.codeehr AS "benefit_code",
                                                                       SUBSTR(rpb.mkb, 1, 6) AS "diagnosis",
                                                                       TO_CHAR(rpb.begindate, 'YYYY-MM-DD') AS
                                                                       "receive_date",
                                                                       TO_CHAR(rpb.enddate, 'YYYY-MM-DD') AS
                                                                       "cancel_date"
                                                                   )
                                                               )
                                                           )
                                                    FROM rds.t_palliative_register_data rpb
                                                    JOIN rds.t_benefits b ON b.benefitsid = rpb.benefits
                                                    JOIN ehr.t_ehr_rus_sdo8u3n5ro ehrr ON ehrr.ehrrusid = b.ehrrussdo8u3n5ro
                                                    JOIN rds.t_export_frllo_data efdb
                                                        ON efdb.objid = rpb.palliativeregisterdataid AND
                                                           efdb.exportfrllo = efd.exportfrllo AND
                                                           ex.filenumber = efdb.filenumber AND
                                                           (p_withouterror = FALSE OR efdb.frlloerror IS NULL)
                                                    WHERE rpb.palliativeregister = efd.objid
                                                   )
                                               )
                                           )
                                       )
                                   )
                            FROM rds.t_export_frllo_data efd
                            JOIN rds.t_export_frllo ef ON ef.exportfrlloid = efd.exportfrllo
                            WHERE efd.type = 0
                              AND ef.exportfrlloid = p_exportid
                              AND ex.filenumber = efd.filenumber
                              AND (p_withouterror = FALSE OR efd.frlloerror IS NULL)
                           )
                       )
                   )
               )
        FROM rds.t_export_frllo_data ex
        WHERE ex.type = 0
          AND ex.exportfrllo = p_exportid
          AND (p_withouterror = FALSE OR ex.frlloerror IS NULL)
        GROUP BY ex.filenumber;
    
    ELSIF (v_type = c_special_benefits_registr) THEN
        
        INSERT INTO temp_outputtable(val)
        SELECT XMLELEMENT(
                   NAME "root",
                   XMLFOREST(
                       p_infosyscode AS "info_sys_code",
                       XMLELEMENT(
                           NAME "documents",
                           (SELECT XMLAGG
                                   (
                                       XMLELEMENT
                                       (
                                           NAME "document",
                                           XMLFOREST(
                                               efd.exportfrllodataid AS "document_id",
                                               ef.actualdate AS "doc_date_time",
                                               (SELECT XMLELEMENT
                                                       (
                                                           NAME "citizen",
                                                           XMLFOREST
                                                           (
                                                               r.specialbenefitsregistrid AS "ext_citizen_id",
                                                               r.firstname AS "name",
                                                               r.lastname AS "surname",
                                                               r.fathername AS "patronymic",
                                                               r.birthdaylastname AS "firstsurname",
                                                               TO_CHAR(r.birthday, 'YYYY-MM-DD') AS "birthdate",
                                                               esex.code AS "sex",
                                                               643 AS "citizenship",
                                                               REPLACE(REPLACE(r.snils, '-', ''), ' ', '') AS "snils",
                                                               r.enp AS "policy_sn",
                                                               XMLELEMENT(
                                                                   NAME "identify_docs",
                                                                   XMLELEMENT(
                                                                       NAME "doc",
                                                                       XMLFOREST
                                                                       (
                                                                           (CASE
                                                                                WHEN
                                                                                    er.codeehr IS NOT NULL AND
                                                                                    r.docnumber IS NOT NULL AND
                                                                                    r.docissuedate IS NOT NULL
                                                                                    THEN er.codeehr
                                                                                ELSE NULL
                                                                            END) AS "doc_type",
                                                                           (CASE
                                                                                WHEN
                                                                                    er.codeehr IS NOT NULL AND
                                                                                    r.docnumber IS NOT NULL AND
                                                                                    r.docissuedate IS NOT NULL
                                                                                    THEN r.docseries
                                                                                ELSE NULL
                                                                            END) AS "serial",
                                                                           (CASE
                                                                                WHEN
                                                                                    er.codeehr IS NOT NULL AND
                                                                                    r.docnumber IS NOT NULL AND
                                                                                    r.docissuedate IS NOT NULL
                                                                                    THEN r.docnumber
                                                                                ELSE NULL
                                                                            END) AS "num",
                                                                           (CASE
                                                                                WHEN
                                                                                    er.codeehr IS NOT NULL AND
                                                                                    r.docnumber IS NOT NULL AND
                                                                                    r.docissuedate IS NOT NULL
                                                                                    THEN TO_CHAR(r.docissuedate, 'YYYY-MM-DD')
                                                                                ELSE NULL
                                                                            END) AS "date_issue",
                                                                           (CASE WHEN er.codeehr IS NOT NULL THEN r.docissueorg ELSE NULL END) AS
                                                                           "authority"
                                                                       )
                                                                   )
                                                               ),
                                                               p_region AS "region"
                                                           )
                                                       )
                                                FROM rds.t_special_benefits_registr r
                                                JOIN vclib.t_enums esex ON esex.enumid = r.sex
                                                LEFT JOIN ehr.t_ehr_rus_5o5e88vhc6 er
                                                    ON er.ehrrusid = r.ehrrus5o5e88vhc6 AND er.is_dul = 1
                                                WHERE r.specialbenefitsregistrid = efd.objid
                                               ),
                                               XMLELEMENT
                                               (
                                                   NAME "benefits",
                                                   (SELECT XMLAGG
                                                           (
                                                               XMLELEMENT(
                                                                   NAME "benefit",
                                                                   XMLFOREST(
                                                                       ehrr.codeehr AS "benefit_code",
                                                                       SUBSTR(r.mkb, 1, 6) AS "diagnosis",
                                                                       TO_CHAR(r.DateOfInclusion, 'YYYY-MM-DD') AS
                                                                       "receive_date",
                                                                       TO_CHAR(r.dateofdeclusion, 'YYYY-MM-DD') AS
                                                                       "cancel_date"
                                                                   )
                                                               )
                                                           )
                                                    FROM rds.t_special_benefits_registr r
                                                    JOIN rds.t_benefits b ON b.benefitsid = r.benefits
                                                    JOIN ehr.t_ehr_rus_sdo8u3n5ro ehrr ON ehrr.ehrrusid = b.ehrrussdo8u3n5ro
                                                    JOIN rds.t_export_frllo_data efdb
                                                        ON efdb.objid = r.specialbenefitsregistrid AND
                                                           efdb.exportfrllo = efd.exportfrllo AND
                                                           ex.filenumber = efdb.filenumber AND
                                                           (p_withouterror = FALSE OR efdb.frlloerror IS NULL)
                                                    WHERE r.specialbenefitsregistrid = efd.objid
                                                   )
                                               )
                                           )
                                       )
                                   )
                            FROM rds.t_export_frllo_data efd
                            JOIN rds.t_export_frllo ef ON ef.exportfrlloid = efd.exportfrllo
                            WHERE efd.type = 0
                              AND ef.exportfrlloid = p_exportid
                              AND ex.filenumber = efd.filenumber
                              AND (p_withouterror = FALSE OR efd.frlloerror IS NULL)
                           )
                       )
                   )
               )
        FROM rds.t_export_frllo_data ex
        WHERE ex.type = 0
          AND ex.exportfrllo = p_exportid
          AND (p_withouterror = FALSE OR ex.frlloerror IS NULL)
        GROUP BY ex.filenumber;
    
    END IF;
    
    SELECT XMLELEMENT(NAME "root_list", XMLAGG(val))::text
    INTO "Result"
    FROM temp_outputtable;

END;
$$;