def wiki_activity(project_id, start_date, end_date, groupsize, groupcnt, db, req,
                   authors_limit=None, ignored_authors=None):
    """
    Get query response for specified time interval, for max `authors_limit` most
    active users, excluding `ignored_authors`:
    Data: <author>: <count wiki modifications>
    """
    authors_limit   = authors_limit or 10
    ignored_authors = ignored_authors or ()

    authors_list = _retrieve_most_active_wiki_authors(project_id, authors_limit, ignored_authors,
                                                      start_date, end_date, db)
    wiki_pages_list = _retrieve_wiki_pages(project_id, authors_list, start_date, end_date, db)

    groups_list, authors_data = aggregate_events_by_periods(authors_list, wiki_pages_list,
                                              start_date, groupsize, groupcnt)

    query_response = QueryResponse("wiki_activity", req.href('/chrome'))
    query_response.set_title(_("Wiki activity from %(start_date)s to %(end_date)s",
                               start_date=format_date(start_date, tzinfo=req.tz),
                               end_date=format_date(end_date, tzinfo=req.tz)))

    columns, rows = adapt_to_table(groups_list, authors_data)
    query_response.set_columns(columns)
    query_response.set_results(rows)

    chart = query_response.chart_info
    chart.type = 'Line'
    chart.x_legend = _('Time periods')
    chart.y_legend = _('Wiki modifications')
    chart.x_labels = groups_list
    chart.data = restructure_data(authors_data)
    chart.tool_tip = "#key#<br>%s:#x_label#<br>%s:#val#" % (_('period'), _('Wiki modifications'))

    return query_response

    def expand_macro(self, formatter, name, content):
        offset = +1
        label = None
        if content is not None:
            if ',' in content:
                offset, label = content.split(',', 1)
            elif content and content[0] in '+-':
                offset = content
            else:
                label = content
            try:
                offset = int(offset)
            except ValueError:
                offset = 0
        mp = MyPageModule(self.env)
        base = mp.get_mypage_base(formatter.perm.username)
        all_mypages = mp.get_all_mypages(base)
        r = formatter.resource
        if r.realm == 'wiki' and r.id.startswith(base):
            mypage = r.id
        else:
            tzinfo = getattr(formatter.context.req, 'tz', None)
            now = datetime.now(tzinfo or localtz)
            today = format_date(now, 'iso8601', tzinfo)
            mypage = '/'.join([base, today])
        selected = base
        idx = bisect(all_mypages, mypage)

        # adjust to actual position if mypage exists
        if 0 <= idx - 1 < len(all_mypages) and all_mypages[idx -1] == mypage:
            idx -= 1
        self.log.debug("Reference is %s, pos %d in %r",
                       mypage, idx, all_mypages)

        # Special cases: at the beginning or at the end, the
        # predecessors resp. successors are "missing"
        missing = False
        if idx >= len(all_mypages) - 1 and offset > 0:
            missing, tooltip = True, _("(at the end)")
        elif idx < 1 and offset < 0:
            missing, tooltip = True, _("(at the beginning)")
        if missing:
            if not label:
                label, tooltip = tooltip, None
            return tag.a(label, title=tooltip, class_='missing')

        # Link to the targeted `MyPage` page
        idx += offset
        selected = all_mypages[max(0, min(idx, len(all_mypages) - 1))]
        self.log.debug("With offset %d, going to %d (adjusted to %d)",
                       offset, idx, max(0, min(idx, len(all_mypages) - 1)))
        selected_day = selected.split('/')[-1]
        try:
            tooltip = _("MyPage for %(day)s",
                        day=format_date(parse_date(selected_day)))
        except TracError:
            tooltip = _("non-day page '%(special)'", special=selected_day)
        return tag.a(label if label is not None else selected, title=tooltip,
                     href=formatter.href.wiki(selected))

