The easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:
TypeScript analyticsbibusiness-intelligencebusinessintelligenceclojuredashboarddatadata-analysisdata-visualizationdatabasemetabasemysqlpostgrespostgresqlreportingslacksql-editorvisualization
drop view if exists v_alerts;
create view v_alerts as
select
n.id as entity_id,
'notification_' || n.id as entity_qualified_id,
n.created_at,
n.updated_at,
n.creator_id,
nc.card_id,
'card_' || nc.card_id as card_qualified_id,
case
when nc.send_condition = 'has_result' then 'rows'
when nc.send_condition in ('goal_above', 'goal_below') then 'goal'
end as alert_condition,
case
when ns.ui_display_type = 'cron/raw' then 'custom'
when REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ ([^ ]+)', 1, 1, '', 1) = '*' or
REGEXP_LIKE(REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ ([^ ]+)', 1, 1, '', 1), '^[0-9]+/[0-9]+$') then 'by the minute'
when REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1) != '*' and
(REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ [^ ]+ [^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1) = '?' or
REGEXP_LIKE(REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ [^ ]+ [^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1), '^[0-9]#1$') or
REGEXP_LIKE(REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ [^ ]+ [^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1), '^[0-9]L$')) then 'monthly'
when REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ [^ ]+ [^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1) != '?' and
REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ [^ ]+ [^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1) != '*' then 'weekly'
when REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1) != '*' then 'daily'
else 'hourly'
end as schedule_type,
case
when REGEXP_LIKE(REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ [^ ]+ [^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1), '^1') then 'sun'
when REGEXP_LIKE(REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ [^ ]+ [^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1), '^2') then 'mon'
when REGEXP_LIKE(REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ [^ ]+ [^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1), '^3') then 'tue'
when REGEXP_LIKE(REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ [^ ]+ [^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1), '^4') then 'wed'
when REGEXP_LIKE(REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ [^ ]+ [^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1), '^5') then 'thu'
when REGEXP_LIKE(REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ [^ ]+ [^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1), '^6') then 'fri'
when REGEXP_LIKE(REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ [^ ]+ [^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1), '^7') then 'sat'
else null
end as schedule_day,
case
when REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1) = '*' then null
when REGEXP_LIKE(REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1), '^[0-9]+$') then
cast(REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1) as integer)
when REGEXP_LIKE(REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1), '^[0-9]+/[0-9]+$') then
cast(REGEXP_SUBSTR(REGEXP_SUBSTR(ns.cron_schedule, '^[^ ]+ [^ ]+ ([^ ]+)', 1, 1, '', 1), '^([0-9]+)/[0-9]+$', 1, 1, '', 1) as integer)
else null
end as schedule_hour,
not n.active as archived,
nh.channel_type as recipient_type,
(select GROUP_CONCAT(cu.email)
from notification_recipient nr
left join core_user cu on nr.user_id = cu.id and nr.type = 'notification-recipient/user'
where nr.notification_handler_id = nh.id) as recipients,
(select GROUP_CONCAT(nr.details)
from notification_recipient nr
where nr.notification_handler_id = nh.id
and nr.type = 'notification-recipient/raw-value') as recipient_external
from notification n
join notification_card nc on n.payload_id = nc.id
join notification_subscription ns on n.id = ns.notification_id
left join notification_handler nh on n.id = nh.notification_id
where n.payload_type = 'notification/card'
and ns.type = 'notification-subscription/cron';