Python django.db.models 模块，Sum() 实例源码

def donations(request):
    '''
    actual data from the FEC, propublica data, through the django ORM cache
    '''
    data = {}

    data["representatives"] = {d["id"]: d for d in
        Representative.objects.all()
            .annotate(name=Concat('first_name', Value(" "), 'last_name'))
            .values("id", "name", "party")}

    data["committees"] = {d["id"]: d for d in
        SuperPAC.objects.all().values("id", "name")}

    data["donations"] = list(Donation.objects.all()
        .annotate(source=F("superpac_id"), destination=F("representative_id"))
        .values("source", "destination", "support")
        .annotate(amount=Sum("amount")))

    return JsonResponse(data)

def get_queryset(self):
        """Get queryset."""
        return self.model.objects.all() \
            .only('id',
                  'salutation',
                  'name',
                  'email') \
            .annotate(
                number_of_books=Count('books'),
                first_book_published_on=Min('books__publication_date'),
                last_book_published_on=Max('books__publication_date'),
                lowest_book_price=Min('books__price'),
                highest_book_price=Max('books__price'),
                average_book_price=Avg('books__price'),
                average_number_of_pages_per_book=Avg('books__pages'),
                number_of_books_sold=Count('books__order_lines'),
                total_amount_earned=Sum(
                    'books__order_lines__book__price'
                )
            )

def donationsDemo(request):
    '''
    actual data from the FEC, propublica data, through the django ORM cache
    this returns specially selected pieces of data for demo purposes
    '''

    data = {"representatives":{},"committees":{},"donations":[]}
    representative_list = Representative.objects.all()
    special_reps = [488,200,390,119,49,445,491]
    for rep in representative_list:
        if rep.first_name+" "+rep.last_name in ['Ted Cruz','Marco Rubio','Tammy Duckworth','Rand Paul','Ruben Kihuen','Stephanie Murphy']:
            data["representatives"][rep.id] = rep.__json__()

    superpac_list = SuperPAC.objects.all()
    special_pacs = [3505,3498,3453,3323,276]
    for sup in superpac_list:
        if sup.name in ['MAKE DC LISTEN','COURAGEOUS CONSERVATIVES PAC','CONSERVATIVE SOLUTIONS PAC','RIGHT TO RISE USA','NEXTGEN CLIMATE ACTION COMMITTEE','AMERICA\'S LIBERTY PAC','STAND FOR TRUTH','TEXAS TEA PARTY PATRIOTS','FLORIDIANS FOR A STRONG MIDDLE CLASS','HOUSE MAJORITY PAC','PLANNED PARENTHOOD VOTES','IMMIGRANT VOTERS WIN PAC']:
            data["committees"][sup.id] = sup.__json__()

    data["donations"] = list(Donation.objects.all()
        .annotate(source=F("superpac_id"), destination=F("representative_id"))
        .values("source", "destination", "support")
        .annotate(amount=Sum("amount")))

    return JsonResponse(data)

def save(self, *args, **kwargs):
        product_final = ProductFinal.objects.filter(pk=self.product_final_id).first()
        # se comprueba que no se repite el valor de las caracteristicas especiales de los productos finales cuando sean unicas
        if product_final:
            if product_final.product.feature_special and product_final.product.feature_special.unique:
                if ProductUnique.objects.filter(
                    value=self.value,
                    product_final__product=product_final.product
                ).exists():
                    raise ValidationError(_('Ya existe un producto final con el valor de la caracteristicas especial'))
        else:
            raise ValidationError(_("Product don't seleted"))

        # save and update stock of product final
        with transaction.atomic():
            r = super(ProductUnique, self).save(*args, **kwargs)
            product_final.stock_real = ProductUnique.objects.filter(product_final=product_final).aggregate(stock=Sum('stock_real'))['stock']
            product_final.save()
            return r


# producto estrella (solo un registro publico)

def get_queryset(self):
        """Get queryset."""
        return self.model.objects.all() \
            .select_related('publisher') \
            .prefetch_related('authors') \
            .only('id',
                  'title',
                  'pages',
                  'price',
                  'publisher__id',
                  'publisher__name',
                  'authors__id',
                  'authors__name') \
            .annotate(
                price_per_page=Sum(
                    F('price') / F('pages'),
                    output_field=DecimalField(decimal_places=2)
                )
            )

