Question

I'm looking to do a sum of time spent on all issues marked with a particular project and area between a start and an end date.

I'm currently putting this information together manually using a combination of saved filters and the Clark Kent plugin. It's not fun.

I'm looking at the XML API as an alternative to gather up this data programmatically but I can't quite seem to get what I want out of the API -- am I missing something or is this just not possible?

I want to get all the time spent on tickets on a project + area between two dates, no matter who the person was working on the ticket.

This seems to be a pretty heavy-weight query right now. I'm thinking:

1. Get a list of tickets that match the project+area filter that span all time. I'm fairly certain I can do this with cmd=search and I don't have to save a filter for each project+area combination I'd want to access this way.
2. Get a list of timesheet intervals for this time period for all users and all cases using the cmd=listIntervals call.
3. Match cases in the list gathered in step 1 against timesheet entries gathered in step 1 and extract elapsed time from those timesheet entries.

Okay, I've got it down to two API calls. But they'll both return quite a bit of data that needs to be sifted through. Am I missing some easier way to pull these time sheet entries from the database?

Answer 1

You could also potentially approach this via a plugin (assuming you're running FogBugz on your own server):

var sb = new StringBuilder();

var rgixProject = new int[]
{
    1, // Project A
    3, // Project B
    4 // Project C
};

var rgixArea = new int[]
{
    1, // Project A - Area 1
    1, // Project A - Area 2
    9, // Project B - Area 1
    10 // Project C - Area 1
};

var qBugs = api.Database.NewSelectQuery("Bug");
qBugs.AddSelect("Bug.ixBug");
qBugs.AddWhere(
    String.Format(
        "Bug.ixProject IN ({0})",
        string.Join(",", Array.ConvertAll(rgixProject, x => x.ToString()))
    )
);
qBugs.AddWhere(
    String.Format(
        "Bug.ixArea IN ({0})",
        string.Join(",", Array.ConvertAll(rgixArea, x => x.ToString()))
    )
);

var dtStart = DateTime.Now.Subtract(new TimeSpan(7 * 24, 0, 0));
var dtEnd = DateTime.Now;

var qIntervals = api.TimeInterval.NewTimeIntervalQuery();
qIntervals.ExcludeUnreadable = false;
qIntervals.AddWhereIn("TimeInterval.ixBug", qBugs);
qIntervals.AddWhere("TimeInterval.dtStart >= @dtStart");
qIntervals.AddWhere("TimeInterval.dtEnd <= @dtEnd");
qIntervals.SetParamDate("dtStart", dtStart);
qIntervals.SetParamDate("dtEnd", dtEnd);

var rgIntervals = qIntervals.List();

// summarize output
sb.AppendLine(qIntervals.ToString() + "<br />");
sb.AppendLine("<ul>");
foreach (var interval in rgIntervals)
{
    sb.AppendLine(
        string.Format(
            "<li>ixPerson: {0}, dtStart: {1}, dtEnd: {2}</li>",
            interval.ixPerson,
            interval.dtStart.ToString(),
            interval.dtEnd.ToString()
        )
    );
}
sb.AppendLine("</ul>");

return sb.ToString();