DROP PROCEDURE IF EXISTS rds.sp_exportfrllorecipedataprocess;

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(
                       p_infosyscode AS "info_sys_code",
                       XMLELEMENT(
                           NAME "documents",
                           (SELECT XMLAGG
                                   (
                                       XMLELEMENT
                                       (
                                           NAME "document",
                                           XMLFOREST(
                                               efd.exportfrllorecipedataid AS "document_id",
                                               ef.actualdate AS "doc_date_time",
                                               (SELECT XMLAGG
                                                       (
                                                           XMLELEMENT(
                                                               NAME "citizen",
                                                               XMLFOREST(
                                                                   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",
                                                                   p_region AS "Region",
                                                                   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
                                                                           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
                                                                           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
                                                                           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')
                                                                           END AS "date_issue",
                                                                           CASE
                                                                               WHEN er.codeehr IS NOT NULL
                                                                                   THEN r.docissueorg
                                                                           END AS "authority"
                                                                       )
                                                                   )
                                                               )
                                                           ),
                                                           XMLELEMENT(
                                                               NAME "recipe",
                                                               XMLFOREST(
                                                                   CASE
                                                                       WHEN r.status = 1
                                                                           THEN 'true'
                                                                       ELSE 'false'
                                                                   END AS "is_delete",
                                                                   r.serial AS "recipe_serial",
                                                                   r.number AS "recipe_num",
                                                                   (r.serial ||
                                                                    CAST(r.number AS VARCHAR) ||
                                                                    CAST(EXTRACT(YEAR FROM r.recipedate) AS VARCHAR) ||
                                                                    COALESCE(mo1.mcod, mo2.mcod)) AS "ext_recipe_id",
                                                                   COALESCE(mo1.oid, mo2.oid) AS "med_org_oid",
                                                                   TRIM(r.doclastname || ' ' || r.docfirstname ||
                                                                        ' ' || COALESCE(r.docfathername, '')) AS
                                                                   "doctor_name",
                                                                   postehr.idehr AS "staff_position_code",
                                                                   REPLACE(REPLACE(r.doctorsnils, '-', ''), ' ', '') AS
                                                                   "doctor_snils",
                                                                   r.acnumber AS "medical_card",
                                                                   benehr.codeehr AS "benefit_code",
                                                                   SUBSTRING(r.mkbcode FROM 1 FOR 6) AS "mkb10_code"
                                                               )
                                                           )
                                                       )
                                                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(
                       p_infosyscode AS "info_sys_code",
                       XMLELEMENT(
                           NAME "documents",
                           (SELECT XMLAGG(
                                       XMLELEMENT(
                                           NAME "document",
                                           XMLFOREST(
                                               efd.exportfrllorecipedataid AS "document_id",
                                               ef.actualdate AS "doc_date_time"
                                           ),
                                           (SELECT XMLAGG(
                                                       XMLELEMENT(
                                                           NAME "relise",
                                                           XMLFOREST
                                                           (
                                                               CASE
                                                                   WHEN re.deleted = 1
                                                                       THEN 'true'
                                                                   ELSE 'false'
                                                               END AS "is_delete",
                                                               re.recipeelementid AS "ext_relise_id",
                                                               (r.serial || CAST(r.number AS VARCHAR) ||
                                                                CAST(EXTRACT(YEAR FROM r.recipedate) AS VARCHAR) ||
                                                                COALESCE(mo1.mcod, mo2.mcod)) AS "ext_recipe_id",
                                                               pharmehr.oid AS "med_org_oid",
                                                               r.pharmacistfio AS "staff_name",
                                                               postehr.idehr AS "staff_position_code",
                                                               REPLACE(REPLACE(r.pharmacistsnils, '-', ''), ' ', '') AS
                                                               "staff_snils",
                                                               CASE
                                                                   WHEN trn.ehrrusp08tsh954n IS NULL AND trn.ehrrus575vsf88og IS NULL
                                                                       THEN esklp.klp_code
                                                               END AS "drug_klp_code",
                                                               medprod.codeehr AS "med_equip_code",
                                                               mednutr.codeehr AS "nutrition_code",
                                                               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 AS "pack_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 AS "item_qty",
                                                               CAST(re.price AS DECIMAL(16, 2)) AS "price",
                                                               TO_CHAR(r.issuedate, 'YYYY-MM-DD') AS "release_date"
                                                           )
                                                       )
                                                   )
                                            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;
$$;