CREATE TABLE IF NOT EXISTS `def_notify` (
`NotifyCode` char(255) NOT NULL,
`Package` char(255) NOT NULL,
`Description` text DEFAULT NULL,
`Users` text DEFAULT NULL,
`UserGroups` text DEFAULT NULL,
`Rank` int(11) DEFAULT 0,
`NotifyValueSql` text DEFAULT NULL,
`TriggerSql` text DEFAULT NULL,
`TriggerCooldown` int(11) DEFAULT 0,
`NotifyMode` char(50) DEFAULT 'LOCAL',
`Module` char(255) DEFAULT '',
`NotifyDiscordSend` char(1) DEFAULT 'N',
`NotifyDiscordConnection` char(255) DEFAULT '',
`NotifyDiscordText` text DEFAULT NULL,
`NotifyPushSend` char(1) DEFAULT 'N',
`NotifyPushConnection` char(255) DEFAULT '',
`NotifyPushText` text DEFAULT NULL,
`NotifySmsSend` char(1) DEFAULT 'N',
`NotifySmsConnection` char(255) DEFAULT '',
`NotifySmsList` text DEFAULT NULL,
`NotifySmsText` text DEFAULT NULL,
`NotifySlackSend` char(1) DEFAULT 'N',
`NotifySlackConnection` char(255) DEFAULT '',
`NotifySlackText` text DEFAULT NULL,
`NotifyEmailSend` char(1) DEFAULT 'N',
`NotifyEmailConnection` char(255) DEFAULT '',
`NotifyEmailText` text DEFAULT NULL,
`NotifyMqttSend` char(1) DEFAULT 'N',
`NotifyMqttConnection` char(255) DEFAULT '',
`NotifyMqttText` text DEFAULT NULL,
`NotifyDbusSend` char(1) DEFAULT 'N',
`NotifyDbusConnection` char(255) DEFAULT '',
`NotifyDbusText` text DEFAULT NULL,
`NotifyWhatsappSend` char(1) DEFAULT 'N',
`NotifyWhatsappConnection` char(255) DEFAULT '',
`NotifyWhatsappText` text DEFAULT NULL,
`NotifyPagerdutySend` char(1) DEFAULT 'N',
`NotifyPagerdutyConnection` char(255) DEFAULT '',
`NotifyPagerdutyText` text DEFAULT NULL,
`Active` char(1) DEFAULT 'Y',
PRIMARY KEY (`NotifyCode`, `Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
CREATE TABLE IF NOT EXISTS `stage_notify` (
`Guid` char(255) NOT NULL,
`NotifyCode` char(255) NOT NULL,
`Package` char(255) NOT NULL,
`User` char(255) NOT NULL,
`PersonGuid` char(50) DEFAULT NULL COMMENT 'PersonGuid as notification target',
`Message` text DEFAULT NULL,
`MessageDetail` text DEFAULT NULL,
`MessageImage` text DEFAULT NULL,
`MessageLink` text DEFAULT NULL,
`EventTimeSent` datetime DEFAULT NULL,
`EventSlackSent` datetime DEFAULT NULL,
`EventPushSent` datetime DEFAULT NULL,
`EventSmsSent` datetime DEFAULT NULL,
`EventDiscordSent` datetime DEFAULT NULL,
`EventPagerdutySent` datetime DEFAULT NULL,
`EventMqttSent` datetime DEFAULT NULL,
`EventWhatsappSent` datetime DEFAULT NULL,
`EventTelegramSent` datetime DEFAULT NULL,
`EventEmailSent` datetime DEFAULT NULL,
`EventDbusSent` datetime DEFAULT NULL,
`TimeRead` datetime DEFAULT NULL,
PRIMARY KEY (`Guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation}
SELECT * FROM def_notify
WHERE NotifyCode = '{notify_code}' AND Package = '{package}'
SELECT COUNT(*) FROM def_notify WHERE NotifyCode = 'SYSADMIN'
INSERT INTO def_notify (NotifyCode, NotifySlackSend, Usergroups, Package, NotifySmsConnection, NotifySmsList)
VALUES ('SYSADMIN', 'Y', 'SYSADMIN', 'ALL', '', '')
INSERT INTO def_notify (NotifyCode, Package, NotifySlackSend, NotifySlackConnection, NotifySmsConnection, NotifySmsList)
VALUES ('{notify_code}', '{package}', 'Y', '{package}', '', '')
UPDATE stage_notify SET TimeRead = now() WHERE NoteId = '{note_id}'
SELECT Guid, NotifyCode, User, Message, MessageDetail, MessageImage, MessageLink
FROM stage_notify
WHERE Event{mes_type}Sent IS NULL AND Package = '{package}'
SELECT Msisdn FROM sys_user WHERE User = '{user}' AND Package = '{package}'
UPDATE stage_notify SET Event{mes_type}Sent = now() WHERE Guid = '{guid}' AND User = '{user}' AND Package = '{package}'
SELECT User FROM sys_user WHERE Package = '{package}' AND Active = 'Y'
SELECT User FROM sys_usergroup_users WHERE Usergroup = '{group}' AND Package = '{package}'
SELECT * FROM sys_user WHERE User = '{user}'
INSERT INTO stage_notify (Guid, NotifyCode, Package, User, Message)
VALUES ('{guid}', '{notify_code}', '{package}', '{user}', '{message}')
SELECT NotifyCode FROM def_notify WHERE Package = '{package}'
SELECT Msisdn FROM data_person
WHERE (
PersonGuid = '{identifier}'
OR User = '{identifier}'
OR Name = '{identifier}'
)
AND Package = '{package}'
AND Active = 'Y'
LIMIT 1
SELECT WhatsappNumber FROM data_person
WHERE (
PersonGuid = '{identifier}'
OR User = '{identifier}'
OR Name = '{identifier}'
)
AND Package = '{package}'
AND Active = 'Y'
LIMIT 1