Enabled Notifications

1. All elements in Favorite Folders (for a Digest with enabled Notifications)

USE CASE:

  • Retrieve all elements in Favorite Folders for a Digest with enabled Notifications

Table favorite_dashboard_element_info contains all elements in Favorite Folders including Shared Folders.

SELECT DISTINCT fdei.user_id, fdei.element_id
FROM favorite_dashboard_element_info AS fdei
JOIN favorite AS f ON (f.favorite_id=fdei.favorite_id)
JOIN user_preference AS up ON (up.user_id=fdei.user_id)
WHERE f.include_in_favorites_digest_ind='Y'
AND up.email_notification_enabled_ind = 'Y';
Click to copy
2. All User Alert Subscriptions (for elements with enabled Notifications)

USE CASE:

  • Retrieve all User Alert Subscriptions for elements with enabled Notifications
  • Table user_alert_rule is used for storing information about user alert subscriptions since 4.0.
  • Table alert_rule_element_info contain elements from alert rule scope with visualizations.
SELECT DISTINCT uar.user_id, arei.visualization_element_id
FROM user_alert_rule AS uar
JOIN alert_rule_element_info AS arei ON (uar.alert_rule_id=arei.alert_rule_id AND uar.element_id=arei.element_id)
JOIN user_preference AS up ON (up.user_id=uar.user_id)
WHERE uar.enabled_ind='Y'
AND up.email_notification_enabled_ind = 'Y';
Click to copy
3. Burst Recipients with enabled Notifications

USE CASE:

  • Retrieve Burst Recipients with enabled Notifications
  • Table user_alert_rule is used for storing information about user alert subscriptions since 4.0.
  • Table alert_rule_element_info contain elements from alert rule scope with visualizations.
SELECT dlarv.notification_schedule_distribution_id as Burst_id, nsd.name AS Burst_name,  nsd.enabled_ind AS Burst_Enabled, u.user_id AS Subscribed_User_id, u.username AS Subscribed_user_name,  u.email AS User_mail
FROM distribution_list_all_recipients_view AS dlarv 
JOIN notification_schedule_distribution AS nsd ON dlarv.notification_schedule_distribution_id=nsd.notification_schedule_distribution_id 
JOIN user AS u ON u.user_id=dlarv.user_id 
WHERE u.enabled_ind='Y'
ORDER BY dlarv.notification_schedule_distribution_id, nsd.name;
Click to copy

The contents of "distribution_list_all_recipients_view" (from the above query) are provided below:

CREATE ALGORITHM=UNDEFINED VIEW `distribution_list_all_recipients_view` AS
    SELECT
        `nsdur`.`user_id` AS `user_id`,
        `nsdur`.`notification_schedule_distribution_id` AS `notification_schedule_distribution_id`
    FROM `notification_schedule_distribution` as `nsd`
        JOIN `notification_schedule_distribution_user_recipient` as `nsdur` ON `nsdur`.`notification_schedule_distribution_id` = `nsd`.`notification_schedule_distribution_id`
    WHERE `nsd`.`recipient_scope` = 'selected'
    UNION
    SELECT
        `f`.`user_id` AS `user_id`,
        `nsd`.`notification_schedule_distribution_id` AS `notification_schedule_distribution_id`
    FROM `notification_schedule_distribution` as `nsd`
        JOIN `notification_schedule_distribution_item` as `nsdi` ON `nsdi`.`notification_schedule_distribution_id` = `nsd`.`notification_schedule_distribution_id` AND `nsd`.`content_type` = 'favorites'
        JOIN `favorite` as `f` ON (`f`.`source_shared_favorite_id` = `nsdi`.`favorite_id` OR `f`.`copied_from_favorite_id` = `nsdi`.`favorite_id`)
    WHERE `nsd`.`recipient_scope` = 'all'
    UNION
    SELECT
        `nsd`.`created_by_user_id` AS `user_id`,
        `nsd`.`notification_schedule_distribution_id` AS `notification_schedule_distribution_id`
    FROM `notification_schedule_distribution` as `nsd`
    WHERE `nsd`.`send_owner_distribution_ind` = 'Y';
Click to copy