示例#1
0
    async def upload_attendance(self, attendance_list):
        # build dataframe to upload to google sheet from attendance list
        df = self.pd.DataFrame(attendance_list, columns = ['Date', 'Mission Name', 'Participant', 'Airframe', 'Role'])

        # setup authorization for google 
        gc = pygsheets.authorize(service_account_file=bundled_data_path(self) / "service_account.json")
        # open specific google sheet based on key, stored in dbconfig file
        sh = gc.open_by_key(self.dbconfig.attendance_sheet_key)
        # set worksheet to second tab
        wks = sh[1]
        # pull data on current sheet for use in determing where to place dataframe and coloring
        cells = wks.get_col(1, include_tailing_empty=False, returnas='matrix')
        last_row = len(cells)
        data_rows = len(df) + last_row
        beige = (0.9882353, 0.8980392, 0.8039216, 0)
        no_color = (None, None, None, None)
        white = (1, 1, 1, 0)

        # Set pandas dataframme as cell values
        wks.set_dataframe(df, start=(last_row + 1,1), copy_head=False, extend=True)
        # add cell background coloring for each different mission dataframe
        previous_color = wks.cell(f'A{last_row - 1}').color
        if previous_color == no_color or previous_color == white and last_row != 1:
            model_cell = pygsheets.Cell("A2")
            model_cell.color = beige
            DataRange(f'A{last_row + 1}',f'E{data_rows}', worksheet=wks).apply_format(model_cell, fields = "userEnteredFormat.backgroundColor")
        elif previous_color == beige and last_row != 1:
            model_cell = pygsheets.Cell("A2")
            model_cell.color = white
            DataRange(f'A{last_row + 1}',f'E{data_rows}', worksheet=wks).apply_format(model_cell, fields = "userEnteredFormat.backgroundColor")  

        status = {'worksheet': sh.title, 'tab': wks.title}
        return status
示例#2
0
    def create_first_column(self, wks):
        self.enable_batch(True)

        zero_coords = (1, 1)
        style_cell = Cell(zero_coords, worksheet=wks)
        self.set_header_styles(style_cell)

        drange = DataRange(
            start=zero_coords,
            end=(
                zero_coords[0] + self.header_height - 1,
                zero_coords[1],
            ),
            worksheet=wks,
        )
        drange.apply_format(style_cell)

        brigade_zero_coords = (self.header_height + 1, 1)
        brigade_cell = Cell(brigade_zero_coords, worksheet=wks)
        self.set_brigade_style(brigade_cell)

        drange = DataRange(
            start=brigade_zero_coords,
            end=(
                brigade_zero_coords[0] + len(self.brigades) - 1,
                brigade_zero_coords[1],
            ),
            worksheet=wks,
        )
        drange.apply_format(brigade_cell)

        wks.adjust_row_height(1, None, 50)
        wks.adjust_row_height(2, self.header_height, 75)
        wks.adjust_column_width(1, None, 200)

        self.enable_batch(False)

        values = [
            [["Название мероприятия", "Участник/статус"]],
        ]

        brigades_titles = [str(brigade) for brigade in self.brigades]

        ranges = [
            (zero_coords, (zero_coords[0] + self.header_height - 1, zero_coords[1])),
            (
                brigade_zero_coords,
                (
                    brigade_zero_coords[0] + len(self.brigades) - 1,
                    brigade_zero_coords[1],
                ),
            ),
        ]

        values.append([brigades_titles])
        self.wks.update_values_batch(ranges=ranges, values=values, majordim="COLUMNS")
示例#3
0
 def named_ranges(self):
     """All named ranges in this spreadsheet."""
     return [
         DataRange(namedjson=x,
                   name=x['name'],
                   worksheet=self.worksheet('id',
                                            x['range'].get('sheetId', 0)))
         for x in self._named_ranges
     ]
示例#4
0
def getMsgFromShBot():

    mesgs = []
    wrsh = spreadsheet_maker.getWorkSheetbyIndex(cnf.INDEX_BOT_WS)
    rows = DataRange(start='A1', worksheet=wrsh).cells
    for row in rows:
        if row[0].value == "TRUE":
            mesgs.append(row[1].value)

    return mesgs
