class Competition(MPTTModel):
    KINDS = XChoices((0, 'velo', _('Velo')),
                    (1, 'cross_country', _('Cross Country')), )

    name = models.CharField(max_length=100)
    alias = AutoSlugField(populate_from='name', always_update=True)
    short_name = models.CharField(max_length=50, blank=True)

    created_by = models.ForeignKey('core.User', related_name='created_%(class)s_set', null=True, blank=True)
    modified_by = models.ForeignKey('core.User', related_name='modified_%(class)s_set', null=True, blank=True)
    created = models.DateTimeField(auto_now_add=True)
    modified = models.DateTimeField(auto_now=True)

    place_name = models.CharField(max_length=50, blank=True)
    competition_date = models.DateField(blank=True, null=True)
    competition_date_till = models.DateField(blank=True, null=True)

    kind = models.SmallIntegerField(choices=KINDS, default=KINDS.velo)

    complex_payment_enddate = models.DateTimeField(blank=True, null=True)
    complex_payment_hideon = models.DateTimeField(blank=True, null=True)
    complex_discount = models.SmallIntegerField(default=0)

    bill_series = models.CharField(max_length=20, blank=True, default='B')
    payment_channel = models.CharField(max_length=20, default='LKDF')

    parent = TreeForeignKey('self', null=True, blank=True, related_name='children')

    # class path that will do processing tasks. Example for result creation.
    processing_class = models.CharField(max_length=100, blank=True)

    legacy_id = models.IntegerField(blank=True, null=True)

    sitetree = models.ForeignKey('sitetree.TreeItem', blank=True, null=True, on_delete=models.SET_NULL)

    is_in_menu = models.BooleanField(default=False)

    skin = models.CharField(max_length=50, blank=True)
    logo = ThumbnailerImageField(upload_to=_get_logo_upload_path, blank=True,)
    logo_svg = models.FileField(upload_to=_get_logo_upload_path, blank=True)

    apply_image = ThumbnailerImageField(upload_to=_get_logo_upload_path, blank=True, )

    params = JSONField(blank=True, null=True)

    sms_text = models.CharField(max_length=255, blank=True)

    map_url = models.URLField(blank=True)

    frontpage_ordering = models.PositiveSmallIntegerField(default=0)

    description = models.TextField(blank=True)

    use_official_team_results = models.BooleanField(default=True)

    is_individual = models.BooleanField(default=False, help_text="If the stage should have individual start list")

    class MPTTMeta:
        order_insertion_by = ['competition_date', 'id']

    def __str__(self):
        return self.name

    @property
    def params_dict(self):
        if isinstance(self.params, dict):
            return self.params
        return json.loads(self.params)

    def get_ids(self):
        if self.level == 2:
            return self.id, self.parent_id
        else:
            return self.id,

    def get_all_children_ids(self):
        if self.level == 2:
            return tuple(obj.id for obj in Competition.objects.filter(parent_id=self.parent_id))
        elif self.get_root().id == 1:
            return tuple(obj.id for obj in Competition.objects.filter(parent_id=self.id))
        else:
            return self.id,

    def get_distances(self):
        return Distance.objects.filter(competition_id__in=self.get_ids()).select_related('competition')

    def get_insurances(self):
        insurances = Insurance.objects.filter(competition_id__in=self.get_ids())
        if self.level == 1 and self.get_root().id == 1:
            insurances = insurances.filter(in_complex=True)
        return insurances.select_related('competition')

    @property
    def get_full_name(self):
        if self.level == 2:
            return '%s - %s' % (self.parent.name, self.name)
        else:
            return self.name

    def get_logo(self):
        if self.level == 2:
            return self.parent.logo
        else:
            return self.logo

    @property
    def competition_header(self):
        if not self.skin:
            return None
        return "core/competition_header/%s.html" % self.skin

    @property
    def is_past_due(self):
        if not self.competition_date and self.competition_date_till:
            if datetime.date.today() < self.competition_date_till:
                return False
            else:
                return True

        if datetime.date.today() >= self.competition_date:
            return True
        return False

    def get_random_image(self):
        from velo.gallery.models import Photo
        photo = Photo.objects.filter(album__competition__tree_id=self.tree_id, is_featured=False, album__gallery_date__year__in=[timezone.now().year, timezone.now().year - 1]).order_by('?').first()
        if photo:
            return photo
        else:
            return Photo.objects.get(id=73105)

    @property
    def is_application_active(self):
        if (self.complex_payment_enddate and self.complex_payment_enddate > timezone.now()) or self.price_set.active().exists():
            return True
        return False

    def get_absolute_url(self):
        return reverse('competition:competition', args=[self.id]) def create_start_list(competition=None, competition_id=None, ever_started=True):
    if not competition and not competition_id:
        raise Exception('Expected at least one variable')
    if not competition:
        competition = Competition.objects.get(id=competition_id)

    root_competition = competition.get_root()

    output = BytesIO()
    distances = competition.get_distances()

    wbk = xlwt.Workbook()

    for distance in distances:
        sheet = wbk.add_sheet(slugify(str(distance))[:30])

        helperresults = HelperResults.objects.filter(competition=competition, participant__distance=distance, participant__is_participating=True).select_related('participant', 'participant__competition', 'participant__distance', 'participant__application',
                                                                                      'participant__price', 'participant__primary_number').order_by('participant__distance', 'participant__primary_number__group', 'participant__primary_number__number', 'participant__registration_dt')

        row = 4
        header_row = (
            '#', 'UID', 'Numurs', 'Alias', 'Sacensības', 'Distance', 'Uzvārds', 'Vārds', 'Dzimšanas diena', 'Dzimums', 'Pilsēta', 'Sacenšas?',
            'Grupa', 'Pieteikuma ziedojums', 'Dalības maksa', 'Apdrošināšanas maksa', 'Kopā samaksāts', 'Atlaižu kods', 'E-pasts', 'Telefons', 'Valsts', 'Komanda', 'Velo', 'Izveidots', 'Punkti', 'Koridors', 'T-krekls')
        for col, value in enumerate(header_row):
            sheet.write(row, col, value)

        row = 5
        for index, res in enumerate(helperresults, start=1):
            item = res.participant
            total_entry_fee = item.total_entry_fee
            total_insurance_fee = item.total_insurance_fee
            final_price = item.final_price

            if root_competition.id == 1 and item.competition.level == 1:
                child_count = item.competition.get_children().filter(is_individual=False).count()
                total_entry_fee = total_entry_fee / child_count
                total_insurance_fee = total_insurance_fee / child_count
                final_price = final_price / child_count

            donation = 0.0
            if item.application:
                donation = item.application.donation

            has_participated = None
            if not ever_started:
                if res.calculated_total and res.calculated_total > 0.0:
                    has_participated = True
                else:
                    has_participated = Participant.objects.filter(slug=item.slug, is_participating=True).filter(Q(competition__parent__parent_id=1)|Q(competition__parent_id=1)).exclude(id=item.id).exists()

            row_values = (
                index, item.id, str(item.primary_number), item.slug, str(item.competition), str(item.distance), item.last_name,
                item.first_name, item.birthday.strftime("%Y-%m-%d"), item.gender, str(item.city) if item.city else "", str(item.is_competing), item.group, donation, total_entry_fee, total_insurance_fee, final_price,
                str(item.application.discount_code or '') if item.application else '', item.email, item.phone_number, str(item.country), item.team_name, str(item.bike_brand2) if item.bike_brand2 else '',
                item.registration_dt.astimezone(riga_tz).strftime("%Y-%m-%d %H:%M"), res.calculated_total, res.passage_assigned if res.passage_assigned else "", item.t_shirt_size if item.t_shirt_size else '', str(has_participated))


            for col, value in enumerate(row_values):
                sheet.write(row, col, value)
            row += 1

    wbk.save(output)
    return output def create_standing_list(competition=None, competition_id=None):
    if not competition and not competition_id:
        raise Exception('Expected at least one variable')
    if not competition:
        competition = Competition.objects.get(id=competition_id)
    output = BytesIO()
    distances = competition.get_distances()

    wbk = xlwt.Workbook()

    for distance in distances:
        sheet = wbk.add_sheet(slugify(str(distance), only_ascii=True).replace(" ", "_")[:25])
                
        slugs = []

        items = SebStandings.objects.filter(competition_id__in=competition.get_ids(),distance=distance).select_related('participant', 'participant__competition', 'participant__distance', 'participant__price', 'participant__primary_number').order_by('participant__distance', 'participant__primary_number__group', 'participant__primary_number__number', 'participant__registration_dt')

        row = 4
        header_row = (
            '#', 'St.ID', 'Numurs', 'Alias', 'Sacensības', 'Distance', 'Uzvārds', 'Vārds', 'Dzimšanas diena', 'Dzimums',
            'Grupa', 'E-pasts', 'Telefons', 'Valsts', 'Komanda', 'Velo', 'Rezultāts', 'Punkti', 'VISI piešķirtie numuri')
        for col, value in enumerate(header_row):
            sheet.write(row, col, value)

        row = 5
        for index, item in enumerate(items, start=1):
            row_values = (
                index, item.id, str(item.participant.primary_number), item.participant_slug, str(item.participant.competition), str(item.participant.distance), item.participant.last_name,
                item.participant.first_name, item.participant.birthday.strftime("%Y-%m-%d"), item.participant.gender, item.participant.group,
                item.participant.email, item.participant.phone_number, str(item.participant.country), item.participant.team_name, str(item.participant.bike_brand2) if item.participant.bike_brand2 else '',
                item.distance_place, item.distance_total, ','.join([str(obj.number) for obj in item.participant.numbers()]))

            for col, value in enumerate(row_values):
                sheet.write(row, col, value)
            row += 1

    wbk.save(output)
    return output def team_member_list(competition=None, competition_id=None):
    if not competition and not competition_id:
        raise Exception('Expected at least one variable')
    if not competition:
        competition = Competition.objects.get(id=competition_id)
    output = BytesIO()
    distances = competition.get_distances().filter(can_have_teams=True)

    notpayed_pattern = xlwt.Pattern()
    notpayed_pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    notpayed_pattern.pattern_fore_colour = 2

    not_payed_style = xlwt.XFStyle()
    not_payed_style.pattern = notpayed_pattern

    payed_style = xlwt.XFStyle()

    wbk = xlwt.Workbook()

    for distance in distances:
        sheet = wbk.add_sheet(slugify(str(distance))[:30])

        row=0
        sheet.write(row, 0, "Team ID")
        sheet.write(row, 1, "Team Name")
        sheet.write(row, 2, "First Name")
        sheet.write(row, 3, "Last Name")
        sheet.write(row, 4, "Year")
        sheet.write(row, 5, "Kind")
        sheet.write(row, 6, "Found participant")
        sheet.write(row, 7, "Bike brand")
        sheet.write(row, 8, "Found unpaid")
        sheet.write(row, 9, "Found potential")
        sheet.write(row, 10, "NR")
        sheet.write(row, 11, "Papildus numuri")

        row += 1
        for team in distance.team_set.all():
            for member in competition.memberapplication_set.filter(member__team=team).order_by('kind').select_related('member', 'participant', 'participant_unpaid', 'participant_potential', 'participant__primary_number',):
                is_payed = True if member.participant_id else False
                sheet.write(row, 0, str(team.id), payed_style if is_payed else not_payed_style)
                sheet.write(row, 1, str(team), payed_style if is_payed else not_payed_style)
                sheet.write(row, 2, str(member.member.first_name), payed_style if is_payed else not_payed_style)
                sheet.write(row, 3, str(member.member.last_name), payed_style if is_payed else not_payed_style)
                sheet.write(row, 4, str(member.member.birthday.year), payed_style if is_payed else not_payed_style)
                sheet.write(row, 5, str(member.get_kind_display()), payed_style if is_payed else not_payed_style)
                sheet.write(row, 6, str(member.participant.slug) if member.participant else '', payed_style if is_payed else not_payed_style)
                sheet.write(row, 7, str(member.participant.bike_brand2) if member.participant and member.participant.bike_brand2 else '', payed_style if is_payed else not_payed_style)
                sheet.write(row, 8, str(member.participant_unpaid.slug) if member.participant_unpaid else '', payed_style if is_payed else not_payed_style)
                sheet.write(row, 9, str(member.participant_potential.slug) if member.participant_potential else '', payed_style if is_payed else not_payed_style)
                number = Number.objects.filter(participant_slug=member.member.slug, competition_id__in=competition.get_ids(), distance=team.distance).order_by('-number')
                if number:
                    sheet.write(row, 10, str(number[0]), payed_style if is_payed else not_payed_style)
                    if number.count()>1:
                        sheet.write(row, 11, str(','.join([str(obj.number) for obj in number[1:]])), payed_style if is_payed else not_payed_style)
                row += 1

    wbk.save(output)
    return output def create_team_list(competition=None, competition_id=None):
    if not competition and not competition_id:
        raise Exception('Expected at least one variable')
    if not competition:
        competition = Competition.objects.get(id=competition_id)

    distances = competition.get_distances().filter(can_have_teams=True)

    output = BytesIO()

    notpayed_pattern = xlwt.Pattern()
    notpayed_pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    notpayed_pattern.pattern_fore_colour = 0x16

    not_payed_style = xlwt.XFStyle()
    not_payed_style.pattern = notpayed_pattern

    payed_style = xlwt.XFStyle()



    wbk = xlwt.Workbook()

    for distance in distances:
        sheet = wbk.add_sheet(slugify(str(distance))[:30])

        sheet.col(0).width = 256 * 4
        sheet.col(1).width = 256 * 4
        sheet.col(2).width = 256 * 5
        sheet.col(3).width = 256 * 20
        sheet.col(4).width = 256 * 5

        sheet.col(5).width = 256 * 4

        sheet.col(6).width = 256 * 4
        sheet.col(7).width = 256 * 4
        sheet.col(8).width = 256 * 5
        sheet.col(9).width = 256 * 20
        sheet.col(10).width = 256 * 5


        row = 5
        for index, team in enumerate(distance.team_set.filter(member__memberapplication__competition=competition, status__gte=0).defer('member__memberapplication__competition__params').distinct(), start=1):
            col_add = 0 if index % 2 == 1 else 6
            sheet.write(row, 0 + col_add, str(index))
            sheet.write(row, 1 + col_add, str(team))
            row += 1
            next_line = row
            for member in competition.memberapplication_set.filter(member__team=team).order_by('kind'):
                is_payed = True if member.participant_id else False



                sheet.write(row, 1 + col_add, str(member.get_kind_display())[0], payed_style if is_payed else not_payed_style)
                sheet.write(row, 2 + col_add, str(member.participant.primary_number) if member.participant else '', payed_style if is_payed else not_payed_style)
                sheet.write(row, 3 + col_add, str(member.member.full_name), payed_style if is_payed else not_payed_style)
                sheet.write(row, 4 + col_add, str(member.member.birthday.year), payed_style if is_payed else not_payed_style)


                row += 1
            if index % 2 == 0:
                row = next_line + 12
            else:
                row = next_line - 1

    wbk.save(output)
    return output def payment_list(competition=None, competition_id=None):
    if not competition and not competition_id:
        raise Exception('Expected at least one variable')
    if not competition:
        competition = Competition.objects.get(id=competition_id)
    output = BytesIO()

    wbk = xlwt.Workbook()

    applications = Application.objects.filter(competition_id__in=competition.get_ids(),
                                              payment_status=Application.PAY_STATUS.payed)

    sheet = wbk.add_sheet('Applications')
    row = 4
    header_row = (
        'ID', 'Izveidots', 'Labots', 'Sacensības', 'Statuss', 'www.velo.lv ID', 'Uzņēmums', 'Rēķina numurs', 'Fināla cena',
        'Ebill kods', 'Ebill Statuss', 'Ebill summa', 'Ebill Kanāls')
    for col, value in enumerate(header_row):
        sheet.write(row, col, value)
    row += 1
    for application in applications:
        payments = application.payment_set.filter(status=Payment.STATUSES.ok)
        row_values = (
            application.id, application.created.date(), application.modified.date(), str(application.competition), application.get_payment_status_display(),
            application.legacy_id, application.company_name, application.invoice.invoice_nr if application.invoice else "",  application.final_price
        )
        for payment in payments:
            row_values += (
                payment.erekins_code, payment.get_status_display(), payment.total, str(payment.channel),
            )
        for col, value in enumerate(row_values):
            sheet.write(row, col, value)
        row += 1

    wbk.save(output)
    return output def create_donations_list(competition=None, competition_id=None):
    if not competition and not competition_id:
        raise Exception('Expected at least one variable')
    if not competition:
        competition = Competition.objects.get(id=competition_id)

    root_competition = competition.get_root()

    output = BytesIO()

    wbk = xlwt.Workbook()

    sheet = wbk.add_sheet('donations')

    applications = Application.objects.filter(competition_id__in=competition.get_ids(), payment_status=Application.PAY_STATUS.payed).exclude(donation=0.0).prefetch_related('participant_set').select_related('competition')

    row = 1
    header_row = (
        '#', 'Pieteikuma ID', 'Sacensības', 'Uzvārds', 'Vārds', 'Personas kods', 'E-pasts', 'Pieteikuma ziedojums', 'Veiksmīgo maksājumu skaits', 'Veiksmiga maksajuma ziedojuma summa (check)', 'Rekina NR')
    for col, value in enumerate(header_row):
        sheet.write(row, col, value)

    row = 2
    for index, application in enumerate(applications, start=1):
        participant = application.participant_set.all()[0]
        total_payed = 0.0
        total_count = 0
        payments = application.payment_set.filter(status=Payment.STATUSES.ok)

        if payments:
            total_count = payments.count()
            total_payed = payments[0].donation

        row_values = (
            index, application.id, str(application.competition), participant.last_name,
            participant.first_name, participant.ssn, application.email, application.donation, total_count, total_payed, application.invoice.invoice_nr if application.invoice else "")

        for col, value in enumerate(row_values):
            sheet.write(row, col, value)
        row += 1

    wbk.save(output)
    return output def start_list_have_participated_this_year(competition=None, competition_id=None):
    if not competition and not competition_id:
        raise Exception('Expected at least one variable')
    if not competition:
        competition = Competition.objects.get(id=competition_id)

    root_competition = competition.get_root()
    child_competitions = competition.get_siblings()

    output = BytesIO()
    distances = competition.get_distances()

    wbk = xlwt.Workbook()

    for distance in distances:
        slugs_participates = [obj.slug for obj in distance.participant_set.filter(is_participating=True, competition=competition)]
        sheet = wbk.add_sheet(slugify(str(distance))[:30])

        row = 4
        header_row = (
            '#', 'UID', 'Numurs', 'Alias', 'Sacensības', 'Distance', 'Uzvārds', 'Vārds', 'Dzimšanas diena', 'Dzimums', 'Pilsēta',
            'Grupa', 'Pieteikuma ziedojums', 'Dalības maksa', 'Apdrošināšanas maksa', 'Kopā samaksāts', 'Atlaižu kods', 'E-pasts', 'Telefons', 'Valsts', 'Komanda', 'Velo', 'Izveidots', 'Rezultāts - Punkti')
        for col, value in enumerate(header_row):
            sheet.write(row, col, value)

        participants = distance.participant_set.filter(is_participating=True).filter(competition__in=child_competitions).exclude(slug__in=slugs_participates).order_by('distance', 'last_name', 'first_name', '-competition')

        row = 5
        for index, item in enumerate(participants, start=1):
            res = item.result_set.order_by('-points_distance')
            res_points = None
            if res:
                res_points = res[0].points_distance


            total_entry_fee = item.total_entry_fee
            total_insurance_fee = item.total_insurance_fee
            final_price = item.final_price

            if root_competition.id == 1 and item.competition.level == 1:
                child_count = item.competition.get_children().filter(is_individual=False).count()
                total_entry_fee = total_entry_fee / child_count
                total_insurance_fee = total_insurance_fee / child_count
                final_price = final_price / child_count

            donation = 0.0
            if item.application:
                donation = item.application.donation

            row_values = (
                index, item.id, str(item.primary_number), item.slug, str(item.competition), str(item.distance), item.last_name,
                item.first_name, item.birthday.strftime("%Y-%m-%d"), item.gender, str(item.city), item.group, donation, total_entry_fee, total_insurance_fee, final_price,
                str(item.application.discount_code or '') if item.application else '', item.email, item.phone_number, str(item.country), item.team_name, str(item.bike_brand2) if item.bike_brand2 else '',
                item.registration_dt.astimezone(riga_tz).strftime("%Y-%m-%d %H:%M"), res_points)

            for col, value in enumerate(row_values):
                sheet.write(row, col, value)
            row += 1

    wbk.save(output)
    return output def create_temporary_participant_list(competition=None, competition_id=None):
    if not competition and not competition_id:
        raise Exception('Expected at least one variable')
    if not competition:
        competition = Competition.objects.get(id=competition_id)

    output = BytesIO()
    distances = competition.get_distances()

    wbk = xlwt.Workbook()

    for distance in distances:
        sheet = wbk.add_sheet(slugify(str(distance))[:30])

        participants = Participant.objects.filter(competition__in=competition.get_ids(), distance=distance, is_participating=True, is_temporary=True).select_related('competition', 'distance', 'application',
                                                                                      'price', 'primary_number').order_by('distance', 'primary_number__group', 'primary_number__number', 'registration_dt')
        row = 4
        header_row = (
            '#', 'UID', 'Numurs', 'Alias', 'Sacensības', 'Distance', 'Uzvārds', 'Vārds', 'Dzimšanas diena', 'Dzimums', 'Pilsēta', 'Sacenšas?',
            'Grupa', 'E-pasts', 'Telefons', 'Valsts', 'Komanda', 'Velo', 'Izveidots')
        for col, value in enumerate(header_row):
            sheet.write(row, col, value)

        row = 5
        for index, participant in enumerate(participants, start=1):

            row_values = (
                index, participant.id, str(participant.primary_number), participant.slug, str(participant.competition), str(participant.distance), participant.last_name,
                participant.first_name, participant.birthday.strftime("%Y-%m-%d"), participant.gender, str(participant.city) if participant.city else "", str(participant.is_competing), participant.group,
                participant.email, participant.phone_number, str(participant.country), participant.team_name, str(participant.bike_brand2) if participant.bike_brand2 else '',
                participant.created.astimezone(riga_tz).strftime("%Y-%m-%d %H:%M"))

            for col, value in enumerate(row_values):
                sheet.write(row, col, value)
            row += 1

    wbk.save(output)
    return output class Payment(TimestampMixin, models.Model):
    STATUSES = Choices((10, 'new', _('New')),
                       (20, 'pending', _('Pending')),
                       (30, 'ok', _('OK')),
                       (-10, 'reversed', _('Reversed')),
                       (-20, 'cancelled', _('Cancelled')),
                       (-30, 'timeout', _('Timeout')),
                       (-40, 'declained', _('Declined')),
                       (-50, 'failed', _('Failed')),
                       (-60, 'error', _('Error')),
                       (-70, 'id_not_found', _('ID not found')),
                       )

    # This model can have relation to either Application or Team
    content_type = models.ForeignKey(ContentType, on_delete=models.PROTECT, null=True, blank=True)
    object_id = models.PositiveIntegerField(null=True, blank=True)
    content_object = GenericForeignKey('content_type', 'object_id')

    # Remove next 2 fields
    channel = models.ForeignKey('payment.ActivePaymentChannel', blank=True, null=True)
    erekins_code = models.CharField(max_length=100, blank=True)  # Erekins code

    total = models.DecimalField(max_digits=20, decimal_places=2, default=0.0)
    donation = models.DecimalField(max_digits=20, decimal_places=2, default=0.0)

    status = models.SmallIntegerField(choices=STATUSES, default=STATUSES.new)

    competition = models.ForeignKey('core.Competition', null=True, blank=True)  # used for payment matching in analytics @celery.task
