Django Opening Balance Calculations

[mintu...@gmail.com]

 

I am trying to create a simple inventory app using Django. My project
 came to a stand still when I couldn't find a solution for calculating
opening and closing balances on the fly/dynamically. I tried for days
researching on the web but no progress yet. If anyone could help i will
appreciate.

All I need is a query to calculate opening and closing balances dynamically.

My Models:

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

and so on.....

I am using item.quantity to store opening stock for the first time only.

Take into consideration that there will be different items and also back dated entries.

help me guys.

I tried adding these functions to my models and use them to display
opening(opening_balance) and closing (closing_balance) balances. But the
 problem is that the loop is not working.

the idea was that if the loop worked then the opening stock will be updated by the new closing stock each time the loop runs.

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