class ProductQuerySet(QuerySet):
one_m_weight = ExpressionWrapper(F('density') * F('width') / 100,
output_field=IntegerField())
price_rub_m = ExpressionWrapper(F('dollar_price') * F('dollar_rate') *
F('one_m_weight') / 1000,
output_field=IntegerField())
density_for_count = Case(When(length_for_count__gt=0,
width__gt=0,
then=F('weight_for_count') /
F('length_for_count') / F('width') * 100),
output_field=IntegerField())
meters_in_roll = Case(When(one_m_weight__gt=0,
then=F('weight') * 1000 / F('one_m_weight')),
output_field=DecimalField(decimal_places=2))
def details(self):
return self.select_related("product_type").annotate(
one_m_weight=self.one_m_weight,
price_rub_m=self.price_rub_m,
density_for_count=self.density_for_count,
meters_in_roll=self.meters_in_roll)
def outstanding(self):
''' Return a set of all outstanding bills. '''
# There is a known bug that results in a bill that has multiple line items
# and a partial payment not showing up in this set as it should.
# https://code.djangoproject.com/ticket/10060
# https://github.com/nadineproject/nadine/issues/300
amount_adjustment = (F('bill_amount') *
F('items_distinct')) / F('item_count')
payment_adjustment = (F('payment_amount') *
F('payments_distinct')) / F('payment_count')
outstanding_query = self.filter(mark_paid=False) \
.annotate(bill_amount=Sum('line_items__amount', output_field=DecimalField())) \
.annotate(item_count=Count('line_items')) \
.annotate(items_distinct=Count('line_items', distinct=True)) \
.annotate(payment_amount=Sum('payment__amount', output_field=DecimalField())) \
.annotate(payment_count=Count('payment')) \
.annotate(payments_distinct=Count('payment', distinct=True)) \
.annotate(adjusted_amount=ExpressionWrapper(amount_adjustment, output_field=DecimalField())) \
.annotate(adjusted_payments=ExpressionWrapper(payment_adjustment, output_field=DecimalField())) \
.annotate(owed=F('adjusted_amount') - F('adjusted_payments'))
no_payments = Q(payments_distinct=0)
partial_payment = Q(owed__gt=0)
return outstanding_query.filter(
bill_amount__gt=0).filter(no_payments | partial_payment)
class AmfiMissingView(ListView):
dematsum_qs = DematSum.objects.filter(isin_code=OuterRef("comp_isin"))
weight_qs = Weight.objects.filter(cap_type=OuterRef("cap_type"))
# missing large cap and mid cap : top 250 only
queryset = Amfi.objects.all(). \
annotate(value_cost=Subquery(dematsum_qs.values('value_cost')[:1])). \
annotate(cap_weight=Subquery(weight_qs.values('cap_weight')[:1])). \
annotate(deficit=ExpressionWrapper(F('cap_weight') * 1000 - F('value_cost'), output_field=IntegerField())). \
values('comp_rank', 'comp_name', 'value_cost', 'deficit'). \
filter(value_cost__isnull=True).filter(comp_rank__lte=250).order_by('comp_rank')
def get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
return context
def calc_points(self):
tasks = Task.objects.annotate(
interval=ExpressionWrapper(
F('estimate') - F('create_date'),
output_field=models.DurationField()
)
)
max_estimate = tasks.aggregate(
max_estimate=Max('interval')
).get('max_estimate')
points = (date.today() - self.create_date) / \
(self.estimate - self.create_date) + \
(self.estimate - self.create_date) / \
max_estimate
return points
def ajusta_horas(self, instance):
tasa = instance.hoja.tasa
es_salario_fijo = instance.hoja.tasa.es_salario_fijo
nro_horas_contrato = instance.hoja.colaborador.nro_horas_mes
horas_trabajadas = nro_horas_contrato
if es_salario_fijo:
horas = HoraHojaTrabajo.objects.filter(
hoja__tasa__lapso=instance.hoja.tasa.lapso,
hoja__colaborador=instance.hoja.colaborador
).aggregate(horas=ExpressionWrapper(Sum('cantidad_minutos') / 60, output_field=DecimalField(max_digits=4)))[
'horas']
if horas > nro_horas_contrato:
horas_trabajadas = horas
tasa.nro_horas_mes_trabajadas = horas_trabajadas
tasa.save()
def get_report(self):
plans = models.Plan.objects.exclude(offering__billable=False)
query = self.parse_query()
if query:
plans = self.apply_filters(query, plans)
resources = self.get_subquery()
remaining = ExpressionWrapper(F('limit') - F('usage'),
output_field=PositiveSmallIntegerField())
plans = plans.annotate(usage=Subquery(resources[:1]), limit=F('max_amount'))\
.annotate(remaining=remaining)
plans = self.apply_ordering(plans)
return self.serialize(plans)
def get_queryset(self):
qs = super(LegManager, self).get_queryset()
# Add the annotation `duration_prefix_sum`
# which is the sum of Leg.duration for the other Legs
# of the TimeTrial which has order <= this Leg's order.
qs = qs.filter(timetrial__leg__order__lte=F('order'))
qs = qs.annotate(duration_prefix_sum=Sum('timetrial__leg__duration'))
dur = seconds_to_duration(F('duration_prefix_sum'))
time = ExpressionWrapper(F('timetrial__start_time') + dur,
output_field=DateTimeField())
qs = qs.annotate(time=time)
return qs
def list(cls, domain, date_, location_filters, sort_column):
return Child.objects.filter(
domain=domain,
dob__range=(date_ - relativedelta(years=5), date_),
**location_filters).annotate(age_in_months=ExpressionWrapper(
(Value(date_) - F('dob')) / Value(30.417),
output_field=IntegerField())).extra(
select={
'lastImmunizationType': 'last_immunization_type',
'lastImmunizationDate': 'last_immunization_date',
'gender': 'sex',
'id': 'person_case_id',
}).values('id', 'name', 'dob', 'gender',
'lastImmunizationType', 'lastImmunizationDate',
'age_in_months').order_by(sort_column)
def get_the_user_cart(user):
"""
Helper function to fetch the cart.
:param user: user fetched from request.
:return: True or False with cart products.
"""
cart_products = CartProducts.objects.filter(
user=user, is_active=True).annotate(cost=ExpressionWrapper(
F('quantity') * F('product__cost'), output_field=FloatField()))
payload = {
'result': True,
'data': CartProductSerializer(instance=cart_products, many=True).data
}
return Response(payload, status=status.HTTP_200_OK)
def get_queryset(self):
# consulta cual es el ultimo movimiento de los adhesivos
disponible = AdhesivoMovimiento.objects.values('adhesivo_id').filter(
adhesivo_id=OuterRef('id'),
ultimo=True).annotate(saldo=Coalesce(Sum('saldo'), 0))
# crea una funcion que modifica un valor del modelo antes de enviarlo al fontend
qs = self.queryset.annotate(disponible=Coalesce(
ExpressionWrapper(
Subquery(disponible.values('saldo')),
# especifica el tipo de campo que se va a adjuntar
output_field=IntegerField()),
0))
# [print(x.__dict__) for x in qs.all()]
return qs
def stats(request):
user_role = UserRole.objects.get(user_id=request.user.id)
if user_role.role.type == 'A':
duration = ExpressionWrapper(F('logout_time') - F('login_time'), output_field=fields.DurationField())
stats = UserStat.objects.values('user_id').annotate(duration=Sum(duration)).annotate(
dcount=Count('user_id')).filter(duration__gt=timedelta(seconds=2)).order_by('user_id')
context = {
'stats': stats,
'total_online': UserRole.objects.filter(is_logged_in=True).aggregate(Count('id')),
'online_users': UserRole.objects.filter(is_logged_in=True).values_list('user_id', flat=True),
}
return render(request, 'nl_bms/stats.html', context)
else:
return HttpResponseForbidden()
def test_pivot_aggregate(self):
shirt_sales = ShirtSales.objects.all()
data = ExpressionWrapper(F('units') * F('price'), output_field=DecimalField())
pt = pivot(ShirtSales, 'store__region__name', 'shipped', data, Avg, default=0)
for row in pt:
region_name = row['store__region__name']
for dt in (key for key in row.keys() if key != 'store__region__name'):
spends = [ss.units * ss.price for ss in shirt_sales if force_text(ss.shipped) == force_text(dt) and ss.store.region.name == region_name]
avg = sum(spends) / len(spends) if spends else 0
if django_version[0] < 3:
self.assertAlmostEqual(row[dt], float(avg), places=4)
else:
self.assertAlmostEqual(row[dt], Decimal(avg), places=4)
def get_data(self, user_id):
data = {}
data = AccountLine.objects.filter(
account__partner__user__id=user_id).values(
'pk', 'amount', 'action_type').annotate(
create_date=ExpressionWrapper(Func(F('create_date'),
Value("DD/MM/YYY"),
function='TO_CHAR'),
output_field=CharField()),
payment_type=F('transaction__payment_method'),
name=F('account__name'),
balance=F('account__balance'),
)
print(data)
return list(data)
def economy_charts(request):
top_economical_bowler = Matches.objects.filter(season=2015).annotate(
bowler=F("deliveries__bowler")).values("bowler").annotate(
economy=ExpressionWrapper(Sum("deliveries__total_runs") /
(Count("deliveries__ball") / 6),
output_field=DecimalField())).order_by(
"economy")[:5]
top_economical_bowler = [{
"bowler": query["bowler"],
"economy": round(float(query["economy"]), 2)
} for query in top_economical_bowler]
context = {"top_economical_bowler": list(top_economical_bowler)}
data = dict(context)
json_data = json.dumps(data)
return render(request, "iplapi/economy.html", context={"data": json_data})
def with_total_price(self):
cls = self.__class__
actual_reservation_query = Ticket.objects.filter(
reservation__debt_id=OuterRef('pk')
).order_by().values('reservation_id').annotate(
_total_ticket_price=Sum(
ExpressionWrapper(
F('category__price') * F('count'),
output_field=models.DecimalField()
)
)
).values('_total_ticket_price')
return self.annotate(**{
cls.FACE_VALUE_FIELD: ExpressionWrapper(
Subquery(actual_reservation_query),
output_field=models.DecimalField()
),
# static price takes precedence
cls.TOTAL_PRICE_FIELD: ExpressionWrapper(
Coalesce(F('static_price'), F(cls.FACE_VALUE_FIELD), 0),
output_field=models.DecimalField()
)
})
def get_initial_queryset(self):
print(self.request.GET)
transaction_id = self.request.GET.get('transaction_id')
qs = self.model.objects.filter(transaction_id=transaction_id).annotate(
create_date_str=ExpressionWrapper(Func(F('create_date'),
Value("DD/MM/YYYY"),
function='TO_CHAR'),
output_field=CharField()),
name=F('account__name'),
balance=F('account__balance'),
holder_name=Concat('account__respartner__user__first_name',
Value(' '),
'account__respartner__user__last_name'),
transaction_no=F('transaction__number')).order_by('pk')
return qs
def sort_by_season(date):
"""
Ordonner les appartements en fonction de la saison (date de la requete)
"""
month = date.month
if month >= 6 and month <= 9:
# ete
queryset = Appartement.objects.annotate(
piscine=ExpressionWrapper(Q(caracteristiques__nom='piscine'),
output_field=BooleanField())).order_by(
'-piscine', '-prix', '-surface')
elif month == 12 or month <= 3:
# hiver
queryset = Appartement.objects.annotate(
ski=ExpressionWrapper(Q(caracteristiques__nom='station ski'),
output_field=BooleanField())).order_by(
'-ski', '-prix', '-surface')
else:
# reste
queryset = Appartement.objects.all().order_by('-prix', '-surface')
return queryset
def checker_beat():
services_to_check = WebService.objects.annotate(
diff_date=ExpressionWrapper(F('last_check') +
(timezone.timedelta(minutes=1) *
F('minutes')),
output_field=DateTimeField())).filter(
Q(diff_date__lte=timezone.now())
| Q(last_check__isnull=True),
is_active=True)
services_to_check_count = services_to_check.count()
for service in services_to_check:
check_uri.delay(service.id, service.url)
services_to_check.update(last_check=timezone.now())
return "This task send {} Services".format(services_to_check_count)
def _populate_storage_daily_summary_table(self):
"""Populate the daily summary table."""
included_fields = [
'usage_start', 'usage_end', 'namespace', 'pod', 'node',
'persistentvolumeclaim', 'persistentvolume', 'storageclass',
'cluster_id', 'cluster_alias'
]
annotations = {
'volume_labels':
Coalesce(F('persistentvolume_labels'),
F('persistentvolumeclaim_labels')),
'persistentvolumeclaim_capacity_gigabyte':
ExpressionWrapper(F('persistentvolumeclaim_capacity_bytes') *
math.pow(2, -30),
output_field=DecimalField()),
'persistentvolumeclaim_capacity_gigabyte_months':
ExpressionWrapper(
F('persistentvolumeclaim_capacity_byte_seconds') / 86400 * 30 *
math.pow(2, -30),
output_field=DecimalField()),
'volume_request_storage_gigabyte_months':
ExpressionWrapper(F('volume_request_storage_byte_seconds') /
86400 * 30 * math.pow(2, -30),
output_field=DecimalField()),
'persistentvolumeclaim_usage_gigabyte_months':
ExpressionWrapper(F('persistentvolumeclaim_usage_byte_seconds') /
86400 * 30 * math.pow(2, -30),
output_field=DecimalField())
}
entries = OCPStorageLineItemDaily.objects.values(
*included_fields).annotate(**annotations)
for entry in entries:
summary = OCPStorageLineItemDailySummary(**entry)
summary.save()
def get_context_data(self, **kwargs):
filter = AdhesionFilter(self.request.GET)
qs = filter.qs.values('rate__name', 'rate__rate')
qs = qs.annotate(headcount=Count('id'))
qs = qs.annotate(amount=ExpressionWrapper(
Count('id') * F('rate__rate'), output_field=DecimalField()))
qs = qs.order_by('-amount')
context = {
'filter': filter,
'date': filter.date.strftime('%d/%m/%Y'),
'data': qs,
'total_headcount': sum([obj['headcount'] or 0 for obj in qs]),
'total_amount': sum([obj['amount'] or 0 for obj in qs]),
}
return context
def page_loads_by_type(request, load_type='All'):
step_hit_count_by_page(request.path)
load_type = load_type.lower()
if load_type == 'ocw':
load_type = 'ocw'
all_loads = HandLoad.objects.all().filter(
Q(Is_Sheriff_Load=False) | Q(Is_Sheriff_Load=False)).order_by(
'Is_Sheriff_Load', '-prod', '-projectile__Diameter').annotate(
prod=ExpressionWrapper(F('projectile__WeightGR') * 0.5 /
7000 / 32.127 * F('Velocity') *
F('Velocity'),
output_field=FloatField()),
rps=ExpressionWrapper(F('Velocity') * 720 /
F('firearm__inches_per_twist') / 60,
output_field=IntegerField()))
elif load_type == 'sheriff':
load_type = 'sheriff'
all_loads = HandLoad.objects.all().filter(
Q(Is_Sheriff_Load=True) | Q(Is_Sheriff_Load=True)).order_by(
'Is_Sheriff_Load', '-prod', '-projectile__Diameter').annotate(
prod=ExpressionWrapper(F('projectile__WeightGR') * 0.5 /
7000 / 32.127 * F('Velocity') *
F('Velocity'),
output_field=FloatField()),
rps=ExpressionWrapper(F('Velocity') * 720 /
F('firearm__inches_per_twist') / 60,
output_field=IntegerField()))
else:
load_type = 'all'
all_loads = HandLoad.objects.all().filter(
Q(Is_Sheriff_Load=True) | Q(Is_Sheriff_Load=False)).order_by(
'Is_Sheriff_Load', '-prod', '-projectile__Diameter').annotate(
prod=ExpressionWrapper(F('projectile__WeightGR') * 0.5 /
7000 / 32.127 * F('Velocity') *
F('Velocity'),
output_field=FloatField()),
rps=ExpressionWrapper(F('Velocity') * 720 /
F('firearm__inches_per_twist') / 60,
output_field=IntegerField()))
# there's probably a better way to do this but, this works for now.
for load in all_loads:
load.has_info = False
has_dope = EstimatedDope.objects.all().filter(Q(hand_load=load.pk))
if has_dope:
load.has_info = True
context = {
# "restart": get_restart_notice,
'release': get_version_json(),
"title": "Load Data: %s" % load_type,
"blurb": get_page_blurb_override('load_data/loads/'),
'all_loads': all_loads,
"copy_year": datetime.now().year
}
return render(request, "loaddata/djangoad.html", context)
def get_queryset(self):
default = Value('-')
output_field=models.CharField()
#**{f'{process}_remain2': case for process in work_field_names}
process_total_exp = reduce(operator.add, (Least(F(f'{process}_total'), F('quantity')) for process in work_field_names))
process_count_exp = reduce(operator.add, (F(f'product__type__{process}') for process in work_field_names))
qs = super().get_queryset().prefetch_related(
#
).select_related(
'product__type',
'product',
'product__type__category', # very good
'order',
'order__batch', # speeds up item listing
'order__company' # speeds up item listing
).annotate(
process_total = process_total_exp
).annotate(
process_count = ExpressionWrapper(process_count_exp, output_field=models.IntegerField())
).annotate(
**{f'{process}_remain2': Case(
When(Q(**{f'product__type__{process}':True}), then=Greatest(F('quantity')-F(f'{process}_total'),Value(0))),
default=default,
output_field=output_field) for process in work_field_names}
#old **{f'{process}_remain2':ExpressionWrapper((F('quantity')-F(f'{process}_total'))*F(f'product__type__{process}'),output_field=models.IntegerField())
# for process in work_field_names}
).annotate(
progress2=ExpressionWrapper(F('process_total') * Decimal('100.0')/F('process_count')/F('quantity'),
output_field=models.DecimalField(0))# FloatField())
).annotate(
#cannot see in the order annotation, but can see on general queries
#category = ExpressionWrapper(F('product__type__category__title'), output_field=models.CharField())
)
html = format_html('<span style="color: #{};">{}</span>', '008000', 'ggg item green')
qs = qs.annotate(ggg = models.Value(html, output_field=models.TextField())) # testing
return qs
def test_grouping_by_q_expression_annotation(self):
authors = Author.objects.annotate(under_40=ExpressionWrapper(
Q(age__lt=40),
output_field=BooleanField()), ).values('under_40').annotate(
count_id=Count('id'), ).values('under_40', 'count_id')
self.assertCountEqual(authors, [
{
'under_40': False,
'count_id': 3
},
{
'under_40': True,
'count_id': 6
},
])
def home(request):
student = Student.objects.all()
subject = Subject.objects.all()
sem_score = Enrollment.objects.all().update(
sem_score=ExpressionWrapper(
(F("prelim_score") + F("midterm_score") + F("final_score"))/3,
output_field=IntegerField()))
enrollment = Enrollment.objects.all()
num_student = Enrollment.objects.all().count()
context = {"student":student,
"subject":subject,
"enrollment":enrollment,
"num_student":num_student
}
return render(request, 'core/home.html', context)
def comparison_to_q(self, a, b, *args, operator, negate=False, modifier=None):
a = self.operation_to_expression(a)
b = self.operation_to_expression(b)
if modifier:
b = modifier(b, *args)
q = Q(
ExpressionWrapper(
operator(
a,
b,
),
output_field=BooleanField()
)
)
return ~q if negate else q
class DematTxnStatBuySellView(ListView):
model = DematTxn
# if pagination is desired
# paginate_by = 300
txn_amount = ExpressionWrapper(F('quantity') * F('txn_price'),
output_field=IntegerField())
queryset = DematTxn.objects.all(). \
annotate(txn_year=ExtractYear('txn_date')). \
values('txn_year', 'action').annotate(txn_amount=Round(Sum(txn_amount))). \
order_by('txn_year')
def get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
return context
def clean(self):
# 校验游戏时间是否符合逻辑
cur_id = self.initial.get('id', 0)
play_time = self.cleaned_data.get('each_time') * self.cleaned_data.get('rounds')
cur_start = self.cleaned_data.get('start_time')
cur_end = self.cleaned_data.get('start_time') + timedelta(seconds=play_time)
reward = self.cleaned_data.get('reward')
cur_is_active = self.cleaned_data.get('is_active')
# 校验游戏是否已经结束
if Game.objects.filter(Q(id=cur_id) & Q(status=Game.OVER)):
raise forms.ValidationError('当前游戏已结束，无法修改相关数据！')
elif Game.objects.filter(Q(id=cur_id) & Q(status=Game.PROCESSING)):
raise forms.ValidationError('当前游戏正在进行中，无法修改相关数据！')
# 赏金不小于0元
if reward < 0:
raise forms.ValidationError('赏金不能低于0元！')
# 校验当前时间和游戏开始时间是否大于 2 分钟
now = datetime.now()
tz = pytz.timezone('Asia/Shanghai')
now = tz.localize(now)
if cur_start < now or cur_end < now:
raise forms.ValidationError('游戏开始和结束时间必须大于当前时间')
if (cur_start - now).seconds < 2 * 60:
raise forms.ValidationError('游戏开始时间必须与当前时间间隔至少 2 分钟')
# 获取数据库中存在的活动时间
games = Game.objects.filter(~Q(id=cur_id), ~Q(status=Game.OVER)).annotate(
end_time=ExpressionWrapper(F('each_time') * F('rounds'),
output_field=IntegerField())).values_list('start_time',
'end_time',
'is_active')
for start_time, end_time, is_active in games:
if max(start_time, cur_start) < min(start_time + timedelta(seconds=end_time), cur_end):
raise forms.ValidationError('当前游戏时间与其他游戏时间有冲突！')
if is_active and cur_is_active:
raise forms.ValidationError('只能同时激活一个游戏实例！')
question_num = int(self.data['gamequestion_set-TOTAL_FORMS'])
if question_num <= 0 or int(self.data["rounds"]) != question_num:
raise forms.ValidationError('回合数和问题数量需相等并且至少一个回合！')
for q in range(question_num):
title = "gamequestion_set-{}-question".format(q)
if not self.data[title]:
raise forms.ValidationError("题目为必填项！")
def get(self, request):
saved_order = Order.objects.get(user=request.user, is_closed=False)
cart = saved_order.cart_set.all()
saved_cart = [{
'name': prop.product.name,
'price': prop.product.price,
'small_image': prop.product.small_image,
'quantity': prop.quantity
} for prop in cart]
total_quantity = sum(item['quantity'] for item in saved_cart)
total_price = Cart.objects.annotate(
price=ExpressionWrapper(F('quantity') * F('product__price'),
output_field=DecimalField(10, 2)))
base = 0
for each_p in total_price:
base += each_p.price
saved_order.total_price = base + saved_order.package_type.price
saved_order.save()
shipping_address = request.user.user_address_set.get(
address_id__is_default=True)
res = [
saved_cart, {
"total_quantity": total_quantity
}, {
"total_price": saved_order.total_price
}, {
"address1": shipping_address.address.address1
}, {
"address2": shipping_address.address.address2
}, {
"city": shipping_address.address.city
}, {
"state": shipping_address.address.state
}, {
"postcode": shipping_address.address.postcode.postcode
}, {
"country": shipping_address.address.country
}, {
"shipping_cost":
shipping_address.address.postcode.shipping_cost
}
]
return JsonResponse({'cart': res}, status=200)
def cart_to_order(self): # save products from cart to order
cart, jc = self.get_cart()
order_items = []
self.del_old_reserves()
if cart:
with transaction.atomic():
order = Order.objects.create(user=self)
for obj in cart:
res_quantity, res_item = self.check_reserved(
obj.product.code)
if res_quantity:
if res_quantity >= obj.quantity:
res_item.quantity -= obj.quantity
res_item.save()
obj.product.reserved -= obj.quantity
obj.product.save()
else:
obj.product.reserved -= res_quantity
res_item.delete()
if (obj.product.qu_in_stock - obj.product.reserved -
obj.quantity) >= 0:
order_items.append(
Order_items(order=order,
code=obj.product.code,
name=obj.product.name,
quantity=obj.quantity,
price=obj.product.price,
discount=obj.product.discount))
obj.product.qu_in_stock -= obj.quantity
obj.product.save()
else:
raise ValueError(
f"Not enough quantity of product {obj.product} "
f"in stock. You should to choose a smaller quantity!"
)
Order_items.objects.bulk_create(order_items)
cart_wt = Order_items.objects.filter(order=order).aggregate(
cart_total=ExpressionWrapper(
Sum(F('price') * F('quantity')),
output_field=models.DecimalField()))
order.total_cost = cart_wt['cart_total']
order.save()
cart.delete()
order_val = Order.objects.values().get(id=order.id)
items = list(order.order_items_set.values())
result = {'order': order_val, 'order_items': items}
return result
def get_balance(group_account_id, user_profile_id):
from care.transaction.models import Transaction
from care.transaction.models import TransactionReal
buyer_transactions = Transaction.objects.filter(
group_account__id=group_account_id, buyer__id=user_profile_id)
consumer_transactions = Transaction.objects.annotate(
amount_per_person=ExpressionWrapper(
F('amount') / (1.0 * Count('consumers')),
output_field=models.FloatField())).filter(
group_account__id=group_account_id,
consumers__id=user_profile_id).prefetch_related(
'consumers')
sender_real_transactions = TransactionReal.objects.filter(
group_account__id=group_account_id, sender__id=user_profile_id)
receiver_real_transactions = TransactionReal.objects.filter(
group_account_id=group_account_id, receiver__id=user_profile_id)
total_bought = 0.0
total_consumed = 0.0
total_sent = 0.0
total_received = 0.0
amount__sum = buyer_transactions.aggregate(
Sum('amount'))['amount__sum']
if amount__sum:
total_bought = float(amount__sum)
amount__sum = sender_real_transactions.aggregate(
Sum('amount'))['amount__sum']
if amount__sum:
total_sent = float(amount__sum)
amount__sum = receiver_real_transactions.aggregate(
Sum('amount'))['amount__sum']
if amount__sum:
total_received = float(amount__sum)
amount_per_person__sum = consumer_transactions.aggregate(
Sum('amount_per_person'))['amount_per_person__sum']
if amount_per_person__sum:
total_consumed = float(amount_per_person__sum)
balance = (total_bought + total_sent - total_consumed - total_received)
return balance