def update_family_codes(file_name: str):
    import xlrd
    from velo.payment.models import DiscountCode

    campaign_ids = [6, 8]
    file_path = os.path.join(settings.MEDIA_ROOT, file_name)
    # adding new codes
    with xlrd.open_workbook(file_path) as wb:
        sheet = wb.sheet_by_name('Pieaugušo kartes')
        code_list = []
        for row in range(1, sheet.nrows):
            active_code = sheet.row_values(row)[0]
            d_codes = DiscountCode.objects.filter(code=active_code)

            if not d_codes:
                for camp_id in campaign_ids:
                    DiscountCode.objects.create(
                        campaign_id=camp_id,
                        code=active_code,
                        usage_times=0,
                    )
            code_list.append(active_code)

    # disabling codes if they ar not in the file
    for discount_code in DiscountCode.objects.filter(campaign_id__in=campaign_ids):
        if discount_code.code not in code_list:
            discount_code.is_active = False
            discount_code.save()

    os.remove(file_path) def reset_family_codes(campaign_id: int = None):
    from velo.payment.models import DiscountCode
    discount_codes = DiscountCode.objects.filter(campaign_id=campaign_id, usage_times_left=0, is_active=True)

    for code in discount_codes:
        code.usage_times_left = 1
        code.usage_times = 0
        code.save() class Application(TimestampMixin, models.Model):
    PAY_STATUS = XChoices((0, 'not_payed', _("Haven't Payed")),
                          (10, 'waiting', _('Waiting for Payment')),
                          (20, 'payed', _('Payed')),
                          (30, 'never', _('Won\'t pay')),
                          (-10, 'cancelled', _('Cancelled')), )

    competition = models.ForeignKey('core.Competition', verbose_name=_('Competition'))
    payment_status = models.SmallIntegerField(_('Payment Status'), default=PAY_STATUS.not_payed, choices=PAY_STATUS)
    discount_code = models.ForeignKey('payment.DiscountCode', blank=True, null=True)

    email = models.EmailField(blank=True)
    can_send_newsletters = models.BooleanField(_("I want to receive velo.lv news in email."), default=True)

    code = models.CharField(max_length=50, default=uuid.uuid4, unique=True)
    legacy_id = models.IntegerField(blank=True, null=True)

    company_name = models.CharField(_('Company name / Full Name'), max_length=100, blank=True)
    company_vat = models.CharField(_('VAT Number'), max_length=100, blank=True)
    company_regnr = models.CharField(_('Company number / SSN'), max_length=100, blank=True)
    company_address = models.CharField(_('Address'), max_length=100, blank=True)
    company_juridical_address = models.CharField(_('Juridical Address'), max_length=100, blank=True)
    invoice_show_names = models.BooleanField(_('Show participant names in invoice'), default=True)

    external_invoice_code = models.CharField(_('Invoice code'), max_length=100, blank=True)  # invoice code from e-rekins used to allow downloading invoice from velo.lv
    external_invoice_nr = models.CharField(_('Invoice Number'), max_length=20, blank=True)  # invoice number from e-rekins used in card payment
    invoice = models.ForeignKey('payment.Invoice', null=True, blank=True)

    donation = models.DecimalField(_('Donation'), max_digits=20, decimal_places=2, default=0.0)

    # Totals
    total_discount = models.DecimalField(max_digits=20, decimal_places=2, default=0.0)
    total_entry_fee = models.DecimalField(max_digits=20, decimal_places=2, default=0.0)
    total_insurance_fee = models.DecimalField(max_digits=20, decimal_places=2, default=0.0)
    final_price = models.DecimalField(max_digits=20, decimal_places=2, default=0.0)

    params = JSONField(default={})

    language = models.CharField(max_length=10, choices=LANGUAGES, default='lv')

    payment_set = GenericRelation(Payment)

    def save(self, *args, **kwargs):
        self.set_final_price()
        return super(Application, self).save(*args, **kwargs)

    def set_final_price(self):
        if self.discount_code:
            _class = load_class(self.discount_code.campaign.discount_kind)
            discount = _class(application=self)
            price = discount.get_final_price_for_application()
            if isinstance(price, float):
                self.final_price = price + float(self.donation)
                return
            else:
                self.discount_code = None

        self.final_price = float(self.total_entry_fee) + float(self.total_insurance_fee) + float(self.donation)

    @property
    def competition_name(self):
        if self.competition.level == 2:
            return '%s - %s' % (self.competition.parent, self.competition)
        else:
            return str(self.competition) def import_lrf_licences_2018():
    from velo.registration.models import UCICategory
    get_params = {'limit': 100}

    for index in [0, 100, 200, 300, 400, 500, 600, 700, 800, 900, 1000]:
        get_params.update({'startat': index})
        lrf_licence_html = requests.get('http://lrf.lv/index.php/licences/2019-gada-licencu-saraksts', params=get_params)
        beautiful_lrf_licence = BeautifulSoup(lrf_licence_html.text, 'html.parser')
        table = beautiful_lrf_licence.find('table', {'class': 'ui table'})
        columns = table.find('thead').find_all('th')
        col = []
        for column in columns:
            col.append(column.get_text().strip())
        rows = table.find('tbody').find_all('tr')
        for row in rows:
            row_dict = {}
            for idx, cell in enumerate(row.find_all('td')):
                value = "" if cell.string is None else cell.string.strip()
                if col[idx] == "UCI kods":
                    if value.isnumeric():
                        row_dict.update({"code": "LAT" + value})
                    else:
                        # UCI ID column contains not only numeric values!
                        print(value)
                        row_dict.update({"code": "LAT" + ''.join(re.findall(r'\b\d+\b', value))})
                elif col[idx] == 'Uzvārds':
                    row_dict.update({"last_name": value})
                elif col[idx] == 'Vārds':
                    row_dict.update({"first_name": value})
                elif col[idx] == 'UCI kategorija':
                    row_dict.update({"category": value})
                elif col[idx] == 'Kategorija':
                    row_dict.update({"group": value})
                elif col[idx] == 'Lic.derīga':
                    if not value:
                        value = '31.12.2018'
                    row_dict.update({"valid_until": datetime.datetime.strptime(value, '%d.%m.%Y')})
                else:
                    continue
            UCICategory.objects.update_or_create(**row_dict) @task(base=LogErrorsTask)
