def get_date_closing(issue):
""":return date of cloising of current issue"""
last_issue = OLAP.select(OLAP.issue_id, fn.Min(OLAP.updated)).where((OLAP.issue_id == issue.issue_id) &
(OLAP.state == 'closed')).get()
return '' if last_issue.updated is None else datetime.datetime.fromtimestamp(
last_issue.updated).strftime(
'%d-%m-%Y %H:%M')
def __init__(self, issue):
# todo see down
# updating created time for issue in db, remove it after updating
q = OLAP.update(created=self._convert_time(issue.created_at)).where(OLAP.issue_id == issue.id)
q.execute()
self.project_id = str(issue.project_id)
self.issue_id = str(issue.id)
self.title = xstr(getattr(issue, 'title', ''))
self.description = xstr(getattr(issue, 'description', ''))
self.assignee = xstr(getattr(issue.assignee, 'name', ''))
self.author = xstr(getattr(issue.author, 'name', ''))
self._convert_time(getattr(issue, 'created_at', ''))
self.created = self._convert_time(getattr(issue, 'created_at', ''))
self.updated = self._convert_time(getattr(issue, 'updated_at', ''))
self.due_date = self._convert_time(getattr(issue, 'due_date', ''))
self.labels = json.dumps(getattr(issue, 'labels', ''))
self.state = getattr(issue, 'state', '')
self.url = getattr(issue, 'web_url', '')
self.milestone_title = xstr(getattr(issue.milestone, 'title', ''))
self.milestone_description = xstr(getattr(issue.milestone, 'description', ''))
self.milestone_due_date = self._convert_time(xstr(getattr(issue.milestone, 'due_date', '')))
time_stats = issue.time_stats()
self.time_estimate = xstr(time_stats['human_time_estimate'])
self.time_spent = xstr(time_stats['human_total_time_spent'])
self.time_estimate_secs = time_stats['time_estimate']
self.time_spent_secs = time_stats['total_time_spent']
# print(time_stats, self.time_estimate, self.time_spent, self.time_estimate_secs, self.time_spent_secs)
self.project = xstr(getattr(issue, 'project', ''))
if xstr(getattr(issue.milestone, 'created_at', '')):
self.milestone_created = self._convert_time(xstr(getattr(issue.milestone, 'created_at', '')))
else:
self.milestone_created = ''
def calc_times(issues):
"""for assignees in db calculate estimate and spent time and add this on db localbase.db(sqlite3)"""
# Assignees DB
# format: assignees = {'assignee email': {'time_estimate': secs, 'time_spent': secs}}
up_time = int(datetime.datetime.now().timestamp())
for issue in issues:
key = issue.assignee
if key is not '':
if key in assignees:
# assignees[key] = {'time_estimate': issue.time_estimate_secs + te, 'time_spent': issue.time_spent_secs + ts}
entry, new = OLAP.get_or_create(issue_id=issue.issue_id)
fill_field(issue, entry, new, up_time)
else:
assignees[key] = {
'time_estimate': issue.time_estimate_secs,
'time_spent': issue.time_spent_secs
}
entry, new = OLAP.get_or_create(issue_id=issue.issue_id)
fill_field(issue, entry, new, up_time)
def calc_times(issues):
"""for assignees in db calculate estimate and spent time and add this on db localbase.db(sqlite3)"""
# Assignees DB
# format: assignees = {'assignee email': {'time_estimate': secs, 'time_spent': secs}}
up_time = int(datetime.datetime.now().timestamp()) - 18000
for issue in issues:
key = issue.assignee
if key is not '':
if key in assignees:
# assignees[key] = {'time_estimate': issue.time_estimate_secs + te, 'time_spent': issue.time_spent_secs + ts}
entry, new = OLAP.get_or_create(issue_id=issue.issue_id)
fill_field(issue, entry, new, up_time)
else:
try:
name, tg = map(lambda x: x.strip(), key.split('@'))
except ValueError:
name = key.strip()
assignees[key] = {'name': name}
entry, new = OLAP.get_or_create(issue_id=issue.issue_id)
fill_field(issue, entry, new, up_time)
def write_to_xls(self):
# connect
from unotools.unohelper import convert_path_to_url
context = unotools.connect(unotools.Socket(host=sohost, port=soport))
calc = Calc(context)
filled_issue = 0
# create tables of assignees
for assignee in assignees:
if assignee:
# get name of assignee
try:
name, _ = map(lambda x: x.strip(), assignee.split('@'))
except ValueError:
name = assignee.strip()
calc.insert_sheets_new_by_name(name, 0) # returns None
sheet = calc.get_sheet_by_name(name)
self.fill_header(sheet, name)
w4_end = self.get_day(datetime.datetime.now() - datetime.timedelta(days=1), 6)
w4_start = self.get_day(datetime.datetime.now()- datetime.timedelta(days=1), 0)
weeks = []
weeks_timestamp = []
this_week_num = w4_end.isocalendar()[1]
before_date = None
for week in reversed(range(1, 4)):
days_range = self.get_range_days_of_week(this_week_num - week, 0, 6)
weeks_timestamp.append(days_range)
weeks.append(days_range[0].strftime(self.date_format) + ' - ' + days_range[1].strftime(self.date_format))
if before_date is None:
before_date = self.get_range_days_of_week(this_week_num-week-1, 0, 6)[1]
weeks.append(str(w4_start.strftime(self.date_format)) + ' - ' + str(w4_end.strftime(self.date_format)))
# filling before
before = sheet.get_cell_range_by_position(4, 1, 5, 1)
before.setDataArray((('< ' + before_date.strftime(self.date_format), ''), ))
before.merge(True)
align = before.getPropertyValue('HoriJustify')
align.value = 'CENTER'
before.setPropertyValue('HoriJustify', align)
for w in weeks:
cell = sheet.get_cell_by_position(6 + weeks.index(w) * 2, 1)
cell.setString(w)
cells = sheet.get_cell_range_by_position(6 + weeks.index(w) * 2, 1, 7 + weeks.index(w) * 2, 1)
cells.merge(True)
align = cell.getPropertyValue('HoriJustify') # property of align 'VertJustify' had too
align.value = 'CENTER'
cells.setPropertyValue('HoriJustify', align)
sheet.get_cell_by_position(6 + weeks.index(w) * 2, 2).setString("План")
sheet.get_cell_by_position(7 + weeks.index(w) * 2, 2).setString("Факт")
logging.info('filling report of: ' + assignee)
# make headers:
# total ts and te of assignee
lines = 4 # start count from 1 row [in GUI 2]
ts, te = 0, 0
for issue in issues:
if issue.assignee == assignee:
report_issue = ReportIssue(issue, before_date, weeks_timestamp)
report_issue.generate_report()
iss = OLAP.select().where(OLAP.issue_id == issue.issue_id).get()
line = sheet.get_cell_range_by_position(0, lines, 3, lines)
iid = issue.url.split('/')[-1]
date_closing = self.get_date_closing(issue)
if date_closing != '':
row = sheet.get_cell_range_by_position(0, lines, 13, lines)
row.setPropertyValue('CellBackColor', 0xdedede)
row.setPropertyValues(self.keys, self.border_lines)
line.setDataArray(((iss.project_name,
"#" + iid + ' ' + iss.issue_title,
str(datetime.datetime.fromtimestamp(issue.created + 18000).strftime(
'%d-%m-%Y %H:%M')),
date_closing,),))
line = sheet.get_cell_range_by_position(4, lines, 13, lines)
t = report_issue.generate_report()
line.setDataArray(((t),))
hyperlink_issue = issue.url
title = sheet.get_cell_by_position(1, lines)
title.Text.Hyperlink = hyperlink_issue
title.Text.CharUnderline = UNDERLINE_SINGLE
title.Text.CharColor = 0x0000aa
title.Rows.Height = 600
ts += iss.time_spent
te += iss.time_estimate
filled_issue += 1
lines += 1
del issue
# sheet.get_cell_by_position(self.estimate_column, 3).setString(seconds_to_time(te) + ' h')
# sheet.get_cell_by_position(self.spend_column, 3).setString(seconds_to_time(ts) + ' h')
sheet.get_cell_range_by_position(1, 0, 1, 0).Columns.Width = 6000
sheet.get_cell_range_by_position(4, 0, 5, 2).Columns.OptimalWidth = True
calc.remove_sheets_by_name('Sheet1')
calc.insert_sheets_new_by_name('Список', 0)
assignee_sheet = calc.get_sheet_by_index(0)
assignee_list = []
for assignee in assignees:
try:
name, _ = map(lambda x: x.strip(), assignee.split('@'))
except ValueError:
name = assignee.strip()
assignee_list.append(name)
assignee_list = sorted(assignee_list)
for assignee in assignee_list:
cell = assignee_sheet.get_cell_by_position(0, assignee_list.index(assignee))
cell.setString(assignee)
cell.Text.Hyperlink = '#{0}'.format(assignee)
# here filling hyperlinks of assignee
logging.info(str(filled_issue) + ' issues from ' + str(len(issues)) + ' have assignee')
# issues_sheet[1:10, 5].border_right_width = 1
# saving
logging.info('saving report into ./Dock/reports/{0}.ods'.format(datetime.date.today()))
url = convert_path_to_url(xls_file.format(datetime.date.today()))
calc.store_to_url(url, 'FilterName', 'writer8')
calc.close(True)
def generate_report(self):
"""preparing report for current issue"""
OLAP_data = OLAP.select().where(OLAP.issue_id == self.issue.issue_id)
created = self.issue.created
# old issues
issue_data = []
closed_day = OLAP.select(OLAP.issue_id,
fn.Min(OLAP.updated),
OLAP.time_estimate,
OLAP.time_spent).where((OLAP.issue_id == self.issue.issue_id) &
(OLAP.state == 'closed')).get()
range_template = namedtuple('dates_range', ['begin', 'end'])
issue_range = range_template(self.issue.created, closed_day.updated)
if closed_day.updated is not None:
# here working
if closed_day.updated < self.before.timestamp():
return (seconds_to_time(closed_day.time_estimate) + ' h'
, seconds_to_time(closed_day.time_spent) + ' h',) + ('',)*8
else:
# todo here start problems
report = ['', '']
previous_spent = 0
_ranges = range_template(ReportCalc.get_first_sec(self.ranges[0][0]),
ReportCalc.get_last_sec(self.ranges[len(self.ranges) - 1][1]))
for w in self.ranges:
current_issue_OLAP = OLAP.select(fn.Max(OLAP.updated),
OLAP.time_estimate,
OLAP.time_spent).where(
(OLAP.updated < ReportCalc.get_last_sec(w[1])) &
(OLAP.issue_id == self.issue.issue_id)).get()
# todo check this
if (self.issue.created > ReportCalc.get_first_sec(w[0])) and (closed_day.updated < ReportCalc.get_last_sec(w[1])):
report += [seconds_to_time(closed_day.time_estimate) + ' h',
seconds_to_time(closed_day.time_spent) + ' h']
elif self.issue.created > ReportCalc.get_first_sec(w[0]):
if previous_spent == 0:
if current_issue_OLAP.time_spent:
previous_spent = current_issue_OLAP.time_spent
report.append(seconds_to_time(previous_spent) + ' h')
report.append(seconds_to_time(current_issue_OLAP.time_spent) + ' h')
else:
# 1
report += ['', '']
# report.append(seconds_to_time(previous_spent) + ' h')
# report.append(seconds_to_time(self.issue.time_spent_secs) + ' h')
else:
# 2
report += ['2', '2']
else:
if issue_range.begin < _ranges.begin:
if issue_range.end < datetime.datetime.timestamp(w[1]):
report += [seconds_to_time(current_issue_OLAP.time_estimate) + ' h',
seconds_to_time(current_issue_OLAP.time_spent) + ' h']
# todo check this fields
else:
# 3
if report[0] == '' and report[1] == '':
report[0] = seconds_to_time(current_issue_OLAP.time_estimate) + ' h'
report[1] = seconds_to_time(current_issue_OLAP.time_spent) + ' h'
report += [seconds_to_time(current_issue_OLAP.time_estimate) + ' h',
seconds_to_time(current_issue_OLAP.time_spent) + ' h']
else:
report += [seconds_to_time(current_issue_OLAP.time_estimate) + ' h',
seconds_to_time(current_issue_OLAP.time_spent) + ' h']
if self.issue.created > _ranges.end:
report.append(seconds_to_time(closed_day.time_estimate) + ' h')
report.append(seconds_to_time(closed_day.time_spent) + ' h')
else:
# 4
report += ['', '']
# report.append('')
# report.append('')
return tuple(report)
else:
# todo make report on non complete issue
pass
return ('',)*10
def fill_field(issue, entry, new: bool, up_time: int):
"""
fill database field received issue
assignee, state, time estimate, time spent, updated, query time
"""
# todo refactor this trash
if new:
# these fields for
# if enrty issue is new, fill fields of this issue
entry.assignee = issue.assignee
entry.state = issue.state
entry.created = issue.created
entry.issue_title = issue.title
entry.time_estimate = issue.time_estimate_secs
entry.time_spent = issue.time_spent_secs
entry.updated = issue.updated
entry.query_time = up_time
entry.project_name = issue.project
entry.project_id = issue.project_id
entry.milestone_date = issue.milestone_due_date
entry.milestone_title = issue.milestone_title
entry.save()
else:
# if id of this issue was in table
iss, new = OLAP.get_or_create(updated=issue.updated,
issue_id=issue.issue_id)
if not new:
_iss, new = OLAP.get_or_create(updated=issue.updated,
time_spent=issue.time_spent_secs,
issue_id=issue.issue_id)
if new:
_iss.issue_id = issue.issue_id
_iss.issue_title = issue.title
_iss.assignee = issue.assignee
_iss.state = issue.state
_iss.created = issue.created
_iss.time_estimate = issue.time_estimate_secs
_iss.time_spent = issue.time_spent_secs
_iss.updated = issue.updated
_iss.query_time = up_time
_iss.project_id = issue.project_id
_iss.project_name = issue.project
_iss.milestone_date = issue.milestone_due_date
_iss.milestone_title = issue.milestone_title
_iss.save()
else:
iss.issue_id = issue.issue_id
iss.issue_title = issue.title
iss.assignee = issue.assignee
iss.state = issue.state
iss.created = issue.created
iss.time_estimate = issue.time_estimate_secs
iss.time_spent = issue.time_spent_secs
iss.updated = issue.updated
iss.project_id = issue.project_id
iss.project_name = issue.project
iss.query_time = up_time
iss.milestone_date = issue.milestone_due_date
iss.milestone_title = issue.milestone_title
iss.save()
def write_to_xls():
# connect
"""
from unotools.unohelper import convert_path_to_url
context = unotools.connect(unotools.Socket(host=sohost, port=soport))
calc = Calc(context, convert_path_to_url(xls_file))
# sheets_count = calc.get_sheets_count()
# create tables of assignees
for assignee in assignees:
try:
name, _ = map(lambda x: x.strip(), assignee.split('@'))
except ValueError:
name = assignee.strip()
try:
sheet = calc.get_sheet_by_name(name)
except:
calc.insert_sheets_new_by_name(name, 0)
sheet = calc.get_sheet_by_name(name)
name_cell = sheet.get_cell_by_position(0,0)
name_cell.setString(name)
total_cell = sheet.get_cell_by_position(0,3)
total_cell.setString('Итого: ')
sheet.get_cell_by_position(1,3).setString('Задача')
sheet.get_cell_by_position(2,3).setString('Проект')
sheet.get_cell_by_position(3, 3).setString('opened')
sheet.get_cell_by_position(4, 3).setString('closed')
sheet.get_cell_by_position(5, 3).setString('Статус, час')
bcol = sheet.get_cell_range_by_position(6,4,6,100)
issues_of_assignee = OLAP.select().distinct().where(OLAP.assignee==assignee)
ioa = tuple(ioa.issue_title for ioa in issues_of_assignee)
print(assignee, len(ioa))
last_cell = 4
issues_in_table = []
while sheet.get_cell_by_position(0,last_cell):
if sheet.get_cell_by_position(0,last_cell).getString() != '':
if sheet.get_cell_by_position(0,last_cell).getString() in ioa:
pass
# update issue info
else:
pass
# add this issue
# for ioa in issues_of_assignee:
# if sheet.get_cell_by_position(0, last_cell).getString() == ioa.issue_title:
# pass
# todo make updating info
issues_in_table.append(sheet.get_cell_by_position(0,last_cell).getString())
last_cell += 1
else:
break
# todo here creation list of issues
pass
# todo updating state and date of issue
# todo updating dates of issues
"""
desktop = pyoo.Desktop(sohost, soport)
doc = desktop.create_spreadsheet()
for assignee in assignees:
try:
name, _ = map(lambda x: x.strip(), assignee.split('@'))
except ValueError:
name = assignee.strip()
doc.sheets.create(name, index=0)
issues_sheet = doc.sheets[name]
try:
del doc.sheets['Sheet1']
except:
pass
# set cell 0.0 to name os assignee
# issues_sheet[row, column]
issues_sheet[0, 0].value = name
# fill service data
issues_sheet[1, 0].value = 'Проект'
issues_sheet[1, 1].value = 'Задача'
issues_sheet[1, 2].value = 'opened'
issues_sheet[1, 3].value = 'closed'
issues_sheet[1, 4].value = 'План'
issues_sheet[1, 5].value = 'Факт'
start_issues = 2
for issue in issues:
if issue.assignee == assignee:
iss = OLAP.select().where(
OLAP.issue_id == issue.issue_id).get()
issues_sheet[start_issues, 1].value = iss.issue_title
issues_sheet[start_issues, 0].value = iss.project_name
issues_sheet[start_issues, 2].value = str(
datetime.datetime.fromtimestamp(issue.created))
issues_sheet[start_issues, 2].inner_border_width = 50
date_closing = get_date_closing(issue)
if date_closing != '':
issues_sheet[start_issues,
0:10].background_color = 0xdedede
issues_sheet[start_issues, 3].value = str(date_closing)
issues_sheet[start_issues, 4].value = seconds_to_time(
iss.time_estimate) + ' h'
issues_sheet[start_issues,
5].value = seconds_to_time(iss.time_spent) + ' h'
# todo get color of marks
start_issues += 1
def get_date_closing(issue):
last_issue = OLAP.select(OLAP.issue_id, fn.Min(
OLAP.updated)).where((OLAP.issue_id == issue.issue_id)
& (OLAP.state == 'closed')).get()
return '' if last_issue.updated is None else datetime.datetime.fromtimestamp(
last_issue.updated)