- SELECT gt.id,
- gt.name,
- gt.date,
- gt.closedate AS closeDate,
- gt.solvedate AS solveDate,
- gt.date_mod AS dateMod,
- gt.due_date AS dueDate,
- gt.users_id_lastupdater AS userIdLastUpdater,
- gt.status,
- gt.users_id_recipient AS userIdRecipient,
- gt.content,
- gt.itilcategories_id AS itilCategoryId,
- gi.completename AS category,
- gt.type,
- gt.solutiontypes_id AS solutionTypeId,
- gt.solution,
- git.items_id AS itemId,
- git.itemtype,
- gt.locations_id AS locationId,
- gl.completename AS locationName,
- Concat(gu.realname, ' ', gu.firstname) AS userRecipient,
- (SELECT Concat(realname, ' ', firstname) AS userApplicant
- FROM glpi_users
- INNER JOIN glpi_tickets_users
- ON glpi_users.id = glpi_tickets_users.users_id
- WHERE glpi_tickets_users.tickets_id = gt.id
- AND type = 1
- LIMIT 1) AS userApplicant,
- (SELECT ( CASE git.itemtype
- WHEN 'Computer' THEN (SELECT name
- FROM glpi_computers
- WHERE id = git.items_id)
- WHEN 'Monitor' THEN (SELECT name
- FROM glpi_computers
- WHERE id = git.items_id)
- WHEN 'Network Equipment' THEN (SELECT name
- FROM glpi_networkequipments
- WHERE id = git.items_id)
- WHEN 'Printer' THEN (SELECT name
- FROM glpi_printers
- WHERE id = git.items_id)
- ELSE NULL
- end ) AS t) AS aliasIT
- FROM glpi_tickets gt
- LEFT JOIN glpi_users gu
- ON gt.users_id_recipient = gu.id
- LEFT JOIN glpi_groups_users ggu
- ON gt.users_id_recipient = ggu.users_id
- LEFT JOIN glpi_groups gg
- ON ggu.groups_id = gg.id
- LEFT JOIN glpi_locations gl
- ON gt.locations_id = gl.id
- LEFT JOIN glpi_itilcategories gi
- ON gt.itilcategories_id = gi.id
- LEFT JOIN glpi_items_tickets git
- ON gt.id = git.tickets_id
- WHERE Date(gt.date) > Date('2015-03-01 00:00:00')
- AND gt.is_deleted = 0
- ORDER BY id DESC,
- gt.date DESC;