DROP PROCEDURE IF EXISTS rds.sp_exportfrllocertificatedataprocess;

CREATE OR REPLACE PROCEDURE rds.sp_exportfrllocertificatedataprocess(
    "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 TEMPORARY TABLE temp_outputtable
    (
        val xml
    );
    INSERT INTO temp_outputtable (val)
    SELECT XMLELEMENT(
               NAME "root",
               XMLFOREST(
                   XMLELEMENT(NAME "info_sys_code", p_infosyscode),
                   XMLELEMENT(
                       NAME "documents",
                       (SELECT XMLAGG
                               (
                                   XMLELEMENT
                                   (
                                       NAME "document",
                                       XMLFOREST(
                                           XMLELEMENT(NAME "document_id", statusmain.electroniccertificatestatusid),
                                           XMLELEMENT(NAME "doc_date_time", cer.createdate),
                                           (SELECT XMLELEMENT
                                                   (
                                                       NAME "cert",
                                                       XMLFOREST
                                                       (
                                                           FALSE AS "is_delete", --2.3.1
                                                           r.frlloid AS "recipe_id", --2.3.2
                                                           cmnn.codeehr AS "drug_smnn_code", --2.3.3
                                                           medprod.codeehr AS "med_equip_code", --2.3.4
                                                           mednutr.codeehr AS "nutrition_code", --2.3.5
                                                           mednutr.codeehr AS "nutrition_code" --2.3.6.1
                                                       ,XMLELEMENT
                                                       (
                                                           NAME "releases",
                                                           (SELECT XMLAGG
                                                                   (
                                                                       XMLELEMENT(
                                                                           NAME "release",
                                                                           XMLFOREST(
                                                                               re.frlloid AS "release_id",
                                                                               re.amount * re.price AS
                                                                               "sum_release_cert"
                                                                           )
                                                                       )
                                                                   )
                                                            FROM rds.t_recipe_element re
                                                            WHERE re.recipe = r.recipeid
                                                           )
                                                       ),
                                                       XMLFOREST(
                                                           stat.certificatenumber AS "ext_cert_id", --2.3.7
                                                       --stat.cert_id                                              as "cert_id",                   --2.3.8
                                                           stat.certificatenumber AS "cert_num", --2.3.9
                                                           app.limitprice AS "unit_cost", --2.3.10
                                                           r.consumerunits * r.unitscount AS "item_qty", --2.3.11
                                                           (SELECT COALESCE(SUM(re.amount * re.price), 0)
                                                            FROM rds.t_recipe_element re
                                                            WHERE re.recipe = r.recipeid
                                                           ) AS "sum_cert", --2.3.12
                                                           CASE
                                                               WHEN stat.currentstatus = 6 THEN FALSE
                                                               ELSE TRUE
                                                           END AS "is_cancel", --2.3.13
                                                           TO_CHAR(stat.startdate, 'YYYY-MM-DD') AS
                                                           "start_date", --2.3.14
                                                           TO_CHAR(stat.enddate, 'YYYY-MM-DD') AS "end_date" --2.3.15
                                                       )
                                                      )
                                                   )
                                            FROM rds.t_electronic_certificate_status stat
                                            JOIN rds.t_electronic_certificate_application app
                                                ON app.electroniccertificateapplicationid =
                                                   stat.electroniccertificateapplication
                                            LEFT JOIN rds.t_recipe r ON r.recipeid = app.recipe
                                            LEFT JOIN pharm.t_c_trn trn ON trn.ctrnid = r.ctrn
                                            LEFT JOIN ehr.t_ehr_rus_9a9g09e8kl cmnn ON cmnn.ehrrusid = r.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 stat.electroniccertificatestatusid =
                                                  statusmain.electroniccertificatestatusid
                                           )
                                       )
                                   )
                               )
                        FROM rds.t_electronic_certificate_status statusmain
                        JOIN rds.t_export_frllo_ec_data efe ON efe.objid = statusMain.electroniccertificatestatusid
                        WHERE efe.exportfrlloelectroniccertificate = p_exportid
                          AND ex.filenumber = efe.filenumber
                          AND (p_withouterror = FALSE OR efe.frlloerror IS NULL)
                       )
                   )
               )
           )
    FROM rds.t_export_frllo_ec_data ex
    JOIN rds.t_export_frllo_electronic_certificate cer
        ON cer.exportfrlloelectroniccertificateid = ex.exportfrlloelectroniccertificate
    WHERE ex.exportfrlloelectroniccertificate = p_exportid
      AND (p_withouterror = FALSE OR ex.frlloerror IS NULL)
    GROUP BY ex.filenumber;

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