Im not an expert in SQL, not at all, but this rules work perfectly, for that reason im so happy to contribute a little bit to the community.
1) The first rule change the status of a ticket when it has an owner. it changes from 'Aberto' to 'Em atendimento'
*select SQL
SELECT HD_TICKET.ID AS ID,
HD_TICKET.TITLE AS TITLE,
U1.FULL_NAME as SUBMITTER_FULLNAME,
U1.EMAIL as SUBMITTER_EMAIL,
U2.FULL_NAME as OWNER_FULLNAME,
U2.EMAIL as OWNER_EMAIL
FROM HD_TICKET
Inner JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID = HD_STATUS.ID
LEFT JOIN USER U1 ON (HD_TICKET.SUBMITTER_ID = U1.ID)
LEFT JOIN USER U2 on (HD_TICKET.OWNER_ID = U2.ID)
WHERE HD_TICKET.OWNER_ID > 0 and HD_STATUS.NAME = 'Opened'
*Actualizar SQL
UPDATE HD_TICKET T
JOIN HD_STATUS ON HD_STATUS.NAME = 'Em atendimento' and T.HD_QUEUE_ID = HD_STATUS.HD_QUEUE_ID
SET T.HD_STATUS_ID = HD_STATUS.ID,
/*reset the state timstamps*/
T.TIME_OPENED = IF(HD_STATUS.STATE = 'opened', NOW(), T.TIME_OPENED),
T.TIME_CLOSED = IF(HD_STATUS.STATE = 'closed', NOW(), T.TIME_CLOSED),
T.TIME_STALLED = IF(HD_STATUS.STATE = 'stalled', NOW(), T.TIME_STALLED),
/*reset the survey*/
T.SATISFACTION_RATING = IF(HD_STATUS.STATE = 'closed', NULL, T.SATISFACTION_RATING),
T.SATISFACTION_COMMENT = IF(HD_STATUS.STATE = 'closed', NULL, T.SATISFACTION_COMMENT)
WHERE T.ID IN (<TICKET_IDS>)
*Frequency: On ticket saved
2)The second one change the status to 'Usuario retornou', that means submitter answered, when the submitter write a note in the ticket. to support people realized which tickets has been answered by the users.
*Select SQL
SELECT
HD_TICKET.ID,
HD_TICKET.SUBMITTER_ID,
HTC.User_ID,
X.IDFilho
From
HD_TICKET
inner Join ( Select TC.HD_TICKET_ID, max(TC.ID) IDFilho from HD_TICKET_CHANGE TC inner join HD_TICKET_CHANGE_FIELD F on ( F.HD_TICKET_CHANGE_ID = TC.ID) Group by TC.HD_TICKET_ID ) X on ( X.HD_TICKET_ID = HD_TICKET.ID )
inner Join HD_TICKET_CHANGE_FIELD F on ( F.HD_TICKET_CHANGE_ID = X.IDFilho)
inner Join HD_TICKET_CHANGE HTC on ( X.IDFilho = HTC.ID)
where
F.FIELD_Changed = 'COMMENT' and
HD_TICKET.SUBMITTER_ID = HTC.User_ID
*Actualizar SQL
update HD_TICKET
JOIN HD_STATUS ON HD_STATUS.NAME = 'Usuário retornou' and HD_TICKET.HD_QUEUE_ID = HD_STATUS.HD_QUEUE_ID
set HD_TICKET.HD_STATUS_ID = HD_STATUS.ID,
HD_TICKET.TIME_OPENED = IF(HD_STATUS.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED),
HD_TICKET.TIME_CLOSED = IF(HD_STATUS.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED),
HD_TICKET.TIME_STALLED = IF(HD_STATUS.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED)
WHERE HD_TICKET.ID IN (<TICKET_IDS>)
*Frequency: On ticket Saved
3) The third rule change the status from 'Usuario retornou' (user answered) to the before state which depends on if it had Owner ('Em atendimento') or if it hadnt ('Aberto')
*Select SQL
SELECT HD_TICKET.ID,HD_TICKET.TITLE, E.FULL_NAME as SOLICITANTE,
DONO.FULL_NAME as ATENDENTE,
S.NAME AS ESTADO
FROM HD_TICKET
left join USER DONO on DONO.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER E on E.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN HD_STATUS S ON (HD_TICKET.HD_STATUS_ID = S.ID)
inner Join ( Select TC.HD_TICKET_ID, max(TC.ID) IDFilho from HD_TICKET_CHANGE TC
inner join HD_TICKET_CHANGE_FIELD F on ( F.HD_TICKET_CHANGE_ID = TC.ID)
Group by TC.HD_TICKET_ID ) X on ( X.HD_TICKET_ID = HD_TICKET.ID )
inner Join HD_TICKET_CHANGE_FIELD F on ( F.HD_TICKET_CHANGE_ID = X.IDFilho)
inner Join HD_TICKET_CHANGE HTC on ( X.IDFilho = HTC.ID)
where
F.FIELD_Changed = 'COMMENT' and
HTC.User_ID != HD_TICKET.SUBMITTER_ID AND
S.NAME = 'USUÁRIO RETORNOU'
group by HD_TICKET.ID
*Actualizar SQL:
update HD_TICKET AS T
Set
T.HD_STATUS_ID = ( Select S.ID from HD_STATUS S Where S.HD_QUEUE_ID = T.HD_QUEUE_ID and S.NAME = (case when T.OWNER_ID ='' then 'Opened' else 'Em atendimento' end) )
WHERE
(T.ID in (<TICKET_IDS>))
*Frequency: on tickect saved
Hope you find this useful!!!!
Comments