GLPI/Script SQL
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, dont le statut n'est ni résolu ni clos :
- 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 ;
- ...
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 = 762 ) ) AND glpi_tickets_users.users_id = 762 ;
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 )