def get_queryset(self):
        """Get queryset."""
        return self.model.objects.all() \
            .only('id',
                  'salutation',
                  'name',
                  'email') \
            .annotate(
                number_of_books=Count('books'),
                first_book_published_on=Min('books__publication_date'),
                last_book_published_on=Max('books__publication_date'),
                lowest_book_price=Min('books__price'),
                highest_book_price=Max('books__price'),
                average_book_price=Avg('books__price'),
                average_number_of_pages_per_book=Avg('books__pages'),
                number_of_books_sold=Count('books__order_lines'),
                total_amount_earned=Sum(
                    'books__order_lines__book__price'
                )
            )

def _get_wordcount_stats(self):
        """calculate full wordcount statistics"""
        ret = {
            'total': 0,
            'translated': 0,
            'fuzzy': 0
        }

        # XXX: `order_by()` here is important as it removes the default
        # ordering for units. See #3897 for reference.
        res = (
            self.units.order_by().values('state')
                      .annotate(wordcount=models.Sum('source_wordcount'))
        )
        for item in res:
            ret['total'] += item['wordcount']
            if item['state'] == TRANSLATED:
                ret['translated'] = item['wordcount']
            elif item['state'] == FUZZY:
                ret['fuzzy'] = item['wordcount']

        return ret

def balance_of_one_to_one(self, end_time=None):
        '''
        ???????????????(???????????)
        '''
        school_account = None
        if hasattr(self, 'schoolaccount'):
            school_account = self.schoolaccount

        # ????????
        latest_time = None
        if school_account:
            latest_time = SchoolIncomeRecord.objects.filter(
                school_account=school_account,
                type=SchoolIncomeRecord.ONE_TO_ONE).aggregate(
                Max('income_time')
            )["income_time__max"] or None
        # ?????????????(?Order??)
        query_set = Order.objects.filter(school=self, status=Order.PAID,
                                         live_class__isnull=True)
        if latest_time:
            query_set = query_set.filter(paid_at__gt=latest_time)
        if end_time:
            query_set = query_set.filter(paid_at__lte=end_time)
        # ????????(????)
        return query_set.aggregate(Sum('to_pay'))["to_pay__sum"] or 0

def test_reverse_success():
    session = cashdesk_session_before_factory()
    trans = transaction_factory(session)
    pos = [transaction_position_factory(transaction=trans, product=product_factory(items=True)),
           transaction_position_factory(transaction=trans)]
    revtransid = reverse_transaction(trans_id=trans.pk, current_session=session)
    revtrans = Transaction.objects.get(pk=revtransid)
    assert revtrans.session == session
    revpos = revtrans.positions.all()
    assert len(revpos) == len(pos)
    for lp, rp in zip(pos, revpos):
        assert rp.reverses == lp
        assert rp.type == 'reverse'
        assert rp.value == -1 * lp.value
        assert rp.tax_value == -1 * lp.tax_value
        assert rp.product == lp.product
        assert {i.id for i in lp.items.all()} == {i.id for i in rp.items.all()}

        ls = TransactionPositionItem.objects.filter(position=lp).aggregate(s=Sum('amount'))['s']
        if ls:
            rs = TransactionPositionItem.objects.filter(position=rp).aggregate(s=Sum('amount'))['s']
            assert rs == ls * -1

def test_reverse_success_single():
    session = cashdesk_session_before_factory()
    trans = transaction_factory(session)
    lp = transaction_position_factory(transaction=trans, product=product_factory(items=True))
    revtrans = Transaction.objects.get(
        pk=reverse_transaction_position(trans_pos_id=lp.pk, current_session=session)
    )
    assert revtrans.session == session
    revpos = revtrans.positions.all()
    assert len(revpos) == 1
    rp = revpos[0]

    assert rp.reverses == lp
    assert rp.type == 'reverse'
    assert rp.value == -1 * lp.value
    assert rp.tax_value == -1 * lp.tax_value
    assert rp.product == lp.product
    assert {i.id for i in lp.items.all()} == {i.id for i in rp.items.all()}

    ls = TransactionPositionItem.objects.filter(position=lp).aggregate(s=Sum('amount'))['s']
    if ls:
        rs = TransactionPositionItem.objects.filter(position=rp).aggregate(s=Sum('amount'))['s']
        assert rs == ls * -1

