Our Round Robin Ticket Rule recently broke. It is leaving tickets as unassigned. Can anyone help?
Our Round Robin Ticket Rule recently broke. It is leaving tickets as unassigned. Can anyone help?
select HD_TICKET.ID from ORG1.HD_TICKET
JOIN ORG1.HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID
WHERE
C.DESCRIPTION LIKE 'Work Order Created%' /* new ticket */
and (HD_TICKET.OWNER_ID=0 OR HD_TICKET.OWNER_ID IS NULL) /* no owner yet */
and HD_TICKET.HD_STATUS_ID not in ('2', '53')
and HD_TICKET.HD_QUEUE_ID=1
UPDATE ORG1.HD_TICKET UT /*, ORG1.HD_TICKET_CHANGE C */
set UT.OWNER_ID =
(select BATTERUP.ID from (select O.ID
from
ORG1.HD_TICKET T
JOIN ORG1.HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID
JOIN ORG1.HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.HD_QUEUE_ID=Q.ID
JOIN ORG1.USER_LABEL_JT UL ON UL.LABEL_ID=QOL.LABEL_ID
JOIN ORG1.USER O ON O.ID=UL.USER_ID
JOIN ORG1.USER_FIELD_VALUE UV ON UV.USER_ID=O.ID
JOIN ORG1.USER_FIELD_DEFINITION UF ON UF.ID=UV.FIELD_ID
LEFT JOIN
(select max(CREATED) MAXC, OWNER_ID, HD_QUEUE_ID
from ORG1.HD_TICKET
WHERE CREATED > CURDATE()
AND OWNER_ID in (select USER_ID from ORG1.USER_LABEL_JT where LABEL_ID=325)
GROUP BY OWNER_ID, HD_QUEUE_ID) T2
ON O.ID=T2.OWNER_ID and Q.ID=T2.HD_QUEUE_ID
where
O.ID NOT IN (SELECT OU.ID
FROM ORG1.USER OU
JOIN ORG1.USER_FIELD_VALUE OV ON OV.USER_ID=OU.ID
WHERE OV.FIELD_VALUE='out'
AND OV.FIELD_ID=4)
and (T.OWNER_ID=0 OR T.OWNER_ID IS NULL)
and T.HD_QUEUE_ID=1
and O.ID in (select USER_ID from ORG1.USER_LABEL_JT where LABEL_ID=325)
ORDER BY MAXC ASC, RAND()
LIMIT 1 ) BATTERUP) /*, */
/* C.NOTIFY_USERS= CONCAT('a:2:{s:13:"ticket_change";s:3:"',UT.OWNER_ID, '";s:14:"comment_change";s:0:"";}'), */
/* UT.HD_STATUS_ID=1 */
WHERE UT.ID = '<TICKET_IDS>'
/* AND C.HD_TICKET_ID= <TICKET_IDS> */
/* AND C.DESCRIPTION LIKE 'Work Order Created%' */
Answers (3)
07/03/2023 08:02:13> Starting: 07/03/2023 08:02:13 07/03/2023 08:02:13> Executing Select Query... 07/03/2023 08:02:13> selected 1 rows 07/03/2023 08:02:13> Executing Update Query... 07/03/2023 08:02:13> mysqli error: [1054: Unknown column 'UT.ID' in 'where clause'] in EXECUTE("UPDATE ORG1.HD_TICKET /*, ORG1.HD_TICKET_CHANGE C */ set UT.OWNER_ID = (select BATTERUP.ID from (select O.ID from ORG1.HD_TICKET T JOIN ORG1.HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID JOIN ORG1.HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.HD_QUEUE_ID=Q.ID JOIN ORG1.USER_LABEL_JT UL ON UL.LABEL_ID=QOL.LABEL_ID JOIN ORG1.USER O ON O.ID=UL.USER_ID JOIN ORG1.USER_FIELD_VALUE UV ON UV.USER_ID=O.ID JOIN ORG1.USER_FIELD_DEFINITION UF ON UF.ID=UV.FIELD_ID LEFT JOIN (select max(CREATED) MAXC, OWNER_ID, HD_QUEUE_ID from ORG1.HD_TICKET WHERE CREATED > CURDATE() AND OWNER_ID in (select USER_ID from ORG1.USER_LABEL_JT where LABEL_ID=325) GROUP BY OWNER_ID, HD_QUEUE_ID) T2 ON O.ID=T2.OWNER_ID and Q.ID=T2.HD_QUEUE_ID where O.ID NOT IN (SELECT OU.ID FROM ORG1.USER OU JOIN ORG1.USER_FIELD_VALUE OV ON OV.USER_ID=OU.ID WHERE OV.FIELD_VALUE='out' AND OV.FIELD_ID=4) and (T.OWNER_ID=0 OR T.OWNER_ID IS NULL) and T.HD_QUEUE_ID=1 and O.ID in (select USER_ID from ORG1.USER_LABEL_JT where LABEL_ID=325) ORDER BY MAXC ASC, RAND() LIMIT 1 ) BATTERUP) /*, */ /* C.NOTIFY_USERS= CONCAT('a:2:{s:13:"ticket_change";s:3:"',UT.OWNER_ID, '";s:14:"comment_change";s:0:"";}'), */ /* UT.HD_STATUS_ID=1 */ WHERE UT.ID = 34020 /* AND C.HD_TICKET_ID= 34020 */ /* AND C.DESCRIPTION LIKE 'Work Order Created%' */") 07/03/2023 08:02:13> Ending: 07/03/2023 08:02:13
If you email me I will let you have some different code that you could use instead of those current rule, not because it’s better just because it may be quicker ;o)