示例#5
0
 def protected_ranges(self):
     """All protected ranges in this spreadsheet."""
     response = self.client.sheet.get(
         spreadsheet_id=self.id,
         fields='sheets(properties.sheetId,protectedRanges)')
     return [
         DataRange(protectedjson=x,
                   worksheet=self.worksheet('id',
                                            sheet['properties']['sheetId']))
         for sheet in response['sheets']
         for x in sheet.get('protectedRanges', [])
     ]
示例#6
0
def store_thinkific_members():
    """
    Function to store all Thinkific Members who are enrolled in UA in Google Sheets
    """
    today_date_time = datetime.now()
    report_date_time = today_date_time.strftime("%b %d, %Y %H:%M:%S")
    #   create a new spread sheet in the given folder
    thinkific_members_sheet = client.create(
        title="UA Thinkific Members " + str(report_date_time),
        folder="1cIjZbTLwNEDo4YdknD8bUu9VPx-Ky7I-")

    #   add a new worksheet to the spreadsheet
    thinkific_wks = thinkific_members_sheet.add_worksheet("UA Members")

    thinkific_df = pd.DataFrame(
        list_of_members
    )  # , columns=["Member Email Address", "Member First Name", "Member Last Name"]
    thinkific_wks.set_dataframe(thinkific_df,
                                start=(1, 1),
                                copy_index=False,
                                copy_head=True,
                                extend=True)

    # change NaN values to blanks
    thinkific_wks.replace("NaN", replacement="", matchEntireCell=True)

    #   format the headers in bold
    # thinkific_wks.cell("A1").set_text_format("bold", True)
    # thinkific_wks.cell("B1").set_text_format("bold", True)
    # thinkific_wks.cell("C1").set_text_format("bold", True)

    bold_cell = thinkific_wks.cell('A1')
    bold_cell.set_text_format('bold', True)
    DataRange('A1', 'L1', worksheet=thinkific_wks).apply_format(bold_cell)

    # sort sheet by email addresses
    thinkific_wks.sort_range(start='A2',
                             end='L50000',
                             basecolumnindex=1,
                             sortorder='ASCENDING')

    # set column width to match content width
    thinkific_wks.adjust_column_width(start=1, end=10, pixel_size=None)

    #   Share spreadsheet with read only access to anyone with the link
    thinkific_members_sheet.share('', role='reader', type='anyone')

    #   print the direct link to the spreadsheet for the user running the code to access
    print("The UA Thinkific Members List can be found here: ",
          thinkific_members_sheet.url)
示例#7
0
    def apply_cells_formating(self, wks, dx):
        # форматирование
        self.enable_batch(True)

        # for header
        zero_coords = (1, 2)
        style_cell = Cell(zero_coords, worksheet=wks)
        self.set_header_styles(style_cell)

        drange = DataRange(
            start=zero_coords,
            end=(
                zero_coords[0] + self.header_height - 1,
                zero_coords[1] + dx,
            ),
            worksheet=wks,
        )
        drange.apply_format(style_cell)

        # for body
        data_zero_coords = (self.header_height + 1, 2)
        data_cell = Cell(data_zero_coords, worksheet=wks)
        self.set_data_style(data_cell)
        data_cell.set_number_format(
            pattern="##0.#####", format_type=pygsheets.FormatType.NUMBER
        )

        drange = DataRange(
            start=data_zero_coords,
            end=(
                data_zero_coords[0] + len(self.brigades) - 1,
                data_zero_coords[1] + dx,
            ),
            worksheet=wks,
        )
        drange.apply_format(data_cell)
        wks.adjust_column_width(
            data_zero_coords[1],
            data_zero_coords[1] + dx,
            150,
        )
        self.enable_batch(False)