def calculate_credit(self, user, start, end):
        """
        Calculate approved days of type for user in given time frame.

        For absence types which fill worktime this will be None.
        """
        if self.fill_worktime:
            return None

        credits = AbsenceCredit.objects.filter(
            user=user,
            absence_type=self,
            date__range=[start, end]
        )
        data = credits.aggregate(credit=Sum('days'))
        credit = data['credit'] or 0

        return credit

def calculate_duration(self, employment):
        """
        Calculate duration of absence with given employment.

        For fullday absences duration is equal worktime per day of employment
        for absences which need to fill day calcuation needs to check
        how much time has been reported on that day.
        """
        if not self.type.fill_worktime:
            return employment.worktime_per_day

        reports = Report.objects.filter(date=self.date, user=self.user_id)
        data = reports.aggregate(reported_time=models.Sum('duration'))
        reported_time = data['reported_time'] or timedelta()
        if reported_time >= employment.worktime_per_day:
            # prevent negative duration in case user already
            # reported more time than worktime per day
            return timedelta()

        return employment.worktime_per_day - reported_time

def dashboard(request):
    user = request.user
    connections = Connection.objects.filter(owner=user, is_active=True)
    form = AddConnectionForm(user)
    if not form.connected:
        return test_authentication(request)

    # Load statistics for superuser
    total_users = 0
    total_connections = 0
    tasks_completed = 0
    if user.is_superuser:
        total_users = User.objects.filter(is_active=True).count()
        total_connections = Connection.objects.filter(is_active=True).count()
        tasks_completed = Connection.objects.aggregate(Sum('tasks_completed'))[
            'tasks_completed__sum']

    return render(request, 'wunderhabit/dashboard.html', {
        'connections': connections,
        'form': form,
        'total_users': total_users,
        'total_connections': total_connections,
        'tasks_completed': tasks_completed,
    })

def talk_email(request):
    talks = Talk.objects.filter(pk__in=request.session.get('talk-email-list', []))
    count = talks.annotate(speakers_count=Count('speakers', distinct=True)).aggregate(Sum('speakers_count'))['speakers_count__sum']
    if not talks.exists():
        messages.error(request, _('Please select some talks.'))
        return redirect('talk-list')
    form = SendTalkMailForm(request.POST or None, initial=request.session.get('talk-email-stored'), talks=talks)
    if request.method == 'POST' and form.is_valid():
        subject = form.cleaned_data['subject']
        body = form.cleaned_data['body']
        request.session['talk-email-stored'] = {'subject': subject, 'body': body}
        if form.cleaned_data['confirm']:
            sent = talk_email_send(talks, subject, body)
            messages.success(request, _('%(count)d mails have been sent.') % {'count': sent})
            del request.session['talk-email-list']
            return redirect('talk-list')
        else:
            messages.info(request, _('Your ready to send %(count)d emails.') % {'count': count})
    else:
        form.fields.pop('confirm')
    return render(request, 'cfp/staff/talk_email.html', {
        'talks': talks,
        'form': form,
    })

def public_stats(request: HttpRequest) -> HttpResponse:
    """Display public galleries and archives stats."""
    if not crawler_settings.urls.enable_public_stats:
        if not request.user.is_staff:
            raise Http404("Page not found")
        else:
            return render_error(request, "Page disabled by settings (urls: enable_public_stats).")

    stats_dict = {
        "n_archives": Archive.objects.filter(public=True).count(),
        "archive": Archive.objects.filter(public=True).filter(filesize__gt=0).aggregate(
            Avg('filesize'), Max('filesize'), Min('filesize'), Sum('filesize')),
        "n_tags": Tag.objects.filter(gallery_tags__public=True).distinct().count(),
        "top_10_tags": Tag.objects.filter(gallery_tags__public=True).distinct().annotate(
            num_archive=Count('gallery_tags')).order_by('-num_archive')[:10]
    }

    d = {'stats': stats_dict}

    return render(request, "viewer/public_stats.html", d)

