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