CREATE TABLE `def_sms` (
`Smscode` char(255) NOT NULL,
`Strategy_id` char(25) NOT NULL,
`Block` char(36) NOT NULL DEFAULT 'ADHOC',
`Package` char(36) DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`LastCheckTime` datetime DEFAULT NULL,
`InclMon` char(1) DEFAULT NULL,
`InclTue` char(1) DEFAULT NULL,
`InclWed` char(1) DEFAULT NULL,
`InclThu` char(1) DEFAULT NULL,
`InclFri` char(1) DEFAULT NULL,
`InclSat` char(1) DEFAULT NULL,
`InclSun` char(1) DEFAULT NULL,
`UserID` char(50) DEFAULT NULL,
`DeliveryTimeCheck` bigint(20) DEFAULT 600,
`NumberCheckRegex` char(255) DEFAULT NULL,
`Provider` char(50) DEFAULT NULL,
`crweb` char(50) DEFAULT NULL,
`Infrastructure` char(1) DEFAULT '',
`Description` longtext DEFAULT NULL,
`Company` char(255) DEFAULT NULL,
`NotBefore` time DEFAULT '07:00:00',
`Threads` int(11) DEFAULT 25,
`SMSconnection` char(255) DEFAULT NULL,
`ActionActive` char(1) DEFAULT 'Y',
`FailedWebhook` char(255) DEFAULT '',
`Limit` bigint(20) DEFAULT NULL,
`DeliveryWebhook` char(255) DEFAULT '',
`NoLaterThan` time DEFAULT '22:00:00',
`DailyLimit` bigint(20) DEFAULT NULL,
`CheckReplies` char(2) DEFAULT NULL,
`Message` longtext DEFAULT NULL,
`ShortCode` char(50) DEFAULT NULL,
`Result1Col` char(255) DEFAULT '',
`Result2Col` char(255) DEFAULT '',
`Result3Col` char(255) DEFAULT '',
`Result4Col` char(255) DEFAULT '',
`Result5Col` char(255) DEFAULT '',
`Result6Col` char(255) DEFAULT '',
`Result7Col` char(255) DEFAULT '',
`Result8Col` char(255) DEFAULT '',
`Result9Col` char(255) DEFAULT '',
`CellnumberCol` char(255) DEFAULT '',
`CountryRules` char(255) DEFAULT NULL,
`subblock` char(255) DEFAULT NULL,
`Checkoutname` char(50) DEFAULT NULL,
`SqlGetParams` mediumtext DEFAULT NULL,
PRIMARY KEY (
`Smscode`, `Strategy_id`, `Block`
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE IF NOT EXISTS `stage_sms` (
`Guid` char(255) NOT NULL,
`Strategy_id` char(25) NOT NULL,
`SMSCode` char(255) DEFAULT NULL,
`Package` char(255) DEFAULT NULL,
`Channel` char(255) DEFAULT 'SMS',
`Status` char(50) DEFAULT NULL,
`StatusError` char(255) DEFAULT NULL,
`Cellnumber` char(50) DEFAULT NULL,
`Message` text DEFAULT NULL,
`SMSPayload` text DEFAULT NULL,
`SMSResult` text DEFAULT NULL,
`SMSResultCode` char(50) DEFAULT NULL,
`Result` char(255) DEFAULT NULL,
`Result1` text DEFAULT NULL,
`Result2` text DEFAULT NULL,
`Result3` text DEFAULT NULL,
`Delivery` char(50) DEFAULT NULL,
`ReplyStatus` char(50) DEFAULT NULL,
`Reply` char(255) DEFAULT NULL,
`Replyid` char(255) DEFAULT NULL,
`Replyfrom` char(255) DEFAULT NULL,
`SourceGuid` char(255) DEFAULT NULL,
`ReturnGuid` char(255) NOT NULL DEFAULT '',
`ReturnPid` int(11) NOT NULL DEFAULT 0,
`PersonNo` int(11) DEFAULT NULL,
`Campaign_id` int(11) DEFAULT NULL,
`NodeId` bigint(20) NOT NULL DEFAULT 0,
`RDG` int(11) DEFAULT NULL,
`DataIdnumber` char(50) DEFAULT NULL,
`ActionPortfolio` char(255) DEFAULT NULL,
`RemoteConnection` char(255) DEFAULT NULL,
`LiveStatus` char(50) DEFAULT NULL,
`CreditsLeft` int(11) DEFAULT -1,
`LogMessage` text DEFAULT NULL,
`Param1` text DEFAULT NULL,
`Param2` text DEFAULT NULL,
`Param3` text DEFAULT NULL,
`Param4` text DEFAULT NULL,
`Param5` text DEFAULT NULL,
`Param6` text DEFAULT NULL,
`Param7` text DEFAULT NULL,
`Param8` text DEFAULT NULL,
`Param9` text DEFAULT NULL,
`Thread` int(11) DEFAULT 0,
`WorkerPid` int(11) NOT NULL DEFAULT 0,
`DispatcherName` char(255) DEFAULT NULL,
`DispatcherPid` int(11) NOT NULL DEFAULT 0,
`Instance` char(255) DEFAULT NULL,
`Nodename` char(255) DEFAULT NULL,
`Checkoutname` char(255) DEFAULT NULL,
`CPULoad` int(11) DEFAULT 0,
`MemoryLoad` int(11) DEFAULT 0,
`DiskLoad` int(11) DEFAULT 0,
`TimeScheduled` datetime DEFAULT NULL,
`TimeAdded` timestamp NULL DEFAULT current_timestamp(),
`TimeThreaded` datetime DEFAULT NULL,
`TimeStarted` datetime DEFAULT NULL,
`TimeSent` datetime DEFAULT NULL,
`TimeCompleted` datetime DEFAULT NULL,
`TimeReplied` datetime DEFAULT NULL,
`TimeDelivered` datetime DEFAULT NULL,
PRIMARY KEY (`Guid`),
UNIQUE KEY `uk_strategy_source_sms_cell` (`Strategy_id`, `SourceGuid`, `SMSCode`, `Cellnumber`),
KEY `idx_status` (`Status`),
KEY `idx_cellnumber` (`Cellnumber`),
KEY `idx_result` (`Result`),
KEY `idx_smscode` (`SMSCode`),
KEY `idx_timescheduled` (`TimeScheduled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `stage_sms_delivery` (
`ChangeId` bigint(20) NOT NULL,
`SMSCode` char(50) DEFAULT NULL,
`CustomerGuid` char(255) DEFAULT NULL,
`CellNumber` char(20) DEFAULT NULL,
`Provider` char(50) DEFAULT NULL,
`Status` char(20) DEFAULT NULL,
`Smstype` char(10) DEFAULT NULL,
`StatusDate` datetime DEFAULT NULL,
PRIMARY KEY (`ChangeId`),
KEY `idx_provider_smscode` (`Provider`, `SMSCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `stage_sms_replies` (
`ReplyId` bigint(20) NOT NULL,
`SMSCode` char(50) DEFAULT NULL,
`CustomerGuid` char(255) DEFAULT NULL,
`CellNumber` char(20) DEFAULT NULL,
`Provider` char(50) DEFAULT NULL,
`Eventid` char(255) DEFAULT NULL,
`ReceivedDate` datetime DEFAULT NULL,
`ReplyText` mediumtext DEFAULT NULL,
`ReplySentDate` datetime DEFAULT NULL,
PRIMARY KEY (`ReplyId`),
KEY `idx_provider_smscode` (`Provider`, `SMSCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `stage_sms_tracking` (
`Guid` char(255) NOT NULL,
`Strategy_id` char(25) DEFAULT NULL,
`SMSCode` char(255) DEFAULT NULL,
`Cellnumber` char(50) DEFAULT NULL,
`Status` char(50) DEFAULT NULL,
`Result` char(255) DEFAULT NULL,
`SentMessage` mediumtext DEFAULT NULL,
`Nodename` char(255) DEFAULT NULL,
`Checkoutname` char(255) DEFAULT NULL,
`Param1` char(255) DEFAULT NULL,
`Param2` char(255) DEFAULT NULL,
`Param3` char(255) DEFAULT NULL,
`Param4` char(255) DEFAULT NULL,
`Param5` char(255) DEFAULT NULL,
`Param6` char(255) DEFAULT NULL,
`Param7` char(255) DEFAULT NULL,
`Param8` char(255) DEFAULT NULL,
`Param9` char(255) DEFAULT NULL,
`TimeScheduled` datetime DEFAULT NULL,
PRIMARY KEY (`Guid`),
KEY `idx_status` (`Status`),
KEY `idx_cellnumber` (`Cellnumber`),
KEY `idx_result` (`Result`),
KEY `idx_smscode` (`SMSCode`),
KEY `idx_timescheduled` (`TimeScheduled`),
KEY `idx_param1` (`Param1`),
KEY `idx_param2` (`Param2`),
KEY `idx_param3` (`Param3`),
KEY `idx_param4` (`Param4`),
KEY `idx_param5` (`Param5`),
KEY `idx_param6` (`Param6`),
KEY `idx_param7` (`Param7`),
KEY `idx_param8` (`Param8`),
KEY `idx_param9` (`Param9`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
AND case
when D.InclMon = 'Y'
and dayname(ifnull(S.Timescheduled,now()))
= 'Monday' then True
when D.InclTue = 'Y'
and dayname(ifnull(S.Timescheduled,now()))
= 'Tuesday' then True
when D.InclWed = 'Y'
and dayname(ifnull(S.Timescheduled,now()))
= 'Wednesday' then True
when D.InclThu = 'Y'
and dayname(ifnull(S.Timescheduled,now()))
= 'Thursday' then True
when D.InclFri = 'Y'
and dayname(ifnull(S.Timescheduled,now()))
= 'Friday' then True
when D.InclSat = 'Y'
and dayname(ifnull(S.Timescheduled,now()))
= 'Saturday' then True
when D.InclSun = 'Y'
and dayname(ifnull(S.Timescheduled,now()))
= 'Sunday' then True
else false end
UPDATE stage_sms
SET {time_column} = now(),
{status_column} = '{status}'
{sms_result_clause}
{log_message_clause}
WHERE Guid = '{guid}'
{move_condition}
SELECT NotBefore, NoLaterThan,
InclMon, InclTue, InclWed, InclThu,
InclFri, InclSat, InclSun
FROM {def_sms_table} AS D
WHERE Smscode = '{sms_code}'
AND M.Package IN ('{package}', '{archetype_package}')
UPDATE stage_Sms
SET TimeScheduled = now()
WHERE TimeScheduled IS NULL
SELECT sum(ct) FROM (
SELECT count(*) AS ct
FROM stage_sms
WHERE date(TimeScheduled) = date(now())
AND Smscode = '{sms_code}'
AND ifnull(Status, '') > ''
AND M.Package IN
('{package}', '{archetype_package}')
UNION ALL
SELECT count(*)
FROM stage_sms_history
WHERE date(TimeScheduled) = date(now())
AND Smscode = '{sms_code}'
AND ifnull(Status, '') > ''
AND M.Package IN
('{package}', '{archetype_package}')
) AS A
SELECT sum(ct) - 1 FROM (
SELECT count(*) AS ct
FROM stage_sms
WHERE date(TimeScheduled) = date(now())
AND Cellnumber IN ({cellnumber_arr})
AND Smscode = '{sms_code}'
AND ifnull(Status, '') > ''
UNION ALL
SELECT count(*)
FROM stage_sms_history
WHERE date(TimeScheduled) = date(now())
AND Cellnumber IN ({cellnumber_arr})
AND Smscode = '{sms_code}'
AND ifnull(Status, '') > ''
) AS A
INSERT IGNORE INTO stage_sms(Guid)
VALUES ('{guid}')
UPDATE stage_sms
SET Status = '{status}',
TimeSent = now(),
TimeCompleted = now(),
SMSResult = '{sms_result}'
WHERE Guid = '{guid}'
SELECT * FROM {def_sms_table} AS D
WHERE Smscode = '{sms_code}'
AND coalesce(D.Package, 'SYSTEM')
IN ('{package}', '{archetype_package}')
INSERT IGNORE INTO stage_sms(
Guid, Status, TimeScheduled, Channel
)
VALUES (
'{guid}', 'DIRECT', now(), '{sms_code}'
)
UPDATE stage_sms
SET Cellnumber = '{cellnumber}'
WHERE Guid = '{guid}'
UPDATE stage_sms
SET Timethreaded = now()
{param_clauses}
WHERE Guid = '{guid}'
SELECT * FROM stage_sms
WHERE Guid = '{guid}'
SELECT DefaultSMSConnection
FROM def_package
WHERE Package = '{package}'