aonestar.public

Parameters

Name	Type	Mode
post_type	character	IN (DEFAULT 'P')
p_only_active	boolean	IN (DEFAULT false)
p_exclude_auto_post	boolean	IN (DEFAULT false)

Definition

DECLARE
    v_result json;
    v_auto_post_ids int[];
    v_msg_text text;
    v_sqlstate text;
    v_detail text;
    v_hint text;
    v_context text;
BEGIN
    IF p_exclude_auto_post THEN
        SELECT array_agg(DISTINCT x)
        INTO v_auto_post_ids
        FROM unnest(ARRAY[
            fn_sys_param('AUTO_POST', 'ROOM',     null::int),
            fn_sys_param('AUTO_POST', 'EXTRABED', null::int),
            fn_sys_param('AUTO_POST', 'ABF',      null::int),
            fn_sys_param('AUTO_POST', 'LUNCH',    null::int),
            fn_sys_param('AUTO_POST', 'DINNER',   null::int)
        ]) AS x
        WHERE x IS NOT NULL;
    END IF;

WITH dept_data AS (
        SELECT
            d.id,
            d.code,
            d.name,
            dg.id as group_id,
            dg.name AS group,
            d.dept_type,
            d.require_info,
            d.taxes,
            d.enabled,
            d.itemizers,
            d.vat_effect
        FROM department d LEFT JOIN department_group dg ON d.group_id = dg.id
        WHERE d.post_type = $1
          AND (NOT p_only_active OR d.enabled = true)
          AND (NOT p_exclude_auto_post
               OR d.id <> ALL (coalesce(v_auto_post_ids, ARRAY[]::int[])))
        ORDER BY d.code collate numeric asc
    )
    SELECT json_agg(dept_data) FROM dept_data
    INTO v_result;

RETURN coalesce(v_result, '[]'::json);
EXCEPTION
    WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS
            v_msg_text = message_text,
            v_sqlstate = returned_sqlstate,
            v_detail = pg_exception_detail,
            v_hint = pg_exception_hint,
            v_context = pg_exception_context;

RETURN fn_handle_error(
            v_sqlstate,
            v_msg_text,
            v_detail,
            v_hint,
            v_context,
            'sp_get_department_json',
            null::jsonb
        );
END;