def award_financial_derivations(derived_fields):
derived_fields["recipient_parent_name"] = Case(
When(
award__latest_transaction__type__in=list(
contract_type_mapping.keys()),
then=
"award__latest_transaction__contract_data__ultimate_parent_legal_enti",
),
default=
"award__latest_transaction__assistance_data__ultimate_parent_legal_enti",
output_field=CharField(),
)
derived_fields["award_type_code"] = Coalesce(
"award__latest_transaction__contract_data__contract_award_type",
"award__latest_transaction__assistance_data__assistance_type",
)
derived_fields["award_type"] = Coalesce(
"award__latest_transaction__contract_data__contract_award_type_desc",
"award__latest_transaction__assistance_data__assistance_type_desc",
)
derived_fields["awarding_agency_code"] = Coalesce(
"award__latest_transaction__contract_data__awarding_agency_code",
"award__latest_transaction__assistance_data__awarding_agency_code",
)
derived_fields["awarding_agency_name"] = Coalesce(
"award__latest_transaction__contract_data__awarding_agency_name",
"award__latest_transaction__assistance_data__awarding_agency_name",
)
derived_fields["awarding_subagency_code"] = Coalesce(
"award__latest_transaction__contract_data__awarding_sub_tier_agency_c",
"award__latest_transaction__assistance_data__awarding_sub_tier_agency_c",
)
derived_fields["awarding_subagency_name"] = Coalesce(
"award__latest_transaction__contract_data__awarding_sub_tier_agency_n",
"award__latest_transaction__assistance_data__awarding_sub_tier_agency_n",
)
derived_fields["awarding_office_code"] = Coalesce(
"award__latest_transaction__contract_data__awarding_office_code",
"award__latest_transaction__assistance_data__awarding_office_code",
)
derived_fields["awarding_office_name"] = Coalesce(
"award__latest_transaction__contract_data__awarding_office_name",
"award__latest_transaction__assistance_data__awarding_office_name",
)
derived_fields["funding_agency_code"] = Coalesce(
"award__latest_transaction__contract_data__funding_agency_code",
"award__latest_transaction__assistance_data__funding_agency_code",
)
derived_fields["funding_agency_name"] = Coalesce(
"award__latest_transaction__contract_data__funding_agency_name",
"award__latest_transaction__assistance_data__funding_agency_name",
)
derived_fields["funding_sub_agency_code"] = Coalesce(
"award__latest_transaction__contract_data__funding_sub_tier_agency_co",
"award__latest_transaction__assistance_data__funding_sub_tier_agency_co",
)
derived_fields["funding_sub_agency_name"] = Coalesce(
"award__latest_transaction__contract_data__funding_sub_tier_agency_na",
"award__latest_transaction__assistance_data__funding_sub_tier_agency_na",
)
derived_fields["funding_office_code"] = Coalesce(
"award__latest_transaction__contract_data__funding_office_code",
"award__latest_transaction__assistance_data__funding_office_code",
)
derived_fields["funding_office_name"] = Coalesce(
"award__latest_transaction__contract_data__funding_office_name",
"award__latest_transaction__assistance_data__funding_office_name",
)
derived_fields["recipient_duns"] = Coalesce(
"award__latest_transaction__contract_data__awardee_or_recipient_uniqu",
"award__latest_transaction__assistance_data__awardee_or_recipient_uniqu",
)
derived_fields["recipient_name"] = Coalesce(
"award__latest_transaction__contract_data__awardee_or_recipient_legal",
"award__latest_transaction__assistance_data__awardee_or_recipient_legal",
)
derived_fields["recipient_parent_duns"] = Coalesce(
"award__latest_transaction__contract_data__ultimate_parent_unique_ide",
"award__latest_transaction__assistance_data__ultimate_parent_unique_ide",
)
derived_fields["recipient_parent_name"] = Coalesce(
"award__latest_transaction__contract_data__ultimate_parent_legal_enti",
"award__latest_transaction__assistance_data__ultimate_parent_legal_enti",
)
derived_fields["recipient_country"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_country_code",
"award__latest_transaction__assistance_data__legal_entity_country_code",
)
derived_fields["recipient_state"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_state_code",
"award__latest_transaction__assistance_data__legal_entity_state_code",
)
derived_fields["recipient_county"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_county_name",
"award__latest_transaction__assistance_data__legal_entity_county_name",
)
derived_fields["recipient_city"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_city_name",
"award__latest_transaction__assistance_data__legal_entity_city_name",
)
derived_fields["recipient_congressional_district"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_congressional",
"award__latest_transaction__assistance_data__legal_entity_congressional",
)
derived_fields["recipient_zip_code"] = Coalesce(
"award__latest_transaction__contract_data__legal_entity_zip4",
Concat(
"award__latest_transaction__assistance_data__legal_entity_zip5",
"award__latest_transaction__assistance_data__legal_entity_zip_last4",
),
)
derived_fields["primary_place_of_performance_country"] = Coalesce(
"award__latest_transaction__contract_data__place_of_perf_country_desc",
"award__latest_transaction__assistance_data__place_of_perform_country_n",
)
derived_fields["primary_place_of_performance_state"] = Coalesce(
"award__latest_transaction__contract_data__place_of_perfor_state_desc",
"award__latest_transaction__assistance_data__place_of_perform_state_nam",
)
derived_fields["primary_place_of_performance_county"] = Coalesce(
"award__latest_transaction__contract_data__place_of_perform_county_na",
"award__latest_transaction__assistance_data__place_of_perform_county_na",
)
derived_fields["primary_place_of_performance_congressional_district"] = Coalesce(
"award__latest_transaction__contract_data__place_of_performance_congr",
"award__latest_transaction__assistance_data__place_of_performance_congr",
)
derived_fields["primary_place_of_performance_zip_code"] = Coalesce(
"award__latest_transaction__contract_data__place_of_performance_zip4a",
"award__latest_transaction__assistance_data__place_of_performance_zip4a",
)
derived_fields["award_base_action_date_fiscal_year"] = FiscalYear(
"award__date_signed")
derived_fields["usaspending_permalink"] = Case(
When(
**{
"award__generated_unique_award_id__isnull":
False,
"then":
Concat(
Value(AWARD_URL),
Func(F("award__generated_unique_award_id"),
function="urlencode"), Value("/")),
}),
default=Value(""),
output_field=CharField(),
)
return derived_fields
def avg_touchdowns_conceded(self):
as_home_agg = self.home_matches.aggregate(
avg_td=Coalesce(Avg("away_touchdowns"), V(0)))
as_away_agg = self.away_matches.aggregate(
avg_td=Coalesce(Avg("home_touchdowns"), V(0)))
return round(as_home_agg["avg_td"] + as_away_agg["avg_td"], 2)
def get_available_seat_count(_id):
total_count = Booking.objects.filter(show__id=_id, ).aggregate(
total_count=Coalesce(Sum('seat_selected'), V(0)))
return total_count.get('total_count')
def __init__(self, provider, report_type):
"""Constructor."""
self._mapping = [
{
'provider': 'OCP',
'annotations': {
'cluster': 'cluster_id',
'project': 'namespace'
},
'end_date': 'usage_end',
'filters': {
'project': {
'field': 'namespace',
'operation': 'icontains'
},
'cluster': [{
'field': 'cluster_alias',
'operation': 'icontains',
'composition_key': 'cluster_filter'
}, {
'field': 'cluster_id',
'operation': 'icontains',
'composition_key': 'cluster_filter'
}],
'pod': {
'field': 'pod',
'operation': 'icontains'
},
'node': {
'field': 'node',
'operation': 'icontains'
},
'infrastructures': {
'field': 'cluster_id',
'operation': 'exact',
'custom':
ProviderAccessor('OCP').infrastructure_key_list
},
},
'group_by_options': ['cluster', 'project', 'node'],
'tag_column': 'pod_labels',
'report_type': {
'costs': {
'tables': {
'query': CostSummary
},
'aggregates': {
'cost':
Sum(
Coalesce(F('pod_charge_cpu_core_hours'),
Value(0, output_field=DecimalField()))
+ Coalesce(
F('pod_charge_memory_gigabyte_hours'),
Value(0, output_field=DecimalField())) +
Coalesce(
F('persistentvolumeclaim_charge_gb_month'),
Value(0, output_field=DecimalField())) +
Coalesce(F('infra_cost'),
Value(0,
output_field=DecimalField())) +
Coalesce(F('markup_cost'),
Value(0, output_field=DecimalField()))
),
'infrastructure_cost':
Sum(F('infra_cost')),
'derived_cost':
Sum(
ExpressionWrapper(
F('pod_charge_cpu_core_hours') +
F('pod_charge_memory_gigabyte_hours') +
F('persistentvolumeclaim_charge_gb_month'),
output_field=DecimalField())),
'markup_cost':
Sum(
Coalesce(F('markup_cost'),
Value(0,
output_field=DecimalField()))),
},
'default_ordering': {
'cost': 'desc'
},
'annotations': {
'cost':
Sum(
Coalesce(F('pod_charge_cpu_core_hours'),
Value(0, output_field=DecimalField()))
+ Coalesce(
F('pod_charge_memory_gigabyte_hours'),
Value(0, output_field=DecimalField())) +
Coalesce(
F('persistentvolumeclaim_charge_gb_month'),
Value(0, output_field=DecimalField())) +
Coalesce(F('infra_cost'),
Value(0,
output_field=DecimalField())) +
Coalesce(F('markup_cost'),
Value(0, output_field=DecimalField()))
),
'infrastructure_cost':
Sum(F('infra_cost')),
'derived_cost':
Sum(
ExpressionWrapper(
F('pod_charge_cpu_core_hours') +
F('pod_charge_memory_gigabyte_hours') +
F('persistentvolumeclaim_charge_gb_month'),
output_field=DecimalField())),
'markup_cost':
Sum(
Coalesce(F('markup_cost'),
Value(0,
output_field=DecimalField()))),
'cost_units':
Value('USD', output_field=CharField())
},
'capacity_aggregate': {},
'delta_key': {
'cost':
Sum(
Coalesce(F('pod_charge_cpu_core_hours'),
Value(0, output_field=DecimalField()))
+ Coalesce(
F('pod_charge_memory_gigabyte_hours'),
Value(0, output_field=DecimalField())) +
Coalesce(
F('persistentvolumeclaim_charge_gb_month'),
Value(0, output_field=DecimalField())) +
Coalesce(F('infra_cost'),
Value(0,
output_field=DecimalField())) +
Coalesce(F('markup_cost'),
Value(0, output_field=DecimalField()))
),
},
'filter': [{}],
'cost_units_key':
'USD',
'sum_columns': [
'cost', 'infrastructure_cost', 'derived_cost',
'markup_cost'
],
},
'costs_by_project': {
'tables': {
'query': CostSummary
},
'aggregates': {
'cost':
Sum(
Coalesce(F('pod_charge_cpu_core_hours'),
Value(0, output_field=DecimalField()))
+ Coalesce(
F('pod_charge_memory_gigabyte_hours'),
Value(0, output_field=DecimalField())) +
Coalesce(
F('persistentvolumeclaim_charge_gb_month'),
Value(0, output_field=DecimalField())) +
Coalesce(F('project_infra_cost'),
Value(0,
output_field=DecimalField())) +
Coalesce(F('project_markup_cost'),
Value(0, output_field=DecimalField()))
),
'infrastructure_cost':
Sum(F('project_infra_cost')),
'derived_cost':
Sum(
F('pod_charge_cpu_core_hours') +
F('pod_charge_memory_gigabyte_hours')),
'markup_cost':
Sum(
Coalesce(F('project_markup_cost'),
Value(0,
output_field=DecimalField()))),
},
'default_ordering': {
'cost': 'desc'
},
'annotations': {
'cost':
Sum(
Coalesce(F('pod_charge_cpu_core_hours'),
Value(0, output_field=DecimalField()))
+ Coalesce(
F('pod_charge_memory_gigabyte_hours'),
Value(0, output_field=DecimalField())) +
Coalesce(
F('persistentvolumeclaim_charge_gb_month'),
Value(0, output_field=DecimalField())) +
Coalesce(F('project_infra_cost'),
Value(0,
output_field=DecimalField())) +
Coalesce(F('project_markup_cost'),
Value(0, output_field=DecimalField()))
),
'infrastructure_cost':
Sum(F('project_infra_cost')),
'derived_cost':
Sum(
F('pod_charge_cpu_core_hours') +
F('pod_charge_memory_gigabyte_hours')),
'markup_cost':
Sum(
Coalesce(F('project_markup_cost'),
Value(0,
output_field=DecimalField()))),
'cost_units':
Value('USD', output_field=CharField())
},
'capacity_aggregate': {},
'delta_key': {
'cost':
Sum(
Coalesce(F('pod_charge_cpu_core_hours'),
Value(0, output_field=DecimalField()))
+ Coalesce(
F('pod_charge_memory_gigabyte_hours'),
Value(0, output_field=DecimalField())) +
Coalesce(
F('persistentvolumeclaim_charge_gb_month'),
Value(0, output_field=DecimalField())) +
Coalesce(F('project_infra_cost'),
Value(0,
output_field=DecimalField())) +
Coalesce(F('project_markup_cost'),
Value(0, output_field=DecimalField()))
),
},
'filter': [{}],
'cost_units_key':
'USD',
'sum_columns': [
'cost', 'infrastructure_cost', 'derived_cost',
'markup_cost'
],
},
'cpu': {
'aggregates': {
'cost':
Sum('pod_charge_cpu_core_hours'),
'usage':
Sum('pod_usage_cpu_core_hours'),
'request':
Sum('pod_request_cpu_core_hours'),
'limit':
Sum('pod_limit_cpu_core_hours'),
'infrastructure_cost':
Sum(Value(0, output_field=DecimalField())),
'derived_cost':
Sum('pod_charge_cpu_core_hours'),
},
'capacity_aggregate': {
'capacity': Max('cluster_capacity_cpu_core_hours')
},
'default_ordering': {
'usage': 'desc'
},
'annotations': {
'usage':
Sum('pod_usage_cpu_core_hours'),
'request':
Sum('pod_request_cpu_core_hours'),
'limit':
Sum('pod_limit_cpu_core_hours'),
'capacity': {
'total': Max('total_capacity_cpu_core_hours'),
'cluster':
Max('cluster_capacity_cpu_core_hours'),
},
'cost':
Sum('pod_charge_cpu_core_hours'),
'infrastructure_cost':
Value(0, output_field=DecimalField()),
'derived_cost':
Sum('pod_charge_cpu_core_hours'),
'cost_units':
Value('USD', output_field=CharField()),
'usage_units':
Value('Core-Hours', output_field=CharField())
},
'delta_key': {
'usage': Sum('pod_usage_cpu_core_hours'),
'request': Sum('pod_request_cpu_core_hours'),
'cost': Sum('pod_charge_cpu_core_hours')
},
'filter': [{}],
'cost_units_key':
'USD',
'usage_units_key':
'Core-Hours',
'sum_columns': [
'usage', 'request', 'limit', 'infrastructure_cost',
'derived_cost', 'cost'
],
},
'memory': {
'aggregates': {
'usage':
Sum('pod_usage_memory_gigabyte_hours'),
'request':
Sum('pod_request_memory_gigabyte_hours'),
'limit':
Sum('pod_limit_memory_gigabyte_hours'),
'cost':
Sum('pod_charge_memory_gigabyte_hours'),
'infrastructure_cost':
Sum(Value(0, output_field=DecimalField())),
'derived_cost':
Sum('pod_charge_memory_gigabyte_hours'),
},
'capacity_aggregate': {
'capacity':
Max('cluster_capacity_memory_gigabyte_hours')
},
'default_ordering': {
'usage': 'desc'
},
'annotations': {
'usage':
Sum('pod_usage_memory_gigabyte_hours'),
'request':
Sum('pod_request_memory_gigabyte_hours'),
'limit':
Sum('pod_limit_memory_gigabyte_hours'),
'capacity': {
'total':
Max('total_capacity_memory_gigabyte_hours'),
'cluster':
Max('cluster_capacity_memory_gigabyte_hours'),
},
'cost':
Sum('pod_charge_memory_gigabyte_hours'),
'infrastructure_cost':
Value(0, output_field=DecimalField()),
'derived_cost':
Sum('pod_charge_memory_gigabyte_hours'),
'cost_units':
Value('USD', output_field=CharField()),
'usage_units':
Value('GB-Hours', output_field=CharField())
},
'delta_key': {
'usage': Sum('pod_usage_memory_gigabyte_hours'),
'request':
Sum('pod_request_memory_gigabyte_hours'),
'cost': Sum('pod_charge_memory_gigabyte_hours')
},
'filter': [{}],
'cost_units_key':
'USD',
'usage_units_key':
'GB-Hours',
'sum_columns': [
'usage', 'request', 'limit', 'infrastructure_cost',
'derived_cost', 'cost'
],
},
'volume': {
'tables': {
'query': OCPStorageLineItemDailySummary
},
'tag_column':
'volume_labels',
'aggregates': {
'usage':
Sum('persistentvolumeclaim_usage_gigabyte_months'),
'request':
Sum('volume_request_storage_gigabyte_months'),
'cost':
Sum('persistentvolumeclaim_charge_gb_month'),
'infrastructure_cost':
Sum(Value(0, output_field=DecimalField())),
'derived_cost':
Sum('persistentvolumeclaim_charge_gb_month'),
},
'capacity_aggregate': {
'capacity':
Sum('persistentvolumeclaim_capacity_gigabyte_months'
)
},
'default_ordering': {
'usage': 'desc'
},
'annotations': {
'usage':
Sum('persistentvolumeclaim_usage_gigabyte_months'),
'request':
Sum('volume_request_storage_gigabyte_months'),
'capacity': {
'total':
Sum('persistentvolumeclaim_capacity_gigabyte_months'
),
'cluster':
Sum('persistentvolumeclaim_capacity_gigabyte_months'
),
},
'cost':
Sum('persistentvolumeclaim_charge_gb_month'),
'infrastructure_cost':
Value(0, output_field=DecimalField()),
'derived_cost':
Sum('persistentvolumeclaim_charge_gb_month'),
'cost_units':
Value('USD', output_field=CharField()),
'usage_units':
Value('GB-Mo', output_field=CharField()),
},
'delta_key': {
'usage':
Sum('persistentvolumeclaim_usage_gigabyte_months'),
'request':
Sum('volume_request_storage_gigabyte_months'),
'cost':
Sum('persistentvolumeclaim_charge_gb_month')
},
'filter': [{}],
'cost_units_key':
'USD',
'usage_units_key':
'GB-Mo',
'sum_columns': [
'usage', 'request', 'infrastructure_cost',
'derived_cost', 'cost'
],
},
},
'start_date': 'usage_start',
'tables': {
'query': OCPUsageLineItemDailySummary,
},
},
]
super().__init__(provider, report_type)
def get_round_count(self):
return self.round_set.count() + Participation.objects.filter(
player__name=self.name).aggregate(
victories=Coalesce(models.Sum('victories'),
models.expressions.Value(0)))['victories']
def annotate_with_numbers(qs, event):
"""
Modifies a queryset of checkin lists by annotating it with the number of order positions and
checkins associated with it.
"""
# Import here to prevent circular import
from . import Order, OrderPosition, Item
# This is the mother of all subqueries. Sorry. I try to explain it, at least?
# First, we prepare a subquery that for every check-in that belongs to a paid-order
# position and to the list in question. Then, we check that it also belongs to the
# correct subevent (just to be sure) and aggregate over lists (so, over everything,
# since we filtered by lists).
cqs_paid = Checkin.objects.filter(
position__order__event=event,
position__order__status=Order.STATUS_PAID,
list=OuterRef('pk')
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(position__subevent=OuterRef('subevent'))
| (Q(position__subevent__isnull=True))).order_by().values(
'list').annotate(c=Count('*')).values('c')
cqs_paid_and_pending = Checkin.objects.filter(
position__order__event=event,
position__order__status__in=[
Order.STATUS_PAID, Order.STATUS_PENDING
],
list=OuterRef('pk')
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(position__subevent=OuterRef('subevent'))
| (Q(position__subevent__isnull=True))).order_by().values(
'list').annotate(c=Count('*')).values('c')
# Now for the hard part: getting all order positions that contribute to this list. This
# requires us to use TWO subqueries. The first one, pqs_all, will only be used for check-in
# lists that contain all the products of the event. This is the simpler one, it basically
# looks like the check-in counter above.
pqs_all_paid = OrderPosition.objects.filter(
order__event=event,
order__status=Order.STATUS_PAID,
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(subevent=OuterRef('subevent'))
| (Q(subevent__isnull=True))).order_by().values(
'order__event').annotate(c=Count('*')).values('c')
pqs_all_paid_and_pending = OrderPosition.objects.filter(
order__event=event,
order__status__in=[Order.STATUS_PAID, Order.STATUS_PENDING]
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(subevent=OuterRef('subevent'))
| (Q(subevent__isnull=True))).order_by().values(
'order__event').annotate(c=Count('*')).values('c')
# Now we need a subquery for the case of checkin lists that are limited to certain
# products. We cannot use OuterRef("limit_products") since that would do a cross-product
# with the products table and we'd get duplicate rows in the output with different annotations
# on them, which isn't useful at all. Therefore, we need to add a second layer of subqueries
# to retrieve all of those items and then check if the item_id is IN this subquery result.
pqs_limited_paid = OrderPosition.objects.filter(
order__event=event,
order__status=Order.STATUS_PAID,
item_id__in=Subquery(
Item.objects.filter(
checkinlist__pk=OuterRef(OuterRef('pk'))).values('pk'))
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(subevent=OuterRef('subevent'))
| (Q(subevent__isnull=True))).order_by().values(
'order__event').annotate(c=Count('*')).values('c')
pqs_limited_paid_and_pending = OrderPosition.objects.filter(
order__event=event,
order__status__in=[Order.STATUS_PAID, Order.STATUS_PENDING],
item_id__in=Subquery(
Item.objects.filter(
checkinlist__pk=OuterRef(OuterRef('pk'))).values('pk'))
).filter(
# This assumes that in an event with subevents, *all* positions have subevents
# and *all* checkin lists have a subevent assigned
Q(subevent=OuterRef('subevent'))
| (Q(subevent__isnull=True))).order_by().values(
'order__event').annotate(c=Count('*')).values('c')
# Finally, we put all of this together. We force empty subquery aggregates to 0 by using Coalesce()
# and decide which subquery to use for this row. In the end, we compute an integer percentage in case
# we want to display a progress bar.
return qs.annotate(
checkin_count=Coalesce(
Case(When(include_pending=True,
then=Subquery(cqs_paid_and_pending,
output_field=models.IntegerField())),
default=Subquery(cqs_paid,
output_field=models.IntegerField()),
output_field=models.IntegerField()), 0),
position_count=Coalesce(
Case(When(all_products=True,
include_pending=False,
then=Subquery(pqs_all_paid,
output_field=models.IntegerField())),
When(all_products=True,
include_pending=True,
then=Subquery(pqs_all_paid_and_pending,
output_field=models.IntegerField())),
When(all_products=False,
include_pending=False,
then=Subquery(pqs_limited_paid,
output_field=models.IntegerField())),
default=Subquery(pqs_limited_paid_and_pending,
output_field=models.IntegerField()),
output_field=models.IntegerField()),
0)).annotate(percent=Case(When(position_count__gt=0,
then=F('checkin_count') * 100 /
F('position_count')),
default=0,
output_field=models.IntegerField()))
def get_queryset(self):
team = self.request.query_params.get('team', None)
if team is None:
raise serializers.ValidationError(
'Request must include "team" query param')
queryset = Task.objects.filter(is_trashed=False, process_type__team_created_by=team)\
.select_related('process_type', 'product_type')
start = self.request.query_params.get('start', None)
end = self.request.query_params.get('end', None)
if start is not None and end is not None:
dt = datetime.datetime
start_date = pytz.utc.localize(
dt.strptime(start, constants.DATE_FORMAT))
end_date = pytz.utc.localize(
dt.strptime(end, constants.DATE_FORMAT))
queryset = queryset.filter(created_at__range=(start_date,
end_date))
flagged = self.request.query_params.get('flagged', None)
if flagged and flagged.lower() == 'true':
queryset = queryset.filter(is_flagged=True)
product_types = self.request.query_params.get('product_types', None)
if product_types is not None:
product_ids = product_types.strip().split(',')
queryset = queryset.filter(product_type__in=product_ids)
process_types = self.request.query_params.get('process_types', None)
if process_types is not None:
process_ids = process_types.strip().split(',')
queryset = queryset.filter(process_type__in=process_ids)
label = self.request.query_params.get('label', None)
if label is not None:
queryset = queryset.filter(
Q(keywords__icontains=label) | Q(search=SearchQuery(label))
| Q(label__istartswith=label)
| Q(custom_display__istartswith=label))
aggregate_products = self.request.query_params.get(
'aggregate_products', None)
ordering = self.request.query_params.get('ordering',
'process_type__name')
queryset_values = [
'process_type',
'process_type__name',
'process_type__code',
'process_type__unit',
'process_type__icon',
'process_type__is_trashed',
]
#Unless aggregate product param is true, return a separate row for each product type
if not aggregate_products or aggregate_products.lower() != 'true':
queryset_values.append('product_type')
return queryset.values(*queryset_values) \
.annotate(
product_type_ids=ArrayAgg('product_type'),
product_type_names=ArrayAgg('product_type__name'),
product_type_codes=ArrayAgg('product_type__code'),
runs=Count('id', distinct=True)
).annotate(amount=Coalesce(Sum('items__amount'), 0)) \
.order_by(ordering)
def set_position(self):
if self.position is None:
self.position = self.field.option_set.aggregate(
max_position=Coalesce(models.Max('position'), 0)
).get('max_position', 0) + 10
def get(self, request):
year = request.GET.get("filter_year")
month = request.GET.get("filter_month")
if year and month:
invoices = Invoice.objects.filter(date__year=year,
date__month=month)
returned = Returned.objects.filter(date__year=year,
date__month=month)
else:
invoices = Invoice.objects.all()
returned = Returned.objects.all()
invoices = self._add_invoice_sorting(request, invoices)
returned_subquery = (Returned.objects.filter(
product=OuterRef("pk")).values("product_id").annotate(
sum_returned_qty=Sum("qty"),
sum_returned=Sum(self._count_summary),
))
released_subquery = (Released.objects.filter(
product=OuterRef("pk"),
invoice__in=invoices).values("product_id").annotate(
sum_qty=Sum("qty"), sum_summary=Sum(self._count_summary)))
products = Product.objects.annotate(
sum_qty=Coalesce(Subquery(released_subquery.values("sum_qty")),
Value(0)),
sum_summary=Coalesce(
Subquery(released_subquery.values("sum_summary")), Value(0)),
sum_returned_qty=Coalesce(
Subquery(returned_subquery.values("sum_returned_qty")),
Value(0),
),
sum_returned=Coalesce(
Subquery(returned_subquery.values("sum_returned")), Value(0)),
sum_with_returned=F("sum_summary") - F("sum_returned"),
sum_with_returned_qty=F("sum_qty") - F("sum_returned_qty"),
).order_by("name")
returned_subquery = (Returned.objects.filter(
client=OuterRef("pk")).values("client_id").annotate(
sum_returned_qty=Sum("qty"),
sum_returned=Sum(self._count_summary),
))
released_subquery = (Released.objects.filter(
invoice__client=OuterRef("pk"),
invoice__in=invoices).values("invoice__client__id").annotate(
sum_qty=Sum("qty"), sum_summary=Sum(self._count_summary)))
clients = Client.objects.annotate(
sum_qty=Coalesce(Subquery(released_subquery.values("sum_qty")),
Value(0)),
sum_summary=Coalesce(
Subquery(released_subquery.values("sum_summary")), Value(0)),
sum_returned_qty=Coalesce(
Subquery(returned_subquery.values("sum_returned_qty")),
Value(0),
),
sum_returned=Coalesce(
Subquery(returned_subquery.values("sum_returned")), Value(0)),
sum_with_returned=F("sum_summary") - F("sum_returned"),
sum_with_returned_qty=F("sum_qty") - F("sum_returned_qty"),
sum_invoices=Coalesce(Count("invoice"), Value(0)),
).order_by("name")
sum_summary = sum([i.summary for i in invoices])
sum_returned = sum([i.summary for i in returned])
context = {
"invoices":
invoices,
"clients":
clients,
"returned":
returned,
"sum_summary":
sum_summary,
"sum_returned":
sum_returned,
"sum_with_returned":
sum_summary - sum_returned,
"products_summary":
products,
"returned_create_form":
ReturnedCreateForm(),
"returned_create_url":
reverse("invoice:returned_create"),
"invoice_sort_fields":
self._generate_sorting_list(self._invoices_sorting),
}
return render(request, self.template_name, context)
def annotate_available_quantity(self):
return self.annotate(
available_quantity=F("quantity") -
Coalesce(Sum("allocations__quantity_allocated"), 0))
def get_queryset(self):
queryset = super(EnterpriseUsersViewSet, self).get_queryset()
queryset = queryset.filter(CONSENT_TRUE_OR_NOENROLL_Q).distinct()
has_enrollments = self.request.query_params.get('has_enrollments')
if has_enrollments == 'true':
queryset = queryset.filter(enrollments__isnull=False).distinct()
elif has_enrollments == 'false':
queryset = queryset.filter(enrollments__isnull=True)
active_courses = self.request.query_params.get('active_courses')
if active_courses == 'true':
queryset = queryset.filter(
Q(enrollments__consent_granted=True),
enrollments__course_end__gte=timezone.now()
)
elif active_courses == 'false':
queryset = queryset.filter(
Q(enrollments__consent_granted=True),
enrollments__course_end__lte=timezone.now()
)
all_enrollments_passed = self.request.query_params.get('all_enrollments_passed')
if all_enrollments_passed == 'true':
queryset = queryset.exclude(enrollments__has_passed=False)
elif all_enrollments_passed == 'false':
queryset = queryset.filter(enrollments__has_passed=False)
extra_fields = self.request.query_params.getlist('extra_fields')
if 'enrollment_count' in extra_fields:
enrollment_subquery = (
EnterpriseEnrollment.objects.filter(
enterprise_user=OuterRef("enterprise_user_id"),
consent_granted=True,
)
.values('enterprise_user')
.annotate(enrollment_count=Count('pk', distinct=True))
.values('enrollment_count')
)
queryset = queryset.annotate(
enrollment_count=Coalesce(
Subquery(enrollment_subquery, output_field=IntegerField()),
Value(0),
)
)
# based on https://stackoverflow.com/questions/43770118/simple-subquery-with-outerref
if 'course_completion_count' in extra_fields:
enrollment_subquery = (
EnterpriseEnrollment.objects.filter(
enterprise_user=OuterRef("enterprise_user_id"),
has_passed=True,
consent_granted=True,
)
.values('enterprise_user')
.annotate(course_completion_count=Count('pk', distinct=True))
.values('course_completion_count')
)
# Coalesce and Value used here so we don't return "null" to the
# frontend if the count is 0
queryset = queryset.annotate(
course_completion_count=Coalesce(
Subquery(enrollment_subquery, output_field=IntegerField()),
Value(0),
)
)
return queryset
def create(self, request):
"""
Function to set subscription of a user to a particular event
:param request: token, event_id, no_of_tickets,
user_id, card_number, expiry_month, expiry_year,
amount, discount_amount, total_amount
:return: json response subscribed successful or error message
"""
logger.log_info("Subscription Started")
data = json.loads(request.body)
event_id = data.get('event_id', None)
no_of_tickets = data.get('no_of_tickets', None)
user_id = data.get('user_id', None)
card_number = data.get('card_number', None)
expiry_month = data.get('expiry_month', None)
expiry_year = data.get('expiry_year', None)
amount = data.get('amount', None)
discount_amount = data.get('discount_amount', None)
total_amount = data.get('total_amount', None)
payment_id = None
token = get_authorization_header(request).split()[1]
payload = jwt.decode(token, SECRET_KEY)
user_id = payload['user_id']
if not event_id or not no_of_tickets or not user_id:
logger.log_error(
"Event_id, no_of_tickets and user_id are mandatory in request")
return api_error_response(message="Request Parameters are invalid")
try:
self.event = Event.objects.get(id=event_id, is_active=True)
except Event.DoesNotExist:
logger.log_error(f"Event_id {event_id} does not exist")
return api_error_response("Invalid event id")
if no_of_tickets < 0:
instance = self.queryset.filter(user=user_id, event=event_id)
tickets_data = instance.values('event').aggregate(
Sum('no_of_tickets'))
remaining_tickets = no_of_tickets + tickets_data[
'no_of_tickets__sum']
if remaining_tickets < 0:
logger.log_error(
f"Can not cancel tickets more than purchase {no_of_tickets}"
)
return api_error_response(
message="Can not cancel tickets more than purchase",
status=400)
if amount:
data = dict(card_number=card_number,
expiry_month=expiry_month,
expiry_year=expiry_year,
amount=amount,
discount_amount=discount_amount,
total_amount=total_amount,
no_of_tickets=no_of_tickets)
payment_access_token = payment_token(user_id)
payment_access_token = payment_access_token.decode('UTF-8')
payment_object = requests.post(
PAYMENT_URL,
data=json.dumps(data),
headers={
"Authorization": "Bearer {}".format(payment_access_token),
"Content-type": "application/json"
})
if payment_object.status_code == 200:
payment_object = payment_object.json().get('data')
if payment_object['status'] == 3:
payment_object[
'total_amount'] = payment_object['total_amount'] * (-1)
else:
return api_error_response(
message="Error while fetching payment", status=500)
payment_id = payment_object['id']
amount = payment_object['total_amount']
data = dict(user=user_id,
event=event_id,
no_of_tickets=no_of_tickets,
id_payment=payment_id,
amount=amount)
if not payment_id and self.event.subscription_fee > 0:
return api_error_response(
message="Required fields are not present")
if self.event.no_of_tickets - self.event.sold_tickets >= no_of_tickets:
serializer = SubscriptionSerializer(data=data)
serializer.is_valid(raise_exception=True)
serializer.save()
if serializer.instance.id_payment:
success_queryset = self.queryset.filter(
user=user_id,
event=event_id,
id_payment__isnull=False,
amount__gt=0)
refund_queryset = self.queryset.filter(
user=user_id,
event=event_id,
id_payment__isnull=False,
amount__lt=0)
success_queryset = success_queryset.select_related('event')
refund_queryset = refund_queryset.select_related('event')
success_queryset = success_queryset.values('event').annotate(
total_amount=Coalesce(Sum('amount'), 0),
total_tickets=Coalesce(Sum('no_of_tickets'), 0),
event_name=F('event__name'),
event_date=F('event__date'),
event_time=F('event__time'),
event_location=F('event__location'))
refund_queryset = refund_queryset.values('event').annotate(
total_amount=Coalesce(Sum('amount'), 0),
total_tickets=Coalesce(Sum('no_of_tickets'), 0))
if len(success_queryset) > 0:
success_queryset = success_queryset[0]
if len(refund_queryset) > 0:
refund_queryset = refund_queryset[0]
refund_total_amount = refund_queryset['total_amount']
refund_total_tickets = refund_queryset['total_tickets']
else:
refund_total_amount = 0
refund_total_tickets = 0
data = dict(
curent_payment_id=payment_id,
no_of_tickets=int(success_queryset['total_tickets'] +
refund_total_tickets),
total_amount=success_queryset['total_amount'] +
refund_total_amount,
event_name=success_queryset['event_name'],
event_date=success_queryset['event_date'],
event_time=success_queryset['event_time'],
event_location=success_queryset['event_location'])
else:
queryset = self.queryset.filter(event=event_id,
user=user_id,
id_payment=None)
queryset = queryset.select_related('event')
tickets_data = queryset.aggregate(Sum('no_of_tickets'))
queryset = queryset.values('event').annotate(
event_name=F('event__name'),
event_date=F('event__date'),
event_time=F('event__time'),
event_location=F('event__location'))
queryset = queryset.first()
data = dict(no_of_tickets=int(
tickets_data['no_of_tickets__sum']),
event_name=queryset['event_name'],
event_date=queryset['event_date'],
event_time=queryset['event_time'],
event_location=queryset['event_location'])
logger.log_info(
f"Subscription successful for user with id {user_id}")
return api_success_response(message="Subscribed Successfully",
data=data,
status=201)
logger.log_error(
f"Number of tickets are invalid for subscription request of user_id {user_id}"
)
return api_error_response(
message="Requested number of tickets are more than available",
status=400)
def annotate_quantities(self):
return self.annotate(
quantity=Coalesce(Sum("stocks__quantity"), 0),
quantity_allocated=Coalesce(
Sum("stocks__allocations__quantity_allocated"), 0),
)
def get_empty_string_if_null_expression(field):
"""Get empty string if field is None."""
return Coalesce(field, Value(''))
def get(database_object_project):
queryset_batches_sandbox = Batch.objects.filter(
project=database_object_project,
use_sandbox=True,
).annotate(count_hits=Count(
'hits',
distint=True,
filter=Q(hits__datetime_expiration__gt=datetime.now()))).annotate(
count_assignments_available=Coalesce(
Count('hits__assignments', distinct=True), 0),
count_assignments_total=F('count_hits') *
F('settings_batch__count_assignments'),
count_assignments_approved=Coalesce(
Count('hits__assignments',
distinct=True,
filter=Q(
hits__assignments__status_external=assignments.
STATUS_EXTERNAL.APPROVED)), 0),
count_assignments_rejected=Coalesce(
Count('hits__assignments',
distinct=True,
filter=Q(
hits__assignments__status_external=assignments.
STATUS_EXTERNAL.REJECTED)), 0),
).annotate(
costs_max=F('count_assignments_total') *
F('settings_batch__reward'),
costs_so_far=F('count_assignments_approved') *
F('settings_batch__reward'),
).aggregate(
sum_costs_max_sandbox=Coalesce(Sum('costs_max'), 0),
max_costs_max_sandbox=Coalesce(Max('costs_max'), 0),
min_costs_max_sandbox=Coalesce(Min('costs_max'), 0),
sum_costs_so_far_sandbox=Coalesce(Sum('costs_so_far'), 0),
max_costs_so_far_sandbox=Coalesce(Max('costs_so_far'), 0),
min_costs_so_far_sandbox=Coalesce(Min('costs_so_far'), 0),
)
queryset_batches = Batch.objects.filter(
project=database_object_project,
use_sandbox=False,
).annotate(count_hits=Count(
'hits',
distinct=True,
filter=Q(hits__datetime_expiration__gt=datetime.now()))).annotate(
count_assignments_available=Coalesce(
Count('hits__assignments', distinct=True), 0),
count_assignments_total=F('count_hits') *
F('settings_batch__count_assignments'),
count_assignments_approved=Coalesce(
Count('hits__assignments',
distinct=True,
filter=Q(
hits__assignments__status_external=assignments.
STATUS_EXTERNAL.APPROVED)), 0),
count_assignments_rejected=Coalesce(
Count('hits__assignments',
distinct=True,
filter=Q(
hits__assignments__status_external=assignments.
STATUS_EXTERNAL.REJECTED)), 0),
).annotate(
costs_max=F('count_assignments_total') *
F('settings_batch__reward'),
costs_so_far=F('count_assignments_approved') *
F('settings_batch__reward'),
).aggregate(
sum_costs_max=Coalesce(Sum('costs_max'), 0),
max_costs_max=Coalesce(Max('costs_max'), 0),
min_costs_max=Coalesce(Min('costs_max'), 0),
sum_costs_so_far=Coalesce(Sum('costs_so_far'), 0),
max_costs_so_far=Coalesce(Max('costs_so_far'), 0),
min_costs_so_far=Coalesce(Min('costs_so_far'), 0),
)
queryset_batches_sandbox.update(queryset_batches)
queryset = Project.objects.filter(
pk=database_object_project.pk).annotate(
**{
key: Value(value, output_field=IntegerField())
for key, value in queryset_batches_sandbox.items()
}).get()
# queryset = Batch.objects.filter(
# project=database_object_project,
# use_sandbox=use_sandbox,
# ).annotate(
# count_hits=Count('hits')
# ).annotate(
# count_assignments_available=Coalesce(Count('hits__assignments', distinct=True), 0),
# count_assignments_total=F('count_hits') * F('settings_batch__count_assignments'),
# count_assignments_approved=Coalesce(Count(
# 'hits__assignments',
# distinct=True,
# filter=Q(hits__assignments__status_external=assignments.STATUS_EXTERNAL.APPROVED)
# ), 0),
# count_assignments_rejected=Coalesce(Count(
# 'hits__assignments',
# distinct=True,
# filter=Q(hits__assignments__status_external=assignments.STATUS_EXTERNAL.REJECTED)
# ), 0),
# ).annotate(
# costs_max=F('count_assignments_total') * F('settings_batch__reward'),
# costs_so_far=F('count_assignments_approved') * F('settings_batch__reward'),
# )
#
# sort_by = request.query_params.get('sort_by')
# if sort_by is not None:
# descending = request.query_params.get('descending', 'false') == 'true'
# queryset = queryset.order_by(
# ('-' if descending else '') + sort_by
# )
#
return queryset