def ticket_activity_user(project_id, username, start_date, end_date, groupsize, groupcnt, db, req):
    """
    Get query response for specified time interval and `username`:
    Data: <event>: <count events>.
    Events: 'created', 'closed'.
    """
    q = '''
        SELECT t.id, t.time, 'created' AS event
        FROM ticket t
        WHERE t.reporter=%s AND t.project_id=%s AND
              t.time >= %s AND t.time < %s
        UNION
        SELECT t.id, tc.time, 'closed' AS event
        FROM ticket t JOIN ticket_change tc ON t.id = tc.ticket
            AND tc.field='status' AND tc.newvalue='closed'
        WHERE t.owner=%s AND t.project_id=%s AND
              tc.time >= %s AND tc.time < %s
        ORDER BY event
    '''
    cursor = db.cursor()
    cursor.execute(q, (username, project_id, to_utimestamp(start_date), to_utimestamp(end_date))*2)
    etypes = (N_('created'), N_('closed'))
    events = [(r[2], from_utimestamp(r[1]), r[0]) for r in cursor]

    # TODO: count closed once, use global closed set
    def init_set(e):
        return set()
    def add_to_set(stor, idx, event_data):
        stor[idx].add(event_data[2])

    groups_list, groups_data = aggregate_events_by_periods(etypes, events,
                                              start_date, groupsize, groupcnt,
                                              add_to_set, init_set)

    for etype, groups in groups_data.iteritems():
        for idx, ids in enumerate(groups):
            groups[idx] = len(ids)

    query_response = QueryResponse("ticket_activity", req.href('/chrome'))
    query_response.set_title(_("Ticket activity from %(start_date)s to %(end_date)s",
                               start_date=format_date(start_date, tzinfo=req.tz),
                               end_date=format_date(end_date, tzinfo=req.tz)))

    groups_data = translate_keys(groups_data)
    columns, rows = adapt_to_table(groups_list, groups_data)
    query_response.set_columns(columns)
    query_response.set_results(rows)

    chart = query_response.chart_info
    chart.type = 'Line'
    chart.width = 600
    chart.x_legend = _('Time periods')
    chart.y_legend = _('Tickets')
    chart.x_labels = groups_list
    chart.data = restructure_data(groups_data)
    chart.tool_tip = "#key#<br>%s:#x_label#<br>%s:#val#" % (_('period'), _('tickets'))

    return query_response

    def get_work_log(self, mode='all'):
        db = self.env.get_db_cnx()
        cursor = db.cursor()
        if mode == 'user':
            cursor.execute('SELECT wl.user, s.value, wl.starttime, wl.endtime, wl.ticket, t.summary, t.status, wl.comment '
                           'FROM work_log wl '
                           'INNER JOIN ticket t ON wl.ticket=t.id '
                           'LEFT JOIN session_attribute s ON wl.user=s.sid AND s.name=\'name\' '
                           'WHERE wl.user=%s '
                           'ORDER BY wl.lastchange DESC', (self.authname,))
        elif mode == 'summary':
            cursor.execute('SELECT wl.user, s.value, wl.starttime, wl.endtime, wl.ticket, t.summary, t.status, wl.comment '
                           'FROM (SELECT user,MAX(lastchange) lastchange FROM work_log GROUP BY user) wlt '
                           'INNER JOIN work_log wl ON wlt.user=wl.user AND wlt.lastchange=wl.lastchange '
                           'INNER JOIN ticket t ON wl.ticket=t.id '
                           'LEFT JOIN session_attribute s ON wl.user=s.sid AND s.name=\'name\' '
                           'ORDER BY wl.lastchange DESC, wl.user')
        else:
            cursor.execute('SELECT wl.user, s.value, wl.starttime, wl.endtime, wl.ticket, t.summary, t.status, wl.comment '
                           'FROM work_log wl '
                           'INNER JOIN ticket t ON wl.ticket=t.id '
                           'LEFT JOIN session_attribute s ON wl.user=s.sid AND s.name=\'name\' '
                           'ORDER BY wl.lastchange DESC, wl.user')
        
        rv = []
        for user,name,starttime,endtime,ticket,summary,status,comment  in cursor:
            starttime = float(starttime)
            endtime = float(endtime)
            
            started = datetime.fromtimestamp(starttime)
            
            dispname = user
            if name:
                dispname = '%s (%s)' % (name, user)
            
            if not endtime == 0:
                finished = datetime.fromtimestamp(endtime)
                delta = 'Worked for %s (between %s %s and %s %s)' % \
                        (pretty_timedelta(started, finished),
                         format_date(starttime), format_time(starttime),
                         format_date(endtime), format_time(endtime))
            else:
                delta = 'Started %s ago (%s %s)' % \
                        (pretty_timedelta(started),
                         format_date(starttime), format_time(starttime))

            rv.append({'user': user,
                       'name': name,
                       'dispname': dispname,
                       'starttime': int(starttime),
                       'endtime': int(endtime),
                       'delta': delta,
                       'ticket': ticket,
                       'summary': summary,
                       'status': status,
                       'comment': comment})
        return rv
        

 def test_can_not_rename_sprints_to_have_slash_in_name(self):
     req = self.teh.mock_request(Usernames.product_owner)
     req.args = dict(edit='edit', save=True, 
                     sprint_name='a/b', 
                     name='fnord',
                     start=format_date(self.sprint.start), 
                     end=format_date(self.sprint.end), 
                     milestone=self.sprint.milestone)
     self.assert_raises(ICommand.NotValidError, self.view.do_post, req)
     self.assert_false(Sprint(self.env, name='a/b').exists)

    def test_format_compatibility(self):
        tz = datefmt.timezone("GMT +2:00")
        t = datetime.datetime(2010, 8, 28, 11, 45, 56, 123456, datefmt.utc)
        tz_t = datetime.datetime(2010, 8, 28, 13, 45, 56, 123456, tz)

        # Converting babel's format to strftime format
        self.assertEqual(tz_t.strftime("%x %H:%M").decode("utf-8"), datefmt.format_datetime(t, "short", tz))
        self.assertEqual(tz_t.strftime("%x").decode("utf-8"), datefmt.format_date(t, "short", tz))
        self.assertEqual(tz_t.strftime("%H:%M").decode("utf-8"), datefmt.format_time(t, "short", tz))
        for f in ("medium", "long", "full"):
            self.assertEqual(tz_t.strftime("%x %X").decode("utf-8"), datefmt.format_datetime(t, f, tz))
            self.assertEqual(tz_t.strftime("%x").decode("utf-8"), datefmt.format_date(t, f, tz))
            self.assertEqual(tz_t.strftime("%X").decode("utf-8"), datefmt.format_time(t, f, tz))

    def test_can_create_sprints_for_milestones_with_slash_in_name(self):
        self.teh.create_milestone("milestone/fnord")
        req = self.teh.mock_request(Usernames.product_owner)

        req.args = dict(add='add', sprint_name='fnord', start=format_date(now()),
                        duration=10, milestone="milestone/fnord")
        self.assert_raises(RequestDone, self.view.do_post, req)

 def _do_list(self):
     print_table([(m.name, m.due and
                     format_date(m.due, console_date_format),
                   m.completed and
                     format_datetime(m.completed, console_datetime_format))
                  for m in model.Milestone.select(self.env)],
                 [_("Name"), _("Due"), _("Completed")])

 def _get_list(self, sids):
     all_anon = "anonymous" in sids or "*" in sids
     all_auth = "authenticated" in sids or "*" in sids
     sids = set(self._split_sid(sid) for sid in sids if sid not in ("anonymous", "authenticated", "*"))
     rows = self.env.db_query(
         """
         SELECT DISTINCT s.sid, s.authenticated, s.last_visit,
                         n.value, e.value, h.value
         FROM session AS s
           LEFT JOIN session_attribute AS n
             ON (n.sid=s.sid AND n.authenticated=s.authenticated
                 AND n.name='name')
           LEFT JOIN session_attribute AS e
             ON (e.sid=s.sid AND e.authenticated=s.authenticated
                 AND e.name='email')
           LEFT JOIN session_attribute AS h
             ON (h.sid=s.sid AND h.authenticated=s.authenticated
                 AND h.name='default_handler')
         ORDER BY s.sid, s.authenticated
         """
     )
     for sid, authenticated, last_visit, name, email, handler in rows:
         if all_anon and not authenticated or all_auth and authenticated or (sid, authenticated) in sids:
             yield (
                 sid,
                 authenticated,
                 format_date(to_datetime(last_visit), console_date_format),
                 name,
                 email,
                 handler,
             )

    def get_daily_backlog_chart(self, backlog_history):

        numdates = backlog_history[0]
        backlog_stats = backlog_history[1]

        # create counted list.
        opened_tickets_dataset = [len(list) for list in backlog_stats['opened']]
        created_tickets_dataset = [len(list) for list in backlog_stats['created']]

        # need to add create and closed ticket for charting purpose. We want to show
        # closed tickets on top of opened ticket in bar chart.
        closed_tickets_dataset = []
        for i in range(len(created_tickets_dataset)):
            closed_tickets_dataset.append(created_tickets_dataset[i] + len(backlog_stats['closed'][i]))

        bmi_dataset = []
        for i in range(len(opened_tickets_dataset)):
            if opened_tickets_dataset[i] == 0:
                 bmi_dataset.append(0.0)
            else:
                bmi_dataset.append(float(closed_tickets_dataset[i]) * 100 / float(opened_tickets_dataset[i]))