def get_context_data(self, **kwargs):
        """Context for rendering the sidebar and other items on the recipe
        details page """
        context = super(PackagesTable, self).get_context_data(**kwargs)

        recipe = Recipe.objects.get(pk=kwargs['recipe_id'])
        project = Project.objects.get(pk=kwargs['pid'])

        in_project = (recipe.layer_version.pk in
                      project.get_project_layer_versions(pk=True))

        packages = self.create_package_list(recipe, project.pk)

        context.update({'project': project,
                        'recipe' : recipe,
                        'packages': packages,
                        'approx_pkg_size' : packages.aggregate(Sum('size')),
                        'in_project' : in_project,
                       })

        return context

def to_representation(self, instance):
        value = super(ChannelMetadataSerializer, self).to_representation(instance)

        # if it has the file_size flag add extra file_size information
        if 'request' in self.context and self.context['request'].GET.get('file_sizes', False):
            descendants = instance.root.get_descendants().exclude(kind=content_kinds.TOPIC)
            total_resources = descendants.count()
            local_files = LocalFile.objects.filter(files__contentnode__channel_id=instance.id).distinct()
            total_file_size = local_files.aggregate(Sum('file_size'))['file_size__sum'] or 0

            on_device_resources = descendants.filter(available=True).count()
            on_device_file_size = local_files.filter(available=True).aggregate(Sum('file_size'))['file_size__sum'] or 0

            value.update(
                {
                    "total_resources": total_resources,
                    "total_file_size": total_file_size,
                    "on_device_resources": on_device_resources,
                    "on_device_file_size": on_device_file_size
                })

        return value

def credit_overview(request):
    bought = OrderLine.objects.all().exclude(order__user__isnull=True).values('order__user').annotate(used=Sum('price'))
    users = User.objects.all().values()
    print(bought)
    for user in users:
        for b in bought:
            user['used'] = 0
            user['left'] = 0
            if b['order__user'] == user['id']:
                user['used'] = b['used']
                user['left'] = user['credit'] - user['used']
                break

    context = {
        'users': users
    }

    return render(request, 'pos/credit_overview.djhtml', context)

def get_queryset(self):
        """
        Append the calculated service area based on business logic.
        (Some bureaus are in service areas not reflected by the data)
        """
        qs = models.BudgetHistory.objects.all()
        qs = qs.values('fiscal_year', 'service_area_code', 'bureau_code', 'bureau_name').annotate(
            sa_calced=Case(
                When(bureau_code__in = LA_Bureaus, then = Value('LA')),
                When(bureau_code__in = EO_Bureaus, then = Value('EO')),
                default = 'service_area_code',
                output_field = CharField()
            ),
            amount=Sum('amount'))
        qs = qs.order_by('fiscal_year', 'service_area_code', 'bureau_code', 'bureau_name')
        return qs

def get_queryset(self):
        """
        Calculate service area based on business logic.
        (Some bureaus are in service areas not reflected by the data)
        """
        qs = models.BudgetHistory.objects.all()
        qs = qs.values('fiscal_year', ).annotate(
            sa_calced=Case(
                When(bureau_code__in = LA_Bureaus, then = Value('LA')),
                When(bureau_code__in = EO_Bureaus, then = Value('EO')),
                default = 'service_area_code',
                output_field = CharField()
            ),
            amount=Sum('amount'),
        )
        qs = qs.order_by('fiscal_year', 'sa_calced')
        return qs

def prepareFinancialStatement(year=None):

    if year:
        filter_year = year
    else:
        filter_year = timezone.now().year

    expenses_ytd = list(ExpenseItem.objects.filter(accrualDate__year=filter_year).aggregate(Sum('total')).values())[0]
    revenues_ytd = list(RevenueItem.objects.filter(accrualDate__year=filter_year).aggregate(Sum('total')).values())[0]
    expenses_awaiting_approval = list(ExpenseItem.objects.filter(approved=False,paid=False).aggregate(Sum('total')).values())[0]
    expenses_awaiting_payment = list(ExpenseItem.objects.filter(approved=True,paid=False).aggregate(Sum('total')).values())[0]
    expenses_paid_notapproved = list(ExpenseItem.objects.filter(approved=False,paid=True).aggregate(Sum('total')).values())[0]

    return {
        'expenses_ytd': expenses_ytd,
        'revenues_ytd': revenues_ytd,
        'expenses_awaiting_approval': expenses_awaiting_approval,
        'expenses_awaiting_payment': expenses_awaiting_payment,
        'expenses_paid_notapproved': expenses_paid_notapproved,
    }