def update_helper_result_table(competition_id, update=False, participant_id=None):

    # Delete duplicate HelperResults if any.
    cursor = connection.cursor()
    cursor.execute("Select participant_id, count(*) from results_helperresults where competition_id=%s group by participant_id having count(*)>1", [competition_id])
    for participant_id, _ in cursor.fetchall():
        HelperResults.objects.filter(competition_id=competition_id, participant_id=participant_id)[0].delete()
    # END Delete duplicate

    competition = Competition.objects.get(id=competition_id)

    participants = Participant.objects.filter(is_participating=True).order_by('distance', 'registration_dt')

    if competition.is_individual:
        participants = participants.filter(competition=competition)
    else:
        participants = participants.filter(competition_id__in=competition.get_ids())

    if not update:
        participants = participants.extra(
            where=[
                "(Select hr1.calculated_total from results_helperresults hr1 where hr1.participant_id=registration_participant.id and hr1.competition_id = %s) is null"
            ], params=[competition_id, ],)

    if participant_id:
        participants = participants.filter(id=participant_id)

    if participants:
        class_ = load_class(competition.processing_class)
        competition_class = class_(competition=competition)

        competition_class.create_helper_results(participants)
import uuid
from django.contrib import messages
from django.contrib.contenttypes.models import ContentType
from django.core.files.storage import default_storage
from django.db.models import Sum, Count
from django.http import HttpResponse
from django.utils.decorators import method_decorator
from django.views.decorators.clickjacking import xframe_options_exempt
from django.views.generic import DetailView, TemplateView
from velo.core.models import Competition
from velo.manager.excels.income_list import create_income_list
from velo.manager.excels.insured import create_insured_list
from velo.manager.excels.start_list import create_start_list, create_standing_list, team_member_list, \
    create_team_list, payment_list, create_donations_list, start_list_have_participated_this_year, create_temporary_participant_list
