def get_salaries(self, obj):
data = []
with connection.cursor() as cursor:
cursor.execute('''
SELECT payroll_salary_id
FROM payroll_employer_highest_salaries
WHERE (employer_id = {employer_id} OR employer_parent_id = {employer_id})
AND reporting_year = {reporting_year}
ORDER BY total_pay DESC
LIMIT 5
'''.format(employer_id=obj.id,
reporting_year=self.context['data_year']))
top_salaries = [x[0] for x in cursor]
# Only retrieve related objects of the top salaries, to further
# optimize the query. Repeat the ordering operation for those five
# salaries to ensure we retain order during display.
top_salaries_with_related_objects = Salary.objects.with_related_objects(
).filter(id__in=top_salaries).order_by('-total_pay')
for salary in top_salaries_with_related_objects:
if salary.amount:
amount = format_salary(salary.amount)
else:
amount = 'Not reported'
if salary.extra_pay:
extra_pay = format_salary(salary.extra_pay)
else:
extra_pay = 'Not reported'
data.append({
'name': str(salary.job.person),
'slug': salary.job.person.slug,
'position': salary.job.position.title,
'employer': salary.job.position.employer.name,
'employer_endpoint': salary.job.position.employer.endpoint,
'employer_slug': salary.job.position.employer.slug,
'amount': amount,
'extra_pay': extra_pay,
'start_date': salary.job.start_date,
})
return data
def get_highest_spending_department(self, obj):
try:
top_department = self.department_statistics[0]
except IndexError:
return None
else:
return {
'name': top_department.name,
'amount': format_salary(top_department.total_expenditure),
'slug': top_department.slug,
}
def get_department_salaries(self, obj):
formatted_salaries = []
for salary in self.department_statistics[:5]:
formatted_salaries.append({
'name':
salary.name,
'slug':
salary.slug,
'headcount':
format_exact_number(salary.headcount),
'median_tp':
format_salary(salary.median_tp),
'entity_bp':
format_ballpark_number(salary.entity_bp),
'entity_ep':
format_ballpark_number(salary.entity_ep),
'total_expenditure':
format_ballpark_number(salary.total_expenditure),
})
return formatted_salaries
def get_median_ep(self, obj):
if self.employer_median_salaries['median_extra_pay']:
return format_salary(
self.employer_median_salaries['median_extra_pay'])
else:
return 'Not reported'