CREATE OR REPLACE PROCEDURE rds.sp_exportfrllorecipedataprocess(
    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;
    v_type         int;
BEGIN
    
    CREATE TEMP TABLE temp_outputtable
    (
        val xml
    );
    
    SELECT e.datatype
    INTO v_type
    FROM rds.t_export_frllo_recipe e
    WHERE e.exportfrllorecipeid = p_exportid;
    
    IF v_type = 0 THEN
        
        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", efd.exportfrllorecipedataid),
                                               XMLELEMENT(NAME "doc_date_time", ef.actualdate),
                                               (SELECT XMLAGG
                                                       (
                                                           XMLELEMENT(
                                                               NAME "citizen",
                                                               XMLELEMENT(NAME "name", r.firstname),
                                                               XMLELEMENT(NAME "surname", r.lastname),
                                                               XMLELEMENT(NAME "patronymic", r.fathername),
                                                               XMLELEMENT(NAME "firstsurname", r.birthsurname),
                                                               XMLELEMENT(NAME "birthdate", TO_CHAR(r.birthday, 'YYYY-MM-DD')),
                                                               XMLELEMENT(NAME "sex", esex.code),
                                                               XMLELEMENT(NAME "region", p_region),
                                                               XMLELEMENT(NAME "snils",
                                                                          REPLACE(REPLACE(r.snils, '-', ''), ' ', '')),
                                                               XMLELEMENT(NAME "policy_sn", r.enp),
                                                               XMLELEMENT(
                                                                   NAME "identify_docs",
                                                                   XMLELEMENT(
                                                                       NAME "doc",
                                                                       XMLELEMENT(NAME "doc_type",
                                                                                  CASE
                                                                                      WHEN
                                                                                          er.codeehr IS NOT NULL AND
                                                                                          r.docnumber IS NOT NULL AND
                                                                                          r.docissuedate IS NOT NULL
                                                                                          THEN er.codeehr
                                                                                  END),
                                                                       XMLELEMENT(NAME "serial",
                                                                                  CASE
                                                                                      WHEN
                                                                                          er.codeehr IS NOT NULL AND
                                                                                          r.docnumber IS NOT NULL AND
                                                                                          r.docissuedate IS NOT NULL
                                                                                          THEN r.docseries
                                                                                  END),
                                                                       XMLELEMENT(NAME "num",
                                                                                  CASE
                                                                                      WHEN
                                                                                          er.codeehr IS NOT NULL AND
                                                                                          r.docnumber IS NOT NULL AND
                                                                                          r.docissuedate IS NOT NULL
                                                                                          THEN r.docnumber
                                                                                  END),
                                                                       XMLELEMENT(NAME "date_issue",
                                                                                  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')
                                                                                  END),
                                                                       XMLELEMENT(NAME "authority",
                                                                                  CASE
                                                                                      WHEN er.codeehr IS NOT NULL
                                                                                          THEN r.docissueorg
                                                                                  END)
                                                                   )
                                                               )
                                                           ),
                                                           XMLELEMENT(
                                                               NAME "recipe",
                                                               XMLELEMENT(NAME "is_delete", CASE
                                                                                                WHEN r.status = 1
                                                                                                    THEN 'true'
                                                                                                ELSE 'false'
                                                                                            END),
                                                               XMLELEMENT(NAME "recipe_serial", r.serial),
                                                               XMLELEMENT(NAME "recipe_num", r.number),
                                                               XMLELEMENT(NAME "ext_recipe_id",
                                                                          (r.serial ||
                                                                           CAST(r.number AS VARCHAR) ||
                                                                           CAST(EXTRACT(YEAR FROM r.recipedate) AS VARCHAR) ||
                                                                           COALESCE(mo1.mcod, mo2.mcod))),
                                                               XMLELEMENT(NAME "med_org_oid", COALESCE(mo1.oid, mo2.oid)),
                                                               XMLELEMENT(NAME "doctor_name",
                                                                          TRIM(r.doclastname || ' ' || r.docfirstname ||
                                                                               ' ' || COALESCE(r.docfathername, ''))),
                                                               XMLELEMENT(NAME "staff_position_code", postehr.idehr),
                                                               XMLELEMENT(NAME "doctor_snils",
                                                                          REPLACE(REPLACE(r.doctorsnils, '-', ''), ' ', '')),
                                                               XMLELEMENT(NAME "medical_card", r.acnumber),
                                                               XMLELEMENT(NAME "benefit_code", benehr.codeehr),
                                                               XMLELEMENT(NAME "mkb10_code", SUBSTRING(r.mkbcode FROM 1 FOR 6))
                                                           )
                                                       )
                                                FROM rds.t_recipe 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 = TRUE
                                                LEFT JOIN LATERAL (
                                                    SELECT mdr308.oid, lp.mcod
                                                    FROM rds.t_lpu_main lp
                                                    LEFT JOIN ehr.t_ehr_rus_mdr308 mdr308 ON mdr308.ehrrusid = lp.ehrrusmdr308dop
                                                    WHERE lp.mcod = r.lpucode
                                                    LIMIT 1
                                                    ) mo1 ON TRUE
                                                LEFT JOIN LATERAL (
                                                    SELECT mdr308.oid, lp.mcod
                                                    FROM rds.t_lpu_main lp
                                                    LEFT JOIN ehr.t_ehr_rus_mdr308 mdr308 ON mdr308.ehrrusid = lp.ehrrusmdr308dop
                                                    WHERE mdr308.oid = r.lpucode
                                                    LIMIT 1
                                                    ) mo2 ON TRUE
                                                LEFT JOIN ehr.t_ehr_rus_7pjsam6gjm postehr
                                                    ON postehr.ehrrusid = r.ehrrus7pjsam6gjm
                                                LEFT JOIN rds.t_benefits b ON b.benefitsid = r.benefit
                                                LEFT JOIN ehr.t_ehr_rus_sdo8u3n5ro benehr ON benehr.ehrrusid = b.ehrrussdo8u3n5ro
                                                WHERE r.recipeid = efd.objid
                                               )
                                           )
                                       )
                                   )
                            FROM rds.t_export_frllo_recipe_data efd
                            JOIN rds.t_export_frllo_recipe ef
                                ON ef.exportfrllorecipeid = efd.exportfrllorecipe
                            WHERE ef.exportfrllorecipeid = p_exportid
                              AND ex.filenumber = efd.filenumber
                              AND (p_withouterror = FALSE OR efd.frlloerror IS NULL)
                           )
                       )
                   )
               )
        FROM rds.t_export_frllo_recipe_data ex
        WHERE ex.exportfrllorecipe = p_exportid
          AND (p_withouterror = FALSE OR ex.frlloerror IS NULL)
        GROUP BY ex.filenumber;
    
    ELSIF (v_type = 1) THEN
        
        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",
                                           XMLELEMENT(NAME "document_id", efd.exportfrllorecipedataid),
                                           XMLELEMENT(NAME "doc_date_time", ef.actualdate),
                                           (SELECT XMLAGG(
                                                       XMLELEMENT(
                                                           NAME "relise",
                                                           XMLELEMENT(NAME "is_delete", CASE
                                                                                            WHEN re.deleted = 1
                                                                                                THEN 'true'
                                                                                            ELSE 'false'
                                                                                        END),
                                                           XMLELEMENT(NAME "ext_relise_id", re.recipeelementid),
                                                           XMLELEMENT(NAME "ext_recipe_id",
                                                                      r.serial || CAST(r.number AS VARCHAR) ||
                                                                      CAST(EXTRACT(YEAR FROM r.recipedate) AS VARCHAR) ||
                                                                      COALESCE(mo1.mcod, mo2.mcod)
                                                           ),
                                                           XMLELEMENT(NAME "med_org_oid", pharmehr.oid),
                                                           XMLELEMENT(NAME "staff_name", r.pharmacistfio),
                                                           XMLELEMENT(NAME "staff_position_code", postehr.idehr),
                                                           XMLELEMENT(NAME "staff_snils",
                                                                      REPLACE(REPLACE(r.pharmacistsnils, '-', ''), ' ', '')),
                                                           XMLELEMENT(NAME "drug_klp_code",
                                                                      CASE
                                                                          WHEN trn.ehrrusp08tsh954n IS NULL AND trn.ehrrus575vsf88og IS NULL
                                                                              THEN esklp.klp_code
                                                                      END
                                                           ),
                                                           XMLELEMENT(NAME "med_equip_code", medprod.codeehr),
                                                           XMLELEMENT(NAME "nutrition_code", mednutr.codeehr),
                                                           XMLELEMENT(NAME "pack_qty",
                                                                      CASE
                                                                          WHEN CAST(RIGHT(CAST(re.amount AS DECIMAL(9, 1)), 1) AS INT) <> 0
                                                                              THEN CAST(CAST(re.amount AS DECIMAL(9, 1)) AS VARCHAR(10))
                                                                          ELSE CAST(CAST(re.amount AS INT) AS VARCHAR(10))
                                                                      END
                                                           ),
                                                           XMLELEMENT(NAME "item_qty",
                                                                      CASE
                                                                          WHEN trn.ehrrusp08tsh954n IS NULL AND trn.ehrrus575vsf88og IS NULL
                                                                              THEN
                                                                              CASE
                                                                                  WHEN CAST(RIGHT(CAST((
                                                                                      CAST(REPLACE(number_units_2, ',', '.') AS DECIMAL(15, 3)) *
                                                                                      re.amount) AS DECIMAL(15, 3)),
                                                                                                  3) AS INT) <> 0
                                                                                      THEN CAST(CAST((
                                                                                      CAST(REPLACE(number_units_2, ',', '.') AS DECIMAL(15, 3)) *
                                                                                      re.amount) AS DECIMAL(15, 1)) AS VARCHAR(15))
                                                                                  ELSE CAST(CAST((
                                                                                      CAST(REPLACE(number_units_2, ',', '.') AS DECIMAL(15, 3)) *
                                                                                      re.amount) AS INT) AS VARCHAR(15))
                                                                              END
                                                                          ELSE
                                                                              CASE
                                                                                  WHEN CAST(RIGHT(CAST(re.amount AS DECIMAL(9, 1)), 1) AS INT) <> 0
                                                                                      THEN CAST(CAST(re.amount AS DECIMAL(9, 1)) AS VARCHAR(10))
                                                                                  ELSE CAST(CAST(re.amount AS INT) AS VARCHAR(10))
                                                                              END
                                                                      END
                                                           ),
                                                           XMLELEMENT(NAME "price", CAST(re.price AS DECIMAL(16, 2))),
                                                           XMLELEMENT(NAME "release_date", TO_CHAR(r.issuedate, 'YYYY-MM-DD'))
                                                       )
                                                   )
                                            FROM rds.t_recipe_element re
                                            LEFT JOIN rds.t_recipe r ON r.recipeid = re.recipe
                                            LEFT JOIN LATERAL (
                                                SELECT mdr308.oid, lp.mcod
                                                FROM rds.t_lpu_main lp
                                                LEFT JOIN ehr.t_ehr_rus_mdr308 mdr308 ON mdr308.ehrrusid = lp.ehrrusmdr308dop
                                                WHERE lp.mcod = r.lpucode
                                                LIMIT 1
                                                ) mo1 ON TRUE
                                            LEFT JOIN LATERAL (
                                                SELECT mdr308.oid, lp.mcod
                                                FROM rds.t_lpu_main lp
                                                LEFT JOIN ehr.t_ehr_rus_mdr308 mdr308 ON mdr308.ehrrusid = lp.ehrrusmdr308dop
                                                WHERE mdr308.oid = r.lpucode
                                                LIMIT 1
                                                ) mo2 ON TRUE
                                            LEFT JOIN rds.t_pharmacy p ON p.code = re.pharmacy
                                            LEFT JOIN ehr.t_ehr_rus_mdr308 pharmehr ON pharmehr.ehrrusid = p.ehrrusmdr308
                                            LEFT JOIN ehr.t_ehr_rus_7pjsam6gjm postehr
                                                ON postehr.ehrrusid = r.ehrrus7pjsam6gjmpharm
                                            LEFT JOIN pharm.t_bill_of_goods bg ON bg.billofgoodsid = re.billofgoods
                                            LEFT JOIN pharm.t_c_trn trn ON trn.ctrnid = bg.ctrn
                                            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
                                            LEFT JOIN ehr.t_ehr_rus_2m69i03gtd esklp ON esklp.ehrrusid = bg.ehrrus2m69i03gtd
                                            WHERE remain.recipeelementid = re.recipeelementid
                                           )
                                       )
                                   )
                            FROM rds.t_recipe_element remain
                            LEFT JOIN rds.t_recipe rmain ON rmain.recipeid = remain.recipe
                            JOIN rds.t_export_frllo_recipe_data efd ON efd.objid = remain.recipeelementid
                            JOIN rds.t_export_frllo_recipe ef ON ef.exportfrllorecipeid = efd.exportfrllorecipe
                            WHERE ef.exportfrllorecipeid = p_exportid
                              AND ex.filenumber = efd.filenumber
                              AND (p_withouterror = FALSE OR efd.frlloerror IS NULL)
                           )
                       )
                   )
               )
        FROM rds.t_export_frllo_recipe_data ex
        WHERE ex.exportfrllorecipe = 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;
$$;