aonestar.public

Parameters

Name	Type	Mode

Definition

DECLARE 
    v_register_id int;
    v_booking_id int;
    v_guest_id int;
BEGIN
    v_register_id = CASE TG_OP WHEN 'DELETE' THEN OLD.register_id ELSE NEW.register_id END;  
    v_booking_id  = CASE TG_OP WHEN 'DELETE' THEN OLD.booking_id  ELSE NEW.booking_id  END;
    
   IF v_register_id IS NOT NULL THEN 
        UPDATE registration 
        SET msg_count = (SELECT count(*) FROM guest_messages WHERE register_id = v_register_id) 
        WHERE id = v_register_id;
    ELSIF v_booking_id IS NOT NULL THEN 
        UPDATE booking 
        SET msg_count = (SELECT count(*) FROM guest_messages WHERE booking_id = v_booking_id) 
        WHERE id = v_booking_id;
    END IF;

-- Send message to guest
    IF TG_OP = 'INSERT' THEN 
        v_guest_id = 
            CASE 
                WHEN (NEW.register_id NOTNULL) THEN (SELECT guest_id FROM registration r WHERE r.id = NEW.register_id) 
                ELSE (SELECT guest_id FROM booking b WHERE b.id = NEW.booking_id) 
            END;
        PERFORM sp_send_message_to_guest(v_guest_id,
            msg_template => 'guest-message',
            params => jsonb_build_object('MESSAGE_FROM', NEW.message_from, 
                                         'MESSAGE'     , NEW.message)
        );
    END IF;

    RETURN NULL;
END

sp_tr_guest_messages_counter

Parameters

Definition