authen.public

Parameters

Name	Type	Mode
p_batch_id	integer	IN
p_user_name	text	IN (DEFAULT NULL)

Definition

/*
    Function: public.sp_get_run_batch_report(integer, text)

Purpose:
    - Build report generation payload JSON for batch report.
    - Used by report-engine endpoint:
      /v2/pms/report/getList

Last fix note:
    - 2026-06-10
    - Previous error #1:
      42883: function fn_get_login_token(text) does not exist

Root cause:
      fn_get_login_token actually requires 2 parameters:
          public.fn_get_login_token(user_name text, prop_id integer)

Wrong old call:
          fn_get_login_token(user_name)

Fixed call:
          public.fn_get_login_token(p_user_name, v_batch.prop_id)

- Previous error #2:
      42702: column reference "batch_id" is ambiguous

Root cause:
      Parameter name "batch_id" conflicted with column names such as:
          bi.batch_id

Fix:
      Renamed parameters to:
          p_batch_id
          p_user_name

Renamed local variables to v_* pattern:
          v_batch, v_item, v_access_token, etc.

Important:
    - Do not rename p_batch_id back to batch_id.
    - Do not call fn_get_login_token with only one argument.
    - This function returns JSON only. It does not generate PDF or print directly.
*/
DECLARE 
    v_batch         record;
    v_item          record;
    v_sys_date      date;
    v_report_params json;
    v_err_message   text;
    v_time_start    timestamp;
    v_execute_time  numeric(8,3);
    v_access_token  text;
    v_reports_json  jsonb := '[]'::jsonb;
    v_result_json   json;
    v_report_index  integer := 0;
BEGIN
    /*
        Load batch report header and property info.

p_batch_id is function parameter.
        Do not use "batch_id" directly here because it can be ambiguous
        with table columns in PL/pgSQL.
    */
    SELECT 
        br.*, 
        p."name"::text AS prop_name, 
        p.code::text AS prop_code, 
        p.id AS prop_id  
    INTO v_batch
    FROM public.batch_report br 
    LEFT JOIN public.property p ON p.id = br.prop_id 
    WHERE br.id = p_batch_id;

IF NOT FOUND THEN
        RETURN json_build_object(
            'status', 'error',
            'error_message', 'batch_report not found',
            'batch_id', p_batch_id
        );
    END IF;

/*
        Access token resolution.

First priority:
            latest active login token from work_log by username + prop_id

Fallback:
            REPORT_GEN_TOKEN from config

Previous bug:
            fn_get_login_token(p_user_name)
        was wrong because real function signature is:
            fn_get_login_token(text, integer)
    */
    v_access_token := coalesce(
        public.fn_get_login_token(p_user_name, v_batch.prop_id),
        public.fn_get_config(v_batch.prop_id, 'REPORT_GEN_TOKEN')
    );

/*
        Get property system date.
        This is used when parsing report option values.
    */
    v_sys_date := public.fn_system_date(v_batch.prop_id);

/*
        Loop through enabled report items in this batch.

JOIN batch_report_items is used intentionally because enabled item
        is required. LEFT JOIN + bi.enabled = true behaves like INNER JOIN anyway.
    */
    FOR v_item IN 
        SELECT 
            bi.report_id,
            bi.option_values,
            r."options" AS report_options,
            COALESCE(bi.report_alias, r.report_name) AS report_name,
            CASE 
                WHEN r.report_filename ILIKE '%.frx' THEN 'frx' 
                ELSE 'jrxml' 
            END AS template_type
        FROM public.batch_report br 
        JOIN public.batch_report_items bi ON bi.batch_id = br.id 
        LEFT JOIN public.reports r ON r.id = bi.report_id  
        WHERE br.id = p_batch_id
          AND br.enabled = true
          AND bi.enabled = true
        ORDER BY r.report_name, bi.duplicated, bi.item_id 
    LOOP
        v_report_index := v_report_index + 1;
        v_time_start := clock_timestamp();

/*
            Reset params for each item.
            Without this, if fn_parse_param_values fails,
            error JSON might accidentally reuse params from previous report.
        */
        v_report_params := NULL;

BEGIN
            /*
                Convert saved option_values to actual report params.
                If this fails, only this item becomes status = error.
                Other report items can continue.
            */
            v_report_params := public.fn_parse_param_values(
                v_item.report_options,
                v_item.option_values,
                v_sys_date
            );

v_execute_time := EXTRACT(EPOCH FROM (clock_timestamp() - v_time_start));

v_reports_json := v_reports_json || jsonb_build_object(
                'index', v_report_index,
                'prop_code', v_batch.prop_code,
                'report_id', v_item.report_id,
                'report_name', v_item.report_name,
                'params', v_report_params,
                'access_token', v_access_token,
                'template_type', v_item.template_type,
                'status', 'success',
                'execute_time_sec', v_execute_time
            );

EXCEPTION WHEN OTHERS THEN
            /*
                Item-level error handling.

This catches errors from parameter parsing or JSON building
                for the current report item only.
            */
            v_execute_time := EXTRACT(EPOCH FROM (clock_timestamp() - v_time_start));
            GET STACKED DIAGNOSTICS v_err_message = MESSAGE_TEXT;

v_reports_json := v_reports_json || jsonb_build_object(
                'index', v_report_index,
                'prop_code', v_batch.prop_code,
                'report_id', v_item.report_id,
                'report_name', v_item.report_name,
                'params', v_report_params,
                'access_token', v_access_token,
                'template_type', v_item.template_type,
                'status', 'error',
                'error_message', v_err_message,
                'execute_time_sec', v_execute_time
            );
        END;
    END LOOP;

/*
        Final response consumed by report-engine.

Note:
        This payload is before actual PDF generation and before printer dispatch.
    */
    v_result_json := json_build_object(
        'batch_id', v_batch.id,
        'batch_name', v_batch.batch_name,
        'prop_code', v_batch.prop_code,
        'prop_name', v_batch.prop_name,
        'system_date', v_sys_date,
        'reports', v_reports_json
    );

RETURN v_result_json;
END