Modify

Opened 7 months ago

Closed 7 months ago

#11562 closed task (fixed)

Adding a project column to custom reports

Reported by: endquote Owned by: falkb
Priority: low Component: SimpleMultiProjectPlugin
Severity: normal Keywords:
Cc: Trac Release: 1.0

Description

I wanted to modify some of the default reports such as "my tickets" to include a project column. To do that I simply added an additional select:

(
  SELECT smp_project.name
  FROM smp_project, smp_component_project, smp_milestone_project
  WHERE (t.milestone = smp_milestone_project.milestone AND smp_milestone_project.id_project = smp_project.id_project)
  OR (t.component = smp_component_project.component AND smp_component_project.id_project = smp_project.id_project)
  LIMIT 1
) AS project

This works, but it's probably not very efficient. But there is another problem...

I then created a new project, but didn't create any components or milestones for it. Then created a ticket for that project. Of course the above selection doesn't return anything in that case. However if I do a custom query with project is myproject, the ticket comes up correctly.

So my question is how a project gets associated with a ticket, if not via the component/milestone tables.

Attachments (0)

Change History (4)

comment:1 Changed 7 months ago by endquote

It would also be good to filter out tickets assigned to closed projects.

comment:2 Changed 7 months ago by anonymous

why not using the query language? It will look like this:

query:?owner=%24USER
&
status=accepted
&
status=assigned
&
status=needs_work
&
status=new
&
status=reopened
&
status=testing
&
group=userfinish
&
col=id
&
col=summary
&
col=status
&
col=type
&
col=priority
&
col=component
&
col=project
&
col=remaininghours
&
col=userfinish
&
report=34
&
order=priority

comment:3 in reply to: ↑ description Changed 7 months ago by anonymous

Replying to endquote:

So my question is how a project gets associated with a ticket, if not via the component/milestone tables.

The whole stuff with custom ticket-fields is stored in db table ticket_custom with a (ticket,name,value)-record, and that column 'name' in the middle is the ticket field name. All changes to fields are logged in db table ticket_change with a (ticket,time,author,field,oldvalue,newvalue)-record.

In your case, an SQL query must select all records from ticket_custom where name=='project' and value=='myproject', which results in a list of ticket IDs you can use to look up the db table ticket to get their details.

comment:4 Changed 7 months ago by endquote

  • Resolution set to fixed
  • Status changed from new to closed

Thanks, I didn't know about the ticket_custom table. Adding this to my query gets what I need.

(SELECT value FROM ticket_custom WHERE ticket = t.id AND name = 'project') AS project,

Add Comment

Modify Ticket

Action
as closed .
The resolution will be deleted. Next status will be 'reopened'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.