We would like to use round robin for our helpdesk to auto assign ticket to technician.  I found the code in IT ninja and it keep getting error. 

This is what I did:

I created a customer ticket rules called "round robin" and run it "on ticket save". I try the code in MySQL workbeach, the select query section seems working, it display a user in returned.  The update query is the one I have problem.

Select query:

select T.ID, O.USER_NAME, MAXC,IF(MAXC IS NULL, 1, 2) BATTERUP
from
  HD_TICKET T
  JOIN HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID
  JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.HD_QUEUE_ID=Q.ID
  JOIN USER_LABEL_JT UL ON UL.LABEL_ID=QOL.LABEL_ID
  JOIN USER O ON O.ID=UL.USER_ID
  LEFT JOIN
      (select max(CREATED) MAXC, OWNER_ID, HD_QUEUE_ID from HD_TICKET WHERE  CREATED > CURDATE() GROUP BY OWNER_ID, HD_QUEUE_ID)T2
              ON    O.ID=T2.OWNER_ID and Q.ID=T2.HD_QUEUE_ID
    LEFT JOIN (select USER_ID from USER_LABEL_JT UL2 JOIN LABEL OUTLABEL
              ON OUTLABEL.ID=UL2.LABEL_ID  and OUTLABEL.NAME='Out of office') OOO ON O.ID=OOO.USER_ID
where
  O.CUSTOM_1<> 'out'
  and OOO.USER_ID IS NULL
  and T.OWNER_ID=0
  and T.ID=123  /* example new ticket # */
ORDER BY MAXC ASC, RAND()
LIMIT 1

Update query:

select HD_TICKET.ID from
  HD_TICKET
  JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID>
WHERE
  C.DESCRIPTION LIKE 'Ticket Created%'  /* new ticket */
  and ( HD_TICKET.OWNER_ID=0 OR HD_TICKET.OWNER_ID IS NULL) /* no owner yet */



UPDATE HD_TICKET
set HD_TICKET.OWNER_ID = (select BATTERUP.ID from  (select O.ID
from
  HD_TICKET T
  JOIN HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID
  JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.HD_QUEUE_ID=Q.ID
  JOIN USER_LABEL_JT UL ON UL.LABEL_ID=QOL.LABEL_ID
  JOIN USER O ON O.ID=UL.USER_ID
  LEFT JOIN
      (select max(CREATED) MAXC, OWNER_ID, HD_QUEUE_ID from HD_TICKET WHERE  CREATED > CURDATE() GROUP BY OWNER_ID, HD_QUEUE_ID) T2
              ON    O.ID=T2.OWNER_ID and Q.ID=T2.HD_QUEUE_ID
    LEFT JOIN (select USER_ID from USER_LABEL_JT UL2 JOIN LABEL OUTLABEL
              ON OUTLABEL.ID=UL2.LABEL_ID and OUTLABEL.NAME='Out of office') OOO ON O.ID=OOO.USER_ID
where
  O.CUSTOM_1<> 'out'
  and OOO.USER_ID IS NULL
  and T.OWNER_ID=0
  and T.ID=<TICKET_IDS>
ORDER BY MAXC ASC, RAND()
LIMIT 1 )  BATTERUP)
WHERE HD_TICKET.ID =<TICKET_IDS>

Thanks for your help.

Andy

2 Comments   [ + ] Show Comments

Comments

  • Does your update query actually contain both the select and the update statements or did that just get posted incorrectly? The KBOX will only accept one SQL statement in the Update Query configuration so that won't work.
  • you are correct. I paste the wrong code
Please log in to comment

Answers

0

I got it working for couple days but it broke again on select statment.  I got the result from MySQL workbench but got error from KACE.

 select T.ID, O.USER_NAME, MAXC,IF(MAXC IS NULL, 1, 2) BATTERUP
from
HD_TICKET T
JOIN HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID
JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.HD_QUEUE_ID=Q.ID
JOIN USER_LABEL_JT UL ON UL.LABEL_ID=QOL.LABEL_ID
JOIN USER O ON O.ID=UL.USER_ID
LEFT JOIN
(select max(CREATED) MAXC, OWNER_ID, HD_QUEUE_ID from HD_TICKET WHERE CREATED > CURDATE() GROUP BY OWNER_ID, HD_QUEUE_ID)T2
ON O.ID=T2.OWNER_ID and Q.ID=T2.HD_QUEUE_ID
LEFT JOIN (select USER_ID from USER_LABEL_JT UL2 JOIN LABEL OUTLABEL
ON OUTLABEL.ID=UL2.LABEL_ID and OUTLABEL.NAME='Out of office') OOO ON O.ID=OOO.USER_ID
where
O.CUSTOM_1<> 'out'
and OOO.USER_ID IS NULL
and T.OWNER_ID=0
and T.ID=111 /* example new ticket # */
ORDER BY MAXC ASC, RAND()
Limit 1

mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and (HD_TICKET.ID = 643)' at line 20] in EXECUTE("select T.ID, O.USER_NAME, MAXC,IF(MAXC IS NULL, 1, 2) BATTERUP
from
HD_TICKET T
JOIN HD_QUEUE Q ON T.HD_QUEUE_ID=Q.ID
JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.HD_QUEUE_ID=Q.ID
JOIN USER_LABEL_JT UL ON UL.LABEL_ID=QOL.LABEL_ID
JOIN USER O ON O.ID=UL.USER_ID
LEFT JOIN
(select max(CREATED) MAXC, OWNER_ID, HD_QUEUE_ID from HD_TICKET WHERE CREATED > CURDATE() GROUP BY OWNER_ID, HD_QUEUE_ID)T2
ON O.ID=T2.OWNER_ID and Q.ID=T2.HD_QUEUE_ID
LEFT JOIN (select USER_ID from USER_LABEL_JT UL2 JOIN LABEL OUTLABEL
ON OUTLABEL.ID=UL2.LABEL_ID and OUTLABEL.NAME='Out of office') OOO ON O.ID=OOO.USER_ID
where
O.CUSTOM_1<> 'out'
and OOO.USER_ID IS NULL
and T.OWNER_ID=0
and T.ID=111 /* example new ticket # */
ORDER BY MAXC ASC, RAND()
Limit 1
and (HD_TICKET.ID = 643) ")


Answered 03/10/2014 by: andylai2k
White Belt

Please log in to comment
Answer this question or Comment on this question for clarity