from velo.manager.tables import ManageCompetitionTable
from velo.manager.views import ManageApplication
from velo.manager.views.permission_view import ManagerPermissionMixin
from velo.payment.models import Payment
from velo.payment.tasks import update_family_codes
from velo.payment.utils import reset_family_codes
from velo.registration.models import Application
from velo.registration.utils import import_lrf_licences_2018 as import_lrf_licences
from velo.velo.mixins.views import SingleTableViewWithRequest, SetCompetitionContextMixin
from velo.manager.tasks import *
from velo.team.tasks import match_team_members_to_participants, copy_registered_teams
from velo.results.tasks import update_helper_result_table



__all__ = [
    'ManageCompetitionList', 'ManageCompetitionDetail', 'ManageApplicationExternalPay'
]


class IncomeObject(object):
    distance = None
    parent_income = None
    income = None
    total = None

    parent_count = 0
    count = 0
    total_count = 0

    def __init__(self, distance, parent_income, income, parent_count, count):
        self.distance = distance
        if parent_income:
            self.parent_income = float(parent_income)
        self.income = float(income)
        if parent_income:
            self.total = self.parent_income + self.income
        else:
            self.total = self.income

        self.parent_count = parent_count
        self.count = count
        if self.parent_count:
            self.total_count = self.parent_count + self.count
        else:
            self.total_count = self.count