def get_nonreserved_free_resources(self, exclude_this_dc=False):
        """Return sum of non-reserved free resources on this node"""
        s = models.Sum  # First we need to count all reserved resources
        reserved = r = DcNode.objects.filter(node=self.node, strategy=self.RESERVED)
        dc_exclude = None

        if exclude_this_dc:
            dc_exclude = self.dc
            r = r.exclude(dc=self.dc)

        r = r.aggregate(s('cpu'), s('ram'), s('disk'))
        cpu_n, ram_n, disk_n = map(int, self.node.resources)  # And subtract reserved from real node resources
        cpu_f = cpu_n - (r['cpu__sum'] or 0)
        ram_f = ram_n - (r['ram__sum'] or 0)
        disk_f = disk_n - (r['disk__sum'] or 0)
        self._nonreserved_resources = (cpu_f, ram_f, disk_f)
        dcs_exclude = reserved.distinct('dc').values_list('dc', flat=True)

        return self.node.get_free_resources(cpu_f, ram_f, disk_f, dc_exclude=dc_exclude, dcs_exclude=dcs_exclude)

def combos(request):
    ids_separated_with_comma = request.path.split('/')[-1]
    if not ids_separated_with_comma:
        return render(request, 'combos.html', {'winners':[], 'eggs':[]})

    #    return HttpResponseRedirect(reverse('index', kwargs={}))

    ids = ids_separated_with_comma.split(',')

    eggs = map(lambda x:Egg.objects.filter(id=x).first(), ids)

    players = Player.objects.all().annotate(eggs=Count('catch'), score=Sum('catch__egg__points')).order_by('-eggs', '-score')

    winners = []

    for p in players:
        print "p:", p
        if p.has_eggs(eggs):
            winners.append(p)
            print "w", p

    return render(request, 'combos.html', {'winners':winners, 'eggs':eggs})

def obtener_kardex(self, almacen, desde, hasta):
        from almacen.models import Kardex
        hasta = hasta + datetime.timedelta(days=1)
        listado_kardex = Kardex.objects.filter(almacen=almacen,
                                               fecha_operacion__gte=desde,
                                               fecha_operacion__lte=hasta,
                                               producto__grupo_productos=self).order_by('producto__descripcion',
                                                                                         'fecha_operacion',
                                                                                         'cantidad_salida',
                                                                                         'created')
        if len(listado_kardex) > 0:
            cantidad_ingreso = listado_kardex.aggregate(Sum('cantidad_ingreso'))
            cantidad_salida = listado_kardex.aggregate(Sum('cantidad_salida'))
            t_cantidad_i = cantidad_ingreso['cantidad_ingreso__sum']
            t_cantidad_s = cantidad_salida['cantidad_salida__sum']
            valor_ingreso = listado_kardex.aggregate(Sum('valor_ingreso'))
            valor_salida = listado_kardex.aggregate(Sum('valor_salida'))
            t_valor_i = valor_ingreso['valor_ingreso__sum']
            t_valor_s = valor_salida['valor_salida__sum']
        else:
            t_cantidad_i = 0
            t_cantidad_s = 0
            t_valor_i = 0
            t_valor_s = 0
        return listado_kardex, t_cantidad_i, t_valor_i, t_cantidad_s, t_valor_s

def roadmap_stats(request):
    user = request.user
    roadmaps = user.roadmap_set.all()
    stat_dict = stats(roadmaps)

    sum_score = 0
    for roadmap in roadmaps:
        scores = Score.objects.filter(task__roadmap=roadmap)
        points_earned = scores.aggregate(models.Sum('points'))['points__sum']
        if points_earned is None:
            points_earned = 0
        sum_score += points_earned

    sum_tasks = 0
    for roadmap in roadmaps:
        tasks = Task.objects.filter(roadmap=roadmap).count()
        sum_tasks += tasks
    return render(request, 'roadmap/stat.html', {'weeks': stat_dict['weeks'], 'months': stat_dict['months'],
                                                 'sum_score': sum_score, 'sum_tasks': sum_tasks})

