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
        
        select 
            ex.ObjId, ex.RegNum
        INTO TEMP_REG_NUM
        from RDS.T_EXPORT_FRLLO_RECIPE_DATA ex
            join RDS.T_EXPORT_FRLLO_RECIPE err on err.ExportFrlloRecipeID = ex.ExportFrlloRecipe
            join RDS.T_EXPORT_FRLLO_RECIPE_DATA cur on cur.ObjId = ex.ObjId and cur.ExportFrlloRecipe = "ExportId" and ("WithoutError" = 0 or cur.FrlloError is null)
            join RDS.T_EXPORT_FRLLO_RECIPE errCur on errCur.ExportFrlloRecipeID = cur.ExportFrlloRecipe
        where ex.ExportFrlloRecipe <> "ExportId" and err.ActualDate < errCur.ActualDate and err.DataType = errCur.DataType and ex.RegNum is not null
        and not exists(select * 
                           from RDS.T_EXPORT_FRLLO_RECIPE_DATA exxxx
                           join RDS.T_EXPORT_FRLLO_RECIPE erOld on erOld.ExportFrlloRecipeID = exxxx.ExportFrlloRecipe
	                       where exxxx.ExportFrlloRecipe <> ex.ExportFrlloRecipe
						       and exxxx.ObjId = ex.ObjId
                               and erOld.ActualDate > err.ActualDate and err.DataType = erOld.DataType
                               and exxxx.RegNum is not null
					           and exxxx.ExportFrlloRecipe <> "ExportId"
                        );

        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.exportfrllorecipedataid),
                                    XMLELEMENT(NAME "doc_date_time", ef.actualdate),
                                    (
                                        SELECT
                                            XMLAGG
                                            (
                                                XMLELEMENT(
                                                    NAME "citizen",
                                                    XMLFOREST(
                                                        rg.regnum AS "register_id",							
                                                        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"
                                                    ),
                                                    XMLFOREST(
                                                        XMLFOREST(
                                                            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"
                                                            ) AS "doc"
                                                        ) AS "identify_docs"
                                                    )
                                                )
                                            )
                                        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
                                        left join TEMP_REG_NUM rg on rg.ObjId = r.RecipeID
                                        WHERE r.recipeid = efd.objid
                                    ),
                                    (
                                        SELECT
                                            XMLAGG(
                                                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",
                                                        (case when r.FrlloId is not null then r.FrlloId else null end)				as "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",
                                                        cmnn.codeehr AS "drug_smnn_code",
                                                        CASE WHEN trn.ehrrusp08tsh954n IS NULL AND trn.ehrrus575vsf88og IS NULL THEN trn.namerus ELSE NULL END AS "drug_name",
                                                        medProd.codeehr AS "med_equip_code",
                                                        medNutr.codeehr AS "nutrition_code",
                                                        TO_CHAR(r.vkdate, 'YYYY-MM-DD') AS "commission_date",
                                                        r.vknumber AS "commission_num",
                                                        CASE
                                                            WHEN trn.EhrRusP08TSH954N IS NOT NULL
                                                              OR trn.EhrRus575VSF88OG IS NOT NULL
                                                            THEN (
                                                                CASE
                                                                    WHEN (r.UnitsCount::numeric(9,1) * 10)::int % 10 <> 0
                                                                    THEN r.UnitsCount::numeric(9,1)::varchar(10)
                                                                    ELSE r.UnitsCount::int::varchar(10)
                                                                END
                                                            )
                                                            ELSE (
                                                                CASE
                                                                    WHEN ((r.UnitsCount * r.ConsumerUnits)::numeric(9,1) * 10)::int % 10 <> 0
                                                                    THEN (r.UnitsCount * r.ConsumerUnits)::numeric(9,1)::varchar(10)
                                                                    ELSE (r.UnitsCount * r.ConsumerUnits)::int::varchar(10)
                                                                END
                                                            )
                                                        END AS "qty",
                                                        TO_CHAR(r.recipedate,'YYYY-MM-DD') AS "recipe_date",
                                                        CASE WHEN r.cito=1 THEN 1 WHEN r.statim=1 THEN 2 ELSE NULL END AS "recipe_priority_code",
                                                        CASE
                                                            WHEN r.Period = 0
                                                                THEN CASE WHEN medProd.CodeEHR IS NOT NULL THEN 6 ELSE 2 END
                                                            WHEN r.Period = 2
                                                                THEN CASE WHEN medProd.CodeEHR IS NOT NULL THEN 7 ELSE 4 END
                                                            WHEN r.Period = 5
                                                                THEN 1
                                                            WHEN r.Period IN (1, 3, 4)
                                                                THEN NULL
                                                        END AS "recipe_expiry_code",
                                                        CASE
                                                            WHEN r.Period = 0
                                                                THEN CASE
                                                                         WHEN medProd.CodeEHR IS NOT NULL
                                                                             THEN to_char(r.RecipeDate + interval '1 month', 'YYYY-MM-DD')
                                                                         ELSE to_char(r.RecipeDate + interval '30 day', 'YYYY-MM-DD')
                                                                     END
                                                            WHEN r.Period = 1
                                                                THEN to_char(r.RecipeDate + interval '14 day', 'YYYY-MM-DD')
                                                            WHEN r.Period = 2
                                                                THEN CASE
                                                                         WHEN medProd.CodeEHR IS NOT NULL
                                                                             THEN to_char(r.RecipeDate + interval '3 month', 'YYYY-MM-DD')
                                                                         ELSE to_char(r.RecipeDate + interval '90 day', 'YYYY-MM-DD')
                                                                     END
                                                            WHEN r.Period = 3
                                                                THEN to_char(r.RecipeDate + interval '5 day', 'YYYY-MM-DD')
                                                            WHEN r.Period = 4
                                                                THEN to_char(r.RecipeDate + interval '10 day', 'YYYY-MM-DD')
                                                            WHEN r.Period = 5
                                                                THEN to_char(r.RecipeDate + interval '15 day', 'YYYY-MM-DD')
                                                        END AS "date_expiry"
                                                    )
                                                )
                                            )
                                        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
                                        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 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",
                XMLELEMENT(NAME "info_sys_code", p_infosyscode),
                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 = true
                                                                THEN 'true'
                                                            ELSE 'false'
                                                            END AS "is_delete",
                                                        re.recipeelementid AS "ext_relise_id",
                                                        (case when re.FrlloId is not null then re.FrlloId else null end)				as "recipe_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))::TEXT, 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))::TEXT,
                                                                                    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))::TEXT, 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;
$$;