aonestar.public

Parameters

Name	Type	Mode
guests	int4[]	IN
msg_template	text	IN
params	jsonb	IN (DEFAULT NULL)
skip_test_mode	boolean	IN (DEFAULT false)
guests	int4[]	IN
msg_template	text	IN
params	jsonb	IN (DEFAULT NULL)

Definition

DECLARE
    TEST_MODE bool = not SKIP_TEST_MODE and is_dev_server();
    msg_count int = 0;
    item record;
    _user_email text;
    _recipients text[];
    _msg_id bigint;
    _sqlstate TEXT;
    _detail   TEXT;
    _hint     TEXT;
    _context  TEXT;
    _msg_text TEXT;
    prop record;
BEGIN
    /*
     * Create by Pisanu (2021/07/08)
     * For send message to guests
     */
    SELECT * INTO prop FROM property_info LIMIT 1;

IF TEST_MODE THEN
        _user_email := COALESCE(fn_user_email(), 'pisanu@newsoft.co.th');
    END IF;

FOR item IN (
        WITH guest_data AS (
            SELECT prop.name AS property_name,
                prop.prop_code as property_code,
                prop.company as property_company,
                prop.logo_url as property_logo_url,
                prop.email as property_email,
                prop.telephone as property_telephone,
                prop.fax as property_fax,
                prop.website as property_website,
                prop.address1 as property_address1,
                prop.address2 as property_address2,
                prop.address3 as property_address3,
                prop.address4 as property_address4,

g.id AS guest_id,
                g.full_name AS guest_name,
                g.first_name, g.last_name,
                g.middle_name, t."name" AS title_name,
                IIF(TEST_MODE, _user_email, g.emails[1]) AS email_address,
                g.mobiles[1] AS mobile_number
            FROM guest g LEFT JOIN title t ON t.id = g.title_id
            WHERE g.id = ANY($1)
        )
        SELECT row_to_json(g)::jsonb || coalesce($3,'{}') AS params,
            g.email_address, g.mobile_number, g.property_name
        FROM guest_data g
        WHERE g.email_address IS NOT NULL
    ) LOOP
        _recipients := ARRAY[item.email_address];

_msg_id := sp_send_message(msg_template, item.params, _recipients, sender_name := item.property_name);

msg_count := msg_count + iif(_msg_id > 0, 1, 0);
    END LOOP;

RETURN msg_count;
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_to_guests',
        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 msg_count;
END