aonestar.public

Parameters

Name	Type	Mode
msg_template	text	IN
params	jsonb	IN (DEFAULT NULL)
recipients	text[]	IN (DEFAULT NULL)
subject	text	IN (DEFAULT NULL)
message	text	IN (DEFAULT NULL)
sender_name	text	IN (DEFAULT NULL)
reply_to	text	IN (DEFAULT NULL)
cc	text[]	IN (DEFAULT NULL)
bcc	text[]	IN (DEFAULT NULL)
attach_data	bytea	IN (DEFAULT NULL)
attach_filename	text	IN (DEFAULT NULL)
subject_suffix	text	IN (DEFAULT NULL)

Definition

DECLARE
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
    sql_text TEXT;
    v_map_key       TEXT;    -- คีย์ lookup mapping; NULL = ไม่ customize ราย property
    v_prop_template TEXT;    -- ชื่อ variant ผู้สมัคร; NULL = ไม่ customize ราย property
    SQL_CREATE_MESSAGE TEXT = $Q$
        WITH tpl AS (
            SELECT coalesce(
                nullif(fn_get_config(%s, 'MESSAGE_TEMPLATE', '@SYSTEM')::jsonb ->> %L, ''),
                (select t.id from msg.templates t where t.id = %L),
                %L) AS id
        )
        Select msg.create_message(
            i_sender => %L,
            i_recipients => %L::text[],
            i_subject => coalesce(%L,
                (select t.subject || %L from msg.templates t where t.id = tpl.id)),
            i_template => tpl.id,
            i_template_params => %L::json,
            i_content => %L,
            i_reply_to => %L,
            i_cc => %L::text[],
            i_bcc => %L::text[],
            attach_data => %L,
            attach_filename => %L
        )
        FROM tpl
    $Q$;
BEGIN
    /*
     * Create by Pisanu (2021/07/08)
     * For send message
     */

-- system-notify เป็นข้อความระบบ ไม่ผูกโรงแรม -> ไม่ customize ทั้งสองชั้น (คงกฎเดิม)
    -- ส่ง NULL ไปทั้งคู่ แล้ว coalesce ในคำสั่งข้างล่างจะตกไปใช้ชื่อฐานเอง
    IF msg_template <> 'system-notify' THEN
        v_map_key       := msg_template;
        -- fn_prop_code() คืนตัวใหญ่ (เช่น SAMPLE) แต่ template id เป็นตัวเล็ก -> ต้อง lower()
        v_prop_template := msg_template || '-' || lower(fn_prop_code());
    END IF;

sql_text = format(SQL_CREATE_MESSAGE,
                      fn_prop_id()::text, v_map_key, v_prop_template, msg_template,
                      sender_name, recipients, subject, subject_suffix,
                      params, message, reply_to, cc, bcc, attach_data, attach_filename);
    RETURN (SELECT msg_id FROM dblink(fn_conn_authen(), sql_text) AS (msg_id bigint));
EXCEPTION
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        _msg_text = MESSAGE_TEXT,
        _sqlstate = RETURNED_SQLSTATE,
        _detail   = PG_EXCEPTION_DETAIL,
        _hint     = PG_EXCEPTION_HINT,
        _context  = PG_EXCEPTION_CONTEXT;
    PERFORM sp_log_warning('sp_send_message',
        coalesce('message  : '||_msg_text||E'\n', '') ||
        coalesce('detail   : '||_detail||E'\n', '') ||
        coalesce('hint     : '||_hint||E'\n') ||
        coalesce('sql_state: '||_sqlstate||E'\n', '') ||
        coalesce('context  : '||_context||E'\n', '')
    );
    RETURN NULL;
END

sp_send_message

Description

Parameters

Definition