CREATE TABLE IF NOT EXISTS `def_ticket_assign_rules` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Package` char(255) NOT NULL DEFAULT '',
`Project` varchar(255) NOT NULL DEFAULT '',
`Priority` varchar(20) NOT NULL DEFAULT '',
`TicketType` varchar(50) NOT NULL DEFAULT '',
`AssignTo` varchar(255) NOT NULL,
`Active` char(2) DEFAULT 'Y',
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`Id`),
KEY `idx_rule_lookup` (
`Package`, `Project`, `Priority`,
`TicketType`, `Active`
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
SELECT AssignTo
FROM def_ticket_assign_rules
WHERE Package = '{package}'
AND Project = '{project}'
AND Priority = '{priority}'
AND TicketType = '{ticket_type}'
AND Active = 'Y'
LIMIT 1
SELECT AssignTo
FROM def_ticket_assign_rules
WHERE Package = '{package}'
AND Project = '{project}'
AND Priority = '{priority}'
AND TicketType = ''
AND Active = 'Y'
LIMIT 1
SELECT AssignTo
FROM def_ticket_assign_rules
WHERE Package = '{package}'
AND Project = '{project}'
AND Priority = ''
AND TicketType = ''
AND Active = 'Y'
LIMIT 1
SELECT AssignTo
FROM def_ticket_assign_rules
WHERE Package = '{package}'
AND Project = ''
AND Priority = '{priority}'
AND TicketType = ''
AND Active = 'Y'
LIMIT 1
SELECT AssignTo
FROM def_ticket_assign_rules
WHERE Package = '{package}'
AND Project = ''
AND Priority = ''
AND TicketType = ''
AND Active = 'Y'
LIMIT 1
SELECT Id, Package, Project, Priority,
TicketType, AssignTo, Active
FROM def_ticket_assign_rules
WHERE Package = '{package}'
ORDER BY
Project ASC,
FIELD(
Priority,
'CRITICAL','HIGH','MEDIUM','LOW','INFO',''
),
TicketType ASC
INSERT INTO def_ticket_assign_rules
(Package, Project, Priority,
TicketType, AssignTo)
VALUES
('{package}', '{project}', '{priority}',
'{ticket_type}', '{assign_to}')
DELETE FROM def_ticket_assign_rules
WHERE Id = {rule_id}
AND Package = '{package}'