#        for idx, numdate in enumerate(numdates):
#            self.env.log.info("%s: %s, %s, %s" % (num2date(numdate), 
#                                                    closed_tickets_dataset[idx],
#                                                    opened_tickets_dataset[idx],
#                                                    created_tickets_dataset[idx]))
        ds_daily_backlog = ''

        for idx, numdate in enumerate(numdates):
                    ds_daily_backlog = ds_daily_backlog + '{ date: "%s", opened: %d, closed: %d, created: %d}, ' \
                          % (format_date(num2date(numdate), tzinfo=utc), opened_tickets_dataset[idx], \
                             closed_tickets_dataset[idx], created_tickets_dataset[idx])

        return '[ ' + ds_daily_backlog + ' ];'

    def get_daily_backlog_chart(self, backlog_history):
        '''
        return data point based on Yahoo JSArray format
        '''

        dates = backlog_history[0]
        backlog_stats = backlog_history[1]

        # create counted list.
        opened_tickets_dataset  = [len(set(list)) for list in backlog_stats['opened']]
        created_tickets_dataset = [len(set(list)) for list in backlog_stats['created']]
        closed_tickets_dataset  = [len(set(list)) for list in backlog_stats['closed']]

#        bmi_dataset = []
#        for i in range(len(opened_tickets_dataset)):
#            if opened_tickets_dataset[i] == 0:
#                bmi_dataset.append(0.0)
#            else:
#                bmi_dataset.append(float(closed_tickets_dataset[i])*100/float(opened_tickets_dataset[i]))

        ds_daily_backlog = ''

        for idx, date_ in enumerate(dates):
                    ds_daily_backlog = ds_daily_backlog +  '{ date: "%s", opened: %d, closed: %d, created: %d}, ' \
                          % (format_date(date_, tzinfo=utc), opened_tickets_dataset[idx], \
                             closed_tickets_dataset[idx], created_tickets_dataset[idx])

        return '[ ' + ds_daily_backlog + ' ];'

    def _validate_add(self, req):
        ty = req.args.get('reminder_type')

        if ty == 'interval':
            try:
                req.args['interval'] = int(req.args.get('interval', '').strip())
                if req.args['interval'] <= 0:
                    add_warning(req, "Nonpositive interval value.")
                    return False
            except ValueError:
                add_warning(req, "Invalid or missing interval value.")
                return False

            if req.args.get('unit') not in ['day', 'week', 'month', 'year']:
                add_warning(req, "Please select interval unit.")
                return False

        elif ty == 'date':
            try:
                time = clear_time(parse_date(req.args.get('date', '').strip()))
                req.args['date'] = format_date(time)
                now = to_datetime(None)
                if time <= now:
                    add_warning(req, "Date value not in the future.")
                    return False
            except TracError:
                add_warning(req, "Invalid or missing date value.")
                return False

        else:
            add_warning(req, "Please select type.")
            return False

        return True

