calculate difference between consecutive numbers in different rows of a field in the same model

user_9601086 0
Viewed 136

I am trying to calculate the difference between consecutive numeric values in the odometer_reading field of my model.
My Models.py has fields like below:

class Refuel(models.Model):
    vehicle = models.ForeignKey(Vehicle, blank=True, null=True, on_delete=models.SET_NULL)
    gaz_station = models.ForeignKey(
        GazStation, related_name=_("Station"), blank=True, null=True, on_delete=models.SET_NULL
    )
    odometer_reading = models.PositiveIntegerField(_("Compteur KM"), blank=True, null=True)
    snitch = models.PositiveIntegerField(_("Mouchard KM"), blank=True, null=True)
    fuel_quantity = models.DecimalField(_("Quantitée en Litres"), max_digits=5, decimal_places=1)
    fuel_unit_price = models.DecimalField(_("Prix en DH"), max_digits=6, decimal_places=2)
    note = models.CharField(_("Remarque"), max_length=255, blank=True, null=True)
    created_at = models.DateTimeField(_("Created at"), auto_now_add=True, editable=False)
    updated_at = models.DateTimeField(_("Updated at"), auto_now=True)
    is_active = models.BooleanField(default=True)

    @property
    def total_price(self):
        total_price = self.fuel_quantity * self.fuel_unit_price
        return total_price

    class Meta:
        ordering = ["gaz_station", "-created_at"]

    def __str__(self):
        return self.vehicle.serie

I want to use a CBV to get the distance between every two refuel for the same vehicle so I can calculate fuel consumption per km. is there a way to do it?
EDITED:
I want to return with every refuel the fuel consumption per km using the precedent refuel.

django-queryset django-views django-models django python
3 Answers

In your views you can create a view function that pulls two refuel objects then you can take the difference and use it as your templates context. You then can access it in the template using whatever you call it in this example we just used the same name as the variable "difference".

from myapp.models import Refuel 
from django.template import loader
from django.shortcuts import render
from django.http import HttpResponse


def odometer_difference(request):
    # get the two refuel objects with the odometer readings you needed to compare 
    refuel_1 = Refuel.objects.filter('your_filter') 
    refuel_2 = Refuel.objects.filter('your_filter')

    #define template 
    template = loader.get_template('my_template.html')
    
    difference = refuel_2.odometer_reading - refuel_1.odometer_reading

    context = { 'difference':difference} 

    return HttpResponse(template.render(context, request))
user_167841080

You can use Window functions that uses Lag to get a previous row's value like this:

from django.db.models import Window
from django.db.models.functions import Lag

last_odometer_reading = Window(
    expression=Lag('odometer_reading', default=0),
    partition_by=F('vehicle')
    order_by=F('created_at').asc(),
)

Refuel.objects.annotate(
    last_odometer_reading=last_odometer_reading
).annotate(
    odometer_difference=F('odometer_reading') - F('last_odometer_reading')
)

Each refuel row will be annotated with the last odometer reading (based on the refuel of the same vehicle) and will also be annotated with the difference between the current reading and the last reading.

user_67598440

I'm Using the form_valid **RefuelCreatView** when validation the new record to get the oldest data and perform my calculation and store the RefuelConsumption Model.

class RefuelCreationView(LoginRequiredMixin, CreateView):
    model = Refuel
    form_class = RefuelCreationForm
    template_name = "refuel/refuel_form.html"
    success_url = reverse_lazy("refuel:controlor-refuel-list")

    def form_valid(self, form):
        form.instance.user_id = self.request.user.id
        form.instance.gaz_station = GazStation.objects.get(Controlor_id=self.request.user.id)
        old_refuel_data = Refuel.objects.order_by().distinct("vehicle")
        for refuel in old_refuel_data:
            if refuel.vehicle == form.instance.vehicle and form.instance.odometer_reading:
                consumption = (refuel.fuel_quantity / (form.instance.odometer_reading - refuel.odometer_reading)) * 100
                FuelConsumption.objects.create(
                    vehicle=refuel.vehicle,
                    gaz_station=form.instance.gaz_station,
                    Controlor_id=self.request.user,
                    driver=refuel.vehicle.driver,
                    consumption=consumption,
                    is_active=True,
                )
            elif refuel.vehicle == form.instance.vehicle and form.instance.snitch:
                consumption = (refuel.fuel_quantity / (form.instance.snitch - refuel.snitch)) * 100
                FuelConsumption.objects.create(
                    vehicle=refuel.vehicle,
                    gaz_station=form.instance.gaz_station,
                    Controlor_id=self.request.user,
                    driver=refuel.vehicle.driver,
                    consumption=consumption,
                    is_active=True,
                )
        return super().form_valid(form)

    def get_queryset(self, *args, **kwargs):
        return Refuel.objects.select_related("vehicle__gaz_station__Controlor_id").filter(
            vehicle__gaz_station__Controlor_id=self.request.user
        )

and this work very well. I don't know if it's respecting the best practices in programming or not, I'm open to any advise.

user_96010860
Related