def past_header(self, title, zeroCell):
self.enable_batch(True)
style_cell = Cell(zeroCell, worksheet=self.wks)
self.set_header_styles(style_cell)
drange = DataRange(
start=zeroCell,
end=(
zeroCell[0] + self.headeing_height - 1,
zeroCell[1] + self.columns - 1,
),
worksheet=self.wks,
)
drange.merge_cells("MERGE_ALL")
drange.apply_format(style_cell)
self.enable_batch(False)
style_cell.value = title
self.current_row = drange.end_addr[0]
def past_info_cells(self, nextRow, values):
style_cell = Cell((nextRow, self.zeroCell[1]), worksheet=self.wks)
self.enable_batch(True)
self.set_info_styles(style_cell)
style_range = DataRange(
start=(style_cell.row, style_cell.col),
end=(style_cell.row + len(values) - 1, style_cell.col + self.columns - 1),
worksheet=self.wks,
)
style_range.apply_format(style_cell)
ranges = []
for index, _ in enumerate(values):
info_cell = Cell(
(style_cell.row + index, style_cell.col + 1), worksheet=self.wks
)
value_range = DataRange(
start=(info_cell.row, info_cell.col),
end=(info_cell.row, info_cell.col + self.columns - 2),
worksheet=self.wks,
)
value_range.merge_cells("MERGE_ALL")
ranges.append(
[
(style_cell.row + index, style_cell.col),
(info_cell.row, info_cell.col),
]
)
self.current_row = style_cell.row + index
self.enable_batch(False)
self.wks.update_values_batch(ranges=ranges, values=values, majordim="COLUMNS")
def render_events(self, events, worth, wks):
columns = self.get_columns(worth=worth)
participants_column_index = index_of(ColumnNames.PARTICIPANT, columns)
volonters_column_index = index_of(ColumnNames.VOLONTEERS, columns)
organizers_column_index = index_of(ColumnNames.ORGANIZERS, columns)
looser_index = index_of(ColumnNames.COMPETITION_PARTICIPANT_WORTH_0, columns)
playoff_index = index_of(ColumnNames.COMPETITION_PARTICIPANT_WORTH_1, columns)
winners_index = index_of(
ColumnNames.COMPETITION_PARTICIPANT_WORTH_1_NOMINATED, columns
)
sum_index = index_of(ColumnNames.SUM, columns)
self.apply_cells_formating(wks=wks, dx=(len(events) * len(columns)) - 1)
canonical_events_count = events.filter(is_canonical=True).count()
canonical_events_sum_bal = {
Participant.WorthEnum.DEFAULT: 0,
Participant.WorthEnum.VOLONTEER: 0,
Participant.WorthEnum.ORGANIZER: 0,
"winner": 0,
"playoff": 0,
"looser": 0,
}
last_festival_count = self.brigades.count()
# значения
ranges = []
values = []
self.enable_batch(True)
for event in events:
logger.warn(f"Current event: {event}")
title_range = DataRange(
start=(1, self.cursor),
end=(1, self.cursor + len(columns) - 1),
worksheet=wks,
)
title_range.merge_cells("MERGE_ALL")
# dict(brigade_id: int)
event_canonical_competitions_sum_value = {
"winner": {},
"playoff": {},
"looser": {},
}
ranges.append([(1, self.cursor), (1, self.cursor + len(columns) - 1)])
ranges.append([(2, self.cursor), (2, self.cursor + len(columns) - 1)])
# hack -- pasting value in merged cell
title_values = [""] * len(columns)
title_values[0] = event.title
values.append([title_values])
values.append([columns])
data = {}
for brigade in Brigade.objects.all():
data[brigade.id] = [None] * len(columns)
event_participants = event.participant.all()
event_date = event.start_date.date()
nominations_count = Nomination.objects.filter(
competition__event=event, is_rated=True
).count()
unique_brigade_count = set()
for participant in event_participants:
### Перебираем участников/волонтеров/оргов ###
brigade = participant.brigade
if brigade:
# TODO определиться что делать в случае length != 1
boec_seasons = Season.objects.filter(
boec=participant.boec, brigade=participant.brigade
).order_by("-year")
last_season = boec_seasons[0]
else:
boec_seasons = Season.objects.filter(
boec=participant.boec
).order_by("-year")
last_season = boec_seasons[0]
# вынесено, чтобы зачесть пред. отряду при выезде в новый отряд
report = last_season.season_reports.first()
last_season_year = report.year
# Если мероприятие осеннее, то мы зачтем все весенние мероприятия выезжавшим в новом сезоне
if (
boec_seasons.count() > 1
and last_season.year == 2021
and event.start_date.date() < autumn_started_date
):
last_season = boec_seasons[1]
report = last_season.season_reports.first()
unique_brigade_count.add(report.brigade.id)
# проверяем можно ли зачесть ему
is_accepted = check_is_accepted(
worth=participant.worth,
year=last_season_year,
event_date=event_date,
ignored=participant.ignored,
)
if (
event.worth == Event.EventWorth.VOLUNTEER
and participant.worth == Participant.WorthEnum.DEFAULT
):
is_accepted = True
if not is_accepted:
continue
report = last_season.season_reports.first()
brigade_id = report.brigade.id
# плсюуем в нужное место
index = None
if (
participants_column_index != None
and participant.worth == Participant.WorthEnum.DEFAULT
):
index = participants_column_index
if (
volonters_column_index != None
and participant.worth == Participant.WorthEnum.VOLONTEER
):
index = volonters_column_index
if (
organizers_column_index != None
and participant.worth == Participant.WorthEnum.ORGANIZER
):
index = organizers_column_index
if index != None:
current_value = data[brigade_id][index] or 0
data[brigade_id][index] = current_value + 1
# если спорт или творчество, ищем конкурсы
if 1 <= event.worth <= 2:
# убираем, где не нужно учитывать рейтинг
competitions = event.competitions.filter(ratingless=False)
sport_competition_sum_bal = {"winner": {}, "playoff": {}, "looser": {}}
for competition in competitions:
competition_participants = (
competition.competition_participation.all()
)
competition_participants_count = competition_participants.count()
brigades_in_playoff = competition_participants.filter(
worth=CompetitionParticipant.WorthEnum.INVOLVEMENT
)
brigades_in_playoff_count = brigades_in_playoff.count()
competition_winners = brigades_in_playoff.filter(
nomination__is_rated=True
)
competition_playoff = brigades_in_playoff.exclude(
nomination__is_rated=True
)
competition_loosers = competition_participants.filter(
worth=CompetitionParticipant.WorthEnum.DEFAULT
)
# нам нужно среднее по каждому соревнованию
if looser_index and event.worth == Event.EventWorth.SPORT:
# ПОДАЧА ЗАЯВКИ и Участие в соревнованиях
for not_playoff_participant in competition_loosers:
brigades = not_playoff_participant.brigades.all()
brigades_count = brigades.count()
for brigade in brigades:
prev_value = data[brigade.id][looser_index] or 0
new_value = 1 / brigades_count
data[brigade.id][looser_index] = prev_value + new_value
# Баллы за участие в соревнованиях мужской и женский дивизион считаются отдельно:
# нужно понимать, что бал в колонке не будет напрямую связан с числом в столбце подсчета
if event.is_canonical:
# добавляем бал
counted_looser_value = (
0.1
* competition_participants_count
/ brigades_in_playoff_count
)
sport_competition_sum_bal["looser"][
brigade.id
] = counted_looser_value
value = sport_competition_sum_bal["looser"].get(
brigade.id, None
)
data[brigade.id][looser_index + 1] = value
continue
else:
current_value = data[brigade.id][looser_index] or 0
counted_looser_value = (
current_value
* canonical_events_sum_bal["looser"]
/ canonical_events_count
)
data[brigade.id][looser_index + 1] = (
None
if counted_looser_value == 0
else counted_looser_value
)
if playoff_index:
for playoff_participant in competition_playoff:
brigades = playoff_participant.brigades.all()
brigades_count = brigades.count()
for brigade in brigades:
prev_value = data[brigade.id][playoff_index] or 0
new_value = 1 / brigades_count
if event.is_canonical:
if event.worth == Event.EventWorth.ART:
# Если отряд участвует в нескольких номерах/номинациях, участие учитывается один раз. (самое максимальное)
data[brigade.id][playoff_index] = (
new_value
if new_value > prev_value
else data[brigade.id][playoff_index]
)
# добавляем бал
counted_playoff_value = (
0.2
* data[brigade.id][playoff_index]
* (last_festival_count / nominations_count)
)
# среднее высчитывается после основного подсчета
event_canonical_competitions_sum_value[
"playoff"
][brigade.id] = counted_playoff_value
if event.worth == Event.EventWorth.SPORT:
# Баллы за участие в соревнованиях мужской и женский дивизион считаются отдельно:
# нужно понимать, что бал в колонке не будет напрямую связан с числом в столбце подсчета
data[brigade.id][playoff_index] = (
prev_value + new_value
)
# добавляем бал
counted_playoff_value = (
0.2
* competition_participants_count
/ brigades_in_playoff_count
)
sport_competition_sum_bal["playoff"][
brigade.id
] = counted_playoff_value
value = sport_competition_sum_bal[
"playoff"
].get(brigade.id, None)
data[brigade.id][playoff_index + 1] = value
continue
else:
data[brigade.id][playoff_index] = (
prev_value + new_value
)
current_value = data[brigade.id][playoff_index] or 0
counted_playoff_value = (
current_value
* canonical_events_sum_bal["playoff"]
/ canonical_events_count
)
data[brigade.id][playoff_index + 1] = (
None
if counted_playoff_value == 0
else counted_playoff_value
)
if winners_index:
for winner_participant in competition_winners:
brigades = winner_participant.brigades.all()
brigades_count = brigades.count()
for brigade in brigades:
prev_value = data[brigade.id][winners_index] or 0
new_value = 1 / brigades_count
data[brigade.id][winners_index] = prev_value + new_value
# добавляем бал
if event.is_canonical:
if event.worth == Event.EventWorth.ART:
counted_winner_value = (
0.3
* data[brigade.id][winners_index]
* (last_festival_count / nominations_count)
)
# среднее высчитывается после основного подсчета
event_canonical_competitions_sum_value[
"winner"
][brigade.id] = counted_winner_value
if event.worth == Event.EventWorth.SPORT:
# Баллы за участие в соревнованиях мужской и женский дивизион считаются отдельно:
# нужно понимать, что бал в колонке не будет напрямую связан с числом в столбце подсчета
nomination = winner_participant.nomination.get(
sport_place__isnull=False
)
place_koef = 0
if nomination.sport_place == 1:
place_koef = 0.15
if nomination.sport_place == 2:
place_koef = 0.10
if nomination.sport_place == 3:
place_koef = 0.05
counted_winner_value = (
place_koef * competition_participants_count
)
prev_value = sport_competition_sum_bal[
"winner"
].get(brigade.id, 0)
sport_competition_sum_bal["winner"][
brigade.id
] = (prev_value + counted_winner_value)
value = sport_competition_sum_bal["winner"].get(
brigade.id, None
)
data[brigade.id][winners_index + 1] = value
continue
else:
current_value = data[brigade.id][winners_index] or 0
counted_winner_value = (
current_value
* canonical_events_sum_bal["winner"]
/ canonical_events_count
)
data[brigade.id][winners_index + 1] = (
None
if counted_winner_value == 0
else counted_winner_value
)
# складывание баллов за соревнования
if event.worth == Event.EventWorth.SPORT and event.is_canonical:
winners_length = len(sport_competition_sum_bal["winner"])
playoff_count = len(sport_competition_sum_bal["playoff"])
looser_count = len(sport_competition_sum_bal["looser"])
if winners_length > 0:
for brigade_id, value in sport_competition_sum_bal[
"winner"
].items():
prev_value = event_canonical_competitions_sum_value[
"winner"
].get(brigade_id, 0)
event_canonical_competitions_sum_value["winner"][
brigade_id
] = (prev_value + value)
if playoff_count > 0:
for brigade_id, value in sport_competition_sum_bal[
"playoff"
].items():
prev_value = event_canonical_competitions_sum_value[
"playoff"
].get(brigade_id, 0)
event_canonical_competitions_sum_value["playoff"][
brigade_id
] = (prev_value + value)
if looser_count > 0:
for brigade_id, value in sport_competition_sum_bal[
"looser"
].items():
prev_value = event_canonical_competitions_sum_value[
"looser"
].get(brigade_id, 0)
event_canonical_competitions_sum_value["looser"][
brigade_id
] = (prev_value + value)
# теперь проставляем бал
# отсортировано по каноничности
for worth, _ in Participant.WorthEnum.choices:
all_participant_count = event_participants.filter(worth=worth).count()
if all_participant_count != 0:
for brigade_id, value in data.items():
count_index = None
if worth == Participant.WorthEnum.DEFAULT:
count_index = participants_column_index
if worth == Participant.WorthEnum.VOLONTEER:
count_index = volonters_column_index
if worth == Participant.WorthEnum.ORGANIZER:
count_index = organizers_column_index
if count_index != None:
# сколько человек в данной worth
brigade_participants_count = value[count_index] or 0
if brigade_participants_count > 0:
counted_value = None
if event.worth == Event.EventWorth.CITY:
if worth == Participant.WorthEnum.VOLONTEER:
counted_value = (
brigade_participants_count
* 0.1
* (
last_festival_count
/ all_participant_count
)
)
if worth == Participant.WorthEnum.ORGANIZER:
counted_value = (
brigade_participants_count
* 0.3
* (
last_festival_count
/ all_participant_count
)
)
else:
if event.is_canonical:
if worth == Participant.WorthEnum.DEFAULT:
counted_value = (
0.1
* (
last_festival_count
/ all_participant_count
)
*
# 10 участников = 1 волонтеру
brigade_participants_count
) / 10
if worth == Participant.WorthEnum.VOLONTEER:
counted_value = (
0.1
* (
last_festival_count
/ all_participant_count
)
* brigade_participants_count
)
if worth == Participant.WorthEnum.ORGANIZER:
counted_value = (
0.3
* (
last_festival_count
/ all_participant_count
)
* brigade_participants_count
)
if counted_value != None:
canonical_events_sum_bal[worth] += (
counted_value / all_participant_count
)
else:
if canonical_events_count > 0:
counted_value = (
brigade_participants_count
* canonical_events_sum_bal[worth]
/ canonical_events_count
)
# индекс бала в массиве
data[brigade_id][count_index + 1] = counted_value
# считаем среднее по каноническим конкурсам
# СРЕДНЕЕ ПО СТОЛБЦУ
if event.is_canonical:
winners_length = len(event_canonical_competitions_sum_value["winner"])
playoff_count = len(event_canonical_competitions_sum_value["playoff"])
looser_count = len(event_canonical_competitions_sum_value["looser"])
if winners_length > 0:
winners_average = (
sum(event_canonical_competitions_sum_value["winner"].values())
/ winners_length
)
canonical_events_sum_bal["winner"] += winners_average
if playoff_count > 0:
playoff_average = (
sum(event_canonical_competitions_sum_value["playoff"].values())
/ playoff_count
)
canonical_events_sum_bal["playoff"] += playoff_average
if looser_count > 0:
looser_average = (
sum(event_canonical_competitions_sum_value["looser"].values())
/ looser_count
)
canonical_events_sum_bal["looser"] += looser_average
# формируем данные для того, чтобы отправить их
# извелкаем id из словаря. нужен чтобы знать строку в таблице
# важно знать, что в data лежат даже мертвые отряды
brigades_ids = [brigade.id for brigade in self.brigades]
for key, value in data.items():
try:
if key in brigades_ids:
current_index = brigades_ids.index(key)
values.append([value])
row = self.header_height + current_index + 1
ranges.append(
[(row, self.cursor), (row, self.cursor + len(columns) - 1)]
)
except Exception as e:
logger.exception("render_events() error: ", exc_info=e)
self.cursor = self.cursor + len(columns)
self.enable_batch(False)
self.wks.update_values_batch(ranges=ranges, values=values, majordim="ROWS")