aonestar.public

Parameters

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

Definition

DECLARE 
    TEST_MODE bool = is_dev_server();  
    msg_count int = 0;
    item record;
    _user_email text;
    _recipients text[];
    _prop_name 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;   
    --_prop_name := (SELECT name 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
        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);
    
        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