def get_message_field_value(
            self,
            obj,
            message_field_name):
        """
            Custom mapping behaviour
        """
        if message_field_name in ("id", "name",):
            return getattr(obj.global_tag, message_field_name)

        if message_field_name == "taginstance_count":
            if hasattr(obj, "taginstance_sum"):
                return long(obj.taginstance_sum)
            else:
                return (
                    obj.videotags
                    .annotate(taginstance_count=Count("tag_instances"))
                    .aggregate(Sum('taginstance_count'))
                )['taginstance_count__sum']

        return super(TagMapper, self).get_message_field_value(
            obj, message_field_name)

def get_queryset(self):
        """Get queryset."""
        return self.model.objects.all() \
            .select_related('publisher') \
            .prefetch_related('authors') \
            .only('id',
                  'title',
                  'pages',
                  'price',
                  'publisher__id',
                  'publisher__name',
                  'authors__id',
                  'authors__name') \
            .annotate(
                price_per_page=Sum(
                    F('price') / F('pages'),
                    output_field=DecimalField(decimal_places=2)
                )
            )

def update_equity(self):
        today, trading = await is_trading_day(datetime.datetime.today().replace(tzinfo=pytz.FixedOffset(480)))
        if trading:
            logger.info('?????? %s %s', today, trading)
            dividend = Performance.objects.filter(
                broker=self.__broker, day__lt=today.date()).aggregate(Sum('dividend'))['dividend__sum']
            if dividend is None:
                dividend = Decimal(0)
            perform = Performance.objects.filter(
                broker=self.__broker, day__lt=today.date()).order_by('-day').first()
            if perform is None:
                unit = Decimal(1000000)
            else:
                unit = perform.unit_count
            nav = self.__current / unit
            accumulated = (self.__current - dividend) / (unit - dividend)
            Performance.objects.update_or_create(broker=self.__broker, day=today.date(), defaults={
                'used_margin': self.__margin,
                'capital': self.__current, 'unit_count': unit, 'NAV': nav, 'accumulated': accumulated})

def balance(self):

        try:
            no_licenses = self.license.records.aggregate(
                models.Sum('number_licenses'))['number_licenses__sum']
        except ObjectDoesNotExist:
            pass

        try:
            paid = self.payments.filter(status='PAID').aggregate(
                models.Sum('amount'))['amount__sum']
        except ObjectDoesNotExist:
            pass

        if not no_licenses:
            no_licenses = 0

        if not paid:
            paid = 0.0

        return (no_licenses * settings.PRICE_PER_LICENSE) - paid

def paginate_queryset(self, queryset, request, view=None):
        self.total_duration = (queryset
                               .aggregate(Sum('duration'))['duration__sum'])

        self.limit = self.get_limit(request)
        if self.limit is None:
            return None

        self.offset = self.get_offset(request)
        self.count = _get_count(queryset)
        self.request = request
        if self.count > self.limit and self.template is not None:
            self.display_page_controls = True

        if self.count == 0 or self.offset > self.count:
            return []

        queryset = queryset[self.offset:self.offset + self.limit]
        self.subtotal_duration = (queryset
                                  .aggregate(Sum('duration'))['duration__sum'])

        return list(queryset)

def create_bid_item_dict(bid_obj):

    bid_item_obj = BidItem.objects.filter(bid=bid_obj.id)

    unique_job_types = set()
    for item in bid_item_obj:
        unique_job_types.add(item.job_type)

    bid_item_dict = {}
    for job in unique_job_types:
        bid_items = bid_item_obj.filter(job_type=job)
        total = bid_items.aggregate(Sum('total'))['total__sum']
        bid_item_dict.setdefault(job, {})
        bid_item_dict[job]['bid_items'] = bid_items
        bid_item_dict[job]['total'] = total

    return bid_item_dict

def get_context_data(self, **kwargs):
        context = super(BidDetail, self).get_context_data(**kwargs)
        bid_obj = Bid.objects.get(id=self.kwargs['pk'])
        bid_item_obj = BidItem.objects.filter(bid=self.kwargs['pk'])
        bid_item_dict = create_bid_item_dict(bid_obj)
        total_cost = bid_item_obj.aggregate(Sum('total'))['total__sum']
        payment_obj = Payment.objects.filter(bid=self.kwargs['pk'])
        total_payments = payment_obj.aggregate(Sum('amount'))['amount__sum']

        context['bid_item_dict'] = bid_item_dict
        context['total_cost'] = total_cost
        context['pdfs'] = PDFImage.objects.all().filter(bid=self.kwargs['pk'])
        context['journal_entries'] = Journal.objects.all().filter(bid=self.kwargs['pk']).order_by('-timestamp')
        context['payments'] = Payment.objects.all().filter(bid=self.kwargs['pk']).order_by('date')
        context['date'] = date.today()

        if total_payments:
            context['remaining_balance'] = total_cost - total_payments
        else:
            context['remaining_balance'] = total_cost

        return context

