Django opening balance calculation

 Hi everyone.

I have my models that looks like these:

class Item(models.Model):
    name = models.CharField(max_length=30)
    buying_price = models.PositiveIntegerField()
    selling_price = models.PositiveIntegerField()
    quantity = models.PositiveIntegerField()

    def __str__(self):
        return self.name    

class SalesSheet(models.Model):
    txn_date = models.DateField()
    item = models.ForeignKey(Item, on_delete=models.CASCADE)
    purchases = models.PositiveIntegerField()
    sales = models.PositiveIntegerField()




What I want after posting purchases and sales is an output like this


id  date        item  opening  purchases sales closing
 1  1-1-19      abc   10       20        5     25
 2  2-12-19     def   25       20        10    35
 3  3-1-19      abc   25       10        25    10
 4  4-1-19      def   35       10        30    15
 5  7-1-19      abc   10       0         0     10
 6  9-1-19      def   15       0         5     10

Note that opening and closing fields are not in the model. They need to be calculated on the fly or rather dynamically. Also Note that there will be back dated entries and the items are different(not just one item)

My big problem is how to write a view function to calculate opening and closing balances.
 I tried putting these functions inside my models, but the loop is not working

class SalesSheet(models.Model):
    txn_date = models.DateField()
    item = models.ForeignKey(Item, on_delete=models.CASCADE)
    purchases = models.PositiveIntegerField()
    sales = models.PositiveIntegerField()

    def opening_balance(self):
        quantity = self.item.quantity
        items = SalesSheet.objects.all()
        for item in items:
            opening = quantity
            closing = opening + self.purchases - self.sales 
            quantity = closing
            return opening

    def closing_balance(self):
        quantity = self.item.quantity
        items = SalesSheet.objects.all()
        for item in items:
            opening = quantity
            closing = opening + self.purchases - self.sales 
            quantity = closing
            return closing
My idea here was to loop through the Transaction Model and update the opening balance as the loop continues, but unfortunately the loop isn't working.

Please if anyone could help write a view function just to display and calculate the balances.

Thanks.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/858bb8f6-6651-494f-9386-a25e79dad4b0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.