CREATE TABLE `def_webhook` (
`WebhookCode` varchar(255) NOT NULL,
`Package` varchar(100) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`Block` varchar(255) NOT NULL DEFAULT '',
`Direction` varchar(5) NOT NULL DEFAULT 'IN',
`Type` varchar(255) DEFAULT 'INIT',
`Description` varchar(255) DEFAULT '',
`Notes` text DEFAULT NULL,
`AiSummary` text DEFAULT NULL COMMENT 'AI-generated webhook summary',
`AiPurpose` text DEFAULT NULL COMMENT 'AI-generated purpose description',
`AiUseCases` text DEFAULT NULL COMMENT 'AI-generated use cases',
`AiRelatedWorkflows` text DEFAULT NULL COMMENT 'AI-identified related workflows',
`PayloadType` varchar(255) DEFAULT 'JSON',
`Httpmethod` varchar(255) DEFAULT 'POST',
`BaseUrl` varchar(255) DEFAULT NULL,
`BaseUrlPrecall` varchar(255) DEFAULT NULL,
`Headers` varchar(255) DEFAULT '',
`User` varchar(255) DEFAULT '',
`Password` varchar(255) DEFAULT '',
`ApiKey` varchar(255) DEFAULT '',
`Timeout` int(11) DEFAULT 60,
`Sleep` int(11) DEFAULT 0,
`Virtual` char(1) DEFAULT 'N',
`Virtualresponse` text DEFAULT NULL,
`Returntype` varchar(50) DEFAULT NULL,
`RemoteConnection` varchar(255) DEFAULT '',
`Buildstructure` char(1) DEFAULT 'Y',
`Buildpayload` char(1) DEFAULT 'N',
`Buildvalidation` char(1) DEFAULT 'N',
`Buildoutputvalidation` char(1) DEFAULT 'N',
`Async_buildpayload` char(1) DEFAULT NULL,
`DataTable` varchar(255) DEFAULT '',
`DataKey` varchar(255) DEFAULT '',
`Dataidnumber` varchar(255) DEFAULT '',
`DataTableFinal` varchar(255) DEFAULT '',
`HookLookupSql` mediumtext DEFAULT NULL,
`HookRedirectSql` mediumtext DEFAULT NULL,
`Hookpayloadsql` text DEFAULT NULL,
`PreSql` mediumtext DEFAULT NULL,
`PostSql` mediumtext DEFAULT NULL,
`SetPreSql` mediumtext DEFAULT NULL,
`Result1Field` varchar(255) DEFAULT '',
`Result2Field` varchar(255) DEFAULT '',
`Result3Field` varchar(255) DEFAULT '',
`Isform` char(1) DEFAULT 'N',
`Formfieldname` varchar(255) DEFAULT NULL,
`DocumentPDF` varchar(255) DEFAULT '',
`DocumentMedia` text DEFAULT NULL,
`DocumentMediaPrefix` varchar(255) DEFAULT '',
`Termsofservice` text DEFAULT NULL,
`Version` varchar(50) DEFAULT NULL,
`Rank` int(11) DEFAULT 0,
`Infrastructure` char(1) DEFAULT 'N',
`NotifyOnCompletion` char(1) DEFAULT 'N' COMMENT 'Send notification on successful completion',
`NotifyOnError` char(1) DEFAULT 'N' COMMENT 'Send notification on error',
`NotifyCompletionCode` varchar(100) DEFAULT '' COMMENT 'Notification code for completion',
`NotifyErrorCode` varchar(100) DEFAULT '' COMMENT 'Notification code for errors',
`Active` char(1) DEFAULT 'Y',
PRIMARY KEY (`WebhookCode`, `Package`, `Block`),
KEY `idx_package` (`Package`),
KEY `idx_direction` (`Direction`),
KEY `idx_type` (`Type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_webhook_parameters` (
`WebhookCode` varchar(150) NOT NULL,
`Package` varchar(100) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`Parameter` varchar(200) NOT NULL,
`Block` varchar(150) NOT NULL DEFAULT '',
`Rank` int(11) DEFAULT 0,
`PostType` varchar(50) NOT NULL DEFAULT 'POST',
`ParameterType` varchar(50) DEFAULT NULL,
`ValueType` varchar(50) DEFAULT NULL,
`Required` char(1) DEFAULT NULL,
`DefaultValue` text DEFAULT NULL,
`ParameterBlock` varchar(255) DEFAULT 'N',
`ParameterSubBlock` varchar(255) DEFAULT 'N',
`ParameterSubSubBlock` varchar(255) DEFAULT NULL,
`ParameterSubSubSubBlock` varchar(255) DEFAULT NULL,
`NormalParameter` varchar(255) DEFAULT '',
`DisplayName` varchar(255) DEFAULT NULL,
`Description` mediumtext DEFAULT NULL,
`Example` varchar(255) DEFAULT NULL,
`AiDescription` text DEFAULT NULL COMMENT 'AI-enhanced parameter description',
`AiValidationRules` text DEFAULT NULL COMMENT 'AI-identified validation rules',
PRIMARY KEY (`WebhookCode`, `Package`, `Parameter`, `Block`),
KEY `idx_package` (`Package`),
KEY `idx_posttype_webhook` (`PostType`, `WebhookCode`),
KEY `idx_rank` (`Rank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_webhook_validations` (
`ValidationGuid` varchar(36) NOT NULL,
`WebhookCode` varchar(255) NOT NULL,
`Package` varchar(100) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`Description` varchar(255) DEFAULT NULL,
`Rank` int(11) NOT NULL DEFAULT 1,
`ValidationSql` mediumtext DEFAULT NULL,
`ValidationNote` varchar(255) DEFAULT NULL,
`RemoteConnection` varchar(255) DEFAULT '',
`Active` char(1) DEFAULT 'Y',
PRIMARY KEY (`ValidationGuid`),
UNIQUE KEY `idx_webhook_rank` (`WebhookCode`, `Package`, `Rank`),
KEY `idx_package` (`Package`),
KEY `idx_webhookcode` (`WebhookCode`),
KEY `idx_active` (`Active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_webhook_output_validations` (
`ValidationGuid` varchar(36) NOT NULL,
`WebhookCode` varchar(255) NOT NULL,
`Package` varchar(100) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`Description` varchar(255) DEFAULT NULL,
`Rank` int(11) NOT NULL DEFAULT 1,
`ValidationSql` mediumtext DEFAULT NULL,
`ValidationNote` varchar(255) DEFAULT NULL,
`RemoteConnection` varchar(255) DEFAULT '',
`Active` char(1) DEFAULT 'Y',
PRIMARY KEY (`ValidationGuid`),
UNIQUE KEY `idx_webhook_rank` (`WebhookCode`, `Package`, `Rank`),
KEY `idx_package` (`Package`),
KEY `idx_webhookcode` (`WebhookCode`),
KEY `idx_active` (`Active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_webhook_batch` (
`WebhookCode` char(255) NOT NULL,
`BatchName` char(255) NOT NULL,
`Package` char(255) NOT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`Active` char(255) DEFAULT NULL,
`InputTable` char(255) DEFAULT NULL,
`InputGuid` char(255) DEFAULT NULL,
`PinpointsTable` char(255) DEFAULT NULL,
`PinpointsGuid` char(255) DEFAULT NULL,
`CompletedFilter` char(255) DEFAULT NULL,
`BatchSignalName` char(255) DEFAULT NULL,
`BatchSignalValue` char(255) DEFAULT NULL,
`BatchLimit` int(11) DEFAULT NULL,
PRIMARY KEY (
`WebhookCode`, `BatchName`, `Package`
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE `def_webhook_constants` (
`WebhookCode` char(255) NOT NULL,
`Package` char(255) NOT NULL,
`ConstantTable` char(255) NOT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`Rank` int(11) NOT NULL DEFAULT 0,
`Description` text DEFAULT NULL,
PRIMARY KEY (
`WebhookCode`, `Package`, `ConstantTable`
) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE `def_webhook_reflections` (
`WebhookCode` char(255) NOT NULL,
`ServiceCode` char(255) NOT NULL,
`Block` char(36) NOT NULL DEFAULT 'ADHOC',
`Rank` int(11) NOT NULL,
`Strategy_id` char(25) NOT NULL DEFAULT '-1',
`Package` char(36) DEFAULT NULL,
`Module` VARCHAR(255) DEFAULT NULL,
`ParameterMask` char(255) DEFAULT NULL,
`Description` mediumtext DEFAULT NULL,
`Notes` mediumtext DEFAULT NULL,
`ParameterField` char(255) DEFAULT NULL,
`RemoteUrl` char(255) DEFAULT NULL,
`Active` char(1) DEFAULT 'Y',
`Mirrorcode` char(255) DEFAULT NULL,
`Mirrordelay` int(11) DEFAULT NULL,
`LookupSql` mediumtext DEFAULT NULL,
`RealtimeFlag` char(1) DEFAULT 'n',
PRIMARY KEY (
`WebhookCode`, `ServiceCode`,
`Block`, `Rank`, `Strategy_id`
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE={collation};
CREATE TABLE IF NOT EXISTS `stage_webhook` (
`Webhook_id` int(11) NOT NULL AUTO_INCREMENT,
`Guid` char(255) NOT NULL DEFAULT '0',
`WebhookCode` char(100) NOT NULL DEFAULT '',
`ServiceCode` char(255) NOT NULL DEFAULT '',
`Package` char(50) DEFAULT NULL,
`Strategy_id` char(25) NOT NULL,
`Status` char(50) DEFAULT '',
`ResultCode` char(10) DEFAULT '',
`ResultMessage` char(255) DEFAULT '',
`ResultIdvalidation` char(50) DEFAULT NULL,
`Response` mediumtext DEFAULT NULL,
`CallLog` mediumtext DEFAULT NULL,
`CallStruct` mediumtext DEFAULT NULL,
`PayloadType` char(255) DEFAULT 'JSON',
`HookLookupSql` longtext DEFAULT NULL,
`HookPayloadSql` mediumtext DEFAULT NULL,
`SourceGuid` char(255) DEFAULT NULL,
`Param1` mediumtext DEFAULT '',
`Param2` mediumtext DEFAULT '',
`Param3` mediumtext DEFAULT '',
`Param4` mediumtext DEFAULT '',
`Param5` mediumtext DEFAULT '',
`Param6` mediumtext DEFAULT '',
`Param7` mediumtext DEFAULT '',
`Param8` mediumtext DEFAULT '',
`Param9` mediumtext DEFAULT '',
`Result1` mediumtext DEFAULT NULL,
`Result2` mediumtext DEFAULT NULL,
`Result3` mediumtext DEFAULT NULL,
`Result4` mediumtext DEFAULT NULL,
`Result5` mediumtext DEFAULT NULL,
`Result6` mediumtext DEFAULT NULL,
`Result7` mediumtext DEFAULT NULL,
`Result8` mediumtext DEFAULT NULL,
`Result9` mediumtext DEFAULT NULL,
`Retries` char(50) NOT NULL DEFAULT '',
`Collated` char(1) DEFAULT 'N',
`LiveStatus` char(50) DEFAULT 'NOTLIVE',
`Async_strategy` char(1) DEFAULT NULL,
`asyncstrategy` char(255) DEFAULT '',
`Fire_strategyId` int(11) NOT NULL DEFAULT 0,
`RunGuid` char(255) NOT NULL DEFAULT '',
`ReturnGuid` char(255) NOT NULL DEFAULT '',
`ReturnPid` int(11) NOT NULL DEFAULT 0,
`Step_id` int(11) DEFAULT 0,
`RDG` int(11) 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,
`StatusError` char(255) DEFAULT NULL,
`TimeScheduled` datetime DEFAULT NULL,
`TimeAdded` timestamp NULL DEFAULT current_timestamp(),
`TimeEntered` datetime DEFAULT current_timestamp(),
`TimeThreaded` datetime DEFAULT NULL,
`TimeStarted` datetime DEFAULT NULL,
`TimeCompleted` datetime DEFAULT NULL,
`TimeResheduled` int(11) DEFAULT 0,
`LastTouch` datetime DEFAULT NULL,
PRIMARY KEY (`Webhook_id`),
KEY `idx_servicecode` (`ServiceCode`),
KEY `idx_guid` (`Guid`),
KEY `idx_timecompleted` (`TimeCompleted`),
KEY `idx_collated` (`Collated`),
KEY `idx_resultmessage` (`ResultMessage`),
KEY `idx_webhookcode` (`WebhookCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE IF NOT EXISTS `stage_webhook_semaphore` (
`Guid` char(255) NOT NULL,
`Strategy_id` char(25) NOT NULL,
`Status` char(50) DEFAULT '',
`Thread` char(50) DEFAULT '',
`WorkerPid` int(11) DEFAULT 0,
`DispatcherPid` int(11) DEFAULT 0,
`LauncherPid` int(11) DEFAULT 0,
`TimeScheduled` datetime DEFAULT NULL,
`TimeStarted` datetime DEFAULT NULL,
PRIMARY KEY (`Guid`),
KEY `idx_status` (`Status`),
KEY `idx_thread` (`Thread`),
KEY `idx_timescheduled` (`TimeScheduled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `def_webhook_workflow_map` (
`WebhookCode` varchar(255) NOT NULL,
`Package` varchar(100) NOT NULL DEFAULT '',
`Module` VARCHAR(255) DEFAULT NULL,
`WorkflowName` varchar(100) NOT NULL,
`FactoryObject` varchar(255) DEFAULT NULL COMMENT 'Factory object that triggers workflow',
`TriggerMethod` varchar(255) DEFAULT NULL COMMENT 'Method that calls workflow',
`ConnectionType` varchar(50) DEFAULT 'DIRECT' COMMENT 'DIRECT, FACTORY, CONDITIONAL',
`Description` text DEFAULT NULL,
`AiFlowDescription` text DEFAULT NULL COMMENT 'AI-generated flow description',
`Active` char(1) DEFAULT 'Y',
PRIMARY KEY (`WebhookCode`, `Package`, `WorkflowName`),
KEY `idx_workflow` (`WorkflowName`),
KEY `idx_factory` (`FactoryObject`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
UPDATE Stage_webhook
SET Status = 'THREAD',
TimeThreaded = NOW(),
ResultCode = ''
WHERE Guid = '{guid}'
SELECT Guid
FROM Stage_webhook
WHERE Guid = '{stage_guid}'
SELECT COALESCE(hooklookupsql, '') AS hooklookupsql,
payloadtype
FROM stage_webhook
WHERE Guid = '{stage_guid}'
UPDATE Stage_webhook
SET Status = 'DONE',
Result1 = '{result1}',
Result2 = '{result2}',
Result3 = '{result3}'
WHERE Guid = '{stage_guid}'
UPDATE Stage_webhook
SET ResultIdvalidation = '{check_pass}'
WHERE Guid = '{stage_guid}'
UPDATE Stage_webhook
SET Status = 'FAIL',
Response = 'NODATASENT'
WHERE Guid = '{guid}'
UPDATE Stage_webhook
SET Status = '{call_status}',
Response = '',
ResultCode = '{result_message}'
WHERE Guid = '{guid}'
UPDATE Stage_webhook
SET Status = 'DONE',
CallLog = '{call_log}',
CallStruct = '{call_struct}',
TimeCompleted = NOW(),
ResultCode = '{result_code}',
Response = '{response}',
ResultMessage = '{result_message}',
Result1 = '{result1}',
Result2 = '{result2}',
Result3 = '{result3}'
WHERE Guid = '{guid}'
UPDATE Stage_webhook
SET Status = 'DONE',
TimeCompleted = NOW(),
ResultCode = '{call_status}'
WHERE Guid = '{guid}'
UPDATE stage_{service}
SET Status = 'QUEUED'
WHERE guid = '{guid}'
SELECT SourceGuid
FROM Stage_webhook
WHERE Guid = '{guid}'
AND Guid = '{stage_guid}'
SELECT
S.webhookcode,
FinalOutputTable,
S.Result1, S.Result2, S.Result3, S.Result4,
S.Result5, S.Result6, S.Result7, S.Result8,
S.Result9,
S.Param1, S.Param2, S.Param3, S.Param4,
S.Param5, S.Param6, S.Param7, S.Param8, S.Param9,
S.SourceGuid, S.`Status`
FROM stage_webhook AS S
LEFT JOIN {def_webhook} D ON S.webhookcode = D.webhookcode
WHERE S.Guid = '{guid}'
AND D.Package IN ('{package}', '{archetype}')
ORDER BY TimeScheduled, Guid
REPLACE INTO stage_service
(Guid, ServiceCode, Param1, Param2, Param3, Param4, Param5,
Param6, Param7, Param8, Param9)
VALUES
('{guid}', '{webhook_code}', '{param1}', '{param2}',
'{param3}', '{hookpayloadsql}', '{payload}')
SELECT *
FROM `{hook_table}`
WHERE WebhookCode = '{webhook_code}'
AND Package IN ('{package}', '{archetype}')
SELECT *
FROM def_webhook
WHERE Webhookcode = '{webhook_code}'
AND Package IN ('{package}', '{archetype}', 'core', 'adhoc')
SELECT
Parameter,
ParameterType,
DefaultValue,
PostType,
ValueType,
Package,
Rank
FROM `{def_webhook_parameters}`
WHERE WebhookCode LIKE '{webhook_code}'
AND Package IN ('{package}', '{archetype}', 'core', 'adhoc')
ORDER BY Rank
SELECT D.Parameter
FROM `{def_webhook_parameters}` D
WHERE D.WebhookCode = '{webhook_code}'
AND D.PostType IN ('POST', 'PATCH', 'REST')
AND D.Package IN ('{package}', '{archetype}', 'core', 'adhoc')
AND NOT COALESCE(D.ValueType, '') IN ('DISPLAY', 'NOTE')
ORDER BY D.Rank
SELECT D.Parameter
FROM `{def_webhook_parameters}` D
WHERE D.WebhookCode = '{webhook_code}'
AND D.PostType IN ('POST', 'PATCH')
AND D.Required LIKE 'Y'
AND D.Package IN ('{package}', '{archetype}', 'core', 'adhoc')
AND NOT D.ValueType IN ('DISPLAY', 'NOTE')
ORDER BY D.Rank
SELECT ValidationSql, ValidationNote
FROM `{def_webhook_validations}`
WHERE webhookcode = '{webhook_code}'
SELECT
ValidationSql,
ValidationNote,
remote_connection,
Description
FROM def_webhook_validations
WHERE webhookcode = '{webhook_code}'
AND package IN ('{package}', '{archetype}')
AND COALESCE(Active, 'Y') = 'Y'
ORDER BY Rank DESC
SELECT
ValidationSql,
ValidationNote,
remote_connection,
Description
FROM def_webhook_output_validations
WHERE webhookcode = '{webhook_code}'
AND package IN ('{package}', '{archetype}')
AND COALESCE(Active, 'Y') = 'Y'
ORDER BY Rank DESC
SELECT * FROM def_webhook
WHERE
WebhookCode LIKE '{webhook_code}'
AND Direction = 'IN'
AND if(ifnull(Package,'')='','CORE',Package)
in ('{package}','CORE','ADHOC')
SELECT DISTINCT
Parameter,
COALESCE(NormalParameter, ''),
COALESCE(Required, 'N'),
COALESCE(Encoding, '')
FROM def_webhook_parameters
WHERE PostType IN ('POST', 'HEAD', 'GET', 'DERIVED', 'ENCODED')
AND webhookcode = '{webhook_code}'
AND COALESCE(Package, 'CORE')
IN ('{package}', 'CORE', 'ADHOC')
ORDER BY Rank
SELECT DISTINCT
Parameter,
NormalParameter,
COALESCE(Example, ''),
ValueType
FROM def_webhook_parameters
WHERE PostType = 'RETURN'
AND webhookcode = '{webhook_code}'
AND COALESCE(Package, 'CORE')
IN ('{package}', 'CORE', 'ADHOC')
ORDER BY Rank
SELECT count(*) FROM def_webhook_reflections
WHERE WebhookCode = '{webhook_code}'
AND package in ('{package}','{archetype}')
AND Active = 'Y'
SELECT
MirrorCode, MirrorDelay, coalesce(LookupSql,''),
'' as MirrorMode, MirrorRemote, MirrorTable
FROM def_webhook_reflections
WHERE WebhookCode = '{webhook_code}'
AND package in ('{package}','{archetype}')
AND Active = 'Y'
SELECT PayloadType, ReflectionType FROM def_webhook
WHERE WebhookCode = '{webhook_code}'
AND package in ('{package}','{archetype}')
SELECT Parameter, Required
FROM def_webhook_parameters
WHERE PostType = 'RETURN'
AND webhookcode = '{webhook_code}'
AND COALESCE(Package, 'CORE')
IN ('{package}', '{archetype}', 'CORE', 'ADHOC')
AND Required = 'Y'
SELECT Password, User, coalesce(Session,'') AS Session
FROM sys_User where active = 'Y'
AND User like '{username}'
AND package in ('{package}','{archetype}')
UPDATE sys_User as user SET Session = '{session_guid}',
LastInteraction = now(),
Lastlogin = now()
WHERE User.Active = 'Y'
AND User like '{username}'
AND package in ('{package}','{archetype}')
UPDATE {table_base} SET
bloom_status = '{status}',
bloom_status_code = {status_code},
bloom_status_note = '{status_note}',
bloom_validation_description = '{validation_description}',
bloom_validation_resultnote = '{validation_note}',
bloom_rdg = '{random_digit}',
bloom_remoteip = '{remote_ip}',
bloom_useragent = '{user_agent}',
bloom_connectionId = CONNECTION_ID(),
bloom_idnumber = '{bloom_idnumber}'
WHERE base_{guid_name} = '{base_guid}'
UPDATE {table_base}
SET bloom_results = '{results_json}'
WHERE base_{guid_name} = '{base_guid}'
SELECT Example, ValueType
FROM Def_Webhook_parameters
WHERE posttype in ('RETURN','JSON')
AND webhookcode = '{webhook_code}'
AND Parameter = '{parameter}'
SELECT PostType, DoTitlePost
FROM Def_Webhook_parameters
WHERE PostType in ('RETURN','LOOKUP')
AND webhookcode = '{webhook_code}'
AND Parameter = '{parameter}'
SELECT Parameter
FROM Def_WebHook_Parameters
WHERE WebhookCode like '{webhook_code}'
AND NormalParameter = '{normal_parameter}'
AND package = '{package}'
SELECT *
FROM def_webhook_batch
WHERE WebhookCode = '{webhook_code}'
AND Package = '{package}'
AND Active = 'Y'
SELECT count(*)
FROM `{input_table}`
{completed_filter}
SELECT guid, idnumber, coalesce(cs_cell_phone_1,'')
FROM bloom_donormalenquiry
ORDER BY RAND()
LIMIT 1
SELECT
guid,
result,
score,
status,
COALESCE(message, 'No message') AS message,
DATE_FORMAT(CreateTime, '%Y-%m-%dT%H:%i:%s') AS timestamp
FROM bloom_testhook
WHERE guid = '$guid$'
LIMIT 1
INSERT INTO bloom_testhook_history
SELECT * FROM bloom_testhook
WHERE guid = '$guid$'
ON DUPLICATE KEY UPDATE
CreateTime = VALUES(CreateTime)
SELECT WebhookCode
FROM `{def_webhook}`
WHERE Buildpayload = 'Y'
AND Async_buildpayload = 'Y'
AND Package = '{package}'
SELECT D.*
FROM {def_webhook} AS D
WHERE D.WebhookCode = '{code}'
AND D.Package IN ('{package}', '{archetype}')
SELECT D.Payload, D.`{guid_name}`
FROM `{datatablefinal}` AS D
LEFT JOIN `{datatablefinal}_payload` AS P
ON D.`{guid_name}` = P.`{payload_guid}`
WHERE P.Payload IS NULL
SELECT D.Payload, D.`{guid_name}`
FROM `{datatablefinal}` AS D
LIMIT 1
REPLACE INTO `{datatablefinal}_payload` (`{payload_guid}`, Payload)
VALUES ('{guid}', 'Failed payload')
SELECT
IFNULL(Parameter, ''),
IFNULL(ParameterBlock, ''),
IFNULL(ParameterSubBlock, ''),
IFNULL(ParameterSubSubblock, ''),
IFNULL(ParameterSubSubSubBlock, ''),
IFNULL(ValueType, '')
FROM `{def_webhook_parameters}`
WHERE WebhookCode LIKE '{webhook_code}'
AND NOT COALESCE(ValueType, 'STRING') IN ('DISPLAY', 'NOTE')
AND Package IN ('{package}', '{archetype}', 'adhoc', 'core')
ORDER BY Rank
CREATE TABLE IF NOT EXISTS {datatable}
( `Guid` CHAR(50) NOT NULL,
`BaseGuid` CHAR(50) NOT NULL,
`CreateTime` TIMESTAMP NOT NULL,
Primary Key (`Guid`),
Index `CreateTime` (`CreateTime`)
) COLLATE='{collation}'
UPDATE {def_webhook_parameters} set NormalParameter = '{normal_parameter}'
WHERE Parameter like '{parameter}'
AND WebhookCode like '{webhook_code}'
SELECT {select_columns}
D.ValueType
FROM {def_webhook_parameters} D
WHERE D.WebhookCode = '{webhook_code}'
AND D.Package in ('{package}','{archetype}','adhoc','core')
{level_condition}
AND not coalesce(D.ValueType) in ('DISPLAY', 'NOTE')
order by D.Rank
SELECT COUNT(*)
FROM {def_webhook_parameters}
WHERE WebhookCode = '{webhook_code}'
AND Parameter = '{param_name}'
AND Package IN ('{package}', '{archetype}', 'core', 'adhoc')
AND PostType = '{direction_type}'
INSERT INTO {def_webhook_parameters}
(WebhookCode, Package, Parameter,Rank, PostType, ValueType, Required)
VALUES
(
'{webhook_code}',
'{package}',
'{param_name}',
{rank},
'{direction_type}',
'{inferred_sql_type}',
'Y'
);
SELECT TestCase1
FROM {def_webhook}
WHERE WebhookCode = '{webhook_code}'
AND Package IN ('{package}',
'{archetype}', 'core', 'adhoc');
SELECT HookLookupSql
FROM {def_webhook}
WHERE WebhookCode = '{webhook_code}'
AND Package IN ('{package}',
'{archetype}', 'core', 'adhoc');
SELECT D.Parameter
FROM {def_webhook_parameters} D
WHERE D.WebhookCode = '{webhook_code}'
AND D.PostType IN ('POST','PATCH','REST')
AND D.Package in ('{package}','{archetype}','core','adhoc')
AND NOT COALESCE(D.ValueType,'') in ('DISPLAY', 'NOTE')
ORDER BY D.Rank
SELECT D.Hooklookupsql
FROM {def_webhook} D
WHERE D.WebhookCode = '{webhook_code}'
AND D.Package in ('{package}','{archetype}')
SELECT D.Parameter
FROM {def_webhook_parameters} D
WHERE D.WebhookCode = '{webhook_code}'
AND D.PostType IN ('POST','PATCH') and D.Required like 'Y'
AND D.Package in ('{package}','{archetype}','core','adhoc')
AND NOT D.ValueType in ('DISPLAY', 'NOTE')
ORDER BY D.Rank
SELECT Hookredirectsql
FROM {def_webhook}
WHERE WebhookCode = '{webhook_code}'
AND D.Package in ('{package}','{archetype}','core','adhoc')
SELECT Parameter
FROM {def_webhook_parameters}
WHERE WebhookCode like '{webhook_code}'
AND ValueType like 'DOCUMENT'
SELECT DISTINCT
w.WebhookCode,
w.Package,
w.Direction,
w.Type,
w.Notes,
w.AiSummary,
w.AiPurpose,
w.AiUseCases,
w.AiRelatedWorkflows,
w.PayloadType,
w.Httpmethod,
w.BaseUrl,
w.Headers,
w.ApiKey,
w.RemoteConnection,
w.Buildstructure,
w.Buildpayload,
w.Buildvalidation,
w.DataTable,
w.DataKey,
w.DataTableFinal,
w.HookLookupSql,
w.HookRedirectSql,
w.Hookpayloadsql,
w.PreSql,
w.PostSql,
w.Result1Field,
w.Result2Field,
w.ResultMessageField,
w.Isform,
w.DocumentPDF,
w.Rank
FROM {def_webhook} w
WHERE w.ServiceCode = '{service_code}'
ORDER BY w.Rank, w.WebhookCode
SELECT
Parameter,
Rank,
PostType,
ParameterType,
ValueType,
Required,
DefaultValue,
ParameterBlock,
DisplayName,
Description,
Example,
AiDescription,
AiValidationRules
FROM {def_webhook_parameters}
WHERE WebhookCode = '{webhook_code}'
AND Package IN ('{package}', '{archetype}', 'core', 'adhoc')
ORDER BY Rank, Parameter
SELECT
workflowcode,
Package,
Archetype,
Nodes,
Branches,
NodeType,
Description,
Active
FROM def_workflows
WHERE workflowcode = '{workflow_code}'
AND Package IN ('{package}', '{archetype}', 'core')
LIMIT 1
SELECT
WebhookCode,
Package,
WorkflowName,
FactoryObject,
TriggerMethod,
ConnectionType,
Description,
AiFlowDescription,
Active
FROM def_webhook_workflow_map
WHERE WebhookCode = '{webhook_code}'
AND Package IN ('{package}', '{archetype}', 'core')
AND Active = 'Y'
ORDER BY WorkflowName
SELECT DISTINCT
w.WebhookCode,
w.Description,
w.Direction,
w.Type,
w.Rank
FROM {def_webhook} w
WHERE w.ServiceCode = '{service_code}'
AND w.Active = 'Y'
ORDER BY w.Rank, w.WebhookCode