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;

    update RDS.T_EXPORT_FRLLO_NEEDS_ELEMENT set FileNumber = 1 where ExportFrlloNeeds = "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", element.frllodocumentid),
           		                        XMLELEMENT(NAME "doc_date_time", to_char(need.docdatetime, 'YYYY-MM-DDThh:mm:ss')),
                                       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",
                                               element.mkb AS "mkb10_code",
           		                               element.qty_benefit AS "qty_benefit",
                                               element.qty_mkb10_code AS "qty_mkb10_code"
           		                                ,(SELECT XMLAGG(
                                                               XMLELEMENT(
                                                                   NAME "equip",
                                                                   XMLFOREST(
                                                                       COALESCE(equip.equip_id, equip.exportfrlloneedsequipid::text) AS "equip_id",
                                                                       equip.ExportFrlloNeedsEquipID 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_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.exportfrlloneeds;
    
SELECT XMLELEMENT(NAME "root_list", XMLAGG(val))::text
INTO "Result"
FROM temp_outputtable;
END;
$$;