DROP PROCEDURE IF EXISTS rds.sp_exportfrllorequestdataprocess;
CREATE OR REPLACE PROCEDURE rds.sp_exportfrllorequestdataprocess(
    "ExportId" uuid,
    "InfoSysCode" VARCHAR(100),
    "Region" VARCHAR(5),
    "WithoutError" BOOLEAN,
    OUT "Result" TEXT)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_exportid     uuid         := "ExportId";
    p_infosyscode  VARCHAR(100) := "InfoSysCode";
    p_region       VARCHAR(5)   := "Region";
    p_withouterror BOOLEAN      := "WithoutError";
BEGIN

    CREATE TEMP TABLE temp_outputtable
    (
        val XML
    ) ON COMMIT DROP;

    INSERT INTO temp_outputtable(val)
    SELECT
        XMLELEMENT(
            NAME "root",
            XMLELEMENT(NAME "info_sys_code", p_infosyscode),
            XMLELEMENT(
                NAME "documents",
                (
                    SELECT
                        XMLAGG(
                            XMLELEMENT(
                                NAME "document",
                                XMLELEMENT(NAME "document_id", efd.exportfrllodrugrequestitemid),
                                XMLELEMENT(NAME "doc_date_time", TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DDTHH24:MI:SS')),
                                (
                                    SELECT
                                        XMLAGG(
                                            XMLELEMENT(
                                                NAME "citizen",
                                                XMLFOREST(
                                                    pdd.firstname AS "name",
                                                    pdd.lastname AS "surname",
                                                    pdd.fathername AS "patronymic",
                                                    TO_CHAR(pdd.birthday, 'YYYY-MM-DD') AS "birthdate",
                                                    esex.code AS "sex",
                                                    REPLACE(REPLACE(pdd.snils, '-', ''), ' ', '') AS "snils",
                                                    CASE
                                                        WHEN LENGTH(pdd.omsnumber) = 16
                                                            THEN pdd.omsnumber
                                                        END AS "policy_sn",
                                                    XMLFOREST(
                                                        XMLFOREST(
                                                            CASE
                                                                WHEN er.codeehr IS NOT NULL AND
                                                                     pdd.docnumber IS NOT NULL AND
                                                                     pdd.docissuedate IS NOT NULL
                                                                    THEN er.codeehr
                                                                ELSE NULL
                                                                END AS "doc_type",
                                                            CASE
                                                                WHEN er.codeehr IS NOT NULL AND
                                                                     pdd.docnumber IS NOT NULL AND
                                                                     pdd.docissuedate IS NOT NULL
                                                                    THEN pdd.docseries
                                                                ELSE NULL
                                                                END AS "serial",
                                                            CASE
                                                                WHEN er.codeehr IS NOT NULL AND
                                                                     pdd.docnumber IS NOT NULL AND
                                                                     pdd.docissuedate IS NOT NULL
                                                                    THEN pdd.docnumber
                                                                ELSE NULL
                                                                END AS "num",
                                                            CASE
                                                                WHEN er.codeehr IS NOT NULL AND
                                                                     pdd.docnumber IS NOT NULL AND
                                                                     pdd.docissuedate IS NOT NULL
                                                                    THEN TO_CHAR(pdd.docissuedate, 'YYYY-MM-DD')
                                                                ELSE NULL
                                                                END AS "date_issue",
                                                            CASE
                                                                WHEN er.codeehr IS NOT NULL
                                                                    THEN pdd.docissueorg
                                                                ELSE NULL
                                                                END AS "authority"
                                                        ) AS "doc"
                                                    ) AS "identify_docs",
                                                    p_region AS "region"
                                                )
                                            )
                                        )
                                    FROM rds.t_patient_drug_demand pdd
                                    LEFT JOIN vclib.t_enums esex ON esex.enumid = pdd.sex
                                    LEFT JOIN ehr.t_ehr_rus_5o5e88vhc6 er
                                    ON er.ehrrusid = pdd.ehrrus5o5e88vhc6 AND er.is_dul = 1
                                    LEFT JOIN rds.t_benefits b ON b.benefitsid = pdd.benefits
                                    LEFT JOIN ehr.t_ehr_rus_sdo8u3n5ro benehr ON benehr.ehrrusid = b.ehrrussdo8u3n5ro
                                    LEFT JOIN pharm.t_c_trn trn ON trn.ctrnid = dali.ctrn
                                    LEFT JOIN ehr.t_ehr_rus_9a9g09e8kl cmnn ON cmnn.ehrrusid = dali.ehrrus9a9g09e8kl
                                    LEFT JOIN ehr.t_ehr_rus_p08tsh954n medprod
                                    ON medprod.ehrrusid = trn.ehrrusp08tsh954n
                                    LEFT JOIN ehr.t_ehr_rus_575vsf88og mednutr
                                    ON mednutr.ehrrusid = trn.ehrrus575vsf88og
                                    WHERE pddmain.patientdrugdemandid = pdd.patientdrugdemandid
                                ),
                                (
                                    SELECT
                                        XMLAGG(
                                            XMLELEMENT(
                                                NAME "plan",
                                                XMLFOREST(
                                                    pdd.patientdrugdemandid AS "ext_id",
                                                    erzk.codeehr AS "appl_prcd_code",
                                                    benehr.codeehr AS "benefit_code",
                                                    XMLFOREST(
                                                        XMLFOREST(
                                                            cmnn.codeehr AS "drug_smnn_code",
                                                            trn.namerus AS "drug_name",
                                                            medprod.codeehr AS "med_equip_code",
                                                            mednutr.codeehr AS "nutrition_code",
                                                            CASE
                                                                WHEN medprod.codeehr IS NOT NULL OR mednutr.codeehr IS NOT NULL
                                                                    THEN pdd.requestquantity
                                                                ELSE pdd.requestquantity * dali.consumerunits
                                                                END AS "qty"
                                                        ) AS "need"
                                                    ) AS "needs"
                                                )
                                            )
                                        )
                                    FROM rds.t_patient_drug_demand pdd
                                    LEFT JOIN vclib.t_enums esex ON esex.enumid = pdd.sex
                                    LEFT JOIN ehr.t_ehr_rus_5o5e88vhc6 er ON er.ehrrusid = pdd.ehrrus5o5e88vhc6 AND er.is_dul = 1
                                    LEFT JOIN rds.t_benefits b ON b.benefitsid = pdd.benefits
                                    LEFT JOIN ehr.t_ehr_rus_sdo8u3n5ro benehr ON benehr.ehrrusid = b.ehrrussdo8u3n5ro
                                    LEFT JOIN pharm.t_c_trn trn ON trn.ctrnid = dali.ctrn
                                    LEFT JOIN ehr.t_ehr_rus_9a9g09e8kl cmnn ON cmnn.ehrrusid = dali.ehrrus9a9g09e8kl
                                    LEFT JOIN ehr.t_ehr_rus_p08tsh954n medprod ON medprod.ehrrusid = trn.ehrrusp08tsh954n
                                    LEFT JOIN ehr.t_ehr_rus_575vsf88og mednutr ON mednutr.ehrrusid = trn.ehrrus575vsf88og
                                    WHERE pddmain.patientdrugdemandid = pdd.patientdrugdemandid
                                )
                            )
                        )
                    FROM rds.t_patient_drug_demand pddmain
                    JOIN rds.t_pharmacy_drug_demand phdd ON phdd.pharmacydrugdemandid = pddmain.pharmacydrugdemand
                    JOIN rds.t_drug_demand dd ON dd.drugdemandid = phdd.drugdemand
                    JOIN rds.t_drug_application_list_item dali ON dali.drugapplicationlistitemid = dd.drugapplicationlistitem
                    JOIN rds.t_export_frllo_drug_request_item efd ON efd.objid = pddmain.patientdrugdemandid
                    JOIN rds.t_export_frllo_drug_request ef ON ef.exportfrllodrugrequestid = efd.exportfrllodrugrequest
                    JOIN rds.t_drug_request dr ON dr.drugrequestid = ef.drugrequest
                    LEFT JOIN ehr.t_ehr_rus_ej36pmtjqk erzk ON erzk.ehrrusid = dr.ehrrusej36pmtjqk
			        where ef.ExportFrlloDrugRequestID = p_exportid and ex.FileNumber = efd.FileNumber and (p_withouterror = FALSE or efd.FrlloError is null)
                )
            )
        )
    FROM rds.t_export_frllo_drug_request_item ex
    WHERE ex.exportfrllodrugrequest = p_exportid
      AND (p_withouterror = FALSE OR ex.frlloerror IS NULL);

    SELECT
        XMLELEMENT(NAME "root_list", XMLAGG(val))::TEXT
    INTO "Result"
    FROM temp_outputtable;
END
$$;