def get_queryset(self):
        queryset = (models.Event.objects.all()
                    .select_related('calendar')
                    .prefetch_related('tags')
                    .annotate(_participants=Sum(F('rsvps__guests') + 1))
                    )
        if not self.request.user.has_perm('events.view_hidden_event'):
            queryset = queryset.filter(published=True)

        after_query = self.request.query_params.get('after', None)
        before_query = self.request.query_params.get('before', None)
        lookup_url_kwarg = self.lookup_url_kwarg or self.lookup_field

        # in the case there is no after_query parameters, and we are not on a single object page
        # we set a default after value of today
        if lookup_url_kwarg not in self.kwargs and after_query is None and before_query is None:
            queryset = queryset.upcoming(as_of=timezone.now(), published_only=False)

        return queryset

def form_valid(self, form):
        # validate quantity
        line_order = form.instance.line_order
        quantity = form.instance.quantity
        quantity_bd = SalesLineAlbaran.objects.filter(line_order=line_order).aggregate(q=Sum('quantity'))
        units_pending = line_order.quantity
        if quantity_bd['q']:
            quantity += quantity_bd['q']
            units_pending -= quantity_bd['q']

        if line_order.quantity < quantity:
            errors = form._errors.setdefault("quantity", ErrorList())
            errors.append(_("La cantidad seleccionada es excesiva. Quedan pendiente {} unidades por albaranar".format(units_pending)))
            return super(LineAlbaranCreate, self).form_invalid(form)

        # initial albaran
        if self.__pk:
            obj = SalesAlbaran.objects.get(pk=self.__pk)
            self.request.albaran = obj
            form.instance.albaran = obj

        return super(LineAlbaranCreate, self).form_valid(form)

def update_stock(self, commit=True):
        self.stock_real = ProductUnique.objects.filter(product_final=self).aggregate(stock=Sum('stock_real'))['stock']
        if commit:
            self.save()
        return self.stock_real

def sale_money_rank(from_time, to_time, rank_limit=10):
    stat_list = Member.objects.filter(active=True, sale__timestamp__gt=from_time,
                                      sale__timestamp__lte=to_time).annotate(Sum('sale__price')).order_by(
        '-sale__price__sum', 'username')[:rank_limit]
    for member in stat_list:
        member.sale__price__sum__formatted = money(member.sale__price__sum)
    return stat_list


# year of the last fjuleparty

def daily(request):
    current_date = timezone.now().replace(hour=0, minute=0, second=0)
    latest_sales = (Sale.objects
                    .prefetch_related('product', 'member')
                    .order_by('-timestamp')[:7])
    top_today = (Product.objects
                 .filter(sale__timestamp__gt=current_date)
                 .annotate(Count('sale'))
                 .order_by('-sale__count')[:7])

    startTime_day = timezone.now() - datetime.timedelta(hours=24)
    revenue_day = (Sale.objects
                   .filter(timestamp__gt=startTime_day)
                   .aggregate(Sum("price"))
                   ["price__sum"]) or 0.0
    startTime_month = timezone.now() - datetime.timedelta(days=30)
    revenue_month = (Sale.objects
                     .filter(timestamp__gt=startTime_month)
                     .aggregate(Sum("price"))
                     ["price__sum"]) or 0.0
    top_month_category = (Category.objects
                          .filter(product__sale__timestamp__gt=startTime_month)
                          .annotate(sale=Count("product__sale"))
                          .order_by("-sale")[:7])

    return render(request, 'admin/stregsystem/report/daily.html', locals())

