authen.public

Parameters

Name	Type	Mode

Definition

DECLARE
    v_property        RECORD;
    v_interface_code  text;
    v_cleared_count   int := 0;
    v_error_count     int := 0;
    v_conn_string     text;
    v_prop_uuid       uuid;
    v_channel_code    text;
    v_rate_enabled    boolean;
    v_restr_enabled   boolean;
    v_params          jsonb;
    v_summary         text := '';
    v_prop_count      int;
    v_del             int;
    v_sdel            int;
BEGIN
    SELECT COUNT(*) INTO v_prop_count FROM property WHERE enabled = true;

    FOR v_property IN
        SELECT id, code, name
        FROM property
        WHERE enabled = true
    LOOP
        -- ดึง connection string
        BEGIN
            v_conn_string := fn_prop_connection(v_property.id);
        EXCEPTION WHEN OTHERS THEN
            v_error_count := v_error_count + 2;
            v_summary := v_summary || format(E'\n[%s] conn error: %s', v_property.code, SQLERRM);
            CONTINUE;
        END;

        -- อ่าน params จาก property DB ในครั้งเดียว
        BEGIN
            SELECT value INTO v_params
            FROM dblink(v_conn_string, $SQL$
                SELECT jsonb_build_object(
                    'channel_code',  COALESCE(NULLIF(fn_intf_param('NBIS','INTERFACE_CODE',''),''), 'CHANNEX'),
                    'rate_enabled',  fn_intf_param('NBIS','INTERFACE_RATE_ENABLED','false')::boolean,
                    'restr_enabled', fn_intf_param('NBIS','INTERFACE_RESTRICTIONS_ENABLED','false')::boolean
                )::jsonb
            $SQL$) AS t(value jsonb);

            v_channel_code  := v_params->>'channel_code';
            v_rate_enabled  := (v_params->>'rate_enabled')::boolean;
            v_restr_enabled := (v_params->>'restr_enabled')::boolean;
        EXCEPTION WHEN OTHERS THEN
            v_error_count := v_error_count + 2;
            v_summary := v_summary || format(E'\n[%s] read params error: %s', v_property.code, SQLERRM);
            CONTINUE;
        END;

        -- อ่าน property UUID ผ่าน channel code
        BEGIN
            SELECT (value->>'property_id')::uuid INTO v_prop_uuid
            FROM dblink(v_conn_string, format($SQL$
                SELECT jsonb_build_object('property_id', fn_intf_param(%L,'PROPERTY_ID',''))::jsonb
            $SQL$, v_channel_code)) AS t(value jsonb);
        EXCEPTION WHEN OTHERS THEN
            v_error_count := v_error_count + 2;
            v_summary := v_summary || format(E'\n[%s] read UUID error: %s', v_property.code, SQLERRM);
            CONTINUE;
        END;

        IF v_prop_uuid IS NULL THEN
            v_error_count := v_error_count + 2;
            v_summary := v_summary || format(E'\n[%s] UUID null (PROPERTY_ID not set in %s?)', v_property.code, v_channel_code);
            CONTINUE;
        END IF;

        -- สรุป params ของ property นี้
        v_summary := v_summary || format(E'\n[%s] ch=%s rate=%s restr=%s uuid=%s',
            v_property.code, v_channel_code, v_rate_enabled, v_restr_enabled,
            left(v_prop_uuid::text, 8));

        -- Clear RATE
        v_interface_code := 'NBIS_' || v_channel_code || '_RATE';
        IF v_rate_enabled THEN
            BEGIN
                CALL public.sp_clear_intf_rate_state(v_interface_code, v_prop_uuid, v_conn_string, v_del, v_sdel);
                v_cleared_count := v_cleared_count + 1;
                v_summary := v_summary || format(' | RATE del=%s stamp=%s', v_del, v_sdel);
            EXCEPTION WHEN OTHERS THEN
                v_error_count := v_error_count + 1;
                v_summary := v_summary || format(' | RATE err: %s', SQLERRM);
            END;
        ELSE
            v_summary := v_summary || ' | RATE skip';
        END IF;

        -- Clear RESTRICTIONS
        v_interface_code := 'NBIS_' || v_channel_code || '_RESTRICTIONS';
        IF v_restr_enabled THEN
            BEGIN
                CALL public.sp_clear_intf_rate_state(v_interface_code, v_prop_uuid, v_conn_string, v_del, v_sdel);
                v_cleared_count := v_cleared_count + 1;
                v_summary := v_summary || format(' | RESTR del=%s stamp=%s', v_del, v_sdel);
            EXCEPTION WHEN OTHERS THEN
                v_error_count := v_error_count + 1;
                v_summary := v_summary || format(' | RESTR err: %s', SQLERRM);
            END;
        ELSE
            v_summary := v_summary || ' | RESTR skip';
        END IF;

    END LOOP;

    PERFORM public.sp_system_notify(
        'DAILY CLEAR INTERFACE STATE',
        format('Cleared: %s | Errors: %s | Props: %s%s',
               v_cleared_count, v_error_count, v_prop_count, v_summary),
        NULL, '🧹'
    );
END;

sp_daily_clear_all_intf_state

Parameters

Definition