def fill_worksheet_with_month_days_specific_data(self, worksheet, formats_dict, timesheet_last_month_day,
issues_count, time_summary_row_index, man_days_row_index,
man_days_hours):
for day in range(0, timesheet_last_month_day):
month_day_column_index = day + self._first_month_day_column_index
month_day_cell = WorksheetCell(self._day_row_index, month_day_column_index, day + 1,
formats_dict['table_header_format'])
self.write_to_cell(worksheet, month_day_cell)
total_worklog_time_spent_for_day_formula_range = \
WorksheetCellsRange(self._first_data_row_index, month_day_column_index,
self._first_data_row_index + issues_count - 1, month_day_column_index, None, None)
total_worklog_time_spent_for_day_formula_cell = WorksheetCell(time_summary_row_index,
month_day_column_index, None, None)
self.write_sum_formula(worksheet, total_worklog_time_spent_for_day_formula_range,
total_worklog_time_spent_for_day_formula_cell, formats_dict['table_header_format'])
if man_days_row_index is not None:
man_days_counting_formula_range = \
WorksheetCellsRange(time_summary_row_index, month_day_column_index, time_summary_row_index,
month_day_column_index, None, None)
man_days_counting_formula_cell = \
WorksheetCell(man_days_row_index, month_day_column_index, None, None)
self.write_man_days_counting_formula(worksheet, man_days_counting_formula_range,
man_days_counting_formula_cell, man_days_hours,
formats_dict['table_row_summary_section_format'])
def fill_totals_counting_columns(self, worksheet, formats_dict, timesheet_last_month_day,
include_total_per_task_section=True):
if include_total_per_task_section:
summary_columns_range = \
WorksheetCellsRange(self._year_row_index, timesheet_last_month_day +
self._first_month_day_column_index,
self._day_row_index, timesheet_last_month_day +
self._first_month_day_column_index,
self._summary_column_text, formats_dict['centered_and_bold_format'])
self.merge_range(worksheet, summary_columns_range)
else:
summary_per_person_columns_range = \
WorksheetCellsRange(self._year_row_index, timesheet_last_month_day +
self._first_month_day_column_index,
self._day_row_index, timesheet_last_month_day +
self._first_month_day_column_index,
self._summary_per_person_column_text, formats_dict['centered_and_bold_format'])
self.merge_range(worksheet, summary_per_person_columns_range)
summary_man_days_columns_range = \
WorksheetCellsRange(self._year_row_index, timesheet_last_month_day +
self._first_month_day_column_index + 1,
self._day_row_index, timesheet_last_month_day +
self._first_month_day_column_index + 1,
self._summary_man_days_column_text, formats_dict['centered_and_bold_format'])
self.merge_range(worksheet, summary_man_days_columns_range)
def fill_man_days_summary_section(self, worksheet, formats_dict, man_days_row_index, timesheet_last_month_day,
last_table_basic_column_header_index):
total_man_days_per_month_formula_range = \
WorksheetCellsRange(man_days_row_index, self._first_month_day_column_index, man_days_row_index,
timesheet_last_month_day + self._first_month_day_column_index - 1, None, None)
total_man_days_per_month_formula_cell = WorksheetCell(man_days_row_index,
timesheet_last_month_day + self._first_month_day_column_index, None, None)
self.write_sum_formula(worksheet, total_man_days_per_month_formula_range,
total_man_days_per_month_formula_cell, formats_dict['table_header_format'])
summary_man_days_cells_range = \
WorksheetCellsRange(man_days_row_index, self._number_data_column_index,
man_days_row_index, last_table_basic_column_header_index, self._summary_row_man_days_text,
formats_dict['table_row_summary_section_aligned_right_format'])
self.merge_range(worksheet, summary_man_days_cells_range)
def fill_timesheet_date_data(self, worksheet, formats_dict, timesheet_month, timesheet_year,
timesheet_last_month_day):
timesheet_month_name = self._date.get_month_name(timesheet_month)
month_cells_range = \
WorksheetCellsRange(self._month_name_row_index, self._first_month_day_column_index,
self._month_name_row_index, timesheet_last_month_day +
self._first_month_day_column_index - 1, timesheet_month_name,
formats_dict['centered_and_bold_format'])
self.merge_range(worksheet, month_cells_range)
year_cells_range = \
WorksheetCellsRange(self._year_row_index, self._first_month_day_column_index,
self._year_row_index, timesheet_last_month_day +
self._first_month_day_column_index - 1, timesheet_year,
formats_dict['centered_and_bold_format'])
self.merge_range(worksheet, year_cells_range)
def fill_total_worklog_summary_section(self, worksheet, formats_dict, time_summary_row_index,
timesheet_last_month_day, last_table_basic_column_header_index,
include_total_per_task_section):
summary_total_per_day_cells_range = \
WorksheetCellsRange(time_summary_row_index, self._number_data_column_index,
time_summary_row_index, last_table_basic_column_header_index,
self._summary_row_total_per_day_text,
formats_dict['table_row_summary_section_aligned_right_format'])
self.merge_range(worksheet, summary_total_per_day_cells_range)
if include_total_per_task_section:
total_worklog_per_month_formula_range = \
WorksheetCellsRange(time_summary_row_index, self._first_month_day_column_index,
time_summary_row_index, timesheet_last_month_day +
self._first_month_day_column_index - 1, None, None)
total_worklog_per_month_formula_cell = WorksheetCell(time_summary_row_index,
timesheet_last_month_day + self._first_month_day_column_index, None, None)
self.write_sum_formula(worksheet, total_worklog_per_month_formula_range,
total_worklog_per_month_formula_cell,
formats_dict['table_row_summary_section_aligned_right_format'])
def fill_user_worklog_data(self, worksheet, formats_dict,
employee_worklog_data,
first_month_day_column_index, issue_key,
timesheet_year, timesheet_month, issue_row):
timesheet_last_month_day = self._date.get_month_days_count(
timesheet_year, timesheet_month)
for worklog in employee_worklog_data[issue_key]:
for day in range(1, timesheet_last_month_day + 1):
worklog_year = worklog['worklog_date']['year']
worklog_month = worklog['worklog_date']['month']
if int(worklog_year) == timesheet_year and int(
worklog_month) == timesheet_month:
worklog_day = int(worklog['worklog_date']['day'])
is_day_of_weekend = self._date.is_day_of_weekend(
timesheet_year, timesheet_month, day)
if is_day_of_weekend:
cell_format = formats_dict['weekend_day_cell_format']
else:
cell_format = None
if worklog_day == day:
time_spent_cell = WorksheetCell(
issue_row,
worklog_day + first_month_day_column_index - 1,
worklog['time_spent'], cell_format)
self.write_to_cell(worksheet, time_spent_cell)
else:
blank_time_spent_cell = WorksheetCell(
issue_row, day + first_month_day_column_index - 1,
'', cell_format)
self.write_to_blank_cell(worksheet,
blank_time_spent_cell)
total_time_per_task_formula_range = \
WorksheetCellsRange(issue_row, first_month_day_column_index, issue_row,
timesheet_last_month_day + first_month_day_column_index - 1, None, None)
total_time_per_task_formula_cell = WorksheetCell(
issue_row, timesheet_last_month_day + first_month_day_column_index,
None, None)
self.write_sum_formula(worksheet, total_time_per_task_formula_range,
total_time_per_task_formula_cell, None)
def prepare_summary_worksheet(self, summary_worksheet, teams_list, timesheet_date, national_holidays_days_list,
po_number, man_days_costs):
timesheet_month = timesheet_date['month']
timesheet_year = self._date.get_timesheet_year(timesheet_date['year'])
days_in_month = self._date.get_month_days_count(timesheet_year, timesheet_month)
teams_and_employees_count = len(teams_list)
for team in teams_list:
teams_and_employees_count += team.get_employees_list_count()
self.fill_summary_worksheet_with_template(summary_worksheet, self._formats_dict, timesheet_year,
timesheet_month, teams_and_employees_count, po_number, man_days_costs)
row_index = 0
for team in teams_list:
team_name = team.get_team_name()
team_row_index = self._first_data_row_index + row_index
team_name_cells_range = \
WorksheetCellsRange(team_row_index, self._name_data_column_index,
team_row_index, self._first_month_day_column_index + days_in_month + 1,
team_name, self._formats_dict['team_line_separator_format'])
self.merge_range(summary_worksheet, team_name_cells_range)
row_index += 1
for employee in team.get_employees_list():
employee_name = employee.get_employee_name()
employee_level = employee.get_employee_configuration_level()
employee_issues_count = employee.get_employee_timesheet_issues_count()
employee_row_index = self._first_data_row_index + row_index
employee_holidays_and_sick_leaves_list = \
employee.get_employee_configuration_holidays_and_sick_leaves_days_list()
employee_trainings_and_other_projects_list = \
employee.get_employee_configuration_trainings_and_other_projects_days_list()
employee_name_lenght = len(employee_name)
if employee_name_lenght > self._name_column_width:
self._name_column_width = employee_name_lenght
employee_name_cell = WorksheetCell(employee_row_index, self._name_data_column_index,
employee_name, None)
self.write_to_cell(summary_worksheet, employee_name_cell)
employee_level_cell = WorksheetCell(employee_row_index, self._level_data_column_index,
employee_level, self._formats_dict['employee_level_text_format'])
self.write_to_cell(summary_worksheet, employee_level_cell)
for day in range(1, days_in_month + 1):
is_day_of_weekend = self._date.is_day_of_weekend(timesheet_year, timesheet_month, day)
if is_day_of_weekend:
cell_format = self._formats_dict['weekend_day_cell_format']
elif len(national_holidays_days_list) > 0 and str(day) in national_holidays_days_list:
cell_format = self._formats_dict['national_holidays_color_format']
elif len(employee_holidays_and_sick_leaves_list) > 0 \
and str(day) in employee_holidays_and_sick_leaves_list:
cell_format = self._formats_dict['holidays_and_sick_leaves_color_format']
elif len(employee_trainings_and_other_projects_list) > 0 \
and str(day) in employee_trainings_and_other_projects_list:
cell_format = self._formats_dict['trainings_and_others_color_format']
else:
cell_format = None
employee_total_hours_row_index = self._first_data_row_index + employee_issues_count
employee_total_man_days_row_index = employee_total_hours_row_index + 1
employee_total_hours_per_day_formula_first_cell = \
WorksheetCell(employee_row_index, self._name_data_column_index, None, None)
employee_total_hours_per_day_formula_second_cell = \
WorksheetCell(employee_total_hours_row_index, self._first_month_day_column_index + day,
None, None)
employee_total_hours_per_day_formula_cell = \
WorksheetCell(employee_row_index, self._employee_logged_time_per_day_first_data_column_index +
day - 1, None, None)
self.write_indirect_formula(summary_worksheet, employee_total_hours_per_day_formula_first_cell,
employee_total_hours_per_day_formula_second_cell,
employee_total_hours_per_day_formula_cell, cell_format)
employee_total_man_days_per_month_formula_first_cell = \
WorksheetCell(employee_row_index, self._name_data_column_index, None, None)
employee_total_man_days_per_month_formula_second_cell = \
WorksheetCell(employee_total_man_days_row_index, self._first_month_day_column_index +
days_in_month + 1, None, None)
employee_total_man_days_per_month_formula_cell = \
WorksheetCell(employee_row_index, self._employee_logged_time_per_day_first_data_column_index +
days_in_month + 1, None, None)
self.write_indirect_formula(summary_worksheet, employee_total_man_days_per_month_formula_first_cell,
employee_total_man_days_per_month_formula_second_cell,
employee_total_man_days_per_month_formula_cell, None)
total_man_days_formula_range = \
WorksheetCellsRange(employee_row_index, self._first_month_day_column_index, employee_row_index,
days_in_month + self._first_month_day_column_index - 1, None, None)
total_man_days_formula_cell = WorksheetCell(employee_row_index, self._first_month_day_column_index +
days_in_month, None, None)
self.write_sum_formula(summary_worksheet, total_man_days_formula_range, total_man_days_formula_cell,
None)
row_index += 1
name_columns_range = WorksheetColumnsRange(self._name_data_column_index, self._name_data_column_index,
self._name_column_width)
self.set_columns(summary_worksheet, name_columns_range)
level_columns_range = WorksheetColumnsRange(self._level_data_column_index, self._level_data_column_index,
self._level_column_width)
self.set_columns(summary_worksheet, level_columns_range)
def fill_summary_worksheet_with_template(self, summary_worksheet,
formats_dict, timesheet_year,
timesheet_month,
teams_and_employees_count,
po_number, man_days_costs):
self.fill_worksheet_with_template(summary_worksheet,
formats_dict,
timesheet_year,
timesheet_month,
teams_and_employees_count,
self._level_data_column_index,
include_man_days_data=False,
include_total_per_task_section=False)
timesheet_last_month_day = self._date.get_month_days_count(
timesheet_year, timesheet_month)
purchase_order_cell = \
WorksheetCell(self._purchase_order_row_index, self._name_data_column_index,
self._purchase_order_text, formats_dict['centered_and_bold_format'])
self.write_to_cell(summary_worksheet, purchase_order_cell)
po_number_cell = WorksheetCell(
self._purchase_order_row_index + 1, self._name_data_column_index,
po_number, formats_dict['centered_and_bold_format'])
self.write_to_cell(summary_worksheet, po_number_cell)
summary_total_man_days_column_index = self._summary_table_initial_column_index + 1
summary_table_base_row_position_index = teams_and_employees_count + self._summary_table_row_index_buffer
last_team_member_row_index = teams_and_employees_count + self._first_data_row_index - 1
total_per_day_row_index = last_team_member_row_index + 1
summary_table_cost_column_index = summary_total_man_days_column_index + 1
total_per_person_summary_column_index = self._first_month_day_column_index + timesheet_last_month_day
total_man_days_summary_column_index = total_per_person_summary_column_index + 1
total_man_days_cost_column_index = summary_total_man_days_column_index + 1
summary_table_summary_cell = \
WorksheetCell(summary_table_base_row_position_index, self._summary_table_initial_column_index,
self._man_days_summary_table_summary_column_text,
formats_dict['summary_man_days_table_headers_format'])
self.write_to_cell(summary_worksheet, summary_table_summary_cell)
summary_table_man_days_cell = \
WorksheetCell(summary_table_base_row_position_index, summary_total_man_days_column_index,
self._man_days_summary_table_total_man_days_column_text,
formats_dict['summary_man_days_table_headers_format'])
self.write_to_cell(summary_worksheet, summary_table_man_days_cell)
summary_table_cost_cells_range = \
WorksheetCellsRange(summary_table_base_row_position_index, summary_table_cost_column_index,
summary_table_base_row_position_index, summary_table_cost_column_index + 1,
self._man_days_summary_table_cost_column_text,
formats_dict['summary_man_days_table_headers_format'])
self.merge_range(summary_worksheet, summary_table_cost_cells_range)
for level in self._man_days_summary_table_levels_list:
level_index = self._man_days_summary_table_levels_list.index(level)
if level_index % 2 == 1:
cell_format = formats_dict[
'specific_background_color_with_align_center_format']
formula_cell_format = formats_dict[
'centered_text_with_specific_color_format']
else:
cell_format = formats_dict['centered_text_format']
formula_cell_format = formats_dict['centered_text_format']
level_row_index = summary_table_base_row_position_index + level_index + 1
level_cell = WorksheetCell(
level_row_index, self._summary_table_initial_column_index,
level, cell_format)
self.write_to_cell(summary_worksheet, level_cell)
total_man_days_column_index = self._first_data_row_index + timesheet_last_month_day
total_man_days_formula_first_range = \
WorksheetCellsRange(self._first_data_row_index, self._level_data_column_index,
last_team_member_row_index, self._level_data_column_index, None, None)
total_man_days_formula_condition_cell = \
WorksheetCell(level_row_index, self._summary_table_initial_column_index, None, None)
total_man_days_formula_second_range = \
WorksheetCellsRange(self._first_data_row_index, total_man_days_column_index, last_team_member_row_index,
total_man_days_column_index, None, None)
total_man_days_formula_cell = \
WorksheetCell(level_row_index, summary_total_man_days_column_index, None, None)
self.write_sumif_formula(summary_worksheet,
total_man_days_formula_first_range,
total_man_days_formula_condition_cell,
total_man_days_formula_second_range,
total_man_days_formula_cell,
formula_cell_format)
man_days_per_level_cost_formula_cell = \
WorksheetCell(level_row_index, summary_total_man_days_column_index, None, None)
man_days_per_level_cost_formula_range = \
WorksheetCellsRange(level_row_index, summary_table_cost_column_index, level_row_index,
summary_table_cost_column_index + 1, None, None)
self.write_product_formula_in_merge(
summary_worksheet, man_days_per_level_cost_formula_cell,
man_days_costs[level], man_days_per_level_cost_formula_range,
cell_format)
levels_count = len(self._man_days_summary_table_levels_list)
summary_table_total_row_index = summary_table_base_row_position_index + levels_count + 1
first_level_row_index = summary_table_base_row_position_index + 1
summary_table_table_total_cell = \
WorksheetCell(summary_table_total_row_index, self._summary_table_initial_column_index,
self._man_days_summary_table_total_row_text, formats_dict['summary_total_row_format'])
self.write_to_cell(summary_worksheet, summary_table_table_total_cell)
total_man_days_summary_table_formula_range = WorksheetCellsRange(
first_level_row_index, summary_total_man_days_column_index,
summary_table_base_row_position_index + levels_count,
summary_total_man_days_column_index, None, None)
total_man_days_summary_table_formula_cell = WorksheetCell(
summary_table_total_row_index, summary_total_man_days_column_index,
None, None)
self.write_sum_formula(summary_worksheet,
total_man_days_summary_table_formula_range,
total_man_days_summary_table_formula_cell,
formats_dict['centered_and_bold_text_format'])
# total_man_days_cost_formula
levels_cells_range = \
WorksheetCellsRange(first_level_row_index, total_man_days_cost_column_index,
summary_table_base_row_position_index + levels_count, total_man_days_cost_column_index,
None, None)
formula_cells_range = WorksheetCellsRange(
summary_table_total_row_index, summary_table_cost_column_index,
summary_table_total_row_index, summary_table_cost_column_index + 1,
None, None)
self.write_sum_formula_in_merge(
summary_worksheet, levels_cells_range, formula_cells_range,
formats_dict['centered_and_bold_text_format'])
total_per_person_summary_column_formula_range = \
WorksheetCellsRange(self._first_data_row_index, total_per_person_summary_column_index,
last_team_member_row_index, total_per_person_summary_column_index, None, None)
total_per_person_summary_column_formula_cell = WorksheetCell(
total_per_day_row_index, total_per_person_summary_column_index,
None, None)
self.write_sum_formula(summary_worksheet,
total_per_person_summary_column_formula_range,
total_per_person_summary_column_formula_cell,
formats_dict['table_header_format'])
total_man_days_summary_formula_range = \
WorksheetCellsRange(self._first_data_row_index, total_man_days_summary_column_index,
last_team_member_row_index, total_man_days_summary_column_index, None, None)
total_man_days_summary_formula_cell = \
WorksheetCell(total_per_day_row_index, total_man_days_summary_column_index, None, None)
self.write_sum_formula(summary_worksheet,
total_man_days_summary_formula_range,
total_man_days_summary_formula_cell,
formats_dict['table_header_format'])
legend_base_row_position_index = teams_and_employees_count + self._legend_row_index_buffer
legend_base_column_index = self._summary_table_initial_column_index + self._legend_column_index_buffer
legend_header_cells_range = \
WorksheetCellsRange(legend_base_row_position_index, legend_base_column_index,
legend_base_row_position_index, legend_base_column_index +
self._legend_header_cells_merge_range, self._legend_title_text,
formats_dict['centered_and_bold_format'])
self.merge_range(summary_worksheet, legend_header_cells_range)
legend_item_index = 0
for legend_item in self._legend_items_to_formula_map:
legend_item_cells_range = \
WorksheetCellsRange(legend_base_row_position_index + legend_item_index + 1,
legend_base_column_index + 1, legend_base_row_position_index +
legend_item_index + 1, legend_base_column_index +
self._legend_header_cells_merge_range, legend_item, None)
self.merge_range(summary_worksheet, legend_item_cells_range)
legend_item_color_cell = WorksheetCell(
legend_base_row_position_index + legend_item_index + 1,
legend_base_column_index, '',
formats_dict[self._legend_items_to_formula_map[legend_item]])
self.write_to_blank_cell(summary_worksheet, legend_item_color_cell)
legend_item_index += 1