Forum GLPI-Project

augusto.ferronato

Member

From: Brasilia - DF / Brasil

Registered: 2008-03-19

Posts: 190

OFF-TOPIC (Help GLPI + Pentaho)

Hello,
I trying do make a report custom, using a BI (Pentaho www.pentaho.com) and i use this MySQL Command Line:

SELECT DISTINCT glpi_tracking.*, glpi_dropdown_tracking_category.completename AS catname, glpi_groups.name as groupname , glpi_entities.completename as entityname, glpi_tracking.FK_entities as entityID
FROM glpi_tracking
LEFT JOIN glpi_groups ON ( glpi_tracking.FK_group = glpi_groups.ID)
LEFT JOIN glpi_dropdown_tracking_category ON ( glpi_tracking.category = glpi_dropdown_tracking_category.ID)
LEFT JOIN glpi_entities ON ( glpi_entities.ID = glpi_tracking.FK_entities)
WHERE ( 1 ) AND glpi_tracking.closedate >= '2010-03-01' AND glpi_tracking.closedate <= adddate( '2010-03-31' , INTERVAL 1 DAY )
ORDER BY glpi_tracking.date_mod DESC

Ok, with this i can get the HelpDesk itens, and others, but, i need to put a FollowUp to, and the correct follow up, a 3 weeks i'm trying, but unsuccessful.
I'm using GLPI em Debug mode to help me.

How i can put the Follow up in my SQL Query?

Thanks for your support

Best Regards,

---

SO: Gentoo
Version: PROD GLPI 0.84.6 / DEV: SVN
PHP: PHP 5.3.4-pl0-gentoo / MySQL: 5.1.51-log
OCS: OCS-NG 2.0

linker3000

Member

Registered: 2010-03-04

Posts: 56

Re: OFF-TOPIC (Help GLPI + Pentaho)

This is a report I wrote in iReport to show currently open tickets + only the latest follow-up that matches certain criteria (there's a date match and it excludes updates and a few other types of follow-up).

SELECT
     glpi_tracking.id as id,
     glpi_dropdown_tracking_category.name as category,
     glpi_tracking.name AS descr,
     glpi_tracking.date AS tdate,
     datediff(Curdate(), glpi_tracking.date) as age,
     glpi_tracking.date_mod as mdate,
     glpi_tracking.status as status,
     glpi_tracking.author as author,
     glpi_tracking.contents as initdescr,
     case glpi_tracking.priority
      when 1 then 'VLow'
      when 2 then 'Low'
      when 3 then 'Med'
      when 4 then 'High'
      when 5 then 'VHigh'
     end as priority ,
     status AS tstatus,
     request_type AS treq_type,
     glpi_followups.contents as followup,
     glpi_followups.date as fdate,
     glpi_users.name as originator,
     glpi_dropdown_user_types.name as grouping
FROM
     glpi_tracking left join glpi_followups on (glpi_followups.date =

(select max(date) from glpi_followups where (glpi_followups.tracking = glpi_tracking.id) and (glpi_followups.contents not like 'Ticket assign%') and (glpi_followups.contents not like 'Change ticket%')
and (glpi_followups.contents not like 'update of the%') and (glpi_followups.contents not like 'email follow%') )
)

left join glpi_users on glpi_users.id = glpi_tracking.author
left join glpi_dropdown_tracking_category on glpi_dropdown_tracking_category.id = glpi_tracking.category
left join glpi_dropdown_user_types on glpi_dropdown_user_types.ID = glpi_users.type

 where glpi_tracking.status not like 'old%'

order by glpi_dropdown_user_types.name,  glpi_tracking.priority desc, glpi_tracking.date desc, id, priority desc

Hope it helps.

Last edited by linker3000 (2010-06-16 14:01:59)