def add_person_id(self, action):
return self.annotate(person_id=Subquery(
PersonDistinctId.objects.filter(
team_id=action.team_id, distinct_id=OuterRef(
'distinct_id')).order_by().values('person_id')[:1]))
def for_table(self, user):
activities = self.model.activities.rel.model
comments = activities.comments.filter(
submission=OuterRef('id')).visible_to(user)
roles_for_review = self.model.assigned.field.model.objects.with_roles(
).filter(submission=OuterRef('id'), reviewer=user)
review_model = self.model.reviews.field.model
reviews = review_model.objects.filter(submission=OuterRef('id'))
opinions = review_model.opinions.field.model.objects.filter(
review__submission=OuterRef('id'))
reviewers = self.model.assigned.field.model.objects.filter(
submission=OuterRef('id'))
return self.with_latest_update().annotate(
comment_count=Coalesce(
Subquery(
comments.values('submission').order_by().annotate(
count=Count('pk')).values('count'),
output_field=IntegerField(),
),
0,
),
opinion_disagree=Subquery(
opinions.filter(
opinion=DISAGREE).values('review__submission').annotate(
count=Count('*')).values('count')[:1],
output_field=IntegerField(),
),
review_staff_count=Subquery(
reviewers.staff().values('submission').annotate(
count=Count('pk')).values('count'),
output_field=IntegerField(),
),
review_count=Subquery(
reviewers.values('submission').annotate(
count=Count('pk')).values('count'),
output_field=IntegerField(),
),
review_submitted_count=Subquery(
reviewers.reviewed().values('submission').annotate(
count=Count('pk', distinct=True)).values('count'),
output_field=IntegerField(),
),
review_recommendation=Case(
When(opinion_disagree__gt=0, then=MAYBE),
default=Subquery(
reviews.submitted().values('submission').annotate(
calc_recommendation=Sum('recommendation') /
Count('recommendation'), ).values(
'calc_recommendation'),
output_field=IntegerField(),
)),
role_icon=Subquery(roles_for_review[:1].values('role__icon')),
).prefetch_related(
Prefetch(
'assigned',
queryset=AssignedReviewers.objects.reviewed().review_order(
).select_related('reviewer', ).prefetch_related(
Prefetch('review__opinions',
queryset=ReviewOpinion.objects.select_related(
'author')), ),
to_attr='has_reviewed'),
Prefetch('assigned',
queryset=AssignedReviewers.objects.not_reviewed().staff(),
to_attr='hasnt_reviewed')).select_related(
'page',
'round',
'lead',
'user',
'screening_status',
'previous__page',
'previous__round',
'previous__lead',
)
def post(self, request, format=None):
""" Return all high-level Federal Account information """
request_data = self._parse_and_validate_request(request.data)
limit = request_data["limit"]
page = request_data["page"]
sort_field = request_data["sort"]["field"]
sort_direction = request_data["sort"]["direction"]
keyword = request_data.get("keyword", None)
fy = request_data["filters"]["fy"]
agency_id = request_data["filters"].get("agency_identifier", None)
lower_limit = (page - 1) * limit
upper_limit = page * limit
agency_subquery = ToptierAgency.objects.filter(
toptier_code=OuterRef("corrected_agency_identifier"))
queryset = (FederalAccount.objects.filter(
treasuryappropriationaccount__account_balances__final_of_fy=True,
treasuryappropriationaccount__account_balances__submission__reporting_period_start__fy
=fy,
).annotate(corrected_agency_identifier=Func(
F("agency_identifier"), function="CORRECTED_CGAC"
)).annotate(
account_id=F("id"),
account_name=F("account_title"),
account_number=F("federal_account_code"),
budgetary_resources=Sum(
"treasuryappropriationaccount__account_balances__total_budgetary_resources_amount_cpe"
),
managing_agency=Subquery(agency_subquery.values("name")[:1]),
managing_agency_acronym=Subquery(
agency_subquery.values("abbreviation")[:1]),
))
# add keyword filter, if it exists
if keyword:
queryset = queryset.filter(
Q(account_name__icontains=keyword)
| Q(account_number__contains=keyword)
| Q(managing_agency__icontains=keyword)
| Q(managing_agency_acronym__contains=keyword.upper()))
if agency_id is not None:
tta_list = DOD_ARMED_FORCES_CGAC if agency_id == DOD_CGAC else [
agency_id
]
tta_filter = Q()
for tta in tta_list:
tta_filter |= Q(account_number__startswith=tta)
queryset &= queryset.filter(tta_filter)
if sort_direction == "desc":
queryset = queryset.order_by(F(sort_field).desc(nulls_last=True))
else:
queryset = queryset.order_by(F(sort_field).asc())
result = {
"count": queryset.count(),
"limit": limit,
"page": page,
"fy": fy,
"keyword": keyword
}
resultset = queryset.values(
"account_id",
"account_number",
"account_name",
"budgetary_resources",
"agency_identifier",
"managing_agency",
"managing_agency_acronym",
)
resultset = resultset[lower_limit:upper_limit + 1]
page_metadata = get_simple_pagination_metadata(len(resultset), limit,
page)
result.update(page_metadata)
resultset = resultset[:limit]
result["results"] = resultset
return Response(result)
def applicable_shipping_methods_by_channel(shipping_methods, channel_id):
query = ShippingMethodChannelListing.objects.filter(
shipping_method=OuterRef("pk"),
channel_id=channel_id).values_list("price_amount")
return shipping_methods.annotate(
price_amount=Subquery(query)).order_by("price_amount")
def index(request):
if 'user_id' not in request.session:
return redirect(settings.BASE_URL)
else:
# project add code Start
if request.method == "POST":
update = request.POST.get('update')
submit = request.POST.get('submit')
project_id = request.POST['project_id']
upload_file = request.FILES.get('project_map_value', False)
# pro_map=request.POST['pro_map']
today = datetime.now()
val = today.strftime("_%d_%m_%Y_%H_%M_%S_%f")
if upload_file is not False:
filename = upload_file.name
splitedfilename = filename.split(".")
length_of_filename = len(splitedfilename)
file_extention = splitedfilename[length_of_filename - 1]
upload_file_name = str(val) + "." + file_extention
fs = FileSystemStorage()
fs.save(upload_file_name, upload_file)
im = Image.open(upload_file)
width = im.size[0]
pro_map = project_id + str(val) + "." + file_extention
height = im.size[1]
else:
if request.POST['pro_map_old'] != "":
upload_file_name = request.POST['pro_map_old']
width = request.POST['width_old']
height = request.POST['height_old']
else:
upload_file_name = ""
width = 0
height = 0
project_name = request.POST.getlist('project_name_project')
ids = str(project_name).split("'")
project_name = ids[1]
project_country = request.POST['project_country']
lat = request.POST['lat']
long = request.POST['long']
project_city = request.POST['project_city']
if project_city is None:
project_city = ""
if project_country is None:
project_country = ""
if upload_file_name is None:
upload_file = ""
if request.POST["add_project_id"] == "":
entry_user = AddProject(project_name=project_name,
project_id=project_id,
project_country=project_country,
project_city=project_city,
width=width,
height=height,
lat=lat,
long=long,
date=datetime.now(),
upload_map=upload_file_name,
create_by=request.session['user_id'])
entry_user.save()
data_id = AddProject.objects.get(project_id=project_id)
# return HttpResponse(data_id.id)
current_project_id = str(data_id.id)
if request.session['user_type'] == "Sub Admin":
entry_project = ProjectMap(
project_id=current_project_id,
user_type=request.session['user_type'],
user_id=request.session['user_id'],
date=datetime.now())
entry_project.save()
return redirect(settings.BASE_URL + 'dashboard/' +
str(current_project_id))
else:
if AddProject.objects.filter(
id=request.POST["add_project_id"]).exists():
AddProject.objects.filter(
id=request.POST["add_project_id"]).update(
project_name=project_name,
project_country=project_country,
project_city=project_city,
width=width,
height=height,
lat=lat,
long=long,
upload_map=upload_file_name,
date=datetime.now())
# ProjectMap.objects.filter(project_id=request.POST["add_project_id"]).update(project=project_name)
# project add code End
user_info = UserRegistration.objects.get(id=request.session["user_id"])
if request.session["user_type"] == "Admin":
project_data = AddProject.objects.all().order_by("project_name")
else:
user_map_project = ProjectMap.objects.filter(
user_id=request.session["user_id"])
project_data = AddProject.objects.filter(
id__in=Subquery(user_map_project.values(
"project_id"))).order_by("project_name")
return render(
request, "project/index.html", {
"BASE_URL": settings.BASE_URL,
"user_info": user_info,
"project_data": project_data,
"user_type": request.session["user_type"],
"temp_user_type": request.session["temp_user_type"]
})
def test_obj_subquery_lookup(self):
qs = HStoreModel.objects.annotate(value=Subquery(
HStoreModel.objects.filter(
pk=OuterRef('pk')).values('field')), ).filter(value__a='b')
self.assertSequenceEqual(qs, self.objs[:2])
def get_queryset(self, ignore_status=False, ignore_products=False):
cqs = Checkin.objects.filter(
position_id=OuterRef('pk'),
list_id=self.checkinlist.pk
).order_by().values('position_id').annotate(
m=Max('datetime')
).values('m')
qs = OrderPosition.objects.filter(
order__event=self.request.event,
).annotate(
last_checked_in=Subquery(cqs)
).prefetch_related('order__event', 'order__event__organizer')
if self.checkinlist.subevent:
qs = qs.filter(
subevent=self.checkinlist.subevent
)
if self.request.query_params.get('ignore_status', 'false') != 'true' and not ignore_status:
qs = qs.filter(
order__status__in=[Order.STATUS_PAID, Order.STATUS_PENDING] if self.checkinlist.include_pending else [Order.STATUS_PAID]
)
if self.request.query_params.get('pdf_data', 'false') == 'true':
qs = qs.prefetch_related(
Prefetch(
lookup='checkins',
queryset=Checkin.objects.filter(list_id=self.checkinlist.pk)
),
'checkins', 'answers', 'answers__options', 'answers__question',
Prefetch('addons', OrderPosition.objects.select_related('item', 'variation')),
Prefetch('order', Order.objects.select_related('invoice_address').prefetch_related(
Prefetch(
'event',
Event.objects.select_related('organizer')
),
Prefetch(
'positions',
OrderPosition.objects.prefetch_related(
'checkins', 'item', 'variation', 'answers', 'answers__options', 'answers__question',
)
)
))
).select_related(
'item', 'variation', 'item__category', 'addon_to', 'order', 'order__invoice_address', 'seat'
)
else:
qs = qs.prefetch_related(
Prefetch(
lookup='checkins',
queryset=Checkin.objects.filter(list_id=self.checkinlist.pk)
),
'answers', 'answers__options', 'answers__question',
Prefetch('addons', OrderPosition.objects.select_related('item', 'variation'))
).select_related('item', 'variation', 'order', 'addon_to', 'order__invoice_address', 'order', 'seat')
if not self.checkinlist.all_products and not ignore_products:
qs = qs.filter(item__in=self.checkinlist.limit_products.values_list('id', flat=True))
if 'subevent' in self.request.query_params.getlist('expand'):
qs = qs.prefetch_related(
'subevent', 'subevent__event', 'subevent__subeventitem_set', 'subevent__subeventitemvariation_set',
'subevent__seat_category_mappings', 'subevent__meta_values'
)
if 'item' in self.request.query_params.getlist('expand'):
qs = qs.prefetch_related('item', 'item__addons', 'item__bundles', 'item__meta_values', 'item__variations').select_related('item__tax_rule')
if 'variation' in self.request.query_params.getlist('expand'):
qs = qs.prefetch_related('variation')
if 'pk' not in self.request.resolver_match.kwargs and 'can_view_orders' not in self.request.eventpermset \
and len(self.request.query_params.get('search', '')) < 3:
qs = qs.none()
return qs
def add_person_id(self, team_id: int):
return self.annotate(person_id=Subquery(
PersonDistinctId.objects.filter(
team_id=team_id, distinct_id=OuterRef(
"distinct_id")).order_by().values("person_id")[:1]))
def get(self, request):
admin = rules.is_admin_for_event(request.user, request.tenant)
if not admin:
return HttpResponseForbidden()
episode = request.GET.get('episode')
puzzle = request.GET.get('puzzle')
team = request.GET.get('team')
puzzles = models.Puzzle.objects.all()
if puzzle:
puzzles = puzzles.filter(id=puzzle)
if episode:
puzzles = puzzles.filter(episode=episode)
# The following query is heavily optimised. We only retrieve the fields we will use here and
# in the template, and we select and prefetch related objects so as not to perform any extra
# queries.
all_guesses = models.Guess.objects.filter(
for_puzzle__in=puzzles,
).order_by(
'-given'
).select_related(
'for_puzzle', 'by_team', 'by__user', 'correct_for'
).only(
'given', 'guess', 'correct_current',
'for_puzzle__id', 'for_puzzle__title',
'by_team__id', 'by_team__name',
'by__user__id', 'by__user__username',
'correct_for__id'
).annotate(
byseat=Subquery(
Attendance.objects.filter(user_info__user__profile=OuterRef('by'), event=self.request.tenant).values('seat')
)
).prefetch_related(
Prefetch(
'for_puzzle__episode',
queryset=models.Episode.objects.only('id', 'name').all()
)
)
if team:
team = teams.models.Team.objects.get(id=team)
all_guesses = all_guesses.filter(by_team=team)
guess_pages = Paginator(all_guesses, 50)
page = request.GET.get('page')
try:
guesses = guess_pages.page(page)
except PageNotAnInteger:
guesses = guess_pages.page(1)
except EmptyPage:
guesses = guess_pages.page(guess_pages.num_pages)
if request.GET.get('highlight_unlocks'):
for g in guesses:
unlockanswers = models.UnlockAnswer.objects.filter(unlock__puzzle=g.for_puzzle)
if any([a.validate_guess(g) for a in unlockanswers]):
g.unlocked = True
# Grab the current URL (which is not the URL of *this* view) so that we can manipulate the query string
# in the template.
current_url = reverse('guesses')
current_url += '?' + request.GET.urlencode()
return TemplateResponse(
request,
'hunts/guesses_content.html',
context={
'guesses': guesses,
'current_url': current_url
}
)
def pattern_selectors(self):
# This method became complicated, hence this comment. What are we trying to achieve here?
#
# Of course, we want a list of pattern creators. But also, if we have selected a creator,
# we want its list of patterns. So there's that. Simple, right?
#
# But it's not over! We also want to avoid having our creator list polluted by patternless
# creators (there are a couple of them in the DB), so our creator_qs has to be beefed up.
#
# But wait! now we have is_free and is_jersey flags. We want these to affect the list of
# patterns and creators available for selection. *this* particularly complicates this
# and make creator_qs much more complex.
item_all = [(0, "Tous")]
def pop(key):
val = params.pop(key, None)
if isinstance(val, list):
val = val[0] if val else None
return val
params = self.request.GET.copy()
params['page'] = '1'
pattern_id = pop('pattern')
pattern_creator_id = pop('pattern_creator')
if pattern_id and Pattern.objects.filter(id=pattern_id).exists():
# More reliable than the request argument.
pattern_creator_id = Pattern.objects.get(id=pattern_id).creator.id
pattern_filters = {
v.replace('pattern_', ''): True
for v in self.pattern_checkboxes().get_selected_options()
}
pattern_qs = Pattern.objects.filter(**pattern_filters)
# Woah, complicated...
# https://stackoverflow.com/a/30753074
pattern_sub = Subquery(
pattern_qs.filter(creator_id=OuterRef('pk')).annotate(
cnt=Count('pk')).values('cnt')[:1],
output_field=IntegerField())
creator_filter = Q(pattern_count__gt=0)
# We always want a selected creator to show up, even if it has no pattern
if pattern_creator_id:
creator_filter |= Q(id=pattern_creator_id)
creator_qs = PatternCreator.objects\
.annotate(pattern_count=pattern_sub)\
.filter(creator_filter)
get_url = '?{}&pattern_creator='.format(params.urlencode())
widget = Select(
attrs={'data-get-url': get_url},
choices=item_all + list(creator_qs.values_list('id', 'name')),
)
result = [
widget.render(
name='pattern_creator',
value=pattern_creator_id,
)
]
if pattern_creator_id and creator_qs.filter(
id=pattern_creator_id).exists():
pattern_creator = PatternCreator.objects.get(id=pattern_creator_id)
pattern_qs = pattern_qs.filter(creator=pattern_creator)
params['pattern_creator'] = pattern_creator_id
get_url = '?{}&pattern='.format(params.urlencode())
widget = Select(
attrs={'data-get-url': get_url},
choices=item_all + list(pattern_qs.values_list('id', 'name')),
)
result.append(widget.render(
name='pattern',
value=pattern_id,
))
return result
def is_in_role(role_name, user_id, project_id):
return RoleMapping.objects.filter(
user_id=user_id,
project_id=project_id,
role_id=Subquery(Role.objects.filter(name=role_name).values('id')),
).exists()
def result(request):
if request.method == 'POST':
country = request.POST.get('country')
state = request.POST.get('state')
district = request.POST.get('district')
taluk = request.POST.get('taluk')
village = request.POST.get('village')
sector = request.POST.get('sector')
scheme = request.POST.get('scheme')
val = 50
if sector == 'M':
x = Credits.objects.filter(medicinal_scheme__lte=val)
yz = x.values('uid_id')
if scheme == 'M1':
q = User.objects.filter(uid__in=Subquery(yz),
age__gte=14,
medical_insurance__lte=40000000)
pq = q.values()
point = x.values()
elif scheme == 'M2':
q = User.objects.filter(uid__in=Subquery(yz),
age__gte=60,
medical_insurance__lte=400000000)
pq = q.values()
point = x.values()
else:
q = User.objects.all()
pq = q.values()
point = x.values()
if sector == 'E':
x = Credits.objects.filter(educational_scheme__lte=val)
yz = x.values('uid_id')
if scheme == 'E1':
q = User.objects.filter(uid__in=Subquery(yz),
age__gte=20,
disability="Yes")
pq = q.values()
point = x.values()
elif scheme == 'E2':
q = User.objects.filter(uid__in=Subquery(yz),
age__lte=60,
caste="SC")
pq = q.values()
point = x.values()
else:
q = User.objects.all()
pq = q.values()
point = x.values()
if sector == 'L':
x = Credits.objects.filter(land_scheme__lte=val)
yz = x.values('uid_id')
if scheme == 'L1':
q = User.objects.filter(uid__in=Subquery(yz),
age__gte=30,
disability="Yes")
pq = q.values()
point = x.values()
elif scheme == 'L2':
q = User.objects.filter(uid__in=Subquery(yz),
age__gte=30,
gender='M',
disability='No')
pq = q.values()
point = x.values()
else:
q = User.objects.all()
pq = q.values()
point = x.values()
if sector == 'F':
x = Credits.objects.filter(financial_scheme__lte=val)
yz = x.values('uid_id')
if scheme == 'F1':
q = User.objects.filter(uid__in=Subquery(yz),
age__gte=30,
gender='F',
disability='Yes',
caste='GEN')
pq = q.values()
point = x.values()
elif scheme == 'F2':
q = User.objects.filter(uid__in=Subquery(yz),
age__gte=30,
caste='SC')
pq = q.values()
point = x.values()
else:
q = User.objects.all()
pq = q.values()
point = x.values()
if sector == 'W':
x = Credits.objects.all()
yz = x.values('uid_id')
q = User.objects.all()
pq = q.values()
point = x.values()
toSend = zip(pq, point)
context = {'toSend': toSend}
return render(request, 'data/search2.html', context)
def _get_queryset(self, cl, form_data):
cqs = Checkin.objects.filter(
position_id=OuterRef('pk'),
list_id=cl.pk).order_by().values('position_id').annotate(
m=Max('datetime')).values('m')
cqsin = cqs.filter(type=Checkin.TYPE_ENTRY)
cqsout = cqs.filter(type=Checkin.TYPE_EXIT)
qs = OrderPosition.objects.filter(order__event=self.event, ).annotate(
last_checked_in=Subquery(cqsin),
last_checked_out=Subquery(cqsout),
auto_checked_in=Exists(
Checkin.objects.filter(
position_id=OuterRef('pk'),
list_id=cl.pk,
auto_checked_in=True))).prefetch_related(
'answers', 'answers__question', 'addon_to__answers',
'addon_to__answers__question').select_related(
'order', 'item', 'variation', 'addon_to',
'order__invoice_address', 'voucher', 'seat')
if not cl.all_products:
qs = qs.filter(
item__in=cl.limit_products.values_list('id', flat=True))
if cl.subevent:
qs = qs.filter(subevent=cl.subevent)
if form_data.get('date_from'):
dt = make_aware(
datetime.combine(
dateutil.parser.parse(form_data['date_from']).date(),
time(hour=0, minute=0, second=0)), self.event.timezone)
qs = qs.filter(subevent__date_from__gte=dt)
if form_data.get('date_to'):
dt = make_aware(
datetime.combine(
dateutil.parser.parse(form_data['date_to']).date() +
timedelta(days=1), time(hour=0, minute=0, second=0)),
self.event.timezone)
qs = qs.filter(subevent__date_from__lt=dt)
o = ()
if self.event.has_subevents and not cl.subevent:
o = ('subevent__date_from', 'subevent__name')
sort = form_data.get('sort') or 'name'
if sort == 'name':
qs = qs.order_by(
*o,
Coalesce(
NullIf('attendee_name_cached', Value('')),
NullIf('addon_to__attendee_name_cached', Value('')),
NullIf('order__invoice_address__name_cached', Value('')),
'order__code'))
elif sort == 'code':
qs = qs.order_by(*o, 'order__code')
elif sort.startswith('name:'):
part = sort[5:]
qs = qs.annotate(resolved_name=Case(
When(attendee_name_cached__ne='', then='attendee_name_parts'),
When(addon_to__attendee_name_cached__isnull=False,
addon_to__attendee_name_cached__ne='',
then='addon_to__attendee_name_parts'),
default='order__invoice_address__name_parts',
)).annotate(resolved_name_part=JSONExtract(
'resolved_name', part)).order_by(*o, 'resolved_name_part')
if form_data.get('attention_only'):
qs = qs.filter(
Q(item__checkin_attention=True)
| Q(order__checkin_attention=True))
if not cl.include_pending:
qs = qs.filter(order__status=Order.STATUS_PAID)
else:
qs = qs.filter(order__status__in=(Order.STATUS_PAID,
Order.STATUS_PENDING))
return qs
def total_queryset(self):
filters = [
self.is_in_provided_def_codes,
self.is_non_zero_total_spending,
self.all_closed_defc_submissions,
Q(treasury_account__isnull=False),
Q(treasury_account__federal_account__isnull=False),
]
annotations = {
"fa_code":
F("treasury_account__federal_account__federal_account_code"),
"description":
F("treasury_account__account_title"),
"code":
F("treasury_account__tas_rendering_label"),
"id":
F("treasury_account__treasury_account_identifier"),
"award_count":
Value(None, output_field=IntegerField()),
"fa_description":
F("treasury_account__federal_account__account_title"),
"fa_id":
F("treasury_account__federal_account_id"),
"obligation":
Coalesce(
Sum(
Case(
When(
self.final_period_submission_query_filters,
then=
F("obligations_incurred_by_program_object_class_cpe"
),
),
default=Value(0),
)),
0,
),
"outlay":
Coalesce(
Sum(
Case(
When(
self.final_period_submission_query_filters,
then=
F("gross_outlay_amount_by_program_object_class_cpe"
),
),
default=Value(0),
)),
0,
),
"total_budgetary_resources":
Coalesce(
Subquery(
GTASSF133Balances.objects.filter(
disaster_emergency_fund_code__in=self.def_codes,
fiscal_period=self.
latest_reporting_period["submission_fiscal_month"],
fiscal_year=self.
latest_reporting_period["submission_fiscal_year"],
treasury_account_identifier=OuterRef(
"treasury_account"),
).annotate(amount=Func("total_budgetary_resources_cpe",
function="Sum")).values("amount"),
output_field=DecimalField(),
),
0,
),
}
# Assuming it is more performant to fetch all rows once rather than
# run a count query and fetch only a page's worth of results
return (FinancialAccountsByProgramActivityObjectClass.objects.filter(
*filters).values(
"treasury_account__federal_account__id",
"treasury_account__federal_account__federal_account_code",
"treasury_account__federal_account__account_title",
).annotate(**annotations).values(*annotations.keys()))
class Meta(NodeHandler.Meta):
abstract = False
queryset = (node_prefetch(Machine.objects.all()).prefetch_related(
'blockdevice_set__iscsiblockdevice__'
'partitiontable_set__partitions').prefetch_related(
'blockdevice_set__physicalblockdevice__'
'partitiontable_set__partitions__filesystem_set').
prefetch_related('blockdevice_set__virtualblockdevice__'
'partitiontable_set__partitions'))
list_queryset = (
Machine.objects.select_related(
'owner', 'zone', 'domain',
'bmc').prefetch_related('blockdevice_set__iscsiblockdevice__'
'partitiontable_set__partitions').
prefetch_related(
'blockdevice_set__physicalblockdevice__'
'partitiontable_set__partitions').prefetch_related(
'blockdevice_set__virtualblockdevice__'
'partitiontable_set__partitions').prefetch_related(
'interface_set__ip_addresses__subnet__vlan__space'
).prefetch_related(
'interface_set__ip_addresses__subnet__vlan__fabric').
prefetch_related('interface_set__vlan__fabric').prefetch_related(
'boot_interface__vlan__fabric').prefetch_related(
'tags').prefetch_related('pool').annotate(
status_event_type_description=Subquery(
Event.objects.filter(
node=OuterRef('pk'),
type__level__gte=logging.INFO).order_by(
'-created',
'-id').values('type__description')[:1]),
status_event_description=Subquery(
Event.objects.filter(
node=OuterRef('pk'),
type__level__gte=logging.INFO).order_by(
'-created',
'-id').values('description')[:1])))
allowed_methods = [
'list',
'get',
'create',
'update',
'action',
'set_active',
'check_power',
'create_physical',
'create_vlan',
'create_bond',
'create_bridge',
'update_interface',
'delete_interface',
'link_subnet',
'unlink_subnet',
'mount_special',
'unmount_special',
'update_filesystem',
'update_disk',
'delete_disk',
'delete_partition',
'delete_volume_group',
'delete_cache_set',
'delete_filesystem',
'delete_vmfs_datastore',
'update_vmfs_datastore',
'create_partition',
'create_cache_set',
'create_bcache',
'create_raid',
'create_volume_group',
'create_logical_volume',
'create_vmfs_datastore',
'set_boot_disk',
'apply_storage_layout',
'default_user',
'get_summary_xml',
'get_summary_yaml',
'set_script_result_suppressed',
'set_script_result_unsuppressed',
'get_suppressible_script_results',
'get_latest_failed_testing_script_results',
]
form = AdminMachineWithMACAddressesForm
exclude = [
"creation_type",
"status_expires",
"previous_status",
"parent",
"boot_interface",
"boot_cluster_ip",
"token",
"netboot",
"ephemeral_deploy",
"agent_name",
"power_state_queried",
"power_state_updated",
"gateway_link_ipv4",
"gateway_link_ipv6",
"enable_ssh",
"skip_bmc_config",
"skip_networking",
"skip_storage",
"instance_power_parameters",
"address_ttl",
"url",
"dns_process",
"managing_process",
"last_image_sync",
"install_rackd",
"install_kvm",
]
list_fields = [
"id",
"system_id",
"hostname",
"locked",
"owner",
"cpu_count",
"cpu_speed",
"description",
"memory",
"power_state",
"domain",
"pool",
"zone",
]
listen_channels = [
"machine",
]
create_permission = NodePermission.admin
view_permission = NodePermission.view
edit_permission = NodePermission.admin
delete_permission = NodePermission.admin
def benefits(self):
qs = EmailTargetableConfiguration.objects.all().values_list(
"benefit_id", flat=True)
benefits = SponsorshipBenefit.objects.filter(id__in=Subquery(qs))
return {str(b.id): b for b in benefits}
def get_queryset(self):
queryset = super().get_queryset()
queryset = queryset.filter(ENROLLMENTS_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__is_consent_granted=True),
enrollments__course_end_date__gte=timezone.now()
)
elif active_courses == 'false':
queryset = queryset.filter(
Q(enrollments__is_consent_granted=True),
enrollments__course_end_date__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 = (
EnterpriseLearnerEnrollment.objects.filter(
enterprise_user=OuterRef("enterprise_user_id"),
is_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 = (
EnterpriseLearnerEnrollment.objects.filter(
enterprise_user=OuterRef("enterprise_user_id"),
has_passed=True,
is_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 benefits_with_assets(self):
qs = BenefitFeature.objects.required_assets().values_list(
"sponsor_benefit__sponsorship_benefit", flat=True).distinct()
benefits = SponsorshipBenefit.objects.filter(id__in=Subquery(qs))
return {str(b.id): b for b in benefits}
def test_obj_subquery_lookup(self):
qs = NullableJSONModel.objects.annotate(field=Subquery(
NullableJSONModel.objects.filter(
pk=OuterRef("pk")).values("value")), ).filter(field__a="b")
self.assertSequenceEqual(qs, [self.objs[3], self.objs[4]])
def get(self, request, organization):
"""
Fetches alert rules and legacy rules for an organization
"""
project_ids = self.get_requested_project_ids(request) or None
if project_ids == {-1}: # All projects for org:
project_ids = Project.objects.filter(
organization=organization).values_list("id", flat=True)
elif project_ids is None: # All projects for user
org_team_list = Team.objects.filter(
organization=organization).values_list("id", flat=True)
user_team_list = OrganizationMemberTeam.objects.filter(
organizationmember__user=request.user,
team__in=org_team_list).values_list("team", flat=True)
project_ids = Project.objects.filter(
teams__in=user_team_list).values_list("id", flat=True)
teams = request.GET.getlist("team", [])
team_filter_query = None
if len(teams) > 0:
try:
teams_query, unassigned = parse_team_params(
request, organization, teams)
except InvalidParams as err:
return Response(str(err), status=status.HTTP_400_BAD_REQUEST)
team_filter_query = Q(
owner_id__in=teams_query.values_list("actor_id", flat=True))
if unassigned:
team_filter_query = team_filter_query | Q(owner_id=None)
alert_rules = AlertRule.objects.fetch_for_organization(
organization, project_ids)
if not features.has("organizations:performance-view", organization):
# Filter to only error alert rules
alert_rules = alert_rules.filter(
snuba_query__dataset=Dataset.Events.value)
issue_rules = Rule.objects.filter(
status__in=[RuleStatus.ACTIVE, RuleStatus.INACTIVE],
project__in=project_ids)
name = request.GET.get("name", None)
if name:
alert_rules = alert_rules.filter(Q(name__icontains=name))
issue_rules = issue_rules.filter(Q(label__icontains=name))
if team_filter_query:
alert_rules = alert_rules.filter(team_filter_query)
issue_rules = issue_rules.filter(team_filter_query)
expand = request.GET.getlist("expand", [])
if "latestIncident" in expand:
alert_rules = alert_rules.annotate(incident_id=Coalesce(
Subquery(
Incident.objects.filter(alert_rule=OuterRef(
"pk")).order_by("-date_started").values("id")[:1]),
Value("-1"),
))
is_asc = request.GET.get("asc", False) == "1"
sort_key = request.GET.getlist("sort", ["date_added"])
rule_sort_key = [
"label" if x == "name" else x for x in sort_key
] # Rule's don't share the same field name for their title/label/name...so we account for that here.
case_insensitive = sort_key == ["name"]
if "incident_status" in sort_key:
alert_rules = alert_rules.annotate(incident_status=Coalesce(
Subquery(
Incident.objects.filter(alert_rule=OuterRef(
"pk")).order_by("-date_started").values("status")[:1]),
Value(-1, output_field=IntegerField()),
))
issue_rules = issue_rules.annotate(
incident_status=Value(-2, output_field=IntegerField()))
if "date_triggered" in sort_key:
far_past_date = Value(datetime.min, output_field=DateTimeField())
alert_rules = alert_rules.annotate(date_triggered=Coalesce(
Subquery(
Incident.objects.filter(alert_rule=OuterRef("pk")).
order_by("-date_started").values("date_started")[:1]),
far_past_date,
), )
issue_rules = issue_rules.annotate(date_triggered=far_past_date)
alert_rule_intermediary = CombinedQuerysetIntermediary(
alert_rules, sort_key)
rule_intermediary = CombinedQuerysetIntermediary(
issue_rules, rule_sort_key)
return self.paginate(
request,
paginator_cls=CombinedQuerysetPaginator,
on_results=lambda x: serialize(
x, request.user, CombinedRuleSerializer(expand=expand)),
default_per_page=25,
intermediaries=[alert_rule_intermediary, rule_intermediary],
desc=not is_asc,
cursor_cls=StringCursor if case_insensitive else Cursor,
case_insensitive=case_insensitive,
)
def get_initial_queryset(self):
id_cli = self.request.GET.get('id_cliente', None)
#print ("id_cli: {0}".format(id_cli))
descuentos_prestam = Descuento.objects.filter(prestamo=OuterRef('pk')).values('prestamo').annotate(descuento_prestamo=Coalesce(Sum('valor_descuento'),V(0))).values('descuento_prestamo')
return Prestamo.objects.values('id','capital_prestado','fecha_prestamo','porcentaje_aplicado__porcentaje').annotate(abonos_capital=Coalesce(Sum('abono__valor_abono_capital'),V(0)),abonos_interes=Coalesce(Sum('abono__valor_abono_interes'),V(0)),saldo_capital=ExpressionWrapper(F('capital_prestado')- Coalesce(Sum('abono__valor_abono_capital'),V(0)) - Coalesce( Subquery(descuentos_prestam.values('descuento_prestamo')) , 0), output_field=FloatField()),saldo_interes=F('saldo_capital') * F('porcentaje_aplicado__porcentaje')/100.0 ).filter(cliente__id=id_cli,estado=1).order_by('-fecha_prestamo')
def get_context_data(self, **kwargs):
ctx = super().get_context_data(**kwargs)
tz = timezone.get_current_timezone()
if 'latest' in self.request.GET:
clear_cache(self.request.event)
subevent = None
if self.request.GET.get("subevent",
"") != "" and self.request.event.has_subevents:
i = self.request.GET.get("subevent", "")
try:
subevent = self.request.event.subevents.get(pk=i)
except SubEvent.DoesNotExist:
pass
cache = self.request.event.cache
ckey = str(subevent.pk) if subevent else 'all'
p_date = OrderPayment.objects.filter(
order=OuterRef('pk'),
state__in=(OrderPayment.PAYMENT_STATE_CONFIRMED,
OrderPayment.PAYMENT_STATE_REFUNDED),
payment_date__isnull=False).values('order').annotate(
m=Max('payment_date')).values('m').order_by()
op_date = OrderPayment.objects.filter(
order=OuterRef('order'),
state__in=(OrderPayment.PAYMENT_STATE_CONFIRMED,
OrderPayment.PAYMENT_STATE_REFUNDED),
payment_date__isnull=False).values('order').annotate(
m=Max('payment_date')).values('m').order_by()
# Orders by day
ctx['obd_data'] = cache.get('statistics_obd_data' + ckey)
if not ctx['obd_data']:
oqs = Order.objects.annotate(
payment_date=Subquery(p_date, output_field=DateTimeField()))
if subevent:
oqs = oqs.filter(all_positions__subevent_id=subevent,
all_positions__canceled=False).distinct()
ordered_by_day = {}
for o in oqs.filter(event=self.request.event).values('datetime'):
day = o['datetime'].astimezone(tz).date()
ordered_by_day[day] = ordered_by_day.get(day, 0) + 1
paid_by_day = {}
for o in oqs.filter(
event=self.request.event,
payment_date__isnull=False).values('payment_date'):
day = o['payment_date'].astimezone(tz).date()
paid_by_day[day] = paid_by_day.get(day, 0) + 1
data = []
for d in dateutil.rrule.rrule(
dateutil.rrule.DAILY,
dtstart=min(ordered_by_day.keys())
if ordered_by_day else datetime.date.today(),
until=max(
max(ordered_by_day.keys(
) if paid_by_day else [datetime.date.today()]),
max(paid_by_day.keys(
) if paid_by_day else [datetime.date(1970, 1, 1)]))):
d = d.date()
data.append({
'date': d.strftime('%Y-%m-%d'),
'ordered': ordered_by_day.get(d, 0),
'paid': paid_by_day.get(d, 0)
})
ctx['obd_data'] = json.dumps(data)
cache.set('statistics_obd_data' + ckey, ctx['obd_data'])
# Orders by product
ctx['obp_data'] = cache.get('statistics_obp_data' + ckey)
if not ctx['obp_data']:
opqs = OrderPosition.objects
if subevent:
opqs = opqs.filter(subevent=subevent)
num_ordered = {
p['item']: p['cnt']
for p in (opqs.filter(
order__event=self.request.event).values('item').annotate(
cnt=Count('id')).order_by())
}
num_paid = {
p['item']: p['cnt']
for p in (opqs.filter(order__event=self.request.event,
order__status=Order.STATUS_PAID).
values('item').annotate(cnt=Count('id')).order_by())
}
item_names = {
i.id: str(i)
for i in Item.objects.filter(event=self.request.event)
}
ctx['obp_data'] = json.dumps([{
'item':
item_names[item],
'item_short':
item_names[item] if len(item_names[item]) < 15 else
(item_names[item][:15] + "…"),
'ordered':
cnt,
'paid':
num_paid.get(item, 0)
} for item, cnt in num_ordered.items()])
cache.set('statistics_obp_data' + ckey, ctx['obp_data'])
ctx['rev_data'] = cache.get('statistics_rev_data' + ckey)
if not ctx['rev_data']:
rev_by_day = {}
if subevent:
for o in OrderPosition.objects.annotate(payment_date=Subquery(
op_date, output_field=DateTimeField())).filter(
order__event=self.request.event,
subevent=subevent,
order__status=Order.STATUS_PAID,
payment_date__isnull=False).values(
'payment_date', 'price'):
day = o['payment_date'].astimezone(tz).date()
rev_by_day[day] = rev_by_day.get(day, 0) + o['price']
else:
for o in Order.objects.annotate(payment_date=Subquery(
p_date, output_field=DateTimeField())).filter(
event=self.request.event,
status=Order.STATUS_PAID,
payment_date__isnull=False).values(
'payment_date', 'total'):
day = o['payment_date'].astimezone(tz).date()
rev_by_day[day] = rev_by_day.get(day, 0) + o['total']
data = []
total = 0
for d in dateutil.rrule.rrule(
dateutil.rrule.DAILY,
dtstart=min(rev_by_day.keys(
) if rev_by_day else [datetime.date.today()]),
until=max(rev_by_day.keys(
) if rev_by_day else [datetime.date.today()])):
d = d.date()
total += float(rev_by_day.get(d, 0))
data.append({
'date': d.strftime('%Y-%m-%d'),
'revenue': round(total, 2),
})
ctx['rev_data'] = json.dumps(data)
cache.set('statistics_rev_data' + ckey, ctx['rev_data'])
ctx['has_orders'] = self.request.event.orders.exists()
ctx['seats'] = {}
if not self.request.event.has_subevents or (ckey != "all"
and subevent):
ev = subevent or self.request.event
if ev.seating_plan_id is not None:
seats_qs = ev.free_seats(sales_channel=None,
include_blocked=True)
ctx['seats']['blocked_seats'] = seats_qs.filter(
blocked=True).count()
ctx['seats']['free_seats'] = seats_qs.filter(
blocked=False).count()
ctx['seats']['purchased_seats'] = \
ev.seats.count() - ctx['seats']['blocked_seats'] - ctx['seats']['free_seats']
seats_qs = seats_qs.values('product', 'blocked').annotate(count=Count('id'))\
.order_by('product__category__position', 'product__position', 'product', 'blocked')
ctx['seats']['products'] = {}
ctx['seats']['stats'] = {}
item_cache = {
i.pk: i
for i in self.request.event.items.annotate(
has_variations=Count('variations')).filter(pk__in={
p['product']
for p in seats_qs if p['product']
})
}
item_cache[None] = None
for item in seats_qs:
product = item_cache[item['product']]
if item_cache[
item['product']] not in ctx['seats']['products']:
price = None
if product and product.has_variations:
price = product.variations.filter(
active=True).aggregate(
Min('default_price'))['default_price__min']
if product and not price:
price = product.default_price
if not price:
price = Decimal('0.00')
ctx['seats']['products'][product] = {
'free': {
'seats': 0,
'potential': Decimal('0.00'),
},
'blocked': {
'seats': 0,
'potential': Decimal('0.00'),
},
'price': price,
}
data = ctx['seats']['products'][product]
if item['blocked']:
data['blocked']['seats'] = item['count']
data['blocked'][
'potential'] = item['count'] * data['price']
else:
data['free']['seats'] = item['count']
data['free'][
'potential'] = item['count'] * data['price']
return ctx
def _compute_orders(self, quotas, q_items, q_vars, size_left):
events = {q.event_id for q in quotas}
subevents = {q.subevent_id for q in quotas}
seq = Q(subevent_id__in=subevents)
if None in subevents:
seq |= Q(subevent__isnull=True)
op_lookup = OrderPosition.objects.filter(
order__status__in=[Order.STATUS_PAID, Order.STATUS_PENDING],
order__event_id__in=events,
).filter(seq).filter(
Q(
Q(variation_id__isnull=True) & Q(
item_id__in={
i['item_id']
for i in q_items
if self._quota_objects[i['quota_id']] in quotas
})) | Q(
variation_id__in={
i['itemvariation_id']
for i in q_vars
if self._quota_objects[i['quota_id']] in quotas
})).order_by()
if any(q.release_after_exit for q in quotas):
op_lookup = op_lookup.annotate(
last_entry=Subquery(
Checkin.objects.filter(
position_id=OuterRef('pk'),
list__allow_entry_after_exit=False,
type=Checkin.TYPE_ENTRY,
).order_by().values('position_id').annotate(
m=Max('datetime')).values('m')),
last_exit=Subquery(
Checkin.objects.filter(
position_id=OuterRef('pk'),
list__allow_entry_after_exit=False,
type=Checkin.TYPE_EXIT,
).order_by().values('position_id').annotate(
m=Max('datetime')).values('m')),
).annotate(is_exited=Case(
When(
Q(last_entry__isnull=False) & Q(last_exit__isnull=False)
& Q(last_exit__gt=F('last_entry')),
then=Value(1, output_field=models.IntegerField()),
),
default=Value(0, output_field=models.IntegerField()),
output_field=models.IntegerField(),
), )
else:
op_lookup = op_lookup.annotate(
is_exited=Value(0, output_field=models.IntegerField()))
op_lookup = op_lookup.values('order__status', 'item_id', 'subevent_id',
'variation_id',
'is_exited').annotate(c=Count('*'))
for line in sorted(
op_lookup,
key=lambda li: (int(li['is_exited']), li['order__status']),
reverse=True): # p before n, exited before non-exited
if line['variation_id']:
qs = self._var_to_quotas[line['variation_id']]
else:
qs = self._item_to_quotas[line['item_id']]
for q in qs:
if q.subevent_id == line['subevent_id']:
if line['order__status'] == Order.STATUS_PAID:
self.count_paid_orders[q] += line['c']
q.cached_availability_paid_orders = self.count_paid_orders[
q]
elif line['order__status'] == Order.STATUS_PENDING:
self.count_pending_orders[q] += line['c']
if q.release_after_exit and line['is_exited']:
self.count_exited_orders[q] += line['c']
else:
size_left[q] -= line['c']
if size_left[q] <= 0 and q not in self.results:
if line['order__status'] == Order.STATUS_PAID:
self.results[q] = Quota.AVAILABILITY_GONE, 0
else:
self.results[q] = Quota.AVAILABILITY_ORDERED, 0
def post(self, request, format=None):
""" Return all high-level Federal Account information """
request_data = self._parse_and_validate_request(request.data)
limit = request_data["limit"]
page = request_data["page"]
sort_field = request_data["sort"]["field"]
sort_direction = request_data["sort"]["direction"]
keyword = request_data.get("keyword", None)
fy = request_data["filters"]["fy"]
agency_id = request_data["filters"].get("agency_identifier")
lower_limit = (page - 1) * limit
upper_limit = page * limit
account_filter = {}
if agency_id:
account_filter["parent_toptier_agency__toptier_code"] = agency_id
# Only return federal accounts that have ever had a submission. Only return budgetary_resources for
# the fiscal year requested. Note that we use Func instead of Sum below because Sum wants to perform a
# grouping when we don't want one.
queryset = (FederalAccount.objects.annotate(has_submission=Exists(
SubmissionAttributes.objects.filter(
appropriationaccountbalances__treasury_account_identifier__federal_account_id
=OuterRef("id")).values("pk")
)).filter(**account_filter, has_submission=True).annotate(
account_id=F("id"),
account_name=F("account_title"),
account_number=F("federal_account_code"),
budgetary_resources=Subquery(
AppropriationAccountBalances.objects.filter(
final_of_fy=True,
submission__reporting_period_start__fy=fy,
treasury_account_identifier__federal_account_id=OuterRef(
"id"),
).annotate(
the_sum=Func(F("total_budgetary_resources_amount_cpe"),
function="SUM")).values("the_sum"),
output_field=DecimalField(max_digits=23, decimal_places=2),
),
managing_agency=F("parent_toptier_agency__name"),
managing_agency_acronym=F("parent_toptier_agency__abbreviation"),
))
# add keyword filter, if it exists
if keyword:
queryset = queryset.filter(
Q(account_name__icontains=keyword)
| Q(account_number__contains=keyword)
| Q(managing_agency__icontains=keyword)
| Q(managing_agency_acronym__contains=keyword.upper()))
if sort_direction == "desc":
queryset = queryset.order_by(
F(sort_field).desc(nulls_last=True), "-federal_account_code")
else:
queryset = queryset.order_by(
F(sort_field).asc(), "federal_account_code")
result = {
"count": queryset.count(),
"limit": limit,
"page": page,
"fy": fy,
"keyword": keyword
}
resultset = queryset.values(
"account_id",
"account_number",
"account_name",
"budgetary_resources",
"agency_identifier",
"managing_agency",
"managing_agency_acronym",
)
resultset = resultset[lower_limit:upper_limit + 1]
page_metadata = get_simple_pagination_metadata(len(resultset), limit,
page)
result.update(page_metadata)
resultset = resultset[:limit]
result["results"] = resultset
return Response(result)
def subaward_annotations():
annotation_fields = {
"subaward_action_date_fiscal_year": FiscalYear("subaward__action_date"),
"prime_award_base_action_date_fiscal_year": FiscalYear("award__date_signed"),
"prime_award_period_of_performance_potential_end_date": Cast(
F("award__latest_transaction__contract_data__period_of_perf_potential_e"), DateField()
),
"prime_award_treasury_accounts_funding_this_award": Subquery(
Award.objects.filter(id=OuterRef("award_id"))
.annotate(value=StringAgg("financial_set__treasury_account__tas_rendering_label", ";", distinct=True))
.values("value"),
output_field=TextField(),
),
"prime_award_federal_accounts_funding_this_award": Subquery(
Award.objects.filter(id=OuterRef("award_id"))
.annotate(
value=StringAgg(
"financial_set__treasury_account__federal_account__federal_account_code", ";", distinct=True
)
)
.values("value"),
output_field=TextField(),
),
"usaspending_permalink": Concat(
Value(AWARD_URL), Func(F("award__generated_unique_award_id"), function="urlencode"), Value("/")
),
"prime_award_object_classes_funding_this_award": Subquery(
FinancialAccountsByAwards.objects.filter(
filter_limit_to_closed_periods(), award_id=OuterRef("award_id"), object_class_id__isnull=False
)
.annotate(
value=ExpressionWrapper(
Concat(F("object_class__object_class"), Value(": "), F("object_class__object_class_name")),
output_field=TextField(),
)
)
.values("award_id")
.annotate(total=StringAgg("value", ";", distinct=True))
.values("total"),
output_field=TextField(),
),
"prime_award_program_activities_funding_this_award": Subquery(
FinancialAccountsByAwards.objects.filter(
filter_limit_to_closed_periods(), award_id=OuterRef("award_id"), program_activity_id__isnull=False
)
.annotate(
value=ExpressionWrapper(
Concat(
F("program_activity__program_activity_code"),
Value(": "),
F("program_activity__program_activity_name"),
),
output_field=TextField(),
)
)
.values("award_id")
.annotate(total=StringAgg("value", ";", distinct=True))
.values("total"),
output_field=TextField(),
),
"prime_award_disaster_emergency_fund_codes": Case(
When(
broker_subaward__action_date__gte=datetime.date(2020, 4, 1),
then=Subquery(
FinancialAccountsByAwards.objects.filter(
filter_limit_to_closed_periods(), award_id=OuterRef("award_id")
)
.annotate(
value=ExpressionWrapper(
Case(
When(
disaster_emergency_fund__code__isnull=False,
then=Concat(
F("disaster_emergency_fund__code"),
Value(": "),
F("disaster_emergency_fund__public_law"),
),
),
default=Value(None, output_field=TextField()),
output_field=TextField(),
),
output_field=TextField(),
)
)
.values("award_id")
.annotate(total=StringAgg("value", ";", distinct=True))
.values("total"),
output_field=TextField(),
),
)
),
"prime_award_outlayed_amount_funded_by_COVID-19_supplementals": Case(
When(
broker_subaward__action_date__gte=datetime.date(2020, 4, 1),
then=Subquery(
FinancialAccountsByAwards.objects.filter(
filter_by_latest_closed_periods(),
award_id=OuterRef("award_id"),
disaster_emergency_fund__group_name="covid_19",
submission__reporting_period_start__gte=str(datetime.date(2020, 4, 1)),
)
.values("award_id")
.annotate(sum=Sum("gross_outlay_amount_by_award_cpe"))
.values("sum"),
output_field=DecimalField(),
),
),
),
"prime_award_obligated_amount_funded_by_COVID-19_supplementals": Case(
When(
broker_subaward__action_date__gte=datetime.date(2020, 4, 1),
then=Subquery(
FinancialAccountsByAwards.objects.filter(
filter_limit_to_closed_periods(),
award_id=OuterRef("award_id"),
disaster_emergency_fund__group_name="covid_19",
submission__reporting_period_start__gte=str(datetime.date(2020, 4, 1)),
)
.values("award_id")
.annotate(sum=Sum("transaction_obligated_amount"))
.values("sum"),
output_field=DecimalField(),
),
),
),
"prime_award_latest_action_date_fiscal_year": FiscalYear("award__latest_transaction__action_date"),
}
return annotation_fields
def get_story(self, doc, form_data):
from reportlab.platypus import Paragraph, Spacer, TableStyle, Table
from reportlab.lib.units import mm
headlinestyle = self.get_style()
headlinestyle.fontSize = 15
headlinestyle.fontName = 'OpenSansBd'
tz = pytz.timezone(self.event.settings.timezone)
tax_rates = set(a for a in OrderFee.objects.filter(
order__event=self.event).values_list(
'tax_rate', flat=True).distinct().order_by())
tax_rates |= set(a for a in OrderPosition.objects.filter(
order__event=self.event).filter(
order__status__in=self.form_data['status']).values_list(
'tax_rate', flat=True).distinct().order_by())
tax_rates = sorted(tax_rates)
# Cols: Order ID | Order date | Status | Payment Date | Total | {gross tax} for t in taxes
colwidths = [a * doc.width for a in [0.12, 0.1, 0.10, 0.12, 0.08]]
if tax_rates:
colwidths += [0.48 / (len(tax_rates) * 2) * doc.width
] * (len(tax_rates) * 2)
tstyledata = [
# Alignment
('ALIGN', (0, 0), (3, 0), 'LEFT'), # Headlines
('ALIGN', (4, 0), (-1, 0), 'CENTER'), # Headlines
('ALIGN', (4, 1), (-1, -1), 'RIGHT'), # Money
('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
# Fonts
('FONTNAME', (0, 0), (-1, 0), 'OpenSansBd'), # Headlines
('FONTNAME', (0, -1), (-1, -1), 'OpenSansBd'), # Sums
]
for i, rate in enumerate(tax_rates):
tstyledata.append(('SPAN', (5 + 2 * i, 0), (6 + 2 * i, 0)))
story = [
Paragraph(
_('Orders by tax rate ({currency})').format(
currency=self.event.currency), headlinestyle),
Spacer(1, 5 * mm)
]
tdata = [
[
_('Order code'),
_('Order date'),
_('Status'),
_('Payment date'),
_('Order total'),
] + sum(([localize(t) + ' %', ''] for t in tax_rates), []),
['', '', '', '', ''] + sum(
([_('Gross'), _('Tax')] for t in tax_rates), []),
]
op_date = OrderPayment.objects.filter(
order=OuterRef('order'),
state__in=(OrderPayment.PAYMENT_STATE_CONFIRMED,
OrderPayment.PAYMENT_STATE_REFUNDED),
payment_date__isnull=False).values('order').annotate(
m=Max('payment_date')).values('m').order_by()
qs = OrderPosition.objects.filter(
order__status__in=self.form_data['status'],
order__event=self.event,
).annotate(payment_date=Subquery(
op_date, output_field=models.DateTimeField())).values(
'order__code', 'order__datetime', 'payment_date',
'order__total', 'tax_rate', 'order__status',
'order__id').annotate(
prices=Sum('price'), tax_values=Sum('tax_value')).order_by(
'order__datetime' if self.form_data['sort']
== 'datetime' else 'payment_date', 'order__datetime',
'order__code')
fee_sum_cache = {
(o['order__id'], o['tax_rate']): o
for o in OrderFee.objects.values('tax_rate', 'order__id').order_by(
).annotate(taxsum=Sum('tax_value'), grosssum=Sum('value'))
}
last_order_code = None
tax_sums = defaultdict(Decimal)
price_sums = defaultdict(Decimal)
status_labels = dict(Order.STATUS_CHOICE)
for op in qs:
if op['order__code'] != last_order_code:
tdata.append([
op['order__code'],
date_format(op['order__datetime'].astimezone(tz),
"SHORT_DATE_FORMAT"),
status_labels[op['order__status']],
date_format(op['payment_date'], "SHORT_DATE_FORMAT")
if op['payment_date'] else '', op['order__total']
] + sum((['', ''] for t in tax_rates), []), )
last_order_code = op['order__code']
for i, rate in enumerate(tax_rates):
odata = fee_sum_cache.get((op['order__id'], rate))
if odata:
tdata[-1][5 +
2 * i] = odata['grosssum'] or Decimal('0.00')
tdata[-1][6 +
2 * i] = odata['taxsum'] or Decimal('0.00')
tax_sums[rate] += odata['taxsum'] or Decimal('0.00')
price_sums[rate] += odata['grosssum'] or Decimal(
'0.00')
i = tax_rates.index(op['tax_rate'])
tdata[-1][5 + 2 * i] = (tdata[-1][5 + 2 * i]
or Decimal('0.00')) + op['prices']
tdata[-1][6 + 2 * i] = (tdata[-1][6 + 2 * i]
or Decimal('0.00')) + op['tax_values']
tax_sums[op['tax_rate']] += op['tax_values']
price_sums[op['tax_rate']] += op['prices']
tdata.append([_('Total'), '', '', '', ''] + sum(([
price_sums.get(t) or Decimal('0.00'),
tax_sums.get(t) or Decimal('0.00')
] for t in tax_rates), []), )
tdata = [[
localize(round_decimal(c, self.event.currency)) if isinstance(
c, (Decimal, int, float)) else c for c in row
] for row in tdata]
table = Table(tdata, colWidths=colwidths, repeatRows=2)
table.setStyle(TableStyle(tstyledata))
story.append(table)
return story
def order_by_end_date(self):
# OutRef path__startswith with find all descendants of the parent
# We only have children, so no issues at this time
rounds = RoundBase.objects.open().filter(path__startswith=OuterRef('path'))
qs = self.public().live().annotate(end_date=Subquery(rounds.values('end_date')[:1]))
return qs.order_by('end_date')
def iterate_list(self, form_data):
tz = pytz.timezone(self.event.settings.timezone)
tax_rates = set(a for a in OrderFee.objects.filter(
order__event=self.event).values_list(
'tax_rate', flat=True).distinct().order_by())
tax_rates |= set(a for a in OrderPosition.objects.filter(
order__event=self.event).filter(
order__status__in=form_data['status']).values_list(
'tax_rate', flat=True).distinct().order_by())
tax_rates = sorted(tax_rates)
headers = [
_('Order code'),
_('Order date'),
_('Company'),
_('Name'),
_('Country'),
_('VAT ID'),
_('Status'),
_('Payment date'),
_('Order total'),
] + sum(([str(t) + ' % ' + _('Gross'),
str(t) + ' % ' + _('Tax')] for t in tax_rates), [])
yield headers
op_date = OrderPayment.objects.filter(
order=OuterRef('order'),
state__in=(OrderPayment.PAYMENT_STATE_CONFIRMED,
OrderPayment.PAYMENT_STATE_REFUNDED),
payment_date__isnull=False).values('order').annotate(
m=Max('payment_date')).values('m').order_by()
qs = OrderPosition.objects.filter(
order__status__in=form_data['status'],
order__event=self.event,
).annotate(payment_date=Subquery(
op_date, output_field=models.DateTimeField())).values(
'order__code', 'order__datetime', 'payment_date',
'order__total', 'tax_rate', 'order__status', 'order__id',
'order__invoice_address__name_cached',
'order__invoice_address__company',
'order__invoice_address__country',
'order__invoice_address__vat_id').annotate(
prices=Sum('price'), tax_values=Sum('tax_value')).order_by(
'order__datetime' if form_data['sort'] == 'datetime'
else 'payment_date', 'order__datetime', 'order__code')
fee_sum_cache = {
(o['order__id'], o['tax_rate']): o
for o in OrderFee.objects.values('tax_rate', 'order__id').order_by(
).annotate(taxsum=Sum('tax_value'), grosssum=Sum('value'))
}
last_order_code = None
tax_sums = defaultdict(Decimal)
price_sums = defaultdict(Decimal)
status_labels = dict(Order.STATUS_CHOICE)
row = None
for op in qs:
if op['order__code'] != last_order_code:
if row:
yield row
row = None
row = [
op['order__code'],
date_format(op['order__datetime'].astimezone(tz),
"SHORT_DATE_FORMAT"),
op['order__invoice_address__company'],
op['order__invoice_address__name_cached'],
op['order__invoice_address__country'],
op['order__invoice_address__vat_id'],
status_labels[op['order__status']],
date_format(op['payment_date'], "SHORT_DATE_FORMAT")
if op['payment_date'] else '',
round_decimal(op['order__total'], self.event.currency),
] + sum(([Decimal('0.00'), Decimal('0.00')]
for t in tax_rates), [])
last_order_code = op['order__code']
for i, rate in enumerate(tax_rates):
odata = fee_sum_cache.get((op['order__id'], rate))
if odata:
row[9 + 2 * i] = odata['grosssum'] or 0
row[10 + 2 * i] = odata['taxsum'] or 0
tax_sums[rate] += odata['taxsum'] or 0
price_sums[rate] += odata['grosssum'] or 0
i = tax_rates.index(op['tax_rate'])
row[9 + 2 * i] = round_decimal(row[9 + 2 * i] + op['prices'],
self.event.currency)
row[10 + 2 * i] = round_decimal(row[10 + 2 * i] + op['tax_values'],
self.event.currency)
tax_sums[op['tax_rate']] += op['tax_values']
price_sums[op['tax_rate']] += op['prices']
if row:
yield row
yield [_('Total'), '', '', '', '', '', '', '', ''] + sum(([
round_decimal(
price_sums.get(t) or Decimal('0.00'), self.event.currency),
round_decimal(
tax_sums.get(t) or Decimal('0.00'), self.event.currency)
] for t in tax_rates), [])
def get_agency_overview(self):
agency_filters = []
if self.filter is not None:
agency_filters.append(
Q(name__icontains=self.filter)
| Q(abbreviation__icontains=self.filter))
reporting_filters = [
Q(toptier_code=OuterRef("toptier_code")),
Q(fiscal_year=self.fiscal_year),
Q(fiscal_period=self.fiscal_period),
]
result_list = (ToptierAgencyPublishedDABSView.objects.filter(
*agency_filters).annotate(
agency_name=F("name"),
fiscal_year=Value(self.fiscal_year,
output_field=IntegerField()),
fiscal_period=Value(self.fiscal_period,
output_field=IntegerField()),
current_total_budget_authority_amount=Subquery(
ReportingAgencyOverview.objects.filter(*reporting_filters).
values("total_budgetary_resources")),
obligation_difference=Subquery(
ReportingAgencyOverview.objects.filter(*reporting_filters).
values("total_diff_approp_ocpa_obligated_amounts")),
total_dollars_obligated_gtas=Subquery(
ReportingAgencyOverview.objects.filter(*reporting_filters).
values("total_dollars_obligated_gtas")),
unlinked_contract_award_count=Subquery(
ReportingAgencyOverview.objects.filter(
*reporting_filters).annotate(
unlinked_contract_award_count=F(
"unlinked_procurement_c_awards") +
F("unlinked_procurement_d_awards")).values(
"unlinked_contract_award_count"),
output_field=IntegerField(),
),
unlinked_assistance_award_count=Subquery(
ReportingAgencyOverview.objects.filter(
*reporting_filters).annotate(
unlinked_assistance_award_count=F(
"unlinked_assistance_c_awards") +
F("unlinked_assistance_d_awards")).values(
"unlinked_assistance_award_count"),
output_field=IntegerField(),
),
recent_publication_date=Subquery(
SubmissionAttributes.objects.filter(
reporting_fiscal_year=self.fiscal_year,
reporting_fiscal_period=self.fiscal_period,
toptier_code=OuterRef("toptier_code"),
).values("published_date")),
recent_publication_date_certified=Subquery(
SubmissionAttributes.objects.filter(
reporting_fiscal_year=self.fiscal_year,
reporting_fiscal_period=self.fiscal_period,
toptier_code=OuterRef("toptier_code"),
).values("certified_date")),
submission_is_quarter=Subquery(
SubmissionAttributes.objects.filter(
reporting_fiscal_year=self.fiscal_year,
reporting_fiscal_period=self.fiscal_period,
toptier_code=OuterRef("toptier_code"),
).values("quarter_format_flag")),
tas_accounts_total=Subquery(
ReportingAgencyTas.objects.filter(
fiscal_year=self.fiscal_year,
fiscal_period=self.fiscal_period,
toptier_code=OuterRef("toptier_code"),
).annotate(
the_sum=Func(F("appropriation_obligated_amount"),
function="SUM")).values("the_sum"),
output_field=DecimalField(max_digits=23, decimal_places=2),
),
tas_obligation_not_in_gtas_total=Subquery(
ReportingAgencyMissingTas.objects.filter(
fiscal_year=self.fiscal_year,
fiscal_period=self.fiscal_period,
toptier_code=OuterRef("toptier_code"),
).annotate(the_sum=Func(F("obligated_amount"),
function="SUM")).values("the_sum"),
output_field=DecimalField(max_digits=23, decimal_places=2),
),
missing_tas_accounts_count=Subquery(
ReportingAgencyMissingTas.objects.filter(
fiscal_year=self.fiscal_year,
fiscal_period=self.fiscal_period,
toptier_code=OuterRef("toptier_code"),
).exclude(obligated_amount=0).annotate(
count=Func(F("tas_rendering_label"),
function="COUNT")).values("count"),
output_field=IntegerField(),
),
).values(
"agency_name",
"abbreviation",
"toptier_code",
"total_dollars_obligated_gtas",
"current_total_budget_authority_amount",
"obligation_difference",
"recent_publication_date",
"recent_publication_date_certified",
"tas_accounts_total",
"tas_obligation_not_in_gtas_total",
"missing_tas_accounts_count",
"fiscal_year",
"fiscal_period",
"submission_is_quarter",
"unlinked_contract_award_count",
"unlinked_assistance_award_count",
))
if self.pagination.sort_order == "desc":
result_list = result_list.order_by(
F(self.pagination.sort_key).desc(nulls_last=True),
"-toptier_code")
else:
result_list = result_list.order_by(
F(self.pagination.sort_key).asc(nulls_last=True),
"toptier_code")
return self.format_results(result_list)
def get_queryset(self, ignore_status=False):
cqs = Checkin.objects.filter(
position_id=OuterRef('pk'),
list_id=self.checkinlist.pk).order_by().values(
'position_id').annotate(m=Max('datetime')).values('m')
qs = OrderPosition.objects.filter(
order__event=self.request.event,
subevent=self.checkinlist.subevent).annotate(
last_checked_in=Subquery(cqs))
if self.request.query_params.get(
'ignore_status', 'false') != 'true' and not ignore_status:
qs = qs.filter(
order__status__in=[Order.STATUS_PAID, Order.STATUS_PENDING]
if self.checkinlist.include_pending else [Order.STATUS_PAID])
if self.request.query_params.get('pdf_data', 'false') == 'true':
qs = qs.prefetch_related(
Prefetch(lookup='checkins',
queryset=Checkin.objects.filter(
list_id=self.checkinlist.pk)), 'checkins',
'answers', 'answers__options', 'answers__question',
Prefetch(
'addons',
OrderPosition.objects.select_related('item', 'variation')),
Prefetch(
'order',
Order.objects.select_related(
'invoice_address').prefetch_related(
Prefetch(
'event',
Event.objects.select_related('organizer')),
Prefetch(
'positions',
OrderPosition.objects.prefetch_related(
'checkins',
'item',
'variation',
'answers',
'answers__options',
'answers__question',
))))).select_related('item', 'variation',
'item__category',
'addon_to', 'order',
'order__invoice_address',
'seat')
else:
qs = qs.prefetch_related(
Prefetch(lookup='checkins',
queryset=Checkin.objects.filter(
list_id=self.checkinlist.pk)), 'answers',
'answers__options', 'answers__question',
Prefetch(
'addons',
OrderPosition.objects.select_related(
'item', 'variation'))).select_related(
'item', 'variation', 'order', 'addon_to',
'order__invoice_address', 'order', 'seat')
if not self.checkinlist.all_products:
qs = qs.filter(item__in=self.checkinlist.limit_products.
values_list('id', flat=True))
return qs
