CREATE OR REPLACE PROCEDURE rds.sp_getprognosisneedsprocess(
    exportid int,
    xml xml
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_xml              xml := xml;
    p_exportid         int := exportid;
    v_ehrrussdo8u3n5ro int;
    v_ehrrusmkb308     int;
    v_ehrrus9a9g09e8kl int;
    v_ehrrusp08tsh954n int;
    v_ehrrus575vsf88og int;
    v_ehrrusej36pmtjqk int;
    v_entide           int;
    v_entidq           int;
    v_startid          int;

BEGIN
    IF (p_xml IS NOT NULL) THEN
        CREATE TEMP TABLE temp_need AS
            SELECT (XPATH('/document/document_id/text()', x))[1]::varchar(100)              AS document_id,
                   (XPATH('/document/doc_date_time/text()', x))[1]::varchar(100)::timestamp AS doc_date_time
            FROM UNNEST(XPATH('root/documents/document', p_xml)) x;
        
        
        CREATE TEMP TABLE temp_need_elements AS
            SELECT (XPATH('/document/document_id/text()', prog))[1]::varchar(100)            AS document_id,
                   (XPATH('/prognosis_needs/prognosis_needs_id/text()', t))[1]::varchar(100) AS prognosis_needs_id,
                   (XPATH('/prognosis_needs/appl_prcd_code/text()', t))[1]::varchar(64)      AS appl_prcd_code,
                   (XPATH('/prognosis_needs/region_okato5/text()', t))[1]::varchar(5)        AS region_okato5,
                   (XPATH('/prognosis_needs/benefit_code/text()', t))[1]::varchar(100)       AS benefit_code,
                   (XPATH('/prognosis_needs/mkb10_code/text()', t))[1]::varchar(100)         AS mkb10_code,
                   (XPATH('/prognosis_needs/qty_mkb10_code/text()', t))[1]::varchar(10)::int AS qty_mkb10_code,
                   (XPATH('/prognosis_needs/qty_benefit/text()', t))[1]::varchar(100)        AS qty_benefit
            FROM UNNEST(XPATH('root/documents/document', p_xml)) prog
            LEFT JOIN LATERAL UNNEST(XPATH('/document/prognosis_needs', prog)) t ON TRUE;
        
        CREATE TEMP TABLE temp_need_elements_equip AS
            SELECT (XPATH('/prognosis_needs/prognosis_needs_id/text()', prog))[1]::varchar(100) AS prognosis_needs_id,
                   (XPATH('/equip/equip_id/text()', t))[1]::varchar(100)                        AS equip_id,
                   (XPATH('/equip/equip_ext_id/text()', t))[1]::varchar(100)                    AS equip_ext_id,
                   (XPATH('/equip/drug_smnn_code/text()', t))[1]::varchar(100)                  AS drug_smnn_code,
                   (XPATH('/equip/drug_name/text()', t))[1]::varchar(1000)                      AS drug_name,
                   (XPATH('/equip/med_equip_code/text()', t))[1]::varchar(100)                  AS med_equip_code,
                   (XPATH('/equip/nutrition_code/text()', t))[1]::varchar(10)::int              AS nutrition_code,
                   (XPATH('/equip/qty_count/text()', t))[1]::varchar(100)::decimal(31, 10)      AS qty_count,
                   (XPATH('/equip/date_count/text()', t))[1]::varchar(100)::timestamp           AS date_count
            FROM UNNEST(XPATH('root/documents/document/prognosis_needs', p_xml)) prog
            LEFT JOIN LATERAL UNNEST(XPATH('/prognosis_needs/equips/equip', prog)) t ON TRUE;
        
        CREATE TEMP TABLE temp_statistics AS
            SELECT (XPATH('/prognosis_needs/prognosis_needs_id/text()', prog))[1]::varchar(100) AS prognosis_needs_id,
                   t::xml                                                                       AS statxml
            FROM UNNEST(XPATH('root/documents/document/prognosis_needs', p_xml)) prog
            LEFT JOIN LATERAL UNNEST(XPATH('/prognosis_needs/equips/statistics/statistic', prog)) t ON TRUE;
        
        UPDATE rds.t_export_frllo_needs needfrllo2
        SET docdatetime     = n.doc_date_time,
            --frllodocumentid = n.document_id,
            region_okato5   = n.region_okato5
        FROM rds.t_export_frllo_needs needfrllo
        CROSS JOIN LATERAL (
            SELECT n.doc_date_time,
                   n.document_id,
                   needelement.region_okato5
            FROM temp_need n
            JOIN temp_need_elements needelement ON n.document_id = needelement.document_id
            LIMIT 1
            ) n
        WHERE needfrllo.exportfrlloneedsid = p_exportid
          AND needfrllo2.exportfrlloneedsid = needfrllo.exportfrlloneedsid;
        
        SELECT e.entityid
        INTO v_ehrrussdo8u3n5ro
        FROM vclib.t_entities e
        WHERE e.entname = 'EHR_RUS_SDO8U3N5RO';
        
        SELECT e.entityid
        INTO v_ehrrusmkb308
        FROM vclib.t_entities e
        WHERE e.entname = 'EHR_RUS_MKB308';
        
        SELECT e.entityid
        INTO v_ehrrus9a9g09e8kl
        FROM vclib.t_entities e
        WHERE e.entname = 'EHR_RUS_9A9G09E8KL';
        
        SELECT e.entityid
        INTO v_ehrrusp08tsh954n
        FROM vclib.t_entities e
        WHERE e.entname = 'EHR_RUS_P08TSH954N';
        
        SELECT e.entityid
        INTO v_ehrrus575vsf88og
        FROM vclib.t_entities e
        WHERE e.entname = 'EHR_RUS_575VSF88OG';
        
        SELECT e.entityid
        INTO v_ehrrusej36pmtjqk
        FROM vclib.t_entities e
        WHERE e.entname = 'EHR_RUS_EJ36PMTJQK';
        
        SELECT e.entityid
        INTO v_entide
        FROM vclib.t_entities e
        WHERE e.entname = 'EXPORT_FRLLO_NEEDS_ELEMENT';
        
        SELECT e.entityid
        INTO v_entidq
        FROM vclib.t_entities e
        WHERE e.entname = 'EXPORT_FRLLO_NEEDS_EQUIP';
        
        SELECT id
        INTO v_startid
        FROM public.t_entity_ids
        WHERE entname = 'RDS.EXPORT_FRLLO_NEEDS_ELEMENT'
          AND fieldname = 'PrimaryKey';
        
        INSERT INTO
            rds.t_export_frllo_needs_element
        (exportfrlloneedselementid,
         prognosis_needs_id,
         qty_mkb10_code,
         qty_benefit,
         statistics,
         exportfrlloneeds,
         ehrrussdo8u3n5ro,
         ehrrusmkb308,
         ehrrusej36pmtjqk)
        SELECT ROW_NUMBER() OVER (ORDER BY needelements.document_id) + COALESCE(v_startid, 0),
               needelements.prognosis_needs_id,
               needelements.qty_mkb10_code,
               needelements.qty_benefit,
               public.converttexttobinaryzip(stat.statxml, ''),
               p_exportid,
               ehr.ehrrusid,
               ehr1.ehrrusid,
               ehr5.ehrrusid
        FROM temp_need_elements needelements
        LEFT JOIN temp_statistics stat ON stat.prognosis_needs_id = needelements.prognosis_needs_id
        CROSS JOIN LATERAL (SELECT prep.ehrrusid
                            FROM ehr.t_ehr_rus_sdo8u3n5ro prep
                            JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = prep.ehrrusrefbooks
                            JOIN ehr.t_ehr_reference ref
                                ON ref.ehrrusrefbooks = rrb.ehrrusrefbooksid AND
                                   ref.attributename = 'EhrRusSdo8u3n5ro' AND
                                   ref.ehrentity = v_ehrrussdo8u3n5ro AND ref.entities = v_entide
                            WHERE prep.codeehr = needelements.benefit_code
                            LIMIT 1) ehr
        CROSS JOIN LATERAL (SELECT prep.ehrrusid
                            FROM ehr.t_ehr_rus_mkb308 prep
                            JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = prep.ehrrusrefbooks
                            JOIN ehr.t_ehr_reference ref
                                ON ref.ehrrusrefbooks = rrb.ehrrusrefbooksid AND ref.attributename = 'EhrRusMkb308' AND
                                   ref.ehrentity = v_ehrrusmkb308 AND ref.entities = v_entide
                            WHERE prep.mkb_code = needelements.mkb10_code
                            LIMIT 1) ehr1
        CROSS JOIN LATERAL (SELECT prep.ehrrusid
                            FROM ehr.t_ehr_rus_ej36pmtjqk prep
                            JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = prep.ehrrusrefbooks
                            JOIN ehr.t_ehr_reference ref
                                ON ref.ehrrusrefbooks = rrb.ehrrusrefbooksid AND
                                   ref.attributename = 'EhrRusEj36pmtjqk' AND
                                   ref.ehrentity = v_ehrrus575vsf88og AND ref.entities = v_entide
                            WHERE prep.codeehr = needelements.appl_prcd_code
                            LIMIT 1) ehr5;
        
        SELECT id
        FROM public.t_entity_ids
        WHERE entname = 'RDS.EXPORT_FRLLO_NEEDS_EQUIP'
          AND fieldname = 'PrimaryKey';
        
        INSERT INTO
            rds.t_export_frllo_needs_equip
        (exportfrlloneedsequipid,
         equip_id,
         equip_ext_id,
         drug_name,
         qty_count,
         date_count,
         exportfrlloneedselement,
         ehrrus9a9g09e8kl,
         ehrrusp08tsh954n,
         ehrrus575vsf88og)
        SELECT ROW_NUMBER() OVER (ORDER BY equip.equip_id) + COALESCE(v_startid, 0)
        FROM temp_need_elements_equip equip
        JOIN rds.t_export_frllo_needs_element elem
            ON elem.prognosis_needs_id = equip.prognosis_needs_id AND elem.exportfrlloneeds = p_exportid
        CROSS JOIN LATERAL (SELECT prep.ehrrusid
                            FROM ehr.t_ehr_rus_9a9g09e8kl prep
                            JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = prep.ehrrusrefbooks
                            JOIN ehr.t_ehr_reference ref
                                ON ref.ehrrusrefbooks = rrb.ehrrusrefbooksid AND
                                   ref.attributename = 'EhrRus9a9g09e8kl' AND
                                   ref.ehrentity = v_ehrrus9a9g09e8kl AND ref.entities = v_entidq
                            WHERE prep.codeehr = equip.drug_smnn_code
                            LIMIT 1
            ) ehr2
        CROSS JOIN LATERAL (SELECT prep.ehrrusid
                            FROM ehr.t_ehr_rus_p08tsh954n prep
                            JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = prep.ehrrusrefbooks
                            JOIN ehr.t_ehr_reference ref
                                ON ref.ehrrusrefbooks = rrb.ehrrusrefbooksid AND
                                   ref.attributename = 'EhrRusP08TSH954N' AND
                                   ref.ehrentity = v_ehrrusp08tsh954n AND ref.entities = v_entidq
                            WHERE prep.codeehr = equip.med_equip_code
                            LIMIT 1
            ) ehr3
        CROSS JOIN LATERAL ( SELECT prep.ehrrusid
                             FROM ehr.t_ehr_rus_575vsf88og prep
                             JOIN ehr.t_ehr_rus_ref_books rrb ON rrb.ehrrusrefbooksid = prep.ehrrusrefbooks
                             JOIN ehr.t_ehr_reference ref
                                 ON ref.ehrrusrefbooks = rrb.ehrrusrefbooksid AND
                                    ref.attributename = 'EhrRus575VSF88OG' AND
                                    ref.ehrentity = v_ehrrus575vsf88og AND ref.entities = v_entidq
                             WHERE prep.codeehr = equip.nutrition_code
                             LIMIT 1
            ) ehr4;
    END IF;

END;
$$