CREATE TABLE `data_ticket` (
`Guid` char(50) NOT NULL,
`TicketNumber` int(11) NOT NULL AUTO_INCREMENT,
`Package` char(255) DEFAULT NULL,
`Project` varchar(100) DEFAULT NULL,
`Subject` varchar(500) DEFAULT NULL,
`Description` text DEFAULT NULL,
`Status` enum('NEW','TRIAGED','IN_PROGRESS','ON_HOLD','ESCALATED','RESOLVED','CLOSED','CANCELLED') DEFAULT 'NEW',
`Priority` enum('CRITICAL','HIGH','MEDIUM','LOW','INFO') DEFAULT 'MEDIUM',
`TicketType` enum('USER','INTERNAL','BUG','FEATURE') DEFAULT 'USER',
`RequestedBy` varchar(255) DEFAULT NULL,
`RequestedByType` enum('INTERNAL','EMAIL','SYSTEM') DEFAULT 'INTERNAL',
`AssignedTo` varchar(255) DEFAULT NULL,
`ServerId` varchar(120) DEFAULT NULL,
`ProblemAnalysis` text DEFAULT NULL,
`WorkScore` int(11) DEFAULT 0,
`SlaResponseDeadline` datetime DEFAULT NULL,
`SlaResolutionDeadline` datetime DEFAULT NULL,
`SlaResponseMet` char(2) DEFAULT NULL,
`SlaResolutionMet` char(2) DEFAULT NULL,
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
`UpdatedDate` datetime DEFAULT NULL,
`IncidentDate` datetime DEFAULT NULL,
`StartDate` datetime DEFAULT NULL,
`IncidentCause` varchar(20) DEFAULT NULL,
`ResolvedDate` datetime DEFAULT NULL,
`ClosedDate` datetime DEFAULT NULL,
`Watchers` text DEFAULT NULL,
`EstimatedDate` datetime DEFAULT NULL,
PRIMARY KEY (`Guid`),
UNIQUE KEY `idx_ticket_number` (`TicketNumber`),
KEY `idx_package` (`Package`),
KEY `idx_status` (`Status`),
KEY `idx_priority` (`Priority`),
KEY `idx_assigned` (`AssignedTo`),
KEY `idx_server_id` (`ServerId`),
KEY `idx_requested` (`RequestedBy`),
KEY `idx_created` (`CreatedDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `data_ticket_history` (
`Guid` varchar(64) NOT NULL,
`TicketNumber` varchar(255) NOT NULL,
`Package` varchar(50) NOT NULL,
`Module` varchar(255) DEFAULT NULL,
`Version` int(11) NOT NULL,
`ThreeWords` varchar(255) DEFAULT NULL,
`VersionDate` datetime DEFAULT NULL,
`NodeCount` int(11) DEFAULT 0,
`ChangedBy` varchar(255) DEFAULT NULL,
`ChangeType` varchar(50) DEFAULT 'IMPORT',
`ChangeNotes` text DEFAULT NULL,
`CreatedAt` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`TicketNumber`, `Package`, `Version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `track_ticket` (
`Guid` char(50) NOT NULL,
`TicketGuid` char(50) NOT NULL,
`Action` varchar(100) DEFAULT NULL,
`OldValue` text DEFAULT NULL,
`NewValue` text DEFAULT NULL,
`ActionBy` varchar(255) DEFAULT NULL,
`Package` char(255) DEFAULT NULL,
`ActionDate` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`Guid`),
KEY `idx_ticket_guid` (`TicketGuid`),
KEY `idx_action` (`Action`),
KEY `idx_action_date` (`ActionDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `data_ticket_document` (
`Guid` char(50) NOT NULL,
`TicketGuid` char(50) NOT NULL,
`FileName` varchar(255) DEFAULT NULL,
`MimeType` varchar(255) DEFAULT NULL,
`FileSize` int(11) DEFAULT 0,
`FileData` longtext DEFAULT NULL,
`UploadedBy` varchar(255) DEFAULT NULL,
`Package` char(255) DEFAULT NULL,
`IsDeleted` char(2) DEFAULT 'N',
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`Guid`),
KEY `idx_ticket_guid` (`TicketGuid`),
KEY `idx_deleted` (`IsDeleted`),
KEY `idx_created` (`CreatedDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
CREATE TABLE `data_ticket_timeentry` (
`Guid` char(50) NOT NULL,
`TicketGuid` char(50) NOT NULL,
`PersonEmail` varchar(255) DEFAULT NULL,
`StartTime` datetime DEFAULT NULL,
`EndTime` datetime DEFAULT NULL,
`DurationMinutes` int(11) DEFAULT 0,
`Description` text DEFAULT NULL,
`Package` char(255) DEFAULT NULL,
`CreatedDate` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`Guid`),
KEY `idx_ticket_guid` (`TicketGuid`),
KEY `idx_person` (`PersonEmail`),
KEY `idx_created` (`CreatedDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE={collation};
SELECT *
FROM data_ticket
WHERE Guid = '{guid}'
SELECT *
FROM data_ticket
WHERE TicketNumber = {ticket_number}
INSERT INTO data_ticket
(Guid, Package, Subject, Description, Status,
Priority, TicketType, RequestedBy,
RequestedByType, ServerId,
SlaResponseDeadline, SlaResolutionDeadline,
CreatedDate)
VALUES
('{guid}', '{package}', '{subject}',
'{description}', 'NEW', '{priority}',
'{ticket_type}', '{requested_by}',
'{requested_by_type}', '{server_id}',
{sla_response_deadline},
{sla_resolution_deadline},
now())
ALTER TABLE `{table_name}`
ADD COLUMN `{column_name}` {column_def}
UPDATE data_ticket
SET Subject = '{subject}',
Description = '{description}',
Priority = '{priority}',
TicketType = '{ticket_type}',
ServerId = '{server_id}',
UpdatedDate = now()
WHERE Guid = '{guid}'
UPDATE data_ticket
SET ServerId = '{server_id}',
UpdatedDate = now()
WHERE Guid = '{guid}'
UPDATE data_ticket
SET Status = '{status}',
UpdatedDate = now()
WHERE Guid = '{guid}'
UPDATE data_ticket
SET AssignedTo = '{assigned_to}',
UpdatedDate = now()
WHERE Guid = '{guid}'
UPDATE data_ticket
SET ProblemAnalysis = '{analysis}',
UpdatedDate = now()
WHERE Guid = '{guid}'
UPDATE data_ticket
SET IncidentDate = '{incident_date}',
UpdatedDate = now()
WHERE Guid = '{guid}'
UPDATE data_ticket
SET IncidentCause = '{incident_cause}',
UpdatedDate = now()
WHERE Guid = '{guid}'
UPDATE data_ticket
SET WorkScore = {work_score},
UpdatedDate = now()
WHERE Guid = '{guid}'
UPDATE data_ticket
SET Watchers = '{watchers}',
UpdatedDate = now()
WHERE Guid = '{guid}'
UPDATE data_ticket
SET Status = 'RESOLVED',
ResolvedDate = now(),
UpdatedDate = now(),
SlaResolutionMet = '{sla_resolution_met}'
WHERE Guid = '{guid}'
UPDATE data_ticket
SET Status = 'CLOSED',
ClosedDate = now(),
UpdatedDate = now()
WHERE Guid = '{guid}'
UPDATE data_ticket
SET Status = 'ESCALATED',
UpdatedDate = now()
WHERE Guid = '{guid}'
UPDATE data_ticket
SET SlaResponseMet = '{sla_response_met}',
UpdatedDate = now()
WHERE Guid = '{guid}'
INSERT INTO track_ticket
(Guid, TicketGuid, Action, OldValue,
NewValue, ActionBy, ActionDate)
VALUES
('{guid}', '{ticket_guid}', '{action}',
'{old_value}', '{new_value}',
'{action_by}', now())
INSERT INTO data_ticket_document
(Guid, TicketGuid, FileName, MimeType,
FileSize, FileData, UploadedBy,
Package, IsDeleted, CreatedDate)
VALUES
('{guid}', '{ticket_guid}',
'{file_name}', '{mime_type}',
{file_size}, '{file_data}',
'{uploaded_by}', '{package}',
'N', now())
SELECT Guid, TicketGuid, FileName, MimeType,
FileSize, UploadedBy, Package,
DATE_FORMAT(CreatedDate, '%Y-%m-%d %H:%i:%s') AS CreatedDate
FROM data_ticket_document
WHERE TicketGuid = '{ticket_guid}'
AND IsDeleted = 'N'
ORDER BY CreatedDate DESC
SELECT Guid, TicketGuid, FileName, MimeType,
FileSize, UploadedBy, Package,
FileData,
DATE_FORMAT(CreatedDate, '%Y-%m-%d %H:%i:%s') AS CreatedDate
FROM data_ticket_document
WHERE Guid = '{guid}'
AND TicketGuid = '{ticket_guid}'
AND IsDeleted = 'N'
LIMIT 1
UPDATE data_ticket_document
SET IsDeleted = 'Y'
WHERE Guid = '{guid}'
AND TicketGuid = '{ticket_guid}'
SELECT *
FROM track_ticket
WHERE TicketGuid = '{ticket_guid}'
ORDER BY ActionDate
SELECT *
FROM data_ticket
WHERE Package = '{package}'
AND Status NOT IN ('CLOSED', 'CANCELLED')
ORDER BY FIELD(
Priority,
'CRITICAL','HIGH','MEDIUM','LOW','INFO'
), CreatedDate
SELECT *
FROM data_ticket
WHERE Status NOT IN
('RESOLVED', 'CLOSED', 'CANCELLED')
AND (
(SlaResponseDeadline IS NOT NULL
AND SlaResponseDeadline < now()
AND SlaResponseMet IS NULL)
OR
(SlaResolutionDeadline IS NOT NULL
AND SlaResolutionDeadline < now()
AND SlaResolutionMet IS NULL)
)
ORDER BY Priority, CreatedDate
SELECT Status, COUNT(*) as Count
FROM data_ticket
WHERE Package = '{package}'
GROUP BY Status
SELECT Email
FROM sys_user
WHERE User = '{user}'
AND Package = '{package}'
SELECT *
FROM data_ticket
WHERE AssignedTo = '{assignee}'
AND Status NOT IN ('CLOSED', 'CANCELLED')
ORDER BY FIELD(
Priority,
'CRITICAL','HIGH','MEDIUM','LOW','INFO'
), CreatedDate
SELECT *
FROM data_ticket
WHERE (AssignedTo IS NULL
OR AssignedTo = '')
AND Status NOT IN ('CLOSED', 'CANCELLED')
ORDER BY FIELD(
Priority,
'CRITICAL','HIGH','MEDIUM','LOW','INFO'
), CreatedDate
SELECT COUNT(*) as BreachCount
FROM data_ticket
WHERE Status NOT IN
('RESOLVED', 'CLOSED', 'CANCELLED')
AND (
(SlaResponseDeadline IS NOT NULL
AND SlaResponseDeadline < now()
AND SlaResponseMet IS NULL)
OR
(SlaResolutionDeadline IS NOT NULL
AND SlaResolutionDeadline < now()
AND SlaResolutionMet IS NULL)
)
SELECT *
FROM data_ticket
WHERE 1=1
{filter_clauses}
ORDER BY FIELD(
Priority,
'CRITICAL','HIGH','MEDIUM','LOW','INFO'
), CreatedDate
SELECT t.*,
s.EscalationMinutes,
s.EscalationContact
FROM data_ticket t
JOIN def_ticket_sla s
ON t.Package = s.Package
AND t.Priority = s.Priority
AND s.Active = 'Y'
WHERE t.Status NOT IN
('RESOLVED','CLOSED','CANCELLED','ESCALATED')
AND s.EscalationMinutes > 0
AND DATE_ADD(
t.CreatedDate,
INTERVAL s.EscalationMinutes MINUTE
) < now()
ORDER BY t.Priority, t.CreatedDate
UPDATE data_ticket_task
SET PullRequest = '{pull_request}',
UpdatedDate = now()
WHERE Guid = '{guid}'
SELECT AssignedTo,
COUNT(*) AS TicketCount
FROM data_ticket
WHERE Status NOT IN ('CLOSED', 'CANCELLED')
AND AssignedTo IS NOT NULL
AND AssignedTo != ''
GROUP BY AssignedTo
ORDER BY TicketCount DESC
SELECT Guid, TicketGuid, TaskName,
PullRequest, AssignedTo, Status
FROM data_ticket_task
WHERE TicketGuid = '{ticket_guid}'
AND PullRequest IS NOT NULL
AND PullRequest != ''
ORDER BY CreatedDate
INSERT INTO data_ticket
(Guid, Package, Subject, Description,
Status, Priority, TicketType,
RequestedBy, RequestedByType,
CreatedDate)
VALUES
('{guid}', '{package}', '{subject}',
'{description}', 'NEW', 'MEDIUM',
'FEATURE', 'BITBUCKET', 'SYSTEM',
now())
INSERT INTO data_ticket_timeentry
(Guid, TicketGuid, PersonEmail,
StartTime, EndTime, DurationMinutes,
Description, Package, CreatedDate)
VALUES
('{guid}', '{ticket_guid}',
'{person_email}', {start_time},
{end_time}, {duration_minutes},
'{description}', '{package}', now())
SELECT Guid, TicketGuid, PersonEmail,
StartTime, EndTime,
DurationMinutes, Description,
Package,
DATE_FORMAT(CreatedDate,
'%Y-%m-%d %H:%i:%s') AS CreatedDate
FROM data_ticket_timeentry
WHERE TicketGuid = '{ticket_guid}'
ORDER BY CreatedDate
SELECT COALESCE(
SUM(DurationMinutes), 0
) AS TotalMinutes
FROM data_ticket_timeentry
WHERE TicketGuid = '{ticket_guid}'
UPDATE data_ticket
SET EstimatedDate = '{estimated_date}',
UpdatedDate = now()
WHERE Guid = '{guid}'
{event}: {detail}