You are not logged in.

Announcement

 Téléchargez la dernière version stable de GLPI      -     Et vous, que pouvez vous faire pour le projet GLPI ? :  Contribuer
 Download last stable version of GLPI                      -     What can you do for GLPI ? :  Contribute

#26 2018-09-11 17:55:49

sebastian.123456
Guest
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?

Offline

#27 2018-09-11 17:58:03

LaDenrée
HELPER
Registered: 2012-11-19
Posts: 4,159

Re: Help with MySQL views

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


Trouver la panne avant de réparer...
*GLPI 9.1.6+fusion9.1+1.1+behaviours1.5.0+reports+fields+appliances+pdf+badges+formcreator2.5.2 PHP7.0 Mariadb10
*GLPI 9.2.4(behaviours1.5.2+fusion9.2+1.0+applicatifs2.3.0+dashboard 0.8.9)hebergé sur serveur mutualisé.
*GLPI 9.3.1  en test (ubuntu 16.04 mariadb 10.2.4)

Offline

#28 2018-09-11 18:00:28

sebastian.123456
Guest
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)

Offline

#29 2018-09-12 08:52:54

orthagh
Administrator
From: TECLIB - CAEN
Registered: 2010-11-30
Posts: 553
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.

Offline

#30 2018-09-12 13:14:55

sebastian.123456
Guest
Registered: 2018-08-31
Posts: 17

Re: Help with MySQL views

according to my query, how could you guide me?

Offline

Board footer

Powered by FluxBB