def sales_api(request):
    startTime_month = timezone.now() - datetime.timedelta(days=30)
    qs = (Sale.objects
          .filter(timestamp__gt=startTime_month)
          .annotate(day=TruncDay('timestamp'))
          .values('day')
          .annotate(c=Count('*'))
          .annotate(r=Sum('price'))
          )
    db_sales = {i["day"].date(): (i["c"], money(i["r"])) for i in qs}
    base = timezone.now().date()
    date_list = [base - datetime.timedelta(days=x) for x in range(0, 30)]

    sales_list = []
    revenue_list = []
    for date in date_list:
        if date in db_sales:
            sales, revenue = db_sales[date]
            sales_list.append(sales)
            revenue_list.append(revenue)
        else:
            sales_list.append(0)
            revenue_list.append(0)

    items = {
        "day": date_list,
        "sales": sales_list,
        "revenue": revenue_list,
    }
    return JsonResponse(items)

def get_queryset(self):
        score_list = dict()
        if Post.objects.all():
            for i in Post.objects.filter(parent__isnull=True):
                children = Post.objects.filter(super_parent=i).aggregate(Sum("score"))
                score_list[i] = children["score__sum"]
                sorted_score_list = sorted(score_list.items(), key=lambda x: x[1], reverse=True)
        else:
            sorted_score_list = []
        return [(i+1, e, f) for i, (e, f) in enumerate(sorted_score_list)][:10]

def update_subtotal(self):
        subtotal = self.items.all().aggregate(sum=models.Sum('total_price'))
        self.price_subtotal = subtotal['sum']
        self.save()

def get_total_quantity_of_items(self):
        qty = self.items.all().aggregate(sum=models.Sum('quantity'))
        return qty['sum']

def get_queryset(self):
        """Get queryset."""
        return self.model.objects.all() \
            .annotate(
                number_of_books=Count('books'),
                first_book_published_on=Min('books__publication_date'),
                last_book_published_on=Max('books__publication_date'),
                lowest_book_price=Min('books__price'),
                highest_book_price=Max('books__price'),
                average_book_price=Avg('books__price'),
                average_number_of_pages_per_book=Avg('books__pages'),
                number_of_books_sold=Count('books__order_lines'),
                total_amount_earned=Sum('books__order_lines__book__price')
            ) \
            .values('id',
                    'salutation',
                    'name',
                    'email',
                    'number_of_books',
                    'first_book_published_on',
                    'last_book_published_on',
                    'lowest_book_price',
                    'highest_book_price',
                    'average_book_price',
                    'average_number_of_pages_per_book',
                    'number_of_books_sold',
                    'total_amount_earned')

def count_votes(self):
        """
        Returns questions annotated with the number of votes they have.
        """
        return self.annotate(num_votes=Coalesce(models.Sum('votes__vote'), 0))

def count_votes(self):
        """
        Returns questions annotated with the number of votes they have.
        """
        return self.annotate(num_votes=Coalesce(models.Sum('votes__vote'), 0))


##########################################################################
## Answer Manager
##########################################################################

def total(self):
        """
        Returns the sum of the votes
        """
        return self.aggregate(total=models.Sum('vote'))

def balance(self) -> Decimal:
        qs = self.transactions.filter(value_datetime__lte=now())
        liability = qs.filter(source_account__account_category='member_fees').aggregate(liability=models.Sum('amount'))['liability'] or Decimal('0.00')
        asset = qs.filter(destination_account__account_category='member_fees').aggregate(asset=models.Sum('amount'))['asset'] or Decimal('0.00')
        return asset - liability

def total_in(self, start=None, end=now()):
        qs = self.incoming_transactions
        if start:
            qs = qs.filter(value_datetime__gte=start)
        if end:
            qs = qs.filter(value_datetime__lte=end)
        return qs.aggregate(incoming=models.Sum('amount'))['incoming'] or 0

def total_out(self, start=None, end=now()):
        qs = self.outgoing_transactions
        if start:
            qs = qs.filter(value_datetime__gte=start)
        if end:
            qs = qs.filter(value_datetime__lte=end)
        return qs.aggregate(outgoing=models.Sum('amount'))['outgoing'] or 0

def total_amount_refunded(self):
        return self.refund_operations.filter(status='completed').aggregate(Sum('amount'))['amount__sum']

    # Comprueba si un pago ha sido totalmente debuelto y cambia el estado en coherencias.

def count(self):
        lines = self.lines.all()
        return lines.aggregate(total_quantity=models.Sum('quantity'))