authen.public

Parameters

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

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;
    ret_index    int := 0;             -- ลำดับในผลลัพธ์ (นับใหม่หลังจาก start_at)
BEGIN
    -- normalize start_at
    start_at := GREATEST(COALESCE(start_at, 1), 1);

    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);

    FOR item IN 
        WITH items AS (
            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,
                ROW_NUMBER() OVER (ORDER BY r.report_name, bi.duplicated, bi.item_id) AS pos_in_batch
            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
        )
        SELECT * FROM items
        WHERE pos_in_batch >= start_at      -- ✅ เริ่มจากลำดับที่กำหนด
        ORDER BY pos_in_batch
    LOOP
        ret_index := ret_index + 1;         -- นับลำดับผลลัพธ์ (1,2,3… หลังจาก start_at)

        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(
                'index', ret_index,                       -- ลำดับในผลลัพธ์
                'pos_in_batch', item.pos_in_batch,        -- ลำดับเดิมใน batch
                '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', ret_index,
                'pos_in_batch', item.pos_in_batch,
                '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_by_index

Parameters

Definition