示例#8
0
def addShContact(chat_id="", phone="", date="", msg_last="", date_last=""):

    wrsh = spreadsheet_maker.getWorkSheetbyIndex(cnf.INDEX_CONTACT_WS)

    # All сells
    rows = DataRange(start='A2', worksheet=wrsh).cells

    if findRowByValueInColum(rows=rows,
                             value=chat_id,
                             colum=contactws_cnf.COLUM_ID) == -1:
        empty_row = rows[findIndexOfEmptyRow(rows)]
        setRow(empty_row, chat_id, phone, date, msg_last, date_last)
        print(
            "Add contact chat_id = %s, phone = %s, date = %s, msg_last = %s, date_last = %s"
            % (chat_id, phone, date, msg_last, date_last))
        return True
    else:
        print("contact %s already exists" % chat_id)
        return False
示例#9
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")
示例#10
0
    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]
示例#11
0
# -- get the working sheet
sheet = spreadsheet.worksheet_by_title('Dashboard')

# -- working sheet info
print(f'WorkSheet : {sheet.title}  size: ({sheet.rows}x{sheet.cols})')

# -- update values in wks
sheet.update_value('A1', '20')
sheet.update_value('B1', '30')
sheet.update_value('C1', '=A1+B1')

# -- setting validaton on a cell
sheet.set_data_validation(start="C2",
                          end="C2",
                          condition_type='ONE_OF_LIST',
                          condition_values=['One', 'Two'],
                          showCustomUi='True')

# -- change cell color
cellB6 = sheet.cell('B6')
c1, c2, c3 = colors.hex_to_rgb("#4285f4")
cellB6.color = (c1 / 255, c2 / 255, c3 / 255, 0.9
                )  # color is a tuple (red, green, blue, alpha) of float values

# -- change row color
row10 = DataRange(
    f'A10', 'Z10',
    worksheet=sheet)  # or row10 = sheet.get_row(10, returnas = "range")
row10.apply_format(cellB6)

# -- formating column text
示例#12
0
    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")
示例#13
0
    def past_boec(self, nextRow, queryset, worth=0):
        params = queryset.values_list("id", "last_name", "first_name", "middle_name")

        data = []
        for boec in params:
            full_name = f"{boec[1]} {boec[2]} {boec[3]}"

            participant = Participant.objects.get(
                boec=boec[0], event=self.object, worth=worth
            )
            ignored = participant.ignored

            # TODO определиться что делать в случае length != 1
            boec_seasons = Season.objects.filter(boec=boec[0]).order_by("-year")
            last_season: 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 self.object.start_date.date() < autumn_started_date
            ):
                last_season = boec_seasons[1]

            is_accepted = check_is_accepted(
                worth=worth,
                year=last_season_year,
                event_date=self.object.start_date.date(),
                ignored=ignored,
            )
            if (
                self.object.worth == Event.EventWorth.VOLUNTEER
                and worth == Participant.WorthEnum.DEFAULT
            ):
                is_accepted = True

            report = last_season.season_reports.first()
            row = [
                full_name.strip(),
                str(report.brigade),
                last_season.year,
                is_accepted,
            ]

            data.append(row)

        if len(data) != 0:
            self.enable_batch(True)
            style_cell = Cell((nextRow, self.zeroCell[1]), worksheet=self.wks)
            self.set_info_styles(style_cell)

            style_range = DataRange(
                start=(nextRow, self.zeroCell[1]),
                end=(nextRow + len(data) - 1, self.zeroCell[1] + self.columns - 1),
                worksheet=self.wks,
            )
            style_range.apply_format(style_cell)
            self.enable_batch(False)
            self.wks.update_values_batch(
                ranges=[
                    (
                        (nextRow, self.zeroCell[1]),
                        (nextRow + len(data) - 1, self.zeroCell[1] + self.columns - 1),
                    )
                ],
                values=[data],
                majordim="ROWS",
            )
            self.wks.set_data_validation(
                start=(nextRow, self.zeroCell[1] + self.columns - 1),
                end=(nextRow + len(data) - 1, self.zeroCell[1] + self.columns - 1),
                condition_type="BOOLEAN",
            )

            self.current_row = style_range.end_addr[0]