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
    );
    
    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.exportfrllodrugrequestitemid AS "document_id",
                                           TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD') AS "doc_date_time",
                                           (SELECT 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",
                                                           XMLELEMENT(
                                                               NAME "identify_docs",
                                                               XMLELEMENT(
                                                                   NAME "doc",
                                                                   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"
                                                                   )
                                                               )
                                                           ),
                                                           p_region AS "region"
                                                       )
                                                   ),
                                                   XMLELEMENT(
                                                       NAME "plan",
                                                       XMLFOREST(
                                                           pdd.patientdrugdemandid AS "ext_id",
                                                           erzk.codeehr AS "appl_prcd_code",
                                                           benehr.codeehr AS "benefit_code",
                                                           XMLELEMENT(
                                                               NAME "needs",
                                                               XMLELEMENT(
                                                                   NAME "need",
                                                                   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"
                                                                   )
                                                               )
                                                           )
                                                       )
                                                   )
                                            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
                       )
                   )
               )
           )
    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
$$;