drop procedure if exists rds.sp_exportprognosisneedsprocess;

CREATE OR REPLACE PROCEDURE rds.sp_exportprognosisneedsprocess(
    "ExportId" integer,
    "InfoSysCode" varchar(100),
    "Region" varchar(5),
    "WithoutError" boolean,
    OUT "Result" TEXT
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_exportid     integer      := "ExportId";
    p_infosyscode  varchar(100) := "InfoSysCode";
    p_region       varchar(5)   := "Region";
    p_withouterror boolean      := "WithoutError";
    v_filenumber   integer      := 0;
BEGIN
    
    CREATE TEMP TABLE temp_outputtable
    (
        val xml
    );
    
    WHILE ((SELECT COUNT(*)
            FROM rds.t_export_frllo_needs_element
            WHERE exportfrlloneeds = p_exportid
              AND filenumber IS NULL
           ) > 0)
        LOOP
            
            WITH cte AS
                     (SELECT el.exportfrlloneedselementid AS id,
                             (ROW_NUMBER() OVER (ORDER BY el.exportfrlloneedselementid)) + v_filenumber  AS filenumber
                      FROM rds.t_export_frllo_needs_element el
                      WHERE exportfrlloneeds = p_exportid
                        AND filenumber IS NULL
                      GROUP BY el.exportfrlloneedselementid
                      LIMIT 100000
                     ),
                 update_result AS
                     (
                         UPDATE rds.t_export_frllo_needs_element r
                             SET filenumber = cte.filenumber
                             FROM cte
                             WHERE r.exportfrlloneedselementid = cte.id
                             RETURNING cte.filenumber
                     )
            SELECT MAX(filenumber)
            INTO v_filenumber
            FROM update_result;
        
        END LOOP;
    
    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", element.frllodocumentid),
           		                        XMLELEMENT(NAME "doc_date_time", to_char(need.docdatetime, 'YYYY-MM-DD')),
                                       XMLELEMENT(
                                           NAME "prognosis_needs",
                                           XMLFOREST(
                                               COALESCE(element.prognosis_needs_id,element.exportfrlloneedselementid::text) AS "prognosis_needs_id",
                                               appl.codeehr AS "appl_prcd_code",
                                               need.region_okato5 AS "region_okato5",
                                               bencat.codeehr AS "benefit_code",
                                               mkb.mkb_code AS "mkb10_code",
                                               element.qty_mkb10_code AS "qty_mkb10_code",
           		                                element.qty_benefit AS "qty_benefit"
           		                                ,(SELECT XMLAGG(
                                                               XMLELEMENT(
                                                                   NAME "equip",
                                                                   XMLFOREST(
                                                                       COALESCE(equip.equip_id, equip.exportfrlloneedsequipid::text) AS "equip_id",
                                                                       equip.equip_ext_id AS "equip_ext_id",
                                                                       meddevice.codeehr AS "drug_smnn_code",
                                                                       equip.drug_name AS "drug_name",
                                                                       medequip.codeehr AS "med_equip_code",
                                                                       specnutrition.codeehr AS "nutrition_code",
                                                                       equip.qty_confirm AS "qty_confirm"
                                                                   )
                                                               )
                                                           )
                                                    FROM rds.t_export_frllo_needs_equip equip
           		                                     	LEFT JOIN ehr.t_ehr_rus_9a9g09e8kl meddevice ON meddevice.ehrrusid = equip.ehrrus9a9g09e8kl
           		                                     	LEFT JOIN ehr.t_ehr_rus_p08tsh954n medequip ON medequip.ehrrusid = equip.ehrrusp08tsh954n
           		                                     	LEFT JOIN ehr.t_ehr_rus_575vsf88og specnutrition ON specnutrition.ehrrusid = equip.ehrrus575vsf88og
                                                    WHERE equip.exportfrlloneedselement = element.exportfrlloneedselementid
													) as "equips"
                                       )
                                   )
                               )
                           )
                    FROM rds.t_export_frllo_needs_element element
                    LEFT JOIN rds.t_export_frllo_needs need ON need.exportfrlloneedsid = element.exportfrlloneeds
                    LEFT JOIN ehr.t_ehr_rus_sdo8u3n5ro bencat ON bencat.ehrrusid = element.ehrrussdo8u3n5ro
                    LEFT JOIN ehr.t_ehr_rus_mkb308 mkb ON mkb.ehrrusid = element.ehrrusmkb308
                    LEFT JOIN ehr.t_ehr_rus_ej36pmtjqk appl ON appl.ehrrusid = element.ehrrusej36pmtjqk
                    WHERE need.exportfrlloneedsid = p_exportid
                      AND needselementmain.filenumber = element.filenumber
                      AND (p_withouterror = FALSE OR element.frlloerror IS NULL)
               )
           )
       )
FROM rds.t_export_frllo_needs_element needselementmain
WHERE needselementmain.exportfrlloneeds = p_exportid
  AND (p_withouterror = 0 OR needselementmain.frlloerror IS NULL)
GROUP BY needselementmain.filenumber;
    
    SELECT XMLELEMENT(NAME "root_list", XMLAGG(val))::text
    INTO "Result"
    FROM temp_outputtable;
END;
$$;