def closed_statuses_for_all_types(self):
"""Returns a dictionary where the keys are tickets types and the associated
values are statuses from workflow status groups where closed='True'.
Essentially if a ticket is in one of these statuses, we consider it closed
and from this infer that no more work is required to complete the ticket.
"""
controller = LogicaOrderController(self.env)
closed_statuses = controller.type_and_statuses_for_closed_statusgroups()
types_and_statuses = []
for type_, statuses in closed_statuses.iteritems():
types_and_statuses.append(type_)
types_and_statuses.extend(statuses)
return closed_statuses, types_and_statuses
def _get_ticket_data(self, req, results):
ats = AgileToolsSystem(self.env)
loc = LogicaOrderController(self.env)
closed_statuses = loc.type_and_statuses_for_closed_statusgroups()
# TODO calculate which statuses are closed using the query system
# when it is able to handle this
tickets = []
for result in results:
if result['status'] not in closed_statuses[result['type']]:
filtered_result = dict((k, v)
for k, v in result.iteritems()
if k in self.fields)
if "remaininghours" in filtered_result:
try:
hours = float(filtered_result["remaininghours"])
except (ValueError, TypeError):
hours = 0
del filtered_result["remaininghours"]
else:
hours = 0
if "effort" in filtered_result:
try:
storypoints = float(filtered_result['effort'])
except (ValueError, TypeError):
storypoints = 0
else:
storypoints = 0
reporter = filtered_result["reporter"]
session = DetachedSession(self.env, reporter)
filtered_result.update({
'id': result['id'],
'position': ats.position(result['id']),
'hours': hours,
'effort': storypoints,
'reporter': session.get('name', reporter),
'changetime': to_utimestamp(filtered_result['changetime'])
})
tickets.append(filtered_result)
return tickets
def _capture(db):
def calculate_initial_values_for_ticket(ticket_id):
# first seen changes will be from the very first information we have about this ticket
initial_values_cursor = db.cursor()
initial_values_cursor.execute("SELECT time FROM ticket WHERE id = %s", (ticket_id,))
ticket_created = from_utimestamp(initial_values_cursor.fetchone()[0])
history_date = ticket_created.date()
# find original values for the ticket
for column in ticket_values.keys():
initial_values_cursor.execute("SELECT oldvalue FROM ticket_change WHERE ticket = %s AND field = %s ORDER BY time LIMIT 1",
(ticket_id, column))
result = initial_values_cursor.fetchone()
if result is None:
if column in built_in_fields:
initial_values_cursor.execute("SELECT %s FROM ticket WHERE id = %%s" % column, (ticket_id,))
result = initial_values_cursor.fetchone()
ticket_values[column] = encode_and_escape(result[0])
else:
initial_values_cursor.execute("SELECT value FROM ticket_custom WHERE ticket = %s AND name = %s", (ticket_id, column))
result = initial_values_cursor.fetchone()
if result:
ticket_values[column] = encode_and_escape(result[0])
else:
ticket_values[column] = r'\N'
else:
ticket_values[column] = encode_and_escape(result[0])
ticket_values['id'] = str(ticket_id)
ticket_values['time'] = str(ticket_created)
ticket_values['changetime'] = str(ticket_created)
ticket_values['_resolutiontime'] = r'\N'
# assumption that you cannot create a ticket in status closed
# so we give isclosed a false value from the off
ticket_values['isclosed'] = "0"
# PROBLEM: How can we detect when a milestone was renamed (and
# tickets updated) - this isn't mentioned in the ticket_change
# table.
# Maybe we have to search the log file for strings?!
# source:trunk/trac/trac/ticket/model.py@8937#L1192
return ticket_values, ticket_created, history_date
last_snapshot_cursor = db.cursor()
last_snapshot_cursor.execute("SELECT _snapshottime FROM ticket_bi_historical ORDER BY _snapshottime DESC LIMIT 1")
last_snapshot_result = last_snapshot_cursor.fetchone()
del last_snapshot_cursor
if last_snapshot_result:
last_snapshot = last_snapshot_result[0]
print "Last successful run was at %s" % last_snapshot
if until <= last_snapshot:
print "Already have data for %s, so can't run with until=%s" % (last_snapshot, until)
return False
else:
last_snapshot = None
print "No previous runs"
# Get statuses we consider to be closed for each ticket type
controller = LogicaOrderController(self.env)
closed_statuses = controller.type_and_statuses_for_closed_statusgroups()
if only_ticket:
ticket_ids = [(int(only_ticket),)]
else:
ticket_ids_c = db.cursor()
ticket_ids_c.execute("SELECT id FROM ticket ORDER BY id")
ticket_ids = ticket_ids_c.fetchall()
insert_cursor = db.cursor()
copy_data_buffer = StringIO()
eta_prediction__start_time = datetime.datetime.now()
eta_prediction__done = 0
eta_prediction__total = len(ticket_ids)
for ticket_id, in ticket_ids:
try:
eta_prediction = eta_prediction__start_time + ((datetime.datetime.now() - eta_prediction__start_time) / eta_prediction__done) * eta_prediction__total
except ZeroDivisionError:
eta_prediction = None
self.log.info("Working on (after) %s to (end of) %s for ticket %d/%d - ETA %s",
last_snapshot,
until,
ticket_id,
eta_prediction__total,
eta_prediction)
eta_prediction__done += 1
# set up a dictionary to hold the value of the ticket fields, which will change as we step forward in time
ticket_values = proto_values.copy()
# populate the "initial" values
if last_snapshot:
history_date = last_snapshot + datetime.timedelta(days=1)
last_snapshot_cursor = db.cursor()
# we add ticket fields and history columns otherwise
# we don't get previous values such as isclosed
columns = ticket_values.keys() + history_columns
last_snapshot_cursor.execute("SELECT %s FROM ticket_bi_historical "
"WHERE id = %%s AND _snapshottime = %%s "
"LIMIT 1 "
% ",".join(columns),
(ticket_id, last_snapshot))
values = last_snapshot_cursor.fetchone()
if not values:
self.log.warn("No historical data for ticket %s on %s?", ticket_id, last_snapshot)
ticket_values, ticket_created, history_date = calculate_initial_values_for_ticket(ticket_id)
else:
ticket_values.update(dict(zip(columns, values)))
# original storage for custom_fields can only store strings, so pretend we had a string
for k, v in ticket_values.items():
# everything wants to be a string as we use COPY to insert to the database
if k in custom_fields and not v:
ticket_values[k] = ''
else:
ticket_values[k] = encode_and_escape(v)
ticket_values['id'] = str(ticket_id)
else:
# first time we've run the history capture script
ticket_values, ticket_created, history_date = calculate_initial_values_for_ticket(ticket_id)
# now we're going to get a list of all the changes that this ticket goes through
ticket_changes_cursor = db.cursor()
ticket_changes_cursor.execute("SELECT time, field, newvalue "
"FROM ticket_change "
"WHERE ticket = %%s AND field in (%s) "
"AND time >= %%s AND time < %%s "
"ORDER BY time "
% db.parammarks(len(ticket_values)),
[ticket_id]
+ ticket_values.keys()
+ [memoized_to_utimestamp(startofday(history_date)),
memoized_to_utimestamp(startofnextday(until)),
]
)
ticket_changes =[(from_utimestamp(time), field, newvalue)
for time, field, newvalue in ticket_changes_cursor]
# and then we'll update 'ticket_values' to make a representation of the ticket for the end of each day, and store that into the history database
def _calculate_totalhours_on_date(date, cursor):
cursor.execute("SELECT SUM(seconds_worked)/3600.0 FROM ticket_time WHERE ticket = %s AND time_started < %s",
(ticket_values['id'],
memoized_to_timestamp(startofnextday(date))))
result = cursor.fetchone()
return result[0] if result else 0
def _calculate_remaininghours_on_date(date, cursor):
# find the closest absolute value
nextdate = startofnextday(date)
cursor.execute("SELECT to_timestamp(time / 1000000), oldvalue FROM ticket_change WHERE "
"field = 'remaininghours' AND ticket = %s AND time >= %s ORDER BY time ASC LIMIT 1",
(ticket_values['id'],
memoized_to_utimestamp(nextdate)))
next_known = cursor.fetchone()
cursor.execute("SELECT to_timestamp(time / 1000000), newvalue FROM ticket_change WHERE "
"field = 'remaininghours' AND ticket = %s AND time < %s ORDER BY time DESC LIMIT 1",
(ticket_values['id'],
memoized_to_utimestamp(nextdate)))
previous_known = cursor.fetchone()
cursor.execute("SELECT now(), value FROM ticket_custom WHERE ticket = %s AND name = 'remaininghours'",
(ticket_values['id'],))
currently = cursor.fetchone()
self.log.debug("Finding remaininghours for end of %s", date)
self.log.debug("Previous known value: %s", previous_known)
self.log.debug("Current known value: %s", currently)
self.log.debug("Next known value: %s", next_known)
candidates = []
try:
candidates.append((currently[0] - nextdate, currently[0], float(currently[1])))
except (TypeError, ValueError), e:
self.log.warning("Invalid float in %s for remaininghours on ticket %s",
currently, ticket_values['id'])
if next_known:
try:
candidates.append((next_known[0] - nextdate, next_known[0], float(next_known[1])))
except (TypeError, ValueError), e:
self.log.warning("Invalid float for next_known in %s for remaininghours on ticket %s",
next_known, ticket_values['id'])
if previous_known:
try:
candidates.append((nextdate - previous_known[0], previous_known[0], float(previous_known[1])))
except (TypeError, ValueError), e:
self.log.warning("Invalid float for previous_known in %s for remaininghours on ticket %s",
previous_known, ticket_values['id'])