def _prep_session_table(env, spread_visits=False):
    """ Populate the session table with known values.

    :return: a tuple of lists `(auth_list, anon_list, all_list)`
    :since 1.0: changed `db` input parameter to `env`
    """
    with env.db_transaction as db:
        db("DELETE FROM session")
        db("DELETE FROM session_attribute")
    last_visit_base = time.mktime(datetime(2010, 1, 1).timetuple())
    visit_delta = 86400 if spread_visits else 0
    auth_list, anon_list = [], []
    with env.db_transaction as db:
        for x in xrange(20):
            sid = 'name%02d' % x
            authenticated = int(x < 10)
            last_visit = last_visit_base + (visit_delta * x)
            val = 'val%02d' % x
            data = (sid, authenticated,
                    format_date(to_datetime(last_visit), console_date_format),
                    val, val, None)
            if authenticated:
                auth_list.append(data)
            else:
                anon_list.append(data)
            db("INSERT INTO session VALUES (%s, %s, %s)",
               (sid, authenticated, last_visit))
            db("INSERT INTO session_attribute VALUES (%s, %s, 'name', %s)",
               (sid, authenticated, val))
            db("INSERT INTO session_attribute VALUES (%s, %s, 'email', %s)",
               (sid, authenticated, val))
    all_list = auth_list + anon_list
    return (auth_list, anon_list, all_list)

 def test_absolute(self):
     t = datetime.now(utc) - timedelta(days=1)
     label = "on %s at %s" % (
         format_date(t, locale=locale_en, tzinfo=utc),
         format_time(t, locale=locale_en, tzinfo=utc),
     )
     self.assertEqual(label, self._format_chrome(t, "absolute", False))
     self.assertEqual(label, self._format_timeline(t, "absolute", False))

 def formatter(self, col, cell_value):
     if col == 'time':
         return cell_value != '' and format_time(from_utimestamp(long(cell_value))) or '--'
     if col in ('date', 'created', 'modified'):
         return cell_value != '' and format_date(from_utimestamp(long(cell_value))) or '--'
     if col == 'datetime':
         return cell_value != '' and format_datetime(from_utimestamp(long(cell_value))) or '--'
     return cell_value

 def  test_session_add_sid_name(self):
     test_name = sys._getframe().f_code.co_name
     rv, output = self._execute('session add john John')
     self.assertEqual(0, rv)
     rv, output = self._execute('session list john')
     self.assertEqual(self.expected_results[test_name]
                      % {'today': format_date(None, console_date_format)},
                      output)

 def _format_developer_data(self, req, developer):
     render_time = lambda x: TimePropertyRenderer(self.env, x).render()
     if developer.load is not None:
         for load in developer.load:
             load.is_working_day = is_working_day(load.day)
             load.day = format_date(load.day, tzinfo=req.tz)
             load.remaining_time = render_time(load.remaining_time)
     total_capacity = getattr(developer, 'total_capacity', None)
     developer.total_capacity = render_time(total_capacity)

 def _do_list(self, *sids):
     if not sids:
         sids = ['*']
     print_table([(r[0], r[1], format_date(to_datetime(r[2]),
                                           console_date_format),
                   r[3], r[4])
                  for r in self._get_list(sids)],
                 [_('SID'), _('Auth'), _('Last Visit'), _('Name'),
                  _('Email')])

    def process_request(self, req):
        req.perm.assert_permission('TIMELINE_VIEW')

        format = req.args.get('format')
        maxrows = int(req.args.get('max', 0))

        # Parse the from date and adjust the timestamp to the last second of
        # the day
        t = time.localtime()
        if req.args.has_key('from'):
            try:
                t = time.strptime(req.args.get('from'), '%x')
            except:
                pass

        fromdate = time.mktime((t[0], t[1], t[2], 23, 59, 59, t[6], t[7], t[8]))
        try:
            daysback = max(0, int(req.args.get('daysback', '')))
        except ValueError:
            daysback = self.default_daysback
        req.hdf['timeline.from'] = format_date(fromdate)
        req.hdf['timeline.daysback'] = daysback

        available_filters = []
        for event_provider in self.event_providers:
            available_filters += event_provider.get_timeline_filters(req)

        filters = []
        # check the request or session for enabled filters, or use default
        for test in (lambda f: req.args.has_key(f[0]),
                     lambda f: req.session.get('timeline.filter.%s' % f[0], '')\
                               == '1',
                     lambda f: len(f) == 2 or f[2]):
            if filters:
                break
            filters = [f[0] for f in available_filters if test(f)]

        # save the results of submitting the timeline form to the session
        if req.args.has_key('update'):
            for filter in available_filters:
                key = 'timeline.filter.%s' % filter[0]
                if req.args.has_key(filter[0]):
                    req.session[key] = '1'
                elif req.session.has_key(key):
                    del req.session[key]

        stop = fromdate
        start = stop - (daysback + 1) * 86400

        events = []
        for event_provider in self.event_providers:
            try:
                events += event_provider.get_timeline_events(req, start, stop,
                                                             filters)
            except Exception, e: # cope with a failure of that provider
                self._provider_failure(e, req, event_provider, filters,
                                       [f[0] for f in available_filters])

 def test_with_iso8601(self):
     tz = datefmt.timezone('GMT +2:00')
     t = datetime.datetime(2010, 8, 28, 11, 45, 56, 123456, tz)
     self.assertEqual('2010-08-28',
                      datefmt.format_date(t, 'iso8601', tz, 'iso8601'))
     self.assertEqual('11:45:56+02:00',
                      datefmt.format_time(t, 'iso8601', tz, 'iso8601'))
     self.assertEqual('2010-08-28T11:45:56+02:00',
                      datefmt.format_datetime(t, 'iso8601', tz, 'iso8601'))