CREATE TABLE IF NOT EXISTS `def_channelmail` (
`Email` varchar(255) NOT NULL DEFAULT '',
`Channel` varchar(255) NOT NULL DEFAULT '',
`ReportDate` datetime DEFAULT NULL,
`Uuid` varchar(255) DEFAULT NULL,
`Clickthrough` varchar(255) DEFAULT NULL,
`ChannelReport3` varchar(255) DEFAULT NULL,
`ChannelReport2` varchar(255) DEFAULT NULL,
`ChannelReport1` varchar(255) DEFAULT NULL,
`ChannelReplyTo` varchar(255) DEFAULT NULL,
`RegisterDate` datetime DEFAULT NULL,
`Status` varchar(255) DEFAULT NULL,
`Firstname` varchar(255) DEFAULT '',
`ChannelDescription` varchar(255) DEFAULT NULL,
`Company` varchar(255) DEFAULT '',
`ChannelReportNotes` text DEFAULT NULL,
`Campaign_id` int(11) DEFAULT NULL,
`User` varchar(255) DEFAULT '',
`Active` varchar(255) DEFAULT 'Y',
`SendStatus` varchar(255) DEFAULT NULL,
`Notes` text DEFAULT NULL,
`TimeCompleted` datetime DEFAULT NULL,
`Cellphone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '',
`TimeAdded` timestamp NULL DEFAULT NULL,
`infrastructure` varchar(1) DEFAULT NULL,
`SendDate` datetime DEFAULT NULL,
`Param4` varchar(255) DEFAULT NULL,
`Param3` varchar(255) DEFAULT NULL,
`Param2` varchar(255) DEFAULT NULL,
`Param1` varchar(255) DEFAULT NULL,
`ReportActive` varchar(255) DEFAULT 'Y',
`ProofReader` varchar(255) DEFAULT 'Y',
`TimeScheduled` datetime DEFAULT NULL,
`Surname` varchar(255) DEFAULT '',
`ChannelNotes` text DEFAULT NULL,
`Step_id` int(11) DEFAULT 0,
`ClickDate` datetime DEFAULT NULL,
`SourceGuid` varchar(255) DEFAULT NULL,
`Package` varchar(255) NOT NULL DEFAULT '',
`Module` varchar(255) DEFAULT NULL,
PRIMARY KEY (`Email`,`Channel`,`Package`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation} ROW_FORMAT=DYNAMIC;
SELECT * FROM def_ChannelMail WHERE Email = '{email}'
AND Channel = '{channel}'
AND IF(IFNULL(Package, '') = '', 'CORE', Package)
= '{package}'
SELECT * FROM def_ChannelMail WHERE Uuid = '{uuid_value}'
AND IFNULL(Package, 'CORE') = '{package}'
INSERT IGNORE INTO def_ChannelMail
(Channel, Email, Uuid, Registerdate, Package)
VALUES
('{channel}', '{email}', '{uuid}', NOW(), '{package}')
UPDATE Track_Channelmail SET Clickthrough = 'Y', Clickdate = NOW() WHERE Uuid = '{uuid}'
UPDATE Stage_channel SET Status = 'SEND',
TimeCompleted = NOW(),
Uuid = '{uuid}'
WHERE Channel_Stage_id = '{item_id}'
SELECT Email, 0, '' AS descript,
'' AS report, '' AS reportnotes
FROM def_ChannelMail WHERE Channel = '{channel}' AND Active = 'Y'
INSERT IGNORE INTO def_Channelmail
(Email, User, Channel, Package)
SELECT Email, User,
'{channel}' AS Channel,
'{package}' AS Package
FROM User
SELECT Attachmentreport, Basedir FROM `{def_table}` WHERE Channel = '{channel}'
AND IFNULL(Package, 'CORE') = '{package}'
LIMIT 1
SELECT Proofreader FROM def_Channelmail WHERE Email = '{email}'
AND Channel = '{channel}'
AND IFNULL(Package, 'CORE') = '{package}'
SELECT Email, Guid, ChannelDescription,
ChannelNotes, ChannelReportNotes
FROM stage_Channel WHERE (Timescheduled IS NULL
OR Timescheduled < NOW())
AND (Status IS NULL OR Status = '')
ORDER BY Guid
SELECT Email, Guid, ChannelDescription,
ChannelNotes, ChannelReportNotes
FROM stage_Channel WHERE Channel = '{channel}'
AND (Timescheduled IS NULL
OR Timescheduled < NOW())
AND (Status IS NULL OR Status = '')
ORDER BY Guid
UPDATE stage_channel SET Timecompleted = NOW(),
LogMessage = '{log_message}',
LogMessageText = '{log_message_text}',
LogTitle = '{log_title}',
LogSmtpServer = '{smtp_server}',
LogSmtpSender = '{smtp_sender}',
LogSmtpUser = '{smtp_user}',
Status = '{status}'
WHERE Guid = '{guid}'
UPDATE stage_channel SET Timecompleted = NOW(),
LogMessage = '{log_message}',
LogMessageText = '{log_message_text}',
LogTitle = '{log_title}',
Status = '{status}'
WHERE Guid = '{guid}'
SELECT * FROM Stage_channel WHERE Guid = '{guid}'
UPDATE stage_channel SET Status = 'DONE',
TimeCompleted = NOW(),
LogMessageText = 'BULK MAIL'
WHERE Guid = '{guid}'
UPDATE Stage_Channel SET Status = '' WHERE Status = 'QUEUED'
SELECT DISTINCT Channel FROM `{def_table}`
UPDATE `{bloom_table}` AS B LEFT JOIN track_channel AS T
ON B.guid = T.Uuid
SET T.Clickthrough = 'Y',
T.Clickdate = B.CreateTime,
T.RemoteIp = B.bloom_RemoteIp
WHERE T.Uuid IS NOT NULL
AND T.Clickthrough = 'N'
CREATE TABLE `{bloom_table}` (
`Guid` CHAR(50) NULL DEFAULT NULL
COLLATE 'latin1_general_ci',
`Createtime` DATETIME NULL DEFAULT NULL,
`Senddate` DATETIME NULL DEFAULT NULL,
`bloom_RemoteIp` CHAR(50) NULL DEFAULT NULL
COLLATE 'latin1_general_ci',
`channel` CHAR(255) NULL DEFAULT NULL
COLLATE 'latin1_general_ci',
`emailguid` CHAR(255) NULL DEFAULT NULL
COLLATE 'latin1_general_ci'
) ENGINE=Aria DEFAULT CHARSET=latin1
COLLATE=latin1_general_ci
INSERT INTO stage_channel ({keys}) VALUES ({values})
SELECT Email, 0, '' AS descript,
'' AS report, '' AS reportnotes
FROM def_ChannelMail WHERE Channel = '{channel}' AND Active = 'Y'
AND DATEDIFF(NOW(), Senddate) > {age}
ORDER BY uuid