1. SELECT gt.id,
  2. gt.name,
  3. gt.date,
  4. gt.closedate AS closeDate,
  5. gt.solvedate AS solveDate,
  6. gt.date_mod AS dateMod,
  7. gt.due_date AS dueDate,
  8. gt.users_id_lastupdater AS userIdLastUpdater,
  9. gt.status,
  10. gt.users_id_recipient AS userIdRecipient,
  11. gt.content,
  12. gt.itilcategories_id AS itilCategoryId,
  13. gi.completename AS category,
  14. gt.type,
  15. gt.solutiontypes_id AS solutionTypeId,
  16. gt.solution,
  17. git.items_id AS itemId,
  18. git.itemtype,
  19. gt.locations_id AS locationId,
  20. gl.completename AS locationName,
  21. Concat(gu.realname, ' ', gu.firstname) AS userRecipient,
  22. (SELECT Concat(realname, ' ', firstname) AS userApplicant
  23. FROM glpi_users
  24. INNER JOIN glpi_tickets_users
  25. ON glpi_users.id = glpi_tickets_users.users_id
  26. WHERE glpi_tickets_users.tickets_id = gt.id
  27. AND type = 1
  28. LIMIT 1) AS userApplicant,
  29. (SELECT ( CASE git.itemtype
  30. WHEN 'Computer' THEN (SELECT name
  31. FROM glpi_computers
  32. WHERE id = git.items_id)
  33. WHEN 'Monitor' THEN (SELECT name
  34. FROM glpi_computers
  35. WHERE id = git.items_id)
  36. WHEN 'Network Equipment' THEN (SELECT name
  37. FROM glpi_networkequipments
  38. WHERE id = git.items_id)
  39. WHEN 'Printer' THEN (SELECT name
  40. FROM glpi_printers
  41. WHERE id = git.items_id)
  42. ELSE NULL
  43. end ) AS t) AS aliasIT
  44. FROM glpi_tickets gt
  45. LEFT JOIN glpi_users gu
  46. ON gt.users_id_recipient = gu.id
  47. LEFT JOIN glpi_groups_users ggu
  48. ON gt.users_id_recipient = ggu.users_id
  49. LEFT JOIN glpi_groups gg
  50. ON ggu.groups_id = gg.id
  51. LEFT JOIN glpi_locations gl
  52. ON gt.locations_id = gl.id
  53. LEFT JOIN glpi_itilcategories gi
  54. ON gt.itilcategories_id = gi.id
  55. LEFT JOIN glpi_items_tickets git
  56. ON gt.id = git.tickets_id
  57. WHERE Date(gt.date) > Date('2015-03-01 00:00:00')
  58. AND gt.is_deleted = 0
  59. ORDER BY id DESC,
  60. gt.date DESC;