authen.public

Parameters

Name	Type	Mode
batch_id	integer	IN
user_name	text	IN (DEFAULT NULL)

Definition

DECLARE 
    batch        record;
    item         record;
    sys_date     date;
    report_params json;
    err_message  TEXT;
    time_start   timestamp;
    execute_time numeric(8,3);
    access_token text;
    reports_json jsonb := '[]'::jsonb;
    result_json  json;
BEGIN
    -- โหลด batch
    SELECT br.*, p."name"::text AS prop_name, p.code::text AS prop_code, p.id as prop_id  
    INTO batch
    FROM batch_report br 
    LEFT JOIN property p ON p.id = br.prop_id 
    WHERE br.id = $1;

    access_token := coalesce(fn_get_login_token(user_name), fn_get_config(batch.prop_id, 'REPORT_GEN_TOKEN'));
    sys_date := fn_system_date(batch.prop_id);

    -- วนทุก report item
    FOR 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 batch_report br 
            left join batch_report_items bi on bi.batch_id = br.id 
            LEFT JOIN reports r ON r.id = bi.report_id  
        WHERE br.id = $1 and br.enabled = true and bi.enabled = true
        ORDER BY r.report_name, bi.duplicated, bi.item_id 
    LOOP

        time_start := clock_timestamp();
        BEGIN
            report_params := fn_parse_param_values(item.report_options, item.option_values, sys_date);
            execute_time := EXTRACT(EPOCH FROM (clock_timestamp() - time_start));

            reports_json := reports_json || jsonb_build_object(
              
                'prop_code', batch.prop_code,
                'report_id', item.report_id,
                'report_name', item.report_name,
                'params', report_params,
                'access_token', to_jsonb(access_token),
                'template_type', item.template_type,
                'status', 'success',
                'execute_time_sec', execute_time
            );
        EXCEPTION WHEN OTHERS THEN
            execute_time := EXTRACT(EPOCH FROM (clock_timestamp() - time_start));
            GET STACKED DIAGNOSTICS err_message = MESSAGE_TEXT;

            reports_json := reports_json || jsonb_build_object(
                'index', report_index,         -- ✅ ใส่ index
                'prop_code', batch.prop_code,
                'report_id', item.report_id,
                'report_name', item.report_name,
                'params', report_params,
                'access_token', to_jsonb(access_token),
                'template_type', item.template_type,
                'status', 'error',
                'error_message', err_message,
                'execute_time_sec', execute_time
            );
        END;
    END LOOP;

    result_json := json_build_object(
        'batch_id', batch.id,
        'batch_name', batch.batch_name,
        'prop_code', batch.prop_code,
        'prop_name', batch.prop_name,
        'system_date', sys_date,
        'reports', reports_json
    );

    RETURN result_json;
END

sp_get_run_batch_report

Parameters

Definition