« GLPI/Script SQL » : différence entre les versions
Aller à la navigation
Aller à la recherche
Ligne 15 : | Ligne 15 : | ||
SELECT DISTINCT glpi_tickets_users.tickets_id, glpi_tickets_users.users_id FROM glpi_tickets_users | SELECT DISTINCT glpi_tickets_users.tickets_id, glpi_tickets_users.users_id FROM glpi_tickets_users | ||
WHERE glpi_tickets_users.tickets_id in | WHERE glpi_tickets_users.tickets_id in | ||
(SELECT DISTINCT glpi_tickets_users.tickets_id | ( | ||
SELECT DISTINCT glpi_tickets_users.tickets_id | |||
FROM glpi_tickets, glpi_tickettasks, glpi_tickets_users, glpi_itilcategories | FROM glpi_tickets, glpi_tickettasks, glpi_tickets_users, glpi_itilcategories | ||
WHERE ( | WHERE ( | ||
( | |||
glpi_tickets.status = 1 | glpi_tickets.status = 1 | ||
OR glpi_tickets.status = 2 | OR glpi_tickets.status = 2 | ||
OR glpi_tickets.status = 3 | OR glpi_tickets.status = 3 | ||
OR glpi_tickets.status = 4 | OR glpi_tickets.status = 4 | ||
) | |||
AND glpi_tickets.id = glpi_tickets_users.tickets_id | |||
AND glpi_tickets.itilcategories_id = glpi_itilcategories.id | |||
AND glpi_tickets.itilcategories_id = 33 | |||
AND glpi_tickets_users.users_id = 560 | |||
) | ) | ||
) | ) |
Version du 23 janvier 2023 à 20:27
Voici quelques exemples de script SQL pour traiter certains aspects.
Exemple de script SQL
Affichage de certains tickets d'un technicien
Affichage des tickets d'un technicien :
- avec affichage du numéro du ticket et du numéro du technicien ;
- dont le statut n'est ni résolu ni clos ;
- dont la catégorie a pour numéro 33 ;
- dont un des techniciens affecté a pour numéro 560.
SELECT DISTINCT glpi_tickets_users.tickets_id, glpi_tickets_users.users_id FROM glpi_tickets_users WHERE glpi_tickets_users.tickets_id in ( SELECT DISTINCT glpi_tickets_users.tickets_id FROM glpi_tickets, glpi_tickettasks, glpi_tickets_users, glpi_itilcategories WHERE ( ( glpi_tickets.status = 1 OR glpi_tickets.status = 2 OR glpi_tickets.status = 3 OR glpi_tickets.status = 4 ) AND glpi_tickets.id = glpi_tickets_users.tickets_id AND glpi_tickets.itilcategories_id = glpi_itilcategories.id AND glpi_tickets.itilcategories_id = 33 AND glpi_tickets_users.users_id = 560 ) ) AND glpi_tickets_users.users_id = 560 ;
Modification d'affectation de tickets d'un technicien
Remplacement du technicien (560) de certains tickets par un autre technicien (762) :
- dont le statut n'est ni résolu ni clos ;
- dont la catégorie a pour numéro 33 ;
- dont le technicien affecté a pour numéro 560 ;
UPDATE glpi_tickets_users SET users_id = 762 WHERE glpi_tickets_users.tickets_id in (SELECT DISTINCT glpi_tickets_users.tickets_id FROM glpi_tickets, glpi_tickettasks, glpi_itilcategories WHERE ( ( glpi_tickets.status = 1 OR glpi_tickets.status = 2 OR glpi_tickets.status = 3 OR glpi_tickets.status = 4 ) AND glpi_tickets.id = glpi_tickets_users.tickets_id AND glpi_tickets.itilcategories_id = glpi_itilcategories.id AND glpi_tickets.itilcategories_id = 33 AND glpi_tickets_users.users_id = 560 ) ) AND glpi_tickets_users.users_id = 560 ;
Requête
Référence :
UPDATE glpi_tickets SET status= "1",takeintoaccount_delay_stat="0" WHERE id IN ( SELECT glpi_tickets.id FROM glpi_tickets LEFT JOIN glpi_tickets_users ON glpi_tickets.id = glpi_tickets_users.tickets_id AND glpi_tickets_users.type = "2" WHERE glpi_tickets.id IN ( SELECT glpi_tickets.id FROM glpi_tickets LEFT JOIN glpi_groups_tickets ON glpi_tickets.id = glpi_groups_tickets.tickets_id WHERE glpi_tickets.status = "2" AND glpi_groups_tickets.groups_id = "1" AND glpi_tickets.is_deleted = "0" ) AND glpi_tickets_users.type IS NULL )