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 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 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 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 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)