Forum GLPI-Project

sebastian.123456

Member

Registered: 2018-08-31

Posts: 17

Re: Help with MySQL views

SELECT ti.id AS id_ticket, ti.name AS title,cat.name AS category,group_concat(tech.name SEPARATOR ' ') AS Technician,req.name AS requester,
CASE
    WHEN ti.status = 1 THEN "Nuevo"
    WHEN ti.status = 2 THEN "Asignado"
    WHEN ti.status = 3 THEN "Planificado"
    WHEN ti.status = 4 THEN "En espera"
    WHEN ti.status = 5 THEN "Resuelto"
    WHEN ti.status = 6 THEN "Cerrado"
END AS status ,
sum(ta.actiontime/60) AS Task_duration,
gr.name AS technician_group,
ti.date AS open_date,
glpi_requesttypes.name AS ticket_source,

CASE
	WHEN ti.type = 1 then "Incidente" 
    when ti.type = 2 then "Solicitud"
END AS ticket_type,

CASE
    WHEN ti.priority = 1 THEN "Muy Baja"
    WHEN ti.priority = 2 THEN "Baja"
    WHEN ti.priority = 3 THEN "Media"
    WHEN ti.priority = 4 THEN "Alta"
    WHEN ti.priority = 5 THEN "Muy Alta"
    WHEN ti.priority = 6 THEN "Cerrado"
END AS prioridad



FROM glpi_tickets AS ti
JOIN glpi_itilcategories AS cat on cat.id=ti.itilcategories_id
LEFT OUTER JOIN glpi_tickets_users AS tutech on tutech.tickets_id=ti.id and tutech.type=2
LEFT OUTER JOIN glpi_users AS tech on tech.id=tutech.users_id
LEFT OUTER JOIN glpi_tickets_users AS tureq on tureq.tickets_id=ti.id and tureq.type=1
LEFT OUTER JOIN glpi_users AS req on req.id=tureq.users_id
LEFT OUTER JOIN glpi_tickettasks AS ta on ta.tickets_id=ti.id
LEFT OUTER JOIN glpi_groups_tickets AS gt on gt.tickets_id=ti.id and gt.type=2
LEFT OUTER JOIN glpi_groups AS gr on gr.id=gt.groups_id
JOIN glpi_requesttypes ON glpi_requesttypes.id = ti.`requesttypes_id`
WHERE ti.date>'2016-01-01'
GROUP BY  id_ticket,title,category,prioridad,requester,status,technician_group,open_date,ticket_source,ticket_type

hello, that I achieved in the consultation, but can you separate the technical column?

for example

technician1   technician 2
bill gates      mcano

or not?

LaDenrée

HELPER

Registered: 2012-11-19

Posts: 6,167

Re: Help with MySQL views

I can't.
maybe  some Mysql expert can do it .

---

Trouver la panne avant de réparer...
GLPI10.0.10 (ubuntu 22.04 PHP8.1  Mariadb10.6 ) plugins : comportements 2.7.2 reports 1.16.0 formcreator 2.13.8, datainjection 2.13.4 fields 1.21.6

sebastian.123456

Member

Registered: 2018-08-31

Posts: 17

Re: Help with MySQL views

ok, anyway, thank you very much for all your support @LaDenrée.

you can use substring?????

Last edited by sebastian.123456 (2018-09-11 20:56:25)

orthagh

Administrator

From: TECLIB - CAEN

Registered: 2010-11-30

Posts: 662

Website

Re: Help with MySQL views

I think it's possible with some IF plus LEFT|RIGHT statements but not easily.
More, it will be very difficult to read and maintain.
And you'll have a fixed number of column where some can be null.
Ex:

Tech 1 | Tech 2 | Tech 3 |
john   | Jane   | null   |
null   | null   | null   |
etc

I think a group_concat with a comma separator is a better idea for the above reason.

sebastian.123456

Member

Registered: 2018-08-31

Posts: 17

Re: Help with MySQL views

according to my query, how could you guide me?