source: sumstatsplugin/0.12/sumstats/web_ui.py

Last change on this file was 15069, checked in by Ryan J Ollos, 8 years ago

0.9.1dev: Fix traceback when overall_completion is None

The issue occurs in Trac 0.12 - as_bool was not as
robust as in later versions of Trac.

Fixes #12589.
File size: 17.0 KB
Line 
1# -*- coding: utf-8 -*-
2#
3# Copyright (C) 2011-2012 Rob Guttman <guttman@alum.mit.edu>
4# All rights reserved.
5#
6# This software is licensed as described in the file COPYING, which
7# you should have received as part of this distribution.
8#
9
10import re
11import copy
12import time
13import urllib
14from datetime import datetime, timedelta
15
16from trac.core import *
17from trac.config import Option, ListOption
18from trac.ticket import TicketSystem, Milestone
19from trac.ticket.roadmap import TicketGroupStats, ITicketGroupStatsProvider
20from trac.ticket.roadmap import DefaultTicketGroupStatsProvider
21from trac.web import IRequestHandler, IRequestFilter
22from trac.web.chrome import ITemplateProvider, add_stylesheet
23from trac.util import as_bool
24from trac.util.translation import _
25
26# FIXME: sql injection
27
28class SumTicketGroupStatsProvider(DefaultTicketGroupStatsProvider):
29   
30    implements(ITicketGroupStatsProvider,
31               IRequestFilter, IRequestHandler, ITemplateProvider)
32
33    # trac.ini options
34    sum_field = Option('sumstats', 'field', '', _("Field name to sum."))
35    sum_label = Option('sumstats', 'label', _('tickets'),
36        _("Plural name of the items being summed."))
37    drilldown_label = Option('sumstats', 'drilldown_label', _('Ticket status'),
38        _("Name of the milestone drilldown label."))
39    query_args = Option('sumstats', 'query_args', '',
40        _("Comma-delimited args to add to all queries."))
41    filter = ListOption('sumstats', 'filter', [],
42        doc=_("Filters out tickets to sum (e.g, type!=epic)."))
43   
44   
45    def _get_groups(self, ticket_ids=None):
46        """Return a set of all ticket group names in order."""
47        groups = []
48        for group in copy.deepcopy(self._get_ticket_groups()):
49            self._update_group(group, ticket_ids)
50            groups.append(group)
51        return groups
52   
53    def _update_group(self, group, ticket_ids):
54        """Update the given group dict extracted from the [milestone-groups]
55        section.  Also, a total count for the group is determined based on
56        the specified field and its value."""
57        field = self._get_field(group.get('field','status'))
58        if group.get('status') == '*':
59            group['status'] = self._get_remaining_values(group, field)
60        group['query_args'] = self._get_query_args(group)
61        group['total'] = self._get_total(group, field, ticket_ids)
62   
63    def _get_field(self, field_name):
64        """Returns the ticket field corresponding to the given group."""
65        for field in TicketSystem(self.env).get_ticket_fields():
66            if field['name'] == field_name:
67                return field
68        raise Exception("Ticket field '%s' not found." % field_name)
69   
70    def _get_remaining_values(self, group, field):
71        """Returns a comma-delimited string of all remaining values for
72        a 'catch all' value of '*'."""
73        remaining_values = set(field.get('options'))
74        for grp in self._get_ticket_groups():
75            if grp.get('field','status') != group.get('field','status'):
76                continue
77            value = grp.get('status')
78            if value == '*':
79                continue # TODO: raise if more than one '*'
80            # TODO: raise if reused a value, or if no remaining values
81            remaining_values -= set([v.strip() for v in value.split(',')])
82        return ','.join(remaining_values)
83   
84    def _get_query_args(self, group):
85        query_args = {}
86        value = group.get('status')
87        for v in value.split(','):
88            query_args.setdefault(group.get('field','status'), []).append(v)
89        args = group.get('query_args','').split(',')+self.query_args.split(',')
90        for arg in [kv for kv in args if '=' in kv]:
91            k, v = [a.strip() for a in arg.split('=', 1)]
92            query_args.setdefault(k, []).append(v)
93        return query_args
94       
95   
96    def _get_total(self, group, field, ticket_ids):
97        """Return either the total (a) count of tickets, or (b) sum of
98        the field values."""
99        if ticket_ids is None:
100            return 0.0
101       
102        name = field['name']
103        sum_field = self.sum_field and self._get_field(self.sum_field) or None
104       
105        db = self.env.get_db_cnx()
106        cursor = db.cursor()
107        sql = "SELECT "
108        id_list = ",".join([str(x) for x in sorted(ticket_ids)])
109       
110        # handle count vs. sum
111        if sum_field:
112            if 'custom' in sum_field:
113                sql += "SUM(sf.value) "
114            else:
115                sql += "SUM(%s) " % self.sum_field
116        else:
117            sql += "COUNT(*) "
118        sql += "FROM ticket t "
119       
120        # add sum field join
121        if sum_field and 'custom' in sum_field:
122            sql += "LEFT OUTER JOIN ticket_custom sf ON sf.ticket = t.id" +\
123                     " AND sf.name='%s' " % self.sum_field
124       
125        # handle built-in vs. custom field
126        value = group.get('status','')
127        vals = ','.join(["'%s'" % v.strip() for v in value.split(',')])
128        if 'custom' in field:
129            sql += "LEFT OUTER JOIN ticket_custom ff ON ff.ticket = t.id" +\
130                     " AND ff.name='%s' WHERE ff.value IN (%s) " % (name,vals)
131        else:
132            sql += "WHERE t.%s IN (%s) " % (name,vals)
133       
134        # apply the filter (if any)
135        for filter in self.filter:
136            fld,val = filter.split('=',1)
137            if fld.endswith('!'):
138                sql += "AND t.%s != '%s' " % (fld[:-1],val)
139            else:
140                sql += "AND t.%s = '%s' " % (fld,val)
141       
142        # assume only open tickets
143        if name not in ('status','resolution'):
144            sql += "AND t.status != 'closed' "
145            group.get('query_args').setdefault('status', []).append('!closed')
146       
147        sql += "AND t.id IN (%s);" % id_list
148        cursor.execute(sql)
149        for (total,) in cursor:
150            return float(total or 0.0)
151        return 0.0
152   
153   
154    # ITicketGroupStatsProvider methods
155    def get_ticket_group_stats(self, ticket_ids):
156        stat = TicketGroupStats(self.drilldown_label, self.sum_label)
157        for group in self._get_groups(ticket_ids):
158            stat.add_interval(
159                title=group.get('label', group['name']),
160                count=group.get('total', 0),
161                qry_args=group.get('query_args', {}),
162                css_class=group.get('css_class', group['name']),
163                overall_completion=as_bool(group.get('overall_completion', '')))
164        stat.refresh_calcs()
165        return stat
166
167    # IRequestFilter methods
168    def pre_process_request(self, req, handler):
169        return handler
170
171    def post_process_request(self, req, template, data, content_type):
172        if req.path_info == '/sumstats/sumstats.css':
173            return template, data, 'text/css'
174        if 'milestone-groups' in self.config and \
175           (re.match(r'/roadmap/?', req.path_info)
176            or re.match(r'/milestone/.*', req.path_info)):
177            add_stylesheet(req, '/sumstats/sumstats.css')
178        return template, data, content_type
179
180    # IRequestHandler methods
181    def match_request(self, req):
182        return req.path_info == '/sumstats/sumstats.css'
183
184    def process_request(self, req):
185        data = {'groups': self._get_groups()}
186        return 'sumstats.css', data, 'text/css'
187
188    # ITemplateProvider methods
189    def get_htdocs_dirs(self):
190        return []
191
192    def get_templates_dirs(self):
193        from pkg_resources import resource_filename
194        return [resource_filename(__name__, 'templates')]
195
196
197class SumTicketDataSourceProvider(Component):
198    implements(IRequestHandler)
199
200    @property
201    def sum_field(self):
202        return self.config.get('sumstats', 'field')
203
204    @property
205    def filter(self):
206        return self.config.getlist('sumstats', 'filter')
207
208    # IRequestHandler methods
209    def match_request(self, req):
210        return req.path_info.startswith('/sumstats/query') and \
211               self._get_milestone(req)
212
213    def process_request(self, req):
214        """Process data source request.  The one param is grabbed from the
215        referral url instead of as a param.
216        """
217        try:
218            columns = self._get_columns(req)
219            milestone = self._get_milestone(req)
220            data = self._get_burndown(columns, milestone)
221            code,type,msg = 200,'application/json',data
222        except Exception, e:
223            import traceback;
224            code,type = 500,'text/plain'
225            msg = "Oops...\n"+traceback.format_exc()+"\n"
226            self.log.error(msg)
227        req.send_response(code)
228        req.send_header('Content-Type', type)
229        req.send_header('Content-Length', len(msg))
230        req.end_headers()
231        req.write(msg)
232   
233    # private methods
234    def _get_columns(self, req):
235        """Extract which columns and their order from the query string.
236        This enables alternative chart configurations.  Note that the
237        'day' will remain as the first column always."""
238        columns = []
239        tq = req.args.get('tq')
240        if tq:
241            select_re = re.compile(r"select (?P<columns>.+)( from)?")
242            match = select_re.search(tq)
243            if match:
244                cols = match.groupdict()['columns']
245                for col in cols.split(','):
246                    columns.append(col.strip())
247            else:
248                raise Exception("Unable to parse query %s" % tq)
249        else:
250            columns = ['todo','done']
251        return columns
252   
253    def _get_milestone(self, req):
254        """Extract the milestone from the referer url.  If not found then
255        return the current milestone."""
256        path = req.environ.get('HTTP_REFERER','')
257        milestone_re = re.compile(r"/milestone/(?P<milestone>[^?]+)")
258        match = milestone_re.search(path)
259        if match:
260            name = urllib.unquote(match.groupdict()['milestone'])
261            for m in Milestone.select(self.env, include_completed=True):
262                if m.name == name:
263                    return m
264            else:
265                raise Exception("Milestone %s not found" % name)
266        else:
267            # milestone not found in url, so assume current milestone
268            for m in Milestone.select(self.env, include_completed=False):
269                return m
270            else:
271                raise Exception("No provided or current milestone")
272        return None
273   
274    def _get_start_date(self, milestone):
275        """Returns the start date for the given milestone based on the
276        prior milestone as follows - this milestone's start date =
277       
278         * the day after the prior milestone's completed date (if completed)
279         * else the day after the prior milestone's due date (if due)
280         * else today
281        """
282        prior = None
283        for m in Milestone.select(self.env, include_completed=True):
284            if m.name == milestone.name:
285                break
286            prior = m
287        else:
288            raise Exception("Milestone %s not found" % milestone.name)
289       
290        if prior:
291            if prior.completed:
292                return self._get_day(prior.completed, 'next')
293            if prior.due:
294                return self._get_day(prior.due, 'next')
295        return self._get_day(datetime.utcnow(), 'end')
296   
297    def _get_end_date(self, milestone, start_date):
298        """Returns the end date to use for the burndown chart based on the
299        given milestone.
300        """
301        if milestone.completed:
302            return self._get_day(milestone.completed, 'end')
303        today = self._get_day(datetime.utcnow(), 'end')
304        if milestone.due and today > self._get_day(milestone.due, 'end'):
305            return today
306        if today > start_date:
307            return today
308        return self._get_day(start_date, 'end')
309   
310    def _get_day(self, date, directive):
311        """Returns a time of day UTC based on the given directive of:
312       
313         * start - midnight
314         * end - one microsecond before midnight
315         * next - midnight of next day
316        """
317        # midnight UTC
318        day = datetime(date.year, date.month, date.day, 0, 0, 0, 0, None)
319        if directive == 'start':
320            return day
321       
322        # midnight the next day UTC
323        day += timedelta(days=1); # add a day
324        if directive == 'next':
325            return day
326       
327        # one second before the next day UTC (end of day)
328        return day - timedelta(0, seconds=1)
329       
330    def _get_burndown(self, columns, milestone):
331        """Return data for a burndown chart for the given columns."""
332        start_date = self._get_start_date(milestone)
333        end_date = self._get_end_date(milestone, start_date)
334       
335        # find a nice buffer
336        today = self._get_day(datetime.utcnow(), 'end').replace(tzinfo=None)
337        if today < start_date:
338            buffer = start_date
339        else:
340            buffer = today
341        buffer += timedelta(days=7)
342        if milestone.completed:
343            date = milestone.completed.replace(tzinfo=None)
344            if date < buffer:
345                buffer = date
346        elif milestone.due:
347            date = milestone.due.replace(tzinfo=None)
348            if date < today:
349                buffer = today
350            elif date < buffer:
351                buffer = date
352       
353        # fetch the data for each day
354        rows = []
355        day = self._get_day(start_date, 'end') # include whole day
356        while day <= buffer:
357            if day > end_date:
358                total,done,todo = 0.0,0.0,0.0
359            else:
360                total,done,todo = self._get_burndown_day(milestone, day)
361            # add to rows
362            rows.append({'day':day, 'total':total, 'todo':todo, 'done':done})
363            day += timedelta(days=1)
364       
365        # package the data for the google visualization query response
366        import gviz_api
367        schema = {"day": ("date", "Day"),
368                  "total": ("number", "Total"),
369                  "todo": ("number", "To Do"),
370                  "done": ("number", "Done")}
371        data = gviz_api.DataTable(schema)
372        data.LoadData(rows)
373        return data.ToJSonResponse(
374            columns_order=("day",) + tuple(columns),
375            order_by="day")
376   
377    def _get_burndown_day(self, milestone, day):
378        """Return the total work, work done and left to do on a given day."""
379        ms = long(time.mktime(day.utctimetuple()))*long(1000000)
380       
381        # decide to sum or count
382        if self.sum_field:
383            sum = "COALESCE(tc1.newvalue,e.value)"
384        else:
385            sum = "1" # equivalent to COUNT(*)
386       
387        sql = """
388        SELECT SUM(%(sum)s) AS "Total",
389               SUM(CASE COALESCE(tc5.newvalue,'new')
390                   WHEN 'closed' THEN %(sum)s
391                   ELSE 0 END) AS "Done",
392               SUM(CASE COALESCE(tc5.newvalue,'new')
393                   WHEN 'closed' THEN 0
394                   ELSE %(sum)s END) AS "To Do"
395        FROM ticket t
396        LEFT OUTER JOIN ticket_change tc1 ON tc1.ticket=t.id AND
397                          tc1.field='%(field)s' AND tc1.time=
398                          (SELECT max(tc2.time) FROM ticket_change tc2
399                           WHERE tc2.ticket=t.id AND tc2.field='%(field)s'
400                            AND tc2.time<=%(day)s)
401        LEFT OUTER JOIN ticket_change tc3 ON tc3.ticket = t.id AND
402                          tc3.field='milestone' AND tc3.time=
403                          (SELECT max(tc4.time) FROM ticket_change tc4
404                           WHERE tc4.ticket=t.id AND tc4.field='milestone'
405                            AND tc4.time<=%(day)s)
406        LEFT OUTER JOIN ticket_change tc5 ON tc5.ticket = t.id AND
407                          tc5.field='status' AND tc5.time=
408                          (SELECT max(tc6.time) FROM ticket_change tc6
409                           WHERE tc6.ticket=t.id AND tc6.field='status'
410                            AND tc6.time<=%(day)s)
411        LEFT OUTER JOIN ticket_change tc7 ON tc7.ticket = t.id AND
412                          tc7.field='resolution' AND tc7.time=
413                          (SELECT max(tc8.time) FROM ticket_change tc8
414                           WHERE tc8.ticket=t.id AND tc8.field='resolution'
415                            AND tc8.time<=%(day)s)
416        LEFT OUTER JOIN ticket_custom e ON e.ticket=t.id AND e.name='%(field)s'
417        WHERE (COALESCE(tc5.newvalue,'new')!='closed'
418               OR COALESCE(tc7.newvalue,'')='fixed')
419         AND t.time <= %(day)s
420         AND COALESCE(tc3.newvalue,t.milestone)='%(milestone)s'
421        """ % {'sum':sum,'field':self.sum_field,
422               'day':ms,'milestone':milestone.name}
423        self.log.debug("burndown day %s:%s" % (str(day),sql))
424       
425        # apply the filter (if any)
426        for filter in self.filter:
427            fld,val = filter.split('=',1)
428            if fld.endswith('!'):
429                sql += "AND t.%s != '%s' " % (fld[:-1],val)
430            else:
431                sql += "AND t.%s = '%s' " % (fld,val)
432       
433        db = self.env.get_db_cnx()
434        cursor = db.cursor()
435        cursor.execute(sql)
436        for total,done,todo in cursor:
437            return float(total or 0.0),float(done or 0.0),float(todo or 0.0)
438        return 0.0,0.0,0,0
Note: See TracBrowser for help on using the repository browser.