class ManageApplicationExternalPay(ManageApplication):
    template_name = 'bootstrap/manager/external_application_update.html'

    @method_decorator(xframe_options_exempt)
    def post(self, request, *args, **kwargs):
        super(ManageApplicationExternalPay, self).post(request, *args, **kwargs)
        return super(ManageApplicationExternalPay, self).get(request, *args, **kwargs)


class ManageCompetitionList(ManagerPermissionMixin, SingleTableViewWithRequest):
    model = Competition
    table_class = ManageCompetitionTable
    template_name = 'bootstrap/manager/table.html'


class ManageCompetitionDetail(ManagerPermissionMixin, SetCompetitionContextMixin, DetailView):
    model = Competition
    template_name = 'bootstrap/manager/competition_detail.html'

    def post(self, request, *args, **kwargs):
        self.competition = Competition.objects.get(id=kwargs.get('pk'))
        class_ = load_class(self.competition.processing_class)
        self._competition_class = class_(self.competition.id)
        if request.POST.get('action') == 'assign_numbers_continuously':
            self._competition_class.assign_numbers_continuously()
        elif request.POST.get('action') == 'legacy_sync':
            pass # TODO: Fix this
            # messages.add_message(request, messages.INFO,
            #                      'Sinhronizācija sākta. Gaidiet e-pastu uz %s ar paziņojumu par beigām.' % request.user.email)
            # legacy_sync.delay(request.user.email)
        elif request.POST.get('action') == 'start_list':
            file_obj = create_start_list(competition=self.competition)
            response = HttpResponse(content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=start_list.xls'
            response.write(file_obj.getvalue())
            file_obj.close()
            return response
        elif request.POST.get('action') == 'ever_started':
            file_obj = create_start_list(competition=self.competition, ever_started=False)
            response = HttpResponse(content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=ever_started.xls'
            response.write(file_obj.getvalue())
            file_obj.close()
            return response
        elif request.POST.get('action') == 'create_temporary_participant_list':
            file_obj = create_temporary_participant_list(competition=self.competition)
            response = HttpResponse(content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=temporary_participant_list.xls'
            response.write(file_obj.getvalue())
            file_obj.close()
            return response

        elif request.POST.get('action') == 'start_list_have_participated_this_year':
            file_obj = start_list_have_participated_this_year(competition=self.competition)
            response = HttpResponse(content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=have_participated_list.xls'
            response.write(file_obj.getvalue())
            file_obj.close()
            return response
        elif request.POST.get('action') == 'donations_list':
            file_obj = create_donations_list(competition=self.competition)
            response = HttpResponse(content_type='application/vnd.ms-excel')
            response['Content-Disposition'] = 'attachment; filename=donations_list.xls'
            response.write(file_obj.getvalue())
            file_obj.close()
            return response


        elif request.POST.get('action') == 'payment_list':

            file_obj = payment_list(competition=self.competition)