I'm working with sensitive currency values. In my case, i have to reproduce a sheet with it's formulas. The point is that i need to round down a currency value with 2 decimal places. A practical example is the number: 9809.4069, it should be rounded to 9809.40, with a truncation. Otherwise, normal rounding function returns me 9809.41.
Obs. For performance reasons i need to bring all my values in one query. Usual ways, with normal functions as round(), doesen't work inside query functions.
Well, my Query i'ts working completly FINE and brings everything that i want, the problem are the fields with Round() function, that returns me the "wrong" value.
The solution: (I'm using MySQL) was to create my own function that implements Trunc native function like the code below:
class Truncate(Func):
function = 'Truncate'
output_field=FloatField()
# In the annotate:
Truncate(F('item_value') * F('availability_percentage'),2)
The Query:
activetime_qs = activetime_qs.values(
'user', 'user__team__name','user__foreign_id','user__location','user__team__cost_center'
).annotate(
full_name=Concat(('user__first_name'),Value(' '),('user__last_name')),
project_id=Subquery(UserProject.objects.filter(user_id=OuterRef('user')).values('project')[:1]),
item_id=Subquery(Project.objects.filter(id=OuterRef('project_id')).values('item')[:1],),
project_name=Subquery(Project.objects.filter(id=OuterRef('project_id')).values('name')[:1]),
project_code=Subquery(Project.objects.filter(id=OuterRef('project_id')).values('code')[:1]),
item_name=Subquery(Item.objects.filter(id=OuterRef('item_id')).values('name')[:1]),
item_value=Subquery(Item.objects.filter(id=OuterRef('item_id')).values('unitary_value')[:1]),
available_time=Sum('duration'),
completed_tasks_amount=Case(
When(user__activity_type=ActivityType.DELIVERABLE,
then=Subquery(TaskExecution.objects.filter(members=OuterRef('user'), completed=True,
start__date__gte=initial_date, start__date__lte=final_date)
.values('pk').annotate(count=Func(F('pk'), function='Count'))
.values('count'), output_field=IntegerField()
)
),
default=1,
),
availability_percentage=Case(
When(user__activity_type=ActivityType.SERVICE_STATION,
then=Round(F('available_time') / expected_time, 3)),
default=0,
output_field=FloatField()
),
subtotal_value=Case(
When(user__activity_type=ActivityType.SERVICE_STATION,
then=Round(F('item_value') * F('availability_percentage'),2)
),
default=Round(F('item_value') * F('completed_tasks_amount'),3),
output_field=FloatField()
),
availability_discount=Case(
When(user__activity_type=ActivityType.SERVICE_STATION,
then=Round(-1 + F('availability_percentage'),3),
),
default=0,
output_field=FloatField()
),
discount_value=Case(
When(user__activity_type=ActivityType.SERVICE_STATION,
then=Round(F('item_value') - F('subtotal_value'),2),
),
default=0,
output_field=FloatField()
),
)
return activetime_qs
Then, i've tried two aprocches to round the annotate values down.
*My custom DecimalFieldClass:
class RoundedDecimalField(FloatField):
def __init__(self, *args, **kwargs):
super(RoundedDecimalField, self).__init__(*args, **kwargs)
self.context = decimal.Context(prec=self.max_digits, rounding=decimal.ROUND_DOWN)
def context(self):
return decimal.Context(prec=self.max_digits, rounding=decimal.ROUND_DOWN)
def to_python(self, value):
self.context = decimal.Context(prec=self.max_digits, rounding=decimal.ROUND_DOWN)
if value is None:
return value
if isinstance(value, float):
if math.isnan(value):
raise exceptions.ValidationError(
self.error_messages["invalid"],
code="invalid",
params={"value": value},
)
return self.context.create_decimal_from_float(value)
try:
return self.context.create_decimal(value)
except (decimal.InvalidOperation, TypeError, ValueError):
raise exceptions.ValidationError(
self.error_messages["invalid"],
code="invalid",
params={"value": value},
)
The annotate values with this approach:
completed_tasks_amount=Case(
When(user__activity_type=ActivityType.DELIVERABLE,
then=Subquery(TaskExecution.objects.filter(members=OuterRef('user'), completed=True,
start__date__gte=initial_date, start__date__lte=final_date)
.values('pk').annotate(count=Func(F('pk'), function='Count'))
.values('count'), output_field=IntegerField()
)
),
default=1,
),
availability_percentage=Case(
When(user__activity_type=ActivityType.SERVICE_STATION, then=Cast(Round(F('available_time') / expected_time, 3), output_field=RoundedDecimalField(max_digits=4,decimal_places=3))),
default=Cast(0, output_field=RoundedDecimalField(max_digits=4,decimal_places=3)),
output_field=RoundedDecimalField(max_digits=4,decimal_places=3)
),
subtotal_value=Case(
When(user__activity_type=ActivityType.SERVICE_STATION,
then=Cast(F('item_value') * F('availability_percentage'),output_field=RoundedDecimalField(max_digits=9,decimal_places=3))
),
default=Cast(F('item_value') * F('completed_tasks_amount'),output_field=RoundedDecimalField(max_digits=9,decimal_places=3)),
output_field=RoundedDecimalField(max_digits=9,decimal_places=4), # DELIVERABLE employee
),
availability_discount=Case(
When(user__activity_type=ActivityType.SERVICE_STATION,
then=Cast(-1 + F('availability_percentage'), output_field=RoundedDecimalField(max_digits=4,decimal_places=3)),
),
default=Cast(0,output_field=RoundedDecimalField(max_digits=4,decimal_places=3)),
output_field=RoundedDecimalField(max_digits=4,decimal_places=3)
),
discount_value=Case(
When(user__activity_type=ActivityType.SERVICE_STATION,
then=Cast(F('item_value') - F('subtotal_value'),output_field=RoundedDecimalField(max_digits=9,decimal_places=2)),
),
default=Cast(0, output_field=RoundedDecimalField(max_digits=9,decimal_places=2)),
output_field=RoundedDecimalField(max_digits=9,decimal_places=2)
),
But the values aren't rounded down yet.
def truncate(value):
try:
return math.trunc(value, 2)
except:
return 0
And in the annotate values i changed the ouput_field to:
FloatField(validators[truncate])
But i doesn't work too.
Is There a way to round the numbers down in the query? I didn't find anything about this. The django.db.models.Round native function doesn't have the round type as parameter and i couldn't use Func() to create my own... Someone could help me?
Mysql has an "truncate" function to round the values retrieved from queries. You could use an database function with 'Truncate' in the function argument
from django.db.models import Func,F
Func(F('item_value') * F('availability_percentage'), 2, function='Truncate', output_field=FloatField())