SELECT
T.id AS 'ID',
SUBSTRING_INDEX(REGEXP_REPLACE(REPLACE(REPLACE(T.content,'<','<'),'>','>'),'<[^>]*>',''),'| ',1) AS 'Demanda',
CASE
WHEN REGEXP_REPLACE(REPLACE(REPLACE(T.content,'<','<'),'>','>'),'<[^>]*>','') LIKE '%SEI:%'
THEN SUBSTRING_INDEX(REGEXP_REPLACE(REPLACE(REPLACE(T.content,'<','<'),'>','>'),'<[^>]*>',''),'| SEI: ',-1)
ELSE 'N/A'
END AS 'Processo',
SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_REPLACE(REPLACE(REPLACE(T.content,'<','<'),'>','>'),'<[^>]*>',''),'| Importância: ',-1),'| ',1) AS 'Importância',
SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_REPLACE(REPLACE(REPLACE(T.content,'<','<'),'>','>'),'<[^>]*>',''),'| Urgência: ',-1),'| ',1) AS 'Urgência',
CASE
WHEN L.name IS NOT NULL
THEN L.name
ELSE 'SETIC'
END AS 'Cliente',
CASE
WHEN U.firstname <> '' OR U.realname <> ''
THEN UPPER(CONCAT(CONCAT(U.firstname, ' '), U.realname))
ELSE U.name
END AS 'Técnico',
CAST((CASE
WHEN T.status = 1
THEN 'Novo'
WHEN T.status = 4
THEN 'Pendente'
WHEN T.status = 5
THEN 'Solucionado'
WHEN T.status = 6
THEN 'Fechado'
ELSE 'Processando'
END) AS CHAR (15)) AS 'Status',
T.date AS 'Abertura',
T.solvedate AS 'Solução',
T.closedate AS 'Fechamento',
V2.`Primeiro contato`,
V2.`Resposta inicial`,
UNIX_TIMESTAMP(T.solvedate) - UNIX_TIMESTAMP(T.date) AS 'Tempo até a solução'
FROM
glpi.glpi_tickets T LEFT JOIN
glpi.glpi_itilcategories C ON T.itilcategories_id = C.id LEFT JOIN
glpi.glpi_locations L ON T.locations_id = L.id LEFT JOIN
glpi.glpi_groups_tickets GT ON T.id = GT.tickets_id LEFT JOIN
glpi.glpi_groups G ON GT.groups_id = G.id LEFT JOIN
glpi.glpi_tickets_users TU ON T.id = TU.tickets_id LEFT JOIN
glpi.glpi_users U ON U.id = TU.users_id LEFT JOIN
glpi.vw_chamados_temporesposta2 V2 ON T.id = V2.Chamado
WHERE
T.is_deleted = 0 AND C.id = 985 AND TU.type = 2
GROUP BY T.id;