CREATE TABLE `def_conversation` (
`ConversationCode` char(255) NOT NULL,
`Package` char(255) DEFAULT NULL,
`WebBanner` char(255) DEFAULT NULL,
`WebDevice` varchar(255) DEFAULT NULL,
`WebDeviceColor` varchar(50) DEFAULT NULL,
`Avatar` char(255) DEFAULT NULL,
`BaseTable` char(255) DEFAULT NULL,
`BaseURL` char(255) DEFAULT NULL,
`Glyph` char(255) DEFAULT NULL,
`RemoteConnection` char(255) DEFAULT NULL,
`Title` char(255) DEFAULT NULL,
`Description` text DEFAULT NULL,
`Msisdn` char(50) DEFAULT NULL,
`ConversationExpireTime` int(11) DEFAULT 14400,
`RoleText` TEXT DEFAULT NULL,
`ConstantsTable` varchar(255) DEFAULT NULL,
`DefaultLang` varchar(10) DEFAULT 'EN',
`OnStartWorkflow` varchar(255) DEFAULT NULL,
`HelpText` varchar(255) DEFAULT NULL,
`RestartText` varchar(255) DEFAULT NULL,
`Palette` varchar(255) DEFAULT 'catppuccin_macchiato',
`AiModel` varchar(255) DEFAULT NULL,
`ContextSql` text DEFAULT NULL,
`ContactIdSql` text DEFAULT NULL,
`Userid` varchar(255) DEFAULT NULL,
`Active` char(2) DEFAULT 'Y',
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
`ModifiedDate` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ConversationCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_conversation_detail` (
`ConversationCode` varchar(255) NOT NULL,
`Package` varchar(255) NOT NULL DEFAULT 'CORE',
`Module` VARCHAR(255) DEFAULT NULL,
`Channel` varchar(50) NOT NULL,
`Botname` varchar(255) DEFAULT NULL,
`Template` varchar(255) DEFAULT NULL,
`OnWelcomeMessage` text DEFAULT NULL,
`OnUploadSql` text DEFAULT NULL,
`OnUploadMessage` text DEFAULT NULL,
`OnHelpMessage` text DEFAULT NULL,
`OnDigitMessage` text DEFAULT NULL,
`OnTextMessage` text DEFAULT NULL,
`OnLocationMessage` text DEFAULT NULL,
`PageLength` int(11) DEFAULT 5,
`BotEmoji` varchar(50) DEFAULT NULL,
`BotAvatar` varchar(500) DEFAULT NULL,
`ApiToken` varchar(255) DEFAULT NULL,
`BotActivation` varchar(255) DEFAULT NULL,
`ErrorMessage` text DEFAULT NULL,
`TimeoutMessage` text DEFAULT NULL,
`TimeoutSeconds` int(11) DEFAULT 300,
PRIMARY KEY (`ConversationCode`,`Package`,`Channel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_conversation_page` (
`ConversationCode` char(255) NOT NULL,
`Package` char(255) DEFAULT NULL,
`PageNo` int(11) NOT NULL,
`HeaderText` text DEFAULT NULL,
`Option1` text DEFAULT NULL,
`Option1Callback` text DEFAULT NULL,
`Option1Display` char(50) DEFAULT NULL,
`Option2` text DEFAULT NULL,
`Option2Callback` text DEFAULT NULL,
`Option2Display` char(50) DEFAULT NULL,
`Option3` text DEFAULT NULL,
`Option3Callback` text DEFAULT NULL,
`Option3Display` char(50) DEFAULT NULL,
`Option4` text DEFAULT NULL,
`Option4Callback` text DEFAULT NULL,
`Option4Display` char(50) DEFAULT NULL,
`Option5` text DEFAULT NULL,
`Option5Callback` text DEFAULT NULL,
`Option5Display` char(50) DEFAULT NULL,
`Option6` text DEFAULT NULL,
`Option6Callback` text DEFAULT NULL,
`Option6Display` char(50) DEFAULT NULL,
`OptionOverrideSql` text DEFAULT NULL,
`OptionOverrideShow` text DEFAULT NULL,
`OptionOverridePossibleCallbacks` text DEFAULT NULL,
`PreSql` text DEFAULT NULL,
`PostSql` text DEFAULT NULL,
`RerouteSql` text DEFAULT NULL,
`PageName` VARCHAR(255) DEFAULT NULL,
`RerouteWorkflow` VARCHAR(255) DEFAULT NULL,
`HeaderReport` VARCHAR(255) DEFAULT NULL,
`HeaderReport2` VARCHAR(255) DEFAULT NULL,
`HeaderReport3` VARCHAR(255) DEFAULT NULL,
`AiPrompt` TEXT DEFAULT NULL,
`AiPromptMode` VARCHAR(10) DEFAULT 'NOTE',
`AiPromptFallback` TEXT DEFAULT NULL,
`TimeoutSeconds` int(11) DEFAULT 0,
`MaxRetries` int(11) DEFAULT 3,
`HeaderImage` varchar(500) DEFAULT NULL,
`MediaUrl` varchar(500) DEFAULT NULL,
`PageBanner` varchar(500) DEFAULT NULL,
`OptionContextVar` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ConversationCode`, `PageNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_constant` (
`ConstantCode` varchar(255) NOT NULL,
`Package` varchar(255) NOT NULL DEFAULT 'CORE',
`Module` VARCHAR(255) DEFAULT NULL,
`Value` text DEFAULT NULL,
`Description` text DEFAULT NULL,
PRIMARY KEY (`ConstantCode`, `Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `list_conversation_types` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `meta_saleswatch_user` (
`User` VARCHAR(255) NOT NULL,
`Package` VARCHAR(255) NOT NULL DEFAULT 'HOMECHOICE',
`SrId` INT NOT NULL,
`StoreId` INT NOT NULL,
`RepName` VARCHAR(255) DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT 'SALESWATCH',
PRIMARY KEY (`User`, `Package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `data_web_conversation` (
`Id` INT AUTO_INCREMENT PRIMARY KEY,
`ConversationCode` VARCHAR(255) NOT NULL,
`Package` VARCHAR(255) NOT NULL,
`ContactId` VARCHAR(255) NOT NULL,
`ConversationGuid` VARCHAR(36) NOT NULL,
`Senderid` VARCHAR(255) NOT NULL,
`Timestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,
`Messagetype` VARCHAR(50) DEFAULT 'TEXT',
`Message` TEXT,
INDEX `idx_contact_ts` (`ContactId`, `Timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
SELECT Value FROM def_constant WHERE ConstantCode = '{constant_code}'
AND Package IN ('{package}', 'CORE')
ORDER BY (Package != 'CORE') DESC LIMIT 1
SELECT * FROM def_conversation WHERE ConversationCode = '{conversation_code}' AND Package IN ('{package}', 'CORE') ORDER BY (Package != 'CORE') DESC LIMIT 1
SELECT Channel, Botname, OnWelcomeMessage, PageLength FROM def_conversation_detail WHERE ConversationCode = '{conversation_code}' AND Package IN ('{package}', 'CORE') ORDER BY (Package != 'CORE') DESC
SELECT PageNo, HeaderText, OptionOverrideSql FROM def_conversation_page WHERE ConversationCode = '{conversation_code}' ORDER BY PageNo
SELECT COUNT(*) FROM def_conversation WHERE ConversationCode = '{conversation_code}' AND Package = '{package}'
SELECT COUNT(*) FROM def_conversation_detail WHERE ConversationCode = '{conversation_code}' AND Package = '{package}' AND Channel = 'CONSOLE'
INSERT INTO def_conversation_detail (ConversationCode, Package, Channel, Botname, OnWelcomeMessage, OnHelpMessage, PageLength) VALUES ('{conversation_code}', '{package}', 'CONSOLE', '{conversation_code}', 'Welcome to the {conversation_code} conversation.', 'Please select one of the options.', 5)
SELECT * FROM def_Conversation c LEFT JOIN def_conversation_detail cd
ON c.ConversationCode = cd.ConversationCode
AND cd.Channel = '{channel}'
AND cd.Package IN ('{package}', 'CORE', '')
WHERE c.ConversationCode = '{conversation_code}'
AND c.Package IN ('{package}', 'CORE', '')
ORDER BY CASE
WHEN c.Package = '{package}' THEN 1
WHEN c.Package = 'CORE' THEN 2
WHEN c.Package = '' THEN 3
ELSE 4
END LIMIT 1
SELECT ConversationCode, PageNo, HeaderText,
Option1, Option1Callback, Option1Display,
Option2, Option2Callback, Option2Display,
Option3, Option3Callback, Option3Display,
Option4, Option4Callback, Option4Display,
Option5, Option5Callback, Option5Display,
OptionOverrideSql, OptionOverrideShow, OptionOverridePossibleCallbacks,
PreSql, PostSql, RerouteSql, HeaderImage, MediaUrl,
PageName, RerouteWorkflow, HeaderReport, HeaderReport2,
HeaderReport3, AiPrompt, AiPromptMode,
PageBanner, OptionContextVar
FROM def_conversation_page WHERE Conversationcode = '{conversation_code}'
AND Pageno = '{page_no}'
AND Package IN ('{package}', 'CORE', '')
ORDER BY CASE
WHEN Package = '{package}' THEN 1
WHEN Package = 'CORE' THEN 2
WHEN Package = '' THEN 3
ELSE 4
END LIMIT 1
SELECT PageNo FROM def_conversation_page WHERE ConversationCode = '{conversation_code}'
AND PageName = '{page_name}'
AND Package IN ('{package}', 'CORE')
ORDER BY (Package != 'CORE') DESC LIMIT 1
CREATE TABLE `data_conversation_{conversation_code}` (
`Guid` CHAR(255) NULL,
`CreateTime` DATETIME NULL,
`UpdateTime` DATETIME NULL,
`Channel` char(255) default '',
`MsIsdn` char(255) default '',
`RDG` int,
PRIMARY KEY (`Guid`)
) COLLATE={collation}
INSERT IGNORE INTO data_conversation_{conversation_code}
(Guid,Channel,MsIsdn,Rdg,CreateTime)
VALUES
('{guid}','{channel}','{msisdn}','{rdg}',now())
UPDATE data_conversation_{conversation_code} SET {field_request_name} = '{request_text}', Channel ='{channel}', Sender = '{sender}', MsIsdn = '{msisdn}', {field_requestno_name} = '{request_no}', UpdateTime = now() WHERE Guid = '{guid}'
SELECT USER FROM sys_user WHERE Usergroups = '{user_group}' AND Package = '{package}'
SELECT * FROM sys_user WHERE User = '{user}'
INSERT INTO stage_notify (
Guid, NotifyCode, Package, User,
EventTimeSent, Message
) VALUES (
'{guid}', '{notify_code}', '{package}', '{user}',
now(), '{message}'
)
SELECT CONCAT(NotifyCode, ' : ', COALESCE(DESCRIPTION, NotifyCode)) AS DESCRIPTION FROM def_notify WHERE Package IN ('{package}', 'CORE')
CREATE TABLE `data_conversation_contact_{conversation_code}` (
`ConversationCode` varchar(255) NOT NULL,
`Package` varchar(255) NOT NULL,
`SenderId` varchar(255) NOT NULL,
`ConversationGuid` varchar(255) DEFAULT NULL,
`Channel` varchar(50) DEFAULT NULL,
`Firstname` varchar(255) DEFAULT NULL,
`Lastname` varchar(255) DEFAULT NULL,
`PageNo` int(11) DEFAULT 0,
`Offset` int(11) DEFAULT 0,
`ExtraContext` TEXT DEFAULT NULL,
`LastUpdate` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`ConversationCode`,`Package`,`SenderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SELECT ConversationGuid, PageNo, `Offset`, LastUpdate, Channel, Firstname, Lastname, ExtraContext FROM data_conversation_contact_{conversation_code} WHERE Conversationcode = '{conversation_code}'
AND Package LIKE '{package}'
AND SenderId = '{contact_id}'
INSERT INTO data_conversation_contact_{conversation_code} (ConversationCode, Package, SenderId, ConversationGuid, Channel, PageNo, `Offset`) VALUES ('{conversation_code}', '{package}', '{contact_id}', '{guid}', '{channel}', 1, 0)
UPDATE data_conversation_contact_{conversation_code} SET PageNo = '{page_no}', `Offset` = '{offset}',
ExtraContext = CASE WHEN '{extra_context}' = '' THEN ExtraContext ELSE '{extra_context}' END
WHERE Conversationcode = '{conversation_code}'
AND Package LIKE '{package}'
AND SenderId = '{contact_id}'
UPDATE data_conversation_contact_{conversation_code} SET ConversationGuid = '{guid}', PageNo = 1, `Offset` = 0, Channel = '{channel}' WHERE Conversationcode = '{conversation_code}'
AND Package LIKE '{package}'
AND SenderId = '{contact_id}'
SELECT DISTINCT PageNo, HeaderText, OptionText, Callback FROM (
SELECT PageNo, HeaderText, OPTION1 AS OptionText, OPTION1Callback AS Callback
FROM def_conversation_page
WHERE ConversationCode = '{conversation_code}'
UNION ALL
SELECT PageNo, HeaderText, OPTION2, Option2Callback
FROM def_conversation_page
WHERE ConversationCode = '{conversation_code}'
UNION ALL
SELECT PageNo, HeaderText, OPTION3, Option3Callback
FROM def_conversation_page
WHERE ConversationCode = '{conversation_code}'
UNION ALL
SELECT PageNo, HeaderText, OPTION4, Option4Callback
FROM def_conversation_page
WHERE ConversationCode = '{conversation_code}'
UNION ALL
SELECT PageNo, HeaderText, OPTION5, Option5Callback
FROM def_conversation_page
WHERE ConversationCode = '{conversation_code}'
UNION ALL
SELECT PageNo, HeaderText, OptionOverrideShow, OptionOverridePossibleCallbacks
FROM def_conversation_page
WHERE ConversationCode = '{conversation_code}'
) AS options
SELECT CONCAT_WS(' ',
HeaderText, Option1, Option2, Option3, Option4, Option5,
OptionOverrideShow
) AS AllText FROM def_conversation_page WHERE ConversationCode = '{conversation_code}' UNION ALL SELECT CONCAT_WS(' ',
OnWelcomeMessage, OnHelpMessage, OnDigitMessage,
OnTextMessage, OnLocationMessage, OnUploadMessage,
TimeoutMessage, ErrorMessage
) AS AllText FROM def_conversation_detail WHERE ConversationCode = '{conversation_code}'
- emoji_placeholders:
['{host}', '{house}', '{receipt}', '{ticket}', '{map}', '{gift}', '{sms}', '{oops}', '{burger}']
- help_command:
H
- help_text:
Send *H* for help
- restart_command:
R
- restart_text:
Send *R* to restart
- fuzzy_match_threshold:
75
- hint_select:
[[AF:Kies]][[EN:Select]]
- hint_help:
[[AF:Hulp]][[EN:Help]]
- hint_restart:
[[AF:Herlaai]][[EN:Restart]]
- hint_lang:
[[AF:Taal]][[EN:Lang]]
- hint_quit:
[[AF:Sluit]][[EN:Quit]]
- hint_awaiting:
[[AF:Wag vir]][[EN:Awaiting]]